Lm th? no ? ki?m tra s? l?ng h? s tr? l?i trong m?t AutoFilter

D?ch tiu ? D?ch tiu ?
ID c?a bi: 152215 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

TM T?T

Khi b?n p d?ng m?t AutoFilter, Microsoft Excel s? hi?n th? s? l?ng h? s ?c l?c ? gc d?i bn tri c?a thanh tr?ng thi.

Bi vi?t ny ch?a m?t m?u Microsoft Visual Basic cho cc ?ng d?ng v? m)Ph? th? t?c) m s? xc ?nh nh th? no nhi?u h? s ? ?c tr? l?i trong m?t danh sch c AutoFilter b?t. B?n c?ng c th? s? d?ng ch?c nng Subtotal ? ?t ?c m?t k?t qu? tng t?. C? hai phng php ?c chi ti?t trong ph?n "Thng tin thm" bi vi?t ny.

THNG TIN THM

Microsoft cung c?p l?p tr?nh v d? ? minh ho? ch?, khng c b?o hnh ho?c th? hi?n hay ng? ?. i?u ny bao g?m, nhng khng gi?i h?n, b?o ?m ng? ? kh? nng bn hng ho?c cho m?t m?c ch c? th?. Bi vi?t ny gi? ?nh r?ng b?n ? quen thu?c v?i ngn ng? l?p tr?nh m ang ?c ch?ng minh v v?i nh?ng cng c? ?c s? d?ng ? t?o ra v g? l?i th? t?c. Microsoft h? tr? cc k? s c th? gip gi?i thch cc ch?c nng c?a m?t th? t?c c? th?, nhng h? s? khng s?a ?i cc v d? ? cung c?p thm ch?c nng ho?c xy d?ng quy tr?nh ? p ?ng cc yu c?u c? th? c?a b?n.

B?ng cch s? d?ng m?t v? m Visual Basic

  1. Chn m?t t? m-un v g? m? sau y:
    Sub Filter_Return()
        Sheets("sheet1").Select
        Range("a1").Select
        Selection.CurrentRegion.Select
        row_count = Selection.Rows.Count - 1     ' Count the rows and
                                                 ' subtract the header.
    
        ' The following three lines run an AutoFilter using "Cat" as the
        ' criteria for the first column and greater than 0 as the
        ' criteria for the second column.
        Selection.AutoFilter
        Selection.AutoFilter Field:=1, Criteria1:="Cat"
        Selection.AutoFilter Field:=2, Criteria1:=">0"
        matched_criteria = 0                         ' Set variable to
                                                     ' zero.
        check_row = 0                                ' Set variable to
                                                     ' zero.
        While Not IsEmpty(ActiveCell)            ' Check to see if row
                                                 ' height is zero.
            ActiveCell.Offset(1, 0).Select
            If ActiveCell.RowHeight = 0 Then
                check_row = check_row + 1
            Else
                matched_criteria = matched_criteria + 1
            End If
        Wend
    
        If row_count = check_row Then            ' If these are equal,
                                                 ' nothing was returned.
            MsgBox "no matching data"
        Else
            MsgBox matched_criteria - 1          ' Display the number
                                                 ' of records returned.
        End If
    
    End Sub
    					
  2. Nh?p thng tin sau vo m?t b?ng tnh:
           A1:  Animal     B1:  In Stock         C1:  Price
           A2:  Dog        B2:  1                C2:  $1.00
           A3:  Cat        B3:  2                C3:  $2.00
           A4:  Dog        B4:  3                C4:  $3.00
           A5:  Cat        B5:  4                C5:  $4.00
           A6:  Bird       B6:  5                C6:  $5.00
    					
  3. Ch?y v? m.

    Microsoft Excel 97 v Microsoft Excel 98

    Trn cc Cng cu tr?nh n, i?m ?n V? m, v sau nh?p vo Macro. Trong cc V? m h?p, ch?n Filter_Return, v sau nh?p vo Ch?y.

    Microsoft Excel Phin b?n 5.0 v 7.0

    Trn cc Cng cu Tr?nh n, nh?p vo V? m. Trong cc V? m h?p, ch?n Filter_Return, v sau nh?p vo Ch?y.
V? m s? hi?n th? m?t h?p th m cc ti?u bang s? l?ng h? s quay tr? l?i, ho?c, n?u khng c d? li?u ?c tr? l?i, l "khng c d? li?u ph h?p." Trong cc v d? ?c a ra, h?p th tr? v? s? 2.

B?ng cch s? d?ng ch?c nng Subtotal

  1. S? d?ng cng m?t m?u d? li?u t? b?c 2, ? trn. N?u AutoFilter hi?n nay trn, i?m ?n B loc trn cc D? li?u tr?nh n, v sau b?m AutoFilter.
  2. Trong t? bo C8, g? cng th?c sau y:
          =SUBTOTAL(3,C2:C6)
    						
    LU ?: ?i s? ?u tin cho cc ch?c nng Subtotal l cc ch?c nng ?c s? d?ng ? tnh ton t?ng. Cc ?i s? trong v d? ny s? d?ng cc ch?c nng tnh (3) ? tnh ton t?ng.
  3. Ch?n cell A1, i?m ?n B loc trn cc D? li?u tr?nh n, v sau b?m AutoFilter.

    i?u ny l?n l?t vo AutoFilter, v b?n nh?n ?c m?t h?p th? xu?ng cho m?i ba l?nh v?c trong v d? ny.
  4. B?m vo h?p th? xu?ng trong cc ?ng v?t l?nh v?c, v sau b?m Mo trong danh sch th? xu?ng.

    AutoFilter l?c t?t c? cc b?n ghi v?i mo trong l?nh v?c ?ng v?t, v trong v d? ny, k?t qu? c?a cc ch?c nng Subtotal trong t? bo C8 l 2.

Thu?c tnh

ID c?a bi: 152215 - L?n xem xt sau cng: 18 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft Excel 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 98 for Macintosh
T? kha:
kbdtacode kbhowto kbprogramming kbmt KB152215 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:152215
Khc t Ni dung trong C s Kin thc Khng con c h tr
Bi vi?t ny ni v? cc s?n ph?m m Microsoft khng c?n h? tr? n?a. Do , bi vi?t ny ?c cung c?p "nguyn b?n" v s? khng ?c c?p nh?t.

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com