Giáo trình Excel ứng dụng (Nghề: Tin học ứng dụng - Cao đẳng) - Trường Cao đẳng Bách khoa Nam Sài Gòn (2021)

Chia sẻ: _ _ | Ngày: | Loại File: PDF | Số trang:41

Thêm vào BST

Báo xấu

12
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Giáo trình "Excel ứng dụng (Nghề: Tin học ứng dụng - Cao đẳng)" được biên soạn nhằm giúp sinh viên nắm được các nội dung về: Các tính năng cao cấp của Excel; Nhóm hàm ứng dụng; Cơ sở dữ liệu; Nhập và tạo mô hình dữ liệu trong Excel;... Mời các bạn cùng tham khảo chi tiết nội dung giáo trình!

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình Excel ứng dụng (Nghề: Tin học ứng dụng - Cao đẳng) - Trường Cao đẳng Bách khoa Nam Sài Gòn (2021)

UỶ BAN NHÂN DÂN BỐ BẢN QUYỀN CHÍ MINH TUYÊN THÀNH PHỐ HỒ TRƯỜNG CAO ĐẲNG trình nên các nguồn thông SÀI GÒN Tài liệu này thuộc loại sách giáo BÁCH KHOA NAMtin có thể được phép dùng nguyên bản hoặc trích dùng cho các mục đích về đào tạo và tham khảo. Mọi mục đích khác mang tính lệch lạc hoặc sử dụng với mục đích kinh doanh thiếu lành mạnh sẽ bị nghiêm cấm. GIÁO TRÌNH MÔN HỌC/MÔ ĐUN: EXCEL ỨNG DỤNG NGÀNH/NGHỀ : TIN HỌC ỨNG DỤNG TRÌNH ĐỘ: CAO ĐẲNG Ban hành kèm theo Quyết định số: /QĐ_.....ngày……tháng…..năm …………………của………………………………. Tp.Hồ Chí Minh, năm…….
MỤC LỤC CHƯƠNG 1: CÁC TÍNH NĂNG CAO CẤP CỦA EXCEL ....................................................................3 1.1. Đặt mật khẩu cho tập bảng tính ......................................................................................................3 1.2. Làm chủ những tính năng lập dàn của Excel (Outlining) ...................................................................5 1.3. Thiết lập giá trị giới hạn cho ô ............................................................................................................5 CHƯƠNG 2: NHÓM HÀM ỨNG DỤNG ................................................................................................10 2.1. Hàm thống kê một điều kiện .............................................................................................................10 2.2. Hàm thống kê nhiều điều kiện...........................................................................................................12 2.3. Hàm tham chiếu ................................................................................................................................14 2.4. Hàm Match ........................................................................................................................................16 2.5. Hàm INDEX......................................................................................................................................20 CHƯƠNG 3: CƠ SỞ DỮ LIỆU ................................................................................................................22 3.1. Các khái niệm cơ bản ........................................................................................................................22 3.2. Data Sort............................................................................................................................................22 3.3. Lọc dữ liệu (Auto Filter và Advanced Filter)...................................................................................23 3.4. Tính năng Subtotals...........................................................................................................................25 CHƯƠNG 4: NHẬP VÀ TẠO MÔ HÌNH DỮ LIỆU TRONG EXCEL ...............................................28 4.1. Các kiểu nhập dữ liệu trong excel( từ csdl, từ bảng tính, sao chép và dán) ......................................28 4.2. Tạo mối quan hệ giữa dữ liệu đã nhập (Pivot table) .........................................................................28 4.3. Extend data model relationships using excel, Power Pivot and Dax. ...............................................30 CHƯƠNG 5: LIÊN KẾT BẢNG TÍNH ...................................................................................................33 5.1. Khái niệm về liên kết. .......................................................................................................................33 5.2. Kỹ thuật tạo liên kết (Importrange, Query) .......................................................................................33 CHƯƠNG 6: MACRO VÀ VBA ..............................................................................................................36 6.1. Macro ................................................................................................................................................36 6.2. VBA căn bản .....................................................................................................................................40
CHƯƠNG 1: CÁC TÍNH NĂNG CAO CẤP CỦA EXCEL 1.1. Đặt mật khẩu cho tập bảng tính 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: o Cách 1: Tab File-> Info-> Protect Workbook o Cách 2: Vào Tab Review-> 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 trọng 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ổ. 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 sử dụng trong vùng ―Allow all users of this worksheet to: o 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 tab Review-> Changes Trong hộp thoại Allow User to Edit Ranges chọn New Thiết lập mật khẩu cho tập tin o Bước 1. Mở tập tin muốn tạo mật khẩu. Vào Tab File-> Info-> Protect Workbook- > Encrypt with Password
o Bước 2. Nhập mật khẩu o Bước 3. Nhập lại mật khẩu vừa đặt o Bước 4. Click Ok hoàn thành 1.2. Làm chủ những tính năng lập dàn của Excel (Outlining) Thiết lập một dãy dàn Bước 1: Chọn một dãy các hàng hay cột (không được chọn ô) Bước 2: Vào Data/Group and Outline/Group Khi đó ta sẽ thấy bên trái màn hình xuất hiện nút có dấu (-), nếu bấm chuột vào nút này thì các hàng hoặc cột được chọn ở bước 1 sẽ được ẩn đi. Nếu muốn các hàng, cột xuất hiện trả lại thì bấm vào nút (+). Xoá bỏ dàn Để xoá các dàn riêng rẽ: - Mở dàn cần xoá (bấm vào dấu (+) để mở dàn trong trường hợp dàn chưa mở) - Chọn hàng hay cột muốn loại bỏ dàn - Vào Data/Group and Outline/Ungroup Để xoá tất cả các dàn: - Không chọn hàng hay cột nào cả - Data/Group and Outline/Clear Outline 1.3. Thiết lập giá trị giới hạn cho ô Khi xây dựng bất cứ bảng tính nào đó phục vụ công việc mình, chắc chắn bạn sẽ cần những vùng nhập dữ liệu theo yêu cầu nhất định nào đó. Dữ liệu đó có thể được giới hạn trong một phạm vi nào đó, có thể là số nguyên, số thập phân, ngày, giờ, dữ liệu trong danh sách sẵn có hoặc chuỗi có độ dài nhất định. Khi đó chức năng Data validation sẽ giúp chúng ta nhập liệu một cách chính xác theo yêu cầu, hạn chế sai sót tối thiểu. Để thiết lập việc nhập liệu theo điều kiện, ta thực hiện: o Bước 1. Chọn vùng muốn thiết lập điều kiện.
o Bước 2. Vào Tab Data-> Group Data Tools-> Data Validation o Bước 3. Trong hộp thoại Data Validation có ba Tab Setings, Input Message, Error Alert. Thẻ Settings Cửa sổ Settings cho phép thiết lập cài đặt về điều kiện nhập liệu trong Validation criteria. Tuỳ vào đối tượng kiểm soát mà ta chọn trong danh sách bên dưới Allow. Mặc định ban đầu cho phép nhập bất cứ kiểu dữ liệu nào trong ô (Any value). Để thay đổi theo ý muốn, đầu tiên ta chọn vùng dữ liệu cần thiết lập chức năng Validation. Trong danh sách thả xuống của Validation criteria, có các lựa chọn Whole number, Decimal, List, Date, Time, Text lenght, Custom. o Whole number: Chức năng này chỉ cho phép nhập liệu là số nguyên. Nếu nhập số thập phân, chuỗi,... sẽ bị báo lỗi. Chức năng này hữu ích khi dữ liệu nhập là tuổi, số lượng mặt hàng, số sản phẩm, điểm thi,... Khi chọn Whole number, chức năng Data xuất hiện cho phép khống chế phạm vi giá trị nhập. Các toán tử: - Between: giá trị trong ô nằm trong đoạn từ a đến b (bao gồm 2 cận trên và dưới a, b) - Not between: giá trị trong ô không nằm trong đoạn [a, b]
- Equal to: giá trị trong ô bằng (=) với giá trị so sánh - Not equal to: giá trị trong ô không bằng (≠) giá trị so sánh - Greater than: giá trị trong ô lớn hơn (>) giá trị so sánh - Less than: giá trị trong ô nhỏ hơn (
Chức năng Input Message cho phép hiển thị thông tin nhập liệu khi di chuyển chuột vào ô đó, từ đó định hướng cho công việc nhập liệu. Gồm có các thông số: o Show input message when cell is selected: Bật (tắt) chế độ hiển thị thông báo khi ô được chọn. Trường hợp này chọn Bật. o Title: Nội dung tiêu đề hiển thị, dùng kiểu gõ Unicode. o Input message: Nội dung thông báo, dùng kiểu gõ Unicode Error Alert Khi ô đã được đặt chế độ Data validation, nếu nhập liệu không đúng quy định sẽ bị thông báo Ta có thể thay đổi nội dung thông báo mặc định đó theo các hoàn cảnh khác nhau và hiển thị nội dung thông báo đó bằng tiếng Việt cho dễ hiểu và sửa đổi.
o Show error alert after invalid data is selected: Bật (tắt) chế độ hiển thị cảnh báo sau khi dữ liệu được nhập vào ô. Trường hợp này chọn Bật. o Style: Kiểu cảnh báo, gồm Stop (dừng lại), Warrning (cảnh báo), Information (thông tin). Tuỳ mức độ cảnh báo mà có cách xử lý phù hợp. - Stop: Thông báo lỗi nhập liệu nghiêm trọng, Excel không chấp nhận giá trị nhập liệu này và yêu cầu phải nhập đúng mới được chấp nhận. - Warning: Thông báo lỗi nhập liệu mang tính cảnh báo, tùy trường hợp có thể chấp nhập hoặc không chấp nhận giá trị nhập liệu. - Information: Thông báo lỗi nhập liệu mang tính thông tin, bạn có thể bỏ qua trường hợp nhập liệu không đúng quy định. o Title: Nội dung tiêu đề thông báo, dùng kiểu gõ Unicode. o Error message: Nội dung thông báo (chú ý nội dung theo những hoàn cảnh cụ thể), dùng kiểu gõ Unicode. Ví dụ: Thiết lập giới hạn cho cột điểm số là số nguyên từ 1 đến 10. Chọn vùng nhập điểm số Vào Data/Data Tools/Data Validation, thiết lập các thông số như sau:
CHƯƠNG 2: NHÓM HÀM ỨNG DỤNG 2.1. Hàm thống kê một điều kiện Hàm COUNTIF Công dụng: tính số ô trong phạm vi đáp ứng các tiêu chí được chỉ định. Cú pháp: =COUNTIF(range, criteria) Trong đó: Range: Các ô cần tính Criteria: Điều kiện Bài tập áp dụng: Cho bảng Dữ liệu sau. Hãy thống kê số học sinh trong lớp theo bảng Xếp loại học sinh. Áp dụng hàm Countif vào trường hợp này, ta có Kết quả tại ô C17 như sau: C17 = Countif (E5:E14,”Giỏi”) Trong đó: E5:E14 là vùng dữ liệu, chứa dữ liệu để đếm. “Giỏi” là điều kiện (Criterial) Hàm Countif sẽ đếm tất cả những ô thỏa mãn điều kiện là “Giỏi” trong vùng dữ liệu từ E5 đến E14 ngược lại sẽ không tính. Tương tự cho các ô còn lại với trường hợp đếm số học sinh Khá, Trung Bình, Yếu. C18 = COUNTIF(E5:E14,”Khá”) C19 = COUNTIF(E5:E14,”Trung Bình”) C20 = COUNTIF(E5:E14,”Yếu”)
Hàm SUMIF Công dụng: dùng để tính tổng các ô hoặc các vùng có giá trị thỏa mãn điều kiện được đặt ra. Cú pháp: =SUMIF(Range, Criteria, Sum_range) Trong đó: Range: Các ô chứa điều kiện Criteria: Điều kiện Sum_range: Các ô cần tính tổng Bài tập áp dụng: Cho bảng tính như mẫu bên dưới. Hãy tính tổng lương cho Phòng Ban là "Kế hoạch" tại ô E15.
Áp dụng hàm Sumif để tính tổng lương cho phòng ban Kế hoạch tại ô E15 ta có. E15=SUMIF(D5:D12,”Kế hoạch”,E5:E12) Trong công thức này: D5:D12– Vùng chứa dữ liệu cần so sánh với điều kiện: “Kế hoạch” “Kế hoạch” – Điều kiện (Criteria) E5:E12 – Vùng cần tính tổng Tính trung bình theo điều kiện: Averageif Cú pháp:= AVERAGEIF (Vùng điều kiện, điều kiện, vùng cần tính) 2.2. Hàm thống kê nhiều điều kiện Hàm Sumifs: - Cú pháp: =SUMIFS(vùng tính tổng, vùng điều kiện 1, điều kiện1, vùng điều kiện 2, điều kiện2,…) - Công dụng: hàm cho ta giá trị tổng của các bản ghi thoả mãn nhiều điều kiện Ví dụ: Tính tổng tiền theo loại phòng và có số người lớn hơn 2.
Hàm Countifs: 1. Chức năng của hàm Countif Hàm Countif dùng để đếm các ô trong vùng dữ liệu theo nhiều điều kiện. 2. Công thức hàm Countifs =COUNTIFS(Vùng điều kiện 1, điều kiện 1, Vùng điều kiện 2, điều kiện 2, ….) Ví dụ: Tính số hàng tồn kho (giá trị cột B lớn hơn 0) nhưng chưa bán được (giá trị cột C bằng 0). = COUNTIFS (B2: B7, “> 0”, C2: C7, “= 0”) Bài tập áp dụng: Cho bảng điểm một số học sinh, hãy đếm số học sinh đạt điểm 9 và 8 ở cả 3 môn Hàm Averageifs: Cú pháp: = AVERAGEIFS( Vùng cần tính,vùng điều kiện 1, điều kiện 1,vùng điều kiện 2, điều kiện 2, ….) Các hàm cơ sở dữ liệu Các hàm cơ sở dữ liệu mang tính chất thống kê những mẫu tin trong CSDL có trường thỏa điều kiện của vùng tiêu chuẩn đã đƣợc thiết lập trước. Cú pháp chung: =Tên hàm(database, field, criteria)
database: địa chỉ vùng CSDL (Chọn địa chỉ tuyệt đối để sao chép). field: cột cần tính toán, field có thể là tên trường, địa chỉ của ô tên field hoặc số thứ tự của trường đó (cột thứ nhất của vùng CSDL đã chọn tính là 1 và tăng dần sang trái). criteria: địa chỉ vùng điều kiện. DAVERAGE(database, field, criteria) Tính trung bình cộng các giá trị trong cột field của các mẫu tin thỏa điều kiện criteria. DMAX (database, field, criteria) Tìm trị lớn nhất trong cột field của các mẫu tin thỏa điều kiện criteria DMIN (database, field, criteria) Tìm trị nhỏ nhất trong cột field của các mẫu tin thỏa điều kiện criteria DCOUNT(database, field, criteria) Đếm các ô kiểu số trong cột field của các mẫu tin thỏa điều kiện criteria DCOUNTA(database, field, criteria) Đếm các ô khác rỗng trong cột field của các mẫu tin thỏa điều kiện criteria 2.3. Hàm tham chiếu Hàm VLOOKUP Công dụng: dùng để tìm kiếm giá trị tương ứng trên ô cột đầu tiên của bảng tham chiếu và cho kết quả tương ứng trong cột chỉ định Cú Pháp: =VLOOKUP(Lookup_value,Table_ array,Col_index_Num,Range_lookup) Trong đó: Lookup_value: giá trị đối chiếu với cột đầu tiên của bảng tham chiếu để lấy được giá trị cần tìm. Table_ array: địa chỉ tuyệt đối của bảng tham chiếu, không lấy dòng tiêu đề. Col_index_Num: Số thứ tự cột chứa giá trị cần lấy của bảng tham chiếu (tính theo thứ tự từ trái qua phải và bắt đầu từ 1).
Range_lookup: Cách tìm kiếm trên bảng tham chiếu. + 0: Cột đầu tiên của bảng tham chiếu chưa sắp xếp. + 1: Cột đầu tiên của bảng tham chiếu đã sắp xếp theo chiều tăng dần (ngầm định). Hàm HLOOKUP Công dụng: dùng để tìm kiếm một giá trị trên dòng đầu tiên của bảng tham chiếu và cho kết quả tương ứng trong dòng chỉ định. Cú pháp: =HLOOKUP(Lookup_value,Table_ array,Row_index_Num,Range_lookup) Trong đó: Lookup_value: giá trị đối chiếu với dòng đầu tiên của bảng tham chiếu để lấy được giá trị cần tìm. Table_ array: là địa chỉ tuyệt đối của bảng tham chiếu, không lấy cột tiêu đề. Row_index_Num: Số thứ tự dòng chứa giá trị cần lấy của bảng tham chiếu (tính theo thứ tự từ trên xuống dưới và bắt đầu từ số 1).
Range_lookup: Cách tìm kiếm trên bảng tham chiếu. + 0: Dòng đầu tiên của bảng tham chiếu chưa sắp xếp. + 1: dòng đầu tiên của bảng tham chiếu đã sắp xếp theo chiều tăng dần (ngầm định). 2.4. Hàm Match Cú pháp: =MATCH(, , [ 1 trong 3 số : 1 hoặc 0 hoặc -1]) Trong công thức trên: (tham số bắt buộc): là giá trị bạn muốn tìm vị trí tương đối trong mảng, có thể thuộc loại dữ liệu số, text, logic hoặc là 1 tham chiếu đến 1 địa chỉ ô (tham số bắt buộc): vùng muốn tìm giá trị [ 1 trong 3 số: 1 hoặc 0 hoặc -1] (Tham số không bắt buộc): định nghĩa cách tìm kiếm o Nếu điền số 1 hoặc bỏ trống: hàm Match sẽ tìm giá trị lớn nhất trong vùng tìm kiếm nhưng nhỏ hơn hoặc bằng giá trị đang được tìm kiếm. Chú ý: vùng tìm kiếm cần được sắp xếp theo chiều tăng dần, nghĩa là, từ giá trị nhỏ nhất đến giá trị lớn nhất hoặc từ A tới Z
o Nếu điền số 0: hàm Match sẽ tìm chính xác giá trị cần tìm trong vùng và trả lại vị trí đầu tiên mà nó tìm thấy o Nếu điền số -1: hàm Match sẽ tìm giá trị nhỏ nhất trong vùng tìm kiếm nhưng lơn hơn hoặc bằng giá trị đang được tìm kiếm. Chú ý: vùng tìm kiếm cần được sắp xếp theo thứ tự giảm dần, nghĩa là, từ giá trị lớn nhất đến giá trị nhỏ nhất hoặc từ Z tới A Ví dụ: Một số lưu ý khi sử dụng hàm Match: - Hàm Match trả về vị trí tương đối của giá trị cần tìm, không trả về giá trị đó - Hàm Match không phân biệt chữ hoa, chữ thường khi làm việc với dữ liệu chữ - Nếu vùng tìm kiếm có một vài giá trị trùng nhau, hàm Match sẽ trả về giá trị đầu tiên mà nó gặp - Nếu giá trị cần tìm không có trong vùng tìm kiếm, lỗi #N/A sẽ được trả về. Dấu hỏi chấm (?) – đại diện cho 1 kí tự bất kì Dấu sao (*) – đại diện cho bất kì số lượng kí tự nào. (Chỉ sử dụng với Match khi tham số thứ 3 của hàm Match có giá trị bằng 0) Ví dụ: Tìm vị trí tương đối của tên người bắt đầu bằng “Car”
Hàm Match có phân biệt chữ hoa, chữ thường Hàm Match khi tìm kiếm sẽ không phân biệt được chữ hoa và chữ thường. Để giúp hàm Match phân biệt chữ hoa, chữ thường, ta sẽ kết hợp hàm Match với hàm EXACT như sau: =MATCH( TRUE, EXACT ( , < giá trị tìm kiếm >), 0) o Hàm EXACT sẽ giúp chúng ta so sánh giá trị cần tìm kiếm với mỗi giá trị trong vùng tìm kiếm. Nếu ô được so sánh trùng khít về nội dung 100% với ô đem đi so sánh thì hàm EXACT sẽ trả về giá trị TRUE, nếu không hàm EXACT sẽ trả về giá trị FALSE o Sau đó hàm Match sẽ tìm kiếm giá trị TRUE được trả về từ hàm EXACT này, kết quả là chúng ta có thể sử dụng hàm MATCH có phân biệt được chữ hoa và chữ thường. Chú ý: đây là 1 công thức mảng, sau khi nhập xong công thức, bạn sử dụng phím tắt CTRL + SHIFT + ENTER để nhập, bạn không cần thêm dấu ngoặc nhọn vào công thức
So sánh 2 cột tìm sự khác biệt Một trong những công việc mất rất nhiều thời gian đó là so sánh 2 cột trong Excel để biết thành phần nào ở cột 1 chưa có trong cột 2 hoặc ngược lại. Có nhiều cách để giải quyết vấn đề này, trong đó có cách sử dụng Match kết hợp với hàm ISNA: Cú pháp: =IF(ISNA(MATCH(< giá trị đầu tiên trong cột 1 >, < cột 2 >, 0)), “Không có trong cột 1”, “” ) Trong đó: o Hàm Match sẽ tìm từng giá trị ở cột 1 trong cột 2, nếu giá trị này được tìm thấy, hàm Match sẽ trả về vị trí tương đối của giá trị đó, nếu không tìm thấy, lỗi #N/A sẽ được trả về. o Hàm ISNA kiểm tra xem kết quả trả về từ hàm Match có phải là #N/A hay không. Nếu hàm ISNA trả về giá trị đúng, nghĩa là giá trị không được tìm thấy, điều này dẫn đến việc tham số đầu tiên của hàm IF có giá trị TRUE, do vậy kết quả của hàm IF sẽ là “Không có trong cột 1”
2.5. Hàm INDEX INDEX với mảng Dạng mảng INDEX trả về giá trị của một phần tử trong một bảng hoặc một mảng dựa trên số hàng và cột mà bạn chỉ định. Cú pháp: INDEX (array, row_num, [column_num]) o array – là một dãi các ô, có tên dải hoặc bảng. o row_num – là hàng thứ mấy trong mảng mà bạn muốn được trả về một giá trị. Nếu row_num bị bỏ qua, column_num là bắt buộc. o column_num – là cột thứ mấy để trả về một giá trị. Nếu column_num bị bỏ qua, row_num là bắt buộc. Ví dụ, công thức “= INDEX (A1: D6, 4, 3)” sẽ trả về giá trị tại giao điểm của hàng thứ 4 và cột thứ 3 trong mảng A1: D6, là giá trị trong ô C4.