Ronald nhập một số phép đo mức tín hiệu dưới dạng một chuỗi giá trị vào Excel. Anh ta cần đếm có bao nhiêu nhóm giá trị liên tiếp tồn tại trong chuỗi giá trị này nằm dưới một ngưỡng nhất định. Ví dụ, anh ta có thể có các số đo sau:

27, 22, 22, 30, 32, 18, 22, 23, 28, 39, 24, 27, 35, 25, 21

Nếu anh ta muốn biết số lượng các nhóm trong đó các thành viên của các nhóm đó dưới 26 tuổi, câu trả lời sẽ là 4. Lưu ý rằng đây là nhóm các giá trị liên tiếp dưới 26, không phải số các giá trị riêng lẻ dưới 26. Vì vậy, trong này trường hợp, bốn nhóm sẽ được hiển thị bằng dấu ngoặc trong sau:

27, [22, 22], 30, 32, [18, 22, 23], 28, 39, [24], 27, 35, [25, 21]

Ronald đang tự hỏi loại công thức nào mà anh ấy có thể sử dụng để tìm ra số lượng các nhóm nằm dưới ngưỡng tùy ý mà anh ấy có thể chỉ định.

Thực tế có một số cách khác nhau để bạn có thể tiếp cận vấn đề này. Đầu tiên là sử dụng “cột kết quả” về cơ bản ghi chú những thay đổi trong nhóm ngưỡng và trình tự. Ví dụ: nếu bạn có các giá trị trên trong cột A của một trang tính (bắt đầu từ ô A2) và giá trị ngưỡng trong ô E1, thì bạn có thể sử dụng công thức sau trong mỗi ô ở bên phải của một giá trị trong cột A:

=IF(A2>=$E$1,B1,IF(A1<$E$1,B1,B1+1))

Công thức giữ tổng số các nhóm đang hoạt động dưới ngưỡng. Giá trị tối đa (hoặc giá trị cuối cùng) của cột B cung cấp tổng số nhóm dưới ngưỡng. Công thức kiểm tra xem liệu giá trị ngay bên trái, trong cột A, là trên hay dưới ngưỡng.

Nếu nó ở trên hoặc nếu không và giá trị trước đó trong cột A cũng ở dưới, thì nó không tăng tổng đang chạy. Nếu không, nó sẽ tăng lên bởi vì một nhóm mới đang bắt đầu.

Một cách liên quan để thực hiện việc đếm là sử dụng công thức này trong cột B, thay vào đó:

=IF(A2>=$E$1,0,IF(A1<$E$1,0,1))

Điều này dẫn đến cột B chứa một loạt các giá trị 0 hoặc 1. Thời gian duy nhất giá trị 1 xảy ra là khi bắt đầu một chuỗi dưới ngưỡng. Điều này giúp dễ dàng tính tổng tất cả các giá trị trong cột B, cung cấp số lượng các nhóm.

Nếu không muốn sử dụng cột kết quả, bạn có thể sử dụng công thức mảng để tính số lượng. Công thức sau đây giả định một lần nữa rằng các giá trị được phân tích nằm trong cột A, bắt đầu từ A2 và giá trị ngưỡng nằm trong ô E1. Ngoài ra, hãy nhớ rằng công thức mảng được nhập bằng cách nhấn Ctrl + Shift + Enter.

=SUM(IF((A2:A16<$E$1)((A2:A16((A1:A15<$E$1)ISNUMBER(A1:A15))),1))

Về cơ bản, công thức thực hiện những gì mà công thức cột kết quả trước đó đã làm (xác định 0 hoặc 1 dựa trên việc nhóm dưới ngưỡng có đang bắt đầu hay không) và sau đó tính tổng các giá trị đó.

Tất nhiên, nếu bạn thực hiện nhiều kiểu so sánh này, bạn có thể muốn phát triển hàm do người dùng xác định của riêng mình (một macro) để tìm số lượng các nhóm cho bạn. Sau đây là một ví dụ về một chức năng như vậy.

Function CountGroups(ByVal MyRange As Range, Threshold As Single)

Dim Cell As Range     Dim bInGroup As Boolean     Dim iCount As Integer

Application.Volatile     iCount = 0     bInGroup = False     For Each Cell In MyRange         If Application.IsNumber(Cell) Then             If Cell < Threshold Then 'Less than the threshold?

If Not bInGroup Then  'Only count if starting new group                     iCount = iCount + 1                     bInGroup = True     'Mark as being in group                 End If             Else                 bInGroup = False        'No longer in a group             End If         End If     Next     CountGroups = iCount End Function

Hàm xem xét từng ô trong một phạm vi và tính toán xem đó có phải là phần bắt đầu của một nhóm mới dưới ngưỡng hay không. Bạn sử dụng hàm bằng cách sử dụng công thức như sau trong trang tính của bạn:

=CountGroups(A2:A16,E1)

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (3171) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: