在 Excel 中有一串數字(如下圖左),如何以公式篩選出不同位數的數字?操作前先執行名稱的定義:
編號:儲存格A2:26;數字:儲存格B2:B26。
儲存格D2:
{=IFERROR(INDEX(資料,LARGE(IF(LEN(數字)=COLUMN(A:A),編號,FALSE),ROW(1:1))+1,2),"")}
複製儲存格D2,貼至儲存格D2:G21。
原理:
在 Excel 中有一串數字(如下圖左),如何以公式篩選出不同位數的數字?操作前先執行名稱的定義:
編號:儲存格A2:26;數字:儲存格B2:B26。
儲存格D2:
{=IFERROR(INDEX(資料,LARGE(IF(LEN(數字)=COLUMN(A:A),編號,FALSE),ROW(1:1))+1,2),"")}
複製儲存格D2,貼至儲存格D2:G21。
原理:
在 Excel 中,針對各班班級幹部的資料表(如下圖),來練習INDEX、MATCH、OFFSET函數。
如果要產生下圖的查詢結果,該如何處理?其中班級名稱和幹部的名稱要用下拉式清單來挑選,而姓名則是以公式來查詢。
(1) 定義以下三個名稱
班級:儲存格A2:A29;幹部:儲存格B1:H1;資料:儲存格A1:H29。
在 Excel 中取得學生量測身高和體重(參考下圖)(虛擬數據),如何計算學生的BMI值和判定體位,如果要告訴學生適當的體重該如何計算?
(1) 因為BMI值為「體重(KG)÷身高(M)的平方」,所以:
儲存格G2:=F2/((E2/100)^2)
(2) 根據下圖右側的BMI範圍對照體位表:
儲存格H2:=LOOKUP(G2,$K$2:$L$7,$N$2:$N$7)
(3) 根據體重來求出適當的體重
這次要練習的查表動作,需要較多的公式,感覺會較難,所以要有耐心和毅力才能有助於學會。
(一)
在 Excel 中有一個全校班級幹部的摘要表(如下圖),如何自動轉換至學生名單中註記幹部名稱(如下下圖)?
儲存格F2:
最近被問到:如果取得一個一年級選組後的報表,如何針對各類組的男、女生人數和各班的選組人數,製作一個摘要表?(參考下圖)
這兩個工作只要交給SUMPRODUCT函數即可解決:
儲存格I2:=SUMPRODUCT(--($E$2:$E$484=1),--($F$2:$F$484=$H2))
--($E$2:$E$484=1):判斷E欄中是否為「1」(男生)的 True/False 陣列,其中「--」乃是將 True/False 陣列轉換成 1/0 的陣列。
--($F$2:$F$484=$H2):判斷F欄中是否為「1」(第1類組)的 True/False 陣列,其中「--」乃是將 True/False 陣列轉換成 1/0 的陣列。
SUMPRODUCT函數會將這兩個陣列相乘,再將這些 1/0 的結果加總。(其實是兩者條件皆成立時,相乘結果才會為1,也才會被加總。)
在 Excel 中取得一個學校社團的基本資料表(如下圖),本例要根據學生選社的結果(如下下圖),來產生學生選社的結果報表,並進一步分析。
首先將社團基本資料表定義一個名稱:社團。
根據以上的兩個資料表,現在要來產生如下圖的選社結果。本次以VLOOKUP函數來做為查表的工具。
VLOOKUP:http://office.microsoft.com/zh-tw/excel-help/HP010343011.aspx (參考微軟網站的說明)
在 Excel 中常會使用INT和TRUNC兩個函數來轉換數值為整數。而TRUNC 函數與 INT 函數非常相似,兩者都可以將帶小數的數值(實數)轉換成整數,但TRUNC 函數可以指定某個位數以下的部分全部移除,而 INT 函數則是找尋最接近但不大於原數值的整數。
這兩個函數的此二函數唯一的差別是在處理負數時,「可能」產生差異。而正數部分,結果會完全相同。(參考下圖)
觀察以下三種變化:
儲存格B2:=TRUNC(A2,0)和儲存格C2:=INT(A2)
儲存格D2:=TRUNC(A2,1)和儲存格E2:=INT(A2*10)/10
儲存格F2:=TRUNC(A2,2)和儲存格G2:=INT(A2*100)/100
在 Excel 中取得一個成績資料表,現在要根據加權平均成績,來以「無條件進位」修正成績,及如果成績達58和60之間者均以60計,該如何處理?(參考下圖)
(1) 計算加權平均成績
儲存格G3:=SUMPRODUCT(B3:F3,$B$1:$F$1)/SUM($B$1:$F$1)
加權平均=(國文X4+英文X4+數學X4+社會X3+自然X3)/(4+4+4+3+3)
(2) 以無條件進位來修正成績
儲存格H3:=ROUNDUP(G3,0)
在 Excel 中,如果想要產生多個範圍的亂數,例如:產生1~40, 101~140, 200~240的亂數,該如何處理?
因為這是三組範圍為0~40的亂數,試試以下公式:
(1) 儲存格B2:{=LARGE(ROW($1:$40)+{0,100,200},INT(RAND()*120+1))}
或
(2) 儲存格B2:{=SMALL(ROW($1:$40)+{0,100,200},INT(RAND()*120+1))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。將儲存格B2複製到儲存格B2:F4。
在 Excel 中的 REPLACE 和 SUBSTITUTE 函數都是用來取代字串中的某些特定文字之用,其用法有那些差異呢?(參考下圖)
REPLACE 函數主要是根據指定的字元起始位置,指定被取代的字元數,然後以新的字串來取代。
(1) 儲存格E2:=REPLACE(A2,5,7,"_^_")
在儲存格A2中的字串中,由第5個字元開始,一共7個字元,以「_^_」取代。
(2) 儲存格E3:=REPLACE(A3,7,4,"999")
(3) 儲存格E4:=REPLACE(A4,11,5,"Word")