Excel tắt mảng động

Tại The Marquee Group, chúng tôi tự hào là những người dùng Microsoft Excel “kiểu cũ”, bắt đầu từ các phiên bản của phần mềm phổ biến có từ đầu những năm 1990 (mặc dù những người dùng cụ thể đó có thể không thừa nhận điều đó). Các thay đổi đối với phần mềm của Excel có xu hướng chậm, tăng dần và gần như luôn tương thích ngược (một trong những yêu cầu để duy trì phần mềm đang được hàng trăm triệu người trên toàn thế giới sử dụng). Tuy nhiên, các phiên bản Excel mới nhất (trong “Kênh hiện tại” của Microsoft 365 kể từ tháng 1 năm 2020) đã giới thiệu một tính năng mới giúp thay đổi cách cơ bản có thể sử dụng bảng tính trong tương lai.   

Trước đây, bạn sẽ nhập một công thức vào một ô và nó sẽ trả về một kết quả duy nhất. Nếu bạn muốn có kết quả bổ sung, bạn cần sao chép công thức xuống. Ví dụ

Mảng động thêm một cách hoàn toàn mới để sử dụng bảng tính. Bây giờ có thể nhập một công thức duy nhất sẽ trả về một loạt kết quả trên một số ô (như hình bên dưới)

Những người đam mê sử dụng Excel sẽ nhận thấy rằng điều này tương tự như hoạt động của Mảng trước đây trong Excel (i. e. công thức bạn sẽ nhập trong một dải ô bằng cách nhập CTRL + SHIFT + ENTER). Mảng động đơn giản hóa đáng kể và mở rộng việc sử dụng hành vi này và tránh được nhiều thiếu sót của tính năng Mảng cổ điển trong Excel

Chìa khóa để hiểu tính năng mới này là nhận ra rằng bạn chỉ nhập công thức vào một ô (trong ví dụ trên, trong ô E4). Kết quả của công thức “tràn” ra ngoài ô để tạo Mảng động. Các ô bên dưới E4 (ví dụ: ô E5), chứa thông tin nhưng không có công thức thực. Nếu bạn chọn ô, bạn sẽ thấy văn bản chuyển sang màu xám trong thanh công thức cho thấy rằng ô chứa kết quả "bị đổ" từ một công thức ở một vị trí khác

Lúc đầu, các công thức này trông giống như một giải pháp thay thế cho việc sao chép trên một phạm vi. Nhưng Microsoft cũng đã giới thiệu một số chức năng mới tận dụng hành vi này (và có một số chức năng cũ hơn cũng có thể tận dụng chức năng này). Chìa khóa để sử dụng các tính năng mới này là hiểu rằng kích thước của Mảng động kết quả có thể tự động thay đổi tùy thuộc vào các giá trị mà bạn cung cấp cho công thức

Các hàm Excel mới sử dụng mảng động

  1. ĐỘC NHẤT. Trả về một Mảng động với một danh sách các giá trị duy nhất trong một danh sách hoặc dải ô (một nhiệm vụ phổ biến khó hơn nhiều với các hàm Excel “tiêu chuẩn”).  
  2. LỌC. Tạo một Mảng động được lọc dựa trên các tiêu chí có thể thay đổi
  3. LOẠI. Sắp xếp nội dung của một phạm vi với khả năng chỉ định cột nào sẽ sắp xếp theo thứ tự tăng dần hoặc giảm dần
  4. SẮP XẾP. Sắp xếp nội dung của một phạm vi với khả năng sắp xếp dựa trên nhiều cột theo thứ tự tăng dần hoặc giảm dần
  5. SỰ LIÊN TIẾP. Tạo một Mảng động với một danh sách các số tuần tự với khả năng đặt giá trị bắt đầu và “bước” cho mỗi chuỗi
  6. RANDARRAY. Tạo Mảng động có danh sách các số ngẫu nhiên (số nguyên hoặc số thập phân) 

Mảng động với hàm OFFSET

Một trong những ứng dụng có giá trị nhất của Mảng động là tạo danh sách cập nhật tự động cùng với hàm OFFSET trong Excel. Hàm offset của Excel sẽ bắt đầu từ một ô tham chiếu, di chuyển một số hàng hoặc cột nhất định theo bất kỳ hướng nào và trả về giá trị trong ô đó.   

OFFSET cũng có hai đối số bổ sung cho phép bạn trả về một mảng giá trị có chiều rộng hoặc chiều cao nhất định. Trước Mảng động, các hàm OFFSET này sẽ được sử dụng với Phạm vi được đặt tên động (một chủ đề cho một ngày khác và được đề cập trong Marquee's Excel 2. khóa học Phân tích dữ liệu nâng cao) hoặc với hành vi Mảng trước đó của Excel (CTRL + SHIFT + ENTER). Với Mảng động, phần lớn sức mạnh của OFFSET đã được mở khóa với khả năng hiển thị danh sách cực kỳ tùy chỉnh từ bộ dữ liệu lớn. Trong ví dụ bên dưới, chúng tôi sử dụng công thức OFFSET cho phép chúng tôi hiển thị các giá trị X đầu tiên trong danh sách (7 trong ví dụ của chúng tôi). Điều này có thể được thay đổi nhanh chóng và danh sách sẽ tự động cập nhật

NHƯNG CHỜ. Tôi thấy ổn với các hàm mới, nhưng tôi muốn hàm OFFSET của mình trong Excel hoạt động như hồi năm 2003. Đừng lo lắng, hãy xem chú thích ở cuối bài viết này để biết cách thực hiện

Cạm bẫy mảng động tiềm ẩn

Mảng động rất hữu ích và tạo ra những cách mới và mạnh mẽ để sử dụng Microsoft Excel. Với bất kỳ công cụ mới nào, điều quan trọng là phải hiểu bất kỳ giới hạn nào trước khi đưa chức năng này vào "tự nhiên" trong bảng tính của bạn

  1. SPILL lỗi. Thay đổi đối với hành vi trong đó một công thức duy nhất có thể trả về kết quả trên một dải ô gây ra một số vấn đề mới. Bạn có thể thấy rằng Excel không thể trả về phạm vi thông tin mà bạn đã yêu cầu. Điều này sẽ tạo ra #SPILL. lỗi. Những lý do khiến bạn có thể gặp lỗi tràn bao gồm.
    –  Phạm vi tràn đã có thông tin khác trong đó
    –  Excel không thể xác định kích thước của mảng bị tràn vì nó không ổn định (i. e. sử dụng một số ngẫu nhiên để xác định kích thước của Mảng động
    –  Kết quả vượt ra ngoài ranh giới của sổ làm việc
    –  Công thức nằm trong Bảng (Mảng động
    –  The result is too large and Excel has run out of memory
    –  The result is spilling into a merged cell (another reason to avoid the use of merged cells in spreadsheets)
  2. tương thích ngược. Mảng động chỉ sẵn dùng trên các phiên bản Microsoft 365 của Excel (Kênh hiện tại) và không sẵn dùng trên Excel 2019/2016 (hoặc các phiên bản cũ hơn). Nếu bạn lưu một trang tính bằng Mảng động và ai đó cố gắng sử dụng trang tính trên phiên bản Excel cũ hơn, Mảng động sẽ được chuyển đổi thành Mảng truyền thống. Điều này có thể gây mất chức năng hoặc hành vi không mong muốn
  3. Tạo sự phức tạp không cần thiết. Marquee Group luôn quan niệm rằng bạn nên sử dụng những tính năng đơn giản nhất, dễ hiểu nhất trong Excel để thực hiện được điều mình mong muốn. Mặc dù Mảng động bổ sung những cách mới thú vị để phân tích dữ liệu, nhưng nên suy nghĩ xem liệu Mảng động có phải là cách tiếp cận đơn giản nhất có thể hay không

Mảng động là một tính năng mới thú vị trong Excel;

Nhóm Marquee đang làm việc trên các phiên hội thảo trên web ngắn hơn sẽ hướng dẫn người tham gia về các trường hợp sử dụng thú vị cho chức năng Mảng động mới của Excel. Theo dõi các phiên bản Benchmarq trong tương lai hoặc theo dõi lịch khóa học của chúng tôi nếu bạn muốn tìm hiểu thêm. Nếu bạn có câu hỏi về Mảng động hoặc cách triển khai chúng trong bảng tính của mình, vui lòng liên hệ với chúng tôi theo địa chỉ info@marqueegroup. ca

——

KẾT THÚC. Những điều mới làm tôi sợ

Đôi khi chúng ta chỉ muốn mọi thứ hoạt động như trong Excel 2003. Khi phím tắt dán đặc biệt là Alt -> E -> S -> F, khi 50 Cent đứng đầu bảng xếp hạng và khi cổ áo popped cuối cùng cũng trở lại.   

Chúng ta có thể buộc Excel ngừng tràn và trả về một giá trị duy nhất từ ​​mảng kết quả bằng cách sử dụng ký hiệu @ sau dấu bằng (i. e. =@OFFSET(B3,0,0,H2)). Đây được gọi là toán tử giao điểm và sẽ trả về một kết quả duy nhất từ ​​mảng nằm trên cùng một hàng (hoặc cột) mà công thức được nhập vào.   

Toán tử giao nhau (và anh em họ của nó, toán tử phạm vi tràn (#)) cho phép một số hành vi thú vị khác với Mảng động, nhưng bài viết này cũng đủ dài rồi;

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

Các công thức Excel trả về một tập hợp các giá trị, còn được gọi là một mảng, trả về các giá trị này cho các ô lân cận. Hành vi này được gọi là đổ. Công thức có thể trả về mảng có kích thước thay đổi được gọi là công thức mảng động.

Tại sao công thức của tôi tràn ra trong Excel?

Với sự ra mắt của mảng động trong Excel 365, bất kỳ công thức nào tạo ra nhiều kết quả phép tính sẽ tự động "tràn" các kết quả đó vào các ô lân cận

Excel 2016 có mảng động không?

Khả năng tương thích ngược. Mảng động chỉ khả dụng trên các phiên bản Microsoft 365 của Excel (Kênh hiện tại) và không khả dụng trên Excel 2019/2016 (hoặc các phiên bản cũ hơn).

Xlookup có phải là một mảng động không?

XLOOKUP() là một trong một số hàm mảng động mới . Nếu bạn đã từng nhập một biểu thức bằng Ctrl + Shift + Enter, thì bạn đã quen với cách Excel sử dụng để làm việc với mảng động.