Giáo trình Ms Excel

Nội dung text: Giáo trình Ms Excel

1. GIÁO TRèNH MS EXCEL GIÁO TRèNH MS EXCEL I. Đại c−ơng về bảng tính 1.Khái niệm bảng tính : Lập 1 danh sách điểm thi học kỳ của lớp gồm các cột: Họ, tên, Ngày sinh, M1, M2, M3, Tổng điểm, Xếp Loại. Đây là 1 danh sách đ−ợc lập d−ới dạng 1 bảng. ở đó: +Mỗi dòng ( trừ dòng đầu lấy làm tiêu đề cột ) là các thông tin về một học sinh; Mỗi cột là một thông tin về các học sinh. + Dữ liệu độc lập (bắt buộc đ−a vào ) gồm: Họ, Tên, Ngày sinh,M1, M2, M3 +Dữ liệu phụ thuộc (sẽ tính toán nhờ dữ liệu độc lập): Tổng điểm, xếp loại. - Dùng quản lý dữ liệu d−ới dạng bảng - Bảng tính là 1 bảng hình chữ nhật đ−ợc chia thành các ô bởi các l−ới đ−ờng song song với cạnh của bảng hình chữ nhật - Các ô theo vệt ngang là dòng, các dòng đ−ợc đánh số: 1,2,3 65536 - Các ô theo vệt dọc là cột, các cột đ−ợc đánh số: A, B, IV (256 cột) - Ô: Giao của dòng với cột là ô, tên ô :ghép tên cột, dòng; ví dụ: A1, F10, 2. Khởi động EXCEL Trong WIN95, 98, 2000 có dùng một trong 3 cách sau: : Kích chuột lần l−ợt vào Start/Program/Microsoft excel : Kích chuột vào chữ X trong biểu t−ợng nằm góc phải bên trên màn hình. 1 khoavp@gmail.com
2. GIÁO TRèNH MS EXCEL : Kích đúp chuột vào biểu t−ợng trên màn hình nền. 3. Cấu trúc bảng tính excel - Mỗi sổ tay (WorkBook) gồm 1 đến 256 sheet (ngầm định 3 sheet) , - Mỗi bảng tính (sheet ) là 1 trang bảng tính nh− trên, nó gồm 254 cột, 65536 dòng, hơn 16 triệu ô. - Mỗi Workbook sẽ đ−ợc ghi vào 1 tệp; số l−ơng tệp Workbook đ−ợc mở không giới hạn, chỉ phụ thuộc vào bộ nhớ của máy. - Cột (columns) có độ rộng ngầm định 9 kí tự (có thể thay đổi từ 0 - 255 kí tự) - Dòng (Rows) có độ cao ngầm định 12.75 chấm điểm (có thể thay đổi từ 0 - 409) - Ô (Cell) là giao của một cột và một dòng; đ−ợc xác định bởi địa chỉ ghép tên cột, tên dòng. Có 3 loại địa chỉ ô: + Địa chỉ t−ơng đối: A1 + Địa chỉ tuyệt đối: $A$1 + Địa chỉ hỗn hợp: $A1, A$1 4. Di chuyển con trỏ và nhập dữ liệu - Con trỏ ô: là con trỏ làm việc, có màu sẫm hơn, chỉ tồn tại duy nhất 1 con trỏ làm việc - Con trỏ soạn thảo: là 1 vach | nhấp nháy trong ô làm việc, dùng để nhập dữ liệu - Di chuyển con trỏ ô: +Dùng 4 phím mũi tên: di chuyển đến các ô gần + Dùng Page UP, Page Down: chuyển trang màn hình 2 khoavp@gmail.com
3. GIÁO TRèNH MS EXCEL + Dùng CTRL+Home: Về A1 + Dùng chuột: kích chuột vào ô nào ô đó trở thành ô làm việc 5. Khối ô (vùng - Range) , chọn khối ô, viết địa chỉ khối ô - Khối ô: là tập hợp những ô liền kề nhau tạo thành hình chữ nhật, khối ô có thể là 1 ô, 1 cột, 1 dòng, một nhóm ô hoặc toàn bộ bảng tính. - Địa chỉ khối ô đ−ợc xác định bởi toạ độ ô đầu hcn( góc trên bên trái) và toạ độ ô cuối hcn ( góc d−ới bên phải) ; Cách viết A1:B10 - Chọn khối ô : +Chọn 1 khối ô: Đặt chuột ở ô đầu khối, kích rê đến ô cuối khối +Chọn nhiều khối ô đồng thời: Chọn khối ô thứ 1, ấn giữ CTRL đồng thời kích rê chuột để chọn khối thứ 2, ( th−ờng dùng khi vẽ đồ thị). 6. Thay đổi độ rộng cột, độ cao dòng Di trỏ chuột ở đ−ờng biên , hoặc , khi chuột thành ↔ thì kích rê. 7. Ra khỏi bảng tính Kích chuột vào menu File/exit II. Lập bảng tính và cất bảng tính vào đĩa 1. Các kiểu dữ liệu và cách nhập : Bảng tính gồm các cột Họ, Tên, Ngày sinh, M1, M2, M3, Tổng, Xếp loại, ở ví dụ trên dữ liệu của các cột là kiểu văn bản , là kiểu Ngày tháng , là kiểu số , là kiểu công thức. - Đ−ợc gõ các kí tự có trên bàn phím - Tối đa đ−ợc 255 kí tự, ngầm định thấy 9 kí tự, nếu ô bên cạnh không có dữ liệu thì các kí tự “ ” sang và vẫn nhìn thấy. - Dữ liệu sau khi nhập vào ô sẽ sát trái (ngầm định) - Chỉ gõ các số từ 0 - 9 , +, -, . - Dữ liệu sau khi nhập vào ô sẽ sát phải - Gõ ngày tháng hợp lệ, theo qui định: mm/dd/yyyy hoặc dd/mm/yyyy - Dữ liệu sau khi nhập vào ô sẽ sát phải 3 khoavp@gmail.com
4. GIÁO TRèNH MS EXCEL - Bắt đầu là dấu =, sau đến biểu thức tính toán viết theo qui định của excel - Dữ liệu nhập vào ô sẽ sát phải nếu kết quả tính là số, sát trái nếu kết quả là văn bản. - Nội dung ô công thức chứa biểu thức tính toán mà ta gõ vào, nh−ng hình thức hiện ra là kết quả tính toán của biểu thức đó. - Khi các ô dữ liệu trong công thức thay đổi, thì giá trị tính toán trong ô công thức cũng thay đổi theo. 2. Hiệu chỉnh dữ liệu - Nếu dữ liệu ngắn: muốn sửa ô nào đ−a con trỏ vào ô gõ đè lên - Nếu dữ liệu dài: để con trỏ vào ô ấn F2 rồi nhìn và sửa dữ liệu trên thanh công thức, xong ấn Enter. 3. Các phép toán và cách viết biểu thức trong EXCEL - Các phép tính số học: +, -, *, / - Các phép so sánh: =,>,>=, - Các hàm tính toán (sẽ xét cụ thể sau) Ví dụ: SUM(C2,D2), AVERAGE(C2,D2) - Biểu thức: Là sự kết hợp giữa các hằng, địa chỉ ô, các hàm với các phép toán và dấu (, ). Có 2 loại biểu thức: Biểu thức tính toán và biểu thức logic (điều kiện) Ví dụ: =(C2+D2)/2 =D2>9 4. Ghi bảng tính vào tệp, đọc bảng tính từ tệp ra - Ghi vào: File/Save ( đuôi ngầm định của tệp là XLS) - Đọc ra: File/Open III. Một số thao tác trên bảng tính ở trên ta đã xét khái niệm khối ô, cách chọn( đánh dấu) khối ô, viết địa chỉ khối ô, bây giờ ta hãy thực hiện các thao tác với các khối ô đó. 1. Các thao tác sao chép, di chuyển, xoá dữ liệu của khối ô - Đánh dấu khối ô - ấn phím Delete - Đánh dấu khối ô - Kích Edit/Cut - Đặt con trỏ ô ở nơi cần chuyển đến - Kích vào Edit/Paste 4 khoavp@gmail.com
5. GIÁO TRèNH MS EXCEL - Đánh dấu khối ô - Kích Edit/Copy - Đặt con trỏ ô ở nơi cần sao chép đến - Kích vào Edit/Paste 2. Sao chép ô công thức với địa chỉ t−ơng đối, tuyệt đối Ví dụ 1: A B C D E F 1 Họ Tên Ngày sinh L−ơng PC Tổng 2 Lê Hà 1/2/67 390 120 510 3 Đỗ Tú 2/3/56 425 220 645 4 Nguyễn An 3/4/78 520 320 840 Ô F2 sẽ viết công thức tính Tổng: =D2 + E2 Sau đó ta copy ô F2 cho các ô F3, F4, >Công thức ở các ô này sẽ đúng Ví dụ 2: A B C D E F 1 Hệ số 1.40 2 Họ Tên Ngày sinh L−ơng PC Tổng 3 Lê Hà 1/2/67 390 120 510 4 Đỗ Tú 2/3/56 425 220 645 5 Nguyễn An 3/4/78 520 320 840 Ô F3 sẽ viết công thức tính Tổng: =D3*$E$1 + E3 Sau đó ta copy ô F3 cho các ô F4, F5, >Công thức ở các ô này sẽ đúng do ta đã tuyệt đối ô $E$1 Khi sao chép ô công thức: địa chỉ t−ơng đối trong công thức sẽ biến đổi để phù hợp với vị trí t−ơng đối của ô đ−ợc sao chép đến, còn địa chỉ tuyệt đối thì giữ nguyên. Cách 1: Copy bình th−ờng nh− cách ở trên Cách 2: (do th−ờng sao chép 1 ô tới các ô liền kề) + Đặt con trỏ ô vào ô công thức + Di chuột ở góc phải d−ới ô công thức, khi chuột thành + thì kích rê đi các ô liền kề 3. Sao chép đặc biệt (sao chép giá trị của khối dữ liệu là công thức) 5 khoavp@gmail.com
6. GIÁO TRèNH MS EXCEL Ví dụ: ở bảng dữ liệu trên cần sao chép sang 1 bảng mới gồm các cột Họ, Tên, Tổng . Cách làm: +Sao chép cột sang bảng mới: làm sao chép bình th−ờng +Sao chép cột đặt bên cạnh, bằng cách: - Đánh dấu cột - Chọn - Đặt con trỏ ô ở nơi cần đến (bên canh cột Tên) - Chọn 4. Điền dãy số có quy luật - Gõ giá trị đầu vào ô thứ 1 - Gõ giá trị thứ 2 vào ô thứ 2 - Đánh dấu 2 ô - Di chuột ở góc phải d−ới của khối ô, khi chuột thành + thì kích rê đi các ô tiếp theo IV. Một số hàm thông dụng trong tính toán 1. Dạng tổng quát của hàm và cách dùng + Dạng tổng quát: Tên hàm (đối số) Đối số có thể là: hằng số, địa chỉ ô, khối ô, biểu thức, tên 1 hàm khác Ví dụ: Sum(A2,B2) + Cách dùng: hàm th−ờng đ−ợc dùng trong biểu thức tính toán. Hàm đ−ợc chèn vào vị trí trong biểu thức bằng cách: Cách 1: Tại vị trí con trỏ trong biểu thức gõ trực tiếp hàm cần nhập vào Cách 2: Tại vị trí con trỏ trong biểu thức cần nhập hàm, hãy vào lần l−ợt: 2. Các nhóm hàm thông dụng - Max(n1, n2, ni): Cho giá trị lớn nhất trong các ni ở đó các ni có thể là: hằng số, địa chỉ ô, khối ô có chứa số - Min(n1, n2, ni): Cho giá trị nhỏ nhất trong các ni - SUM(n1, n2, ni): Cho tổng các ni - AVERAGE(n1, n2, ni): Cho trung bình cộng của các ni - PRODUCT(n1, n2, ni): Cho tích các ni - COUNT(n1, n2, ni): Đếm các dữ liệu số trong danh sách - COUNTA(n1, n2, ni): Đếm các dữ liệu cả số lẫn kí tự trong danh sách Ví dụ: COUNT(1,ha,4) cho kết quả 2 COUNTA(1,ha,4) cho kết quả 3 - ABS(x) =|x| 6 khoavp@gmail.com
7. GIÁO TRèNH MS EXCEL - INT(x) = phần nguyên của x - MOD(x,y)= số d− của x/y - SQRT(x) = căn bậc hai của x - EXP(x) = e x - TODAY() cho ngày tháng hệ thống - YEAR(dl kiểu ngày) cho năm (4 số) - MONTH(dl kiểu ngày) cho tháng - DAY(dl kiểu ngày) cho ngày - DATE(yyyy,mm,dd): cho giá trị kiểu ngày - WEEKDAY(dl kiểu ngày,kiểu) cho thứ trong tuần; ở đó: kiểu = 1 thì trong tuần sẽ tính thứ Hai:2, thứ Bảy:7, chủ nhật:1 kiểu = 2 thì trong tuần sẽ tính thứ Hai:1, thứ Bảy:6, chủ nhật:7 kiểu = 3 thì trong tuần sẽ tính thứ Hai:0, thứ Bảy:5, chủ nhật:6 Ví dụ: Xem từ ngày sinh ra đến nay là bao nhiêu ngày = TODAY()-DATE(1984,02,25) - UPPER(x): đổi xâu kí tự X thành chữ in hoa - LEFT(X,m): Đ−a ra 1 xâu con từ xâu X gồm m kí tự kể từ bên trái - RIGHT(X,n): Đ−a ra 1 xâu con từ xâu X gồm n kí tự kể từ bên phải - LEN(X): cho độ dài xâu kí tự X a b a ∩b a ∪ b 0 0 0 0 0 1 0 1 1 0 0 1 1 1 1 1 - AND(đk1,đk2, ): cho giao của các đk. : + đtb>9 và Đt>8 thì xếp Giỏi: AND(đtb>9,Đt>8) + Đ−a ra những ng−ời sinh nhật vao ngay hôm nay: AND(day(Today()=Day(d2),Month(today()=Month(d2)) - OR(đk1,đk2, ): cho hợp của các đk Ví dụ: Đ−a ra ng−ời có ĐT hoặc ĐL>=9: OR(ĐT>=9,ĐL>=9) Giá trị 1 nếu BTĐK đúng - IF(BTĐK,giá trị 1, giá trị 2) = Giá trị 2 nếu BTĐK sai . Xếp loại học sinh lên lớp, ở lại dựa vào ĐTB = IF(ĐTB>=5,”lên lớp”, “ở lại” ) 7 khoavp@gmail.com
8. GIÁO TRèNH MS EXCEL Xếp loại học sinh vào 4 loại: Yếu, TB, Khá, Giỏi dựa vào ĐTB =IF(ĐTB =5 hoặc ĐTB>=4 và ĐT>7; còn lại là ở lại =IF(OR(DTB>=5,AND(DTB>=4,DT>7)),”lên lớp”, “ở lại”) V. Định dạng dữ liệu ( trình bày ) bảng tính 1. Thay đổi cấu trúc bảng - Đánh dấu ô (cột, dòng) ở phía d−ới hoặc bên phải vị trí cần chèn - Chọn , xuất hiện hộp thoại: Nếu chọn: + : đẩy khối ô đánh dấu sang phải khi chèn + : đẩy khối ô đánh dấu xuống d−ới khi chèn + : chèn dòng trống phía trên khối ô đánh dấu + : chèn cột trống phía bên trái khối ô đánh dấu b. Xoá ô, cột, dòng - Đánh dấu ô (cột, dòng) cần xoá - Chọn , xuất hiện hộp thoại: Nếu chọn: + : xoá ô và dồn sang trái + : Xoá ô và dồn lên trên + : Xoá dòng đánh dấu + : Xoá cột đánh dấu 2. Định dạng dữ liệu kiểu số, ngày - Chọn (đánh dấu) vùng dữ liệu kiểu số hoặc ngày - Chọn , xuất hiện bảng: 8 khoavp@gmail.com
9. GIÁO TRèNH MS EXCEL - Trong bảng này chọn nút + Trong : Chọn nếu định dạng số Chọn nếu định dang ngày Giả sử chọn , ta có hộp thoại: + Chọn kiểu đ−a ra cho kiểu ngày tháng ở bảng bên phải, nếu không có thì chọn để tự định nghĩa kiểu đ−a ra. +Chọn 3. Định dạng dữ liệu kí tự - Đánh dấu vùng dữ liệu - Chọn , xuất hiện bảng nh− trên, - Trong bảng này chọn nút 9 khoavp@gmail.com
10. GIÁO TRèNH MS EXCEL + Chọn phông, kiểu, cỡ chữ, (giống ở Word) + Kết thúc kích 4. Điều chỉnh dữ liệu trong ô - Đánh dấu vùng dữ liệu - Chọn , xuất hiện bảng nh− trên, - Trong bảng này chọn nút , có hộp thoại tiếp: ở đó, nếu chọn: + : Để căn dữ liệu ngang ô nh− sau: : giữ nguyên dữ liệu nh− khi đ−a vào Căn thẳng mép trái : Căn vào giữa 10 khoavp@gmail.com
11. GIÁO TRèNH MS EXCEL : Căn thẳng mép phải : Căn đều 2 bên Làm đầy ô các kí tự đã có : Đ−a vào giữa khối ô + : Để căn dữ liệu dọc ô : Sát trên; : Sát d−ới Vào giữa : đều trên d−ới + : Chọn hình thức trải dữ liệu + : Cuốn dữ liệu xuống dòng khi đến lề bên phải ô. 5. Tạo đ−ờng kẻ cho khối ô - Đánh dấu khối ô - Chọn xuất hiện hộp thoại trên - Chọn nút , có hộp thoại tiếp: + Chọn đ−ờng viền xung quang hoặc ở giữa các ô ở mục + Chọn đ−ờng viền từng phía trong mục + Chọn kiểu đ−ờng viền trong mục + Chọn VI. Vẽ biểu đồ 1. Các b−ớc vẽ biểu đồ B−ớc 1: - Đánh dấu các vùng dữ liệu cho trục OX, OY - Kích nút lệnh trên thanh công cụ hoặc chọn -> ra bảng 1 - Chọn kiểu biểu đồ ở 11 khoavp@gmail.com
12. GIÁO TRèNH MS EXCEL - Chọn > ra B−ớc 2: - Chọn -> ra B−ớc 3: 12 khoavp@gmail.com
13. GIÁO TRèNH MS EXCEL - Điền tên đồ thị ở ô - Điền tên trục OX ở ô - Điền tên trục OY ở ô - Chọn -> ra B−ớc 4: - Nếu kích chuột vào A s new sheet : Đặt biểu đồ trên trang tính mới, có tên là ( n là 1 số) - Nếu kích chuột vào As object in : Đặt biểu đồ trong trang tính, trang tính đ−ợc xác định bởi 13 khoavp@gmail.com
14. GIÁO TRèNH MS EXCEL - Kích vào -> ra biểu đồ nh− sau: 2. Hiệu chỉnh biểu đồ - Đánh dấu biểu đồ - Kích rê chuột trên biểu đồ để thay đổi vị trí - Kích rê chuột ở một trong các nút đen bao quanh biểu đồ để thay đổi kích cỡ - Đánh dấu biểu đồ - Chọn Font, kiểu, cỡ chữ, màu chữ trên thanh công cụ hoặc trong menu Đánh dấu Biểu đồ/Kích vào / sau đó chọn: - : Thay đổi kiểu biểu đồ - : Thay đổi vùng dữ liệu để vẽ - : Thay đổi các tiêu đề cho biểu đồ và các trục : Thay đổi nơi đặt biểu đồ ••• Chú ý : 14 khoavp@gmail.com
15. GIÁO TRèNH MS EXCEL VII. Cơ sở dữ liệu trên bảng tính 1. Khái niệm cơ sở dữ liệu trong bảng tính Ví dụ: stt Họ và tên 15phút 1 tiết TBKT học kỳ TB mônkì1 1 Tạ Quang C−ờng 9 9 9.0 9 9.0 2 Nguyễn Anh Dũng 7 7 7.0 8 7.3 3 Chu Thuỳ D−ơng 8 6 6.7 8 7.1 4 Nguyễn Hùng Dũng 10 10 10.0 10 10.0 5 Nguyễn Hữu Đại 10 10 10.0 8 9.3 Một bảng dữ liệu trên trang tính đ−ợc tổ chức thành các hàng và các cột. Theo thuật ngữ của CSDL thì mỗi hàng (trừ hàng đầu) sẽ là một bản ghi, mỗi cột là một tr−ờng. Hàng đầu ghi tiêu đề của mỗi cột sẽ là tên mỗi tr−ờng. 2. Sắp xếp dữ liệu - Đánh dấu khối dữ liệu cần SX (gồm dòng tên tr−ờng (tên cột đầu biểu) và các bản ghi (các dòng dữ liệu)) - Chọn Trong hộp thoại trên chọn: +Kích vào + Chọn khoá SX thứ 1 ở hộp SORT BY (là tiêu đề cột cần sx), và chiều SX của nó (A : tăng, D: giảm) + Nếu có khoá SX thứ 2, 3 thì chọn t−ơng tự ở hộp THEN BY + Kết thúc kích vào 3. Tìm kiếm dữ liệu (lọc) 15 khoavp@gmail.com
16. GIÁO TRèNH MS EXCEL : Đánh dấu vùng dữ liệu (gồm dòng tiêu đề cột và các dòng dữ liệu) : Vào DATA/FILTER/AUTO FILTER Bảng tính xuất hiện các ô mũi tên cạnh tiêu đề cột: : Lọc từng điều kiện: Kích chuột vào ô mũi tên bên cạnh tên cột dùng làm điều kiện lọc, đ−a ra bảng chọn: + Nếu chọn CUSTOM, đ−a ra bảng tiếp: + Chọn mức so sánh ở : Equals = is less than or equal to Begins with Bắt đầu bằng chữ cái is greater than > Does not begin with Không bắt đầu bằng is greater than or equal to >= Ends with Kết thúc bằng is less than =9 - Đ−a ra những ng−ời cơ TBKH>=8, 1 tiết>9 - Đ−a ra những ng−ời có TBKT từ 7 đến 9 (dùng AND ) 16 khoavp@gmail.com
17. GIÁO TRèNH MS EXCEL : Tạo vùng điều kiện nh− sau: - Dòng 1: chứa tên các tiêu đề cột giống ở vùng dữ liệu (nên copy sang) - Dòng 2 (hoặc các dòng sau nữa dùng để ghi những điều kiện theo yêu cầu): Nhập các điều kiện ở ngay phía d−ới tên tiêu đề cột ở vùng điều kiện : Đặt con trỏ ô vào vùng dữ liệu và chọn lênh: Xuất hiện hộp thoại: : Lựa chọn yêu cầu thực hiện trên khung : - – : Để hiển thị kết quả lọc ngay trên vùng dữ liệu khai thác. - : Để sao chép kết quả lọc vào vùng sẽ đ−ợc chỉ ra trên bảng tính ở Copy to. : Khai báo phạm vi các vùng trong hộp thoại: - : nhập địa chỉ (hay kích rê phạm vi) vùng dữ liệu khai thác - : Nhập địa chỉ (hoặc kích rê) vùng điều kiện. - : Nhập địa chỉ (hoặc kích rê) vùng chứa kết quả. : Chọn trong hộp thoại. Ví dụ 17 khoavp@gmail.com
18. GIÁO TRèNH MS EXCEL Giả sử có bảng dữ liệu: stt Họ và tên 15phút 1 tiết TBKT 1 Tạ Quang C−ờng 9 9 9.0 2 Nguyễn Anh Dũng 7 7 7.0 3 Chu Thuỳ D−ơng 8 6 6.7 4 Nguyễn Hùng Dũng 10 10 10.0 Vùng điều kiện: stt Họ và tên 15phút 1 tiết TBKT Nguyễn 7 >=9 Vùng kết quả: stt Họ và tên 15phút 1 tiết TBKT 1 Tạ Quang C−ờng 9 9 9.0 2 Nguyễn Anh Dũng 7 7 7.0 4 Nguyễn Hùng Dũng 10 10 10.0 4. Một số hàm dùng với CSDL Qui −ớc: VDL: C: VDK: 18 khoavp@gmail.com
19. GIÁO TRèNH MS EXCEL - DSUM(VDL,C,VDK): Tính tổng các ô ở cột cần xét thoả mãn điều kiện - DAVERAGE(VDL,C,VDK): Tính trung bình cộng các ô trên cột cần xét thoả mãn điều kiện - DCOUNT(VDL,C,VDK): Đếm các ô trên cột cần xét thoả mãn điều kiện (Nếu cột cần xét là số ) - DCOUNTA(VDL,C,VDK): Đếm các ô trên cột cần xét thoả mãn điều kiện (Nếu cột cần xét là văn bản ) - DMAX(VDL,C,VDK): Đ−a ra số lớn nhất trong các ô trên cột cần xét thoả mãn điều kiện - DMIN(VDL,C,VDK): Đ−a ra số nhỏ nhất trong các ô trên cột cần xét thoả mãn điều kiện Ví dụ: 19 khoavp@gmail.com
20. GIÁO TRèNH MS EXCEL Ví dụ: Đếm những ng−ời có chữ cái đầu của Họ và tên là N, vùng điều kiện ta lập nh− sau: đầuhọ =Left(B2,1)=”N” 5. Tìm kiếm bằng VLOOKUP, HLOOKUP VLOOKUP(Giá trị tìm, Vùng cần tìm,Cột cần lấy): Thực hiện việc tìm kiếm một giá trị trong cột đầu tiên của vùng cần tìm và cho kết quả t−ơng ứng trong cột cần lấy (cột chỉ định). Đây là vùng dữ liệu Vùng cần tìm Cột thứ 2 HLOOKUP(Giá trị tìm, Vùng cần tìm, Dòng cần lấy): Thực hiện việc tìm kiếm một giá trị trong dòng đầu tiên của vùng cần tìm và cho kết quả t−ơng ứng trong dòng cần lấy (dòng chỉ định). ở hàm Vlookup : Vùng cần tìm có cột thứ nhất đ−ợc sắp xếp tăng dần (từ trên xuống d−ới) ở hàm Hlookup : Vùng cần tìm có dòng thứ nhất đ−ợc sắp xếp tăng dần (từ trái sang phải). 20 khoavp@gmail.com
21. GIÁO TRèNH MS EXCEL Ví dụ: đây là vùng dữ liệu Vùng cần lấy dòng thứ 2 6. Tổng hợp dữ liệu theo nhóm Ví dụ: Danh sách SV gồm các tr−ờng: Họ tên, Ngày sinh, Lớp, Khối, Điểm TB, Học bổng ->Tính tiền học bổng cho từng khối, từng lớp đặt d−ới từng lớp, khối a. Các b−ớc thực hiện b1. Sắp xếp dữ liệu của bảng với khóa là các tr−ờngcó thể tổng hợp theo nhóm b2. Đặt con trỏ vào vùng CSDL b3. Vào menu Data / Subtotal -> xuất hiện hộp thoại 21 khoavp@gmail.com
22. GIÁO TRèNH MS EXCEL - Chọn cột căn cứ tạo nhóm trong At each change in - Chọn hàm tính tóan trong mục Use Function - Chọn các cột sẽ thêm Subtotal vào trong Add Subtotal to b4. Kích OK b. Ví dụ c. Chú ý - Các nút cho phép chọn các cấp của các nhóm - Nhấn nút :để thu nhỏ nhóm, chỉ hiện dòng tổng hợp của nhóm - Nhấn nút mở rộng để xem các dòng chi tiết trong nhóm 22 khoavp@gmail.com
23. GIÁO TRèNH MS EXCEL Trong hộp thoại Subtotal, các nút chọn có ý nghĩa:
    
    
    Replace current subtotal: bật / tắt việc thay thế dữ liệu trong thao tác Subtotal mới cho dữ liệu của Subtotal cũ
    
    
    Page break between groups: bật / tắt việc tự động chèn ngắt trang vào giữa các nhóm
    
    
    Summary below data: bật / tắt việc tạo dòng tổng hợp đặt ngay d−ới từng nhóm dữ liệu VIII. In bảng tính Ta th−ờng gặp hai tr−ờng hợp: - Phân trang tự động (nếu cả trang tính là 1 danh sách) - Phân trang bằng tay (nếu trang tính gồm nhiều danh sách) 1. Đặt trang in tự động - Đánh dấu toàn danh sách - Vào + Kích chọn nút Page : Để chọn kích cỡ, h−ớng trang in - : in dọc - : in ngang - : chọn cỡ giấy 23 khoavp@gmail.com
24. GIÁO TRèNH MS EXCEL + Kích chọn nút Margin : để đặt lề trang in +Kích chọn nút Header/footer: để tạo tiêu đề đầu, cuối trang in - Mẫu tiêu đề đầu, cuối đ−ợc chọn ở bảng sau: + Kích chọn nút Sheet : để tạo cột/dòng đầu cho mọi trang in: 24 khoavp@gmail.com
25. GIÁO TRèNH MS EXCEL - Trong Print area : gõ địa chỉ tuyệt đối của bảng dữ liệu, Ví dụ: $A$1:$E$10 -Trong Rows To repeat at top : gõ các dòng mà ta muốn in trong mọi trang in theo qui cách: $tên dòng:$tên dòng Ví dụ: $1:$2. Nghĩa là mọi trang in đều in 2 dòng 1, 2 ở đầu bảng. - Trong Colums To repeat at left : gõ các cột mà ta muốn in trong mọi trang in theo qui cách: $ tên cột:$tên cột, Ví dụ: $A:$A. Nghĩa là mọi trang in đều in cột A ở cột đầu .(It dùng nếu số cột không quá nhiều quá khổ giấy) 2. Đặt trang in bằng tay - Đặt con trỏ ô ở góc phải d−ới của trang dự định, - Vào I nsert/Page Break - Đặt con trỏ ô ở góc phải d−ới của trang - Vào I nsert/ Remove Page Break 3. In trang tính 25 khoavp@gmail.com
26. GIÁO TRèNH MS EXCEL Sau khi đã đặt trang in (tự động hoặc bằng tay) và tạo các tiêu đề, đánh số trang (nếu cần) xong ta bật máy in, lắp giấy và thực hiện việc in, bằng cách chọn menu: -> xuất hiện hộp thoại: Trong hộp thoại trên ta chọn: + All: để in ra tất cả các trang + Pages: để in ra trang cụ thể, bằng cách gõ số trang vào (in từ trang), vào (đến trang) + Cuối cùng kích vào BÀI T ẬP EXCEL Bài 1. Để quản lý điểm của 1 lớp, mỗi học sinh có các yếu tố sau: họ và tên, ngày sinh, điểm của các môn nh− Toán, Lý, Hoá, Sinh, Văn, Sử, Địa, Điểm của mỗi môn đ−ợc tính với hệ số nào đó. Từng học kỳ thầy giáo phải tính điểm trung bình cho từng học sinh (giả sử với 1 số môn) nh− sau: Danh sách điểm các môn của lớp 12A Stt Họ và tên NS Toán Lý Hoá Văn Tb 1 Lê Văn An 20/3/84 8 8 9 9 8.5 1. Xác định kiểu dữ liệu của các cột, các thông tin độc lập và thông tin phụ thuộc 26 khoavp@gmail.com
27. GIÁO TRèNH MS EXCEL 2. Lập biểu bảng gồm các cột, trình bày nh− trên và vào dữ liệu (vào các dữ liệu độc lập) cho 10 học sinh. Ghi vào tệp. 3. Tính điểm TB cho mỗi học sinh. Ghi lại Bài 2. Hàng tháng, từng học kỳ thày giáo cần thống kê số l−ợng và tỉ lệ phần trăm học sinh của 1 lớp thuộc các loại d−ới trung bình (DTB), trung bình (TB), khá (KH) và giỏi (G). Tr−ớc hết thày giáo phải kẻ một bảng nh− hình sau và đếm số l−ợng (SL) đối với từng loại để ghi vào: DTB TB KH G Tháng TS SL TL SL TL SL TL SL TL 09 50 4 15 20 11 10 50 3 16 17 14 11 50 5 12 18 15 12 50 6 10 19 15 1. Hãy lập 1 bảng nh− trên, ghi vào tệp 2. Tính tỉ lệ (TL) của mỗi loại trong từng tháng (TL = SL/TS). Ghi lại tệp Bài 3. Một kế toán phải tính l−ơng cho các cán bộ, họ lập một bảng gồm các cột: Stt, Họ và tên, Ngày công (NC), L−ơng chính (LC) tính theo đơn vị ngàn đồng, Tiền th−ởng (TT), trợ cấp (TC), Tổng số (TS). Theo mẫu sau: Stt Họ và tên NC LC TT TC TS 1 Trần An Bình 24 450 2 . . . 1. Lập một bảng nh− trên và vào dữ liệu cho khoảng 5 ng−ời. Ghi vào tệp 2. Tính tiền th−ởng theo số ngày công: TT=NC*5 (5 ngàn đồng) 3. Tính trợ cấp theo mức phần trăm l−ơng chính đ−ợc h−ởng, giả sử là 30% l−ơng chính. 4. Tính tổng số tiền đ−ợc lĩnh của mỗi ng−ời. Ghi lại tệp Bài 4. Một HTX giao ruộng cho xã viên canh tác. Họ lập một bảng để nắm tình hình thu thuế. Bảng này có các cột sau: Số thứ tự, Họ và tên chủ hộ, diện tích đất hạng 1, diện tích đất hạng 2, diện tích đất hạng 3, mức thuế hàng năm cho mỗi hạng đất. Tổng cộng thuế phải đóng hàng năm đối với mỗi hộ và đối với cả HTX trên mỗi hạng đất và toàn bộ. (Mức thu thuế tính trên một đơn vị diện tích chỉ phụ thuộc vào hạng đất) 1. Lập bảng tính để tính thuế cho 5 xã viên, ghi vào tệp 2. Tính tổng cộng thuế phải đóng của mỗi xã viên 3. Tính tổng thuế của mỗi hạng đất và của toàn bộ (để ở hàng cuối) (L−u ý ) 27 khoavp@gmail.com
28. GIÁO TRèNH MS EXCEL Bài 5. Một danh sách lớp gồm các cột: Họ và tên, ngày sinh, điểmToán, điểm Văn, điểm Ngoại ngữ, điểm TB (ĐTB), xếp loại, ghi chú. 1. Lập bảng và vào dữ liệu cho khoảng 5 học sinh (các cột dữ liệu phụ thuộc: ĐTB, xếp loại, ghi chú sẽ tính toán và điền bởi các câu hỏi sau) 2. Tính điểm trung bình cho mỗi học sinh 3. Xếp loại cho mỗi học sinh dựa vào điểm trung bình : Nếu ĐTB =5 xếp loại Đạt 4. Hãy lập công thức tính để cột Ghi chú sẽ đ−ợc tự động ghi dấu * đối với các học sinh có ĐTB cao nhất (có thể có nhiều học sinh cùng có ĐTB cao nhất), còn các học sinh khác ghi dấu - . (Gợi ý: tạo ra một ô phụ ghi ĐTB cao nhất để so sánh) *5. Thêm cột Sinh chủ nhật để ghi những ng−ời sinh vào Chủ nhật. Biết rằng ngày 31/12/1996 là ngày thứ Ba. Sử dụng các hàm thích hợp để tự động viết vào cột này chữ CN đối với những ng−ời sinh đúng vào chủ nhật. (Gợi ý: Do ngày 31/12/1996 là ngày thứ ba nên ngày 5/1/1997 là Chủ nhật, vậy tất cả các ngày chênh với ngày 5/1/1997 một số ngày chia hết cho 7 đều là Chủ nhật (dùng hàm ABS, hàm MOD và hàm IF để viết công thức cần thiết) . Bài 6. Lập một bảng tính gồm những thông tin sau: Họ, Tên, Ngày sinh, số con, L−ơng chính, phụ cấp, Thu nhập, Ghi chú. 1. Vào dữ liệu cho 5 ng−ời 2. Tính thu nhập cho mỗi cán bộ (Thu nhập = L−ơng chính + phụ cấp) 3. Ghi chú sẽ ghi với điều kiện nh− sau: - Những ng−ời sinh từ 1936 trở về tr−ớc thì ghi Về H−u - Những ng−ời sinh sau 1936 thì ghi Làm Việc. 4. Vẽ đồ thị thể hiện sự t−ơng quan mức thu nhập giữa các cán bộ 5. Sắp xếp bảng tính sao cho cột Thu nhập theo chiều giảm dần. 6. Đếm số ng−ời có số con từ 2 trở xuống, kết quả để ở ô A10 7. Chỉ ra mức thu nhập cao nhất trong cơ quan , kết quả để ở ô B10. Sau đó đếm tổng số những ng−ời có mức thu nhập cao nhất, kết quả để ở ô B12. 8. Tính tổng l−ơng của những ng−ời Về h−u để ở ô C14; tổng l−ơng của những ng−ời Làm việc để ở ô D14. 9. Lọc và đ−a ra danh sách gồm những ng−ời có l−ơng chính >=425 và số con <=2. 10. Lọc và đ−a ra 1 danh sách gồm những ng−ời Về h−u, 1 danh sách gồm những ng−ời Làm việc. Bài 7. 1. Lập một bảng theo mẫu sau: Danh sách khách hàng vay tiền tiết kiệm Ngày trả: 12/6/97 % lãi quá hạn/ngày: 0.0002 Tên khách Đối Ngày Ngày Tiền vay Tiền lãi Tiền lãi Tổng cộng 28 khoavp@gmail.com
29. GIÁO TRèNH MS EXCEL t−ợng vay đến hạn quá hạn Thanh Hà 1 12/6/96 12/6/97 2000000 Anh Xuân 2 20/2/94 15/6/96 1500000 Hà An 2 15/3/95 20/6/96 3000000 Anh Tuấn 1 30/6/95 10/4/96 2500000 Xuân Thao 3 12/6/96 12/6/97 1000000 2. Tính cột tiền lãi nh− sau: - Nếu đối t−ợng 1 thì tiền lãi = Tiền vay *(Ngày đến hạn - ngày vay) * 0.001 - Các đối t−ợng khác tiền lãi = Tiền vay *(Ngày đến hạn - ngày vay) * 0.002 3. Tính tiền lãi quá hạn nh− sau: Tiền lãi quá hạn=Tiền vay*(Ngày trả-Ngày đến hạn) * % Lãi quá hạn. 4. Tính tiền tổng cộng = Tiền vay + tiền lãi + tiền lãi quá hạn. 5. Sắp xếp tiền vay theo chiều giảm dần. 6. Đếm số ng−ời thuộc đối t−ợng 1, kết quả để ở ô A14. 7. Tính tổng tiền vay của các đối t−ợng loại 2 hoặc loại 1, kết quả để ở ô B15. 8. Tính tổng tiền Tổng cộng của những ng−ời có ngày vay vào năm 1995, kết quả để ở ô C15 8. Vẽ đồ thị so sánh tiền Tổng cộng và Tiền lãi của từng khách hàng. 9. Đ−a ra số tiền vay cao nhất, ít nhất trong các khách hàng 10. Lọc ra một danh sách gồm các đối t−ợng loại 1 có ngày vay sau 1/1/1996. Bài 10. 1. Lập một bảng tính và trình bày nh− mẫu sau: 2) Xác định điểm −u tiên cho mỗi ng−ời nh− sau: XLHTập Điểm Có thể dùng hàm IF hoặc UT VLOOOKUP để xác định điểm −u tiên cho mỗi29 ng−ời khoavp@gmail.com
30. GIÁO TRèNH MS EXCEL A 1 B 0.5 C 0 3) Tính Điểm TB nh− sau: Điểm TB=(Toán*2+Văn*2+Lý+Sinh+Ngoại Ngữ+ĐiểmUT)/7 4) Kết quả xếp nh− sau: Nếu ĐiểmTB =8 xếp loại Giỏi. 5) Sắp xếp danh sách sao cho Điểm TB theo chiều giảm dần 6) Điền số thứ tự tự động vào mục STT 7) Đếm số ng−ời ở lớp 12A2, kết quả để ở ô B12 8) Đ−a ra 1 dánh sách gồm những ng−ời xếp loại học tập là A và có Điểm TB>=7 9) Vẽ biểu đồ so sánh điểm TB của mỗi học sinh Bài 11. Danh sách SV đ−ợc cấp học bổng năm học 200 –200 Ngày hiện tại: (cho ngày cụ thể hôm nay) Stt Họ và tên Ngày Mã Môn Môn Điểm Tiền Ghi sinh UT 1 2 TB HB chú Nguyễn Lâm A1 8 9 Lê Tú Nam A2 7 9 Đoàn ThuThuỷ B2 8 7 Lâm Tú Trinh B1 7 9 Phạm Thị Hoa C2 9 8 Lã Bích Ngọc C1 8 8 Hoàng Tiến A2 9 7 Lê Thị Lan B1 6 5 1. Nhập dữ liệu theo bảng trên (tự cho ngày tháng năm sinh cho phù hợp độ tuổi hiện nay đang học Đại học) 2. Điền số thứ tự cho cột STT 3. Tính điểm TB nh− sau: Điểm TB= (Môn 1 + Môn 2 )+Điểm UT ở đó Điểm UT cho mỗi ng−ời đ−ợc tính nh− sau: Nếu Mã UT=”A” thì Điểm UT là 1; Mã UT = “B” thì Điểm UT=0.5; Mã UT=”C” thì Điểm UT=0. 4. Tiền HB (tiền học bổng) tính nh− sau: Nếu Điểm TB >=9 và tuổi Điểm TB>=8 và 22>=tuổi >20 thì học bổng là 300000 Còn lại không cấp học bổng. L−u ý: Tuổi = Năm hiện tại – Năm sinh 5. Tính tổng học bổng của các học sinh có Mã UT với các chữ cái đầu là ”A”; ”B”;”C” để kết quả vào 3 ô: A12, B12, C12. 30 khoavp@gmail.com
31. GIÁO TRèNH MS EXCEL 6. Đếm xem có bao nhiêu học sinh có điểm TB từ 9 trở lên và có môn 1>=8 7. Trích ra 1 vùng khác trên bảng tính: Những học sinh có tiền học bổng =500000 và môn 1>=9. 8. Điền vào cột Ghi chú: Những ai sinh nhật vào ngày hôm nay thì ghi “SN”, nếu không ghi “-“. (Sau đó thử 1 vài ng−ời trong danh sách có sinh nhật vào hôm nay) 9. Sắp xếp danh sách trên sao cho cột Tiền HB giảm dần nếu Tiền HB trùng nhau thì sắp xếp Điểm TB tăng dần. 10. Vẽ đồ thị so sánh điểm 2 môn của mỗi học sinh. 31 khoavp@gmail.com