[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『累計の出し方』（あかいも）
いつもお世話になっております。
有識者の方、どうかご教示いただけますと幸いです。
現在、Excelで実績表を作成しています。
検索セルを用いて月を選択し、その月までの実績を足し合わせた結果を表示したいと考えております。
データベースとなるものはパワークエリで取り込みして、下記のようになっています。
※CD＝コード
Ａ B C D E F G H
年度 月 商品CD 商品名 得意先CD 得意先名 金額 数量
2020 4月 123456 アイス 987654 東京 20,000 200
このようなデータが約1万行あり、今後も増え続ける予定です。
このデータを今度は下記のように使用しています。
検索セル
・年度（C3）
・月（C4）
・表示内容（金額か数量を選択します。）(C5)
D E F G H
｜ ｜ 商品CD ｜→以降同様の内容
検索セルでは4月〜3月までを入力することができ、
5月と入力した場合には4月と5月の実績を足し合わせた金額を結果として表示させたいです。
単月のみを表示させているシートでは下記数式を使用しています。
=SUMIFS(IF($C$5="金額",実績[金額],実績[数量]),実績[年度],累計!$C$3,実績[得意先CD],累計!$D10,実績[月],累計!$C$4,実績[商品CD],累計!G$7)
数式を複雑化せずともパワークエリで累計を出せる等でも良いので、
ご教示いただけますと幸いです。
拙い分で申し訳ないのですが、よろしくお願い致します。
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
ピボットテーブルの利用を検討されたらいかがでしょう。 「計算の種類」に「累計」というのがあるので、それを使って年度始めからの累計を求めることができそうです。 ネット上で検索すると、きっと記事があると思われます。
（xyz） 2024/06/04(火) 17:43:01
累計については、 https://hamachan.info/win8/excel/pivot_ruikei.html 前年比とかの集計フィールドの利用については、 https://k-ohmori9616.hatenablog.com/entry/2019/09/10/111707 と言うのが参考になりそうです。研究してみて下さい。
されようとしていることは、結構普遍的なニーズがあるもののように見受けますので、 そのためにExcelが用意しているツールである「ピボットテーブル」機能を調べてみることは有益でしょう。
（xyz） 2024/06/05(水) 07:40:49
ご丁寧にリンクまでつけてくださりありがとうございます...!
ピボットテーブルとあまり向き合ってこなかったので、これを機にチャレンジしてみようと思います！
参考までに、数式で累計を出すこと自体は可能なのでしょうか？
（あかいも） 2024/06/05(水) 18:20:39
申し訳ないが、私にはわかりません。 他の方からのコメントをお待ちください。 （xyz） 2024/06/05(水) 20:57:05
いろいろとご教示いただきましてありがとうございました...！
まずはピボットテーブルで累計を抽出してみたいと思います！
（あかいも） 2024/06/06(木) 11:33:05
ピボットテーブルで累計の抽出にチャレンジしているのですが、「月」をフィルターに入れてみたところ、
5月を選択しても4月と5月の実績を足し合わせた金額は表示されませんでした...。
単月の実績が出てしまうのですが、改善方法等はございますでしょうか。
初歩的な質問で申し訳ないのですが、ご教示いただけますと幸いです。
よろしくお願い致します。
（あかいも） 2024/06/06(木) 13:59:54
＞データベースとなるものはパワークエリで取り込みして、下記のようになっています。 ＞Ａ B C D E F G H ＞年度 月 商品CD 商品名 得意先CD 得意先名 金額 数量 ＞2020 4月 123456 アイス 987654 東京 20,000 200
「年度」が本当に年度なのか、カレンダー年なのか分かりませんが、 下の形式の数値に変換して取り込めば、 Sumifs関数の複数条件(202004以上且つ指定年月以下)で累計算出ができると思うのですが。 Ａ B C D E F G カレンダー年月 商品CD 商品名 得意先CD 得意先名 金額 数量 202004 123456 アイス 987654 東京 20,000 200
（半平太） 2024/06/06(木) 15:39:55
| 単月の実績が出てしまうのですが、改善方法等はございますでしょうか。 ピボットテーブルに、行とか列とかΣ値とかを指定する時、 Σ値に金額を二回指定して、二回目のものには、「計算の種類」として「累計」を指定していますか？ 私が示したサイトの例にあがっていることをよく確認して、同じようにやってみてください。 その累計を指定した項目には年度始からの累計金額が表示されるはずです。
（xyz） 2024/06/06(木) 22:14:04
ご回答頂きましてありがとうございます。
煩わしい思いをさせてしまってすみません...
表示したい表について、補足させてください。
D、E列には7行目から得意先名とコードが120程入ります。
F列から右に商品が約40個入ります。
縦→得意先
横→商品
実績はパワークエリでグループ化を行い、各月の各商品の各得意先ごとに抽出されております。
検索セルで月を選択すると、その月までの累計だけが表示されるようにしたいと考えていたのですが、
ここはピボットテーブルのフィルターで選択したとしたら、ご参照いただいたやり方と求めていた表が異なってしまい、
理想の形で出すことが出来ませんでした...。
(私の知識不足です。申し訳ございません。)
半平太さん
数式での対応方法をご教示頂きましてありがとうございます！！
こちらも同時進行で使用してみたいと思います！
純粋な疑問なのですが、数式で結果表示させるよりピボットテーブルで結果表示させた方が処理速度は早いのでしょうか？
（あかいも） 2024/06/06(木) 22:47:37
| 検索セルで月を選択すると、その月までの累計だけが表示されるようにしたいと考えていたのですが、 | ここはピボットテーブルのフィルターで選択したとしたら、ご参照いただいたやり方と求めていた表が異なってしまい、
ピボットテーブルの「フィルター」で月を指定するということは、その月のデータだけを計算の対象にするということです。 累計値は元の全データから計算されるべき項目なので、元データが特定月のものに限定されれば累計値が正しくないのは当然です。 むしろ、すべての月を対象にして、累計値を含む表を出し、オートフィルタで特定月だけに絞り込むことになるでしょう。
| 純粋な疑問なのですが、数式で結果表示させるよりピボットテーブルで結果表示させた方が処理速度は早いのでしょうか？
ピボットテーブルは色々な切り口での分析表などを、簡単?な操作で作成することを目的にした道具と理解しています。 別に高速化を主目的にしたものではありませんし、 一方で、遅くて困るという話は余り聞いたことはありません。食わず嫌いの人が多い印象はあります（私見）。 なお、Power PivotというものもExcelから使えますので、うんざりかもしれませんが、紹介だけしておきます。
私は別にピボットテーブルを無理強いする積りもありませんし、利害関係は一切ありませんので、 どうぞ気兼ねなく自己判断で取捨選択して下さい。 それでは私はこれで。 （xyz） 2024/06/07(金) 06:33:28
この時の関数はどのようにしたら良いのでしょうか？
調べ方が悪いと思うのですが、条件の仕方がわかりませんでした。
大変お手数ではございますが、ご教示いただけますと幸いです。
よろしくお願い致します。
（あかいも） 2024/06/10(月) 21:09:13
＞ Ａ B C D E F G ＞ カレンダー年月 商品CD 商品名 得意先CD 得意先名 金額 数量 ＞ 202004 123456 アイス 987654 東京 20,000 200 ↑ こう言う形のテーブルで取り込めたんですね？
そうだとすると、以下の構想ですけども・・
＞=SUMIFS(IF($C$5="金額",実績[金額],実績[数量]),実績[年度],累計!$C$3,実績[得意先CD],累計!$D10,実績[月],累計!$C$4,実績[商品CD],累計!G$7)
↓
=SUMIFS(IF($C$5="金額",実績[金額],実績[数量]),実績[カレンダー年月],">="&C3&"04",実績[カレンダー年月],"<="&C3&TEXT(SUBSTITUTE(C4,"月",""),"00"),実績[得意先CD],累計!$D10,実績[商品CD],累計!G$7)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
（半平太） 2024/06/10(月) 23:38:23
↑ ミスりました。m(__)m
期間終期の方は、月が1〜3の場合、翌年になるようにしてください(つまり指定年に１プラス）。
（半平太） 2024/06/11(火) 08:35:12
早々にご回答ありがとうございます。
半平太さんのカレンダー年月で取り込めました!
下線を引いていただいた箇所について、検索セルを下記のようにした場合、
検索セル
・年度（C3）
・月（C4）
→C3とC4を組み合わせて、D3に202004と表示(IFS関数で1~3月の場合はプラス1にします)
→C3と"00"を組み合わせてD4に202000と表示
・表示内容（金額か数量を選択します。）(C5)
=SUMIFS(IF($C$5="金額",実績[金額],実績[数量]),実績[カレンダー年月],">="&D3",実績[カレンダー年月],"<="&D4",実績[得意先CD],累計!$D10,実績[商品CD],累計!G$7)
これでも大丈夫でしょうか？
（あかいも） 2024/06/11(火) 10:13:10
そちらで大丈夫と判断されたのであればいいと思います。
こちらは詳細が分からないので、再確認の為、以下記しておきます。 累計の始期は、yyyy04 (必ず4月スタート) 累計の終期は、yyyymm 指定月mmが01〜03の場合は、年は始期のyyyyより1大きくする 202000と言う形は、2020(前年度)の1月〜3月を含みますので 通常はマズいハズなのですが、その辺はそちらで判断してください。
（半平太） 2024/06/11(火) 11:14:46
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.