Как сделать ячейку константой в excel?

Массивы констант в MS 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.

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

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

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

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

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

А. Умножение векторов (столбец на столбец, строку на строку)
С помощью формулы массива умножим столбец значений (B2:B6) на массив констант <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 имеется список числовых значений.

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

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

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

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

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

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

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

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

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

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

Массивы констант в MS 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.

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

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

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

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

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

А. Умножение векторов (столбец на столбец, строку на строку)
С помощью формулы массива умножим столбец значений (B2:B6) на массив констант <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 имеется список числовых значений.

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

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

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

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

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

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

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

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

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

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

Читать еще:  Как сделать формат в excel?
Ссылка на основную публикацию
Adblock
detector