- ホーム >
- 基本操作 >
- エクセル(Excel)編 >
Excelのデータベース関数の使い方|データベースの作成(2)
データベースの知識を得ておくと、いま収集しているデータについて、未来の活用の様々な方法を思いつくようになります。
つまり、データ収集段階において、そのデータをどのように活用して、どのような方向性や指標を見出していくかという予測を立てることができるようになります。
すなわち、データというもの、どのように扱えばそれが「生きたデータ」となるのかを理解することができ、Excelに限らず、いろいろな場面で応用の効く考え方が身につくと思います。
そのためには、日常の様々なデータ(例えば、趣味で集めているグッズや小遣いの収支など)をデータベース化するためのフォーマット(テンプレート)を作成し、実際にデータを溜めてデータベース化し、そしてそのデータベースを分析していくという一連のプロセスを実際に練習するしかありません。
実際にやってみることで、こういう風にフォーマットをつくっておけばよかったとか、こういう項目もデータとして集計しておけばよかった、など気づくことがたくさんあります。ぜひ、データの収集を行い、データ分析を行ってみてください。
さて、本項では「データベースの作成(1)」としていますが、基本的なデータベースの仕組みは前項を参照していただき、また前項と同様に例題の作成に重点を置かず、今度は多少応用的な、データベースに対してよく利用する関数について学習していきたいと思います。
データベースにため込んだデータをどのように利用し、活用していくのか。また、どのような切り口で分析するのか、といった手助けとなる基礎的な関数を紹介します。
例題12 | 例題10のサンプルデータ |
---|
例題12は、レポートの作成 の項でピボットテーブルを作成したときの例題と同じサンプルデータになります。このデータベースを利用していろいろな関数を適用してみましょう。(2枚目のシートには回答をつけてあります)
データベース系の関数は、文字の先頭にその名のとおり、「D」の文字が付きます。下図のとおり、関数の分類でも「データベース」という分類があります。
まずは、リストメニューのトップ「DAVERAGE」関数を使用してみましょう。DAVERAGEは「D(データベース)」のAVERAGE関数になります。AVERAGE関数は、家計簿の作成(3) で解説のとおり平均値を求める関数になります。
通常のAVERAGE関数との違いは、指定したデータベースの範囲のなかで、条件に合致するものだけを抽出して平均値をもとめることができるというものです。
例えば、例題のデータベースにおいて、「武器」を抽出の条件として、「武器」のみの売上高の平均値を求めてみましょう。
上図のとおり、一見引数の入力は簡単そうに見えます。しかし、やり方を知っておかないとデータベース関数はまず使うことができません。
まず、データベース関数で必要なのは、上図の「条件」のところであり、
抽出するデータの条件を記述した表を作成する必要がある
のです。つまり、この「条件」には、例えば「"武器"」などの文字列や「A1」といったセル番号が記述されるわけではなく、作成した表の範囲が指定されることになります。
具体的には、下図のようになります。
この条件の表は適宜追加し、また必要部分のみ参照することにより、単数条件で抽出したり、複数の条件をあわせて抽出したりすることができます。
このときの注意事項として、
同じ行の条件は「AND」、異なる行の条件は「OR」
という検索条件になるということです。「AND」検索とは、日本語でいえば「かつ」であり、「AかつB」という条件になります。つまり、「AとBの両方を含む場合」という条件になります。
一方、「OR」検索とは、「AまたはB」であり、「AかBのどちらかを含む場合」になります。したがって、AND検索は条件が厳しく、OR検索はどちらか一方を含んでいるだけでよいので、ゆるい条件といえます。
ではまず、「武器」だけを検索条件として抽出し、武器だけの「売上高」の平均を、「J8」セルに出してみましょう。
引数の「データベース」には、データベースの範囲を記述します。マウスでドラッグして、「A3:G53」を記述します。ここで、重要なのは、必ず見出し行(項目名の行)も範囲に含める ということです。
なぜなら、項目名(「売上高」など)は、抽出条件で指定するため、この行を範囲に含めておかないと検索する条件を探すことができないからです。
次に「フィールド」には、求めたい平均値の項目を記述します。フィールドとはデータベースの「列」のことをあらわします。(行は「レコード」)つまり、「合計値」の列を記述します。
しかし、ここでも単純に「合計値」の列である「F」と記述してもダメです。見出し行の「見出しセル」を指定します。したがって、「売上高」の「F3」を指定します。(ただし、列番号の「6」を記述することができます。列番号は左から数えた列数のことです)
最後に「条件」には、作成してある表を範囲指定します。指定したい条件は「武器」だけなので、表の一部をマウスでドラッグして「I3:I4」を記述します。(ここで「売上高」まで範囲に含めてしまうと条件がかわってしまうので注意してください)
このときの条件を書き上げた表について、項目名(この場合は「カテゴリ」)は、データベースの項目名と同じでなければなりません。全角、半角の違いも許されませんので注意が必要です。
引数の値は上図のとおりで、記述式は、「=DAVERAGE(A3:G53,F3,I3:I4)」となります。(「F3」のところが「6」であっても可です)
すると値は、「43236.3」になります。したがって、武器だけの売上高の平均値を求めることができました。この結果は、下図のとおり「武器」だけを書き出した表から「AVERAGE関数」で求めた平均値と一致しており、正しいものです。
次は、複数条件にしてみましょう。複数条件にするには、条件を書き出した表に条件を追加し、引数の「条件」に記述する範囲を広げればよいわけです。
条件に「I3:J4」を指定してみると、検索条件は「武器」かつ「売上高10000以下」となります。(IF関数 で解説のとおり、「以下」をあらわす不等号は「>=」となります)「J9」セルに平均値を出してみましょう。
すると値は、「4897.1」となります。記述式は、「=DAVERAGE(A3:G53,F3,I3:J4)」となり、「条件」の引数が異なるのみです。
次に、さらに条件を増やして、日付によって範囲を限定してみましょう。「2012/3/1以降」という条件を付けたい場合、どのように記述すればよいでしょうか?
日付の場合も考え方は同じです。不等号によって「以前」「以後」を指定することができます。したがって、「2012/3/1以降」は「>=2012/3/1」もしくは「>2012/2/29」であらわすことができます。(以前であれば不等号が逆を向きます)「K4」のセルに記述し、「J10」セルに平均値を出してみましょう。
すると値は、「6356.7」となります。記述式は、「=DAVERAGE(A3:G53,F3,I3:K4)」となり、この場合も「条件」の引数が異なるのみです。
この場合は、「武器」であり「売上高が10000以下であり」「2012/3/1以降」という条件のすべてを含むレコード(行)の平均値になります。これらを実際に書き出してみると下図のとおりで、値は正しいものになります。
では平均値の最後に、「OR検索」で平均値を求めてみましょう。条件表の2行名の任意のセルに適当に項目を記述して、「J11」セルに値を出してみてください。
例えば、下図のように「I5」セルに「防具」を記述するとどうなるでしょうか?
この場合、「武器」または「防具」という検索条件となり、つまり「武器+防具」の平均値を求めることになります。引数の「条件」に「I3:I5」を記述すると、値は「28566.5」となります。
このように、データベース関数はさまざまな条件のもとに計算させることが容易な関数です。基本的なデータベース関数の仕組みは同じですので、この一連の流れを覚えておけば、あとは関数の種類が違うだけでなにも難しいことはありません。
合計値を求めるSUM関数のデータベース版「DSUM関数」では、下図のとおり、DAVERAGE関数と引数はまったく同じものになります。
条件は、「I3:I5」とすると値は、「19070」になります。
これで、基本的なデータベース関数の使い方については習得できたと思います。また、条件表の値(「武器」など)を変更すると、ともなって平均値等も変化することを確認してください。
では、ここからは、データベース関数とはいえませんが、比較的データベースに対してよく使われる関数をコンスタントに紹介していきたいと思います。
まずは、「COUNT関数」です。COUNT関数は、範囲指定したなかに「数値のセル」がいくつあるか、数値セルの個数を返す関数です。
ここでいう「数値」とはセルの表示形式が数値形式あるセルという意味です。したがって、いくら数字にみえても「文字列」形式ではカウントされません。(表示形式については、セルの書式設定と表示形式 を参照してください)
COUNT関数は、「統計」に分類され、引数には任意の範囲を記述するだけです。
上図のように「値1」「値2」とあり、複数の範囲(隣接しない離れた範囲など)を指定することができます。また、いくらでも値を増やすことができます。
それでは実際にやってみましょう。「L14」セルに、設問のとおり「D列の数値のセルの数」を求める関数を記述してみてください。
関数はCOUNT関数です。範囲は「D列」なので、D列すべてを範囲指定してもよいし、D列でデータが記述されている範囲だけを指定してもかまいません。
ここでは範囲を「D1:D53」としました。すると、求められる値は「50」になります。見出し行のセル「販売価格」は数値セルではないのでカウントされず、数値セルは50個あるという結果になります。
この関数の使い方は、基本的に「件数」を求めるときに用いられます。例えば、パチンコで勝った日付だけ金額を入力し、負け日は空欄(blank)や「負け」などと記入する運用のシートで、「勝ち」の件数を求めるといった場合に有効です。
次は、「COUNTA関数」です。COUNTA関数は、範囲指定したなかに「空白でないセル」がいくつあるか、空白でないセルの個数を返す関数です。つまり、COUNT関数とは異なり、何がしか値が入力されているセルをカウントします。
同様に、設問のとおり「C列で値が入力されているセルの数」を求める関数を、「L15」セルに記述してみてください。
関数はCOUNTA関数です。COUNTA関数も「統計」に分類され、引数には任意の範囲を記述するだけです。範囲は「C列」なので、C列すべてを範囲指定してもよいし、D列でデータが記述されている範囲だけを指定してもかまいません。
上図のとおり、ここでは範囲を「C1:C53」としました。すると、求められる値は「51」になります。見出し行のセル「販売価格」も空白でないセルに含まれるのでカウントされ、空白でないセルの数は51個あるという結果になります。
この関数は全体的な件数、総数をカウントするのに便利な関数です。
次は、「COUNTIF関数」です。COUNTIF(カウントイフ)関数は、範囲指定したなかに「指定した条件のセル」がいくつあるかを返す関数です。つまり、任意で指定した条件に一致するセルの個数を求めることができます。
COUNTIF関数は比較的よく利用される関数
なので、しっかり覚えておきましょう。ここでいう条件とは、指定した値との「一致」や「以上」、「以下」などをいいます。つまり、「A」という値と一致するセルの個数や「1000以上」のセルの個数などを求めることができます。
それでは実際にやってみましょう。「L16」セルに、設問のとおり「"夜神月"の文字数(セル数)」を求める関数を記述してみてください。
関数はCOUNTIF関数です。COUNIF関数も「統計」に分類されます。
引数には「範囲」と「条件」を指定します。範囲はデータベースの表の範囲を指定し、「条件」に文字通り条件を指定します。
今回は、「夜神月」の文字数を求めるので、セルの値が「夜神月」の文字と「一致」することが条件となります。つまり、条件にはそのまま「夜神月」を記述します。また、これは文字列ですので、「""」で囲む必要があります。
原則的に自動付記されますが、文字列をあらわす「""」の意識を持っておくことが大切です。(詳しくは、IF関数 を参照してください)
すると、求められる値は「8」になります。この関数も「件数」を求める関数ですが、例えば勤務表を作成し、「有休」や「欠勤」の総数などを求めることができます。
また、条件には不等号を用いることもできます。例えば「10,000より大きい売上の件数」など下図のように求めることもできるので、COUNTIF関数は非常に使い勝手のよい関数になります。
次は、範囲のなかの「最大値」を求める「MAX関数」です。
これは文字通りの関数で、引数には単純に範囲指定した範囲を記述するだけです。そのなかの最大値を自動的に抽出してくれます。
設問の「売上高の最大値」を求めてみましょう。MAX関数は「統計」に分類されています。MAX関数を選択して、売上高の範囲を指定します。
すると、最大値である「500,000」を返してくれます。
次は、最大値の反対で「最小値」を求める関数の「MIN関数」です。MIN(ミニマム、ミン)関数は、MAX関数と使い方はまったく同じです。
設問の「販売価格の最小値」を求めてみましょう。MIN関数も同じく「統計」分類から選択し、販売価格の範囲を指定します。
すると、最小値である「8」を返してくれます。
さて、それでは本項では最後に解説する関数です。値のランキングをつけてくれる面白い関数を使ってみましょう。その名も「RANK関数」で、そのまま名前どおりの仕事をします。
この関数も単純で、範囲指定した範囲のなかで指定したセルの順位が「何位」であるかを返します。設問の「1月25日の売上高は全体の何位か?」を求めてみましょう。
RANK関数は「互換性」に分類されています。互換性というのは、Excel2010からはRANK関数は、「RANK.EQ(ランクイコール)関数」となり、もうひとつ「RANK.AVG(ランクアベレージ)関数」に細分化されたからです。
通常のRANK関数(RANK.EQ関数)は、例えば同じ値が2つあった場合、順位は同順となり、仮に1位だった場合には1位が2人で、次の順位は2位が飛んで3位からになるというのがRANK関数の考え方であるのに対して、RANK.AVG関数は順位の平均を返すというような少しわかりにくい関数になっています。
ここでは、そこまで深くランキングを掘り下げる必要はありませんので、従来の「RANK関数」を使用します。
引数には、「数値」にランキングを出したいセルを指定し、「参照」に範囲を指定します。そして「順序」に「0」を記述すると降順、「0以外」を記述すると昇順でランキングが降られます。
つまり、下図のとおり引数を入力すると、「F10」セルの値「1,500」は、「参照」に指定した範囲のなかでは、数値の大きいランキングで「16位」となるわけです。
一気にランキングを出すときには、下図のように「参照」の範囲を絶対参照にして(絶対参照については、絶対参照と相対参照 を参照してください)、複写して(ドラッグ&ドロップで)出来あがりです。
これは、会社の売上ランキングや成績表など順位を確定するときに用いると便利な関数です。
以上で関数の解説は終了です。途中からデータベースの作成から多少逸脱してしまいましたが、データを収集して分析するという一連のプロセスをしっかり把握しておくことが大切です。
関数はあくまでも補助的な機能であって、データを規則正しく収集できてさえいれば、どんなかたちにも加工できるものです。本項の練習課題は特にありません。
例題をもう一度見直して、それぞれの関数にスムーズに引数が記述できるようになったら次項に進みましょう。
更新履歴
- 2012年6月26日
- ページを公開。
- 2017年12月29日
- ページをSSL化によりHTTPSに対応。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- ウェブサイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします!