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

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

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


·í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)

¦^´_ 1# ziv976688


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

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2020-11-30 09:17 ½s¿è

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

TOP

¥»©«³Ì«á¥Ñ jcchiang ©ó 2020-11-30 13:12 ½s¿è

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

TOP

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

TOP

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2020-11-30 14:57 ½s¿è

=SUMPRODUCT(MMULT(N(AB1=$D2:$Z70),ROW(1:23)^0)*(0&$C2:$C70))
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

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

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¤H­nª¾ºÖ¡B±¤ºÖ¡B¦A³yºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD