※さっさと本題にイケ！計算式よこせ！という諸君がいたときのために、
目次を作っておいた。
我輩は最近、まともに仕事をしている。
なぜならば繁忙期が目前に迫っている、というかもう繁忙期に片足ツッコんでいるかもしれないからだ。
いや実は既に繁忙期かもしれない。
梅雨明らかに入ってるよな？というような連日雨状態でもなお梅雨入り宣言を絶対しない気象庁*1みたいな感じで、我輩も繁忙期が来ているのか来ていないのかハッキリ分かっていないということだろう。まあとにかく最近忙しい。
あと周りも忙しくなってきたのか、頼まれごとがちょっと増えた。
そんな本日。
昨日所長から頼まれていた、BIで集計したデータがちゃんと役立っているか心配になった我輩。
使えてますか、問題ないですか、ちょっと注意事項が…的に所長へ声をかけたところ、
あ～大丈夫ですよ！助かりました！と答えた所長。そのまま我輩は注意事項を伝えたかったのだが、
「ちょうどよかった！ボロボロさんにExcelのことでちょっと訊きたいことが( `･ω･)」
と、とあるExcel上のデータを見せられた。
質問の内容はこうである。
Q.日ごとの売上数ヶ月分を、週ごと、つまり7日ごとに集計して表にしたいが、楽に集計するには一体…？
例えば、一年分の365日の売上データがある場合、それを一週目、二週目、三週目、四週目、五週目、六週目・・・と週単位で加算し表にしたい、と言うことだ。
ちなみに我輩は触らないと思い出せない事が多い。
頭で覚えていると言うより本能的な感覚で覚えているからだろう。なので、ちょっと考えて正直に「すみません、触らないと思い出せない人間なんですよ…思い出したらお伝えします」と伝え、追加で頼まれたBIでの集計と併せて自席に持ち帰った。
追加依頼分のデータをBIで集計しながら、
質問のあった週ごと集計の計算式について自分の課で使っている集計用のExcelデータをいじくりながらの2つの案が思いついたので、BIからの集計データと併せて所長へ報告。
バカ下手な説明で2パターンの計算式をプレゼンし終わったあと。
一応ためにはなったらしく、「昨日検索したんですけどなんか違うなーってものしか出てこなくて！こんな方法があるんですね！よかったです！」と喜んでくれていた所長の言葉を聞いて、
なるほど、検索したらまどろっこしい回答が出てくることもあるよな、単純シンプル明快な回答、あるといいよな、あと備忘録がてら残しておくか、
と思った結果、今、この記事を書くに至っている(所長の検索の仕方が下手だった可能性は置いといて)。
A.等間隔日ごとの数値を集計する計算式2パターン
※今回は7日ごとで計算しているが、3日ごとでも5日ごとでもなんでも可
①とりあえずオートフィル多用する系
これはあまり考えなくてもできるというか、アホみたいなやり方だが、一応出来るというやつ。
例えば下記のように3月分の日付ごとに並んだ値を一週間分ずつで集計したい場合。
C3:C9までが一週間分(7日分)であるのでとりあえず適当にオートフィルをかけやすいすぐ横のD列に
=SUM(C3:C9)
と入力する↓
で、そのまま何も考えずにオートフィル↓
そうするとこのように3/1～3/7分を集計した次に、3/2～3/8分と、一日ずれで集計され全く使えねーなみたいな状態になっている↓
それはそれとして置いといて、適当によこっちょのセル(F2)に、
7日ごとなので7という数値を入れ、下(F3)に1日から集計開始なので1と入力↓
一週間分ずつの開始位置を知りたい。つまり3/1～3/7で言えば開始位置は1日、その次が3/8～3/14で開始位置は8日、と言った具合に開始位置を入力したいが手打ちするのはめんどい。なので、最初の1日以降を加算で求める。まずセルF4に、
=F3+$F$2
を入力↓
そして、ぺー*2っと下に引っ張る。
するとなんと素敵だろうか、開始位置日付が入力されていく！↓
次に、A列に1、2と入力してあとはオートフィルで連番を作る↓
まあこれはわかりやすく色を塗っただけである。
1が入力されているA3と同じ行のD3にはこのように1～7日までの集計値が入っており、
8が入力されているA10と同じ行には2週目の集計値が入力されている。
あとはVLOOKUPを使うだけである。
つまりF3，F4，F5・・・と一致するA列の値の同行にあるD列の値を求めたい(説明したつもりが余計まどろっこしくなったスマン)。
なのでセルG3に
=VLOOKUP(F3,A:D,4,FALSE)
を入力。
あとはオートフィル！↓
要らんところを消して完成である。
ダサいやり方代表みたいなものであるがコイツのデメリットはダサさでは無く、VLOOKUPという、参照と検索を繰り返して値を求めるクソ重な関数を使用しているというところにある。なのでデータ量が多い場合は絶対避けた方が良い。
つかXLOOKUPなんて関数できてたのか、うらやましい。左列開始じゃ無くて良いって意外にも汎用性が広がってるのと、不要なセルを参照しないんだと。パフォーマンスが向上しているということだな。なんでOffice365ユーザーだけなんだ。くそったれ(※2021/04/08現在時点)。
②SUMIF使用(実用性がある方)
手順が少ないので断然こっちの方が楽である。じゃあ①要らねーだろって話だが、保険だ保険(記憶の)。
条件は同じく、3月分の日ごとの値を一週間分ずつ集計、である。
まず日付が入力されているB列の左隣のA列に1を一週間分、つまりA3:A9まで、7つのセルに入力↓
次に2をセル7つ分入力したいが面倒なので、A10には、
=A3+1
を入力する。
これにオートフィルをかけると、単純に1ずつ増えた数値が入力されていく。
このA列に入力された数値が週ということになる。例えばA17:A23までが3週目だ。
説明が下手で申し訳ないがスクショがあるので理屈は分かるだろう。
こんな感じである↓
で、適当に横のセル(E3)に、求めたい週の数値を入力。
ざっくり5週目までとし(これ以上無いので)、集計のためにセルF3へ
=SUMIF(A:A,E3,C:C)
と入力。意味は、A列内にE3と一致する値が入力されたセルがあれば、一致したセルと同じ行にあるC列の値を合計、みたいな感じだ(若干違うが大体そんな意味)。
で、これをオートフィルし完成である↓
とまあ、これで計算式の説明は以上である。
似たようなもので曜日ごともついでにやろうとしたがやめた…。スクショとリサイズがなかなか面倒である。
そういえば余談だが、
所長へ伝えたかったBIで集計したデータに関する注意事項は、とあることをしないとダブりが発生する状況だったので、我輩がとあることをして無事使えるデータにしたが、そのデータを使ってくれているか、の確認だったのだが、これは手遅れであった*3。
ダブり＝数値がアホほどズレていたはずなのだが、所長はこれで満足しており、なんだか、複雑な気持ちである。
分析とは、結局都合よく行われている曖昧なものなのかもしれない。
