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

На рисунке ниже приведен список студентов, которые получили определенные оценки:

Наша задача перевести оценку из числового вида в ее словесное описание и вывести соответствующие значения в диапазоне C2:C7. В данном случае создавать отдельную табличку для хранения текстового описания оценок не имеет смысла, поэтому выгоднее создать следующий массив констант:

={«»;»Неудовл.»;»Удовл.»;»Хорошо»;»Отлино»}

В данном случае первый элемент массива содержит пустую строку, поскольку предполагается, что оценки 1 быть не может.

Тогда формула, возвращающая нужный нам результат, будет выглядеть следующим образом:

В этом примере функция ИНДЕКС возвращает значение элемента из массива констант, положение которого задано порядковым номером (оценкой).

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

Конечно же, мы в силах скопировать данную формулу в остальные ячейки и получить нужный нам результат:

Но грамотнее будет использовать многоячеечную формулу массива. Выглядеть она будет следующим образом:

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

Не забывайте указывать знак равенства в поле Диапазон, иначе Excel воспримет массив как текстовую строку.

Теперь формула выглядит менее пугающей:

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

РЕДАКТИРОВАНИЕ ФОРМУЛ МАССИВА В EXCEL

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

ПРАВИЛА РЕДАКТИРОВАНИЯ ФОРМУЛ МАССИВА

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

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

1. Нельзя изменять содержимое одной ячейки, содержащей формулу массива. Но к каждой ячейке можно применить свое форматирование.

2. Нельзя удалять ячейки, которые входят в формулу массива. Можно удалить только весь массив.

4. Нельзя вставлять новые ячейки, в том числе строки и столбцы, в диапазон массива.

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

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

ВЫДЕЛЕНИЕ МАССИВА В EXCEL

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

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

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

А затем на вкладке Главная из раскрывающегося списка Найти и выделить выберите пункт Выделить группу ячеек.

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

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

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

КАК УДАЛИТЬ ФОРМУЛУ МАССИВА

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

КАК ОТРЕДАКТИРОВАТЬ ФОРМУЛУ МАССИВА

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

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

1. Выделите диапазон массива любым из известных Вам способов. В нашем случае это диапазон C1:C12.

2. Перейдите в режим редактирования формулы, для этого щелкните по строке формул или нажмите клавишу F2. Excel удалит фигурные скобки вокруг формулы массива.

1. 3. Внесите необходимые корректировки в формулу:

4. А затем нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Формула будет отредактирована.

ИЗМЕНЕНИЕ РАЗМЕРОВ ФОРМУЛЫ МАССИВА

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

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

ПРИМЕНЕНИЕ ФОРМУЛ МАССИВА В EXCEL

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

• ПОДСЧЕТ КОЛИЧЕСТВА ЗНАКОВ В ДИАПАЗОНЕ ЯЧЕЕК
• На рисунке ниже представлена формула, которая подсчитывает количество знаков, включая пробелы, в диапазоне A1:A10.
• В данном примере функция ДЛСТР подсчитывает длину каждой текстовой строки из заданного диапазона, а функцияСУММ – суммирует эти значения.
• НАИБОЛЬШИЕ И НАИМЕНЬШИЕ ЗНАЧЕНИЯ ДИАПАЗОНА В EXCEL
• Следующая формула возвращает 3 наибольших значения диапазона A1:D6
• Чтобы возвратить другое количество наибольших значений, достаточно изменить массив констант. Например, следующая формула возвращает уже 6 наибольших значений того же диапазона:
• Если необходимо найти наименьшие значения, просто замените функцию НАИБОЛЬШИЙ на НАИМЕНЬШИЙ.
• ПОДСЧЕТ КОЛИЧЕСТВА ОТЛИЧИЙ ДВУХ ДИАПАЗОНОВ В EXCEL
• Формула массива, представленная на рисунке ниже, позволяет подсчитать количество различий в двух диапазонах:

Данная формула сравнивает соответствующие значения двух диапазонов. Если они равны, функция ЕСЛИ возвращает ноль, а если не равны – единицу. В итоге получается массив, который состоит из нулей и единиц. Затем функцияСУММ суммирует значения данного массива и возвращает результат.

Необходимо, чтобы оба сравниваемых диапазона имели одинаковый размер и ориентацию.

ТРАНСПОНИРОВАНИЕ МАССИВА В EXCEL

Вспомним предыдущий пример и попробуем усложнить задачу. К примеру, требуется сравнить диапазоны в Excel, которые имеют одинаковый размер, но разную ориентацию – один горизонтальный, а другой вертикальный. В этом случае на помощь придет функция ТРАНСП, которая позволяет транспонировать массив. Теперь формула из прошлого примера, немножко усложнится:

1. Транспонировать массив в Excel – значит изменить его ориентацию, а точнее заменить строки столбцами, а столбцы строками.
2. СУММИРОВАНИЕ ОКРУГЛЕННЫХ ЗНАЧЕНИЙ В EXCEL

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

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

В Excel существует, как минимум, два способа исправить эту погрешность.

1. Ввести в ячейки D4:D8 уже округленные значения. Формула массива будет выглядеть следующим образом:

2. Использовать в ячейке D9 формулу массива, которая сначала округляет значения, а затем суммирует их.

Теперь мы можем быть уверенными в том, что сумма в ячейке D9 соответствует действительности.

Как видите, сумма до и после округления немного отличается. В нашем случае это всего лишь одна копейка.

НАИБОЛЬШЕЕ ИЛИ НАИМЕНЬШЕЕ ЗНАЧЕНИЕ ПО УСЛОВИЮ

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

• Пусть в ячейке G3 мы будем задавать фамилию продавца, тогда формула массива будет выглядеть следующим образом:

В данном случае функция ЕСЛИ сравнивает значения диапазона B3:B1234 c заданной фамилией. Если фамилии совпадают, то возвращается сумма продажи, а если нет – ЛОЖЬ.

В итоге функция ЕСЛИ формирует одномерный вертикальный массив, который состоит из сумм продаж указанного продавца и значений ЛОЖЬ, всего 1231 позиция.

Затем функция МАКС обрабатывает получившийся массив и возвращает из него максимальную продажу. В нашем случае это:

1. Если массив содержит логические значения, то функции МАКС и МИН их игнорируют.
2. Чтобы вывести минимальную продажу, воспользуемся этой формулой:
3. Данная формула позволяет вывести 5 наибольших продаж указанного продавца:
4. ПОДХОДЫ К РЕДАКТИРОВАНИЮ ФОРМУЛ МАССИВА В EXCEL

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

• ПОДХОД №1
• На рисунке ниже представлена формула массива, размер которой необходимо расширить с C3:C8 до C1:C10.
• Чтобы проделать это, выполните следующие действия:

1. Выделите диапазон ячеек, которые будет содержать новый массив. Здесь главное, чтобы новый массив перекрывал собой старый, иначе Excel не даст закончить редактирование и выдаст предупреждение. В нашем случае это диапазон C1:C10.

2. Далее нужно удостовериться, что активной является ячейка, принадлежащая старому массиву. Это позволит сохранить структуру формулы, пусть и с «ложными» ссылками на ячейки. Поскольку в нашем случае активной является пустая ячейка, выделим подходящую. Допустим C3:

Сделать ячейку активной в выделенном диапазоне можно с помощью клавиши Enter.

4. Сохраним изменения в формуле, нажав комбинацию клавиш Ctrl+Shift+Enter. Формула массива будет обновлена.

ПОДХОД №2

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

1. На рисунке ниже представлен массив, диапазон которого необходимо уменьшить с С1:С10 до C1:C5.
2. Чтобы проделать это, выполните следующие действия:

1. Выделите диапазон, содержащий формулу массива.

2. Нажмите клавишу F2, чтобы перейти в режим редактирования.

3. Нажмите комбинацию клавиш Ctrl+Enter. Это действие вводит отдельную формулу в каждую из ячеек диапазона. Причем эти формулы не являются формулами массивов. Например, в ячейке С3 содержится следующая формула:

4. Перезадайте диапазон, в котором будет находиться новый массив, и сделайте активной ячейку из старого массива. Это позволит сохранить формулу, пусть и с «ложными» ссылками. В нашем случае мы просто уменьшим размер диапазона до С1:C5:

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

6. Нажмите комбинацию клавиш Ctrl+Shift+Enter, чтобы сохранить изменения. Размер массива будет уменьшен.

7. Удалите лишние формулы из диапазона C6:C10.

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

КАК НАСТРОИТЬ ЛЕНТУ В EXCEL 2013

Не всем пользователям Microsoft Excel удобно работать с предустановленными на Ленте вкладками. Иногда гораздо практичнее создать свою собственную вкладку с требуемым набором команд. В данном уроке мы покажем Вам как это можно сделать в Excel.

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

2. В появившемся диалоговом окне Параметры Excel найдите и выберите Создать вкладку.

3. Убедитесь, что выделена Новая группа. Выберите команду и нажмите Добавить. Вы также можете перетаскивать команды напрямую в группы.

4. Добавив все необходимые команды, нажмите OK. Вкладка будет создана, а команды добавлены на Ленту.

• Если Вы не нашли необходимую команду среди часто используемых, откройте раскрывающийся список Выбрать команды и выберите пункт Все команды.
• РЕЖИМ СЕНСОРНОГО УПРАВЛЕНИЯ ЛЕНТОЙ В EXCEL 2013

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

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

1. Щелкните стрелку справа от Панели быстрого доступа и из раскрывающегося меню выберите пункт Режим сенсорного управления или мыши.

2. Команда Режим сенсорного управления или мыши появится на Панели быстрого доступа.

3. В раскрывающемся меню команды выберите пункт Сенсорное управление.

4. Лента переключится в режим сенсорного управления, а размер иконок и расстояние между ними увеличится.

1. Чтобы отключить Режим сенсорного управления, щелкните команду Режим сенсорного управления или мыши и из раскрывающегося меню выберите пункт Мышь.

Источник: https://studopedia.net/1_40031_primer-primeneniya-massiva-konstant-v-EXCEL.html

Подстановка "из ниоткуда"

5231 18.03.2015 Скачать пример

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

Функция ВЫБОР

Если нужно подставить данные из одномерного массива по номеру, то можно использовать функцию ИНДЕКС или ее более простой и подходящий, в данном случае, аналог – функцию ВЫБОР (CHOOSE). Она выводит элемент массива по его порядковому номеру. Так, например, если нам нужно вывести название дня недели по его номеру, то можно использовать вот такую конструкцию

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

Массив констант в формуле

Предположим, что у нас есть список городов, куда с помощью функции ВПР (VLOOKUP) подставляются значения коэффициентов зарплаты из второго столбца желтой таблицы справа:

Хитрость в том, что можно заменить ссылку на диапазон с таблицей $E$3:$F$5 массивом констант прямо в формуле, и правая таблица будет уже не нужна. Чтобы не вводить данные вручную можно пойти на небольшую хитрость.

Выделите любую пустую ячейку. Введите с клавиатуры знак «равно» и выделите диапазон с таблицей – в строке формул должен отобразиться его адрес:

Выделите с помощью мыши ссылку E3:F5 в строке формул и нажмите клавишу F9 – ссылка превратится в массив констант:

Осталось скопировать получившийся массив и вставить его в нашу формулу с ВПР, а саму таблицу удалить за ненадобностью:

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

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

Для этого нажмите на вкладке Формулы (Formulas) кнопку Диспетчер Имен (NameManager).

Затем нажмите кнопку Создать, придумайте и введите имя (пусть будет, например, Города) и в поле Диапазон (Reference) вставьте скопированный в предыдущем способе массив констант:

Нажмите ОК и закройте Диспетчер имен. Теперь добавленное имя можно смело использовать на любом листе книги в любой формуле – например, в нашей функции ВПР:

Компактно, красиво и, в некотором смысле, даже защищает от шаловливых ручек непрофессионалов 🙂

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/2/221/

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

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

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

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

Решение

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

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

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

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

Для этого введем в ячейку следующую формулу =18000/НДС. В итоге ячейка примет значение 10 000.

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

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

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

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

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

Источник: https://micro-solution.ru/excel/ranges/name-constant

Простой способ зафиксировать значение в формуле Excel

     Сегодня я бы хотел поделиться с вами такой небольшой хитростью, как можно правильно зафиксировать значение в формуле Excel. К сожалению, очень мало пользователей используют таким удобным функционалом табличного процессора, а это жаль. Часто многие сталкивались с такой ситуацией что возникает необходимость сдвинуть или скопировать формулы, но вот незадача, адреса ячеек также уходили «налево» и результата невозможно было получить. А для получения нужного результата, нам окажет помощь доллар, а точнее знак «$», вот именно он является самым главным условием что бы закрепить значение в ячейках.         

  Итак, рассмотрим более детально все варианты как закрепляется ячейка. Есть три варианта фиксации:

Полная фиксация ячейки

      Полная фиксация ячейки — это когда закрепляется значение по вертикали и горизонтали (пример, $A$1), здесь значение никуда не может сдвинутся, так называемая абсолютная формула. Очень удобно такой вариант использовать, когда необходимо ссылаться на значение в ячейке, такие как курс валют, константа, уровень минимальной зарплаты, расход топлива, процент доплат, кофициент и т.п.

      В примере у нас есть товар и его стоимость в рублях, а нам нужно узнать он стоит в вечнозеленых долларах. Поскольку, обменный курс у нас постоянная ячейка D1, в которой сам курс может меняться исходя из экономической ситуации страны. Сам диапазон вычисление находится от E4 до E7.

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

А вот если внести изменения и зафиксировать значение в формуле простым символом доллара («$»), то мы получим следующий результат =D4/$D$1 и в этом случае, сдвигая и копируя, формулу мы получаем нужный нам результат во всех ячейках диапазона;

Фиксация формулы в Excel по вертикали

Частичная фиксация по вертикали (пример $A1), это закрепления только столбцов, возможность сдвига формулы частично сохраняется, но только по горизонтали (в строке). Как видно со скриншота или скачанного вами файла с примером.

Фиксация формул по горизонтали

Следующее закрепление будет по горизонтали (пример, A$1). И все правила остаются действительными как и предыдущем пункте, но немножко наоборот. Рассмотрим данный пример подробнее.

У нас есть товар, продаваемый, в разных городах и имеющие разную процентную градацию наценок, а нам необходимо высчитать какую наценку и где мы будем ее получать. В диапазоне K1:M1 мы проставили процент наценки и эти ячейки у нас должны быть закреплены для автоматических вычислений.

Итак, мы фиксируем горизонтальную строку $1 и вертикальный столбец $J и в ячейке К4 прописываем формулу =$J4*K$1 и после ее копирование во все ячейки вычисляемого диапазона и получаем нужный результат без каких-либо сдвигов в формуле.

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

    Что бы постоянно не переключать раскладку клавиатуры при прописании знака «$» для закрепления значение в формуле, можно использовать «горячую» клавишу F4.

Если курсор стоит на адресе ячейки, то при нажатии, будет автоматически добавлен знак «$» для столбцов и строчек.

При повторном нажатии, добавится только для столбцов, еще раз нажать, будет только для строк и 4-е нажатие снимет все закрепления, формула вернется к первоначальному виду.

      Скачать пример можно здесь.

      А на этом у меня всё! Я очень надеюсь, что вы поняли все варианты как возможно зафиксировать ячейку в формуле. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

    Не забудьте поблагодарить автора!

Деньги — нерв войны. Марк Туллий Цицерон

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/prostoj-sposob-zafiksirovat-znachenie-v-formule-excel/

Как в excel закрепить (зафиксировать) ячейку в формуле

Очень часто в Excel требуется закрепить (зафиксировать) определенную ячейку в формуле. По умолчанию, ячейки автоматически протягиваются и изменяются. Посмотрите на этот пример.

• У нас есть данные по количеству проданной продукции и цена за 1 кг, необходимо автоматически посчитать выручку.
• 
• Чтобы это сделать мы прописываем в ячейке D2 формулу =B2*C2

Если мы далее протянем формулу вниз, то она автоматически поменяется на соответствующие ячейки. Например, в ячейке D3 будет формула =B3*C3 и так далее.

В связи с этим нам не требуется прописывать постоянно одну и ту же формулу, достаточно просто ее протянуть вниз.

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

Взгляните на вот такой пример. Допустим, нам необходимо посчитать выручку не только в рублях, но и в долларах. Курс доллара указан в ячейке B7 и составляет 35 рублей за 1 доллар. Чтобы посчитать в долларах нам необходимо выручку в рублях (столбец D) поделить на курс доллара.

Если мы пропишем формулу как в предыдущем варианте. В ячейке E2 напишем =D2*B7 и протянем формулу вниз, то у нас ничего не получится.

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

Для этого необходимо указать значки доллара и формула в ячейке E3 будет выглядеть так =D2/$B$7, вот теперь, если мы протянем формулу, то ссылка на ячейку B7 не будет двигаться, а все что не зафиксировано будет меняться так, как нам необходимо.

Примечание: в рассматриваемом примере мы указал два значка доллара  $B$7. Таким образом мы указали Excel, чтобы он зафиксировал и столбец B и строку 7, встречаются случаи, когда нам необходимо закрепить только столбец или только строку. В этом случае знак $ указывается только перед столбцом или строкой B$7 (зафиксирована строка 7) или  $B7 (зафиксирован только столбец B)

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

Чтобы не прописывать знак доллара вручную, вы можете установить курсор на формулу в ячейке E2 (выделите текст B7) и нажмите затем клавишу F4 на клавиатуре, Excel автоматически закрепит формулу, приписав доллар перед столбцом и строкой, если вы еще раз нажмете на клавишу F4, то закрепится только столбец, еще раз — только строка, еще раз — все вернется к первоначальному виду.

Источник: https://sirexcel.ru/osvaivaem-excel/osnovy/kak-v-excel-zakrepit-zafiksirovat-yachejku-v-formule/

Именованные константы в Excel

Допустим, у нас есть лист, на котором генерируется счет-фактура и рассчитывается налог на добавленную стоимость – НДС.

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

А можно и вовсе обойтись без ячейки, сохранив значение ставки налога в именованной константе.[1]

Рис. 1. Определение имени, ссылающегося на константу

Скачать заметку в формате Word или pdf

Выполните следующие действия (рис. 1):

1. Пройдите по меню Формулы –> Определенные имена –> Присвоить имя, чтобы открыть диалоговое окно Создание имени.
2. Введите имя (в данном случае НLC) в поле Имя.
3. В качестве области для этого имени укажите вариант Книга. Если хотите, чтобы это имя действовало только на определенном листе, выберите в списке Область именно этот лист.
4. Установите курсор в поле Диапазон и удалите все его содержимое, вставив взамен простую формулу, например, 18%.
5. Нажмите Ok, чтобы закрыть окно.

Вы создали именованную формулу, в которой не используется никаких ссылок на ячейки. Попробуем ввести в любую ячейку следующую формулу: =НДС. Эта простая формула возвращает значение 0,18. Поскольку эта именованная формула всегда возвращает один и тот же итог, ее можно считать именованной константой. Эту константу можно использовать и в более сложной формуле, например, =А1*НДС.

Именованная константа может состоять и из текста. Например, в качестве константы можно задать имя компании. В диалоговом окне Создание имени можно ввести, например, следующую формулу, называющуюся MSFT: =»Microsoft Corporation».

Далее можно использовать формулу ячейки: =»Annual Report: «&MSFT. Данная формула возвращает текст Annual Report: Microsoft Corporation (Годовой отчет: корпорация Microsoft).

Однако они отображаются в диалоговом окне Вставка имени (оно открывается при нажатии клавиши F3), а также в раскрывающемся списке, применяемом при создании формулы (рис. 2; при наборе формулы введите букву Н, и Excel выдаст подсказку).

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

Рис. 2. Именованная константа доступна для использования в формулах

Как вы уже догадались, значение константы можно изменить, когда угодно, открыв диалоговое окно Диспетчер имен (команда Формулы –> Определенные имена –> Диспетчер имен).

Нажмите в нем кнопку Изменить, чтобы вызвать окно Изменение имени. Затем введите новое значение в поле Диапазон.

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

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 112, 113.

Источник: http://baguzin.ru/wp/imenovannye-konstanty-v-excel/

Как закрепить ячейки в формулах Excel?

Подробности
Категория: Надстройки Excel

Опубликовано: 19 февраля 2019

Адрес ячейки на листе рабочей книги Excel определяется двумя координатами, названием или номером столбца (в зависимости от выбранного стиля ссылок) и номером строки.

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

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

Как изменить тип ссылки?

Стандартный способ

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

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

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

Программный способ

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

Надстройка устанавливается в приложение, диалоговое окно программы вызывается кнопкой, расположенной на ленте Excel.

В этом окне выбирается нужный тип ссылки, задается диапазон ячеек (используемый диапазон, выделенный или предварительно выделенный диапазон, диапазон от заданной ячейки и до конца рабочего листа, либо именованный диапазон).

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

Надстройка позволяет:

1. Устанавливать в ячейках с формулами относительные, абсолютные и смешанные ссылки (абсолютная строка и относительный столбец, относительная строка и абсолютный столбец);
2. изменять тип ссылок во всех ячейках заданного диапазона, содержащих формулы;
3. изменять тип ссылок в формулах заданного диапазона ячеек как на одном, так и на разных листах рабочей книги;
4. оставлять формулы без изменений при невозможности их конвертирования.

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

Видео по работе с надстройкой

Источник: http://macros-vba.ru/nadstrojki/excel/312-kak-zakrepit-yachejki-v-formulah-excel

Как правильно использовать именованные константы в Excel — Трюки и приемы в Microsoft Excel

Эта статья описывает полезный метод, который поможет вам привести в порядок листы, — именованные константы.

Рассмотрим лист, который генерирует счет и рассчитывает налог с суммы продаж.

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

Чтобы упростить процесс, вы, вероятно, назвали бы эту ячейку как-то вроде SalesTax.

1. Выберите Формулы ► Определенные имена ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
2. Введите имя (в нашем случае SalesTax) в поле Имя.
3. Щелкните на поле Диапазон, удалите его содержимое и замените простой формулой, например =7,5% (рис. 88.1).
4. Нажмите кнопку ОК для закрытия окна.

Рис. 88.1. Определение имени, относящегося к константе

В предыдущих шагах была создана именованная формула, которая не использует никаких ссылок на ячейки. Чтобы протестировать ее, введите следующую формулу в любую ячейку: =SalesTax. Эта простая формула вернет .075 — результат формулы с именем SalesTax.

Поскольку эта именованная формула всегда возвращает один и тот же результат, вы можете думать о ней как об именованной константе и использовать эту константу в более сложных формулах, таких как эта: =A1*SalesTax.

SalesTax — это имя уровня книги, так что вы можете указывать его на любом листе в книге.

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

Используйте диалоговое окно Создание имени для создания следующей формулы, названной MSFT: =»Microsoft Corporation».

Затем вы можете использовать формулу ячейки, такую как эта: =»Annual Report: «&MSFT. Эта формула возвращает текст Annual Report: Microsoft Corporation.

Имена, которые не относятся к диапазонам, не появляются в поле Имя или диалоговом окне Переход (которое открывается при нажатии клавиши F5).

Такая ситуация имеет смысл, поскольку эти константы не находятся где-нибудь реально. Однако они отображаются в диалоговом окне Вставка имени (которое появляется при нажатии клавиши F3).

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

Как вы могли ожидать, у вас есть возможность изменять значение константы в любое время, используя диалоговое окно Диспетчер имен (выберите Формулы ► Определенные имена ► Диспетчер имен).

Просто нажмите кнопку Изменить для открытия диалогового окна. Изменение имениЗатем измените значение в поле Диапазон.

Источник: http://excelexpert.ru/kak-pravilno-ispolzovat-imenovannye-konstanty-v-excel

VBA Excel. Цвет ячейки (заливка, фон)

Заливка ячейки цветом в VBA Excel. Фон ячейки. Свойства .Interior.Color и .Interior.ColorIndex. Цветовая модель RGB. Стандартная палитра. Очистка фона ячейки.

Свойство .Interior.Color объекта Range

Начиная с Excel 2007 основным способом заливки диапазона или отдельной ячейки цветом (зарисовки, добавления, изменения фона) является использование свойства .Interior.Color объекта Range путем присваивания ему значения цвета в виде десятичного числа от 0 до 16777215 (всего 16777216 цветов).

Заливка ячейки цветом в VBA Excel

Пример кода 1:

Sub ColorTest1()
Range(«A1»).Interior.Color = 31569
Range(«A4:D8»).Interior.Color = 4569325
Range(«C12:D17»).Cells(4).Interior.Color = 568569
Cells(3, 6).Interior.Color = 12659
End Sub

Поместите пример кода в свой программный модуль и нажмите кнопку на панели инструментов «Run Sub» или на клавиатуре «F5», курсор должен быть внутри выполняемой программы. На активном листе Excel ячейки и диапазон, выбранные в коде, окрасятся в соответствующие цвета.

Есть один интересный нюанс: если присвоить свойству .Interior.Color отрицательное значение от -16777215 до -1, то цвет будет соответствовать значению, равному сумме максимального значения палитры (16777215) и присвоенного отрицательного значения. Например, заливка всех трех ячеек после выполнения следующего кода будет одинакова:

Sub ColorTest11()
Cells(1, 1).Interior.Color = -12207890
Cells(2, 1).Interior.Color = 16777215 + (-12207890)
Cells(3, 1).Interior.Color = 4569325
End Sub

Проверено в Excel 2016.

Вывод сообщений о числовых значениях цветов

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

Пример кода 2:

Sub ColorTest2()
MsgBox Range(«A1»).Interior.Color
MsgBox Range(«A4:D8»).Interior.Color
MsgBox Range(«C12:D17»).Cells(4).Interior.Color
MsgBox Cells(3, 6).Interior.Color
End Sub

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

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

В VBA Excel есть предопределенные константы часто используемых цветов для заливки ячеек:

Предопределенная константа	Наименование цвета
vbBlack	Черный
vbBlue	Голубой
vbCyan	Бирюзовый
vbGreen	Зеленый
vbMagenta	Пурпурный
vbRed	Красный
vbWhite	Белый
vbYellow	Желтый
xlNone	Нет заливки

Присваивается цвет ячейке предопределенной константой в VBA Excel точно так же, как и числовым значением:

Пример кода 3:

Range(«A1»).Interior.Color = vbGreen

Цветовая модель RGB

Цветовая система RGB представляет собой комбинацию различных по интенсивности основных трех цветов: красного, зеленого и синего. Они могут принимать значения от 0 до 255. Если все значения равны 0 — это черный цвет, если все значения равны 255 — это белый цвет.

Выбрать цвет и узнать его значения RGB можно с помощью палитры Excel:

Палитра Excel

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

Пример кода 4:

Range(«A1»).Interior.Color = RGB(100, 150, 200)

Очистка ячейки (диапазона) от заливки

Для очистки ячейки (диапазона) от заливки используется константа xlNone:

Range(«A1»).Interior.Color = xlNone

Свойство .Interior.ColorIndex объекта Range

До появления Excel 2007 существовала только ограниченная палитра для заливки ячеек фоном, состоявшая из 56 цветов, которая сохранилась и в настоящее время. Каждому цвету в этой палитре присвоен индекс от 1 до 56. Присвоить цвет ячейке по индексу или вывести сообщение о нем можно с помощью свойства .Interior.ColorIndex:

Пример кода 5:

Range(«A1»).Interior.ColorIndex = 8
MsgBox Range(«A1»).Interior.ColorIndex

Просмотреть ограниченную палитру для заливки ячеек фоном можно, запустив в VBA Excel простейший макрос:

Пример кода 6:

Sub ColorIndex()
Dim i As Byte
For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next
End Sub

Номера строк активного листа от 1 до 56 будут соответствовать индексу цвета, а ячейка в первом столбце будет залита соответствующим индексу фоном.

Готовую стандартную палитру из 56 цветов можете посмотреть здесь.

Источник: https://vremya-ne-zhdet.ru/vba-excel/tsvet-yacheyki-zalivka-fon/

Excel. Формулы. Именованные константы

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

Создать именованную константу можно с помощью встроенного в Excel и доступного во всех его версиях инструмента — «Диспетчер имен». Он расположен на вкладке «Формулы» в группе команд «Определенные имена».

Команда на ленте

Для создания имени нужно открыть диспетчер имен и в появившемся окне выбрать команду «Создать». После этого откроется диалоговое окно «Создание имени».

В него нужно будет ввести:

• имя Вашей константы,
• область, в которой она будет действовать (обычно, в качестве области действия указывают всю книгу, чтобы именованная константа была доступна на всех листах),
• примечание (необязательная опция, в которой можно указать, что именно означает Ваша именованная константа),
• диапазон (для создания константы нужно указывать не диапазон, а ее значение, через знак «=» или без него).

Например, для создания именованной ставки НДФЛ заполнить поля нужно примерно так:

Создание имени

После нажатия на кнопку «ОК» Вы снова увидите на экране окно «Диспетчера имен», но оно уже будет содержать созданную только что именованную константу.

Именованная константа в «Диспетчере имен»

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

Пример использования

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

Помимо числовых значений в качестве именованных констант можно использовать и обычный текст. Например, длинное полное наименование Вашей компании можно спрятать под коротким именем, что существенно ускорит его ввод в ячейку (при создании такой константы текст в поле Диапазон можно вводить без кавычек).

Пример создания текстовой константы

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Чтобы не пропустить новые уроки и постоянно повышать свое мастерство владения Excel — подписывайтесь на наш канал в Telegram Excel Everyday

Куча интересного по другим офисным приложениям от Microsoft (Word, Outlook, Power Point, Visio и т.д.) — на нашем канале в Telegram Office Killer

• Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot
• Вопросы по другому ПО (кроме Excel) задавайте второму боту — @KillOfBot
• По заказам и предложениям обращайтесь к нам на сайте tDots.ru
• С уважением, команда tDots.ru

Источник: https://zen.yandex.ru/media/id/59affb7afd96b11e8eadd771/5a438a9e248090b430dbe091