Hướng dẫn Excel nâng cao: Kết hợp các hàm VLOOKUP, SUM và SUMIF

Bài viết dưới đây sẽ giới thiệu cách áp dụng linh hoạt hàm VLOOKUP, SUM cùng SUMIF để tìm kiếm và tính toán theo các tiêu chí cụ thể.

Danh sách nội dung

  1. Cách thức kết hợp VLOOKUP và SUMIF
  2. Phương pháp tổng hợp số liệu mà không cần thêm cột trợ giúp với LOOKUP và SUM
  3. Kết hợp VLOOKUP và SUMIF để tìm kiếm và tổng hợp dữ liệu dựa trên điều kiện nhất định
  4. Thực hiện tổng cộng dữ liệu dựa trên nhiều điều kiện trong cùng một cột

Cách thức kết hợp VLOOKUP và SUMIF

Bạn đọc khi đã quen thuộc với hàm VLOOKUP hàm SUMIF , chúng ta sẽ nhanh chóng đi vào ví dụ đầu tiên ngay sau đây.

screen-shot-2016-12-15-at-13-32-18

Để tính tổng sản lượng của loại Cam trong các tháng 1, 2 và 3, ta sẽ sử dụng công thức dưới đây:

Khi đã điền công thức vào, các bạn cần lưu ý rằng đây là công thức mảng nên bạn bắt buộc phải dùng phím tắt CTRL + SHIFT + ENTER để công thức có thể nhập chính xác và cho kết quả đúng.

Để giúp các bạn hiểu rõ hơn, ta có thể giải thích công thức mảng bằng cách chia nhỏ ra thành ba công thức sau đây:


=VLOOKUP( A2 , A1:I8 , 2 , FALSE ) + VLOOKUP( A2 , A1:I8 , 3 , FALSE ) + VLOOKUP( A2 , A1:I8 , 4 , FALSE )

Các số trong dấu ngoặc vuông {2,3,4} biểu thị cho cột 2, 3, và 4 của bảng dữ liệu ở A1:I8. Và bạn có thể thắc mắc rằng tại sao chúng ta không áp dụng công thức đơn giản hơn để tính tổng.

Lý do là, với mục tiêu hỗ trợ cho việc tạo các báo cáo hoặc dashboard, nếu chỉ sử dụng hàm SUM thông thường, ta sẽ cần phải thay đổi công thức mỗi khi muốn tính tổng sản lượng cho một loại sản phẩm khác trong ô đã cho sẵn.

Vì thế, sử dụng công thức mảng phối hợp hàm SUM và VLOOKUP như trên, chúng ta sẽ tạo được báo cáo về số lượng sản phẩm nhanh chóng theo cách sau:

 

kết hợp hàm sum và vlookup trong excel nâng cao

Bên cạnh hàm SUM, có thể linh động thay thế bằng các hàm khác như AVERAGE, MIN, MAX hay áp dụng các phép toán khác tuỳ thuộc vào yêu cầu. Và chú ý rằng do đây là công thức mảng, bạn cần sử dụng tổ hợp phím CTRL + SHIFT + ENTER để nhập công thức trong Excel.

Phương pháp tổng hợp dữ liệu không cần cột phụ, kết hợp hàm LOOKUP và SUM:

Xét ví dụ dưới đây, trong đó có hai bảng dữ liệu, bảng đầu tiên chứa thông tin về sản phẩm và giá cả; bảng thứ hai ghi lại thông tin khách hàng, sản phẩm và số lượng mà khách đã mua. Mục tiêu là định lượng tổng giá trị mua hàng của một khách hàng cụ thể.

Hướng dẫn Excel nâng cao với hàm SUM và LOOKUP
Hướng dẫn Excel nâng cao với hàm SUM và LOOKUP

Thông thường,

Để thực hiện, chúng ta sẽ sử dụng một cột bổ sung như minh họa dưới đây:

Excel nâng cao, sum kết hợp lookup

Dưới đây là các công thức áp dụng:

</code.

Trong trường hợp không thể thêm cột bổ sung, chúng ta có thể áp dụng công thức dưới đây tại ô K2:

Lưu ý khi áp dụng công thức kết hợp hàm SUM và LOOKUP:

  1. Để hàm LOOKUP hoạt động chính xác, cột A cần được sắp xếp theo thứ tự tăng dần hoặc từ A đến Z.
  2. Để nhập công thức này, cần sử dụng phím CTRL + SHIFT + ENTER. Nếu không, hàm SUM có thể được thay thế bằng hàm SUMPRODUCT.

Bản chất của công thức này là gì?

  1. Phần LOOKUP($E$2:$E$8,$A$2:$A$8,$B$2:$B$8) sẽ trả về kết quả như cột đơn giá trong hình minh họa.
  2. Mảng $F$2:$F$8 là số lượng sản phẩm.
  3. Mảng ($D$2:$D$8=K1) sẽ tạo ra các giá trị ĐÚNG hoặc SAI, khi đó quy tắc TRUE là 1, và FALSE là 0 được áp dụng khi nhân mảng này với một số.
  4. Hàm SUM sẽ tiến hành tổng hợp và cung cấp kết quả cuối cùng.

Bạn có thể tải file Excel để dễ theo dõi từ link này

Kết hợp hàm VLOOKUP và SUMIF để tra cứu và tổng hợp dữ liệu dựa trên điều kiện

Ví dụ sau đây:

Excel nâng cao, kết hợp VLOOKUP và SUMIF
Excel nâng cao, kết hợp VLOOKUP và SUMIF

Ta có hai bảng trong ví dụ này, một bảng chứa tên và ID của các Telesale, bảng còn lại liệt kê ID và doanh số tương ứng. Mục tiêu là tính tổng doanh số cho mỗi Telesale dựa vào tên của họ.

Chúng ta sẽ dùng công thức như sau:

Hàm VLOOKUP sẽ giúp xác định ID của Telesale tương ứng với tên, sau đó ta áp dụng hàm SUMIF để tính tổng giống như cách làm thông thường.

Tính tổng với nhiều điều kiện trong cùng một cột:

Xét ví dụ về việc tính tổng các giao dịch có mã bắt đầu bằng 111 và 131, giả sử tất cả các điều kiện định nghĩa đều nằm trong một cột:

Chúng ta có ba phương pháp tính tổng, với công thức chứa dấu {}, tức là công thức mảng, cần nhấn tổ hợp phím CTRL + SHIFT + ENTER sau khi gõ công thức để nhận được kết quả.

=SUMIF(A:A,”111*”,C:C)+SUMIF(A:A,”131*”,C:C)
{=SUM(SUMIF(A:A,{“111*”,”131*”},C:C))}
{=SUM(C2:C14*(–(LEFT(A2:A14,3)={“111″,”131”})))}

Bài viết này đã giúp ta khám phá một số phương pháp tính tổng kết hợp với các hàm tìm kiếm nhằm phục vụ việc làm báo cáo mà không yêu cầu thêm cột phụ. Mong rằng những chia sẻ trên sẽ hỗ trợ công việc của bạn, đặc biệt trong lĩnh vực kế toán và lập báo cáo kho hàng.

Để áp dụng Excel một cách hiệu quả trong công việc, bạn cần thành thạo nhiều hàm và công cụ khác trong Excel:

Một số hàm cơ bản thường gặp như:

  • SUMIF, SUMIFS
  • Chức năng SUMIF và SUMIFS để tính tổng theo một hoặc nhiều điều kiện khác nhau
  • COUNTIF và COUNTIFS dùng để đếm số liệu theo một hoặc nhiều điều kiện cụ thể
  • Sử dụng các hàm chuyên biệt cho việc xử lý thông tin theo dạng chuỗi, dạng ngày tháng, hay dạng số
  • Sử dụng các hàm tìm kiếm như Index+Match hoặc hàm SUMPRODUCT

Một số công cụ thường hay áp dụng bao gồm:

  • Ứng dụng Conditional formatting để định dạng dựa trên các tiêu chí nhất định
  • Thiết lập Data Validation để đặt ra các điều kiện nhập liệu
  • Phương pháp đặt Name và áp dụng Name trong công thức
  • Kỹ thuật xây dựng báo cáo thông qua Pivot Table

Những ví dụ tiên tiến trong Excel: sự kết hợp của VLOOKUP, SUM và SUMIF

Cách áp dụng hàm Sumif đồng thời với hàm vlookup

Xét ví dụ dưới đây để rõ hơn về việc sử dụng hàm Sumif cùng lúc với hàm Vlookup:

cách sử dụng hàm Sumif cùng với hàm vlookup
Phương pháp sử dụng hàm Sumif kèm hàm vlookup

Bạn có một bảng dữ liệu từ A1:C15, chứa thông tin về Mã hàng, Ngày bán, và Số lượng của mỗi sản phẩm bán ra.

Điều mà chúng ta muốn là tính toán tổng số lượng bán của một sản phẩm được chọn tại ô F11. Trong bảng A1:C15, không hề có mục tên sản phẩm. Để biết tên sản phẩm nào tương ứng với mã nào, ta phải xem tham chiếu tại bảng E1:F8.

Vậy nên:

  • Bản yêu cầu có tính toán theo điều kiện => Dùng hàm SUMIF để tìm ra kết quả
  • Điều kiện không thể xác định một cách trực tiếp và phải xem xét tới bảng khác => Sử dụng VLOOKUP để xác định điều kiện

Cách thức thực hiện như sau:

Tạo hàm SUMIF

Hàm SUMIF có cấu trúc: =SUMIF(Khoảng_cách, Tiêu_chí, Phạm_vi_cộng)

  • Khoảng_cách: phần chứa điều kiện cần xét. Trong bảng A1:C15 chỉ có mã sản phẩm liên quan đến tiêu chí => Chọn khoảng A2:A15 (không lấy dòng tiêu đề)
  • Tiêu_chí: điều kiện đề ra là Tên sản phẩm. Nhưng khoảng cách lại là Mã sản phẩm. Vì thế cần lấy Mã sản phẩm từ Tên => Dùng hàm VLOOKUP.
  • Phạm_vi_cộng: cột Số lượng cần tính là kết quả mong muốn => Lựa chọn phạm vi C2:C15

Tạo hàm VLOOKUP

Khi muốn tham chiếu Mã sản phẩm từ Tên sản phẩm, chúng ta viết hàm VLOOKUP như sau:

=VLOOKUP(giá trị muốn tìm, khoảng tham chiếu, số thứ tự cột chứa kết quả, loại tìm kiếm)

  • Giá trị muốn tìm: tên sản phẩm tại ô F11
  • Khoảng tham chiếu: Khu vực bảng từ E2:F8 (có thể không cần dòng tiêu đề)
  • Số thứ tự cột chứa kết quả: Mã sản phẩm ở cột số 2
  • Loại tìm kiếm: tìm kiếm chính xác theo tên sản phẩm => sử dụng giá trị 0

Điều này cho ra công thức VLOOKUP(F11,E2:F8,2,0).

Xem thêm bài viết: Hướng dẫn chi tiết về hàm vlookup trong Excel và các trường hợp ứng dụng cụ thể

Khi kết hợp hai hàm với nhau, ta được công thức như sau tại ô F13:

=SUMIF(A2:A15,VLOOKUP(F11,E2:F8,2,0),C2:C15)

Cách sử dụng hàm Vlookup trong Excel
Cách áp dụng hàm Vlookup trong Excel

Khi viết hàm SUMIF kết hợp hàm VLOOKUP, điều quan trọng là xác định hàm VLOOKUP sẽ đặt ở đâu trong hàm SUMIF. Thông thường, điều kiện trong hàm SUMIF không được xác định trực tiếp,

Bài viết này từ Siêu Marketing sẽ giải thích cách sử dụng kết hợp giữa hàm Sumifs và hàm vlookup trong Excel

Hàm SUMIFS hỗ trợ công việc tính toán tổng cộng dựa trên nhiều điều kiện. Một số tình huống cụ thể yêu cầu điều kiện báo cáo không thể xác định trực tiếp mà phải tham khảo từ một khu vực dữ liệu khác. Do đó, cần phải kết hợp hàm SUMIFS với hàm VLOOKUP để xử lý.

Xem xét ví dụ dưới đây:

Có tổng cộng 3 điều kiện cần quan tâm:

  • Điều kiện 1: Ngày bắt đầu từ 01/06/2018
  • Điều kiện 2: Ngày kết thúc vào 30/06/2018
  • Điều kiện 3: Sản phẩm cần tìm là Mận Hà Giang loại 2 (có mã là M102 trong bảng E1:F8)

Do đó, dùng hàm SUMIFS là cần thiết để tính lượng hàng bán ra các ngày trong tháng với những điều kiện trên.

Tuy nhiên, tham chiếu tới tên hàng trong điều kiện thứ 3 yêu cầu chúng ta phải xem xét bảng E1:F8. Qua tên hàng được chọn trong ô F13, hàm Vlookup giúp xác định mã hàng và từ đó chúng ta có thể sử dụng mã này để tìm cột Mã hàng trong ô A1:C15, giúp SUMIFS hoạt động chính xác.

Cấu trúc viết hàm SUMIFS

Cách đặt hàm SUMIFS với 3 điều kiện:

=SUMIFS(Khu vực tính tổng, khu vực điều kiện 1, điều kiện số 1, khu vực điều kiện 2, điều kiện số 2, khu vực điều kiện 3, điều kiện số 3)

  • Khu vực tính tổng: Liệt kê số lượng trong cột C, khu vực C2:C15
  • Khu vực điều kiện thứ nhất: Ngày tháng trong cột B, địa điểm B2:B15
  • Điều kiện số 1: Ngày bắt đầu từ 01/06/2018, ô F11. Cách dùng là “>=”&F11
  • Khu vực điều kiện thứ hai: Ngày tháng trong cột B, địa điểm B2:B15
  • Điều kiện số 2: Ngày kết thúc vào ngày 30/06/2018, ô F12. Cách dùng là “<=”&F12
  • Khu vực điều kiện thứ ba: Cột Mã hàng, khu vực A2:A15
  • Điều kiện số 3: Mã hàng sẽ được tham chiếu bằng hàm Vlookup dựa trên tên hàng ở ô F13

Hướng dẫn cách viết hàm VLOOKUP

Khi cần liên kết Mã hàng với Tên hàng, hàm VLOOKUP sẽ được thiết lập như sau:

=VLOOKUP(giá trị tìm, khu vực tham khảo, cột chứa thông tin cần tìm, kiểu tìm kiếm)

  • Giá trị tìm: Tên hàng ở ô F13
  • Khu vực tham khảo: Khu vực từ E2 đến F8 (không tính dòng tiêu đề)
  • Cột chứa thông tin: Mã hàng là thông tin ở cột thứ 2
  • Kiểu tìm kiếm: Tìm chính xác, do đó nhập số 0

Kết hợp chúng ta có công thức VLOOKUP(F13,E2:F8,2,0)

Kết quả khi ghép hai hàm sẽ xuất hiện ở ô F15 như sau:

=SUMIFS(C2:C15,B2:B15,">="&F11,B2:B15,"<="&F12,A2:A15,VLOOKUP(F13,E2:F8,2,0))

Bạn có thể thay đổi tên hàng trong ô F13 và quan sát sự biến đổi của kết quả hàm SUMIFS trong ô F15.

Điểm kết

Khả năng viết hàm SUMIFS có thể áp dụng cho nhiều điều kiện khác nhau. Điều này đặt ra nhu cầu xác định liệu có thể trực tiếp tìm thấy điều kiện đó trong bảng dữ liệu hay không.

  • Nếu có thể: Liên kết trực tiếp với điều kiện đó (như giá trị ngày tháng trong ví dụ).
  • Nếu không thể: Dùng thêm các hàm khác như VLOOKUP, DATE, EOMONTH, và các hàm khác để từ các điều kiện đó có thể gián tiếp tạo ra điều kiện có thể xác định được.

Đây chính là một kỹ thuật cơ bản giúp bạn lập ra các báo cáo một cách chính xác và nhanh chóng theo nhiều tiêu chí khác nhau.

Hy vọng bạn có thể áp dụng những kiến thức này vào công việc của mình!

Kiến thức là không giới hạn, và bạn có thể học tất cả những điều này trong khóa học SM90 – Excel từ cơ bản đến nâng cao tại Siêu Marketing. Đây là khóa học giúp bạn nắm vững kiến thức một cách đầy đủ, chi tiết và không giới hạn thời gian. Bạn có thể học bất kỳ lúc nào và tra cứu thông tin một cách tiện lợi. Tham khảo thông tin chi tiết và ưu đãi tại: sieumarketing.com

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop