Công thức mảng là một trong những tính năng khó hiểu nhất trong Excel, nhưng dường như lại là một trong những điều thú vị và hấp dẫn nhất.
3. Công thức mảng – đếm tất cả các ký tự trong một dải nhất định
Ví dụ công thức mảng này sẽ chỉ cho bạn cách đếm số ký tự, bao gồm khoảng trắng, trong một dải ô. Công thức rất đơn giản, như sau:
= SUM (LEN (range))
Bạn sử dụng hàm LEN để trả lại chiều dài của chuỗi văn bản trong mỗi ô riêng lẻ, và sau đó sử dụng hàm SUM để thêm các con số đó vào để tính tổng.
Ví dụ, công thức mảng = SUM (LEN (A1: A10)) tính tổng số tất cả các ký tự (có tính kí tự khoảng cách) trong dải A1: A10.
4. Công thức mảng để tính bất kỳ (các) ký tự nào trong dải
Nếu bạn muốn biết có bao nhiêu lần một nhân vật nhất định hoặc một nhóm nhân vật xuất hiện trong một dải ô được chỉ định, một công thức mảng với hàm LEN có thể giúp một lần nữa. Trong trường hợp này, công thức phức tạp hơn một chút:
= SUM ((LEN (range) – LEN (SUBSTITUTE (range,character, “”))) / LEN (character))
Và đây là một ví dụ thực tế. Giả sử bạn có một danh sách các đơn đặt hàng mà một ô có thể chứa một số thứ tự được phân cách bởi dấu phẩy hoặc bất kỳ dấu phân cách nào khác. Có một số loại lệnh và mỗi loại có một số nhận dạng duy nhất của riêng nó – ký tự đầu tiên trong một số đơn đặt hàng.
Giả sử các đơn đặt hàng nằm trong các ô B2: B5 và mã nhận biết đơn hàng đặt trong ô E1, công thức như sau:
= SUM ((LEN (B2: B5) – LEN (SUBSTITUTE (B2: B5, E1, “”))) / LEN (E1))
Công thức mảng để tính bất kỳ (các) ký tự nào trong dải
5. Các giá trị tổng hợp trong mỗi hàng hoặc cho hàng thứ N
Nếu bạn muốn tổng hợp mỗi hàng khác hoặc hàng thứ N trong một bảng, bạn sẽ cần kết hợp các hàm SUM và MOD trong một công thức mảng:
= SUM ((– (MOD (ROW (range), Nth row) = 0)) * (range))
Hàm MOD trả về số dư sau khi làm tròn số kia thành số nguyên gần nhất và chia cho số chia. Chúng tôi nhúng hàm ROW để lấy ra số thứ tự của hàng, và sau đó chia nó cho hàng thứ N (ví dụ bằng 2 để tổng hợp mỗi ô thứ hai) và kiểm tra nếu phần còn lại là số không. Nếu có, thì ô được tính tổng.
Toán tử đơn vị (–) được sử dụng để chuyển thành các giá trị Boolean không-phải-giá-trị-số là TRUE và FALSE, và sau khi sử dụng hàm MOD thì trở thành 1 và 0, để cuối cùng thì hàm SUM để có thể thêm các số đó.
Ví dụ, để đếm mỗi ô khác trong khoảng B2: B10, bạn sử dụng một trong các công thức sau:
Đếm cả hàng (hàng thứ 2, thứ 4, v.v.):
= SUM ((– (MOD (ROW ($ B2: B10), 2) = 0)) * (B2: B10))
Đếm các hàng lẻ (hàng thứ nhất, thứ ba, etc.):
= SUM ((– (MOD (ROW ($ B2: B10), 2) = 1)) * (B2: B10))
Để có được một công thức phổ quát có thể tổng hợp các giá trị trong bất kỳ dòng thứ N nào mà bạn chỉ định và hoạt động chính xác với bất kỳ dải nào bất kể vị trí của chúng trong một bảng tính, thì công thức trên vẫn cần phải được cải thiện thêm một chút nữa, như sau:
= SUM ((– (MOD ((ROW ($ B $ 2: $ B $ 7) -ROW ($ B $ 2)), E1) = E1-1)) * ($ B $ 2: $ B $ 7))
Với ô E1 lần lượt là hàng N mà bạn muốn tính tổng.
Chúc các bạn thành công nhé.
Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ
Bài viết liên quan
