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

[µo°Ý] ¦h±ø¥ó²Î­p

[µo°Ý] ¦h±ø¥ó²Î­p

±z¦n¡I
½Ð°Ý¦h±ø¥ó²Î­p¦¸¼Æ
¥Ñ¡u¸ê®Æ¡v¤u§@ªí²Î­p¨C¤é¡]BÄæ¡^¦U¤H¤f¡]HÄæ¡^¨Ï¥Î¦¸¼Æ¡A¨Ã¿é¥X©ó¡u¤u§@ªí2¡v¡C
ÁÂÁÂ
W1.zip (19.49 KB)
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

¦^´_ 1# b9208


C7=COUNTIFS(¸ê®Æ!$B:$B,¤u§@ªí2!$B$7,¸ê®Æ!$H:$H,¤u§@ªí2!C6)¥k©Ô
C8=COUNTIFS(¸ê®Æ!$B:$B,¤u§@ªí2!$B$8,¸ê®Æ!$H:$H,¤u§@ªí2!C6)¥k©Ô

TOP

¦^´_ 2# jcchiang
«D±`·PÁ«ü¾É
¦ý»Ý²Î­p¦Ü¤Ö¤@¦~¥H¤W¸ê®Æ¡A¦p¥ÎEXCEL¨ç¼Æ¤èªk¡A¹Bºâ®É¶¡¤Ó¤[¡C
©Ò¥H½Ð±Ð§Q¥ÎVBA¤è¦¡¹Bºâ
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

²Ä6¦æªº N1~N4, S1~S5 ¬O©T©w¤£Åܪº???

TOP

¦^´_ 4# ­ã´£³¡ªL
©T©w¤£ÅÜ
ÁÂÁÂ
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 3# b9208

¸Õ¸Õ¬Ý
    Sub ex()
Dim arr
Dim d As Object
Set d = CreateObject("Scripting.Dictionary")
arr = Sheets("¸ê®Æ").[B5].CurrentRegion
For i = 2 To UBound(arr)
   If Not d.Exists(arr(i, 2) & arr(i, 8)) Then
      d.Add (arr(i, 2) & arr(i, 8)), 1
   Else
      d(arr(i, 2) & arr(i, 8)) = d(arr(i, 2) & arr(i, 8)) + 1
   End If
Next
With Sheets(1)
arr = .[B6].CurrentRegion
For i = 2 To UBound(arr, 2) - 1
   For j = 2 To UBound(arr)
      If d(arr(j, 1) & arr(1, i)) = "" Then
         arr(j, i) = 0
      Else
         arr(j, i) = d(arr(j, 1) & arr(1, i))
      End If
   Next
Next
.[B6].CurrentRegion = arr
.[L7].Resize(UBound(arr) - 1) = "=sum(c7:K7)"
.[L:L] = .[L:L].Value
End With
Set d = Nothing
End Sub

TOP

¦^´_ 6# jcchiang
«D±`·PÁ¨ó§U»P«ü¾É
µ{¦¡°õ¦æOK
¦ý¡u¤u§@ªí2¡v¤§¤é´Á¡A¬O¨Ì¾Ú¡u¸ê®Æ¡v¤º¤é´Á¿é¥X¤£­«´_¤é´Á¡A¨Ã«D¨Æ¥ý¿é¤Jªº¡C
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

Sub TEST()
Dim Arr, Brr, xD, i&, j%, K, R&, C&, N&
Set xD = CreateObject("Scripting.Dictionary")
[¤u§@ªí2!B7:B2000].EntireRow.Delete
Arr = [¤u§@ªí2!B6:K6]
For j = 2 To UBound(Arr, 2): xD(Arr(1, j)) = j: Next
Arr = Range([¸ê®Æ!A1], Sheets("¸ê®Æ").UsedRange)
ReDim Brr(1 To UBound(Arr), 1 To 11)
For i = 5 To UBound(Arr)
    K = Arr(i, 2): R = Val(xD(K)): C = Val(xD(Arr(i, 8)))
    If K = "" Or C = 0 Then GoTo 101
    If R = 0 Then N = N + 1: R = N: xD(K) = N: Brr(N, 1) = K
    Brr(R, C) = Brr(R, C) + 1: Brr(R, 11) = Brr(R, 11) + 1
101: Next i
With [¤u§@ªí2!B7].Resize(N, 11)
     .Value = Brr
     .Borders.LineStyle = 1
End With
End Sub

Xl0000221.rar (21.58 KB)


=====================

TOP

¦^´_ 8# ­ã´£³¡ªL

µ{¦¡°õ¦æOK
²Å¦X´Á±æ
«D±`·PÁ±z
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 8# ­ã´£³¡ªL
ª©¥D ±z¦n
°Ñ¦Òª©¥D¤§µ{¦¡½X¡A©ó¥t¤u§@ªí(Outq) ²Î­p¥X¤f¬OOKªº¡C
À|¸Õ©ó¤u§@ªí2¦P®É¿é¥X¤J¤f¤Î¥X¤f²Î­pªí¡Aµ{¦¡°õ¦æ¿ù»~¡AÀµ½Ðª©¥D«ü¾É¡C
«D±`·PÁÂ
W2.rar (65.42 KB)
100 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

        ÀR«ä¦Û¦b : µêªÅ¦³ºÉ¡D§ÚÄ@µL½a¡AµoÄ@®e©ö¦æÄ@Ãø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD