Formulas & Functions Excel 2007 - 4. Creating Advanced Formulas (1 người xem)

[BNTT]

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

Part I: MASTERING EXCEL RANGES AND FORMULAS - Nắm vững các dãy và công thức trong Excel

• Chapter 1: Getting The Most Out Of Range - Tận dụng tối đa các dãy

• Chapter 2: Using a Range Name - Sử dụng tên cho dãy

• Chapter 3: Building Basic Formulas - Thiết lập các công thức (cơ bản)

• Chapter 4: Creating Advanced Formulas - Thiết lập các công thức (nâng cao)

• Chapter 5: Troubleshooting Formulas - Xử lý lỗi công thức

Chapter 4 - CREATING ADVANCED FORMULAS

Thiết lập các công thức (nâng cao)

Excel is a versatile program with many uses, from acting as a checkbook to a flat-file database management system, to an equation solver, to a glorified calculator. For most business users, however, Excel’s forte is building models that enable them to quantify particular aspects of the business. The skeleton of the business model is made up of the chunks of data entered, imported, or copied into the worksheets. But the lifeblood of the model and the animating force behind it is the collection of formulas that summarizes data, answers questions, and makes predictions.
Excel là một chương trình linh hoạt có nhiều công dụng, từ việc có chức năng như một cuốn sổ dùng để tính toán, cho đến một hệ thống cơ sở dữ liệu phẳng, một công cụ để giải phương trình, một chương trình tính toán tuyệt vời. Tuy nhiên, với đa số những người sử dụng là những nhà kinh doanh, sở trường của Excel là xây dựng những mô hình cho phép định lượng những khía cạnh cụ thể về công việc kinh doanh. Khung sườn của mô hình kinh doanh được tạo từ những cụm dữ liệu được nhập vào hoặc được sao chép vào các bảng tính. Nhưng nhân tố quyết định của mô hình và động lực chính đằng sau nó là tập hợp các công thức để tổng kết số liệu, trả lời cho những câu hỏi và đưa ra những dự đoán.

You saw in Chapter 3, "Building Basic Formulas" that, armed with the humble equals sign and the set of operators and operands, you can cobble together useful, robust formulas. But Excel has many other tricks up its digital sleeve, and these techniques enable you to create muscular formulas that can take your business models to the next level.
Ở chương 3, "Thiết lập những công thức (cơ bản)", bạn đã thấy rằng chỉ với một dấu bằng khiêm tốn cùng với một tập hợp các toán tử và toán hạng, bạn có thể tạo nên những công thức mạnh mẽ, hữu dụng. Nhưng Excel còn có nhiều thủ thuật khác và những kỹ thuật này cho phép tạo ra các công thức mạnh mẽ hơn, có thể đưa những mô hình kinh doanh của bạn lên tầm cao hơn.​

IN THIS CHAPTER:
Những nội dung chính trong chương này:

• Working with Arrays
  Làm việc với các mảng

• Using Iteration and Circular References
  Sử dụng sự lặp lại và các tham chiếu tuần hoàn

• Consolidating Multisheet Data
  Hợp nhất dữ liệu trên nhiều trang bảng tính

• Applying Data-Validation Rules to Cells
  Áp dụng các quy tắc hiệu lực hóa liệu (data-validation) vào các ô

• Using Dialog Box Controls on a Worksheet
  Sử dụng các điều khiển hộp thoại trong một bảng tính

 

[BNTT]

Chapter 4 - CREATING ADVANCED FORMULAS


4.1. Working with Arrays
Làm việc với các mảng

When you work with a range of cells, it might appear as though you’re working with a single thing. In reality, however, Excel treats the range as a number of discrete units.
Khi bạn làm việc với một dãy các ô, dường như bạn đang làm việc với một thứ duy nhất. Trong thực tế, Excel xem dãy này như một "đơn vị số" riêng biệt.

This is in contrast with the subject of this section: the array. An array is a group of cells or values that Excel treats as a unit. In a range configured as an array, for example, Excel no longer treats the cells individually. Instead, it works with all the cells at once, which enables you to do things like apply a formula to every cell in the range using just a single operation.
Điều này hơi trái với chủ đề của phần này: mảng (array). Mảng là một nhóm ô hoặc giá trị mà Excel xem như là một đơn vị. Ví dụ, trong một dãy được cấu hình dưới dạng một mảng, Excel không còn xử lý các ô một cách riêng lẻ nữa. Thay vào đó, nó làm việc với tất cả các ô trong cùng một lúc, nghĩa là cho phép bạn làm những điều như áp dụng một công thức vào mỗi ô trong dãy chỉ với một thao tác.

You create arrays either by running a function that returns an array result (such as LOGEST() or by entering an array formula, which is a single formula that either uses an array as an argument or enters its results in multiple cells.
Bạn có thể tạo các mảng bằng cách chạy một hàm mà kết quả trả về là một mảng (ví dụ như hàm LOGEST() hoặc bằng cách nhập một công thức mảng (array formula), là một công thức đơn nhưng sử dụng các mảng làm đối số hoặc nhập các kết quả của nó trong nhiều ô.​

 

[BNTT]

4.1. Working with Arrays


4.1.1. Using Array Formulas
Sử dụng các công thức mảng

Here’s a simple example that illustrates how array formulas work. In the Expenses workbook shown in Figure 4.1, the 2008 BUDGET totals are calculated using a separate formula for each month, as shown here:
Sau đây là một ví dụ đơn giản minh họa công thức mảng làm việc như thế nào. Trong bảng tính Expenses ở hình 4.1, các ô tính tổng 2008 BUDGET tính toán bằng cách sử dụng từng công thức riêng biệt trong mỗi ô, như sau đây:

January 2008 BUDGET: =C11*$C$3
February 2008 BUDGET: =D11*$C$3
March 2008 BUDGET: =E11*$C$3​

You can replace all three formulas with a single array formula by following these steps:
Bạn có thể thay thế cả 3 công thức ấy bằng một công thức mảng đơn giản, theo từng bước sau đây:

1. Select the range that you want to use for the array formula. In the 2008 BUDGET example, you’d select C13:E13.
   Chọn dãy ô mà bạn muốn dùng công thức mảng. Ví dụ trong bảng tính 2008 BUDGET, bạn chọn dãy ô C13:E13.
   
   
2. Type the formula and, in the places where you would normally enter a cell reference, type a range reference that includes the cells you want to use. Don’t — I repeat, don’t — press Enter when you’re done. In the example, you’d enter:
   Gõ công thức, và ở những nơi mà bạn thường nhập một tham chiếu đến một ô, hãy nhập vào đó tham chiếu đến dãy bao gồm những ô mà bạn muốn dùng để tính toán. Đừng (tôi nhắc lại, đừng) nhấn Enter sau khi nhập xong công thức. Trong ví dụ này, bạn nhập công thức sau:
   
   = C11:E11*$C$3 ​
3. To enter the formula as an array, press Ctrl+Shift+Enter.
   Để nhập một công thức ở dạng một công thức mảng, nhấn Ctrl+Shift+Enter.
   
   The 2008 BUDGET cells (C13, D13, and E13) now all contain the same formula:
   Các ô 2008 BUDGET (C13, D13 và E13) bây giờ đều có chung một công thức:
   
   {=C11:E11*$C$3}​

Figure 4.1 (hình 4.1)​

In other words, you were able to enter a formula into three different cells using just a single operation. This can save you tremendous amounts of time when you have to enter the same formula into many different cells.
Nói cách khác, bạn có thể nhập một công thức vào 3 ô khác nhau chỉ với một thao tác. Điều này có thể giúp bạn tiết kiệm được một lượng thời gian đáng kể so với việc nhập cùng một công thức vào nhiều ô khác nhau.

Notice that the formula is surrounded by braces ({}). This identifies the formula as an array formula. When you enter array formulas, you never need to enter these braces yourself; Excel adds them automatically.
Chú ý rằng công thức trên được đặt trong một cặp dấu ngoặc {}. Điều này cho ta biết đó một công thức mảng. (Khi bạn nhập một công thức mảng, bạn không bao giờ tự tay nhập cặp dấu này, mà Excel sẽ tự thêm nó vào, sau khi bạn nhấn Ctrl+Shift+Enter. (Nói thêm, khi bạn nhấn F2 để sửa công thức, bạn sẽ thấy cặp dấu {} tự động biến mất; và nhớ rằng, sau khi sửa công thức, bạn phải nhấn Ctrl+Shift+Enter chứ không phải nhấn Enter như bình thường.)

NOTE: Because Excel treats arrays as a unit, you can’t move or delete part of an array. If you need to work with an array, you must select the whole thing. If you want to reduce the size of an array, select it, activate the formula bar, and then press Ctrl+Enter to change the entry to a normal formula. You can then select the smaller range and re-enter the array formula.
Như đã nói ở trên, bởi vì Excel xem mảng như một đơn vị, nên bạn không thể di chuyển hay xóa một thành phần nào trong một mảng. Nếu bạn cần phải làm việc với một mảng, bạn phải chọn toàn bộ mảng. Nếu bạn muốn thay đổi kích thước của một mảng, bạn chọn nó, kích hoạt thanh công thức (formula bar), rồi nhấn Ctrl+Enter để chuyển công thức trong đó trở thành công thức bình thường. Bây giờ bạn có thể chọn lại một dãy ô nhỏ hơn (hoặc lớn hơn) và nhập lại công thức mảng.

Note that you can select an array quickly by activating one of its cells and pressing Ctrl+/.
Ghi chú thêm: Bạn có thể chọn nhanh một mảng bằng cách kích hoạt một ô bất kỳ trong mảng đó và nhấn Ctrl+/ (dấu gạch chéo)​

 

[BNTT]

4.1.1. Using Array Formulas


■ Understanding Array Formulas
Tìm hiểu các công thức mảng

To understand how Excel processes an array, you need to keep in mind that Excel always sets up a correspondence between the array cells and the cells of whatever range you entered into the array formula.
Để hiểu Excel xử lý một mảng như thế nào, bạn cần ghi nhớ rằng Excel luôn luôn thiết lập một sự tương ứng giữa các ô trong mảng và các ô của bất kỳ dãy nào mà bạn đã nhập vào công thức mảng.

In the 2008 BUDGET example, the array consists of cells C13, D13, and E13, and the range used in the formula consists of cells C11, D11, and E11. Excel sets up a correspondence between array cell C13 and input cell C11, D13 and D11, and E13 and E11. To calculate the value of cell C13 (the January 2008 BUDGET), for example, Excel just grabs the input value from cell C11 and substitutes that in the formula. Figure 4.2 shows a diagram of this process.
Trong ví dụ 2008 BUDGET, mảng bao gồm các ô C13, D13 và E13; và dãy được sử dụng trong công thức bao gồm các ô C11, D11, và E11. Excel thiết lập sự tương ứng giữa ô mảng C13 với ô nhập liệu C11, ô D13 với ô D11, và ô E13 với ô E11. Ví dụ, để tính giá trị của ô C13 (January 2008 BUDGET), Excel chỉ việc lấy giá trị nhập liệu từ ô C11 và thay thế giá trị đó trong công thức. Hình 4.2 minh họa một biểu đồ của tiến trình này.

Figure 4.2 (hình 4.2)​

Array formulas can be confusing, but if you keep these correspondences in mind, you should have no trouble figuring out what’s going on.
Các công thức mảng có thể sẽ làm cho bạn bối rối, nhưng nếu bạn nhớ những điểm tương ứng tôi vừa nói này, thì bạn sẽ không gặp khó khăn gì khi hiểu được điều gì đang xảy ra.
​

 

[BNTT]

4.1.1. Using Array Formulas


■ Array Formulas That Operate on Multiple Ranges
Các công thức mảng thao tác trên nhiều dãy

In the preceding example, the array formula operated on a single range, but array formulas also can operate on multiple ranges. For example, consider the Invoice Template worksheet shown in Figure 4.3. The totals in the Extension column (cells F12 through F16) are generated by a series of formulas that multiply the item’s price by the quantity ordered:
Trong ví dụ ở bài trước, công thức mảng thao tác trên một dãy đơn, nhưng các công thức mảng cũng có thể thao tác trên nhiều dãy. Ví dụ, xem bảng tính Invoice Template được minh họa ở hình 4.3. Các tổng trong cột Extension (từ ô F12 đến ô F16) được tạo bằng một chuỗi công thức lấy giá của món hàng nhân với số lượng được đặt hàng:

F12 = B12*E12
F13 = B13*E13
F14 = B14*E14
F15 = B15*E15
F16 = B16*E16

Figure 4.3 (Hình 4.3)​

You can replace all these formulas by making the following entry as an array formula into the range F12:F16:
Bạn có thể thay thế tất cả các công thức này bằng cách nhập công thức mảng sau đây vào dãy F12:F16:

= B12:B16*E12:E16​

Again, you’ve created the array formula by replacing each cell reference with the corresponding range (and by pressing Ctrl+Shift+Enter).
Một lần nữa, bạn đã tạo công thức mảng bằng cách thay thế mỗi tham chiếu ô bằng dãy tương ứng (và bằng cách nhấn Ctrl+Shift+Enter sau khi nhập xong công thức).

NOTE: You don’t have to enter array formulas in multiple cells. For example, if you don’t need the Extended totals in the Invoice Template worksheet, you can still calculate the Subtotal by making the following entry as an array formula in cell F17:
Bạn không cần phải nhập công thức mảng vào nhiều ô. Ví dụ, nếu bạn không muốn tính các tổng ở cột Extension trong bảng tính Invoice Template, mà bạn chỉ cần tính giá trị Subtotal, bạn nhập công thức sau đây vào ô F17:

= SUM(B12:B16*E12:E16)​

File ví dụ ở hình 4.3: InvoiceTemplate.xlsx​

 

[BNTT]

4.1. Working with Arrays


4.1.2. Using Array Constants
Sử dụng các hằng mảng

In the array formulas you’ve seen so far, the array arguments have been cell ranges. You also can use constant values as array arguments. This procedure enables you to input values into a formula without having them clutter your worksheet.
Trong các công thức mảng mà tôi đã trình bày từ đầu topic đến giờ, các đối số mảng là những dãy ô. Bạn cũng có thể sử dụng các giá trị hằng số để làm các đối số mảng. Thủ tục này cho phép bạn nhập thẳng các giá trị vào trong một công thức mà không làm xáo trộn bảng tính.

To enter an array constant in a formula, enter the values right in the formula and observe the following guidelines:
Để nhập một hằng mảng vào trong công thức, bạn nhập các giá trị ngay trong công thức và chú ý đến những quy tắc sau đây:

• Enclose the values in braces ({}).
  Đặt các giá trị trong cặp dấu ngoặc {}.

• If you want Excel to treat the values as a row, separate each value with a semicolon.
  Nếu bạn muốn Excel xem các giá trị là nằm trên một hàng, bạn tách biệt các giá trị bằng một dấu chấm phẩy (.

• If you want Excel to treat the values as a column, separate each value with a comma.
  Nếu bạn muốn Excel xem các giá trị là nằm trong một cột, bạn tách biệt các giá trị bằng một dấu phẩy (,).

For example, the following array constant is the equivalent of entering the individual values in a column on your worksheet:
Ví dụ, hằng mảng sau đây tương đương với việc nhập các giá trị riên lẻ trong mỗi cột trong bảng tính:

{1;2;3;4}​

Similarly, the following array constant is equivalent to entering the values in a worksheet range of three columns and two rows:
Tương tự, hằng mảng sau đây tương đương với việc nhập các giá trị riêng lẻ trong một dãy của bảng tính gồm có 3 cột và 2 hàng:

{1,2,3;4,5,6}​

Figure 4.4 shows two different array formulas. The one on the left (used in the range E4:E7) calculates various loan payments, given the different interest rates in the range C5:C8. The array formula on the right (used in the range F4:F7) does the same thing, but the interest rate values are entered as an array constant directly in the formula.
Hình 4.4 minh họa hai ví dụ về công thức mảng khác nhau. công thức ở bên trái (trong dãy E4:E7) tính các khoản vay phải thanh toán với những lãi suất khác nhau trong dãy C5:C8. Còn công thức ở bên phải (trong dãy F4:F7) cũng tính toán tương tự, nhưng các giá trị lãi suất được nhập trực tiếp dưới dạng một hằng mảng trong công thức.

Figure 4.4 (Hình 4.4)​

• Hàm PMT()

 

[BNTT]

4.1. Working with Arrays


4.1.3. Functions That Use or Return Arrays
Những hàm sử dụng các mảng hoặc trả về kết quả là các mảng

Many of Excel’s worksheet functions either require an array argument or return an array result (or both). Table 4.1 lists several of these functions and explains how each one uses arrays.
Có nhiều hàm trong bảng tính của Excel đòi hỏi đối số phải là mảng hoặc trả về kết quả là mảng (hay cả hai). Bảng 4.1 liệt kê danh sách một số hàm này và giải thích mỗi hàm sử dụng các mảng như thế nào.

Table 4.1 - Some Excel Functions That Use Arrays
Bảng 4.1 - Một số hàm sử dụng mảng trong Excel

When you use functions that return arrays, be sure to select a range large enough to hold the resulting array, and then enter the function as an array formula.
Khi bạn sử dụng các hàm trả về kết quả là mảng, bạn nhớ chọn một dãy đủ lớn để chứa mảng kết quả được tạo ra, sau đó nhập hàm dưới dạng một công thức mảng.​

 

[BNTT]

Chapter 4 - CREATING ADVANCED FORMULAS


4.2. Using Iteration and Circular References
Sử dụng sự lặp lại và các tham chiếu tuần hoàn

A common business problem involves calculating a profit-sharing plan contribution as a percentage of a company’s net profits. This isn’t a simple multiplication problem because the net profit is determined partly by the profit-sharing figure. For example, suppose that a company has revenue of $1,000,000 and expenses of $900,000, which leaves a gross profit of $100,000. The company also sets aside 10% of net profits for profit sharing. The net profit is calculated with the following formula:
Một vấn đề kinh doanh thường gặp là tính toán sự đóng góp kế hoạch chia sẻ lợi nhuận dưới dạng phần trăm lợi nhuận ròng hằng năm của công ty. Đây không phải là bài toán nhân đơn giản, bởi vì lợi nhuận ròng được quyết định một phần bởi con số chia sẻ lợi nhuận. Ví dụ, công ty có thu nhập $1,000,000 và chi phí đã hết $900,000, để lại tổng lợi nhuận là $100,000. Công ty dành riêng 10% lợi nhuận ròng cho việc chia sẻ lợi nhuận. Lợi nhuận ròng được tính toán bằng công thức sau:

Net Profit = Gross Profit - Profit Sharing Contribution
(Lợi nhuận ròng = Tổng lợi nhuận - Khoản đóng góp chia sẻ lợi nhuận)​

This is called a circular reference formula because there are terms on the left and right sides of the equals sign that depend on each other. Specifically, the Profit Sharing Contribution is derived with the following formula:
Đây được gọi là một công thức tham chiếu tuần hoàn bởi vì có các số hạng ở vế trái và vế phải của dấu bằng lệ thuộc vào nhau. Cụ thể, Profit Sharing Contribution (khoản đóng góp chia sẻ lợi nhuận) được tính bởi công thức sau đây:

Profit Sharing Contribution = (Net Profit)*0.1
(Khoản đóng góp chia sẻ lợi nhuận = (Lợi nhuận ròng)*0.1)​

One way to solve such a formula is to guess at an answer and see how close you come. For example, because profit sharing should be 10% of net profits, a good first guess might be 10% of gross profits, or $10,000. If you plug this number into the formula, you end up with a net profit of $90,000. This isn’t right, however, because 10% of $90,000 is $9,000. Therefore, the profit-sharing guess is off by $1,000.
Một cách để giải quyết loại công thức như vầy là đoán câu trả lời và xem bạn đạt gần đến như thế nào. Ví dụ, bởi vì viêc chia sẻ lợi nhuận là 10% của lợi nhuận ròng, điều có thể đoán được trước tiên có thể là 10% tổng lợi nhuận, tức $10,000. Nếu bạn đưa con số này vào công thức, bạn sẽ có lợi nhuận ròng là $90,000. Tuy nhiên, điều này sai, bởi vì 10% của $90,000 là $9,000. Do đó, việc đoán số tiền dùng để chia sẻ lợi nhuận ròng lệch $1,000.

So, you can try again. This time, use $9,000 as the profit-sharing number. Plugging this new value into the formula gives a net profit of $91,000. This number translates into a profit-sharing contribution of $9,100 — which is off by only $100.
Vậy, bạn có thể thử lại. Lần này, dùng $9,000 làm con số chia sẻ lợi nhuận ròng. Đem giá trị mới này vào công thức, bạn có lợi nhuận ròng là $91,000. Con số này sẽ đưa đến con số chia sẻ lợi nhuận ròng là $9,100 — chỉ lệch có $100 so với con số đã đoán.

If you continue this process, your profit-sharing guesses will get closer to the calculated value (this process is called convergence). When the guesses are close enough (for example, within a dollar), you can stop and pat yourself on the back for finding the solution. This process is called iteration.
Nếu bạn tiếp tục tiến trình đoán mò này, những ước đoán về con số chia sẻ lợi nhuận ròng sẽ ngày càng gần với giá trị tính toán hơn (tiến trình này được gọi là sự hội tụ). Khi các tiên đoán đủ gần (ví dụ, sự lệch nhau chỉ trong vòng 1 đô-la) thì bạn ngừng lại và tìm lời giải. Tiến trình này được gọi là sự lặp lại.

Of course, you didn’t spend your (or your company’s) hard-earned money on a computer so that you could do this sort of thing by hand. Excel makes iterative calculations a breeze, as you see in the following procedure:
Nhưng mà, chẳng lẽ bạn (hoặc công ty của bạn) lại bỏ đi số tiền cực khổ kiếm được để mua máy tính, rồi lại làm công việc này bằng tay? Excel sẽ giúp bạn làm cho các phép tự động lập đi lập lại cách dễ dàng, như bạn thấy sau đây:

1. Set up your worksheet and enter your circular reference formula. Figure 4.5 shows a worksheet for the example discussed previously. If Excel displays a dialog box telling you that it can’t resolve circular references, click OK, and then choose Formulas, Remove Arrows.
   Thiết lập bảng tính của bạn và nhập cái công thức tham chiếu tuần hoàn ấy vào. Hình 4.5 minh họa một bảng tính cho ví dụ mà tôi vừa trình bày trên đây. Nếu Excel mở ra một hộp thoại báo rằng nó không thể phân tích các tham chiếu tuần hoàn, bạn cứ nhấn OK (có thể phải đóng cả cái cửa sổ Help mở ra tiếp theo đó) và sau đó chọn Formulas, Remove Arrows (trong nhóm Formula Auditing).
   
   

   
   Figure 4.5 (hình 4.5)​
   
   
   
2. Choose Office, Excel Options to display the Excel Options dialog box.
   Chọn Office, Excel Options để mở hộp thoại Excel Options.
   
   
3. Click Formulas.
   Chọn Formulas.
   
   
4. Activate the Enable Iterative Calculation check box.
   Kích hoạt tùy chọn Enable Iterative Calculation.
   
   
5. Use the Maximum Iterations spin box to specify the number of iterations you need. In most cases, the default figure of 100 is more than enough.
   Sử dụng hộp spin Maximum Iterations để xác định số lần lặp lại mà bạn cần. Trong hầu hết các trường hợp, con số mặc định 100 thì đã quá đủ rồi.
   
   
6. Use the Maximum Change text box to tell Excel how accurate you want your results to be. The smaller the number is, the longer the iteration takes and the more accurate the calculation will be. Again, the default value of 0.001 is a reasonable compromise in most situations.
   Nhập một con số vào trong khung Maximum Change để cho Excel biết bạn muốn kết quả chính xác đến cỡ nào. Con số càng nhỏ thì sự lặp đi lặp lại sẽ càng lâu, nhưng phép tính sẽ càng chính xác. Cũng như ở trên, con số mặc định 0.001 là một lựa chọn hợp lý trong hầu hết các tình huống.
   
   
7. Click OK. Excel begins the iteration and stops when it has found a solution (see Figure 4.6).
   Nhấn OK, Excel bắt đầu sự lặp lại và dừng lại khi nó đã tìm thấy một lời giải (xem hình 4.6).
   
   

   
   Figure 4.6 (hình 4.6)​

TIP: If you want to watch the progress of the iteration, activate the Manual check box in the Calculation options tab, and enter 1 in the Maximum Iterations text box. When you return to your worksheet, each time you press F9, Excel performs a single pass of the iteration.
Nếu bạn muốn xem Excel thực hiện tiến trình này như thế nào, bạn kích hoạt tùy chọn Manual trong khung Calculation options (khi bạn làm xong bước 6 ở trên và trước khi nhấn OK), và nhập số 1 trong khung Maximum Iterations. Khi bạn quay về bảng tính, mỗi khi bạn nhấn F9, Excel sẽ thực hiện một bước của sự lặp lại.​

File ví dụ trong bài này: Iteration.xlsx​

 

[BNTT]

Chapter 4 - CREATING ADVANCED FORMULAS


4.3. Consolidating Multisheet Data
Tổng hợp dữ liệu trên nhiều bảng tính

Many businesses create worksheets for a specific task and then distribute them to various departments. The most common example is budgeting. Accounting might create a generic “budget” template that each department or division in the company must fill out and return. Similarly, you often see worksheets distributed for inventory requirements, sales forecasting, survey data, experimental results, and more.
Nhiều doanh nghiệp tạo các bảng tính cho một công việc cụ thể rồi sau đó phân phối chúng cho những bộ phận khác nhau. Phổ biến nhất là việc lập ngân sách. Bộ phận kế toán tạo ra một khuôn mẫu ngân sách chung, và mỗi bộ phận trong công ty phải điền vào đó, xong gửi về lại. Những loại bảng tính tương tự như thế này mà bạn thường thấy là những bảng tính về việc kiểm kê, dự đoán doanh số, khảo sát dữ liệu, kết quả thử nghiệm, v.v...

Creating these worksheets, distributing them, and filling them in are all straightforward operations. The tricky part, however, comes when the sheets are returned to the originating department, and all the new data must be combined into a summary report showing company-wide totals. This task is called consolidating the data, and it’s often no picnic, especially for large worksheets. However, as you’ll soon see, Excel has some powerful features that can take the drudgery out of consolidation.
Tạo những bảng tính như vậy, phân phối chúng, và điền vào chúng thì chỉ là những việc đơn giản. Phần khó là khi các bảng tính này được trả về bộ phận ban đầu, và tất cả các dữ liệu mới sẽ được tổng hợp thành một báo cáo, trình bày tổng con số của toàn công ty. Tác vụ này được gọi là tổng hợp dữ liệu và thường không phải là chuyện dễ dàng, nhất là các bảng tính lớn. Tuy nhiên, như bạn sẽ thấy sau đây, Excel có một số tính năng mạnh mẽ nhằm giúp bạn thực hiện công việc khó khăn này.

Excel can consolidate your data using one of the following two methods:
Excel có thể tổng hợp dữ liệu bằng cách sử dụng một trong những phương pháp sau đây:

• Consolidating by position — With this method, Excel consolidates the data from several worksheets using the same range co-ordinates on each sheet. You would use this method if the worksheets you’re consolidating have an identical layout.
  Tổng hợp theo vị trí — Với phương pháp này, Excel tổng hợp dữ liệu từ nhiều bảng tính bằng cách sử dụng các tọa độ dãy giống nhau trên mỗi bảng tính. Bạn sử dụng phương pháp này nếu các bảng tính mà bạn tổng hợp có cùng một một cách trình bày.

• Consolidating by category — This method tells Excel to consolidate the data by looking for identical row and column labels in each sheet. For example, if one worksheet lists monthly Gizmo sales in row 1 and another lists monthly Gizmo sales in row 5, you can still consolidate as long as both sheets have a “Gizmo” label at the beginning of these rows.
  Tổng hợp theo hạng mục — Phươn g pháp này yêu cầu Excel tổng hợp dữ liệu bằng cách tìm theo các tiêu đề hàng và cột trong mỗi bảng tính. Ví dụ, nếu có một bảng tính liệt kê doanh số Gizmo hằng tháng trong hàng 1, và một bảng tính khác liệt kê doanh số Gizmo hằng tháng trong hàng 5, bạn vẫn có thể tổng hợp dữ liệu miễn là cả hai bảng tính có một tiêu đề "Gizmo" ở đầu những hàng này.

In both cases, you specify one or more source ranges (the ranges that contain the data you want to consolidate) and a destination range (the range where the consolidated data will appear). The next couple of sections take you through the details for both consolidation methods.
Trong cả hai trường hợp, bạn xác định một hay nhiều dãy nguồn (dãy chứa dữ liệu mà bạn muốn tổng hợp) và một dãy đích (dãy mà bạn sẽ tổng hợp dữ liệu vào đó). Các bài tiếp theo đây sẽ trình bày chi tiết về cả hai phương pháp tổng hợp này.
​

 

[BNTT]

4.3. Consolidating Multisheet Data


4.3.1. Consolidating by Position
Tổng hợp theo vị trí

If the sheets you’re working with have the same layout, consolidating by position is the easiest way to go. For example, check out the three workbooks — Division I Budget, Division II Budget, and Division III Budget — shown in Figure 4.7. As you can see, each sheet uses the same row and column labels, so they’re perfect candidates for consolidation by position.
Nếu các bảng tính mà bạn sẽ dùng để tổng hợp có cùng một khuôn mẫu, việc tổng hợp theo vị trí là lựa chọn dễ thực hiện nhất. Ví dụ, kiểm tra 3 bảng tính — Division I Budget, Division II Budget, và Division III Budget — được minh họa trong hình 4.7. Như bạn thấy, mỗi bảng tính sử dụng các tiêu đề cột và tiêu đề hàng giống như nhau, do đó chúng hoàn toàn thích hợp để tổng hợp theo vị trí.

Figure 4.7 (Hình 4.7)​

Begin by creating a new worksheet that has the same layout as the sheets you’re consolidating. Figure 4.8 shows a new Consolidation workbook that I’ll use to consolidate the three budget sheets.
Hãy bắt đầu bằng cách tạo một bảng tính mới có cùng khuôn mẫu như các bảng tính mà bạng đang tổng hợp. Hình 4.8 minh họa một bảng tính mới có tên là Consolidation mà bạn dùng để tổng hợp 3 bảng tính ngân sách đã nói ở trên.

Figure 4.8 (Hình 4.8)​

As an example, let’s see how you’d go about consolidating the sales data in the three budget worksheets shown in Figure 4.7. We’re dealing with three source ranges:
Như là một ví dụ, bạn hãy xem mình sẽ bắt đầu tổng hợp các dữ liệu doanh số (sales) trong 3 bảng tính ngân sách minh họa ở hình 4.7 như thế nào. Bạn bắt đầu làm việc với 3 dãy nguồn:

'[Division I Budget]Details' !B4:M6
'[Division II Budget]Details' !B4:M6
'[Division III Budget]Details' !B4:M6​

With the consolidation sheet active, follow these steps to consolidate by position:
Bạn kích hoạt bảng tính tổng hợp (Consolidate.xlsx) rồi làm theo các bước sau đây để tổng hợp theo vị trí:

1. Select the upper-left corner of the destination range. In the Consolidate By Position worksheet, the cell to select is B4.
   Chọn ô trên cùng bên trái của dãy đích. Trong trang tính Consolidate By Position, bạn chọn ô B4.
   
   
2. Choose Data, Consolidate. Excel displays the Consolidate dialog box.
   Chọn Data, Consolidate. Excel sẽ mở hộp thoại Consolidate.
   
   
3. In the Function drop-down list, click the operation to use during the consolidation. You’ll use Sum most of the time, but Excel has 10 other operations to choose from, including Count, Average, Max, and Min.
   Trong danh sách xổ xuống Function, bạn chọn phép tính sẽ dùng cho việc tổng hợp. Thường thì bạn sẽ dùng hàm Sum (tính tổng), nhưng Excel có 10 phép tính khác nữa cho bạn chọn, bao gồm Count (đếm), Average (trung bình), Max (lớn nhất), Min (nhỏ nhất).
   
   
4. In the Reference text box, enter a reference for one of the source ranges. Use one of the following methods:
   Trong khung Reference, bạn nhập tham chiếu cho một trong các dãy nguồn. Sử dụng một trong các phương pháp sau đây:
   
   
   • Type the range coordinates by hand. If the source range is in another workbook, be sure to include the workbook name enclosed in square brackets. If the workbook is in a different drive or folder, include the full path to the workbook as well.
     Nhập bằng tay tọa độ của dãy. Nếu dãy nguồn ở trong một bảng tính khác, bạn phải nhập cả tên bảng tính ở trong một cặp dấu ngoặc vuông. Nếu bảng tính nằm trong một ổ đĩa khác hoặc một thư mục khác, phải bao gồm đường dẫn đầy đủ cho bảng tính đó.
     
     
   • If the sheet is open, activate it (either by clicking it or by clicking it in the View, Switch Windows menu), and then use your mouse to highlight the range.
     Nếu trang tính (nguồn) đang mở, bạn kích hoạt nó (bằng cách nhấn vào nó hoặc nhấn vào tab View, chọn menu Switch Windows), và rồi dùng chuột để chọn dãy nguồn.
     
     
   • If the workbook isn’t open, choose Browse, select the file in the Browse dialog box, and then click OK. Excel adds the workbook path to the Reference box. Fill in the sheet name and the range coordinates.
     Nếu bảng tính (nguồn) chưa mở sẵn, chọn Browse, chọn file trong hộp thoại Browse, và nhấn OK. Excel sẽ tự động thêm đường dẫn đầy đủ vào trong khung Reference. Bạn điền thêm tên trang tính (sheet) và tọa độ dãy nguồn.
   
   
5. Click Add. Excel adds the range to the All References box (see Figure 4.9).
   Nhấn Add, Excel sẽ thêm dãy nguồn vào trong khung All References (xem hình 4.9)
   
   

   
   Figure 4.9 (Hình 4.9)​
   
   
6. Repeat steps 4 and 5 to add all the source ranges.
   Lập lại bước 4 và bước 5 cho tất cả các dãy nguồn.
   
   
7. If you want the consolidated data to change whenever you make changes to the source data, leave the Create Links to Source Data check box activated.
   Nếu bạn muốn số liệu tổng hợp sẽ tự cập nhật khi bạn thay đổi dữ liệu gốc, bạn để nguyên tùy chọn Create Links to Source Data ở trạng thái được kích hoạt.
   
   
8. Click OK. Excel gathers the data, consolidates it, and then adds it to the destination range (see Figure 4.10).
   Nhấn OK. Excel thu thập dữ liệu, tổng hợp chúng, và rồi thêm chúng vào trong dãy đích (xem hình 4.10).
   
   

   
   Figure 4.10 (Hình 4.10)​

Các file mẫu dùng trong bài này: Division I Budget.xlsx | Division II Budget.xlsx | Division III Budget.xlsx | Consolidation.xlsx​

 

[BNTT]

4.3. Consolidating Multisheet Data


4.3.1. Consolidating by Position (continued)
Tổng hợp theo vị trí (tiếp theo bài trước)

If you chose not to create links to the source data in step 7, Excel just fills the destination range with the consolidation totals. If you did create links, however, Excel does three things:
Nếu bạn chọn không tạo ra liên kết với dữ liệu nguồn ở bước 7, Excel chỉ điền vào dãy đích những dữ liệu đã tổng hợp. Nhưng nếu bạn chọn tạo liên kết với dữ liệu nguồn, Excel sẽ làm 3 điều sau đây:

• Adds link formulas to the destination range for each cell in the source ranges you selected (➔ see "Working with Links in Formulas")
  Thêm các công thức liên kết với dãy đích vào mỗi ô trong các dãy nguồn mà bạn đã chọn (xem lại bài "Làm việc với các liên kết trong công thức")

• Consolidates the data by adding SUM() functions (or whatever operation you selected in the Function list) that total the results of the link formulas.
  Tổng hợp dữ liệu bằng cách thêm các hàm SUM() (hoặc là phép tính nào mà bạn đã chọn trong danh sách Function) để tính tổng các kết quả của các công thức liên kết.

• Outlines the consolidation worksheet and hides the link formulas, as you can see in Figure 4.10.
  Bao quanh các bảng tính tổng hợp vào ẩn đi những công thức liên kết, như bạn đã thấy ở hình 4.10.

If you display the Level 1 data, you’ll see the linked formulas. For example, Figure 4.11 shows the detail for the consolidated sales number for Books in January (cell B7). The detail in cells B4, B5, and B6 contain formulas that link to the corresponding cells in the three budget worksheets (for example, ='D:\GPE\[Division I Budget.xlsx]Details'!$B$4).
Nếu bạn hiển thị dữ liệu ở cấp độ 1 (Level 1), bạn sẽ thấy được những công thức liên kết. Ví dụ, hình 4.11 minh họa chi tiết của doanh số tổng hợp cho Books của January (ô B7). Chi tiết trong các ô B4, B5, và B6 chứa các công thức liên kết đến các ô tương ứng trong 3 bảng tính ngân sách (ví dụ, ='D:\GPE\[Division I Budget.xlsx]Details'!$B$4)

Figure 4.11 (hình 4.11)​

​

 

[BNTT]

4.3. Consolidating Multisheet Data


4.3.2. Consolidating by Category
Tổng hợp theo hạng mục

If your worksheets don’t use the same layout, you need to tell Excel to consolidate the data by category. In this case, Excel examines each of your source ranges and consolidates data that uses the same row or column labels. For example, take a look at the Sales rows in the three worksheets shown in Figure 4.12.
Nếu các bảng tính mà bạn sẽ dùng để tổng hợp không có cùng một khuôn mẫu, bạn cần sử dụng phương pháp tổng hợp theo hạng mục. Trong trường hợp này, Excel sẽ kiểm tra từng dãy nguồn và tổng hợp dữ liệu trong những cột và những hàng có cùng chung tiêu đề. Ví dụ, ở hình 4.12, bạn hãy xem kỹ những hàng trong nhóm Sales của 3 bảng tính.

Figure 4.12 (Hình 4.12)​

As you can see, Division C sells books, software, videos, and CD-ROMs; Division B sells books and CD-ROMs; and Division A sells software, books, and videos. Here’s how you go about consolidating these numbers:
Như bạn thấy, Division C thì bán books, software, videos và CD-ROMs, Division B thì bán books và CD-ROMs, và Division A thì bán software, books, và videos. Sau đây là cách mà bạn tổng hợp những con số đó:

1. Create or select a new worksheet for the consolidation, and select the upper-left corner of the destination range. It isn’t necessary to enter labels for the consolidated data because Excel does it for you automatically. However, if you want to see the labels in a particular order, it’s okay to enter them yourself. (Just make sure, however, that you spell the labels exactly as they’re spelled in the source worksheets.)
   Tạo mới hoặc chọn một bảng tính mới để tổng hợp, và chọn góc trái phía trên của dãy đích. Bạn không cần phải nhập các tiêu đề (hàng hay cột) cho các dữ liệu sẽ tổng hợp bởi vì Excel sẽ tự làm điều đó cho bạn. Tuy nhiên, nếu bạn thích sắp xếp các tiêu đề theo một thứ tự cụ thể, bạn có thể tự nhập chúng. (Tuy nhiên, phải bảo đảm rằng bạn nhập chính xác tên các tiêu đề như chúng đã có ở trong các dữ liệu nguồn).
   
   
2. Choose Data, Consolidate. Excel displays the Consolidate dialog box.
   Chọn Data, Consolidate. Excel sẽ mở hộp thoại Consolidate.
   
   
3. In the Function drop-down list, click the operation to use during the consolidation.
   Trong danh sách xổ xuống Function, bạn chọn phép tính sẽ dùng cho việc tổng hợp.
   
   
4. In the Reference text box, enter a reference for one of the source ranges. In this case, make sure that you include in each range the row and column labels for the data.
   Trong khung Reference, bạn nhập tham chiếu cho một trong các dãy nguồn. Với trường hợp này, hãy chắc chắn là bạn có bao gồm cả các tiêu đề hàng và cột của dữ liệu.
   
   
5. Click Add to add the range to the All References box.
   Nhấn Add để thêm dãy nguồn vào trong khung All References.
   
   
6. Repeat steps 4 and 5 to add all the source ranges.
   Lập lại bước 4 và bước 5 cho tất cả các dãy nguồn.
   
   
7. If you want the consolidated data to change whenever you make changes to the source data, leave the Create Links to Source Data check box activated.
   Nếu bạn muốn số liệu tổng hợp sẽ tự cập nhật khi bạn thay đổi dữ liệu gốc, bạn để nguyên tùy chọn Create Links to Source Data ở trạng thái được kích hoạt.
   
   
8. If you want Excel to use the data labels in the top row of the selected ranges, activate the Top Row check box. If you want Excel to use the data labels in the left column of the source ranges, activate the Left Column check box.
   Nếu bạn muốn Excel sử dụng tiêu đề dữ liệu ở hàng trên cùng của dãy được chọn, bạn kích hoạt tùy chọn Top Row. Nếu bạn muốn Excel sử dụng tiêu đề dữ liệu ở cột bên trái của dãy được chọn, bạn kích hoạt tùy chọn Left Column. (Hoặc bạn có thể chọn cả hai)
   
   
9. Click OK. Excel gathers the data, consolidates it, and then adds it to the destination range (see Figure 4.13).
   Nhấn OK. Excel thu thập dữ liệu, tổng hợp chúng, và rồi thêm chúng vào trong dãy đích (xem hình 4.13).
   
   

   
   Figure 4.13 (Hình 4.13)​

Các file mẫu dùng trong bài này: Division A Budget.xlsx | Division B Budget.xlsx | Division C Budget.xlsx | Consolidation.xlsx​

 

[BNTT]

Chapter 4 - CREATING ADVANCED FORMULAS


4.4. Applying Data-Validation Rules to Cells
Áp dụng các quy tắc Data-Validation cho các ô

It’s an unfortunate fact of spreadsheet life that your formulas are only as good as the data they’re given. It’s the GIGO effect, as the programmers say: garbage in, garbage out. For basic data errors (for example, entering the wrong date or transposing a number’s digits), there’s not a lot you can do other than exhorting yourself or the people who use your worksheets to enter data carefully. Fortunately, you have a bit more control when it comes to preventing improper data entry. By improper, I mean data that falls in either of the following categories:
Có một điều không may khi làm việc với các bảng tính là các công thức của bạn chỉ hoạt động theo những dữ liệu mà chúng nhận được. Đó là hiệu ứng GIGO, như các nhà lập trình thường nói, garbage in, garbage out (nếu ta nạp dữ liệu vào máy sai thì ta cũng thu được kết quả sai). Đối với những lỗi dữ liệu cơ bản (ví dụ như nhập sai ngày tháng, nhập sai số), bạn không thể làm gì khác ngoài việc tự cổ vũ chính mình và những người sử dụng các bảng tính của bạn là phải nhập dữ liệu cách cẩn thận! Nhưng cũng còn chút may mắn là bạn có một số khả năng kiểm soát để ngăn chận việc nhập dữ liệu không đúng. Không đúng ở đây có nghĩa là dữ liệu rơi vào một trong hai loại sau:

• Data that is the wrong type — for example, entering a text string in a cell that requires a number.
  Dữ liệu có kiểu nhập không đúng — ví dụ, nhập một chuỗi dạng văn bản vào trong một ô đòi hỏi phải nhập vào một con số.

• Data that falls outside of an allowable range — for example, entering 200 in a cell that requires a number between 1 and 100
  Dữ liệu nằm ngoài một giới hạn cho phép — ví dụ, nhập 200 vào một ô mà ô này chỉ cho phép nhập trong khoảng từ 1 đến 100.

You can prevent these kinds of improper entries, to a certain extent, by adding comments that provide details on what is allowable inside a particular cell. However, this requires other people to both read and act on the comment text.
Bạn có ngăn chận những loại dữ liệu nhập không đúng này trong một phạm vi nhất định bằng cách thêm các chú thích nhằm cung cấp chi tiết về những gì có thể được phép nhập vào trong một ô cụ thể. Tuy nhiên điều này đòi hỏi những người khác phải vừa đọc vừa làm theo những chú thích của bạn.

Another solution is to use custom numeric formatting to “format” a cell with an error message if the wrong type of data is entered. This is useful, but it works only for certain kinds of input errors.
Một cách khác là sử dụng một số kiểu định dạng số để "định dạng" một ô với một thông báo lỗi sẽ xuất hiện nếu như dữ liệu nhập vào không đúng. Điều này thì hữu dụng, nhưng chỉ có tác dụng với một số lỗi nhập liệu nhất định nào đó thôi.

The best solution for preventing data entry errors is to use Excel’s data-validation feature. With data validation, you create rules that specify exactly what kind of data can be entered and in what range that data can fall. You can also specify pop-up input messages that appear when a cell is selected, as well as error messages that appear when data is entered improperly.
Giải pháp tốt nhất để ngăn ngừa các lỗi nhập liệu là sử dụng tính năng data-validation của Excel. Với data-validation, bạn có thể tạo ra những quy tắc nhằm xác định chính xác loại dữ liệu nào có thể được nhập và những giới hạn cho loại dữ liệu đó. Bạn cũng có thể đưa ra những thông báo sẽ xuất hiện khi có một ô được chọn, hay khi nhập liệu không đúng (với những quy tắc bạn đã đưa ra).
​

 

[BNTT]

4.4. Applying Data-Validation Rules to Cells


4.4. Applying Data-Validation Rules to Cells (continued)
Áp dụng các quy tắc Data-Validation cho các ô (tiếp theo)

Follow these steps to define the settings for a data-validation rule:
Để thiết lập các quy tắc data-validation, bạn theo các bước sau:

1. Select the cell or range to which you want to apply the data validation rule.
   Chọn ô hay dãy ô mà bạn muốn áp dụng quy tắc data-validation.
   
   
2. Choose Data, Data Validation. Excel displays the Data Validation dialog box, shown in Figure 4.14.
   Chọn Data, Data Validation. Excel mở ra hộp thoại Data Validation như minh họa ở hình 4.14.
   
   

   Figure 4.14 (Hình 4.14)​
   
   
   
3. In the Settings tab, use the Allow list to click one of the following validation types:
   Trong tab Settings, sử dụng một trong các quy tắc dưới đây của danh sách Allow:
   
   
   • Any Value — Allows any value in the range. (That is, it removes any previously applied validation rule. If you’re removing an existing rule, be sure to also clear the input message, if you created one as shown in step 7.)
     Chấp nhận bất kỳ loại giá trị nào nhập vào trong dãy. (Điều này cũng có nghĩa là gỡ bỏ quy tắc đã được thiết lập trước đây. Khi bạn gỡ bỏ một quy tắc hiện có, bạn hãy nhớ xóa luôn cả thông báo lỗi, nếu bạn đã tạo ra nó như cách làm ở bước 7 dưới đây.)
     
     
   • Whole Number — Allows only whole numbers (integers). Use the Data list to choose a comparison operator (between, equal to, less than, and so on), and then enter the specific criteria. (For example, if you click the Between option, you must enter a Minimum and a Maximum value.)
     Chỉ cho phép nhập số nguyên vào dãy. Sử dụng thêm danh sách Data để chọn một loại toán tử so sánh (nằm trong khoảng, bằng, tối thiểu, v.v...), và nhập một tiêu chuẩn cụ thể. (Ví dụ, nếu bạn chọn Between (nằm trong khoảng), bạn phải nhập một giá trị Minimum và một giá trị Maximum).
     
     
   • Decimal — Allows decimal numbers or whole numbers. Use the Data list to choose a comparison operator, and then enter the specific numeric criteria.
     Chỉ cho phép nhập giá trị là số (số thập phân hoặc số nguyên). Sử dụng thêm danh sách Data để chọn một loại toán tử so sánh, và nhập một tiêu chuẩn cụ thể.
     
     
   • List — Allows only values specified in a list. Use the Source box to specify either a range on the same sheet or a range name on any sheet that contains the list of allowable values. (Precede the range or range name with an equals sign.) Alternatively, you can enter the allowable values directly into the Source box (separated by commas). If you want the user to be able to select from the allowable values using a drop-down list, leave the In-Cell Drop-Down check box activated.
     Chỉ cho phép nhập các giá trị được xác định trong danh sách kèm theo. Dùng hộp Source để xác định một dãy trong cùng một sheet hoặc một tên dãy trong bất kỳ sheet nào có chứa danh sách các giá trị được phép nhập. (Đặt một dấu bằng trước dãy hoặc tên dãy). Hoặc, bạn có thể nhập trực tiếp các giá trị này vào trong hộp Source (cách nhau bằng dấu phẩy). Nếu bạn muốn người dùng có thể chọn những giá trị này từ một danh sách xổ xuống (drop-down list), bạn kích hoạt tùy chọn In-Cell Drop-Down.
     
     
   • Date — Allows only dates. (If the user includes a time value, the entry is invalid.) Use the Data list to choose a comparison operator, and then enter the specific date criteria (such as a Start Date and an End Date).
     Chỉ cho phép nhập các giá trị ngày tháng. (Nếu người sử dụng nhập vào các giá trị thời gian, thì các giá trị này sẽ không hợp lệ). Sử dụng thêm danh sách Data để chọn một loại toán tử so sánh, và nhập một tiêu chuẩn ngày tháng cụ thể (chẳng hạn như ngày bắt đầu và ngày kết thúc).
     
     
   • Time — Allows only times. (If the user includes a date value, the entry is invalid.) Use the Data list to choose a comparison operator, and then enter the specific time criteria (such as a Start Time and an End Time).
     Chỉ cho phép nhập các giá trị thời gian. (Nếu người sử dụng nhập vào các giá trị ngày tháng, thì các giá trị này sẽ không hợp lệ). Sử dụng thêm danh sách Data để chọn một loại toán tử so sánh, và nhập một tiêu chuẩn thời gian cụ thể (chẳng hạn như thời gian bắt đầu và thời gian kết thúc).
     
     
   • Text Length — Allows only alphanumeric strings of a specified length. Use the Data list to choose a comparison operator, and then enter the specific length criteria (such as a Minimum and a Maximum length).
     Chỉ cho phép nhập các chuỗi text với chiều dài được chỉ định. Sử dụng thêm danh sách Data để chọn một loại toán tử so sánh, và nhập một tiêu chuẩn độ dài chuỗi cụ thể (chẳng hạn như độ dài tối thiểu và tối đa của chuỗi text).
     
     
   • Custom — Use this option to enter a formula that specifies the validation criteria. You can either enter the formula directly into the Formula box (be sure to precede the formula with an equals sign) or enter a reference to a cell that contains the formula. For example, if you’re restricting cell A2 and you want to be sure the entered value is not the same as what’s in cell A1, you’d enter the formula = A2<>A1.
     Dùng tùy chọn này để nhập một công thức xác định một điều kiện nào đó. Bạn có thể nhập trực tiếp công thức vào hộp Formula (phải chắc chắn rằng có dấu bằng ở trước công thức), hoặc nhập một tham chiếu dẫn tới một ô chứa công thức. Ví dụ, nếu bạn giới hạn ô A2 và bạn muốn chắc chắn rằng giá trị nhập vào sẽ luôn khác giá trị trong ô A1, bạn gõ công thức: = A2<>A1.
   
   
4. To allow blank entries, either in the cell itself or in other cells specified as part of the validation settings, leave the Ignore Blank check box activated. If you clear this check box, Excel treats blank entries as zero and applies the validation rule accordingly.
   Để bao gồm cả những giá trị rỗng, trong chính ô đó hoặc trong những ô là một phần của những thiết lập validation, bạn kích hoạt tùy chọn Ignore Blank. Nếu bạn không kích hoạt tùy chọn này, Excel sẽ xem các ô rỗng có giá trị là zero và áp dụng những quy tắc validation phù hợp với chúng.
   
   
5. If the range had an existing validation rule that also applied to other cells, you can apply the new rule to those other cells by activating the Apply These Changes to All Other Cells with the Same Settings check box.
   Nếu dãy đã có một quy tắc validation đang áp dụng cho những ô khác, bạn có thể áp dụng luôn quy tắc mới vào các ô khác đó bằng cách kích hoạt tùy chọn Apply These Changes to All Other Cells with the Same Settings.
   
   
6. Click the Input Message tab.
   Chọn tab Input Message.
   
   
7. If you want a pop-up box to appear when the user selects the restricted cell or any cell within the restricted range, leave the Show Input Message When Cell Is Selected check box activated. Use the Title and Input Message boxes to specify the message that appears. For example, you could use the message to give the user information on the type and range of allowable values.
   Nếu bạn muốn có một thông báo xuất hiện khi người dùng chọn ô được giới hạn hay bất kỳ ô nào trong dãy được giới hạn, bạn kích hoạt tùy chọn Show Input Message When Cell Is Selected. Xác định nội dung của thông báo trong khung Title and Input Message. Ví dụ, bạn có thể cung cấp cho người dùng biết thông tin về loại và khoảng giá trị được phép nhập vào.
   
   
8. Click the Error Alert tab.
   Chọn tab Error Alert.
   
   
9. If you want a dialog box to appear when the user enters invalid data, leave the Show Error Alert After Invalid Data Is Entered check box activated. In the Style list, click the error style you want: Stop, Warning, or Information. Use the Title and Error Message boxes to specify the message that appears.
   Nếu bạn muốn có một hộp thoại xuất hiện khi người dùng nhập những dữ liệu không hợp lệ, kích hoạt tùy chọn Show Error Alert After Invalid Data Is Entered. Trong danh sách Style, chọn kiểu báo lỗi mà bạn muốn xuất hiện: Stop, Warning, hoặc Information. Xác định nội dung của thông báo trong khung Title and Input Message.
   
   CAUTION: Only the Stop style can prevent the user from ignoring the error and entering the invalid data anyway.
   Chú ý: Chỉ có loại Stop mới có thể ngăn cản người dùng cố tình bỏ qua lỗi và nhập dữ liệu không hợp lệ bằng mọi giá. ​
   
   
10. Click OK to apply the data validation rule.
    Nhấn OK để áp dụng quy tắc Data-validation.

 

[BNTT]

Chapter 4 - CREATING ADVANCED FORMULAS


4.5. Using Dialog Box Controls on a Worksheet
Sử dụng các nút điều khiển trong một bảng tính

In the previous section, you saw how choosing List for the type of validation enabled you to supply yourself or the user with an in-cell drop-down list of allowable choices. This is good data-entry practice because it reduces the uncertainly about the allowable values.
Trong phần trước, bạn đã thấy việc chọn List trong các loại quy tắc Validation đã cung cấp cho chính bạn hoặc cho người sử dụng chọn ra một giá trị hợp lệ trong một danh sách xổ xuống. Nếu như bạn không chắc chắn lắm giá trị nào mới là hợp lệ, để nhập vào, thì đây mà một phương pháp nhập liệu tốt.

One of Excel’s slickest features is that it enables you to extend this idea and place not only lists, but other dialog box controls such as spinners and check boxes, directly on a worksheet. You can then link the values returned by these controls to a cell to create an elegant method for entering data.
Một trong những tính năng hay nhất của Excel là nó cho phép bạn mở rộng ý tưởng này, và đặt không chỉ các danh sách mà còn có thêm các công cụ điều khiển (dialog box control) khác chẳng hạn như các spinner (cái nút nhỏ làm tăng hoặc giảm giá trị lên xuống từng nấc một) hay là các check box (cái nút vuông để đánh dấu các lựa chọn) trực tiếp ngay trên bảng tính. Sau đó, bạn có thể liên kết các giá trị được trả về từ những cái nút điều khiển này đến một ô, để tạo ra một phương pháp nhập liệu khá tốt. ​

4.5.1. Using the Form Controls
Sử dụng Form Controls

Before you can work with dialog box controls, you need to display the Ribbon’s Developer tab:
Trước khi bạn có thể làm việc với các công cụ điều khiển, bạn cần hiển thị tab Develope của thanh Ribbon (tab này, theo mặc định khi cài Excel 2007 vào máy, sẽ không hiển thị ra):

1. Choose Office, Excel Options to open the Excel Options dialog box.
   Chọn Office, Excel Options để mở hộp thoại Excel Options.
   
   
2. Click Popular.
   Chọn Popular.
   
   
3. Click to activate the Show Developer Tab in the Ribbon check box.
   Nhấn chuột để kích hoạt tùy chọn Show Developer Tab in the Ribbon.
   
   
4. Click OK.
   Nhấn OK.

You add the dialog box controls by choosing Developer, Insert and then selecting tools from the Form Controls list, shown in Figure 4.15. Note that only some of the controls are available for worksheet duty. I’ll discuss the controls in detail a bit later in this section.
Bạn thêm vào bảng tính các công cụ điều khiển bằng cách chọn tab Develope, Insert, và rồi chọn các công cụ từ bảng Form Controls như minh họa ở hình 4.15. Chỉ có một số công cụ là có sẵn cho công việc với bảng tính. Tôi sẽ nói chi tiết về các công cụ điều khiển khác trong phần sau.

Figure 4.15 (hình 4.15)​

 

[BNTT]

4.5. Using Dialog Box Controls on a Worksheet


4.5.2. Adding a Control to a Worksheet
Thêm công cụ điều khiển vào một bảng tính

You add controls to a worksheet using the same steps you use to create any graphic object. Here’s the basic procedure:
Bạn thêm các công cụ điều khiển vào trong một bảng tính theo các bước tương tự như cách mà bạn thường sử dụng để tạo các đối tượng đồ họa. Đây là các thủ tục cơ bản:

1. Choose Developer, Insert and then click the form control you want to create. The mouse pointer changes to a crosshair.
   Chọn Developer, Insert và nhấp vào công cụ mà bạn muốn tạo. Khi đó con trỏ chuột sẽ có dạng là dấu chữ thập.
   
   
2. Move the pointer onto the worksheet at the point where you want the control to appear.
   Di chuyển con trỏ trên bảng tính đến điểm mà bạn muốn xuất hiện công cụ điều khiển.
   
   
3. Click and drag the mouse pointer to create the control.
   Nhấn và rê chuột để tạo ra công cụ điều khiển.

Excel assigns a default caption to group boxes, check boxes, and option buttons. To edit this caption, you have two ways to get started:
Theo mặc định, Excel gán một chú thích vào các Group Boxes (hộp nhóm), Check Boxes (hộp kiểm), và Option Buttons (nút tùy chọn). Để sửa lại nội dung các chú thích này, bạn có hai cách để bắt đầu:

• Right-click the control and choose Edit Text.
  Nhấp phải chuột vào công cụ điều khiển và chọn Edit Text.
  
  
• Hold down Ctrl and click the control to select it. Then click inside the control.
  Nhấn giữ phím Ctrl và nhấp chuột vào công cụ điều khiển để chọn nó. Rồi nhấn thêm một cái vào bên trong công cụ điều khiển.

Edit the text accordingly; when you’re done, click outside the control.
Sửa lại nội dung (chú thích) cho phù hợp. Khi đã xong, nhấp chuột ra bên ngoài công cụ điều khiển.​

 

[BNTT]

4.5. Using Dialog Box Controls on a Worksheet


4.5.3. Linking a Control to a Cell Value
Liên kết một công cụ điều khiển với giá trị trong một ô

To use the dialog box controls for inputting data, you need to associate each control with a worksheet cell. The following procedure shows you how it’s done:
Để sử dụng các công cụ điều khiển cho việc nhập dữ liệu, bạn cần phải kết hợp mỗi công cụ điều khiển với một ô trong bảng tính. Thủ tục sau đây hướng dẫn cho bạn cách thực hiện điều này:

1. Select the control you want to work with. (Again, remember to hold down the Ctrl key before you click the control.)
   Chọn công cụ điều khiển mà bạn muốn làm việc với nó. (Nhắc lại, để chọn một công cụ điều khiển, bạn hãy nhớ nhấn và giữ phím Ctrl trước khi bạn nhấp chuột vào nó).
   
   
2. Right-click the control and then click Format Control (or press Ctrl+1) to display the Format Control dialog box.
   Nhấp phải chuột vào công cụ và chọn Format Control (hay là nhấn Ctrl+1) để hiển thị hộp thoại Format Control.
   
   
3. Click the Control tab and then use the Cell Link box to enter the cell’s reference. You can either type the reference or select it directly on the worksheet.
   Chọn tab Control và nhập tham chiếu ô vào trong khung Cell Link. Bạn có thể nhập tham chiếu này bằng tay hoặc chọn nó trực tiếp trên bảng tính.
   
   
4. 4. Choose OK to return to the worksheet.
   Nhấn OK để quay về bảng tính.

TIP: Another way to link a control to a cell is to select the control and enter a formula in the formula bar of the form = cell. Here, cell is a reference to the cell you want to use. For example, to link a control to cell A1, you enter the formula = A1.
Một cách khác để tạo liên kết cho một công cụ điều khiển tới một ô là chọn công cụ đó và nhập một công thức ở trên thanh formula với dạng = cell. Ở đây, cell là tham chiếu đến ô mà bạn muốn liên kết với công cụ đó. Ví dụ, để liên kết một công cụ điều khiển với ô A1, bạn nhập công thức = A1.​

 

[BNTT]

4.5. Using Dialog Box Controls on a Worksheet


4.5.4. Understanding the Worksheet Controls
Tìm hiểu các công cụ điều khiển bảng tính

To get the most out of worksheet controls, you need to know the specifics of how each control works and how you can use each one for data entry. To that end, the next few sections take you through detailed accounts of each control.
Để tận dụng tối đa khả năng của các công cụ điều khiển bảng tính, bạn cần biết những điểm riêng biệt về cách hoạt động của mỗi công cụ và cách bạn có thể sử dụng mỗi công cụ cho việc nhập dữ liệu. Các mục tiếp theo đây sẽ trình bày chi tiết mỗi công cụ điều khiển.​

■ Group Boxes

Group boxes don’t do much on their own. Instead, you use them to create a grouping of two or more option buttons. The user can then select only one option from the group. For this to work, you must proceed as follows:
Bản thân các Group Box (hộp nhóm) không làm được gì nhiều. Thay vào đó, bạn sử dụng chúng để tạo một nhóm có hai hoặc nhiều nút tùy chọn (option button). Sau đó người dùng chỉ chọn được một tùy chọn trong nhóm. Để tạo các Group Box, bạn theo các bước sau đây:

1. Choose Developer, Insert, Group Box in the Form Controls list.
   Chọn Developer, Insert, Group Box trong danh sách Form Controls.
   
   
2. Click and drag to draw the group box on the worksheet.
   Nhấn và rê chuột để vẽ một Group Box trong bảng tính.
   
   
3. Choose Developer, Insert, Option Button in the Form Controls list.
   Chọn Developer, Insert, Option Button trong danh sách Form Controls.
   
   
4. Click and drag within the group box to create an option button.
   Nhấn và rê chuột bên trong Group Box để tạo một Option Button.
   
   
5. Repeat steps 3 and 4 as often as needed to create the other option buttons.
   Lập lại bước 3 và 4 để tạo thêm những Option Button khác.

Remember, it’s important that you create the group box first and then draw your option buttons within the group box.
Luôn nhớ rằng, điều quan trọng là tạo Group Box trước, và rồi vẽ những cái Option Button của bạn ở bên trong Group Box.

NOTE: If you have one (and only one) option button outside of a grouping, you can still include it in a group box. (If you have multiple option buttons outside of a group box, this technique won’t work.) To do this, first hold down Ctrl and click the option button to select it. Release Ctrl, click and drag an edge of the option button, and then drop it within the group box.
Nếu bạn có một (và chỉ một) cái Option Button nằm ngoài một nhóm, bạn vẫn có thể bao gồm nó vào trong một cái Group Box. (Nếu bạn có nhiều cái Option Button nằm ngoài một Group Box, thì không thể làm được). Để làm điều này, đầu tiên bạn nhấn giữ phím Ctrl và nhấn vào cái Option Button để chọn nó. Rồi thả phím Ctrl ra, nhấn và rê một mép của cái Option Button vào bên trong Group Box.​

 

[BNTT]

4.5.4. Understanding the Worksheet Controls


■ Option Button

Option buttons are controls that usually appear in groups of two or more, and the user can activate only one of the options. As I said in the previous section, option buttons work in tandem with group boxes, in which the user can activate only one of the option buttons within a group box.
Option button (các nút tùy chọn) là những công cụ điều khiển thường xuất hiện với một nhóm gồm 2 hoặc nhiều cái, và người sử dụng chỉ có thể kích hoạt được 1 cái trong nhóm đó (nghĩa là chỉ được phép chọn một tùy chọn trong nhiều tùy chọn). Như tôi đã nói trong phần trước, nút tùy chọn làm việc cùng với Group Box và người dùng chỉ có thể kích hoạt được một tùy chọn trong Group Box mà thôi.

By default, Excel draws each option button in the unchecked state. Therefore, you should specify in advance which of the option buttons is checked:
Theo mặc định, Excel vẽ mỗi cái nút tùy chọn trong trạng thái không được chọn (unchecked). Do đó, bạn nên xác định trước nút nào trong số các nút tùy chọn là nút được chọn sẵn (checked):

1. Hold down Ctrl and click the option button you want to display as checked.
   Nhấn giữ phím Ctrl và nhấp chuột vào nút tùy chọn mà bạn muốn hiển thị nó trong trạng thái được chọn.
   
   
2. Right-click the control and then click Format Control (or press Ctrl+1) to display the Format Control dialog box.
   Nhấn phải chuột vào nút này và chọn Format Control (hoặc nhấn Ctrl+1) để hiển thị hộp thoại Format Control.
   
   
3. In the Control tab, activate the Checked option.
   Trong tab Control, kích hoạt tùy chọn Checked.
   
   
4. Click OK.
   Nhấn OK.

On the worksheet, activating a particular option button changes the value stored in the linked cell. The value stored depends on the option button, where the first button added to the group box has the value 1, the second button has the value 2, and so on. The advantage of this is that it enables you to translate a text option into a numeric value.
Trong bảng tính, việc kích hoạt một nút tùy chọn cụ thể sẽ thay đổi giá trị được lưu trữ trong ô liên kết. Giá trị được lưu trữ phụ thuộc vào nút tùy chọn, cụ thể là nút đầu tiên sẽ có giá trị là 1, nút thứ hai có giá trị là 2, v.v... Ưu điểm của điều này là cho phép bạn chuyển đổi một tùy chọn ở dạng text thành một giá trị số.

For example, Figure 4.16 shows a worksheet in which the option buttons give the user three freight choices: Surface Mail, Air Mail, and Courier. The value of the chosen option is stored in the linked cell, which is E4. For example, if Air Mail is selected, the value 2 is stored in E4.
Ví dụ, hình 4.16 minh họa một bảng tính có các nút tùy chọn cho người dùng lựa chọn 3 hình thức vận chuyển: Surface Mail, Air Mail, và Courier. Giá trị của tùy chọn được lưu trữ trong ô được liên kết, là ô E4. Ví dụ, nếu Air Mail được chọn, thì ô E4 sẽ có giá trị là 2.

Figure 4.16 (hình 4.16)​

Download file ví dụ: Worksheet Dialog Box Controls.xlsx​

 

[BNTT]

4.5.4. Understanding the Worksheet Controls


■ Check Box

Check boxes enable you to include options that the user can toggle on or off. As with option buttons, Excel draws each check box in the unchecked state. If you prefer that a particular check box start in the checked state, use the Format Control dialog box to activate the control’s Checked option, as described in the previous section.
Các Check Box (hộp kiểm) cho phép bạn đưa ra các tùy chọn mà người dùng có thể bật hoặc tắt (chọn hay không chọn). Giống như các Option Button, Excel vẽ các Check Box với trạng thái mặc định là không được chọn. Nếu bạn muốn một Check Box cụ thể nào đó xuất hiện với trạng thái ban đầu là được chọn, bạn dùng hộp thoại Format Control để kích hoạt tùy chọn Checked như đã nói trong bài trước.

On the worksheet, an activated check box stores the value TRUE in its linked cell; if the check box is cleared, it stores the value FALSE (see Figure 4.17). This is handy because it enables you to add a bit of logic to your formulas. That is, you can test whether a check box is activated and adjust a formula accordingly. Figure 4.17 shows a couple of examples:
Trên bảng tính, một Check Box được chọn sẽ lưu giữ giá trị TRUE trong ô liên kết với nó; nếu Check Box bị xóa (không được chọn) thì nó lưu giữ giá trị FALSE (xem hình 4.17). Điều này cho phép bạn thêm một chút logic vào trong các công thức của mình. Nghĩa là, bạn có thể thử xem Check Box có được kích hoạt hay không và điều chỉnh công thức cho phù hợp. Hình 4.17 minh họa hai ví dụ:

Figure 4.17 (hình 4.17)​

• Use End-Of-Period Payments — This check box could be used to determine whether a formula that determines the monthly payments on a loan assumes that those payments are made at the end of each period (TRUE) or at the beginning of each period (FALSE).
  Check Box này có thể được dùng để quyết định xem trong công thức tính các khoản thanh toán hằng tháng cho một khoản vay, thì những khoản thanh toán đó được thực hiện ở cuối mỗi kỳ hạn (TRUE) hay ở đầu mỗi kỳ hạn (FALSE).
  
  (BNTT: Nói thêm, khi dùng loại công thức tính các khoản thanh toán hằng tháng cho một khoản vay thì thường có một đối số là type , dùng để xác định tính khoản thanh toán ở cuối hay ở đầu mỗi kỳ hạn. Dùng cái Check Box như ví dụ này sẽ cho bạn thử xem thanh toán ở đầu kỳ hạn và thanh toán ở cuối kỳ hạn sẽ khác nhau như thế nào một cách nhanh chóng, mà không cần phải chọn công thức để sửa đối số type này — Xem thêm: Các Hàm Tài Chính trong Excel).
  
  
• Include Extra Monthly Payments — This check box could be used to determine whether a model that builds a loan amortization schedule formula includes an extra principal repayment each month.
  Check Box này có thể dùng để xác định xem khi lập công thức để tính toán một kế hoạch trả nợ, có bao gồm khoản thanh toán lãi + gốc hằng tháng hay không.

In both cases, and in most formulas that take check box results into account, you would use the IF() worksheet function to read the current value of the linked cell and branch accordingly.
Trong cả hai trường hợp, và trong hầu hết các công thức có dùng kết quả của Check Box (ở ô liên kết), bạn nên sử dụng hàm IF() để đọc giá trị hiện hành ở ô liên kết (với Check Box) và phân nhánh (chọn đối số trong hàm IF) cho phù hợp. ​