●各月のデータをセル参照式で集める

　同じシート内のセルを参照するためのセル参照式はセル番地を記述するだけでよい、しかし、他のシートに入力している値を参照するセル参照式はシート名とセル番地を記述することになる。たとえば、１月の店舗売上額なら次のような参照式になる。１や２つならキーボードから入力しても苦にならないが、数が多いとやっかいな作業だ。

 

        ='１月'!D38

　　　　　　↑　↑ ↑

　　　　　　�@　�A �B

 

�@参照するシート名を「’」で囲んで記述する

�Aシート名とセル番地を繋ぐ記号「！」

�B参照するセル番地を記述する

 

●マウス操作でセル参照式を入力する

　シート名が短ければよいが、長い名前だとその分よけいに手間がかかる。そこで、セル参照式をマウス操作だけで入力する方法を紹介する。なお、以下の操作はＥｘｃｅｌを標準的なセットアップ状態で使用している。セットアップ状態やシステムの状態により操作中の［数式の結果を表示するダイアログ］が表示されない場合があるが、操作に支障はない。以下はセルＣ７に入力する例だが、他のセルも同じように操作すればよい。

 

＞操作

�@数式を入力するセルをクリックしてセルを選択する

�A数式バーの［＝］ボタンをクリックする

�B数式バーに＝が入力され、数式の結果を表示するダイアログが開く

�C参照先のシートタブをクリック（左ボタン）する

�Dシート表示が切り替わり、数式バーにシート名が自動入力される

�E参照するセルをクリック（左ボタン）する

�Fセル番地が入力される

�G［チェック］をクリックする

�H数式入力が完了して結果が表示される

 

 

　

EX4C542Z　　　　　　　　　　　　　EX4C543Z

 

 

 

 

　　

EX4C552Z　　　　　　　　　　　　　　　　　　　　　　　　　EX4C565Z

 

　

　EX4C546Z

 

 

 

 

 

 

 

 

 

 

 

 

 

■グラフを作る

　グラフは、ツールバーの［グラフウィザード］ボタンで簡単に作成できる。グラフにするデータ範囲を選択状態にして、ボタンをクリックすればグラフウィザードが起動する。グラフの種類を選択して完了すれば、即座にグラフが表示される。既定値のサイズでできあがるので、適切にサイズ変更して、見やすい位置へ移動すけばよい。サイズ変更は、グラフの周りに表示される■にマウスポインタを合わせ、ドラッグ＆ドロップすればよい。グラフ内部の白い部分をドラッグ＆ドロップすれば表示位置の移動になる。

 

●データ範囲を指定してグラフを作る

　グラフにするデータ範囲を指定するときのポイントは、項目名も同時に指定することだ。そうすれば、縦横の項目名も自動的にグラフの項目名として表示される。また、選択するセル範囲が広いときは、範囲選択をマウスで行わずに、［ＳＨＩＦＴ］＋［矢印］キーで操作するほうがやりやすい。

 

＞操作

�@グラフ化するデータ範囲を選択状態にする

        （図ＡでみればＢ６：Ｎ１０になる）

�A［グラフウィザード］ボタンをクリックする

�Bグラフウィザードが起動する

�C［標準］タブのグラフの種類を選択する（ここでは「縦棒」を選択）

�D形式を選択する（ここでは「３Ｄの集合縦棒グラフ」を選択）

�E［サンプルを表示する］をクリックすると見本が表示される

�F［完了］をクリックする

�Gグラフが自動作成される

 

　EX4C557Z

 

　　

EX4C558Z　　　　　　　　　　　　　　　　　　　　EX4C559Z

 

 

　EX4C560Z

 

●グラフの表示位置の移動

＞操作

�@白い部分をドラッグ＆ドロップして任意の位置へ移動する

 

　EX4C562Z

 

●グラフのサイズ変更

　グラフを選択状態にすれば、周りに■が表示される。■にマウスポインタを合わせドラッグ＆ドロップすればサイズが変更される。■が消えているときは、白い部分をクリックすれば■が表れる。

＞操作

�@グラフの白い部分をクリックして選択状態にする

�A■をドラッグ＆ドロップしてサイズ変更する

 

　EX4C563Z

 

［上級編］セル参照式を間接セル参照にする

　各シートで集計した値を参照する数式をマウス操作で入力した。数式を手操作で入力するより手間が省けるのだが、それでも１つ１つ入力するには手間の掛かる作業だ。１つの集計だと縦あるいは横の合計を求める数式を１つだけ入力して、あとはマウスのドラッグ＆ドロップ操作で複写できたが、他のシートを参照するセル参照式もできないのだろうか。マウスのドラッグ＆ドロップではシート名は書き変わらないので、セル参照式を直接ドラッグ＆ドロップで複写することはできない。今回のケースではドラッグ＆ドロップで数式を複写できないのか、というとそうでもない。かなり高度な応用力が必要だが可能だ。まず、ＡＤＤＲＥＳＳ関数を使って参照先のセル番地を作成する。そして、その結果を元に間接セル参照を行うＩＮＤＩＲＥＣＴ関数を使って値を参照すればよい。この方法で行えば数式のドラッグ＆ドロップ複写が可能だ。

 

●ＡＤＤＲＥＳＳ関数を使って参照先のセル番地を作成する

　たとえば、１月の店舗売上額（図４−１で示すセルＤ３８）なら次のような式になる。以下の数式を集計表のじゃまにならないところ（ここではセルＣ２）に入力する。結果は、先にマウス操作で入力したセル番地と同じセル番地（'１月'!D38）が返される。

 

=ADDRESS(38,4,1,,C6)

                ↑   ↑ ↑ ↑ ↑

                �@   �A �B �C �D

 

�@行番号 セルの行番号を指定する

�A列番号 セルの列番号を指定する

�B参照の型       通常は絶対参照（1あるいは省略）を指定する

�C参照形式       通常はＡ１形式（TRUEあるいは省略時）を指定する

�Dシート名       シート名を記述しているセル番地を指定する

                シート名を直接記述するときはダブルクォーテイションで囲む

                   例 ADDRESS(38,4,1,,"１月")

 

◆セル番地Ｄ３８を指定する場合（図４−１）

　　　　　　　　　　　　EX4C548Z

 

＞操作

�@セルＣ２に数式を入力する

�A参照先のセル番地が作成される

 

　EX4C549Z　　　EX4C550Z

 

 

●ＩＮＤＩＲＥＣＴ関数を使って値を参照する

　ＡＤＤＲＥＳＳ関数を使って作成したセル番地は文字列として作成されるので、このままではセル参照式として使うことはできない。先頭に＝を付けても文字として表示されるだけである。作成したシート名付きのセル番地は、間接セル参照を行うＩＮＤＩＲＥＣＴ関数で利用する。この関数は、参照先のセル番地を文字で指定するようになっている。たとえば、セルＣ２に文字として表示しているシート名付きのセル番地を利用するのなら、次のように記述すればよい。

 

=INDIRECT(C2)

                  ↑

　　　　　　　　セル番地を表示しているセル番地を指定する

 

　このようにセル番地が入力あるいは表示されているセル番地を指定するので、間接セル参照という。

 

＞操作

�@セルＣ７に数式を入力する

�AセルＣ２に表示されているセル番地の値が参照される

 

　EX4C551Z　　　　EX4C552Z

 

●ドラッグ＆ドロップで複写する

　残りの配達と自販の間接セル参照を作成して１月分を完成させ、２月〜１２月分をマウスのドラッグ＆ドロップ操作で複写すれば、完成する。なお、計の値は間接セル参照ではなくＳＵＭ関数で計算すればよい。各セルに入力する数式は次のとおりだ。なお、ドラッグ＆ドロップによる複写は罫線も一緒に複写されるので、線種にもよるが、先に罫線を引いている場合は引き直しが必要になる。詳しい解説は省くが、Ｃ列の左側に細線、Ｂ列の右側に太線を引いて、Ｃ列をドラッグ＆ドロップすれば太線が複写されるのを防ぐことができる。

 

セルＣ３ =ADDRESS(38,5,1,,C6)

セルＣ４ =ADDRESS(38,6,1,,C6)

セルＣ８ =INDIRECT(C3)

セルＣ９ =INDIRECT(C4)

セルＣ１０       =SUM(C7:C9)

 

＞操作

�@１月分の各セルに数式を入力する

�AセルＣ２〜Ｃ４を選択してドラッグ＆ドロップで１２月まで複写する

�BセルＣ７〜Ｃ１０を選択してドラッグ＆ドロップで１２月まで複写する

◆最後に２行〜４行を非表示にすれば冒頭の図のようになる

 

　　　　　

EX4C553Z　　　　　　　　　　　　　　EX4C555Z

 

●ＡＤＤＲＥＳＳ関数●

行番号と列番号からセルのアドレス(参照)を表す文字列を作成する

書式    ADDRESS(行番号,列番号,参照の型,参照形式,シート名)

 

行番号     行番号を指定する

列番号     列番号を指定する、先頭列（Ａ列）を１とした値で指定する

参照の型   作成するセル参照の型を指定する（省略は１を指定したと見なされる）

指定値      作成されるセル参照の型

1/省略     絶対参照

2          行が絶対参照で、列が相対参照

3          行が相対参照で、列が絶対参照

4          相対参照

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

                参照形式　　　　　形式

                　TRUE（省略）   A1形式

                　FALSE         R1C1形式

シート名   参照先ワークシート名を指定する（省略するとシート名は使用されません）

 

●ＩＮＤＩＲＥＣＴ関数●

間接的なセル参照するための関数、間接セル参照という。

書式    INDIRECT(参照文字列, 参照形式)

 

参照文字列   参照先のセル番地を文字列で入力されているセルの番地を指定する

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

                TRUE    A1形式（省略時）

                FALSE   R1C1形式

 

 

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

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

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

 

 

 

EX4H975Z

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

  セル参照式でデータを集約 年間売上グラフを作成する