【EXCEL関数】異なるシートへの条件付き抽出
- 一覧表から特定の条件を満たすデータのみを別のシートに抽出するためのEXCEL関数について教えてください。
- フィルタではなく関数を使用する理由は、抽出したデータに名前の定義を使用してリスト表示を行いたいためです。
- 試したフィルタオプションや配列関数などではうまく結果が表示されなかったので、正しい関数の使い方についてのアドバイスをお願いします。
- ベストアンサー
【EXCEL関数】違うシートに抽出結果を出したい
一覧表から「○」の条件が付いたデータのみを別シートに関数で抽出したいです。 フィルタではなく関数を使用したいのは、抽出したデータから名前の定義を使用して リスト表示をさせたいためです。ご教示のほど、宜しくお願いします。 シート1は一覧表となっており、A列に「○」のついたデータのみをシート2に表示 シート1(一覧表) A B 1 ○ 山田太郎 2 × 佐藤次郎 3 ○ 高橋三郎 4 × 鈴木四郎 5 × 中村五郎 シート2(抽出結果を表示させる) A B 1 ○ 山田太郎 2 ○ 高橋三郎 3 4 5 フィルタオプション、当方の考え得る限りの配列関数等、色々試しましたが、 不勉強のために上手く結果が表示されませんでした。 ご教示のほど、宜しくお願い致します。
- tabinobino
- お礼率92% (12/13)
- Excel(エクセル)
- 回答数5
- ありがとう数9
- みんなの回答 (5)
- 専門家の回答
質問者が選んだベストアンサー
vlookupは検索元データの左側に作業列が無いといけませんが、index matchでは右側に検索データがあっても構いません。 sheet1 C列を作業列 C1式 =IF(A1="○",ROW(),"") sheet2 A1式 =IFERROR(INDEX(Sheet1!A:A,MATCH(SMALL(Sheet1!$C:$C,ROW(A1)),Sheet1!$C:$C,0)),"") A1をB1にコピー A1~B1を下方へオートフィル。 データが無い場合は空白を返すので、想定より多いセル範囲に数式をコピーしておけば、sheet1のデータが修正されても追随します。
その他の回答 (4)
- Prome_Lin
- ベストアンサー率42% (201/470)
私の回答は、無視してください。 質問者の問題を「VBA」で解いてみたくなっただけです(自分の勉強用です)。 Option Explicit Sub Test() Dim c, i As Integer c = 0 For i = 1 To Worksheets(1).Range("A1").End(xlDown).Row If Worksheets(1).Cells(i, 1).Value = "○" Then c = c + 1 Worksheets(2).Cells(c, 1).Value = "○" Worksheets(2).Cells(c, 2).Value = Worksheets(1).Cells(i, 2).Value End If Next i End Sub 一応、説明しておきます。 シート「1」の1行目から「A」列にデータのある最終行(この場合、データが抜けている行があると、その前の行までになってしまいます)までを繰り返す。 シート「1」の「A」列に「○」を発見したらシート「2」の「c」行目の「A」列に「○」を、「B」列にシート「1」からお名前を取り込む。 以上です。
お礼
あ、ありがとうございます! いつかそんな難しいことにも挑戦してみます!
- bunjii
- ベストアンサー率43% (3589/8248)
シート2のA1へ次の数式を設定します。 =IF(COUNTIF(シート1!$A$1:$A$10,"○")>=ROW(),INDEX(シート1!A:A,LARGE(INDEX((シート1!$A$1:$A$10="○")*ROW(A$1:A$10),0),COUNTIF(シート1!$A$1:$A$10,"○")+1-ROW())),"") 右へB1までオートフィルコピーし、そのまま下へ10行までオートフィルコピーすれば10行分が完成します。 元データ(シート1)の行数が10行以上のときは数式内の行番号を変更してください。 数式の解説が必要のときは補足してください。
お礼
ありがとうございます! 他の方の回答で解決しましたが、いただいた数式も試してみます!
- kagakusuki
- ベストアンサー率51% (2610/5101)
今仮に、Sheet3のA列を作業列として使用するものとします。 まず、Sheet3のA1セルに次の様な関数を入力して下さい。 =IF(INDEX(Sheet1!$A:$A,ROW())="○",ROW(),"") 次に、Sheet3のA1セルをコピーして、Sheet3のA2以下に貼り付けて下さい。 次に、Sheet2のB1セルに次の様な関数を入力して下さい。 =IF(ROWS($1:1)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL(Sheet3!$A:$A,ROWS($1:1)))) 次に、Sheet2のA1セルに次の様な関数を入力して下さい。 =IF($B1="","","○") 次に、Sheet2のA1~B1のセル範囲をコピーして、Sheet2のA列~B列の2行目以下に貼り付けて下さい。 尚、もしSheet1のB列に入力されている氏名の中に、同じ名前が複数回現れる事が無い事が保障されている場合には、作業列を使わなくとも抽出する方法はあります。 その場合、Sheet2のB1セルに入力する関数を次の様なものとすればOKです。(Sheet2のA列の関数には変更ありません) =IF(ROWS($1:1)>COUNTIF(Sheet1!$A:$A,"○"),"",INDEX(Sheet1!$B:$B,IF(ROWS($1:1)=1,MATCH("○",Sheet1!$A:$A,0),MATCH("○",INDEX(Sheet1!$A:$A,MATCH(INDEX(B:B,ROW()-1),Sheet1!$B:$B,0)+1):INDEX(Sheet1!$A:$A,ROWS(B:B)),0)+MATCH(INDEX(B:B,ROW()-1),Sheet1!$B:$B,0))))
- keithin
- ベストアンサー率66% (5278/7940)
サルでも出来る簡単な方法としては Sheet1: A B C 印 検索 内容 ○ 1 あれ × これ ○ 2 それ どれ × やれ B2: =IF(A2="○",COUNTIF(A$2:A2,A2),"") 以下コピー と準備 Sheet2: A B 番号 内容 1 あれ 2 それ A2: =IF(ROW(A1)>MAX(Sheet1!B:B),"",ROW(A1)) B2: =IF(A2="","",VLOOKUP(A2,Sheet1!B:C,2)) 以下コピー といった具合に順繰り拾っていけば,間違えなく出来ます。
お礼
ありがとうございます! 他の方の回答で解決しましたが、いただいた数式も試してみます!
関連するQ&A
- シート内の抽出について(Excel)
シート内の抽出について(Excel) シート内に同じ文字列が複数存在するデータから抽出して使用したいのですが、ご存知の方おりましたら教えていただきたいのですが。 例 A B C D 1 001 ABC 500 太郎 2 001 ABC 200 花子 3 001 ABC 150 太郎 4 002 DEF 500 次郎 5 002 DEF 200 三郎 6 002 DEF 150 次郎 7 002 DEF 100 次郎 このようなシートがあったとしますと 抽出条件として ’001’ とした場合 番号:001 A B C D 1 001 ABC 500 太郎 2 001 ABC 200 花子 3 001 ABC 150 太郎 このように他のシートに抽出できればベストなのです。 データーが膨大なものでオートフィルターで抽出しコピー&ペーストでは時間がかかり過ぎてしまいます。関数でなんとかなればと思っておりますがお知恵をかしていただけないでしょうか?
- ベストアンサー
- その他MS Office製品
- Excel関数である条件に満たしたものを抽出
Excel関数である条件に満たしたものを別シートに抽出したいのですが たとえばsheet1に A B C ID 名前 条件フラグ 101 太郎 1 102 花子 3 103 一郎 2 104 二郎 3 というデータがあってここから 条件フラグが「3」のものだけをsheet2に抽出したいのですが オートフィルタではなく関数で処理をしたいと思います。お分かりの方、ご教授ください。
- ベストアンサー
- オフィス系ソフト
- Excel2010でシートAとシートBとの比較
よろしくお願いします。 Excel2010を使用しています。 シートA(マスターデータ)とシートB(ランダムデータ)のリストを比較して シートAのデータからシートBのデータを取り除いたデータを抽出したいです。 シートA(マスターデータ) A列 B列 111111 田中一郎 222222 田中二郎 333333 田中三郎 444444 田中四郎 シートB(ランダムデータ) A列 B列 222222 田中二郎 444444 田中四郎 この場合、抽出したいのは 111111 田中一郎 333333 田中三郎 の2名です。 いろいろネットで調べてトライしてみたのですがなかなかうまくいきませんでした。 Excelは少し苦手ですががんばりますのでよろしくお願いします。
- ベストアンサー
- その他MS Office製品
- EXCEL関数でフィルタの様にデータ抽出したい
発注品一覧表から社別にデータを抽出した発注シートを関数で作りたいです。 オートフィルタは諸事情あって使用しません。 過去の質問のこちらが、かなり近い回答なのだと思いますが 不勉強で自分用に修正ができませんでした。 http://okwave.jp/qa/q3157199.html 一覧表はこのようになっています。 日付 社名 品目 値段 4/1 C社 鉛筆 50円 4/1 A社 定規 150円 4/2 C社 ペン 100円 4/2 B社 鉛筆 100円 社別の別シートに日付・品目・値段を一覧から関数で抽出できるでしょうか? どうぞよろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- 「並び替えとフィルター」を使って空白以外を抽出
シート1に別のシートから抽出した人名が並んでいます。 シート2にも別のシートから抽出した数字が並んでいます。 人名と数字は同じ行/列に格納されます。 シート1もシート2も空白が含まれています。 シート3に人名と数値を隣り合わせで格納したいのです。 シート1とシート2共に、「並び替えとフィルター」を使って空白以外を 抽出したいのですが、「昇順/降順」を使うと、人名と数字の関連が崩れる ので困っています。 「並び替えとフィルター」で不可能ならば、別な方法でも構いません。 ご教示いただけますようお願い致します。 シート1 シート2 A B C D A B C D 1 太郎 次郎 1 100 10 2 三郎 花子 春子 2 5 200 50 3 秋子 夏子 3 500 100 4 四郎 五郎 六郎 4 80 300 400 5 七郎 冬子 5 300 60 シート3 A B C D E F G H 1 太郎 100 花子 200 次郎 10 春子 50 2 三郎 5 五郎 300 秋子 500 夏子100 3 四郎 80 六郎 400 冬子 400 4 七郎 300 5
- ベストアンサー
- Excel(エクセル)
- Excel関数でデータを抽出する方法
Excel初心者教室での課題で、以下のような問題を出されました。 次の表から、データが全てあるものだけを抽出し、sheet1からsheet2に移しなさい。 〈名前〉〈 E社〉〈 F社〉〈 G社〉 〈 A 〉〈 1 〉 〈 2 〉 〈 3 〉 〈 B 〉 〈 2 〉 〈 2 〉 〈 C 〉〈 3 〉〈 1 〉〈 5 〉 〈 D 〉 〈 1 〉 上の表からAとC(3社共にデータがある)ものだけを抽出したいのですが、回答条件が ●データ抽出にはオートフィルター機能は使用せず、関数で処理すること ●今まで習った知識で回答可能 とあり、まだ初心者クラスで、データ抽出に関係がありそうな関数はIF関数やVLOOKUPくらいしか思い当たらず、それらを使って抽出を試みたのですが「三社共にデータがあるものだけ抽出」という条件が処理出来ず、上手く出来ませんでした。 手詰まりで先に進めずに今、困っています。 そこで、Excelに詳しい方にアドバイス頂けたらと思い、書き込みしました。 宜しくお願いします。 文章力が低いので分かり辛い文面で、表も見辛くてすみません。
- ベストアンサー
- オフィス系ソフト
- エクセルの抽出結果表示について。
こんばんは。 エクセルのピポットテーブルを利用し、集計表を作成しています。 そこで質問なのですが、シート(1)には、 A B C D 1 伝票番号 お客様名 品名 数量 2 123 山田太郎 りんご 10 3 123 山田太郎 みかん 12 4 123 山田太郎 レモン 9 5 125 山田花子 りんご 11 6 125 山田花子 メロン 6 7 139 川田昭子 なす 5 8 139 川田昭子 トマト 6 ・ ・ ・ というような、データが入力されているとします。(日々入力されていく為、何千行になることも・・・) シート(2)には、このシート(1)より伝票番号からデータを呼び出し、順番に下の行に表示を加えいきたいのです。 そして、このシート(2)のデータを基にピポットテーブルを作成し、集計表を完成させたいのです。 いまは、シート(2)のような内容を2度手間のように、入力し集計表を作成しています。 ただシート(1)のような全体分のデータはあるので、利用したいし、手間を省きたいのです。 マクロとかになるのですかね? (あまり分からないけれど・・・) VLOOKUP関数を利用しようとしたのですが、例のように、同じ伝票番号に対して、複数行のデータがあるため、1行しか表示できないのです。 なにか、いい方法はありますか?
- 締切済み
- オフィス系ソフト
- 別シートに抽出
データを別シートに抽出したい。 画像を参照して下さい。 シートの「一覧」を別シートへ関数を使って抽出したのですが、 思うような結果になりません。お教え下さい。 別シートに VLOOKUP(A3,'一覧'!$A$3:$E$36,2,1) VLOOKUP(A3,'一覧'!$A$3:$E$36,3,1) VLOOKUP(A3,'一覧'!$A$3:$E$36,4,1) VLOOKUP(A3,'一覧'!$A$3:$E$36,5,1)の関数をセルに入れてセル事に 抽出します。その時に別シートに A01・A02・A03・A03・A04・・・・ の順序で抽出したいのですが、うまく抽出できません。
- ベストアンサー
- オフィス系ソフト
- Excel関数について教えて下さい。
データベースの中から2つの条件に合うデータの抽出がしたい。 例) A列に店舗番号、B列に職位、C列に名前があるデータベースの中から、 別ファイルで店舗番号別のマネージャー(職位)が誰であるかの一覧表を作成したい。 単純に、オートフィルタでデータ抽出はできるが、頻繁にデータベースの内容が変わるため、 常に最新のデータが作れるように、関数で一覧表をつくりたい。 IFとANDとVLOOKUPでいろいろと考えてみてますが、うまくいきません。。。 どうぞ宜しくお願いいたします。
- 締切済み
- オフィス系ソフト
- Excel(マクロ?VBA?)で最大日付の行を抽出
Excelのデータ抽出で困っております。 下記のようなデータがあり、各IDの最大日付の行を抽出しようとしていますが、抽出方法がわからず、困っています。 氏名 ID 日付 山田太郎 10 2012/12/01 山田太郎 10 2013/08/01 山田太郎 10 2014/12/01 山田太郎 10 2015/08/01 山田花子 20 2012/12/01 山田花子 20 2013/08/01 山田花子 20 2013/12/01 この際に、 山田太郎 10 2015/08/01 山田花子 20 2013/12/01 という2つの行を抽出したいですが、抽出方法がわかりません。 関数を使ってやろうとも思いましたが、関数をどれを使えばいいかわかりません。 別シートに抽出でも全く問題ありませんので、恐れ入りますが、抽出方法を教えてください。 よろしくお願いいたします。
- 締切済み
- Excel(エクセル)
お礼
ありがとうございます! 画像付きで分かりやすそうだったので一番に試してみたところ、 あっという間に解決しました! 数式の意味はこれからじっくり考えてみますが、本当に助かりました! 解決したのでベストアンサーにさせていただきます。 他の方もありがとうございました!