�@日付をリンク張り付けするとシリアル値で表示された

�A同じ表示形式のセルを複写する（右クリックのショートカットメニュー［コピー］）

�Bシリアル値表示のセルを右クリックして［形式を選択して張り付け］をクリックする

�C［形式を選択して張り付け］ダイアログボックスが開く

�D［書式］をクリックして●印を付ける

�E［ＯＫ］をクリックする

�F同じ表示形式が設定される

 

　　

EX4C330Z　　　　　　　　　EX4C336Z　　　　　　　　　　　　　EX4C343Z

 

　　　

EX4C344Z　　　　　　　　　　　　　　　EX4C334Z

 

 

 

第５回　月間の集計表を作る「２」

　前回に引き続き月間の集計表の作成を解説する。今回は、期間別、顧客別、担当者別など指定するデータだけを集計する方法を紹介する。

 

 

 

●顧客別の集計

　一覧表に並べたデータの中から、特定のデータだけを抜き出して合計

 

 

●期間の集計

 

 

 

●担当者別の集計

 

 

 

［ 上 級 編 ］

●顧客名を一覧から選択する（コンボボックスの利用）

　顧客名や商品名など、ある程度決まった内容はＶＬＯＯＫＵＰ関数などを使って番号入力に置き換える処理を行うが、番号と顧客名の対応を覚える、あるいは一覧表を作成してそれを見ながら入力する、といったことが必要になる。ここで紹介するコンボボックスを使うと、Ｅｘｃｅｌのツールバーに入っているフォントや文字サイズを選択するボックスと同じ機能で顧客名を選択することができるようになる。ここで使うコンボボックスなどは、ＶＢＡというプログラムを伴う処理になるが、一覧からの選択程度なら、特別なプログラムも必要なく、設定操作だけで使うことができる。使ったことのないような関数を使うより簡単に利用できる。

●一覧表示するデータをまとめる

 

 

●プロパティを設定する

 

 

 

●セルと連結する

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

実践活用講座

 エクセル

　 第5回

 

　前回に引き続き月間の集計表の作成を解説する。今回は、期間別、顧客別、担当者別など指定するデータだけを集計する方法を紹介する。指定するデータだけを対象に計算などの処理を行う関数のことをデータベース関数と呼ぶ。

 

 

EX4K771Z

 

 

 

図Ａ 商品販売の履歴簿

　EX4C388Z

�@指定した顧客の合計       �A指定期間の合計

 

 

●ＤＳＵＭ関数の書き方

書式             DSUM(データ範囲,フィールド,検索条件)

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の合計計算に使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

◆データ範囲

　指定するデータの範囲は、図Ａ 商品販売の履歴簿のような列の上部に項目名を記述したスタイルでないと使えない。図Ａの場合でみれば、データ範囲はＢ５：Ｇ２３（累計の列を省いたが含めてる差し支えはない）になる。

◆フィールド

　計算を行うデータの列を指定する。請求金額の合計を求めるなら、Ｇ列を指定することになる。フィールドの指定方法は、項目名で指定する方法とセル番地で指定する方法があり、どちらの方法で指定しても同じ結果が得られる。項目名で指定する場合は、項目名を半角のダブルクォーテイションで囲むことが必要だ。セル番地で指定する場合は、項目名を入力しているセル番地を指定する。

 

◆検索条件

　どのデータを合計するかを指定する条件を記述する。たとえば、顧客名［ラッコ商店］のデータを集計するなら、列の項目名「顧客名」と検索データ［ラッコ商店］を図Ｂのように縦にならべて入力し、そのセル範囲を関数の引数として指定する。入力する項目名やデータは、入力内容さえ一致していればよいので、［左揃え］のままでも［中央揃え］を施しても構わない。図Ｂの位置に入力した場合の関数に記述する引数はＪ５：Ｊ６になる。

 

 

 

 

 

 

 

１●顧客別の集計

　一覧表に並べたデータの中から、特定のデータだけを抜き出して合計を求めるには、ＤＳＵＭ関数を使えばよい（関数名の先頭のＤはデータベースを意味する）。この関数はＳＵＭ関数のように合計を求める関数だが、指定する検索条件に合うデータだけを合計する。たとえば、すべての売上を記録した販売履歴簿などから、ある顧客だけの合計請求金額を求める場合などで使用する。

 

 

図Ｂ

　EX4C372Z

 

◆入力する数式

　以上の条件でＤＳＵＭ関数を記述すれば、以下のようになる。上側の記述はフィールドをセル番地、下側の記述はフィールドを項目名で記述している。どちらを使っても同じ結果が得られる。計算結果は、データ範囲に入力している顧客名［ラッコ商店］の請求金額が求められる。

 

        ＝ＤＳＵＭ（Ｃ５：Ｇ２３，Ｇ２３，Ｊ５：Ｊ６）

　　　　　　　　　　　　↑　　　　　↑　　　　↑

　　　　　　　　　データ範囲　フィールド　検索条件

　　　　　　　　　　　　↓　　　　　 ↓　　　  　↓

        ＝ＤＳＵＭ（Ｃ５：Ｇ２３，"請求金額"，Ｊ５：Ｊ６）

 

�@ＤＳＵＭ関数を入力する

�A指定した顧客名だけの合計が計算される

 

　EX4C373Z

 

２●期間の集計
　顧客名のように１つの条件で集計するには、単純に検索する顧客名だけを記述すれば良いが、何日から何日のように自至期間の集計を求める場合、検索条件は、比較演算子を使った記述方法で、はじまりの日付と終わりの日付を横にならべて入力することが必要だ。たとえば、２月１日から２月２０日のデータを集計するには、図Ｃのように検索条件を入力する。検索条件として入力する日付は、数式扱いになるので、先頭に＝を付け、入力内容をダブルクォーテイションで囲むこと。期間の自の指定は比較演算子に＞＝を使って「〜以上」、至の指定は＜＝で「〜以下」を指定する。表Ａに比較演算子の一覧を載せておくので参考にしていただきたい。なお、両方に項目名の「日」の入力を忘れてはいけない。

 

�@セルＪ９とＫ９に項目名「日」を入力する

�AセルＪ１０に自の日付「＝”＞＝９９／２／１”」を入力する

�BセルＫ１０に自の日付「＝”＜＝９９／２／２０”」を入力する

�CセルＬ１３にＤＳＵＭ関数を入力する

        「=DSUM(C5:G23,"請求金額",J9:K10)」

�D期間の合計が求められる

 

図Ｃ

　EX4C375Z

 

 

 

EX4C376Z

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　EX4C377Z

 

　指定した期間には、前回で紹介したＳＵＢＴＯＴＬＥ関数を使った小計を表示しているが、この行には日付が入力されていないのでＤＳＵＭ関数の合計計算には含まれない。日付を入力すれば、計算の対象になってしまうので注意が必要だ。また、一覧表で表示している日付には年は表示されていないが、これはセルの表示形式で非表示（月と日だけを表示）にしているだけであって、実際に入力している日付は年を含めたデータである。

 

●期間内の顧客別集計

　期間を指定した検索条件の横に、さらに顧客名の条件を並べると、指定期間内の顧客別の集計を求めることができる。

 

�@顧客名の検索条件を入力する

�AＤＳＵＭ関数の検索条件のセル範囲を書き替える

�B結果が求められる

 

 

　

EX4C378Z　　　　　　　　　　　　　　　　　　　　　　　　　　EX4C379Z

 

●平均値や最大値・最小値を求める

　合計を求めるDSUM関数以外に、条件を指定して求める関数がある。平均値を求めるDAVERAGE関数、最大値を求めるDMAX関数、最小値を求めるDMIN関数もある。指定する引数などはDSUM関数と同じだ。先のＤＳＵＭ関数と同じ検索条件（期間と顧客名）を指定した使用例を載せておくので参考にしていただきたい。なお、各セルの表示形式は金額に設定している。平均値に小数点以下が表示される場合はツールバーの［小数点表示桁上げ］［小数点表示桁下げ］ボタンで適切に調整する。

 

●ＤＡＶＥＲＡＧＥ関数

書式             DAVERAGE(データ範囲,フィールド,検索条件)

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

●ＤＭＡＸ関数／ＤＭＩＮ関数

書式             DMAX(データ範囲,フィールド,検索条件)

                DMIN(データ範囲,フィールド,検索条件)

データ範囲       データベースのセル範囲を指定する

フィールド       データベース内の使用する列を指定する

検索条件 条件が設定されているセル範囲を指定する

 

［Ａ］検索されたデータの平均値が求められる（表示形式は適切に設定する）

 

　　

EX4C389Z　　　　　　　　　　　　　　　　　　　　　　　　　EX4C390Z

 

EX4C393Z

 

　

EX4C391Z　　　　　　　　　　　　　　　　　　　　EX4C392Z

 

 

 

 

各関数で求められた値は、検索条件で抽出された「ラッコ商店」２月２日の￥２４，５６０、２月１８日の￥６５，０００、２月１９日の￥８８，９００に基づく値である。入力した数式は以下のとおりである。

 

�@=DAVERAGE(C5:G23,"請求金額",J9:L10)

�A=DMAX(C5:G23,"請求金額",J9:L10)

�B=DMIN(C5:G23,"請求金額",J9:L10)

 

 

 

 

 

 

　　　　　　　　　　コンボボックスで入力を簡略化する

 

●顧客名を一覧から選択する（コンボボックスの利用）

　顧客名や商品名など、ある程度決まった内容はＶＬＯＯＫＵＰ関数などを使って番号入力に置き換える処理を行うことだろうが、番号と顧客名の対応を覚える、あるいは一覧表を作成してそれを見ながら入力する、といったことが必要になる。ここで紹介するコンボボックスを使うと、Ｅｘｃｅｌのメニュー（ツールバー）に入っているフォントや文字サイズを選択するプルダウン形式のボックスと同じ動作（データ一覧から１つを選択する機能）を作成できる。一例として、顧客名を選択するコンボボックスを作成してみよう。ここで使うコンボボックスなどは、ＶＢＡというプログラムを伴う処理になるが、一覧からの選択程度なら、特別なプログラムも必要なく、設定操作だけで使うことができる。使ったことのない関数を使うより簡単に利用できる。

 

 

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　図Ｍ

　EX4C384Z

　　　　　　　　　　　　　　　　　　　　　　　　EX4C385Z

 

●一覧表示するデータをまとめる

　コンボボックスのプルダウン形式で一覧表示するデータを縦方向に連続するセルにらなべて入力しておく。ここでは、図ＥのようにセルＯ６：Ｏ１０の範囲に入力しておくことにする。

 

図Ｅ

　EX4C380Z

 

●コンボボックスを挿入する

　メニュー［表示］→［ツールバー］→［コントロールツールボックス］を選択すれば、使用可能なコントロール一覧のツールバーが表示される。この中の［コンボボックス］をクリックして押し込んだ状態にしてから、シート上の任意の位置でドラッグ＆ドロップ操作でコントロールを挿入する。なお、ツールバーはシステムの状態により表示される位置は異なる。他のツールバーと同様にタイトル部をドラッグ＆ドロップして任意の位置へ移動できる。

 

�@コントロールツールボックスを表示する

�A［コンボボックス］をクリックして押し込んだ状態にする

�Bシート上にドラッグ＆ドロップ操作でコントロールを挿入する

 

EX4C387Z

 

EX4C386Z

 

 

 

 

 

 

 

 

 

 

 

 

 

 

●プロパティを設定する

　次に、挿入したコンボボックスを右クリックして、ショートカットメニューの［プロパティ］を選択してプロパティの設定表を開く。そしてプロパティ［ＬｉｎｋｅｄＣｅｌｌ］欄に顧客名を入力するセル番地（ここではＪ６）を、プロパティ［ＬｉｓｔＦｉｌｌＲａｎｇｅ］欄にプルダウン形式で一覧表示するデータを入力したセル範囲（ここではＯ６：Ｏ１０）を入力する。

 

�@コントロールを右クリックしてショートカットメニューを表示する

�A［プロパティ］を選択する

�Bプロパティ設定のウィンドウが開く

�Cプロパティ［ＬｉｎｋｅｄＣｅｌｌ］欄にＪ６を入力する

�Dプロパティ［ＬｉｓｔＦｉｌｌＲａｎｇｅ］欄にＯ６：Ｏ１０を入力する

�E［×］をクリックしてウィンドウを閉じる

 

EX4C383Z

 

EX4C382Z

 

 

 

第６回　年間の集計表を作る

　前回までの操作で作成した月間の集計表を元に、１２月分、つまり年間の集計を求める方法を紹介する。

 

 

 

●１枚のシートに入力した月データ

 

●シートの挿入

 

●シートの移動

 

●シートの削除

 

●シートを複写して使う

移動

 

 

●シート間の合計

連続するシートの合計はＳＵＭ関数で集計できる

連続しないシートの配列では１つ１つの加算式で合計を求める

 

 

 

 

 

 

 

上級編］

１つのブックで年間のデータを扱う場合、コンピュータシステムの状態にもよるが、データの記録できる量に制限がある。どの程度の容量があるのかは、システムの状態により変わるので一概に言えないが無制限ではない。多くのデータを記録したり集計する必要があるなら、月ごとのファイルにして、ファイル単位で集計を行うようにすればよい。

 

●複数のブックファイルを集計する

 

 

 

 

 

 

 

 

 

 

 

 

 

  必要なデータだけを抜き出し、 集計を行う

 売上集計表を作る[2]

●適切な表示形式を指定する（ユーザー設定の書式はコピーされない）
　同じシート内で［コピー］と［張り付け］を行った場合、コピー先セルにコピー元と同じ表示形式が設定されるが、独自に作成した表示形式（ユーザー設定）の場合は、表示形式のコピーは行われない。この場合、リンク操作の後あるいは先に表示形式を適切に設定することが必要だ。目的のスタイルで表示されているセルがあれば、そのセルの書式だけを複写することも可能だ。