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.
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ề cách tính tổng các giá trị trong ô, cột hoặc hàng, hãy xem mục Tính tổng các cách cộng và đếm dữ liệu Excel.
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.
Trong bài viết này
-
Đếm dựa trên một hoặc nhiều điều kiện
-
Đếm số ô trong một phạm vi dựa trên một điều kiện duy nhất bằng cách sử dụng hàm COUNTIF
-
Đế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
-
Đế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
-
Đế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
-
Đếm khi dữ liệu của bạn chứa các giá trị trống
-
Đếm số ô không trống trong một phạm vi bằng cách sử dụng hàm COUNTA
-
Đế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
-
Đếm số ô trống trong một phạm vi liền kề bằng cách sử dụng hàm COUNTBLANK
-
Đế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
-
Đế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 tổng >đế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.
Thêm hàng Tổng phụ
Bạn có thể thêm hàng tổng phụ vào dữ liệu Excel của mình. Bấm vào vị trí bất kỳ 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ứ không hoạt động trên bảng Excel, PivotTable hoặc PivotChart.
Ngoài ra, hãy tham khảo các bài viết sau:
Đế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 bảng Excel hoặc phạm vi ô. Nếu bảng hoặc dải ô chứa các ô ẩn, bạn có thể sử dụng SUBTOTAL để bao gồm hoặc loại trừ các ô ẩn đó và đây là sự khác biệt lớn nhất giữa các hàm SUM và SUBTOTAL.
Cú pháp SUBTOTAL giống như sau:
SUBTOTAL(function_num,ref1,[ref2],...)
Để bao gồm các giá trị ẩn trong dải ô của mình, bạn function_num đối số thành 2.
Để loại trừ các giá trị ẩn trong phạm vi của bạn, hãy function_num đối số thành 102.
Đế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 COUNTIF và COUNTA để chỉ đếm các ô đáp ứng điều kiện mà bạn chỉ định.
Đế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 có chứa giá trị số trong dải ô, do đó đầu ra là 3.
Lưu ý: A7 là một giá trị thời gian, nhưng nó chứa văn bản (a.m.), do đó COUNT không coi nó là giá trị số. Nếu bạn muốn xóa a.m. từ ô, COUNT sẽ xem A7 là giá trị số và thay đổi đầu ra thành 4.
Đế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
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 ô.
Đế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 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.
Hàm DCOUNT dùng các điều kiện để xác định nơi trả về giá trị. Điều kiện thường được nhập vào các ô trong trang tính và 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 xác định rằng giá trị trả về phải lớn hơn 400 và ô còn lại chỉ rõ rằng tháng kết thúc phải bằng hoặc lớn hơn 31/03/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).
Đế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ự như 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ể sử dụng tối đa 127 cặp phạm vi/tiêu chí với COUNTIFS.
Cú pháp cho COUNTIFS là:
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)
Hãy xem ví dụ sau:
Đế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 ý:
-
Phải 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 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ả như mong đợi. Trong các phiên bản Excel for Mac cũ hơn, sử
-
Để các công thức ví dụ hoạt động, đối số thứ hai cho hàm IF phải là số.
Đế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 IF và SUM. Đầ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à Dodsworth thì hàm SUM sẽ hiển thị tổng các bản ghi đáp ứng điều kiện. Công thức này tìm thấy 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 đáp ứng điều kiện. Công thức này tìm thấy hai bản ghi D3 và D5 với các giá trị nhỏ hơn $9000, rồi D4 và D6 với cá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 dưới $9000 thì hàm SUM sẽ hiển thị tổng các bản ghi đáp ứng điều kiện. Công thức tìm thấy rằng C6 đáp ứng điều kiện và hiển thị 1.
Quan trọng: Phải 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 Excel for Mac cũ hơn, sử
Hãy xem bài viết Cơ sở Kiến thức sau để biết các mẹo bổ sung:
Đế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.
Chúng ta hãy xem một kịch bản mẫu của bảng tính Doanh số, nơi bạn có thể đếm có bao nhiêu giá trị doanh số cho Golf và Tennis 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 đây vào bảng tính Excel.
-
Chọn A2:C8
-
Bấm Chèn > PivotTable.
-
Trong hộp thoại Tạo PivotTable, hãy bấm Chọn một bảng hoặc phạm vi, sau đó bấm Trang tính Mới, rồi bấm 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 Quartervà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 trông như thế này:
-
Trong khu vực Giá trị, bấm vào menu thả xuống bên cạnh SumofSales2, rồi chọn Thiết đặt Trường Giá trị.
-
Trong hộp thoại Thiết đặt Trường Giá trị, hãy làm như sau:
-
Trong mục 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 vào OK.
-
PivotTable hiển thị số lượng bản ghi cho Gôn và Tennis trong Quý 3 và Quý 4, cùng với số liệu bán hàng.
-
Đế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 đã 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 ô có chứa giá trị trong cột D. Hàm tìm sáu ô trong cột D có chứa các giá trị và hiển thị 6 dưới dạng đầu ra.
Đế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 đây 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 các điều kiện- hàng 2 và 4 và hiển thị giá trị 2 làm đầu ra.
Đếm số ô trống trong phạm vi liền kề bằng cách sử dụng hàm COUNTBLANK
Dùng hàm COUNTBLANK để trả về số ô trống trong phạm vi liền kề (các ô liền kề nếu chúng được kết nối 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 hiểu 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 dải ô nhưng hàm này không hoạt động tốt lắm khi các ô bạn quan tâm nằm trong sổ làm việc đóng hoặc khi chúng không tạo thành phạm vi liền kề. Bài viết Trong Cơ sở Kiến thức XL: Khi nào nên Sử dụng SUM(IF()) thay vì CountBlank() cho bạn biết cách sử dụng công thức mảng SUM(IF()) trong những trường hợp đó.
Đế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. Nói chung, 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ị hay không, rồi tính tổng số các giá trị FALSE mà công thức trả về.
Xem một số ví dụ về kết hợp hàm SUM và IF trong mục trước Đó Đế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.
Đế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 kết hợp các hàm PivotTable, COUNTIF, SUM và IF 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.
Để dùng Bộ lọc Nâng cao, hãy bấm tab Dữ liệu và trong nhóm Sắp xếp & Lọc , hãy 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
Đế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, MATCH và LEN.
Để 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 mục trùng lặp.
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 . Hãy xem hình ảnh sau đây để biết ví dụ:
Đế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, TRIM và SUBSTITUTE trong công thức mảng. Ví dụ sau đây cho thấy kết quả của việc sử dụng công thức lồng nhau để tìm số từ trong 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 TRIM và SUBSTITUTE 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:
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 đó biến nó 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 Tổng quan về các công thức trong Excelvà Tạo công thức mảng.
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.
Bạn cần thêm trợ giúp?
Bạn luôn có thể hỏi chuyên gia trong Cộng đồng kỹ thuật Excel hoặc nhận hỗ trợ trong Cộng đồng.
