文書處理 - Excel 函數運用分享 - 電腦

前往內容


Excel 函數運用分享

最近剛好有人詢問有關(陣列)查詢的作法,
所以就順便整理了幾個較常遇到的狀況,
把一些 Excel 函數利用多重套用的方式介紹給大家,
一方面讓自己方便查詢,
也能讓更多人學習這方面的技巧!

個人所使用的公式並不見得完美,
如有更好的方式可提出來一起研究,
有疑問者也歡迎提出討論!

Data 工作表:自動套用萬年曆
只需輸入年&月,所有日期及星期會自動帶入!



B3:
=DATE($C$2+1911, $G$2, COLUMN()-1)
利用 DATE 函數將該欄變成日期!

C3:
=IF(B3="", "",
IF(DAY(DATE($C$2+1911, $G$2, COLUMN()-2))=
DAY(EOMONTH(DATE($C$2+1911, $G$2, 1), 0)), "",
DATE($C$2+1911, $G$2, COLUMN()-1)))

說明:
IF(B3="", "", 副條件)
若前一天的日期為空白,代表已超過月底,故不顯示內容!
(此條件僅會在二月份成立)

副條件:IF(DAY(DATE($C$2+1911, $G$2, COLUMN()-2))=
DAY(EOMONTH(DATE($C$2+1911, $G$2, 1), 0)),
"", DATE($C$2+1911, $G$2, COLUMN()-1))
因為有大小月,所以利用此方式隱藏不適合的日期,
此公式亦可由 AD3 再開始(因為超過 28 天才有可能超過月底)

注意,該列儲存格格式要設定如下:



B4:
=RIGHT(TEXT(B3,"aaa"),1)
取得 B3 欄日期是星期幾!

範圍 B3:AF4 設定格式化條件(如下圖),
遇到六、日底色會自行變色!





List 工作表:陣列查詢
LOOKUP 家族及 MATCH 函數僅能針對單一結果查詢,
若結果可能有多筆,則需使用陣列函數!



B4:
=IFERROR(OFFSET(Data!$A$6,
SMALL(IF(LEFT(OFFSET(Data!$B$6, 0, DAY($A$2)-1,
COUNTA(Data!$A$6:$A$15), 1), LEN($B$3))=B$3,
ROW(OFFSET(Data!$B$1, 0, DAY($A$2)-1,
COUNTA(Data!$A$6:$A$15), 1)), FALSE),
INT((ROW(1:1)+1)/2))-1, COLUMN($A:$A)-1), "")

上述陣列公式輸入完成後,
需以 Ctrl + Shift + Enter 結束,
此時公式最外層會自動加上大括號,代表是陣列公式,
剩下需顯示資料的欄位則依一般複製欄位方式複製!


說明:
首先要了解 OFFSET 的用法,
OFFSET(基準欄位, 偏移列數, 偏移欄數, [高度], [寬度])
前三項為必要,後兩項可省略(省略時代表 1),

基準欄位:Data!$A$6
因為最後顯示的姓名在『Data』的 A 欄,
所以用此欄位當作基準起始點!

偏移列數:SMALL(IF(範圍=B$3, ROW(範圍), FALSE), n)-1
用 SMALL 函數分別取得第 n 小的列數,
若改為 LARGE,則查詢結果會以相反順序顯示!

IF(範圍=B$3, ROW(範圍), FALSE)
找出範圍中與 B3 相同的有哪幾列!

範圍:
LEFT(OFFSET(Data!$B$6, 0, DAY($A$2)-1,
COUNTA(Data!$A$6:$A$15), 1), LEN($B$3))
利用 OFFSET 及 DAY 取得要查詢的班別範圍!
加入 LEFT 函數可查詢範圍內左側指定長度符合的項目,
否則僅查詢完全符合者(亦可視需求使用 RIGHT 或 MID 函數),
例如:若不使用 LEFT 函數,Data 工作表中的 B6 將不會顯示!

n:INT((ROW(1:1)+1)/2))-1
因為是每隔一行顯示下一筆資料,
所以利用 INT 函數來達成目的,
如果是每行一筆則改為 ROW(1:1)-1

偏移欄數:COLUMN($A:$A)-1
若只顯示單一欄位,此處可直接用 0 取代,
但考慮到不同情況下,查詢出來的結果可能有數個欄位要顯示,
所以使用此方式,僅需去掉 $ 然後向右複製即可!
My Interior Knowledge is Extraordinaire
Data 部份是固定預留31 的天,如果要美觀一點,當月是小月的話,31號底下就不會有班數的出現,讓怎麼做?


另外,如果做法改法一直向右增加班數(如目前是8月,之後往右加9月的)
要改的部份是否要到 List.SHEET 那邊修改陣列公式中的讀取、參照範圍?

AoLin wrote:
Data 部份是固定預留31 的天,如果要美觀一點,
當月是小月的話,31號底下就不會有班數的出現,讓怎麼做?


有兩種方式:
1.
正常來說,排班並無規律性,
所以直接刪掉空白日期下的文字最簡單!

2.
可參考前面提到的『設定格式化條件』,
當日期(或星期)欄位為空白時,
把該欄下方的文字變成白色(與底色相同),
即使班別文字仍存在,但看起來會是隱形!
(僅需設定日期可能為空白的 29~31 三欄即可)

AoLin wrote:
另外,如果做法改法一直向右增加班數(如目前是8月,之後往右加9月的)
要改的部份是否要到 List.SHEET 那邊修改陣列公式中的讀取、參照範圍?


不建議這麼做,
因為一來當資料愈來愈多時,在輸入新資料時較不方便,版面也會變得混亂,
再者不管是 Data 或 List 中的公式,在設計上要顧慮的地方會更多,也更複雜,
後續若需要修改時會變得更困難!

個人覺得較好的做法是:
全月班表維持每月一個工作表,
月底時直接複製該工作表,只需輸入新的排班即可,
而 List 工作表的公式僅需稍作修改,加入 INDIRECT 函數,
之後即使是不同月份,List 工作表中的公式也不用再修改內容!

全月班表的命名方式就很重要,因為關係到公式設計,
例如:201608, 201609, 201610, 201611, .....


函數 INDIRECT 這部份您可以自己先試作看看!
My Interior Knowledge is Extraordinaire

1頁 (共1頁)

前往




此文章的引用連結