Các hàm Excel

Phần IV Excel 2003
4. 1 Khái quát về Excel
Là phần mềm công cụ trong bộ Microsoft office
Thích hợp với công tác kế toán, văn phòng
Tính tương thích cao, làm cơ sở trung gian cho nhiều cơ sở dữ liệu
Tổ chức dữ liệu dưới dạng bảng biểu như bảng lương, bảng kế toán, bảng thanh toán, bảng thống kê, dự toán.
Nhiều công cụ toán học mạnh xử lý bảng biểu thống kê, tính toán tài chính, giải các bài toán logic và tối ưu
Có nhiều công cụ mạnh như vẽ biểu đồ, hình minh họa
Có nhiều công cụ hỗ trợ tự động hóa nhiều quá trình
Có thể lập trình trong Excel
Khởi động Excel:
- Start  Programs  Microsoft office  Microsoft Excel
- Nháy vào biểu tượng Excel trong Microsoft office shortcut bar
4.2 Màn hình làm việc của Excel
4.2 Màn hình làm việc của Excel
Namebox
Formula
Title
Task Pane
Menu
column
Row
Forrmatting
Scroll bar
Border
Trang tính
Status bar
Drawing
Standard
4.2 Màn hình làm việc của Excel
Title bar: tiêu đề của bảng tính.
Menu bar
Thanh Standard
Thanh Formatting
Thanh Formula
Thanh Drawing
Workbook: cửa sổ chứa nội dung tệp. Tên mặc định là Book#. Phần mở rộng định sẵn là XLS.
Row border, Row column
Row: hàng, đánh số thứ tự. Max – 65536 dòng
Column: cột, đánh số A, B, ..,Z, AA, AB,.., IV. Max 256 cột. trị mặc định của cột: 9 ký tự.
Ô (Cell): giao của dòng và cột. địa chỉ của ô: vd C6. Độ cao (Height) mặc định:12.75 Pt.
Sheet: một trang tính. Workbook có mặc định 3 sheet, tối đa 255 Sheet. Điều chỉnh số Sheet trong Workbook bằng lệnh Tools/Options/General, chọn số lượng Sheet.
Có thể điều khiển kiểu, cỡ Font ngầm định
Scroll bar: thanh cuộn dọc và ngang
Status bar: tình trạng hiện hành của hệ thống.
4.2 Màn hình làm việc của Excel
4.3 Tác nghiệp với dữ liệu
Nhập dữ liệu
Phần I Phần II Chú thích thêm
Làm việc với hàng, cột
Điền dãy số tự động
Sao chép, xóa, Cut dữ liệu trong vùng
Nhập, sao chép công thức
Sao chép với Paste special
Di chuyển vùng dữ liệu
Hủy bỏ thao tác vừa thực hiện
Điền dãy số tự động
Là điền một dãy số vào một hàng ô hay một cột ô.
Gõ giá trị bắt đầu vào ô đầu. Dùng lệnh Edit/Fill/Series
Chọn dòng (cột)
Chọn Type: Linear- cộng trị bước nhảy, Growth- nhân trị bước nhảy, Date – theo dạng ngày, AutoFill – điền tự động
Step Value: Trị số bước nhảy, Stop Value: Trị kết thúc
Về AutoFill
Demo
4.4 Các kiểu dữ liệu
Kiểu số: chứa các số từ 0 đến 9 và dấu +, -
Kiểu chuỗi (Text): Ký tự đầu tiên từ A đến Z và một số ký tự canh biên. Ký tự số trở thành ký tự chuỗi khi thêm vào trước nó dấu ‘
Kiểu công thức: Ký tự đầu là =
Trong ô hiển thị không phải công thức mà là giá trị của công thức.
Các toán tử sử dụng trong công thức: cộng +, trừ -, nhân *, chia /, lũy thừa ^, phần trăm %, nối chuỗi &, so sánh: bằng =, không bằng <>,..
Địa chỉ tương đối: ví dụ: A1, B2, C4, D3. Khi sao chép địa chỉ tham chiếu sẽ thay đổi theo phương, chiều và khoảng cách. vd: C1 chứa công thức = A1*B1. Khi sao chép sang ô C2 thì C2 sẽ = A2*B2
Địa chỉ tuyệt đối. Có dạng $$. Khi sao chép nội dung trong ô sẽ giữ nguyên
Địa chỉ hỗn hợp: Khi 1 dấu $ không có trước hàng(cột). Tại vùng đích, địa chỉ tham chiếu sẽ bị thay đổi tương ứng theo hàng (cột).
Di chuyển dữ liệu vùng: chọn vùng Edit/Cut 
Chọn ô trái vùng đích  Edit  Paste (hoặc Ctrl+V)
Dán giá trị
Dùng để sao chép một dãy công thức và chỉ dán những giá trị tại vùng đích
Các bước
Chọn dãy ô sao chép
Di chuyển đến vị trí mới
Nháy vào Edit  Paste special
Trong hộp thoại Paste Special chọn Value
Chọn value
2.5 Các thao tác trên tệp
Lưu trữ Workbook với lệnh Save và Save As (Giống như trong Word) | biểu tượng Save.
Mở tệp Workbook có sẵn: Lệnh File/Open hoặc biểu tượng Open. Truy cập vào ổ đĩa  thư mục
Tạo một Workbook mới: Lệnh File New | biểu tượng New trên thanh công cụ
Đóng tệp Workbook: lệnh File/Close.
Bảo vệ bảng tính
Bảo vệ toàn bộ tệp: FileSave As Tools  General Options nhập Password và Confirm
Bảo vệ Sheet: Tools  Protection  Protect Sheet
Cơ sở dữ liệu trên bảng tính
F1:F2 vùng Criteria
A1: D8 Vùng Database
A10: D13 vùng Extract
Tìm kiếm bản ghi thỏa mãn điều kiện
Di chuyển con trỏ vào vùng Database
Thực hiện lệnh Data/ Filter/Avanced Filter
Chọn Filter the list, in-Place
Điền Đ/c vùng Database, vùng Criteria vào các hộp List Range và Criteria Range
Đánh dấu các ô, đ/c sẽ tự động điền vào các hộp thoại trên
KQ: chỉ hiển thị các ô thỏa mãn điều kiện.
Muốn hiện lại toàn bộ: Data/Filter/Show All
Rút các bản ghi thỏa mãn Đ/k sang vùng khác
Thực hiện giống như tìm kiếm bản ghi thỏa mã điều kiện. khác biệt ở chỗ trong hộp thoại Advanced Filter
Chọn Copy to Another location
Vào Đ/c của vùng đích trong hộp Copy to
Lựa chọn Unique Records only chỉ hiển thị 1 bản ghi giống nhau.
Xóa các bản ghi thỏa mãn điều kiện
Chỉ cần vùng dữ liệu và vùng Criteria
Thực hiện các bước như tìm kiếm đối với tiêu chuẩn cần xóa.
Nhận được các bản ghi cần xóa.
Edit/Delete/Entire Row để xóa các dòng trên
Data/Filter/Show all để hiện lại những bản ghi không cần xóa.
Các dạng vùng tiêu chuẩn
Tiêu chuẩn số hay chuỗi: như vùng F1: F2 ở vd trên
tiêu chuẩn so sánh: chứa toán tử và giá trị so sánh
Tiêu chuẩn công thức:
- Tiêu đề ô tiêu chuẩn phải  tên trong database
- Trong ô điều kiện lấy đ/c ô đầu tiên để so sánh
Liên kết tiêu chuẩn: khác cột – toán tử And
khác dòng – toán tử Or
= Left(B2,1)=“M”
Lọc nhờ AutoFilter
AutoFilter cho phép tự động lọc theo một tiêu chuẩn nhất định có sẵn trong Database.
Dùng Data/Filter /AutoFilter. Xuất hiện trong mọi cột. nháy vào đó trong cột DON VI, xuất hiện một bảng chọn như ở cột E. ta có thể chọn các đơn vị, All, Top 10 hoặc Custom
Tính năng lập dàn trong Excel
Excel cho phép bạn che dấu hay hiển thị chi tiết một bảng tính
Chọn dòng (cột ) cần lập dàn
Vào Data  Group and outline  Group
Chọn Rows
Nháy vào dấu để dấu hoặc mở rộng dàn.
3. Một số hàm toán học trong Excel
Excel có một số hàm mẫu dùng tiện lợi và đơn giản VD: = A3+A4+A5 thay bằng Sum(A3:A5)
Các hàm số học:
- ABS(N): cho giá trị tuyệt đối của biểu thức
- SQRT(N): căn bậc 2 của số N
- INT(N): cho phần nguyên của biểu thức
- PI(): cho giá trị của số Pi
- Các hàm: MODE, ROUND, AVERAGE, MAX, MIN, COUNTA(số ô chứa trị trong DS)…
3. Một số hàm toán học trong Excel
Các hàm ngày tháng:
DAY, MONTH, YEAR
Các hàm Logic:
IF (biểu thức Logic, biểu thức 1, biểu thức 2)
AND (ĐK1, ĐK2,…)
OR (ĐK1, ĐK2,…)
NOT (Điều kiện)


4. Định dạng dữ liệu
4.1 Thay đổi kích thước dòng và cột
Đặt chuột vào đường ngăn cột (hàng) và rê chuột
Đổi độ rộng nhiều cột (hàng): Lệnh Format/Column /Width hộp thoại Column xuất hiện. cho tham số cột (đối với hàng cũng tương tự)
Định dạng dữ liệu số
Định dạng dữ liệu số
Một số khi nhập vào ô được tự động canh lề phải
Hiển thị phụ thuộc: Lớp (Category) và dạng. Các lớp gồm: Number, date, Time, Percent,…Trong lớp lại có nhiều dạng như: M/D/YY, D-MMM-YY,…
Cách thực hiện định dạng: Lệnh Format  cell, chọn Number, chọn số chữ số sau dấu phảy, dấu ngăn cách 1000, kiểu biểu hiện số âm.
Có thể dùng các nút Currency Style, Percentage Style, Comma Style, Increase/Decrease Decimal trên thanh Formatting để định dạng lại dữ liệu số
4.4 Hiệu chỉnh vị trí của dữ liệu trong ô
4.4 Hiệu chỉnh vị trí của dữ liệu trong ô
Khung Horizontal: điều chỉnh dữ liệu theo chiều ngang: Center, Right, Fill (điền toàn bộ ô bởi ký tự có trong ô), Justify, Center Across Select.
Khung Vertical: Điều chỉnh dữ liệu theo chiều dọc: Top, Bottom, Center, Justify
Ô kiểm Wrap text: cố dịnh độ rộng cột, dàn dữ liệu ra nhiều dòng
Ô Shrink to fit: cố lèn dữ liệu vào ô
Merge Cells: hợp các ô thành 1 ô.
Orientation: quay dữ liệu

4.5 Định dạng Font
Font Type
Font Style
Font Size
Underline
Color
Ô Preview để xem qua hiển thị

4.6 Tạo các đường kẻ ô
các đường viền: None, Outline, Inside
Các kiểu nét vẽ
các nét Border
Màu đường kẻ
4.6 Tạo các đường kẻ ô
Chọn vùng ô, lệnh Format/cells/trong hộp thoại Format cells chọn Border
Nút outline: vẽ đường viền bao, Inside: vẽ các đường kẻ trong ô, none: hủy bỏ các đường kẻ
Tám nút trong khung Border kẻ các đường ngang dọc, chéo trong vùng ô, ở giữa các nút là mẫu tác dụng của các nút
Khung Style: chọn 1 trong 13 mẫu nét kẻ.
Bật tắt lưới ô của bảng tính: Tools/Options/View dùng dấu kiểm để bật tắt Gridline.

4.7. Định dạng nền dữ liệu
Có thể tô nền các ô theo mẫu tô và màu tô. Chọn vùng > Format/cells > chọn Patterns > xử lý hộp màu
4.8 Sắp xếp dữ liệu
Sắp xếp dữ liệu trong khối ô đã chọn
Thực hiện theo hàng ngang hoặc dọc
Sắp xếp tối đa theo 3 khóa
Data/Sort > chọn khóa thư nhất/kiểu khóa (tăng giảm) >chọn khóa thứ 2/kiểu khóa
Nếu chỉ sắp xếp theo hàng hay cột thì chỉ cần vào Dât/Sort/Options > chọn Sort Top to Bottom (cho hàng) hay Sort Left to right (cho cột)
5. Vẽ biểu đồ
Xét vd về thu nhập của 1 đơn vị NN
(đơn vị tính ngàn đồng)
5.1 Các bước tạo biểu đồ
Chọn vùng, VD chọn A1:B8
Nháy vào biểu tượng Wizard trên thanh công cụ
Nhận hộp thoại Chart Wizarrd – step 1 of 4 Chart Type
Thực hiện theo từng bước để chọn: kiểu biểu đồ, các nhãn tiêu đề, thuyết minh, trục X và trục Y, Legend, chọn các màu và hướng quay cho trục X, Y
Chọn nơi sẽ đặt biểu đồ và có thể di chuyển biểu đồ trong 1 Sheet.
Sau đây là minh họa thực hiện các bước.
5.1 Các bước tạo biểu đồ
Chọn vùng (A1:B8)
Nháy vào biểu tượng Wizard trên thanh công cụ
Xuất hiện trang Chart Wizard – Step 1 of 4 – chart Type
Next
Bước 1
Bước 2
Cho nguồn dữ liệu và cách sắp xếp
Bước 3
Tên biểu đồ
Tên trục x
Tên trục Y
Bước 4
Chọn nơi đặt biểu đồ: tại Sheet hiện hành hoặc Sheet mới. Sau có thể dùng Kéo-thả chuột để di chuyển đến nơi cần thiết và chỉnh sửa
6. In bảng tính ra giấy
Định dạng trang bằng lệnh File/ Page setup
- Mục Page để định chế độ in ngang hoặc dọc
- Mục Margine để định lề trang in
- Header/Footer: định tiêu đề đầu trang cuối tr.
- Mục Sheet: Lựa chọn in lưới (gridlines), vùng in (Print Area), Thứ tự trang in (Page Order) - không nên thay đổi
Trước khi in nên xem qua bằng Print Preview