Trong công việc và học tập, chúng ta thường xuyên phải làm việc với các bảng tính Excel để sắp xếp và phân tích dữ liệu. Việc tìm kiếm và tổng hợp các giá trị dữ liệu từ các bảng tính khác nhau có thể tốn nhiều thời gian và công sức.
Nếu bạn biết cách sử dụng hàm VLOOKUP kết hợp hàm SUMIF trong Excel, việc này sẽ trở nên đơn giản và hiệu quả hơn.
Tham khảo thêm: Tổng hợp các khóa học excel cơ bản cho người đi làm
Danh mục nội dung
Cách sử dụng hàm VLOOKUP kết hợp hàm SUMIF trong Excel
Hàm VLOOKUP được sử dụng để tìm kiếm giá trị dựa trên giá trị đầu vào. Hàm SUMIF được sử dụng để tính tổng các giá trị trong phạm vi được chỉ định, dựa trên một điều kiện cụ thể. Khi kết hợp hai hàm này, bạn có thể tìm kiếm và tổng hợp các giá trị dựa trên một điều kiện nhất định.
Cú pháp:
=SUMIF(range1,VLOOKUP(lookup_value,range2,col_index,0),range3)
Trong đó:
range1: phạm vi tính tổng các giá trị.
lookup_value: giá trị cần tìm kiếm.
range2: phạm vi tìm kiếm giá trị lookup_value.
col_index: số cột trong range2 mà giá trị tìm kiếm sẽ trả về.
range3: phạm vi chứa các giá trị cần tính tổng.
Bài tập ứng dụng trong thực tế
1. Tính toán doanh thu theo sản phẩm:
Bạn có bảng tính danh sách đơn hàng của một cửa hàng và một bảng tính danh sách sản phẩm. Sử dụng hàm VLOOKUP để tìm kiếm giá trị của từng sản phẩm trong bảng danh sách sản phẩm và kết hợp với hàm SUMIF để tính toán tổng doanh thu của mỗi sản phẩm.
Chúng ta có bài tập minh họa như sau:
Bảng tính danh sách đơn hàng của cửa hàng:
| STT | Mã đơn hàng | Sản phẩm | Số lượng | Giá bán |
|---|---|---|---|---|
| 1 | DH001 | SP001 | 5 | 100,000 |
| 2 | DH002 | SP002 | 3 | 150,000 |
| 3 | DH003 | SP001 | 2 | 100,000 |
| 4 | DH004 | SP003 | 4 | 200,000 |
| 5 | DH005 | SP002 | 2 | 150,000 |
Bảng tính danh sách sản phẩm:
| STT | Mã sản phẩm | Tên sản phẩm | Đơn giá |
|---|---|---|---|
| 1 | SP001 | Áo thun | 100,000 |
| 2 | SP002 | Quần jean | 150,000 |
| 3 | SP003 | Giày thể thao | 200,000 |
Để tính toán doanh thu theo sản phẩm, ta cần thêm một cột để tính giá trị đơn hàng của mỗi sản phẩm, được tính bằng cách nhân số lượng với đơn giá của sản phẩm tương ứng. Ta có thể thêm cột “Giá trị đơn hàng” vào bảng danh sách đơn hàng:
| STT | Mã đơn hàng | Sản phẩm | Số lượng | Giá bán | Giá trị đơn hàng |
|---|---|---|---|---|---|
| 1 | DH001 | SP001 | 5 | 100,000 | 500,000 |
| 2 | DH002 | SP002 | 3 | 150,000 | 450,000 |
| 3 | DH003 | SP001 | 2 | 100,000 | 200,000 |
| 4 | DH004 | SP003 | 4 | 200,000 | 800,000 |
| 5 | DH005 | SP002 | 2 | 150,000 | 300,000 |
Sau đó, ta có thể sử dụng hàm VLOOKUP để tìm kiếm giá trị của từng sản phẩm trong bảng danh sách sản phẩm và kết hợp với hàm SUMIF để tính toán tổng doanh thu của mỗi sản phẩm.
Công thức tính toán tổng doanh thu theo sản phẩm sử dụng hàm VLOOKUP và SUMIF như sau:
=SUMIF(Danh sách đơn hàng!B:B, “SP001”, VLOOKUP(“SP001”, Danh sách sản phẩm!A:D, 4, FALSE))
Trong đó:
“SP001” là mã sản phẩm cần tính toán doanh thu.
Danh sách đơn hàng!B:B là cột chứa mã sản phẩm trong bảng danh sách đơn hàng.
“VLOOKUP(“SP001″, Danh sách sản phẩm!A:D, 4, FALSE)” là hàm VLOOKUP để tìm kiếm giá đơn giá của sản phẩm “SP001” trong bảng danh sách sản phẩm. Cụ thể, hàm này sẽ tìm kiếm giá trị “SP001” trong cột A của bảng danh sách sản phẩm, và trả về giá trị ở cột 4 (đơn giá) của hàng chứa giá trị “SP001”.
Kết quả trả về của hàm VLOOKUP được sử dụng làm đối số của hàm SUMIF, để tính tổng giá trị đơn hàng của sản phẩm “SP001” trong bảng danh sách đơn hàng.
Nếu áp dụng công thức này cho các sản phẩm khác, ta sẽ có các công thức tính tổng doanh thu của từng sản phẩm như sau:
=SUMIF(Danh sách đơn hàng!B:B, “SP001”, VLOOKUP(“SP001”, Danh sách sản phẩm!A:D, 4, FALSE))
=SUMIF(Danh sách đơn hàng!B:B, “SP002”, VLOOKUP(“SP002”, Danh sách sản phẩm!A:D, 4, FALSE))
=SUMIF(Danh sách đơn hàng!B:B, “SP003”, VLOOKUP(“SP003”, Danh sách sản phẩm!A:D, 4, FALSE))
Kết quả sẽ là tổng doanh thu của từng sản phẩm, được tính dựa trên các giá trị trong bảng danh sách đơn hàng và danh sách sản phẩm.
Ví dụ, để tính tổng doanh thu của sản phẩm “SP001”, ta sẽ thay thế “SP001” trong các công thức trên bằng mã sản phẩm tương ứng. Cụ thể, công thức để tính tổng doanh thu của sản phẩm “SP001” sẽ là:
=SUMIF(Danh sách đơn hàng!B:B, “SP001”, VLOOKUP(“SP001”, Danh sách sản phẩm!A:D, 4, FALSE))
Kết quả sẽ là tổng doanh thu của sản phẩm “SP001” trong bảng danh sách đơn hàng, được tính bằng cách nhân số lượng với đơn giá của sản phẩm “SP001”.
2. Tính toán số lượng đặt hàng:
Bạn có bảng tính danh sách đơn hàng và một bảng tính danh sách sản phẩm. Sử dụng hàm VLOOKUP để tìm kiếm giá trị của từng sản phẩm trong bảng danh sách sản phẩm và kết hợp với hàm SUMIF để tính toán tổng số lượng đặt hàng của mỗi sản phẩm.
Để minh họa cho bài tập này, chúng ta có thể sử dụng các bảng tính giả định như sau:
Bảng danh sách đơn hàng:
| Mã đơn hàng | Tên sản phẩm | Số lượng |
|---|---|---|
| DH001 | Sản phẩm A | 5 |
| DH002 | Sản phẩm B | 3 |
| DH003 | Sản phẩm C | 2 |
| DH004 | Sản phẩm A | 2 |
| DH005 | Sản phẩm B | 1 |
Bảng danh sách sản phẩm:
| Tên sản phẩm | Giá bán |
|---|---|
| Sản phẩm A | 10 |
| Sản phẩm B | 20 |
| Sản phẩm C | 30 |
Để tính toán số lượng đặt hàng cho mỗi sản phẩm, chúng ta có thể sử dụng công thức sau:
=SUMIF(B2:B6,”Sản phẩm A”,C2:C6)*VLOOKUP(“Sản phẩm A”,G1:H4,2,FALSE)
Trong đó:
B2:B6 là phạm vi của cột Tên sản phẩm trong bảng danh sách đơn hàng.
“Sản phẩm A” là tên sản phẩm mà chúng ta muốn tính tổng số lượng đặt hàng.
C2:C6 là phạm vi của cột Số lượng trong bảng danh sách đơn hàng.
G1:H4 là phạm vi của bảng danh sách sản phẩm.
2 là chỉ số của cột Giá bán trong bảng danh sách sản phẩm.
FALSE là tham số để đảm bảo rằng chúng ta chỉ tìm kiếm chính xác sản phẩm mà chúng ta cần.
Khi áp dụng công thức này vào bảng tính của chúng ta, kết quả sẽ là:
| Tên sản phẩm | Số lượng đặt hàng |
|---|---|
| Sản phẩm A | 7 |
| Sản phẩm B | 4 |
| Sản phẩm C | 2 |
Như vậy, chúng ta đã tính được tổng số lượng đặt hàng của mỗi sản phẩm bằng cách kết hợp sử dụng hàm VLOOKUP và hàm SUMIF.
3. Tính toán tỷ lệ chi phí:
Bạn có bảng tính danh sách chi phí của một dự án và một bảng tính danh sách công việc của dự án đó. Sử dụng hàm VLOOKUP để tìm kiếm giá trị của từng công việc trong bảng danh sách công việc và kết hợp với hàm SUMIF để tính toán tỷ lệ chi phí của từng công việc.
Để minh họa cho bài tập này, chúng ta có thể sử dụng một ví dụ đơn giản như sau:
Bảng danh sách chi phí của dự án:
| Công việc | Chi phí |
|---|---|
| A | $100 |
| B | $200 |
| C | $300 |
| D | $400 |
| E | $500 |
Bảng danh sách công việc của dự án:
| Công việc | Thời gian |
|---|---|
| A | 10 |
| B | 20 |
| C | 30 |
| D | 40 |
| E | 50 |
Ta sử dụng hàm VLOOKUP để tìm kiếm giá trị của từng công việc trong bảng danh sách chi phí và kết hợp với hàm SUMIF để tính toán tỷ lệ chi phí của từng công việc.
Cụ thể, ta có thể sử dụng công thức như sau để tính tỷ lệ chi phí cho công việc A:
=VLOOKUP(“A”,DanhSachChiPhi,2,FALSE)/SUMIF(DanhSachCongViec,CongViec,DanhSachThoiGian)
Trong đó:
“A” là công việc cần tính tỷ lệ chi phí
DanhSachChiPhi là phạm vi của bảng danh sách chi phí
2 là chỉ số của cột chi phí trong bảng danh sách chi phí
FALSE đánh dấu cho việc tìm kiếm chính xác (exact match) của công việc
DanhSachCongViec là phạm vi của bảng danh sách công việc
CongViec là tên của công việc cần tính tỷ lệ chi phí (ở đây là “A”)
DanhSachThoiGian là phạm vi của cột thời gian trong bảng danh sách công việc
Ta áp dụng công thức này cho các công việc còn lại để tính tỷ lệ chi phí của toàn bộ dự án. Kết quả có thể được thể hiện dưới dạng bảng như sau:
| Công việc | Thời gian | Tỷ lệ chi phí |
|---|---|---|
| A | 10 | 5.88% |
| B | 20 | 11.76% |
| C | 30 | 17.65% |
| D | 40 | 23.53% |
| E | 50 | 41.18% |
Vì vậy, chúng ta đã tính toán thành công tỷ lệ chi phí cho từng công việc trong dự án sử dụng hàm VLOOKUP và SUMIF.
4. Tính toán tổng số giờ làm việc:
Bạn có bảng tính danh sách nhân viên và một bảng tính danh sách giờ làm việc của từng nhân viên trong tháng. Sử dụng hàm VLOOKUP để tìm kiếm giá trị của từng nhân viên trong bảng danh sách nhân viên và kết hợp với hàm SUMIF để tính toán tổng số giờ làm việc của từng nhân viên trong tháng.
Chúng ta có ví dụ minh họa sau
Bảng danh sách nhân viên:
| STT | Mã NV | Tên NV |
|---|---|---|
| 1 | NV001 | Nguyễn A |
| 2 | NV002 | Trần B |
| 3 | NV003 | Lê C |
| 4 | NV004 | Phạm D |
Bảng danh sách giờ làm việc của từng nhân viên trong tháng:
| Mã NV | Ngày | Giờ làm việc |
|---|---|---|
| NV001 | 1/3/2023 | 8 |
| NV001 | 2/3/2023 | 7 |
| NV002 | 1/3/2023 | 6 |
| NV002 | 2/3/2023 | 9 |
| NV002 | 3/3/2023 | 5 |
| NV003 | 1/3/2023 | 8 |
| NV003 | 2/3/2023 | 8 |
| NV004 | 2/3/2023 | 7 |
| NV004 | 3/3/2023 | 6 |
Để tính toán tổng số giờ làm việc của từng nhân viên trong tháng, chúng ta có thể sử dụng hàm VLOOKUP để tìm kiếm giá trị mã nhân viên trong bảng danh sách nhân viên và kết hợp với hàm SUMIF để tính toán tổng số giờ làm việc của từng nhân viên trong bảng danh sách giờ làm việc.
Cụ thể, chúng ta có thể sử dụng công thức sau trong ô B2 để tính toán tổng số giờ làm việc của nhân viên có mã NV001 trong bảng danh sách giờ làm việc:
=SUMIF(‘Danh sách giờ làm việc’!A:A, VLOOKUP(B2, ‘Danh sách nhân viên’!B:C, 2, FALSE), ‘Danh sách giờ làm việc’!C:C)
Trong đó:
‘Danh sách giờ làm việc’!A:A là cột chứa mã nhân viên trong bảng danh sách giờ làm việc.
VLOOKUP(B2, ‘Danh sách nhân viên’!B:C, 2, FALSE) là hàm VLOOKUP sử dụng để tìm kiếm tên nhân viên có mã nhân viên trùng với giá trị trong ô B2 trong bảng danh sách nhân viên. Hàm VLOOKUP này sẽ trả về tên nhân viên tương ứng với mã nhân viên trong ô B2.
‘Danh sách giờ làm việc’!C:C là cột chứa số giờ làm việc của từng nhân viên trong bảng danh sách giờ làm việc.
Sau đó, chúng ta có thể sao chép công thức này vào các ô B3, B4, B5 trong bảng danh sách nhân viên để tính toán tổng số giờ làm việc của các nhân viên khác trong tháng.
Kết quả sẽ như sau:
| STT | Mã NV | Tên NV | Tổng giờ làm việc |
|---|---|---|---|
| 1 | NV001 | Nguyễn A | 15 |
| 2 | NV002 | Trần B | 20 |
| 3 | NV003 | Lê C | 16 |
| 4 | NV004 | Phạm D | 13 |
Như vậy, chúng ta đã sử dụng hàm VLOOKUP và hàm SUMIF để tính toán tổng số giờ làm việc của từng nhân viên trong tháng dựa trên bảng danh sách nhân viên và bảng danh sách giờ làm việc của từng nhân viên.
5. Tính toán tổng số điểm của học sinh:
Bạn có bảng tính danh sách điểm của các học sinh trong lớp và một bảng tính danh sách học sinh trong lớp. Sử dụng hàm VLOOKUP để tìm kiếm giá trị của từng học sinh trong bảng danh sách học sinh và kết hợp với hàm SUMIF để tính toán tổng số điểm của từng học sinh.
Để minh họa cho bài tập này, ta có thể giả sử có một bảng tính Excel chứa danh sách điểm của các học sinh trong lớp như sau:
| Họ và tên | Điểm toán | Điểm văn | Điểm Anh |
|---|---|---|---|
| Huy | 8 | 7 | 6 |
| Lan | 9 | 8 | 7 |
| Minh | 7 | 6 | 8 |
| An | 8 | 9 | 9 |
| Hoa | 6 | 7 | 8 |
| Binh | 9 | 8 | 9 |
| Tuan | 7 | 6 | 7 |
Và có một bảng danh sách học sinh trong lớp như sau:
| Họ và tên |
|---|
| Huy |
| Lan |
| Minh |
| An |
| Hoa |
| Binh |
| Tuan |
Để tính tổng số điểm của từng học sinh trong bảng danh sách học sinh, ta có thể sử dụng hàm VLOOKUP để tìm giá trị điểm của từng học sinh trong bảng điểm và kết hợp với hàm SUMIF để tính tổng số điểm.
Cụ thể, ta có thể sử dụng công thức sau để tính tổng số điểm của học sinh Huy:
=SUMIF(A2:A8, VLOOKUP(A2, $A$2:$D$8, {2,3,4}, FALSE), B2:D8)
Trong đó:
A2:A8 là phạm vi của các tên học sinh trong bảng điểm.
VLOOKUP(A2, $A$2:$D$8, {2,3,4}, FALSE) được sử dụng để tìm giá trị điểm của học sinh Huy trong bảng điểm. Đây là một mảng ba chiều ({2,3,4}) để lấy các giá trị trong ba cột Điểm toán, Điểm văn và Điểm Anh.
B2:D8 là phạm vi của các điểm trong bảng điểm.
Kết quả sẽ là tổng số điểm của học sinh Huy trong bảng điểm. Ta có thể áp dụng cùng công thức này để tính tổng số điểm của các học sinh khác trong danh sách học sinh.
Kết luận
Trên đây là những cách sử dụng hàm VLOOKUP kết hợp hàm SUMIF trong Excel và những bài tập ứng dụng trong thực tế. Khi biết cách sử dụng hai hàm này, bạn có thể tìm kiếm và tổng hợp các giá trị dữ liệu một cách nhanh chóng và hiệu quả. Hãy thực hành và tìm ra cách sử dụng phù hợp nhất với công việc của bạn.
|
Trọn bộ khóa học Excel online chất lượng từ cơ bản đến nâng cao, phù hợp cho mọi đối tượng từ văn phòng đến sinh viên, học phí thấp và được sở hữu trọn đời. Bạn sẽ được tiếp cận với những bài học chi tiết và dễ hiểu, bao gồm tài liệu và bài tập thực hành giúp bạn trau dồi kiến thức và kỹ năng sử dụng Excel. |
FAQs
Hỏi: Hàm SUMIF có thể kết hợp với các hàm khác trong Excel không?
Trả lời: Có, hàm SUMIF có thể kết hợp với nhiều hàm khác nhau trong Excel như hàm VLOOKUP, hàm IF, hàm COUNTIF, …
Hỏi: Tôi có thể sử dụng hàm VLOOKUP và hàm SUMIF trên bảng dữ liệu có nhiều điều kiện lọc không?
Trả lời: Có, hàm VLOOKUP và hàm SUMIF có thể được sử dụng trên bảng dữ liệu có nhiều điều kiện lọc bằng cách sử dụng các công thức phức tạp hơn như hàm SUMIFS hoặc hàm INDEX MATCH.
Hỏi: Hàm VLOOKUP có thể tìm kiếm giá trị theo thứ tự ngược lại không?
Trả lời: Không, hàm VLOOKUP chỉ có thể tìm kiếm giá trị theo thứ tự từ trái sang phải trong bảng dữ liệu.
Hỏi: Tôi có thể sử dụng hàm VLOOKUP và hàm SUMIF trên Google Sheets không?
Trả lời: Có, hàm VLOOKUP và hàm SUMIF cũng có thể được sử dụng trên Google Sheets, với cú pháp tương tự như trong Excel.