Discover the powerful new features of Microsoft 365 Excel

Microsoft 365 訂閱版的 Excel 持續進化，不斷推出令人驚豔的新功能，大幅提升資料處理、分析和協作的效率。以下為您介紹幾個近年來的亮點功能：

1. 動態陣列 (Dynamic Arrays) 與新函數

這是 Excel 近年最重要的變革之一。傳統上，陣列公式需要複雜的 Ctrl+Shift+Enter 操作，且結果固定在特定範圍。動態陣列讓公式結果能自動「溢出」(Spill) 到鄰近的空白儲存格，極大簡化了公式撰寫。

伴隨動態陣列而來的是一系列強大的新函數：

• FILTER：根據指定條件篩選範圍或陣列中的資料。
• SORT: Sort content by range or array.
• SORTBY：根據對應範圍或陣列中的值對範圍或陣列的內容進行排序。
• UNIQUE: Return a list of unique values in a list or range
• SEQUENCE：生成一系列循序數字，例如 1, 2, 3, 4。
• RANDARRAY: Returns an array of random numbers between 0 and 1

2. XLOOKUP 函數

XLOOKUP Recognized as VLOOKUP 和 HLOOKUP Its powerful successor. It overcomes many limitations of the traditional query function, providing a more flexible and intuitive way to query data:

• defaultAccurate comparisonReduce errors
• You can do this fromLeft sideor search on the right (unlike VLOOKUP, which can only be searched from the first column on the left).
• You can specify a default value when a value cannot be foundReturn Results。
• SupportReverse search(Starting with the last item).
• More concise syntax.

Basic syntax:XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

3. LET 函數

LET Functions allow you to define variable names and their corresponding values or calculated results within a formula. This makes complex formulas easier to read, understand, and maintain, and can improve performance by performing repeated calculations only once.

For example, a long formula can be simplified by LET Become more clear:

=LET(avg_sales, AVERAGE(C2:C10), IF(SUM(B2:B10) > avg_sales * 5, "High Performance", "Normal"))

Here, 3. Price coverageavg_sales 被定義為 AVERAGE(C2:C10), and use directly in subsequent IF statements.

4. LAMBDA 函數

LAMBDA Functions are a major breakthrough in Excel features, allowing users toCustomizable reusable functions, without needing VBA or code. You can define a LAMBDA formula and then name it through the "name manager," after which you can call it anywhere in a live workbook just like a built-in function.

這對於建立複雜的、需在多處使用的計算邏輯非常有用，大大提高了公式的模組化和重用性。

5. Data types

Excel can convert plain text into "data types" that contain rich information. Currently supported types includeStocks、Geography、Organization (Requires a specific account) etc.

For example, after entering a country name and converting it to a "Geography" data type, you can easily extract information such as population, capital, area, etc. into different cells.

6. Analyze Data / Ideas

This feature (formerly called Ideas) uses AI to automatically analyze the data range you select andRecommended hubs for analysis, charts, and trends, helping you quickly identify patterns and key insights in your data.

Simply select the data and click the "Analyze Data" button on the "Common" tab. Excel will provide various visualization options for you to choose from.

7. 增強的協作功能

• Real-Time Co-authoring: Multiple people can edit the same Excel workbook stored on OneDrive or SharePoint at the same time and see each other's changes in real time.
• Comments and @mentions (@mentions): Modern annotation features support chat threads, and you can mention colleagues using the @ symbol, who will receive a notification.
• Sheet View: When collaborating, you can create your own temporary views (sort, filter) without affecting other users who are viewing or editing the same file.

Conclusion

The new features in Microsoft 365 Excel go beyond this, including continuous enhancements to Power Query, new chart types, performance check tools, and more. These updates aim to make Excel more powerful, intelligent, and collaborative. If you're a Microsoft 365 subscriber, be sure to explore and leverage these new tools to boost your productivity!