月別アーカイブ: 2006年9月
条件に合致するデータのみ、たし算する（配列関数）（2）
2006年9月18日 よく使う関数
[Sheet1]
| A | B | C | |
| 1 | 氏名 | 得点 | 性別 |
| 2 | Ａさん | 53 | 男 |
| 3 | Ｂさん | 61 | 女 |
| 4 | Ｃさん | 89 | 女 |
| 5 | Ｄさん | 75 | 男 |
という表があったとき、配列関数を使用して、女性の70点以上の点数だけ たし算する場合は、
(1)B6セルを選択。
(2)=SUM(IF((B2:B5>=70)*(C2:C5="女"),B2:B5,"")) と入力して、CTRL+SHIFT+ENTER。
という手順で行うことは前回説明した通りですが、まずは {=SUM(IF(B2:B5>=70,B2:B5,""))} と、「女性」という条件を除いて式を組み立てました。今回はこの「女性」というand条件を加えてみて、式を完成させます。
先ほどの式の、IF関数の部分（青字部分）を、IF(and(B2:B5>=70,C2:C5="女"),B2:B5,"") としてもエラー値が返ってきます。
配列関数の中では「and」や「or」は使えないのです。
そこで、論理値をそのまま使用することになります。論理値というのは前述の「TRUE」と「FALSE」のことで、「TRUE」=1，FALSE」=0と変換されます。IF関数で調べた結果、「TRUE」となったものだけがsum関数で計算されることになります。通常、「and」条件は「*」で、「or」条件は「+」で表現することができますので、「and(B2:B5>=70,C2:C5="女")」 というのは、「(B2:B5>=70)*(C2:C5="女")」と表現できます。
計算を順番に追っていきますと、まず、B2セルの値が70以上かどうかチェックされ「53」であることがわかると"FALSE"(→ゼロという意味)が返されます。
次にC2セルの値が「女」かどうかチェックされ「男」であることがわかると"FALSE"が返されます。かけ算によって、0*0=0が返され、sum関数の対象から外れます。
そして次に、B3セルの値が70以上かどうかチェックされ「61」であることがわかると"FALSE"が返されます。次にC3セルの値が「女」かどうかチェックされ「女」であることがわかると"TRUE"(→1という意味)が返されます。かけ算によって、0*1=0が返され、sum関数の対象から外れます。
そして次に、B4セルの値が70以上かどうかチェックされ「89」であることがわかると"TRUE"が返されます。次にC4セルの値が「女」かどうかチェックされ「女」であることがわかると"TRUE"が返されます。かけ算によって、1*1=1が返され、sum関数の対象となります。
同様に、次のB5セル，C5セルについては、"TRUE"*"FALSE"="FALSE"となり sum関数の対象から外れます。
このようにして、「89」という計算結果が最終的に得られます。
配列関数は、sum関数のほか、average関数，count関数，max関数，min関数，large関数，small関数などで使用できます。知っておくと何かと便利ですよ。
<<ガイドシートURL>>
http://www.tcat.ne.jp/~ryu/PLE/Kansu/39.pdf
条件に合致するデータのみ、たし算する（配列関数）
2006年9月17日 よく使う関数
草加駅前の「CAFE de ARK」です。
ミルクレープがおいしいです。
草加って意外とゆっくりできるカフェがたくさんあってよく行ってます。
（‐＾▽＾‐）
ﾟ･*:.｡..｡.:*･ﾟﾟ･*:.｡..｡.:*･ﾟ ﾟ･*:.｡..｡.:*･ﾟﾟ･*:.｡..｡.:*･ﾟ ﾟ･*:.｡..｡.:*･ﾟﾟ･*:.｡..｡.:*･ﾟ
さて、例えば、
[Sheet1]
| A | B | C | |
| 1 | 氏名 | 得点 | 性別 |
| 2 | Ａさん | 53 | 男 |
| 3 | Ｂさん | 61 | 女 |
| 4 | Ｃさん | 89 | 女 |
| 5 | Ｄさん | 75 | 男 |
という表があったとき、配列関数を使用して、女性の70点以上の点数だけ たし算してみましょう。
(1)B6セルを選択。
(2)=SUM(IF((B2:B5>=70)*(C2:C5="女"),B2:B5,"")) と入力して、CTRL+SHIFT+ENTER。
{=SUM(IF((B2:B5>=70)*(C2:C5="女"),B2:B5,""))} と表示され、B6セルに 89 が入ればOKです。ENTERキーのみでなく、CTRL+SHIFT+ENTERキーで確定する関数式を「配列関数」といいます。
配列関数を使うと、複数セルの処理が一つの式の中で一気に計算することができます。上の例では、
1．B2セルからB5セルの中から70以上の数値を探す。
2．C2セルからC5セルの中から「女」という文字列を探す。
3．B2セルからB5セルの中から条件を満たすものだけ足し算する。
という3つのことを一つの式の中で一気に計算しています。あまり見かけないと思いますので、ゆっくりとご説明。。
まずは「女性」という条件を除いて式を作ってみますと、
{=SUM(IF(B2:B5>=70,B2:B5,""))}
となります。「=SUM(IF(B2:B5>=70,B2:B5,""))」と入力した後、ENTERキーのみでなく、CTRL+SHIFT+ENTERキーで確定してください。
この例の場合は、IF関数を使って、B2セルからB5セルの中で70以上の数値を探して、それに対応するB2セルからB5セルまでのデータをsum関数を使って足し算するようにしています。IF関数の中身の意味としては、
「B2セルからB5セルの中で70以上の数値があれば、B2:B5 という範囲を返しなさい。そうでなければNULL(空白)を返しなさい。」という意味になります。
計算を順番に追っていきますと、まず、B2セルの値が70以上かどうかチェックされ「53」であることがわかると"FALSE"(→ゼロという意味)が返されます。
次にB3セルの値が70以上かどうかチェックされ「61」であることがわかると"FALSE"が返されます。
その次にB4セルの値が70以上かどうかチェックされ「89」であることがわかると"TRUE"(→1という意味)が返されます。
その次にB5セルの値が70以上かどうかチェックされ「75」であることがわかると"TRUE"が返されます。
そして最後にsum関数により、TRUEのデータだけが足し合わされます。
さて、ここで「女性」というand条件を加えてみて、次回 式を完成させたいと思います。
指定したデータが指定した範囲の何番目にあるのかを検索する（match関数）
2006年9月11日 よく使う関数
[Sheet1]
| A | B | C | D | E | F | |
| 1 | 番号 | 氏名 | ﾚﾍﾞﾙ | 難易度 | 得点 | 補正値 |
| 2 | 100 | Ｄさん | A | 難 | 45 | |
| 3 | 120 | Ａさん | B | 中 | 50 | |
| 4 | 130 | Ｅさん | C | 易 | 80 | |
| 5 | 140 | Ｃさん | B | 易 | 95 | |
| 6 | 150 | Ｂさん | C | 難 | 60 |
という表があったとき、次の[Sheet2]の「レベル-難易度別補正表」を使って各人の得点を補正します。
[Sheet2]「レベル-難易度別補正表」
| A | B | C | D | |
| 1 | A | B | C | |
| 2 | 難 | 50 | 40 | 30 |
| 3 | 中 | 30 | 20 | 10 |
| 4 | 易 | 10 | 5 | 0 |
例えば、Ｄさんの補正後の得点を求めるには、
[Sheet1]のF2セルに、=index(Sheet2!A1:D4,2,2) とindex関数を使用することは前回説明しましたが、大量にデータ処理する場合は、この「2,2」に相当する部分も関数で持って来て計算させる必要があります。
以下、match関数と組み合わせて、全員分の補正後得点を計算させてみたいと思います。
(1)[Sheet1]のF2セルに、=index(Sheet2!$A$1:$D$4,match(D2,Sheet2!$A$1:$A$4,0),match(C2,Sheet2!$A$1:$D$1,0)) と index関数の中に match関数を組み合わせる。
(2)[Sheet1]のG2セルに、=E2+F2 と入力する。
(3)F2セルからG2セルを範囲指定し、[編集(E)]→[コピー(C)]。
(4)F2セルからG6セルを選択し、[編集(E)]→[貼り付け(P)]。
前回説明した通り、index関数の( )内には、まずどこの範囲からデータを持ってくるのかを入れます。
上の例では、[Sheet2]の「レベル-難易度別補正表」からデータを持って来るので、その範囲「Sheet2!$A$1:$D$4」と入れます。(後でコピーするので「$」マークをつけて絶対参照にしておきます。)
次に「，」で区切って、その範囲の何列目から持ってくるのか（左から何番目か）を入れ、さらに「，」で区切って、その範囲の何行目から持ってくるのか（上から何番目か）を入れます。
ここで match関数を使用します。match関数は、指定したデータが 指定した範囲の何番目にあるのかを返します。
match関数の( )内には、まずどのデータを検索するのかを入れます。
上の例の最初の match関数では、まず難易度が[Sheet2]の「レベル-難易度別補正表」の何行目のデータかを知りたいので、「D2」と入れます。
次に「，」で区切って、どの範囲から持ってくるのかを入れます。
上の例では、[Sheet2]の「レベル-難易度別補正表」の左端の列からデータを探して来るので、その範囲「Sheet2!$A$1:$A$4」と入れ、さらに「，」で区切って「0」(ゼロ)を入れます。(お約束みたいなものです。)
これで「左から何番目のデータか」という部分が関数で表されましたので、次は同様にして「上から何番目のデータか」という部分を関数で表します。
これが2番目に出て来る match関数です。ここで、「レベル」が[Sheet2]の「レベル-難易度別補正表」の何列目のデータかを計算させています。
<<ガイドシートURL>>
http://www.tcat.ne.jp/~ryu/PLE/Kansu/38.pdf
