こんにちは！
Excel業務改善コンサルタントの小野です。
わたしが毎月開催しているエクセルセミナーでは、受講してくださった方に、毎回アンケートをお願いしています。
その中で、エクセル講座を受講したいと思ったきっかけを伺っています。
エクセル講座を受講しようと思ったきっかけで1番多いものは何だと思います？
1番はエクセルのスキルアップです。
では2番目は何だと思います？
エクセル研修の受講者様から嬉しい感想をいただきました！
3月に行ったリクルートマネジメントスクール様でのエクセルセミナーのアンケート結果は5段階で4.3でした。
さらに1か月後のアンケートで
「仕事上役立っていることや成果につながっている」の欄は4.1でした！！
講習アンケートで高評価をいただくと、やっててよかったぁ～と実感しますね♪
と同時に、
さらにセミナー内容を良くしようと意欲がわきますね。
直後の感想はすでにいただいていたけど、今回は受講1か月後の感想も入っていました。
直後のアンケートに答えてくださったのが61％。
さらに1か月後のアンケートに答えてくださった方が29％。
次回は6月です。
すでに19名のお申込があるので、しっかりと準備をして臨みます！
Excel（エクセル）徹底活用術
～関数を活用した業務効率アップのコツ～
リクルートマネジメントスクール様
http://www.recruit-ms.co.jp/open-course/dtl/S00150/
エクセル集計表で対前年実績と当期実績も集計するには？
エクセルではいろんな集計表を作成します。
集計表ごとに計算式を考えて作らなければいけません。
どんな計算式にすればいいかわかる時はいいですが
わからなければネットで検索することになります。
ピンポイントで見つかればいいけど
なかなかそう うまくはいきませんよね。
ネット検索ってあっという間に時間が経ちますよね(・・;)
今日はメルマガ読者で受講者の方からいただいた質問への回答です。
エクセルの集計表で当月実績だけでなく期首からの累計も集計したいです。
その際は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のピボットテーブルで複数項目を集計したい！
Excelのピボットテーブルってご存知ですか？
ピボットテーブルはなんだか難しそう・・・と敬遠されている方も多いのですが、実は誰でも簡単に使える機能です。
ピボットテーブルは大量データでも数分で集計できる便利な機能です。
Excelの計算式を使って集計表を作るより、ピボットテーブルを使ったほうが簡単で便利です。
集計項目を変えたりレイアウトを変えるのも一瞬で変更できます。
Excelのピボットテーブルで数量と金額など複数の項目を集計したい時はどうすればいいのでしょう？
値のところに数量と金額を配置すればいいだけ！
そうです。こんな感じです。
Excelのピボットで複数項目を集計し縦に並べたい！
もちろんこのように、数量と金額が横に並んでいる状態でもよいのですが、数量と金額を縦に並べたい時はどうしたらいいのでしょう？
Excelのピボットテーブルで集計する複数項目を縦に並べたい時は、次の図のように「列」に配置された「値」を「行」に移動します。
これで数量と金額を縦に並べることができます。
Excelのピボットテーブルは、集計作業が楽になるツールです。
しかも操作はとても簡単！
ぜひこの記事を参考にして使ってみてくださいね。
Excel業務の効率アップに必須！ピボットテーブルの使い方
Excel業務改善コンサルタントの小野です。
これは私が担当したEラーニング Excelコースのひとつ「Excel 2016 ピボットテーブル活用術」の一部です。
（YouTubeで公開されている動画です。どなたでもご覧いただけます）
Excelのピボットテーブルの使い方を解説しています。
ピボットテーブルは難しそう・・・
と思っている方がとても多いんですが、実はExcelのピボットテーブルの使い方はとても簡単！
Excel初心者でも簡単に集計表を作成できる、とっても便利な機能です。
あまりに簡単に集計表ができるので、他の人がピボットテーブルを操作しているのを見て
こんなに簡単に表ができるなんて・・・ まるで魔法のよう～
きっとなにか難しいものを使っているに違いない～
そう思われるのかもしれませんね。
でもピボットテーブルの使い方はドラッグ アンド ドロップ操作だけ。
それで集計表が作成できる、とても便利な機能なんですよ。
マウスでドラッグができれば使うことができます。
Excel関数や計算式を使う必要はありません。
Excelのピボットテーブルは
- Excelの集計業務をサクッと終わらせ、分析に集中したい
- 複数の集計表を一度に作りたい
- アンケートの結果を集計したい
こんな方にオススメです。
1つの集計表を数秒～数分で作成できますよ。
Excelを自己流で覚えた人がセミナー受講後にしみじみ語った事
Excel業務改善コンサルタントの小野です。
ある木曜日の午後7時、私はいつものようにExcelセミナーを開催しました。
セミナーのタイトルは「売上分析が楽になるExcelセミナー【0からのピボットテーブル】」
- 受講者の皆さんが質問しやすい人数
- 私から全員の方の操作の様子がよくわかる
を考え、いつも少人数で開催しています。
私はいくつかExcelの単発セミナーを開催していますが、中でもピボットテーブルのセミナーは人気です。
その日も8名満席でした。
みなさん熱心に聞いていただき2時間がアッという間でした。
9時になり講習が終わり、個別の質問も終わるころ、一人二人と席を立ち帰っていかれました。
そんな中、席を立たず最後まで残っていた男性がいました。
私が「ご質問は大丈夫ですか？」と声をかけると「大丈夫です。質問はありません」とのこと。
そして男性が話を始めました。
容量と商品名が一緒に入力されている明細を容量別に集計したい
Excel業務改善コンサルタントの小野です。
たとえば、上の画像のように、商品名と容量が一緒に入力された明細表があるとします。
この表を使って容量別に集計するとしたら、どのように作業しますか？