Chào các bạn,

Đôi khi chúng ta bắt gặp trên Excel một số công thức nằm trong dấu { … } ví dụ như:

Công thức ở ô C7 có kết quả =4, nội dung công thức là {=MAX[B2:B5_C2:C5]}

Nhưng nếu như chỉ sử dụng công thức =MAX[B2:B5_C2:C5] thì sẽ ra kết quả lỗi.

\=> Ở đây đã sử dụng công thức mảng tại ô C7, ký hiệu { } là ký hiệu chứng tỏ công thức đó là công thức mảng.

1. Công thức mảng là gì?

Công thức mảng là công thức có chứa tham chiếu tới các mảng.

Mảng có thể là 1 vùng ô, hoặc một phần của mỗi phần tử trong vùng ô đó.

Ví dụ: Công thức mảng xét phần bên trái của mỗi phần tử trong vùng A2:A5, xét 4 ký tự phía bên trái của mỗi phần tử.

Công thức mảng có thể đặt ở nhiều ô, hoặc ở 1 nhóm ô. Khi đặt cho 1 nhóm ô thì toàn bộ nhóm ô đó đều chứa cùng 1 công thức mảng, tại mỗi vị trí sẽ cho 1 kết quả khác nhau tương ứng với các phần tử trong mảng

Ví dụ: lấy 2 ký tự bên trái của mỗi phần tử trong mảng từ A2:A5

Công thức mảng được thực hiện khi kết thúc công thức bằng tổ hợp phím Ctrl + Shift + Enter [thay vì chỉ sử dụng phím Enter thông thường]

2. Cách hoạt động của công thức mảng

Trở lại ví dụ sau:

Chúng ta cùng tìm hiểu cách hoạt động của công thức mảng này để giải thích tại sao kết quả lại = 4

Như vậy chúng ta có thể thấy công thức mảng đã được hoạt động như sau:

• Xét hiệu từng phần tử trong mỗi mảng [Phần tử B2 của mảng B2:B5 – Phần tử C2 của mảng C2:C5]
• Tính số lớn nhất trong kết quả tạo bởi việc xét hiệu từng phần tử trong mỗi mảng

Như vậy chỉ với 1 công thức mảng đã có thể giúp chúng ta thu gọn quá trình tính toán đi rất nhiều, vừa tiết kiệm tài nguyên, vừa tiết kiệm thời gian xử lý bảng tính để ra kết quả.

3. Đặc điểm của công thức mảng

Ưu điểm

• Đồng nhất công thức trong một vùng ô kết quả => Giúp đảm bảo mỗi phần tử trong đó đều được tính đúng
• Hạn chế sai sót: Bạn không thể sửa công thức của từng phần tử trong vùng đặt công thức mảng. Khi muốn thay đổi bạn phải chọn cả vùng đó và sửa công thức mảng chung, sau đó kết thúc công thức phải dùng lại thao tác ctrl + shift + enter
• Kích cỡ chương trình nhỏ gọn hơn, tiết kiệm thời gian viết công thức: Thay vì sử dụng mỗi ô 1 công thức gần giống nhau, chúng ta có thể chỉ cần sử dụng duy nhất 1 công thức mảng. Như vậy chương trình tính cũng nhanh hơn, dung lượng thấp hơn.

Nhược điểm

• Quên sử dụng tổ hợp phím Ctrl + Shift + Enter => Xảy ra lỗi

  N/A

• Nếu chưa hiểu về công thức mảng thì rất khó hiểu, khó sử dụng
• Công thức mảng trên phạm vi quá lớn, bị lạm dụng có thể làm tốc độ tính toán bị chậm đi do khối lượng tính toán tăng lên nhiều

Như vậy chúng ta đã nắm được kiến thức về công thức mảng rồi phải không nào. Các bạn cần cân nhắc kỹ ưu điểm và nhược điểm của công thức mảng để sử dụng sao cho hiệu quả nhất nhé.

Bạn đang ở:Trang chủ / Excel / Sức mạnh của công thức mảng trong trích lọc dữ liệu và báo cáo

Công thức mảng là công cụ vô cùng mạnh mẽ và hữu ích nhưng cũng rất phức tạp, trừu tượng và đầy ảo diệu nên không có nhiều người biết cách sử dụng. Công thức mảng giúp chúng ta có thể giải quyết được rất nhiều bài toán mà sử công thức thông thường sẽ rất khó hoặc phải áp dụng những công thức rất phức tạp mới xử lý được. Vậy công thức mảng là gì? Mảng là một hàng giá trị, một cột giá trị hoặc là một kết hợp các hàng và cột giá trị. [ví dụ: {1,2,3,4}] Công thức mảng là công thức được bao bởi cặp dấu ngoặc nhọn {} do excel tự động thêm vào sau khi kết thúc nhập công thức. Công thức mảng là công thức có thể thực hiện nhiều phép tính đối với một hoặc nhiều mục trong mảng. Công thức mảng có thể trả về nhiều kết quả hoặc một kết quả duy nhất Công thức mảng bao gồm nhiều ô được gọi là công thức đa ô và công thức mảng trong một ô duy nhất được gọi là công thức đơn ô. Nhập công thức mảng: Chọn ô hoặc vùng ô cần nhập công thức. Nhập công thức cần tính toán Nhấn tổ hợp phím Ctrl + Shift + Enter Bây giờ các bạn xem các ví dụ dưới đây để thấy rõ hơn công dụng của công thức mảng nhé Tính tổng nhanh theo dãy VD1: Tính thành tiền thu mua sản phẩm của từng nhân viên trong bảng sau

Thông thường tại ô G5 các bạn sẽ nhập công thức =E5*F5 enter rồi sao chép xuống các ô còn lại. Tuy nhiên khi dùng công thức mảng, các bạn chỉ cần làm như sau: Chọn vùng ô G5:G11 sau đó nhập công thức =E5:E11*G5:G11 rồi nhấn tổ hợp các phím Ctrl + Shift + Enter ta nhận được kết quả như sau

Ví dụ 2: Cũng bảng dữ liệu trên, các bạn cần tính tổng thành tiền thu mua sản phẩm của tất cả các nhân viên Thay vì các bạn phải tính thành tiền của từng nhân viên ra rồi cộng lại. Các bạn chỉ cần nhập vào ô cần hiển thị kết quả công thức như sau: =SUM[E5:E11*F5:F11] sau đó nhấn tổ hợp phím Ctrl + Shift + Enter, ta nhận được kết quả

Tính tổng theo điều kiện, đếm theo điều kiện Ví dụ 3: Cũng bảng dữ liệu trên, các bạn cần đếm số nhân viên thu mua sản phẩm trên 10 tạ Thay vì dùng countif[] để đếm thì bạn chỉ cần sử dụng công thức mảng như sau \=SUM[–[E5:E11>10]]  nhấn tổ hợp Ctrl + Shift + Enter ta được kết quả là 3

Trong trường hợp các bạn muốn tính tổng số lượng thu mua của tất cả các nhân viên mua được sản phẩm trên 10 tạ Các bạn nhập công thức như sau: =SUM[[E5:E11>10]*[E5:E11]] nhấn tổ hợp Ctrl + Shift + Enter ta được kết quả là 40.7

Ví dụ 4: Tính tổng số lượng theo Top cao nhất hoặc thấp nhất Cũng với bảng dữ liệu trên, yêu cầu tính tổng số lượng thu mua được của 4 nhân viên có số lượng thu mua lớn nhất Tại ô cần tính ta nhập công thức =SUM[LARGE[E5:E11,{1,2,3,4}]] Nhấn tổ hợp phím Ctrl + Shift + enter ta nhận được kết quả là 47.7

Ví dụ 5: Cách khác để đánh số thứ tự sử dụng công thức mảng Giả sử các bạn muốn nhập giá trị lần lượt cho cột STT trong bảng sau

Thay vì các bạn nhập 2 số đầu tiên của dãy vào rồi tự động điền theo sap chép công thức thì các bạn sử dụng công thức mảng như sau Các bạn bôi đen dải ô B5:B11 sau đó nhập công thức \=ROW[D5:D11]-ROW[D5]+1 rồi nhấn tổ hợp phím Ctrl + Shift + enter Ta nhận được kết quả

Xem thêm: Kết hợp hàm Sum và If trong công thức mảng để tính tổng đa chiều

Công thức mang được đặt trong đầu gì?

Sự khác biệt chính là ở chỗ khi dùng công thức mảng, bạn nhấn Ctrl+Shift+Enter để nhập công thức. Khi bạn làm điều này, Excel đặt công thức mảng của bạn trong một cặp dấu ngoặc nhọn — nếu bạn nhập dấu ngoặc nhọn theo cách thủ công, thì công thức của bạn sẽ được chuyển thành chuỗi văn bản và nó sẽ không hoạt động.

Làm sao để bỏ công thức trong Excel?

Xóa bỏ công thức mảng.

Bấm vào một ô trong công thức mảng..

Trên tab Trang đầu, trong nhóm Sửa, bấm Tìm & Chọn, rồi bấm Đi Tới..

Bấm Đặc biệt..

Bấm Mảng hiện tại..

Nhấn DELETE..

Làm sao để ăn công thức trong Excel?

Để ẩn công thức Excel, hãy thực hiện các bước sau..

Chọn một ô hoặc dải ô chứa các công thức bạn muốn ẩn. ... .

Mở hộp thoại Format Cells bằng cách thực hiện bất kỳ thao tác nào sau đây: ... .

Trong hộp thoại format cells, chuyển sang tab Protection và chọn hộp kiểm Ẩn [hidden]. ... .

Nhấp vào nút OK..

Dấu ngoặc nhọn trong Excel có tác dụng gì?

Khi nhấn tổ hợp phím trên, Microsoft Excel đặt công thức trên trong {dấu ngoặc nhọn}, hay được gọi một dấu hiệu dễ nhìn thấy cho biết đó là một công thức mảng. Công thức sẽ nhân giá trị trong mỗi hàng riêng của mảng được chỉ định [các ô từ B2 đến C6], có thêm các tổng [phụ], và kết quả cuối cùng là tổng doanh thu.