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

Foodband

Создание и использование именованных констант

Описание проблемы

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

Минусы данного способа очевидны:

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

Решение

Избавится от этого можно путем создания именованных констант. Например, создадим константу под именем НДС. Для этого:

  1. Перейдите на вкладку Формулы. Найдите группу Определенные имена и выберите команду Присвоить имя. Откроется диалоговое окно Создание имени.

  1. Введите имя константы в поле Имя. В нашем случае это НДС.
  2. В поле Область следует указать значение Книга. В этом случае выбранная переменная будет доступна на всех листай файла Excel. Если необходимо, чтобы константа работала только на конкретном листе, следует выбрать название нужного листа.
  3. В поле Примечание можно указать любой текст при необходимости.
  4. В поле Диапазон введите значение константы =18% не ссылаясь на ячейки и нажмите кнопку ОК.

Создание именованной константы в Excel

Теперь на любом листе формула =НДС будет выдавать одинаковый результат 0,18. Теперь давайте используем нашу переменную и посчитаем, например, стоимость товара без НДС (пусть стоимость с НДС будет 18000). Для этого введем в ячейку следующую формулу =18000/НДС. В итоге ячейка примет значение 10 000. Согласитесь, что формула стала намного понятней, нежели вместо НДС была указана ссылка на некую ячейку (да еще на другом листе).

Вставка именованной константыЧтобы вставить именованную константу в формулу не обязательно помнить ее точный синтаксис. Для удобства клавишей F3 можно вызвать список констант и выбрать ее из формы Вставка имени. Также это окно можно вызвать перейдя на вкладку Формулы -> Определенные имена -> Использовать в формуле.

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

Значения констант можно менять по ходу работы. Для этого необходимо открыть окно диспетчера имен Формулы -> Определенные имена -> Диспетчер имен.

Диспетчер имен в Excel

Выберите переменную и нажмите кнопку Изменить. Введите новое значение переменной, закройте диспетчер имен. Excel заново пересчитает все все значения связанные с данной константой.

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

history 8 апреля 2013 г.
    Группы статей

  • Имена
  • Формулы массива
  • Текстовые последовательности

Массив значений (или константа массива или массив констант) – это совокупность чисел или текстовых значений, которую можно использовать в формулах массива . Константы массива необходимо вводить в определенном формате, например, для чисел <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 .

Foodband

Теперь попробуем удалить один элемент массива, например из ячейки 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 имеется список числовых значений.

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

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

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

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

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

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

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

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

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

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

Foodband

Ссылка на основную публикацию