Tính Đơn Giá Dựa Vào Mã Hàng Trong Excel

Có thể bạn quan tâm: Tìm Giá Trị Khác Nhau Giữa 2 Cột Trong Excel

Giới thiệu

Trong môi trường kinh doanh hiện đại, quản lý hàng tồn kho và tính giá trị bán hàng là những nhiệm vụ quan trọng mà bất kỳ doanh nghiệp nào cũng phải thực hiện một cách chính xác và nhanh chóng. Khi quy mô dữ liệu ngày càng tăng, việc nhập liệu thủ công và tính toán bằng tay sẽ dẫn đến nhiều lỗi và làm giảm hiệu suất làm việc. Đó là lý do tại sao Excel – công cụ bảng tính mạnh mẽ nhưng dễ tiếp cận – trở thành lựa chọn hàng đầu để tự động hóa quá trình tính đơn giá dựa vào mã hàng.

Bài viết này sẽ hướng dẫn bạn cách thiết lập một hệ thống tính đơn giá tự động trong Excel, từ cách nhập dữ liệu, tổ chức bảng tính, sử dụng hàm tìm kiếm (VLOOKUP, XLOOKUP, INDEX & MATCH), tới cách áp dụng công thức, dữ liệu liên kết, và thậm chí là viết macro VBA để tự động hoá quá trình. Bài viết cũng sẽ chia sẻ các mẹo thực tiễn, giải quyết các vấn đề thường gặp và đưa ra những gợi ý nâng cao để bạn có thể tùy biến theo nhu cầu cụ thể của doanh nghiệp mình.

Có thể bạn quan tâm: Tìm Số Lớn Nhất Có Điều Kiện Trong Excel

Tại sao cần tính đơn giá dựa vào mã hàng?

1. Tiết kiệm thời gian: Khi bạn có bảng dữ liệu chính xác về mã hàng và giá bán, việc tính đơn giá cho hàng hóa mới chỉ cần một công thức đơn giản, tránh việc phải tính toán thủ công từng dòng.
2. Đảm bảo tính chính xác: Công thức tự động giúp giảm thiểu sai sót do nhập liệu hoặc tính toán tay.
3. Phân tích nhanh: Khi dữ liệu được liên kết, bạn có thể dễ dàng tạo báo cáo, biểu đồ, hay tính toán lợi nhuận theo từng mặt hàng.
4. Dễ dàng cập nhật: Khi giá bán thay đổi, chỉ cần thay đổi giá trong bảng dữ liệu tham chiếu và toàn bộ hệ thống sẽ cập nhật tự động.
5. Tổ chức dữ liệu: Thông qua việc phân loại mã hàng vào các bảng tham chiếu, bạn có thể quản lý khối lượng dữ liệu lớn một cách có cấu trúc.

Có thể bạn quan tâm: Tìm Giá Trị Xuất Hiện Nhiều Nhất Trong Excel

Các thành phần chính của hệ thống

Trước khi bắt đầu, hãy xác định các thành phần cần có trong hệ thống Excel của bạn:

1. Bảng dữ liệu mặt hàng (Product Table): Chứa mã hàng, tên mặt hàng, mô tả, đơn vị tính, giá bán, giá nhập, chi phí phụ, v.v.
2. Bảng nhập dữ liệu bán hàng (Sales Input Sheet khu bidez): Nơi bạn nhập mã hàng, số lượng, ngày bán, v.v.
3. Bảng tính đơn giá (Pricing Sheet): Tự động lấy giá từ bảng mặt hàng dựa vào mã hàng nhập vào.
4. Bảng tổng hợp (Summary): Tính tổng doanh thu, lợi nhuận, hoặc các chỉ số kinh doanh khác.
5. Tài liệu hướng dẫn (Documentation): Ghi chú cách sử dụng, quy tắc đặt tên, quy trình kiểm tra dữ liệu.

Trong thực tế, bạn có thể gom các bảng này vào một file Excel duy nhất, chia thành nhiều sheet hoặc sử dụng bảng dữ liệu (Table) trong Excel để quản lý dữ liệu dễ dàng hơn.

Chuẩn bị dữ liệu

1. Bảng dữ liệu mặt hàng

Bảng này nên được đặt trong một sheet riêng, ví dụ tên sheet là MặtHàng. Dưới đây là cấu trúc mẫu:

• Mã Hàng: Dùng để xác định duy nhất mỗi mặt hàng.
• Giá Bán: Đây là giá bạn định bán cho khách hàng; nếu muốn tính theo giá gốc + lợi nhuận, bạn có thể tạo cột “Lợi nhuận” và tính giá bán dựa trên công thức.

Để làm cho bảng dữ liệu dễ quản lý, bạn nên chuyển vùng dữ liệu thành một Table (chọn vùng dữ liệu rồi nhấn Ctrl + T). Khi đó, Excel sẽ tự động cộng tên cho bảng, ví dụ Table_MatHang. Việc sử dụng Table giúp công thức luôn cập nhật khi thêm mới dòng dữ liệu.

2. Bảng nhập dữ liệu bán hàng

Tạo một sheet mới, ví dụ tên BánHàng. Cấu trúc mẫu:

• Giá Đơn Vị: Thông thường sẽ được tự động lấy từ bảng mặt hàng. Bạn có thể dùng dữ liệu validation (danh sách) cho cột Mã Hàng để tránh nhập sai.
• Tổng Tiền: =C2D2 – tổng doanh thu cho dòng bán hàng.

Tính đơn giá tự động

1. Sử dụng hàm VLOOKUP

Hàm VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) là một trong những hàm phổ biến nhất để tìm kiếm giá trị dựa vào một ô tham chiếu.

Cách thực hiện:

1. Chọn ô D2 (Giá Đơn Vị) trong sheet BánHàng.
2. Nhập công thức:
   excel =VLOOKUP(B2, Table_MatHang, 6, FALSE)
3. B2: Mã hàng nhập vào.
4. Table_MatHang: Bảng dữ liệu mặt hàng (có chứa cột Mã Hàng và Giá Bán).
5. 6: Số thứ tự cột trong bảng chứa giá bán (cột F). Nếu bạn chuyển sang Table, Excel sẽ tự động dùng tên cột như @Giá Bán.

6. FALSE: Tìm kiếm chính xác.

7. Kéo công thức xuống các dòng tiếp theo.

Ưu điểm: Đơn giản, dễ hiểu.
Nhược điểm: Nếu bảng dữ liệu có nhiều cột, bạn cần nhớ đúng số thứ tự cột. Nếu bảng được sắp xếp lại, số thứ tự sẽ thay đổi.

2. Sử dụng hàm XLOOKUP

Excel 365 và Excel 2026 hỗ trợ hàm XLOOKUP, zwemmen mạnh mẽ hơn VLOOKUP.

Công thức:

=XLOOKUP(B2, Table_MatHangMã Hàng, Table_MatHangGiá Bán, "Không tìm thấy")

• B2: Mã hàng.
• Table_MatHangMã Hàng: Trường dữ liệu mã hàng.
• Table_MatHangGiá Bán: Trường dữ liệu giá bán.
• "Không tìm thấy": Giá trị trả về khi không tìm thấy.

Ưu điểm: Dễ đọc, không cần số thứ tự cột, có thể tìm kiếm từ trái sang phải hoặc ngược lại.

3. Sử dụng INDEX & MATCH

Khi bạn cần linh hoạt hơn, ví dụ muốn tìm kiếm theo nhiều điều kiện, INDEX + MATCH là lựa chọn tốt.

Công thức:

=INDEX(Table_MatHangGiá Bán, MATCH(B2, Table_MatHangMã Hàng, 0))

恒一: MATCH trả về vị trí của mã hàng trong cột Mã Hàng, INDEX lấy giá bán tại vị trí đó.

Ưu điểm: Tính linh hoạt cao, có thể dùng để tìm kiếm theo nhiều điều kiện.

Tạoських dữ liệu validation

Để tránh nhập sai mã hàng, bạn có thể tạo Data Validation:

1. Chọn cột B trong sheet BánHàng.
2. Vào Data > Data Validation.
3. Chọn List và nhập nguồn:
   excel =Table_MatHangMã Hàng
4. Nhấn OK.

Khi người dùng nhập mã hàng, họ sẽ thấy một danh sách dropdown chứa toàn bộ mã hàng từ bảng dữ liệu.

Tính tổng doanh thu và lợi nhuận

Sau khi tính được Giá Đơn Vị và Tổng Tiền, bạn có thể tính lợi nhuận mà doanh nghiệp kiếm được.

Giả sử Giá Nhập nằm ở cột E trong bảng Table_MatHang. Bạn có thể tính lợi nhuận trên mỗi dòng bán hàng:

1. Thêm cột Lợi Nhuận (G2):
   excel =C2 (D2 - VLOOKUP(B2, Table_MatHang, 5, FALSE))
2. C2: số lượng bán.
3. D2: giá bán (đã lấy từ bảng).

4. VLOOKUP(B2, Table_MatHang, 5, FALSE): angu giá nhập, tính lãi trên mỗi đơn vị.

5. Tính tổng lợi nhuận:
   excel =SUM Floats(G2:G100)

Áp dụng công thức tự động khi nhập dữ liệu

Bạn có thể áp dụng công thức XLOOKUP hoặc VLOOKUP vào toàn bộ cột Giá Đơn Vị một cách tự động khi người dùng nhập mã hàng. Để làm điều này:

1. Đặt công thức vào ô đầu tiên của cột Giá Đơn Vị (D2).
2. Kéo công thức xuống hết cột, hoặc sử dụng Table để tự động mở rộng công thức khi thêm dòng mới.

Định dạng dữ liệu và báo cáo

Có thể bạn quan tâm: Tính Tổng Các Giá Trị Giống Nhau Trong Excel

1. Định dạng ngày tháng

Cuối cùng, bạn có thể áp dụng Conditional Formatting để làm nổi bật ngày hết hạn tồn kho, hoặc ngày bán lẻ quan trọng.

2. Pivot Table

Pivot Table giúp tổng hợp dữ liệu bán hàng theo mã hàng, ngày, hoặc khu vực. Bạn có thể làm như sau:

1. Chọn vùng dữ liệu trong BánHàng.
2. Vào Insert > PivotTable.
3. Đặt Mã Hàng vào Rows, Ngày vào Columns, Tổng Tiền vào Values.

Pivot Table sẽ cho bạn tổng doanh thu theo ngày và mã hàng một cách nhanh chóng.

3. Bảng điều khiển (Dashboard)

Sử dụng Slicer để lọc dữ liệu theo ngày, mã hàng, hoặc khách hàng. Kết hợp với biểu đồ để tạo dashboard trực quan.

Tự động hoá bằng VBA

Nếu bạn muốn tự động cập nhật bảng dữ liệu khi nhập mã hàng mới, hoặc tự động tính toán, bạn có thể viết macro VBA. Dưới đây là một ví dụ đơn giản:

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Set rng = Intersect(Target, Me.Range("B2:B100")) If Not rng Is Nothing Then Application.EnableEvents = False Dim cell As Range For Each cell In rng If cell.Value <> "" Then ' Tìm giá bán Dim giáBán As Variant giáBán = Application.WorksheetFunction.VLookup(cell.Value, Worksheets("MặtHàng").Range("A:F"), 6, False) If Not IsError(giáBán) Then cell.Offset(0, 2).Value = giáBán Else cell.Offset(0, 2).Value = "Không tìm thấy" End If End If Next cell Application.EnableEvents = True End If
End Sub

• Chức năng: Khiीर người dùng nhập mã hàng vào cột B, macro tự động tìm giá bán trong bảng MặtHàng và điền vào cột D.

Tự động xuất báo cáo

Bạn có thể viết macro để xuất bảng dữ liệu vào PDF, hoặc gửi email tự động. Ví dụ:

Sub ExportReport() Dim ws As Worksheet Set ws = Worksheets("BánHàng") ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Reports\Report_" & Format(Date, "yyyymmdd") & ".pdf"
End Sub

Kiểm tra dữ liệu và xử lý lỗi

1. Kiểm tra dữ liệu trùng lặp

• Sử dụng Conditional Formatting để làm nổi bật mã hàng trùng lặp trong bảng MặtHàng.

2. Kiểm tra giá bán hợp lý

• Nếu giá bán thấp hơn giá nhập, bạn có thể cảnh báo:
  excel =IF(D2 < VLOOKUP(B2, Table_MatHang, 5, FALSE), "Lỗi: Giá bán thấp hơn giá nhập", "")

3. Kiểm tra dữ liệu thiếu

• Đặt Data Validation để không cho phép mã hàng rỗng.

Tối ưu hóa hiệu suất

Khi dữ liệu lớn (thousands of rows), công thức VLOOKUP có thể chậm. Bạn có thể:

• Sử dụng XLOOKUP (nhiều nhanh hơn).
• Đặt bảng dữ liệu vào Table để tự động mở rộng.
• Sử dụng INDEX & MATCH thay vì VLOOKUP nếu bảng dữ liệu có nhiều cột.

Hướng dẫn nâng cao

1. Tính giá bán dựa vào mức lợi nhuận

Nếu bạn muốn tính giá bán dựa vào lợi nhuận mục tiêu.vertical:

• Thêm cột Lợi nhuận % trong bảng MặtHàng.
• Tính giá bán:
  excel =@Giá Nhập (1 + @Lợi nhuận %)

2. Tính giá bán theo số lượng

Đôi khi bạn muốn áp dụng giá bán khác nhau tùy theo số lượng bán. Ví dụ:
– Đối với từ 1-10 đơn vị: Giá bán 2500
– Từ 11-20 đơn vị: Giá bán 2400

Bạn có thể tạo bảng Giá_Bán_Tùy_Thừa và dùng XLOOKUP kèm CHOOSE để xác định giá làm.

3. Sử dụng Power Query

Nếu dữ liệu nguồn là từ cơ sở dữ liệu hoặc file CSV, bạn có thể dùng Power Queryxton để nhập dữ liệu, làm sạch, và tải vào Excel. Power Query hỗ trợ tự động refresh dữ liệu khi nguồn thay đổi.

Thực hành: Tạo một file mẫu

1. Bảng Mặt Hàng (MặtHàng):
2. Chèn 20 dòng dữ liệu mẫu.
3. Đặt tên Table: Table_MatHang.
4. Sheet Bán Hàng (BánHàng):
5. Dùng Data Validation cho cột Mã Hàng.
6. Dùng XLOOKUP để tự động lấy giá bán.
7. Tính tổng tiền và lợi nhuận.
8. Pivot Table:
9. Tạo Pivot Table tổng doanh thu theo mã hàng.
10. Dashboard:
11. Thêm một biểu đồ bar hiển thị doanh thu theo ngày.
12. Macro:
13. Thêm macro tự động cập nhật giá bán khi nhập mã hàng.

Sau khi hoàn thiện, bạn có thể lưu file dưới dạng Excel Macro-Enabled Workbook (.xlsm) để cho phép macro hoạt động.

Kết luận

Việc tính đơn giá dựa vào mã hàng trong Excel không chỉ giúp doanh (
…

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ẻ.