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

¨Ì¾Ú­­¨î±ø¥ó¦Û°Ê¶ñ¤J§PÂ_

¦^´_ 5# clio


    (¸û©P¥þ) ¨Ò :N »P N«á­±¦³¦r²Å·|¥X¿ù=IFERROR(LOOKUP(1,0/FIND("+"&K$5:K$13&"+","+"&C5&"+"),L$5:L$13),"")
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 11# hcm19522
hcm19522«e½ú¡K
¤pªº¹ï©ó³o­Ó°ÝÃDÁÙ¬O¤£¤ÓÀ´
§Ú§â¡iFIND("+"&K$5:K$13,C5)¡j³o­Ó¤½¦¡¥t¥~§ì¥X¦b¾ï¦âªº³¡¥÷¡AÆ[¹îµ²ªG¼Æ­È¡A¦ý¬O¥X²{ªº¼Æ­È¡A§Ú¤£¬O«Ü¯à²z¸Ñ¡A
²Ä¤@­Ó12¡A¥i¥Hª¾¹D¦bC5¤¤¦³³Q§ä¨ì+N¡A¦ì¸m¦b12ªº³¡¥÷¡A¦ý¬O¬°¤°»òC6ªºÀ³¸Ó¤]­n§ä¨ì¡A«o¥X²{#VALUE!ªº³¡¥÷¡A­Y¬O»¡¡A¦]¬°¥L¬O¤@­ÓK5:K13ªº°}¦Cµ²ªG¡A¥u·|¬Ý¨ìK5ªº¤ñ¹ïµ²ªG¡A¬°¤°»ò¦bC8ªº¦a¤è¤S¯à§äÅã¥Ü14©O
¥LªºÅã¥ÜÀ³¸Ó¬O¤@­Ó°}¦Cªº¦U§Oµ²ªG¡A¦ý¬O¥H¹Ï­±¨Ó¬Ý¤S¤£¬O³£Åã¥Ü²Ä¤@µ§¡A©Î¬O¯à§ä¨ìªº¨º¤@µ§¡A©Ò¥H·d¤£²M·¡¬OÅã¥Ü¤°»òªº©O¡H
2019-01-09_144130.png
2019-1-10 10:42
clio

TOP

¦^´_ 12# clio

FIND("+"&K$5:K$13,C5) ¤U©Ô ¦PFIND("+"&K5,C5) ©Ò¥HFIND("+"&K$5:K$13,C5)¼Æ¦C  ©|¶·¦A§ä«D¿ù»~ªº¦ì¸m L­È
FIND("+"&K$5:K$13&"+",C5&"+") ¤~©P¥þ
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 13# hcm19522
hcm19522¤j¤j¡A
¤£¦n·N«ä¡A¥Ñ©ó¤p§Ì¤ñ²Â¤@ÂI¡A·Ó±zªº²Ó¤ß¸ÑÄÀ¤S¥h°µ¤F´ú¸Õ¡A¦p¤U¹Ï
½T¹ê¾ï¦â³¡¥÷¡A·|¹³ÂŦ⪺µ²ªG¤@¼Ë¡A¦ý¬O¦]¬°§Ú­Ì³]©wªº¬OK$5:K$13¡A©Ò¥H·í¦ì¸m¶W¹L9¡A¦b10¦ì®É´N³£·|¥X²{#VALUE!¡A©Ò¥H¦b¾ï¦âM14¥H«áªº¥L¬Oµ¥©óFIND("+"& Y, C14)¡A³o­ÓYªº¦ì§}·|¬O¤°»ò©O¡H

2019-01-10_165555.png
2019-1-10 17:02
clio

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2019-1-10 18:04 ½s¿è

¦^´_ 14# clio

FIND("+"&K$5:K$13,C5)³æ¦¹¤½¦¡¬OµL·N¸q ©Ò¥H¤U©Ô³£¤£¬O·Q­n ¶·°t¦XLOOKUP

¤½¦¡¤G=IFERROR(LOOKUP(1,0/(MID(C5,FIND("+",C5),9)&"+"="+"&K$5:K$13&"+"),L$5:L$13),"")


FIND("+"& Y, C14)-->¦ì§}­n¦³­^¤å¦r¥À+¼Æ¦r ,­Y¬O¦r²Å ­n+Âù¤Þ¸¹ ©Î "+Y"
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 15# hcm19522
¤F¸Ñ¡K¨º§Ú´N¤£°õµÛ¤F¡K·PÁÂhcm19522ªº­@¤ß¸Ñ»¡
clio

TOP

­Y"+"³£¦b³Ì«á
=iferror(VLOOKUP(MID(C5,FIND("+",C5)+1,9),K:L,2,),"")

KÄæ¯Á¤Þ­È¥²¶·¬O[¤å¦r®æ¦¡]
Xl0000367.rar (6.81 KB)

TOP

        ÀR«ä¦Û¦b : ¯à¥I¥X·R¤ß´N¬OºÖ¡A¯à®ø°£·Ð´o´N¬O¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD