[µo°Ý] ¦p¦ó´£¥X²Å¦X条¥óªº资®Æ¡A²Å¦X条¥ó­«Î`ªº数Õuj¥u´£¥X¤@¦¸

AngelaIndo1st

¦U¦ì¡A §A们¦n¡A
ªþ¥ó¤W¦³两个问题¡A §Æ±æ¤j®a¥i¥H

hcm19522

http://blog.xuite.net/hcm19522/twblog/377136390
E2:G11{=INDEX($C:$C,SMALL(IF((MATCH($B$2:$B$21&$C$2:$C$21,$B$2:$B$21&$C$2:$C$21,)=ROW(B$2:B$21)-1)*($B$2:$B$21=E$1),ROW(B$2:B$21),4^8),ROW(A1)))&""

AngelaIndo1st

Hi HCM19522,

§Ú·Q§Ú发问ªº¤£²M·¡§a¡AA¦ÜP ¬O¤@个¤w¶ñ写¦nªº资®Æ库¡A 现¦b§Ú·Q¦b¥t¥~¤@页«öA ¦ÜP ¤w¦³资®Æ´£¨ú¬Û关ªº数Õu¡C
1¡^ ²Ä¤@个·Q´£¨ú ´N¬O¦bE 栏¦ìªº´Ú号¡A¦ý­«Î`ªº´Ú号¥u´£¨ú¤@¦¸¡A¥t¥~¡A ´£¨úªº¤]¥²»Ý¬O¦b  C栏¦ìª`©ú¬O ORDER CONF IRM, ¡A ¦p C 栏¦ì¤£¬O ORDER CONFIRM¡A¤£´£¨ú .
2¡^ ¥t¥~¤@个·Q´£¨ú¬O  栏¦ìªº¤é´Á¡A°£ ²Å¦X¦b S 栏¦ìªº BOOKING NR¥~¡AC栏¦ì¤@©w­n ª`©ú¬O PENDING PROJECTION ©ÎªÌ¬O ESTIMATE PROJECTION ¤~´£¨ú¡A ¦p C 栏¦ìª`©ú¬O¨ä¥¦¦r

­ã´£³¡ªL

¢°¡D´£¨ú¤£­«Âдڸ¹¡D°}¦C¤½¦¡¡G
=INDEX(E:E,SMALL(IF(ISNUMBER(0/(MATCH("ORDER CONFIRM"&E$4:E$295,C$4:C$95&E$4:E$95,)=ROW(E$4:E$295)-3)),ROW(E$4:E$295),4^8),ROW(A1)))&""

¢±¡D´£¨ú«ü©w½s¸¹ªº¡e¤é´Á¡f¡G
=TEXT(INDEX(L:L,SMALL(IF((D$4:D$295=S$4)*((C$4:C$295="Penging Projection")+(C$4:C$295="ESTIMATE BOOKING")),ROW(L$4:L$295),4^8),ROW(A1)))&"","d-mmm")


¥H¤W¤½¦¡¬°¦P¤@¤u§@ªí¨Ï¥Î¡A­Y­n¥tªí¨Ï¥Î¡A¦Û¦æ¥[¤J¤u§@ªí¦WºÙ°Ñ·Ó¡I
¡@
°}¦C¤½¦¡¿é¤Jªk¡G¡@
¿é¤J©Î­×§ï¤½¦¡§¹¦¨«á¡A´å¼Ð¯d¦b¡e½s¿è¦C¡f¡A¥ý¤£­n«öEnter¡A
¦P®É«ö¦í¡eShift + Ctrl¡f¤£©ñ¡A¦A«ö¡eEnter¡f¡C

AngelaIndo1st

hi ­ã´£³¡ªL,
´£¨ú¤£­«Âдڸ¹¡D°}¦C¤½¦¡
§Ú¥u¬O§ï¤F¦C¤Î¦æ数¡A¦ý为¤°¤\´£¨ú¤£¤F数Õu¡A 烦

AngelaIndo1st

hi ­ã´£³¡ªL,

¤£ª¾为¦ó¥u显¥Ü¤@¤p³¡¥÷内®e¡C
´£¨ú¤£­«Âдڸ¹¡D°}¦C¤½¦¡
§Ú¥u¬O§ï¤F¦C¤Î¦æ数¡A¦ý为¤°¤\´£¨ú¤£¤F数Õu¡A 烦请¬Ý¬Ý
=INDEX('Order Details'!F:F,SMALL(IF(ISNUMBER(0/(MATCH("Order Confirm"&F$10:F$300,D$10$300&F$10:F$300,)=ROW(F$10:F$300)-3)),ROW(F$10:F$300),4^8),ROW(A2)))&""

AngelaIndo1st

hi ­ã´£³¡ªL,

¥t¥~¡A §Ú¥Î¥H¤£¤½¦¡¥i¥H´£¥X·Q­nªº数Õu¡A¦ý¨ì较¦Z¦ì¸m会¥X现#NA 错误¡A 尝试过¥ÎISNA ¤ÎISERROR ¡A ¥[¤F¦Z¤°¤\¤]´£¥X¤£¤F¡A请«ü点
=INDEX('Order Details'!B$10:B$300,(MATCH('Order Status Recap'!F22&'Order Status Recap'!G22,'Order Details'!F$10:F$300&'Order Details'!D$10$300,)))

­ã´£³¡ªL

¦^´_ 6# AngelaIndo1st


ROW(F$10:F$300)-3 ­n§ï¬° ROW(F$10:F$300)-9¡@¥ç§Y¬Ò±q¢°¶}©l¡A


°Ñ¦ÒÀÉ¡G
http://www.funp.net/771362

AngelaIndo1st

¥Ñ²Ä10¦æ开©l¤ÀªR¡A ©Ò¥H将数¦r§ï为9¦Ó¤£¬O3¡A ¤w§ï¤F还¬O¤£¦¨§r¡H
¦b¦P¤@页试¤F¤£¦¨
=INDEX(F:F,SMALL(IF(ISNUMBER(0/(MATCH("Confirm Booking"&F$10:F$300,D$10$300&F$10:F$300,)=ROW(F$10:F$300)-9)),ROW(F$10:F$300),4^8),ROW(A1)))&""

实际数Õu¬O­n©Ô¨ì¥t¥~¤@页ªº总ªí¡A§Ú试¤F¤]¬O¤£¦¨

=INDEX('Order Details'!F:F,SMALL(IF(ISNUMBER(0/(MATCH("Confirm Booking"&'Order Details'!F10:F300&'Order Details'!D10300,)=ROW('Order Details'!F10:F300)-9)),ROW('Order Details'!F10:F300),4^8),ROW(A1)))&""

AngelaIndo1st

Hi, ­ã´£³¡ªL

¥i§_将§¹¾ã¤å¥ó发¤ñ§A¬Ý¡A¥i¥H¥ÎQQ 邮¥ó©Î¨ä¥¦邮¥ó½c吗¡H ¦]§¹¾ã¤å¥ó¦³¤@¨Ç资®Æ¤£«K¤½开©ñ¦bÊI¤W