Chng trnh bng tnh Excel 2003 Chng 1

Chương trình bảng tính Excel 2003 Chương 1: Làm quen với Excel Chương 2: Làm việc với Bảng tính Chương 3: Định dạng và In bảng tính Chương 4: Các hàm cơ bản của Excel Chương 5: Quản trị dữ liệu Chương 6: Đặt tên và sử dụng tên trong công thức Chương 7: Đồ thị Chương 8: Sử dụng Pivot. Table trong tổng hợp báo cáo dữ liệu Chương 9: Một số thủ thuật TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

Chương 1: Làm quen với Excel Bài 1: Giới thiệu tổng quan về Excel Bài 2: Một số khái niệm Bài 3: Các thao tác với tập tin TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

Bài 1: Giới thiệu tổng quan về Excel Chức năng I. Khởi động Excel II. III. IV. Giới thiệu màn hình của Excel Thoát khỏi Excel TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Chức năng Excel là phần mềm bảng tính điện tử có kích thước lớn, gồm nhiều cột (Column) và nhiều hàng (Row), lập thành những đơn thể chứa và xử lý dữ liệu gọi là ô (Cell). Các chức năng chính của Excel là: 1 - Bảng tính (Worksheet): Nhập, tính toán và phân tích dữ liệu. 2 - Đồ thị (Chart): Tạo ra các biểu đồ liên kết với số liệu của bảng tính, nhằm nhấn mạnh sự tương quan giữa các số liệu trong bảng tính với nhau. 3 - Cơ sở dữ liệu (Database): Quản trị cơ sở dữ liệu trên Excel bao gồm các công việc: Sắp xếp, trích rút dữ liệu theo các tiêu chuẩn khác nhau.

II. Khởi động Excel C 1: Kích đúp chuột vào biểu tượng Microsoft Excel trên màn hình nền (Destop). C 2: Kích chuột vào biểu tượng của Excel ( ) trên thanh Shortcut Bar ở góc trên bên phải màn hình nền. C 3: Vào Start/ Programs/ Microsoft Excel

II. Giới thiệu màn hình của Excel 1. Thanh tiêu đề (mầu xanh trên cùng): chứa tên chương trình và tên tệp 2. Thanh thực đơn (File, Edit, View, . . . ): chứa toàn bộ các lệnh của chương trình. 3. Thanh công cụ: Chứa các biểu tượng thể hiện các lệnh thường dùng. Để kích hoạt thanh công cụ: Vào View/Toolbars/ Standard 4. Thanh định dạng: chứa các biểu tượng thể hiện các lệnh định dạng thường dùng. Xem hình Để kích họat thanh định dạng: Vào View/Toolbar/Formatting 5. Thanh công thức: Dùng để xem chi tiết nội dung của một ô hiện hành Để kích hoạt thanh công thức: Vào View / Formular Bar 6. Worksheet: là các trang bảng tính. 7. Vùng bảng tính (vùng trắng ở giữa màn hình) để nhập dữ liệu và thực hiện các tính toán.

Thanh tiêu đề Thanh thực đơn Thanh công cụ Thanh định dạng Thanh công thức Vùng bảng tính các work sheet

III. Thoát khỏi Excel C 1: Ấn tổ hợp phím Alt+F 4 C 2: Kích chuột vào nút Close làm việc của chương trình. ở góc trên cùng bên phải cửa sổ C 3: Vào menu File / Exit Chú ý: Khi thoát khỏi Excel, nếu dữ liệu trong bảng tính chưa được ghi lại, Excel sẽ mở hộp thoại với những thông báo như sau: Yes : Thoát và ghi lại (nếu Tệp bảng tính chưa được đặt tên thì Excel mở cửa sổ cho phép đặt tên rồi mới thoát) No : Thoát ra, không ghi dữ liệu Cancel : Trở lại bảng tính, huỷ bỏ lệnh thoát.

Bài 2: Một số khái niệm I. Các kiểu dữ liệu trong bảng tính II. Các toán tử và một số biểu tượng thường dùng III. Cấu trúc Bảng tính TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Các kiểu dữ liệu trong Bảng tính Qui tắc chung về cách bố trí dữ liệu trong Excel là: Trong một ô chỉ có thể chứa một kiểu dữ liệu. Khi làm việc với bảng tính ta phải làm quen với rất nhiều kiểu dữ liệu, nhưng tất cả các kiểu dữ liệu đó đều dựa vào 3 kiểu cơ bản: Kiểu số, Kiểu chữ và Kiểu công thức. 1. Dữ liệu kiểu số: Bao gồm các số được đánh từ: 0 đến 9; Các giá trị ngày tháng, thời gian, tiền tệ, phần trăm… đều là dữ liệu kiểu số (có thể tính toán cộng, trừ, nhân, chia). Dữ liệu kiểu này nếu đánh đúng sẽ nằm bên phải của ô, khi dùng trong công thức không phải bỏ trong dấu nháy kép. 2. Dữ liệu kiểu chữ: bao gồm các chữ cái, chữ số và các ký tự đặc biệt. Dữ liệu kiểu này nếu đánh đúng sẽ nằm bên trái của ô, khi dùng trong công thức phải bỏ trong dấu “nháy kép”.

I. Các kiểu dữ liệu (2) 3. Dữ liệu kiểu công thức: qbắt đầu bởi dấu: =, + (thông thường nhất là sử dụng dấu =) qsau đó là: các hằng số, địa chỉ ô, hàm số được nối với nhau bởi các phép toán. q. Các phép toán: + , - , * , / , %, ^ (luỹ thừa) Ví dụ: = 10 + A 3 = B 3*B 4 + B 5/5 = 2*C 2 + C 3^4 – ABS(C 4) = SIN(A 2) =SUM(B 1: B 4)

II. Các toán tử & một số biểu tượng thường dùng 1. Các toán tử trong một biểu thức - Toán tử số học (+) Cộng (-) Trừ (*) Nhân (/ ) Chia - Toán tử xâu kí tự & Nối xâu - Toán tử so sánh ( > ) Lớn hơn ( < ) Nhỏ hơn ( = ) Bằng ( > = ) Lớn hơn hay bằng ( < = ) Nhỏ hơn hay bằng ( ^ ) Luỹ thừa

II. Các toán tử & một số biểu tượng thường dùng (2) 2. Một số biểu tượng thường dùng Xem hình -Sắp xếp tăng dần -Sắp xếp giảm dần -Tạo dấu phân nhóm -Hòa nhập các ô được chọn lại thành một ô -Tăng phần thập phân - Giảm phần thập phân

sắp xếp tăng dần sắp xếp giảm dần tạo dấu phân nhóm tăng phần thập phân giảm phần thập phân

III. Cấu trúc Bảng tính 1. Workbook: Là tệp tài liệu của Excel, mỗi Workbook có thể có từ 1 đến 255 bảng tính (Worksheet). Trong một Workbook thì các Worksheet, các đồ thị thường có liên hệ với nhau. 2. Worksheet (bảng tính): Mỗi bảng tính là một trang trong Workbook, mỗi bảng tính có trên 16 triệu ô dùng để nhập dữ liệu và thực hiện các phép tính toán. Mỗi bảng tính được đặt tên mặc định theo mẫu là Sheet i, với i là số thứ tự của bảng tính được tạo. (Sau này ta có thể đổi tên bảng tính sao cho thích hợp). 3. Cột (Columns): Là tập hợp các ô trong bảng tính theo chiều đứng. Có tổng cộng 256 cột trong một bảng tính, mỗi cột được gán một kí tự theo thứ tự chữ cái (bắt đầu từ A đến Z, AA đến AZ, BA đến BZ, . . và kết thúc là IV).

III. Cấu trúc Bảng tính (2) 4. Dòng (Rows): Là tập hợp các ô trong bảng tính theo chiều ngang. Có tổng cộng 65536 dòng trong một bảng tính và mỗi dòng được gán một số thứ tự (bắt đầu từ 1 đến 65536). 5. Ô (Cells): Là giao điểm của một cột và một dòng. Ô được xác định bởi toạ độ (địa chỉ) dựa theo tên cột và số thứ tự của dòng (Ví dụ: Ô A 1 là giao điểm của cột A và dòng 1). 6. Vùng (Range): Là tập hợp các ô xếp liền nhau, trong một vùng có dạng hình chữ nhật. Địa chỉ của vùng được xác định bởi địa chỉ của ô góc trên bên trái và ô góc dưới bên phải, giữa địa chỉ 2 ô là dấu hai chấm (: ) Ví dụ: A 2: B 6 (là vùng dữ liệu từ ô A 2 đến ô B 6).

Bài 3: Các thao tác với tập tin I. Tạo một File bảng tính mới II. Lưu File bảng tính III. Lưu File bảng tính với một tên khác IV. Mở một File bảng tính đã có trên đĩa TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Tạo một File bảng tính mới C 1: Kích chuột vào biểu tượng New công cụ. C 2: Ấn tổ hợp phím Ctrl+N C 3: Vào File / New…/ Blank Workbook Xem hình trên

II. Lưu File bảng tính C 1: Kích chuột vào biểu tượng Save trên thanh công cụ C 2: Ấn tổ hợp phím Ctrl+S. C 3: Vào menu File / Save. Khi đó sẽ xuất hiện hộp thoại cho phép ta đặt tên cho File bảng tính: Save in: chọn ổ đĩa, thư mục cần lưu File bảng tính vào. File name: gõ vào tên File bảng tính Save: Thực hiện ghi Xem hình Chú ý: khi ta ra lệnh lưu, nếu bảng tính đã được đặt tên thì những nội dung sửa đổi trên bảng tính sẽ lưu vào tên File đó.

chọn ổ đĩa, thư mục để lưu File gõ vào tên File bảng tính thực hiện ghi

III. Lưu File bảng tính với một tên khác Khi lưu File bảng tính với 1 tên khác thì File cũ vẫn tồn tại, File mới được tạo ra có cùng nội dung với File cũ. Cách thực hiện như sau: - Nhấn phím F 12 vào tên bảng tính mới (Hoặc vào File / Save As sau đó vào lại tên File mới)

IV. Mở một File bảng tính đã có trên đĩa Để mở một File bảng tính đã có trên đĩa, cần phải biết File bảng tính đó được lưu giữ ở đâu, (ổ đĩa nào, thư mục nào). Cách thực hiện như sau: C 1: Vào File / Open Xem hình C 2: Bấm tổ hợp phím Ctrl + O C 3: Bấm chuột vào biểu tượng trên thanh công cụ Khi đó Excel sẽ mở cửa sổ cho phép tìm File bảng tính: B 1: Look in: chọn ổ đĩa, thư mục chứa File bảng tính cần mở B 2: Chọn File bảng tính cần mở ở cửa sổ bên dưới chọn Open để mở (Hoặc bấm đúp chuột vào File cần mở ở cửa sổ bên dưới).

1. Chọn nơi chứa File 2. Chọn File cần mở 3. Bấm nút Open để mở File Bấm nút Cancel để hủy lệnh mở File

Chương 2: Làm việc với Bảng tính 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. Các loại địa chỉ Tham chiếu ô Các phím dịch chuyển con trỏ ô Chọn vùng-hàng-cột-bảng Nhập-Sửa-Xóa dữ liệu Cách thức tính toán và sao chép công thức trong Excel Sao chép khối Điều chỉnh chiều cao của hàng-chiều rộng của cột Chèn hàng-chèn cột Xóa hàng-Xóa cột-Xóa khối Các thao thác với Worksheet TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

1. Các loại địa chỉ a. Địa chỉ tương đối: v Dạng thức: Gồm tên cột và tên dòng. Ví dụ: A 15, C 43 v Tính chất: Thay đổi tương ứng trong thao tác COPY công thức. v Ví dụ: Ø Tại ô D 2 chứa công thức là =B 2+C 2, nếu Copy ô D 2 xuống ô D 3 thì khi đó công thức tại ô D 3 sẽ tự động thay đổi là =B 3+C 3 ØTại ô B 5 chứa công thức =B 2+B 3+B 4, nếu Copy ô B 5 sang ô C 5 thì khi đó công thức tại ô C 5 sẽ tự động thay đổi là =C 2+C 3+C 4

b. Địa chỉ tuyệt đối v Dạng thức: Thêm dấu $ trước tên cột và tên dòng. Ví dụ: $A$1, $C$2, $B$5 v Tính chất: Cố định (không thay đổi) trong thao tác COPY công thức. Ví dụ: Xét bảng tính sau, để tính cột Tiền Việt, khi đó tại ô C 4, ta nhập công thức =B 4*$C$1 Sau đó sao chép công thức này cho các ô còn lại.

c. Địa chỉ hỗn hợp - Địa chỉ tuyệt đối dòng: là địa chỉ có dòng không thay đổi nhưng cột thay đổi. Ký hiệu của địa chỉ tuyệt đối dòng là: tencot$tendong (ví dụ: C$3 là địa chỉ tuyệt đối dòng 3) - Địa chỉ tuyệt đối cột: là địa chỉ có cột không thay đổi nhưng dòng thay đổi. Ký hiệu của địa chỉ tuyệt đối cột là: $tencottendong (ví dụ: $C 3 là địa chỉ tuyệt đối cột C)

d. Dùng phím F 4 để thay đổi giữa các loại địa chỉ: Tại ô chứa công thức Chuyển sang chế độ sửa dữ liệu Bôi đen địa chỉ ô (hoặc địa chỉ vùng), rồi nhấn phím F 4 để thay đổi giữa các loại địa chỉ: - Nhấn 1 lần phím F 4: sẽ có dạng $A$1 - Nhấn 2 lần phím F 4: sẽ có dạng A$1 - Nhấn 3 lần phím F 4: sẽ có dạng $A 1 - Nhấn 4 lần phím F 4: sẽ có dạng A 1

2. Tham chiếu ô - Tham chiếu ô: Một công thức có chứa địa chỉ của ô khác, khi đó ta nói công thức sử dụng tham chiếu ô. - Lợi ích khi sử dụng tham chiếu ô: Khi sử dụng công thức nếu dùng địa chỉ ô (tham chiếu ô) thì khi giá trị trong ô được tham chiếu thay đổi, khi đó giá trị của công thức sẽ tự động tính lại. - Tham chiếu ô dùng chuột: Thay vì phải gõ vào địa chỉ của ô khi nhập dữ liệu hay sửa công thức, ta có thể sử dụng chuột để nhập tham chiếu ô. Cách thực hiện như sau: B 1: Gõ dấu “=” vào ô cần chứa công thức để mở đầu một công thức B 2: Bấm chuột vào ô cần tham chiếu (nếu tham chiếu đến 1 ô thì chuyển sang B 4) B 3: Gõ toán tử thích hợp Bấm chuột vào ô cần tham chiếu tiếp theo. B 4: Nhấn Enter để kết thúc.

3. Các phím dịch chuyển con trỏ ô + + + , , , dịch chuyển 1 ô theo hướng mũi tên Page Up dịch con trỏ lên 1 trang màn hình. Page Down dịch chuyển xuống 1 trang màn hình. Home cột đầu tiên (cột A) của dòng hiện tại Ctrl + tới cột cuối cùng (cột IV) của dòng hiện tại. Ctrl + tới cột đầu tiên (cột A) của dòng hiện tại. Ctrl + tới dòng cuối cùng (dòng 65536) của cột hiện tại. Ctrl + tới dòng đầu tiên (dòng 1) của cột hiện tại. Ctrl + + tới ô trái trên cùng (ô A 1). Ctrl + + tới ô phải trên cùng (ô IV 1). Ctrl + + tới ô trái dưới cùng (ô A 65536). Ctrl + + tới ô phải dưới cùng (ô IV 65536).

4. Chọn vùng-hàng-cột-bảng v Chọn vùng liên tục: kích chuột vào ô đầu khối, giữ và di tới ô cuối khối, nhả chuột. (hoặc kích chuột vào ô đầu khối, giữ phím Shift, kích chuột vào ô cuối khối) v Chọn nhiều vùng rời nhau: kích chuột vào ô đầu khối, giữ phím Ctrl rồi chọn các vùng tiếp theo. v Chọn cả hàng: kích chuột vào ô tên hàng. v Chọn cả cột: kích chuột vào ô tên cột. v Chọn cả bảng tính: kích chuột vào ô giao giữa tên hàng và tên cột.

5. Nhập-Sửa-Xóa dữ liệu Cách nhập số liệu: B 1: Chuyển con trỏ ô về ô cần nhập B 2: Tiến hành nhập số liệu từ bàn phím. B 3: Nhấn phím Enter (hoặc các phím mũi tên, hoặc phím Tab) để kết thúc việc nhập liệu. Chỉnh sửa dữ liệu trong ô: có 3 cách sau - Kích đúp chuột trái vào ô cần sửa - Chọn ô cần sửa rồi nhấn phím F 2 - Chọn ô cần sửa và sửa nội dung của ô trên thanh công thức Xóa dữ liệu: Bôi đen vùng dữ liệu cần xoá rồi nhấn phím Delete.

6. Cách thức tính toán và sao chép công thức trong Excel a, Nhập công thức tính toán: Nếu muốn tính toán với giá trị của ô nào thì sử dụng địa chỉ của ô đó để tính toán. Ví dụ: Muốn cộng 2 số ở địa chỉ B 3 và địa chỉ C 3 với nhau thì công thức tại ô cần đặt kết quả là =C 3+B 3 (không nhất thiết phải nhớ địa chỉ của các ô, chỉ cần gõ dấu bằng (=) sau đó kích chuột vào ô B 3, gõ tiếp dấu cộng (+) rồi kích chuột vào ô C 3, cuối cùng nhấn Enter sẽ có được kết quả)

6. Cách thức tính toán và sao chép công thức trong Excel (2) b, Sao chép công thức: Đưa con trỏ chuột vào góc dưới bên phải của ô chứa công thức, khi xuất hiện hình dấu cộng (+) nhỏ mầu đen thì bấm chuột trái, giữ, kéo dê đến vị trí cần sao chép rồi thả chuột.

7. Sao chép khối B 1: Chọn khối cần Copy B 2: gọi lện Copy (Ctrl+c) B 3: định vị con trỏ chuột tới chỗ cần Copy tới B 4: gọi lệnh dán (Ctrl+v)

8. Điều chỉnh chiều cao của hàng-chiều rộng của cột Hàng: - Đưa trỏ chuột đến đường phân chia giữa 2 tên hàng khi thấy xuất hiện hình mũi tên 2 chiều thì bấm chuột trái + giữ + kéo dê xuống dưới để tăng chiều cao, lên trên để giảm chiều cao. * Để thay đổi chiều cao của nhiều hàng: Bôi đen các hàng cần thay đổi rồi thực hiện kéo thả như trên. Cột: - Đưa trỏ chuột đến đường phân chia giữa 2 tên cột khi thấy xuất hiện hình mũi tên hai chiều thì Bấm+giữ+kéo dê sang phải để tăng chiều rộng, sang trái để giảm chiều rộng. * Để thay đổi chiều rộng của nhiều cột: Bôi đen các cột cần thay đổi rồi thực hiện kéo thả như trên.

9. Chèn hàng-chèn cột v Chèn hàng: C 1: dùng chuột - Bôi đen hàng mà cần chèn hàng trắng lên trước - Nháy phải chuột vào vùng bôi đen, rồi chọn Insert C 2: dùng menu lệnh - Định vị con trỏ ô vào hàng cần chèn hàng trắng lên trước - Vào menu Insert/ Rows v Chèn cột: C 1: dùng chuột - Bôi đen cột mà cần chèn cột trắng lên trước - Nháy phải chuột vào vùng bôi đen, rồi chọn Insert C 2: dùng menu lệnh - Định vị con trỏ ô vào cột cần chèn cột trắng lên trước - Vào menu Insert/ Columns

10. Xóa hàng-Xóa cột-Xóa khối Xóa hàng: Chọn hàng cần xóa, phải chuột vào vùng chọn, chọn Delete. Xóa cột: Chọn cột cần xóa, phải chuột vào vùng chọn, chọn Delete. Xoá khối: - Chọn khối cần xoá - Nháy phải chuột vào vùng chọn, chọn Delete, khi đó sẽ xuất hiệ hộp thoại với các lựa chọn như sau: Shift cells left: Các ô ở ngay kề bên phải sẽ dồn về bên trái thay thế vào vị trí của ô (khối) bị xoá. Shift cells up: Các ô ở dưới sẽ dồn lên trên thay vào chỗ của ô (khối) vừa bị xoá. Entire row: Xoá toàn bộ dòng chứa ô (hoặc khối) đã chọn Entire column: Xoá toàn bộ cột chứa ô (hoặc khối) đã chọn Cuối cùng bấm chọn OK để thực hiện xoá.

11. Các thao thác với Worksheet v Chèn thêm một Worksheet mới: Vào Insert/ Worksheet v Đổi tên Worksheet: Bấm đúp chuột trái vào Worksheet cần đổi tên, gõ tên mới, rồi nhấn Enter. v Xoá Worksheet: Nháy chuột phải vào Worksheet cần xoá, chọn Delete từ thực đơn vừa xuất hiện: v Sao chép Worksheet: v Nhấn phím Ctrl, bấm chuột trái vào Worksheet cần sao chép, giữ, kéo dê đến vị trí mới rồi nhả chuột để thực hiện Copy. v Di chuyển Worksheet: Bấm chuột vào Worksheet cần di chuyển, giữ, kéo dê đến vị trí cần di chuyển tới rồi nhả chuột.

Chương 3: Định dạng và In bảng tính Bài 1: Định dạng bảng tính Bài 2: In bảng tính TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

Bài 1: Định dạng bảng tính Việc định dạng bảng tính bao gồm có 2 công việc: Định dạng ô và định dạng trang. I. Định dạng ô (vào menu Format/Cells… ) II. Định dạng trang (vào menu File / Page Setup. . . ) III. Xoá toàn bộ định dạng ô đã làm TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Định dạng ô (vào menu Format/Cells… ) Gồm có 5 mục định dạng chính: - Định dạng số - Căn chỉnh dữ liệu - Định dạng phông chữ - Kẻ bảng - Tô mầu nền 1. Định dạng số 2. Căn chỉnh dữ liệu 3. Định dạng phông chữ 4. Kẻ bảng 5. Tô mầu nền TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

1. Định dạng số B 1: Bôi đen vùng cần định dạng. B 2: Vào Format / Cells, chọn thẻ Number, và chọn dạng thức của dữ liệu số tương ứng. Ta quan tâm tới các dạng thức thông dụng sau: + General: Dạng tổng quát ( ngầm định) + Number: Các dạng thức của dữ liệu số thật sự + Currency: Các dạng thức của dữ liệu số có dùng đơn vị tiền tệ

1. Định dạng số (2) + Date: Các dạng thức của dữ liệu ngày. + Time: Các dạng thức của dữ liệu giờ. + Text: Kiểu sâu kí tự + Custom: Cho phép lựa chọn theo ý của người sử dụng Xem hình VD 1: Để nhập $125. 000 thì: Bước 1: Nhập 125. 000 Bước 2: Dùng Curency và chọn dạng $1, 234. 10 VD 2: Để nhập số “ 1” máy tính hiện “ 001” thì: Bước 1: Chọn những ô cần định dạng như trên Bước 2: Dùng Custom, và gõ vào hộp Type ở cửa sổ Xem hình bên phải số: 000

2. Căn chỉnh dữ liệu B 1: Bôi đen vùng cần căn chỉnh B 2: Vào Format /Cells. . . chọn thẻ Alignment, rồi thiết lập như sau: + Horizontal: Căn chỉnh theo chiều ngang. General: Dạng tổng quát ( ngầm định). Left: Căn trái. Center: Căn giữa Right: Căn phải. + Vertical: Căn chỉnh theo chiều đứng. Top: Căn trên Center: Căn giữa. Bottom: Căn dưới. + Orientation (Hướng chữ): bấm chuột vào hình vuông mầu đỏ, giữ, kéo dê để có hướng tùy ý rồi nhả chuột + Wrap Text: Xuống hàng khi nội dung vượt quá độ rộng của ô + Shrink to fit: tự động điều chỉnh nội dung hiển thị đủ trong ô. + Merge cells: Hòa nhập các ô đã chọn lại thành một ô Thiết lập xong bấm OK

3. Định dạng phông chữ B 1: Bôi đen vùng cần định dạng B 2: Vào Format / Cells / chọn thẻ Font B 3: Thiết lập phông chữ như sau: + Font: Chọn phông chữ + Font style: Chọn kiểu chữ Regular: Chữ thường Italic: Chữ nghiêng Bold: Chữ đậm Bold Italic: Vừa đậm vừa nghiêng + Size: Cỡ chữ. + Underline: Các kiểu gạch chân + Color: Màu chữ + Superscipt: Chỉ số trên (X 2) + Subscript: Chỉ số dưới (X 2) Thiết lập xong Chọn OK

4. Kẻ bảng B 1: Bôi đen vùng cần kẻ bảng. B 2: Vào Format / Cells / Border. B 3: Thiết lập kẻ bảng như sau: + Style: Chọn kiểu đường. + Color: chọn mầu cho đường + Presets: Kiểu định sẵn: None: Không tạo đường kẻ. Outline: Viền ngoài. Inside: Viền trong. + Border: Tạo các đường kẻ phụ. Ghi chú: Sau khi chọn kiểu đường, mầu đường ta có thể bấm chuột vào phạm vi cần kẻ bảng (trái, phải, trên, dưới, hàng, cột) để kẻ Chọn OK.

5. Tô mầu nền B 1: Bôi đen vùng cần tô mầu B 2: Vào Format / Cells / chọn thẻ Patterns B 3: Chọn mầu cần tô B 4: OK

II. Định dạng trang (vào menu File / Page Setup. . . ) Gồm có 4 mục định dạng chính - Định dạng trang - Căn chỉnh lề - Thiết lập tiêu đề trên- tiêu đề dưới - Thiết lập tiêu đề cột-tiêu đề hàng cho các trang in 1. Định dạng trang 2. Căn chỉnh lề 3. Thiết lập tiêu đề trên- tiêu đề dưới 4. Thiết lập tiêu đề cột-tiêu đề hàng cho các trang in TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

1. Định dạng trang B 1: Vào File/ Page Setup Chọn thẻ Page B 2: Thiết lập như sau: - Orientation: Chọn hướng in Portait : quay dọc trang giấy Landscape: quay ngang trang giấy - Paper size: chọn khổ giấy in (A 3, A 4, . . . ) Chọn OK để thiết lập Chú ý

1. Định dạng trang (2) Chú ý: Trong trường hợp bảng tính lớn, phức tạp và đã căn chỉnh đẹp, cân đối. Nhưng lại không thể in vừa đủ ra khổ giấy cần thiết (bảng tính lớn hơn, hoặc nhỏ hơn trang giấy). Nếu phải căn chỉnh lại từng cột để phóng to / hoặc thu nhỏ toàn bộ bảng tính thì sẽ mất rất nhiều thời gian (vì bảng tính lớn). Khi đó ta chỉ cần điều chỉnh hệ số tỉ lệ ở mục “Adjust to” như sau: + Adjust to (ngầm định là 100%) Nếu tăng > 100: thì vùng in sẽ tự động lớn hơn Nếu giảm < 100: thì vùng in sẽ tự động nhỏ hơn Khi tăng hoặc giảm tỉ lệ của vùng in thì dữ liệu trong các ô của bảng tính vẫn đảm bảo cân đối.

2. Căn chỉnh lề Vào File / Page Setup. . . /chọn thẻ Margins Ở mục này ta không cần căn chỉnh lề (thực hiện ở phần căn chỉnh trước khi in) mà chỉ quan tâm tới việc “căn giữa trang in” như sau: + Horizontally: Nếu được chọn (đánh dấu) sẽ căn giữa theo chiều ngang + Vertically: Nếu được chọn (đánh dấu) sẽ căn giữa theo chiều đứng

3. Thiết lập tiêu đề trên- tiêu đề dưới B 1: Vào File /Page Setup. . . chọn thẻ Header / Footer B 2: Khai báo tiêu đề - Bấm chuột vào “Custom Header”: Để khai báo tiêu đề trên - Bấm chuột vào “Custom Footer”: Để khai báo tiêu đề dưới B 3: Cách chèn tiêu đề

- Tại tiêu đề trên, tiêu đề dưới: ta có thể định vị con trỏ chuột tại các vị trí để đặt tiêu đề như sau: Left Section: Góc trái của trang Center Section: ở giữa trang Right Section: Góc phải của trang - Có thể gõ nội dung của tiêu đề vào các vị trí trên, hoặc bấm chuột vào các biểu tượng để chèn các đối tượng đặc biệt. Cuối cùng chọn OK chèn File ảnh định dạng phông chữ chèn số trang chèn tổng số trang ngày hiện tại của m. tính Giờ hiện tại của m. tính tên Sheet đường dẫn tới File tên File

4. Thiết lập tiêu đề cột-tiêu đề hàng cho các trang in Vào File / Page Setup. . . /chọn thẻ Sheet, rồi thiết lập như sau: - Print area: Khai báo vùng cần in. Nếu không khai báo, Excel sẽ in tất cả các ô chứa dữ liệu. - Print Titles: Tiêu đề hàng cột Xem hình + Rows to repeat at top: Tiêu đề hàng được in ra ở tất cả các trang + Columns to repeat at left: Tiêu đề cột được in ra ở tất cả các trang Để chọn tiêu đề hàng: Định vị con trỏ chuột vào hộp “Rows to repeat at top” rồi bấm chuột vào tên hàng cần làm tiêu đề hàng Để chọn tiêu đề cột: Định vị con trỏ chuột vào hộp “Columns to repeat at left” rồi bấm chuột vào tên cột cần làm tiêu đề cột Để khai báo vùng in: Gõ vào địa chỉ vùng cần in (hoặc định vị con trỏ chuột vào hộp “Print area” rồi dùng chuột chọn vùng cần in)

III. Xoá toàn bộ định dạng ô đã làm Trong trường hợp đã định dạng xong nhưng lại muốn xoá định dạng đi để làm lại (xoá không mất dữ liệu), ta làm như sau: - Bôi đen vùng muốn bỏ định dạng - Vào Edit -> Clear -> Format

Bài 2: In bảng tính I. Căn chỉnh trước khi in (Print Priview) II. In bảng tính TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Căn chỉnh trước khi in (Print Priview) C 1: Bấm chuột vào biểu tượng Print Priview trên thanh công cụ C 2: Vào File / Print Priview Khi đó Excel sẽ xuất hiện cửa sổ xem trước (toàn cảnh trang in) để ta quan sát và căn chỉnh với các nút lệnh như sau: Next: chuyển đến trang tiếp theo; Previous: trở về trang trước đó Zoom: phóng to, thu nhỏ; Print: Gọi lệnh in; Setup: gọi định dạng trang Margins: bật / tắt các lề (giúp kéo giãn lề, cột) Xem hình lớn Page Break Priview: xem ngắt trang (trở về dạng bình thường: vào View / Normal) Close: đóng chế độ xem trước khi in, trở lại bảng tính Để điều chỉnh khoảng cách lề, cột ta dy chuyển chuột tới đường phân chia (hình vuông mầu đen nhỏ) khi thấy xuất hiện mũi tên 2 chiều mầu đen thì bấm+giữ+kéo dê để hiệu chỉnh.

II. In bảng tính Cách 1: In toàn bộ bảng tính (in tất cả các ô chứa dữ liệu trên Worksheet hiện hành) - Bấm chuột vào biểu tượng máy in trên thanh công cụ Cách 2: In lựa chọn - B 1: Vào menu File/ Print. . (hoặc nhấn tổ hợp phím Ctrl+P) - B 2: Thiết lập như sau: Xem hình + All: In toàn bộ bảng tính + Page(s): In trang tuỳ chọn Ø From: khai báo trang bắt đầu in Ø To: khai báo trang in cuối Bấm chuột vào Priview để xem trước khi in Bấm chuột vào OK để in

Chương 4: Các hàm cơ bản của Excel Bài 1: Giới thiệu Bài 2: Nhóm hàm số học Bài 3: Nhóm hàm thời gian Bài 4: Nhóm hàm Logic Bài 5: Nhóm hàm xử lý văn bản Bài 6: Nhóm hàm Thống kê Bài 7: Nhóm hàm Tìm kiếm và tham chiếu Bài 8: Nhóm hàm Cơ sở dữ liệu TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

Bài 1: Giới thiệu v. Hàm là 1 đoạn chương trình có sẵn nhằm thực hiện 1 yêu cầu nào đó về tính toán. v. Hàm luôn trả về 1 giá trị thuộc 1 kiểu dữ liệu nào đó. v. Dạng tổng quát của một hàm như sau: =Tên hàm([đối số 1, đối số 2, . . . , đối sốN]) v. Một số hàm không có đối số nhưng bắt buộc phải có cặp dấu ngoặc đơn () kèm theo tên hàm. Ví dụ: Now()

Bài 1: Giới thiệu (2) v. Tên hàm viết bằng chữ hoa hay thường đều có giá trị như nhau. vĐối số có thể là 1 hằng, một địa chỉ ô, địa chỉ vùng, một biểu thức, hay một hàm số khác. v. Nếu hàm đứng đầu một công thức thì phải bắt đầu bằng dấu bằng (=) hoặc dấu cộng (+). v. Hàm có thể chứa tối đa 30 đối số, các đối số phải được đặt cách nhau bởi dấu quy định trong Control Panel (thường là dấu phẩy, hoặc dấu chấm phẩy) v. Thông thường khi ta gõ xong tên hàm và gõ tới dấu mở ngoặc đơn thì cấu trúc của hàm do ta vừa gõ sẽ hiện ra tại vị trí con trỏ ô, nếu quan sát ta sẽ biết được sử dụng dấu nào.

v Nếu không tính được thì hàm sẽ trả về một trong các mã lỗi sau:

Bài 2: Nhóm hàm số học 1. Hàm ABS( ): Tính giá trị tuyệt đối của biểu thức số v. Cú pháp: ABS(Biểu thức số) v. Ví dụ: =ABS(-4) cho giá trị là 4 2. Hàm SQRT( ): Tính giá trị căn bậc hai dương của biểu thức số v. Cú pháp: SQRT(Biểu thức số) v. Ví dụ: =SQRT(4) cho giá trị là 2 =SQRT(-4) lỗi

Bài 2: Nhóm hàm số học (2) 3. Hàm INT: Lấy giá trị là phần nguyên. Cú pháp: INT(X) Cho giá trị là phần nguyên của X X: có thể là hằng số, biểu thức hoặc địa chỉ ô Ví dụ: 4. Hàm MOD: Cho giá trị là phần dư. Cú pháp: MOD( a, b) Cho giá trị là phần dư của phép chia a: b Ví dụ: =Mod(10; 3) cho giá trị là 1

I. Nhóm hàm số học (3) 5. Hàm ROUND: Làm tròn số. Cú pháp: ROUND( X, n) Làm tròn n số của X - Nếu n>0: làm tròn N số bên phải kể từ vị trí dấu chấm thập phân (làm tròn phần thập phân) Ví dụ: =Round(1260. 556, 2) Kết quả là 1260. 56 - Nếu n<0: làm tròn N số bên trái kể từ vị trí dấu chấm thập phân (làm tròn phần nguyên) Ví dụ: =ROUND(1260. 55, -2) kết quả là: 1300 =ROUND(1245. 55, -2) kết quả là: 1200 - Nếu n=0: Không lấy phần thập phân

Bài 3: Nhóm hàm thời gian 1. Hàm TODAY: Cho giá trị là ngày hiện tại của máy tính. Cú pháp: TODAY( ) Ví dụ: =Today( ) 06/12/2006 2. Hàm NOW: Cho ngày và giờ ở thời điểm hiện tại của máy tính. Cú pháp: NOW( ) Ví dụ: =Now() 06/12/2006 3: 46 3. Hàm DAY: Cho giá trị là ngày của biểu thức ngày tháng. Cú pháp: DAY(BTNT)

2. Nhóm hàm ngày tháng (2) 4. Hàm MONTH: Cho giá trị là tháng của biểu thức ngày tháng. Cú pháp: MONTH (BTNT) 5. Hàm YEAR: Cho giá trị là năm của biểu thức ngày tháng. Cú pháp: YEAR (BTNT) 6. Hàm DAYS 360: Cho giá trị là số ngày giữa khoảng cách của BTNT 1 và BTNT 2 Cú pháp: DAYS 360 ( BTNT 1, BTNT 2)

Bài 4: Nhóm hàm Logic 1. Hàm AND: phép và Cú pháp: AND ( điều kiện 1, điều kiện 2, . . . ) Hàm chỉ đúng (TRUE) khi tất cả các điều kiện đều có giá trị đúng, ngược lại hàm nhận giá trị sai (FALSE). Ví dụ: 2. Hàm OR: phép hoặc Cú pháp: OR ( điều kiện 1, điều kiện 2, . . ) Hàm chỉ sai khi tất cả các điều kiện đều có giá trị sai, ngược lại hàm nhận giá trị đúng. Lưu ý: Hàm AND và hàm OR thường được kết hợp với hàm IF để thực hiện các yêu cầu của bài toán.

IV. Nhóm hàm Logic (2) 3. Hàm IF: Nếu Cú pháp: IF (Biểu thức logic, giá trị 1, giá trị 2) Phát biểu: Nếu Biểu thức logic là đúng thì hàm trả về giá trị 1, ngược lại hàm trả về giá trị 2 Ví dụ 1: Hãy điền vào cột kết quả trong bảng danh sách học sinh như sau: Nếu điểm TB >= 5 thì điền chữ “Đỗ”, ngược lại điền chữ “Trượt”.

IV. Nhóm hàm Logic (3) v. Chú ý: Trong một số trường, hợp ta phải dùng hàm IF() lồng nhau khi bài toán có nhiều hơn 2 khả năng trả về, vì hàm IF() chỉ có thể trả về 2 giá trị. v. Nếu có N khả năng sảy ra thì có N-1 hàm IF( ) lồng nhau. v. Ví dụ 2: Xếp loại theo tiêu chuẩn sau: §Nếu Điểm TB <5: loại Yếu §Nếu 5 <= Điểm TB <6, 5: loại TB §Nếu 6, 5<= điểm TB <8: loại khá §Nếu 8<= Điểm TB <9: loại giỏi §Nếu Điểm TB >=9: Xuất sắc

Ví dụ 3: Hàm if kết hợp với hàm AND và hàm OR Hãy điền vào cột tiền thưởng theo yêu cầu sau: Nếu giới tính là Nữ hoặc Nam là loại giỏi thì thưởng 1. 500. 000. Trường hợp còn lại thưởng 1. 000.

Bài 5: Nhóm hàm xử lý văn bản 1. Hàm LEFT: Lấy ký tự từ bên trái sang Cú pháp: LEFT ( X, n) lấy n ký tự từ bên trái sang của văn bản X 2. Hàm RIGHT: Lấy ký tự từ bên phải sang Cú pháp: RIGHT ( X, n) lấy n ký tự từ bên phải sang của văn bản X

III. Nhóm hàm văn bản (2) 3. Hàm MID: Lấy ký tự bên trong của chuỗi. - Cú pháp: MID (X, m, n) lấy n ký tự trong chuỗi X bắt đầu từ vị trí m.

III. Nhóm hàm văn bản (3) 4. Hàm TRIM(): Cắt bỏ ký tự TRẮNG THỪA §Cú pháp: TRIM(x) Cắt bỏ ký tự TRẮNG THỪA ở 2 đầu của văn bản X. §Ví dụ: =Trim(“ ABC ”) = “ABC” 5. Hàm LEN(): Tính chiều dài của văn bản §Cú pháp: LEN(x) Tính chiều dài của văn bản X. §Ví dụ: =LEN(“ABC”)=3 =LEN(TRIM(“ ABC ”)=3

Bài 6: Nhóm hàm Thống kê 1. Hàm SUM: Tính tổng của các biểu thức số 2. Hàm AVERAGE: Tính giá trị trung bình của các biểu thức số 3. Hàm MAX: Tìm giá trị lớn nhất của các biểu thức số 4. Hàm MIN: Tìm giá trị nhỏ nhất của các biểu thức số 5. Hàm RANK: Xếp thứ hạng 6. Hàm COUNT: đếm giá trị số-đếm số ô không trống 7. Hàm COUNTA: Đếm số ô chứa dữ liệu (không rỗng) 8. Hàm COUNTIF: đếm có điều kiện 9. Hàm SUMIF: Tính tổng có điều kiện

2. Hàm AVERAGE: Tính giá trị trung bình của các biểu thức số. Cú pháp: AVERAGE (đối 1, đối 2, . . . đối n) - Trả về giá trị là trung bình cộng của các đối số - Các đối số là các hằng, địa chỉ ô, vùng

3. Hàm MAX: Hàm tìm giá trị lớn nhất Cú pháp: MAX (đối 1, đối 2, . . . đối n) - Cho giá trị lớn nhất trong danh sách các đối số

4. Hàm MIN: Hàm tìm giá trị nhỏ nhất của các biểu thức số Cú pháp: MIN (đối 1, đối 2, . . . đối n) - Cho giá trị nhỏ nhất trong danh sách các đối số

5. Hàm RANK: Xếp thứ hạng Cú pháp: RANK ( ô cần xếp thứ, vùng cần so sánh, cách xếp) Vùng cần so sánh: Thường để ở chế độ giá trị tuyệt đối: $ Cách xếp: Nếu bằng 0(hoặc không có): Xếp theo giá trị số giảm dần Nếu bằng 1: Xếp theo giá trị số tăng dần. VD: Xếp thứ cho bảng dưới đây dựa vào cột tổng điểm: + Cách xếp không có: Tại ô B 2 ta đánh công thức = RANK(A 2; $A$2: $A$4) + Cách xếp bằng 1: Tại ô B 2 ta đánh công thức: =RANK(A 2; $A$2: $A$4; 1)

6. Hàm COUNT: Đếm giá trị số-đếm số ô không trống - Cú pháp: COUNT(đối 1, đối 2, . . . đối n)

7. Hàm COUNTA: Đếm số ô chứa dữ liệu (không rỗng) Cú pháp: COUNTA(đối 1, đối 2, . . . đối n)

8. Hàm COUNTIF: Hàm đếm có điều kiện Cú pháp: COUNTIF ( Vùng cần đếm, điều kiện) Đếm số lượng các ô trong vùng đếm thỏa mãn điều kiện. VD 1: Đếm xem có bao nhiêu người có mức lương là: 800. 000 đ. Tại ô B 6 ta đánh công thức: =COUNTIF(B 2: B 5; 800000) VD 2: Đếm xem có bao nhiêu người xếp loại A. Tại ô A 6 ta đánh công thức: =COUNTIF(A 2: A 5; "A") VD 3: Đếm xem có bao nhiêu người có lương > 650. 000 =COUNTIF(A 2: A 5; ”>650000”)

9. Hàm SUMIF: Hàm tính tổng có điều kiện Cú pháp: SUMIF(vùng kiểm tra, điều kiện, vùng tính tổng) Ghi chú: Điều kiện bao giờ cũng phải bỏ trong dấu “nháy kép” VD: Tính tổng Lương của những người có Chức vụ là TP Tại ô C 6 ta đánh công thức: =SUMIF(A 2: A 5; "TP"; C 2: C 5)

Bài 7: Nhóm hàm Tìm kiếm và tham chiếu 1. Hàm VLOOKUP: Tìm kiếm và tham chiếu theo cột 2. Hàm HLOOKUP: Tìm kiếm và tham chiếu theo hàng 3. Hàm MATCH: Xác định vị trí 4. Hàm INDEX: Tham chiếu ô 5. Kết hợp hàm INDEX và hàm MATCH để dò tìm

1. Hàm VLOOKUP: Hàm tìm kiếm và tham chiếu theo cột Cú pháp: VLOOKUP (giá trị tìm kiếm, bảng tham chiếu, cột cần lấy, cách tìm) Ý nghĩa: Giá trị tìm kiếm: đem so sánh với Bảng tham chiếu: Ví dụ: tìm chính xác Ví dụ: tìm gần đúng - do ta tự lập ra sao cho phù hợp với yêu cầu của bài toán. - sử dụng trong công thức thường để ở chế độ giá trị tuyệt đối. - cột đầu tiên của Bảng tham chiếu là các danh mục cần tìm kiếm, các cột tiếp theo là giá trị tương ứng lấy theo cột đầu tiên. Cột cần lấy: chỉ ra cần lấy trong cột thứ mấy của bảng tra cứu. Cách tìm: Có 2 giá trị được liệt kê dưới đây: 0: Tìm chính xác, nếu không tìm được sẽ trả về lỗi #N/A 1: + Tìm gần đúng: Cột đầu tiên của Bảng tham chiếu được tìm kiếm cho giá trị lớn nhất nhưng nhỏ hơn hoặc bằng “giá trị cần tìm”. + Cột đầu tiên trong vùng tham chiếu phải được sắp xếp tăng dần + Nếu không tìm được giá trị thỏa mãn sẽ trả về lỗi #N/A

Ví dụ: Hãy điền phụ cấp chức vụ từ Bảng phụ lục vào cột PCCV trong Bảng thanh toán lương theo mẫu sau: =VLOOKUP(A 2; $B$9: $C$13; 2; 0)

2. Hàm HLOOKUP: Hàm tìm kiếm và tham chiếu theo hàng Cú pháp: HLOOKUP(giá trị tìm kiếm, bảng tham chiếu, hàng cần lấy, cách tìm) Ví dụ: Dựa vào bảng khen thưởng và cột xếp loại, hãy điền tiền khen thưởng vào cột Khen thưởng. - Tại ô C 2 ta có thể gõ công thức như sau: =HLOOKUP(A 2; $A$8: $C$9; 2; 0)

3. Hàm MATCH: Xác định vị trí vÝ nghĩa: Sử dụng hàm MATCH khi ta có một giá trị đã biết, và muốn xác định vị trí của nó (nhất, nhì, ba, . . ) trong danh sách 1 chiều (1 hàng, hoặc 1 cột) v. Cú pháp: MATCH(giá trị cần tìm, vùng tham chiếu, kiểu tìm) -Giá trị cần tìm: đem so sánh với vùng tham chiếu -Vùng tham chiếu: phải là mảng 1 chiều (1 hàng, hoặc 1 cột) -Kiểu tìm: có 3 giá trị được liệt kê dưới đây 0: tìm chính xác, nếu không tìm được sẽ trả về lỗi #N/A 1: + tìm giá trị lớn nhất nhỏ hơn hoặc bằng “giá trị cần tìm”. + vùng tham chiếu phải được sắp xếp tăng dần + nếu tất cả giá trị trong “vùng tham chiếu” lớn hơn giá trị cần tìm, hàm sẽ trả về lỗi #N/A

3. Hàm MATCH: Xác định vị trí (2) -1: + tìm giá trị nhỏ nhất lớn hơn hoặc bằng “giá trị cần tìm” + vùng tham chiếu phải được sắp xếp giảm dần + nếu tất cả giá trị trong vùng tham chiếu nhỏ hơn giá trị cần tìm, hàm sẽ trả về lỗi #N/A v. Các ví dụ: Tìm chính xác (kiểu tìm =0) Tìm giá trị gần nhất (kiểu tìm =1, hoặc kiểu tìm = -1)

Trường hợp tìm chính xác (kiểu tìm =0) =MATCH("Bưởi"; B 3: B 7; 0) cho kết quả là 4, bởi vì Bưởi là giá trị thứ bốn trong vùng B 3: B 7. =MATCH("Quý 4"; B 3: F 3; 0) cho kết quả là 5, bởi vì Quý 4 là giá trị thứ năm trong vùng B 3: F 3.

Tìm giá trị gần nhất =MATCH(92; B 3: B 6; 1) cho kết quả là 4, vì giá trị thứ tư trong vùng là giá trị lớn nhất nhỏ hơn hoặc bằng giá trị cần tìm (92) =MATCH(74; B 3: B 6; -1) trả về #N/A, vì vùng tham chiếu sắp xếp theo thứ tự tăng dần (không phù hợp với kiểu tìm = 1) =MATCH(74; D 3: D 6; -1) cho kết quả là 2, vì giá trị thứ hai trong vùng là giá trị nhỏ nhất lớn hơn hoặc bằng giá trị cần tìm (74)

4. Hàm INDEX: Tham chiếu ô vÝ nghĩa: Hàm INDEX dùng để tham chiếu đến 1 ô trong 1 vùng (hoặc tới 1 phần tử trong mảng) khi vị trí của ô này đã biết. v. Cú pháp: INDEX(vùng tham chiếu, Xác định 1, Xác định 2, số vùng) -Vùng tham chiếu: là mảng 1 chiều hoặc 2 chiều. -Xác định 1: + Nếu vùng tham chiếu là 1 chiều: Xác định vị trí của dòng hoặc cột cần lấy giá trị. + Nếu vùng tham chiếu là 2 chiều: Xác định vị trí của dòng cần lấy giá trị. -Xác định 2: Xác định vị trí của cột trong vùng tham chiếu (chỉ sử dụng đối số này nếu vùng tham chiếu là 2 chiều). -Số vùng: Xác định số vùng khi vùng tham chiếu bao gồm nhiều vùng không liên tiếp.

4. Hàm INDEX: Tham chiếu ô (2) Các ví dụ: v Vùng tham chiếu 1 chiều v Vùng tham thiếu 2 chiều v Vùng tham chiếu chứa nhiều vùng nhỏ hơn

Vùng tham chiếu 1 chiều =INDEX(B 3: B 7; 4) cho kết quả là Bưởi, phần tử thứ bốn của vùng B 3: B 7 =INDEX(B 3: F 3; 4) cho kết quả là Quý 3, phần tử thứ bốn của vùng B 3: F 3 =INDEX(B 3: F 3; 6) cho kết quả là lỗi #REF!, vì chỉ có 5 ô trong vùng B 3: F 3

Vùng tham thiếu 2 chiều =INDEX(B 3: F 7; 2; 4) cho kết quả là 22, là phần tử giao nhau của dòng thứ hai và cột thứ tư trong vùng B 3: F 7

Vùng tham chiếu chứa nhiều vùng nhỏ hơn Giả thiết: Trên 1 Worksheet có 2 vùng không liên tục: B 3: F 7 và H 3: L 7 được đặt tên là TONGHOP =INDEX(Tong. Hop; 2; 5; 1) cho kết quả là 20, là phần giao của dòng 2, và cột 5 của vùng 1 trong vùng TONGHOP =INDEX(Tong. Hop; 2; 5; 2) cho kết quả là 58, là phần giao của dòng 2, và cột 5 của vùng 2 trong vùng TONGHOP

5. Kết hợp hàm INDEX và hàm MATCH để dò tìm BT: Cho Bảng bán hàng và bảng giá như bên dưới. Hãy dùng hàm INDEX và hàm MATCH để điền vào cột Giá trong Bảng bán hàng dựa vào Bảng giá đã cho.

Bài 8: Nhóm hàm Cơ sở dữ liệu 1. Hàm DAVERAGE: Tính trung bình các giá trị trong cột thoả mãn điều kiện của vùng tiêu chuẩn 2. Hàm DCOUNT: Đếm số ô chứa giá trị số trong cột thoả mãn đk trong vùng tiêu chuẩn 3. Hàm DMAX: Hàm tìm giá trị cao nhất trong cột giá trị thoả mãn điều kiện của vùng tiêu chuẩn 4. Hàm DMIN: tính giá trị nhỏ nhất trong cột giá trị thoả mãn điều kiện của vùng tiêu chuẩn 5. Hàm DSUM: tính tổng trong cột giá trị thoả mãn điều kiện của vùng tiêu chuẩn

1. Hàm DAVERAGE: Tính trung bình các giá trị trong cột thoả mãn điều kiện của vùng tiêu chuẩn Cú pháp: DAVERAGE (vùng dữ liệu, cột cần tính, vùng tiêu chuẩn) Cách tạo vùng tiêu chuẩn: Vùng tiêu chuẩn phải có ít nhất hai hàng: - Hàng đầu tiên: là hàng tiêu đề, tên tiêu đề này phải giống với tên tiêu đề của vùng dữ liệu. - Hàng thứ hai trở đi: là các giá trị làm điều kiện tính toán. Ví dụ thực hành

VD: - Tính trung bình lương của những người có chức vụ là Giám đốc và Trưởng phòng - Tính trung bình lương của những người có chức vụ là nhân viên (NV).

2. Hàm DCOUNT: Đếm số ô chứa giá trị số trong cột thoả mãn đk trong vùng tiêu chuẩn Cú pháp: DCOUNT(vùng dữ liệu, cột cần đếm, vùng tiêu chuẩn) VD: Đếm xem có bao nhiêu người có mức lương là 500. 000 Tại ô D 8 ta đánh công thức: =DCOUNT(A 2: C 6; 3; E 2: E 3)

3. Hàm DMAX: Hàm tìm giá trị cao nhất trong cột giá trị thoả mãn điều kiện của vùng tiêu chuẩn Cú pháp: DMAX (vùng dữ liệu, cột giá trị, vùng tiêu chuẩn). VD: Tìm xem nhân viên có mức lương cao nhất là bao nhiêu. Tại ô E 8 ta đánh công thức: =DMAX(B 2: D 6; 3; F 2: F 3)

4. Hàm DMIN: Tìm giá trị nhỏ nhất trong cột giá trị thoả mãn điều kiện của vùng tiêu chuẩn * Cú pháp: DMIN (vùng dữ liệu, cột giá trị, vùng tiêu chuẩn). VD: Tìm xem nhân viên có mức lương thấp nhất là bao nhiêu. Tại ô E 8 ta đánh công thức: =DMIN(B 2: D 6; 3; F 2: F 3)

5. Hàm DSUM: Tính tổng trong cột giá trị thoả mãn điều kiện của vùng tiêu chuẩn Cú pháp: DSUM (vùng dữ liệu, cột giá trị, vùng tiêu chuẩn) VD: Tính tổng tiền lương những người có chức vụ là nhân viên. Tại ô D 8 ta đánh công thức: =DSUM(B 2: D 6; 3; F 2: F 3)

Chương 5: Quản trị dữ liệu Khái niệm về cơ sở dữ liệu II. Sắp xếp dữ liệu III. Lọc dữ liệu và kết suất dữ liệu IV. Tổng hợp theo nhóm I. TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Khái về cơvề sởcơ dữsở liệu 1. niệm Khái niệm dữ(CSDL) liệu § CSDL gồm các trường (field) và bản ghi (record). § Trường là một cột trong CSDL, mỗi trường biểu thị một thuộc tính của đối tượng và có kiểu dữ liệu nhất định. § Bản ghi là một hàng dữ liệu. § Dòng đầu của CSDL chứa các tên trường (hay còn gọi là dòng tiêu đề của CSDL), các dòng tiếp sau là các bản ghi, và là số liệu của CSDL. Xem minh hoạ

I. Khái niệm về cơ sở dữ liệu (2) Quản trị dữ liệu trên Excel bao gồm các công việc về sắp xếp, tìm kiếm, kết suất thông tin trực tiếp với các dòng (bản ghi) và các cột (trường). Lưu ý: Để khai thác dữ liệu được dễ dàng ta nên: + Trong CSDL không nên có dòng trống hoặc cột trống + Tách phạm vi CSDL với các phần khác trên bảng tính ít nhất 1 dòng hoặc 1 cột.

II. Sắp xếp dữ liệu a. Giới thiệu: q. Một CSDL không có tên trường thì tên cột sẽ thay thế. q Trường quy định cách sắp xếp gọi là khoá. q Excel cho phép ta chọn tối đa tới 3 khoá để sắp xếp. Ta có thể chọn 1 khóa, 2 khóa hoặc cả 3 khóa để sắp xếp. q. Nếu chọn cả 3 khóa để sắp xếp thì cách thức Excel tiến hành như sau: Ø Đầu tiên: CSDL sẽ được sắp xếp theo khóa thứ nhất. Ø Tiếp theo: Các bản ghi ở khoá thứ nhất sẽ được sắp xếp theo khoá thứ 2. Ø Cuối cùng: Các bản ghi ở khoá thứ hai sẽ được sắp xếp theo khoá thứ 3.

2. Sắp xếp dữ liệu (2) b. Cách sắp xếp: B 1: Chọn vùng CSDL cần sắp xếp, rồi vào Data/ Sort B 2: Xác lập các thông số như sau: + Sort by: Chọn khóa thứ nhất để sắp xếp + Then by: Chọn khóa thứ 2, thứ 3 để sắp xếp - Hướng sắp xếp: + Ascending: Tăng dần + Descending: Giảm dần

2. Sắp xếp dữ liệu (3) - My list has: Khai báo tiêu đề cho bảng tính + Header Row : Có dòng tiêu đề + No Header Row: Không có tiêu đề Xem hình - Bấm vào nút “Options” để thay đổi chế độ sắp xếp + Sort top to bottom: sắp xếp từ trên xuống (theo dòng) + Sort left to right: sắp xếp từ trái sang phải (theo cột) - Bấm OK để thực hiện việc sắp xếp lại dữ liệu. Chú ý: Nếu không khai báo tiêu đề thì khi sắp xếp rất có thể tiêu đề sẽ bị xáo trộn với các bản ghi khác.

Chọn khoá thứ nhất Sắp xếp tăng dần [Chọn khoá thứ hai] Sắp xếp giảm dần [Chọn khoá thứ ba] Ko có dòng tên trường (sắp xếp cả dòng đầu) Dòng đầu là tên trường (ko sắp xếp) Xếp từ trên xuống dưới Xếp từ trái sang phải

III. Lọc dữ liệu và kết suất dữ liệu • Mục đích: Lấy ra những bản ghi (thông tin) thoả mãn điều kiện nhất định. • Có thể lọc theo 2 cách: – Auto. Filter: Excel hỗ trợ điều kiện lọc – Advanced Filter… : người sử dụng tự định điều kiện lọc. 1) Lọc dữ liệu dùng Auto. Filter 2) Lọc dữ liệu dùng Advanced Filter

1) Lọc dữ liệu dùng Auto. Filter B 1: Định vị con trỏ chuột vào 1 ô bất kỳ trong CSDL, vào Menu Data/ Filter/ Auto. Filter khi đó sẽ xuất hiện các mũi tên thả xuống ở mỗi tên trường B 2: Kích chuột vào ký hiệu lọc (mũi tên) của trường cần lọc, rồi chọn các giá trị tương ứng như sau: ü Một giá trị cụ thể: chọn trực tiếp giá trị muốn lọc trong hộp liệt kê. ü All: để tắt bộ lọc cho trường này, các trường khác bộ lọc vẫn hoạt động ü Top 10… (chỉ có tác dụng với trường có giá trị số): + Ở hộp Text bên trái: nếu chọn Top hiện các giá trị lớn nhất hoặc chọn Bottom hiện các giá trị nhỏ nhất. + Ở hộp Text giữa: chọn số bản ghi cần hiện + Ở hộp Text bên phải: nếu chọn Items hiện số bản ghi lớn nhất (hoặc nhỏ nhất), nếu chọn Percent hiện % của số bản ghi lớn nhất (hoặc nhỏ nhất) Xem CSDL ü Custom… : tự định điều kiện lọc

1) Lọc dữ liệu dùng Auto. Filter (2) • Nếu chọn Custom… sẽ hiện hộp thoại cho phép ta tự định điều kiện lọc: Ở đây Excel cho phép ta chọn tới 2 điều kiện để lọc: + Dòng bên trên là điều kiện 1, dòng bên dưới là điều kiện 2. + Tại ô bên trái ta chọn các phép toán cơ bản, tại ô bên phải ta nhập vào giá trị cần lọc. + Kết hợp điều kiện 1 và điều kiện 2 là phép logic (phép và; phép hoặc) a. Ý nghĩa các phép toán cơ bản b. Ví dụ: phép toán cơ bản giá trị (toán hạng) phép toán Logic c. Chú ý: Để tắt bộ lọc cho toàn bộ bảng tính và hiển thị tất cả dữ liệu, chọn Data --> Filter --> Auto. Filter

a. Ý nghĩa các phép toán cơ bản Toán tử equal: does not equal: is greater than or equal to: is less than or equal to begins with does not begin with ends with does not end with contains does not contain Kí hiệu = (bằng) < > (khác) > (lớn hơn) > = (lớn hơn hoặc bằng) < (nhỏ hơn) < = (nhỏ hơn hoặc bằng) bắt đầu với không bắt đầu với kết thúc với không kết thúc với chứa không chứa

b. Ví dụ: Cho bảng bán hàng vật liệu xây dựng như sau: - Để lọc ra những mặt hàng bắt đầu là “gạch men” ta làm như sau: Tại trường Mặt hàng chọn Custom. . . chọn phép toán cơ bản là “begins with”, toán hạng là “gạch men”. - Để lọc ra những mặt hàng có giá lớn hơn hoặc bằng 65. 000 đồng ta làm như sau: Tại trường “Giá (VNĐ)” chọn Custom. . . chọn phép toán cơ bản là is greater than or equal to, toán hạng là “ 65000”

2) Lọc dữ liệu dùng Advanced Filter Để lọc dữ liệu dùng Advancd Filter, thì ta phải tạo vùng tiêu chuẩn (đã học ở bài 8, chương VI) B 1: Tạo vùng tiêu chuẩn (điều kiện): – Dòng đầu ghi tên trường để định điều kiện (tên trường phải giống hệt tên trường của vùng CSDL) – Các dòng tiếp dưới ghi điều kiện: các điều kiện cùng dòng là phép AND, các điều kiện khác dòng là phép OR. Ví dụ về vùng tiêu chuẩn

vùng tiêu chuẩn để lọc các bản ghi có số mặt hàng bán ra với số tiền = 750. 000 vùng tiêu chuẩn để lọc các bản ghi có số mặt hàng bán ra với số tiền > 750. 000 vùng tiêu chuẩn để lọc các bản ghi có số mặt hàng bán ra với: 500. 000<Số tiền< 3. 000 vùng tiêu chuẩn để lọc các bản ghi có số mặt hàng bán ra với: số tiền >800. 000 hoặc số lượng >20

2) Lọc dữ liệu dùng Advanced Filter (2) B 2: Thực hiện lọc - Chọn một ô bất kỳ trong vùng cơ sở dữ liệu - Vào menu Data/Filter/ Advanced Filter…, khi đó xuất hiện hộp thoại với thiết lập như sau: • Filter the list, in-place: Hiện KQ lọc ngay tại vùng dữ liệu • Copy to another location: Hiện KQ lọc ra nơi khác

2) Lọc dữ liệu dùng Advanced Filter (3) List rage: Chọn vùng CSDL Criteria range: Chọn vùng tiêu chuẩn Copty to: Chọn vùng hiện KQ Unique records only: Chỉ hiện 1 bản ghi trong số những KQ trùng lặp Bấm OK để thực hiện việc lọc dữ liệu. Xem hình

Hiện KQ lọc ngay tại vùng dữ liệu Hiện KQ lọc ra nơi khác Chọn vùng CSDL Chọn vùng tiêu chuẩn Chọn vùng hiện KQ Chỉ hiện 1 bản ghi trong số những KQ trùng lặp

IV: Tổng hợp theo nhóm Bước 1: Sắp xếp dữ liệu ( Sắp xếp trường dữ liệu mà cần tổng hợp theo nhóm) Bước 2: Đặt con trỏ vào 1 ô trong CSDL Bước 3: Vào Data / Sub. Total, rồi thiết lập như sau: At each change in: Chọn trường chính để nhóm Use function: Chọn tên hàm cần tổng hợp (SUM, Average, . . . ) Add subtotal to: Chọn trường cần tổng hợp theo trường chính TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

Page break between groups: Nếu chọn thì mỗi nhóm sẽ được in trên một trang riêng biệt. Summary below data: Nếu chọn thì dòng tổng số phụ được đặt ở phía dưới dữ liệu, nếu không nó đặt trên đỉnh. Chọn Remove All: Gỡ bỏ tổng hợp nhóm Chọn OK để thực hiện

Chương 6: Đặt tên và sử dụng tên trong công thức I. Đặt tên 1. Định nghĩa 2. Qui tắc đặt tên 3. Cách đặt tên 4. Xóa tên II. Sử dụng tên trong công thức TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

1. Định nghĩa ĐN: Tên là nhãn dễ nhận diện và dễ nhớ cho một ô hoặc một vùng. Việc sử dụng tên trong công thức sẽ giúp rõ ràng, dễ hiểu hơn, đảm bảo tính nguyên vẹn của tham chiếu, tăng hiệu quả công việc. Ví dụ: tên là BHYT thì dễ nhận ra và dễ nhớ hơn tham chiếu đến ô $G$5 tên là Phong. Ban thì dễ nhận ra và dễ nhớ hơn tham thiếu đến vùng $D$5: $D$30

2. Qui tắc đặt tên - Chỉ cho phép dùng những ký tự sau: A-: - Z (không phân biệt chữ hoa hay thường), 0 -: - 9, dấu chấm, dấu gạch nối dưới. - Tên phải bắt đầu bằng chữ cái hoặc dấu gạch nối dưới. - Độ dài của tên <= 255 ký tự. - Tên không được trùng với địa chỉ của ô.

3. Cách đặt tên Bước 1: Chọn ô (hoặc vùng) cần đặt tên Bước 2: Vào Insert/Name/Define, khi đó sẽ xuất hiện hộp thoại với những thiết lập như sau: - Names in workbook: gõ vào tên cần đặt cho ô (hoặc vùng). - Bấm Add để tạo tên mới - Bấm vào Close để đóng hộp thoại

4. Xóa tên - Vào Insert Name Define - Chọn tên cần xóa ở cửa sổ bên dưới - Bấm nút Delete để xóa.

II. Sử dụng tên trong công thức Một khi tên đã được đặt thì ta chỉ việc sử dụng nó vào trong công thức thay vì phải gõ vào địa chỉ ô, địa chỉ vùng, hay là một hằng số. Ví dụ: Sử dụng hàm trong công thức: =Vlookup(A 2, Phong. Ban, 2, 0)

Chương 7: Chèn đồ thị I. Một số biểu đồ thông dụng và ý nghĩa của chúng II. Cách tạo biểu đồ Bước 1: Chọn kiểu biểu đồ Bước 2: Chọn dữ liệu nguồn Bước 3: Điều khiển-sắp đặt các thành phần của biểu đồ Bước 4: Chọn vị trí đặt biểu đồ III. Tinh chỉnh biểu đồ đã tạo Thiết lập lại biểu đồ: Kích hoạt biểu đồ, bấm chuột vào biểu tượng tạo biểu đồ , Excel sẽ quay trở lại các bước đã thực hiện nêu trên. Tinh chỉnh biểu đồ: Bấm đúp chuột vào thành phần cần tinh chỉnh trên biểu đồ, Excel sẽ mở màn hình chỉnh, sửa biểu đồ (thay đổi mầu sắc của từng thành phần, . . . ) TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Một số biểu đồ thông dụng và ý nghĩa của chúng 1. Pie : Hình bánh, chỉ sự tương quan giữa một phần dữ liệu với toàn bộ dữ liệu 2. Bar : Hình Bar, so sánh các giá trị vào những thời điểm khác nhau 3. Column: Hình cột, tương tự như hình Bar 4. Line: Đường, chỉ hướng thay đổi dữ liệu theo thời gian 5. Area: Bước, nhấn mạnh sự thay đổi trên dữ liệu 6. Doughnut: Cũng giống như biểu đồ hình Pie, nhưng có thể vẽ nhiều vùng sử dụng một biểu đồ Doughnut.

Bước 1: Chọn kiểu biểu đồ - Bôi đen bảng tính ( vùng cần tạo biểu đồ) - Chọn biểu tượng ( hoặc vào Insert / Chart ) - Chọn kiểu đồ thị cần trong Chart type, sau đó chọn tiếp ở Chart sub – type. - Chọn Next để chuyển sang bước tiếp theo

Bước 2: Chọn dữ liệu nguồn - Data range: chọn vùng dữ liệu cần tạo biểu đồ - Series in: Rows: Đồ thị kiểu hàng. Columns: Đồ thị kiểu cột. - Bấm Next để chuyển sang bước tiếp theo

Bước 3: Điều khiển-sắp đặt các thành phần của biểu đồ - Titles: Thiết lập tiêu đề - Axes: cách đặt các giá trị cho trục X, và trục Y. - Gridlines: Cách đặt các đường lưới - Legend: chọn vị trí đặt chú thích của biểu đồ - Data Labels: Gán các nhãn cho biểu đồ - Data Table: Có gắn bảng dữ liệu nguồn vào biểu đồ hay không. - Bấm Next để chuyển sang bước tiếp theo.

Bước 4: Chọn vị trí đặt biểu đồ - As new sheet: đặt biểu đồ ra một Sheet riêng biệt. - As object in: đặt biểu đồ tại Sheet của bảng dữ liệu nguồn. - Bấm Finish để kết thúc

Chương 8: Sử dụng Pivot. Table trong tổng hợp báo cáo dữ liệu Bài 1: Sử dụng cơ bản Pivot. Table Bài 2: Hiệu chỉnh một Pivot. Table TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

Bài 1: Sử dụng cơ bản Pivot. Table I. Giới thiệu II. Cách tạo một Pivot Table đơn giản từ một cơ sở dữ liệu nội tại III. Làm mới một Pivot. Table IV. Thay đổi tên trường V. Thêm, bớt trường và hoán chuyển tiêu đề VI. Loại bỏ Pivot. Table VII. Sao chép Pivot. Table TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Giới thiệu v. Pivot. Tables được dùng để tóm tắt và phân tích dữ liệu một cách uyển chuyển. v. Bất cứ lúc nào ta cũng có thể thay đổi lối trình bày của nó bằng cách biến đổi và sắp xếp lại, hoặc xoay trục, những đầu cột, đầu mục dễ dàng và nhanh chóng. v. Cấu trúc của Pivot. Table gồm có 4 phần: + Page (trang): Được dùng để lọc cơ sở dữ liệu, cho phép các dữ liệu được nhìn thấy một trang cùng lúc.

I. Giới thiệu (2) + Colunm (tiêu đề cột): Trường được dùng cho tiêu đề cột + Row (tiêu đề dòng): Trường được dùng cho tiêu đề dòng + Data (phần dữ liệu): Là vùng dữ liệu của Pivot. Table. Ta có thể đặt các trường cần tổng hợp, báo cáo vào vùng này. Ít nhất phải đặt một trường trong vùng này.

II. Cách tạo một Pivot Table đơn giản từ một cơ sở dữ liệu nội tại Gồm có 3 bước: ØBước 1: Xác định nguồn dữ liệu và cách tạo ØBước 2: Xác nhận vùng cơ sở dữ liệu ØBước 3: Xác định nơi đặt Pivot. Table và đưa các trường dữ liệu vào Pivot. Table

Bước 1: Xác định nguồn dữ liệu và cách tạo - Mở một cơ sở dữ liệu cần tạo báo cáo - Chọn một ô bất kỳ trong cơ sở dữ liệu - Vào menu Data/ Pivot. Table and Pivot. Chart Report … Khi đó sẽ xuất hiện cửa sổ để chọn nguồn dữ liệu và cách tạo như sau:

Bước 1: Xác định nguồn dữ liệu và cách tạo (2) v Xác định nguồn dữ liệu Chọn mục: - Microsoft Office Excel list or database: Dữ liệu nguồn chứa trong cơ sở dữ liệu của một Worksheet. v. Cách tạo: Chọn mục: - Pivot. Table: Tạo báo cáo Bấm Next để chuyển sang Bước 2

Bước 2: Xác nhận vùng cơ sở dữ liệu ØNếu ô hiện hành ở trong một vùng của cơ sở dữ liệu cần tạo thì khi ta khởi động Pivot. Table sẽ tự động tìm ra biên của cơ sở dữ liệu. Ta có thể chọn một vùng dữ liệu khác bằng cách: Xóa hết nội dung trong hộp “Range: ”, định vị con trỏ chuột tại ô “Range”, rồi dùng chuột chọn một vùng cần tạo Pivot. Table. ØNgay sau khi xác nhận vùng cơ sở dữ liệu, nhấp chuột vào Next để chuyển sang Bước 3

Bước 3: Xác định nơi đặt Pivot. Table và đưa các trường dữ liệu vào Pivot. Table v. Xác định nơi đặt Pivot. Table: ØNếu chọn: New worksheet (Pivot. Table sẽ đặt ra một Worksheet mới). ØNếu chọn: Existing worksheet: (Pivot. Table sẽ đặt tại Worksheet chứa cơ sở dữ liệu), rồi bấm chuột chọn 1 ô trên bên trái để đặt Pivot. Table

Bước 3: Xác định nơi đặt Pivot. Table và đưa các trường dữ liệu vào Pivot. Table (2) v Chọn các trường đặt vào Pivot. Table: - Bấm chuột vào nút “Layout”: Để hiện cửa sổ sơ đồ cấu trúc của Pivot. Table và các trường trong cơ sở dữ liệu: + Khung ở giữa hộp thoại thể hiện cấu trúc của Pivot. Table (Page, Column, Row, Data). + Các nhãn ở bên phải hộp thoại là tên các trường của cơ sở dữ liệu do ta đã chọn.

Bước 3: Xác định nơi đặt Pivot. Table và đưa các trường dữ liệu vào Pivot. Table (3) Muốn đưa trường nào vào thành phần nào của Pivot. Table, ta chỉ việc bấm+giữ+kéo dê các trường đó từ danh sách bên phải đặt vào vị trí thành phần tương ứng của Pivot. Table ở khung giữa hộp thoại. Chọn xong thì bấm nút OK. Cuối cùng bấm Finish để hoàn tất.

III. Làm mới một Pivot. Table v. Do Pivot. Table không tự động tính lại khi nguồn dữ liệu thay đổi. Vì vậy ta phải biết cách làm mới một Pivot. Table để cập nhật lại khi dữ liệu nguồn thay đổi. v. Cách thực hiện như sau: ØNháy phải chuột vào 1 ô bất kỳ trong bảng Pivot. Table, rồi chọn Refresh Data

IV. Thay đổi tên trường - Bấm chuột vào tên Trường cần đổi tên Gõ tên mới vào Enter

V. Thêm, bớt trường và hoán chuyển tiêu đề v Ta có thể dễ dàng thêm trường, bớt trường, và hoán chuyển tiêu đề hàng thành tiêu đề cột (hoặc ngược lại) như sau: B 1: Bấm chuột chọn một ô bất kỳ bên trong Pivot. Table B 2: Vào menu Data/ Pivot. Table and Pivot. Chart Report … B 3: Bấm chuột vào nút “Layout…” Khi đó sẽ mở ra cửa sổ giống như khi ta tạo Pivot. Table. Tại đây ta có thể dễ dàng thay đổi nối trình bày như sau: + Để loại bỏ một trường ra khỏi Pivot. Table: Kéo dê và thả trường đó ra ngoài. + Để thêm trường dữ liệu vào Pivot. Table: Kéo dê trường dữ liệu và đặt vào các thành phần của Pivot. Table. + Để hoán chuyển tiêu đề cột thành tiêu đề hàng (hoặc ngược lại): Bấm+giữ+kéo dê trường đó từ Column Row (hoặc ngược lại)

VI. Loại bỏ Pivot. Table v Để loại bỏ Pivot. Table thì ta phải loại bỏ toàn bộ Pivot. Table, không thể loại bỏ một bộ phận của Pivot. Table. v Có 2 cách để loại bỏ Pivot. Table ØCách 1: Xóa Worksheet chứa Pivot. Tabale ØCách 2: Xóa tất cả những ô chứa Pivot. Table, gồm cả các trường Page nếu có. v Nếu cố gắng xóa hoặc loại bỏ một bộ phận của Pivot. Table, một thông báo lỗi sẽ xuất hiện và lệnh loại bỏ sẽ không được thực hiện

VII. Sao chép Pivot. Table Các trường hợp sao chép với Pivot. Table: v Sao chép một Pivot. Table Cách 1: Sao chép toàn bộ Workshet chứa Pivot. Table Cách 2: Sao chép tất cả các ô chứa Pivot. Table, gồm cả trường Page nếu có. v Sao chép nội dung các ô trong một Pivot. Table - Thực hiện bằng cách chọn một phần của Pivot. Table cần sao chép rồi thực hiện Copy/dán.

Bài 2: Hiệu chỉnh một Pivot. Table Định dạng một Pivot. Table II. Thay đổi định hướng trường dữ liệu III. Thay đổi lối tổng kết I. TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

I. Định dạng một Pivot. Table 1/. Định dạng trường dữ liệu: v. Cách 1: Như định dạng ô thông thường (Format/Cells) v. Cách 2: Theo cách định dạng dành riêng cho Pivot. Table - Nháy phải vào 1 ô bất kỳ thuộc trường dữ liệu cần định dạng - Chọn Field Settings - Bấm chuột vào nút Number, rồi chọn các dạng định dạng số cho trường đã chọn (ví dụ: tạo dấu phân nhóm cho hàng nghìn, …) 2/. Định dạng một Pivot. Table Sau khi một Pivot. Table đã được tạo ra, để trình bày một cách chuyên nghiệp và nhanh thì ta có thể sử dụng phương pháp định dạng tự động: - Bấm chuột chọn một ô trong vùng Pivot. Table - Vào menu Format/ Auto. Format… - Chọn một mẫu bất kỳ/ OK

II. Thay đổi định hướng trường dữ liệu v Khi các trường dữ liệu được đặt trong một Pivot. Table, chúng được định hướng theo chiều dọc do mặc định của chương trình. Dạng như sau: Ta để ý thấy trường “S. lượng” và “Thành tiền” ở cùng một cột. v Đôi khi ta muốn 2 trường này được đặt cạnh nhau trên 2 cột để thỏa mãn ý trình bày: v Ta làm như sau: -Nhấp chuột trên nút Data và rê nó về bên phải đặt sang ô bên cạnh thì nhả chuột. -Để trở về dạng ban đầu: Nhấp chuột trên nút Data và rê nó về bên trái đặt sang ô bên cạnh thì nhả chuột.

III. Thay đổi lối tổng kết Theo mặc định khi tạo mới một Pivot. Table thì: - Khi một trường dữ liệu là số thì việc tổng kết được thực hiện là Sum (tính tổng). Đây là lý do tại sao các tên trường dữ liệu được đặt trước bằng “Sum of”. - Khi một trường dữ liệu không phải là số thì mặc định tính là Count (đếm). Trong thực tế là có nhiều cách để tổng kết khác nhau, ta có thể thay đổi cách tổng kết của từng trường dữ liệu nếu cần. Các hàm tổng kết thường dùng là: + Sum: Tổng các giá trị + Count: Đếm (theo dòng) + Average: Trung bình cộng + Max: Giá trị lớn nhất + Min: Giá trị nhỏ nhất + Product: Tích của dữ liệu kiểu số + Count Nums: Đếm các giá trị số (theo dòng)

II. Thay đổi lối tổng kết (2) Để thay đổi cách tổng kết của một trường dữ liệu ta làm như sau: B 1: Nháy phải chuột vào bất kỳ ô nào thuộc trường dữ liệu cần thay đổi lối tổng kết B 2: Chọn Field Settings B 3: Chọn một hàm tổng kết bất kỳ OK

Chương 9: Một số thủ thuật 1. 2. 3. 4. 5. 6. 7. Dấu cột hay hàng Các phương pháp dấu số 0 trong Excel Thiết lập chế độ in đen trắng Đóng băng dòng tiêu đề Đưa các lệnh thường dùng ra thanh công cụ Thay đổi biểu tượng và tên của các lệnh trên thanh công cụ Bảo vệ bảng tính TRUNG T M TIN HỌC QDC -----o 0 o----- BIÊN SOẠN: BÙI VĂN DUNG

1. Dấu cột hay hàng v Đôi khi trong quá trình xử lý bảng tính ta muốn ẩn đi một vài dòng hay một vài cột nào đó không cần thiết để tránh rối mắt. v. Cách thực hiện như sau: ØDấu hàng: + Chọn hàng muốn ẩn đi rồi vào Format | Row | Hide ØDấu cột: + Chọn cột muốn ẩn đi rồi vào Format | Column | Hide. ØĐể hiện trở lại những hàng đã bị giấu đi: Chọn vùng lân cận (trên và dưới) của hàng đang bị ẩn rồi vào Format | Row | Undide ØĐể hiện trở lại những cột đã bị giấu đi: Chọn vùng lân cận (bên trái, bên phải) của cột đang bị ẩn rồi vào Format | Column | Unhide. Bằng cách này ta cũng có thể giấu đi cả một Sheet của mình.

2. Các phương pháp dấu số 0 trong Excel v. Khi tính toán trong Excel, ta có thể thấy kết quả số 0 nằm ở nhiều nơi trong bảng tính. Điều này gây khó nhìn hoặc lúc in ra sẽ không thẩm mỹ. v. Ta có thể tìm để xoá các số 0 nhưng rất mất thời gian và nếu chúng là ô có công thức thì khi tính toán thay đổi (giá trị khác 0) khi đó ta phải tạo công thức tại ô đã xóa. Điều này sẽ rất bất tiện. v. Sau đây là một số phương pháp “thủ tiêu” số 0: PP 1 - Giấu tất cả số 0 trong bảng tính PP 2 - Dùng định dạng số để giấu các số 0 trong những ô được chọn PP 3 - Dùng định dạng có điều kiện để giấu số 0 được trả về từ kết quả của công thức PP 4 - Dùng định dạng số để giấu số 0 và thay bằng dấu gạch nối (-): PP 5 - Giấu số 0 trong Pivot. Table

Cách 1 - Giấu tất cả số 0 trong bảng tính Nhấn vào menu Tools/Options, chọn thẻ View. Xoá hộp kiểm Zero values OK.

Cách 2 - Dùng định dạng số để giấu các số 0 trong những ô được chọn - Chọn các ô có số 0 cần giấu. Nhấn menu Format>Cells, chọn thẻ Number. -Trong hộp Category, chọn Custom. -Trong hộp Type, gõ 0; -0; ; @

Cách 3 - Dùng định dạng có điều kiện để giấu số 0 được trả về từ kết quả của công thức - Chọn ô có số 0 cần giấu. - Nhấn menu Format>Conditional Formatting. Ở hộp bên trái chọn Cell Value Is, hộp thứ hai tiếp theo chọn equal to, hộp kế tiếp gõ số 0. - Nhấn nút Format, chọn thẻ Font. Trong hộp Color, chọn màu trắng (hay trùng với màu nền của bảng tính). Bấm OK 2 lần.

Cách 4 - Dùng định dạng số để giấu số 0 và thay bằng dấu gạch nối (-): -Chọn các ô có số 0 cần giấu và thay thế bằng (-) -Nhấn menu Format/Cells, chọn thẻ Number. -Trong hộp Category, chọn Custom. -Trong hộp Type, gõ _(* #. ##0_); _(* (#. ##0); _(* "-"_); _(@_) (hoặc cuộn thanh cuộn xuống, chọn mục thứ 3 từ dưới lên).

Cách 5 - Giấu số 0 trong Pivot. Table - Nháy chuột phải vào một ô bất kỳ thuộc Pivot. Table, rồi chọn Table Options. - Đánh dấu chọn mục: For empty cells, show. Nếu muốn thay số 0 bằng ký tự khác thì gõ vào hộp kế bên; ngược lại muốn giấu số 0 thì để trống hộp.

3. Thiết lập chế độ in đen trắng v Thông thường trong bảng tính, những ô sử dụng công thức ta thường tô mầu nền để phân biệt và dễ quản lý (tránh xóa nhầm, hoặc vô tình làm sai công thức). v Nhưng khi in một bảng tính, những ô đã tô mầu nền sẽ khó nhìn, có mầu sẫm (vì máy thường in với máy in đen trắng). v Để không phải định dạng lại những ô đã tô mầu nền, mà khi in lại bỏ qua các mầu đã định dạng cho ô các đó (in đen trắng) ta làm như sau: Chọn Worksheet cần thiết lập Vào File/Page Setup. . . , rồi chọn thẻ Sheet, tiếp theo đánh dấu tích vào mục “Black and white”, chọn tiếp

4. Đóng băng dòng tiêu đề Trong mỗi bảng tính thường có dòng tiêu đề cố định vị trí cho từng cột (column). Nếu ta nhập dữ liệu trong nhiều dòng và tràn quá màn hình, công việc nhập dữ liệu sẽ rất khó khăn vì dòng tiêu đề “trôi” mất. Để “đóng băng” dòng tiêu đề ta làm như sau: + Nhấn chuột chọn một ô ngay dưới dòng tiêu đề + Chọn Window / Freeze Panes Để đóng băng cả tiêu đề dòng và tiêu đề cột: + Nhấn chuột chọn một ô ngay dưới dòng tiêu đề và bên phải cột tiêu đề + Chọn Wondw/ Freeze Panes

5. Đưa các lệnh thường dùng ra thanh công cụ vĐể tạo ra một nút bấm trên thanh công cụ giúp thực hiện một công việc thường dùng được dễ dàng hơn ta làm như sau: Ø vào Tools | Customize chọn thẻ Customize Ø Trong danh sách liệt kê Categories bên tay trái chọn tên một thực đơn chứa lệnh cần đưa ra thanh công cụ ØTrong danh sách liệt kê Commands bên tay phải chứa toàn bộ các lệnh con của thực đơn đã chọn ở khung liệt kê bên tay trái. Tìm lệnh cần đưa ra thanh công cụ, rồi Bấm+giữ+kéo dê đặt lên thanh công cụ. Ví dụ 1 Ví dụ 2

Ví dụ 1: Đưa lệnh “Đóng băng” dòng tiêu đề (lệnh tạo thanh chặn tiêu đề) trong Excel ra thanh công cụ ta làm như sau: Øvào Tools | Customize chọn thẻ Customize ØTrong danh sách liệt kê Categories : Chọn dòng Window and Help ØTrong danh sách liệt kê Commands : Bấm+giữ+kéo dê biểu tượng “Freeze Panes” đặt lên thanh công cụ rồi nhả chuột

Ví dụ 2: Đưa lệnh thêm hàng trong Excel ra thanh công cụ: vvào Tools | Customize chọn thẻ Customize v. Trong danh sách liệt kê Categories : Chọn dòng Insert v. Trong danh sách liệt kê Commands : Bấm+giữ+kéo dê biểu tượng “Rows” đặt lên thanh công cụ rồi nhả chuột. (nếu muốn kéo lệnh thêm cột thì kéo biểu tượng “Columns”, v. v…)

6. Thay đổi biểu tượng và tên của các lệnh trên thanh công cụ vĐể làm cho nút bấm trở nên thân thiện hơn, ta có thể thay đổi các biểu tượng trên thanh công cụ và tên của nó. v. Cách thực hiện như sau: Øvào Tools | Customize chọn thẻ Customize ØNháy phải chuột vào biểu tượng cần thay đổi trên thanh công cụ, rồi thực hiện các thay đổi như sau: - Reset: Khởi tạo lại các thiết lập ngầm định - Delete: Xóa lệnh đó khỏi thanh công cụ - Name: Đặt tên cho lệnh đó - Change Button Image: Thay đổi biểu tượng

7. Bảo vệ bảng tính a. Bảo vệ không cho mở và sửa đổi bảng tính b. Bảo vệ Sheet c. Bảo vệ Workbook

a. Bảo vệ không cho mở và sửa đổi bảng tính § Vào Tool | Options | Security, rồi chọn thẻ Security § tại ô Password to open: đặt mật khẩu không cho mở tệp § tại ô “Password to modify”: đặt mật khẩu không cho sửa đổi nội dung § Chọn OK, rồi ra lệnh lưu tài liệu Để gỡ bỏ mật khẩu ta lại vào Tool | Options | Security, rồi chọn thẻ Security, Sau đó xóa bỏ hết những dấu hoa thị (*) ở hộp mà trước đó ta đã đặt.

b. Bảo vệ Sheet v vào Tool | Protection | Protect Sheet v nhập mật khẩu bảo vệ vào trong hộp “Password to unprotect sheet” (đây là mật khẩu để gỡ bỏ mọi hình thức bảo vệ đối với bảng tính đã được bảo vệ) v Trong vùng “Allow all user of this worksheet to”: Ta có thể cho phép người sử dụng có thể thực hiện được những thao tác gì trên Sheet đã được bảo vệ. Ø Nếu ta không lựa chọn gì thì không ai có thể thay đổi được gì trên Sheet đó trừ khi người đó có mật khẩu loại bỏ tính năng Protect Sheet

c. Bảo vệ Workbook Tính năng bảo vệ Workbook giúp ta bảo vệ toàn bộ tệp bảng tính: Không cho phép thêm, sửa, xóa Sheet, hay thay đổi cửa sổ, v. v… - vào Tool | Protection | Protect Workbook - Gõ vào mật khẩu cần bảo vệ OK