Hướng dẫn học hàm Excel 2007

Sự nghiệp - Công việc

Cùng tham khảo những hướng dẫn học hàm Excel 2007 nhé. Sử dụng hàm IF, AND và VLOOKUP trong Excel

Sử dụng hàm IF & AND trong Excel

Hàm IF VÀ AND trong excel rất hay kết hợp với nhau trong tính toán. sau đây tôi xin hướng dẫn các bạn cách sử dụng khi hàm if và and kết hợp.

* Hàm if (hàm điều kiện)
ví dụ : nếu hôm nay trời mưa tôi sẽ nghỉ học ( điều kiện là "nếu trời mưa")
cú pháp:IF(Điều kiện kiểm tra, giá trị nếu điều kiện đúng, giá trị nếu điều kiện sai)

* Hàm AND có nghĩa là VÀ. Dùng khi muốn nói đến cái này và cái này và cái này.
Cú pháp: AND(giá trị 1 , giá trị 2,...)

- Kết hợp hàm if và and ( lồng hàm and vào hàm if)
cú pháp : IF(AND(giá trị 1 , giá trị 2,...),"giá trị nếu điều kiện đúng","giá trị nếu điều kiện sai")

Ví Dụ Bài Tập Hàm IF VÀ AND:

đề bài : dựa vào điểm trung bình của học sinh hay xếp loại học lực của học sinh đó :
cụ thể :
+ nếu ĐTB >5.0 VÀ ĐTB< 6.5 => xếp loại trung bình

+ nếu ��TB > 8.0 => xếp loại giỏi

+ nếu ĐTB >=6.5 VÀ ĐTB < 8.0 => XẾP LOẠI KHÁ

+ ĐTB < 5.0 => xếp loại yếu.

* cách tính : sử dụng hàm if và and

công thức tính : =IF(AND(J7>5,J7<6.5)," trung binh",IF(AND(J7>=6.5,J7<8),"kha",IF(J7>=8,"gioi","yeu")))

Kết quả

chúc bạn thành công !

Sử dụng hàm IF trong Excel để tăng hiệu quả việc xử lý bảng tính

Sử dụng hàm IF trong Excel để tăng hiệu quả việc xử lý bảng tính

Bài viết dưới đây nhắm đến các bạn mới tập sử dụng máy tính hoặc một số thầy cô giáo cần dùng Excel để nhập dữ liệu tổng kết điểm cho sinh viên nhưng không được đào tạo tin học văn phòng một các bài bản.

Bài viết sẽ đưa ra một vài ví dụ đơn giản giúp các bạn làm quen và hiểu rõ hơn về cách sử dụng hàm IF trong Excel để đạt hiệu quả hơn trong công việc.
Các ví dụ trong bài, mình sử dụng Excel của bộ Office 2007, các bạn hoàn toàn có thể áp dụng cho Office 2003 , 2010, hay 2013.
Trước hết nhắc lại cấu trúc hàm IF:

=IF(BIỂU THỨC SO SÁNH, GIÁ TRỊ NẾU ĐÚNG, GIÁ TRỊ NẾU SAI)

Ví dụ 1 :

Tại cột A tôi có cột dữ liệu dạng số. Nhu cầu của tôi là sử dụng hàm IF để tại cột B tôi có được kết quả là "Nếu giá trị bên cột A = 0 thì cột B hiển thị "Không", nếu cột A khác 0 thì hiển thị "Có".
Đơn giản, tôi chỉ cần gõ công thức (không phân biệt HOA - thường) tại ô B2 :
=IF(A2=0,"Không","Có")
Copy công thức này cho ô B3 cho đến B7 ta có được kết quả:

Vậy là bạn đã nắm sơ qua được cách thức sử dụng hàm IF, giờ ta chuyển sang một ví dụ khó hơn để các bạn thực hành.

Ví dụ 2:

Vẫn với cột giá trị tương tự như tại ví dụ 1. Nhưng nhu cầu của tôi giờ là làm sao để cột B hiển thị theo mong muốn : Nếu giá trị cột A = 0 thì hiển thị "Không", nếu <0 thì hiển thị "Âm" và nếu nếu >0 thì hiển thị "Dương". Ta viết công thức tại ô B2 như sau:
=IF(A2=0,"Không",IF(A2<0,"âm","dương"))
Nhìn vào công thức thì có vẻ phức tạp dần, một số bạn sẽ vẫn còn chưa rõ, ta có thể hiểu công thức trên qua minh họa

Copy công thức vừa nhập tại B2 cho các ô B3 đến B7, ta có được kết quả như mong muốn :

Áp dụng những kiến thức và kinh nghiệm có được qua 2 ví dụ trên. Ta đi đến ví dụ 3.

Ví dụ 3:

Mình có bảng dữ liệu điểm của học sinh như hình trên. Mình muốn tại cột C hiển thị theo mong muốn :
      0<= Điểm < 5 - Loại "Yếu"
      5<= Điểm < 8 - Loại "Khá"
      8<= Điểm - Loại "Giỏi"
(Tên học sinh, điểm, xếp loại chỉ mang tính chất ví dụ)

Muốn làm được vậy tại ô C2 mình gõ công thức:
=IF(B2>0,IF(B2<5,"Yếu",IF(B2<8,"Khá","Giỏi")))
Copy công thức cho các ô C3 đến C5 ta có được kết quả :

Trong Excel còn rất nhiều hàm khác nữa nhưng khuôn khổ bài viết giới hạn mình không thể giới thiệu hết được. Hy vọng từ các ví dụ nhỏ ở trên các bạn có thể nắm được phần nào cách sử dụng hàm IF trong Excel để giải quyết công việc được nhanh chóng và chính xác.

VLOOKUP (Hàm VLOOKUP)

Mô tả

Bạn có thể dùng hàm VLOOKUP để tìm cột đầu tiên của một phạm vi ô, sau đó trả về một giá trị từ bất kỳ ô nào trên cùng hàng của phạm vi. Ví dụ, giả sử bạn có một danh sách các nhân viên trong phạm vi A2:C10. Số ID của các nhân viên được lưu trữ trong cột đầu tiên của phạm vi, như thể hiện trong minh họa sau đây.

Nếu bạn biết số ID của nhân viên, bạn có thể dùng hàm VLOOKUP để trả về bộ phận hoặc tên của nhân viên đó. Để có được tên của nhân viên số 38, bạn có thể dùng công thức =VLOOKUP(38, A2:C10, 3, FALSE). Công thức này tìm giá trị 38 trong cột đầu tiên của phạm vi A2:C10, sau đó trả về giá trị được chứa trong cột thứ ba của phạm vi và trên cùng hàng làm giá trị tra cứu ("Axel Delgado").

Chữ V trong VLOOKUP đại diện cho vertical (theo chiều dọc). Dùng VLOOKUP thay cho HLOOKUP khi các giá trị so sánh của bạn được đặt trong một cột ở bên trái của dữ liệu mà bạn muốn tìm.

Cú pháp

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Cú pháp hàm VLOOKUP có các đối số dưới đây:

lookup_value Bắt buộc. Các giá trị để tìm kiếm trong cột đầu tiên của bảng hoặc phạm vi. Đối số lookup_value có thể được xem như là một giá trị hoặc một tham chiếu. Nếu giá trị bạn cung cấp cho đối số lookup_value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của đối số table_array, VLOOKUP trả về giá trị lỗi #N/A.
table_array Bắt buộc. Phạm vi ô có chứa dữ liệu. Bạn có thể dùng một tham chiếu cho một phạm vi (ví dụ, A2:D8), hoặc một tên phạm vi. Các giá trị trong cột đầu tiên của table_array là các giá trị được tìm kiếm bởi lookup_value. Những giá trị này có thể là văn bản, số hoặc giá trị lô-gic. Văn bản chữ hoa và chữ thường tương đương nhau.
col_index_num Bắt buộc. Số cột trong đối số table_array mà giá trị khớp phải được trả về từ đó. Một đối số col_index_num của 1 trả về giá trị trong cột đầu tiên trong table_array; một col_index_num của 2 trả về giá trị trong cột thứ hai trong table_array, và cứ như vậy.

Nếu đối số col_index_num là:

Nhỏ hơn 1, VLOOKUP trả về giá trị lỗi #VALUE .
Lớn hơn số lượng cột trong table_array, VLOOKUP trả về giá trị lỗi #REF! .

range_lookup Tùy chọn. Một giá trị lô-gic xác định bạn có muốn VLOOKUP tìm thấy một kết quả khớp chính xác hay kết quả khớp tương đối:
- Nếu range_lookup là TRUE hoặc bị bỏ qua, một kết quả khớp chính xác hoặc tương đối được trả về. Nếu một kết quả khớp chính xác không được tìm thấy thì giá trị lớn nhất kế tiếp nhỏ hơn lookup_value được trả về.

Quan trọng Nếu range_lookup là TRUE hoặc bị bỏ qua, các giá trị ở cột đầu tiên của table_array phải được đặt theo trật tự sắp xếp giảm dần; nếu không, VLOOKUP có thể không trả về giá trị đúng.

Để biết thêm thông tin, vui lòng xem Sắp xếp dữ liệu trong phạm vi hoặc bảng.

Nếu range_lookup là FALSE, các giá trị trong cột đầu tiên của table_array không cần được sắp xếp.

Nếu đối số range_lookup là FALSE, VLOOKUP sẽ chỉ tìm một kết quả khớp chính xác. Nếu có hai hoặc nhiều giá trị trong cột đầu tiên của table_array khớp với lookup_value, giá trị đầu tiên được tìm thấy sẽ được sử dụng. Nếu một kết quả khớp chính xác không được tìm thấy, một giá trị lỗi #N/A được trả về.

Chú thích

Khi tìm kiếm các giá trị văn bản trong cột đầu tiên của table_array, hãy đảm bảo rằng dữ liệu trong cột đầu tiên của table_array không chứa dấu cách trống ở đầu, dấu cách trống ở cuối, việc sử dụng không nhất quán dấu nháy kép thẳng ( ' hoặc " ) và dấu nháy kép cong ( ‘ hoặc “), hoặc các ký tự không in ra. Trong những trường hợp này, VLOOKUP có thể trả về một giá trị không chính xác hoặc không được mong đợi.

Để biết thêm thông tin, vui lòng xem Hàm CLEAN và hàm TRIM.

Khi tìm kiếm các giá trị ngày, hãy bảo đảm dữ liệu trong cột đầu tiên của table_array không được lưu trữ như là các giá trị văn bản. Trong trường hợp này, VLOOKUP có thể trả về một giá trị không đúng hoặc không được mong đợi.
Nếu range_lookup là FALSE và lookup_value là văn bản, bạn có thể sử dụng các ký tự đại diện — dấu chấm hỏi (?) và dấu sao (*) — trong lookup_value. Một dấu chấm hỏi khớp bất kỳ ký tự đơn nào; một dấu sao phù hợp với bất kỳ chuỗi ký tự nào. Nếu bạn muốn tìm một dấu chấm hỏi hay dấu sao thực sự, hãy gõ dấu ngã (~) trước ký tự.

Ví dụ

Sao chép dữ liệu của ví dụ trong bảng sau đây và dán vào ô A1 của một trang tính Excel mới. Để công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.

Mật độ	Độ nhớt	Nhiệt độ
0,457	3,55	500
0,525	3,25	400
0,606	2,93	300
0,675	2,75	250
0,746	2,57	200
0,835	2,38	150
0,946	2,17	100
1,09	1,95	50
1,29	1,71	0
Công thức	Mô tả	Kết quả
=VLOOKUP(1,A2:C10,2)	Dùng khớp gần đúng để tìm kiếm giá trị 1 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong cột A là 0,946 và sau đó trả về giá trị từ cột B trong cùng hàng.	2,17
=VLOOKUP(1,A2:C10,3,TRUE)	Dùng khớp gần đúng để tìm kiếm giá trị 1 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 1 trong cột A là 0,946 và sau đó trả về giá trị từ cột C trong cùng hàng.	100
=VLOOKUP(0.7,A2:C10,3,FALSE)	Dùng khớp chính xác để tìm giá trị 0,7 trong cột A. Vì không có sự khớp chính xác trong cột A, lỗi được trả về	#N/A
=VLOOKUP(0.1,A2:C10,2,TRUE)	Dùng khớp gần đúng để tìm giá trị 0,1 trong cột A. Vì 0,1 nhỏ hơn giá trị nhỏ nhất trong cột A, lỗi được trả về.	#N/A
=VLOOKUP(2,A2:C10,2,TRUE)	Dùng khớp gần đúng để tìm kiếm giá trị 2 trong cột A, rồi tìm giá trị lớn nhất nhỏ hơn hoặc bằng 2 trong cột A là 1,29 và sau đó trả về giá trị từ cột B trong cùng hàng.	1,71

Ví dụ 2

ID Mục	Mục	Cost	Tăng giá
ST-340	Ghế đẩy	$145,67	30%
BI-567	Yếm dãi	$3,56	40%
DI-328	Tả lót	$21,45	35%
WI-989	Khăn tay	$5,12	40%
AS-469	Máy hút gió	$2,56	45%
Công thức	Mô tả	Kết quả
= VLOOKUP("DI-328", A2:D6, 3, FALSE) * (1 + VLOOKUP("DI-328", A2:D6, 4, FALSE))	Tính toán giá bán lẻ của tả lót bằng cách thêm tỷ lệ phần trăm tăng giá vào chi phí.	$28,96
= (VLOOKUP("WI-989", A2:D6, 3, FALSE) * (1 + VLOOKUP("WI-989", A2:D6, 4, FALSE))) * (1 - 20%)	Tính toán giá bán của khăn tay bằng cách trừ đi một phần chiết khấu đã xác định trong giá bán lẻ.	$5,73
= IF(VLOOKUP(A2, A2:D6, 3, FALSE) >= 20, "Giá tăng thêm là " & 100 * VLOOKUP(A2, A2:D6, 4, FALSE) &"%", "Chi phí dưới $20,00")	Nếu chi phí của một mặt hàng lớn hơn hoặc bằng $20,00, sẽ hiển thị chuỗi "Giá tăng thêm là nn%"; nếu không, sẽ hiển thị chuỗi "Chi phí dưới $20,00"	Giá tăng thêm là 30%
= IF(VLOOKUP(A3, A2:D6, 3, FALSE) >= 20, "Giá tăng thêm là: " & 100 * VLOOKUP(A3, A2:D6, 4, FALSE) &"%", "Chi phí là $" & VLOOKUP(A3, A2:D6, 3, FALSE))	Nếu chi phí của một mặt hàng lớn hơn hoặc bằng $20,00, sẽ hiển thị chuỗi Giá tăng thêm là nn%"; nếu không, sẽ hiển thị chuỗi Chi phí là $n.nn"	Chi phí là $3,56

Ví dụ 3

ID	Họ	Tên	Chức danh	Ngày sinh
1	Davis	Sara	Đại diện Bán hàng	08/12/68
2	Fontana	Olivier	Phó chủ tịch Kinh doanh	19/02/1952
3	Leal	Karina	Đại diện Bán hàng	30/08/1963
4	Patten	Michael	Đại diện Bán hàng	19/09/1958
5	Burke	Brian	Quản lý Bán hàng	04/03/1955
6	Sousa	Luis	Đại diện Bán hàng	02/07/1963
Công thức	Mô tả	Kết quả
=INT(YEARFRAC(DATE(2004,6,30), VLOOKUP(5,A2:E7,5, FALSE), 1))	Đối với năm tài chính 2004, tìm tuổi của nhân viên có ID là 5. Dùng hàm YEARFRAC để trừ đi ngày sinh từ ngày kết thúc năm tài chính và hiển thị kết quả là một số nguyên bằng hàm INT.	49
=IF(ISNA(VLOOKUP(5,A2:E7,2,FALSE)) = TRUE, "Không tìm thấy nhân viên", VLOOKUP(5,A2:E7,2,FALSE))	Nếu có một nhân viên có ID bằng 5, sẽ hiển thị họ của nhân viên; nếu không, sẽ hiển thị thông báo "Không tìm thấy nhân viên". Hàm ISNA trả về giá trị TRUE khi hàm VLOOKUP trả về giá trị lỗi #N/A.	Burke
=IF(ISNA(VLOOKUP(15,A3:E8,2,FALSE)) = TRUE, "Không tìm thấy nhân viên", VLOOKUP(15,A3:E8,2,FALSE))	Nếu có một nhân viên có ID bằng 15, sẽ hiển thị họ của nhân viên; nếu không, sẽ hiển thị thông báo "Không tìm thấy nhân viên". Hàm ISNA trả về một giá trị TRUE khi hàm VLOOKUP trả về giá trị lỗi #N/A.	Không tìm thấy nhân viên
=VLOOKUP(4,A2:E7,3,FALSE) & " " & VLOOKUP(4,A2:E7,2,FALSE) & " là " & VLOOKUP(4,A2:E7,4,FALSE)	Đối với nhân viên có ID bằng 4, ghép nối (kết hợp) các giá trị bao gồm ba ô trong một câu hoàn chỉnh.	Michael Patten là một Đại diện Bán hàng.