２．抽出条件
　同じ項目で複数のデータを合わせて合計するときは、項目名の下に続けて複数のデータを入力して、抽出条件の範囲もそれに合わせて大きくします。


【操作】
�@　項目名の下に複数の品番を続けて入力する
�A　セルＨ７に式を入力（変更）する
　　＝ＤＳＵＭ（Ｂ５：Ｅ１８，３，Ｇ６：Ｇ８）
�@ 品番「Ａ−００１」と「Ａ−００２」の合計金額が計算される


　同じ項目の列に、縦に続けて抽出データを入力（ここでは品番をＧ７とＧ８に入力）すれば、複数の抽出データをまとめて合計します。計算結果は、品番Ａ−００１が２つとＡ−００２が２つの金額の合計になります。


　EX4D255Z

　EX4D256Z


【ポイント】空白の抽出データ
　抽出データ範囲内のセルが１つでも空白になっていると、正しく計算されません。範囲を複数行にしたときは、何かのデータ（ここでは品番）を入力することが必要です。セルＧ８を空白にするとＡ−００１の合計ではなく、不正な値が表示されます。

【まとめ】
●抽出条件の範囲を複数行にすると、各データの総和（合計）が計算される
●抽出条件の範囲内に空白セルがあると正しく計算されない。



■日付ごとの合計
　売上明細書などの集計表から、同じ日に販売した商品の合計を求めるようなとき、DSUM関数の条件に日付を指定して同じ日付のデータ（金額や販売個数など）だけを集計することができます。


１．明細表
　日付で集計するには、明細書などに日付を入力する項目が含まれいてることが必要です。日付を入力するセルは日付の表示形式になっていないと正しく作動しない場合があるので注意が必要です。ふつうは、日付形式のデータ（97/04/08など）を入力すると表示形式が自動的に日付に設定されますが、データの入力後に故意に変更したり、余分な文字を付け足して文字列として入力したとき、正しく設定されません。


�@売上明細書のデータ範囲　Ｂ５：Ｅ１８
　　項目名も含めた範囲を使います


２．抽出条件
　この例では品番と日付を抽出条件にしていますが、日付だけを条件にしてもかまいません。条件を横に並べると２つの条件に一致するデータだけを合計する働きになります。抽出条件のセル範囲は、２つの項目を含めたＧ９：Ｈ１０になります。それぞれのセル範囲をＤＳＵＭ関数に当てはめると以下のようになります。

        ＤＳＵＭ（データの範囲，合計を求める列，抽出条件）
　　　　　　　　　　　　　　　↓
　      　＝ＤＳＵＭ（Ｂ５：Ｅ１８，３，Ｇ９：Ｈ１０）


�A同じ日に販売した同じ品番の金額を合計している
�BＤＳＵＭ関数を入力する
　　＝ＤＳＵＭ（Ｂ５：Ｅ１８，３，Ｇ９：Ｈ１０）を入力する
�C抽出条件に品番を入れても、品番の入力欄（セルG10）を空白にしておくと日付だけを抽出条件に合計される


　　　　　　　　　　　　　　　　　　　　　　　　　　　　　作例　1-2-F
　EX4D257Z


【ヒント】項目名の入力
　抽出条件に入力する項目名を参照式で入力すれば、入力の手間を省くことができ、入力間違いを防ぐこともできます。（セルＧ９に＝Ｃ５、Ｈ９に＝Ｅ５を入力する）

【ヒント】データの範囲
　データ範囲に名前を付けて、DSUMのデータ範囲にその名前で入力すれば、データの増減でデータ範囲が変わったとき、DSUMを書き換えなくても自動更新されます。詳しくは「データ範囲に範囲名を使う」をご覧ください。


３．日付の入力
　データの範囲（明細書など）や抽出条件に入力する日付は、そのセルに最初に入力する内容（入力形式）で表示形式が自動設定されます。たとえば、４／１６を入力すれば４月１６日が表示され、９７／４／１６を入力すれば1997/4/16が表示されるように設定されます。どちらを使っても合計は正しく計算されます。一度設定された表示形式は入力内容を書き替えても自動変更されません。変更する場合は、以下の「日付が正しく表示されない」で解説する方法で変更します。


�r操作

�@４／１６を入力すると４月１６日形式で表示される

�A９７／４／１６を入力すると1997/4/16形式で表示される


　EX4D259Z　　　　EX4D258Z


４．日付が正しく表示されない
　日付以外のデータを最初に入力すると、日付以外の表示形式に自動設定される場合があり、日付を抽出条件として正しく判断されず、合計を正しく求められない場合があります。このときは、そのセルの表示形式をメニュー［書式(O)］�e［セル(E)�s］�e［表示形式］タブ、あるいはセルを右クリックしてショートカットメニューを開いて［セルの書式設定］→［表示形式］タブで適切な日付形式を選択して変更します。このような現象として考えられるケースは、そのセルがコンマ形式の数値、パーセント表示、などに設定されている場合です。


�r操作
�@ ４／１６を入力すると０が表示された
�@ 入力内容を見ると、０．２５になっている
◆ 4/16が数式として判断され計算結果の0.25が入力されている
�A 変更するセルを右クリックする
�A ショートカットメニューが開く
�B ［セルの書式設定］を選択する
�B ダイアログボックスが開く
�C 適切な日付形式（ここでは「1997/3/4」）をクリック（選択）する
�D［ＯＫ］をクリックする
�C 設定した日付形式で表示される
◆ 日付を入力し直して表示を確認する


　表示形式を変更後、入力されていたデータ（ここでは０．２５）を日付のシリアル値と見立てた日付が表示されます。もう一度、日付（4/16）を入力して正しく表示され、合計も正しいかを確かめます。


　EX4D264Z



　　
EX4D262Z 　　　　　　　　　　　　　　　　　　EX4D261Z

　EX4D263Z


ポイント
複数の抽出条件に一致するデータを合計するときは、抽出条件を横に並べる。

【操作のまとめ】
●抽出条件に日付を使うと指定した日付だけを合計することができる
●抽出条件を横に並べると、並べた条件にすべて一致するデータだけを合計することができる
●データ範囲（明細書など）や抽出条件の日付が正しく表示されないときは、そのセルの表示形式を日付に設定し直す



■期間別の合計
　月間や年間などの売り上げ明細表から、期間を指定して合計を求めることができます。商品などを条件に加えると、指定商品についての期間の合計といったこともできます。


１．明細表
　データの範囲になる集計表は、前項の「日付ごとの合計」で使った集計表をここでも使うことにします。内容の詳細は、前項を参考にしてください。


�@売上明細書のデータ範囲　Ｂ５：Ｅ１８
　　項目名も含めた範囲を使います

データの範囲（明細書など）の日付に関係する注意事項は、前項を参考にしてください。

２．抽出条件
　ある期間を条件にするときは、抽出条件に日付を２つ入れて、日付の自至を指定するようにします。２つの条件に一致する抽出は、条件を横に並べます。商品毎の集計もできるように品番を付け足しておくことにします。

        ＤＳＵＭ（データの範囲，合計を求める列，抽出条件）

　　　　　　　　　　　　　　　↓

　      　＝ＤＳＵＭ（Ｂ５：Ｅ１８，３，Ｇ２１：Ｉ２２）

�A指定商品（C-005）の自至で指定する期間に販売した金額を合計する

�BここにＤＳＵＭ関数を入力する

　　＝ＤＳＵＭ（Ｂ５：Ｅ１８，３，Ｇ２１：Ｉ２２）を入力する

期間の合計など、ある範囲を抽出条件にするときは、どこから、どこまで、を指定することが必要になります。日付で考えると、何日から何日まで、ということになります。

●「何日から」を指定する

　指定日だけでなく、何日から、を指定するときは、記号＞＝を使って以下のようにします。

        ＞＝９９／０３／２１　　←　半角で入力する

前部の記号を＞だけにすると、指定した日付を含めない（97/03/21以降）になります。月を０３で入力しているのは、見やすくするために２桁に揃えているだけです。３だけを入力してもかまいません。

　　　　　　　　　　�@

　EX4D272Z

●「何日まで」を指定する

何日まで、を指定するときは、記号＜＝を使って以下のようにします。

        ＜＝９７／０４／２０　　←　半角で入力する

前部の記号を＜だけにすると、指定した日付を含めない（97/04/20以前）になります。月の０４は「何日から」の指定と同じ意味です。

�@「何日から」を指定するデータを入力する

　　     ＞＝９７／３／２１

�A「何日まで」を指定するデータを入力する

　      ＜＝９７／４／２０

�B「何日から」「何日まで」の２つの条件に一致するデータの合計が求められます。

        品番を指定していないので、指定期間のすべての商品の合計が計算されます。



　　
EX4D273Z　　　　　　　　　　　　　　　　　　EX4D274Z


●不適切な抽出条件
　ここで操作しているように日付を抽出条件として使用する場合、入力するデータは必ず半角で入力します。全角で入力すると正しく計算されません。なお、この日付は文字列として入力されるので、表示形式は標準になります。

�r操作

�@ 全角で入力している

�@ 計が正しく計算されていません



　EX4D280Z



３．計算されないとき
　自至の日付を半角で正しく入力しても、合計が表示されない場合、他の抽出条件が不正であることが考えられます。以下の例は、品番が空白のようですが、スペースが入力されています。スペースも文字の１つなので、品番が入力されている、と判断され、スペースを品番として抽出しているからです。データの範囲にスペースを品番にした商品がないので合計が０になっています。



�@ 品番にスペースが入力されている
�@ 正しく計算されない
�A 品番を入力するセルＧ２２をクリックして選択する
◆［ＤＥＬ］キーを押す
�A 品番が空白になったので正しく計算される


　　
EX4D274Z　　　　　　　　　　　　　　　　　　EX4D275Z


ポイント
　ある範囲（ここでは日付の期間）を抽出条件にするときは、記号＞、＞＝、＜、＜＝、を使って、何日から何日まで、のように指定する


【操作のまとめ】
●自至の日付は半角で入力する。全角で入力すると正しく計算されない。
●自だけを入力すると、その日から以降を合計する
●至だけを入力すると、その日までを合計する


注意
　空白とスペースは、共に画面を見るだけでは判断できないが、動作の異なることがある。セルを空白にするには、そのセルを選択して［ＤＥＬ］キーを押す



■シート間の合計
　年間のデータなどデータ量が多くなる場合、１つのシートに１年分のデータを入力せずに、同じブック内の複数のシートに月毎に分割して入力することがあります。シート毎に分割したデータは、シート間の計算で合計などの集計計算ができます。


１．集計表のスタイル
　シートごとに作成する月毎の集計表は、同じスタイルにしておくことが、シート間の合計を求める「こつ」です。同じスタイルの集計表にしておくことで、シートの範囲でSUM関数を作ることができます。以下に、ここで操作する集計表を載せておくので参考にしてください。


�@１月の集計表（シート名［１月売上］）
�A２月の集計表（シート名［２月売上］）
�B３月の集計表（シート名［３月売上］）
�C４月の集計表（シート名［４月売上］）
�D前期（１月〜４月）のデータを合計する集計表（シート名［売上集計］）




２．シート間の計算式
　他のシートに入力されているデータを計算に使うとき、シート名とセル番地を記号！でつないで指定します。たとえば、Sheet1にデータを入力して、Sheet2で合計を求める場合を考えると、以下のような計算式の書き方になります。


�@シート１（シート名［Sheet1］）にデータが入力されている
�Aシート１のデータをシート２（シート名［Sheet2］）で集計する


シート２（�Aの位置）に入力する計算式は、シート名とセル番地を使って次のようになります。


セルＣ６

        ＝’Ｓｈｅｅｔ１’！Ｃ５＋’Ｓｈｅｅｔ１’！Ｄ５＋’Ｓｈｅｅｔ１’！Ｅ５

　　　　　　　　↑　　　　　↑　　　　　　　　　↑　　　　　　　　　　↑

　　　　   　シート名　 セル番地                ↑　　　　　　　　　　↑

　　 　　（Sheet1のセルC5を指す）（Sheet1のセルD5を指す）（Sheet1のセルE5を指す）


　シート名はシングルクォーテイション（［ ’］、７と同じキーにある）で囲み、記号！付けてセル番地を入力します。上の式は、シート１のセルＣ５、Ｄ５、Ｅ５を＋で加算していますが、セルが横に連続しているので、SUM関数に置き換えて以下のように書くことができます。セルＣ９の合計は同じシート内に表示したデータを集計しています。


セルＣ６　＝ＳＵＭ（’Ｓｈｅｅｔ１’！Ｃ５：Ｅ５）
セルＣ７　＝ＳＵＭ（’Ｓｈｅｅｔ１’！Ｃ６：Ｅ６）
セルＣ８　＝ＳＵＭ（’Ｓｈｅｅｔ１’！Ｃ７：Ｅ７）
セルＣ９　＝ＳＵＭ（Ｃ６：Ｃ８）


　　　　
EX4D230Z　　　　　　　　　　　　　　　　　　　　EX4D231Z　　　　　作例 1-2-14



３．シートを範囲指定する
　連続したセル範囲をＣ５：Ｅ５のようにコロン［：］でつないで指定するのと同じように、連続するシートを範囲指定することができます。シートの範囲もコロン［：］でつないで以下のように書きます。


        ’Ｓｈｅｅｔ１：Ｓｈｅｅｔ５’


　このようにすると、Ｓｈｅｅｔ１、Ｓｈｅｅｔ２、Ｓｈｅｅｔ３、Ｓｈｅｅｔ４、Ｓｈｅｅｔ５を指定したことになります。ここで注意があります。連続する範囲の指定になるので、はじまりのＳｈｅｅｔ１と終わりのＳｈｅｅｔ５に挟まれた（囲まれた）シート（始まりと終わりを含む）が選択されます。



●シートを範囲で指定するとき
　集計する各シートが連続していることが必要です。指定範囲のはじまりから終わりの間に入っているシートを計算範囲として集計するので、次のように並んでいると正しく集計されません。


シートが連続していない場合（不適切な例）
　EX4D233Z

　　　　　　　　　　SUM('１月売上:４月売上'!C5)　　　　　　　　　　作例 1-2-16


　シート［１月売上］から［４月売上］の間には売上以外のシート［仕入］が入っているので、［１月売上］［１月仕入］［２月売上］［２月仕入］［３月売上］［３月仕入］［４月売上］のセルＣ５を合計してしまいます。このように連続しないシート間の合計を求めるには、それぞれのシートを加算式で合計します。


        ='１月売上'!C5+'２月売上'!C5+'３月売上'!C5+'４月売上'!C5


シートが連続している場合（適切な例）
　EX4D232Z  作例 1-2-15
　　　　　　　
　　　　　SUM('１月売上:４月売上'!C5)


　シート［１月売上］［２月売上］［３月売上］［４月売上］が指定されるので、ＳＵＭ（’１月売上：４月売上’！Ｃ５）で合計すれば正しい値が求められる。



４．計算式を入力する
　集計表のスタイルで紹介した週間売上表に当てはめた計算式を考えてみましょう。合計を求める集計表（シート名［売上集計］）に入力する式、１月のサラダ（セルＣ６）を例にすると、シート名［１月売上］のセルＣ６〜Ｃ９の合計になり、数式は以下のようになります。

        ＝ＳＵＭ（’１月売上’！Ｃ６：Ｃ９）


この式の意味は、シート［１月売上］のセルＣ６〜Ｃ９を合計する、になります。


�@シート「１月売上」のセルＣ６〜Ｃ９を合計する
�Aシート「売上集計」のセルＣ６にSUM関数を入力する
        ＝ＳＵＭ（’１月売上’！Ｃ６：Ｃ９）を入力する
�@ 結果が表示される


　EX4D234Z

　EX4D235Z


　EX4D236Z

●数式の複写

シート名を付けた計算式（ここではSUM関数）も複写すれば、セル番地を自動的に更新した式で複写されるので、１つ１つの式を入力しなくて済みます。自動更新されるのはセル番地だけなので、セル番地だけを変更すればよい横方向の複写に限られます。たとえば、サラダの２月分の式は、

 

        ＝ＳＵＭ（’２月売上’！Ｃ６：Ｃ９）

　　　　　　　　　　　↑　　　　　　↑

　　　　　　　　ここが違う　　　　ここは同じ

 

シート名は自動更新されないので、セルＣ６に入力した式を２月分へ複写してもダメです。横のスープについては、シート名は同じでセル番地が更新されればいいので、複写することができます。

 

        ＝ＳＵＭ（’１月売上’！Ｄ６：Ｄ９）

　　　　　　　　　　　↑　　　　　　↑

　　　　　　　　ここが同じ　　　　ここが違う

 

　複写元にセルポインタを合わせると、セル右下に■が表示されます。■にマウスポインタを合わせ（カーソルが＋字に変わる）、ドラッグで複写先へ枠を延ばす

 

�r操作

�@ 複写元を複写先へドラッグする

�@ セルの入力内容が複写される

�A セル番地が更新された式が複写される

 

ヒント  複写でセル番地が更新されるのは、相対セル番地による効果です。シート名の相対指定はありません。

注意    ドラッグアンドドロップの複写は罫線も複写するので、罫線が変更される場合があります。複写後に罫線を引き直してください。

 

　EX4D237Z

 

　EX4D238Z

 

　EX4D239Z

 

【操作のまとめ】

●セル番地の指定にシート名を付けると、他のシートのデータを計算できる

●シート名を含めたセル番地でもセル番地の相対指定は有効に働く

●シート名をセル番地の相対指定のよう自動更新されない。相対で扱うことはできない。

●シート名をコロンでつなげば、連続するシートを範囲として指定できる

 

ポイント

シート名はシングルクオテイション（ ’）で囲む。シート名とセル番地は記号（！）でつなぐ

 

注意［ファイルが見つかりません］

シート名を使った数式を入力したとき、「ファイルが見つかりません」ダイアログボックスが表示されたときは、シート名を間違えています。キャンセルをクリックして数式を書き換えてください。



■ブック間の合計
　年間の合計を求めるとき、１つのブックに１月から１２月のデータが入力されていればシート間の合計で計算しますが、月ごとに別ファイルとして保存している場合、ブック（ブックを保存したファイル）間をリンクしてデータの合計を求めます。


１．リンクの必要なとき
　月間のデータを１つのブックに入力して、１月から１２月までそれぞれ１つのファイルとして保存すると、１２個のファイルができあがります。その保存したデータ（ファイル）から年間の合計を求めるとき、合計を求めるための新しいブックに１２個のファイルをリンクして合計を求めます。

�@このシート（ブック）にリンクを設定して合計を求める

�A月ごとの集計データ（１月分）

�B月ごとの集計データ（２月分）

 

ファイル名［生産数 合計］

　EX4D276Z

 

ファイル名［生産数 １月］

　EX4D277Z

 

ファイル名［生産数 ２月］

　EX4D278Z

 

ヒント［リンク］

　ファイル間のつながりを設定して、データを入力しているブック（リンク元）のデータが更新されたとき、データを集めたり、利用する側のブック（リンク先）のデータを自動更新する設定をリンクという。

 

２．集計表を作る

　年間などの合計を求めるブック（シート）を作ります。ここでは、このブックのファイル名を「生産数合計」にしています。作成済みの場合はそれを開きます。このシートと、月間データとして別に保存しているファイルの間にリンクを設定します。

 

�@ 新しいブックで年間の集計表を作る

�A 合計などは通常の計算式（SUM関数など）を入力して求める

　EX4D279Z

 

 

３．リンクを設定する

　リンクの設定は、２つのファイルが開いている状態で操作します。リンクを設定するシート（リンク先）は、前２の操作で開いているのでリンク元のファイル（ここでは「生産数１月」）を開きます。画面には、開いたリンク元が表示されます。以下の操作で［コピー］ボタンが見あたらないときは、ツールバーの端に表示されている［ EX4C640Zその他のボタン］をクリックして探します。

 

�r操作 １］●リンク元のデータ範囲を選択する●

�@リンク元のファイル「生産数 １月」を開く

�Aリンクするデータ範囲を選択する

        マウスのドラッグアンドドロップなどで範囲を反転表示にする

�B［コピー］ボタンをクリックする

 

　EX4D281Z

�r操作 １］●リンク先に切り替えて張り付ける●

�@［ウィンドウ］→リンク先のファイル「生産数 合計」を選択する

�@ 画面が切り替わる

�A リンク先（データの挿入位置）にセルポインタを合わせる（セルをクリックする）

◆ リンク元を範囲指定している場合のリンク先の指定は、先頭位置（左上のセル位置）を指定する

�B メニュー［編集(E)］→［形式を選択して張り付け(S)�s］を選択する

　EX4D282Z

　EX4D283Z

　EEX4D284Z

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

�C［罫線をのぞくすべて(X)］を選択する

�D［リンク張り付け(L)］をクリックする

�B データが挿入される

�C 数式バーにリンク元のファイル名とシート名、セル番地が表示される

　       例｛＝［生産数 １月．ｘｌｓ］Ｓｈｅｅｔ１’！＄ＡＨ＄６：＄ＡＨ＄１０｝

�D 集計値がその場で表示される

　EX4D286Z


●リンク元を閉じる
　以上の操作でリンクが設定されました。リンク元のファイルを閉じて、残りの２月から１２月を同じように操作してリンクします。


４．データが更新されたとき
　リンクを設定したファイルを開くとき、リンク元が更新されている場合、データ更新の確認ダイアログボックスが表示されます。


�@［はい］をクリックすると新しいデータに更新して開きます。
�A［いいえ］をクリックするとデータを更新しないで開きます。


　EX4D287Z



５．リンクを解除する
　リンクでデータを挿入したセルにセルポインタを合わせて［ＤＥＬ］キーを押せば解除できます。


�r操作
�@ 解除するセルにセルポインタを合わせる
◆［ＤＥＬ］キーを押す
�@ 解除される

　
EX4D288Z　　　　　　　　　　　　　　　　　　　　　　　　　　　　　EX4D289Z



●Ｅｘｃｅｌ９７／９５版
　リンクのとき、セル範囲で設定している場合は、セル範囲を選択して［ＤＥＬ］キーを押さないと解除できません。

�@リンクを設定したセルにセルポインタを合わせ［ＤＥＬ］キーを押す

�Aセル範囲をリンクしたので、削除できない

�B［ＯＫ］をクリックして削除を中止する

�Cリンクしたセル範囲を選択（反転表示）して、［ＤＥＬ］キーを押す



　
EX4D292Z（８Bit）　　　　　　　　　　　　EX4D293Z（８Bit）


６．リンクの状態を見る
　ブックに設定されているリンクの状態は、メニュー［編集(E)］�e［リンクの設定］で確認できます。リンクしているファイル名を確認することができます。リンクデータの更新はファイルを開くときに更新する／しない、を選択できますが、開いている途中で強制的に手動で最新のデータに更新するときもこのダイアログボックスで操作します。


�r操作
�@［編集(E)］�e［リンクの設定(K)］を選択する
�@ リンクダイアログボックスが表示される
�A 見終わるときは、［閉じる］をクリックする
�B［今すぐ更新］をクリックするとリンクデータが最新のデータに更新されます。


　ファイルを開いている途中に、他のユーザーがリンク元ファイルを更新したときなどで、�Dの操作で最新のデータに更新します。更新を手動にしているときも�Dで最新のデータに更新します。


　EX4D290Z



　EX4D291Z


【操作のまとめ】
●複数のファイルに保存されたデータを１つのシートに集め、そのデータを合計して年間の合計を求めるとき、リンクを使います。
●リンクを設定できるのは2つのブック間だけではなく、リンクを設定されたブック（リンク元）にもリンクを設定して階層構造にすることもできる。


ブック間のリンクの効果
●非常に大きなワークシートを保存することが実際的でない場合、たとえば、保存したファイルをバックアップするようなとき、フロッピーディスクを使うと、保存できる最大サイズは約１メガバイトになります。月ごとのブックにして１つのファイルサイズを１メガバイト以下におさえる必要がある。

●商品名など複数のユーザーが同じデータを使うとき、ブック間にリンクを設定し、関連するデータを一カ所に集めデータの重複をさける。

●複雑なモデルをリンクを設定したいくつかのブックに分類することで、関連するシートをすべて開かなくても作業できます。小さいブックほど簡単に編集でき、メモリもあまり必要としません。また、開く、保存、計算、といった操作をすばやくできます。



■データ範囲に範囲名を使う
　データの範囲に範囲名を付けて、データベース関数などに入力するデータ範囲に範囲名を使うと、データの増減などによるデータ範囲変更を自動化することができます。


１．範囲名を使うメリット

　データベース関数は、データの抽出元になるデータ範囲の指定が必要です。データ範囲をコロンでつないだセル番地（例Ｂ５：Ｅ１８など）で指定していると、データの増減などでデータ範囲が変わったとき、関数内に入力したセル番地の書き換えが必要になります。

 

        ＝ＤＳＵＭ（Ｂ５：Ｅ１８，３，Ｇ６：Ｇ７）

　　　　　　　　　　　   ↑

　　　　　　　　　　データの範囲

 

�@関数を入力したときのデータ範囲（Ｂ５：Ｅ１８）

�Aデータが増えるとデータ範囲も変更が必要になる

 

　データ範囲を１つの関数で使っているなら、その関数を書き換えるだけですみますが、同じデータ範囲を複数の関数で使っている場合、使っている関数すべてを書き換えることになり作業量が多くなり大変です。また、書き換えの必要か関数を見落とすこともありトラブルの原因にもつながります。


　　

EX4D294Z　　　　　　　　　　　　　　　　　　EX4D295Z

 

２．使い方

前項の「商品ごとの合計」で使ったDSUM関数を例にすると、以下の手順になります。

 

　セル範囲（データの範囲）　Ｂ５：Ｅ１８　に範囲名［販売経歴］を付ける

　　　　　　　　　　　　↓

　関数に範囲名を使う

        ＝ＤＳＵＭ（Ｂ５：Ｅ１８，３，Ｇ６：Ｇ７）

　　　　　　　　　　　　↓

        ＝ＤＳＵＭ（販売経歴，３，Ｇ６：Ｇ７）

　　　　　　　　　　　　↑

　　　　　　　　　　データ範囲を範囲名で指定する

 

 

 

３．範囲名を付ける

　データの範囲に範囲名を付けるときは、データ範囲を選択状態に（マウスのドラッグアンドドロップなどで反転表示に）して、メニューバーの［挿入(I)］�e［名前(N)］�e［定義(D)�s］を選択して操作します。

 

�r操作

�@ データの範囲を選択状態（反転表示）にする

�A メニュー［挿入(I)］�e［名前(N)］�e［定義(D)�s］を選択する

�@ 名前の定義ダイアログボックスが開く

�A 名前欄に仮の名前が表示される（選択した範囲の左上角セルの内容）

◆ この名前をそのまま使うときは、ここで［ＯＫ］をクリックする

◆ 仮表示の名前を［Backspace］あるは［Delete］キーで消す

�B 名前を入力する（データ範囲に付ける名前を入力します、ここでは「販売履歴」）

�C［ＯＫ］をクリックする、あるいは［リターン］キーを押す

 

　以上の操作で選択したデータ範囲に名前が付けられます。実際に利用するには、ここで設定した名前（範囲名）を使って関数を作ります。すでに入力した関数には反映されません。入力済みの関数に利用するときは、その関数の書き換えが必要です。

　　　
EX4D296Z　　　　　　　　　　　　　　　　　　　EX4D297Z

　　

EX4D298Z　　　　　　　　　　　　　　　　　　　EX4D299Z

 

 

３．範囲名を入力する

　設定した範囲名を利用するときは、関数を入力するときに範囲名を直接入力する方法と、範囲名の一覧から選択して入力する方法があります。直接入力するときは、関数を入力しているとき、キーボードから範囲名を入力してください。以下の操作は、一覧から選択して入力する方法を紹介しています。

 

�r操作

�@ 数式を入力するセルにセルポインタを合わせる

�A データ範囲を入力するところまでの数式を入力する（ここでは「＝ＤＳＵＭ（」を入力する

◆ 文字カーソルは範囲名を入力する位置で点滅していること

�B メニュー［挿入(I)］�e［名前(N)］�e［張り付け(P)�s］を選択する

◆ あるいは［Ｆ４］キーを押してもよい

�@ 名前の張り付けダイアログボックスが開く

�C 挿入する範囲名をクリックで選択（反転表示）する

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

�A カーソル位置に範囲名が挿入される

　EX4D354Z

 

　EX4D355Z

 

　EX4D356Z

 

　EX4D357Z

 

 

以上の操作で、DSUM関数のデータ範囲の位置に範囲名が入力されます。続けて、残りの部分を入力すればDSUM関数の完成です。ここで使った例題はＰ＿＿＿１−２「商品ごとの合計」操作した内容です。そちらでは名前「販売経歴」の部分にセル番地を入力して使っています。

 

�@ 関数の残りの部分を入力して［リターン］キーで決定する

        ＝ＤＳＵＭ（販売経歴，３，Ｇ６：Ｇ７）

　　　　　　　　　　　　　　この部分を入力する


　EX4D358Z

 

 

４．範囲が変化したとき

　データの増減がありデータ範囲が変わったとき、範囲を変化に合わせ修正します。範囲名を定義したときと同じダイアログボックスを表示して変更します。

 

�r操作●範囲変更のダイアログボックス

�@ データが追加された

�A 同じ品番が追加されているが、正しく合計されていない

◆ 範囲を変更するために、［名前の定義］を呼び出す

�@［挿入(I)］�e［名前(N)］�e［定義(D)�s］を選択する

�B ダイアログボックスが表示される

�A 変更する範囲名をクリックで選択（反転表示）する

�B セル参照ボタン［］をクリックする

◆ セル参照範囲の入力バーが表示される

�C セル参照範囲の入力バーが表示される

�D 現在のセル範囲に波線の枠が表示される

 

　図�Cの入力欄が反転表示になっていないときは、入力欄をクリックして［Backspace］あるいは［Delete］キーを押して内容（セル範囲）を消す（反転表示のときは、以下の操作で自動的に消えます）

 

 

　EX4D359Z

 

　EX4D360Z

　　　　　　　　　　　　　　　EX4D361Z

 

　　　EX4D362Z

 

 

�r操作●変更の操作

◆ 新しいセル範囲をドラッグアンドドロップまたは［SHIFT］＋［矢印］キーで指定する

�@ 範囲の開始点にマウスポインタを合わせドラッグする

�A 範囲の終了点へドラッグドロップする

�@ 入力欄に表示されたセル範囲が正しいかを確認する

◆ 違っているときは入力内容を削除してから上�@�Aを繰り返す

�B セル参照ボタン［］をクリックしてダイアログボックスへ戻る

�A ここでもセル範囲が正しいかを確認する

◆ 違っているときは「セル参照ボタン［］をクリックする」ところからやり直す

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

�B 変更された範囲の計算結果が表示される



　EX4D363Z

 

　EX4D364Z

 

　EX4D365Z


　EX4D366Z

 

注意

　範囲名のセル範囲を増加するとき、［ＣＴＲＬ］キーを押しながら追加範囲（増加したセル範囲）をドラッグアンドドロップで付け足すことができますが、使う関数により正しく作動しない場合があります。データベース関数（DSUM関数など）のデータ範囲として利用する場合は、本文解説４のとうりに範囲指定してください。

 

 

５．正しく計算できないケース

　セル範囲の変更操作のとき、現在の指定範囲を消さずにセル範囲を指定したり、［ＣＴＲＬ］キーを押しながら範囲指定すると、現在のセル範囲に２つ目の範囲として追加入力され複数の範囲指定になります。複数の範囲指定は、関数の種類によりエラーになります。

 

�r操作

�@ 現在の設定範囲（=Sheet1!$B$5:$E$18）

�A［ＣＴＲＬ］キーを押しながらセル範囲を指定する

�B 現在のセル範囲に今指定した範囲が追加される

　      =Sheet1!$B$5:$E$18,Sheet1!$B$19:$E$22

　　　　　　　　　↑　　　　　　　　↑

　　　　　　現在の範囲　　　　　追加された範囲

�@ 追加の状態で操作を完了するとエラーになる

　EX4D367Z

 

　EX4D368Z

 

ヒント

データ範囲内で行の挿入または削除を行えば、範囲名で入力または、セル番地で入力しているデータ範囲にかかわらず自動更新されます。挿入または削除の操作は、行ラベル（シートの左端に表示される行番号）を右クリックして表示されるショートカットメニューの［挿入］または［削除］を使います。


【操作のまとめ】
●データの範囲が増減したときは、範囲名に設定したセル範囲を増減した範囲に変更する
●範囲名に設定したセル範囲を変更すると、範囲名を使ってる関数すべてに反映される
●セル範囲を変更するとき、コンマで結合されたセル範囲指定にならないように注意する



■累計表を作る

　昨日のデータに今日のデータを加算するような、累計表を作成してみましょう。加算だけでなく収支を考えた内容の累計表にします。

 

１．累計表

　ここで作成する累計表を以下のようにします。データを入力するセルに色付けして明確にしています。日付、科目、摘要、には色付けしていませんが日付や文字を入力セルです。ここで解説する計算式に注目したセルにだけ色付けしています。

 

�@ 収支を求める累計表を作成する

�A データを入力するセルに色付けする

�B 累計欄（Ｇ６〜Ｇ１８）に計算式を入力する

　EX4D369Z　　　　作例 1-2-J



２．計算式
　累計表のスタイルを作り、計算式を入力します。この表の場合、１行目（セルＧ６）と２行目から以降（Ｇ７〜Ｇ１８）に入力する計算式が少し違います。１行目はセルＧ４の繰越額に収支を加減算しますが、２行目からは前行の値に収支を加減算する数式になります。セル自体の空白は数値０と見なして計算されます。


●最初の１行目
　最初の１行目（セルＧ６）に入力する計算式は、繰越額（Ｇ４）に収入（Ｅ６）を加算して、支出（Ｆ６）を減算する数式になります。１行に収入と支出を同時に入力することはありませんが、その行に入力されるデータ（金額）が、収入と支出のどちらになるのか分からないので双方を計算式に入れておきます。


式−１　＝Ｇ４＋Ｅ６−Ｆ６

�r操作

�@ 計算式を入力するセルにセルポインタを合わせ、数式を入力する

        ＝Ｇ４＋Ｅ６−Ｆ６　を入力する

�@［リターン］キーを押して入力を決定すると結果が表示される


　EX4D370Z

　　EX4D371Z

 

●２行目から以降の数式

　前行の値に収入を加算して、支出を減算する数式になります。２行目に入力する数式を例にすると以下のようになります。

 

式−２　＝Ｇ６＋Ｅ７−Ｆ７

 

�r操作

�@ ２行目に数式を入力する「＝Ｇ６＋Ｅ７−Ｆ７」

�@ 計算結果が表示される

◆ この数式を下に続くセルＧ８〜Ｇ１８に複写する

�A 複写元のセルにセルポインタを合わせる

�B セルポインタの右下■にマウスポインタを合わせセルＧ１８までドラッグする

�A ドロップすると結果が反転表示で表示される

�C このあたりの空白セルをクリックして反転表示を解除する

 

　このままではすべての行に金額が表示されて少し見にくいですね。それに、収支が入力されていないのに残高だけが表示されているのも、なんだかへんです。次の処理でこれを解決します。

 

　EX4D372Z

　EX4D373Z

 

 

　EX4D374Z

 

　　　　　　　　　　　　　EX4D375Z

 

 

３．未入力行の表示を消す

　収入または支出のセルに金額が入力されたとき、計算結果を表示するようにIF関数を使った数式に変更します。COUNT関数を使えば、数値が入力されたセル数を求めることができるので、収入セルと支出セルをCOUNT関数で数値（金額）が入力されたかを調べます。

 

        ＣＯＵＮＴ（Ｅ６：Ｆ６）　　結果が０のとき、両方に金額の入力なし

　　　　　　　　　　　　　　　　　　結果が１のとき、どちらかに金額の入力あり

 

これをIF関数の条件式に当てはめて、以下のようにします。数式の書き換えは、書き替えるセル（以下の操作ではセルＧ６）にセルポインタを合わせて［ｆ・２］キーを押せば編集できます。

 

●１行目の数式を変更する

�@ セルＧ６の数式を書き換える

        ＝ＩＦ（ＣＯＵＮＴ（Ｅ６：Ｆ６）＝０，””，Ｇ４＋Ｅ６−Ｆ６）　に変更する

�@ 金額表示が消える

�A 下に続くセルにはエラーが表示される

 

 

　

EX4D376Z　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　EX4D377Z

 

　このとき、下に続くセル（２行目から以降）にエラー（#VALUE!）が表示（図�A）されます。これは計算不能を現すエラー表示です。IF関数で空白を表示するようにしたことにより、下に続くセルでは空白を加算する矛盾が生じたからです。セルＧ７に入力している数式で見ると以下のようになります。先に入力した数式（式−２）のときは、空白を０と見なされ計算されましたが、IF関数が出力する空白（””）は数値０と見なされません。

 

　      セルＧ７の数式の場合

　　　　＝Ｇ６＋Ｅ７−Ｆ７

　　　　　　↑

　　　　　このセル内容がIF関数が出力する空白（””）になったので計算できない

 

　このエラー表示は、１行目と同じようにIF関数を使った数式に変更することで解消できます。続けて、以下の操作を実行してください。

 

●２行目から以降の数式を変更する

ここでもセルＧ７（２行目）を変更して、その数式を下へ続くセル（Ｇ８〜Ｇ１８）へ複写します。複写の方法は２を参考にしてください。

 

�r操作

�@ ２行目の数式を変更する

        ＝ＩＦ（ＣＯＵＮＴ（Ｅ７：Ｆ７）＝０，””，Ｇ６＋Ｅ７−Ｆ７）　に変更する

�@ エラー表示が消える

　

EX4D378Z　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　EX4D379Z

 

４．データを入力してみる

正しく計算できるかを確かめるために、仮のデータを入力してみます。１行目、２行目、とデータを入力して残高が正しく表示されるかを確かめてください。

 

�r操作

�@１行目にデータを入力する

�A正しい値が表示される

�B２行目にデータを入力する

�C正しい値が表示される

�B データの入力していない行は空白表示


　　EX4D380Z



　ふつうは、上から順にデータを入力するので、これで累計表が完成したことになります。ところが、データ入力の仕方でエラーが表示されます。たとえば、月が代わる行で１行空けてデータを入力すると、エラー#VALUE!が表示されます。このエラーの原因も先ほど解説したことと同じです。この現象は、計算を他のセルで行い、その結果を残高のセルに表示するようにすれば解決します。


�r操作

�@ 行を空けてデータを入力する

�@ 計算が正しくできない

�A 上側のセル内容が空白なので計算不能になる


　EX4D381Z



５．計算を他の場所でする
　行を空けたときに表示されるエラーを回避するには、計算式を別の場所へ移動して、別の場所で計算した値を累計表に表示するように処理しします。


�@累計計算を他の場所で行う

        ここでは、ゼロ表示やエラー表示を消すIF関数を使わない計算式を入力する

        ２で入力した数式（以下の式）をそのまま使います。

        ＝Ｇ４＋Ｅ６−Ｆ６　をセルＪ６に入力する

       ＝Ｊ６＋Ｅ７−Ｆ７　をセルＪ７に入力して、下に続くセル（Ｊ１８まで）に複写する

�A残高の数式は、�@で表示された値を参照する数式を入力する

        ここでは、IF関数を使って収支の入力判断を行う

       ＝ＩＦ（ＣＯＵＮＴ（Ｅ６：Ｆ７）＝０，””，Ｊ６）　をセルＧ６に入力して、下に続くセル（Ｇ１８まで）に複写する

�B行を空けて入力しても累計表にエラーが表示されなくなる


　計算している部分（セルＪ５〜Ｊ１９）がめざわりだったり、じゃまになるようなら、この列を非表示に設定したり、もっと離れた場所に作ればよい。


　EX4D382Z

　EX4D383Z



【操作のまとめ】
●セル自体の空白は数値０と見なされるが、IF関数などで表示する空白（””）は数値０と見なされないので、数式を考慮する必要がある
●計算に矛盾が生じるようなとき、計算を別の場所で行い、計算結果を目的の場所に表示する方法がある。

■複数の品名を合計する
　データベースの合計関数DSUM()は一つの商品について合計するだけではありません。抽出条件の同じ項目を縦に複数の商品を入力すれば複数の商品をまとめて合計することができます。


１．合計関数
　抽出条件（データの範囲、合計を求める列）以外は、前項の「商品ごとの合計」と同じなので、そちらをご覧ください。データベース合計関数DSUMの書き方は以下のようになります。

        ＤＳＵＭ（データの範囲，合計を求める列，抽出条件）
　　　　　　　　　　　　　　　　　　　　　　　　　↑
　　　　　　　　　　　　　　　　　　抽出条件のセル範囲を複数行にする

　前項の例と同じ集計表（�@）を使うと、次のように、データの範囲と、合計を求める列、は同じ値になり、抽出条件が複数のセル範囲（�A）で指定することになります。

　      　＝ＤＳＵＭ（Ｂ５：Ｅ１８，３，Ｇ６：Ｇ８）
　　　　　　　　　　　　　　　　　　　　　  ↑

　　　　　　　　　　　　　　　　　　抽出条件の指定範囲を複数行にする

図Ａ
　EX4D254Z
例 1-2-F