質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

Q&A

解決済

1回答

567閲覧

スプレッドシートで順不同のデータを照合して引き出したい

ISPEI

総合スコア12

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

0グッド

0クリップ

投稿2023/03/19 03:52

実現したいこと

文字列のみのシートデータが順不同と別シートと照合して該当列のデータを抽出したい。

発生している問題・エラーメッセージ、試したこと

今INDEX関数とMATCH関数を使ってますが、検索値の文字列が見つからないというエラーが出ました。
フォントや文字数、スペース空白など調べましたがすべて一致してます。

また、試しに出力先シートと照合先シートのデータ順番を揃えたところ、上から順番に取っていることが分かりました。

該当のソースコード

=IFNA(INDEX('抽出シート'!N:N,MATCH(N41&E41,'L抽出シート'!L:L&'抽出シート'!M:M,0),1),"")

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

検索値が見つからないというエラーが出ている理由の1つは、配列式を使用していないことです。以下のようにArrayFormula関数を使って、配列式を使用しましょう。

=IFNA(INDEX('抽出シート'!N:N,MATCH(N41&E41,ArrayFormula('L抽出シート'!L:L&'抽出シート'!M:M),0),1),"")

ArrayFormula関数を使用することで、L抽出シート'!L:Lと'抽出シート'!M:Mを結合した配列が正しく作成され、MATCH関数で検索することができます。

もし、これでも検索値が見つからない場合は、以下のことを確認してください。

検索値が'L抽出シート'!L:Lと'抽出シート'!M:Mの結合された値に実際に存在していることを確認してください。
データの型(数値や日付など)が一致していることを確認してください。
文字列として一致させるためには、TEXT関数を使ってデータ型を揃える必要があります。

投稿2023/03/19 03:58

quiz

総合スコア258

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

ISPEI

2023/03/19 04:19

回答ありがとうございます。やってみましたが、結果変わらずでした。また、TEXT関数やデータの型も確認しましたが、一致してます。ちなみにしたがTEXT関数を入れた式です。あと、L抽出シートは間違えでした。そこも修正してやってみましたが結果変わらずでした。 =INDEX('抽出シート'!N:N,TEXT(MATCH(N29&E29,ArrayFormula('抽出シート'!L:L&'抽出シート'!M:M),0),1))
quiz

2023/03/19 04:27

=IFERROR(INDEX('抽出シート'!N:N, MATCH(N29&E29, ARRAYFORMULA(TEXT('抽出シート'!L:L, "0") & TEXT('抽出シート'!M:M, "0")), 0)), "") この式では、IFERROR関数を使用して、一致するデータが見つからない場合にエラーを回避し、空文字列("")を返します。また、TEXT関数をARRAYFORMULAの中に入れて、各セルのフォーマットを合わせています。 ただし、この式でも結果が変わらない場合、問題はデータやその他の要素にあるかもしれません。 その場合、以下の点を再確認してください。 1.セルN29とE29のデータが正しいかどうか。 2.'抽出シート'!L:Lおよび'抽出シート'!M:Mのデータが正しいかどうか。 3.データが正しく一致している場合でも、フォーマット(例: 数値の桁数、日付の形式)が異なる可能性があるので、それを確認してください。
ISPEI

2023/03/19 05:14

結果変わらずでした。アドバイス通り1,2,3を確認しましたが問題ありませんでした。 試しに直接順番を整えると結果反映されます。
quiz

2023/03/19 05:18

なるほど、それでは別のアプローチを試してみますか。 QUERY関数を使って、条件に一致する行を抽出することができます。 この方法では、別シートからデータを検索し、該当するデータを抽出することができます。 以下のようにして、QUERY関数を使って試してください。 =QUERY('抽出シート'!A:N, "SELECT N WHERE L = '"&N41&"' AND M = '"&E41&"'") スプレッドシートの問題が解決しない場合、別のアプローチを試してみましょう。 QUERY関数を使って、条件に一致する行を抽出することができます。この方法では、別シートからデータを検索し、該当するデータを抽出することができます。以下のようにして、QUERY関数を使って試してください。 less Copy code =QUERY('抽出シート'!A:N, "SELECT N WHERE L = '"&N41&"' AND M = '"&E41&"'") この式では、'抽出シート'のA:N列のデータ範囲から、L列がN41の値と一致し、M列がE41の値と一致する行のN列の値を抽出しています。 この方法で試しても結果が変わらない場合、シートの名前やセルの参照が正しいかどうか、もう一度確認してみてください。 また、検索するデータに非表示のスペースや改行が含まれていないかどうか確認してください。 それでも問題が解決しない場合は、スプレッドシートの具体的なデータや設定を共有していただくことで、分かる可能性があります。
ISPEI

2023/03/19 05:27

ありがとうございます。実行した所エラーは出なくなりましたが、その代わり抽出先シートの一番上のセル情報が反映されました。
ISPEI

2023/03/19 05:33

また、スプレッドシートは委託先から共有させて頂いているもので、秘密保持の為共有を控えさせていただきます。申し訳ありません。 ただ、できる限り引っかからない程度答えていこうと思います。
quiz

2023/03/19 05:53

おそらく INDEX 関数の引数の調整が必要です。以前提案した式を少し修正してみてください。 =INDEX('抽出シート'!N:N,MATCH(N29&E29,ArrayFormula('抽出シート'!L:L&'抽出シート'!M:M),0)) この修正で、MATCH 関数が返す行番号がそのまま INDEX 関数に渡されるようになります。 これで、正しいデータが抽出されることを期待しています。 もし問題が解決しない場合は、再度お知らせください。
ISPEI

2023/03/19 06:03

結果変わらずエラーになり、見つからない判定になりました。
quiz

2023/03/19 06:10

一旦リセットして再度質問を見直しました。 順不同のデータを照合して引き出すには、INDEX関数とMATCH関数を使った組み合わせが適切ですが、MATCH関数の配列計算を有効にするためにARRAYFORMULAを使用する必要があります。次の式を試してみてください。 =IFNA(INDEX('抽出シート'!N:N, MATCH(1, (N41='抽出シート'!L:L) * (E41='抽出シート'!M:M), 0)), "") この式では、MATCH関数の中で(N41='抽出シート'!L:L) * (E41='抽出シート'!M:M)を計算して、一致する行を検索しています。 各条件が一致する場合は1を返し、そうでない場合は0を返します。MATCH関数は、この結果の中で最初に1が現れる位置を返します。
ISPEI

2023/03/19 06:13

MATCHの評価で値「1」が見つかりませんでした。 というエラーが出ました。
ISPEI

2023/03/19 06:17

念のためチェックしていることをお伝えします。 表示形式では共に自動となっており、スペースの空白はメモにコピペして確認し、問題なく、LEN関数とTEXT関数も掛けて同じでした。 列のアルファベットをその都度確認して、問題ありませんでした。
quiz

2023/03/19 06:20

エラーが発生していることを考慮し、SUMPRODUCT関数を使用して配列計算を行う方法を試してみてください。 以下の式を使って、照合先シートと出力先シートのデータの順番が不揃いでも一致するデータを検索して該当列のデータを抽出できます。 =IFNA(INDEX('抽出シート'!N:N, SUMPRODUCT((N41='抽出シート'!L:L) * (E41='抽出シート'!M:M) * ROW('抽出シート'!L:L))), "") この式では、SUMPRODUCT関数を使用して、N41が'抽出シート'!L:L内の各セルと一致し、かつ、E41が'抽出シート'!M:M内の各セルと一致する行番号を計算しています。 一致する行が見つかった場合、その行番号をINDEX関数に渡して、該当するN列のデータを抽出します。 一致するデータが見つからない場合、IFNA関数によって空文字列("")が返されます。 この式で、順不同のデータの照合と抽出ができるはずです。もしまだ問題が解決されない場合は、シートのデータに特殊な文字や隠れたスペースが含まれている可能性があります。 その場合、TRIM関数やCLEAN関数を使用して、余分なスペースや制御文字を取り除くことを試してください。
ISPEI

2023/03/19 06:24

参照が存在しません。とエラーが出ました。
ISPEI

2023/03/19 06:27

N29='ハイライトURL抽出シート'!L:L の部分が「参照が存在しません」と出ました。
quiz

2023/03/19 06:31

質問に基づいて、シート名を「抽出シート」としていましたが、あなたのコメントによると、「ハイライトURL抽出シート」が正しいシート名のようです。 以下の式を試してみてください。 =IFNA(INDEX('ハイライトURL抽出シート'!N:N, SUMPRODUCT((N41='ハイライトURL抽出シート'!L:L) * (E41='ハイライトURL抽出シート'!M:M) * ROW('ハイライトURL抽出シート'!L:L))), "") この式では、正しいシート名「ハイライトURL抽出シート」が使用されています。 一致する行が見つかった場合、その行番号をINDEX関数に渡して、該当するN列のデータを抽出します。 一致するデータが見つからない場合、IFNA関数によって空文字列("")が返されます。 この式で、順不同のデータの照合と抽出ができるはずです。 参照エラーが解決されない場合は、再度お知らせください。
ISPEI

2023/03/19 06:41

すみません、修正して実行しましたがけっかかわらずでした。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問