有網友問到一個非常實用的問題:一般在 Excel 的工作表中輸入資料時,可以利用「資料驗證」中的清單來產生一個選單,方便使用者以選取選項的方式來輸入資料。但是,如果選項內容是會變動的,或是內容會出現一些空白選項(參考下圖),將會造成一些困擾,該如何克服這些問題?
我們要的是一個會自動增加內容的選單,而且沒有多餘的空白項:
做法如下:
假設選取用來作為選項的內容為儲存格D2:D22。
有網友問到一個非常實用的問題:一般在 Excel 的工作表中輸入資料時,可以利用「資料驗證」中的清單來產生一個選單,方便使用者以選取選項的方式來輸入資料。但是,如果選項內容是會變動的,或是內容會出現一些空白選項(參考下圖),將會造成一些困擾,該如何克服這些問題?
我們要的是一個會自動增加內容的選單,而且沒有多餘的空白項:
做法如下:
假設選取用來作為選項的內容為儲存格D2:D22。
有網友問到:如下圖左的資料,希望能在不同的工作表中以「區別」篩選出合乎的資料(分別將北區、西區、南區、東區篩選至不同工作表),參考下圖右,該如何處理?
【準備工作】
選取所有的資料範圍,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、區別、金額。
以篩選「北區」為例來說明:
【輸入公式】
有網友問到:如果給一個起始的日期,要產生一個固定星期幾週期的日期,該如何處理?參考下圖,以產生每週星期一和星期四為例。
其中:星期一和星期四差三天,星期四和下個星期一相差四天。
在儲存格A2輸入一個起始日期,本例為 2014/2/17。
儲存格A3:=A2+IF(MOD(ROW(1:1),2),3,4)
ROW(1:1):取得第1列的數值(ROW(1:1)=1),當往下複製儲存格時會產生 ROW(2:2)=2、ROW(3:3)=3、…。
有網友根據之前一篇:Excel-標示週末假日的日期(WEEKDAY),想要知道如果日期資料放在不同欄(同一列)時,該如何處理?(參考下圖)
要標示特定日期,需要藉助 WEEKDAY 函式,並利用設定格式化的條件來處理。
1. 選取儲存格A2:I3。
2. 選取「設定格式化的條件」指令,新增一個規則。
有網友問到:在以下的資料表中,如何根據 Num 欄位的內容來傳回「大/中/小」文字?
規則:輸入1,2,3,傳回「小」;輸入4,5,6,傳回「中」;輸入7,8,9,傳回「大」。
以下提供二種不同作法:
(1) 儲存格B1:=VLOOKUP(A2,{1,"小";4,"中";7,"大"},2,TRUE)
這是利用陣列查表方法找出數字對應的文字。
在 Excel 中有一個資料表,其中有多個分組的資料(參考下圖),如何找出各組最大值和最小值呢?
你可能使用過 SUMIF、COUNTIF、AVERAGEIF 等條件式運算函數,但 Excel 中並沒有 MAXIF 或 MINIF 等函數,不過你可以使用陣列公式來取代。
【準備工作】
選取儲存格A1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:組別、數值。
【建立公式】
你是否也是 Gmail 的使用者,你知道有那些 Gmail 的快速鍵可以使用嗎?使用快速鍵可以加快操作的效能,你只要在 Gmail 介面中按一下「?」,即可出現鍵盤快速鍵的一覽表:
在以下的網址中,列出了 Gmail 可以使用的快速鍵:
網址:https://support.google.com/mail/answer/6594?hl=tw
有些快速鍵是預設為啟用的,例如:
有網友問到(參考下圖),想要找尋資料中的每一列之最後一筆資料為何,該如何處理?
(1) 每列資料全為數字
儲存格K1:=OFFSET(B1,0,COUNT(B1:H1)-1)
複製儲存格K1,貼至儲存格K1:K10。
COUNT(B1:H1):計數儲存格B1:H1中共有幾個數字,再透過 OFFSET 函數取得後一個儲存格的位置。
有位網友問到:參考下圖,每月有不固定的人員支領薪資,如何計算各個人員整年度支領的薪資?下圖中以四個月來當範例。
【準備工作】
選取儲存格B2:C25,按一下 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:姓名、薪資。
【輸入公式】
有網友想要知道一年中每個月份的星期一、星期二、…、星期日的數量,該如何處理?
參考下圖,以2014年為例,找出每個月星期日到星期六的日數:
【輸入公式】
儲存格C2:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0))),1)=COLUMN(A:A)))
DATE($A2,$B2,1):取得該月的第一天。