みなさんご存知のようにエクセルでは123と行を数え、ＡＢＣと列を数えます。
一つの表をデータベースといい、表の中の1行分のデータをレコードといい、
１つの列をフィールドといいます。
データベース関数はたくさんありますが、今回はDSUM、DAVERAGE、DCOUNTA
の３つをとりあげてみます。DSUMは和菓子屋さんの売上伝票のなかから、
特定の商品の売上を瞬時に計算するものです。
DAVERAGEは年齢別身長体重の表から年代別の平均をだすものです。
DCOUNTAは月別の勤務表からそれぞれの休日の回数などを数えます。
1.DSUM
1）売上伝票の作成
A1から横に商品コード、商品名、単価、個数、売上といれます。Ｇ1から
横に商品コード、商品名、単価といれます。
Ｇ2から下に100、101、102、103、104、105とコードをいれます。
Ｈ2から下にさくらもち、イチゴ大福、みたらし団子、ういろう、
あんみつ、柏餅といれます。Ｉ2から下に70、80、120、320、250、100です。
この価格表は今回同じシートに作成しましたが、
別のシートでもいいですね。
2）計算
Ａ2に101といれたら、Ｂ2にイチゴ大福と表示させてみます。
Ｂ2に=IF(A2="","",VLOOKUP(A2,$G$2:$I$7,2))といれます。
隣のＣ2には=IF(A2="","",VLOOKUP(A2,$G$2:$I$7,3))です。
80と表示されれば正解です。Ｄ2には2といれます。
Ｅ2には売上額ですから、=IF(D2="","",C2*D2)となります。
Ａ3からＡ11までにコード表にあるおすきなコードを記入してください。
Ｄ2からＤ11までにはこれはいくつ売れるかしらと想像しながら
おすきな数字をいれましょう。
Ａ13から横に商品コード、商品名、単価、売上といれます。
Ａ14に101、Ｂ14、Ｃ14、Ｅ14は上の式をコピーします。
Ｄ14に=IF(A14="","",DSUM(A1:E11,D1,B13:B14))といれます。
コードを102、103と変えて、きちんと計算できることを確かめましょう。
2.DAVERAGE
第１回目のご質問コーナーで年齢別身長・体重の平均値を扱いました。
このときは並べ替えをしてから平均をして、３回目には配列関数もご紹介
したのですが、今回はおなじものをデータベース関数を使ってだしてみます
1）表の作成
Ａ3から横へお名前、身長、体重、年齢といれます。
Ａ4から14までにお名前をいれます。ここではＡからＫまでのアルファベット
をいれました。データは右のとおりです。
2）計算
Ｂ17に年齢、Ｄ17に身長、Ｅ17に体重といれます。
Ａ18に20代、Ａ20に30代、Ａ22に40代、Ａ24に50代といれます。
Ｂ18には<30、Ｂ20に<40、Ｃ20に>=30、Ｂ22に<50、Ｃ22に>=40、
Ｂ24に>=50といれます。Ｂ19、Ｃ19、Ｂ21、Ｃ21、Ｂ23にも年齢です。
Ｄ18には=DAVERAGE($A$3:$D$14,$B$3,B17:C18)
Ｅ18には=DAVERAGE($A$3:$D$14,$C$3,B17:C18)といれます。
これを20行目、22行目、24行目にもコピーします。
19行目と21行目、23行目は非表示にしておくと見やすいですね。
3.DCOUNTA
DCOUNTA関数をつかって、一覧からある言葉がはいっている数を数えてみます。
今月の勤務一覧からこの人の泊まりは何日とか、お休みは何日とか、
人目でわかるようにしてみます。
1）表の作成
Ａ1に７月勤務表、Ａ3から横に日付、それぞれのお名前です。
今回はＡさんからＤさんまで４名を記入してみました。
Ａ4から下へ1から31の数字をいれます。Ｂ4から下にＡさんの勤務です。
泊まり、日帰り、休み、待機などとしました。
Ｂ37、Ｂ40、Ｂ43、Ｂ46からよこにお名前をコピーしておきます。
Ｂ38には泊まりＢ41には日帰り、Ｂ44には待機、Ｂ47には休み
と書いて横にコピーします。
Ａ39、Ａ42、Ａ45、Ａ48にもそれぞれの文字をコピーしておきます。
2）計算
Ｂ39には=DCOUNTA($A$3:$E$34,B$3,B37:B38)といれ、右にコピー、
Ｂ39からＥ39までの式をコピーして、４２行目、４５行目、４８行目にも
貼りつけておきます。
Ｂ50には確認のために=B39+B42+B45+B48といれて横にコピーします。
ここが３１日になればちゃんと計算されていることになります。
3）書式の設定
最後にわかりやすく計算式の入ったセルに色をつけておきます。
ここには計算式がはいっているからまちがって消さないでねという
意味をこめました。
すっきりと印刷したいときなどは37、38、40、41、43、44、46、47、50
の各行をコントロールを押しながら選択して、非表示にしておくと
すつきりします。
次回は住所録から文字列関数を使って都道府県リストを作成します。
========================= 応用してみましょう =====================
データベース関数には今回ご紹介させていただいた３つのほかにも
ＤＭＡＸとか、ＤＭＩＮとかの関数もあります。
身長、体重の表から年代別の最大値、最小値などを求めてみましょう。
=========================== ご質問コーナー =======================
エクセルで作成した６月の表をあたらしいシートにコピーしたら、
セル幅などの書式がついてこないので、また設定しなおしている。
なにかいい方法はないかとのご質問です。この場合は表のコピーでなく、
シートのコピーをすると簡単です。シート名のところにマウスポインタを
あてて、コントロールキーを押して横にドラッグするとシートがコピー
されますので、シート名と表の中の６月を７月に訂正し、
データを書き換えてください。
Back Next エクセル関数トップ