本文作者:竺蘭
本文編輯:竺蘭
大家好,我是懂點 Excel、正在努力學習的小蘭~
最近隔壁的小王接到了一項任務,需要統計 2019 年至今的專案完成情況,為是否要繼續開發新專案做評估,比如直播帶貨……以下是部分表格:
要求是:
已完成的專案輸入「OK」,單元格標記為黃色;
將已完成的專案標灰;
給已到期未完成的專案標記為逾期,並計算逾期天數。
如果覺得文字描述不夠直觀,可以看看這張圖
好了,要求都看懂了吧!
接下來,我們來幫他想想,該怎樣完成這項任務~
排除手動,用
自動標記
,我們要做高效職場人!
那麼,該如何操作呢?繼續往下看,答案馬上揭曉~
自動標記單元格顏色
這個操作,懂點 Excel 的同學應該都知道怎麼做!
不信,來看看!
選擇 E 列;
在【開始】選項卡下找到【條件格式】-【突出顯示單元格規則】-【等於】;
在彈出的對話方塊中輸入公式:
填充顏色!
是不是都學會了?
接下來,我們來看看如何
自動標記整行顏色
:
自動標記整行顏色
要實現這一操作,要用的方法,和自動標記單元格顏色有些相似,都要使用【條件格式】功能。
不同的是,這裡需要【使用公式確定要設定格式的單元格】,不過不會用到公式,超級簡單!
小白也可以輕鬆做到!
一起來看具體操作步驟吧!
選中 A1:G20 單元格區域;
在【開始】選項卡找到【條件格式】-【新建規則】;
在彈出的視窗中,選擇【使用公式確定要設定格式的單元格】,並在下面的對話方塊裡面輸入公式:
像上文一樣,設定「格式」-【確定】;
調整一下【條件格式】的規則順序,
將「自動標記單元格顏色」的規則放上面。
選中 A1:G20 區域,單擊【條件格式】-【管理規則】;
在「條件格式規則管理器」中選擇「單元格值」,點一下,確定!結果如下圖:
設定完成,我們來看看效果圖:
自動標記逾期
計算逾期天數
接下來,需要完成第 3 點要求:
自動標記逾期,並計算逾期天數。
這裡需要用到兩個比較簡單常見的函式:IF 函式和 TODAY 函式。
首先,我們先來算下「逾期天數」;然後,根據逾期天數和是否「OK」來判斷是否「逾期」。
操作步驟:
選中 G2 單元格,輸入以下公式,下拉填充:
公式含義:
如果 E2 單元格中標有「OK」,則當前單元格為空;否則,返回今天和 D2 單元格之間的天數差。
其中,
如果為負數,就是指任務還未到期。
PS:「開始日期」和「截止日期」必須是標準日期格式。
選中 F2 單元格,輸入以下公式,下拉填充:
公式含義:如果 G2 單元格不為空並且 E2 單元格沒標「OK」,就顯示「逾期」,否則顯示為「空」。
到這裡,我們就全部設定成功了!
最終效果圖:
總結一下
我們來回顧一下,本文一共講了 3 個小技巧:
自動標記單元格顏色;
自動標記整行顏色;
自動標記逾期,計算逾期天數。
PS:以上 3 個小技巧也可以拆分來用哦~
本文作者:竺蘭
本文來源:秋葉Excel(IDexcel100)和秋葉一起學Excel,每週推送影片+圖文教程,助你輕鬆掌握Excel函式、圖表、透視表……一起成為辦公效率達人!