[µo°Ý] ±ø¥ó¥[Á`

ziv976688

¤U¸ü (111.2 KB)

2020-11-29 15:23

·íAB1:BX1<>¡¨"¡A¥B¦³¥X²{¦b$D$2:$Z$70®É¡A«h±N¸Ó¼Æ¦r¦bCÄ檺¦¸¼Æ¥[Á`¡A¨Ì§Ç¶ñ¤JAB2:BX2¡C
EX : AB2=1¡AAC2=3¡AAD2=6¡AAE2=4¡AAF=2¡A¡K¡K¨ä¾lÃþ±À¡C
½Ð°Ý¡JAB2ªºExcel 2003ª©¤½¦¡­n¦p¦ó½s¼g ?
ÁÂÁÂ !
PS ¡G¡@¦]¬°­pºâ½d³ò¤£©T©w¡A©Ò¥H±N­pºâ½d³òÂX¤j¹w¯d¡C

±ø¥ó¥[Á`.rar (4.62 KB)

¤U¸ü¦¸¼Æ: 9

2020-11-29 15:23

samwang

¦^´_ 1# ziv976688


    AB2=COUNTIF($D:$Z,AB1)

ziv976688

¦^´_ 2# samwang
ÁÂÁ±zªº¦^ÂСA¦ý±z©¿²¤¤FCÄ檺­¿¼Æ¡C

§Ú¬O¦]¬°$D$Z$70ªº¦U¼Æ¦r¤£¤@©w¬O³æ¤@­Ó¡A¥BCÄ檺¹ïÀ³¦U¦¸¼Æªº¹ïÀ³¼Æ¦r¤]¤£¬O³æ¤@­Ó ¡A©Ò¥HAB2²Å¦X»Ý¨Dªº¤½¦¡¡A§Ú¼g¤£¥X¨Ó ^^"

jcchiang

¦^´_ 3# ziv976688

Sub ex()
Dim d As Object
Dim a, x%
Set d = CreateObject("Scripting.Dictionary")
For Each a In Range([c2], [c65535].End(3))
   For x = 1 To 20
      If a.Offset(, x).Value <> "" Then
         If Not d.exists(a.Offset(, x).Value) Then
            d(a.Offset(, x).Value) = a
         Else
            d(a.Offset(, x).Value) = d(a.Offset(, x).Value) + a
         End If
      Else: Exit For
      End If
   Next
Next
For Each a In Range("AB1:BU1")
   If d.exists(a.Value) Then a.Offset(1) = d(a.Value)
Next
Set d = Nothing
End Sub

samwang

¦^´_ 3# ziv976688

½Ð¦A½T»{¬O§_¬°±zªº»Ý¨D¡A·PÁ¡C

Sub tt()
Dim Arr, xD, Drr, i&, j&, s%
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([Z1], [C65536].End(3))
For i = 2 To UBound(Arr)
    For j = 2 To UBound(Arr, 2)
        T = Arr(i, j)
        If T = "" Then GoTo 100
        xD(T) = xD(T) + Arr(i, 1)
100: Next
Next
Drr = Range([AB1], [AB1].End(2))
For j = 1 To UBound(Drr, 2)
    T1 = Drr(1, j)
    Drr(1, j) = xD(T1)
    s = s + 1
Next
Range("AB2").Resize(, s) = Drr
End Sub

ziv976688

¦^´_ 4# jcchiang
J¤j:
´ú¸ÕOK
«D±`·PÁ±zªºÀ°¦£

ziv976688

¦^´_ 5# samwang
S¤j:
´ú¸ÕOK
«D±`·PÁ±zªºÀ°¦£

hcm19522

=SUMPRODUCT(MMULT(N(AB1=$D2:$Z70),ROW(1:23)^0)*(0&$C2:$C70))

ziv976688

¦^´_ 8# hcm19522
h¤j:
´ú¸ÕOK
¦A¦¸ÁÂÁ±zªº­@¤ßÀ°¦£~·P®¦

½Ð°Ý :   ROW(1:23)^0 ¬O«ü¤°»ò½d³ò?
Àµ½Ð½ç±Ð¡CÁÂÁ±z!

ziv976688

¦^´_ 8# hcm19522
h¤j:
«öF9«á¡A¤wÁA¸Ñ ROW(1:23)^0  ªº·N²[½d³ò¤F~ÁÂÁ±z