当記事では、Excel の関数を使用した串刺し計算を行う方法についてご説明します。
目次
1. 串刺し計算とは
2. 串刺し計算（3D 集計）できる関数
3. 同じファイル内にて串刺し計算をする方法
4. 複数のファイルで串刺し計算をする方法
1. 串刺し計算とは
串刺し計算とは、Excel の機能を使用した計算方法の一つで、「3D 計算」や「 3D 集計」、「3D 参照」などとも呼ばれていることから、一つのシートだけではなく、複数のシートに入力されたデータを「串刺し」するように重ねて集計することができる計算方法です。
主に小売店や飲食店などの年間の売上高を、関数を利用して計算を行う際に使用されています。
便利な計算方法ですが、計算したい数値のセルに位置を合わせないと串刺し計算が出来ないので、全てのシートを同じレイアウトにすることで、より力を発揮する計算方法です。
2. 串刺し計算（3D 集計）できる関数
串刺し計算（3D 集計）できる関数は以下の通りです。
◆SUM～個々の値、セル参照、セル範囲、またはこれらすべての組み合わせを加算できます。
◆AVERAGE～引数の平均 (算術平均) を返します。
◆AVERAGEA～引数リストに含まれる値の平均 (算術平均) を計算します。
◆COUNT～数値を含むセルの個数、および引数リストに含まれる数値の個数を数えます。
◆COUNTA～範囲に含まれる空白ではないセルの個数を返します。
◆HSTACK(Office 365)～配列を水平方向に順番に追加して、より大きな配列を返します。
◆MAX～一連の引数のうち、最大の数値を返します。
◆MAXA～一連の引数のうち、最大の値を返します。
◆MIN～一連の引数のうち、最小の数値を返します。
◆MINA～引数リストに含まれる最小の値を返します。
◆PRODUCT～引数として指定された数値を乗算し、プロダクトを返します。
◆STDEV～標本に基づいて標準偏差の推定値を計算します。
◆STDEVA～標本に基づいて標準偏差の推定値を計算します。
◆STDEVP～引数を母集団全体であると見なして、母集団の標準偏差を計算します。
◆STDEVPA～文字列や論理値を含む引数を母集団全体と見なして、母集団の標準偏差を計算します。
◆VAR～標本に基づいて母集団の分散の推定値 (不偏分散) を返します。
◆VARA～標本に基づいて母集団の分散の推定値 (不偏分散) を返します。
◆VARP～母集団全体に基づいて分散を計算します。
◆VARPA～母集団全体に基づいて分散を計算します。
◆VSTACK(Office 365)～配列を水平方向に順番に追加して、より大きな配列を返します。
※参照元：https://support.microsoft.com/ja-jp/office/複数のワークシートで同じセル範囲への 3-D 参照を作成する-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6
3. 同じファイル内にて串刺し計算をする方法
同じファイル内にて串刺し計算をする方法というのは、一つの Excelファイル内の別シートに対して串刺し計算を行うということです。
以下、イメージ図です。
(図.1) 同じファイル内の別シートに対して串刺し計算を行った際のイメージ図 (図.1)では、一つの Excelファイルを使用し、3枚の別シートに記載している 10 月～ 12 月の札幌支店の売上高を、SUM 関数を使用して 4 枚目の「第 3 四半期合計」のシートにその合計値を記載しています。
この方法の注意点は、同じセルの数値を計算するため、4 枚のシートを同じ表のレイアウトにする必要があります。
同じ表のレイアウトにすることのより、一つを串刺し計算をすることで、オートフィル機能により全てのセルを串刺し計算することができます。
初めに事前準備として以下、(図.2-1)～(図2-4)のように同じレイアウトの表を用意いたします。
(図.2-1)～(図2-3)には、すでに各月の売上高や営業利益などの数値を記入している状態で、その各月の合計数値を(図2-4)にSUM関数を使用し記入します。
(図.2-1) 10月 営業利益
(図.2-2) 11月 営業利益
(図.2-3) 12月 営業利益
(図.2-4) 第3四半期(10月～12月)
それでは以下、同じファイル内の別シートに対して、SUM関数を使用した串刺し計算を行う方法の手順です。
(図.3)
① 各シート（10月、11月、12月）の合計を入力したいシート（第３四半期合計）を選択します。(図.3参照)
② セルの " C3 " に「=SUM( 」と入力します。(図.3参照)
(図.4)
③［10月］のシートへ移動します。(図.4参照)
④ セルの " C3 " にアクティブセルを合わせます。(図.4参照)
(図.5)
⑤ キーボードの［SHIFT］キーを押下しながら、［12月］のシートを選択します。(図.5参照)
※キーボードの［SHIFT］キーを押下しながら、［12月］のシートを選択することにより、10月～12月のシートの " C3 " の数値が選択されている状態になります。(図.5参照)
(図.6)
⑥［ENTER］を押下することにより、札幌支店の10月から12月の売上高の合計が " C3 " に表示されます。(図.6参照)
(図.7)
⑦ " C3 " に札幌支店の10月から12月の売上高が表示されたので、" D3（東京支店）" 、" E3（横浜支店）"、" F3（計）" も " C3 " と同じようにオートフィル機能を使用し、10月から12月までの売上高を表示させます。(図.7参照)
⑧オートフィル機能の「書式なしコピー」にチェックを入れます。(図.7参照)
※「書式なしコピー」にチェックをしない場合、図の罫線が消えてしまいます。
(図.8)
⑨ オートフィル機能を使用し、全てのセルに串刺し関数を反映させるため、初めに" C3 " ～ " F3 " をドラッグ（範囲を指定）します。(図.8参照)
⑩ " C3 " ～ " F3 " をドラッグしたまま、" F3 " ～ " F7 " に対しオートフィル機能を使用します。(図.8参照)
※ " C3 " ～ " F3 " をドラッグしたまま、" F3 " ～ " F7 " に対しオートフィル機能を使用することにより、" C3 " ～ " F7 " のセルに対し串刺し関数を反映させることができます。
⑪ オートフィル機能の「書式なしコピー」にチェックを入れます。(図.8参照)
上記作業を行うことにより、" C3 " ～ " F7 " の全てのセルに対し串刺し関数を反映させたことになり、10月から12月の数値の合計が表示されます。
以上
4. 複数のファイルで串刺し計算をする方法
先ほどは、一つのファイル内での串刺し計算でしたが、今回は複数のファイルを一つのファイルにまとめるための串刺し計算の方法をご説明いたします。
この方法を覚えると、異なるファイルで計算したものを一つにまとめることができるため、飲食店など店舗数が多い企業のデータを、一つのファイルにまとめる時などにとても有効な計算方法と言えます。
では初めに、事前準備として以下(図.1)のように、「全支店」「東京支店」「横浜支店」「札幌支店」のように複数のファイルを用意します。
また、複数のファイル内のレイアウトも同じにします。(図.2-1)～(図.2-4)参照
(図.1)
(図.2-1) 全支店
(図.2-3) 横浜支店
(図.2-2) 東京支店
(図.2-4) 札幌支店
それでは以下、複数のファイルで串刺し計算を行う方法の手順です。
(図.3)
① 関連するファイル（「全支店」「東京支店」「横浜支店」「札幌支店」）を全て開きます。(図.3参照)
(図.4)
②「全支店」のファイルを選択します。(図.4参照)
③ セルの " B4 " に「=SUM( 」と入力します。(図.4参照)
(図.5)
④「東京支店」のファイルを選択します。(図.5参照)
⑤ セルの " B4 " を選択（アクティブセルを合わせること）します。その際、数式バーに自動的に「'[東京支店.xlsx]2024年'!$B$4」と絶対参照の関数が反映されるますので、「'[東京支店.xlsx]2024年'!B4」と相対参照の関数に手動で変更します。絶対参照から相対参照に変更を行わないと、最後にオートフィル機能を利用して全てのセルに対し、自動でデータを反映することができなくなるので変更しましょう。(図.5参照)
⑥ 数式バーに表示されている「'[東京支店.xlsx]2024年'!B4」の後ろに「+」を入力します。(図.5参照)
(図.6)
⑦「横浜支店」のファイルを選択します。(図.6参照)
⑧ セルの " B4 " を選択（アクティブセルを合わせること）します。その際、数式バーに自動的に「'[横浜支店.xlsx]2024年'!$B$4」と絶対参照の関数が反映されるますので、「'[横浜支店.xlsx]2024年'!B4」と相対参照の関数に手動で変更します。(図.6参照)
⑨ 数式バーに表示されている「'[横浜支店.xlsx]2024年'!B4」の後ろに「+」を入力します。(図.6参照)
(図.7)
⑩「札幌支店」のファイルを選択します。(図.7参照)
⑪ セルの " B4 " を選択（アクティブセルを合わせること）します。その際、数式バーに自動的に「'[札幌支店.xlsx]2024年'!$B$4」と絶対参照の関数が反映されるますので、「'[札幌支店.xlsx]2024年'!B4」と相対参照の関数に手動で変更します。(図.7参照)
⑫［ENTER］を押下します。
(図.8)
⑬ 全支店のセル " B4 " に、「東京支店」と「横浜支店」と「札幌支店」のセル " B4 " の合計のデータが反映されます。(図.8参照)
※=SUM('[東京支店.xlsx]2024年'!B4+'[横浜支店.xlsx]2024年'!B4+'[札幌支店.xlsx]2024年'!B4)
(図.9)
⑭ 全支店ファイルの" B4 " に「東京支店」、「横浜支店」、「札幌支店」の1月の合計売上高が表示されたので、オートフィル機能を使用し、2月から12月までの売上高を表示させます。(図.9参照)
⑮オートフィル機能の「書式なしコピー」にチェックを入れます。(図.9参照)
※「書式なしコピー」にチェックをしない場合、図の罫線が消えてしまいます。
(図.10)
⑯ オートフィル機能を使用し、全てのセルに串刺し関数を反映させるため、初めに" B4 " ～ " N4 " をドラッグ（範囲を指定）します。(図.10参照)
⑰ " B4 " ～ " N4 " をドラッグしたまま、" N4 " ～ " N8 " に対しオートフィル機能を使用します。(図.10参照)
※ " B4 " ～ " N4 " をドラッグしたまま、" N4 " ～ " N8 " に対しオートフィル機能を使用することにより、" B4 " ～ " N8 " のセルに対し串刺し関数を反映させることができます。
⑱ オートフィル機能の「書式なしコピー」にチェックを入れます。(図.10参照)
上記作業を行うことにより、" B4 " ～ " N8 " の全てのセルに対し串刺し関数を反映させたことになり、全支店の1月から12月の合計の数値が表示されます。
以上
本記事では、関数を使用した串刺し計算の方法を紹介しました。
上記の例でいえば、ネットワークさえ繋がっていれば、遠く離れた支店の数値を更新すると、全支店のファイルを開いてなくても、支店の数値が反映されるのでとても便利です。
また、名簿や家計簿など様々なことを管理するのにも応用でき、使いこなすと作業の効率化が図れる素晴らしい機能の一つです。