• 締切済み
  • 困ってます

EXCELで別シートから値の一致しないデータを抽出する方法

Sheet1のA列に A1 123 A2 456 A3 789 のデータが入力されており、Sheet2のB列に B1 666 B2 123 B3 888 B4 999 B5 456 B6 888 のデータが入力されている場合に、 下の表のように、Sheet1のA4以降のセルにSheet2のB列から ・Sheet1のA1~A3に一致しないデータ ・Sheet2のB列で重複しないデータ ・降順 の3点の条件で抽出する方法を関数で教えてください。 よろしくお願いします。 Sheet1 A1 123 A2 456 A3 789 A4 999 <-ここからSheer2のデータでA1~A3以外の値でSheet2の重複をさけて降順で表示させたい A5 888 A6 666

共感・応援の気持ちを伝えよう!

  • 回答数6
  • 閲覧数3306
  • ありがとう数3

みんなの回答

  • 回答No.6

◆少し、式が長くなりましたが、作業列なしの方法です A4=IF(ROW(A1)>SUMPRODUCT((Sheet2!$B$1:$B$7<>"")*(COUNTIF($A$1:$A$3,Sheet2!$B$1:$B$7)=0)/COUNTIF(Sheet2!$B$1:$B$7,Sheet2!$B$1:$B$7&"")),"",LARGE(IF(FREQUENCY(Sheet2!$B$1:$B$7,Sheet2!$B$1:$B$7),INDEX(Sheet2!$B$1:$B$7*(COUNTIF($A$1:$A$3,Sheet2!$B$1:$B$7)=0),)),ROW(A1))) ★下にコピー

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • エクセルでシートにリンクを貼り付けているのですが元シートに行を挿入した

    エクセルでシートにリンクを貼り付けているのですが元シートに行を挿入した場合にリンク先シートの行も挿入になってほしいのですが・・・ 例えば Sheet1    A    B 1 Aさん  150 2 Bさん  270 3 Cさん  100 4 Dさん  300 5 Eさん  350 と入力し、Sheet2に以下の式?を入力した場合 Sheet2の式                   Sheet2の表示      A        B               A     B     1 =Sheet1!A1  =Sheet1!B1*2       1 Aさん  300 2 =Sheet1!A2  =Sheet1!B2*2       2 Bさん  540 3 =Sheet1!A3  =Sheet1!B3*2  ->  3 Cさん  200 4 =Sheet1!A4  =Sheet1!B4*2       4 Dさん  600 5 =Sheet1!A5  =Sheet1!B5*2       5 Eさん  700 このようになるかと思います。 Sheet1の3行目に行を挿入した場合ですが Sheet1    A    B 1 Aさん  150 2 Bさん  270 3 ←行の挿入→ 4 Cさん  100 5 Dさん  300 6 Eさん  350 Sheet2は以下のようになってしまします。 Sheet2の式                   Sheet2の表示      A        B               A     B     1 =Sheet1!A1  =Sheet1!B1*2       1 Aさん  300 2 =Sheet1!A2  =Sheet1!B2*2       2 Bさん  540 3 =Sheet1!A4  =Sheet1!B4*2  ->  3 Cさん  200 4 =Sheet1!A5  =Sheet1!B5*2       4 Dさん  600 5 =Sheet1!A6  =Sheet1!B6*2       5 Eさん  700 元シートに行を挿入した場合以下のようになってほしいのですが方法はございますでしょうか? Sheet2の式                   Sheet2の表示      A        B               A     B     1 =Sheet1!A1  =Sheet1!B1*2       1 Aさん  300 2 =Sheet1!A2  =Sheet1!B2*2       2 Bさん  540 3 =Sheet1!A3  =Sheet1!B3*2  ->  3  空白  空白 4 =Sheet1!A4  =Sheet1!B4*2       4 Cさん  200 5 =Sheet1!A5  =Sheet1!B5*2       5 Dさん  600 6 =Sheet1!A6  =Sheet1!B6*2       6 Eさん  700 因みにエクセルのバージョンは2003です。 いろいろと調べてみたのですがやり方を見つける事ができませんでした。 マクロを組めば出来そうな気もするのですがなにぶん素人なもので組み方もわからず困り果ててしまいました。 初歩的な質問で申し訳ございませんがどなたかご指導よろしくお願いします。

  • エクセル等でのデータ抽出

     こんばんは、愚生は、エクセルデータベースの全くの初心者でございます。以下の質問をさせていただきたく、ブログをたてたせてもらいました。 以下のようなA列に日付;B列に地名;C列に数値が載せたtableがございます。(行数は総計約5000程度まで続きます) A1: 2009/10/3, B1: 東京; C1: 12000 A2: 2009/10/3, B2: 大阪; C2: 10000 A3: 2009/10/3, B3: 名古屋; C3: 7000 A4: 2009/10/3, B4: 京都; C4: 3000 A5: 2009/10/3, B5: 金沢; C5: 500 A6: 2009/10/3, B6: 神戸; C6: 4000 A7: 2009/10/5, B7: 大阪; C7: 7000 A8: 2009/10/5, B8: 東京; C8: 8000 A9: 2009/10/5, B9: 京都; C9: 5000 A10: 2009/10/5, B10: 横浜 C10; 4000  A11: 2009/10/6, B11: 東京; C11: 9000 A12: 2009/10/6, B12: 大阪; C12: 7000 A13: 2009/10/6, B13: 名古屋; C13; 8000 A14: 2009/10/6, B14: 京都; C14: 4000 A15: 2009/10/6, B10: 横浜; C15: 5000 2009/10/3には計6個のデータがあります 2009/10/3には計4個のデータがあります 2009/10/6には計5個のデータがあります →   「各日にちごとに、C列の値が大きい順にベスト3ずつを選び、表示させたい訳でございます。」 A1: 2009/10/3, B1: 東京; C1: 12000 A2: 2009/10/3, B2: 大阪; C2: 10000 A3: 2009/10/3, B3: 名古屋; C3: 7000 」 A4: 2009/10/5, B6: 東京; C6: 8000 A5: 2009/10/5, B7: 大阪; C7: 7000 A6: 2009/10/5, B8: 京都; C8: 5000 」 A7: 2009/10/6, B9: 東京; C9: 9000 A8: 2009/10/6, B10: 名古屋 C10; 8000 A9: 2009/10/6, B11: 大阪; C11: 7000 」  もし、mysql (SQL server)等のデータベストソフトが必要な場合も、対処させていただきます。  それでは、よろしくお願い申し上げます。

  • VBAを使った、Excelでのシート間データ抽出

    はじめまして。みなさまどうか教えてください。 Sheet1にはA列に250行程、コードが存在します。 Sheet2にはA列(コード)からI列まで、そして1000行程データが存在します。 Sheet1にあるコードは重複はなく、Sheet2のコード内に必ず同じコードがあります。 Sheet2にも重複コードはありません。 そこでSheet1のコードを使い、Sheet2を検索し、同一コードのデータ(A列からI列の行すべて)を全て(250件分)、Sheet1のコード記載順(A1、A2、A3・・・・)で、Sheet3に抽出したいのです。 どうか、よろしくお願いします。

  • 回答No.5
  • tom04
  • ベストアンサー率49% (2537/5117)

こんばんは! すでに色々回答が出ていますが・・・ 作業列を使う方法の一例です。 ↓の画像で右側がSheet2になります。 Sheet2のC・D列を作業用の列にしています。 Sheet2のC1セルに =IF(AND(COUNTIF(Sheet1!$A$1:$A$3,B1)=0,COUNTIF($B$1:B1,B1)=1),ROW(A1),"") D1セルに =IF(COUNT($C$1:$C$100)<ROW(A1),"",INDEX($B$1:$B$100,SMALL($C$1:$C$100,ROW(A1)))) という数式を入れ、C1・D1セルを範囲指定し、D1セルにフィルハンドルで下へずぅ~~~!っとコピーします。 そして、Sheet1のA4セルに =IF(COUNT(Sheet2!$D$1:$D$100)<ROW(A1),"",LARGE(Sheet2!$D$1:$D$100,ROW(A1))) という数式を入れ、オートフィルでコピーすると 画像のような感じになります。 尚、数式は100行まで対応できるようにしてみました。 以上、参考になれば幸いですが、 他に良い方法があれば読み流してくださいね。m(__)m

共感・感謝の気持ちを伝えよう!

  • 回答No.4
  • KURUMITO
  • ベストアンサー率42% (1835/4283)

シート2のC列は作業列としてC1セルには次の式を入力して下方にオートフィルドラッグします。 =IF(B1="","",IF(COUNTIF(Sheet1!A$1:A$3,B1)>0,"",IF(COUNTIF(B$1:B1,B1)=1,B1,""))) シート1のA4セルには次の式を入力して下方にオートフィルドラッグします。 =IF(ISERROR(LARGE(Sheet2!C:C,ROW(A1))),"",LARGE(Sheet2!C:C,ROW(A1)))

共感・感謝の気持ちを伝えよう!

  • 回答No.3
  • imogasi
  • ベストアンサー率27% (4558/16316)

例の挙げ方だか、A1-A3と3つしか上がってない。これが現実のデータ数を反映したものか、簡略化したものか、でフィルタオプションの設定の条件の作れる可能性に影響する。 何百もあれば条件に書いていられないだろう。 その点どうか。 出来ることなら関数など使わずにエクセルの操作でやるほうが良いと思う。 関数なら作業列に一旦「A1-A3以外」か「A1-A3と一致」のコードを空き列に作ったほうが考えやすい。 ーー フィルタ利用  例データ Sheet1 123 456 789 ーー Sheet2 データ 666 123 888 789 999 456 888 789 123 666 945 124 ーーー Sheet2のE1:G2 データデータデータ <>123<>456<>789 ーー A1;A13を範囲指定して データーフィルターフィルタオプションの設定 指定した範囲 リスト範囲 A1;A13 検索条件範囲 E1:G2 抽出する範囲 I1:I13 重複するレコードは無視する 結果 I列 データ 666 888 999 945 124 あとI列でソート。 Sheet1のA4へコピペ(シート外に直接抽出することはあきらめて) ーーー 関数では 同じデータで Sheet2 A列    B列 データ 666666 123 888888 789 999999 456 888 789 123 666 945945 124124 B2の式は =IF(AND(COUNTIF(Sheet1!$A$1:$A$3,A2)=0,COUNTIF($B$1:B1,A2)=0),A2,"") 下方向に式を複写。 これをつめて集めるのは省略。 Googleでimogasi方式で照会すれば方法は出ている。 データ A列  B列   C列 6666661 123 8888882 789 9999993 456 888 789 123 666 9459454 1241245 のC列のように、上から連番を振る。 C2の式は =IF(AND(COUNTIF(Sheet1!$A$1:$A$3,A2)=0,COUNTIF($B$1:B1,A2)=0),MAX($C$1:C1)+1,"") を入れて縦方向に式を複写する。 この1、2・・をSheet1の「行数ー3」と関連づけてSheet2のA列データを引っ張ってくる。

共感・感謝の気持ちを伝えよう!

  • 回答No.2
noname#204879

Fig-1    Fig-2 Sheet2   Sheet1   B      A  B  C 1 data   1 123 2 666   2 456   TRUE 3 123   3 789 4 888   4 data 5 999 6 456 7 888 8 1.Sheet2 の上端に1行を[挿入]して、セル B1 に data と入力 2.Sheet2 のセル B1 を[コピー]して、それを Sheet1 のセル A4 に[貼り付け] 3.Sheet1 のセル C2 に式 =COUNTIF(A$1:A$3,Sheet2!B2)=0 を入力   (Sheet1 のセル C1 は空白のままにしておく!) 4.Sheet1 において、次の操作を実行  4.1.[データ]→[フィルタ]→[フィルタオプションの設定]  4.2.[抽出先]として“指定した範囲”を指定  4.3.[リスト範囲]  → Sheet2!$B$1:$B$7  4.4.[検索条件範囲] → $C$1:$C$2  4.5.[抽出範囲]   → $A$4  4.6.“重複するレコードは無視する”にチェック入れ  4.7.[OK]をクリック その結果を下左(Fig-3)に示す。 5行目以降を降順に[並べ替え]た後で、4行目を[削除] Fig-3 Sheet1   A 1 123 2 456 3 789 4 data 5 666 6 888 7 999

共感・感謝の気持ちを伝えよう!

  • 回答No.1
  • MackyNo1
  • ベストアンサー率53% (1521/2850)

A4セルに以下の数式を入力します。 =MAX(INDEX(ISNA(MATCH(Sheet2!$B$1:$B$6,$A$1:$A$3,0))*Sheet2!$B$1:$B$6,)) A5セル以下には以下の数式を入力し下方向にオートフィルします。 =LARGE(INDEX((ISNA(MATCH(Sheet2!$B$1:$B$6,$A$1:$A$3,0))*Sheet2!$B$1:$B$6),),RANK(A4,Sheet2!$B$1:$B$6)+COUNTIF(Sheet2!$B$1:$B$6,Sheet1!A4)) 配列数式なので、エラー処理の数式を含めるとると数式が複雑になるだけでなく計算負荷加が高くなる恐れがあるので省略していますが、もし必要なら条件付書式やIF関数で適宜エラー値を表示しない設定にしてください(現在は該当データがないとエラー表示となります)。

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • エクセルで重複したデータの処理をしたい

    お世話になります。 A列はランダムに数字が入っています。(並び替えはされていない) このA列の数字は重複するものがあります。 A列の中で重複する場合に、上から順に1/3,2/3,3/3などになるようにしたい。 例) A1:1 A2:2 A3:1 A4:3 A5:1 A6:2 A7:4 のとき 1は3つ出てくるので1/3,2/3,3/3 2は2つ出てくるので1/2,2/2  3,4は1つなので空白にする。 このときB列に分子を、C列には分母を入力する。 この例の場合、B1:1 C1:3 B2:1 C2:2 B3:2 C3:3 B4:空白 C4:空白 B5:3 C5:3 B6:2 C6:2 B7:空白 C7:空白 と入力されるようにするにはどういう計算式をB列、C列に入れておけばよいのでしょうか? Excel2002です。 実際はかなりの量があります。 よろしくお願いします。

  • Excelをデータベースのように使用した場合 データの抽出

    Excelをデータベースのようにして使用している場合について教えて欲しいことがあります。  ・Sheetを2つ作成したとします。  ・Sheet1はたくさんのデータが入っているシートです。  ・Sheet1の名前を仮に『Data』とします。  ・Sheet2はSheet1から必要なデータを抽出してきて並べるシートです。  ・Sheet2の名前を仮に『Report』とします。  ・『Data』のA列には1~200までの数字が入力されています。  ・その数字は ランダムである上に 重複していることもあります。  ・例えばA1には「1」が入力されていて A2には「2」が入力されています。  ・しかし その後A3にもA4にもA5にも「2」が入力されています。  ・同様にA6~A15までは「3」が入力されているのです。  ・このようにA列の数字は 同じ数字が何度も重複しており その重複には規則性はありません。  ・『Report』シート上で『Data』シートからデータを抽出したいのですが A列に「1」と入力されているデータだけを取り出すのなら「VLOOKUP」でもできますよね・・・・  ・しかし A列に「2」と入力されているデータを「VLOOKUP」関数で選んでも その先頭行のデータしか読み込んできません。  ・『Data』シートのA列に入力している数字から 必要な数字が入力されている行のデータだけを 全て『Report』シートに並べようとした場合 手作業で行うとすると・・・ 『Data』でA列が「1」のデータだけを選び,それを『Report』シートに貼り付ける。次は「2」で実行する。・・・・・という作業を200回繰り返すことになりますが 簡単に行うにはどのようにすればいいのでしょうか? マクロについては あまり詳しくないので できれば関数を使いたいのですが 無理でしょうか? マクロを使うのであれば 分かりやすく教えていただきたいのですが・・・ 勝手なお願いで申し訳ありませんが よろしくお願いします。

  • Excelでシート間のデータの抽出方法

    1つのブックに集計用のシートと、A店、B店、C店という各店舗別の売上シートがあります。 Sheet集計用のA1にA店のコード(A店は001、B店は002というようなコード)を入れると、Sheet集計用のB列の上期売上、C列の下期売上にSheetA店のB列の上期売上、C列の下期売上のデータが入るようにすることは可能でしょうか? Sheet集計用のA1のコード変更することによってデータを引っ張ってくるシートが変わる様にしたいということなんですが、Excelでどのようにすればいいかわかりません。 各店舗のシートのA1のセルにも、その店舗のコードが入力されています。

  • Excel シート間のデータ抽出と置き換え

    AとBのシートを比較して、 Aと同じ単語のあるセルがBにもあった場合には、 該当単語のある行から、特定セルの内容を、 Bのシートの該当行(同単語のある行)の特定場所にコピーする。 説明がヘタですみません。 例を挙げますと、 <Aのシート> A1:あ B1:Goo A2:い B2:Yahoo A3:う B3:Google <Bのシート> A1:あ B1:データなし A2:か B2:データなし A3:さ B3:データなし この様な場合、 AシートA1のデータ"あ"は、BシートのA1にもありますので、 その場合には、B1データの"Goo"を BシートのB1にコピーし <Bのシート> A1:あ B1:Goo A2:か B2:データなし A3:さ B3:データなし と、なるようすることを関数だけで行うことは可能なのでしょうか。 自分なりに色々調べてみたのですが、該当例を探すことができませんでした。 ご存知の方がいらっしゃいましたらご教授ください。 補足説明といたしまして、 データは全て文字です。 B1のシート内で検索データが重複することはありません。 検索語(検索対象語)の文字列にスペース(半角・全角共)含まれていた場合 そのスペースは検索対象から除外(無視)して認識する。 検索語は複数あります。 できれば、一括で行いたいのですが、それですとVBAでないと無理でしょうか。 宜しくお願いいたします。 教えてGooさんの枠を超えた質問であれば申し訳ありません。

  • エクセルの関数 反映の仕方

    sheet1のA1→10 A2→11 A3→12 A4→13 A5→14 A6→15 A7→16 A8→17 A9→18 A10→19 sheet2にA1→11 B1→3,A2→14 B2→2,A3→16 B3→3 A4→18 B4→1 とあった場合 Sheet2のB1~B4の数をsheet1の数の横(B列)に反映させたいのですがどうやったら出来るでしょうか? 分かりにくかったら補足しますので宜しくお願いします

  • エクセル 関数

    エクセル画面です       A     B     C 1 2     C 3    =Sheet1!B3 4    =Sheet1!B4 5    =Sheet1!B5 6    =Sheet1!B6 現在の画面は Sheet2 であってSheet1のB列のデーターを 読み込んでいます。このB列をC列に変更したい場合は、編集→ 置換で行いますが、セルA2にCを入力することでA3からA6 の式のB列をC列に置換え出来ないでしょうか

  • こんな表のExcelグラフの作成方法について

    下記のような表はどのようにグラフを作ればよいでしょうか。 表の範囲はA1からB7です。 B2に"回数"と入力 A2とA3、A4とA5、A6とA7はそれぞれ結合されています。 B2とB3、B4とB5、B6とB7はそれぞれ結合されています A2とA3の結合されたセルに"aさん" A4とA5の結合されたセルに"bさん" A6とA7の結合されたセルに"cさん" Bの列も同様な形でたてに"5","7","9"が入っています。        回数 aさん    5 bさん    7 cさん    9 グラフを作ろうとしましたが結合されたセルであるにもかかわらず、グラフを作ると凡例が"aさん"の行以下6列分出来ています。 このような場合にグラフをつくれる方法があれば教えてください。 よろしくお願いいたします。

  • 複数シートのデータをまとめたい

    複数のシートを下の例のように一つにまとめたいのですが、どうすればよいでしょうか? 注)見づらい例で申し訳ありません。 --------------------------------(例)---------------------------- [sheet1]と[sheet2]のデータを[sheet3]に出力させたい。 ※例では[sheet1][sheet2]共,5行目までしかデータがありませんが、  これより少ない時も多い時もあります。 [sheet1] (A1):担当者A (A2):項目A (B2):項目B (C2):項目C (D2):項目D (A3):テキスト1 (B3):15 (C3):2 (D3):テキスト4 (A4):テキスト2 (B4):3 (C4):1 (D4):テキスト5 (A5):テキスト3 (B5):4 (C5):2 (D5):テキスト6 [sheet2] (A1):担当者B (A2):項目A (B2):項目B (C2):項目C (D2):項目D (A3):テキスト7 (B3):5 (C3):3 (D3):テキスト10 (A4):テキスト8 (B4):7 (C4):2 (D4):テキスト11 (A5):テキスト9 (B5):9 (C5):4 (D5):テキスト12 [sheet3] (A2):項目A(B2):項目B(C2):項目C(D2):項目D(E2):担当者 (A3):テキスト1(B3):15(C3):2(D3):テキスト4(E3):担当者A (A4):テキスト2(B4):3(C4):1(D4):テキスト5(E4):担当者A (A5):テキスト3(B5):4(C5):2(D5):テキスト6(E5):担当者A (A6):テキスト7(B6):5(C6):3(D6):テキスト10(E6):担当者B (A7):テキスト8(B7):7(C7):2(D7):テキスト11(E7):担当者B (A8):テキスト9(B8):9(C8):4(D8):テキスト12(E8):担当者B ----------------------------------------------------------------

  • エクセルの空白を詰めて別シートに表示

    sheet1のデータを参照して、空白を詰めてsheet2へ表示させたいんです! sheet1(元データになるもの)  A列 |B列|C列・・・ 1行目A|100|100 2行目B|  |200 3行目C|100| 4行目D|  |200 5行目E|100| 6行目F|  |100 ・ ・ sheet2(sheet1でB列に入力があるものを抽出)  A列 |B列 1行目A|100 2行目C|100 3行目E|100 sheet3(sheet1でC列に入力があるものを抽出)  A列 |B列 1行目A|100 2行目B|200 3行目D|200 4行目F|100 sheet2のA1に下記の式を入力してA列とB列に数式をコピー =IF(COUNT(Sheet1!$B$1:$B$6)<ROW(A1),"",INDEX(Sheet1!A$1:A$6,SMALL(IF(Sheet1!$B$1:$B$6<>"",ROW($A$1:$A$6)),ROW(A1)))) すると下記のように表示されます。 A1=A     B1=100 A2=#NUM! B2=#NUM! A3=#NUM! B3=#NUM! 4行目から空白 sheet2のA2のところにエラーが出ていますが、「関数の引数」のところで「数式の結果」には「100」と 正解が表示されています。(B2、A3、B3も同様に)数式の結果のところには正解が表示されています。 答えの「#NUM!」のところに正解を表示させるには、どうしたらいいですか? 見よう見まねでつくったのもで。。。関数にあまり詳しくありません。 よろしくお願い致します。

  • excel:別シートの値を飛び飛びで参照させたい

    エクセルです。 数値データが1列に2千個並んでます。 その数値データを別シートから5個おきに参照しようとしてます。通常なら =sheet1!A1 =sheet1!A6 =sheet1!A11 のように入力していけばいいのですが、元データが2千個なので参照する方も400個と大量にあります。一個ずつ入力したくはありません。一気に済ませる方法はないでしょうか?