Bài giảng Ứng dụng Excel trong kiểm toán BCTC

REMEMBER THE FUTURE

Chương trình đào tạo nội bộ AASC

Ứng dụng excel trong kiểm toán BCTC

Người trình bày: Phạm Anh Tuấn Thiết kế chương trình: Tạ Tiến Hoàng Ngày trình bày: 10/29/2016

Các nội dung chính

REMEMBER THE FUTURE

• Một số kiến thức cơ bản về hàm Excel

• Giới thiệu các hàm Excel căn bản

• Kỹ thuật đặt Name để tường minh hóa công thức

• Lọc dữ liệu với Auto Filter và Advanced Filter

• Một số cách convert dữ liệu khách hàng về Excel

• Cơ chế lập tổng hợp bút toán điều chỉnh

10/29/2016 2

Cơ bản về hàm Excel

REMEMBER THE FUTURE

• Toán tử • Hàm số • Địa chỉ • Thiết lập định dạng dấu phân cách và dấu thập phân; • Thiết lập định dạng ngày tháng; • Thiết lập cấp độ bảo mật • Các lỗi thường gặp khi dùng hàm

10/29/2016 3

Cơ bản về hàm Excel Toán tử

REMEMBER THE FUTURE

• Các toán tử toán học gồm: + , -, *, /, ^ (lũy thừa); • Các toán tử so sánh gồm: >, >=, <, =<, <>;

10/29/2016 4

Cơ bản về hàm Excel Hàm số

REMEMBER THE FUTURE

• Mọi công thức, hàm số trong Excel đều bắt đầu với

dấu bằng =

• Cấu trúc hàm Excel:

=([<Đối số 1>,<Đối số 2>,..])  do Excel cung cấp, nếu nhập sai sẽ báo lỗi #NAME!;  <Đối số 1>, <Đối số 2> có thể là tham chiếu đến ô, dãy ô, địa chỉ mảng, hay kết quả trả về của một công thức hoặc hàm khác;  Excel cho phép tối đa 30 đối số và tổng chiều dài của công thức tối đa là 255 ký tự;  Các đối số phải được đặt trong dấu ngoặc đơn ( );  Đối với những đối số kiểu ký tự, chuỗi nhập vào công thức thì phải được đặt trong dấu ngoặc kép " "; Dấu phân cách giữa các đối số là dấu phẩy (,) hoặc dấu chấm phẩy (;) tùy vào thiết lập trong hệ thống.

10/29/2016 5

Cơ bản về hàm Excel Địa chỉ

REMEMBER THE FUTURE

• Hàm số Excel sử dụng các địa chỉ ô để đại diện cho

các giá trị bên trong ô và gọi là tham chiếu;

• Địa chỉ ô có 3 loại:

 Địa chỉ tương đối .

Ví dụ: A10

 Địa chỉ tuyệt đối $$.

Ví dụ: $A$10

 Địa chỉ hỗn hợp $ hoặc $.

Ví dụ: $A10

10/29/2016 6

Cơ bản về hàm Excel Định dạng dấu phân cách/thập phân

REMEMBER THE FUTURE

• C1: Control Panel  Regional and Language Options  Customize…;

Quy định dấu thập phân

Quy định dấu phân cách số

Quy định dấu phân cách trong công thức

10/29/2016 7

Cơ bản về hàm Excel Định dạng dấu phân cách/thập phân

REMEMBER THE FUTURE

• C2: Tools  Options…

Bỏ chọn để sử dụng tùy chỉnh của Excel

Quy định dấu thập phân

Quy định dấu phân cách số

10/29/2016 8

Cơ bản về hàm Excel Định dạng ngày tháng

REMEMBER THE FUTURE

• Control Panel  Regional and Language Options  Customize…;

Định dạng ngày tháng: dd/MM/yyyy

Định dạng dấu phân cách ngày tháng: /

10/29/2016 9

Cơ bản về hàm Excel Thiết lập cấp độ bảo mật

REMEMBER THE FUTURE

• Tools  Macro 

Security…

Thiết lập chế độ bảo mật ở mức Trung bình: Excel sẽ hỏi có cho phép chạy macro hay không khi mở file

10/29/2016 10

Cơ bản về hàm Excel Các lỗi thường gặp

REMEMBER THE FUTURE

Một số lỗi thường gặp khi dùng hàm Excel:

Lỗi

Giải thích

##### Lỗi độ rộng hoặc khi nhập ngày tháng hoặc thời gian là số âm

#DIV/0!

Trong công thức có chứa phép chia cho 0 (zero) hoặc chia ô rỗng

#NAME? Do dánh sai tên hàm hay tham chiếu hoặc đánh thiếu dấu nháy

#N/A

Hàm không có kết quả hoặc giá trị trả về không tương thích.

#NULL!

Lỗi dữ liệu rỗng (Hàm sử dụng dữ liệu giao nhau của 2 vùng mà 2 vùng này không có phần chung nên phần giao rỗng)

#NUM!

Công thức chứa giá trị số không hợp lệ

#REF!

Tham chiếu bị lỗi, thường là do ô tham chiếu trong hàm bị xóa

#VALUE! Công thức tính toán có chứa kiểu dữ liệu không đúng

10/29/2016 11

Giới thiệu các hàm Excel căn bản

REMEMBER THE FUTURE

• Nhóm hàm Text

• Nhóm hàm Thời gian

• Nhóm hàm Logic

• Nhóm hàm Toán học

• Nhóm hàm Tham chiếu

• Nhóm hàm Thống kê

• Nhóm hàm Thông tin

10/29/2016 12

Nhóm hàm Text

REMEMBER THE FUTURE

Bao gồm các hàm xử lý chuỗi văn bản như trích lọc, tìm kiếm, thay thế, chuyển đổi chuỗi văn bản trong Excel.

Một số hàm tiêu biểu: • LEFT • RIGHT • TRIM • LEN • LOWER • UPPER • TEXT • VALUE

10/29/2016 13

Nhóm hàm Text Hàm Left

REMEMBER THE FUTURE

• Công dụng: Trích bên trái một chuỗi một hoặc nhiều ký tự dựa vào số ký tự mà bạn chỉ định. • Công thức: =LEFT(text,num_chars) text là chuỗi cần trích ký tự num_chars là ký tự mà bạn cần trích bên trái chuỗi text

Lưu ý!  num_chars không phải là số âm;  num_chars nếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text;  num_chars nếu bỏ qua thì mặc định là 1.

10/29/2016 14

Nhóm hàm Text Hàm Left (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 15

Nhóm hàm Text Hàm Right

REMEMBER THE FUTURE

• Công dụng: Trích bên phải một chuỗi văn bản một hoặc nhiều ký tự dựa vào số ký tự mà bạn chỉ định. • Công thức: =RIGHT(text,num_chars) text là chuỗi cần trích ký tự num_chars là ký tự mà bạn cần trích bên trái chuỗi text

10/29/2016 16

Nhóm hàm Text Hàm Right (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 17

Nhóm hàm Text Hàm Trim

REMEMBER THE FUTURE

• Công dụng: Xóa tất cả các ký tự trắng của chuỗi trừ những khoảng đơn dùng để làm khoảng cách bên trong chuỗi • Công thức: =TRIM(text)

Text: chuỗi cần xóa các ký tự trắng • Ví dụ:

10/29/2016 18

Nhóm hàm Text Hàm Len

REMEMBER THE FUTURE

• Công dụng: Tính độ dài (số ký tự) của một chuỗi. • Công thức: =LEN(text) text là nội dung cần xác định độ dài • Ví dụ:

10/29/2016 19

Nhóm hàm Text Hàm Lower

REMEMBER THE FUTURE

=LOWER(text)

• Công dụng: Đổi tất cả các ký tự trong chuỗi sang chữ thường. • Công thức: text: là chuỗi, hoặc tham chiếu đến chuỗi cần chuyển định dạng. • Ví dụ:

10/29/2016 20

Nhóm hàm Text Hàm Upper

REMEMBER THE FUTURE

• Công dụng: Chuyển tất cả các ký tự trong chuỗi thành ký tự hoa. • Công thức: =UPPER(text) Text: là chuỗi văn bản cần chuyển định dạng. • Ví dụ:

10/29/2016 21

Nhóm hàm Text Hàm Text

REMEMBER THE FUTURE

• Công dụng: Chuyển một giá trị số sang văn bản với kiểu định dạng số được chỉ định. • Công thức: =TEXT(value,format_text) value: là giá trị số, hoặc tham chiếu đến giá trị số cần chuyển đổi. format_text kiểu định dạng bạn muốn chuyển đổi. Có thể tham khảo các kiểu định dạng trong Format - Cells, thẻ Number, trong danh sách Category.

Lưu ý:  format_text không được có dấu *  kết quả của TEXT không được tính toán ở kiểu số nữa.

10/29/2016 22

Nhóm hàm Text Hàm Text (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

=”Giá của Monitor là “ & TEXT(B3;"#.##0,00") --> Giá của Monitor là 15.000.000,00

10/29/2016 23

Nhóm hàm Text Hàm Value

REMEMBER THE FUTURE

• Công dụng: Đổi chuỗi text đại diện cho một số thành dữ liệu kiểu số • Công thức: =VALUE(text)

text là chuỗi văn bản đại diện cho một số.

Lưu ý:  text là định dạng số, ngày tháng, hoặc thời gian bất kỳ được Microsoft Excel công nhận. Nếu không phải định dạng trên sẽ trả về lỗi #VALUE!

10/29/2016 24

Nhóm hàm Text Hàm Value (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 25

Nhóm hàm Thời gian

REMEMBER THE FUTURE

Bao gồm các hàm xử lý về ngày tháng, thời gian.

Một số hàm tiêu biểu: • TODAY • DAY • MONTH • YEAR • DAYS360 • WEEKDAY

10/29/2016 26

Nhóm hàm Thời gian Hàm Today

REMEMBER THE FUTURE

• Công dụng: Trả về ngày hiện tại trong hệ thống của bạn. Nếu định dạng ô là General trước khi hàm nhập công thức, kết quả trả về ở định dạng ngày tháng.

• Công thức: =TODAY()

Lưu ý:  Hàm NOW() trả về định dạng ngày tháng và thời gian hiện tại, Hàm TODAY() chỉ trả về ngày tháng hiện tại.

10/29/2016 27

Nhóm hàm Thời gian Hàm Day

REMEMBER THE FUTURE

• Công dụng: Trả về thứ tự của ngày từ chuỗi ngày tháng.

• Công thức: =DAY(serial_number)

serial_number dạng chuỗi số tuần tự của ngày cần tìm.

Lưu ý:  Ngày tháng này nên nhập bằng hàm DATE hoặc kết quả trả về từ hàm khác.  Có thể bị lỗi nếu bạn nhập serial_number là một chuỗi dạng văn bản.

10/29/2016 28

Nhóm hàm Thời gian Hàm Day (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 29

Nhóm hàm Thời gian Hàm Month

REMEMBER THE FUTURE

• Công dụng: Trả về thứ tự của tháng từ giá trị kiểu ngày tháng. • Công thức: =MONTH(serial_number) serial_number dạng chuỗi số tuần tự của tháng cần tìm.

Lưu ý:  Ngày tháng này nên nhập bằng hàm DATE hoặc kết quả trả về từ hàm khác;  Có thể bị lỗi nếu bạn nhập serial_number là một chuỗi dạng văn bản.

10/29/2016 30

Nhóm hàm Thời gian Hàm Month (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 31

Nhóm hàm Thời gian Hàm Year

REMEMBER THE FUTURE

• Công dụng: Trả về năm của một giá trị hoặc chuỗi đại diện cho ngày tháng. • Công thức: =YEAR(serial_number) serial_number dạng chuỗi hoặc số thập phân đại diện ngày tháng mà bạn cần tìm số năm của nó

10/29/2016 32

Nhóm hàm Thời gian Hàm Year (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 33

Nhóm hàm Thời gian Hàm Days360

REMEMBER THE FUTURE

• Công dụng:Trả về số ngày giữa 2 giá trị ngày tháng dựa trên cơ sở một năm có 360 ngày (12 tháng, mỗi tháng 30 ngày). • Công thức: =DAYS360(start_date,end_date,method) • Ví dụ:

10/29/2016 34

Nhóm hàm Thời gian Hàm Days360 (tiếp)

REMEMBER THE FUTURE

Lưu ý:  start_date, end_date là mốc bắt đầu và kết thúc để tính số ngày. Nếu start_date lớn hơn end_date hàm sẽ trả về số âm. start_date và end_date nên nhập bằng hàm DATE hoặc kết quả trả về từ công thức hoặc hàm khác.

 method giá trị logic xác định phương thức tính toán: FALSE (mặc định)

dùng phương pháp Mỹ (NASD); TRUE dùng phương pháp Châu Âu.

 Phương pháp Mỹ: Nếu start_date là 31 thì nó được chuyển thành ngày 30 của tháng đó. Nếu end_date là ngày 31 đồng thời start_date là ngày trước ngày 30 của tháng cùng tháng với end_date thì end_date được chuyển thành ngày 1 của tháng kế tiếp, ngược lại end_date được chuyển thành ngày 30 của tháng đó.

 Phương pháp Châu Âu: Nếu start_date và end_date rơi vào ngày 31

thì nó được chuyển thành ngày 30 của tháng đó.

Nhóm hàm Thời gian Hàm Weekday

REMEMBER THE FUTURE

• Công dụng: Trả về thứ tự của ngày trong tuần của định dạng ngày tháng chỉ ra. • Công thức: =WEEKDAY(serial_number,return_type) serial_number là chuỗi số đại diện cho ngày tháng cần tìm. return_type: Xác định kiểu giá trị gửi về. Cụ thể:  return_type=1 (hoặc không nhập) hàm WEEKDAY trả về 1 là Chủ nhật, 7 là thứ Bảy.  return_type = 2: WEEKDAY trả về 1 là Thứ 2, 7 là Chủ nhật.  return_type = 3: WEEKDAY trả về 0 là Thứ 2, 6 là Chủ nhật.

• Ví dụ:

10/29/2016 36

Nhóm hàm Logic

REMEMBER THE FUTURE

Các hàm logic luôn trả về một trong 2 giá trị TRUE (đúng) hoặc FALSE (sai). Kết quả của hàm logic thường được dùng làm đối số trong các hàm có sử dụng điều kiện như IF, SUMIF, COUNTIF…

Một số hàm tiêu biểu là: • AND • OR • IF

10/29/2016 37

Nhóm hàm Logic Hàm And

REMEMBER THE FUTURE

=AND(logical_1,logical_2,...)

• Công dụng: Trả về kết quả TRUE nếu tất cả điều kiện đều TRUE, Trả về FALSE nếu một trong các điều kiện FALSE. • Công thức: logical_1, logical_2 là các điều kiện cần kiểm tra

Lưu ý:  Các điều kiện có thể là biểu thức, vùng tham chiếu hoặc mảng giá trị;  Các điều kiện phải có giá trị là TRUE hoặc FALSE;  Nếu 1 trong các điều kiện có giá trị không phải Logic, hàm AND trả về lỗi #VALUE! .

10/29/2016 38

Nhóm hàm Logic Hàm And (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 39

Nhóm hàm Logic Hàm Or

REMEMBER THE FUTURE

=OR(logical_1,logical_2,...)

• Công dụng: Trả về TRUE nếu một trong các điều kiện là TRUE. Trả về FALSE nếu tất cả các điều kiện là FALSE. • Công thức: logical_1, logical_2 là các điều kiện cần kiểm tra

Lưu ý:  Các điều kiện có thể là biểu thức, vùng tham chiếu hoặc mảng giá trị.  Các điều kiện phải có giá trị là TRUE hoặc FALSE.  Nếu 1 trong các điều kiện có giá trị không phải Logic, hàm OR trả về lỗi #VALUE!

10/29/2016 40

Nhóm hàm Logic Hàm Or (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 41

Nhóm hàm Logic Hàm If

REMEMBER THE FUTURE

• Công dụng: Trả về một giá trị nếu điều kiện là đúng, trả về một giá trị khác nếu điều kiện là sai • Công thức:

=IF(logical_test,value_if_true,value_if_false) logical_test: điều kiện để xét, logical có thể là kết quả của một hàm luận lý như AND, OR,... value_if_false: giá trị trả về nếu value_if_true: giá trị trả về điều kiện logical_test là FALSE. Lưu ý: Có thể có 7 hàm IF được lồng vào nhau để tạo nên công thức phức tạp hơn. Các điều kiện phải có giá trị là TRUE hoặc FALSE. Nếu điều kiện có giá trị không phải Logic, hàm IF trả về lỗi #VALUE

10/29/2016 42

Nhóm hàm Logic Hàm If (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 43

Nhóm hàm Toán Học

REMEMBER THE FUTURE

Bao gồm các hàm về toán học, làm tròn, cộng có điều kiện và hàm lượng giác

Một số hàm thường dùng: • INT • ROUND • RAND • SUM • SUMIF • SUBTOTAL • SUMPRODUCT

10/29/2016 44

Nhóm hàm Toán học Hàm Int

REMEMBER THE FUTURE

• Công dụng: Làm tròn một số xuống số nguyên gần nhất. • Công thức: =INT(number) number: Số cần làm tròn

• Ví dụ:

10/29/2016 45

Nhóm hàm Toán học Hàm Round

REMEMBER THE FUTURE

• Công dụng: Làm tròn một số theo yêu cầu • Công thức: =ROUND(number,num_digits) number số muốn làm tròn. num_digits số ký số muốn làm tròn

• Ví dụ:

10/29/2016 46

Nhóm hàm Toán học Hàm Rand

REMEMBER THE FUTURE

• Công dụng: Trả về một số ngẫu nhiên lớn hơn hoặc bằng 0 và nhỏ hơn 1. • Công thức: =RAND( ) Lưu ý: Hàm Rand() trả về một số mới khi trang bảng tính được tính toán lại. Để tạo một số ngẫu nhiên trong khoảng giá trị a đến b dùng công thức sau:=RAND()*(b-a)+a • Ví dụ:

10/29/2016 47

Nhóm hàm Toán học Hàm Sum

REMEMBER THE FUTURE

• Công dụng: Tính tổng tất cả các số trong dãy số. • Công thức: =SUM(number1,number2,....) Lưu ý:  Trong Excel 2003 trở về trước, hàm Sum chỉ nhận tối đa 30 đối số number1, number2… • Ví dụ:

10/29/2016 48

Nhóm hàm Toán học Hàm Sumif

REMEMBER THE FUTURE

• Công dụng: Tính tổng tất cả các số trong dãy ô thỏa mãn điều kiện cho trước. • Công thức: =SUMIF(range,criteria,sum_range) range dãy các ô để tính tổng, có thể là ô chứa số, tên, mảng, hay tham chiếu đến các ô chứa số. criteria điều kiện để tính tổng có thể là số, biểu thức, hoặc kiểu văn bản. Ví dụ: 10,">=10","CPU“. sum_range là vùng thực sự để tính tổng. Nếu bỏ qua, Excel sẽ coi như sum_range = range Lưu ý:  Có thể dùng các ký tự đại diện trong điều kiện: dấu ? đại diện cho một ký tự, dấu * đại diện cho nhiều ký tự (nếu như điều kiện là tìm những dấu ? hoặc , thì gõ thêm dấu ~ ở trước dấu ? hay ).

49 10/29/2016

Nhóm hàm Toán học Hàm Sumif (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 50

Nhóm hàm Toán học Hàm Sumproduct

REMEMBER THE FUTURE

• Công dụng: Nhân các phần tử tương ứng trong các mảng với nhau và trả về tổng của chúng.

• Công thức: =SUMPRODUCT(array1,array2...) array1,array2,... có thể có từ 2 đến 30 dãy số cùng kích thước

Lưu ý:  Nếu các mảng giá trị không cùng kích thước hàm sẽ trả về lỗi #VALUE!  Một phần tử bất kỳ trong mảng không phải là số thì coi như bằng 0 (zero)

10/29/2016 51

Nhóm hàm Toán học Hàm Sumproduct (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 52

Nhóm hàm Toán học Hàm SubTotal

REMEMBER THE FUTURE

• Công dụng: Tính tổng phụ trong một danh sách hoặc CSDL. • Công thức: =SUBTOTAL(function_num,ref1,ref2,...) ref1, ref2,... có 1 đến 29 hay tham chiếu mà bạn muốn tính tổng phụ; function_num Các con số từ 1 đến 11 (hay 101 đến 111) qui định hàm nào sẽ được dùng để tính toán. Lưu ý:  Nếu có hàm SUBTOTAL khác đặt lồng trong các đối số ref1, ref2,… thì các hàm lồng này sẽ bị bỏ qua không được tính, nhằm tránh trường hợp tính toán 2 lần.  Hàm SUBTOTAL sẽ bỏ qua không tính toán tất cả các hàng bị ẩn lệnh Filter (Auto Filter) mà không phụ thuộc vào đối số bởi function_num được dùng (1 giống 101...). 10/29/2016

Nhóm hàm Toán học Hàm SubTotal (tiếp)

REMEMBER THE FUTURE

• Bảng tra các hàm sử dụng của SUBTOTAL trong Excel 2003/ 2007

10/29/2016 54

Nhóm hàm Toán học Hàm SubTotal (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 55

Nhóm hàm Tham chiếu

REMEMBER THE FUTURE

Bao gồm các hàm tìm kiếm và tham chiếu rất hữu ích khi làm việc với CSDL lớn trong EXCEL như kế toán, tính lương, thuế...

Một số hàm tiêu biểu là: • VLOOKUP • HLOOKUP • INDEX • MATCH

10/29/2016 56

Nhóm hàm Tham chiếu Hàm Vlookup

REMEMBER THE FUTURE

• Công dụng: Dò tìm một giá trị ở cột đầu tiên bên trái của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên dòng với giá trị tìm thấy và trên cột chỉ định. • Công thức: =VLOOKUP(lookup_value,table_array,col_index,range_lookup) lookup_value là giá trị dùng để tìm kiếm, nó có thể là một giá trị, một tham chiếu hay một chuỗi ký tự. table_array là vùng chứa dữ liệu cần tìm. col_index là số thứ tự cột trên vùng tìm kiếm cần lấy dữ liệu range_lookup là giá trị logic chỉ định VLOOKUP tìm kiếm chính xác hay là tương đối. Nếu là TRUE hàm sẽ trả về kết quả tìm kiếm tương đối tức là nếu không tìm thấy kết quả chính xác, nó sẽ trả về một giá trị lớn nhất mà nhỏ hơn giá trị tìm kiếm lookup_value. Nếu là FALSE hàm sẽ tìm kiếm chính xác, nếu không có trả về lỗi #N/A!

10/29/2016 57

Nhóm hàm Tham chiếu Hàm Vlookup (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 58

Nhóm hàm Tham chiếu Hàm Hlookup

REMEMBER THE FUTURE

• Công dụng: Dò tìm một giá trị ở dòng đầu tiên của một bảng dữ liệu. Nếu tìm thấy sẽ trả về giá trị ở cùng trên cột với giá trị tìm thấy và trên hàng chỉ định.

• Công thức: =HLOOKUP(lookup_value,table_array,row_index,range_lookup)

lookup_value là giá trị dùng để tìm kiếm, nó có thể là một giá trị, một tham chiếu hay một chuỗi ký tự. table_array là vùng chứa dữ liệu cần tìm. row_index là số thứ tự dòng trên vùng tìm kiếm cần lấy dữ liệu range_lookup là giá trị logic chỉ định HLOOKUP tìm kiếm chính xác hay là tương đối. Nếu là TRUE hàm sẽ trả về kết quả tìm kiếm tương đối tức là nếu không tìm thấy kết quả chính xác, nó sẽ trả về một giá trị lớn nhất mà nhỏ hơn giá trị tìm kiếm lookup_value. Nếu là FALSE hàm tìm kiếm chính xác, nếu không có trả về lỗi #N/A!

10/29/2016 59

Nhóm hàm Tham chiếu Hàm Hlookup (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 60

Nhóm hàm Tham chiếu Hàm Index

REMEMBER THE FUTURE

• Công dụng: Trả về một giá trị hoặc một tham chiếu đến một giá trị nằm bên trong một bảng hoặc một mảng dựa vào chỉ số dòng và cột. • Công thức: (Dạng mảng)

=INDEX(array,row_num,column_num)

array là một dãy ô hoặc mảng hằng. row_num là số chỉ dòng của giá trị trong mảng cần trả về column_num là số chỉ cột của giá trị trong mảng cần trả về. Lưu ý:  Nếu mảng chỉ có một dòng hoặc một cột, thì row_num hay column_num tương ứng là tùy chọn;  Nếu hai đối số col_num và row_num đều được sử dụng, INDEX() sẽ trả về giá trị của ô là giao điểm của col_num và row_num.

10/29/2016 61

Nhóm hàm Tham chiếu Hàm Index

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 62

Nhóm hàm Tham chiếu Hàm Match

REMEMBER THE FUTURE

• Công dụng: Trả về vị trí (chỉ mục) của một giá trị từ một dãy giá trị • Công thức: =MATCH(lookup_value, lookup_array,match_type) lookup_value là giá trị cần tìm trong bảng giá trị; lookup_array là một dãy ô liên tục để tìm kiếm giá trị; match_type là một số -1, 0 hoặc 1 chỉ định kiểu tìm kiếm. = 1 (hoặc không nhập), hàm sẽ trả về vị trí của phần tử lớn nhất mà nhỏ hơn hoặc bằng giá trị cần tìm lookup_value (dãy giá trị lookup_array phải được sắp xếp theo thứ tự tăng dần). = 0, tìm vị trí chính xác của phần tử lookup_value. Dãy giá trị lookup_array không cần sắp xếp. = -1, hàm sẽ trả về vị trí của phần tử nhỏ nhất mà lớn hơn hoặc bằng giá trị cần tìm lookup_value (dãy giá trị lookup_array phải được sắp xếp theo thứ tự giảm dần). 10/29/2016

Nhóm hàm Tham chiếu Hàm Match

REMEMBER THE FUTURE

Lưu ý:  Nếu không tìm thấy giá trị cần tìm trong bảng giá trị, MATCH trả về lỗi #N/A.  Nếu match_type = 0, và giá trị cần tìm là ký tự, thì có thể dùng dấu sao (*) để đại diện cho nhiều ký tự, dùng dấu hỏi (?) để đại diện cho 1 ký tự tại vị trí đặt. • Ví dụ:

10/29/2016 64

Nhóm hàm Thống kê

REMEMBER THE FUTURE

Là nhóm hàm giúp bạn giải quyết các bài toán thống kê từ đơn giản đến phức tạp.

Nhóm hàm thống kê gồm các hàm cơ bản sau:

• AVERAGE • COUNT • COUNTA • COUNTIF

• MAX • MIN • RANK • SMALL • LARGE

10/29/2016 65

Nhóm hàm Thống kê Hàm Average

REMEMBER THE FUTURE

• Công dụng: Tính trung bình cộng của các đối số. • Công thức: =AVERAGE(number1,number2,...) number1,number2 có thể có từ 1 đến 30 số

Lưu ý:  Các đối số có thể là số, tên, mảng hoặc vùng tham chiếu;  Nếu đối số là một mảng hay là một tham chiếu có chứa text, giá trị logic, ô rỗng, các giá trị lỗi, v.v... thì các giá trị đó sẽ được bỏ qua; tuy nhiên, các ô chứa giá trị là zero (0) thì vẫn được tính.

10/29/2016 66

Nhóm hàm Thống kê Hàm Average (tiếp)

REMEMBER THE FUTURE

• Ví dụ: •

10/29/2016 67

Nhóm hàm Thống kê Hàm Count

REMEMBER THE FUTURE

• Công dụng: Đếm số ô dữ liệu kiểu số trong vùng tham chiếu. • Công thức: =COUNT(value1,value2,...) value1,value2,... có thể có từ 1 đến 30 vùng giá trị mà bạn muốn đếm số ô chứa dữ liệu kiểu số. Lưu ý:  Các tham số có thể là số, ngày tháng hoặc chuỗi đại diện cho số đều được tính. Giá trị lỗi hoặc chuỗi không thể chuyển đổi sang số được bỏ qua.  Nếu các đối số là một mảng hoặc vùng tham chiếu thì những ô trống, chứa giá trị logic, chuỗi hay giá trị lỗi đều được bỏ qua.

10/29/2016 68

Nhóm hàm Thống kê Hàm Count (tiếp)

REMEMBER THE FUTURE

• Ví dụ: •

10/29/2016 69

Nhóm hàm Thống Kê Hàm Counta

REMEMBER THE FUTURE

=COUNTA(value1,value2,...)

• Công dụng: Đếm số ô có dữ liệu (không phải là ô rỗng) trong vùng tham chiếu. • Công thức: value1,value2,...có thể có từ 1 đến 30 vùng giá trị mà bạn muốn đếm

Lưu ý: Để đếm số ô chứa dữ liệu kiểu số dùng hàm COUNT(), để đếm ô rỗng dùng hàm COUNTBLANK().

10/29/2016 70

Nhóm hàm Thống kê Hàm Counta (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 71

Nhóm hàm Thống kê Hàm Countif

REMEMBER THE FUTURE

• Công dụng: Đếm số lượng các ô trong một vùng thỏa một điều kiện cho trước. • Công thức: =COUNTIF(range,criteria) range là dãy các ô để đếm, có thể là ô chứa số, text, tên, mảng hay tham chiếu đến các ô chứa số. Ô rỗng sẽ được bỏ qua criteria là điều kiện để đếm. Có thể ở dạng số, biểu thức, hoặc text. Ví dụ, criteria có thể là 32, "32", "> 32", hoặc "apple", v.v...

10/29/2016 72

Nhóm hàm Thống kê Hàm Countif (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 73

Nhóm hàm Thống kê Hàm Max

REMEMBER THE FUTURE

• Công dụng: Trả về giá trị lớn nhất của dãy số. • Công thức: =MAX(number1,number2,...) number1,number2,... có thể có từ 1 đến 30 số mà bạn muốn tìm số lớn nhất Lưu ý: Các tham số có thể là số, ô rỗng, giá trị logic hoặc chuỗi đại diện cho giá trị số. Nếu là giá trị chuỗi không thể đổi thành số thì hàm trả về lỗi.  Nếu các đối số là một mảng hoặc vùng tham chiếu thì những ô trống, chứa giá trị logic, chuỗi hay giá trị lỗi đều được bỏ qua.  Nếu các đối số không phải là số, hàm trả về 0.

10/29/2016 74

Nhóm hàm Thống kê Hàm Max (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 75

Nhóm hàm Thống kê Hàm Min

REMEMBER THE FUTURE

• Công dụng:Trả về giá trị nhỏ nhất của dãy số. • Công thức: =MIN(number1,number2,...) number1,number2,... có thể có từ 1 đến 30 số muốn tìm số lớn nhất Lưu ý: Các tham số có thể là số, ô rỗng, giá trị logic hoặc chuỗi đại diện cho giá trị số. Nếu chuỗi không thể đổi thành số thì hàm trả về lỗi.  Nếu các đối số là một mảng hoặc vùng tham chiếu thì những ô trống, chứa giá trị logic, chuỗi hay giá trị lỗi đều được bỏ qua.  Nếu các đối số không phải là số, hàm trả về 0.

10/29/2016 76

Nhóm hàm Thống kê Hàm Min (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 77

Nhóm hàm Thống kê Hàm Rank

REMEMBER THE FUTURE

• Công dụng: Tìm vị thứ của một số trong dãy số. • Công thức: =RANK(number,ref,order) number giá trị mà bạn cần tìm vị thứ ref là mảng hoặc vùng tham chiếu đến một danh sách giá trị kiểu số. Những giá trị không phải là số được bỏ qua. order: phương thức sắp xếp.  order = 0 hoặc bỏ qua thì sắp xếp giảm dần tức là số lớn nhất có vị trí là 1.  order = 1 thì sắp xếp tăng dần tức là số nhỏ nhất có vị trí là 1.

10/29/2016 78

Nhóm hàm Thống kê Hàm Rank (tiếp)

REMEMBER THE FUTURE

Lưu ý: Nếu 2 số cùng vị thứ thì vị thứ tiếp theo được bỏ qua. Ví dụ: Có 2 giá trị ở vị thứ số 3 thì sẽ không có vị thứ số 4 mà chỉ có vị thứ số 5.  Khi áp dụng công thức với một danh sách thì trong công thức vùng tham chiếu ref phải là giá trị tuyệt đối để khi copy công thức cho các ô bên dưới cho kết quả đúng.  Đế tạo địa chỉ tuyệt đối trong công thức sau khi chọn vùng tham chiếu, nhấn phím F4 để tạo địa chỉ tuyệt đối nhằm cố định vùng tham chiếu trong công thức.

10/29/2016 79

Nhóm hàm Thống kê Hàm Rank (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 80

Nhóm hàm Thống kê Hàm Small

REMEMBER THE FUTURE

• Công dụng: tìm giá trị nhỏ thứ k trong vùng dữ liệu • Công thức: =SMALL(array,k) array: là vùng dữ liệu mà bạn muốn xác định vị trí nhỏ thứ k k: là vị trí (tính từ số nhỏ nhất) trong vùng dữ liệu Lưu ý:  Nếu array rỗng, SMALL() sẽ trả về giá trị lỗi #NUM!  Nếu k < 0 hay k lớn hơn số lượng các số có trong array, SMALL() sẽ trả về giá trị lỗi #NUM!  Giả sử n là số lượng các số có trong array, thì SMALL(array, 1) trả về giá trị nhỏ nhất (MIN), và SMALL(array, n) sẽ trả về giá trị lớn nhất (MAX).

10/29/2016 81

Nhóm hàm Thống kê Hàm Small (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 82

Nhóm hàm Thống kê Hàm Large

REMEMBER THE FUTURE

• Công dụng: tìm giá trị lớn thứ k trong vùng dữ liệu • Công thức: =LARGE(array,k) array: là vùng dữ liệu mà bạn muốn xác định vị trí lớn thứ k k: là vị trí (tính từ số lớn nhất) trong vùng dữ liệu Lưu ý:  Nếu array rỗng, LARGE() sẽ trả về giá trị lỗi #NUM!  Nếu k < 0 hay k lớn hơn số lượng các số có trong array, LARGE() sẽ trả về giá trị lỗi #NUM!  Giả sử n là số lượng các số có trong array, thì LARGE(array, 1) trả về giá trị lớn nhất (MAX), và LARGE(array, n) sẽ trả về giá trị nhỏ nhất (MIN).

10/29/2016 83

Nhóm hàm Thống kê Hàm Large (tiếp)

REMEMBER THE FUTURE

• Ví dụ:

10/29/2016 84

Nhóm hàm Thông tin

REMEMBER THE FUTURE

Bao gồm các hàm kiểm tra kiểu dữ liệu và lấy một số thông tin trong bảng tính.

Một số hàm tiêu biểu: • ISBLANK • ISERROR

10/29/2016 85

Nhóm hàm Thông tin Hàm IsBlank

REMEMBER THE FUTURE

• Công dụng: Kiểm tra tham chiếu rỗng. Trả về giá trị logic là TRUE nếu đúng và FALSE nếu sai. • Công thức: =ISBLANK(value) valuegiá trị cần kiểm tra. • Ví dụ:

10/29/2016 86

Nhóm hàm Thông tin Hàm IsError

REMEMBER THE FUTURE

• Công dụng: Kiểm tra có phải là một lỗi. Trả về giá trị logic là TRUE nếu đúng, và FALSE nếu sai. • Công thức: =ISERROR(value) valuegiá trị cần kiểm tra • Ví dụ:

10/29/2016 87

Giới thiệu Name trong Excel

REMEMBER THE FUTURE

• Định nghĩa: Name là tên được gán cho một ô hoặc một dãy ô (range) • Công dụng: được sử dụng để thay thế cho các tọa độ dãy VD: = TenHam(range_Name) • Lợi ích:

Các tên dễ nhớ hơn các tọa độ dãy. Các tên không thay đổi khi bạn di chuyển một dãy sang một chỗ khác trong bảng tính. Các dãy được đặt tên tự động điều chỉnh bất cứ khi nào bạn chèn hoặc xóa các hàng, hoặc các cột trong dãy. Các tên làm cho dễ định hướng một bảng tính hơn. Bạn có thể sử dụng lệnh GoTo để nhảy nhanh đến một dãy đã được đặt tên.

10/29/2016 88

Giới thiệu Name trong Excel

REMEMBER THE FUTURE

• Một số lưu ý khi đặt tên:

Tên chỉ có thể có tối đa 255 ký tự. Tên phải bắt đầu với một chữ cái hoặc ký tự gạch dưới (_). Đối với phần còn lại của tên, bạn có thể sử dụng bất kỳ ký tự nào (chữ, số, symbol) ngoại trừ khoảng trắng. Không sử dụng các địa chỉ ô (chẳng hạn như Q1) hay bất kỳ ký hiệu toán tử nào (+, -, *, /, <, >, và &) bởi vì những ký hiệu này có thể gây rắc rối cho bạn, nhất là khi sử dụng tên dãy trong công thức. Không sử dụng các tên có sẵn của Excel. Những tên đó là: Auto_Activate, Auto_Close, Auto_Deactivate, Auto_Open, Consolidate_Area, Criteria, Data_Form, Database, Extract, FilterDatabase, Print_Area, Print_Titles, Recorder, và Sheet_Title.

10/29/2016 89

Giới thiệu Name trong Excel

REMEMBER THE FUTURE

• Cách đặt tên:

 C1: Sử dụng hộp Name box  C2: Sử dụng hộp thoại Define Name…

10/29/2016 90

Lọc dữ liệu với Auto Filter

REMEMBER THE FUTURE

• B1: Chọn vùng dữ liệu cần lọc • B2: Data  Filter  Auto Filter • B3: Sử dụng các điều kiện lọc để tìm kiếm thông tin

Điều kiện lọc

10/29/2016 91

Lọc dữ liệu với Auto Filter

REMEMBER THE FUTURE

Demo một số ví dụ: • Lọc các giá trị lớn nhất hoặc nhỏ nhất • Lọc các giá trị chứa điều kiện lọc là text (CY: điều kiện

lọc có thể chứa cả ký tự đại diện *, ?,~)

• Lọc các giá trị bắt đầu hoặc kết thúc bằng điều kiện lọc • Lọc các ô trống (Blank) hoặc ô có dữ liệu (NonBlank) • Lọc các giá trị số lớn hơn hay nhỏ hơn điều kiện lọc • Lọc các giá trị bằng hoặc không bằng điều kiện lọc • Sắp xếp dữ liệu tăng dần hoặc giảm dần

10/29/2016 92