Sử dụng hàm VLOOKUP thần thánh qua những ví dụ cụ thể

 

https://ub.com.vn/f/threads/cach-su-dung-ham-vlookup-than-thanh-qua-nhung-vi-du-cu-the.270754/

 

Hàm VLOOKUP là một hàm đặc biệt hữu ích trong Excel giúp bạn dò và trả về dữ liệu tương ứng. Điện máy XANH sẽ hướng dẫn chi tiết bạn cách sử dụng hàm VLOOKUP trong bài viết sau nhé.​

Ví dụ trong bài được thực hiện trên phiên bản Excel 2016. Bạn có thể áp dụng tương tự trên các phiên bản Excel khác như: 2007, 2010, 2013, 2017 và Microsoft Excel 365.

1. Hàm VLOOKUP là gì?​

Hàm VLOOKUP được sử dụng khi bạn cần dò tìm dữ liệu trong một bảng, một phạm vi theo hàng dọc và trả về dữ liệu tương ứng theo hàng ngang tương ứng.

Trong thực tế, hàm VLOOKUP cực kỳ thông dụng khi tìm tên sản phẩm, đơn giá, số lượng,.. dựa trên mã vạch, mã sản phẩm,... hoặc tìm tên nhân viên, xếp loại nhân viên dựa trên các tiêu chí trên.

Ngược lại, khi bạn cần dò tìm dữ liệu trong một bảng, một phạm vi theo hàng ngang và trả về dữ liệu tương ứng theo hàng dọc thì hãy sử dụng hàm HLOOKUP.

LOOKUP là Look Up nghĩa là tìm kiếm trong tiếng Anh. V là viết viết tắt của Vertical - hàng dọc và H là viết tắt của Horizontal - hàng ngang.

2. Công thức hàm VLOOKUP​

Công thức hàm VLOOKUP:
=VLOOKUP(Lookup_value, Table_array, Col_index_ num, Range_lookup)
Trong đó:

• Lookup_value: Giá trị cần dò tìm, có thể điền giá trị trực tiếp hoặc tham chiếu tới một ô trên bảng tính.
• Table_array: Bảng giới hạn để dò tìm.
• Col_index_num: Số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
• Range_lookup: tìm kiếm chính xác hay tìm kiếm tương đối với bảng giới hạn, nếu bỏ qua thì mặc định là 1.

Nếu Range_lookup = 1 (TRUE): dò tìm tương đối.
Nếu Range_lookup = 0 (FALSE): dò tìm chính xác.
Nếu bạn copy công thức cho các ô dữ liệu khác, bạn cần sử dụng dấu $ để cố định Table_array đề giới hạn dò tìm bằng cách thêm trực tiếp trước khai báo (VD: $H$6:$J$13), cột hoặc sử dụng nút F4 sau khi chọn bảng.

3. Ví dụ hàm VLOOKUP​

Bạn có thể truy cập vào đây để tải về các file ví dụ bên dưới

Ví dụ 1: Tính phụ cấp theo chức vụ

Do tình hình Covid-19, công ty quyết định phụ cấp cho nhân viên theo chức vụ tương ứng như bảng 2 (B16:C21). Lúc này, dựa vào danh sách nhân viên cùng với chức vụ có sẵn ở bảng 1, ta sẽ xác định mức phụ cấp tương ứng.

Cách thực hiện là bạn sẽ dò tìm giá trị của chức vụ của nhân viên tại bảng 1, sau đó dò tìm tại cột 1 trong bảng 2 từ trên xuống dưới. Khi bạn tìm thấy giá trị, bạn sẽ lấy giá trị tương ứng tại cột 2 của bảng 2 để điền vào bảng 1.

Với một danh sách nhân viên vài trăm, một ngàn người thì bạn không thể làm thủ công như vậy. Đó chính là cách hàm VLOOKUP phát huy tác dụng.

Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$16:$C$21,2,0)

• Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
• 2 là số thứ tự của cột dữ liệu.
• Range_lookup = 0 (FALSE) để dò tìm chính xác.

Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã hoàn thành việc tính phụ cấp theo chức vụ nhanh chóng.

Ví dụ 2: Xếp loại học sinh theo điểm số

Sau kỳ kiểm tra, ta có kết quả bài thi tương ứng với học sinh như bảng 1. Ta cần xếp loại theo điểm số dựa trên bảng 2 (B11:C15).

Tại ô E4, bạn điền công thức: =VLOOKUP(D4,$B$11:$C$15,2,1)

• Dấu $ được sử dụng để cố định các dòng, các cột của bảng 2 khi bạn copy công thức sang các ô khác.
• 2 là số thứ tự của cột dữ liệu.
• Range_lookup = 1 (TRUE) để dò tìm điểm số có giá trị gần nhất.

Excel sẽ lấy điểm số ở cột D4 và dò trong bảng 2. Khi thấy giá trị gần nhất của D4 trong bảng (ở đây là 8.5), Excel sẽ trả về kết quả tương ứng ở cột 2 là Giỏi.

Sau đó, bạn chỉ cần copy công thức cho các ô khác hoặc sử dụng Flash Fill và bạn đã hoàn thành việc xếp loại học sinh theo điểm số nhanh chóng.

4. Các lỗi thường gặp khi dùng hàm VLOOKUP​

Lỗi #N/A​

Một ràng buộc của hàm VLOOKUP là nó chỉ có thể tìm các giá trị trên cột ngoài cùng bên trái trong Table_array, nếu không sẽ xuất hiện lỗi #N/A. Lúc này bạn hãy cân nhắc sử dụng hàm INDEX kết hợp hàm MATCH.

Như ví dụ bên dưới, Table_array là A2:C10, nên hàm VLOOKUP sẽ tìm kiếm trong cột A. Để sửa trường hợp này, bạn đổi Table_array thành B2:C10, hàm VLOOKUP sẽ tìm kiếm trong cột B.

Ngoài ra, nếu không tìm thấy kết quả khớp chính xác sẽ trả về hàm sẽ trả về lỗi #N/A do dữ liệu không có trong Table_array. Lúc này bạn có thể sử dụng hàm IFERROR để đổi #N/A thành giá trị khác.

Như ví dụ bên dưới, "Rau muống" không có trong bảng dò tìm nên hàm VLOOKUP sẽ không tìm thấy.

Nếu bạn chắc chắn rằng dữ liệu có trong Table_array của mình và hàm VLOOKUP không tìm được được, hãy kiểm tra lại rằng các ô dữ liệu được tham chiếu không có khoảng trắng ẩn hoặc ký tự không in. Ngoài ra, hãy đảm bảo rằng các ô dữ liệu tuân theo đúng định dạng.

Lỗi #REF!​

Nếu Col_index_num lớn hơn số cột trong Table_array, bạn sẽ nhận được giá trị lỗi #REF!. Lúc này, bạn hãy kiểm tra lại công thức để đảm bảo Col_index_num bằng hoặc nhỏ hơn số cột trong Table_array.

Như ví dụ bên dưới, Col_index_num là 3, trong khi Table_array là B2:C10 chỉ có 2 cột.

Lỗi #VALUE!​

Nếu Col_index_num nhỏ hơn 1 trong công thức, bạn sẽ nhận giá trị lỗi #VALUE!.

Trong Table_array, cột 1 là cột tìm kiếm, cột 2 là cột đầu tiên ở bên phải của cột tìm kiếm, v.v... Vì vậy khi xuất hiện lỗi này, hãy kiểm tra lại giá trị Col_index_number trong công thức.

Như ví dụ bên dưới, Col_index_num bằng 0 dẫn đến việc xuất hiện lỗi #VALUE!.

Lỗi #NAME?​

Lỗi #NAME? xuất hiện khi Lookup_value thiếu dấu ngoặc kép ("). Để tìm kiếm giá trị định dạng văn bản (Text), bạn dùng dấu ngoặc kép để Excel có thể hiểu công thức.

Như ví dụ bên dưới, Cải xoăn không bỏ vào dấu ngoặc kép (") sẽ làm xuất hiện lỗi #NAME?. Bạn sửa lỗi bằng cách thay Cải xoăn thành "Cải xoăn".

5Một số lưu ý khi dùng hàm VLOOKUP​

Sử dụng tham chiếu tuyệt đối​

Trong quá trình copy công thức, hãy biến Table_array hoặc Lookup_value thành tham chiếu tuyệt đối bằng cách đặt dấu đô la ($) trước các cột và hàng để công thức không bị thay đổi.

Như ví dụ dưới, ta có công thức tại ô C13 là =VLOOKUP(B13,$B$2:$C$10,2,0). Khi copy công thức cho ô C4 Table_array sẽ giữ nguyên.

Nếu không chuyển đổi thành tham chiếu tuyệt đối, Lookup_value hoặc Table_array sẽ bị thay đổi, làm kết quả tìm kiếm bị sai lệch.

Như ví dụ dưới, ta có công thức tại ô C13 là =VLOOKUP(B13,B2:C10,2,0). Khi copy công thức cho ô C4 Table_array sẽ biến thành =VLOOKUP(B14,B3:C11,2,0).

Không lưu trữ giá trị số dưới dạng văn bản​

Nếu trong Table_array, dữ liệu số đang để dưới dạng văn bản và Lookup_value lại là dạng số thì hàm VLOOKUP sẽ trả về lỗi #N/A.

Như ví dụ dưới, ta có dữ liệu tại ô A2:A5 đang ở dạng văn bản nhưng Lookup_value tại ô A8 đang ở dạng số.

Trong trường hợp này, hãy chuyển định dạng ô A2:A5 thành dạng số và hàm sẽ trả về kết quả bình thường.

Bảng dò tìm chứa những giá trị bị trùng​

Nếu bảng biểu của bạn chứa nhiều giá trị trùng nhau, hàm VLOOKUP sẽ trả về kết quả đầu tiên mà nó tìm thấy từ trên xuống dưới.

Như ở ví dụ dưới, trong bảng ta có 2 giá trị ứng với Táo là 97 và 23. Hàm VLOOKUP sẽ trả về kết quả 97 vì đó là giá trị đầu tiên nó tìm thấy

Giải pháp 1: Nếu bạn muốn loại bỏ giá trị trùng lặp, bạn bôi đen bảng dò tìm và chọn Data > Remove Duplicates

Giải pháp 2: Sử dụng Pivot Table để lọc ra danh sách kết quả

Nguồn: Điện Máy Xanh​

 

 

 

 

 

 

 

 

Dánh số thứ tự tự dộng trong excel

 

https://ub.com.vn/f/threads/cach-danh-so-thu-tu-tu-dong-trong-excel-sieu-nhanh-don-gian.270749/

 

Công việc thống kê số liệu bằng Excel sẽ vô cùng đơn giản khi bạn biết đến cách đánh số thứ tự. Giúp bạn quản lý thông tin dễ dàng hơn, hãy theo dõi bài viết để biết 4 cách đánh số thứ tự tự động trong Excel siêu nhanh, đơn giản.​

I. Cách đánh số thứ tự trong Excel bằng cách kéo chuột​

1. Hướng dẫn nhanh​

Mở tệp Excel của bạn, nhập số thứ tự cho 2 ô đầu tiên > Dùng chuột phải bôi đen 2 ô vừa điền số > Đặt chuột ở góc ô kéo xuống mức thứ tự bạn muốn.

2. Hướng dẫn chi tiết​

Bước 1: Mở tệp Excel của bạn, nhập số thứ tự cho 2 ô đầu tiên > Dùng chuột phải bôi đen 2 ô vừa điền số.

Đánh số thứ tự



Bước 2: Đặt chuột ở hình vuông nhỏ tại góc ô kéo xuống mức thứ tự bạn muốn.

Kéo chuột phải

II. Cách đánh số thứ tự trong Excel bằng công thức​

1. Hướng dẫn nhanh​

Mở tệp Excel của bạn, nhập số thứ tự cho ô đầu tiên > Nhập công thức = vị trí ô vừa điền + 1 vào ô tiếp theo > Dùng chuột đặt ở góc ô đó và kéo xuống số thứ tự bạn muốn.

2. Hướng dẫn chi tiết​

Bước 1: Mở tệp Excel của bạn, nhập số thứ tự cho ô đầu tiên > Nhập công thức = vị trí ô vừa điền + 1 vào ô tiếp theo.

Ví dụ: Bạn nhập số thứ tự ô đầu tiên tại vị trí A1 thì nhập công thức ở ô bên dưới =A1+1 như hình bên dưới. Bạn có thể thực hiện tương tự tại vị trí khác.

Nhập hàm



Bước 2: Dùng chuột đặt ở hình vuông nhỏ tại góc ô đó và kéo xuống số thứ tự bạn muốn.

Kéo chuột phải

III. Cách đánh số thứ tự trong Excel bằng Editing chọn Fill/Series​

1. Hướng dẫn nhanh​

• Mở tệp Excel của bạn, nhập số thứ tự cho ô đầu tiên > Chọn Fill > Chọn Series.
• Chọn Rows nếu đánh số theo hàng, chọn Columns nếu đánh số theo cột.
• Chọn Linear để đánh sô kiểu tăng dần.
• Nhập số thứ tự bắt đầu vào mục Step value > Nhập số thứ tự cuối cùng vào mục Stop value > Chọn OK để hoàn tất và xem kết quả.

2. Hướng dẫn chi tiết​

Bước 1: Mở tệp Excel của bạn, nhập số thứ tự cho ô đầu tiên > Chọn Fill > Chọn Series.

Editing



Lưu ý: Có 4 loại đánh số

• Linear: Đánh số theo kiểu tăng dần.
• Growth: Kiểu lũy tiến.
• Date: Kiểu ngày tháng.
• AutoFill: Kiểu tự động điền.

Bước 2:

• Chọn Rows nếu đánh số theo hàng, chọn Columns nếu đánh số theo cột. Tùy vào mục đích của bạn để chọn Rows hoặc Columns nhé!
• Trong hướng dẫn này mình chọn Linear để đánh số kiểu tăng dần.
• Nhập số thứ tự bắt đầu vào mục Step value > Nhập số thứ tự cuối cùng vào mục Stop value > Chọn OK.

Chọn editing



Sau đó màn hình sẽ hiển thị dãy số thứ tự bạn đã chỉnh sửa.

Kéo chuột phải

IV. Cách đánh số thứ tự trong Excel bằng hàm Subtotal​

Lưu ý:

• Cách này được áp dụng khi sử dụng chức năng Filter.
• Việc sử dụng hàm Subtotal giúp cho số thứ tự của giá trị được chọn sẽ đánh số lại từ đầu khác với hàm CoutnA là các số thứ tự của từng giá trị sẽ bị giữ nguyên khi lọc.
• Chỉ áp dụng được trên cột, không thể áp dụng được trên hàng.

1. Cú pháp của hàm​

Ở cách này mình sẽ thực hiện lồng 2 hàm lại với nhau đó chính là hàm IF và hàm Subtotal với cú pháp như sau:

=IF(logical_test; [value_if_true]; [value_if_false]; Subtotal(funtion_num; ref1;...))

Trong đó:

• logical_test: Điều kiện của hàm IF.
• [value_if_true]: Giá trị trả về nếu điều kiện đúng.
• [value_if_false]: Giá trị trả về nếu điều kiện sai.
• funtion_num: Hàm được hiểu theo giá trị số (Ở đây là số 3 tương đương với hàm CountA).
• ref1;...: Vùng chứa số thứ tự.

2. Ví dụ và cách thực hiện​

Mình có 1 ví dụ như sau:

Bảng này đã được mình đánh số thứ tự từ trên xuống dưới bằng 2 hàm CountA và Subtotal với 1 vài ô trống không có số thứ tự. Sau đây hãy xem hàm Subtotal làm việc nhé!

Ví dụ đánh Số thứ tự



Bước 1: Nhập hàm theo cú pháp B3 = IF(C3="";"";SUBTOTAL(3;$C$3:C3)).

Giải thích hàm (Mình sẽ giải thích hàm này từ trong ra ngoài cho dễ hiểu nhé)

Đối với hàm SUBTOTAL(3;$C$3:C3) ta có:

• funtion_num = 3: Được hiểu là hàm CountA trong Subtotal.
• ref1 = $C$3:C3: Là vùng đã được đánh số thứ tự (Lưu ý: Luôn phải khóa giá trị đầu tiên, ở đây là B2, nếu không khóa thì khi chạy hàm sẽ bị lỗi đối với những ô bên dưới).

Đối với hàm IF(C3="";"";SUBTOTAL(3;$C$3:C3)) ta có:

• C3="";"": Có nghĩa là nếu giá trị ở ô C3 là rỗng thì Số thứ tự cũng sẽ là rỗng.
• SUBTOTAL(3;$C$3:C3): Nếu ô C3 không rỗng thì sẽ sử dụng hàm Subtotal.

Nhập hàm theo cú pháp B3 = IF(C3="";"";SUBTOTAL(3;$C$3:C3))



Bước 2: Thực hiện lọc dữ liệu bằng Filter và xem sự thay đổi khi sử dụng hàm Subtotal.

Trên đây là hướng dẫn 3 cách đánh số thứ tự tự động trong Excel siêu nhanh, đơn giản trên máy tính giúp bạn dễ dàng trong việc thống kê số liệu. Nếu cảm thấy bài viết hữu ích, hãy chia sẻ cho bạn bè và đừng quên để lại bình luận phía dưới nhé!

 

 

 

 

 

 

 

 

Tự dộng tô màu cho các giá trị trùng lặp trên Excel

 

https://ub.com.vn/f/threads/huong-dan-tu-dong-to-mau-cho-cac-gia-tri-trung-lap-tren-excel.270748/

 

Trong quá trình làm việc với ứng dụng Excel, chúng ta thường phải đối mặt với những dữ liệu phức tạp và đôi khi hay bị lặp lại. Điều này sẽ gây stress rất nhiều cho bạn trong công việc.

Để tránh khỏi những sai sót không đáng có thì việc phân tách, định dạng dữ liệu là điều khá quan trọng, và mình nghĩ là cần thiết.

Vâng, và trong bài viết ngày hôm nay mình sẽ giới thiệu với các bạn cách tô màu các giá trị trùng lặp trên file Excel bằng công cụ Conditional Formatting.

Cách thiết lập tự động tô màu các giá trị giống nhau trong Excel​

+ Bước 1: Bạn mở file Excel của mình lên => sau đó click chọn vào ô giá trị cần tô màu như hình bên dưới.

+ Bước 2: Bạn nhấn Ctrl + A để bôi đen toàn bộ văn bản => rồi click vào Conditional Formatting => và chọn Manage Rules.

+ Bước 3: Tại hộp thoại Conditional Formatting Rules Manager => bạn click New Rule.

+ Bước 4: Sau đó tại hộp thoại New Formatting Rule, bạn chọn Format only cells that contain định dạng ô theo giá trị của nó.

=> Rồi tại Format only cells with bạn chọn Cell value, và equal to tức là giá trị trong ô phải bằng với giá trị mà bạn mong muốn, tiếp theo bạn nhập giá trị nội dung tương ứng ở ô kế đó rồi click Format.
Ví dụ lúc nảy ở bước một bạn chọn Hà Nội để tạo màu nền, thì tại đây bạn nhập Hà Nội nhé.

+ Bước 5: Hộp thoại Format Cells xuất hiện, bạn click chọn Tab Fill, và click tông màu mà mình muốn tô ở ô nội dung, rồi click OK => và OK để đóng hộp thoại New Formatting Rule.

+ Bước 6: Sau khi định dạng màu sắc cho Text Hà Nội xong, tại hộp thoại Conditional Formatting Rules Manager bạn click Apply => và OK để lưu kết quả và thoát khỏi hộp thoại.

Ok vậy là xong, bây giờ tất cả các ô chứa nội dung là Hà Nội đều sẽ được tô màu như lựa chọn rồi nhé.

Ngoài ra, từ bây giờ khi bạn nhập ký tự Hà Nội hoặc hà nội bỏ Capslock, thì Excel cũng sẽ tự động tô màu lên chúng mà không cần bạn phải mất thời gian Resize lại nữa.

Với cách sử dụng công cụ Conditional Formatting thì bạn có thể tô màu tương tự với nhiều giá trị có khả năng lặp lại, để dễ dàng phân biệt chúng trong quá trình làm việc của mình.

Rất là tiện lợi cho quá trình làm việc của bạn phải không ạ !

Nguồn: Blog Chia sẻ kiến thức​

 

 

 

 

 

 

 

 

Cách tách tên dùng VBA

https://www.facebook.com/groups/cungonthithue/permalink/2626998244198563/

#excel
#tachten
Có rất nhiều cách tách Tên, đây là cách Tách tên dùng vba anh sưu tầm được trên mạng. Cách thực hiện:
1. Mở file excel
2. Ấn Alt+ F11, chọn Insert- Module, sau đó dán đoạn code sau vào cửa sổ Module
Function TEN(S As String) As String
S = WorksheetFunction.Trim(S)
TEN = Split(S, " ")(UBound(Split(S, " ")))
End Function
3. Đóng cửa sổ Module
4. Giờ muốn tách tên thì gõ =Ten(ô chứa họ tên)

The old house were ................... down to make way for a block of flats.

https://moon.vn/hoi-dap/the-old-house-were-down-to-make-way-for-a-block-of-flats-6916

ID [6916]

The old house were ................... down to make way for a block of flats.

A.	put.
B.	hit.
C.	banged.
D.	knocked.

Đáp án D

haihm: chi co knock down hop nghia: pha huy , ngoai ra knock down con co nghia thu 2 la 'tong vao ai '(tai nan) . cac dap an khac khong dung nghia put down: dan ap, hit down : danh , dam, bang down : no tung , tieng no

quangtrung94tq: không thể là A vì put down là tước quyền,B và C không đúng vì hit là đánh,đấm,xúc phạm.bang là sập.chỉ có D knocked down là tháo tung ra hợp với nghĩa của câu

	vivivann knock down a house: dỡ bỏ một ngôi nhà . 27/11/2011 27 Tháng 11 lúc 20:8
	jibber0403 knock down a house ohhhhhh . 11/12/2011 11 Tháng 12 lúc 0:18
	killua9x knock down gan giong kieu ha noc ao tong quen anh y de nho hiii . 22/12/2011 22 Tháng 12 lúc 0:4
	pepuzzpanda la bang moi dung vi bang dowm co nghia la pha sap ma . 3/3/2012 3 Tháng 3 lúc 17:39
	davinci e nghi rut gon chi o menh de quan he thui chu . 16/3/2012 16 Tháng 3 lúc 22:8
	lanlila tim nghia ntn a . 29/4/2012 29 Tháng 4 lúc 9:49
	ktl94 t dịch câu này nhé hì chưa thấy ai dịch: cái nhà cũ đã bị tháo dỡ để thực hiện bản phác thảo về dãy nhà.nghe kỳ kỳ ai dịch lại cho t nhé, cảm ơn:) . 19/6/2012 19 Tháng 6 lúc 8:35
	pyn93 minh nghi? la c vi co nghia dap vo? ma ! . 12/7/2012 12 Tháng 7 lúc 19:48
	haihm knock down : pha huy nha thui ko the bang dk . 12/7/2012

miloboybaby: DAP AN D LA DUNG 1 CACH HOAN HAO

https://moon.vn/hoi-dap/the-old-houses-were-down-to-make-way-for-a-block-of-flats-308134

ID [308134]

The old houses were ______ down to make way for a block of flats.

A.	banged
B.	hit
C.	knocked
D.	put

Đáp án C Giải thích: cụm từ to knock down mang nghĩa phá đổ (nhà), dỡ (nhà).

https://vi4.ilovetranslation.com/1caoXFCJ_01=d/

https://vi4.ilovetranslation.com/DCsa_dOqf_W=d/

https://loga.vn/cau-hoi/the-old-houses-were-down-to-make-way-for-a-block-of-flats-33624

http://www.tienganh.com.vn/showthread.php?26497-S%E1%BB%ADa-l%E1%BB%97i-sai-%C4%91i-b%C3%A0-con/page6