Hiển thị kết quả tìm kiếm từ 1 đến 7 trên tổng số: 7
  1. #1
    Tham gia ngày
    01 2007
    Bài gởi
    1
    Cảm ơn
    0
    Được cảm ơn 0 lần trong 0 bài viết

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

    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. #2
    Tham gia ngày
    06 2006
    Nơi Cư Ngụ
    Nha Trang
    Bài gởi
    528
    Cảm ơn
    185
    Được cảm ơn 740 lần trong 289 bài viết
    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.
    Always careful - Always successful

  3. #3
    Tham gia ngày
    08 2006
    Bài gởi
    3,267
    Cảm ơn
    1,733
    Được cảm ơn 3,751 lần trong 1,767 bài viết
    Trích Nguyên văn bởi 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!
    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]file:///C:/DOCUME%7E1/TranHieu/LOCALS%7E1/Temp/msohtml1/01/clip_image001.gif[/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]file:///C:/DOCUME%7E1/TranHieu/LOCALS%7E1/Temp/msohtml1/01/clip_image002.gif[/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. #4
    Tham gia ngày
    08 2006
    Bài gởi
    3,267
    Cảm ơn
    1,733
    Được cảm ơn 3,751 lần trong 1,767 bài viết
    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/ex...CH100645361033
    Averageifs
    http://office.microsoft.com/en-us/ex...CH100645381033
    ..................................
    Thân!

  5. #5
    Tham gia ngày
    08 2006
    Bài gởi
    3,267
    Cảm ơn
    1,733
    Được cảm ơn 3,751 lần trong 1,767 bài viết
    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. #6
    Trích Nguyên văn bởi Mr Okebab View Post
    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]file:///C:/DOCUME%7E1/TranHieu/LOCALS%7E1/Temp/msohtml1/01/clip_image001.gif[/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]file:///C:/DOCUME%7E1/TranHieu/LOCALS%7E1/Temp/msohtml1/01/clip_image002.gif[/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!
    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. #7
    Tham gia ngày
    05 2008
    Nơi Cư Ngụ
    TP HCM
    Bài gởi
    1,735
    Cảm ơn
    807
    Được cảm ơn 2,227 lần trong 1,177 bài viết
    Trích Nguyên văn bởi nationalfox View Post
    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:
    PHP Code:
    =COUNTIFS(A1:A30,">100",B1:B30,""
    Reject
    (Debt – Disease – Drugs – Depression – Dice – Divorce – Dalliance – Death – Drinking – Deviancy - Demoralized people - Demotivation - poor Direction)

    "NHẠC GÌ CŨNG NHẢY..."
    anhtunhn@yahoo.com

Thông tin về chủ đề này

Users Browsing this Thread

Hiện có 1 người đang xem đề tài này. (0 thành viên và 1 khách)

Đề tài tương tự

  1. Hỏi về hàm countifs
    Viết bởi linhnhung trong chuyên mục Hướng dẫn sử dụng các hàm trong Excel
    Trả lời: 5
    Bài mới gởi: 04-03-13, 09:06 PM
  2. Hỏi về hàm countifs hoặc các hàm tương đương
    Viết bởi blon trong chuyên mục Giải thích, gỡ rối, xử lý lỗi công thức
    Trả lời: 5
    Bài mới gởi: 28-02-13, 07:23 PM
  3. Cần giúp đỡ về hàm Countifs
    Viết bởi blon trong chuyên mục Giải thích, gỡ rối, xử lý lỗi công thức
    Trả lời: 9
    Bài mới gởi: 29-01-13, 10:01 AM
  4. Giúp E xử lý đúng với hàm Countifs này với ạ.
    Viết bởi trananhtommy trong chuyên mục Hàm và công thức Excel
    Trả lời: 1
    Bài mới gởi: 30-07-12, 08:54 AM
  5. Hỏi về hàm countifs
    Viết bởi teutamteu trong chuyên mục Hàm và công thức Excel
    Trả lời: 3
    Bài mới gởi: 20-02-12, 09:27 PM

Bookmarks

Bookmarks

Quyền Sử Dụng Ở Diễn Ðàn

  • Bạn không thể đăng đề tài mới
  • Bạn không thể đăng trả lời
  • Bạn không thể đăng file đính kèm.
  • Bạn không thể sửa bài viết.
  •  

Mudim v0.8 Tắt VNI Telex Viqr Tổng hợp
Chính tả Bỏ dấu kiểu mới  [Bật/Tắt (F9)]