[PR]当サイトはアフィリエイト広告を利用しています。
はじめに
今回はエクセル関数を対象に学んだことをまとめたいと思います。
XLOOKUP関数は目的のセルを検索範囲内で最初の一つだけ取得する関数です。
しかしすべての目的セルを順番に取得したいという需要もあるかと思います。
今回はその方法を解説します。
ちなみにXLOOKUP関数から値を取り出しますが、XLOOKUP関数は使いません。
他の関数で実装します。
今回の対象データ。
次のようなデータを考えます。
ここから所属先ごとに該当する名前を抽出して並べるということがやりたいことです。
つまり
F3：佐藤
G3：前田
H3：橋本
I3：中山
F4：田中
G4：小池
F5：池田
としたいということです。
関数の入力
まず次のような表を作ります。
ちなみに各関数の解説は後ほど行います。
とりあえず手順を先に示します。
C12、D12セルに次のように入力します。
C12: =MATCH($B12,C$1:C$9,0)
D12: =MATCH($B12,INDIRECT(“C”& TEXT($C12+1,”@”) &”:C9″),0)
「$」マークの位置に注意してください。
オートフィルで引っ張るので$記号の位置が大切です。
この状態でC12セルをC14まで、D12セルをD14セルまでオートフィルで引っ張ります。
次に
E12：=IF(SUM($C12:D12)<=8,MATCH($B12,INDIRECT(“C”&TEXT(SUM($C12:D12)+1,”@”)&”:C9″),0),””)
この状態でE12セルをE14セルまでオートフィルで引っ張ります。
そしてE12からE14セルを選択した状態で横にI列までオートフィルで引っ張ります。
次にG3セルに次のように入力します。
G3：=INDIRECT(“B”&TEXT(C12,”@”))
G3セルをG5セルまでオートフィルで引っ張ります。
F列を空白にするのは意味がありません。やり方がわかれば削除していただいてかまいません。
オートフィル用に空けてみたのですが、使いませんでした。
次に次のように入力します。
H3：=IF(SUM($C12:D12)<=8,INDIRECT(“B”&TEXT(SUM($C12:D12),”@”)),IF(COUNTBLANK($C12:D12)=0,$B$9,””))
H3セルをH5セルまでオートフィルで引っ張ります。
最後にH3セルからH5セルまで選択してM列までオートフィルで引っ張ります。
とりあえずやりたかったことはできましたね。
関数の解説
C12: =MATCH($B12,C$1:C$9,0)
D12: =MATCH($B12,INDIRECT(“C”& TEXT($C12+1,”@”) &”:C9″),0)
MATCH関数は検索したい値が検索範囲にあった場合、検索範囲の何番目にあったかを出力する関数です。
検索した値は所属先AですからB12が入り、検索範囲はC列です。
INDIRECT関数はセル範囲を文字列で操作したいとき使う関数です。
“C”& TEXT($C12+1,”@”) &”:C9″
この書き方はMATCH関数の検索範囲に直接入れることができません。
セルの番地を参照するなど、複雑なセル範囲を書きたいときはINDIRECT関数を使います。
続いて
E12：=IF(SUM($C12:D12)<=8,MATCH($B12,INDIRECT(“C”&TEXT(SUM($C12:D12)+1,”@”)&”:C9″),0),””)
IF関数とSUM($C12:D12)が新しいところです。
IF関数の条件は、MATCH関数で並べたヒットした位置が、C列の最終位置まで到達したかどうかを判定しています。
SUM($C12:D12)がこれまで見つかったヒット位置の最後の位置を表しています。
C列の最終位置まで到達したら空白「””」を入力します。
続いては
G3：=INDIRECT(“B”&TEXT(C12,”@”))
H3：=IF(SUM($C12:D12)<=8,INDIRECT(“B”&TEXT(SUM($C12:D12),”@”)),IF(COUNTBLANK($C12:D12)=0,$B$9,””))
INDIRECT(“B”&TEXT(C12,”@”))はC列で見つかった値の最初の値を出力しています。
H3の関数は、最初のIF関数がC列の最終列まで到達したかどうかを判定しています。
これは最終列にある最後の値ただ一つを含んでいる所属先、つまりAしかFALSEになりません。
TRUEのときは
INDIRECT(“B”&TEXT(SUM($C12:D12),”@”))
が実行されます。
見つかった名前の現在の値を出力します。
FALSEのときは
IF(COUNTBLANK($C12:D12)=0,$B$9,””)
が実行されます。
これは空白を数えて、空白がない位置（C列の最終列の値に到達した直後）だけC列の最終列の値を出力し、それ以外のとき（C列の最終列に到達したが、すでに値を出力した状態）では空白を出力させています。
まとめ
今回はエクセル関数でXLOOKUP関数から順番に値を取り出して並べるという処理を解説しました。
VBAが苦手という方で関数ならわかるというときは、参考にしていたければと思います。
