Bạn đang xem bài viết Nhóm Hàm Tham Chiếu Trong Excel (Bài 5) được cập nhật mới nhất trên website Rafs.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.
Trong bài học này, chúng tôi sẽ trình bày 3 hàm trong nhóm hàm tham chiếu gồm hàm VLOOKUP, hàm HLOOKUP và hàm INDEX. Công dụng, cách sử dụng và ví dụ chúng tôi sẽ trình bày ngay bên dưới.
Nhóm hàm tham chiếu trong excel – Hàm VLOOKUP
Hàm VLOOKUP được sử dụng để trích xuất thông tin từ bảng tham chiếu theo chiều dọc. Hãy quan sát bảng tham chiếu 1 (Bảng chức vụ) ở hình bên trên để thấy rõ cách thể hiện thông tin của bảng này.
Cú pháp hàm VLOOKUP trong excel
VLOOKUP(Trị dò, Bảng tham chiếu, Chỉ số cột tham chiếu, Cách dò)Trong đó: Trị dò thuộc bảng chính; Chỉ số cột tham chiếu tính từ trái qua phải và bắt đầu từ 1; Cách dò là 0 nếu Trị dò là chuỗi hoặc ngày tháng năm, Cách dò là 1 nếu Trị dò là số.
Ví dụ sử dụng hàm VLOOKUP trong excel (Lập công thức cho cột CHỨC VỤ của bảng Danh sách nhân viên công ty ABC) dựa vào bảng CHỨC VỤ
B9 là Mã chức vụ ( Trị dò); B3:C6 là bảng chức vụ ( Bảng tham chiếu luôn luôn dùng địa chỉ tuyệt đối); 2 là Chỉ số của cột tham chiếu (cột Chức vụ trong bảng chức vụ); vì B9 chứa chuỗi nên Cách dò là 0.
Kết quả sau khi hoàn thành
Nhóm hàm tham chiếu trong excel – Hàm HLOOKUP
Nếu VLOOKUP dò tìm theo chiều dọc thì HLOOKUP dò tìm theo chiều ngang (Bảng khu vực)
Cú pháp hàm HLOOKUP trong excel
HLOOKUP(Trị dò, Bảng tham chiếu, Chỉ số dòng tham chiếu, Cách dò)Ví dụ sử dụng hàm HLOOKUP trong excel (Lập công thức cho cột KHU VỰC của bảng Danh sách nhân viên công ty ABC) dựa vào bảng KHU VỰC
Kết quả sau khi hoàn thành
Nhóm hàm tham chiếu trong excel – Bài tập thực hành
Cho bảng tính như hình và lập công thức cho những cột tô màu vàng
Câu 1: SỐ NGÀY THUÊ = NGÀY ĐI – NGÀY ĐẾN
Câu 2: TIỀN PHÒNG = Đơn giá phòng (Tra trong bảng ĐƠN GIÁ PHÒNG) * SỐ NGÀY THUÊ
Câu 3: Dựa vào 2 ký tự cuỗi của MÃ THANH TOÁN và tra trong bảng ĐƠN GIÁ KHẨU PHẦN ĂN để lập công thức cho cột ĐƠN GIÁ KHẨU PHẦN ĂN
Câu 4: TIỀN ĂN = SỐ NGÀY THUÊ * ĐƠN GIÁ KHẨU PHẦN ĂN
Câu 5: THÀNH TIỀN = TIỀN PHÒNG + TIỀN ĂN
Câu 6: TỶ LỆ GIẢM dựa vào SỐ NGÀY THUÊ và tra trong bảng TỶ LỆ GIẢM
Câu 7: PHẢI TRẢ = THÀNH TIỀN – TỶ LỆ GIẢM
Hàm Vlookup Trong Excel, Cách Dùng Hàm Vlookup Trong Excel, Hàm Tìm Ki
Cách sử dụng hàm Vlookup trong Excel, cú pháp cũng như các ví dụ sẽ được chúng tôi chia sẻ tất tần tật trong bài viết sau đây. Các bạn cùng tham khảo để sử dụng hàm Vlookup cho đúng.
Cách thực hiện này giúp bạn:– Dùng Vlookup hiệu quả– Biết cách sửa lỗi hàm VLOOKUP– Biết cách kết hợp hàm Vlookup với các hàm khác
Hàm VLOOKUP trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc, đây có thể nói là hàm excel khá thông dụng và tiện lợi với những người hay sử dụng Excel để thống kê, dò tìm dữ liệu theo cột chính xác. Trong khuôn khổ thủ thuật , chúng tôi sẽ trình bày tới bạn đọc 2 cách dò tìm cơ bản của hàm này.
Ví dụ hàm Vlookup, bài tập hàm Vlookup trong Excel
cách sử dụng hàm Vlookup trong Excel 2016, 2013, 2010, 2007
I. Cú pháp hàm Vlookup
Lưu ý cú pháp hàm: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])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).
II. Ví dụ về hàm Vlookup trong Excel 1. Cách dò tìm tương đối:
VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách dưới:
Ví dụ về hàm Vlookup excel
Ta sử dụng công thức ở cột D6 là: =VLOOKUP(C6,$C$16:$D$20,2,1)Kết quả thu được:
Kết quả khi dùng hàm vlookup
Với cách dò tìm tuyệt đối thì bạn sẽ tìm được chi tiết hơn cách dò tìm tương đối.VD: Điền thông tin Quê quán và trình độ của các nhân viên vào bảng căn cứ vào mã nhân viên đã cho tương ứng phía dưới.
Ví dụ dò tìm tuyệt đối khi dùng hàm vlookup
Để điền thông tin quê quán của nhân viên ta dùng công thức Vlookup cho ô E6 như sau: =VLOOKUP(A6,$D$12:$F$17,2,0)A6 là giá trị đem dò tìm$D$12:$F$17 là bảng dò tìm2 : số thứ tự cột dữ liệu trên bảng dò0 : Kiểu dò tìm chính xác
Cách sử dụng dò tuyệt đối với hàm Vlookup
Tương tự để điền vào ô trình độ của nhân viên ta làm như sau:
Với ô F6 có công thức : =VLOOKUP(A6,$D$12:$F$17,3,0)
Kết quả dùng hàm Vlookup dò giá trị tuyệt đối
III. Hàm Vlookup kết hợp hàm Hlookup, Left, Right, Match
Ở nội dung trên chúng tôi đã hướng dẫn bạn tìm hiểu cơ bản về hàm Vlookup, để giúp các bạn hiểu hơn và áp dụng thực tế hơn, bây giờ chúng tôi sẽ đưa ra một tình huống bài tập cụ thể như sau. Bài toán này là sự kết hợp giữa hàm Vlookup và các hàm trong Excel như hàm Hlookup, hàm Left, hàm Right và hàm Match.
Yêu cầu cần giải quyết:
Câu 1: Căn cứ vào 2 ký tự bên trái và 2 ký tự bên phải của Mã SP trong Bảng 1, hãy tra trong Bảng 2 đề điền giá trị cho cột Tên Hàng – Tên Hãng Sản xuất (Tính dữ liệu cho cột C5:C10).VD: KD-NĐ Tức là gạo Khang Dân – Nam Định.Câu 2: Hãy điền Đơn Giá cho mỗi mặt hàng đựa vào Mã SP ở Bảng 1 và tra ở Bảng 2. (Tính dữ liệu cho cột D5:D10).Câu 3: Tính thành tiền = Số lượng * Đơn giá
Câu 3: Tính thành tiềnỒ, câu hỏi này thường xuất hiện trong những bài toán tính toán dữ liệu như bảng lương, chi phí, tổng thành tiền, … Khá là dễ rồi phải không các bạn.Công thức tính thành tiền sẽ như sau, Tại ô F5 các bạn nhập E5=+D5*E5
IV. Sửa lỗi #NA hàm Vlookup Lỗi #NA là gì?
Lỗi #NA được trả về trong công thức excel khi không tìm thấy giá trị thích hợp
Khi sử dụng hàm Vlookup, chúng ta gặp lỗi #NA khi không tìm thấy điều kiệm tìm kiếm trong vùng điều kiến, chính xác ở đây là cột đầu tiên trong vùng điều kiện của hàm Vlookup
Cách sửa lỗi #NA trong hàm Vlookup
Trong Excel, hàm Vlookup được sử dụng phổ biến giúp bạn có thể thực hiện hàm tìm kiếm giá trị, trả về phương thức hàng dọc tốt, nhanh chóng. Hiện nay, bạn có thể áp dụng một trong hai cách dò hàm Vlookup.
Với hai cách dò tìm tương đối và tuyệt đối của hàm Vlookup, hàm dò tìm giá trị theo cột bạn dễ dàng thống kê dữ liệu trên bảng tính Excel. Vlookup giúp cho việc thống kê các chi tiết để làm báo cáo lọc ra các danh sách cần thiết, công việc của bạn sẽ chính xác và rút ngắn thời gian hơn. Đây là hàm được dùng khá nhiều trong quá trình thao tác trên bảng tính.
Để học tốt cũng như hiểu rõ hơn về hàm tìm kiếm bạn có thể tham khảo hàm Hlookup trong Excel đã được chúng tôi giới thiệu. Hàm Hllookup giúp bạn tìm kiếm và tham chiếu theo cột, hoặc theo dòng, Khi bạn sử dụng hàm này, nếu thỏa mãn điều kiện đặt ra.
Bên cạnh hàm tìm kiếm Vlookup trong Excel, bạn có thể tham khảo Hàm COUNTIF trong Excel, Hàm COUNTIF là một hàm đếm khá tiện lợi, với những điều kiện ban đầu, Hàm COUNTIF sẽ cho bạn những kết quả chính xác nhất.
Hàm tính toán cũng có khá nhiều hàm khác nhau trong Excel, và Hàm SUMIF là một trong những hàm được nhiều người sử dụng, Hàm SUMIF là hàm tính tổng có điều kiện, giúp bạn tính tổng các cột, các hàng riêng lẻ mà không phải tính tổng tất cả các dữ liệu trong file Excel
https://thuthuat.taimienphi.vn/huong-dan-su-dung-ham-vlookup-trong-excel-633n.aspx Sử dụng Excel không thể không nhắc tới Hàm MID, đây là hàm cơ bản trong Excel hỗ trợ cắt những chuỗi ký tự trong một dãy ký tự cho trước trong bảng tính Excel, hàm cơ bản MID được sử dụng rất nhiều trong các trường hợp cần lấy ra những ký tự đặc biệt nhất trong các chuỗi ký tự dài có thể lên tới hàng trăm ký tự
Cách Dùng Hàm Vlookup Trong Excel L Bài Tập Và Video Hướng Dẫn
Trong bài viết này, Trường sẽ tập trung vào 02 điểm chính sau:
Cú pháp và cách sử dụng hàm
Giải Bài tập ví dụ áp dụng để tìm đơn giá nhằm lập bảng nhập xuất nông sản.
Link download bài tập thực hành
Các bạn chỉ cần dành thời gian đọc và thực hành bài tập được giao, ắt hẳn sẽ thành thạo. Mọi ý kiến góp ý hoặc cần hỗ trợ, bạn gửi cho Trường tại: webkynang.vn@gmail.com
1. Định nghĩa và cú pháp hàm Vlookup
Hàm Vlookup là hàm tìm kiếm theo cột.
Vlookup ( [Lookup_value] ; [Table_array] ; [Col_index_num] ; [Range_lookup])
Lookup_value: Giá Trị dùng để tìm kiếm
Table_array: Vùng điều kiện
Col_index_num: Cột chỉ định hay còn được gọi là số thứ tự cột chứa giá trị được tìm kiếm tính từ cột đầu tiên của vùng dữ liệu
Range_lookup: Kiểu tìm kiếm – gồm có 2 kiểu 0 và 1
Mời bạn xem bài: Cách tìm kiếm dữ liệu từ sheet, file khác
2. Ví dụ thực tế về cách dùng hàm vlookup trong excel 2007 2010 2013
Ví dụ cách sử dụng hàm tìm kiếm trong Excel
Yêu cầu: Căn cứ vào Bảng tồn kho đầu kỳ (bảng đầu tiên). Hãy hoàn thành Bảng kê Mua – Bán hàng trong kỳ (bảng thứ 2).
2.1. Phân tích yêu cầu và dữ liệu để vận dụng – kết hợp hàm
Để hoàn thành được Bảng kê Mua – Bán hàng thì chúng ta cần phải tìm được Đơn giá nhập và Đơn giá xuất.
Sau đó căn cứ vào số lượng bán hàng từng lần chúng ta sẽ tính được Trị giá Nhập và Xuất của từng lần giao dịch.
Chúng ta có thể tóm tắt mẫu biểu và yêu cầu sau:
Như vậy, các dữ liệu về tên, đơn giá nhập – xuất của từng mặt hàng chúng ta có thể lấy từ Bảng Tồn kho đầu kỳ.
Phần còn lại là lấy SL Nhập Xuất rồi nhân với đơn giá nhập – xuất tương ứng.
Tên hàng được sử dụng trong bảng nhập – xuất hàng đều giống với tên được sử dụng trong Bảng Tồn kho đầu kỳ.
Bên cạnh đó, tên các mặt hàng và đơn giá với mặt hàng tương ứng đều ở dạng cột. Do đó để có thể lấy đơn giá nhập – xuất từ bảng tồn kho đầu kỳ để nhập vào bảng nhập xuất hàng trong kỳ thì chúng ta hãy sử dụng Hàm tìm kiếm theo cột
=Vlookup ( Điều kiện để tìm kiếm, Vùng tìm kiếm, Số thứ tự cột chứa giá trị tìm kiếm, kiểu tìm kiếm)
Điều kiện để tìm kiếm: Tên hàng bên bảng nhập – xuất hàng ví dụ: Bạc hà, cam, chanh.
Vùng điều kiện: Chính là vùng chứa dữ liệu để ta dùng công thức lọc ra. Đó là toàn bộ Bảng tồn kho đầu kỳ cột bắt đầu là cột tên hàng. Vì ta đang dùng tên hàng là điều kiện tìm kiếm nên cột tên hàng phải là cột đầu tiên trong vùng tìm kiếm
Số thứ tự cột chứa giá trị tìm kiếm: Tính từ cột tên hàng trong vùng tìm kiếm.
Kiểu tìm kiếm: 0 là kiểu tìm kiếm chính xác 100% và là kiểu thường đc sử dụng hơn là kiểu 1.
Qua phân tích, thì các thành phần của hàm Vlookup dùng để lấy đơn giá nhập từ bảng tồn kho đầu kỳ (Vùng tìm kiếm) là:
4. Lời giải cho cách sử dụng hàm tìm kiếm theo cột để tìm đơn giá nhập – xuất
Ta đánh vào ô E13 bên bảng nhập xuất để tìm đơn giá nhập theo tên hàng hóa, Công thức như sau
=Vlookup (“Bạc Hà”, $B$4:$F$9, 4, 0)
Trong đó từ Bạc Hà chúng ta có thể thay bằng địa chỉ ô chứa giá trị “Bạc hà” trong cột tìm đơn giá nhập
Vlookup (“Bạc Hà”, $B$4:$F$9, 4, 0) =Vlookup (C13, $B$4:$F$9, 4, 0)
Giải thích cách sử dụng hàm
Hàm vlookup được dùng rất nhiều trong công việc của kế toán. Do vậy Trường đã dành thời gian ghi lại video hướng dẫn cách dùng hàm vlookup trong file kế toán excel.
Các bài tập trong file trên chủ yếu phục vụ việc luyện tập cách dùng hàm vlookup của bạn.
Tuy nhiên đây là các bài tập thực tế do đó cần phải có tương đối đầy đủ các yêu cầu. Điều này khiến chúng ta phải sử dụng nhiều hàm khác cùng một lúc; Ví dụ: IF, Left, Mid,…
Đó có thể là bài toán về tổng hợp doanh thu bán hàng, tính lương nhân viên. Hoặc tính tiền điện với bảng định mức – đơn giá áp dụng cho từng trường hợp.
Cách sử dụng hàm vlookup trong các phiên bản excel 2003, 2007, 2010, 2013 đều không có gì khác nhau.
Có thể bạn cũng quan tâm:
Cách Nhóm Các Sheet Trong Excel
Bạn có thể dễ dàng nhóm các sheet trong Excel để có thể thực hiện các thay đổi cùng một lúc (thay vì đi đến từng sheet một và thực hiện các thay đổi).
Ví dụ, giả sử bạn có một cửa sổ làm việc có nhiều sheet ( Mỗi 1 sheet tượng trưng cho mỗi tháng ). Bây giờ bạn muốn thêm một mục mới ( Laptop) vào ô A7 trong tất cả các sheet.
Với khả năng nhóm các sheet trong Excel, bạn có thể nhóm tất cả các sheet trước và sau đó, bạn cần thực hiện mục nhập này một lần và nó sẽ tự động được thêm vào tất cả các sheet
Nhóm một số Sheet cụ thể
Đôi khi. bạn có thể chỉ muốn một số sheet cụ thể và thực hiện thay đổi nội dung ở trong các sheet đó.
Mình thường làm cách này khi phải ẩn một vài Sheet . Thay vì ẩn từng cái một, mình chỉ cần nhóm các trang này lại và sau đó ẩn tất cả chúng cùng một lúc.
Chọn bất kỳ một trong các sheet mà bạn muốn được nhóm lại.-
Giữ phím Ctrl trên bàn phím của bạn
Sử dụng chuột để chọn tất cả các sheet mà bạn muốn nhóm lại (giữ phím Ctrl khi thực hiện việc này)
Nhả phím Ctrl
Các bước trên sẽ nhóm tất cả các sheet bạn đã chọn.
Vì vậy, thay vì làm từng cái một, bạn chỉ cần nhóm các sheet lại và thực hiện ở 1 sheet là dữ liệu ở các sheet khác cũng sẽ thay đổi tương tự. Nó giúp rút ngời thời gian làm việc của bạn và cũng giúp nâng cao khả năng làm việc chuyên nghiệp.
Nhóm toàn bộ sheet trong Excel
Việc nhóm sheet này khá đơn giản khi bạn xác đình rằng dữ liệu ở các sheet đã hoàn chỉnh.
Nhấp chuột vào bất kỳ sheet nào.
Cách 2.
Giữ phím Shift ở sheet đầu tiên
Bấm chuột vào sheet cuối cùng trong file ( Vẫn giữ phím shift)
Nhả phím Shift
Bỏ nhóm sheet trong Excel
Giữ phím Ctrl
Nhấp vào tất cả các Sheet mà bạn muốn hủy nhóm ( Nếu muốn bỏ nhóm cho tất cả các sheet thì hãy chọn Ungroups Sheet)
Nhả phím Ctrl ra.
Thao tác này sẽ hủy nhóm các sheet đã chọn nhưng vẫn giữ nhóm của các sheet còn lại.
Cập nhật thông tin chi tiết về Nhóm Hàm Tham Chiếu Trong Excel (Bài 5) trên website Rafs.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!