¥Î¨ç¼Æªí¥Ü

kim223824

¦p¦ó¥Î¨ç¼Æ¥i¥H±N¥ªÃ䪺ªí®æÅܦ¨¥kÃä?
¦b¥kÃäªí®æNOÄæ¿é¤J " 1112" ´N·|±a¥X¨ä¥L6µ§¸ê®Æ

­ã´£³¡ªL

¦bµ{¦¡°Ï¨D¨ç¼Æ¡H¡H¡H
¸ê®Æ¦h¤£¦h¡A³oºØ¤½¦¡¤£Â÷ INDEX ,SMALL °}¦C¹Bºâ¡A
¸ê®Æ¤@¦h·|¥dÀÉ¡A¹ïÀÉ®×¾Þ§@¿é¤J¬Û·í¤£§Q¡A
¦ó¤£¿ý­Ó¿z¿ï¥¨¶°¡A­×¤@¤U´N¦n¡I¡I¡I
¡@
¡@

ML089

¦^´_ 1# kim223824

I2 ¬d¸ß¿é¤J 1112
H2 Åã¥Ü¸ê®Æ¦³¦h¤Öµ§¡A¤½¦¡ =COUNTIF(A:A,I2)

I3:M12 °Ï°ì°}¦C¤½¦¡
=INDEX(A:E,SMALL(IF(I2=A2:A11,ROW($2:$11),4^8),ROW(1:10)),{1,5,3,2,4})&""
¤½¦¡¤¤ ROW(1:10) ªí¥Ü¬°²Å¦X±ø¥óÁ`µ§¼Æ¡A¥i¥H¦Û¦æ½Õ¾ã»Ý¨D¡C

¥i¥H¥Î¤½¦¡¦WºÙ NO ³]©w°ÊºA½d³ò
=INDEX(A:E,SMALL(IF(I2=NO,ROW(NO),4^8),ROW(1:10)),{1,5,3,2,4})&""
¦WºÙ NO =OFFSET(!$A$2,,,COUNTA(!$A:$A)-1)
20160325_¬d¸ß1.rar (9.06 KB)

¤U¸ü¦¸¼Æ: 1

2016-3-26 17:41

­ã´£³¡ªL

¢Õ¢°¡D¿é¤J¿z¿ï½s¸¹¡G1112
¢Ö¢±¡G¤U©Ô
=LOOKUP("z",IF({1,0},"",(MATCH(G$1,OFFSET(A$1,H1,,1999),)+H1)&""))
©Î
=LOOKUP("z",IF({1,0},"",(MATCH(G$1,OFFSET(A$1,H2,,COUNTA(A:A)-H2),)+H2)&""))

¢×¢±¡G¥k©Ô¤U©Ô
=IF($H2="","",INDEX($A:$E,$H2,CHOOSE(COLUMN(A$1),1,5,3,2,4)))

¤U¸ü (6.69 KB)

2016-3-26 16:18

Hsieh

¦^´_ 1# kim223824

¸Õ¸Õ¸ê®Æ¬d¸ß

   

¤U¸ü (671.38 KB)

2016-3-27 00:00