Tin học đại cương

Chương 4
Microsoft Excel 2003
Lê Thị Thu Nga
Khoa Tin Học
Trường Đại Học Quy Nhơn
Bài 1.Giới thiệu về Excel
Là một chương trình trong bộ Microsoft Office do hãng Microsoft sản xuất.
Là một chương trình bảng tính quản lý dữ liệu được tổ chức dưới dạng bảng, giúp người sử dụng quản lý, tính toán, tổng hợp dữ liệu trên bảng thuận tiện, nhanh chóng và dễ dàng, được dùng rất thông dụng hiện nay.
Một số phiên bản:
Excel 97
Excel 2000
Excel 2002 (XP)
Excel 2003
Khởi động và thoát khỏi Excel
Khởi động:
Cách 1: Double click vào biểu tượng trên desktop
Cách 2: Menu Start  Programs  Microsoft office  Microsoft office Excel 2003


Khởi động và thoát khỏi Excel
Thoát:
Cách 1: Click vào nút Close trên thanh tiêu đề
Cách 2: Từ menu File, chọn Exit
Cách 3: Nhấn tổ hợp phím Alt+F4.
Màn hình làm việc của Excel
Title bar
Menu bar
Formula
bar
Standard toolbar
Status bar
Formating toolbar
Sheet Tabs
Cell:
Là sự giao nhau của cột và hàng, để phân biệt giữa các ô người ta dùng địa chỉ. Ví dụ: A5 (cột A hàng 5).
Ô hiện hành là ô đang nhập và chỉnh sửa dữ liệu trên nó.
WorkSheet (trang tính): được tạo thành từ:
256 cột (column): Ký hiệu A,B,....Z,AA,AB....AZ, BA......,IV
65536 dòng (Row): Ký hiệu 1,2....65536
Để phân biệt giữa các Sheet người ta đặt tên cho Sheet là Sheet1, Sheet2… Ta có thể đặt tên lại cho Sheet.
Workbook (bảng tính):
Tập hợp của nhiều Sheet tạo thành 1 workbook.
1 workbook được lưu trên 1 file có phần mở rộng *.xls
Bài 2.Các khái niệm cơ bản
Các thao tác cơ bản trên WorkBook
Các thao tác cơ bản trên WorkSheet
Đặc trưng của Excel
Các loại địa chỉ
Các loại địa chỉ
Các loại địa chỉ
Bài 3.Các thao tác soạn thảo
Di chuyển trong bảng tính
Đánh dấu khối
Sửa, xóa dữ liệu
Phục hồi trạng thái trước đó
Chèn dòng, cột
Chèn ô
Xóa dòng, cột
Xóa ô
Thay đổi độ rộng dòng, cột
Ẩn/ hiện dòng, cột
Cố định dòng/cột tiêu đề
Di chuyển
Sao chép
Sao chép đặc biệt
Di chuyển trong bảng tính
Đánh dấu khối
Đánh dấu khối
Đánh dấu khối
Sửa, xóa dữ liệu
Sửa dữ liệu:
Di chuyển con trỏ tới ô cần sửa.
Nhấn phím F2 hoặc kích đúp chuột trái.
Sửa nội dung.
Nhấn Enter.


Xóa dữ liệu:
Chọn miền cần xoá
Nhấn phím Delete để xoá dữ liệu
(các ô không bị xoá).

Phục hồi lại trạng thái trước đó
Chèn dòng, cột
Chèn ô
Xóa dòng, cột
Xóa ô
Thay đổi độ rộng dòng, cột
Ẩn/ hiện dòng, cột
Cố định dòng/cột tiêu đề
Dòng/cột tiêu đề thường sẽ hiển thị cố định khi sử dụng thanh cuộn
Cố định:
Nhắp chuột vào ô đầu tiên của vùng dữ liệu muốn thay đổi theo thanh cuộn
Chọn thực đơn Window  Freeze Panes
Gỡ bỏ việc cố định tiêu đề
Chọn thực đơn Window  UnFreeze Panes
Di chuyển
Chọn miền cần chuyển
Nhấn Ctrl+X hoặc chọn menu EditCut.
Miền sẽ được bao bởi HCN nhấp nháy.
Di chuyển tới nơi cần chép tới.
Nhấn Ctrl+V hoặc chọn menu EditPaste.
Sao chép
Chọn miền cần sao chép.
Nhấn Ctrl+C hoặc chọn menu EditCopy.
Miền được bao bởi HCN nhấp nháy.
Di chuyển tới nơi cần chép tới.
Nhấn Ctrl+V hoặc chọn menu EditPaste.
Cách khác:
Nhấn Ctrl, di con trỏ tới biên vùng đến khi xuất hiện dấu + ở cạnh chuột.
Rê chuột để kéo vùng cần sao chép sang vị trí khác
Sao chép đặc biệt
Chọn phạm vi dữ liệu cần sao chép
Ấn nút Copy trên thanh công cụ (hoặc nhấn Ctrl+C hoặc Từ menu Edit, chọn Copy)
Chọn phạm vi đích
Từ menu Edit hoặc menu nhanh, chọn Paste Special
xuất hiện hộp thoại Paste Spectial
Sao chép đặc biệt
Khung Paste: kiểu sao chép
Khung Operation: phép toán thao tác tương ứng trên dữ liệu nguồn với dữ liệu đích
Transpose: sao chép và chuyển vị cột thành hàng
Skip Blanks: không sao chép các ô trống trong phạm vi nguồn
Nút Paste Link: để liên kết dữ liệu nguồn với dữ liệu đích
Nhắc lại:
Sử dụng Regional Setting trong Control Panel
Bài 4.Các kiểu dữ liệu
Kiểu Text
Kiểu Numeric
Kiểu Date & Time
Kiểu Logic
Kiểu công thức
Kiểu Text
Kiểu Text
Kiểu Text
Kiểu Numeric
Kiểu Numeric
Kiểu Numeric
Kiểu Date & Time
Kiểu Date & Time
Kiểu Date & Time
Kiểu Logic
Kiểu Logic
Kiểu công thức
Kiểu công thức
Bài 5.Định dạng dữ liệu
Chọn vùng dữ liệu cần định dạng
Từ menu Format, chọn Cells (hoặc ấn phím phải chuột, chọn Format Cells)
Xuất hiện hộp thoại Format Cells:
Định dạng số
Tab Number:



Khung Category:
chọn nhóm dạng thức cần định dạng. Tuỳ vào mỗi nhóm đã chọn mà có các dạng lựa chọn dịnh dạng khác nhau.

Định dạng số
Ta có thể định dạng số bằng cách sử dụng các nút lệnh:
Current Style: định dạng tiền tệ
Percent Style: định dạng phần trăm
Comma Style: định dạng dấu phân cách ngàn triệu
Increase Decimal: thêm vào mô t vị trí dành cho số lẻ
Decrease Decimal: bớt đi một vị trí dành cho số lẻ
Định dạng lề
Tab Alignment:
Horizonal: kiểu sắp xếp dữ liệu theo chiều ngang
Vertical: kiếu sắp xếp dữ liệu theo chiều dọc
Orientation: hướng trình bày dữ liệu.
Định dạng lề
Tab Alignment:
Horizonal:kiếu sắp xếp dữ liệu theo chiều ngang
General: dạng chung (chuỗi: sxếp về bên trái, số: sxếp về bên phải)
Left: thẳng bên trái ô (tương đương nút Align Left )
Right: thẳng bên phải ô (tương đương nút Align Right )
Center: vào giữa ô (tương đương nút Center )
Fill: lấp đầy ô
Justify: thẳng cả 2 bên trái phải ô (tương đương nút )
Center Across Selection: sxếp dliệu vào giữa các ô đã chọn (tđương nút )
Vertical: kiếu sắp xếp dữ liệu theo chiều dọc
Top: dữ liệu dồn lên trên các ô
Center: dữ liệu dồn vào giữa các ô
Bottom: dữ liệu dồn xuống dưới các ô
Justify: dữ liệu trải đều cả trên lẫn dưới ô
Orientation: hướng trình bày dữ liệu.
Text Control:
Wrap text: cuốn văn bản xuống dòng khi đụng lề
Shrink to fit:canh dữ liệu vừa khít ô
Merge cell: gộp nhiều ô thành 1 ô
Định dạng Font
Tab Font:
Định dạng viền
Tab Border:
Định dạng nền
Tab Patterns:
Bài 6. Sử dụng hàm
Cách nhập hàm
Nhập hàm từ bàn phím:
Đưa con nháy đến vị trí cần nhập
Nhập dấu “=“, sau đó nhập tên hàm và giá trị các tham số
Sử dụng hộp thoại Paste Function:
Chọn ô cần nhập hoặc đưa
con nháy đến vị trí cần nhập
Từ menu Insert,
chọn Function
(hoặc nhấn Shift+F3,
hoặc nhắp vào nút
Paste Funtion )
xhiện hthoại Paste Funtion:
Chú ý:
Các nhóm hàm thông dụng
Nhóm hàm Logic
Nhóm hàm Logic
Nhóm hàm Logic
Nhóm hàm Logic
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Khi thứ thự xếp bằng 1
Khi thứ thự xếp bằng 0
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
XII.Hàm COUNT( )
Nhóm hàm toán học, thống kê
Nhóm hàm toán học, thống kê
Ví dụ:





SUMIF(B2:B5,“>160”,C2:C5) cho kết quả bằng 63 (=14+21+28)
Nhóm hàm toán học, thống kê
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm xử lý chuỗi
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm thời gian
Nhóm hàm Tìm kiếm và tham chiếu
Nhóm hàm Tìm kiếm và tham chiếu
Nhóm hàm Tìm kiếm và tham chiếu
Nhóm hàm Tìm kiếm và tham chiếu
Nhóm hàm Tìm kiếm và tham chiếu
Nhóm hàm Tìm kiếm và tham chiếu
Nhóm hàm Tìm kiếm và tham chiếu
Nhóm hàm Tìm kiếm và tham chiếu
Bài 7. Khai thác cơ sở dữ liệu
Chức năng Sort
Chọn vùng cần sắp xếp.
Từ menu Data, Chọn Sort -> xuất hiện hộp thoại Sort




Dùng nút lệnh:

Nút Ascending sxếp tăng

Nút Decending sxếp giảm
Chức năng Filter
Chức năng Filter
Chức năng Filter
Chức năng Filter
Tạo bảng điều kiện bằng cách sao chép dòng tên trường đến một vị trí trống và nhập các điều kiện lọc, theo quy tắc:
Điều kiện lọc liên quan đến trường nào thì nhập dưới tên trường đó trong vùng điều kiện.
Nếu điều kiện lọc là một hằng thì nhập tên hằng đó dưới tên trường tương ứng trong vùng điều kiện. Nếu điều kiện lọc là một quan hệ với một hằng thì nhập dấu phép toán quan hệ trước hằng đó vào dưới tên trường tương ứng trong miền điều kiện.
Nếu điều kiện lọc phải thoả mãn đồng thời nhiều điều kiện liên quan đến các trường khác nhau thì nhập các điều kiện đó dưới các trường tương ứng trong vùng điều kiện trên cùng một dòng. Nếu điều kiện lọc phải thoả mãn điều kiện này hoặc điều kiện khác thì nhập các điều kiện đó vào dưới các tên trường tương ứng trong vùng điều kiện trên các dòng khác nhau.
Nếu điều kiện lọc phải thỏa mãn đồng thời nhiều điều kiện có liên quan đến một trường, phải sao chép tên trường đó trên một dòng và nhập các điều kiện vào bên dưới tên trường trong vùng điều kiện.
Chức năng Filter
Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 =400
Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 >150
Miền đ/k để lọc các bản ghi có số SP bán ra trong tháng 1 150Miền đ/k để lọc các bghi có số SP bán ra trong tháng 1 >150 hoặc trong tháng 2 200
Chức năng Subtotal
Sắp xếp lại vùng dữ liệu theo trường dùng để phân chia nhóm cần tính.
Chọn vùng dữ liệu vừa sắp xếp.
Từ menu Data, chọn Subtotals
Xuất hiện hộp thoại Subtotals:

Để xoá tất cả các Subtotals đã có,
chọn nút Remove All
Chức năng Consolidate
Chức năng Consolidate
Sao chép các tiêu đề hàng, cột cần thiết đến nơi chứa kết quả. Nếu muốn liên kết kết quả với dữ liệu nguồn thì vùng kết quả phải nằm ở sheet khác.
Chọn vùng chứa kết quả tổng hợp số liệu.
Từ menu Data, chọn Consolidate  xuất hiện hộp thoại Consolidate:
Chức năng Consolidate
Hộp thoại Consolidate:
Function: chọn hàm dùng để tổng hợp dữ liệu.
Reference: nhập địa chỉ vùng dữ liệu nguồn (nếu vùng chứa dữ liệu nguồn nằm trên một bảng tính chưa mở thì nhắp vào nút Browse, sau đó chọn tên bảng tính trong hộp thoại Browse để mở)chọn nút Add để tiếp tục nhập các vùng dữ liệu còn lại.
Use labels in, chọn:
Top row: tổng hợp dữ liệu dựa vào dòng đâu tiên
Left column: tổng hợp dữ liệu dựa vào cột bên trái
(Nếu không chọn hai mục này thì Excel sẽ tổng hợp dựa trên vị trí tương ứng)

Create links to source data: Nếu muốn liên kết kết quả với dữ liệu nguồn. Khi đó vùng kết quả phải nằm ở sheet khác với vùng chứa dữ liệu nguồn.
Nếu muốn xoá bớt vùng số liệu tham gia tổng hợp, trên hộp thoại Consolidate  chọn địa chỉ của vùng dữ liệu muốn loại bỏ trong khung All reference chọn nút Delete
Ví dụ: Một nhà máy muốn lập kế hoạch vận chuyển sản phẩm từ 4 xưởng sản xuất A1, A2, A3 và A4 đến 3 địa điểm tiêu thụ B1, B2, và B3. Biết khả năng sản xuất của các xưởng, nhu cầu các địa điểm tiêu thụ và đơn giá vận chuyển một tấn sản phẩm (tính bằng 1000 đồng) từ các xưởng đến các địa điểm tiêu thụ cho ở bảng sau:
Hãy lập phương án vận chuyển sao cho tổng chi phí vận chuyển là nhỏ nhất
Chức năng Solver
Nhập địa chỉ ô chứa hàm mục tiêu vào khung này
Chọn kiểu bài toán tại Equal To
Với hàm mục tiêu cực đại, chọn Max
Với hàm mục tiêu cực tiểu, chọn Min
Định các ô ẩn số vào khung này
Định các ràng buộc của bài toán tại khung này
Chọn nút Delete để xóa một ràng buộc ra khỏi hệ ràng buộc
a. Nhập dữ liệu, ban hành công thức để xây dựng bài toán trên bảng tính Excel
b. Định nghĩa bài toán trong hộp thoại Solver Parameters:
Trong menu Tools, chọn Solver, hộp thoại Solver Parameters xuất hiện


Chức năng Solver
Sau khi giải xong bài toán, Excel xuất hiện hộp thoại Solver Results
Chọn một hoặc một số kiểu báo biểu bên dưới khung Reports
Để giữ lại kết quả, chọn mục này và� chọn nút OK
Để trả� lại giá trị nguyên thủy, chọn mục này
Chọn nút OK
Chức năng Solver
Hàm thao tác trên CSDL
DCOUNT(Database, Field, Criteria)
Đếm số ô chứa giá trị số của một trường được chọn đối với các bản ghi thỏa mãn điều kiện tìm kiếm.
Trong đó:
-Database: địa chỉ hoặc tên CSDL
-Field: Trường sẽ được đếm (địa chỉ ô chứa tên trường)
-Criteria: địa chỉ hoặc tên vùng điều kiện
Hàm thao tác trên CSDL
DCOUNTA(Database, Field, Criteria)
Đếm số ô có nghĩa (không trống) của một trường được chọn đối với các bản ghi thỏa mãn điều kiện tìm kiếm.
Trong đó:
-Database: địa chỉ hoặc tên CSDL
-Field: Trường sẽ được đếm (địa chỉ ô chứa tên trường)
-Criteria: địa chỉ hoặc tên vùng điều kiện
Hàm thao tác trên CSDL
DAVERAGE(Database, Field, Criteria)
Tính trung bình giá trị của một trường được chọn đối với các bản ghi thỏa mãn điều kiện tìm kiếm.
Trong đó:
-Database: địa chỉ hoặc tên CSDL
-Field: Trường mà giá trị của nó sẽ được tính trung bình (địa chỉ ô chứa tên trường)
-Criteria: địa chỉ hoặc tên vùng điều kiện
Hàm thao tác trên CSDL
DSUM(Database, Field, Criteria)
Trả về tổng giá trị của một trường được chọn đối với các bản ghi thỏa mãn điều kiện tìm kiếm.
Trong đó:
-Database: địa chỉ hoặc tên CSDL
-Field: Trường cần tính tổng (địa chỉ ô chứa tên trường)
-Criteria: địa chỉ hoặc tên vùng điều kiện
Hàm thao tác trên CSDL
DMAX(Database, Field, Criteria)
Trả về giá trị lớn nhất của một trường được chọn đối với các bản ghi thỏa mãn điều kiện tìm kiếm.
Trong đó:
-Database: địa chỉ hoặc tên CSDL
-Field: Trường tìm giá trị lớn nhất (địa chỉ ô chứa tên trường)
-Criteria: địa chỉ hoặc tên vùng điều kiện
Hàm thao tác trên CSDL
DMIN(Database, Field, Criteria)
Trả về giá trị nhỏ nhất của một trường được chọn đối với các bản ghi thỏa mãn điều kiện tìm kiếm.
Trong đó:
-Database: địa chỉ hoặc tên CSDL
-Field: Trường tìm giá trị nhỏ nhất (địa chỉ ô chứa tên trường)
-Criteria: địa chỉ hoặc tên vùng điều kiện
Hàm thao tác trên CSDL
DGET(Database, Field, Criteria)
Trả về giá trị của một trường đối với một bản ghi thỏa mãn điều kiện tìm kiếm.
Trong đó:
-Database: địa chỉ hoặc tên CSDL
-Field: Trường của bản ghi sẽ được lấy giá trị (địa chỉ ô chứa tên trường)
-Criteria: địa chỉ hoặc tên vùng điều kiện
Chú ý: Lệnh DGET thường chỉ dùng cho việc tìm kiếm duy nhất, tức là chỉ có duy nhất một bản ghi thỏa mãn điều kiện. Nếu nhiều hơn mộ bản ghi thỏa điều kiện thì hàm sẽ đưa thông báo lỗi #NUM, nếu không có bản ghi nào thỏa điều kiện thì hàm đưa thông báo lỗi #VALUE.
Bài 8. Đồ thị
Tạo đồ thị
Tạo đồ thị
Tạo đồ thị
Tạo đồ thị
Thẻ Title
Tạo đồ thị
Tạo đồ thị
Tạo đồ thị
Tạo đồ thị
Thẻ Data Labels
Tạo đồ thị
Tạo đồ thị
Hiệu chỉnh đồ thị
Hiệu chỉnh đồ thị
Hiệu chỉnh đồ thị
Hiệu chỉnh đồ thị