Giáo trình học tập Chuyên đề: Xử lý bảng tính Excel nâng cao
TRUNG TÂM TIN HỌC SAO VIỆT BIÊN HÒA
Địa chỉ: 90/47 Đồng khởi, Tân Phong, Biên Hòa
Hotline: 0812 114 345
----------
GIÁO TRÌNH HỌC TẬP
CHUYÊN ĐỀ: XỬ LÝ BẢNG TÍNH
EXCEL NÂNG CAO
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
MỤC LỤC
1.1. Chia Sẻ Và Bảo Quản Tài Liệu........................................................................1
1.1.1. Thiết lập và lựa chọn hiệu chỉnh dữ liệu.....................................................1
1.1.2. Thiết lập bảo mật và chia sẻ tài liệu............................................................5
1.1.3. Chia sẻ và Bảo quản tài liệu........................................................................9
1.2. Thao Tác Với Công Thức Và Hàm ................................................................12
1.2.1. Kiểm tra công thức....................................................................................12
1.2.3. Thao tác lựa chọn cho công thức ..............................................................33
1.2.4. Công thức thống kê ...................................................................................34
1.2.5. Công thức mảng ........................................................................................39
1.2.6. Sử dụng các hàm xây dựng sẵn.................................................................44
1.2.7. Các hàm về chuỗi......................................................................................44
1.2.10. Các hàm thông tin (ISfunction) .............................................................51
Các lệnh xử lý dữ liệu............................................................................52
1.3. Trình diễn dữ liệu ...........................................................................................56
1.3.1. Thao tác với PivotTables...........................................................................57
1.3.3. Thao tác với PivotCharts...........................................................................60
1.3.4. Biểu đồ nâng cao.......................................................................................62
1.3.5. Tính năng Sparkline ..................................................................................67
1.3.7. Sử dụng dữ liệu liên kết ngoài ..................................................................73
1.4. Làm Việc Với Macros Và Forms....................................................................80
1.4.1. Tạo và thực hiện macros ...........................................................................80
1.4.2. Thao tác với form controls........................................................................88
0812 114 345 | tinhocsaoviet.com
Trang 2
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
BÀI TẬP EXCEL 1 .................................................................................................100
BÀI TẬP EXCEL 2 .................................................................................................101
BÀI TẬP EXCEL 3 .................................................................................................102
BÀI TẬP EXCEL 4 .................................................................................................103
BÀI TẬP EXCEL 5 .................................................................................................104
BÀI TẬP EXCEL 6 .................................................................................................106
BÀI TẬP EXCEL 7 .................................................................................................107
BÀI TẬP EXCEL 8 .................................................................................................108
BÀI TẬP EXCEL 9 .................................................................................................109
BÀI TẬP EXCEL 10 ...............................................................................................110
BÀI TẬP EXCEL 11 ...............................................................................................112
BÀI TẬP EXCEL 12 ...............................................................................................113
BÀI TẬP EXCEL 13 ...............................................................................................114
BÀI TẬP EXCEL 14 ...............................................................................................116
BÀI TẬP EXCEL 15 ...............................................................................................118
BÀI TẬP EXCEL 16 ...............................................................................................119
0812 114 345 | tinhocsaoviet.com
Trang 3
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
MICROSOFT EXCEL NÂNG CAO
Trong phần này, chúng ta sẽ xây dựng những kỹ năng cần thiết để tạo, chỉnh
sửa, định dạng bảng tính trong Microsoft ExceL. Chúng ta sẽ tìm hiểu sâu thêm về các
kỹ năng để có thể được chứng nhận là một “chuyên gia Microsoft Excel”.
Chia sẻ và bảo quản tài liệu
Áp dụng công thức và hàm
Trình bày dữ liệu trực quan
Làm việc với macro và các hình thức
1.1. Chia Sẻ Và Bảo Quản Tài Liệu
Chúng ta cần thiết lập một bảng tính được sử dụng bởi nhiều người riêng rẽ hay
nhóm người dùng, bằng cách tạo ra một bảng tính mẫu, hay chúng ta cần bảo vệ cấu
trúc của một bảng tính, cấm sự ngăn chặn của người dùng chèn hoặc xóa .
Đối với một số bài tập nhóm thì chỉ có những thành viên trong nhóm mới có thể
truy cập hay thiết lập riêng chỉ cho một số người có thể truy cập và cập nhật còn
những người khác chỉ có thể đọc.
Thiết lập và lựa chọn hiệu chỉnh dữ liệu
Thiết lập bảo mật và chia sẻ tài liệu
Bảo quản tài liệu được chia sẻ
1.1.1. Thiết lập và lựa chọn hiệu chỉnh dữ liệu
Trong phần này chúng ta sẽ thiết lập một bảng tính như một bảng mẫu,
thiết lập các thuộc tính, và cách để nhập và xuất dữ liệu sử dụng XML và bản
đồ XML.
Lưu một bảng tính mẫu
Một bảng tính mẫu là một mẫu bảng tính mới trên một tập tin với tập hợp tiêu
đề các cột và hàng , các công thức, định dạng và các thành phần khác đã được đặt ra.
Bước đầu tiên trong việc thiết kế một bảng tính mẫu là ta thiết lập một bảng tính
với những công thức, định dạng, và các yếu tố khác mà ta cần.
Để ngăn chặn sự thay đổi mẫu bảng tính mà ta tạo ra, ta có thể tạo mật khẩu cho
mẫu. Ta cũng có thể tạo mật khẩu cho đọc và truy cập vào các tập tin và mật khẩu để
người dùng phải nhập vào để có thể thay đổi mẫu.
Mặc định khi lưu các bảng tính mẫu (template ) thì Excel lưu trong thư mục:
Users / UserName / AppData / Roaming / Microsoft /Template. Khi ta lưu mẫu ở vị trí
này thì Excel sẽ hiển thị nó trong các mẫu hộp thoại khi người dùng chọn mẫu hoặc ta
có thể lưu mẫu bảng tính trên mạng chia sẻ, nơi mà nhóm người dùng có quyền truy cập.
Các bước lưu bảng tính mẫu
0812 114 345 | tinhocsaoviet.com
Trang 1
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Bước 1. Chọn File Save As
Bước 2. Trong hộp thoại Save As, chọn loại ( Save as type ) Excel
Template (*.xltx). Nếu tập tin ta đang lưu như là một mẫu bao gồm các Macro,
ta cần phải chọn Excel Macro – Enable Template (*.xltm). Nếu template ta cần
để sử dụng với các phiên bản của Excel thì chọn: Excel 97-2003 Template (*
.xlt).
Bước 3. Chọn Tools General Options
0812 114 345 | tinhocsaoviet.com
Trang 2
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Bước 4. Trong hộp thoại Options nhập mật khẩu để mở và một mật
khẩu riêng để kiểm soát việc sửa đổi.
Bước 5. Sau đó chọn Ok Chọn Save As trong hộp thoại Save As.
Thiết lập thuộc tính bảng tính
Khi ta tạo ra, nhập liệu, và chỉnh sửa bảng tính, Excel đều lưu lại các
thuộc tính đó: kích thước tập tin, thời gian mà bảng tính được tạo ra và sửa đổi lần
cuối cùng, cũng như tên tác giả của bảng tính. Thuộc tính thông thường là thuộc
tính chỉ đọc ( Read Only). Các thuộc tính khác, bao gồm các thuộc tính nâng cao và
các tùy chọn mà ta định nghĩa, nó có thể được thiết lập và chỉnh sửa bởi người
dùng.
0812 114 345 | tinhocsaoviet.com
Trang 3
Trung Tâm Tin Học Sao Việt
Chúng ta có thể làm quen với các thuộc tính cơ bản mà Excel cung
cấp. Vào tab File / Info:
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Để thiết lập thuộc tính nâng cao cho bảng tính ta chọn Properties
Advanced Properties :
Sau đó ta thiết lập các thuộc tính trong hộp thoại Advanced Properties:
0812 114 345 | tinhocsaoviet.com
Trang 4
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Các Tab General, Statistics, và Contents hiển thị thông tin của bảng tính,
một số thông tin đã hiển thị trong Info. Tab Summary, chúng ta có thể thiết lập Tiêu
đề (Title) và Môn học ( Subject ). Tab Custom hiển thị danh sách các thuộc tính nâng
cao : Check By, Client, Project, và Typist. Ta có thể để dạng mặc định hoạc có thể tự
thiết lập theo danh sách có sẵn. Sau khi đã chọn ta Click Add. Để đổi tên, loại, hay
giá trị của thuộc tính Click chọn Modify.
1.1.2. Thiết lập bảo mật và chia sẻ tài liệu
Trong một số trường hợp chúng ta có thể chia sẻ bảng tính và cho phép bất kỳ
người sử dụng nào làm việc với nó, chỉnh sửa nó theo ý thích : thay đổi định dạng, thêm
hoặc xóa dữ liệu, chèn bảng,…Nhưng đa số chúng ta muốn bảo vệ bảng tính của mình,
nhất là các bảng tính có tính chất quan trọng. Vì vậy để bảo vệ bảng tính ta thực hiện:
- Cách 1: Tab File Info Protect Workbook
0812 114 345 | tinhocsaoviet.com
Trang 5
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
- Cách 2: Vào Tab Review group Changes
Protecting Workbooks and Worksheets
Chúng ta có thể thiết lập bảo vệ bảng tính và một số Sheet quan trong
bằng cách:
Bảo vệ cấu trúc bảng tính, bao gồm các worksheets, kích thước, vị
trí. Khi một cấu trúc bảng tính được bảo vệ ( Protect Structure ) thì người sử dụng
không thể chèn, xóa, hoặc đổi tên, hoặc hiển thị các worksheet đẫ bị ẩn. Khi một cửa
sổ bảng tính được bảo vệ ( Protect Windows ) thì người sử dụng không thể thay đổi
kích thước hoặc vị trí của cửa sổ.
0812 114 345 | tinhocsaoviet.com
Trang 6
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Bảo vệ Worksheet : trong một bảng tính có nhiều worksheet, nhưng
ta chỉ cần bảo mật một vài sheet cần thiết thì ta chọn Protect Worksheet . Trong hộp
thoại Protect Sheet nhập mật khẩu, và lựa chọn một số thuộc tính cho phép người người
sử dụng trong vùng “ Allow all users of this worksheet to:”
Ta cũng có thể thiết lập một khu vực trong worksheet được bảo vệ
cho phép người sử dụng có thể chỉnh sửa bằng chức năng “ Allow User to Edit
Ranges” trong Maintab Review group Changes
- Trong hộp thoại Allow User to Edit Ranges chọn New :
0812 114 345 | tinhocsaoviet.com
Trang 7
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Thiết lập mật khẩu cho tập tin
Bước 1.
Mở tập tin muốn tạo mật khẩu Tab File Info
Protect Workbook Encrypt with Password
Bước 2.
Nhập mật khẩu
0812 114 345 | tinhocsaoviet.com
Trang 8
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Bước 3.
Nhập lại mật khẩu vừa đặt
Bước 4.
Click Ok hoàn thành
1.1.3. Chia sẻ và Bảo quản tài liệu
Chia sẻ tài liệu
Bước 1. Chọn Tab Review Group Changes Share
Wokbook
Bước 2. Trong cửa sổ Share Workbook Tab Editing Allow
changes by more than one user at the same time. This also allows workbook
merging
0812 114 345 | tinhocsaoviet.com
Trang 9
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Bước 3. Tab Advanced cho phép bạn thiết lập một số tính năng cho
tập tin được chia sẻ.
Bước 4. Click Ok để hoàn thành.
Theo dõi sự thay đổi của tập tin
0812 114 345 | tinhocsaoviet.com
Trang 10
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Sau khi đã chia sẻ tập tin cho người khác hay nhóm cùng làm việc, để
theo dõi sự thay đổi của tập tin để xem tập tin được chỉnh sửa, thêm hay thay đổi dữ liệu
nào , ta thiết kế theo các bước:
Bước 1.
Chọn Tab Review group Changes Track Changes
Highlight Changes…
Bước 2.
Trong hộp thoại Highlight Changes lựa chọn những điều
kiện When , Who, Where thiết hợp theo yêu cầu của bạn.
Bước 3. Click Ok để hoàn thành.
Với thiết lập này thì những vị trí mà dữ liệu thay đổi sẽ được làm dấu với
Highlight.
Xem chi tiết những thay đổi của tập tin
Bước 1. Trong Tab Review group Changes Track Changes
Accept/Reject Changes
0812 114 345 | tinhocsaoviet.com
Trang 11
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Bước 2. Trong hộp thoại Select Changes to Accept or Reject bạn sẽ
lựa chọn xem chi tiết thay đổi của dữ liệu khi nào , của ai, hay vùng nào,..
Bước 3. Trong hộp thoại Accept or Reject Changes sẽ hiển thị chi
tiết các dữ liệu đã bị thay đổi và bạn có quyền chấp nhập hoặc không sự thay đổi
đó.
1.2. Thao Tác Với Công Thức Và Hàm
1.2.1. Kiểm tra công thức
Chức năng Kiểm tra công thức (formula-auditing) của Excel hoạt động
bằng cách tạo ra các tracer — là những cái mũi tên hai đầu chỉ ra các ô có liên quan
trong một công thức.
Bạn có thể sử dụng các tracer để tìm ra ba loại ô như sau:
Precedents: Đây là các ô được tham chiếu trực tiếp hoặc gián tiếp trong
một công thức. Ví dụ, ô B4 chứa công thức =B2; rồi B2 sau đó lại là
một precedent trực tiếp của B4. Bây giờ giả sử rằng ô B2 chứa công thức =A2/2;
điều này làm cho A2 trở thành một precedent trực tiếp của B2 đồng thời cũng là
một precedent gián tiếp của B4.
0812 114 345 | tinhocsaoviet.com
Trang 12
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Dependents: Đây là các ô được tham chiếu trực tiếp hoặc gián tiếp bởi
một công thức đang nằm trong một ô khác. Trong ví dụ trên đây, ô B2 là
một dependent trực tiếp của A2, và B4 là một dependent gián tiếp của A2.
Errors : Đây là những ô chứa một giá trị lỗi và đang được tham chiếu trực
tiếp hoặc gián tiếp trong một công thức (và do đó nó gây ra một lỗi tương tự trong
công thức này).
Hình 2.2.2: Minh họa một bảng tính với ba ví dụ của các mũi tên tracer
- Ô B4 chứa công thức =B2, và B2 chứa công thức =A2/2. Những cái mũi tên (màu
xanh) chỉ ra các precedent(trực tiếp và gián tiếp) của B4.
- Ô D4 chứa công thức =D2, và D2 chứa công thức =D1/0, gây ra lỗi #DIV/0!, do
đó, lỗi này cũng xuất hiện ở ô D4. Mũi tên (màu đỏ) chỉ ra nguồn gốc của lỗi.
- Ô G4 chứa công thức =Sheet2!A1. Excel hiển thị một mũi tên đứt khúc với một
cái biểu tượng bảng tính ở đuôi mũi tên khi precedent hoặc dependent nằm trên
một trang tính (worksheet) khác.
Truy tìm các Precedent của ô
Bước 1.
Chọn ô chứa công thức mà bạn muốn truy
tìm precedent của nó.
Bước 2.
Chọn Tab Formulas Group Fomula Auditing Trace
Precedents. Excel sẽ thêm một mũi tên vào mỗi cái precedent trực tiếp
0812 114 345 | tinhocsaoviet.com
Trang 13
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Bước 3.
Tiếp tục lập lại bước 2 để thêm các cấp precedent (nếu có).
Truy tìm các Dependent của ô
Bước 1. Chọn ô chứa công thức mà bạn muốn truy tìm Dependent của
nó.
Bước 2. Chọn Tab Formulas Group Fomula Auditing Trace
Dependents
0812 114 345 | tinhocsaoviet.com
Trang 14
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Bước 3. Tiếp tục lập lại bước 2 để thêm các cấp Dependent (nếu có).
Gỡ bỏ những mũi tên Tracer
Chọn Tab Formulas Group Fomula Auditing Remove
Arrows
Để gỡ bỏ các mũi tên precedent mỗi lần một cấp, chọn Remove
Precedent Arrows.
Để gỡ bỏ các mũi tên dependent mỗi lần một cấp, và chọn Remove
Dependent Arrows.
Các thông báo lỗi thường gặp
Khi Excel không tính được một công thức thì chương trình sẽ báo lỗi sai,
bắt đầu bằng dấu #, dưới đây là danh sách các thông báo lỗi thường gặp.
Thông báo
Nguyên nhân
lỗi
#DIV/0!
#N/A
Trong công thức có phép tính chia cho 0.
Công thức tham chiếu đến ô có giá trị không
tìm thấyhoặc nhập hàm thiếu đối số.
#NAME?
#NULL
#NUM!
#REF!
Trong công thức có tên hàm hoặc tên ô sai.
Xảy ra khi xác định giao giữa 2 vùng
nhưng vùng giao nhau là rỗng.
Dữ liệu số bị sai.
Xảy ra khi trong công thức có tham chiếu
đến một địa chỉ không hợp lệ.
Trong công thức có các toán hạng và
toán tử sai kiểu.
#VALUE!
Sửa chữa lỗi công thức
0812 114 345 | tinhocsaoviet.com
Trang 15
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
Nếu bạn thiếu một dấu ngoặc đơn khi nhập một công thức, hoặc nếu bạn
đặt một dấu ngoặc đơn sai vị trí, Excel thường hiển thị một hộp thoại như minh họa ở
hình bên dưới khi bạn cố xác nhận công thức. Nếu thấy công thức (do Excel gợi ý trong
hộp thoại) là đúng những gì bạn muốn, bạn nhấn Yes để Excel tự động sửa lại công thức
cho bạn; còn nếu thấy công thức đó sai, bạn nhấn No và tự sửa lại công thức.
Sử dụng chức năng kiểm tra lỗi công thức
Nếu bạn sử dụng Microsoft Word, có lẽ bạn đã quen với những đường
gợn sóng màu xanh xanh xuất hiện ở bên dưới các từ hoặc cụm từ mà chương trình
kiểm tra văn phạm (grammar checker) cho là không đúng. Grammar checker hoạt
động bằng cách sử dụng một bộ quy tắc để kiểm tra văn phạm và cú pháp. Khi bạn
nhập văn bản, grammar checker âm thầm theo dõi từng câu từng chữ của bạn, nếu
có thứ gì đó bạn nhập không đúng với những quy tắc của grammar checker, đường
gợn sóng sẽ xuất hiện để báo cho bạn biết là có vấn đề.
Excel cũng có tính năng tương tự như vậy: chức năng kiểm tra lỗi công
thức (formula error checker). Nó tương tự như grammar checker, dùng một một bộ
quy tắc để kiểm tra các phép tính và cũng hoạt động cách âm thầm khi giám sát
những công thức của bạn. Nếu nó phát hiện ra điều gì đó không ổn, nó sẽ hiển thị
một dấu hiệu báo lỗi — một cái tam giác màu xanh — ở góc trái phía trên của ô
chứa công thức.
Cách sửa chữa lỗi
Khi bạn chọn cái ô có dấu hiệu báo lỗi, Excel hiển thị một smart tag ngay
cạnh đó, và nếu bạn đặt con trỏ chuột lên trên cái biểu tượng mới xuất hiện này, một
câu thông báo miêu tả lỗi mắc phải sẽ hiện lên, như minh họa trong hình trên. Bên
góc phải của biểu tượng này còn có một nút nhấn để mở ra một danh sách những
cách xử lý lỗi cho bạn chọn:
0812 114 345 | tinhocsaoviet.com
Trang 16
Trung Tâm Tin Học Sao Việt
90/47 Đồng Khởi, Tân Phong, Biên Hòa
- Help on This Error : Tìm hiểu thông tin về lỗi qua hệ thống Help
của Excel.
- Show Calculation Steps : Chạy chức năng Evaluate
Formula (đánh giá công thức).
- Ignore Error : Bỏ qua, giữ nguyên công thức sai như vậy.
- Edit in Formula : Hiển thị công thức trong chế độ chỉnh sửa
(Edit) trên thanh công thức (formula bar). Chẳng qua là để cho bạn
tự sửa lại công thức.
- Error-Checking : Hiện các tùy chọn của chức năng Error
Checking từ hộp thoại Option để bạn chọn.
Thiết lập các tùy chọn cho việc kiểm tra lỗi
Cũng giống như việc kiểm tra ngữ pháp trong Word, chức
năng kiểm tra lỗi công thức (Formula Error Checker) cũng có một số những tùy
chọn để quy định cách nó làm việc và sẽ đánh dấu những lỗi nào. Để xem những
tùy chọn này, bạn có hai cách:
- Chọn Office, Excel Options để hiển thị hộp thoại Excel
Options, và chọn Formulas
- Chọn Error-Checking Options trong danh sách xổ xuống
của cái biểu tượng báo lỗi (như đã nói trong bài trước).
Cả hai cách đều mở ra những tùy chọn cho Error
Checking và Error Checking Rules như minh họa:
0812 114 345 | tinhocsaoviet.com
Trang 17
Tải về để xem bản đầy đủ
123 trang
13/04/2022
19580
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình học tập Chuyên đề: Xử lý bảng tính Excel nâng cao", để tải tài liệu gốc về máy hãy click vào nút Download ở trên
File đính kèm:
- giao_trinh_hoc_tap_chuyen_de_xu_ly_bang_tinh_excel_nang_cao.pdf
