第5回 - 2013/10/25

データ処理に便利な関数


データベース関数

代表的な関数

セル検索を行う関数

VLOOKUP(検索値, 範囲, 列番号[, 検索の型])

利用例:伝票作成→デモ(以下の表は左上をA1にしてください)

商品コード単価商品名
12994250クッキー
12995280ポップコーン
PG34350オレンジジュース
PS92450マンゴージュース
RRR2340トマトジュース
XD001230コーヒーS
XD002280コーヒーM
XD003400コーヒーL

MATCH(検査値, 検査範囲[, 照合の型])

INDEX(範囲, 行番号, 列番号[, 領域番号])

利用例:2つの表から異なっている部分を抽出する→デモ(以下の表は左上のセルをA14にしてください)

ここでの「異なっている」というのは次のパターンがあると考える。

商品コード単価商品名
12994280クッキー
12995280ポップコーン
PG34400オレンジジュース
RRR3300トマトジュース
XD001230コーヒーS
XD002280コーヒーM
XD003400コーヒーL
3422320ホットドッグ
45223640ハンバーグ定食

INDIRECT(セル)

吉祥寺A2:C9
錦糸町A15:C23

本日の演習

練習問題5-1

第3回のワークシートにあるデータを利用して、データベース関数を使って集計をすること。担当者ごとの9月の期間だけの売上表を作成すること。最終的には担当者ごとの表になっていればいいので、それ以外の部分は自由にセルを利用してかまわない。

練習問題5-2

VLOOKUPとほぼ等価な式を、MATCHとINDEX関数だけで作成できる。作成してみること(インターネットで検索するとすぐに出てくると思われるが、とりあえず自分で考えてみて、セルにいろいろ試しに入力して検証すること)。ちなみに、VLOOKUPはいちばん左の列の検索しかできないが、表の中の左端ではない列から検索してデータを取り出すような場合には、この方法を使って解決ができる。

練習問題5-3

デモでは、A1から始まる表に、もう一方の表に同じ商品があるかどうか、あるなら単価は同じかどうかを判断する式を追加した。もう1つの表にも同じ式を付け加えること。このとき、最終的な表示結果は「ない」「あるが単価は違う」「あって単価も同じ」のいずれかの文字列を表示すること

練習問題5-4

2つの店舗でそれぞれ単価テーブルを作っているが、1つの表にまとめたい。単価と店舗を無視して、「商品コード」と「商品名」の組について重複しない表を作ること。とりあえず、両方の表の内容を別のワークシートにでもコピーして、つなげた状態から何をすればいいかを考えてみよう

解答

練習問題5-1

「式を一発コピーで…」はあまりにトリッキーなので、それはいいとして、こんな感じで求めたい条件の範囲を都度都度作るのが一般的です。ともかく、ピボットテーブルに勝るものではないでしょう。ただし、ワークシートできちんとした表を作る場合にはDSUM等を知っておくと便利かもしれません。

練習問題5-2

ポイントはINDEX関数の2番目の引数にMATCH関数を使う事です。

練習問題5-3

この練習問題はIF文の練習ですね。

練習問題5-4

この練習問題はやりようはいろいいろありますが、1つの方法としては、2つ表をまとめてしまい、その表に対してピボットテーブルを作り、列ラベルに「商品番号」「商品名」を両方並べる事です。ですが、このままでは小計が表示されているので、図のようにピボットテーブル内の「商品コード」の列の中を右クリックして「“商品コード”の小計」のチェックを外し、小計表示をやめます。すると、後の図のようになり、あとは適当にコピー&ペーストしてきれいな表に仕上げるなどの作業を行えばいいでしょう。ここでオレンジジューズに異なる2つの商品コードがあることも分かるわけです(これをなんとかすることはやりようがないので、そのままでいいでしょう)。