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

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

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

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

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

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

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

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

その際は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関数はとっても便利な関数です。
複雑な条件でも、このように少し手を加えればシンプルに計算できます。

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

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

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

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

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

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

答えは・・・

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

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

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

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

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

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

 

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

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

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

 

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

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

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

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

 

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

 

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

 

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

 

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

 

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

 

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

 

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

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

 

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

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

 

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

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

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

 

上記の内容でいくと

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

 

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

 

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

 

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

 

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

 

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

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

 

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

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

 

 

エクセルのデータ修正が一瞬で終わる簡単便利なフラッシュフィル

「フラッシュフィル」という機能をご存知ですか?

Excel2013で新たに追加された機能です。

 

もう何年も前から使えるのに、案外知らない方が多いんですよね。

1つのセルに
会社名と支店名が入力されている
商品名と容量が入力されている

数字だけで入力された電話番号の間に
「-」を追加したい
などなど・・・
Excelではちょっとデータを変えたいということがよくあります。

“エクセルのデータ修正が一瞬で終わる簡単便利なフラッシュフィル” の続きを読む

あなたが送ったファイルは無用の長物になってませんか?

毎月取引先から送信されるファイルに不要なシートが含まれている。

毎月別な部署から配布されるファイルは使いづらいので全く見ていない。

毎月別な部署から届くグラフ付の集計表は見づらく項目も足りないので部署内で作り直しているなどなど。

 

利用されないファイル、つまり無用の長物になってしまっているファイルが送受信されてる事があります。
以前は使ってたけど、今はいらないんだよね・・と言いつつ、そのままになってるケースも。

 

えぇー?と思うかもしれませんが、よくある話です。

 

“あなたが送ったファイルは無用の長物になってませんか?” の続きを読む

Excelのデータも計算式も合ってるのに計算結果がエラー!?

昨日はマンツーマン講習をやりました。

マンツーマン講習を受講される方はほとんどが資料持ち込みです。

持込資料の計算式を修正したり新たに作ったりと

資料を拝見しながらやりたい事をお聞きしその場で問題解決します。
固有の名称などはテストデータになっていてもその方が実際に会社で使っている資料です。

守秘義務の問題でファイルを送っていただくわけにいかないのでその場で初めて拝見します。

マンツーマン講習の到達点は今ある問題を解決し必要な計算式をご自身で作れるようになるです。

おかげさまで今まで到達しなかった方はいません。
昨日の講習内であった質問の中によくある質問があったのであなたにも共有しますね。

昔私が現場を預かっていた時よく直面し対応していた問題です。

データも計算式も合ってるのにエラーになる!?

“Excelのデータも計算式も合ってるのに計算結果がエラー!?” の続きを読む

Excelのピボットで金額と数量を集計するには?

Excelのピボットテーブルで複数項目を集計したい!

Excelのピボットテーブルってご存知ですか?

ピボットテーブルはなんだか難しそう・・・と敬遠されている方も多いのですが、実は誰でも簡単に使える機能です。

ピボットテーブルは大量データでも数分で集計できる便利な機能です。

Excelの計算式を使って集計表を作るより、ピボットテーブルを使ったほうが簡単で便利です。

集計項目を変えたりレイアウトを変えるのも一瞬で変更できます。

Excelのピボットテーブルで数量と金額など複数の項目を集計したい時はどうすればいいのでしょう?

値のところに数量と金額を配置すればいいだけ!

そうです。こんな感じです。

Excelのピボットで複数項目を集計し縦に並べたい!

もちろんこのように、数量と金額が横に並んでいる状態でもよいのですが、数量と金額を縦に並べたい時はどうしたらいいのでしょう?

Excelのピボットテーブルで集計する複数項目を縦に並べたい時は、次の図のように「列」に配置された「値」を「行」に移動します。

 

これで数量と金額を縦に並べることができます。

 

Excelのピボットテーブルは、集計作業が楽になるツールです。

しかも操作はとても簡単!

ぜひこの記事を参考にして使ってみてくださいね。

集合縦棒グラフと積み上げ縦棒グラフを一緒に作るには?

Excelで2種類の縦棒グラフを一つのグラフに作るには?

Excelでは集計表からワンクリックでグラフを作ることができます。

中でもよく利用されるのが縦棒グラフ。

ワンクリックで作れるシンプルなグラフなら簡単に作れますが、少し複雑なグラフとなると、なかなかうまくいきません。

Excel実務で作るグラフは、教科書のサンプルのようなシンプルなグラフより、少し複雑なグラフを作ることが多いですよね。

集合縦棒グラフと積み上げ縦棒グラフではなく
集合縦棒グラフと積み上げ縦棒グラフの組み合わせに見えるような積み上げ縦棒グラフを作ります。


積み上げ縦棒を2つ並べるには表の作り方に工夫が必要

今回の例に限らず、多くの場合、Excelでグラフを作る時は元になる表の作り方に工夫が必要です。

まず次のような表を作ります。

商品ごとの4月~6月の売上実績と、6月の売上見込です。

今回の場合、見込額があるのは6月だけですが、表は実績と見込とそれぞれ列を作ります。

実際に数字が入るのは6月だけです。

この表をもとに積み上げ縦棒グラフを作れば出来上がりです。

このように、既存のグラフに少し手を加え、わかりやすく仕上げると見やすいですよね。

集計表はピボットテーブルで時間短縮

グラフと集計表はセットです。

グラフを作るには、必ず集計表を作らなければいけません。

すばやくミスなく集計表を作るために、ピボットテーブルを使って時間短縮してくださいね。

上記のように明細表をもとに、ピボットテーブルで商品別月別売上実績の集計表を作ります。

これを利用すれば、グラフの元として利用した表も効率よく作れます。

さらに先ほどの通常の集計表に、ピボットテーブルの集計結果を連動できるような計算式を入れておけば、明細表のデータを入れ替えるだけで、使いまわしできます。

Excelで作業をするときは、正確に集計出来る事、短時間で仕上げる事、再利用できる事を、常に意識してくださいね。

あなたは手持ちのカードを何枚お持ちですか?

おけましておめでとうございます。

Excel業務改善コンサルタントの小野 眸です。

新年早々、Excelではなくトランプの話?? と思われたかもしれませんが・・
Excelの話です。

  • Excelで資料を作る時
  • Excelで計算式を考える時

どれを使うかは、自分が知っている機能・関数から選ぶことになります。
いやぁ ネット検索すれば、自分の知らない関数だって使えるよ!
という反論が聞こえてきそうですが・・・

“あなたは手持ちのカードを何枚お持ちですか?” の続きを読む

Excelで横へのオートフィルを使い4ステップでカレンダーを作成する方法

月 火 水・・・
第1週 第2週 第3週・・・
1月 2月 3月・・・

あなたはこんな連続したデータを手入力してませんか?
Excelでは、こんな連続したデータを入力すること多いですよね。

Excelには「オートフィル」と言って、上のような連続した内容を自動で入力してくれる優れた機能が備わっています。

知らなかった・・・ というあなたも心配はいりません。
簡単だからすぐに使えるようになりますよ。
できれば、実際にExcelを操作しながら読んでみてくださいね。
すぐに実感できますよ。

“Excelで横へのオートフィルを使い4ステップでカレンダーを作成する方法” の続きを読む

Excelの縦棒グラフでラベルを1つだけ表示したい時は?

Excelの集計表をわかりやすく見せたい時はグラフ!

使いますよね。

中でもよく使われるのが縦棒グラフです。

Excelでグラフを作るのはとても簡単!

集計表のなかを1か所クリックし、[挿入]タブ → [グラフ]の縦棒グラフから、集合縦棒」を選択すれば、上のようなグラフが作成されます。

Excelでグラフを作るのはワンクリックでできますから、あとは見やすく仕上げていくというわけです。

Excelでグラフを見やすく仕上げるコツは不要なものを非表示に!

“Excelの縦棒グラフでラベルを1つだけ表示したい時は?” の続きを読む