edmondsforum

¦^´_ 4# quickfixer

¦A«×ÁÂÁ¤j¤j´£¨Ñ¤èªk¡A¦ý¦]¦Ò¶q²Å¸¹¦]¯À©M¥¼¨Ó some_array=SMALL(IF(arange=¬ö¿ý!$F3,ROW(brange)-1,""), ROW(INDIRECT("1:"&COUNT(IF(arange=¬ö¿ý!$F3,ROW(brange),"")))))
³o­Ó  ¬ö¿ý!$F3   ·|®Ú¾Ú¤u§@ªí¡G ¨Ò¦p  ²Ä1¶g!F3    /   ²Ä2¶g!F3  ....¨Ì¦¹Ãþ±À
¥i¯à­n³Ð³y100­Ó¦WºÙ....
©Ò¥H­è­è¦³§ä¨ì¥t¤@­Ó¤è¦¡¡A¥u¥i±¤¨S¿ìªk Åý¨ä¥L¶g¦P®É¥X²{¤@¼Ëªº¤å¦r½}¤F...
G3 =TEXTJOIN(":",TRUE,IF(($B$2:$B$30=F3)*($C$2:$C$30<>""),IF(MATCH($C$2:$C$30,$C$2:$C$30,0)=(ROW($C$2:$C$30)-MIN(ROW($C$2:$C$30))+1),$C$2:$C$30,""),""))

¤U¸ü (86.62 KB)

2020-6-23 10:51

¥Ø«e¬O§Æ±æ¯àÅý²Ä3¶g ¯à¶]¥X ¬Û¦Pªº¤å¦r  

¸Ñ¨M¤FªÅ¥Õ©M­«½Æ¡A«o³y¦¨¨ä¥L¶g­«½Æ¶]¤£¥X¨Ó «¢«¢
¦A«ô°U¦U¦ì¤j¤j¨ó§U¤F

TEST03.zip (13.26 KB)

¤U¸ü¦¸¼Æ: 5

2020-6-23 10:53

quickfixer

¦^´_ 3# edmondsforum

#N/A ´N¥Îifna³B²z´N¦n¤F
=TEXTJOIN(";",,IFNA(IF(MATCH(carray,carray,0)=rowarray,carray,""),""))
(ctrl+shift+enter)

¤U¸ü (68.53 KB)

2020-6-23 10:09

edmondsforum

¦^´_ 2# quickfixer
ÁÂÁ¤j¤j©pªº¤À¨É¡A¦ý§Ú§Ñ¤F­z»¡¤@¥ó¨Æ¡A¦pªG§Ú§â³¡¤À¤º®e³£¬°ªÅ¥Õ¡A¥L·|Åã¥Ü#N/A..«ç»ò¿ì©O

¤U¸ü (108.61 KB)

2020-6-23 08:47

quickfixer

§Ú§ä¨ì³o½g±Ð¾Ç,¸ò§A¤@¼Ëªº®æ¦¡,§Ú·ÓµÛ°µok³á
https://dhexcel1.wordpress.com/2017/04/04/multiple-lookup-values-in-a-single-cell-withwithout-duplicates-using-only-excel-formulas-by-david-hager/

¤U¸ü (43.84 KB)

2020-6-23 04:54

¤U¸ü (19.5 KB)

2020-6-23 04:56

arange=¬ö¿ý!$B$2:$B$30
brange=¬ö¿ý!$C$2:$C$30
some_array=SMALL(IF(arange=¬ö¿ý!$F3,ROW(brange)-1,""), ROW(INDIRECT("1:"&COUNT(IF(arange=¬ö¿ý!$F3,ROW(brange),"")))))
carray=INDEX(brange,N(IF(1,some_array)))
countarray=COUNTA(carray)
rowarray=ROW(INDIRECT("1:"&countArray))

g3=TEXTJOIN(";",,IF(MATCH(carray,carray,0)=rowarray,carray,""))
(ctrl+shift+enter)