Công thức mảng Excel 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.

Ví dụ 1. Đếm các ô thỏa mãn các điều kiện nhất định

Việc giới thiệu các hàm SUMIF và COUNTIF cũng giống như đa phần các đối tượng khác trong Excel 2007, đều không thể luôn phù hợp với mọi trường hợp. Tuy nhiên, vẫn có những nhiệm vụ mà công thức mảng là giải pháp có hiệu quả nhất  vẫn có các lựa chọn thay thế khác.

Giả sử bạn có 2 cột số, cột A (đã lên kế hoạch – planned) và cột B (thực tế). Và bạn muốn biết cột B lớn hơn gấp bao nhiêu lần so với cột A, khi một giá trị trong cột B lớn hơn 0.

Nhiệm vụ này đòi hỏi phải so sánh hai dãy và rất cần công thức mảng Excel ở đây.

Vì vậy, bạn thể hiện các điều kiện như (B2: B10> = A2: A10) và (B2: B10> 0), nối chúng bằng dấu hoa thị (*) hoạt động như hàm AND trong các công thức mảng, và đưa chúng vào trong đối số của hàm SUM:

= SUM ((B2: B10> = A2: A10) * (B2: B10> 0))

Và hãy nhớ nhấn Ctrl + Shift + Enter để nhập chính xác mảng trong Excel!

Hai đoạn tiếp theo dành cho những ai tìm hiểu sâu hơn về công nghệ. Nếu bạn không quan tâm, thì bạn có thể bỏ qua và đi tới ví dụ công thức tiếp theo.

Để hiểu rõ hơn về công thức này nói riêng và các công thức mảng Excel nói chung, hãy chọn hai biểu thức bên trong ngoặc đơn của hàm SUM trong thanh công thức, rồi nhấn F9 để xem các mảng trong thành phần của công thức.

Vì vậy, những gì chúng ta có ở đây là hai mảng các giá trị Boolean, trong đó TRUE tương đương với 1 và FALSE tương đương với 0. Do chúng ta đang sử dụng toán tử AND () trong công thức, nên hàm SUM sẽ chỉ thêm các hàng có giá trị TRUE (1) trong cả hai mảng, như trong ảnh chụp màn hình dưới đây:

Chú ý: Không phải tất cả các hàm Excel hỗ trợ các mảng có thể chuyển đổi TRUE và FALSE thành 1 và 0. Trong các công thức mảng phức tạp hơn, thì bạn có thể cần phải sử dụng dấu gạch đôi (–), được gọi là toán tử đôi, để chuyển đổi các số không phải là giá trị Boolean với số.

Bao gồm 2 dấu gạch ngang trong công thức trên sẽ không làm ảnh hưởng gì cả, mà nó sẽ chỉ giữ công thức bạn an toàn hơn: = SUM (- (B2: B10> = A2: A10) * (B2: B10> 0))

Và đây là một ví dụ công thức mảng Excel phức tạp hơn mà hoàn toàn yêu cầu sử dụng toán tử đơn vị.

Đừng vội bỏ qua: các hàm thường dùng trong excel

Ví dụ 2. Sử dụng một số hàm trong công thức mảng Excel

Các công thức mảng có thể làm việc với một số hàm Excel tại một thời điểm và thực hiện nhiều phép tính trong một công thức.

Ví dụ: nếu bạn có một bảng liệt kê nhiều sản phẩm bán hàng của một số nhân viên bán hàng và bạn muốn biết doanh số bán hàng tối đa của một người cụ thể cho một sản phẩm nhất định, bạn có thể viết một công thức mảng dựa trên mẫu sau:

= MAX (IF ((salesmen_range = “name”) * (product_range = “name”), sales_range, “”))

Giả sử rằng tên của nhân viên bán hàng nằm trong cột A, tên sản phẩm nằm trong cột B và doanh số bán hàng nằm trong cột C, thì để tính doanh số bán táo (apples) nhiều nhất mà Mike có thể bán, sử dụng công thức:

= MAX (IF (($ A $ 2: $ A $ 9 = “mike”) * ($ B $ 2: $ B $ 9 = “apples”), $ C $ 2: $ C $ 9, “”))

Đương nhiên, bạn có thể thay thế các tên trong công thức bằng các tham chiếu ô để người dùng của bạn chỉ cần gõ tên vào các ô nhất định mà không sửa đổi công thức mảng của bạn:

Trong hình ở trên có sử dụng các công thức mảng trong Excel (và đừng quên nhấn Ctrl + Shift + Enter để nhập chính xác nhé):

Giá trị lớn nhất: =MAX(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,””))

Giá trị nhỏ nhất: =MIN(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,””))

Giá trị trung bình: =AVERAGE(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,””))

Tổng cộng: =SUM(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,””))

Ví dụ 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.

Ví dụ 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))

Ví dụ 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

Khóa học excel kế toán khuyến mãi cực lớn

Ví dụ 6. Thực hiện tính toán khác nhau trên các con số trong dải khác nhau

Dưới đây là một tình huống điển hình cho nhiều nhà cung cấp – chính là đơn giá thay đổi tùy thuộc vào số lượng mua, và vấn đề đặt ra cho bạn là viết một công thức tính tổng số tiền cho bất kỳ giá đầu vào nào ở một 1 ô cụ thể.

Vấn đề này có thể dễ dàng giải quyết bằng cách sử dụng các công thức IF lồng nhau như sau:

= B8 * IF (B8> = 101, B6, IF (B8> = 50, B5, IF (B8> = 20, B4, IF (B8> = 11, B3, IF (B8> = 1, B2, )))))

Tuy nhiên, cách tiếp cận này có một hạn chế đáng kể. Bởi vì công thức tham chiếu đến mỗi giá trong các ô B2:B6 là độc lập, nên bạn sẽ phải cập nhật công thức ngay khi người dùng thay đổi bất kỳ dải ô nào hoặc thêm vào một dải số mới.

Để làm cho công thức linh hoạt hơn, nên để thao tác trên mảng hơn là trên các ô riêng lẻ. Trong trường hợp này, bất kể có bao nhiêu giá trị được thay đổi, thêm hoặc xóa, bạn sẽ chỉ phải cập nhật một tham chiếu dải ô trong công thức.

= SUM (B8 * (B2: B6) * (– (B8> = VALUE (LEFT (A2: A6, FIND (“”, A2: A6))))) * (– (B8 <= VALUE (RIGHT (A2: A6, LEN (A2: A6) – FIND (“to”, A2: A6) -LEN (“to”))))))

Việc chia nhỏ công thức nàyđể phân tích chi tiết có thể sẽ yêu cầu một bài báo riêng, do đó tôi sẽ chỉ đưa ra một cái nhìn khái quát về mặt logic của công thức. Nếu bạn chọn từng phần của công thức trong thanh công thức và nhấn F9, bạn sẽ thấy nó đánh giá 3 mảng sau đây (với số lượng là 100 (ô B8) như trong ảnh chụp màn hình ở trên):

= SUM (B8 * {20; 18; 16; 13; 12} * {1; 1; 1; 1; 0} * {0; 0; 0; 1; 1}

Năm phần tử đầu tiên của mảng chính là giá cả trong các ô B2: B6. Và 2 mảng cuối cùng của 0 và 1 xác định chính xác giá nào sẽ được sử dụng để tính toán. Vì vậy, câu hỏi chính là – lấy được 2 mảng này như thế nào và ý nghĩa của chúng?

Công thức bao gồm 2 hàm VALUE, như sau: (B8> = VALUE ()) * (B8 <= VALUE ())

Hàm thứ nhất kiểm tra xem giá trị của B8 có lớn hơn hay bằng giới hạn dưới của mỗi dải “số lượng đơn vị” và hàm thứ 2 để kiểm tra nếu B8 nhỏ hơn hoặc bằng với giới hạn trên của mỗi dãy (kết hợp các hàm LEFT , RIGHT, FIND và LEN để lấy ra các giá trị giới hạn trên và dưới). Thì sau đó, bạn sẽ nhận được 0 nếu điều kiện không được thỏa, và 1 nếu thỏa điều kiện.

Cuối cùng, hàm SUM sẽ nhân số lượng trong B8 bởi với giá tương ứng trong mảng đơn giá (B2: B6) và mỗi phần tử của mảng 0 và 1. Vì nhân với 0 luôn cho 0, nên luôn chỉ có một giá được sử dụng cho mỗi sản phâm.

Trong ví dụ này, số lượng được nhân với 13 đô la nằm trong khoảng giá trị “50 đến 100”. Đây là mục thứ 4 của mảng giá (ô B5 trong phạm vi B2: B6), và nó là thành phần duy nhất có mặt trong hai mảng cuối cùng.

Để công thức làm việc chính xác, hãy chắc chắn kiểm tra lại hai điều sau:

• Số lượng trong các ô A2: A6 nên tạo thành một khoảng giá trị liên tiếp để không có giá trị nào bị bỏ lại.
• Tất cả các khối lượng trong các ô A2: A6 phải được nhập vào trong mẫu “X đến Y” này vì nó được mã hoá trong công thức. Nếu số lượng của bạn được nhập bằng một cách khác, hãy nói “1 – 10”, sau đó thay “đến” bằng “-” trong công thức.

Nếu bạn muốn hiển thị một thông báo “Nằm ngoài dải” khi số lượng đầu vào trong B8 nằm ngoài dải số tiền, thì hãy tham khảo câu lệnh IF sau đây:

= IF (AND (B8> = VALUE (LEFT (A2, FIND (“”, A2))), B8 <= VALUE (RIGHT (A6, LEN (A6) – FIND(“to”, A6) -LEN ( “to”)))), SUM (…))

Công thức hàm If này dù nhìn có vẻ phức tạp nhưng thực sự lại rất đơn giản – nó kiểm tra rằng nếu giá trị trong B8 lớn hơn hoặc bằng với ràng buộc dưới trong A2 và ít hơn hoặc bằng với các ràng buộc trên trong ô A6. Nói cách khác, nó kiểm tra điều kiện này: AND (B8> = 1, B8 <= 200).

Công thức đầy đủ mà bạn cần như sau:

= IF (AND (B8> = VALUE (LEFT (A2, FIND (“”, A2))), B8 <= VALUE (RIGHT (A6, LEN (A6) -FIND (“to”, A6) -LEN (” “), SUM (B8 * (B2: B6) * (- (B8> = VALUEỊ (LEFT (A2: A6, FIND (” “, A2: A6))))) * (- ( B8 <= VALUE (RIGHT (A2: A6, LEN (A2: A6) -FIND (“to”, A2: A6) -LEN (“to”)))))), “Out of range”)

Ví dụ 7. Các hàm do người dùng định nghĩa trong công thức mảng Excel

Ví dụ này nhằm dành cho những người biết và hiểu về các macro VBA trong Excel và các hàm do người dùng tự định nghĩa.

Bạn có thể sử dụng hàm đã được người dùng (bạn) định nghĩa từ trước trong các công thức mảng trong Excel, cho phép một công thức xác định hỗ trợ các phép tính trong các mảng.

Ví dụ, một trong những chuyên gia Excel của chúng tôi đã viết một hàm gọi là GetCellColor có thể nhận biết được một màu của tất cả các ô trong một dải, y như tên của nó. Bạn có thể lấy mã của hàm công thức trên từ bài viết này – Làm thế nào để đếm và tính tổng các ô dựa trên màu sắc trong Excel.

Và bây giờ, chúng ta hãy xem làm thế nào mà bạn có thể sử dụng hàm GetCellColor trong một công thức mảng. Giả sử bạn có một bảng với một cột mã màu và bạn muốn tổng hợp các giá trị đáp ứng một số điều kiện, bao gồm màu của ô. Ví dụ: hãy tính tổng doanh số các cột màu “xanh” và “vàng” của Neal:

Như đã trình bày trong hình trên, chúng ta sử dụng công thức mảng Excel như sau:

=SUM(–($A$2:$A$10=$F$1) * ($C$2:$C$10) * (–(GetCellColor($C$2:$C$10)=GetCellColor($E$2))))

Trong đó, ô F1 là tên người bán hàng và E2 là mẫu màu.

Công thức tính các ô trong khoảng C2: C10 nếu thỏa mãn hai điều kiện sau:

• $A$2:$A$10 = $F$1 – kiểm tra nếu một ô trong cột A khớp với tên người bán trong F1, tức là Neal như trong ví dụ này.
• GetCellColor ($C$2:$C$10) = GetCellColor ($ E $ 2) – sử dụng hàm được người dùng định nghĩa tùy chỉnh để lấy màu của các ô C2 đến C10 và kiểm tra xem nó có phù hợp với mẫu màu trong E2 hay không, trong trường hợp này.

Hãy chú ý rằng chúng ta sử dụng toán tử đơn vị với cả hai biểu thức trên để chuyển đổi các giá trị Boolean TRUE và FALSE trở về 1 và 0 để hàm SUM có thể hoạt động trên dữ liệu này. Nếu cả hai điều kiện được đáp ứng, nghĩa là hai giá trị được trả lại, SUM sẽ cộng thêm số tiền bán hàng từ một ô tương ứng trong cột C.

Địa chỉ học Excel tại Hà Nội

Công thức mảng Excel – những hạn chế và các phương án thay thế

Các công thức mảng là một trong những tính năng mạnh mẽ nhất của Excel, nhưng không phải là ở mọi phương diện. Đây là những hạn chế quan trọng nhất của mảng trong Excel.

1. Những mảng lớn có thể làm chậm Excel:

Mặc dù Microsoft Excel không áp đặt bất kỳ giới hạn nào về kích thước của mảng bạn sử dụng trong các bảng tính, nhưng bạn sẽ bị giới hạn bởi bộ nhớ sẵn có trên máy tính của bạn, vì việc tính toán lại các công thức với các mảng lớn tốn kha khá thời gian. Mặc dù trên lý thuyết, bạn có thể tạo các mảng khổng lồ bao gồm hàng trăm hoặc hàng ngàn thành phần, trong thực tế điều này không được khuyến cáo bởi vì chúng có thể làm chậm đáng kể bảng tính của bạn.

2. Không được phép chọn các mảng hoàn toàn chỉ có cột.

Bạn không được phép tạo một mảng bao gồm toàn bộ cột hoặc vài cột vì lý do rõ ràng được giải thích ở trên. Các công thức mảng trong Excel rất thiếu tài nguyên và Microsoft đang áp dụng các biện pháp ngăn chặn việc đóng băng của Excel.

3. Giới hạn của các công thức mảng khi tham chiếu đến 1 trang tính khác:

Trong Excel 2003 và các phiên bản trước, một bảng tính nhất định có thể chứa tối đa 65.472 công thức mảng đề cập đến một trang tính khác. Trong các phiên bản hiện tại của Excel 2013, 2010 và 2007, công thức mảng các bảng tính chéo được giới hạn bởi bộ nhớ sẵn có.

4. Gỡ lỗi cho các công thức mảng

Nếu công thức mảng trả về kết quả không chính xác, hãy chắc chắn là bạn đã nhấn Ctrl + Shift + Enter khi nhập nó. Nếu bạn đã làm, hãy chọn các phần của công thức và nhấn F9 để đánh giá và gỡ lỗi chúng.

5. Các lựa chọn thay thế cho các công thức mảng.

Nếu bạn thấy công thức mảng quá phức tạp và khỏ hiểu, thì bạn vẫn có thể sử dụng một trong những hàm công thức Excel khác để xử lý các dữ liệu mảng (mà không cần đến tổ hợp phím Ctrl + Shift + Enter). Ví dụ như hàm SUMPRODUCT – nhân các giá trị của các mảng nhất định với nhau và cho ra kết quả là tổng tất cả sản phẩm. Một ví  dụ khác là hàm INDEX trong Excel khi có các giá trị trống hoặc bằng 0 trong đối số row_num hay col_num thì sẽ trả về giá trị mảng của toàn bộ các cột và hàng tương ứng.

Nếu bạn muốn tải xuống các ví dụ về hàm công thức mảng (sử dụng phương pháp lật ngược vấn đề hiểu sâu hơn), thì bạn sẽ dễ dàng tải xuống các ví dụ về công thức. Đây là tệp .xlsm kể từ ví dụ 6 chứa chức năng VBA tuỳ chỉnh, do đó bạn sẽ phải nhấp vào nút Enable Content sau khi tải xuống, để cho phép macro chạy.

Và đó là tất cả bài học mà chúng tôi muốn dành cho bạn cho ngày hôm nay, cảm ơn các bạn đã đọc!