Hiển thị các bài đăng có nhãn Excel-Kế toán. Hiển thị tất cả bài đăng
Hiển thị các bài đăng có nhãn Excel-Kế toán. Hiển thị tất cả bài đăng

Khóa và mở khóa (Protect/Unprotect) vùng dữ liệu trong bảng tính excel

Tính năng Protect sheet trong bảng tính excel cho phép bạn bảo vệ, khóa, không cho người khác sửa đổi các ô chứa công thức hay vùng dữ liệu nhạy cảm khá dễ dàng.

Protect sheet bảo vệ bảng tính, chống sửa đổi các ô vùng chọn bằng thiết lập mật khẩu. Tuy nhiên, nếu không may bạn quên mật khẩu, hay bạn cần Unprotect sheet bảng tính khác mà không có mật khẩu thì làm thế nào?

Bài viết sau sẽ trình bày cách khóa một bảng tính, khóa một vùng chọn của bảng tính và cách 'phá pass' Unprotect sheet mà không cần phải dùng phần mềm can thiệp.

Cách bảo vệ bảng tính bằng Protect sheet

Nếu khóa toàn bộ bảng tính trừ vùng chọn: đầu tiên bạn cần chọn (bôi đen) vùng chọn không bị khóa (người dùng khác có thể chỉnh sửa dữ liệu), mở tab Protection trong Format Cells (nhấp phải chuột/Format Cells/Protection):

Khóa và mở khóa (Protect/Unprotect) vùng dữ liệu trong bảng tính excel

Nhấp bỏ chọn dấu stick trong mục Locked, nhấp OK.

Nếu chỉ khóa vùng chọn: Bạn chọn toàn bộ bảng tính (nhấn tổ hợp phím Ctr + A) rồi thao tác các bước như trên.

Kế tiếp, bạn chọn vùng dữ liệu cần khóa, cũng thao thao tác tương tự như trên nhưng chọn lại dấu kiểm Locked trong tab Protection của Format Cells trước khi nhấn OK.

Nếu khóa toàn bộ bảng tính: bạn không cần phải thực hiện các bước trên vì Excel mặc định là Locked.

Tiếp theo, bạn nhấp Tab Review, chọn Protect sheet:

Khóa và mở khóa (Protect/Unprotect) vùng dữ liệu trong bảng tính excel

Menu Protect sheet xuất hiện như sau:

Khóa và mở khóa (Protect/Unprotect) vùng dữ liệu trong bảng tính excel

Trong phần Allow all uers of this worksheet to bạn chọn các tính năng cần khóa, xong nhập mật khẩu vào ô Password to unprotect sheet. Nhấp OK và lập lại mật khẩu lần nữa khi được nhắc.

Lúc này phần chọn của sheet của bạn đã bị khóa, khi nhấp chuột (hoặc nhấn F2) để sửa đổi sẽ không thực hiện được và excel hiện ra một thông báo "xám xịt" về tình trạng của sheet (sheet bị khóa, cần pass để mở).

Mở khóa bảng tính (Unprotect sheet)

Nếu bạn có mật khẩu để mở khóa vùng chọn bị khóa (Unprotect sheet) thì mọi việc quá dễ dàng, chỉ việc thao tác: nhấp Tab Review, chọn Unprotect sheet nhập mật khẩu vào hộp Unprotect sheet khi nó xuất hiện và nhấn OK là xong.

Tuy nhiên, nếu chẳng may bạn quên mật khẩu hoặc cần cập nhật thông tin trên sheet bị khóa của một file excel nào đó mà không pass thì phải xử lý thế nào?

May mắn là chúng ta vẫn có thể xử lý được tình huống này mà không cần phải dùng phần mềm của bên thứ 3 can thiệp, đó là sử dụng Visual Basic Editor.

Các bước thao tác như sau:

Đầu tiên, bạn cần mở file excel bị khóa lên, vào Visual Basic Editor bằng cách nhấn phím tắt ALT + F11. Bạn click chọn vào sheet cần mở khóa:

Khóa và mở khóa (Protect/Unprotect) vùng dữ liệu trong bảng tính excel

(Trong hình trên là flie phantichcongno_mau, sheet2 (tonghop))

Tiếp theo, bạn copy đọan code sau dán vào phần General để phá pass cho sheet cần mở khóa:

Sub PasswordBreaker()
If ActiveSheet.ProtectContents = False Then
    MsgBox "Sheet '" & ActiveSheet.Name & "' is unprotected!", vbInformation
Else
    If MsgBox("Sheet '" & ActiveSheet.Name & "' is protected, do you want to unprotect it?", _
    vbYesNo + vbQuestion, "Unprotect Active Sheet") = vbNo Then Exit Sub
    Dim i As Integer, j As Integer, k As Integer
    Dim l As Integer, m As Integer, n As Integer
    Dim i1 As Integer, i2 As Integer, i3 As Integer
    Dim i4 As Integer, i5 As Integer, i6 As Integer
    On Error Resume Next
    For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
    For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
    For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
    For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
        ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
        Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
        Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
    Next: Next: Next: Next: Next: Next
    Next: Next: Next: Next: Next: Next
    If ActiveSheet.ProtectContents = False Then MsgBox "Sheet '" & ActiveSheet.Name & "' is unprotected!", vbInformation
End If
End Sub

Truy cập vào menu Run → Run Sub/UserForm (hoặc nhấn F5) để chạy lệnh trên, nhấn Yes:

Khóa và mở khóa (Protect/Unprotect) vùng dữ liệu trong bảng tính excel

Nếu thành công, bạn cần xác nhận lại lệnh trên, nhấn OK để hoàn tất.

Như vậy, pass Protect sheet đã được gở bỏ, bạn hoàn toàn thao tác trên bảng tính này một cách bình thường.

Chúc các bạn thành công.
Ketoan.biz

Chèn nhanh bảng tính excel vào file word

Chèn nhanh bảng tính excel vào file word
Word và Excel là "anh em song sinh" tích hợp trong bộ Microsoft Office nên chúng có sự tương tác, hỗ trợ qua lại lẫn nhau khá tốt.

Chúng ta biết rằng bản thân word chuyên về soạn thảo văn bản, nên việc thực hiện các phép tính đơn thuần trên word khá khó khăn. Trong khi đó, excel lại mạnh về tính toán, có thể thực hiện nhiều phép tính từ đơn giản đến phức tạp thông qua các hàm, công thức.
Liệu có cách nào khi chúng ta đang soạn thảo văn bản trên word, cần nhu cầu tính toán thì có thực hiện được các phép tính như trên bảng tính excel- vốn rất quen thuộc hay không? Câu trả lời là hoàn toàn có thể khi bạn thực hiện chèn bảng tính excel vào word với một vài thao tác khá đơn giản.

Bài viết sau sẽ được trình bày trên Office 2016 theo 2 cách: Chèn cả bẳng tính excel vào word bằng Object và chèn một phần bảng bảng tính excel vào word bằng Paste Special.

1. Chèn file excel vào word bằng Object

Đầu tiên bạn mở file word cần chèn bảng tính excel lên. Tại menu Insert, đặt trỏ chuột tại vị trí muốn chèn bảng tính, click chọn mũi tên trỏ xuống của tab Object, chọn Object ...

Chèn nhanh bảng tính excel vào file word

Hộp thoại Object xuất hiện:

Chèn nhanh bảng tính excel vào file word

Tại đây, chúng ta có hai lựa chọn: Create New nếu muốn chèn bảng tính mới (trắng), Create from File nếu muốn chèn bảng tính từ một file có sẵn.

Trong hình trên chúng ta đang chọn  Create New, và chọn Microsoft Excel 97-2003 Worksheet. Xong, nhấn OK để chèn bảng tính vào word.

Nếu bạn cần chèn file excel có sẵn vào word, hãy chọn Create from File, như hình sau:

Chèn nhanh bảng tính excel vào file word

Tại ô File name gõ đường dẫn chứa file hoặc click chọn Browse để tìm nơi chứa file excel cần chèn vào word, nhấn Insert hoặc Open. Xong nhấn OK.

Chèn nhanh bảng tính excel vào file word

Ở chế độ soạn thảo văn bản word (như trên), bạn có thể điều chỉnh khối bảng tính khi rê chuột vào các 6 nút chấm, kéo thả để tăng/ giảm kích thước khối.

Muốn bảng tính chuyển sang chế độ excel, bạn nhấp đúp chuột trái lên bảng tính. Lúc này, bạn có thể thao tác bảng tính như trên excel, như hình sau:

Chèn nhanh bảng tính excel vào file word
Muốn trở về chế độ soạn thảo văn bản của word, bạn chỉ cần nhấp chuột ngoài vùng bảng tính excel là được.

2. Chèn một phần bảng tính excel vào word bằng Paste Special

Khác trường hợp trên là chèn cả (file) bảng tính vào văn bản word, trường hợp sau chúng ta chỉ chèn một vùng (lựa chọn) của bảng tính excel đưa vào word.

Trường hợp này, bạn copy một vùng bảng tính excel để chèn vào văn bản word nhưng vẫn thực hiện được các tính toán như trên excel.

Tại bảng tính excel, chọn vùng dữ liệu cần copy, nhấn Ctr + C (như hình sau):

Chèn nhanh bảng tính excel vào file word

Trở về phần soạn thảo văn bản word, tại Menu Home → Chọn PastePaste Special, như sau:

Chèn nhanh bảng tính excel vào file word

Xuất hiện hộp thoại Paste Special:

Chèn nhanh bảng tính excel vào file word

Chọn Microsoft Excel 2003 Worksheet Object (như hình trên) trong vùng As, xong nhấn OK. Ngay sau đó, vùng bảng tính sẽ được đưa vào văn bản word:

Chèn nhanh bảng tính excel vào file word

Nếu thao tác đúng, bạn có thể thực hiện các phép tính như trên excel. Việc chuyển sang chế độ excel, bạn chỉ việc nhấp đúp chuột lên vùng bảng tính như cách Chèn excel vào word bằng Object trình bày ở trên.

Việc chèn bảng tính excel vào word khá thuận tiện cho các bạn khi lập các báo cáo, giải trình trên word mà có "đụng" đến excel. Ứng dụng này được kế toán áp dụng khi lập Bản thuyết minh báo cáo tài chính trên file word khá hiệu quả.

Chúc các bạn thành công.
Ketoan.biz

Cách thêm hộp check box vào excel, word

Thường khi thêm check box, chúng ta hay vào Tab Insert → Symboy để chèn hộp kiểm cố định vào văn bản. Tuy nhiên cách này chỉ phù hợp cho dạng văn bản in ra giấy, việc cần khảo sát online sẽ gây khá bất tiện do người dùng không thể tự tích chọn được.

Giải pháp tốt nhất có lẻ là dùng hộp check box tích hợp trong Developer, cả excel và word đều có thể áp dụng được. Hộp kiểm dạng này cho phép người dùng có thể tích chọn / bỏ chọn dễ dàng, thích hợp cho các cuộc khảo sát, thăm dò ý kiến online, tăng tính hiệu quả, chuyên nghiệp.

Bài viết sau sẽ trình bày việc chèn hộp check box vào bảng tính, văn bản với Office 2016.

Cách thêm hộp check box vào excel, word

Data Validation: Thuộc tính hay thiết lập cố định giá trị trong ô excel

Đôi khi làm việc một bảng tính excel, các mẫu biểu với những trường định sẳn, việc cố định những trường này với một giá trị được chỉ định trước là cần thiết, nhằm tránh một số sai sót khi nhập liệu.

Data Validation- Một tính năng hay của excel được tích hợp trong menu DATA, giúp chúng ta cố định nhanh giá trị của một cell (hoặc một số cell được lựa chọn) với một giá trị được chỉ định trước, tức nhập liệu vào ô phải thỏa mãn điều kiện được định trước nào đó.

Trong ví dụ minh họa dưới đây, chúng ta thiết lập số tiền "Văn phòng phẩm" tại ô C3 với thiết lập là không quá 2 triệu đồng khi nhập liệu vào ô này. Để làm được điều này, chúng ta tiến hành một số thao tác như sau:

Đặt trỏ chuột tại ô C3 (có thể chọn nhiều ô), nhấp chuột vào menu DATA và nhấp tiếp tab Data Validation → chọn Data Validation:

Data Validation: Thuộc tính hay thiết lập cố định giá trị trong ô excel

Hộp thoại Data Validation xuất hiện như hình bên dưới:

Data Validation: Thuộc tính hay thiết lập cố định giá trị trong ô excel1


Nhấp chọn tab Settings, trong hộp Allow là các tùy chọn điều kiện để chúng ta lựa chọn. Do điều kiện của ô C3 là số tiền không quá 2 triệu đồng, nên chúng ta chọn Whole nembers.


Data Validation: Thuộc tính hay thiết lập cố định giá trị trong ô excel2

Mục Data cho ta nhiều lựa chọn, ở đây chúng ta chọn between, mục Minimun nhập vào số 0, mục Maximun nhập vào 2000000, tức điều kiện hợp lệ trong ô C3 mà chúng ta cần xác lập là số từ 0 đến 2.000.000.

(Lưu ý, mục Minimun, Maximun có thể tham chiếu đến dữ liệu của sheet khác vẫn được)

Tiếp theo, tại Tab Input Message chúng ta nhập thông báo nhắc nhở người nhập liệu nhập đúng điều kiện đã thiết lập. Mục Error Alert nhập vào lời cảnh báo khi người nhập liệu nhập sai điều kiện. Các bạn xem hình bên dưới:

Data Validation: Thuộc tính hay thiết lập cố định giá trị trong ô excel3


Lưu ý: mục Style chúng ta chọn stop (như hình trên) thì bắt buộc phải nhập đúng điều kiện, chọn 2 thuộc tính còn lại thì Excel chỉ xuất hiện cảnh báo khi nhập sai điều kiện.

Đến đây, việc thiết lập điều kiện nhập liệu cho ô C3 đã hoàn tất, các bạn nhấp chọn nút OK để xem kết quả:

Data Validation: Thuộc tính hay thiết lập cố định giá trị trong ô excel4

Nếu các bạn nhập liệu vào ô C3 đúng điều kiện (từ dưới 2 triệu) thì mọi việc có vẻ "êm xuôi", nếu không (>2 triệu) thì bạn sẽ nhận ngay một cảnh báo như trên. Lúc này, chỉ có nước nhấp chọn Retry để sửa lại số cho đúng mà thôi!

Hy vọng với tính năng Data Validation đã trình bày ở trên sẽ hữu ích cho các bạn khi vận dụng vào công việc.

Ketoan.biz

Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns

Trong công việc của dân văn phòng, kế toán thường phải làm việc trên các files excel được trích xuất ra từ phần mềm quản lý như phần mềm quản lý nhân sự, phần mềm kế toán ... Tuy nhiên các files này dữ liệu lại "dính" chung với nhau được ngăn cách bởi các dấu "/". "*", "-", ":" ... việc cần làm là phải tách riêng dữ liệu trước khi xử lý, lập các báo cáo phân tích số liệu.

Có nhiều cách tách dữ liệu ra trước và sau các dấu "/". "*", "-", ":" ... như sử dụng công thức kết hợp các hàm tích hợp trong excel để tách chúng. Việc sử dụng hàm nâng cao trong excel để xử lý dữ liệu không phải ai cũng làm được bởi tính phức tạp của nó. May mắn thay, excel đã tích hợp sẳn tính năng tách dữ liệu rất nhanh và cực kỳ đơn giản, đó là tính năng Text to Columns.

Tính năng Text to Columns là một ứng dụng hay của Excel ẩn trong tab Data chuyên tách các dữ liệu thành cột riêng được chỉ định cụ thể.

Trong ví dụ minh họa bên dưới, chúng ta cần tách số liệu (ngăn cách bởi dẫu "-") sang cột riêng, ngày tháng sang cột riêng.

Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns

Đầu tiên, bạn chọn vùng dữ liệu cần xử lý, đồng thời click chuột chọn Tab DataText to Columns, xuất hiệu hộp thoại sau:

Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns1

Tiếp theo, các bạn chọn Dellimited rồi click Next để chuyển sang bước kế tiếp:

Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns2

Ở hình trên, các bạn nhấp chọn dấu kiểm mục Other và nhập vào ô vuông dấu "-", xong nhấp Next Finish để xem kết quả:

Nếu thao tác dúng, chúng ta sẽ được kết quả như hình bên dưới: Tách dữ liệu số và ngày tháng ra 2 cột E và F.

Tách dữ liệu nhanh chóng trong Excel với tính năng Text to Columns4

Lưu ý:

Việc tách dữ liệu trước và sau dấu "," bạn nhấp chọn hộp Comma, tách dữ liệu giữa các khoản trắng bạn chọn hộp Space như hình trên.

Ngoài ra, tách dữ liệu trước và sau dấu bất kỳ ("/". "*", "-", ":" ...), bạn nhấp chọn mục Other rồi nhập vào ô vuông dấu mà mình muốn tách chúng.

Hy vọng với thủ thuật đơn giản trên sẽ hữu ích cho các bạn trong công việc khi phải xử lý một file excel với các tình huống, trường hợp tương tự.
Ketoan.biz

Thủ thuật tách gộp họ tên trong Excel không cần phần mềm

Trong công việc, đôi khi chúng ta cần phải tách họ và tên riêng trong một danh sách Excel nào đó, từ file Word chuyển qua Excel chẳng hạn.

Trong trường hợp sau, chúng ta cần tách tên riêng một cột và họ (tên lót) riêng một cột trong một bảng tính excel. Công việc sẽ trở nên đơn giản khi chúng ta thực hiện một số thủ thuật sau đây:

1. Đầu tiên, cần copy danh sách họ tên tại cột B sang cột khác, cột E. Chọn vùng danh sách cột E (bôi đen vùng chọn cột E) như hình bên dưới;

2. Nhấn tổ hợp phím Ctrl + F để mở để mở hộp tìm kiếm và thay thuế (Find and Replace), chọn tab Replace để tách tên.

Trong hộp Find what, đánh dấu * và một khoản trắng (nhấn phím * và phím spacebar); Hộp Relace with thì để trống (không làm gì cả);

Mục đích là excel sẽ xóa từ ký tự trắng đầu tiên bên trái cell bảng tính, tức chỉ giữ lại tên.


Thủ thuật tách gộp họ tên trong Excel dân văn phòng nên biết



Chọn Replace All, bạn sẽ thấy hiệu quả ngay lập tức.


Nếu thao tác đúng, bạn sẽ được kết quả như hình sau: Tách được tên riêng trong danh sách tại cột E.

Thủ thuật tách gộp họ tên trong Excel dân văn phòng nên biết 2


3. Tách họ và tên lót vào cột D:

Công việc còn lại cũng khá đơn giản, tại ô D3 nhập vào công thức: =LEFT(B3,LEN(B3)-LEN(E3))


Thủ thuật tách gộp họ tên trong Excel dân văn phòng nên biết 3

Copy xuống dưới với nhứng ô còn lại, kết quả chúng ta đã tách được danh sách với tên và họ thành hai cột riêng.

4. Gộp tên họ từ nhiều cột thành một cột

Tức là yêu cầu công việc, quy trình làm ngược lại như đã trình bày ở trên. Chẳng hạn, chúng ta có một danh sách với tên họ nằm ở hai cột D và E và giờ muốn gộp chúng lại thì phải làm thế nào?

Công việc trở nên khá đơn giản: Đó là sử dụng "&" hoặc hàm Concat(Text1,...) để nối chúng lại với nhau:

Tại ô F3, gỏ vào công thức: =D3&" "&E3

Hoặc công thức: =CONCAT(D3," ",E3)

Thủ thuật tách gộp họ tên trong Excel không cần phần mềm

Copy công thức xuống dưới cho những ô còn lại, ta sẽ được kết quả như hình trên.

Lưu ý: 

Nếu tách riêng Họ thành một cột, Tên và Tên lót riêng riêng một cột thì chúng ta thực hiện tương tự như trên,  nhưng tại bước 2 trong hộp Find what, chúng ta nhập khoản trắng trước rồi mới đến dấu * (chỉ để tách họ riêng một cột); Tại bước 3, tại ô F3, chúng ta gỏ vào công thức =RIGHT(B3,LEN(B3)-LEN(E3)).

Nếu bản danh sách ban đầu khi trích xuất mà có các khoản cách giữa các chữ không đồng nhất (trước và sau các chữ có khoản trắng thừa) thì chúng ta buộc phải xử lý loại bỏ khoản trắng thừa rồi mới áp dụng các thủ thuật trên. Loại bỏ khoản trắng bị thừa bằng công thức sau =TRIM(SUBSTITUTE(B3,CHAR(160),"")) với B3 là ô cần xử lý.

Hy vọng với chia sẻ trên sẽ hữu ích cho các bạn trong công việc khi phải "tự tay" tách hay gộp nhiều cột với nhau mà không phải sử dụng đến các phần mềm phức tạp can thiệp.
Ketoan.biz

Ứng dụng hàm SumProduct trong kế toán

Hàm SumProduct có hầu hết ở các phiên bản Excel, đây là hàm tính tổng tích các phần tử trong mảng một cách khá đơn giản. 

Với hàm SumProduct, giúp ta tính tổng của tích các phần tử trong hai hay nhiều mảng khác nhau, giúp cho kế toán dễ dàng chọn lọc cũng như phân tích số liệu một cách linh hoạt và hiệu quả.

Cú Pháp: 

SUMPRODUCT(Array1, [Array2], [Array3],...)

Trong đó:

- Array1: Bắt buộc

- Array2, Array3: Tùy chọn

Đối số của hàm SUMPRODUCT đều là các mảng, có thể có đến 256 mảng. Các đối số trong mỗi mảng phải có cùng kích thước, nghĩa là có cùng số phần tử.
Khi gặp các phần tử của mảng không phải ở dạng số, hàm SUMPRODUCT coi là số 0.

Bây giờ chúng ta xem xét bảng tính bên dưới: đây là bảng kê hàng nhập kho trong tháng 5, 6, 7 (bảng tính ở trên) được lập trên cùng 1 sheet.

Ứng dụng hàm SumProduct trong kế toán

Để tính tổng giá trị hàng nhập trong các tháng, tại ô G19, chúng ta gỏ vào công thức  =SUMPRODUCT(E3:E17,F3:F17), kết quả là 141,450.

Nếu dùng hàm SumProduct để tính tổng trong trường hợp này thì quá đơn giản, ngay cả hàm Sum cũng có thể làm được.

Ta xét một yêu cầu cao hơn, chẳng hạn thống kê, tính tổng giá trị điện thoại Iphone5 (mã IP5) trong tháng 5 thì tính thế nào? Trường hợp này hàm Sumif cũng "bó tay" vì nó không tính tổng riêng cho từng tháng được.

Giải pháp tối ưu trong trường hợp này là sử dụng hàm SumProduct.

Để dễ thao tác, chúng ta lập thêm Bảng tổng hợp (bảng nhỏ ở dưới), trong đó có mã hàng, tên hàng và số tháng cần thống kê.

Tại ô D25, ta gỏ vào công thức: 

=SUMPRODUCT((MONTH($A$3:$A$17)=D$24)*($B$3:$B$17=$B25),$G$3:$G$17)

Kết quả như đã thấy, ô D25 bằng 24,500 là tổng giá trị hàng nhập trong tháng 5 của điện thoại Iphone5.

Giải thích các tham số:
MONTH($A$3:$A$17) lấy tháng của cột ngày tháng
D24 là tháng cần tổng hợp, ở đây là tháng 5.
$B25 là mã hàng cần tổng hợp, ở đây là IP5.
$B$3:$B$17 là vùng tham chiếu mã cần tổng hợp.
$G$3:$G$17 là vùng tham chiếu tính tổng giá trị hàng nhập.

Để tính tính tổng giá trị hàng nhập trong tháng 6 cũng là điện thoại Iphone5, chúng ta copy công thức tại ô D25 dán vào ô E25, hay ô F25 nếu muốn tính tổng giá trị IP5 trong tháng 7.

Tương tự, chúng ta có thể tính tổng các mã hàng hóa còn lại theo các tháng bằng cách copy công thức từ ô D25 dán vào các ô tuong ứng.

Để tiện tham khảo, các bạn có thể tải file Excel ví dụ trên tại đây.

Hy vọng với hàm SumProduct đã trình bày ở trên sẽ phần nào đáp ứng được công việc kế toán của các bạn.
Ketoan.biz

Ứng dụng Excel trong kế toán với các hàm tính tổng

Ứng dụng Excel trong kế toán với các hàm tính tổng
Excel là một công cụ hổ trợ đắc lực cho kế toán trong công việc, từ tính toán các bảng lương, bảng tính giá thành … cho đến thiết lập sổ sách kế toán. Một trong những hàm thông dụng của excel phải kể đến là các hàm tính tổng, giúp kế toán xử lý nhiều tính toán một cách nhanh chóng.

1. HÀM SUMIF:

Nếu bạn không hài lòng với với hàm tính tổng đơn giản là Sum thì bạn có thể tìm hiểu thêm hàm Sumif- tính tổng có điều kiện, giúp bạn tính nhanh với những đối số được chỉ định. Hàm Sumif có hầu hết trong các phiên bản Excel.

Cú pháp: =SUMIF(range,criteria,sum_range)

Trong đó: 

+ Range: Là vùng được chọn có chứa các ô điều kiện.

+ Criteria: Là điều kiện để tính tổng.

+ Sum_range: Vùng cần tính tổng.

 Trong ví dụ (1) tính tổng tiền lương của chức vụ quản lý (QL): Tại ô E24, nhâp vào công thức như hình (1), với Range từ C7-C20, Criteria điều kiện là "QL" (ô C24), Sum_range vùng tính tổng từ J7-J20, kết quả bằng 13.728.846

2. HÀM SUMIFS:

Nếu hàm Sumif chỉ cho phép bạn tính tổng với một điều kiện duy nhất thì hàm Sumifs cho phép bạn tính tổng với nhiều điều kiện cùng lúc. Hàm Sumifs sinh ra là để khắc phục nhược điểm của hàm Sumif, nó xuất hiện từ phiên bản excel 2007 trở lên.

Cú pháp: =SUMIFS (sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…)

Trong đó:

+ sum_range: bắt buộc, là một vùng các ô để tính tổng cho các ô có giá trị chuỗi, nếu rỗng thì được bỏ qua.
+ criteria_range1: bắt buộc, là một vùng các ô để so sánh với điều kiện tính tổng thứ nhất. 
+ criteria1: bắt buộc, là điều kiện để tính tổng, giá trị của criterial có thể là số, biểu thức, cột tham chiếu, chuỗi.
+ criteria_range2, criteria2, … là tùy chọn, không bắt buộc. Excel cho phép tối đa là 127 cặp điều kiện criteria_range/criteria.

Trong ví dụ (2) tính tổng lương của những người có chức vụ là "Thợ" của bộ phận "Mộc", tại ô E26 điền công thức như hình trên của trường hợp (2), với sum_range là vùng cần tính tổng  từ J7-J20, vùng chứa điều kiện thứ nhất criteria_range1 từ C7-C20, điều kiện thứ nhất criteria1 là chức vụ "Thợ"  là ô C26; Tương tự vùng chứa điều kiện thứ 2 và điều kiên thứ 2 là D7-D20 và D26. Kết quả tại ô E26 bằng 19.273.077

3. HÀM DSUM

Nếu bạn cảm thấy hàm Sumifs hơi phức tạp, thì hàm Dsum được biết đến như một hàm tính tổng rất nhanh mà công thức cũng rất đơn giản. Dsum cũng là hàm tính tổng có điều kiện, nó có hầu hết trong các phiên bản Excel. Hàm Dsum dùng để tính tổng ở trường dữ liệu (Field) trong bảng dữ liệu (Database) thỏa mãn điều kiện (Criterial) cho trước.

Cú pháp: =Dsum(Database,Field,Criterial)

Trong đó:

+ Database: Bảng dữ liệu bao gồm cả cột Tiêu đề

+ Field: Địa chỉ của trường cần tính tổng

+ Criterial: Điều kiện để tính tổng.

Trong ví dụ (3) chúng ta cũng tính tổng lương của những người có chức vụ là "Thợ" của bộ phận "Mộc" xem có đúng với ví dụ (2) không nhé.

Đầu tiên bạn cần tạo bảng điều kiện (Criterial) như hình trên (A32-J33).
Tại ô E29, bạn điền vào công thức như hình trên (trường hợp 3) với bảng dữ liệu (Database) từ A6-J20 (bao gồm cả tiêu đề bảng), trường cần tính tổng (Field) là "Tổng lương", điều kiện để tính tổng (Criterial) từ (A32-J33). Kết quả tại ô E29 cũng bằng 19.273.077, đúng với kết quả trường hợp (2).

Trên đây là một số ví dụ về việc sử dụng hàm Sumif, Sumifs, Dsum, qua đó các bạn cũng nhận thấy dược sự tương đồng cũng như khác nhau của 3 hàm này. Hy vọng các hàm trên sẽ hữu ích cho các bạn khi ứng dụng vào công việc.
Ketoan.biz

Một số hàm Excel cơ bản dùng trong kế toán

Một số hàm Excel cơ bản dùng trong kế toán
(Ảnh minh họa, nguồn internet)

I. HÀM LOGIC

 1. Hàm AND

- Cú pháp:   AND (Logical1, Logical2, ….)

- Các đối số:   Logical1, Logical2… là các biểu thức điều kiện.

Hàm trả về giá trị TRUE (1) nếu tất cả các đối số của nó là đúng, trả về giá trị FALSE (0) nếu một hay nhiều đối số của nó là sai.

2. Hàm OR

- Cú pháp:   OR (Logical1, Logical2…)

- Các đối số: Logical1, Logical2… là các biểu thức điều kiện.

- Hàm trả về giá trị TRUE (1) nếu bất cứ một đối số nào của nó là đúng, trả về giá trị FALSE (0) nếu tất cả các đối số của nó là sai.

- Ví dụ:  =OR(F7>03/02/74,F7>01/01/2002)

3. Hàm NOT

- Cú pháp:   NOT(Logical)

- Đối số: Logical là một giá trị hay một biểu thức logic.

- Hàm đảo ngược giá trị của đối số. Sử dụng NOT khi bạn muốn phủ định giá trị của đối số trong phép toán này.

II. NHÓM HÀM TOÁN HỌC

1. Hàm ABS

Lấy giá trị tuyệt đối của một số

- Cú pháp: ABS(Number)

- Đối số: Number là một giá trị số, một tham chiếu hay một biểu thức.

- Ví dụ:   =ABS(A5 + 5)

2. POWER

Hàm trả về lũy thừa của một số.

- Cú pháp: POWER(Number, Power)

- Các tham số:  Number: Là một số thực mà bạn muốn lấy lũy thừa.

- Power: Là số mũ.

- Ví dụ    = POWER(5,2) = 25

3. Hàm PRODUCT

Bạn có thể sử dụng hàm PRODUCT thay cho toán tử nhân * để tính tích của một dãy.

- Cú pháp:    PRODUCT(Number1, Number2…)

- Các tham số: Number1, Number2… là dãy số mà bạn muốn nhân.

4. Hàm MOD

Lấy giá trị dư của phép chia.

- Cú pháp: MOD(Number, pisor)

- Các đối số:   Number: Số bị chia.

- pisor: Số chia.

5. Hàm ROUNDUP

Làm tròn một số.

-  Cú pháp:  ROUNDUP(Number, Num_digits)

- Các tham số:   Number: Là một số thực mà bạn muốn làm tròn lên.

Number_digits: là bậc số thập phân mà bạn muốn làm tròn.

- Chú ý:

  – Nếu Num_digits > 0 sẽ làm tròn phần thập phân.

  – Nếu Num_digits = 0 sẽ làm tròn lên số tự nhiên gần nhất.

  – Nếu Num_digits < 0 sẽ làm tròn phần nguyên sau dấu thập phân.

6. Hàm EVEN

- Làm tròn lên thành số nguyên chẵn gần nhất.

- Cú pháp: EVEN(Number)

- tham số: Number là số mà bạn muốn làm tròn.

- Chú ý:

- Nếu Number không phải là kiểu số thì hàm trả về lỗi #VALUE!

 7. Hàm ODD

Làm tròn lên thành số nguyên lẻ gần nhất.

- Cú pháp: ODD(Number)

- Tham số: Number là số mà bạn muốn làm tròn.

8. Hàm ROUNDDOWN

Làm tròn xuống một số.

- Cú pháp:   ROUNDDOWN(Number, Num_digits)

- Các tham số: tương tự như hàm ROUNDUP.

 III. NHÓM HÀM THỐNG KÊ

A. Nhóm hàm tính tổng

1. Hàm SUM

Cộng tất cả các số trong một vùng dữ liệu được chọn.

- Cú pháp:   SUM(Number1, Number2…)

- Các tham số: Number1, Number2… là các số cần tính tổng.

2. Hàm SUMIF : 

Tính tổng của các ô được chỉ định bởi những tiêu chuẩn đưa vào.

- Cú pháp:  SUMIF(Range, Criteria, Sum_range)

- Các tham số:  Range: Là dãy mà bạn muốn xác định.

- Criteria: các tiêu chuẩn mà muốn tính tổng. Tiêu chuẩn này có thể là số, biểu thức hoặc chuỗi.

- Sum_range: Là các ô thực sự cần tính tổng.

- Ví dụ:    = SUMIF(B3:B8,”<=10″)

- Tính tổng của các giá trị trong vùng từ B2 đến B5 với điều kiện là các giá trị nhỏ hơn hoặc bằng 10.

B. NHÓM HÀM TÍNH GIÁ TRỊ TRUNG BÌNH

1. Hàm AVERAGE

Trả về gi trị trung bình của các đối số.

- Cú pháp:    AVERAGE(Number1, Number2…)

- Các tham số: Number1, Number2 … là các số cần tính giá trị trung bình.

2. Hàm SUMPRODUCT

Lấy tích của các dãy đưa vào, sau đó tính tổng của các tích đó.

- Cú pháp:    SUMPRODUCT(Array1, Array2, Array3…)

- Các tham số: Array1, Array2, Array3… là các dãy ô mà bạn muốn nhân sau đó tính tổng các tích.

- Chú ý:

-  Các đối số trong các dãy phải cùng chiều. Nếu không hàm sẽ trả về giá trị lỗi #VALUE.

C. NHÓM HÀM TÍNH GIÁ TRỊ LỚN NHẤT, NHỎ NHẤT

1. Hàm MAX

Trả về số lớn nhất trong dãy được nhập.

- Cú pháp:   MAX(Number1, Number2…)

- Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị lớn nhất ở trong đó.

2. Hàm LAGRE

Tìm số lớn thứ k trong một dãy được nhập.

- Cú pháp:   LARGE(Array, k)

- Các tham số:  Array: Là một mảng hoặc một vùng dữ liệu.

  k: Là thứ hạng của số bạn muốn tìm kể từ số lớn nhất trong dãy.

3. Hàm MIN

Trả về số nhỏ nhất trong dãy được nhập vào.

- Cú pháp:   MIN(Number1, Number2…)

- Các tham số: Number1, Number2… là dãy mà bạn muốn tìm giá trị nhỏ nhất ở trong đó.

4. Hàm SMALL

Tìm số nhỏ thứ k trong một dãy được nhập vào.

- Cú pháp:  SMALL(Array, k)

- Các tham số:   Array: Là một mảng hoặc một vùng của dữ liệu.

 k: Là thứ hạng của số mà bạn muốn tìm kể từ số nhỏ nhất trong dãy.

D. NHÓM HÀNG ĐẾM DỮ LIỆU

1. Hàm COUNT

Hàm COUNT đếm các ô chứa dữ liệu kiểu số trong dãy.

- Cú pháp:  COUNT(Value1, Value2, …)

- Các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

2. Hàm COUNTA

Đếm tất cả các ô chứa dữ liệu.

- Cú pháp:  COUNTA(Value1, Value2, …)

- Các tham số: Value1, Value2… là mảng hay dãy dữ liệu.

3. Hàm COUNTIF

Hàm COUNTIF đếm các ô chứa giá trị số theo một điều kiện cho trước.

- Cú pháp:   COUNTIF(Range, Criteria)

- Các tham số:   Range: Dãy dữ liệu mà bạn muốn đếm.

- Criteria: Là tiêu chuẩn cho các ô được đếm.

 Ví dụ:  = COUNTIF(B3:B11,”>100″): (Đếm tất cả các ô trong dãy B3:B11 có chứa số lớn hơn 100)

IV. NHÓM HÀM CHUỖI

1. Hàm LEFT

Trích các ký tự bên trái của chuỗi nhập vào.

- Cú pháp: LEFT(Text,Num_chars)

- Các đối số: Text: Chuỗi văn bản.

Num_Chars: Số ký tự muốn trích.

Ví dụ:  =LEFT(Tôi tên là,3) = “Tôi”

2. Hàm RIGHT

Trích các ký tự bên phải của chuỗi nhập vào.

- Cú pháp: RIGHT(Text,Num_chars)

- Các đối số: tương tự hàm LEFT.

Ví dụ:  =RIGHT(Tôi tên là,2) = “là”

3. Hàm MID

Trích các ký tự từ số bắt đầu trong chuỗi được nhập vào.

- Cú pháp:  MID(Text,Start_num, Num_chars)

- Các đối số: Text: chuỗi văn bản.

  Start_num: Số thứ tự của ký tự bắt đầu được trích.

  Num_chars: Số ký tự cần trích.

4. Hàm UPPER

  Đổi chuỗi nhập vào thành chữ hoa.

- Cú pháp: UPPER(Text)

5. Hàm LOWER

  Đổi chuỗi nhập vào thành chữ thường.

- Cú pháp: LOWER(Text)

6. Hàm PROPER

  Đổi ký từ đầu của từ trong chuỗi thành chữ hoa.

- Cú pháp: PROPER(Text)

  Ví dụ:  =PROPER(phan van a) = “Phan Van A”

7. Hàm TRIM

  Cắt bỏ các ký tự trắng ở đầu chuỗi và cuối chuỗi.

- Cú pháp: TRIM(Text)

V. NHÓM HÀM NGÀY THÁNG

1. Hàm DATE

  Hàm Date trả về một chuỗi trình bày một kiểu ngày đặc thù.

- Cú pháp: DATE(year,month,day)

- Các tham số: Year: miêu tả năm, có thể từ 1 đến 4 chữ số. Nếu bạn nhập 2 chữ số, theo mặc định Excel sẽ lấy năm bắt đầu là: 1900.(Ví dụ)

  Month: miêu tả tháng trong năm. Nếu month lớn hơn 12 thì Excel sẽ tự động tính thêm các tháng cho số miêu tả năm.

  Day: miêu tả ngày trong tháng. Nếu Day lớn hơn số ngày trong tháng chỉ định, thì Excel sẽ tự động tính thêm ngày cho số miêu tả tháng.

Lưu ý:

  Excel lưu trữ kiểu ngày như một chuỗi số liên tục, vì vậy có thể sử dụng các phép toán cộng (+), trừ (-) cho kiểu ngày.(Ví dụ)

2. Hàm DAY

  Trả về ngày tương ứng với chuỗi ngày đưa vào. Giá trị trả về là một số kiểu Integer ở trong khoảng từ 1 đến 31.

- Cú pháp: DAY(Serial_num)

- Tham số: Serial_num: Là dữ liệu kiểu Date, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.

3. Hàm MONTH

  Trả về tháng của chuỗi ngày được mô tả. Giá trị trả về là một số ở trong khoảng 1 đến 12.

- Cú pháp: MONTH(Series_num)

- Tham số: Series_num: Là một chuỗi ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.

4. Hàm YEAR

  Trả về năm tương ứng với chuỗi ngày đưa vào. Year được trả về là một kiểu Integer trong khoảng 1900-9999.

- Cú pháp: YEAR(Serial_num)

- Tham số:

- Serial_num: Là một dữ liệu kiểu ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác

5. Hàm TODAY

  Trả về ngày hiện thời của hệ thống.

- Cú pháp: TODAY()

- Hàm này không có các đối số.

6. Hàm WEEKDAY

  Trả về số chỉ thứ trong tuần.

- Cú pháp: WEEKDAY(Serial, Return_type)

- Các đối số:   Serial: một số hay giá trị kiểu ngày.

                       Return_type: chỉ định kiểu dữ liệu trả về.

 VI. HÀM VỀ THỜI GIAN

1. Hàm TIME

  Trả về một chuỗi trình bày một kiểu thời gian đặc thù. Giá trị trả về là một số trong khoảng từ 0 đến 0.99999999, miêu tả thời gian từ 0:00:00 đến 23:59:59.

- Cú pháp:  TIME(Hour,Minute,Second)

- Các tham số: Được tính tương tự ở hàm DATE.

- Hour: miêu tả giờ, là một số từ 0 đến 32767.

- Minute: miêu tả phút, là một số từ 0 đến 32767.

- Second: miêu tả giây, là một số từ 0 đến 32767.

2. Hàm HOUR

- Trả về giờ trong ngày của dữ liệu kiểu giờ đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 (12:00A.M) đến 23 (11:00P.M).

- Cú pháp: HOUR(Serial_num)

- Tham số:

  Serial_num: Là dữ liệu kiểu Time. Thời gian có thể được nhập như:

  Một chuỗi kí tự nằm trong dấu nháy (ví dụ “5:30 PM”)
  
  Một số thập phân (ví dụ 0,2145 mô tả 5:08 AM)

  Kết quả của một công thức hay một hàm khác.

3. Hàm MINUTE

  Trả về phút của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

- Cú pháp: MINUTE(Serial_num)

- Tham số:   Serial_num: Tương tự như trong công thức HOUR.

4. Hàm SECOND

  Trả về giây của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.

- Cú pháp: SECOND(Serial_num)

- Tham số: Serial_num: Tương tự như trong công thức HOUR.

5. Hàm NOW

  Trả về ngày giờ hiện thời của hệ thống.

- Cú pháp: NOW()

- Hàm này không có các đối số.

VII. NHÓM HÀM DÒ TÌM DỮ LIỆU

1. Hàm VLOOKUP

  Tìm ra một giá trị khác trong một hàng bằng cách so sánh nó với các giá trị trong cột đầu tiên của bảng nhập vào.

- Cú pháp: VLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])

- Các tham số:
  
  Lookup Value: Giá trị cần đem ra so sánh để tìm kiếm.

  Table array: Bảng chứa thông tin mà dữ liệu trong bảng là dữ liệu để so sánh. Vùng dữ liệu này phải là tham chiếu tuyệt đối.

  Nếu giá trị Range lookup là TRUE hoặc được bỏ qua, thì các giá trị trong cột dùng để so sánh phải được sắp xếp tăng dần.

  Col idx num: số chỉ cột dữ liệu mà bạn muốn lấy trong phép so sánh.

  Range lookup: Là một giá trị luận lý để chỉ định cho hàm VLOOKUP tìm giá trị chính xác hoặc tìm giá trị gần đúng. + Nếu Range lookup là TRUE hoặc bỏ qua, thì giá trị gần đúng được trả về.

Chú ý:

- Nếu giá trị Lookup value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng Table array, nó sẽ thông báo lỗi #N/A.

- Ví dụ:   =VLOOKUP(F11,$C$20:$D$22,2,0)

- Tìm một giá trị bằng giá trị ở ô F11 trong cột thứ nhất, và lấy giá trị tương ứng ở cột thứ 2.

 2. Hàm HLOOKUP

   Tìm kiếm tương tự như hàm VLOOKUP nhưng bằng cách so sánh nó với các giá trị trong hàng đầu tiên của bảng nhập vào.

- Cú pháp:  HLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])

- Các tham số tương tự như hàm VLOOKUP.

3. Hàm INDEX

Trả về một giá trị hay một tham chiếu đến một giá trị trong phạm vi bảng hay vùng dữ liệu.

- Cú pháp: INDEX(Array,Row_num,Col_num)

 Các tham số:

- Array: Là một vùng chứa các ô hoặc một mảng bất biến.

- Nếu Array chỉ chứa một hàng và một cột, tham số Row_num hoặc Col_num tương ứng là tùy ý.

- Nếu Array có nhiều hơn một hàng hoặc một cột thì chỉ một Row_num hoặc Col_num được sử dụng.

- Row_num: Chọn lựa hàng trong Array. Nếu Row_num được bỏ qua thì Col_num là bắt buộc.

- Col_num: Chọn lựa cột trong Array. Nếu Col_num được bỏ qua thì Row_num là bắt buộc.

4. Hàm RANK

Dò tìm sắp xếp thứ hạng của một trong danh sách vùng dữ liệu (xếp thứ hạng danh sách).

- Cú pháp: RANK(Number,Ref,[Order])

- Các tham số: 

Number: Số mà bạn muốn tìm thứ hạng.

Ref: Tham chiếu tơi một danh sách các số.

Order: Tùy chọn sắp xếp (nếu là 0 (không) danh sách tham chiếu theo thứ tự giảm dần, nếu là 1 thì tác dụng ngược lại).
Ketoan.biz tổng hợp