Ứng dụng Excel trong kiểm toán BCTC - Phạm Anh Tuấn

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 REMEMBER THE FUTURE Các nội dung chính • 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 210/29/2016 REMEMBER THE FUTURE Cơ bản về hàm Excel • 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 310/29/2016 REMEMBER THE FUTURE Cơ bản về hàm Excel Toán tử • 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: >, >=, ; 410/29/2016 REMEMBER THE FUTURE Cơ bản về hàm Excel Hàm số • Mọi công thức, hàm số trong Excel đều bắt đầu với dấu bằng = 510/29/2016 • Cấu trúc hàm Excel: =([,,..])  do Excel cung cấp, nếu nhập sai sẽ báo lỗi #NAME!;  , 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. REMEMBER THE FUTURE Cơ bản về hàm Excel Địa chỉ • 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; 610/29/2016 • Đị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 REMEMBER THE FUTURE Cơ bản về hàm Excel Định dạng dấu phân cách/thập phân • C1: Control Panel  Regional and Language Options  Customize; 710/29/2016 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 REMEMBER THE FUTURE Cơ bản về hàm Excel Định dạng dấu phân cách/thập phân • C2: Tools  Options 810/29/2016 Quy định dấu thập phân Quy định dấu phân cách số Bỏ chọn để sử dụng tùy chỉnh của Excel REMEMBER THE FUTURE Cơ bản về hàm Excel Định dạng ngày tháng 910/29/2016 • 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: / REMEMBER THE FUTURE Cơ bản về hàm Excel Thiết lập cấp độ bảo mật • Tools  Macro  Security 1010/29/2016 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 REMEMBER THE FUTURE Cơ bản về hàm Excel Các lỗi thường gặp 1110/29/2016 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 Một số lỗi thường gặp khi dùng hàm Excel: REMEMBER THE FUTURE Giới thiệu các hàm Excel căn bản • 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 1210/29/2016 REMEMBER THE FUTURE Nhóm hàm Text 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. 10/29/2016 13 Một số hàm tiêu biểu: • LEFT • RIGHT • TRIM • LEN • LOWER • UPPER • TEXT • VALUE REMEMBER THE FUTURE Nhóm hàm Text Hàm Left • 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. 10/29/2016 14 • 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. REMEMBER THE FUTURE Nhóm hàm Text Hàm Left (tiếp) • Ví dụ: 10/29/2016 15 REMEMBER THE FUTURE Nhóm hàm Text Hàm Right • 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. 10/29/2016 16 • 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 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. REMEMBER THE FUTURE Nhóm hàm Text Hàm Right (tiếp) • Ví dụ: 10/29/2016 17 REMEMBER THE FUTURE Nhóm hàm Text Hàm Trim • 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 10/29/2016 18 • Công thức: =TRIM(text) Text: chuỗi cần xóa các ký tự trắng • Ví dụ: REMEMBER THE FUTURE Nhóm hàm Text Hàm Len • Công dụng: Tính độ dài (số ký tự) của một chuỗi. 10/29/2016 19 • Công thức: =LEN(text) text là nội dung cần xác định độ dài • Ví dụ: REMEMBER THE FUTURE Nhóm hàm Text Hàm Lower • Công dụng: Đổi tất cả các ký tự trong chuỗi sang chữ thường. 10/29/2016 20 • Công thức: =LOWER(text) text: là chuỗi, hoặc tham chiếu đến chuỗi cần chuyển định dạng. • Ví dụ: REMEMBER THE FUTURE Nhóm hàm Text Hàm Upper • Công dụng: Chuyển tất cả các ký tự trong chuỗi thành ký tự hoa. 10/29/2016 21 • Công thức: =UPPER(text) Text: là chuỗi văn bản cần chuyển định dạng. • Ví dụ: REMEMBER THE FUTURE Nhóm hàm Text Hàm Text • 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. 10/29/2016 22 • 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. REMEMBER THE FUTURE Nhóm hàm Text Hàm Text (tiếp) • Ví dụ: 10/29/2016 23 =”Giá của Monitor là “ & TEXT(B3;"#.##0,00") --> Giá của Monitor là 15.000.000,00 REMEMBER THE FUTURE Nhóm hàm Text Hàm Value • Công dụng: Đổi chuỗi text đại diện cho một số thành dữ liệu kiểu số 10/29/2016 24 • 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! REMEMBER THE FUTURE Nhóm hàm Text Hàm Value (tiếp) • Ví dụ: 10/29/2016 25 REMEMBER THE FUTURE Nhóm hàm Thời gian 10/29/2016 26 Một số hàm tiêu biểu: • TODAY • DAY • MONTH • YEAR • DAYS360 • WEEKDAY Bao gồm các hàm xử lý về ngày tháng, thời gian. REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Today • 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. 10/29/2016 27 • 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. REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Day • Công dụng: Trả về thứ tự của ngày từ chuỗi ngày tháng. 10/29/2016 28 • 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. REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Day (tiếp) 10/29/2016 29 • Ví dụ: REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Month • Công dụng: Trả về thứ tự của tháng từ giá trị kiểu ngày tháng. 10/29/2016 30 • 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. REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Month (tiếp) • Ví dụ: 10/29/2016 31 REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Year • 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. 10/29/2016 32 • 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ó 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. REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Year (tiếp) • Ví dụ: 10/29/2016 33 REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Days360 • 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). 10/29/2016 34 • Công thức: =DAYS360(start_date,end_date,method) • Ví dụ: REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Days360 (tiếp) 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 đó. REMEMBER THE FUTURE Nhóm hàm Thời gian Hàm Weekday • 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. 10/29/2016 36 • 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ụ: REMEMBER THE FUTURE Nhóm hàm Logic 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 10/29/2016 37 Một số hàm tiêu biểu là: • AND • OR • IF REMEMBER THE FUTURE Nhóm hàm Logic Hàm And • 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. 10/29/2016 38 • Công thức: =AND(logical_1,logical_2,...) 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! . REMEMBER THE FUTURE Nhóm hàm Logic Hàm And (tiếp) • Ví dụ: 10/29/2016 39 REMEMBER THE FUTURE Nhóm hàm Logic Hàm Or • 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. 10/29/2016 40 • Công thức: =OR(logical_1,logical_2,...) 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! REMEMBER THE FUTURE Nhóm hàm Logic Hàm Or (tiếp) • Ví dụ: 10/29/2016 41 REMEMBER THE FUTURE Nhóm hàm Logic Hàm If • 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 10/29/2016 42 • 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 REMEMBER THE FUTURE Nhóm hàm Logic Hàm If (tiếp) • Ví dụ: 10/29/2016 43 REMEMBER THE FUTURE Nhóm hàm Toán Học 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 10/29/2016 44 Một số hàm thường dùng: • INT • ROUND • RAND • SUM • SUMIF • SUBTOTAL • SUMPRODUCT REMEMBER THE FUTURE Nhóm hàm Toán học Hàm Int • Công dụng: Làm tròn một số xuống số nguyên gần nhất. 10/29/2016 45 • Công thức: =INT(number) number: Số cần làm tròn • Ví dụ: REMEMBER THE FUTURE Nhóm hàm Toán học Hàm Round • Công dụng: Làm tròn một số theo yêu cầu 10/29/2016 46 number số muốn làm tròn. num_digits số ký số muốn làm tròn • Ví dụ: • Công thức: =ROUND(number, num_digits) REMEMBER THE FUTURE Nhóm hàm Toán học Hàm Rand • 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. 10/29/2016 47 • Công thức: =RAND( ) • Ví dụ: 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 REMEMBER THE FUTURE Nhóm hàm Toán học Hàm Sum • Công dụng: Tính tổng tất cả các số trong dãy số. 10/29/2016 48 • Công thức: =SUM(number1,number2,....) • Ví dụ: Lưu ý:  Trong Excel 2003 trở về trước, hàm Sum chỉ nhận tối đa 30 đối số number1, number2 REMEMBER THE FUTURE Nhóm hàm Toán học Hàm Sumif • 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. 10/29/201649 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 • Công thức: =SUMIF(range,criteria,sum_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 *). REMEMBER THE FUTURE Nhóm hàm Toán học Hàm Sumif (tiếp) • Ví dụ: 10/29/2016 50 REMEMBER THE FUTURE Nhóm hàm Toán học Hàm Sumproduct • 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. 10/29/2016 51 • 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) REMEMBER THE FUTURE Nhóm hàm Toán học Hàm Sumproduct (tiếp) • Ví dụ: 10/29/2016 52 REMEMBER THE FUTURE Nhóm hàm Toán học Hàm SubTotal • Công dụng: Tính tổng phụ trong một danh sách hoặc CSDL. 10/29/2016 53 • 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 bởi lệnh Filter (Auto Filter) mà không phụ thuộc vào đối số function_num được dùng (1 giống 101...). REMEMBER THE FUTURE Nhóm hàm Toán học Hàm SubTotal (tiếp) • Bảng tra các hàm sử dụng của SUBTOTAL trong Excel 2003/ 2007 10/29/2016 54 REMEMBER THE FUTURE Nhóm hàm Toán học Hàm SubTotal (tiếp) • Ví dụ: 10/29/2016 55 REMEMBER THE FUTURE Nhóm hàm Tham chiếu 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ế... 10/29/2016 56 Một số hàm tiêu biểu là: • VLOOKUP • HLOOKUP • INDEX • MATCH REMEMBER THE FUTURE Nhóm hàm Tham chiếu Hàm Vlookup • 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. 10/29/2016 57 • 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! REMEMBER THE FUTURE Nhóm hàm Tham chiếu Hàm Vlookup (tiếp) • Ví dụ: 10/29/2016 58 REMEMBER THE FUTURE Nhóm hàm Tham chiếu Hàm Hlookup • 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. 10/29/2016 59 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! • Công thức: =HLOOKUP(lookup_value,table_array,row_index,range_lookup) REMEMBER THE FUTURE Nhóm hàm Tham chiếu Hàm Hlookup (tiếp) • Ví dụ: 10/29/2016 60 REMEMBER THE FUTURE Nhóm hàm Tham chiếu Hàm Index • 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. 10/29/2016 61 • 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. REMEMBER THE FUTURE Nhóm hàm Tham chiếu Hàm Index • Ví dụ: 10/29/2016 62 REMEMBER THE FUTURE Nhóm hàm Tham chiếu Hàm Match 10/29/2016 63 • Công dụng: Trả về vị trí (chỉ mục) của một giá trị từ một dãy giá trị 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). • Công thức: =MATCH(lookup_value, lookup_array,match_type) REMEMBER THE FUTURE Nhóm hàm Tham chiếu Hàm Match