Giáo trình Tin học nhóm ngành Nông-Lâm-Ngư & Môi trường - Chương 4: Khai thác bảng tính điện tử (MS Excel 2010)

Tin học nhóm ngành Nông-Lâm-Ngư & Môi trường Bộ môn HTTT -Khoa CNTT-Đại học Vinh- Chương 4: Khai thác bảng tính điện tử (MS Excel 2010) Nội dung chính  Khái niệm và thao tác cơ bản  Lập và định dạng bảng tính  Lập công thức và các hàm cơ bản  Sắp xếp và trích lọc dữ liệu  In bảng tính  Tạo biểu đồ trong Power Point với dữ liệu trên Excel Nội dung chính  Khái niệm và thao tác làm việc cơ bản  Lập và định dạng bảng tính  Lập công thức và các hàm cơ bản  Sắp xếp và trích lọc dữ liệu  In bảng tính  Tạo biểu đồ trong Power Point với dữ liệu trên Excel  Microsoft Excel là chương trình xử lý bảng tính nằm trong bộ Microsoft Office của hãng phần mềm Microsoft  Chức năng: giúp ghi lại, trình bày các thông tin xử lý dưới dạng bảng, thực hiện tính toán và xây dựng các số liệu thống kê trực quan có trong bảng từ Excel Khái niệm và thao tác cơ bản  Khởi động MS Excel Thao tác cơ bản Giao diện MS Excel  Giao diện phần Tab Menu, Ribbon, chế độ xem phóng to thu nhỏ,... tương tự trong MS Word  Các cột được đánh lần lượt từ trái sang phải theo bảng chữ cái: A,B,C,D,...  Các dòng được đánh số thứ tự từ trên xuống: 1,2,3...  Khung làm việc nhập liệu là bảng các ô lưới  Mỗi ô là một Cell có địa chỉ là tọa độ cột và hàng gióng vuông góc. Ví dụ: J6  Mỗi Cell có thể chứa dữ liệu nhập vào hoặc công thức tính toán (Hàm)  Một file Excel có thể chứa nhiều bảng tính: Sheet Khái niệm cơ bản  Tạo mới tệp: File > New > Blank Workbook  Mở tệp có sẵn: File > Open > Chọn đường dẫn file  Lưu tệp: File > Save hoặc Save as  Đóng tệp: các cách sau o Chọn File > Close (Save nếu cần thiêt) o Nhấp chuột vào [x] trên cùng bên phải cửa sổ o Nhấn tổ hợp phím Alt + F4 Thao tác với tệp bảng tính Nội dung chính  Khái niệm và thao tác làm việc cơ bản  Lập và định dạng bảng tính  Lập công thức và các hàm cơ bản  Sắp xếp và trích lọc dữ liệu  In bảng tính  Tạo biểu đồ trong Power Point với dữ liệu trên Excel  Thiết lập Tiếng Việt trong Excel o Sử dụng Unikey, kiểu gõ Telex như trong MS Word Các khái niệm, thao tác cơ bản  Các loại địa chỉ 1 ô (1 Cell) trong Excel o Địa chỉ tương đối: (Sẽ thay đổi địa chỉ khi copy công thức tính toán) o Địa chỉ tuyệt đối: $ $ (Khi chép đến vùng đích, địa chỉ tham chiếu của vùng đích giống như vùng nguồn) o Địa chỉ hỗn hợp: $ (hoặc $ (Khi chép đến vùng đích, địa chỉ tham chiếu của vùng đích bị thay đổi một cách tương ứng hoặc theo cột hoặc theo hàng) Các khái niệm, thao tác cơ bản  Địa chỉ miền ô o Dạng 1: [địa chỉ ô đầu] : [địa chỉ ô cuối] (Nếu miền ô đó gồm các ô liền kề nhau)  VD: B2:D3 o Dạng 2: [địa chỉ ô đầu, .. , địa chỉ ô cuối] (Nếu miền ô đó gồm các ô rời nhau)  VD: A1, B3, C2 Chú ý: Nếu miền ô là dãy các ô liền kề gọi là mảng  VD: A2:C2 Các khái niệm cơ bản  Chọn Vùng ô: Nhấn rê trên miền ô tính từ ô đầu nằm ở góc trái trên đến ô cuối nằm ở góc phải dưới, hoặc nhấn giữ phím CTRL rồi nhấn trên từng ô chọn (nếu các ô đó là không nằm liền kề nhau)  Chọn Dòng: Click chuột vào chỉ số dòng cần chọn ở mép trái vùng bảng tính  Chọn Cột: Click chuột vào chữ cái Cột cần chọn ở góc trên vùng bảng tính Các khái niệm, thao tác cơ bản  Nhập dữ liệu: Nhấp chuột vào ô cần nhập, ấn Enter o Kiểu số o Kiểu ngày tháng o Kiểu chuỗi o Kiểu công thức  Sửa dữ liệu: Nhấp chuột vào ô, thay đổi dữ liệu, Enter  Xóa dữ liệu: Nhấp chuột vào ô, dùng phím Backspace hoặc Delete để xóa dữ liệu, ấn Enter  Xóa thao tác vừa thực hiện: Ctrl+Z Các khái niệm, thao tác cơ bản  Sao chép hoặc di chuyển một vùng cell o Chọn vùng xử lý o Click chuột phải o Chọn Copy/Cut o Chuyển đến vùng mới o Click chuột phải o Chọn Paste Các khái niệm, thao tác cơ bản  Xóa ô tính: Chọn vùng ô, click chuột phải, chọn delete Các khái niệm, thao tác cơ bản Các ô bên phải sẽ dồn sang trái, Các ô phía dưới sẽ đẩy lên trên Xóa dòng chứa ô đang chọn Xóa cột chứa ô đang chọn  Định dạng dữ liệu cho cell o Chọn Cell o Tab Home > General > o Chọn kiểu dữ liệu o Chọn định dạng cho dữ liệu Các khái niệm, thao tác cơ bản  Định dạng dữ liệu cho cell o Định dạng kiểu số o Căn bám lề o Chọn Font o Chọn khung viền o Chọn màu nền Các khái niệm, thao tác cơ bản  Căn bám lề cho dữ liệu: Alignment Các khái niệm, thao tác cơ bản  Định dạng Font chữ: Home > Fomat > Fomat cells  Chọn Font Các khái niệm, thao tác cơ bản  Định dạng Font chữ: Home > Fomat > Fomat cells  Chọn Font Các khái niệm, thao tác cơ bản  Đánh số tự động  Nhập giá trị cho 1 ô > Tab Editing > Fill > Chọn Series... > Chọn fill theo hàng hoặc cột Các khái niệm, thao tác cơ bản  Trộn ô (Cell)  Chọn các ô cần trộn > Home > Aligment > Merge and Center  Thay đổi độ rộng của Cột: Chọn và kéo Rê chuột đường gạch đứng giữa hai cột để thay đổi độ rộng cột bên trái  Thay đổi độ cao một Hàng: Kéo và rê chuột trên đường gạch ngang giữa hai hàng để thay đổi chiều cao dòng bên trên Các khái niệm, thao tác cơ bản  Tạo khung viền cho bảng tính o Chọn ô, miền ô cần xử lý o Chọn Home > Font > Format Cell > Border o Chọn khung viền cần hiển thị Các khái niệm, thao tác cơ bản  Tô màu nền Chọn ô/miền ô cần xử lý Chọn Home > Font > Chọn Fill để tô màu nền cho miền ô đã chọn Các khái niệm, thao tác cơ bản Nội dung chính  Khái niệm và thao tác làm việc cơ bản  Lập và định dạng bảng tính  Lập công thức và các hàm cơ bản  Sắp xếp và trích lọc dữ liệu  In bảng tính  Tạo biểu đồ trong Power Point với dữ liệu trên Excel  Đối với dữ liệu kiểu số o Phép toán số học: + - * / ^ % o Phép toán logic: AND, OR, NOT o Phép toán quan hệ so sánh trả về True, False: = , , , >= o Mức ưu tiên (cùng mức sẽ thực hiện từ trái sang phải) Lập công thức Loại phép toán Mức ưu tiên Dấu ngoặc đơn () 1 (Cao nhất) Lũy thừa (^) 2 Nhân (*) và chia (/) 3 Cộng (+) và trừ (-) 4 Quan hệ so sánh 5 Logic 6  Đối với dữ liệu kiểu chuỗi o Phép ghép chuỗi: & o Phép toán quan hệ so sánh: = , , > , <  Đối với dữ liệu kiểu ngày tháng o Phép toán : + , - o Phép toán quan hệ so sánh: = , , > , < Lập công thức  Đưa con trỏ tới vị trí ô cần đặt kết quả của công thức  Gõ công thức : o Bắt đầu công thức bằng dấu “=” hoặc dấu “+” o Đánh công thức tính toán (giá trị tham chiếu của các ô được thay bởi địa chỉ của các ô tương ứng) o Nhấn Enter Cách lập công thức  Công thức chỉ gồm các số và toán hạng • Ví dụ: = (45 + 127)^2 / 76 - 0.3  Công thức gồm địa chỉ miền ô/tên miền: • Ví dụ: = A5 * C5 - B6 hoặc = Ten1 + Ten2  Công thức vừa có địa chỉ ô, vừa có con số: • Ví dụ: = (H7 * I8 + H7 * K8)^3 / 5  Công thức chỉ có các hàm: • Ví dụ: = SUM(C3: C9)  Công thức tổng quát: gồm cả số, địa chỉ ô/miền ô/tên miền, các hàm và các phép toán • Ví dụ: = IF(LEFT(A2,1)=”N”, $H$3*5, $H$3*3) + $H$5 Các dạng công thức  Sử dụng hàm: =Tên hàm(danh sách các đối số) Tên hàm: Gõ trực tiếp hoặc chọn Formulas > Insert Function > Chọn hàm Danh sách đối số cách nhau bởi dấu ‘,’ Ví dụ: SUM Sử dụng hàm trong Excel  Hàm toán học  Hàm logic  Hàm thống kê cơ bản  Hàm xử lý dữ liệu kiểu chuỗi  Hàm xử lý dữ liệu kiểu thời gian  Hàm tìm kiếm và tham chiếu Các hàm thường dùng trong Excel  ABS(n): cho giá trị tuyệt đối của biểu thức số n  SQRT (n): cho giá trị căn bậc hai của n (n>0)  POWER(number, power): tính number mũ power  SUM (danh sách các trị): tính tổng của các giá trị có trong danh sách  AVERAGE(danh sách các trị): tính trung bình cộng của các giá trị có trong danh sách  MAX(danh sách các trị): tìm giá trị lớn nhất của các giá trị có trong danh sách  MIN(danh sách các trị): tìm giá trị nhỏ nhất của các giá trị có trong danh sách Các hàm toán học  IF(biểu thức logic, biểu thức1, biểu thức2): nếu biểu thức logic là đúng thì hàm cho giá trị là , ngược lại cho giá trị . • , có thể là hằng, trị , biểu thức, cũng có thể lại là một hàm IF khác  AND(điều kiện1, điều kiện 2, ...): cho giá trị đúng khi mọi điều kiện trong danh sách đều cho trị đúng  OR(điều kiện1, điều kiện 2, ...): cho trị đúng khi có bất kì một điều kiện nêu trong danh sách cho trị đúng  NOT(điều kiện): cho kết quả ngược lại với điều kiện Các hàm xử lý logic  COUNTA(danh sách các trị): Trả về số ô chứa dữ liệu trong danh sách (kể cả dữ liệu lỗi và văn bản trống ‘’)  COUNT(danh sách các trị): Trả về số các ô chứa giá trị số trong danh sách  RANK(x, danh sách): Xác định thứ hạng của giá trị x so với các giá trị trong danh sách (thứ hạng xếp theo thứ tự giảm dần). Giá trị x và danh sách phải là các giá trị số Các hàm thống kê cơ bản  SUMIF(vùng tiêu chuẩn, tiêu chuẩn, vùng lấy tổng): Cộng các ô trong vùng lấy tổng tương ứng với các ô thuộc vùng tiêu chuẩn thỏa mãn tiêu chuẩn đã cho, nếu không có vùng lấy tổng thì được lấy trong vùng tiêu chuẩn  COUNTIF(vùng cần đếm, tiêu chuẩn): Đếm số ô trong vùng cần đếm thỏa mãn tiêu chuẩn Các hàm thống kê cơ bản  LEN(Text): Trả về độ dài của chuỗi Text  LOWER(Text): Chuyển chuỗi Text thành chữ thường  UPPER(Text): Chuyển chuỗi Text thành chuỗi chữ hoa  PROPER(Text): Chuyển các ký tự đầu từ của chuỗi Text thành chữ hoa  VALUE(Text): Chuyển chuỗi có dạng số sang dạng số  TEXT(giá trị số, định dạng): Chuyển giá trị số thành chuỗi theo định dạng đã định  MID(text, start_num, nums):Trả về chuỗi nums ký tự trong text bắt đầu từ start_nam Các hàm xử lý chuỗi  DAY(dữ liệu kiểu ngày): Trả về giá trị ngày của dữ liệu MONTH(dữ liệu kiểu ngày): Trả về giá trị tháng của dữ liệu  YEAR(dữ liệu kiểu ngày): Trả về giá trị năm của dữ liệu  NOW(): Trả về ngày và giờ hiện tại của hệ thống  TODAY (): Trả về ngày hiện hành của hệ thống Các hàm xử lý dữ liệu thời gian  VLOOKUP (x, bảng, STT cột cần lấy giá trị, cách dò)  Ý nghĩa: dò tìm trị x ở cột bên trái của bảng. Khi tìm thấy thì chuyển sang cột tham chiếu để lấy trị trong ô ở đó ứng với vị trí của x. Bảng là một khối các ô và thường gồm nhiều hàng, nhiều cột. Cột bên trái của bảng luôn chứa các trị để dò tìm, các cột khác chứa các trị tương ứng để tham chiếu. Cột tham chiếu là thứ tự của cột (tính từ trái của bảng trở đi), cột đầu tiên của bảng là cột 1 o Cách dò là  1 : dò tìm tương đối  0 : dò tìm tuyệt đối Các hàm tìm kiếm và tham chiếu  HLOOKUP(x, bảng, hàng tham chiếu, cách dò)  Mọi nguyên tắc hoạt động của hàm HLOOKUP giống như hàm VLOOKUP, chỉ khác là hàm VLOOKUP dò tìm ở cột bên trái, tham chiếu số liệu ở các cột bên phải, còn hàm HLOOKUP dò tìm ở hàng trên cùng, tham chiếu số liệu ở các hàng phía dưới Các hàm tìm kiếm và tham chiếu Nội dung chính  Khái niệm và thao tác làm việc cơ bản  Lập và định dạng bảng tính  Lập công thức và các hàm cơ bản  Sắp xếp và trích lọc dữ liệu  In bảng tính  Tạo biểu đồ trong Power Point với dữ liệu trên Excel  Trích lọc dữ liệu nhằm mục đích cho phép xem xét hoặc lấy thông tin từ bảng dữ liệu mà thoả mãn các điều kiện nào đó  Trong Excel có hai phương pháp lọc o Lọc tự động o Lọc nâng cao Trích lọc dữ liệu  Chọn vùng dữ liệu cần lọc (bao gồm cả tiêu đề) o Chọn Data > chọn Filter Trích lọc dữ liệu tự động  Giao diện bảng dữ liệu với chức năng lọc Trích lọc dữ liệu tự động  Lọc dữ liệu o Chọn cột muốn lọc o Click chọn dữ liệu hiển thị o Chọn sắp xếp nếu cần Trích lọc dữ liệu tự động  Tạo bảng phụ điều kiện lọc có tên tiêu đề cột trùng với tên cột dữ liệu cần lọc  Chọn vùng bảng tính cần lọc  Chọn Tab Data > Chọn Advanced Trích lọc dữ liệu nâng cao o Filter the list, in-place: kết quả nằm trên bảng lọc o Copy to another location: Lọc và copy kết quả tới vùng khác o List range: Địa chỉ vùng chứa các dữ liệu muốn lọc o Criteria range: Địa chỉ của bảng phụ o Copy to: Địa chỉ muốn đặt kết quả sau khi lọc Trích lọc dữ liệu nâng cao  Sắp xếp dữ liệu o Chọn cột dữ liệu cần sắp xếp o Chọn Data > chọn Sort Sắp xếp dữ liệu Nội dung chính  Khái niệm và thao tác làm việc cơ bản  Lập và định dạng bảng tính  Lập công thức và các hàm cơ bản  Sắp xếp và trích lọc dữ liệu  In bảng tính  Tạo biểu đồ trong Power Point với dữ liệu trên Excel  Định dạng trang in o Nhấn chọn miền ô chứa dữ liệu cần in o Chọn Page Layout > Print Area > Set Print Area In bảng tính  Thiết lập trang in: o Chọn Page Layout > Page Setup > chọn Page In bảng tính  Thiết lập trang in: o Chọn Page Layout > Page Setup > chọn Margin In bảng tính Khoảng cách từ mép giấy đến dòng đầu tiên Khoảng cách từ mép giấy đến dòng cuối cùng Khoảng cách lề phải Khoảng cách lề trái  Thiết lập trang in: o Chọn Page Layout > Page Setup > chọn Header/Footer In bảng tính  Thiết lập trang in: o Chọn Page Layout > Page Setup > chọn Sheet  Cách in o Chọn File > Print > o Sử dụng các tùy chọn In bảng tính Nội dung chính  Khái niệm và thao tác làm việc cơ bản  Lập và định dạng bảng tính  Lập công thức và các hàm cơ bản  Sắp xếp và trích lọc dữ liệu  In bảng tính  Tạo biểu đồ trong Power Point với dữ liệu trên Excel  Nhập bảng dữ liệu thống kê trên Excel  Chọn bảng dữ liệu  Chọn Tab Insert > Charts > Chọn loại biểu đồ phù hợp Tạo biểu đồ với dữ liệu trên Excel  Cách 1: tạo biểu đồ bằng Excel rồi Copy vào Slide  Cách 2: tạo từ tùy chọn Power Point o Chọn Insert > Illustrations > Chart o Chọn loại biểu đồ > OK (Tự động bật chương trình Excel với bảng số liệu mẫu, trên Power Point cũng xuất hiện biểu đồ mẫu tương ứng) o Thay đổi thông tin số liệu và tên số liệu,... Để có đồ thị như ý Tạo biểu đồ trong Power Point  Máy tính là Máy tính và tin học