Bài giảng 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 
Khái niệm và thao tác cơ bản 
 . 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 
Thao tác cơ bản 
 . Khởi động MS Excel 
Giao diện MS Excel 
Khái niệm cơ bản 
. 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 
Thao tác với tệp bảng tính 
 . 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 
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 
Các khái niệm, thao tác cơ bản 
. 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 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, thao tác 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 ô 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 
Các khái niệm, thao tác cơ bả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 
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 
Lập công thức 
. Đố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) 
 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 
Lập công thức 
 . Đố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: = , , > , < 
Cách 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ác dạng 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 
Sử dụng hàm trong Excel 
 . 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 
Các hàm thường dùng 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 toán học 
 . 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 xử lý logic 
. 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 thống kê cơ bản 
. 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 xử lý chuỗi 
. 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ý dữ liệu thời gian 
. 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 tìm kiếm và tham chiếu 
. 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 
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 
 . 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 tự động 
 . 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 nâng cao 
 . 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 
Sắp xếp dữ liệu 
 . 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 
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 
In bảng tính 
 . Đị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 
 Khoảng 
 cách từ mép 
 giấy đến dòng 
 đầu tiên 
 Khoảng 
 Khoảng cách lề Khoảng 
 cách từ mép trái cách lề phải 
 giấy đến 
 dòng 
 cuối cùng 
In bảng tính 
. 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 
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 
Tạo biểu đồ 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 đồ trong Power Point 
. 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ư ý 
Máy tính và tin học 
. Máy tính là