Người làm marketing dùng Excel hằng tuần để làm sạch lead, so chi phí quảng cáo với số đăng ký và canh ngân sách của từng chiến dịch. Bộ bài tập Excel Marketing có lời giải này gồm 5 bài bám sát các việc đó, theo đúng kiểu đề test khi phỏng vấn vị trí Marketing Executive hoặc Digital Marketing.

Bộ đề có 5 bài, tổng 100 điểm, thời gian làm bài gợi ý là 120 phút. Bạn sẽ làm sạch danh sách lead và cắt mã utm_campaign, tính CTR, CPL, CPA, ROAS theo chiến dịch, đếm phễu chuyển đổi theo kênh, theo dõi ngân sách giữa tháng và phân bổ lại ngân sách còn lại theo ROAS. Bài 1 đến Bài 4 làm được trên Excel 2010 trở lên, còn Bài 5 cần Excel 365 hoặc Excel 2024. Mọi công ty, chiến dịch và số liệu trong đề đều là giả định, không lấy từ nền tảng quảng cáo thật.

Tải bộ đề: Bộ đề Excel có lời giải - Marketing (OneDrive)

Bạn làm bài mà bị vướng ở câu nào? Bạn có thể đăng câu hỏi trong nhóm Vườn ươm nhà Nghề để cùng trao đổi và được giải đáp. Vào nhóm hỏi đáp

Bộ đề Excel Marketing gồm những bài nào?

Bài

Điểm

Việc cần làm

Kiến thức chính

1

20

Bạn làm sạch lead từ form, cắt mã UTM và tách học viên cũ.

SUBSTITUTE, LEFT, MID, LEN, SEARCH, FIND, UPPER, COUNTIF

2

20

Bạn lập báo cáo hiệu quả quảng cáo theo chiến dịch và kênh.

ROUND, IF lồng, AND, VLOOKUP, SUMIFS

3

20

Bạn đếm phễu chuyển đổi theo kênh và cảnh báo lead chưa gọi.

MATCH, COUNTIFS, AVERAGEIFS, INDEX, MIN

4

20

Bạn theo dõi ngân sách tháng so với kế hoạch và dự báo chi tiêu.

SUMIFS theo khoảng ngày, IF lồng, MAX, ROUND

5

20

Bạn phân bổ lại ngân sách còn lại theo ROAS.

XLOOKUP, SUMPRODUCT, FILTER, SORT, CHOOSECOLS

Bài 5 dùng kết quả của Bài 2 và Bài 4, nên bạn cần làm xong 2 bài này trước.

Bạn dùng file bộ đề như thế nào?

File có 3 sheet. Sheet Giới thiệu khóa học giới thiệu khóa học của Nghề Excel, sheet Đề bài là nơi bạn làm bài, còn sheet Kết quả chứa lời giải bằng công thức.

Đầu sheet Đề bài gồm tiêu đề, bối cảnh chung, bảng tra cứu ngày báo cáo và danh mục 8 chiến dịch
Ngày báo cáo 15/11/2026 và danh mục 8 chiến dịch được dùng chung cho cả 5 bài

Bạn điền công thức vào các cột hoặc ô có tiêu đề màu vàng, không gõ số tay. Các ngưỡng như CPL tối đa, ROAS mục tiêu hay biên vượt ngân sách đều nằm trong bảng tra cứu, nên công thức phải tham chiếu tới ô đó. Bạn nên làm xong rồi mới mở sheet Kết quả để đối chiếu.

Danh sách 20 lead trên sheet Đề bài, 9 cột tiêu đề vàng từ SĐT bỏ ký tự tới Phân loại đang để trống
Các cột tiêu đề vàng đánh dấu phần bạn cần điền

Các phần dưới đây tóm tắt yêu cầu và cách giải chính. Yêu cầu đầy đủ của từng bài nằm ngay trong file.

Bài 1: Bạn làm sạch danh sách lead và mã UTM như thế nào?

Khách điền số điện thoại theo nhiều kiểu, nên bước đầu tiên là bỏ dấu cách, dấu chấm, gạch ngang và dấu cộng bằng 4 hàm SUBSTITUTE lồng nhau ở cột G. Sau đó, công thức tại ô H48 đưa số về dạng 10 số bắt đầu bằng 0:

=IF(G48="","",IF(AND(LEFT(G48,2)="84",LEN(G48)=11),"0"&MID(G48,3,9),IF(LEFT(G48,1)<>"0","0"&G48,G48)))

Điểm khó nằm ở điều kiện dài đúng 11 ký tự. Lead số 2 gõ "+84 903 112 233", sau khi bỏ ký tự còn 84903112233 với 11 ký tự, nên công thức thay "84" bằng "0" thành 0903112233. Lead số 8 gõ "845 123 456" thì chỉ có 9 ký tự, nên công thức không cắt "84" mà thêm số 0 vào đầu thành 0845123456. Nếu bạn chỉ xét LEFT(G48,2)="84", số của lead số 8 sẽ bị cắt sai.

Mã chiến dịch nằm sau chuỗi "utm_campaign=" trong link đăng ký. Cột L tìm vị trí ký tự đầu của mã bằng SEARCH cộng 13, còn công thức tại ô M48 cắt mã tới trước dấu "&" kế tiếp:

=IF(L48=0,"",UPPER(MID(F48,L48,FIND("&",F48&"&",L48)-L48)))

Phần F48&"&" nối thêm một dấu "&" vào cuối link, nên FIND vẫn tìm thấy khi mã đứng cuối link. Ví dụ, link của lead số 3 là "example.com/kids?utm_campaign=TT-KIDS&utm_source=tiktok", nên mã bắt đầu ở ký tự 31 và dấu "&" nằm ở ký tự 38. Hàm MID lấy đúng 7 ký tự "TT-KIDS".

Cột Phân loại xét 4 trường hợp và đối chiếu SĐT chuẩn với danh sách học viên cũ. Công thức tại ô O48 như sau:

=IF(AND(I48="Sai",K48="Sai"),"Loại",IF(I48="Sai","Chỉ có email",IF(COUNTIF($C$40:$C$44,H48)>0,"Học viên cũ","Lead mới")))

Danh sách 20 lead đã làm sạch với SĐT chuẩn, email chuẩn, mã chiến dịch, kênh và phân loại
Lead số 7 và số 20 sai cả SĐT lẫn email nên được tô đỏ và xếp vào nhóm Loại

Kết quả có 12 lead mới, 3 học viên cũ, 3 lead chỉ có email và 2 lead bị loại. Lead có link không gắn UTM được tính vào kênh "Tự nhiên", còn mã IG-IELTS không có trong danh mục nên được ghi "Không rõ".

Bảng tổng hợp lead theo 6 kênh, tổng 20 lead và 12 lead mới, tỷ lệ 60%
TikTok Ads có 4 lead nhưng chỉ 1 lead mới, vì có 1 học viên cũ, 1 lead chỉ có email và 1 lead bị loại

Bài 2: Bạn đánh giá hiệu quả từng chiến dịch quảng cáo như thế nào?

Bài này tính CTR, CPL (chi phí cho 1 lead), Tỷ lệ chốt, CPA (chi phí cho 1 đăng ký) và ROAS (doanh thu trên chi phí quảng cáo) cho 8 chiến dịch tháng 10. Chiến dịch ZL-KIDS có 0 lead, nên công thức CPL đặt tại ô K99 rồi kéo xuống phải có IF, để ô K105 của ZL-KIDS để trống thay vì báo lỗi chia cho 0:

=IF(F99=0,"",ROUND(C99/F99,0))

Cột Đánh giá xét ROAS trước, rồi mới xét cùng lúc ROAS và CPL. Công thức tại ô O99 như sau:

=IF(N99<$E$95,"Dừng",IF(AND(N99>=$E$94,K99<=$E$93),"Tăng ngân sách","Theo dõi"))

Chiến dịch FB-GT có doanh thu 90.000.000 đồng trên chi phí 30.000.000 đồng, nên ROAS đúng bằng mốc 3,00 và được "Tăng ngân sách". Chiến dịch GG-IELTS có ROAS 3,47 nhưng CPL là 316.667 đồng, cao hơn mức tối đa 250.000 đồng, nên chỉ được "Theo dõi". Chiến dịch ZL-GT có ROAS đúng bằng mốc tối thiểu 1,00, nên vẫn được "Theo dõi" chứ chưa phải dừng.

Các cột kết quả của bảng hiệu quả 8 chiến dịch tháng 10 gồm kênh, CTR, CPL, tỷ lệ chốt, CPA, ROAS và đánh giá
Dòng Tổng cộng tính lại từ số tổng: CPL 220.985 đồng và ROAS 2,69

Bạn cần chú ý dòng Tổng cộng phải tính lại từ các số tổng. Ví dụ, ROAS của dòng này tại ô N107 là =ROUND(H107/C107,2), tức 508.000.000 chia 188.500.000 bằng 2,69. Nếu lấy trung bình cột ROAS, bạn sẽ ra một con số sai.

Bảng tổng hợp theo kênh dùng SUMIFS cộng chi phí, lead, đăng ký và doanh thu theo cột Kênh vừa tra được. Công thức tại ô C111 như sau:

=SUMIFS($C$99:$C$106,$I$99:$I$106,B111)

Bảng tổng hợp 4 kênh với chi phí, tỷ trọng, lead, đăng ký, doanh thu, CPL và ROAS
TikTok Ads có CPL thấp nhất là 168.627 đồng, nhưng ROAS chỉ đạt 1,49

Bảng kênh cho thấy CPL thấp chưa chắc mang về doanh thu. TikTok Ads có CPL thấp nhất nhưng chỉ có 9 đăng ký trên 255 lead, trong khi Facebook Ads có ROAS 3,44.

Bài 3: Bạn đếm phễu chuyển đổi theo kênh như thế nào?

Mỗi lead trên CRM chỉ ghi giai đoạn hiện tại. Một lead đã đăng ký cũng đã đi qua bước liên hệ và học thử, nên phễu phải đếm cả các lead ở giai đoạn sau. Cột Bậc dùng MATCH để đổi tên giai đoạn thành số 1 đến 4, và công thức tại ô H140 là =MATCH(E140,$C$132:$C$135,0).

Sau đó, bảng phễu đếm số lead có bậc lớn hơn hoặc bằng bậc của từng cột. Công thức tại ô C167 lấy bậc từ chính tên giai đoạn ở dòng tiêu đề, nên bạn sao chép được sang phải và xuống dưới:

=COUNTIFS($G$140:$G$163,$B167,$H$140:$H$163,">="&MATCH(C$166,$C$132:$C$135,0))

Cột Cảnh báo chỉ xét lead còn ở bậc 1. Lead L022 nhận ngày 13/11/2026, tới ngày báo cáo là 2 ngày, đúng bằng mức tối đa nên vẫn là "Chờ gọi". Lead L021 nhận ngày 12/11/2026 đã qua 3 ngày, nên bị ghi "Quá hạn gọi".

Danh sách 24 lead từ CRM với kênh, bậc, số ngày từ lúc nhận, số ngày chốt và cảnh báo
4 lead quá hạn gọi được tô đỏ bằng Conditional Formatting

Cột Bước rơi nhiều nhất tìm bước có tỷ lệ chuyển đổi thấp nhất. Công thức tại ô K167 như sau:

=INDEX($G$166:$I$166,MATCH(MIN(G167:I167),G167:I167,0))

Facebook Ads có 8 lead, 6 lead đã liên hệ, 4 lead đã học thử và 2 lead đã đăng ký, nên tỷ lệ 3 bước là 75%, 67% và 50%. Bước rơi nhiều nhất của kênh này là "Học thử → Đăng ký". Zalo Ads chưa có lead học thử, nên tỷ lệ "Học thử → Đăng ký" để trống và hàm MIN bỏ qua ô trống đó.

Bảng phễu 4 kênh với số lead từng giai đoạn, tỷ lệ chuyển đổi, bước rơi nhiều nhất và số ngày chốt trung bình
Toàn bộ 24 lead cho ra 5 đăng ký, tức tỷ lệ chuyển đổi 21%

TikTok Ads có 2 bước cùng đạt 50%. Trong trường hợp này, hàm MATCH trả về bước đứng trước là "Liên hệ → Học thử".

Bài 4: Bạn theo dõi ngân sách quảng cáo giữa tháng như thế nào?

Đề đặt bối cảnh bạn làm báo cáo vào sáng 17/11/2026 với số liệu chốt đến hết ngày báo cáo 15/11/2026. Nhật ký chi tiêu được xuất cùng buổi sáng đó, nên có cả dòng của tháng 10 và dòng ngày 16/11/2026. Công thức Đã chi tại ô D219 chỉ cộng các dòng từ đầu tháng 11 tới ngày báo cáo:

=SUMIFS($D$187:$D$208,$C$187:$C$208,B219,$B$187:$B$208,">="&$E$10,$B$187:$B$208,"<="&$E$9)

Dự báo cả tháng giả định chiến dịch tiếp tục chi đều như 15 ngày đầu. Cột Tình trạng tại ô H219 so dự báo với ngân sách cộng hoặc trừ biên cho phép:

=IF(C219=0,"Đã tắt",IF(F219>C219*(1+$E$211),"Vượt ngân sách",IF(F219<C219*(1-$E$212),"Chi chậm","Đúng nhịp")))

Chiến dịch FB-IELTS đã chi 33.600.000 đồng trong 15 ngày, nên dự báo cả tháng là 67.200.000 đồng. Con số này lớn hơn 63.000.000 đồng (ngân sách 60.000.000 đồng cộng biên 5%), nên chiến dịch bị ghi "Vượt ngân sách". Dòng chi 2.600.000 đồng ngày 16/11/2026 của chiến dịch này không được tính, vì ngày đó nằm sau ngày báo cáo.

Bảng theo dõi ngân sách tháng 11 của 8 chiến dịch với đã chi, dự báo, tình trạng, còn lại và mức chi mỗi ngày
Chiến dịch ZL-GT đã chi 120% ngân sách nên phần Còn lại được MAX đưa về 0

Bài này có 2 trường hợp nằm đúng mốc. FB-GT có dự báo 37.800.000 đồng, đúng bằng 105% ngân sách, còn GG-KIDS có dự báo 18.700.000 đồng, đúng bằng 85% ngân sách. Cả 2 chiến dịch vẫn được ghi "Đúng nhịp", vì đề dùng phép so sánh lớn hơn và nhỏ hơn, không dùng dấu bằng.

Bài 5: Bạn phân bổ lại ngân sách theo ROAS như thế nào?

Bài cuối chia lại toàn bộ 95.150.000 đồng còn lại của tháng 11. Bài này cần Excel 365 hoặc Excel 2024 vì có hàm CHOOSECOLS. ROAS và Đánh giá được lấy từ Bài 2 bằng XLOOKUP, ví dụ công thức tại ô C248 là =XLOOKUP(B248,$B$99:$B$106,$N$99:$N$106).

Trọng số của mỗi chiến dịch bằng ngân sách tháng 11 nhân ROAS tháng 10. Chiến dịch bị đánh giá "Dừng" và chiến dịch đã tiêu hết ngân sách tháng (Còn lại theo kế hoạch bằng 0) có trọng số bằng 0. Công thức tại ô G248 dùng OR để gộp 2 điều kiện này:

=IF(OR(D248="Dừng",F248=0),0,E248*C248)

Chiến dịch ZL-GT có ROAS 1,00 và không bị "Dừng", nhưng đã chi 120% ngân sách ở Bài 4. Vì Còn lại theo kế hoạch của ZL-GT bằng 0, chiến dịch này không được nhận thêm tiền.

Phân bổ tạm được làm tròn đến hàng trăm nghìn, nên tổng các dòng có thể lệch so với số cần chia. Công thức tại ô I248 cộng phần lệch đó cho chiến dịch có trọng số lớn nhất:

=H248+IF(G248=MAX($G$248:$G$255),$E$243-SUM($H$248:$H$255),0)

Tổng Phân bổ tạm là 95.100.000 đồng, nên phần lệch 50.000 đồng được cộng cho FB-IELTS. Chiến dịch này có trọng số lớn nhất là 223.800.000, và Phân bổ đề xuất của nó là 38.150.000 đồng.

Cột Hành động xét ngân sách tháng 11 trước. ZL-KIDS có ngân sách bằng 0 nên được ghi "Đã tắt", còn ZL-GT có Phân bổ đề xuất bằng 0 nên được ghi "Tắt". Công thức tại ô K248 như sau:

=IF(E248=0,"Đã tắt",IF(I248=0,"Tắt",IF(I248>F248*(1+$E$244),"Tăng",IF(I248<F248*(1-$E$244),"Giảm","Giữ"))))

Bảng phân bổ lại ngân sách 8 chiến dịch với ROAS, trọng số, phân bổ tạm, phân bổ đề xuất, chênh lệch và hành động
Các dòng Tăng được tô xanh, các dòng Tắt được tô đỏ, còn ZL-KIDS ghi Đã tắt vì ngân sách tháng 11 bằng 0

Sau đó, bạn dùng SUMPRODUCT để so doanh thu kỳ vọng của 2 phương án, với giả định ROAS giữ như tháng 10. Công thức tại ô E262 là =SUMPRODUCT($I$248:$I$255,$C$248:$C$255). Phương án giữ kế hoạch cho ra 277.705.000 đồng, còn phương án phân bổ lại cho ra 312.363.500 đồng, tức tăng 34.658.500 đồng.

Danh sách chiến dịch bị giảm hoặc tắt chỉ dùng một công thức tại ô B267, và kết quả tự tràn ra các ô bên dưới:

=SORT(FILTER(CHOOSECOLS($B$248:$L$255,1,9,10),($K$248:$K$255="Giảm")+($K$248:$K$255="Tắt")),2,1)

Bảng doanh thu kỳ vọng của 2 phương án và danh sách 4 chiến dịch bị giảm hoặc tắt
TT-GT bị tắt và mất 9.900.000 đồng nên đứng đầu danh sách, còn ZL-KIDS ghi Đã tắt nên không nằm trong danh sách

Dấu cộng giữa 2 điều kiện trong FILTER có nghĩa là "hoặc". CHOOSECOLS lấy cột thứ 1, 9 và 10 của vùng, tức Mã CD, Chênh lệch và Hành động.

Đề đã đơn giản hóa những gì so với thực tế?

Bộ đề giữ đúng bản chất nghiệp vụ, nhưng đơn giản hóa một số điểm để bài gọn hơn:

  • SĐT và email: Đề chỉ xét SĐT đủ 10 số và email có dấu chấm sau "@", chưa kiểm tra đầu số nhà mạng hay tên miền email có thật.

  • Ghi nhận doanh thu: Đề gán mỗi đăng ký cho đúng một chiến dịch, trong khi khách thật có thể thấy nhiều quảng cáo trước khi đăng ký.

  • ROAS: Đề tính ROAS trên học phí ghi nhận trong tháng, chưa tính học viên đăng ký khóa tiếp theo hay hoàn học phí.

  • Dự báo chi tiêu: Đề giả định mỗi ngày chi đều nhau, trong khi chi tiêu thật thường thay đổi theo ngày trong tuần.

  • Phân bổ lại: Đề chia tiền theo một công thức, chưa tính mức chi tối thiểu để nền tảng quảng cáo chạy ổn định.

  • Số tiền cần chia: Đề coi phần còn lại tính đến ngày báo cáo 15/11/2026 là số tiền cần chia, dù ngày làm báo cáo là 17/11/2026.

Khi đi làm, bạn cần kiểm tra các điểm này theo cách đo và quy trình của công ty mình.

Bạn nên luyện bộ đề này theo thứ tự nào?

Bạn nên làm lần lượt từ Bài 1 đến Bài 5, vì độ khó tăng dần và Bài 5 dùng kết quả của Bài 2, Bài 4. Nếu vướng ở Bài 1, bạn có thể đọc lại bài hàm xử lý chuỗi trong Excel. Nếu vướng ở phần doanh thu kỳ vọng của Bài 5, bạn có thể xem bài hàm SUMPRODUCT trong Excel.

Nghề Excel cũng chia sẻ bộ bài tập Excel Sales Admin có lời giải nếu bạn muốn luyện thêm phần đơn hàng và công nợ.

Tải bộ đề tại đây: Bộ đề Excel có lời giải - Marketing (OneDrive)

Bạn muốn dùng Excel thành thạo cho công việc marketing?

Bộ đề này giúp bạn tự kiểm tra kỹ năng. Điều khó hơn khi đi làm là tự đọc yêu cầu và biết nên kết hợp các hàm ra sao. Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.