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

jysun1

¬¡­¶Ã¯.zip (8.16 KB)

¤U¸ü¦¸¼Æ: 6

2017-8-21 22:34

¤U¸ü (40.43 KB)

2017-8-21 22:30

[attach]27647[/attach]

jysun1

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

ML089

¦^´_ 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

jysun1

¦^´_ 4# hcm19522

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

hcm19522

¦^´_ 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))),"")

jysun1

¤Ó¤Ó¤Ó·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

hcm19522

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