Trong các chương trình bảng tính như Excel và Bảng tính Google, một mảng là một dãy hoặc một loạt các giá trị dữ liệu liên quan được lưu trữ bình thường trong các ô liền kề trong một trang tính.
Một công thức mảng là một công thức thực hiện các phép tính — chẳng hạn như phép cộng, hoặc phép nhân — trên các giá trị trong một hoặc nhiều mảng hơn là một giá trị dữ liệu duy nhất.
Công thức mảng:
- sử dụng cú pháp giống như các công thức thông thường — tất cả đều bắt đầu bằng dấu bằng (=) chẳng hạn;
- sử dụng cùng toán tử;
- và làm theo cùng một thứ tự hoạt động.
Công thức mảng và hàm Excel
Nhiều hàm dựng sẵn của Excel — chẳng hạn như SUM , AVERAGE hoặc COUNT — cũng có thể được sử dụng trong công thức mảng.
Ngoài ra còn có một vài chức năng - chẳng hạn như hàm TRANSPOSE — phải luôn được nhập dưới dạng mảng để nó hoạt động đúng.
Tính hữu ích của nhiều hàm như INDEX và MATCH hoặc MAX và IF có thể được mở rộng bằng cách sử dụng chúng cùng nhau trong một công thức mảng.
Công thức CSE
Trong Excel, các công thức mảng được bao quanh bởi các dấu ngoặc nhọn " {} ". Các dấu ngoặc nhọn này không thể được nhập vào, nhưng phải được thêm vào công thức bằng cách nhấn các phím Ctrl, Shift và Enter sau khi nhập công thức vào ô hoặc ô.
Vì lý do này, một công thức mảng đôi khi được gọi là công thức CSE trong Excel.
Một ngoại lệ cho quy tắc này là khi các dấu ngoặc nhọn được sử dụng để nhập một mảng làm đối số cho một hàm thường chỉ chứa một tham chiếu ô hoặc giá trị đơn.
Ví dụ, trong hướng dẫn dưới đây sử dụng hàm VLOOKUP và hàm CHOOSE để tạo một công thức tra cứu trái, một mảng được tạo ra cho đối số Index_num của hàm CHOOSE bằng cách gõ các dấu ngoặc ôm xung quanh mảng đã nhập.
Các bước để tạo một công thức mảng
- Nhập công thức;
- Giữ phím Ctrl và Shift trên bàn phím;
- Nhấn và nhả phím Enter để tạo công thức mảng;
- Nhả phím Ctrl và Shift .
Nếu được thực hiện đúng, công thức sẽ được bao quanh bởi các dấu ngoặc nhọn và mỗi ô giữ công thức sẽ chứa một kết quả khác.
Chỉnh sửa công thức mảng
Bất cứ lúc nào công thức mảng được chỉnh sửa, dấu ngoặc nhọn biến mất khỏi công thức mảng.
Để đưa chúng trở lại, công thức mảng phải được nhập bằng cách nhấn lại các phím Ctrl, Shift và Enter giống như khi công thức mảng được tạo lần đầu tiên.
Các loại công thức mảng
Có hai loại công thức mảng chính:
- công thức mảng đa ô - cùng một công thức được đặt trong nhiều ô trong một trang tính
- công thức mảng đơn công thức một công thức thực hiện nhiều phép tính trong một ô trang tính đơn lẻ
Công thức mảng đa ô
Giống như tên của họ cho thấy, các công thức mảng được đặt trong nhiều ô bảng tính và chúng cũng trả về một mảng làm câu trả lời.
Nói cách khác, cùng một công thức nằm trong hai hoặc nhiều ô và trả về các câu trả lời khác nhau trong mỗi ô.
Do đó, mỗi bản sao hoặc thể hiện của công thức mảng thực hiện cùng một phép tính trong mỗi ô, nhưng mỗi thể hiện của công thức sử dụng dữ liệu khác nhau trong các phép tính của nó và do đó, mỗi cá thể tạo ra các kết quả khác nhau.
Ví dụ về công thức mảng nhiều ô sẽ là:
{= A1: A2 * B1: B2}
Nếu ví dụ trên nằm trong ô C1 và C2 trong trang tính thì kết quả sau sẽ là:
- dữ liệu trong A1 được nhân với dữ liệu trong B1 và kết quả được lưu trong ô C1
- dữ liệu trong A2 được nhân với dữ liệu trong B2 và kết quả được lưu trữ trong ô C2
Công thức mảng ô đơn
Loại công thức mảng thứ hai này sử dụng hàm, chẳng hạn như SUM, AVERAGE hoặc COUNT, để kết hợp đầu ra của công thức mảng nhiều ô thành một giá trị trong một ô đơn lẻ.
Ví dụ về công thức mảng ô đơn lẻ sẽ là:
{= SUM (A1: A2 * B1: B2)}
Công thức này cộng với sản phẩm của A1 * B1 và A2 * B2 và trả về một kết quả duy nhất trong một ô đơn lẻ trong trang tính.
Một cách khác để viết công thức trên sẽ là:
= (A1 * B1) + (A2 * B2)
Danh sách các công thức mảng Excel
Dưới đây được liệt kê một số hướng dẫn có chứa các công thức mảng Excel.
- Hướng dẫn đầu tiên bao gồm các công thức mảng đa ô
- Tám hướng dẫn tiếp theo là về các công thức mảng ô duy nhất sử dụng nhiều hàm Excel
- Hướng dẫn cuối cùng là chức năng TRANSPOSE
01 trên 10
Công thức mảng Excel đa ô
Công thức mảng nhiều ô hoặc đa ô là công thức mảng nằm trong nhiều ô trong trang tính . Các tính toán tương tự được thực hiện trong nhiều ô sử dụng dữ liệu khác nhau cho mỗi công thức. Hơn "
02 trên 10
Hướng dẫn từng bước một trong Excel
Các công thức mảng ô đơn thường đầu tiên thực hiện phép tính mảng đa ô (chẳng hạn như phép nhân) và sau đó sử dụng hàm như AVERAGE hoặc SUM để kết hợp đầu ra của mảng vào một kết quả duy nhất. Hơn "
03 trên 10
Bỏ qua các giá trị lỗi khi tìm AVERAGE
Công thức mảng này có thể được sử dụng để tìm giá trị trung bình cho dữ liệu hiện có trong khi bỏ qua các giá trị lỗi như # DIV / 0! Hoặc #NAME?
Nó sử dụng hàm AVERAGE cùng với các hàm IF và ISNUMBER. Hơn "
04 trên 10
Công thức Mảng SUM IF của Excel
Sử dụng hàm SUM và hàm IF trong công thức mảng để đếm thay vì tổng các ô dữ liệu đáp ứng một trong một số điều kiện.
Điều này khác với hàm COUNTIFS của Excel yêu cầu phải đáp ứng tất cả các điều kiện đã đặt trước khi ô được tính.
05 trên 10
Excel MAX IF Array Formula để tìm số dương hoặc số âm lớn nhất
Hướng dẫn này kết hợp hàm MAX và hàm IF trong một công thức mảng sẽ tìm giá trị lớn nhất hoặc lớn nhất cho một phạm vi dữ liệu khi một tiêu chí cụ thể được đáp ứng. Hơn "
06 trên 10
Excel MIN IF Công thức mảng - Tìm số dương hoặc số âm nhỏ nhất
Tương tự như bài viết ở trên, điều này kết hợp hàm MIN và hàm IF trong một công thức mảng để tìm giá trị nhỏ nhất hoặc nhỏ nhất cho một phạm vi dữ liệu khi một tiêu chí cụ thể được đáp ứng. Hơn "
07 trên 10
Công thức mảng Excel MEDIAN IF - Tìm giá trị trung bình hoặc trung bình
Hàm MEDIAN trong Excel tìm giá trị trung bình cho một danh sách dữ liệu. Bằng cách kết hợp nó với hàm IF trong một công thức mảng, giá trị trung bình cho các nhóm dữ liệu có liên quan khác nhau có thể được tìm thấy. Hơn "
08 trên 10
Công thức tra cứu với nhiều tiêu chí trong Excel
Bằng cách sử dụng công thức mảng, một công thức tra cứu có thể được tạo sử dụng nhiều tiêu chí để tìm thông tin trong cơ sở dữ liệu. Công thức mảng này bao gồm việc lồng các hàm MATCH và INDEX . Hơn "
09 trên 10
Công thức tra cứu Excel bên trái
Hàm VLOOKUP thường chỉ tìm kiếm dữ liệu nằm trong các cột ở bên phải, nhưng bằng cách kết hợp nó với hàm CHOOSE, công thức tra cứu eft có thể được tạo để tìm kiếm các cột dữ liệu ở bên trái đối số Lookup_value . Hơn "
10 trên 10
Transpose hoặc Flip Row hoặc Cột dữ liệu trong Excel
Hàm TRANSPOSE được sử dụng để sao chép dữ liệu nằm trong một hàng vào một cột hoặc sao chép dữ liệu nằm trong một cột thành một hàng. Hàm này là một trong số ít trong Excel phải luôn được sử dụng làm công thức mảng. Hơn "