Excel2007 関数 表内の検索

質問

いつもお世話になります。

Excel2007で作成した表内で
A列　C列　E列　G列に年月日
B列　D列　F列　H列に商品名が入っています。

年月日と商品名が交互に10列くらいあるんですが、
別シートのA列の年月日から隣の列商品名を検索してB列に表示したいのですが
何かいい方法はありませんでしょうか
できれば関数でしたいのですが・・・・

別シートの表は
A列年月日(手入力で表示)、B列には検索で表示したい商品名の2列のみの表です。

回答No.3

kagakusuki · ベストアンサー

ANo.2です。
　先程の私の回答の方法に、条件付き書式を組み合わせますと、表の見栄えが良く成るかと思います。
　具体的な方法は以下の通りです。

Sheet2のC2セルを選択
　　↓
[ホーム]タブ内の「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択してください」欄の中にある[数式を使用して、書式設定するセルを決定]を選択してクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄に

=AND(C2="",$D2<>"")

と入力
　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[罫線]タブをクリック
　　↓
現れた「スタイル」欄の中にある[なし]をクリック
　　↓
「罫線」欄の中の上辺をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある、「ルール(表示順で適用)」欄が「数式:=AND(C2=...」、「適用先」欄が「=$C$2」となっている行の、「適用先」欄に入力されている内容をC2～「C列の（表中で）最下段の行のセル」の範囲にに変更する。
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

回答No.2

kagakusuki

今仮に、元データである「年月日と商品名が交互の列に並んだ表」が存在しているシートがSheet１であり、その表の1行目は「日付」や「商品名」といった項目名が並んでいて、実際のデータは2行目以下に入力されているものとします。
　又、検索結果を表示するシートはSheet2であるものとして、A列の２行目以下に指定の日付を入力するものとします。
　只、その際、Sheet1の表の中にA列で指定した日付が存在していなかったり、同じ日付が複数存在していたりする事も考えられますし、Sheet2のA列に同じ日付が複数入力される事も考えられますから、その様な場合には、Sheet2のA列で特定の日付が入力されている行数と、B列で表示しなければならない商品名の数が異なって来るという問題が発生します。
　例えば、Sheet2のA4セルに３月５日と入力されていて、A5セルに３月8日と入力されていた場合には、３月５日に対応する商品名を表示する事が出来るセルはB4セルの１つだけですが、もし、Sheet１の表中において、3月5日と入力されているセルがA7セルとC12セルとG20セルの3ヶ所であった場合には、Sheet2で3月5日に対応する商品名を表示させる場所が足りなくなります。
　それと同様に、Sheet2のA列に４月2日と入力されているのが、A3セルとA7セルの２ヶ所であった場合で、Sheet1で４月2日に対応する商品名が例えば「商品A」只一つだけであった場合には、Sheet1には「商品A」は１つだけしか存在していないにも拘らず、Sheet2の表ではB3セルとB7セルの2ヶ所に「商品A」と表示しなくてはならない事になってしまいます。
　これらの問題を避けるために、Sheet2においてB列に商品名を表示するのではなく、A列に入力した日付から重複しているデータを除いた上で、その日付を各日付ごとに必要なだけの行数を取りながら（例えば、その日付に対応する商品名が3つ存在していた場合には、次の日付は2行空けた処に表示させる）、C列上に昇順に並べ替えて表示し、その日付に対応する商品名はD列に表示するものとします。
　又、Sheet1のデータを一元化して処理し易くするために、Sheet3のA列～E列を作業列として使用するものとします。

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

=IF(COUNT(OFFSET(Sheet1!$A:$A,,ROWS($2:2)*2-2)),MATCH(9E+99,OFFSET(Sheet1!$A:$A,,ROWS($2:2)*2-2))-ROW(Sheet1!$A$1),"")

　次に、Sheet3のB1セルに

0

と入力して下さい。
　次に、Sheet3のB2セルに次の関数を入力して下さい。

=IF($A2="","",SUM($A$1:$A2))

　次に、Sheet3のA2～B2の範囲をコピーして、同じ列の3行目以下に、「Sheet1において日付が入力される可能性のある列の本数」を上回るのに充分な行数となるまで貼り付けて下さい。
　次に、Sheet3のD2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>MAX($B:$B),"",OFFSET(Sheet1!$A$1,ROWS($2:2)-VLOOKUP(ROWS($2:2)-1,$B:$B,1),(MATCH(ROWS($2:2)-1,$B:$B)-ROW($B$1))*2))

　次に、Sheet3のC2セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/$D2),COUNTIF($D:$D,"<"&$D2)+COUNTIF($D$1:$D2,$D2),"")

　次に、Sheet3のE2セルに次の関数を入力して下さい。

=IF($C2="","",OFFSET(Sheet1!$B$1,ROWS($2:2)-VLOOKUP(ROWS($2:2)-1,$B:$B,1),(MATCH(ROWS($2:2)-1,$B:$B)-ROW($B$1))*2)&"")

　次に、Sheet3のC2～E2の範囲をコピーして、同じ列の3行目以下に、「Sheet1において日付が入力される可能性のあるセルの個数を上回るのに充分な行数となるまで貼り付けて下さい。

　次に、Sheet2のC2セルに次の関数を入力して下さい。

=IF(MAX(C$1:C1)=MAX($A:$A),"",IF(ROWS($1:1)=1,MIN($A:$A),IF(ROWS($1:2)-MATCH(9E+99,$C$1:$C1)<COUNTIF(Sheet3!$D:$D,VLOOKUP(9E+99,$C$1:$C1,1)),"",SMALL($A:$A,COUNTIF($A:$A,"<="&VLOOKUP(9E+99,$C$1:$C1,1))+1))))

　次に、Sheet2のD2セルに次の関数を入力して下さい。

=IF(ISNUMBER(1/(ROWS($1:2)-MATCH(9E+99,$C$1:$C2)<COUNTIF(Sheet3!$D:$D,VLOOKUP(9E+99,$C$1:$C2,1)))),VLOOKUP(COUNTIF(Sheet3!$D:$D,"<"&VLOOKUP(9E+99,$C$1:$C2,1))+ROWS($1:2)-MATCH(9E+99,$C$1:$C2)+1,Sheet3!$C:$E,3,FALSE)&"",IF(ISNUMBER($C2),"(該当無し)",""))

　次に、Sheet2のC2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　これで、Sheet2のA列に入力した日付をSheet2のC列に重複無しに必要な行数を確保して昇順ら並べて表示し、D列にはその日付に対応する商品名が表示されます。

回答No.1

keithin

言わずもがなですがエクセルの検索関数は、１列に検索値が並んでいて、別の１列に欲しい結果が並んでるみたいなトコロから値を参照してくる事しかできません。

なので
＞年月日と商品名が交互に10列くらいあるんですが

こういうイレギュラーな事をしたいのでしたら、相応にメンドクサイ数式を組み立てなきゃなりません。

作成例：
シート１のＡ、Ｃ、Ｅ、Ｇ列に日付
シート１のＢ、Ｄ、Ｆ、Ｈ列に商品

シート２のＡ列に日付
Ｂ１：
=IF(A1="","",IF(COUNTIF(Sheet1!A:G,A1),INDIRECT("Sheet1!"&TEXT(SUMPRODUCT((Sheet1!$A$1:$G$99=A1)*(ROW($B$1:$H$99)*100+COLUMN($B$1:$H$99))),"!R0!C00"),FALSE),""))