Как сделать номенклатуру в Excel
Обновлено: 21.11.2024
Создавайте имена Excel, которые ссылаются на ячейки, постоянное значение или формулу. Используйте имена в формулах или быстро выберите именованный диапазон.
Имена Excel — Введение
В Excel вы можете создавать имена, которые относятся к:
- Ячейки на листе
- Конкретное значение
- Формула
После того, как вы определите имена Excel, вы сможете:
- Используйте эти имена в формуле вместо постоянного значения или ссылки на ячейку.
- Введите имя, чтобы быстро перейти к именованному диапазону ячеек.
Приведенные ниже инструкции показывают, как создавать имена и использовать имена в файлах Excel. Получите образец рабочей книги Excel, чтобы следовать инструкциям.
ПРИМЕЧАНИЕ. Чтобы создать краткий список всех имен в книге, см. инструкции «Быстрый список имен — без макросов».
Как назвать ячейки
Посмотрите это короткое видео, чтобы узнать, как назвать группу ячеек. Затем перейдите к этой именованной группе ячеек или используйте имя в формуле. Письменная инструкция находится под видео. ТОП
Ячейки с именами — Поле с именами
Вы можете быстро назвать выбранные ячейки, введя текст в поле имени. ПРИМЕЧАНИЕ. Существует несколько правил для имен Excel
- Выберите ячейки, которым нужно присвоить имя.
- Нажмите в поле "Имя" слева от строки формул.
- Введите допустимое название списка, состоящее из одного слова, например, Список фруктов.
- Нажмите клавишу Enter.
Правила создания имен
На сайте Microsoft есть правила для имен Excel, и они кратко изложены ниже.
Для более подробного ознакомления с тем, какие символы разрешены, см. проект excel-names Мартина Труммера на GitHub — в нем есть письменные примеры и файл Excel для загрузки.
Следуйте этим правилам Microsoft при создании имени в Excel.
- Первый символ имени должен быть одним из следующих символов:
- письмо
- подчеркивание (_)
- обратная косая черта (\).
- буквы
- числа
- периоды
- символы подчеркивания
- Пробелы не допускаются в составе имени.
- Имена не могут выглядеть как адреса ячеек, например 35 австралийских долларов или R2D2.
- C, c, R, r — нельзя использовать в качестве имен — Excel использует их как ярлыки для выбора.
Посмотрите имена
ПРИМЕЧАНИЕ. Чтобы создать краткий список всех имен в книге, см. инструкции «Быстрый список имен — без макросов».
Чтобы увидеть некоторые из именованных диапазонов на листе, используйте этот прием:
- В правом нижнем углу окна Excel выберите параметр "Уровень масштаба".
- В диалоговом окне "Масштаб" выберите "Пользовательский".
- Введите 39 в процентное поле и нажмите "ОК".
Имена некоторых диапазонов будут отображаться на листе синим текстом, как MonthList на этом снимке экрана.
- Имена, созданные с помощью формулы, например YearList, отображаться не будут.
- Некоторые диапазоны могут быть слишком маленькими, чтобы отображать их названия.
Изменить именованный диапазон
После создания именованного диапазона может потребоваться изменить ячейки, на которые он ссылается. В этом коротком видео показаны шаги, а под видео есть письменные инструкции.
Выполните следующие действия, чтобы изменить ссылку на диапазон:
- На ленте откройте вкладку "Формулы".
- Нажмите "Диспетчер имен".
- В списке нажмите на имя, которое хотите изменить.
- В поле "Относится к" измените ссылку на диапазон или перетащите лист, чтобы выбрать новый диапазон.
- Нажмите на галочку, чтобы сохранить изменения
- Нажмите "Закрыть", чтобы закрыть ВЕРХНЮЮ часть диспетчера имен.
Создать имена из текста ячейки
Чтобы быстро назвать отдельные ячейки или отдельные диапазоны, можно использовать в качестве имен текст заголовка ячейки. Посмотрите это видео, чтобы увидеть шаги. Письменные инструкции находятся под видео.
Создать имена из текста ячейки
Быстрый способ создать имена – создать их на основе текста заголовков ячеек (меток листа). В показанном ниже примере ячейки в столбце E будут названы на основе меток в столбце D.
Чтобы назвать ячейки или диапазоны на основе меток рабочего листа:
-
Выберите метки и ячейки, которые должны быть названы.Метки могут располагаться выше, ниже, слева или справа от ячеек, которые нужно назвать. В этом примере метки находятся в столбце B слева от ячеек, которым будут присвоены имена.
Создать имя для значения
Большинство имен Excel относятся к диапазонам на листе, но имена также могут использоваться для хранения значений.
Часто используемые значения
Например, создайте имя для хранения процентной суммы, которую вы часто используете, например ставки розничного налога:
Затем используйте это имя в формулах вместо ввода значения
Специальные значения
Вы также можете создавать имена для хранения значений, которые трудно ввести. Например, в некоторых формулах используется это странное число. Согласно спецификациям Excel на сайте Microsoft, это максимальное положительное число, которое можно ввести в ячейку Excel.
Вместо того, чтобы вводить это число в свои формулы, вы можете определить имя, используя это значение (скопируйте число с этой страницы, прежде чем создавать имя):
Затем используйте имя XL_Max в формулах, например эту формулу ПРОСМОТР, которая находит последнее число в столбце.
=ПРОСМОТР(9.99999999999999E+307, Данные о весе[Вес])
Как использовать имена Excel
После создания имен вы можете их использовать:
Использовать имена для быстрой навигации
Если имя относится к диапазону, вы можете выбрать это имя в раскрывающемся списке Поле имени, чтобы выбрать именованный диапазон на листе.
ПРИМЕЧАНИЕ. Если имя не отображается в раскрывающемся списке, вместо него можно ввести имя
Использование имен в формулах
Вы также можете использовать имена в формулах. Например, у вас может быть группа ячеек с количеством проданных товаров. Назовите эти ячейки Количество, а затем используйте эту формулу для расчета общей суммы:
=СУММ(Количество)
Трюки с полем имени
Помимо использования поля имени для создания именованного диапазона или выбора именованного диапазона, есть еще несколько приемов с полем имени.
Изменить размер поля имени
В старых версиях Excel поле имени имело заданную ширину, и изменить ее было нельзя. Вот как можно настроить ширину окна имени в новых версиях:
- Наведите указатель мыши на кнопку с тремя точками справа от поля имени.
- Когда указатель примет вид двунаправленной стрелки, перетащите его влево или вправо, чтобы изменить ширину.
Выбрать ячейки
Еще один удобный прием заключается в том, что вы можете использовать поле имени для выбора безымянных ячеек. Вот несколько способов, которыми этот трюк может быть полезен: показать столбцы или заполнить большой диапазон ячеек.
Показать столбцы
Вот быстрый способ отобразить определенные столбцы и оставить другие скрытыми.
Заполнить ячейки
С помощью функции автозаполнения Excel вы можете очень быстро создать список дат, чисел или других последовательностей. Просто введите одно или два значения в качестве начальной последовательности, выберите эти ячейки и дважды щелкните маркер заполнения, чтобы заполнить последнюю строку данных.
Иногда в соседнем столбце нет данных, поэтому автозаполнение не работает при двойном щелчке. Вы можете перетащить маркер заполнения вниз, но это не очень эффективно, если вам нужно создать длинную серию.
Вот как создать список из 1000 чисел в столбце A:
- Нажмите в поле имени.
- Введите a1:a1000 в поле имени и нажмите Enter.
- Выделив ячейки, введите цифру 1 и нажмите Ctrl + Ввод.
- Далее выберите ячейку A1 и введите первое число в своей серии, например 5
- Выберите ячейку A2 и введите второе число в ряду, например 10
- Выберите ячейки A1 и A2 и дважды нажмите маркер заполнения, чтобы создать серию из 1000 чисел.
Создать динамический именованный диапазон
Если список, который вы хотите назвать, будет часто меняться, добавляя и удаляя элементы, вам следует создать динамический именованный диапазон. Размер динамического именованного диапазона будет автоматически изменяться при изменении списка. Вот два способа создания динамического именованного диапазона:
Использование именованной таблицы Excel
Самый простой способ создать динамический именованный диапазон — начать с создания именованной таблицы Excel. Затем определите диапазон на основе одного или нескольких столбцов в этой таблице.
В этом примере на листе есть список частей, а также будут созданы именованная таблица и динамические именованные диапазоны. Позже, если вы добавите в таблицу новые элементы, именованный диапазон автоматически расширится.
Сначала создайте таблицу:
- Выберите ячейку в списке деталей.
- На вкладке "Вставка" ленты нажмите "Таблица".
- Убедитесь, что выбран правильный диапазон, и установите флажок "Моя таблица имеет заголовки".
- Нажмите "ОК", чтобы создать таблицу.
Далее создайте динамический список идентификаторов деталей:
- Выберите ячейки A2:A9, которые содержат идентификаторы частей (но не заголовок)
- Нажмите на панели формул и введите имя диапазона, состоящее из одного слова: PartIDList
- Нажмите клавишу Enter, чтобы завершить ввод имени.
Чтобы увидеть определение имени, выполните следующие действия:
- Перейдите на вкладку "Формулы" ленты и выберите "Диспетчер имен".
- В списке есть два именованных элемента:
- таблица Parts с именем по умолчанию Table1 (или именем, которое вы дали таблице)
- PartIDList, основанный на поле PartID в таблице 1.
Текст динамического диапазона
Поскольку именованный диапазон PartIDList основан на именованной таблице, размер списка автоматически изменится, если вы добавите или удалите идентификаторы частей в списке.
- Добавить новый элемент в список идентификаторов деталей.
- В поле «Имя» выберите имя PartIDList.
- Именованный диапазон выбран и включает новый идентификатор детали. ТОП
Динамический именованный диапазон — формула
Когда вы создаете именованный диапазон в Excel, он не включает новые элементы автоматически. Если вы планируете добавлять новые элементы в список, вы можете использовать динамическую формулу для определения именованного диапазона Excel. Затем, когда в список добавляются новые элементы, именованный диапазон будет автоматически расширяться, чтобы включить их.
Письменные инструкции находятся под видео.
Динамический именованный диапазон на основе формулы
Если вы не хотите использовать именованную таблицу, вы можете использовать динамическую формулу для определения именованного диапазона. По мере добавления новых элементов диапазон будет автоматически расширяться.
Примечание. Динамические именованные диапазоны не отображаются в раскрывающемся списке Поле имени. Однако вы можете ввести имена в поле имени, чтобы выбрать этот диапазон на листе.
- На ленте откройте вкладку "Формулы".
- Нажмите "Определить имя".
- Введите имя диапазона, например Список имен
- Оставьте для параметра "Область" значение "Книга".
- Справочная ячейка: Sheet1!$A$1
- Строки для смещения: 0
- Колонки для смещения: 0
- Количество строк: COUNTA(Лист1!$A:$A)
- Количество столбцов: 1
- Примечание: для динамического количества столбцов замените 1 на:
COUNTA(Sheet1!$1:$1)
Получить образец файла
Чтобы следовать инструкциям на этой странице, загрузите образец файла имен Excel. Заархивированный файл имеет формат xlsx и не содержит макросов. ТОП
Имя — это значимое сокращение, которое облегчает понимание назначения ссылки на ячейку, константы, формулы или таблицы, каждое из которых может быть трудно понять с первого взгляда. Следующая информация показывает распространенные примеры имен и то, как они могут улучшить ясность.
Пример типа
Пример без названия
Пример с именем
Подробнее об использовании имен
Существует несколько типов имен, которые вы можете создавать и использовать.
Заданное имя Имя, представляющее ячейку, диапазон ячеек, формулу или постоянное значение. Вы можете создать свое собственное определенное имя, или Excel может создать определенное имя для вас, например, когда вы устанавливаете область печати.
Имя таблицы Имя таблицы Excel, представляющей собой набор данных о конкретном предмете, хранящихся в записях (строках) и полях (столбцах). Excel создает имя таблицы Excel по умолчанию Table1, Table2 и т. д. каждый раз, когда вы вставляете таблицу Excel. Вы можете изменить имя таблицы, чтобы сделать его более понятным. Дополнительные сведения о таблицах Excel см. в разделе Использование структурированных ссылок с таблицами Excel.
Все имена имеют область действия либо для определенного рабочего листа (также называемого локальным уровнем рабочего листа), либо для всей книги (также называемого глобальным уровнем рабочей книги). Область действия имени — это место, в котором имя распознается без уточнения. Например:
Если вы определили имя, например Budget_FY08, и его областью действия является Лист1, это имя, если оно не уточнено, распознается только на Листе1, но не на других листах.
Чтобы использовать имя локального рабочего листа в другом рабочем листе, вы можете уточнить его, указав перед ним имя рабочего листа. Например:
Если вы определили имя, например Sales_Dept_Goals, и его областью действия является книга, это имя распознается для всех листов в этой книге, но не для других книг.
Имя всегда должно быть уникальным в пределах своей области. Excel не позволяет вам определить имя, которое уже существует в его области. Однако вы можете использовать одно и то же имя в разных областях. Например, вы можете определить имя, такое как GrossProfit, которое ограничено листами Sheet1, Sheet2 и Sheet3 в одной и той же книге. Хотя все имена одинаковы, каждое имя уникально в пределах своей области. Это можно сделать, чтобы гарантировать, что формула, использующая имя GrossProfit, всегда ссылается на одни и те же ячейки на локальном уровне листа.
Вы даже можете определить то же имя, GrossProfit, для глобального уровня рабочей книги, но опять же область будет уникальной. Однако в этом случае может возникнуть конфликт имен. Чтобы разрешить этот конфликт, по умолчанию Excel использует имя, определенное для рабочего листа, поскольку локальный уровень рабочего листа имеет приоритет над глобальным уровнем рабочей книги. Если вы хотите переопределить приоритет и использовать имя книги, вы можете устранить неоднозначность имени, добавив префикс имени книги. Например:
Вы можете переопределить локальный уровень рабочего листа для всех рабочих листов в книге. Единственным исключением является первый рабочий лист, который всегда использует локальное имя, если возникает конфликт имен, который нельзя переопределить.
Вы определяете имя с помощью:
Поле "Определенные имена" в строке формул Лучше всего использовать его для создания имени уровня рабочей книги для выбранного диапазона.
Определить имя из выделенного. Вы можете удобно создавать имена из существующих меток строк и столбцов, используя выбранные ячейки на листе.
Диалоговое окно «Новое имя» Это лучше всего использовать, когда вам нужна большая гибкость при создании имен, например указание области на уровне локального рабочего листа или создание комментария к имени.
Примечание. По умолчанию в именах используются абсолютные ссылки на ячейки.
Вы можете ввести имя следующим образом:
Ввод имени, например, в качестве аргумента формулы.
Использование автозаполнения формул Используйте раскрывающийся список автозаполнения формул, в котором автоматически отображаются допустимые имена.
Выбор из команды "Использовать в формуле" Выберите определенное имя из списка, доступного в команде "Использовать в формуле" в группе "Определенные имена" на вкладке "Формулы".
Вы также можете создать список определенных имен в рабочей книге. Найдите область с двумя пустыми столбцами на листе (список будет содержать два столбца, один для имени и один для описания имени). Выберите ячейку, которая будет левым верхним углом списка. На вкладке "Формулы" в группе "Определенные имена" нажмите "Использовать в формуле", нажмите "Вставить", а затем в диалоговом окне "Вставить имена" нажмите "Вставить список".
Ниже приведен список правил синтаксиса для создания и редактирования имен.
Допустимые символы Первым символом имени должна быть буква, символ подчеркивания (_) или обратная косая черта (\). Остальные символы в имени могут быть буквами, цифрами, точками и символами подчеркивания.
Совет. Нельзя использовать символы верхнего и нижнего регистра "C", "c", "R" или "r" в качестве определенного имени, так как они используются в качестве сокращения для выбора строки или столбца для выбранного в данный момент при вводе их в текстовое поле «Имя» или «Перейти».
Ссылки на ячейки запрещены. Имена не могут совпадать со ссылкой на ячейку, например Z$100 или R1C1.
Пробелы недопустимы. Пробелы не допускаются как часть имени. Используйте символ подчеркивания (_) и точку (.) в качестве разделителей слов, например, Sales_Tax или First.Quarter.
Длина имени Имя может содержать до 255 символов.
Чувствительность к регистру Имена могут содержать прописные и строчные буквы. Excel не различает прописные и строчные буквы в именах. Например, если вы создаете имя "Продажи", а затем другое имя "ПРОДАЖИ" в той же книге, Excel предложит вам выбрать уникальное имя.
Определить имя для ячейки или диапазона ячеек на листе
Выберите ячейку, диапазон ячеек или несмежные выделения, которым вы хотите присвоить имя.
Нажмите поле "Имя" в левом конце строки формул.
Введите имя, которое вы хотите использовать для ссылки на ваш выбор. Имена могут содержать до 255 символов.
Примечание. Вы не можете назвать ячейку, пока изменяете ее содержимое.
Вы можете преобразовать существующие метки строк и столбцов в имена.
Выберите диапазон, который вы хотите назвать, включая метки строк или столбцов.
На вкладке "Формулы" в группе "Определенные имена" нажмите "Создать из выбранного".
В диалоговом окне «Создать имена из выделенного» укажите местоположение, содержащее метки, установив флажок «Верхний ряд», «Левый столбец», «Нижний ряд» или «Правый столбец». Имя, созданное с помощью этой процедуры, относится только к ячейкам, содержащим значения, и исключает существующие метки строк и столбцов.
На вкладке "Формулы" в группе "Определенные имена" нажмите "Определить имя".
В диалоговом окне "Новое имя" в поле "Имя" введите имя, которое вы хотите использовать в качестве ссылки.
Примечание. Имя может содержать до 255 символов.
Чтобы указать область действия имени, в раскрывающемся списке Область действия выберите Рабочая книга или имя рабочего листа в рабочей книге.
При необходимости в поле "Комментарий" введите описательный комментарий длиной до 255 символов.
В поле Относится к выполните одно из следующих действий:
Чтобы ввести ссылку на ячейку, введите ссылку на ячейку.
Совет. Текущий выбор вводится по умолчанию. Чтобы ввести другие ссылки на ячейки в качестве аргумента, нажмите «Свернуть диалоговое окно» (что временно свернет диалоговое окно), выберите ячейки на листе и нажмите «Развернуть диалоговое окно» .
Чтобы ввести константу, введите = (знак равенства), а затем введите значение константы.
Чтобы ввести формулу, введите =, а затем введите формулу.
Чтобы завершить работу и вернуться к рабочему листу, нажмите кнопку "ОК".
Совет. Чтобы сделать диалоговое окно "Новое имя" шире или длиннее, нажмите и перетащите ручку внизу.
Управление именами с помощью диалогового окна "Диспетчер имен"
Используйте диалоговое окно "Диспетчер имен" для работы со всеми определенными именами и именами таблиц в книге. Например, вам может понадобиться найти имена с ошибками, подтвердить значение и ссылку на имя, просмотреть или отредактировать описательные комментарии или определить область действия. Вы также можете сортировать и фильтровать список имен, а также легко добавлять, изменять или удалять имена из одного места.
Чтобы открыть диалоговое окно "Диспетчер имен", на вкладке "Формулы" в группе "Определенные имена" нажмите "Диспетчер имен".
Диалоговое окно "Диспетчер имен" отображает следующую информацию о каждом имени в списке:
Этот столбец:
Один из следующих:
Определенное имя, обозначенное значком определенного имени.
Имя таблицы, обозначенное значком имени таблицы.
Текущее значение имени, например результат формулы, строковая константа, диапазон ячеек, ошибка, массив значений или заполнитель, если формулу невозможно вычислить. Ниже приведены типичные примеры:
"это моя строковая константа"
Текущая ссылка на имя. Ниже приведены типичные примеры:
Имя рабочего листа, если областью действия является локальный уровень рабочего листа.
"Рабочая книга", если областью действия является глобальный уровень рабочего листа.
Дополнительная информация об имени до 255 символов. Ниже приведены типичные примеры:
Срок действия этого значения истекает 2 мая 2007 г.
Не удалять! Критическое имя!
На основании номеров сертификационных экзаменов ISO.
Вы не можете использовать диалоговое окно "Диспетчер имен" во время изменения содержимого ячейки.
Диалоговое окно «Диспетчер имен» не отображает имена, определенные в Visual Basic для приложений (VBA), или скрытые имена (для свойства Visible имени установлено значение «False»).
Чтобы автоматически изменить размер столбца в соответствии с самым длинным значением в этом столбце, дважды щелкните правую часть заголовка столбца.
Чтобы отсортировать список имен в порядке возрастания или убывания, щелкните заголовок столбца.
Используйте команды в раскрывающемся списке «Фильтр», чтобы быстро отобразить подмножество имен. Выбор каждой команды включает или выключает операцию фильтрации, что позволяет легко комбинировать или удалять различные операции фильтрации для получения желаемых результатов.
Чтобы отфильтровать список имен, выполните одно или несколько из следующих действий:
Имена, относящиеся к рабочему листу
Отображать только те имена, которые являются локальными для рабочего листа.
Имена в рабочей книге
Отображать только те имена, которые являются глобальными для книги.
Имена с ошибками
Имена без ошибок
Отображать только те имена, значения которых не содержат ошибок.
Заданные имена
Отображать только имена, определенные вами или Excel, например область печати.
Имена таблиц
Отображать только имена таблиц.
Если вы измените определенное имя или имя таблицы, все варианты использования этого имени в книге также изменятся.
На вкладке "Формулы" в группе "Определенные имена" нажмите "Диспетчер имен".
В диалоговом окне "Диспетчер имен" выберите имя, которое хотите изменить, а затем нажмите "Изменить".
Совет. Можно также дважды щелкнуть имя.
В диалоговом окне "Изменить имя" в поле "Имя" введите новое имя ссылки.
В поле "Ссылается на" измените ссылку и нажмите "ОК".
В диалоговом окне "Диспетчер имен" в поле "Ссылка" измените ячейку, формулу или константу, представленную именем.
Чтобы отменить нежелательные или случайные изменения, нажмите "Отмена" или нажмите клавишу ESC.
Чтобы сохранить изменения, нажмите "Подтвердить" или нажмите клавишу ВВОД.
Кнопка "Закрыть" закрывает только диалоговое окно "Диспетчер имен". Это не требуется для уже внесенных изменений.
На вкладке "Формулы" в группе "Определенные имена" нажмите "Диспетчер имен".
В диалоговом окне "Диспетчер имен" щелкните имя, которое хотите изменить.
Выберите одно или несколько имен, выполнив одно из следующих действий:
Чтобы выбрать имя, нажмите на него.
Чтобы выбрать более одного имени в непрерывной группе, щелкните и перетащите имена или нажмите клавишу SHIFT и щелкните кнопку мыши для каждого имени в группе.
Чтобы выбрать более одного имени в несмежной группе, нажмите клавишу CTRL и щелкните кнопкой мыши для каждого имени в группе.
Нажмите «Удалить». Вы также можете нажать DELETE.
Нажмите "ОК", чтобы подтвердить удаление.
Кнопка "Закрыть" закрывает только диалоговое окно "Диспетчер имен". Это не требуется для уже внесенных изменений.
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.
Если вы работаете с электронными таблицами Excel, это может означать большую экономию времени и эффективность.
В этом руководстве вы узнаете, как создавать именованные диапазоны в Excel и как использовать их для экономии времени.
Это руководство охватывает:
Именованные диапазоны в Excel — введение
Если кому-то нужно позвонить мне или обратиться ко мне, они будут использовать мое имя (вместо того, чтобы говорить, что мужчина находится в таком-то месте с таким-то ростом и весом).
Аналогично в Excel вы можете дать имя ячейке или диапазону ячеек.
Теперь вместо ссылки на ячейку (например, A1 или A1:A10) вы можете просто использовать присвоенное ей имя.
Например, предположим, что у вас есть набор данных, как показано ниже:
В этом наборе данных, если вам нужно обратиться к диапазону с датой, вам придется использовать A2:A11 в формулах. Точно так же для торговых представителей и продаж вам придется использовать B2:B11 и C2:C11.
Хотя это нормально, когда у вас есть только несколько точек данных, но если у вас огромные сложные наборы данных, использование ссылок на ячейки для ссылки на данные может занять много времени.
Именованные диапазоны Excel упрощают обращение к наборам данных в Excel.
Вы можете создать именованный диапазон в Excel для каждой категории данных, а затем использовать это имя вместо ссылок на ячейки. Например, даты могут называться «Дата», данные о торговых представителях могут называться «Представители по продажам», а данные о продажах могут называться «Продажи».
Вы также можете создать имя для отдельной ячейки. Например, если в ячейке указан процент комиссии за продажу, вы можете назвать эту ячейку «Комиссия».
Преимущества создания именованных диапазонов в Excel
Вот преимущества использования именованных диапазонов в Excel.
Используйте имена вместо ссылок на ячейки
При создании именованных диапазонов в Excel можно использовать эти имена вместо ссылок на ячейки.
Например, вы можете использовать =СУММ(ПРОДАЖИ) вместо =СУММ(C2:C11) для приведенного выше набора данных.
Посмотрите на приведенные ниже формулы. Вместо ссылок на ячейки я использовал именованные диапазоны.
- Количество продаж со значением более 500: =СЧЁТЕСЛИ(Продажи,">500″)
- Сумма всех продаж, совершенных Томом: =СУММЕСЛИ(СУММЕСЛИ(Представитель по продажам, "Том",Продажи)
- Комиссия, полученная Джо (продажи Джо, умноженные на комиссионный процент):
=СУММЕСЛИ(SUMIF(SalesRep»,Joe»,Sales)*Commission
Вы согласитесь, что эти формулы легко создавать и легко понимать (особенно когда вы делитесь ими с кем-то еще или пересматриваете сами).
Нет необходимости возвращаться к набору данных для выбора ячеек
Еще одно существенное преимущество использования именованных диапазонов в Excel заключается в том, что вам не нужно возвращаться назад и выбирать диапазоны ячеек.
Вы можете просто ввести пару букв этого именованного диапазона, и Excel покажет соответствующие именованные диапазоны (как показано ниже):
Именованные диапазоны делают формулы динамичными
Используя именованные диапазоны в Excel, вы можете сделать формулы Excel динамическими.
Например, в случае комиссии с продаж вместо значения 2,5 % можно использовать именованный диапазон.
Теперь, если позже ваша компания решит увеличить комиссию до 3%, вы можете просто обновить Именованный диапазон, и все расчеты будут автоматически обновлены, чтобы отразить новую комиссию.
Как создавать именованные диапазоны в Excel
Вот три способа создания именованных диапазонов в Excel:
Вот шаги для создания именованных диапазонов в Excel с помощью определения имени:
Это создаст именованный диапазон SALESREP.
Это рекомендуемый способ, когда у вас есть данные в табличной форме и вы хотите создать именованный диапазон для каждого столбца/строки.
Например, в приведенном ниже наборе данных, если вы хотите быстро создать три именованных диапазона (Date, Sales_Rep и Sales), вы можете использовать метод, показанный ниже.
Вот шаги, чтобы быстро создать именованные диапазоны из набора данных:
Это создаст три именованных диапазона — Date, Sales_Rep и Sales.
Обратите внимание, что он автоматически выбирает имена из заголовков. Если между словами есть пробел, вставляется символ подчеркивания (поскольку в именованных диапазонах пробелов быть не может).
Соглашение об именах для именованных диапазонов в Excel
Существуют определенные правила именования, которые необходимо знать при создании именованных диапазонов в Excel:
- Первым символом именованного диапазона должна быть буква и символ подчеркивания (_) или обратная косая черта (\). Если это что-то другое, он покажет ошибку. Остальные символы могут быть буквами, цифрами, специальными символами, точкой или символом подчеркивания.
- Вы не можете использовать имена, которые также представляют ссылки на ячейки в Excel. Например, вы не можете использовать AB1, так как это также ссылка на ячейку.
- Вы не можете использовать пробелы при создании именованных диапазонов. Например, у вас не может быть торгового представителя в качестве именованного диапазона. Если вы хотите объединить два слова и создать именованный диапазон, используйте для его создания символ подчеркивания, точку или символы верхнего регистра. Например, у вас может быть Sales_Rep, SalesRep или SalesRep.
- При создании именованных диапазонов Excel одинаково обрабатывает прописные и строчные буквы. Например, если вы создадите именованный диапазон ПРОДАЖИ, вы не сможете создать другой именованный диапазон, такой как «продажи» или «Продажи».
Слишком много именованных диапазонов в Excel? Не волнуйтесь
Иногда при работе с большими наборами данных и сложными моделями в Excel может создаваться множество именованных диапазонов.
Что делать, если вы не помните название созданного вами именованного диапазона?
Не волнуйтесь — вот несколько полезных советов.
Получение имен всех именованных диапазонов
Вот как получить список всех созданных вами именованных диапазонов:
Это даст вам список всех именованных диапазонов в этой книге. Чтобы использовать именованный диапазон (в формулах или ячейке), дважды щелкните по нему.
Отображение совпадающих именованных диапазонов
Если вы уже создали именованный диапазон, вы можете изменить его, выполнив следующие действия:
Полезные ярлыки именованных диапазонов (сила F3)
Вот несколько полезных сочетаний клавиш, которые пригодятся при работе с именованными диапазонами в Excel:
- Чтобы получить список всех именованных диапазонов и вставить его в формулу: F3.
- Чтобы создать новое имя с помощью диалогового окна диспетчера имен, нажмите Control + F3.
- Чтобы создать именованные диапазоны из выделенного: Control + Shift + F3.
Создание динамических именованных диапазонов в Excel
До сих пор в этом руководстве мы создавали статические именованные диапазоны.
Это означает, что эти именованные диапазоны всегда будут ссылаться на один и тот же набор данных.
Например, если A1:A10 называется "Продажи", это всегда будет относиться к A1:A10.
Если вы добавите больше данных о продажах, вам придется вручную обновить ссылку в именованном диапазоне.
В мире постоянно расширяющихся наборов данных это может занять у вас много времени. Каждый раз, когда вы получаете новые данные, вам, возможно, придется обновлять именованные диапазоны в Excel.
Чтобы решить эту проблему, мы можем создать динамические именованные диапазоны в Excel, которые будут автоматически учитывать дополнительные данные и включать их в существующий именованный диапазон.
Например, например, если я добавлю две дополнительные точки данных о продажах, динамический именованный диапазон будет автоматически ссылаться на A1:A12.
Этот вид динамического именованного диапазона можно создать с помощью функции Excel ИНДЕКС. Вместо указания ссылок на ячейки при создании именованного диапазона мы указываем формулу. Формула автоматически обновляется при добавлении или удалении данных.
Давайте посмотрим, как создавать динамические именованные диапазоны в Excel.
Предположим, у нас есть данные о продажах в ячейке A2:A11.
Вот шаги для создания динамических именованных диапазонов в Excel:
Теперь у вас есть динамический именованный диапазон с названием "Продажи". Он будет автоматически обновляться всякий раз, когда вы добавляете в него данные или удаляете из него данные.
Как работают динамические именованные диапазоны?
Чтобы объяснить, как это работает, вам нужно немного больше узнать о функции ИНДЕКС в Excel.
Большинство людей используют ИНДЕКС для возврата значения из списка на основе номера строки и столбца.
Но у функции ИНДЕКС есть и другая сторона.
Его можно использовать для возврата ссылки на ячейку, когда она используется как часть ссылки на ячейку.
Например, вот формула, которую мы использовали для создания динамического именованного диапазона:
INDEX($A$2:$A$100,COUNTIF($A$2:$A$100,"<>"&"") –> Ожидается, что эта часть формулы вернет значение (которое будет 10-м значение из списка, учитывая, что элементов десять).
Однако при использовании перед ссылкой (= $A$2: ИНДЕКС($A$2:$A$100,СЧЁТЕСЛИ($A$2:$A$100,"<>"&"")) возвращается ссылка на ячейку вместо значения.
Следовательно, здесь возвращается =$A$2:$A$11
Если мы добавим два дополнительных значения в столбец продаж, он вернет =$A$2:$A$13
При добавлении новых данных в список функция Excel СЧЁТЕСЛИ возвращает количество непустых ячеек в данных. Этот номер используется функцией ИНДЕКС для извлечения ссылки на ячейку последнего элемента в списке.
Примечание:
- Это будет работать, только если в данных нет пустых ячеек.
- В приведенном выше примере я назначил большое количество ячеек (A2:A100) для формулы именованного диапазона. Вы можете изменить это в зависимости от вашего набора данных.
Вы также можете использовать функцию OFFSET для создания динамических именованных диапазонов в Excel, однако, поскольку функция OFFSET нестабильна, это может привести к медленной работе книги Excel. ИНДЕКС, с другой стороны, является полузависимым, что делает его лучшим выбором для создания динамических именованных диапазонов в Excel.
Используйте функцию PROPER для написания имен с заглавной буквы в Excel
поиск меню
Урок 21. Использование функции ПРОПИСЬ для написания имен с заглавной буквы в Excel
Используйте функцию ПРОПИСЬ, чтобы использовать заглавные буквы в именах в Excel
Когда вы думаете о функциях Excel, вы, вероятно, думаете о выполнении вычислений с числами. Хотя вы действительно можете использовать функции для выполнения множества полезных действий с числами в Excel, некоторые функции также могут помочь вам отформатировать текст. Одним из хороших примеров является функция PROPER, которая делает заглавной первую букву каждого слова в ячейке. Если у вас есть ячейки, содержащие имена собственные, такие как имена или титулы, вы можете использовать функцию PROPER, чтобы убедиться, что все написано правильно. Функция PROPER работает и в Google Таблицах.
Например, ваша компания хочет вручить кому-то награду за достижения в жизни. Вы попросили своих коллег ввести свои кандидатуры на награду в эту таблицу:
К сожалению, вы можете видеть, что не все позаботились о том, чтобы написать с большой буквы имена и фамилии людей, которых они хотят номинировать, поэтому таблица выглядит беспорядочно. Вы можете просмотреть столбец и исправить имена вручную, но использование функции ПРОПИСЬ будет быстрее и проще.
В этом примере имена номинантов находятся в столбце A, поэтому мы поместим нашу формулу в столбец B. В ячейку B2 мы введем формулу, которая сообщает Excel, что имя в ячейке A2 должно быть написано с заглавной буквы, что содержит первое имя в нашем списке. Формула будет выглядеть так:
=ПРАВИЛЬНЫЙ(A2)
Как вы, возможно, помните из нашего урока "Простые формулы" в нашем учебнике по формулам Excel, важно убедиться, что вы начинаете любую формулу Excel со знака равенства. После того, как вы ввели формулу, нажмите клавишу Enter, и в ячейке B2 отобразится имя из A2 с правильной регистрацией заглавных букв: Томас Линли.
Теперь все, что нам нужно сделать, это щелкнуть и перетащить маркер заполнения через ячейку A14, и в столбце B отобразятся все имена в списке с правильным написанием заглавных букв:
Отлично! Теперь все имена номинантов в таблице правильно пишутся с заглавной буквы. Однако есть одна проблема: у нас все еще есть исходные имена без заглавных букв в столбце A. Мы не можем удалить столбец A, потому что наша формула в столбце B ссылается на него. Вместо этого мы можем скопировать значения из столбца B в новый столбец с помощью функции «Вставить значения» в Excel.
Для этого выделите ячейки B2:B14 и нажмите команду "Копировать" (или нажмите Ctrl+C на клавиатуре). Затем щелкните правой кнопкой мыши ячейку, в которую вы хотите вставить значения (например, C2), а затем выберите кнопку «Значения» в появившемся меню. Если вы используете Google Таблицы, вы можете щелкнуть правой кнопкой мыши и выбрать Специальная вставка > Вставить только значения.
Теперь у нас есть столбец, в котором отображаются исправленные имена, но это не зависит от формулы или ссылки на ячейку. Это означает, что мы можем удалить наши исходные столбцы (столбец A и столбец B). Вот и все: красивая, аккуратная таблица со всеми именами номинантов, написанными с заглавной буквы.
Читайте также: