Bạn đang xem bài viết Hướng Dẫn Cách Kết Hợp Hàm Vlookup Với Hàm If Trong Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Chào mừng các bạn đến với Seri bài viết: Tùy biến các hàm trong thực tế công việc.
Trong seri này chúng ta sẽ tìm hiểu những ví dụ thực tế, khi các yêu cầu công việc đòi hỏi phải tùy biến, kết hợp nhiều hàm với nhau thì mới giải quyết được yêu cầu.
Bài học ngày hôm nay là : Kết hợp hàm VLOOKUP với hàm IF
Dùng hàm IF để tránh lỗi #N/A cho hàm VLOOKUP
Ở hình trên, chúng ta thấy công thức ở ô E2 cho kết quả lỗi. Bởi vì nội dung ở ô D2 không có giá trị, tức là Lookup_Value không có nội dung nên sẽ báo lỗi #N/A
Để khắc phục lỗi này, chúng ta có thể sử dụng kết hợp hàm IF như sau:
= IF(D2=””,””, VLOOKUP(D2,$A$2:$B$7,2,0))
Nếu ô D2 (lookup_value của hàm vlookup) là rỗng thì sẽ rỗng
Nếu ô D2 không rỗng thì sẽ sử dụng hàm Vlookup
Dùng hàm IF để tự động thay đổi số cột kết quả trong hàm Vlookup
Ở ví dụ này, chúng ta muốn khi thay đổi điều kiện ở ô E1 để xét kết quả của hàm Vlookup tương ứng với điều kiện này.
Nếu E1 là Số tiền thì sẽ
Cột Giới tính: Cột 3
Chúng ta kết hợp hàm IF như sau:
= IF(D2=””,””,VLOOKUP(D2,$A$2:$C$7, IF(E1=”Số tiền”,2,3),0))
Khác với ví dụ 1, ở đây chúng ta cần mở rộng bảng tham chiếu bao gồm cả cột C
Để có thể sử dụng tốt việc kết hợp các hàm với nhau, chúng ta cần nắm rõ được logic của vấn đề trước, sau đó mới xác định sử dụng hàm nào, đặt thứ tự các hàm tại vị trí nào.
Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học Excel online tại Gitiho.com
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Hướng Dẫn Cách Sử Dụng Hàm Vlookup Kết Hợp Với Hàm Sum Hoặc Sumif Trong Excel
Nhiệm vụ có thể khác nhau, nhưng có cùng bản chất – bạn muốn tìm và tính tổng các giá trị dựa trên một hay nhiều tiêu chí trong Excel. Đó là loại giá trị gì? Bất cứ giá trị số nào. Vậy tiêu chí là gì? Bất kỳ tiêu chí nào 🙂 Bắt đầu từ một chữ sô hay một tham chiếu đến một ô chứa giá trị chính xác, rồi kết thúc bằng toán tử logic và công thức trong Excel trả về kết quả.
Hãy lưu ý rằng, đây là các ví dụ nâng cao – điều này có nghĩa là bạn đã quen thuộc với các quy tắc và cú pháp cơ bản của hàm VLOOKUP. Nếu không, bạn chắc chắn phải xem qua phần đầu tiên của bài hướng dẫn về hàm VLOOKUP cho người mới bắt đầu.
HÀM VLOOKUP VÀ HÀM SUM TRONG EXCEL – TÍNH TỔNG CỦA CÁC GIÁ TRỊ TRÙNG KHỚP
Giả sử, bạn có danh sách các sản phẩm có đính doanh số trong vài tháng, một cột là một tháng.
Giải pháp là sử dụng mảng trong thông số thứ ba (col_index_num) của hàm VLOOKUP trong Excel. Đây là công thức hàm VLOOKUP mẫu:
=SUM(VLOOKUP(lookup_value, lookup_range, {2,3,4}, FALSE))
Như bạn có thể thấy, chúng ta sử dụng mảng {2,3,4} trong câu lệnh thứ ba để hiển thị vài giá trị cần tìm trong cùng công thức VLOOKUP để tính tổng các giá trị ở cột 2, 3, và 4.
Và bây giờ, hãy điều chỉnh sự kết hợp của hàm VLOOKUP và hàm SUM đối với dữ liệu của chúng ta để tính tổng tất cả doanh số từ cột B đến cột M trong bảng trên:
=SUM(VLOOKUP(B2, ‘Doanh số hàng tháng’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13},FALSE))
Quan trọng! Vì bạn đang tạo một công thức mảng, hãy chắc rằng bạn nhấp Ctrl+Shift+Enter thay vì chỉ nhấn mỗi Enter khi bạn gõ xong. Khi bạn thực hiện xong, Microsoft Excel sẽ đóng khung công thức của bạn trong dấu ngoặc nhọn như thế này:
{=SUM(VLOOKUP(B2, ‘Doanh số hàng tháng’!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13},FALSE))}
Nếu bạn nhấp Enter, thì chỉ có giá tị đầu tiên trong mảng được xử lý, điều này sẽ cho kết quả sai.
Như bạn có thể thấy, việc sử dụng hàm VLOOKUP cùng với hàm SUM rất dễ. Tuy nhiên, đây không phải là giải pháp hoàn hảo, đặc biệt khi bạn đang thao tác với bảng lớn. Điểm mấu chốt ở đây là việc sử dụng công thức mảng có thể gây ảnh hưởng ngược lại hoạt động của sổ làm việc vì mỗi giá trị trong mảng cần một hàm VLOOKUP riêng biệt. Vì thế, càng có nhiều giá trị trong mảng thì càng có nhiều công thức mảng trong sổ làm việc, Excel sẽ chạy càng chậm.
Bạn có thể loại bỏ vấn đề này bằng cách sử dụng hàm INDEX và hàm MATCH thay vì hàm VLOOKUP và hàm SUM, và tôi sẽ cung cấp cho bạn vài ví dụ công thức trong bài viết tiếp theo.
CÁCH THỰC HIỆN CÁC PHÉP TÍNH KHÁC VỚI HÀM VLOOKUP TRONG EXCEL
Tính giá trị trung bình
{=AVERAGE(VLOOKUP(A2, ‘Bảng cần tìm’!$A$2:$D$10,{2,3,4},FALSE))}
Công thức tìm giá trị từ ô A2 trong “Bảng cần tìm’ và tính giá trị trung bình ở các cột B,C,D trên cùng một hàng.
Tìm giá trị lớn nhất
{=MAX(VLOOKUP(A2,’Bảng cần tìm’$A$2:$D$10,{2,3,4},FALSE))}
Công thức tìm giá trị từ cột A2 trong ‘Bảng cần tìm’ rồi tìm giá trị lớn nhất ở các cột B, C, D trên cùng một hàng.
Tìm giá trị nhỏ nhất
{=MIN(VLOOKUP(A2,’Bảng cần tìm’$A$2:$D$10,{2,3,4},FALSE))}
Công thức tìm giá trị từ cột A2 trong ‘Bảng cần tìm’ rồi tìm giá trị nhỏ nhất ở các cột B, C, D trên cùng một hàng.
Tính phần trăm trên tổng số
{=0.3*SUM(VLOOKUP(A2,’Bảng cần tìm’$A$2:$D$10,{2,3,4},FALSE))}
Công thức tìm giá trị từ cột A2 trong ‘Bảng cần tìm’, tính tổng giá trị ở các cột B, C, D trên cùng một hàng, rồi tính 30% trên tổng số.
Lưu ý. Vì các công thức trên đều là công thức mảng, nên hãy nhớ nhấn Ctrl+Shift+Enter để nhập chúng vào ô được chính xác.
Nếu ta thêm các công thức trên vào bảng “Tổng doanh số” của ví dụ trước, thì kết quả sẽ tương tự như thế này:
Trong trường hợp thông số cần tìm là một mảng thay vì một giá trị riêng lẻ, thì hàm VLOOKUP sẽ không có tác dụng bởi vì nó không thể tìm kiếm trong các mảng dữ liệu. Trong trường hợp này, bạn có thể sử dụng hàm LOOKUP – hàm tương tự với hàm VLOOKUP nhưng có thao tác với mảng và với các giá trị riêng lẻ.
Hãy xem xét các ví dụ sau, để bạn hiểu hơn điều mà tôi nói. Giả sử, bạn có một bảng liệt kê tên khách hàng, sản phẩm mua, và số lượng (Bảng chính). Bạn cũng có bảng thứ hai liệt kê giá sản phẩm (Bảng cần tìm). Nhiệm vụ của bạn là lập công thức tính tổng các đơn hàng đặt bởi các khách hàng xác định.
=SUM(LOOKUP($C$2:$C$10,’Bảng cần tìm’!$A$2:$A$16,’Bảng cần tìm’!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))
Bởi vì đây là công thức mảng, hãy nhớ nhấn Ctrl+Shift+Enter để hoàn thành.
Chúng ta sẽ đặt hàm SUM qua một bên, bởi vì công dụng của nó quá rõ ràng, và chỉ tập trung vào 3 thành phần được được nhân với nhau:
LOOKUP($C$2:$C$10,’Bảng cần tìm’!$A$2:$A$16,’Bảng cần tìm’!$B$2:$B$16)
Hàm LOOKUP này tìm kiếm hàng hóa được liệt kê trong cột C trong bảng chính, và trả về giá tiền tương ứng từ cột B trong bảng cần tìm.
Thông số này trả về số lượng mỗi sản phẩm mua bởi khách hàng, số lượng được liệt kê ở cột D trong bảng chính. Khi được nhân với giá tiền, điều này được trả về bởi hàm VLOOKUP ở trên, hàm này sẽ cung cấp cho bạn giá tiền của mỗi sản phẩm đã mua.
Công thức này so sánh tên khách hàng ở cột B với tên khách hàng ở ô G1. Nếu trùng khớp, nó sẽ trả về “1”, nếu không thì nó sẽ trả về “0”. Bạn sử dụng nó chỉ để xóa tên khách hàng không xuất hiện ở ô G1, bởi vì tất cả chúng ta đều biết bất cứ số nào nhân với 0 đều bằng 0.
Bởi vì công thức của chúng ta là công thức mảng nên nó lặp lại quá trình trên cho mỗi giá trị trong mảng cần tìm. Và cuối cùng, hàm SUM tính tổng các kết quả của phép nhân. Chẳng có gì khó khăn cả, đúng không nào?
Lưu ý. Để công thức LOOKUP hoạt động chính xác thì bạn cần phải lọc cột cần tìm trong bảng cần tìm theo thứ tự tăng dần (từ A đến Z). Nếu việc lọc không được chấp nhận đối với dữ liệu của bạn, thì hãy thử qua công thức SUM/TRANSPOSE được gợi ý bởi Leo.
HÀM VLOOKUP VÀ HÀM SUMIF – TÌM KIẾM VÀ TÍNH TỔNG CÁC GIÁ TRỊ ĐÁP ỨNG CÁC TIÊU CHUẨN NHẤT ĐỊNH
Bảng chính chứa nhiều mục có cùng số chứng minh nhân dân theo thứ tự ngẫu nhiên.
Bạn không thể thêm cột “Tên người bán” vào bảng chính.
Trước khi lập công thức, hãy để tôi nhắc bạn cú pháp của hàm SUMIF:
SUMIF(range, criteria, [sum_range])
range – thông số này đã tự giải thích cho chính nó rồi, chỉ là một dải ô mà bạn muốn đánh giá thông qua các tiêu chuẩn nhất định.
criteria – điều kiện nói cho công thức biết cần tính tổng giá trị nào. Nó có thể được dùng dưới dạng số, tham chiếu ô, biểu thức, hay hàm Excel khác.
sum_range – thông số này là tùy chọn, nhưng rất quan trọng đối với chúng ta. Nó xác định dải ô nơi mà các giá trị của ô tương ứng sẽ được cộng. Nếu loại bỏ, Excel sẽ tính tổng tất cả giá trị của các ô đã được xác định ở câu lệnh thứ nhất (thông số đầu tiên).
Hãy nhớ những điều trên, giờ chúng ta hãy xác định ba thông số cho hàm SUMIF của mình. Có thể bạn vẫn còn nhớ, chúng ta muốn tính tổng tất cả doanh số của một người xác định. Tên của người đó được nhập vào ô F2 trong bảng chính (hãy xem lại hình ảnh ở trên).
Range – bởi vì chúng ta đang tìm kiếm thông qua số chứng minh nhân dân của người bán, nên thông số range cho hàm SUMIF của chúng ta chính là cột B ở bảng chính. Bây giờ, bạn có thể nhập dải ô B:B, hay nếu bạn đổi dữ liệu của mình thành bảng, bạn có thể sử dụng tên cột để thay thế: Main_table[ID]
Criteria – bởi vì chúng ta có tên người bán hàng ở bảng khác (bảng khác), nên chúng ta phải công thức VLOOKUP để tìm số chứng minh nhân dân tương ứng của người đó. Tên của người đó được nhập ở ô F2 trong bảng chính, vì thế chúng ta sử dụng công thức này để tìm kiếm nó:
VLOOKUP($F$2,Lookup_table,2,FALSE)
Tất nhiên, bạn có thể nhập tên trong tiêu chuẩn cần tìm của hàm VLOOUP, nhưng sử dụng tham chiếu ô tuyệt đối thì tốt hơn bởi vì nó tạo ra công thức chung có hiệu quả cho bất cứ tên nào được nhập vào ô xác định.
Sum range – đây là phần dễ nhất. Bởi vì các con số thể hiện doanh thu của chúng ta nằm ở cột C có tên “Doanh số”, chúng ta chỉ cần đặt Main_table[Sales].
Bây giờ, tất cả những gì bạn cần là tập hợp tất cả các phần của công thức lại và công thức SUMIF + VLOOKUP của bạn đã sẵn sàng:
=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])
Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học
Cách Sử Dụng Kết Hợp Hàm Vlookup Với Hàm Left
Việc kết hợp sử dụng các hàm với nhau trong Excel sẽ khiến cho công việc tính toán của bạn trở nên thuận lợi và tăng độ chính xác của phép tính lên rất nhiều. Vlookup là một hàm tra cứu dựa trên điều kiện cụ thể, hàm Left thì lại là một hàng lấy ký tự ở phía bên trái của một chuỗi ký tự. Khi ta kết hợp hai hàm Vlookup và Left này với nhau sẽ giúp cho hàm Vlookup thực hiện tra cứu một cách chính xác, nhanh chóng hơn.
HƯỚNG DẪN CÁCH SỬ DỤNG KẾT HỢP HÀM VLOOKUP VỚI HÀM LEFT Cú pháp và cách sử dụng của hàm Vlookup và hàm Left
Hàm Vlookup: Là hàm tra cứu và trả về kết quả theo hàng dọc.
– Cú pháp: =Vlookup(lookup_value, table_array, col_index_num, [range_lookup]
Trong đó:
+ lookup_value: Giá trị dùng để dò tìm
+ table_array: Vùng dữ liệu tra cứu
+ col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.
+ range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
Lưu ý: Đối với giá trị lookup_value ấn F4 3 lần, đối với giá trị table_array ấn F4 1 lần.
Ý nghĩa của việc sử dụng F4
– F4 (1 lần): để có giá trị tuyệt đối. Tuyệt đối được hiểu là cố định cột và cố định dòng ⇒ $cột$dòng
Ví dụ: $A$8 ⇒ cố định cột A và cố định dòng 8
– F4 (2 lần): để có giá trị tương đối cột và tuyệt đối dòng – Được hiểu là cố định dòng , không cố định cột ⇒ cột$dòng
Ví dụ: A$8 ⇒ cố định dòng 8, không cố định cột A
– F4 (3lần): để có giá trị tương đối dòng và tuyệt đối cột – Được hiểu là cố định cột, không cố định dòng ⇒ $cộtdòng
Hàm Left: Là hàm dùng để cắt lấy các ký tự bên trái của một chuỗi ký tự
+ Text: chuỗi ký tự cần cắt
+ n: số ký tự muốn cắt
Chúng ta có một bảng dữ liệu như hình dưới
Chúng ta sẽ sử dụng hàm LEFT để lấy Mã ngành từ cột Lớp.
Bước 1: Điền vào ô C6 công thức: =LEFT(B6,2). Ý nghĩa của công thức trên là cắt 2 ký tự ở ô B6
Bước 2: Sau đó ấn Enter. Kết quả hiện ra là 2 ký tự CN đã được cắt ra từ chuỗi ký tự CNTT1.
Bước 3: Các bạn kéo từ ô C6 xuống để các ô bên dưới tự động điền công thức và cho ra kết quả tương ứng.
Bước 4: Các bạn điền vào ô D6 công thức: =VLOOKUP($C6,$G$6:$H$10,2,0). $C6 là Giá trị dùng để tìm kiếm, ,$G$6:$H$10 là Vùng dữ liệu tra cứu, 2 là thứ tự cột cần lấy giá trị trong Vùng dữ liệu tra cứu, 0 là phạm vi tìm kiếm mang tính tuyệt đối.
Bước 5: Sau đó ấn Enter. Kết quả hiện ra là Tên ngành tương ứng với Mã Ngành. Các bạn lại kéo xuống để các ô bên dưới tự động điền công thức và cho ra kết quả tương ứng
Bước 6: Các bạn điền vào ô E6 công thức: =VLOOKUP(LEFT(B6,2),$G$6:$H$10,2,0). Chúng ta thay giá trị dùng để tìm kiếm từ $C6 thành LEFT(B6,2).
Bước 7: Sau đó ấn Enter và làm tương tự như bước 5.
Cách Dùng Hàm Vlookup, If Kết Hợp Trong Excel
Nói về hàm Vlookup thì đây là hàm tìm kiếm giá trị cần thiết trong một cột, sử dụng hàm Vlookup để tra cứu các thông tin thỏa mãn hàng và cột. Nếu như kết hợp cách dùng hàm Vlookup và IF với nhau chúng ta sẽ được một phép toán tìm kiếm thỏa mãn rất nhiều điều kiện bao gồm cả hàng và cột. Để có thể dễ hiểu hơn về cách dùng hàm Vlookup và IF kết hợp với nhau thì hướng dẫn sau đây sẽ làm giải thích cách dùng cho bạn.
Còn đây là chỉ tiêu của các nhóm được thể hiện ở dưới, bảng này rất quan trọng vì nếu sử dụng Vlookup chúng ta cần phải tra cứu thông tin từ đây.
Hàm IF ở đây có tác dụng gì:
Hàm IF trong ví dụ này sẽ chỉ ra được trường hợp nào thỏa mãn được doanh thu và cho ra kết quả đạt hay không đạt tại ô kết quả. Tuy nhiên hàm excel IF không thể truy xuất được vào dữ liệu ở bảng thứ 2 để biết được doanh thu của nhóm có đạt chỉ tiêu hay không, nhất là khi có đến 4 chỉ tiêu khác nhau. Nếu đây là một bảng gồm 100 nhân viên với hàng chục nhóm chắc bạn sẽ mất cả ngày để tạo xử lý.
Công thức:
– IF (Logical_test, Value_if_True, Value_if_False)
Giải thích:
– Nếu như “Logical_test” đúng thì kết quả hàm trả về là “Value_if_True”, ngược lại trả về “Value_if_False”.
Hàm Vlookup có tác dụng gì:
Như đã nói ở trên, hàm excel IF không thể biết được các nhân viên kia có đạt chỉ tiêu hay không thì hàm Vlookup sẽ có nhiệm vụ lọc ra giá trị thỏa mãn của từng thành viên trong nhóm riêng và sau đó sẽ xét tiếp đến hàm IF để cuối cùng cho ra.
Công thức:
– VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Giải thích :
– lookup_value: Giá trị dùng để dò tìm
– table_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4)
– col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.
– range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).
– C4,$C$16:$D$20,2,0: Tham chiếu cột nhóm nằm ở cột C và thứ tự dòng là 16 cho đến cột chỉ tiêu ở cột D với giá trị cuối cùng là dòng 20.
Lưu ý: để hiển thị ký tự dạng liệt kê $ bạn phải khoanh vùng như bình thường rồi nhấn F4.
Bước 2: Và khi nhấn Enter bạn sẽ thấy kết quả hiện ra nhân viên đầu tiên Nguyễn Ngọc Anh thuộc nhóm A có doanh Thu là 18, khi chiếu xuống nhóm (hình số 3) sẽ thấy nhóm A chỉ cần chỉ tiêu là 18 nên nhân viên Nguyễn Ngọc Anh được hiển thị Đạt.
Và tất nhiên bạn cũng có thể kiểm chứng lại mức độ chính xác khi xài cách dùng hàm Vlookup và IF kết hợp với nhau.
http://thuthuat.taimienphi.vn/cach-dung-ham-vlookup-if-ket-hop-24105n.aspx Ngoài ra, trong Excel còn rất nhiều hàm cơ bản khác mà bạn có thể tự học và thực hành, với mỗi hàm cơ bản trong Excel đều có những cú pháp và công dụng khác nhau để bạn lựa chọn và kết hợp vào các bài toán của mình, việc nhớ được hết các hàm cơ bản trong Excel đòi hỏi bạn phải cần cù và trí nhớ tốt.
Cập nhật thông tin chi tiết về Hướng Dẫn Cách Kết Hợp Hàm Vlookup Với Hàm If Trong Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!