Thay thế một bảng tổng hợp bằng 3 công thức mảng động - Mẹo Excel

Mục lục

Xem video

Đã tám ngày kể từ khi công thức mảng động được công bố tại hội nghị Ignite 2018 ở Orlando. Đây là những gì tôi đã học được:

Mảng hiện đại đã được công bố tại Ignite vào ngày 24 tháng 9 năm 2018 và chính thức được gọi là Mảng động.
Tôi đã viết một cuốn sách điện tử dài 60 trang với 30 ví dụ về cách sử dụng chúng và tôi sẽ cung cấp miễn phí cho đến cuối năm 2018.
Quá trình triển khai sẽ chậm hơn rất nhiều so với mong muốn của bất kỳ ai, điều này thật khó chịu. Tại sao quá chậm? Nhóm Excel đã thực hiện các thay đổi đối với mã Calc Engine đã ổn định trong 30 năm. Đặc biệt quan tâm: với các phần bổ trợ đưa công thức vào Excel đã vô tình sử dụng phần giao nhau ngầm. Các phần bổ trợ đó sẽ bị hỏng nếu Excel bây giờ trả về phạm vi Tràn.
Có một cách mới để tham chiếu đến phạm vi được trả về bởi một mảng: =E3#nhưng nó chưa có tên. Các # được gọi là công thức đổ điều hành . Bạn nghĩ gì về một cái tên như Spill Ref (do Excel MVP Jon Acampora đề xuất ) hoặc The Spiller (do MVP Ingeborg Hawighorst đề xuất)?

Với tư cách là đồng tác giả của Pivot Table Data Crunching, tôi thích một bảng tổng hợp tốt. Nhưng điều gì sẽ xảy ra nếu bạn cần các bảng tổng hợp của mình cập nhật và bạn không thể tin tưởng người quản lý của người quản lý của mình nhấp vào Làm mới? Kỹ thuật được mô tả ngày hôm nay cung cấp một loạt ba công thức để thay thế một bảng tổng hợp.

Để có được danh sách các khách hàng duy nhất được sắp xếp, hãy sử dụng =SORT(UNIQUE(E2:E564))trong I2.

Một công thức mảng động để tạo khách hàng ở bên cạnh báo cáo

Để đặt sản phẩm trên cùng, hãy sử dụng =TRANSPOSE(SORT(UNIQUE(B2:B564)))trong J1.

Đối với vùng cột, hãy sử dụng TRANSPOSE

Đây là một vấn đề: bạn không biết danh sách khách hàng sẽ cao bao nhiêu. Bạn không biết danh sách sản phẩm sẽ rộng như thế nào. Nếu bạn tham chiếu đến I2 #, Spiller sẽ tự động tham chiếu đến kích thước hiện tại của mảng được trả về.

Công thức trả về vùng giá trị của bảng tổng hợp là một công thức mảng đơn trong J2 : =SUMIFS(G2:G564,E2:E564,I2#,B2:B564,J1#).

Trong tiếng Anh, điều này nói rằng bạn muốn thêm doanh thu từ G2: G564 trong đó Khách hàng trong E khớp với khách hàng của hàng hiện tại từ công thức mảng I2 và các sản phẩm trong B khớp với cột hiện tại của công thức mảng trong J1.

Đây là một công thức ngọt ngào

Điều gì sẽ xảy ra nếu dữ liệu cơ bản thay đổi? Tôi đã thêm một khách hàng mới và một sản phẩm mới bằng cách thay đổi hai ô này trong nguồn.

Thay đổi một số ô trong dữ liệu gốc

Báo cáo cập nhật với các hàng mới và cột mới. Tham chiếu phạm vi mảng của I2 # và J1 # xử lý hàng và cột bổ sung.

Báo cáo tab chéo của bạn tự động mở rộng với dữ liệu mới

Tại sao SUMIFS hoạt động? Đây là một khái niệm trong Excel được gọi là Broadcasting. Nếu bạn có một công thức đề cập đến hai mảng:

Mảng một là (27 hàng) x (1 cột)
Mảng hai là (1 hàng) x (3 cột)
Excel sẽ trả về một mảng kết quả có chiều cao và chiều rộng bằng phần cao nhất và rộng nhất của các mảng được tham chiếu:
Kết quả sẽ là (27 hàng) x (3 cột).
Đây được gọi là mảng phát sóng.