VLOOKUP（SUMPRODUCT・・・の式ですが

質問

お世話になっています。
　先日こちらでエクセルのセット料金の作り方を問合せした者ですが、このときに以下のような式を教えていただきました。

=VLOOKUP(SUMPRODUCT((C5:F5="○")*10^{3,2,1,0}),F13:G27,2,FALSE)
（すみませんが下の表を参照願います。　顧客毎のセット料金を○のパターンで自動的に求めるという仕組みです）

　教えていただいた当時、式の中身は理解できませんでしたが「本やネットで調べれば理解できるだろう」と考えて、質問を締め切ったのですが後で色々と考えたり調べてみたりしてもついに式の中身が理解出来ませんでした。
特にSUMPRODUCT((C2:f2="○")*10^{3,2,1,0})の辺りがもう全然分りませんでした。（VLOOKUPの検索値だと思っているのですが）
　大変お手間をお掛けいたしますが再度ご教授下さいますようお願い致します。

回答No.1

grumpy_the_dwarf · ベストアンサー

vlookupの検索値に使うコードを、○の組み合わせから生成する配
列式ですね。累乗を使うのは思いつかなかったなぁ。

たとえば山田さん。C5:F5は○,○,○,""なので、(C5:F5="○")は
{TRUE;TRUE;TRUE;FALSE}となります。そこに、10^{3,2,1,0}という
配列をかけますが、まず四則演算でTRUEは1、FALSEは0として扱う
ということと、10^3=1000、10^2=100...を思い出すと、結果の配列
は{1000;1000;10;0}となり、sumproduct関数は配列の要素を全部足
し算するので、1110という数値が出てきますね。

(C5="○")*10^3+(D5="○")*10^2+(E5="○")*10^1+(F5="○")*10^0
をスマートに書いたんだと思ってください。

で、vlookup関数は1110というコードを検索して、2番目の\11,000
を返すというわけです。

お礼

お手数をお掛け致しました。

なるほど、この式には「配列」という仕組みが使われていたのですね。
それすら判っていなかったので混乱しまくっていました。
丁寧なご説明、本当に有難うございました。

補足

ご回答いただきありがとうございます。

TRUEは1、FALSEは0だったのですか。知りませんでした。
「＝TRUE+TRUE」と入力したら本当に「2」と出てきたので驚きました。
（VLOOKUP関数などでFALSEとか入力するのが面倒な時の省略に「0」を入れれば良い、位の認識しかありませんでした）

さて、
=VLOOKUP((C5="○")*10^3+(D5="○")*10^2+(E5="○")*10^1+(F5="○")*10^0,$F$12:$G$26,2,FALSE)
という風に置き換えてみるとようやく理解が出来たのですが、すみませんがまだSUMPRODUCT((C5:F5)*10^{3,2,1,0}という式が今ひとつ理解できません。
「*10＾{3,2,1,0}」ということは、もしかして
=VLOOKUP(SUMPRODUCT(((C5:F5="○")*10^3)*((C5:F5="○")*10^2)*((C5:F5="○")*10^1)*((C5:F5="○")*10^0)),F12:G26,2,FALSE)
なのか？と思って入力しましたが、やはりエラーでした。

大変理解が悪くて申し訳ございません。（質問の内容も意味不明かもしれませんが）
もし可能ならその辺をもう少し教えていただけますでしょうか。

回答No.4

_Kyle

#2です。#2の記述ですが、わたしの方こそ読み違えていました。

　>結果の配列は{1000;1000;10;0}となり、

というのは、
　【　10^{3,2,1,0}　】の「結果」ではなく、
　【　(C5:F5="○")*10^{3,2,1,0}　】　の「結果」ということですね。

#1さまのご解説に瑕疵はありません。大変失礼しました。
------------------------------------------------------------
なお、[数式の検証]風に書くとこんな感じになります。

SUMPRODUCT((C5:F5="○")*10^{3,2,1,0})
↓
※SUMPRODUCT((C5:F5="○")*{10^3,10^2,10^1,10^0})
↓
SUMPRODUCT((C5:F5="○")*{1000,100,10,1})
↓
SUMPRODUCT({TRUE,TRUE,TRUE,FALSE}*{1000,100,10,1})
↓
※SUMPRODUCT({TRUE*1000,TRUE*100,TRUE*10,FALSE*1})
↓
SUMPRODUCT({1000,100,10,0})
↓
1000+100+10+0
↓
1110

※をつけている部分は、計算の過程をイメージとして表したもので、
数式としては正しくありません。

ご参考まで。

お礼

数式の検証までしていただき本当に有難うございます。

SUMPRODUCT関数も中途半端な理解しか無かったところに配列とかが合わさってわけがわからなくなっていました。
エクセルって難しいですね（汗）

回答No.3

imogasi

解説は済んだ様なので、別のやり方を書きます。
料金が10^{3,2,1,0})のように規則性が無くても下記は大丈夫（料金表決定しだい）。
質問の例の「コード」列に注目した。
A2:G4
F列にコード列を作る。
AーーーーB－－－－CーーーーーD－－－－E－－－－F－－－－G
田中 ○ ○ ○ ○ 1111 12000
山田 ○ ○ ○ 1110 11000
山本 ○ ○ ○ 1011 10000
F2の式は=((B2="○")*1&(C2="○")*1&(D2="○")*1&(E2="○")*1)*1
＊１は数値化するために、入れている。
TRUE、FALSEの数値化
数字文字だけの文字列の数値化　の２つの面で。
検索表は
セット料金表のコードと料金列
J－ーーーーK列とする。
コード 料金
1111 12000
1110 11000
1011 10000
G列は平凡なVLOOKUP関数
G2は　=VLOOKUP(F2,$J$2:$K$10,2,FALSE)
式を下方向に複写。
質問者はVLOOKUP関数の第2引数に＄をつける癖をつけること。
＝＝＝＝
エクセルの関数で作業列を使うと、式が考えやすくなる例は、たびたび有る！

お礼

回答有難うございます。
この方法で表を作ってみました。
確かに作業列を置くと式の仕組みが分り易いようです。

＞質問者はVLOOKUP関数の第2引数に＄をつける癖をつけること。
　はい。十分に気をつけたいと思います。

有難うございました。

回答No.2

_Kyle

■エクセルでセット料金表を作りたいのですが
http://bekkoame.okwave.jp/qa5087371.html
の#2、問題の数式を書いた「元凶」です(^^;;;
#1さま、お手数をおかけして申し訳ありません。

#1さまのご解説で十分かと思い、票も入れたのですが、
行き掛り上出てまいりました。
-----------------------------------------
さて。

　>（VLOOKUPの検索値だと思っているのですが）

その通りです。
SUMPRODUCT((C2:f2="○")*10^{3,2,1,0})　の部分は、
「配列」という考え方(仕組)を利用して、VLOOKUPの検索値を作っています。

■日経PC21 / エクセル「配列数式」講座
http://pc.nikkeibp.co.jp/pc21/special/hr/
■配列研究室
http://www.clayhouse.jp/array/array.htm

基本的な考え方は#1さまが解説されている通り、
　・C列が○なら1000、○でなければ0
　・D列が○なら100、○でなければ0
　・E列が○なら10、○でなければ0
　・F列が○なら1、○でなければ0
として全部足し上げればコードになる、ということです。

1点のみ、これは単なるケアレスミスだと思いますけど、
　>結果の配列は{1000;1000;10;0}となり、
10^0 = 1　ですから
　>結果の配列は{1000;1000;10;1}となり、
ですね。

あのケースでは、素朴に
　=VLOOKUP(SUMPRODUCT((C5:F5="○")*{1000,100,10,1}),F13:G27,2,FALSE)
とした方が良かったかもしれません。

以上ご参考まで。
-----------------------------------------
なお、行き掛かり上出てきただけですので、
ポイント配分の際はご配慮ください。

この投稿のマルチメディアは削除されているためご覧いただけません。

お礼

この間は本当にお世話になりました。
今回もお手数を取らせることになりまして恐縮です。

補足説明、有難うございます。
これからがんばって「配列数式」を勉強しようと思います。