admin 4 tuần trước

Các hàm và công thức thông dụng trong Microsoft Excel

Mục lục

CÁC HÀM TRONG EXCEL

Nhóm hàm xử lý ngày tháng

CÚ PHÁP	CÔNG DỤNG
TODAY()	Trả về ngày hiện hành của hệ thống Ví dụ: = TODAY()
NOW()	Trả về ngày và giờ hiện hành của hệ thống Ví dụ: =NOW()
DAY(date)	Trả về giá trị ngày trong tháng của biểu thức ngày date Ví dụ: =DAY(D1)
MONTH(date)	Trả về giá trị tháng của biểu thức ngày date Ví dụ: =MONTH(D1)  9
YEAR(date)	Trả về giá trị năm của biểu thức ngày date Ví dụ: =YEAR(D1)  2020
WEEKDAY(date)	Trả về số thứ tự ngày trong tuần của biểu thức date Giá trị trả về là 1 (Sunday), 2 (Monday),…, 7(Starurday) Ví dụ: =WEEKDAY(D1)  1
DATE(year, month, day)	Trả về giá trị dạng Date theo quy định của hệ thống

CÚ PHÁP

CÔNG DỤNG

Ví dụ: = DATE(2020,09,15)  15/09/2020

TIME(hour, minute, second)

Trả về giá trị dạng Time

Ví dụ: = TIME(8, 25, 28)  8:25:28 AM

Bảng 2-3 Các hàm ngày và giờ

Nhóm hàm xử lý ký tự

CÚ PHÁP	CÔNG DỤNG
LEFT(text, num_chars)	Trả về num_chars ký tự bên trái chuỗi text. Ví dụ: =LEFT(“ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI”, 7)  “ĐẠI HỌC”
RIGHT(text, num_chars)	Trả về num_chars ký tự bên phải chuỗi text. Ví dụ: = RIGHT(“ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI”, 7)  “VẬN TẢI”
MID(text,start_num, num_chars)	Trả về chuỗi ký tự có độ dài num_chars bắt đầu từ vị trí start_num của chuỗi text. Ví dụ: = MID (“ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI”,9,7)  “CÔNG NGHỆ”
UPPER(text)	Chuyển chuỗi text thành chữ in hoa Ví dụ: = UPPER(“ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI”)  ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI
LOWER(text)	Chuyển chuỗi text thành chữ thường Ví dụ: = LOWER(“ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI”)  ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI
PROPER(text)	Đổi các ký tự đầu của mỗi từ trong chuỗi text thành chữ in hoa, còn lại đều là chữ thường. Ví dụ: PROPER(“Đại học Công nghệ giao thông vận tải ”)  ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI.
TRIM(text)	Cắt bỏ các ký tự trống vô ích trong chuỗi text Ví dụ: = TRIM(“ ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI ”)  “ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI”
LEN(text)	Trả về độ dài của chuỗi text

CÚ PHÁP	CÔNG DỤNG
	Ví dụ: = LEN(“ĐẠI HỌC CÔNG NGHỆ GIAO THÔNG VẬN TẢI”)  35

Bảng 2-4 Các hàm xử lý chuỗi

Nhóm hàm thống kê

CÚ PHÁP	CÔNG DỤNG
MIN(number1, number2,…)	Trả về giá trị nhỏ nhất của các giá trị số trong danh sách tham số. Ví dụ: = MIN (1,2,3,5)  1
MAX(number1, number2,…)	Trả về giá trị nhỏ lớn nhất của các giá trị số trong danh sách tham số. Ví dụ: = MAX (1,2,3,5)  5
AVERAGE(number1, number2,…)	Trả về giá trị trung bình của các giá trị số trong danh sách tham số. Ví dụ: = AVERAGE (1,2,3,5)  2.75
RANK(number, ref, [order])	Trả về thứ hạng của number trong ref, với order là cách xếp hạng. Nếu order =0 hoặc được bỏ qua thì ref được hiểu là có thứ tự giảm dần Nếu order <> 0 thì ref được hiểu là có thứ tự tăng dần
COUNT(value1, value2,…)	Đếm số các giá trị số trong danh sách tham số Ví dụ: = COUNT (2, “hai”,4,-6)  3
COUNTA(value1, value2,…)	Đếm số các ô không rỗng trong danh sách tham số Ví dụ: =COUNTA(A1:A5)  4
COUNTBLANK(range)	Đếm số các ô rỗng trong vùng range Ví dụ: =COUNTBLANK (B1:B5)

CÚ PHÁP

CÔNG DỤNG

COUNTIF (range, criteria)

Đếm các ô thỏa mãn điều kiện criteria trong vùng range

Range: là vùng mà điều kiện sẽ được so sánh

Criteria: là chuỗi mô tả điều kiện. Ví dụ: “10”, “>15”, “<20”

Ví dụ: =COUNTIF(A2:A6,”Táo”)

Bảng 2-5 Các hàm thống kê đơn giản

Nhóm hàm logic

CÚ PHÁP

CÔNG DỤNG

AND(logical1, logical2,…)

Trả về giá trị TRUE nếu tất cả các điều kiện đều là TRUE

Ví dụ: = AND(3>2, 5<8, 9=9) TRUE

CÚ PHÁP	CÔNG DỤNG
OR(logical1, logical2,…)	Trả về giá trị FALSE nếu tất cả các điều kiện đều là FALSE Ví dụ: = OR(2>3, 12<8, 9>3)  TRUE = OR(2>3, 12<8, -9>3)  FALSE
NOT(logical)	Lấy phủ định của giá trị logical Ví dụ: = NOT(2>3) TRUE
IF (logical_test, value_if_true, value_if_false)	Trả về giá trị value_if_true nếu điều kiện logical_test là TRUE, ngược lại sẽ trả về giá trị value_if_false Ví dụ: = IF(A1>=5, “Đỗ”, “Trượt”)

Bảng 2-6 Các hàm Logic

Nhóm hàm xử lý tham chiếu

- - - Hàm VLOOKUP: Hàm tham chiếu theo cột

Cú pháp:

VLOOKUP (lookup_value, Table_array, col_index_num, range_lookup)

Trong đó:

Lookup_value: Giá trị tra cứu. Table_array: Vùng tham chiếu. Col_index_num: Số cột muốn hiển thị.

[Range_lookup]: 0_tìm chính xác, 1_tìm tương đối

Chức năng:

Tìm giá trị lookup_value trong cột trái nhất bảng table_array theo chuẩn dò tìm range_lookup, trả về vị trí tương ứng trong cột thứ col_index_num (nếu tìm thấy)

Range_lookup=1: Tìm tương đối. Danh sách các giá trị dò tìm của bảng Table_array phải sắp xếp theo thứ tự tăng dần. Nếu tìm không thấy sẽ trả về giá trị lớn nhất nhưng nhỏ hơn lookup_value.
Range_lookup=0: Tìm chính xác. Danh sách các giá trị dò tìm của bảng Table_array không cần sắp xếp thứ tự. Nếu tìm không thấy sẽ trả về lỗi #N/A.

Ví dụ: Hiển thị tiền phải thanh toán của khách hàng có mã hóa đơn DH003.

- - - Hàm HLOOKUP: Hàm tham chiếu theo hàng

Cú pháp

HLOOKUP (Lookup_value, Table_array, row_index_num, range_lookup)

Trong đó:

Lookup_value: Giá trị tra cứu. Table_array: Vùng tham chiếu. row_index_num: Số hàng muốn hiển thị.

[Range_lookup]: 0_tìm chính xác, 1_tìm tương đối

Chức năng

Ý nghĩa của các đối số của hàm Hlookup tương tự như hàm Vlookup.

Tìm giá trị lookup_value trong dòng trên cùng của bảng table_array theo chuẩn dò tìm range_lookup, trả về giá trị tương ứng trong dòng thứ row_index_num (nếu tìm thấy).

Ví dụ: Điền tên hàng theo mã hàng.

- - - Hàm MATCH

Cú pháp

MATCH (lookup_value, lookup_array, match_type)

Trong đó:

Lookup_value: Giá trị tra cứu. Lookup_array: Vùng tham chiếu.

Match_type: 0_tìm chính xác, 1_tìm tương đối

Chức năng

Hàm trả về vị trí của lookup_value trong mảng lookup_array theo cách tìm match_type

Match_type = 1: Tìm tương đối, danh sách các giá trị dò tìm của bảng Table_array phải sắp xếp theo thứ tự tăng dần. Nếu tìm không thấy sẽ trả về vị trí của giá trị lớn nhất nhưng nhỏ hơn lookup_value.
Match_type = 0: Tìm chính xác, danh sách các giá trị dò tìm của bảng Table_array không cần sắp xếp thứ tự. Nếu tìm không thấy sẽ trả về lỗi #N/A
Match_type = -1: Tìm tương đối, danh sách phải sắp xếp các giá trị dò tìm của bảng Table_array theo thứ tự giảm dần. Nếu tìm không thấy sẽ trả về vị trí của giá trị nhỏ nhất nhưng lớn hơn lookup_value.

Ví dụ:

- - - Hàm INDEX

Cú pháp

INDEX (Array, Row_num, Column_num)

Chức năng

Trả về giá trị trong ô ở hàng thứ row_num, cột thứ column_num trong mảng array.

Ví dụ

Các hàm toán học

CÚ PHÁP	CÔNG DỤNG
ABS(number)	Trả về giá trị tuyệt đối của một số thực Ví dụ: = ABS(12 – 20)  8
INT(number)	Trả về số nguyên lớn nhất không vượt quá number Ví dụ: = INT(5.6) 5 = INT(-5.6)  -6
MOD(number, divisor)	Trả về số dư của phép chia nguyên number cho divisor (trong đó number và divisor là các số nguyên). Ví dụ: = MOD(5,3)  2
ODD(number)	Làm tròn lên tới một số nguyên lẻ gần nhất Ví dụ: = ODD(3.6) 5 = ODD(-2.2) -3
PRODUCT(number1, number2,…)	Tính tích của các giá trị trong danh sách tham số Ví dụ: = PRODUCT(2,-6,3,4)  -144
ROUND(number, num_digits)	Làm tròn số number với độ chính xác đến num_digits chữ số thập phân (với quy ước 0 là làm tròn tới hàng đơn vị, 1 là lấy một chữ số thập phân, -1 là làm tròn tới hàng chục, …) Ví dụ: = ROUND(5.13687,2)  5.14 = ROUND(145.13687)  100
RAND()	Tạo ra một số ngẫu nhiên trong khoảng 0 và 1 Ví dụ: =RAND()  Số ngẫu nhiên
SQRT(number)	Tính căn bậc hai của số dương number Ví dụ: =SQRT(36)  6
POWER(number, power)	Trả về lũy thừa power của số number. Ví dụ: = POWER(2,3)=8
SUM(number1, number2,…)	Tính tổng của các giá trị trong danh sách tham số. Ví dụ: = SUM(2,-6,8,4)  8
SUMIF (range, criteria, [sum_ranger]) CÔNG DỤNG	Sum_range: vùng được tính tổng. Các ô trong vùng này sẽ được tính tổng nếu các ô tương ứng trong

CÚ PHÁP

CÔNG DỤNG

Tính tổng các ô thỏa mãn điều kiện.

Range: vùng mà điều kiện sẽ được so sánh

Criteria: chuỗi mô tả điều kiện. Ví dụ: “10”, “>15”, “<20”, …

Sum_ranger: vùng dữ liệu cần tính tổng.

vùng range thỏa mãn điều kiện. Nếu không có sum_range thì vùng range sẽ được tính

Ví dụ 1: = SUMIF (C2:C6, “>=15”, E2:E6)

Bảng 2-7 Các hàm toán học

Các hàm kiểm tra (IS_function)

Các hàm kiểm tra dùng để kiểm tra xem kiểu của một giá trị hay của một ô có thỏa mãn một điều kiện nào đó không.

Các hàm kiểm tra luôn trả về một trong hai giá trị TRUE hoặc FALSE. Như vậy, các hàm này có thể đáp ứng được trong các trường hợp mà có một số dữ liệu ngoại lệ trong một bảng dữ liệu cần tính toán.

- - - Hàm ISERROR

Cú pháp ISERROR (value)

Ý nghĩa

Trả về giá trị TRUE nếu value là một lỗi bất kỳ, ngược lại trả về giá trị FALSE

- - - Hàm ISNA

Cú pháp ISNA (value)

Ý nghĩa

Trả về giá trị TRUE nếu value là lỗi #N/A, ngược lại trả về giá trị FALSE

- - - Hàm ISNUMBER

Cú pháp ISNUMBER (value)

Ý nghĩa

Trả về giá trị TRUE nếu value là giá trị số, ngược lại trả về giá trị FALSE

- - - Hàm ISTEXT

Cú pháp ISTEXT (value)

Ý nghĩa

Trả về giá trị TRUE nếu value là một chuỗi, ngược lại trả về giá trị FALSE

Công thức mảng

Hỗ trợ thống kê, tính toán dựa trên nhiều điều kiện khách nhau và được thực hiện trên mảng dữ liệu.

Khi thực hiện tính toán bằng công thức mảng thì công thức được bao bọc bởi hai dấu {}. Hai dấu ngoặc này người dùng không gõ mà được tự phát sinh khi người dùng thực hiện tính toán bằng cách nhấn tổ hợp phím Ctrl+Shift+Enter. Nếu khi thực hiện tính toán hoặc sửa chữa công thức mà quên nhấn tổ hợp phím trên thì công thức sẽ trả về giá trị không đúng hay thông báo lỗi #VALUE! Error.

Ví dụ: Tính tổng số lượng điện thoại do Minh bán

Thực hiện

Nhập công thức:

=SUM ((M27:M32=”Điện thoại”) * (N27:N32=”Minh”) * (O27:O32)

Ấn tổ hợp phím Ctrl+Shift+Enter để thực hiện tính toán bằng công thức mảng.

Nếu phần tử Mi là “Điện thoại” tức là 1(True) được trả về, ngược lại trả về 0 (False)
Nếu phần tử Ni là “Điện thoại” tức là 1(True) được trả về, ngược lại trả về 0 (False)
Cuối cùng, phần tử Oi được trả về.

Ba giá trị này được nhân lại với nhau. Sau đó, hàm Sum ở ngoài cùng sẽ tính tổng cho tất cả các dòng

Cơ sở dữ liệu

- 1. Khái niệm

Khi quản lý thông tin về một đối tượng, ta phải quản lý các thuộc tính liên quan đến đối tượng đó. Ví dụ, quản lý nhân viên thì cần quản lý tông tin của nhân viên như: họ tên, mã nhân viên, phái, năm sinh, nơi sinh, địa chỉ, mã ngạch, bậc, hệ số, lương, phụ cấp, chức vụ, … Đó là các thuộc tính phản ánh nội dung của một đối tượng quản lý. Các thuộc tính đó thường được biểu diễn dưới dạng các kiểu dữ liệu khác nhau (là chuỗi, số, ngày tháng,

…) và được hợp nhất thành một đơn vị thông tin duy nhất gọi là bản ghi (record). Các bản ghi hợp thành một cơ sở dữ liệu.

Trong Excel, cơ sở dữ liệu có dạng như một danh sách. Ví dụ: danh sách nhân viên, danh sách hàng hóa, … Mỗi danh sách có thể gồm có một hay nhiều cột, mỗi cột được gọi là một trường (field) của cơ sở dữ liệu. Tên của cột sẽ được gọi là tên trường.

Hàng đầu tiên trong danh sách (cơ sở dữ liệu) chứa các tên trường được gọi là hàng tiêu đề (Header row), các hàng tiếp theo mỗi hàng là một bản ghi (record) cho biết thông tin về đối tượng mà ta quản lý.

Sắp xếp dữ liệu

Lệnh Data  Sort dùng để sắp xếp các hàng hoặc các cột trong vùng được chọn theo thứ tự tùy chọn tương ứng khóa sắp xếp được chỉ định, vùng sắp xếp phải chọn tất cả các ô có liên hệ với nhau.

Thực hiện

Chọn vùng dữ liệu cần sắp xếp
Vào Data  (Group Sort & Filter)  Sort, xuất hiện hộp thoại.

Hình 2.11. Hộp thoại Sort

- Sort by: Chọn khóa sắp xếp
- Sort On: Giá trị sắp xếp (giá trị, mầu nền, màu chữ, biểu tượng).
- Order: Thứ tự tăng dần hoặc giảm dần.
- Add Level: Thêm khóa sắp xếp, nếu dữ liệu trong cột khóa phía trên bị trùng.
- Copy Level: Copy điều kiện.
- Delete Level: Xóa điều kiện
- Nếu muốn sắp xếp theo hàng thì chọn nút lệnh Options của hộp thoại Sort, sau đó chọn mục Sort left to right.

Muốn sắp xếp nhanh theo cột nào đó thì đặt con trỏ vào ô bất kỳ của cột đó, Click chọn nút Sort AZ hoặc Sort ZA trên thanh công cụ chuẩn.

Lọc dữ liệu Tự động – Nâng cao

- - 1. Lọc dữ liệu tự động (AutoFilter)

Chức năng: Lệnh Data (Group Sort & Filter)  Filter dùng để lọc các bản ghi thỏa mãn những tiêu chuẩn nào đó từ cơ sở dữ liệu ban đầu. Kết quả chỉ hiển thị những bản ghi thỏa mãn điều kiện. Những bản ghi còn lại sẽ tạm thời bị ẩn đi.

Thực hiện

- - - - Chọn vùng CSDL với tiêu đề
      - Chọn Tab Data  (Group Sort & Filter)  Filter. Excel sẽ tự động xuất hiện các nút thả cạnh tên field cho phép chọn điều kiện lọc tương ứng với các field đó.
      - Chọn điều kiện lọc trong hộp liệt kê của từng field tương ứng.
      - Chọn Text Fillter/Number Fillter để thực hiện chức năng lọc nâng cao theo yêu cầu của người dùng.
    1. Lọc dữ liệu nâng cao (Advanced Filter)

Chức năng

Lệnh Data  (Group Sort & Filter)  Advanced dùng để trích ra các mẫu tin theo các điều kiện chỉ định trong vùng điều kiện được tạo trước.

Thực hiện

Tạo vùng điều kiện lọc. Sử dụng một trong hai cách sau.

Cách 1: Sử dụng tên trường để tạo vùng điều kiện.

Vùng điều kiện sẽ có ít nhất hai hàng, hàng đầu chứa các tên trường (field) điều kiện, các hàng khác dùng để mô tả điều kiện.

- Chọn các ô trống trong bảng tính để làm vùng điều kiện
- Sao chép tên field điều kiện làm tiêu đề của vùng điều kiện.
- Nhập trực tiếp các điều kiện vào ô dưới tên trường tương ứng. Các điều kiện ghi trên cùng một hàng là các điều kiện thỏa nãm đồng thời (AND). Các điều kiện ghi trên các dòng khác nhau là những điều kiện thỏa mãn không đồng thời (OR)

Ví dụ:

Cách 2: Sử dụng công thức để tạo vùng điều kiện.

Vùng điều kiện sẽ có 2 ô. Ô trên chứa tiêu đề hoặc bỏ trống nhưng phải khác với tên trường, ô dưới là công thức mô tả điều kiện.

- Chọn 2 ô trống trong bảng tính để làm vùng tiêu chuẩn.
- Nhập tiêu đề ở ô trên của vùng tiêu chuẩn
- Nhập công thức vào ô bên dưới mô tả điều kiện. Dùng bản ghi đầu tiêu trong cơ sở dữ liệu để đặt điều kiện so sánh. Hàm AND dùng để lập các điều kiện thỏa mãn đồng thời, hàm OR dùng để lập các điều kiện thỏa mãn không đồng thời.
Vào Data  (Group Sort & Filter)  Advancel, xuất hiện hộp thoại có các tùy chọn sau
- Action:
  - Filter the list, in-place: kết quả hiển thị trực tiếp trên vùng CSDL
  - Copy to another location: kết quả được đặt tại một vị trí khác
- List range: Chọn địa chỉ vùng CSDL
- Criteria range: Chọn địa chỉ vùng tiêu chuẩn
- Copy to: Chọn địa chỉ của ô đầu tiên trong vùng kết quả (phải chọn mục Copy to another location).
- Unique records only: Nếu có nhiều bản ghi giống nhau thì chỉ lấy duy nhất một bản ghi đại diện. Ngược lại, lấy hết các mẫu tin thỏa mãn điều kiện của vùng tiêu chuẩn (dù giống nhau).

Các hàm Cơ sở dữ liệu

Các hàm cơ sở dữ liệu mang tính chất thống kê những bản ghi trong CSDL có trường thỏa mãn điều kiện của vùng tiêu chuẩn đã được thiết lập trước.

Cú pháp chung

= Tên hàm (database, field, criteria)

– Database: Địa chỉ vùng CSDL (Chọn địa chỉ tuyệt đối để sao chép).

Field: Cột cần tính toán, field có thể là tên trường, địa chỉ của ô tên trường (field) hoặc số thứ tự của trường đó (cột thứ nhất của vùng CSDL đã chọn tính là 1 và tăng dần sang trái).
Criteria: Địa chỉ vùng điều kiện

Ví dụ: Ta có một cơ sở dữ liệu như sau.

Hình 2.12 Các hàm về Cơ sở dữ liệu

- - - Một số hàm thông dụng về CSDL

Cú pháp	Công dụng
DAVERAGE(database, field, criteria)	Tính trung bình cộng các giá trị trong cột field của các bản ghi thoả mãn criteria
DMAX (database, field, criteria)	Tìm giá trị lớn nhất trong cột field của các bản ghi thoả mãn criteria
DMIN (database, field, criteria)	Tìm giá trị nhỏ nhất trong cột field của các bản ghi thoả mãn criteria
DCOUNT (database, field, criteria)	Đếm các ô kiểu số trong cột field của các bản ghi thoả mãn criteria

Cú pháp	Công dụng
DCOUNTA (database, field, criteria)	Đếm các ô khác rỗng trong cột field của các bản ghi thoả mãn criteria
DSUM (database, field, criteria)	Tính tổng các số trong field của các bản ghi thoả mãn criteria

Bảng 2-8 Các hàm về cơ sở dữ liệu

Subtotals

- - - Chức năng

Thống kê dữ liệu theo từng nhóm trong CSDL.

- - - Thực hiện

Xét cơ sở dữ liệu BẢNG LƯƠNG dưới đây. Vấn đề đặt ra là cần tình tổng tiền lương theo từng nhóm Đơnvị.

- - - - Dùng lệnh Data  (Group Sort & Filter)  Sort để sắp xếp dữ liệu theo Đơn vị. Mục đích để các bản ghi có cùng đơn vị nằm liền kề nhau.
      - Chọn bảng CSDL cần tổng hợp với tiêu đề là một hàng
      - Vào Data  (Group Outline) Subtotals, xuất hiện hộp thoại Subtotal với các tùy chọn sau:

At each change in: Chọn tên trường cần tổng hợp

Use function: Chọn hàm sử dụng tính toán hay thống kê

Add subtotal to: Chọn tên trường chứa dữ liệu cần thực hiện tính toán hay thống kê

Replace current subtotals: Thay thế các dòng tổng hợp cũ để ghi dòng tổng hợp mới.

Page break between

groups: Tạo ngắt trang giữa các nhóm

Summary below data: Thêm dòng tổng hợp sau mỗi nhóm.

Kết quả Subtotal

Hình 2.13. Kết quả Subtotal – Chế độ hiển thị All Recrord

- - - Làm việc với màn hình kết quả sau khi tổng hợp

Click vào nút để chọn các mức dữ liệu bạn muốn xem

- - - - Chỉ hiển thị tổng chính (Grand Total Only)

- - - - Hiển thị tổng chính và tổng phụ (Grand Total And Subtotal)
      - Hiển thị chi tiết tất cả các bản ghi cùng các tổng hợp (All Record)

Định dạng và in ấn bảng tính

- 1. Đặt lề trang in

Lề là khoảng trống giữa nội dung và cạnh của trang giấy in. Đôi khi, cần phải điều chỉnh lề để dữ liệu hiển thị dễ đọc hơn.

Chuyển văn bản sang trạng thái in ấn. Chọn File  Print
Trong trình đơn thả xuống, chọn Settings  chọn Page Margins  Chọn kiểu lề phù hợp, hoặc tự thiết lập kích thước lề nhờ tính năng Custom Margins

Hình 2.14. Cách đặt lề trang in trong Excel

Tạo tiêu đề đầu trang và cuối trang

Để định dạng Header&Footer cho trang in, ta thực hiện như sau:

Chọn Ribbon File Print chọn Page Setup, xuất hiện hộp thoại Page Setup

Hình 2.15. Hộp thoại Page Setup

Chọn tab Header/Footer  Nhập nội dung cần hiển thị tương ứng vào mục Header và Footer  Ấn OK

Lặp lại tiêu đề bảng tính khi sang trang

Để tiêu đề của bảng tính cơ sở dữ liệu được lặp lại ở tất cả trang tin (trong trường hơp dữ liệu bảng tính quá dài hơn một trang in). Để thuận tiện cho người đọc theo dõi nội dung in ấn, Excel cung cấp tính năng lặp lại tiêu đề bảng tính.

Chọn Page Layout  chọn Print Titles, xuất hiện hộp thoại.
Chọn hàng hoặc cột lặp lại trên mỗi trang, nhấp vào nút Collapse Dialog  Hộp thoại Page Setup sẽ bị thu gọn lại, con trỏ sẽ trở thành mũi tên lựa chọn nhỏ (mũi tên màu đen)  Chọn hàng hoặc cột cần lặp lại bằng cách bôi đen vùng dữ liệu cần lặp hoặc nhập địa chỉ vùng dữ liệu cần lặp lại vào hộp địa chỉ.

Nút Collapse Dialog, chọn dòng lặp lại

Chọn cột lặp lại

Hình 2.16. Hộp thoại Page Setup/Sheet

Hình 2.17. Chọn hàng cần lặp dữ liệu

Hình 2.18. Chọn cột cần lặp dữ liệu

Thực hiện in ấn

- - 1. Lưu trữ bảng tính

Lưu trữ với định dạng excel

Sử dụng tổ hợp phím Ctrl+S hoặc vào File  Save/ Save As (Lưu với tên file khác), xuất hiện màn hình lưu trữ  Chọn vị trí lưu.

Hình 2.19. Màn hình lưu trữ dữ liệu

OneDrive: Lưu trữ bảng tính qua dịch vụ điện toán đám mây
Computer: Lưu trữ bảng tính trong máy tính
Add a Place: Chọn một dịch vụ lưu trữ dữ liệu khác Màn hình xuất hiện tiếp theo sau khi chọn nơi lưu trữ:

Hình 2.20. Chọn vị trí lưu trữ dữ liệu

Lưu trữ với định dạng tùy ý

Các dạng có thể lưu trữ:

.xlsm: định dạng excel có chứa macro
.xls: định dạng excel 97-2000
.mhtm, .mhtml: định dạng web.
.xltx: định dạng template dùng cho việc tạo bảng định dạng mẫu
.txt, .csv: định dạng text
.pdf: định dạng pdf
.xps: định dạng XPS

Khi lưu workbook với một trong các định dạng này ta có thể lựa chọn các tùy chọn nội dung muốn thêm vào trong workbook trong mục Options.

Lưu workbook trên OneDrive

Để sử dụng OneDrive, người dùng phải có hoặc đăng ký (mới) một tài khoản miễn phí của Microsoft tại https://onedrive.live.com.

Thực hiện lưu trữ trên OneDrive

Đăng nhập vào tài khoản Microsoft trong cửa số Microsoft Excel. Chú ý vào góc phía trên bên phải cửa sổ Excel, nhấp chuột vào Sign in
Nhập tài khoản Microsoft (email) và mật khẩu để đăng nhập. Khi đăng nhập thành công, tên tài khoản sẽ xuất hiện ở góc trên bên phải màn hình.

Chọn tập tin cần lưu trữ  vào File  Chọn Save/ Save as  Chọn OneDrive.

Chọn OneDrive

Hình 2.21. Màn hình lưu workbook trên OneDrive

Chọn vị trí upload trên OneDrive  Chọn Save để upload tập tin lên OneDrive.

Sau khi lưu trữ, file dữ liệu có thể được truy cập và chỉnh sửa ở bất kỳ đâu bằng cách đăng nhập tài khoản Microsoft qua địa chỉ https://onedrive.live.com.

- - 1. In ấn
      - Chọn một hoặc nhiều bảng tính (sheet) muốn in. Muốn chọn nhiều bảng tính, nhấp chọn sheet đầu tiên, giữ phím Ctrl + chọn các sheet còn lại.

- - - - Chọn File Print, mở ra cửa số In ấn
      - Chọn Print Active Sheets (Các trang tính hiện hành)  Chọn vùng in (Print Range)

Print Active Sheets: In các trang tính (sheet) hiện hành

Print Entire Workbook: In toàn bộ bảng tính

Print Selection: Chỉ in vùng được lựa chọn

- - - - Xem trước nội dung in trong cửa số Preview

- - - - Ấn Print
    1. Tùy chỉnh nội dung trước khi in ấn

Đôi khi, phải thực hiện những điều chỉnh nhỏ khi in ấn để nội dung nằm gọn trong một trang in.

- - - - Hiển thị trang in trong cửa số Preview. File  Print. Nếu nội dung bảng tính bị cắt thành nhiều trang, người dùng muốn nội dung nằm gọn trong một trang in, chuyển Bước 2.
      - Trong mục Settings  chọn trình đơn thả xuống Scaling  chọn Fit All Columns on One Page.  Bảng tính sẽ được thu gọn vừa đủ trong một trang in.

Lưu ý: Nội dung bảng tính sẽ trở nên khó đọc hơn vì chúng được thu nhỏ lại. Vì vậy, có thể chúng ta sẽ không muốn sử dụng tùy chọn này khi in một bảng tính nhiều thông tin. Chế độ mặc định để in ấn với kích thước thực là No Scaling.

Trình diễn dữ liệu

- 1. Thao tác với chức năng PivotTables
    - Ý nghĩa

Thống kê dữ liệu theo nhiều cấp độ khác nhau với nhiều hình thức đa dạng từ một bảng dữ liệu chính.

- - - Cách thực hiện
      - Đặt con trỏ trong bảng dữ liệu
      - Chọn Tab Insert  (Group Tables)  Pivot Table  PivotTable, xuất hiện hộp thoại yêu cầu chọn vùng dữ liệu làm thống kê và nơi xuất hiện bảng thống kê.

Hình 2.22. Hộp thoại Create PivotTable

Select a table or range: Cho phép chọn vùng dữ liệu là Sheet hiện hành
Use an external data source: Cho phép chọn vùng dữ liệu từ file Excel có sẵn
New Worksheet: Phát sinh bảng thống kê trên sheet mới
Existing Worksheet: Phát sinh bảng thống kê từ địa chỉ được nhập vào.

Click Ok, xuất hiện hộp thoại cho phép kéo thả các field là điều kiện kiện thống kê.

Hình 2.23. Hộp thoại PivotTable Field List

Vùng ROWS và COLUMNS sẽ chứa field làm điều kiện thống kê.
Vùng VALUES chứa những field số liệu muốn thống kê

Drag chuột kéo field vào vùng tương ứng, kết quả sẽ tự động cập nhật tạo thành bảng thống kê.

Ví dụ: Kết quả thống kê theo nhóm đơn vị

Hình 2.24. Kết quả thống kê PivotTable

Thao tác với chức năng Consolidate

- - - Ý nghĩa

Consolidate cho phép hợp nhất từ nhiều vùng dữ liệu liệu nguồn (Sources) và hiển thị kết quả trong vùng dữ liệu đích (Destination).

- - - Thực hiện

Giả sử công ty ABC có 3 cửa hàng, mỗi cửa hàng có một bảng báo cáo doanh thu năm 2019. Công ty có nhu cầu tổng hợp các báo cáo của 3 cửa hàng thành một báo cáo doanh thu năm 2019 của công ty.

- - - - Chọn vùng dữ liệu đích bằng cách trỏ chuột vào một ô bất kỳ ở vùng đó.
      - Chọn Tab Data  (Group Data Tools)  Consolidate, xuất hiện hộp thoại Consolidate.

Function: Chọn hàm sử dụng (Sum, Min, Max, …), thông thường khi tổng hợp dữ liệu ta chọn hàm Sum để tính tổng.

Reference: Để tham chiếu lần lượt địa chỉ của các vùng dữ liệu nguồn.

Browse: Để chọn dữ liệu từ tập tin khác.

- - - - All reference: Chứa tất cả các vùng dữ liệu nguồn cần thiết cho việc hợp nhất. Để thêm dữ liệu nguồn, ta chọn Add. Để xóa một vùng dữ liệu trong hộp All references, ta chọn vùng đó, rồi nhấp nút Delete.

Hình 2.25. Hộp thoại Consolidate

- - - - Use labes in:

Top row: nếu muốn sử dụng lại tên cột của vùng nguồn,

Left column: nếu muốn sử dụng lại các giá trị của cột đầu tiên của vùng nguồn. (Ở đây là giá trị của cột Mặt hàng).

- - - - Create links to source data: Nếu muốn dữ liệu hợp nhất được cập nhật mỗi khi có thay đổi ở vùng dữ liệu nguồn.

Kết quả thu được:

Sử dụng nút để thay đổi chế độ hiển thị kết quả

Tạo đồ thị trên dữ liệu của bảng tính

Đồ thị là một dạng biểu diễn dữ liệu trực quan, sinh động giúp người quản lý dữ liệu, người xem dễ dàng nắm bắt các số liệu theo các tiêu chí biểu diễn dữ liệu được đặt ra trước đó.

Đồ thị giúp trình bày dữ liệu của bảng tính bằng cách hiển thị các sự kiện và con số dưới dạng hình ảnh trực quan rất có ý nghĩa.

Đồ thị cho phép biểu diễn sự tương quan của dữ liệu trong bảng tính trên phương diện đồ họa, giúp biến đổi các hàng, cột thông tin thành những hình ảnh có ý nghĩa.

Đồ thị giúp chúng ta so sánh số liệu trong bảng tính một cách trực quan, tránh việc phải đọc các số liệu chi chít trên bảng tính.

Đồ thị giúp tiên đoán được sự phát triển của dữ liệu mô tả trong bảng, làm cho bảng trở nên sinh động và thuyết phục hơn.

- - - Vẽ đồ thị
      - Chọn vùng dữ liệu cần biểu diễn đồ thị.
      - Chọn kiểu đồ thị từ Ribbon  Insert  nhóm Charts. Mỗi nhóm đồ thị sẽ có nhiều kiểu khác nhau.
      - Hoàn tất layout cho đồ thị. Layout của đồ

thị là cách bố trí các thành phần trong đồ thị sao cho đẹp mắt, dễ xem. Thực hiện bằng cách chọn đồ thị  Ribbon Design

Chart Layouts Quick Layout  Chọn cách bố trí thích hợp.

- - - - Điều chỉnh chuỗi hiển thị dữ liệu từ dòng sang cột và ngược lại trong trường hợp ở Bước 3 chưa hiển thị dạng như mong muốn. Thực

hiện: chọn đồ thị  Ribbon DesignDataSwitch Row/Column.

– Chọn dạng đồ thị khác để phù hợp hơn với dữ liệu biểu diễn. Chọn đồ thị  Ribbon Design  Type 

Change Chart Type

Hình 2.26. Hộp thoại Change Chart Type

- - - - Column: biểu đồ cột. Một biểu đồ cột thường biểu thị các thể loại dọc theo trục ngang (thể loại) và các giá trị dọc theo trục dọc (giá trị).
      - Line: biểu đồ đường. Thường dùng để biểu diễn các khuynh hướng dữ liệu tại các khoảng thời gian bằng nhau như tháng, quý hoặc năm tài chính.
      - Pie: biểu đồ hình tròn và vành khuyên bị cắt. Nên sử dụng khi:

Chỉ có một chuỗi dữ liệu.

Không có giá trị nào trong dữ liệu là giá trị âm.

Không có giá trị nào trong dữ liệu là bằng không.

Không có quá 7 thể loại và tất cả các thể loại này đều biểu thị một phần giá trị của toàn bộ hình tròn

- - - Bar: biểu đồ thanh. Sử dụng khi:
Các nhãn trục quá dài.
Các giá trị được biểu thị là các quãng thời gian
- - - - Area: biểu đồ vùng. Được dùng khi muốn biểu diễn sự thay đổi theo thời gian và hướng sự chú ý đến tổng giá trị theo một khuynh hướng nào đó.
      - X Y(Scatter): Biểu đồ phân tán. Sử dụng khi :

Muốn thay đổi thang tỷ lệ của trục ngang.

Muốn đặt trục đó theo tỷ lệ lô-ga-rit.

Các giá trị của trục ngang được cách quãng không đều.

Có nhiều điểm dữ liệu trên trục ngang.

Muốn điều chỉnh thang đo trục độc lập của biểu đồ tán xạ để cung cấp thêm thông tin về các dữ liệu có chứa cặp hoặc nhóm giá trị.

Muốn biểu thị điểm giống nhau giữa các tập hợp dữ liệu lớn thay vì sự khác biệt giữa các điểm dữ liệu.

Muốn so sánh nhiều điểm dữ liệu mà không liên quan đến thời gian, càng nhiều dữ liệu được đưa vào thì càng đưa ra những so sánh tốt hơn.

- - - - Stock: biểu đồ chứng khoán. Thường dùng trong việc minh họa những dao động lên xuống của giá cổ phiếu hay lượng mưa hàng ngày, nhiệt độ hàng năm.
      - Surface : Biểu đồ bề mặt. Sử dụng để biểu diễn mối quan hệ giữa các lượng dữ liệu lớn (trường hợp dạng biểu đồ khác khó xem)
      - Radar : So sánh các giá trị tổng hợp của một vài chuỗi dữ liệu

- - - - Combo : Biểu đồ này kết hợp 2 hay nhiều dạng biểu đồ khác nhau để biểu diễn cho nguồn dữ liệu đa dạng.
    - Điều chỉnh màu sắc cho đồ thị. Chọn đồ thị  Ribbon Design Change Colors  Bảng màu xuất hiện và chọn màu tùy thích.

Ví dụ: Thống kê học lực của sinh viên qua từng cấp học thông qua bảng tính dữ liệu.

Hình 2.27. Đồ thị thống kê dạng Column

- - - Các thao tác trên đồ thị

Di chuyển đồ thị

Chọn đồ thị  Chọn Ribbon Design  Chọn biểu tượng Move Chart, xuất hiện hộp thoại Move Chart

- - - - New sheet: Đặt chuột ở workbook mới
      - Object in: Đặt đồ thị tại một sheet thuộc workbook hiện tại.

muốn.

Thay đổi kích thước đồ thị

Đưa chuột vào đường bao của đồ thị, kéo chuột di chuyển đến kích thước mong

Sao chép đồ thị

Chọn đồ thị  nhấn Ctrl-C, di chuyển đến nơi đặt nhấn Ctrl-V

Xóa đồ thị

Chọn đồ thị  nhấn Delete.

Hiệu chỉnh đồ thị

Chọn đồ thị  R_Click trên đồ thị chọn Format Chart Area  chọn thành phần

muốn hiệu chỉnh.

- - - Đánh giá dữ liệu bằng Sparklines

Sparklines cách nhanh và đơn giản nhất để thêm thành phần đồ thị cỡ nhỏ (mini) trong một ô (cell). Sparklines tập trung vào các giá trị tối đa và tối thiểu bằng các màu sắc khác nhau để phân tích xu hướng dữ liệu như: tiêu dùng, doanh thu…Ví dụ, ta có bảng dữ liệu sau, dùng Sparklines để xem tỉ lệ lao động theo mỗi năm của từng nhóm tuổi. Tùy thuộc vào dữ liệu mà ta sẽ chọn dạng Sparklines là Column/Line/WinLoss.