Kết hợp hàm VLOOKUP và SUM
Trong bài viết này, mình sẽ hướng dẫn các bạn làm thế nào để có thể kết hợp các hàm VLOOKUP, SUM và SUMIF để dò tìm và tính tổng dựa trên 1 số điều kiện. Mục lục Show
Kết hợp hàm VLOOKUP và SUMIFTrước khi bắt đầu vào bài này, chắc hẳn các bạn đã thành thạo với hàm VLOOKUPvà hàm SUMIF rồi. Vậy nên chúng ta có thể bắt đầu vào ngay ví dụ đầu tiên. Trong ví dụ này, chúng ta sẽ đi tính tổng sản lượng của Cam trong tháng 1, tháng 2, tháng 3 bằng cách sử dụng công thức sau đây:
Sau khi nhập công thức này, các bạn lưu ý vì đây là công thức mảng, vậy nên sau khi nhập công thức, bạn phải sử dụng tổ hợp phím
Mục đích đưa ra những công thức phức tạp này, chúng ta sẽ phục vụ cho việc tạo báo cáo hoặc dashboard. Nếu chúng ta sử dụng một công thức tính tổng đơn thuần bằng hàm SUM, khi chúng ta có 1 ô chứa các loại hàng hoá, muốn thay đổi ô này để tính tổng sản lượng của 1 sản phẩm, chúng ta phải thay đổi công thức SUM theo. Với công thức mảng kết hợp hàm SUM và VLOOKUP như ở trên, chúng ta có thể tạo ra 1 báo cáo về sản lượng của các sản phẩm 1 cách nhanh chóng như sau: Từ ví dụ phía trên, tất nhiên, ta có thể thay hàm SUM bằng 1 số hàm khác để phục vụ mục đích của chúng ta như hàm AVERAGE, hàm MIN, hàm MAX hoặc thực hiện các phép tính toán khác … Và lưu ý vì các công thức này đều là công thức mảng, nên bạn cần sử dụng tổ hợp phím tắt CTRL + SHIFT + ENTER khi nhập công thức này trong Excel.Tổng hợp dữ liệu không thêm cột phụ, kết hợp hàm LOOKUP và SUM:Chúng ta có ví dụ sau, trong ví dụ này, chúng ta có 2 bảng dữ liệu, 1 bảng gồm có sản phẩm và đơn giá; bảng thứ 2 bao gồm khách hàng, sản phẩm và số lượng sản phẩm khách hàng đã mua. Chúng ta sẽ muốn đi tính tổng giá trị của 1 khách hàng Excel nâng cao, kết hợp hàm SUM và LOOKUPBình thường, chúng ta sẽ cần dùng cột phụ như sau: Các công thức như sau:
Chú ý khi sử dụng công thức kết hợp SUM và LOOKUP này:
Công thức này được hiểu như thế nào?
Tới đây, bạn có thể download file excel để theo dõi cho dễ tại đây Kết hợp hàm VLOOKUP và SUMIF tra cứu và tổng hợp dữ liệu theo điều kiệnVí dụ: Excel nâng cao, kết hợp VLOOKUP và SUMIFTrong ví dụ này, ta có 2 bảng, 1 bảng là tên Telesale và ID của họ, 1 bảng khác chỉ có ID và doanh số. Nhiệm vụ ở ví dụ này: cần đi tính tổng doanh số của bất kì Telesale nào dựa vào tên của họ. Công thức chúng ta sẽ sử dụng ở đây là
VLOOKUP sẽ có nhiệm vụ đưa lại mã ID của Telesale với tên tương ứng, dựa vào ID này chúng ta áp dụng SUMIF như một ví dụ đơn giản bình thường. Tính tổng nhiều điều kiện ở cùng 1 cột:Chúng ta có ví dụ tính tổng sau đây, điều kiện tính tổng đều năm ở 1 cột: Tính tổng của các giao dịch với đầu mã là 111 và 131 Để làm được điều này, chúng ta có 3 cách tính, với cách mà công thức có dấu {}, nghĩa là công thức mảng, bạn cần nhấn tổ hợp phím tắt CTRL + SHIFT + ENTER sau khi nhập công thức để được kết quả
Vậy là trong bài này, chúng ta đã cùng nhau tìm hiểu một số cách kết hợp công thức, hàm tính tổng với các hàm dò tìm để giải quyết nhu cầu làm báo cáo mà không cần dùng thêm cột phụ. Hi vọng bài viết có ích cho công việc của các bạn đặc biệt là trong công việc kế toán, lập báo cáo trong kho. Ngoài ra để ứng dụng Excel vào công việc một cách hiệu quả thì bạn còn phải sử dụng tốt các hàm, các công cụ khác của Excel: Một số hàm cơ bản thường gặp như:
Một số công cụ hay sử dụng như:
Các ví dụ Excel nâng cao: kết hợp VLOOKUP, SUM và SUMIFCách sử dụng hàm Sumif kết hợp hàm vlookupĐể giúp chúng ta dễ hình dung được khi nào thì sử dụng hàm Sumif kết hợp hàm vlookup, hãy xem ví dụ sau đây: Cách sử dụng hàm Sumif kết hợp hàm vlookupChúng ta có 1 bảng dữ liệu từ A1:C15, gồm các cột Mã hàng, Ngày bán, Số lượng bán được của từng mặt hàng. Tuy nhiên yêu cầu của chúng ta là tính tổng số lượng bán theo Tên mặt hàng được chọn trong ô F11. Trong bảng dữ liệu A1:C15 không có cột tên mặt hàng. Muốn biết Tên mặt hàng đó ứng với Mã hàng nào, chúng ta phải tham chiếu trong bảng E1:F8. Như vậy:
Cách làm như sau: Viết hàm SUMIF Cấu trúc của hàm SUMIF bao gồm:
Viết hàm VLOOKUPChúng ta muốn tham chiếu Mã hàng dựa vào Tên hàng, do đó hàm VLOOKUPsẽ viết như sau: =VLOOKUP(giá trị tìm kiếm, vùng tham chiếu, cột chứa kết quả, phương thức tìm kiếm)
Như vậy ta có VLOOKUP(F11,E2:F8,2,0) Xem thêm: Hàm vlookup trong Excel và các ứng dụng nâng cao thường gặp Khi kết hợp hai hàm với nhau ta có công thức tại ô F13 như sau:
Khi viết hàm SUMIF kết hợp với hàm VLOOKUP, chúng ta cần phải xác định: hàm VLOOKUP sẽ đặt tại vị trí tham số nào trong hàm SUMIF. Thông thường hay gặp nhất chính là: Điều kiện trong hàm SUMIF không xác định được 1 cách trực tiếp, mà phải tham chiếu tới 1 bảng tính nào đó. Hướng dẫn cách sử dụng hàm Sumifs kết hợp hàm vlookup trong ExcelHàm SUMIFS là một hàm rất hữu ích trong Excel giúp chúng ta có thể tính tổng theo nhiều điều kiện cùng lúc. Nhưng trong một số trường hợp điều kiện của báo cáo lại không xác định được một cách trực tiếp mà phải tham chiếu tới 1 vùng dữ liệu khác. Khi đó chúng ta cần phải kết hợp hàm SUMIFS với hàm VLOOKUP. Trong bài viết này Học Excel Online sẽ hướng dẫn các bạn biết sử dụng hàm Sumifs kết hợp hàm vlookup trong Excel. Trước tiên hãy xét ví dụ sau đây: Trong yêu cầu trên, chúng ta thấy có tới 3 điều kiện:
Vì vậy để có thể tính được số lượng bán thỏa mãn đồng thời cả 3 điều kiện trên, chúng ta sẽ cần dùng tới hàm SUMIFS. Nhưng ở điều kiện 3 là điều kiện liên quan tới tên hàng. Tên hàng không có sẵn trong bảng dữ liệu A1:C15 mà phải xác định thông qua bảng E1:F8. Từ tên hàng (ở ô F13) tham chiếu ra mã hàng tương ứng. Sử dụng mã hàng đó tham chiếu tiếp tới cột Mã hàng trong bảng A1:C15 để ra kết quả cho hàm SUMIFS. Cách viết hàm SUMIFS Cấu trúc hàm SUMIFS với 3 điều kiện bao gồm:
Cách viết hàm VLOOKUP húng ta muốn tham chiếu Mã hàng dựa vào Tên hàng, do đó hàm VLOOKUP sẽ viết như sau:
Như vậy ta có VLOOKUP(F13,E2:F8,2,0) Khi kết hợp 2 hàm này, chúng ta có kết quả tại ô F15 như sau: Kết luậnHàm SUMIFS có thể viết được với rất nhiều điều kiện. Khi đó chúng ta phải xác định rõ: Điều kiện đó có thể xác định trực tiếp được trong bảng dữ liệu không.
Đây là một trong những kỹ thuật căn bản giúp lập báo cáo theo nhiều điều kiện một cách chính xác, nhanh chóng. Chúc các bạn áp dụng tốt kiến thức này vào công việc nhé! Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online |