Home » Excel(エクセル)の関数・数式の使い方 » 表からデータを取得する・表引きする−参照演算子

表からデータを取得する・表引きする−参照演算子

対象:Excel2002, Excel2003, Excel2007

Excelで業務用ワークシートなどを使っている場合に、「表引き」といわれる動作が必要になることがあります。

「表引き」とは何らかの値を指定して、表からデータを取得するといった動きのことです。

わかりやすい例としては、荷物のサイズと輸送距離によって料金がかわる、宅配便の料金表のようなものです。

荷物のサイズと輸送距離を指定したら、自動的に該当する料金が表示されるようなExcelのワークシートを作りたいといった要望があげられたりします。

そんなときによく知られているのは、INDEX関数とMATCH関数を組み合わせる方法です。

他にもこの手の表引きをする方法がありますのでご紹介しておきます。

[スポンサードリンク]

参照演算子としてのスペースを使う方法です。


▼操作方法:表からデータを取得する
※C8:F10セルの表からF10セルの値をB4セルに取得する例
B4セルに「=F8:F10 C10:F10」という数式を入力する

参照演算子としてのスペースを使ったことのない方にとっては上記の
「=F8:F10 C10:F10」
という数式は何だかよくわからないものかもしれません。

▼サンプルファイル(003534.xls 48KByte)ダウンロード

サンプルファイルの「表引き(1)」シートに上記の例を作成してあります。

「=F8:F10 C10:F10」
という数式は、F8:F10とC10:F10の共通部分を返しなさいという数式なのです。

ですから、
「=F8:F10 C10:F10」
という数式は、F8:F10とC10:F10の共通部分であるF10セルの値を返します。

注意点は、前半のセル範囲「F8:F10」と後半のセル範囲「C10:F10」の間にスペースを空けることです。

サンプルファイルの「表引き(1)」シートのB4セルの数式を
「=C8:C10 C9:F9」
「=D8:D10 C8:F8」
などのように変更したらどのような値が取得できるかご確認ください。

ただし、上記のような
「=F8:F10 C10:F10」
といった数式のままでは、実務で表引きをしたいという要望にはイマイチです。

実務で使うには、名前を定義して名前をつかった数式にするほうが現実的でしょう。


▼操作方法:表からデータを取得する
※C8:F10セルの表で名前を定義して、LLサイズで遠距離のデータを取得する例
B4セルに「=LLサイズ 遠距離」という数式を入力する

サンプルファイルの「表引き(2)」シートにこの名前を定義した例を作成してあります。

「表引き(2)」には、
C8:C10セル=「Sサイズ」
D8:D10セル=「Mサイズ」
E8:E10セル=「Lサイズ」
F8:F10セル=「LLサイズ」
C8:F8セル=「近距離」
C9:F9セル=「中距離」
C10:F10セル=「遠距離」
という名前を定義してあります。

「LLサイズ」はF8:F10セル
「遠距離」はC10:F10セル
なのですから名前を使った
「=LLサイズ 遠距離」
という数式は、
「=F8:F10 C10:F10」
と同じ意味になり、やはりF10セルの値が取得できます。

注意点は、「LLサイズ」と「遠距離」との間にやっぱりスペースを空けることです。

「=F8:F10 C10:F10」
と比べて
「=LLサイズ 遠距離」
は随分わかりやすくなりました。

とはいえ、実務で使うにはもうちょいです。

「=LLサイズ 遠距離」
という数式では、この数式を直接修正しなければなりません。

実務で使うワークシートなら、例えば、どこかのセルにドロップダウンリストを表示して、そのリストから「LLサイズ」「遠距離」を選択したら自動的に料金表からデータを取得したくなるでしょう。


▼操作方法:表からデータを取得する
※C8:F10セルの表で名前を定義して、B1セル・B2セルに値を指定したときにB4セルにデータを取得する例
B4セルに「=INDIRECT(B1) INDIRECT(B2)」という数式を入力する

このサイトでこれまで何度かご紹介したINDIRECT関数の出番です。


INDIRECT関数を使うと、文字列をセルアドレスやセル範囲として参照できるようになります。

サンプルファイルの「表引き(3)」シートにはこのINDIRECT関数を使った例を作成してあります。

B1セル・B2セルには入力規則機能を使ってドロップダウンリストから選択きるようにしてあります。

今回ご紹介した方法は、定義した名前の管理をしっかり行っておく必要がありますが、表引きの方法としてはINDEX関数とMATCH関数の組み合わせよりもわかりやすいかもしれません。

サンプルファイルの「INDEX関数&MATCH関数」シートには、IDEX関数とMATCH関数を組み合わせて、「表引き(3)」シートと同様のことができるようにしてありますので合わせてご確認ください。

[スポンサードリンク]

Home » Excel(エクセル)の関数・数式の使い方 » 表からデータを取得する・表引きする−参照演算子

TrackBack:1

TrackBack URL
#NULL!エラーの表示される例 from インストラクターのネタ帳
対象:Excel2002, Excel2003, Excel2007 Excelを結構使えるレベルの方から 「#NULL!エラーというのはどういうときに...
[スポンサードリンク]

Home » Excel(エクセル)の関数・数式の使い方 » 表からデータを取得する・表引きする−参照演算子

「Excel(エクセル)の関数・数式の使い方」の記事一覧

検索


Copyright © インストラクターのネタ帳 All Rights Reserved.

.