FC2ブログ

複数ある検索データを取り出す 3 関数_作業列あり

前の 2回は、
 * オートフィルタ
 * フィルターオプションの詳細設定

でしたので、一度抽出すると それでおしまいでした

そこで、
 抽出条件を変えるだけで
 抽出内容が 変化するように
 関数を使いたいという方がいる
 元のデータは、以前と同じ

  201905280011.png

比較しやすいように
 抽出するのは、「 男 」のデータ

最初に、書いておきますが
     いろいろな方法が あります
その1つの方法で、作業列を使用します


 考え方を 理解していただければ、
  ご自分のケースに 応用できると思います


 1.抽出する Sheet 2 に、下の画像のように入力します
   条件のセル
   抽出する項目行

  201905280021.png
  ※ Sheet 2 A2 には、リストで、「男」・「女」を選択できるようにする
  ※ 仮に、「男」にしておく

 2.Sheet 1 の一覧表の右端に、「作業列」を作成します
   「 男 」 を 探すと、どの行も 同じ「 男 」で しかないので
   一番上にある「 男 」・2番目・・と 区別する目的
   設定する数式は
   「性別」が、Sheet 2 の A2 セルと一致する場合
   Sheet 1 D列で、その行までに、一致するデータがあった個数を表示
   一致しない場合は、空欄
   =IF(D3=Sheet2!$A$2,COUNTIF($D$3:D3,Sheet2!$A$2),"")

  201905280022.png

 J3 セルに、入力した数式について
  =IF(D3=Sheet2!$A$2,COUNTIF($D$3:D3,Sheet2!$A$2),"")
  D3=Sheet2!$A$2 これは、「性別」が、Sheet 2 で、指定した性別ということ
  D3 は、数式をオートフィルする場合に、同じように移動して欲しいのでそのまま
  Sheet2!A2 は、移動したら困るので、絶対参照 ($)設定
  COUNTIF($D$3:D3,Sheet2!$A$2) は、D列に、指定した性別がいくつあるか?
  D列のセル範囲 $D$3:D3 
  始まりは、D3 セル 移動したら困るので 絶対参照
  セル範囲の終わりは、その数式がある行まで 移動して欲しいのでそのまま
  数式を オートフィルする場合、
  セルやセル範囲の変化を 想像して 「 $ 」 を 設定する


作業列に 設定した数式により
抽出したいデータは、
  作業列に、数値がある行ということになる


ここからが、ややこしいのですが
Sheet2 のデータを表示したいセル Sheet2 A5 に 以下の数式を入力する
 ※ 文字で書こうとしたら、記号が良くないのか表示できないので、画像になってしまった
201905280023.png

A5 セルを 右へオートフィルする

  201905280024.png

そのまま データがありそうな位置まで、下へオートフィルする
 ※ 少し長めに設定する

  201905280025.png

結果
 文字列の配置や罫線は ともかく データは、表示される

  201905280026.png

肝心の関数の意味

 =IF(MAX(Sheet1!$J$3:$J$47)<ROW(A1),"",INDEX(Sheet1!B$2:B$47,MATCH(ROW(A1),Sheet1!$J$2:$J$47,0)))
 ※ 「<」を 「<」(全角)に変更したら、アップ出来ました

一番外側は、I F 関数
 条 件
 MAX(Sheet1!$J$3:$J$47)<ROW(A1)
  MAX関数、作業列の最大値=求めるデータの総数
  ROW関数 数式を設定した行の数
  これを比較して、数式を設定した行が、データの数を超えるかどうか
 真の場合
  「データの数」が、「数式を設定した行」より少ない場合、空欄
  ※ データが 無い行は、空欄にする
 偽の場合
  データがある場合
  INDEX(Sheet1!B$2:B$47,MATCH(ROW(A1),Sheet1!$J$2:$J$47,0))
  まず INDEX 関数
  範 囲
  元の一覧表(Sheet 1)の表示したい列のセル範囲
  数式を入力したのが、「受験番号」の列なので、Sheet 1 では、 B列
  数式を オートフィルするので  列は、自由 行は、固定
  
  MATCH 関数で、
  最初のセルは、1 次のセルは 2 を 探したいので
  ROW 関数で、探す数値を決定
  探す範囲は、「作業列」
  照合の型
  完全一致の 「 0 」 を 入力

数式を 右へ オートフィルすると
 INDEX 関数の範囲が、1つづつ右へずれる
 下へオートフィルすると
 ROW 関数が、1つづつ増えて、順に探す行が移動する
 データがなくなると、I F 関数によって、空欄になる

応用する方法

この例に使用した一覧表では、わかりやすく
条件を 1つにしましたが
「 作業列 」 に、入力する数式を変更する
例えば、**で、〇〇の場合などは
AND 関数を使用する
**または、〇〇の場合
OR 関数をしようするなど
ポイントは、
「 作業列 」に 表示したいデータの行に順番に番号を表示
必要ない行は、空欄にする

この例では、すべての列を 表示しましたが
「 名 前 」 だけを、表示したい場合は
Sheet 1 の C列のセル範囲だけを 取り出せばよい
INDEX 関数の「 範囲 」 を Sheet1!C$2:C$47 にするだけ
=IF(MAX(Sheet1!$J$3:$J$47)<ROW(B1),"",INDEX(Sheet1!C$2:C$47,MATCH(ROW(B1),Sheet1!$J$2:$J$47,0)))
※ 「<」(全角)になっています、コピーして使用する場合は、「<」(半角)に変更してください

.
スポンサーサイト



 

コメント

コメントの投稿

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

トラックバック

トラックバックURL: http://cheflapin.blog.fc2.com/tb.php/570-00aa224b