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

[µo°Ý] ³]©w3­Ó±ø¥ó§ä¥Xµª®×

[µo°Ý] ³]©w3­Ó±ø¥ó§ä¥Xµª®×

¤u§@°O¿ý.rar (47.96 KB)

¦U¦ì¤j¤j¦n,

½Ð±Ð¦pªþ¥ó¸ê®Æ,·í
1)sheet!3F°O¨ÆªºDÄæ=P1,
2)sheet!3F°O¨ÆªºBÄæ=Q1,
3)sheet!3F°O¨ÆªºF~BVÄæ=C2
¦p¦ó¦bsheet!ª½¦¡ªí®æ,¥N¥X¤½¦¡?Åý¥¦¥i¥H¼Ð¥Ü¬°"½Ð°²,°±¥ë",¦pªG¨S¦³¸ê®Æ´NªÅ¥Õ.

¥ýÁÂÁ¤F..

¦^´_ 1# PJChen

¥Ñ©ó§Ú¤½¦¡¤¤ªº©m¦W¬O¨Ï¥Î"ª½¦¡ªí®æ"²Ä¤@Äæ§@¬°¬d¸ß¨Ì¾Ú¡A¦Ó§AC14~C17ªÅ¤F¤@¤j¬q¡A¸gµû¦ô§A¨â­Ó¬¡­¶Ã¯ªº©m¦W¶¶§Ç³£¬O¹ïÀ³ªº¡A
©Ò¥H§Ú¾Õ¦Û­×§ï¤F§Aªºªí®æ¡A§R°£C14~C17¡A¥|­Ó¦C¡A±o¨ìªþ¥óµ²ªG¡A¤wÅçÃÒ¹LµL»~¡A§A¦A¬Ý¬Ý¹ï¤£¹ï~
¤u§@°O¿ý.rar (51.51 KB)

TOP

¢Ý¢±¡G
=IFERROR(VLOOKUP(Q$1,INDEX('3F°O¨Æ'!A:CA,MATCH(P$1,'3F°O¨Æ'!D:D,),N(IF({1,0},2,MATCH(C2,'3F°O¨Æ'!$3:$3,)))),2,)&"","")

©Î:
=LOOKUP("ùÜ",IF({1,0},"",VLOOKUP(Q$1,INDEX('3F°O¨Æ'!A:CA,MATCH(P$1,'3F°O¨Æ'!D:D,),N(IF({1,0},2,MATCH(C2,'3F°O¨Æ'!$3:$3,)))),2,)&""))

TOP

¦^´_ 2# tku0216
±z¦n,
¥Ñ©ó2Ã䪺ªí®æ¥i¯à·|¤£©w´Á¦³§R´î©Î¼W¥[,§Ú¸ÕµÛ§R°£¨Ç¦C¡Aµ²ªG±a¥Xªºµª®×´N·|¿ù»~¡A·íµM¤]¥i¯à¬O§Ú¿ù»~ªº¨Ï¥Î,·Q½Ð°Ý·í2Ãä¦P®ÉÅÜ°Ê,©Î¥u¦³¤@Ãäªí®æ¦³ÅÜ°Ê®É,¤½¦¡¥²¶·­«·s¤U¶Ü?

¦^´_ 3# ­ã´£³¡ªL
±z¦n,
§Ú¥ý¸Õ¤F²Ä¤@ºØ¤½¦¡,¤£½×ªí®æ§R´î©Î¼W¥[³£¯à±a¥X¥¿½Tµª®×,¦ý½Ð°Ý²Ä¤G­Ó¤½¦¡ªº=LOOKUP("ùÜ",¨ä¤¤ªº¤¤¤å¦rªº§@¥Î¬O¤°»ò?

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2016-1-18 20:32 ½s¿è

¦^´_ 4# PJChen


=LOOKUP("ùÜ",IF({1,0},"",~~)¡@

¦b¢±¢¯¢¯¢²ª©¥H¤U¡A·í MATCH , VLOOKUP §ä¤£¨ì²Å¦X­È¦Ó²£¥Í¡e¿ù»~­È¡D#N/A¡f®É¡A¥iÅã¥Ü¬°""¡A
¨ä¾÷¨î½Ð¥h§ä§ä LOOKUP ªº¯S©Ê¡A¦b¡@http://club.excelhome.net/forum-3-1.html¡@¦³«D±`¦hªº¨Ò¤l¡A
¥\¥Î»P IFERROR Ãþ¦ü¡A¦ý¶È­­¡e¤å¦r®æ¦¡¡f¦³®Ä¡I

TOP

¦^´_ 5# ­ã´£³¡ªL

½Ð°Ý¤j¤j,

­ì¨ÓªºÃD¥Ø
1) ·í§Ú§âSheet 3F°O¨ÆªºAÄæ§R°£®É,Sheet ª½¦¡ªí®æO2=IFERROR(VLOOKUP(Q$1,INDEX('3F°O¨Æ'!A:BZ,MATCH(P$1,'3F°O¨Æ'!C:C,),N(IF({1,0},2,MATCH(C2,'3F°O¨Æ'!$3:$3,)))),2,)&"","")
§Q¥Î¤½¦¡©Ò±a¥Xªºµª®×¥þ³¡¤£¨£¤F¡A½Ð°Ý¤½¦¡­n¦p¦ó­×§ï¡H

2)½Ð°Ý¤½¦¡¤¤ .....N(IF({1,0},2,....³o¥Nªíªº·N¸q¬O¤°»ò,­n¦p¦ó¸ÑŪ¡H¦³¤°»ò½d¨Ò¥i¥H§ä¨Ó°Ñ¦Ò¡H

TOP

¦^´_ 6# PJChen

§R±¼¢ÏÄæ¡A§ï¬°¡G
=LOOKUP("ùÜ",IF({1,0},"",VLOOKUP(Q$1,INDEX('3F°O¨Æ'!A:BZ,MATCH(P$1,'3F°O¨Æ'!C:C,),N(IF({1,0},1,MATCH(C2,'3F°O¨Æ'!$3:$3,)))),2,)&""))

N(IF({1,0},1,MATCH(C2,'3F°O¨Æ'!$3:$3,)))¡@³o¦³¢±­Ó°Ñ¼Æ¡AÅýINDEX¦P®É§ì²Ä¢°Äæ¤Î²Å¦X¤H¦WªºÄæ¦ì¡A
INDEX('3F°O¨Æ'!A:BZ,MATCH(P$1,'3F°O¨Æ'!C:C,),{1,5})¡@¡÷¡@{"¤é±`","½Ð°²,°±¥ë999"}

TOP

¦^´_ 7# ­ã´£³¡ªL

¤j¤j,

¥H¤Uªº©Ð¸¹¬O¦³¸ê®Æªº³¡¥÷,
©Ð¸¹
301
303
306
313
315
329
336
338
351
355
357
366
377

¤½¦¡§ï¬° =LOOKUP("ùÜ",IF({1,0},"",VLOOKUP(Q$1,INDEX('3F°O¨Æ'!A:BZ,MATCH(P$1,'3F°O¨Æ'!C:C,),N(IF({1,0},1,MATCH(C2,'3F°O¨Æ'!$3:$3,)))),2,)&""))
«o¥u±a¥X¥H¤U¸ê®Æ
©Ð¸¹
303
313
336
338
351
357
366
377

1)½Ð°Ý§ÚÁٻݭn§ó§ï¤½¦¡ªº¤°»ò¦a¤è¡H
2)IF({1,0},1.....¤j¬A©·ªº³¡¥÷¬O§_ÄÝ©ó°}¦C¡H

TOP

¦^´_ 8# PJChen


­n¬ÝÀɮ׬d°ÝÃD!!!

TOP

¦^´_ 9# ­ã´£³¡ªL

¤j¤j,

ªþ¤WÀÉ®× ¤u§@°O¿ý.rar (48.18 KB)
³Â·Ð±z

TOP

        ÀR«ä¦Û¦b : ¡i°µ¤Hªº¶}©l¡j¨C¤@¤Ñ³£¬O¬G¤Hªº¶}©l¡A¨C¤@­Ó®É¨è³£¬O¦Û¤vªºÄµ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD