Lập Công Thức Tính Đơn Giá Trong Excel

Có thể bạn quan tâm: Lập Bảng Tiến Độ Công Việc Bằng Excel: Hướng Dẫn Chi Tiết Cho Người Mới Bắt Đầu

Giới thiệu

Trong công việc quản lý bán hàng, kế toán, hay bất kỳ lĩnh vực nào liên quan đến giá trị và chi phí, việc tính toán đơn giá (price per unit) luôn là một thao tác thiết yếu. Đơn giá giúp doanh nghiệp đánh giá lợi nhuận, so sánh sản phẩm, lập báo cáo tài chính, và quyết định chính sách giá cả. Excel, với sức mạnh tính toán và tính linh hoạt cao, trở thành công cụ phổ biến nhất để thực hiện những tính toán này. Bài viết dưới đây sẽ hướng dẫn chi tiết cách lập công thức tính đơn giá trong Excel Sequences, từ những công thức cơ bản đến những kỹ thuật nâng cao, cùng với các ví dụ thực tiễn và mẹo tối ưu hiệu suất.

Có thể bạn quan tâm: Lập Bảng Thống Kê Doanh Thu Trong Excel – Hướng Dẫn Chi Tiết

1. Khái niệm cơ bản: Đơn giá và công thức tính

1.1 Đơn giá là gì?

Đơn giá (Unit Price) là giá bán hoặc giá trị tiền tệ đối với một đơn vị của một mặt hàng. Nếu bạn bán विर् 100 chiếc bánh, đơn giá của mỗi chiếc bánh sẽ là tổng doanh thu chia cho số lượng bán ra.

1.2 Công thức tính đơn giá cơ bản

Công thức đơn giản nhất để tính đơn giá là:

Đơn giá = Tổng doanh thu / Số lượng bán

Trong Excel, công thức này sẽ được biểu diễn như sau:

=SUM(A2:A10)/SUM(B2:B10)

Giả sử cột A chứa tổng doanh thu và cột B chứa số lượng bán, ta có thể tính đơn giá cho từng dòng (mặt hàng) hoặc cho toàn bộ danh sách.

1.3 Kiến thức quan trọng khi làm việc với công thức

• Tham chiếu ایسی: Khác nhau giữa tham chiếu tuyệt đối ($A$1) và tham chiếu tương đối (A1) ảnh hưởng tới việc sao chép công thức assess.
• Sử dụng tên vùng (named range): Giúp công thức dễ đọc và dễ bảo trì.
• Kiểm tra lỗi: Sử dụng IFERROR để tránh lỗi khi chia cho 0 hoặc dữ liệu không hợp lệ.

Có thể bạn quan tâm: Lấy Lại File Excel Khi Bị Mất Điện: Hướng Dẫn Chi Tiết Từng Bước

2. Thiết kế bảng dữ liệu cơ bản

Trước khi áp dụng công thức, hãy xác định cấu trúc bảng dữ liệu:

• A: Mã sản phẩm
• B: Tên sản phẩm
• C: Số lượng bán (đơn vị)
• D: Tổng doanh thu (đơn vị tiền tệ)
• E: Đơn giá (được tính tự động)

2.1 Nhập dữ liệu mẫu

3. Tính đơn giá cho từng dòng

3.1 Công thức đơn giản

Trong ô E2, nhập công thức:

=D2/C2

Sao chép công thức xuống các dòng tiếp theo (E3, E4, E5…). Kết quả:

3.2 Kiểm tra lỗi chia cho 0

Nếu số lượng bán có thể là 0, ta dùng IFERROR:

=IFERROR(D2/C2, "Không hợp lệ")

Bây giờ, nếu C2 = 0, E2 sẽ hiển thị “Không hợp lệ” thay vì lỗi.

3.3 Định dạng số tiền

Chọn cột E, click chuột phải → Format Cells → Number → Currency or Accounting. Nhập 0 decimal places để hiển thị số tiền đúng định dạng.

4. Tính đơn giá tổng hợp cho toàn bộ danh sách

Đôi khi bạn muốn biết đơn giá trung bình cho toàn bộ danh sách sản phẩm. Công thức:

=SUM(D2:D5)/SUM(C2:C5)

Kết quả: 50,000 đồng.

Bạn có thể đặt công thức này ở ô E7, sau bảng dữ liệu.

5. Tính đơn giá trong trường hợp có chi phí khác

Giả sử doanh thu đã bao gồm chi phí vận chuyển, chi phí đóng gói hoặc chi phí nguyên vật liệu. Để tính đơn giá thực tế, ta cần trừ các chi phí này.

5.1 Thêm cột chi phí

5.2 Công thức tính đơn giá sau chi phí

= (D2 - F2 - G2 - H2) / C2

Sao chép công thức xuống các dòng. Kết quả:

6. Tính đơn giá với chiết khấu

Nếu khách hàng được giảm giá theo số lượng, ta tính đơn giá sau chiết khấu.

6.1 Thêm cột chiết khấu

6.2 Công thức tính đơn giá sau chiết khấu

= (D2 (1 - I2)) / C2

Sao chép công thức xuống. Kết quả:

7. Sử dụng hàm VLOOKUP / INDEX-MATCH để tự động tra cứu giá bán

Khi làm việc với danh mục sản phẩm lớn, ta thường muốn tự động lấy giá bán từ bảng dữ liệu khác (ví dụ bảng giá tùy theo khách hàng).

7.1 Bảng giá tùy theo khách hàng

7.2 Tạo bảng dữ liệu bán hàng

7.3 Công thức tra cứu giá bán

Có thể bạn quan tâm: Hướng Dẫn Chi Tiết: Làm Bảng Xếp Hạng Bóng Đá Bằng Excel – Từ Cơ Bản Tới Nâng Cao

Sử dụng INDEX + MATCH (được khuyến khích vì linh hoạt hơn):

=INDEX($M$2:$M$9, MATCH(1, ($K$2:$K$9=C2)($L$2:$L$9=D2), 0))

Nhấn Ctrl + Shift + Enter để nhập công thức mảng (đối với Excel cũ). Nếu dùng Excel 365, chỉ cần nhập công thức bình thường.

Sau khi nhập công thức, cột E sẽ tự động lấy giá bán phù hợp. Bây giờ ta có thể tính đơn giá (giá bán số lượng) hoặc áp dụng chiết khấu, chi phí, v.v.

8. Tính đơn giá với công thức nâng cao: Dynamic Array, XLOOKUP

Với Excel 365, XLOOKUP và công thức động (dynamic arrays) giúp viết công thức ngắn gọn và hiệu quả.

8.1 Sử dụng XLOOKUP

=XLOOKUP(C2, $K$2:$K$9, $M$2:$M$9, "Không có giá")

Công thức này trả về giá bán tương ứng nếu tìm thấy, hoặc “Không có giá” nếu không tìm thấy.

8.2 Tính đơn giá sau chiết khấu

= XLOOKUP(C2, $K$2:$K$9, $M$2:$M$9, "Không có giá") (1 - L2)

Trong đó, L2 là cột chiết khấu (onaise).

9. Kiểm tra và xử lý dữ liệu sai lệch

9.1 Sử dụng Conditional Formatting

• Highlight các đơn giá thấp hơn mức trung bình: Conditional Formatting → New Rule → Use a formula → =E2<AVERAGE($E$2:$E$5) → Format (red fill).
• Highlight đơn giá vượt quá một ngưỡng: =E2>50000 → Format (yellow fill).

9.2 Sử dụng Data Validation

• Đặt Data Validation cho cột số lượng bán: chỉ cho phép số nguyên dương.
• Thêm Error Alert tùy chỉnh để cảnh báo khi nhập sai dữ liệu.

9.3 Kiểm tra lỗi công thức

• Sử dụng ISERROR hoặc IFERROR trong công thức.
• Kiểm tra #DIV/0!, #N/A, #REF!.

10. Tối ưu hoá tính năng công thức

10.1 Sử dụng Named Ranges

• Đặt tên cho cột số lượng bán: Số_lượng.
• Đặt tên cho cột doanh thu: Doanh_thu.
• Công thức trở nên dễ đọc: =Doanh_thu/Số_lượng.

10.2 Tránh tính toán lặp

Thay vì viết công thức trong mỗi ô, tạo một công thức chung trong cột và sử dụng Fill Down. Khi dữ liệu thay đổi, chỉ cần cập nhật một công thức.

10.3 Sử dụng Table (Excel Table)

• Chuyển dữ liệu thành Table (Ctrl + T). Khi thêm dòng mới, công thức tự động mở rộng.
• Tên cột trong Table có thể được tham chiếu bằng cú pháp Tên cột.

11. Tích hợp công thức tính đơn giá vào báo cáo doanh thu

11.1 Kết hợp với PivotTable

• Tạo PivotTable từ bảng dữ liệu.
• Kéo Tên sản phẩm vào Rows, Số lượng bán và Tổng doanh thu vào Values.
• Thêm một trường tính cho Đơn giá bằng cách sử dụng tính toán trong PivotTable: Tổng doanh thu ÷ Số lượng bán.

11.2 Kết hợp với Power Query

• Nhập dữ liệu từ nguồn (Excel, CSV, database).
• Dùng Power Query để tạo cột mới Đơn giá bằng công thức = Tổng doanh thu / Số lượng bán.
• Tải dữ liệu về Excel và sử dụng trong báo cáo.

12. Tạo biểu đồ hiển thị đơn giá

• Chọn cột Tên sản phẩm và Đơn giá.
• Vào Insert → Bar Chart → Clustered Bar.
• Định dạng trục X, trục Y và tiêu đề biểu đồ.
• Thêm Data Labels để hiển thị giá trị đơn vị.

Biểu đồ giúp người đọc nhanh chóng nhận diện sản phẩm nào có giá cao, thấp, và so sánh trực quan.

13. Tình huống thực tế: Tính đơn giá trong kế toán bán hàng

13.1 Dữ liệu mẫu

13.2 Công thức tính đơn giá

= (D2 (1 - E2)) / (C2 + F2)

• D2 (1 - E2) tính giá bán sau chiết khấu.
• C2 + F2 là tổng số lượng (số lượng bán + chi phí vận chuyển, Cam kết tính khác).

13.3 Kết quả

14. Kết luận và lời khuyên

• Sử dụng công thức tối giản: Luôn ưu tiên tính toán trong một công thức để tránh lỗi.
• Định dạng dữ liệu: Dùng Currency, Accounting, Percent cho phù hợp.
• Kiểm tra lỗi: Sử dụng IFERROR, ISERROR, và Conditional Formatting để phát hiện dữ liệu sai.
• Sutig table và named range: Giúp công thức dễ hiểu và bảo trì.
• Tận dụng tính năng mới: XLOOKUP, FILTER, UNIQUE, và dynamic arrays mở rộng khả năng tính toán.
• Kết hợp với PivotTable/Power Query: Tạo báo cáo tự động, cập nhật nhanh chóng khi dữ liệu thay đổi.

Bạn có thể áp dụng các công thức và quy trình ở trên vào bất kỳ bảng tính Excel nào, từ đơn giản nhất đến phức tạp nhất, giúp tối ưu hóa quy trình tính đơn giá và nâng cao hiệu quả công việc. Happy Excel-ing!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.