【配列数式を使わずに】表から複数のデータを抽出して、別の表に上から順に並べたい。

おはようございます！

画像の表①（Sheet1）から条件に合うデータを抽出し、
表②（Sheet2）にデータを上から並べる方法を知りたいです。
できればマクロではなく関数でお願いします。

具体的には、
「『収入1』が0ではない」を条件に表①から条件に合うデータを抽出し、
表②に、「名前」と「収入1」のデータを上から並べたいです。
（「収入1」の数の大小関係なく、表①のデータを上から抽出した順に並べたいです。）

最初は配列数式でやってみたのですが、
表①が参照されたデータからできている点と、
抽出先の表（②）に結合されたセルがある場合があるため、できませんでした。
参照と結合に関しては書式で決まっていること等もあり変えられません…

そこで、VLOOKUP関数が使えるかなと思ったのですが、、、

「0ではない」の条件は検索値にはなれないので、
表①の左端に新しい列を挿入し、
【「『収入1』が0ではない」が正なら「○」、負なら空白】のIF関数を入力しました。
その「○」をVLOOKUP関数の検索値としてやってみたのですが、これも上手くいきませんでした。
（なぜか名前「えええ」が一番上に抽出されました。収入の値が最小だから？）

また、VLOOKUP関数で仮に名前「あああ」が一番上に抽出できたとしても、2番目以降のセルに入れる式がわかりません。



私がやろうとしていること、関数でできるのでしょうか。
どなたかご教示ください。

なお、VLOOKUP関数にこだわりはありません。他の関数でできるのであればそちらでやります。
また、ここでは「収入1」を条件に「収入1」と「名前」を抽出していますが、
場面によっては条件も変わり、また、他の項目の抽出もしたいので、応用できるものだとありがたいです。
（例えば、「収入2」を条件に「名前」「住所」の抽出など。）


大変長々と失礼いたしました。
よろしくお願いいたします。

質問者からの補足コメント

• https://webtan.impress.co.jp/e/2013/09/12/16022
  から拝借しました。

  
    補足日時：2019/01/18 21:51

No.2 ベストアンサー 回答者： fujillin 回答日時： 2019/01/16 10:55

こんにちは

方法はいろいろあると思いますので、以下に一例を示します。

＞【配列数式を使わずに】
とのことですので、作業列を用います。
添付図では仮にI列としていますが、離れたところでも非表示の列でもかまいません。
上段が元となるデータ、下段が抽出結果の表を示しています。

まず、作業列のI2セルに
　=IF(D2>0,COUNT(I$1:I1)+1,"")
を入力して、下方にフィルコピーしています。
これによって、条件式（今回はD2>0）に該当する行だけ、上から順に連番が振られます。

おわかりのことと思いますが、抽出結果の表では、この作業列の値を1から順に検索してゆけば良いことになります。
作業列が表全体の左側にはないので、VLookupではなく、MatchとIndexの組み合わせで参照することになります。

抽出する表のA2セルには
　=IFERROR(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!I:I,0)),"")
B2セルには、
　=IFERROR(INDEX(Sheet1!D:D,MATCH(ROW(A1),Sheet1!I:I,0)),"")
の式を入力して、下方にフィルコピーしています。

式中の「Sheet1!A:A」、「Sheet1!D:D」の部分が参照したい対象の列を示していていますが、あとの部分はまったく同じ式になっています。

この回答へのお礼

できました！
元の表の情報量が多く構造も複雑だったため、4ヶ月くらいずっと苦戦して、もうできないのかと思っていました。
私の「やりたいこと」を形にしていただき本当にありがとうございました！

お礼日時：2019/01/18 21:04

No.1 回答者： mike_g 回答日時： 2019/01/16 10:54

》 配列数式でやってみたのですが、

》 …点と、…ため、できませんでした
と仰ってますが、参考までにその出来なかった「…点」と「…ため」を含む具体例を教えてください、配列数式で試してみたいので。

参考までに、お示しの表①と表②を利用して、「配列数式でやってみた」結果を示しておきます。(添付図 by Excel 2013)

１．範囲 A1:E6 選択 ⇒ Alt+MC ⇒ “左端列”だけにチェック入れ ⇒ [OK]
２．セル A1 をセル H5 にコピー＆ペースト
３．セル I5 選択 ⇒ Alt+AVV で、下記の[データの入力規則]を設定後、
￣￣“収入1”を表示させておく
￣￣￣￣入力値の種類: “リスト”
￣￣￣￣元の値: =$A$4:$A$6
３．セル H5 に次式を入力
￣￣=IFERROR(INDEX(INDIRECT(H$5),,SMALL(IF(INDIRECT($I$5)>0,COLUMN(INDIRECT($H$5))),ROW($A1))-1),"")
￣￣【お断り】上式は必ず配列数式として入力のこと
４．セル H5 を右隣および下３行にオートフィル

この回答へのお礼

丁寧なご回答ありがとうございます！

「具体例」ですが、
まず、「表①が参照されたデータからできている点」ですが、
「表①」は、もともと別の表（表⓪とします）のデータを参照した（リンクさせた）ものです。
参照している理由は、表⓪の作りが複雑なためです。表⓪は、田んぼの「田」の字のような形の所謂「テーブル・表」ではなく、画像（補足参照）のような「入力フォーム」風のもので、しかもこれが複数人分あります。そのため、同じ「氏名」のデータでも一人一人のデータが離れた場所にあったり、一行にひとつのデータがある行もあれば、一行に複数のデータが含まれる行もあったりで、配列数式での範囲指定などが上手くできませんでした。
そこで入力データを作業用のシンプルなテーブルにリンクさせて、それを配列数式に利用しようと思いましたが、結果が「0」になり、できませんでした。（配列数式を解説しているサイトに、参照されたデータは配列数式が使えない旨が記載されていました。ですが、どこのものか思い出せず、見つけることもできませんでした…。）

次に、「抽出先の表（②）に結合されたセルがある場合がある」ですが、
一度実験的に、リンクを用いていない実験用のテーブルで配列数式を使ってみたときに、Ctrl＋Shift＋Enterで確定させたところ、「配列数式は結合されたセルに使えません」というような注意書きが出ました。配列数式を使いたかったセルは書式で決まっている様式であったため、どうすることもできず、諦めました。

お礼日時：2019/01/18 21:50