［［ 目次 ］］
年間の集計表を作る[1]

●カレンダー型の集計表------------------------------------- 000
●繰り返しコピーの範囲指定したコピー------------------------- 000
●曜日を求める-------------------------------------------- 000

●ドラッグ＆ドロップで曜日入力する-------------------------- 000

●列の非表示と表示---------------------------------------- 000

［ 上 級 編 ］ドラッグ＆ドロップで自動入力する項目を追加する---- 000

●新しいリストを登録する----------------------------------- 000

●シートに入力している内容で登録する------------------------- 000

●登録の削除--------------------------------------------- 000

 

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

●毎月の集計表-------------------------------------------- 000

●計算結果の０を消す-------------------------------------- 000
●未入力欄の０だけを消す----------------------------------- 000
●シート名を付けて分かりやすくする-------------------------- 000
●シートを複写して作成する--------------------------------- 000
●シートの移動-------------------------------------------- 000
●シートの削除-------------------------------------------- 000
●シートタブの表示領域------------------------------------- 000

●シート間の合計------------------------------------------ 000

●数式だけを複写する-------------------------------------- 000

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

●ＡＤＤＲＥＳＳ関数●------------------------------------- 000

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

 

年間の集計表を作る[3] ------------------------------------ 000

●各月の集計値を集めてグラフにする-------------------------- 000

●新しいシートを追加する----------------------------------- 000

●行と列を入れ替えてコピーする------------------------------ 000

●各月のデータを集める集計表-------------------------------- 000

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

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

■グラフを作る-------------------------------------------- 000

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

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

●グラフのサイズ変更-------------------------------------- 000

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

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

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

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

●ＡＤＤＲＥＳＳ関数●------------------------------------- 000

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

縦型カレンダー式集計表[1] --------------------------------- 000

●１日の表示位置------------------------------------------ 000

●結果を参照して１以下を非表示にする------------------------- 000
●数式だけを複写する-------------------------------------- 000

●１日の表示位置を自動化する-------------------------------- 000
●ＷＥＥＫＤＡＹ関数-------------------------------------- 000

�r操作［１日のシリアル値を求める］-------------------------- 000
●先頭を日曜にする---------------------------------------- 000
●月数を参照する------------------------------------------ 000

上級編　　　　　　　　　　　月末の日数を正しく表示する
●３１日以上を非表示にする方法------------------------------ 000
●大小の月や閏年の２８日を判断する-------------------------- 000
●月日で表示する------------------------------------------ 000

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

実践活用講座

 エクセル

　 第6回

 

　背景色の異なるコピーは範囲指定の繰り返しコピーを使う

 

　今回から３回の予定で年間の集計表を作成する。月間集計として１枚のシートに１月分のカレンダースタイルの集計表を作成して、１２枚のシートで１２月分の年間集計を求める方法を紹介する。

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

　　　　　　　　　　　　　　　　　　　　

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

 

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　

 

 

 

 

 

 

 

 

 

 

 

 

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　EX4K759Z

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

　　　　　　　　　　　　　　　　　　　　　　　EX4K762Z

 

 

 

 

作成手順

　さて、どこから書き始めればいいのでしょう。

 

●繰り返しコピーの範囲指定したコピー

　図Ａの集計表のように行毎に色分けしている場合、連番や数式をドラッグ＆ドロップでコピーするとコピー元の選択状態により色分けが崩れることがある（図Ｂ失敗例）。この現象を防ぐ方法として、交互に色付けしているセル範囲（ここでは２行分）を選択して、それをドラッグ＆ドロップでコピーすればよい。まず、複写する計算式を２行分入力して、その２行（２つのセル）をドラッグ＆ドロップする（図Ｃ）。連続するセル範囲なら縦だけでなく横にも連続するセルを範囲に含めてもよい（図Ｄ）。また、複写先のセル範囲が広い（長い）ときは、数回に分けてドラッグ＆ドロップすれば操作しやすい。

 

計算式

　各列の計算式は次のようにしている。表内の１行目と２行目（ワークシートの７行目と８行目）に入力する式である。各式を各セルに入力してドラッグ＆ドロップの操作で下へ続くセルに複写すればよい。

 

セルＧ７　＝ＳＵＭ（Ｄ７：Ｆ７）     セルＪ７　＝Ｈ７＋Ｉ７       セルＫ７　＝Ｇ７−Ｊ７

セルＧ８　＝ＳＵＭ（Ｄ８：Ｆ８）     セルＪ８　＝Ｈ８＋Ｉ８       セルＫ８　＝Ｇ８−Ｊ８

 

日数をドラッグ操作で入力する

�@はじまりのセルに１を入力、２つ目のセルに２を入力して、入力した２つのセルを選択する

�A選択範囲の右下角をドラッグする

�B色分けの状態でコピーされる

　　　　

EX4C428Z　　　　　　EX4C429Z　　　　　　　　　EX4C430Z

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

数式を入力してコピーする成功例

�@２行分の数式を入力する

�A数式を入力した２つのセルを選択する

�Bドラッグ＆ドロップで下へ続くセルにコピーする

�C色分けが保たれた状態でコピーができる

 

図Ｃ

　　　　　

EX4C434Z　　　　　　　　EX4C435Z　　　　　　　　EX4C436Z

 

図Ｄ

　EX4C437Z

 

 

●曜日を求める

　ＷＥＥＫＤＡＹ関数を使えば、日付のシリアル値からその日の曜日を番号で求めることができる。日付のシリアル値は１９００年１月１日を１として、１日進むごとに１を加算した値のことで、日付計算などでよく使われる。指定する日付のシリアル値を求めるのはＤＡＴＥ関数だ。この２つの関数を組み合わせて、次のようにすれば指定した日付の曜日が番号で求められる。年と月を指定するセル番地を絶対形式（＄付き）で指定して、日を相対形式（＄なし）で指定いるのは、ドラッグ＆ドロップの複写ができるようにするためである。＄を付けた絶対形式のセル番地は複写しても必ずその番地を指し、＄を付けない相対形式のセル番地は複写先に合わせた番地に変化することになる。

 

　=WEEKDAY(DATE($C$2,$C$3,B7))　　　　　−−− 数式Ａ

　　　　　　　　　　　　　↑　　　↑　　↑

　　　　　　　　　　　　　年　　　月　　日

 

　操作Ａで求められた結果は５なので、ＷＥＥＫＤＡＹ関数の［結果と曜日の対応表］から１９９９年４月１日は木曜だということが分かる。しかし、このままだと、曜日が数字で表示されるので分かりにくく、実用的ではない。そこで、以前に使ったＶＬＯＯＫＵＰ関数を応用して、曜日の番号から漢字の曜日を抽出して表示するようにする。この動作は品番から品名を抽出するのと同じだ。曜日の番号と漢字の曜日を一覧にしたテーブルを作成して（図Ｅ）、数式を次のように書き替えればよい。２行目にも数式を入力して、先ほどのコピー操作とどうように、２つのセルを選択して下へ続くセルに複写すればよい。表示される曜日は数式で求めているので、セルＣ２の年やセルＣ３の月を書き替えれば該当する曜日に自動的に変更させることができる。

 

数式Ｂ (セルＣ７の数式)

　=VLOOKUP(WEEKDAY(DATE($C$2,$C$3,B7)),$N$7:$O$13,2,FALSE)

　　　　　　　　　　　　　　　↑　　　　　　　　　　　　　　↑　　　　　　↑　　↑

　　　　　　　　　　　　　　抽出条件　　　　　　　　　　　　テーブル　　抽出列　完全一致

　　　 (セルＣ８の数式)

　=VLOOKUP(WEEKDAY(DATE($C$2,$C$3,B8)),$N$7:$O$13,2,FALSE)

 

＝ＤＡＴＥ（年，月，日）

　年、月、日、を数値で指定する

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

操作Ａ（数式Ａ）

�@セルＣ２に年数（１９９９）を入力する

�AセルＣ３に月数（４）を入力する

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

�C指定した日付（１９９９年４月１日）の曜日が番号で求められる

 

　EX4C447Z　　　　　EX4C448Z

 

 

 

●ドラッグ＆ドロップで曜日入力する

　連続するセル範囲に連番を入力するときのドラッグ＆ドロップの要領で、曜日も自動入力することができる。曜日だけでも可能だが、ここでは、番号（連番）も一緒にドラッグ＆ドロップして自動入力しよう。

 

�@先頭の番号「１」と曜日「日」をセルに入力する

�A両方のセルを選択状態にして、右下角をドラッグ＆ドロップする

�B連番と曜日が自動入力される

 

図Ｅ

　　

EX4C413Z　　　　　　　EX4C414Z　　　　　　　　　EX4C415Z

 

操作Ｂ（数式Ｂ）

�@セルＣ７の数式を数式Ｂに書き替える

�A指定した日付（１９９９年４月１日）の曜日が漢字で求められる

�BセルＣ８にも数式を入力する

�Cドラッグ＆ドロップで下へ続くセルに複写する

�D各日の曜日が表示される

 

　EX4C449Z

 

　EX4C450Z　　　　　EX4C451Z

 

 

 

 

 

●列の非表示と表示

　曜日を抽出するために使用するテーブルなどは、常時表示されている必要はない。目障りな場合は、列単位で列ごと隠してしまうことができる。以前に紹介した表示形式で消す方法だと、そのセルが空いているものだと勘違いして何かを入力してしまう危険性がある。列に他の入力が無いなら列ごと隠してしまうことで、セルを選択することすらできなくなるので間違えて消したり書き替えたりする危険性ない。また、簡単な操作で隠した列を元通りに表示することもできる。

 

［列を隠す］

�@隠す列の列ラベルをドラッグで選択する

�A黒い部分をマウスで右クリックしてショートカットメニューを開く

�B［表示しない］を選択する

�C選択した列が消える

 

　　

EX4C416Z　　　　　　　　　　　　　　　　　　EX4C417Z

 

［列を表示に戻す］

�@隠れいてる列の両側の列をドラッグで選択する

�A黒い部分をマウスで右クリックしてショートカットメニューを開く

�B［再表示］を選択する

�C隠れていた列が表示に戻る

 

 

　　

EX4C418Z　　　　　　　　　　　　　　EX4C419Z

 

 

 

［ 上 級 編 ］ドラッグ＆ドロップで自動入力する項目を追加する

　曜日などのドラッグ＆ドロップで自動入力される内容は、Excel本体のオプションのユーザー設定リストに登録されている。この登録内容に新しいリストを追加登録すれば、その内容もドラッグ＆ドロップで自動入力できるようになる。たとえば、「前期」をセルに入力してドラッグ＆ドロップの操作で、中期、後期、を自動入力したければ、ユーザー設定リストにそれを登録すればよい。

 

●新しいリストを登録する

登録操作

�@［ツール］→［オプション］を選択する

�A［オプション］ダイアログボックスが開く

�B［ユーザー設定リスト］タブを選択する

�C［新しいリスト］を選択する

�D自動入力する内容を入力する

�E［追加］をクリックする

�F［ＯＫ］をクリックして閉じる

 

　

EX4C427Z　　　　　　　　　　　　　　　　　　　EX4C420Z

 

動作確認

�@リストの１つ目を入力する

�Aセルの角をドラッグ＆ドロップする

�Bリストが自動入力される

 

 

　　

　EX4C421Z　　　　　　EX4C422Z

 

●シートに入力している内容で登録する

　シートに入力している内容を登録したければ、ユーザー設定リストのインポートを使って登録できる。たとえば曜日は、日、月、火、のように何曜日の曜日の付いていないスタイルと付いているスタイルがあらかじめ登録されているが、日曜、月曜、火曜、のように何曜日の日を省いたスタイルは登録されていなので、このリストを登録する場合、連続するセルに入力しておき、インポート操作を行う。

 

�@あらかじめ連続するセルに登録リストを入力しておく

�A［オプション］ダイアログボックスを開く

�B［ユーザー設定リスト］の［新しいリスト］を選択する

�Cセル参照ボタンをクリックする

�D範囲取り込みダイアログボックスが開く

�Eリストを入力しているセル範囲をマウスのドラッグ＆ドロップなどで選択する

�Fセル参照の完了ボタンをクリックする

�G［インポート］をクリックする

�Hリストが取り込まれて追加が完了する

�I［ＯＫ］をクリックして操作を終わる

 

　

EX4C423Z　　　　　　　　　　　　　　　　　　　　EX4C425Z

 

 

　EX4C424Z

 

●登録の削除

　登録したリストを削除するときは、削除するリストを選択して［削除］をクリックすればよい。

 

�@削除するリストを選択する

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

 

　EX4C426Z

年間の集計表を作る

関数を組み合わせて

曜日を自動表示させる