Массивы констант в EXCEL

Массив значений (либо константа массива либо массив констант) – это совокупа чисел либо текстовых значений, которую можно применять в формулах массива . Константы массива нужно вводить в определенном формате, к примеру, для чисел <1:2:3:4:5>либо для текстовых значений <"Север":"ЮГ":"Восток":"Запад">.

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

Создание одномерного массива констант

Перед созданием Массива констант поглядим повнимательнее на спектр ячеек, содержащий обыденные значения (см. Файл примера )

в спектр ячеек A1:A5 введите, к примеру, 5 поочередных чисел от 1 до 5.

в ячейке B1 введите формулу =A1:A5 ;

в Строке формул выделите A1:A5 и нажмите кнопку F9 ;
получим некоторую запись <1:2:3:4:5>, представляющую собой набор значений из спектра A1:A5

Этот набор значений, как и формулы массива , обрамлен в фигурные скобки, сами значения разбиты двоеточиями. Если б значения были расположены в строке (в спектре A1:E1 ), а не в столбце, то значения могли быть разбиты точкой с запятой <1;2;3;4;5>.

Сделаем константу массива в ячейке B2 . Для этого введем в ячейку выражение = <1:2:3:4:5>и нажмем ENTER . Массив значений не заключается в скобки автоматом, как формулы массива опосля нажатия CTRL+SHIFT+ENTER . Это нужно созодать вручную. В ячейке отразится лишь 1-ое значение массива, т.е. 1.

Обычно массив значений не вводят в одну ячейку, т.к. в этом случае нереально вынуть отдельные значения. Чтоб показать все значения нашего массива значений необходимо выделить 5 ячеек в столбце (к примеру, B1:B5 ), в Строке формул ввести выражение = <1:2:3:4:5>и надавить CTRL+SHIFT+ENTER .

Сейчас попробуем удалить один элемент массива, к примеру из ячейки B 3 . Получим предупреждение « Недозволено изменить часть массива » — это определенного вида защита массива.

Чтоб избежать мучительного ввода поочередных чисел для вертикального массива констант можно пользоваться формулой =СТРОКА(1:5) . Записав ее хоть какой пустой ячейке, выделите ее в строке формул и нажмите F9 , а потом нажмите ENTER . Получите массив констант <1:2:3:4:5>. Скопируйте содержимое ячейки в буфер обмена ( CTRL+C ), потом выделите вертикальный спектр ячеек, соответственный размерности массива, вставьте в активную ячейку содержимое Буфера обмена и нажмите CTRL+SHIFT+ENTER.

Чтоб избежать мучительного ввода поочередных чисел для горизонтального массива констант используйте формулу =ТРАНСП(СТРОКА(1:5)) либо =СТОЛБЕЦ(A:E) . Получите массив констант = <1;2;3;4;5>. Значения массива будут разбиты точкой с запятой.

Применение массива констант

А. Умножение векторов (столбец на столбец, строчку на строчку) При помощи формулы массива умножим столбец значений ( B 2: B 6 ) на массив констант <1:2:3:4:5>и просуммируем. Массив констант записан в «вертикальном» виде с внедрением двоеточия, т.е. также представляет собой столбец. Размерности столбца и массива должны совпадать.

Опосля ввода формулы нужно надавить CTRL+SHIFT+ ENTER .

Формула массива поначалу выполнит поэлементное умножение значений из столбца и констант из массива. Эквивалентом данной формулы является последующее обыденное выражение:

=СУММ(B2*1;B3*2; B4*3; B5*4; B6*5)

В последнюю очередь производится сложение значений с помощью функции СУММ() .

Б. Проверка значений Проверим, равно ли значение в ячейке А1 одному из определенных значений: 4, 6 либо 9.

Опосля ввода формулы нет необходимости жать CTRL+SHIFT+ENTER . Таковая запись может значительно уменьшить время сотворения формулы по сопоставлению с внедрением вложенных функций ЕСЛИ() .

Именование массива констант

Массиву констант можно присвоить Имя . Обычно так поступают с константами, образующими группу однотипных значений, к примеру последовательности с конечным количеством частей = <1:2:3>.

Чтоб присвоить массиву констант имя нужно создать последующее:

на вкладке Формулы в группе Определенные имена изберите команду Присвоить имя .
В поле Имя введите Массив123 .
В поле Спектр введите массив констант (не забудьте ввести скобки вручную), к примеру <1:2:3>;
Нажмите клавишу ОК.

Пример, найдем сумму 3-х больших значений , записав формулу =СУММПРОИЗВ(НАИБОЛЬШИЙ(A1:A10;Массив123)) .

Предполагается, что в спектре A1:A10 имеется перечень числовых значений.

Создание двумерного массива констант

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

выделите в книжке спектр ячеек из 4 столбцов и 3-х строк ( A1:D3 ).
в активной ячейке ( А1 ), в Cтроке формул введите выражение =
нажмите сочетание кнопок CTRL+SHIFT+ENTER

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

Применение двумерного массива констант

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

Присвоим Имя Месяцы двумерному массиву:

Чтоб избежать мучительного ввода этого массива сделайте два столбца, один с числами от 1 до 12, иной с наименованиями месяцев (см. статью Текстовые последовательности ). Потом, в Строке формул введите ссылку на этот спектр, нажмите F9 и скопируйте в Буфер обмена .

Сейчас записав формулу =ВПР(A2;Месяцы;2) , где в ячейке A2 — номер месяца, получим хотимый итог.

Работа с массивами функций в Excel

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

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

Виды массивов функций Excel

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

Зависимо от расположения частей различают массивы:

одномерные (данные находятся в ОДНОЙ строке либо в ОДНОМ столбце);
двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

горизонтальными (данные – в строке);
вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сходу несколько листов (это сотки и тыщи данных).

Формула массива – дозволяет обработать данные из этого массива. Она может возвращать одно значение или давать в итоге массив (набор) значений.

При помощи формул массива реально:

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

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

Синтаксис формулы массива

Используем формулу массива с спектром ячеек и с отдельной ячейкой. В первом случае найдем промежные итоги для столбца «К оплате (расчёту за купленный товар или полученную услугу)». Во 2-м – итоговую сумму коммунальных платежей.

Выделяем спектр Е3:Е8.
В строчку формул вводим последующую формулу: =C3:C8*D3:D8.
Жмем сразу клавиши: Ctrl + Shift + Enter. Промежные итоги посчитаны:

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

Если попробовать изменить данные в какой-нибудь ячейке столбца «К оплате (расчёту за купленный товар или полученную услугу)» — ничего не выйдет. Формула в массиве защищает значения спектра от конфигураций. На дисплее возникает соответственная запись:

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

Выделяем ячейку Е9 (напротив «Итого»).
Вводим формулу вида: =СУММ(C3:C8*D3:D8).
Жмем сочетание кнопок: Ctrl + Shift + Enter. Итог:

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

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

Разглядим ее синтаксис:

Функции работы с массивами Excel

Представим, в последующем месяце планируется повышение коммунальных платежей на 10%. Если мы введем обыденную формулу для итога =СУММ((C3:C8*D3:D8)+10%), то навряд ли получим ожидаемый итог. Нам необходимо, чтоб любой аргумент возрос на 10%. Чтоб программка сообразила это, мы используем функцию как массив.

Поглядим, как работает оператор «И» в функции массива . Нам необходимо выяснить, сколько мы платим за воду, жаркую и прохладную. Функция: . Итого – 346 руб.
Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате (расчёту за купленный товар или полученную услугу) в порядке возрастания. Для перечня отсортированных данных сделаем спектр. Выделим его. В строке формул вводим . Нажимаем сочетание Ctrl + Shift + Enter.
Транспонированная матрица. Особая функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сходу несколько значений. Конвертирует горизонтальную матрицу в вертикальную и напротив. Выделяем спектр ячеек, где количество строк = числу столбцов в таблице с начальными данными. А количество столбцов = числу строк в начальном массиве. Вводим формулу: . Выходит «перевернутый» массив данных.
Поиск среднего значения без учета нулей. Если мы воспользуемся обычной функцией «СРЗНАЧ», то получим в итоге «0». И это будет верно. Потому вставляем в формулу доп условие: 0;A1:A8))’ >. Получаем:

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

Как изменить часть массива в excel

Встречали такую ошибку «Недозволено изменять часть массива»? Мне вот за крайнее время пару раз попадался файл, в котором недозволено было удалить определенные строчки либо столбцы, взамен выползает ошибка как на рисунке

Недозволено изменять часть массива — уберите все объединение ячеек в файле

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

Чтоб удалить все объединение ячеек, сделайте обыкновенные деяния — выделите всю таблицу на листе, нажав на клавишу в верхнем левом углу

Рекомендую выбирать конкретно эту клавишу, а не выделять вручную, т.к. может быть у вас есть объединения в невидимой части таблицы.

Потом перебегаем Основная — блок Сглаживание — Клавишу соединить ячейки, если она активна как на картинке, означает объединения есть — нажимаем на нее, обязано посодействовать.

2. Употребляется формула массива

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

Включаем в Меню формулы видимость всех формул в модуле зависимости формул

И отыскиваете, где формулы помещены в фигурные скобки <>. Как к примеру:

3. Ячейка ссылает на группу ячеек с формулой массива

Быстрее всего это глюк Excel 2007 и версий ниже. Т.е. есть формула массива, она корректно работает и так дальше. Но вы ссылаетесь на 2 и наиболее ячеек из этого массива, чтоб посчитать к примеру сумму.

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

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

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

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

Виды массивов функций Excel

Зависимо от расположения частей различают массивы:

одномерные (данные находятся в ОДНОЙ строке либо в ОДНОМ столбце);
двумерные (НЕСКОЛЬКО строк и столбцов, матрица).

Одномерные массивы бывают:

горизонтальными (данные – в строке);
вертикальными (данные – в столбце).

Примечание. Двумерные массивы Excel могут занимать сходу несколько листов (это сотки и тыщи данных).

При помощи формул массива реально:

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

Синтаксис формулы массива

Выделяем спектр Е3:Е8.
В строчку формул вводим последующую формулу: =C3:C8*D3:D8.
Жмем сразу клавиши: Ctrl + Shift + Enter. Промежные итоги посчитаны:

Выделяем ячейку Е9 (напротив «Итого»).
Вводим формулу вида: =СУММ(C3:C8*D3:D8).
Жмем сочетание кнопок: Ctrl + Shift + Enter. Итог:

Разглядим ее синтаксис:

Функции работы с массивами Excel

Поглядим, как работает оператор «И» в функции массива . Нам необходимо выяснить, сколько мы платим за воду, жаркую и прохладную. Функция: . Итого – 346 руб.
Функция «Сортировки» в формуле массива. Отсортируем суммы к оплате (расчёту за купленный товар или полученную услугу) в порядке возрастания. Для перечня отсортированных данных сделаем спектр. Выделим его. В строке формул вводим . Нажимаем сочетание Ctrl + Shift + Enter.
Транспонированная матрица. Особая функция Excel для работы с двумерными массивами. Функция «ТРАНСП» возвращает сходу несколько значений. Конвертирует горизонтальную матрицу в вертикальную и напротив. Выделяем спектр ячеек, где количество строк = числу столбцов в таблице с начальными данными. А количество столбцов = числу строк в начальном массиве. Вводим формулу: . Выходит «перевернутый» массив данных.
Поиск среднего значения без учета нулей. Если мы воспользуемся обычной функцией «СРЗНАЧ», то получим в итоге «0». И это будет верно. Потому вставляем в формулу доп условие: 0;A1:A8))’ >. Получаем:

На этом шаге мы разглядим ввод и редактирование формул массива, выделение массива.

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

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

Редактирование формулы массива
Если формула массива помещена в несколько ячеек, то нужно отредактировать все ячейки спектра как одну ячейку. Недозволено изменять лишь один элемент, содержщий формулу массива! Если Вы попытаетесь создать это, Excel выдаст сообщение, показанное на рисунке 1.

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

Ниже перечислены правила, относящиеся к формулам массивов. При нарушении хотя бы 1-го из их Excel выдаст предупреждение.

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

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

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

Перейдите к одной из ячеек спектра массива. Выберете команду Правка | Перейти либо нажмите кнопку F5 , в открывшемся диалоговом окне Переход щелкните на кнопочке Выделить и в последующем диалоговом окне Выделение группы ячеек выберете опцию Текущий массив . Щелкните на кнопочке OK , чтоб закрыть диалоговые окна.
Перейдите к одной из ячеек спектра массива и нажмите комбинацию кнопок Ctrl + / , чтоб выделить весь массив.

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

Интересно почитать: Vba excel как закрыть excel