条件に該当するデータを横並びに抽出するなら、Excel2021/365ならFILTER関数で抽出したデータをTRANSPOSE関数で横並びに入れ替えて求めましょう。Excel2019/2016なら、COUNTIF関数で条件ごとに連番を付けて、条件＋連番をもとにINDEX+MATCH+COLUMN関数で抽出しましょう。
指定の形で抽出
TRANSPOSE関数、FILTER関数、COUNTIF関数、IFNA関数、INDEX関数、MATCH関数、COLUMN関数
例題１｜
- データを求めるセル（B3セル）を選択し、「=TRANSPOSE(FILTER(」と入力する。
- ［配列］…抽出する「氏名」のセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
- ［含む］…「B7セル～B9セル」の「都道府県」がA3セルの都道府県である場合の条件式を入力する。
- ［空の場合］…省略して、「Enter」キーで数式を確定する。
- 数式を他の行に複写する。
【数式】 =TRANSPOSE(FILTER($A$7:$A$9,$B$7:$B$9=A3))
TRANSPOSE関数、FILTER関数、COUNTIF関数、IFNA関数、INDEX関数、MATCH関数、COLUMN関数
数式の解説
「=TRANSPOSE(FILTER($A$7:$A$9,$B$7:$B$9=A3))」の数式は、B7セル～B9セルの「都道府県」がA3セルの都道府県「埼玉県」である場合の条件を満たす、「氏名」（A7セル～A9セル）の行列を入れ替えて抽出する。ほかの行にコピーすることで、「都道府県」ごとに「氏名」が横並びで抽出される。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx
例題２｜
- D列のセルを選択し、「=B7&COUNTIF($B$7:B7,B7)」と入力したら、「Enter」キーで数式を確定する。
- 数式を必要なだけ複写する。
- データを求める表の左上のセル（B3セル）を選択し、「=IFNA(INDEX(」と入力する。
- ［参照］…抽出する「氏名」のセル範囲を選択して、「F4」キーを1回押して［$］記号をつけ絶対参照にする。
- ［行番号］…MATCH関数で条件「都道府県&1」のセルが表のD列の「都道府県&連番」の何番目にあるかを求める数式を入力する。
- ［列番号］［領域番号］…省略する。
- ［NAの場合の値］…［””］（空白）を入力して、「Enter」キーで数式を確定する。
- 数式を必要なだけ複写する。
【数式】=IFNA(INDEX($A$7:$A$9,MATCH($A3&COLUMN(A1),$D$7:$D$9,0)),””)
TRANSPOSE関数、FILTER関数、COUNTIF関数、IFNA関数、INDEX関数、MATCH関数、COLUMN関数
数式の解説
「=B7&COUNTIF($B$7:B7,B7)」の数式を作成してコピーすると、それぞれの「都道府県」に、同じ「都道府県」が1つなら「1」、2つあるなら「2」とカウントされた数をつなげた1つの文字列が作成される。
「MATCH($A3&COLUMN(A1),$D$7:$D$9,0)」の数式は、A3セルの「埼玉県」&1がD7セル～D9セルに作成した文字列内の何番目にあるかを求める。
求められた番目をINDEX関数の引数の［行番号］に組み合わせて数式を作成すると、「埼玉県」の「氏名」が抽出される。数式をコピーすると、次の列にはCOLUMN関数で求められた列番号が指定された数式「埼玉県」の2つ目の「氏名」が抽出される。次の行にコピーすると、「=IFNA(INDEX($A$7:$A$9,MATCH($A4&COLUMN(A2),$D$7:$D$9,0)),“”)」とMATCH関数の引数の［検査値］に「神奈川県」が指定されるので、「都道府県」ごとに「氏名」が横並びで抽出される。
Excelデータダウンロード
以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx
コメント