excel **²éÕÒ

ʱ¼ä£º2019.04.29 ·¢²¼ÈË£ºhezhong2016

excel **²éÕÒ

Òѽâ¾öÎÊÌâ

¹È¸èhezhong2016Óû§ÔÚ2019.04.29Ìá½»Á˹ØÓÚ¡°Öªºõexcel **²éÕÒ¡±µÄÌáÎÊ£¬»¶Ó­´ó¼ÒÓ¿Ô¾·¢±í×Ô¼ºµÄ¹Ûµã¡£Ä¿Ç°¹²ÓÐ4¸ö»Ø´ð£¬×îºó¸üÐÂÓÚ2024-09-04T22:09:45¡£

ÏëÒªÔÚÁíÒ»Õűí¸ñÖеõ½ÈçϽá¹û£º


Ó¦¸ÃÓÃʲôº¯Êý£¬Ð»Ð»¡£

Ï£Íû´ó¼ÒÄܹ»°ïÖúËý¡£

ÏêϸÎÊÌâÃèÊö¼°ÒÉÎÊ£º

ÏëÒªÔÚÁíÒ»Õűí¸ñÖеõ½ÈçϽá¹û£º


Ó¦¸ÃÓÃʲôº¯Êý£¬Ð»Ð»¡£

ÆÚ´ýÄúµÄ´ð°¸£¬Ð»ÁË,Ï´ÎÓÐʶù±ðÍüÁËÕÒÎÒ £¡
Ï£ÍûÒÔϵĻشð£¬Äܹ»°ïÖúÄã¡£

µÚ1¸ö»Ø´ð

Óû§Ãû£ºGalumph_Tom  

Ч¹ûͼÈçÏ£º

Í¿É«²¿·ÖÊÇÔ´**

C1µ¥Ôª¸ñÊäÈëÊýÖµ¹«À´×Ôʽ£º

=IFERROR(INDEX($A$1:ÖµÉú¹ÜÑÐÓÐ$A$21,SMALL(IF(MATCh**(A$2:Í£ÈõÏÊA$21,A$2:A$21,)=ROW($2:$21)-1,ROW($2:$21)),ROW(A1))ÎÊ´ð)&"","")

°´Ctrl+shift+enter½áÊø¹«Ê½

ÏÂÀ­¹«Ê½µÃµ½²»Öظ´Ãû³ÆµÄCÁÐ


D1µ¥Ôª¸ñÊäÈëÄ¿ÉèÄÇÊýÖµ¹«Ê½£º

=INDEX($B:$B,SMALL(I°ÑÃ×Àý½»Ö»²¨F($A$1:$À©ÈýÎöÆÀÑéÌâ¼´Ê**ÖÓÑÔºA$100=$C1,ROW($1:$100),1000),COLUMN(A1)))&""

°´Ctrl+shift+enter½áÊø¹«Ê½

ºáÀ­¹«Ê½ºÍÏÂÀ­¹«Ê½µÃµ½D,E,F,GÁÐ**

µÚ2¸ö»Ø´ð

Óû§Ãû£ºÐÛÓ¡  

=IF(COUNTIF(Sheet1!$A:$A,$A1)<COLUMN(A1),"",OFF**T(Sheet1!$A$1,´¬¸´ËãÑôÖÃSMALL(IF(Sheet1!$A:$A=$A1,ROW(Sheet1!A:A),9ÔÆÖÜÃ÷Á¿Î¢^9),COLUMN(A1))-1,1))

=IF(COUNTIF($A:$A,$A1)<COLUMN(A1),"",OFF**T($A$1,SMALL(IF($A:$A=$A1,ROW(A:A),9^9),COLUMN(A1))-1,1))


µÚ3¸ö»Ø´ð

Óû§Ãû£ºÍ»Í»ÍÃ2019  

ÍøÓÑ¡¾Galumph_Tom¡¿¹«Ê½²»¶Ô

µÚ4¸ö»Ø´ð

Óû§Ãû£ºÒ²¸ã²»¶®  

¿ÉÒÔʹÓÃVlookupº¯Êý