エクセル複数シートの活用

1　ワークシートの操作方法

1-1　シートの選択

エクセルで新しいブックを開くと、「Sheet1」、「Sheet2」、「Sheet3」の３つのワークシートが用意されています。どのように複数のシートを使っても良いのですが、同じブック内にあるワークシートを上手に使うことで随分便利になります。

　この説明のために、My Free-style PCオリジナルのサンプルとして下の「簡単家計簿」を用意しました。なお以降、説明ではワークシートのことを、元がSheetですから単にシートと略して記します。



　この表では、「Sheet1」、「Sheet2」、「Sheet3」の標準のシートから、「今月」、「前月」、「集計表」、「区分」、「品目」、「品目印刷用」、「元帳様式」とシートの名前を変えて、7つのシートに数を増やしています。

シートの選択は、シート名のタブをクリックするだけと簡単です。

　上の表で例えば、「集計表」の見出しタブをクリックすると、右の表のように集計表のシートに切り替えることができます。

　エクセルでブックを保存し、そのブックのファイルを再度開くと、保存したときに表示されているシートが開いた状態で表示されます。

　そのため、複数シートを使っている場合は、頻繁にシートの切り替えを行うことになります。

　なお、エクセルの画面表示サイズにもよりますが、シート数が多いと、見出しタブが隠れて選べないことがあります。

　その場合、左下端の横▲矢印をクリックすると、隠れている見出しタブが表示され、作業したいシート名のタブを選ぶことができます。

1-2　シートの名前の変更・挿入・削除

「Sheet1」というシートの名前を、そのまま使っても良いのですが、名前を変えた方が、そのシートがどういう表なのか解り易いでしょう。

左の画像のように、シートの見出しタブを右クリックし、名前の変更は、表示されるメニューの「名前の変更」をクリックし行います。

　この後、Sheet1の文字が反転した状態から任意の名前に置き換えることができます。

　左の画像では、「Sheet1」の名前を変更しようとしていますが、「Sheet2」、「Sheet3」もそれぞれのシート名のタブを右クリックして、同じように名前を変更することができます。

　表示される右クリックメニューの一番上の「挿入」をクリックすると、挿入のダイアログボックスが表示され、「ワークシート」が選択された状態で「OK」をクリックすると新しいシート、この場合は「Sheet4」が追加されます。

　また表示される右クリックメニューの「削除」をクリックすると、削除してよいか注意が表示され「OK」をクリックすると、そのシートを削除できます。

　「削除」する場合は、削除したいシート名のタブを右クリックして行いますが、注意が表示されるように元に戻すことができないため、間違えて残しておきたいシート名のタブから右クリックして削除しようとしていないかどうか確認してください。

1-3　シートのコピー・移動

シートの見出しタブを右クリックし、表示される上の画面のメニューには、「移動またはコピー」というメニューがあります。

この「移動またはコピー」をクリックすると、左の画面の小さなダイアログボックスが表示されます。

　この画面で、下段の「コピーを作成する」のチェックボックスにチェックを入れて「OK」をクリックすると、選択したシートと同じ内容のコピーしたシートが追加されます。

　この場合「Sheet1」の見出しを右クリックして、コピーをしようとしていますから、結果は「Sheet1（2）」というシートが先頭に追加されます。

　シートの移動は、中段の挿入先の枠内で、指定した先に移動することができます。

　この場合「Sheet1」の見出しを右クリックして、移動をしようとしていますから、中段の挿入先の枠内「Sheet3」を選択すると、　「Sheet1」、「Sheet2」、「Sheet3」の順であったものが、「Sheet2」、「Sheet1」、「Sheet3」の順に並び替えられます。つまり「Sheet1」が「Sheet3」の直前に移動したということです。

　もちろん（末尾へ移動）を選択すると、「Sheet2」、「Sheet3」、「Sheet1」という順、つまり「Sheet1」が末尾に移動します。

　実は、シートの移動は、こんな面倒なことをしなくても可能です。
　単純に、移動したいシート名のタブを左クリックし、そのままドラッグ＆ドロップで好きな位置に持って行けば順番を変えることができます。

　むしろ、コピーの作成の際に、コピーシートを末尾に追加したいのであれば、中段の挿入先の枠内で（末尾へ移動）を選択し、「コピーを作成する」にチェックを入れて「OK」をクリックすると、先頭ではなく末尾に追加できるという使い方が便利でしょう。

＜スポンサーリンク＞

2　複数シートの基本的な使い方

2-1　試しに複数の案を作成

エクセルで表を作成していて、途中でどうしようか迷うこともあるでしょう。
　一般的にパソコンで作業をしているときに、そのまま作業を続けると後から手戻りになりそうな場合は、その時点で、別名でファイルを保存することが普通ですが、エクセルの場合は、同じブック内にシートをコピーすると便利です。

　表の作成途中では、シートをコピーし、案１、案２、案３とシート名を付けておくと、後から前のアイデアの方が良かった思ったときに助かります。



　上の表では、案1と案2は、縦横を入れ替えています。
　縦横を入れ替えたい場合は、シートのコピーではなく、別シートに表のセル範囲をコピーする方が簡単です。

上の例では、A1〜F6セルを一括選択して、右クリックし、コピーをクリックします。

　次に貼り付ける別のシートに移動し、貼り付けたい位置のセル（この場合はA1セル）で右クリックし、「形式を選択して貼り付け」をクリックします。

　右の画像の「形式を選択して貼り付け」のダイアログボックスで、下段右側の「行列を入れ替える」のチェックボックスにチェックして「OK」をクリックすれば、縦横が変換されて貼り付けることができます。

　つまり、この場合は手順としては、「Sheet1」から縦横を入れ替えて「Sheet2」に貼り付け、その後で「Sheet1」を「案1」に、「Sheet2」を「案2」に名前を変更しています。

　もちろん、作成途中で単純なコピーを案として残しておくことも便利な使い方ですし、また、表がほぼ完成してからシートをコピーをして、セルの配色、文字サイズや罫線を変更したりして複数のシートを作ることもよくあることです。

2-2　複数のグループ分のシートを作成

同種の複数のデータ郡を入力するために複数のシートに分けたり、関連する異なる種類のシートを設けたりすることもよくあることです。



　上の表では、1か月分の表のシートが完成してから、4月〜3月の1年分の月別シートをコピーして増やしています。なおコピーしてから月名を変更しています。

　また、一つのブックに種類の異なる関連する表を別シートで作成することも多く、サンプルの簡単家計簿でも、文字入力を避けるために「区分」や「品目」を別シートにしたり、「集計表」も別シートにしています。

　この簡単家計簿では、「区分」や「品目」は他のシートに必要なシートとなっており、また「集計表」のシートも、入力が不要で自動的に計算され、参照関係のあるシート郡を含めてブックを構成しています。

　異なるシート間でのセル参照については後述しますが、もちろん、直接参照関係に無いシートでも、同じブック内のシートとして保存しておくと便利なことも多いでしょう。

　書籍でも、長編小説もあれば、短編小説が複数収録されている本もあるようなもので、ビギナーの方は好きなように、使い易いように複数シートを使ってみてください。

2-3　印刷用シートの作成

簡単家計簿では、収入項目と支出項目を、区分番号と品目番号、つまり数字を入力することで文字入力を避け、簡単に記帳できるようにしています。

そのため数字を入力するときに見る「品目印刷用」の右のシートを用意しています。

　印刷用シートの場合、用紙サイズを想定して表を作る必要があります。

　この「区分・品目リスト」も、A4サイズ縦長で印刷するための表としていますが、右の画像上は大きくなりすぎるので上部のみ載せています。

　表中の品目欄は、番号のみで空欄となっている欄がありますが、後から品目を追加できるように空番号を用意しておくこともテクニックとして使われることです。

　もちろん、元の「品目」のシートにも空番号が用意されています。

　このシートは入力時に見るためですが、印刷用シートでは、集計結果を見やすい表の形にして印刷用に用意するケースが多いでしょう。また、結果をグラフとして作成し、別のシートに描くこともあるでしょう。

＜スポンサーリンク＞

3　複数シートの高度な活用法

3-1　リストを別シートに作成

同じ列に同種のデータを並べた整然とした表が「リスト」であり、実はリストはそんなに特殊なものでもなく、むしろ原始的で単純なものですが、リストは単純なゆえにデータベース的な使い方ができ便利な面があります。

　リストの便利な使い方については、My Free-style PCサイト内の「エクセル簡単テクニック 後編」でリストの並べ替えを、「エクセルのオート機能　後編」でリストの特性とオートフィルタの使い方を説明していますが、リストそのものの便利な使い方だけではなく、基本的にはリストを別シートに作成し、このリストからデータを参照して他のシートで使い易いという大きなメリットがあります。

　

　簡単家計簿の各月の入力シートは、合計値や残高を計算していること、VLOOKUP関数を使用しており、単純な入力用のシートではないのですが、上の画像のとおり、リストとしてオートフィルタも使えます。

　この各月の入力シートのデータを参照し、別シートの「集計表」を作成していますが、このようにリストや元帳のデータをベースに、別シートに表を作成することは常用の手段です。

3-2　集計表や総括表の作成

集計表や総括表は、ほぼ元表が完成してから、元表のセルのデータを参照するようにして別シートに作成します。

簡単家計簿では、「今月」と「前月」のデータを使って、「集計表」を作成しています。

　別シートのセルを参照することは、難しいことではなく、参照セルの引数を指定するときに、別シートを開いて、参照セルの範囲を指定すれば良いのです。

　数式の参照範囲は、例えばSheet1のb1〜b12を参照する場合、同じシートから参照する場合の引数は（b1:b12）となりますが、別のシートから参照する場合は、（Sheet1!b1:b12）となります。

　簡単家計簿の「集計表」のC列とD列の数式では「今月」のシートを、F列の数式では「前月」のシートを参照している数式があり、各列の主な数式は次の表のとおりです。

C列のC4セルでは、「今月」シートのB列のB3〜B122のセルを参照しており、参照範囲の引数に「今月!」が加わり、（今月!B$3:B$122,0）を参照しています。

　この数式では、今月の収入の件数をカウントしており、COUNTIF関数を使い「区分」の「NO」が「0」、つまり「今月」シートのB列のB3〜B122のセルのうち値が「0」の収入の件数のみカウントしています。

　C列のC5セルでは、(今月!B$3:B$122,1)を参照しており、同じ「今月」シートのB列のB3〜B122のセルのうち値が「1」の食費の件数のみカウントしています。

　なお、COUNTIF関数の書式は、COUNTIF(範囲, 検索条件)であり、この場合は検索条件は数値を指定しており、参照範囲内の同じ数値の数を数えています。

　また数式中の前半、=IF(COUNTIF(今月!B$3:B$122,1)=0,"",COUNTIF(今月!B$3:B$122,1))の赤字の部分のIF関数は、カウントした件数が1件も無いときにブランクのままにしておくために用いています。

　D列のD4セルでは、SUMIF関数を使って、「今月」シートのB列のB3〜B122のセルのうち値が「0」の収入の合計額を計算しています。

　なお、SUMIF関数の書式は、SUMIF(範囲, 検索条件, 合計範囲)であり、この場合は合計範囲に「今月」シートのF列を指定しており、範囲B3〜B122内の検索条件を満たすセルに対応するF列のセルの値が合計されます。

　IF関数、COUNTIF関数、SUMIF関数の使い方について、詳しくは、「My Free-style PC」サイト内の「エクセルIF関数を使おう」を参照してください。

　E列の数式は、費目別の支払額を収入額で割っているだけと簡単ですが、E5セルの数式の分母はD$4と「$」を付して行を固定する複合参照にしています。これはE5の数式をE6〜E13セルにコピーして使うときに便利なためであり、複合参照を使うと便利な典型的な例です。

　F列のF5セルの数式は、「前月」シートのB列のB3〜B122のセルを参照しており、参照範囲の引数に「前月!」が加わり、（前月!B$3:B$122,0）を参照しています。この数式では、「前月」シートのB列のB3〜B122のセルのうち値が「0」の収入の合計額を計算しており、D列の数式と異なるのは「前月」シートを参照していることのみです。

　G列の数式は、単純に今月の金額を前月で割って、前月比を計算しています。数式は簡単ですが、セルの書式設定で「％」で表示し、収入と残高は前月より少なければ赤字で表示、支出項目は前月よりオーバーすれば赤字で表示するように書式設定は少し凝っています。セルの書式設定について詳しくは、「エクセル簡単テクニック（前編）」で説明しています。

　この簡単家計簿では、COUNTIF関数等を使っており、少し難しいかもしれませんが、別シートの元表を単純に参照することは、難しいことではないので使って慣れてください。

3-3　VLOOKUP関数の利用

表のデータを検索するための関数として、LOOKUP関数、VLOOKUP関数、HLOOKUP関数があり、その書式は次のとおりです。

　　LOOKUP（検索値,検索範囲,対応範囲）　または　LOOKUP（検索値,配列）
　　VLOOKUP（検索値,範囲,列番号,検索の型）
　　HLOOKUP（検索値,範囲,行番号,検索の型）

　この3つの関数は、同種の関数で似たような書式ですが、取り敢えず最も実用的で解りやすいVLOOKUP関数を初めにマスターすると良いでしょう。

　このVLOOKUP関数では、同じシート内の検索範囲を指定して使うこともできますが、別シートのリストやVLOOKUP関数の検索専用の別シートを用意することも使いやすくて一般的です。

　

この簡単家計簿の「区分」と「品目」のシートは、VLOOKUP関数の検索のための専用シートです。

　右の画像が「区分」のシートであり、A列に区分のNO（番号）が、B列に区分の名前が入力されているだけの単純な表です。

　「品目」のシートも画像は省略しますが、「区分」のシートと同じように、A列にNOとB列に品目の名前が入力されています。

　上の「今月」のシートの区分のNO（B3セル）に、「0」と入力すると、その横の区分の欄（C3セル）に「収入」と自動的に表示されます。

　これは、「今月」のシートのC列のセルに、VLOOKUP関数が使われ、「区分」のシートから参照しているためであり、数式は次のとおりです。

「今月」のシートのC3セルのVLOOKUP関数の数式は、左隣のB3セルの値を検索値として、「区分」シートの範囲（A2〜B11）の左端の列（この場合A列のA2〜A11セル）を対象として検索し、検索値と一致する値を調べ、その値の対応する列番号「2」（この場合B列）の値を表示します。

　VLOOKUP（検索値,範囲,列番号,検索の型）ですが、この場合「今月」のシートのB3セルの値は「0」であり、「区分」シートの範囲の左端のA列のA2〜A11セルを調べ、範囲内の1行目のA2セルの値が「0」であるので一致し、この場合列番号は「2」なのでB列の範囲内の1行目のセル値である「収入」を表示しています。

　VLOOKUP関数の書式は少し難しいのですが、難しい原因は、範囲に見出しを含めないこと、範囲は検索値を探す列だけでなく結果を返す列も含むこと、という範囲の指定の問題と、列番号は普通にA列、B列、C列・・・と記述するのでなく、列番号には範囲内の左端から数えた順番の「1」、「2」、「3」・・・と番号を記述しなければならないことです。

　また検索の型は、この場合「FALSE」を指定していますから検索値に完全に一致するもののみ探していますが、「TRUE」を指定すると、検索値以下の最も大きな値を探します。

　E列のE3セルも、同じように「品目」のシートを範囲として、VLOOKUP関数で検索しています。

　そして、VLOOKUP関数を使うことによって、簡単家計簿では、各月のシートでは一々教養娯楽費とか放送受信料とか文字を入力しなくても、NO（番号）のみ入力すれば記帳できるように簡単にしてあります。なお、さらに細品目を入力したい場合のために、右端のメモ欄を用意していますが、「品目」シートをカスタマイズしたり、品名を増やした方が、毎日記帳するときは楽でしょう。

　またA列の日付欄も、7月1日と入力しなくても、「7/1」と入力すれば「7月1日」と表示されるように書式設定しており、数量や細かい品名を記帳しなければ、日本語変換は不要で半角英数や直接入力で記帳できることがメリットです。

　この簡単家計簿では、VLOOKUP関数で参照するために別のシートを用意していますが、既存のリストを参照する使い方も多いでしょう。また、「エクセル簡単テクニック（後編）」でVLOOKUP関数の代わりに入力規則のプルダウンリストを使い入力の手間を省く方法を説明しており、プルダウンリストは選択項目が少ないときは扱いやすいので参考としてください。

3-4　OFFSET関数の利用

簡単家計簿では、月単位に一つのシートを用意し、使い方としては、月が変わればシートをコピーして増やし、シート名を変更して一年間使うことを想定しています。

　初めから1年間分の月別のシートを用意しておいても良いのですが、例えば、6月が過ぎれば、従前の「前月」のシートをコピーしてシート名を「5月」に変更、従前の「今月」のシートのデータを「前月」にコピーし、「今月」のシートの入力データをクリアして使うという方法で、使っていくうちに一年分のシートが揃います。

　また、記帳欄が不足する場合は、行の挿入で増やすことができます。

　あらかじめ必要な行数が決まっていない表では、使っていて行を増やさなければならないことが多いのですが、もちろん数式が使われている行を追加するときは数式のコピーが必要であり、その際に数式の参照範囲には注意が必要です。

　一般的に、コピーしたときに数式の参照範囲がおかしくならないようにするためには、絶対参照と複合参照を上手に使うことですが、実は、この簡単家計簿のように残高の計算の数式は、行を追加したり削除すると以降のセルも修正が必要となり少し面倒です。

　行を追加したり削除したりしても修正しやすくする、つまりタフな表にするために、残高欄の数式は次のようにOFFSET関数を使って工夫しています。

エクセルでは、普通に「H4」と記述すれば相対参照、「$H$4」と[$」を付けて記述すれば絶対参照と説明されており、この「My Free-style PC」サイト内の他のファイルでも、そのように説明しています。

　しかし、この相対参照という意味は、例えば「H4」セルを参照先とする数式を他のセルにコピーしたときに、数式の入力されているセルと「H4」セルの位置関係によって自動的に数式中の参照先のセルが変更されるということであり、現在の選択されているセルの位置から、例えば、一つ上とか、2つ左のセルとか相対的な位置を示しているわけではありません。

　そしてエクセルには、基準のセルとの相対的な位置関係を示す関数として、OFFSET関数があり、基準のセルから指定した行数と列数だけシフトした位置にあるセルまたはセル範囲の参照を返します。

　実は残高の計算は、前の残高から、加えたり引いたりして計算するものであり、この簡単家計簿のように順に記帳する表では、前の残高は数式を入力しようとするセルの一つ上のセルにあります。

　つまり上のOFFSET関数を使った数式は、数式を入力しようとするH4セルを基準として、一つ上のセル（H3セル）の値に、現在のセルの2つ左隣のセル（F4セル）の値を加え、一つ左隣のセル（G4セル）の値を引くという数式になっています。

OFFSET関数の書式は、次の書式ですが、少し難しいので、右の画像で説明します。

OFFSET(基準, 行数, 列数, 高さ, 幅)

　この書式の基準は、任意のセルで良いのですが、上の式では、数式を入力しようとして選択された現在のセル（H4セル）を基準としています。

　行数は、基準のセルを「0」として、一つ上のセルの行数は「-1」、一つ下のセルの行数は「1」となります。

　さらに、上に離れたセルの行数は「-2」、「-3」・・・、下に離れたセルの行数は「2」、「3」・・・と、上の画像のようにシフトした行数分増減します。

　列数は、基準のセルを「0」として、一つ左のセルの列数は「-1」、一つ右のセルの列数は「1」となります。さらに、左に離れたセルの列数は「-2」、「-3」・・・、右に離れたセルの列数は「2」、「3」・・・と、上の画像のようにシフトした列数分増減します。

　高さと幅は、複数のセル範囲を参照するためにあり、高さ「3」はその行も含めて下に3行、幅「-2」は左に2列というように、行または列の数を指定します。なお上の式のように、単独のセルを参照する場合は、高さは「1」、幅も「1」です。

　例えば、H4セルを基準として、OFFSET(H4,0,-1,-3,1)と高さを「-3」と指定すれば、参照範囲はG4セルも含めてマイナス3行分、つまり上の画像の青色の部分(G2:G4)となり、SUM関数にSUM(OFFSET(H4,0,-1,-3,1))と組み込めば、SUM(G2:G4)と同じ結果が求められます。

　エクセルの表では、左上方面のセルを参照することが多く、OFFSET関数で指定する行数、列数、高さ、幅はマイナスの数値を指定することが多いでしょう。

　簡単家計簿のH4セルの残高を計算する数式は、普通に「＝IF(AND($F4="",$G4=""),"",$H3+$F4-$G4)」と記述しても結果は同じです。

　ただし、記帳忘れがあり一行挿入したり、逆にダブって記帳して一行削除したい場合は、挿入または削除した行以降のセルの数式を全て修正する必要があります。修正は数式のみコピーをかければ良いのですが、それでも時々あると忘れてしまうこともあります。

　OFFSET関数を使っても、追加した行には数式の入力が必要ですが、追加した行のみ数式を入力すれば良く、行を削除しても修正が不要です。

　200件の記帳件数のシートまで用意しましたが、それでも行が不足するということが当然考えられるため、OFFSET関数を使って数式をタフにしてておくと良いでしょう。


＜他のシートを参照する数式の注意＞

　エクセルでは、他のシートを参照する数式では、参照先のシートがみつからないと「#REF!」エラーとなります。

　この簡単家計簿でも、「今月」と「前月」のシート名を削除すると、「集計表」ではエラーとなるため注意が必要であり、「今月」と「前月」のシートは残し、データを移した方が良いでしょう。

　またエクセルでは、数式をコピーすると「#REF!」エラーとなることもありますが、こうしたトラブルは参照シートや参照セルの範囲を自動的に変更してしまうことが原因であり、勝手なことをしては困ると言いたくもなります。

　こうしたトラブルのときは、一旦数式をメモ帳にコピーすることで、コピー元と関わり無い文字として扱うことができ、メモ帳からコピーしたいシートのセルの数式バーにコピーすることで、数式をそのままコピーすることができます。

　この「エクセル複数シートの活用」のサンプルの表は、この「簡単家計簿」をクリックし、ファイルメニューの「Microsoft Excel for Windowsで編集」をクリックすればエクセルのファイルとして利用できます。この簡単な使い方の説明は、「集計表」シートに記載しています。

　最終的には１か月で200件のデータを記帳できるように改良していますが、行が不足すれば追加し、また、品目を変更したり追加したり、自分に合うように改良することで実用的な表になり、また学習できますので使ってみてください。

　なお、改良した後は、名前を付けて保存するときに、ファイルの種類を「Microsoft Excel ブック」として保存すれば、以降はエクセルのファイルとして使えます。ファイルの無断転載はお断りしますが、私的利用であればご自由にお使いください。

★　My Free-style PC サイト内関連ファイルのご案内