Excel свернулся и не расширяется

Обновлено: 04.07.2024

Динамические массивы — это самое большое изменение в формулах Excel за последние годы. Может быть, самое большое изменение когда-либо. Это связано с тем, что динамические массивы позволяют легко работать с несколькими значениями одновременно в формуле. В этой статье представлен обзор со множеством ссылок и примеров.

Динамические массивы — это самое большое изменение в формулах Excel за последние годы. Может быть, самое большое изменение когда-либо. Это связано с тем, что динамические массивы позволяют легко работать с несколькими значениями одновременно в формуле. Многие пользователи впервые понимают и используют формулы массива.

Это большое обновление и долгожданное изменение. Динамические массивы решат некоторые действительно сложные проблемы в Excel и коренным образом изменят способ проектирования и создания рабочих листов.

Доступность

Динамические массивы и новые функции, указанные ниже, доступны только в Excel 365 и Excel 2021. В Excel 2016 и Excel 2019 поддержка формул динамического массива не предусмотрена. Для удобства я буду использовать «Динамический Excel» (Excel 365) и «Традиционный Excel» (2019 или более ранняя версия), чтобы различать версии ниже.

Новые функции

В рамках обновления динамического массива Excel теперь включает 8 новых функций, которые напрямую используют динамические массивы для решения задач, которые традиционно трудно решить с помощью обычных формул. Нажмите на ссылки ниже, чтобы просмотреть подробные сведения и примеры для каждой функции:

td> < /tr>

Функция	Назначение
ФИЛЬТР	Фильтровать данные и возвращать соответствующие записи
RANDARRAY	Создать массив случайных чисел
ПОСЛЕДОВАТЕЛЬНОСТЬ	Создать массив последовательных чисел
СОРТИРОВАТЬ	Сортировать диапазон по столбцу
SORTBY	Сортировка диапазона по другому диапазону или массиву
UNIQUE	Извлечение уникальных значений из списка или диапазона
XLOOKUP	Современная замена функции VLOOKUP
XMATCH	Современная замена функции MATCH< /td>

Функция LET теперь также является частью новых функций динамического массива.

Пример

Прежде чем мы углубимся в детали, давайте рассмотрим простой пример. Ниже мы используем новую функцию UNIQUE для извлечения уникальных значений из диапазона B5:B15 с помощью одной формулы, введенной в E5:

Результатом является список из пяти уникальных названий городов, которые появляются в E5:E9.

Как и все формулы, UNIQUE будет автоматически обновляться при изменении данных. Ниже Ванкувер заменил Портленд в строке 11. Результат от UNIQUE теперь включает Ванкувер:

Растекание: одна формула, много значений

В Dynamic Excel формулы, которые возвращают несколько значений, будут "выбрасывать" эти значения непосредственно на рабочий лист. Это сразу будет более логичным для пользователей формул. Кроме того, это полностью динамическое поведение: при изменении исходных данных результаты немедленно обновляются.

Прямоугольник, в который заключены значения, называется "диапазоном заполнения". Вы заметите, что область разлива имеет специальную подсветку. В приведенном выше примере UNIQUE диапазон разлива — E5:E10.

При изменении данных диапазон разлива будет увеличиваться или уменьшаться по мере необходимости. Вы можете увидеть добавление новых значений или исчезновение существующих значений. Таким образом, диапазон распространения — это новый вид динамического диапазона.

В традиционном Excel, напротив, вы можете увидеть несколько результатов, возвращаемых формулой массива, в строке формул, если вы используете F9 для проверки формулы. Однако, если формула не введена как формула массива с несколькими ячейками, на листе будет отображаться только одно значение. Такое поведение всегда затрудняло понимание формул массива. Расширение делает формулы массива более интуитивными.

Справочник по диапазону разлива

Это то же самое, что и ссылка на весь диапазон сброса, и вы увидите этот синтаксис при написании формулы, которая ссылается на полный диапазон сброса.

Вы можете напрямую передать ссылку на диапазон разливов в другие формулы. Например, чтобы подсчитать количество городов, возвращенных UNIQUE, вы можете использовать:

При изменении диапазона разлива формула будет отражать последние данные.

Значительное упрощение

Добавление новых формул динамического массива означает, что некоторые формулы могут быть значительно упрощены. Вот несколько примеров:

Извлечение и перечисление уникальных значений (до | после)
Подсчет уникальных значений (до | после)
Фильтрация и извлечение записей (до | после)
Извлечь частичные совпадения (до | после)

Сила одного

Одним из наиболее весомых преимуществ подхода "одна формула, много значений" является меньшая зависимость от абсолютных или смешанных ссылок. Поскольку формула динамического массива выводит результаты на рабочий лист, ссылки остаются неизменными, но формула генерирует правильные результаты.

Например, ниже мы используем функцию ФИЛЬТР для извлечения записей в группе "А". В ячейку F5 вводится одна формула:

Обратите внимание, что оба диапазона являются разблокированными относительными ссылками, но формула работает отлично.

Это огромное преимущество для многих пользователей, поскольку оно значительно упрощает процесс написания формул. Еще один хороший пример см. в таблице умножения ниже.

Связывание функций

Все становится действительно интересным, когда вы связываете вместе более одной функции динамического массива. Возможно, вы хотите отсортировать результаты, возвращаемые UNIQUE? Легкий. Просто оберните функцию SORT вокруг функции UNIQUE следующим образом:

Как и раньше, при изменении исходных данных автоматически появляются новые уникальные результаты, хорошо отсортированные.

Нативное поведение

Важно понимать, что поведение динамического массива является нативным и глубоко интегрированным. Когда какая-либо формула возвращает несколько результатов, эти результаты будут распределены по нескольким ячейкам на листе. Сюда входят старые функции, изначально не предназначенные для работы с динамическими массивами.

Например, в традиционном Excel, если мы зададим функции ДЛСТР диапазон текстовых значений, мы увидим один результат. В Dynamic Excel, если мы зададим функции ДЛСТР диапазон значений, мы увидим несколько результатов. Этот экран ниже показывает старое поведение слева и новое поведение справа:

Это огромное изменение, которое может затронуть все виды формул. Например, функция ВПР предназначена для извлечения одного значения из таблицы с использованием индекса столбца. Однако в Dynamic Excel, если мы зададим ВПР более одного индекса столбца, используя такую константу массива:

ВПР вернет несколько столбцов:

Другими словами, хотя функция ВПР никогда не предназначалась для возврата нескольких значений, теперь она может это делать благодаря новому механизму формул в Dynamic Excel.

Все формулы

И наконец, обратите внимание, что динамические массивы работают со всеми формулами, а не только с функциями. В приведенном ниже примере ячейка C5 содержит одну формулу:

Результат попадает в диапазон 10 x 10, включающий 100 ячеек:

Если числа в диапазонах B5:B14 и C4:L4 сами по себе являются динамическими массивами (т. е. созданы с помощью функции SEQUENCE), оператор ссылки на разлив можно использовать следующим образом:

Массивы становятся популярными

С внедрением динамических массивов слово "массив" будет появляться гораздо чаще. На самом деле вы можете увидеть, что «массив» и «диапазон» используются почти взаимозаменяемо. Вы увидите массивы в Excel, заключенные в фигурные скобки следующим образом:

Массив — это термин программирования, обозначающий список элементов, расположенных в определенном порядке.Причина, по которой массивы так часто встречаются в формулах Excel, заключается в том, что массивы могут идеально отображать значения в диапазоне ячеек.

Операции с массивами становятся важными

Поскольку динамические формулы Excel могут легко работать с несколькими значениями, операции с массивами станут более важными. Термин «операция с массивом» относится к выражению, которое запускает логическую проверку или математическую операцию над массивом. Например, приведенное ниже выражение проверяет, равны ли значения в B5:B9 "ca"

поскольку в B5:B9 5 ячеек, результатом будет 5 значений TRUE/FALSE в массиве:

Приведенная ниже операция с массивом проверяет суммы, превышающие 100:

Последняя операция массива объединяет тест A и тест B в одном выражении:

Примечание. Excel автоматически приводит значения ИСТИНА и ЛОЖЬ к 1 и 0 во время математической операции.

Чтобы вернуться к формулам динамического массива в Excel, приведенный ниже пример демонстрирует, как мы можем использовать точно такую же операцию массива внутри функции ФИЛЬТР, что и аргумент include:

FILTER возвращает две записи, в которых состояние = "ca" и количество > 100.

Новые и старые формулы массива

В Dynamic Excel нет необходимости вводить формулы массива с помощью клавиш Ctrl + Shift + Enter. При создании формулы Excel проверяет, может ли формула возвращать несколько значений. Если это так, она будет автоматически сохранена как формула динамического массива, но вы не увидите фигурных скобок. В приведенном ниже примере показана типичная формула массива, введенная в Dynamic Excel:

Если вы откроете ту же формулу в традиционном Excel, вы увидите фигурные скобки:

С другой стороны, когда "традиционная" формула массива открыта в Dynamic Excel, вы увидите фигурные скобки в строке формул. Например, на приведенном ниже экране показана простая формула массива в традиционном Excel:

Однако, если вы повторно введете формулу без изменений, фигурные скобки будут удалены, и формула вернет тот же результат:

Суть в том, что формулы массива, введенные с помощью клавиш Ctrl + Shift + Enter (CSE), по-прежнему работают для обеспечения совместимости, но вам не нужно вводить формулы массива с помощью CSE в Dynamic Excel.

Символ @

С введением динамических массивов вы увидите, что символ @ чаще появляется в формулах. Символ @ включает поведение, известное как «неявное пересечение». Неявное пересечение — это логический процесс, при котором многие значения сводятся к одному значению.

В традиционном Excel неявное пересечение — это молчаливое поведение, используемое (при необходимости) для сведения нескольких значений к одному результату в одной ячейке. В Dynamic Excel это обычно не требуется, поскольку на лист может попасть несколько результатов. При необходимости неявное пересечение вызывается вручную с помощью символа @.

При открытии электронных таблиц, созданных в более ранней версии Excel, вы можете увидеть, что символ @ автоматически добавляется к существующим формулам, которые имеют потенциал для возврата многих значений. В традиционном Excel формула, возвращающая несколько значений, не будет отображаться на листе.Символ @ вызывает такое же поведение в Dynamic Excel, поэтому формула ведет себя так же и возвращает тот же результат, что и в исходной версии Excel.

Другими словами, @ добавляется, чтобы старая формула не выбрасывала несколько результатов на лист. В зависимости от формулы вы можете удалить символ @, и поведение формулы не изменится.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Иногда вам нужно переключать или вращать ячейки. Вы можете сделать это, скопировав, вставив и используя опцию Transpose. Но при этом создаются дублированные данные. Если вы этого не хотите, вы можете вместо этого ввести формулу, используя функцию ТРАНСП. Например, на следующем рисунке формула =ТРАНСП(A1:B4) берет ячейки с A1 по B4 и упорядочивает их по горизонтали.

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите Ctrl+Shift+Enter для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Шаг 1. Выберите пустые ячейки

Сначала выберите несколько пустых ячеек. Но обязательно выберите то же количество ячеек, что и исходный набор ячеек, но в другом направлении. Например, здесь 8 ячеек, расположенных вертикально:

Итак, нам нужно выделить восемь горизонтальных ячеек, вот так:

Здесь окажутся новые транспонированные ячейки.

Шаг 2. Введите =ТРАНСП(

Не снимая выделения с пустых ячеек, введите: =ТРАНСПОЗА(

Excel будет выглядеть примерно так:

Обратите внимание, что восемь ячеек по-прежнему выделены, несмотря на то, что мы начали вводить формулу.

Шаг 3. Введите диапазон исходных ячеек.

Теперь введите диапазон ячеек, которые вы хотите транспонировать. В этом примере мы хотим перенести ячейки из A1 в B4. Таким образом, формула для этого примера будет следующей: =ТРАНСП(A1:B4) -- но пока не нажимайте клавишу ВВОД! Просто перестаньте печатать и перейдите к следующему шагу.

Excel будет выглядеть примерно так:

Шаг 4. Наконец, нажмите CTRL+SHIFT+ENTER

Теперь нажмите CTRL+SHIFT+ENTER. Почему? Поскольку функция ТРАНСП используется только в формулах массива, именно так вы завершаете формулу массива. Короче говоря, формула массива — это формула, которая применяется более чем к одной ячейке. Поскольку на шаге 1 вы выбрали более одной ячейки (вы это сделали, не так ли?), формула будет применена более чем к одной ячейке. Вот результат после нажатия CTRL+SHIFT+ENTER:

Вам не нужно вводить диапазон вручную. После ввода =ТРАНСП( вы можете использовать мышь, чтобы выбрать диапазон. Просто щелкните и перетащите от начала диапазона к концу. Но помните: нажмите CTRL+SHIFT+ENTER, когда закончите, а не ENTER сам по себе.

Нужно также транспонировать текст и форматирование ячеек? Попробуйте скопировать, вставить и использовать опцию Transpose. Но имейте в виду, что это создает дубликаты. Поэтому, если ваши исходные ячейки изменятся, копии не будут обновлены.

Есть еще что узнать о формулах массива. Создайте формулу массива или ознакомьтесь с подробными рекомендациями и примерами здесь.

Технические детали

Функция ТРАНСП возвращает диапазон ячеек по вертикали как диапазон по горизонтали или наоборот. Функцию ТРАНСП нужно вводить как формулу массива в диапазоне, который имеет такое же количество строк и столбцов, соответственно, как исходный диапазон имеет столбцы и строки. Используйте ТРАНСП, чтобы сместить вертикальную и горизонтальную ориентацию массива или диапазона на листе.

Синтаксис

Синтаксис функции ТРАНСП имеет следующий аргумент:

Обязательный массив. Массив или диапазон ячеек на листе, которые вы хотите транспонировать. Транспонирование массива создается путем использования первой строки массива в качестве первого столбца нового массива, второй строки массива в качестве второго столбца нового массива и так далее. Если вы не знаете, как ввести формулу массива, см. раздел Создание формулы массива.

Функция МУМНОЖ возвращает матричное произведение двух массивов. В результате получается массив с тем же количеством строк, что и у массива1, и таким же количеством столбцов, как у массива2.

Примечание. Если у вас установлена текущая версия Microsoft 365, вы можете просто ввести формулу в верхнюю левую ячейку выходного диапазона, а затем нажать клавишу ВВОД, чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести как устаревшую формулу массива, сначала выбрав выходной диапазон, введя формулу в верхнюю левую ячейку выходного диапазона, а затем нажав CTRL+SHIFT+ENTER для подтверждения. Excel вставляет фигурные скобки в начале и в конце формулы. Дополнительные сведения о формулах массивов см. в разделе Рекомендации и примеры формул массивов.

Синтаксис

МУМНОЖ(массив1, массив2)

Синтаксис функции МУМНОЖ имеет следующие аргументы:

массив1, массив2 Обязательно. Массивы, которые вы хотите умножить.

Примечания

Количество столбцов в массиве1 должно совпадать с количеством строк в массиве2, и оба массива должны содержать только числа.

Массив1 и массив2 могут быть заданы как диапазоны ячеек, константы массива или ссылки.

Все ячейки пусты или содержат текст.

Количество столбцов в массиве1 отличается от количества строк в массиве2.

Массив матричного произведения a двух массивов b и c равен:

где i — номер строки, а j — номер столбца.

Примеры

Вы должны ввести приведенные выше формулы как формулы массива, чтобы они работали правильно. После ввода формулы нажмите клавишу ВВОД, если у вас есть действующая подписка на Microsoft 365; в противном случае нажмите Ctrl+Shift+Enter. Если формула не введена как формула массива, возвращается один результат.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Формулы массива — это мощные формулы, которые позволяют выполнять сложные вычисления, которые часто невозможно выполнить с помощью стандартных функций рабочего листа. Их также называют формулами «Ctrl-Shift-Enter» или «CSE», поскольку для их ввода необходимо нажать Ctrl+Shift+Enter. Вы можете использовать формулы массива, чтобы сделать, казалось бы, невозможное, например

Подсчитать количество символов в диапазоне ячеек.

Суммируйте числа, соответствующие определенным условиям, например наименьшие значения в диапазоне или числа, находящиеся между верхней и нижней границей.

Суммировать каждое n-е значение в диапазоне значений.

Excel предоставляет два типа формул массива: формулы массива, которые выполняют несколько вычислений для получения одного результата, и формулы массива, которые вычисляют несколько результатов. Некоторые функции рабочего листа возвращают массивы значений или требуют массив значений в качестве аргумента. Дополнительные сведения см. в разделе Рекомендации и примеры формул массива.

Этот тип формулы массива может упростить модель рабочего листа, заменив несколько разных формул одной формулой массива.

Нажмите ячейку, в которую вы хотите ввести формулу массива.

Введите формулу, которую хотите использовать.

Формулы массива используют стандартный синтаксис формулы. Все они начинаются со знака равенства (=), и в формулах массива можно использовать любые встроенные функции Excel.

Например, эта формула вычисляет общую стоимость массива цен акций и акций и помещает результат в ячейку рядом с "Общая стоимость".

Формула сначала умножает акции (ячейки B2 – F2) на их цены (ячейки B3 – F3), а затем складывает эти результаты, чтобы получить общее число 35 525. Это пример формулы массива с одной ячейкой, поскольку формула находится только в одной ячейке.

Нажмите Enter (если у вас есть действующая подписка на Microsoft 365); в противном случае нажмите Ctrl+Shift+Enter.

Когда вы нажимаете Ctrl+Shift+Enter, Excel автоматически вставляет формулу между (парой открывающих и закрывающих фигурных скобок).

Чтобы вычислить несколько результатов с помощью формулы массива, введите массив в диапазон ячеек с таким же количеством строк и столбцов, которое вы будете использовать в аргументах массива.

Выберите диапазон ячеек, в который вы хотите ввести формулу массива.

Введите формулу, которую хотите использовать.

В следующем примере формула умножает акции на цену в каждом столбце, и формула сохраняется в выбранных ячейках в строке 5.

Нажмите Enter (если у вас есть действующая подписка на Microsoft 365); в противном случае нажмите Ctrl+Shift+Enter.

Если вам нужно включить новые данные в формулу массива, см. раздел Расширение формулы массива. Вы также можете попробовать:

Формулы Excel, возвращающие набор значений, также известный как массив, возвращают эти значения в соседние ячейки. Такое поведение называется сбросом.

Формулы, которые могут возвращать массивы переменного размера, называются формулами динамического массива. Формулы, которые в настоящее время возвращают массивы, которые успешно переносятся, можно назвать формулами перенесенного массива.

Ниже приведены некоторые примечания, которые помогут вам понять и использовать формулы такого типа.

Что означает разлив?

Примечание. Старые формулы массива, известные как устаревшие формулы массива, всегда возвращают результат фиксированного размера — они всегда распределяются по одному и тому же количеству ячеек. Поведение при сбросе, описанное в этом разделе, не применяется к устаревшим формулам массива.

Рассеивание означает, что формула привела к нескольким значениям, и эти значения были помещены в соседние ячейки. Например, =SORT(D2:D11,1,-1), который сортирует массив в порядке убывания, вернет соответствующий массив высотой 10 строк. Но вам нужно только ввести формулу в верхнюю левую ячейку или в данном случае F2, и она автоматически переместится в ячейку F11.

Ключевые моменты

Когда вы нажмете Enter, чтобы подтвердить формулу, Excel динамически изменит размер выходного диапазона и поместит результаты в каждую ячейку в этом диапазоне.

Если вы пишете формулу динамического массива для работы со списком данных, может быть полезно поместить ее в таблицу Excel, а затем использовать структурированные ссылки для ссылки на данные. Это связано с тем, что структурированные ссылки автоматически корректируются при добавлении или удалении строк из таблицы.

Формулы перенесенного массива не поддерживаются в самих таблицах Excel, поэтому их следует размещать в сетке за пределами таблицы. Таблицы лучше всего подходят для хранения строк и столбцов независимых данных.

После того как вы введете формулу массива с разбросом, при выборе любой ячейки в области разброса Excel поместит выделенную рамку вокруг диапазона.Граница исчезнет, когда вы выберете ячейку за пределами области.

Редактировать можно только первую ячейку в области разлива. Если вы выберете другую ячейку в области разлива, формула будет видна в строке формул, но текст будет «замаскирован» и не может быть изменен. Если вам нужно обновить формулу, вы должны выбрать верхнюю левую ячейку в диапазоне массива, изменить ее по мере необходимости, тогда Excel автоматически обновит остальную часть области разлива для вас, когда вы нажмете Enter.

Устаревшие формулы массива, вводимые с помощью клавиш CTRL+SHIFT+ENTER (CSE), по-прежнему поддерживаются из соображений обратной совместимости, но их больше нельзя использовать. Если хотите, вы можете преобразовать устаревшие формулы массива в формулы динамического массива, найдя первую ячейку в диапазоне массива, скопировав текст формулы, удалив весь диапазон устаревшего массива, а затем повторно введя формулу в верхней части. левая ячейка. Перед обновлением устаревших формул массива до формул динамического массива следует знать о некоторых различиях в расчетах между ними.

Нужна дополнительная помощь?

Читайте также: