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

[µo°Ý] ­pºâÃþ§O±ø¥ó¥[Á`

¦^´_ 20# ML089

hmmm...¦ü¥G¤S¬O¦hºû¤Þ¥Î©MN()¡BT()¨ç¼Æªº°ÝÃD
¬Ý¨Ó§Ú±o¦n¦n¬ã¨s¤@¤U¡A¨ì©³excel¬O¦p¦ó§PÂ_¤°»ò®É­Ô¬O°}¦C¡A¤°»ò®É­Ô¤£¬O
¤£µM³oºØ°ÝÃDªº­ì²z¦Ñ¬O·d¤£À´¡K¡K

http://club.excelhome.net/thread-98081-1-1.html
http://club.excelhome.net/forum.php?mod=viewthread&tid=145062

TOP

¦^´_ 21# Bodhidharma

¨º¨â½g¦hºû¤Þ¥Î§Ú¤]¬Ý¹Lºâ¸g¨å¡A¾Ç°}¦C(¤j³°¥s²Õ¼Æ?ÁÙ¬O¼Æ²Õ?)¥²¬Ý¤§¤å³¹¡C
³oÃD§Ú¦Û¤v¤]±¼¨ì³´¨À¸Ì¡AINDEX¤ÎVLOOKUP¦b¦hºû¤Þ¥Î¤W¤£®e©ö¡A¤@¯ë³£¬O¨Ï¥Î N(OFFSET(...))¡BT(OFFSET(...))±Ä­°ºûªº¤è¦¡¨Ó³B²z¡C
³o¤@ÃD¤@¶}©l¦bÀx¦s®æ¤W´ú¸ÕVLOOUP¦¡OKªº¡A¦ý¦b°}¦C¤½¦¡(²Õ¼Æ¤½¦¡)®ÉµLªk²Õ¦¨¤º¦s²Õ¼Æ¡A§Ú¦Û¤v³ºµM¨S¦³µo²{³o­Ó¿ù»~¡C
§Ú¦Û¤v²ßºD¼g§¹°}¦C¤½¦¡·|±N°}¦C­È¼g¨ì¹ïÀ³Àx¦s®æ¡AINDEX¡BVLOOKUP¦b³o¤è­±³£·|§e²{¬O¹ïªº¡A¦ý¥ÎF9¥hÆ[¹î¤½¦¡ªº­pºâ­È¤S§e²{¤£¥X¤º¦s²Õ¼Æ¡A©Ò¥H¥ÎF9¨ÓÆ[¹î¤ñ¸û¥¿½T¡C¦ýF9¦³¬d¬Ý¼Æ¶qªº­­¨î¤Î¤£©öÆ[¬Ýªº§xÂZ¡C
¦pªG­n¨Ï¥ÎINDEX¡BVLOOKUP¥h²Õ°}¦C´N¥²¶·±Ä¥Î INDEX(N(IF(...¡BVLOOKUP(N(IF(...ªº¤è¦¡¡A³o¬OPINY¤j®v«Ü­«­nªºµo²{¡C
¦³¿³½ì¥h ¥i¥H¬Ý¤@¤U http://club.excelhome.net/forum.php?mod=viewthread&tid=681243
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

·PÁÂ2¦ìªºÀ°¦£~
=SUM((B2:B7="Á`¤½¥q")*(INT(C2:C7/VLOOKUP(T(IF({1},A2:A7)),E2:F8,2,))*10+MOD(C2:C7,VLOOKUP(T(IF({1},A2:A7)),E2:F8,2,))*2))
³o­Ó¤½¦¡ºâ¥Xªº¬O¥¿½T¤F~
¦ý¬O¤£À´¦p¦ó­pºâ¥Xªº~

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2013-6-9 21:58 ½s¿è

¦^´_ 23# home1913

·Q­n²`¤J¤F¸Ñ¡A«e­±´£ªº¤@¨Çºô­¶¸ê®Æ¥i¥H²Ó²Ó«~¹Á¡A­YÁÙ¤£¤F¸Ñ³o¤]¬O²z©Ò·íµM¡A²¦³º³o¨Ç³£¬O¤ñ¸û°ª¶¥ªº¥Îªk¡A¥ý¤F¸Ñ¥Îªk´N¦n¡C
¤@¯ë¤ñ¸û±`¥Î¬O N(OFFSET())¥Îªk¡A§Aªºªí®æ¤¤½c¤J¼Æ¬O¤å¦r®æ¦¡¡A¶·±Ä¥ÎT(OFFSET())¥Îªk¡A
­Y¬O¤å¼Æ¦r®æ¦¡²V¥Î´N¶·±Ä¥Î INDEX(½d³ò, N(IF({1} )))©ÎVLOOKUP(T(IF({1})))¥Îªk

±Ä¥ÎT(OFFSET())¥Îªk¡A½d¦C¦p¤U
  1. =SUMPRODUCT((B2:B7="Á`¤½¥q")*(INT(C2:C7/T(OFFSET(F1,MATCH(A2:A7,E2:E8,),)))*10+MOD(C2:C7,--T(OFFSET(F1,MATCH(A2:A7,E2:E8,),)))*2))
½Æ»s¥N½X
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¤H¤£©È¿ù¡A´N©È¤£§ï¹L¡A§ï¹L¨Ã¤£Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD