データバー を例にとって解説します 数値の入っているセル範囲 G2:I16 をすべて選択した状態で [ ホーム ] 2

紙面の都合で書籍に載せられなかったコンテンツ ページ数の都合で載せられなかったコンテンツの中で 大切なものをいくつかご紹介します 本文の理解を助ける補足資料としてご利用いただければ幸いです 課長のためのエクセル再入門講座 異なるバージョンにおける手順の解説や 本文の補足に コラム を使っていますが 一部紙面の都合で載せられませんでした ここでは 特に 第 3 章の コラム を補足資料として取り上げます 条件付き書式についての補足資料 ( 第 3 章 ) 2007 以降のバージョンでは 条件付き書式の表現力が大幅にアップする新機能も追加されています これらについても少し触れておきましょう わざわざグラフ化して表示するまでもないけれども 数値の大きさを視覚的に表現したい そんなときに役立つのが データバー カラースケール アイコンセット という 3 つの新機能です 1

データバー を例にとって解説します 数値の入っているセル範囲 G2:I16 をすべて選択した状態で [ ホーム ] 2

タブ [ スタイル ] グループ [ 条件付書式 ] [ データバー ] から 青のデータバー を選択します データバーの条件設定は 上図 [ その他のルール ] から変更できます 他にも セルの色の使い分けによって表現する カラースケール 記号の使い分けによって表現する アイコンセット が用意されているので 次のように それらを組合せて使用することも可能です 3

また 条件付き書式 ではありませんが 2010 では 複数データの推移を セル内にミニグラフを表示することによって簡単に可視化できる スパークライン 機能も追加されました 使い方は簡単なので実際に試してみましょう (1) [ 挿入 ] タブ [ スパークライン ] グループ [ 折れ線 ] をクリック (2) [ スパークラインの作成 ] ダイアログで データ範囲 に セル範囲 G2:I16 を スパークラインを配置する場所の範囲 に セル範囲 J2:J16 を指定して [OK] ボタンをクリック 4

(3) セル内に小さな折れ線グラフが挿入されます [ スパークラインツール ] の [ デザイン ] タブからいろいろな設定ができるので お試しください 5

デスクワークを 3 倍効率化するテクニック 各章の終わりに ちょっと寄り道 コーナーを設けていましたが 紙面の都合で載せられませんでした ここでは 第 3 章と第 4 章の ちょっと寄り道 コーナーを補足資料として取り上げます (1)SUMPRODUCT 関数 ( 第 3 章 ) VLOOKUP 関数と IF 関数との組合せを紹介しましたが それに関連して よく似た使い方ができる SUMPRODUCT 関数 もご紹介しておきます 実は 前述 (P.30) の 4 上記の組合せやその他の方法で解決できる質問 の中でも 多いのは SUMPRODUCT 関数に関する質問です さっそく例を見てみましょう 図 1 をご参照ください 最近は 書籍の内容を CD で聴く オーディオブック やネットで配信する ネットブック などの形態も出てきました 図 1 では タイトルとその種類を入力すると 価格が表示される計算式を作成します まずは VLOOKUP 関数を使ってやってみます このように 検索条件が複数ある場合は 一番左端に新たに作業列を挿入して 検索条件を一つにまとめてしまいます A2 セルに =B2&C2 と B2 セルと C2 セルの値をくっつけて一つにし A8 セルまでコピペしておきます 6

G3 セルには =VLOOKUP(G1&G2,$A:$D,4,0) としておけば完成です 図 1 さて これを SUMPRODUCT 関数を使ってやってみましょう この場合 A 列のような作業列は不要です まずは 当関数の基本的な考え方から順次解説していきます SUMPRODUCT 関数は もともと SUM 関数 ( たし算 ) と PRODUCT 関数 ( かけ算 ) とが合体した関数ですので かけ算したものをたし合わせるときに使用します たとえば 図 2 のようなケースで C3 セルに =SUMPRODUCT(A1:A3,B1:B3) 1 と入れると 2 3+5 6+10 1=46 を返します 2 3,5 6,10 1 の結果をそれぞれ順番に覚えておいて 最後にすべてをたし算します カンマで区切った範囲どうしを上から順番にかけ算して その結果をたし合わせる関数 と覚えておいてください 7

図 2 これの応用形で たとえば A 列が 5 以下で かつ B 列が 3 以上の組合せが何個あるかをカウントすることができます これの答は A 列が 2,B 列が 3 の組合せ と A 列が 5,B 列が 6 の組合せ の 2 つですね これを求めるための関数式は =SUMPRODUCT((A1:A3<=5)*(B1:B3>=3)) 2 となります まず この式 2 の意味を解説します 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 が答になります 図で表すと次のようになります 8

(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) 3 となります この式 3 の意味を解説しましょう 式 2 との相違点は 式 2 のカッコ内の最後に,B1:B3 という範囲が追加されただけです 式 2 では 最終的に 1 1 0 という結果が返され それらがたし算されました 今度はそれに カンマで区切られ B 列の範囲が追加されています 式 1 で解説したように SUMPRODUCT 関数は カンマで区切った範囲どうしを上から順番にかけ算して その結果をたし合わせる関数 ですので 1 1 0 に B 列の値 3 6 1 が順番にかけ算され そ 9

の結果がたし合わされることになります よって 1 3+1 6+0 1=9 が答になります 図で表すと次のようになります (A1:A3<=5) * (B1:B3>=3), B1:B3 まとめますと 1. 複数条件を満たすものがいくつあるのか 個数をカウントするには SUMPRODUCT 関数の ( ) の中に 条件 1* 条件 2* 条件 3* と入れていきます 条件の記載の仕方は例示したように 列範囲 = 条件 の形式で記載します ( 条件は 30 個まで指定可能です ) 一般的に * は かつ, + は または を意味しますので これらを組み合わせて複雑な条件を指定することも可能です 2. 複数条件を満たす数値のトータルを求めるには 上記の条件の次にカンマで区切って たし算する列範囲を指定します これを踏まえて 図 1 の G3 セルにはどんな SUMPRODUCT 関数を入れればいいのかを考えてみましょう 10

図 1 再掲 セル範囲 B2:B8 の中から G1 セルと一致するデータを選択し かつ セル範囲 C2:C8 の中から G2 セルと一致するデータを選択し 両方の条件を満たすところの D 列データを持ってくれば ( たし合わせれば ) いい ということですね ( 今回の例では タイトル - 種類 - 価格 は一意的な関係ですので たし合わせる は 持ってくる と同義です ) ですので この例では G3 セルに =SUMPRODUCT((B2:B8=G1)*(C2:C8=G2),D2:D8) とすれば OK です 求める値が数値の場合は このように SUMPRODUCT 関数を使って持ってくる ( たし合わせる ) ことができます しかし 文字列を求める場合には ( たし合わせることができないため ) 使用できませんので その場合は前述したような 作業列を利用した VLOOKUP 関数を使いましょう (2) 配列関数 ( 第 4 章 ) 配列関数 という言葉をご存知でしょうか 11

複数セルを使って計算しないとできないはずの処理を 一つの式の中で一気に計算してしまいたいときに使用します 前の ちょっと寄り道 コーナーで紹介した 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] キーで確定する関数式を 配列関数 といいます 配列関数を使うと 複数セルの処理を一つの式の中で一気に計算するこ 12

とができます 上の例では 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 ( ゼロという意味 ) が返されます 13

次に 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=" 女 ") と表現できます また順を追って確認していきましょう 14

まず 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] が正解です 青字の ~ &">="& という条件の書き方に注意してください 15

セルを参照して比較する場合はこのように記述するルールになっています 配列関数は 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] で確定する 以上です 16