Gần đây, tôi đến thăm một người bạn đang làm việc trên một bản in rõ ràng là do một ứng dụng bảng tính tạo ra. Đó là danh sách tên và địa chỉ của khách hàng được sắp xếp theo Mã ZIP, và bạn tôi đang đếm thủ công xem có bao nhiêu khách hàng trong mỗi vùng Mã ZIP. Tôi ghét thấy người dùng lãng phí thời gian để làm một việc gì đó thủ công mà phần mềm có thể làm được.
XEM: Google Workspace so với Microsoft 365: Phân tích song song với danh sách kiểm tra (TechRepublic Premium)
Đương nhiên, tôi phải chúi mũi vào quy trình và chỉ ra rằng có một cách tốt hơn nhiều để có được những con số đó. Trong hướng dẫn này, tôi sẽ chỉ cho bạn những gì tôi đã chỉ cho họ: Cách sử dụng hàm COUNTIF() của Excel để trả về số lần một giá trị cụ thể — trong trường hợp này là Mã ZIP — xuất hiện trong một danh sách. Đồng thời, bạn cũng sẽ tìm hiểu kiến thức cơ bản về COUNTIF() để có thể sử dụng hàm linh hoạt này cho công việc của riêng mình. Sau đó, chúng tôi sẽ sử dụng hàm SUBTOTAL() để đếm khi lọc.
Đối với phần minh họa này, tôi đang sử dụng Microsoft 365 trên hệ thống Windows 10 64 bit, nhưng bạn cũng có thể sử dụng chức năng này với các phiên bản Excel cũ hơn. Microsoft Excel dành cho web hỗ trợ cả hai hàm mà chúng ta sẽ làm việc ở đây.
Chuyển đến:
COUNTIF đối số
Trước khi chúng ta sử dụng một trong hai hàm, hãy xem các đối số COUNTIF(). COUNTIF() trả về số ô đáp ứng một điều kiện cụ thể mà bạn chỉ định. Trong trường hợp của chúng tôi, chúng tôi đang đếm số lần một mã ZIP cụ thể xuất hiện trong một phạm vi cụ thể.
COUNTIF() sử dụng cú pháp sau:
COUNTIF(range,criteria)
trong đó “phạm vi” xác định danh sách các giá trị bạn đang đếm và “tiêu chí” thể hiện điều kiện để đếm.
Trước khi chúng ta tiếp tục, điều quan trọng cần biết là hàm COUNTIF() có một hạn chế. Đối số tiêu chí được giới hạn ở 255 ký tự khi sử dụng giá trị chuỗi ký tự. Chắc chắn bạn sẽ gặp phải giới hạn này, nhưng nếu gặp phải, bạn có thể nối các chuỗi bằng toán tử nối & để xây dựng một chuỗi dài hơn.
Khắc phục sự cố COUNTIF
Nếu hàm COUNTIF() không trả về giá trị nào và bạn biết giá trị tồn tại, hãy xem xét các hành động và mẹo sau:
- Đảm bảo phân định các giá trị: Ví dụ: “apples” sẽ đếm số lần từ apple xuất hiện trong phạm vi được tham chiếu; nếu bạn bỏ qua dấu ngoặc kép, nó sẽ không hoạt động. Các giá trị số không yêu cầu dấu phân cách, ngoại trừ ngày tháng sử dụng dấu phân cách
#dấu phân cách. - Kiểm tra các giá trị: Phạm vi được tham chiếu của bạn có thể có một ký tự khoảng trắng không cần thiết trước hoặc sau các ký tự khác. Sử dụng TRIM() để chỉ trả lại các giá trị bạn muốn.
- Kiểm tra xem tệp của bạn có đang mở không: Nếu COUNTIF() đề cập đến một sổ làm việc khác, thì tệp đó phải được mở. Nếu không, hàm trả về lỗi #VALUE! lỗi.
- Hãy xem kỹ văn bản tiêu chí của bạn: Giá trị tiêu chí COUNTIF() không phân biệt chữ hoa chữ thường. Tuy nhiên, dấu ngoặc nhọn trong tiêu chí sẽ trả về lỗi, vì vậy nếu bạn đang dán một giá trị, hãy cẩn thận. Nói chung, đây không phải là một vấn đề.
- Đừng dựa vào định dạng ô: COUNTIF() không thể đếm ô dựa trên giá trị màu nền hoặc màu phông chữ.
Bây giờ bạn đã quen thuộc với chức năng này, hãy sử dụng nó với một ví dụ đơn giản.
Cách sử dụng hàm COUNTIF trong Excel
Hãy bắt đầu với cách sử dụng đơn giản COUNTIF(). Như bạn có thể thấy trong Hình Achức năng
=COUNTIF(Table1[ZIPS],10123)
trả về giá trị của 2.
Hình A
Đó là vì giá trị Mã ZIP, 10123, xuất hiện hai lần trong Bảng có tên là Bảng1. Nếu bạn không sử dụng đối tượng Bảng, hãy sử dụng tham chiếu phạm vi như sau:
=COUNTIF(A2:A21,10123)
Nếu bạn không quen thuộc với tham chiếu có cấu trúc, Table1[ZIPS] có thể làm bạn bối rối. Dữ liệu ví dụ được định dạng dưới dạng đối tượng Bảng Excel. Table1 là tên của đối tượng Table và [ZIPS] là tên cột.
Việc chỉ định một Mã ZIP duy nhất rất dễ dàng, nhưng bạn có thể muốn mở rộng số lượng này bằng cách bao gồm tất cả chúng.
Làm cách nào để đếm nhiều mục trong Excel?
Bạn có thể chỉ định một giá trị bằng chữ khi sử dụng COUNTIF(), nhưng đối số tiêu chí hỗ trợ tham chiếu ô hoặc phạm vi.
Để chứng minh tính linh hoạt của chức năng này, chúng tôi sẽ đếm số lần xuất hiện của mỗi Mã ZIP trong dữ liệu mẫu. Thông thường, Mã ZIP sẽ đi kèm với các giá trị địa chỉ khác như tên, địa chỉ, thành phố và tiểu bang. Mục đích của chúng tôi là giữ cho ví dụ của mình đơn giản vì những giá trị đó không liên quan khi bạn chỉ tính các giá trị Mã ZIP.
XEM: Chính sách sử dụng dịch vụ Microsoft 365 (TechRepublic Premium)
Nếu bạn đang sử dụng Microsoft 365, hãy sử dụng biểu thức sau để tạo một danh sách duy nhất gồm các giá trị Mã ZIP được sắp xếp (Hình B):
=SORT(UNIQUE(Table1[ZIPS]))
Hình B
SORT() và UNIQUE() đều là các hàm mảng động, chỉ có sẵn trong Excel 365. Trong ví dụ của chúng ta, chỉ có một biểu thức nằm trong D2. Tuy nhiên, biểu thức tràn vào các ô bên dưới để đáp ứng các giá trị được trả về dưới dạng một mảng. Nếu bạn gặp lỗi tràn, thì có điều gì đó đang chặn mảng trong các ô bên dưới biểu thức.
Khi bạn có một danh sách Mã ZIP duy nhất, bạn có thể sử dụng hàm COUNTIF() để trả về số lượng của từng giá trị Mã ZIP, như minh họa trong Hình Csử dụng
=COUNTIF(Table1[ZIPS],D2)
và sao chép nó vào các ô còn lại.
Hình C
Để tìm hiểu thêm về mảng động, bạn có thể đọc Cách tạo danh sách duy nhất được sắp xếp trong bảng tính Excel.
Làm cách nào để đếm nhiều mục trong Excel trước 365?
Đối với những người dùng đang sử dụng phiên bản Excel cũ hơn Excel 365, bạn sẽ phải làm việc chăm chỉ hơn một chút để có cùng kết quả. Nếu điều quan trọng đối với bạn là danh sách Mã ZIP duy nhất được sắp xếp, hãy sắp xếp dữ liệu nguồn trước khi tiếp tục.
Để làm như vậy, bạn chỉ cần nhấp vào bất kỳ ô nào trong cột A và nhấp vào nút Sắp xếp tăng dần trong nhóm Sắp xếp & Lọc trên tab Dữ liệu. Ngoài ra, bạn có thể nhấp vào Sắp xếp & Lọc trong nhóm Chỉnh sửa trên tab Trang chủ.
Để tạo một danh sách Mã ZIP duy nhất từ các giá trị trong cột A, hãy làm như sau:
- Nhấp vào bất kỳ nhóm ô nào trong tập dữ liệu — trong ví dụ này, chúng tôi đã chọn A1:A21.
- Nhấp vào tab Dữ liệu rồi nhấp vào Nâng cao trong nhóm Sắp xếp & Lọc.
- Nhấp vào tùy chọn Sao chép sang Vị trí khác.
- Excel sẽ hiển thị $A$1:$A$21 dưới dạng Phạm vi Danh sách. Nếu nó không làm điều này, bạn có thể sửa nó bằng tay.
- Xóa Phạm vi tiêu chí nếu có.
- Bấm vào Sao chép để kiểm soát, rồi bấm vào một ô chưa được chọn, chẳng hạn như G1.
- Kiểm tra tùy chọn Chỉ bản ghi duy nhất (Hình dung).
Hình dung
- Nhấp vào OK.
Tính năng này cũng sao chép văn bản tiêu đề từ A1 và định dạng. Không có cách nào xung quanh một trong hai bản sao này, nhưng điều đó không sao, bởi vì cả hai đều không can thiệp vào nhiệm vụ của chúng tôi.
Tại thời điểm này, tất cả những gì còn lại là chức năng đếm các mục duy nhất trong cột A dựa trên các mục trong danh sách duy nhất trong cột G. Bây giờ là lúc nhập hàm sau vào ô H2:
=COUNTIF(Table1[ZIPS],G2)
Sau đó, bạn sẽ sao chép nó vào các ô còn lại. Như bạn có thể thấy trong Hình Ehàm này trả về số đếm giống như hàm đầu tiên.
Hình E
Bạn có để ý số 20 in đậm trong ô H9 không? Đó là hàm SUM(), đảm bảo số lượng mục nhập được đếm bằng với số lượng mục nhập ban đầu. Vì chúng tôi có 20 mục nhập trong dữ liệu nguồn của mình ở cột A, nên chúng tôi mong muốn tổng số mục nhập duy nhất được tính là giống nhau.
COUNTIF() là một cách hữu ích để đếm các giá trị cụ thể trong danh sách, nhưng bạn cũng có thể gặp phải tình huống muốn đếm các mục trong danh sách đã lọc. Hãy đề cập đến cách thực hiện điều đó tiếp theo.
Làm cách nào để đếm danh sách đã lọc trong Excel?
Việc sử dụng hàm COUNTIF() rất hiệu quả trong nhiều trường hợp, nhưng nếu bạn muốn đếm dựa trên kết quả của danh sách đã lọc thì sao? Trong trường hợp này, hàm COUNTIF() sẽ không hoạt động với bạn. Hàm sẽ tiếp tục trả về kết quả chính xác nhưng sẽ không trả về số đếm chính xác cho tập hợp đã lọc. Thay vào đó, bạn sẽ muốn sử dụng hàm SUBTOTAL() để đếm danh sách đã lọc.
Hàm SUBTOTAL() của Excel khá đặc biệt vì nó hỗ trợ lọc. Cụ thể, bất kể phép tính toán học là gì, hàm này chỉ đánh giá các giá trị nằm trong danh sách được lọc. Hàm này sử dụng cú pháp sau:
SUBTOTAL(number,reference)
“Số” xác định phép tính toán học và “tham chiếu” chỉ định các giá trị. Theo mặc định, số là 109, là SUM(). tham khảo Bảng A để biết danh sách đầy đủ các giá trị số:
Bảng A
| Bao gồm các hàng ẩn | Loại trừ hàng ẩn | Chức năng |
|---|---|---|
| 1 | 101 | TRUNG BÌNH |
| 2 | 102 | ĐẾM |
| 3 | 103 | ĐẾM |
| 4 | 104 | TỐI ĐA |
| 5 | 105 | TỐI THIỂU |
| 6 | 106 | SẢN PHẨM |
| 7 | 107 | STDEV |
| số 8 | 108 | STDEVP |
| 9 | 109 | TỔNG |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
Trong phần trước, hàm COUNTIF() không quan tâm dữ liệu nguồn là phạm vi dữ liệu thông thường hay đối tượng Bảng. Để giải pháp này hoạt động, bạn phải làm việc với đối tượng Bảng. Để chuyển đổi phạm vi dữ liệu thành đối tượng Bảng, nhấp vào bất kỳ đâu bên trong phạm vi dữ liệu và nhấn Ctrl + T rồi nhấp vào OK để xác nhận chuyển đổi. Làm như vậy sẽ tự động hiển thị danh sách thả xuống bộ lọc trong ô tiêu đề.
Trước khi bắt đầu lọc chúng ta phải thêm một hàng đặc biệt vào Table như sau:
- Nhấp vào bất kỳ đâu bên trong Bảng.
- Nhấp vào tab Thiết kế bảng theo ngữ cảnh.
- Trong nhóm Tùy chọn Kiểu Bảng, bấm vào mục Tổng số Hàng (Hình F).
Hình F
Như bạn có thể thấy trong Hình F, hàng này mặc định là hàm SUBTOTAL() tính tổng các giá trị theo mặc định. Trong trường hợp này, chúng tôi không muốn tổng số mà là số lượng. Để thay đổi đối số của hàm SUBTOTAL(), bấm vào A22 và chọn Đếm từ danh sách thả xuống hiển thị trong Hình G.
Hình G
Như bạn có thể thấy, có nhiều chức năng khác nhau mà bạn có thể chọn. Hình H hiển thị số đếm, là 20.
Hình H
Đối số đầu tiên của hàm SUBTOTAL() ban đầu là 109, đại diện cho SUM(). Khi bạn thay đổi hàm tổng thành Đếm, SUBTOTAL() cập nhật đối số đó thành 103, đại diện cho COUNT().
Bắt đầu quá trình lọc
Sau khi tổng số hàng được đặt đúng chỗ và hiển thị số lượng, bạn đã sẵn sàng để bắt đầu lọc. Để bắt đầu, hãy thử nhấp vào menu thả xuống lọc trong A1 và thực hiện như sau:
- Bỏ chọn (Chọn tất cả).
- Kiểm tra tùy chọn 10125 (hình tôi).
hình tôi
- Nhấp vào OK.
Như bạn có thể thấy trong Hình Jtập hợp đã lọc bao gồm hai mục và hàm SUBTOTAL() hiện trả về hai mục thay vì 20. Hàm này đặc biệt vì, không giống như các hàm khác, hàm SUBTOTAL() cập nhật khi bạn áp dụng bộ lọc.
Hình J
Hãy thử lại, chỉ lần này, hãy kiểm tra hai Mã ZIP (Hình K).
Hình K
Như bạn có thể thấy trong Hình LSUBTOTAL() trả về số lượng của cả hai giá trị Mã ZIP là 7. SUBTOTAL() đủ linh hoạt để xử lý bất kỳ bộ lọc nào bạn áp dụng bằng tính năng bộ lọc nâng cao.
Hình L
tài nguyên bổ sung
Cho dù bạn sử dụng COUNTIF() hay SUBTOTAL() thông qua tổng số hàng của đối tượng Bảng, việc đếm các giá trị là công việc dễ dàng. Để tìm hiểu thêm về cách đếm, hướng dẫn khác này của TechRepublic có thể giúp: Cách sử dụng hàm UNIQUE() để trả về số lượng các giá trị duy nhất trong Excel.
Đọc tiếp theo: 8 lựa chọn thay thế tốt nhất cho Microsoft Project (Miễn phí & Trả phí) (TechRepublic)
