Ctrl shift enter excel: Создание формулы массива — Служба поддержки Майкрософт

Содержание

Почему формулы массива (Ctrl+Shift+Enter) такие сложные: неявное пересечение

Это перевод главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Почему формулы массива (Ctrl+Shift+Enter) были так сложны для понимания?

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

Вопрос 1. Если в ячейку D8 вы введете =B4:B14, каков будет результат?

  • 2047, сумма значений B4:B14.
  • 1, значение первой ячейки в диапазоне.
  • 16, значение в той же строке диапазона.
  • Ошибка #ЗНАЧ! синтаксис формулы неверный

Рис. 44. Что вы получите, если в старом Excel в одной ячейке попытаться вернуть много значений?

Скачать заметку в формате Word или pdf, примеры в формате Excel

Вопрос 2. Если вы введете =B2:B14 в ячейку D16. Что вы получите? Ответ такой же, как и для вопроса 1? Или иной?

Вопрос 3. Что вернет формула =СУММ(C2:E2*B5:B7)?

  • 6, потому что 2 умножить на 3 = 6.
  • 18, потому что каким-то волшебным образом выполнится 2*3 + 2*3 + 2*3 = 18.
  • 54, потому что вы только что приняли ЛСД.
  • Ошибка #ССЫЛКА! потому что такая запись недопустима.

Рис. 45. Сумма произведений двух массивов

Вопрос 4. Чему равно =СУММ(ДЛСТР(A4:A15))? Будут ли результаты разными, если:

  • ввести формулу и нажать Enter;
  • ввести формулу и нажать Ctrl+Shift+Enter;
  • ввести в ячейку С4 формулу =ДЛСТР(A4), скопировать ее вниз до С15, суммировать значения в диапазоне С4:С15.

Рис. 46. Функция ДЛСТР ожидает в качестве аргумента одну ячейку

В прошлом я мог взять сложную формулу массива из книги Майка Гирвина Ctrl+Shift+Enter и использовать ее. Я иногда мог объяснить, как она работает. Для этого я открывал диалоговое окно Вычисление формулы на вкладе Формулы. Однажды я даже целый час говорил о формулах массива на мероприятии в Люцерне, Швейцария. Экхард Пфайффер присутствовал при этом и даже кивнул мне, подтверждая, что я не полностью ошибаюсь в своем понимании. Правда, я говорил по-английски для немецкоговорящей аудитории, так что, возможно, я ошибался довольно часто, но слушатели были слишком вежливы, чтобы указать мне на это.

Но я никогда по-настоящему не понимал формулы массивов, пока не посмотрел презентацию Джо Макдэйда о формулах массивов в прежних версиях Excel.

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

Краткий глоссарий
  • Старый Excel: Excel, который не поддерживает формулы динамических массивов.
  • Скалярный: одно значение или одна ячейка.
  • Массив или вектор: много значений или много ячеек прямоугольного диапазона.
Старый Excel использовал массивы гораздо чаще, чем мы думаем

В старом Excel вы использовали массивы в следующих случаях:

  • Когда имя не было диапазоном.
  • При использовании формулы в условном форматировании.
  • При нажатии клавиши F9 в то время, как были выделены некоторые символы в строке формул. Даже обычные формулы демонстрировали поведение массивов.

В следующих разделах описываются пять типов поведения при работе с массивами:

  1. Неявное пересечение (Implicit Intersection)
  2. Подъем (Lifting).
  3. Попарный подъем (Pairwise Lifting).
  4. Трансляция (Broadcasting).
  5. Усечение массива (Array Truncation).
Неявное пересечение

Неявное пересечение возникает, когда диапазон передается в формулу, ожидающую скаляр (одно значение), и Excel выбирает значение в той же строке или столбце, что и формула. Если в старом Excel вы введете =ДЛСТР($A$2:$A$7)[1] в ячейке В2, формула вернет значение 5, т.е. длину строки в ячейке А2.

Рис. 47. Неявное пересечение приводит к тому, что ответ основан на ячейке A2

Если вы введете эту же формулу в ячейке В4, ответ будет основан на ячейке А4:

Рис. 48. Скопируйте формулу в ячейку В4, и ответ будет основан на A4

Неявное пересечение работает и с горизонтальными диапазонами. Если ввести формулу =ДЛСТР(@$E$1:$J$1) в ячейку Е3, ответом будет длина строки Е1:

Рис. 49. ДЛСТР работает и с горизонтальными диапазонами

Неявное пересечение всегда работало в старом Excel. Мы просто не знали о нем, и поэтому не использовали. Однажды я выдал приз Гуру Excel за следующую формулу: =ВПР($A$2:$A$10;$F$2:$H$30;3;ЛОЖЬ). Эта формула одинакова во всем диапазоне D2:D10. Единственная причина, по которой она работает, заключается в том, что неявное пересечение указывает для формулы в D2 только на A2 из всего диапазона A2:A10… В D3 на А3, и т.д.

Рис. 50. В старом Excel можно использовать ВПР как функцию массива благодаря неявному пересечению

Нарушение неявного пересечения

Неявное пересечение терпит неудачу, когда вы вводите формулу в ячейку, которая не пересекает данные. Например, в ячейке G3:

Рис. 51. Если пересекаться не с чем, формула возвращает ошибку

Ошибка вернется, и когда Excel не сможет решить, с какой именно ячейкой использовать неявное пересечение. С В8, В9 или В10?

Рис. 52. Ошибка возвращается также, если формула пересекается с более чем одной ячейкой диапазона

В старом Excel формула =СУММ(В2:В6*С2:С6) в зависимости от того, в какой ячейке она вводилась, либо возвращала ошибку, либо выдавала результат перемножения двух значений с использованием неявного пересечения:

Рис. 53. Там, где в старом Excel не справляется функция СУММ на выручку приходит СУММПРОИЗВ

Если заменить СУММ на СУММПРОИЗВ, результат будет адекватным. Это связано с тем, что СУММПРОИЗВ игнорирует неявное пересечение.

Неявное пересечение – это то, что иногда приводило к ошибкам. Более того, часто вы не понимали, что виновато неявное пересечение.

Когда формула ожидает одно значение, а поступает массив, происходит подъем (lifting)

Что происходит, когда старый Excel ожидает одно значение, а вы предоставляете диапазон значений? Excel сформирует из этих значений массив, и функция вернет массив того же размера. Это называется подъем – подъем (или увеличение размерности) от скалярной величины к вектору.

Рис. 54. Функция ДЛСТР ожидает скаляр

Когда вы подаете на вход функции диапазон значений (вектор), Excel поднимает функцию и вычисляет в оперативной памяти 12 значений. Их можно увидеть, если выделить формулу в строке формул, и нажать F9, заменяя формулу значениями:

Рис. 55. В строке формул показан массив из 12 результатов работы функции ДЛСТР(А1:С4)

Усечение массива

Если вы попытаетесь использовать =ДЛСТР(A1:C4) в старом Excel, и введете формулу, нажимая Ctrl+Shift+Enter, Excel применит к функции подъем и вычислит 12 результатов. Но вернет только одно значение, соответствующее верхней левой ячейке диапазона A1:C4, или 3, потому что ДЛСТР("Joe") = 3. Это иллюстрирует концепцию усечения массива.

Прим. Багузина. Массив может усекаться не до одной ячейки, о до нескольких. Например, выделите ячейки Е6:F7 и введите формулу массива {=ДЛСТР(A1:C4)}. 12 значений, хранящиеся в памяти функции =ДЛСТР(A1:C4) будут представлены четырьмя результатами, соответствующими диапазону А1:В2.

Рис. 56. Усечение массива из 12 значений до четырех

Использование функции-оболочки в старом Excel

Распространенным подходом в старом Excel было использование функции массива ДЛСТР(A1:C4) внутри функции-оболочки. Например, вводя =СУММ(ДЛСТР(A1:C4)) и нажимая Ctrl+Shift+Enter в любом месте листа вы получите правильный результат – 67. Нажатие Ctrl+Shift+Enter блокирует неявное пересечение.

Рис. 57. Формула массива работает в старом Excel в любом месте листа

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

Пять функций в старом Excel блокировали неявное пересечение и без использования Ctrl+Shift+Enter. Это: СУММПРОИЗВ, МУМНОЖ, МОБР, ЧСТРОК и ЧИСЛСТОЛБ.

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

Благодаря динамическим массивам вам больше не нужно беспокоиться о том, что неявное пересечение испортит результаты. Формула =СУММ(ДЛСТР(A1:C4)), введенная в любом месте листа без Ctrl+Shift+Enter, вернет правильный результат:

Рис. 58. Формула динамического массива в новом Excel не требует Ctrl+Shift+Enter

От подъема к попарному подъему

Если Excel ожидает два скалярных выражения, а вы предоставляете ему два массива, Excel применит к ним попарное поднятие. Например, в старом Excel нельзя получить корректный результат, просто введя формулу ={1:2:3:4}+{1:1:1:1}

Рис. 59. Два массива имеют одинаковое количество строк (четыре), но для их сложения нужно поместить векторы внутрь функции

А. Если вы введете формулу ={1:2:3:4}+{1:1:1:1} в ячейку А1, и нажмете Enter или Ctrl+Shift+Enter, результат будет одинаковым = 2. Т.е., сумма двух первых элементов каждого массива. Б. Если выделить формулу ={1:2:3:4}+{1:1:1:1} в строке формул, и нажать F9, формула вернет массив {2:3:4:5}. В. Если выбрать диапазон С1:С4, ввести формулу ={1:2:3:4}+{1:1:1:1} и нажать Ctrl+Shift+Enter, вычисленный в Б. диапазон отразится в С1:С4. Г. Корректное суммирование двух массивов можно получить, если поместить исходную формулу внутрь функции СУММ.

Трансляция приводит все массивы к одному размеру

Когда Excel имеет дело с массивом и скаляром, он расширяет скаляр до размерности массива. Просто размножая значение скаляра. Предположим, что у вас есть массив из трех строк по одному столбцу. Чтобы умножить его на скаляр (или массив из одной строки и одного столбца), Excel расширит этот скаляр теми же значениями до размера первого массива:

Рис. 60. Трансляция (или расширение) приводит два массива к одному размеру

Если поместить произведение массива и скаляра внутрь функции суммирования =СУММ({1;2;3}*3), вы получаете верный ответ = 18. Как обычно, вы можете ввести формулу ={1;2;3}*3, и в строке формул нажать F9, чтобы увидеть, что Excel возвращает массив ={3;6;9}.

Кстати о массивах констант. Они состоят из фигурных скобок со значениями внутри. Элементы в одной строке и разных столбцах разделяются точкой с запятой. Для перехода к следующей строке используется двоеточие. Например, массив {1;2;3:4;5;6} соответствует:

Рис. 61. Синтаксис массива констант

Любопытно, что если вы попытаетесь умножить массив {1;2;3} на {3;3}, Excel не будет знать, что делать с третьей ячейкой во втором массиве. Поэтому промежуточное вычисление вернет СУММ(3;6;#Н/Д), и общий результат будет ошибочным #Н/Д.

Рис. 62. В случае массивов разных размеров формула может вернуть ошибку

Как описанное выше поведение влияет на динамические массивы?

Неявный оператор пересечения @ делает то, что уже было возможно и в старом Excel. Но оператор явно указывает на пересечение, что делает ваши действия более осмысленными. В старом Excel формулы массива работали только тогда, когда вы совершали какую-то магию. Например, Ctrl+Shift+Enter или СУММПРОИЗВ / МУМНОЖ. Последние позволяли отключить неявное пересечение. С появлением динамических массивов неявное пересечение отключено в Excel по умолчанию. Если всё же вам нужно использовать пересечение (ставшее явным) добавьте оператор @.

Почему Excel иногда добавляет оператор пересечения?

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

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

За те 15 месяцев, пока динамические массивы проходили бета-тестирование было много вопросов, почему Excel добавил @ к той или иной формуле. Джо Макдэйд вступал в долгую дискуссию о том, почему в определенные дни, когда Луна в такой-то фазе эта формула может вызвать неявное пересечение. Эти повторяющиеся дискуссии заставили меня осознать, что неявное пересечение в старом Excel происходило существенно чаще, чем я подозревал.

На рис. 64 формула в B2 не может принять вид =ИНДЕКС(B4:F15;0;2) или =ИНДЕКС(B4:F15;2;0).[2] Проверка данных в ячейках С1 и F1 предотвращает это. Но если кто-то введет 0 в A2, а затем скопирует A2 и вставит в F1, то =ИНДЕКС(B4:F15;2;0) вернет массив {2;4;8;16;32}. Поскольку формуле в В2 некуда разлиться, Excel вернет ошибку #ПЕРЕНОС! Неважно, что этого никогда не произойдет. Excel понял, что это может произойти, и добавил @ к формуле. Этот оператор подавит динамический диапазон, и выведет только одно значение в соответствии с неявным пересечением (ставшим в такой нотации явным).

Рис. 64. Джо и команда Calc рассматривают массу сценариев, в которых может возникнуть неявное пересечение

Ответы на вопросы в начале главы

Вопрос 1. Если в ячейку D8 вы введете =B4:B14, каков будет результат?

  • 2047, сумма значений B4:B14.
  • 1, значение первой ячейки в диапазоне.
  • 16, значение в той же строке диапазона.
  • Ошибка #ЗНАЧ! синтаксис формулы неверный.

Рис. 65. В старом Excel в результате неявного пересечения вы получите 16 в строке 8 и #ЗНАЧ! в строке 16

Вопрос 2. Если вы введете =B2:B14 в ячейку D16. Что вы получите? Ответ такой же, как и для вопроса 1? Или иной?

См. рис. 65. Ответ иной. Вы получите ошибку #ЗНАЧ! так как неявное пересечение происходит за пределами исходного массива.

Вопрос 3. Что вернет формула =СУММ(C2:E2*B5:B7)?

  • A. 6, потому что 2 умножить на 3 = 6.
  • B. 18, потому что каким-то волшебным образом выполнится 2*3 + 2*3 + 2*3 = 18.
  • C. 54, потому что вы только что приняли ЛСД.
  • D. Ошибка #ССЫЛКА! потому что такая запись недопустима.

Благодаря трансляции Excel расширит массив {2;2;2} до {2;2;2:2;2;2:2;2;2}, а массив {3:3:3} до {3;3;3:3;3;3:3;3;3}. У вас будет два массива 3х3, и их перемножение даст массив 3х3 в каждой ячейке которого будет шестерка. А девять по шесть вернет 54:

Рис. 66. Произведение двух зеленых массивов даст 54

Вопрос 4. Чему равно =СУММ(ДЛСТР(A4:A15))? Будут ли результаты разными, если:

  • ввести формулу и нажать Enter;
  • ввести формулу и нажать Ctrl+Shift+Enter;
  • ввести в ячейку С4 формулу =ДЛСТР(A4), скопировать ее вниз до С15, суммировать значения в диапазоне С4:С15.

Рис. 67. В старом Excel используйте Ctrl+Shift+Enter, чтобы предотвратить неявное пересечение

В старом Excel, если ввести формулу =СУММ(ДЛСТР($A$4:$A$15)) в ячейки диапазоне С4:С15 и нажать Enter, то, из-за неявного пересечения они вернут длину той же строки из диапазоне А4:А15. Если ввести =СУММ(ДЛСТР(A4:A15)) в любом месте листа и нажать Ctrl+Shift+Enter, формула вернет 113. ДЛСТР ожидает один аргумент, но подъем заставляет функцию вычислить длину строки 12 раз. Если поместить ДЛСТР(A4:A15) внутрь функции СУММ, эти 12 значений будут суммированы. Варианты b) и c) идентичны.

[1] Поскольку у меня на ПК установлен новый Excel, при вводе формулы =ДЛСТР($A$2:$A$7) в ячейку В2 он разольет массив на диапазон В2:В7. Я использую оператор неявного пересечения @ в аргументе функции, чтобы подавить работу функции динамического массива, и вывести только одно значение. – Здесь и далее прим. Багузина.

[2] Ноль во втором или третьем аргументе означает, что формула вернет массив, включающий весь столбец или всю строку диапазона B4:F15.

Пример как работать с формулой массива в таблице Excel

Как узнать, является ли данная формула формулой массива? Что вообще она означает?

На этапе создания формула (или также функция) сама по себе не является ни формулой массива, ни обычной формулой. Это вы определяете, как Excel должен истолковать формулу, которую вы вводите. То, что формула является формулой массива – это не столько особенность самой формулы, а скорее способом, которым программа Excel введенную формулу «обрабатывает». Подтверждение формулы с помощью сочетания клавиш «Ctrl + Shitf + Enter» – это является для Excelя командой на выполнение (обработку данных) как массив вычислений. Тогда он используется в качестве аргумента функции и возвращает в качестве результата вычислений таблицу (массив данных).

Примеры формул массива и отличие от обычных формул в Excel

Некоторые функции Excelя по умолчанию в качестве аргумента принимают диапазон ячеек (массив) и в результате возвращают одно значение. Отличными примерами являются функции СУММ, СЧЕТЕСЛИ, СРЗНАЧ и т.д. Для этих функций не имеет никакого значения, вводите ли вы их как функции массива или нет. Они и так обрабатывают таблицы, и найдут выход (сработают правильно) из любой ситуации. Вот такие маленькие Excel-евские приспособленцы.

К счастью, существуют другие функции, которые работают совершенно иначе, т.е. в зависимости от вашего решения относительно их принадлежности к «функциям массива» (иногда они вообще не хотят работать). Прекрасным примером является функция ЕСЛИ.



Когда формула является формулой массива, а когда обычной?

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

{23;-32;15;7} – это синтаксис массива значений в Excel. Он может быть использован в аргументах функций.

Диапазон ячеек A1:A4 – так же является массивом значений в Excel. Естественно так же используется в аргументах функций. Например сравним результаты вычислений двух формул: =СУММ(A1:A4) и =СУММ({23;-32;15;7}) – они идентичны:

Визуально формула массива находится так же внутри фигурных скобок, но они не должны быть введены вручную, а только лишь при помощи комбинации клавиш CTRL+SHIFT+Enter. Если ввести вручную фигурные скобки, то формула не будет выполнятся в массиве – это будет синтаксическая ошибка в Excel.

Формула массива (введенная с помощью сочитания CTRL+SHIFT+Enter) будет использоваться везде, где вы хотите, чтобы функция, которая обычно работает с отдельными значениями (ячейками), внезапно повела себя иначе и приняла в качестве аргумента и вернула в качестве результата массив значений (таблицу). Вернемся к уже упомянутой функции ЕСЛИ. В качестве аргумента она принимает логическое значение ИСТИНА или ЛОЖЬ. В классической форме:

=ЕСЛИ($A$1>0;»больше»;»меньше»)

Если значение в ячейке A1 больше нуля, в качестве аргумента функция получит значение ИСТИНА и в качестве результата вернет текстовую строку «больше». Однако, если бы вы хотели проверить несколько ячеек сразу и передать результат такой проверки другой функции, вы должны были бы использовать вышеуказанную формулу как формулу массива. Для этого при вводе нажмем сочитание клавиш CTRL+SHIFT+Enter, а не как обычно (просто Enter):

{=ЕСЛИ($A$1:$A$4>0;»больше»;»меньше»)}

В качестве аргумента функция принимает целый диапазон $A$1:$A$4. В результате проверки каждой ячейки диапазона в памяти компьютера создается таблица значений в массиве. Схематически таблицу можно отобразить так:

А так выглядят эти значения в массиве:

{ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА}

Например, чтобы прочитать этот массив и получить второе значение (сделать выборку значений) воспользуемся функцией:

=ИНДЕКС({ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА};2)

Тоже самое что и:

Затем создается другая таблица, значения которой зависят непосредственно от значений в первой таблице. Если элемент в первом массиве имеет значение ИСТИНА, во втором массиве он примет значение «больше». Если он имеет значение ЛОЖЬ, элемент во второй таблице примет значение «меньше». После этой операции первая таблица удаляется из памяти компьютера, и в конечном счете, функция возвращает массив {«больше», «меньше», «больше», «больше»}. Схематически вторую таблицу можно отобразить так:

Так же ее можно прочитать функцией:

=ИНДЕКС({«больше»;»меньше»;»больше»;»больше»};2)

В примере с функцией ЕСЛИ была введена формула массива только в одну ячейку, поэтому в результате получили только одно значение, соответствующее первому значению в таблице. Однако достаточно ввести формулу массива в диапазон ячеек, чтобы увидеть все значения массива результатов. Для этого выделяем диапазон из нескольких ячеек, нажимаем клавишу F2 (или заново вводим формулу вручную) и жмем CTRL+SHIFT+Enter.

В примере (рисунок ниже) видно, что таблица результатов содержит ровно четыре элемента, о которых я упоминал выше.

Примеры как использовать формулу массива в Excel

Это все хорошо, но возникают некоторые вопросы: «Зачем же нужна формула массива?» или «Как или где использовать формулу в массиве?», «Чем она лучше обычной формулы?».

Разумеется, массив, возвращаемый функцией ЕСЛИ, может передаваться далее на «обработку» в качестве аргумента для другой функции.

Пример. Представим, что вы хотели бы найти сумму ячеек B7:B10, но только тех, которые имеют значение больше нуля. Конечно же, вы можете использовать функцию СУММЕСЛИ, однако в нашем примере мы хотим сделать это только с помощью формулы массива. Суммируя значения ячеек нашего диапазона, необходимо будет как-то избавиться от значения «-32». Функции СУММ необходимо передать массив, в котором содержатся только значения больше нуля. Везде там, где значение меньше нуля, мы заменяем его на ноль, что, конечно же, не повлияет на результат. Как вы уже знаете, временную таблицу с соответствующими значениями вы можете получить, используя функцию ЕСЛИ. В конечном итоге соответствующая формула будет выглядеть так:

Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter. В результате проверки каждой ячейки диапазона $A$1:$A$4 (является ли значение больше нуля) в памяти компьютера создается массив {ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА}. Затем создается очередная таблица. Если элемент в первом массиве имеет значение ИСТИНА, то во второй таблице будет отображаться значение из соответствующей ячейки. Если он имеет значение ЛОЖЬ, то элемент во второй таблице примет значение 0. После этой операции первая таблица удаляется из памяти компьютера, и в конечном итоге функция ЕСЛИ возвращает массив {23; 0; 15; 7}. Затем эта таблица передается в качестве аргумента функции =СУММ({23; 0; 15; 7}), которая, согласно своему предназначению, возвращает сумму всех элементов в таблице. В нашем примере сумма равна 45. В завершении, посмотрите, что произойдет, если вы скажете Excelю обработать приведенную выше формулу не как формулу массива.

Ни одна из описанных выше таблиц, в этом случае, не будет создана. Только одна ячейка диапазона будет проверена (ячейка в той же строке, в которой находится функция). В нашем случае 15>0 значит, как первый аргумент функция ЕСЛИ получит логическое значение ИСТИНА. Затем ВЕСЬ диапазон A1:A4 будет передан функции СУММ, и в результате функция возвращает значение равное 13 (23-32+15+7). Если бы в ячейке вместо значения 15 было число меньше нуля, функция ЕСЛИ в качестве аргумента получила бы значение ЛОЖЬ, и, следовательно, только значение нуля было бы передано функции СУММ. Наша функция СУММ в результате также вернет значение равное нулю.

Как отличать формулу массива от обычной формулы

При нажатии клавиш CTRL+SHIFT+Enter для подтверждения ввода в строке формул будут отображены фигурные скобки по краям. Значит данная формула выполняется в массиве. Но что если еще на этапе создания неизвестно какой тип формул следует применять?

Правильное «распознавание», когда следует нажимать CTRL+SHIFT+Enter, а когда просто Enter полностью зависит от понимания того, как работают массивы в формулах. Когда вы это поймете, сможете сказать, что конкретную формулу следует вводить (подтверждать) сочетанием клавиш – CTRL+SHIFT+Enter.

Конечно же, не подтвержденная, а просто как формула также может возвращать КАКОЙ-ТО результат (в чем вы могли только что убедиться сами). Однако, если вы сможете прочитать формулу и понять механизм, то вы заметите, что такой результат является ОШИБОЧНЫМ. И поэтому для правильной работы формулы вам необходимо ее подтвердить «Ctr+Shift+Enter». Как и все, понимание и использование формул массива требует практики. Тем не менее, стоит некоторое время посвятить тому, чтобы во всем разобраться. Потому что формулы массива позволяют решить многие проблемы, которые на первый взгляд могут казаться неразрешимыми.

Примеры вычислений и анализа формул массива

Каким образом можно просматривать и проверять значения промежуточных результатов расчета, например, содержание массивов, созданных в памяти компьютера и используемых для выполнения последующих действий? Ничего сложного! Пример 1:

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

Нажмите клавишу F9 (или «Пересчет» в правом верхнем углу меню «Формулы»), и вы получите (в строке формулы) значения аргументов, которые используются для вычислений, как показано ниже:

— запись с использованием двоеточий означает, что мы имеем дело с элементами вертикального (столбикового) массива, элементы горизонтального (строкового) разделены стандартным символом — «;» (точкой с запятой).

Пример 2: Снова перейдите на ячейку с формулой массива, но на этот раз выделите первый аргумент функции целиком вместе со знаком сравнения «>» и значением критерия – «0»).

Нажмите кнопку F9, и вы получите массив результатов вычислений, как показано ниже:

То есть, созданный в памяти компьютера массив:

{ИСТИНА:ЛОЖЬ:ИСТИНА:ИСТИНА}

Пример 3: Выделите ячейку формулой массива где в функцию СУММ вложена функция ЕСЛИ. Затем в строке формул выберите весь аргумент функции СУММ (вместе с функцией ЕСЛИ):

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

То есть, созданный в памяти компьютера массив:

{23\0\15\7}

Пример 4: Просто перейдите на ячейку с формулой B1 и выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу»

После чего нажмите на кнопку «Вычислить»:

В результате ссылка на диапазон ячеек в аргументе вложенной функции ЕСЛИ разложилась на массив значений. Снова нажмите на кнопку «Вычислить»:

Мы получили массив значений теперь уже для функции СУММ. Такой же, как и в примере 3.

Читайте также: Работа с массивами функций в Excel.

Часто неопытные пользователи Excel возмущаются, что формула не работает. В конце концов оказалось, как легко догадаться, формулу в массиве вводили как обычную (просто Enter). Речь идет не о недоразумении, которое произошло, а о том факте, что у этих пользователей возникает вопрос: как избегать таких ошибок? Поэтому важно сразу во всем разобраться, чтобы в дальнейшем больше не задавать таких вопросов.

Control Shift Введите ярлык Excel (CSE) и формулы массива

от Ilker | 19 ноября 2019 г. | Советы и рекомендации по Excel

Control Shift Enter Сочетание клавиш Excel, известное как CSE, представляет собой комбинацию клавиш, которую можно использовать для применения функций массива. Аббревиатура CSE также используется для формул массива, которые вводятся с помощью комбинации клавиш Control + Shift + Enter, когда ввод формулы. Формулы массива немного отличаются от обычных формул, которые вводятся нажатием только клавиши Enter. В этом руководстве мы покажем вам, как использовать сочетание клавиш Control Shift и Enter Excel (CSE).

Загрузить рабочую книгу

Формулы массива (CSE)

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

Несколько действий в одной ячейке

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

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

Используя сочетание клавиш Control Shift Enter Excel, вы можете выполнить эту операцию в одной ячейке! Вместо использования вспомогательной ссылки на диапазон E3:E6 в функции SUM вы можете использовать ссылки диапазонов в функции и оценивать каждую операцию вместе.

Как видно из приведенного выше примера, формула в ячейке h4 возвращает то же значение, что и в обычном методе. CSE в Excel устраняет необходимость во вспомогательном столбце. Давайте посмотрим, как это работает.

{=СУММ(B3:B6/C3:C6)}

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

Чтобы вычислить такую ​​формулу,

  1. выберите ячейку, содержащую формулу массива
  2. в строке формул выберите часть «B3:B6/C3:C6»
  3. и нажмите клавишу F9

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

Заполнение нескольких ячеек

Если вы хотите заполнить массив значений в ячейках, вы также можете использовать формулы массива. В первом примере использовалась функция SUM для суммирования значений массива. Функция СУММ возвращает только одно значение. Давайте теперь посмотрим, как бы работала формула, если бы не использовалась функция СУММ. Если вы исключите функцию СУММ, вы получите что-то вроде следующего:

{=B3:B6/C3:C6}

Если вы выберете одну ячейку и введете приведенную выше формулу, вы получите 0,20 , что равно 1/5 . Это может показаться неправильным, но если вы выберете диапазон из 4 строк и 1 столбца и введете формулу как функцию массива, вы увидите, что все ячейки заполнены правильно.

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

Оценка массивов без CSE

Формулы массива — не единственное решение для упрощения сложных операций. В частности, в этом примере предполагается, что вы хотите вернуть одно значение, а функция Excel СУММПРОИЗВ — это мощная формула, которая может оценивать массивы как есть. Вот формула, использующая наш предыдущий пример:

=СУММПРОИЗВ(B3:B6/C3:C6)

Чтобы узнать больше о формуле СУММПРОИЗВ, см. Функция: СУММПРОИЗВ.

Функции, возвращающие массивы

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

  • ТРАНСП
  • СМЕЩЕНИЕ
  • ИНДЕКС
  • ЛИНЕЙН
  • ЛОГЕСТ

Обратите внимание, что функцию ТРАНСП можно использовать только с комбинацией CSE. В противном случае вы получите #ЗНАЧ! Ошибка . Остальные функции возвращают первые значения в возвращаемом массиве.

Что это такое и как его использовать

Если вы пользователь Excel, возможно, вы сталкивались с термином «Ctrl Shift Enter» при работе с формулами. Но что именно это означает и как вы можете использовать это в своих интересах? В этой статье мы более подробно рассмотрим это сочетание клавиш и рассмотрим некоторые распространенные сценарии, в которых оно пригодится.

Содержание

  • Что такое Ctrl Shift Enter в Excel?
  • Как использовать Ctrl Shift Enter в формулах массива
  • Расширенные приемы с помощью Ctrl Shift Enter
  • Примеры Ctrl Shift Enter в реальных ситуациях
  • Распространенные ошибки и советы по их устранению
  • Советы по освоению Ctrl Shift Enter
  • Заключение
  • Часто задаваемые вопросы

Что такое Ctrl Shift Enter в Excel?

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

Чтобы ввести формулу массива в Excel, вам нужно использовать сочетание клавиш Ctrl Shift Enter вместо обычной клавиши Enter. Это сообщает Excel, что вы вводите формулу массива, а не обычную формулу.

Как использовать Ctrl Shift Enter в формулах массива

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

Вот пример формулы массива, в которой используется Ctrl Shift Enter:

{=СУММ(ЕСЛИ(A1:A5="Яблоки",B1:B5,0))}

Эта формула вычисляет сумму все значения в диапазоне B1:B5, но только для ячеек, где соответствующая ячейка в диапазоне A1:A5 равна «Яблоки». Обратите внимание на фигурные скобки вокруг формулы — это означает, что это формула массива.

Расширенные методы с Ctrl Shift Enter

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

Вот пример формулы вложенного массива, в которой используется Ctrl Shift Enter:

{=MAX(IF(A1:A5="Яблоки",IF(B1:B5>5,C1:C5,0),0) )}

Эта формула сначала проверяет, равна ли каждая ячейка в диапазоне A1:A5 яблокам. Если да, то проверяется, больше ли соответствующая ячейка в диапазоне B1:B5, чем 5. Если да, то возвращается значение в соответствующей ячейке в диапазоне C1:C5. Наконец, он вычисляет максимальное значение всех возвращаемых значений.

Примеры Ctrl Shift Enter в реальных ситуациях

Ctrl Shift Enter можно использовать в различных реальных ситуациях, например, при создании финансовых моделей или анализе больших наборов данных. Вот несколько примеров:

  • Расчет средней доходности портфеля акций на основе исторических данных
  • Поиск первых 10 клиентов по доходам в наборе данных о продажах
  • Расчет общей себестоимости реализованных товаров для предприятия-производителя на основе производственных данных

Распространенные ошибки и советы по устранению неполадок

Одной из распространенных ошибок при использовании Ctrl Shift Enter является забывание поставить фигурные скобки в начале и в конце формулы. Это приведет к тому, что формула вернет ошибку.

Если вы столкнулись с ошибкой при использовании Ctrl Shift Enter, попробуйте проверить синтаксис вашей формулы и убедитесь, что вы включили все необходимые аргументы и скобки. Также рекомендуется перепроверить, что вы выбрали правильный диапазон ячеек и что ваша формула правильно структурирована.