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

[µo°Ý] ¥¨¶°¿ý»s=countifªº¨Ï¥Î°ÝÃD

[µo°Ý] ¥¨¶°¿ý»s=countifªº¨Ï¥Î°ÝÃD

§Ú¨Ï¥Î¤F¨âºØ±`¨£¨ç¼Æ¡A¦ý¬O¬O¨Ï¥Î»E¶°¿ý»s¦Ó¦¨ªº~©Ò¥H°õ¦æ¤W·|¦³ÂIºC
=COUNTIF
=CONCATENATE
¯à§_§ïµ½©O?
§Ú¤£¸Ñªº¬O~
y = Cells(9, 1).End(xlDown).Row
¬°¤°»òy±o¨ìªº­È¤£¬O1553?
¨D°ª¤â«ü¾É

³o¥÷ÀÉ®×,¸g¹L¿z¿ï«á
JÄæ¦ì¬O1ªº¡A¥u¬Ý¥X²{1¦¸ªº~~


Sch_Check.rar (30.88 KB)

¦^´_ 1# boblovejoyce
y = Cells(9, 1).End(xlDown).Row ¬°¤°»òy±o¨ìªº­È¤£¬O1553?
Y ªº´Á±æ­È¬O Cells(9, 1) = A9,©¹¤U³Ì«á¤@­Ó¦³¸ê®ÆªºÀx¦s®æ
ªþÀɵ{¦¡¤¤ ¬°¦ó¤£¬OA1¦Ó¥ÎA9
¸Õ¸Õ¬Ý  ¤u§@ªí¤W¦³¤Ó¦h¤½¦¡·|¦«ºCµ{¦¡¹B¦æ
  1. Option Explicit
  2. Sub test()
  3.     Dim S As Worksheet, f As Integer, r As String, i As Integer, j As Integer, a() As String, t As Date
  4.     Dim tmpath As String, MyFile As String
  5.     t = Timer
  6.     tmpath = ActiveWorkbook.Path
  7.     MyFile = tmpath & "\" & "Sch_chk.txt" 'Ū¨ú·í«eEXCELÀɮ׸ô®|¤Uªº«ü©wTXT
  8.     i = 1 '±q²Äi¦C¶}©l¼g¤JÀɮ׸ê®Æ,i¥i¦Û­q¨Ì¦Û¤v»Ý­n
  9.     Set S = ActiveSheet
  10.     S.Cells.Clear
  11.     f = FreeFile
  12.     Open MyFile For Input As #f
  13.     Do While Not EOF(f)
  14.         Line Input #f, r
  15.         If (InStr(1, r, "CHPT Design Note", vbTextCompare)) = 0 And _
  16.                 (InStr(1, r, "_Index_", vbTextCompare)) = 0 Then 'ªÅ®æ_¡A¬O¤@­Ó³s±µµü¡A¥Î©ó´«¦æ
  17.                 a = Split(r, "!") '¸ÓÀÉ®×¥H!¬°¤À¹j²Å¸¹
  18.                 S.Cells(i, "a").Resize(, UBound(a) + 1) = a
  19.             'For j = 0 To UBound(a)
  20.             '   s.Cells(i, j + 1).Value = a(j) 'Ū¨ú¸ê®Æ¨Ì§Ç¦s¤J²Äi¦Cªº²Ä1­Ó¨ìj­ÓÄæ¦ì
  21.             'Next j
  22.             i = i + 1
  23.         End If
  24.     Loop
  25.     Close #f
  26.         With S.Range("D1:D" & S.[A1].End(xlDown).Row) 'D1:D"&¨ìAÄæ³Ì«á¤@¦C¸¹ªº½d³ò
  27.             .Cells = "=RC[-2]&RC[-1]"
  28.             ' µ¥¦P¦Û°Ê¶ñº¡ : AutoFill Destination:=Range("D1:D" & x &)
  29.             .Value = .Value
  30.         End With
  31.         'ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
  32.        'ActiveCell.FormulaR1C1 = "=RC[-2]&RC[-1]"
  33.        ' Range("D1").Select
  34.         'x = Cells(3, 1).End(xlDown).Row
  35.         'Selection.AutoFill Destination:=Range("D1:D" & x & "") 'Àx¦s®æ¦Û°Ê¶ñº¡
  36.         
  37.         S.Range("A:A,D:D").Copy
  38.         S.Range("H1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  39.             :=False, Transpose:=False
  40.         'Range("A:A,D:D").Select
  41.         'Selection.Copy
  42.         'Range("H1").Select
  43.         'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  44.             :=False, Transpose:=False
  45.         'Application.CutCopyMode = False
  46.         
  47.         '*****************************
  48.         '2003ª©¨S RemoveDuplicates ³o¤èªk,¥i¥Î¶i¶¥¿z¿ï¤£­«½Æªº¸ê®Æ
  49.         ActiveSheet.Range("$H$1:$I$65536").RemoveDuplicates Columns:=2, Header:=xlNo
  50.         '*****************************************
  51.         
  52.         With S.Range("J1:J" & S.[A1].End(xlDown).Row) 'J1:J"&¨ìAÄæ³Ì«á¤@¦C¸¹ªº½d³ò
  53.             .Cells = "=COUNTIF(C[-6],RC[-1])"
  54.             ' µ¥¦P¦Û°Ê¶ñº¡ : AutoFill Destination:=Range("J1:J" & x )
  55.             .Value = .Value
  56.         End With
  57.         'y = Cells(9, 1).End(xlDown).Row
  58.         'Range("J1").Select
  59.         'ActiveCell.FormulaR1C1 = "=COUNTIF(C[-6],RC[-1])"
  60.         'Selection.AutoFill Destination:=Range("J1:J" & s.[A1].End(xlDown).Row)
  61.         'Selection.AutoFilter
  62.         '*****2003 ¦Û°Ê¿z½d³ò¿ï,»Ý¬O³sÄò½d³ò****************
  63.         'ActiveSheet.Range("H:H,J:J").AutoFilter Field:=3, Criteria1:="1"
  64.         '**************************************************
  65.         S.Range("H1").AutoFilter Field:=3, Criteria1:="1"
  66.         'MsgBox "Ū¨úÀɮ׸ê®Æok"
  67.         MsgBox Format(Timer - t, "0.0000")
  68. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 2# GBKEE
®¤§Ú·M¬N¡A½Ð°Ý GBKEE ª©¤j¡A±z¦b³Ì«á¤§
  1. S.Range("H1").AutoFilter Field:=3, Criteria1:="1"
½Æ»s¥N½X
»yªkªº³]©w¥Î·N¬O¡H  ¥¦·Qªí¹Fªº§@¥Î¬O¡H
¤S Field:=3, Criteria1:="1" ¦U«ü¦V­þ¸Ì¡H

ÁÂÁ±z¡I

TOP

¦^´_ 3# c_c_lai
  1. 'Selection.AutoFilter
  2. 62.        '*****2003 ¦Û°Ê¿z½d³ò¿ï,»Ý¬O³sÄò½d³ò****************
  3. 63.        'ActiveSheet.Range("H:H,J:J").AutoFilter Field:=3, Criteria1:="1"
½Æ»s¥N½X


S.Range("H1").AutoFilter Field:=3, Criteria1:="1"
AutoFilter(¦Û°Ê¿z¿ï:·|§ì¨ú«ü©wªº³sÄòÄæ¦ì) ²Ä¤@Äæ,¬°Field:=1,,,,Ãþ±À
©Ò¥H Field:=3 (²Ä¤TÄæ)-> ¬°S.Range("J1")
  1. AutoFilter ¤èªk  [¦Û°Ê¿z¿ï] ¿z¿ï¥X¤@­Ó²M³æ¡C¬° Variant¡C
  2. expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
  3. expression ¥²¿ï¡C¸Ó¹Bºâ¦¡·|¶Ç¦^ [®M¥Î©ó] ²M³æ¤¤ªº¨ä¤¤¤@­Óª«¥ó¡C
  4. Field ¿ï¾Ü©Êªº Variant¡C¬Û¹ï©ó§@¬°¿z¿ï°ò·ÇÄæ¦ì (±q²M³æ¥ª°¼¶}©l¡A³Ì¥ª°¼ªºÄæ¦ì¬°²Ä¤@­ÓÄæ¦ì) ªº¦ì²¾¡C
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE
·PÁ±z¡I
¸ò§Ú¤ß¤¤¹w´úªºª¬ºA¤@¼Ë¡AÁÂÁ±zÄÀºÃ¡I

TOP

¦^´_  boblovejoyce
y = Cells(9, 1).End(xlDown).Row ¬°¤°»òy±o¨ìªº­È¤£¬O1553?
Y ªº´Á±æ­È¬O Cells(9, ...
GBKEE µoªí©ó 2016-6-9 07:17



ÁÂÁªO¥D¤j¤jªº«üÂI~

TOP

        ÀR«ä¦Û¦b : ­n¥Î¤ß¡A¤£­n¾Þ¤ß¡B·Ð¤ß¡C
ªð¦^¦Cªí ¤W¤@¥DÃD