Tổng hợp dữ liệu từ nhiều sheet trong Excel cực nhanh, đơn giản
Khánh Duy 01/11/2021 2 bình luận
Tổng hợp dữ liệu từ nhiều sheet trong Excel sẽ giúp bạn nhanh chóng xử lý, giải quyết được công việc và nếu bạn chưa biết cách sử dụng. Hãy cùng theo dõi bài viết sau để hiểu rõ hơn về cách thực hiện nhé.
Hướng dẫn được thực hiện trên Laptop hệ điều hành Windows 10 với phiên bản Excel 2016. Bạn cũng có thể thực hiện tương tự trên các sản phẩm Excel 2007, 2010, 2013, 2016, 2109 và trên các dòng laptop có cùng hệ điều hành hay dòng MacOS có cùng thao tác khác.
I. Tổng hợp dữ liệu bằng hàm trong Excel
1. Cách hàm cần sử dụng
Để có thể thực hiện được việc tổng hợp dữ liệu từ các sheets. Bạn cần hiểu và nắm rõ các hàm sau.
Hàm IF: Là hàm so sánh lô-gic của một biểu thức.
Cấu trúc hàm: =IF [Logical_test, [value_if_true], [value_if_false]]
Trong đó:
- Logical_test: Là một giá trị hay biểu thức logic trả về giá trị TRUE[đúng] hoặc FALSE[sai].
- Value_if_true: Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị TRUE hay nói cách khác là điều kiện thỏa mãn.
- Value_if_false: Là giá trị mà hàm sẽ trả về nếu biểu thức logic cho giá trị FALSE hay nói cách khác là điều kiện không thỏa mãn.
Hàm ISNA: Là hàm giúp chúng ta kiểm tra được lỗi #N/A trong Excel.
Cấu trúc hàm: =ISNA[value]
Trong đó:
- IS: Là chức năng kiểm tra. Trả về kết quả đúng, sai.
- NA: chính là lỗi #N/A
Hàm VLOOKUP: Là hàm dùng để tìm kiếm thông tin cần tìm.
Cấu trúc hàm: =VLOOKUP[lookup_value, table_array, col_index_num, [range_lookup]]
Trong đó:
- Lookup_value: Là giá trị dùng để dò tìm.
- Table_array: Là vùng dữ liệu chứa giá trị cần dò tìm. Vùng có từ 2 cột trở lên.
- Col_index_num: Là số thứ tự cột đếm từ vùng dò tìm chứa giá trị cần trả về.
- Range_lookup: Là phương pháp dò tìm. Range_lookup là 0 hoặc FALSE để tìm giá trị chính xác, range_lookup là 1 hoặc TRUE hoặc bỏ qua để tìm giá trị gần đúng.
2. Cách tổng hợp dữ liệu
Ví dụ: Mình có bảng dữ liệu như hình dưới ở các sheet trong cùng bảng tính.
Bảng dữ liệu ở sheet 1 mình đặt tên là Tháng 1-2.
Dữ liệu minh họa.
Bảng dữ liệu ở sheet 2 mình đặt tên là Tháng 3-4.
Dữ liệu minh họa.
Bước 1: Trong bảng dữ liệu sheets "Tổng hợp" bạn nhập hàm =IF[ISNA[VLOOKUP[B2,'Thang 1-2'!$B$2:$C$6,2,0]],0,[VLOOKUP[B2,'Thang 1-2'!$B$2:$C$6,2,0]]]+IF[ISNA[VLOOKUP[B2,'Thang 3-4'!$B$3:$C$7,2,0]],0,[VLOOKUP[B2,'Thang 3-4'!$B$3:$C$7,2,0]]] vào ô tham chiếu.
Giải thích hàm: Hàm này có nghĩa là Nếu dữ liệu ở ô B2 [tức ô sản phẩm ở bảng tổng hợp] không được tìm thấy hoặc bị lỗi N/A ở bảng sản phẩm tháng 1-2 thì sẽ trả về giá trị 0, còn nếu tìm thấy thì sẽ lấy giá trị ở cột thứ 2 [tức từ C2 đến C6] và cộng với giá trị ở bảng tháng 3-4.
- IF, ISNA và VLOOKUP: Là những lệnh hàm.
- B2: Là ô chứa giá trị cần dò tìm.
- 'Thang 1-2'!$B$2:$C$6: Là vùng dữ liệu chứa giá trị cần dò tìm.
- 2: Là số thứ tự cột đếm từ vùng dò tìm chứa giá trị cần trả về.
- 0: Là phương pháp dò tìm.
Cách tổng hợp dữ liệu.
Bước 2: Nhấn Enter để hiển thị kết quả và dùng trỏ chuột kéo xuống để hiển thị các kết quả còn lại như hình.
Nhấn Enter để hiển thị kết quả.
II. Tổng hợp dữ liệu bằng Consolidate trong Excel
1. Consolidate là gì?
Consolidate là một trong những hàm thống kê dữ liệu giúp tính toán hiệu quả khi được tích hợp cho phép thực hiện nhanh các phép tính tổng, trung bình, giá trị lớn nhất, nhỏ nhất,...
2. Cách tổng hợp dữ liệu bằng Consolidate
Ví dụ: Sử dụng dữ liệu ở cùng ví dụ minh họa trên. Bạn hãy thực hiện các bước sau.
Bước 1: Trong sheets "Tổng hợp" bạn quét chọn vùng dữ liệu để hiển thị kết quả > Chọn mục Data > Chọn Consolidate để mở hộp thoại Consolidate.
Cách tổng hợp dữ liệu bằng Consolidate.
Bước 2: Trong hộp thoại Consolidate, bạn chọn Sum trong mục Function > Chọn biểu tượng
icon
Lưu ý: Các chức năng của Consolidate trong mục Function có thể giúp bạn tính toán:
- Sum: Tính tổng.
- Average: Tính trung bình.
- Count: Đếm tất cả.
- Max: Lấy giá trị lớn nhất.
- Min: Lấy giá trị nhỏ nhất
- Product: Tính tích.
- Count Number: Chỉ đểm những cột có giá trị là số.
- StdDev: Ước tính độ lệch chuẩn của mẫu.
- StdDevp: Ước tính độ lệch chuẩn của toàn bộ tập hợp.
- Var: Tính phương sai trên mẫu.
- Varp: Tính phương sai trên toàn bộ tập hợp.
Cách tổng hợp dữ liệu bằng Consolidate.
Bước 3: Sau khi nhấn OK bạn có kết quả như hình.
Nhấn Enter để hiển thị kết quả.
Trên đây là Tổng hợp dữ liệu từ nhiều sheet trong Excel cực nhanh, đơn giản. Hy vọng bài viết sẽ giúp bạn thực hiện được trong công việc cũng như học tập và nếu bạn có góp ý hãy để lại bình luận bên dưới và đứng quên chia sẻ nếu thấy hữu ích bạn nhé.
15.748 lượt xem
Chủ đề: Cách Sử Dụng Hàm vlookup Giữa 2 file Và 2 Sheet
Sử dụng hàm vlookup bạn đã biết hết cách dùng của hàm với ứng dụng như thế nào vào trong bảng tính excel chưa? Nếu như bạn đã từng làm quen và biết đến hàm vlookup thì bạn dễ dàng có thể hình dung ra hàm xử lý công việc gì khi nhắc đến cách dùng hàm trong trường hợp nhất định nào đó. Ở đây chúng tôi muốn nói tới là cách dùng hàm vlookup giữa 2 file, 2 sheet. Bài viết học Excel online dưới đây sẽ giúp bạn hình dung và hiểu rõ hơn điều này.
Bạn đọc cũng quan tâm:
>> Hàm Hlookup và Cách dùng hàm Hlookup
>> Cách sử dụng hàm VLOOKUP nhanh nhất
>> Cách sử dụng hàm Lookup trong Excel
1. Hàm Vlookup
Chắc có những bạn chưa biết về hàm Vlookup. Đây là hàm khá thông dụng và được sử dụng khá thường xuyên trên bảng tính excel.
Ở một khái niệm cơ bản và bao quát nhất thì hàm thực hiện việc truy xuất dữ liệu với các điều kiện cho trước.
Tuy nhiên thì thường hay áp dụng hàm trên cùng một file cùng một bảng tính nhất định, nhưng với một số trường hợp công việc lại không đơn giản như vậy, vùng điều kiện và vùng bạn cần truy xuất dữ liệu lại thuộc vào 2 sheet khác nhau, thậm chí xa hơn có thể là 2 file excel khác nhau. Tuy nhiên bạn vẫn có thể thực hiện việc truy xuất bình thường với hướng dẫn từng mục dưới đây.
2. Áp dụng hàm Vlookup giữa 2 sheet
Ví dụ hướng dẫn cụ thể sau:
Cho 2 sheet với 2 mục nội dung khác nhau. Sheet 1 là mục danh sách các nhân viên đang cần nhập số tiền thưởng tết. Sheet 2 là những điều kiện xếp loại ứng với số tiền thưởng tết.
Áp dụng hàm Vlookup giữa 2 sheet [1]
Hãy xem xem hàm vlookup sẽ làm gì để tính ra số tiền thưởng tết của mỗi nhân viên thông qua sheet 2 nhé.
– Chọn 1 ô cho 1 nhân viên cần tính tiền thưởng đầu tiên thiết lập công thức hàm Vlookup cho ô chọn. Đặt tên cho sheet 2 là Thuong. Tại ô D3 nhập công thức:
Áp dụng hàm Vlookup giữa 2 sheet [2]
– Giữ nguyên công thức vừa nhập, chọn vào sheet 2 và chọn vùng dữ liệu điều kiện lương A1: B4 sau đó sử dụng F4 để lần lượt chọn vào các ô dữ liệu A1: B4.
Áp dụng hàm Vlookup giữa 2 sheet [3]
– Trở lại Sheet 1, tại công thức vừa thiết lập nhập thêm vào công thức 2,1:
Áp dụng hàm Vlookup giữa 2 sheet [4]
– Khi đó kéo sao chép công thức xuống dưới các ô còn lại cột lương thưởng sẽ hiển thị ra hết kết quả:
Áp dụng hàm Vlookup giữa 2 sheet [5]
3. Áp dụng hàm vlookup giữa 2 file
Cũng với cùng một chủ đề để tìm ra số tiền thưởng cho nhân viên. Thực hiện công việc này trên excel 2016.
File thứ nhất chứa các thông tin về người nhận tiền thưởng. File thứ 2 chứa thông tin về điều kiện thưởng:
Áp dụng hàm Vlookup giữa 2 file [1]
Và:
Áp dụng hàm Vlookup giữa 2 file [2]
Để mở cùng lúc 2 file này trên màn hình làm việc cho tiện việc xử lý công việc bạn chỉ cần mở 1 file ra trước và sau đó dưới thanh taskbar click chuột phải vào microsoft excel chọn file thứ 2 là OK.
– Tại ô D3 nhập công thức tương tự như áp dụng với 2 sheet trên:
Áp dụng hàm Vlookup giữa 2 file [3]
– Chuyển sang File 2, [đặt tên cho file 2 là dulieu.xlsx] chọn vùng dữ liệu A1: B4 > dùng F4 nhấn chọn từng ô dữ liệu trong vùng A1: B4.
Áp dụng hàm Vlookup giữa 2 file [4]
– Khi quay lại File 1 thì nhập thêm cho công thức 2,0 vào và thực hiện sao chép công thức và cho ra toàn bộ kết quả thưởng của từng nhân viên.
Áp dụng hàm Vlookup giữa 2 file [5]
Trên đây là cách sử dụng hàm Vlookup giữa 2 file và 2 sheet trong excel, hy vọng sẽ giúp ích cho bạn trong việc học Kế toán online và học Excel cùng Unica
Cùng với đó, bạn cũng hoàn toàn có thể nắm trọn hệ thống hàm Excel quan trọng và cần thiết nhất với khóa học “Chinh phục Execl công sở” có trên Unica.vn.
Khóa học do giảng viên- Thạc Sĩ Nguyễn Thành Đông hướng dẫn sẽ đem đến cho bạn những kiến thức và kỹ năng học tin học văn phòng với Excel cực tuyệt vời và nhanh chóng chỉ sau một khóa học duy nhất.
Với 34 bài giảng trong khóa học, giảng viên sẽ giúp bạn bổ sung, cung cấp và hướng dẫn áp dụng chính xác các công thức hàm Excel để thực hiện phân tích, xử lý và thống kê số liệu một cách hiệu quả nhất, thành công nâng cao hiệu suất công việc của bạn.
Không chỉ có vậy trong suốt khóa học của mình giảng viên cũng sẽ chia sẻ đến cho bạn những kiến thức và kinh nghiệm xử lý số liệu thực chiến cực hay, giúp bạn có thể ứng dụng luôn chúng vào công việc của mình.
THAM KHẢO KHÓA HỌC GIẢM 40% NGAY HÔM NAY
Lưu ý: GIẢM 40% KHI SỬ DỤNG MÃ UNICAN40 – ĐỘC QUYỀN DÀNH RIÊNG CHO CÁC KHÓA HỌC TẠI UNICA.VN [KHÔNG ÁP DỤNG KÈM THEO CÁC CHƯƠNG TRÌNH KHUYẾN MÃI KHÁC TẠI UNICA].
Chúc bạn một ngày làm việc hiệu quả!
Nguồn tham khảo: //unica.vn/