有同事問到,如果在資料表中陸續輸入資料,在眾多資料中,想要標示資料輸入不完全的記錄,該如何處理?以下圖的例子,如果生日或是組別其中一項未輸入,要以不同色彩的底色標示。
這種問題可以使用「設定格式化條件」來處理:
1. 選取資料範圍(例如:儲存格A1:D42)。
2. 選取常用[常用/設定格式化的條件]中的「管理規則」選項。
有同事問到,如果在資料表中陸續輸入資料,在眾多資料中,想要標示資料輸入不完全的記錄,該如何處理?以下圖的例子,如果生日或是組別其中一項未輸入,要以不同色彩的底色標示。
這種問題可以使用「設定格式化條件」來處理:
1. 選取資料範圍(例如:儲存格A1:D42)。
2. 選取常用[常用/設定格式化的條件]中的「管理規則」選項。
在 Excel 的工作表中,每種色彩對應一個數(參考下圖),如果想要以色彩查表取得數值再計算總和,該如何處理?
(1) 轉換數字後計算總和
儲存格B2:=VLOOKUP(A2,$F$1:$G$10,2,FALSE)
利用 VLOOKUP 函數,取得每個色彩所代表的數值,再加總即為答案。
(2) 直接計算總和
如果不想先將色彩轉換為數字,而要直接求得總和。
在 Excel 的運算中,有時你會發現類似以下的問題:
B1:0.000123456789012345
B2:1
B3:1.00012345678901
若 B3=B1+B2,觀察儲存格B3,結果好像不正確(應為0.000123456789012345)。那是因為在 Excel 中最多只能儲存 15 個有效位數的精確度所致。
在 Excel 中,如果要計算兩個日期之間的週數,該如何處理?這個題目的意思是要求二個日期之間橫跨了幾週?只要善用 WEEKDAY 函數,即可完成。
儲存格C2:=INT((B2-A2)/7)+MAX(1,(WEEKDAY(B2,1)<WEEKDAY(A2,1))+1)
複製儲存格C2,往下各列貼上。
【補充資料】
在 Excel 中,如果想要將身份證字號中的字母轉換為數字,該何如處理?(參考下圖)
身份證字號中的英文字母A, B ~ Z,分別對應01, 02 ~ 26。
儲存格B2:=RIGHT("0"&CODE(LEFT(A2,1))-64&RIGHT(A2,9),11)
複製儲存格B2,往下各列貼上。
LEFT(A2,1):取出身份證字號中的英文字母。
同事問到,在 Excel 中的一長串日期中,如何能自動標示週末假日(星期六、日)?(參考下圖)
建議使用「設定格式化的條件」來處理較為簡單,參考下的步驟:
1. 選取儲存格A2:B20。
2. 新增規則,選取「使用公式來決定要格式化哪些儲存格」。
3. 輸入公式:=WEEKDAY($A2,2)>5,即取出星期六和星期日者。
在 Excel 中,如果要根據開始點和長度,在儲存格中自動繪出對應的儲存格底色(參考下圖),該如何處理?只要使用「設定格式化的條件」即可解決!
參考以下步驟:
1. 選取儲存格D1:R11。
2. 在「設定格式化的條件」中新增規則,選取「使用公式來沫定要格式化哪些儲存格」。
3. 輸入公式:=AND(MOD(ROW(1:1),2)=0,D$1>=$B2,D$1<$B2+$C2),設定儲存格底色為橙色。
有人問到:在 Excel 中如何計算兩組日期區間的重疊天數(參考下圖)?
日期區間 A 和日期區間 B 的相對關係(重疊狀況)可能有以下五種情形:
根據以上觀念,發展出以下的公式:
儲存格C2:=MAX(0,MIN(A2,$F$1)-MAX(B2,$F$2)+(B2-A2)+($F$2-$F$1)+1)
有網友問到:在 Excel 中,如何根據一個數值,將同列的儲存格標示某種底色(參考下圖)?這個問題只要交給「設定格式化的條件」來做即可。
本例根據A欄中的數值,將同列的儲存格設定為和該數值相同數量的儲存格底色。
1. 選取儲存格B2:K16。
2. 選取[常用/樣式]中的「設定格式化的條件」選項。
3. 新增一個規則:使用公式來決定要格式化哪些儲存格。輸入公式:=COLUMN(A:A)<=$A2。
這次來練習,在 Excel 中輸入一個數(例如45789),如何取出其中的每一位數(4,5,7,8,9)?為了說明方便,假設輸入的數字最多有5位數。
儲存格B2:=MID(RIGHT("00000"&$A2,5),COLUMN(A:A),1)
RIGHT("00000"&$A2,5):將 "00000" 串接原來的數,再透過 RIGHT 函數取出由右算起的5 個字。將不滿 5 位數者,在其左側以 0 填補。
MID(RIGHT("00000"&$A2,5),COLUMN(A:A),1):透過 COLUMN 函數指定第幾個數,其中 COLUMN(A:A)=1、COLUMN(B:B)=2、…。透過 MID 函數逐一取出各個位數。
在 Excel 的資料表中,可能一個數列中包含了沒有規則排序的一些正數和一些負數(參考下圖),例如數列中有收入和支出的資料等。如果想要以此計算正數和、負數和該如何輸入公式。
選取儲存格B1:B21,按一下 Ctrl+Shift+Enter 鍵,定義名稱:數值。
儲存格F2:=SUMPRODUCT(--(數值>=0),數值)
儲存格F3:=SUMPRODUCT(--(數值<0),數值)
上式中的「--」,可以將關係運算(<0)所得的 True/Fasle 陣列,轉換為 1/0 陣列。
在 Excel 中如果要執行排多的運算,最常看到使用 RANK 函數來操作,例如:
儲存格C2:=RANK(B2,$B$2:$B$11)
複製儲存格C2,貼至儲存格C2:C11。
還有其他方式可以來操作嗎?例舉以下三種來練習:
(1) 儲存格C2:=COUNTIF($B$2:$B$11,">="&B2)
常會有人問到如何在一個資料表中,以垂直和水平交叉方式來查詢資料,方法有很多種,例舉如下:
首先定義名稱:月分、人員、資料和報表。(參考下圖的名稱範圍)
選取儲存格A1:G11,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列、最左欄」,定義一月~六月、甲~癸等名稱。
以下介紹四種函數,分別經由儲存格J1和儲存格J2的內容要交叉查詢資料:
在 Excel 中有一個資料表(如下圖),根據這個資料表要求得「加權平均」分數該如何處理?
儲存格E2:
(1) 求得加權計分
=SUMPRODUCT(MOD(ROW(C1:C20),2),C1:C20,C2:C21)
MOD(ROW(C1:C20),2):{1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0,1,0}
C2:C21:{65,4,81,4,74,4,84,2,79,2,80,2,60,2,73,2,69,2,78,2}
在 Excel 的資料表中如果有一數列,如果要求取這些數列的平方和,你可以先求得每個數的平方,再將其加總。現在,我們來利用函數來求取平方和。(參考下圖)
選取儲存格A1:A16,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」選項,定義名稱:數值。
以下三種方式都可以在一個儲存格中直接求得平方和,大家來練習看看。
(1) 儲存格C2:=SUMSQ(數值)
(2) 儲存格C2:=SUMPRODUCT(數值*數值) 或 =SUMPRODUCT(數值*數值)
如果在 Excel 中有一個含有起訖日期的資料表,想要來計算例假日數和上班日數,該如何處理?Excel 提供了 NETWORKDAY 函數,可以輕鬆的計算出上班日數。
儲存格C2:=NETWORKDAYS(A2,B2)
儲存格D2:=B2-A2-C2+1
試試不同的做法來練習函數的運用:
儲存格C2:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<6))
在 Excel 中輸入日期是常有的事,你可能要輸入西曆日期,可能可輸入國曆日期;可能要輸入今天的日期,可能要輸入過去、未來的日期。Excel 其實很聰明,它會協助你輸入各種日期,但是也常引起一些困擾。
如果輸入YY/MM/DD,當 YY 為 00~29,Excel會認定為西曆日期 20XX 年日期;當 YY 為 30~99,Excel會認定為西曆日期19XX 年日期。參考序號(1)(2)(3)(4)。
如果 YY 超過 100,則 Excel 會認為該日期為一般文字。參考序號(5)
如果你要輸入國曆日期,則可以在日期前加上 R 或 r。Excel 會以國曆日期顯示,例如民國 XX 年日期。參考序號(6)(7)
如果輸入的日期以 0 開頭,則 Excel 會認為該日期為一般文字。參考序號(8)
如果要輸入今天的日期,只要按 Ctrl+: 鍵。參考序號(9)
在 Excel 中,當你取一個名冊時(參考下圖左),想要隨機重組這個名冊的順序(參考下圖右),該如何處理?
首先,你要藉助一個輔助欄位-亂數。
儲存格D2:=RAND()
複製儲存格D2,貼至以下各列。
為了公式說明方便,選取儲存格D2:D26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:「亂數」。
在 Excel 的工作表中有一個成績表(如下圖),如果要將成績表轉換成可列印的標籤形式(參考下下圖),該如何處理?
有許多種方式可以達到查表功能,這次主要使用 OFFSET 函數:
儲存格B1:=OFFSET(H$2,INT((ROW()-1)/3),,,)
INT((ROW()-1)/3):當往下複製時,在第1,5,9,…列,會產生0,1,2,…,可以分別抓取座號1,2,3,…的資料。
儲存格D1:=OFFSET(I$2,INT((ROW()-1)/3),,,)
有同事問到,在 Excel 中如何可以在儲存格中輸入以 0 開頭的數字時,能保留最左端的 0 (通常 Excel 如果認定數字時,會預設將左端不管幾個 0 均予以消除),其中之一的作法是先加入「`」,強迫 Excel 認定該儲存格為文字(參考下圖)。
(你也可以將其轉換為數字,Excel 會自動消除左端位數的所有 0。)
但是如果你只想消除這個「`」符號,還是要保留左端位數的 0 ,該如何處理?你可以試試以下作法:
選取含有「`」開頭的儲存格,在其他位置選取一個儲存格,按右鍵選取「123」圖示(選擇性貼上[值])即可。這些儲存格還是被視為文字,但不會顯示「`」符號。