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

[µo°Ý] ¦C¥X¤£¬Û¦P¼Æ­È©Î¦r¦ê¥X²{¦¸¼Æ

[µo°Ý] ¦C¥X¤£¬Û¦P¼Æ­È©Î¦r¦ê¥X²{¦¸¼Æ

¥»©«³Ì«á¥Ñ jesscc ©ó 2019-8-4 09:53 ½s¿è

°ÝÃD¦bªþ¥ó±Ô­z
[attach]31120[/attach]

test.rar (7.25 KB)

Jess

Sub TEST()
Dim Arr, xD, i&, j&, A$, B, T$, N&
[E2:F2000].ClearContents
Arr = Range([B6], [A65536].End(xlUp))
Set xD = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(Arr)
    A = Arr(i, 1):  B = Arr(i, 2)
    If A = "" Or Val(B) = 0 Then GoTo 101
    If xD(A) = 0 Then N = N + 1: xD(A) = N: Arr(N, 1) = A: Arr(N, 2) = 0
    B = Split(B & "-" & B, "-")
    For j = B(0) To B(1)
        T = A & "/" & j:  xD(T) = xD(T) + 1
        If xD(T) = 1 Then Arr(xD(A), 2) = Arr(xD(A), 2) + 1
    Next j
101: Next
If N > 0 Then [E2:F2].Resize(N) = Arr
End Sub

TOP

ÁÂÁ­ã¤j¡A¯u¬O§Ö¤S·Ç¡A¦ý¬O§Ú¦³2­Ó¤p°ÝÃD¡A¦]¬°"ºØÃþ"©M"½c¼Æ"¤§¶¡ÁÙ¦³¨ä¥LÄæ¦ì¡A¬O§_¯à°÷¥u¦C¥X½c¼Æ´N¦n?¯à¥Î¤½¦¡¼g¥X³o¼Ëªº®ÄªG¶Ü?
Jess

TOP

¦^´_ 3# jesscc


¤½¦¡¬O¨S¿ìªk,
ªí®æ¬O¤°»ò¼Ë???  ¤W¶Ç¬Ý¬Ý~~

TOP

­ìªí®æ¦pªþ¥ó
test1.rar (7.53 KB)
Jess

TOP

¦^´_ 5# jesscc


³o¬O¹ê»Úªí®æ???
ºØÃþ©T©w¥u¦³3ºØ???

TOP

¥H¤Uµ{¦¡½X©ñ¦b¤@¯ë¼Ò²Õ:
Function GetCount(xA As Range, xB As Range, C$)
Dim Arr, Brr, xD, i&, j&, B
Set xD = CreateObject("Scripting.Dictionary")
Arr = xA: Brr = xB
For i = 1 To UBound(Arr)
    If Arr(i, 1) <> C Or Val(Brr(i, 1)) = 0 Then GoTo 101
    B = Split(Brr(i, 1) & "-" & Brr(i, 1), "-")
    For j = B(0) To B(1):  xD(j) = 1:  Next j
101: Next
GetCount = xD.Count
End Function


D3/¤½¦¡:
=IF(B3="","",GetCount(A$7:A$20,D$7:D$20,B3))


¸ê®Æ¦hªº¸Ü, À³¸Ó·|¥d
===============================

TOP

ºØÃþ¨ä¹ê¤£¬O¤ôªG¡A¥u¦³¤T¤jÃþ¡C
¤p§ÌVBA«Ü²Ê²L¡A§Ú¬O²Ä¤@¦¸¬Ý¨ì¤½¦¡·f°tµ{¦¡³o¼Ëªº¼gªk¡C
·Q½Ð°Ý­ã¤j
GetCount ¬O¦Û©w¸q¨ç¼Æªº¦WºÙ¶Ü?
If Arr(i, 1) <> C Or Val(Brr(i, 1)) = 0 ³o¤@¬q§¹¥þ¬Ý¤£À´
¯à½Ð­ã¤j±Ð¤@¤U¶Ü?
Jess

TOP

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

TOP

hcm19522 µoªí©ó 2019-8-5 12:29


h¤j,§Ú¬Ý¤F±zªº¤½¦¡¡A¦³¤@ÂI¤£©ú¥Õ,´N¬O
E7:E17=IF(ISERR(FIND("-",D7)),(COUNTIFS($A$7:A7,A7,D$7:D7,D7)=1)*1,MID(D7,FIND("-",D7)+1,9)-LEFT(D7,FIND("-",D7)-1)+1)
¨ä¤¤¨º­Ó9¬O¤°»ò·N«ä?DÄ檺¸ê®Æ¡AÁ`¦r¤¸ªø«×¤£¬O³Ì¦h¤]¥u¦³5­Ó¦r¤¸¶Ü?
Jess

TOP

        ÀR«ä¦Û¦b : ¤H¥Í³Ì¤jªº¦¨´N¬O±q¥¢±Ñ¤¤¯¸°_¨Ó¡C
ªð¦^¦Cªí ¤W¤@¥DÃD