(免費) 有冇人想學Excel formula, 或者想搵人幫手砌excel表

補充一點
對我黎講我淨係要去care 個keyword 係唔係第一個column 我已經覺得浪費左我時間
index同match 我直接選擇lookup 同return 既column , 係另類既more direct

再補充多一點
的確有嘢係vlookup 做到但index match 做唔到
但應該唔係你諗果啲

if you have a lot of data, I recommend using pivot table to "sumif".

sumif is slow but sumproduct is much slower
http://www.exceluser.com/blog/483/excels-sumifs-or-sumproduct-which-is-faster.html

呢個情況下我睇唔到會有任何分別

pivot table更加唔會係首選

when you are working with data with rows more than 300,000
using sumif is a nightmare, however, pivot table still performs very fast in such a case

不過我本身係唔係好鍾意果堆好specific既功能
包括vlookup sum/countifs以至pivot table
老實講sumproduct 如果唔係可以慳個cse, 我直頭會用sum

不過呢啲只係我個人既偏執多
如果係performance好重要既情況下, 的確果堆野係會好用，我事實上都會用

但你真係要好specific要match 到佢既condition 先可以用，呢個就係我唔鍾意既地方

下面有回應

there are few options to "sumif"
1. pivot table
2. sumif() / sumifs()
3. sumproduct()
4. array formula: {sum(if())}

I prefer 1 if I have a lot of data, 2 if data set is small, I seldom use 4 as array formula is volatile and very slow as a result. I almost never use 3 except for rare cases

你講既performance問題我其實都知
所以呢個係手勢問題唔係單純考慮performance 既問題
即係我用落順唔順手既問題

第二就係通常我要sum 既都唔係已經計好左係度俾我sumif 既數(即係你所講既special case?)
好多時就算用到sumproduct 都係要cse啪落去
咁樣sumproduct 既優勢就冇左
結果就習慣直接用sum

所以我上面都講左，如果performance係最優先要考慮，你要自己搵最好既方法

如果唔係，sum + cse啪落去最直接

講到尾你用咩方法都係要根據你本身要做乜野，有幾多data

pivot 唔會係我叫人用既首選因為你習慣左用呢樣野就真係冇野學到

推

留名

歡迎

ching讀咩出身

完全不相關

pish

好皮毛
我唔係做it 相關

點解唔用pivot table .禁有咩推介？
利申 excel垃圾

係我唔教,唔係叫你唔好用
我原意係教你地點樣諗一條formula點砌
從而令你地第日可以自己諗formula出黎解決問題

而pivot真係冇咩好教,唔通教你禁邊粒制開邊個function出黎咩
呢d真係google下就搵到

可唔可以講下address同indirect加埋回點用，google過睇唔明

address
係用黎幫你gen個reference 格式既text出黎
例如address(2,1) return "$A$2"
因為你row入2, col 入1, 就出A2
留意呢個係text,唔係真係reference,佢本身係參照唔到任何野的

indirect係將text作為一個ref咁用
我當A1 呢一格CELL本身寫住"C3"
C3 呢一格CELL本身寫住"時代革命"
case 1 : INDIRECT(A1) return "時代革命"
case 2 : INDIRECT("C3") return "時代革命"
case 3 : INDIRECT(C3) return #REF!

1. A1 係ref, 唔係text
A1入面寫住"C3", "C3"係一個TEXT, INDIRECT就將呢個TEXT作為REF咁用, 所以就return C3 果格既value,即係"時代革命"

2. "C3" 係text 唔係 ref, INDIRECT直接將呢個TEXT作為REF咁用
所以就return C3 果格既value,即係"時代革命"

3. C3 係ref, 唔係text
C3入面寫住"時代革命", "時代革命"係一個TEXT, INDIRECT就將呢個TEXT作為REF咁用, 但係"時代革命" 唔係一個REF既正確格式,所以會出ERROR