Excel經典公式嵌套(OFFSET+SUMIFS+ROW)按"周"匯總的所有情況案例,你一定會用到!

Urin的自习室
19粉絲
大家好
這一章我們來講週數據彙總的案例
等會會要講到三個案例
每一個案例都非常的經典
首先大家可以看到這張表格是
2023年的1月2號
然後一直統計到4月2號的一些數據
第一個表格是比較規範的
等會我們會要去講到不規範的情況
在大家的理解當中是不是這個樣子的
一週就是從週一一直到本週的週日
對嗎
這個叫做一週
當然也有可能在大家的理解當中
它是從週日到下一週的週六
這個它算作一個週期
所以這個根據大家自己的實際情況來
如果你認爲它是一到七
正好是一個周的話
你自己在統計數據的時候
你就從週一開始來進行統計
因爲有很多同學可能會比較癡迷去
使用到一個叫做week number函數
注意這個week number函數
它是以什麼
它是以一年的第一天開始
然後去做的循環周
好吧
所以如果這一年它最開始
它是以週五開始的
這個時候你就很難去做準確的計算
所以你自己在做統計的時候
你一定要說
比如說你可能前面還統計了兩三天的
數據
那個數據你就不要管它了
你就從一月二號開始統計之前的
數據
你算作是之前的歷史數據
好吧
我們去做周的計算的話
就按照大家自己的從週一開始的邏輯
然後不停的往下去排就行了
好
接下來第一種比較規範的情況是
這個樣子的
你看我會統計每一天
這個就是週一到週日
這個是一個周對吧
第二個是第二個循環周
然後再往下走是第三個循環周
這個樣子
所以如果我們要去做週一的數據的話
理論上就是以7天爲單位去進行一個
彙總
對嗎
所以你有可能會說
那我直接就選擇SUM函數選擇到
這一列當中選擇這7天
然後你再往下拉
這個樣子是不行的
因爲你在往下拉動的過程中間
它其實是隻移動了一個單元格
所以這個位置我們需要使用到
offset函數去進行
以7天爲單位去進行移動
所以可以來寫到等於offset函數
選擇到第一個單元格
好
然後在row這個位置
注意我會選擇使用到row函數
好
Row當前row是第三行
我用3去減去一個行差值
再用它們的一個什麼
再用它們的一個相減值再去乘以7
然後跳過colon在高度這個位置
我們可以選擇給到7
對吧
因爲它是以7天爲一個單位
去進行的一個計算
所以括弧回來確定
這個時候你就會發現說
正好這個就是一週
看到沒有
這個就正好是一週
然後你再往下拉扯一個單元格
你再看一下這個位置
我們是從哪個位置開始
是應該從41開始一直蔓延到57
對吧
所以剛纔在做計算的過程中間
我們再往下拉的過程中
你們這邊把它給換成是6
然後我們再來看一下這個結論
這邊是沒有問題
你再往下拉扯一格的話
就是41到57
好
你再往下拉扯一個單元格
就變成是第三週
第三週就是從72一直到87
對吧
好
這個部分做完了之後
你直接可以在前方去做一個
sum函數
去做一個求和
我們就可以得出最後的結論
這個就是第一種比較規範的情況
好
我們再來看第二種情況
第二種情況是這個樣子的
首先大家可以看到
1月2號對吧
它到1月8號
然後它整個是一週
但是你像第一種情況
我們是每一天都會去做統計
但是有可能在實際的運轉過程中間
它就會有一些天
它不會統計進來
比如說1月9號
1月10號
你看1月11號沒有對吧
所以如果你按照之前的一個方法論
去做的話
就是以它爲基點
然後蔓延7個單元格的高度
這個位置往下蔓延7個單元格的
高度不就變到這個位置來了
對吧
所以這個時候這種做法
和第一種做法就會不一樣
如果你在統計的過程中間
中間會有缺失的情況
我們就只能夠通過計算的方式去做
好
首先我先給大家做輔助例
輔助例
這個叫做開始時間
然後這個叫做結束時間
等會我們可以把輔助例的公式
最後把它給合併在一個公式當中
大家需要了解它中間的邏輯所在
好
首先我們可以選擇等於選擇到
1月2號這個單元格
對嗎
你注意1月2號
它一定
因爲如果你要去統計數據的話
它一定是以週一爲單位去進行的統計
所以如果我把1月2號
把它給換成是日期格式的話
然後我們可以來看一下
這個就是週一
對吧
好
你再看
週一
我在它的基礎之上去加上多少
去加上
一個7的話
其實也就到了下週的週一
對吧
所以我們理論上
應該是加上6
這個樣子纔等於是
本週的週日
對吧
好
但是我的總體思路
是說
我可以來找到開始時間
對吧
就比如說週一
然後到1月8號
在這個範圍當中
它就是屬於
第一週它的數據
所以我可以用sumifs函數
然後大於等於開始時間
小於等於結束時間
但是這個位置它是1月8號
所以我們應該如何來做
你看首先
我們可以在6的基礎之上
然後去乘以
row函數
然後當前row是第三行
我們用3去減去一個行差
再用它們的括弧
再去乘以6
所以0去乘以6的話
就等於0
0再去加上這個C3
它是不是就變成了是
1月2號
對吧
所以你在後面
可以用它再去加上
6
就等於說是
這個結束時間
但是這種做法它還是有問題
你看
當我再往下拉的時候
往下拉
我多拉扯幾個單元格
你可以來觀察一下
似乎看上去沒有問題
其實它是有問題的
爲什麼這麼說
你看
我現在往下拉的過程中間
這個值它沒有問題
它是1月9號
然後你再往下拉扯一個單元格
它變成是1月16號
對吧
看似沒有問題
但是當你拉的足夠長的時候
就會出現問題
因爲你可以發現
它這個中間的邏輯是怎麼樣的
我們用C3單元格去加上它
你再往下移動變成C4
再往下走變成C5
如果中間一旦出現斷檔的話
我拉扯到1月10號
你再往下拉
其實應該是11號再往下去
加上這個row減3
然後再乘以6
對吧
但是你拉扯到這個位置來的時候
可能就會出現什麼樣的問題
可能就會出現說
它會往下多走一天
多走一天的話
就意味着說
某一週它的週一
它就沒被加進去
對吧
所以比較穩妥的做法
就是說我們可以把C3單元格
去做一個鎖定
鎖定之後
每一次往下去移動一個單元格
你看它變成C4
實質上就是說
在它的基礎之上去加上了1
對吧
所以你可以去乘以
然後同樣是加上row函數
然後row函數去減去3
然後你再看
我們用row函數去減去3的話
此時此刻是不是就應該是零
對吧
所以第一個位置它沒有變化
但是當你在往下拉扯的過程中間
你再看一下
當然第一個位置
我們需要去做絕對引用
然後你再往下去拉扯一下
這個就是1月9號
對吧
就是在剛纔的C3
它的基礎之上
然後又累了一天
對吧
然後你再往下去做
就絕對不會出現任何問題
好
然後後面的結束時間
就是在它的基礎之上去加上6
所以你要最後把它給匯放在一個公式當中
所以你可以把剛纔的公式去做一個複製
然後選擇把它給加過來之後
在它的基礎之上再去加上6
這個樣子的話
纔是我們的結束時間
和開始時間它的計算方法
好
然後我們可以來寫上
等於sum if函數
然後選擇對這一列去做求和
對嗎
然後再選擇到第一列當中
來作爲求和的一個條件區域
然後我們再選擇
它應該是雙引號
大於等於
開始時間連接到單元格
然後我們再來選擇到同日期這一列當中
然後它應該怎麼樣
雙引號小於等於
然後再去連接結束時間
然後給到確定
然後你再去做一個雙擊下了
這個也就是我們的彙總結果
當然最後我們之前是講過的
需要把這個公式把它給融合進去
對嗎
其實就是你想讓公式簡化的話
可以去做一些let函數之類的
這個位置大家自己可以去做
對它去做control c的複製
然後用它去替換剛纔的開始時間
對吧
然後結束時間
我們對它去做一個複製
然後再選擇
把它給貼在結束時間所在的位置
看似比較長
其實它的最核心的部分
就是這個開始時間
和結束時間的計算
最後你可以把這兩列
可以去做一個刪除的處理
也就是我們公式它的一個製作方法
當然這個位置會有一個比較
取巧的地方
就是說我們在統計數據支出的
第一天它是週一
所以你就可以直接接觸到單元格
如果說你是從1月4號開始
所統計的這一週的話
其實這一週可能零零散散
也就只有三四天四五天這個樣子
所以它應該如何把它給包囊進來
這個計算就需要接觸到很多
輔助單元格
包括這個公式會比較長
需要使用到weekday函數
關於這種做法
我們之前應該是講過的
大家可以翻閱一下之前的章節
好
最後一個案例的話
就更加會貼近我們的實際了
你看這邊它可能
你看1月2號對吧
然後這邊它是有冬瓜西瓜黃瓜
然後分別所對應到的彙總的數據
所以其實我們所謂的第一週
是從1月2號一直到1月8號
對吧
但是這個中間會有很多的重複
所以你說你用offset函數
完全就不能是適用到我們的這樣的
情境
對吧
包括中間可能你看1月11號那天
又沒有統計進來
所以這個其實跟剛纔我們的做法
就是非常的相似
所以我們可以選擇把剛纔的公式
對它去做一個複製
然後再選擇把它給貼進來
貼進來
當然貼進來會發生一個錯位
發生一個錯位
所以大家自己在做的時候
你可以先來寫到開始時間和結束時間
所以我們可以選擇到這個單元格
然後F4決定用它
然後去加上row函數
然後row函數去減去一個行差值
然後再去加上
加上什麼
加上這個6
對吧
然後去乘以
對吧
這個row函數
然後再去減去3
然後後面需要加上括弧
前面加不加括弧
其實都差不多
沒有太多區別
好吧
然後可以把這段公式可以來做個複製
然後給它確定
對吧
這個就是1月2號
然後我們再來選擇等於SUMIFS函數
然後選擇到整個數據
彙總的這一列當中
對這一列去做求和
當然多了一個
剛纔的等於號
然後再選擇到日期系列當中
日期它應該怎麼樣
應該是雙引號
對吧
然後大於等於開始時間
然後它還需要怎麼樣
它還需要去小余到
所以我們再次選擇到日期系列當中
它應該是雙引號
應該是小余等於
然後再去連接
然後剛纔的開始時間
在它的基礎之上
再去加上一個6
對吧
然後我們所算出來的結果
其實也就是一個彙總的值
就包含了冬瓜
然後西瓜
然後和黃瓜
它們分別所對應到的值
它的彙總
但是上方我還有條件
還有冬瓜西瓜和黃瓜
對嗎
所以這個時候
你可以在剛纔的條件
它的基礎之上
再去選擇到產品系列當中
對吧
然後再去選擇
當前的產品
它的名稱所行而不所立
看似比較長
說實話也還是比較簡單的
如果大家能夠了解到
剛纔我們所做的公式的話
好吧
這一張咱們就講到這個位置
希望大家能夠有所收穫
