投稿2022/01/24 06:59
編集2022/01/24 07:26Google Spreadsheetで検索して複数の行が一致した場合、一致した行のうち、特定の列の空白でないセルの最初の値を抽出したい。
| A | B | C | |
|---|---|---|---|
| id | 名前 | 名前2 | |
| 1 | 100 | 太郎 | |
| 2 | 100 | 太郎 | 次郎 |
| 3 | 100 | 太郎 | |
| 4 | 100 | 太郎 |
| A | B | C | |
|---|---|---|---|
| 1 | id | 名前 | 名前2 |
| 2 | 100 | 太郎 | 次郎 |
「元データ」では「id」が「100」のレコードが4行ありますが、「抽出データ」では1行にまとめ、「元データ」の「名前2」の空白ではない最初の値を取得したいです。
「抽出データ」の「C2」で「次郎」を取得するには、関数をどのように設定すればよいのでしょうか。
vlookupを使ってみましたが、空白になりました。
よろしくお願いいたします。
回答1件
0
ベストアンサー
補足事項を考慮して説明しますね^^
まずVLOOKUP関数は一つだけの値を返すものなので、今回のように複数の列を取り出したあとで条件に合ったものを取り出すみたいなことはできません。
いろいろ方法はありますがまず一番単純な方法から！
途中、下にコピーという文言が出てきますが表の大きさに合わせて適度な場所で止めてください。
今回は999行まで表があることとします。
Q&A
index関数で取り出すC列の空白以外を取り出そうという考え方です。
まず「作業列D列」に対して式を書きます。D2セルに...（D1セルには0か何も入力しないでください）
=IF(IsBlank(C$2), -ABS(D1), ABS(D1) + 1)
これを下にコピーします。
すると作業列D列には名前2が空白でない場合のみ正の数の値が付きます。
次に「作業列E列」に対して式を書きます。
...というかE2が「1」で、1, 2, 3, 4...となるように下に番号を書きます。
数式を使うなら、E2セルに...
=E1 + 1
と書いて下にコピーしましょう（E2セルには「1」と入力しても構いません）
次に「作業列F」に対して式を書きます。
=MATCH(E2, D$2:D$999, 0)
と書いて下にコピーしましょう。
E列に書いてある数字がD列の何番目にあるか、インデックスを計算してくれます。
エラーが出るセルもありますが、無視して構いません。
最後に出力です。
H列以降に出力しましょうか👀
H2セルに...
=IFERROR(INDEX($F2, A$2:A$999), "")
とすれば、F列の値（インデックス）をみて、元の表から値を取り出してくれます。
もしエラーが出た場合は空白を返す関数で囲んでいます。
これを、H2:J999の範囲にすべてコピーしましょう。
作業列を嫌う人もいますが、基本的に作業列を使うことで速度が速まります。（作業列を使わないと、関数を1回呼べばよいところを何回も呼んでしまうような書き方が増えます）
上の章では、説明を重視するために少し省いているところがあります。
もしもっと速度を上げたい場合はこちらの章を試してください。
表が999行あると仮定して説明を進めていましたが、999という値を表の高さに応じて切り替える方法です。
MATCH関数は値を検索する関数ですが、999行すべて検索する必要は無く、表の高さ分検索すればそれで充分です。
なので、
F1セルにでも、表の高さを計算する関数を入力します。
=COUNTA(A$2:A$999)
A列しか見ていませんが、ID列が空白なことはないでしょうからこれで表の高さが求まります。
そしてF2セルを以下のように書き換えましょう。
=MATCH(E2, OFFSET(D$2, 0, 0, F$1, 1), 0)
実はVLOOKUPを使う方法にも変更できます。
方法は簡単で、D列をA,B,C列の左側に持ってくることです。
つまり例の表がB,C,D列になり、A列が作業列になりますね。（もちろん参照を変更しないような移動方法にするか、式の参照を手書きで変更すること）
A列は...
=IF(IsBlank(D$2), -ABS(A1), ABS(A1) + 1)
みたいになるでしょう👀
E列には今まで通り1～999までの連番。
F列以降に出力します。
F2セルに...
=VLOOKUP($E2, $A$2:$D$2, 2)
としましょう。（「2」のところは「3」や「4」にすれば取り出す列が変わります）
作業列が一つ減り、速くより簡潔に書けるメリットがありますが、A作業列の位置を変更できないのが欠点です。VLOOKUPの性質上、検索する値は表の左に置いておかないといけないからです（A列を非表示にはできます）。
なので、作業列の位置の融通が利くのはMATCHとINDEXの組み合わせですね！
作業列をもっと使わずに書く方法がどうしても必要なら返信してください。
...ただ、よほどな理由がない限り作業列を使った方がよいとは思います。
私がよくやる手は、AA列以降に作業列を書いて、列を非表示に設定する方法ですね👀
別シートに書いて、結果だけ持ってくるのもありです。シートを非表示にもできたかな？（Excelならできますが、スプレッドシートは忘れた💦）
問題がありそうならお気軽に返信してくださいね～^^
投稿2022/01/25 00:48
編集2022/01/25 00:54総合スコア322
あなたの回答
tips
プレビュー
