ГлавнаяРазноеCtrl shift enter в excel

Формула массива excel: не нужно «ctrl-shift-enter»? Ctrl shift enter в excel


Формулы массива в MS EXCEL. Знакомство. Примеры и методы

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

Без формул массива (array formulas) можно обойтись, т.к. это просто сокращенная запись группы однотипных формул. Однако, у формул массива есть серьезное преимущество: одна такая формула может заменить один или несколько столбцов с обычными формулами.

Например, можно найти сумму квадратов значений из диапазона А2:A12, просто записав в ячейке B14 формулу =СУММ(A2:A12^2). Для сравнения: чтобы найти сумму квадратов, используя обычные формулы, нам потребуется дополнительный столбец для вычисления квадратов значений и одна ячейка для их суммирования (см. файл примера или диапазон B2:B13 на рисунке ниже).

В отличие от ввода обычных формул, после ввода формулы массива нужно нажать вместо ENTER комбинацию клавиш CTRL+SHIFT+ENTER (поэтому, иногда, формулы массива также называются формулами CSE - это первые буквы от названия клавиш, используемых для ввода Ctrl, Shift, Enter). После этого формула будет обрамлена в фигурные скобки { } (их не вводят с клавиатуры, они автоматически появляются после нажатия CTRL+SHIFT+ENTER). Это обрамление показано на рисунке выше (см. Строку формул).

Если бы мы нажали просто ENTER, то получили бы сообщение об ошибке #ЗНАЧ!, возникающую при использовании неверного типа аргумента функции, т.к.  функция СУММ() принимает в качестве аргумента только диапазон ячеек (или формулу, результатом вычисления которой является диапазон, или константы). В нашем случае мы в качестве аргумента ввели не диапазон, а некое выражение, которое еще нужно вычислить перед суммированием, поэтому и получили ошибку.

Чтобы глубже понять формулы массива проведем эксперимент:

  • выделим ячейку B13, содержащую обычную формулу =СУММ($B$2:$B$12);
  • в Cтроке формул выделим аргумент функции СУММ(), т.е. $B$2:$B$12;
  • нажмем клавишу F9, т.е. вычислим, выделенную часть формулы;
  • получим {1:4:9:16:25:36:49:64:81:100:121} – массив квадратов значений из столбца В. Массив – это просто набор неких элементов (значений).

Т.е. обычная функция СУММ() в качестве аргумента получила некий массив (или точнее ссылку на него).Теперь проведем тот же эксперимент с формулой массива:

  • выделим ячейку, содержащую формулу массива =СУММ($A$2:$A$12^2);
  • в строке формул выделим аргумент функции СУММ(), т.е. $A$2:$A$12^2;
  • нажмем клавишу F9, т.е. вычислим, выделенную часть формулы;
  • получим {1:4:9:16:25:36:49:64:81:100:121} – тот же массив, что и в первом случае.

Т.е. нажатие CTRL+SHIFT+ENTER заставило EXCEL перед суммированием произвести промежуточные вычисления с диапазоном ячеек (с массивом содержащихся в нем  значений). Для самой функции СУММ() ничего не изменилось – она получила тот же массив, только предварительно вычисленный, а не прямо из диапазона ячеек, как в случае с обычной формулой. Понятно, что вместо функции СУММ() в формуле массива может быть использована любая другая функция MS EXCEL: СРЗНАЧ(), МАКС(), НАИБОЛЬШИЙ() и т.п.

Вышеприведенный пример иллюстрирует использование функции массива возвращающей единственное значение, т.е. результат может быть выведен в одной ячейке. Это достигается использованием функций способных «свернуть» вычисленный массив до одного значения (СУММ(), СРЗНАЧ(), МАКС()). Примеры таких функций массива приведены в статье Формулы массива, возвращающие одно значение.

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

Преимущества и недостатки формул массива рассмотрены в одноименной статье Формулы массива. Преимущества и недостатки.

В файле примера также приведено решение данной задачи функцией СУММПРОИЗВ(), которая зачастую не требует введения ее как формулы массива:=СУММПРОИЗВ($A$2:$A$12^2)

Здесь, при вводе формулы СУММПРОИЗВ() нажимать CTRL+SHIFT+ENTER необязательно.

Ссылки на статьи о формулах массива на сайте Microsoft:

1. ]]>Основы формул массива]]> 

2. ]]>Основы формул массива (на английском)]]> из книги авторов Colin Wilcox и John Walkenbach

ПРИМЕЧАНИЕ:При создании Именованных формул и правил Условного форматирования формулы массива нельзя ввести нажимая CTRL+SHIFT+ENTER. Эти формулы вводятся только в ячейки листа. Однако, если формуле массива присвоить Имя, то EXCEL «сообразит», что нужно с ней нужно делать. Например, если формуле =СУММ($A$2:$A$12^2) присвоить имя Сумма_квадратов, а затем в ячейке указать =Сумма_квадратов, то получим правильный результат.

excel2.ru

Редактирование формул массива в Excel

В прошлых уроках мы разобрали основные понятия и сведения касаемо массивов в Excel. В этом уроке мы продолжим изучение формул массива, но с большим уклоном на их практическое применение. Итак, как же изменить уже существующую формулу массива в Excel?

Правила редактирования формул массива

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

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

  1. Нельзя изменять содержимое одной ячейки, содержащей формулу массива. Но к каждой ячейке можно применить свое форматирование.
  2. Нельзя удалять ячейки, которые входят в формулу массива. Можно удалить только весь массив.
  3. Нельзя перемещать ячейки, которые входят в формулу массива. Зато можно переместить весь массив.
  4. Нельзя вставлять новые ячейки, в том числе строки и столбцы, в диапазон массива.
  5. Нельзя использовать многоячеечные формулы массива в таблицах, созданных с помощью команды Таблица.

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

Выделение массива в Excel

Если необходимо изменить формулу массива, то первое, что нужно сделать – это выделить диапазон, в котором содержится массив. В Excel существует, как минимум, 3 способа сделать это:

  1. Выделить диапазон массива вручную, т.е. с помощью мыши. Это самый простой, но в ряде случаев абсолютно непригодный способ.
  2. С помощью диалогового окна Выделить группу ячеек. Для этого выделите любую ячейку, которая принадлежит массиву:А затем на вкладке Главная из раскрывающегося списка Найти и выделить выберите пункт Выделить группу ячеек.

    Откроется диалоговое окно Выделить группу ячеек. Установите переключатель на пункт текущий массив и нажмите ОК.

    Текущий массив будет выделен:

  3. При помощи комбинации клавиш Ctrl+/. Для этого выберите любую ячейку массива и нажмите комбинацию.

Как удалить формулу массива

Самое простое, что Вы можете сделать с массивом в Excel – это удалить его. Для этого достаточно выделить нужный массив и нажать клавишу Delete.

Как отредактировать формулу массива

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

Чтобы отредактировать формулу массива, выполните следующие действия:

  1. Выделите диапазон массива любым из известных Вам способов. В нашем случае это диапазон C1:C12.
  2. Перейдите в режим редактирования формулы, для этого щелкните по строке формул или нажмите клавишу F2. Excel удалит фигурные скобки вокруг формулы массива.
  3. Внесите необходимые корректировки в формулу:
  4. А затем нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Формула будет отредактирована.

Изменение размеров формулы массива

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

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

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

Существует несколько подходов к редактированию размеров массива, которые, возможно, Вам пригодятся. Подходы приведены в данном уроке.

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel

Documents войти Загрузить ×
  1. No category
advertisement advertisement
Related documents
Excel. Вычисление стандартного отклонения для данных с тенденцией
1 ( ) sin( ), 0
Задание1.
Глава 12. Выборка из диапазона дат с помощью критерия в...
На вход программе подаются сведения о номерах школ
ОТ РЕДАКТОРА НОВАЯ ФОРМУЛА РУССКОЙ
Приложение 2 1. Найти сумму положительных элементов в массиве.
Задача 10-2. В таблице даны теплоты образования Q
Решение задачи получения из заданного целочисленного
Вопросы к итоговому междисциплинарному экзамену
Скачать advertisement StudyDoc © 2018 DMCA / GDPR Пожаловаться

studydoc.ru

не нужно «ctrl-shift-enter»? MS Excel онлайн

Формула массива должна копировать и вставлять в полном порядке и сохранять ее массив до тех пор, пока вы скопируете ячейку, а не скопируете формулу в строке формул. Он также копирует штраф, если вы перетаскиваете или заполняете ячейку вниз или поперек. Возможно, если вы хотите сохранить диапазон B2: B100 при перетаскивании, используйте $ B $ 2: $ B $ 100, чтобы остановить его увеличение.

F4 – это хороший короткий вырез, который может помочь здесь. Наведите курсор на B2: B100 и нажмите F4.

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

Отрицательных:

-Он включает VBA

-Он очистит стек Undo / буфер обмена

-Это потенциально более запутанно

Временное решение

Формулы тегов, которые должны быть формулой массива с термином, который не влияет на результат. Например, (0*0)+ . Этот термин можно затем проверить и сделать всегда, чтобы формула массива через код.

поэтому ваша формула выше {=(0*0)+MATCH(TRUE,(B2:B100}>0,0)}

Затем в THISWORKBOOK

введите этот код

Option Explicit Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim cell As Range For Each cell In Target If Target.Cells.Count = 1 Then If InStr(1, Target.Formula, "(0*0)+", vbTextCompare) And _ Target.HasArray = False Then Target.FormulaArray = Target.Formula End If End If Next cell End Sub

когда ячейка изменена, код будет проверять термин (0 * 0) + и назначать его как формулу массива, если он уже не один.

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

=MATCH(TRUE,INDEX(B2:B100>0,0),0)

excel.bilee.com