Cách sử dụng hàm Countifs (2 người xem)

[vungo2007]

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!

 

[Đào Việt Cường]

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

SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2…)
Sum_range is one or more cells to sum, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.
Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.
Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.
Remarks

• Each cell in sum_range is summed only if all of the corresponding criteria specified are true for that cell.
• Cells in sum_range that contain TRUE evaluate as 1; cells in sum_range that contain FALSE evaluate as 0 (zero).
• Unlike the range and criteria arguments in the SUMIF function, in SUMIFS each criteria_range must be the same size and shape as sum_range.
• 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.

 

[Mr Okebab]

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!

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.

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.

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(B22,"=Yes") = 1
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes") = 2
=COUNTIFS(B55,"=Yes",B33,"=Yes") = 1



Thân!

 

[Mr Okebab]

Trong O2007 còn có rất nhiều hàm có chữ s đằng sau nhằm cải thiện những khiếm khuyết của các Ver trước. VD : Sumifs; Averageifs . .
bạn xem nhé :
SUMIFS
http://office.microsoft.com/en-us/excel/HA100475041033.aspx?pid=CH100645361033
Averageifs
http://office.microsoft.com/en-us/excel/HA100474931033.aspx?pid=CH100645381033
..................................
Thân!

 

[Mr Okebab]

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!

 

[nationalfox]

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.

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.

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(B22,"=Yes&quot = 1
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes&quot = 2
=COUNTIFS(B55,"=Yes",B33,"=Yes&quot = 1



Thân!

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,"=""""")

 

[dat_butmuc]

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,"=""""")

Phải như thế này:

=COUNTIFS(A1:A30,">100",B1:B30,"")