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

[µo°Ý] §ä¡u¥X²{¦¸¼Æ³Ì¦h¡vªº¨ç¼Æ

[µo°Ý] §ä¡u¥X²{¦¸¼Æ³Ì¦h¡vªº¨ç¼Æ

½Ð±Ð«e½ú­Ì¤@­Ó°ÝÃD¡G
°²³]¦³¤@¸ê®Æªí¡A¨ä¤º®e¬°¤å¦r«¬ºA¡A¨Ò¦pN¡BE¡BW¡BS¡BNE¡BNW¡Kµ¥¤è¦ì¸ê®Æ¡C
­Y­n§ä¥X¸ê®Æªí¤¤¥X²{³Ì¦h¦¸ªº¤º®e¡Aexcel¬O§_¦³¾A¦Xªº¨ç¼Æ©O¡H
¡]§Úgoogle¤F¤@¤U¡A§ä¨ì¤@­Ómode()¨ç¼Æ¡A¦ý¸Ó¨ç¼Æ¦ü¥G¥u¯à³B²z¼Æ¦r«¬ºAªº¸ê®Æ¡C¡^
ÁÂÁÂ ^^

¦^´_ 1# rosebud


=INDEX(A:A,MODE(MATCH(A1:A99,A:A,)))
°}¦C¤½¦¡¡A¥²¶·¥HCTRL+SHIFT+ENTER¤TÁä»ô«ö¤è¦¡¿é¤J¤½¦¡

A1:A99½Ð¨Ì½d³ò­×§ï
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_  rosebud


=INDEX(A:A,MODE(MATCH(A1:A99,A:A,)))
°}¦C¤½¦¡¡A¥²¶·¥HCTRL+SHIFT+ENTER¤TÁä»ô«ö¤è ...
ML089 µoªí©ó 2014-10-28 17:55


ÁÂÁ¡A¥i¥H¥Î¤F ^^
¥H¤U¬O§Ú¹ïª©¥D¤j¤H´£¨Ñªº¤½¦¡ªº²z¸Ñ¡A¤£¾å±o¹ï¤£¹ï
==============================================================
MATCH(A1:A99,A:A,)                                           -->·|§âA1:A99ªº¤º®eÂন¹ïÀ³¨ìA:A¤¤ªº§Ç¸¹¡]¼Æ¦r¡^
MODE(MATCH(A1:A99,A:A,))                            -->§ä¥X¥X²{¦¸¼Æ³Ì¦hªº§Ç¸¹
INDEX(A:A,MODE(MATCH(A1:A99,A:A,)))      -->Âà´«¬°¸Ó§Ç¸¹¹ïÀ³¤§¤å¦r¤º®e
===============================================================
¥u¬O§ÚÁÙ¦³¤@­Ó°ÝÃD¡G
¤W­zªº¤½¦¡¥i¥¿±`®M¥Î¦b¤@ºû°}¦C¤W¡A¨º¤Gºûªºªí®æ¸ê®Æ­n«ç¼Ë³B²z©O¡H
¨Ò¦p¦³¤@¸ê®Æ¥H®É¶¡¡]24¤p®É¡^¬°¾î¶b¡F¤é´Á¡]31¤Ñ¡^¬°Áa¶b§@ªí,
¨º­n«ç»ò¹ï¥¦­Ì±Æ§Ç¡B­pºâ¡A¶i¦Ó¹ïÀ³¥X¹ê»Ú¤º®e­È¡H
¡]¸Ó¸ê®Æªí¦pªþ¥ó©Ò¥Ü¡^ test.zip (15.1 KB)
ÁÂÁÂ:)

TOP

¦^´_ 3# rosebud

°ÝÃD¤@
MATCH ¥i¥H§ä¨ì¬Û¦Pªº¦WºÙªº²Ä1­Ó¥X²{¦ì¸m(¦C¸¹)
MODE§ä¥X³Ì¦h¬Û¦Pªº¦ì¸m(¦C¸¹)
INDEX¨úªº¦C¸¹¦ì¸mªº¸ê®Æ

°ÝÃD¤G
AB2 =INDIRECT(TEXT(MOD(MAX(COUNTIF(B2:Y32,B2:Y32)*10^6+ROW(2:32)*10^3+COLUMN(B:Y)),10^6),"!R0!C000"),)
°}¦C¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦pªG¥X²{¦¸¼Æ³Ì¦hªº¤º®e¦³¤G­Ó¥H¤Wªº¸Ü¡A¨º­n¦p¦ó³]©w¨ç¼Æ©O¡H
¢²¢ù

TOP

¦^´_ 5# q1a2z5
AB2 =INDIRECT(TEXT(MOD(MAX(COUNTIF(B2:Y32,B2:Y32)*10^6+ROW(2:32)*10^3+COLUMN(B:Y)),10^6),"!R0!C000"),)

¦³¦h­Ó®É

MAX()§ï¬° LARGE(.... , ROW(A1)) ¡A¨C¤U©Ô¤@­Ó´N§ä¥X¤@­Ó
IF(ROW(A1)>COUNT(0/(MAX(COUNTIF(B2:Y32,B2:Y32))=COUNTIF(B2:Y32,B2:Y32))), "",  ...) ±±¨î¦³´X­Ó

AB2 =IF(ROW(A1)>COUNT(0/(MAX(COUNTIF(B2:Y32,B2:Y32))=COUNTIF(B2:Y32,B2:Y32))), "",  INDIRECT(TEXT(MOD(LARGE(COUNTIF(B2:Y32,B2:Y32)*10^6+ROW(2:32)*10^3+COLUMN(B:Y)),row(A1)),10^6),"!R0!C000"),)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

http://blog.xuite.net/hcm19522/twblog/511796229
3272.png
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD