エクセル式 更新可能なリストの複数条件別抽出
エクセル式 更新可能なリストの複数条件別抽出
更新できる再抽出(別セル又は別シート)したい
エクセルのフィルター詳細設定はデータ更新できない。
データより別表への抽出は、フィルター→詳細設定より
条件を指定してカラム名を指定して行えますが、もとのデータが更新された場合の
再実行ができません。
都度実行するには、同じ処理を行わなければならない。
VLOOKUPを使用した場合は、更新自体は行えるが、抽出は1行のみとなるためリストの抽出はできない。
今回は、下記にてエクセル表を作成しました。
例題
下記の表は、EX1にマスターデータとして
A列 東京、大阪、名古屋の地域
B列 1/1~1/5までの日付
C列 担当者の人名
D列 参加人数の数値がリストアップしてある表です。
この表より、各地域の担当者を日付別に別リストにしてみましょう。
①範囲内の日付を最小値で取り出す
EX1が今回のマスターデータとなる表になります。1/1~1/5までの5日分のデータがまとめられています。
翌週になればこのマスターデータが更新されると想定します。
F1のセルにB列の最小値を記入します
=MIN($B$1:$B$15)
次に 2日目を算出します
F11セルに =MIN($B$1:$B$15)+1
または=F1+1でも構いません。
3日目はF21セルにMIN($B$1:$B$15)+2とします。
これで、マスターデータが更新されて翌週の日付に書き換わっても自動的に更新されるようになります
②複数条件で絞り込んだ、複数データを別表へ抽出する。
次に1日目の東京のデータを抽出します。 H3~H9までに該当する担当者名 I3~I9に参加人数を抽出します。
H3セルへ
=IFERROR(INDEX($A$1:$D$15,SMALL(IF(($A$1:$A$15=$G$2)*($B$1:$B$15=$F$1),ROW($A$1:$A$15)),ROW()-2),3),””)と記入後
CTRとSIFT ENTERを同時に押して配列関数とします。
I3セルへ
=IFERROR(INDEX($A$1:$D$15,SMALL(IF(($A$1:$A$15=$G$2)*($B$1:$B$15=$F$1),ROW($A$1:$A$15)),ROW()-2),4),””)記入後
CTRとSIFT ENTERを同時に押します。
H3とH4の違いは最後の3,4のみです。
これは、マスターデータの何番目の列を返すかを表します。よって、 3番目の列なら人名列を 4番目の列なら参加人数を表示します。
最後にH3、H4セルの式は配列関数ですので、H3H4を選択後オートフィルでH9I9までドラッグします。
これで、1日目に東京の項目が複数あっても7件分までは表示できます。
表示最大数を増やすには オートフィルでドラッグするセルを増やしてください。
補足
*ROW()-2は、H3セルと マスターデータのオフセットを行います。よってH13セルに2日目を記入する場合は
ROW()-12とし、 H22へ3日目を抽出する場合はROW()-22とします
補足
**SMALL関数は配列関数のため、IF内でOR、ANDが使えない。そのため
IF((条件1)*(条件2)を使用します。
以上で 更新可能なリストの別表抽出、 複数条件で抽出ができました。
これで来週もバッチリマネジメントできますね。
最近のコメント