Как посмотреть все именованные диапазоны в excel

Именованные диапазоны ячеек

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

Как присвоить название диапазону

1. Выделите нужные ячейки.
2. Нажмите на Данные , а далее перейдите в Именованные диапазоны . Справа откроется меню.

3. Укажите название для выбранного диапазона.

4. Чтобы изменить диапазон, нажмите на значок выбрать диапазон данных.

5. Выберите новый диапазон в таблице или введите его в текстовом поле, а затем нажмите ОК.

Поиск именованных диапазонов

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

Именующий диапазон можно найти на вкладке «Главная», нажав кнопку «Найти &Выбрать» и выбрав «Перейти».

• Во всплываемом окне «Перейти» показаны имененные диапазоны на всех книгах.
• Чтобы перейти к диапазону неименованых ячеек, нажмите CTRL+G, введите диапазон в поле «Ссылка» и нажмите ввод (или кнопку ОК). Поле «Перейти» отслеживает диапазоны по мере их ввода, и вы можете вернуться к любому из них, дважды щелкнув их.
• Чтобы перейти к ячейке или диапазону на другом листе, введите в поле «Ссылка» следующее: имя листа вместе с восклицательный индекс и абсолютные ссылки на ячейки. Например, лист2!$D $12 для перейти к ячейке, а лист3!$C$12:$F$21 — для перейти к диапазону.
• В поле «Ссылка» можно ввести несколько именовых диапазонов или ссылок на ячейки. Разделяя каждую из них запятой, например: Price, Typeили B14:C22,F19:G30,H21:H29. Когда вы нажмете ввод или нажмете кнопку«ОК», Excel выделит все диапазоны.

Дополнительные сведения о поиске данных в Excel

• Поиск или замена текста и чисел на листе
• Поиск объединенных ячеек
• Удаление или разрешение циклической ссылки
• Поиск ячеек, содержащих формулы
• Поиск ячеек с условным форматированием
• Поиск скрытых ячеек на листе

Использование Диспетчера имен в Excel

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

Чтобы открыть диалоговое окно Диспетчер имен, на вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.

В диалоговом окне Диспетчер имен отображаются следующие сведения о каждом имени в списке.

Имя столбца

Одно из следующих значений:

определенное имя, которое обозначается значком определенного имени;

Примечание: Имя таблицы — это имя таблицы Excel, которая представляет собой коллекцию данных о конкретном субъекте, хранящихся в записях (строках) и полях (столбцах). Excel создает имя таблицы Excel по умолчанию Table1, Table2 и т. д. каждый раз, когда вы вставляете таблицу Excel. Вы можете изменить имя таблицы, чтобы сделать ее более понятной. Дополнительные сведения о таблицах Excel см. в разделе Использование структурированных ссылок с таблицами Excel.

Текущее значение имени, такое как результаты формулы, строковая константа, диапазон ячеек, ошибка, массив значений или знаки-заполнители, если формулу не удается вычислить. Вот типичные примеры.

Текущая ссылка для имени. Вот типичные примеры.

• =Лист1!$A$3
• =8,3
• =HR!$A$1:$Z$345
• =СУММ(Лист1!A1,Лист2!B2)

• имя листа, если область действия — локальный уровень листа;
• «Книга», если область является глобальным уровнем книги. Этот вариант применяется по умолчанию.

Дополнительные сведения об имени длиной до 255-ти знаков. Вот типичные примеры.

• Срок действия этого значения истекает 2 мая 2007 г.
• Не удалять! Критическое имя!
• Основано на числах экзамена на сертификацию ISO.

Ссылка на выбранное имя.

Вы можете быстро изменить диапазон имени, изменив сведения в поле Ссылки . После внесения изменений можно нажать кнопку Зафиксировать

, чтобы сохранить изменения, или нажать кнопку Отмена

, чтобы отменить изменения.

• Диалоговое окно Диспетчер имен нельзя использовать при изменении содержимого ячейки.
• В диалоговом окне Диспетчер имен не отображаются имена, определенные в Visual Basic для приложений (VBA), или скрытые имена (свойство Visible имени имеет значение False).

Создание именованного диапазона

1. На вкладке Формулы в группе Определенные имена выберите команду Присвоить имя.
2. В диалоговом окне Новое имя в поле Имя введите имя, которое вы хотите использовать для ссылки.

Примечание: Длина имени не может превышать 255 знаков.

Щелкните Свернуть диалоговое окно

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

• Будьте осторожны при использовании абсолютных или относительных ссылок в формуле. Если создать ссылку, щелкнув ячейку, на которую вы хотите ссылаться, Excel создаст абсолютную ссылку, например «Лист1!$B$1». Если вы вводите ссылку, например «B1», она является относительной ссылкой. Если при определении имени активной ячейки является A1, то ссылка на «B1» действительно означает «ячейка в следующем столбце». Если в формуле в ячейке используется определенное имя, ссылка будет на ячейку в следующем столбце относительно того, где вы вводите формулу. Например, если ввести формулу в C10, ссылка будет иметь значение D10, а не B1.
• Дополнительные сведения. Переключение между относительными, абсолютными и смешанными ссылками

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

Изменение имени

При изменении определенного имени или имени таблицы все его использование в книге также будет изменено.

1. На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
2. В диалоговом окне Диспетчер имен дважды щелкните имя, которое нужно изменить, или щелкните имя, которое нужно изменить, а затем нажмите кнопку Изменить.
3. В диалоговом окне Изменение имени введите новое имя для ссылки в поле Имя.
4. Измените ссылку в поле Диапазон и нажмите кнопку ОК.
5. В диалоговом окне Диспетчер имен в поле Диапазон измените ячейку, формулу или константу, представленную этим именем.

Удаление одного или нескольких имен

1. На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
2. В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.
3. Выделите одно или несколько имен одним из способов, указанных ниже.
   • Для выделения имени щелкните его.
   • Чтобы выбрать несколько имен в смежных группах, щелкните и перетащите имена или нажмите клавишу SHIFT и нажмите кнопку мыши для каждого имени в группе.
   • Чтобы выбрать несколько имен в неконтигуционной группе, нажмите клавишу CTRL и нажмите кнопку мыши для каждого имени в группе.
4. Нажмите кнопку Удалить.
5. Нажмите кнопку ОК, чтобы подтвердить удаление.

Фильтрация имен

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

Отфильтровать можно по следующим параметрам:

Имена на листе

Отобразить только локальные имена листа.

Имена в книге

Отобразить только глобальные имена в книге.

Имена с ошибками

Отображение только имен со значениями, содержащими ошибки (например, #REF, #VALUE или #NAME).

Имена без ошибок

Отобразить только те имена, в значениях которых нет ошибок.

Определенные имена

Отобразить только те имена, которые определены пользователем или Excel, такое как имя области печати.

Имена таблиц

Отобразить только имена таблиц.

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Именованные диапазоны в Excel — несколько трюков использования

Опубликовано 11.07.2013 Автор Ренат Лотфуллин

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

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

Многоразовое создание именованного диапазона в один прием

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

К примеру, у вас имеется набор данных (как показано ниже) и вы хотите создать отдельные именованные диапазоны для каждой колонки. Вместо того чтобы создавать их по одному, вы можете воспользоваться сочетанием клавиш CTRL + SHIFT + F3, которое откроет диалоговое окно Создание имен из выделенного диапазона. Тоже самое окно доступно во вкладке Формулы -> Определенные имена –> Создать из выделенного. Теперь вы можете создать больше одного диапазона – по строкам, столбцам, оба варианта.

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

Доступ к управлению именованными диапазонами

Чтобы открыть диалоговое окно Диспетчер имен, перейдите по вкладке Формулы в группу Определенные имена и щелкните по кнопке Диспетчер имен. Либо нажатием сочетаний клавиш Ctrl + F3.

Использование формулы СМЕЩ

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

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

На самом деле не уверен, это конструктивная особенность или ошибка. Используя относительные ссылки (A1 вместо $A$1) при определении именованного диапазона, они не остаются на том же месте, как бы вы этого не хотели. Давайте рассмотрим этот случай на примере. Предположим, вы хотите создать диапазон, который смещается вниз на 10 строк от ячейки A1. Первое, что приходит в голову, это написать формулу =СМЕЩ(A1;10;0).

Пока все хорошо. Если вы захотите воспользоваться этим именованным диапазоном, необходимо подобрать для нее ячейку (скажем B1) и ввести что-то типа =мой_имен_диап. Где мой_имен_диап — это имя, которое вы дали диапазону на предыдущем шаге.

Но если вы выберите другую ячейку и снова откроете диспетчер имен, формула, которую вы ввели до этого =СМЕЩ(Лист1!A1;10;0), магическим образом преобразится (к примеру, =СМЕЩ(Лист1!A1048576;10;0)). Это происходит потому, что при создании именованного диапазона мы использовали относительные ссылки, т.е. целевой диапазон будет все время смещаться в зависимости от адреса, выбранной на данный момент, ячейки.

Использование F2 для изменения именованного диапазона

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

Чтобы избежать недоразумений при использовании стрелок, нажмите клавишу F2.

Возможно у вас имеются свои трюки по использованию именованных диапазонов?! Не хотите поделиться?)

Вам также могут быть интересны следующие статьи

• Проверка данных с помощью выпадающего списка в ячейке
• Как отфильтровать список элементов из длинного-длинного списка
• Импорт данных с Excel книги, находящейся на веб-сайте
• Повторяющиеся значения в Excel — найти, выделить или удалить дубликаты в Excel
• Excel дашборд по обслуживанию клиентов — формулы и система расчетов [часть 2 из 4]
• Формулы массивов в Excel — синтаксис формул массивов и массивов констант
• SkyDrive Excel или Как организовать одновременную работу нескольких людей в одном файле
• Гиперссылка в Excel — создание, изменение и удаление
• Инструмент Найти и заменить в Excel
• Поиск и выделение конкретных значений

Рубрика: Ввод данных | Метки: именованные диапазоны, Проверка данных | 2 комментария | Permalink

2 комментария

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

Здравствуйте, Ренат! Очень интересная диаграмма, даже при том, что и не классический тримап. Скажите, а можно построить подобную диаграмму так, чтобы её составляющие были положительные и отрицательные. Чтобы их размер зависел, насколько далеко их значение от 0, а располагались они справа и слева от оси, в зависимости от знака? Была бы очень интересная диаграмма весов.