数字最多33个,最大是33,CY4:CY9中数字按重复数多少在DD3行开始重新排列且重复数大于1,请见图。求修改简化=IFERROR(SUBSTITUTE(TEXT(LARGE(IFERROR(--(TEXT(FREQUENCY(FILTERXML("<a><b>"&SUBSTITUTE(TRIM(SUBSTITUTE(PHONETIC($CY4:$CY9),","," "))," ","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(TRIM(SUBSTITUTE(PHONETIC($CY4:$CY9),","," "))," ","</b><b>")&"</b></a>","//b")),"[>0]0;")*100+FILTERXML("<a><b>"&SUBSTITUTE(TRIM(SUBSTITUTE(PHONETIC($CY4:$CY9),","," "))," ","</b><b>")&"</b></a>","//b")),""),COLUMN(A1)),"0个00"),"个0","个0"),"")
å ä¸ºä½ ä¸ä¸ªåå æ ¼æ33个æ°åï¼å ±æ6个åå æ ¼ï¼è¦ä¸ä¸æ¾åºå¹¶æåºç°æ¬¡æ°å¤å°æåºï¼è¿æ¬æ¥å°±æ¯ä¸ä¸ªå¾å¤æçé®é¢ãæé®ä¸çå ¬å¼å·²å¾ä¸éäºãç°å¨ç»ä½ æä¾ä¸ä¸ªå ¬å¼ï¼ä½ä¹è¿æç¹äºå¤æçãï¼
CZ4=TEXT(MOD(LARGE(FREQUENCY(--TRIM(MID(TEXTJOIN(" ",,$CY4:$CY9),ROW(1:198)*3-2,3)),ROW(1:33))*1000+ROW(1:34)*(FREQUENCY(--TRIM(MID(TEXTJOIN(" ",,$CY4:$CY9),ROW(1:198)*3-2,3)),ROW(1:33))>0),COLUMN(A:A)),1000),"00;;;")
æ
CZ4=TEXT(MOD(LARGE(FREQUENCY(--TRIM(MID(TEXTJOIN(" ",,$CY4:$CY9),ROW(1:198)*3-2,3)),ROW(1:33))*1000+ROW(1:34)*ISNUMBER(FIND(TEXT(ROW(1:34),"00"),TEXTJOIN(" ",,$CY4:$CY9))),COLUMN(A:A)),1000),"00;;;")
å ¬å¼ç¨çå 个å符ã
ä¸¤ä¸ªå ¬å¼ä¸ç
FREQUENCY(--TRIM(MID(TEXTJOIN(" ",,$CY4:$CY9),ROW(1:198)*3-2,3)),ROW(1:33))>0
æ
ISNUMBER(FIND(TEXT(ROW(1:34),"00"),TEXTJOIN(" ",,$CY4:$CY9)))
æ¯ä¸ºäºä½¿æ²¡åºç°çæ°åä¸å ä¸row(1:34)ï¼ä»èå®ç°æç»æ¾ç¤ºç©ºç½ã
ä¸æå°åºç°ç©ºç½åå æ ¼
æªå¾ä¸ç¬¬6è¡ååºäºå个æ°ååºç°ç次æ°ã
æµè¯æé®ä¸çå ¬å¼ï¼å¾åºçç»æä¸æ¬äººçå ¬å¼ç»æä¸ä¸è´ãä½æåæ°ååºç°ç次æ°ï¼æ¬äººæä¾çå ¬å¼ä¸æ»ç个æ°ä¸º198个ââSUM(C6:EF6)ï¼ä¸æ°æ®ä¸ªæ°ä¸è´ãé¢ä¸»è¯·èªå·²æµè¯ä¸ãå ¬å¼æ¯å¦æ»¡è¶³è¦æ±ã
追é®ä¸ç¥é为ä»ä¹ ææå
¬å¼å¤å¶è¿å»æ¾ç¤ºä¸ºï¼
#NAME?
Excelæ¯ä»ä¹çæ¬ï¼æ¤å¤ï¼åçä¸æä¾çå ¬å¼æ¯æ°ç»å ¬å¼ï¼è¾å ¥æç²è´´å ¬å¼åï¼è¦åæ¶æCtrl+Shift+Enterä¸é®è¾å ¥æ°ç»å ¬å¼ã