第5回 - 2017/10/20

データ処理に便利な関数

データベース関数

Dで始まるいくつかの関数の総称で、「D関数」と俗称もある。
「データベース範囲」から、「条件」に合ったレコードについて、指定した「フィールド」に対する計算を行う。
「データベース範囲」とは、1行に1レコードで、列ごとにフィールドが分かれた形式のセル範囲
条件は、1行目にフィールド名、2行目以降に条件、3行以上はOR条件、列を追加すればAND条件

代表的な関数

DSUM(データベース範囲, フィールド, 条件)
DCOUNT(データベース範囲, フィールド, 条件)
DCOUNTA(データベース範囲, フィールド, 条件)
DMAX(データベース範囲, フィールド, 条件)
DMIN(データベース範囲, フィールド, 条件)
他に統計処理のデータベース関数もある。

セル検索を行う関数

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

指定した「範囲」は一覧表の形式になっている
「範囲」の１列目から、「検索値」を探す（大文字小文字は無視）
その行の「列番号」で指定した列の値を返す
検索の型は検索値が１列目にない時の挙動を指定する（ただし、数値以外は動作が複雑になるので注意）

TRUEないしは省略：一致しない場合は検索値に一番近い最大の値を戻すが、1列目で並べ替えが必要
FALSE：完全に一致した値しか返さない
見つからない場合は「エラー」を返す

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

商品コード	単価	商品名
12994	250	クッキー
12995	280	ポップコーン
PG34	350	オレンジジュース
PS92	450	マンゴージュース
RRR2	340	トマトジュース
XD001	230	コーヒーS
XD002	280	コーヒーM
XD003	400	コーヒーL

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

検査値が検索範囲内で何番目に出てくるかを求める
照合の型によって一致するものがないときの挙動が決まる

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

いろいろな記述があるので、完全なリファレンスはヘルプを参照
範囲の中から、指定した行番号、列番号のセルの値を取り出す
行番号や列番号は１から範囲内の数値を指定する

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

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

両方に存在する商品、あるいは片方だけにある商品（手がかりは「商品コード」）
両方に存在する商品だが単価が異なる（手がかりは「単価」）

商品コード	単価	商品名
12994	280	クッキー
12995	280	ポップコーン
PG34	400	オレンジジュース
RRR3	300	トマトジュース
XD001	230	コーヒーS
XD002	280	コーヒーM
XD003	400	コーヒーL
3422	320	ホットドッグ
45223	640	ハンバーグ定食

INDIRECT(セル)

セルに入力されている文字列をセル範囲と見なす
利用例：伝票に支店を追加する→デモ（本関数は省略ます）

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

授業中に作業した内容

本日の演習

練習問題5-1

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

練習問題5-2

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

練習問題5-3

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

練習問題5-4

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

解答

練習問題5-1

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

練習問題5-2

ポイントはINDEX関数の2番目の引数にMATCH関数を使う事です。（5-3の解答例にまとめてあります。）

練習問題5-3

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

練習問題5-4

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