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
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.1
- 回答日時:
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関数で適宜エラー値を表示しない設定にしてください(現在は該当データがないとエラー表示となります)。
No.2
- 回答日時:
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.3
- 回答日時:
例の挙げ方だか、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.4
- 回答日時:
シート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.5
- 回答日時:
こんばんは!
すでに色々回答が出ていますが・・・
作業列を使う方法の一例です。
↓の画像で右側が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.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が見つからない時は、教えて!gooで質問しましょう!
このQ&Aを見た人はこんなQ&Aも見ています
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
あなたが普段思っている「これまだ誰も言ってなかったけど共感されるだろうな」というあるあるを教えてください
-
映画のエンドロール観る派?観ない派?
映画が終わった後、すぐに席を立って帰る方もちらほら見かけます。皆さんはエンドロールの最後まで観ていきますか?
-
海外旅行から帰ってきたら、まず何を食べる?
帰国して1番食べたくなるもの、食べたくなるだろうなと思うもの、皆さんはありますか?
-
天使と悪魔選手権
悪魔がこんなささやきをしていたら、天使のあなたはなんと言って止めますか?
-
Excelで[表1]にあって、[表2]にないものを抽出する関数
その他(Microsoft Office)
-
EXCELで2つのシートから一致しない情報だけ抽出
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
ExcelでASCを使って全角を半角...
-
今まで文字化けなく開けていたc...
-
Microsoft 365Excelの見開きペ...
-
Excel関数について教えてくださ...
-
マクロの処理が遅くなった
-
スプレッドシートで指定された...
-
エクセルの質問です。 F列からL...
-
Excel関数について教えてくださ...
-
エクセルのセル内に分数などの...
-
作成した数式を値として表示し...
-
ワークシートに出現したこの画...
-
条件付き書式設定で罫線を引き...
-
EXCELの散布図で日付が1900年に...
-
シートの情報を別のシートへま...
-
Excelでの文字色
-
【マクロ】VLOOKUPにて参照元に...
-
エクセルの文字が途中から消える
-
エクセルの数式バーのフォント...
-
エクセルでファイルの最終更新...
-
OFFSET関数を使用したいのです...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excelの警告について
-
Excelで数値を時間数に変換する...
-
エクセルの数式バーのフォント...
-
エクセルで数字の組み合わせを...
-
エクセルを使用して、円周率を...
-
Excelで特定の文字列が含まれて...
-
Excel 対象のセルに入力が無い...
-
任意の値が存在する行に名前を...
-
エクセルでファイルの最終更新...
-
index関数の説明をお願いします。
-
条件付き書式でやりたいのですが
-
重複しない値を取り出したい
-
【ExcelVBA】UTF-8(BOM無)でC...
-
【マクロ】マクロが割当てされ...
-
エクセル IF計算式?でしょうか?
-
エクセルで曜日を入れたい
-
表中の指定した条件の文字列を...
-
【Excel】版が同じ事を示す番号...
-
EXCELの散布図で日付が1900年に...
-
Excelについて。Excelに縦1列に...
おすすめ情報