SKU と地域の2条件で価格を引く（スキャン値の整形つき）
問題
問題文
同じ SKU でも地域ごとに価格が違うため、キーは1つでは足りません。価格表は A2:C7、スキャンした SKU は F1（空白つき）、地域は F2 にあります。価格を Price セル F3 に入れてください。
条件は2つ。しかも SKU は式の中で整えます。スキャナは小文字と空白で書き出すためです。
問題のデータ
表でデータ全体を見る(選択・コピーできます)
| A | B | C | E | F | |
|---|---|---|---|---|---|
| 1 | SKU | Region | Price | SKU scanned | sku-102 |
| 2 | SKU-101 | US | 49 | Region | EU |
| 3 | SKU-102 | EU | 19.5 | Price | |
| 4 | SKU-102 | US | 21 | ||
| 5 | SKU-103 | EU | 24.99 | ||
| 6 | SKU-101 | EU | 52 | ||
| 7 | SKU-103 | US | 25.5 |
ポイント解説
答えが数値で、すべての条件が等号なら SUMIFS が検索として使えます。すべての条件を満たす行の合計を返し、それが唯一の価格になります。TRIM(UPPER(F1)) でキーを先に整えます。INDEX + MATCH で条件を掛ける書き方は配列版で、答えが文字列のときはこちらを使います。
解き方のヒント
回答は F3 に入力します。送信時は 3 通りの入力で数式を再実行するので、数値を直接書くと必ず失敗します。数式はセルを参照してください。
- 対象セルは F3
- 先にキーを整える：TRIM(UPPER(F1))
- SUMIFS に範囲と条件を2組渡すと検索になる
- 形：=SUMIFS(?, ?, ?, ?, ?)
模範解答
模範解答を表示(2 通り・まずは自分で試してみましょう)
方法1：SUMIFS を2条件の検索として
=SUMIFS($C$2:$C$7, $A$2:$A$7, TRIM(UPPER(F1)), $B$2:$B$7, F2)
TRIM(UPPER(" sku-102 ")) は "SKU-102" になり、EU と同時に一致する行は1つだけなので 19.50 です。一致しないときは 0 が返るため、0 が正当な価格の場合は注意が必要です。
方法2：INDEX + MATCH を2条件で
=INDEX($C$2:$C$7, MATCH(1, ($A$2:$A$7=TRIM(UPPER(F1)))*($B$2:$B$7=F2), 0))
MATCH(1, ...) は掛け算で両方 1 になる最初の行を探します。答えは同じで、SUMIFS と違い文字列も取得でき、見つからないときは静かな 0 ではなく #N/A を返します。
関数ガイド
よくある質問
2つの列で決まる値を引くには？
答えが数値なら =SUMIFS(値, 列1, キー1, 列2, キー2)、文字列なら =INDEX(値, MATCH(1, (列1=キー1)*(列2=キー2), 0)) です。
SUMIFS がエラーではなく 0 を返すのはなぜ？
一致する行がないと空の合計になり、それが 0 だからです。0 が実在しうる価格なら =IF(COUNTIFS(...)=0, "not found", SUMIFS(...)) と包みます。