新規記事の投稿を行うことで、非表示にすることが可能です。
2016年11月17日
エクセル,技,まとめ,並べ替え
マイクロソフトアクセスのまとめ記事はこちら
人気ブログランキングへ
データの並べ替え
上記のようなデータがある場合、色々な法則で並べ替えたい時や、
抽出したいときとかありますね。
そのような時にデータの並べ替え・フィルターを利用すると便利です。
まず「並べ替え」ですが、データ内の任意のセル(現在はA2)を選択して
「データ」タブの「並べ替え」を選択します
下のような表示になりますので「最優先されるキー」というところを選択すると、
項目が表示されます。
ここで並べ替えのルールを決めます。
まず、並べ替えのルールとして、何を一番優先させるかを決定します。
一応「販売日」にしてみましょう。
「並べ替えのキー」「順序」はとりあえずそのままで「OK」すると下記のように日付順で並びます。
同じように「相手先」を最優先させると「相手先」ごとに並びます。
タグ:エクセル,技,並べ替え
2016年11月15日
エクセル,技,印刷タイトル
マイクロソフトアクセスのまとめ記事はこちら
人気ブログランキングへ
印刷タイトル
印刷が複数枚に及ぶ際に2枚目以降はタイトル行が表示されないですね。
しかしタイトル行があったほうが分かりやすいという場合があります。
例えば「販売履歴」を印刷すると、1枚目は下のようにタイトル行が表示されていますね。
しかし2枚目以降は項目だけで、タイトル行はありません。
ここにタイトル行を追加表示する方法です。
「ページレイアウト」タブの「印刷タイトル」を押してください。
「ページ設定」という表示が出ますので、そこの「タイトル行」を選択してください。
赤矢印のところです。
下のような表示になりますので、マウスで1行目の左端をクリックしてください。
「$1:$1」という表示が出ましたか。1行目に点線の枠が出来ました。
この状態で、赤矢印を押してください。
下の画面に戻ります。「タイトル行」にも「$1:$1」という表示が出ましたか。
この状態で「OK」ボタンを押して「印刷プレビュー」を見てください。
下は1ページ目です。当然タイトル行はありますね。
これが2ページ目です。2ページ目にもタイトル行が表示されました。
これはもっと多いページでも同じようにタイトル行が追加されます。
2016年11月13日
エクセル,技,テキストボックス
マイクロソフトアクセスのまとめ記事はこちら
人気ブログランキングへ
テキストボックス
テキストボックスは「挿入」タブの「テキストボックス」を選択します。
ここでは「横書きテキストボックス」を作ってみましょう。
横書きテキストボックスを選択してシート状に枠を作ると上のように枠が出来ます。
これが横書きテキストボックスです。
ここにテキストを入力し、大きさも配置も自由に設定できます。
このテキストボックスも便利ですね。
縦書きテキストボックスもあります。
2016年11月11日
エクセル,技,カレンダー作成
マイクロソフトアクセスのまとめ記事はこちら
人気ブログランキングへ
【閑話休題】
先日職場の方より「簡単なスケジュール表を作ってほしい」という依頼があり。
作ってみました。
・1ヶ月ごとにA4で予定を書き込めるように枠を大きく。というのが条件です。
まあ毎月手動で日付を入れても構わないのですが、いいチャンスなので様々な
技を使ってスケジュール表を作ってみたので掲載いたします。
・レイアウトのイメージです。
K1に作成したい月の年月日(日は1日)を入力して、下の「カレンダーの作成」ボタンを
押すとその月のカレンダーが作成されるという仕組みです。
これを作成する際に私が検討した内容を思いついたまま書いてみます。
・A1には年月を記載・・・K1の内容をコピーして表示設定を「年・月」にする。
・その月の1日は何曜日なのかを検出して、2行目の同じ曜日にK1の日付を挿入する
・第一週は「1」を挿入した次の列から「+1」していく。土曜日(I列)まで
・1日以前の枠に前回作成した日付が残っている場合があるため、1日以前は空白にする必要がある。
・日曜・祝日は赤文字にしたい
・6週まであるのは、もし1日が金曜日とか土曜日から始まると、
週末が24/31のような形にする必要があります。
またそのようにすると「予定欄」が狭くなります。
結局6週分作成する方が目的に叶うかなと思い6週にしました。
上記の条件で作成開始しました。
・最初にA1の設定。セルには「=K1」と入力しK1の内容をコピーします。
セルの書式設定で「yyyy”年”m”月”」とします。
そうすることによってA1にはK1の日付が取り込まれ、表示は年・月になります。
・作成月の1日の曜日を取得する
これはVBAで取得する方が簡単なので下記のようなプログラムを挿入します。
コマンドボタンを作成してそこにプログラムを書いてください。
Sheets("メイン").Range("L1").Value = Weekday(Sheets("メイン").Range("k1").Value)
まずWeekday関数で日付から曜日の数字を取得します。
取得した数字をL1に挿入しなさいという命令文です。
上の例でいうと「L1」に「5」が表示されていますね。
日曜日が1で木曜日が5になります。
Sheets("メイン").Range("L2").Value = WeekdayName(Sheets("メイン").Range("L1"))
今度は逆にWeekdayName関数で「L1」の数字から曜日を作成して「L2」に挿入しています。
例ではL2に「木曜日」と表示されていますね。
これで作成月の1日の曜日は取得できました。
・次にC1から順番に見ていき先ほど取得した曜日と等しい曜日の2行目に
K1の日付をコピーしたいと思います。
やり方は色々あると思いますが、分かりやすいやり方として、
C1から順番に比較する方法を取りました。
Dim n As Integer 列の変数の宣言
For n = 3 To 9
If Sheets("メイン").Cells(1, n) = Sheets("メイン").Range("L2") Then
Sheets("メイン").Cells(2, n) = Sheets("メイン").Range("k1")
Exit For
End If
Next n
上記の内容ですが、For〜Nextを使用した繰り返し文です。
C1から見ていきL2の内容と同じ曜日の場合、2行目にK1の日付を入れよという命令文です。
同じ曜日を見つけて日付を入力したらForから出なさいということです。
・次に「1日」より前の列の入力を消去していきます。
Sheets("メイン").Range(Cells(2, 3), Cells(2, n - 1)).Select
Selection.ClearContents
これはCellsで範囲指定してその間を選択して、消去せよという命令文です。
・次に「1日」以降の列に日付を入れていく必要があります。
For n = n To 8
Sheets("メイン").Cells(2, n + 1).Value = Sheets("メイン").Cells(2, n) + 1
Next n
ここでFor n = n To 8とありますね。先ほどの1日の続きなので現在nは「5」ですね。
ですから変数nは5〜8ということです。
Sheets("メイン").Cells(2, n + 1)は1日の次の列を指定しています。
ここではH2からスタートします。
順番に+1していきなさいという命令文です。
これで第一週は出来ました。
2週目以降はどうしているのかというと、下のようにC4には「=I2+1」と入力されています。
「I2」の日付に+1するようにセルに式を入れています。
D4は下のようにC4に+1するように式を入れています。
E4以降も同じ。第3週以降も同じ式を入れています。
これで6週目までのカレンダーが出来ました。ただしこれは日付だけの話です。
・日曜・祝日を赤文字にしたい。
日曜日の列は単純に赤文字設定にすれば問題ありません。
後は休日設定ですね。
休日に関しては少し離れた場所(N18〜O35)あたりに2016年の休日一覧表を作っています。
ここを参照して休日を赤文字にしたいと思います。
隣に2017分がありますが、これは一旦無視してください。
全部の日付の欄に条件付き書式設定を設定します。
下のように「数式を使用して、書式設定するセルを決定」にしてルールは
「=COUNTIF($N$20:$N$35,C2)」と入力し、書式は赤文字にします。
まあC2は日曜日なので関係ないのですが一応同じように書式設定しておきます。
同じようにほかの日付が入るセルに条件付き書式設定をしてください。
めんどくさいのでコピーをしようと思うのですが、例えばC2をコピーしてD2からI2まで選択して、
貼り付けるとうまく行かないと思います。
1個ずつやればうまく行きます。C2コピーしてD2に貼り付けという感じですね。
1列(C2からI2)終われば、その1列全部コピーして2週目に貼り付けるのはOKです。
そのようにして3週目から6週目まで貼り付けしてください。
最後にVBAの全文を記載しておきます。
Private Sub CommandButton1_Click()
Dim n As Integer
Sheets("メイン").Range("L1").Value = Weekday(Sheets("メイン").Range("k1").Value)
Sheets("メイン").Range("L2").Value = WeekdayName(Sheets("メイン").Range("l1"))
For n = 3 To 9
If Sheets("メイン").Cells(1, n) = Sheets("メイン").Range("l2") Then
Sheets("メイン").Cells(2, n) = Sheets("メイン").Range("k1")
Exit For
End If
Next n
Sheets("メイン").Range(Cells(2, 3), Cells(2, n - 1)).Select
Selection.ClearContents
For n = n To 8
Sheets("メイン").Cells(2, n + 1).Value = Sheets("メイン").Cells(2, n) + 1
Next n
End Sub
これでK1に日付を記入してコマンドボタンを押すとカレンダーが完成します。
2017年になれば休日一覧を2016年の部分に上書きしてください。
VBAの書き方は色々あると思います。
上級者はもっと違うプログラムを組むと思いますが、私のレベルではこのくらいですね。
VBAの上級者を目指して頑張って行きましょう。
以上です。
一つだけ忘れていました。各日付のセルには「セルの書式設定」で「d」にしてください。
日にちだけ表示させるためです。
人気ブログランキングへ
【閑話休題】
先日職場の方より「簡単なスケジュール表を作ってほしい」という依頼があり。
作ってみました。
・1ヶ月ごとにA4で予定を書き込めるように枠を大きく。というのが条件です。
まあ毎月手動で日付を入れても構わないのですが、いいチャンスなので様々な
技を使ってスケジュール表を作ってみたので掲載いたします。
・レイアウトのイメージです。
K1に作成したい月の年月日(日は1日)を入力して、下の「カレンダーの作成」ボタンを
押すとその月のカレンダーが作成されるという仕組みです。
これを作成する際に私が検討した内容を思いついたまま書いてみます。
・A1には年月を記載・・・K1の内容をコピーして表示設定を「年・月」にする。
・その月の1日は何曜日なのかを検出して、2行目の同じ曜日にK1の日付を挿入する
・第一週は「1」を挿入した次の列から「+1」していく。土曜日(I列)まで
・1日以前の枠に前回作成した日付が残っている場合があるため、1日以前は空白にする必要がある。
・日曜・祝日は赤文字にしたい
・6週まであるのは、もし1日が金曜日とか土曜日から始まると、
週末が24/31のような形にする必要があります。
またそのようにすると「予定欄」が狭くなります。
結局6週分作成する方が目的に叶うかなと思い6週にしました。
上記の条件で作成開始しました。
・最初にA1の設定。セルには「=K1」と入力しK1の内容をコピーします。
セルの書式設定で「yyyy”年”m”月”」とします。
そうすることによってA1にはK1の日付が取り込まれ、表示は年・月になります。
・作成月の1日の曜日を取得する
これはVBAで取得する方が簡単なので下記のようなプログラムを挿入します。
コマンドボタンを作成してそこにプログラムを書いてください。
Sheets("メイン").Range("L1").Value = Weekday(Sheets("メイン").Range("k1").Value)
まずWeekday関数で日付から曜日の数字を取得します。
取得した数字をL1に挿入しなさいという命令文です。
上の例でいうと「L1」に「5」が表示されていますね。
日曜日が1で木曜日が5になります。
Sheets("メイン").Range("L2").Value = WeekdayName(Sheets("メイン").Range("L1"))
今度は逆にWeekdayName関数で「L1」の数字から曜日を作成して「L2」に挿入しています。
例ではL2に「木曜日」と表示されていますね。
これで作成月の1日の曜日は取得できました。
・次にC1から順番に見ていき先ほど取得した曜日と等しい曜日の2行目に
K1の日付をコピーしたいと思います。
やり方は色々あると思いますが、分かりやすいやり方として、
C1から順番に比較する方法を取りました。
Dim n As Integer 列の変数の宣言
For n = 3 To 9
If Sheets("メイン").Cells(1, n) = Sheets("メイン").Range("L2") Then
Sheets("メイン").Cells(2, n) = Sheets("メイン").Range("k1")
Exit For
End If
Next n
上記の内容ですが、For〜Nextを使用した繰り返し文です。
C1から見ていきL2の内容と同じ曜日の場合、2行目にK1の日付を入れよという命令文です。
同じ曜日を見つけて日付を入力したらForから出なさいということです。
・次に「1日」より前の列の入力を消去していきます。
Sheets("メイン").Range(Cells(2, 3), Cells(2, n - 1)).Select
Selection.ClearContents
これはCellsで範囲指定してその間を選択して、消去せよという命令文です。
・次に「1日」以降の列に日付を入れていく必要があります。
For n = n To 8
Sheets("メイン").Cells(2, n + 1).Value = Sheets("メイン").Cells(2, n) + 1
Next n
ここでFor n = n To 8とありますね。先ほどの1日の続きなので現在nは「5」ですね。
ですから変数nは5〜8ということです。
Sheets("メイン").Cells(2, n + 1)は1日の次の列を指定しています。
ここではH2からスタートします。
順番に+1していきなさいという命令文です。
これで第一週は出来ました。
2週目以降はどうしているのかというと、下のようにC4には「=I2+1」と入力されています。
「I2」の日付に+1するようにセルに式を入れています。
D4は下のようにC4に+1するように式を入れています。
E4以降も同じ。第3週以降も同じ式を入れています。
これで6週目までのカレンダーが出来ました。ただしこれは日付だけの話です。
・日曜・祝日を赤文字にしたい。
日曜日の列は単純に赤文字設定にすれば問題ありません。
後は休日設定ですね。
休日に関しては少し離れた場所(N18〜O35)あたりに2016年の休日一覧表を作っています。
ここを参照して休日を赤文字にしたいと思います。
隣に2017分がありますが、これは一旦無視してください。
全部の日付の欄に条件付き書式設定を設定します。
下のように「数式を使用して、書式設定するセルを決定」にしてルールは
「=COUNTIF($N$20:$N$35,C2)」と入力し、書式は赤文字にします。
まあC2は日曜日なので関係ないのですが一応同じように書式設定しておきます。
同じようにほかの日付が入るセルに条件付き書式設定をしてください。
めんどくさいのでコピーをしようと思うのですが、例えばC2をコピーしてD2からI2まで選択して、
貼り付けるとうまく行かないと思います。
1個ずつやればうまく行きます。C2コピーしてD2に貼り付けという感じですね。
1列(C2からI2)終われば、その1列全部コピーして2週目に貼り付けるのはOKです。
そのようにして3週目から6週目まで貼り付けしてください。
最後にVBAの全文を記載しておきます。
Private Sub CommandButton1_Click()
Dim n As Integer
Sheets("メイン").Range("L1").Value = Weekday(Sheets("メイン").Range("k1").Value)
Sheets("メイン").Range("L2").Value = WeekdayName(Sheets("メイン").Range("l1"))
For n = 3 To 9
If Sheets("メイン").Cells(1, n) = Sheets("メイン").Range("l2") Then
Sheets("メイン").Cells(2, n) = Sheets("メイン").Range("k1")
Exit For
End If
Next n
Sheets("メイン").Range(Cells(2, 3), Cells(2, n - 1)).Select
Selection.ClearContents
For n = n To 8
Sheets("メイン").Cells(2, n + 1).Value = Sheets("メイン").Cells(2, n) + 1
Next n
End Sub
これでK1に日付を記入してコマンドボタンを押すとカレンダーが完成します。
2017年になれば休日一覧を2016年の部分に上書きしてください。
VBAの書き方は色々あると思います。
上級者はもっと違うプログラムを組むと思いますが、私のレベルではこのくらいですね。
VBAの上級者を目指して頑張って行きましょう。
以上です。
一つだけ忘れていました。各日付のセルには「セルの書式設定」で「d」にしてください。
日にちだけ表示させるためです。
タグ:エクセル,技,カレンダー
2016年11月09日
エクセル,技,吹き出し
マイクロソフトアクセスのまとめ記事はこちら
人気ブログランキングへ
この吹き出しにテキストを挿入するには右クリックして「テキストの編集」を選択します。
この状態で何かコメントを入れてみてください。
???何も書けないですよね。
カーソルだけが移動していくということになります。
このテキストのフォントの設定で文字の色を確認してほしいのですが、
「自動」になっていますね。
この「自動」は「白」で設定されています。
吹き出しの塗りつぶしはデフォルトで青だったと思います。
そこから文字は白ということになっているのでしょうね。
最初は分からなくて「文字が書けない!」とパニックになった記憶があります。
■自動になっているのだから当然「黒」だと思いますよね。
黒又は別の色にするために、ここでしっかり色の指定をします。自動は「白」ですよ。
しっかり「黒」を選択してください。
これで「黒」でテキストが書けるようになります。
タグ:エクセル,技,吹き出し
2016年11月07日
エクセル,技,吹き出し
マイクロソフトアクセスのまとめ記事はこちら
人気ブログランキングへ
シート上に吹き出しとかセルに無関係でコメントみたいなものを
入れたい時があります。
吹き出しを作ってみましょう。
タブの「挿入」から「図形」を選択して下の方にある「吹き出し」を選択しましょう。
シート上で適当な大きさで描画してください。下のようになりましたか?
エクセル2010ではデフォルトでは上記のようなブルー系に色になっています。
ここに文字入力すると「白」で表示されます。
このままではちょっと使いづらいのでこの吹き出しを加工します。
図形を右クリックして「図形の書式設定」を開いたところです。
「塗りつぶし」「線の色」「線のスタイル」大体この3つで好みの吹き出しが作成できると思います。
私がよく使用するのは「塗りつぶし」なし、「線の色」は黒、「線のスタイル」は
1ポイントですね。
それからこの図形を右クリックして「既定の図形に設定」すると、
このシート内ではこれがデフォルトになります。
タグ:エクセル,技,吹き出し
2016年11月06日
エクセル,技,リストボックス
マイクロソフトアクセスの使い方はこちら
人気ブログランキングへ
リストボックスの作り方
開発タブの「挿入」からフォームコントロールで「リストボックス」を選択します。
上のようにB列付近にリストボックスを配置しました。
ここにマウスを当てて右クリックしてください。
メニューから「コントロールの書式設定」を選択します。
ここでまた範囲をしているところがあります。赤矢印を押してからリスト範囲をドラッグしてください。
次に「リンクするセル」とありますね。これは選択したリストをどこに表示させるかを決定します。
一応ここを押してD2を選択しましょう。
上記の状態で「OK」を押してみましょう。
下のようにリストボックスが出来ましたね。今「小沢 真琴」を選択しています。
D2に「9」という数字が表示されていますね。
これは9番目のリストが選択されているという意味です。
しかし「9」なんて数字で表示されても困りますよね。
そこでちょっとした関数を使用します。
表示させるセルをD4にします。
D4に下のように「=INDEX(E2:E24,D2)」と入力します。
そうするとD4に「小沢 真琴」が出力されます。
他の名前を選択した時もその名前がD4に出るか確かめてください。
リストボックスとコンボボックスの違い
・コンボボックスは直接入力できる。リストボックスは一覧から選択するしかない。
・コンボボックスの選択肢は1つだが、リストボックスは設定により複数選択が可能。
大きな違いは上記くらいでしょうか。用途によって使い分けしてください。
私個人は圧倒的にコンボボックスを利用しています。
2016年11月05日
エクセル,技,コンボボックス
マイクロソフトアクセスの使い方はこちら
人気ブログランキングへ
コンボボックス
エクセルのシート上にコンボボックスを作ってみましょう。
下記のシートでE列に名前があります。
B2にこの名前のコンボボックスを作ります。
B2を選択した状態でデータタブの「データの入力規則」で「データの入力規則」を選択してください。
「設定」の「入力値の種類」でリストを選択して「OK」を押してください。
下の画面になれば赤矢印のところをクリックします。ここはリストの範囲を指定します。
データの入力規則という小さなボックスが表示されます。
この状態でE2からE24までマウスでドラッグすると下記のように
「元の値」というところに「=$E$2:$E$24」という値が自動的に入力されます。
もちろん手入力で直接入力してもOKです。
そして選択が終われば右端の四角を押してください。
上記のようになりましたか。ここで「OK」を押します。
上記のようにコンボボックスが完成しました。
それから、なにか「リクエスト」があればコメント入れてください。「過去分でもっと詳細に。」でも結構です。
よろしくお願い致します。
2016年11月04日
エクセル,技,ユーザー設定
マイクロソフトアクセスはこちら
人気ブログランキングへ
書式のユーザー設定
通常「セル」に書式設定する場合は下記の部分で「通貨」とか「日付」とか
「パーセンテージ」とか選択しますね。
しかしここに無い場合はどうしたらいいのか。
その場合は一番下の「ユーザー定義」を使用します。
ユーザー定義でもいくつかサンプルがあるのでその中に利用できるものが
あればそれを使用すればいいと思います。
しかしそれでもない場合は自分で作るしかありません。
例えば上記B4で「2000」と入力しています。
それを「2,000件」というように3桁区切りで最後に「件」を付けたいと思います。
「G/標準」と書かれているところに上のように「0,000”件”」と入力します。
これは3ケタ区切りで最後に「件」を入れるということです。
結果は下のように「2,000件」と表示されました。文字列を付けたいときは「” ”」で囲ってください。
上記以外でも様々な形で表示できますので色々試してみてください。
また文字列を前につけることも可能です。
「”合計”0,000”件”」とした場合は「合計2,000件」という表示になります。
この「ユーザー定義」は意外に役に立ちます。
私もよく利用しています。
2016年11月03日
エクセル,技,ピボットテーブル
マイクロソフトアクセスはこちら
人気ブログランキングへ
ピボットテーブルの作成・使い方
ピボットテーブルを使用すると簡単にデーターベースを統合・集計・分析できます。
例えば、「相手先別」の売上とか、「日付別」の売り上げ、「商品」ごとの販売状況など、
目的に合わせて情報を抽出することが出来ます。
【作成方法】
データーベース上の任意の場所にマウスでクリックしてセルを選択します。
こうすると自動的にデーターベースの範囲を取得します。
リボンの「挿入」タブの左端に「ピボットテーブル」というところがあるので、クリックしてください。
下記のようにデーター範囲を聞いてきますので良ければ「OK」を押してください。
下記のように他のシートにピボットテーブルの基本が作成されます。
下のように「レポートに追加するフィールドを選択してください」というところにチェックを
入れると下の「行ラベル」「Σ値」に項目が追加される。
またこの行ラベルの項目を「列ラベル」までドラッグすると列ラベルになる
いま「商品名」を列ラベルに移動したところです。シート内の表示で商品名は列になりましたね。
この段階で列に表示されている商品は各相手先にどれだけ販売されているか、
また販売数量の合計が表示されます。
いま、販売日にチェックを入れました。下のように販売日別の集計が表示されています。
また販売日をその上の「レポートフィルター」に移動すると左上に「販売日」「すべて」
というセルが出現します。
このB1セルで販売日が選択でき、その日の販売状況を確認できます。
下のように2015/1/6を選択するとその日の販売分だけ表示されます。
このようにピボットテーブルはデータを簡単な手順で様々な抽出条件設定で抽出できる機能です。
データを分析したい時は非常に便利だと思います。