Công thức đếm số lượng trong excel

Excel cho gianghoky.vn 365 Excel cho gianghoky.vn 365 dành cho máy Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2016 for Mac Excel 2013 Excel 2010 Excel 2007 Excel for Mac 2011 Xem thêm...Ít hơn

Việc đếm là một phần không thể thiếu trong việc phân tích dữ liệu, dù bạn đang kiểm lại số nhân viên của một phòng trong tổ chức của mình hay số đơn vị đã bán được theo quý. Excel cung cấp nhiều kỹ thuật mà bạn có thể sử dụng để đếm ô, hàng hoặc cột dữ liệu. Để giúp bạn đưa ra lựa chọn tốt nhất, bài viết này cung cấp một bản tóm tắt toàn diện về các phương pháp, một sổ làm việc có thể tải xuống với các ví dụ tương tác và nối kết đến các chủ đề liên quan để hiểu thêm.

Bạn đang xem: Công thức đếm số lượng trong excel


Lưu ý: Không nên nhầm lẫn giữa đếm và tính tổng. Để biết thêm thông tin về việc tính tổng các giá trị trong ô, cột hoặc hàng, hãy xem Tính tổng các cách cộng và đếm số Excel liệu.


Tải xuống ví dụ của chúng tôi

Bạn có thể tải xuống sổ làm việc ví dụ cung cấp các ví dụ để bổ sung thông tin trong bài viết này. Hầu hết các mục trong bài viết này sẽ tham chiếu đến trang tính thích hợp trong sổ làm việc ví dụ cung cấp các ví dụ và thông tin thêm.

Tải xuống ví dụ để đếm giá trị trong bảng tính

Trong bài viết này

Đếm đơn giản

Đếm dựa trên một hoặc nhiều điều kiện

Đếm khi dữ liệu của bạn chứa các giá trị trống

Đếm số lần xuất hiện duy nhất của giá trị

Các trường hợp đặc biệt (đếm tất cả các ô, đếm số từ)

Đếm đơn giản

Bạn có thể đếm số giá trị trong một phạm vi hoặc bảng bằng cách sử dụng công thức đơn giản, bấm vào một nút hoặc bằng cách sử dụng hàm trang tính.

Excel cũng có thể hiển thị số ô đã chọn trong Excel thanh trạng thái. Xem demo video sau đây để biết thông tin tổng quan về cách sử dụng thanh trạng thái. Đồng thời, hãy xem mục Hiển thị các phép tính và số lượng trên thanh trạng thái để biết thêm thông tin. Bạn có thể tham chiếu đến các giá trị hiển thị trên thanh trạng thái khi bạn muốn xem nhanh dữ liệu của mình và không có thời gian để nhập công thức.

Video: Đếm số ô bằng cách sử dụng thanh trạng thái của Excel

Xem video sau để tìm hiểu cách xem số lượng trên thanh trạng thái.



Sử dụng Tự Tính tổng

Dùng Tự động Tính tổng bằng cách chọn một phạm vi ô có chứa ít nhất một giá trị số. Sau đó trên tab Công thức, bấm Tự động Tính >Đếm Số.

*

Excel trả về số giá trị số trong phạm vi ở một ô liền kề với phạm vi mà bạn đã chọn. Thông thường, kết quả này được hiển thị trong một ô ở bên phải cho phạm vi ngang hoặc trong một ô bên dưới cho phạm vi dọc.

Đầu trang

Thêm hàng Tổng phụ

Bạn có thể thêm hàng tổng phụ vào dữ liệu Excel bạn. Bấm vào bất kỳ đâu bên trong dữ liệu của bạn, rồi bấm >Tổng phụ.


Lưu ý: Tùy chọn Tổng phụ sẽ chỉ hoạt động trên dữ liệu Excel bình thường, chứ Excel bảng, PivotTable hoặc PivotChart.


*

Ngoài ra, hãy tham khảo các bài viết sau đây:

Đầu Trang

Đếm số ô trong một danh sách hoặc cột bảng Excel bằng cách sử dụng hàm SUBTOTAL

Dùng hàm SUBTOTAL để đếm số lượng giá trị trong một Excel bảng hoặc phạm vi ô. Nếu bảng hoặc dải ô có chứa các ô ẩn, bạn có thể sử dụng SUBTOTAL để bao gồm hoặc loại trừ những ô ẩn đó và đây là sự khác biệt lớn nhất giữa hàm SUM và SUBTOTAL.

Cú pháp SUBTOTAL như sau:

SUBTOTAL(function_num,ref1,,...)

*

Để bao gồm các giá trị ẩn trong dải ô của mình, bạn nên đặt tham function_num thành 2.

Để loại trừ các giá trị ẩn trong phạm vi, hãy đặt tham function_num thành 102.

Đầu Trang

Đếm dựa trên một hoặc nhiều điều kiện

Bạn có thể đếm số ô trong phạm vi đáp ứng điều kiện (còn được gọi là tiêu chí) mà bạn chỉ định bằng cách sử dụng một số hàm trang tính.

Video: Sử dụng các hàm COUNT, COUNTIF và COUNTA

Xem video sau để xem cách sử dụng hàm COUNT và cách sử dụng các hàm COUNTIFCOUNTA để chỉ đếm các ô đáp ứng điều kiện mà bạn chỉ định.



Đầu Trang

Đếm số ô trong một phạm vi bằng cách sử dụng hàm COUNT

Sử dụng hàm COUNT trong công thức để đếm số giá trị số trong một phạm vi.

*

Trong ví dụ trên, A2, A3 và A6 là các ô duy nhất chứa giá trị số trong dải, vì vậy đầu ra là 3.


Lưu ý: A7 là một giá trị thời gian, nhưng nó chứa văn bản(còn gọi là ), do đó COUNT không xem nó là giá trị số. Nếu bạn muốn loại bỏ .m. từ ô, COUNT sẽ xem A7 là giá trị số và thay đổi đầu ra thành 4.


Đầu Trang

Đếm số ô trong một phạm vi dựa trên một điều kiện bằng cách sử dụng hàm COUNTIF

Sử dụng hàm COUNTIF để đếm một giá trị cụ thể xuất hiện bao nhiêu lần trong một phạm vi ô.

*

Đầu Trang

Đếm số ô trong một cột dựa trên một hoặc nhiều điều kiện bằng cách sử dụng hàm DCOUNT

Hàm DCOUNT đếm các ô chứa số trong một trường (cột) bản ghi trong danh sách hoặc cơ sở dữ liệu khớp với các điều kiện mà bạn xác định.

Trong ví dụ sau đây, bạn muốn tìm số tháng, bao gồm hoặc sau tháng 3 năm 2016 đã bán được nhiều hơn 400 đơn vị. Bảng đầu tiên trong trang tính, từ A1 đến B7, chứa dữ liệu bán hàng.

*

DCOUNT sử dụng các điều kiện để xác định nơi giá trị sẽ được trả về từ đó. Điều kiện thường được nhập vào các ô trong chính trang tính đó, sau đó bạn tham chiếu đến các ô này trong đối số criteria. Trong ví dụ này, các ô A10 và B10 chứa hai điều kiện—một điều kiện chỉ rõ rằng giá trị trả về phải lớn hơn 400 và ô còn lại chỉ định tháng kết thúc phải bằng hoặc lớn hơn hoặc bằng ngày 31 tháng 3 năm 2016.

Bạn nên sử dụng cú pháp sau đây:

=DCOUNT(A1:B7,"Tháng kết thúc",A9:B10)

DCOUNT kiểm tra dữ liệu trong phạm vi từ A1 đến B7, áp dụng các điều kiện xác định trong A10 và B10 và trả về 2, tổng số hàng thỏa mãn cả hai điều kiện (hàng 5 và 7).

Đầu trang

Đếm số ô trong một phạm vi dựa trên nhiều điều kiện bằng cách sử dụng hàm COUNTIFS

Hàm COUNTIFS tương tự với hàm COUNTIF với một ngoại lệ quan trọng: COUNTIFS cho phép bạn áp dụng tiêu chí vào các ô qua nhiều phạm vi và đếm số lần tất cả các tiêu chí này được đáp ứng. Bạn có thể dùng đến 127 cặp phạm vi/tiêu chí với COUNTIFS.

Cú pháp cho COUNTIFS là:

COUNTIFS(criteria_range1, criteria1, ,…)

Hãy xem ví dụ sau đây:

*

Đầu Trang

Đếm dựa vào tiêu chí bằng cách dùng kết hợp hàm COUNT và IF

Chẳng hạn bạn cần xác định có bao nhiêu người bán hàng đã bán một mặt hàng cụ thể trong một khu vực nhất định, hoặc bạn muốn biết doanh số bao nhiêu trên một giá trị nhất định được thực hiện bởi một người bán hàng cụ thể. Bạn có thể dùng kết hợp hàm IF và COUNT; bằng cách là trước tiên bạn dùng hàm IF để kiểm tra một điều kiện, rồi sau đó chỉ khi kết quả của hàm IF là True, thì bạn mới dùng hàm COUNT để đếm các ô.


Lưu ý: 

Hãy nhập công thức trong ví dụ này dưới dạng công thức mảng. Nếu bạn đã mở sổ làm việc này trong Excel for Windows hoặc Excel 2016 for Mac và muốn thay đổi công thức hoặc tạo một công thức tương tự, hãy nhấn F2, rồi nhấn Ctrl+Shift+Enter để công thức trả về kết quả bạn mong đợi. Trong các phiên bản cũ Excel for Mac, sử +Shift+Enter.

Để các công thức ví dụ hoạt động, đối số thứ hai cho hàm IF phải là một số.


*

Đầu Trang

Đếm tần suất xuất hiện của nhiều văn bản hay giá trị số bằng cách dùng kết hợp hàm SUM và IF

Trong ví dụ tiếp theo, chúng ta dùng kết hợp hàm IFSUM. Đầu tiên hàm IF kiểm tra giá trị trong một số ô và sau đó nếu kết quả kiểm tra là True thì SUM sẽ tính tổng các giá trị qua được phần kiểm tra đó.

Ví dụ 1

*

Hàm ở trên cho biết nếu C2:C7 chứa các giá trị Buchanan và Dodsworththì hàm SUM sẽ hiển thị tổng các bản ghi có điều kiện được đáp ứng. Công thức này tìm ba bản ghi cho Buchanan và một bản ghi cho Dodsworth trong dải ô đã cho và hiển thị 4.

Ví dụ 2

*

Hàm ở trên cho biết nếu D2:D7 chứa các giá trị nhỏ hơn $9000 hoặc lớn hơn $19.000 thì hàm SUM sẽ hiển thị tổng tất cả các bản ghi có điều kiện đáp ứng. Công thức này tìm thấy hai bản ghi D3 và D5 có giá trị nhỏ hơn $9000, sau đó D4 và D6 có giá trị lớn hơn $19.000 và hiển thị 4.

Ví dụ 3

*

Hàm ở trên cho biết nếu D2:D7 có hóa đơn cho Buchanan nhỏ hơn $9000 thì hàm SUM sẽ hiển thị tổng các bản ghi có điều kiện được đáp ứng. Công thức tìm thấy C6 đáp ứng điều kiện và hiển thị 1.


Quan trọng: Hãy nhập công thức trong ví dụ này dưới dạng công thức mảng. Điều đó có nghĩa là bạn nhấn F2 rồi nhấn Ctrl+Shift+Enter. Trong các phiên bản cũ hơn Excel for Mac, sử +Shift+Enter.


Hãy xem bài viết Cơ sở Kiến thức sau để biết các mẹo bổ sung:

Đầu trang

Đếm số ô trong một cột hoặc hàng trong PivotTable

PivotTable tóm tắt dữ liệu của bạn, đồng thời giúp bạn phân tích và truy sâu xuống dữ liệu của mình bằng cách cho phép bạn chọn thể loại mà bạn muốn xem dữ liệu của mình.

Bạn có thể nhanh chóng tạo PivotTable bằng cách chọn một ô trong phạm vi dữ liệu hoặc bảng Excel, rồi trên tab Chèn, trong nhóm Bảng, bấm PivotTable.

*

Hãy xem kịch bản mẫu của bảng tính Doanh số, trong đó bạn có thể đếm có bao nhiêu giá trị doanh số dành cho Gôn và Vô số cho các quý cụ thể.


Lưu ý: Để có trải nghiệm tương tác, bạn có thể chạy các bước này trên dữ liệu mẫu được cung cấp trong trang tính PivotTable trong sổ làm việc có thể tải xuống.


Nhập dữ liệu sau vào bảng Excel tính.

*

Chọn A2:C8

Bấm Chèn > PivotTable.

Xem thêm: Bản Mở Rộng The Sim 4

Trong hộp thoại Tạo PivotTable, bấm vào Chọn bảnghoặc dải ô , rồi bấm vào Trang tính mới, rồi bấm vào OK.

Một PivotTable trống sẽ được tạo trong một trang tính mới.

Trong ngăn Trường PivotTable, hãy làm như sau:

Kéo Thể thao vào khu vực Hàng.

Kéo Quývào vùng Cột.

Kéo Doanh số vào vùng Giá trị.

Lặp lại bước c.

Tên trường hiển thị là SumofSales2 trong cả PivotTable và vùng Giá trị.

Tại thời điểm này, ngăn Trường PivotTable sẽ trông như thế này:

*

Trong khu vực Giá trị, bấm vào danh sách thả xuống cạnh SumofSales2 và chọn Value Field Cài đặt.

Trong hộp thoại Giá trị Cài đặt, hãy làm như sau:

Trong trường Tóm tắt giá trị theo, chọn Đếm.

Trong trường Tên Tùy chỉnh, sửa đổi tên thành Đếm.

*

Bấm OK.

PivotTable hiển thị số lượng bản ghi cho Gôn và Sân gôn trong Quý 3 và Quý 4, cùng với số liệu bán hàng.

*

Đầu Trang

Đếm khi dữ liệu của bạn chứa các giá trị trống

Bạn có thể đếm các ô chứa dữ liệu hoặc là ô trống bằng cách sử dụng hàm trang tính.

Đếm số ô không trống trong một phạm vi bằng cách sử dụng hàm COUNTA

Sử dụng hàm COUNTA để chỉ đếm các ô trong phạm vi có chứa giá trị.

Khi bạn đếm các ô, đôi khi bạn muốn bỏ qua ô trống bất kỳ vì chỉ ô có giá trị mới có ý nghĩa với bạn. Ví dụ: bạn muốn đếm tổng số nhân viên bán hàng đã thực hiện bán hàng (cột D).

*

Hàm COUNTA bỏ qua các giá trị trống trong D3, D4, D8 và D11 và chỉ đếm các ô chứa giá trị trong cột D. Hàm tìm sáu ô trong cột D chứa giá trị và hiển thị 6 làm kết quả.

Đầu trang

Đếm số ô không trống trong danh sách với các điều kiện cụ thể bằng cách sử dụng hàm DCOUNTA

Sử dụng hàm DCOUNTA để đếm số ô không trống trong một cột bản ghi trong danh sách hoặc cơ sở dữ liệu khớp với các điều kiện mà bạn chỉ định.

Ví dụ sau sử dụng hàm DCOUNTA để đếm số lượng bản ghi trong cơ sở dữ liệu nằm trong phạm vi A1:B7 đáp ứng các điều kiện được chỉ định trong phạm vi tiêu chí A9:B10. Những điều kiện đó là giá trị ID Sản phẩm phải lớn hơn hoặc bằng 2000 và giá trị Xếp loại phải lớn hơn hoặc bằng 50.

*

DCOUNTA tìm thấy hai hàng đáp ứng điều kiện- hàng 2 và 4 và hiển thị giá trị 2 làm đầu ra.

Đầu Trang

Đếm số ô trống trong phạm vi liền kề bằng cách sử dụng hàm COUNTBLANK

Sử dụng hàm COUNTBLANK để trả về số lượng ô trống trong phạm vi liền kề (ô liền kề nếu chúng được liên kết theo trình tự không gián đoạn). Nếu một ô chứa công thức trả về văn bản trống ("") thì ô đó cũng được đếm.

Khi bạn đếm số ô, có thể sẽ có lúc bạn muốn bao gồm các ô trống, vì chúng có ý nghĩa với bạn. Trong ví dụ sau đây về bảng tính bán hàng tạp hóa. giả sử bạn muốn tìm xem có bao nhiêu ô không có số liệu bán hàng được đề cập.

*

Lưu ý: Hàm trang tính COUNTBLANK cung cấp phương pháp thuận tiện nhất để xác định số ô trống trong một phạm vi, nhưng nó không hoạt động tốt khi các ô bạn quan tâm nằm trong một sổ làm việc đóng hoặc khi chúng không tạo thành phạm vi kề nhau. Bài viết Cơ sở Tri thức XL: Khi bạn sử dụng SUM(IF()) thay cho CountBlank() sẽ chỉ cho bạn cách sử dụng công thức mảng SUM(IF()) trong các trường hợp đó.


Đầu Trang

Đếm số ô trống trong một phạm vi không liền kề bằng cách sử dụng kết hợp hàm SUM và hàm IF

Sử dụng kết hợp hàm SUM và hàm IF. Thông thường, bạn thực hiện điều này bằng cách dùng hàm IF trong công thức mảng để xác định xem mỗi ô được tham chiếu có chứa giá trị không, rồi tính tổng số các giá trị FALSE mà công thức trả về.

Xem một vài ví dụ về tổ hợp hàm SUM và IF trong mục trước đây Đếm tần suất xuất hiện của nhiều giá trị văn bản hoặc số bằng cách sử dụng kết hợp hàm SUM và IF trong chủ đề này.

Đầu Trang

Đếm số lần xuất hiện giá trị duy nhất

Bạn có thể đếm các giá trị duy nhất trong một phạm vi bằng cách sử dụng các hàm PivotTable,COUNTIF,SUM và IFcùng nhau hoặc hộp thoại Bộ lọc Nâng cao.

Đếm số lượng giá trị duy nhất trong một cột danh sách bằng cách sử dụng Bộ lọc Nâng cao

Sử dụng hộp thoại Bộ lọc Nâng cao để tìm các giá trị duy nhất trong một cột dữ liệu. Bạn có thể lọc các giá trị tại chỗ hoặc bạn có thể xuất và dán chúng vào một vị trí mới. Sau đó, bạn có thể sử dụng hàm ROWS để đếm số lượng các mục trong phạm vi mới.

Để sử dụng Bộ lọc Nâng cao, bấm tab Dữ liệu và trong nhóm Sắp xếp & Lọc, bấm Nâng cao.

Hình dưới đây cho thấy cách bạn sử dụng Bộ lọc Nâng cao để sao chép chỉ những bản ghi duy nhất tới vị trí mới trên trang tính.

*

Trong hình dưới đây, cột E chứa các giá trị được sao chép từ phạm vi trong cột D.

*

Lưu ý: 

Nếu bạn lọc dữ liệu tại chỗ, các giá trị sẽ không bị xóa khỏi trang tính của bạn — một hoặc nhiều hàng có thể bị ẩn. Bấm Xóa trong nhóm Sắp xếp & Lọc trên tab Dữ liệu để hiển thị lại các giá trị đó.

Nếu bạn chỉ muốn xem qua số lượng giá trị duy nhất, hãy chọn dữ liệu sau khi bạn đã sử dụng Bộ lọc Nâng cao (hoặc dữ liệu được lọc hoặc dữ liệu được sao chép) rồi xem thanh trạng thái. Giá trị Số lượng trên thanh trạng thái phải bằng số lượng giá trị duy nhất.


Để biết thêm thông tin, hãy xem mục Lọc bằng cách sử dụng tiêu chí nâng cao

Đầu trang

Đếm số lượng giá trị duy nhất trong một phạm vi đáp ứng một hoặc nhiều điều kiện bằng cách sử dụng các hàm IF, SUM, FREQUENCY, MATCH và LEN

Sử dụng các tổ hợp khác nhau của các hàm IF, SUM, FREQUENCY, MATCHLEN.

Để biết thêm thông tin và ví dụ, hãy xem mục "Đếm số lượng giá trị duy nhất bằng cách sử dụng hàm" trong bài viết Đếm các giá trị duy nhất trong số các giá trị trùng lặp.

Đầu Trang

Các trường hợp đặc biệt (đếm tất cả các ô, đếm số từ)

Bạn có thể đếm số ô hoặc số từ trong một phạm vi bằng cách sử dụng các kết hợp khác nhau của các hàm trang tính.

Đếm tổng số ô trong một phạm vi bằng cách sử dụng các hàm ROWS và COLUMNS

Giả sử bạn muốn xác định kích cỡ trang tính lớn để quyết định nên sử dụng tính toán thủ công hay tự động trong sổ làm việc của bạn. Để đếm tất cả các ô trong một phạm vi, hãy dùng công thức nhân các giá trị trả về bằng cách dùng hàm ROWSvà COLUMNS. Xem hình ảnh sau đây để biết ví dụ:

*

Đầu trang

Đếm số từ trong một phạm vi bằng cách sử dụng kết hợp các hàm SUM, IF, LEN, TRIM và SUBSTITUTE

Bạn có thể sử dụng kết hợp các hàm SUM, IF, LEN, TRIMSUBSTITUTE trong công thức mảng. Ví dụ sau cho thấy kết quả của việc sử dụng một công thức lồng để tìm số từ trong một phạm vi 7 ô (trong đó có 3 ô trống). Một số ô chứa khoảng trắng ở đầu hoặc cuối — các hàm TRIMSUBSTITUTE loại bỏ những khoảng trắng thừa này trước khi thực hiện bất kỳ hoạt động đếm nào. Hãy xem ví dụ sau đây:

*

Bây giờ, để công thức ở trên hoạt động đúng cách, bạn phải biến công thức này thành công thức mảng, nếu không công thức sẽ trả về giá #VALUE! lỗi. Để thực hiện điều đó, hãy bấm vào ô có công thức, rồi trong thanh Công thức, nhấn Ctrl + Shift + Enter. Excel thêm một dấu ngoặc nhọn ở đầu và cuối công thức, do đó nó trở thành công thức mảng.

Để biết thêm thông tin về công thức mảng, hãy xem mục Tổng quan về các công Excel trong công thức và Tạo công thức mảng.

Đầu Trang

Hiển thị các phép tính và số lượng trên thanh trạng thái

Khi đã chọn một hoặc nhiều ô, thông tin về dữ liệu trong các ô đó sẽ được hiển thị trên thanh trạng thái của Excel. Ví dụ: nếu bốn ô trên trang tính của bạn được chọn và chứa các giá trị 2, 3, một chuỗi văn bản (chẳng hạn như "đám mây") và 4 thì tất cả các giá trị sau có thể được hiển thị đồng thời trên thanh trạng thái: Trung bình, Số lượng, Số Đếm, Nhỏ nhất, Lớn nhất và Tổng. Bấm chuột phải vào thanh trạng thái để hiển thị hoặc ẩn bất kỳ hay tất cả các giá trị này. Các giá trị này được hiển thị trong hình minh họa sau.

*

Đầu Trang

Bạn cần thêm trợ giúp?

Bạn luôn có thể hỏi một chuyên gia trong Cộng đồng Kỹ thuật Excel hoặc nhận sự hỗ trợ trongCộng đồng trả lời.