Excel月報生產或銷售計劃目標類型（EOMONTH VLOOKUP SUMIFS INT）綜合應用！

大家好 這張呢 我們來講一個實戰案例 那這個案例呢 但凡你用Excel表格 關於什麼超欠產呀 包括什麼實際和目標的去對比呀 你都會使用到這個案例當中的一些情境 好 首先呢 最左側的表格呀 是一張實際的銷售表格 當然你也可以把它給想象成爲是一張生產的表格 那一月份到六月份的一些銷售數據情況 好 那這個是實際的銷售情況 然後在這個右邊的表格呢 是關於呀 在我們的這個團隊當中 每一個人 他在這一年當中的總體目標情況 最後達到的目的呢 你看啊 我們可以在每一個月的月底呢 來統計 比如說掃地森 這個是一月的月底 這個是二月的月底 三月的月底 一直到六月的月底 然後因爲我們會有年度的銷售目標 那均攤在每一個月當中呢 那就應該是他在每個月當中的銷售目標情況 那這個呢 是他實際完成的情況 那當月超欠的情況以及累積超欠的情況 那最後呢 就是說 譬如啊 你看在一月份的時候 他的目標是6100 而實際完成了只有4700左右 所以呢 當月啊 他欠了1350億 好 那下個月的計劃呢 我們就應該把這個月沒有完成的放在下個月當中去作爲下個月的目標 所以呢 我們只需要做完第一行的函數之後呢 整個往下去進行一個轉手就可以得出所有的結果 看到我們空白的表格 在這個表格當中呢 是我們需要去做計算的內容 那這個啊 是我們的數據員情況 在沒有寫任何內容的時候 可能你會這樣去做 把姓名整個呢 把它給複製放在這個位置 然後呢 再分別的來標記 他都是這個一月份 然後再往下走呢 這一塊呢 就應該是二月份 再往下走呢 就應該是三月份 那有沒有函數的方式去進行生存呢 首先啊 我們要確定一點 就目前爲止呢 我們總共是六個月的數據 所以呢 就譬如說掃地森 他就應該要重複六次 然後呢 阿清呢 同樣是重複六次啊 所以如何用函數去做 這個是我們需要功課的第一個難題 好 你看啊 我會怎麼做呢 你可以在這邊來使用到一個row函數 譬如說我們想要重複某一個序號 重複六次 我就會選擇row選擇到 第六行當中 然後呢 再去除以六 確定好把這一列啊 整個呢 先可以把它給換成是常規格式 好 你看啊 當在往下拉的時候呢 他就會變成一 然後一點幾 然後到這個位置來的時候呢 他其實就等於說是 row十二行嗎 用十二呢 去除以六的話 正好就等於二 而在之前的部分呢 他是用第十一行的行位去除以六 他就等於一點幾嗎 然後你再往下拉的時候呢 他就會呈現一個這樣的倍數 那三好 那我們是不是可以把但凡他是一點幾呢 或者是一的話呢 就把它給變成一 如果是二點幾 那我們就把它給變成啊 那這樣的話呢 其實就可以達到就是把這個數值呢 去做一個重複顯示啊 所以你可以在前方呢 來加上一個int函數 把它給變成是一個整數 破戶回來確定 然後呢 再做一個雙擊下拉啊 當然我之前呢 是已經把它給做好了位置啊 當然你可以往下拉更多啊 他也不會影響我們的結果了 好吧 好 大概呢 是把它給拉扯在這個位置 大家自己在做的時候呢 需要手動注意一下 好 然後呢 你看啊 這個是不是一啊 這個一呢 所代表的就是在整個這一列當中 我們來找到第一行的內容 然後在整個這一塊呢 我們可以來找到第二行的內容 對不對 所以呢 可以在前方呀 來給到一個index函數 然後呢 選擇到這一列當中 F4絕對應用它 然後啊 剛纔的這個int函數呢 來作爲行爲確定 確定 然後做一個雙擊下拉 那這樣的話呢 我們就可以把所有的這個負責人呢 去做一個六次的擺放 好 那關於日期呢 你看 是不是這個掃地森就應該是一月份 你看啊 我先把它給換成是常規格式 然後這邊就是二月份 三月份 四月份 五月份 六月份就應該是這樣去做 對不對 那但是啊 有可能啊 你的這個在原始表格當中呢 它並不是從一月份開始的啊 可能是從二月份開始的啊 所以呢 你可以這樣來做啊 你可以提前呢 把這張表格的日期呢 去做一個排序 那最小值呢 就應該在最頂端 當你在做基礎的數據統計的時候呢 那也是從啊 比較遠的日期統計到比較近的日期嘛 它也會遵循是啊 從上往下去統計的時候呢 這個時間呢 它是遞增的啊 所以你可以來選擇到等於這個單元格 但是啊 我們通常呢 都是在月底呢 去做統計啊 所以你可以在前方呢 來給上EOMAS函數 然後選擇給到0 代表是當月的月底 然後呢 在下一個單元格當中呢 我們就可以來做一個判斷 如果這個單元格 它等於這個單元格 你看他們倆是一樣的嘛 當他們倆是一樣的話呢 我們就會在前面的這個單元格的基礎值上 去跳到下個月的月底啊 所以呢 你可以來給到EOMAS函數 選擇到 選擇到它 然後在下個月的月底怎麼表示啊 因爲0是當月月底嘛 1呢 就代表是下個月的月底啊 括弧回來 那否則的話呢 我們則會顯示EOMAS函數 選擇到這個固定的單元格 然後呢 0代表是當月的月底 括弧回來 確定確定 好 然後呢 做一個雙擊下拉 好 你看啊 我們可以來把它換成是日期格式 1月的月底 然後2月的月底 3月呢 6月的月底啊 下方呢 它都是1月份到6月份的月底顯示 接下來呢 我們可以來做目標銷售量的調取 你看啊 掃地森呢 它的年目標銷售量是7萬多 所以均攤在12個月當中呢 就應該是用這個值呢 去除以12 對吧 所以啊 我們可以用VLOOKUP函數來查找它 在這張表格中間來進行查找 返回第二點 0精確匹配括弧回來 然後呢 在這個基礎之上去除以12 確定 然後呢 再來做一個雙擊下拉 那實際完成應該如何去做呢 實際完成 你看啊 這個是實際的表格 意思也就是說 2021年的1月份掃地森 它實際具體完成了多少 那是不是就應該是把掃地森在1月份當中 它所有的這個銷售量來做一個彙總呢 但是你會發現這邊呢 它是這個2021年的1月31號 它是一個時間點 而這邊呢 它同樣是時間點 那我們只能夠用月份去做匹配 當然你可以考慮使用到桑木EFS函數去做也可以啊 我們可以先來做一下桑木EFS函數 等於桑木EFS函數選擇對整個啊 這個實際銷量呢 來做一個求和 好 然後再來選擇到應該是掃地森啊 選擇到姓名列當中 當前姓名 好 然後再來選擇到時間列當中 你可以調取它的月份嗎 在桑木EFS函數中間 你沒有辦法使用到month函數去調取它的月份 那就變成了數組 就沒有辦法去執行 所以呢 我們可以來雙引號 它應該要大於等於 大於等於月初嘛 那這邊只有月底顯示嘛 所以呢 你可以來連接EOMonth函數 然後選擇到好吧 選擇到它 然後呢 再來輸入-1 代表的是上個月的月底 如果是上個月的月底的話呢 那你直接呢 就可以來給到大於號就可以了 不要等於 大於上個月的月底呢 那就是從本月的月初開始 同時呢 在時間列當中呢 它還得雙引號 還得來小於等於 對吧 當月的月底 然後呢 再來連接當前的當月的月底單元格 確定確定 然後呢 做一個雙擊下拉 那這個是第一種做法 那第二種做法呢 我們可以啊 把這個實際完成 把它給標記過來 啊 然後呢 可以來寫到啊 等於什麼呢 直接可以來使用到布林邏輯 Sum Product函數的方式去做 首先呢 可以來選擇到整個姓名列當中 它應該是等於當前姓名 括弧 這是第一重啊 第二重呢 我們可以來選擇Month函數 然後呢 選擇到整個日期列當中 當它的Month呢 等於我們當前的Month 再來選擇到它 好吧 括弧回來 啊 並且呢 最後反饋的內容呢 是需要對實際銷售量去做彙總 所以啊 來選擇誠意這個範圍 在最前方呢 來使用到Sum Product函數 括弧回來 確定確定 然後雙擊下拉 啊 所以他們倆的結果呢 它是一樣的 都是一樣的啊 那你可以使用到這兩個公式都可以啊 我在課件當中呢 會要把它給做個隱藏 大家可以把它給展開看一下 當月超欠應該如何去算呀 等於我們原始的目標呢 是6100左右 但是實際呢 只完成了4700啊 所以呢 去減去它 那這個呢 是當月超欠 雙擊下拉 好 然後累積超欠呢 我們可以來使用到Sum If函數 需要對當月超欠去做一個累積超欠的求和啊 所以呢 可以來選擇的這個單元格 到這個單元格 並且呢 把第一個J3呢 來做一個絕對引用 這個範圍來作爲求和區域 條件呢 就應該是選擇到這個單元格 同樣到這個單元格 到這個單元格啊 這個是條件區域啊 最後呢 是條件啊 同樣這個F3第一個位置呢 需要來做絕對引用啊 扣活回來 確定 然後呢 再來做一個雙擊下拉 下個月的計劃呢 你看啊 它既然是計劃的話 並且呢 是在月底所做出來的計劃 那當然下個月的計劃 我們是統計在一月的月底當中 而不是統計在二月的月初當中啊 所以應該統計在這個位置 它應該如何來做呢 很簡單啊 直接呢 使用到目標銷量 來加上累積超欠 確定 然後呢 再來做一個雙擊下拉 好 如何來做驗證呢 你看啊 那譬如說 我們可以來選擇整個這個系列當中啊 因爲這邊我們應該不會用到了啊 所以呢 我可以先插入一些空白列 給大家來做一下驗證 好 你看啊 我們可以到這邊先來算一下 掃地森啊 他前五個月的一個目標銷量 或者是選擇到六個月的銷量啊 那這個呢 就是我們在上半年呢 他的一個整個銷量的目標值 對吧 好 然後呢 我們再來看一下這個掃地森呢 他實際的前五個月的銷量 所以呢 就應該是從這個位置拉扯到這個位置 你看他只完成了這麼多 所以啊 在你看啊 在這個五月底的時候呢 我們就會想說 哎呦 那六月份的計劃會有多少呢 你看啊 是不是就應該是等於他去減去他 那9333 正好呢 就等於那五月底 也就是在六月份當中的一個目標值 對吧 所以呢 這邊應該是沒有任何問題的 在這一張當中呢 其實啊 關於這個公式 大家需要好好去理解一下 那如果你的這個實際銷售情況呢 他會有12個月的話呢 那你就可以把它給輸入是A12單元格去除以12 其他的部分不需要去做改變 然後保留啊 這個前面兩行 然後整個呢 你再把它給往下拉 就可以得出最後的結果 好吧 所以這個位置 大家也需要特別理解一下 那關於什麼sum product函數呀 sum if 函數啊 這個都是我們所講過的 那關於後面這一塊的什麼下個月的計劃之類的呢 都是屬於一個比較簡單的計算邏輯啊 大家需要好好的去理一下 那如果你是和生產相關的專業的話呢 可以把這個表格呢 把它給變成是生產相關的一個計劃表格就可以了 那今天這張呢 咱們就講到這個位置啊 希望大家能夠有所收穫