Bài giảng Sử dụng phần mềm bảng tính điện tử Microsoft Excel

* Nội dung Bài 1: Tổng quan về MS-Excel Bài 2: Thao tác nhập và định dạng dữ liệu trong bảng tính Bài 3: Công thức và hàm Bài 4: Đồ thị và in ấn Bài 5: Cơ sở dữ liệu trong MS-Excel * §1 Tổng quan về MS-Excel Giới thiệu Bảng tính điện tử MS-Excel là phần mềm chuyên dùng cho công việc quản lý số liệu chứa nhiều công thức tính toán tại các công sở, trường học như tính toán bảng lương, thống kê tài sản, lập bảng điểm thi, xếp loại học sinh, … Bảng tính điện tử là bảng có kích thước lớn gồm nhiều cột (column) và nhiều hàng (row) tạo thành các đơn vị lưu trữ và xử lý dữ liệu gọi là ô (cell). * Một số thao tác cơ bản Khởi động MS-Excel Cách 1: Nhấn chuột vào nút Start, chọn Program, chọn Microsoft office và chọn Ms-Excel Cách 2: Nhấp đúp chuột vào biểu tượng chương trình Ms-Excel trên Desktop * Sau khi khởi động chương trình MS-Excel, chúng ta có một sổ bảng tính (workbook). *  Cửa sổ bảng tính MS-Excel có các thành phần chính sau: Thanh tiêu đề: cho biết tên tập tin Thanh thực đơn lệnh: cung cấp các nhóm lệnh làm việc với bảng tính Thanh công cụ: cung cấp các nút thao tác nhanh Thanh công thức (Formula Bar): hiển thị tọa độ ô soạn thảo và nội dung dữ liệu của ô. * Đường viền ngang: ghi tên cột Đường viền dọc: ghi số thứ tự dòng Thanh trượt ngang và dọc: cho phép hiển thị những ô bị che khuất Thanh trạng thái: cho biết thông tin về bảng tính. * Trong một sổ bảng tính có nhiều trang bảng tính (Sheet). Một sổ bảng tính có thể chứa từ 1 đến 255 trang bảng tính. Một trang bảng tính gồm có 256 cột được đánh chỉ số theo chữ cái A,B,C, … Z,AA,AB,AC, … và 65536 dòng được đánh chỉ số theo số thứ tự 1,2,3 … 65536. Theo mặc định, mỗi lần tạo bảng tính mới thì MS_Excel tạo 3 trang bảng tính trắng đặt tên là Sheet1, Sheet2, Sheet3. Sau này chúng ta có thể chèn thêm các trang mới và chúng được đặt tên là Sheet4, Sheet5, … *   Các thao tác cơ bản về Sheet Sheet đang làm việc hoặc đang được chọn gọi là Sheet hiện hành. Muốn chọn Sheet nào ta click vào Sheet đó trên Sheet Tab. Click phải chuột tại Sheet Tab để kích hoạt ShortCut menu gồm: Insert: chèn thêm Sheet vào vị trí Sheet hiện hành Delete: xóa Sheet hiện hành Rename: đổi tên Sheet hiện hành Move or copy: di chuyển hoặc sao chép Sheet Select all Sheets: chọn tất cả các Sheet *  Ô (cell) Ô của trang bảng tính là giao của cột và dòng. Ô trên trang bảng tính có địa chỉ viết theo trật tự xác định gồm chữ cái tên cột đứng trước và số thứ tự dòng đứng sau. Ví dụ địa chỉ của ô đầu tiên là A1 và địa chỉ của ô cuối cùng là IV65536. * Tạo bảng tính mới theo mẫu mặc định Nhắp chuột vào biểu tượng New trên thanh công cụ hoặc nhấn tổ hợp phím Ctrl + N. * Tạo bảng tính mới theo mẫu lựa chọn Nhắp chuột vào menu File -> New làm xuất hiện hộp thoại chọn mẫu * Ghi lưu bảng tính Nhắp chuột vào menu File -> Save (hoặc nhắp chuột vào biểu tượng Save ). Xuất hiện hộp thoại Save as: * Gõ tên tệp vào ô File name (nên gõ không dấu) Xác định ổ đĩa, thư mục chứa tệp tin bằng cách chọn trong hộp Save in Nhấn nút Save để hoàn tất việc ghi lưu. * Đóng bảng tính, đóng chương trình Excel Nhắp chọn thực đơn lệnh File -> Close để đóng bảng tính đang làm việc Nhắp chọn thực đơn lệnh File -> Exit để đóng chương trình MS-Excel hoặc nhấn nút tại góc trên bên phải màn hình. * Mở một hoặc nhiều bảng tính Nhắp chọn menu File -> Open (hoặc nhắp chuột vào biểu tượng Open trên thanh công cụ). Xuất hiện hộp thoại: * Tìm thư mục chứa tệp tin trong hộp Look in Chọn các tệp tin cần mở rồi nhấp nút Open * §2. THAO TÁC NHẬP VÀ ĐỊNH DẠNG DỮ LIỆU TRONG BẢNG TÍNH Nhập dữ liệu vào bảng tính Dữ liệu một ô trong bảng tính sau khi nhập sẽ được Excel tự động phân loại và đưa về một trong các kiểu dữ liệu sau: Kiểu số (Number) Kiểu chuỗi (Text) Kiểu logic Kiểu mã lỗi (Error). * Nhập dữ liệu kiểu số Mặc định được căn theo lề phải của ô Dữ liệu kiểu số Ví dụ: 789, -789, 7.89, 7.89E+08 Số âm: gõ dấu “-” trước số hoặc đưa số đó vào cặp dấu ngoặc đơn - “( số )” Dấu “.” để ngăn cách giữa phần nguyên và phần thập phân * Dữ liệu kiểu ngày tháng Cách thức nhập được quy định trong mục Regional Settings trong cửa sổ Control Panel Thứ tự nhập mặc định: tháng/ngày/năm Chú ý: phải nhập giá trị ngày tháng theo đúng quy định được đặt trong mục Regional Settings * Nhập dữ liệu kiểu văn bản Mặc định được căn theo lề trái của ô Ví dụ: 10AA109, 208 675 Sử dụng dấu nháy đơn “ ‘ ”, dấu nháy kép “ “ “ để ép kiểu Ví dụ: ‘232323 được hiểu là một xâu ký tự có nội dung 232323 * Nhập dữ liệu vào 1 ô Di chuyển con trỏ đến ô cần nhập Nhập dữ liệu tùy theo kiểu đã qui định Kết thúc nhập dữ liệu ta di chuyển con trỏ ra khỏi ô. Để sửa đổi dữ liệu tại một ô ta nhấn phím F2 hoặc nhấn đúp chuột tại ô đó. * 2. Nhập cùng dữ liệu cho một nhóm các ô Chọn phạm vi nhóm ô cần nhập dữ liệu Nhập dữ liệu tùy theo kiểu đã qui định Nhấn tổ hợp phím Ctrl+Enter * Công cụ điền nội dung tự động Điền tự động theo cấp số cộng Nhập giá trị cho 2 ô đầu tiên theo quy luật cấp số cộng, ví dụ: 1, 4 Chọn 2 ô vừa nhập Đưa con trỏ chuột vào hình vuông ở góc phải dưới của vùng vừa chọn Nhấn vào kéo chuột theo chiều dọc, ngang như mong muốn * b) Điền tự động theo chuỗi dữ liệu Nhập 2 chuỗi cho 2 ô đầu tiên theo quy luật, ví dụ: 05TC0001, 05TC0002 Chọn 2 ô vừa nhập Đưa con trỏ chuột vào hình vuông ở góc phải dưới của vùng vừa chọn Nhấn vào kéo chuột theo chiều dọc, ngang như mong muốn * c) Điền tự động theo cấp số nhân Nhập giá trị cho 2 ô đầu tiên theo quy luật cấp số nhân, ví dụ: 1, 3 Chọn 2 ô vừa nhập Đưa con trỏ chuột vào hình vuông ở góc phải dưới của vùng vừa chọn Nhấn phải chuột vào kéo chuột theo chiều dọc, ngang như mong muốn Đến ô cuối cùng nhả chuột phải Chọn Growth Trend * II. Các thao tác về ô, cột, hàng Các đối tượng hay dùng * 1. Sao chép, di chuyển, xóa các ô a) Sao chép các ô Chọn các ô muốn sao chép Vào menu Edit/ Copy (hoặc nhấp nút Copy hoặc bấm tổ hợp phím Ctrl+C) Chọn ô muốn sao chép đến Vào menu Edit/ Paste (hoặc nhấn nút Paste hoặc bấm tổ hợp phím Ctrl+V) (Thực hiện tương tự khi sao chép các ô sang trang bảng tính khác) * b) Di chuyển các ô Chọn các ô muốn di chuyển Vào menu Edit/ Cut (hoặc nhấp nút Cut hoặc nhấn tổ hợp phím Ctrl+X) Chọn ô muốn di chuyển đến Vào menu Edit/ Paste (hoặc nhấn nút Paste hoặc nhấn tổ hợp phím Ctrl+V) (Thực hiện tương tự khi chuyển các ô sang trang bảng tính khác) * Xóa nội dung các ô Chọn các ô cần xóa Vào menu Edit /Clear / Contents (Hoặc nhấn phím Delete) * 2. Thêm/bớt ô, dòng, cột Thêm dòng Chọn dòng muốn chèn dòng mới lên trên nó Vào menu Insert / Rows Thêm cột Chọn cột muốn chèn cột mới bên trái nó Vào menu Insert / Column * Thêm ô Chọn ô muốn thêm ô mới bên cạnh nó Vào menu Insert  Cell Xuất hiện hộp thoại: Chọn Shift cells right: chèn ô trống và đẩy ô hiện tại sang phải Chọn Shift cells down: chèn ô trống và đẩy ô hiện tại xuống dưới Chọn Entire row: chèn một dòng mới lên trên Chọn Entrire column: chèn cột mới sang trái * Xóa vùng ô Chọn vùng ô muốn xóa Trên thanh thực đơn chọn EditDelete Xuất hiện hộp thoại Chọn Shift cells left: xóa các ô và đẩy ô bên trái sang Chọn Shift cells up: xóa các ô và đẩy các ô bên phải sang Chọn Entire row: xóa các dòng có ô đang chọn Chọn Entrire column: xóa các cột có ô đang chọn * a) Thay đổi độ rộng cột Cách 1: Chuyển con trỏ chuột vào cạnh phải của tiêu đề cột. Sau đó nhấn và kéo di chuột sang phải/ trái để tăng/giảm kích thước chiều rộng cột Cách 2: Chọn menu Format -> Column -> Width. Nhập kích thước cột, nhấn OK Thay đổi kích thước cột, hàng * b) Thay đổi chiều cao dòng Cách 1 : Chuyển con trỏ chuột vào cạnh dưới của tiêu đề dòng. Nhấn và kéo di chuột xuống dưới/lên trên để tăng/giảm độ cao dòng. Cách 2: Chọn menu Format -> Row -> Height. Nhập chiều cao dòng. Nhấp nút OK để kết thúc * Ẩn/hiện cột Ẩn cột Chọn các cột muốn ẩn Vào menu Format  Column  Hide Hiện cột Chọn vùng cột chứa các cột đang bị ẩn Vào menu Format  Column  Unhide Ẩn/ hiện cột, hàng * Ẩn/hiện hàng Ẩn hàng Chọn các hàng muốn ẩn Vào menu Format  Row  Hide Hiện hàng Chọn vùng hàng chứa các hàng đang bị ẩn Vào menu Format  Row  Unhide * III. Thao tác định dạng Cách 1: Các thao tác định dạng ô được thực hiện với các nút chức năng trên thanh công cụ Formating Phông chữ Cỡ chữ Kiểu chữ Căn lề Định dạng kiểu số Kẻ đường viền * Ý nghĩa một số nút lệnh Thêm kí hiệu tiền tệ Thêm kí hiệu phần trăm Thêm dấu phẩy phân cách phần nghìn Tăng thêm 1 chữ số phần thập phân Giảm bớt 1 chữ số phần thập phân * Cách 2: nhắp chọn menu Format -> Cells làm xuất hiện hộp hội thoại Format Cells * Thẻ Number: Dạng biểu diễn dữ liệu trong ô. Các dạng biểu diễn dữ liệu General: dạng chung Number: dạng số Currency: dạng tiền tệ Date: dạng ngày tháng Time: dạng thời gian Text: dạng văn bản Custom: dạng người dùng tự định nghĩa * Thẻ Font: Thay đổi kiểu chữ, cỡ chữ, dạng chữ (Các thao tác tương tự trong MS-Word) Thẻ Patterns: thay đổi màu chữ và màu nền ô: Thay đổi mầu trong bảng Color Chọn mẫu hoa văn trong mục Pattern * Thẻ Aligment: Vị trí dữ liệu trong ô Text aligment: Chọn hướng căn chỉnh lề Horizontal: căn chỉnh theo chiều ngang Vertical: căn chỉnh theo chiều dọc Wrap text: Cho phép hiển thị dữ liệu bằng nhiều dòng Merge cell: Trộn cell. Orientation: Thay đổi hướng văn bản * Thẻ Border: Tạo đường viền cho các ô Chọn kiểu nét trong hộp Style Chọn mẫu đường trong hộp Color Chọn một đường viền cụ thể để áp dụng trong hộp Border * §3. CÔNG THỨC VÀ HÀM Các toán tử dùng trong Excel Toán tử số học: + (cộng), -(trừ), *(nhân), /(chia), ^ (dấu mũ),% (lấy phần trăm) Toán tử so sánh: = (bằng), > (lớn hơn), = (lớn hơn hoặc bằng), (không bằng) Toán tử ghép chuỗi: & * Thứ tự ưu tiên các phép toán - : dấu âm % : lấy phần trăm ^ : dấu mũ *, / & Các phép toán so sánh * 2) Cách tạo công thức cơ bản Trước tiên nhập ký tự =, sau đó nhập nội dung công thức. Nhấn phím Enter để kết thúc. * Nhận biết và sửa lỗi ####: không đủ độ rộng của ô để hiển thị, #VALUE!: dữ liệu không đúng theo yêu cầu của công thức #DIV/0!: chia cho giá trị 0 #NAME?: không xác định được ký tự trong công thức #N/A: không có dữ liệu để tính toán #NUM!: dữ liệu không đúng kiểu số  Sửa lại cho phù hợp với yêu cầu * 3) CÁC LOẠI ĐỊA CHỈ 3.1 Địa chỉ tương đối (Relative Address) Ví dụ: A7, H14, AB12 Địa chỉ tương đối có trong công thức sẽ thay đổi theo vị trí ô khi chúng ta thực hiện sao chép công thức từ một ô đến các ô khác. * Ví dụ: Công thức ô D2 là =B2*C2 Khi sao chép sang ô D3 sẽ là = B3*C3 Khi sao chép sang ô F4 sẽ là = D4*E4 * 3.2 Địa chỉ tuyệt đối Ví dụ: $A$7, $H$14, $AB$12 Địa chỉ tuyệt đối có trong công thức sẽ cố định không thay đổi khi chúng ta thực hiện sao chép công thức từ một ô đến các ô khác. Ví dụ: Công thức ô D2 là =B2*$C$2 Khi sao chép sang ô D3 sẽ là = B3*$C$2 Khi sao chép sang ô F4 sẽ là = $$ D4*$C$2 * 3.3 Địa chỉ hỗn hợp Ví dụ: $A7, H$14, AB$12 Địa chỉ hỗn hợp có trong công thức sẽ thay đổi theo phần tương đối, không thay đổi theo phần tuyệt đối khi chúng ta thực hiện sao chép công thức từ một ô đến các ô khác. Ví dụ: Công thức ô D2 là =B$2*$C2 Khi sao chép sang ô D3 sẽ là = B$2*$C3 Khi sao chép sang ô F4 sẽ là = $ hoặc $ D$2*$C4 * 4) Sử dụng hàm 4.1 Khái niệm hàm : Hàm là một đoạn chương trình được viết sẵn nhằm thực hiện các thao tác tính toán nào đó. Hàm luôn trả về giá trị thuộc một kiểu dữ liệu. * Dạng tổng quát của hàm : Tên hàm (các đối số). Phần lớn các hàm trong MS-Excel đều có một hoặc nhiều đối số. Đối số có thể là một giá trị kiểu số, một xâu kí tự, địa chỉ ô hay vùng ô, công thức hay những hàm khác. Tên hàm được sử dụng theo qui ước của Excel. Có 2 loại hàm là hàm có sẵn và hàm do người dùng tự viết. * Nhập hàm Cách 1: gõ trực tiếp vào ô theo dạng “=(đối số 1, đối số 2,…, đối số n)” Cách 2: Chuyển con trỏ ô về ô muốn nhập công thức Trên thanh thực đơn chọn Insert  Function Chọn loại hàm trong mục Function Category Chọn hàm trong mục Function name Nhấn nút OK Nhập các đối số cần thiết Nhấn nút OK để hoàn tất * 4.2 Các hàm cơ bản : Nhóm các hàm số Hàm ABS Cú pháp : ABS(Number) Chức năng : Tính giá trị tuyệt đối của số Number Ví dụ : =ABS(-5) cho kết quả là 5 * Hàm SQRT Cú pháp : SQRT (Number) Chức năng : Tính căn bậc hai của số Number Ví dụ : = SQRT (9) cho kết quả là 3 * Hàm INT Cú pháp : INT (Number) Chức năng : Tính phần nguyên của số Number Ví dụ : = INT (4.135) cho kết quả là 4 = INT(6.78) cho kết quả là 6 * Hàm ROUND Cú pháp: ROUND(đối số 1, đối số 2) Đối số 1: số muốn làm tròn Đối số 2: số thập phân muốn làm tròn Làm tròn đến một số thập phân nhất định Ví dụ: ROUND(2.578,2)= 2.58 ROUND(153.5,0)= ROUND(153.5,-2)= 154 200 * Hàm MOD Cú pháp : MOD (đối số 1, đối số 2) Chức năng : Tính phần dư của phép chia đối số 1 cho đối số 2 Ví dụ : = MOD (7,6) cho kết quả là 1 *  Nhóm hàm thống kê * Hàm MAX Cú pháp: MAX(đối số 1, đối số 2,…) Trả lại số lớn nhất trong danh sách Ví dụ: MAX(6,7,2,9,13)=13 MAX(D2:D6) = 9 * Hàm MIN Cú pháp: MIN(đối số 1, đối số 2,…) Trả lại số nhỏ nhất trong danh sách Ví dụ: MIN(6,7,2,9,13)=2 MIN(D2:D6) = 5 * Hàm AVERAGE Cú pháp: AVERAGE(đối số 1, đối số 2,…) Trả lại giá trị trung bình cộng của các số trong danh sách Ví dụ: AVERAGE(6,7,14)=9 AVERAGE (D2:F2)=8 * Hàm SUM Cú pháp: SUM (danh sách đối số) Chức năng: Tính tổng các giá trị của danh sách đối số Ví dụ: = SUM (7,3,6) cho kết quả là 16 = SUM (D2 :D6) cho kết quả là 34 * Hàm SUMIF Cú pháp: SUMIF (vùng ước lượng, điều kiện, vùng tính toán) Vùng ước lượng: là 1 vùng ô Điều kiện: có thể là hằng số, địa chỉ ô hay dạng thức “>160” Vùng tính toán: các ô thật sự cần tính toán. Nếu bỏ qua tham số này thì vùng ước lượng được lấy làm vùng tính toán. Chức năng: Tính tổng theo điều kiện * Ví dụ: Tính tổng điểm Toán của những người sinh năm 1988 = SUMIF (C2:C6,1988,D2:D6) cho kết quả là 14 * COUNT Cú pháp: COUNT(đối số 1, đối số 2,…) Đếm những ô chứa dữ liệu số Ví dụ: COUNT(6,7,”Nữ”,2,9,13,”Nam”)=5 COUNTA Cú pháp: COUNTA(đối số 1, đối số 2,…) Đếm những ô chứa dữ liệu trong vùng Ví dụ: COUNTA(6,7,”Nữ”,2,9,13,”Nam”)=7 * Hàm COUNTIF Cú pháp: COUNTIF (vùng ước lượng, điều kiện) Vùng ước lượng: là 1 vùng ô Điều kiện: có thể là hằng số, địa chỉ ô hay dạng thức “>160” Chức năng: Đếm các ô trong vùng ước lượng thỏa điều kiện. * Ví dụ: 1) Có bao nhiêu người sinh năm 1988 = COUNTIF (C2:C6,1988) cho kết quả là 2 2) Có bao nhiêu người đạt điểm toán trên 6 = COUNTIF (D2:D6,”>6”) cho kết quả là 3 * Hàm RANK Cú pháp: RANK (x, danh sách l, d) Chức năng: Tìm thứ hạng của x trong danh sách l theo qui định của d Nếu d=0 : thứ hạng theo giá trị giảm dần. Nếu d=1 : thứ hạng theo giá trị tăng dần. Ví dụ: = RANK (G2,$G$2:$G$6,0) *  Nhóm hàm chuỗi Hàm LEFT Cú pháp: LEFT (Text, n) Chức năng: Trả về n ký tự tính từ trái của chuỗi Text. Ví dụ: = LEFT (“spring”,2) cho kết quả là “sp” = LEFT(B2,3) Cho kết quả là “Trú” * Hàm RIGHT Cú pháp: RIGHT (Text, n) Chức năng: Trả về n ký tự tính từ phải của chuỗi Text. Ví dụ: = RIGHT (“spring”,3) cho kết quả là “ing” * Hàm MID Cú pháp: MID (Text, i, n) Chức năng: Trả về n ký tự tính từ kí tự thứ i của chuỗi Text. Ví dụ: = MID (“spring”,2,3) cho kết quả là “pri” = MID(B2,5,2) cho kết quả là “ A” * Hàm TRIM Cú pháp: TRIM (Text) Chức năng: Cắt bỏ các kí tự trống thừa của chuỗi Text. Ví dụ: = TRIM (“ mùa xuân”) cho kết quả là “mùa xuân” * Hàm LEN Cú pháp: LEN (Text) Chức năng: Trả về chiều dài của chuỗi Text. Ví dụ: = LEN (“spring”) cho kết quả là 6 * Hàm UPPER Cú pháp: UPPER (Text) Chức năng: Đổi chuỗi Text sang chữ in hoa Ví dụ: = UPPER (“spring”) cho kết quả là “SPRING” * Hàm LOWER Cú pháp: LOWER (Text) Chức năng: Đổi chuỗi Text sang chữ thường. Ví dụ: = LOWER (“spriNG”) cho kết quả là “spring” * Hàm PROPER Cú pháp: PROPER (Text) Chức năng: Đổi ký tự đầu của chuỗi Text sang chữ in, còn lại chữ thường. Ví dụ: = PROPER (“spring”) cho kết quả là “Spring” * Hàm VALUE Cú pháp: VALUE (Text) Chức năng: Đổi chuỗi Text thành số Ví dụ: = Value(Right(“Fe12”,2)) -> 12 *  Nhóm hàm logic Hàm AND Cú pháp: AND (bt logic 1, bt logic 2, …, bt logic n) Chức năng: Hàm trả về giá trị True nếu tất cả các bt logic đều có giá trị True. Trả về giá trị False nếu có ít nhất 1 bt logic có giá trị False. Ví dụ: = AND (5=9,”XS”,IF(G2>=8,” GIỎI”, IF(G2>=7,”KHÁ”,IF(G2>=5,”TB”,”YẾU”)))) *  Nhóm hàm về thời gian Hàm NOW Cú pháp: NOW() Chức năng: Lấy giá trị ngày giờ của hệ thống. Hàm TODAY Cú pháp: TODAY () Chức năng: Lấy giá trị ngày giờ của hệ thống. * Hàm DAY Cú pháp: DAY (dữ liệu kiểu ngày) Chức năng: Lấy giá trị ngày của dữ liệu ngày tháng. Ví dụ: DAY(“10/11/2007”) = 10 Hàm MONTH Cú pháp: MONTH (dữ liệu kiểu ngày) Chức năng: Lấy giá trị tháng của dữ liệu ngày tháng. * Hàm YEAR Cú pháp: YEAR (dữ liệu kiểu ngày) Chức năng: Lấy giá trị năm của dữ liệu ngày tháng. Ví dụ: YEAR(“10/11/2007”) = 2007 * Hàm TIME Cú pháp: TIME (giờ, phút, giây) Chức năng: đổi 3 trị số trên thành biểu thức giờ. Ví dụ: = TIME(8,20,40) cho kết quả là 8:20:40 AM * Hàm HOUR Cú pháp: HOUR (dữ liệu kiểu giờ) Chức năng: lấy giá trị giờ của dữ liệu kiểu giờ. * Hàm MINUTE Cú pháp: MINUTE (dữ liệu kiểu giờ) Chức năng: lấy giá trị phút của dữ liệu kiểu giờ. * Hàm SECOND Cú pháp: SECOND (dữ liệu kiểu giờ) Chức năng: lấy giá trị giây của dữ liệu kiểu giờ. *  Nhóm hàm tìm kiếm Hàm VLOOKUP Cú pháp: VLOOKUP(giá trị tìm kiếm, vùng bảng đối chiếu, cột trả kết quả, kiểu tìm) * Chức năng: Tìm giá trị tìm kiếm trong cột thứ nhất của vùng đối chiếu,nếu tìm thấy trả về giá trị cùng hàng tại cột trả kết quả, nếu không tìm thấy thì tùy theo kiểu tìm : Nếu kiểu tìm là 0: thông báo lỗi N/A Nếu kiểu tìm là 1: sẽ tìm lấy giá trị lớn nhất nhưng nhỏ hơn giá trị cần tìm. Trong trường hợp này, cột đầu tiên trong bảng đối chiếu phải xếp theo thứ tự tăng dần. * * Ví dụ: Tính phụ cấp chức vụ dựa vào bảng phụ: Chọn ô F2 gõ công thức : =VLOOKUP(D2,$B$8 :$C$12,2,0) * Hàm HLOOKUP Cú pháp: HLOOKUP(giá trị tìm kiếm, vùng bảng đối chiếu, hàng trả kết quả, kiểu tìm) * Chức năng: Tìm giá trị tìm kiếm trong hàng trên cùng của vùng đối chiếu, nếu tìm thấy trả về giá trị cùng cột tại hàng trả kết quả, nếu không tìm thấy thì tùy theo kiểu tìm : Nếu kiểu tìm là 0: thông báo lỗi N/A Nếu kiểu tìm là 1: sẽ tìm lấy giá trị lớn nhất nhưng nhỏ hơn giá trị cần tìm. Trong trường hợp này, hàng trên cùng trong bảng đối chiếu phải xếp theo thứ tự tăng dần. * * Ví dụ: Tính phụ cấp chức vụ dựa vào bảng phụ: Chọn ô F2 gõ công thức : =VLOOKUP(D2,$C$13 :$G$14,2,0) * §4. ĐỒ THỊ VÀ IN ẤN Vẽ đồ thị Các bước thực hiện Chọn vùng dữ liệu muốn vẽ biểu đồ Trên thanh thực đơn chọn Insert  Chart Xuất hiện hộp thoại Chart Wizard: * Ta thực hiện 4 bước thông qua 4 hộp thoại như sau: Bước 1:Chọn một kiểu biểu đồ cụ thể trong khung Chart Type, nhấn Next Bước 2:Chọn vùng dữ liệu để vẽ biểu đồ trong ô Data Range, nhấn Next Bước 3:Nhập tiêu đề chung, tiêu đề trục ngang, trục dọc, nhấn Next Bước 3:Chọn nơi đặt biểu đồ Nhấn Finish * Định dạng trang Để điều chỉnh trang in, trên thanh thực đơn chọn File  Page Setup Xuất hiện hộp hội thoại Page Setup: * Thẻ Margin có các hộp chọn sau: Top: lề trên Bottom: lề dưới Left: lề trái Right: lề phải Header: khoảng cách cho vùng đầu trang Footer: khoảng cách cho vùng chân trang * Center on page: để căn chỉnh dữ liệu và giữa 2 chiều của trang in Nhấn nút Print để in ngay Nhấn nút Print Preview để xem trước Nhấn nút Options để thiết đặt máy in Nhấn nút OK để hoàn tất việc thiết đặt trang in * Xem tài liệu trước khi in Trên thanh thực đơn chọn File  Print Preview Các nút chức năng hỗ trợ khi xem trước Next: hiển thị trang kế tiếp Previous: hiển thị trang in trước đó Zoom: phóng to, thu nhỏ Print: thực hiện lệnh in Setup: mở hộp thoại Page Setup * In ấn Trên thanh thực đơn chọn File  Print. Xuất hiện hộp thoại Print * Chọn máy in trong hộp name Nhấn nút Properties để thiết đặt lại thông số cho máy in Chọn phạm vi in ấn trong vùng Print range: All: in tất cả các trang Page From … To: in từ trang … đến trang … * Chọn nội dung in ấn trong phần Print What Chọn số bản in trong hộp Number of Copies Nhấn OK để in ấn * §6.CƠ SỞ DỮ LIỆU TRONG MS- EXCEL Khái niệm: Cơ sở dữ liệu (CSDL) là tập hợp các dữ liệu có liên quan với nhau được tổ chức và lưu trữ nhằm mục đích phục vụ các yêu cầu khi khai thác dữ liệu. Trong MS Excel, CSDL được tổ chức thành các bảng, mỗi bảng gồm các cột và các hàng. Trên một CSDL ta có thể sắp xếp, tìm kiếm xóa, trích dữ liệu thỏa mãn tiêu chuẩn nào đó. * Ví dụ: * Sắp xếp dữ liệu Chọn vùng cần sắp xếp Nhấn menu Data ->Sort. Xuất hiện hộp thoại: * Giải thích các lựa chọn: Sort by: chọn trường dùng làm khóa để sắp xếp Then by: chọn trường dùng làm khóa thứ 2, thứ 3 để sắp xếp Ascending/ Descending: sắp xếp theo thứ tự tăng dần/ giảm dần. Header Row/ No Header Row: có/ không có hàng chứa tên trường. Nhấn OK. * Chức năng Filter Chọn lọc hoặc trích các bản ghi thỏa mãn các điều kiện lọc do chúng ta đặt ra. a) Auto Filter Chọn vùng cần lọc Menu Data -> Filter ->Auto Filter Nhắp vào mũi tên phải của trường làm điều kiện lọc * b) Advanced Filter Di chuyển con trỏ vào vùng Database Menu Data -> Filter ->Advanced Filter. Hộp đối thoại sau hiện ra: * * Filter the list in place: hiển thị những mẫu tin thỏa mãn điều kiện ngay trên vùng CSDL Copy to Another Location: những mẫu tin thỏa điều kiện được trích và chứa tại vị trí dược chỉ định tại ô Copy to * List Range: địa chỉ vùng Database Criteria Range: địa chỉ vùng Criteria.Vùng này gồm ít nhất 2 hàng: hàng thứ nhất chứa các tiêu đề, các hàng còn lại chứa điều kiện. Các điều kiện trên cùng một hàng : mệnh đề And Các điều kiện khác hàng: mệnh đề Or Copy to: địa chỉ các tiêu đề của vùng trích dữ liệu Unique Records Only: chỉ trích các bản ghi không trùng lắp Gõ Enter hay chọn OK * Các hàm Database Hàm DSUM Cú pháp: DSUM(Vùng CSDL, cột x, vùng tiêu chuẩn) Chức năng: Tính tổng các giá trị trên cột x của vùng CSDL thỏa điều kiện của vùng tiêu chuẩn. * Ví dụ: Tính tổng tiền lương cơ bản của những người có số công >=27 Ta lập vùng tiêu chuẩn: = DSUM(A2:E7, 4, A14:A15) * Hàm DMAX: Cú pháp: DMAX (Vùng CSDL, cột x, vùng tiêu chuẩn) Chức năng: Tìm giá trị lớn nhất trên cột x của vùng CSDL thỏa điều kiện của vùng tiêu chuẩn. * Ví dụ: Tìm tiền lương cơ bản lớn nhất của những người có số công >=27 = DMAX (A2 :E7, 4, A14:A15) * Hàm DMIN: Cú pháp: DMIN (Vùng CSDL, cột x, vùng tiêu chuẩn) Chức năng: Tìm giá trị nhỏ nhất trên cột x của vùng CSDL thỏa điều kiện của vùng tiêu chuẩn. Ví dụ: Tìm tiền lương cơ bản nhỏ nhất của những người có số công >=27 = DMIN (A2 :E7, 4, A14:A15) * Hàm DAVERAGE: Cú pháp: DAVERAGE(Vùng CSDL, cột x, vùng tiêu chuẩn) Chức năng: Tính giá trị trung bình của các giá trị trên cột x của vùng CSDL thỏa điều kiện của vùng tiêu chuẩn. Ví dụ: Tính tiền lương cơ bản trung bình của những người có số công >=27 = DAVERAGE (A2 :E7, 4, A14:A15) * Hàm DCOUNTA: Cú pháp: DCOUNTA(Vùng CSDL, cột x, vùng tiêu chuẩn) Chức năng: Đếm số phần tử khác rỗng của các giá trị trên cột x của vùng CSDL thỏa điều kiện của vùng tiêu chuẩn. Ví dụ: Đếm số người có số công >=27 = DCOUNTA (A2 :E7, 4, A14:A15) * Tổng hợp số liệu theo nhóm: Subtotal  Các bước thực hiện Bước 1: Sắp xếp CSDL theo khóa là cột cần thực hiện tổng hợp thống kê. Bước 2: Đặt con trỏ vào vùng CSDL, chọn menu Data -> Subtotal, hộp hội thoại sau xuất hiện: * * At Each Change in: Tên trường cần tạo nhóm tổng hợp Use Function: Chọn hàm cần tính toán Add Subtotal to: Chọn những trường cần tính toán Chọn OK * Muốn hủy bỏ tính Năng Subtotal trong CSDL, thực hiện lệnh Data -> Subtotal, rồi chọn Remove All.