セルに埋め込まれた文字列を式として利用したい

質問

品名　　　　　　|ランク
------------|-----------
みかん　　　　　|A
いちご　　　　　|B
みかん　　　　　|A
みかん　　　　　|B
りんご　　　　　|A
りんご　　　　　|B
いちご　　　　　|B
りんご　　　　　|B

品名選択：
--------------------
|全て選択　　　　　|▼| ←A15セル
--------------------
|いちご　　　　　　　|
|みかん　　　　　　　|
|りんご　　　　　　　|
|いちご＆みかん　　　|
----------------------

上記のようなExcelの表があります。
表の下には品名選択のコンボボックスが用意されています。

品名選択で選択されたもので、ランクがＡのものを数えたいと思っていますが、
品名選択の方法が複雑なため、SUMPRODUCTで表すと式がとても長くなってしまいます。

=SUMPRODUCT((B2:B9="A")*((A15<>"全て選択")*(A15<>"いちご＆みかん")*(A2:A9=A15)+(A15="全て選択")+((A15="いちご＆みかん")*((A2:A9="いちご")+(A2:A9="みかん")))))

式をもう少し簡潔にしたいと思い、以下のように品名が選択された時に隣のセル(B15)に式を表示するようにし、

--------------------
|全て選択　　　　　|▼|　(A15="全て選択")
--------------------
|いちご　　　　　　　|　(A2:A9=A15)
|みかん　　　　　　　|　(A2:A9=A15)
|りんご　　　　　　　|　(A2:A9=A15)
|いちご＆みかん　　　|　((A2:A9="いちご")+(A2:A9="みかん"))
----------------------

=SUMPRODUCT((B2:B9="A")*(B15))としてみたのですが「#VALUE」となってしまい計算されません。
(=SUMPRODUCT((B2:B9="A")*INDIRECT(B15))も試してみたのですが、出来ませんでした…うーん？？）
コンボの隣にセットした文字列を数式の一部として使用する事は出来るのでしょうか？
出来る場合、方法を教えて下さい。

よろしくお願いします。

回答No.5

mshr1962 · ベストアンサー

No.1です。すみません間違えました
=SUMPRODUCT((B2:B9="A")*(ISNUMBER(FIND(A2:A9,A15,1))+(A15="すべて選択")))
が正解です。

お礼

お礼が遅くなってしまい申し訳ありません。

mshr1962さん、回答ありがとうございました。
回答の式を使用させていただきます♪

回答No.6

keithin

データメニューのピボットテーブルレポートを使ってみると，複雑な数式を全く使わずに必要な集計を自在に取り出すことができます。（複雑な数式大好きの時は，スルーしてください）

手順：仮にご利用のExcelが2003までの場合
Ａ：Ｂ列を列選択してピボットテーブルレポートの作成を開始する
　行に品名
　列にランク
　データ範囲にランクを投入
いまＡだけ集計したいのでランク▼の選択肢からＡのみチェック
品名▼の選択肢から集計したい品目（添付図ではみかんとリンゴ）を選択すると，２つと１つで合計で３つありますと即座に結果が得られます。

もしもこの集計結果を更に何かの計算で二次利用したいときは，GETPIVOTDATA関数を使います。

お礼

お礼が遅くなってしまい申し訳ありません。

keithinさん、回答ありがとうございます。
ピボットテーブルは、使った事なかったのですが便利ですね～∑(＠_＠)！
色々試してみたのですが、欲しいデータがすぐに出て嬉しくなりました（笑）

複雑な数式を作ったはいいけど、修正する時に「…はて？」と思う事が多いので、
これからは積極的にピボットテーブルを使ってみたいと思います。

ありがとうございました♪

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＃２です
>いただいた回答の式が理解できなくて…解析中です(；・∀・)
空白を条件範囲に入れてしまうと、すべて数えてしまうようです。
(for XL2003)

=DCOUNT(A1:B9,,D1:INDEX(E2:E7,COUNTA(D2:D7)))
数式は内側から検証します

COUNTA(D2:D7)
品名を入力した個数です

INDEX(E2:E7,COUNTA(D2:D7))
品名が2個入力されていれば
INDEX(E2:E7,2)
つまり E3セルを返します

INDEX関数は、セル参照を返す性質があります。
よって
D1:INDEX(E2:E7,COUNTA(D2:D7))
は
D1:E3 を返します(品名2この場合)

=DCOUNT(A1:B9,,D1:E3)
はデータベース関数
=DCOUNT(Database,フィールド,Criteria)
フィールドは特に必要ないので省略。
Criteriaは条件表の書いてあるセル範囲。

# ちなみにセルに設定する入力規則から作るリストは、
# 「ドロップダウンリスト」と呼びます。

お礼

CoalTarさん、丁寧に解説までして下さりありがとうございます。

いただいた説明で、どういう式なのか理解できました。感謝です！
DCOUNT関数、これからガンガン使えそうですヽ(´ー｀)ノ

→式中にE1セルの参照が無かったので、横着して「ランク」ってタイトルを付けずにテストして
　ました。
　良く分からない結果が返ってきて…「？」だったのですが、横着しちゃだめですね！

# 「ドロップダウンリスト」と呼びます。
これから気をつけます。紛らわしくしてしまってすいません（汗）

回答No.3

mshr1962

No.1です。
そういえば"全て選択"もあるのでしたね。
=SUMPRODUCT((B2:B9="A")*ISNUMBER(FIND(A2:A9&"全て選択",A15,1)))

お礼

mshr1962さん、何度も回答くださり、ありがとうございます。

いただいた式ですが、私の環境だとなぜかうまくいかなかったので、少し直して↓の式にしました。
=SUMPRODUCT((B2:B9="A")*((A15="全て選択")+ISNUMBER(FIND(A2:A9,A15,1))))

それでも、最初の式よりは断然、簡潔になりました。
ありがとうございます♪

回答No.2

某HN クロメート(Chromate)（@CoalTar）

別の条件表を作ってはどうですか？
=DCOUNT(A1:B9,,D1:INDEX(E2:E7,COUNTA(D2:D7)))
別表において品名を上から順に入れること

# コンボボックスを作れるなら、マクロに挑戦しても良いかもしれませんね

お礼

CoalTarさん、お忙しい中、時間を割いて考えて下さりありがとうございます。

私のお勉強不足で、いただいた回答の式が理解できなくて…解析中です(；・∀・)
取り急ぎ、お礼をと思いまして…

回答No.1

mshr1962

B15のセルを使わないで、下記の数式でできませんか？
=SUMPRODUCT((B2:B9="A")*ISNUMBER(FIND(A2:A9,A15,1)))

お礼

mshr1962さん、回答ありがとうございます。

教えていただいた式だと、「りんご＆みかん」とか「りんご＆いちご」等が増えても対応できますね。
| 　 ´ω｀|φメモメモ

でも、「全て選択」に対応できないみたいです…（悲）
「りんご＆みかん＆いちご」みたいにすればいいのですが、実際は商品数が多いのです。
説明が足りてなくてすいません。

ありがとうございます。