主な内容
１．文字列演算子「＆」
２．SUMIF 関数
３．SUM(IF )ネスト関数に配列数式を使用
４．配列数式とは
小切手帳のワークシートを作成して、収支を記録することができます。そのワークシート上に、 現在の収支を計算する数式を作成します。
取引預金高 引出額 残高 100000 50000 30000 120000
この例では、セル C2 に前回からの繰越し金額
セル A3 に最初の取り引き預金高
セル B3 に引き出し額を入力します。
最初の収支残高を計算するには、セル C3 に次の数式を入力します。
「 =SUM(C2,A3,-B3) 」
新規の取り引きを入力するときには、各取り引き後の残高を表示するセルにこの
数式をコピーします。
たとえば、セル [A1] に[上田]と入力され、セル [B1] に[春子]と入力されている場合
表示したいセルを選択して（例えばセル[C1]）次のように指定します。
と入力すると、姓が入力されている列と、名前が入力されている列の内容を連結することができます。
[&]は「アンバサンド」と読みます。
”上田春子” のように、姓名を姓と名前の順序で表示する場合に使用します。
たとえば、セル [A1] に[Brown]と入力され、セル [B1] に[Barry]と入力されている場合
表示したいセルを選択して（例えばセル[C1]）次のように指定します。（姓・名順）
「 =A1&B1 」
”Barry Brown” のように、名前と姓を逆にしてスペースを挿入するには、
表示したいセルを選択して（例えばセル[C1]）次のように指定します。（名・姓順）
2 つのセルのデータを結合して文字列を作成するには、& (アンパサンド) 文字列演算子を使用
します。
セルに入力した数値、日付、または時刻を文字列と結合するには、TEXT ワークシート
関数を使用します。
たとえば、セル [E1] に [請求日:] 、セル [F1] に[2004/8/10]という請求書の発行日が入力されて
いる場合、文字列 ”請求日: 平成16年8月10日” を表示するには次の数式を入力します。
なお、月日表示ですが、[F1]ではスラッシ[/]表示でしたが、連結後は平成年月日表示に改めてあります。
セルに入力されている数値を一定の割合で増加させることができます。
たとえば、セル [B1] に入力されている初期値を 5% ずつ増加させるには、次行のセル{B2}に次の数式を入力します。
「 =B1*(1+5%) 」
50 52.5 55.125000 57.881250 60.775313 63.814078
パーセンテージの値が、[A1] などの他のセルに入力されている場合は、セル{B2}に次のように指定します。
「 =B1*(1+$A$1) 」
5% 50 52.500000 55.125000 57.881250 60.775313 63.814078
セル F2 は絶対参照として参照されるため、この数式を他のセルにコピーしても参照先は変わりません。 (上記は、関数というより数学的解法でした。)
SUMIF ワークシート関数を使用すると、特定のセル範囲の値に基づいて別のセル範囲の合計値
を計算できます。
たとえば、セル範囲 B1:B9 に "Northwind" という値が入力されている
すべてのセルに対応するセル範囲 C1:C9 の値を合計するには、次の数式を入力します。
「 =SUMIF(B1:B9,"Northwind",C1:C9) 」
Northwind 1 Aaa 2 Bbb 3 Northwind 4 Ccc 5 Northwind 6 Ddd 7 Fff 8 Northwind 9 20
「エクセル フアイルを作りました」宜しかったらダウンロ−ドどうぞ
たとえば、セル範囲 B2:B9 に "りんご" という品目が入力され、セル範囲 C2:C9 に出荷先 "東京" が入力されている場合に、「りんご」且つ「東京」の２つの条件を満たす（AND条件）セル範囲 D2:D9 の値を合計するには、次の数式を入力します。
この数式は、「配列数式」という形式なので、ただ、[Enter]キーで確定するのではなく、[Ctrl+Shift]キーを押しながら[Enter]キーで確定します。すると、数式の両端が「 { } 」の括弧で囲まれます。
品目 出荷先 金額
○月 りんご 東京 1000000
りんご 神奈川 500000
みかん 埼玉 350000
ぶどう 千葉 300000
×月 りんご 東京 800000
みかん 神奈川 200000
りんご 埼玉 650000
ぶどう 千葉 400000
1800000
↓
数式「 {=SUM(IF((B2:B9="りんご")*(C2:C9="東京"),D2:D9))} 」
計算内容について
さくら うめ 2 =IF(A1="さくら",1+IF(B1="うめ",TRUE)) 0 =IF(A1="さくら",1-IF(B1="うめ",TRUE)) 1 =IF(A1="さくら",1*IF(B1="うめ",TRUE)) 1 =IF(A1="さくら",1/IF(B1="うめ",TRUE)) 犬 猫 2 =IF(A6="犬",TRUE)+IF(A7="猫",1) 0 =IF(A6="犬",TRUE)-IF(A7="猫",1) 1 =IF(A6="犬",TRUE)*IF(A7="猫",1) 1 =IF(A6="犬",TRUE)/IF(A7="猫",1) 4 =IF(A1="さくら",1+IF(B1="うめ",TRUE+IF(A6="犬",TRUE+IF(A7="猫",1)))) 7 =IF(A6="犬",TRUE)*7 狸 0 =0/IF(A14="狸",TRUE) 狸 FALSE =IF(A15="きつね","",FALSE) 狸 #DIV/0! =5/IF(A16="きつね","",FALSE)
ＡかつＢをクリアした金額の合計となると、AND 関数的用法になります。
（方法：その１）では[*]（アスタリスク)乗算を使用しましたが、この条件をIF 関数を繋げることで、下式のように解決しています。
品目 出荷先 金額
○月 りんご 東京 1000000
りんご 神奈川 500000
みかん 埼玉 350000
ぶどう 千葉 300000
×月 りんご 東京 800000
みかん 神奈川 200000
りんご 埼玉 650000
ぶどう 千葉 400000
1800000
↓
数式「 {=SUM(IF(B2:B9="りんご",IF(C2:C9="東京",D2:D9)))} 」
ちなみに、（愚かな実験の話です。）
IF 関数の[論理式]に「 AND(B2:B9="りんご",C2:C9="東京") 」とAND 関数を用い、
SUM 関数にネストさせ、下式にしましたが、戻り値は[0]でした。
数式「 {=SUM(IF(AND(B2:B9="りんご",C2:C9="東京"),D2:D9))} 」
そもそも、AND 関数の[引数1][引数2]の入力を終え IF 関数の数式パレットに戻した時の IF 関数の[論理値]は[FALSE]になっていました。
論理値が FALSE では、この段階で「ノーグッド」な話です。
それでも、こりずに、「 {=SUM(IF(AND(B2:B9="りんご",C2:C9="東京"),"",D2:D9))} 」と「偽の場合に「配列範囲」を指定したところ、総合計の[4200000]と戻りました。
「配列数式」では、[AND]や[OR] 関数は使いないという実例です。
どうしても、AND 関数を使いたかったら下記になりますが、合計は別途[Σ]を必要とし、２度手間になります。それをしないで済ますために「配列数式」が有るのでしょう。
（下式は配列数式ではありません。 Enter キーを押して確定します。）
品目 出荷先 金額 AND条件クリア値
○月 りんご 東京 1000000 1000000
りんご 神奈川 500000 0
みかん 埼玉 350000 0
ぶどう 千葉 300000 0
×月 りんご 東京 800000 800000
みかん 神奈川 200000 0
りんご 埼玉 650000 0
ぶどう 千葉 400000 0
↓
数式「 {=IF(AND(B2="りんご",C2="東京"),D2,0)} 」
セル範囲 B2:B9 に "りんご" または "ぶどう" という値が入力されている場合に、「りんご」または「ぶどう」の条件を満たす（OR 条件）セル範囲 D2:D9 の値を合計するには、次の数式を入力します。
「 =SUM(IF((B2:B9="りんご")+(B2:B9="ぶどう"),D2:D9)) 」
この数式も、「配列数式」という形式なので、ただ、[Enter]キーで確定するのではなく、[Ctrl+Shift]キーを押しながら[Enter]キーで確定します。すると、数式の両端が「 { } 」の括弧で囲まれます。
品目 出荷先 金額 ○月 りんご 東京 1000000 りんご 神奈川 500000 みかん 埼玉 350000 ぶどう 千葉 300000 ×月 りんご 東京 800000 みかん 神奈川 200000 りんご 埼玉 650000 ぶどう 千葉 400000 3650000
No.7 その２では[B列][C列]を参照して[該当するD列]の値の合計を求めました。
今回は、[B列][C列]を参照して「条件に合った行の数」の算出です。
次の数式は、セル範囲 B2:B9 に "りんご" という文字列を含むセルがあった場合に、その隣のセル
(セル範囲 C2:C9) に "東京" という文字列があるかどうかがチェックされ、その両方の文字列が
ある行の数を計算します。
「 =SUM(IF(B2:B9="りんご",IF(C2:C9="東京",1,0))) 」
この数式は配列数式です。Ctrl キーと Shift キーを押しながら Enter キーを押して入力します。
引数[1]と[0]について
数式パレットの「真の場合」の記入注釈に
「真の場合には[論理式]の結果が TRUE であった場合に返される値を指定します。」とあります。
ここでは、双方の条件を満たす「レコード」（行方向のデータ）が[TRUE]で１行に付き「１件」ですので、
[1]と入力します。
（１）TRUE は指定すれば、「0 以外の数値」を全て当てることができます。
（２）FALSE は指定すれば、「0 以外に空白」を当てることができます。
品目 出荷先 金額
○月 りんご 東京 1000000
りんご 神奈川 500000
みかん 埼玉 350000
ぶどう 千葉 300000
×月 りんご 東京 800000
みかん 神奈川 200000
りんご 埼玉 650000
ぶどう 千葉 400000
2 ----------→{=SUM(IF(B2:B9="りんご",IF(C2:C9="東京",1,0)))}
実行する前のご忠告
操作を間違えて、終了しようとしても「配列の１部を変更できません。」
この警告が出た場合、何をどうしようとしても、操作が不能になります。
(１)マウスでセルの選択ができなくなります。
(２)「言語バー」がおかしくなります。対処法のHPを開くにも開けません。
(３)エクスプローラの表示も常態でなく、下手にフォルダを開くと、中のフアイルが何十個も開いたりして散々です。
(４)これを解決するには、数式バーの[×]ボタンを押して解除します。
その方法です。
配列（行方向や列方向に連続した値を一纏めしたデータ）を参照し、
配列に含まれる値ごと（複数の）の計算結果を配列範囲（戻り値のセル範囲）に返す数式のことです。
複数の計算を行い、1 つまたは複数の結果を返す数式で、計算を1つの数式で行うことができます。
１．数式バーで
計算式を入力後、カーソルを数式バーでアクティブの状態にして
Ctrl キーと Shift キーを押しながら Enter キーを押します。
すると、中かっこ ({ }) の間に数式が自動的に挿入されます。
({ })括弧は、キーボードから入力しても無効です。必ず[Shift＋Ctrl]＋Enterキーで入力する必要があります。
２．数式パレットで
数式パレットの入力を終了して[ＯＫ]を押す段階で、[Shift＋Ctrl]キーを押しながら[ＯＫ]ボタンをクリックします。
最も分り易い代表的な配列数式「FREQUENCY 関数」の例で説明いたします。
範囲内でのデータの頻度分布を、縦方向の数値の配列として返します。
たとえば、この関数を使うと、試験の成績の範囲内に含まれる成績の頻度分布を計算することができます。
この関数では、値は配列として返され、配列数式として入力されます。
(注釈)度数分布を垂直配列で返します。
(注釈)データ配列：データ配列には度数分布を求めたい値の配列、または範囲を指定します。
(注釈)区間配列 ：区間配列には＜データ＞の値を区切るための区間配列、または範囲を指定します。
範囲内でのデータの頻度分布を、縦方向の数値の配列として返します。
たとえば、この関数を使うと、試験の成績の範囲内に含まれる成績の頻度分布を計算することができます。
この関数では、値は配列として返され、配列数式として入力されます。
データ配列 頻度調査の対象となるデータを含む配列またはセル範囲を指定します。
データ配列に値が含まれていないと、要素としてゼロ (0) を含む配列が返されます。
区間配列 データ配列で指定したデータをグループ化するため、値の間隔を配列またはセル範囲として指定します。
区間配列に値が含まれていないと、データ配列で指定した配列要素の個数が返されます。
下図使用例(1) (この使用例ではテストの点数が整数であると仮定しています。)
解説
FREQUENCY 関数を使うと、隣接するセル範囲が選択された後、そのセル範囲に配列数式として入力されます。
このセル範囲にデータの頻度分布が表示されます。
返された配列要素の個数は、区間配列の個数より 1 つだけ多くなっています。
この追加された配列要素には、最も高い間隔を超えた値の個数が返されます。
たとえば、3 つのセルに入力した 3 つの範囲 (間隔) の値をカウントする場合は、FREQUENCY 関数を 4 つのセルに入力します。
余分のセルには、3 つ目の間隔を超えたデータ配列の値の個数が返されます。
引数として指定した配列またはセル範囲に空白セルまたは文字列が含まれている場合、これらは無視されます。
計算結果が配列となる数式は、配列数式として入力する必要があります。
この数式が配列数式として入力されていない場合、単一の値 1 のみが計算結果として返されます。
----------------------------以上、MS−ヘルプより
使用例(2)
上図と同様ですが。
A B C D
1 氏名 国語 得点分布
2 山田太郎 80 範囲 人数
3 川田花子 75 0 0---→[D3:D9]に入力 {=FREQUENCY(B2:B11,C3:C9)}
4 草田一郎 100 50 2---→[D3:D9]に入力 {=FREQUENCY(B2:B11,C3:C9)}
5 木田一美 50 60 1---→[D3:D9]に入力 {=FREQUENCY(B2:B11,C3:C9)}
6 上田春子 70 70 3---→[D3:D9]に入力 {=FREQUENCY(B2:B11,C3:C9)}
7 中田夏男 60 80 2---→[D3:D9]に入力 {=FREQUENCY(B2:B11,C3:C9)}
8 下田秋子 65 90 1---→[D3:D9]に入力 {=FREQUENCY(B2:B11,C3:C9)}
9 天野和雄 40 100 1---→[D3:D9]に入力 {=FREQUENCY(B2:B11,C3:C9)}
10地脇智子 90
11人見賢治 65
COUNTIF ワークシート関数を使用すると、指定したセル範囲に含まれる特定の値の個数を計算できます。
たとえば、セル範囲 A1:A) の "Northwind" という文字列を含むセルの個数を計算するには、次の数式を入力します。
Northwind 1 Aaa 2 Bbb 3 Northwind 4 Ccc 5 Northwind 6 Ddd 7 Eeez 8 Northwind 9 4 -----------→ セル[A10]に入力 =COUNTIF(A1:A9,"Northwind")