Excel動態收益日曆模板,但凡是需要做日曆動態展示,這個方法都非常適用!趕緊用起來!

Urin的自习室
19粉絲
3 次觀看·2024年12月29日
大家好
這一張我們來講一個非常經典的小模板
這個也是來自於一些粉絲同學的提問
比如說這個是一個收益日曆
我們可以選擇到某一個月份
然後就可以體現這個月份當中
每一天我們的盈虧的一個情況
包括這邊的小卡片
也都可以發生相應的變化
好
首先我們這個案例是基於
可能你說你買了10個項目
然後每個項目它的淨值是多少
好
然後我們去算出每一天
它分別所對應到的收益
然後再把收益放在對應的每一天
它的下方
當我們在進行滾動
它也可以根據我們所選擇它的變化
而發生相應的變化
好
我們一步一步來進行構建
首先我們可以到這邊來寫到一個
2023年
然後這邊寫到一個月份
好
然後年份你可以標記在中間
所以去做一個自定義格式
並且在後方去加上一個年字
好
這個位置可以對它去做一個自定義格式
後面可以來寫上一個月子
好
我們可以選擇用一個豎直調節按鈕
對它去進行一個滾動
所以選擇去增加兩個按鈕進來
好
第一個按鈕
我們可以讓它去控制2023年
對嗎
所以可以選擇稍微拉下來一些些
整個把它給放在剛纔的框架中間
好
然後我們可以選擇對它去做一個數字
它最小值2023年
最大值可能你未來會要用很長時間
所以2026年
2028年都可以
單元格鏈接選擇到前方的單元格
好
這個位置再去對它去做一個單元格鏈接
鏈接到它
並且1到12月份
確定
好
現在當我們在進行滾動
這兩個值它就可以發生相應的變化了
好
接下來我們需要來構建當前的日曆
寫到一個日子
然後整個把它給往後去做拉扯
或者能寫到一字
就是週一開始
選擇週一到週日
好
接下來我們需要去搞清楚的日曆當中
最核心的一個函數
注意各位
我們到這邊可以來做個基本測試
譬如說我到這邊來寫到
等於data函數來進行日期的組合
2023年1月1號
括弧回來
確定
這個2023年它的1月1號
它一定會放在這個位置嗎
不一定
因爲你可以發現
2023年的1月1號
它可能它不是週一
沒有那麼吵
它是週日了
所以我們如何來構建第一天
就是在日曆當中
好
你自己想一下
我們可以到這邊來寫到一個
wake day函數
選擇到它選擇到二模式
如果它是週一的話
它就是一
如果它是週日的話
那就是7
所以括弧回來確定
你會發現
2023年的1月1號這一天
它是週日對嗎
週日的話
它對應到的是7
所以你想
如果現在此時此刻
我們用2023年的1月1號
然後去減去7的話
是不是它依然是週末
所以我們等於到上網的單元格
你再次把它給更換成爲是
星期幾的模式
你看它照樣是週日
所以我在剛纔它們相減的基礎之上
去加上
它就一定是
週一
所以我們應該是把週一
放在單元格當中
所以這個函數
它完整的構建方式
是使用到data函數
選擇到它
再選擇到amount
再輸入給到1
然後再去減去
它的一個次序位
weekday函數
選擇同樣
可以把data函數
把它給放進來
其實
關於
這個函數
我們之前是講過的
再加上1
這個大家注意
把它更換成爲是日期格式了
你看
2022年的12月
26號這一天
它就一定是週一
所以後面的單元格
可以等於前一個單元格
去加上1
再把它給往後
去做一個拉扯
我給大家把電腦當中的截圖
也做出來了
你看
2023年的1月份的時候
它就是剛纔我們所做出來的結果
正好週日這個位置
就應該是放上是
1號放在週日
它所在的位置
好
接下來
我們中間需要去空一行
對吧
空一行是需要去放上
我們所對應到的收益值
所以
當然你還需要對它去做一個數值
把它給變成是D模式
因爲我們可以在上方看到年份和月份
只需要在這個位置去顯示D就行了
這個位置可以來寫上
等於
最後一個單元格去加上1
中間空一行
後面的單元格
再等於前一個單元格去加上1
然後再把它給往後去做個拉扯
再選擇
看好了
選擇整個這一行
選擇隔行
去做一個複製粘貼
複製粘貼
總共我們應該是需要去做
5到6行左右
就類似於這個樣子
我們先暫且做成這個樣子
不夠我們再說
好
再選擇
可以把整個它的列寬
對它去做一個酌情的調整
再選擇
可以把表頭部分對它去做居中
然後選擇下方的文字部分
同樣對它去做居中
對下方去增加一個表格的邊框
好
然後選擇
試圖選擇把網格線來進行取消
這個樣子的話
我們整個日曆
基本上來說的話
就已經構建出來了
我們可以來嘗試一下
選擇2月份
然後選擇3月份
然後選擇4月份
好
這一步沒有問題之後
接下來我們再繼續往後走
我們可以選擇
把這兩個單元格
去增加這個邊框
讓它看上去會更加協調一些些
好
然後選擇把表頭
對它去做一個顏色填充
所以我會選擇
給到一個灰色
然後字體顏色
把它給變成是白色
再選擇到
隔行當中
把表頭這一塊
我們都選擇
給它填充一個顏色
給到一個類似於黴紅色
然後把隔行
等會需要填充數值的部分
它的行高
稍微去做一些調整
合適就行
就類似於這個樣子的
好
當然了
我們等會還需要去做一些
比較麻煩的條件格式
條件格式
等會再說
我們先把函數
先把它給做出來
好
下一個步驟
我們需要去做的
也就是
大家可以看到
這個是我們的數據
起初的時候
其實我們只有
這樣一份數據
我把這份數據把它給複製出來
好
大家可以看到
這邊總共
我們是從2023年的1月份
1月1號
然後每一天
它都會有
它的這樣一個淨值
對吧
它可能不會直接去統計到你的收益
收益需要你自己去算
淨值每一天
每一個項目
它的淨值
我們都是知道的
好
接下來
我需要在當前這個表格
它的基礎之上
還要去做一些相關的延伸
什麼意思
首先
淨值
我們所謂的收益計算
其實是淨值
與上一天的淨值之間的一個差異
所以上一天淨值
淨值
好
這個應該如何去做
各位
首先
我們來搞清楚一件事情
先增加一些表格邊框進來
好
選擇可以把網格線對它去進行取消
你想嗎
如果現在我們想要去算出它的收益
對吧
收益可以寫在旁邊
收益它等於什麼
收益它是不是應該是等於
比如說在1月2號
因爲我們是1月1號買進的
1月1號買進的項目的話
1月1號
它的淨值
我想要把它給放到1月2號來
然後
1月2號的淨值
去減去1月1號的淨值
這個纔是叫做
對吧
它的收益
對不對
所以應該是這個樣子去做
但是
現在我們的問題是
我們如何在1月2號
把起初第一天初始日期的淨值
把它給調取過來
這個
你可以來使用到一個函數
叫做xlookup函數
有好多同學可能會使用到lookup函數
然後去查找最後一個
沒有必要
這個位置已經更新了很多新的函數
所以你可以使用到xlookup函數
選擇
去查找項目1
在哪個位置去進行查找
在這個範圍當中去進行查找
把第一個CR來做一個絕對引用
返回區域來自於淨值列當中
把第二個DR去做一個絕對引用
好
然後你可以往後去進行跳轉
它有一個查找模式
是從最後一項
到第一項去進行
逆序的查找
所以我們選擇了它
然後選擇給到確定
好
然後再去做一個雙擊下落
好
你自己觀察一下
是不是我就可以把
這個項目一上一次
它的淨值把它給放過來
對不對
好
你再看
如果是到1月3號的時候
我就可以把
1月2號它的淨值
把它給放到1月2號
它所在的位置
你看
所以這個樣子的話
我們就可以把它給錯開來進行擺放了
對嗎
第一天當然它是沒有這個收益的
所以第二天我們可以來進行一個計算
選擇等於
選擇什麼
選擇
用當前的淨值
去減去上一天的淨值
然後再去做一個雙擊下落
這個也就是我們所謂的收益
對不對
好
收益已經有了之後
我們需要說把1月2號這一天
它所有的收益
對吧
對它去進行求和
然後求和之後
把它給放在
1月
2號
它所對應到的格子當中
對不對
所以這個時候就是一個比較簡單的函數
三目一副參數看好了
等於三目一副參數
選擇到剛纔這個表格中間的收益這一粒
F4決定用它
然後我們再選擇條件去
這個位置條件它只和日期有關係
所以選擇到日期系列當中F4決定用它
然後條件是什麼
條件也就是當前的日期
所行而不所立
然後
括弧回來
確定
然後再把它往後去做一個拉扯
第一就是第一天它是沒有收益的
好
然後你再選擇到這一行
選擇control c
control v
然後這個時候你會發現
注意它會一直鎖在這個行它所在的位置
所以你之前在做的過程中間
你是考慮說我
往右邊去拉
因爲隔行你是沒有辦法去進行拉扯的
所以你就沒有必要去做鎖定了
直接就給到一個
引用就行了
再選擇往右側去做拉扯
再選擇到這一行
control v
這個樣子的話
我們就可以得到最後的結論
你看當你再往下去做粘貼
它所對應到的所有的單元格
它都是上方的單元格
對吧
所以這個樣子的話
我們關於每一天它的收益
就可以把它給放在當前的表格中間了
你看
這一步做完了之後
接下來我們還有一些需求
什麼需求
就是說我想要去找到我的歷史的
因爲我在這邊給大家去做了一些相關的計算
我想要去做總盈虧的情況
所以總盈虧應該如何去進行計算
首先我們可以到這邊來使用到
unique函數
選擇到項目列當中
對吧
總共就會有12個項目
好12個項目
然後它的初始盈虧是多少
初始淨值
初始淨值是多少
初始淨值就應該是等於對吧
你當然直接可以懇求V把它給放過來了
對吧
或者你可以用V的二盤數
這種形式去查找了
對吧
都可以
這個無所謂了
直接我選擇把它給複製過來
好
然後我們的除了會有初始盈虧的話
我們最後一天
最後一天淨值
對不對
最後一天淨值應該如何去算
最後一天的淨值
看好了
首先最後一天的淨值
它其實它就是等於我們整個表格中間
3月15號是我們最後一次
它所統計的內容
對吧
所以我想要把3月15號這一天
它所對應到的淨值把它給放過來
所以這個也是非常的簡單的
怎麼樣
Xlook二盤數再次把它給用起來
選擇到什麼
選擇到查找當前項目
在哪個位置去進行查找
在項目列當中去進行查找
返回哪一列
返回禁止列
是不是同樣我們需要逆序從下往上去進行查找
對吧
選擇給到-1
然後再去做一個雙擊下拉
這個樣子的話
我們可以得到最後一天的禁止
最新的禁止
對嗎
它的一個歷史收益
就等於我們的最後一天禁止
去減去初始禁止
對嗎
然後再去做一個雙擊下拉
再選擇最後一行
加上等於號
就可以算出我們整個的這樣一塊球和它的情況
這個樣子的話
你就可以看到
我們歷史的一個收益情況
其實它是負值了
好吧
所以我們可以選擇把初始值把它給放過來
可以把它給放在這個位置
再選擇可以把最後一天的禁止
然後把它給放過來
或者是最新禁止
你可以這樣去理解
最新
好
然後還有一個就是我們的歷史收益
對吧
可以把它給放過來
然後同樣你可以選擇用格式刷
把它給刷一下
好
然後我們再選擇
因爲我需要下方也需要去做邊框部分了
所以你就可以把它給引用
直接放在我們當前的表格中間
搭配這個日曆去進行同步的滾動觀看
所以初始禁止它就應該等於到它
然後最新禁止就應該是等於到它
然後歷史收益就應該是等於到它
對不對
所以包括我們應該是選擇了
它們選擇對它居多或居中
好吧
好
還有一個問題就是我們當月的一個收益
就比如說現在我是在1月份當中
我想要計算一下
1月份它整個的一個收益應該如何去做
好
我到這邊可以來寫上叫做什麼
叫做當月收益
當月
好
然後這個值它應該如何去算
首先當月它就是2003年的1月份
所以我可以到這邊來寫到
等於到這邊的1月份
如果我到這邊的切換給到了2月份的話
那這邊就會變成是2月
對吧
好
那我可以到這邊來寫到
叫做當月收益
首先這個當月收益
它是不是應該是我們整個這個收益列
對它去做求和
對嗎
對收益列去做求和
然後是基於當前的項目
以及它是在2月份當中
所以這個就是一個典型的
some product函數
它的一個製作方法
所以接下來
我們可以直接來寫到這個函數
等於
month函數先來做判斷
選擇什麼
選擇到日期這一列當中
對吧
好
F4絕對用它
日期它應該是等於month函數
日期調取之後
應該是等於到當前的這個月份
對不對
確定
好
然後這個只是我們的第一個判斷條件
括弧
再去乘以什麼
當然這個L1單元格也需要去做絕對引用
然後再去乘以什麼
乘以項目系列當中
它應該是等於我們的當前項目
對不對
好
然後我們可以再來
這個位置就不需要鎖定它了
好
再去乘以
乘以什麼
乘以我們的收益這一列
對不對
所以在選擇到收益這一列
F4絕對用它
然後在前方來使用到some product函數
括弧
確定
這個也就是我們
當月每一個項目的它的一個收益情況
然後我再對它去做一個求和
這個位置
out加上
等於號算出
我們當月其實應該還
盈利了
7600塊錢左右
所以我們可以選擇把這個值
然後把它給放在這個位置
等於選擇等於到它
等於到它
好
這幾個值都已經有了之後
當然你可以對它去做美化
包括你可以選擇把它的表頭
變成是自己比較喜歡的顏色
選擇把下方的值的部分
如果是負值的話
可以去做個條件格式
怎麼樣都可以的
這個根據大家自己的實際情況來
好吧
這一塊做完了之後
其實還有一個比較核心的部分
也就是說
你想
如果現在我們是在
比如說是在1月份當中
1月份當中
你想
1月
之前的內容
我就不會讓它顯示在中間
對吧
這個它是屬於2022年的
是2月份的內容了
對不對
然後你再往後走
你看這個位置
它也是2月1號
2月2號
2月3號
所以如果我選擇滾動到1月
我就不想看到
和本月無關的任何內容
對吧
所以這個應該如何去做
好的
首先看好了
你應該是選擇這兩行當中
因爲它跟我們純粹的去做日曆
是不一樣的
純粹的日曆就比較簡單
但是我們現在此時此刻
它其實是隔行的
中間我們是需要去填充其他函數的
對嗎
所以你就選到前面兩行
因爲它如果可能會存在上個月
或者是下個月
它可能存在的函數
應該是第一行當中
以及最後兩行當中
它可能會存在下個月
這個位置它可能會存在上個月
對嗎
所以我們再選擇到
這兩行當中
選擇條件格式
新建規則
使用公式
然後公式比較簡單
等於month函數
month函數
然後選擇到當前的單元格
鎖行而不鎖列
當它month
因爲它其實是日期了
對嗎
你比如說這個26號
我用month把它給調取出來
它其實它的月份
它應該是12月份了
所以當它如果是不等於
我們當前的月份的時候
我則會給它填充一個顏色
選擇給到灰白色
再選擇把字體顏色
同樣給到灰白色
好吧
我們再來看一下確定
確定你看
這個樣子的話
非1月份的部分
它就由於文字顏色
它變成了是灰色
然後包括頂端
它也就是我們整個的背景
它也變成了是灰色
所以當然字體就看不到了
然後我們再選到這個區
你只不能夠說選擇整個這個區
你只能夠單獨的去選擇到這兩行
然後單獨的去做數字選擇
公式
然後選擇同樣等於month函數
month哪個
month這個單元可鎖行而不鎖列
當它如果是不等於1月份的時候
我則同樣給它填充剛纔的一個填充顏色
確定
然後最後一行也是一樣的道理
所以我們再次去選擇單獨去做
就不會出現錯
不然的話
大家自己可能理頭緒都理不清楚了
再選擇到這個單元可鎖行而不鎖列
然後當它的month
它不等於當前月份
我們再選擇同樣的設置方法
選擇同樣的字體的顏色的設置方法
確定
好
這個樣子的話
我們可以再來看一下
你看
1月份
2月份
3月份
4月份
當我們在進行滾動
它都可以滾動到
就是它所對應到的這樣一個內容
好吧
所以說實話
這個也只是給大家一個
如果你要去做類似的表格的一些基本思路
當然你可以用這種思路
然後結合你自己專業的
然後去做很多其他的相關分析
好嗎
整個這一招咱們就講到這個位置
大家可以下載課件之後
好好的去練習一下
