Các hàm excel thông dụng trong kế toán
- Cú pháp: = SUM(số1, số2,…, số hoặc vùng dữ liệu)
Ý nghĩa: Là hàm tính tổng các giá trị
Ví dụ: =SUM(4,5,1) => Kết quả =10
- Cú pháp: = AVERAGE (giá trị 1, giá trị 2, giá trị 3,… giá trị n hoặc vùng dữ liệu)
Ý nghĩa: Là hàm trả về trung bình cộng các tham số đưa vào hoặc của 1 vùng dữ liệu
Ví dụ: =SUM(7,8,9) => Kết quả =8
- Cú pháp: = If (Logical_test,[value_if_true],[value_if_false]) nghĩa là If( Điều kiện , giá trị 1, giá trị 2)
Ý nghĩa: Là hàm trả về giá trị 1 nếu điều kiện đúng, hàm trả về giá trị 2 nếu điều kiện sai
Ví dụ : Cho bảng điểm của học sinh, xét kết quả để biết học sinh đó đỗ hay phải thi lại
Ta sử dụng công thức cho ô D6 là : =IF(C6>=5,”Đỗ”,”Thi lại”)
Các bạn đã có kết quả của các học sinh đỗ hay thi lại
- Cú pháp: = AND((Logical1; [Logical2]; [Logical3];…) nghĩa là And(đối 1, đối 2,..)
Ý nghĩa: Hàm này là Phép VÀ, chỉ đúng khi tất cả các đối số có giá trị đúng. Các đối số là các hằng, các biểu thức logic
- Hàm AND có tối đa 256 đối số phải là các giá trị logic hay các mảng hoặc tham chiếu chứa các giá trị logic. Tất cả các giá trị sẽ bị bỏ qua nếu một đối số mảng hoặc tham chiếu có chứa văn bản hoặc ô rỗng.
- Các đối số phải là giá trị logic hoặc mảng hay tham chiếu có chứa giá trị logic.
- Nếu đối số tham chiếu là giá trị text hoặc Null (rỗng) thì những giá trị đó bị bỏ qua.
- Nếu vùng tham chiếu không chứa giá trị logic thì hàm trả về lỗi #VALUE!
Ví dụ 1: Sử dụng hàm AND để biết các công thức sau đúng hay sai.
Duyệt tên và giới tính của học viên xem đúng hay sai
Với công thức ở C6:= AND(A6=”Nguyễn Văn Đạt”,B6=”Nam”)
Nếu đúng thì kết quả sẽ trả về là TRUE
Ngược lại nếu sai thì bạn sẽ thu được kết quả là FALSE
- Cú pháp: = OR((Logical1; [Logical2]; [Logical3];…) nghĩa là Or(đối 1, đối 2,..)
Ý nghĩa: Hàm này là Phép HOẶC, chỉ sai khi tất cả các đối số có giá trị sai
Ví dụ: =OR(F7>03/02/76,F7>01/01/2016)
- Cú pháp: = MAX(number 1, number 2, …)
Ý nghĩa: Trả về giá trị lớn nhất của number1, number 2,.. hoặc giá trị lớn nhất của cả 1 vùng dữ liệu số
- Cú pháp: = MIN(number 1, number 2, …)
Ý nghĩa: Trả về giá trị nhỏ nhất của number1, number 2,.. hoặc giá trị nhỏ nhất của cả 1 vùng dữ liệu số
- Cú pháp: = SUBTOTAL ((function_num,ref1,[ref2],…)
- Function_num: Bắt buộc. Số 1-11 hay 101-111 chỉ định hàm sử dụng cho tổng phụ. 1-11 bao gồm những hàng ẩn bằng cách thủ công, còn 101-111 loại trừ chúng ra; những ô được lọc ra sẽ luôn được loại trừ.
- Ref1 Bắt buộc. Phạm vi hoặc tham chiếu được đặt tên đầu tiên mà bạn muốn tính tổng phụ cho nó.
- Ref2,… Tùy chọn. Phạm vi hoặc chuỗi được đặt tên từ 2 đến 254 mà bạn muốn tính tổng phụ cho nó.
- Nếu có hàm SUBTOTAL khác lồng đặt tại 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
- Đối số Function_num nếu từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị trong tập số liệu (hàng ẩn). Đối số Function_num nếu từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (bỏ qua các giá trị ẩn0
- 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) không phụ thuộc vào đối số Function_num được dùng
- Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc, nó không được thiết kế để tính theo chiều ngang
- Hàm này chỉ tính toán cho dữ liệu 2-D do vậy nếu dữ liệu tham chiếu dạng 3-D thì hàm SUBTOTAL báo lỗi #VALUE
- V là viết tắt của Vertical có nghĩa là hàng dọc (thẳng đứng). Lookup là hàm tham chiếu, tìm kiếm
VLOOKUP nghĩa là tìm kiếm theo chiều dọc, theo cột
- Cú pháp: = VLOOKUP(x, vùng tham chiếu, cột thứ n,0)
Ý nghĩa: lấy một giá trị(x) đem so sánh theo cột của vùng tham chiếu để trả về giá trị ở cột tương ứng trong vùng tham chiếu (cột thứ n) , 0: so sánh tương tối, 1: so sánh tuyệt tối
- Vùng tham chiếu: cột đầu tiên của tham chiếu phải bao phủ vừa đủ và toàn bộ các giá trị dò tìm. Luôn luôn phải để ở giá trị tuyệt đối
- Cột cần lấy đếm xem nó là cột thứ bao nhiêu trong bảng tham chiếu. Khi đếm phải đếm từ trái qua phải
- Cú pháp: = SUMIF((range, criteria,sum_range) nghĩa là Sumif (Vùng chứa điều kiện, điều kiện, vùng cần tính tổng)
Ý nghĩa: Hàm này trả về giá trị tính tổng cảu các ô trong vùng cần tính thỏa mãn một điều kiện đưa vào
- Do tính toán trong ô của Excel, nên hàm SUMIF tính tổng này trên các phiên bản Excel 2016, Excel 2013, hay các phiên bản đời trước như Excel 2010, 2007, 2003 đều áp dụng cấu trúc hàm như nhau.
Ví dụ: Bài toán sử dụng hàm SumIF tính tổng phụ cấp cho những người có chức vụ là “Nhân Viên” trong bảng số liệu dưới đây:
Với công thức cho ô D15 là : =SUMIF(C5:C14,”Nhân Viên”,D5:D14)
Ta thu được kết quả như sau: 2500000. Khi chúng ta kiểm tra lại kết quả thì thấy rằng trong VD hàm Sumif này có 5 người có chức vụ là “Nhân viên”, như vậy tổng phụ cấp cho những người đó với kết quả là 2500000 là chính xác.
- Cú pháp: = COUNTIF (range,criteria)
Range: là dãy dữ liệu mà bạn muốn đếm có điều kiện.
Criteria: là điều kiện để một ô được đếm.
Ý nghĩa: Hàm COUNTIF TRONG excel dùng để đếm số ô thỏa mãn điều kiện (Criteria) trong phạm vi (Range)
Ví dụ: = COUNTIF(B4:B1,”>500″)
Có nghĩa là đếm tất cả các ô trong dãy B4:B12 có chứa số lớn hơn 500
- Cú pháp: = LEFT(chuỗi, ký tự muốn lấy)
Ý nghĩa: Tách lấy những ký tự bên trái chuỗi ký tự
Ví dụ: = LEFT(“THANH HUE”,4) => Kết quả: = THANH
- Cú pháp: = RIGHT(chuỗi, ký tự muốn lấy)
Ý nghĩa: Tách lấy những ký tự bên phải chuỗi ký tự
Ví dụ: = LEFT(“THANH HUE”,3) => Kết quả: = HUE
- Cú pháp: = NOW()
Cú pháp hàm NOW không có đối số nào
Ý nghĩa: Hàm =NOW() để hiển thị ngày giờ của hệ thống trong tính. Ví dụ như sau:
Bạn chỉ cần nhập hàm =now() và nhấn Enter là ngay lập tức sẽ hiển thị ngày và giờ ở thời điểm hiện tại như hình bên dưới.
- Có thể sẽ phát sinh lỗi #### nhưng bạn chỉ cần kéo ô tính rộng hơn một chút là sẽ OK thôi
Cấu trúc: TEXT(value, format_text)
Tác dụng: Trình bày lại 1 giá trị (số hoặc ngày tháng) vào trong 1 đoạn text mà vẫn giữ được định dạng của loại dữ liệu đó
Viết lại thông tin liên quan tới thời gian lập các báo cáo kế toán tổng hợp
Phần mềm kế toán excel cập nhật theo thông tư 133 & thông tư 200 miễn phí
https://drive.google.com/file/d/1BgKkUMyCEfhHw6DyVFBTfRXtRXtNI-kS/view
Những phần chính trong phần mềm kế toán excel:
- Thông tin doanh nghiệp
- Hệ thống tài khoản kế toán
- Danh sách vật tư, hàng hóa
- Khách hàng và nhà cung cấp
- Các nhân viên
- Sổ nhật ký chung
- Nhật ký mua hàng
- Sổ nhật ký bán hàng
- Sổ Chi tiết từng tài khoản
- Chi tiết tiền mặt
- Sổ chi tiết tiền gửi ngân hàng
- Sổ cái
- Sổ tổng hợp theo từng tài khoản
- Phiếu thu
- Phiếu chi
- In phiếu kế toán
- Bảng cân đối phát sinh
- Lưu chuyển tiền tệ
- Báo cáo KQKD
- Bản thuyết minh BCTC
- …
Một số màn hình thao tác trên màn hình
Cách làm sổ sách kế toán trên excel
Các công việc đầu năm cần phải làm khi làm sổ sách:
– Những DN đã và đang hoạt động thì đầu năm các bạn phải chuyển số dư cuối năm trước sang đầu năm nay, cụ thể như sau:
– Vào số dư đầu kỳ “Bảng cân đối phát sinh tháng”
– Vào số dư dầu kỳ các Sổ chi tiết tài khoản 242, 211, 131,
– Vào Bảng tổng hợp Nhập Xuất Tồn, và các Sổ khác (nếu có)
– Chuyển lãi (lỗ) năm nay về năm trước (Căn cứ vào số dư đầu kỳ TK 4212 trên Bảng CĐTK để chuyển). Việc thực hiện này được hạch toán trên Nhật ký chung và chỉ thực hiện 1 lần trong năm, vào thời điểm đầu năm.
Các công việc trong tháng cần phải làm:
– Hiện nay đa phần DN lựa chọn ghi sổ theo hình thức Nhật ký Chung, hướng dẫn cách ghi sổ nhật ký chung trên Excel.
Chú ý: Trong quá trình hạch toán ghi sổ bạn phải làm theo nguyên tắc đó là: Đồng nhất về tài khoản và đồng nhất về mã hàng hóa
Cách ghi sổ khi có phát sinh tại Doanh nghiệp:
+) Khi có phát sinh thêm Khách hàng hoặc nhà cung cấp mới:
– Thì các bạn khai báo chi tiết đối tượng KH hoặc NCC mới bên bảng Danh mục tài khoản và đặt mã Tài khoản (Mã khách hàng) cho KH/NCC đó, đồng thời định khoản chi tiết bên NKC theo mã TK mới khai báo.
Ví dụ: Phải thu của Công ty kế toán Thiên Ưng (là khách hàng mới):
Bước 1: Sang DMTK khai chi tiết khách hàng – Công ty kế toán Thiên Ưng với mã Khách hàng mới là: 1311 hoặc 131TU (Khai báo phía dưới Tk 131)
-> (Việc khai báo mã TK như thế nào là tuỳ vào yêu cầu quản trị của bạn, sao cho dễ kiêm tra đối chiếu)
Bước 2: Hạch toán bên NKC theo mã TK (Mã KH) đã khai báo cho Công ty kế toán Thiên Ưng là 1311 hoặc 131TU
– Nếu không phát sinh Khách hàng mới thì khi gặp các nghiệp vụ liên quan đến TK 131 và TK 331, ta quay lại Danh mục TK để lấy Mã Khách hàng đã có và định khoản trên NKC.
+) Trường hợp phát sinh mới Công cụ dụng cụ hoặc TSCĐ (tức liên quan đến TK 242, 214 )
– Sau khi định khoản trên NKC phải sang bảng phân bổ 242, 214 để khai báo thêm công cụ dụng cụ hoặc tài sản này vào bảng và tính ra số cần phân bổ trong kỳ hoặc số cần trích khấu hao trong kỳ.
Bước 1: Bên Nhật ký chung không phải khai chi tiết từng mặt hàng mua vào, chỉ hạch toán chung vào TK 156 tổng số tiền ở dòng “Cồng tiền hàng” trên hoá đơn mua vào
– Bước 2: Đồng thời về Phiếu nhập kho, khia báo chi tiết từng mặt hàng mua theo hoá đơn vào phiếu nhập kho:
– Nếu mặt hàng mua vào đã có tên trong Danh mục hàng háo thì quay về DM hàng hoá để lấy Mã hàng, tên hàng cho hàng hoá đó và thực hiện kê nhập.
– Nếu mặt hàng mùa vào là hàng mới thì phải đặt Mã hàng cho từng mặt hàng trên DMHH sau đó thực hiện kê nhập trên PNK theo mã hàng đã khai báo
Bước 3: Nếu phát sinh chi phí (vận chuyển, bốc dỡ, lưu kho…) cho việc mua hàng thì Đơn giá nhập kho là đơn giá đã bao gồm chi phí. Khi đó phải phân bổ chi phí mau hàng cho từng mặt hàng như sau:
(Có thể lập bảng tính riêng cho việc phân bổ chi phí)
| Chi phí của mặt hàng A | = | Tổng chi phí | X | số lượng ( hoặc thành tiền) mặt hàng A |
| Tổng số lượng ( hoặc tổng thành tiền Của lô hàng ) |
| Chi phí đơn vị Mặt hàng A | = | Tổng chi phí của mặt hàng A |
| Tổng số lượng của mặt hàng A |
| Đơn giá nhập kho Mặt hàng A | = | Đơn giá mua của Mặt hàng A | + | Chi phí đơn vị Của mặt hàng A |
Bước 1: Bên Nhật ký chung không phải khai chi tiết từng mặt hàng bán ra, chỉ hạch toán chung vào TK 5111 tổng số tiền ở dòng “CỘng tiền hàng” trên hoá đơn bán ra.
Bước 2: Đồng thời về Phiếu Xuất kho, khai báo chi tiết từng mặt hàng bán ra theo Hoá đơn vào Phiếu XK.
– Để lấy được Mã hàng xuất kho, ta quay về Danh mục hàng hoá để lấy.
– Không hạch toán bút toán Giá vốn hàng bán: Vì CÔng ty áp dụng phương pháp tính gái xuất kho là phương pháp “Bình quân cuối kỳ“, nên cuối tháng mới thực hiện bút toán này để tập hợp giá vốn hàng bán trong kỳ.
– Khi vào bảng kê xuất kho thì chỉ vào số lượng, chưa có đơn gá xuất kho vì đơn giá cuối kỳ mới tính được bên Bảng Nhập Xuất TỒn kho
– Khi tính được Đơn giá bên bảng Nhập – Xuất – Tồn thì sử dụng hàm VLOOKUP tìm đơn giá xuất kho từ bảng Nhập – XUất – TỒn về PXK.
c. Khi cần nhập kho hoặc xuất kho:
– Các bạn vào phiếu nhập kho, xuất kho (cũng làm theo như phần trên nhé)
Lập sổ chi tiết các Tài khoản cuối kỳ:
a. Lập bảng Tổng hợp phải thu khách hàng – TK 131:
– Cột Mã KH và Tên KH, sử dụng hàm IF kết hợp với hàm LEFT tìm ở DMTK về
– Cột mã khách hàng: = IF(LEFT(DMTK!A14,3)=”131”,DMTK!A14,””) (địa chỉ ô A 14 là TK chi tiết đầu tiên của TK 131). Hoặc dùng VLOOKUP tìm DMTK về
– Cột tên khách hàng; = IF(A11=””,””,VLOOKUP(A11,DMTK!$A$14:$E$150,2,0))
– Dư Nợ và Dư có đầu kỳ: Dùng hàm VLOOKUP tìm ở CĐPS tháng về.
– Cột Dư NỢ đầu kỳ: = VLOOKUP($A11,’CDSPS-thang’!$A$9:$H$150,3,0)
– Cột Dư Có đầu kỳ: = VLOOKUP($A11,’CDSPS-thang’!$A$9:$H$150,4,0)
– Cột số phát sinh Nợ và phát sinh Có, sử dụng hàm SUMIF tập hợp từ NKC về.
– Cột số phát sinh Nợ:
= SUMIF(NKC!$E$13:$E$607.’So cong no TK 131’!$A11,NKC!$G$13:$G$607)
– Cột số phát sinh Có:
= SUMIF(NKC!$E$13:$E$607,’So cong no TK 131’$A11,NKC!$H$13:$H$607)
– Cột dư Nợ và dư Có cuối kỳ, dùng hàm Max
– Cột dư Nợ cuối kỳ: = MAX ( C11+E11-D11-F11,0)
– Cột dư Có cuối kỳ: = MAX( D11+F11–C11-E11,0)
b. Lập Bảng tổng hợp Phải trả khách hàng – TK 331
– Cách làm tương tự như bảng tổng hợp TK 131
c. Lập sổ quỹ tiền mặt và sổ tiền gửi ngân hàng;
– Riêng sổ quỹ tiền mặt và sổ Tiền gửi ngân hàng chúng ta không thể chuyển sổ trên NKC mà phải tính riêng 2 sổ này, vì 2 loại sổ này có mẫu sổ khác so với các sổ chi tiết TK, sổ tổng hợp TK khác
+) Cách lập sổ Quỹ tiền mặt: ( Dữ liệu lấy từ sổ Nhật Ký Chung)
– Cách lập công thức cho từng cột như sau: Trên sổ quỹ tiền TM, xây dựng thêm 3 ô: Tháng báo cáo; Tài khoản báo cáo ( là TK 1111); Nối tháng và TK cáo cáo.
– Ô nối tháng và TK báo cáo = K6&”;”&L6 ( dùng tính số dư dầu kỳ theo từng tháng)
– Cột ngày tháng: = IF($L$6=NKC!$E13,NKC!A13,””) ( L6: ô TK báo cáo)
– Cột Diễn giải: = IF($L$6=NKC!$E13,NKC!D13,””)
– Cột Tài khoản đối ứng: = IF($L$6=NKC!$E13,NKC!F13,””)
– Cột thu: = IF($L$6=NKC!$E13,NKC!G13,””)
– Cột Chi: = IF($L$6=NKC!$E13,NKC!H13,””)
– Cột số phiếu thu: = IF(H11<=0,””,COUNTIF(H$11:H11,”>”)) ( H: cột tiền thu)
– Cột số phiếu chi: = IF(I11<=0,””,COUNTIF(I$11:I11,”>”)) ( I: Cột tiền chi)
– Dòng số dư dầu kkỳ dùng hàm SUMIF lấy trên bảng CDPS chi tiết của từng tháng.
Để tính được số dư đầu kỳ của từng tháng trên Sổ quỹ TM thì ta phải xây dựng bên phải bảng “Cân đối phát sinh tháng” của các tháng thêm 2 cột:
– Cột BC: Gõ số tháng tại dòng tương ứng với TK 111 của Bảng CĐPS và coppy cho những dòng tiếp theo của tháng đó ( làm cho tất cả các tháng).
– Cột “ Nối tháng và TK báo cáo” : =I9&”;”&A9 ( Là dãy điều kiện cho hàm SUMIF)
Sau đó dùng hàm SUMIF để tính ra số dư đầu kỳ trên sổ Quỹ TM;
= SUMIF(‘CDPS-thang’!$J$9:$J$450,’So quy’!$M$6,’CDPS-thang’!$C$9:$C$450)
– Cột tồn tiền cuối ngày dùng hàm Subtotal:
Cú pháp hàm: = $J$9+Subtotal(9,H$11:H11)-Subtotal(9,I$11:I11)
– Dòng cộng số phát sinh : Dùng hàm subtotal
– Dòng số dư cuối kỳ: Dùng công thức đơn giản như sau:
Dư cuối kỳ = Tồn đầu kỳ + TỔng thu – TỔng chi.
( Sổ quỹ TM được lập cho cả kỳ kế toán, Bạn muốn xem tháng nào thì lọc tháng đó lên, Cụ thể có ở phần in sổ)
– Cách làm tương tự như sổ quỹ tiền mặt. Nhưng cột số hiệu và Ngày tháng chứng từ thì công thức tương tự như cột Ngày tháng ghi sổ.
Tag: 2019 2018 tt200 tải hành sự tạo mộc lan vaa 2017 file thường nâng cao cơ may mặc nội lương giáo dành dạy ăn uống máy kỹ năng gì sumifs