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

[µo°Ý] ¦h±ø¥ó­pºâ¼Æ­È

[µo°Ý] ¦h±ø¥ó­pºâ¼Æ­È

¦h±ø¥ó­pºâ¼Æ­È
¦³¤TºØ±ø¥ó¡A¨Ó­pºâ²Å¦X±ø¥óªº¦³´X­Ó¡A¦p¤U¹Ï
½Ð°Ý¦³¤°»ò¨ç¼Æ¥i¥HÀ³¥Î

¦^´_ 1# mark761222


    ½Ð°Ý§A­n°Ýªº¬O¤£¬OCÄæ<14¤Ñ¡ABÄæ=ºØÃþ1¥B¸¹½X¤£­«½ÆªºÁ`¼Æ?
BÄæ±ø¥ó¥i¯à¬°ºØÃþ2.3.4....µ¥?
¥i¬O§A¹Ï¤ùÅã¥Üµ²ªG¬O3?
1001<14¤Ñ¥BºØÃþ1=1
1002>14=0
1003ºØÃþ2=0
1004ºØÃþ2=0
1005<14¥BºØÃþ1=1
§Ú«ç»òºâµ²ªG³£¬O2¡AÁÙ¬O...¤£¬O³o¼Ëºâ?
¤@¤À§V¤O¡A¤@¤À¦¬Ã¬¡C
µo°ÝÃD«e¥i¥H¥ý·j¯Á¤º¤å¬O§_¦³¬ÛÃö½d¨Ò¡C

TOP

¦^´_ 1# mark761222

°Ñ¦Ò¬Ý¬Ý
  1. =SUMPRODUCT(($C$2:$C$10<14)*($B$2:$B$10=1)/(COUNTIF($A$2:$A$10,$A$2:$A$10)))
½Æ»s¥N½X

TOP

SUMPRODUCT((MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)*(B2:B10=1)*(C2:C10<14))
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 2# faye59


    ¹ï! ¬O2 ¤U­±¨â¦ì¤j¤jªº¨ç¼Æ¥i¥H¡A·PÁÂÀ°¦£¦^µª

TOP

¦^´_ 4# hcm19522


    SUMPRODUCT((MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)*(B2:B10=1)*(C2:C10<14))

¥i¥HÀ°¦£»¡©ú¨ç¼Æ³£¥Î·N¶Ü? ¨Ò¦pROW(A2:A10)-1)  ¬°Ô£­n-1 ¨ú³o­Ó­È¥i¥H°µ¤°»ò?

TOP

¦^´_ 4# hcm19522



MATCH(A2:A10,A2:A10,)
½d³ò­È¹ïÀ³½d³ò­Èªº¥Î·N¬O¬°¤°»ò¡H

TOP

¦^´_ 7# mark761222

¥ý»¡µ²½×¦n¤F¡A
¥H³o±ø¨ç¼Æ¨Ó¤ÏõXµ²ªG·|¦³¤@ÂI°ÝÃD¡A
¦]¬°¨ç¼Æ¥»¨­¶È¤ÏÀ³¥X­º¦¸¥X²{½s¸¹±ø¥ó¬O§_²Å¦X¡A
­Y§Ú°µ¥H¤U½Õ¾ã¡A¾ã­Ó­pºâ´N·|²£¥Í¿ù»~¡C


hcm19522¤j¤j¨ç¼Æªº­ì²z¦p¤U
1. MATCH(·j´M¥Ø¼Ð,·j´M½d³ò,·j´M¤èªkºØÃþ) ¡÷ ¥Ø¼Ð¦b½d³ò¤¤ªº¦ì¸m
¦]¬°¸¹½X¥u¯à­«½Æºâ¤@¦¸¡A
©Ò¥H¨Ï¥ÎMatch±N¦U¶µ¸¹½X¦b½d³ò¤¤²Ä¤@¦¸¥X²{ªº§Ç¼Æ¦^õX¥X¨Ó¡A
¦p¹Ï©Ò¥Ü¡C


2. ROW(Àx¦s®æ) ¡÷ Àx¦s®æ¦C¼Æ
¦p¦P¤½¦¡¦r­±¤Wªº·N«ä¡A
¦^¶ÇÀx¦s®æªº¦C¼Æ¡A
¨Ò¦p ROW(C21) ¡÷ 21

3. MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)
¤À¦¨¨â­Ó³¡¤À¸ÑÄÀ¡A
­º¥ý¬O¡u=¡vªº³¡¤À¡A
¬°¤F¶ÈÅý­º¦¸¥X²{ªº¸¹½X¥Í®Ä (¬°¤F¹F¦¨¨C­Ó¸¹½X¥u¯à­«½Æºâ¤@¦¸)¡A
¬G³]¤U±ø¥ó¡u­YMatchªº¦¸§Ç»P¦æ¦C¬Û¦P¤~ºâ¦³®Ä¡v¡C
¦ý¦]¬°¹ê»Úªº½d³ò¬O¡uA2:A10¡v¡A
·N§Y¶¶§Ç²Ä¤@­Ó¦C¼Æ¬O2¡B¶¶§Ç²Ä¤G­Ó¦C¼Æ¬O3¡B¶¶§Ç²Ä¤T­Ó¦C¼Æ¬O4....ªº®t§O¡A
©Ò¥H¤~¸É¤W¡u-1¡v¨Ó½Õ¾ã³o­Ó®t§O¡C
BTW,­Y¥H§Ú¥Ü½d¹Ï¤¤ªº½d³ò¡A«h»Ý­n§ï¦¨¡u-15¡v¡A¦]¬°²Ä¤@¶¶¦ìªº¦C¼Æ¬O16¡C

TOP

¦^´_ 7# mark761222
  1. = SUMPRODUCT((MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)*(COUNTIFS($A$2:$A$10,$A$2:$A$10,$B$2:$B$10,1,$C$2:$C$10,"<14")))
½Æ»s¥N½X
·PÁÂ¥H¤W¨â¦ì¤j¤jªº¤À¨É¡A
§Ú¤~¦³¿ìªk±À¾É¥X³o­Ó¨ç¦¡¡C
¥D­n¤À¦¨¨â­Ó³¡¤À¡A
²Ä¤@­Ó³¡¤À§Ú¦b¤W¤@½g¦^¤å¦³¸Ñ»¡¹L¡A
  1. = MATCH(A2:A10,A2:A10,)=ROW(A2:A10)-1)
½Æ»s¥N½X
Á`µ²´N¬O¤ÏõX¸Ó½d³ò¡u¬O§_¬°­º¦¸¥X²{ªº½s¸¹¡vªº°}¦C¡C
¦pªG±N¸Ó¨ç¦¡¤À°t¦ÜÀx¦s®æ¤¤¡A®ÄªG¦p¤U¡C
Match_01.GIF
  1. =COUNTIFS($A$2:$A$10,$A$2:$A$10,$B$2:$B$10,1,$C$2:$C$10,"<14")
½Æ»s¥N½X
¦Ó¦¹¨ç¼Æ¦b­pºâ¦b½d³ò¤º¦P®Éº¡¨¬¬Û¦P½s½X¡BºØÃþ¬°¡u1¡v¥B¤Ñ¼Æ¥¼º¡14¤Ñ¤T­Ó±ø¥óªº­Ó¼Æ¡C
¤]¬O¤@¼Ë¦^¶Ç¸Ó½d³ò­pºâµ²ªGªº°}¦C¡A
¦pªG±N¸Ó¨ç¦¡¤À°t¦ÜÀx¦s®æ¤¤¡A®ÄªG¦p¤U¡C
Match_02.GIF

©Ò¥Hºî¦X¥H¤W¨âÂI¡A
§Y¥i±o¨ì¸Ó½s¸¹¦b½d³ò¤¤²Å¦X±ø¥óªº­Ó¼Æ¡A
³Ì«á¦A¥ÎSUMPRODUCT±N°}¦C¤¤ªºµ²ªG¬Û¥[¡C

TOP

¦^´_ 9# a5007185
¤j¤j:·ÓµÛ§Aªº¤è¦¡°µ¤F¤@¹M¡A¸ÕµÛ¤F¸Ñ¡A¦ý§Ú¥X²{ªº¦n¹³¸ò§A¤£¤@¼Ë

TOP

        ÀR«ä¦Û¦b : ¡i¥Í©R¦b©I§l¶¡¡j¦òªû»¡¡G¡u¥Í©R¦b©I§l¶¡¡C¡v¤HµLªkºÞ¦í¦Û¤vªº¥Í©R¡A§óµLªk¾×¦í¦º´Á¡AÅý¦Û¤v¥Ã¦í¤H¶¡¡C¬JµM¥Í©R¥h¨Ó³o»òµL±`¡A§Ú­Ì§óÀ³¸Ó¦n¦n¦a·R±¤¥¦¡B§Q¥Î¥¦¡B¥R¹ê¥¦¡AÅý³oµL±`¡BÄ_¶Qªº¥Í©R¡A´²µo¥¦¯uµ½¬üªº¥ú½÷¡A¬M·Ó¥X¥Í©R¯u¥¿ªº»ù­È¡C
ªð¦^¦Cªí ¤W¤@¥DÃD