Nhóm hàm logic và một số hàm khác

HÀM LOGIC
Hàm AND
Có lẽ khỏi giải thích nhỉ. AND có nghĩa là VÀ. Vậy thôi. Dùng hàm này khi muốn nói đến cái này và cái này và cái này......
Cú pháp: AND(logical1 [, logical2] [, logical3]...)
logical: Những biểu thức sẽ được xét xem đúng (TRUE) hay sai (FALSE)
Nếu tất cả các biểu thức đều đúng, hàm AND() sẽ trả về giá trị TRUE, và chỉ cần 1 trong các biểu thức sai, hàm AND() sẽ trả về giá trị FALSE.
Bạn có thể dùng hàm AND() bất cứ chỗ nào bạn muốn, nhưng thường thì hàm AND() hay được dùng chung với hàm IF().
Ví dụ:
=IF(AND(B2 > 0, C2 > 0), "1000", "No bonus")
Nếu giá trị ở B2 và ở C2 lớn hơn 0, thì (thưởng) 1.000, còn nếu chỉ cần một trong 2 ô B2 hoặc C2 nhỏ hơn 0, thì không thưởng chi cả.
=FALSE(): Có thể nhập trực tiếp FALSE vào trong công thức, Excel sẽ hiểu đó là một biểu thức có giá trị FALSE mà không cần dùng đến cú pháp của hàm này

Hàm IF
=IF (logical_test, value_if_true, value_if_false) : Dùng để kiểm tra điều kiện theo giá trị và công thức
Cú pháp: IF (điều kiện, giá trị 1, giá trị 2) Nếu điều kiện đúng thì hàm trả về giá trị 1, ngược lại hàm nhận giá trị 2
Cái lập luận: "Nếu tôi đúng thì làm cho tôi cái này, nếu tôi sai thì làm cho tôi cái kia".. Có lẽ trong chúng ta ai cũng hiểu.
Một tình huống đơn giản nhất
Cú pháp: IF(logical_test, value_is_true)
logical_test: Một biểu thức sẽ được xét xem đúng (TRUE) hay sai (FALSE)
value_is_true: giá trị trả về khi biểu thức logical_test được kiểm tra là đúng (TRUE)
Ví dụ:
=IF(A1 >= 1000, "It's big!")
Nghĩa là, nếu giá trị ở A1 lớn hơn hoặc bằng 1000, thì kết quả nhận được sẽ là "It's big!", còn không, nếu A1 nhỏ hơn 1000, kết quả sẽ là FALSE.
Một ví dụ khác, giả sử bạn có một bảng đánh giá mức độ bán ra, mua vào của một danh mục hàng hóa dài, và bạn muốn theo dõi những mặt hàng có doanh số bán ra không đạt yêu cầu để điều chỉnh chiến lược kinh doanh của mình, bằng cách gán những dấu "<" bên cạnh nó, hễ phần trăm doanh số càng thấp thì những dấu hiệu "<" càng nhiều...
Bạn có thể dùng hàm IF(), theo mẫu:
=IF(cell<0, flag)
Với cell là giá trị doanh số mà bạn muốn theo dõi, và flag là dấu hiệu để mô tả, ví dụ, cell chứa giá trị doanh số là B2:
=IF(B2<0, "<<<<<")
Để những dấu "<" tỷ lệ thuận với mức sụt giảm doanh số bán hàng, bạn có thể dùng hàm REPT(), với công thức:
REPT("<" , B2 * -100)
Ở đây, phải nhân giá trị của B2 với -100, bởi vì chúng ta chỉ xét những trường hợp B2<0
Và công thức hoàn chỉnh để thể hiện mức độ sụt giảm doanh số của từng mặt hàng sẽ là:
=IF(B2<0, REPT("<" , B2 * -100))

Những hàm IF lồng nhau
Trong cuộc sống đời thường, có mấy ai dễ dàng chấp nhận chuyện "một cái nếu", phải không các bạn.
Chúng ta thường sẽ dùng kiểu, nếu... rồi nhưng mà nếu... nhiều khi kéo dài đến vô tận!
Trong Excel cũng vậy. Giả sử, chúng ta xếp loại học tập, nếu điểm trung bình (ĐTB) lớn hơn 9 thì giỏi, vậy ĐTB nhỏ hơn 9 thì dở? Chưa, ĐTB nhỏ hơn 9 nhưng lớn hơn 7 thì khá cái đã, rồi ĐTB nhỏ hơn 7 nhưng chưa bị điểm 5 thì trung bình, chỉ khi nào ĐTB nhỏ hơn 4 thì mới gọi là dở (cái này tôi chỉ ví dụ thôi, các bạn đừng sử dụng để xếp loại nhé).
Khi đó, chúng ta sẽ dùng những hàm IF() lồng nhau, IF() này nằm trong IF() kia. Sau này chúng ta sẽ học cách ghép thêm nhiều điều kiện khác vào nữa.
Ví dụ, tôi lấy lại ví dụ đã nói ở bài trước:
=IF(A1 >= 1000, "Big!", "Not big")
Bi giờ thêm chút, A1 lớn hơn 1000 là "big" rồi, nhưng chẳng lẽ 10000 thì cũng chỉ là "big" ? Có lẽ nên tặng thêm một danh hiệu cao hơn:
=IF(A1 >= 1000, IF(A1 >= 10000, "Really big!!", Big!"), "Not big")
Hoặc là, đồng ý rằng <1000 là "Not big", nhưng nó khác "Small" chứ (tui không lớn, chưa chắc tui nhỏ), vậy chúng ta thêm một định nghĩa "Small" thử xem:
=IF(A1 >= 1000, "Big!", IF(A1 < 100, "Small", "Not big"))
Bạn để ý nhé, ở đây tôi đặt cái IF "con" không giống như ở trên, sao cũng, miễn là đừng sai cú pháp của IF().
Và nếu thích, bạn có thể ghép tất cả lại:
=IF(A1 >= 1000, IF(A1 >= 10000, "Really big!!", Big!"), IF(A1 < 100, "Small", "Not big"))
Chĩ cần một lưu ý, là những dấu đóng mở ngoặc đơn. Nếu bạn đóng và mở không đúng lúc hoặc không đủ, Excel sẽ không hiểu, hoặc là cho các bạn kết quả sai đấy.

Hàm IFERROR
Trong quá trình thao tác với bảng tính, không ít lần chúng ta gặp lỗi, và cũng khó mà tránh được lỗi. Ví dụ, một công thức đơn giản thôi =A/B có thể gây lỗi #DIV/0! nếu như B bằng 0, hoặc gây lỗi #NAME? nếu A hoặc B không tồn tại, gây lỗi #REF! nếu có ô nào đó liên kết với A hoặc B bị xóa đi...
Tuy nhiên, đôi lúc chúng ta lại cần phải lợi dụng chính những cái lỗi này, ví dụ sẽ đặt ra một tình huống: nếu có lỗi thì làm gì đó... Gọi nôm na là BẪY LỖI.
Có lẽ vì vậy mà hàm này có hai chữ đầu là IF; IFERROR = nếu xảy ra lỗi (thì)...
MS Excel 2003 trở về trước có hàm ISERROR(value), với value là một biểu thức. Nếu biểu thức này gặp lỗi, ISERROR() sẽ trả về giá trị TRUE, còn nếu biểu thức không có lỗi, ISERROR() trả về giá trị FALSE.
Và chúng ta thường dùng ISERROR() kèm với IF:
=IF(ISERROR(expression), ErrorResult, expression)
Nếu như biểu thức (expression) có lỗi, công thức trên sẽ lấy giá trị ErrorResult (một ô rỗng, hoặc một thông báo lỗi, v.v..), ngược lại, sẽ lấy chính giá trị biểu thức đó.
Ví dụ: =IF(ISERROR(A/B), "", A/B)
Cái bất tiện khi phải dùng vừa IF() vửa ISERROR() là chúng ta phải nhập cái biểu thức hai lần: một lần trong hàm ISERROR() và một lần ở tham số value_is_False của IF()
Có thể cái bất tiện vừa nói trên không đáng kể, tuy nhiên cách sử dụng này làm cho công thức của chúng ta trở nên khó dùng hơn,
bởi vì nếu thay cái biểu thức(expression), thì chúng ta phải thay đổi nguyên cả công thức.
Excel 2010 dường như hiểu được sự bất tiện đó, nên đã gộp hai hàm IF() và ISERROR lại thành một, đó là IFERROR()
Cú pháp: IFERROR(value, value_if_error)
_____value: Biểu thức có thể sẽ gây ra lỗi
_____value_if_error: kết quả trả về nếu value gây ra lỗi
Nếu biểu thức value không gây lỗi, IFERROR() sẽ lấy biểu thức đó, còn nếu nó có lỗi thì lấy cái biểu thức value_if_error.
Ví dụ, công thức =IF(ISERROR(A/B), "", A/B) nếu dùng IFERROR() thì sẽ là
=IFERROR(A/B, "")
Bạn thấy đấy, IFERROR() ngắn gọn và dễ hiểu hơn nhiều.
=NOT (logical) : Đảo ngược giá trị của các đối số

Hàm OR
OR có nghĩa là HOẶC. Dùng hàm này khi muốn nói đến cái này hoặc cái này hay cái kia... cái nào cũng được, miễn là phải có ít nhất 1 cái!
Cú pháp: OR(logical1 [, logical2] [, logical3]...)
logical: Những biểu thức sẽ được xét xem đúng (TRUE) hay sai (FALSE)
Nếu tất cả các biểu thức đều sai, hàm OR() sẽ trả về giá trị FALSE, và chỉ cần 1 trong các biểu thức đúng, hàm OR() sẽ trả về giá trị TRUE.
Giống như hàm AND(), bạn có thể dùng hàm OR() bất cứ chỗ nào bạn muốn, nhưng thường thì hàm OR() hay được dùng chung với hàm IF().
Ví dụ:
=IF(OR(B2 > 0, C2 > 0), "1000", "No bonus")
Nếu giá trị ở B2 hoặc ở C2 lớn hơn 0 (tức là chỉ cần 1 trong 2 ô lớn hơn 0), thì (thưởng) 1.000, còn nếu cả 2 ô B2 hoặc C2 đều nhỏ hơn 0, thì không thưởng chi cả.
=TRUE(): Có thể nhập trực tiếp TRUE vào trong công thức, Excel sẽ hiểu đó là một biểu thức có giá trị TRUE mà không cần dùng đến cú pháp của hàm này
Bỏ qua những ô bị lỗi khi chạy công thức
Ví dụ: Cột Gross Margin (cột D) của bảng tính dưới đây có chứa một số ô gặp lỗi chia cho 0 (#DIV/0!), do bên cột C có những ô trống.
Để tính trung bình cộng của cột D, kể những ô có lỗi #DIV/0!, phải dùng công thức mảng như sau:
{=AVERAGE(IF(ISERROR(D3:D12), "", D3:D12))}
(nghĩa là nếu gặp những ô có lỗi thì coi như nó bằng rỗng)
Xác định tên của cột (Determining the Column Letter)
Trong Excel có hàm COLUMN(), cho ra kết quả là số của cột (ví dụ, gõ hàm này trong cột B thì kết quả sẽ là 2).
Nhưng đôi khi bạn muốn kết quả là tên của cột chứ không muốn đó là con số (B chứ không phải là 2), thì làm sao?
Đây là một vấn đề đòi hỏi sự khôn khéo một chút, vì tên cột trong bảng tính chạy từ A đến Z, từ AA đến AZ... và cho tới tận cùng là XFD (!)
Có một hàm giúp chúng ta tìm địa chỉ tuyệt đối của một cell, đó là hàm CELL("address"), ví dụ $A$2, hoặc $B$10...

Hàm CELL(info_type [,reference])
Với info_type là một tham số đã được định nghĩa (sẽ nói kỹ hơn trong những bài sau)
Và reference là cell mà bạn chỉ định, nếu bỏ trống thì Excel sẽ lấy ngay cái cell có chứa công thức CELL().
Trong bài này, để tìm địa chỉ tuyệt đối của một cell, chúng ta sẽ dùng công thức CELL() với info_type là "address"
Tinh ý một chút, ta thấy tên của cột chính là những chữ cái nằm giữa hai dấu dollar ($) trong cái địa chỉ tuyệt đối này.
Bắt đầu làm nhé: dùng hàm MID() trích ra chữ cái từ vị trí thứ 2 trong địa chỉ tuyệt đối của cell:
=MID(CELL("Address"), 2, num_chars)
Cái khó là cái num_chars này đây, vì tên cột thì có thể là 1, 2, hoặc 3 ký tự (ví dụ: A, AA hoặc AAA). Vận dụng hàm FIND thôi:
FIND("$", CELL("address"",A2), 3) - 2
Giải thích chút nhé: Dùng hàm FIND(), tìm vị trí của dấu $ trong cái địa chỉ tuyệt đối của cell, và bắt đầu tìm từ vị trí thứ 3 trong cái địa chỉ này.
Tại sao phải trừ đi 2? Công thức trên sẽ chỉ ra vị trí (là một con số) của dấu $ thứ hai trong địa chỉ tuyệt đối của cell, tức là cái dấu $ phía sau tên cột,
phải trừ đi 2 tức là trừ bớt đi 2 cái $, lúc này kết quả sẽ chính là số ký tự của tên cột (1 chữ, 2 chữ hoặc 3 chữ)
Bây giờ, công thức hoàn chỉnh sẽ như sau:
=MID(CELL("Address"), 2, FIND("$", CELL("address"), 3) - 2)
Công thức này áp dụng cho chính cell chứa công thức.
Nếu muốn tìm tên cột tại một cell nào đó, bạn chỉ việc thêm địa chỉ (hoặc một cái gì đó tham chiếu đến địa chỉ này) của cell muốn tìm vào phía sau cái "address"
Ví dụ, muốn tìm tên của cell AGH68, bạn gõ:
=MID(CELL("Address", AGH68), 2, FIND("$", CELL("address", AGH68), 3) - 2) → AGH
Lập mã số tự động
Có nhiều danh mục khách hàng hoặc danh mục hàng hóa được lập mã số bằng cách dùng vài ký tự đầu của tên khách hàng (hoặc tên hàng hóa) kết hợp với một con số.
Cách đặt mã số tự động như vậy rất dễ làm trong Excel, bằng cách sử dụng những hàm xử lý văn bản và chuỗi.
Giả sử danh mục của chúng ta nằm ở cột A và bắt đầu tại cell A2.
Trước tiên, chúng ta hãy tách 3 ký tự đầu tiên của danh mục và định dạng cho nó thành những chữ in hoa, bằng công thức:
UPPER(LEFT(A2, 3))
Tiếp theo, tận dụng chính số của những hàng trong bảng tính để lập mã số tự động: ROW(A2),
và định dạng sao cho những con số này luôn có 4 chữ số, bằng công thức:
TEXT(ROW(A2),"0000")
Và đây là công thức hoàn chỉnh:
=UPPER(LEFT(A2, 3)) & TEXT(ROW(A2), "0000")

Hàm BETADIST()
Trả về giá trị của hàm tính mật độ phân phối xác suất tích lũy beta.
Thông thường hàm này được dùng để nghiên cứu sự biến thiên về phần trăm các mẫu, ví dụ như khoảng thời gian mà người ta dùng để xem TV trong một ngày chẳng hạn.
Cú pháp: = BETADIST(x, alpha, beta, A, B)
x : Giá trị giữa A và B, dùng để tính mật độ hàm.
alpha & beta : Tham số của phân phối.
A : Cận dưới của khoảng x, mặc định là 0.
B : Cận trên của khoảng x, mặc định là 1.
Lưu ý:
* Nếu có bất kỳ đối số nào không phải là số, BETADIST() trả về giá trị lỗi #VALUE!
* Nếu alpha ≤ 0 hay beta ≤ 0, BETADIST() trả về giá trị lỗi #NUM!
* Nếu x < A, x > B hay A = B, BETADIST() trả về giá trị lỗi #NUM!
* Nếu bỏ qua A và B, nghĩa là mặc định A = 0 và B = 1, BETADIST() sẽ sử dụng phân phối tích lũy beta chuẩn hóa.
Hàm BETAINV()
Trả về nghịch đảo của hàm tính mật độ phân phối xác suất tích lũy beta.
Nghĩa là nếu xác suất = BETADIST(x, ...) thì x = BETAINV(xác suất, ...)
Thường dùng trong việc lên kế hoạch dự án, để mô phỏng số lần mở rộng xác suất, biết trước thời gian bổ sung kỳ vọng và độ biến đổi.
Cú pháp: = BETAINV(probability, alpha, beta, A, B)
Probability : Xác suất của biến cố x trong phân phối xác suất tích lũy beta.
alpha & beta : Tham số của phân phối.
A : Cận dưới của khoảng x, mặc định là 0.
B : Cận trên của khoảng x, mặc định là 1.
Lưu ý:
* Nếu có bất kỳ đối số nào không phải là số, BETAINV() trả về giá trị lỗi #VALUE!
* Nếu alpha ≤ 0 hay beta ≤ 0, BETAINV() trả về giá trị lỗi #NUM!
* Nếu probability ≤ 0 hay probability > 1, BETAINV() trả về giá trị lỗi #NUM!
* Nếu bỏ qua A và B, nghĩa là mặc định A = 0 và B = 1, BETAINV() sẽ sử dụng phân phối tích lũy beta chuẩn hóa.
* BETAINV() sử dụng phương pháp lặp khi tính mật độ phân phối. Với probability cho trước, BETAINV() lặp cho tới khi kết quả chính xác trong khoảng ±0.0000003. Nếu BETAINV() không hội tụ sau 100 lần lặp, nó sẽ trả về giá trị lỗi #NA!
Ví dụ:
BETAINV(0.6854706, 8, 10, 1, 3) = 2

Hàm BINOMDIST()
Trả về xác suất của những lần thử thành công của phân phối nhị phân.
BINOMDIST() thường được dùng trong các bài toán có số lượng cố định các phép thử, khi kết quả của các phép thử chỉ là thành công hay thất bại, khi các phép thử là độc lập, và khi xác xuất thành công là không đổi qua các cuộc thử nghiệm.
Ví dụ, có thể dùng BINOMDIST() để tính xác suất khoảng hai phần ba đứa trẻ được sinh ra là bé trai.
Cú pháp: = BINOMDIST(number_s, trials, probability_s, cumulative)
Number_s : Số lần thử thành công trong các phép thử.
Trials : Số lần thử.
Probability_s : Xác suất thành công của mỗi phép thử.
Cumulative : Một giá trị logic để xác định hàm tính xác suất.
= 1 (TRUE) : BINOMDIST() trả về hàm tính xác suất tích lũy, là xác suất có số lần thành công number_s lớn nhất.
= 0 (FALSE) : BINOMDIST() trả về hàm tính xác suất điểm (hay là hàm khối lượng xác suất), là xác suất mà số lần thành công là number_s.
Lưu ý:
* Nếu number_s và trials là số thập phân, chúng sẽ được cắt bỏ phần lẻ để trở thành số nguyên.
* Nếu number_s, trials hay probability_s không phải là số, BINOMDIST() trả về giá trị lỗi #VALUE!
* Nếu number_s < 0 hay number_s > trials, BINOMDIST() trả về giá trị lỗi #NUM!
* Nếu probability_s < 0 hay probability_s > 1, BINOMDIST() trả về giá trị lỗi #NUM!
Ví dụ:
BINOMDIST(6, 10, 0.5, 0) = 0.2050781
BINOMDIST(6, 10, 0.5, 1) = 0.828125

Hàm CHIDIST()
Trả về xác xuất một phía của phân phối chi-squared.
Phân phối chi-squared kết hợp với phép thử chi-squared dùng để so sánh các giá trị quan sát với các giá trị kỳ vọng.
Ví dụ, một thí nghiệm về di truyền có thể giả thiết rằng thế hệ kế tiếp của các cây trồng sẽ thừa hưởng một tập hợp các màu sắc nào đó; bằng cách so sánh các giá trị quan sát được với các giá trị kỳ vọng, có thể thấy được giả thiết ban đầu là đúng hay sai.
Cú pháp: = CHIDIST(x, degrees_freedom)
x : Giá trị dùng để tính phân phối.
degrees_freedom : Số bậc tự do.
Lưu ý:
* Nếu các đối số không phải là số, CHIDIST() trả về giá trị lỗi #VALUE!
* Nếu x < 0, CHIDIST() trả về giá trị lỗi #NUM!
* Nếu degrees_freedom không phải là số nguyên, phần thập phân của nó sẽ bị cắt bỏ để trở thành số nguyên.
* Nếu degrees_freedom < 1 hay degrees_freedom > 10^10, CHIDIST() trả về giá trị lỗi #NUM!
* CHIDIST() được tính toán theo công thức: CHIDIST = P(X > x), với X là biến ngẫu nhiên chi-squared.
Ví dụ:
CHIDIST(18.307, 10) = 0.050001

Hàm NORMINV()
Trả về nghịch đảo của phân phối tích lũy chuẩn.
Cú pháp: = NORMINV(probability, mean, standard_dev)
probability : Xác suất ứng với phân phối chuẩn
mean : Giá trị trung bình cộng của phân phối
standard_dev : Độ lệch chuẩn của phân phối
Lưu ý:
* Nếu có bất kỳ đối số nào không phải là số, NORMINV() sẽ báo lỗi #VALUE!
* Nếu probability nhỏ hơn 0 hoặc lớn hơn 1, NORMINV() sẽ báo lỗi #NUM!
* Nếu standard_dev nhỏ hơn hoặc bằng 0, NORMDINV() sẽ báo lỗi #NUM!
* Nếu mean = 0 và standard_dev = 1, NORMINV() sẽ dùng phân bố chuẩn.
* NORMINV() sử dụng phương pháp lặp đi lặp lại để tính hàm. Nếu NORMINV() không hội tụ sau 100 lần lặp, hàm sẽ báo lỗi #NA!