Mảng động trong Excel

Nội dung

Mảng động là gì

Vào tháng 2018 năm 7, Microsoft đã phát hành bản cập nhật bổ sung một công cụ hoàn toàn mới cho Microsoft Excel: Mảng động và XNUMX chức năng mới để làm việc với chúng. Những điều này, không phóng đại, thay đổi hoàn toàn tất cả các kỹ thuật thông thường làm việc với các công thức và hàm và mối quan tâm, theo nghĩa đen, mọi người dùng.

Hãy xem xét một ví dụ đơn giản để giải thích bản chất.

Giả sử chúng ta có một bảng đơn giản với dữ liệu về tháng thành phố. Điều gì sẽ xảy ra nếu chúng ta chọn bất kỳ ô trống nào ở bên phải trang tính và nhập vào đó một công thức liên kết không đến một ô mà ngay lập tức với một dải ô?

Trong tất cả các phiên bản Excel trước, sau khi nhấp vào đăng ký hạng mục thi chúng ta sẽ chỉ nhận được nội dung của một ô B2 đầu tiên. Làm thế nào khác?

Chà, hoặc có thể bao bọc phạm vi này trong một số loại hàm tổng hợp như = SUM (B2: C4) và nhận tổng số lớn cho nó.

Nếu chúng ta cần các phép toán phức tạp hơn một tổng nguyên thủy, chẳng hạn như trích xuất các giá trị duy nhất hoặc Top 3, thì chúng ta sẽ phải nhập công thức của mình dưới dạng công thức mảng bằng cách sử dụng phím tắt Ctrl+sự thay đổi+đăng ký hạng mục thi.

Bây giờ mọi thứ đã khác.

Bây giờ sau khi nhập một công thức như vậy, chúng ta có thể chỉ cần nhấp vào đăng ký hạng mục thi - và kết quả là ngay lập tức tất cả các giá trị uXNUMXbuXNUMXbto mà chúng tôi đã tham chiếu:

Đây không phải là phép thuật, mà là các mảng động mới mà Microsoft Excel hiện có. Chào mừng đến với thế giới mới 🙂

Các tính năng làm việc với mảng động

Về mặt kỹ thuật, toàn bộ mảng động của chúng tôi được lưu trữ trong ô đầu tiên G4, điền vào số lượng ô cần thiết ở bên phải và xuống bằng dữ liệu của nó. Nếu bạn chọn bất kỳ ô nào khác trong mảng, thì liên kết trong thanh công thức sẽ không hoạt động, cho thấy rằng chúng ta đang ở một trong các ô “con”:

Nỗ lực xóa một hoặc nhiều ô “con” sẽ không dẫn đến bất kỳ điều gì - Excel sẽ ngay lập tức tính toán lại và điền chúng.

Đồng thời, chúng ta có thể tham khảo một cách an toàn các ô “con” này trong các công thức khác:

Nếu bạn sao chép ô đầu tiên của một mảng (ví dụ: từ G4 đến F8), thì toàn bộ mảng (các tham chiếu của nó) sẽ di chuyển theo cùng một hướng như trong các công thức thông thường:

Nếu chúng ta cần di chuyển mảng, thì nó sẽ đủ để di chuyển (bằng chuột hoặc kết hợp Ctrl+X, Ctrl+V), một lần nữa, chỉ ô chính đầu tiên G4 - sau ô này, nó sẽ được chuyển đến một vị trí mới và toàn bộ mảng của chúng ta sẽ được mở rộng trở lại.

Nếu bạn cần tham chiếu ở một nơi khác trên trang tính đến mảng động đã tạo, thì bạn có thể sử dụng ký tự đặc biệt # (“pound”) sau địa chỉ của ô đầu tiên của nó:

Ví dụ: bây giờ bạn có thể dễ dàng tạo danh sách thả xuống trong một ô tham chiếu đến mảng động đã tạo:

Lỗi mảng động

Nhưng điều gì sẽ xảy ra nếu không có đủ không gian để mở rộng mảng hoặc nếu có các ô đã bị chiếm bởi dữ liệu khác trong đường dẫn của nó? Gặp một loại lỗi cơ bản mới trong Excel - #CHUYỂN KHOẢN! (#TRÀN!):

Như mọi khi, nếu chúng ta nhấp vào biểu tượng có hình kim cương màu vàng và dấu chấm than, chúng ta sẽ nhận được lời giải thích chi tiết hơn về nguồn gốc của vấn đề và chúng ta có thể nhanh chóng tìm thấy các ô gây nhiễu:

Các lỗi tương tự sẽ xảy ra nếu mảng đi ra khỏi trang tính hoặc chạm vào một ô đã hợp nhất. Nếu bạn loại bỏ chướng ngại vật, thì mọi thứ sẽ ngay lập tức được sửa chữa nhanh chóng.

Mảng động và bảng thông minh

Nếu mảng động trỏ đến bảng “thông minh” được tạo bằng phím tắt Ctrl+T hoặc bằng cách Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng), thì nó cũng sẽ kế thừa chất lượng chính của nó - tự động định cỡ.

Khi thêm dữ liệu mới vào dưới cùng hoặc bên phải, bảng thông minh và phạm vi động cũng sẽ tự động kéo dài:

Tuy nhiên, có một hạn chế: chúng tôi không thể sử dụng tham chiếu phạm vi động trong công thức diễn đàn bên trong bảng thông minh:

Mảng động và các tính năng Excel khác

Được rồi, bạn nói. Tất cả điều này là thú vị và hài hước. Như trước đây, không cần phải kéo dài công thức theo cách thủ công với tham chiếu đến ô đầu tiên của dải ô ban đầu xuống bên phải và tất cả những điều đó. Và đó là tất cả?

Không khá.

Mảng động không chỉ là một công cụ khác trong Excel. Giờ đây, chúng được gắn vào chính trái tim (hoặc bộ não) của Microsoft Excel - công cụ tính toán của nó. Điều này có nghĩa là các công thức và hàm Excel khác quen thuộc với chúng ta giờ đây cũng hỗ trợ làm việc với mảng động. Hãy xem một vài ví dụ để bạn có ý tưởng về chiều sâu của những thay đổi đã diễn ra.

Chuyển đổi

Để chuyển đổi một phạm vi (hoán đổi hàng và cột) Microsoft Excel luôn có một hàm tích hợp GIAO THÔNG (VẬN CHUYỂN). Tuy nhiên, để sử dụng nó, trước tiên bạn phải chọn chính xác phạm vi cho kết quả (ví dụ: nếu đầu vào là phạm vi 5 × 3, thì bạn phải chọn 3 × 5), sau đó nhập hàm và nhấn sự kết hợp Ctrl+sự thay đổi+đăng ký hạng mục thi, bởi vì nó chỉ có thể hoạt động trong chế độ công thức mảng.

Bây giờ bạn có thể chỉ cần chọn một ô, nhập cùng một công thức vào đó và nhấp vào bình thường đăng ký hạng mục thi - mảng động sẽ tự làm mọi thứ:

Bảng cửu chương

Đây là ví dụ tôi đã sử dụng để đưa ra khi tôi được yêu cầu hình dung những lợi ích của công thức mảng trong Excel. Bây giờ, để tính toán toàn bộ bảng Pitago, chỉ cần đứng trong ô đầu tiên B2, nhập vào đó một công thức nhân hai mảng (tập hợp dọc và ngang của số 1..10) và chỉ cần nhấp vào đăng ký hạng mục thi:

Dán và chuyển đổi trường hợp

Mảng không chỉ có thể được nhân lên mà còn được dán lại với nhau bằng toán tử tiêu chuẩn & (dấu và). Giả sử chúng ta cần trích xuất họ và tên từ hai cột và sửa trường hợp nhảy trong dữ liệu gốc. Chúng tôi làm điều này với một công thức ngắn tạo thành toàn bộ mảng và sau đó chúng tôi áp dụng hàm cho nó TUYÊN BỐ (THÍCH HỢP)để dọn dẹp sổ đăng ký:

Kết luận Top 3

Giả sử chúng ta có một loạt các số mà chúng ta muốn lấy ba kết quả hàng đầu, sắp xếp chúng theo thứ tự giảm dần. Bây giờ điều này được thực hiện bởi một công thức và, một lần nữa, không có bất kỳ Ctrl+sự thay đổi+đăng ký hạng mục thi như trước đây:

Nếu bạn muốn các kết quả không được đặt trong một cột mà trong một hàng, thì bạn chỉ cần thay dấu hai chấm (dấu phân cách dòng) trong công thức này bằng dấu chấm phẩy (dấu phân cách phần tử trong một dòng). Trong phiên bản tiếng Anh của Excel, các dấu phân cách này tương ứng là dấu chấm phẩy và dấu phẩy.

VLOOKUP giải nén nhiều cột cùng một lúc

Chức năng VPR (Vlookup) bây giờ bạn có thể kéo các giá trị không phải từ một mà từ nhiều cột cùng một lúc - chỉ cần chỉ định số của chúng (theo bất kỳ thứ tự mong muốn nào) dưới dạng một mảng trong đối số thứ ba của hàm:

Hàm OFFSET trả về một mảng động

Một trong những hàm thú vị và hữu ích nhất (sau hàm VLOOKUP) để phân tích dữ liệu là hàm TRANH CHẤP (BÙ LẠI), mà tôi đã dành toàn bộ một chương trong cuốn sách của mình và một bài báo ở đây. Khó khăn trong việc hiểu và thành thạo hàm này luôn là kết quả là nó trả về một mảng (phạm vi) dữ liệu, nhưng chúng tôi không thể nhìn thấy nó, bởi vì Excel vẫn chưa biết cách làm việc với các mảng.

Bây giờ vấn đề này là trong quá khứ. Xem cách bây giờ, bằng cách sử dụng một công thức duy nhất và một mảng động được trả về bởi OFFSET, bạn có thể trích xuất tất cả các hàng cho một sản phẩm nhất định từ bất kỳ bảng được sắp xếp nào:

Hãy cùng xem những lập luận của cô ấy:

A1 - ô bắt đầu (điểm tham chiếu)
ПОИСКПОЗ (F2; A2: A30; 0) - tính toán sự thay đổi từ ô bắt đầu xuống - đến bắp cải đầu tiên được tìm thấy.
0 - chuyển “cửa sổ” sang bên phải so với ô bắt đầu
СЧЁТЕСЛИ (A2: A30; F2) - tính toán chiều cao của “cửa sổ” trả về - số dòng có bắp cải.
4 - kích thước của “cửa sổ” theo chiều ngang, tức là xuất ra 4 cột

Các chức năng mới cho mảng động

Ngoài việc hỗ trợ cơ chế mảng động trong các hàm cũ, một số hàm hoàn toàn mới đã được thêm vào Microsoft Excel, đặc biệt để làm việc với mảng động. Cụ thể là:

GRADE (LOẠI) - sắp xếp phạm vi đầu vào và tạo ra một mảng động trên đầu ra
SẮP XẾP (SẮP XẾP) - có thể sắp xếp một phạm vi theo các giá trị từ một phạm vi khác
LỌC (LỌC) - truy xuất các hàng từ phạm vi nguồn đáp ứng các điều kiện được chỉ định
ĐỘC NHẤT (ĐỘC NHẤT) - trích xuất các giá trị duy nhất từ một phạm vi hoặc loại bỏ các bản sao
MƯỢT (RANDARRAY) - tạo một mảng các số ngẫu nhiên có kích thước nhất định
SAU KHI SANH (SỰ PHỐI HỢP) - tạo mảng từ một dãy số với một bước nhất định

Thông tin thêm về chúng - một chút sau đó. Chúng đáng là một bài báo riêng biệt (chứ không phải một bài báo) để nghiên cứu chu đáo 🙂

Kết luận

Nếu bạn đã đọc mọi thứ được viết ở trên, thì tôi nghĩ bạn đã nhận ra quy mô của những thay đổi đã diễn ra. Vì vậy, nhiều thứ trong Excel hiện có thể được thực hiện dễ dàng hơn, dễ dàng hơn và hợp lý hơn. Tôi phải thừa nhận rằng tôi hơi sốc về việc có bao nhiêu bài báo sẽ phải được sửa chữa ở đây, trên trang này và trong sách của tôi, nhưng tôi sẵn sàng làm điều này với một trái tim nhẹ nhàng.

Tổng hợp kết quả, плюсы mảng động, bạn có thể viết như sau:

Bạn có thể quên về sự kết hợp Ctrl+sự thay đổi+đăng ký hạng mục thi. Excel hiện không thấy sự khác biệt giữa “công thức thông thường” và “công thức mảng” và xử lý chúng theo cùng một cách.
Về chức năng SUMPRODUCT (GIỚI THIỆU), trước đây được sử dụng để nhập công thức mảng mà không có Ctrl+sự thay đổi+đăng ký hạng mục thi bạn cũng có thể quên - bây giờ thật dễ dàng TÓM TẮT и đăng ký hạng mục thi.
Các bảng thông minh và các hàm quen thuộc (SUM, IF, VLOOKUP, SUMIFS, v.v.) giờ đây cũng hỗ trợ đầy đủ hoặc một phần các mảng động.
Có khả năng tương thích ngược: nếu bạn mở sổ làm việc có mảng động trong phiên bản Excel cũ, chúng sẽ chuyển thành công thức mảng (trong dấu ngoặc nhọn) và tiếp tục hoạt động theo “kiểu cũ”.

Đã tìm thấy một số nhược điểm:

Bạn không thể xóa các hàng, cột hoặc ô riêng lẻ khỏi một mảng động, tức là nó tồn tại như một thực thể duy nhất.
Bạn không thể sắp xếp một mảng động theo cách thông thường thông qua Dữ liệu - Sắp xếp (Dữ liệu - Sắp xếp). Bây giờ có một chức năng đặc biệt cho điều này. GRADE (LOẠI).
Không thể biến dải động thành bảng thông minh (nhưng bạn có thể tạo dải động dựa trên bảng thông minh).

Tất nhiên, đây chưa phải là kết thúc, và tôi chắc chắn rằng Microsoft sẽ tiếp tục cải thiện cơ chế này trong tương lai.

Tôi có thể tải xuống ở đâu?

Và cuối cùng, câu hỏi chính 🙂

Microsoft lần đầu tiên công bố và hiển thị bản xem trước của các mảng động trong Excel vào tháng 2018 năm XNUMX tại một hội nghị Đốt cháy. Trong vài tháng tới, đã có quá trình thử nghiệm kỹ lưỡng và chạy các tính năng mới, trước tiên ~~mèo~~ nhân viên của chính Microsoft, và sau đó là những người kiểm tra tình nguyện từ vòng kết nối Người dùng nội bộ Office. Năm nay, bản cập nhật bổ sung mảng động bắt đầu được triển khai dần dần cho những người đăng ký Office 365 thông thường. Ví dụ: tôi chỉ nhận được nó vào tháng 365 với đăng ký Office XNUMX Pro Plus (Nhắm mục tiêu hàng tháng) của tôi.

Nếu Excel của bạn chưa có mảng động, nhưng bạn thực sự muốn làm việc với chúng, thì có các tùy chọn sau:

Nếu bạn có đăng ký Office 365, bạn chỉ cần đợi cho đến khi bản cập nhật này đến tay mình. Điều này xảy ra nhanh như thế nào tùy thuộc vào tần suất các bản cập nhật được gửi đến Office của bạn (mỗi năm một lần, sáu tháng một lần, mỗi tháng một lần). Nếu bạn có PC công ty, bạn có thể yêu cầu quản trị viên thiết lập các bản cập nhật để được tải xuống thường xuyên hơn.
Bạn có thể tham gia vào hàng ngũ tình nguyện viên thử nghiệm Người dùng nội bộ Office đó - sau đó bạn sẽ là người đầu tiên nhận được tất cả các tính năng và chức năng mới (tất nhiên là có khả năng xảy ra nhiều lỗi hơn trong Excel).
Nếu bạn không có đăng ký, nhưng có một phiên bản Excel độc lập được đóng hộp, thì ít nhất bạn sẽ phải đợi cho đến khi phát hành phiên bản Office và Excel tiếp theo vào năm 2022. Người dùng của các phiên bản như vậy chỉ nhận được các bản cập nhật bảo mật và sửa lỗi, và tất cả các “tính năng bổ sung” mới hiện chỉ dành cho người đăng ký Office 365. Buồn nhưng có thật 🙂

Trong mọi trường hợp, khi mảng động xuất hiện trong Excel của bạn - sau bài viết này, bạn sẽ sẵn sàng cho nó 🙂

Công thức mảng là gì và cách sử dụng chúng trong Excel
Tổng cửa sổ (phạm vi) sử dụng hàm OFFSET
3 cách chuyển đổi bảng trong Excel