Cách Tính Hoa Hồng Đại Lý Vé Máy Bay Theo Hãng Bằng Google Sheets

Trả lời nhanh: Lập một tab tra tỷ lệ hoa hồng cho từng hãng (Hãng A, B, C... do bạn tự nhập theo thỏa thuận của mình). Ở mỗi dòng vé, dùng VLOOKUP lấy tỷ lệ theo tên hãng rồi nhân với giá vé. Cuối tháng cộng bằng SUMIFS theo hãng và ngày xuất vé.

Thử hình dung một phòng vé nhỏ có ba người cùng bán. Cuối tháng, chủ phòng vé ngồi lật lại tin nhắn và file riêng của từng nhân viên để biết hãng nào nợ mình bao nhiêu hoa hồng. Mỗi người ghi một kiểu: người thì gõ "A 3%" vào ô ghi chú, người thì tính nhẩm rồi chỉ ghi con số cuối. Có vé bị đổi giữa tháng, có vé hủy mà hoa hồng vẫn nằm trong bảng. Tính xong ba lần ra ba con số khác nhau. Bài này dựng một file Google Sheets đủ gọn để tránh chuyện đó, mà chỉ cần vài công thức quen thuộc.

Toàn bộ số liệu bên dưới là giả định để minh họa. Tên "Hãng A", "Hãng B", "Hãng C" và các tỷ lệ 3%, 5%, 2% không phải mức hoa hồng của hãng bay nào. Mức thật phụ thuộc vào thỏa thuận giữa bạn và từng hãng (hoặc nhà cung cấp), nên bạn tự nhập vào bảng của mình.

Tách tỷ lệ hoa hồng ra khỏi bảng bán vé

Cách làm phổ biến nhất của đại lý nhỏ là gõ thẳng tỷ lệ vào công thức, kiểu =D2*0.03. Chạy được vài tuần thì phát sinh rắc rối. Hãng đổi chính sách, bạn phải sửa từng dòng. Nhân viên mới copy nhầm công thức của hãng khác. Không ai nhớ dòng nào đang dùng 3%, dòng nào đã chuyển sang 3,5%.

Cách gọn hơn là mỗi hãng một dòng trong một tab riêng, mỗi vé chỉ ghi tên hãng, còn tỷ lệ do công thức tự lấy về. Khi cần đổi tỷ lệ, bạn sửa đúng một ô. Lúc đối chiếu với hãng cũng chỉ cần mở một chỗ để xem bạn đang áp mức nào.

Dựng bảng tra tỷ lệ theo hãng

Tạo một tab đặt tên TyLe với hai cột. Cột A là tên hãng, cột B là tỷ lệ hoa hồng. Định dạng cột B là phần trăm để gõ 3% là ra đúng 0,03.

Ô	A (Hãng)	B (Tỷ lệ hoa hồng)
Dòng 1	Hãng	Tỷ lệ
Dòng 2	Hãng A	3%
Dòng 3	Hãng B	5%
Dòng 4	Hãng C	2%

Công thức bên dưới tham chiếu đến dòng 20, nên bạn còn chỗ thêm hãng mới mà không phải sửa công thức. Tên hãng ở đây phải là chuỗi duy nhất cho mỗi hãng. Viết "Hãng A" ở chỗ này thì ở bảng bán vé cũng phải đúng "Hãng A", không thừa dấu cách, không viết tắt.

Bảng bán vé và công thức tra tỷ lệ

Tab thứ hai đặt tên Ban, mỗi vé một dòng. Cấu trúc cột tối thiểu như sau:

• Cột A: ngày xuất vé (định dạng ngày thật, không gõ chữ)
• Cột B: mã PNR
• Cột C: hãng (chọn từ danh sách thả xuống)
• Cột D: giá vé dùng làm cơ sở tính hoa hồng
• Cột E: tỷ lệ hoa hồng (công thức tự tra)
• Cột F: hoa hồng (công thức)
• Cột G: phí dịch vụ thu thêm của khách
• Cột H: lãi gộp của vé
• Cột I: trạng thái (Đã xuất, Đã hủy)

Một chi tiết cần chốt với từng hãng: họ tính hoa hồng trên giá vé cơ bản hay trên một phần khác của giá vé. Cột D phải chứa đúng khoản mà thỏa thuận của bạn nói tới, nếu không công thức đúng mấy cũng ra số sai. Phần này chỉ bạn và hãng mới chốt được.

Công thức ở E2: lấy tỷ lệ theo hãng

Với VLOOKUP, ở ô E2 dán:

=VLOOKUP(C2,TyLe!$A$2:$B$20,2,FALSE)

C2 là tên hãng của vé, TyLe!$A$2:$B$20 là bảng tra (có dấu $ để kéo xuống không bị trượt), số 2 nghĩa là lấy cột thứ hai, FALSE bắt buộc khớp chính xác tên hãng. Nếu bản Google Sheets của bạn có XLOOKUP, dùng cách này cũng ra kết quả tương tự và dễ đọc hơn:

=XLOOKUP(C2,TyLe!$A$2:$A$20,TyLe!$B$2:$B$20)

Nên cố ý không bọc IFERROR quanh công thức. Nếu ai đó gõ nhầm tên hãng, ô E hiện #N/A ngay và bạn thấy lỗi. Bọc IFERROR trả về 0 thì lỗi biến mất, và hoa hồng của vé đó âm thầm thành 0 cho đến khi cuối tháng bạn thấy tổng thiếu mà không biết thiếu ở đâu. Để chặn gõ nhầm từ đầu, chọn cả cột C, vào Dữ liệu, Xác thực dữ liệu, chọn "Danh sách từ một phạm vi" và trỏ tới TyLe!A2:A20.

Công thức ở F2, H2: hoa hồng và lãi gộp

Hoa hồng bằng giá vé nhân tỷ lệ, nhưng vé đã hủy thì không có hoa hồng:

=IF(I2="Đã hủy",0,D2*E2)

Lãi gộp ở H2 trong ví dụ này gồm hoa hồng cộng với phí dịch vụ bạn thu thêm của khách:

=F2+G2

Nếu bạn còn khoản chi trực tiếp khác cho từng vé, thêm một cột và trừ đi ở công thức này. Ví dụ ở đây giữ gọn để dễ theo dõi.

Ví dụ năm vé với số giả định

Giả sử trong tháng 10/2026 bạn xuất năm vé như bảng dưới. Tất cả đều là dữ liệu minh họa.

Hãng	Giá vé (D)	Tỷ lệ (E)	Hoa hồng (F)	Phí dịch vụ (G)	Lãi gộp (H)
Hãng A	2.000.000	3%	60.000	50.000	110.000
Hãng B	3.000.000	5%	150.000	50.000	200.000
Hãng C	1.500.000	2%	30.000	50.000	80.000
Hãng A	4.000.000	3%	120.000	100.000	220.000
Hãng B	2.500.000	5%	125.000	50.000	175.000

Cộng lại: tổng hoa hồng là 485.000, tổng phí dịch vụ là 300.000, tổng lãi gộp là 785.000. Hoa hồng chiếm khoảng 62% lãi gộp, nghĩa là với kiểu giao dịch này, nếu tính sai hoa hồng thì bạn tính sai phần lớn lợi nhuận của tháng. Với các vé giá thấp, đừng chủ quan: vé Hãng C ở trên chỉ cho 30.000 hoa hồng, nhưng nhân với vài chục vé mỗi tháng thì con số không còn nhỏ.

Cộng hoa hồng theo tháng bằng SUMIFS

Tạo tab TongHop. Ô B1 chứa ngày đầu tháng cần xem, ví dụ 01/10/2026. Cột A từ dòng 3 trở xuống liệt kê từng hãng (A3 là Hãng A, A4 là Hãng B, A5 là Hãng C). Ở B3 dán công thức:

=SUMIFS(Ban!$F$2:$F$500,Ban!$C$2:$C$500,$A3,Ban!$A$2:$A$500,">="&$B$1,Ban!$A$2:$A$500,"<"&EDATE($B$1,1))

Công thức cộng cột F (hoa hồng) của những dòng có hãng đúng với A3 và ngày xuất vé từ đầu tháng ở B1 đến trước ngày đầu của tháng kế tiếp. EDATE cộng thêm một tháng, nên đổi B1 sang 01/11/2026 là cả bảng nhảy sang tháng 11. Kéo công thức xuống cho các hãng còn lại.

Với năm vé ở trên, kết quả là Hãng A 180.000 (60.000 + 120.000), Hãng B 275.000 (150.000 + 125.000), Hãng C 30.000. Cộng ba hãng được 485.000, khớp với tổng hoa hồng ở bảng trước. Đây là bước kiểm tra nhanh nên làm: tổng ở TongHop phải bằng tổng cột F của tab Ban trong cùng tháng, lệch là có dòng nào đó sai ngày hoặc sai tên hãng.

Để so với lãi gộp, thêm một cột C ở TongHop dùng cùng công thức nhưng đổi vùng cộng từ cột F sang cột H, rồi chia hoa hồng cho lãi gộp. Nếu bạn thấy tỷ lệ này tụt dần qua các tháng, có thể bạn đang bán nhiều vé của hãng hoa hồng thấp, hoặc phí dịch vụ đang bị giảm để giữ khách. File sẽ chỉ cho bạn thấy con số, còn quyết định tăng phí hay đẩy hãng nào là việc của bạn. Nếu các hãng có mức thưởng theo sản lượng, cách chia bậc có thể tham khảo ở mẫu tính hoa hồng bán hàng theo bậc thang.

Lỗi hay gặp: đổi vé, hủy vé và đổi tỷ lệ

Vé bị đổi hoặc hủy

Đây là chỗ sai nhiều nhất. Khi khách hủy, đừng xóa dòng. Chuyển cột I thành "Đã hủy" và công thức ở F2 tự đưa hoa hồng về 0. Giữ dòng lại để sau này còn dấu vết khi đối chiếu với hãng. Khi khách đổi sang chuyến khác, coi như vé cũ hủy và thêm một dòng mới cho vé mới với giá và hãng mới, cùng mã PNR hoặc ghi chú trỏ về vé cũ. Làm vậy hoa hồng của vé cũ và vé mới không chồng lên nhau.

Tình huống khó hơn là hãng đã chi hoa hồng của tháng trước rồi tháng sau vé mới bị hủy. Cách đơn giản là thêm một dòng điều chỉnh ở tháng phát sinh, với giá trị hoa hồng âm tương ứng. Chuyện hãng có thu hồi hoa hồng hay không là tùy thỏa thuận của bạn với họ, nên hãy hỏi lại trước khi áp dụng.

Đổi tỷ lệ giữa chừng

Hãng đổi tỷ lệ từ tháng này thì sửa ô B trong tab TyLe, và tất cả các vé cũ cũng thay đổi theo vì chúng cùng tra về một ô. Hoa hồng của các tháng đã chốt bị sai lặng lẽ. Cách chữa đơn giản: cuối mỗi tháng, chọn cột E và F của các dòng tháng đó, Sao chép rồi Dán đặc biệt, chỉ dán giá trị. Từ đó con số của tháng đã chốt không còn thay đổi theo bảng tra.

Các lỗi nhỏ khác

• Tên hãng có dấu cách thừa ở cuối làm VLOOKUP báo #N/A. Dùng danh sách thả xuống ở cột C thì tránh được.
• Ngày nhập dạng chữ thì SUMIFS không tính vào tháng nào. Kiểm tra bằng cách căn lề ô: ngày thật tự căn phải.
• Một hãng có tỷ lệ khác nhau theo hạng vé thì bảng tra một cột không đủ. Cần thêm cột hạng vé và tra theo hai điều kiện, hoặc ghép hãng và hạng thành một khóa duy nhất.
• Nhân viên sửa nhầm công thức ở cột E hoặc F. Khóa hai cột này ở chế độ bảo vệ vùng dữ liệu, chỉ cho chủ file chỉnh.

Bảng tra theo hãng chỉ là một mảnh của việc quản lý đại lý. Phần theo dõi tiền khách còn nợ và nhà cung cấp còn chờ trả nằm ở cách quản lý công nợ đại lý vé máy bay bằng Google Sheets, còn cách ghi mã PNR và trạng thái booking để dòng nào cũng truy ngược được nằm ở hướng dẫn quản lý booking PNR và hoa hồng vé máy bay.

Khi file tự dựng bắt đầu khó giữ

File tự dựng như trên đủ dùng cho một phòng vé vài người, vài chục vé mỗi tháng. Nó bắt đầu mệt khi có nhiều nhân viên cùng nhập (ai cũng có thể sửa nhầm bảng tra), khi công nợ khách doanh nghiệp tách ra thành một file nữa, hoặc khi bạn muốn xem doanh thu theo nhân viên và theo nhiều tiêu chí mà không phải viết thêm công thức mỗi lần.

Nếu đến mức đó, SheetStore có Quản Lý Bán Hàng - Vé Máy Bay, một file Google Sheets dựng sẵn cho đại lý vé: quản lý booking và mã PNR, xuất vé và hóa đơn, công nợ khách hàng và nhà cung cấp, hoa hồng đại lý theo hãng bay, quản lý khách hàng và doanh nghiệp, báo cáo doanh thu theo nhiều tiêu chí, phân quyền nhân viên bán vé. Giá 798.000đ, trả một lần dùng trọn đời, không phí hằng tháng, có dùng thử 7 ngày. Dữ liệu nằm trong Google Drive của chính bạn, nên cần có tài khoản Google; nếu bạn không muốn hoặc không thể dùng tài khoản Google thì file này không phù hợp.

Nếu số vé chưa nhiều, cứ giữ cách tự dựng ở trên. Bảng tra, công thức VLOOKUP và SUMIFS bạn vừa làm chính là nền tảng để sau này chuyển sang một file đầy đủ hơn mà vẫn hiểu hoa hồng đang được tính ra sao.

Câu hỏi thường gặp

Vì sao không nên gõ thẳng tỷ lệ vào công thức kiểu =D2*0.03?

Khi hãng đổi chính sách, bạn phải sửa từng dòng. Nhân viên mới dễ copy nhầm công thức của hãng khác, và không ai nhớ dòng nào đang dùng 3%, dòng nào đã chuyển sang 3,5%. Tách tỷ lệ sang tab TyLe thì mỗi hãng một dòng, vé chỉ ghi tên hãng, công thức tự tra về. Khi cần đổi, bạn sửa đúng một ô.

Vì sao bài cố ý không bọc IFERROR quanh công thức VLOOKUP?

Nếu ai đó gõ nhầm tên hãng, ô E hiện #N/A ngay nên bạn thấy lỗi liền. Bọc IFERROR trả về 0 thì lỗi biến mất, hoa hồng của vé đó âm thầm thành 0 cho đến cuối tháng, khi bạn thấy tổng thiếu mà không biết thiếu ở đâu. Để chặn gõ nhầm từ đầu, hãy dùng danh sách thả xuống ở cột hãng.

Khi khách hủy hoặc đổi vé thì xử lý dòng trong bảng thế nào?

Đừng xóa dòng. Chuyển cột trạng thái thành "Đã hủy", công thức ở cột hoa hồng sẽ tự đưa về 0 và vẫn còn dấu vết để đối chiếu với hãng. Khi khách đổi chuyến, coi như vé cũ hủy rồi thêm một dòng mới cho vé mới, ghi cùng mã PNR hoặc ghi chú trỏ về vé cũ, để hoa hồng không chồng lên nhau.

Hãng đổi tỷ lệ giữa chừng thì làm sao để các tháng đã chốt không bị sai?

Khi bạn sửa ô tỷ lệ trong tab TyLe, mọi vé cũ cũng đổi theo vì cùng tra về một ô, nên hoa hồng tháng đã chốt bị sai lặng lẽ. Cách chữa: cuối mỗi tháng, chọn cột E và F của các dòng tháng đó, Sao chép rồi Dán đặc biệt, chỉ dán giá trị. Từ đó số của tháng đã chốt không đổi theo bảng tra.