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

¦p¦ó±N¦P¼Ë¸¹½Xªº¸ê®Æ¼g¦b¥t¤@±i¤u§@ªíªº¦P¤@¦C¤W

¦p¦ó±N¦P¼Ë¸¹½Xªº¸ê®Æ¼g¦b¥t¤@±i¤u§@ªíªº¦P¤@¦C¤W

¦p¦ó±N¦P¼Ë¸¹½Xªº¸ê®Æ¼g¦b¥t¤@±i¤u§@ªíªº¦P¤@¦C¤W
»¡©ú:
²Ä1±i¤u§@ªí¬°­pºâ°e¦L½c¤l¤Î¨ú¦^¦L¦nªM¤l¼Æ¶q©M­pºâÁÙ¦³¦h¤Ö½c¤l¦b¥~­±ªº±±¨îªí.
²Ä2±i¤u§@ªí¡¨¥X³f¤Î¨ú³f¤Î¾P°â¡¨:¬°¬ÛÃö¥X³f¤Î¨ú¦^¤Î¾P°âªÅ¥ÕªM¤l¤§¸ê®ÆY07,Y09,Y11¬°¤TºØ¤£¦P¤Ø¤o¤§ªÅ¥ÕªM¤l,¥i¯à¥ý°e¥h¦L¨ê,¦A¨ú¦^½æµ¹«È¤á,¦p1001(«È¤áA)¤Î1002(«È¤áB),¤]¥i¯àª½±µ½æµ¹«È¤á,¦p1003(«È¤áC) ¦p°e¦L¨ê®É,¦bFÄæ·|¦³¥[µù¡¨CUP PRINT¡¨,¥BµLª÷ÃB¸ê®Æ,¬O¤u§@ªí1­n¦¬¶°ªº¸ê®Æ(¼Æ¦r¬°¥¿),ªí¥Ü¬°°e¥Xªº³f,¬O­n§@²Ö¥[ªº,¦Óª½±µ½æªº,¦p«È¤áC´N¤£¼g¤J¤u§@ªí1
1.        YP¶}ÀY(¦p2002),ªí¥Ü¨ú¦^¦L¨ê«áªºªM¤l(¦U¦Û·|¹ïÀ³¦ÜY07,Y09¤ÎY11),¥ç¬°À³¼g¤J¤u§@ªí1ªº¼Æ¦r,ªí¥Ü¨ú¦^,©Ò¥H¼Æ¦r¬°­t(§YÀ³´î¥h)
2.        Y¦rÀY°e¦Lªº´î¥hYP¦rÀYªº´N¬OÁÙ¦b¦L¨ê¼tÁÙ¨S¦L¦nªº¼Æ¦r
¶D¨D:
A°w¹ï¦P¤@OS¸¹ªº¸ê®Æ§Ú¥u¯à¼g¥X¤À¦h¦Cªº«¬ºA¼g¤J¤u§@ªí1,§Æ±æ¯à¼g¦b¦P¤@¦C¤W(¦p¤U¹Ï¥k¤è)
B.­«Âаõ¦æµ{¦¡½X®É,¤u§@ªí1¤£·|¼g¤J­«ÂЪº¸ê®Æ
¦¬¶°¥X³f¤Î¨ú¦^¤§¼Æ¶q.rar (60.65 KB)

¸É¤W¹Ï¤ù
¦P¸¹½X¼g¦b¦P¤@¦C.png
2018-10-10 19:24

TOP

N2:P6 (¤T¶µ¤£­«½Æ){=IFERROR(INDEX(B:B,SMALL(IF((MATCH($B$2:$B$15&$C$2:$C$15&$D$2:$D$15,$B$2:$B$15&$C$2:$C$15&$D$2:$D$15,)=ROW(B$2:B$15)-1)*($B$2:$B$15<>""),ROW(B$2:B$15)),ROW(A1))),"")

Q2:X6{=IFERROR(IF(MOD(COLUMN(A1),3)=0,"",INDIRECT(TEXT(RIGHT(SMALL(IF((MMULT(($B$2:$D$15=$N2:$P2)*1,{1;1;1})=3)*($E$2:$L$15<>""),COLUMN($E2:$L2)*10001+ROW(E$2:L$15)/1%),INT(COLUMN(A1)/3)*2+MOD(COLUMN(A1),3)),4),"!R0C00"),)),"")

B2:L15¬O¼Æ¾Ú
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

N26 (¤T¶µ¤£­«½Æ){=IFERROR(INDEX(B:B,SMALL(IF((MATCH($B$2B$15&$C$2C$15&$D$2D$15,$B$2B$15&$C ...
hcm19522 µoªí©ó 2018-10-11 10:27

ÁÂÁ¤j¤j¼ö¤ß¦^ÂÐ,¦]¬°ªí¬Oµ¹°ê¥~ªº¤p©j¨Ï¥Î,¥B¬O¤é³øªí,§Æ±æ¦b¨C­Ó¤éªí¼g¤JVBA,¨Ã¥D°Ê·JÁ`¦Ü²Ä1­Ó¤u§@ªí¤W,©Ò¥H·Q¨DVBAªº§@ªk.

TOP

Sub §ó·s()
Dim Arr, Brr, C&, i&, T1$, T2$, U$, N&
Call ²M°£
Arr = Range([¥X³f¤Î¨ú³f!F1], [¥X³f¤Î¨ú³f!A1].Cells(Rows.Count, 1).End(xlUp))
ReDim Brr(1 To UBound(Arr), 1 To 11)
For i = 2 To UBound(Arr)
    T1 = Arr(i, 1): If T1 = "" Then GoTo 101
    If T1 = "OS" Then
       If Arr(i, 6) <> "PRINT CUP" And Left(Arr(i + 1, 1), 2) <> "YP" Then T2 = "": GoTo 101
       T2 = "P": N = N + 1
       Brr(N, 1) = Arr(i - 1, 3): Brr(N, 2) = Arr(i - 1, 2): Brr(N, 3) = "#" & Arr(i - 1, 1)
    End If
    U = Left(T1, 1) & Right(T1, 1)
    C = Switch(U = "Y9", 4, U = "Y1", 7, U = "Y7", 10, U = U, 0)
    If T2 = "" Or C = 0 Then GoTo 101
    Brr(N, C) = T1:   Brr(N, C + 1) = Arr(i, 3) * IIf(Left(T1, 2) = "YP", -1, 1)
101: Next i
If N = 0 Then Exit Sub
With [¥X³fµ²¾l!B7:L7].Resize(N)
     .Value = Brr
     .Columns(1).NumberFormatLocal = "m/d"
     .Borders.LineStyle = 1
End With
End Sub
'======================================
Sub ²M°£()
Sheets("¥X³fµ²¾l").UsedRange.Offset(6, 0).EntireRow.Delete
End Sub

¦¬¶°¥X³f¤Î¨ú¦^¤§¼Æ¶q.rar (42.93 KB)

TOP

Sub §ó·s()
Dim Arr, Brr, C&, i&, T1$, T2$, U$, N&
Call ²M°£
Arr = Range([¥X³f¤Î¨ú³f!F1], [¥X³f¤Î¨ú ...
­ã´£³¡ªL µoªí©ó 2018-10-13 11:51

ÁÂÁ·Ǥj,§¹¥þ²Å¦X©Ò¨D,¤]·PÁ¦³³o¦³½×¾Â!

TOP

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