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

¦p¦ó±N¤@¤£µ¥¤j¤pdata°Ï¶ô±Æ¦¨¤@¦æ

¦p¦ó±N¤@¤£µ¥¤j¤pdata°Ï¶ô±Æ¦¨¤@¦æ

¥»©«³Ì«á¥Ñ jysun1 ©ó 2017-8-21 22:35 ½s¿è

¬¡­¶Ã¯.zip (8.16 KB) [attach]27647[/attach]

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2017-8-22 11:35 ½s¿è

F13:F21{=IFERROR(INDEX(LOOKUP(ROW(A$1:A$27),ROW(A$1:A$27)/FIND(F$1,A$1:A$27),A$1:A$27),SMALL(IF(($A$1:$A$27=G$1)*($B$1:$C$27<>""),ROW(B$1:C$27)),ROW(A1))),"")

G13:I21{=IFERROR(INDIRECT(TEXT(RIGHT(SMALL(IF((LOOKUP(ROW(A$1:A$27),ROW(A$1:A$27)/FIND($F$1,$A$1:$A$27),$A$1:$A$27)=$F13)*($A$1:$A$27=G$12)*($B$1:$C$27<>""),ROW(B$1:C$27)*10+COLUMN($B1:$C27)*10001),COUNTIF($F$13:$F13,$F13)),4),"!R0C0"),),"")

http://blog.xuite.net/hcm19522/twblog/528333020

3514.png (42.22 KB)

3514.png

1

µû¤À¤H¼Æ

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¤Ó¤Ó¤Ó·PÁ hcm19522, §K°£§Ú¤@ª½½Æ»s & ¿ï¾Ü©Ê¶K¤W
¤£¦n·N«ä¦A½Ð±Ð¤@¤U¡A¦]¬°§Úªº Tool id¨ä¹ê¨S¦³³W«h©Ê eg. NPxx11 BPxx23, NExx99...etc,³o¼Ë´N¨S¿ìªk¥ÎFIND(F$1,A$1:A$27)¡A¦³¤°»ò¿ìªk§¹¦¨©O?ÁÂÁ¡C

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2017-8-23 09:55 ½s¿è

¦^´_ 3# jysun1
F13:F21{=IFERROR(INDEX(LOOKUP(ROW(A$1:A$27),ROW(A$1:A$27)/ISERR(FIND("Para",A$1:A$27)),A$1:A$27),SMALL(IF(($A$1:$A$27=G$1)*($B$1:$C$27<>""),ROW(B$1:C$27)),ROW(A1))),"")
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 4# hcm19522

·PÁÂhcm19522¦^ÂФp¾Çµ{«×ªº°ÝÃD~~~

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2017-8-24 09:26 ½s¿è

¦^´_ 3# jysun1


F13 {=LOOKUP(SMALL(($B$1:$C$99="")*4^7+ROW($1:$99),ROW(A1)*3-2),ROW($1:$99)/ISNA(MATCH(TEXT($A$1:$A$99,"[=]9"),$G$1:$I$1,)),$A$1:$A$99&"")
°}¦C¤½¦¡¡A¤U©Ô

G13 {=INDIRECT(TEXT(SMALL((($A$1:$A$99<>G$1)+($B$1:$C$99="")>0)*(60001)+ROW($1:$99)*10+COLUMN($B$1:$C$99),ROW(A1)),"!R0C0"),)&""
°}¦C¤½¦¡¡A¥k©Ô¤U©Ô

$99 ªí¥Ü³Ì¤Ö¶·¨Ì·Ó§AªºÀx¦s®æ³Ì«á¦ì¸m + 1 ®æ
ROW(A1)*3-2 ªí¥Ü Para1¡BPara2¡BPara3 ¦³ 3¡Aµø»Ý¨D½Õ¾ã 3 ¤Î $G$1:$I$1¡C
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# ML089
:) ¤Ó·PÁ ML089 ¸Ô²Óªº»¡©ú!!!!!!

TOP

        ÀR«ä¦Û¦b : ¥ÌÄ@°µ¡BÅw³ß¨ü¡C
ªð¦^¦Cªí ¤W¤@¥DÃD