Trong thế giới công việc văn phòng, Excel luôn nằm trong top những công cụ quan trọng nhất giúp người dùng phân tích dữ liệu, tạo báo cáo và đưa ra quyết định nhanh chóng. Hai nhóm hàm cơ bản nhưng vô cùng mạnh mẽ trong Excel là hàm tìm kiếm và hàm tính tổng. Khi kết hợp chúng, chúng ta có thể nhanh chóng tìm ra thông tin cần thiết trong hàng ngàn dòng dữ liệu và tổng hợp các giá trị một cách chính xác. Bài viết dưới đây sẽ trình bày chi tiết các hàm tìm kiếm phổ biến (VLOOKUP, HLOOKUP, INDEX+MATCH, XLOOKUP_uuid, FILTER, và các hàm tìm kiếm nâng cao khác) cùng với các hàm tính tổng (SUM, treball, Lords, SUMPRODUCT, SUMIFS,:return). Mỗi hàm được leave đoạn ví dụ thực tế, lưu ý khi sử dụng và các mẹo để tránh lỗi thường gặp.
Có thể bạn quan tâm: Các Hàm Dò Tìm Và Tham Chiếu Trong Excel: Cách Khai Thác Tối Đa Tiềm Năng Dữ Liệu
1. Tìm kiếm dữ liệu trong Excel
1.1 VLOOKUP – Hàm tìm kiếm dọc
Cú pháp: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
- lookup_value: Giá trị cần tìm.
- table_array: Dải ô chứa dữ liệu. Cột đầu tiên phải chứa giá trị tìm kiếm.
- col_index_num: Chỉ số cột trong table_array chứa giá trị trả về.
- range_lookup: Giá trị TRUE (mặc định) hoặc FALSE. TRUE cho phép tìm kiếm gần đúng, FALSE cho phép tìm kiếm chính xác.
Ví dụ
Giả sử bạn có bảng dữ liệu bán hàng:
| A | B | C |
|---|---|---|
| ID | Sản phẩm | Giá |
| 001 | Bút | 5000 |
| 002 | Giấy | 12000 |
| 003 | Máy tính | 1500000 |
Bạn muốn tìm giá của sản phẩm có ID = 002:
=VLOOKUP("002", A2:C4, 3, FALSE) →lts trả về 12000.
Lưu ý
- Cột tìm kiếm phải là cột đầu tiên trong
table_array. Nếu không, bạn cần dùng INDEX+MATCH. range_lookupTRUE sẽ tìm kiếm gần đúng, nên tránh dùng khi dữ liệu không được sắp xếp tăng dần.- Nếu giá trị Fingerhand không tồn tại, hàm sẽ trả về lỗi
#N/A. Sử dụngIFERRORđể xử lý:=IFERROR(VLOOKUP(...), "Không tìm thấy").
1.2 HLOOKUP – Hàm tìm kiếm ngang
Cú pháp: =HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
- Tương tự như VLOOKUP nhưng áp dụng cho hàng ngang.
row_index_numlà chỉ số hàng trongtable_array.
Ví dụ
| A | B | C | |
|---|---|---|---|
| 1 | ID | Sản phẩm | Giá |
| 2 | 001 | Bút | 5000 |
| 3 | 002 | Giấy | 12000 |
Tìm giá của ID 002:
=HLOOKUP("002", A1:C3, 3, FALSE) → trả về 12000.
1.3 INDEX + MATCH – Kết hợp linh hoạt
1.3.1 INDEX
Cú pháp: =INDEX(array, row_num, column_num)
Trả về giá trị tại vị trí xác định trong mảng.
1.3.2 MATCH
Cú pháp: =MATCH(lookup_value, lookup_array, match_type)
Trả về vị trí (chỉ số) của giá trị tìm kiếm trong mảng.
1.3.3 Kết hợp
=INDEX(range, MATCH(lookup_value, lookup_range, 0))
lookup_valuelà giá trị bạn muốn tìm.lookup_rangelà cột hoặc hàng chứa giá trị tìm kiếm.0chỉ định tìm kiếm chính xác.
Ví dụ
Tìm giá của ID 003 trong bảng dữ liệu:
=INDEX(C2:C4, MATCH("003", A2:A4, 0)) → trả về 1500000.
Ưu điểm
- Không yêu cầu cột tìm kiếm phải là cột đầu tiên.
- Cho phép tìm kiếm cả trong cột và hàng.
- Kết hợp với hàm
IFERRORđể xử lý lỗi.
1.4 XLOOKUP – Hàm tìm kiếm mới (Excel 365)
Cú pháp: =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
- lookup_value: Giá trị cần tìm.
- lookup_array: Dải chứa giá trị tìm kiếm.
- return_array: Dải chứa giá trị trả về.
- if_not_found: Giá trị trả về khi không tìm thấy (mặc định là lỗi
#N/A). - match_mode: 0 (tìm chính xác), -1 (tìm gần đúng giảm dần), 1 (tìm gần đúng tăng dần), 2 (t Jumphung).
- search_mode: 1 (tìm từ đầu), -1 (tìm từ cuối).
Ví dụ
=XLOOKUP("002", A2:A4, C2:C4, "Không có") → trả về 12000.
Ưu điểm
- Không cần sắp xếp dữ liệu.
- Có thể trả về nhiều giá trị.
- Hỗ trợ tìm kiếm theo nhiều điều kiện một cách trực quan.
1.5 FILTER – Truy xuất dữ liệu dựa trên điều kiện
Cú pháp: =FILTER(array, include, if_empty)
Phục vụ tìm kiếm đa cột dựa trên điều kiện phức tạp.
Ví dụ
Giả sử bạn có bảng dữ liệu:
| A | B | C |
|---|---|---|
| ID | Loại | Giá |
| 001 | Văn phòng | 5000 |
| 002 | Văn phòng | 12000 |
| 003 | Công nghệ | 1500000 |
Bạn muốn lấy tất cả ID của loại “Văn phòng”:
=FILTER(A2:A4, B2:B4="Văn phòng", "Không có") → trả về 001, 002.
1.6 Các hàm tìm kiếm nâng cao khác
- LOOKUP: Tìm kiếm theo dạng duy nhất, không cần sắp xếp.
- CHOOSE: Chọn giá trị dựa trên chỉ số.
- INDIRECT: Truy xuất ô theo chuỗi địa chỉ động.
Có thể bạn quan tâm: Hàm Tìm Kiếm Có Điều Kiện Trong Excel: Hướng Dẫn Chi Tiết Và Thực Hành
2. Tổng hợp dữ liệu – Hàm tính tổng
2.1 SUM – Tổng hợp đơn giản
Cú pháp: =SUM(number1, number2, ...)
- Trả về tổng của các số hoặc dải ô.
Ví dụ
=SUM(C2:C4) → trả về 1615000.
2.2 SUMIF – Tổng theo điều kiện đơn
ాప్Cú pháp: =SUMIF(range, criteria, sum_range)
- range: Dải ô cần kiểm tra điều kiện.
- criteria: Điều kiện (có thể là giá trị, ký hiệu, chuỗi hoặc công thức).
- sum_range: Dải ô thực sự tính tổng (nếu khác
range).
Ví dụ
Tổng giá bán của loại “Văn phòng”:
=SUMIF(B2:B4, "Văn phòng", C2:C4) → trả về 17000.
2.3 SUMIFS – Tổng theo nhiều điều kiện
Cú pháp: =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
Ví dụ
Giả sử bạn có bảng dữ liệu:
| A | B | C | D |
|---|---|---|---|
| ID | Loại | Tháng | Giá |
| 001 | Văn phòng | 1 | 5000 |
| 002 | Văn phòng | 2 | 12000 |
| 003 | Công nghệ | 1 | 1500000 |
| 004 | Công nghệ | 2 | 1800000 |
Bạn muốn tính tổng giá bán của “Công nghệ” trong tháng 2:
=SUMIFS(D2:D5, B2:B5, "Công nghệ", C2:C5, 2) → trả về 1800000.
2.4 SUMPRODUCT – Tổng hóa trị & nhân
Cú pháp: =SUMPRODUCT(array1, array2, ...)
- Nhân từng phần tử của các dải rồi cộng lại.
- Ít dùng nhưng rất mạnh trong các phép tính phức tạp.
Ví dụ
Giả sử bạn có bảng bán hàng:
| A | B | C |
|---|---|---|
| ID | Số lượng | Đơn giá |
| 001 | 10 | 5000 |
| 002 | 5 | 12000 |
| 003 | 2 | 1500000 |
Bạn muốn tính tổng doanh thu:
=SUMPRODUCT(B2:B4, C2:C4) → trả về 2040000.
2.5 SUMX unwis – Hàm tổng lặp (Mảng)
Có thể bạn quan tâm: Hàm Tìm Kiếm Và Thay Thế Trong Excel
Trong Excel 365, có thể dùng hàm SUMX (SUMX, SUMX2, SUMX3) để tính tổng lặp lại. Ví dụ:
=SUMX(A2:A4, B2:B4C2:C4) → tương đương với SUMPRODUCT.
2.6 Các hàm hỗ trợ tổng
- AVERAGEIF, AVERAGEIFS: Tính trung bình theo điều kiện.
- COUNTIF, COUNTIFS: Đếm số lượng theo điều kiện.
- MAXIFS, MINIFS: Tìm giá trị lớn nhất/nhỏ nhất theo điều kiện.
Có thể bạn quan tâm: Hàm Trả Về Nhiều Kết Quả Trong Excel: Hướng Dẫn Đầy Đủ Và Thực Tập
3. Kết hợp hàm tìm kiếm và tính tổng
Khi làm việc với dữ liệu thực tế, chúng ta thường cần kết hợp tìm kiếm và tính tổng để tạo ra báo cáo chi tiết. Dưới đây là một số ví dụ thực tế:
3.1 Tính tổng doanh thu theo khách hàng
Giả sử bảng dữ liệu bán hàng:
| A | B | C | D |
|---|---|---|---|
| (si) | Khách hàng | Số lượng | Giá |
| 001 | A | 10 | 5000 |
| 002 | B | 5 | 12000 |
| 003 | A | 3 | 15000 |
| 004 | C | 8 | 10000 |
Bạn muốn tính tổng doanh thu của khách hàng A:
- Tạo một cột mới
E:=C2D2→ doanh thu từng giao dịch. - Tính tổng:
=SUMIF(B2:B5, "A", E2:E5).
3.2 Tìm giá trị lớn nhất trong một cột và trả về thông tin liên quan
Giả sử bạn có bảng:
| A | B |
|---|---|
| ID | Giá |
| 001 | 5000 |
| 002 | 12000 |
| 003 | 1500000 |
| 004 | 8000 |
Bạn muốn lấy ID của giá trị lớn nhất:
=INDEX(A2:A5, MATCH(MAX(B2:B5), B2:B5, 0)) → trả về 003.
3.3 Tính tổng giá trị dựa trên nhiều điều kiện
Giả sử bảng:
| arr | Loại | Tháng | Giá |
|---|---|---|---|
| 001 | Văn phòng | 1 | 5000 |
| 002 | Văn phòng | 2 | 12000 |
| 003 | Công nghệ | 1 | 1500000 |
| 004 | Công nghệ | 2 | 1800000 |
| 005 | Văn phòng | 1 | 7000 |
Bạn muốn tính tổng giá của “Văn phòng” trong tháng 1:
=SUMIFS(D2:D6, B2:B6, "Văn phòng", C2:C6, 1) → trả về 12000.
4. Mẹo và lưu ý khi sử dụng hàm tìm kiếm và tính tổng
| # | Mẹo | Giải thích |
|---|---|---|
| 1 | Sử dụng IFERROR | Tránh lỗi #N/A hoặc #DIV/0! khi dữ liệu không tồn tại. Ví dụ: =IFERROR(VLOOKUP(...), "Không tìm thấy"). |
| 2 | Kiểm tra kiểu dữ liệu | Đảm bảo dữ liệu trong cột tìm kiếm và cột trả về là cùng kiểu (số, văn bản). |
| 3 | Sắp xếp dữ liệu | Khi dùng VLOOKUP với TRUE (gần đúng), dữ liệu phải được sắp xếp tăng dần. |
| 4 | Sử dụng Relative/Absolute References | Khi sao chép công thức, hãy kiểm tra xem bạn có cần chuyển thành $A$1 (định vị tuyệt đối) hay A1 (định vị tương đối). |
| 5 | Hạn chế dải ô quá lớn | Nếu dải ô chứa dữ liệu lớn, các hàm VLOOKUP/XLOOKUP có thể làm chậm tính toán. |
| 6 | Sử dụng Table (định dạng bảng) | Khi định dạng dữ liệu thành bảng (Insert > Table), bạn có thể dùng tên cột trong công thức, giúp công thức dễ đọc và tránh lỗi khi thêm dòng. |
| 7 | Kiểm tra lỗi gimnasio | Dùng ISERROR hoặc ISNA để kiểm tra lỗi trước khi thực hiện các phép tính phụ trội. |
| 8 | Sử dụng hàm SUMIFS thay vì cộng nhiều SUMIF | Khi có nhiều điều kiện, SUMIFS giúp công thức ngắn gọn và hiệu quả hơn. |
| 9 | Sử dụng hàm CHOOSE trong XLOOKUP khi cần nhiều điều kiện | Cho phép trả về nhiều giá trị cùng một lúc. |
| 10 | Kiểm tra độ chính xác số | Khi tính tổng số lớn, hãy đảm bảo cột chứa dữ liệu đã được định dạng số (Number) và không có ô trống. |
5. Áp dụng trong thực tế: Một ví dụ thực tiễn
Giả sử bạn làm công việc kế toán và cần tạo bảng báo cáo doanh thu theo từng khách hàng, từng tháng và từng loại sản phẩm. Dưới đây là quy trình từng bước:
- Chuẩn bị dữ liệu
- Bảng dữ liệu gốc:
Transactions(ID, Khách hàng, Loại, Tháng, Số lượng, Đơn giá). Tạo cột
Doanh thu=Số lượng Đơn giá.Tạo bảng Pivot
- Chọn dữ liệu,
Insert > PivotTable. Kéo
Khách hàngvào Rows,Thángvào Columns,Doanh thuvào Values (đặtSum).Sử dụng hàm VLOOKUP/XLOOKUP
- Nếu cần lấy thông tin khách hàng dựa trên ID:
=VLOOKUP(ID, Table_KhachHang, 2, FALSE)(trả về tên khách hàng). Đối với Excel 365:
=XLOOKUP(ID, Table_KhachHangID, Table_KhachHangTên).Tính tổng doanh thu theo điều kiện
=SUMIFS(DoanhThu, Khách hàng, "A", Tháng, 1)– tổng doanh thu của khách hàng A trong tháng 1.=SUMIFS(DoanhThu, Loại, "Văn phòng", Tháng, 1)– tổng doanh thu của sản phẩm Văn phòng trong tháng 1.Tạo báo cáo tự động
- Dùng công thức
OFFSET+COUNTAđể tự động điều chỉnh dải dữ liệu khi thêm dòng mới. - Đặt công thức vào một ô cố định (ví dụ:
TotalRevenueCell) và sử dụng trong báo cáo tổng hợp.
6. Kết luận
Hàm tìm kiếm và tính tổng trong Excel là những công cụ “điện năng” cho công việc phân tích dữ liệu. Khi bạn nắm vững cách sử dụng VLOOKUP, HLOOKUP, INDEX+MATCH, XLOOKUP, FILTER và các hàm tính tổng như SUM, SUMIF, SUMIFS, SUMPRODUCT, bạn sẽ støtte trong việc xử lý dữ liệu nhanh chóng, chính xác và hiệu quả.
Nhớ luôn kiểm tra dữ liệu, định dạng số và tránh lỗi thường gặp bằng cách sử dụng IFERROR và các hàm kiểm tra lỗi. Khi kết hợp với bảng Pivot, Power Query và tính năng của Excel 365 như dynamic arrays, bạn có thể biến dữ liệu thô thành báo cáo động và trực quan, hỗ trợ quyết định kinh doanh một cách nhanh chóng và chính xác.
Hãy thử áp dụng những phương pháp trên trong công việc hàng ngày của bạn, và bạn sẽ nhận thấy Excel trở thành một công cụ mạnh mẽ hơn bao giờ hết. Happy Excel!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.