【図 58 】も D 。。記 < ま陽伸〈市攸「き 木ド 木前 藤 ( 1 2 3 4 5 6 7 8 0 1 3 4 に 0 6 7 8 0 、 0 1 ・第 , ( り 0 リ「 / CO 90 7 ・ ( 0 日 - O にに 0 7 ー 374 345 362 まー 0 8 ここに平均値を 加える 72 86 203 21 8 200 3 組 ・本、 - ( り 1 玉 - 70 5 B9 セルに =AVERAGE (B3:B7) と入れて D9 セルまでコピ べしますか ? 各組の人数が同じであれば、 1 組の分だけそう いうふうにつくっておいて、あとの組の分はそれをコピべ、コ ピペ・・・・・・と 100 回繰り返していけばなんとかなるかもしれませ んが、この場合人数はそれぞれバラバラです。 2 組、 3 組・・ とそれぞれの範囲に対応した AVERAGE 関数をつくっている と日が暮れてしまいますね。 この場合のネックは、つくろうとしている AVERAGE 関数 の範囲が一定でないことです。 ところが、全部の組に共通して、 SUM 関数による合計は正 しく入っているので、この SUM 関数で使っている範囲をなん 53 95

まず、波線部分ですが、シート「大阪」の、セル範囲 A2 : 117 には、「大阪」という名前をつけていましたので、こは、 範囲名の「大阪」とだけ記載すれば式として機能するはすです ね。しかも、この「大阪」というワードは、「ページ 1 」列に すでにあります。 以前、ウイザードで「選択したデータ範囲を識別するための ・・ラベルを指定してください」というところで同じ「大阪」 という名称を付けました。 これを、 INDIRECT 関数で指定すれば、 VLOOKUP 関数の 検索範囲として有効となるはずです。 なので、 INDIRECT ($D2) を、範囲としてセットします。 次に、太字部分ですが、「何も考えない」バージョンのよう に、定数「 2 」を列番号として入れてしまえば、式をコピーし たときにその部分が変化せず、項目の列番号に応じて「 3 」と か「 4 」等に手で変更しなければならなくなります。 これを防ぐために、列番号を取得する COLUMN 関数を使 って、列番号をセットしよう、ということです。 この VLOOKUP 関数を入力するセルは E2 セルで、 E は 5 列目に相当しますので、 COLUMN 関数だけ入れると、「 5 」が 返されます。今ほしい値は「 2 」なので、「 5 」と「 2 」の差分で ある「 3 」を、 COLUMN 関数からマイナスして帳尻を合わせ る必要があります。なので、 COLUMN()-3 を、列番号とし てセットします。 196

1 VLOOKUP 関数とは 「基本的な使い方」と「お作法」をお教えします ます最初に、 VLOOKUP 関数のお約東をおさらいしておき ます。 VLOOKUP 関数とは、「指定した条件に合致するデータを、 別表の左端の列から縦方向 (Vertical) に、探して (Lookup) きて、その位置から指定された列たけ右にある値を持ってくる 関数」です。 ちなみに、横方向 (Horizon) に探して (Lookup) くる HLOOKUP 関数というものもあります。 「別表を検索して見つけたデータを転記してくれる関数」と 思っていただければ結構です。ポイントは、太字で強調して記 載した部分、 ①別表 ②左端の列 3 指定された列だけ右にある値を持ってくる という点です。 ①別表からデータを持ってくるので、 VLOOKUP 関数を使 用するときには、必ず、別に「参照表」が存在します。現在あ なたが作成している表以外に別の表が存在していることが前提 です。そして、この表は、 VLOOKUP 関数の式をコピーして も参照範囲としては変わらないので、必す式の中で範囲を固定 034

を参照して賞与額を持ってくることができます。 ( 4 ) 旧関数と組み合わせていろいろな条件に合わせた結果を 出そう VLOOKUP 関数と旧関数とを組み合わせることによって、 たとえば、 2 つの表の差異を簡単に確認することができます。 A 表にはあって B 表にはないデータ、あるいはその逆を見つ けることができます。 IF 関数の書式は、 という形になります。日本語に翻訳すると、 「もし、《条件》が満たされていれば《 A 》を返し、《条件》 が満たされていなければ《 B 》を返しなさい」という意味にな ります。 IF 関数の便利なところは、複数の条件を組み合わせて次の ような書式で使うことができるところです。 日本語に翻訳すると、 「もし、《条件①》が満たされていれば《 A 》を返しなさい。 もし、《条件①》が満たされていないのであれば、《条件②》 0 章別の表から瞬時に自動転記 ! 「 VLOOKUP 関数」の活用法 051

=VL00KUP($A2,Master!$A:$H,6,0) ここで明らかなように、列番号が 1 ずつ増えるだけで、関数 式のその他の部分は同じですよね。であれば、この列番号部分 に関数式を入れて、コピべで 1 すっ増えるように変化させれば、 たとえ大量のデータであっても簡単に持ってくることができる これ =COLUMN() セルで、 COLUMN 関数は、列番号を返す関数です。たとえば、 B3 こういうときに、 COLUMN 関数を使います。 ようになります。 は「 4 」ですから、 COLUMN 関数を使って「 4 」をつくります。 【図 9 】の B2 セルに入っている VLOOKUP 関数式の列番号 わち 3 列目の列だから、「 3 」を返します。これを利用します。 C3 セルで同様に入力すれば、 C 列は A 列の次の次の列、すな 列の次の列、すなわち 2 列目の列だから、「 2 」を返します。 と入力すれば ( カッコの中には何も入れません ) 、 B 列は A 044 とすると、 B 列を表す「 2 」を返してしまいますので、 =VL00KUP($A2,Master!$A:$H,COLUMN() , 0 )

3 VLOOKUP 関数は、他の関数と 組み合わせると最大活用できる ( 1 ) COLUMN 関数と組み合わせてラクにコビべしよう VLOOKUP 関数で横方向に複数データを持ってきたい場合、 普通にコピペすると列番号 ( 何列目のデータを持ってきたいの か ) が変わらないため、列番号をいちいち手で変更する必要が 生じます。 そんなときは COLUMN 関数と組み合わせることによって、 列番号を自動的に変化させ、楽にコビべできるようになるので す。 たとえば、社員番号 10 番の人の名前と性別と生年月日を参 照表から持ってくるとします。 A2 セルに社員番号を入力する と、「 Master 」という別シートの参照表から、 B2 セル以降に それぞれのデータを持ってくるように関数をつくっていきます ( 【図 9 】、【図 10 】参照 ) 。 042

②章 たった 3 つの機能で、 仕事のスビードは飛躍的に加速する 3 2 1 他の関数と組み合わせると最大活用できる VLOOKUP 関数は、 思考の整理・典型的なシチュエーション こんなときに使おう 「基本的な使い方」と「お作法」をお教えします VLOOKUP 関数とは 「 VLOOKUP 関数」の活用法 別の表から瞬時に自動転記 ! 0 章 質問の半分以上が解決できた 3 つの機能の活用で、 知っているけれど」という人が多い ! 「なんとなく機能があることは 「使い捨てマクロ」の活用で差をつけよう ~ 「 VLOOKUP 関数」・「ピボットテーブル」・ 026 030 034 040 042

さて、 INDIRECT 関数は、指定したセルに入っている文字 を読み取って、それが示している範囲を参照する関数です。た とえば、【図 15 】で、 D4 セルに =IND 旧 ECT(A4) と入力すれば、 A4 セルを見に行って、それが示している範 囲、すなわち「 B2 」セルを参照して「阿部将史」という文字 を返します。 【図 15 】 =INDIRECT(A4) 別年 1 977 / 1 0 / 26 D4 ー - ク」っ凵 4 , 5 ) に 日 2 この関数を、 VLOOKUP 関数式の「範囲」に用いると、参 照範囲を切り替えることが可能となります。【図 12 】の F2 セ =VL00KUP(D2,INDIRECT(C2) ,2,0) と入力してみてください。 C2 セルには「一般社員」という 文字が入っています。 INDIRECT 関数はこれが示す範囲を参 照しますので、「一般社員」という名前を付けたセル範囲 ( 一 0 章 別の表から瞬時に自動転記 ! 「 VLOOKUP 関数」の活用法 049

とか AVERAGE 関数に利用できないかを考えます。 どの組のときにも汎用的に使える方法を考えて、まずは 1 組 の平均値を加え、その作業を " 使い捨てマクロ " で繰り返して 全部の組の平均値を入れてみましよう。 まず、 SUM 関数に使われている範囲をそのまま使いたいの で、適当な SUM 関数 ( ここでは B8 セルの SUM 関数 ) を " コ ピー " ではなく " 切り取り " で下のセルに持ってきます。 持ってきた SUM 関数のフィルハンドルをドラッグして右方 向に 3 セル分コピペ。 そのまま置き換えで「 SUM 」を「 AVERAGE 」へ。 切り取って穴のあいた部分にはその隣の SUM 関数をコピペ して穴埋め。 ここまでで 1 組の分は完成。同じことを、マクロのスタート 地点 ( 基点となるセル ) を定めて 100 組分繰り返せば全部でき そうですね。 マクロの自動記述を絡めて再度手順を整理すると、以下の通 りです。 ① E8 セルを選択して、「 = IF ( A8 = " 合計 " " ※※※ " , " " ) 」と入力。 ② [ 編集 (E)] → [ コピー (C)] で、セル範囲 EI : E1000 まで [ 貼り付け (P) ] 。 このようにして、マクロのスタート地点 ( 基点となるセル ) に、 IF 関数で " ※※※ " フラグをつけていきます。 0 章ー連の作業をあっという間にパッケージ化 ! 「使い捨てマクロ」の活用法 153

と入力すれば、「 S 」という文字を、セル範囲 AI:FI の中か ら探してきて、それが何個目にあるかを返してくれます。 ・・と順番に探して見つけるので、 2 では、 AI セル、 BI セル・・ 個目に見つけたことになり、「 2 」を返します。これを列番号の ところで利用します。 【図 12 】の F2 セルに、 =VL00KUP(D2, 一般職用 !$A:$F, MATCH(E2, 一般職用 !AI : ド 1 ,0),0) と入力してみてください。 450 , 000 円という金額が入りまし たでしようか。 太字にした MATCH 関数の部分は、「 2 」の意味でしたよね。 ですので、この式を日本語に翻訳すると、 「 D2 セルに入っているデータと同じものを、『一般職用』と いう名前のシートのセル範囲 A:F にある別表の左端の列から 探しなさい。そして、完全に一致するものが見つかったら、そ のデータから 2 列だけ右にあるデータを持ってきなさい」とい う意味になります。 ( 3 ) D 旧 ECT 関数と組み合わせて参照表を切り替えて使 0 章別の表から瞬時に自動転記 ! 「 VLOOKUP 関数」の活用法 VLOOKUP 関数と IND 旧 ECT 関数とを組み合わせること おう 047