Các ví dụ công thử mảng cho Excel dành cho người mới bắt đầu và người dùng muốn tìm hiểu sâu

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))

 An Excel array formula to count cells that meet two conditions

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:

Select portions of the formula and press F9 to view the arrays behind the formula parts.

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:

Using several functions in Excel array formulas

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.

>>> Xem thêm:  Nối dữ liệu trên nhiều dòng Excel theo nhóm bằng VBA

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))

 Array formula to count any given character(s) in a ranges

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

An array formula to sum values in every other or N<sup>th</sup> row

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ể.

Source data

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”))))))

 Array formula to perform different calculations on numbers within different ranges

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):

>>> Xem thêm:  Tìm hiểu về các hàm làm tròn số trong Excel và các nguyên tắc làm tròn số

= 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ể