読者です 読者をやめる 読者になる 読者になる

dods’ blog - 今と昔のノートブック

今の記録は情報提供。昔の記憶は話題提供。気ままに記します。

どこにでもいるエクセルの達人 8 帳簿を自動作成(2)

宿題の確認

シリーズ8回目です。前回の宿題「帳簿を自動作成」を確認しましょう。下のような家計簿に入力していくだけで、

f:id:dods:20160326112241p:plain

自動的に下のような食費の帳簿をつくるには、どうする?

f:id:dods:20160501202934p:plain

これが宿題でした。順を追って、私のやり方を説明します。

宿題の取りかかり

  1. 家計簿中のセル範囲B4:F11に名前「家計簿」を付ける。
  2. 帳簿「食費」のセルD22に、=COUNTIF(D4:D11,B22)を入力する。2つ目の引数を"食費"ではなくB22としたのは、他の帳簿への活用を考えて。
  3. セルD22に名前「食費数」を付ける。
  4. 帳簿「食費」のF列、G列を中途計算用の列として確保し、検索文字列の作成と検索を実行する。

F列、G列の活用

4は話がややこしいです。G列、F列をジグザグに使い、G列で検索すべき文字列を特定、F列でその文字列をINDIRECT関数でセル参照に変換し、MATCH関数でそのセル範囲内の最初の「食費」行を検索、という流れです。F列、G列の働きを概念化すると、

f:id:dods:20160329213910p:plain

実際にセル範囲F23:G25に入力した計算式は次の通り。

 f:id:dods:20160329182848p:plain

F23に3と入力されている理由は、G23以下を同じ式で済ませるため。F24ではIF関数を使い、帳簿「食費」の列数(A列に1,2,3…と入力してある)のほうが家計簿中の食費の数より多くなったら、「-」と表示する仕掛けになってます。F24とG23の式を31行目までコピペした結果、返す値は次の通りです。

f:id:dods:20160501203000p:plain

G28以下がエラー表示になっているのは、F28に数値がない、つまり食費件数である4をオーバーしているため。G列は非表示になる列なので、式にエラー処理は加えません。

宿題の仕上げ

ここまで来たら、ゴールまであと1段階だけ。

  1. VLOOKUP関数を使い、家計簿からデータを行、列を指定し、帳簿「食費」を自動作成する。

実際にセル範囲B24:E31に入力した計算式は次の通り。

f:id:dods:20160329224148p:plain

f:id:dods:20160329224201p:plain

 IF関数で、F列が「-」、つまり食費件数オーバーだったら、こちらも「-」と表示する設定にしています。加えて、2点補足しておきます。まずF列(F24〜F31)のセルを指定するのに、Fではなく$Fとなっている理由。

絶対参照

$を列や行の前につけると「絶対参照」、相対的にセル参照させるのではなく、どの行、列からも一つのセルを参照させる方法です。コピペで手早く作業させようと思うときには必須の技です。実際には、まずセルB24の数式をC24からE24までコピペ。その後、VLOOKUP関数内の3つ目の引数、列番号を修正した上で、B24からE24を25列から31列までコピペします。

VLOOKUPの引数、列番号

ではその列番号について。この場合、名前「家計簿」の左から何列目なのかを意味します。たとえば帳簿「食費」のE列(支出)で参照したいのは名前「家計簿」で第5列(一番右の列)なので、E24の数式の中で、VLOOKUP関数内の最後の引数は「5」となっています。

以上で、「どこにでもいるエクセルの達人」第1シリーズは終わります。すべてに目を通していただいた方、感謝申し上げます。ところどころを読んでいただいた方、同じく感謝申し上げます。どちらの方であっても、コメント大歓迎です。

さて、次回シリーズではVBAについて、エクセルの達人的使い方を紹介しようと思っています。どうぞまたお付き合いください。