エクセル関数 Vlookupと配列数式（とsumif）の違い、利点不利点

質問

以下のような設定でvlookupと配列数式とifを組み合わせた場合の結果が異なる（ことがある？）んですが、原因を教えていただけないでしょうか。あと、配列数式のここが便利！（このvlookupの類似機能に限ったことではない）というものがあれば付随して教えてくださると助かります。

A1:A3 に順に1,2,3と（参照される側の）インデックスが入力されている。
B1:B3 はa,b,cと文字が入力されている
C1:C3 には2,2,3と（参照する側の）インデックスが入力されている。

C列のインデックスにより、Ａ列のインデックスに対応するＢ列の文字列を引いてきたいとき、普通はvlookupで、
＜セルD1＞=vlookup(C1,A1:B3,2,0)などとすると思います。
これを配列数式を用いて、
＜セルD1:D3＞= if(C1:C3=A1:A3,B1:B3,0)
として同じことをしようとしても、何故かD1セルのみ=0となってしまいます。何故でしょうか？配列数式にそれほどくわしくありませんが、利用価値が非常に高そうなのでわからないことを解明したいのです。
（sumifもタイトルにいれたのは、B1:B3が文字列ではなく数値ならば、同じことをsumifで表現できるから、それだけの理由です）

宜しくお願いします。

回答No.1

BLUEPIXY · ベストアンサー

＜セルD1:D3＞= {if(C1:C3=A1:A3,B1:B3,0)}
{}の部分は、
if(C1=A1,B1,0)
if(C2=A2,B2,0)
if(C3=A3,B3,0)
の様に展開されて
D1:D3にそれぞれ設定されます。つまり
D1=if(C1=A1,B1,0)
D2=if(C2=A2,B2,0)
D3=if(C3=A3,B3,0)
なので、
結果は、
D1=0
D2=b
D3=c
となります
Ａ１の値を１にしてみたりＡ２の値を１にしてみたりしてどうなるかみてみるといいかも・

お礼

BlUEPIXYさん、
ありがとうございます。完全に勘違いしていたようです。よくわかりました。
このようなvlookupと同様の結果をえるための関数は他に何かあるのでしょうか？何かあれば後学のために教えていただけないでしょうか。

回答No.4

BLUEPIXY

>vlookupと同様の結果をえるための関数は他に何かあるのでしょうか？
matchとindexあるいはoffsetを使ってvlookupと同等の結果が得られると思いますが、手間を考えるとvlookupをそのまま使った方がいいと思うので細部は省きます。
今回の
if(C1:C3=A1:A3,B1:B3,0)
のような形式で、
該当した場合は０以外の数値、そうでない場合は０になるような場合は、
sum(if(C1:C3=A1:A3,B1:B3,0))
（の配列数式）とすることで（配列としての数値が足されるので）
vlookupと同等として使うことができます。
また、sumproductを使えば、配列式を足し算する様な感じで（SHIFT+CTRL+ENTERで配列式として入力しないでも、配列を扱える）使えるので、便利です。

＞配列式のここが便利！
まあ、普通ですけど、通常だったら複数のセルが必要な（配列としての分作業用セルが必要となる）ところを１つのセルで済ませられるところですかね。

逆に不便なところは、
入力・編集時にSHIFT+CTRL+ENTERが必要。
範囲に入力したときは、部分だけ変更や削除ができない＜当たり前ですけど
どういう風に計算されるのかがわかりにくい。
配列を扱えない関数があることに注意しなければならない（例えば、SUMは配列を扱えるがCONCATENATEは扱えない）とか。
まあ、参考まで。

お礼

match, offsetに関して使ったことがなかったので、調べてみます。ありがとうございます。勉強になりました。

回答No.3

macchan1

基本的に配列数式の考えかたを勘違いしているようです。

まず、配列数式で入力する場合は以下のように絶対参照にして、Ctrl+Shift+Enterで確定する必要があります。

= IF($C$1:$C$3=$A$1:$A$3,$B$1:$B$3,0)

この部分の数式だけですとまだ配列しか返していませんのですべてのセルで同じ答えがかえります（1番最初の配列を計算）。
このとき、セルの中では以下のような計算をしています。

IF({FALSE;TRUE;FALSE},{"a";"b";"c"},0)

最終的にはIF関数で以下の列の配列を返しています。

{0;"s";0}

この配列は0、s、0という3つの値が縦に入力されていると同じ状況を意味します（E1:E3にこの数値を入れてある状態）。
したがって、この関数部分は、そのまま答えが返るのではなく、他の関数の検索範囲などとして使用することになります。

お礼

ありがとうございます。
{0;"s";0}

とありますが、"s"とはなんでしょうか？

回答No.2

BLUEPIXY

>Ａ１の値を１にしてみたりＡ２の値を１にしてみたりしてどうなるかみてみるといいかも・
は
Ｃ１の値を１にしてみたりＣ２の値を１にしてみたりしてどうなるかみてみるといいかも・
の間違いでした