CTRL Shift-Enter в Excel
Ctrl Shift-Enter в Excel (Содержание)
Excel Ctrl Shift-Enter
Ctrl Shift-Enter является одним из ярлыков, используемых в Excel для выполнения расчетов с формулами массива. Он поддерживает в выполнении сложных расчетов с использованием стандартных функций Excel. Он широко используется в формулах массива для применения функций и формул к набору данных. Совместное использование Ctrl Shift-Enter помогает конвертировать данные в формат массива, состоящий из нескольких значений данных в Excel. Он также поддерживает различие между обычной формулой и формулой массива. Благодаря этому ярлыку есть два основных преимущества, таких как работа с набором значений за раз, а также возврат нескольких значений за раз.
Объяснение Ctrl Shift-Enter в Excel
Как использовать Ctrl Shift-Enter в Excel?
CTRL SHIFT-ENTER используется во многих приложениях в Excel.
Примеры Ctrl Shift-Enter в Excel
Ниже приведены примеры управления Excel Shift-Enter.
Этот пример иллюстрирует использование Ctrl SHIFT-ENTER при расчете суммы продаж, созданных для разных продуктов. Следующие данные рассматриваются для этого примера.
Шаг 1: Поместите курсор в пустую ячейку, где вы хотите рассчитать общую сумму продаж продукта.
Шаг 2: введите формулу как сумма = сумма (D6: D11 * E6: E11), как показано на рисунке ниже.
Шаг 3: После ввода формулы нажмите CTRL SHIFT-ENTER, чтобы преобразовать общую формулу в формулу массива.
Шаг 4: Открывающая и закрывающая скобки добавляются в формулу суммы. Результат получается так, как показано на рисунке.
Шаг 5: Проверьте результат определения итога с помощью общей процедуры, которую мы использовали.
Результат, полученный в результате двух процедур, одинаков. Но использование формулы массива с помощью клавиши CTRL SHIFT ENTER позволяет легко определить общий объем продаж, исключив вычисление дополнительного столбца промежуточной суммы.
Пример №2. Определение суммы с использованием условия
Этот пример иллюстрирует использование Ctrl SHIFT-ENTER при расчете суммы продаж, сгенерированных для различных продуктов, с использованием условий. Следующие данные рассматриваются для этого примера.
Шаг 1: На первом шаге определите детали продавца и продукта, которые хотят рассчитать объем продаж, сгенерированный индивидуально. Рассмотрим продавца = John и Product = PC и введите эти данные в соответствующие ячейки, как показано на скриншоте ниже.
Шаг 2: Чтобы определить объем продаж, произведенных Джоном при продаже продуктов для ПК, формула
= СУММА (ЕСЛИ (((A2: A10 = F2) * (B2: B10 = F3)), (C2: C10)))
Шаг 3: Нажмите CTRL SHIFT-ENTER, чтобы получить желаемый результат, как показано на рисунке.
Шаг 4: Измените значения ячеек F2 и F3, чтобы определить продажи, произведенные другими лицами.
Таким образом, расчет сгенерированных продаж автоматизируется с помощью комбинации клавиш CTRL SHIFT-ENTER.
Для этого примера рассмотрим следующую матрицу А.
Шаг 1: Введите матрицу A в лист Excel, как показано на рисунке ниже.
Диапазон матрицы B1: C2
Шаг 3: После выделения диапазона ячеек введите формулу MINVERSE для вычисления обратной матрицы. Будьте внимательны при вводе формулы, чтобы все ячейки были выделены.
Шаг 4: Введите диапазон массива или матрицы, как показано на скриншоте.
Шаг 5: После успешного ввода функции MINVERSE используйте комбинацию клавиш CTRL SHIFT-ENTER, чтобы сгенерировать формулу массива, чтобы получить результаты всех четырех компонентов матрицы без повторного входа в другие компоненты. Преобразованная формула массива отображается как (= MINVERSE (B1: C2))
Шаг 6: Результирующая обратная матрица получается как:
То, что нужно запомнить
Рекомендуемые статьи
Формулы массива в Excel
Терминология
Под массивом обычно понимают набор данных, объединенных в группу. Массивы бывают одномерные (элементы массива образуют строку или столбец) или двумерные (матрица). Легко сообразить, что почти в любой таблице Excel при желании можно найти один или несколько таких массивов:
Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:
Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.
Пример 2. Разрешите Вас. транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.
Допустим, имеем двумерный массив ячеек, который хотим транспонировать.
жмем Ctrl + Shift + Enter и получаем «перевернутый массив» в качестве результата:
Редактирование формулы массива
Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.
Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)
Пример 3. Таблица умножения
Вспомните детство, школу, свою тетрадку по математике. На обороте тетради на обложке было что? Таблица умножения вот такого вида:
При помощи формул массива она вся делается в одно движение:
и получаем результат:
Пример 4. Выборочное суммирование
Посмотрите как при помощи одной формулы массива красиво и легко выбираются данные по определенному товару и заказчику:
В данном случае формула массива синхронно пробегает по всем элементам диапазонов C3:C21 и B3:B21, проверяя, совпадают ли они с заданными значениями из ячеек G4 и G5. Если совпадения нет, то результат равенства ноль, если совпадение есть, то единица. Таким образом суммы всех сделок, где заказчик не ANTON и товар не Boston Crab Meat умножаются на ноль и суммируются только нужные заказы.
Создание формулы массива
Формулы массива — это мощные формулы, позволяющие выполнять сложные вычисления, которые часто не могут выполняться со стандартными функциями. Их также называют формулами CTRL+SHIFT-ВВОД или CSE, так как для их ввода необходимо нажать CTRL+SHIFT+ВВОД. Формулы массива можно использовать для практически невозможного, например
Подсчет количества символов в диапазоне ячеек.
Суммирует числа, которые отвечают определенным условиям, например наименьшие значения в диапазоне или числа, которые выпадают между верхней и нижней границами.
Суммирование всех n-х значений в диапазоне значений.
Excel массива содержит два типа формул массива: формулы массива, которые выполняют несколько вычислений для получения одного результата, и формулы массива, которые вычисляют несколько результатов. Некоторые функции возвращают массивы значений или требуют массив значений в качестве аргумента. Дополнительные сведения см. в руководстве и примерах формул массива.
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Этот тип формулы позволяет упростить модель листа благодаря замене нескольких отдельных формул.
Щелкните ячейку, в которую нужно ввести формулу массива.
Введите необходимую формулу.
В формулах массива используется синтаксис обычных формул. Все они начинаются со знака равно (=), и вы можете использовать любую из встроенных Excel формул массива.
Например, эта формула вычисляет итоговую стоимость массива цен на акции и помещает результат в ячейку рядом с элементом «Итоговая стоимость».
Сначала формула умножает общие итоги (ячейки B2 – F2) на их цены (ячейки B3 – F3), а затем добавляет эти результаты, чтобы получить общий итог 35 525. Это пример формулы массива с одной ячейкой, так как она находится только в одной ячейке.
Нажмите ввод (если у вас есть текущая Microsoft 365 подписка); в противном случае нажмите CTRL+SHIFT+ВВОД.
При нажатии CTRL+SHIFT+ВВОДExcel автоматически вставляет формулу между (пара открываемой и закрываемой скобок).
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Чтобы вычислить несколько результатов с помощью формулы массива, введите массив в диапазон ячеек с таким же количеством строк и столбцов, что и в аргументах массива.
Выделите диапазон ячеек, в который нужно ввести формулу массива.
Введите необходимую формулу.
В формулах массива используется синтаксис обычных формул. Все они начинаются со знака равно (=), и вы можете использовать любую из встроенных Excel формул массива.
В следующем примере формула множается по цене в каждом столбце, а формула живет в выбранных ячейках строки 5.
Нажмите ввод (если у вас есть текущая Microsoft 365 подписка); в противном случае нажмите CTRL+SHIFT+ВВОД.
При нажатии CTRL+SHIFT+ВВОДExcel автоматически вставляет формулу между (пара открываемой и закрываемой скобок).
Примечание: Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Если вам нужно включить новые данные в формулу массива, см. статью Расширение формулы массива. Вы также можете попробовать:
Удаление формулы массива (здесь также нажатие CTRL+SHIFT+ВВОД)
Привяжем константы массива (они упрощают использование констант)
Ctrl Shift Enter в Excel
Excel Ctrl Shift-Enter Command
Совместное использование Ctrl Shift-Enter помогает преобразовать данные в формат массива, который состоит из нескольких значений данных в Excel. Он также поддерживает различие между обычной формулой и формулой массива в Excel. Этот ярлык дает два основных преимущества, таких как одновременная работа с набором значений и одновременный возврат нескольких значений.
Ctrl Shift-Enter — одно из сочетаний клавиш, используемых в Excel для выполнения вычислений с формулами массива. Он поддерживает выполнение сложных вычислений с использованием стандартных функций Excel. Он широко используется в формулах массива для применения функций и формул к набору данных.
Объяснение
Примеры Ctrl Shift-Enter в Excel
Пример №1 — Определение суммы
Этот пример лучше всего иллюстрирует использование Ctrl SHIFT ENTER для расчета суммы продаж, произведенных для различных продуктов. В этом примере рассматриваются следующие данные.
Шаг 1: Поместите курсор в пустую ячейку, в которой вы хотите вывести сумму общих продаж продукта.
Шаг 2: Введите формулу как total = sum (D6: D11 * E6: E11), как показано на рисунке.
Шаг 3: После ввода формулы нажмите CTRL SHIFT ENTER для преобразования общей формулы в формулу массива.
Шаг 4: Открывающая и закрывающая фигурные скобки добавляются к формуле суммы в Excel. Результат получен, как показано на рисунке.
Шаг 5: Проверьте результат определения общей суммы с помощью общей процедуры, которую мы использовали.
Результат, полученный от двух процедур, одинаков. Но, используя формулу массива с CTRL SHIFT ENTER, легко определить общий объем продаж, исключив необходимость вычисления дополнительного столбца промежуточной суммы.
Пример # 2 — Определение суммы с использованием условия
Этот пример лучше всего иллюстрирует использование Ctrl SHIFT ENTER при вычислении суммы продаж, произведенных для различных продуктов с использованием условий. В этом примере рассматриваются следующие данные.
Шаг 1: На первом этапе определите детали продавца и продукта, для которых нужно рассчитать продажи, произведенные индивидуально. Рассмотрим Sили = Джон и Product = ПК и введите эти данные в соответствующие ячейки, как показано на рисунке.
Шаг 2: Для определения продаж, произведенных Джоном при продаже продуктов для ПК, формула
= СУММ (ЕСЛИ (((A2: A10 = F2) * (B2: B10 = F3)), (C2: C10)))
Шаг 3: Нажмите CTRL SHIFT ENTER, чтобы получить желаемый результат, как показано на рисунке.
Шаг 4: Измените значения ячеек F2 и F3, чтобы определить продажи, произведенные другими людьми.
Таким образом, расчет произведенных продаж автоматизирован с помощью сочетания клавиш CTRL SHIFT ENTER.
Пример # 3 — Определение обратной матрицы
В этом примере рассмотрим следующую матрицу A.
Шаг 1: Введите матрицу A в таблицу Excel, как показано на рисунке ниже.
Диапазон матрицы таков: B1: C2.
Шаг 3: Выделив диапазон ячеек, введите формулу MINVERSE для вычисления обратной матрицы. При вводе формулы проследите, чтобы все ячейки были выделены.
Шаг 4: Введите диапазон массива или матрицы, как показано на скриншоте.
Шаг 5: После успешного ввода функции MINVERSE используйте сочетание клавиш CTRL + SHIFT + ENTER, чтобы сгенерировать формулу массива, чтобы получить результаты всех четырех компонентов матрицы без повторного ввода в другие компоненты. Формула преобразованного массива отображается как
Шаг 6: Результирующая обратная матрица создается как:
Приложения
CTRL + SHIFT + ENTER используется во многих приложениях Excel.
5 основных функции для работы с массивами
Виды массивов функций Excel
Массив – данные, объединенные в группу. В данном случае группой является массив функций в Excel. Любую таблицу, которую мы составим и заполним в Excel, можно назвать массивом. Пример:
В зависимости от расположения элементов различают массивы:
Одномерные массивы бывают:
Примечание. Двумерные массивы Excel могут занимать сразу несколько листов (это сотни и тысячи данных).
Формула массива – позволяет обработать данные из этого массива. Она может возвращать одно значение либо давать в результате массив (набор) значений.
С помощью формул массива реально:
Когда мы используем формулы массива, Excel видит диапазон значений не как отдельные ячейки, а как единый блок данных.
Классика жанра – товарный чек
Задача: рассчитать общую сумму заказа. Если идти классическим путем, то нужно будет добавить столбец, где перемножить цену и количество, а потом взять сумму по этому столбцу. Если же применить формулу массива, то все будет гораздо красивее:
Т.е. Excel произвел попарное умножение элементов массивов B2:B5 и C2:C5 и образовал новый массив стоимостей (в памяти компьютера), а затем сложил все элементы этого нового массива.
Обратите внимание на фигурные скобки, появившиеся в формуле – отличительный признак формулы массива. Вводить их вручную с клавиатуры бесполезно – они автоматически появляются при нажатии Ctrl + Shift + Enter.
Разрешите Вас… транспонировать?
При работе с таблицами часто возникает необходимость поменять местами строки и столбцы, т.е. развернуть таблицу на бок, чтобы данные, которые раньше шли по строке, теперь располагались в столбцах и наоборот. В математике такая операция называется транспонированием. При помощи формулы массива и функции ТРАНСП (TRANSPOSE) это делается на раз.
Допустим, имеем двумерный массив ячеек, который хотим транспонировать.
жмем Ctrl + Shift + Enter и получаем “перевернутый массив” в качестве результата:
Функция СТРОКА
Определяет и возвращает номер строки указанной ссылкой ячейки.
Синтаксис: =СТРОКА([ссылка]), где аргумент «ссылка» не является обязательным. Если он опущен, но возвращается номер текущей строки.
=СТРОКА(D4) – результат 4.
=СТРОКА() – функция вернет номер строки, в которой она расположена.
Функция СТОЛБЕЦ
Возвращает номер столбца ячейки, указанной ссылкой.
Синтаксис: =СТОЛБЕЦ([ссылка]), где «ссылка» не обязательный аргумент. По умолчанию возвращается номер текущего столбца.
=СТОЛБЕЦ(C4) – формула вернет значение 3.
=СТОЛБЕЦ() – функция возвращает номер столбца, в котором расположена.
Функция АДРЕС
Возвращает текст, представляющий адрес ячейки, заданной номерами строки и столбца.
Синтаксис: =АДРЕС(строка; столбец; [тип_закрепления]; [стиль_ссылки]; [имя_листа]), где:
=АДРЕС(1;1)>=АДРЕС(1;1;4)>=АДРЕС(1;1;4;ЛОЖЬ)>=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″)>
Функция ДВССЫЛ
Преобразует адрес ссылки, заданный текстовой строкой, в ссылку на данный адрес.
Синтаксис: =ДВССЫЛ(адрес_ссылки; [стиль_ссылки]), где
=ДВССЫЛ(“a3”) – возвращает ссылку на ячейку A3.
=ДВССЫЛ(“r3c3”) – вернет ошибку #ССЫЛКА!, так как текст для ссылки в формате R1C1, а второй аргумент имеет значение по умолчанию.
=ДВССЫЛ(“r3c3”; ЛОЖЬ) – возвращает ссылку на ячейку C3.
=ДВССЫЛ(АДРЕС(СТРОКА(C3);СТОЛБЕЦ(C3))) – функция вернет аналогичный предыдущему примеру результат.
Вложение функции ДВССЫЛ со ссылкой на диапазон:
Функция ВЫБОР (CHOOSE)
Позволит вам выбрать значение из общего списка по указанному номеру позиции:
Функция ИНДЕКС (INDEX)
Эта функция возвращает указанное значение из одно- или двумерного диапазона:
=ИНДЕКС(A1:C6;4;3)
Как видно с примера, полученное значение 37, в указанном диапазоне стоит на пересечении строки №4 и столбика №3 в диапазоне A1:C6 указанном в формуле. В более простом примере показано как в диапазоне С1:С6, на 2 месте находится значение 15:
=ИНДЕКС(С1:С6;2) 
Функция ПОИСКПОЗ (MATCH)
Эта функция вернет позицию значения, которое вы будете искать в указанном диапазоне:
=ПОИСКПОЗ(B3;B2:B5;0)
Редактирование формулы массива
Если формула массива расположена не в одной ячейке (как в Примере 1), а в нескольких ячейках (как в Примере 2), то Excel не позволит редактировать или удалить одну отдельно взятую формулу (например в ячейке D10) и выдаст предупреждающее сообщение Невозможно изменить часть массива.
Для редактирования формулы массива необходимо выделить весь диапазон (A10:H11 в нашем случае) и изменить формулу в строке формул (или нажав F2). Затем необходимо повторить ввод измененной формулы массива, нажав сочетание клавиш Ctrl + Shift + Enter.
Excel также не позволит свободно перемещать ячейки, входящие в формулу массива или добавлять новые строки-столбцы-ячейки в диапазон формулы массива (т.е. в диапазон A10:H11 в нашем случае)
Описание категории «Ссылки и массивы»
Категория функций «Ссылки и массивы», другое название категории «Функции просмотра» используются для поиска значений в списках или таблицах, манипулирования диапазоном данных, а также, для получения дополнительной информации, например, информации о количестве строк или столбцов диапазона.
Ярким представителем функций данной категории является самая популярная функция ВПР, которая позволяет найти значение из ячейки диапазона, выполнив поиск в первом столбце этого же диапазона
Принцип работы функции ВПР из категории «Ссылки и массивы»
Функция ВПР (VLOOKUP) ищет значение в крайнем левом столбце таблицы и возвращает значение ячейки, находящейся в указанном столбце той же строки.
Пример применения формулы массива
Наиболее простой задачей, которой можно проиллюстрировать мощь формулы массива, является простой товарный чек. То есть у нас есть наименование товаров, их количество и цена за единицу. На выходе мы должны получить общую сумму покупки.
Как бы мы решали данную задачу стандартным образом?
Во-первых, мы бы получили итоговую сумму по каждому товару, перемножив количество товара на его цену.
Ну а затем просуммировали бы получение суммы, для получения итоговой. То есть нам бы потребовался дополнительный столбец для промежуточных вычислений.
Ту же самую задачу можно решить с помощью простейшей формулы массива.
Так как мы в формуле использовали диапазоны (массивы) данных, то и формула должна быть формулой массива. Для этого нужно нажать сочетание клавиш Ctrl + Shift + Enter и получим результат.
Что произошло и как работает формула массива? Это важно понять, так как в дальнейшем можно будет применять формулы массива для решения намного более заковыристых задач…
Итак, при вычислении формулы массива Excel произвел попарное умножение значений диапазонов B2:B5 и C2:C5. В результате получился массив значений который был просуммирован соответствующей функцией. Мы получили только одно значение итоговой суммы без вспомогательных вычислений.
Обратите внимание на формулу в строке формул. Она заключена в фигурные скобки, которые указывают нам, что мы имеем дело с формулой массива.
Похожим образом работает и функция ГПР (HLOOKUP):
Функции для работы со ссылками и массивами
В Excel имеется ряд функций для обработки ссылок и массивов: вычисление номеров строки или столбца таблицы по имени ссылки, определение количества столбцов (строк) ссылки или массива, выбор значения по номеру индекса и т. д.
Таблица 4.10. Функции для работы со ссылками и массивами.
| АДРЕС | Создает адрес ячейки в виде текста, используя номер строки и номер столбца. |
| ВПР | Просматривает левый столбец массива в поисках определенного значения и возвращает значение из указанной ячейки. |
| ВЫБОР | Использует номер индекса, чтобы выбрать и вернуть значение из списка аргументов-значений. |
| ГИПЕРССЫЛКА | Создание ссылки, открывающей документ, находящийся на жестком диске, сервере сети или Internet. |
| ГПР | Просматривает верхнюю строку массива в поисках определенного значения и возвращает значение из указанной ячейки. |
| двссыл | Возвращает ссылку, заданную аргументом ссылка на ячейку. |
| ИНДЕКС | Выбирает по индексу значение из ссылки или массива. |
| ОБЛАСТИ | Возвращает количество областей в ссылке. Область – это интервал смежных ячеек или отдельная ячейка. |
| ПОИСКПОЗ | Возвращает относительную позицию элемента массива. |
| ПОЛУЧИТЬ.ДАННЫЕ. СВОДНОЙ.ТАБЛИЦЫ | Получение данных сводной таблицы |
| ПРОСМОТР | Ищет значения в векторе или массиве. |
| СМЕЩ | Возвращает ссылку заданной высоты и ширины, отстоящую от другой ссылки на заданное количество строки и столбцов. |
| СТОЛБЕЦ | Возвращает номер столбца по заданной ссылке. |
| СТРОКА | Возвращает номер строки, определяемой ссылкой. |
| ТРАНШ | Возвращает транспонированный массив. |
| ЧИСЛСТОЛБ | Возвращает количество столбцов в ссылке или массиве. |
| ЧСТРОК | Возвращает количество строк в ссылке или массиве. |
Функция ГПР
Горизонтальное первое равенство. Ищет совпадение по ключу в первой строке определенного диапазона и возвращает значение из указанной строки этого диапазона в совпавшем с ключом столбце.
Синтаксис: =ГПР(ключ; диапазон; номер_строки; [интервальный_просмотр]).
Так как функция аналогична функции ВПР, за исключением того, что использует горизонтальные таблицы вместо вертикальных, то описания и примеры использования подходят и для ГПР с учетом упомянутых различий.
