ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] VLOOKUP ¦WºÙ¤£­«½Æ¡A¼Æ¶q¥[Á`°ÝÃD

[µo°Ý] VLOOKUP ¦WºÙ¤£­«½Æ¡A¼Æ¶q¥[Á`°ÝÃD

¦U¦ì¤j¤j ±z¦n:
¦pªG­n¨Ï¥X³fªí-1¸ò¥X³fªí-2¦@¦P¥[Á`¬O­n¥Î¦óºØ¨ç¼Æ¸û¬°¾A¦X?
¦]¬°¤p§Ì¥Î¤FVLOOKUPÁÙ¬OµLªk¶i¦æ¦P«~¶µ¥[Á`¥u·|¥[Á`¨ä¤¤1­Ó¬Û¦üªº¼Æ­È¡A
¦ÓµLªk¥[Á`©Ò¦³¬Û¦Pªº¼Æ­È!
«~¦W¤£­«½Æ¡A¦ý¥X³fÁ`¼Æ¶q·|»P¥X³fªí-1¸ò¥X³fªí-2¶i¦æ¥[Á`¡A½Ð°Ý¤j¤j­n¦p¦ó¨Ï¥Î?
¤Z½Ð¤j¤j½ç±Ð  ±Ð±Ð¤p§Ì¦p¦ó¶i¦æ¦WºÙ¤£­«½Æ¡A¼Æ¶q¶i¦æ¥[Á`!

VLOOKUP¸ê®Æ¾ã¦X°ÝÃD.rar (4.02 KB)

¸Õ¸Õsumifs¨ç¼Æ¦pªþ¥ó¡A¤£ª¾¬O§_¹F¨ì§Aªº»Ý¨D¡H
¦ý¦n¹³¦³¬Û®e©Ê°ÝÃD¡A¤£½T©w§A¨ºÃä¬O§_¥i¥H¬Ý¨ìµ²ªG¡A¦b"Á`­p"¤u§@ªíªºE1Äæ¦ì¡A¶K¤W¤½¦¡=SUMIFS('¥X³fªí-1'!$E$2:$E$25,'¥X³fªí-1'!$D$2:$D$25,¸ê®Æ®w!$D2)+SUMIFS('¥X³fªí-2'!$E$2:$E$25,'¥X³fªí-2'!$D$2:$D$25,¸ê®Æ®w!$D2)¡A ¤§«á©¹¤U½Æ»s¡C
VLOOKUP¸ê®Æ¾ã¦X°ÝÃD.rar (8.98 KB)

TOP

¦^´_ 1# ¤ÑÅ]¶Â¦Ð
   
E2 =SUMPRODUCT(SUMIF(INDIRECT("'¥X³fªí-"&ROW($1:$2)&"'!C:C"),C2,INDIRECT("'¥X³fªí-"&ROW($1:$2)&"'!E:E")))
¤U©Ô

·í¦³¦hªí®æ¦³§Ç¸¹³W«h®É¡A¥i¥H¥Î¦¹¦¡ÂX¥R
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 2# tku0216

¤j¤j ±z¦n :
§Ú´ú¸Õµ²ªG¦n¦V·|¥X²{¶Ã½X¡AµLªk¥¿½TÅã¥Ü¸Ó¼Æ­È¡C

TOP

¦^´_ 3# ML089


¤j¤j ±z¦n:
§Aªº¸Ñµª¨ç¼Æ¡A¥¿¬O¤p§Ì·Q­nªº¨ç¼Æ¡A¦ý¬O·Q½Ð°Ý¬O§_¦³¬Æ»ò³]©w¥i¦Û°Ê±N¦³¼Æ¶qªº«~¦W¡A¶i¦æÅã¥Ü¦Ó¤£­«½Æ©O?

TOP

¦^´_ 5# ¤ÑÅ]¶Â¦Ð
¦hªí®æ¥X³f¼Æ¶qÁ`­p(SUMIF)_ML089.rar (8.62 KB)

½Ð°Ñ¦ÒÀÉ®×»¡©ú

Sheet:¸ê®Æ®w

¼W¥[E(¥X³f¼Æ¶q)¡BF(§Ç¸¹»²§UÄæ)¡BG(¥X³fªí¦WºÙ)µ¥¤TÄæ¦ì               
¶À¦â°Ï¬°¤½¦¡¦p¤U               

E2 =SUMPRODUCT(SUMIF(INDIRECT("'"&¥X³fªí¦WºÙ&"'!C:C"),B2,INDIRECT("'"&¥X³fªí¦WºÙ&"'!E:E")))               
F2 =N(F1)+(E2>0)               
¦WºÙ©w¸q:               
¥X³fªí¦WºÙ        =OFFSET(¸ê®Æ®w!$G$2,,,COUNTA(¸ê®Æ®w!$G:$G)-1)       

---------------------------------------------------------
Sheet:Á`­p

A2 =IF(ROW(A1)>MAX(¸ê®Æ®w!F:F),"",ROW(A1))
C2 =IF(A2="","",INDEX(¸ê®Æ®w!B:B,MATCH(A2,¸ê®Æ®w!F:F,)))
D2 =IF(A2="","",INDEX(¸ê®Æ®w!D:D,MATCH(A2,¸ê®Æ®w!F:F,)))
E2 =IF(A2="","",INDEX(¸ê®Æ®w!E:E,MATCH(A2,¸ê®Æ®w!F:F,)))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089

¤j¤j±z¦n :

Àɮפp§ÌµLªk¤U¸ü¡A¦ý·Q½Ð°Ý¤@¤U§Ú¦A·s¼W¨ä¥L¥X³fªí®É¡A¶i¦æ¥[Á`¡A«oµLªk¶i¦æ¥[Á`«o¥X²{¿ù»~¡A³o­n¦p¦ó¶i¦æ¨ç¼Æ¤½¦¡¦êÁp©O?
ÁÙ¦³¤@­Ó´N¬O(¥X³fªí-)³o­Ó¬O¹ïÀ³­þ­Ó­¶ÅÒªº?¦]¬°¥u­n­¶Åҭקï...¸Ó¨ç¼Æ¦ì¸m·|¥X²{¿ù»~!
·Ð½Ð¤j¤j½ç±Ð!

TOP

¦^´_ 7# ¤ÑÅ]¶Â¦Ð

Sheet: ¸ê®Æ®w
GÄæ(¥X³fªí¦WºÙ)¡A´N¬O¥ÑG2¶}©l¶ñ¤J§A­n²Î­pªº¥X³fªí¦WºÙ¡A³o¼Ë¼W´î¤ñ¸û¦³¼u©Ê¡A¥X³fªí¦WºÙ­×§ï®É³o¸Ì¤]­n¦P¨B­×§ï¤~¤£·|³y¦¨¿ù»~¡C

¨Ò¦p
¥X³fªí-1
¥X³fªí-2
...
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 8# ML089

ÁÂÁ¤j¤j¡AÅý¤p§Ì¾Ç¨ì¨ç¼Æ¤½¦¡¤è¦¡!

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD