エクセルのセル毎に○とか○◇とか入力した場合の○の数を合計したい - Office系ソフト - 教えて！goo

『配列数式』は、複数のセル範囲をひとつのかたまり（配列）として算式で扱う方法です。
Helpを読んでもよく分かりませんね。私も書いてみました。

複雑な計算を行う場合、if関数を重ねたり、たくさんの関数を組み合わせたり、計算用のセル（テンポラリー）を使ったりします。そういう時に配列関数を使うと、簡単に済むことがあります。

プログラムでは、100回の繰り返しも1000回の繰り返しも同じように間単に記述できます。個人的には、ワークシート関数にこの繰り返し感覚を持ち込んだものが『配列数式』だと思っています。

例えば、
　A1～A10に個人名
　B1～B10に個人の時給
　C1～C10に個人の１日の勤務時間（毎日同じとします）
　D1～D10に個人の１ヶ月の勤務日数　　　　　　　　　　　とします。

この10人の１ヶ月の総給与は配列数式を使って、
　=SUM(B1:B10*C1:C10*D1:D10)　として出せます。

普通なら、個人の月の給与を　Bi×Ci×Di　で計算して、それをSumで合計します。

この配列数式の意味は、数学にある

　 10
　=Σ(Bi×Ci×Di)
　 i=1

を想像すると理解しやすいかもしれません。


また、２つ以上の条件に合致するデータの個数のカウントや合計計算ではCountIfやSumIfやデータベース関数を使ったりしますが、配列数式で簡単に計算できることがあります。

例えば、１行目から10行目までで、A列が『1』、B列が『女性』の個数は、

　=SUM(IF(A1:A10=1,IF(B1:B10="女性",1,0),0))

のようにして計算できます。これも
=Σ(IF(Ai=1,IF(Bi="女性",1,0),0))　と考えれば理解しやすいでしょう。

個人的には、1つのセルを正しく評価できる算式を作って、それを範囲に拡張しCtrl+Shift+Enterで登録します。今回の質問はこれに当たります。


また、ある条件下での順位（あればRankIf？）の計算。
n行おきのカウント・合計・・・・と使用方法は沢山あります。


しかし、見た目、複雑に感じたり、広い範囲に使うと計算に負荷がかかったりします。その辺の兼ね合いを見て使っていくのでしょうか。Helpや本にも余り載っていませんが、なれて使っていけば有効な手段になると思っています。

通報する

これは解説するよりも実際に目で見たほうが分かりやすいかもしれません。

例えば、nishi6さんの設定の通りで、B102:B200と縦に細長く領域を指定して、その状態でnishi6さんの式からsum部分を除いた=LEN(B$2:B$100)-LEN(SUBSTITUTE(B$2:B$100,"○",""))という式を数式窓に入力し、Ctrl+Shift+Enterとします。

すると、0（＝○がない）や1（＝○がある）が縦にずらっと並び、その中の一つのセルにカーソルを合わせると、{=LEN(B$2:B$100)-LEN(SUBSTITUTE(B$2:B$100,"○",""))}となっているはずです。これが配列数式で、これはB102:B200という領域の中で、例えば一番上のセルにはB2のセルの値を使って=LEN(B2)-LEN(SUBSTITUTE(B2,"○",""))、同様にその下のセルはB3、B4、…、B100を使って、同じ計算をしたものとして扱われます。

そして、nishi6さんの式に戻りますと、その縦に長い領域のSUMを取っているのですから、見かけ上は1つのセルに収まっているのですが、実際には配列数式として扱われるのです。

このような説明でよろしかったでしょうか？

通報する

「自信あり」チェックをつけておきながら、思いっきり問題の解釈を間違えていました。すみませんm(_ _)m
nishi6さんのおっしゃる通りです。

もしも○が2つなど同じ記号の複数入力がない、もしくは同じ記号の複数入力があっても1つとして計算したい場合には、=COUNT(FIND("○",B$2:B$100))として頂ければ結構です。
（数式を入力して、通常Enterキーを押すところでCtrl+Shift+Enterを同時に押すのも一緒です。）

通報する

１月がＢ列でデータは2行目から100行目まであるとします。

１月の『○』の数は、
　=SUM(LEN(B$2:B$100)-LEN(SUBSTITUTE(B$2:B$100,"○","")))
とします。これは配列数式なので、数式窓で入力し、登録する時に、Ctrl+Shift+Enter　とします。
『◇、□、△』も同様に算式を作ります。
１つのセルが『○△○□○』となっていたら、○は３個、△１個、□１個と計算します。


『○』について説明すると、
セル毎に『元の文字列の長さ』－『元の文字の○を消した文字列の長さ』を計算し、それを配列数式で加算しています。

通報する

COUNTIF関数を使って、例えば○の数を数えたいなら=COUNTIF(G$3:G$8,"○")のように指定してやればいいと思います。

通報する