やりかを良く忘れるのでメモ
- 特定のセルの値と合致 / 含む、行を探してその中からあるセルの値を取得する(
VLOOKUP) - 上記の処理を列全体で実行する (
ARRAYFORMULA)
他のシートから検索した値と合致した行の値を取得する (VLOOKUP)
=VLOOKUP(検索キー, 範囲, 番号, [並べ替え済み])
上記のような商品の data シートが有り、A列にある販売したの商品コードと合致する商品名と価格を反映させたい
data シートの A〜C列を検索して、商品名 B 列と価格の C 列の値を取り出せば良い
商品名のB列は検索する A〜C列の 2番目、価格のC列は 3番目なので、下記のようにすればOK
// 商品名 =VLOOKUP(A2, data!A2:C, 2, FALSE) // 売上 =VLOOKUO(A2, data!A2:C, 3, FALSE)
VLOOKUP は単体の値しか取得できないので、商品名・売上のそれぞれのセルにマクロを書く必要がある
ARRAYFORMULA を使うと複数のセルの値が取得できる
配列数式から返された値を複数行または複数列に表示したり、非配列関数で配列を使用したりすることができます。
cf. ARRAYFORMULA - Google ドキュメント エディタ ヘルプ
ARRAYFORMULA は扱う値を配列にできるので、VLOOKUP で取得する値を配列にし複数セルを取得させることができる
検索して取得したいデータが検索する範囲の2番目と3番目のセルの場合は {2, 3} のように指定すればOK
=ARRAYFORMULA(VLOOKUP(A2, data!A2:C, {2,3}, FALSE))
(出力が 2セルになるので 売上 に入力していたマクロは削除する)
VLOOKUP の処理を列全体に適応させる (ARRAYFORMULA)
ARRAYFORMULA で検索する値を範囲に変更すれば、A 列にある売上商品コードと合致する商品名・売上 (価格) を自動的に反映させられる
検索範囲を A列 A2:A に変更すればOK
=ARRAYFORMULA(VLOOKUP(A2:A, data!A2:C, {2,3}, FALSE))
適応範囲が A 列の最後までなので、検索条件の値に依っては空白のセルにも VLOOKUP が適応されてしまう問題がある
空白のセルを除外する
= IF(論理式, TRUEの時の値, FALSEの時の値)
IF を用いて検索条件が空白のなら空白のままにするようにすれば良い
=ARRAYFORMULA(IF(A2:A="", "", VLOOKUP(A2:A, data!A2:C, {1,3}, FALSE)))
いい感じになった！
* (ワイルドカード) を使うことで VLOOKUP の検索条件を曖昧にできる
検索条件に * を含めた文字列にすると、前方一致・後方一致・含むにすることができる
// 前方一致 =VLOOKUP(A2&"*", data!:A2:C, 2, FALSE) // 後方一致 =VLOOKUP("*"&A2, data!:A2:C, 2, FALSE) // 含む =VLOOKUP("*"&A2&"*", data!:A2:C, 2, FALSE)
文字列結合をしているので、検索条件のセルが文字列である必要がある
文字列でないとエラーになるので、その場合は値を文字列変換すればよい
おわり
[参考]
