網友問到:在 Excel 中如下圖的資料表中,如何在挑選日期後自動計算每日的數量總和?
參考下圖,當指定一個項目時,希望能計算各日的總和;當指定一個日期時,希望能計算所有項目的總和,該如何處理?
【公式設計與解析】
1. 為儲存格範圍定義名稱
網友問到:在 Excel 中如下圖的資料表中,如何在挑選日期後自動計算每日的數量總和?
參考下圖,當指定一個項目時,希望能計算各日的總和;當指定一個日期時,希望能計算所有項目的總和,該如何處理?
【公式設計與解析】
1. 為儲存格範圍定義名稱
有網友問到在 Excel 中跨工作表使用SUMPRODUCT函數的問題。參考下圖:
假設在工作表3的 SUMPRODUCT 函數要使用工作表1和工作表2的內容來運算。
在 SUMPRODUCT 函數中參照其他工作表的位址是可以的,只要儲存格範圍的對照是正確的即可。如下公式:
工作表3的儲存格A2:=SUMPRODUCT((工作表1!$B$2:$B$11>5)*工作表2!B2:B11)
如果你覺得公式太長不好處理,可以參考以下的做法:
網友問到:在下圖左的 Excel 工作表中有一些廠商和產品的需求量,其中有一些是重覆的項目,如何將其轉換為下圖右的摘要統計表?
在下圖中,轉換過程中,空白列也一併消除了。該如何建立這個報表?
網友詢問該用何公式來處理?我也還沒想出來。但是使用樞紐分析表,幾分鐘即可以輕易做出結果。
參考以下的步驟:
1. 選取儲存格A1:C20,建立樞紐分析表。
網友問到的 Excel 問題:如下圖左有一個人員的排班基本表,其中每個日期分為二列,分別是早班和晚班的人員輪值名單,現在要轉換為下圖右的人員和日期的彙整表,該如何處理?對照
【公式設計與解析】
選取儲存格A1:A21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
儲存格I2:=OFFSET($B$1,SUMPRODUCT((OFFSET($C$1,MATCH($H2,
網友問到實用且簡單的問題:在下圖的 Excel 工作表中,如何將報到日期欄位尚未輸入資料者,整列以不同底色標示?
參考以下的步驟:
1. 選取所有的資料,本例為儲存格A2:D26。
2. 在「設定格式化的條件」中新增一個規則。
規則類型:使用公式來決定要格式化哪些儲存格
有同事問到一個簡單卻常見的問題。在 Excel、Word 中,我們常會利用各種捷徑、超連結、釘選等功能來達到快速開啟文件,但卻常忽略了這份文件的實體位置,而在檔案總管中要切換至文件所屬資料夾,總覺得要想很久文件放在那個資料夾,或切換很多層資料夾才能到達文件所在位置。
如何才能在檔案總管中,快速開啟某一文件的所在資料夾呢?
以 Excel 2013 版本為例,其「資訊」頁面提供了解答!
當你開啟了一份文件後,切換至[檔案]功能表的「資訊」面頁下。你會在檔案名稱之下看到檔案的儲存格位置,點選這個位置就會顯示快顯功能,點選「開啟檔案位置」,即會在檔案總管中開啟這個資料夾。
或是在視窗右下角的[相關文件]區中也可以點選「開啟檔案位置」,快速在檔案總管裡進入這份文件的資料夾。
網友問到:在 Excel 工作表中有一個社團選取結果的資料表(如下圖左),如何才能分社團/分星期的列出各個報表?
例如:在下圖中有多個班級的選社結果,其中星期一至星期五,每個學生每天都有一個要參加的社團,如何能分別依各星期和各社團列出社團人員的清單?
【公式設計與解析】
1. 定義儲存格範圍名稱
選取D欄至H欄中有資料的範圍(例如:儲存格D1:H200),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:星期一、星期二、星期三、星期四、星期五。
這篇文章雖然簡單,但是有感而發。因為常看到同事在已寫好的公式又新增資料,造成一些調整公式上的困擾。
在 Excel 中寫好的計算公式,如下圖:
一旦新增了某些資料後,還得要自行更新某些公式,是否有些較方便的方式?
(1)
網友提問 Excel 的問題:想要統計公司裡的三間工廠於一年中各個月的離職人數,該如何處理?
如下圖,有一個日期清單,在工廠欄位中有A廠、B廠、C廠三種可能,如何能建立分月/分廠的分析報表?
【公式設計與解析】
1. 定義儲存格範圍名稱
網友在下圖的 Excel 工作表中,想要根據圖上的A欄條件,要在B欄顯示一個結果,該如何處理?
如下圖,共有四個條件,四個結果,假設A欄小於或等於 500 時,結果預設為 0。
【公式設計與解析】
(1) 使用 IF 函數
網友問到 Excel 的問題,如下圖一組分成 2, 3, 4, 6, 7, ... 個,如何計算分組後的小計(加總。
以下圖為例,每 2 個一組時,第01組為儲存格A2:A3、第02組為儲存格A4:A5、...,如何自動計算各個分組的小計?
【公式設計與解析】
儲存格D2:=SUM(OFFSET($A$2,(ROW(1:1)-1)*D$1,0,D$1,1))
網友想要在 Excel 工作表中來計算一個月的工作天,其中要計算不含星期日和不含星期六、日的工作天,分別為多少?
如下圖,本例提供二種不同的計算方式。其一:列出該月的每一天;其二:只列出某年某月,即可計算工作天數。
【公式設計與解析】
(1) 計算不含星期六日的工作天數(使用日期清單)
儲存格E2:=SUMPRODUCT(1*(WEEKDAY(A2:A32,2)<6))
網友問到:如下圖,根據儲存格A2和儲存格B2的內容來決定整欄的色彩。
當A2>B2時,A欄為淺綠色、B欄為粉紅色。
當A2<B2時,A欄為粉紅色、B欄為淺綠色。
1. 如下圖,預設A欄色彩設定為淡綠色,預設B欄色彩設定為粉紅色。
學校同仁問到 Word 合併列印時遇到的問題,如下圖的主文件設定(錯誤),通常我們會使用 Next Record 指令,用以在一頁中合併多筆資料(本例中每 1 頁有 4 筆記錄):
得到的結果,每 4 筆就跳過 1 筆,問題出在那裡?(座號 5 和座位 10 未被列出)
因為 Word 在列印時會在下一頁時即會自動列印下一筆記錄,因此在設定主文件時,最後一筆記錄之後,不需再加上 Next Record 指令。
在 Excel 中的個資料表,如下圖,在A欄中的項目有許多是重覆的,如何計算各個不同項目的四個欄位資料的總和?
例如:在『甲』項目在第5,6列,要計算數值1~數值4的總和。
【公式設計與解析】
儲存格H2:=SUMPRODUCT(($A$2:$A$22=G2)*$B$2:$E$22)
$A$2:$A$22=G2:在儲存格A2:A22中判斷是否和儲存格G2相同,傳回 TRUE/FALSE 陣列。
有網友問到:在 Excel 的工作表中,每三列中只有第二列有資料,如何複製第二列的資料至第一列和第三列中?
如下圖,儲存格A2有資料,要將儲存格A1和儲存格A3複製儲存格A2的資料,同欄依此類推的操作,該如何處理?
【公式設計與解析】
1. 原始資料在A欄,在C欄中顯示處理後的結果。
有網友想要在 Excel 的兩個工作表中,使用一個 VLOOKUP 函數查詢,該如何處理?
就我的理解,VLOOKUP 函數無法使用在跨工作表中,所以必須稍加調整公式才能查詢結果。
例如下圖中,工作表1和工作表2含有相同資料範圍但是不同內容的資料,如果想要在一個儲存格中完成查詢的工作表該如何設計公式?
【公式設計與解析】
網友問到:在 Excel 中如何為儲存格內容加上編號?
如下圖A欄為原始內容,在B欄和C欄分別加上不同格式的編號。
【公式設計與解析】
(1) B欄編號格式(例舉三種)
儲存格B2:=ROW(1:1)&"."&A2
網友問到:如下的 Excel 資料表,如何從表格中找出最大值,並且傳回其欄、列名稱?
如下圖,欄標題:甲、乙、…、癸,列標題:子、丑、…、亥。假設資料中的數值,其中沒有重複的內容。
【公式設計與解析】
選取儲存格B2:K13,定義名稱:DATA。
有網友根據下圖左 Excel 的基本資料表,其中包含日期、項目和數值的清單,而項目是由多個不固定的內容所組成。若要轉換成下圖右的資料表,分別依內容和月份計算數值總和,該如何處理?
【公式設計與解析】
選取儲存格A1:C24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:
日期、項目、數值。