Board logo

¼ÐÃD: ¤À²Õ±Æ§Ç [¥´¦L¥»­¶]

§@ªÌ: chin15    ®É¶¡: 2012-9-7 21:22     ¼ÐÃD: ¤À²Õ±Æ§Ç

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-9-10 21:51 ½s¿è

³o­Ó¤½¦¡ÀYµh¤F¦n¤[¡A²{§â¸ê®Æ²¤Æ¤@¤U¡AÀµ½Ð°ª¤H«ü¾É¡C[attach]12403[/attach]
§@ªÌ: Hsieh    ®É¶¡: 2012-9-8 01:48

¦^´_ 1# chin15

­Y¦³¸s²Õ±Æ¦C¸Õ¸Õ¬Ý

    [attach]12409[/attach]
§@ªÌ: chin15    ®É¶¡: 2012-9-8 10:54

¦hÁ¶Wª©¦^ÂÐ
¥i¯à§Ú»¡±o¤£°÷²M·¡¡A¤£¬O©w¸q¦WºÙ¤À²Õ
§Ú·Q¥Î°}¦C¤½¦¡ªí²{¡A¦Ó¥B¤£¤U©Ô
¦P®É¿ï¨úd2:d18¡A¿é¤J¤½¦¡«á«öctrl+shift+enter
³o¼Ë±o¨ìªº°}¦C¥i§@¨ä¥L¥Î³~
³o­Ó¤½¦¡¹ï§ÚÃø«×»á°ª¡A¥Ø«e¯Z§OÄæ¬O°²¦X¨Ö¡A¦pªG¬O¯u¦X¨Ö¤½¦¡¤S¸Ó¦p¦óªí²{¡H
¦hÁ­@¤ß«ü¾É¡C
§@ªÌ: ML089    ®É¶¡: 2012-9-8 17:11

¦^´_ 1# chin15

¥»¥H¬°±Æ¦W¦¸À³¸Ó¬O«Ü²³æ¡A¥J²Ó¬ã¨s§Aªº±Æ¦W¤è¦¡ÁÙº¡¤£¦P¡AÁÙ¨S¦³¬ã¨s¥X¨Ó¡C

¨ä¤¤¦X¨ÖÀx¦s®æ¸Ì¨C®æ³£¦³­È¡A­ËÅý§Ú«Ü¦³¿³½ì¡A¥H«e§ä¹Lºô¸ô¨S§ä¨ì¦p¦ó³]©w¡A§Æ±æ§A¯àÀ°§Ú¦^µª¦¹°ÝÃD¡A
ÃD¥Øµo°Ý¦p¤U
http://forum.twbts.com/viewthread.php?tid=7748&extra=
§@ªÌ: Hsieh    ®É¶¡: 2012-9-9 00:57

¦^´_ 3# chin15
­nª½±µ¨ú¥X¤À²Õ¤º¤£­«½Æ¤À¼Æ±Æ¦W¤w¸gÅý¤H¶Ë³z¸£µ¬¡A­Y¦A¥[¤W¦X¨ÖÀx¦s®æ°ÝÃD¡A¬Û«H¤º«Ø¨ç¼Æ¬OµLªk¹F¦¨
¯Âºé¦nª±¼g­Ó¦Û©w¸q¨ç¼Æ¡A¨ú±o±Æ¦W°}¦C
[attach]12420[/attach]
§@ªÌ: ML089    ®É¶¡: 2012-9-9 11:36

¦^´_ 3# chin15

­«½Æ¦W¦¸¤£¸õ¸¹(1¡B1¡B2¡B3¡B3¡B4¡B5 ...)ªº±Æ¦W


¯Z§OÄæ¬O°²¦X¨Ö¡A¥i¥H±Ä¥Î MATCH(A2:A18,A2:A18,)*10^5+C2:C18 ´N¥i¥H­«·s¤ÀÃþ½s¸¹
±N­«½Æ¤À¼ÆÂo°£¡A¥i¥H±Ä¥Î FREQUENCY(¤ÀÃþ½s¸¹,¤ÀÃþ½s¸¹)>0 Âo°£­«½Æ¤À¼Æ»P¶Q¦¡¦X¨Ö¨Ï¥Î
¶Q¦¡«Øij­×§ï¬°¡AMMULT((...)*(...)*TRANSPOSE(INDEX(Âo°£­«½Æ¤À¼Æ,ROW(1:17))), ...)

¥þ¿ï¦¡°}¦C¿é¤J¡A¦P®É¿ï¨úd2:d18¡A¿é¤J¤½¦¡«á«öctrl+shift+enter


¯Z§OÄæ¬O¯u¦X¨Ö®É¡A­«·s¤ÀÃþ½s¸¹§Ú¥¿¦b¬ã¨s¤U¦¡
LOOKUP("ùÜ",INDIRECT("A2:A"&ROW(2:18)),ROW(2:18))*10^5+C2:C18
³æ¿W´ú¸Õ®ÉOK¡A¤@²Õ¦X«á´N²£¥Í¿ù»~¡A«áÄòÁÙ­n¬ã¨s...¡A¥ýPO¥X¨Ó¤]Åý©p·Q¤@·Q¡C
§@ªÌ: ML089    ®É¶¡: 2012-9-9 17:29

¦^´_ 1# chin15

¨âºØ¸Ñªk´£¨Ñ°Ñ¦Ò
¸Ñ1 : ª©¤j­ì¦¡ + FREQUENCY Âo°£­«½Æ¤À¼Æ
¸Ñ2 : ±Ä¥Î MATCH + SMALL ¤£­«½Æ¤À¼Æ±Æ§Ç ¸Ñªk
[attach]12428[/attach][attach]12428[/attach]
§@ªÌ: chin15    ®É¶¡: 2012-9-9 19:23

¯u¬O­n±o¡I¤F¤£°_¡I¨¯­W¤F¡A¦¬¤U¾Ç²ß¡CÁÂÁ¡I
¥ý¶i­Y¦³®É¶¡¤]½Ð¤@¨Ö«ü¾Éa¦C¯u¦X¨Ö®Éªº³B²z¡C
§@ªÌ: ML089    ®É¶¡: 2012-9-10 13:00

¦^´_ 8# chin15

¯Z§OÄæ¬O¯u¦X¨Ö®É¡A­«·s¤ÀÃþ½s¸¹¥i¥Î¤U¦¡
MATCH(ROW(2:19),IF(A2:A19<>"",ROW(2:19)))
µ{¦¡ºc¬[¦P«e

½d¦CÀɮצp¤U
[attach]12436[/attach]

PS: ¾A¥Î©ó ¯Z§OÄæ¬O¯u¦X¨Ö®É
§@ªÌ: chin15    ®É¶¡: 2012-9-10 20:04

¥»©«³Ì«á¥Ñ chin15 ©ó 2012-9-10 20:06 ½s¿è

·PÁ·PÁ¡A¨¯­W¤F¡A¤½¦¡¦n½ÆÂø¡A§Ú±o¤À¸Ñ¶}¨ÓºCºC²z¸Ñ¾Ç²ß¡C
¯uªº¤£¬O´¶³qªº¼F®`¡A¨Ø¨Ø¤§¦Ü¡C

¥t¥~·Q½Ð±Ðª©¥D¡A§Ú§ä¤£¨ì¥i¥Hµû¤Àªº¦a¤è¡A¬O¤£¬O¥u¦³ª©¥D¥i¥Hµû¤À¡H
§@ªÌ: BigDog    ®É¶¡: 2012-11-27 20:45

¦^´_  chin15

­Y¦³¸s²Õ±Æ¦C¸Õ¸Õ¬Ý
Hsieh µoªí©ó 2012-9-8 01:48


½Ð±Ð¶Wª©¤j:
    SUMPRODUCT((a>C2)*(COUNTIF(a,a)>1))/2
¬õ¦â¦rÅé§Ú¤£¤Ó©ú¼I,§Ú¬Ýaªº¦WºÙµ¥©óÁ`¤Àªº¼Æ­È,"a>c2"³oÅÞ¿è³£¬Ofalse,"countif(a,a)>1"¤]³£µ¥©ófalse.
©Ò¥Hsumproduct(false*false)/2=0,¥i¥H½Ð¶Wª©¤j¸ÑÄÀ¤@¤U¶Ü¡H³o­Ó¦a¤è§Ú­þ¸Ì§PÂ_¿ù»~¡HÁÂÁ¡I
§@ªÌ: Hsieh    ®É¶¡: 2012-11-27 21:44

¦^´_ 11# BigDog
©w¸q¦WºÙa¬O¨ú±o¨C¯ZªºÁ`¤À½d³ò
SUMPRODUCT((a>C2)*(COUNTIF(a,a)>1))
¥H¤@¦~¤@¯Z¬°¨Ò¡Aaªº½d³ò¬OC2:C4
a>C2´ú¸Õ¨C­ÓÁ`¤À¤ñC2¤jªº¼Æ¦³C3¡BC4
©Ò¥H±o¨ì¤@­Ó°}¦C{TRUE,FALSE,FALSE}
COUNTIF(a,a)>1«h¬O­pºâa½d³ò¤º¨C­Ó¼Æ値¥X²{¦¸¼Æ
±o¨ì°}¦C{1,1,1}>1´N¦¨¤F{FALSE,FALSE,FALSE}
SUMPRODUCT({TRUE,FALSE,FALSE}*{FALSE,FALSE,FALSE})
´N¦¨¤FTRUE*FALSE+FALSE*FALSE+FALSE*FALSE
=0+0+0
=0
§@ªÌ: BigDog    ®É¶¡: 2012-11-29 17:52

¦^´_  BigDog
©w¸q¦WºÙa¬O¨ú±o¨C¯ZªºÁ`¤À½d³ò
SUMPRODUCT((a>C2)*(COUNTIF(a,a)>1))
¥H¤@¦~¤@¯Z¬°¨Ò¡Aa ...
Hsieh µoªí©ó 2012-11-27 21:44


©çÁÂ,¦A¦¸½Ð±Ð¶Wª©¤j,
°ÝÃD¤@:
¥H¤@¦~¤@¯Z¬°¨Ò¡Aaªº½d³ò¬OC2:C4, a>C2´ú¸Õ¨C­ÓÁ`¤À¤ñC2¤jªº¼Æ¦³C3¡BC4.
¥H¤@¦~¤T¯Z¬°¨Ò¡Aaªº½d³ò¬OC9:C13, a>C9¨S¥b­Ó,¨º¤½¦¡´N³£¦¨¬°false,³o¸Ì§ÚªºÆ[©À­þ¸Ì¦³°ÝÃD¡H
°ÝÃD¤G:
¹ÏÀÉÂŦâ¦rÅ骺¤½¦¡ÀHµÛÀx¦s®æ¦Ó§ïÅÜ,¨º¤½¦¡´N·|¤@ª½¤£¦¨¥ß?
[attach]13334[/attach]
ÁÂÁÂ!!
§@ªÌ: Hsieh    ®É¶¡: 2012-11-29 21:50

¦^´_ 13# BigDog


    ¨S¿ù°Ú!
³o¬O­n¥[¶i¨Ó¤ñCÄæ¤jªº¼Æ値­Ó¼Æ¡AC9¬OC9:C13¤¤³Ì¤j­È
©Ò¥H¡A±N¤º«ØRANK¨ç¼Æ¨ú±oªº±Æ¦W´î¥h¤ñ¸Ó¦CCÄæ¤jªº­Ó¼Æ´N¬O¥¿½T¤£­«½Æ±Æ¦W
§@ªÌ: BigDog    ®É¶¡: 2012-11-30 14:41

¦^´_ 14# Hsieh


    ¸ê½è¶w®©,·PÁ¦^µª. ^^"
§@ªÌ: BigDog    ®É¶¡: 2012-12-3 16:46

¦^´_ 14# Hsieh

½Ð°Ý¶Wª©¤j
    (C2:C18<=TRANSPOSE(C2:C18)  => ³o¤½¦¡¤£¬O¥þ³£TRUE¶Ü¡H ¤£¬O«ÜÀ´,¬°¤°»ò¦³¨Ç·|ÅÜFALSE.
    (A2:A18=TRANSPOSE(A2:A18)     => ³o¤½¦¡¤£¬O¥þ³£TRUE¶Ü¡H ¤£¬O«ÜÀ´,¬°¤°»ò¦³¨Ç·|ÅÜFALSE.


    [attach]13382[/attach]

ÁÂÁ¶Wª©¤j.
§@ªÌ: BigDog    ®É¶¡: 2012-12-3 23:19

¦^´_ 1# chin15


    chin15¤j¤j±z¦n:
                          ¯à§_À°§Ú¸ÑÄÀ16fªº°ÝÃD¶Ü¡HÁÂÁÂ!!
§@ªÌ: BigDog    ®É¶¡: 2012-12-4 16:50

¦^´_  chin15


    chin15¤j¤j±z¦n:
                          ¯à§_À°§Ú¸ÑÄÀ16fªº°ÝÃD¶Ü¡HÁÂÁÂ!!
BigDog µoªí©ó 2012-12-3 23:19



    ¤w¸g¬ÝÀ´¤F@@"
ªá¤F¤@¾ã¤Ñªº®É¶¡,¦ý­n¹ê»Ú¹B¥ÎÁÙ¯u¦³¨Ç§xÃø,­n¦h¦h½m²ß,ÁÂÁ¦U¦ìªº¸Ñ»¡....
§@ªÌ: pigchen    ®É¶¡: 2013-3-12 09:46

ÁÂÁª©¥D¾A®É¸Ñ¨M§ÚªººÃ°Ý
§@ªÌ: chhars    ®É¶¡: 2014-12-11 12:11

¦¨¥ß»P¤£¦¨¥ß¥i¥H¦^¶Ç¦¨¤å¦r©Î¼Æ­È
³o¼Ë±o¥Xªº¸ê®Æ¡A¤§«áÁÙ¯à°÷®M¤J¨ä¥LªºªF¦è
§@ªÌ: aoao001    ®É¶¡: 2015-7-27 08:39

¸Õ¸Õ¬Ý¡A¬ã¨s¤@¤U

¬Ý¬Ý¦p¦ó¯à¸Ñ¨M
§@ªÌ: hcm19522    ®É¶¡: 2015-10-24 14:32

http://blog.xuite.net/hcm19522/twblog/351915358
§@ªÌ: hcm19522    ®É¶¡: 2015-10-24 19:13

¥»©«³Ì«á¥Ñ ML089 ©ó 2015-10-24 19:27 ½s¿è

D2:D18{=SUM(((((MATCH(MATCH(A$2:A$18,A$2:A$18,)*100+C$2:C$18,MATCH(A$2:A$18,A$2:A$18,)*100+C$2:C$18,)=ROW(B$2:B$18)-1)*C$2:C$18)*(MATCH(A$2:A$18,A$2:A$18,)=MATCH(A2,A$2:A$18,)))>C2)*1)+1
§@ªÌ: hcm19522    ®É¶¡: 2015-10-25 15:04

=SUMPRODUCT((((MATCH(A$2:A$18&C$2:C$18,A$2:A$18&C$2:C$18,)=ROW(B$2:B$18)-1)*(A$2:A$18=A2)*C$2:C$18)>C2)*1)+1
§@ªÌ: hcm19522    ®É¶¡: 2015-10-25 15:12

ª©¤j¸ê®Æ AÄæÁö¸óÄæ¸m¤¤ ¦ýA3,A4»PA2¦P ,A6,A7,A8»PA5¦P...«DªÅ¥Õ ¥t 22#¦³­×§ï¼W¦C Á`©M¤½¦¡
http://blog.xuite.net/hcm19522/twblog/351915358




Åwªï¥úÁ{ ³Â»¶®a±Ú°Q½×ª©ª© (http://forum.twbts.com/)