![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
おはようございます!
画像の表①(Sheet1)から条件に合うデータを抽出し、
表②(Sheet2)にデータを上から並べる方法を知りたいです。
できればマクロではなく関数でお願いします。
具体的には、
「『収入1』が0ではない」を条件に表①から条件に合うデータを抽出し、
表②に、「名前」と「収入1」のデータを上から並べたいです。
(「収入1」の数の大小関係なく、表①のデータを上から抽出した順に並べたいです。)
最初は配列数式でやってみたのですが、
表①が参照されたデータからできている点と、
抽出先の表(②)に結合されたセルがある場合があるため、できませんでした。
参照と結合に関しては書式で決まっていること等もあり変えられません…
そこで、VLOOKUP関数が使えるかなと思ったのですが、、、
「0ではない」の条件は検索値にはなれないので、
表①の左端に新しい列を挿入し、
【「『収入1』が0ではない」が正なら「○」、負なら空白】のIF関数を入力しました。
その「○」をVLOOKUP関数の検索値としてやってみたのですが、これも上手くいきませんでした。
(なぜか名前「えええ」が一番上に抽出されました。収入の値が最小だから?)
また、VLOOKUP関数で仮に名前「あああ」が一番上に抽出できたとしても、2番目以降のセルに入れる式がわかりません。
私がやろうとしていること、関数でできるのでしょうか。
どなたかご教示ください。
なお、VLOOKUP関数にこだわりはありません。他の関数でできるのであればそちらでやります。
また、ここでは「収入1」を条件に「収入1」と「名前」を抽出していますが、
場面によっては条件も変わり、また、他の項目の抽出もしたいので、応用できるものだとありがたいです。
(例えば、「収入2」を条件に「名前」「住所」の抽出など。)
大変長々と失礼いたしました。
よろしくお願いいたします。
![「【配列数式を使わずに】表から複数のデータ」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/f/542838033_5c3e59bc80a72/M.jpg)
No.2ベストアンサー
- 回答日時:
こんにちは
方法はいろいろあると思いますので、以下に一例を示します。
>【配列数式を使わずに】
とのことですので、作業列を用います。
添付図では仮に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」の部分が参照したい対象の列を示していていますが、あとの部分はまったく同じ式になっています。
![「【配列数式を使わずに】表から複数のデータ」の回答画像2](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/b/587726_5c3e8de67bf8a/M.jpg)
できました!
元の表の情報量が多く構造も複雑だったため、4ヶ月くらいずっと苦戦して、もうできないのかと思っていました。
私の「やりたいこと」を形にしていただき本当にありがとうございました!
No.1
- 回答日時:
》 配列数式でやってみたのですが、
》 …点と、…ため、できませんでした
と仰ってますが、参考までにその出来なかった「…点」と「…ため」を含む具体例を教えてください、配列数式で試してみたいので。
参考までに、お示しの表①と表②を利用して、「配列数式でやってみた」結果を示しておきます。(添付図 by Excel 2013)
1.範囲 A1:E6 選択 ⇒ Alt+MC ⇒ “左端列”だけにチェック入れ ⇒ [OK]
2.セル A1 をセル H5 にコピー&ペースト
3.セル I5 選択 ⇒ Alt+AVV で、下記の[データの入力規則]を設定後、
 ̄ ̄“収入1”を表示させておく
 ̄ ̄ ̄ ̄入力値の種類: “リスト”
 ̄ ̄ ̄ ̄元の値: =$A$4:$A$6
3.セル H5 に次式を入力
 ̄ ̄=IFERROR(INDEX(INDIRECT(H$5),,SMALL(IF(INDIRECT($I$5)>0,COLUMN(INDIRECT($H$5))),ROW($A1))-1),"")
 ̄ ̄【お断り】上式は必ず配列数式として入力のこと
4.セル H5 を右隣および下3行にオートフィル
![「【配列数式を使わずに】表から複数のデータ」の回答画像1](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/0/298588_5c3e8ecf5a3ac/M.jpg)
丁寧なご回答ありがとうございます!
「具体例」ですが、
まず、「表①が参照されたデータからできている点」ですが、
「表①」は、もともと別の表(表⓪とします)のデータを参照した(リンクさせた)ものです。
参照している理由は、表⓪の作りが複雑なためです。表⓪は、田んぼの「田」の字のような形の所謂「テーブル・表」ではなく、画像(補足参照)のような「入力フォーム」風のもので、しかもこれが複数人分あります。そのため、同じ「氏名」のデータでも一人一人のデータが離れた場所にあったり、一行にひとつのデータがある行もあれば、一行に複数のデータが含まれる行もあったりで、配列数式での範囲指定などが上手くできませんでした。
そこで入力データを作業用のシンプルなテーブルにリンクさせて、それを配列数式に利用しようと思いましたが、結果が「0」になり、できませんでした。(配列数式を解説しているサイトに、参照されたデータは配列数式が使えない旨が記載されていました。ですが、どこのものか思い出せず、見つけることもできませんでした…。)
次に、「抽出先の表(②)に結合されたセルがある場合がある」ですが、
一度実験的に、リンクを用いていない実験用のテーブルで配列数式を使ってみたときに、Ctrl+Shift+Enterで確定させたところ、「配列数式は結合されたセルに使えません」というような注意書きが出ました。配列数式を使いたかったセルは書式で決まっている様式であったため、どうすることもできず、諦めました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) エクセルでINDEXとMACTHで出てきたデータの数を数えるには? 1 2023/04/25 10:21
- C言語・C++・C# C言語初心者 構造体 課題について 1 2023/03/10 19:30
- Excel(エクセル) Excelの数式についての質問 1 2022/10/31 15:50
- Excel(エクセル) ExcelのVLOOKUP関数 7 2022/08/23 06:46
- Excel(エクセル) エクセルの関数を教えてください。② 9 2023/05/25 15:28
- Excel(エクセル) エクセルで対象日に該当するデータがある場合に別表へ全対象者を表示させたい。 3 2023/07/12 09:48
- Excel(エクセル) エクセルの関数 2 2023/01/17 15:24
- Excel(エクセル) Indirect関数について、Formulatextで抽出した数式を参照したい。 1 2022/12/15 11:16
- Excel(エクセル) エクセルのマクロで複数条件に当てはまるものを全て抽出したいです 7 2022/05/21 08:51
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
IF関数の複数条件とINDEX関数とMATCH関数を使った長い関数を短くできる?
Excel(エクセル)
-
Excelで配列数式を使わずにとびとびの集計をしたいのですが
Excel(エクセル)
-
【Excel】 INDEX ,MATCH でいいのか。該当が複数ある場合
Excel(エクセル)
-
-
4
【excelVBA】Findメソッドで検索対象を複数列
Excel(エクセル)
-
5
Excelで重複データの件数ではなく、何番目かを求める方法
Excel(エクセル)
-
6
excelの特定のセルの隣のセル指定について
その他(Microsoft Office)
-
7
エクセルVBAでNumLockキーの状態を確認する
Excel(エクセル)
-
8
excel:条件付き書式の適用範囲が変わる
Excel(エクセル)
-
9
Excelで、条件と一致する最後のセルを検索したい
Excel(エクセル)
-
10
VBAでブックを非表示で開いて処理して閉じる方法
Excel(エクセル)
-
11
複数の条件に合う行番号を取得するには
その他(Microsoft Office)
-
12
数式による空白を無視して最終行を取得するマクロ
Excel(エクセル)
-
13
フィルターかけた後、重複を除いてカウントしたい。 すみませんアドバイスお願いします! 取引コード 販
Excel(エクセル)
-
14
条件に一致する最終行の値をエクセル関数で抽出する方法
Excel(エクセル)
-
15
OFFSET関数とCOUNTIFって組み合わせはできますか? COUNTIF関数で行を新しく追加した
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について教えて...
-
Excelデータをコピペして、ペー...
-
Excel関数-文字列で自動作成さ...
-
スプレッドシート、Excelでの数...
-
Excelで50個のセルに同じ文字を...
-
Microsoft Officeの中古は信用...
-
スプレッドシートで使う数式を...
-
エクセルVBA、別ブックへ転記す...
-
エクセルで会社の従業員のデー...
-
エクセルで不等号記号(≠)が上に...
-
エクセルの表で1年間の曜日を...
-
A列とB列を参照してC列に連番を...
-
エクセルの空欄をつめて、次の...
-
エクセルでの特別な文字を上に...
-
エクセルでセルに標準で入力さ...
-
エクセル日付 文字列の関数がエ...
-
エクセル2013で月間勤務表から...
-
エクセルの日付を編集する
-
EXCELの質問です 119から足した...
-
【マクロ】アクティブセルにブ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報
https://webtan.impress.co.jp/e/2013/09/12/16022
から拝借しました。