An Excel công thức mảnglà công thức thực hiện các phép tính trên các giá trị trong một hoặc nhiều mảng chứ không phải một giá trị dữ liệu duy nhất. Trong chương trình bảng tính, mảng là một dải ô hoặc chuỗi các giá trị dữ liệu có liên quan thường nằm trong các ô liền kề trong một trang tính.
Những hướng dẫn này áp dụng cho Excel 2019, 2016, 2013, 2010 và Excel cho Microsoft 365.
Công thức Mảng là gì?
Công thức mảng tương tự như các công thức thông thường. Họ:
- Bắt đầu bằng dấu bằng (=)
- Sử dụng cú pháp tương tự như các công thức thông thường
- Sử dụng các toán tử toán học giống nhau
- Thực hiện theo trình tự hoạt động tương tự
Tuy nhiên, công thức mảng được bao quanh bởi dấu ngoặc nhọn {}. Và bạn không thể chỉ nhập chúng vào; bạn phải thêm chúng bằng cách nhấn các phím Ctrl,Shiftvà Entersau khi nhập công thức vào ô hoặc các ô. Vì lý do này, công thức mảng đôi khi được gọi là công thức CSEtrong Excel.
Bất kỳ khi nào bạn chỉnh sửa công thức mảng, dấu ngoặc nhọn sẽ biến mất. Để lấy lại chúng, nhấn lại các phím Ctrl,Shiftvà Enter.
Có hai loại công thức mảng chính:
- Công thức mảng một ô thực hiện nhiều phép tính trong một ô trang tính
- Công thức mảng nhiều ô có trong nhiều ô trang tính
Cách tạo Công thức Mảng
- Nhập công thức vào một ô.
- Nhấn giữ phím Ctrlvà Shifttrên bàn phím.
- Nhấn và thả phím Enterđể tạo công thức mảng.
- Thả phím Ctrlvà Shift.
- Nếu được thực hiện đúng, dấu ngoặc nhọn sẽ bao quanh công thức.
Công thức mảng ô đơn
Một công thức mảng ô đơn sử dụng một hàm, chẳng hạn như SUM,AVERAGEhoặc COUNT, để kết hợp đầu ra của công thức mảng nhiều ô thành một giá trị duy nhất trong một ô. Dưới đây là một ví dụ:
{=SUM (A1: A2B1: B2)}
Công thức ở trên cộng tích số của A1B1và A2B2, rồi trả về một kết quả duy nhất trong một một ô trong trang tính. Một cách khác để trình bày công thức đó là:
=(A1B1) + (A2B2)
Công thức Mảng Nhiều Ô
Đúng như tên gọi, công thức mảng nhiều ô nằm trong nhiều ô trang tính và chúng trả về một mảng dưới dạng 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à nó trả về các câu trả lời khác nhau trong mỗi ô.
Mỗi bản sao, hoặc bản sao của công thức mảng, thực hiện cùng một phép tính trong mỗi ô mà nó sinh ra, nhưng mỗi bản sao sử dụng dữ liệu khác nhau. Do đó, mỗi loại tạo ra kết quả khác nhau. Ví dụ về công thức mảng nhiều ô là:
{=A1: A2B1: B2}
Nếu công thức mảng ở trên nằm trong ô C1và C2trong một trang tính, thì kết quả sẽ như sau:
- Dữ liệu trong A1 được nhân với dữ liệu trong B1và kết quả xuất hiện trong ô C1.
- Dữ liệu trong A2 được nhân với dữ liệu trong B2và kết quả xuất hiện trong ô C2.
Công thức Mảng và Hàm Excel
Bạn có thể sử dụng nhiều hàm có sẵn của Excel, chẳng hạn như SUM,AVERAGEvà COUNT, trong một công thức mảng. Ngoài ra còn có một số hàm, chẳng hạn như TRANSPOSE, luôn phải là một công thức mảng để hoạt động chính xác. (Hàm TRANSPOSEsao chép dữ liệu từ một hàng vào một cột hoặc ngược lại.)
Bạn cũng có thể mở rộng tính hữu dụng của nhiều chức năng như INDEXvà MATCHhoặc MAXvà IFbằng cách sử dụng chúng cùng nhau trong một công thức mảng.
Tạo Công thức Mảng Ô đơn Đơn giản
Công thức mảng ô đơn trước tiên thường thực hiện phép tính nhiều ô và sau đó sử dụng một hàm như AVERAGEhoặc SUMđể kết hợp kết quả đầu ra của mảng thành một kết quả duy nhất.
Bỏ qua Giá trị Lỗi khi Tìm Dữ liệu
Công thức mảng này sử dụng các hàm AVERAGE,IFvà ISNUMBER để 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!và NAME ?
Đếm ô dữ liệu
Sử dụng hàm SUMvà IFtrong công thức mảng để đếm các ô dữ liệu đáp ứng một trong một số điều kiện; kỹ thuật này khác với việc sử dụng hàm COUNTIFScủa Excel, hàm này yêu cầu đáp ứng tất cả các điều kiện đã đặt trước khi nó đếm ô.
Tìm số dương hoặc số âm lớn nhất
Ví dụ này kết hợp hàm MAXvà hàm IFtrong 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 nó đáp ứng các tiêu chí cụ thể. Ở đây, giá trị lớn nhất thể hiện thời gian chậm nhất.
Tìm số dương hoặc số âm nhỏ nhất
Tương tự như ví dụ trên, bạn có thể kết hợp các hàm MINvà IFtrong một công thức mảng để tìm giá trị nhỏ nhất hoặc nhỏ nhất cho một loạt dữ liệu khi nó đáp ứng các tiêu chí cụ thể.
Tìm Giá trị Trung bình hoặc Giá trị Trung bình
Hàm MEDIANtrong Excel tìm giá trị giữa cho danh sách dữ liệu. Bằng cách kết hợp nó với hàm IFtrong một công thức mảng, bạn có thể tìm thấy giá trị giữa cho các nhóm dữ liệu liên quan khác nhau.
Tạo Công thức Tra cứu với Nhiều Tiêu chí
Công thức mảng này liên quan đến việc lồng các hàm MATCHvà INDEXđể tìm thông tin cụ thể trong cơ sở dữ liệu.
Tạo Công thức Tra cứu Bên trái
Hàm Vlookupthườ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 CHỌN, bạn có thể tạo công thức tra cứu bên trái sẽ tìm kiếm các cột dữ liệu ở bên trái của đối số Lookup_value.
Khi nhập một mảng làm đối số cho một hàm thường chỉ chứa một giá trị hoặc tham chiếu ô duy nhất, bạn có thể nhập trực tiếp vào dấu ngoặc nhọn, thay vì sử dụng Ctrl + Shift + EnterTổ hợp phím, như trong ví dụ trên.
