Category Archives: Excel

Overlooked functions in Excel

max & min choose iserror index match

Posted in Excel | Comments Off

Formula for Day of

MonthEnd date(year(today()),month(today())+1,0) YearEnd date(year(today())+1,1,0)

Posted in Excel | Comments Off

3 ways to extract data from excel table

針對一個保險從業的佣金計算作出三個方案: 純用方程式和含數; 用vba; 運用pivot table. 純用方程式和含數會使用match, index, iferror, countifs,sumifs,也用上名稱name。(原来也有同事用上的if,small,row,false; 希望他在設計table時能用上data validation 的list去减少鍵入错誤啦!) 用vba 使用macro錄製 advanced filtering (wild card) 和copy range,加按鈕完成。這方式要存檔為xlxm,也要設定新的安全方案。 運用pivot table 彈性較大。最主要是設計和運用容易為初學者所接收。

Posted in Excel, Windows 7 | Comments Off

Excel Pivot Table

Found a good tutorial site for the subject, save me time to create a similiarvone for the purpose. Excel Easy – Pivot Table Excel Easy – VLookup

Posted in Excel | Comments Off

10 Secrets for excel tables

Posted in Excel | Comments Off