Đăng ký học Excel và VBA cùng GPE tháng 11 - TPHCM

Mua sách "VBA trong Excel - Cải thiện và tăng tốc" tái bản

Cách sử dụng hàm Countifs

Thảo luận trong 'Hàm và công thức Excel' bắt đầu bởi vungo2007, 26 Tháng một 2007.

  1. vungo2007

    vungo2007 Thành viên mới

    Bạn nào biết cách sử dụng hàm "Countifs" (có chữ s) xin chỉ giúp giùm. Tôi chân thành cảm ơn nhiều!
     
  2. Đào Việt Cường

    Đào Việt Cường Cu Tí sành điệu

    Với phiên bản mới Office 2007, Excel cung cấp thêm cho chúng ta các hàm tính toán với nhiều điều kiện. Bạn tham khảo hàm SUMIFS để vận dụng tương tự như hàm COUNTIFS

     
  3. Mr Okebab

    Mr Okebab Ngon Ngất Ngây

    Bạn xem nhé :
    Applies to: Microsoft Office Excel 2007

    Counts the number of cells within a range that meet multiple criteria.
    Syntax
    COUNTIFS(range1, criteria1,range2, criteria2…)
    Range1, range2, … are 1 to 127 ranges in which to evaluate the associated criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
    Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be counted. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
    Remarks
    • Each cell in a range is counted only if all of the corresponding criteria specified are true for that cell.
    • If criteria is an empty cell, COUNTIFS treats it as a 0 value.
    • You can use the wildcard characters, question mark (?) and asterisk (*), in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
    Example
    The example may be easier to understand if you copy it to a blank worksheet.
    [​IMG]How to copy an example
    1. Create a blank workbook or worksheet.
    2. Select the example in the Help topic.
    Note Do not select the row or column headers.
    [​IMG]
    Selecting an example from Help
    1. Press CTRL+C.
    2. In the worksheet, select cell A1, and press CTRL+V.
    3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
    Ví dụ


    0….A……………B………..……C….………D
    1…Davolio……...Yes…………...No………..No
    2….Buchanan…...Yes…………..Yes………..No
    3…Suyama…….Yes…………….Yes…….…Yes
    4…..Leverling…No……………..Yes………..Yes





    =COUNTIFS(B2:D2,"=Yes") = 1
    =COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes") = 2
    =COUNTIFS(B5:D5,"=Yes",B3:D3,"=Yes") = 1



    Thân!
     
  4. Mr Okebab

    Mr Okebab Ngon Ngất Ngây

  5. Mr Okebab

    Mr Okebab Ngon Ngất Ngây

    Sau đây là một số hàm của O2007:

    Function..............Description
    AVEDEV................Returns the average of the absolute deviations of data points from their mean
    AVERAGE...............Returns the average of its arguments
    AVERAGEA..............Returns the average of its arguments, including numbers, text, and logical values
    AVERAGEIF.............Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria
    AVERAGEIFS............Returns the average (arithmetic mean) of all cells that meet multiple criteria.
    BETADIST..............Returns the beta cumulative distribution function
    BETAINV...............Returns the inverse of the cumulative distribution function for a specified beta distribution
    BINOMDIST.............Returns the individual term binomial distribution probability
    CHIDIST...............Returns the one-tailed probability of the chi-squared distribution
    CHIINV................Returns the inverse of the one-tailed probability of the chi-squared distribution
    CHITEST...............Returns the test for independence
    CONFIDENCE............Returns the confidence interval for a population mean
    CORREL................Returns the correlation coefficient between two data sets
    COUNT.................Counts how many numbers are in the list of arguments
    COUNTA................Counts how many values are in the list of arguments
    COUNTBLANK............Counts the number of blank cells within a range
    COUNTIF...............Counts the number of cells within a range that meet the given criteria
    COUNTIFS..............Counts the number of cells within a range that meet multiple criteria
    COVAR.................Returns covariance, the average of the products of paired deviations
    CRITBINOM.............Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
    DEVSQ.................Returns the sum of squares of deviations
    EXPONDIST.............Returns the exponential distribution
    FDIST.................Returns the F probability distribution
    FINV..................Returns the inverse of the F probability distribution
    FISHER................Returns the Fisher transformation
    FISHERINV.............Returns the inverse of the Fisher transformation
    FORECAST..............Returns a value along a linear trend
    FREQUENCY.............Returns a frequency distribution as a vertical array
    FTEST.................Returns the result of an F-test
    GAMMADIST.............Returns the gamma distribution
    GAMMAINV..............Returns the inverse of the gamma cumulative distribution
    GAMMALN...............Returns the natural logarithm of the gamma function, Γ(x)
    GEOMEAN...............Returns the geometric mean
    GROWTH................Returns values along an exponential trend
    HARMEAN...............Returns the harmonic mean
    HYPGEOMDIST...........Returns the hypergeometric distribution
    INTERCEPT.............Returns the intercept of the linear regression line
    KURT..................Returns the kurtosis of a data set
    LARGE.................Returns the k-th largest value in a data set
    LINEST................Returns the parameters of a linear trend
    LOGEST................Returns the parameters of an exponential trend
    LOGINV................Returns the inverse of the lognormal distribution
    LOGNORMDIST...........Returns the cumulative lognormal distribution
    MAX...................Returns the maximum value in a list of arguments
    MAXA..................Returns the maximum value in a list of arguments, including numbers, text, and logical values
    MEDIAN................Returns the median of the given numbers
    MIN...................Returns the minimum value in a list of arguments
    MINA..................Returns the smallest value in a list of arguments, including numbers, text, and logical values
    MODE..................Returns the most common value in a data set
    NEGBINOMDIST..........Returns the negative binomial distribution
    NORMDIST..............Returns the normal cumulative distribution
    NORMINV...............Returns the inverse of the normal cumulative distribution
    NORMSDIST.............Returns the standard normal cumulative distribution
    NORMSINV..............Returns the inverse of the standard normal cumulative distribution
    PEARSON...............Returns the Pearson product moment correlation coefficient
    PERCENTILE............Returns the k-th percentile of values in a range
    PERCENTRANK...........Returns the percentage rank of a value in a data set
    PERMUT................Returns the number of permutations for a given number of objects
    POISSON...............Returns the Poisson distribution
    PROB..................Returns the probability that values in a range are between two limits
    QUARTILE..............Returns the quartile of a data set
    RANK..................Returns the rank of a number in a list of numbers
    RSQ...................Returns the square of the Pearson product moment correlation coefficient
    SKEW..................Returns the skewness of a distribution
    SLOPE.................Returns the slope of the linear regression line
    SMALL.................Returns the k-th smallest value in a data set
    STANDARDIZE...........Returns a normalized value
    STDEV.................Estimates standard deviation based on a sample
    STDEVA................Estimates standard deviation based on a sample, including numbers, text, and logical values
    STDEVP................Calculates standard deviation based on the entire population
    STDEVPA...............Calculates standard deviation based on the entire population, including numbers, text, and logical values
    STEYX.................Returns the standard error of the predicted y-value for each x in the regression
    TDIST.................Returns the Student's t-distribution
    TINV..................Returns the inverse of the Student's t-distribution
    TREND.................Returns values along a linear trend
    TRIMMEAN..............Returns the mean of the interior of a data set
    TTEST.................Returns the probability associated with a Student's t-test
    VAR...................Estimates variance based on a sample
    VARA..................Estimates variance based on a sample, including numbers, text, and logical values
    VARP..................Calculates variance based on the entire population
    VARPA.................Calculates variance based on the entire population, including numbers, text, and logical values
    WEIBULL...............Returns the Weibull distribution
    ZTEST.................Returns the one-tailed probability-value of a z-test

    Thân!
     
  6. nationalfox

    nationalfox Thành viên mới

    Trong trường hợp tôi muốn đếm các ô trống (rỗng) thì sử dụng thế nào??? Excell 2010 kg thực hiện được Countifs(A1:A5, ">100", B1:B5,"=""""")
     
  7. dat_butmuc

    dat_butmuc weitə r ə'pɔn prɔvidəns

    Phải như thế này:
    PHP:
    =COUNTIFS(A1:A30,">100",B1:B30,"")
     

Chia sẻ trang này