Formulas & Functions Excel 2007 - 12. Working with Statistical Functions

[BNTT]

Phỏng dịch từ cuốn Formulas and Functions with Microsoft Office Excel 2007 của Paul McFedries

Part II: HARNESSING THE POWER OF FUNCTIONS - Tận dụng sức mạnh của các hàm

• Chapter 6: Understanding Functions - Tìm hiểu các hàm
  
  
• Chapter 7: Working with Text Functions - Làm việc với các hàm xử lý chuỗi văn bản
  
  
• Chapter 8: Working with Logical and Information Functions - Làm việc với các hàm luận lý và tra cứu thông tin
  
  
• Chapter 9: Working with Lookup Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11284 - Làm việc với các hàm tìm kiếm
  
  
• Chapter 10: Working with Date and Time Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11365 - Làm việc với các hàm ngày tháng và thời gian
  
  
• Chapter 11: Working with Math Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11551 - Làm việc với các hàm toán học
  
  
• Chapter 12: Working with Statistical Functionshttp://www.giaiphapexcel.com/forum/showthread.php?t=11623 - Làm việc với các hàm thống kê

---------------------------------------------------------------------------------------


PART II - HARNESSING THE POWER OF FUNCTIONS
Phần II - Tận dụng sức mạnh của các hàm


Chapter 12 - WORKING WITH STATISTICAL FUNCTIONS
Chương 12 - Làm việc với các hàm thống kê

Excel’s statistical functions calculate all the standard statistical measures, such as average, maximum, minimum, and standard deviation. For most of the statistical functions, you supply a list of values (which could be an entire population or just a sample from a population). You can enter individual values or cells, or you can specify a range. Excel has dozens of statistical functions, many of which are
rarely, if ever, used in business.
Những hàm thống kê của Excel tính toán tất cả những các số đo thống kê chuẩn như trung bình, lớn nhất, nhỏ nhất, và độ lệch chuẩn. Đối với hầu hết các hàm thống kê, bạn cung cấp cho nó một danh sách các giá trị (có thể là toàn bộ tập hợp hay chỉ là một mẫu của tập hợp). Bạn có thể nhập những giá trị hoặc những ô riêng lẻ, hay là xác định một mảng. Excel có hàng chục hàm thống kê, có nhiều hàm trong số đó hiếm khi được sử dụng trong công việc kinh doanh.​

12.1. Understanding Descriptive Statistics
Tìm hiểu về thống kê mô tả

One of the goals of this book is to show you how to use formulas and functions to turn a jumble of numbers and values into results and summaries that give you useful information about the data. Excel’s statistical functions are particularly useful for extracting analytical sense out of data nonsense. Many of these functions might seem strange and obscure, but they reward a bit of patience and effort with striking new views of your data.
Một trong những mục đích của cuốn sách này là trình bày cho bạn cách sử dụng các công thức và các hàm để biến một mớ hỗn độn những con số và những giá trị thành những kết quả và bảng tổng kết, nhằm cho bạn thông tin hữu dụng về dữ liệu. Các hàm thống kê của Excel đặc biệt hữu dụng cho việc trích xuất ra những phân tích có nghĩa khỏi những dữ liệu vô nghĩa. Nhiều hàm trong số này có vẻ lạ lẫm và khó hiểu, nhưng chúng sẽ đền đáp cho sự kiên nhẫn và nỗ lực của bạn bằng những cái nhìn mới đáng ngạc nhiên cho dữ liệu.

This is particularly true of the branch of statistics known casually as descriptive statistics (or summary statistics). As the name implies, descriptive statistics are used to describe various aspects of a data set, to give you a better overall picture of the phenomenon underlying the numbers. In Excel’s statistical repertoire, 16 measures make up its descriptive statistics package: sum, count, mean, median, mode, maximum, minimum, rank, kth largest, kth smallest, standard deviation, variance, standard error of the mean, confidence level, kurtosis, and skewness.
Điều này đặc biệt đúng với nhóm thống kê được biết với tên là thống kê mô tả (hay thống kê tổng hợp). Như tên gọi của nó, thống kê mô tả được sử dụng để mô tả những khía cạnh khác nhau của một tập hợp dữ liệu, nhằm mang lại cho bạn một cái nhìn rõ ràng hơn về sự thật ở bên dưới các con số. Trong kho thống kê của Excel, có 16 số đo thống kê tạo nên một gói thống kê mô tả: sum (tính tổng), count (đếm), mean (giá trị trung bình), median (trung bình vị), mode (số lần xuất hiện), maximum (giá trị lớn nhất), minimum (giá trị nhỏ nhất), rank (thứ hạng), kth largest (giá trị lớn thứ k), kth smallest (giá trị nhỏ thứ k), standard deviation (độ lệch chuẩn), variance (phương sai), và những lỗi thông thường của giá trị trung bình, mức tin cậy, độ nhọn, hệ số lệch...

In this chapter, you’ll learn how to wield all of these statistical measures (except sum, which you’ve already seen earlier in this book).
Trong chương này, bạn sẽ học cách nắm vững tất cả các số đo thống kê (ngoại trừ sum(tính tổng) bạn đã học trong phần trước).​

You can download the workbook that contains this chapter’s examples here:
Bạn có thể tải về bảng tính với những ví dụ trong chương này tại đây:

www.mcfedries.com/Excel2007Formulas/​

Trong chương này, do hầu hết các hàm tôi đã trình bày chi tiết ở topic: Các hàm Thống kê, nên tôi sẽ không trình bày lại danh sách các hàm, cú pháp và chú giải các đối số của mỗi 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.

 

[BNTT]

Chapter 12 - WORKING WITH STATISTICAL FUNCTIONS


12.2. Counting Items with the COUNT() Function
Đếm các phần tử với Hàm COUNT

The simplest of the descriptive statistics is the total number of values, which is given by the COUNT() function:
Thống kê đơn giản nhất trong số các thống kê mô tả là tổng số các giá trị được cho bởi hàm COUNT():

COUNT (value1 [,value2,...])

The COUNT() function counts only the numeric values that appear in the list of arguments. Text values, dates, logical values, and errors are ignored. In the worksheet shown in Figure 12.1, the following formula is used to count the number of defect values in the database:
Hàm COUNT() chỉ đếm các giá trị số ở trong danh sách các đối số. Các giá trị text, giá trị ngày tháng, giá trị logic, và các lỗi sẽ được bỏ qua. Trong bảng tính minh họa ở hình 12.1, công thức sau đây được dùng để đếm số sản phẩm bị lỗi trong cơ sở dữ liệu:

=COUNT(D322)

Figure 12.1 - Statistics.xlsx​

TIP:

To get a quick look at the count, select the range or, if you’re working with data in a table, select a single column in the table. Excel displays the Count in the status bar.
Để xem nhanh kết quả của việc đếm, bạn chọn dãy muốn đếm, hoặc nếu bạn đang làm việc với dữ liệu dạng bảng, thì chọn một cột đơn trong bảng. Excel sẽ hiển thị tất cả những gì nó đếm được ở mục Count dưới thanh trạng thái (Status bar).

If you want to know how many numeric values are in the selection, right-click the status bar and then click the Numerical Count value.
Nếu bạn chỉ muốn biết có bao nhiêu giá trị số (numeric value) trong vùng đang chọn, bạn nhấn nút chuột phải vào thanh trạng thái và chọn giá trị Numerical Count.

 

[BNTT]

Chapter 12 - WORKING WITH STATISTICAL FUNCTIONS


12.3. Calculating Averages
Tính trung bình

The most basic statistical analysis worthy of the name is probably the average, although you always need to ask yourself which average you need. There are three: mean, median, and mode. The next few sections show you the worksheet functions that calculate them.
Sự phân tích thống kê cơ bản đáng chú ý nhất có lẽ là giá trị trung bình, mặc dù bạn luôn cần phải biết bạn muốn có loại trung bình nào. Có ba loại: trung bình cộng (mean), trung bình vị (median), và trung bình trọng số (mode). Vài phần tiếp theo đây sẽ trình bày cho bạn những hàm để tính các giá trị trung bình này.​

12.3.1. The AVERAGE Function — Hàm AVERAGE

The mean is what you probably think of when someone uses the term average. That is, it’s the arithmetic mean of a set of numbers. In Excel, you calculate the mean using the AVERAGE() function:
Mean nghĩa là những gì bạn có thể nghĩ đến khi người ta sử dụng thuật ngữ average. Nghĩa là, nó là trung bình cộng của một tập hợp các con số. Trong Excel, bạn tính trung bình cộng bằng hàm AVERAGE():

AVERAGE (number1 [, number2,...])

For example, to calculate the mean of the values in the defects database, you use the following formula:
Ví dụ, để tính trung bình cộng của những giá trị trong cơ sở dữ liệu các sản phẩm lỗi (xem lại ví dụ của bài trước, hình 12.1), bạn dùng công thức sau:

=AVERAGE(D322)

TIP:

If you need just a quick glance at the mean value, select the range. Excel displays the Average in the status bar.
Nếu bạn chỉ cần xem lướt qua giá trị trung bình cộng của môt dãy, bạn chọn nó, Excel hiển thị giá trị này ở mục Average dưới thanh trạng thái.

CAUTION:

The AVERAGE() function (as well as the MEDIAN() and MODE() functions discussed in the next two sections) ignores text and logical values. It also ignores blank cells, but it does not ignore cells that contain the value 0.
Hàm AVERAGE() (cũng như hàm MEDIAN() và hàm MODE() sẽ nói đến trong hai phần tiếp theo đây) bỏ qua các giá trị text và các giá trị logic. Nó cũng bỏ qua các ô rỗng, nhưng nó không bỏ qua những ô có giá trị là 0.​

12.3.2. The MEDIAN Function — Hàm MEDIAN

The median is the value in a data set that falls in the middle when all the values are sorted in numeric order. That is, 50% of the values fall below the median, and 50% fall above it. The median is useful in data sets that have one or two extreme values that can throw off the mean result because the median is not affected by extremes. You calculate the median using the MEDIAN() function:
Median — trung bình vị — là một giá trị nằm ở giữa một tập hợp các dữ liệu, khi tất cả các giá trị được sắp xếp theo thứ tự. Nghĩa là, có 50% giá trị xếp dưới median và 50% giá trị xếp trên median. Trung bình vị hữu dụng trong một tập hợp dữ liệu có một hoặc hai giá trị cực trị có thể đưa đến kết quả trung bình, bởi vì trung bình vị không phụ thuộc vào các cực trị. Bạn sử dụng hàm MEDIAN() để tính trung bình vị:

MEDIAN (number1 [, number2,...])

For example, to calculate the median of the values in the defects database, you use the following formula:
Ví dụ, để tính trung bình vị của những giá trị trong cơ sở dữ liệu các sản phẩm lỗi, bạn dùng công thức sau:

=MEDIAN(D322)​

12.3.3. The MODE Function — Hàm MODE

The mode is the value in a data set that occurs most frequently. The mode is most useful when you’re dealing with data that doesn’t lend itself to being either added (necessary for calculating the mean) or sorted (necessary for calculating the median). For example, you might be tabulating the result of a poll that included a question about the respondent’s favorite color. The mean and median don’t make sense with such a question, but the mode will tell you which color was chosen the most.
Mode là một giá trị xuất hiện nhiều lần nhất trong một tập hợp các dữ liệu. Mode rất hữu dụng khi bạn làm việc với những dữ liệu không thích hợp với việc cộng (việc cần thiết để tính trung bình cộng) hoặc sắp xếp theo thứ tự (cần thiết cho việc tính trung bình vị). Ví dụ, bạn có thể lập một bảng kết quả của một thăm dò có bao gồm một câu hỏi về màu da ưa thích nhất. Trung bình cộng (mean) và trung bình vị (median) không thích hợp với một câu hỏi như vậy, nhưng mode sẽ cho bạn biết màu nào được chọn nhiều nhất.

You calculate the mode using the MODE() function:
Bạn tính mode (số lần xuất hiện nhiều nhất) bằng hàm MODE():

MODE (number1 [, number2,...])

For example, to calculate the mode of the values in the defects database, you use the following formula:
Ví dụ, để tính giá trị xuất hiện nhiều nhất trong cơ sở dữ liệu các sản phẩm lỗi, bạn dùng công thức sau:

=MODE(D322)​

 

[BNTT]

12.3. Calculating Averages


12.3.4. Calculating the Weighted Mean
Tính trung bình gia trọng

In some data sets, one value might be more important than another. For example, suppose that your company has several divisions, the biggest of which generates $100 million in annual sales and the smallest of which generates only $1 million in sales. If you want to calculate the average profit margin for the divisions, it doesn’t make sense to treat the divisions equally because the largest is two orders of magnitude bigger than the smallest. You need some way of factoring the size of each division into your average profit margin calculation.
Trong một số tập hợp dữ liệu, có thể có một giá trị thì quan trọng hơn những giá trị khác. Ví dụ, giả sử rằng công ty của bạn có vài bộ phận, bộ phận lớn nhất đem lại $100 triệu trong tổng doanh thu hằng năm, còn bộ phận nhỏ nhất chỉ mang lại doanh thu có $1 triệu. Nếu bạn muốn tính mức lợi nhuận trung bình cho các bộ phận, việc xem các bộ phận đều như nhau thì không hợp lý bởi vì bộ phận lớn nhất hơn bộ phận nhỏ nhất đến hai bậc. Bạn cần có cách khác để đưa quy mô của mỗi bộ phận vào trong phép tính trung bình lợi nhuận.

You can do this by calculating the weighted mean. This is an arithmetic mean in which each value is weighted according to its importance in the data set. Here’s the procedure to follow to calculate the weighted mean:
Bạn có thể làm điều đó bằng cách tính trung bình gia trọng. Đây là một trung bình cộng mà trong đó mỗi giá trị có trọng số tùy theo mức quan trọng của nó trong một tập hợp dữ liệu. Đây là những bước để tính mức trung bình gia trọng:

1. For each value, multiply the value by its weight.
   Với mỗi giá trị, nhân giá trị với trọng số của nó.
   
   
2. Sum the results from step 1.
   Tính tổng của các kết quả ở bước 1.
   
   
3. Sum the weights.
   Tính tổng các trọng số.
   
   
4. Divide the sum from step 2 by the sum from step 3.
   Chia tổng ở bước 2 cho tổng ở bước 3

Let’s make this more concrete by tying this into our database of product defects. Suppose you want to know the average percentage of product defects (the values in column F). Simply applying the AVERAGE() function to the range F3:F22 doesn’t give an accurate answer because the number of units produced by each division is different (the maximum is 1,625 in division C, and the minimum is 690 in division R). To get an accurate result, you must give more weight to those divisions that produced more units. In other words, you need to calculate the weighted mean for the percentage of defective products.
Hãy làm cho điều này cụ thể hơn bằng cách đưa nó vào trong cơ sở dữ liệu của các sản phẩm lỗi của chúng ta. Giả sử bạn muốn biết số phần trăm trung bình các sản phẩm bị lỗi (những giá trị trong cột F). Việc đơn giản áp dụng hàm AVERAGE() vào dãy F3:F22 không đem lại một kết quả chính xác bởi vì số sản phẩm mà mỗi đơn vị sản xuất được thì khác nhau (bộ phận C làm ra nhiều nhất, 1,625 sản phẩm; còn bộ phận R làm được ít nhất, 690 sản phẩm). Để có được kết quả chính xác, bạn phải tăng thêm trọng số (gia trọng) cho những bộ phận làm được nhiều sản phẩm hơn. Nói cách khác, bạn cẩn phải tính mức trung bình gia trọng cho số phần trăm các sản phẩm lỗi.

Figure 12.2 - Statistics.xlsx​

In this case, the weights are the units produced by each division, so the weighted mean is calculated as follows:
Trong trường hợp này, trọng số là số sản phẩm của mỗi bộ phận làm được, do đó trung bình gia trọng được tính như sau:

1. Multiply the percentage defective values by the units. (The sharp-eyed reader will note that this just gives the number of defects. I’ll ignore this for now for illustration purposes.)
   Nhân giá trị phần trăm sản phẩm bị lỗi với số sản phẩm. (Nếu bạn tinh mắt bạn sẽ thấy điều này cho ra con số các sản phẩm lỗi. Tôi sẽ bỏ qua điều này, bởi vì đây chỉ là minh họa).
   
   
2. Sum the results from step 1.
   Tính tổng các kết quả ở bước 1.
   
   
3. Sum the units.
   Tính tổng các sản phẩm.
   
   
4. Divide the sum from step 2 by the sum from step 3.
   Lấy tổng số ở bước 2 chia cho tổng số ở bước 3.

You can combine all of these steps into the following array formula, as shown in Figure 12.2:
Bạn có thể kết hợp các bước trên trong công thức mảng sau đây, như minh họa ở hình 12.2:

{=SUM(F3:F22 * E3:E22) / SUM(E3:E22))}​

 

[BNTT]

Chapter 12 - WORKING WITH STATISTICAL FUNCTIONS


12.4. Calculating Extreme Values
Tính các cực trị

The average calculations tell you things about the “middle” of the data, but it can also be useful to know something about the “edges” of the data. For example, what’s the biggest value and what’s the smallest? The next two sections take you through the worksheet functions that return the extreme values of a sample or population.
Các phép tính trung bình cho bạn biết về "điểm giữa" của dữ liệu, nhưng cũng thật hữu ích nếu biết được điều gì đó về "biên" của dữ liệu, ví dụ, giá trị lớn nhất và giá trị nhỏ nhất là gì? Hai phần tiếp theo đây trình bày cho bạn những hàm trả về các cực trị của một mẫu hoặc một tập hợp.​

12.4.1. The MAX and MIN Function — Hàm MAX và Hàm MIN

If you want to know the largest value in a data set, use the MAX() function:
Khi bạn muốn biết giá trị lớn nhất của một tập hợp dữ liệu, bạn cùng hàm MAX():

MAX (number1 [, number2,...])

For example, to calculate the maximum value in the defects database, you use the following formula:
Ví dụ, để tính giá trị lớn nhất trong cơ sở dữ liệu các sản phẩm lỗi, bạn dùng công thức sau:

=MAX(D322)


To get the smallest value in a data set, use the MIN() function:
Để có giá trị nhỏ nhất trong một tập hợp dữ liệu, dùng hàm MIN():

MIN (number1 [, number2,...])

For example, to calculate the minimum value in the defects database, you use the following formula:
Ví dụ, để tính giá trị nhỏ nhất trong cơ sở dữ liệu các sản phẩm lỗi, bạn dùng công thức sau:

=MIN(D322)

TIP:

If you need just a quick glance at the maximum or minimum value, select the range, right-click the status bar, and then click the Maximum or Minimum value.
Nếu bạn chỉ muốn xem lướt qua giá trị cực đại hoặc cực tiểu, bạn chọn dãy và nhấn nút phải chuột vào Status bar, và chọn giá trị Maximum hoặc Minimum.​

NOTE:

If you need to determine the maximum or minimum over a range or array that includes text values or logical values, use the MAXA() or MINA() functions instead. These functions ignore text values and treat logical values as either 1 (for TRUE) or 0 (for FALSE).
Nếu bạn muốn những giá trị cực đại hay cực tiểu trong một dãy hoặc một mảng bao gồm cả những giá trị text và các giá trị logic, bạn dùng hàm MAXA() hoặc MINA(), những hàm này sẽ bỏ qua các giá trị text, và xem những giá trị logic hoặc là 1 (TRUE) hoặc là 0 (FALSE). ​

 

[BNTT]

12.4. Calculating Extreme Values


12.4.2. The LARGE and SMALL Function — Hàm LARGE và Hàm SMALL

Instead of knowing just the largest value, you might need to know the kth largest value, where k is some integer. You can calculate this using Excel’s LARGE() function:
Thay vì chỉ biết có giá trị lớn nhất, bạn cũng nên biết giá trị lớn thứ k, với k là một số nguyên nào đó. Bạn có thể tính giá trị này với hàm LARGE() của Excel:

LARGE (array, k)

For example, the following formula returns 15, the second-largest defects value in the product defects database:
Ví dụ, công thức sau đây trả về 15, giá trị lớn thứ hai của các sản phẩm lỗi trong cơ sở dữ liệu các sản phẩm lỗi:

=LARGE(D322, 2)


Similarly, instead of knowing just the smallest value, you might need to know the kth smallest value, where k is some integer. You can determine this value using the SMALL() function:
Tương tự, thay vì chỉ biết giá trị nhỏ nhất, bạn cũng cần biết được giá trị nhỏ thứ k, với k là một số nguyên nào đó. Bạn có thể xác định giá trị này bằng hàm SMALL():

SMALL (array, k)

For example, the following formula returns 4, the third-smallest defects value in the product defects database (see Figure 12.3):
Ví dụ, công thức sau đây trả về 4, giá trị nhỏ thứ ba của các sản phẩm lỗi trong cơ sở dữ liệu các sản phẩm lỗi (xem hình 12.3):

=SMALL(D322, 3)

Figure 12.3 - Statistics.xlsx​

 

[BNTT]

12.4.2. The LARGE and SMALL Function

• Performing Calculations on the Top k Values
  Thực hiện các phép tính với k giá trị cao nhất

Sometimes, you might need to sum only the top 3 values in a data set, or take the average of the top 10 values. You can do this by combining the LARGE() function and the appropriate arithmetic function (such as SUM()) in an array formula. Here’s the general formula:
Đôi khi, bạn cần phải tính tổng của 3 giá trị cao nhất trong một tập hợp dữ liệu, hoặc lấy trung bình của 10 giá trị cao nhất. Bạn có thể làm điều này bằng cách kết hợp hàm LARGE() và một hàm số học thích hợp (như là SUM()) trong một công thức mảng. Đây là công thức chung:

{=FUNCTION(LARGE(range, {1,2,3,...,k}))}

Here, FUNCTION() is the arithmetic function, range is the array or range containing the data, and k is the number of values you want to work with. In other words, LARGE() applies the top k values from range to the FUNCTION().
Ở đây, FUNCTION là một hàm số học, range là một mảng hay là một dãy chứa dữ liệu, và k là số các giá trị mà bạn muốn làm việc với chúng. Nói cách khác, LARGE() áp dụng k giá trị cao nhất của range vào trong FUNCTION.

For example, suppose that you want to find the mean of the top five values in the defects database. Here’s an array formula that does this:
Ví dụ, giả sử bạn muốn tìm trung bình của 5 giá trị cao nhất trong cơ sở dữ liệu các sản phẩm lỗi, đây là công thức mảng để làm điều này:

{=AVERAGE(LARGE(D322,{1,2,3,4,5}))}​

• Performing Calculations on the Bottom k Values
  Thực hiện các phép tính với k giá trị thấp nhất

You can probably figure out that performing calculations on the smallest k values is similar. In fact, the only difference is that you substitute the SMALL() function for LARGE():
Tương tự, bạn có thể thực hiện các phép tính với k giá trị thấp nhất trong một tập hợp dữ liệu. Thật sự thì chỉ có mỗi một điều khác, là bạn thay hàm LARGE() bằng hàm SMALL():

{=FUNCTION(SMALL(range, {1,2,3,...,k}))}

For example, the following array formula sums the smallest three defect values in the defects database:
Ví dụ, công thức mảng sau đây tính tổng của 3 giá trị thấp nhất trong cơ sở dữ liệu các sản phẩm lỗi:

{=SUM(SMALL(D322,{1,2,3}))}​

 

[BNTT]

Chapter 12 - WORKING WITH STATISTICAL FUNCTIONS


12.5. Calculating Measures of Variation
Tính độ biến thiên

Descriptive statistics such as the mean, median, and mode fall under what statisticians call measures of central tendency (or sometimes measures of location). These numbers are designed to give you some idea of what constitutes a “typical” value in the data set.
Những thống kê mô tả như tính trung bình cộng (mean), trung bình vị (median), và tính số lần xuất hiện (mode) là những điều mà các nhà thống kê gọi là thước đo của xu hướng trung tâm (hoặc đôi khi được gọi là thước đo của vị trí). Những con số này được thiết kế để đem lại cho bạn một số ý niệm về những cái cấu thành một giá trị "tiêu biểu" trong một tập hợp dữ liệu.

This is in contrast to the so-called measures of variation (or sometimes measures of dispersion), which are designed to give you some idea of how the values in the data set vary with respect to one another. For example, a data set in which all the values are the same would have no variability; in contrast, a data set with wildly different values would have high variability. Just what is meant by “wildly different” is what the statistical techniques in this section are designed to help you calculate.
Tương phản với thước đo của xu hướng trung tâm (measures of central tendency) là độ biến thiên (hay còn gọi là độ phân tán), được thiết kế để cho bạn biết một số ý niệm về những giá trị trong tập hợp dữ liệu khác nhau như thế nào. Ví dụ, một tập hợp dữ liệu những giá trị hoàn toàn giống nhau sẽ không có sự biến thiên, trái lại, một tập hợp những dữ liệu cực kỳ khác nhau thì sẽ có độ biến thiên cao. Để hiểu "cực kỳ khác nhau" là gì, phần này sẽ trình bày những kỹ thuật thống kê được thiết kế để giúp bạn tính toán. ​

12.5.1. Calculating the Range
Tính độ biến thiên trong dãy

The simplest measure of variability is the range, which is defined as the difference between a data set’s maximum and minimum values. Excel doesn’t have a function that calculates the range directly. Instead, you first apply the MAX() and MIN() functions to the data set. Then, when you have these extreme values, you calculate the range by subtracting the minimum from the maximum.
Độ biến thiên đơn giản nhất là độ biến thiên trong dãy, được định nghĩa là sự khác nhau giữa các giá trị lớn nhất và nhỏ nhất trong một tập hợp. Excel không có hàm để tính độ biến thiên trong dãy cách trực tiếp, nhưng thay vào đó, đầu tiên bạn áp dụng hàm MAX() và MIN() vào tập hợp dữ liệu này, rồi khi bạn đã có những cực trị, bạn tính độ biến thiên trong dãy bằng cách lấy giá trị cực đại trừ đi giá trị cực tiểu.

For example, here’s a formula that calculates the range for the defects database:
Ví dụ, đây là công thức để tính độ biến thiên trong dãy cho những sản phẩm lỗi:

=MAX(D322) - MIN(D322)

Speaking generally, the range is a useful measure of variation only for small sample sizes. The larger the sample is, the more likely it becomes that an extreme maximum or minimum will occur, and the range will be skewed accordingly.
Nói chung, độ biến thiên trong dãy chỉ hữu dụng với những mẫu có kích thước nhỏ. Một mẫu càng lớn thì khả năng chênh lệch giữa một giá trị cực đại và một giá trị cực tiểu càng lớn, và dãy cũng sẽ bị lệch nhiều hơn.
​

 

[BNTT]

12.5. Calculating Measures of Variation


12.5.2. Calculating the Variance with the VAR() Function
Tính phương sai bằng hàm VAR

When computing the variability of a set of values, one straightforward approach is to calculate how much each value deviates from the mean. You could then add those differences and divide by the number of values in the sample to get what might be called the average difference. The problem, however, is that, by definition of the arithmetic mean, adding the differences (some of which are positive and some of which are negative) gives the result 0. To solve this problem, you need to add the absolute values of the deviations and then divide by the sample size. This is what statisticians call the average deviation.
Khi tính sự biến thiên của một tập hợp giá trị, có một phương pháp đơn giản là tính mỗi giá trị lệch khỏi giá trị trung bình (mean) bao nhiêu, sau đó, bạn cộng hết mấy độ lệch này rồi chia cho số giá trị có trong mẫu, và bạn sẽ có cái gọi là độ lệch trung bình. Tuy nhiên, vấn đề là theo định nghĩa của trung bình cộng thì việc cộng các độ lệch (một số cái là dương và một số cái là âm) sẽ cho ra kết quả là 0. Để giải quyết vấn đề này, bạn cần cộng các giá trị tuyệt đối của các độ lệch và sau đó mới chia cho số giá trị trong mẫu. Đây là cái mà các nhà thống kê gọi là độ lệch trung bình.

Unfortunately, this simple state of affairs is still problematic because (for highly technical reasons) mathematicians tend to shudder at equations that require absolute values. To get around this, they instead use the square of each deviation from the mean, which always results in a positive number. They sum these squares and divide by the number of values (I’m simplifying things considerably here), and the result is the called the variance. This is a common measure of variation, although interpreting it is hard because the result isn’t in the units of the sample: It’s in those units squared. What does it mean to speak of “defects squared,” for example? This doesn’t matter that much for our purposes because, as you’ll see in the next section, the variance is used chiefly to get to the standard deviation.
Nhưng không may, công việc đơn giản này vẫn gây hoài nghi (vì những lý do mang tính kỹ thuật cao), các nhà toán học hay rùng mình trước những chương trình đòi hỏi những giá trị tuyệt đối. Để giải quyết điều này, họ sử dụng bình phương của mỗi độ lệch từ giá trị trung bình, việc này luôn tạo ra một giá trị dương, rồi họ tính tổng các bình phương này rồi chia cho số giá trị, và kết quả được gọi là phương sai. Đây là một độ biến thiên chung, mặc dù hiểu được nó thì không dễ bởi vì kết quả không nằm trong các đơn vị của mẫu, mà nó nằm trong các đơn vị được tính bình phương. Ví dụ, "các sản phẩm lỗi" được tính bình phương là gì? Điều này không quan trọng cho lắm đối với mục đích của chúng ta, bởi vì như bạn sẽ thấy trong phần tiếp theo đây, phương sai được sử dụng chủ yếu là để có được độ lệch chuẩn.

In any case, variance is usually a standard part of a descriptive statistics package, so that’s why I’m covering it. Excel calculates the variance using the VARP() and VAR() functions:
Trong bất kỳ trường hợp nào, phương sai thường là một chuẩn của một thống kê mô tả, do đó đây là lý do tại sao nó được đề cập đến. Excel tính phương sai bằng cách sử dụng các hàm VARP() và VAR():

VARP (number1 [, number2,...])

VAR (number1 [, number2,...])

You use the VARP() function if your data set represents the entire population (as it does, for example, in the product defects case); you use the VAR() function if your data set represents only a sample from the entire population.
Bạn dùng hàm VARP() nếu tập hợp dữ liệu của bạn tượng trưng cho toàn bộ tập hợp (ví dụ như trong trường hợp các sản phẩm lỗi); bạn dùng hàm VAR() nếu tập hợp dữ liệu chỉ tượng trưng cho một mẫu từ toàn bộ tập hợp.

For example, to calculate the variance of the values in the defects database, you use the following formula:
Ví dụ, để tính phương sai của các giá trị trong cơ sở dữ liệu các sản phẩm lỗi, bạn dùng công thức sau đây:

=VARP(D322)

NOTE:
If you need to determine the variance over a range or array that includes text values or logical values, use the VARPA() or VARA() functions instead.These functions ignore text values and treat logical values as either 1 (for TRUE) or 0 (for FALSE).
Nếu bạn muốn tính phương sai trong một dãy hoặc một mảng bao gồm cả những giá trị text và các giá trị logic, bạn dùng hàm VARPA() hoặc VARA(), những hàm này sẽ bỏ qua các giá trị text, và xem những giá trị logic hoặc là 1 (TRUE) hoặc là 0 (FALSE).

 

[BNTT]

12.5.3. Calculating the Standard Deviation with the STDEVP and STDEV Functions
Tính độ lệch chuẩn bằng hàm STDEVP và hàm STDEV

As I mentioned in the previous section, in real-world scenarios, the variance is really used only as an intermediate step for calculating the most important of the measures of variation, the standard deviation. This measure tells you how much the values in the data set vary with respect to the average (the arithmetic mean). What exactly this means won’t become clear until you learn about frequency distributions in the next section. For now, however, it’s enough to know that a low standard deviation means that the data values are clustered near the mean, and a high standard deviation means the values are spread out from the mean.
Như tôi đã nói trong bài trước, trong thực tế, phương sai chỉ được dùng như một bước trung gian để tính cái chính yếu của độ lệch, hay còn gọi là độ lệch chuẩn. Độ lệch chuẩn cho bạn biết giá trị trong tập hợp dữ liệu lệch bao nhiêu so với giá trị trung bình. Định nghĩa này sẽ rõ ràng hơn khi bạn học về sự phân bổ tần số trong bài sau. Tuy nhiên, nó cũng đủ để hiểu rằng một độ lệch chuẩn thấp nghĩa là các giá trị của dữ liệu thì gần với giá trị trung bình, còn độ lệch chuẩn cao có nghĩa là các giá trị của dữ liệu thì cách xa giá trị trung bình.

The standard deviation is defined as the square root of the variance. This is good because it means that the resulting units will be the same as those used by the data. For example, the variance of the product defects is expressed in the meaningless defects squared units, but the standard deviation is expressed in defects.
Độ lệch chuẩn được định nghĩa là căn bậc hai của phương sai. Điều này thì tốt bởi vì nó có nghĩa là những đơn vị của kết quả sẽ giống như các đơn vị được dùng trong dữ liệu. Ví dụ, phương sai của các sản phẩm lỗi được biểu thị như "bình phương sản phẩm lỗi", nhưng độ lệch chuẩn thì được biểu thị bằng chính các sản phẩm lỗi.

You could calculate the standard deviation by taking the square root of the VAR() result, but Excel offers a more direct route:
Bạn có thể tính độ lệch chuẩn bằng cách lấy căn bậc hai của kết quả của hàm VAR(), nhưng Excel cho bạn một cách làm trực tiếp hơn:

STDEVP (number1 [, number2,...])

STDEV (number1 [, number2,...])

You use the STDEVP() function if your data set represents the entire population (as in the product defects case); you use the STDEV() function if your data set represents only a sample from the entire population.
Bạn dùng hàm STDEVP() nếu tập hợp dữ liệu của bạn tượng trưng cho toàn bộ tập hợp (ví dụ như trong trường hợp các sản phẩm lỗi); bạn dùng hàm STDEV() nếu tập hợp dữ liệu chỉ tượng trưng cho một mẫu từ toàn bộ tập hợp.

For example, to calculate the standard deviation of the values in the defects database, you use the following formula (see Figure 12.4):
Ví dụ, để tính độ lệch chuẩn của các giá trị trong cơ sở dữ liệu các sản phẩm lỗi, bạn dùng công thức sau đây (xem hình 12.4):

=STDEVP(D322)

Figure 12.4 - Statistics.xlsx​

NOTE:
If you need to determine the standard deviation over a range or array that includes text values or logical values, use the STDEVPA() or STDEVA() functions instead.These functions ignore text values and treat logical values as either 1 (for TRUE) or 0 (for FALSE).
Nếu bạn muốn tính phương sai trong một dãy hoặc một mảng bao gồm cả những giá trị text và các giá trị logic, bạn dùng hàm STDEVPA() hoặc STDEVA(), những hàm này sẽ bỏ qua các giá trị text, và xem những giá trị logic hoặc là 1 (TRUE) hoặc là 0 (FALSE).

 

[BNTT]

Chapter 12 - WORKING WITH STATISTICAL FUNCTIONS


12.6. Working with Frequency Distributions
Làm việc với các sự phân bổ tần số

A frequency distribution is a data table that groups data values into bins — ranges of values — and shows how many values fall into each bin. The size of each bin is called the bin interval. How many bins should you use? The answer usually depends on the data. If you want to calculate the frequency distribution for a set of student grades, for example, you’d probably set up six bins: 0–49, 50–59, 60–69, 70–79, 80–89, and 90+. For poll results, you might group the data by age into four bins: 18–34, 35–49, 50–64, and 65+.
Một sự phân bổ tần số là một bảng nhóm các dữ liệu theo từng dãy giá trị (bin) và trình bày số giá trị trong mỗi dãy đó. Kích thước của mỗi dãy giá trị (bin) được gọi là khoảng giá trị. Bạn nên sử dụng bao nhiêu khoảng giá trị? Câu trả lời thường phụ thuộc vào dữ liệu. Ví dụ, nếu bạn muốn tính sự phân bổ tần số cho một tập hợp điểm thi của sinh viên, bạn có thể thiết lập 6 khoảng giá trị: 0-49 (điểm, tối đa là 100), 50–59, 60–69, 70–79, 80–89, và trên 90. Đối với các bảng thăm dò, bạn có thể kết nhóm dữ liệu theo độ tuổi vào 4 khoảng giá trị: 18–34, 35–49, 50–64, và trên 65 tuổi.

If your data has no obvious bin intervals, you can use the following rule:
Nếu dữ liệu của bạn không có các khoảng giá trị rõ ràng, bạn có thể dùng quy tắc sau đây:

If n is the number of values in the data set, enclose n between two successive powers of 2, and take the higher exponent to be the number of bins.
Nếu n là số giá trị trong tập hợp dữ liệu, đặt n vào giữa hai lũy thừa liên tục của 2, và lấy số mũ cao nhất để làm số khoảng giá trị.​

For example, if n is 100, you would use 7 bins because 100 lies between 26 (64) and 27 (128). For the product defects, n is 20, so the number of bins should be 5 because 20 falls between 24 (16) and 25 (32).
Ví dụ, nếu n là 100, bạn sẽ dùng 7 khoảng giá trị, bởi vì 100 nằm giữa 2^6 (=64) và 2^7 (=128). Với ví dụ về các sản phẩm lỗi, n là 20, nên số khoảng giá trị được dùng là 5, vì 20 nằm giữa 2^4 (=16) và 2^5 (=32).

NOTE:
Here’s a worksheet formula that implements the bin-calculation rule:
Đây là một công thức để tính ra số khoảng giá trị:

=CEILING(LOG(COUNT(input_range), 2), 1)

(input_range là dãy chứa các giá trị)

 

[BNTT]

12.6. Working with Frequency Distributions


12.6.1. The FREQUENCY Function — Hàm FREQUENCY

To help you construct a frequency distribution, Excel offers the FREQUENCY() function:
Để giúp bạn xây dựng một sự phân bổ tần số, Excel có hàm FREQUENCY():

FREQUENCY (data_array, bins_array)

Here are some things you need to know about this function:
Đây là một số điều bạn cần biết về hàm này:

• For the bins_array, you enter only the upper limit of each bin. If the last bin is openended (such as 16+), you don’t include it in the bins_array. For example, here’s the bins_array for the product defects frequency distribution shown earlier: {3, 7, 11, 15}.
  Đối với đối số bins_array, bạn chỉ nhập giới hạn trên của mỗi khoảng giá trị. Nếu khoảng giá trị cuối cùng là mở (ví dụ, trên 16), bạn đừng đưa nó vào trong danh sách bins_array. Ví dụ, đây là bins_array cho sự phân bổ tần số của ví dụ về các sản phẩm lỗi mà chúng ta đã xem xét từ đầu đến giờ: {3, 7, 11, 15}.
  

  CAUTION:
  Make sure that you enter your bin values in ascending order.
  Hãy chắc chắn rằng bạn luôn nhập những khoảng giá trị của mình (trong đối số bin_array) theo thứ tự tăng dần.

  
  
• The FREQUENCY() function returns an array (the number of values that fall within each bin) that is one greater than the number of elements in bins_array. For example, if the bins_array contains four elements, FREQUENCY() returns five elements (the extra element is the number of values that fall in the open-ended bin).
  Hàm FREQUENCY() trả về một mảng (số các giá trị trong mỗi khoảng giá trị) lớn hơn số các phần tử trong bins_array 1 đơn vị. Ví dụ, nếu bins_array chứa 4 phần tử, FREQUENCY() sẽ trả về 5 phần tử (phần tử cuối cùng là số giá trị cho khoảng giá trị mở, thường là khoảng giá trị cuối cùng trong danh sách các khoảng giá trị của bạn).
  
  
• Because FREQUENCY() returns an array, you must enter it as an array formula. To do this, select the range in which you want the function results to appear (again, make this range one cell bigger than the bins_array range), type in the formula, and press Ctrl+Shift+Enter.
  Bởi vì FREQUENCY() trả về một mảng, nên bạn phải nhập nó ở dạng công thức mảng. Để làm điều này, bạn chọn dãy mà bạn muốn chưa kết quả của hàm (nhắc lại, dãy này phải nhiều hơn dãy trong bins_array 1 ô), nhập công thức, rồi nhấn Ctrl+Shift+Enter.

Figure 12.5 shows the product defects database with a frequency distribution added. The bins_array is the range K4:K7, and the FREQUENCY() results appear in the range L5:L8, with the following formula entered as an array in that range:
Hình 12.5 minh họa cơ sở dữ liệu các sản phẩm lỗi với một bảng phân bổ tần số được thêm vào (phía bên phải). Bins_array là dãy K4:K7, và kết quả của FREQUENCY() xuất hiện trong dãy L5:L8, với công thức mảng sau đây được nhập vào trong dãy đó:

{=FREQUENCY(D322, K4:K7)}

Figure 12.5 - Statistics.xlsx

Chú thích: Nhìn vào bảng đó, ta biết rằng từ số sản phẩm lỗi được phân loại theo số lượng như sau: từ 0-3 (sản phẩm lỗi trên tổng số sản phẩm làm ra của một đơn vị) có 2 cái, từ 4-7 có 5 cái, từ 8-11 có 8 cái, từ 12-15 có 4 cái và trên 15 có 1 cái.​

 

[BNTT]

12.6. Working with Frequency Distributions


12.6.2. Understanding the Normal Distribution and the NORMDIST Function
Tìm hiểu Phân bố chuẩn và hàm NORMDIST

The next few sections require some knowledge of perhaps the most famous object in the statistical world: the normal distribution (it’s also called the normal frequency curve). This refers to a set of values that are symmetrically clustered around a central mean, with the frequencies of each value highest near the mean and falling off as you move farther from the mean (either to the left or to the right).
Vài bài tiếp theo đây đòi hỏi một số kiến thức về đối tượng có lẽ là nổi tiếng nhất trong giới thống kê: Phân bố chuẩn (hay còn gọi là Đường cong tần số chuẩn). Phân bố chuẩn là một tập hợp giá trị được gộp lại xung quanh một giá trị trung bình một cách đối xứng, với các tần số của mỗi giá trị cao nhất thì gần với giá trị trung bình, và giảm dần khi đi xa ra khỏi giá trị trung bình (về phía bên trái hoặc về phía bên phải).

Figure 12.6 shows a chart that displays a typical normal distribution. In fact, this particular example is called the standard normal distribution, and it’s defined as having mean 0 and standard deviation 1. The distinctive bell shape of this istribution is why it’s often called the bell curve.
Hình 12.6 minh họa một biểu đồ hiển thị một phân bố chuẩn điển hình. Trong thực tế, phân bố này được gọi là một phân bố chuẩn chuẩn tắc (standard normal distribution), là một phân bố có giá trị trung bình là 0 và độ lệch chuẩn là 1. Vì nó có đồ thị dạng cái chuông, nên nó còn được gọi là đường cong chuông.

Figure 12.6 - Statistics.xlsx​

To generate this normal distribution, I used Excel’s NORMDIST() function, which returns the probability that a given value exists within a population:
Để tạo một phân bố chuẩn, tôi dùng hàm NORMDIST() của Excel, là hàm trả về xác suát của một giá trị nào đó hiện hữu trong một tập hợp:

NORMDIST (x, mean, standard_dev, cumulative)

For example, consider the following example that computes the standard normal distribution — mean 0 and standard deviation 1 — for the value 0:
Ví dụ sau đây tính phân bố chuẩn chuẩn tắc — phân bố có giá trị trung bình là 0 và độ lệch chuẩn là 1 — của giá trị 0:

=NORMDIST(0, 0, 1, TRUE)

With the cumulative argument set to TRUE, this formula returns 0.5, which makes intuitive sense because, in this distribution, half of the values fall below 0. In other words, the probabilities of all the values below 0 add up to 0.5.
Với số cumulative được đặt là TRUE, công thức trên trả về 0.5, là một giá trị chính xác mang tính trực giác, vì trong phân bố này có một nửa giá trị nằm dưới 0. Nói cách khác, xác suất của tất cả những giá trị nằm dưới 0 cộng lại thành 0.5.

Now consider the same function, but this time with the cumulative argument set to FALSE:
Bây giờ, cũng công thức đó, nhưng lần này đối số cumulative được đặt là FALSE:

=NORMDIST(0, 0, 1, FALSE)

This time, the result is 0.39894228. In other words, in this distribution, about 3.99% of all the values in the population are 0.
Lần này, kết quả là 0.39894228, hay nói cách khác, trong phân bố này, có khoảng 3.99% số giá trị trong tập hợp là 0.​

 

[BNTT]

12.6. Working with Frequency Distributions


12.6.3. The Shape of the Curve I: The SKEW Function
Hình dạng của đường cong I: Hàm SKEW

How do you know if your frequency distribution is at or close to a normal distribution? In other words, does the shape of your data’s frequency curve mirror that of the normal distribution’s bell curve?
Làm thế nào để bạn biết sự phân bố tần số nằm ngay tại phân bố chuẩn hay nằm gần phân bố chuẩn? Hay nói cách khác: Hình dạng đường cong tần số của dữ liệu của bạn có đối xứng với hình dạng cái chuông của đường cong của phân bố chuẩn không?

One way to find out is to consider how the values cluster around the mean. For a normal distribution, the values cluster symmetrically about the mean. Other distributions are asymmetric in one of two ways:
Một trong những cách để biết là xem các giá trị gộp lại xung quanh giá trị trung bình như thế nào. Đối với một phân bố chuẩn, các giá trị gộp lại cách đối xứng xung quanh giá trị trung bình. Những phân bố khác thì không cân đối theo một trong hai cách:

• Negatively skewed — The values are bunched above the mean and drop off quickly in a “tail” below the mean.
  Lệch âm — Các giá trị tụ lại trên giá trị trung bình và rơi xuống nhanh trong một "đuôi" ở dưới giá trị trung bình.
  
  
• Positively skewed — The values are bunched below the mean and drop off quickly in a “tail” above the mean.
  Lệch dương — Các giá trị tụ lại dưới giá trị trung bình và rơi xuống nhanh trong một "đuôi" ở trên giá trị trung bình.

Figure 12.7 shows two charts that display examples of negative and positive skewness.
Hình 12.7 minh họa hai biểu đồ hiển thị hiển thị những ví dụ về lệch âm và lệch dương.

Figure 12.7 - Statistics.xlsx​

In Excel, you calculate the skewness of a data set by using the SKEW() function:
Trong Excel, bạn tính độ lệch của một tập hợp dữ liệu bằng cách sử dụng hàm SKEW():

SKEW (number1 [, number2,...])

For example, the following formula returns the skewness of the product defects:
Ví dụ, công thức sau đây trả về độ lệch của tập hợp những sản phẩm lỗi:

=SKEW(D322)

The closer the SKEW() result is to 0, the more symmetric the distribution is, so the more like the normal distribution it is.
Kết quả của SKEW() càng gần 0 thì phân bố càng đối xứng, do dó nó càng giống phân bố chuẩn.​

 

[BNTT]

12.6. Working with Frequency Distributions


12.6.4. The Shape of the Curve II: The KURT Function
Hình dạng của đường cong II: Hàm KURT

Another way to find out how close your frequency distribution is to a normal distribution is to consider the flatness of the curve:
Một cách khác để biết sự phân bố tần số có gần với phân bố chuẩn hay không là xem xét độ phẳng của đường cong:

• Flat — The values are distributed evenly across all or most of the bins.
  Phẳng — Các giá trị được phân bố đều qua tất cả hoặc hâu hết các khoảng giá trị (bin).
  
  
• Peaked — The values are clustered around a narrow range of values.
  Nhọn — Các giá trị tụ lại xung quanh một dãy các giá trị hẹp.

Statisticians call the flatness of the frequency curve the kurtosis: a flat curve has a negative kurtosis, and a peaked curve has a positive kurtosis. The further these values are from 0, the less the frequency is like the normal distribution. Figure 12.8 shows two charts that display examples of negative and positive kurtosis.
Các nhà thống kê gọi độ phẳng của đường cong tần số là độ nhọn (kurtosis): một đường cong phẳng thì có một độ nhọn âm, và một đường cong nhọn thì có một độ nhọn dương. Những giá trị càng xa số 0, thì tần số giống phân bố chuẩn càng ít. Hình 12.8 minh họa hai biểu đồ hiển thị hai ví dụ về độ nhọn âm và độ nhọn dương.

Figure 12.8 - Statistics.xlsx​

In Excel, you calculate the kurtosis of a data set by using the KURT() function:
Trong Excel, bạn tính độ nhọn của một tập hợp dữ liệu bằng cách sử dụng hàm KURT():

KURT (number1 [, number2,...])

For example, the following formula returns the kurtosis of the product defects:
Ví dụ, công thức sau đây trả về độ nhọn của tập hợp những sản phẩm lỗi:

=KURT(D322)

Figure 12.9 shows the final product defects worksheet, including values for the skewness and kurtosis.
Hình 12.9 minh họa bảng tính sau cùng của bảng tính cơ sở dữ liệu các sản phẩm lỗi (mà chúng ta đã dùng làm ví dụ từ những bài trước tới giờ), bao gồm những giá trị về độ lệch và độ nhọn.

Figure 12.9 - Statistics.xlsx​

 

[BNTT]

Chapter 12 - WORKING WITH STATISTICAL FUNCTIONS


12.7. Using the Analysis ToolPak Statistical Tools
Sử dụng các công cụ thống kê của Analysis ToolPak

When you load the Analysis ToolPak, the add-in inserts a new Data Analysis button in the Ribbon’s Data tab. Click this button to display the Data Analysis dialog box shown in Figure 12.10. This dialog box gives you access to 19 new statistical tools that handle everything from an analysis of variance (anova) to a z-test.
Khi bạn đã cài Analysis ToolPak, add-in này hiển thị một nút Data Analysis trong tab Data của Ribbon. Khi bạn nhấn nút này, hộp thoại Data Analysis sẽ hiển thị như minh họa ở hình 12.10. Hộp thoại này cho bạn truy cập 19 công cụ thống kê mới, xử lý mọi thứ, từ việc phân tích phuong sai (anova) cho đến một phép thử z.

Here’s a summary of what each statistical tool can do for your data:
Sau đây là phần tóm tắt mỗi công cụ thống kê có thể thực hiện cho dữ liệu của bạn:

• Anova: Single Factor — A simple (that is, single-factor) analysis of variance. An analysis of variance (anova) tests the hypothesis that the means from several samples are equal.
  Một phân tích phương sai đơn giản (một hệ số). Phân tích phương sai (anova) kiểm tra giả thuyết các giá trị trung bình lấy từ nhiều mẫu thì bằng nhau.
  
  
• Anova: Two-Factor with Replication — An extension of the single-factor anova to include more than one sample for each group of data.
  Một mở rộng của phân tích phương sai đơn giản (cho hai hệ số), có bao gồm nhiều mẫu cho mỗi nhóm dữ liệu.
  
  
• Anova: Two-Factor Without Replication — A two-factor anova that doesn’t include more than one sampling per group.
  Một phân tích phương sai cho hai hệ số nhưng không bao gồm nhiều hơn một mẫu cho mỗi nhóm dữ liệu.
  
  
• Correlation — Returns the correlation coefficient: a measure of the relationship between two sets of data. This is also available via the following worksheet function:
  Trả về hệ số tương quan: số đo mối quan hệ giữa hai tập hợp dữ liệu. Bạn cũng có thể tính hệ số tương quan bằng hàm có sẵn sau đây:
  
  CORREL (array1, array2)
  
  
• Covariance — Returns the average of the products of deviations for each data pointpair. Covariance is a measure of the relationship between two sets of data. This is also available via the following worksheet function:
  Trả về hiệp phương sai, là trung bình của tích số các độ lệch của các cặp dữ liệu. Hiệp phương sai là một số đo mối quan hệ giữa hai tập hợp dữ liệu, và nó cũng có sẵn (trong Excel) thông qua hàm sau đây:
  
  COVAR (array1, array2)
  
  
• Descriptive Statistics — Generates a report showing various statistics (such as median, mode, and standard deviation) for a set of data.
  Tạo một báo cáo trình bày những số liệu thống kê mô tả (chẳng hạn như giá trị trung bình (median), tần số xuất hiện (mode), và độ lệch chuẩn standard deviation)) cho một tập hợp dữ liệu.
  
  
• Exponential Smoothing — Returns a predicted value based on the forecast for the previous period, adjusted for the error in that period.
  Trả về một giá trị dự đoán dựa vào sự dự đoán của chu kỳ trước, sau điều chỉnh lỗi trong chu kỳ đó.
  
  
• F-Test Two-Sample for Variances — Performs a two-sample F-test to compare two population variances. This tool returns the one-tailed probability that the variances in the two sets are not significantly different. This is also available via the following worksheet function:
  Thực hiện một phép thử F cho hai mẫu để so sánh phương sai của hai tập hợp. Công cụ này trả về xác suất một phía, trong đó phương sai của hai tập hợp không đáng kể. Phép thử này cũng có sẵn (trong Excel) với hàm sau:
  
  FTEST (array1, array2)
  
  
• Fourier Analysis — Performs a Fast Fourier Transform. You use Fourier Analysis to solve problems in linear systems and to analyze periodic data.
  Thực hiện phép biển đổi nhanh Fourier. Bạn sử dụng Fourier Analysis để giải quyết các vấn đề trong những hệ thống tuyến tính và để phân tích chu kỳ dữ liệu.
  
  
• Histogram — Calculates individual and cumulative frequencies for a range of data and a set of data bins. The FREQUENCY() function, discussed earlier in this chapter, is a simplified version of the Histogram tool.
  Tính các tần số riêng lẻ và tích lũy cho một dãy dữ liệu và một tập hợp các khoản dữ liệu (lập biểu đồ phân phối xác suất). Hàm FREQUENCY(), đã được trình bày ở phần trước trong chương này, là một phiên bản đơn giản của công cụ Histogram.
  
  
• Moving Average — Smoothes a data series by averaging the series values over a specified number of preceding periods.
  Làm nhẵn một chuỗi dữ liệu bằng cách tính trung bình của chuỗi dữ liệu trên một số chu kỳ đã xác định.
  
  
• Random Number Generation — Fills a range with independent random numbers.
  Điền đầy một dãy với những con số ngẫu nhiên độc lập (không trùng nhau).
  
  
• Rank and Percentile — Creates a table containing the ordinal and percentage rank of each value in a set. These are also available via the following worksheet functions:
  Tạo một bảng xếp hạng theo thứ tự và phần trăm của mỗi giá trị trong một tập hợp. Những điều này cũng có thể thực hiện thông qua các hàm sau:
  
  RANK (number, ref [, order])
  
  PERCENTILE (array, k)
  
  
• Regression — Performs a linear regression analysis that fits a line through a set of values using the least squares method.
  Thực hiện một sự phân tích hồi quy tuyến tính theo các điểm của một đường thẳng đi qua một tập hợp giá trị bằng cách sử dụng phương pháp bình phương tối thiểu.
  
  
• Sampling — Creates a sample from a population by treating the input range as a population.
  Tạo một mẫu từ một tập hợp bằng cách xem dãy đầu vào là một tập hợp.
  
  
• t-Test: Paired Two-Sample for Means — Performs a paired two-sample student’s t-Test to determine whether a sample’s means are distinct. This is also available via the following worksheet function (set type equal to 1):
  Thực hiện một phép thử t-Test của phân phối Student với hai mẫu để quyết định các giá trị trung bình của một mẫu có riêng biệt hay không. Điều này cũng có thể thực hiện thông qua hàm có sẵn sau đây (đặt đối số type bằng 1):
  
  TTEST (array1, array2, tails, type)
  
  
• t-Test: Two-Sample Assuming Equal Variances — Performs a paired two-sample student’s t-Test, assuming that the variances of both data sets are equal. You can also use the TTEST() worksheet function with the type argument set to 2.
  Thực hiện một phép thử t-Test của phân phối Student với hai mẫu, giả sử rằng phương sai của hai tập hợp dữ liệu là bằng nhau. Bạn cũng có thể sử dụng hàm TTEST() với đối số type bằng 2.
  
  
• t-Test: Two-Sample Assuming Unequal Variances — Performs a paired two-sample student’s t-Test, assuming that the variances of both data sets are unequal. You can also use the TTEST() worksheet function with the type argument set to 3.
  Thực hiện một phép thử t-Test của phân phối Student với hai mẫu, giả sử rằng phương sai của hai tập hợp dữ liệu là khác nhau. Bạn cũng có thể sử dụng hàm TTEST() với đối số type bằng 3.
  
  
• z-Test: Two-Sample for Means — Performs a two-sample z-Test for means with known variances. This is also available via the following worksheet function:
  Thực hiện một phép thử z-Test cho hai mẫu để lấy các giá trị trung bình với các phương sai đã biết. Điều này cũng có thể thực hiện thông qua hàm có sẵn sau đây
  
  ZTEST (array1, array2, tails, type)

The next few sections look at five of these tools in more depth: Descriptive Statistics, Correlation, Histogram, Random Number Generation, and Rank and Percentile.
Các phần tiếp theo đây sẽ xem xét chuyên sâu hơn của 5 trong số những công cụ này: Descriptive Statistics, Correlation, Histogram, Random Number Generation, và Rank and Percentile.​

 

[BNTT]

12.7. Using the Analysis ToolPak Statistical Tools


12.7.1. Using the Descriptive Statistics Tool
Sử dụng công cụ Descriptive Statistics

You saw earlier in this chapter that Excel has separate statistical functions for calculating values such as the mean, maximum, minimum, and standard deviation values of a population or sample. If you need to derive all of these basic analysis stats, entering all those functions can be a pain. Instead, use the Analysis ToolPak’s Descriptive Statistics tool. This tool automatically calculates 16 of the most common statistical functions and lays them all out in a table. Follow these steps to use this tool:
Ở đầu chương này, bạn đã thấy rằng Excel có nhiều hàm thống kê riêng biệt để tính toán các giá trị như là trung bình, lớn nhất, nhỏ nhất, độ lệch chuẩn của một tập hợp hay một mẫu. Nếu bạn cần dẫn xuất tất cả các số liệu thống kê phân tích cơ bản này, việc nhập các hàm vào có thể là một công việc rất cực. Thay vào đó, bạn nên sử dụng công cụ Descriptive Statistics của Analysis ToolPak. Công cụ này tự động tính 16 trong số các hàm thống kê phổ biến và sắp đặt tất cả chúng trong một bảng. Bạn làm theo các bước sau đây để sử dụng công cụ này:

NOTE:

Keep in mind that the Descriptive Statistics tool outputs only numbers, not formulas.Therefore, if your data changes, you’ll have to repeat the following steps to run the tool again.
Lưu ý rằng, đầu ra của công cụ Descriptive Statistics này chỉ là những con số (kết quả) chứ không phải là những công thức. Do đó, nếu số liệu của bạn thay đổi, bạn sẽ phải làm lại từ đầu những bước sau đây. ​

1. Select the range that includes the data you want to analyze (including the row and column headings, if any).
   Chọn dãy chứa dữ liệu mà bạn muốn phân tích (kể cả các tiêu đề hàng và cột, nếu có).
   
   
2. Choose Data, Data Analysis to display the Data Analysis dialog box.
   Chọn Data, Data Analysis để hiển thị hộp thoại Data Analysis.
   
   
3. Click the Descriptive Statistics option and click OK. Excel displays the Descriptive Statistics dialog box. Figure 12.11 shows the completed dialog box.
   Nhấn chọn mục Descriptive Statistics và nhấn OK. Excel hiển thị hộp thoại Descriptive Statistics. Hình 12.11 minh họa hộp thoại này (đã hoàn chỉnh).
   
   

   
   Figure 12.11​
   
   
   
4. Use the Output Options group to select a location for the output. For each set of data included in the input range, Excel creates a table that is 2 columns wide and up to 18 rows high.
   Sử dụng nhóm Output Options để chọn nơi sẽ chứa các kết quả. Với mỗi tập hợp dữ liệu đã đưa vào ở mục Input Range, Excel sẽ tạo ra một bảng rộng 2 cột và có tới 18 hàng.
   
   
5. Choose the statistics you want to include in the output:
   Chọn loại số liệu thống kê mà bạn muốn bao gồm trong kết quả:
   
   
   • Summary Statistics — Activate this option to include statistics such as the mean, median, mode, and standard deviation.
     Chọn mục này để bao gồm các số liệu thống kê như giá trị trung bình, trung bình vị, số lần xuất hiện, và độ lệch chuẩn.
     
     
   • Confidence Level for Mean — Activate this option if your data set is a sample of a larger population and you want Excel to calculate the confidence interval for the population mean. A confidence level of 95% means that you can be 95% confident that the population mean will fall within the confidence interval. For example, if the sample mean is 10 and Excel calculates a confidence interval of 1.5, you can be 95% sure that the population mean will fall between 8.5 and 12.5.
     Chọn mục này nếu dữ liệu của bạn mà một mẫu của một tập hợp lớn hơn, và bạn muốn Excel tính toán khoảng tin cậy cho giá trị trung bình của cả tập hợp. Một mức tin cậy 95% nghĩa là bạn có thể tự tin rằng 95% giá trị trung bình của tập hợp sẽ rơi vào trong khoảng tin cậy. Ví dụ, nếu giá trị trung bình của mẫu là 10, và Excel tính một khoảng tin cậy 1.5, bạn có thể chắc chắn 95% rằng giá trị trung bình của cả tập hợp sẽ rơi vào giữa 8.5 và 12.5.
     
     
   • Kth Largest — Activate this option to add a row to the output that specifies the kth largest value in the sample. The default value for k is 1 (that is, the largest value), but if you want to see any other number, enter a value for k in the text box.
     Chọn mục này để thêm một hàng vào kết quả để xác định giá trị lớn thứ k trong mẫu. Giá trị mặc định cho k luôn là 1 (là giá trị lớn nhất), nếu bạn muốn xem một giá trị khác, nhập giá trị đó cho k vào trong khung bên cạnh.
     
     
   • Kth Smallest — Activate this option to include the sample’s kth smallest value in the output. Again, if you want k to be something other than 1 (that is, the smallest value), enter a number in the text box.
     Chọn mục này để thêm một hàng vào kết quả để xác định giá trị nhỏ thứ k trong mẫu. Lần nữa, nếu bạn muốn xem một giá trị khác ngoài 1 (là giá trị nhỏ nhất), nhập giá trị đó cho k vào trong khung bên cạnh.
   
   
   
6. Click OK. Excel calculates the various statistics and displays the output table. (See Figure 12.12 for an example.)
   Nhấn OK. Excel sẽ tính toán các loại số liệu thống kê này và hiển thị nó trong bảng kết quả. (Xem ví dụ ở hình 12.12)
   
   

   
   Figure 12.12 - Statistics.xlsx​

 

[BNTT]

12.7. Using the Analysis ToolPak Statistical Tools


12.7.2. Determining the Correlation Between Data
Quyết định sự tương quan giữa các dữ liệu (công cụ Correlation)

Correlation is a measure of the relationship between two or more sets of data. For example, if you have monthly figures for advertising expenses and sales, you might wonder whether they’re related. That is, do higher advertising expenses lead to more sales? To determine this, you need to calculate the correlation coefficient. The coefficient is a number between –1 and 1 that has the following properties:
Correlation (tương quan) là một thước đo mối quan hệ giữa hai hay nhiều tập hợp dữ liệu. Ví dụ, bạn có các số liệu chi phí quảng cáo và doanh thu hằng tháng, bạn muốn biết chúng có liên quan với nhau hay không; nghĩa là chi phí quảng cáo cao thì có dẫn đến doanh thu cao không? Để xác định điều này, bạn cần tính toán các hệ số tương quan. Hệ số là một con số giữa -1 và 1, với những đặc tính sau đây:

• Correlation Coefficient = 1 : The two sets of data are perfectly and positively correlated. For example, a 10% increase in advertising produces a 10% increase in sales.
  Hai tập hợp dữ liệu tương quan hoàn toàn và tỷ lệ thuận với nhau. Ví dụ, việc tăng 10% chi phí quảng cáo sẽ làm tăng 10% doanh thu.
  
  
• Correlation Coefficient between 0 and 1 : The two sets of data are positively correlated (an increase in advertising leads to an increase in sales). The higher the number, the higher the correlation is between the data.
  Hai tập hợp dữ liệu tương quan và tỷ lệ thuận với nhau (việc tăng chi phí quảng cáo sẽ làm tăng doanh thu). Số càng cao (càng gần 1) thì sự tương quan giữa dữ liệu càng cao.
  
  
• Correlation Coefficient = 0 : There is no correlation between the data.
  Dữ liệu không tương quan với nhau.
  
  
• Correlation Coefficient between -1 and 0 : The two sets of data are negatively correlated (an increase in advertising leads to a decrease in sales). The lower the number is, the more negatively correlated the data is.
  Hai tập hợp dữ liệu tương quan và tỷ lệ nghịch với nhau (việc tăng chi phí quảng cáo sẽ làm giảm doanh thu). Số càng thấp (càng gần -1) thì tỷ lệ nghịch của sự tương quan giữa dữ liệu càng cao.
  
  
• Correlation Coefficient = -1 : The data sets have a perfect negative correlation. For example, a 10% increase in advertising leads to a 10% decrease in sales (and, presumably, a new advertising department).
  Hai tập hợp dữ liệu tương quan hoàn toàn và tỷ lệ nghịch với nhau. Ví dụ, việc tăng 10% chi phí quảng cáo sẽ làm giảm 10% doanh thu (và có lẽ nên thay đổi một hình thức quảng cáo khác).

To calculate the correlation between data sets, follow these steps:
Để tính sự tương quan giữa hai tập hợp dữ liệu, bạn theo các bước sau:

1. Choose Data, Data Analysis to display the Data Analysis dialog box.
   Chọn Data, Data Analysis để hiển thị hộp thoại Data Analysis.
   
   
2. Click the Correlation tool and then click OK. The Correlation dialog box, shown in
   Figure 12.13, appears.
   Nhấn chọn công cụ Correlation và nhấn OK. Hộp thoại Correlation sẽ mở ra như minh họa ở hình 12.13.
   
   

   
   Figure 12.13​
   
   
3. Use the Input Range box to select the data range you want to analyze, including the row or column headings.
   Sử dụng hộp Input Range để chọn dãy dữ liệu bạn muốn phân tích, bao gồm cả tiêu đề dòng hoặc cột.
   
   
4. If you included labels in your range, activate the Labels in First Row check box. (If your data is arranged in rows, this check box reads Labels in First Column.)
   Nếu bạn có bao gồm các nhãn (tiêu đề cột), thì kích hoạt hộp kiểm Labels in First Row (nếu dữ liệu của bạn sắp xếp theo các hàng, thì hộp kiểm này sẽ có tên là Labels in First Column).
   
   
5. Excel displays the correlation coefficients in a table, so use the Output Range box to enter a reference to the upper-left corner of the table. (If you’re comparing two sets of data, the output range is three columns wide by three rows high.) You also can select a different sheet or workbook.
   Excel sẽ hiển thị các hệ số tương quan trong một bảng, do đó bạn sử dụng hộp Output Range để nhập một tham chiếu cho ô trên cùng bên trái của bảng này (nếu bạn so sánh hai tập hợp dữ liệu, vùng kết quả xuất ra sẽ gồm 3 cột và 3 hàng). Bạn cũng có thể chọn một trang tính khác hoặc một bảng tính khác làm nơi chứa kết quả.
   
   
6. Click OK. Excel calculates the correlation and displays the table.
   Nhấn OK. Excel tính toán các tương quan và hiển thị ra bảng.

Figure 12.14 shows a worksheet that compares advertising expenses with sales. For a control, I’ve also included a column of random numbers (labeled Tea in China). The Correlation table lists the various correlation coefficients. In this case, the high correlation between advertising and sales (0.74) means that these two factors are strongly (and positively) correlated. As you can see, there is (as you might expect) almost no correlation among advertising, sales data, and the random numbers.
Hình 12.14 minh họa một bảng tính so sánh chi phí quảng cáo với doanh thu. Để cho rõ ràng hơn, tôi cũng bao gồm thêm một cột gồm những con số ngẫu nhiên (có tên là Tea in China). Bảng tương quan liệt kê các hệ số tương quan khác nhau. Trong trường hợp này, số tương quan cao giữa advertising và sales (0.74) có nghĩa rằng hai hệ số này tương quan mạnh (và tỷ lệ thuận) với nhau. Và như bạn có thể thấy, chẳng có sự tương quan nào giữa quảng cáo và doanh thu với mấy con số ngẫu nhiên cả.

Figure 12.14 - Statistics.xlsx​

 

[BNTT]

12.7. Using the Analysis ToolPak Statistical Tools


12.7.3. Working with Histograms
Làm việc với công cụ Histograms

The Analysis ToolPak’s Histogram tool calculates the frequency distribution of a range of data. It also calculates cumulative frequencies for your data and produces a bar chart that shows the distribution graphically.
Công cụ Histogram của Analysis ToolPak tính toán sự phân bổ tần số của một dãy dữ liệu. Nó cũng tính các tần số tích lũy cho dữ liệu và tạo một biểu đồ dạng thanh (bar chart) minh họa cho đồ thị của sự phân bổ.

Before you use the Histogram tool, you need to decide which groupings (or bins) you want Excel to use for the output. These bins are numeric ranges, and the Histogram tool works by counting the number of observations that fall into each bin. You enter the bins as a range of numbers, where each number defines a boundary of the bin.
Trước khi sử dụng công cụ Histogram, bạn càn quyết định các nhóm (các khoản dữ liệu) mà bạn muốn Excel sử dụng cho dữ liệu xuất ra. Những khoản dữ liệu là các dãy số, và công cụ Histogram làm việc bẳng cách đếm số giá trị rơi vào trong mỗi khoản dữ liệu này. Bạn nhập các khoản dữ liệu như một dãy số, trong đó mỗi số định nghĩa cho một biên của khoản dữ liệu.

For example, Figure 12.15 shows a worksheet with two ranges. One is a list of student grades. The second range is the bin range. For each number in the bin range, Histogram counts the number of observations that are greater than or equal to the bin value, and less than (but not equal to) the next higher bin value. Therefore, in Figure 12.15, the six bin values correspond to the following ranges:
Ví dụ, hình 12.15 minh họa một bảng tính với hai dãy. Một dãy liệt kê số điểm của các sinh viên. Dãy thứ hai là các khoản dữ liệu (bin). Với mỗi số trong dãy thứ hai này, Histogram đếm số giá trị lớn hơn hay bằng giá trị một khoản dữ liệu, và nhỏ hơn (nhưng không bằng) giá trị của khoản dữ liệu kế tiếp. Trong hình 12.15, 6 khoản dữ liệu tương ứng với các dãy sau đây:

00 <= Grade < 50
50 <= Grade < 60
60 <= Grade < 70
70 <= Grade < 80
80 <= Grade < 90
90 <= Grade < 100

Figure 12.15 - Statistics.xlsx​

Follow these steps to use the Histogram tool:
Theo những bước sau đây để biết cách sử dụng công cụ Histogram:

1. Choose Data, Data Analysis to display the Data Analysis dialog box.
   Chọn Data, Data Analysis để hiển thị hộp thoại Data Analysis.
   
   
2. Click the Histogram option and then click OK. Excel displays the Histogram dialog
   box. Figure 12.16 shows the dialog box already filled in.
   Nhấn chọn Histogram và nhấn OK. Excel hiển thị hộp thoại Histogram. Hình 12.16 minh họa hộp thoại này, đã được điền đầy đủ các thông tin.
   
   
   
   
3. Use the Input Range and Bin Range text boxes to enter the ranges holding your data and bin values, respectively.
   Sử dụng hộp Input Range và Bin Range để nhập lần lượt các dãy chứa dữ liệu và dãy chứa các khoản dữ liệu.
   
   
4. Use the Output Options group to select a location for the output. The output range will be one row taller than the bin range, and it could be up to six columns wide (depending on which of the following options you choose).
   Sử dụng nhóm Output Options để chọn vị trí cho kết quả xuất ra. Dãy này sẽ nhiều hơn dãy chứa các khoản dữ liệu một hàng, và nó có thể có tới 6 cột (tùy thuộc vào những tùy chọn trong nhóm Option mà bạn đã chọn).
   
   
5. Select the other options you want to use for the frequency distribution:
   Chọn thêm các tùy chọn mà bạn muốn dùng cho sự phân bố tần số sau đây:
   
   
   • Pareto (sorted histogram) — If you activate this check box, Excel displays a second output range with the bins sorted in order of descending frequency. (This is called a Pareto distribution.)
     Nếu bạn kích hoạt hộp kiểm này, Excel sẽ hiển thị một dãy kết quả thứ hai với các biên được phân loại theo thứ tự giảm dần (cái này còn được gọi là phân bố Pareto).
     
     
   • Cumulative Percentage — If you activate this option, Excel adds a new column to the output that tracks the cumulative percentage for each bin.
     Nếu bạn kích hoạt hộp kiểm này, Excel sẽ thêm một cột nữa vào dãy kết quả để theo dõi phần trăm tích lũy cho mỗi khoản dữ liệu.
     
     
   • Chart Output — If you activate this option, Excel automatically generates a chart for the frequency distribution.
     Nếu bạn kích hoạt hộp kiểm này, Excel sẽ tự động vẽ một biểu đồ cho sự phân bổ tần số.
   
   
   
6. Click OK. Excel displays the histogram data, as shown in Figure 12.17.
   Nhấn OK. Excel hiển thị dữ liệu được phân bổ tần số như minh họa ở hình 12.17

Figure 12.17 - Statistics.xlsx​

 

[BNTT]

12.7. Using the Analysis ToolPak Statistical Tools


12.7.4. Using the Random Number Generation Tool
Sử dụng công cụ Random Number Generation

Unlike the RAND() function that generates real numbers only between 0 and 1, the Analysis ToolPak’s Random Number Generation tool can produce numbers in any range and can generate different distributions, depending on the application. Table 12.1 summarizes the seven available distribution types:
Không giống như hàm RANK(), là hàm chỉ tạo ra những con số thực trong khoảng giữa 0 và 1, công cụ Random Number Generation của Analysis ToolPak có thể tạo ra các con số trong bất kỳ dãy nào và còn có thể tạo ra những phân bố khác nhau tùy thuộc vào ứng dụng. Bảng 12.1 tóm tắt 7 loại phân bố có sẵn trong công cụ này.

Table 12.1. The Distributions Available with the Random Number Generation Tool
Bảng 12.1 - Các phân bố có sẵn với công cụ Random Number Generation

• Uniform : Generates numbers with equal probability from the range of values you provide. Using the range 0 to 1 produces the same distribution as the RAND() function.
  Tạo ra các số có xác suất bằng nhau từ dãy giá trị bạn đã cung cấp. Sử dụng dãy 0 đến 1 sẽ tạo ra một phân bố giống kết quả của hàm RAND().
  
  
• Normal : Produces numbers in a bell curve (normal) distribution based on the mean and standard deviation you enter. This is good for generating samples of things such as test scores and population heights.
  Tạo ra các số trong một phân bố chuẩn (phân bố có đường cong hình chuông) dựa vào giá trị trung bình và độ lệch chuẩn của bạn. Điều này thích hợp cho việc tạo ra các mẫu thử của những thứ như là điểm thi, chiều cao dân số.
  
  
• Bernoulli : Generates a random series of 1s and 0s based on the probability of success on a single trial. A common example of a Bernoulli distribution is a coin toss (in which the probability of success is 50%; in this case, as in all Bernoulli distributions, you would have to assign either heads or tails to be 1 or 0).
  Tạo ra môt chuỗi ngẫu nhiên gồm các số 1 và số 0 dựa trên xác suất thành công của một lần thử. Môt ví dụ thông thường của của phân bố Bernoulli là việc tung đồng xu (trong đó xác suất thành công là 50%; trong trường hợp này, như trong tất cả các phân bố Bernoulli, bạn phải gán cho mặt ngửa hoặc mặt sấp của đồng xu là 0 hay 1).
  
  
• Binomial : Generates random numbers characterized by the probability of success over a number of trials. For example, you could use this type of distribution to model the number of responses received for a direct-mail campaign. The probability of success would be the average (or projected) response rate, and the number of trials would be the number of mailings in the campaign.
  Tạo ra những con số ngẫu nhiên được mô tả bởi xác suất thành công trên một số lần thử. Ví dụ, bạn có thể dùng loại phân bố này để tạo mô hình về số phản hồi nhận được cho một chiến dịch gửi thư mời trực tiếp. Xác suất thành công là mức độ trung bình (hoặc dự đoán) tỷ lệ phản hồi, và số lần thử là số lần gửi thư trong chiến dịch này.
  
  
• Poisson : Generates random numbers based on the probability of a designated number of events occurring in a time frame. The distribution is governed by a value, Lambda, that represents the mean number of events known to occur over the time frame.
  Tạo ra các con số ngẫu nhiên dựa trên xác suất của một số sự kiện đã ấn định xảy ra trong một khung thời gian. Sự phân bố được chi phối bởi một giá trị, Lambda (λ), tượng trưng cho số sự kiện trung bình được biết xảy ra trong khung thời gian đó.
  
  
• Patterned : Generates random numbers according to a pattern that’s characterized by a lower and upper bound, a step value, and a repetition rate for each number and the entire sequence.
  Tạo ra những con số ngẫu nhiên phù hợp với một mẫu được mô tả với một cận dưới và một cận trên, một bước giá trị, và một tốc độ lặp lại cho mỗi số và cho toàn bộ chuỗi.
  
  
• Discrete : Generates random numbers from a series of values and probabilities for these values (in which the sum of the probabilities equals 1). You could use this distribution to simulate the rolling of dice (where the values would be 1 through 6, each with a probability of 1/6; see the following example).
  Tạo ra những số ngẫu nhiên từ một chuỗi giá trị liên tục và xác suất cho những giá trị này (với tổng của các xác suất bằng 1). Bạn có thể dùng phân bố này để mô phỏng việc tung xúc xắc (trong đó các giá trị là từ 1 đến 6, mỗi giá trị có xác suất xuất hiện là 1/6; xem trong ví dụ dưới đây).

Follow the steps outlined in the following procedure to use the Random Number Generation tool.
Bạn theo các bước trong các thủ tục dưới đây để biết cách sử dụng công cụ Random Number Generation:

1. Choose Data, Data Analysis to display the Data Analysis dialog box.
   Chọn Data, Data Analysis để hiển thị hộp thoại Data Analysis.
   
   
2. Click the Random Number Generation option and then click OK. The Random Number Generation dialog box appears, as shown in Figure 12.18.
   Nhấn chọn Random Number Generation và nhấn OK. Hộp thoại Random Number Generation mở ra như minh họa ở hình 12.18.
   
   
   
   
3. If you want to generate more than one set of random numbers, enter the number of sets (or variables) you need in the Number of Variables box. Excel enters each set in a separate column. If you leave this box blank, Excel uses the number of columns in the Output Range.
   Nếu bạn muốn tạo ra nhiều hơn một tập hợp các số ngẫu nhiên, bạn nhập số tập hợp (còn gọi là variables) bạn cần trong hộp Number of Variables. Excel nhập mỗi tập hợp trong một cột riêng lẻ. Nếu bạn bỏ trống ô này, Excel sẽ dùng số cột có trong Output Range.
   
   
4. Use the Number of Random Numbers text box to enter how many random numbers you need. Excel enters each number in a separate row. If you leave this box blank, Excel fills the Output Range.
   Dùng hộp Number of Random Numbers để nhập vào số lượng số ngẫu nhiên mà bạn cần. Excel sẽ nhập mỗi con số trong một hàng riêng biệt. Nếu bạn bỏ trống ô này, Excel sẽ điền vào dãy Output Range.
   
   
5. Use the Distribution drop-down list to click the distribution you want to use.
   Sử dụng danh sách Distribution xổ xuống để chọn loại phân bố bạn muốn dùng.
   
   
6. In the Parameters group, enter the parameters for the distribution you selected. (The options you see depend on the selected distribution.)
   Trong nhóm Parameters, nhập các tham số cho kiểu phân bố mà bạn đã chọn.
   
   
7. The Random Seed number is the value Excel uses to generate the random numbers. If you leave this box blank, Excel generates a different set each time. If you enter a value (which must be an integer between 1 and 32,767), you can reuse the value later to reproduce the same set of numbers.
   Số Random Seed là giá trị mà Excel sử dụng để tạo ra các số ngẫu nhiên. Nếu bạn bỏ trống hộp này, Excel sẽ tạo ra mỗi lần một tập hợp khác nhau. Nếu bạn nhập một giá trị (nằm trong khoảng từ 1 đến 32,767), sau này bạn có thể sử dụng lại các giá trị để tái tạo cùng một tập hợp số.
   
   
8. Use the Output Options group to select a location for the output.
   Sử dụng nhóm Output Options để chọn vị trí chứa dữ liệu xuất ra.
   
   
9. Click OK. Excel calculates the random numbers and displays them in the worksheet.
   Nhấn OK. Excel tính toán các số ngẫu nhiên và hiển thị chúng trong bảng tính.

As an example, Figure 12.19 shows a worksheet that is set up to simulate rolling two dice. The Probabilities box shows the values (the numbers 1 through 6) and their probabilities (=1/6 for each). A Discrete distribution is used to generate the two numbers in cells H2 and H3. The Discrete distribution’s Value and Probability Input Range parameter is the range $D$2:$E$7. Figure 12.20 shows the formulas used to display Die #1. (The formulas for Die #2 are similar, except that $H$2 is replaced with $H$3.)
Như một ví dụ, hình 12.19 minh họa một bảng tính được thiết lập để mô phỏng việc tung hai con xúc xắc. Hộp Probabilities minh họa các giá trị (từ 1 đến 6) và các xác suất của chúng (đều là 1/6). Một phân bố Discrete được dùng để tạo ra hai con số ở ô H2 và H3. Tham số Value và Probability Input Range của phân bố Discrete là dãy $D$2:$E$7. Hình 12.20 minh họa các công thức được sử dụng để hiển thị Die #1 (công thức cho Die#2 cũng giống vậy, ngoại trừ $H$2 được thay bằng $H$3).

Figure 12.19 - Statistics.xlsx (Ở hình này, tôi dùng font Wingdings để hiển thị được mấy cái chấm tròn)

Figure 12.20 - Statistics.xlsx​