Excel任務進度自動化模板(雙擊自動打卡+條件格式的精妙變色法),真的簡直了!高級!

Urin的自习室
19粉絲
大家好
這一張呢
我們來講一個新的智能化的打卡模板類型啊
比如說這邊總共會有15個任務
然後呢
每個任務需要完成6個階段
也就是完成100%啊
這個中間的會有以下的一些功能
比如說我們選擇到某一個單元格的時候呢
你只需要去雙擊選擇這個單元格
然後呢
它自動的就可以去劃勾
好
譬如說我們如果是完成比例超過60%的話呢
就會顯示大家所看到的這個綠色
那如果現在我們完成的是30到60之間的話呢
它就會顯示成是這種橘色
如果你完成是小於這個30以下的話呢
就會顯示成紅色啊
所以這種打卡模板應該如何去進行製作呢
這個表格呢
它有兩個核心部分
第一個核心部分的就是我們如何選擇這個單元格去進行自動的勾選
第二個部分呢
就是如何去做這種可變的色調
並且呢
我沒有使用到Reptile數之類的
我直接啊
就是使用到條件格式去做的啊
所以這兩個關鍵點都是大家可能不瞭解的啊
所以我們一步一步給大家來進行講解啊
首先大家可以看到這個呢
是我們最開始的這個數據啊
總共會有15個任務
總共會有6個階段
然後呢
我們選擇到這個範圍當中
然後可以選擇去添加一個邊框
好
然後呢
我們再選擇可以把視圖當中的網格線去進行取消
然後可以稍微把它給調小一些些
我們先來解決的是第一個問題啊
第一個問題就是如何雙擊這個單元格可以去進行打卡
注意
如果你使用到這個功能的話呢
你需要選擇到文件選擇到另存
把當前的這個表格呢
然後把它給另存成爲是XLSM格式
XLSM格式啊
因爲我們會要使用到那麼一丟丟的VBA啊
然後呢
再回到當前的表格當中
當然了
有很多同學呢
比較習慣的做法呢
是到中間去寫1對嗎
然後呢
用自定義格式呢
把這個1呢
把它給換成是一個勾的一個選擇
這個之前我們講過
那今天呢
我們所講的呢
就相對會比較智能一些
首先啊
我們可以選擇到開發工具選擇到value basic
然後呢
選擇自己啊
可以來找到當前這個表格
它的名稱啊
是叫做sheets2對嗎
所以呢
我們就選擇到sheets2
不需要去選擇什麼插入模塊
好
然後呢
接下來啊
我們可以選擇在這個sheets2當中呢
VBA框架當中啊
選擇把通用切換成爲是worksheets
然後呢
這邊會有個叫做selection change
這個selection change呢
就是你選擇單元格
它就可以發生變化
這種模式
它是單選的模式
那這種模式呢
它其實最後所起到的效果是這個樣子
就是你選擇某一個單元格
這個單元格
它立即就會變成一個勾選
那但是如果我有的時候呢
在查驗這個單元格的時候
可能不會想要去做勾選
所以我們想要說雙擊
然後呢
它纔會出現這種勾選
所以呢
我們就會用到剛纔我們在這個worksheets當中的
另外一個屬性啊
叫做before double click
選擇到這個屬性
然後呢
再選擇把下方的這個部分可以去做刪除
好
那接下來呢
我們在中間去寫一些比較簡短的
比較簡單的代碼啊
首先呢
我們可以來寫到
如果target
大家注意到一個問題啊
這個target是什麼意思呢
就是我們現在此時此刻所選擇的單元格
但是你想嘛
我如果想讓它去進行勾選
我不能說我選擇到第一單元格
選擇的第三單元格
它都會去觸發這個勾選
所以呢
我們如果是想要去觸發勾選
它其實就是在一個比較有限的數據範圍當中
對吧
也就是任務1到任務15
這個範圍當中的6個階段
這個框架當中呢
去進行一個滿足條件的雙擊勾選啊
所以呢
如果target.row
意思也就是說
我們所選擇的單元格
它的行
它要大於等於現在呢
是第6行
對吧
所以呢
大於等於6
同時呢
這個target呢
它的row呢
它還得小於等於多少呢
因爲我們下方不是有這個最後是15個項目嗎
所以呢
你可以寫到15
除此以外呢
我們還需要啊
end
然後再來給到一個下滑線
當然你可以連續往後面寫啊
或者呢
可以去做一個換行
然後呢
可以來寫到target.colon
它應該要大於等於3
就是第3行嘛
第C列嘛
對吧
3
然後呢
同時呢
target.colon
它還得小於等於多少呢
你看啊
你自己可以數一下
這個是第3行啊
第3列45678
它必須小於8啊
所以呢
小於8
好
如果滿足這個條件的點
我們則會執行什麼東西
好
首先啊
大家注意啊
呃
我用的方法是比較簡單的
當然大家如果是比較熟悉的話呢
可以使用到這個intersect
這種方法去做啊
那在這邊呢
我們可以來寫上
我們還需要去做一個判斷
如果
譬如說這個單元格
它現在是空值
對吧
所以呢
如果target.value呢
它等於空值
點我們則會執行什麼
如果它等於空值
當我們在雙擊這個單元格
那它就會出現勾嘛
如果它是已經有勾的情況下面
我們再雙擊這個單元格的話呢
它就會變成是空值嘛
所以呢
如果它爲空值
我則會寫到啊
叫做target.value
它等於什麼呢
等於application
我們可以使用到一個函數啊
叫做.worksheet function
然後呢
點啊
叫做uniture
uniture呢
你可以來寫到10004
代表的就是勾選
好嗎
好
那如果它不爲空值的話呢
else
那我們則會寫到叫做
target
啊
target
.value呢
它就等於空值
對吧
and if
閉環啊
我們的中環的這樣一個if
然後呢
還需要去做一個and if
去end我們最開始的這個target
它的行列
它的限定條件範圍啊
所以它的意思其實很簡單的
各位不要有
就是覺得是微編的
就覺得特別難
其實它的意思就是說
首先我們可操縱的空間呢
其實就是在
那第6行到第20行之間
對吧
同時還需要滿足它是c到h列當中
在這個範圍當中
然後呢
你再看
如果這個單元格是空值
那我雙擊的時候呢
它就會觸發這個函數啊
就是uniture函數
然後呢
雙擊你看勾選就來了嘛
然後呢
如果現在啊
它已經有內容了
對吧
所以如果它有內容
那我就不會去執行這一段
就和if函數如出一轍啊
就會執行這一段
那target.value呢
它就會變成是空值
所以當我們再次雙擊
你看它就沒有了
所以呢
你雙擊雙擊雙擊雙擊
你看這個樣子的話呢
我們就可以實現
在我們雙擊的過程中間
它就可以在啊
當前的這個單元格當中呢
去進行這個內容的輸入了
好吧
對20啊
這個位置是20
不是15
好
然後呢
你看你看好
所以啊
這個就是我們第一步的操作
那接下來呢
我們再來做第二步操作
叫做
呃
完成進度
對吧
完成進度
好
然後呢
這個完成進度的計算就會特別簡單
等於count a函數
因爲這個它其實就是非空單元格嘛
其他的部分它都是空單元格嘛
所以呢
你選擇到這一行當中
括弧回來
你看計數就是1
如果再雙擊呢
就變成了是2
對嗎
總共呢
我們會有6個項目啊
所以呢
你可以使用到它呢
去除以6
或者呢
同樣使用到count a函數
選擇到我們的這個表頭系列當中
啊
F4決定這個區域當中
括弧回來確定
好
然後呢
再去做一個雙擊下拉
同時把它的格式啊
把它給更換成爲是百分比格式
精確到小數點後面移位
好
然後這個色條呢
因爲如果你直接在它的上方去做色條的話
就會出現一個問題
就是這個數值呢
它會和色條呢
就是整個摻雜在一起
就感覺看上去不會那麼好看
所以你看我最後所做好的效果當中呢
我其實是分成兩列啊
給大家去做的
這一列我們所做的是色條
這一列呢
我們所做的僅僅只是這個數值
它的顯示而已
好
所以呢
我們在這個位置就可以選擇到呢
後面的這兩列當中呢
去做一個這個邊框
然後呢
可以把上方呢
去做一個合併單元格啊
最好呢
自己是可以來選擇到整個頁面當中
然後把它的字體呢
變成是自己比較喜歡的字體類型
好
然後這個位置的顏色
你也可以把它給變成是這種藍色啊
再次合併單元格
好
然後這邊的數值呢
可以稍微把它給做一個弱化的處理
看上去會更加精巧一些
寬度呢
可以再去做個調整
好
那接下來我把這一塊呢
把它給做一個複製啊
大家等會我可以去參考一下這一塊
首先做完這一步之後呢
我們需要去做一個色條
色條其實說實話
你們自己在做的時候
你們就選擇這個區域當中
對吧
然後呢
選擇到開始條件格式
然後呢
選擇給到色條
但是這種色條呢
它是單一格式的
好
你再看一下我們這邊的做法啊
如何去做一個可變的色條呢
啊
首先我會選擇後面單元格
它等於1去減去33%
這邊就是67%
對嗎
好
你再看啊
這個位置呢
這個技巧呢
大家確實是需要好好的去理解一下
你看啊
首先呢
我如果要去做一個可變色條的話
我會先選擇把這個單元格呢
它的顏色把它給變成是一個綠色啊
變成是綠色啊
稍微淺一些些啊
綠色
你再看
如果現在這個位置是66%的話
那我是不是可以去做一個反向色條呢
什麼叫做反向色條啊
你看啊
我選擇到這一類當中
然後呢
可以來選擇到條件格式
選擇給到一個色條
你隨便選擇一個顏色
好
然後呢
我們再選擇對剛纔的這個色條呢
去做一個調整
然後呢
我會選擇把它給更換成是數值
那0是最小值
然後呢
1是最大值
對嗎
好
然後我會選擇給到
那顏色我就暫且呢
就先給到大家所看到的這個藍色啊
然後呢
給到這個邊框就無邊框了
那通常情況下面我們去做這種色條
它都是從這個單元格的左邊
往右邊去做延伸
對嗎
好
但是如果你現在選擇從右往左呢
去做延伸的話
你就會發現它會有一些不太一樣
好
那你先假想一下
就是現在這個66.7%呢
它是從右邊往左邊去做的延伸
對不對
所以你可以認爲現在我們所看到的這個位置呢
它是藍色
那如果現在你把它給變成是白色的話呢
你再看啊
選擇把它給變成是白色
是不是就是我們想要的那種色條它的效果
你看現在你可以不用去參考剛纔這個右邊的這邊了
我們現在看到33%
它是不是加上67%
正好等於100%
同時33%
它正好是現在我們所看到的這個綠色
它進度條它所對應到的位置呢
對吧
所以我們把單元格底色把它給填充成是綠色
然後再去做一個反向的色條
然後用1去減去前方的單元格
它們倆加起來等於100%
所以前面這個綠色的部分
它所反饋的其實就是前面的值
那這個83.3%
跟這邊它是同樣的道理
對嗎
好
那這一步做完了之後呢
這個反向色條啊
我們就有了
然後你可以來選擇
就是把這個中間的數值可以去做隱藏
所以呢
選擇到數值格式自定義啊
可以來輸到一個分號
就可以去做隱藏啊
分號就可以去做隱藏
你最好呢是輸入三個分號啊
選擇確定
那這個時候大家再看一下
16.7%
33.3%
那如果我們把這邊的整個全部把它給劃勾之後
它就是100%
好
所以現在其實我們所看到的色條
注意它是單元格底色
而我們之前所做的那個反向色條
它充當了我們的一個33%
右側的這個部分啊
所以這個就是它的邏輯所在
好
你再看
那現在我們需要滿足到的邏輯呢
是這個樣子的
如果它是小於35%
我就會要給到紅色
對嗎
所以這個時候呢
你就可以對單元格底色去做加工了
所以呢
選擇可以來給到新建啊
規則當中的選擇到前方的單元格
並且呢
鎖力而不鎖航
當它如果是小於等於0.3
那我則會給到一個顏色
給到紅色
對嗎
所以呢
選擇把它給變成是紅色
當然這個顏色
如果你覺得它的顏色有一些濃厚的話呢
你可以選擇把這個顏色啊
稍微把它給調淡一下
在這個紅色系當中呢
去進行選擇啊
因爲剛纔我們所看到的那個紅色呢
確實還是有一點點太深了
確定確定確定
好
這個時候你就會發現是不是紅色
是不是紅色
對吧
好
你再看
我們在選擇到這個系列當中
選擇條件格式
選擇新建規則
選擇啊
來給到
如果它是小於60
應該怎麼辦呢
所以呢
你再次選擇到前方的單元格所列而不所行
它小於等於0.6
那我則會給到一個橘色的填充啊
確定確定好吧
好
這個時候你又會發現一個問題啊
它這個中間它都變成了金黃色
對吧
所以這個時候你要注意到它的牌的一個先後關係
你應該說把它把它給放到最頂端
然後再選擇把紅色放在頂端
再選擇把這個最後的金黃色把它給放在最下方
那這個樣子的話呢
我們再來看一下
各位
所以這個樣子的話
你看33%就金黃色
對嗎
如果是30%以下的話呢
就會給到紅色
那如果它是100%
它就是綠色
而這個綠色
它不在於說剛纔我們所做的條件格式
因爲整個單元格底色填充它就是綠色啊
所以呢
這個就是它的技巧所在
你看
所以這個位置會有一些些繞啊
所以我希望大家呢
最好呢
是跟着我的這個視頻呢
然後一步一步的去練習一下
好嗎
然後練習完了之後呢
其實你可能瞬間就明白了
好
然後呢
我們還需要去做一個類似於大家所看到的總完成進度
對嗎
所以呢
我把這個總完成進度呢
把它給貼過來
寬度可以去多拉寬
同時呢
你這個主題是什麼呢
你也可以把它給複製過來啊
那這個主題呢
你們自己呢
可以去根據字的情況呢
去進行一個輸入
好
然後在這個旁邊的單元格呢
就可以去計算它的總進度
總進度應該如何去進行計算
並不是算它的均值啊
應該是說我們總共會有15個項目
那每一個項目呢
它都會有6個階段
所以我們應該說選擇等於看此A函數選擇到這個範圍當中
對吧
就是有多少個非空單元格
現在已經畫了勾的
總共會有14個
那我們總共會有多少個單元格需要去勾選呢
所以呢
在它的基礎之上再去除以看此A函數選擇到任務這一頁當中
總共會有15個任務
用15個任務再去乘以它總共會有6個階段
對吧
所以呢
使用到看此A函數啊
當然你直接去寫6啊
16乘以5也可以啊
括弧回來確定
這個啊就是我們啊
當然這邊呢
後面需要加上括弧啊
這個就是我們的完成比例啊
把它給更換成是百分比格式
精確到小數點後面一位
那後面的這個部分呢
我們就需要去做一個進度條了
對嗎
所以呢
你直接可以選擇到剛纔我們所做的某一個這個進度條選擇格式刷
然後選擇把這個單元格把它給刷一下
同時呢
因爲進度條你不可能做到一個單元格啊
因爲下面的內容會比較多啊
所以呢
我會選擇到這個系列當中選擇去做一個合併單元格
那這個邏輯呢
它和下方它是一樣的啊
所以這個位置注意啊
我們應該是在當前的這個單元格當中呢
去進行輸入
等於用1呢
然後去減去前面的25.6%
對吧
應該是這個樣子去做的啊
然後呢
我們再選擇到這個管理規則當中自己可以檢查一下
它是不是呢
這邊它是C3
它小於等於啊
這個0.3
然後呢
是C3單元格啊
然後呢
這個位置呢
它應該同樣也是C3
它小於等於0.6
然後呢
這個位置它所作用到的呢
就是第三單元格好嗎
好所以呢
現在我們可以來看一下
我們選擇來進行勾選
看啊
超過30%
你看變金黃色
對吧
好現在呢
我們可以再來選擇多拉扯一些內容啊
當它超過60%的時候呢
你看它的顏色就變成是綠色
對吧
所以這個呢
也就是我們當前這個模板它的製作方法啊
大家最好是去嘗試一下中間的知識點呢
確實也是比較多的
