14招必學秘笈讓你玩轉Excel數據

面對表格中大量數據，你是不是曾經感到束手無策？巧用數據驗證和各種條件格式，學會這14招秘笈，讓你玩轉Excel數據！

錄入數據時難免會有大量數據需要重複輸入，如果還是需要打字輸入的，那更是低效。為了避免低效的錄入，可以對需要輸入數據的區域，在「數據——數據有限性——選擇需輸入的數據序列」製作出下拉菜單，在輸入數據時，可以直接使用下拉菜單選擇數據，同時也可避免錯誤數據的輸入。

每到月底核算員工工資的時候，擔心姓名會重複，這樣就會有人得到多份工資。為此，每次都得核對好多次才放心。有沒有辦法快速識別出重複錄入呢？選中數據區域，設置數據有效性，選擇「圈釋無效數據」能讓重複數據瞬間露處原形，話說，比照妖鏡還好使。

三、數據提示輸入 防範錄入錯誤

表格同時有多人在維護，但是不同的人填寫不規範咋辦？用提示性輸入，可以防範錄入錯誤。具體操作：選中需設置數據區域，數據——數據有效性——輸入信息，填寫提示信息。設置好後，游標點擊單元格，即可出現信息提示輸入內容、格式規範。提醒規範輸入的同時，可以避免出現錯誤。

四、限制錄入重複數據

大量錄入數據時，難免因疏忽使數據重複錄入，除了錄入前提示、錄入後圈釋無效數據，在錄入時是否可以自動識別重複數據的錄入？選中數據區域，點擊數據——數據有效性，設置自定義條件為：=countif(E:E,E1)=1,設置出錯警告內容為：重複錄入！好了，現在要是有重複錄入的數據，立馬出現信息提示重複錄入。

五、使用公式製作業績圖表

枯燥的業績表怎樣來點新花樣讓人眼前一亮？善於使用原數據作為條件，將數據值大小轉換為形狀個數替代，如下面這個例子。將C列單元格數據用公式：=rept("",C3)，將數值大小作為條件替換成用「」個數代表數值大小。「」作為文字還可以直接設置字體顏色。怎麼樣，閃瞎了你的眼睛了沒有？

六、使用數據條展示銷售業績

技多不壓身，除了用圖形替代數字，再學一招用數據條展示，可實現同樣的圖表效果。選中需展示的數據區域，複製到新區域，使用「開始--條件格式--數據條」，選擇填充方式，數字就根據大小顯示對應的數據條長短表示。如果不需要數據顯示，可以在規則設置中勾選「僅顯示數據條」，結果更簡潔。

七、使用條件格式 成績變等級

如何使用條件格式將成績按區間顯示為等級？選中數據區域範圍，使用「開始--條件格式--新建規則--使用公式確定要設置格式的單元格」，例如將成績小於60分的設置為不及格並用紅色字體顯示，則需在規則中輸入公式:=A1<60、設置數字格式自定義為「不及格」、字體顏色為紅色，其他等級「及格」、「良好」、「優秀」通過類似的規則新建、輸入公式、設置數字格式、字體顏色即可。完成所有規則設置後，由於規則應用有先後順序，需將最先滿足條件的規則排在最前面，後實現的規則按順序依次排列。數字還是數字，但是已經變樣了，你看出來了嗎？

八、使用條件格式 突出顯示優秀成績

如何快速篩選排名top10並完成標記？對Excel還算熟練的小夥伴，會使用降序排序然後再對前十個數據用字體顏色、填充色。當然，這是一種好方法。但是，天下武功唯快不破，其實還有更快的方法哦。選中選取範圍數據區域，在「條件格式」下的「項目選取規則」功能，可以使用「值最大的10項」直接選取前十數據並完成標記，而且可更改為top5、top10%等篩選方式，非常方便靈活。

九、巧用條件格式 實現旋風圖效果

喜歡體育賽事的看過來，看球賽的時候經常會看到球隊數據對比，直接看數據的話又不直觀，使用圖表製作對比圖又比較耗費時間。快來試試用條件格式模擬實現數據對比的旋風圖效果吧。操作時，選中需設置的球隊數據，使用「條件格式」的「數據條」將球隊數據轉換成數據條表示，分別設置規則為「僅顯示數據條」並對放在左邊的數據條選擇圖形方式為從右到左，最後細微調整下字體、列寬等設置，使結果更加符合對稱的美感。不要問俺是啥星座，俺就是處女座的……

十、用數據條展示氣溫變化

遇到負數怎麼都拐不過彎來，更別說查看溫度變化規律了，怎麼破？從數據直接查看比較不容易查看出變化規律，使用數據條顯示能使數據變化情況一目了然。複製粘貼溫度數據到新列，使用「條件格式--數據條」設置新複製粘貼的數據用數據條顯示，同時為了用紅色代表高溫、藍色代表低溫，需要在管理規則中對數據條顏色做調整設置。為了更美觀，當然是要去掉多餘的數字了，另外必須讓正負數據條坐標居中顯示啊。額，好吧，處女座毛病又發作了。

十一、用條件格式給優秀成績插紅旗

如何完成優秀成績標記？對成績優秀的童靴，老師總是青睞有加。查看成績時也希望能有醒目的標記，幫助一眼找到。Excel中條件格式還真給提供了該功能可以完成標記的。在「開始--條件格式」下新建規則，格式樣式除了數據刻度，還可以選擇圖標，如紅綠黃信號燈、旗幟等，在Excel提供的圖標基礎上還可以自定義規則顯示，使結果只顯示標記的成績。像俺這種從沒進入老師眼裡的學渣，先讓我到廁所哭會。

十二、使用條件格式，整行突出顯示

需要突出顯示相同類型的數據，數據量較少的時候通常用顏色填充就能直接完成了。但有的時候，數據量比較多，而且判斷是否填充的條件需要變動，要是用手動直接填充的話，要是哪幾個數據條件有變動的，那得改動到累死。使用條件格式新建規則，利用規則設置，可以快速完成批量填充，而且數據條件變動的時候，會自動完成修改顏色填充，方便又快捷，而且關鍵是又可以tou lan了。快來看看是如何操作的吧。

十三、突出顯示周末日期

結合函數使用條件格式可以完成更多意想不到的功能。接下來要介紹一招用函數+條件格式標記周末日期的高級技能，擦亮眼睛看好了哦。使用條件格式新建規則，使用公式設置格式，輸入公式：=weekday($A2,2)>5,weekday函數返回某日期為星期幾，參數2表示從星期一開始為第一天(1)星期日為第七天(7)。再完成填充顏色設置後，就可以突出顯示周末日期了。

十四、用條件格式製作項目進度圖

使用條件格式還可以製作項目進度圖，實現甘特圖效果。在條件格式新建規則並使用規則設置公式：=(D$2>=$B3)*(D$2<=$C3),注意單元格相對引用方式，按F4可以切換引用方式。簡直驚呆了有木有，只要敢想的Excel都有辦法實現，還有什麼是Excel辦不到的？

版權聲明：本文是Excel技巧精選 原創動畫轉載請註明來源和微信號(ExcelSkill)

你的讚賞是我堅持原創的動力

讚賞共 0 人讚賞
推薦閱讀：