Blog của Lê Văn Luật

Đời người thì có hạn mà sự học thì vô hạn!

Kế toán trên Excel 1: Ôn tập kiến thức Ms Excel

Phần này ôn tập lại một vài kiến thức Ms Excel liên quan:

Địa chỉ tương đối & địa chỉ tuyệt đối

Địa chỉ tương đối sẽ tự động thay đổi khi copy công thức sang ô khác. Địa chỉ mới được tạo thành từ địa chỉ cũ và độ dời dòng, cột từ vị trí cũ của công thức tới vị trí mới.

Ví dụ: A1

Địa chỉ tuyệt đối: phần nào được xác định là tuyệt đối (bằng cách thêm dấu $ vào trước) sẽ không thay đổi khi sao chép công thức tới vị trí khác. Có thể dùng phím F4 để thay đổi loại địa chỉ (từ tương đối -> tuyệt đối ->…).

Ví dụ:
Tuyệt đối cột, tuyệt đối dòng: $A$1
Tuyệt đối cột, tương đối dòng: $A1
Tương đối cột, tuyệt đối dòng: A$1

Vùng ô & Tên vùng

Vùng ô được chỉ ra bởi địa chỉ ô góc trên trái và ô góc dưới phải.

Ví dụ: A1:C3

Vùng ô có thể là 1 ô.

Có thể đặt tên cho vùng ô. Tên vùng ô có thể có phạm vi (scope) trên toàn workbook hoặc chỉ trong một worksheet.

Cách đặt tên vùng ô:

ex01-vungo

Chọn vùng ô muốn đặt tên =>chọn tab FORMULAS => kích chuột vào Define Name => hộp thoại New Name sẽ xuất hiện như hình trên => Nhập tên vùng ô vào ô Name, chọn phạm vi trong mục Scope => Ok.

Để xóa hoặc chỉnh sửa tên vùng:

ex02-vungo2

Chọn vùng ô muốn đặt tên =>chọn tab FORMULAS => kích chuột vào Name Manager => hộp thoại Name Manager sẽ xuất hiện như hình trên => Chọn vùng ô muốn thao tác => kích chuột vào nút Edit nếu muốn chỉnh sửa, kích chuột vào Delete nếu muốn xóa.

Sử dụng chức năng gợi ý tên của Ms Excel (chỉ có từ Ms Excel 2010 trở lên)

Từ Ms Excel 2010 trở lên có chức năng gợi ý tên hàm, tên vùng giúp ta nhập không bị sai tên khi nhập công thức.

Nhập = rồi đánh một vài chữ cái đầu của tên hàm sẽ thấy Ms Excel liệt kê các tên hàm bắt đầu bằng các ký tự đã nhập. Ta có thể dùng các phím mũi tên lên, xuống để di chuyển tới tên hàm cần chọn và bấm phím Tab để chọn.

ex03-sen

Trong công thức, khi cần nhập tên vùng ta cũng sử dụng cách tương tự để chọn tên vùng.

Tính năng này giúp giảm thời gian nhập công thức đồng thời bảo đảm ta nhập tên hàm, tên vùng chính xác.

Các hàm liên quan

Hàm MAX, MIN

MAX( VùngÔ1[, VùngÔ2,…])      MIN( VùngÔ1[, VùngÔ2,…])

Cho giá trị lớn nhất trong các vùng ô liệt kê.

Ví dụ: MAX( a; 0) cho a nếu a > 0, cho 0 nếu a = 0, cho 0 nếu a < 0.

Hàm SUMIF

Cách dùng 1: SUMIF(VùngÔ, ĐiềuKiện)

tính tổng các ô số, ngày trong VùngÔ thỏa ĐiềuKiện

Ví dụ: SUMIF(A1:C6, “>6”) = 18

ex04-sumif1

Cách dùng 2: SUMIF(VùngÔ, ĐiềuKiện, VùngTínhTổng)

Ví dụ:

ex05-sumif2

SUMIF(E3:E15, “111”, G3:G15) = tổng phát sinh Nợ của tài khoản 111 (không quan tâm trong khoảng thời gian nào).

SUMIF(F3:F15, “111”, G3:G15) = tổng phát sinh Có của tài khoản 111 (không quan tâm trong khoảng thời gian nào).

Có thể dùng các ký tự đại diên (gồm ? và *) trong ĐiềuKiện. Dấu chấm hỏi (?) thaycho 1 ký tự bất kỳ. Dấu hoa thị (*) thay cho một chuỗi ký tự bất kỳ.

Ví dụ:

SUMIF(vCTTKNo, “11?”, vCTSoPhatSinh) – cho tổng phát sinh nợ của các tài khoản cấp 1 thuộc nhóm 11 (111, 112, 113,…). Các tài khoản cấp 2 trở đi không tính.

SUMIF(vCTTKNo, “11*”, vCTSoPhatSinh) – cho tổng phát sinh nợ của các tài khoản bắt đầu bằng 11 (111, 1111, 1112,…,112, 1121,….).

Hàm MATCH

MATCH(GiáTrịĐãBiết, VùngÔ, CáchTìm)

Cho vị trí của GiáTrịĐãBiết trong VùngÔ.

CáchTìm có thể là 0 – tìm chính xác hoặc 1 – tìm gần đúng.

Ví dụ:

ex06-vlookup

MATCH(112, A3:A19, 0) = 2

MATCH(121, A3:A19, 0) = 4

Hàm INDEX

INDEX(VùngÔ, VịTrí)

Cho giá trị nằm tại VịTrí trong VùngÔ

Ví dụ: (xem hình trong ví dụ hàm MATCH)

INDEX(A3:A19, 2) = 112

Hàm VLOOKUP

VLOOKUP(GiáTrịĐãBiết, VùngÔ, CộtTrảVề, CáchTìm)

CáchTìm có thể là 0 – tìm chính xác hoặc 1 – tìm gần đúng.

Thường VùngÔ là tên vùng hoặc dùng địa chỉ tuyệt đối.

Ví dụ:

ex06-vlookup

VLOOKUP(111, $A$3:$E$19, 2, 0) cho “Tiền mặt”

VLOOKUP(112, vHTTK, 2, 0) cho “Tiền gửi ngân hàng” (trong đó vHTTK là vùng A3:E19)

VLOOKUP(111, vHTTK, 3, 0) cho N (Loại tài khoản )

VLOOKUP(111, vHTTK, 4, 0) cho 200.000.000 (số dư Nợ đầu năm)

Hàm SUMPRODUCT

SUMPRODUCT(VùngÔ1*VùngÔ2,…)

Lấy từng ô tương ứng trong các vùng ô thực hiện phép nhân với nhau sau đó cộng các kết quả đó lại.

Ví dụ:

ex07-sumproduct

SUMPRODUCT(A2:A6*B2:B6) – Ms Excel sẽ lấy lần lượt từng ô tương ứng ở 2 vùng nhân với nhau: A2*B2, A3*B3, A4*B4,… sau đó công các tích này lại và cho kết quả 2.530.

ex05-sumif2

SUMPRODUCT(G3:G16*(A3:A16<DATE(2013,2,1))) – tính tổng tất cả các số phát sinh của các chứng từ có ngày ghi số nhỏ hơn ngày 1/2/2013 (tức phát sinh trước ngày 1/2/2012).

SUMPRODUCT(G3:G16*(E3:E16=111)*(A3:A16<DATE(2013,2,1))) – tính tổng phát sinh Nợ của tài khoản 111 trước ngày 1/2/2013.

SUMPRODUCT(G3:G16*(E3:E16=111)*(A3:A16>=DATE(2013,2,1))*(A3:A16<=DATE(2013,2,28))) – tính tổng phát sinh Nợ của tài khoản 111 trong tháng 2/2013.

SUMPRODUCT(G3:G16*(LEFT(E3:E16,2)=”11″)*(A3:A16>=DATE(2013,2,1))*(A3:A16<=DATE(2013,2,28))) – tính tổng phát sinh Nợ của tài khoản có số hiệu bắt đầu bằng 11 trong tháng 2/2013.

Hàm ROW, COLUMN

ROW() – cho số thứ tự dòng của ô chứa hàm. Ví dụ: nhập công thức =ROW() trong ô B3 sẽ cho kết quả = 3 (dòng thứ 3)

ROW(ĐịaChỉ) – cho số thứ tự dòng của ĐịaChỉ). Ví dụ: ROW(B3) = 3.

COLUMN() – cho số thứ tự cột của ô chứa hàm.

COLUMN(ĐịaChỉ) – cho số thứ tự cột của ĐịaChỉ. Ví dụ: COLUMN(B3) = 2.

Hàm INDIRECT

Tạo địa chỉ thực từ chuỗi có dạng địa chỉ.

Ví dụ: SUM(INDIRECT(“A1:C5”)) cho tổng của vùng ô A1:C5. Lưu ý rằng nếu nhập công thức = SUM(“A1:C5”) sẽ cho kết quả là #VALUE! vì hàm SUM chỉ tính tổng các giá trị số hoặc ngày mà “A1:C5” (lưu ý nằm trong cặp nháy kép) là một chuỗi ký tự.

Advertisements

Trả lời

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

Information

This entry was posted on 06/03/2013 by in Kế toán trên máy tính.

Điều hướng

%d bloggers like this: