複数条件に合う文字列をカウントする関数

質問

現在、Excel2010を使って、タッチパネルを想定した処理表を作っているのですが、
関数に関してわからないことがあります。

ランダムに配列された項目(画像右、2行目のア～エ)と、その下にある文字列（A+～B）の右表から、
項目ごとに文字列をカウントして左の表に移したいです。

項目（ア～エ）の数やその順番はランダムに増減したり入れ替わったりするので、
関数によって項目名と文字列を一致するものを探し、それをカウントしたいのですが、
どのような関数を使えばこのような複数条件に合う文字列をカウントできるのかわかりません。

もしご存知の方がいれば、ご教授頂けないでしょうか？よろしくお願いします。m(_ _)m

回答No.6

kagakusuki · ベストアンサー

回答No.4を書いている間に、追加の御要望が来ておりました。

>表の右の項目の上に数値を置き、その数値の分、カウントされた個数を乗算し、その結果を左の表に出すことは可能でしょうか？

　「"カウントされた個数に"乗算」という御説明の内容が今一つ不明瞭です。
　カウント数が2以上の場合には、表の右の項目の上の該当する列の数値を全て掛け合わせた値を、カウントされた個数に乗ずるのでしょうか？
　それとも、表の右の項目の上の該当する列の数値の合計値を、カウントされた個数に乗ずるのでしょうか？

　恐らく質問者様は後者の意味で仰っておられるのではないかと思いますが、それならばA2セルに入力する関数を次の様な関数にされると良いと思います。

=SUMIFS($U$1:$AB$1,$U$2:$AB$2,INDEX($A$1:$T$1,MATCH(CHAR(1),$A$1:A$1,-1)),$U3:$AB3,A$2)

或いは

=SUMPRODUCT(($U$2:$AB$2=INDEX($A$1:$T$1,MATCH(CHAR(1),$A$1:A$1,-1)))*($U3:$AB3=A$2)*$U$1:$AB$1)

お礼

親切に何度も回答頂き、感謝します。
質問の内容が不明瞭で失礼しました。質問としては後者の方で間違いないです。
回答頂いた関数を入力したところ、目的の結果を算出することができました。
これで目的のシステムの構築が大きく進みそうです。

まだ提示された関数の一つ一つの意味を理解していないのですが、
試行錯誤して自分なりに別のシステムでも応用できるよう頑張ってみたいと思います。

kagakusukiさんとMarcoRossiItalyさんのどちらの関数でも目的は達成できたので、
できればお二人にベストアンサーを捧げたい気持ちなのですが、
今回は様々なケースに対応した、きめ細やかな回答をして頂いたkagakusukiさんに
ベストアンサーを捧げたいと思います。本当にありがとうございました！m(_ _)m

回答No.5

MarcoRossiItaly

＞表の右の項目の上に数値を置き、その数値の分、カウントされた個数を乗算し、……

A3　=sumIFS($U$1:$AB$1,$U$2:$AB$2,OFFSET($A$1,0,5*INT((COLUMNS($A3:A3)-1)/5)),$U3:$AB3,A$2)

No.3 の数式中、COUNTIFS を SUMIFS 関数に書き換え、第 1 ～第 4 引数をそれぞれ第 2 ～第 5 引数とし、新たな第 1 引数として $U$1:$AB$1 を指定するだけです。

お礼

わがままな要望でしたが、素早い回答有難うございます！m(_ _)m
自分にとっては複雑な関数でしたが、MarcoRossiItalyさんの関数を入力したところ、
思い通りの結果を算出することができました！素晴らしくて感動しました！
画像や数式の解説もわかりやすく、とても参考になりました。ありがとうございました。

回答No.4

kagakusuki

尚、左側の1行目に入力されている「ア」～「エ」の各項目が、何れも5列ずつ1組になっている場合には、次の様な関数でもOKです。

=COUNTIFS($U$2:$AB$2,INDEX($A$1:$T$1,INT((COLUMN()-COLUMN($A$1))/5)*5+1),$U3:$AB3,A$2)

或いは

=SUMPRODUCT(($U$2:$AB$2=INDEX($A$1:$T$1,INT((COLUMN()-COLUMN($A$1))/5)*5+1))*($U3:$AB3=A$2))

お礼

様々なパターンに対応した関数を記述いただき、感謝します。
ベストアンサーの関数で目的は達成できたのですが、
この関数も参考に今後エクセルを使いこなしていきたいです。

回答No.3

MarcoRossiItaly

A3　=countifs($U$2:$AB$2,offset($A$1,0,5*int((columns($A3:A3)-1)/5)),$U3:$AB3,A$2)

A3 セルをコピーし A3:T17 のセル範囲に貼り付け

お礼

具体的な例示、わざわざ表まで作って頂いて大変ありがとうございます。
とてもわかりやすいです! m(_ _)m

早速、自分が作りたいシステムに取り込んでみたところ、
見事、動作することができました。
kagakusukiさんとは違う関数ですが、同じ答えが導き出せるんですね。

下のkagakusukiさんのコメントにも記載していますが、
もし、よろしければで良いのですが、
下の画像のような関数というのは作ることができるのでしょうか？

http://uploda.cc/img/img529adf8cde847.jpg

表の右の項目の上に数値を置き、その数値の分、カウントされた個数を乗算し、
その結果を左の表に出す、ということをしたいです。
何度か試行錯誤してみたのですが、どのような計算式を用いれば良いのかわからず、
立ち往生しています。

回答No.2

kagakusuki

因みに、ＥｘｃｅｌのバージョンがＥｘｃｅｌ2007よりも前のバージョンの場合にはCOUNTIFS関数を使う事が出来ませんので、その様な場合にも使う事が出来る関数は次の様な関数となります。

=SUMPRODUCT(($U$2:$AB$2=INDEX($A$1:$T$1,MATCH(CHAR(1),$A$1:A$1,-1)))*($U3:$AB3=A$2))

お礼

ありがとうございます。
Excelのバージョンを記載していませんでしたが、Excel2010を使用していたので、
下の関数で問題なく動作することができました。ご丁寧にありがとうございました。

回答No.1

kagakusuki

今仮に、添付画像の例の中で赤色の地色の中に白文字で「ア」と入力されているセルがA1セル～E1セルを結合したセルであり、添付画像の右側にある黒色の地色の中に白文字で「ア」～「エ」の文字がランダムに表示されているセルがU2セル～AB2セルであるものとします。

　まず、A3セルに次の関数を入力して下さい。

=COUNTIFS($U$2:$AB$2,INDEX($A$1:$T$1,MATCH(CHAR(1),$A$1:A$1,-1)),$U3:$AB3,A$2)

　次に、A3セルをコピーして、カウント数を表示させたい全てのセルに貼り付けて下さい。（貼り付ける際には、範囲選択して一括して貼り付けると作業が楽だと思います）

　尚、A1～T1の範囲に入力されている「ア」～「エ」のデータが、実際の場合には文字列ではなく、数値データが入力されている場合には、上記の関数の中の

MATCH(CHAR(1),$A$1:A$1,-1)

の部分を

MATCH(9E+307,$A$1:A$1)

に置き換えた関数として下さい。

お礼

とてもわかりやすい解説と、具体的な関数の例をありがとうございます。
早速、目的のファイルに関数を組み込んで試行錯誤してみたところ、
おかげさまで目的にあった関数を作ることができました。ありがとうございました。
回答のためにわざわざエクセルファイルも作成して頂き、頭が下がります。

ここまで回答して頂いて、大変申し訳ないのですが、
あともう一点改善したい点が出てきました。もしお時間あれば教えて頂けないでしょうか？

下の画像なのですが、
http://uploda.cc/img/img529adf8cde847.jpg
表の右の項目の上に数値を置き、その数値の分、カウントされた個数を乗算し、
その結果を左の表に出すことは可能でしょうか？
何度か試行錯誤してみたのですが、どのような計算式を用いれば良いのかわからず、
立ち往生しています。

もしお時間がありましたら、アドバイスをお願いします。
（ただ、この問題は自分から新しくトピックを増やしてしまっているので、
回答が難しければ一旦kagakusukiさんをベストアンサーとして質問を閉じ、
また新しく質問したいと思います。）