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

¥[Á`²Å¦X±ø¥óªºªñ¥|µ§¸ê®Æ

¥[Á`²Å¦X±ø¥óªºªñ¥|µ§¸ê®Æ

§Ú¦³¤@²Õ¸ê®Æ¡AÁ`¦@¦³A,B,CÄæ¡A§Ú­n·Q¦bA©MBÄæ§ä¥X²Å¦X±ø¥ó¸ê®Æ¡AµM«á¥[Á`CÄ檺¸ê®Æ
°²³]²Å¦X±ø¥óªº¸ê®Æ¦³¤Qµ§¡A§Ú·Q­n¥[Á`ªñ¥|µ§¸ê®Æ¡A¤]´N¬O¦C¸¹«e¥|°ªªº¨º¥|µ§¸ê®Æ¡A¸Ó«ç»ò°µ©O?
¸Õ¤F¤@¨Ç°}¦C¤è¦¡³£µLªk
  1. ={SUM(LARGE(IF((A2:A60='XX")*(B2:B60="KK"),ROW(C2:C60),""),INDIRECT("1:4")))}
½Æ»s¥N½X
¤W­±¬O§Úªì¨Bºc·Q¡A¦ý¬O¥u¯à¥[Á`¦C¸¹¡AµLªk¦A§â¦C¸¹Âন¬Û¹ïÀ³Àx¦s®æ¶i¦Ó¥[Á`
¤£ª¾¹D¦³¨S¦³¤j¤j¦³¤°»ò¦n¤èªk
ÁÂÁÂ
hi

¦^´_ 1# 01300607

¦A¥[­Óindex¸Õ¸Õ
  1. ={SUM(INDEX(C2:C60,LARGE(IF((A2:A60='XX")*(B2:B60="KK"),ROW(C2:C60),""),{1,2,3,4})))}
½Æ»s¥N½X

TOP

²q¤@²q

{=SUM(SUBTOTAL(9,OFFSET(C1,LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(2:60)),ROW(1:4))-1,)))}
300 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2013-5-25 14:57 ½s¿è

¦^´_ 2# Bodhidharma
  1. {}=SUM(INDEX(C2:C60,LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(C2:C60),""),{1,2,3,4})))
½Æ»s¥N½X
­ì¦¡(¤W¦¡)À³¸Ó¬O¤£¯à°õ¦æªº§a? INDEX¤£¯à³o¼Ë²£¥Í²Õ¼Æ

¤@¯ë¥Î N(OFFSET(...)) ªº¤è¦¡¤ñ¸û¦h
  1. {}=SUM(N(OFFSET(C1,LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(C2:C60)),{1,2,3,4})-1,)))
½Æ»s¥N½X
¦p­n¥ÎINDEX¡A¥i¥H°Ñ¦Ò EXCELHOME PINY¤j®vªº INDEX(...,N(IF({1},...)))
  1. {}=SUM(INDEX(C:C,N(IF({1},LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(C2:C60)),{1,2,3,4})))))
½Æ»s¥N½X
{}=... ªí¥Ü¤½¦¡»Ý¥Î°}¦C¿é¤J(CTRL+SHIFT+ENTER¤TÁä»ô«ö¿é¤J¤èªk)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 4# ML089


    ½Ð°Ý¤W­z¤½¦¡¤¤ªºN¬O¤°»ò·N«ä¡H
   ÁÂÁÂ
hi

TOP

²q¤@²q

{=SUM(SUBTOTAL(9,OFFSET(C1,LARGE(IF((A2:A60="XX")*(B2:B60="KK"),ROW(2:60)),ROW(1:4))-1,))) ...
zz5151353 µoªí©ó 2013-5-24 21:51


ªO¥Dªº¤è¦¡§Ú¸Õ¤F¤§«á¥i¦æ¡AÁÂÁªO¥DÁÙ¦³¦U¦ìªO¤ÍªºÀ°¦£¡C

¤£¦n·N«ä¡A¤p§Ìªº°ÝÃD¤S¦³¶i¤@¨Bªº©µ¦ù¡A¤£¹L§Úı±o¥i¯à­n¥ÎVBA¤~¥i¯à¿ì¨ì¡A¤£¹LÁÙ¬O·Q°Ý°Ý¤j®a·N¨£
¦pªG§Úªº¤@²Õ¸ê®Æ¡A¦@¦³A,,B,C,D¥|­ÓÄæ¦ì¡A§Ú·Q³]©w±ø¥óªºÄæ¦ì¬OA©MB¡A¤@¼Ë¬O·Q¥[Á`²Å¦X±ø¥óªºªñ¥|µ§¸ê®Æ
­YA²Å¦X±ø¥ó«h¨úCÄæ¡A­YB²Å¦X±ø¥ó«h¨úD¡AA¸òBªº±ø¥ó¬O¤¬¥¸ªº¡A¤£·|¦P®Éµo¥Í¡A·Q§â²Å¦X±ø¥óªºªñ¥|µ§CDÄæ¸ê®Æ¥[Á`
§Ú¸Õ¤F¤@¤U¦n¹³µLªk¥[Á`²Å¦X±ø¥ó¦ý¤£¦PªºÄæ¦ì¡AÁÂÁ¤j®a
hi

TOP

¦^´_ 5# 01300607

¦n¹³¤£®e©öÁ¿²M·¡ N/T ¨ç¼Æ¡A
¥ý¬Ý¬Ý³o½g»¡©ú http://club.excelhome.net/forum.php?mod=viewthread&tid=145062
§Ú¥H«e¤]¬d¸ß¬ã¨s¹L¡A«á¨Ó¤S§Ñ°O¤F¤]¤£ª¾¹D«ç»ò»¡©ú¡A¤Ï¥¿´N¬O³o´X©Û¥ý¾Ç°_¨Ó¡A­ì²z¦AºCºC¬ã¨s§a

¦³¿³½ì¦Û¦æ¦A¦h¬d¬d¬ÛÃö¸ê®Æ¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

­pºâ·Ç«h¡G¢ÏÄæ²Å¦X"C"¡A¨ú¢ÑÄæ¡F¢ÐÄæ²Å¦X"X"¡A¨ú¢ÒÄæ¡A²Å¦X³o¢±¶µªÌ¡A¨ú³Ì«á¥|µ§¥[Á`¡C
½Ð±N¥H¤U¤å¦r¶K¦Ü¢Ï¢°¡ã¢Ï¢±¢¯¡A¦A¥H¤å¦r­åªR¬°¢Ï¢Ð¢Ñ¢ÒÄæ¡A
B,X,21,41
A,Y,22,42
C,Z,23,43
B,Y,24,44
B,Z,25,45
C,Z,26,46
A,X,27,47
B,Y,28,48
C,Z,29,49
A,Z,30,50
B,Y,31,51
A,Z,32,52
B,X,33,53
A,Z,34,54
B,Y,35,55
B,Y,36,56
C,Z,37,57
A,Y,38,58
B,Z,39,59
A,Y,40,60

¤½¦¡¡D°}¦C¿é¤J¡G
=SUM(N(INDIRECT(TEXT(LARGE(((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X"),{1,2,3,4}),"[=3]!R1C256;!r00c00"),)))

¸Õ¸Õ¬O§_¾A¥Î¡A¤½¦¡¤£¤Ó¦n»¡©ú¡A¶È¨Ñ°Ñ¦Ò¡I

TOP

¤½¦¡¸ÑªR¡G½Ð¥H¤U¤½¦¡¶K¤JÀx¦s®æ¡AÂù«ö·Æ¹«¥ªÁä¶i¤J½s¿èª¬ºA¡A«ö¢Ô¢¸¬d¬Ý¦U°}¦C­È
=((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X")
=LARGE(((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X"),{1,2,3,4})
=TEXT(LARGE(((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X"),{1,2,3,4}),"[=3]!R1C256;!r00c00")
=N(INDIRECT(TEXT(LARGE(((A1:A20="C")+(B1:B20="X"))*ROW(1:20)*100+3+(B1:B20="X"),{1,2,3,4}),"[=3]!R1C256;!r00c00"),))

R1C256 ¬°Àx¦s®æ¡e¢×¢ä¢°¡f¡A¤@¯ë¬°¨S¥Î¨ìªº¡e¶¢¸mªÅ®æ¡f¡A·í²Å¦X±ø¥óªÌ¤£¨¬¥|µ§®É¡A·|¥H¡e¢×¢ä¢°¡f¬°¥[Á`¹ï¹³¡A¥HÁקK²£¥Í¿ù»~­È¡I

TOP

¤½¦¡¸ÑªR¡G½Ð¥H¤U¤½¦¡¶K¤JÀx¦s®æ¡AÂù«ö·Æ¹«¥ªÁä¶i¤J½s¿èª¬ºA¡A«ö¢Ô¢¸¬d¬Ý¦U°}¦C­È
=((A1:A20="C")+(B1:B20 ...
­ã´£³¡ªL µoªí©ó 2013-5-25 20:02


¤j­ôªº¤½¦¡¯u¬OÅý§Ú¤j¶}²´¬É¡A¤£¹L§Ú¸Õ¤F¤@¤U¦b²Ä¥|­Ó¤½¦¡¥X²{REF!¿ù»~
¨Ì§Ú¥Ø«eªº¥\¤O­n²z¸Ñ³o­Ó¤½¦¡ÁÙ­n¤@¬q®É¶¡
¤£¹LÁÙ¬OÁÂÁ¤j­ô´£¨Ñ¨º»ò¦nªº¸ÑÄÀ©M¸Ñµª
hi

TOP

        ÀR«ä¦Û¦b : Ä@­n¤j¡B§Ó­n°í¡B®ð­n¬X¡B¤ß­n²Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD