Cách tính đơn giá loại 1 loại 2 trong Excel cực dễ hiểu và đơn giản

Nhắc tới Excel, người ta nghĩ ngay tới những công thức từ cơ bản tới khó và để thành thạo được chúng thì cần phải bỏ rất nhiều thời gian, công sức làm bài tập rèn luyện. Trong Excel cơ bản, số lượng bài tập rất nhiều, đa dạng về thể loại nhằm nâng cao khả năng thành thạo kỹ năng Excel của các bạn. Một trong số đó chính là bài tập tính đơn giá loại 1 loại 2, một thể loại bài tập đã gây rất nhiều khó khăn cho các bạn mới vì nó đòi hỏi tính tư duy tuy không cao nhưng cũng đủ để khiến bạn phải lên mạng tìm tòi.

Bài viết ngày hôm nay của Tin Học Thành Luân sẽ giới thiệu tới các bạn 4 cách để có thể giải quyết được bài toán tính đơn giá loại 1, loại 2 này. Trong bài viết khi thực hành làm với các ví dụ sẽ có nhiều hàm cơ bản, bạn có thể tham khảo tới các bài viết về hàm đó để hiểu hơn về cách sử dụng hàm. Hoặc các bạn có thể tham gia ngay khóa học Excel online cơ bản tới nâng cao dành cho người mới với gần 80 giờ học, bài tập và kiểm tra. Nào, chúng ta bắt đầu đi tìm hiểu 4 cách tính đơn giá loại 1 loại 2 trong Excel ngay dưới đây thôi nào!

Cách tính đơn giá loại 1 loại 2 trong Excel

Khi tính toán hay làm một bài toán bất kỳ nào đó, bạn hãy tư duy về cách làm bài đó như thế nào ví dụ như cách sử dụng nếu…thì. Nếu nó như này thì kết quả sẽ như này, vậy dùng công thức gì để có thể làm được điều đó, bạn hãy lên trên mạng tìm kiếm với từ khóa đó và sẽ có cho bạn câu trả lời.

Cách tính đơn giá cũng vậy, khi bạn lên mạng tìm kiếm cách làm thì sẽ có rất nhiều cách giúp bạn và cái bạn cần là tư duy làm bài, xử lý bài toán đó. Bài viết của mình trên trang web này đều hướng tới cách làm từng bước chi tiết để bạn có thể hiểu hơn và có thể áp dụng vào những bài toán sau.

Đối với bài toán đơn giá loại 1, loại 2 này có thể hiểu đơn giản một sản phẩm A nào đó có 2 chất lượng tốt và chất lượng đủ dùng thì sẽ có 2 loại đơn giá. Ngoài ra, có thể có nhiều loại đơn giá hơn nhưng về cách làm thì nó cũng sẽ giống như bài toán đơn giá loại 1 loại 2. Đầu tiên, mình sẽ giới thiệu tới các bạn 3 hàm sẽ xuất hiện trong ví dụ sắp tới đó là hàm IF, hàm VLOOKUP và hàm LEFT.  Và với 4 cách tương ứng với khá nhiều hàm chứa trong các ví dụ mình tin chắc sẽ giúp các bạn hiểu hơn về các hàm cơ bản này.

(Cách 1) Sử dụng hàm IF lồng hàm Vlookup

Giả sử, chúng ta sẽ có bảng báo cáo bán hàng dưới đây:

* Chú giải: 2 ký tự đầu của MÃ MH cho biết Mặt hàng, ký tự cuối của MÃ MH cho biết Đơn giá (Loại 1 hay Loại 2).

Và chúng ta có thêm một bảng Đơn Giá như sau:

Bạn có thể tải bài tập về và thực hành ngay trong quá trình đọc bài viết: Tải bài tập tính đơn giá loại 1 loại 2 trong Excel

Yêu cầu bài toán như sau:

1. Tính ĐƠN GIÁ: Căn cứ vào MÃ MH, tra cứu ở bảng ĐƠN GIÁ.
2. PHÍ CHUYÊN CHỞ: = 1% * ĐƠN GIÁ đối với mặt hàng loại 1 và 5% * ĐƠN GIÁ đối với mặt hàng loại 2.
3. THÀNH TIỀN = SỐ LƯỢNG * (ĐƠN GIÁ + PHÍ CHUYÊN CHỞ).
4. TỔNG CỘNG = THÀNH TIỀN – TIỀN GIẢM biết rằng nếu THÀNH TIỀN >= 1000 sẽ giảm 1% * THÀNH TIỀN và định dạng với không số lẻ.

Với ví dụ này ở câu số 1 chúng ta sẽ làm với 4 cách, cách đầu tiên sẽ dùng hàm IF lồng VLOOKUP. Hai hàm này rất dễ sử dụng trong Excel, mình sẽ nêu lại cú pháp của 2 hàm này như sau:

Cú pháp hàm IF: =IF(điều kiện logic, kết quả trả về nếu điều kiện đúng, kết quả trả về nếu điều kiện sai)

Cú pháp hàm VLOOKUP: =VLOOKUP(giá trị mang đi dò tìm, vùng dò tìm, số thứ tự cột cần dò tìm, chỉ số chính xác hoặc tương đối)

Và khi hàm IF lồng hàm VLOOKUP thì cú pháp nó sẽ như thế này: =IF(điều kiện logic, VLOOKUP(…), VLOOKUP(…))

Điều kiện logic ở đây chính là đơn giá khi bằng 1 hoặc bằng 2, hai hàm VLOOKUP ở vị trí sau sẽ làm nhiệm vụ dò tìm đơn giá khi đã biết mặt hàng đó là loại 1 hay loại 2. Thể hiện trong công thức đối với yêu cầu tính đơn giá khi sử dụng hàm IF lồng VLOOKUP sẽ như sau:

Công thức: =IF(RIGHT(A6, 1)= “1”, VLOOKUP(LEFT(A6, 2), $A$18:$C$22, 3, 0), VLOOKUP(LEFT(A6, 2), $A$18:$D$22, 4, 0))

RIGHT(A6, 1) = “1”: Phần điều kiện đơn giá =1 hoặc =2 phải dùng hàm RIGHT để lấy 1 ký tự bên phải của mã hàng hóa.

LEFT(A6, 2): Lấy 2 ký tự là Mã Hàng Hóa  để đem đi dò tìm trong bảng đơn giá

VLOOKUP(LEFT(A6, 2), $A$18:$C$22, 3, 0): Dò tìm đơn giá loại 1 bởi điều kiện logic chúng ta để là 1 thì nếu điều kiện là đúng nó sẽ thực hiện công thức này.

VLOOKUP(LEFT(A6, 2), $A$18:$D$22, 4, 0): Ngược lại, nếu điều kiện sai tức là Mã Hàng Hóa chứa số 2 thì sẽ thực hiện công thức này

Chú ý:

“1”: Khi làm công thức phải cho số 1 vào dấu “” bởi kết quả trả về của hàm LEFT là một ký tự chữ (Text) nên phải cho số 1 vào trong dấu ngoặc kép để biến số 1 thành kiểu chữ (Text). Nếu không công thức sẽ vẫn chạy nhưng sai kết quả.

LEFT(A6, 2): Phần này hãy chú ý dấu đóng ngoặc nếu không sẽ bị lẫn sang thành phần của hàm VLOOKUP

$A$18:$C$22: Hãy cố định vùng khi dò tìm cố định bằng hàm VLOOKUP bởi nếu bạn không cố định lại sẽ dẫn tới không tìm kiếm được hiển thị lỗi #N/A.

Kết quả của câu 1 như ảnh dưới đây, bạn hãy đối chiếu xem kết quả có đúng như vậy không nhé:

Từ câu số 2 tới câu số 4 chúng ta sẽ làm ở phần cuối cùng, mình sẽ hướng dẫn các bạn các cách tính đơn giá trong Excel bằng nhiều hàm khác nhau trước. Vì những câu sau khá dễ các bạn có thể tự làm được, tiếp dưới đây sẽ là hàm VLOOKUP lồng hàm IF.

(Cách 2) Sử dụng hàm Vlookup lồng hàm IF

Chắc các bạn sẽ thấy lạ vì trên đã có hàm IF lồng hàm VLOOKUP rồi mà cách sau vẫn là hàm VLOOKUP lồng hàm IF phải không nào? Đừng vội, hãy cũng mình khám phá thêm cách thứ 2 này sẽ rất thú vị và bạn sẽ có thể đúc kết lựa chọn riêng cho mình một trong 2 cách đầu tiên này.

Đối với cách này, các bạn chỉ xáo trộn ngược lại của hàm VLOOKUP và hàm IF làm sao hàm IF sẽ nằm trong thành phần số thứ tự cột. Bởi vì hàm VLOOKUP sẽ lấy ra giá trị nhờ vào số thứ tự cột nên khi cho hàm IF vào thành phần này sẽ trả về số thứ tự loại 1 hoặc 2.

Công thức chung của hàm VLOOKUP lồng hàm IF như sau: =VLOOKUP(giá trị dò tìm, vùng dữ liệu, IF(điều kiện logic, STT cột loại 1, STT loại 2), 0)

Khi nhìn vào công thức chung trên bạn đã có thể hình dung ra được công thức chạy như thế nào rồi đúng không nào? Vậy công thức chuẩn cho câu 1 này khi sử dụng hàm VLOOKUP lồng hàm IF sẽ như sau:

Công thức hàm VLOOKUP lồng hàm IF: =VLOOKUP(LEFT(A6, 2), $A$18:$D$22, IF(RIGHT(A6, 1) = “1”, 3, 4), 0)

LEFT(A6, 2): Lấy 2 ký tự đầu của Mã Hàng Hóa mang đi dò tìm

RIGHT(A6, 1) = “1”: Lấy ra ký tự số là 1

3, 4: Đây chính là số thứ tự cột đơn giá loại 1 hoặc loại 2, bạn có thể đếm từ trái qua phải bảng Đơn Giá

Chú ý:

$A$18:$D$22: Vùng dò tìm cần phải cố định lại nếu không sẽ bị lỗi #N/A

RIGHT(A6, 1) = “1”: Giống như chú ý ở trên thì chỗ này bạn cũng cần đưa số 1 vào dấu ngoặc kép để tránh gây lỗi.

Vậy là 2 hàm IF và hàm VLOOKUP đã phát huy tác dụng khi chúng kết hợp lại và xử lý được bài toán tính đơn giá loại 1 và loại 2 này. Tiếp theo dưới đây, mình sẽ giới thiệu tới các bạn cách số 3 là sử dụng hàm INDEX và hàm MATCH.

(Cách 3) Sử dụng hàm Index kết hợp hàm Match

Hàm INDEX và hàm MATCH đều là 2 hàm tìm kiếm chỉ khác về phạm vi sử dụng của mỗi hàm, ví dụ như hàm INDEX nó sẽ giúp bạn tìm giá trị trong một cột hoặc hàng cùng 1 công thức. Còn hàm MATCH thì chỉ có thể tìm kiếm giá trị trong một cột hoặc một hàng. Khi 2 hàm này kết hợp với nhau sẽ rất cơ động và mạnh mẽ, bạn hãy xem công thức chung sau đây để biết được cách kết hợp 2 hàm này với nhau nhé.

Công thức chung: =INDEX(vùng cần lấy đơn giá, MATCH(2 ký tự đầu của Mã HH, vùng Mã HH ở bảng Đơn Giá,0), MATCH(ký tự thứ 3 của Mã HH, vùng loại đơn giá, 0))

Khá dài phải không nào, nhưng đừng lo khi bạn viết công thức thực tế nó sẽ không dài như vậy đâu, bạn hãy xem công thức thực tế dưới đây:

Công thức: =INDEX($C$18:$D$22, MATCH(LEFT(A6, 2), $A$18:$A$22, 0), MATCH(VALUE(RIGHT(A6, 1)), $C$17:$D$17, 0))

Ở đây, mình đã bỏ chữ “Loại” tại bảng Đơn Giá còn lại số 1 và số 2 để sử dụng trong công thức được thuận tiện hơn. Trong công thức trên mình cũng sử dụng thêm hàm VALUE để biến đổi kiểu Text (chữ) về kiểu Number (số). Và trong công thức cũng sử dụng 2 lần hàm MATCH để tìm ra số thứ tự cột và hàng trả về kết quả đơn giá.

Giải thích công thức:

$C$18:$D$22: Vùng dò tìm cần lấy đơn giá, bạn có thể xem trực tiếp trong file excel

LEFT(A6, 2): Lấy 2 ký tự đầu của Mã HH

VALUE(RIGHT(A6, 1): Lấy 1 ký tự cuối của Mã HH đồng thời chuyển đổi kiểu Text về Number

$A$18:$A$22: Vùng dò tìm đơn giá loại 1 hoặc loại 2

Chú ý:

$C$18:$D$22: Cố định vùng dò tìm nếu không sẽ bị lỗi #N/A

VALUE(RIGHT(A6, 1)): Nếu bạn không để thêm hàm VALUE ở ngoài để chuyển kiểu TEXT thành NUMBER, Excel sẽ không hiểu bạn đang tìm cái gì trong 2 đơn giá loại 1 và loại 2. Vì giả sử RIGHT(A6, 1) trả về kiểu TEXT mà bạn lại mang đi dò tìm trong vùng kiểu NUMBER là sai.

(Cách 4) Sử dụng hàm Vlookup kết hợp hàm Match

Cách cuối cùng trong bài viết ngày hôm nay đó là chúng ta sẽ sử dụng hàm VLOOKUP kết hợp hàm MATCH để xử lý được bài toán đơn giá loại 1 loại 2 này. Tương tự như những cách trên thì 2 hàm này sẽ có công thức chung như sau: =VLOOKUP(2 ký tự đầu của Mã HH, vùng bảng Đơn Giá, MATCH(ký tự cuối của Mã HH, Vùng Đơn Giá 1 2, 0), 0).

Công thức sử dụng 2 hàm này có phần giống với cách số 3 bởi phần dò tìm hàm MATCH cần phải có hàm VALUE để chuyển kiểu TEXT về NUMBER. Và công thức hàm VLOOKUP kết hợp hàm MATCH sẽ như sau:

Công thức: =VLOOKUP(LEFT(A6, 2), $A$18:$D$22, MATCH(VALUE(RIGHT(A6, 1)), $A$17:$D$17, 0), 0)

Giải thích:

LEFT(A6, 2): Lấy 2 ký tự đầu của Mã HH

$A$18:$D$22: Vùng bảng Đơn Giá bao gồm cả cột đơn giá loại 1 và loại 2

VALUE(RIGHT(A6, 1)): Lấy ký tự cuối cùng đồng thời chuyển đổi kiểu TEXT sang kiểu NUMBER

$A$17:$D$17: Vùng đơn giá loại 1 loại 2, cái này mình có kéo dài về đằng trước để khi trả về kết quả 1 hoặc 2 thì sẽ không cần phải cộng thêm 2 cột đằng trước nữa

Như vậy là mình đã hướng dẫn các bạn 4 cách sử dụng hàm để xử lý bài toán đơn giá loại 1 loại 2 rồi, bây giờ mình sẽ làm công thức từ câu 2 tới câu 4 để các bạn tham khảo thêm.

Ví dụ nâng cao về cách tính đơn giá loại 1 loại 2 trong Excel

1. PHÍ CHUYÊN CHỞ: = 1% * ĐƠN GIÁ đối với mặt hàng loại 1 và 5% * ĐƠN GIÁ đối với mặt hàng loại 2.
2. THÀNH TIỀN = SỐ LƯỢNG * (ĐƠN GIÁ + PHÍ CHUYÊN CHỞ).
3. TỔNG CỘNG = THÀNH TIỀN – TIỀN GIẢM biết rằng nếu THÀNH TIỀN >=1000 sẽ giảm 1% * THÀNH TIỀN và định dạng với không số lẻ.

Câu số 2

Câu phí chuyên chở các bạn sẽ làm công thức như sau: =IF(RIGHT(A6, 1)= “1”, 1%, 5%) * C6

Giải thích:

RIGHT(A6, 1)= “1”: So sánh điều kiện Mã HH là loại 1 hay loại 2

1%: Phần trăm khi MẶT HÀNG là loại 1

5%: Phần trăm khi MẶT HÀNG là loại 2

*C6: Nhân với đơn giá để ra phí chuyên chở

Câu số 3

Câu này quá đơn giản rồi phải không nào, khi các bạn tính toán xong phần phí chuyên chở thì công thức câu số 3 này sẽ như sau: =D6 * (C6 + E6)

Câu số 4

Câu này cần tính TỔNG CỘNG, nếu không để ý sẽ bị đọc nhầm câu hỏi và làm sai. Bạn cần tính phần TIỀN GIẢM trước bằng công thức hàm IF sau đó sẽ ghép vào công thức TỔNG CỘNG = THÀNH TIỀN – TIỀN GIẢM. Và công thức của câu số 4 này sẽ như sau: =F6 – IF(F6 >= 1000, 1%, 0) * F6

Và bạn nhớ định dạng không có số lẻ nào ở cột TỔNG CỘNG như sau:

Bạn kích chuột trái vào mũi tên chéo ở trên thanh công cụ Number Format và chọn Tab Number như hình trên rồi điền số 0 tại “Decimal Places”.

Khá là đơn giản phải không nào, đáp án của 3 câu 2 3 4 các bạn tham khảo ở ảnh dưới đây:

Như vậy, mình đã hướng dẫn các bạn cách tính đơn giá loại 1 loại 2 trong Excel bằng nhiều cách các hàm kết hợp với nhau kèm ví dụ rất chi tiết. Hi vọng rằng với bài viết chia sẻ này bạn đã có thể tìm ra được một cách mà bạn thấy phù hợp nhất và nhanh nhất. Các bạn đừng quên để lại một bình luận dưới bài viết này hoặc chia sẻ bài viết này với bạn bè!