Giáo trình Thực hành kế toán excel (Ngành: Kế toán doanh nghiệp - Trung cấp) - Trường Cao đẳng nghề Ninh Thuận

Chia sẻ: _ _ | Ngày: | Loại File: PDF | Số trang:87

Thêm vào BST

Báo xấu

7
lượt xem 2
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Giáo trình "Thực hành kế toán excel (Ngành: Kế toán doanh nghiệp - Trung cấp)" được biên soạn với mục tiêu nhằm giúp sinh viên nắm được các kiến thức về: Các khái niệm cơ bản các hàm Excel; phân biệt được các hàm trong Excel. Mời các bạn cùng tham khảo!

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình Thực hành kế toán excel (Ngành: Kế toán doanh nghiệp - Trung cấp) - Trường Cao đẳng nghề Ninh Thuận

ỦY BAN NHÂN DÂN TỈNH NINH THUẬN TRƯỜNG CAO ĐẲNG NGHỀ NINH THUẬN --------- GIÁO TRÌNH MÔN HỌC/MÔ ĐUN: THỰC HÀNH KẾ TOÁN EXCEL NGHỀ: KẾ TOÁN DOANH NGHIỆP TRÌNH ĐỘ: TRUNG CẤP Ban hành theo Quyết định số: QĐ/CĐN ngày …tháng …năm của hiệu trưởng trường Cao đẳng nghề Ninh Thuận Ninh Thuận, năm 2019 0
TUYÊN BỐ BẢN QUYỀN Tài liệu này thuộc loại sách giáo trình nên các nguồn thông tin có thể được phép dùng nguyên bản hoặc trích dùng cho các mục đích về đào tạo và tham khảo. Mọi mục đích khác mang tính lệch lạc hoặc sử dụng với mục đích kinh doanh thiếu lành mạnh sẽ bị nghiêm cấm. 1
LỜI GIỚI THIỆU Môn học thực hành kế toán excel là một môn học chuyên ngành quan trọng trong chương trình đào tạo nghề kế toán doanh nghiệp. Đây là môn học cung cấp kiến thức cần thiết về các hàm excel và việc ứng dụng các hàm excel vào kế toán. Để phục vụ việc giảng dạy và học tập trong nhà trường và nhu cầu nghiên cứu của cán bộ kế toán các doanh nghiệp. Khoa Kinh Tế Tổng Hợp, trường cao đẳng Nghề Ninh Thuận đã triển khai biên soạn cuốn “Thực hành kế toán excel ”. Nội dung của giáo trình “Thực hành kế toán excel” được xây dựng trên cở sở kế thừa những nội dung đã được giảng dạy ở các cơ sở giáo dục nghề nghiệp kết hợp với các nội dung mới để đáp ứng được yêu cầu học nghề thực tế tại các địa phương, nhằm mang lại hiệu quả cao nhất cho người dạy và học trong quá trình đào tạo nghề. Giáo trình được biên soạn với nội dung ngắn gọn, rõ ràng, sát với thực tế và điều chỉnh theo đúng quy định nhà trường. Trong suốt quá trình xây dựng giáo trình “Thực hành kế toán excel” tôi đã nhận được sự giúp đỡ tận tình của bộ môn Kế Toán. Bên cạnh đó tôi xin bày tỏ lòng biết ơn chân thành đến Ban Giám Hiệu, Khoa Kinh Tế Tổng Hợp và các phòng ban đã hỗ trợ tôi trong thời gian qua. Chân thành cảm ơn! Ninh Thuận, ngày tháng năm 2019 Tham gia biên soạn Chủ biên Ngô Thị lựu 2
MỤC LỤC LỜI GIỚI THIỆU ......................................................................................................... 2 CHƯƠNG 1: LẬP TRANG TÍNH BẰNG EXCEL ...................................................... 6 1.Khái niệm chung.......................................................................................................6 2. Khái niệm về trang tính ........................................................................................... 6 3. Các thao tác trên trang tính ...................................................................................... 6 CHƯƠNG 2: CÁC HÀM THÔNG DỤNG TRONG EXCEL ................................ 11 1. Các hàm toán học .................................................................................................. 11 2. Các hàm logic ........................................................................................................ 12 3. Các hàm về thời gian ............................................................................................. 13 4. Các hàm khác ........................................................................................................ 14 CHƯƠNG 3: TỔ CHỨC DỮ LIỆU TRÊN EXCEL ............................................... 20 1. Khái niệm về cơ sở dữ liệu (Data Base) ................................................................ 20 2. Sắp xếp trên cơ sở dữ liệu ..................................................................................... 21 3. Các hàm trên cơ sở dữ liệu .................................................................................... 21 CHƯƠNG 4: ỨNG DỤNG EXCEL VÀO KẾ TOÁN DOANH NGHIỆP ............ 24 1.Kế toán excel theo hình thức nhật ký chung .......................................................... 24 2.Tổ chức dữ liệu kế toán .......................................................................................... 25 Tài liệu tham khảo.....................................................................................................................86 3
GIÁO TRÌNH MÔ ĐUN Tên mô đun: Thực hành kế toán Excel Mã mô đun: MĐ 25 Thời gian thực hiện mô đun: 60 giờ (Lý thuyết: 0 giờ; Thực hành/bài tập: 58 giờ; Kiểm tra: 2 giờ) 1. Vị trí, tính chất, ý nghĩa và vai trò của mô đun: - Vị trí: Mô đun Thực hành kế toán Excel thuộc nhóm các môn học/mô đun chuyên môn được bố trí giảng dạy sau khi đã học xong các môn học, mô đun chuyên môn của nghề. - Tính chất: Mô đun Thực hành kế toán Excel là mô đun sử dụng bảng tính Excel và ứng dụng vào thực hành công tác kế toán, giúp người học có kỹ năng trong việc xử lý dữ liệu kế toán. - Ý nghĩa và vai trò của mô đun: Mô đun này cung cấp các kiến thức về excel và ứng dụng excel vào công tác kế toán 2. Mục tiêu mô đun: - Về kiến thức: + Trình bày được các khái niệm cơ bản các hàm Excel; + Phân biệt được các hàm trong Excel. - Về kỹ năng: + Thực hành phần mềm Excel trên máy tính; + Ứng dụng được các phần mềm Excel vào công tác kế toán. - Về năng lực tự chủ và trách nhiệm: + Trung thực, cẩn thận, tuân thủ đúng chế độ kế toán hiện hành. 3. Nội dung mô đun Nội dung tổng quát và phân bổ thời gian Thời gian (giờ) Tổng Lý Thực Kiểm Số số thuyết hành, thí tra Tên chương, mục TT nghiệm, thảo luận, bài tập 1 Lập trang tính bằng Excel 5 5 4
2 Các hàm thông dụng của Excel 10 10 3 Tổ chức dữ liệu trên Excel 15 15 4 Ứng dụng Excel vào kế toán trong doanh 30 30 2 nghiệp Cộng 60 58 2 5
CHƯƠNG 1: LẬP TRANG TÍNH BẰNG EXCEL Mã tên chương: MĐ25-01 Mục tiêu của chương: - Trình bày được một số khái niệm cơ bản trong Excel; - Xác định được các định dạng dụng trong Excel; - Lập được các bảng biểu dữ liệu trên trang tính; - Nghiêm túc, cẩn thận. 1. Khái niệm chung Excel là trình ứng dụng bảng tính trong Windows, thuộc bộ công cụ văn phòng Microsoft Office (MsOffice). Excel là ứng dụng đa văn bản – nghĩa là có thể mở đồng thời nhiều hơn một cửa sổ văn bản. Các thao tác trong Excel tuân theo tiêu chuẩn của Windows, như: làm việc với cửa sổ, các hộp đối thoại, hệ thống menu, sử dụng mouse, các biểu tượng lệnh... Excel có thể được cài đặt một cách độc lập, nhưng thông thường là qua bộ cài đặt MsOffice. Đường dẫn đến chương trình EXCEL.EXE thường là ...Programs\Microsoft Office. 2. Khái niệm về trang tính - Cell: Đây là môt ô lưu trữ dữ liệu bên trong bảng tính và mỗi ô sẽ có một địa chỉ xác định. - Row: Là tập hợp các ô nằm trên một hàng, tổng số ô trong một hàng rất là lớn. Mỗi hàng có một địa chỉ xác định - Column: Là tập hợp các ô nằm trên một cột, số lượng ô trong một cột là không giới hạn. Mỗi cột sẽ có một địa chỉ xác định - Range: Là tập hợp nhiều ô liền nhau tạo thành một vùng giới hạn, và đương nhiên nó cũng có một địa chỉ xác định bởi địa chỉ của ô đầu tiên và địa chỉ của ô cuối cùng - Worksheets: Nó là tổng hợp những trang tính trong Excel. Mỗi file Excel có thể có nhiều trang tính và có sự liên kết dữ liệu với nhau, nghĩa là trang tính A có thể sử dụng dữ liệu ở trang tính B để đưa vào công thức. 6
3. Các thao tác trên trang tính - Chèn thêm Column vào bảng tính  Đặt con trỏ vào cột muốn chèn  Ra lệnh Insert ➔ Column - Chèn thêm Row vào bảng tính:  Đặt con trỏ vào dòng muốn chèn  Ra lệnh Insert ➔ Row - Chèn thêm sheet (bảng tính) vào workbook:  Ra lệnh Insert/ Work sheet - Đặt tên cho Worksheet:  Chọn sheet muốn đặt tên là sheet hiện hành  Format ➔ Rename (hoặc double click tại phần tên sheet) – gõ tên worksheet. - Dấu (hiện) Row, column, worksheet:  Chọn Row, column hay sheet muốn dấu đi  Ra lệnh format ➔ (Row, column hay sheet tương ứng)  Muốn dấu thì chọn Hide (hiện thì chọn Unhide) - Phép tham chiếu ô Phép tham chiếu dùng để chỉ các ô cụ thể theo sự phối hợp giữa hàng và cột, ví dụ ô A1 là giao điểm giữa cột A và hàng 1. Nếu muốn chuyển qua lại giữa tham chiếu tương đối, tham chiếu tuyệt đối và tham chiếu hỗn hợp chỉ cần nhấn phìm F4.  Phép tham chiếu ô tuyệt đối Ký hiệu đô la ($) được đặt trước một phối hợp hàng hoặc cột xác định rằng phép tham chiếu mang tính tuyệt đối và sẽ không thay đổi. Ví dụ: tham chiếu $A$1 được dùng trong một công thức sẽ luôn chỉ ô A1, dù cho công thức đặt ở đâu và có sao chép hay không.  Phép tham chiếu ô tương đối Tham chiếu tương đối không có ký hiệu $. Nó chỉ các ô thông qua sự định vị tương đối. Ví dụ: Nếu nhập =C8 vào ô D8, công thức này chỉ một ô ở phía trái, trên cùng hàng. Nếu công thức này được sao chép từ ô D8 đến ô D9 thì nó sẽ chỉ đến ô C9 –một ô ở phía trái, trên cùng hàng. 7
 Phép tham chiếu ô hỗn hợp Tham chiếu ô hỗn hợp chứa cả tham chiếu tương đối lẫn tham chiếu tuyệt đối. Nếu phần cột của tham chiếu là tuyệt đối thì cột sẽ không thay đổi, nhưng phần hàng là tương đối sẽ thay đổi. Ngược lại, có thể tham chiếu hỗn hợp với một cột thay đổi và hàng cố định. Ví dụ: $G4 là tham chiếu hỗn hợp có cột cố định và hàng thay đổi; G$4 là tham chiếu hỗn hợp có cột thay đổi và hàng cố định. - Đặt tên khối (Cell Range):  Chọn khối cần đặt tên bằng cách rê chuột từ ô đầu tiên đến ô cuối cùng của khối. (Cell range). Ra lệnh Insert ➔ Name ➔ Define  Nhập tên khối vào hộp Name In Work book: (lưu ý: tên khối không có khoảng trắng - chỉ có một từ)  Chọn ADD để khai báo - Áp fonts Timesnewroman cho toàn bảng tính:  Ra lệnh Tools ➔ Options… ➔ General  Chọn font Timesnewroman, size 12 trong khung Standard font:  Thoát Excel, sau đó khởi động lại. - Ý nghĩa của thông báo lỗi Khi Excel không thể ước định chính xác công thức, nó trả về một thông báo lỗi. Tất cả lỗi trong Exel bắt đầu bằng dấu (#). Một số báo lỗi (tuỳ theo dạng lỗi) hiển thị dưới dạng. 8
Tên lỗi Nguyên nhân Đây là lỗi divide – by – zero (chia cho 0), thông báo mẫu số bằng 0 do đó không thể thực hiện được phép tính. Exel mặc định các ô trắng có #DIV/0 giá trị bằng 0, cho nên lỗi này có thể là thực hiện phép chia cho một ô trống Lỗi này có ý nghĩa tuỳ theo công thức. Có lẽ công thức đã tham chiếu đến một giá trị không tồn tại, ví dụ, trong hàm Vlookup (tìm kiếm), #N/A cũng có thể được sử dụng như một ký tự gữi chỗ (palaceholder) #N/A khi dữ liệu chưa có sẵn. Đặc tính lập biểu đồ của Exel bỏ qua #N/A, vì vậy ký hiệu #N/A rất hữu dụng khi bạn vẽ biểu đồ. Nếu bạn để ô trống thì Exel sẽ cho rằng các ô trắng có giá trị là 0, và phản ánh sai lệch vào biểu đồ Lỗi này xảy ra khi Exel không thể nhận diện được tên sử dụng trong công thức. Tên này không tồn tại, hoặc đã bị xoá so sơ xuất, hoặc bị #NAME? đánh vần sai. Cũng có thể bạn quên đặt chuỗi văn bản trong dấu ngoặc kép. Nghĩa là có vấn đề với một số - số này không thể phiên dịch được vì #NUM! quá lớn hay quá bé, hoặc nó không tồn tại. Có lẽ bạn đã sử dụng một đối số không phù hợp trong hàm. Lỗi này cho biết có vấn đề với tham chiếu ô, và thường thì do việc xoá hàng hoặc cột – đã được sử dụng trong công thức. Lỗi này cũng có thể #REF! do phép tham chiếu từ xa đến một chương trình không chạy, chẳng hạn lỗi DDE. Lỗi giá trị do một số nguyên nhân, nhưng thường thì nó đi đôi với việc #VALUE! tính toán ở ký tự (thay vì số), hoặc nhập một đối số không phù hợp cho một hàm. Lỗi Null cho biết không tồn tại phép giao đối với các dãy ô trong công #NULL! thức (phần giao rỗng). 9
10
CHƯƠNG 2: CÁC HÀM THÔNG DỤNG TRONG EXCEL Mã tên chương: MĐ25-02 Mục tiêu của chương: - Trình bày cú pháp các hàm thông dụng trong Excel; - Sử dụng thành tạo các hàm thông dụng trong Excel; - Nghiêm túc, cẩn thận 1. Các hàm toán học - Hàm Count( )  Cú pháp: =Count(Gtrị 1, gtrị 2, gtrị 3,…). Trong đó các giá trị có thể là các địa chỉ ô chứa giá trị, một biểu thức, địa chỉ nhóm ô chứa giá trị kiểu số…  Công dụng: Hàm đếm tổng các giá trị kiểu số liệt kê trong hàm, hay đếm tổng các ô chứa giá trị kiểu số trong hàm.  Ví dụ: =Count(3, 5, 6, 2) ➔ Kết quả: 4(có 4 số) - Hàm Sum( )  Cú pháp: =Sum(gtrị 1, gtrị 2, gtrị 3…), trong đó các giá trị có thể là các địa chỉ ô chứa giá trị, một biểu thức, địa chỉ nhóm ô chứa giá trị kiểu số…  Công dụng: Hàm tính tổng các giá trị liệt kê trong hàm hay tính tổng giá trị các ô chứa giá trị trong hàm.  Ví dụ: =Sum(3, 5, 6, 2) ➔ kết quả: 16 - Hàm Round( )  Cú pháp: =Round(n,m), trong đó n là một số lẻ, địa chỉ ô chứa số lẻ: m là số nguyên hàng thứ m.  Công dụng: Nếu m âm thì làm tròn sang phân nguyên, nếu m dương thì hàm làm tròn sang phần thập phân.  Ví dụ: n=1234,5678 ➔ Round(n,2) = 1234,56; Round(n,-2) = 1200 - Hàm Max()  Cú pháp: =Max(n1, n2, n3…), trong đó n1, n2, n3… là các số, các địa chỉ ô chưa số, địa chỉ nhóm ô chứa số.  Công dụng: Hàm Max( ) trả về giá trị kiểu số là số lớn nhất trong dãy số n1, n2, n3…  Ví dụ: =Max(1, 3, 5, 2, 9) ➔ 9 11
- Hàm Min( )  Cú pháp: =Min(n1, n2, n3…), trong đó n1, n2, n3… là các số, các địa chỉ ô chưa số, địa chỉ nhóm ô chứa số.  Công dụng: Hàm Min( ) trả về giá trị kiểu số là số nhỏ nhất trong dãy số n1, n2, n3…  Ví dụ: =Min(1, 3, 5, 2, 9) ➔ 1 - Hàm Average( )  Cú pháp: =Average(n1, n2, n3…) trong đó n1, n2, n3… là các số, các địa chỉ ô chứa số, địa chỉ nhóm ô chưa số.  Công dụng: Hàm Average( ) trả về giá trị kiểu số là số bình quân cộng của dãy số n1, n2, n3,…  Ví dụ: =Average (1, 3, 5, 2, 7) ➔ 3,6; =Average(5, 6) ➔ 6 2. Các hàm logic - Hàm Left( )  Cú pháp: =Left(Str,n). Trong đó Str là một chuỗi văn bản hoặc một địa chỉ ô chứa giá trị chuỗi, còn n là số nguyên dương.  Công dụng: Hàm trả về giá trị là một chuỗi con được cắt từ bên trái Str sang n ký tự.  Ví dụ: =Left(“Excel”,2) ➔ trả về chuỗi con “Ex“ - Hàm Right( )  Cú pháp: =Right(Str,n). Trong đó Str là một chuỗi văn bản hoặc một địa chỉ ô chứa giá trị chuỗi, còn n là số nguyên dương.  Công dụng: Hàm trả về giá trị là một chuỗi con được cắt từ bên phải Str sang n ký tự.  Ví dụ: =Right (“Excel”,2) ➔ trả về chuỗi con “el” - Hàm Mid( )  Cú pháp: =Mid(Str, n, m). Trong đố Str là một chuỗi văn bẳn hoặc một địa chỉ ô chứa giá trị chuỗi, còn n và m là hai số nguyên dương.  Công dụng: Hàm trả về giá trị là một chuỗi con được cắt ở giữa Str bắt đầu từ ký tự n sang m ký tự. 12
 Ví dụ: =Mid(“Thực hành kế toán trên Excel”, 10, 7) ➔ trả về chuỗi “kế toán” - Hàm If( )  Cú pháp: =IF(TestValue, Value If True, Value If False). Trong đó TestValue là biểu thức điều kiện, Value If True và Value If False là các giá trị sẽ trả về của hàm (hoặc địa chỉ chỉ ô chứa giá trị).  Công dụng: Khi thực hiện hàm sẽ tính toán và xét biểu thức điều kiện TestValue, nếu đúng thì hàm trả về giá trị Value If True, nếu sai hàm trả về giá trị Value If False.  Ví dụ: =IF(4>6,4,6) ➔ Hàm trả về số 6. - Hàm Or( )  Cú Pháp: =Or(Btđk1, Btđk2, Btđk3,…). Trong đó: Btđk1, Btđk2,… là các biểu thức điều kiện trả về giá trị kiểu Logical.  Công dụng: Hàm trả về giá trị kiểu Logical, bằng True khi một trong các biểu thức điều kiện trong hàm đúng. Bằng False khi tất cả các biểu thức điều kiện đều sai.  Ví dụ: =Or(2>3, 4>3, 5>7) ➔ Hàm trả về giá trị True =Or(2>3, 47) ➔ Hàm trả về giá trị False - Hàm And( )  Cú pháp: =And(Btđk1, Btđk2, Btđk3,…) trong đó: Btđk1, Btđk2,… là các biểu thức điều kiện trả về giá trị kiểu Logical.  Công dụng: Hàm trả về giá trị kiểu Logical, bằng False khi chỉ cần một trong các biểu thức điều kiện trong hàm sai. Bằng True khi tất cả các biểu thức điều kiện đều đúng.  Ví dụ: =And(2>3, 4>3, 5>7) ➔ Hàm trả về giá trị False 3. Các hàm về thời gian - Hàm Day  Cú pháp: DAY(dãy số kiểu ngày) Hàm Day trả lại giá trị ngày (từ 1 đến 31) từ dãy số kiểu ngày. 13
Dãy số kiểu ngày có thể là một chuỗi ngày (“03/01/98”) hoặc tham chiếu đến ô chứa giá trị ngày.  Ví dụ: Day(“07/21/98”) = 21 (ngày 21 tháng 7 – dạng mm/dd/yy) - Hàm Month  Cú pháp: MONTH(dãy số kiểu ngày) Hàm Month trả lại giá trị tháng (từ 1 đến 12) từ dãy số kiểu ngày.  Ví dụ: Month(“07/21/98”) = 7 (ngày 21 tháng 7 – dạng mm/dd/yy) - Hàm Year  Cú pháp: YEAR(dãy số kiểu ngày) Hàm Year trả lại giá trị năm (từ 1900 đến năm lớn nhất có thể) từ dãy số kiểu ngày.  Ví dụ: Year(“07/21/98”) = 1998 (năm nhập 2 chữ số được xem như 19xx) - Hàm Now  Cú pháp: NOW() Hàm Now không có đối số, trả lại giá trị là một dãy số gồm ngày, tháng, năm và giờ hiện tại đang được lưu trong đồng hồ của máy tính.  Ví dụ: Year(Now()) trả lại năm hiện tại (là 2005) Giả sử ô C7 chứa ngày sinh, khi đó công thức: Year(Now()) - Year(C7) sẽ cho giá trị là tuổi của đối tượng. - Các hàm về thời gian  Hour ® trả lại giá trị giờ (1 – 24)  Minute ® trả lại giá trị phút (1 – 60)  Second ® trả lại giá trị giây (1 – 60) 4. Các hàm khác - Hàm Vlookup( )  Cú pháp: Vlookup(LookupValue, Table Array, ColunmIndexNumber, RangeLookup) Trong đó: − LookupValue là một giá trị (hoặc địa chỉ ô chứa giá trị) mà hàm sẽ mang đi dò tìm trong cột đầu tiên của bảng TableArray. 14
− TableArray: là bảng chứa giá trị dò tìm và giá trị lấy ra của hàm. Giá trị dò tìm phải ở cột đầu tiên của bảng, giá trị lấy ra của hàm phải ở các cột từ thứ hai trở đi. − ColunmIdexNumber: là chỉ số cột được chỉ định chứa giá trị trả về trong bảng(chỉ số cột phải >=2, vì giá trị trả về ở các cột từ số 2 trở đi trong bảng). − RangeLookup: là True(), hoặc False(). Nếu là 0 thì việc dò tìm của hàm phải chính xác, nếu là 1 thì việc dò tìm một giá trị trong bảng không cần chính xác chỉ cần gần đúng là được.  Công dụng: Nếu tìm thấy giá trị LookupValue ở cột đầu tiên của bảng thì hàm trả về giá trị của một ô trong bảng TableArray ứng với dòng chứa giá trị dò tìm và cột ColunmIndexNumber, ngược lại hàm trả về giá trị #NA# (không tìm thấy). Ta có thể biểu diễn lại cú pháp hàm dưới dạng bảng như sau: Mã TK Tên TK …. 111 TM =VLOOKUP( “111”, 112 TG NH ,2 ,0) 113 TĐC 152 HTK Với hàm trên, kết quả trả về chuổi “TM” (giá trị của ô giao nhau giữa dòng 2 và cột 2). Vì dòng 2 của bảng chứa giá trị mang đi dò tìm là “111”, chỉ số cột xác định trong hàm là 2. Nếu ta thay giá trị dò tìm là “112” thì hàm sẽ trả về giá trị của ô giao nhau giữa dòng 3 và cột 2 (TGNH). Nếu ta thay giá trị dò tìm là “511” thì hàm sẽ trả về giá trị #N/A (không tìm thấy). Vì TK 511 không có trong bảng dò tìm. - Hàm Match( )  Cú pháp: =Match(LookupValue, LookupArray, MatchType) Trong đó: − LookupValue: là một giá trị (hoặc địa chỉ ô chứa giá trị) mà hàm sẽ mang đi dò tìm trong mảng Array. 15
− LookupArray: là mảng một chiều chứa các giá trị dò tìm (dãy các giá trị) của hàm. Giá trị dò tìm LookupValue phải cùng kiểu dữ liệu với các giá trị trong LookupArray. − Match Type: Là True() hoặc False(). Nếu là 0 thì việc dò tìm của hàm phải chính xác, nếu là 1 thì việc tìm một giá trị trong bảng không cần phải chính xác chỉ cần gần đúng là được.  Công dụng: Hàm trả về giá trị kiểu số là số thứ tự của phân tử trong mảng chứa Lookup Value, ngược lại hàm trả về giá trị #N/A (không tìm thấy). Ta có thể biểu diễn lại cú pháp hàm dưới dạng bảng ví dụ như sau: =Match (“A01”, A01 A01 B01 B02 B03 0,) Kết quả trả về của hàm là số 1. Nếu thay giá trị dò tìm là “B02” thì hàm trả về giá trị là số 4. Nếu thay giá trị dò tìm là “B06” thì hàm trả về giá trị là #N/A (vì không tìm thấy) - Hàm Index( )  Cú pháp: Index(Array, RowNumber, ColunmNumber), trong đó: − Array: bảng hai chiều chứa các giá trị trả về của hàm. − Rownumber, ColunmNumber: là chỉ số dòng và chỉ số cột của ô được chỉ định chứa giá trị trả về trong bảng.  Công dụng: hàm trả về giá trị của 1 ô trong bảng Array là ô giao nhau giữa RowNumber và ColunmNumber. Ta có thể biểu diễn lại cú pháp hàm dưới dạng ví dụ như sau: 11 89 04 42 12 42 57 23 =INDEX( 30 65 13 14 ,2,3) 15 55 78 83 A B C D Kết quả trả về 57 (giá trị của ô giao nhau giữa dòng 2 và cột 3). Nếu thay chỉ số dòng là 3 và chỉ số cột là 3 thì kết quả trả về là 13. Nếu thay chỉ số dòng là 3 và chỉ số cột là 4 thì kết quả trả về là 14. Nếu thay chỉ số dòng là 5 và chỉ số cột là 4 thì kết quả trả về là “D”. 16
Ta thường dùng kết hợp giữa hàm Index với hàm Match để tham chiếu đến một giá trị trong bảng, cách dùng như sau: Ví dụ: Cho bảng đơn giá vận chuyển các loại hàng hoá đến các khu vực như sau: A B C D E F G 1 MH01 MH02 MH03 MH04 MH05 MH06 2 TP 12 55 97 24 20 41 3 VT 13 14 19 23 14 12 4 BD 56 45 55 58 44 21 5 MT 85 61 67 80 51 23  Dùng hàm Index để tham chiếu đơn giá vận tải của mặt hàng có mã số MH04 đi khu vực BD. Ta thấy khu vực BD ở dòng 3, và mã hàng MH04 ở cột 4 và sử dụng hàm Index như sau: =Index ($B$2:$G$5,3,4) và kết quả trả về 58.  Nhưng cách dùng như trên sẽ không linh hoạt khi ta có mã hàng và khu vực thay đổi. để sử dụng hàm linh hoạt hơn ta kết hợp với hàm Match. Hàm Match sẽ đóng vai trò xác định chỉ số dòng và chỉ số cột cho hàm Index. Ví dụ: =Index ($B$2:$G$5,Match(“BD”,$A$2:$A$5,0),Match(“MH04”,$B$1:$G$1,0)) Kết quả của hàm Match thứ nhất trả về số 3, hàm Match thứ hai trả về số 4. Do vậy hàm Index sẽ trả về giá trị của 1 ô trong bảng $B$2:$G$5 là giao nhau giữa dòng 3 và côt 4 (58).  Ta cũng thường dùng kết hợp giữa hàm Vlookup với hàm Match để tham chiếu trên một giá trị trong bảng, trong trường hợp này hàm Match sẽ giúp xác định được chỉ số cột colunmIndexNumber chứa giá trị trả về. Ví dụ: =Vlookup (“BD”,$B$1:$G$5,Match(“BD”,$A$1:$G$1,0),0)  Ta cũng thường dùng kết hợp giữa hàm Hlookup với hàm Match để tham chiếu trên một giá trị trong bảng, trong trường hợp này hàm Match sẽ giúp xác định được chỉ số dòng RowIndexNumber chứa giá trị trả về. Ví dụ: =Hlookup (“MH04”,$B$1:$G$5,Match(“BD”,$A$1:$A$5,0),0)  Kết quả của hàm Index, Vlookup, Hlookup là như nhau. - Hàm Sumif()  Cú pháp: =Sumif(Range, criteria, SumRange), trong đó: − Range: cột chứa giá trị để so sánh với điều kiện Criteria khi tính toán. 17
− Criteria : Điều kiện tính toán, có kiểu dữ liệu trùng với kiểu dữ liệu của cột Range. − SumRange: cột chứa giá trị kiểu số, cột SumRange có chiều cao bằng với cột Range và tương ứng các giá trị.  Công dụng: Hàm tính tổng theo điều kiện.  Nguyên tắc tính toán của hàm: Khi thi hành sẽ so sánh điều kiện Criteria với các giá trị tổng cột Range. Nếu bằng thì lấy giá trị tương ứng (cùng dòng) bên cột SumRange cộng lại. Ta có thể biểu diễn lại bằng ví dụ sau: =Sumif( Range, Criteria, Sumrange) MÃ HÀNG SỐ LƯỢNG 10 A01 A02 10 , “A01”, =Sumif( A03 100 100 ) A01 A02 20 Kết quả của hàm trả về số lượng 110. Nếu thay điều kiện thành mã hàng A02 thì kết quả sẽ trả về số lượng 30. Nếu thay điều kiện thành mã hàng A03 thì kết quả trả về số lượng 100. - Hàm Dsum( ):  Cú pháp: =Dsum(Database, Field/Number, Criteria), trong đó: − Database: Địa chỉ (hoặc tên) bảng dữ liệu nguồn bao gồm cả dòng tiêu đề cột của bảng. − Field/Number: số thứ tự cột trong bảng DataBase, cột này có dữ liệu kiểu số được dùng làm cột tính tổng cộng các giá trị trong cột khi điều kiện Criteria thoả mãn. Field/Number có thể dùng để chỉ số thứ tự cột trong bảng hay dùng tiêu đề cột cần tính của bảng (dùng địa chỉ ô chứa tiêu đề cột). − Criteria: Bảng điều kiện dùng làm căn cứ tính tổng.  Công dụng: Hàm Dsum() sẽ tính tổng các giá trị trong cột Field/Number ứng với dụng chứa điều kiện (thõa điều kiện) trong bảng điều kiện criteria. 18
 Nguyên lý làm việc: điều kiện của criteria sẽ được so sánh với những giá trị của cột có tiêu đề cột trùng với tiêu đề cột của bảng điều kiện. Nếu bằng thì sẽ lấy giá trị trong cột FieldNumber cộng lại, ngược lại sẽ không làm gì cả.  Ta có thể biểu diễn lại hàm Dsum() bằng ví dụ sau: Số CT TK ghi nợ Tk ghi có Số lượng Số tiền TK ghi nợ Tk ghi có 1 111 112 57 3.000 1521 111 =Dsum 2 112 111 13 12.000 ,5 ) 3 1521 111 78 2.500 ( 4 1521 112 1 90.000 ,  Kết quả của hàm trả về giá trị: 2.500  Nếu thay bảng điều kiện thành: TK ghi nợ Tk ghi có 1521 112 1521 111  Hàm sẽ tính tổng các giá trị trong cột số tiền (cột thứ 5 của bảng) ứng với những định khoản có TK ghi nợ là 1521 đối ứng với 111 hoặc có TK ghi nợ là 1521 và đối ứng với 112. Các định khoản khác có TK ghi nợ là 1521 nhưng tài khoản ghi có khác 111, 112 thì không thoả mãn điều kiện và không được tính. Kết quả của hàm theo điều kiện trên trả về: 92.500. 19