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

[µo°Ý] ½Ð°Ý¡G±N¤T­Óªí®æ¦X¨Ö¦b¤@­Óªí®æ¸Ì

[µo°Ý] ½Ð°Ý¡G±N¤T­Óªí®æ¦X¨Ö¦b¤@­Óªí®æ¸Ì

¤j®a¦n¡A·Q½Ð°Ý¦p¦ó±N¡u¤T­Ó¤u§@ªíªí®æªº¸ê®Æ¡v¦X¨Ö¦b¤@­Óªí®æ¸Ì¡A¸Ô²Ó»¡©ú¦p¤U¡G

¤u§@ªí¡GX1
1.PNG
2019-11-27 17:01

¦³4­Ó¸¹½X(¨S¦³·Ó¶¶§Ç±Æ§Ç)¡A¤Î¹ïÀ³ªº¤p­p­È
(¹ê»Ú¤Wªºµ§¼Æ¡A¤£©T©w¡C¨Ò¤l¶È¥H4µ§¬°¨Ò)

¤u§@ªí¡GX2
2.PNG
2019-11-27 17:01

¦³4­Ó¸¹½X(¨S¦³·Ó¶¶§Ç±Æ§Ç)¡A¤Î¹ïÀ³ªº¤p­p­È
(¹ê»Ú¤Wªºµ§¼Æ¡A¤£©T©w¡C¨Ò¤l¶È¥H4µ§¬°¨Ò)

¤u§@ªí¡GX3
3.PNG
2019-11-27 17:01

¦³3­Ó¸¹½X(¨S¦³·Ó¶¶§Ç±Æ§Ç)¡A¤Î¹ïÀ³ªº¤p­p­È
(¹ê»Ú¤Wªºµ§¼Æ¡A¤£©T©w¡C¨Ò¤l¶È¥H3µ§¬°¨Ò)

¤u§@ªí¡GÁ`ªí
4.PNG
2019-11-27 17:02

§Æ±æ§e²{¥X¡G
(1) ±NX1~X3ªº¸¹½X«ö¶¶§Ç±Æ¦C
(2) X1ªº¤p­p­È¡A·|©ñ¦bX1ªºÄ橳¤U
(3) X2ªº¤p­p­È¡A·|©ñ¦bX2ªºÄ橳¤U
(4) X3ªº¤p­p­È¡A·|©ñ¦bX3ªºÄ橳¤U
(5) ¨Ã±NX1~X3¤p­p­È¥[Á`«á¡A©ñ¦b¦X­p

Ãö©ó±Æ§Ç¤§«e¦³µo¤å¡G¡u½Ð°Ý¦p¦ó±N¤å¦rÂন­È¡A¨Ã±Æ§Ç?¡v¡A¦ý¥ý«e¥u¾A¥Î³B²z³æ¤@ªº¤u§@ªí¡C
¥Ø«e¬O·Q±NX1~X3ªº¸¹½X¡A¦X¨Ö¦bÁ`ªí¸Ì¡A¨Ã¥H¦p¹Ïªº¤è¦¡§e²{¡C

³Ì«áªþ¤WEXCELÀɮצp¤U¡AÁÂÁ¡C
¬¡­¶Ã¯1.zip (8.83 KB)

¦A½Ð¦U¦ì¤j¯«À°¦£¨ó§U´£¨Ñ·Qªk¡A«D±`·PÁ¡C

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


    ÁÂÁ­㴣¤j¤j´£¨Ñªº¤èªk¡A«D±`ÁÂÁ±z¡C:)

TOP

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


    ¤Ó°ª¤F¡I¨ØªA¡I¨ØªA¡I

TOP

Sub TEST()
Dim Arr, Brr, xD, i&, j&, R&, C&, U&, T$
Sheets("Á`ªí").UsedRange.Clear
Set xD = CreateObject("Scripting.Dictionary")
ReDim Brr(1 To 2000, 1 To 99)
Brr(1, 1) = "¸¹½X"
For i = 1 To Sheets.Count
    If Left(Sheets(i).Name, 1) <> "X" Then GoTo i99
    C = C + 1: Brr(1, C + 1) = Sheets(i).Name
    Arr = Sheets(i).UsedRange
    For j = 2 To UBound(Arr)
        T = Arr(j, 1): If T = "" Then GoTo j99
        U = xD(T)
        If U = 0 Then R = R + 1: U = R: xD(T) = R: Brr(U + 1, 1) = T
        Brr(U + 1, C + 1) = Brr(U + 1, C + 1) + Arr(j, 2)
j99: Next j
i99: Next i
With Sheets("Á`ªí").[a1].Resize(R + 1, C + 2)
     .Columns(1).NumberFormatLocal = "@"
     .Value = Brr
     .Sort Key1:=.Item(1), Order1:=xlAscending, Header:=xlYes
     .Columns(C + 2) = "=SUM(RC[-" & C & "]:RC[-1])"
     .Rows(R + 2) = "=n(SUM(R[-" & R & "]C:R[-1]C))"
     .Cells(1, C + 2) = "¦X­p": .Cells(R + 2, 1) = "¦X­p"
     Union(.Rows(1), .Rows(R + 2), .Columns(C + 2)).Font.Bold = True
End With
End Sub

Xl0000008.rar (15.4 KB)


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

TOP

        ÀR«ä¦Û¦b : ¯u¥¿ªº·R¤ß¡A¬O·ÓÅU¦n¦Û¤vªº³oÁû¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD