FC2ブログ

複数ある検索データを取り出す 5 作業列なし関数

作業列を使わない
   関数で求める方法


 条件と一致する行が、何行目か 計算
 求めた行数の小さい方から
 順番に データを取り出す
 これを、一つの関数内で設定する


使用するのは、今までと同じ

  201905280011.png
 Sheet2 は、以下のような状態

  201905311001.png

  Sheet2 の A5 セルに
 =IF(COUNTIF(Sheet1!$D$3:$D$47,$A$2)<ROW(A1),"",INDEX(Sheet1!B$3:B$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1))))
 注意:ネットにアップするために、関数内の「<」を全角にしています、本来は半角です
数式欄に入力が終わったら
 [Ctrl]+[Shift]+[Enter]キーを 押す
  ※ 関数内が、配列なので、 「配列数式
 {=IF(COUNTIF(Sheet1!$D$3:$D$47,$A$2)<ROW(A1),"",INDEX(Sheet1!B$3:B$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1))))}
 注意:ネットにアップするために、関数内の「<」を全角にしています、本来は半角です


I F 関数
 「 論理式 」
  COUNTIF(Sheet1!$D$3:$D$47,$A$2)<ROW(A1)
  注意:ネットにアップするために、関数内の「<」を全角にしています、本来は半角です
 「 真の場合 」
  ""
 「 偽の場合 」
  INDEX(Sheet1!B$3:B$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1)))

「 論理式 」

 COUNTIF(Sheet1!$D$3:$D$47,$A$2)<ROW(A1)
 Sheet1 の D列=「性別」の列に、
 選択した性別「男」の数を COUNTIF 関数で求め
 関数を入力したセルの数と比較する
 データが増える=行数が増える
 最初のセルに ROW(A1) なので、データの数と同じになる
 つまり、行数が、データの数より増えた時点で、TRUE ( 真 )

 「 真の場合 」
 
 行数が、データの数を超えたら、空欄=""

 「 偽の場合 」

 INDEX(Sheet1!B$3:B$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1)))

 INDEX 関数

 配列
  Sheet1!B$3:B$47
 行番号
  SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(A1))

配列
求める値は、一番左の列「受験番号」のB列
設定する数式を 右へオートフィルして、別の列のデータを求めるので
列は、固定しない

行番号
Small 関数
配列
 IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45))
順位
 ROW(A1)

配列は、D列が、「男」の場合 行番号を求め
順位は、関数を設定したセル上から順番に求める

A5 セルに、入力し、配列数式に決定

 201905311002.png

右へ オートフィルする
 
 201905311003.png

下へも オートフィル
 ※ 予想データ数より すこし多めに オートフィルする

 201905311004.png

結果

 201905311005.png

A2 には、リストを設定してあるので、「女」に変更する

 201905311006.png

 201905311007.png

ここでは、すべてのデータを表示しましたが、
「 氏名 」だけを抽出したい場合
 {=IF(COUNTIF(Sheet1!$D$3:$D$47,$A$2)ROW(B1),"",INDEX(Sheet1!C$3:C$47,SMALL(IF(Sheet1!$D$3:$D$47=Sheet2!$A$2,ROW(Sheet1!$B$1:$B$45)),ROW(B1))))}
 注意 : ネットにアップするために、関数内の「<」を全角にしています、本来は半角です
  コピーして使用する場合は、注意してください


INDEX 関数の「範囲」を 目的の「 氏名 」のセル範囲にするだけで良い

201905311009.png

 201905311008.png


使用している セル ・セル範囲
 1. D3:D47 = 検索対象のセル範囲
 2. ROW(A1) これは、2回とも データを 1から確認する方法
    A1でも B1 でも C1 行数が「 1 」 ならば 構わない
 3. INDEX 関数の範囲 求めるデータの列(セル範囲)
 4. Sheet2!$A$2 は、検索の値
 5. ROW(Sheet1!$B$1:$B$45) データと同じ幅を持つセル範囲
   列は、どの列でも構わない、行が、「 1 」から 必ず、データの量と同じ行数にする

変更するには、
以上のことを 考慮し 数式を変更し
最後に、必ず、配列数式に 設定する
[Ctrl]+[Shift]+[Enter]キーを 押す

.
スポンサーサイト



 

コメント

コメントの投稿

  • URL
  • コメント内容
  • password
  • 秘密
  • 管理者にだけ表示を許可する

トラックバック

トラックバックURL: http://cheflapin.blog.fc2.com/tb.php/572-5238c3bc