Как создать именованные диапазоны в Excel (пошаговое руководство)

Что в названии?

Если вы работаете с электронными таблицами Excel, это может означать значительную экономию времени и эффективность.

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

Именованные диапазоны в Excel - Введение

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

Правильно?

Точно так же в Excel вы можете дать имя ячейке или диапазону ячеек.

Теперь вместо использования ссылки на ячейку (например, A1 или A1: A10) вы можете просто использовать присвоенное ей имя.

Например, предположим, что у вас есть набор данных, как показано ниже:

В этом наборе данных, если вам нужно обратиться к диапазону с датой, вам придется использовать в формулах A2: A11. Точно так же для торгового представителя и отдела продаж вам придется использовать B2: B11 и C2: C11.

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

Именованные диапазоны Excel упрощают обращение к наборам данных в Excel.

Вы можете создать именованный диапазон в Excel для каждой категории данных, а затем использовать это имя вместо ссылок на ячейки. Например, даты могут называться «Дата», данные торгового представителя - «SalesRep», а данные о продажах - «Продажи».

Вы также можете создать имя для отдельной ячейки. Например, если в ячейке указан процент комиссии с продаж, вы можете назвать эту ячейку «Комиссия».

Преимущества создания именованных диапазонов в Excel

Вот преимущества использования именованных диапазонов в Excel.

Используйте имена вместо ссылок на ячейки

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

Например, вы можете использовать = СУММ (ПРОДАЖИ) вместо = СУММ (C2: C11) для указанного выше набора данных.

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

• Количество продаж со значением более 500: = СЧЁТЕСЛИ (Продажи, "> 500 ″)
• Сумма всех продаж, выполненных Томом: = СУММЕСЛИ (SalesRep, «Том», Продажи)
• Комиссия, заработанная Джо (продажи Джо, умноженные на процент комиссии):
  = СУММЕСЛИ (SalesRep; «Джо»; Продажи) * Комиссия

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

Нет необходимости возвращаться к набору данных для выбора ячеек

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

Вы можете просто ввести пару алфавитов этого именованного диапазона, и Excel покажет соответствующие именованные диапазоны (как показано ниже):

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

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

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

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

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

Вот три способа создания именованных диапазонов в Excel:

Метод №1 - Использование определения имени

Вот шаги для создания именованных диапазонов в Excel с помощью определения имени:

• Выберите диапазон, для которого вы хотите создать именованный диапазон в Excel.
• Перейдите в Формулы -> Определить имя.
• В диалоговом окне «Новое имя» введите имя, которое вы хотите присвоить выбранному диапазону данных. Вы можете указать область как всю книгу или конкретный рабочий лист. Если вы выберете конкретный лист, имя не будет доступно на других листах.
• Щелкните ОК.

Это создаст именованный диапазон SALESREP.

Метод № 2: Использование поля имени

• Выберите диапазон, для которого вы хотите создать имя (не выбирайте заголовки).
• Перейдите в поле имени слева от панели формул и введите имя, с которым вы хотите создать именованный диапазон.
• Обратите внимание, что созданное здесь Имя будет доступно для всей Рабочей книги. Если вы хотите ограничить его рабочим листом, используйте метод 1.

Метод № 3: Использование параметра «Создать из выделенного фрагмента»

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

Например, в наборе данных ниже, если вы хотите быстро создать три именованных диапазона (Date, Sales_Rep и Sales), вы можете использовать метод, показанный ниже.

Вот шаги, чтобы быстро создать именованные диапазоны из набора данных:

• Выберите весь набор данных (включая заголовки).
• Перейдите в Формулы -> Создать из выделенного (Сочетание клавиш - Control + Shift + F3). Откроется диалоговое окно «Создать имена из выделенного».
• В диалоговом окне «Создать имена из выделенного» отметьте параметры, в которых у вас есть заголовки. В этом случае мы выбираем только верхнюю строку, поскольку заголовок находится в верхней строке. Если у вас есть заголовки и в верхнем ряду, и в левом столбце, вы можете выбрать оба. Точно так же, если ваши данные упорядочены, когда заголовки находятся только в левом столбце, вы выбираете только параметр «Левый столбец».

Это создаст три именованных диапазона - Date, Sales_Rep и Sales.

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

Соглашение об именах для именованных диапазонов в Excel

При создании именованных диапазонов в Excel необходимо знать определенные правила именования:

• Первым символом именованного диапазона должна быть буква и символ подчеркивания (_) или обратная косая черта (\). Если это что-то еще, отобразится ошибка. Остальные символы могут быть буквами, цифрами, специальными символами, точкой или подчеркиванием.
• Вы не можете использовать имена, которые также представляют ссылки на ячейки в Excel. Например, вы не можете использовать AB1, поскольку это также ссылка на ячейку.
• При создании именованных диапазонов нельзя использовать пробелы. Например, у вас не может быть торгового представителя в качестве именованного диапазона. Если вы хотите объединить два слова и создать именованный диапазон, используйте для его создания символы подчеркивания, точки или прописные буквы. Например, у вас может быть Sales_Rep, SalesRep или SalesRep.
  • При создании именованных диапазонов Excel обрабатывает прописные и строчные буквы одинаково. Например, если вы создаете именованный диапазон SALES, вы не сможете создать другой именованный диапазон, такой как «sales» или «Sales».
• Именованный диапазон может содержать до 255 символов.

Слишком много именованных диапазонов в Excel? Не волнуйся

Иногда в больших наборах данных и сложных моделях вы можете создать много именованных диапазонов в Excel.

Что делать, если вы не помните название созданного вами именованного диапазона?

Не волнуйся - вот несколько полезных советов.

Получение имен всех именованных диапазонов

Вот шаги, чтобы получить список всех именованных диапазонов, которые вы создали:

• Перейдите на вкладку "Формулы".
• В группе «Определенные именованные» нажмите «Использовать в формуле».
• Нажмите «Вставить имена».

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

Отображение совпадающих именованных диапазонов

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

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

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

• Перейдите на вкладку "Формулы" и нажмите "Диспетчер имен".
• В диалоговом окне «Диспетчер имен» будут перечислены все именованные диапазоны в этой книге. Дважды щелкните именованный диапазон, который вы хотите отредактировать.
• В диалоговом окне «Изменить имя» внесите изменения.
• Щелкните ОК.
• Закройте диалоговое окно Диспетчер имен.

Полезные ярлыки именованного диапазона (сила F3)

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

• Чтобы получить список всех именованных диапазонов и вставить его в формулу: F3
• Чтобы создать новое имя с помощью диалогового окна диспетчера имен: Ctrl + F3
• Чтобы создать именованные диапазоны из выделения: Control + Shift + F3

Создание динамических именованных диапазонов в Excel

До сих пор в этом руководстве мы создали статические именованные диапазоны.

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

Например, если A1: A10 был назван «Продажи», он всегда будет относиться к A1: A10.

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

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

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

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

Такой тип динамического именованного диапазона можно создать с помощью функции ИНДЕКС Excel. Вместо указания ссылок на ячейки при создании именованного диапазона мы указываем формулу. Формула автоматически обновляется при добавлении или удалении данных.

Давайте посмотрим, как создавать динамические именованные диапазоны в Excel.

Предположим, у нас есть данные о продажах в ячейке A2: A11.

Вот шаги для создания динамических именованных диапазонов в Excel:

1. 1. Перейдите на вкладку «Формула» и нажмите «Определить имя».
   2. В диалоговом окне «Новое имя» введите следующее:
      • Имя: Продажи
      • Объем: Рабочая тетрадь
      • Относится к: = $ A $ 2: ИНДЕКС ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))
   3. Щелкните ОК.

Готово!

Теперь у вас есть динамический именованный диапазон с названием «Продажи». Это будет автоматически обновляться всякий раз, когда вы добавляете в него или удаляете данные.

Как работают динамические именованные диапазоны?

Чтобы объяснить, как это работает, вам нужно немного больше узнать о функции ИНДЕКС в Excel.

Большинство людей используют ИНДЕКС для возврата значения из списка на основе номера строки и столбца.

Но у функции ИНДЕКС есть и другая сторона.

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

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

= $ A $ 2: ИНДЕКС ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, "" & ""))

ИНДЕКС ($ A $ 2: $ A $ 100, СЧЁТЕСЛИ ($ A $ 2: $ A $ 100, ”” & ””) -> Ожидается, что эта часть формулы вернет значение (которое будет 10-м значением из списка, учитывая, что есть десять пунктов).

Однако при использовании перед ссылкой (=2 доллара США:INDEX ($ A $ 2: $ A $ 100, COUNTIF ($ A $ 2: $ A $ 100, ”” & ””))) он возвращает ссылку на ячейку вместо значения.

Следовательно, здесь он возвращает = $ A $ 2: $ A $ 11

Если мы добавим два дополнительных значения в столбец продаж, он вернет = $ A $ 2: $ A $ 13.

Когда вы добавляете новые данные в список, функция Excel СЧЁТЕСЛИ возвращает количество непустых ячеек в данных. Этот номер используется функцией ИНДЕКС для получения ссылки на ячейку последнего элемента в списке.

Примечание:

• Это будет работать, только если в данных нет пустых ячеек.
• В приведенном выше примере я назначил большое количество ячеек (A2: A100) для формулы именованного диапазона. Вы можете настроить это на основе вашего набора данных.

Вы также можете использовать функцию СМЕЩЕНИЕ для создания динамических именованных диапазонов в Excel, однако, поскольку функция СМЕЩЕНИЕ непостоянна, она может привести к медленной работе книги Excel. Индекс, с другой стороны, является полу-летучим, что делает его лучшим выбором для создания динамических именованных диапазонов в Excel.

Вам также могут понравиться следующие ресурсы Excel:

• Бесплатные шаблоны Excel.
• Бесплатное онлайн-обучение по Excel (онлайн-видеокурс из 7 частей).
• Полезные примеры макрокода Excel.
• 10 расширенных примеров ВПР в Excel.
• Создание выпадающего списка в Excel.
• Создание именованного диапазона в Google Таблицах.
• Как сослаться на другой лист или книгу в Excel