Excelで2つのbookから両方にあるやつを取得

質問

Excelで2つのbookから両方にあるやつを取得

Excelの使い方をあまり把握していないのですが、データのinner joinマッチングのようなことはできますか？
DBを使えば簡単なのですけど、Excelのvlookupやらなんやらでできないかなと思いまして。

例のようにbook1とbook2を基にbook3を作れますか？

book1.sheet1

A B
---- ----
1 A
2 B
3 C
4 D
5 E
6 F

book2.sheet1

A B
---- ----
2 a
3 b
4 c
7 d

book3.sheet1

A B C
---- ---- ----
2 B a
3 C b
4 D c

回答No.11

luka3

少し短くできました。

=IFERROR(INDEX([Book1.xlsx]Sheet1!$A:$A,SMALL(IF(ISNUMBER(MATCH([Book1.xlsx]Sheet1!$A:$A, [Book2.xlsx]Sheet1!$A:$A,0)),IF([Book1.xlsx]Sheet1!$A:$A<>"",ROW(A:A))),ROW(A1))),"")

回答No.10

luka3

FILTER関数はExcel2021からでした。

いろいろ試してとりあえず下記でできそうです。

=IF(INDEX([Book1.xlsx]Sheet1!$A:$A, SMALL(IF(ISNUMBER(MATCH([Book1.xlsx]Sheet1!$A:$A, [Book2.xlsx]Sheet1!$A:$A, 0)), ROW($A:$A)), ROW(C1)))=0, "", INDEX([Book1.xlsx]Sheet1!$A:$A, SMALL(IF(ISNUMBER(MATCH([Book1.xlsx]Sheet1!$A:$A, [Book2.xlsx]Sheet1!$A:$A, 0)), ROW($A:$A)), ROW(C1))))

A1に貼り付けたら、下へコピペまたはフィルしてください。

数値の入っている部分では正確に抽出できるのですが、空欄同士がマッチしてしまうためこれを除去するために煩雑になってます。
もう少しいい方法がありそうなのですが、時間がなく今はここまでです。

回答No.9

kkkkkm

book1.sheet1の結果と同じ結果出るような式をbook3.sheet1に作成します。book1.sheet1の式はいらなくなります。

book3.sheet1の
たとえばD1に
=IF(COUNTIF([Book2.xlsx]Sheet1!$A:$A,[Book1.xlsx]Sheet1!A1)>0,ROW(),"")
として下にコピー

A1に
=IFERROR(INDEX([Book1.xlsx]Sheet1!$A:$A,SMALL(D:D,ROW(A1)),1),"")
として下にコピー

としてください。

回答No.8

kkkkkm

> book3.sheet1のA1に下記を貼り付けましたが何も出ません

回答No.4の添付画像を見てもらったらできているのが分かると思いますが

回答No.3で回答している

> book1.sheet1の
> C1に(C1でなくても空いている何処かの列に)
> =IF(COUNTIF([Book2]Sheet1!$A:$A,A1)>0,ROW(),"")
>
> として下にコピーします。

これを実行しているでしょうか。
回答No.4の添付画像の一番上の状態になります
(Book2は実際のブック名に変更してください)

C列以外に設定したのでしたら
book3.sheet1の
A1の式で
$C:$Cはbook1.sheet1で追加した列を指定してください

お礼

ありがとうございます。

＞book1.sheet1の

それは見落としていました。
book1.sheet1に記述すると出ました。

ただ、参照側のbookを編集する必要があるとなると話が変わってくるので、book3.xlsx側でできる方法を引き続き募集します。

回答No.7

luka3

Excelでスピル機能及びFILTER関数が使えるのは、Excel 365およびExcel 2019からのようです。
お使いのバージョンを、教えてください。

補足

Excel 2019です。

2019 MSO(16.0.10414.20002) 64ビット

更新は最新の状態です。

回答No.6

luka3

B,C列は解決済みのようですので、A列だけ

A1:=FILTER([Book1.xlsx]Sheet1!$A:$A,ISNUMBER(MATCH([Book1.xlsx]Sheet1!$A:$A, [Book2.xlsx]Sheet1!$A:$A,0)), "重複なし")

お礼

ありがとうございます。

=FILTER([Book1.xlsx]Sheet1!$A:$A,ISNUMBER(MATCH([Book1.xlsx]Sheet1!$A:$A, [Book2.xlsx]Sheet1!$A:$A,0)), "重複なし")

Microsoft Excel
その関数は正しくありません。

というメッセージが出ました。

セルで=FIと打つと

FILTERXML
FIND
FINDB
FISHER
FISHERINV
FIXED
FINV

が候補に出てきます。

回答No.5

kkkkkm

全ての式はbook1、book2、book3が全て開いている状態での式になります

お礼

３つ開いた状態で

book3.sheet1のA1に下記を貼り付けましたが何も出ません

=IFERROR(INDEX([book1.xlsx]Sheet1!$A:$A,SMALL([book1.xlsx]Sheet1!$C:$C,ROW(A1)),1),"")

回答No.4

kkkkkm

> =IFERROR(INDEX([Book1]Sheet1!$A:$A,SMALL([Book1]Sheet1!$C:$C,ROW(A1)),1),"")
>
> と入れても何も出ませんでした。

Book1は実際のブック名Book1.xlsxとかを入れてください。

添付画像は上から

book1
book2
book3

です

回答No.3

kkkkkm

> book3.sheet1のA列はどうやったら求められますか？

book1.sheet1の
C1に(C1でなくても空いている何処かの列に)
=IF(COUNTIF([Book2]Sheet1!$A:$A,A1)>0,ROW(),"")

として下にコピーします。

book3.sheet1の
A1に
=IFERROR(INDEX([Book1]Sheet1!$A:$A,SMALL([Book1]Sheet1!$C:$C,ROW(A1)),1),"")
として下にコピーします($C:$Cはbook1.sheet1で追加した列を指定してください)

これで試してみてください。

お礼

book3.sheet1の
A1に
=IFERROR(INDEX([Book1]Sheet1!$A:$A,SMALL([Book1]Sheet1!$C:$C,ROW(A1)),1),"")

と入れても何も出ませんでした。

回答No.2

kkkkkm

回答No.1の追加です。

バージョンによっては

下にコピーせずに
B1だけに
=IFERROR(VLOOKUP(A1:A100,[Book1]Sheet1!$A:$B,2,FALSE),"")
C1だけに
=IFERROR(VLOOKUP(A1:A100,[Book2]Sheet1!$A:$B,2,FALSE),"")

としてもいけると思います。

A1:A100はbook3のA列で結果が必要な範囲を指定してださい。

お礼

ありがとうございます。

肝心のA列には何を指定すればよろしいですか？