Giao lộ ngầm định trong Excel và toán tử @

Giao lộ ngầm đã xuất hiện trong Excel trong nhiều năm, nhưng rất ít người quan tâm đến nó. Bây giờ nó không còn là một hành vi mặc định trong Excel 365, vì vậy rất nhiều câu hỏi phát sinh. Hướng dẫn này nhằm mục đích cung cấp câu trả lời.

Bạn có cảm giác bạn biết mọi thứ về sổ làm việc của mình, và sau đó ký tự @ xuất hiện từ hư không trong đầu công thức của bạn. Điều đó có nghĩa là gì? Và nó hoạt động chính xác như thế nào? Tóm lại, nó là một toán tử giao nhau ngầm vô hiệu hóa hành vi mảng mặc định mới của một công thức và yêu cầu Excel trả về một giá trị duy nhất. Để biết thêm chi tiết, vui lòng tiếp tục đọc.

Giao lộ ngầm định trong Excel

Giao lộ ngầm trong Excel có nghĩa là giảm nhiều giá trị xuống một giá trị duy nhất. Thông thường, nó xảy ra khi một mảng hoặc dải ô được cung cấp cho một công thức được cho là chỉ xuất một giá trị trong một ô duy nhất.

Giao điểm ngầm trong Excel 2019-2000

Trong Excel truyền thống, giao lộ ngầm định là hành vi mặc định. Nó được thực hiện âm thầm trong nền cho tất cả các công thức. Logic giao nhau ngầm như sau:

  • Nếu công thức tạo ra một giá trị duy nhất, trả về giá trị đó (trên thực tế, giao lộ ngầm định không làm gì trong trường hợp này).
  • Trong trường hợp của một phạm vi, sử dụng một giá trị từ ô trên cùng một hàng hoặc cột với công thức.
  • Trong trường hợp của một mảng, sử dụng giá trị trên cùng bên trái.

Ví dụ: khi nhân hai cột số, Excel chỉ chọn một số từ mỗi cột trong cùng một hàng có công thức và chỉ xuất kết quả trong một ô (trong trường hợp của chúng tôi là D2):

=B2:B5*C2:C5

Để nhân các số trong các ô khác, bạn cần sao chép công thức xuống.

Giao lộ ngầm định mặc định trong Excel tiền động

Đến vô hiệu hóa giao lộ ngầm, bạn phải nhập một công thức mảng với Ctrl + Shift + Enter (đó là lý do tại sao các công thức mảng truyền thống đôi khi được gọi là Công thức CSE). Điều này rõ ràng cho Excel biết để xử lý nhiều giá trị đầu vào dưới dạng phạm vi hoặc mảng.

Trong trường hợp của chúng tôi, hãy chọn các ô D2: D5, nhập công thức ở trên và xác nhận nó bằng cách nhấn Ctrl + Shift + Enter các phím với nhau. Khi bạn làm điều này, công thức được bao quanh trong {dấu ngoặc nhọn}, cho biết đó là một công thức mảng. Kết quả là, các số trong mỗi hàng được nhân cùng một lúc:

Công thức mảng truyền thống (công thức CSE)

Giao điểm ngầm trong Excel 365

Sự ra đời của mảng động đã thay đổi hành vi mặc định của tất cả các công thức trong Excel 365. Giờ đây, bất kỳ công thức nào có khả năng tạo ra nhiều kết quả, đều tự động tràn ra chúng trên trang tính. Điều đó làm cho giao lộ ngầm không cần thiết, và nó là không còn được kích hoạt theo mặc định. Vì lý do này, Excel 365 đôi khi được gọi là mảng động Excel hoặc là DA Excel.

Ở đây, toàn bộ phạm vi được nhân với một công thức bình thường chỉ được nhập vào ô trên cùng (D2):

=B2:B5*C2:C5

Kết quả là một phạm vi tràn gồm 4 ô:

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

Nếu bạn muốn một công thức chỉ trả về một giá trị, bạn cần phải cho phép đánh chặn ngầm. Đối với điều này, họ đã giới thiệu một nhà điều hành đặc biệt và trong phần tiếp theo, bạn sẽ tìm thấy chi tiết đầy đủ về nó.

Toán tử giao nhau ngầm trong Excel – ký hiệu @

Các toán tử giao lộ ngầm đã được giới thiệu trong Excel 365 để ngăn chặn hành vi mảng động mặc định. Nếu bạn muốn một công thức chỉ trả về một giá trị, hãy đặt @ trước tên của hàm (hoặc trước một phạm vi hoặc mảng nhất định bên trong công thức) và nó sẽ hoạt động giống như một công thức không phải mảng thông thường trong các phiên bản trước động.

Ví dụ:

[email protected]:B5*@C2:C5

Toán tử giao nhau ngầm trong Excel 365

Ghi chú. Toán tử giao lộ ngầm chỉ được hỗ trợ trong các đăng ký Microsoft 365. Nếu bạn cố gắng thêm dấu @ trong các phiên bản cũ hơn, nó sẽ bị xóa một cách âm thầm khi hoàn thành công thức.

Tại sao @ được thêm vào công thức cũ?

Khi mở sổ làm việc được tạo trong phiên bản cũ hơn, bạn có thể nhận thấy ký tự @ được thêm vào một số công thức của mình. Đối với hầu hết các phần, nó được thực hiện để buộc một công thức hoạt động giống như cách nó đã làm trong phiên bản gốc mà nó được tạo ra. Nói cách khác, nếu một công thức trả về một giá trị duy nhất trong phiên bản cũ hơn nhưng sẽ trả về nhiều kết quả trong Excel 365, thì công thức đó sẽ được tự động bắt đầu bằng @ để hủy kích hoạt hoạt động của mảng.

Thông thường, toán tử giao nhau được chèn vào trước hàm có thể trả về mảng hoặc dải ô nhiều ô như OFFSET, INDEX và các hàm do người dùng xác định.

Ví dụ: công thức sau được tạo trong Excel trước động:

=INDEX(B2:C5,,F1)

sẽ có dạng sau trong mảng động Excel:

[email protected](B2:C5,,F1)

Lý do là không có toán tử @, công thức sẽ trả về tất cả các giá trị từ C2: C5 vì row_num đối số của MỤC LỤC chức năng bị bỏ qua. Để đảm bảo hành vi nhất quán trong tất cả các phiên bản, sự giao nhau ngầm không được chú ý trước đây trở nên rõ ràng. Vui lòng so sánh kết quả:

Toán tử giao nhau ngầm được thêm vào các công thức cũ.

Trong trường hợp một hàm có đầu ra nhiều ô tiềm năng được lồng trong một hàm khác có thể xử lý các mảng và xuất ra một kết quả duy nhất (chẳng hạn như SUM, COUNT, AVERAGE, v.v.), thì không có lý do gì để kích hoạt giao cắt ngầm và công thức được chuyển sang Excel động như nguyên trạng mà không cần thêm dấu @. Ví dụ:

=AVERAGE(INDEX(B2:C5,,F1))

Công thức được chuyển sang Excel động mà không cần thêm dấu @.

Tôi có thể xóa ký hiệu @ khỏi công thức của mình không?

Chắc chắn, bạn có thể. Excel thực hiện chuyển đổi công thức chỉ một lần. Nếu toán tử @ phá vỡ hoặc thay đổi tiêu cực hoạt động của các công thức của bạn, bạn có thể xóa @ theo cách thủ công và nó sẽ không xuất hiện lại sau khi lưu sổ làm việc.

Hậu quả của việc xóa toán tử giao lộ ngầm là gì? Tùy thuộc vào phần của công thức sau dấu @ trả về, có ba kết quả có thể xảy ra:

  • Nếu một giá trị duy nhất được trả lại, sẽ không có thay đổi.
  • Nếu một mảng được trả về, nó sẽ tràn vào các ô bên cạnh.
  • Nếu một mảng được trả về nhưng có không đủ ô trống để hiển thị tất cả các giá trị, lỗi #SPILL sẽ xảy ra.

Tại sao sử dụng toán tử @ trong Excel 365?

Như đã đề cập, Excel for Microsoft 365 xử lý tất cả các công thức dưới dạng công thức mảng theo mặc định mà bạn không cần phải nhấn Ctrl + Shift + Enter như bạn đã từng làm trong các phiên bản trước. Nếu bạn muốn vô hiệu hóa hành vi của mảng, hãy chèn toán tử giao nhau ngầm.

Ví dụ 1. Thoát khỏi lỗi #SPILL! lỗi

Một tình huống rất phổ biến là ngăn chặn hoặc sửa chữa #SPILL lỗi. Nếu bạn thấy thuận tiện khi tham chiếu đến toàn bộ các cột (thường không phải là một ý tưởng hay vì nó làm chậm Excel), nhưng tuy nhiên, công thức như vậy sẽ hoạt động tốt trong các phiên bản trước động:

=VLOOKUP(A:A, D:E, 2, FALSE)

Trong Excel động, nó sẽ dẫn đến lỗi #SPILL vì không có đủ dung lượng để hiển thị gần 1,05 triệu kết quả.

Thêm @ trước đối số lookup_value sẽ giải quyết được sự cố:

=VLOOKUP(@A:A, D:E, 2, FALSE)

Sử dụng giao lộ ngầm định để ngăn lỗi #SPILL

Khi Excel dự đoán rằng công thức có thể tràn ra ngoài các cạnh của trang tính, nó sẽ đề xuất sửa và bạn nên chấp nhận nó:

Lời nhắc giao nhau ngầm trong Excel

Ví dụ 2. Nhận công thức hoạt động bình thường trong Excel cũ hơn

Nếu bạn đang sử dụng đăng ký Microsoft 365 và chia sẻ tệp của mình với người nào đó sử dụng phiên bản cũ hơn, điều quan trọng là phải đảm bảo hành vi công thức nhất quán cho mọi người.

Giả sử bạn đã viết công thức mảng động này trong Excel 365:

=B2:B5*C2:C5

Trong các phiên bản cũ hơn, nó sẽ được chuyển đổi thành công thức mảng CSE kế thừa:

{=B2:B5*C2:C5}

và trả về một dải giá trị trong cả hai trường hợp:

Công thức mảng động được chuyển đổi thành công thức mảng CSE.

Nếu bạn chỉ muốn xuất một kết quả, hãy thêm @ vào trước mỗi biểu thức, sau đó sao chép công thức vào nhiều ô nếu cần (để giữ cho các phạm vi không thay đổi, hãy nhớ khóa chúng bằng tham chiếu ô tuyệt đối):

[email protected]$B$2:$B$5*@$C$2:$C$5

Trong Excel cũ hơn, ký hiệu @ sẽ tự động bị xóa và công thức sẽ có giao diện bình thường:

=$B$2:$B$5*$C$2:$C$5

Một công thức với toán tử @ được chuyển đổi thành một công thức không phải mảng bình thường.

Điều bạn không nên làm là trộn giao lộ ngầm tính toán mảng trong một công thức! Ví dụ: nếu bạn cố gắng nhập một cái gì đó như thế này trong Excel 365:

[email protected]:B5*C2:C5

Bạn sẽ được thông báo rằng công thức như vậy không được hỗ trợ trong các phiên bản Excel cũ hơn:

Công thức không được hỗ trợ trong các phiên bản Excel cũ hơn

Nếu bạn từ chối biến thể được đề xuất, công thức hỗn hợp sẽ được cam kết và sẽ mang lại một số kết quả (tuy nhiên, nó có thể không phải là kết quả bạn mong đợi). Nhưng khi bạn mở công thức đó trong Excel trước động, hàm _xlfn.SINGLE sẽ xuất hiện thay vì toán tử giao nhau ngầm:

=_xlfn.SINGLE(B2:B5)*C2:C5

Khi công thức này được đánh giá bởi Excel cũ hơn, lỗi #NAME! lỗi sẽ được trả lại.

Giao điểm ngầm trong bảng Excel

Nói chung, hành vi giao nhau ngầm trong những cái bàn phù hợp với phiên bản Excel của bạn.

Trong Excel 2019 trở về trước, bạn có thể tham chiếu đến toàn bộ cột và công thức sẽ phân giải thành một ô duy nhất trong hàng hiện tại.

Ví dụ: công thức này nhân thành công các số trong Giá bánSố lượng. cột:

=[Price]*[Qty.]

Mặc dù nó đề cập đến toàn bộ các cột, Excel trước động vẫn hoạt động trên các giá trị đơn lẻ ở cấp hàng.

Giao điểm ngầm trong bảng Excel

Trong Excel 365, phương pháp này sẽ không hoạt động vì giao lộ ngầm định bị tắt theo mặc định. Bởi vì công thức trả về nhiều giá trị và Excel không thể khớp tất cả chúng vào một bảng, nó sẽ đề xuất đặt tiền tố tên cột bằng ký hiệu @:

Các công thức trả về nhiều giá trị không được hỗ trợ trong bảng Excel.

Nếu bạn có kinh nghiệm với bảng tham khảo, cú pháp này sẽ quen thuộc với bạn – ký hiệu @ chỉ ra rằng công thức là xử lý các giá trị từ các cột Giá bánSố lượng. trên cùng một hàng.

=[@Price]*[@[Qty.]]

Và nó sẽ hoạt động tốt trong tất cả các phiên bản:

Toán tử giao nhau ngầm trong tham chiếu bảng

Tiền boa. Nếu bạn có ý định sử dụng hàm mảng động để tính toán dữ liệu của bảng, hãy đảm bảo đặt công thức của bạn bên ngoài bảng, vì mảng động bên trong bảng không được hỗ trợ.

Đó là cách giao nhau ngầm hoạt động trong Excel. Dịch vụ kế toán trọn gói của Ketoanmvb cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Trả lời