Excelの関数について教えてください。

質問

Excelの関数について教えてください。

シート１に顧客表があります。
そこから検索をかけてシート２に該当する顧客を表示させたいです。
検索項目が一つならばVLOOKUPなどを使えばいいのですが、
検索に必要な項目が３つあります。

例えば
　　Ａ　Ｂ　Ｃ　Ｄ…
１　１　１　１　企業A
２　１　１　３　企業B
３　１　２　１　企業C

のような感じで1000くらいのデータが並んでいます。

シート２にて

　　Ａ　Ｂ　Ｃ　Ｄ
１　１　１　１　企業Ａ
２

それぞれのセルに１－１－１と入力するとD1に『企業A』と表示させ、
１－１－３と入力すれば『企業B』というようにA1~C3に入力する数値によりD1の表示を変えていきたいです。
そして該当がなければ『該当無し』と表示したいです。

この場合の関数は何を用いてどのような式を作ればいいのでしょうか？

関数に詳しい方、ご協力お願いいたします。

回答No.5

某HN クロメート(Chromate)（@CoalTar） · ベストアンサー

Excelの関数について教えてください。
http://okwave.jp/qa/q5934288.html
回答ナンバー3 CoalTarです。先の質問であるこちらの質問とリンク先の質問とどこが違うのでしょうか？

でリンク先の回答の説明がいまいちだったのでこちらで修正します

各番号(A:C列)の桁数は3桁まで
E1セルに =A1*10^6+B1*10^3+C1
フィルハンドルダブルクリック
E列非表示

J1セルに=IF(ISNA(MATCH(G1*10^6+H1*10^3+I1,E:E,0)),"該当なし",
INDEX(D:D,MATCH(G1*10^6*H1+10^3+I1,E:E,0)))
G:J列を切り取って別シートへ貼り付け

添付図参照、VLOOKUP関数はINDEX/MATCH関数で代用できます。
ちなみに1行目はタイトルを使ったほうが、並べ替えが簡単にでき、抽出や集計ができるので便利ですよ。

で、上記をふまえて、作業列を使わない数式。
=IF(ISNA(MATCH(G2*10^6+H2*10^3+I2,
MMULT(A$1:INDEX(C:C,COUNT(C:C)),10^{6;3;0}),0)),"該当なし",
INDEX(D:D,MATCH(G2*10^6+H2*10^3+I2,
MMULT(A$1:INDEX(C:C,COUNT(C:C)),10^{6;3;0}),0)))

列Cの数値の個数によって範囲を可変にしています。
タイトルを1行入れた場合のセル範囲はA$2:INDEX(C:C,COUNT(C:C))とします。
MMULTは行列積を返します

回答No.4

KURUMITO

シート2のＤ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNT(A1:C1)<>3,"",IF(SUMPRODUCT((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1000=B1)*(Sheet1!C$1:C$1000=C1)*(ROW(Sheet1!A$1:A$1000)))=0,"該当なし",INDEX(Sheet1!D$1:D$1000,SUMPRODUCT((Sheet1!A$1:A$1000=A1)*(Sheet1!B$1:B$1000=B1)*(Sheet1!C$1:C$1000=C1)*(ROW(Sheet1!A$1:A$1000))))))

回答No.3

nayuta_lot

こんにちは
検索する項目を３つ繋げてＶＬＯＯＫＵＰを使用したらどうでしょうか？

Ａ　Ｂ　Ｃ　Ｄ　Ｅ
１　１　１　１　企業A
２　１　１　３　企業B
３　１　２　１　企業C

というデータがあるなら、一番左の列に１列挿入して

　　　Ａ　　　Ｂ　Ｃ　Ｄ　Ｅ　 Ｆ
１-１-１-１　１　１　１　１　企業A
２-１-１-３　２　１　１　３　企業B
３-１-２-１　３　１　２　１　企業C

Ａには、

=B2 & "-" & C2 & "-" & D2 & "-" & E2

のようにしてコードを連結して、最終行までコピーします。
検索する方のシート２には

　　Ａ　Ｂ　Ｃ　Ｄ
１　１　１　１　企業Ａ　←　Ｄ列に

=IF(ISNA(VLOOKUP(A1&"-"&B1&"-"&C1&"-"&D1,Sheet1!$A:$F,6,0)),
"該当なし",VLOOKUP(A1&"-"&B1&"-"&C1&"-"&D1,Sheet1!$A:$F,6,0))

のように入れます。

データベース側のキーを連結して、検索する側でも連結キーを作って検索
すれば、VLOOKUPを使用することができます。

まぁ、一案ですが、ご参考まで

お礼

ありがとうございます。

コードをすべて繋げて記入しまえばやりやすいのですが、地域コードなどで残念ながら並び替えなどをするときに困るらしくできないんです(+ω+)

フォームもなるべく崩すなと上司にキツく言われているので挿入もできるだけ使わずに、今あるデータに数式のみを用て解決できないか模索中なんです。

回答はとても参考になりました！
次回に自分がフォームから作るときは利用させて貰います(●^∀^●)
ありがとうございました！

回答No.2

tom04

No.1です！

作業列を使わない方法をご希望だということなので・・・
少し数式は長くなりますが・・・

前回の表を使わせてもらって
Sheet2のD2セルに

=IF(COUNTBLANK(A2:C2),"",IF(SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2:$B$1000=B2)*(Sheet1!$C$2:$C$1000=C2))=0,"該当なし",INDEX(Sheet1!$D$2:$D$1000,SUMPRODUCT((Sheet1!$A$2:$A$1000=A2)*(Sheet1!$B$2:$B$1000=B2)*(Sheet1!$C$2:$C$1000=C2)*ROW($A$1:$A$999)))))

としてみてください。
この場合は作業列は必要ないのですが、
私個人的には作業列を使って、数式の短い方をおススメします。

他にもっと簡単な方法があるかもしれません。

今はこの程度しか思い浮かびません。
以上、参考になれば幸いです。m(__)m

回答No.1

tom04

こんばんは！
一例です。
↓の画像で説明させていただきます

Sheet1に作業用の列を設けています。
作業列E2セルを
=A2&B2&C2
として、フィルハンドルの（＋）マークでダブルクリック、又はオートフィルで下へコピーします。

そして、Sheet2のD2セルに
=IF(COUNTBLANK(A2:C2),"",IF(COUNTIF(Sheet1!$E$2:$E$1000,A2&B2&C2)=0,"該当なし",INDEX(Sheet1!$D$2:$D$1000,MATCH(A2&B2&C2,Sheet1!$E$2:$E$1000,0))))
という数式を入れオートフィルで下へずぃ～～～！っとコピーすると
画像のような感じになります。

数式はSheet1の1000行目まで対応できるようにしています。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

お礼

ありがとうございます！！
大変参考になりました＾＾
画像で説明していただいたのでかなりわかりやすかったです！！

できれば残存のデータのみで行う（作業例の欄を作らずに）ことって可能でしょうか？
方法があれば教えていただきたいです＾＾