第７回　年間の集計表を作る［２］

　背景色を省いた数式だけのコピーは形式を選択してコピー

　同じスタイルの集計表はシートごと複写して作る

 

 

 

●シート間の合計

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

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

 

 

 

 

 

●未入力欄の０だけを消す

 

 

 

 

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

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

 

 

 

 

 

●シート名を付けて分かりやすくする

　新規のブックを開くとワークシートの下部にシート名が表示される。これをシートタブと呼び、クリックするとシート表示を切り替えることができる。タブに表示されるシート名は、初期値としてＳｈｅｅｔ１、Ｓｈｅｅｔ２、Ｓｈｅｅｔ３、のように自動的に付けられる。このシート名を目的に添った名前に付け替えてどのシートに何を入力しているのか分かりやすくすることができる。

 

 

 

●シートを複写して作成する

　

 

●シートの移動

 

●シートの削除

 

●シートを複写して使う

移動

 

 

 

 

 

 

実践活用講座

 エクセル

　 第7回

 

　前回に引き続き年間の集計表を作成する。今回は、計算結果のゼロ表示の消し方、シートの複写・移動・削除、シート間の集計計算を解説する。

 

 

図Ａ

　EX4C496Z

 

　　　　　　　　　　　　月ごとのシートは複写で作る

　　　　　　　　　　　　　　　　　　　　　　

 

●毎月の集計表

　年間のデータ集計を行うために月単位の集計表を１２ヶ月分作成することを考えた場合、１ヶ月分の集計表（図Ｂ）を作成して、その集計表をシート単位でまるごと複写で作成すればよい。前回でもふれたが、２月の２９日もあるが、各月の日数は決まっているので変化する曜日を自動表示させ、各月用に自動更新させれば、各月用の集計表をわざわざ作らなくてもよい。そのために、前回で曜日を自動表示させる方法を解説した。前回で作成した集計表をそのまま利用していも良いが、ゼロ表示を消す工夫を加えてから複写することにする。複写で１２ヶ月分の集計表を作成して、さらに図Ａの年間集計を行う集計表を作成する。

 

図Ｂ　前回作成した月データの集計表

　

EX4C480Z

 

●計算結果の０を消す

　計算結果のゼロ（０）を消す方法にＩＦ関数を使って結果のゼロを判断する手法がよく使われるが、単純にゼロを消せばいいというものでもない。図ＢのセルＫ１１（Ｇ１１とＪ１１の計を含む）の場合は、データの入力がないのでゼロの非表示は好ましいが、セルＫ１４の場合ばどうだろう。データは入力されているが損益の計算結果がゼロになる場合、ゼロが表示されないと違和感がある。この場合はゼロが表示された方が好ましい。参考のために、セルＫ１４を例に計算結果のゼロを判断する数式を紹介すると次のようになる。

 

        =IF(G14-J14=0,"",G14-J14)

 

＞操作

�@セルＫ１４に数式を入力する

�A計算結果のゼロが非表示になる

 

　　

EX4C488Z　　　　　　　　　　　　　　　　　　　　　　EX4C489Z

 

●未入力欄の０だけを消す

　ゼロ表示を消すための方法でデータの入力状態を判断すれば、計算結果にかかわりなく、データの入力がなければ何も表示しない、データの入力があれば計算結果を表示する、といったことができるようになります。セルに入力されているデータを検査する関数にＣＯＵＮＴ関数がある。この関数は数値が入力されてるセルの数を返す。これを利用して数値入力があるのかをＩＦ関数で判断させればよい。たとえば、［計］の列に入力する数式をセルＧ１１の場合で考えれば次のようになる。

 

=IF(COUNT(D11:F11)=0,"",SUM(D11:F11))

 

　この数式の意味は、セル範囲Ｄ１１〜Ｆ１１の３つのセルを検査して、数値入力がなければ（COUNT(D11:F11)=0）何も表示しない（””）、ＣＯＵＮＴ関数の結果が０以外（数値入力がある）場合は、SUM(D11:F11)を実行する。

 

●−−−−−−−−−−−−−−−−−−−−−−−−−●

ＣＯＵＮＴ関数

指定したセル範囲の数値が入力されているセル数を返す

書式

＝ＣＯＵＮＴ（セル範囲）

●−−−−−−−−−−−−−−−−−−−−−−−−−●

 

＞操作

�@セルＧ１１に数式を入力する

        =IF(COUNT(D11:F11)=0,"",SUM(D11:F11))

�Aゼロ表示が消える

�Bセル範囲内のどれかに値（ここでは１０００）を入力する

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

        ゼロ（０）を判断しているのではないので、�Bで値に０を入力すれば０が表示される

�D表示の確認後は次の操作の確認のために入力データを消しておく

 

　EX4C481Z

 

　

EX4C482Z　　　　　　　　　　　　　　　　　　　EX4C497Z

 

　同じように、Ｊ列の［計］とＫ列の［損益］の数式は次のようになる。ここでは、それぞれ１１行目の数式だけを書き替えたが、他の行も同じように書き替えればよい。数式は、ドラッグ＆ドロップの操作で複写できるので、１１行目に入力した数式を上下のセル範囲に複写すればよい。なお、セルＪ１１の数式を変更した時点で損益の計算式がエラー表示される。これは、セルＪ１１のゼロ表示を消すために空白（””）を表示させたためである（空白は計算できない、という意味だ）。このエラーはセルＫ１１の数式を以下の数式に変えることにより解消される。詳しく解説すれば、Ｇ１１とＪ１１が数値（COUNT(G11,J11)=2）のとき計算（G11-J11）するになる。

 

セルＪ１１

=IF(COUNT(H11:I11)=0,"",H11+I11)

セルＫ１１

=IF(COUNT(G11,J11)=2,G11-J11,"")

 

＞操作

�@セルＪ１１に数式を入力する

�A数式を変更したことによりエラー表示される

�BセルＫ１１に数式を入力する

�Cエラーおよびゼロ表示が消える

 

　EX4C483Z

 

　EX4C484Z

 

　　

EX4C485Z　　　　　　　　　　　　　　　　　　　　　　　　　EX4C486Z

 

●シート名を付けて分かりやすくする

　新規のブックを開くとワークシートの下部にシート名が表示される。これをシートタブと呼び、クリックするとシート表示を切り替えることができる。タブに表示されるシート名は、初期値としてＳｈｅｅｔ１、Ｓｈｅｅｔ２、Ｓｈｅｅｔ３、のように自動的に付けられる。このシート名を目的に添った名前に付け替えてどのシートに何を入力しているのか分かりやすくすることができる。シートを複写する前にシート名を４月に付け替えておこう。

 

＞操作

�@シートタブにマウスポインタを合わせて右クリックする（ショートカットメニューが開く）

�A［名前の変更］を選択する

�Bシート名が選択される

�C目的の名前に書き替えて［Ｅｎｔｅｒ］キーを押す

 

 

 

 

 

　EX4C490Z　　　　EX4C491Z

 

　EX4C492Z

 

 

●シートを複写して作成する

　シートを丸ごと複写するには、シートタブを右クリックして表示されるショートカットメニューの［移動またはコピー］を使う。操作例として、５月分のシートを作成してみよう。操作の注意点は、シートの移動またはダイアログが開いたとき、まず最初に［コピーを作成する］にチェックを付けることだ。付け忘れるとシートの移動になってします。複写されたシートには番号の付いたシート名になっているので目的の名前に書き替えればよい。複写で作成したシートのセルＣ３に入力している月数を５に書き替えれば、曜日表示が５の値に自動的に書き変わる。同じように操作して残りの１月〜３月と６月から１２月を作ればよい。各月末日数の調整も忘れずに施しておこう。最後に、年計用のシート（図Ａ）も複写を利用して作成する。

 

＞操作

�@シートタブにマウスポインタを合わせて右クリックする（ショートカットメニューが開く）

�A［移動またはコピー］を選択する

�Bシートの移動またはダイアログが開く

�C［コピーを作成する］をクリックしてチェックを付ける

�D複写先をクリックする（クリックしたシートが後へ移動する）

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

�Fシートが複写される（シート名に番号が付けられる）

 

　EX4C493Z　　EX4C494Z

 

　EX4C495Z

 

●シートの移動

　誤操作などで複写したシートが目的の場所に挿入できなかったときは、シートタブをドラッグ＆ドロップして任意の位置へ移動させることが可能だ。

 

＞操作

�@移動させるシートのシートタブをマウス右ボタンでドラッグする

�Aドロップした位置へ移動する

 

 

　EX4C511Z　　　EX4C512Z

 

●シートの削除

　間違ってシートを挿入してしまったときは、削除するシートタブを右クリックしてショートカットメニューを表示、［削除］を選択してシートを削除する。

 

＞操作

�@削除するシートのシートタブを右クリックする（ショートカットメニューが開く）

�A［削除］をクリックする

�B削除の確認ダイアログボックスが開く

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

 

　

EX4C507Z　　　　　　　　　　　　　　　　EX4C508Z

 

●シートタブの表示領域

　シートをいくつも追加すれば、シートタブの表示がはみ出す。このときは、領域の右端に表示されている領域バーをドラッグ＆ドロップで左右に移動して表示幅を変えればよい。また、シートタブのスクロールボタンでタブ表示を左右にスクロールさせることもできる。

 

　EX4C506Z

↑↑↑↑（スクロールボタン）　　　　　　　　　　　　　　　　　　 　↑

先頭　１つ前　１つ後　最後　　　　　　　　　　　　　　　　　　表示幅の領域バー

 

●シート間の合計

　他のシートに入力しているデータを集計する場合、セル番地にシート名を付けて計算式を記述すればよい。たとえば、１月から１２月の各シートのセルＤ７を合計するなら、ＳＵＭ関数を使って次のように記述すればよい。

 

        =SUM('１月:１２月'!D7)

　　　　　　　　　　　↑　　　　　　↑

　　　　　　　シートの範囲　　　　セル番地

 

　セル範囲と同じように、はじまりのシート名と終わりのシート名を半角コロン（：）で繋いで、全体を半角のシングルクォーテイション（「’」７と同じキーにある）で囲む。その後に半角の！に続けてセル番地（ここではＤ７）を記述すればよい。この数式をシート［年計］のセルＤ７に記述すれば、１月〜１２月の各セルＤ７の合計が計算される。

 

 

＞操作

�@シート［年計］のセルＤ７に数式を入力する

�Aシート１月〜１２月のセルＤ７に入力されている値の合計が表示される

　　

EX4C513Z　　　　　　　　　　　　　　　　　　EX4C514Z

 

●数式だけを複写する

　他のセルについても同じように数式を入力すればよいが、シート名を使った数式でも複写で入力することができる。しかし、通常の［コピー］と［張り付け］だと、セルの色や罫線も同時に複写される。ここでの場合、セルにより背景色が違ったり、罫線の線種が違っているセルもある。このような場合、セルに入力した数式だけを複写するようにすればよい。

 

＞操作

�@複写元のセル（ここではＤ７）を右クリックしてショートカットメニューを開く

�A［コピー］をクリックする

�Bコピー先のセル（ここではＤ８）を右クリックしてショートカットメニューを開く

�C［形式を選択して張り付け］をクリックする

�D形式を選択して張り付けダイアログが開く

�E［数式］欄をクリックして●印を付ける

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

�G数式がコピーされ、合計が表示される

 

　　　

EX4C515Z　　　　　　　　　　　　　　　　　　EX4C516Z

　EX4C517Z　　

　　　　　　　　　　　　　　　　　　　　　　　EX4C518Z

 

 

　複写元に表示されている波線の点滅が続く限り、張り付け操作を繰り返すことができる。また、セル範囲を選択して［形式を選択して張り付け］の操作を行えば、一度にコピーすることができる。なお、点滅を解除するには、キーボードの［ＥＳＣ］キーを押せばよい。

 

＞操作

�@セル範囲を選択する

�A選択したセル範囲内で右クリックしてショートカットメニューを開く

◆［形式を選択して張り付け］をクリックして張り付け操作を行う

◆選択したセル範囲に数式がコピーされ、合計が表示される

 

　EX4C519Z

 

 

 

 

 

 

 

 

［上級編］参照先のセル番地を数式で自動作成する

　先ほどは、シート間のセル参照をマウスのドラッグ＆ドロップ操作で行いましたが、この操作も１２ヶ月分も行うのは大変な作業だ。そこで、もっと簡単な操作で１２ヶ月分のセル参照式を入力できる手法を使用会する。Ｅｘｃｅｌに組み込まれている検索関数にセル参照を行う関数がある。

 

 

 

　列の項目名をシート名に見立てて、ＡＤＤＲＥＳＳ関数を次のように記述すれば、数式で参照先のセル番地を作成することができる。

 

       

 

 

        =ADDRESS(      )

 

 

 

 

 

 

 

 

＞操作

�@セルＣ２に１９９９を入力する

�A２９日は表示されない

�BセルＣ２に２０００を入力する

�C２９日は表示される

 

 

 

 

 

 

●ＡＤＤＲＥＳＳ関数●

指定した日付（シリアル値）から月数を返す

書式     ＝ＭＯＮＴＨ（日付シリアル値）

日付シリアル値はＤＡＴＥ関数あるいは日付を入力しているセル番地を指定する

 

●ＩＮＤＩＲＥＣＴ関数

間接的なセル参照を行う（間接セル参照という）

書式     ＝ＩＮＤＩＲＥＣＴ（参照文字列，参照形式）

参照文字列       文字列で記述したセル番地

参照形式 セル番地の形式を指定する

                TRUE あるいは 省略        Ａ１形式

                FALSE                   R1C1形式

 

−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

次回（第８回）この年間集計のデータからグラフ作りのための集計表とグラフ表示を解説する。

次回（第９回）週単位の集計表を作成する。曜日の表示を固定（一覧形式）に並べて日を自動表示させる

次回（　　　）時間の計算、勤怠表を作る

 

  関数で結果の表示・非表示を判断 複数シートを横断して集計する

 年間の集計表を作る[2]