46

view

添付画像をご参照願います。

作業列は使用していません。
参照する元データの並びは、貴方のものと同じとします。
I5 に検索したい対象の「販売日」、J5 に検索したい対象の「営業所」を予め入力しておいたとします。
K5 以降の K列に、条件の一致した行の「商品」を表示させる数式です。

K5 セルの数式 ：
=IF(SUM(($B$5:$B$22&$C$5:$C$22=$I$5&$J$5)*1)<ROW(A1),"",INDEX($B$5:$D$22,SMALL(IF($B$5:$B$22&$C$5:$C$22=$I$5&$J$5,ROW($5:$22)),ROW(A1))-4,3))

この数式を入力後、「Ctrl」＋「Shift」＋「Enter」で確定します。
（配列数式の為）

あとは K5 セルを下へ適当な行数分までコピペします。



*****

【数式の解説】


◆$B$5:$B$22&$C$5:$C$22

B5&C5、B6&C6、・・・B22&C22 と、日付と営業所を「&」で繋いで文字列にしています。

◆SUM(($B$5:$B$22&$C$5:$C$22=$I$5&$J$5)*1)

I5&J5 と一致しているか否かを判定して、一致したものを合計しています。
これはご存知の関数を用いた形に置き換えると
SUM(IF($B$5:$B$22&$C$5:$C$22=$I$5&$J$5,1,0))
IF 内で一致すれば「1」、しなければ「0」を返し、それを SUM で合計します。

◆ROW(A1)

ROW は行番号を返す関数です。
ROW(A1) A列1行目の行番号なので、即ち「1」となります。
これを下の行へコピペすると、ROW(A2)、ROW(A3)・・・と変化します。
つまり、自動的に「○番目」の指定の数値を増やす役割を持ちます。

◆IF(SUM(($B$5:$B$22&$C$5:$C$22=$I$5&$J$5)*1)<ROW(A1),"",INDEX～)

この数式を5行目以降にコピペした場合、ROW(A5)=5 なので、一致する総数「4」を超える事となり、IF 関数に従って「""」（空欄）表示となります。


＜INDEX 文の中身＞

◆IF($B$5:$B$22&$C$5:$C$22=$I$5&$J$5,ROW($5:$22))

繋いだ文字列が、検索したい条件を繋いだ文字列と一致した場合、その行番号を返します。
ここでは ROW 関数は本来の機能として、行番号の値を求める使い方をしています。
このデータの場合ですと、{5,6,8,10} という行番号の配列が抽出されます。

◆SMALL(IF(～略～),ROW(A1))

IF 文で返された行番号の内、ROW(A1)番目に小さい番号を求めます。
下へコピペすれば、2番目に小さいもの、3番目に小さいもの・・・と、指定が変化します。

◆INDEX($B$5:$D$22,SMALL(～略～)-4,3)

B5～D22 を全体範囲として、SMALL(～略～)-4 の行、3列目のデータを表示させる数式です。
SMALL の最後に「-4」としているのは、データの開始行が5行目からなので、その分を差し引いています。
INDEX 関数は、最初に示した「B5～D22」という範囲の、一番左上のセルを基準に、○行目、○列目を指定しなければならない為です。