![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
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.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)))
★下にコピー
![「EXCELで別シートから値の一致しないデ」の回答画像6](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/5/384385_5497ed9e5ff72/M.jpg)
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
![「EXCELで別シートから値の一致しないデ」の回答画像5](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/6/667667_5497dff1b724b/M.jpg)
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.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.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.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関数で適宜エラー値を表示しない設定にしてください(現在は該当データがないとエラー表示となります)。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- その他(プログラミング・Web制作) python文字化けエラーが発生しているようです 3 2022/04/13 19:41
- Excel(エクセル) VBAにてエクセルをpdf化する方法 1 2023/03/10 16:20
- Visual Basic(VBA) このプログラムなんですがsheetにデータを置いて表示できるようにしてありますがsheetに101を 2 2023/02/23 20:13
- Excel(エクセル) マクロ セルの選択 5 2022/08/12 22:47
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Visual Basic(VBA) Sheet「状況」から、分類の年齢別カウント数をSheet「D表」へ転記する下記マクロを作っています 7 2022/12/14 17:57
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルでファイルの最終更新...
-
Excelに詳しい方! B列が「日...
-
F9キーについて。
-
Excelファイルが開けません
-
シフト表をエクセルで作るとき...
-
Excel関数について教えてくださ...
-
Excel 小さくなったスクロール...
-
計算能力
-
excel2013 MonthDays 関数が使...
-
スプレッドシートの関数につい...
-
Excel 2019 [オプション]の[リボンのユ...
-
Excelのピボットグラフの作り方...
-
エクセルで 自動的に◯や数字を...
-
【EXCEL】画像の黄色部分の抽出...
-
特定の文字列を含む、住所を抽...
-
EXCELの散布図で日付が1900年に...
-
【マクロ】2回実行したら、エ...
-
Excelで表を作ったところに文字...
-
マイクロソフトのPADを使ってい...
-
Excel分数の表示について
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報