MỤC LỤC
4.1. GIỚI THIỆU EXCEL ............................................................................ 2
4.1.1. Các thao tác với bảng tính ............................................................................ 2 4.1.2. Cấu trúc bảng tính ......................................................................................... 3 4.1.3. Các kiểu dữ liệu ............................................................................................. 3 4.1.4. Các kiểu tham chiếu ...................................................................................... 4
4.2. CÁC THAO TÁC VỚI BẢNG TÍNH ................................................... 5
4.2.1. Di chuyển trong bảng tính và giữa các bảng tính ......................................... 5 4.2.2. Chọn các ô trong bảng tính ........................................................................... 5 4.2.3. Sao chép, chuyển dữ liệu ............................................................................... 5 4.2.4. Nhập dữ liệu vào bảng tính ........................................................................... 6 4.2.5. Xoá, sửa dữ liệu............................................................................................. 6 4.2.6. Chèn ô, hàng, cột ........................................................................................... 6 4.2.7. Xóa ô, hàng, cột ............................................................................................. 7
4.3. ĐỊNH DẠNG BẢNG TÍNH .................................................................. 8
4.3.1. Thay đổi độ rộng cột, chiều cao hàng ........................................................... 8 4.3.2. Đặt màu nền .................................................................................................. 8 4.3.3. Vẽ khung ........................................................................................................ 8 4.3.4. Định dạng số ................................................................................................. 9 4.3.5. Định dạng văn bản ........................................................................................ 9 4.3.6. Canh dữ liệu trong ô ..................................................................................... 9
4.4. SỬ DỤNG HÀM TRONG EXCEL ..................................................... 11
4.4.1. Khái niệm .................................................................................................... 11 4.4.2. Một số hàm thường dùng ............................................................................. 11
4.5. QUẢN TRỊ DỮ LIỆU .......................................................................... 16
4.5.1. Khái niệm về cơ sở dữ liệu: ......................................................................... 16 4.5.2. Sắp xếp dữ liệu: ........................................................................................... 16 4.5.3. Lọc, rút trích dữ liệu: .................................................................................. 17 4.5.4. Các dạng tiêu chuẩn: .................................................................................. 20
4.6. HOÀN THIỆN TÀI LIỆU ................................................................... 23
4.6.1. Các thiết lập trang in: ................................................................................. 23
4.6.2. Xem trước khi in: ......................................................................................... 26 4.6.3. In tài liệu: .................................................................................................... 27
4.1. GIỚI THIỆU EXCEL + Khởi động vào Excel Thực hiện Click chuột vào nút Start Program Files Microsoft Excel hoặc Click chuột vào biểu tượng Microsoft Excel trên màn hình Desktop. Kết quả sẽ được một màn hình giao diện như sau (Hình 4.1):
Thanh công định dạng Formatting Thanh tiêu đề Thanh thực đơn Thanh công cụ chuẩn Standard
Thanh công thức
Thanh cuộn
Vùng lập bảng tính
Các bảng tính Thanh trạng thái
Hình 4.1. Màn hình giao diện bảng tính Excel 4.1.1. Các thao tác với bảng tính Thanh công thức: - Hiển thị toạ độ (địa chỉ), nội dung của ô hiện hành. Nếu ô hiện hành chứa kết quả của một phép tính, công thức của phép tính đó sẽ được hiện lên cho phép thay đổi, sửa chữa... Thanh trạng thái: - Thông báo các chế độ hoạt động của bàn phím: Numlock, Caplock... - Thông báo các chế độ làm việc của Excel:
+ Ready: Sẵn sàng chờ nhập dữ liệu hoặc thực hiện lệnh. + Edit: Chế độ chỉnh sửa dữ liệu. + Point: Tham chiếu đến một địa chỉ ô. + Enter: Đang nhập dữ liệu vào ô. Vùng làm việc: (cửa sổ bảng tính) - Hiển thị một phần bảng tính với những đường kẻ lưới phân cách các ô. Con trỏ ô: - Trong mọi thời điểm luôn tồn tại một con trỏ ô. Con trỏ ô có thể di chuyển khắp bảng tính, dùng chỉ ra ô sẽ chịu tác động bởi các lệnh của Excel. Ô có con trỏ gọi là ô hiện hành, địa chỉ và nội dung ô hiện hành luôn hiện trên thanh công thức. Các nhãn hàng, nhãn cột: - Hiện các tên cột và các số thứ tự dòng của bảng tính. Cho phép người sử dụng dựa vào đó xác định chính xác địa chỉ các ô. 4.1.2. Cấu trúc bảng tính Sổ làm việc: (Workbook) - Một Workbook gồm nhiều nhất 255 bảng tính, mỗi bảng đều được phân biệt bởi từ Sheet1 đến Sheet 255 (ngầm định là 3). Mỗi Workbook được lưu trên đĩa dưới một file có đuôi XLS. Bảng tính: (Sheet) - Một bảng tính gồm các ô, hàng, cột. Cho phép nhập dữ liệu và thực hiện các yêu cầu tính toán. Cột: (Column) - Tập hợp các ô trong bảng tính được liệt kê theo chiều dọc. Có tổng cộng 256 cột trong một bảng. Mỗi cột được gán một tên theo thứ tự chữ cái (A - Z, AA AZ, BA - BZ, IV). Hàng: (Row) - Hàng là một tập hợp các ô theo chiều ngang. Có tổng cộng 65536 hàng trong một bảng tính. Mỗi hàng được gán một số thứ tự từ 1 đến 65536. Ô: (Cell) - Ô là giao điểm giữa hàng và cột. Mỗi ô có một địa chỉ duy nhất được xác định bởi tên cột và số thứ tự hàng (ví dụ: A5; B9...). 4.1.3. Các kiểu dữ liệu Kiểu số: - Là kiểu dữ liệu được sử dụng chủ yếu trong bảng tính, bao gồm: + Các kí số: 0 - 9.
+ Các dấu + , - , chấm thập phân (.), phẩy ngăn cách hàng nghìn (,). Kiểu chữ: (văn bản, chuỗi) - Dùng thể hiện văn bản trong bảng tính, bao gồm tất cả các kí tự: 0 - 9, a z... Kiểu ngày, giờ: - Dùng quản lí những thông tin về thời gian. Kiểu ngày, giờ phải được nhập theo dạng thức đã lựa chọn trong Control Panel của Windows. Nếu nhập không đúng, Excel sẽ tự động chuyển sang kiểu chuỗi. Kiểu công thức: - Phải bắt đầu bởi dấu = hay +. - Kết quả công thức được hiển thị trong ô, bản thân công thức sẽ xuất hiện trên thanh công thức. - Một công thức bao gồm các toán tử và các toán hạng. a) Toán hạng: - Bao gồm các dữ liệu kiểu số, kiểu chuỗi (phải đặt trong cặp nháy kép), kiểu ngày, giờ, địa chỉ ô, các hàm. b) Toán tử: - Toán tử tính toán: + , - , * , / , ^ (luỹ thừa): dùng với dữ liệu kiểu số. - Toán tử liên kết: & (dùng liên kết chuỗi) - Toán tử so sánh: = , <> , > , < , >= , <= : trả về một trong hai giá trị: đúng (TRUE), sai (FALSE). 4.1.4. Các kiểu tham chiếu - Việc một ô sử dụng giá trị của một ô khác thông qua địa chỉ trong công thức gọi là công thức. Ô chứa giá trị là ô nguồn, ô sử dụng giá trị là ô đích. Địa chỉ tương đối: (A1) - Là địa chỉ mà trong đó địa chỉ ô nguồn thay đổi tương ứng với vị trí ô đích khi sao chép công thức trong ô đích tới vị trí mới. Địa chỉ tuyệt đối: ($A$1) - Là địa chỉ mà trong đó địa chỉ ô nguồn vẫn không thay đổi khi sao chép công thức trong ô đích tới vị trí mới. Địa chỉ hỗn hợp: - Tuyệt đối cột, tương đối hàng: ($A1) - Tương đối cột, tuyệt đối hàng: (A$1) - Là tham chiếu mà trong đó địa chỉ ô nguồn chỉ thay đổi tương ứng theo hàng hoặc theo cột với vị trí ô đích khi sao chép công thức trong ô đích tới vị trí mới.
4.2. CÁC THAO TÁC VỚI BẢNG TÍNH 4.2.1. Di chuyển trong bảng tính và giữa các bảng tính 4.2.1.1. Các thao tác trong bảng tính: - Lên một màn hình: Page Up. - Xuống một màn hình: Page Down. - Sang phải một màn hình: Alt + Page Down. - Sang trái một màn hình: Alt + Page Up. - Tới ô đầu hàng: Home. - Tới ô đầu tiên của bảng tính (ô A1): Ctrl + Home. 4.2.1.2. Thao tác giữa các bảng tính: - Tới bảng tính kế tiếp: Ctrl + Page Down. - Tới bảng tính trước: Ctrl + Page Up. 4.2.2. Chọn các ô trong bảng tính 4.2.2.1. Bằng chuột: - Chọn một vùng: Bấm chuột tại ô đầu tiên của vùng cần chọn sau đó kéo chuột đến vô cuối cùng của vùng cần chọn (vùng chọn sẽ được bôi đen). - Chọn các hàng, các cột: Kéo chuột trên các nhãn hàng, nhãn cột cần chọn. - Chọn cả bảng tính: Nhấp chuột vào giao điểm giữa các nhãn hàng và nhãn cột. 4.2.2.2. Bằng bàn phím: - Một vùng: Đưa con trỏ về ô đầu tiên của vùng cần chọn, giữ Shift đồng thời sử dụng các phím mũi tên để xác định vùng chọn. - Một hàng: Shift + Spacebar. - Một cột: Ctrl + Spacebar. - Cả bảng: Ctrl + A. 4.2.3. Sao chép, chuyển dữ liệu 4.2.3.1. Cách một: - Chọn các ô muốn sao chép hoặc di chuyển. - Thực hiện một trong các cách: + Mở menu Edit, chọn Copy để sao chép, chọn Cut để chuyển. + Nhấn chuột vào nút Copy hoặc Cut trên thanh công cụ. + Bấm Ctrl + C để sao chép hoặc Ctrl + X để di chuyển. - Đưa con trỏ ô tới vùng muốn dán dữ liệu, chọn một trong các cách:
+ Bấm Enter. + Thực hiện Edit \ Paste. + Nhấn nút Paste trên thanh công cụ. + Bấm Ctrl + V. 4.2.3.2. Cách hai: - Chọn các ô cần thao tác. - Đưa trỏ chuột tới đường viền của vùng sao cho con trỏ biến thành hình mũi tên. - Kéo vùng đã chọn tới vị trí mới để di chuyển, giữ thêm Ctrl để sao chép. 4.2.4. Nhập dữ liệu vào bảng tính 4.2.4.1. Nhập thông thường: - Chọn ô chứa thông tin cần nhập (ô nguồn). - Đưa trỏ chuột tới góc dưới bên phải ô cho đến khi nó biến thành hình chữ thập. - Giữ chuột và kéo đến hết vùng cần nhập dữ liệu. - Nhả chuột. - Sau đó mới thực hiện nhập dữ liệu vào các ô đó (cách này sẽ cho phép chúng ta chỉ được nhập dữ liệu trong các ô đã chọn mà thôi). 4.2.4.2. Nhập với dữ liệu tăng hoặc giảm: - Nhập giá trị đầu tiên của dãy cần điền vào ô thứ nhất, giá trị tiếp theo vào ô thứ hai. - Chọn cả hai ô (lưu ý ô hiện hành phải là ô thứ nhất, có màu trắng). - Đưa trỏ chuột tới góc dưới bên phải của vùng cho tới khi nó thành hình chữ thập màu đen. - Giữ chuột và kéo đến hết vùng cần điền. - Nhả chuột. 4.2.5. Xoá, sửa dữ liệu 4.2.5.1. Xoá: - Chọn vùng chứa dữ liệu cần xoá. - Bấm Delete hoặc chọn Clear từ menu Edit. Nếu chọn Clear thì xuất hiện menu con, tại đó chọn: + All: Xoá tất cả. + Format: Xoá định dạng. + Contents: Xoá nội dung.
+ Notes: Xoá ghi chú. 4.2.5.2. Sửa: - Bấm F2 hoặc nhấp đúp chuột vào ô cần sửa. - Tiến hành sửa dữ liệu. - Kết thúc sửa bằng bấm Tab hoặc Enter. 4.2.6. Chèn ô, hàng, cột - Chọn vùng cần chèn. - Menu Insert chọn: + Columns: Chèn cột. + Rows: Chèn hàng. + Cells: Chèn ô, xuất hiện hộp thoại Insert, tại đây chọn:
Hình 4.2. Hộp thoại tùy chọn khi chèn các ô vào bảng tính - Shift cells right: Chèn ô mới bằng cách đẩy các ô cũ sang phải. - Shift cells down: Chèn ô mới bằng cách đẩy các ô cũ xuống dưới. - Entire row: Chèn cả hàng và đẩy các hàng cũ xuống dưới. - Entire column: Chèn cả cột và đẩy các cột cũ sang phải. 4.2.7. Xóa ô, hàng, cột - Chọn vùng cần xoá. - Thực hiện Edit \ Delete, xuất hiện hộp thoại Delete, chọn:
Hình 4.3. Hộp thoại tùy chọn khi xóa các ô trong bảng tính - Shift cells left: Xoá vùng chọn và kéo các ô bên phải về. - Shift cells up: Xoá vùng chọn và kéo các ô ở dưới lên. - Entire row: Xoá cả hàng. - Entire column: Xoá cả cột.
4.3. ĐỊNH DẠNG BẢNG TÍNH 4.3.1. Thay đổi độ rộng cột, chiều cao hàng Cách một: - Đưa chuột vào đường phân cách bên phải nhãn cột hoặc bên dưới nhãn dòng. Kéo chuột để thay đổi. Cách hai: - Thay đổi độ rộng cột: Chọn các cột cần thay đổi. Thực hiện Format \ Column \ Width. Nhập vào độ rộng mới, nhấn OK. - Thay đổi chiều cao hàng: Chọn các hàng cần thay đổi. Thực hiện Format \ Row \ Height. Nhập vào chiều cao mới, nhấn OK. 4.3.2. Đặt màu nền - Chọn vùng cần đặt màu. - Thực hiện Format \ Cells hoặc bấm Ctrl + 1. Xuất hiện hộp thoại, chọn tab Patterns. Chọn một màu trong Color OK. 4.3.3. Vẽ khung - Chọn các ô muốn vẽ khung. - Thực hiện Format \ Cells hoặc Ctrl+1, chọn tab Border.
Hình 4.4a. Hộp thoại định dạng bảng tính - định dạng các đường viền + Style: Chọn kiểu đường kẻ. + Color: Chọn màu đường kẻ. + Các nút còn lại dùng để vẽ khung.
4.3.4. Định dạng số - Chọn vùng cần định dạng. - Thực hiện Format \ Cells hoặc Ctrl+1, tab Number. - Chọn một kiểu định dạng trong khung Category. (có thể xem ví dụ mẫu trên dòng Sample). 4.3.5. Định dạng văn bản - Chọn vùng cần định dạng - Thực hiện Format \ Cells hoặc Ctrl+1, tab Font - Chọn font chữ thích hợp trong khung Font - Thay đổi cỡ chữ trong khung Size - Trong khung Font Style, chọn: + Regular: Kiểu chữ bình thường + Italic: Kiểu chữ nghiêng + Bold: Kiểu chữ đậm + Bold Italic: Vừa đậm vừa nghiêng - Trong hộp Underline, chọn: + Single: Gạch chân nét đơn + Double: Gạch chân nét đôi - Chọn màu chữ trong hộp Color - Strikethrough: Gạch ngang văn bản - Superscript: Chỉ số trên - Supscript: Chỉ số dưới - Nhấn OK. 4.3.6. Canh dữ liệu trong ô - Chọn vùng cần canh - Thực hiện Format \ Cells, tab Alignment.
Hình 4.4b. Hộp thoại định dạng bảng tính - định dạng dữ liệu - Chọn trong hộp Horizontal: + General: Thông thường + Left: Căn trái dữ liệu + Center: Căn giữa + Right: Căn phải + Fill: Lặp lại dữ liệu đến khi đầy ô + Justify: Trải dữ liệu đến hết chiều rộng ô + Center across selection: Căn giữa dữ liệu trong khoảng ô lựa chọn - Trong hộp Vertical, chọn: + Top: Căn dữ liệu sát đỉnh ô. + Center: Căn giữa ô theo chiều dọc. + Bottom: Căn sát đáy ô. + Justify: Trải dữ liệu hết chiều cao ô. - Chọn hướng thể hiện dữ liệu trong khung Orientation. - Chọn Wrap text để điều khiển việc xuống dòng của dữ liệu khi chiều dài dữ liệu vượt quá độ rộng ô. - Chọn Shrink to fit để co dữ liệu lại cho vừa chiều ngang ô. - Chọn Merge Cells để nối các ô được chọn thành một ô duy nhất.
- Nhấn OK.
4.4. SỬ DỤNG HÀM TRONG EXCEL 4.4.1. Khái niệm * Hàm là dạng công thức đã được viết sẵn để giúp tính toán nhanh hơn. Tất cả các hàm đều có dạng tổng quát như sau: Tên hàm(Các tham biến). 4.4.2. Một số hàm thường dùng 4.4.2.1. Nhóm hàm số:
Hình 4.5. Minh họa ví dụ về sử dụng nhóm hàm số 1. ABS(số): Lấy giá trị tuyệt đối của một số. =2+ABS(-5.2) {=7.2} 2. INT(số): Lấy phần nguyên của một số. =INT(6.7) {=6} =INT(-6.1) {=-7} 3. MOD(số bị chia, số chia): Trả về giá trị dư của phép chia. =MOD(10, 3) {=1} 4. ROUND(số cần làm tròn, số lẻ): Làm tròn số. =ROUND(15.3524, 2) {=15.35} =ROUND(15.831, 0) {=16} =ROUND(15268.534, -3) {15000} 5. SQRT(số): Trả về căn bậc 2. =SQRT(16) {=4}
4.4.2.2. Nhóm hàm thống kê: 1. AVERAGE(số thứ 1, thứ 1,...): Tính trung bình các số trong ngoặc. =AVERAGE(4, 6, 8, 10) {=7} 2. COUNT(giá trị 1, giá trị 2,...): Đếm các ô kiểu số. =COUNT(B1:B4) {=3} 3. COUNTA(giá trị 1, giá trị 2,...): Đếm các ô có chứa dữ liệu. =COUNT(A7:C8) {=2} 4. MAX(số thứ 1, số thứ 2,...): Lấy số lớn nhất. =MAX(B2:B6) {=28} 5. MIN(số thứ 1, số thứ 2,...): Lấy số nhỏ nhất. =MIN(B2:B6) {=4} 6. SUM(số thứ 1, thứ 2,...): Tính tổng các số. =SUM(B2:B5) {=28} 4.4.2.3. Nhóm hàm chuỗi: 1. LEFT(chuỗi, số kí tự muốn lấy): Lấy các kí tự phía bên trái của chuỗi. =LEFT(“Tp.HCM”, 2) {=Tp} 2. RIGHT(chuỗi, số kí tự muốn lấy): Lấy các kí tự phía bên phải của chuỗi. =RIGHT(“Tp.HCM”, 3){=HCM} 3. MID(chuỗi, số bắt đầu, n): Lấy n kí từ từ số bắt đầu. =MID(“Vu Thi Thu”, 4,
Hình 4.6. Minh họa ví dụ về sử dụng nhóm hàm thống kê
3){=Thi} 4. UPPER(chuỗi): Đổi chuỗi thành chữ in. =UPPER(“van an”) {=VAN AN} 5. LOWER(chuỗi): Đổi chuỗi thành chữ thường. =LOWER(“VAN AN”){=van an}
Hình 4.7. Minh họa ví dụ về sử dụng nhóm hàm chuỗi
6. PROPER(chuỗi): Đổi kí tự đầu mỗi từ thành chữ in. =PROPER(“nguyen thanh huyen coi”) {=Nguyen Thanh Huyen Coi} 7. TRIM(chuỗi): Cắt bỏ khoảng trống ở đầu và cuối chuỗi. =TRIM(“ SCC ”) {=SCC} 4.4.2.4. Nhóm hàm ngày giờ: 1. NOW(): Trả về thời điểm hiện tại. =NOW() {=12:25} 2. TODAY(): Trả về ngày tháng năm hiện tại. =TODAY() {=10/29/2000} 3. YEAR(tháng-ngày-năm): Trả về giá trị của năm. =YEAR(B3) {=98} 4. MONTH(tháng-ngày-năm): Trả về giá trị của tháng. =MONTH(B3) {=11} 5. DAY(tháng/ngày/năm): Trả về giá trị của ngày. =DAY(B3) {=6}
Hình 4.8. Minh họa ví dụ về sử dụng nhóm hàm ngày giờ
6. WEEKDAY(tháng-ngày-năm): Trả về giá trị thứ trong tuần. =WEEKDAY(B1) {=6}
4.4.2.5. Nhóm hàm điều kiện: 1. IF(điều kiện, giá trị đúng, giá trị sai): Trả về giá trị đúng nếu điều kiện đúng, ngược lại trả về giá trị sai. =IF(C4>=5, “Nhanh”, “Chậm”) Kết quả là: {=Nhanh} 2. SUMIF(vùng 1, “điều kiện”, vùng 2): Tính tổng các ô trong vùng 2 tương ứng với các ô vùng 1 đã thoả mãn điều kiện. Dữ liệu trong vùng B4:B7 là 3, 6, 8, 6 Dữ liệu trong vùng C4:C7 là 6, 9, 9, 4 =SUMIF(B4:B7,“>5”,C4:C7) Kết quả là: {=22}
Hình 4.9 Minh họa ví dụ về sử dụng nhóm hàm điều kiện
Tính tổng giá trị các ô trong vùng C4:C7 sao cho các giá trị tương ứng trong vùng B4:B7 phải lớn hơn 5, như vậy các ô có giá trị 6, 8, 6 trong vùng B4:B7 thoả mãn điều kiệnl là lớn hơn 5, tương ứng với các ô có giá trị 9, 9, 4 trong vùng C4:C7, kết quả tổng bằng 22. 3. COUNTIF(vùng, "điều kiện"): Đếm các ô thoả mãn điều kiện trong vùng. (Lấy dữ liệu của B4:B7 ở ví dụ trên – Hình 4.9) =COUNTIF(B4:B7, “<5”) {=1} 4.4.2.6. Nhóm hàm logic: (Xem hình minh họa về nhóm hàm này Hình 3.10) 1. AND(các biểu thức điều kiện): Trả về giá trị “Và” logic (True hay False) của các biểu thức điều kiện. =IF(AND(B3>5, E3=”A”), “Đ”, “H”) Nếu B3 lớn hơn 5 và (AND) E4 là A thì cho kết quả là “Đ”, ngược lại kết quả là “H”. 2. OR(các biểu thức điều kiện): Trả về giá trị “Hoặc” logic (True hay False) của các biểu thức điều kiện. =IF(OR(B3<5, C3<1), “Không đạt”, “Đạt”) Nếu B3 nhỏ hơn 5 hoặc (OR) C3 nhỏ hơn 1 thì cho kết quả là “Không đạt”, ngược lại kết quả là “Đạt”. 3. NOT(các biểu thức điều kiện): Trả về giá trị “Không” logic (True hay False) của các biểu thức điều kiện.
=IF(AND(B3>5, NOT(C3=0)), “Đ”, “H”) Nếu B3 lớn hơn 5 và (AND) C3 không (NOT) bằng 0 thì kết quả là “Đ”, ngược lại sẽ cho kết quả là “H”.
Hình 4.10. Minh họa ví dụ về sử dụng nhóm hàm logic 4.4.2.7. Nhóm hàm tìm kiếm: 1. VLOOKUP(x, vùng tham chiếu, n, 0): Tìm giá trị x ở cột thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở cột thứ n. (Hình 4.11) Dựa vào bảng mã hàng điền tên thích hợp vào cột tên hàng của bảng phiếu giao hàng. Lập công thức cho ô C13: = VLOOKUP(B13, $A$2:$B$5, 2, 0) {kết quả=Sắt} Tìm một giá trị bằng giá trị của ô B13 trong cột thứ nhất của vùng A2:B5 và lấy giá trị tương ứng ở cột thứ 2 (kết quả =Sắt). .
Hình 4.11. Minh họa ví dụ về sử dụng nhóm hàm tìm kiếm
* Trong công thức nên đặt giá trị tuyệt đối cho địa chỉ vùng để không bị thay đổi khi sao chép.
2. HLOOKUP(x, vùng tham chiếu, n, 0): Tìm giá trị x ở dòng thứ nhất trong vùng tham chiếu và lấy giá trị tương ứng ở dòng thứ n. (Xem hình bên): Dựa vào bảng giá để điền vào cột giá của bảng phiếu giao hàng. Lập công thức cho ô D13: =HLOOKUP(B13, $A$7:$D$8, 2, 0) {=100} Tương tự như hàm VLOOKUP nhưng tìm theo dòng thay vì cột
4.5. QUẢN TRỊ DỮ LIỆU 4.5.1. Khái niệm về cơ sở dữ liệu: * Trên một cơ sở dữ liệu dạng bảng của Excel ta có thể: Lọc (Filter), xoá (Delete) rút trích (Extract) những dòng dữ liệu thoả mãn một tiêu chuẩn nào đó. Để thực hiện các thao tác này cần tạo ra những vùng sau: 1. Vùng dữ liệu (Database): Là vùng cơ sở dữ liệu gồm ít nhất 2 dòng (Row). Dòng đầu tiên chứa các tiêu đề cột (Field name), các dòng còn lại chứa dữ liệu gọi là mẩu tin (Record). 2. Vùng tiêu chuẩn (Criteria): Là vùng tiêu chuẩn chứa điều kiện để tìm kiếm, xoá, rút trích hay trích lọc. Vùng này gồm ít nhất 2 dòng. Dòng đầu chứa tiêu đề (Field name), các dòng còn lại chứa điều kiện.
Hình 4.12. Minh họa về cơ sở dữ liệu
3. Vùng rút trích (Extract): Là vùng trích dữ liệu chứa các mẩu tin của vùng dữ liệu (Database) thoả điều kiện của vùng tiêu chuẩn. Vùng rút trích (Extract) cũng có dòng đầu tiên chứa các tiêu đề muốn rút trích.
4.5.2. Sắp xếp dữ liệu: - Đưa con trỏ ô vào vùng dữ liệu cần sắp xếp. - Thực hiện Data / Sort, hộp thoại Sort xuất hiện: - Chọn cột cần sắp xếp trong hộp Sort by. Chọn Ascending để sắp xếp tăng dần. Chọn Descending để sắp xếp giảm dần. - Nếu muốn sắp xếp cho 2 hay 3 cột cùng lúc, chọn chúng trong các hộp Then by còn lại. - Chọn Header row để xác nhận chỉ sắp xếp các dòng chứa thông tin (các bản ghi) trong vùng dữ liệu (không sắp xếp dòng tiêu đề). - Chọn No header row để sắp xếp cả dòng tiêu đề. - Nhấn OK.
Hình 4.13. Hộp thoại xắp xếp dữ liệu
4.5.3. Lọc, rút trích dữ liệu: - Khi muốn tìm kiếm hay liệt kê các mẩu tin (Record) theo một yêu cầu nào đó. Trong trường hợp này ta dùng phương pháp lọc (Filter) hoặc phương pháp rút trích dữ liệu (Extract). 4.5.3.1. Lọc tự động: - Đưa con trỏ ô vào vùng cần lọc (hoặc chọn vùng cần lọc). - Thực hiện Data\Filter\Autofilter. - Khi đó các nút mũi tên sẽ xuất hiện tại tiêu đề các cột trong vùng cơ sở dữ liệu.
Hình 4.14a. Minh họa ví dụ lọc dữ liệu tự động
- Nhấn chuột vào nút mũi tên để chọn các giá trị của cột cần xác định điều kiện lọc. Khi đó các mẩu tin thoả điều kiện sẽ được hiển thị còn các mẩu tin không thoả sẽ bị dấu (hide) đi. - Muốn hiển thị lại các mẩu tin, chọn Data\Filter\Show all. - Muốn bỏ chế độ lọc, chọn Data\Filter\Autofilter một lần nữa. * Trong danh sách còn có những lựa chọn khác: - All: Hiện trở lại tất cả các bản ghi. - Top 10: Xuất hiện hộp thoại Top 10 AutoFilter cho phép lọc ra một số bản ghi chứa giá trị cao (Top) hoặc thấp (Bottom).
Hình 4.14b. Hộp thoại thiết lập lọc dữ liệu theo các chỉ tiêu lựa chọn có sẵn + Chọn Top hay Bottom trong hộp thứ nhất. + Thay đổi số bản ghi cần lấy trong hộp thứ hai. + Hộp thứ ba cho phép xác định số bản ghi cần lấy theo số đếm bình thường (Items) hay theo đơn vị phần trăm (Percent).
- Custom: Xuất hiện hộp thoại Custom AutoFilter cho phép lọc theo các điều kiện phức hợp.
Hình 4.14c. Hộp thoại đặt lọc dữ liệu theo điều kiện * Nếu có một điều kiện lọc thì chỉ cần sử dụng hai hộp danh sách bên trên như sau: + Chọn một toán tử so sánh trong hộp danh sách bên trái: - equals = - does not equal <> - is greater than > - is greater than or equal to >= - is less than < - is less than or equal to <= - begins with bắt đầu bằng kí tự... - does not begin with không bắt đầu bằng kí tự... - ends with kết thúc bằng kí tự... - does not end with không kết thúc bằng kí tự... - contains có chứa kí tự... - does not contain không chứa kí tự... + Nhập vào hộp bên phải giá trị cần so sánh. * Trường hợp có hai điều kiện lọc thì phải lặp lại các thao tác trên đối với 2 hộp danh sách còn lại (chọn toán tử so sánh trong hộp bên trái, nhập giá trị cần so sánh vào hộp bên phải). + Chọn And để kết hợp hai điều kiện trên theo tiêu chuẩn "và" (giống hàm And). + Chọn Or để kết hợp hai điều kiện trên theo tiêu chuẩn "hoặc" (giống hàm Or). + Nhấn OK.
* Ví dụ: Cho bảng danh sách các học sinh trong lớp. Yêu cầu 1: Hiện ra màn hình những học sinh tiên tiến (có điểm trung bình từ 7,5 đến 8,5). - Thực hiện vào menu Data Filter AutoFilter để xuất hiện các nút mũi tên bên phải các tiêu đề cột. - Nhấn nút mũi tên tại cột "Điểm trung bình" và chọn Custom trong danh sách hiện ra. - Nhập vào hộp Custom AutoFilter các giá trị như sau rồi nhấn OK.
Hình 4.15a. Minh họa ví dụ thiết lập các điều kiện đặt lọc Yêu cầu 2: Tìm những học sinh có họ Lê. - Nhấn nút mũi tên tại cột "Họ tên" và chọn Custom trong danh sách hiện ra. - Nhập vào hộp Custom AutoFilter các giá trị như sau rồi nhấn OK.
Hình 4.15b. Minh họa ví dụ về thiết lập điều kiện đặt lọc 4.5.3.2. Rút trích dữ liệu: * Trong thao tác này ta cần xác định vùng dữ liệu (Database) và tạo trước vùng tiêu chuẩn (Criteria) cùng vùng rút trích (Extract). - Đưa con trỏ ô vào ô bất kì trong vùng dữ liệu. - Thực hiện Data / Filter / Advanced Filter, xuất hiện hộp thoại Advanced Filter.
- Chọn Filter the list, in-place: Các mẩu tin đã trích lọc sẽ xuất hiện trong vùng dữ liệu này. - Chọn Copy to another location: Đưa các mẩu tin đã trích lọc sang vùng khác. - Nhập địa chỉ của vùng dữ liệu cần trích lọc trong hộp List range. Nhập địa chỉ của vùng điều kiện (tiêu chuẩn) trong hộp Criteria range. Nhập địa chỉ ô đầu của vùng rút trích trong hộp Copy to.
Hình 4.16a Hình 4.16b - Hình 4.16a: Hộp thoại thiết lập các thuộc tính trích rút dữ liệu - Hình 4.16b: Minh họa ví dụ về trích rút dữ liệu - Nếu chọn Unique record only, dữ liệu lọc ra mà có những dòng trùng nhau sẽ chỉ hiện một dòng. - Nhấn OK để thực hiện.
4.5.4. Các dạng tiêu chuẩn: 4.5.4.1. Tiêu chuẩn số: - Ô điều kiện có kiểu số Văn 6 3.5.4.2. Tiêu chuẩn chuỗi: - Ô điều kiện có kiểu chuỗi Họ tên T* - Trong ô điều kiện có thể chứa các kí tự gộp * : Thể hiện cho một nhóm kí tự ? : Thể hiện cho một kí tự bất kì Ví dụ: Cho biết danh sách sinh viên có họ tên bắt đầu là chữ T
Hình 4.17a. Minh họa ví dụ về sử dụng các tiêu chuẩn để trích rút dữ liệu
4.5.4.3. Tiêu chuẩn so sánh: - Ô điều kiện chứa toán tử so sánh kèm với giá trị so sánh. Văn >6 - Các toán tử so sánh: > : Lớn hơn >= : Lớn hơn hoặc bằng < : Nhỏ hơn <= : Nhỏ hơn hoặc bằng = : Bằng <> : Không bằng Hình 4.17b. Minh họa ví dụ về sử dụng Ví dụ: Cho biết danh sách các các tiêu chuẩn để trích rút dữ liệu sinh viên có điểm văn > 6. 4.5.4.4. Tiêu chuẩn công thức: - Ô điều kiện có kiểu công thức, trường hợp này cần lưu ý 2 yêu cầu sau:
+ Ô tiêu đề của vùng tiêu chuẩn phải khác với tất cả các tiêu đề của vùng dữ liệu. + Trong ô điều kiện phải lấy địa chỉ của ô trong mẩu tin đầu tiên (sau dòng tiêu đề hay là dòng thứ 2 của vùng dữ liệu) để so sánh (ví dụ: Các ô B3, C3, D3). Ví dụ: Trích lọc danh sách các sinh viên có tổng điểm > 15. Tổng =Sum(B3:D3)>15 Trong vùng điều kiện tên tiêu đề đặt là Tổng (khác với các tiêu đề vùng dữ liệu), công thức trong ô điều kiện được xác lập là =Sum(B3:D3)>15. Kết quả như hình bên. Hình 4.17c. Minh họa ví dụ về sử dụng các tiêu chuẩn để trích rút dữ liệu
4.5.4.5. Tiêu chuẩn liên kết: - Có thể lọc, xoá hay rút trích các mẩu tin trong vùng dữ liệu bằng phép giao (And) hay phép hợp (Or) của nhiều điều kiện khác nhau. - Phép AND: Nếu các ô điều kiện cùng dòng. - Phép OR: Nếu các ô điều kiện khác dòng. Ví dụ 1: Trích lọc danh sách sinh viên có điểm Văn > 5 và điểm Địa > 5. Văn >5 Văn >6 6 Địa >5
Ví dụ 2: Trích lọc danh sách sinh viên có điểm Văn lớn hơn hoặc bằng 6.
Hình 4.18. Minh họa ví dụ về sử dụng các tiêu chuẩn liên kết AND, OR để đặt lọc dữ liệu
4.6. HOÀN THIỆN TÀI LIỆU 4.6.1. Các thiết lập trang in: Thực hiện File/Page Setup, xuất hiện hộp thoại Page Setup gồm 4 tab:
Hình 4.19a. Hộp thoại thiết lập trang in - về khổ giấy, hướng giấy 4.6.1.1. Tab Page: - Portrait: In bảng tính theo chiều dọc trang giấy. - Landscape: In bảng tính theo chiều ngang trang giấy. - Paper size: Chọn khổ giấy (thường là khổ A4). - Print quality: Chọn độ phân giải khi in (độ phân giải càng cao bản in càng đẹp). - First page number: Xác định số trang bắt đầu. 4.6.1.2. Tab Margins: (Xác định các lề cho trang in)
Hình 4.19b. Hộp thoại thiết lập trang in - về lề - Top: Thay đổi lề trên trang giấy. - Bottom: Thay đổi lề dưới trang giấy. - Left: Thay đổi lề trái trang giấy. - Right: Thay đổi lề phải trang giấy. - Header: Xác định khoảng cách cho tiêu đề đầu. - Footer: Xác định khoảng cách cho tiêu đề cuối. - Horizontally: Căn bảng tính giữa trang giấy theo chiều ngang. - Vertically: Căn bảng tính giữa trang giấy theo chiều dọc. 4.6.1.3. Tab Header/Footer: (Tạo tiêu đề đầu và cuối cho trang in)
Hình 4.19c. Hộp thoại thiết lập trang in - về tiêu đề Sử dụng hộp Header và hộp Footer để chọn một kiểu tiêu đề có sẵn. Hoặc có thể nhấn vào nút Custom Header hay nút Custom Footer để tự tạo tiêu đề đầu và tiêu đề cuối. Hộp thoại Header hay Footer tương ứng sẽ hiện ra gồm 3 phần: + Left Section: Phần bên trái tiêu đề. + Center Section: Phần giữa của tiêu đề.
Hình 4.20. Hộp thoại thiết đặt tiêu đề trên của trang in (Header) + Right Section: Phần bên phải tiêu đề. - Người sử dụng có thể nhập bất kì văn bản nào cho mỗi phần. Ngoài ra có thể nhấn vào các nút phía trên của hộp thoại để thêm những thông tin đặc biệt vào các phần của tiêu đề: Thay đổi font chữ. Chèn số trang. Chèn tổng số trang. Chèn ngày tháng năm hiện hành. Chèn giờ hiện hành. Chèn tên file. Chèn tên bảng tính. 4.6.1.4. Tab Sheet:
Hình 4.21. Hộp thoại thiết lập các thuộc tính cho trang in - Print area: (Vùng in) Nhập địa chỉ vùng cần in vào hộp. - Rows to repeat at top: Chọn hàng cần lặp lại trên đỉnh mỗi trang in. - Columns to repeat at left: Chọn cột cần lặp lại bên trái mỗi trang. - Gridlines: Xác nhận in cả các đường lưới. - Black and while: Chỉ in hai màu đen và trắng. - Draft quality: In chất lượng thấp nhưng giảm được thời gian in. - Row and column headings: In cả các nhãn hàng, nhãn cột. - Down, then over: In các trang theo thứ tự từ trên xuống dưới. - Over, then down: In các trang theo thứ tự từ trái sang phải. 4.6.2. Xem trước khi in: Chọn File/Print Preview hoặc nhấn nút Print Preview trên thanh công cụ. Màn hình Preview hiện ra cho phép xem toàn thể từng trang bảng tính.
Hình 4.22. Màn hình kết hiển thị trang in khi thực hiện lện xem trước khi in Sử dụng các nút: - Next: - Previous: - Zoom: - Print: - Setup: - Margins:
Tới trang tiếp theo. Về trang trước. Phóng to / thu nhỏ trang in. In. Mở hộp thoại Page Setup. Làm xuất hiện các dấu định lề quanh trang giấy. Dùng chuột kéo các dấu này để thay đổi các lề của trang. - Page Break Preview: Cho phép thay đổi dấu ngắt trang bằng cách kéo chuột. - Close: Thoát khỏi chế độ xem trước khi in. - Help: Hiện màn hình trợ giúp.
4.6.3. In tài liệu: Thực hiện File/Print hoặc chọn biểu tượng Print xuất hiện hộp thoại Print:
trên thanh công cụ,
Hình 4.23. Hộp thoại thiết lập máy in và trang in - Chọn kiểu máy in trong hộp Name. - Chọn All để tất cả các trang. - Chọn Page(s): chỉ in những trang được chỉ định trong hai hộp From và To. - Selection: Chỉ in vùng được chọn (bôi đen). - Entire workbook: In tất cả các bảng tính trong file. - Active sheet(s): Chỉ in bảng tính hiện hành. - Number of copies: Xác định số bản sao. - Collate: Cho phép in từng bộ bản sao riêng biệt. Nhấn OK để thực hiện in.