giao_trinh_excel + VBA hhhhhhhhhhhhhhhhhh

học excel cơ bản + VBA

1. Đào tạo excel cho sinh viên trường Kinh tế quốc dân Hà Nội - 08/2023

2. Giới thiệu mở đầu Thông tin cá nhân Hoàng Nam Anh Data Scientist - Team Lead in Viettel Digital Services Certifications: Senior Big Data Analyst - DASCA Financial Risk Manager (FRM) - GARP Microsoft Office Specialist (MOS) Word Expert - Microsoft Liên hệ: [email protected] 096 777 2902 Cách thức điểm danh Các nội dung mong muốn chia sẻ, giảng dạy Bài kiểm tra giữa kỳ dạng project và chấm trực tiếp, theo thang 10

3. Giới thiệu mở đầu Nội dung đào tạo Tuần 1 Soạn thảo dữ liệu Căn chỉnh bản in và xuất file Tuần 2 Xác thực dữ liệu Định dạng có điều kiện Xử lý dữ liệu lặp Sử dụng bảng Tuần 3 Hàm tính toán trong Excel Tuần 4 Hàm mapping và điều kiện Tuần 5 Visual chart Tuần 6 Pivot Table Protect sheet Mail merge Tuần 7 Đọc dữ liệu Power query Phiên bản excel tối thiểu: 2013 hoặc 2016. Link tải: https://tinhte.vn/thread/download-office-2016-professional-plus-ban-chinh-thuc.2797924/ Link bẻ khóa (nếu không có tài khoản): https://tuihocit.com/tai-kmspico-11-kmspico-2020/ Cách cài office 365 miễn phí: https://vatvostudio.vn/huong-dan-dang-ky-office-365-mien-phi-tu-gia-han-tu-microsoft/ Luyện kỹ năng đánh máy: https://www.typingclub.com/sportal/

4. Chuyên đề 01 Soạn thảo dữ liệu

5. Chuyên đề 01 Phím tắt trong excel Phân biệt Hot Key (Ctrl + ) với Tips Key (Alt + )

6. Chuyên đề 01 Soạn thảo trong excel Thêm sheet (Ctrl F11) và chuyển sheet, rename và move sheet Hide/Unhide (không nên dùng với sheet) Bật Option để thiết lập các cài đặt ban đầu ( Alt + T + O), Auto Correct Kỹ thuật soạn thảo nhanh : Alt + Enter, F2, Wrap text, Format Column Width, số 0, hyperlink Kỹ thuật xóa dữ liệu: Clear, Backspace khác Delete, xóa hàng và cột Kỹ thuật chọn vùng nhanh ( sử dụng Ctrl, Shift và các mũi tên bàn phím ) Kỹ thuật copy, paste dữ liệu và công thức Kỹ thuật copy định dạng Kỹ thuật copy chèn 01 hàng/cột, chèn 01 vùng

7. Chuyên đề 01 Soạn thảo trong excel Chọn nhanh địa chỉ Đặt tên vùng (Ctrl + F3) hoặc vào Name Manager -> để chọn nhanh địa chỉ Thêm comment Tạo custom list trong advanced Fill justify để ghép chữ Fill series để đánh số tự động Cố định công thức (hàng, cột)

8. Chuyên đề 01 Tùy chỉnh định dạng hệ thống Tùy chỉnh dấu thập phân và dấu tách hàng nghìn ; tùy chỉnh định dạng ngày tháng MM/dd/ yyyy ; tùy chỉnh phân cách separator khi xuất csv (Chỉ cần apply và không cần tắt đi bật lại excel)

9. Chuyên đề 01 Tùy chỉnh định dạng Ctrl + 1 <POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT> [Blue]"Lãi "#,##0.00;[Red] "lỗ "#,##0.00;[Green] "hòa" Dữ liệu gốc Định dạng Code/Type 1234.59 1234.6 ####.# 8.9 8.900 #.000 .631 0.6 0.# 12 1234.568    12.0 1234.57 #.0# 5.25 5.3 5 1/4 5 3/10 # ???/??? Định dạng ngày tháng ddMMyyyy hh:mm:ss AM/PM hoặc am /pm Giới thiệu thêm về alignment, font, border & fill Giới thiệu thêm về chỉnh sửa themes và styles

10. Chuyên đề 01 Tìm kiếm, thay thế Ctrl + F, Ctrl + H Nên giới hạn vùng tìm kiếm Look in Values/Formula chỉ khác nhau với các cột có công thức Match case: không nên tick vì phân biệt hoa thường Match entire: bắt buộc phải gõ đủ nội dung của ô mới tìm được Tìm kiếm nâng cao với regex (* ? hoặc ~ để tìm *?) Lọc giá trị số được theo các khoảng Lưu ý tự động điền công thức khi có bộ lọc sẽ chỉ điền các ô không bị lọc . Xóa hàng khi có bộ lọc cũng sẽ tương tự Hạn chế sử dụng tính năng Table vì nó khiến cho excel bị nặng

11. Chuyên đề 01 Sắp xếp và Bộ lọc Chọn add level để thêm thứ tự sắp xếp Lọc nâng cao với regex Lọc giá trị số được theo các khoảng Lưu ý tự động điền công thức khi có bộ lọc sẽ chỉ điền các ô không bị lọc . Xóa hàng khi có bộ lọc cũng sẽ tương tự Hạn chế sử dụng tính năng Table khi lọc vì nó khiến cho excel bị nặng

12. Chuyên đề 02 Căn chỉnh bản in

13. Chuyên đề 02 Chia tách trang Hiển thị đường kẻ ngắt trang Chỉ có thể làm đường kẻ ngắt trang nhỏ lại theo chiều ngang hoặc dọc Nếu muốn đường kẻ ngắt trang giãn ra chỉ có cách căn chỉnh lề theo bước tiếp

14. Chuyên đề 02 Căn chỉnh bản in Căn chỉnh horizontally, giảm lề Tab page chỉnh lại độ fit của trang ( chế độ adjust to hoặc fit to) Luôn chọn khổ giấy A4 khi in Giới thiệu thêm về tạo header/footer

15. Chuyên đề 02 Cố định tiêu đề Cố định lại tiêu đề khi in nhiều trang tiêu đề cần lặp lại các trang Ngoài ra có thể chỉnh thêm break trang , điểm đặt dưới 01 dòng Khi sheet quá dài có thể cố định lại tiêu đề để xem cho dễ Điểm đặt con trỏ sẽ dưới một dòng hoặc bên phải chỗ cần cố định

16. Chuyên đề 02 Xem nhiều sheet cùng lúc Chọn new window + tick Windows of active khi muốn xem song song nhiều sheet Không tick để xem song song các workbook với nhau Chọn side by side và scrolling để xem song song khi lăn con trỏ Trong thực tế thì nên dùng các hàm mapping

17. Chuyên đề 02 X u ất file Nhấn Ctrl + O, Ctrl + S để mở và lưu file Nhấn F12 để Save as Xlsm để lưu có Macro ( có code VBA) Xlsx, xls chỉ khác nhau về phiên bản office Csv, csv UTF8, Tab delimited để dễ đưa vào các hệ thống khác Pdf là để in và gửi tài liệu Khi save pdf cần lưu ý thêm các option hình bên để bản in chính xác nhất

18. Chuyên đề 03 Xác thực và định dạng dữ liệu

19. Chuyên đề 03 Xác thực dữ liệu Whole number chỉ cho phép nhập liệu là số nguyên Decimal Decimal này chỉ cho phép nhập liệu là số nguyên hoặc số thập phân. T ránh được những sai sót do quy định về số thập phân Quốc tế và Việt Nam List chỉ cho phép nhập liệu từ một danh sách sẵn có. Date chỉ cho phép nhập liệu là ngày. Text length chuỗi có độ dài xác định ( tính bằng số ký tự , kể cả khoảng trắng , dấu ,...). Input Message cho phép hiển thị hướng dẫn nhập liệu khi di chuyển chuột vào ô đó Chức năng Error Alert nếu nhập liệu không đúng quy định sẽ nhận được thông báo .

20. Chuyên đề 03 Xác thực động dữ liệu Hướng dẫn sử dụng hàm rẽ nhánh Lưu ý sử dụng iferror, ifna Đặt tên vùng dữ liệu , sau đó có 2 cách + Sử dụng hàm if để tạo ra các nhánh tương ứng với các list khác nhau =IF($K$8="131","MAKH",IF($K$8="331","MANB","MANV")) + Sử dụng indirect và tên vùng dữ liệu

21. Chuyên đề 03 Định dạng có điều kiện Only cells that contain – để so sánh giá trị số Use a formula – tùy ý đặt các công thức miễn sao ouput ra là True hoặc False, đây là rule linh hoạt nhất Cell based on their values – định dạng theo giá trị để vẽ heatmap, icon chart Duplicate values – để tìm các giá trị lặp hoặc có thể tìm bằng formula với công thức countif =COUNTIF($V$22:$W$28;V22)>1 Tính năng xóa dòng bị lặp có thể xóa lặp trên 01 cột hoặc nhiều cột

22. Chuyên đề 03 Tạo bảng Chỉnh định dạng Lọc bảng, tạo slicers và sắp xếp Tính toán trong bảng Xóa bản ghi lặp trong bảng Convert to range -> Hạn chế dùng bảng với dữ liệu lớn

23. Chuyên đề 04 Formulas

24. Chuyên đề 04 Cơ bản về hàm Các phép toán cơ bản (sqrt, pow, =, <>, &) Các phép thống kê cơ bản (avg, sum, min, max, median) Cách tra cứu function Cách nhập function nested tránh lỗi cú pháp (sử dụng Insert function)

25. Chuyên đề 04 Xử lý string Dùng exact hơn là = Concat và định dạng số Sử dụng trim, lower, upper, proper để chuẩn hóa Kết hợp len, left, right, mid Thường dùng substitute hơn replace Dùng search hơn find (phân biệt chữ hoa, không dùng được wildcard) Sử dụng text to column

26. Chuyên đề 04 Xử lý datetime Lỗi năm nhuận (năm chia hết 100), ngày trước 1900 Trích xuất year, day, month, weekday, weeknum Tính datediff (có workday hoặc không) Now và Today Hàm Date, Time và convert serial number Timevalue convert string sang thời gian

27. Chuyên đề 04 Nhóm hàm count, sum Nhóm đếm: count, countif, countifs, counta, countblank Kết hợp với kiểm tra định dạng isna, istext, iserror = isna + iserr Nhóm tính tổng: sum, sumif, sumifs, sumproduct Cách tính cummulative sum (sử dụng công thức cố định)

28. Chuyên đề 04 Nhóm hàm join Cách 1 Vlookup Hàm tìm lookup_value tại cột đầu tiên trong bảng/mảng table_array, nếu tìm được sẽ trả về giá trị ở dòng tương ứng tại cột ở vị trí col_index_num trong bảng. R ange_lookup: thường để mặc định là false để tìm chính xác nhất Hạn chế : chỉ lookup theo cột đầu tiên Cách 2 Index, match Khắc phục được việc chỉ lookup theo cột đầu tiên mà có thể tìm kiếm theo hai mảng MATCH tìm lookup_value trong mảng/vùng lookup_array, nếu tìm được trả về vị trí của giá trị tìm được trong mảng. INDEX lấy một hoặc mảng giá trị tại dòng- row_num, cột- column_num trong mảng array. Lưu ý: bảng map dữ liệu cần phải distinct vì excel sẽ không thể tìm kiếm hết các trường hợp bị trùng Hlookup là ngược lại của Vlookup là tìm kiếm theo cột ( thường ít dùng trong dữ liệu ) Việc tìm kiếm sẽ dễ bị rỗng nên dùng thêm hàm iferror và ifna Tìm kiếm theo 02 cột sử dụng Index(match(A&B, cột A & cột B,0))

29. Chuyên đề 05 Visualization

30. Trình bày báo cáo Tổng hợp các loại đồ thị Các loại đồ thị thường xuyên sử dụng Các loại nên tránh numbers ~2 Group Bar chart Stacked bar Line Scatter Bar chart Line Gauge Number Waterfall Treemap Heatmap

31. Trình bày kết quả Bố cục của một đồ thị Cửa hàng A sụt giảm doanh thu có thể do việc thay đổi quản lý ở năm thứ 2 2% Mỗi năm Ở năm thứ hai, doanh thu cửa hàng A bắt đầu tụt giảm so với xu hướng chung Nguồn: tổng hợp từ báo cáo tài chính hàng năm Doanh thu qua các năm 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 Title: là câu khẳng định/ nghi vấn (trong 01 câu ngắn gọn nhất) để nói về key message của story Subtitle: mô tả về cái biểu đồ thể hiện Axis: phải có tên, đơn vị (có thể bỏ đi) Chart: cần phải chọn đúng loại chart Refference: có thể là line hoặc band Label: có thể bỏ đi hoặc không cần hiển thị hết Annotation: giải thích các điểm trên hình Gridline: để mờ (khi không có label) hoặc bỏ đi 10 11 Background: nên để màu nhạt hoặc trắng Source: khi số liệu do bên khác tổng hợp Legend: nên để bên phải hoặc phía dưới đồ thị Callouts: bình luận tác giả như giải thích, khuyến nghị, hoặc so sánh thêm với các indicators thị trường, năm ngoái,...

32. Trình bày báo cáo Lưu ý khi sử dụng đồ thị Lỗi không xuất phát từ 0 Lỗi resize axis

33. Trình bày báo cáo Lưu ý khi sử dụng đồ thị Kích thước chiều rộng của cột nên lớn hơn khoảng trống hai cột một cách vừa đủ

34. Trình bày báo cáo Lưu ý khi sử dụng đồ thị Lưu ý về picture chart, treemap và pie/donut chart

35. Trình bày báo cáo Lưu ý khi sử dụng đồ thị Hướng dẫn cách tạo combo chart

36. Trình bày báo cáo Bố cục kể chuyện Các sắc thái khi trình bày dữ liệu phổ biến Underdog/Betrayal: mô tả/ giải thích sự biến động tốt lên hoặc xấu đi Victory/ tragedy: nhấn mạnh sự thành công hoặc thất bại Redemption: so sánh để làm nổi bật hàm ý cần diễn đạt (tốt/xấu) Thị phần nhà mạng Viettel triệu người Trung bình 01 ngày người chết 41.3 ~7,800 Ở năm thứ hai, doanh thu cửa hàng A bắt đầu tụt giảm so với xu hướng chung

37. Chuyên đề 06 Pivot table

38. Chuyên đề 06 Pivot table Tạo slicer Tạo timeline Tạo filter Tạo calculated field (vào list formulas để hiển thị công thức đã tạo) Ẩn hiện subtotal/grand total Tùy chỉnh giá trị hiển thị : Summarize values by Show values as

39. Chuyên đề 06 Pivot table Ứng dụng của show values as: Running total Differ from Percentage Tạo group bất kỳ Expend, collapse hiển thị group

40. Chuyên đề 06 Pivot chart Cần tùy chỉnh chart ở hai điểm : + các chart elements ( cấu phần chart có thể bật hoặc tắt ) + bật format chart area để tùy chỉnh về màu sắc , vị trí , kích thước label.

41. Chuyên đề 07 Đọc dữ liệu vào excel

42. Chuyên đề 07 Đọc file và tách cột Khi đọc file csv, txt nên đọc qua import data from text + Origin chọn đúng Encode của file thường là UTF8 sẽ không bị lỗi Tiếng Việt khi vào excel + Chọn data has header + Chọn delimited là dấu , hoặc tab tùy cấu trúc file + Chọn type cho các cột ( mặc định general), trừ các cột là số điện thoại có số 0 ở đều convert về text còn không giữ nguyên Tương tự với tính năng text to columns

43. Chuyên đề 07 Power query Dùng để tạo luồng biến đổi dữ liệu tự động từ nhiều nguồn khác nhau , đầu ra có thể là dữ liệu trên bảng tính excel hoặc đổ vào power BI. Ưu điểm : Xử lý được hơn triệu dòng với tốc độ nhanh Có thể tự động hợp nhiều file dữ liệu to Có nhiều tính năng biến đổi dữ liệu như pivot, unpivot, select, union, add/drop/split column, group by, aggregate,….

44. Chuyên đề 06 Power automate desktop

45. Icons & Shapes Vector Icons Resize the Icons Edit the colors Free Google Slides Templates uses Vectorial Icons from the most popular Free Sources. All Icons are resizable. Change the icons size without losing resolution. All Icons are editable. Change the color fill and lines without affecting the shape.

46. Credits Microsoft® and PowerPoint® are trademarks or registered trademarks of Microsoft Corporation. © 2016 Google Inc, used with permission. Google and the Google logo are registered trademarks of Google Inc. Google Drive® is a registered trademark of Google Inc. Report made by Data Analytics Department – Techology Center – Viettel Digital Services The Template provides a theme with some basic colors: Fonts Color Palette Trademarks The fonts used in this template are Montserrat family Title: Montserrat Semibold – size 28 Sub title: Montserrat Medium – size 18 Body: Montserrat – size 9, 12, 14 Edit character spacing – condensed 0.4pt to loose space Can use transperent or gradient to make various colors

47. 47