網友問到 Excel 的問題:如何在含有日期清單的資料中,列出指定日期區間內的資料?
如下圖左,有一個「日期、項目、數值」組成的資料清單,想要找出指定的兩個日期之間的資料(參考下圖右)。以下提供三種方式來練習。
1. 手動:使用篩選工具
網友問到 Excel 的問題:如何在含有日期清單的資料中,列出指定日期區間內的資料?
如下圖左,有一個「日期、項目、數值」組成的資料清單,想要找出指定的兩個日期之間的資料(參考下圖右)。以下提供三種方式來練習。
1. 手動:使用篩選工具
網友問到在 Excel 中的問題:如何將每個 25 日後的日期算為下個月?參考下圖,3/30要傳回 4 月,而 12/27 要傳回 1 月。
【公式設計與解析】
儲存格C2:
=MONTH(A2)+(DAY(A2)>25)*1-((MONTH(A2)+(DAY(A2)>25)*1)>12)*12
在 Excel 中有一個數值清單,其中的數值有重覆的清況(如下圖),如何在其中找出指定第N大的數值?
在下圖中,最大值是 15,有 3 個,接下來是 14,有 1 個,接下來跳至 12,有 2 個。通常我們會使用 LARGE 或是 SMALL 來找第 N大或第 N 小的數值,但是這兩個函數均不會忽略重覆的數。該如何解決?
【公式設計與解析】
儲存格C2:=IFERROR(LARGE(IF(FREQUENCY($A$2:$A$25,$A$2:$A$25),
在 Excel 中有一個資料表(如下圖左),想要根據『座號』來取得姓名、成績和名次的資料,該如何處理?(如下圖右)
通常你會使用 VLOOKUP 函數或 HLOOKUP 函數來執行查詢工作,在本例中先觀察這個資料表,是由姓名、座號、成績、名次等四個欄位所組成,若要使用 VLOOKUP 函數來查詢,當取用資料在A欄~D欄時,因為A欄是姓名而非座號,所以無法使用。若取資料B欄~D欄,可以順利用座號查詢,但是無法查詢姓名,因為姓名欄位不在資料範圍。而且查詢結果的顯示順序和原資料的排列順序並不相同。
如何以一個公式便能完成多個儲存格的查詢?還有其他方式可以達到查詢結果,來練習看看。
【公式設計與解析】
有時你會在網頁上看到資料表,當你要將網頁上的資料放至 Excel 活頁簿中,除了複製/貼上之外,你還有別的選擇。
你也可以直接將網頁儲存為 HTML 格式(*.htm 或 *.html),再由 Excel 中開啟。例如:以下的網頁中有一個表格,先將該網頁存檔。
存檔後,你會得到一個檔案(*.html)和一個同名資料夾(*_files):
點入該資料夾,可以看到檔案內容:
網友想要了解如何在 Excel 中快速找出最靠近某日的星期幾日期,例如:在下圖中要找出最靠近 2016/11/19 的星期日、星期五、星期三的日期,該如何處理?
(1) 最近的星期日
儲存格C2:=A2+7-WEEKDAY(A2,2)
本例公式 WEEKDAY 函數使用參數『2』,表示要以數字 1~7 代表傳回星期一~星期日。參考下圖可以發現,指定不同參數可以得到不同的傳回值。
同仁問到一個實用的問題:在 Excel 活頁簿中的每個工作表內容只有一頁大小,當在列印多個工作表時,希望這些工作表的頁碼能連續顯示,該如何處理?
這個操作,其實很多人常會用到。以下圖為例,這個活頁簿中有四個工作表,列印時每個工作表只佔一頁,現在要四個工作表都要列印,並且頁碼也要連續列印。
參考作法:
1. 點選第一個工作表名稱(工作表1),按著 Shift 鍵,再點選最後一個工作表名稱(工作表4)。如此,可以選取這四個工作表。
2. 開啟[版面設定]對話框,進入[頁首/頁尾]標籤。
有同仁問到:在 Excel 中常會用到一些運算,通常在公式中使用『+、-’*、/』,但是如果是想要在表達乘法或除法時使用『×、÷』符號,該如何輸入比較快速?
其實,不管是不是在 Excel 中,都可能會用到乘法和除法『×、÷』符號。在此,先提供了二種輸入方式:
1. 在英數輸入法之下
在英數輸入法之下,可以按著 Alt 鍵不放,再依序按下Keypad上的 2, 4, 7 鍵。
同理,
網友問到:在 Excel 中如何將三欄的資料轉換為表格形式呈現?如下圖,圖左中每一列的資料都沒有重覆,要將其轉換為左上和左下兩種表格呈現,該如何處理?
網友常問到 SUMPRODUCT 函數和陣列的關係,藉這個例子來比較一下。
【公式設計與解析】
(1) 定義名稱