Giáo trình Công nghệ thông tin - Modun 4: Kỹ năng sử dụng bảng tính cơ bản - Nguyễn Quốc Sử

MÔ ĐUN 04 – KỸ NĂNG SỬ DỤNG BẢNG TÍNH CƠ BẢN Biên soạn: Ths. Nguyễn Quốc Sử sites.google.com/site/lecnqsnguyenquocsu@hotec.edu.vn1Nội dung4.1. Giới thiệu Microsoft Excel (MS Excel) 20104.2. Thao tác trên bảng tính4.3. Định dạng bảng tính4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn24.1. Giới thiệu Microsoft Excel (MS Excel) 2010Microsoft Excel là một phần mềm hay là một chương trình ứng dụng, mà khi chạy chương trình ứng dụng này sẽ tạo ra một bảng tính và bảng tính này giúp ta dễ dàng hơn trong việc thực hiện:Tính toán đại số, phân tích dữ liệuLập bảng biểu báo cáo, tổ chức danh sáchTruy cập các nguồn dữ liệu khác nhauVẽ đồ thị và các sơ đồTự động hóa các công việc bằng các macronguyenquocsu@hotec.edu.vn34.1. Giới thiệu Microsoft Excel (MS Excel) 2010nguyenquocsu@hotec.edu.vn4Khởi động Microsoft Excel bằng cáchCách 1: Start  All Programs Microsoft Office 2010 Microsoft Office Excel 2010Cách 2: Run  excelCách 3: Double Click icon trên Desktop4.1. Giới thiệu Microsoft Excel (MS Excel) 2010nguyenquocsu@hotec.edu.vn54.1.1 Cửa sổ làm việc4.1. Giới thiệu Microsoft Excel (MS Excel) 2010nguyenquocsu@hotec.edu.vn64.1.2. Tab File4.1. Giới thiệu Microsoft Excel (MS Excel) 2010nguyenquocsu@hotec.edu.vn74.1.3. Ribbon MS Excel 2010 thay đổi giao diện người dùng từ việc sử dụng các thanh menu truyền thống thành các cụm lệnh dễ dàng truy cập được trình bày ngay trên màn hình gọi là Ribbon. Có các nhóm Ribbon chính: Home, Insert, Page Layout, Formulas, Data, Reviews, View, Developer, Add-Ins.4.2. Thao tác trên bảng tínhnguyenquocsu@hotec.edu.vn84.2.1. Tạo, mở, lưu bảng tính Tạo mới bảng tínhC1: Nhấn Menu File, chọn New, Blank WorksheetC2: sử dụng phím tắt Ctrl + N4.2. Thao tác trên bảng tínhnguyenquocsu@hotec.edu.vn94.2.1. Tạo, mở, lưu bảng tínhMở một bảng tính tạo sẵnC1: Nhấn Menu File, chọn Open.C2: Phím tắt mở tài liệu có sẵn là Ctrl+O.Lưu tài liệuC1: Nhấn Menu File, chọn Save.C2: Phím tắt để lưu tài liệu là Ctrl+SNếu file tài liệu này trước đó chưa được lưu lần nào, Excel sẽ được yêu cầu đặt tên file và chọn nơi lưu.Để lưu tài liệu với tên khác (hay định dạng khác), nhấn Menu File, chọn Save As (phím tắt F12).4.2. Thao tác trên bảng tínhnguyenquocsu@hotec.edu.vn104.2.1. Nhập dữ liệuKhi nhập dữ liệu trong Excel có thể nhập dữ liệu vào một ô, một vài ô cùng một thời điểm, hay vào nhiều trang tính cùng một thời điểm. Dữ liệu nhập vào có thể là số, văn bản, ngày tháng hoặc thời gian. Ta có thể định dạng dữ liệu theo nhiều cách khác nhau.Nhập văn bản hay số vào một ô1.Trên trang tính, hãy bấm vào một ô.2. Nhập số hoặc văn bản mà bạn muốn nhập rồi nhấn Enter hoặc Tab. Mẹo: Để nhập dữ liệu trên một dòng mới trong một ô, hãy tạo ngắt dòng bằng cách nhấn Alt+Enter.4.2. Thao tác trên bảng tínhnguyenquocsu@hotec.edu.vn114.2.1. Nhập dữ liệuNhập ngày tháng hoặc thời gian vào một ô1.Trên trang tính, hãy bấm vào một ô.2. Hãy nhập ngày tháng hay thời gian như sau:Để nhập ngày tháng, hãy dùng dấu gạch chéo hay dấu gạch nối để phân tách các phần của ngày tháng; ví dụ: hãy nhập 05/09/2014 hay 05-09-2014.Để nhập thời gian dựa trên đồng hồ 12 giờ, hãy nhập thời gian theo sau là dấu cách rồi nhập AM hoặc PM sau thời gian đó; chẳng hạn như 9:00 PM. Nếu không, Excel sẽ nhập thời gian dưới dạng AM.Mẹo: Để nhập thời gian hiện tại, hãy nhấn Ctrl+Shift+; (dấu chấm phẩy).4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn124.3.1. Workbook, WorksheetWorkbook: là một tập tin mà trên đó người dùng làm việc (tính toán, vẽ đồ thị, ) và lưu trữ dữ liệu. Vì mỗi workbook có thể chứa nhiều sheet (bảng tính), do vậy người dùng có thể tổ chức, lưu trữ nhiều loại thông tin có liên quan với nhau chỉ trong một tập tin (file). Một workbook chứa rất nhiều worksheet hay chart sheet tùy thuộc vào bộ nhớ máy tính của người dùng.Worksheet: Còn gọi tắt là sheet, là nơi lưu trữ và làm việc với dữ liệu, nó còn được gọi là bảng tính. Một worksheet chứa nhiều ô (cell), các ô được tổ chức thành các cột và các dòng. Worksheet được chứa trong workbook. Một Worksheet chứa được 16.384 cột và 1.048.576 dòng (phiên bản cũ chỉ chứa được 256 cột và 65,536 dòng).4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn134.3.1. Workbook, WorksheetThao tác trên Worksheet4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn144.3.2. Di chuyển trong bảng tínhDi chuyển trong bảng tínhCó thể dùng chuột, các phím , thanh cuốn dọc, thanh cuốn ngang, sheet tab, các tổ hợp phím tắt đề di chuyển qua lại giữa các sheet hay đi đến các nơi bất kỳ trong bảng tính.Thanh cuốn dọc, thanh cuốn ngangThanh cuốn dọc và thanh cuốn ngang được gọi chung là các thanh cuốn (scroll bars). Bởi vì màn hình Excel chỉ có thể hiển thị (cho xem) một phần của bảng tính đang thao tác, nên ta phải dùng thanh cuốn dọc để xem phần bên trên hay bên dưới bảng tính và dùng thanh cuốn ngang để xem phần bên trái hay bên phải của bảng tính. 4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn154.3.2. Di chuyển trong bảng tínhThanh Sheet tabĐể di chuyển qua lại giữa các sheet ta nhấp chuột lên tên của sheet trên thanh sheet tab. Ngoài ra chúng ta có thể dùng để di chuyển đến sheet liền trước sheet hiện hành và để di chuyển đến sheet liền sau sheet hiện hànhnguyenquocsu@hotec.edu.vn164.3.2. Di chuyển trong bảng tínhNhấn phímDi chuyển→ hoặc Tab Sang ô bên phải ← hoặc Shift + Tab Sang ô bên trái ↑ Lên dòng ↓ Xuống dòng Home Đến ô ở cột A của dòng hiện hành Ctrl + Home Đến địa chỉ ô A1 trong worksheet Ctrl + End Đến địa chỉ ô có chứa dữ liệu sau cùng trong worksheet Alt + Page Up Di chuyển ô hiện hành qua trái một màn hình Alt + Page Down Di chuyển ô hiện hành qua phải một mành hình Page Up Di chuyển ô hiện hành lên trên một màn hình Page Down Di chuyển ô hiện hành xuống dưới một màn hình F5 Mở hộp thoại Go To End + → hoặc Ctrl + →Đến ô bên phải đầu tiên mà trước hoặc sau nó là ô trống End + ← hoặc Ctrl + ←Đến ô bên trái đầu tiên mà trước hoặc sau nó là ô trống End + ↑ hoặc Ctrl + ↑ Lên ô phía trên đầu tiên mà trên hoặc dưới nó là ô trống End + ↓ hoặc Ctrl + ↓ Xuống ô phía dưới đầu tiên mà trên hoặc dưới nó là ô trốngCtrl + Page Up Di chuyển đến sheet phía trước sheet hiện hành Ctrl + Page Down Di chuyển đến sheet phía sau sheet hiện hành 4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn174.3.2. Chèn ô,dòng, cộtChèn ô trống1. Chọn các ô mà người dùng muốn chèn các ô trống vào đó (muốn chọn các ô không liên tục thì giữ Ctrl trong khi chọn các ô).2. Chọn Home  chọn nhóm Cells  Insert  Insert Cells3. Chọn lựa chọn phù hợp trong hộp thoại InsertChèn dòng1. Chọn một hoặc nhiều dòng liên tục hoặc cách khoảng mà người dùng muốn chèn số dòng tương ứng phía trên các dòng này.2. Chọn Ribbon Home chọn nhóm Cells  Insert Insert Sheet Rows4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn184.3.2. Chèn ô,dòng, cột4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn194.3.4. Định dạng chungCác nút định dạng thông dụng của Excel được bố trí rất thuận lợi truy cập trong nhómHome của thanh Ribbon. Trong quá trình soạn thảo, tính toán trên Excel, mỗi khi cần định dạng người dùng chỉ việc nhấn chuột lên nút lệnh phù hợp dưới đây.4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn204.3.4. Định dạng văn bản và sốKhi cần các định dạng phức tạp hơn mà trên thanh Ribbon không có nút lệnh thì người dùng truy cập vào hộp thoại Format Cells: Chọn Home  nhóm Cells  Format  Format Cells4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn214.3.5. Định dạng khung (border)4.3. Định dạng bảng tínhnguyenquocsu@hotec.edu.vn224.3.5. Định dạng màu nền4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn234.4.1. Giới thiệu công thức (Formula)Công thức giúp bảng tính hữu ích hơn rất nhiều, nếu không có các công thức thì bảng tính cũng giống như trình soạn thảo văn bản. Chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới giúp chúng ta đỡ tốn công sức tính lại nhiều lần. Vậy công thức có các thành phần gì?Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm.4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn244.4.1. Giới thiệu công thức (Formula)Các toán tửToán tửChức năng Ví dụ Kết quả +Cộng =3+3 3 cộng 3 là 6 -Trừ =45-4 45 trừ 4 còn 41 *Nhân =150*.05 150 nhân 0.50 thành 7.5 /Chia =3/3 3 chia 3 là 1 ^Lũy thừa =2^4 =16^(1/4) 2 lũy thừa 4 thành 16 Lấy căn bậc 4 của 16 thành 2 4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn254.4.1. Giới thiệu công thức (Formula)Các toán tửToán tửChức năng Ví dụ Kết quả &Nối chuỗi =“Lê” & “Thanh” Nối chuỗi “Lê” và “Thanh” lại thành “Lê Thanh” =Bằng =A1=B1 Ví dụ ô A1=3, ô B1=6Kết quả: FALSE > Lớn hơn =A1>B1 Ví dụ ô A1=3, ô B1=6Kết quả: FALSE =Lớn hơn hoặc bằng =A1>=B1 Ví dụ ô A1=3, ô B1=6Kết quả: FALSE  Khác =A1B1 Ví dụ ô A1=3, ô B1=6Kết quả: TRUE 4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn26Thứ tự ưu tiên của các toán tử Toán tửMô tảƯu tiên: (hai chấm) (1 khoảng trắng) , (dấu phẩy) Toán tử tham chiếu 1– Số âm (ví dụ –1) 2% Phần trăm 3^ Lũy thừa 4* và / Nhân và chia 5+ và – Cộng và trừ 6&Nối chuỗi 7= = So sánh 84.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn274.4.2. Giới thiệu hàm (Function)Hàm trong Excel được lập trình sẵn dùng tính toán hoặc thực hiện một chức năng nào đó. Việc sử dụng thành thạo các hàm sẽ giúp chúng ta tiết kiệm được rất nhiều thời gian so với tính toán thủ công không dùng hàm. Các hàm trong Excel rất đa dạng bao trùm nhiều lĩnh vực, có những hàm không yêu cầu đối số, có những hàm yêu cầu một hoặc nhiều đối số, và các đối số có thể là bắt buộc hoặc tự chọn.Ví dụ:=Rand(): hàm không có đối số=If(A1>=5,”Đạt”,”Rớt”): hàm 3 đối số=PMT(10%,4,1000,,1): hàm nhiều đối số và đối số tùy chọn4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn28Trong Excel 2010 có các hàm chính như sau:Hàm ngoại: Call, Registed.ID, Hàm lấy dữ liệu từ SSAS: Cubeset, Cubevalue, Hàm dữ liệu: Dmin, Dmax, Dcount, Hàm ngày và thời gian: Time, Now, Date,. Hàm kỹ thuật: Dec2Bin, Dec2Hex, Dec2Oct, Hàm tài chính: Npv, Pv, Fv, Rate, Hàm thông tin: Cell, Thông tin, IsNa, Hàm luận lý: If, And, Or, Hàm tham chiếu và tìm kiếm: Choose, Vlookup, OffSet, Hàm toán và lượng giác: Log, Mmult, Round, Hàm thống kê: Stdev, Var, CountIf, Hàm văn bản: Asc, Find, Text, 4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn29Nhập công thức và hàmNhập công thức trong Excel rất đơn giản, muốn nhập công thức vào ô nào người dùng chỉ việc nhập dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm. Có thể nhìn vào thanh Formula để thấy được trọn công thức. Một điều hết sức lưu ý khi làm việc trên bảng tính là tránh nhập trực tiếp các con số, giá trị vào công thức mà người dùng nên dùng đến tham chiếu.4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn30Function Library Một trong những cách dễ dàng nhất để sử dụng hàm trong Excel là sử dụng thư viện hàm. Khi người dùng muốn sử dụng hàm nào chỉ việc vào thanh Ribbon  chọn nhóm FormulasFunction Library  chọn nhóm hàm chọn hàm cần sử dụng.4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn314.4.3. Nhóm hàm thông dụngCÚ PHÁPÝ NGHĨA VÀ VÍ DỤABS(number) Trả về giá trị tuyệt đối của một số thực. =ABS(12 - 20)8 INT(number) Trả về số nguyên lớn nhất không vượt quá number. =INT(5.6) 5 =INT(-5.6) 6 MOD(number, divisor)  Trả về số dư của phép chia nguyên number cho divisor (number, divisor là các số nguyên). =MOD(5, 3) 2 4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn324.4.3. Nhóm hàm thông dụngCÚ PHÁPÝ NGHĨA VÀ VÍ DỤODD(number)  Làm tròn lên tới một số nguyên lẻ gần nhất. =ODD(3.6) =ODD(-2.2) 3 PRODUCT(number1, number2, ...) Tính tích của các giá trị trong danh sách tham số. =PRODUCT(2, -6, 3, 4) 144 SQRT(number)  Tính căn bậc 2 của một số dương number. =SQRT(36) 6 SUM(number1, num-ber2, ...)  Tính tổng của các giá trị trong danh sách tham số. =SUM(2, -6, 8, 48 4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn334.4.3. Nhóm hàm thông dụngCÚ PHÁPÝ NGHĨA VÀ VÍ DỤROUND(number, num_digits)  Làm tròn số number với độ chính xác đến num_digits chữ số thập phân (với qui ước 0 là làm tròn tới hàng đơn vị, 1 là lấy 1 chữ số thập phân, -1 là làm tròn tới hàng chục, ...). =ROUND(5.13687, 2) 5.14 =ROUND(145.13687, -2) 100 4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn344.4.3. Nhóm hàm thông dụngNgoài ra còn có các nhóm hàm:Nhóm hàm Logic: AN, OR, XOR, NOT, IFNhóm hàm thống kê: MAX, MIN, AVERAGE, COUNT, COUNTIF, COUNTA, COUNTBLACK, SUM, SUMIFNhóm hàm xử lý choỗi: LOWER, UPPER, TRIM, LEN, VALUE, LEFT, RIGHT, MIDNhóm hàm thời gian: TODAY(), NOW(), MONTH(), WEEK(), YEAR()4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn354.4.4. Nhóm hàm tìm kiếmHàm VLOOKUP Tìm giá trị lookup_valuetrong cột trái nhất của bảng table_arraytheo chuẩn dò tìm range_lookup, trả về trị tương ứng trong cột thứ col_index_num(nếu tìm thấy). Cú pháp: 4.4. Hàm và các nhóm hàm thông dụngnguyenquocsu@hotec.edu.vn364.4.4. Nhóm hàm tìm kiếmHàm VLOOKUP Tìm giá trị lookup_valuetrong dòng trên cùng của bảng table_arraytheo chuẩn dò tìm range_lookup, trả về trị tương ứng trong dòng thứ row_index_num(nếu tìm thấy).Cú pháp: