Khác biệt lệnh =A1:B5 giữa Microsoft Excel 365 và Excel 2016

[SVBK2000]

Tại sao khi em sử dụng lệnh =A1:B5 bảng Microsoft Excel thì nó tạo dự liệu cho em luôn mà khi sử dụng trong Excel 2016 thì lại bị lỗi như hình dưới?

Mong mọi người trong diễn đàn giúp em với ạ.

 

[VetMini]

Bị hổng bài trích dẫn rồi. Xin phép xóa và xin lỗi mọi người.

Chú thích: tôi không đối thoại với "e" cho nên tôi xin phép không đưa lên giải thích của mình.
Bạn nào muốn thử nghe giải thích này thì mở thớt mà hỏi.
.

 

[ptm0412]

Tại Excel 365 hỗ trợ kết quả mảng, 2016 thì không.

 

[SVBK2000]

Tại Excel 365 hỗ trợ kết quả mảng, 2016 thì không.

Em cảm ơn đã giải đáp thắc mắc ạ

Bài đã được tự động gộp: 29/8/22

Tại Excel 365 hỗ trợ kết quả mảng, 2016 thì không.

Anh có biết công thức nào có thể thay thế mà kết quả vẫn tương tự như viết trên Excel 365 hoặc cách khắc phục Excel 2016 không ạ?
Em cảm ơn ạ.

 

[ptm0412]

Anh có biết công thức nào có thể thay thế mà kết quả vẫn tương tự như viết trên Excel 365 hoặc cách khắc phục Excel 2016 không ạ?
Em cảm ơn ạ.

Công thứ thì cũng vậy nhưng tô C1: D5 rồi gõ công thức và ctrl shift enter. Nói vậy thôi chứ copy paste cho nhanh. Trừ khi nói rõ mục đích.

 

[SVBK2000]

Công thứ thì cũng vậy nhưng tô C1: D5 rồi gõ công thức và ctrl shift enter. Nói vậy thôi chứ copy paste cho nhanh. Trừ khi nói rõ mục đích.

Em cảm ơn ạ.
Em muốn lúc thêm dòng hoặc cột vào mảng gốc thì mảng vị trí mình chọn như C1: D5 tự động thêm dòng hoặc cột.
Còn khi sử dụng Excel 2016, lúc thêm dòng hoặc cột thì dữ liệu chỉ lấy trong vùng C1: D5, còn ngoài vùng đấy dữ liệu sẽ bị mất.

Bài đã được tự động gộp: 29/8/22

View attachment 280466

Bị hổng bài trích dẫn rồi. Xin phép xóa và xin lỗi mọi người.

Chú thích: tôi không đối thoại với "e" cho nên tôi xin phép không đưa lên giải thích của mình.
Bạn nào muốn thử nghe giải thích này thì mở thớt mà hỏi.
.

Lần đầu em tham gia diễn đàn, có gì còn thiếu sót hoặc hiểu lầm. Mong anh và mọi người thông cảm và bỏ qua cho em. Em sẽ cố gắng sửa đổi và học tập.

 

[ptm0412]

Em cảm ơn ạ.
Em muốn lúc thêm dòng hoặc cột vào mảng gốc thì mảng vị trí mình chọn như C1: D5 tự động thêm dòng hoặc cột.
Còn khi sử dụng Excel 2016, lúc thêm dòng hoặc cột thì dữ liệu chỉ lấy trong vùng C1: D5, còn ngoài vùng đấy dữ liệu sẽ bị mất.

Kể cả 365, viết công thức đấy cũng không tự nới rộng.

Bị hổng bài trích dẫn rồi. Xin phép xóa và xin lỗi mọi người.

Tôi đang định hỏi. Vả lại hình và bài của anh (Đã sửa xóa) không liên quan đến bài trích?

 

[befaint]

Tại Excel 365 hỗ trợ kết quả mảng, 2016 thì không.

Chữ 365 ở đâu ra thế anh?
Vụ này hay ghê.

 

[ptm0412]

Chữ 365 ở đâu ra thế anh?
Vụ này hay ghê.

Ở đâu đó .... Cũng tại nhanh nhẩu quá ...

 

[HeSanbi]

@SVBK2000
Nếu bạn dùng hàm tự tạo thì có thể vượt qua một phần mà chức năng không có ở các phiên bản Excel cũ hơn

 

[VetMini]

...Lần đầu em tham gia diễn đàn, có gì còn thiếu sót hoặc hiểu lầm. Mong anh và mọi người thông cảm và bỏ qua cho em. Em sẽ cố gắng sửa đổi và học tập.

Cảm ơn.
Có những lý do khiến yêu cầu của bạn rất khó thực hiện. Những lý do ấy không phải do phần mềm thiếu sót. Mà là lý do thực tiễn, dân chuyên test phần mềm đều biết (xem thêm giải thích bên dưới)

...
Tôi đang định hỏi. Vả lại hình và bài của anh (Đã sửa xóa) không liên quan đến bài trích?

Trong hình đã xóa ở bài trên, tôi nhắc lại do người ấy một bài tôi đã từng giải thích gần đây, và có chút xíu liên quan. Chút xíu liên quan, nhưng nếu hiểu rõ thì sẽ dễ hiểu nổi khó khăn của công thức mảng.
Hai cái hình trong bài ấy, cho thấy cùng một bảng, cùng một công thức nhưng một cái ra kết quả và cái kia ra error. Đó là hiệu ứng của "implicit intersection" (tôi có dặn đi tìm học theo từ khóa này)

Trở lại, về mảng.
Trong Excel, chỉ có 4 hàm thực sự là hàm mảng: Index, Sumproduct, Lookup, và Aggregate. Frequency và MMult cũng là hàm mảng nhưng chúng hoạt động hơi khác.

Luật của Excel là "hàm ghi tại một ô không thể chỉnh sửa ô khác. Muốn ghi qua ô khác thì phải chọn cả ô ấy"
Luật này dùng để bảo vệ dữ liệu sẵn có của bảng tính. Không bảo vệ sẽ gây đến việc mất dữ liệu bất ngờ.
Lưu ý: tôi nói "mất" có nghĩa là dữ liệu đáng lẽ độc lập với hàm trên thì bị ghi đè. Các ô tham chiếu là chuyện khác, chúng chỉ tự động chỉnh theo chứ không hề bị mất.

Biểu thức trong đề bài =A1:B5 vốn là biểu thức mảng. Nếu chỉ =A1:A1 (1 cột) thì nó có thể theo luật implicit intersection để chọn đúng dòng. Nhưng vì A1:B5 là nhiều hơn 1 cột cho nên implicit intersection không có cách nào để thỏa cả dòng và cột. Kết quả phải là error.
Muốn sử dụng thì phải nhập theo kiểu mảng (CTtrl_Shift+Enter). Kết quả sẽ là mảng { 1,6; 2,7; 3,8; 4,9; 5,10 }. Nhưng vì nó không phải là mảng động cho nên nó chỉ trả về kết quả trong các ô được chọn. Xem hình bên dưới, tôi chọn 3 ô thì nó trả về cho 3 ô (nếu tôi chỉ chọn 1 ô C1 thì kết quả sẽ là 1, tức A1). Lưu ý là luật bảo vệ vẫn ddowcj tôn trọng, lúc chọn ô bạn đã biết mình sẽ ghi đè những ô nào.



Phiên bản 365 và 2021 đưa ra một loại mảng mới là mảng động. Loại mảng này có tính chất "rải/spill" tức là nó sẽ có một cái mốc (ô mà ta ghi thức) và mảng kết quả tự rải vào các ô còn lại.
Lưu ý là luật bảo vệ sự đồng nhất của bảng tính vẫn được tôn trọng. Nếu rải nhằm ô đã có dữ liệu thì nó sẽ báo động.
Loại mảng động này khá phức tạp. Đại khái là nhóm mảng thực ra chỉ có một ô mốc (anchor) và chỗ còn lại chỉ đặt chỗ ô thôi (nghĩ đến khách sạn: bạn có thể đặt/chiếm phòng những không ở)
Vì sự phức tạp của nó cho nên từ MS không muốn viết Cải Chỉnh/Service Pack phiên bản 2019 trở xuống. Và việc viết VBA để bắt chước/emulate khá khó khăn.

Hàm UDF tự căn bản được MS giới hạn buộc phải tuân theo luật bảo vệ dữ liệu. Nói cách khác, cũng như hàm trên bảng tính, UDF không thể thay đổi dữ liệu ngoài những ô nó chọn.

Có một vài cách đi vòng qua luật này. Nhưng chúng rất phức tạp. Tôi không muốn đi xa hơn nữa ở đây.
Lời thật mất lòng. Theo tôi thì với trình độ của bạn cũng không cần tìm hiểu kỹ thuật này làm gì, chỉ tổ vác một cái code mà bạn không thể làm chủ nó thôi. Nếu bạn đủ sức học kỹ thuật đi vòng giới hạn của MS thì bạn đã không mở câu hỏi chủ đề này.
.

 

[ptm0412]

Hai cái hình trong bài ấy, cho thấy cùng một bảng, cùng một công thức nhưng một cái ra kết quả và cái kia ra error. Đó là hiệu ứng của "implicit intersection" (tôi có dặn đi tìm học theo từ khóa này)

Hai cái hình trong bài của anh, 1 hình không lỗi và 1 hình lỗi, nhưng hình không lỗi không thấy dấu hiệu công thức mảng mặc dù chỉ là 1 ô (dấu hiệu công thức mảng là cặp {...})

 

[VetMini]

Hai cái hình trong bài của anh, 1 hình không lỗi và 1 hình lỗi, nhưng hình không lỗi không thấy dấu hiệu công thức mảng mặc dù chỉ là 1 ô (dấu hiệu công thức mảng là cặp {...})

Nếu là công thức mảng thì nó đã không lỗi. Xem lại phần bổ túc của tôi ở cái paragraph ngay trên hình bài #11.
CSE là một hình thức vô hiệu hóa implicit intersection.

 

[ptm0412]

Nếu là công thức mảng thì nó đã không lỗi. Xem lại phần bổ túc của tôi ở cái paragraph ngay trên hình bài #11.
CSE là một hình thức vô hiệu hóa implicit intersection.

Tôi ghi nhớ cái hình mà anh đã xóa. Chỉ 1 ô. Công thức = A1:A5. Không thấy {}. Cũng không lỗi. Hình 2 công thức y chang nhưng lỗi. Thế nên mới hỏi ạ.

 

[VetMini]

Tôi ghi nhớ cái hình mà anh đã xóa. Chỉ 1 ô. Công thức = A1:A5. Không thấy {}. Cũng không lỗi. Hình 2 công thức y chang nhưng lỗi. Thế nên mới hỏi ạ.

À. Đó là hiệu ững của Implicit inersection. Hiệu ứng này, theo đúng tên gọi của nó. Công thức nhận mảng vào chỗ tham mà đối với nó đáng lẽ phải là trị đơn/scalar sẽ chọn dòng hoặc cột tương ứng với vị trí của của chính công thức. Implicit intersection sẽ không xảy ra cho một trong hai trường hợp sau:
1. công thức vốn là công thức mảng.
2. công thức được nhập bằng CSE

Công thức =A1:A5 được bảng tính quay trở lại cái range mà nó tham chiếu, và dùng dòng của công thức để chọn dòng. Công thức ghi ở C1, tức là tương ứng với A1, và nó chọn A1. Nếu công thức ghi ở C2 thì nó sẽ chọn A2.

Trong hình thứ 2 của bài bị xóa, công thức ghi ở C6, tuột ra khỏi A1:A5 cho nên không implicit intersection được. Và kết quả là error.
Tuy nhiên nếu công thức là CSE thì không còn implicit intersection, và kết quả sẽ là 1 (ô thứ nhất của mảng kết quả)

Tôi bổ sung thêm một chút về hiệu ứng này để dễ hiểu hơn:
Trong hình dưới đây, công thức không phải CSE (không có {}) nhưng vẫn hoạt động đúng là nhờ hiệu ứng này. Nó tự chọn đúng dòng để tính toán.
(đương nhiên, trong trường hợp này nhiều người sẽ dùng C6 = A6+B6, công thức này sẽ tự động chuyển số dòng khi fill vào các ô khác. Điểm khác nhau là công thức bình thường sẽ ra trống nếu kéo quá số ô, công thức của tôi sẽ ra error. Tùy nhu cầu mà quý vị chọn kiểu nào)



Chú thích: dân GPE khoái bẫy lỗi dữ liệu thay vì tìm hiểu chỗ sai của dữ liệu cho nên hiệu ứng implicit intersection rất ít kkhi trải qua.
.

 

[ptm0412]

hiệu ứng implicit intersection rất ít khi trải qua.
.

Tôi có thấy hiệu ứng này khi bảng dữ liệu định dạng table, hoặc xưa hơn là khi tạo name tự động (vừa name dòng vừa name cột, hoặc chỉ dòng, hoặc chỉ cột). Còn 1 bảng khơi khơi thế kia thì chưa gặp bao giờ thật. Vả lại cũng không dùng đến bao giờ.