时间：6:30 pm - 10:10 pm
地点：Seri Alam (J88 Food Court > Kluang Rail Coffee)
出席：叶明义, Desmond, Elaine, 小黑, Yuki Lim, Vincent, Moon Poh, YK, Wisty
2026年9月26日星期六
2026年9月11日星期五
Typing underscores in Excel
Select the area
Press Ctrl + 1 (or right click, choose Format Cells)
Under Category, Go to Custom
Under Type, remove General, type #*_,click ok
Enter number and see the result
Excel Consolidate Data
Click Product (red)
Go to Data Tab > Consolidate
Add reference tables One-by-One
At the window of Consolidate
Under Reference, click on the blank
1) Choose the area of two column with multiple row
2) Then, click Add
Repeating step 1 & 2
After done add reference, click the box of Top row & Left column
Click OK
Excel Spin Button
to quickly increase or decrease numbers in Excel
Developer > Insert > Spin Button (Form Control)
Right-click the Spin Button > Format Control
At the window of Format Control, at the blank of Cell link: choose the Quantity cell
2026年9月9日星期三
Excel 下拉菜单
一级下拉菜单
Choose the area of State B3:B13 (including title)
Click Data > Data Validation (Data Tools) > Data Validation
At the window of Data Validation,
Allow: List
Source: E3:G3 (state category)
Click OK
二级下拉菜单
Choose the area of E3:G9 (including title)
Click Ctrl + G (or Home > Editing > Find & Select > Go to Special)
Click Special
Click Constants, then click OK
Go to Formulas > Define Name > Create Names from Selection
Make sure only Top row was click, then click OK
Choose the area of City C3:C13 (including title)
Click Data > Data Validation (Data Tools) > Data Validation
At the window of Data Validation,
Allow: List
Source: =indirect(B3) <Remove $>
2026年9月8日星期二
Excel lookup functions: VLOOKUP (Vertical Lookup), XLOOKUP (Modern Lookup), HLOOKUP (Horizontal Lookup) & "INDEX & MATCH"
=VLOOKUP(B13,$B$25:$D$33,2)
=VLOOKUP(B15,B$25:D$33,2,0)
=XLOOKUP(B14,B25:B33,C25:D33,"No Data")
vlookup 一不小心会导致导入错误
XLOOKUP 比较安全，不会导入错误
VLOOKUP 漏掉了最后一个参数（最核心原因）
公式：=VLOOKUP(B13, B23:D31, 2)
导致的问题：你没有写第四个参数，Excel 会默认以为你要进行模糊匹配。而在模糊匹配下，如果你的工号序列没有严格按“从小到大”升序排列，Excel 就会彻底罢工，直接找错人或者返回错误值。
解决办法：必须加上 ,0 或 ,FALSE 来强制精确匹配。
___________________________________________________________________________
=HLOOKUP(B41,C49:G51,2)
=HLOOKUP(B42,C$49:G$51,2,0)
HLOOKUP 漏掉了最后一个参数（导致模糊匹配报错）
问题所在：公式末尾没有写 ,0 或 ,FALSE。Excel 默认进行模糊匹配，而你的数据横向并没有按字母顺序严格升序排列，所以必错无疑。
解决办法：必须在末尾加上 ,0 确保精确匹配。
________________________________________________________________________________
=INDEX(C64:F73,MATCH(D57,C64:C73,0),MATCH(C58,C64:F64,0))
=INDEX(Choose the area of reference table with title,MATCH(student name,student name list with title,0),MATCH(subject,subject list with title,0))
=INDEX(Choose the area of reference table with title,MATCH(student name,student name list column with title,0),MATCH(subject,subject list row with title,0))
订阅：
博文 (Atom)