Excel

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

Dynamic Array Formulas Excel

Mảng động là thay đổi lớn nhất đối với công thức Excel trong nhiều năm. Có lẽ là thay đổi lớn nhất từ ​​trước đến nay. Điều này là do Mảng động cho phép bạn dễ dàng làm việc với nhiều giá trị cùng lúc trong một công thức. Đối với nhiều người dùng, đây sẽ là lần đầu tiên họ hiểu và sử dụng công thức mảng.





Đây là một nâng cấp lớn và thay đổi đáng hoan nghênh. Mảng động sẽ giải quyết một số vấn đề thực sự khó trong Excel và thay đổi cơ bản cách trang tính được thiết kế và xây dựng.

khả dụng

Mảng động và các chức năng mới bên dưới chỉ khả dụng Excel 365 . Excel 2016 và Excel 2019 không cung cấp hỗ trợ công thức mảng động. Để thuận tiện, tôi sẽ sử dụng 'Excel động' (Excel 365) và 'Excel truyền thống' (2019 trở xuống) để phân biệt các phiên bản bên dưới.





Mới: Video đào tạo về Công thức mảng động

Các chức năng mới

Là một phần của bản cập nhật mảng động, Excel hiện bao gồm 8 hàm mới tận dụng trực tiếp mảng động để giải quyết các vấn đề truyền thống khó giải bằng các công thức thông thường. Nhấp vào liên kết bên dưới để biết chi tiết và ví dụ cho từng chức năng:

Hàm số Mục đích
LỌC Lọc dữ liệu và trả về các bản ghi phù hợp
RANDARRAY Tạo mảng số ngẫu nhiên
SỰ NỐI TIẾP Tạo mảng số tuần tự
LOẠI Sắp xếp phạm vi theo cột
SORTBY Sắp xếp phạm vi theo một phạm vi hoặc mảng khác
DUY NHẤT Trích xuất các giá trị duy nhất từ ​​danh sách hoặc phạm vi
XLOOKUP Thay thế hiện đại cho VLOOKUP
XMATCH Thay thế hiện đại cho chức năng MATCH

Băng hình: Các hàm mảng động mới trong Excel (khoảng 3 phút).



cách tính độ lệch chuẩn của giá trị trung bình trong excel

Ghi chú: XLOOKUP và XMATCH không nằm trong nhóm ban đầu của các hàm mảng động mới, nhưng chúng chạy rất tốt trên công cụ mảng động mới. XLOOKUP thay thế hàm VLOOKUP và cung cấp một cách tiếp cận hiện đại, linh hoạt tận dụng các mảng. XMATCH là bản nâng cấp cho chức năng MATCH, cung cấp các khả năng mới để INDEX và MATCH các công thức.

Thí dụ

Trước khi đi vào chi tiết, chúng ta hãy xem một ví dụ đơn giản. Dưới đây chúng tôi đang sử dụng Hàm UNIQUE để trích xuất các giá trị duy nhất từ ​​phạm vi B5: B15, với Độc thân công thức đã nhập trong E5:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

Ví dụ về hàm UNIQUE

Kết quả là danh sách năm tên thành phố duy nhất, xuất hiện trong E5: E9.

Giống như tất cả các công thức, UNIQUE sẽ tự động cập nhật khi dữ liệu thay đổi. Dưới đây, Vancouver đã thay thế Portland ở hàng 11. Kết quả từ UNIQUE hiện bao gồm Vancouver:

Ví dụ về hàm UNIQUE sau khi thay đổi

Spilling - một công thức, nhiều giá trị

Trong Excel động, các công thức trả về nhiều giá trị sẽ ' trò chơi 'các giá trị này trực tiếp vào trang tính. Điều này ngay lập tức sẽ hợp lý hơn đối với người dùng công thức. Nó cũng là một hành vi hoàn toàn động - khi dữ liệu nguồn thay đổi, kết quả tràn sẽ ngay lập tức cập nhật.

Hình chữ nhật bao quanh các giá trị được gọi là ' phạm vi trò chơi '. Bạn sẽ nhận thấy rằng phạm vi tràn có điểm nhấn đặc biệt. Trong ví dụ DUY NHẤT ở trên, phạm vi tràn là E5: E10.

Khi dữ liệu thay đổi, phạm vi tràn sẽ mở rộng hoặc thu hẹp khi cần thiết. Bạn có thể thấy các giá trị mới được thêm vào hoặc các giá trị hiện có biến mất. Theo cách này, phạm vi tràn là một loại phạm vi động mới.

Lưu ý: khi quá trình đổ bị chặn bởi dữ liệu khác, bạn sẽ thấy lỗi #SPILL. Khi bạn dành chỗ cho phạm vi tràn, sữa công thức sẽ tự động tràn.

Băng hình: Tràn và phạm vi tràn

Tham chiếu phạm vi tràn

Để tham chiếu đến phạm vi tràn, hãy sử dụng ký hiệu băm (#) sau ô đầu tiên trong phạm vi. Ví dụ: để tham chiếu kết quả từ hàm UNIQUE ở trên, hãy sử dụng:

 
=E5# // reference UNIQUE results

Điều này cũng giống như tham chiếu toàn bộ phạm vi tràn và bạn sẽ thấy cú pháp này khi viết công thức đề cập đến phạm vi tràn hoàn chỉnh.

Bạn có thể cung cấp trực tiếp tham chiếu phạm vi tràn vào các công thức khác. Ví dụ: để đếm số lượng thành phố được trả về bởi UNIQUE, bạn có thể sử dụng:

 
= COUNTA (E5#) // count unique cities

Ví dụ về tham chiếu phạm vi tràn mảng động

Khi phạm vi tràn thay đổi, công thức sẽ phản ánh dữ liệu mới nhất.

Đơn giản hóa hàng loạt

Việc bổ sung các công thức mảng động mới có nghĩa là một số công thức nhất định có thể được đơn giản hóa đáng kể. Đây là vài ví dụ:

  • Trích xuất và liệt kê các giá trị duy nhất ( trước | sau )
  • Đếm các giá trị duy nhất ( trước | sau )
  • Lọc và trích xuất các bản ghi ( trước | sau )
  • Trích xuất các kết quả phù hợp từng phần ( trước | sau )

Sức mạnh của một

Một trong những lợi ích mạnh mẽ nhất của phương pháp tiếp cận 'một công thức, nhiều giá trị' là ít phụ thuộc vào tuyệt đối hoặc Trộn người giới thiệu. Khi công thức mảng động đổ kết quả vào trang tính, các tham chiếu vẫn không thay đổi, nhưng công thức tạo ra kết quả chính xác.

Ví dụ, bên dưới chúng tôi sử dụng hàm FILTER để trích xuất các bản ghi trong nhóm 'A'. Trong ô F5, một công thức duy nhất được nhập:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

Ví dụ chỉ về mảng động một công thức

Lưu ý rằng cả hai phạm vi đều là tham chiếu tương đối được mở khóa, nhưng công thức hoạt động hoàn hảo.

Đây là một lợi ích rất lớn đối với nhiều người dùng, bởi vì nó làm cho quá trình viết công thức trở nên đơn giản hơn rất nhiều. Để có một ví dụ điển hình khác, hãy xem bảng cửu chương bên dưới.

Chuỗi chức năng

Mọi thứ trở nên thực sự thú vị khi bạn xâu chuỗi nhiều hơn một hàm mảng động lại với nhau. Có lẽ bạn muốn sắp xếp các kết quả trả về bởi UNIQUE? Dễ. Chỉ cần quấn Hàm SORT xung quanh hàm UNIQUE như thế này:

Ví dụ về UNIQUE và SORT cùng nhau

Như trước đây, khi dữ liệu nguồn thay đổi, các kết quả duy nhất mới sẽ tự động xuất hiện, được sắp xếp độc đáo.

Hành vi bản địa

Điều quan trọng là phải hiểu rằng hành vi của mảng động là bản địa và tích hợp sâu . Khi nào không tí nào công thức trả về nhiều kết quả, các kết quả này sẽ tràn vào nhiều ô trên trang tính. Điều này bao gồm các chức năng cũ hơn không được thiết kế ban đầu để hoạt động với mảng động.

Ví dụ: trong Excel truyền thống, nếu chúng tôi cung cấp Hàm LEN đến phạm vi giá trị văn bản, chúng ta sẽ thấy một Độc thân kết quả. Trong Excel động, nếu chúng ta cung cấp cho hàm LEN một phạm vi giá trị, chúng ta sẽ thấy nhiều kết quả. Màn hình dưới đây hiển thị hành vi cũ ở bên trái và hành vi mới ở bên phải:

Hàm LEN với mảng - cũ và mới

Đây là một thay đổi lớn có thể ảnh hưởng đến tất cả các loại công thức. Ví dụ, Hàm VLOOKUP được thiết kế để tìm nạp một giá trị duy nhất từ ​​bảng, sử dụng chỉ mục cột. Tuy nhiên, trong Excel động, nếu chúng ta cung cấp hàm VLOOKUP nhiều hơn một chỉ mục cột bằng cách sử dụng mảng hằng số như thế này:

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP sẽ trả về nhiều cột:

Nhiều kết quả với VLOOKUP và mảng động

Nói cách khác, mặc dù hàm VLOOKUP không bao giờ được thiết kế để trả về nhiều giá trị, nhưng giờ đây nó có thể làm như vậy, nhờ vào công cụ công thức mới trong Dynamic Excel.

Tất cả các công thức

Cuối cùng, lưu ý rằng mảng động hoạt động với tất cả các công thức không chỉ là chức năng . Trong ví dụ dưới đây, ô C5 chứa một công thức:

công thức excel cho phần trăm là gì
 
=B5:B14*C4:L4

Kết quả tràn vào phạm vi 10 x 10 bao gồm 100 ô:

Bảng cửu chương mảng động

Lưu ý: Trong Excel Truyền thống, bạn có thể thấy nhiều kết quả được trả về bởi công thức mảng nếu bạn sử dụng F9 để kiểm tra công thức . Nhưng trừ khi bạn đang nhập công thức dưới dạng công thức mảng nhiều ô , chỉ một giá trị sẽ hiển thị trên trang tính.

Mảng trở thành xu hướng chủ đạo

Với việc triển khai các mảng động, từ ' mảng 'sẽ xuất hiện thường xuyên hơn. Trên thực tế, bạn có thể thấy 'mảng' và 'dải ô' được sử dụng gần như thay thế cho nhau. Bạn sẽ thấy các mảng trong Excel được đặt trong dấu ngoặc nhọn như sau:

 
{1,2,3} // horizontal array {123} // vertical array

Mảng là một thuật ngữ lập trình đề cập đến một danh sách các mục xuất hiện theo một thứ tự cụ thể. Lý do các mảng xuất hiện rất thường xuyên trong các công thức Excel là vì các mảng có thể thể hiện hoàn hảo các giá trị trong một loạt các ô .

Băng hình: Mảng là gì?

Các phép toán mảng trở nên quan trọng

Vì các công thức Dynamic Excel có thể dễ dàng làm việc với nhiều giá trị nên các phép toán mảng sẽ trở nên quan trọng hơn. Thuật ngữ 'hoạt động mảng' đề cập đến một biểu thức chạy kiểm tra logic hoặc hoạt động toán học trên một mảng. Ví dụ: biểu thức bên dưới kiểm tra xem các giá trị trong B5: B9 có bằng 'ca' hay không

 
=B5:B9='ca' // state = 'ca'

Kiểm tra ví dụ hoạt động mảng a

bởi vì có 5 ô trong B5: B9, kết quả là 5 giá trị TRUE / FALSE trong một mảng:

 
{FALSETRUEFALSETRUETRUE}

Hoạt động mảng bên dưới kiểm tra số tiền lớn hơn 100:

 
=C5:C9>100 // amounts > 100

Kiểm tra ví dụ hoạt động mảng b

Hoạt động mảng cuối cùng kết hợp kiểm tra A và kiểm tra B trong một biểu thức duy nhất:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

Kiểm tra ví dụ hoạt động mảng a và b

Lưu ý: Excel tự động ép các giá trị TRUE và FALSE thành 1 và 0 trong khi thực hiện phép toán.

Để đưa điều này trở lại công thức mảng động trong Excel, ví dụ dưới đây trình bày cách chúng ta có thể sử dụng chính xác cùng một hoạt động mảng bên trong hàm FILTER như bao gồm tranh luận:

Hoạt động mảng với chức năng FILTER

FILTER trả về hai bản ghi mà trạng thái = 'ca' và số tiền> 100.

Để có một cuộc biểu tình, hãy xem: Cách lọc với hai tiêu chí (băng hình).

Công thức mảng mới và cũ

Trong Dynamic Excel, không cần nhập công thức mảng với control + shift + enter. Khi một công thức được tạo, Excel sẽ kiểm tra xem công thức có thể trả về nhiều giá trị hay không. Nếu vậy, nó sẽ tự động được lưu dưới dạng công thức mảng động, nhưng bạn sẽ không thấy dấu ngoặc nhọn. Ví dụ dưới đây cho thấy một công thức mảng điển hình được nhập trong Dynamic Excel:

Công thức mảng cơ bản trong Excel truyền thống

Nếu bạn mở cùng một công thức trong Excel Truyền thống, bạn sẽ thấy các dấu ngoặc nhọn:

Công thức mảng cơ bản trong Excel động

Đi theo hướng khác, khi một công thức mảng 'truyền thống' được mở trong Dynamic Excel, bạn sẽ thấy dấu ngoặc nhọn trong thanh công thức. Ví dụ: màn hình bên dưới hiển thị một công thức mảng đơn giản trong Excel Truyền thống:

Công thức mảng đơn giản với dấu ngoặc nhọn hiển thị

Tuy nhiên, nếu bạn nhập lại công thức mà không có thay đổi, dấu ngoặc nhọn sẽ bị xóa và công thức trả về cùng một kết quả:

Công thức mảng đơn giản với dấu ngoặc nhọn không hiển thị

Điểm mấu chốt là các công thức mảng được nhập bằng control + shift + enter (CSE) vẫn hoạt động để duy trì khả năng tương thích, nhưng bạn không cần phải nhập công thức mảng bằng CSE trong Dynamic Excel.

Nhân vật

Với sự ra đời của mảng động, bạn sẽ thấy ký tự @ xuất hiện thường xuyên hơn trong các công thức. Ký tự @ cho phép một hành vi được gọi là ' giao lộ ngầm '. Giao điểm ngầm định là một quá trình logic trong đó nhiều giá trị được giảm xuống một giá trị.

Trong Excel truyền thống, giao lộ ngầm định là một hành vi im lặng được sử dụng (khi cần thiết) để giảm nhiều giá trị xuống một kết quả duy nhất trong một ô. Trong Excel động, nó thường không cần thiết, vì nhiều kết quả có thể tràn vào trang tính. Khi cần, giao lộ ngầm định được gọi theo cách thủ công với ký tự @.

Khi mở bảng tính được tạo phiên bản Excel cũ hơn, bạn có thể thấy ký tự @ được thêm tự động vào các công thức hiện có có tiềm năng để trả về nhiều giá trị. Trong Excel Truyền thống, một công thức trả về nhiều giá trị sẽ không tràn trên trang tính. Ký tự @ buộc hành vi tương tự này trong Excel Động để công thức hoạt động theo cùng một cách và trả về kết quả giống như trong phiên bản Excel gốc.

Nói cách khác, @ được thêm vào để ngăn một công thức cũ hơn tràn nhiều kết quả vào trang tính. Tùy thuộc vào công thức, bạn có thể xóa ký tự @ và hành vi của công thức sẽ không thay đổi.

Tóm lược

  • Mảng động sẽ giúp viết một số công thức dễ dàng hơn nhiều.
  • Giờ đây, bạn có thể lọc dữ liệu phù hợp, sắp xếp và trích xuất các giá trị duy nhất một cách dễ dàng bằng các công thức.
  • Các công thức Mảng động có thể được xâu chuỗi (lồng nhau) để thực hiện những việc như lọc và sắp xếp.
  • Các công thức trả về nhiều hơn một giá trị sẽ tự động tràn ra.
  • Không nhất thiết phải sử dụng Ctrl + Shift + Enter để nhập công thức mảng.
  • Công thức mảng động chỉ có sẵn trong Excel 365.
Tác giả Dave Bruns


^