• ベストアンサー

表のある番号のものだけ抽出する

－-－-－- 01|a|あ －-－-－- 01|b|う －-－-－- 01|c|お －-－-－- 02|d|き －-－-－- 02|e|け －-－-－- 上記のような表があったとします。 別のシートの、とある一箇所に例えば「02」と入力すると、 -－- d|き -－- e|け -－- と、出るような表を作りたいと考えています。 それぞれ「d」「e」「き」「け」と入力されている部分には どのような式を入力すればいいでしょうか。 もちろん「01」と入力したら、以下のように出るようにしたいです。 -－- a|あ -－- b|う -－- c|お -－- 元となるデータの行は1000行くらいで、 抽出したい行数は最小で1行、最大で20行くらいです。 よろしくお願いいたします。

質問者が選んだベストアンサー

  • ベストアンサー
  • mu2011
  • ベストアンサー率38% (1910/4994)
回答No.3

Ｎｏ１です。 すみません一部範囲に記載誤りがありましたので、次の数式をお試し下さい。 =IF(ROW($A1)-1<COUNTIF(Sheet3!$A$1:$A$1000,$A$1),INDEX(Sheet3!B$1:B$1000,SMALL(IF(Sheet3!$A$1:$A$1000=$A$1,ROW(Sheet3!$A$1:$A$1000),999),ROW($A1))),"")

sasakuri
質問者

お礼

ありがとうございます。 できるようになりました！

その他の回答 (2)

  • rin01
  • ベストアンサー率43% (33/76)
回答No.2

こんにちは～♪ こんな方法もあります。。。 =IF(COUNTIF(Sheet3!$A:$A,$A$1)<ROW(A1),"",INDEX(Sheet3!B:B,SMALL(INDEX(SUBSTITUTE((Sheet3!$A$1:$A$1000=$A$1)*1,0,10^5)*ROW($1:$1000),),ROW(A1))) 普通に、Enter 右と下へコピーしてください。。 。。。Ms.Rinでした～♪♪

sasakuri
質問者

お礼

ありがとうございます。

  • mu2011
  • ベストアンサー率38% (1910/4994)
回答No.1

表範囲がシート３のA1:C1000、抽出シート１の番号入力をA1セルとします。 以下の数式を任意セルに入力後、縦横にコピーしてお試し下さい。 尚、配列数式を使用していますので、入力完了時にshift＋ctrl＋enterと同時押下して下さい。 =IF(ROW($A1)-1<COUNTIF(Sheet3!$A$1:$A$1000,$A$1),INDEX(Sheet3!B$1:B$1000,SMALL(IF(Sheet3!$A$1:$A$1000=$A$1,ROW(Sheet3!$A$1:$A$6),999),ROW($A1))),"")

sasakuri
質問者

お礼

ありがとうございます。 いただいた式だと、シート3に以下のような表を用意した場合、 1 aa あああ 1 aa あああ 1 aaaf たたあ 2 a たたあ 2 a たたたた 2 a ららら 2 a あらら 2 b あらららら 3 b じゃあ 4 b はあああ 4 bcc あああ 4 cc だだだ 「1」をシート1のA1に入力するとできたので、3くらいから先はどうしても#N/Aになってしまいます。 どこを直せばいいでしょうか？ よろしくお願いいたします。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • 関数で２つの条件から抽出する表を作りたい。

    A1は手入力D4手入力、H2は=D2&C2 担当者と週で検索して抽出する式を教えて下さい。 上手く出来なくて１行しか抽出しないんです。 　A　　　B　　C　　　　　　D　　　E　　　F　　G　　　H １担当者　沼田　週　　　　1 ２ 番号 日付 週 担当 企業 名前 住所 検索用 ３　1 7月1日 1 沼田 a 高橋 土浦 沼田1 ４　2 7月2日 1 佐藤 a 山田 阿見 佐藤1 ５　3 7月2日 1 沼田 r 沖田 水戸 沼田1 ６ 4 7月7日　 1 沼田　c 斎藤 土浦 沼田1 ７　5　 7月9日 2 佐藤 h 鈴木 牛久 佐藤2 ８　6 7月14日 3 佐藤 d 河合 土浦 佐藤3 ９　7 7月15日 3 高橋 e 吉田 阿見 高橋3 10　8 7月20日 4 高橋 e 木田 水戸 高橋4 11 9 7月4日 1 沼田 g 飯田 牛久 沼田1 上記の表から１週目だったら、１週目の担当者のデータをシート２の表に抽出 週２だったら２週目の担当者データを抽出 A1・D1は手入力 A3は=IF(COUNTIF(Sheet1!D$2:D$1000,B$1)=0,"",MATCH($B$1&$D$1,Sheet1!$H$2:$H$1000,0)) シート２ 　　　 A 　　B　 　C 　D 　E 　F 　G １ 　担当 沼田 週 1 ２ 　番号 日付 　週 　担当　 企業 　名前 　　住所 ３　　1　 7月1日 　1 　沼田 　　a 　　 会田 　　牛久 ４　　4 　7月2日　 1　 沼田　　 r　　　沖田　　 水戸 ５　　6 　7月7日　 1　 沼田　　 c　　　斎藤　　土浦　 ６　　9　 7月4日　 1 　沼田 　　g　　　飯田　　牛久 上記の様にシート２に表示したいんです。 細かく教えて下さい。何度やっても１行目のデータしか抽出しないんです。 A3=IF(COUNTIF(Sheet1!D$2:D$1000,B$1)=0,"",MATCH($B$1&$D$1,Sheet1!$H$2:$H$1000,0)) B3=IF($A3="","",IF(VLOOKUP($A3,Sheet1!$A$2:$H$1000,COLUMN(B1),0)="","",VLOOKUP($A3,Sheet1!$A$2:$H$1000,COLUMN(B1),0))) A4の２行目以降1になってしまって同じ物しか出ないんです。 Aの番号の抽出方法の式を教えて下さい。

  • ２つの条件からシート２に抽出

    シート１ A1は手入力D4手入力、H2は=D2&C2 担当者と週で検索して抽出する式を教えて下さい。何度もすみません。 上手く出来なくて１行しか抽出しないんです。 上記の表から１週目だったら、１週目の担当者のデータをシート２の表に抽出 週２だったら２週目の担当者データを抽出 A1・D1は手入力 A3は=IF(COUNTIF(Sheet1!D$2:D$1000,B$1)=0,"",MATCH($B$1&$D$1,Sheet1!$H$2:$H$1000,0)) 上記の様にシート２に表示したいんです。 細かく教えて下さい。何度やっても１行目のデータしか抽出しないんです。 A3=IF(COUNTIF(Sheet1!D$2:D$1000,B$1)=0,"",MATCH($B$1&$D$1,Sheet1!$H$2:$H$1000,0)) B3=IF($A3="","",IF(VLOOKUP($A3,Sheet1!$A$2:$H$1000,COLUMN(B1),0)="","",VLOOKUP($A3,Sheet1!$A$2:$H$1000,COLUMN(B1),0))) A4の２行目以降1になってしまって同じ物しか出ないんです。 Aの番号の抽出方法の式を教えて下さい。

  • エクセル　別シートから一覧を抽出したい

    部品一覧表を作成しているのですが、2シートあり 1入力シート・2部品コードシートとあります。 部品コードシートにはB列に部品番号・C列部品名・D列部品番号・E列部品名と2行ずつ使い番号・品名がB～Wまで47行分入力されています。 B1:C47、D1:E47…と2列47行にはそれぞれ【A】、【B】、…とセルの名前の定義をつけました。現在【H】までありますが、今後増える可能性があります。 入力シートに、セルの名前を指定したときに部品コードのシートから 一覧を抽出したいのですが、どの関数を使えばよいのかわかりません。 入力シート　　　　　　　　　　 　　　 　　｜部品コードシート G　　　H　　　　　　　　　　　　　　　　　 ｜ A　Bコード　C部品名 　Dコード　E部品名 4式入力用にあけています。 　　　　｜1　1800　　ユニットA　　　1501　電源A 5コード　部品名　　　　　　 　　　　　　｜2　1801　　ユニットB　　　1502　電源B 6　　　　　　　　　　　　　　　　　 　　　　 ｜3 G4に関数を入れてG6～G52まで部品コードのシートA1～A47を一気に表示 させたいのです。部品コードシートの行数が変わることはありません。 マクロを使わないと、関数では難しいでしょうか？

  • データ表より抽出・集計について

    いつもお世話になります。先日、質問した内容ですがわからないなどの指摘を頂きましたので反省して再質問いたします。 ・元データ表：概略　WORK2.XLS 　　A　　　　　B　　　　　C　　　　　D 1　日付　　　 2　　　　　　不良１　　不良２　　不良３ 3　　　　　　　15　　　　　60　　　　　25　　　　　 4　　　　　　　0.15　　　　0.6　　　　0.25　　　　 5　12/1 　　　5 　　　　　 25　　 　 　5 6　12/2 　　　3 　　　 　　15 　 　　　3 7　12/3 　　　2 　　　　　15 　 　 　 5 8　12/4 　　　5 　　　　　　5 　 　 　2 1行：日付名称　2行：不良項目　3行：不良項目合計数　4行：不良項目発生率　5行以下：日付別発生数　を表しています。 ・抽出 上記表よりワースト不良発生率を抽出 不良２　　　0.6%　　→　LARGE・INDEX関数により：完了 ・日付別の発生数を表にしたい 　　　　A　　　　　　　B 1 　12/1　　　　　　25 2 　12/2　　　　　　15　 3 　12/3　　　　　　15 4 　12/4　　　　　　5 B1=SUMIF('WORK2.xls]sheet1'!$A:$A,A1,'[WORK2.xls]sheet1'!$C:$C) 下へオートフィルで完成しますが、式の$C:$C（不良項目）は抽出した不良項目により変わるので上の式で固定はできません。（そのたびに変えるのは避けたい） 抽出した不良項目に対応出来るようにするにはどうすればよいのでしょうか？　教えてください。　

  • Excelの表から値を抽出

    ---------- |a|b|c|あ| ---------- |a|b|d|い| ---------- |a|e|f|う| ---------- 少々判りにくいかもしれませんが、上記の3行4列の表があったとします。 1列目が「a」、2列目が「b」、3列目が「d」を条件として、4列目「い」を返す式を作るにはどのような式がいいでしょうか？ 説明がわかりにくいかもしれませんが、お願いします。

  • エクセルの表から条件にあった数字が入力されているセルの行数を抽出する

    A1からD100まで任意の数字が入力されている表があります。 E1にある数字を入力したとき、A1からD100までの表を1行目から順に、2行目、3行目と検索していき、E1の数字よりも小さな数字がはじめて出てきた行数をE2に自動的に入力するようにしたいのですが、関数を使ってそのようなことは可能でしょうか。

  • エクセル抽出に関して教えて下さい。

    エクセル初心者なんですが、抽出について教えて下さい！ 例 シート(1)　 　　　A　　B　　C　　D　　E 1　　8 2　　　　■ シート(2) 　　　A　　B　　C　　D　　E 1　　4　　5　　6　　7　　8 2　　1　　1　　3　　7　　2　　 3　　5　　6　　7　　1　　2 シート(1)のB2に シート(1)のA1に入った数字を シート(2)のA1～E1の中で探して 該当した数字の列の3行目をシート(1)のB2に表示させる。 すみません、意味不明だったらごめんなさい。 ご理解いただける方、よろしくお願い致します。

  • データの抽出について

    エクセルにてデータの抽出をしたいのですが、わからないので教えてください。 例えば、下の様なデータがあります。 回数　　　１回目　　　２回目 　　3回目 　　4回目 1000　　　　あ　　　　　　a　　　　　　Ａ　　　　　　０ 1001　　　　い 　 　　　　b 　　　　　　Ｂ　　　　　　１　 1002　　　　う 　 　　　　c　 　　　　　Ｃ　　　　　　２ 1003　　　　え 　　　　　d 　　　　　　Ｄ　　　　　　３　　 1004　　　　お 　　　　　e 　　　　　　Ｅ　　　　　　４　 1005　　　　か 　　　　　f 　　　　　　Ｆ　　　　　　５ 別のシートに、 回数の１００１と入力すると、３回目のＢが抽出 　　　１００３と入力すると、３回目のＤが抽出 　　　１００５と入力すると、３回目のＦが抽出出来る様にしたいです わかりにくい文章ですいませんが、ご回答よろしくお願いします。　

  • 同じ様式の表の複数シートから行ごと抽出

    A2:K12の範囲に表がある全く同じ様式のシートが複数あります。 特定の条件が満たされた行を全て別シートに抽出したいです。 1列目と2列目とX列目(これは集計のA1セルに入った値を参照)が空白でない行を全て抽出したいです。1列目と2列目は1行目から順番に埋まっているのですが、X列目は空白になっているものと空白でないものが混在しています。 たとえば、Xが10列目のときは、Aさん、Bさん、Cさん、Dさん、Eさんの行が抽出されるようにしたいです。 VBAのコードを自力で書けないので、よろしくお願いします。

  • 抽出した表でデータをカウントしたいのですが・・・

    タイトルにあるとおり、あるデータをオートフィルで抽出した状態で、その抽出結果からデータをカウントしたいのです。 以下のような表があったとします。 　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　　Ｆ　　Ｇ　　Ｈ　　Ｉ １　　○　　　　　　　　　　　○　　○　　○ ２　　　　　○　　○　　　　　○　　　　　　　　○ ３　　　　　　　　○　　○　　○　　○ ４　　○　　○　　　　　○　　　　　○ ５　　　　　○　　　　　　　　　　　　○　　○　　　　○ ６　　　　　○　　○　　　　　○　　　　　　　　○ ７　　○　　　　　　　　○　　　　　　　　○　　　　　○ ８　　　　　○　　○　　　　　○　　　　　　　　○ ９　　○　　　　　　　　○　　○　　　　　○ この表では、仮に各行○が4つとして、ランダムに各列に○が振り分けられています。 Ａ列・4個、Ｂ列・5個、Ｃ列・4個、Ｄ列・4個、Ｅ列・6個、Ｆ列・4個、Ｇ列・4個、Ｈ列・3個、Ｉ列・2個 という状態です。 　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ　　　Ｆ　　Ｇ　　Ｈ　　Ｉ １　　○　　　　　　　　　　　○　　○　　○ ２　　　　　○　　○　　　　　○　　　　　　　　○ ３　　　　　　　　○　　○　　○　　○ ４　　○　　○　　　　　○　　　　　○ ５　　　　　○　　　　　　　　　　　　○　　○　　　　○ ８　　　　　○　　○　　　　　○　　　　　　　　○ ９　　○　　　　　　　　○　　○　　　　　○ ある条件でオートフィルによって、抽出した結果が上の表です。 今回の例では6,7行が消えた状態だったとします。 それぞれ、Ａ列・3個、Ｂ列・4個、Ｃ列・3個、Ｄ列・3個、Ｅ列・5個、Ｆ列・4個、Ｇ列・3個、Ｈ列・2個、Ｉ列・1個 と、いう結果になりました。 このような状態になった表で最下段、この場合9から上に向かって3行分の○をカウントするような方法、または便利な関数はありますでしょうか？ 今回の例ですと、最下段から、3行分、5、8、9のそれぞれの列の○をカウントするという感じです。 Ａ列・1個、Ｂ列・2個、Ｃ列・1個、Ｄ列・1個、Ｅ列・2個、Ｆ列・1個、Ｇ列・2個、Ｈ列・1個、Ｉ列・1個 これが今回の例における求めたい結果です。 抽出してない状態でしたら、ＯＦＦＳＥＴ関数でいけるのですが、抽出した状態からだと私のスキルでは解決できません。 どなたか、ご存知の方ご教示よろしくお願いします＾＾；

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する