Содержание
Создание и использование именованных констант
Описание проблемы
Как правило, мы часто пользуемся различными константами. Например, ставка НДС, налог на прибыль, различные математические константы и прочее. Как мы поступаем, чтобы их использовать? Мы берем отдельный лист, вносим туда эти константы и ссылаемся на них в лучшем случае закрепляя при этом ссылки на эти ячейки.
Минусы данного способа очевидны:
- Сложность набора формул, так как приходится делать ссылку на ячейку в другом листе.
- Сложность восприятия и чтения таких формул. В случае проверки приходится проходить по ссылкам прыгая с листа на лист.
- Большое количество констант загромождает лист и он также становится трудно читаем.
Решение
Избавится от этого можно путем создания именованных констант. Например, создадим константу под именем НДС. Для этого:
- Перейдите на вкладку Формулы. Найдите группу Определенные имена и выберите команду Присвоить имя. Откроется диалоговое окно Создание имени.
- Введите имя константы в поле Имя. В нашем случае это НДС.
- В поле Область следует указать значение Книга. В этом случае выбранная переменная будет доступна на всех листай файла Excel. Если необходимо, чтобы константа работала только на конкретном листе, следует выбрать название нужного листа.
- В поле Примечание можно указать любой текст при необходимости.
- В поле Диапазон введите значение константы =18% не ссылаясь на ячейки и нажмите кнопку ОК.
Теперь на любом листе формула =НДС будет выдавать одинаковый результат 0,18. Теперь давайте используем нашу переменную и посчитаем, например, стоимость товара без НДС (пусть стоимость с НДС будет 18000). Для этого введем в ячейку следующую формулу =18000/НДС. В итоге ячейка примет значение 10 000. Согласитесь, что формула стала намного понятней, нежели вместо НДС была указана ссылка на некую ячейку (да еще на другом листе).
Чтобы вставить именованную константу в формулу не обязательно помнить ее точный синтаксис. Для удобства клавишей F3 можно вызвать список констант и выбрать ее из формы Вставка имени. Также это окно можно вызвать перейдя на вкладку Формулы -> Определенные имена -> Использовать в формуле.
В качестве именованных переменных можно задавать не только числовые значения, но также и текстовые, даты.
Значения констант можно менять по ходу работы. Для этого необходимо открыть окно диспетчера имен Формулы -> Определенные имена -> Диспетчер имен.
Выберите переменную и нажмите кнопку Изменить. Введите новое значение переменной, закройте диспетчер имен. 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 .
Теперь попробуем удалить один элемент массива, например из ячейки 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 на английском языке ]]> .