在 Excel 中可以使用自動填滿或公式方式來產生各種數列和清單,
參考:http://isvincent.pixnet.net/blog/post/32233649
如果像下圖中根據第一個數來產生頭尾都要以二位數流水號來變化,該如何處理呢?
儲存格C1:=TEXT(ROW(1:1),"00")&"XXXX"&TEXT(ROW(1:1),"00")
其中TEXT(ROW(1:1),"00")的用意是:在第一列中ROW(1:1)=1,當往下複製公式時會產生 ROW(2:2)=2 → ROW(3:3)=3 → ROW(4:4)=4 → … 。
在 TEXT 函數中使用「"00"」格式,可以讓 1 ~ 9 的數字顯示為 00 ~ 09。
vincent 發表在 痞客邦 留言(4) 人氣()
網友想要驗證身分證字號是否正確,要如何使用 Excel 來幫這個忙呢?
首先要來理解中華民國國民身份證字號的編碼規則,你可以參考維基百科:
http://zh.wikipedia.org/wiki/中華民國國民身分證
其中第一碼的對應轉換字元:
有些目前已不使用:
vincent 發表在 痞客邦 留言(2) 人氣()
最近在輔導學生和教職員工參加 GLAD 的 BAP 認證時,同仁問到為何要使用「名稱」這個概念。因為大多數人在操作 Excel 時都沒有用過「名稱」,所以無法體會其用意。
以下圖中的統計表為例,要查詢季別和人員的交叉對應的內容:
儲存格D8:=INDEX(B2:E6,MATCH(C8,A2:A6,0),MATCH(B8,B1:E1,0))
公式正確的求得結果。接著來設定名稱:
選取儲存格A1:E6,按 Ctrl+Shift+F3 鍵,勾選「頂端列、最左欄」(預設),定義名稱:第一季、第二季、第三季、第四季和甲、乙、丙、丁、戊。
vincent 發表在 痞客邦 留言(1) 人氣()
有網友想要在 Excel 的工作表中複製不連續的儲存格,而且在不同的位置依原來的排列來貼上,但是似乎有些困難存在。
參考下圖,已經選取了儲存格A2, A4, A6, A8, A10,想要複製到 I 欄的平行位置。
當按下 Ctrl+C 鍵進行複製,再於儲存格I2中按一下 Ctrl+V 鍵,執行貼上動作,卻發現結果並沒有依照複製儲存格的相對位置來貼上,而是分散的儲存格變成連續了。
所以,要改變做法。
vincent 發表在 痞客邦 留言(4) 人氣()
網友想要在 Excel 的工作表中計算多個欄位的乘積,如下圖中每一個商品有A組和B組的數量,如何計算所有A組數量乘以單價加上B組數量乘以單價所得的總金額?
【準備工作】
選取儲存格A1:D11,按 Ctrl+Shift+F3 鍵,定義名稱:商品、單價、A組、B組。
vincent 發表在 痞客邦 留言(4) 人氣()
網友想要在 Excel 的一個含有日期和金額的清單中,分別計算例假日和平日之金額的平均,該如何處理呢?(參考下圖)
這個問題藉助 SUMPRODUCT 函數可以輕易達成。
【準備工作】
選取儲存格A1:C26,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、金額。
vincent 發表在 痞客邦 留言(2) 人氣()
在 Excel 的工作表中有一個如下圖的資料表,其中有些儲存格中沒有輸入數值,要如何取出每一列中有輸入數值的最左邊一個數值呢?(參考下圖)
【公式設計】
儲存格G2:{=INDEX(A2:F2,MIN(IF(A2:F2<>"",COLUMN(A2:F2))))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動產生「{ }」
vincent 發表在 痞客邦 留言(2) 人氣()
有網友想要將在 Excel 資料表中的兩種民國年的表示法,轉換為西元年的表示法,該如何處理呢?(參考下圖)c
觀察這兩種民國年的表示法,對 Excel 而言,其被認為二種「字串」,而非日期。在 Excel 中日期是一種數值,同一個日期不管以何種格式顯示,其背後儲存格的數值都是相同的。
儲存格B2:
=TEXT(DATE(MID(A2,1,3)+1911,MID(A2,5,2),MID(A2,8,2)),"yyyy/mm/dd")
vincent 發表在 痞客邦 留言(10) 人氣()
在 Excel 的資料表中一個多個項目的類別清單,有網友想要依不同類別編序號,該如何處理?(參考下圖)
【公式設計】
觀察類別清單中,已經將同類的項目集合在一起。
儲存格C2:=A2&TEXT(COUNTIF($A$2:A2,A2),"000")
COUNTIF($A$2:A2,A2):計算從第一個儲存格A2開始,到指定的儲存格之間的範圍共有幾個和儲存格A2相同的內容。
vincent 發表在 痞客邦 留言(1) 人氣()
網友想要在 Excel 的資料表中 A, B兩組找出重覆者剔除,也就是說要留下完全沒有重覆者的名單,該如何處理呢?(參考下圖)
【準備工作】
1. 將A組資料和B組資料分別複製到C欄的「合併」欄位下。
2. 選取儲存格C1:C25,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:合併。
vincent 發表在 痞客邦 留言(6) 人氣()
有網友想要在 Excel 的資料表中的一個文數字清單,將每個文數字中的數字取出並計算總和,該如何處理?(參考下圖)
例如下圖中的儲存格A2:OK231PP2L98,其中數字為2,3,1,2,9,8,其和為25。
【設計公式】
儲存格B2:{=SUM(IFERROR(MID(A2,ROW($1:$30),1)+0,0))}
vincent 發表在 痞客邦 留言(8) 人氣()
在下圖中的 Excel 工作表的儲存格A2,裡面有一些字元由空格隔開所組成的字串,如何取出其中姓名的部分呢?
觀察儲存格A2的內容,其中由一些「數字+姓名」所組成,每個數字和姓名之間由「空格」所隔開。
【公式設計】
本題我是參考其他人的做法而設計:
儲存格C2:=SUBSTITUTE(MID(SUBSTITUTE($A$2," ",REPT("*",500)),
vincent 發表在 痞客邦 留言(3) 人氣()
在下圖中是一個 Excel 的資料表,如果要計算各個日期中不含空白的不重覆資料個數,該如何處理?
【設計公式】
選取儲存格A1:A23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
思考之後,發現得設計一個輔助欄位才能順利設計公式。
vincent 發表在 痞客邦 留言(3) 人氣()
在 Excel 的一個資料表中有一個時間構成的數列,如果其分鐘數:
若為00~19者要捨去,若為20~29要變為30,若為30~49者要變為30,若為50~50者要進位至小時。基於這些條件來調整時間,該如何處理呢?(參考下圖)
【公式設計】
儲存格D2:
vincent 發表在 痞客邦 留言(3) 人氣()
下圖中是 Excel 的一般資料表,給予二個條件,其實就是水平和垂直的項目,二項都符合者所得到(十字交叉處)的結果,該如何用公式自動求得?這其實就是查表的概念。
【公式設計】
以下使用三種方式來執行查表的工作。
(1) 使用 INDEX 函數
儲存格F8:=INDEX(A1:F6,MATCH(B8,A2:A6,0)+1,MATCH(D8,B1:F1,0)+1)
vincent 發表在 痞客邦 留言(0) 人氣()
在下圖左裡有一個 Excel 的資料表,有網友想要轉換成下圖右的形式,即將相同負責人的內容集合在一個儲存格裡,該如何處理呢?
【公式設計與解析】
觀察原始資料中,已經將相同負責人的資料集合在一起了,這樣就簡化了這個問題。
儲存格F2:=PHONETIC(OFFSET($B$1,MATCH(E2,$A$2:$A$22,0),,
vincent 發表在 痞客邦 留言(3) 人氣()
網友問到:在 Excel 的工作表中有一個數列(參考下圖),如何計算數列中包含幾個特定字元(例如:05),或是數列中全部有幾個 0,該如何處理呢?
(1) 含「05」的個數
儲存格B2:=(LEN(A2)-LEN(SUBSTITUTE(A2,"05","")))/2
利用 SUBSTITUTE 函數將某個字串(本例為:05)轉換為空白,再使用 LEN 函數將轉換前後的字數相減、再除以2(因為05為兩個字),即為 05 的數量。
複製儲存格B2,貼至儲存格B2:B15。
vincent 發表在 痞客邦 留言(0) 人氣()
網友問到:在 Excel 中有一個由多個名稱為連續編號命名的工作表,如何快速取得每一個工作表的相同儲存各內容?(參考下圖)
通常,要連結某一個工作表的某一個儲存格,其格式為:='工作表'!儲存格
例如:='1'!A1,此為取得名為「1」的工作表之儲存格A1。
但是如果需要連結很多個連續編號的工作表,公式將需要一個一個的編寫,很不方便。
透過 INDIRECT 函數和 ROW 函數,即可快速完成。
vincent 發表在 痞客邦 留言(0) 人氣()
網友問到:在 Excel 的工作表中輸入一些數字(例如電話號碼),可能會遇到的一些問題,例如:
●第一個字若為0,可能會被 Excel 自動消去,輸入0930123456變為930123456。
●想要自動為電話加上「-」,輸入0930123456變為0930-123456
●改變電話格式為電話加上「()」,輸入0227481234變為(02)27481234
●想要自動為電話加上「+886」,輸入0930123456變為+886930123456。
●...
vincent 發表在 痞客邦 留言(4) 人氣()
在使用電腦建立文件,不管是 Word、Excel、PowerPoint 軟體,表格是高頻率使用的物件。在教學歷程中發現,常有人搞不清楚在表格中垂直方向、水平方向是欄(column)還是列(row)?
最近有人提及十幾年前我是如何教他們的:參考下圖,看中文字凸顯的一筆劃就知誰是垂直、誰是水平了!這樣你也記得下來嗎?
在 Excel 中位址以「欄名列號」來表示,所以稱A欄、B欄、...,稱第1列、第2列、...。
vincent 發表在 痞客邦 留言(7) 人氣()