½Ð°Ý¦r¦ên¦p¦ó²Îp¦¸¼Æ¤£«´_?
©«¤l 11 ¥DÃD 4 ºëµØ 0 ¿n¤À 23 ÂI¦W 0 §@·~¨t²Î xp ³nÅ骩¥» sp2 ¾\ŪÅv 10 ©Ê§O ¨k ¨Ó¦Û u µù¥U®É¶¡ 2010-5-18 ³Ì«áµn¿ý 2024-8-7
½Ð°Ý¦r¦ên¦p¦ó²Îp¦¸¼Æ¤£«´_?
¥»©«³Ì«á¥Ñ oiggu ©ó 2010-7-24 12:47 ½s¿è
½Ð°Ý¦r¦ên¦p¦ó²Îp¦¸¼Æ¤£«´_
oiggu
©«¤l 472 ¥DÃD 5 ºëµØ 0 ¿n¤À 485 ÂI¦W 0 §@·~¨t²Î Windows ³nÅ骩¥» MS Office ¾\ŪÅv 100 ©Ê§O ¨k ¨Ó¦Û »´ä µù¥U®É¶¡ 2010-7-4 ³Ì«áµn¿ý 2014-12-28
¥»©«³Ì«á¥Ñ kimbal ©ó 2010-7-24 16:31 ½s¿è
¦bd3 ©ñ¤W³oÓ¤½¦¡:=SUMPRODUCT((MID($A$1:$A$30,2,4)=D$2&"")*(LEFT($A$1:$A$30,1)=$C3)*($A$1:$A$30<>"")/COUNTIF($A$1:$A$30,$A$1:$A$30)) ½Æ»s¥N½X µM«á§Û¨ì¨ä¥L®æ
©«¤l 4901 ¥DÃD 44 ºëµØ 24 ¿n¤À 4916 ÂI¦W 245 §@·~¨t²Î Windows 7 ³nÅ骩¥» Office 20xx ¾\ŪÅv 150 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2010-4-30 ³Ì«áµn¿ý 2024-11-11
¦^´_ 1# oiggu
¿ï¨úd3Àx¦s®æ,¥[¤J©w¸q¦WºÙx
¤½¦¡=OFFSET(Sheet1!$A:$A,,,COUNTA(Sheet1!$A:$A),)
d3°}¦C¤½¦¡
{=IF(ISNUMBER(MATCH($C3&D$2,LEFT(x,5),0)),SUM(1/COUNTIF(OFFSET($A$1,MATCH($C3&D$2,LEFT(x,5),0)-1,,COUNTIF(x,"="&$C3&D$2&"*")),OFFSET($A$1,MATCH($C3&D$2,LEFT(x,5),0)-1,,COUNTIF(x,"="&$C3&D$2&"*")))),0)}
¦V¤U¦V¥k½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý
©«¤l 455 ¥DÃD 8 ºëµØ 1 ¿n¤À 480 ÂI¦W 0 §@·~¨t²Î WINS home ³nÅ骩¥» OFFICE XP ¾\ŪÅv 100 ©Ê§O ¨k ¨Ó¦Û ¥Âí µù¥U®É¶¡ 2010-5-1 ³Ì«áµn¿ý 2015-1-22
¥»©«³Ì«á¥Ñ gong ©ó 2010-7-24 17:08 ½s¿è
d3=SUMPRODUCT((LEFT($A$1:$A$30,5)=$C3&D$2)*1/COUNTIF($A$1:$A$30,$A$1:$A$30))
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I
©«¤l 11 ¥DÃD 4 ºëµØ 0 ¿n¤À 23 ÂI¦W 0 §@·~¨t²Î xp ³nÅ骩¥» sp2 ¾\ŪÅv 10 ©Ê§O ¨k ¨Ó¦Û u µù¥U®É¶¡ 2010-5-18 ³Ì«áµn¿ý 2024-8-7
ÁÂÁ¦U¦ì:
¸g´ú¸Õ³£¥i¥H¨Ï¥Î¡A¦ý¹J¨ì¤@Ó°ÝÃD¸ê®Æ¦³5000µ§·|¼vÅT°õ¦æ³t«×·|«D±`摱
½Ð°Ý¬O§_¥i§ïvba ¦A¶×¤J®æ¤l¥[§Ö°õ¦æ³t«×¡C
oiggu
©«¤l 4901 ¥DÃD 44 ºëµØ 24 ¿n¤À 4916 ÂI¦W 245 §@·~¨t²Î Windows 7 ³nÅ骩¥» Office 20xx ¾\ŪÅv 150 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2010-4-30 ³Ì«áµn¿ý 2024-11-11
¦^´_ 5# oiggu Sub MyCount()
Set d = CreateObject("Scripting.Dictionary")
With Sheet1
For Each a In .Range(.[A1], .[A65536].End(xlUp))
If d(Left(a, 5)) = "" Then
d(Left(a, 5)) = a
ElseIf InStr(d(Left(a, 5)), a) = 0 Then
d(Left(a, 5)) = d(Left(a, 5)) & "," & a
End If
Next
For Each a In .[C3:C5]
For Each b In .[D2:F2]
.Cells(a.Row, b.Column) = UBound(Split(d(a & b), ",")) + 1
Next
Next
End With
End Sub ½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý
©«¤l 11 ¥DÃD 4 ºëµØ 0 ¿n¤À 23 ÂI¦W 0 §@·~¨t²Î xp ³nÅ骩¥» sp2 ¾\ŪÅv 10 ©Ê§O ¨k ¨Ó¦Û u µù¥U®É¶¡ 2010-5-18 ³Ì«áµn¿ý 2024-8-7
¥»©«³Ì«á¥Ñ oiggu ©ó 2010-7-26 06:29 ½s¿è
ÁÂÁ¶W¯Åª©¥D¡A¤Ó¯«¤F¡C
¤w¸g¥i¥H²Îp¦¸¼Æ
½Ð°Ýn¦p¦ó¥ÎVBA§âª÷ÃB²Îp¥X¨Ó
©ñ¤J®æ¦¡¤º¡B¦r¦ê¶}ÀY+¦~¤ë²Îp
²Îp.JPG
(99.92 KB)
²Îp.zip
(9.51 KB)
oiggu
©«¤l 4901 ¥DÃD 44 ºëµØ 24 ¿n¤À 4916 ÂI¦W 245 §@·~¨t²Î Windows 7 ³nÅ骩¥» Office 20xx ¾\ŪÅv 150 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2010-4-30 ³Ì«áµn¿ý 2024-11-11
¦^´_ 7# oiggu Sub MyCount()
Set d = CreateObject("Scripting.Dictionary")
Set d1 = CreateObject("Scripting.Dictionary")
With Sheet1
For Each a In .Range(.[A1], .[A65536].End(xlUp))
d1(Left(a, 5)) = d1(Left(a, 5)) + Application.Sum(a.Offset(, 1).Resize(, 3))
If d(Left(a, 5)) = "" Then
d(Left(a, 5)) = a
ElseIf InStr(d(Left(a, 5)), a) = 0 Then
d(Left(a, 5)) = d(Left(a, 5)) & "," & a
End If
Next
For Each a In .[F5:F7]
For Each b In .[G3:L3]
If (b.Column Mod 2) = 1 Then Cells(a.Row, b.Column) = UBound(Split(d(a & b), ",")) + 1
If (b.Column Mod 2) = 0 Then Cells(a.Row, b.Column) = d1(a & b)
Next
Next
End With
End Sub ½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý
©«¤l 11 ¥DÃD 4 ºëµØ 0 ¿n¤À 23 ÂI¦W 0 §@·~¨t²Î xp ³nÅ骩¥» sp2 ¾\ŪÅv 10 ©Ê§O ¨k ¨Ó¦Û u µù¥U®É¶¡ 2010-5-18 ³Ì«áµn¿ý 2024-8-7
[ª©¥DºÞ²z¯d¨¥] Hsieh(2010-7-27 18:57): À³¸Ó¹ï¤Wz§Þ¥©¥[¥H¤F¸Ñ,¦Û¤v¹Á¸Õ«á¦A°w¹ï°ÝÃDµo°Ý
¦^´_ 8# Hsieh
·PÁ¶W¯Åª©¥D
§A¼gªº vba ²¼ä¦³¤O¡A¤w¸g¥i¥H²Îp¦¸¼Æ¨Ï¥Î¥¿±`¡A
ÁٽбФ@Ó°ÝÃD¡A¦p¦ó§â¦P¦WºÙªº¼Æ¦rÁ`p°_¨Ó¡B
¹ï·Ó §÷®Æ¶µ¥Ø¤ÀÃþpºâ¡A¦A¥Î VBA ¶×¤J®æ¦¡n¦p¦ó§@?
test.zip
(3.29 KB)
oiggu
©«¤l 11 ¥DÃD 4 ºëµØ 0 ¿n¤À 23 ÂI¦W 0 §@·~¨t²Î xp ³nÅ骩¥» sp2 ¾\ŪÅv 10 ©Ê§O ¨k ¨Ó¦Û u µù¥U®É¶¡ 2010-5-18 ³Ì«áµn¿ý 2024-8-7
¦^´_ 9# oiggu
Sub sum()
For k = 2 To 10
Set d = CreateObject("Scripting.Dictionary")
With Sheets("date")
For Each a In Sheets("date").Range(.[a3], .[a65536].End(xlUp))
d(a) = d(a) + a.Offset(, k - 1)
Next
For Each a In Sheets("a").Range([a3], [a65536].End(xlUp))
Sheets("a").Cells(a.Row, k) = d(a)
Next
End With
Next
End Sub
·PÁªO¥D
¤£¾å±o¨ºùØ¥X¿ù¡B ´ú¸Õ¥X¨ÓªºÈ=0 ¤£¥¿½T
½Ð°Ý Sheets("a").Cells(a.Row, k) = d(a) ¿ù¦b¨ºùØ?
oiggu