giao_trinh_excel + VBA hhhhhhhhhhhhhhhhhh

linhsamye 1 views 47 slides Sep 29, 2025
Slide 1
Slide 1 of 47
Slide 1
1
Slide 2
2
Slide 3
3
Slide 4
4
Slide 5
5
Slide 6
6
Slide 7
7
Slide 8
8
Slide 9
9
Slide 10
10
Slide 11
11
Slide 12
12
Slide 13
13
Slide 14
14
Slide 15
15
Slide 16
16
Slide 17
17
Slide 18
18
Slide 19
19
Slide 20
20
Slide 21
21
Slide 22
22
Slide 23
23
Slide 24
24
Slide 25
25
Slide 26
26
Slide 27
27
Slide 28
28
Slide 29
29
Slide 30
30
Slide 31
31
Slide 32
32
Slide 33
33
Slide 34
34
Slide 35
35
Slide 36
36
Slide 37
37
Slide 38
38
Slide 39
39
Slide 40
40
Slide 41
41
Slide 42
42
Slide 43
43
Slide 44
44
Slide 45
45
Slide 46
46
Slide 47
47

About This Presentation

học excel cơ bản + VBA


Slide Content

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

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

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/

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

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

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

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)

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)

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

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

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

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

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

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

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

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

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

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

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 .

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

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

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

Chuyên đề 04 Formulas

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)

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

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

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)

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))

Chuyên đề 05 Visualization

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

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

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

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 đủ

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

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

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

Chuyên đề 06 Pivot table

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

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

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.

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

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

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,….

Chuyên đề 06 Power automate desktop

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.

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
Tags