Bạn đang xem bài viết Hướng Dẫn Cách Dùng Hàm Countif Trong Công Việc Kế Toán Trên Excel được cập nhật mới nhất trên website Hoisinhvienqnam.edu.vn. Hy vọng những thông tin mà chúng tôi đã chia sẻ là hữu ích với bạn. Nếu nội dung hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất.
Tìm hiểu cấu trúc hàm COUNTIF trong ExcelHàm COUNTIF thuộc nhóm hàm thống kê, trong đó ý nghĩa của tên hàm là:
Cú pháp của hàm như sau:
=COUNTIF(range, criteria)
range: là vùng chứa nội dung cần đếm (thống kê ở đâu)
criteria: điều kiện đếm (thống kê theo điều kiện gì)
Chỉ áp dụng với 1 điều kiện
Cách viết các điều kiện với từng kiểu dữ liệu: Text (văn bản), Number (Số), Date (ngày)
Tính kết quả bảng chấm công bằng hàm COUNTIFBài tập tính kết quả bảng chấm công bằng hàm COUNTIF
Khi gặp các yêu cầu như đếm, thống kê, tính số lần… đều có thể sử dụng hàm COUNTIF. Khi sử dụng hàm này cần lưu ý:
Tổng số công X (cột AG): đếm tổng số ký tự X trong vùng từ cột B tới cột AF
Tổng số công N (cột AH): đếm tổng số ký tự N trong vùng từ cột B tới cột AF
Ứng dụng hàm COUNTIF trong trường hợp này như sau:
Tổng cộng số công = Số công X + (Số công N / 2)
Ví dụ chúng ta có 1 bảng chấm công như sau:
Vùng cần thống kê là B9:AF9 (từ cột B tới cột AF là các ngày công, dòng 9 là tại vị trí mã nhân viên NV01, đây chính là dòng đang áp dụng công thức)
Điều kiện cần thống kê là chữ X, do đó khi viết chữ X trong công thức phải đặt ký tự X bên trong cặp dấu nháy kép
Trong bảng chấm công này đã có sẵn thông tin về các nhân viên và ngày công của từng người trong tháng. Yêu cầu là bạn cần thống kê được tổng số công của từng người biết:
Tại ô AG9 bạn nhập công thức sau:
=COUNTIF(B9:AF9,”X”)
Trong đó:
Ta có kết quả như sau:
Bạn có thể kéo công thức tại ô AG9 xuống tới ô AG15 để có được kết quả cho toàn bộ các nhân viên khác.
Áp dụng tương tự cho ô AH9 để đếm số ký hiệu N như sau:
Vị trí đặt công thức: ô F2 cho khách hàng công ty A, tương tự sẽ áp dụng công thức cho các vùng F3, F4, F5
Vùng cần thống kê: là cột Tên khách hàng (vùng A3:A20)
Điều kiện thống kê: là giá trị tại ô E2 (là chữ Công ty A, có thể viết trực tiếp chữ này hoặc tham chiếu tới ô chứa tên đó)
Sau đó copy công thức từ ô AH9 tới ô AH15, chúng ta có kết quả như sau:
Với cột AI (tổng cộng số công làm việc), bạn chỉ cần dùng phép tính:
AI9=AG9+AH9/2
Trong đó cột AH là nửa ngày công, do đó số lần đếm được giá trị N sẽ chỉ 1 nửa, tức là chia 2.
Thống kê số lần mua hàng của khách hàng bằng hàm COUNTIFVí dụ cho tình huống này như sau:
Trong bảng dữ liệu bán hàng, chúng ta thấy mỗi khách hàng xuất hiện nhiều lần. Do đó bạn được yêu cầu thống kê số lần mua hàng của từng công ty.
Trong trường hợp này bạn có thể dùng hàm COUNTIF để thống kê như sau:
Vùng cần đếm thường được cố định
Hàm COUNITF chỉ thống kê được theo 1 điều kiện. Muốn thống kê theo nhiều điều kiện, bạn cần sử dụng hàm COUNTIFS.
Khi đó ta viết công thức tại ô F2 như sau:
Kết quả là:
Chú ý khi dùng hàm COUNTIFKhi sao chép công thức từ ô F2 xuống các ô F3, F4, F5 thì chúng ta chú ý:
Nếu không cố định tọa độ vùng A3:A20 trong công thức thì vùng này sẽ bị thay đổi khi bạn sao chép xuống phía dưới, dẫn tới kết quả của hàm COUNTIF không còn chính xác nữa.
Để tránh lỗi này, bạn cần cố định vùng A3:A20 (hay còn gọi là cố định vùng cần đếm). Cách cố định là đặt dấu $ vào trước tọa độ dòng, cột của vùng này (hoặc bạn chọn tọa độ vùng này trong công thức rồi nhấn phím F4)
50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: Vietinbank, Vietcombank, BIDV, VP Bank, TH True Milk, VNPT, FPT Software, Samsung SDIV, Ajinomoto Việt Nam, Messer,…
Như vậy công thức tại ô F2 chính xác hơn sẽ là:
=COUNTIF($A$3:$A$20,E2)
Khi đó kéo công thức từ F2 xuống F5 sẽ không bị sai nữa.
Kết luậnKhi sử dụng hàm COUNTIF, bạn cần ghi nhớ:
Hy vọng những chia sẻ trong bài viết này sẽ hữu ích trong công việc của bạn. Nếu có bất kỳ thắc mắc gì bạn có thể góp ý, phàn hồi ngay dưới bài viết này để chúng tôi có thể giải quyết mọi thắc mắc của bạn một cách sớm nhất.
Những kiến thức bạn đang xem thuộc khóa học Excel từ cơ bản tới nâng cao của chúng tôi Khóa học này cung cấp cho bạn kiến thức một cách đầy đủ và có hệ thống về các hàm, các công cụ trong excel, ứng dụng excel trong công việc… Hiện nay hệ thống đang có nhiều ưu đãi khi bạn đăng ký tham gia khóa học này.
Tải về file bài tập thực hànhBạn có thể tải về file bài tập thực hành trong bài viết tại đường link bên dưới:
Tài liệu kèm theo bài viết
Với sứ mệnh: ” Mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người “, đội ngũ phát triển đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: Tin học văn phòng, Phân tích dữ liệu, Thiết kế, Công nghệ thông tin, Kinh doanh, Marketing, Quản lý dự án…
Gitiho tự hào khi được đồng hành cùng:
Hướng Dẫn Cách Dùng Hàm Sumif Trong Kế Toán Excel
Hàm SUMIF dùng để tính tổng của một vùng dữ liệu nào đó theo điều kiện cho trước.
Ví dụ: Bạn muốn tính Tổng doanh số theo mã khách hàng hoặc tính tổng thưởng doanh số theo nhân viên…
SUMIF (range, criteria, [sum_range])
Trong đó:
Range: vùng chứa điều kiện tính tổng
Criteria: điều kiện tính tổng
Sum_range: vùng cần tính tổng
Bằng
=
=SUMIF(B1:B15, 20000000,D1:D15)
Tổng các giá trị trong dãy ô D1:D15 với các giá trị tương ứng trong dãy ô B1:B15 = 20000000
Lớn hơn
Nhỏ hơn
<
=SUMIF(B1:B15, “<20000000”,D1:D15)
Tổng các giá trị trong dãy ô D1:D15 với các giá trị tương ứng trong dãy ô B1:B15 <20000000
3. Sử dụng hàm SUMIF trong kế toán Excela. Dùng hàm SUMIF với điều kiện so sánh bằng
Giả sử bạn có một Bảng cân đối tài khoản có sẵn các số liệu số dư, số phát sinh của các tài khoản cấp 1, 2. Yêu cầu đặt ra là bạn cần tính tổng số số dư, số phát sinh của các tài khoản cấp 1 như hình sau:
Bạn trỏ chuột vào Ô C16 và gõ công thức =SUMIF($I$4:$I$14,1,C4:C15)
Khi đó:
Sau khi nhập xong công thức các bạn ấn phím Enter, kết quả như sau:
Tổng số dư nợ đầu năm các tài khoại cấp 1 là 941.462.500. Ta có thể kiểm tra lại kết quả như sau:
Số dư Nợ đầu năm của các tài khoản cấp 1 bao gồm:
– Tiền mặt = 700.650.400
– Tiền gửi ngân hàng = 35.160.800
– Phải thu khách hàng = 170.900.700
– Thuế GTGT được khấu trừ = 34.750.600
– Công cụ dụng cụ = 0
Sau đó bạn sao chép công thức của ô C17 cho các ô cần tính tổng khác. Như vậy bạn đã tính được tổng dùng hàm SUMIF với điều kiện so sánh bằng.
b. Dùng hàm SUMIF với điều kiện so sánh lớn hơn
Minh hoạ bằng ví dụ sau:
Trong đó:
E3:E8 là dải ô (cột) doanh số cần tính tổng
Sau khi nhập xong công thức trên bạn bấm Enter, màn hình hiển thị kết quả sau:
c. Dùng hàm SUMIF với điều kiện so sánh nhỏ hơn
Ví dụ: Cho danh sách các mặt hàng sau, tính tổng doanh thu các mặt hàng có số lượng <50
Trỏ chuột tại ô E9, bạn nhập công thức sau: =SUMIF(D3:D8,”<50″,E3:E8)
Trong đó:
Nhập xong công thức trên bạn ấn phím Enter và kết quả hiển thị trên màn hình như sau:
Với các điều kiện tính tổng khác các bạn làm tương tự
“<= 20” : điều kiện nhỏ hơn hoặc bằng 20
Khi bạn đã hiểu và biết cách dùng hàm SUMIF một cách thành thạo bạn có thể dùng hàm SUMIF kết hợp với rất nhiều các hàm khác để có thể tính được các số liệu theo yêu cầu.
Ứng Dụng Excel Trong Công Việc Kế Toán Lương
Bài 1: Lập bảng chấm công – cho bộ phận hành chính 3. Yêu cầu 2: Chấm công theo giờ bắt đầu và giờ kết thúc
Thông thường việc chấm công theo giờ bắt đầu và giờ kết thúc được thực hiện thông qua máy chấm công.
Tôi sẽ ví dụ với các bạn kết quả thu được từ một máy chấm công như sau:
Đây là bước rất quan trọng, Việc phân tích bảng dữ liệu nguồn sẽ giúp chúng ta có phương án xử lý phù hợp và chính xác.
Để thực hiện công việc này, chúng ta cần nắm được các nguyên tắc sau:
Mục đích công việc: Tính công làm việc theo thời gian
Mã Nhân viên: Liên tục theo dòng, không được để dòng trống
Ngày trong tháng: Bao gồm xác định ngày trong tháng, thứ trong tuần, phân biệt ngày nghỉ lễ, nghỉ bù
Giờ vào, giờ ra: Các ngày làm việc đều phải có giờ vào, giờ ra. Khi tính công sẽ dựa trên việc đánh giá giờ vào, giờ ra để tính ra các loại công.
Ta có thể thấy, để tính toán được, cần phải điều chỉnh lại cấu trúc bảng dữ liệu sao cho đúng các tiêu chí.
Việc tính toán trên nhiều điều kiện, nên hàm sẽ sử dụng để tính toán là SUMIFS, COUNTIFS và hàm IF
Bước 2: Cấu trúc lại bảng dữ liệu
Để rút gọn bài viết, mình sẽ không nêu từng thao tác, mà sẽ đưa hình mẫu và gợi ý các cách làm để các bạn có thể tự làm được:
Phần Tháng và Năm (dòng 2, 3): Đặt bên ngoài để tiện việc thay đổi
Ngày trong tháng (dòng 4): Dùng hàm Date, bắt đầu với ngày 26 tháng trước đó (vì bảng nguyên liệu kết xuất tính từ ngày 26 nên ta sẽ giữ nguyên yêu cầu này. Tính công từ ngày 26 tháng trước đến ngày 25 tháng sau). Riêng 2 ngày cuối có thể sang tháng tiếp theo (trường hợp tháng có 28-29 ngày) nên có thể xử lý để bỏ trống nếu sang tháng sau, tức là ngày 27 tháng này trở đi là đã bước sang tháng sau của bảng tính công.
Phần Mã NV (cột A, B, C): Mỗi mã 1 dòng, để tập hợp theo mã NV chính xác hơn.
Thứ trong tuần (dòng 5): Dùng hàm Choose kết hợp với hàm Weekday để xác định. Kết hợp tính năng Conditional Formatting để tô màu phân biệt ngày CN với ngày thường.
Giờ vào, giờ ra (dòng 6): có thể quy ước số 1 là giờ vào, số 2 là giờ ra.
Lưu ý: 1 ngày có 2 giờ, nên 2 cột sẽ tính cho 1 ngày. Vì vậy khi xử lý ngày ở dòng 4 cần chú ý nội dung này.
Bước 3: Lọc và lấy dữ liệu
Tất cả những nội dung trên, mình đã hướng dẫn chi tiết trong bài: Tạo bảng chấm công trên excel. Vui lòng xem lại bài này để được hướng dẫn chi tiết.
Tạo thêm 1 vùng, trong đó mỗi ngày chỉ tương ứng 1 cột. Cách xử lý ngày, thứ giống với phần trước.
Giờ công làm việc trong ngày = Giờ ra – Giờ vào
Giờ ra = (Tổng số phút quy ước tại thời điểm chấm công ra = Số giờ * 60 + Số phút)/60
Giờ vào = (Tổng số phút quy ước tại thời điểm chấm công vào = Số giờ * 60 + Số phút)/60
BN7 =ROUND(((HOUR(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2))*60+MINUTE(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,2)))-(HOUR(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1))*60+MINUTE(SUMIFS($D7:$BM7,$D$4:$BM$4,BN$4,$D$6:$BM$6,1))))/60,2)
Bạn có thể filldown, fillright công thức từ BN7 cho các ô khác.
* Lưu ý:
Hướng Dẫn Kế Toán Sử Dụng Hàm Subtotal Trong Kế Toán Excel
Hướng dẫn kế toán sử dụng hàm Subtotal trong kế toán Excel
Hướng dẫn kế toán sử dụng Hàm SUBTOTAL trong quá trình hoạch toán kế toán trên EXCELHàm SUBTOTAL là một hàm rất linh hoạt nhưng cũng là một trong các hàm hơi khó sử dụng của Excel. Điều khó hiểu thứ nhất chính là cái tên của nó vì nó thực sự làm được nhiề thứ hơn ý nghĩa của tên hàm. Đối số thứ nhất của hàm bắt buộc bạn phải nhớ con số đại diện cho phép tính cần thực hiện trên tập số liệu. (Trong Excel 2007 có tính năng AutoComplete giúp chúng ta khỏi nhớ các con số này). Hàm SUBTOTAL được Microsoft nâng cấp kể từ phiên bản Excel 2003 với sự gia tăng các tuỳ chọn cho đối số thứ nhất của hàm, tuy nhiên điều này dẫn đến sự không tương thích với các phiên bản cũ nếu chúng ta sử dụng các tính năng mới bổ sung này.
Đối số đầu tiên của của hàm SUBTOTAL xác định hàm thực sự nào sẽ được sử dụng khi tính toán trong danh sách bên dưới. Ví dụ nếu đối số là 1 thì hàm SUBTOTAL hoạt động giống nhưng hàm AVERAGE, nếu đối số thứ nhất là 9 thì hàm hàm SUBTOTAL hoạt động giống nhưng hàm SUM.
Subtotal là hàm tính toán cho một nhóm con trong một danh sách hoặc bảng dữ liệu tuỳ theo phép tính mà bạn chọn lựa trong đối số thứ nhất.
SUBTOTAL(function_num,ref1,ref2,…)
· Function_num là các con số từ 1 đến 11 (hay có thêm 101 đến 111 trong phiên bản Excel 2003, 2007) qui định hàm nào sẽ được dùng để tính toán trong subtotal
· Ref1, ref2,… là các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trê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ị ẩn 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ị ẩ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) 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 (Ví dụ về tham chiếu 3-D: =SUM(Sheet2:Sheet13!B5) thì hàm SUBTOTAL báo lỗi #VALUE!. (Các loại tham chiếu xem bài sẽ đăng tiếp sau)
CÔNG TY KẾ TOÁN THIÊN ƯNG
Chuyên dạy thực hành kế toán tổng hợp, dạy kế toán Excel
Cập nhật thông tin chi tiết về Hướng Dẫn Cách Dùng Hàm Countif Trong Công Việc Kế Toán Trên Excel trên website Hoisinhvienqnam.edu.vn. Hy vọng nội dung bài viết sẽ đáp ứng được nhu cầu của bạn, chúng tôi sẽ thường xuyên cập nhật mới nội dung để bạn nhận được thông tin nhanh chóng và chính xác nhất. Chúc bạn một ngày tốt lành!