Việc xác định đơn giá [hay tính đơn giá] theo nhiều điều kiện thường khiến chúng ta gặp nhiều khó khăn. Bời việc này đòi hỏi bạn phải sử dụng tốt các hàm, biết cách tổ chức bảng dữ liệu và có tư duy logic tốt. Để giúp các bạn rèn luyện thêm những điều đó, chúng ta hãy cùng Học Excel Online tìm hiểu 4 cách xác định đơn giá theo nhiều điều kiện trong excel nhé.
Bạn đang xem: Cách tính đơn giá trong excel
Ví dụ chúng ta có bảng dữ liệu bán hàng như sau:
Đơn giá của mỗi sản phẩm được xác định dựa theo 2 yếu tố: Mã hàng và Mã công ty.
Yêu cầu là hãy xác định đơn giá dựa vào mã công ty kết hợp với mã hàng tra trong bảng đơn giá để có giá trị tương ứng tại vùng G3:G9.
Xem nhanh
Cách thứ 1: Dùng hàm VLOOKUP kết hợp hàm MATCH
Tham khảo thêm bài viết: Cách sử dụng hàm vlookup kết hợp hàm Match khi tìm theo nhiều cột chứa kết quả
Khi nghĩ tới việc tham chiếu 1 giá trị [cụ thể ở đây là đơn giá] theo nhiều điều kiện [từ 2 điều kiện trở lên], chúng ta nghĩ tới việc sử dụng hàm VLOOKUP kết hợp hàm MATCH.
Trọng tâm của cách làm này là các bạn phải xác định VLOOKUP theo giá trị nào, MATCH theo giá trị nào. Để làm điều đó, chúng ta chú ý cấu trúc của bảng đơn giá:
Mã công ty nằm ở cột đầu tiên trong bảng đơn giá => Theo nguyên tắc hàm VLOOKUP thì giá trị tìm kiếm phải nằm ở cột đầu tiên. Vì vậy VLOOKUP sẽ tìm theo mã công ty.Kết quả cần tìm của hàm VLOOKUP là đơn giá của các mã hàng, tương ứng theo cột thứ mấy trong bảng. Vì vậy để xác định cột thứ mấy, chúng ta dùng hàm MATCH để xét giá trị Mã hàng.Xem thêm: Sắp Được Chơi Overwatch Chơi Miễn Phí 100% ? Overwatch Cho Chơi Miễn Phí Từ 20/11
Cấu trúc như sau: VLOOKUP[Mã công ty, Bảng đơn giá, MATCH[mã hàng, dòng mã hàng, 0], 0]
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=VLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Cách thứ 2: Dùng hàm HLOOKUP kết hợp hàm MATCH
Hàm HLOOKUP cũng là 1 hàm tìm kiếm tương tự với VLOOKUP, chỉ thay đổi về phương hướng, chiều tìm kiếm. Trong trường hợp này chúng ta biện luận như sau:
Đối tượng tìm kiếm của hàm HLOOKUP phải nằm ở dòng đầu tiên của bảng tìm kiếm. Vì vậy chúng ta thấy dòng Mã hàng [dòng 13] là dòng đầu tiên của vùng bảng đơn giá hàng hóa. Do đó đối tượng tìm kiếm của hàm HLOOKUP trong trường hợp này là Mã hàng.Khi đó kết quả của hàm HLOOKUP sẽ lấy tương ứng xuống bao nhiêu dòng? Căn cứ vào mã công ty để xác định dòng. Vì vậy chúng ta dùng hàm MATCH tìm theo Mã công ty.Cấu trúc như sau: HLOOKUP[Mã hàng, Bảng đơn giá, MATCH[Mã công ty, cột Mã công ty, 0], 0]
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=VLOOKUP[C3,$B$14:$F$17,MATCH[B3,$B$13:$F$13,0],0]
Cách thứ 3: Dùng hàm INDEX và MATCH
Bạn có biết rằng dùng Index + Match thì tốt hơn dùng Vlookup hay Hlookup? Nếu không tin thì bạn hãy xem lại bài viết:
Vì sao dùng INDEX và MATCH tốt hơn dùng VLOOKUP trong Excel
Trong trường hợp này, chúng ta cùng tìm hiểu cách sử dụng INDEX MATCH như thế nào nhé.
Khi nhắc tới INDEX MATCH, các bạn hãy ghi nhớ 3 yếu tố:
Vùng dữ liệu nào? Chúng ta cần tính đơn giá, do đó xét vùng B13:F17 là Bảng đơn giá.Kết quả ở dòng nào? Xác định dòng dựa theo hàm MATCH, tìm theo Mã công ty.Kết quả ở cột nào? Xác định cột dựa theo hàm MATCH, tìm theo Mã hàng.Cấu trúc cụ thể: INDEX[Bảng dữ liệu, MATCH[Mã công ty, Cột Mã công ty,0], MATCH[Mã hàng, dòng Mã hàng,0]]
Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=INDEX[$B$13:$F$17,MATCH[C3,$B$13:$B$17,0],MATCH[B3,$B$13:$F$13,0]]
Cách thứ 4: Dùng hàm SUMPRODUCT xác định đơn giá theo nhiều điều kiện
Hàm SUMPRODUCT là một hàm sử dụng được trong rất nhiều trường hợp. Và chúng ta hoàn toàn có thể ứng dụng hàm này trong việc xác định đơn giá theo nhiều điều kiện. Cụ thể như sau:
Khi nào áp dụng được? Khi bảng đơn giá không có các mã trùng nhau.Cách dùng: Xét 2 điều kiện dạng mảng 2 chiều: Mã hàng [vùng C13:F13] và Mã công ty [vùng B14:B17], kết quả tương ứng lấy trong vùng C14:F17Khi thay các nội dung thành tọa độ tham chiếu ta có:
G3=SUMPRODUCT[[$B$14:$B$17=C3]*[$C$13:$F$13=B3]*$C$14:$F$17]
Cả 4 cách đều cho ra kết quả giống nhau.
Bạn thấy đó, để thực hiện 1 yêu cầu trong Excel chúng ta có thể có nhiều cách làm khác nhau. Có rất nhiều hàm có thể sử dụng, kết nối với nhau giúp chúng ta đạt được kết quả như mong muốn. Hãy tìm hiểu thêm nhiều kiến thức của Excel cùng Học Excel Online bạn nhé.
Trong quá trình quản lý hàng hóa, chúng ta phân loại hàng và đặt các mã hàng chứa một số ký tự giống nhau cho các mặt hàng cùng loại. Vậy làm thế nào để chúng ta tìm và tính đơn giá trong Excel với các mã hàng đó? Câu trả lời chính là hàm VLOOKUP. Cùng Gitiho tìm hiểu cách làm trong bài viết ngày hôm nay nhé.
Xem thêm: Hướng dẫn cách viết các hàm trong Excel chi tiết dễ hiểu nhất
Tuyệt đỉnh Excel - Trở thành bậc thầy Excel trong 16 giờ
Giới thiệu về hàm VLOOKUP trong Excel
Công thức hàm VLOOKUP trong Excel
Hàm VLOOKUP là một trong những hàm tham chiếu Excel thông dụng nhất giúp người dùng tìm kiếm dữ liệu trong trang tính. Công thức hàm như sau:
=VLOOKUP[lookup_value, table_array, col_index_ num, [range_lookup]]
Trong đó:- lookup_value là giá trị cần dò tìm.
- table_array là bảng giới hạn để dò tìm.
- col_index_num là số thứ tự của cột lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải.
- range_lookup là giá trị Logic [TRUE=1, FALSE=0] xác định Excel cần so sánh, tìm kiếm chính xác hay so sánh, tìm kiếm tương đối với bảng giới hạn.
- Nếu Range_lookup = 1 [TRUE]: So sánh tương đối.
- Nếu Range_lookup = 0 [FALSE]: So sánh chính xác.
- Nếu bỏ qua đối này thì Excel hiểu là range_lookup = 1 một cách mặc định.
Một số lưu ý với hàm VLOOKUP trong Excel
Khi sử dụng hàm VLOOKUP để tìm kiếm giá trị trong Excel, bạn cần lưu ý các điểm sau đây:
- Hàm VLOOKUP không phân biệt chữ hoa và chữ thường.
- Danh sách tìm kiếm phải được sắp xếp trước khi sử dụng hàm.
- Hàm VLOOKUP chỉ tìm kiếm từ trái qua phải.
- Hàm VLOOKUP chỉ tìm kiếm và trả về giá trị đầu tiên xuất hiện.
- Số cột tìm kiếm trong công thức hàm phải nhỏ hơn hoặc bằng tổng số cột trong bảng tìm kiếm.
Xem thêm: Sử dụng hàm VLOOKUP cơ bản: Hướng dẫn tìm hiểu và sử dụng qua các ví dụ
Tìm và tính đơn giá trong Excel bằng hàm VLOOKUP
Chúng ta có một ví dụ về bảng theo dõi bán hàng và bảng giá để thực hành tính đơn giá trong Excel bằng hàm VLOOKUP như trong hình dưới đây.
Nhiệm vụ của chúng ta là điền đúng giá tương ứng với các mã hàng vào trong bảng theo dõi bán hàng. Ta nhận thấy tại bảng giá, các mã hàng được hiển thị dưới dạng các ký tự ghép giữa 2 ký tự đầu và 2 ký tự cuối trong mã hàng tại bảng theo dõi bán hàng.
Đầu tiên, chúng ta cần xác định mã ghép tại bảng giá.
- Để lấy 2 ký tự đầu của mã hàng, chúng ta sử dụng hàm LEFT.
- Để lấy 2 ký tự cuối của mã hàng, chúng ta sử dụng hàm RIGHT.
Sau đó, chúng ta sử dụng hàm CONCAT để nối kết quả của hàm LEFT và hàm RIGHT vừa thực hiện:
=CONCAT[LEFT[A2,2],RIGHT[A2,2]]
Tiếp theo, chúng ta sử dụng hàm VLOOKUP để tìm và tính đơn giá trong Excel dựa vào mã tạo bởi hàm CONCAT bằng công thức:
=VLOOKUP[CONCAT[LEFT[C3,2],RIGHT[[C3,2]],$I$3:$K$6,3,0]
Trong đó:
lookup_value là kết quả hàm CONCAT.
lookup_array là bảng giá [I3:K6].
col_index_num là cột Đơn giá.
range_lookup là phương thức tìm kiếm chính xác theo mã hàng.
Kết quả như sau:
Xem thêm: Hướng dẫn cách dùng hàm VLOOKUP nâng cao
Tổng kết
Như vậy, chúng ta đã tìm hiểu cách tìm và tính đơn giá trong Excel bằng cách sử dụng hàm VLOOKUP kết hợp các hàm xử lý văn bản. Để học thêm các cách sử dụng hàm tham chiếu này vào công việc hàng ngày, các bạn hãy tham khảo các bài viết trên blog Gitiho và nhanh tay đăng kí khóa học Tuyệt đỉnh Excel với chúng mình nhé.
Gitiho chúc các bạn thành công!
Tham khảo thêm các bài viết về Excel dưới đây:
Hướng dẫn cách dùng hàm index trong Excel chi tiết nhất
Hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong Excel
Hướng dẫn cách sắp xếp ngày trong Excel
Hướng dẫn cách dùng hàm VLOOKUP qua các ví dụ từ cơ bản đến nâng cao
Hướng dẫn cách sử dụng hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau và hơn thế
Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản