Tính chất động của hàm mảng, công thức mảng trong Excel 365

[ptm0412]

Tính chất động của các công thức mảng trên Excel 365
Trước đây, các phiên bản cũ của Excel tuân thủ nguyên tắc 1 công thức cho 1 ô kết quả. Muốn có kết quả mảng phải tô khối ô và nhấn Ctrl Shift Enter.
Nay, Excel 365 đã thay đổi: 1 công thức duy nhất trên 1 ô có thể cho ra 1 mảng kết quả 1 cột, 1 dòng và thậm chí 1 ma trận kết quả nhiều dòng nhiều cột.
Khi tham số trong công thức thay đổi, mảng kết quả cũng thay đổi:

• Thay đổi giá trị
• Thay đổi kích thước

Do đó có thể nói công thức mảng trên Excel 365 mang tính chất động. Tính chất động thấy rõ ràng trong các hàm mảng mới của Excel 365. Không những thế những công thức chứa hàm thông thường của phiên bản cũ cũng có được tính chất động này.
Mảng động đơn giản nhất
Với công thức đơn giản tham chiếu đến 1 khối ô ta cũng có kết quả mảng
F2 =A2:A11



Hoặc F2=A2:C11



Hàm đơn giản
Hàm SumIf
Tham số thứ hai (điều kiện) nay có thể là 1 mảng và kết quả cũng trả về mảng mặc dù công thức chỉ gõ 1 ô.

G2 =SUMIF(B2:B11,F2:F4,C2:C11)



Hàm Vlookup
Tương tự như trên, tham số thứ nhất giá trị dò tìm cũng có thể là 1 mảng
G9=VLOOKUP(F9:F11,F2:H4,2,0)



Hoặc tham số thứ 3 (thứ tự cột để lấy kết quả) cũng có thể là mảng để lấy kết quả nhiều hơn 1 cột. Thí dụ sau lấy 2 cột 2 và 3
G9 =VLOOKUP(F9,F2:H4,{2,3},0)



Cũng lấy 2 cột như trên, đảo cột 3 ra trước cột 2:
G9 =VLOOKUP(F9,F2:H4,{3,2},0)



Lấy giá trị 2 cột như trên, nhưng đổi mảng ngang thành mảng dọc thì kết quả cũng thành 2 dòng:
G10 =VLOOKUP(F9,F2:H4,{2;3},0)





Lỗi #SPILL!
Lỗi này xảy ra khi có dữ liệu khác xen lẫn vào vùng kết quả. Trong hình dưới đây, đáng lẽ kết quả trùm từ F2 đến F11, nhưng bị dữ liệu khác xen vào F9 nên không trải (Spill) kết quả ra được.



Lỗi #SPILL! xuất hiện trong các trường hợp:

• Có dữ liệu khác chèn vào vùng kết quả (hình trên)
• Vùng kết quả ra khỏi giới hạn dòng/ cột của Excel
• Vùng kết quả không có kích thước xác định (lỗi tham số không đúng chuẩn)
• Công thức viết trong 1 table
• Vùng kết quả có ô bị merge
• Vùng kết quả quá lớn làm cho tràn bộ nhớ

Tham chiếu đến mảng động
Như đã thấy trong các thí dụ ở chủ đề giới thiệu các hàm mảng, tham chiếu đến 1 khối ô là kết quả mảng của công thức động, chỉ cần ghi địa chỉ ô đầu tiên và thêm dấu #.
Thí dụ nếu F2:F4 là kết quả của hàm Unique và là động:



Thì hàm SumIf có thể tham chiếu như sau:
G2 =SUMIF(B2:B11,F2#,C2:C11)



Vậy nếu như dữ liệu gốc thêm 1 dòng dữ liệu khiến cho thêm 1 khu vực 4, tất cả công thức tự nới kích thước ra:



Tương tự nếu tham chiếu validation là 1 mảng động và ứng dụng dấu #, thì cũng là 1 validation động tự điều chỉnh kích thước và giá trị. Hình dưới đây cho thấy validation của ô F9 tham chiếu đến F2#, và thay đổi giá trị cũng như kích thước từ 4 items thành 5 items khi F2 thay đổi.



Trường hợp không động
Trường hợp không muốn động (không spill kết quả)
Nếu không muốn trả về 1 mảng mà muốn lấy giá trị hàng ngang theo yêu cầu (và bị ứng xử như hàm mảng trước 365), thì gõ ký tự @ vào trước tham chiếu



Trường hợp không động do sử dụng Office trước 365

• File excel sử dịng hàm mảng hoặc công thức mảng khi gởi sang máy sử dụng phiên bản Office thấp hơn 365, công thức mảng sẽ bị tự động gắn cắp dấu {} bên ngoài công thức và trở thành hàm mảng phải nhấn Ctrl Shift Enter

• Các hàm không có trong các phiên bản trước ngoài cặp {} còn hiển thị _xlfn. hoặc _xlfn.+xlws. trước tên các hàm lạ.

Các thao tác với công thức mảng
Chỉnh sửa
Không chỉ được tham chiếu rút gọn, các công thức có kết quả mảng bao gồm hàm mảng mới và các hàm cũ được chỉnh sửa rất đơn giản:

• Chỉ cần chỉnh sửa công thức ô trên cùng bên trái
• Khi xoá, chỉ cần xoá ô trên cùng bên trái
• Khi copy, cũng chỉ cần copy 1 ô
• Khi chèn dòng, cột xuyên qua vùng kết quả, nếu là công thức mảng Ctrl Shift Enter ở phiên bản cũ, sẽ bị báo lỗi. Nhưng khi thao tác ở Excel 365 thì thực hiện bình thường: Excel tự điền lại kết quả bao gồm dòng và cột mới chèn

Không được thao tác

• Không xoá sửa các ô kết quả không chứa công thức
• Không tạo công thức mảng trong vùng có dữ liệu khác. Nên chừa khoảng trống bên dưới vùng kết quả dự phòng khi dữ liệu gốc tăng lên, kết quả có thể tràn xuống dữ liệu khác và bị lỗi #SPILL!

HẾT

 

[viettrithanyeu]

Mn cho em hỏi, em dùng hàm filter. Lúc đầu dùng thì ra nét bao mảng (hình 1), nhưng khi đóng file excel lại, mở ra thì trong công thức lại bị dấu { }, không hiện nét bao mảng nữa (hình 2). Có file còn bị ghim số luôn, không thể hiện được công thức. Mỗi lần mở file bị Protect view nữa. Vậy đây là lỗi gì ạ?
Xin lỗi mn, em đã tìm ra lỗi. Là do em lưu file ở đuôi xls (97-2003). Nếu lưu dạng xlsm thì không sao (trong file em có sử dụng vba). Nguyên nhân là hồi trước có lần em dùng 1 file sử dụng vba lưu file bị lỗi, chuyển sang đuôi xls(97-2003) thì không bị nên từ đó hay lưu xls.

 

[VetMini]

Thêm vài điều giải thích về mảng động của phiên bản 365:

Định nghĩa SPILL: Khi kết quả của công thức là một mảng thì Excel tự động "spill" (tràn) ra cho đủ mảng. Một vùng SPILL có các tính chất sau:
- Vùng này sẽ là một mảng, hình thức như ma trận 1 hoặc 2 chiều tùy theo kết quả công thức. Nơi ta gõ công thức là khởi điểm. Ô này trở thành phần tử 1, các phần tử khác trong dòng (các cột của mảng) sẽ "spill" ra bên phải, và các phần tử khác trong cột sẽ "spill" xuống dưới. Túm lại, phần tử chính sẽ là ô bên trái và trên cùng của mảng. Từ đây, tôi sẽ gọi nó là ô chủ.
- Vùng SPILL chính là cách hiển thị mới của việc bạn chọn một vùng và nhấn Ctrl+Shift+Enter trong các phiên bản trước. Chỉ là mảng trong các phiên bản trước không có ô chủ, cho nên chỉnh sửa xóa xiếc gì cũng phải chọn cả mảng.

Về dấu @ (Trường hợp không động trong bài #1):
Excel các đời trước có một tính năng gọi là "implicit intersection" (intersect là phép giao). Tính năng này có tác dụng tự động giao vùng tham chiếu với dòng hoặc cột theo vị trí của công thức. Trong ví dụ ở bài #1, công thức (tham chiếu cột) nằm ở dòng 4 tức dòng 3 của vùng nó tham chiếu (A2:A11). Excel tự động dùng luật implicit intersection để "giao" với dòng 3 và lấy được A4.
Điều lý thú: nếu bạn đặt công thức ở ô E12, bạn sẽ được #Value!. Bởi vì phép giao ngầm cho ra kết quả rỗng. Vùng tham chiếu của bạn chỉ tới dòng A11.
Đương nhiên, với công thức mảng thì Excel sử dụng cả vùng tham chiếu, và không cần phải dùng phép giao ngầm ở trên.
Phiên bản 365 mặc định dùng mảng động cho nên cũng "ém" đi phép giao ngầm này.
Để buộc 365 dùng phép giao ngầm, người ta dặt một dấu @ trước hàm. Hàm có dấu @ sẽ không có tính chất mảng động.
Chú thích:
Nếu code VBA của bạn có công thức mảng động, và viết cho file phiên bản đời trước thì VBA sẽ tự động chua thêm @ vào các hàm tham chiếu mảng. Muốn tránh điều này, bạn dùng Range.Formula2 thay vì Formula.

Về dấu # (Tham chiếu đến mảng động):
# là một toán tử thuộc về nhóm toán tử tham chiếu (+-*/ thuộc về nhóm toán tử tính trị scalar)
Nhóm này gồm:
: (hai chấm) = toán tử tham chiếu vùng range
, (dấu phẩy) = toán tử union (hội, dùng cho cả ranges lẫn trị)
(dấu cách) = toán tử intersection (giao 2 vùng ranges)
# (dấu băm) = toán tử tham chiếu cả vùng SPILL range
@ (dấu a móc) = toán tử buộc hàm sử dụng phép giao ngầm khi tham chiếu.

@Thớt:
Sau này viết bài giáo khoa thì nên đánh số các hình chụp. Điều này giúp người khác trích dẫn cho chính xác.

 

[HieuCD]

Thêm vài điều giải thích về mảng động của phiên bản 365:

Định nghĩa SPILL: Khi kết quả của công thức là một mảng thì Excel tự động "spill" (tràn) ra cho đủ mảng. Một vùng SPILL có các tính chất sau:
- Vùng này sẽ là một mảng, hình thức như ma trận 1 hoặc 2 chiều tùy theo kết quả công thức. Nơi ta gõ công thức là khởi điểm. Ô này trở thành phần tử 1, các phần tử khác trong dòng (các cột của mảng) sẽ "spill" ra bên phải, và các phần tử khác trong cột sẽ "spill" xuống dưới. Túm lại, phần tử chính sẽ là ô bên trái và trên cùng của mảng. Từ đây, tôi sẽ gọi nó là ô chủ.
- Vùng SPILL chính là cách hiển thị mới của việc bạn chọn một vùng và nhấn Ctrl+Shift+Enter trong các phiên bản trước. Chỉ là mảng trong các phiên bản trước không có ô chủ, cho nên chỉnh sửa xóa xiếc gì cũng phải chọn cả mảng.

Về dấu @ (Trường hợp không động trong bài #1):
Excel các đời trước có một tính năng gọi là "implicit intersection" (intersect là phép giao). Tính năng này có tác dụng tự động giao vùng tham chiếu với dòng hoặc cột theo vị trí của công thức. Trong ví dụ ở bài #1, công thức (tham chiếu cột) nằm ở dòng 4 tức dòng 3 của vùng nó tham chiếu (A2:A11). Excel tự động dùng luật implicit intersection để "giao" với dòng 3 và lấy được A4.
Điều lý thú: nếu bạn đặt công thức ở ô E12, bạn sẽ được #Value!. Bởi vì phép giao ngầm cho ra kết quả rỗng. Vùng tham chiếu của bạn chỉ tới dòng A11.
Đương nhiên, với công thức mảng thì Excel sử dụng cả vùng tham chiếu, và không cần phải dùng phép giao ngầm ở trên.
Phiên bản 365 mặc định dùng mảng động cho nên cũng "ém" đi phép giao ngầm này.
Để buộc 365 dùng phép giao ngầm, người ta dặt một dấu @ trước hàm. Hàm có dấu @ sẽ không có tính chất mảng động.
Chú thích:
Nếu code VBA của bạn có công thức mảng động, và viết cho file phiên bản đời trước thì VBA sẽ tự động chua thêm @ vào các hàm tham chiếu mảng. Muốn tránh điều này, bạn dùng Range.Formula2 thay vì Formula.

Về dấu # (Tham chiếu đến mảng động):
# là một toán tử thuộc về nhóm toán tử tham chiếu (+-*/ thuộc về nhóm toán tử tính trị scalar)
Nhóm này gồm:
: (hai chấm) = toán tử tham chiếu vùng range
, (dấu phẩy) = toán tử union (hội, dùng cho cả ranges lẫn trị)
(dấu cách) = toán tử intersection (giao 2 vùng ranges)
# (dấu băm) = toán tử tham chiếu cả vùng SPILL range
@ (dấu a móc) = toán tử buộc hàm sử dụng phép giao ngầm khi tham chiếu.

@Thớt:
Sau này viết bài giáo khoa thì nên đánh số các hình chụp. Điều này giúp người khác trích dẫn cho chính xác.

ChatGPT chào thua phiên bản cao cấp ChatVetMini