Имена ячеек
Пользовательские имена ячеек и диапазонов





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Пользовательские имена ячеек и диапазонов

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

Вспомнить формулу
На прошлом уроке мы разобрали, как использовать абсолютный тип адреса, если ссылки на ячейки не должны меняться при копировании формулы. Но теперь давайте представим реальную рабочую картину – у вас есть небольшой набор показателей (курсы валют, нормы расходов материалов, поправочные коэффициенты, проценты скидок и т.д.) и скорее всего он будет расположен на отдельном листе. Чтобы использовать их в вычислениях на других листах файла, придется каждый раз искать сначала этот лист, а потом и нужную ячейку. И попробуйте через неделю вспомнить, разбираясь в формулах, что это были за ячейки… И вот такая у вас должна получиться картинка в режиме редактирования ячейки E4 на первом листе вложенного файла

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

Как быстро найти ячейки, участвующие в расчетах
Выделите нужную ячейку с формулой выполните команду Формулы – Зависимости формул – Влияющие ячейки [Formulas - Formula Auditing - Trace Precedents] и на листе появятся стрелки двух типов:
1. синие стрелки указывают на ячейки, использованные в формуле и расположенные на этом же листе
2. черная пунктирная стрелка с иконкой листа приведет к ячейкам, расположенным на других листах книги

Синей стрелкой воспользоваться достаточно легко - она выходит из ячейки, участвующей в расчете, но чтобы добраться до ячеек, находящихся на других листах, нужно навести курсор мыши на пунктирную линию черной стрелку и выполнить на ней двойной щелчок левой кнопкой мыши, после чего откроется диалоговое окно Переход [Go To] со списком тех самых ячеек – выбираете в нем нужную, жмете Ок и Excel отправит Вас на выбранную ячейку на каком бы листе она ни находилась. Убрать все стрелки можно при помощи команды из той же группы инструментов Убрать стрелки [Remove arrows]

Зачем нужны пользовательские имена ячеек
Но можно оформить предыдущую задачу более красиво и миновать поиск нужной ячейки (как для написания формулы, так и для чтения) вообще. Делается это следующим образом:
1. выделите ячейку, которая будет многократно участвовать в расчетах (в примере ячейка B4 листа Показатели)
2. щелкните в поле имени – оно находится под лентой слева – и впишите там свое название (только не делайте пробелов и начните с буквы), у меня будет курс1 . Ввод имени завершите нажатием клавиши Enter

3. аналогичным образом присвойте имена ячейкам B5 и B6 - курс2 и курс3 соответственно
4. вернитесь на лист с расчетами и выберите ячейку для расчета (у меня F4 на первом листе) и начните писать формулу =D4* теперь начните вводить первые буквы имени – ку допустим – и под курсором мыши отобразится список функций и имен, соответствующих введенной комбинации – выбирайте нужную позицию Double-кликом или клавишей Tab . Для завершения ввода формулы в ячейке нажмите Enter

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

Что делать с уже написанными формулами?
Проверьте формулы столбца E «Сумма в руб. дата 2» - там осталась стандартная ссылка до ячейки, то есть Excel автоматически не заменяет "родные" ссылки пользовательскими именами. Есть два способа решить эту задачу:
Стандартная команда Excel Формулы – Определенные имена – Присвоить имя (стрелка) – Применить имена [Formulas – Defined Names – Define Name – Apply Names] В некоторых версиях программы эта команда не всегда правильно работает, особенно она не любит ячейки других листов
Для таких слаев можно испльзовать другой сценарий. Скопируйте адрес ячейки из текста формулы (в примере Показатели!$B$5) и вставьте его в поле Найти [Find] диалогового окна Найти и Заменить [FindandReplace], добраться до которого можно Главная – Редактирование – Найти и выделить – Заменить [Home – Editing – Find & select – Replace], а в поле Заменить [Replace] напишите созданное Вами имя этой ячейки курс2. Далее под кнопкой Параметры [Options] установите в списке Искать / [Within]: значение в книге [workbook] и жмите Заменить все [ReplaceAll]

Этот вариант работает всегда :-)

Как изменить или удалить созданное имя?

Не стоит изменять пользовательские имена ячеек через то же окно, в котором они создавались (поле имени), так как таким способом Вы не измените старое имя, но добавите еще одно для той же ячейки. Управлять именами нужно через Диспетчер имен [Name Manager], который находится по центру ленты на вкладке Формулы – Определенные имена [Formulas – Defined Names – Name Manager] В открывшемся окне сначала выделите имя, с которым будете работать, а потом нажмите в верхней части окна нужную команду (Удалить [Delete] или Изменить [Edit], например)
При корректировке имени Excel может вести себя капризно – если это случилось и программа выводит сообщения, что такое имя использовать нельзя, закройте все книги Excel и откройте заново (иными словами, перезапустите программу) – должно помочь

Как найти ячейку в большой таблице данных?
Усложним задачу. Для расчета значений столбца «Сумма в руб. валюта-дата ?» в Таблице № 1 нужно использовать ячейку из Таблицы № 3 (предположительно, очень большой). Как сделать это быстро?
Задача сводится к следующему: сумму в у.е. из Таблицы № 1 нужно умножить на ячейку из Таблицы № 3 , расположенную на пересечении определенным образом именованных строки и столбца
Для этого нужно дать строкам и столбцам таблицы имена, соответствующие ее названиям строк и столбцов. Можно, конечно, выделить диапазон данных и точно так же, как и в выше рассмотренном случае с ячейками, присвоить имя через поле имени, но гораздо быстрее это сделает сам Excel. Итак, поступаем следующим образом:
1. выделяем всю таблицу данных (например, выбираем ее любую заполненную ячейку и жмем Ctrl + * )
2. выполняем команду Формулы – Определенные имена – Создать из выделенного [Formulas – Defined Names – Create Namesfrom Selection]
3. в открывшемся окне проверяем, корректно ли Excel выставил флажки – в нашем случае должны стоять два: в строке выше [Top row] и в столбце слева [Left column] – и жмем ОК

При выполнении данной команды Excel не сообщает о выполненных действиях, но все созданные имена легко обнаружить в Диспетчере имен [Name Manager] . Можно выделить любое имя в списке, а далее щелкнуть в поле Диапазон [Refers to] в нижней части окна и на рабочем листе увидеть тот самый диапазон ячеек, для которого создано выделенное имя – он будет выделен пунктирной рамкой (рис. на следующей странице конспекта)
Кстати, создавая такие имена, Excel сам заменит пробел на знак нижнего подчеркивания

Диспетчер имен [Name Manager] просто закройте и возвращаемся к ячейке, в которой нужно выполнить расчет (G4 в Таблице № 1) , и пишем следующую формулу: =D4*(Дата3 Валюта4) – в данной формуле имена Дата3 и Валюта4 начинаем писать с клавиатуры, а далее выбираем нужное имя в выпадающем списке, а между ними ставим пробел – в данной ситуации он работает как оператор пересечения диапазонов:

Копируйте формулу до конца таблицы ( $ не нужно, Таблица № 3 может располагаться на другом листе и скорее всего - так и будет)

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора.

Похожие материалы

Настройки сводных таблиц
Сводные таблицы. Часть 2
Перестройка макета отчёта. Традиционная фильтрация данные в сводных таблиц. Использование специальных срезов (для новых версий).

Примеры использования функции ЕСЛИ
ЕСЛИ логическая функция
Как выполнить обработку ошибок с помощью ЕСЛИОШИБКА. Как ЕСЛИ выполнить проверку данных по нескольким условиям - И, ИЛИ, вложенные ЕСЛИ

Текстовые функции для отбора и исправления знаков в ячейке
Текстовые функции
Как взять из ячейки первые или последние несколько знаков? А из середины текста? Как правильно использовать функции ЛЕВСИМВ, ПРАВСИМВ, ПСТР и другие?

Создание сводной таблицы и настройки отчёта
Сводные таблицы. Часть 1
Команда Сводная таблица позволяет получить компактный упорядоченный отчёт по выгрузке из базы данных. Создать и настроить отчёт сводной таблицы очень просто. Главное - начать.

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



Оставляя заявку, вы соглашаетесь на обработку ваших персональных данных.