Giáo trình Chương trình bảng tính điện tử Microsoft Excel

eet2 sẽ nhận giá trị của ô A2 ở file dulieu.xls\sheet1. Các loại địa chỉ: Trong Excel cung cấp hai loại địa chỉ, địa chỉ tương đối và địa chỉ tuyệt đối. Ví dụ trong một bảng, ở ô B2 ta viết =A1. Địa chỉ ta viết như vậy gọi là địa chỉ tương đối, nếu đổi cách viết A1 thành $A$1 thì địa chỉ này trở thành địa chỉ tuyệt đối. Sự khác nhau giữa địa chỉ tương đối và địa chỉ tuyệt đối: thứ nhất là nếu giá trị trong ô A1 di chuyển sang một ô khác (dùng Cut và Paste) thì giá trị trong ô B2 sẽ dữ nguyên nếu ta dùng địa chỉ tuyệt đối, ngược lại giá trị này sẽ =0 khi ta dùng địa chỉ tương đối. Thứ hai khi ta dùng công cụ tự động điền dữ liệu vào các ô dựa vào dữ liệu của các ô bên cạnh (Automatically fill in data based on adjacent cells- Fill handle) thì biểu thức điền vào các ô sẽ không thay đổi nếu ta dùng địa chỉ tuyệt đối, ngược lại nếu dùng địa chỉ tương đối thì biểu thức điền vào các ô sẽ tự động thay đổi. + Một số kiểu dữ liệu trong Excel Kiểu dữ liệu Tiếng anh Tiếng việt Ví dụ General Chung chung Trung tam CNTT Nghe An; 123; 1.234 Number Số 1432498; Currency Tiền tệ $ 30 Date Ngày tháng 12/25/2002 Time Thời gian 10:30 Percentage Phần trăm 50 % Scientific Khoa học 1.00E4; 1.254E00; 1.00E+03 Text Chuỗi (ký tự) Trung tam CNTT Nghe An LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel11 II.3. Làm việc với biểu đồ và đồ thị Trong nhiều trường hợp ta cần xem xét sự quan hệ giữa hai hay nhiều dữ liệu với nhau. Ví dụ: xét số lượng hàng hoá bán ra trong một năm theo thời gian (đơn vị là tháng) để biết vào khoãng thời gian nào bán được số lượng hàng nhiều nhất, ít nhất. Hay cùng một mặt hàng, ta cần xem xét số lượng bán ra ở các cửa hàng khác nhau như thế nào? cửa hàng nào bán được nhiều nhất, ít nhất? Excel cung cấp cho người sử dụng công cụ Biểu đồ và Đồ thị cho phép quan sát trực quan các yêu cầu này. Giúp cho quá trình nhận định, đánh giá số liệu được thuận tiện. Vậy việc sử dụng công cụ này như thế nào? ta xét các ví dụ sau: Ví dụ 1: Nhập vào Excel bảng dữ liệu gồm hai cột: cột Tháng và cột Doanh thu như hình 1. Để vẽ đồ thị mô tả quan hệ Doanh thu theo từng Tháng ta chọn (bôi đen) vùng dữ liệu sau đó chọn menu: Insert\Chart, cửa sổ Chart Wizard hiện ra; Trong ô Chart type ta chọn mục XY(Scatter) và ở vùng bên phải cửa sổ này ta chọn ô thứ 2 trên xuống (được bôi đen) như hình 1, cuối cùng ta chọn nút Finish để hoàn thành. Kết quả ta có đồ thị như hình 2. Chú ý: Chart Wizard có 4 bước, ở đây ta kết thúc từ bước 1 như vậy ta đã sử dụng các lựa chọn mặc định ở các bước sau đó. Các bước sau cho phép đặt tiêu đề cho trục dọc (trục tung), trục ngang (trục hoành), cho phép chọn bảng tính để hiển thị kết quả v.v. Chart Wizard cung cấp nhiều cách thể hiện khác nhau (xem hình 1). Tưong tự cách làm trên, ta có thể thử nghiệm các lựa chọn còn lại để biết và chọn ra lựa chọn phù hợp cho mình. ▲ 1. Vùng dữ liệu được chọn và cửa sổ Chart Wizard LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel12 Ví dụ 2: Xem Nhập vào Excel bảng dữ liệu, chọn vùng dữ liệu cần vẽ sơ đồ (hình 3a), chọn menu: Insert\Chart, cửa sổ Chart Wizard hiện ra; ở bước một chọn Pie và ở vùng bên phải chọn ô thứ hai ngoài, hàng trên (hình 3b). ◄ 2. Đồ thị mô tả quan hệ doanh thu theo tháng Từ đồ thị ta thấy doanh thu các tháng đầu và một số tháng cuối năm nói là thấp; Và doanh thu cao ở các tháng 7,8,9; ◄ 3. Vùng dữ liệu được chọn và bước 1 của Chart Wizard ◄ 4. Thực hiện lựa chọn ở bước 3 LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel13 Sau đó chọn nút lệnh “Next >” đến bước 4, chọn như hình 4; Kết quả thực hiện được trình bày trên hình 5. Tương tự cách làm này ta có thể mô tả dữ liệu của bất kỳ lĩnh vực nào bằng biểu đồ hoặc đồ thị. Chúng sẽ trợ giúp cho việc đánh giá phân tích cũng như tạo các báo cáo trực quan, dễ hiểu. ◄ 5. Biểu đồ mô tả quan hệ doanh thu của ba doanh nghiệp Ở đây khi ta chọn từng phần trong biểu đồ các thông tin về biểu đồ sẽ hiện ra. LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel14 II.4. Định dạng bảng tính + Định dạng cho một ô hoặc một nhóm ô Chọn (bôi đen) ô hoặc một nhóm ô cần định dạng, sau đó chọn menu: Format\Cells ... cửa sổ Format Cells hiện ra: Trong cửa sổ này, tương ứng với các táp cho phép ta chọn các định dạng cho dữ liệu được mô tả tóm tắt trong bảng sau: Tên táp Công việc sẽ thực hiện Chi tiết Number Chọn kiểu dữ liệu Như mô tả ở phần kiểu dữ liệu Alignment Chọn căn lề Căn trái, phải, chính giữa, căn đều hai bên; dữ liệu nằm ngang, dựng đứng, nghiêng v.v Lựa chọn Warp text cho phép hiện nhiều dòng trong một ô Font Chọn phông chữ Chọn: mẫu chữ (hộp Fonts) kiểu chữ (Fonts style) cỡ chữ (Size) màu chữ (Color), vv Border Chọn đường kẻ viền bao quanh Chọn: có hay không có đường bao xung quanh các đường kẻ ở giữa có gạch chéo kiểu đường (nét đứt, liền, vv) Patterns Các mẫu tô nền Chọn màu tô nền Sau khi chọn kiểu dữ liệu, nhập dữ liệu vào và chọn định dạng cho các ô ta có thể thêm lời ghi chú cho từng ô dữ liệu trong đó bằng cách chọn menu: Insert\Comment, một hộp soạn thảo hiện ra, gõ vào lời ghi chú, khi gõ xong nháy chuột ra ngoài hộp soạn thảo để kết thúc. ◄ 1. Cửa sổ định dạng dữ các ô dữ liệu. LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel15 Như vậy, mỗi ô trong Excel chứa các thông tin sau: nội dung, định dạng, lời ghi chú. Phần trên đã trình bày cách đưa dữ liệu vào, đặt định dạng và thêm lời ghi chú. Vậy muốn loại bỏ chúng thì làm như thế nào? Việc này được thực hiện bằng cách chọn menu: Edit\Clear và chọn: All để xoá tất cả, Formats để xoá định dạng, Contents để xoá nội dung và Comments để xoá lời ghi chú. + Thay đổi độ rộng cột và chiều cao dòng Ta thực hiện như sau: trước hết là chọn cột (hoặc dòng) cần thay đổi, sau đó chọn menu: Format\Column\Width; điền vào giá trị độ rộng; chọn OK để thay đổi độ rộng. Format\Row\Height; điền vào giá trị chiều cao; chọn OK để thay đổi chiều cao. Cách làm nhanh hơn là đưa chuột đến đường phân cách giữa hai cột (gạch đứng), hoặc giữa hai hàng (đường ngang) trên thanh ghi địa chỉ A,B,C ... hoặc 1,2,3 ..., hình dạng con chuột đổi thành mũi tên hai chiều, ấn và rê chuột để thay đổi. II.5. In bảng tính Trước khi in ta phải định dạng trang giấy bằng cách chọn: File\Page Setup, xuất hiện hộp thoại Page Setup với 4 táp như sau: Trong táp Page (hình 1): - Trong Oriention: chọn Portrait để in dọc, Landscape để in ngang trang giấy. - Trong Scaling: nên dữ Adjust to 100% normal size. Trường hợp chỉ thiếu vài dòng sẽ trọn một trang nên chọn Fit to 1 page để Excel ép lại cho vừa một trang. - Trong Paper size: Chọn cỡ giấy in. thông thường ta chọn giấy khổ A4 - Trong Print Quality: chọn chất lượng in; 600 dpi nghĩa là in với độ phân giải 600 điểm/in; Trong táp Margins (hình 2): Đặt các canh lề cho trang giấy - Tương ứng: Top, Bottom, Left và Right là lề trên, dưới, trái và phải; Tức là vùng in sẽ cách các mép tương ứng của trang giấy một khoảng bao nhiêu cm (hoặc in). ◄ 1. Hộp thoại Page Setup, trong táp Page LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel16 - Header, Footer khoảng cách mép trên và mép dưới trang giấy đến mép dưới (trên) của phần Header (Footer). - Horizontally, Vertically: Canh giữa theo chiều ngang, dọc hay không? Trong táp Header/Footer: dùng để định tiêu đề đầu trang và cuối trang. Excel sẽ in ra một dòng trên đỉnh trang gọi là dòng đầu trang (Header) và một dòng ở cuối trang gọi là dòng chân trang (Footer). Trong mục Header và mục Footer của táp này có các dòng gợi ý có sẵn ta có thể chọn chúng để in. Trường hợp ta không thích các dòng này mà muốn thay nó bằng một dòng khác ta chọn: Custom Header và Custom Footor. Khi thực hiện lựa chọn này một cửa sổ soạn thảo sẽ xuất hiện, trong đó Excel chia dòng ra làm 3 phần: Trái, Giữa và Phải ta có thể soạn thảo vào các ô này (hình 3). ▲ 3. Cửa sổ soạn thảo dòng đầu trang Header (cửa sổ soạn thảo dòng cuối trang giống cửa sổ này, chỉ khác tiêu đề là Footer) ◄ 2. Táp căn lề trang (táp Margins). LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel17 Trong cửa sổ soạn thảo có thanh công cụ, gồm các biểu tượng theo thứ tự từ trái qua phải là: Font chữ (chọn Fonts), số trang, tổng số trang, ngày giờ, tên file, tên bảng tính (cho phép chèm vào). Táp Sheet: Cho phép in một vùng dữ liệu bất kỳ trong bảng tính - Hộp Print Area: nhập vào địa chỉ vùng dữ liệu cần in ra - Trong Page Order: cho phép chọn hướng in; Sau khi định dạng trang xong, trước khi in ra giấy ta nên xem thử các trang in đã vừa ý chưa bằng cách chọn menu: File\Print Preview (hoặc chọn nút Print Preview trong cửa sổ Page Setup, hoặc chọn biểu tượng Print Preview trên thanh công cụ) Thực hiện in dữ liệu ra giấy bằng cách chọn menu: File\Print, hộp thoại Print hiện ra: Khung Printer: chọn máy in, lựa chọn Print to file: in ra một file mới, mặc định không chọn nó sẽ in ra giấy. Khung Print range: chọn trang in, All: in tất cả các trang; Pages: từ trang ... đến trang ...; Khung Print what: Selection: in vùng dữ liệu đã chọn, Active sheets: in các bảng tính đang kích hoạt; Entire workbook: in tất cả các bảng tính trong file. Khung Copies: Number of copies: số bản được in ra (sao chép nhiều bản), Collate: có duyệt thứ tự trang in. Chọn: nút Print Preview để xem thử, nút OK để Excel thực hiện in và nút Cancel để huỷ bỏ. ◄ 4. Khung Page Order trong táp Sheet, cho phép chọn hướng trang in ◄ 5. Hộp thoại in dữ liệu LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel18 III. Phân tích dữ liệu Sau khi vào dữ liệu xong, việc phân tích dữ liệu cung cấp thêm những thông tin có giá trị. Ví dụ: Kết thúc kỳ thi tuyển sinh kết quả thi đã được lưu trữ, dữ liệu này mới cho biết thí sinh nào đạt hay chưa đạt, bao nhiêu điểm. Khi ta muốn biết các thông tin tổng hợp như: có bao nhiêu phần trăm thí sinh đạt loại xuất sắc, giỏi, khá, trung bình, yếu và kém; theo khu vực hay theo tỉnh thành hoặc theo từng trường học thì tỷ lệ này là bao nhiêu; để qua đó đánh giá giá chất lượng đào tạo, có ý kiến phản ánh và luật lập kế hoạch đào tạo. Các bài toán trong quản lý hay trong kinh tế v.v cũng có những nhu cầu tương tự. Việc xử lý dữ liệu trong Excel nên thực hiện bằng cách: dữ liệu đầu vào -> xử lý -> dữ liệu đầu ra. Trong đó dữ liệu đầu vào có thể là một bảng (có một số hàng và cột), hoặc một Bảng tính (sheet), hoặc là một file dữ liệu (book) tuỳ vào dữ liệu này là ít hay nhiều (mỗi sheet có thể lưu 65000 hàng, mỗi hàng 256 cột). Dữ liệu đầu ra có thể lưu trên một file khác, sheet khác hoặc là một bảng khác ngay trong sheet đó. Phần này chúng tôi cung cấp một số hàm và công cụ của Excel dùng cho việc phân tích dữ liệu, và các ví dụ áp dụng đơn giản. Những ví dụ đơn giản, các hàm được cung cấp cho học viên như những công cụ cơ bản để học viên vận dụng linh hoạt chúng trong từng bài toán cụ thể. III.1. Sử dụng các hàm 1) Danh sách một số hàm thường dùng (các hàm khác ta có thể tra cứu bằng chương trình trợ giúp của Excel bằng cách chọn menu: Help\Contents and Index\tab Index\gõ vào từ khoá “functions”\Display và thực hiện theo hướng dẫn trên màn hình để xem) Phân loại Tên hàm Ý nghĩa Ví dụ minh hoạ Xử lý số abs(n) cho giá trị tuyệt đối của số n abs(-2) = 2 sqrt(n) cho giá trị căn bậc hai của số n (n>0) sqrt(9) = 3 power(x,n) cho giá trị xn power(2,3) = 8 int(x) cho giá trị phần nguyên của x int(3.45) = 3 mod(n,m) lấy phần dư của phép chia n cho m mod(10,3) = 1 round(x,n) làm tròn số x đến n số thập phân round(2.1246,3)=2.125 min(danh sách giá trị) cho giá trị nhỏ nhất trong một danh sách các giá trị min({1,2,5,0}) = 0 max(danh sách giá trị) cho giá trị lớn nhất trong một danh sách các giá trị max({1,2,5,0}) = 5 average( danh sách giá trị) cho giá trị trung bình của các giá trị trong danh sách average({1,2,6,0}) = 2.25 sum(danh sách giá trị) tính tổng các giá trị có trong danh sách sum({1,2,6,0}) = 9 product( danh sách giá trị) tính tích các giá trị có trong danh sách product(2,3,4) = 24 pi() cho số pi pi() = 3.141593 sin(x) tính sin của x radian sin(pi()/2) = 1 LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel19 cos(x) tính cos của x radian cos(pi()/2) = 0 tan(x) tính tang của x radian tan(pi()/4) = 1 asin(x) tính arcsin của x asin(1) = 1.570796 acos(x) tính arccos của x acos(0) = 1.570796 atan(x) tính arctang của x atan(1) = 0.785398 exp(x) tính ex exp(1) = 2.718282 Xử lý chuỗi left(s,n) cho chuỗi con của chuỗi s được tính từ trái qua phải n ký tự left(“nghe an”,4) = nghe mid(s,n1,n2 ) cho chuỗi con của chuỗi s được lấy ra từ vị trí n1 ra n2 ký tự mid(“nghe an”,6,2) = an right(s,n) cho chuỗi con của chuỗi s được tính từ phải qua trái n ký tự right(“nghe an”,4) = e an len(s) cho độ dài của chuỗi s len(“nghe an”) = 7 lower(s) chuyển chuỗi s thành chữ thường lower(“Nghe An”) = nghe an upper(s) chuyển chuỗi s thành chữ hoa lower(“Nghe An”) = NGHE AN proper(s) chuyển các chữ đầu của mỗi từ trong chuỗi s thành chữ hoa proper(“nghe an”) = Nghe An trim(s) cắt các ký tự trống ở hai đầu chuỗi s trim(“ an “) = “an” Hàm tìm kiếm count(danh sách giá trị) cho số giá trị là số có trong danh sách count({1,4,6,”a”,”c”}) = 3 counta(danh sách giá trị) cho số giá trị có trong danh sách (không tính các ô không chứa dữ liệu) counta({1,4,6,”a”,”c”}) = 5 khi các ô A1:A5 tương ứng nhận các giá trị: {1,4, , ”a”,”c”} (A3 không có dữ liệu) thì counta(A1:A5) = 4 rank(x, danh sách số, n) cho biết vị trí của số x trong danh sách các số; tương ứng n=1 và n=0 (hoặc không viết) các số trong danh sách sắp theo thứ tự giảm dần và tăng dần. các ô A1:A5 lần lượt chứa các giá trị: 7, 3.5, 3.5, 1, và 2 thì: rank(A2,A1:A5,1) = 3 rank(A1,A1:A5,1) = 5 rank(A1,A1:A5,0) = 1 rank(A1,A1:A5) = 1 vlookup(x, bảng giá trị, n, điều kiện ) Tìm giá trị x trong bảng giá trị và trả về giá trị cùng hàng với giá trị tìm được đầu tiên và ở cột thứ n trong bảng. Nếu điều kiện là TRUE, cách tìm gần đúng, FALSE tìm chính xác; cho các ô nhận giá trị tương ứng sau: A1:A3 là A, B, C B1:B3 là 10, 8, 5 vlookup("C",A1:B2,2, false) = 5 hlookup(x, bảng giá trị, n, điều kiện ) Tương tự hàm vlookup, hàm này không tìm theo cột mà tìm theo hàng. cho các ô nhận giá trị tương ứng sau: A1, B1, C1 là A, B, C A2, B2, C2 là 10, 8, 5 hlookup("A",A1:C2,2, false) = 10 LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel20 frequency (danh sách giá trị, nhóm giá trị) xác định tần suất xuất hiện của một nhóm giá trị nào đó trong một danh sách giá trị. xem trợ giúp (chọn menu Help) Xử lý cơ sở dữ liệu dsum(bảng dữ liệu, n, vùng tiêu chuẩn) Tính tổng các số thoã mãn tiêu chuẩn trong cột N của bảng dữ liệu. Tương tự như hàm dsum còn có các hàm: dmax, dmin, dcount, daverage, v.v. xem các ví dụ áp dụng sumif( danh sách 1, điều kiện, danh sách 2) Tính tổng các số trong danh sách giá trị 2 thoả mãn điều kiện ở danh sách giá trị 1. Tương tự cho hàm countif(danh sách giá trị, điều kiện) cho các ô nhận giá trị tương ứng sau: A1: A4 là 1, 2, 3, 4 B1: B4 là 2, 4, 6, 8 sumif(A1:A4,”>2”,B1:B4) = 14 (6+8) Logic if(biểu thức logic, biểu thức 1, biểu thức 2) Nếu biểu thức logic đúng hàm trả về giá trị của biểu thức 1, không thì trả về giá trị của biểu thức 2. Biểu thức 1 và 2 có thể chứa một hàm if khác (if lồng nhau) khối A1:B4 cho như trên: if(B1>B2,A1+A3,A1*A3) = 3 if(B1<B2,A1+A2,A1*A3) = 4 and(điều kiện 1, điều kiện 2, ...) cho giá trị TRUE nếu tất cả các điều kiện đều đúng, không thì cho giá trị FALSE and(B1<B2,B2<B3) = TRUE and(B1>B2,B2<B3) = FALSE or(điều kiện 1, điều kiện 2, ...) cho giá trị FALSE nếu tất cả các điều kiện đều sai, không thì cho giá trị TRUE or(B1>B2,B2<B3) = TRUE or(B1>B2,B2>B3) = FALSE not(điều kiện ) cho giá trị TRUE nếu điều kiện đều sai, và ngược lại cho giá trị FALSE khi điều kiện đúng not(B1>B2) = TRUE not(B1<B2) = FALSE Thời gian day(dữ liệu) Dữ liệu có kiểu là ngày tháng (date), hàm cho giá trị ngày trong đó gõ vào ô A1: 12/25/2002 khi đó: day(a1) = 25 moth(dữ liệu) hàm cho giá trị tháng trong đó month(a1) = 12 year(dữ liệu) hàm cho giá trị năm trong đó year(a1) = 2002 2) Các ví dụ áp dụng Ví dụ 1: Xử lý kết quả thi của học sinh. (Thực tế số học sinh có thể là hàng nghìn, hay chục nghìn hay lớn hơn nhưng ở đây ta xét một số ít đại diện mang tính chất nguyên lý. Khi áp dụng thực tế động tác còn lại chỉ là thiết lập mẫu và thực hiện sao chép, kéo thả chuột một cách nhanh chóng.) Công việc cần làm gồm có: LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel21 + Nhập thông tin, kết quả thi của học sinh + Tính tổng số điểm của các môn thi, điểm thi thấp nhất, cao nhất và trung bình của các học sinh + Xếp loại học sinh theo kết quả thi + Xét điểm lên lớp cho các học sinh + Thống kê học lực của học sinh (bao nhiêu phần trăm học sinh đạt loại xuất sắc, giỏi, khá, trung binh, yếu và kém) Trong đó Đối tượng ưu tiên được nhập vào cột “Uu tien” và TB: thương binh, LS: liệt sỹ, VC: vùng cao, KK: khó khăn, KC: không có (không hưởng ưu tiên). Để tính tổng điểm ta thực hiện như sau: nhập vào ô H3:” = D3+E3 +F3+G3” Sau đó điền tự động vào các ô H4 : H12;(rê chuột đến góc dưới bên phải ô H3, chuột trở thanh dấu +, ấn chuột trái và rê đến ô H12) ◄ 1. Nhập thông tin, kết quả thi của học sinh. Kết quả này được lưu dữ liệu lên đĩa với tên “vidu1” Excel sẽ tự động thêm phần mở rộng là “.xls” ◄ 2. Tính tổng số điểm. LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel22 Để xếp loại học sinh theo kết quả thi, chẳng hạn ta đưa ra tiêu chuẩn xếp loại như sau: tương ứng với điểm trung bình cho mỗi môn thì được xếp theo: 0 - 3 - 5 - 6,5 - 8 - 9 -10 (hoặc tính theo tổng số điểm của cả 4 môn: 0 - 12 - 20 - 26 - 32 - 36 - 40) là kém - yếu - trung bình - khá - giỏi - xuất sắc. Ta thực hiện xếp loại bằng cách, tại ô I3 gõ vào biểu thức: “ =IF(H3>=36,"XSac",IF(AND(H3>=32,H3=26,H3<32),"Kha", IF(AND(H3>=20,H3=12,H3<20),"Yeu","Kem"))))) “ và sau đó điền tự động cho các ô I4-I12 (giống như cách làm ở bước 2). Kết quả thu được như hình vẽ ◄ 3. Xác định điểm cao nhất, thấp nhất và điểm trung bình. Ta nhập các công thức vào các ô như sau: H14: = max( h3:h12) H15: = min( h3:h12) H14: = average(h3:h12) ◄ 4.Xếp loại học sinh theo kết quả thi LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel23 Xét điểm lên lớp cho các học sinh, các điểm ưu tiên được tính theo thang cộng điểm (ở cuối bảng trong hình vẽ). Có nghĩa, học sinh là con thương binh được cộng thêm 2 điểm trong việc xét cho lên lớp, tương tự con liệt sỹ được 3 điểm, con em vùng cao và gia đình khó khăn mỗi trường hợp được cộng thêm 1 điểm và đối tượng KC không hưởng điểm ưu tiên, tức là cộng thêm 0 điểm. Việc xét điểm lên lớp được thực hiện như sau: Tại ô J3 gõ vào biểu thức: “ =VLOOKUP(C3,$B$14:$C$18,2,FALSE)+H3 ” sau đó điền tự động cho các ô J4-J12 (giống như cách làm ở bước 2). Kết quả thu được như hình vẽ. Trong biểu thức này ta có sử dụng cách viết địa chỉ tuyệt đối cho vùng điều kiện của hàm vlookup: $B$14:$C$18. Khi sử dụng cách viết này, quá trình điền tự động giá trị địa chỉ của vùng điều kiện sẽ không thay đổi, trong công thức chỉ có C3 trở thành C4, C5, v.v. và H3 trở thành H4, H5, v.v. đáp ứng việc điền công thức của ta. Lưu ý: Nguyên tắc tìm kiếm của hàm vlookup đã nêu ở trên, ví dụ hàm trong ô J3 sẽ tìm được “TB” (giá trị trong ô C3) ở ô B14 và trả về giá trị là 2 (giá trị trong ô C14). Tương tự, các hàm trong J4-J12 cũng thực hiện như vậy. ◄ 5. Xét điểm lên lớp (có tính đến điểm ưu tiên) cho các học sinh LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel24 Thống kê học lực của học sinh (bao nhiêu phần trăm học sinh đạt loại xuất sắc, giỏi, khá, trung bình, yếu và kém). Bài toán này có dữ liệu vào là bảng A12:H12, cột cần tính toán là cột H (thứ 8 từ trái qua phải của bảng dữ liệu). Ở đây ta sử dụng hàm dcount và hàm count để thực hiện tính toán. Để sử dụng hàm dcount ta phải tạo vùng tiêu chuẩn, tương ứng với các trường hợp: kém, yếu, trung bình, khá, giỏi và xuất sắc ta sử dụng vùng tiêu chuẩn: A20:A21, B20:C21, D20:E21, F20:G21,H20:I21 và J20:J21. Quá trình thực hiện công việc gồm: Thứ nhất tại ô J14 gõ vào biểu thức: “ =DCOUNT($A$2:$H$12,8,($A$20:$A$21))/COUNT($H$3:$H$12) “ sau đó điền tự động cho các ô J15-J19 (giống như cách làm ở bước 2). Sau đó sữa lại các vùng tiêu chuẩn tương ứng như trên, ví dụ trong ô J15 ta sữa $A$20:$A$21 thành $B$20:$C$21. Kết quả thu được như hình vẽ. Căn cứ vào kết quả thống kê ta đưa ra các nhận xét. Ví dụ như kết quả ở trên cho ta thấy kết quả học tập của học sinh còn sự chênh lệch về tỷ lệ học sinh khá và yếu 50% so với 30%. Chưa có học sinh giỏi và học sinh xuất sắc. Như vậy mục tiêu chất lượng đào tạo quý sau là phải giảm tỷ lệ học sinh yếu và tăng tỷ lệ học sinh giỏi và xuất sắc. Lưu ý: + Định dạng của các ô J14:J19 là %. Thực hiện bằng cách bôi đen vùng ô này, sau đó chọn menu: Format\Cells...\ trong danh sách Category chọn Percentage, đặt ô trắng Decimal places = 0\OK; + Kết quả thống kê có thể lưu ở bảng tính (sheet) hoặc file khác. Để liên kết đến vùng dữ liệu ta viết theo cú pháp sau: [tên file]tên bảng tính!địa chỉ ô. Ví dụ: ô A1 của sheet1 ở file thu1.xls viết: = [thu2.xls]sheet2!B2 có nghĩa là giá trị trong ô A1 được tham chiếu từ ô B2 của sheet2 ở file thu2. Nếu liên kết giữa các bảng tính cú pháp sẽ là : = tên bảng tính, địa chỉ ô. Ví dụ: ô C1 của sheet1 ở file thu2 viết: = sheet2!B2 tức tạo liên kết giữa ô sheet1\C1 với sheet2\B2 trong cùng một file thu2. ◄ 6. Thống kê học lực của học sinh thông qua kết quả thi LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel25 Ví dụ 2: Tính tiền và theo dõi tình hình khách đến ở Khách sạn. + Nhập thông tin về khách hàng, phòng ở + Tính tiền khách phải trả (theo số ngày ở, loại phòng) cho khách sạn Các bước thực hiện: Số ngày khách lưu lại ở Khách sạn bằng ngày đi trừ ngày đến. Nên trong ô F3 ta gõ vào nội dung: =E3-D3. Sau đó điền tự động cho các ô F4-F12. Chú ý, kiểu dữ liệu trong D3:D12 và E3:E12 là kiểu ngày tháng (Date), còn F3:F12 là kiểu chung (General). Kết quả được chỉ ra trên hình vẽ. Tiền thuê phòng được tính theo theo bảng đơn giá cho từng loại phòng. Tiền thuê bằng số ngày ở nhân với đơn giá một ngày cho loại phòng tương ứng. Nếu số ngày ở bằng 0 (khách đến và đi trong ngày) thì tiền thuê bằng một nữa giá một ngày với loại phòng tương ứng. Để tính tiền thuê phòng, đầu tiên ta viết biểu thức sau vào ô H3: 1. Nhập thông tin về khách hàng: Tên, đến từ đâu, ngày đến, ngày đi và phòng ở ▼ Kết quả này được lưu dữ liệu lên đĩa với tên “vidu2” 2. Tính số ngày khách ở Khách sạn ▼ LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel26 “=IF(F3>0,VLOOKUP(LEFT(G3,1),$G$19:$I$21,2,FALSE)*F3,VLOOKUP(LEFT(G3,1),$ G$19:$I$21,2,FALSE)/2)” sau đó điền tự động cho các ô H4-H12 (giống như cách làm ở bước 2 ví dụ 1). Kết quả được trình bày trên hình vẽ. Trong biểu thức này ta sử dụng hàm left, ví dụ trong ô H3 hàm LEFT(G3,1)=”A” và chữ A sẽ là ký tự cần tìm của hàm vlookup. Ở đây ta lưu ý vùng H3:H12 và H19:H21 có kiểu dữ liệu là kiểu tiền tệ (Accounting) và vùng I19:I21 có kiểu phần trăm (Percentage). Tương tự như tính tiền thuê phòng, ta xác định tiền chi phí phục vụ. Tiền phục vụ bằng tiền thuê phòng nhân với phần trăm chi phí phục vụ ứng với mỗi loại phòng. Giá chi phí phục vụ cho mỗi loại phòng được cho ở bảng đơn giá. Các bước thực hiện bao gồm: thứ nhất ta viết biểu thức sau vào ô I3: “=H3*VLOOKUP(LEFT(G3,1),$G$19:$I$21,3,FALSE)” sau đó điền tự động cho các ô I4-I12. Tiền mà mỗi khách hàng phải trả cho khách sạn gồm tiền phòng và tiền chi phí phục vụ. Để tính khoản tiền này, ta gõ vào ô J3 biểu thức “= H3+I3” sau đó điền tự động cho các ô J4-J12. Kết quả được trình bày trên hình vẽ. Chú ý: Để thuận tiện trong việc trình bày, có cách viết rõ ràng trong biểu thức Excel cho phép định nghĩa một bảng (ví dụ như vùng dữ liệu hay vùng điều kiện) bằng một tên. Ta có thể thay vùng G19:I21 bởi tên VungDieuKien bằng cách chọn vùng G19:I21 sau đó chọn menu: Insert\ Name\Define\gõ tên vùng\OK; 3. Tính tiền thuê phòng cho các khách hàng ▼ LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel27 4. Tính tiền chi phí phục vụ và tổng số tiền phải trả cho mỗi khách hàng ▼ LTVinh Trung tâm CNTT Nghệ an Giáo trình Tin học văn phòng – MS Excel28 III.2. Sử dụng các công cụ 1) Sử dụng công cụ Lọc tự động (Auto Filter: AF) AF cho phép hiển thị tất