@ สูตรหน้าตาใหม่ใน Excel 365 ที่ทำงานแทน Array แบบเดิมๆที่ใส่ {}
ในกรณีที่มีการอ้างอิงเป็นพื้นที่ตารางและต้องการนำเพียงค่าเดียวใน row นั้นมาใช้ เดิมทีต้องสร้างสูตรแบบ Array แบบนี้
{=Range1*Range2}
ใน Excel 365 ใช้แบบนี้แทน
=@Range1*@Range2
=============================
Implicit intersection operator: @
The implicit intersection operator was introduced as part of substantial upgrade to Excel’s formula language to support dynamic arrays. Dynamic arrays bring significant new calculation ability and functionality to Excel.
Upgraded Formula Language
Excel’s upgraded formula language is almost identical to the old language, except that it uses the @ operator to indicate where implicit intersection could occur, whereas the old language did this silently. As a result, you may notice @’s appear in some formulas when opened in dynamic array Excel. It’s important to note that your formulas will continue to calculate the same way they always have.
What is implicit intersection?
Implicit intersection logic reduces many values to a single value. Excel did this to force a formula to return a single value, since a cell could only contain a single value. If your formula was returning a single value, then implicit intersection did nothing (even though it was technically being done in the background). The logic works as follows:
◾If the value is a single item, then return the item.
◾If the value is a range, then return the value from the cell on the same row or column as the formula.
◾If the value is an array, then pick the top-left value.
With the advent of dynamic arrays, Excel is no longer limited to returning single values from formulas, so silent implicit intersection is no longer necessary. Where an old formula could invisibly trigger implicit intersection, dynamic array enabled Excel shows where it would have occurred with the @.
Why the @ symbol?
The @ symbol is already used in table references to indicate implicit intersection. Consider the following formula in a table =[@Column1]. Here the @ indicates that the formula should use implicit intersection to retrieve the value on the same row from [Column1].
Can you remove the @?
Often you can. It depends on what the part of the formula to the right of the @ returns:
◾If it returns a single value (the most common case), there will be no change by removing the @.
◾If it returns a range or array, removing the @ will cause it to spill to the neighboring cells.
If you remove an automatically added @ and later open the workbook in an older version of Excel, it will appear as a legacy array formula (wrapped with braces {}), this is done to ensure the older version will not trigger implicit intersection.
When do we add the @ to old formulas?
Generally speaking, functions that return multi-cell ranges or arrays will be prefixed with @ if they were authored in an older version of Excel. It’s important to note that there is no change to the way your formula behaves — you can just see the previously invisible implicit intersection now. Common functions that could return multi-cell ranges include INDEX, OFFSET, and User Defined Functions (UDFs). A common exception is if they are wrapped in a function that accepts an array or range (e.g. SUM() or AVERAGE()).
เรื่องนี้เริ่มสร้างปัญหาต่อไปนี้ตามมา พวกเราต้องระวังเวลาใช้ของใหม่กันหน่อยแล้วครับ
https://techcommunity.microsoft.com/t5/excel/implicit-intersection-operator-breaking-my-formulas/m-p/928020?WT.mc_id=M365-MVP-4000499
https://techcommunity.microsoft.com/t5/excel/implicit-intersection-operator-breaking-formulas/m-p/1166827?WT.mc_id=M365-MVP-4000499
https://techcommunity.microsoft.com/t5/excel/implicit-intersection-operator-advice-needed/m-p/1184393?WT.mc_id=M365-MVP-4000499
https://techcommunity.microsoft.com/t5/excel/implicit-intersection-operator-impact-on-opening-csv-files/m-p/1329195?WT.mc_id=M365-MVP-4000499
https://techcommunity.microsoft.com/t5/excel/microsoft-office-implicit-intersection-operator-quot-quot/m-p/1138367?WT.mc_id=M365-MVP-4000499
