Formulas & Functions Excel 2007 - 13. Analyzing Data with Tables (1 người xem)

[BNTT]

13.6. Excel’s Table Functions


13.6.1. About Table Functions
Đôi điều về các hàm Table

To illustrate the table functions, consider an example. For example, if you want to calculate the sum of a table field, you can enter SUM(range), and Excel produces the result. If you want to sum only a subset of the field, you must specify as arguments the particular cells to use. For tables containing hundreds of records, however, this process is impractical.
Để minh họa các hàm Table, bạn xem xét một ví dụ sau. Ví dụ, bạn muốn tính tổng của một cột (field) trong Table, bạn có thể nhập SUM(range), và Excel cho ra kết quả. Nếu bạn chỉ muốn tính tổng một phần nào đó trong cột, bạn phải chỉ rõ ra trong đối số bạn sẽ tính những ô nào. Tuy nhiên, với những Table chứa hàng trăm record, thì việc này xem ra không thực tế.

The solution is to use DSUM(), which is the table equivalent of the SUM() function. The DSUM() function takes three arguments: a table range, field name, and criteria range. DSUM() looks at the specified field in the table and sums only records that match the criteria in the criteria range.
Giải pháp cho vấn đề trên là dùng hàm DSUM(), là một hàm Table tương đương với hàm SUM(). Hàm DSUM() cần ba đối số: một dãy Table, một tên field, và một dãy tiêu chuẩn. DSUM() tìm những record trong field đã chỉ định trong Table và chỉ cộng những record thỏa mãn tiêu chuẩn trong dãy tiêu chuẩn.

The table functions come in two varieties: those that don’t require a criteria range and those that do.
Các hàm Table có hai loại: một loại không yêu cầu phải có dãy tiêu chuẩn và một loại thì phải có dãy tiêu chuẩn.​

 

[BNTT]

13.6. Excel’s Table Functions


13.6.2. Table Functions That Don’t Require a Criteria Range
Những hàm Table không đòi hỏi phải có một dãy tiêu chuẩn (Criteria Range)

Excel has three table functions that enable you to specify the criteria as an argument rather than a range: COUNTIF(), SUMIF(), and AVERAGEIF().
Excel có ba hàm Table cho phép bạn xác định tiêu chuẩn ngay trong một đối số thay vì phải dùng một dãy tiêu chuẩn: COUNTIF(), SUMIF(), và AVERAGEIF().​

• Using COUNTIF - Sử dụng hàm COUNTIF

The COUNTIF() function counts the number of cells in a range that meet a single criterion:
Hàm COUNTIF() đếm số ô trong một dãy thỏa mãn một điều kiện nào đó.

COUNTIF (range, criteria)

For example, Figure 13.20 shows a COUNTIF() function that calculates the total number of products that have no stock (that is, where the Qty On Hand field equals zero).
Ví dụ, hình 13.20 minh họa một hàm COUNTIF() tính tổng số sản phẩm không có sẵn trong kho (nghĩa là, đếm những ô có giá trị bằng 0 trong field Qty On Hand)

Figure 13.20 - Tables.xlsx​

• Using SUMIF - Sử dụng hàm SUMIF

The SUMIF() function is similar to COUNTIF(), except that it sums the range cells that meet its criterion:
Hàm SUMIF() tương tự hàm COUNTIF(), ngoại trừ việc nó tính tổng của một dãy ô thỏa mãn một điều kiện nào đó.

SUMIF (range, criteria [, sum_range])

Figure 13.21 shows a Parts table. The SUMIF() function in cell F16 sums the Total Cost field for the parts where the Division field is equal to 3.
Hình 13.21 minh họa Table Parts. Hàm SUMIF() trong ô F16 tính tổng của những ô trong field (cột) Total Cost mà có giá trị tương ứng trong field Division là 3.

Figure 13.21 - Tables.xlsx​

• Using AVERAGEIF - Sử dụng hàm AVERAGEIF

The new AVERAGEIF() function calculates the average of a range of cells that meet its criterion:
Hàm AVERAGEIF() - chỉ có trong Excel 2007 - tính trung bình cộng của một dãy ô thỏa mãn một điều kiện nào đó.

AVERAGEIF (range, criteria [, average_range])

In Figure 13.22, the AVERAGEIF() function in cell F17 averages the Gross Margin field for the parts where the Cost field is less than 10.
Trong hình 13.22, hàm AVERAGEIF() trong ô F17 tính trung bình cộng của những ô trong field (cột) Gross Margin mà có giá trị tương ứng trong field Cost nhỏ hơn 10.

Figure 13.22 - Tables.xlsx​

 

[BNTT]

13.6. Excel’s Table Functions


13.6.3. Table Functions That Accept Multiple Criteria
Những hàm Table chấp nhận nhiều điều kiện

In previous versions of Excel, if you wanted to sum table values that satisfy two or more criteria, it was possible, but it usually required jumping through some serious formula hoops. For example, you could nest multiple IF() functions inside a SUM() function that’s entered as an array formula. It was doable, in other words, but it wasn’t for the faint of heart!
Trong những phiên bản trước của Excel, nếu bạn muốn tỉnh tổng các giá trị trong một bảng mà theo hai hoặc nhiều điều kiện, thì vẫn được, nhưng thường phải qua nhiều hàm trung gian phức tạp. Ví dụ, bạn có thể phải lồng hàm IF() vào bên trong một hàm SUM() rồi nhập với dạng công thức mảng. Nói cách khác, điều đó khả thi nhưng nó không dành cho những kẻ yếu tim!

Excel 2007 fixes all that by offering three new functions that enable you to specify multiple criteria: COUNTIFS(), SUMIFS(), and AVERAGEIFS(). Note that none of these functions requires a separate criteria range.
Excel 2007 sửa chữa các điều đó bằng cách cung cấp ba hàm mới, cho phép bạn xác định nhiều tiêu chuẩn: COUNTIFS(), SUMIFS(), và AVERAGEIFS(). Lưu ý rằng những hàm này cũng không đòi hỏi phải có một dãy tiêu chuẩn.​

• Using COUNTIFS - Sử dụng hàm COUNTIFS

The COUNTIFS() function counts the number of cells in one or more ranges that meet one or more criteria:
Hàm COUNTIFS() đếm số ô trong một dãy thỏa mãn một hoặc nhiều điều kiện nào đó.

COUNTIFS (range1, criteria1 [, range2, criteria2,...])

You can enter up to 127 range/criteria pairs. For example, Figure 13.23 shows a COUNTIFS() function that returns the number of customers where the Country field equals USA and the Region field equals OR (short for Oregon; don’t confuse this with Excel’s OR() function!).
Bạn có thể nhập tới 127 range/criteria (có nghĩa là bạn có thể sử dụng đến 127 điều kiện để đếm). Ví dụ, hình 12.23 minh họa một hàm COUNTIFS() trả về con số những khách hàng sống tại USA (giá trị trong field Country là USA) và ở tại vùng OR (giá trị nơi field Region là OR - viết tắt của chữ Oregon; đừng nhầm lẫn với hàm OR() của Excel!)

Figure 13.23 - Tables.xlsx​

• Using SUMIFS - Sử dụng hàm SUMIFS

The SUMIFS() function sums cells in one or more ranges that meet one or more criteria:
Hàm SUMIFS() tính tổng của những ô thỏa mãn một hay nhiều điều kiện nào đó.

SUMIFS (sum_range, range1, criteria1 [, range2, criteria2,...])

You can enter up to 127 range/criteria pairs. Figure 13.24 shows the Inventory table. The SUMIFS() function in cell G2 sums the Qty On Hand field for the products where the Product Name field includes Soup and the Qty On Hold field equals zero.
Bạn có thể nhập tới 127 range/criteria (có nghĩa là bạn có thể sử dụng đến 127 điều kiện để tính tổng). Hình 13.24 minh họa Table Inventory. Hàm SUUMIFS() trong ô G2 tính tổng của những sản phẩm trong field Qty On Hand mà giá trị tương ứng của chúng trong field Product Name là Soup và giá trị tương ứng trong field Qty On Hold bằng 0.

Figure 13.24 - Tables.xlsx​

• Using AVERAGEIFS - Sử dụng hàm AVERAGEIFS

The AVERAGEIFS() function averages cells in one or more ranges that meet one or more criteria:
Hàm AVERAGEIFS() tính trung bình cộng của một dãy ô thỏa mãn một hay nhiều điều kiện nào đó.

AVERAGEIFS (average_range, range1, criteria1 [, range2, criteria2,...])

You can enter up to 127 range/criteria pairs. Figure 13.25 shows the account receivable table. The AVERAGEIFS() function in cell G3 averages the Days Overdue field for the invoices where the Days Overdue is greater than 0 and where the Invoice Amount field is greater than or equal to 1000.
Bạn có thể nhập tới 127 range/criteria (có nghĩa là bạn có thể sử dụng đến 127 điều kiện để tính trung bình cộng). Hình 13.25 minh họa Table các khoản phải thu. Hàm AVERAGEIFS() trong ô G3 tính trung bình cộng của những hóa đơn trong field Days Overdue có số ngày quá hạn lớn hơn 0 và có giá trị lớn hơn hay bằng 1000 (trong field Invoice Amount).

Figure 13.25 - Tables.xlsx​

 

[BNTT]

13.6. Excel’s Table Functions


13.6.4. Table Functions That Require a Criteria Range
Những hàm Table đòi hỏi phải có một dãy tiêu chuẩn (Criteria Range)

The remaining table functions require a criteria range. These functions take a little longer to set up, but the advantage is that you can enter compound and computed criteria.
Những hàm Table còn lại đòi hỏi phải có một dãy tiêu chuẩn. Để thiết lập những hàm này thì hơi mất thời gian hơn, nhưng lợi ích là bạn có thể nhập các tiêu chuẩn phức tạp và các tiêu chuẩn dùng công thức (computed criteria).

Phần tiếp theo của bài này tôi đã trình bày chi tiết ở topic: Các hàm quản lý cơ sở dữ liệu và danh sách, nên tôi sẽ không trình bày lại cú pháp và chú giải các đối số của hàm nữa (không theo như nguyên bản cuốn sách này). Trong các bài dịch sau đây, khi nói đến một hàm nào, tôi sẽ tạo liên kết (link) đến bài viết về hàm đó. Nếu muốn tìm hiểu kỹ hơn về cú pháp và cách sử dụng các đối số (argument), các bạn theo những liên kết này để xem.

You enter table functions the same way you enter any other Excel function. You type an equals sign (=) and then enter the function — either by itself or combined with other Excel operators in a formula. The following examples all show valid table functions:
Bạn nhập các hàm Table này giống y như cách bạn nhập những hàm khác trong Excel. Bạn gõ một dấu bằng (=) và rồi nhập hàm — nhập chính nó hoặc kết hợp với những toán tử khác của Excel trong công thức. Những ví dụ sau đây minh họa những hàm Table hợp lệ:

=DSUM(A6:H14, “Total Cost”, A1:H3)
=DSUM(Table, “Total Cost”, Criteria)
=DSUM(AR_Table, 3, Criteria)
=DSUM(1993_Sales, “Sales”, A1:H13)

The next two sections provide examples of the DAVERAGE() and DGET() table functions.
Hai phần tiếp theo đây sẽ trình bày các ví dụ của hàm DAVERAGE() và hàm DGET().​

 

[BNTT]

13.6.4. Table Functions That Require a Criteria Range

• Using DAVERAGE - Sử dụng Hàm DAVERAGE

The DAVERAGE() function calculates the average field value in the database records that match the criteria. In the Parts database, for example, suppose that you want to calculate the average gross margin for all parts assigned to Division 2. You set up a criteria range for the Division field and enter 2, as shown in Figure 13.26. You then enter the following DAVERAGE() function (see cell H3):
Hàm DAVEGARE() tính trung bình của các giá trị thỏa mãn tiêu chuẩn nào đó trong một cột (field) của một Table. Ví dụ, trong Table Parts database, giả sử rằng bạn muốn tính tổng lợi nhuận (Gross margin) trung bình của tất cả các part thuộc về Division 2. Bạn thiết lập một dãy tiêu chuẩn cho cột Division và nhập vào 2, như minh họa ở hình 13.26. Rồi bạn nhập hàm DAVERAGE() sau đây (xem ô H3):

=DAVERAGE(Parts[#All], "Gross Margin", A2:A3)

Figure 13.26 - Tables.xlsx​

• Using DGET - Sử dụng Hàm DGET

The DGET() function extracts the value of a single field in the database records that match the criteria. If there are no matching records, DGET() returns #VALUE!. If there is more than one matching record, DGET() returns #NUM!.
DGET() typically is used to query the table for a specific piece of information. For example, in the Parts table, you might want to know the cost of the Finley Sprocket. To extract this information, you would first set up a criteria range with the Description field and enter Finley Sprocket. You would then extract the information with the following formula (assuming that the table and criteria ranges are named Parts and Criteria, respectively):
Hàm DGET() trích ra giá trị thỏa mãn tiêu chuẩn nào đó từ một cột (field) trong một Table. Nếu không có giá trị nào thỏa mãn tiêu chuẩn, DGET() trả về #VALUE!. Nếu có nhiều hơn một giá trị thỏa mãn tiêu chuẩn, DGET() trả về #NUM!. Ví dụ, trong Table Parts, bạn muốn biết chi phí của Finley Sprocket. Để trích ra thông tin này, trước hết bạn thiết lập một dãy tiêu chuẩn với cột Description và nhập vào Finley Sprocket. Rồi bạn trích ra thông tin với công thức sau đây (giả sử rằng Table và dãy tiêu chuẩn lần lượt đã có tên là Parts và Criteria):

=DGET(Parts[#All], "Cost", Criteria)

A more interesting application of this function would be to extract the name of a part that satisfies a certain condition. For example, you might want to know the name of the part that has the highest gross margin. Creating this model requires two steps:
Một ứng dụng thú vị của hàm này là trích ra tên của một thành phần thỏa mãn một tiêu chuẩn nhất định nào đó. Ví dụ, bạn muốn biết tên của thành phần có tổng lợi nhuận cao nhất. Bạn tạo mô hình theo hai bước sau:

1. Set up the criteria to match the highest value in the Gross Margin field.
   Thiết lập dãy tiêu chuẩn để tìm giá trị cao nhất trong cột Gross Margin.
   
   
2. Add a DGET() function to extract the description of the matching record.
   Thêm một hàm DGET() để trích ra thông tin mô tả của record tìm được.

Figure 13.27 shows how this is done. For the criteria, a new field called Highest Margin is created. As the text box shows, this field uses the following computed criteria:
Hình 13.27 minh họa điều này làm việc như thế nào. Với tiêu chuẩn, một field mới có tên là Highest Margin đã được thêm vào. Như minh họa ở textbox trong hình, field này sử dụng tiêu chuẩn dùng công thức sau đây:

=H7 = MAX(Parts2[Gross Margin])

Figure 13.27 - Tables.xlsx​

Excel matches only the record that has the highest gross margin. The DGET() function in cell H3 is straightforward:
Excel chỉ tìm record nào có tổng lợi nhuận cao nhất. Còn hàm DGET() ở ô H3 thì đơn giản như sau:

=DGET(Parts2[#All], "Description", A2:A3)

This formula returns the description of the part that has the highest gross margin.
Công thức này trả về mô tả (tên) của thành phần có tổng lợi nhuận cao nhất.​

 

[BNTT]

Case Study


13.7. Applying Statistical Table Functions to a Defects Database
Áp dụng các hàm thống kê Table vào Defects Database (cơ sở dữ liệu các sản phẩm lỗi)

Many table functions are most often used to analyze statistical populations. Figure 13.28 shows a table of defects found among 12 work groups in a manufacturing process. In this example, the table (B315) is named Defects, and two criteria ranges are used — one for each of the group leaders, Johnson (G3:G4 is Criteria1) and Perkins (H3:H4 is Criteria2).
Nhiều hàm Table thường được dùng để phân tích thống kê các tập hợp. Hình 13.28 minh họa Table các sản phẩm lỗi được tìm thấy từ 12 nhóm làm việc trong một quá trình sản xuất. Trong ví dụ này, Table (B3:B15) được đặt tên là Defects, và có hai dãy tiêu chuẩn được sử dụng — mỗi dãy cho mỗi trưởng nhóm: Jonhson (G3:G4, là Criteria1) và Perkins (H3:H4, là Criteria2).

Figure 13.28 - Tables.xlsx​

The table shows several calculations. First, DMAX() and DMIN() are calculated for each criteria. The range (a statistic that represents the difference between the largest and smallest numbers in the sample; it’s a crude measure of the sample’s variance) is then calculated using the following formula (Johnson’s groups):
Table này minh họa nhiều phép tính. Trước hết, DMAX() và DMIN() tính toán dựa theo mỗi tiêu chuẩn (Maximum và Minimum). Range (một số liệu thống kê tượng trưng cho chênh lệch giữa các số lớn nhất và các số nhỏ nhất trong mẫu, là một phương sai dạng thô của mẫu) được tính toán bởi công thức sau đây (cho nhóm của Johnsin):

=DMAX(Defects[#All], "Defects", Criteria1) − DMIN(Defects[#All], "Defects", Criteria1)

Of course, instead of using DMAX() and DMIN() explicitly, you can simply refer to the cells containing the DMAX() and DMIN() results.
Dĩ nhiên, thay vì dùng DMAX() và DMIN() một cách cụ thể như trên, bạn chỉ cần đơn giản tham chiếu đến những ô chứa kết quả của DMAX() và DMIN().

The next line uses DAVERAGE() to find the average number of defects for each group leader. Notice that the average for Johnson’s groups (11.67) is significantly higher than that for Perkins’s groups (8.67). However, Johnson’s average is skewed higher by one anomalously large number (26), and Perkins’s average is skewed lower by one anomalously small number (0).
Dòng kế tiếp sử dụng hàm DAVERAGE() để tìm số trung bình các sản phẩm lỗi trong mỗi nhóm. Chú ý rằng, trung bình của nhóm Johnson (11.67) cao hơn đáng kể so với trung bình của nhóm Perkins (8.67). Tuy nhiên, trung bình của nhóm Johnson bị lệch cao hơn bởi một số quá lớn (26), còn trùng bình của nhóm Perkins bị lệch thấp hơn bởi một số nhỏ quá (0).

To allow for this situation, the Adjusted Avg line uses DSUM(), DCOUNT(), and the DMAX() and DMIN() results to compute a new average without the largest and smallest number for each sample. As you can see, without the anomalies, the two leaders have the same average.
Để tránh tình huống này, dòng Adjusted Avg sử dụng DSUM(), DCOUNT(), và kết quả của DMAX() và DMIN() để tính một giá trị trung bình khác, không bao gồm con số lớn nhất và nhỏ nhất trong mỗi mẫu. Như bạn thấy, khi không có những số quá lớn hoặc quá nhỏ, cả hai nhóm có cùng một giá trị trung bình.

NOTE:

As shown in cell G10 of Figure 13.28, if you don’t include a field argument in the DCOUNT() function, it returns the total number of records in the table.
Như minh họa ở ô G10 của hình 13.28, nếu bạn không đưa đối số field vào trong hàm DCOUNT(), nó sẽ trả về tổng số record có trong Table.​

The rest of the calculations use the DSTDEV(), DSTDEVP(), DVAR(), and DVARP() functions.
Các phép tính còn lại sử dụng các hàm DSTDEV(), DSTDEVP(), DVAR(), và DVARP().​

------------------------------------- End of Chapter 13 -------------------------------------​

 

[duyetpn]

Thật tuyệt vời, cám ơn bác nhiều!
river city quận 7, căn hộ River City quận 7