1
紙面の都合で書籍に載せられなかったコンテンツ
ページ数の都合で載せられなかったコンテンツの中で、大切なものをい くつかご紹介します。本文の理解を助ける補足資料としてご利用いただけ れば幸いです。 ■『課長のためのエクセル再入門講座』 異なるバージョンにおける手順の解説や、本文の補足に「コラム」を使 っていますが、一部紙面の都合で載せられませんでした。 ここでは、特に、第３章の「コラム」を補足資料として取り上げます。 条件付き書式についての補足資料（第３章） 2007 以降のバージョンでは、条件付き書式の表現力が大幅にアップす る新機能も追加されています。これらについても少し触れておきましょう。 「わざわざグラフ化して表示するまでもないけれども、数値の大きさを 視覚的に表現したい…」そんなときに役立つのが「データバー」「カラー スケール」「アイコンセット」という3 つの新機能です。2
「データバー」を例にとって解説します。
3 タブ→[スタイル]グループ→[条件付書式]→[データバー]から、「青のデー タバー」を選択します。 データバーの条件設定は、上図[その他のルール]から変更できます。 他にも、セルの色の使い分けによって表現する「カラースケール」、記 号の使い分けによって表現する「アイコンセット」が用意されているので、 次のように、それらを組合せて使用することも可能です。
4 また、「条件付き書式」ではありませんが、2010 では、複数データの推 移を、セル内にミニグラフを表示することによって簡単に可視化できる 「スパークライン」機能も追加されました。使い方は簡単なので実際に試 してみましょう。 (1) [挿入]タブ→[スパークライン]グループ→[折れ線]をクリック (2) [スパークラインの作成]ダイアログで、「データ範囲」に、セル範囲 G2:I16 を、「スパークラインを配置する場所の範囲」に、セル範囲J2:J16 を指定して[OK]ボタンをクリック
5
(3) セル内に小さな折れ線グラフが挿入されます。[スパークラインツー ル]の[デザイン]タブからいろいろな設定ができるので、お試しください。
6 ■『デスクワークを３倍効率化するテクニック』 各章の終わりに、「ちょっと寄り道」コーナーを設けていましたが、紙 面の都合で載せられませんでした。 ここでは、第３章と第４章の「ちょっと寄り道」コーナーを補足資料と して取り上げます。 （1）SUMPRODUCT 関数（第３章） VLOOKUP 関数と IF 関数との組合せを紹介しましたが、それに関連し て、よく似た使い方ができる「SUMPRODUCT 関数」もご紹介しておき ます。 実は、前述（P.30）の「④上記の組合せやその他の方法で解決できる質 問」の中でも、多いのはSUMPRODUCT 関数に関する質問です。 さっそく例を見てみましょう。【図1】をご参照ください。 最近は、書籍の内容をCD で聴く「オーディオブック」やネットで配信 する「ネットブック」などの形態も出てきました。【図 1】では、タイト ルとその種類を入力すると、価格が表示される計算式を作成します。 まずは、VLOOKUP 関数を使ってやってみます。このように、検索条 件が複数ある場合は、一番左端に新たに作業列を挿入して、検索条件を一 つにまとめてしまいます。 A2 セルに、 =B2&C2 と、B2 セルと C2 セルの値をくっつけて一つにし、A8 セルまでコピペし ておきます。
7 G3 セルには、 =VLOOKUP(G1&G2,$A:$D,4,0) としておけば完成です。 【図1】 さて、これをSUMPRODUCT 関数を使ってやってみましょう。この場 合、A 列のような作業列は不要です。 まずは、当関数の基本的な考え方から順次解説していきます。
SUMPRODUCT 関数は、もともと、SUM 関数（たし算）と PRODUCT 関数（かけ算）とが合体した関数ですので、かけ算したものをたし合わせ るときに使用します。たとえば、【図2】のようなケースで、C3 セルに、 =SUMPRODUCT(A1:A3,B1:B3) …① と入れると、 2×3＋5×6＋10×1＝46 を返します。 2×3，5×6，10×1 の結果をそれぞれ順番に覚えておいて、最後にすべ てをたし算します。「カンマで区切った範囲どうしを上から順番にかけ算 して、その結果をたし合わせる関数」と覚えておいてください。
8 【図2】 これの応用形で、たとえば、A 列が 5 以下で、かつ B 列が 3 以上の組 合せが何個あるかをカウントすることができます。 これの答は、「A 列が 2，B 列が 3 の組合せ」と、「A 列が 5，B 列が 6 の組合せ」の2つですね。これを求めるための関数式は、 =SUMPRODUCT((A1:A3<=5)*(B1:B3>=3)) …② となります。 まず、この式②の意味を解説します。
A 列を見てください。A1 セルの「2」から、A2 セル「5」，A3 セル「10」
まで順番に確認します。5 以下であれば「TRUE」，そうでなければ 「FALSE」が入ります。「TRUE」は数字で表現すると「1」，「FALSE」 は「0」なので、A 列は A1 セルから順に、「TRUE」（1），「TRUE」（1）， 「FALSE」（0）となります。 今度は B 列を確認します。B1 セルの「3」から、B2 セル「6」，B3 セ ル「1」まで順番に確認し、3 以上であれば「TRUE」，そうでなければ 「FALSE」が入ります。そうすると、B 列は B1 セルから順番に、「TRUE」 （1），「TRUE」（1），「FALSE」（0）となります。 この式はかけ算になっていますので、A 列と B 列を順番にかけ算し、そ の結果を覚えておいて、最後にすべてをたし算します。よって、 1×1＋1×1＋0×0＝2 が答になります。図で表すと次のようになります。
9 (A1:A3<=5) * (B1:B3>=3) さて、今度は、A 列が 5 以下で、かつ B 列が 3 以上という条件を満た す場合のB 列のトータル値を計算してみましょう。 これの答は、『 A 列が 2，B 列が 3 の場合の B 列の値「3」 』と、「 A 列が5，B 列が 6 の場合の B 列の値「6」 』のたし算で「9」ですね。こ れを求めるための関数式は、 =SUMPRODUCT((A1:A3<=5)*(B1:B3>=3),B1:B3) …③ となります。 この式③の意味を解説しましょう。 式②との相違点は、式②のカッコ内の最後に「,B1:B3」という範囲が追 加されただけです。 式②では、最終的に、「1」「1」「0」という結果が返され、それらがたし 算されました。 今度はそれに、カンマで区切られ、B 列の範囲が追加されています。 式①で解説したように、SUMPRODUCT 関数は、「カンマで区切った 範囲どうしを上から順番にかけ算して、その結果をたし合わせる関数」で すので、「1」「1」「0」に B 列の値「3」「6」「1」が順番にかけ算され、そ
10 の結果がたし合わされることになります。よって、 1×3＋1×6＋0×1＝9 が答になります。図で表すと次のようになります。 (A1:A3<=5) * (B1:B3>=3)
，
B1:B3 まとめますと、 １．複数条件を満たすものがいくつあるのか、個数をカウントするには、 SUMPRODUCT 関数の( )の中に「条件 1*条件 2*条件 3*…」と入れてい きます。条件の記載の仕方は例示したように、「列範囲＝条件」の形式で 記載します。（条件は30 個まで指定可能です。） 一般的に、「*」は「かつ」，「+」は「または」を意味しますので、これら を組み合わせて複雑な条件を指定することも可能です。 ２．複数条件を満たす数値のトータルを求めるには、上記の条件の次にカ ンマで区切って、たし算する列範囲を指定します。 これを踏まえて、【図1】の G3 セルにはどんな SUMPRODUCT 関数を 入れればいいのかを考えてみましょう。11 【図1】再掲 セル範囲 B2:B8 の中から、G1 セルと一致するデータを選択し、かつ、 セル範囲C2:C8 の中から、G2 セルと一致するデータを選択し、両方の条 件を満たすところのD 列データを持ってくれば（たし合わせれば）いい、 ということですね。 （今回の例では、「タイトル」－「種類」－「価格」は一意的な関係です ので、「たし合わせる」は「持ってくる」と同義です。） ですので、この例ではG3 セルに、 =SUMPRODUCT((B2:B8=G1)*(C2:C8=G2),D2:D8) とすればOK です。 求める値が数値の場合は、このようにSUMPRODUCT 関数を使って持 ってくる（たし合わせる）ことができます。しかし、文字列を求める場合 には（たし合わせることができないため）使用できませんので、その場合 は前述したような、作業列を利用したVLOOKUP 関数を使いましょう。 （2）配列関数（第４章） 「配列関数」という言葉をご存知でしょうか。
12 複数セルを使って計算しないとできないはずの処理を、一つの式の中で 一気に計算してしまいたいときに使用します。 前の「ちょっと寄り道」コーナーで紹介した SUMPRODUCT 関数は、 実は最初から「配列」の考え方を前提とした関数です。かけ算した結果を 覚えておいて、たし合わせる関数でしたね。 この、計算結果をいったん覚えておいて別の計算式に使うような計算を するのが「配列」の考え方です。 たとえば、 という表があったとき、配列関数を使用して、女性の70 点以上の点数 だけを平均してみましょう。 (1) C7 セルを選択。 (2) =AVERAGE(IF((C2:C6>=70)*(B2:B6="女"),C2:C6,"")) と入力して、 [CTRL]+[SHIFT]+[ENTER]。 {=AVERAGE(IF((C2:C6>=70)*(B2:B6="女"),C2:C6,""))} と表示され、 C7 セルに 84 が入れば OK です。 [ENTER]キーではなく、[CTRL]+[SHIFT]+[ENTER]キーで確定する関 数式を「配列関数」といいます。 配列関数を使うと、複数セルの処理を一つの式の中で一気に計算するこ
13 とができます。上の例では、 1．C2 セルから C6 セルの中から 70 以上の数値を探す。 2．B2 セルから B6 セルの中から「女」という文字列を探す。 3．C2 セルから C6 セルの中から条件を満たすものだけ平均する。 という3 つのことを一つの式の中で一気に計算しています。あまり見かけ ないと思いますので、ゆっくり説明していきます。 まずは「女性」という条件を除いて式を作ってみますと、 {=AVERAGE(IF(C2:C6>=70,C2:C6,""))} となります。 「=AVERAGE(IF(C2:C6>=70,C2:C6,""))」と入力した後、[ENTER]キー ではなく、[CTRL]+[SHIFT]+[ENTER]キーで確定してください。 この例の場合は、IF 関数を使って、C2 セルから C6 セルの中で 70 以 上の数値を探して、それに対応する C2 セルから C6 セルまでのデータを AVERAGE 関数を使って平均するようにしています。IF 関数の中身（青 字部分）の意味としては、 「C2 セルから C6 セルの中で 70 以上の数値があれば、C2:C6 という範 囲を返しなさい。そうでなければ NULL(空白)を返しなさい。」という意 味になります。 計算を順番に追っていきますと、まず、C2 セルの値が 70 以上かどうか チェックされ「53」であることがわかると「FALSE」(ゼロという意味) が返されます。
14 次に C3 セルの値が 70 以上かどうかチェックされ「61」であることが わかると「FALSE」が返されます。 その次に C4 セルの値が 70 以上かどうかチェックされ「78」であるこ とがわかると「TRUE」(1 という意味)が返されます。 同様に、C5 セルの値が 70 以上かどうかチェックされ「75」であるこ とがわかると「TRUE」が返され、C6 セルの値も「90」なので「TRUE」 が返されます。 そして最後にAVERAGE 関数により、TRUE のデータだけが平均され ます。 さて、これを踏まえて、「女性」という and 条件を加えて、完全な式を 完成させたいと思います。 先ほどの式の、IF 関数の部分（青字部分）を、 IF(and(C2:C6>=70,B2:B6="女"),C2:C6,"") としてもエラー値が返ってきます。配列関数の中では「and」や「or」は 使えないのです。 そこで、論理値をそのまま使用することになります。論理値というのは 前述の「TRUE」と「FALSE」のことで、「TRUE」=1，FALSE」=0 と 変換されます。 IF 関数で調べた結果、「TRUE」となったものだけが AVERAGE 関数 で計算されることになります。 通常、「and」条件は「*」で、「or」条件は「+」で表現することができ ますので、「and(C2:C6>=70,B2:B6="女")」 というのは、 「(C2:C6>=70)*(B2:B6="女")」と表現できます。 また順を追って確認していきましょう。
15 まず、C2 セルの値が 70 以上かどうかチェックされ「53」であること がわかると「FALSE」が返されます。次に B2 セルの値が「女」かどうか チェックされ「男」であることがわかると「FALSE」が返されます。か け算によって、0*0=0 が返され、AVERAGE 関数の対象から外れます。 そして次に、C3 セルの値が 70 以上かどうかチェックされ「61」であ ることがわかると「FALSE」が返されます。次に B3 セルの値が「女」か どうかチェックされ「女」であることがわかると「TRUE」が返されます。 かけ算によって、0*1=0 が返され、AVERAGE 関数の対象から外れます。 そして次に、C4 セルの値が 70 以上かどうかチェックされ「78」であ ることがわかると「TRUE」が返されます。次に B4 セルの値が「女」か どうかチェックされ「女」であることがわかると「TRUE」が返されます。 かけ算によって、1*1=1 が返され、AVERAGE 関数の対象となります。 同様に、次のC5 セル，B5 セルについては、 「TRUE」*「FALSE」=「FALSE」となり、AVERAGE 関数の対象から 外れます。 C6 セル，B6 セルについては、 「TRUE」*「TRUE」=「TRUE」となり、AVERAGE 関数の対象となり ます。 このようにして、AVERAGE 関数の対象が、「78」「90」の 2 つとなり、 「84」という計算結果が最終的に得られます。 さらに、「合格点以上」というand 条件を加えたら、どんな式になると 思いますか。 {=AVERAGE(IF((C2:C6>=70)*(C2:C6&">="&D2:D6)*(B2:B6= "女"),C2:C6,""))} と入力して、[CTRL]+[SHIFT]+[ENTER] が正解です。 青字の「～ &">="& …」という条件の書き方に注意してください。
16
セルを参照して比較する場合はこのように記述するルールになってい ます。
配列関数は、AVERAGE 関数のほか、SUM 関数，COUNT 関数，MAX
関数，MIN 関数，LARGE 関数，SMALL 関数などで使用できます。知っ
ておくと何かと便利ですよ。 最後に、配列関数式の作成方法をまとめておきます。 (1) IF 関数で、どんな条件を満たしたときに、どの範囲を使うかを表す式 を作成する。（まずは 1 つのセルで作ってみる。青字の部分はパターンな ので覚えてしまいましょう。） 例．IF((C2>=70)*(B2="女"),C 列の範囲,"") (2) 対象となるすべてのセル範囲に変更する。（このとき、範囲のセルの 数がすべて一致していることを確認する。） 例．IF((C2:C6>=70)*(B2:B6="女"),C2:C6,"") (3) IF 関数で作成した条件を満たしたときに使いたい関数を頭にくっつ ける。（IF 関数はカッコの中に入れる。） 例．=SUM(IF((C2:C6>=70)*(B2:B6="女"),C2:C6,"")) (4) [CTRL]+[SHIFT]+[ENTER]で確定する。 以上です。
