Formulas & Functions Excel 2007 - 8. Working with Logical and Information Functions (2 người xem)

  • Thread starter Thread starter BNTT
  • Ngày gửi Ngày gửi
Liên hệ QC

Người dùng đang xem chủ đề này

Đang trực tuyến: 2 (Thành viên: 0, Khách: 2)
BNTT

BNTT

Bùi Nguyễn Triệu Tường
Thành viên danh dự
Tham gia
3/7/07
Bài viết
4,946
Được thích
23,212
Nghề nghiệp
Dạy đàn piano
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
---------------------------------------------------------------------------------------



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 8 - WORKING WITH LOGICAL AND INFORMATION FUNCTIONS
Chương 8 - Làm việc với các hàm luận lý và tra cứu thông tin
I mentioned in Chapter 6, “Understanding Functions,” that one of the advantages to using Excel’s worksheet functions is that they enable you to build formulas that perform actions that are simply not possible with the standard operators and operands.
Ở chương 6, "Tìm hiểu các hàm", tôi đã đề cập đến một trong những lợi ích của việc sử dụng các hàm trong Excel là chúng ta có thể sử dụng những công thức để thực hiện những phép tính mà với những toán tử và toán hạng cơ bản thì không thể làm được.

This idea becomes readily apparent when you learn about those functions that can add to your worksheet models the two cornerstones of good business analysis — intelligence and knowledge. You get these via Excel’s logical and information functions, which I describe in detail in this chapter.
Ý tưởng này trở nên rõ ràng hơn khi bạn học về những hàm mà có thể thêm vào trong những mô hình bảng tính của bạn hai nền tảng phân tích kinh doanh tốt — là trí tuệ và kiến thức. Bạn có được những điều này thông qua các hàm luận lý (logical function) và tra cứu thông tin (information function) của Excel, mà tôi sẽ trình bày chi tiết trong chương này.
​


IN THIS CHAPTER
Trong chương này:
  1. Adding Intelligence with Logical Functions
    Thêm trí tuệ với các hàm luận lý

  2. Building an Accounts Receivable Aging Worksheet
    Tạo một bảng tính về các khoản phải thu ngắn hạn

  3. Getting Data with Information Functions
    Thu thập dữ liệu với các hàm tra cứu thông tin

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:
Nhấp chuột vào đây để mở rộng...


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 luận lý và topic Các hàm tra cứu thông tin, nên tôi sẽ không trình bày lại cú pháp và chú giải các đối số của hàm nữa (không theo như nguyên bản cuốn sách này). Trong các bài dịch tiếp theo đâ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.
 
Lần chỉnh sửa cuối:
Chapter 8 - WORKING WITH LOGICAL AND INFORMATION FUNCTIONS


8.1. Adding Intelligence with Logical Functions
Thêm trí tuệ với các hàm luận lý
In the computer world, we very loosely define something as intelligent if it can perform tests on its environment and act in accordance with the results of those tests. However, computers are binary beasts, so “acting in accordance with the results of a test” means that the machine can do only one of two things. Still, even with this limited range of options, you’ll be amazed at how much intelligence you can bring to your worksheets. Your formulas will actually be able to test the values in cells and ranges, and then return results based on those tests.
Trong thế giới máy tính, chúng ta định nghĩa rất thoải mái một thứ gì đó là trí tuệ nếu nó có thể thực hiện các cuộc thử nghiệm trong môi trường của nó và hành động phù hợp với kết quả của những cuộc thử nghiệm đó. Tuy nhiên, các máy tính là những con số nhị phân, do đó "hành động phù hợp với kết quả của một cuộc thử nghiệm" nghĩa là máy tính có thể thực hiện chỉ một trong hai chiều. Song ngay cả với phạm vị tùy chọn giới hạn này, bạn sẽ ngạc nhiên với trí tuệ mà bạn có thể đem vào các bảng tính của mình. Các công thức của bạn sẽ thật sự có thể thử nghiệm các giá trị trong các ô và các dãy, và trả về những kết quả dựa trên các phép thử đó.

This is all done with Excel’s logical functions, which are designed to create decision-making formulas. For example, you can test cell contents to see whether they’re numbers or labels, or you can test formula results for errors. Table 8.1 summarizes Excel’s logical functions.
Tất cả những điều này được thực hiện bằng các hàm luận lý của Excel, những hàm được thiết kế để tạo ra những công thức thực hiện các quyết định. Ví dụ, bạn có thể thử nội dung của ô xem chúng là các số hay là các nhãn, hoặc bạn có thể thử kết quả của một công thức xem nó có lỗi không. Bảng 8.1 tóm tắt các hàm luận lý trong Excel.


Table 8.1 - Excel’s Logical Functions
Bảng 8.1 - Các hàm luận lý trong Excel
  • AND (logical1 [, logical2, ...]) : Returns TRUE if all the arguments are true
    Trả về TRUE nếu tất cả các đối số là đúng

  • FALSE (): Returns FALSE
    Trả về giá trị FALSE

  • IF (logical_test, value_if_true [, value_if_false]) : Performs a logical test and returns a value based on the result
    Thực hiện một phép thử logic và trả về một kết quả dựa trên kết quả của phép thử

  • IFERROR (value, value_if_error) : Returns value_if_error if
    value is an error
    Trả về giá trị value_if_error nếu value có một lỗi

  • NOT (logical) : Reverses the logical value of the argument
    Đảo ngược các giá trị logic của đối số

  • OR (logical1 [, logical2, ...]) : Returns TRUE if any argument is true
    Trả về TRUE nếu có bất kỳ một đối số nào là đúng

  • TRUE (): Returns TRUE
    Trả về giá trị TRUE
 
Lần chỉnh sửa cuối:
8.1. Adding Intelligence with Logical Functions


8.1.1. Using the IF Function — Sử dụng Hàm IF()
I don’t think I’m exaggerating even the slightest when I tell you that the royal road to becoming an accomplished Excel formula builder involves mastering the IF() function. If you become comfortable wielding this function, a whole new world of formula prowess and convenience opens up to you. Yes, IF() is that powerful.
Tôi không nghĩ rằng tôi phóng đại quá mức khi tôi nói với bạn rằng con đường để trở thành một người xây dựng các công thức Excel hoàn hảo, là làm chủ được hàm IF(). Nếu bạn cảm thấy thoải mái nắm giữ được hàm này, thì tất cả những gì là sức mạnh và tiện ích của các hàm đã mở ra cho bạn. Vâng, hàm IF() là một hàm rất mạnh.

To help you master this crucial Excel feature, I’m going to spend a lot of time on it in this chapter. You’ll get copious examples that show you how to use it in real-world situations.
Để giúp bạn làm chủ được hàm IF(), tôi sẽ dành nhiều thời gian cho nó trong chương này. Bạn sẽ có nhiều ví dụ cụ thể hướng dẫn bạn cách sử dụng nó trong những tình huống thực tế.​


The Simplest Case
Trường hợp đơn giản nhất
Let’s start with the simplest version of the IF() function:
Hãy bắt đầu với phiên bản đơn giản nhất của hàm IF():

IF(logical_test, value_if_true)

For example, consider the following formula:
Ví dụ, xem công thức sau đây:

=IF(A1 >= 1000, "It’s big!")

The logical expression A1 >= 1000 is used as the test. Let’s say you add this formula to cell B1. If the logical expression proves to be true (that is, if the value in cell A1 is greater than or equal to 1,000), the function returns the string It’s big!, and that’s the value you see in cell B1. (If A1 is less than 1,000, you see the value FALSE in cell B1, instead.)
Biểu thức logic A1 >= 1000 được dùng làm phép thử. Giả sử bạn nhập công thức này vào ô B1. Nếu biểu thức logic chứng tỏ là đúng (nghĩa là giá trị ở ô A1 lớn hơn hoặc bằng 1,000), hàm trả về chuỗi It's big!, và đó là giá trị bạn thấy ở ô B1. (Và ngược lại, nếu A1 nhỏ hơn 1,000, bạn sẽ thấy giá trị FALSE trong ô B1.)

Another common use for the simple IF() test is to flag values that meet a specific condition. For example, suppose you have a worksheet that shows the percentage increase or decrease in the sales of a long list of products. It would be useful to be able to flag just those products that had a sales decrease. A basic formula for doing this would look something like this:
Một công dụng thông thường khác cho phép thử IF() là đánh dấu các giá trị đáp ứng một điều kiện riêng biệt. Ví dụ, giả sử bạn có một bảng tính minh họa việc tăng hoặc giảm phần trăm trong doanh số của danh sách sản phẩm khá dài. Khả năng chỉ đánh dấu vào các sản phẩm bị giảm doanh số là một điều hữu ích. Một công thức cơ bản để làm điều này có dạng như sau:

=IF(cell < 0, flag)

Here, cell is the cell you want to test, and flag is some sort of text that you use to point out a negative value. Here’s an example:
Ở đây, cell là ô bạn muốn thử, và flag là một vài ký tự ngắn nào đó mà bạn dùng để chỉ ra rõ một giá trị âm. Sau đây là một ví dụ:

=IF(B2 < 0, "<<<<<")

A slightly more sophisticated version of this formula would vary the flag, depending on the negative value. That is, the larger the negative number was, the more less-than signs (in this case) the formula would display. This can be done using the REPT() function discussed in Chapter 7, “Working with Text Functions”:
Một phiên bản hơn phức tạp hơn của công thức trên sẽ thay đổi ký hiệu đánh dấu, dựa vào giá trị âm. Nghĩa là, số càng âm nhiều, thì số ký hiệu nhỏ hơn (trong trường hợp này) càng nhiều. Điều này có thể thực hiện được bằng cách sử dụng hàm REPT() mà bạn đã thấy ở chương 7, "Làm việc với các hàm xứ lý chuỗi văn bản".

REPT("<", B2 * -100)

This expression multiplies the percentage value by –100 and then uses the result as the number of times the less-than sign is repeated. Here’s the revised IF() formula:
Biểu thức này nhân giá trị phần trăm cho -100 và rồi sử dụng kết quả như là số lần lặp lại ký hiệu nhỏ hơn (<). Đây là công thức IF() đã được sửa lại:

=IF(B2 < 0, REPT("<", B2 * -100))

Figure 8.1 shows how it works in practice.
Hình 8.1 minh họa công thức này làm việc như thế nào trong thực tế.

Figure81.jpg

Figure 8.1 - Logical Functions.xlsx​
 
Lần chỉnh sửa cuối:
8.1.1. Using the IF Function


Handling a FALSE Result
Xử lý một kết quả FALSE
As you can see in Figure 8.1, if the result of the IF() condition calculates to FALSE, the function returns FALSE as its result. That’s not inherently bad, but our worksheet would look tidier (and, hence, be more useful) if the formula returned, say, the null string ("") instead.
Như bạn thấy ở hình 8.1, nếu kết quả của điều kiện tính toán IF() là FALSE, hàm trả về FALSE như là chính kết quả của nó. Điều này thì không sai, nhưng bảng tính của chúng ta sẽ trông ngăn nắp hơn (và hữu dụng hơn) nếu công thức trả về một chuỗi rỗng ("") thay vì là như vậy.

To do this, you need to use the full IF() function syntax:
Để làm điều này, bạn cần sử dụng cú pháp đầy đủ của hàm IF():

IF(logical_test, value_if_true, value_if_false)

For example, consider the following formula:
Ví dụ, xem công thức sau đây:

=IF(A1 >= 1000, "It’s big!", "It’s not big!")

This time, if cell A1 contains a value that’s less than 1,000, the formula returns the string: It’s not big!.
Lần này, nếu ô A1 chứa một giá trị nhỏ hơn 1,000, công thức trả về chuỗi: It’s not big!

For the negative value flag example, use the following revised version of the formula to return no value if the cell contains a non-negative number:
Với ví dụ đánh dấu vào những ô có giá trị âm, bạn sử dụng phiên bản của công thức đã được chỉnh sửa sau đây để không trả về một giá trị nào cả nếu ô chứa một con số không âm:

=IF(B2 < 0, REPT("<", B2 * -100), "")

As you can see in Figure 8.2, the resulting worksheet looks much tidier than the first version.
Như bạn thấy ở hình 8.2, kết quả trên trang tính trông ngăn nắp hơn phiên bản đầu tiên nhiều.
Figure82.jpg

Figure 8.2 - Logical Functions.xlsx​



Avoiding Division by Zero
Tránh phép chia cho zero (0)
As you saw in Chapter 5, “Troubleshooting Formulas,” Excel displays the #DIV/0! error if a formula tries to divide a quantity by zero. To avoid this error, you can use IF() to test the divisor and ensure that it’s nonzero before performing your division.
Như bạn đã thấy ở chương 5, "Xử lý lỗi công thức", Excel hiển thị lỗi #DIV/0! nếu một công thức cố chia một số cho số không. Để tránh lỗi này, bạn có thể dùng hàm IF() để kiểm tra số chia và bảo đảm rằng nó không phải là số 0 trước khi thực hiện phép chia.
➔ To learn about the #DIV/0! error, see "#DIV/0!"
Để nghiên cứu về lỗi #DIV/0!, xem bài "#DIV/0!"​
For example, the basic equation for calculating gross margin is (Sales – Expenses)/Sales. To make sure that Sales isn’t zero, use the following formula (I’m assuming here that you have cells named Sales and Expenses that contain the appropriate values):
Ví dụ, phương trình cơ bản để tính tổng lợi nhuận là (Sales – Expenses)/Sales (lấy doanh số trừ đi chi phí rồi chia lại cho doanh số). Để chắc chắn rằng Sales không phải là số không, dùng công thức sau đây (tôi giả sử rằng bạn đã có các ô được đặt tên là Sales và Expenses, chứa những giá trị tương ứng):

=IF(Sales <> 0, (Sales - Expenses)/Sales, "Sales are zero!")

If the logical expression Sales <> 0 is true, that means Sales is nonzero, so the gross margin calculation can proceed. If Sales <> 0 is false, the Sales value is 0, so the message Sales are zero! is displayed instead.
Nếu biểu thức logic Sales <> 0 là đúng, có nghĩa là Sales không phải là số 0, thì tiến hành phép tính tổng lợi nhuận. Nếu Sales <> 0 là sai, giá trị Sales bằng 0, thì hiển thị thông báo Sales are zero! thay vào đó.​
 
Lần chỉnh sửa cuối:
8.1. Adding Intelligence with Logical Functions


8.1.2. Performing Multiple Logical Tests
Thực hiện nhiều phép thử logic
The capability to perform a logical test on a cell is a powerful weapon, indeed. You’ll find endless uses for the basic IF() function in your everyday worksheets. The problem, however, is that the everyday world often presents us with situations that are more complicated than can be handled in a basic IF() function’s logical expression. It’s often the case that you have to test two or more conditions before you can make a decision.
Khả năng thực hiện một phép thử logic trên một ô là một "vũ khí mạnh mẽ". Bạn sẽ thấy những công dụng vô hạn của hàm IF() cơ bản trong các bảng tính hằng ngày của bạn. Tuy nhiên, vấn đề là thế giới hằng ngày thường mang đến cho chúng ta những tình huống phức tạp hơn những gì có thể xử lý trong biểu thức logic của một hàm IF() cơ bản. Điều này thường xảy ra khi bạn phải thử hai hoăc nhiều điều kiện trước khi đưa ra một quyết định.

To handle these more complex scenarios, Excel offers several techniques for performing two or more logical tests: nesting IF() functions, the AND() function, and the OR() function. You learn about these techniques over the next few sections.Để giải quyết những tình huống phức tạp hơn này, Excel cung cấp cho bạn một số kỹ thuật để thực hiện hai hoặc nhiều phép thử: những hàm IF() lồng nhau, hàm AND(), và hàm OR(). Bạn sẽ học về những kỹ thuật này qua những bài tiếp theo đây.​


Nesting IF() Functions
Những hàm IF() lồng nhau
When building models using IF(), it’s common to come upon a second fork in the road when evaluating either the value_if_true or value_if_false arguments. For example, consider the variation of our formula that outputs a description based on the value in cell A1:
Khi xây dựng những mô hình sử dụng hàm IF(), người ta thường gặp phải một nhánh thử thứ hai khi đang trên con đường lượng giá các đối số value_if_true hay value_if_false. Ví dụ, xem biến thể của công thức trả về một mô tả dựa theo giá trị trong ô A1:

=IF(A1 >= 1000, "Big!", "Not big")

What if you want to return a different string for values greater than, say, 10,000? In other words, if the condition A1 > 1000 proves to be true, you want to run another test that checks to see if A1 > 10000. You can handle this scenario by nesting a second IF() function inside the first as the value_if_true argument:
Bạn phải làm gì nếu bạn muốn trả về một chuỗi khác cho những giá trị lớn hơn 10,000 chẳng hạn? Nói cách khác, nếu điều kiện A1 > 1000 là đúng, bạn muốn thực hiện một phép thử khác để kiểm tra khi giá trị trong A1 có lớn hơn 10000 hay không. Bạn có thể giải quyết tình huống này bằng cách xếp lồng một hàm IF() với dạng là đối số value_if_true của hàm IF() thứ nhất:

=IF(A1 >= 1000, IF(A1 >= 10000, "Really big!!", "Big!"), “Not big”)

If A1 > 1000 returns TRUE, the formula evaluates the nested IF(), which returns Really big!! if A1 > 10000 is TRUE, and returns Big! if it’s FALSE; if A1 > 1000 returns FALSE, the formula returns Not big.
Nếu A1 > 1000 trả về TRUE, công thức tính tiếp hàm IF() được lồng ở trong đó, là hàm trả về Really big!! nếu A1 > 10000 cũng là TRUE, và trả về Big! nếu nó là FALSE; nếu A1 > 1000 trả về FALSE, công thức trả về Not big.

Note, too, that you can nest the IF() function in the value_if_false argument. For example, if you want to return the description Small for a cell value less than 100, you would use this version of the formula:
Lưu ý rằng, bạn cũng có thể lồng hàm IF() trong đối số value_if_false. Ví dụ, nếu bạn muốn trả về mô tả là Small cho một ô có giá trị nhỏ hơn 100, bạn dùng phiên bản này của công thức:

=IF(A1 >= 1000, "Big!", IF(A1 < 100, "Small", "Not big"))​
 
8.1.2. Performing Multiple Logical Tests


Calculating Tiered Bonuses
Tính toán mức tiền thưởng theo từng cấp
A good time to use nested IF() functions arises when you need to calculate a tiered payment or charge. That is, if a certain value is X, you want one result; if the value is Y, you want a second result; and if the value is Z, you want a third result. For example, suppose you want to calculate tiered bonuses for a sales team as follows:
Một thời điểm tốt để sử dụng các hàm IF() lồng nhau là khi bạn cần tính một khoản thanh toán hay chi phí theo từng cấp. Nghĩa là, với một giá trị là X, bạn muốn có một kết quả; với giá trị là Y, bạn muốn một kết quả thứ hai; và nếu giá trị là Z, bạn muốn có một kết quả thứ 3. Ví dụ, bạn muốn tính các mức tiền thưởng theo từng cấp cho một tổ bán hàng như sau:
  • If the salesperson did not meet the sales target, no bonus is given.
    Nếu nhân viên bán hàng không đạt được doanh số, không có tiền thưởng.

  • If the salesperson exceeded the sales target by less than 10%, a bonus of $1,000 is awarded.
    Nếu nhân viên bán hàng đạt doanh số và vượt không quá 10% doanh số, thưởng $1,000.

  • If the salesperson exceeded the sales target by 10% or more, a bonus of $10,000 is awarded.
    Nếu nhân viên bán hàng đạt doanh số và vượt doanh số từ 10% trở lên, thưởng $10,000.
Here’s a formula that handles these rules:
Sau đây là công thức để xử lý những quy tắc trên:

=IF(D2 < 0, "", IF(D2 < 0.1, 1000, 10000))

If the value in D2 is negative, nothing is returned; if the value in D2 is less than 10%, the formula returns 1000; if the value in D2 is greater than or equal to 10%, the formula returns 10000. Figure 8.3 shows this formula in action.
Nếu giá trị trong ô D2 là âm, sẽ không có gì trả về; nếu giá trị trong D2 nhỏ hơn 10%, công thức trả về 1000; nếu giá trị trong D2 lớn hơn hoặc bằng 10%, công thức trả về 10000. Hình 8.3 minh họa hoạt động của công thức này.
Figure83.jpg

Figure 8.3 - Logical Functions.xlsx​
 
8.1. Adding Intelligence with Logical Functions


8.1.3. The AND Function — Hàm AND
It’s often necessary to perform an action if and only if two conditions are true. For example, you might want to pay a salesperson a bonus if and only if dollar sales exceeded the budget and unit sales also exceeded the budget. If either the dollar sales or the unit sales fell below budget (or if they both fell below budget), no bonus is paid. In Boolean logic, this is called an And condition because one expression and another must be true for a positive result.
Chúng ta thường phải thực hiện một hành động nếu và chỉ nếu hai điều kiện là đúng. Ví dụ, bạn có thể muốn trả cho một nhân viên bán hàng tiền thưởng nếu và chỉ nếu doanh số của anh ta vượt mức đề ra và doanh số của đơn vị cũng vượt mức này; nếu doanh số của người đó hoặc doanh số của đơn vị không đạt mức đề ra (hoặc cả hai đều không đạt) thì không trả tiền thưởng. Trong biểu thức logic kiểu Boolean, đây được gọi là một điều kiện AND (VÀ), bởi vì một biểu thức và một biểu thức khác phải là đúng (true) thì mới đạt được kết quả "dương tính".

In Excel, And conditions are handled, appropriately enough, by the AND() logical function:
Trong Excel, các điều kiện AND được xứ lý một cách thích hợp bằng hàm logic AND():

AND(logical1 [, logical2, ...])

The AND() result is calculated as follows:
Kết quả của hàm AND() được tính như sau:
  • If all the arguments return TRUE (or any nonzero number), AND() returns TRUE.
    Nếu tất cả các đối số đều trả về giá trị TRUE (hoặc bất kỳ con số nào khác 0), hàm AND() trả về giá trị TRUE.

  • If one or more of the arguments return FALSE (or 0), AND() returns FALSE.
    Nếu có một hay nhiều đối số trả về FALSE (hoặc 0), hàm AND() trả về giá trị FALSE.

You can use the AND() function anywhere you would use a logical formula, but it’s most often pressed into service as the logical condition in an IF() function. In other words, if all the logical conditions in the AND() function are TRUE, IF() returns its value_if_true result; if one or more of the logical conditions in the AND() function are FALSE, IF() returns its value_if_false result. Here’s an example:
Bạn có thể dùng hàm AND() bất kỳ nơi nào bạn muốn dùng một công thức logic, nhưng nó thường được dùng làm điều kiện logic trong một hàm IF(). Nói cách khác, nếu tất cả các điều kiện logic trong hàm AND() là TRUE, hàm IF() trả về kết quả value_if_true của nó; còn nếu có một hoặc nhiều điều kiện logic trong hàm AND() là FALSE, hàm IF() trả về kết quả value_if_false. Sau đây là một ví dụ:

=IF(AND(B2 > 0, C2 > 0), "1000", "No bonus")

If the value in B2 is greater than 0 and the value in C2 is greater than 0, the formula returns 1000; otherwise, it returns No bonus.
Nếu giá trị trong ô B2 và giá trị trong ô C2 đều lớn hơn 0, công thức trả về giá trị 1000, còn không, nó trả về giá trị No Bonus.​
 
Lần chỉnh sửa cuối:
8.1.3. The AND Function


Slotting Values into Categories
Sắp xếp các giá trị vào trong từng mục
A good use for the AND() function is to slot items into categories that consist of a range of values. For example, suppose that you have a set of poll or survey results, and you want to categorize these results based on the following age ranges: 18–34, 35–49, 50–64, and 65+. Assuming that each respondent’s age is in cell B9, the following AND() function can serve as the logical test for entry into the 18–34 category:
Một công dụng của hàm AND() là sắp xếp các hạng mục (item) từ một dãy giá trị theo từng mục. Ví dụ, giả sử rằng bạn có một tập hợp các kết quả thăm dò hoặc khảo sát, và bạn muốn chia những kết quả này theo từng hạng mục dựa vào các độ tuổi như sau: 18-34, 35-49, 50-64 và trên 64 tuổi. Giả sử tuổi của mỗi người được đặt trong ô B9, hàm AND() sau đây có thể dùng làm phép thử logic để nhập vào hạng mục 18-34:

AND(B9 >= 18, B9 <= 34)

If the response is in C9, the following formula will display it if the respondent is in the 18–34 age group:
Nếu sự kết quả khảo sát nằm trong ô C9, công thức sau đây sẽ hiển thị nó nếu nó thuộc nhóm người từ 18 đến 34 tuổi:

=IF(AND(B9 >= 18, B9 <= 34), C9, "")

Figure 8.4 tries this on some data, and here are the formulas used for the other age groups:
Hình 8.4 thử công thức này trên một số dữ liệu, và dưới đây là các các công thức được sử dụng cho các nhóm tuổi khác:

35-49: =IF(AND(B9 >= 35, B9 <= 49), C9, "")
50-64: =IF(AND(B9 >= 50, B9 <= 64), C9, "")
65+: =IF(B9 >= 65, C9, "")
Figure84.jpg

Figure 8.4 - Logical Functions.xlsx​
 
8.1. Adding Intelligence with Logical Functions


8.1.4. The OR Function — Hàm OR
Similar to an And condition is the situation when you need to take an action if one thing or another is true. For example, you might want to pay a salesperson a bonus if she exceeded the dollar sales budget or if she exceeded the unit sales budget. In Boolean logic, this is called an Or condition.
Tương tự điều kiện AND là tình huống bạn cần tiến hành một hành động nếu một điều kiện này hoặc một điều kiện khác là đúng. Ví dụ, bạn có hể muốn trả tiền người cho một nhân viên bán hàng nếu người này đạt doanh số vượt mức đã đề ra (cho riêng cá nhân người đó) hoặc vượt mức doanh số đã đề ra cho cả đơn vị. Trong biểu thức logic kiểu Boolean, đây được gọi là một điều kiện OR (HOẶC).

You won’t be surprised to hear that Or conditions are handled in Excel by the OR() function:
Bạn sẽ không ngạc nhiên khi nghe rằng các điều kiện OR được xử lý trong Excel bởi hàm OR():

OR(logical1 [, logical2, ...])

The OR() result is calculated as follows:
Kết quả của hàm OR() được tính như sau:
  • If one or more of the arguments return TRUE (or any nonzero number), OR() returns TRUE.
    Nếu có một hay nhiều đối số trả về giá trị TRUE (hoặc bất kỳ con số nào khác 0), hàm OR() trả về giá trị TRUE.

  • If all of the arguments return FALSE (or 0), OR() returns FALSE.
    Nếu tất cả các đối số trả về FALSE (hoặc 0), hàm OR() trả về giá trị FALSE.

As with AND(), you use OR() wherever a logical expression is called for, most often within an IF() function. This means that if one or more of the logical conditions in the OR() function are TRUE, IF() returns its value_if_true result; if all of the logical conditions in the OR() function are FALSE, IF() returns its value_if_false result. Here’s an example:
Cũng như hàm AND(), bạn sử dụng hàm OR() ở bất ký nơi nào đòi hỏi có một biểu thức logic, nhưng thường thì bạn hay thấy nó trong một hàm IF(). Có nghĩa là, nếu có một hay nhiều điều kiện logic trong hàm OR() là TRUE, hàm IF() trả về kết quả value_if_true; còn nếu tất cả các điều kiện logic trong hàm OR() là FALSE, hàm IF() trả về kết quả value_if_false. Sau đây là một ví dụ:

=IF(OR(B2 > 0, C2 > 0), "1000", "No bonus")

If the value in B2 is greater than 0 or the value in C2 is greater than 0, the formula returns 1000; otherwise, it returns No bonus.
Nếu giá trị trong ô B2 hoặc giá trị trong ô C2 lớn hơn 0, công thức trả về 1000; còn không thì nó trả về No Bonus.
​
 
Lần chỉnh sửa cuối:
8.1. Adding Intelligence with Logical Functions


8.1.5. Applying Conditional Formatting with Formulas
Áp dụng định dạng có điều kiện với các công thức
In Chapter 1, “Getting the Most Out of Ranges,” you learn about the powerful new conditional formatting features available in Excel 2007. These features enable you to highlight cells, create top and bottom rules, and apply three new types of formatting: data bars, color scales, and icon sets.
Trong chương 1, "Tận dụng tối đa các dãy ô", bạn đã học về sức mạnh của những tính năng định dạng có điều kiện mới trong Excel 2007. Những tính năng này cho phép bạn đánh dấu các ô, tạo các quy tắc cao nhất (top) và thấp nhất (bottom), và áp dụng 3 loại định dạng mới: thanh dữ liệu (data bar), các thang màu (color scale), và tập hợp các biểu tượng (icon set).
➔ For the details on conditional formatting, see “Applying Conditional Formatting to a Range”.
Xem chi tiết về định dạng có điều kiện ở bài "Áp dụng Định dạng có điều kiện cho môt dãy"​

Excel 2007 comes with another conditional formatting component that makes this feature even more powerful: You can apply conditional formatting based on the results of a formula. In particular, you can set up a logical formula as the conditional formatting criteria. If that formula returns TRUE, Excel applies the formatting to the cells; if the formula returns FALSE, instead, Excel doesn’t apply the formatting. In most cases, you use an IF() function, often combined with another logical function such as AND() or OR().
(Ngoài những thành phần vừa nêu trên,) Excel 2007 còn cung cấp thêm một thành phần Định dạng có điều kiện khác nhằm làm cho tính năng này càng mạnh mẽ hơn: Bạn có thể áp dụng định dạng có điều kiện dựa vào kết quả của một công thức. Đặc biệt, bạn có thể thiết lập một công thức logic như là một tiêu chuẩn của định dạng có điều kiện. Nếu công thức trả về TRUE, Ecxel áp dụng định dạng cho các ô; nếu công thức trả về FALSE, Excel không áp dụng các định dạng. Trong đa số các trường hợp, bạn sử dụng hàm IF(), và thường kết hợp với một hàm luận lý khác như là AND() hoặc OR().

Before getting to an example, here are the basic steps to follow to set up formula-based conditional formatting:
Trước khi đi vào ví dụ, đây là những bước cơ bản để thiết lập một định dạng có điều kiện dựa theo công thức:

  1. Select the cells to which you want the conditional formatting applied.
    Chọn những ô bạn muốn áp dụng định dạng có điều kiện.

  2. Choose Home, Conditional Formatting, New Rule. Excel displays the New Formatting Rule dialog box.
    Chọn Home, Conditional Formatting, New Rule, Excel hiển thị hộp thoại New Formatting Rule.

  3. Click Use a Formula to Determine Which Cells to Format.
    Nhấn vào phần Use a Formula to Determine Which Cells to Format.

  4. In the Format Values Where This Formula Is True range box, type your logical formula.
    Nhập công thức luận lý của bạn trong khung Format Values Where This Formula Is True.

  5. Click Format to open the Format Cells dialog box.
    Nhấn Format để mở hộp thoại Format Cells.

  6. Use the Number, Font, Border, and Fill tabs to specify the formatting you want to apply, and then click OK.
    Sử dụng các tab Number, Font, Border, và Fill để xác định loại định dạng bạn muốn áp dụng, rồi nhấn OK.

  7. Click OK.
    Nhấn OK.

For example, suppose you’re working with a table of inventory data that includes the following three fields:
Ví dụ, giả sử bạn đang làm việc với một bảng kiểm kê hàng hóa, trong đó bao gồm ba trường (field) sau:
Qty Available — This is the number of units available to be sold. (In practice, this field would be the current quantity on hand less the current quantity on hold.)
Đây là con số của các món hàng có thể bán (trong thực tế, trường này lấy số lượng ở trường quantity on hand trừ đi số lượng ở trường quantity on hold.

Qty On Order — The number of units that have been ordered, but not yet received, from supplies.
Là con số các món hàng đã đặt mua thêm, nhưng chưa nhận được hàng từ nhà cung cấp.

Reorder Level — The number of units at or below which the product should be reordered.
Con số các món hàng bằng hoặc dưới mức cần phải đặt hàng thêm.​
Given these fields, we want to format those records that meet two criteria:
Với các trường này, chúng ta muốn định dạng cho những dữ liệu đáp ứng hai tiêu chuẩn sau:
  • The Qty Available is less than or equal to the Reorder Level.
    Qty Available nhỏ hơn hoặc bằng Reorder Level.

  • The Qty On Order equals 0.
    Qty On Order bằng 0.

In other words, we need to construct a logical formula that returns TRUE if both the criteria are satisfied. Because we need both conditions to be true, we use an AND() function inside an IF(). Here’s a simplified version of the formula:
Nói cách khác, chúng ta cần tạo một công thức luận lý trả về giá trị TRUE nếu thỏa mãn cả hai điều kiện trên. Bởi vì chúng ta cần cả hai điều kiện đều đúng, nên chúng ta sử dụng hàm AND() bên trong một hàm IF(). Đây là một dạng đơn giản của công thức này:

=IF(AND([Qty Available] <= [Reorder Level], [Qty On Order] = 0), TRUE, FALSE)

The actual formula we need to use is more complex because it uses the new table reference syntax in Excel 2007:
Công thức thực sự chúng ta cần dùng thì phức tạp hơn, bởi vì nó sử dụng kiểu tham chiếu bảng mới của Excel 2007:

=IF(AND(Table1[[#This Row],[Qty Available]] <= Table1[[#This Row], [Reorder Level]], Table1[[#This Row],[Qty On Order]] = 0), TRUE, FALSE)


Figure 8.5 shows a table of inventory data conditionally formatted using the preceding formula.
Hình 8.5 minh họa một bảng kiểm kho đã được định dạng có điều kiện bằng cách sử dụng công thức trên.
(Ở đây tôi bỏ qua kiểu tham chiếu bảng, mà sử dụng kiểu tham chiếu bình thường trong công thức, để các bạn dễ hình dung)
Figure85.jpg

Figure 8.5 - Logical Functions.xlsx​
 
Lần chỉnh sửa cuối:
8.1. Adding Intelligence with Logical Functions


8.1.6. Combining Logical Functions with Arrays
Kết hợp các hàm luận lý với công thức mảng
When you combine the array formulas that you learned about in Chapter 4, “Creating Advanced Formulas,” with IF(), you can perform some remarkably sophisticated operations. Arrays enable you to do things such as apply the IF() logical condition across a range, or sum only those cells in a range that meet the IF() condition.
Khi bạn kết hợp các công thức mảng mà bạn đã học ở chương 4, "Thiết lập các công thức (nâng cao)", với hàm IF(), bạn có thể thực hiện một số thao tác tinh vi đáng chú ý. Các mảng cho phép bạn làm những điều như áp dụng điều kiện logic IF() qua một dãy hoặc chỉ tính tổng của những ô trong một dãy đáp ứng một điều kiện IF() nào đó.

➔ To learn about array formulas, see “Working with Arrays”
Để học về các công thức mảng, xem bài "Làm việc với các mảng"​


Applying a Condition Across a Range
Áp dụng một điều kiện qua một dãy
Using AND() as the logical condition in an IF() function is useful for perhaps three or four expressions. After that, it just gets too unwieldy to enter all those logical expressions. If you’re essentially running the same logical test on a number of different cells, a better solution is to apply AND() to a range and enter the formula as an array.
Sử dụng hàm AND() như một điều kiện logic trong một hàm IF() có lẽ chỉ thì hữu dụng với chừng 3 hoặc 4 biểu thức. Vì sau đó, việc nhập tất cả các biểu thức logic đó trở nên khá vụng về. Nếu, về cơ bản, bạn thực hiện cùng một phép thử logic trên một số ô khác nhau thì có một giải pháp tốt hơn, là áp dụng hàm AND() vào trong một dãy và áp dụng công thức dưới dạng một mảng.

For example, suppose that you want to sum the cells in the range B3:B7, but only if all of those cells contain values greater than 0. Here’s an array formula that does this:
Ví dụ, giả sử rằng bạn muốn cộng các ô trong dãy B3:B7, nhưng chỉ thực hiện phép cộng khi các ô có giá trị lớn hơn 0. Đây là một công thức mảng để thực hiện điều này:

{=IF(AND(B3:B7 > 0), SUM(B3:B7), "")}
Recall from Chapter 4 that you don’t include the braces — { and } — when you enter an array formula. Type the formula without the braces and then press Ctrl+Shift+Enter.
Hãy nhớ lại, ở chương 4, rằng bạn không tự đưa vào các dấu móc ngoặc — { và } — khi bạn nhập một công thức mảng. Bạn cứ nhập bình thường, không có các dấu móc ngoặc, và sau đó nhấn Ctrl+Shift+Enter.
Nhấp chuột vào đây để mở rộng...
This is useful in a worksheet in which you might not have all the numbers yet, and you don’t want a total entered until the data is complete. Figure 8.6 shows an example. The array formula in B8 is the same as the previous one. The array formula in B16 returns nothing because cell B14 is blank.
Điều này thật là hữu dụng với một bảng tính mà trong đó bạn chưa có đủ tất cả các số, và bạn không muốn tính tổng một khi các dữ liệu chưa hoàn chỉnh. Hình 8.6 minh họa một ví dụ. Công thức mảng trong B8 giống như công thức ở trên đây. Công thức mảng trong B16 không trả về kết quả gì bởi vì ô B14 trống.
Figure86.jpg

Figure 8.6 - Logical Functions.xlsx​
 
Lần chỉnh sửa cuối:
8.1.6. Combining Logical Functions with Arrays


Operating Only on Cells That Meet a Condition
Chỉ thao tác trong một ô thỏa mãn một điều kiện cho trước
In the previous section, you saw how to use an array formula to perform an action only if a certain condition is met across a range of cells. A related scenario arises when you want to perform an action on a range, but only on cells that meet a certain condition. For example, you might want to sum only those values that are positive.
Trong phần trước, bạn đã xem cách sử dụng một công thức mảng để thực hiện một hành động chỉ khi thỏa mãn một điều kiện nhất định trong một dãy ô. Một tình huống liên quan xảy ra là khi bạn muốn thực hiện một hành động trên một dãy, nhưng chỉ trên các ô đáp ứng một điều kiện nhất định nào đó. Ví dụ, bạn có thể muốn rằng chỉ tính tổng của những giá trị dương.

To do this, you need to move the operation outside of the IF() function. For example, here’s an array formula that sums only those values in the range B3:B7 that contain positive values:
Để làm điều đó, bạn cần dời phép tính ra ngoài hàm IF(). Ví dụ, sau đây là một công thức mảng chỉ tính tổng của những giá trị dương trong dãy B3:B7:

{=SUM(IF(B3:B7 > 0, B3:B7, 0))}

The IF() function returns an array of values based on the condition (the cell value if it’s positive, 0 otherwise), and the SUM() function adds those returned values.
Hàm IF() trả về một dãy các giá trị dựa trên điều kiện đã đưa ra (bằng giá trị của chính ô đó nếu nó dương, và bằng 0 nếu như ngược lại), và hàm SUM() công các giá trị được trả về đó.

For example, suppose you have a series of investments that mature in various years. It would be nice to set up a table that lists these years and tells you the total value of the investments that mature in each year. Figure 8.7 shows a worksheet set up to do just that.
Ví dụ, gỉ sử rằng bạn có một chuỗi những khoản đầu tư phải đáo hạn trong những năm khác nhau. Sẽ là tốt nếu bạn thiết lập một bảng liệt kê những năm này, và cho bạn biết tổng giá trị của các khoản đầu tư đáo hạn trong mỗi năm đó. Hình 8.7 minh họa một bảng tính được thiết lập để làm điều vừa nêu trên.
Figure87.jpg

Figure 8.7 - Logical Functions.xlsx​

The investment maturity dates are in column B, the investment values at maturity are shown in column C, and the various maturity years are in column E. To calculate the maturity total for 2009, for example, the following array formula is used:
Các ngày đáo hạn nằm ở cột B, các giá trị đầu tư phải đáo hạn trình bày trong cột C, và các năm đáo hạn khác nhau nằm ở cột E. Ví dụ để tính tổng đáo hạn của năm 2009, công thức mảng sau đây được sử dụng:

{=SUM(IF(YEAR($B$3:$B$18) = E3, $C$3:$C$18, 0))}

The IF() function compares the year value in cell E3 (2009) with the year component of the maturity dates in range B3:B18. (Absolute references are used so that the formula can be filled down to the other years.) For cells in which these are equal, IF() returns the corresponding value in column C; otherwise, it returns 0. The SUM() function then adds these returned values.
Hàm IF() so sánh các giá trị năm trong ô E3 (2009) với các giá trị chỉ năm của các ngày đáo hạn trong dãy B3:B18 (các tham chiếu tuyệt đối được sử dụng để công thức có thể điền dọc xuống (fill down) các năm khác). Đối với các ô mà ở đó hai giá trị này bằng nhau, IF() trả về một giá trị tương ứng trong cột C; còn nếu không, nó trả về 0. Sau đó, hàm SUM() cộng những giá trị được trả về này.​
* Xem thêm: Hàm SUM(), Hàm YEAR()​
 
8.1.6. Combining Logical Functions with Arrays


Determining Whether a Value Appears in a List
Xác định một giá trị có xuất hiện trong một danh sách hay không
Many spreadsheet applications require you to look up a value in a list. For example, you might have a table of customer discounts in which the percentage discount is based on the number of units ordered. For each customer order, you need to look up the appropriate discount, based on the total units in the order.
Nhiều ứng dụng bảng tính yêu cầu bạn phải dò tìm một giá trị trong một danh sách. Ví dụ, bạn có một bảng gồm những khoản chiết khấu cho khách hàng, trong đó phần trăm chiết khấu dựa vào số hàng người đó đã đặt. Với mỗi đơn đặt hàng, bạn cần dò tìm những chiết khấu thích hợp dựa vào tổng số mặt hàng trong đơn đặt hàng.

You’ll see some sophisticated tools for looking up values in Chapter 9, “Working with Lookup Functions.” However, array formulas combined with logical functions also offer some tricks for looking up values.
Bạn sẽ học một số công cụ tìm kiếm tinh vi ở chương 9, "Làm việc với các hàm dò tìm và tham chiếu". Tuy nhiên, những công thức mảng kết hợp với các hàm luận lý cũng có vài thủ thuật để tìm kiếm các giá trị.

For example, suppose that you want to know whether a certain value exists in an array. You can use the following general formula, entered into a single cell as an array:
Ví dụ, giả sử rằng bạn muốn biết là liệu có tồn tại một giá trị xác định ở trong một mảng hay không. Bạn có thể dùng công thức chung sau đây, nhập trong một ô dưới dạng công thức mảng:

{=OR(value = range)}

Here, value is the value you want to search for, and range is the range of cells in which to search. For example, Figure 8.8 shows a list of customers with overdue accounts. You enter the account number of the customer in cell B1, and cell B2 tells you whether the number appears in the list.
Ở đây, value là giá trị bạn muốn tìm, và range là dãy ô có thể có chứa giá trị muốn tìm. Ví dụ ở hình 8.8 minh họa một danh sách khách hàng với các tài khoản quá hạn. Bạn nhập số tài khoản của khách hàng trong ô B1, và ô B2 cho biết số tài khoản này có ở trong danh sách hay không.
Figure88.jpg

Figure 8.8 - Logical Functions.xlsx​

Here’s the array formula in cell B2:
Đây là công thức mảng trong ô B2:

{=OR(B1 = B6:B29)}

The array formula checks each value in the range B6:B29 to see whether it equals the value in cell B1. If any one of those comparisons is true, OR() returns TRUE, which means that the value is in the list.
Công thức mảng này kiểm tra từng giá trị trong dãy B6:B29 để tìm xem giá trị đó có giống với giá trị trong ô B1 không. Nếu có bất kỳ một phép so sánh nào là đúng, hàm OR() trả về giá trị TRUE, có nghĩa là giá trị này có trong danh sách.

NOTE: As a similar example, here’s an array formula that returns TRUE if a particular account number is not in the list:
Tương tự như ví dụ trên, đây là một công thức mảng trả về giá trị TRUE nếu tài khoản đó không có không danh sách:

{=AND(B1 <> B6:B29)}

The formula checks each value in B6:B29 to see whether it does not equal the value in B1. If all of those comparisons are true, AND() returns TRUE, which means that the value is not in the list.
Công thức này kiểm ra mỗi giá trị trong dãy B6:B29 để tìm xem giá trị đó có khác với giá trị ở B1 không. Nếu tất cả các phép so sánh này đều đúng, hàm AND() trả về giá trị TRUE, nghĩa là giá trị này không có trong danh sách.
Nhấp chuột vào đây để mở rộng...
 
8.1.6. Combining Logical Functions with Arrays


Counting Occurrences in a Range
Đếm số lần xuất hiện của một giá trị trong một dãy
Now you know how to find out whether a value appears in a list, but what if you need to know how many times the value appears? The following formula does the job:
Bạn đã biết cách để tìm xem một giá trị có xuất hiện trong một dãy hay không, và bây giờ làm sao để biết (nếu tồn tại trong dãy thì) giá trị đó xuất hiện bao nhiêu lần? Công thức sau đây làm được việc này:

{=SUM(IF(value = range, 1, 0))}

Again, value is the value you want to look up, and range is the range for searching. In this array formula, the IF() function compares value with every cell in range. The values that match return 1, and those that don’t return 0. The SUM() function adds these returns values, and the final total is the number of occurrences of value. Here’s a formula that does this for our list of overdue invoices:
Cũng như ở bài trước, value là giá trị bạn muốn tìm, và range là dãy ô (có thể có) chứa giá trị muốn tìm. Trong công thức mảng này, hàm IF() so sánh value với từng ô trong range. Những giá trị tìm thấy (là giống) sẽ trả về 1, còn những giá trị khác (không giống) thì trả về 0. Hàm SUM() cộng những giá trị này lại, và tổng số cuối cùng chính là số lần xuất hiện của value trong dãy. Đây là công thức thực hiện điều đó cho danh sách những hóa đơn quá hạn:

=SUM(IF(B1 = B6:B29, 1, 0))

Figure 8.9 shows this formula in action (cell B3):
Hình 8.9 minh họa hoạt động của công thức này (ô B3):
Figure89.jpg

Figure 8.9 - Logical Functions.xlsx

NOTE: The generic array formula {=SUM(IF(condition, 1, 0))} is useful in any context when you need to count the number of occurrences in which condition returns TRUE. The condition argument is normally a logical formula that compares a single value with each cell in a range of values. However, it’s also possible to compare two ranges, as long as they’re the same shape (that is, they have the same number of rows and columns). For example, suppose that you want to compare the values in two ranges named Range1 and Range2 to see if any of the values are different. Here’s an array formula that does this:
Mẫu công thức mảng {=SUM(IF(condition, 1, 0))} rất hữu dụng trong bất kỳ tình huống nào bạn cần đếm số lần xuất hiện khi condition trả về giá trị TRUE. Đối số condition thường là một công thức luận lý, so sánh một giá trị nào đó với mỗi ô trong dãy các giá trị. Tuy nhiên, nó cũng có thể dùng để so sánh hai dãy với nhau, miễn là hai dãy này có cùng hình dạng (nghĩa là có cùng số cột và số hàng). Ví dụ, giả sử rằng bạn muốn so sánh các cặp giá trị trong hai dãy có tên là Range1 và Range2 để tìm xem có bao nhiêu cặp giá trị khác nhau trong hai dãy đó. Đây là một công thức mảng để làm việc này:

{=SUM(IF(Range1 <> Range2, 1, 0))}

This formula compares the first cell in Range1 with the first cell in Range2, the second cell in Range1 with the second cell in Range2, and so on. Each time the values don’t match, the comparison returns 1; otherwise, it returns 0. The sum of these comparisons is the number of different values between the two ranges.
Công thức này so sánh ô đầu tiên trong Range1 với ô đầu tiên trong Range2, ô thứ hai trong Range1 với ô thứ hai của Range2, v.v... Mỗi lần tìm thấy một cặp khác nhau, phép so sánh trả về 1, và ngược lại thì trả về 0. Tổng số của (kết quả) những phép so sánh này chính là số cặp giá trị khác nhau giữa hai dãy.
(Nói thêm cho rõ: Công thức trên không phải là công thức để tìm có bao nhiêu giá trị không trùng nhau trong hai dãy, mà là tìm xem có bao "cặp giá trị" không trùng nhau)
Nhấp chuột vào đây để mở rộng...
 
8.1.6. Combining Logical Functions with Arrays


Determining Where a Value Appears in a List
Xác định vị trí xuất hiện của một giá trị trong một danh sách
What if you want to know not just whether a value appears in a list, but where it appears in the list? You can do this by getting the IF() function to return the row number for a positive result:
Phải làm gì nếu bạn biết rằng một giá trị có xuất hiện trong một danh sách, nhưng nó xuất hiện ở chỗ nào? Bạn có thể làm được bằng cách yêu cầu hàm IF() trả về con số chỉ hàng (row number) cho một kết quả đúng:

IF(value = range, ROW(range), "")

Whenever value equals one of the cells in range, the IF() function uses ROW() to return the row number; otherwise, it returns the empty string.
Bất cứ khi nào value bằng một trong số các giá trị trong range, hàm IF() sẽ sử dụng hàm ROW() để trả về con số chỉ hàng, còn ngược lại thì trả về một chuỗi rỗng.

To return that row number, we use either the MIN() function or the MAX() function, which return the minimum and maximum, respectively, in a collection of values. The trick here is that both functions ignore null values, so applying that to the array that results from the previous IF() expression tells us where the matching values are:
Để trả về con số chỉ hàng đó, chúng ta sử dụng hàm MIN() và hàm MAX() để xác định số nhỏ nhất và lớn nhất tương ứng trong một danh sách các giá trị. Thủ thuật ở đây là, cả hai hàm này đều bỏ qua các giá trị rỗng, vì vậy việc áp dụng chính cái "bỏ qua" này vào mảng kết quả trả về của hàm IF() sẽ cho chúng ta biết vị trí của value:

  • To get the first instance of the value, use the MIN() function in an array formula, like so:
    Để có được (con số chỉ hàng của) lần xuất hiện đầu tiên của value, dùng hàm MIN() trong một công thức mảng như sau:

    {=MIN(IF(value = range, ROW(range), ""))}

  • To get the last instance of the value, use the MAX() function in an array formula, as shown here:
    Để có được (con số chỉ hàng của) lần xuất hiện cuối cùng của value, dùng hàm MAX() trong một công thức mảng như sau:

    {=MAX(IF(value = range, ROW(range), ""))}

Here are the formulas you would use to find the first and last occurrences in the previous list of overdue invoices:
Đây là những công thức mà bạn sẽ sử dụng để tìm (con số chỉ hàng của) lần xuất hiện đầu tiên và cuối cùng của một hóa đơn quá hạn trong danh sách (ở ví dụ của bài trước):

=MIN(IF(B1 = B6:B29, ROW(B6:B29), ""))

=MAX(IF(B1 = B6:B29, ROW(B6:B29), ""))

Figure 8.10 shows the results (the row of the first occurrence is in cell D2, and the row of the last occurrence is in cell D3).
Hình 8.10 minh họa kết quả tìm vị trí xuất hiện của giá trị đang ở B1 (con số chỉ hàng của lần xuất hiện đầu tiên ở D2, và con số chỉ hàng của lần xuất hiện cuối cùng ở D3)
Figure810.jpg

Figure 8.10 - Logical Functions.xlsx

NOTE: It’s also possible to determine the address of the cell containing the first or last occurrence of a value in a list. To do this, use the ADDRESS() function, which returns an absolute address, given a row and column number:
(Không chỉ xác định mỗi con số chỉ hàng (row number), chúng ta) cũng có thể xác định đúng địa chỉ xuất hiện lần đầu và lần cuối của một giá trị trong một dãy. Bằng cách dùng hàm ADDRESS() trả về một địa chỉ tuyệt đối, cả con số chỉ hàng và con số chỉ cột:

{=ADDRESS(MIN(IF(B1 = B6:B29, ROW(B6:B29), "")), COLUMN(B6:B29))}

{=ADDRESS(MAX(IF(B1 = B6:B29, ROW(B6:B29), "")), COLUMN(B6:B29))}
Nhấp chuột vào đây để mở rộng...

​
* Xem thêm: Hàm ADDRESS(), Hàm COLUMN(), Hàm ROW(), Hàm MAX(), Hàm MIN()​
 
Lần chỉnh sửa cuối:
Case Study


8.2. Building an Accounts Receivable Aging Worksheet
Tạo một bảng tính về các khoản phải thu quá hạn
If you use Excel to store accounts receivable data, it’s a good idea to set up an aging worksheet that shows past-due invoices, calculates the number of days past due, and groups the invoices into past-due categories (1–30 days, 31–60 days, and so on).
Nếu bạn dùng Excel để lưu trữ dữ liệu của các khoản phải thu, bạn nên thiết lập một bảng tính dùng để theo dõi những khoản phải thu quá hạn, tính số ngày quá hạn và phân chúng thành các nhóm dựa theo số ngày quá hạn (1–30 ngày, 31–60 ngày, v.v...) (

Figure 8.11 shows a simple implementation of an accounts receivable database. For each invoice, the due date (column D) is calculated by adding 30 to the invoice date (column C). Column E subtracts the due date (column D) from the current date (in cell B1) to calculate the number of days each invoice is past due.
Hình 8.11 minh họa một "chương trình" đơn giản của một cơ sở dữ liệu các khoản phải thu. Với mỗi hóa đơn, ngày đến hạn (cột D) được tính toán bằng cách cộng thêm 30 vào ngày lập hóa đơn (cột C). Cột E lấy ngày hiện tại (ở ô B1) trừ đi ngày đến hạn (cột D) để tính ra số ngày quá hạn của mỗi hóa đơn.
Figure811.jpg

Figure 8.11 - Logical Functions.xlsx
​


Calculating a Smarter Due Date
Tính toán một ngày đến hạn khôn khéo hơn
You might have noticed a problem with the due dates in Figure 8.11: The date in cell D11 falls on a weekend. The problem here is that the due date calculation just adds 30 to the invoice date. To avoid weekend due dates, you need to test whether the invoice date plus 30 falls on a Saturday or Sunday. The WEEKDAY() function helps because it returns 7 if the date is a Saturday, and 1 if the date is a Sunday. So, to check for a Saturday, you could use the following formula:
Có lẽ bạn thấy các ngày đến hạn ở hình 8.11 có một vấn đề: Ngày tháng trong ô D11 rơi vào ngày cuối tuần. Vấn đề ở đây là phép tính ra ngày đến hạn chỉ là cộng thêm 30 vào ngày hóa đơn. Để tránh các ngày đến hạn là cuối tuần, bạn cần phải thử xem ngày hóa đơn cộng với 30 có rơi vào thứ Bảy hay Chủ Nhật không. Hàm WEEKEND() giúp bạn được chuyện này vì nó trả về con số 7 nếu ngày đó là thứ Bảy, và trả về 1 nếu ngày đó là Chủ Nhật. Vậy, để kiểm tra ngày thứ Bảy, bạn dùng công thức sau:

=IF(WEEKDAY(C4 + 30) = 7, C4 + 32, C4 + 30)

Here, I’m assuming that the invoice date resides in cell C4. If WEEKDAY(C4 + 30) returns 7, the date is a Saturday, so you add 32 to C4 instead (this makes the due date the following Monday). Otherwise, you just add 30 days as usual. Checking for a Sunday is similar:
Ở đây, tôi giả sử rằng ngày hóa đơn nằm ở ô C4. Nếu công thức WEEKDAY(C4 + 30) trả về 7, thì đó là thứ Bảy, do đó bạn cộng thêm 32 vào C4 (nó sẽ lui ngày đến hạn đến ngày thứ Hai sau đó); còn nếu không, bạn chỉ việc cộng thêm 30 như bình thường. Việc kiểm tra ngày Chủ Nhật cũng giống vậy:

=IF(WEEKDAY(C4 + 30) = 1, C4 + 31, C4 + 30)

The problem, though, is that you need to combine these two tests into a single formula. To do that, you can nest one IF() function inside another. Here’s how it works:
Vấn đề là bạn cần kết hợp hai phép thử trên vào trong một công thức. Để làm điều đó, bạn cần lồng một hàm IF() vào một hàm IF() khác. Đây là cách làm:

=IF(WEEKDAY(C4+30) = 7, C4+32, IF(WEEKDAY(C4+30) = 1, C4+31, C4+30))

The main IF() checks to see whether the date is a Saturday. If it is, you add 32 days to C4; otherwise, the formula runs the second IF(), which checks for Sunday. Figure 8.12 shows the revised aging sheet with the nonweekend due date in cell D11.
Hàm IF() chính kiểm tra xem liệu một ngày có phải là thứ Bảy không. Nếu đúng, bạn cộng thêm 32 ngày vào C4. Còn nếu không, công thức chạy tiếp hàm IF() thứ hai, kiểm tra xem ngày đó có phải là Chủ Nhật không. Hình 8.12 minh họa một bảng tính tính ngày quá hạn đã được sửa lại, với ngày đến hạn ở ô D11 không bị rơi vào cuối tuần nữa.
Figure812.jpg

Figure 8.12 - Logical Functions.xlsx​

Xem thêm: Hàm WEEKDAY()​
(To be continued)
 
Lần chỉnh sửa cuối:
Case Study


8.2. Building an Accounts Receivable Aging Worksheet
Tạo một bảng tính về các khoản phải thu quá hạn
(Continue)


Aging Overdue Invoices Date
Các hóa đơn quá hạn
For cash-flow purposes, you also need to correlate the invoice amounts with the number of days past due. Ideally, you’d like to see a list of invoice amounts that are between 1 and 30 days past due, between 31 and 60 days past due, and so on. Figure 8.13 shows one way to set up accounts receivable aging:
Với mục đích theo dõi việc luân chuyển tiền tệ, bạn cũng cần lập một sự tương quan giữa những giá trị hóa đơn quá hạn với con số những ngày quá hạn. Về mặt lý tưởng thì, bạn sẽ muốn thấy một danh sách các giá trị của những hóa đơn quá hạn được sắp xếp theo số ngày quá hạn của nó: từ 1 đến 30 ngày (1 tháng), từ 31 đến 60 ngày (2 tháng), v.v... Hình 8.13 minh họa một cách để thiết lập các khoản phải thu quá hạn:
Figure813.jpg

Figure 8.13 - Logical Functions.xlsx​

For the invoice amounts shown in column G (1–30 days), the sheet uses the following formula (this is the formula that appears in G4):
Với những giá trị hóa đơn nằm trong cột G (từ 1 đến 30 ngày), bảng tính sử dụng công thức sau đây (đây là công thức trong ô G4):

=IF(E4 <= 30, F4, "")

If the number of days the invoice is past due (cell E4) is less than or equal to 30, the formula displays the amount (from cell F4); otherwise, it displays a blank.
Nếu số ngày quá hạn của hóa đơn (ở ô E4) mà ít hơn hay bằng 30, công thức sẽ hiển thị giá trị của hóa đơn đó (lấy từ ô F4), còn không thì để trống.

The amounts in column H (31–60 days) are a little trickier. Here, you need to check whether the number of days past due is greater than or equal to 31 days and less than or equal to 60 days. To accomplish this, you can press the AND() function into service:
Các giá trị trong cột H (31-60 ngày) thì hơi khó hơn. Ở đây, bạn cần kiểm tra xem số ngày quá hạng có lớn hơn hoặc bằng 31 ngày và nhỏ hơn hay bằng 60 ngày. Để làm điều này, bạn cần sử dụng hàm AND():

=IF(AND(E4 >= 31, E4 <= 60), F4, "")

The AND() function checks two logical expressions: E4> = 31 and E4 <= 60. If both are true, AND() returns TRUE, and the IF() function displays the invoice amount. If one of the logical expressions isn’t true (or if they’re both not true), AND() returns FALSE, and the IF() function displays a blank. Similar formulas appear in column I (61–90 days)
and column J (91–120 days). Column K (Over 120) looks for past-due values that are greater than 120.
Hàm AND() kiểm tra hai biểu thức logic: E4> = 31 and E4 <= 60. Nếu cả hai đều đúng, AND() trả về TRUE, và hàm IF() hiển thị giá trị của hóa đơn. Nếu một trong hai biểu thức logic đó không đúng (hoặc cả hai đều không đúng), AND() trả về FALSE và hàm IF() bỏ trống ô. Các công thức trong cột I (61-90 ngày) và cột J (91-120 ngày) cũng tương tự như vậy. Cột K (nhiều hơn 120 ngày) tìm những giá trị quá hạn của những hóa đơn đã quá 120 ngày.​
 
Lần chỉnh sửa cuối:
Chapter 8 - WORKING WITH LOGICAL AND INFORMATION FUNCTIONS


8.3. Getting Data with Information Functions
Thu thập dữ liệu với các hàm thông tin
Excel’s information functions return data concerning cells, worksheets, and formula results.
Các hàm thông tin của Excel trả về dữ liệu liên quan đến các ô, các bảng tính, và các kết quả của công thức.

Table 8.2 - List all Excel’s Information Functions
Bảng 8.2 - Danh sách các hàm thông tin trong Excel
 
Lần chỉnh sửa cuối:
8.3. Getting Data with Information Functions


8.3.1. Counting the Number of Blanks in a Range
Đếm số ô trống trong một dãy
When putting together the data for a worksheet model, it’s common to pull the data from various sources. Unfortunately, this often means that the data arrives at different times and you end up with an incomplete model. If you’re working with a big list, you might want to keep a running total of the number of pieces of data that you’re still missing. This is the perfect opportunity to break out the ISBLANK() function and plug it into the array formula for counting that you learned earlier:
Khi nhập dữ liệu cho một mô hình bảng tính, dữ liệu thường được lấy từ nhiều nguồn khác nhau. Thật không may, điều này thường có nghĩa là dữ liệu nhập vào tại các thời điểm không giống nhau, và rốt cuộc bạn có một mô hình không hoàn hảo. Nếu bạn làm việc với một danh sách lớn, có lẽ bạn muốn có được tổng số những dữ liệu còn thiếu. Đây là một cơ hội để phân tích hàm ISBLANK() và đưa nó vào trong một công thức mảng (bạn đã học ở những bài trước) để đếm:

{=SUM(IF(ISBLANK(range), 1, 0))}

The IF() function runs through the range looking for blank cells. Each time it comes across a blank cell, it returns 1; otherwise, it returns 0. The SUM() function adds the results to give the total number of blank cells. Figure 8.14 shows an example (see cell G1).
Hàm IF() chạy xuyên suốt trong cả dãy để tìm những ô trống. Cứ mỗi lần nó đi ngang một ô trống, nó trả về 1; còn nếu đi ngang một ô có dữ liệu thì nó trả về 0. Hàm SUM() cộng hết các kết quả của IF() trả về và cho ra tổng số các ô trống. Hình 8.14 minh họa một ví dụ (xem công thức ở ô G1).
Figure817.jpg

Figure 8.14 - Logical Functions.xlsx​



8.3.2. Checking a Range for Non-numeric Values
Kiểm tra các giá trị không phải là số trong một dãy
A similar idea is to check a range upon which you’ll be performing a mathematical operation to see if it holds any cells that contain non-numeric values. In this case, you plug the ISNUMBER() function into the array counting formula, and return 0 for each TRUE result and 1 for each FALSE result. Here’s the general formula:
Một ý tưởng tương tự là kiểm tra một dãy mà trong đó bạn sẽ thực hiện một phép toán để xem nó có bao gồm một ô nào chứa những giá trị không phải là số hay không. Trong trường hợp này, bạn đưa hàm ISNUMBER() vào trong công thức mảng (dùng để đếm, như đã nói ở trên), và cho nó trả về 0 với mỗi kết quả TRUE, trả về 1 với mỗi kết quả FALSE. Đây là công thức mẫu:

{=SUM(IF(ISNUMBER(range), 0, 1))}​
 
Lần chỉnh sửa cuối:
8.3. Getting Data with Information Functions


8.3.3. Counting the Number of Errors in a Range
Đếm số lỗi trong một dãy
For the final counting example, it’s often nice to know not only whether a range contains an error value, but also how many such values it contains. This is easily done using the ISERROR() function and the array counting formula:
Với ví dụ đếm ở bài trên, sẽ thú vị hơn nếu bạn không chỉ biết liệu rằng trong một dãy có giá trị lỗi nào không, mà còn biết được có bao nhiêu lỗi trong dãy đó. Điều này rất dễ làm, bằng cách dùng hàm ISERROR() và một công thức đếm mảng:

{=SUM(IF(ISERROR(range), 1, 0))}​


8.3.4. Ignoring Errors When Working with a Range
Bỏ qua lỗi khi làm việc với một dãy
Sometimes, you have to work with ranges that contain error values. For example, suppose that you have a column of gross margin results (which require division), but one or more of the cells are showing the #DIV/0! error because you’re missing data. You could wait until the missing data is added to the model, but it’s often necessary to perform preliminary calculations. For example, you might want to take the average of the results that you do have.
Thỉnh thoảng, bạn phải làm việc với dãy có chứa những giá trị lỗi. Ví dụ, bạn có một cột tính tổng lợi nhuận (có đòi hỏi một phép chia), nhưng trong cột đó có chứa một hoặc vài ô hiển thị lỗi #DIV/0! bởi vì bạn thiếu dữ liệu. Bạn có thể đợi cho tới khi bổ sung đầy đủ các dữ liệu vào mô hình, nhưng thường thì bạn cần phải thực hiện một vài phép tính sơ khởi. Ví dụ, bạn muốn lấy trung bình của những kết qủa đã có.

To do this efficiently, you need some way of bypassing the error values. Again, this is possible by using the ISERROR() function plugged into an array formula. For example, here’s a general formula for taking an average across a range while ignoring any error values:
Để làm điều này một cách có hiệu quả, bạn cần phải có cách gì đó để bỏ qua những giá trị lỗi. Lại một lần nữa, bạn có thể dùng hàm ISERROR() nhúng bên trong một công thức mảng. Ví dụ, đây là một công thức mẫu dùng để lấy giá trị trung bình của một dãy mà bỏ qua những giá trị lỗi trong dãy đó:

{=AVERAGE(IF(ISERROR(range), "", range))}

Figure 8.15 provides an example:
Hình 8.15 minh họa một ví dụ:
Figure818.jpg

Figure 8.15 - Information Functions.xlsx​


---------------------------------------------------- End of Chapter 8 ----------------------------------------------------​
 
Lần chỉnh sửa cuối:
Bạn phải đăng nhập hoặc đăng ký để trả lời bài viết tại đây.
Web KT

Bài viết mới nhất

Emyeuexcel
  • Question Question
In tất cả các file trong cùng 1 thư mục (1 người xem)
Trả lời
3
Đọc
179
Hoàng Tuấn 868
H
F
  • Question Question
Tự động Merge Mail bằng VBA (1 người xem)
Trả lời
1
Đọc
85
Hoàng Tuấn 868
H
L
Lỗi font khi convert từ pdf (1 người xem)
Trả lời
2
Đọc
80
DeTong
D
micam111
  • Question Question
Cách chạy nhiều đồng hồ đếm giờ trong VBA (1 người xem)
Trả lời
2
Đọc
613
tranduc36
T
vsitcnh
Hỗ trợ công thức tính điểm dựa theo điều kiện. (1 người xem)
Trả lời
13
Đọc
128
Hoàng Tuấn 868
H
1
Hỏi về đánh dấu hàng ngang hàng dọc bằng màu khi kích chuột vào 1 ô
Trả lời
7
Đọc
181
Gà Công Nghệ
Gà Công Nghệ
PhanTuHuong
Sách Lập trình VBA trong AutoCAD đã có mặt
    • Thích
    • Yêu thích
2
Trả lời
20
Đọc
1K
Mr.hieudoanxd
Mr.hieudoanxd
P
Excell code vba : Tìm Cell để sửa dữ liệu
    • Buồn
2
Trả lời
26
Đọc
533
Phạm Trường 80
P
D
Nhờ anh em giúp đỡ.
    • Buồn
Trả lời
2
Đọc
86
SA_DQ
SA_DQ
00029591
Cần hỗ trợ công thức tổng hợp dữ liệu không cần cột phụ Vlookup (1 người xem)
Trả lời
2
Đọc
86
DeTong
D
H
Đổi số thành chữ trong office 365
Trả lời
19
Đọc
1K
HoangTan891026
H
Gà Công Nghệ
Lấy dữ liệu file excel từ sheet này sang sheet khác
Trả lời
4
Đọc
132
Gà Công Nghệ
Gà Công Nghệ
N
Mong mọi người giúp đỡ
Trả lời
1
Đọc
61
minhminh27
minhminh27
minhduc1o2
Excel lỗi font Time New Roman+
Trả lời
7
Đọc
116
minhduc1o2
minhduc1o2
H
Lọc dữ liệu trong XL
    • Cười
Trả lời
0
Đọc
49
huong.dinh
H
giaiphap
Add-Ins hổ trợ Word trộn danh sách và lưu ra nhiều tệp tin khác nhau.
    • Thích
    • Yêu thích
    • Cảm ơn
2
Trả lời
39
Đọc
9K
fujishima
F
K
xin file excel bảng đấu bóng đá
    • Yêu thích
2 3
Trả lời
40
Đọc
25K
subin1905
S
B
Xin giải pháp chống sửa, xóa dữ liệu đã nhập vào từ ngày hôm trước.
    • Thích
2
Trả lời
24
Đọc
7K
chihuumvc
C
Trungvt3
Công thức trả về định dạng không như mong muốn và viết phân số trong 1 ô.
Trả lời
1
Đọc
66
bebo021999
B
G
Hướng dẫn Lập danh sách File
    • Không thích
Trả lời
16
Đọc
272
thanhhuy_hvnh
thanhhuy_hvnh
Xem thêm…

Thành viên có số lượng bài viết cao nhất tháng

Thành viên có điểm tương tác cao nhất tháng

  • Không tìm thấy.

Đếm ngược thời gian

000
Ngày
00
Giờ
00
phút
00
giây
TALK SHOW: CÙNG NHẢY LAM BA ĐA VỚI HÀM LAMBDA (λ) EXCEL 365
Back
Top Bottom