Как узнать список имен в excel
Обновлено: 04.07.2024
Диспетчер имен в Excel применяется для присвоения символьных имён ячейкам или диапазонам ячеек, а также для редактирования созданных имён. Присвоенные имена можно считать обычными переменными, которые допускается использовать внутри различных выражений Excel.
видео может дополнять статью или содержать её полностьюОсновное назначение Диспетчера имён в Excel заключается в присвоении символьных имён отдельным ячейкам или диапазонам ячеек. В определённых случаях это может быть весьма удобно, поскольку позволяет в дальнейшем использовать выбранные имена в различных формулах.
Как открыть диспетчер имен в Excel, чтобы посмотреть как он выглядит? В верхнем меню выберите раздел «Формулы» и нажмите кнопку «Диспетчер имён» (кнопка обычно находится примерно посередине панели, но вообще это зависит от размера экрана и того, менялись ли стандартные настройки панелей Excel).
Сам Диспетчер имён представляет собой всплывающее окно, которое показано на скриншоте ниже. На рисунке показан Диспетчер имен в Excel 2010 версии программы, но в целом его вид практически не различается от версии к версии (к примеру, Диспетчер имен в Excel 2016 практически такой же).
[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов
Далее рассмотрено как задавать имена и использовать их в собственных формулах.
Как использовать Диспетчер имён в Excel
Прежде всего нужно понимать те ситуации, в которых вообще имеет смысл задавать ячейкам фиксированные имена. Обычно это требуется в тех случаях, когда размеры некоторых таблиц (или их частей) остаются неизменными, причём не меняется и смысл контента в ячейках.
К примеру, если в какой-то ячейке находится некоторый числовой коэффициент, то возможно имеет смысл задать этой ячейке имя, по которому можно будет сразу понять, что именно находится в данной ячейке.
Фактически можно считать, что Excel позволяет создавать переменные вида «x=10», при этом слева будет имя переменной, а справа — её значение.
Как задать имя и изменить его
Откройте Диспетчер имён и нажмите кнопку «Создать. ». При этом откроется маленькое окошко, которое Вы можете видеть на скриншоте выше. Для каждой переменной требуется обязательно указать:
- Имя (с учётом ограничений, налагаемых на имена переменных в Excel);
- Область видимости (поле «Область»);
- Значение переменной (параметр «Диапазон»);
Также можно указать необязательный комментарий в качестве пояснения. Обычно это поле оставляют просто пустым.
Ограничения для имён
Вы не можете присвоить абсолютно любое имя переменной, поскольку в Excel есть некоторые ограничения на имена переменных. При несоблюдении правил именования переменных Вы увидите ошибку.
Правила задания имён переменных следующие:
- Допустимые символы
Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания. В качестве определенного имени нельзя использовать буквы "C", "c", "R" и "r", поскольку эти буквы используются как сокращенное имя строки и столбца выбранной в данный момент ячейки при их вводе в поле Имя или Перейти. - Имена в виде ссылок на ячейки запрещены
Имена не могут быть такими же, как ссылки на ячейки, например, Z$100 или R1C1. - Пробелы не допускаются
Использовать в имени пробелы нельзя. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.), как в примерах "Налог_с_Продаж" и "Первый.Квартал". - Имя может содержать до 255-ти символов
- Учет регистра
Имя может состоять из строчных и прописных букв. Excel не различает строчные и прописные буквы в именах. Например, если создать имя "Продажи" и затем создавать имя "ПРОДАЖИ", Excel предложит выбрать уникальное имя.
Например, мы можем создать переменную с именем «ЧислоПИ». Это правильное имя.
Использование переменных
Имена переменных можно использовать вместо адресов ячеек или диапазонов в формулах. Ставить имя переменной в кавычки или как-то ещё специально обозначать не требуется — Excel самостоятельно определит, что указанное имя является переменной, если на данном листе эта переменная имеет значение в соответствие с её областью видимости.
Если в имени переменной есть ошибка или её область видимости не позволяет использовать переменную на данном листе, то в результате в формуле будет ошибка.
Что ещё можно сказать о переменных в Excel
Обратите внимание, что имена при помощи Диспетчера можно присваивать не только обычным ячейкам, но и объединённым. В этом случае объединённая ячейка считается за одну, как и принято в Excel.
Напомним, что в Excel адресом объединённой ячейки является верхняя левая ячейка диапазона, объединением которого и была получена ячейка.
При выделении ячейки или диапазона, для которых задано имя, в левом верхнем углу таблицы вместо адреса ячейки появляется заданное имя.
Задавайте ячейкам имена в тех случаях, когда предполагается использовать это имя более одного раза. Иначе особого смысла в использовании имён нет.
Примеры заданных переменных и их использование
После статьи Вы можете скачать файл Эксель, в котором приводится простой пример задания имени диапазону ячеек, а также использование имени в формулах. Также некоторые подобные примеры Вы можете найти в прикреплённых файлах к другим статьям по Excel на нашем сайте.
Для комплексного изучения программы Вы можете воспользоваться нашим специальным учебным видеокурсом по Excel, с учебной программой и примерами видео-уроков из которого можно познакомиться по указанной ссылке.
Если Вы нашли интересный пример использования Диспетчера имён, то поделитесь этим в комментариях после статьи. Это совсем несложно, но поможет другим пользователям, изучающим работу в Эксель.
Смотреть видео
Диспетчер имен в Excel
Прикреплённые документы
- Определение и использование имен в формулах Excel.pdf
Файлы для загрузки
Вы можете скачать прикреплённые ниже файлы для ознакомления. Обычно здесь размещаются различные документы, а также другие файлы, имеющие непосредственное отношение к данной публикации.
Если в книге есть несколько именованных диапазонов, и вы хотите просмотреть эти именованные диапазоны, кроме открытия диалогового окна Диспетчер имен, как вы можете это сделать? Здесь я расскажу о некоторых приемах, позволяющих перечислить или выделить именованные диапазоны, чтобы вы могли легко увидеть их в Excel.
Выделите все именованные диапазоны на активном листе
Список всех именованных диапазонов в ячейках
В Excel есть утилита, позволяющая копировать и вставлять все именованные диапазоны в ячейки.
1. Выберите ячейку, в которую вы поместите именованные диапазоны, и щелкните Формулы > Использование в формуле > Вставить имена. Смотрите скриншот:
2. Затем в Вставить имя диалоговое окно, нажмите Вставить список. Смотрите скриншот:
Теперь именованные диапазоны книги перечислены в ячейках.
Список и редактирование именованного диапазона в области навигации
Если у вас есть Kutools for Excel, вы можете применить Область переходов чтобы увидеть все именованные диапазоны и отредактировать их по своему усмотрению.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
Нажмите на Kutools > Навигация > Имя менеджера для отображения всех именованных диапазонов на панели. Смотрите скриншот:
(1) На этой панели вы можете отфильтровать именованный диапазон, используя Фильтр.
(2) Вы можете добавлять, удалять, обновлять или редактировать именованные диапазоны по своему усмотрению.
Выделите все именованные диапазоны на активном листе
Работы С Нами Kutools for ExcelАвтора Выделить имена утилита, все именованные диапазоны в активном листе будут выделены цветом фона.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Нажмите Kutools Plus > Дизайн рабочего листа для Дизайн таб. Смотрите скриншот:
2. Затем перейдите к Дизайн вкладку и щелкните Выделить имена для выделения названных диапазонов на активном листе.
Выпадающий список работает так: выбираете ячейку и справа от нее появляется кнопка со стрелкой вниз (правда, стрелка больше похожа на треугольник).
После нажатия на кнопку списка появится перечень доступных значений, одно из которых можно выбрать.
Значений в списке может быть много, но в обзор помещается до 8 строк. Если значений в списке больше восьми, справа от них появится полоса прокрутки.
Чтобы создать выпадающий список, выделите ячейку, где он должен появиться (или группу ячеек) и перейдите на вкладку Данные -> Проверка данных.
В появившемся окне укажите тип данных – Список, поставьте галочку рядом со строкой «Список допустимых значений».
Источником данных может быть:
- Текст – пишется через точку с запятой «;» и без знака равно «=», например
Материалы;Заработная плата;Амортизация
- Ссылки на ячейки:
=$A$1:$A$7
- Именованный диапазон:
=ИмяДиапазона
Связанные выпадающие списки
Связанные выпадающие списки – это списки, в которых выпадающие значения появляются не «просто так», а в зависимости от уже заполненных данных. Так, для выбранной группы появится только список входящих в неё наименований.
Создадим выпадающие списки несколькими способами – для разных таблиц с исходными данными.
Способ 1. Названия групп в заголовках столбцов, в строках – элементы групп.
Способ 2. Названия групп – в первом столбце, элементы групп – во втором столбце.
Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов
Исходные данные: таблица с названиями групп в заголовках столбцов.
Справка:
Форматированная («умная») таблица Excel
Форматированная таблица – это таблица, у которой есть свое имя, свойства и структура. Такая таблица представляет из себя именованный «саморасширяющийся» диапазон. При добавлении в нее новых данных границы таблицы автоматически «захватят» новое значение.
У форматированной таблицы множество преимуществ по сравнению с обычной. Поэтому на курсах и семинарах я советую использовать такие таблицы везде, где есть такая возможность.
Создать форматированную таблицу просто: выделите диапазон ячеек и перейдите в меню Главная -> Форматировать как таблицу -> выберите понравившийся вид таблицы. Готово – форматированная таблица создана.
Формула ДВССЫЛ
Формула ДВССЫЛ передает значения из ячейки, адрес которой записан в самой формуле в виде текстовой строки.
Например, записываем в ячейке B1 адрес ячейки А1. Формула ДВССЫЛ( B1 ) «увидит», какой адрес записан в ячейке B1, а результатом вычисления формулы будет текст, записанный в ячейке А1. Эту же формулу можно записать, указав адрес ячейки в кавычках – ДВССЫЛ( « А1 » ).
С помощью ДВССЫЛ можно обратиться к ячейке по адресу с помощью других формул, например СЦЕПИТЬ, & или ЕСЛИ и т.д. Так, формула на рисунке ДВССЫЛ( B1 & C1 ) обращается к тексту в ячейке А1. После нажатия Enter в ячейке, где вводилась формула ДВССЫЛ( B1 & C1 ) появится значение из ячейки A1, в нашем случае это «текст».
Пошаговая инструкция по созданию связанных выпадающих списков
Шаг 1. Создайте справочник исходных данных в виде форматированной smart-таблицы.
- Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: выберите в меню Главная -> Форматировать как таблицу.
- В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.
Столбец таблицы: = Источник[Материалы]
Чтобы появилась такая формула, нажмите равно = и выделите столбец, его имя появится в строке формул.
Шаг 2. Создайте выпадающий список с группами.
Готово! В столбце «группа» появился выпадающий список.
Шаг 3. Создайте выпадающий список со статьями.
Готово! В столбце «статья» появляется только список статей, входящих в группу.
Теперь в форматированную smart-таблицу можно добавлять новые группы и статьи. Добавим, например, новый столбец «Прочее», и такая группа сразу же появится в выпадающем списке.
Способ 2. Связанные выпадающие списки из таблицы с группами в первом столбце и элементами - во втором
Исходные данные: таблица с названиями групп в первом столбце, элементами групп – во втором столбце.
На самом деле в сети можно найти несколько вариантов реализации этого способа. Но у них у всех есть один недостаток: такой список нужно «администрировать». Потому что таблица должна быть всегда отсортирована по названиям групп – нельзя, чтобы группы располагались произвольно. Если группы будут идти «как попало», то формула, с помощью которой это всё сделано (СМЕЩ) не сработает, и список будет создаваться с ошибкой. Т.е. пользователю нужно все время сортировать первый столбец или добавлять туда данные в алфавитном порядке. А еще потребуется записывать где-то отдельно сами названия групп и это тоже нужно будет делать «вручную».
Мы придумали, как обойти эту проблему: с помощью сводных таблиц , которые будут за нас упорядочивать данные. Благодаря этому постоянное «администрирование» будет заменено на простую процедуру «обновить».
Для создания списков используем форматированные (умные) таблицы, сводные таблицы, формулы СМЕЩ + ПОИСКПОЗ + СЧЁТЗ, СЧЁТЕСЛИ и диспетчер имен.
Справка:
Формула СМЕЩ
СМЕЩ выдает ссылку на диапазон ячеек, находящийся в указанном количестве ячеек от исходной. Ссылка определяется с учетом заданного в формуле размера диапазона – числа строк и столбцов. Другими словами, этой формулой вы можете «сказать» Excel-ю на сколько ячеек он должен отступить и какой диапазон «захватить».
Синтаксис формулы СМЕЩ такой:
СМЕЩ(ссылка ; смещ_по_строкам ; смещ_по_столбцам ; [высота] ; [ширина] ), где
- ссылка – ссылка, от которой вычисляется смещение, может быть адресом ячейки или группы ячеек;
- смещ_по_строкам – количество строк, которые требуется отсчитать вверх или вниз от начальной ссылки;
- смещ_по_столбцам – количество столбцов, которые требуется отсчитать влево или вправо от начальной ссылки;
- [высота] – число строк возвращаемой ссылки (необязательный);
- [ширина] – число столбцов возвращаемой ссылки (необязательный).
Формула ПОИСКПОЗ
Ищет нужный нам элемент в диапазоне ячеек и выдает его порядковый номер в диапазоне.
Синтаксис ПОИСКПОЗ такой:
ПОИСКПОЗ( искомое_значение ; просматриваемый_массив ; [тип_сопоставления] )
Подробнее про эту формулу можно посмотреть в видеоинструкции: Какая формула лучше ВПР и работает с несколькими критериями
Формула СЧЁТЗ
СЧЁТЗ просто считает количество непустых ячеек в диапазоне.
Формула СЧЁТЕСЛИ
Почти тот же СУММЕСЛИ, только проще – подсчитывает количество значений, соответствующих определенному условию.
Пошаговая инструкция по созданию списков
Шаг 1. Преобразуйте исходные данные в форматированную smart-таблицу.
- Выделите таблицу со статьями и преобразуйте ее в smart-таблицу: перейдите в меню Главная -> Форматировать как таблицу.
- В появившемся окне обязательно проверьте галочку рядом с надписью «Таблица с заголовками». Если ее нет – поставьте.
Форматированная таблица «статьи» создана.
- Создайте первую сводную таблицу с группами статей.
Выделите любую ячейку таблицы с исходными данными, перейдите в меню Вставка -> Сводная таблица. Добавьте сводную таблицу на существующий лист и поместите группы в область строк.
- Создайте вторую сводную таблицу со статьями: меню Вставка -> Сводная таблица. В область строк поместите группы и статьи.
- Форматируем сводную таблицу со статьями и придаем ей вид справочника.
Выделите любую ячейку таблицы, перейдите на вкладку Конструктор -> Макет отчета -> Показать в табличной форме. У нас получится почти та таблица, которая нам нужна, но в ней автоматом появятся промежуточные суммы. Чтобы их отключить, идем: Промежуточные итоги -> Не показывать промежуточные суммы.
- Скройте строку «Общий итог» в обеих таблицах справочников. Перейдите на вкладку Конструктор -> Общие итоги -> Отключить для строк и столбцов.
В итоге получатся два справочника, как на рисунке ниже. Для удобства разместите таблицы рядом на одном листе – с первой строки и в столбцах A, C и D, как на рисунке (это поможет разобраться с формулой СМЕЩ).
Шаг 3. Создайте именованные диапазоны с помощью диспетчера имен.
- Откройте диспетчер имен: в меню Формулы -> Диспетчер имен.
- Введите имя «ГруппыСписок» и формулу, которая будет определять диапазон:
=СМЕЩ($A$1;1;0;СЧЁТЗ($A:$A)-1;1)
Пояснения к формуле:
СМЕЩ ( $A$1 ; 1 ; 0 ; СЧЁТЗ( $A:$A ) – 1 ; 1 ) – определяет адрес ячеек с названиями групп.
- $A$1 – это первая ячейка в справочнике групп.
- Следующие цифры 1 ; 0 – это отступ от первой ячейки на 1 строку и 0 столбцов (отступ нужен, потому что в первой ячейке название столбца).
- СЧЁТЗ( $A:$A ) – 1 Считаем число непустых ячеек в столбце А. Вычитаем -1, потому что название столбца не должно быть в списке.
- Последнее число 1 в формуле – это количество столбцов.
Нажмите ОК. Названия листов в формуле появятся сами.
- Точно так же создайте в диспетчере имен список статей.
Введите имя ГруппыСтатей, а для диапазона – формулу:
=СМЕЩ($C$1;ПОИСКПОЗ($G2;$C:$C;0)-1;1;СЧЁТЕСЛИ($C:$C;$G2);1)
Пояснения к формуле:
СМЕЩ ( $C$1 ; ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 ; 1 ; СЧЁТЕСЛИ( $C:$C ; $G2 ) ; 1 ) – определяет адрес ячеек с названиями статей из группы с помощью ПОИСКПОЗ, которая ищет группы статей.
- $С$1 – это первая ячейка в столбце с группами.
- ПОИСКПОЗ ( $G2 ; $C:$C ; 0 ) – 1 Определяет, на сколько строк нужно отступить от первой ячейки. ПОИСКПОЗ ищет название группы, выбранной в таблице с данными (столбец $G) среди ячеек справочника (столбец $C). В адресе ячейки $G2 не «закрепляем» номер строки с помощью знака $, чтобы формула работала для каждой ячейки в столбце.
- Следующая цифра 1 – это отступ на 1 столбец вправо, т.е. переходим к столбцу «статьи», откуда нужно брать данные.
- СЧЁТЕСЛИ( $C:$C ; $G2 ) – считаем число ячеек в столбце $C, в которых названия групп такие же, как в столбце с данными. Здесь тоже не «закрепляем» номер строки у ячейки $G2 с помощью знака $.
- Последнее число 1 в формуле – это количество столбцов.
Шаг 4. Создайте выпадающие списки.
Выделите ячейки в столбце «группы», перейдите в меню Данные -> Проверка данных. Задайте тип данных Список, источник =ГруппыСписок.
То же самое – для статей. Тип данных – список, источник =ГруппыСтатьи
Выпадающие списки готовы. Форматированные smart-таблицы позволят «захватить» все данные, а сводные таблицы – избежать ошибок, отсортировать справочник и создать список групп.
COUNT - это встроенная функция в MS Excel, которая будет подсчитывать количество ячеек, содержащих числа в ячейке. Он относится к категории статистической функции и используется для возврата целого числа в качестве выходных данных. Существует множество способов подсчета ячеек в заданном диапазоне по нескольким критериям пользователя. Например, COUNTIF, DCOUNT, COUNTA и т. Д.
Формула: в простой функции подсчета есть много параметров-значений, где она будет считать все ячейки, содержащие числа.
Есть несколько конкретных встроенных функций Count, которые перечислены ниже:
- COUNT : будет подсчитано количество ячеек, содержащих числа.
- COUNTIF : будет подсчитывать количество ячеек, содержащих числа, и должно соответствовать критериям пользователя.
- DCOUNT : будет подсчитывать ячейки, которые содержат некоторое количество в выбранной базе данных и которые удовлетворяют пользовательским критериям.
- DCOUNTA : будет подсчитывать непустые ячейки в выбранной базе данных, которые удовлетворяют пользовательским критериям.
Как считать имена в Excel?
Подсчет имен в Excel очень прост и легок. Итак, вот несколько примеров, которые могут помочь вам подсчитать имена в Excel.
Вы можете скачать этот шаблон Excel Count Names здесь - Шаблон Excel Count Names
Пример № 1 - Имя счетчика с данными о возрасте
Давайте предположим, что есть пользователь, у которого есть личные данные некоторых людей, такие как Имя и Возраст, где пользователь хочет рассчитать количество имен, у которых есть данные о возрасте в таблице.
Давайте посмотрим, как мы можем сделать это с помощью функции Count.
Шаг 1: Откройте MS Excel из меню Пуск >> Перейдите на Лист1, где пользователь сохранил данные.
Шаг 2: Теперь создайте заголовки для имени счета, где пользователь хочет, чтобы имя счета имело данные о возрасте.
Шаг 3: Теперь вычислите количество имен в данных с помощью функции Count >> используйте знак равенства для вычисления >> Write в ячейке D2 и используйте COUNT >> «= COUNT («
Шаг 4: Теперь будет запрошено значение 1, указанное в ячейке B2 - B10 >> выберите ячейку B2 - B10 >> «= COUNT (B2: A10)»
Шаг 5: Теперь нажмите на клавишу ввода.
Краткое изложение примера № 1: поскольку пользователь хочет вычислить количество имен, для которых в таблице указаны данные о возрасте. Итак, в приведенном выше примере есть 6 имен, у которых есть данные о возрасте в таблице.
Давайте предположим, что есть пользователь, у которого есть личные данные некоторых людей, такие как Имя и Возраст, где пользователь хочет рассчитать количество имен, для которых в имени встречается строка «Jr.». Давайте посмотрим, как мы можем сделать это с помощью функции COUNTIF.
Шаг 1: Откройте MS Excel из меню «Пуск» >> Перейдите на Sheet2, где пользователь сохранил данные.
Шаг 2: Теперь создайте заголовок для имени Count, в имени которого есть строка «Jr.».
Шаг 3: Теперь вычислите количество имен в данных с помощью функции COUNTIF >> используйте знак равенства для вычисления >> Запись в ячейку D2 и используйте COUNTIF >> «= COUNTIF («
Шаг 4: Теперь он запросит значение1, которое указано в ячейке A2 - A10 >> выберите ячейку A2 - A10 >> «= COUNTIF (A2: A10, »
Шаг 5: Теперь он запросит критерии, по которым нужно искать только строку «Jr.» в имени >>, поэтому напишите в ячейке D2 >> «= COUNTIF (A2: A10, « * Jr. »)»
Шаг 6: Теперь нажмите клавишу Enter.
Краткое изложение примера № 2: поскольку пользователь хочет сосчитать имя, для которого в таблице есть строка «Jr.», общая для его имени. Итак, в приведенном выше примере есть 3 имени, в имени которых есть строка «младший».
Пример № 3 - количество букв с окончанием определенной строки
Предположим, что пользователь хочет сосчитать имена, в имени которых есть 5 букв и строка «ry». Давайте посмотрим, как мы можем сделать это с помощью функции COUNTIF.
Шаг 1: Откройте MS Excel из меню «Пуск» >> Перейдите к Sheet3, где пользователь сохранил данные.
Шаг 2: Теперь создайте заголовок для имени Count, в котором есть 5 букв и строка «ry», общая для их имени.
Шаг 3: Теперь вычислите количество имен в данных с помощью функции COUNTIF >> используйте знак равенства для вычисления >> Запись в ячейку E2 и используйте COUNTIF >> «= COUNTIF («
Шаг 4: Теперь он запросит значение1, которое указано в ячейке A2 - A10 >> выберите ячейку A2 - A10 >> «= COUNTIF (A2: A10, »
Шаг 5: Теперь он запросит критерии, по которым нужно искать только строку «ry» в имени с 5 буквами >>, поэтому пишите в ячейку E2 >> «= COUNTIF (A2: A10, « . ry ») »
Шаг 6: Теперь нажмите клавишу Enter.
Краткое изложение примера № 3: Поскольку пользователь хочет подсчитать имена, которые имеют 5 букв в имени и строку «ry», общие для их имени в таблице. Итак, в приведенном выше примере есть 2 имени, в имени которых есть строка «ry» с пятью буквами.
Что нужно помнить о количестве имен в Excel
Рекомендуемые статьи
Это руководство по подсчету имен в Excel. Здесь мы обсуждаем, как считать имена в Excel вместе с примерами и загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше -
Диспетчер имен в Excel применяется для присвоения символьных имён ячейкам или диапазонам ячеек, а также для редактирования созданных имён. Присвоенные имена можно считать обычными переменными, которые допускается использовать внутри различных выражений Excel.
видео может дополнять статью или содержать её полностьюОсновное назначение Диспетчера имён в Excel заключается в присвоении символьных имён отдельным ячейкам или диапазонам ячеек. В определённых случаях это может быть весьма удобно, поскольку позволяет в дальнейшем использовать выбранные имена в различных формулах.
Как открыть диспетчер имен в Excel, чтобы посмотреть как он выглядит? В верхнем меню выберите раздел «Формулы» и нажмите кнопку «Диспетчер имён» (кнопка обычно находится примерно посередине панели, но вообще это зависит от размера экрана и того, менялись ли стандартные настройки панелей Excel).
Сам Диспетчер имён представляет собой всплывающее окно, которое показано на скриншоте ниже. На рисунке показан Диспетчер имен в Excel 2010 версии программы, но в целом его вид практически не различается от версии к версии (к примеру, Диспетчер имен в Excel 2016 практически такой же).
[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтовДалее рассмотрено как задавать имена и использовать их в собственных формулах.
Как использовать Диспетчер имён в Excel
Прежде всего нужно понимать те ситуации, в которых вообще имеет смысл задавать ячейкам фиксированные имена. Обычно это требуется в тех случаях, когда размеры некоторых таблиц (или их частей) остаются неизменными, причём не меняется и смысл контента в ячейках.
К примеру, если в какой-то ячейке находится некоторый числовой коэффициент, то возможно имеет смысл задать этой ячейке имя, по которому можно будет сразу понять, что именно находится в данной ячейке.
Фактически можно считать, что Excel позволяет создавать переменные вида «x=10», при этом слева будет имя переменной, а справа — её значение.
Как задать имя и изменить его
Откройте Диспетчер имён и нажмите кнопку «Создать. ». При этом откроется маленькое окошко, которое Вы можете видеть на скриншоте выше. Для каждой переменной требуется обязательно указать:
- Имя (с учётом ограничений, налагаемых на имена переменных в Excel);
- Область видимости (поле «Область»);
- Значение переменной (параметр «Диапазон»);
Также можно указать необязательный комментарий в качестве пояснения. Обычно это поле оставляют просто пустым.
Ограничения для имён
Вы не можете присвоить абсолютно любое имя переменной, поскольку в Excel есть некоторые ограничения на имена переменных. При несоблюдении правил именования переменных Вы увидите ошибку.
Правила задания имён переменных следующие:
- Допустимые символы
Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания. В качестве определенного имени нельзя использовать буквы "C", "c", "R" и "r", поскольку эти буквы используются как сокращенное имя строки и столбца выбранной в данный момент ячейки при их вводе в поле Имя или Перейти. - Имена в виде ссылок на ячейки запрещены
Имена не могут быть такими же, как ссылки на ячейки, например, Z$100 или R1C1. - Пробелы не допускаются
Использовать в имени пробелы нельзя. В качестве разделителей слов можно использовать символ подчеркивания (_) и точку (.), как в примерах "Налог_с_Продаж" и "Первый.Квартал". - Имя может содержать до 255-ти символов
- Учет регистра
Имя может состоять из строчных и прописных букв. Excel не различает строчные и прописные буквы в именах. Например, если создать имя "Продажи" и затем создавать имя "ПРОДАЖИ", Excel предложит выбрать уникальное имя.
Например, мы можем создать переменную с именем «ЧислоПИ». Это правильное имя.
Использование переменных
Имена переменных можно использовать вместо адресов ячеек или диапазонов в формулах. Ставить имя переменной в кавычки или как-то ещё специально обозначать не требуется — Excel самостоятельно определит, что указанное имя является переменной, если на данном листе эта переменная имеет значение в соответствие с её областью видимости.
Если в имени переменной есть ошибка или её область видимости не позволяет использовать переменную на данном листе, то в результате в формуле будет ошибка.
Что ещё можно сказать о переменных в Excel
Обратите внимание, что имена при помощи Диспетчера можно присваивать не только обычным ячейкам, но и объединённым. В этом случае объединённая ячейка считается за одну, как и принято в Excel.
Напомним, что в Excel адресом объединённой ячейки является верхняя левая ячейка диапазона, объединением которого и была получена ячейка.
При выделении ячейки или диапазона, для которых задано имя, в левом верхнем углу таблицы вместо адреса ячейки появляется заданное имя.
Задавайте ячейкам имена в тех случаях, когда предполагается использовать это имя более одного раза. Иначе особого смысла в использовании имён нет.
Примеры заданных переменных и их использование
После статьи Вы можете скачать файл Эксель, в котором приводится простой пример задания имени диапазону ячеек, а также использование имени в формулах. Также некоторые подобные примеры Вы можете найти в прикреплённых файлах к другим статьям по Excel на нашем сайте.
Для комплексного изучения программы Вы можете воспользоваться нашим специальным учебным видеокурсом по Excel, с учебной программой и примерами видео-уроков из которого можно познакомиться по указанной ссылке.
Если Вы нашли интересный пример использования Диспетчера имён, то поделитесь этим в комментариях после статьи. Это совсем несложно, но поможет другим пользователям, изучающим работу в Эксель.
Смотреть видео
Диспетчер имен в ExcelПрикреплённые документы
- Определение и использование имен в формулах Excel.pdf
Файлы для загрузки
Вы можете скачать прикреплённые ниже файлы для ознакомления. Обычно здесь размещаются различные документы, а также другие файлы, имеющие непосредственное отношение к данной публикации.
Читайте также:
- Введите имя «ГруппыСписок» и формулу, которая будет определять диапазон: