內容
什麼是動態數組
2018 年 7 月，Microsoft 發布了一個更新，為 Microsoft Excel 添加了一個全新的工具：動態數組和 XNUMX 個用於處理它們的新函數。 毫不誇張地說，這些東西從根本上改變了使用公式和函數的所有常用技術，並且真正關心每個用戶。
考慮一個簡單的例子來解釋本質。
假設我們有一個包含城市月份數據的簡單表格。 如果我們選擇工作表右側的任何空單元格並在其中輸入一個不鏈接到一個單元格而是立即鏈接到一個範圍的公式會發生什麼？
在所有以前版本的 Excel 中，點擊後 Enter 我們只會得到一個第一個單元格 B2 的內容。 能怎樣？
好吧，或者可以將這個範圍包裝在某種聚合函數中，比如 =SUM(B2:C4) 並得到一個總計。
如果我們需要比原始求和更複雜的運算，例如提取唯一值或 Top 3，那麼我們將不得不使用鍵盤快捷鍵將我們的公式作為數組公式輸入 按Ctrl+轉移+Enter.
現在一切都不同了。
現在輸入這樣的公式後，我們可以簡單地點擊 Enter – 結果立即得到我們提到的所有值 uXNUMXbuXNUMXbto：
這不是魔術，而是 Microsoft Excel 現在擁有的新動態數組。 歡迎來到新世界🙂
使用動態數組的特點
從技術上講，我們的整個動態數組存儲在第一個單元格 G4 中，用它的數據填充所需數量的單元格。 如果您選擇數組中的任何其他單元格，則編輯欄中的鏈接將處於非活動狀態，表明我們位於“子”單元格之一中：
嘗試刪除一個或多個“子”單元格不會導致任何結果——Excel 將立即重新計算並填充它們。
同時，我們可以安全地在其他公式中引用這些“子”單元格：
如果復制數組的第一個單元格（例如，從 G4 到 F8），則整個數組（其引用）將沿與常規公式相同的方向移動：
如果我們需要移動數組，那麼移動就足夠了（使用鼠標或結合 按Ctrl+X, 按Ctrl+V)，同樣，只有第一個主單元格 G4——在它之後，它將被轉移到一個新的地方，我們的整個陣列將再次被擴展。
如果您需要在工作表上的其他位置引用創建的動態數組，則可以在其前導單元格的地址後使用特殊字符 #（“磅”）：
例如，現在您可以輕鬆地在引用創建的動態數組的單元格中創建一個下拉列表：
動態數組錯誤
但是，如果沒有足夠的空間來擴展數組，或者路徑中的其他數據已經佔用了單元格，會發生什麼？ 在 Excel 中遇到一種全新的錯誤類型—— ＃轉移！ （＃灑！）:
和往常一樣，如果我們點擊帶有黃色菱形和感嘆號的圖標，我們將得到更詳細的問題根源解釋，我們可以快速找到干擾單元格：
如果數組離開工作表或碰到合併的單元格，也會發生類似的錯誤。 如果您移除障礙物，那麼一切都會立即得到糾正。
動態數組和智能表
如果動態數組指向由鍵盤快捷鍵創建的“智能”表 按Ctrl+T 或 主頁 – 格式為表格 （首頁——表格格式），那麼它也將繼承它的主要品質——自動調整大小。
在底部或右側添加新數據時，智能表和動態範圍也會自動拉伸：
但是，有一個限制：我們不能在智能表內的論壇中使用動態範圍參考：
動態數組和其他 Excel 功能
好吧，你說。 這一切都很有趣也很有趣。 無需像以前一樣手動拉伸公式，並參考原始範圍的第一個單元格向下和向右等等。 就這樣？
不完全的。
動態數組不僅僅是 Excel 中的另一個工具。 現在它們已嵌入 Microsoft Excel 的核心（或大腦）——它的計算引擎。 這意味著我們熟悉的其他 Excel 公式和函數現在也支持使用動態數組。 讓我們看幾個例子，讓您了解已經發生的變化的深度。
顛倒
轉置範圍（交換行和列）Microsoft Excel 一直有一個內置函數 交通運輸部 （移調）. 但是，要使用它，您必須先正確選擇結果的範圍（例如，如果輸入的範圍是 5×3，那麼您必須選擇 3×5），然後進入函數並按組合 按Ctrl+轉移+Enter，因為它只能在數組公式模式下工作。
現在您可以只選擇一個單元格，在其中輸入相同的公式，然後單擊正常 Enter – 動態數組會自己做所有事情：
乘法表
這是我被要求在 Excel 中可視化數組公式的好處時給出的示例。 現在，要計算整個畢達哥拉斯表，站在第一個單元格 B2 中就足夠了，在那裡輸入一個將兩個數組相乘的公式（垂直和水平的數字集 1..10），然後單擊 Enter:
膠合和外殼轉換
數組不僅可以相乘，還可以用標準運算符 &（& 符號）粘合在一起。 假設我們需要從兩列中提取名字和姓氏，並糾正原始數據中的跳躍大小寫。 我們用一個形成整個數組的簡短公式來做到這一點，然後我們將函數應用於它 普羅納赫 （恰當的）整理註冊表：
結論 前 3 名
假設我們有一堆數字，我們想從中得出前三個結果，並按降序排列。 現在這是通過一個公式完成的，同樣，沒有任何 按Ctrl+轉移+Enter 就像之前一樣：
如果您希望結果不是放在一列中，而是放在一行中，那麼將這個公式中的冒號（行分隔符）替換為分號（一行內的元素分隔符）就足夠了。 在英文版的 Excel 中，這些分隔符分別是分號和逗號。
VLOOKUP 一次提取多列
操作功能 VPR （VLOOKUP） 現在您可以一次從多個列中提取值，而不是從一個列中提取值 - 只需在函數的第三個參數中將它們的數字（以任何所需的順序）指定為數組：
OFFSET 函數返回一個動態數組
用於數據分析的最有趣和最有用的（在 VLOOKUP 之後）函數之一是函數 處置 （抵消），我曾經在我的書中用了一整章和一篇文章在這裡。 理解和掌握這個函數的難點一直是它作為結果返回了一個數組（範圍）的數據，但是我們看不到它，因為 Excel 仍然不知道如何開箱即用地處理數組。
現在這個問題已經過去了。 看看現在如何使用一個公式和一個由 OFFSET 返回的動態數組，您可以從任何排序表中提取給定產品的所有行：
讓我們看看她的論點：
- A1 – 起始單元格（參考點）
- ПОИСКПОЗ(F2;A2:A30;0) – 計算從起始單元到第一個發現的捲心菜的移動。
- 0 – 相對於起始單元格向右移動“窗口”
- СЧЁТЕСЛИ(A2:A30;F2) – 計算返回的“窗口”的高度 – 有捲心菜的行數。
- 4 — 水平“窗口”的大小，即輸出 4 列
動態數組的新函數
除了在舊函數中支持動態數組機制外，Microsoft Excel 中還添加了幾個全新的函數，專門用於處理動態數組。 特別是，這些是：
- 年級 （種類） – 對輸入範圍進行排序並在輸出上生成一個動態數組
- 排序策略 （排序方式） - 可以按另一個範圍的值對一個範圍進行排序
- 過濾器 （篩選） – 從源範圍中檢索滿足指定條件的行
- 優尼克 （獨特的） – 從範圍中提取唯一值或刪除重複項
- 大規模的 （蘭德雷） – 生成給定大小的隨機數數組
- 胞衣 （序列） — 以給定的步長從一系列數字形成一個數組
更多關於他們的信息——稍後。 他們值得一篇單獨的文章（而不是一篇）進行深思熟慮的研究🙂
結論
如果您已經閱讀了上面寫的所有內容，那麼我認為您已經意識到已經發生的變化的規模。 Excel 中的許多事情現在可以更輕鬆、更輕鬆、更合乎邏輯地完成。 我必須承認，我對現在有多少文章必須在這裡、在這個網站和我的書中進行更正感到有點震驚，但我已經準備好輕鬆地做這件事了。
總結結果， плюсы 動態數組，您可以編寫以下內容：
- 你可以忘記組合 按Ctrl+轉移+Enter. Excel 現在認為“常規公式”和“數組公式”之間沒有區別，並以相同的方式對待它們。
- 關於功能 SUMPRODUCT （總和），以前用於輸入數組公式 按Ctrl+轉移+Enter 你也可以忘記 - 現在很容易 和 и Enter.
- 智能表和熟悉的函數（SUM、IF、VLOOKUP、SUMIFS 等）現在也完全或部分支持動態數組。
- 存在向後兼容性：如果您在舊版本的 Excel 中打開帶有動態數組的工作簿，它們將變成數組公式（在花括號中）並繼續以“舊樣式”工作。
找到了一些號碼 缺點:
- 您不能從動態數組中刪除單個行、列或單元格，即它作為單個實體存在。
- 您不能以通常的方式對動態數組進行排序 數據 - 排序 （數據——排序）. 現在有一個特殊的功能。 年級 （種類）.
- 動態範圍不能變成智能表（但您可以基於智能表製作動態範圍）。
當然，這還沒有結束，我相信微軟未來會繼續完善這個機制。
我在哪裡可以下載？
最後，主要問題🙂
微軟於 2018 年 XNUMX 月在一次會議上首次宣布並展示了 Excel 中動態數組的預覽 點燃. 在接下來的幾個月裡，對新功能進行了徹底的測試和試運行，首先是 貓 微軟本身的員工，然後是 Office Insiders 圈子的志願者測試人員。 今年，添加動態數組的更新開始逐步向普通 Office 365 訂閱者推出。 例如，我僅在 365 月通過我的 Office XNUMX Pro Plus（每月定向）訂閱收到了它。
如果您的 Excel 還沒有動態數組，但您確實想使用它們，那麼有以下選項：
- 如果您有 Office 365 訂閱，您只需等到此更新到達您的手中。 這種情況發生的速度取決於向您的 Office 交付更新的頻率（一年一次、每六個月一次、每月一次）。 如果您有一台公司 PC，您可以要求您的管理員將更新設置為更頻繁地下載。
- 您可以加入那些 Office Insiders 測試志願者的行列——然後您將是第一個獲得所有新特性和功能的人（當然，Excel 中的錯誤可能會增加）。
- 如果您沒有訂閱，而是有盒裝獨立版 Excel，那麼您至少要等到 2022 年發布下一版 Office 和 Excel。 此類版本的用戶只收到安全更新和錯誤修復，所有新的“好東西”現在只提供給 Office 365 訂閱者。 悲傷但真實🙂
無論如何，當動態數組出現在您的 Excel 中時——閱讀本文後，您將做好準備🙂
- 什麼是數組公式以及如何在 Excel 中使用它們
- 使用 OFFSET 函數的窗口（範圍）求和
- 在 Excel 中轉置表格的 3 種方法