エクセル集計表で対前年実績と当期実績も集計するには?

エクセル集計表,対前年実績,当期実績

エクセルではいろんな集計表を作成します。

集計表ごとに計算式を考えて作らなければいけません。

 

どんな計算式にすればいいかわかる時はいいですが
わからなければネットで検索することになります。

 

ピンポイントで見つかればいいけど
なかなかそう うまくはいきませんよね。

 

ネット検索ってあっという間に時間が経ちますよね(・・;)

 

今日はメルマガ読者で受講者の方からいただいた質問への回答です。
エクセルの集計表で当月実績だけでなく期首からの累計も集計したいです。

 

その際はSUMIFS関数でいいですか?

具体的な計算式を教えてください。

 

 

わたしがよくやるのは四半期ごと、半期ごと、年間で列を設けます。

それを1期分用意します。

 

四半期:SUM関数で3か月分を合計
半期:SUM関数で前半の四半期と後半の四半期を合計
当期:SUM関数で上期と下期を合計
こんな感じです。

来月以降は空欄ですから
このような集計表ならシンプルな計算式でできますよね。

 

ただ集計行が増えるので、少し表がごちゃごちゃします。
たとえば上記のエクセル集計表のように
前月実績、当月実績、当期実績だけ必要という場合。
次のような考え方で作成するとよいでしょう。

当期実績もSUMIFS関数を使って集計

 

エクセルで集計表を作る時
計算式はできるだけシンプルな数式にすることをオススメします。

 

複雑な長い数式や
よく知られていないExcel関数を使った数式は
メンテナンスがやっかいです。

 

エクセルの達人しか修正できないという
属人化を招き、その資料はブラックボックス化してしまいます。

 

ということで

今回の質問では明細表をもとに
条件に合致した集計ができるSUMIFS関数を使います。

 

SUMIFS関数は複数条件を指定できる便利な関数です。

 

今回の場合、期間と店舗名が合致したデータを集計するという計算式になります。

 

エクセル 日付 年月 計算 関数

集計用に売上年月を計算しておくと便利!

明細データには2017年3月までのデータが入っています。

一般的に基準となる日付は上記のように
「年/月/日 時:分」や「年/月/日」となっています。

 

そのままでは集計の条件としてつかいにくいので
売上年月を別項目で追加しておきます。

売上年月:=YEAR(A2)*100+MONTH(A2)

 

これは数値データにしたい場合です。

もし文字データのほうがよければ
売上年月:=TEXT(A2,”yyyymm”)

こんな計算式になります。

 

あとはSUMIFS関数で集計します。

エクセル 売上年月 複数条件 集計 SUMIFS 関数

売上年月と店舗名を条件にして実績を集計

 

=SUMIFS(明細リスト!$L$2:$L$201,
明細リスト!$Q$2:$Q$201,$C3,明細リスト!$B$2:$B$201,D$2)

 

上記計算式の内容は以下の通りです。

 

合計対象範囲:明細リストの売上欄

条件範囲1:明細リストの売上年月

条件1:集計表の売上年月

条件範囲2:明細リストの店舗名

条件2:集計表の店舗名

 

今年実績欄も同じ要領で集計出来ますよね。

当期実績はSUMIFS関数の条件を変える!

 

当期実績の計算式は以下の通りです。

 

=SUMIFS(明細リスト!$L$2:$L$201,
明細リスト!$Q$2:$Q$201,”>=201604″,明細リスト!$B$2:$B$201,D$2)

 

上の計算式と条件1だけが違います。

 

条件1:201604以上

できれば計算式には固定の数値を使いたくないので
この条件も関数を使って求めれるようにしておいたほうがいいですね。

 

SUMIFS関数はとっても便利な関数です。
複雑な条件でも、このように少し手を加えればシンプルに計算できます。

 

参考になさってください!

 

Excel業務改善コンサルタント 小野 眸(おの ひとみ)。
20年間のシステムエンジニア経験から得た「最適な機能・関数を選択するコツ」と、コンサルタント経験から得た価値あるデータの見極め方を、15年以上の講師経験をもとにわかりやすく解説しています。

「Excel応用力 X データ活用力」の両方を兼ね備えた『ビジネスExcel力』が身に付き、ワンランク上の仕事ができる人材育成に貢献しています。

ブログではExcel業務の改善、Excelスキルアップに関する情報を発信しています。

ご相談やご質問がありましたら、Excel業務改善コンサルタントの小野 眸までご連絡ください。
E-mail:contact★pcgaku.com ★を@に打ち替えてお送りくださいね。

Twitterフォローをお願いします~
facebookのフォローをお願いします~

◆◇Excel実務に特化したExcel解説やスキルアップ情報をメルマガで配信中◇◆
ご購読は無料です。下記からお申し込みください。

ビジネスExcel力を高めExcel業務の効率を30%UP!

この記事が役に立ったと思ったらシェアしてね

エクセル計算式の入替を最も効率よく間違えなくできる方法

エクセル計算式をコピーし入れ替えるやり方

受講者でメルマガ読者の方からこんな質問がありました!

 

エクセルの集計表で
計算式が入力されている行の入替を簡単に行う事はできますか?

最も効率がよく間違えなくできる方法もご教授いただけないでしょうか。

 

答えは・・・

もちろん「はい!あります」

 

エクセルの計算式も入力データも行の入れ替えは簡単にできます!

事例)明細からSUMIFS関数で集計

たとえば上記のような表があるとします。

これは明細表をもとに、支店ごとの前年実績と今年実績を集計。

さらに前年比も計算しています。

 

今年実績には明細表をもとに売上年月と支店が合致した明細を集計してくれるSUMIFS関数が入力されています。

前年比には今年÷前年の計算式が入力されています。

「前年比」と「今年実績」の行を入れ替えたい

 

エクセルの計算式を一生懸命考えて作った集計表は
できるだけ変更したくないですよね。

 

特に前任者が作った表をそのまま使っている場合など
極力、触りたくないというのが本音だと思います。

 

しかし残念ながら
エクセルの資料に見直しはつきもの。

 

ある程度使ったら
ほとんど見直しや業務に合わせた修正が出てきます。

 

エクセルの行や列を入れ替える場合
中身がデータなら特に意識することなく
切り取ったらコピーしたりできると思います。

 

でも計算式だと
違う内容にならないか不安ですよね。

 

基本的にこの手順で大丈夫です!

 

移動したい箇所を切り取り

「切り取ったセルの挿入」で移動

 

計算式は正しく移動されます。

もし心配な時は次の手順で作業してください。

  1. 現在の集計表をコピーし空いてる箇所に値で貼っておく
  2. 移動したい箇所を切り取る
  3. 切り取ったセルの挿入で移動する
  4. 1の内容と照らし合わせ数値が変わっていないか検証
  5. 1で貼り付けた内容を消去

計算式を変更する時は
自分の意図した内容と違う内容にならないか心配です。

 

だから
事前にバックアップとして
シートやファイルをコピーしたりします。

 

大幅な変更の時はそれも大切ですが
今回のようにちょっとした変更で
バックアップを取るのは面倒ですよね。

 

そんな時は空いてるスペースにちょっとコピーしておきます。
この時普通にコピペしてはダメですよ。

 

コピペだと計算式がコピーされてしまうので
結果が違ってしまい確認用に使えません。

 

計算式に「$」が付いていたら要注意!やり方が違います。

 

計算式に「$」が付いているもの
見たことありますか?

 

これは計算式をコピーする時に
セル番地がずれないよう固定するために使います。

範囲指定がその代表例です。

 

計算式にこの「$」がついている時は注意が必要です。

やり方が違う場合があるからです。

 

計算式内のどのセルに「$」が付いているかがポイントです。

移動したセルを指定している計算式は要注意です。

移動後はセル番地が変わりますからね。

 

上記の内容でいくと

今年実績は明細表をもとに売上年月と支店が合致した
明細の集計する計算式が入っています。

 

前年比には今年÷前年の計算式が入力されています。

 

その中で「$」が付いているセル番地のうち
移動した行に関連のある個所を確認します。

 

移動後も正しいセル番地になっていればOKです。

 

もし正しく変わっていない場合は
セル番地を正しく修正してくださいね。

 

大量にある場合は「置換」機能を利用すると便利ですよ。

[ホーム]タブにある「置換」機能です。

 

計算式も置換の対象になります。

ぜひ活用してくださいね!

 

 

Excel業務改善コンサルタント 小野 眸(おの ひとみ)。
20年間のシステムエンジニア経験から得た「最適な機能・関数を選択するコツ」と、コンサルタント経験から得た価値あるデータの見極め方を、15年以上の講師経験をもとにわかりやすく解説しています。

「Excel応用力 X データ活用力」の両方を兼ね備えた『ビジネスExcel力』が身に付き、ワンランク上の仕事ができる人材育成に貢献しています。

ブログではExcel業務の改善、Excelスキルアップに関する情報を発信しています。

ご相談やご質問がありましたら、Excel業務改善コンサルタントの小野 眸までご連絡ください。
E-mail:contact★pcgaku.com ★を@に打ち替えてお送りくださいね。

Twitterフォローをお願いします~
facebookのフォローをお願いします~

◆◇Excel実務に特化したExcel解説やスキルアップ情報をメルマガで配信中◇◆
ご購読は無料です。下記からお申し込みください。

ビジネスExcel力を高めExcel業務の効率を30%UP!

この記事が役に立ったと思ったらシェアしてね