Giáo trình tin học văn phòng - Excel

Độ ưu tiên của toán tử: theo thứ tự giảm dần: ( ), ^, */, + - ... Các loại địa chỉ ô | khối Địa chỉ tương đối Ký hiệu: Ví dụ: Giả sử Giả sử công thức tại ô C1 như sau: = A1 * B1, kết quả là 30 Khi sao chép công thức của ô C1 đến ô C2 thì công thức tại ô này như sau: = A2 * B2, kết quả là 80 Khi sao chép công thức của ô C1 đến ô D1 thì công thức tại ô này như sau: =B2 * C2, kết quả là 90 Khi sao chép công thức của ô C1 đến ô D2 thì công thức tại ô này như sau: =B2 * C2, kết quả là 320 Như vậy khi sao chép công thức của ô C1 đến các ô khác thì ô A1 và B1 thay đổi theo hàng và cột tương ứng. Vậy ô A1, B1 trong công thức là loại địa chỉ tương đối. Địa chỉ tuyệt đối Ký hiệu: $$ VD: Yêu cầu tính các cột Chi phí 1 và Chi phí 2 trong bảng số liệu sau, biết rằng: Chi phí 1 = Số lượng 1 * Đơn giá 1 * Tỷ lệ chi phí Chi phí 2 = Số lượng 2 * Đơn giá 2 * Tỷ lệ chi phí Ta có công thức tại ô E3 là: = A3 * C3 *$B$1 Khi sao chép công thức từ ô E3 sang các ô E4, E5, F3, F4, F5 thì công thức sẽ thay đổi như sau: E4 = A4 * C4 *$B$1 E5 = A5 * C5 *$B$1 F3 = B3 * D3 *$B$1 F4 = B4 * D4 *$B$1 F5 = B5 * D5 *$B$1 Vậy trong công thức, địa chỉ $B$1 là loại địa chỉ tuyệt đối. Địa chỉ hỗn hợp Ký hiệu: $ hoặc $ VD: Yêu cầu tính các cột Chi phí V.Chuyển và Chi phí B.Xếp trong bảng số liệu sau, biết rằng: Chi phí V.Chuyển = Số lượng * Đơn giá * Tỷ lệ chi phí của V.Chuyển Chi phí B.Xếp = Số lượng * Đơn giá * Tỷ lệ chi phí của B.Xếp Ta có C3 = $A3 * $B3 * C$2 Khi sao chép công thức từ ô C3 sang ô C4, C5, D3, D4, D5 thì công thức thay đổi như sau: C4 = $A4* $B4* C$2 C5 = $A5 $B5 C$2 D3 = $A3 * $B3* D$2 D4 = $A4 * $B4* D$2 D5 = $A5 * $B5* D$2 Trong công thức các địa chỉ $A3, $B3, C$2 là các địa chỉ hỗn hợp. Cách tạo công thức Chọn một trong các cách: Cách 1: Nhập trực tiếp từ bàn phím Cách 2: Nhập bằng bàn phím các giá trị là hằng số, toán tử. Kích chuột vào các ô, khối mà công thức tham chiếu và dùng phím F4 để chọn loại địa chỉ (con trỏ đang đứng sau địa chỉ nào thì phím F4 sẽ tác dụng lên địa chỉ đó) VD: Muốn tạo công thức tại ô C2 = $B3 - C$1 ta làm như sau: Đưa con trỏ đến ô C2 Gõ dấu = Kích chuột vào ô B3, sau đó ấn phím F4 cho đến khi được $B3 Gõ dấu – Kích chuột vào ô C1, sau đó ấn F4 cho đến khi được C$1 Nhấn Enter. Định dạng dữ liệu Dữ liệu mới nhập vào sẽ ở dạng thô, dạng nguyên thuỷ. Muốn dữ liệu trình bày theo dạng thức phù hợp, cần phải tiến hành định dạng. Chọn phạm vi dữ liệu cần định dạng Chọn lệnh Format \ Cells Xuất hiện hộp thoại: Hình 8 Thẻ Number: để định dạng kiểu dữ liệu số cho ô đã chọn (các kiểu dữ liệu số đã được nói ở phần phân loại dữ liệu) Thẻ Alignment: Thẻ Aligment Canh chỉnh dữ liệu trong ô Horizontal Canh dữ liệu trong ô theo chiều ngang General Mặc nhiên Left Canh trái Right Canh phải Center Canh giữa Vertical Canh theo dữ liệu trong ô theo chiều dọc Top Ở trên Center Canh giữa Bottom Canh dưới Orientation Hướng dữ liệu Text Control Wrap text tự động điều chỉnh độ rộng ô sao cho vừa với dữ liệu trong ô Shrink to fit thay đổi kích thước dữ liệu sao cho vừa vặn với độ rộng ô Merge cells Kết hợp các ô được chọn thành một ô Ngoài ra có thể canh biên dữ liệu bằng cách sử dụng nhanh các nút trên thanh công cụ Formatting: Hình 9 Thẻ Font: Định dạng Font cho phạm vi dữ liệu được chọn Thẻ Pattern: Tô nền cho phạm vi Thẻ Border: kẻ khung cho phạm vi Presets: Các kiểu kẻ Style: Các kiểu nét kẻ Color: màu sắc của nét kẻ Border: Chứa các nút hiển thị vị trí của từng đường viền trong phạm vi. Có thể thao tác trực tiếp bằng cách kích chuột vào các nút này để kẻ từng nét cho phạm vi. Thẻ Protection: Bảo vệ dữ liệu Ngoài ra có thể thực hiện kẻ khung bằng nút Borders trên thanh công cụ: Hình 10 Có thể định dạng số nhanh bằng cách sử dụng các nút trên thanh công cụ Formatting: Hình 11 Hiệu chỉnh bảng tính Xử lý ô, cột, hàng: Chèn Chèn ô: Chọn lệnh Insert - Cells Xuất hiện hộp thoại: Hình 12 Shift Cell Right: Chèn một ô, đẩy các ô cùng hàng sang phải Shift Cell Down: Chèn một ô, đẩy các ô cùng cột xuống Entire row: Chèn một hàng vào vị trí hiện tại Entire Column: Chèn một cột vào vị trí hiện tại Chèn cột: Chọn lệnh Insert / Columns Chèn hàng: Chọn lệnh Insert / Rows Ngoài ra có thể kích phải chuột vào vị trí cần chèn rồi chọn lệnh tương ứng. Xóa Chọn khối ô Chọn lệnh Edit / Delete Xuất hiện hộp thoại: Hình 13 Shift Cells Left: Xoá ô, kéo các ô cùng hàng sang trái Shift Cells Up: Xoá một ô, kéo các ô cùng cột ở phía dưới lên Entire row: Xoá một hàng tại vị trí hiện hành Entire Column: Xoá một cột tại vị trí hiện hành Che dấu Chọn một trong các cách: Cách 1: Dùng chuột kéo trên vách ngăn tiêu đề cột, hàng cho đến khi 2 vách ngăn trùng nhau Cách 2: Chọn lệnh Format / Row hoặc Columns sau đó Hide hoặc Unhide (che dấu hoặc hiển thị lại) Cách 3: Kích phải chuột trên tiêu đề cột/ tiêu đề hàng - Chọn lệnh Hide/Unhide Xử lý bảng tính (Sheet) Chèn thêm bảng tính Cách 1: Chọn lệnh Insert / WorkSheet Cách 2: Kích phải chuột vào tên bảng tính hiện hành, chọn Insert. Xuất hiện hộp thoại, Chọn WorkSheet và OK Xóa bảng tính Cách 1: Chọn lệnh Edit / Delete Sheet Cách 2: Kích phải chuột vào tên bảng tính hiện hành, chọn Delete. Đổi tên bảng tính Cách 1: Chọn lệnh Format / Sheet / Rename Gõ tên và Enter Cách 2: Kích phải chuột vào tên bảng tính cần sửa, chọn Rename Gõ lại tên và Enter Cách 3: Nhấp đôi chuột vào tên bảng tính gõ lại tên và Enter Che dấu, hiện bảng tính Che dấu: Chọn lệnh Format / Sheet / Hide Hiển thị lại: Chọn lệnh Format / Sheet / Unhide BÀI TẬP CHƯƠNG 2 Bài tập 1: Nhập và định dạng bảng số liệu sau Yêu cầu: nhập đúng giá trị và kiểu dữ liệu. Dữ liệu cột Tổng là loại công thức được tính: Tổng = Toán + Văn + Anh Bài tập 2: Nhập bảng số liệu trên Tính toán các cột số liệu theo yêu cầu: Lương = NC * LCB Phụ cấp = 1/5 Lương Thưởng = 1/2 Lương Thực lãnh = Lương + Phụ cấp + Thưởng – Tạm ứng Tính tổng cộng cho các cột NC, Phụ cấp, Thưởng và Thực lãnh Bài tập 3: Nhập vào và định dạng như bảng số liệu trên Tính số Ngày ở = Ngày đi – Ngày đến Tính Tiền phòng = Số ngày ở * Đơn giá Tính Phụ thu = 5% * Tiền phòng Tính Phải trả = Tiền phòng + Phụ thu Tính tổng cộng cho các cột Bài tập 4: Nhập và định dạng như bảng số liệu trên Tính giá trị cho các cột Phụ cấp, BHXH, BHYT. Biết rằng giá trị cho mỗi cột được tính bằng Tỷ lệ phần trăm của mỗi cột * Lương trong đó Lương = NC * LCB Tính Thực lãnh = Lương + Phụ cấp – BHXH – BHYT Bài tập 5 Yêu cầu: Chỉ sử dụng duy nhất công thức tại ô B2, sao đó sao chép sang các ô khácChương 3: HÀM (FUNCTION) Định nghĩa hàm Hàm là một thành phần của dữ liệu loại công thức và được xem là những công thức được xây dựng sẵn nhằm thực hiện các công việc tính toán phức tạp. Dạng thức tổng quát: (Tham số 1, Tham số 2,...) Trong đó: là tên qui ước của hàm, không phân biệt chữ hoa hay thường. Các tham số: Đặt cách nhau bởi dấu , hoặc ; tuỳ theo khai báo trong Control Panel (xem phần khai báo môi trường - chương II) Cách nhập hàm: Chọn một trong các cách: Cách 1: Chọn lệnh Insert – Function Cách 2: Ấn nút Insert Function trên thanh công cụ. Cách 3: Gõ trực tiếp từ bàn phím. Các hàm thông dụng Nhóm hàm xử lý số ABS Cú pháp: ABS(n) Công dụng: Trả về giá trị tuyệt đối của số n Ví dụ: ABS(-5) KQ: 5 SQRT Cú pháp: SQRT(n) Công dụng: Trả về giá trị là căn bật hai của số n Ví dụ: SQRT(9) KQ: 3 ROUND Cú pháp: ROUND(m, n) Công dụng: Làm tròn số thập phân m đến n chữ số lẻ. Nếu n dương thì làm tròn phần thập phân. Nếu n âm thì làm tròn phần nguyên. Ví dụ 1: ROUND(1.45,1) KQ: 1.5 Ví dụ 2: ROUND(1.43,1) KQ: 1.4 Ví dụ 3: ROUND(1500200,-3) KQ: 1500000 Ví dụ 4: ROUND(1500500,-3) KQ: 1501000 INT Cú pháp: INT(n) Công dụng: Trả về giá trị là phần nguyên của số thập phân n Ví dụ: INT(1.43) KQ: 1 MOD Cú pháp: MOD(m,n) Công dụng: Trả về giá trị phần dư của phép chia số m cho số n Ví dụ: MOD(10,3) KQ: 1 Nhóm hàm xử lý chuỗi LOWER Cú pháp: LOWER(s) Công dụng: Chuyển tất cả các ký tự trong chuỗi s sang chữ thường. Ví dụ: LOWER(“ExCeL”) KQ: “excel” UPPER Cú pháp: UPPER(s) Công dụng: Chuyển tất cả các ký tự trong chuỗi s sang chữ hoa. Ví dụ: UPPER(“ExCeL”) KQ: “EXCEL” PROPER Cú pháp: PROPER(s) Công dụng: Chuyển tất cả các ký tự đầu của mỗi từ trong chuỗi s sang chữ hoa và các ký tự còn lại là chữ thường. Ví dụ: PROPER(“MiCRosoFt ExCeL”) KQ: “Microsoft Excel” LEFT Cú pháp: LEFT(s, n) Công dụng: Trích ra n ký tự của chuỗi s kể từ bên trái. Ví dụ: LEFT(“EXCEL”,2) KQ: “EX” RIGHT Cú pháp: RIGHT(s, n) Công dụng: Trích ra n ký tự của chuỗi s kể từ bên phải. Ví dụ: RIGHT(“EXCEL”,2) KQ: “EL” MID Cú pháp: MID(s, m, n) Công dụng: Trích ra n ký tự của chuỗi s kể từ vị trí thứ m. Ví dụ: MID(“EXCEL”,3,2) KQ: “CE” LEN Cú pháp: LEN(s) Công dụng: Trả về giá trị là chiều dài của chuỗi s. Ví dụ: LEN(“EXCEL”) KQ: 5 TRIM Cú pháp: TRIM(s) Công dụng: Trả về chuỗi s sau khi đã cắt bỏ các ký tự trống ở hai đầu. Ví dụ: TRIM(“ EXCEL ”) KQ: “EXCEL” Chú ý: Nếu các hàm LEFT, RIGHT không có tham số n thì Excel sẽ hiểu n=1 Nhóm hàm thống kê COUNT Cú pháp: COUNT(phạm vi) Công dụng: Đếm số ô có chứa dữ liệu số trong phạm vi. Ví dụ: Để đếm số nhân viên trong bảng dưới thì dùng công thức: COUNT(E2:E6) KQ: 5 COUNTA Cú pháp: COUNTA(phạm vi) Công dụng: Đếm số ô có chứa dữ liệu trong danh sách List. Ví dụ: Để đếm số nhân viên trong cột C ở bảng trên thì dùng công thức: COUNT(C2:C6) KQ: 4 COUNTIF Cú pháp: COUNTIF(phạm vi, điều kiện) Công dụng: Đếm số ô thỏa mãn điều kiện trong phạm vi. Ví dụ: Để đếm số nhân viên thuộc phòng Kế toán (xem bảng ở mục a) thì dùng công thức:COUNTIF(D2:D6, “Kế toán”) KQ: 3 Chú ý: Trừ trường hợp điều kiện là một con số chính xác thì các trường hợp còn lại đều phải bỏ điều kiện trong một dấu ngoặc kép. Ví dụ 1: Đếm số nhân viên có Lương CB là 1.000.000 COUNTIF(E2:E6,1000000) KQ: 2 Ví dụ 2: Đếm số nhân viên có Lương CB nhỏ hơn 1.000.000 COUNTIF(E2:E6,”<1000000”) KQ: 2 MAX Cú pháp: MAX(phạm vi) Công dụng: Trả về giá trị là số lớn nhất trong phạm vi. Ví dụ: Để biết Lương CB cao nhất (xem bảng ở mục a) thì dùng công thức:MAX(E2:E6) KQ: 1.200.000 MIN Cú pháp: MIN(phạm vi) Công dụng: Trả về giá trị là số nhỏ nhất trong phạm vi. Ví dụ: Để biết Lương CB thấp nhất (xem bảng ở mục a) thì dùng công thức: MIN(E2:E6) KQ: 800.000 AVERAGE Cú pháp: AVERAGE(phạm vi) Công dụng: Trả về giá trị là trung bình cộng của các ô trong phạm vi. Ví dụ: Để biết mức lương trung bình (xem bảng ở mục a) thì dùng công thức: AVERAGE(E2:E6) KQ: 980.000 SUM Cú pháp: SUM(phạm vi) Công dụng: Trả về giá trị là tổng các ô trong phạm vi Ví dụ: Để tính tổng Lương CB của tất cả các nhân viên SUM(E2:E6) KQ: 4.900.000 SUMIF Cú pháp: SUMIF(vùng chứa điều kiện, điều kiện, vùng cần tính tổng) Công dụng: Hàm dùng để tính tổng có điều kiện. Chỉ những ô nào trên vùng chứa điều kiện thoả mãn điều kiện thì sẽ tính tổng những ô tương ứng trên vùng cần tính tổng. Ví dụ: Tính tổng Lương CB của nhân viên phòng Kế toánSUMIF(D2:D6, “Kế toán”, E2:E6) KQ: 3.000.000 Hàm xếp vị trí thứ (RANK) Cú pháp: RANK(X, Khối, n) Công dụng: Xếp vị thứ cho giá trị X trong khối. Trong đó, n là tham số qui định cách sắp xếp: Nếu n = 0 (hoặc không có tham số này) thì kết quả sắp xếp theo kiểu lớn đứng trước, nhỏ đứng sau Nếu n khác 0 thì kết quả sắp xếp theo kiểu nhỏ đứng trước, lớn đứng sau Ví dụ: Để xếp vị thứ cho các học sinh trong bảng dưới thì tại ô D2 gõ công thức sau: RANK(C2,$C$2:$C$6) Nhóm hàm xử lý dữ liệu logic IF Cú pháp: IF(biểu thức điều kiện, giá trị 1, giá trị 2) Công dụng: Hàm tiến hành kiểm tra biểu thức điều kiện: Nếu biểu thức điều kiện là True (đúng) thì trả về giá trị 1. Ngược lại, nếu biểu thức điều kiện là False (sai) thì trả về giá trị 2. Ví dụ 1: Hãy điền giá trị cho cột kết quả. Biết rằng: Nếu điểm thi lớn hơn hoặc bằng 5 thì ghi “Đậu”. Ngược lại thì ghi “Hỏng”IF(B2>=5, "Đậu", "Hỏng") Ví dụ 2: IF(2>3,”Sai”, “Đúng”) KQ: “Đúng” Chú ý: Hàm IF dùng để chọn 1 trong 2 lựa chọn nhưng nếu phải chọn nhiều hơn 2 lựa chọn thì dùng hàm IF theo kiểu lồng nhau. Ví dụ 3: Hãy điền giá trị cho cột xếp loại trong bảng dưới. Biết rằng: Nếu ĐTB >=9 thì ghi “Giỏi”, nếu ĐTB >=7 thì ghi “Khá”, nếu ĐTB >=5 thì ghi “TB”, còn lại thì ghi “Yếu”.IF(B2>=9,"Giỏi",IF(B2>=7,"Khá",IF(B2>=5,"TB","Yếu"))) Biểu thức điều kiện của hàm IF luôn phải có kết quả trả về 1 trong 2 giá trị True (đúng) hoặc False (sai). Ví dụ 4: IF(“A”=“B”, ”C”, ”D”) KQ: “D” Ví dụ 5: IF(“A”, ”B”, ”C”) KQ: Lỗi #VALUE Trong hàm IF, nếu không có đối số thứ 3 thì khi biểu thức điều kiện sai hàm sẽ trả về giá trị False. Ví dụ 6: IF(2<3, ”Sai”) KQ: “Sai” Ví dụ 7: IF(2>3, “Sai”) KQ: FALSE AND Cú pháp: AND(biểu thức điều kiện 1, biểu thức điều kiện 2,...) Công dụng: Hàm trả về giá trị True (đúng) nếu tất cả các biểu thức điều kiện đều đúng và trả về giá trị False (sai) khi có ít nhất một biểu thức điều kiện sai. Ví dụ: Hãy điền giá trị cho cột kết quả trong bảng dưới. Biết rằng: Nếu tổng điểm>=10 và điểm Anh văn>=5 thì ghi “Đậu” Các trường hợp còn lại thì ghi “Hỏng”. IF(AND(B2>=10,C2>=5),"Đậu", "Hỏng") OR Cú pháp: OR(biểu thức điều kiện 1, biểu thức điều kiện 2,...) Công dụng: Hàm trả về giá trị True (đúng) nếu có ít nhất một bt điều kiện đúng và trả về giá trị False nếu tất cả các bt điều kiện đều sai. Ví dụ: Hãy điền giá trị cho cột kết quả trong bảng ở mục b. Biết rằng: Nếu tổng điểm>=10 hoặc điểm Anh văn>=5 thì ghi “Đậu” Các trường hợp còn lại thì ghi “Hỏng”. IF(OR(B2>=10,C2>=5),"Đậu", "Hỏng") NOT Cú pháp: NOT(biểu thức logic) Công dụng: Trả về giá trị là phủ định của biểu thức logic Ví dụ: NOT(2<3) KQ: False Nhóm hàm xử lý dữ liệu ngày – tháng – năm: TODAY Cú pháp: TODAY( ) Công dụng: Trả về giá trị là ngày tháng năm của hệ thống NOW Cú pháp: NOW( ) Công dụng: Trả về giá trị là ngày tháng năm và giờ - phút của hệ thống DAY Cú pháp: DAY(biểu thức ngày - tháng - năm) Công dụng: Trả về phần ngày của biểu thức ngày - tháng - năm Ví dụ: DAY(TODAY( )) MONTH Cú pháp: MONTH(biểu thức ngày - tháng - năm) Công dụng: Trả về phần tháng của biểu thức ngày - tháng - năm. Ví dụ: MONTH(TODAY( )) YEAR Cú pháp: YEAR(biểu thức ngày - tháng - năm) Công dụng: Trả về phần năm của biểu thức ngày - tháng - năm. Ví dụ: YEAR(TODAY( )) WEEKDAY Cú pháp: WEEKDAY(biểu thức ngày - tháng - năm , kiểu trả về) Công dụng: Trả về giá trị là số thứ tự của biểu thức ngày -tháng năm trong một tuần tùy thuộc vào kiểu trả về: Nếu kiểu trả về là 1 (hoặc không có) thì chủ nhật được xem là ngày đầu tiên trong tuần và được đánh số thứ tự theo bảng sau: Ví dụ: Giả sử ô A1 chứa ngày 30/07/2006 (tức chủ nhật) thì: WEEKDAY(A1) KQ: 1 Nếu kiểu trả về là 2 thì thứ 2 được xem là ngày đầu tiên trong tuần và được đánh STT theo bảng sau: Ví dụ: Giả sử ô A2 chứa giá trị ngày 31/07/2006 (tức thứ 2) thì WEEKDAY(A2) KQ: 1 Nếu kiểu trả về là 3 thì thứ 2 được xem là ngày đầu tiên trong tuần và và được đánh STT theo bảng sau: Ví dụ: Giả sử ô A2 chứa giá trị ngày 31/07/2006 (thứ 2) thì WEEKDAY(A2) KQ: 0 Nhóm hàm xử lý dữ liệu giờ – phút – giây: SECOND Cú pháp: SECOND(biểu thức giờ - phút - giây) Công dụng: Trả về phần giây của biểu thức giờ - phút - giây Ví dụ: Giả sử tại ô D5 chứa gía trị 08:30:20 thì: SECOND(D5) KQ: 20 MINUTE Cú pháp: MINUTE(biểu thức giờ - phút - giây) Công dụng: Trả về phần phút của biểu thức giờ - phút – giây Ví dụ: Giả sử tại ô D5 chứa gía trị 08:30:20 thì: MINUTE(D5) KQ:30 HOUR Cú pháp: HOUR(biểu thức giờ - phút - giây ) Công dụng: Trả về phần giờ của biểu thức giờ - phút – giây Ví dụ: Giả sử tại ô D5 chứa gía trị 08:30:20 thì:HOUR(D5) KQ: 8 Nhóm hàm tra cứu VLOOKUP Cú pháp: VLOOKUP(n, khối, m, r) Công dụng: Tiến hành tìm giá trị n trong cột đầu tiên của khối và lấy giá trị tương ứng trên cột thứ m. Nếu r=0 (hoặc FALSE) thì tìm giá trị chính xác bằng với n. Nếu không tìm thầy thì trả về lỗi #N/A (lỗi không tìm thấy) Ví dụ: Điền giá trị cho cột Phụ cấp trong bảng dưới dựa vào chức vụ và trả ở bảng 1: tại ô D2 nhập: VLOOKUP(C2,$F$3:$G$7,2,0) Nếu r=1 (hoặc TRUE) thì cột đầu tiên của khối phải được sắp xếp tăng dần và lúc đó nếu không tìm thấy giá trị chính xác với n sẽ lấy giá trị tương ứng gần của n. Ví dụ: Điền giá trị cho cột Xếp loại trong bảng sau dựa vào ĐTB và tra ở bảng tra: nhập vào tại ô D2: =VLOOKUP(C2,$F$2:$G$5,2,1) HLOOKUP HLOOKUP có cú pháp và công dụng tương tự VLOOKUP nhưng được dùng trong trường hợp bảng tra được bố trí theo hàng ngang thay vì theo hàng dọc như VLOOKUP. Ví dụ: Điền giá trị cho cột Phụ cấp trong bảng ở mục a dựa vào chức vụ và trả ở bảng 2: HLOOKUP(C2,$C$13:$G$14,2,0) MATCH Cú pháp: MATCH(n, Khối, r) Công dụng: Trả về số thứ tự của n trong khối. Trong đó, r có ý nghĩa tương tự r trong hàm VLOOKUP. Ví dụ: Hãy xác định vị trí của nhân viên tên Chi trong cột B của hình 1 mục a: MATCH(“Chi”, B2:B11,0) KQ: 7 INDEX Cú pháp: INDEX(Khối, m, n) Công dụng: Trả về giá trị của ô nằm giao của hàng m và cột n trong khối. Ví dụ: Hãy xác định giá thuê phòng Loại B ở tầng 3 trong bảng sau là bao nhiêu?INDEX($C$3:$E$5,2,3) KQ: 260 BÀI TẬP CHƯƠNG 3 Bài tập 1: Cho bảng số liệu sau: Câu 1: Tính cột tổng: = tổng điểm của từng môn nhân với hệ số của môn đó Câu 2: Tính điểm trung bình = tổng điểm chia cho tổng hệ số Câu 3: Tính cột Xếp hạng Câu 4: Xếp loại cho HS dựa vào điểm trung bình Điểm TB >= 9 xếp loại A Điểm TB >= 7 xếp loại B Điểm TB >= 5 xếp loại C Còn lại xếp loại D Câu 5: Xác định điểm TB cao nhất, thấp nhất Câu 6: Tính tổng số học sinh xếp loại A Bài tập 2: Nhập bảng số liệu sau: BẢNG TÍNH TIỀN ĐIỆN Câu 1: Tính cột định mức, biết rằng khách hàng ở khu vực 1 thì là 50, khu vực 2 là 100 và khu vực 3 là 150 Câu 2: Tính lượng điện tiêu thụ biết rằng Tiêu thụ = số mới – số cũ Câu 3: Tính Tiền điện biết rằng Tiền điện = Tiêu thụ * đơn giá trong đó Nếu số KW Tiêu thụ <= Số KW định mức của khu vực mình thì tính giá 450đ/KW Ngược lại, cứ mỗi KW vượt định mức tính giá 800 đ/KW (Số KW trong định mức vẫn là 450 đ/KW) Câu 4: Tính thuê bao = 5% * tiền điện Câu 5: Tính Phải trả = tiền điện + thuê bao Bài tập 3: Nhập bảng số liệu sau: Câu 1: Tính cột tiền VNĐ = số ngoại tệ * tỷ giá của loại ngoại tệ đó Câu 2: Tính tổng tiền ngoại tệ thu đổi được trong ngày Bài tập 4: Yêu cầu: Tính cột Lương biết rằng Lương = (HSL+HSTN)*Công*Đơn vị tiền lương/Công qui định Tính Xếp loại cho nhân viên: Nếu Công>=Công qui định thì xếp loại A Nếu trong tháng nghỉ không quá 3 ngày thì xếp loại B Còn lại xếp loại C Tính tiền thưởng cho nhân viên biết rằng Tiền thưởng sẽ dựa theo xếp loại của nhân viên (trong Bảng tiền thưởng) Tính cột BHXH = Lương * Tỷ lệ %BHXH và tính BHYT = Lương * Tỷ lệ % BHYT Tính Thực lĩnh = Lương + Thưởng – BHYT – BHXH – Tạm ứng Tính tổng số nhân viên nhóm VP: Đếm có bao nhiêu nhân viên thuộc nhóm VP. Bài tập 5: Yêu cầu: Tính cột số tuần biết rằng: Số tuần = (Ngày đi – Ngày đến)/7 và chỉ lấy phần nguyên Tính cột ĐG tuần biết rằng ĐG Tuần được tính dựa theo Loại phòng (Bảng giá theo loại phòng). Kí tự đầu tiên bên trái của cột Phòng dịch vụ thể hiện Loại phòng Tính cột TT Tuần = Số tuần * ĐG tuần Tính cột Số ngày = (Ngày đi – Ngày đến)/7 và chỉ lấy phần dư Tính cột TT Ngày = Số ngày * Đơn giá Tính cột Dịch vụ = (Ngày đi – Ngày đến) * Giá dịch vụ mỗi ngày (Dựa theo bảng Giá dịch vụ và Loại phòng. Ký tự đầu tiên bên phải của cột Phòng Dịch vụ thể hiện loại phòng) Tính cột Tổng = TT Tuần + TT Ngày + Dịch vụ  Bài tập 6: Cho bảng tính sau: Yêu cầu: Tính cột Diện CS biết rằng cột STT gồm 4 ký tự, ký tự thứ 2 cho biết Diện CS Tính cột Tổng điểm = Môn CS + Môn CN1 + Môn CN2 + Điểm CS trong đó Điểm CS là 1 nếu Diện CS là 1, Điểm CS là 0.5 nếu Diện CS là 2 còn lại là 0. Tính cột Loại HB biết rằng: Loại HB = A nếu Tổng điểm >= 24 và không có môn nào nhỏ hơn 8 Loại HB = B nếu: Hoặc Tổng điểm >= 24 và không có môn nào nhỏ hơn 6 Hoặc Tổng điểm >= 22 và không có môn nào nhỏ hơn 6 và Diện CS là 1 Còn lại là C Tính cột Học bổng (Dựa vào Loại học bổng và được tra cứu trên bảng Loại học bổng) Tính cột Tổng: Tổng tiền học bổng đã phát ra cho từng loại học bổng Bài tập 7: Cho bảng tính sau: Yêu cầu: Tính cột Xếp loại: dựa vào số Ngày công Nếu Ngày công >= 22 thì xếp loại A Nếu Ngày công >= 20 thì xếp loại B Nếu Ngày công >= 18 thì xếp loại C Còn lại xếp loại D Tính cột Tiền thưởng dựa vào bảng tiền thưởng.Chương 4: CHÈN HÌNH ẢNH – ĐỒ THỊ Chèn các đối tượng hình ảnh vào bảng tính Cách chèn tương tự như trong Microsoft Word. Đồ thị Giới thiệu đồ thị (Graph) Biểu đồ là sự biểu diễn các con số, dữ liệu bằng hình ảnh để người đọc nắm bắt thông tin một cách trực quan hơn Đồ thị có thể được sử dụng để minh hoa sự biến động của chuỗi số liệu trong bảng tính, cho phép nhìn khái quát các đối tượng và tiên đoán hướng phát triển trong tương lai. Trước khi tạo lập đồ thị, cần phải xác định phạm vi dữ liệu tham gia minh hoạ trong đồ thị. Ví dụ: Cho bảng số liệu về doanh số bán ra trong hai năm của một công ty thương mại như hình bên: Yêu cầu: Tạo lập đồ thị So sánh Doanh số của hai năm 2007, 2008 theo từng Quý Thao tác đồ thị Chọn phạm vi dữ liệu cần biểu diễn lên đồ thị Chọn lệnh Insert / Chart (hoặc click nút hình đồ thị trên thanh công cụ Standard). Xuất hiện hộp thoại Chart Wizard - Step 1 of 4 - Chart Type: Hình 14 Tại đây chọn dạng thức đồ thị cần dùng trong danh sách bên trái và chọn một dạng con của nó trong danh sách bên phải và ấn nút Next Hộp thoại Chart Wizard - Step 2 of 4 - Chart Source Data: Hình 15 Tại đây ta khai báo vùng dữ liệu sẽ dùng để tạo đồ thị (Data range). Thẻ Data Range: Khai báo dữ liệu nguồn Data Range: Toạ độ khối dữ liệu dùng để vẽ đồ thị Series in: Chọn dạng đồ thị đọc dữ liệu theo hàng (Row) hay Cột (Column) Thẻ Series: Khai báo từng chuỗi số liệu trên đồ thị, trong đó: Series: Chứa các chuỗi dữ liệu tham gia đồ thị Values: Toạ độ khối chứa giá trị Name: Toạ độ ô chứa tên của chuỗi dữ liệu Category (X) Axis labels: Khối dùng làm nhãn trục X Sau khi khai báo xong tại hộp thoại này, ấn nút Next Hộp thoại Chart Wizard - Step 3 of 4 - Chart Option: Hình 16 Hộp thoại này dùng để khai báo các nội dung về: Tiêu đề của đồ thị (Titles), trục toạ độ (Axes), đường lưới (Gridlines), chú thích (Legend), nhãn (Data Labels), bảng dữ liệu (Data Table)... Khai báo xong, ấn nút Next để tiếp tục. Hộp thoại Chart Wizard - Step 4 of 4 - Chart Location: Hình 17 Khai báo vị trí đặt đồ thị: As new sheet: đồ thị được đặt ở một Sheet khác với Sheet chứa số liệu As object in: đồ thị được đặt trên cùng Sheet với bảng số liệu Khai báo xong ta ấn nút Finish để kết thúc tạo lập đồ thị. Kết quả: Hình 18 Hiệu chỉnh đồ thị Hoặc Double Click vào thành phần cần hiệu chỉnh trên đồ thị để mở hộp thoại hiệu chỉnh của thành phần được chọn. Hoặc kích chọn thành phần này trên đồ thị rồi chọn lệnh Format - Select <tên thành phần)... Các thành phần trên đồ thị Thành phần Ý nghĩa Chart Title Tiêu đề của đồ thị X Title Tiêu đề của trục X Y Title Tiêu đề của trục Y Category label Tiêu đề dữ liệu số Data Series Đường biểu diễn của biểu đồ Gridlines Các đường lưới Axes Trục toạ độ Data Labels Nhãn Data Table Bảng dữ liệu Legend Chú thích Hình 19 BÀI TẬP CHƯƠNG 4 Bài tập 1: Cho bảng số liệu sau: BÁO CÁO DOANH THU QUÝ 3 NĂM 2008CỬA HÀNG SỐ 1 Quầy hàng Tháng 7 Tháng 8 Tháng 9 Quý 3 Quầy điện tử 25.5 23 24.5 73 Quầy xe máy 47.5 30 27 104.5 Quầy đồ chơi trẻ em 7.5 2.5 5.5 15.5 Quầy bếp ga 12.5 11.5 7.5 31.5 Tổng cộng 93 67 64.5 224.5 Hãy tạo đồ thị biểu diễn sự so sánh doanh thu giữa các quầy hàng qua các tháng bằng biểu đồ hình cột và hình tròn. Bài tập 2: Cơ cấu giá trị sản xuất ngành trồng trọt (%) NămCác nhóm cây 1990 2002 Cây lương thực 67.1 60.8 Cây công nghiệp 13.5 22.7 Cây ăn quả, rau đậu và cây khác 19.4 16.5 Tạo các biểu đồ sau: Bài tập 3: Biểu đồ cơ cấu GDP của các ngành dịch vụ năm 2002 (%) Thương nghiệp, dịch vụ sữa chữa 36.7 Khách sạn, nhà hàng 8.3 Dịch vụ cá nhân và công cộng 6.0 Giao thông vận tải, bưu chính viễn thông 10.2 Tài chính, tín dụng 4.7 Kinh doanh tài sản, tư vấn 11.9 KHCN, giáo dục, y tế, văn hóa, thể thao 15.1 Quản lý nhà nước, đoàn thể và bảo hiểm bắt buộc 7.1 Vẽ biểu đồ sau: Bài tập 4: Biểu đồ mật độ điện thoại cố định (số máy / 100 dân) Năm Mật độ 1991 0.2 1995 1.0 1997 2.1 1999 3.0 2002 7.1 Bài tập 5: Cơ cấu GDP của nước ta thời kỳ 1991 - 2002 (%) 1991 1993 1995 1997 1999 2001 2002 Tổng số 100.0 100.0 100.0 100.0 100.0 100.0 100.0 Nông, lâm, ngư nghiệp 40.5 29.9 27.2 25.8 25.4 23.3 23.0 Công nghiệp - xây dựng 23.8 28.9 28.8 32.1 34.5 38.1 38.5 Dịch vụ 35.7 41.2 44.0 42.1 40.1 38.6 38.5 Chương 5: CƠ SỞ DỮ LIỆU Khái niệm Là tập hợp các thông tin, dữ liệu được tổ chức theo cấu trúc hàng, cột để khai thác, truy cập nhan