FC2ブログ

複数ある検索データを取り出す 4 ちょっと変わった方法

複数ある検索データを取り出す 
ここに、入れようか迷ったのですが
私が、面白いって、思ってしまい
どうしても忘れないうちに、書いておこうと思い
無理やり、ここへ入れました

ちょっと前に、書いた 新しい関数の TEXTJOIN 関数を使った方法

使用するのは、今までと同じ一覧表
条件も同じ「 男 」のデータ

自分用と思ってください
ややこしいです

Sheet2 は、以下のようにする

 201905280027.png

A5 セルに、以下の数式
=TEXTJOIN(",",1,IF($A$2=Sheet1!$D$3:$D$47,Sheet1!B$3:B$47,""))
[Crtl]+[Shift]+[Enter]キーで、確定
配列数式です
{=TEXTJOIN(",",1,IF($A$2=Sheet1!$D$3:$D$47,Sheet1!B$3:B$47,""))}

201905280028.png

こうなってから、右へオートフィル

 201905280029.png

そのまま、コピーして
少し離れた A7セルで、右クリック
「形式を選択して貼り付け」をクリック

 201905280030.png

[形式を選択して貼り付け]ダイアログ
【 〇 値 】 に、チェックを 入れ
【 □ 行列を入れ替える 】 に、チェックを入れ
[ OK ] クリック

 201905280031.png

データが、「 , 」区切りで、表示される

 201905280032.png

データ]タブ 「区切り位置」をクリックする

 201905280033.png

データの区切り位置 設定ウィザード]ダイアログ
【 〇 区切り記号 】 に、チェックを入れる
[ 次へ ] を クリック

 201905280034.png

データの区切り位置 設定ウィザード- 2/3]ダイアログ
【 □ カンマ 】 に、チェックを入れる
[ 完了 ] を クリック

 201905280035.png

データが、横ですが、表示される

 201905280036.png

A7 セルから、[Ctrl]+[Shift]+[→]で、右端まで選択
[Ctrl]+[Shift]+[↓]で、すべて選択
コピーする
A16 セルに、先ほどと同様
「形式を選択して貼り付け」
【 〇 値 】 に、チェックを 入れ
【 □ 行列を入れ替える 】 に、チェックを入れ
[ OK ] クリック
行列を入れ替えて、値で貼り付ける

 201905280037.png

この後は、5行目から15行を削除する

 201905280038.png

実用には、向かないかもしれませんが
     何かのヒントには、なりそう


 

複数ある検索データを取り出す 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)))
※ 「<」(全角)になっています、コピーして使用する場合は、「<」(半角)に変更してください

. 

複数ある検索データを取り出す 2

今回は、
フィルタ オプションの設定(データ抽出)

サンプルは、前回と同じ

  201905280011.png

 抽出する場所を、別のシートへ抽出する

 この場合は、抽出する条件の「見出し」をコピー
 条件は、「性別」が、「 男 」 なので、
 見出しは、「性別」を コピーする

 別シートへ 貼り付ける この場合 Sheet2
 そして、そのセルの下のセルへ条件を入力する
 条件は、男

  201905280013.png

  この入力したセルから 
  1列 1行以上離したセルをクリック A4 セル
  [データ]タブ
  「詳細設定」をクリックする

  201905280014.png

 [フィルタオプションの設定]画面が表示される
 
  201905280015.png

 「抽出先」
 【 〇 指定した範囲 】 に、チェックを入れる


  201905280016.png

 「リスト範囲」欄に、既に「Sheet2!$A$4」とあるが
 削除し、Sheet1 の一覧表を選択する

  201905280017.png
  セル範囲 B2~I47 を選択した場合
  シート名が表示され、セル範囲は、絶対参照($)になる
次の 「検索条件範囲」欄には、
 先ほど作成した A1:A2 を選択する

  201905280018.png

 最後の 「抽出範囲」欄には、
  最初にクリックしたセル A4 を選択する
  ※ この位置が、抽出するデータの左上になる

 201905280019.png

 [OK]を クリックすると
 データが表示される

  201905280020.png

これで、データは抽出できたので
後は、列幅を調整するだけ


次回は、関数を使用して表示する方法

. 

複数ある検索データを取り出す 1

表計算で、質問が多いもの
複数ある検索データを取り出す
どうやるのか?

検索データを表示する場合
初心者の場合
関数では、VLOOKUP関数や INDEX + MATCH 関数が
思いつくでしょう
しかし
これでは、1つデータしか表示できない

複数のデータが、検索に一致する場合
一番簡単な方法は、

 オートフィルタ (データ抽出) 
 フィルタオプションの設定(データ抽出)などの機能

  201905280003.png

このような一覧表の場合
性別が、「男」のデータを取り出すと
データは、複数行になります

この一覧表は、ちゃんとデータ形式になっていますので
すぐに、オートフィルタを設定できます
1.表の中のセルを選択 仮に E6 セル
2.[ホーム]タブ 「自動フィルタ」→「自動フィルタ」 を クリック

  201905280004.png

3.「項目名」のセルに、▼ が 表示される

  201905280005.png

4.「性別」欄の ▼ を クリックし、「 男 」にだけチェックを入れ、[OK]をクリックする

  201905280006.png

5.これだけで、「 男 」のデータを表示できます

  201905280007.png

7.このデータを別シートへ表示したい場合は
  この表をコピーし、貼り付ければ良い

  201905280008.png

次回は、コピーしないで、直接別シートへ抽出する方法

.
 

旧製品「KINGSOFT Office」製品サポート終了のお知らせ

先ほど ちょっと Kingsoft Office の HP へ 行ってみた
お知らせが、いくつかありました

 1.新元号に対応した最新プログラムの提供を開始しました
 2.WPS Office(KINGSOFT Office)をご利用中のお客様へのご案内
 3.旧製品「KINGSOFT Office」製品サポート終了のお知らせ

一番重要なことは、3番目の「サポート終了」のお知らせではないでしょうか?

 先般ご案内のとおり、
 長らくご愛顧いただきました
 旧製品「KINGSOFT Office」の製品サポートが
 2019年6月28日を もって 終了いたします。
 それ以降は、
  操作に関するお問い合わせ、
  シリアル番号認証解除等の技術サポート、
  製品不具合の改修などへの
 ご対応は致しかねますので、予めご了承ください。