MATCH関数+INDEX関数で指定範囲内から自由にデータを取り出す
Excel関連記事
Excel・Wordで値の貼り付けができるショートカットキー
ExcelのTEXT関数で今日の日付から何日後の年月日・曜日を取得する
phpで文字列が数字かどうかを判別するctype_digit
【Excel】重複がある一覧の中から対象のセルを探す方法
【Excel】重複のない一覧の中から、複数の対象のセルを探す方法
【Excel】手入力や関数での、セル内の文章を改行する方法
【Excel】MATCH関数とINDEX関数で、指定範囲内から自由にデータを取り出す
【Excel】SUMIF関数の文字列版を作る方法
【Excel】VLOOKUP関数で、重複する一覧から正しく該当行を検索する
INDEX関数の使い方
INDEX関数は縦横の番号を指定することで的確にデータを取り出せます。
簡単にいうとVLOOKUP関数の上位版のイメージです。
VLOOKUP関数は条件に一致した最初の行しか取り出せませんが、
INDEX関数を使うと条件に合致するセルが複数あったとしても正しいデータが取り出せます。
VLOOKUP関数を複数対応にする方法で使った表で試してみます。
このシート名を「在庫データ」シートにしました。
例えば商品コード「17910」のサイズが「42」のデータを取り出したい場合、
INDEX関数を使うと
で取り出せます。
範囲(A2:C9)の中の行番号(3)と列番号(2)の交わるセルのデータを取得したということです。
上のような、「商品データ」シートを作りました。
ここには各サイズの対応サイズが入力されています。
次は関数を使って、
「商品データ」シートの各対応サイズを「在庫データ」シートの各サイズに反映させてみます。
関数を使って列番号を取得する
VLOOKUP関数を複数対応にする方法で使った「全てのデータに出現順をつける」方法でやってみます。
下の関数を「在庫データ」シートのD2に入力します。
あとはオートフィルタでD列全体に適用します。
そうすると「出現順」＝「サイズ1,2,3,4」という図式ができます。
※「在庫データ」と「商品データ」のサイズ順をあわせておく必要があります。
これでそれぞれの列番号を取得できました。
関数を使って行番号を取得する
「商品データ」シートの各対応サイズを取得するので、行番号は「商品データ」シートから取得します。
この数式を「在庫データ」シートのE列に入力します。
MATCH関数は検索範囲(商品データ!A:A)の中から検索対象(A2)が何番目に出てくるかを教えてくれます。
これで行番号を取得できました。
※上の数式では検索範囲をA列で指定しているので表題の「商品コード」のセルも範囲に含まれます。
INDEX関数を使うときに範囲指定が変わってくるので注意して下さい。
それかMATCH関数で取得した数値を「-1」してやれば解決です。
INDEX関数で取り出す
この数式を「在庫データ」シートのF列に入力します。
範囲内から(商品データ!$B$1:$E$3)列番号(E2)と行番号(D2)が交わるセルのデータを取得します。
範囲はオートフィルタでずれないように行、列に$をつけて固定します。
範囲がBから始まっているのは、出現順(行番号)が1からカウントしているからです。
Aから始めたいのなら、出現順(行番号)に「+1」するように数式に追加しましょう。
精度を高める
この方法でやると「在庫データ」と「商品データ」のサイズ順をあわせておく必要があります。
それがもしずれていたら間違った情報を取り出してしまうことになります。
それを防ぐために、チェックをしてOKなら反映する方法を取ることで間違った情報を入力するリスクを減らします。
やり方は表記のパターンにもよりますが、今回の場合「商品データ」のサイズには最初に「在庫データ」と共通するサイズが入力されています。
それをもとにチェックしていきます。
IF関数で取り出したデータが正しいかをチェックしています。
「商品データ」のサイズには最初に共通するサイズと半角スペースがあるのでそれをFIND関数で目印をつけ、LEFT関数で切り取っています。
FIND関数の値を「-1」しているのは半角スペースの位置を取得するのでLEFT関数で切り取るときに半角スペースまで一緒に切り取るのを防ぐためです。
また、LEFT関数で切り出した値は文字列扱いになります。
IF関数で比較したときにデータの型が違うのでFALSEになるのを防ぐために在庫データの値をTEXT関数で文字列にしています。
このように誤りが起きやすいところにはチェックを挟んでいくとより精度の高い数式ができます。
コメント