セールをやろうという時に、通常価格の何パーセント引きみたいに、割引率をかけて金額を決めますよね。
たとえば・・
通常価格が9,600円の商品があって、値引率を78%にした場合
9,600円Ｘ78%＝7,488円
です。
まぁ このままでもいいんですけど・・
100円以下の細かい数字を丸めて、キリのいい数字にしたい時ありませんか？
7,488円じゃなく7,500円にしたいみたいな・・。
こんな時どうしたらいいと思います？
エクセル集計表で対前年実績と当期実績も集計するには？
エクセルではいろんな集計表を作成します。
集計表ごとに計算式を考えて作らなければいけません。
どんな計算式にすればいいかわかる時はいいですが
わからなければネットで検索することになります。
ピンポイントで見つかればいいけど
なかなかそう うまくはいきませんよね。
ネット検索ってあっという間に時間が経ちますよね(・・;)
今日はメルマガ読者で受講者の方からいただいた質問への回答です。
エクセルの集計表で当月実績だけでなく期首からの累計も集計したいです。
その際はSUMIFS関数でいいですか？
具体的な計算式を教えてください。
わたしがよくやるのは四半期ごと、半期ごと、年間で列を設けます。
それを1期分用意します。 四半期：SUM関数で3か月分を合計 半期：SUM関数で前半の四半期と後半の四半期を合計 当期：SUM関数で上期と下期を合計 こんな感じです。
来月以降は空欄ですから
このような集計表ならシンプルな計算式でできますよね。
ただ集計行が増えるので、少し表がごちゃごちゃします。
たとえば上記のエクセル集計表のように
前月実績、当月実績、当期実績だけ必要という場合。
次のような考え方で作成するとよいでしょう。
当期実績もSUMIFS関数を使って集計
エクセルで集計表を作る時
計算式はできるだけシンプルな数式にすることをオススメします。
複雑な長い数式や
よく知られていないExcel関数を使った数式は
メンテナンスがやっかいです。
エクセルの達人しか修正できないという
属人化を招き、その資料はブラックボックス化してしまいます。
ということで
今回の質問では明細表をもとに
条件に合致した集計ができるSUMIFS関数を使います。
SUMIFS関数は複数条件を指定できる便利な関数です。
今回の場合、期間と店舗名が合致したデータを集計するという計算式になります。
集計用に売上年月を計算しておくと便利！
明細データには2017年3月までのデータが入っています。
一般的に基準となる日付は上記のように
「年/月/日 時:分」や「年/月/日」となっています。
そのままでは集計の条件としてつかいにくいので
売上年月を別項目で追加しておきます。
売上年月：=YEAR(A2)*100+MONTH(A2)
これは数値データにしたい場合です。
もし文字データのほうがよければ
売上年月：=TEXT(A2,”yyyymm”)
こんな計算式になります。
あとはSUMIFS関数で集計します。
売上年月と店舗名を条件にして実績を集計
=SUMIFS(明細リスト!$L$2:$L$201,
明細リスト!$Q$2:$Q$201,$C3,明細リスト!$B$2:$B$201,D$2)
上記計算式の内容は以下の通りです。
合計対象範囲：明細リストの売上欄
条件範囲１：明細リストの売上年月
条件１：集計表の売上年月
条件範囲２：明細リストの店舗名
条件２：集計表の店舗名
今年実績欄も同じ要領で集計出来ますよね。
当期実績はSUMIFS関数の条件を変える！
当期実績の計算式は以下の通りです。
=SUMIFS(明細リスト!$L$2:$L$201,
明細リスト!$Q$2:$Q$201,”>=201604″,明細リスト!$B$2:$B$201,D$2)
上の計算式と条件１だけが違います。
条件１：201604以上
できれば計算式には固定の数値を使いたくないので
この条件も関数を使って求めれるようにしておいたほうがいいですね。
SUMIFS関数はとっても便利な関数です。
複雑な条件でも、このように少し手を加えればシンプルに計算できます。
参考になさってください！
エクセル計算式の入替を最も効率よく間違えなくできる方法
受講者でメルマガ読者の方からこんな質問がありました！
エクセルの集計表で
計算式が入力されている行の入替を簡単に行う事はできますか？
最も効率がよく間違えなくできる方法もご教授いただけないでしょうか。
答えは・・・
もちろん「はい！あります」
エクセルの計算式も入力データも行の入れ替えは簡単にできます！
たとえば上記のような表があるとします。
これは明細表をもとに、支店ごとの前年実績と今年実績を集計。
さらに前年比も計算しています。
今年実績には明細表をもとに売上年月と支店が合致した明細を集計してくれるSUMIFS関数が入力されています。
前年比には今年÷前年の計算式が入力されています。
「前年比」と「今年実績」の行を入れ替えたい
エクセルの計算式を一生懸命考えて作った集計表は
できるだけ変更したくないですよね。
特に前任者が作った表をそのまま使っている場合など
極力、触りたくないというのが本音だと思います。
しかし残念ながら
エクセルの資料に見直しはつきもの。
ある程度使ったら
ほとんど見直しや業務に合わせた修正が出てきます。
エクセルの行や列を入れ替える場合
中身がデータなら特に意識することなく
切り取ったらコピーしたりできると思います。
でも計算式だと
違う内容にならないか不安ですよね。
基本的にこの手順で大丈夫です！
移動したい箇所を切り取り
「切り取ったセルの挿入」で移動
計算式は正しく移動されます。
もし心配な時は次の手順で作業してください。
- 現在の集計表をコピーし空いてる箇所に値で貼っておく
- 移動したい箇所を切り取る
- 切り取ったセルの挿入で移動する
- １の内容と照らし合わせ数値が変わっていないか検証
- １で貼り付けた内容を消去
計算式を変更する時は
自分の意図した内容と違う内容にならないか心配です。
だから
事前にバックアップとして
シートやファイルをコピーしたりします。
大幅な変更の時はそれも大切ですが
今回のようにちょっとした変更で
バックアップを取るのは面倒ですよね。
そんな時は空いてるスペースにちょっとコピーしておきます。
この時普通にコピペしてはダメですよ。
コピペだと計算式がコピーされてしまうので
結果が違ってしまい確認用に使えません。
計算式に「＄」が付いていたら要注意！やり方が違います。
計算式に「＄」が付いているもの
見たことありますか？
これは計算式をコピーする時に
セル番地がずれないよう固定するために使います。
範囲指定がその代表例です。
計算式にこの「＄」がついている時は注意が必要です。
やり方が違う場合があるからです。
計算式内のどのセルに「＄」が付いているかがポイントです。
移動したセルを指定している計算式は要注意です。
移動後はセル番地が変わりますからね。
上記の内容でいくと
今年実績は明細表をもとに売上年月と支店が合致した
明細の集計する計算式が入っています。
前年比には今年÷前年の計算式が入力されています。
その中で「＄」が付いているセル番地のうち
移動した行に関連のある個所を確認します。
移動後も正しいセル番地になっていればOKです。
もし正しく変わっていない場合は
セル番地を正しく修正してくださいね。
大量にある場合は「置換」機能を利用すると便利ですよ。
［ホーム］タブにある「置換」機能です。
計算式も置換の対象になります。
ぜひ活用してくださいね！
Excelのデータも計算式も合ってるのに計算結果がエラー！？
昨日はマンツーマン講習をやりました。
マンツーマン講習を受講される方はほとんどが資料持ち込みです。
持込資料の計算式を修正したり新たに作ったりと
資料を拝見しながらやりたい事をお聞きしその場で問題解決します。
固有の名称などはテストデータになっていてもその方が実際に会社で使っている資料です。
守秘義務の問題でファイルを送っていただくわけにいかないのでその場で初めて拝見します。
マンツーマン講習の到達点は今ある問題を解決し必要な計算式をご自身で作れるようになるです。
おかげさまで今まで到達しなかった方はいません。
昨日の講習内であった質問の中によくある質問があったのであなたにも共有しますね。
昔私が現場を預かっていた時よく直面し対応していた問題です。
データも計算式も合ってるのにエラーになる！？
Excelでシート数を数えたい時はSHEETS関数を使います！
Excelのファイルに保存されている複数のシート。
シート数がいくつあるか数えたい時、あなたはどうしていますか？
えぇーっと
シート見出しのところにマウスをあてて、１ ２ ３ ・・・ って数えてます？
10コ以下くらいならそれでもいいですが、たくさんのシートがあったら大変ですよね？
受講者の方から、こんな質問がありました。
シート数を簡単に数える方法はありますか？
“Excelでシート数を数えたい時はSHEETS関数を使います！” の続きを読むExcel関数を使って消費税を四捨五入するROUND関数の使い方
たとえば、Excel関数を使って消費税を四捨五入したい時、どんなExcel関数を使えばいいのでしょう？
またそのExcel関数の具体的な使い方は？
Excelで氏名の姓と名を結合して表示する方法
Excelで名簿を管理する時、氏名はどんな風に入力しますか？
名前という1つの項目？
それとも姓と名で分けて2つの項目？
どちらだと思いますか？
ExcelのIF関数は条件によって結果を変えられる便利な関数
条件によってやることを変えられる便利なIF関数
業務でExcelを使っている場合、「IF関数」という名前を聞いたことがある方は多いと思います。
たとえばこのような納品書で、数量か単価が入力されたら金額が自動計算されるようにするためには、どんな計算式にしたらよいでしょう？
“ExcelのIF関数は条件によって結果を変えられる便利な関数” の続きを読む時代遅れのExcel解説サイトに注意しましょう！
Excel業務改善コンサルタントの小野です。
Ｅｘｃｅｌに限らずパソコンのソフトはバージョンアップするたびに、機能が進化します。
Ｅｘｃｅｌ解説サイトの中には、バージョンアップのたびに新機能や新しい操作方法を解説しているところもあります。
しかし中には、古い情報のまま止まっているサイトがあります。
そんなサイトは要注意。
なぜなら操作方法はどんどん変わるからです。
では、あなたが見つけたサイトが古い情報かどうか見分けるにはどうしたらいいと思いますか？
そう、更新日付を確認するのは1つの方法ですね。
しかし厄介なのは、比較的新しい記事でも、内容が古く間違っている場合もあるということ。
先日、私が見つけたのはこちらの情報。
「Ｅｘｃｅｌ関数を入力する時、ダイアログボックスを使ってはいけません。
なぜなら、関数の組み合わせができないからです！」
これは間違った情報です。
容量と商品名が一緒に入力されている明細を容量別に集計したい
Excel業務改善コンサルタントの小野です。
たとえば、上の画像のように、商品名と容量が一緒に入力された明細表があるとします。
この表を使って容量別に集計するとしたら、どのように作業しますか？