Chắc hẳn đối với các bạn gặp nhiều khó khăn trong việc sử dụng Microsoft Excel thì học phần Tin học ứng dựng ắt hẳn cũng là một “thảm họa” đối với các bạn. Dưới đây mình xin gửi các bạn đề ôn tập tổng hợp kèm cách giải để hỗ trợ các bạn trong việc đạt điểm cao cuối kỳ nhé!
Các bạn có thể lưu file phía trên lại để luyện tập cùng mình nhé ❤
Ý nghĩa Số HĐ:
– 4 ký tự đầu là số thứ tự hóa đơn.
– Ký tự 5: Mã sản phẩm.
– Ký tự 6: Loại sản phẩm.
– Ký tự 7: Nếu là 1: khách hàng giao dịch dưới 5 năm.
Nếu là 2: khách hàng giao dịch trên 5 năm.
Yêu cầu bài làm:
- Điền tên sản phẩm. Điền giá trị cột đơn giá theo tên và loại của sản phẩm.
- Tính giá trị tiền giảm của tổng số lượng đã bán. Biết rằng tháng của hóa đơn nằm trong tháng khuyến mãi thì được giảm 10% trị giá/1 sp.
- Tính thành tiền = Số lượng * Đơn giá – Tiền giảm.
- Điền giá trị Bảng thống kê doanh thu 1 – theo Tên SP và Loại.
- Trích các hóa đơn bán trong tháng 2 ra vùng riêng trên bảng tính.
- Cho biết tổng số hóa đơn đã bán trong chương trình giảm giá.
- Tạo bảng PivotTable phân tích báo cáo và đồ thị.
- Tạo sheet mới và dùng công cụ SubTotal để báo cáo tổng kết.
- Định dạng dấu phân cách hàng ngàn.
- Trình bày: tiêu đề, canh biên, kẻ khung, …
- Định dạng bảng in như sau:
- Thiết lập khu vực in: Bảng báo cáo Chi tiết Bán hàng năm 2020, không in bảng dò tìm. Canh lề bảng báo cáo chính giữa trang in.
- Khổ giấy A4 – giấy ngang, khoảng cách lề top = bottom = left = right = 0.5 inch, header = footer = 0.3 inch.
- In ra tập tin PDF.
BÀI GIẢI
1. Điền tên sản phẩm. Điền giá trị cột đơn giá theo tên và loại của sản phẩm.
Tên sản phẩm: Dựa vào số hóa đơn và bảng dò tìm bên dưới thì mình sẽ xác định sử dụng hàm VLOOKUP vì Mã SP và Tên SP ở bảng dò nằm ở 2 cột khác nhau nên ta sẽ phải dò thẳng đứng từ trái sang. Có thể thấy thấy ký tự viết tắt của Mã SP nằm trong Số HĐ và ta cần lấy 1 ký tự ra nên sẽ dùng hàm MID. Từ đó ta có thể viết hàm như sau (tùy máy tính mà các bạn có thể ngăn cách bằng dấu, hoặc dấu ; nhé):
=VLOOKUP(MID(B6,5,1),$B$17:$C$19,2,0)
Giải thích thêm trong trường hợp các bạn quên, cột từ ký hiệu được coi là cột 1, còn cột cần điền là cột 2, do đó ta phải điền số 2 vào trong hàm, còn số 0 ý nghĩa là để điền chính xác từ trùng với ký hiệu trong hóa đơn.
Đơn giá: Để điền được cột này, cần phải trùng cả tên SP và Loại SP, do đó ta sử dụng hàm INDEX kết hợp hàm MATCH (để đảm bảo giá trị cần dò và giá trị trong bảng dò trùng nhau) và hàm VALUE (vì Loại SP mang ký tự số, để có thể lấy được ký tự thứ 6 trong số HĐ thì ta phải đổi từ “ký tự” sang “chữ số”).
=INDEX($D$17:$G$19,MATCH(C6,$C$17:$C$19,0),MATCH(VALUE(MID(B6,6,1)),$D$16:$G$16,0))
Kết quả câu 1:
2. Tính giá trị tiền giảm của tổng số lượng đã bán. Biết rằng tháng của hóa đơn nằm trong tháng khuyến mãi thì được giảm 10% trị giá/1 sp.
Tiền giảm: Ta lấy SL nhân với Đơn giá, kết hợp dùng hàm IF, nếu ngày bán có tháng trùng với tháng khuyến mãi (tức MONTH bằng 3) thì nhân thêm với 10%, nếu không thì nhân với 0.
=E6*F6*IF(MONTH(D6)=$F$3,10%,0)
Kết quả câu 2:
3. Tính thành tiền = Số lượng * Đơn giá – Tiền giảm.
Thành tiền: Áp dụng các phép tính + – * / là ta có thể hoàn thành câu này một cách dễ dàng rồi nè.
=E6*F6-G6
Kết quả câu 3:
4. Điền giá trị Bảng thống kê doanh thu 1 – theo Tên SP và Loại.
Bảng thống kê doanh thu 1: Vì ta cần thống kê tổng số tiền đã bán, kèm theo 2 điều kiện là Tên SP và Loại nên ta có thể dử dụng hàm SUMIFS trong trường hợp này. Tuy nhiên, ở phần điều kiện dựa vào Tên SP, ta cần cố định I (tức là thành $I17 (bấm F4 3 lần)) để khi kéo hàm sẽ không nhảy qua cột J K L, vì nếu nhảy cột sẽ không thể so điều kiện với Tên SP được. Tương tự, khi so điều kiện với Loại SP ta cần cố định hàng 16 (tức J$16 (bấm F4 2 lần)). Tuy nhiên để có thể so với ký tự 6 trong Số HĐ, ta phải dùng ? (bỏ trong ngoặc kép vì là ký tự) thay cho ký tự tượng trưng và kết hợp dấu & để nối các ký tự lại.
=SUMIFS($H$6:$H$12,$C$6:$C$12,$I17,$B$6:$B$12,”?????”&J$16&”?”)
Kết quả câu 4:
5. Trích các hóa đơn bán trong tháng 2 ra vùng riêng trên bảng tính.
Đầu tiên, bấm vào nút mở rộng ở ô Ngày bán. Nếu không thấy, các bạn tô vùng từ A5 đến H5, chọn Sort & Filter trên thanh công cụ và chọn Filter.
Tiếp theo, chọn Tháng 2 (hay February) và nhấn OK.
Sau khi đã lọc, tô đen kết quả và Copy (Ctrl + C).
Cuối cùng, chọn một vùng riêng trên bảng tính và Paste (Ctrl + V), ta có kết quả bên dưới.
6. Cho biết tổng số hóa đơn đã bán trong chương trình giảm giá.
Có nhiều cách để tính tổng ở câu này, nhưng mình sẽ dùng cách mình thấy dễ hiểu và dễ dùng nhất. Mình chọn hàm COUNTIFS, chọn vùng và ra điều kiện, nếu >= ngày 1/3/2020 và <= ngày 31/3/2020 thì đếm số hóa đơn thỏa điều kiện.
=COUNTIFS($D$6:$D$12,”>=”&DATE(2020,3,1),$D$6:$D$12,”<=”&DATE(2020,3,31))
Kết quả câu 6:
7. Tạo bảng PivotTable phân tích báo cáo và đồ thị.
Đầu tiên, tô đen vùng cần tạo Privot (trừ cột STT).
Tiếp theo, vào Insert trên thanh công cụ, chọn PivotTable.
Khi này, trên màn hình sẽ hiển thị một khung nhỏ để lựa chọn tạo PivotTable, chọn Existing Worksheet để tạo Pivot ngay trong sheet hiện tại và chọn một ô để tạo Pivot, còn nếu các bạn muốn tạo trong sheet mới thì chọn New Worksheet, sau đó nhấn OK.
Bên phải màn hình sẽ hiện ra bảng PivotTable Fields, lúc này các bạn kéo Số HĐ vào ô Filter để khi tạo bảng để có thể lọc theo Số HĐ, trong trường hợp nếu đề bài yêu cầu lọc theo Tên sản phẩm hay Ngày bán thì kéo vào ô Filter, sau đó tích chọn tất cả các ô còn lại.
Để tạo đồ thị, ta tô đen bảng Pivot vừa tạo, trên thanh công cụ chọn Insert, chọn biểu tượng đồ thị và chọn loại đồ thị bất kỳ.
Cuối cùng, ta sẽ có kết quả câu 7:
8. Tạo sheet mới và dùng công cụ SubTotal để báo cáo tổng kết.
Đầu tiên, tô đen vùng như hình bên dưới (trừ cột STT) và Copy (Ctrl + C).
Sau đó, chuyển qua sheet mới, nhấn chuột phải vào một ô bất kỳ, chọn Paste Special và chọn Values & Number Formatting để khi dán dữ liệu không bị lỗi và giữ được định dạng ngày tháng.
Tiếp tục tô đen vùng như bên dưới, chọn Data trên thanh công cụ và chọn Sort.
Chúng ta sẽ sắp xếp theo Tên sản phẩm theo thứ tự từ A đến Z để khi sử dụng SubTotal mới có thể theo một trật tự nhất định.
Sau khi nhấn OK trong bảng Sort, ta tiếp tục chọn Subtotal.
Phía trên ta Sort theo Tên sản phẩm thì bên dưới ta cũng sẽ Tính tổng theo từng mục Sản phẩm.
Cuối cùng, ta có kết quả câu 8:
9. Định dạng dấu phân cách hàng ngàn.
Trước hết, ta tô đen những vị trí mang ý nghĩa tiền để phân cách, trên thanh công cụ chọn Home, sau đó bấm vào dấu mở rộng ở Number.
Sau đó, màn hình sẽ hiển thị bảng Format Cells, tại mục Category ta chọn Number, tick vào ô Use 1000 Separator (,) và nhấn OK.
Lặp lại ở những vị trí khác ta sẽ có kết quả câu 9:
Các bạn có thể tô đen những vị trí tiền và bấm nút F4 sau khi đã hoàn thành các bước trên để tiết kiệm thời gian vì F4 mang ý nghĩa lặp lại hành động trước đó của chúng ta.
10. Trình bày: tiêu đề, canh biên, kẻ khung, …
Ở việc định dạng các bạn có thể định dạng theo ý muốn của mình để bài Excel của các bạn dễ nhìn và đẹp hơn, sẽ gây ấn tượng hơn trong mắt người đọc. Dưới đây là ví dụ về cách định dạng của mình.
11. Định dạng bảng in như sau:
- Thiết lập khu vực in: Bảng báo cáo Chi tiết Bán hàng năm 2020, không in bảng dò tìm. Canh lề bảng báo cáo chính giữa trang in.
- Khổ giấy A4 – giấy ngang, khoảng cách lề top = bottom = left = right = 0.5 inch, header = footer = 0.3 inch.
- In ra tập tin PDF.
Trước tiên, đọc qua đề ta có thể thấy đề bài yêu cầu định dạng theo inch, do đó ta phải kiểm tra xem máy đang định dạng theo cm hay inch để phù hợp với yêu cầu. Ta chọn File trên thanh công cụ, sau đó chọn Options ở cuối thì sẽ xuất hiện bảng Excel Options, ta tiếp tục chọn Advanced và kéo xuống phần Display, ở mục Ruler Units chọn Inches và nhấn OK.
Trên thanh công cụ chọn Page Layout, chọn vào nút mở rộng ở Page Setup.
Tại mục Page, chọn Landscape (tức giấy ngang), Paper size ta chọn A4 như yêu cầu đề bài.
Qua mục Margins, ta đánh số theo yêu cầu đề bài, tiếp theo tick chọn cả Horizontally và Vertically để bảng tính được canh giữa.
Tới mục Sheet, Print Area (khu vực in) ta khoanh vùng cần in như hình bên dưới sau đó nhấn nút Print.
Màn hình Print hiện lên, ta tiếp tục nhấn nút Print.
Lúc này màn hình hiển thị Save hiện lên, ta kiểm tra tên và loại tệp (PDF) và nhấn Save.
Cuối cùng, ta có kết quả của câu 11.
Mình đã hướng dẫn xong cho các bạn về cách làm một bài Excel một cách hoàn chỉnh, cảm ơn các bạn đã đọc bài viết này và đừng ngần ngại đặt câu hỏi cho CLB Kết Nối Trẻ nếu các bạn có thắc mắc nhé. Mình sẽ đính kèm bên dưới thêm 1 đề nữa để các bạn có thể tự ôn tập tại nhà nè.
Chúc các bạn có một mùa thi thật tốt nhé ❤
Tác giả: Bùi Ngọc Mai Phương (sinh viên lớp 22DTH3, khoa Công nghệ thông tin)
Tài liệu Kết Nối Trẻ 