如图所示:B2:K25是数据区域,B$1:K$1是固定的,A列的单元格数据是要查找的,L列是查找的结果。
A2=56789在数据区域B2:K2出现的数据是5、7、5、6、6,其中最大数是7,对应的值是6,那么L2=6才是我要求的结果。即查找A列单元格内的每个数字在数据区域内出现最大数是哪个,并最大数对应值列出来。
åé®é¢å é¤äºï¼ä¸ååç»ä½ æä¾åçåï¼å°±æ¾ä¸å°é®é¢äºãä¸åæ交åçåï¼æ¬æ¥è¿ç®è¡¥å ä¸çï¼å´æ¾ä¸å°äºã
å¦æExcelçæ¬æ¯365æ2019ï¼å ¬å¼æ¯è¾ç®åå¦æªå¾ä¸çMåï¼
M2=TEXTJOIN(,,IF(B2:K2=MAX(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2),B$1:K$1,""))
åæ¶æCtrl+Shift+Enterä¸é®è¾å
¥æ°ç»å
¬å¼ï¼ä¸æã
å¦æä¸çæ¬ä½ä¸äºï¼å ¬å¼ä¹è¦å¤æäºï¼å¦æªå¾ä¸Låï¼
L2=IF(B2=MAX(B2:K2),0,"")&SUBSTITUTE(SUM(((B2:K2=MAX(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2))*B$1:K$1)*10^(10-COLUMN(A:J))),0,)
åæ ·æ°ç»å ¬å¼ã
ä¸åçåçä¸ï¼æ²¡ææä¾åä¸ä¸ªå
¬å¼ï¼æ¬æ¥å°±æ¯è¡¥å
è¿ä¸ªå
¬å¼çã
ä½ å¥½ï¼æè¯ç¨äºä¸¤ä¸ªå ¬å¼ï¼é½åºç°åæ ·çé®é¢ï¼åé¢å 个没é®é¢ï¼åé¢å°±å¼å§åºç°æ··ä¹±ãçæªå¾æç½äº
åé¢çå
¬å¼å¿½è§äºä¸ä¸ªå°é®é¢ï¼è¡¥å
ä¸ä¸ªæ¡ä»¶å³å¯ï¼
L2=IF((B2=MAX(B2:K2*ISNUMBER(FIND(B$1:K$1,A2))))*(LEFT(A2)="0"),0,"")&SUBSTITUTE(SUM(((B2:K2=MAX(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2))*B$1:K$1*(ISNUMBER(FIND(B$1:K$1,A2))))*10^(10-COLUMN(A:J))),0,)
M2=TEXTJOIN(,,IF(B2:K2=MAX(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2)*ISNUMBER(FIND(B$1:K$1,A2)),B$1:K$1,""))
é常æè°¢ï¼é®é¢å¾å°è§£å³äºï¼ç°å¨ææå ¬å¼ç¨å°æéè¦çå°æ¹æ¯å°±åºç°é®é¢äºï¼æä¸æç½å ¬å¼
ä¸çCOLUMN(A:J)表示ä»ä¹ææï¼
大ç¥ï¼å¸®æçä¸æªå¾éçå
¬å¼åªéåºç°é®é¢äº?
COLUMN(A:J)ä¸çA:Jå¯ä»¥ç¨å ¶å®æ¥ä»£æ¿åï¼è¿æ ·çè¯å¯ä»¥éææ¢è½¬å°å ¶å®è¡¨æ ¼é
追çCOLUMN(A:J)ä¿æä¸åè¯è¯ã
åä½ è¯´ä¸å
¬å¼ç大ä½æè·¯ï¼ç¨ç¬¬1è¡ï¼ä½ çå®é
æ°æ®æ¯ç¬¬3è¡ï¼ç0å°9å¨Aåï¼ä½ çå®é
æ°æ®æ¯Eåï¼ä¸ææ¥æ¾ï¼æ¾å°çå°±æ¯æ°åï¼ISNUMBERï¼ï¼è¿ä¸ªISNUMBERä¸è¯¥è¡ä¸çæ°åç¸ä¹ï¼è¿æ ·ç¬¬3è¡çæ°åå¨Eåä¸æçï¼å°±å¾å°æ°åï¼æ²¡æçå°±å¾å°0ï¼åç¨MAXæ±åºæ大æ°ãç¶å该è¡æ°åçäºè¿ä¸ªæ大æ°çï¼åé¢çåçåªèèå°è¿ä¸æ¥ï¼å°±æ¯åå¾æ°åï¼å®é
è¿è¦å ä¸ä¸ä¸ªæ¡ä»¶ï¼ä¸ç¬¬3è¡çæ°åå¨Eåæ¾å°çï¼åé¢çåç忽è§äºè¿ä¸ç¹ï¼ï¼ä¸¤ä¸ªæ¡ä»¶é½æ»¡è¶³ï¼å°±å该æ°åï¼å¦åå0ï¼æååå«ä¹ä¸10çN次æ¹ï¼Nä»å³å°å·¦åå«ä»0å°9ï¼è¿æ ·åæ¯ä¸ºSUMååå¤ï¼æä¸åæ°ä½ä¸çæ°åæ©å¤§å°10çN次æ¹ãç¨SUMæ±ååï¼ä¸é´ä¼æå¾å¤0ï¼ä¹æ£å¥½æ¯ä¸åæ°çä½ç½®ä¸ï¼ï¼ç¨SUBSTITUTEæè¿äº0å»æä¹ãåé¢ç0åç¬ç¨äºå
¬å¼è¿è¡å¤çï¼åºè¯¥æ¯è¾å®¹æç解ã
ä»ä¸å¯ç¥ã10-COLUMN(A:J)ï¼å°±æ¯è¦å¾å°9ã8ã7ï¼ç´å°0ãæä»¥ä½ æå
¬å¼æ¬å°åªæ¯10-COLUMN(A:J)ï¼ä¸è¦éä½ç½®åå¨ã
ä¸ç¨10-COLUMN(A:J)å½ç¶å¯ä»¥çï¼å°±ç¨{9ï¼8ï¼7ï¼6ï¼5ï¼4ï¼3ï¼2ï¼1ï¼0}
åï¼è®²è§£çé常详ç»ï¼è°¢è°¢ï¼å¦ææè¦å¨è¿åºç¡ä¸åæå°çï¼æ¯ä¸æ¯ç¨MAXæ¹MINå°±å¯ä»¥äºï¼å¦æéå第äºå¤§(åé¢æ¯åæ大ç)çæ°å¼ï¼è¯¥å¦ä½ç¨å ¬å¼è¡¨è¾¾ï¼
追çæå°ç¨ï¼MINï¼ç¬¬äºå¤§ç¨ï¼LAGRE(æ°æ®,2)ï¼åºè¯¥æ¯å¯ä»¥çï¼ä½è¦æµè¯çæ¯å¦ä¼æææ³ä¸å°çé®é¢ã
é«çæ¬å»ºè®®ç¨textjionå
¬å¼ï¼ç®ç»å¾å¤
æµè¯äºï¼é½æ¾ç¤ºç©ºå¼ãä¸è½ç´æ¥å¥ç¨å ¬å¼äºï¼éå°è¿ä¸¤é®é¢ï¼å¦ä½ç¨å ¬å¼æ¥è¡¨ç¤ºï¼
追ç第2大ç¨largeæ¯æ²¡é®é¢çï¼
=IF((B2=MAX(B2:K2*ISNUMBER(FIND(B$1:K$1,A2))))*(LEFT(A2)="0"),0,"")&SUBSTITUTE(SUM(((B2:K2=LARGE(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,2))*B$1:K$1*(ISNUMBER(FIND(B$1:K$1,A2))))*10^(10-COLUMN(A:J))),0,)
æå°è¿æä¸ä¸ªé®é¢ï¼å°±æ¯åå
¬å¼ä¸ï¼æ²¡æ¾å°çæ°æ®ä¸º0ï¼æ以æå°ä¼å0ãå
¬å¼æè·¯å¯è½å¾éæ°æ´çã
æå°æ¥äºï¼
=IF((B2=MIN(B2:K2*ISNUMBER(FIND(B$1:K$1,A2))))*(LEFT(A2)="0"),0,"")&SUBSTITUTE(SUM(((B2:K2=MIN(IF(ISNUMBER(FIND(B$1:K$1,A2)),B2:K2,9^9)))*B$1:K$1*(ISNUMBER(FIND(B$1:K$1,A2))))*10^(10-COLUMN(A:J))),0,)
å¨ç¬¬2大çå ¬å¼éï¼IFå½æ°ä¸çMAXä¸ç¨æ´æ¹åï¼
追çåé¢çå¤ç0ä¹è¦æ´æ¹ï¼
=IF((B2=LARGE(B2:K2*ISNUMBER(FIND(B$1:K$1,A2))),2)*(LEFT(A2)="0"),0,"")&SUBSTITUTE(SUM(((B2:K2=LARGE(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,2))*B$1:K$1*(ISNUMBER(FIND(B$1:K$1,A2))))*10^(10-COLUMN(A:J))),0,)
æ¾ç¤ºå ¬å¼åå¨é误
追çåç°ç¬¬äºçé®é¢è¿æ¯è¾å¤ï¼éè¦éæ°ç¼åå ¬å¼ãææ¶é´åå¼ã
追é®æ¯åï¼é®é¢æ¯è¾å¤æäº
追ç第äºå¤§æ°ï¼è¯·èªå·±æµè¯ä¸å
¬å¼ï¼ä¸æ³åè±å¤§éæ¶é´æ¥è¾å
¥æ°æ®æ¨¡æï¼ï¼
=IF((B2=LARGE(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2*(MATCH(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,)=COLUMN(A:J)),2))*ISNUMBER(FIND(B$1,A2)),0,"")&SUBSTITUTE(SUM((ISNUMBER(FIND(B$1:K$1,A2))*B2:K2=LARGE(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2*(MATCH(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,)=COLUMN(A:J)),2))*B$1:K$1*10^(10-COLUMN(A:J))),0,)