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

冇圖冇真相 2020-1-21 11:40:36 有ABCD 4個級別
每個級別入面有唔同既數字
例如A級入面有1 5 9 13 17 21
我想搵出4 7 9 12 14 17所屬既級別係咩
想知條formula係咩thanks

:^(

古都光 2020-1-21 11:58:29 想問pivot table double click咪可以gen果堆raw data出離，但出離個表有d唔用既行想hidden, 但又有大量，冇理由次次都人手咁逐行hidden
有冇得set個rxport tempate出離一禁就係果個樣

PeterStrange 2020-1-21 14:22:24 用唔到match

:^(

btw啲數字係example?
如果真實都係咁我會直接計個級別出黎算

:^(

希特拉 2020-1-21 14:42:23 =IF(ISNA(VLOOKUP($G2,$B$2:$B$7,1,0)),"","A") & IF(ISNA(VLOOKUP($G2,$C$2:$C$7,1,0)),"","B") & IF(ISNA(VLOOKUP($G2,$D$2:$D$7,1,0)),"","C") & IF(ISNA(VLOOKUP($G2,$E$2:$E$7,1,0)),

希特拉 2020-1-21 14:43:01 =IF(ISNA(VLOOKUP($G2,$B$2:$B$7,1,0)),"","A") & IF(ISNA(VLOOKUP($G2,$C$2:$C$7,1,0)),"","B") & IF(ISNA(VLOOKUP($G2,$D$2:$D$7,1,0)),"","C") & IF(ISNA(VLOOKUP($G2,$E$2:$E$7,1,0)),"","D")

PeterStrange 2020-1-21 14:54:14 .....

:^(

冇圖冇真相 2020-1-21 18:21:00 Thank you先

:^(

如果去到Z係唔係要&到Z 26條式?

PeterStrange 2020-1-21 18:21:56 please check #928

冇圖冇真相 2020-1-21 19:15:07 數字係example黎
真實既話應該係不規則整數黎
而ABCD唔跟順序同未必係ABCD有機會係甲乙丙丁

PeterStrange 2020-1-21 19:17:45 你照用呢個就可以

桐人梧桐命 2020-1-21 19:29:11 留名
多數遇到唔識既都係google

:^(

一去到array formula就覺得好難明

冇圖冇真相 2020-1-21 19:30:02 唔係好明COLUMN( )

鱔爛蛇死 2020-1-21 19:30:50

:^(

PeterStrange 2020-1-21 19:38:34 可以講下你覺得乜野難明

PeterStrange 2020-1-21 20:34:26 column(A1

:^(

1) 出 {1,2,3,4}
(B5:E7=G5)← 呢個ARRAY得一個數會出TRUE
兩個乘埋就知INDEX第幾格

PeterStrange 2020-1-21 20:40:58 column(A1: D1)

賭完又賭 2020-1-21 20:52:15 請問SUMPRODUCT點用好

PeterStrange 2020-1-21 20:56:47 冇乜情況係一定要用SUMPRODUCT
sumproduct(a,b)
sumproduct(a*b)
sum(a*b)
其實係同一樣野黎
sumproduct只係優化左少少performance
同埋好多情況下可以唔使CSE咁入 ( ctrl+shift+enter)

呢題個情況完全可以唔用,純粹係為左慳個cse

:^(

PeterStrange 2020-1-21 21:02:49 優自己老母化

:^(

sumproduct只係增加左少少performance

:^(

冇圖冇真相 2020-1-21 22:10:12 明白晒 thank you

:^(

PeterStrange 2020-1-21 22:19:45

:^(

PeterStrange 2020-1-22 12:02:11

:^(

PeterStrange 2020-1-22 19:32:27

:^(

PeterStrange 2020-1-23 14:20:20

:^(

鳩美斯 2020-2-2 23:08:36 lm