Công thức mảng động Excel và các ví dụ


Công thức mảng động Excel và các ví dụ

Các công thức mảng Excel luôn được coi là một đặc quyền của các chuyên gia về kinh nghiệm và công thức. Nếu ai đó nói với bạn, "Điều này có thể được thực hiện bằng một công thức mảng", một phản ứng ngay lập tức của nhiều người dùng là "Ồ, không có cách nào khác sao?".

Sự ra đời của mảng động là một thay đổi được chờ đợi từ lâu và được hoan nghênh nhất. Do khả năng làm việc với nhiều giá trị một cách đơn giản, không có bất kỳ thủ thuật và quirks nào, công thức mảng động là thứ mà mọi người dùng Excel có thể hiểu và thích tạo.

Mảng động Excel là gì?

Mảng động là các mảng có thể thay đổi kích thước tính toán tự động và trả về giá trị thành nhiều ô.
 
Trải qua hơn 30 năm lịch sử, Microsoft Excel đã trải qua nhiều thay đổi, nhưng một điều vẫn không đổi - một công thức, một tế bào. Ngay cả với các công thức mảng truyền thống, cần phải nhập công thức vào từng ô nơi bạn muốn kết quả xuất hiện. Với mảng động, quy tắc này không còn đúng nữa. Bây giờ, bất kỳ công thức nào trả về một mảng các giá trị sẽ tự động tràn vào các ô lân cận mà không cần bạn phải nhấn Ctrl + Shift + Enter hoặc làm bất kỳ động thái khác. Nói cách khác, vận hành mảng động trở nên dễ dàng như làm việc với một ô duy nhất.
 
Hãy để tôi minh họa khái niệm với một ví dụ rất cơ bản. Giả sử, bạn cần nhân hai nhóm số, ví dụ, để tính tỷ lệ phần trăm khác nhau.
 
Trong các phiên bản tiền động của Excel, công thức dưới đây sẽ chỉ hoạt động cho ô đầu tiên, trừ khi bạn nhập nó vào nhiều ô và nhấn Ctrl + Shift + Enter để rõ ràng làm cho nó một công thức mảng:
 
=A3:A5*B2:D2
 
 
Bây giờ, hãy xem điều gì xảy ra khi sử dụng cùng một công thức trong Excel 365. Bạn nhập nó vào chỉ một ô (B3 trong trường hợp của chúng tôi), nhấn phím Enter và có thể chứa toàn bộ cơn thịnh nộ cùng một lúc:
 
 
Làm đầy nhiều ô với một công thức duy nhất được gọi là đổ tràn và phạm vi ô được điền được gọi là phạm vi tràn .
 
Một điều quan trọng cần lưu ý là bản cập nhật gần đây không chỉ là một cách xử lý mảng mới trong Excel. Trên thực tế, đây là một thay đổi đột phá đối với toàn bộ công cụ tính toán. Với các mảng động, một loạt các hàm mới đã được thêm vào Thư viện hàm Excel và các hàm hiện có bắt đầu hoạt động nhanh hơn và hiệu quả hơn. Cuối cùng, các mảng động mới được cho là sẽ thay thế hoàn toàn các công thức mảng lỗi thời được nhập bằng Ctrl + Shift + Enter đường tắt.

Hàm mảng động Excel

Là một phần của chức năng mới, 6 chức năng mới đã được giới thiệu trong Excel 365 xử lý các mảng nguyên bản và xuất dữ liệu vào một phạm vi ô. Đầu ra luôn động - khi có bất kỳ thay đổi nào xảy ra trong dữ liệu nguồn, kết quả sẽ tự động cập nhật. Do đó tên nhóm - hàm mảng động .
 
Các chức năng mới này dễ dàng đối phó với một số nhiệm vụ thường được coi là hạt cứng để bẻ khóa. Ví dụ: họ có thể loại bỏ trùng lặp, trích xuất và đếm các giá trị duy nhất, lọc ra khoảng trống, tạo số nguyên ngẫu nhiên và số thập phân, sắp xếp theo thứ tự tăng dần hoặc giảm dần, và nhiều hơn nữa.
 
Dưới đây bạn sẽ tìm thấy một mô tả ngắn gọn về những gì mỗi chức năng làm cũng như các liên kết đến các hướng dẫn chuyên sâu:
 
UNIQUE  - trích xuất các vật phẩm độc đáo từ một loạt các tế bào.
FILTER - lọc dữ liệu dựa trên các tiêu chí bạn xác định.
SORT - sắp xếp một phạm vi ô theo một cột được chỉ định.
SORTBY - sắp xếp một phạm vi ô theo một phạm vi hoặc mảng khác.
RANDARRAY - tạo ra một mảng các số ngẫu nhiên.
SEQUENCE - tạo danh sách các số liên tiếp.
Ngoài ra, có hai thay thế hiện đại của các hàm Excel phổ biến, không chính thức trong nhóm, nhưng tận dụng tất cả các lợi thế của mảng động:
 
XLOOKUP - là sự kế thừa mạnh mẽ hơn của VLOOKUP , HLOOKUP và LOOKUP có thể tra cứu cả trong cột và hàng và trả về nhiều giá trị.
 
XMATCH - là một kế thừa linh hoạt hơn của hàm MATCH có thể thực hiện tra cứu dọc và ngang và trả về vị trí tương đối của mục được chỉ định.

Công thức mảng động Excel

Trong các phiên bản mới nhất của Excel 365, hành vi mảng động được tích hợp sâu và trở thành nguồn gốc cho tất cả các chức năng , ngay cả những chức năng ban đầu không được thiết kế để hoạt động với mảng. Nói một cách đơn giản, đối với bất kỳ công thức nào trả về nhiều hơn một giá trị, Excel sẽ tự động tạo một phạm vi có thể thay đổi kích thước mà kết quả là đầu ra. Do khả năng này, các chức năng hiện có có thể thực hiện phép thuật!
 
Các ví dụ dưới đây cho thấy các công thức mảng động mới đang hoạt động cũng như ảnh hưởng của các mảng động đối với các hàm hiện có.

Ví dụ 1. Hàm mảng động mới

Ví dụ này cho thấy một giải pháp có thể được thực hiện nhanh hơn và đơn giản hơn với các hàm mảng động Excel.
 
Để trích xuất danh sách các giá trị duy nhất từ ​​một cột, theo truyền thống, bạn thường sử dụng công thức CSE phức tạp như công thức này . Trong Excel 365, tất cả những gì bạn cần là một công thức UNIQUE ở dạng cơ bản:
 
=UNIQUE(B2:B10)
 
Bạn nhập công thức vào bất kỳ ô trống nào và nhấn Enter. Excel ngay lập tức trích xuất tất cả các giá trị khác nhau trong danh sách và xuất chúng thành một phạm vi ô bắt đầu từ ô nơi bạn nhập công thức (trong trường hợp của chúng tôi là D2). Khi dữ liệu nguồn thay đổi, kết quả được tính toán lại và cập nhật tự động.

Ví dụ 2. Kết hợp một số hàm mảng động trong một công thức

Nếu không có cách nào để hoàn thành một nhiệm vụ với một chức năng, hãy xâu chuỗi một vài nhiệm vụ lại với nhau! Ví dụ: để lọc dữ liệu dựa trên điều kiện và sắp xếp kết quả theo thứ tự bảng chữ cái, hãy bọc hàm SORT xung quanh LỌC như thế này:
 
=SORT(FILTER(A2:C13, B2:B13=F1, "No results"))
 
Trong đó A2: C13 là dữ liệu nguồn, B2: B13 là các giá trị cần kiểm tra và F1 là tiêu chí.
 
 

Ví dụ 3. Sử dụng các hàm mảng động mới cùng với các hàm hiện có

Vì công cụ tính toán mới được triển khai trong Excel 365 có thể dễ dàng biến các công thức thông thường thành mảng, không có gì ngăn bạn kết hợp các hàm mới và cũ với nhau.
 
Chẳng hạn, để đếm xem có bao nhiêu giá trị duy nhất trong một phạm vi nhất định, hãy lồng hàm UNIITE mảng động vào hàm COUNTA cũ:
 
=COUNTA(UNIQUE(B2:B10))
 
 

Ví dụ 4. Các hàm hiện có hỗ trợ mảng động

Nếu bạn cung cấp một phạm vi ô cho hàm TRIM trong phiên bản cũ hơn như Excel 2016 hoặc Excel 2019, nó sẽ trả về một kết quả duy nhất cho ô đầu tiên:
 
=TRIM(A2:A6)
 
Trong Excel động, cùng một công thức xử lý tất cả các ô và trả về nhiều kết quả, như được hiển thị bên dưới:

Ví dụ 5. Công thức VLOOKUP để trả về nhiều giá trị

Như mọi người đều biết, hàm VLOOKUP được thiết kế để trả về một giá trị duy nhất dựa trên chỉ mục cột mà bạn chỉ định. Tuy nhiên, trong Excel 365, bạn có thể cung cấp một mảng các số cột để trả về kết quả khớp từ một số cột:
 
=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)
 

Ví dụ 6. Công thức TRANSPOSE được thực hiện dễ dàng

Trong các phiên bản Excel trước đó, cú pháp của hàm TRANSPOSE không còn chỗ cho các lỗi. Để xoay dữ liệu trong trang tính của bạn, bạn cần đếm các cột và hàng ban đầu, chọn cùng một số ô trống nhưng thay đổi hướng (một thao tác gây chú ý trong các trang tính lớn!), Nhập công thức TRANSPOSE trong phạm vi đã chọn và nhấnCtrl + Shift + Enterđể hoàn thành nó một cách chính xác. Phù!
 
Trong Excel động, bạn chỉ cần nhập công thức vào ô ngoài cùng bên trái của phạm vi đầu ra và nhấn Enter:
 
=TRANSPOSE(A1:B10)
 
Làm xong!
 
 
Bài tập công thức mảng trong Excel
Công thức mảng SUMIFS
Công thức mảng tính tổng nhiều điều kiện
Cách xóa công thức mảng trong Excel
Dụng công thức mảng để thông kê
Công thức mảng INDEX
Công thức mảng COUNTIF
Hàm IF trong công thức mảng
 
Chúc bạn thành công !

Các tin cũ hơn