先日、久々にExcel使って集計作業をしました。
最近、マクロでデータベースから取得したデータをピボットでごにょごにょするくらいしかしていなかったので頭が固まっていて、はじめのうちはエクセルワークもアナログ作業の連続、いわゆる力技で関数の変数1300項目を手で直そうとしていました。
さすがに途中でしんどくなってきたので、作業しながらながら色々と考えながら関数やら置き換え機能を思い付きでやっていきました。。
考えてみたら使っている関数とかショートカットキーって毎度同じもの使うなと思ったのでまとめておこうかと。
これよく使うよねという関数5つ
１．vlookup関数：指定した検索値を割り当てる。
２．countifs関数：複数条件セルデータをカウントする。
拠点1の回数が10以下の数
３．置き換え：検索した値を指定した値に置き換える。
４．sum関数：合計を計算します。鉄板です。
５．if関数：条件にあえば真の値を、条件に合わなければ偽の値をセルに表示する。
チェックにも使えます。
合わせて操作編、ショートカットキーをある程度覚えておけば作業がスムーズに運びます。
で、よく使うショートカットキー10選がこちら。
１．Ctrl + c （コピー）
２．Ctrl + v （貼り付け・ショートカットせずに値、数式、書式で貼り付けも結構使いますね）
３．Ctrl + ↑ （一番上のセルまで移動）※連続した値のあるセルの場合
４．Ctrl + → （一番右のセルまで移動）※連続した値のあるセルの場合
５．Ctrl + Shift + ↑ （行列の範囲選択で
６．Ctrl + Shift + → でよく使います）
７．Ctrl + 1 （セルの書式設定・表示形式（小数点第何位とか）から罫線まで）
８．Ctrl + s （上書き保存／作業中に何かあったら泣きますので定期的に保存する）
９．F4キー （前の操作を繰り返す／セル色塗り等々）
クリック、F4、クリック、F4という作業
１０．選択してDelete （ショートカットではないが；不要なオブジェクトよさようなら）
以下。。。
以上、基本的な操作ばかりですが使いようで作業時間が大幅に短縮されます。
今回はある意味偏ったあくまで私が作業した時によく使うものを取り上げました。
人それぞれ流派が？あるかもしれませんが参考になればと思います。
最後に今回やった作業を編集しつつざっくりと書きますと
拠点でひと月に何回担当しているか、また回数が10回単位の人数をカウントする
上記分析をするのに拠点ごとに担当したしたデータがあります。
よくある基本的な分析です。
ピボットテーブルで拠点と回数を行に、人のIDを値として値フィールドを個数で集計し、人IDの数を単位（0-10，11-20等）でグループ化すれば簡単に表現することができます。
ただし、今回は０から10回の中にある0回の人を別にカウントしたいという要望がありました。これをどうやって集計するか？
上で出した0-10回データからデータから拠点ごとに担当が0のものをcountifsした数字を引けば出すことができます。。
色々な方法が考えられるのですが今回はやり方を探してる時間があれば作れてしまうかと結局countifsを使って 単位ごとの差分で単位ごとの数値を割り出しました。
ただ、この作業はそれなりに作業工数がかかります。
なぜか？例えば
countifs($A$3:$A$200,”拠点１”,$B$3:$B$200,"<=10") ｰ countifs($A$3:$A$200 _
,”拠点１”,$B$3:$B$200,"<=1")
※_は次の行へ ※回数が整数の場合の範囲集計です。この式だと0.1のデータは集計漏れになります。
の中にある”拠点１”または"<=10"などは手で打ち変えないといけません。
これだけでも約1300箇所。
さて、どうするか？
ここで使うのが置換です。
一つ計算式を作ったら行・拠点と列・単位回数（1-10、2-20）のセルに数式で貼りつけます。すべて同じ計算式になっていますので（絶対参照のA列B列は変わらない）
例えば、”拠点１”で検索して”拠点２”に置換。"<=10"で検索して"<=20"に置換します。
あとはポチポチ行ごとに置換する値を変えていけばできあがり。
で、作成したフォームを月別にまとめた別シートに貼りつけていきます。
また、計算式を使い回しをしようと別のエクセルブックやエクセルシートに貼りつけるとブック名やシート名が計算式に追加されます。
この修正作業がまた面倒です。
ここでも検索・置き換え機能を使って追加された名前を（これはすべて共通なので一つ検索してすべて置き換えるだけ）置き換えるだけで瞬時に修正が可能です。
もしこの作業をもちまちまやってたら結構な時間がかかりますね。
これでだいぶ時間短縮になったと思います。
一つの例を上げましたがこんな方法あるのにという突っ込みがあればぜひともコメントいただければと思います；
それにしても久々だったので途中でこうしたほうがいいなと修正しながらの作業ではありましたが。。。やはりたまには触っていないと忘れてしまいますね。
今日取り上げたのはほんの一部、他にもたくさん便利な機能や関数があるので、どういった集計作業の時にこれが使えるなど、ある程度覚えておくとと一層作業がはかどるかもしれません。
VBAやマクロを駆使すればもっと簡単なのかもしれませんが、また同じ作業をする訳でもないのでそんな時はエクセルにもとある機能や関数を使ってぱぱっと作るのも大切かと。
定期的に同じ作業をするならばマクロやVBAなど駆使してテンプレート化させてもいいかもしれませんのでその点は状況に合わせて、結局は求められるデータさえ作成できたらいいのですから。。。（ほんとはその先にある分析で何かが増えたり改善されるという事まで追わないといけませんけどね。。。）
追記：最後の内容がわかりづらいので少し書き直してみました。
