エクセルのセル毎に○とか○◇とか入力した場合の○の数を合計したい

質問

Ａ　　　Ｂ　　Ｃ　　Ｄ
１　氏名　１月　２月　３月　～
２　Ａ　　　○　　○　　◇
　　　　　　◇　　◇　　△
　　　　　　□

３　Ｂ　　　□　　○　　◇
　　　　　　◇　　△

４　下にも実際は入力されている
　　　○計　１　　２　　０
　　　◇計　２　　１　　２
　　　□計　２　　０　　０
　　　△計　０　　１　　１

のように、一つのセル内に○～△の記号の何れかが毎月入力されています。
毎月下の計に、それぞれの記号の数を合計したいのですが関数でできるでしょうか？
一つごとのセルに一つ記号が入力されている場合は、今まで出来ていたのですが、今回のような場合大変行数が増えてしまう為、無理矢理一つのセルに入力しています

回答No.5

nishi6 · ベストアンサー

『配列数式』は、複数のセル範囲をひとつのかたまり（配列）として算式で扱う方法です。
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さんにはいつも大変お世話になっています
度々の懇切丁寧な回答ありがとうございました
すごく奥が深くまだまだ勉強不足で申し訳有りませんでした
これからも初心者の質問を投稿するかもしれませんが、今後ともよろしくご教授をお願いします

今回はほんとに有り難うがございました

回答No.4

take_777

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

例えば、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つのセルに収まっているのですが、実際には配列数式として扱われるのです。

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

お礼

お礼が遅れて申し訳有りません
度々の懇切丁寧な回答ありがとうございました
すごく奥が深くまだまだ勉強不足で申し訳有りませんでした
これからも初心者の質問を投稿するかもしれませんが、今後ともよろしくご教授をお願いします

今回はほんとに有り難うがございました

回答No.3

take_777

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

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

補足

深夜の回答度々申し訳ありませんでした
nishi6さんの数式でもできるし、take777の数式でも出来るし、すごいもんですね、感心するばかりです

大変申し訳ありませんが少し分からないことがありますので、もう少し教えてください

Enterキーを押すと、その数式は機能しないのに、Ctrl+Shift+Enterを同時に押すと、この数式が機能することが、私には始めての経験で、狐につままれたような感じです
どういう動作の時使うのでしょうか？

よろしくお願いします

回答No.2

nishi6

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

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

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

お礼

深夜の回答申し訳ありませんでした
nishi6さんにはいつもお世話になっています

こんなことが出来るんですね、私にとっては画期的なことです
大変ありがとうございました

今後ともよろしくお願いします

回答No.1

take_777

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

補足

大変深夜の回答ありがとうございます

一つのセルに○と仮に□が入力されている場合、○の数を
数値に変えれませんでした

明日でも、もう少し教えてもらえれば助かります
よろしくお願いします