最近在學期末的當兒,學校又開始處理下一階段的行事曆了!如果將 Google 日曆的資料轉換至 Excel 後,如何轉換為以週為單位的表格呈現。
以下圖為例,由 Google日歷取得行事曆內容,加以整理後保留一欄日期、一欄事項。要將這個清單轉換為下圖左以週為單位的表格。
先建立好以週為單位的週曆。其中,利用格式設定方式,讓儲存格只顯示「日」的數字。
公式設計與解析
最近在學期末的當兒,學校又開始處理下一階段的行事曆了!如果將 Google 日曆的資料轉換至 Excel 後,如何轉換為以週為單位的表格呈現。
以下圖為例,由 Google日歷取得行事曆內容,加以整理後保留一欄日期、一欄事項。要將這個清單轉換為下圖左以週為單位的表格。
先建立好以週為單位的週曆。其中,利用格式設定方式,讓儲存格只顯示「日」的數字。
公式設計與解析
今天又被問到這個常見的問題:在 Excel 裡的儲存格有中英文,如何將中文和英文分離成二欄?
在下圖中,A欄是中英文組成的字串,要將中文和英文分開成二欄(B欄和C欄),最快的方式應該是使用快速鍵。
1..選取儲存格A4中的中文字,貼在儲存格B4;選取儲存格A4中的;英文字,貼在儲存格C4。
2.選取儲存格B4:B16,按 Ctrl+E 鍵,產生全部中文。
2.選取儲存格C4:C16,按 Ctrl+E 鍵,產生全部英文。
在 Excel 裡,如果要比對兩者是否相同,只要透過「=」運算子,就可以輕鬆完成。但是「=」運算預設會忽略英文字大小寫的差異。如果你的比對是要將大小寫英文字視為不同者,可以改用 EXACT 函數。
參考下圖,如果要比對 chatGPT 和 CHATGPT 是否相同,則需使用 EXACT 函數。圖中也指出,文句中的一個空格和二個空格,EXACT 函數和「=」運算子,都是視為不同的。
因為用 EXACT 函數來將兩個儲存格不同者標示出來就很簡單了!
在本例中:
1. 選取儲存格A8:B14。
在 Excel 裡,如果要找出和今年裡每一天的星期幾都相同者(年曆完全一樣),該如何處理?
參考下圖,不同年份的年曆要完全相同,取決的是一年有幾天,和第一天是星期幾。
(1)計算年度有幾天
儲存格B5:=DATE(A5+1,1,1)-DATE(A5,1,1)
將隔年第一天日期減掉當年第一天,即可得當年的天數。(其中閏年和平年的天數差一)
在 Excel 裡的 COUNTIF 函數可以用於計算合於條件的數量,但是在多個工作表時,想要一個公式計算總和,似乎有困難。
以下圖為例,共有三個工作表(1月、2月、3月),如果要計算單一工作表超過80的個數。
3月的公式:=COUNTIF(INDIRECT(A5&"!A1:F6"),">80")
利用 INDIRECT 函數將字串轉換為位址,再由 COUNTIF 函數計算大於80的個數。
如果以公式:=SUM(COUNTIF('1月:3月'!A1:F6,">80"))
則會出現錯誤訊息:#VALUE!。
在 Excel 常用到串接字,要串接,只要使用「&」運算子就可以搞定。但是如果儲存格裡的日期,則在串接過程中,會將日期轉換為數值,所以串接的結果往往不是想要的結果,如何解決?
例如串接儲存格內容:王冠宏、男、2001/09/02,會得到「王冠宏 男 37136」,而非「王冠宏 男 2001/09/02」。
儲存格E4:=B4&" "&C4&" "&D4
單純使用「&」運算子來串接,結果不正確。改用公式:
儲存格F4:=B4&" "&C4&" "&TEXT(D4,"yyyy/mm/dd")
在 Excel 中可以使用下拉式清單來輸入資料,如何讓清單中的內容是不重複的內容?
本篇要示範:在下圖中,由一個資料表中取得地點的清單,先轉換為不重複的清單,再轉換至下拉式清單中。
1. 列出不重複項目
儲存格E4:=SORT(UNIQUE(C4:C25))
(1) UNIQUE(C4:C25)
在使用Excel進行數據分析時,FILTER函數是一個非常方便的工具,可以用來高效能的篩選操作。然而,在Excel的早期版本中,FILTER函數尚未被引入。幸運的是,大多數篩選任務仍然可以通過其他內建函數如SUMIF函數等來實現同樣的結果。在本篇教學文章中,我們將透過三個精選的例子展示如何巧妙地將SUMIF函數與FILTER函數結合使用,以便即使在舊版Excel中也能有效地處理數據篩選任務。
1. 篩選組別:乙(結果:366)
公式:=SUMIF(B4:B22,G3,D4:D22)
公式:=SUM(FILTER(D4:D22,B4:B22=G3))
2.篩選代碼:CC(結果:165)
2024年再過幾小時就到來,新的一年需要一個新的年曆,如何設計一個整年含有月份、日期、星期的萬年曆?
在下圖中,只要在儲存格B3中輸入年份(本例:2024),即可產生12個月的日期和星期對照表。
本篇要介紹如何一式就搞定這個工作。
公式設計與解析
因為要製作萬年月曆,所以年份要設計為變數,而1月至12月是連續的12欄。以年份:2024為例。
最近一直在做研習的講義,在 PowerPoint 的投影片中大多以文字方塊來呈現文字,想要把所有投影片中的文字提供給研習者以方便練習,這時才發現並沒有簡單的輸出方式。
例如,以下是人工智慧製圖的投影片,如果要方便使用者仿做或進行修改,就必須提供文字。而如果我把簡報另存為 PDF 檔,對使用者也是可以複製內容,只是必須一頁、一頁的單獨取字。
後來我求助 ChatGPT,它建議我採巨集指令,並且提供我 VBA 程式,一鍵即可完成。
【指令】如何使用VBA,一次取出PowerPoint簡報檔中所有投影片裡的文字?
你只要修改檔案路徑和檔名,並且在 VBA 環境中執行,即可一鍵取字。
同事問到:在 Excel 裡的 SORT 函數可以資料排序,但是如果是以一個文字形式的日期清單來排序,則會得到不正確的結果,該如何處理?
如下圖,當一月排完後,出現的是十月,再來是十一月,顯然是錯誤的。這是文字排序的結果!要如何修正?
問題解方
原來公式:=SORT(A4:A29)
修正公式:=TEXT(SORT(A4:A29+0),"yyyy/m/d")
網友提問到:要如何轉換分鐘數為「天、時、分」?
以下圖為例,總分鐘數可能超過一天的分鐘數(1440),要如何解析出天、時、分?
參考以下設計:
(1)計算天數:=INT(A6/(24*60))
利用 INT 函數計算不大於的最大整數,即儲存格A6的總分鐘數換算為天時,可以取不超過且最接近的整數。
在 PowerPoint 投影片的圖片上,當使用草繪線條時,你會發現在設定圖片框線後,會有框線和圖片無法完美合而為一的現象,該如何解決?
試著採用以下的方式來調整:
1.新增一個矩形(和原大小相同)。
2.在矩形上編輯端點達到草繪效果。
3.複製原始圖片,再點選矩形:填滿剪貼簿。
最近發現,在使用 Google Chrome 瀏覽器來儲存圖片時,預設的圖片格式被改為「.jfif」,而非原來的「.jpeg」,要如何才能改回來?
這個改變的確讓不少人困擾不已,有時習慣的操作方式,突然不同了,連帶的應用也會有所不同。
例如,在 Image Creator 中要下載一張 jpg 格式的圖片。
儲存後卻顯示 jfif 格式。
在 Excel 裡的工作表中有多個欄位的內容,分別是不同組別的資料,如何計算多組的資料中內容相同者的數量?
在下圖中分別有A組、B組、C組的內容,以下要介紹:
1. 計算A組和B組內容相同者的數量
2. 計算A組和B組和C組內容相同者的數量
3. 計算A組和B組內容相同者的數量(不包含空白)
在 Excel 裡,VLOOKUP 函數是查詢常用的函數之一,其特性是必須要以第一欄做為搜尋的條件。
如果以 INDEX 函數搭配 MATCH 函數,則可以由任意欄位查詢另一個欄位的資料。
依編號查詢姓名
儲存格B15:=VLOOKUP(A15,A3:D12,3,FALSE)
儲存格B16:=INDEX(C3:C12,MATCH(A16,A3:A12,0))
如何在 Excel 裡,將查詢結果串接在一個儲存格?
如下圖的資料表中共有三個等第,要將每個等第的姓名串接在一個儲存格中。
先定義儲存格名稱:
選取儲存格A2:C17,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:座號、姓名、等第。
輸入公式,儲存格E4:=TEXTJOIN(",",,FILTER(姓名,等第=E3))
在 Excel 裡輸入的資料看似數字,但是可能是文字型態。當你在運算時,例如執行 SUM 函數運算,會將文字型態的數字視為「0」處理。導致結果和你想的不一樣。
周邊的同仁就有這個困擾,詣問問題要如何解決?
其實就是把所有的數字和文字都「*1」或「+0」,即可以將文字型態轉換為數字型態,而且數字和文字都不會改變其數值。
網友根據這篇:Excel-解析LET函數,問到如果 Excel 中沒有 LET 函數,可以如何達到相同效果。
在此提供一方式:利用定義名稱的方式來取代 LET 函數。
例如:選取儲存格G4,先在名稱管理員中新增名稱「y」,設定:
參照到:=RANK(工作表1!F4,工作表1!$F$4:$F$13)
儲存格G4:=IFS(y<4,"優良",y<7,"尚可",y<11,"加油")
在 Excel 中要處理一個資料表,在資料表中有「類別、項目、數量」三個欄位,當你要做資料整理時,常用樞紐分析工具來幫你產生這個分析報表。
例如:在樞紐分析表中,將「類別」拖曳至欄,將「項目」拖曳至列,將「數量」拖曳至值(預設為加總)。
結果即會得到類別和項目的所有交叉統計結果。
如果你改用公式來處理:
先選取儲存格A1:C1000(所有資料範圍),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:類別、項目、數量。