業務に役立つExcelデータを列と行で抽出。INDEXとMATCH関数。DL付。

シェアする

  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存
  • 0

業務に役立つExcelで、作成したデータベースから行と列でピンポイントにデータを抽出してくれる大変便利な関数( INDEX関数とMATCH関数)を紹介します。今回もDLファイル付です。

はじめに

データ抽出で、一番利便性が高く、活用度が高い関数に、VLOOKUPがあります。

VLOOKUPは検索値は行のみで、列は指定した列数となります。

それに対して、INDEX関数とMATCH関数は行と列ともに検索値とします。

よって行と列が交わるピンポイントの1データを抽出させる事が出来ます。業務で役立つ場面は多用にあると思います。

これを活用すれば、顧客データ注文請書や、料金表が簡単でかつ短時間に管理出来るようになりますよ。

データから行と列を検索して抽出(INDEXとMATCH)

VLOOKUP関数とINDEX/MATCH関数の違い

表のイメージとしては下記のような感じで捉えて下さればわかりやすいと思います。

INDEX①

INDEX②

サンプルファイル

今回も解説用にサンプルファイルを用意しました。

シート数は3つ

まず、下記が集計用のメインシートです。

INDEX③

こちらに今回の関数INDEXとMATCH関数がF列K列に入れてあります。

そして、下記が商品用のINDEXとMATCH用の検索データベースです。

INDEX④

行に商品の種類、列に数量データとなっています。

ちなみに数量が多くなる程、値引き率を上げていく計算式が入っております。

一例として

数量が1SET多くなるにつれて5%値引きする計算にしてあります。

そして、下記が送料用のINDEXとMATCH用の検索データベースです。

INDEX⑤

INDEX⑥

やはり、一番に思い浮かんだ、宅配便の送料表をアレンジしました(笑)

  • この表は、ヤフオクや通販で愛用させて頂いております、クロネコヤマト運輸様の宅急便運賃一覧表アレンジさせて頂きました!

  icon-external-link クロネコヤマト運輸様宅急便運賃一覧表

  • クロネコヤマト様、いつも大変お世話になっております!

D列は、検索値用の列で、

B列とC列を合体させてあります。今回の紹介する主役は、INDEXとMATCH関数です。そのINDEXとMATCH関数セルをややこしくせずシンプルにする為に別途計算用の列を設けました。

集計用シートの解説

INDEX⑦

B列のNo.列には、ROW関数が入れてあります。イレギュラーに途中で行の挿入をしたときにROW関数でのNo.付は大変便利なので重宝します。

INDEX⑧

C列の顧客名は普通に手入力列です。

そしてD列、E列、G列、H列、J列のリスト選択列は、入力規則のリスト選択列となります。下記のリストをそれぞれ対応列へ指定してあります。

INDEX⑨

INDEX⑩

そして、F列に、商品種別と数量のクロス検索抽出用に今回の主役関数 INDEXとMATCH関数が入っています。

INDEX11

K列には、着地地域とサイズ、出荷地域のクロス検索抽出用に、INDEXとMATCH関数が入っています。

INDEX12

L列は単純に抽出した商品金額と、同じく抽出した送料の合計ですね。念のため、途中で入力不備の場合を考えて、IFERROR関数を入れて、途中で入力不足の場合は、「入力不足」を表示するように指定してあります。

INDEX/MATCH関数の解説

ここで、ようやくINDEXとMATCH関数の解説をしたいと思います。

F列に入っている計算式

この、F列では商品の金額を検索し抽出しています。

ここがポイントロゴ

icon-check-square-o INDEXとMATCHの基本的な構成が下記の解説

INDEX(データベースの見出しを含めた全ての行と列,

MATCH(行の検索値,見出し行上から下までを選択,0),

MATCH(列の検索値,見出し列を左から右まで選択,0)

なので、F列を例にすると

  • INDEXで商品価格表のデータベース全て、この場合 B3:L13
  • MATCHで 検索したい行、D4 そのD4の検索データベース行 商品価格表のB3:B13
  • 次のMATCHで、検索したい列、E4 そのE4の検索データベース列 商品価格表のB3:L3 を指定してあるという理屈です。

icon-check-square-oここでも、検索値の行と列どちらかが未入力になる事を考えて、IFERROR関数を用いて、どちらかが入力されていない場合、”入力なし”が表示されるように指定してあります。

K列に入っている計算式

そして、K列では送料の金額を検索し抽出しています。

これで、INDEXとMATCH関数を理解して頂けたと思います。

icon-check-square-o数式でそれぞれ$付けしている箇所は、そのまま下へドラッグして数式をコピー出来るようにする為です。

ダウンロードファイル

“Excelファイル行と列で検索し抽出” をダウンロード INDEXMATCH.xlsx – 112 回のダウンロード – 27 KB

    • ご自身で使用されているファイルへご使用されるならば、この記事を参考に計算式をコピペして使用して下さい。
    • もちろんダウンロードして頂いたファイルのデータベースシートもそのままご利用になれますので、ご活用下さい。

さいごに

大きなデータベースがあり、それらを項目ごとに細分化し抽出するには、VLOOKUPがとても優秀で、使用頻度は高いかと思います。

それに対して、このINDEXとMATCH関数は、1データベースに1抽出ファイルのような使用をするのに大変有効です。

活用する事が出来れば、日頃の業務の大幅な効率化を図れるでしょう。

もちろん、関数を知っていても、活用出来るか、否かは皆さま次第です。

是非、日頃の業務にお役立て頂ければ幸いです。

以上、業務に役立つExcelデータを列と行で抽出。INDEXとMATCH関数。DL付。でした。

ご視聴ありがとうございました。

合わせて読みたい

業務に役立つExcel指定範囲をVBAで一括削除。DL付。
業務に役立つExcelで事前に選択した範囲の値を、マクロボタン一つで自動で削除してしまう計算式をご紹介します。DLファイル付です。
業務に役立つExcel選択範囲に一瞬で罫線。DL付。
業務に役立つExcelで任意に選択した範囲へ罫線をマクロボタン一つで自動で挿入してしまう計算式をご紹介します。DLファイル付です。
業務に役立つExcel任意行ごとに空白行自動挿入。DL付。
業務に役立つExcelで任意に指定した行数ごとにマクロボタン一つで自動で空白行を挿入していく計算式をご紹介します。DLファイル付です。
業務に役立つExcelデータベースを自動で仕訳し抽出。DL付。
業務に役立つExcelで作成したデータベースを、自動で仕訳し別シートへ抽出させる計算式をご紹介します。DLファイル付です。
業務に役立つExcelファイル会計年度計算の関数式。DL付。
業務に役立つExcelのファイリングで会計年度計算の関数式です。 DLファイル付きです。
業務に役立つExcelで検索エンジンVBAを5分で搭載。DL付。
業務に役立つExcelのデータベースファイリングに、VBAで作成した検索エンジンを5分で簡単に搭載出来ます。サンプルDL付です。

スポンサーリンク

シェアする

  • このエントリーをはてなブックマークに追加
  • Evernoteに保存Evernoteに保存

フォローする


スポンサーリンク

トップへ戻る