ボロボロ皇帝のボロ切れと塊

ボロボロ皇帝が悪態、嫌み、好み、何でも己の視点だけで自由に吐く「偏」な場所。たまにExcel、家電、音楽等についても好きに吐いてる。

ボロボロ皇帝が悪態、嫌み、好み、何でも己の視点だけで自由に吐く「偏」な場所。
たまにExcel、家電、音楽、ゲームイラスト小説映画、性癖について等、マジで何でも好きに吐いてる。

どうでも良いかもしれんが背景色変わっていくの見てくれ。
NEW→更にどうでも良いかもしれんがアイコンが息してるの見てくれ。


我輩は何にも制約されない身、
「耐えぬ」事を恐れないと決めたのだ!
我輩は好きなときに悪態をつく!
妬み僻み嫉みを背負い、
マイナーだろうが邪道だろうが
我輩は我輩の道を行く!
好きなものは懸命で不器用な諸君,
嫌いなものは器用で完璧を気取った奴らだ。

   ボロボロ皇帝は自由を求めてやってきた。もう我慢はしないと決めたのだ。かぶらず着飾らず、我輩は悪態をつくのだ!
    ※初期不良の【海馬】を神様が返品交換してくれず約四半世紀付き合ってきた結果、嫌なことも含めて色々忘れる。そのため比較的楽観思考ではある。
    悪態は激しい興奮を覚えながら吐いている。

         

Excel計算式:一週間分などの等間隔日ごとの数値を集計する方法 を訊かれたので

 

※さっさと本題にイケ!計算式よこせ!という諸君がいたときのために、

目次を作っておいた。

 

 

我輩は最近、まともに仕事をしている

なぜならば繁忙期が目前に迫っている、というかもう繁忙期に片足ツッコんでいるかもしれないからだ。

いや実は既に繁忙期かもしれない

梅雨明らかに入ってるよな?というような連日雨状態でもなお梅雨入り宣言を絶対しない気象庁*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)

と入力する↓

f:id:wornoutemperor:20210402010417p:plain

 

 で、そのまま何も考えずにオートフィル

f:id:wornoutemperor:20210402010420p:plain

 

そうするとこのように3/1~3/7分を集計した次に、3/2~3/8分と、一日ずれで集計され全く使えねーなみたいな状態になっている

f:id:wornoutemperor:20210402010329p:plain

 

それはそれとして置いといて、適当によこっちょのセル(F2)に、

7日ごとなので7という数値を入れ、下(F3)に1日から集計開始なので1と入力↓

f:id:wornoutemperor:20210402010337p:plain

 

一週間分ずつの開始位置を知りたい。つまり3/1~3/7で言えば開始位置は1日、その次が3/8~3/14で開始位置は8日、と言った具合に開始位置を入力したいが手打ちするのはめんどい。なので、最初の1日以降を加算で求める。まずセルF4に、

=F3+$F$2

を入力↓

f:id:wornoutemperor:20210402010341p:plain

 

そして、ぺー*2っと下に引っ張る

するとなんと素敵だろうか、開始位置日付が入力されていく!↓

f:id:wornoutemperor:20210402010345p:plain

 

次に、A列に1、2と入力してあとはオートフィルで連番を作る↓

f:id:wornoutemperor:20210402010350p:plain

 

まあこれはわかりやすく色を塗っただけである。

1が入力されているA3と同じ行のD3にはこのように1~7日までの集計値が入っており、

f:id:wornoutemperor:20210402010356p:plain

 

8が入力されているA10と同じ行には2週目の集計値が入力されている。

f:id:wornoutemperor:20210402010401p:plain

 

あとはVLOOKUPを使うだけである。

つまりF3,F4,F5・・・と一致するA列の値の同行にあるD列の値を求めたい(説明したつもりが余計まどろっこしくなったスマン)

なのでセルG3に

=VLOOKUP(F3,A:D,4,FALSE)

を入力。

f:id:wornoutemperor:20210402010405p:plain

あとはオートフィル!

f:id:wornoutemperor:20210402010410p:plain

 

要らんところを消して完成である。

f:id:wornoutemperor:20210402010413p:plain

 

ダサいやり方代表みたいなものであるがコイツのデメリットはダサさでは無く、VLOOKUPという、参照と検索を繰り返して値を求めるクソ重な関数を使用しているというところにある。なのでデータ量が多い場合は絶対避けた方が良い

 

つかXLOOKUPなんて関数できてたのか、うらやましい。左列開始じゃ無くて良いって意外にも汎用性が広がってるのと、不要なセルを参照しないんだと。パフォーマンスが向上しているということだな。なんでOffice365ユーザーだけなんだ。くそったれ(※2021/04/08現在時点)



②SUMIF使用(実用性がある方)

手順が少ないので断然こっちの方が楽である。じゃあ①要らねーだろって話だが、保険だ保険(記憶の)。

 

条件は同じく、3月分の日ごとの値を一週間分ずつ集計、である。

まず日付が入力されているB列の左隣のA列に1を一週間分、つまりA3:A9まで、7つのセルに入力

f:id:wornoutemperor:20210402010424p:plain

 

次に2をセル7つ分入力したいが面倒なので、A10には、

=A3+1

を入力する。

f:id:wornoutemperor:20210402010311p:plain

 

これにオートフィルをかけると、単純に1ずつ増えた数値が入力されていく

このA列に入力された数値がということになる。例えばA17:A23までが3週目だ。

説明が下手で申し訳ないがスクショがあるので理屈は分かるだろう。

こんな感じである↓

f:id:wornoutemperor:20210402010315p:plain

 

で、適当に横のセル(E3)に、求めたい週の数値を入力。

f:id:wornoutemperor:20210402010318p:plain

 

ざっくり5週目までとし(これ以上無いので)、集計のためにセルF3へ

=SUMIF(A:A,E3,C:C)

と入力。意味は、A列内にE3と一致する値が入力されたセルがあれば、一致したセルと同じ行にあるC列の値を合計、みたいな感じだ(若干違うが大体そんな意味)。

f:id:wornoutemperor:20210402010322p:plain

 

で、これをオートフィルし完成である↓

f:id:wornoutemperor:20210402010326p:plain

 

 

とまあ、これで計算式の説明は以上である。

似たようなもので曜日ごともついでにやろうとしたがやめた…。スクショとリサイズがなかなか面倒である。

 

 

そういえば余談だが、

所長へ伝えたかったBIで集計したデータに関する注意事項は、とあることをしないとダブりが発生する状況だったので、我輩がとあることをして無事使えるデータにしたが、そのデータを使ってくれているか、の確認だったのだが、これは手遅れであった*3

ダブり=数値がアホほどズレていたはずなのだが、所長はこれで満足しており、なんだか、複雑な気持ちである。

分析とは、結局都合よく行われている曖昧なものなのかもしれない。

 

 

*1:まるで宣言したら負けとでも言うかのように。

*2:ペーストのペ。

*3:前日にここに保存しておく、と伝えていたのだが、所長は自分でBIからデータをダウンロードして使っている状況だった。