Excel

Имя excel: Присвоить имя в Excel — НА ПРИМЕРАХ

Содержание

Присвоить имя в Excel — НА ПРИМЕРАХ

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

Рассмотрим примеры присвоения имен.

Присвоить имя ячейке

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

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

Итак, первый способ. Выделяем ячейку, которой нам нужно присвоить имя, кликаем по полю Имя и вводим необходимое имя, в данном случае имя «Скидка».

Данный способ самый простой и быстрый.

 

Присвоить имя в Excel – Присвоить имя ячейке

Второй способ.

Это способ предусматривает использование диалогового окна, которое вызывается путем «Вставка»—> «Имя»—> «Определить…».

Сравним результаты и формы записи вычислений в режиме формул.

Присвоить имя в Excel – Результаты расчетов двумя способами

Результаты расчётов абсолютно идентичны.

 

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

Выделяем диапазон D3:D6, где содержится стоимость по каждому товару. В поле Имя присваиваем этому диапазону имя «Стоимость», а в ячейке D7 вводим формулу =СУММ(Стоимость).

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

Присвоить имя в Excel – Присвоить имя диапазону

Рассмотрим синтаксические правила, которые следует соблюдать при создании и изменении имен.

Синтаксические правила для имен

  1. Допустимые символы.
    Первым символом имени должна быть буква, знак подчеркивания или обратная косая черта. Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания.

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

  1. Имена в виде ссылок на ячейки запрещены. Имена не могут быть такими же, как ссылки на ячейки (например, $H7 или R7C8)
  2. Пробелы не допускаются. В качестве разделителей можно использовать символ нижнего подчеркивания (_) и точку (.), например, «Стоимость_со_скидкой» или «Стоимость.Скидка».
  3. Длина имени не должна превышать 255 символов.
  4. Учет регистра. Имя может состоять из строчных и прописных букв. Учтите, что создать два имени «Стоимость» и «СТОИМОСТЬ» у вас не получится. Excel предложит выбрать уникальное имя

 

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

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

Манипуляции с именованными областями

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

Существуют обязательные требования к наименованию группы ячеек:

  • В нём не должно быть пробелов;
  • Оно обязательно должно начинаться с буквы;
  • Его длина не должна быть больше 255 символов;
  • Оно не должно быть представлено координатами вида A1 или R1C1;
  • В книге не должно быть одинаковых имен.

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

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

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

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

  1. Самый быстрый и простой вариант присвоения названия массиву – это записать его в поле имен после выделения соответствующей области. Итак, выделяем массив и вводим в поле то название, которое считаем нужным. Желательно, чтобы оно легко запоминалось и отвечало содержимому ячеек. И, безусловно, необходимо, чтобы оно отвечало обязательным требованиям, которые были изложены выше.

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

Выше был назван самый быстрый вариант наделения наименованием массива, но он далеко не единственный. Эту процедуру можно произвести также через контекстное меню

  1. Выделяем массив, над которым требуется выполнить операцию. Клацаем по выделению правой кнопкой мыши. В открывшемся списке останавливаем выбор на варианте «Присвоить имя…».

  2. Открывается окошко создания названия. В область «Имя» следует вбить наименование в соответствии с озвученными выше условиями. В области
    «Диапазон»     отображается адрес выделенного массива. Если вы провели выделение верно, то вносить изменения в эту область не нужно. Жмем по кнопке «OK».

  3. Как можно видеть в поле имён, название области присвоено успешно.

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

  1. Выделяем область ячеек, которую требуется преобразовать в именованную. Передвигаемся во вкладку «Формулы». В группе «Определенные имена» производим клик по значку «Присвоить имя».

  2. Открывается точно такое же окно присвоения названия, как и при использовании предыдущего варианта. Все дальнейшие операции выполняются абсолютно аналогично.

Последний вариант присвоения названия области ячеек, который мы рассмотрим, это использование

Диспетчера имен.

  1. Выделяем массив. На вкладке «Формулы», клацаем по крупному значку «Диспетчер имен», расположенному всё в той же группе «Определенные имена». Или же можно вместо этого применить нажатие сочетания клавиш Ctrl+F3.

  2. Активируется окно Диспетчера имён. В нем следует нажать на кнопку «Создать…» в верхнем левом углу.

  3. Затем запускается уже знакомое окошко создания файлов, где нужно провести те манипуляции, о которых шёл разговор выше. То имя, которое будет присвоено массиву, отобразится в Диспетчере. Его можно будет закрыть, нажав на стандартную кнопку закрытия в правом верхнем углу.

Урок: Как присвоить название ячейке в Экселе

Операции с именованными диапазонами

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

На одном листе у нас перечень моделей компьютерной техники. У нас стоит задача на втором листе в таблице сделать выпадающий список из данного перечня.

  1. Прежде всего, на листе со списком присваиваем диапазону наименование любым из тех способов, о которых шла речь выше. В итоге, при выделении перечня в поле имён у нас должно отображаться наименование данного массива. Пусть это будет наименование «Модели».

  2. После этого перемещаемся на лист, где находится таблица, в которой нам предстоит создать выпадающий список. Выделяем область в таблице, в которую планируем внедрить выпадающий список. Перемещаемся во вкладку «Данные» и щелкаем по кнопке «Проверка данных» в блоке инструментов «Работа с данными» на ленте.

  3. В запустившемся окне проверки данных переходим во вкладку «Параметры». В поле «Тип данных» выбираем значение «Список». В поле «Источник» в обычном случае нужно либо вручную вписать все элементы будущего выпадающего списка, либо дать ссылку на их перечень, если он расположен в документе. Это не очень удобно, особенно, если перечень располагается на другом листе. Но в нашем случае все намного проще, так как мы соответствующему массиву присвоили наименование. Поэтому просто ставим знак «равно» и записываем это название в поле. Получается следующее выражение:

    =Модели

    Жмем по «OK».

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

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

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

Итак, мы имеем таблицу, в которой помесячно расписана выручка пяти филиалов предприятия. Нам нужно узнать общую выручку по Филиалу 1, Филиалу 3 и Филиалу 5 за весь период, указанный в таблице.

  1. Прежде всего, каждой строке соответствующего филиала в таблице присвоим название. Для Филиала 1 выделяем область с ячейками, в которых содержатся данные о выручке по нему за 3 месяца. После выделения в поле имен пишем наименование «Филиал_1» (не забываем, что название не может содержать пробел) и щелкаем по клавише Enter. Наименование соответствующей области будет присвоено. При желании можно использовать любой другой вариант присвоения наименования, о котором шел разговор выше.

  2. Таким же образом, выделяя соответствующие области, даем названия строкам и других филиалов: «Филиал_2», «Филиал_3», «Филиал_4», «Филиал_5».

  3. Выделяем элемент листа, в который будет выводиться итог суммирования. Клацаем по иконке «Вставить функцию».

  4. Инициируется запуск Мастера функций. Производим перемещение в блок «Математические». Останавливаем выбор из перечня доступных операторов на наименовании «СУММ».

  5. Происходит активация окошка аргументов оператора СУММ. Данная функция, входящая в группу математических операторов, специально предназначена для суммирования числовых значений. Синтаксис представлен следующей формулой:

    =СУММ(число1;число2;…)

    Как нетрудно понять, оператор суммирует все аргументы группы «Число». В виде аргументов могут применяться, как непосредственно сами числовые значения, так и ссылки на ячейки или диапазоны, где они расположены. В случае применения массивов в качестве аргументов используется сумма значений, которая содержится в их элементах, подсчитанная в фоновом режиме. Можно сказать, что мы «перескакиваем», через действие. Именно для решения нашей задачи и будет использоваться суммирование диапазонов.

    Всего оператор СУММ может насчитывать от одного до 255 аргументов. Но в нашем случае понадобится всего три аргумента, так как мы будет производить сложение трёх диапазонов: «Филиал_1», «Филиал_3» и «Филиал_5».

    Итак, устанавливаем курсор в поле «Число1». Так как мы дали названия диапазонам, которые требуется сложить, то не нужно ни вписывать координаты в поле, ни выделять соответствующие области на листе. Достаточно просто указать название массива, который подлежит сложению: «Филиал_1». В поля «Число2» и «Число3» соответственно вносим запись «Филиал_3» и «Филиал_5». После того, как вышеуказанные манипуляции были сделаны, клацаем по «OK».

  6. Результат вычисления выведен в ячейку, которая была выделена перед переходом в Мастер функций.

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

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

Урок: Как посчитать сумму в Майкрософт Эксель

Управление именованными диапазонами

Управлять созданными именованными диапазонами проще всего через Диспетчер имен. При помощи данного инструмента можно присваивать имена массивам и ячейкам, изменять существующие уже именованные области и ликвидировать их. О том, как присвоить имя с помощью Диспетчера мы уже говорили выше, а теперь узнаем, как производить в нем другие манипуляции.

  1. Чтобы перейти в Диспетчер, перемещаемся во вкладку «Формулы». Там следует кликнуть по иконке, которая так и называется «Диспетчер имен». Указанная иконка располагается в группе «Определенные имена».

  2. После перехода в Диспетчер для того, чтобы произвести необходимую манипуляцию с диапазоном, требуется найти его название в списке. Если перечень элементов не очень обширный, то сделать это довольно просто. Но если в текущей книге располагается несколько десятков именованных массивов или больше, то для облегчения задачи есть смысл воспользоваться фильтром. Клацаем по кнопке «Фильтр», размещенной в правом верхнем углу окна. Фильтрацию можно выполнять по следующим направлениям, выбрав соответствующий пункт открывшегося меню:
    • Имена на листе;
    • в книге;
    • с ошибками;
    • без ошибок;
    • Определенные имена;
    • Имена таблиц.

    Для того, чтобы вернутся к полному перечню наименований, достаточно выбрать вариант «Очистить фильтр».

  3. Для изменения границ, названия или других свойств именованного диапазона следует выделить нужный элемент в Диспетчере и нажать на кнопку «Изменить…».

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

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

    После того, как редактирование данных окончено, жмем на кнопку «OK».

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

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

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

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

  3. Как видим, выбранный элемент был удален из перечня Диспетчера. Это означает, что массив, к которому он был прикреплен, утратил наименование. Теперь он будет идентифицироваться только по координатам. После того, как все манипуляции в Диспетчере завершены, клацаем по кнопке «Закрыть», чтобы завершить работу в окне.

Применение именованного диапазона способно облегчить работу с формулами, функциями и другими инструментами Excel. Самими именованными элементами можно управлять (изменять и удалять) при помощи специального встроенного Диспетчера.

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТ

VBA Excel. Правильные имена переменных и процедур

Правила наименования переменных, констант, процедур и аргументов пользовательских функций в VBA Excel. Использование суффиксов в именах переменных.

Правильные имена в VBA Excel

Правила наименования переменных, констант, процедур и аргументов:

  • Имя может состоять из букв, цифр и знака подчеркивания (_). Пробел и другие специальные символы не допускаются, кроме шести символов-суффиксов для переменных.
  • Первым знаком имени должна быть буква. Длина имени ограничена 255 знаками.
  • Не следует использовать имена, совпадающие с именами встроенных функций, операторов, методов, свойств и констант VBA Excel.
  • Нельзя допускать повторения имен внутри одной области видимости (действия) переменных, например, внутри одной процедуры.

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

Примеры имен

Переменные: x, y, z, a1, a2, a3, summa, myRange, my_Currency;

Подпрограммы: Primer1(), Raschet_Stazha(), SozdaniyeTablitsy();

Функции с аргументом: СуммаПрописью(x As Double) As String, ПоследнийДеньМесяца(x As Date) As Date.

Области видимости переменных

ПеременнаяОбласть действияСпособ объявления
Локальнаяотдельная процедуравнутри процедуры с помощью ключевого слова Dim или Static
Модульнаяотдельный модульперед первой процедурой в модуле с помощью ключевого слова Dim или Private
Глобальнаявсе стандартные модулиперед первой процедурой в стандартном модуле с помощью ключевого слова Public

Суффиксы в именах переменных

Переменные в VBA Excel могут быть объявлены с одним из следующих типов данных: Byte, Boolean, Integer, Long, Single, Double, Currency, Date, String (для строк переменной или фиксированной длины), Object или Variant. Если при объявлении переменной тип данных не указан, по умолчанию присваивается тип Variant.

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

Символы, назначенные типам данных:

  • % – Integer;
  • & – Long;
  • ! – Single;
  • # – Double;
  • @ – Currency;
  • $ – String.

Примеры объявления переменных

Пример 1

‘Объявление переменных без суффикса

Dim myInteger as Integer, mySingle as Single, myCurrency as Currency

‘Объявление переменных с суффиксом

Dim myInteger%, mySingle!, myCurrency@

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

Пример 2

Если в модуле не используются операторы Option Explicit и Dim (Static, Public), то суффиксы являются единственным способом назначить переменной тип данных. То, что суффиксы работают и в этом случае, проверим на следующей процедуре:

Sub Primer()

a$ = «привет»

a = StrConv(a, 1)

End Sub

Указываем с помощью суффикса $, что переменная a является строковой, и ставим паузу на операторе End Sub. Запускаем процедуру:

На изображении видно, что переменной a присвоен тип данных String (окно Locals). Теперь запускаем ту же процедуру, но с переменной без суффикса:

Как и ожидалось, переменной a присвоен тип данных по умолчанию – Variant. Через косую черту указан тип данных, который VBA Excel идентифицировал у содержимого переменной a.

Как присвоить имя ячейке (диапазону) в таблице Excel

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

Требования к именам ячеек

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

  1. Нельзя использовать пробелы, запятые, двоеточия, точки с запятыми в качестве разделителя слов (выходом из ситуации может быть замена на нижнее подчеркивание или точку).
  2.  Максимальная длина символов – 255.
  3. В начале названия должны быть буквы, нижнее подчеркивание или обратная косая черта (никаких цифр и прочих символов).
  4. Нельзя указывать адрес ячейки или диапазона.
  5. Название должно быть уникальным в рамках одной книги. При этом следует помнить, что буквы в разных регистрах программа будет воспринимать как полностью одинаковые.

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

Допустим, ячейке B2 присвоено имя “Продажа_1”.

Если она будет участвовать в формуле, то вместо B2 мы пишем “Продажа_1”.

Нажав клавишу Enter убеждаемся в том, что формула, действительно, рабочая.

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

Метод 1: строка имен

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

  1. Любым удобным способом, например, с помощью зажатой левой кнопки мыши, выделяем требуемую ячейку или область.
  2. Щелкаем внутри строки имен и вводим нужное название согласно требованиям, описанным выше, после чего нажимаем клавишу Enter на клавиатуре.
  3. В результате мы присвоим выделенному диапазону название. И при выделении данной области в дальнейшем мы будем видеть именно это название в строке имен.
  4. Если имя слишком длинное и не помещается в стандартном поле строки, его правую границу можно сдвинуть с помощью зажатой левой кнопки мыши.

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

Использование контекстного меню в Эксель позволяет выполнить популярные команды и функции. Присвоить имя ячейке также можно через этот инструмент.

  1. Как обычно, для начала нужно отметить ячейку или диапазон ячеек, с которыми хотим выполнить манипуляции.
  2. Затем правой кнопкой мыши щелкаем по выделенной области и в открывшемся перечне выбираем команду “Присвоить имя”.
  3. На экране появится окно, в котором мы:
    • пишем имя в поле напротив одноименного пункта;
    • значение параметра “Поле” чаще всего остается по умолчанию. Здесь указывается границы, в которых будет идентифицироваться наше заданное имя – в пределах текущего листа или всей книги.
    • В области напротив пункта “Примечание” при необходимости добавляем комментарий. Параметр не является обязательным для заполнения.
    • в самом нижнем поле отображаются координаты выделенного диапазона ячеек. Адреса при желании можно отредактировать – вручную или с помощью мыши прямо в таблице, предварительно установив курсор в поле для ввода информации и стерев прежние данные.
    • по готовности жмем кнопку OK.
  4. Все готово. Мы присвоили имя выделенному диапазону.

Метод 3: применение инструментов на ленте

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

  1. Отмечаем нужные элементы. После этого переключаемся во вкладку “Формулы”. В группе “Определенные имена” жмем по кнопке “Задать имя”.
  2. В результате откроется окно, работу в котором мы уже разобрали во втором разделе.

Метод 4: работа в Диспетчере имен

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

  1. Выделив нужный диапазон ячеек (или одну конкретную ячейку) переходим во вкладку “Формулы”, где в блоке “Определенные имена” щелкаем по кнопке “Диспетчер имен”.
  2. На экране появится окно Диспетчера. Здесь мы видим все ранее созданные имена. Чтобы добавить новое нажимаем кнопку “Создать”.
  3. Откроется то же самое окно создания имени, которое мы уже рассматривали выше. Заполняем данные и нажимаем OK. Если при переходе в Диспетчер имен был заранее выделен диапазон ячеек (как в нашем случае), то его координаты автоматически появятся в соответствующем поле. В противном случае, заполняем данные самостоятельно. Как это сделать, описано во втором методе.
  4. Мы снова окажемся в основном окне Диспетчера имен. Здесь же можно удалять или редактировать ранее созданные имена.Для этого достаточно выбрать нужную строку и затем нажать на команду, которую требуется выполнить.
    • при нажатии кнопки “Изменить”, открывается окно изменения имени, в котором мы можем выполнить требуемые корректировки.
    • при нажатии кнопки “Удалить” программа  запросит подтверждение на выполнение операции. Подтверждаем действие, кликнув по кнопке OK.
  5. Когда работа в Диспетчере имен завершена, закрываем его.

Заключение

Присвоение имени отдельной ячейке или диапазону ячеек в Эксель – не самая распространенная операция и используется редко. Однако в некоторых случаях перед пользователем встает такая задача. Сделать это в программе можно различными способами, и можно выбрать тот, что больше нравится и кажется наиболее удобным.

Имя ячейки в Excel | Блог Александра Воробьева

Опубликовано 24 Янв 2016
Рубрика: Справочник Excel | 11 комментариев

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

Имя ячейки – это её точные координаты на поле листа, которые необходимы для ссылок на именно эту ячейку при написании формул Excel.

По умолчанию (традиционно) имя ячейки программа Excel назначает по буквам столбцов и номерам строк. Например, имя B2 означает, что ячейка находится на пересечении столбца B со строкой 2.

Некоторые профессионалы (чаще — программисты) считают более удобным в работе стиль ссылок «R1C1», когда ячейкам рабочего листа Excel присваиваются имена по номерам строк R и номерам столбцов C. Например, R2C2 — это ячейка на пересечении строки 2 со столбцом 2.

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

MS Excel предоставляет возможность «зашифровать координаты» ячейки путем назначения ей почти любого собственного имени, которое следует просто вписать в окно «Имя» строки формул. Однако после создания программы или простого расчета писать каждый раз руками имя ячейки не очень-то хочется, да и по времени это затратное занятие. Но есть быстрый способ переименовать ячейки и получить в строке формул Excel более удобные и понятные записи выражений. При этом совсем не обязательно помнить правила переименований, о которых будет сказано в конце статьи, программа Excel все поправит сама…

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

Пример!

При расчете балки на изгиб требуется вычислить изгибающий момент Mx(z), действующий в расчетном сечении.

Допустим, формула имеет следующий вид:

Mx(z)=R*(z— b1) — F1*(z— b2) — F1*(z— b3) — F1*(z— b4) — F2*(z— b5) — q*(z— b1)2/2

Открываем Excel и создаем таблицу.

1. В ячейки B3-B14 вводим наименования параметров, в ячейки C3-C14 вписываем их буквенно-цифровые обозначения так, как они обозначаются в вышеприведенной формуле (можно со знаками «=», Excel их отбросит при автоматическом присвоении имен), а в ячейки D3-D13 заносим числовые значения исходных данных.

2. Если сейчас ввести в ячейку D14 формулу да еще с применением различных видов ссылок  на ячейки (относительных – типа «A1», абсолютных — типа «$A$1» или смешанных – «A$1» или «$A1»), то в строке формул мы увидим нечто трудно читаемое, что изображено на скриншоте ниже.

3. Чтобы получить иной вид выражения в строке формул, прежде чем вводить расчетное выражение в D14 сделаем так:

3.1. Выделяем диапазон C3-D14.

3.2. Выбираем в главном меню программы MS Excel «Вставка» — «Имя» — «Создать».

3.3. В появившемся окошке «Создать имена» выбираем «По тексту в столбце слева» и закрываем окно кнопкой «ОК».

Теперь ячейкам D3-D14 присвоены имена в соответствии с записями в ячейках C3-C14. После ввода формулы в ячейку D14 вверху в строке формул мы увидим достаточно легко читаемое выражение.

Обратите внимание на то, как Excel назначил имена ячейкам!

У имен переменных F1, F2, R, b1, b2, b3, b4, b5, справа появилось нижнее подчеркивание. Дело в том, что Excel не может разным ячейкам листа дать одинаковые имена! Поэтому, например, ячейке D6 присвоено имя F1_, а не просто F1, так как на листе уже есть ячейка с именем-адресом F1.

Правила назначения имен ячейкам.

1. Можно использовать в именах только буквы, цифры, символы нижнего подчеркивания «_», точки «.» и обратные косые слеши «\».

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

3. Нельзя начинать имя ячейки Excel с цифры.

4. Нельзя назначать имена, совпадающие с уже существующими именами ячеек.

5. Имя ячейки может состоять из одной буквы, исключения — буквы «R» и «C».

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

Для более полного знакомства с темой можно посмотреть выпадающие окна по адресу: главное меню MS Excel «Вставка» — «Имя» — «Присвоить», «Вставить», «Создать», «Применить», «Заголовки диапазонов».

Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце статьи или в окне вверху страницы!

Другие статьи автора блога

На главную

Статьи с близкой тематикой

Отзывы

Почему Excel теперь дает мне уже существующее предупреждение диапазона имен на листе копирования?

я работал над электронной таблицей Microsoft Excel 2007 в течение нескольких дней. Я работаю с основным шаблоном, таким как sheet, и неоднократно копирую его на новый лист. До сегодняшнего дня это происходило без проблем. Однако в середине сегодняшнего дня это вдруг изменилось, и я не знаю, почему. Теперь, всякий раз, когда я пытаюсь скопировать лист, я получаю около десяти диалоговых окон, каждый из которых имеет другой объект диапазона имен (показан ниже как «XXXX»), и я нажимаю да для каждого из них:

формула или лист, который вы хотите переместить или скопировать, содержит имя «XXXX», которое уже существует на целевом листе. Вы хотите использовать эту версию названия?

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

объекты диапазона имен ссылаются на ячейки в лист. Например, E6 называется именем диапазона PRE на нескольких листах (и все вместе) и некоторые формулы относятся к PRE вместо $E. Один из «XXXX» выше это PRE. Эти диапазоны имен должны разрешаться только в пределах листа, на котором они отображаются. Это не было проблемой раньше, несмотря на тот же диапазон имен, существующий на нескольких листах раньше. Я хочу сохранить диапазон имен.

что могло измениться в моей таблице, чтобы вызвать это изменения в поведении? Я вернулся к предыдущим листам, созданным таким образом, и теперь они тоже дают сообщение при копировании. Я попробовал другой компьютер и другого пользователя, и такое же поведение наблюдается повсюду. Я могу только заключить, что что-то в электронной таблице изменилось. Что это может быть и как я могу вернуть старое поведение, при котором я могу копировать листы с диапазонами имен и не получать никаких предупреждений?

ищем в Name Manager Я вижу, что диапазоны имен жалуются на шоу дважды, один раз в область Template и снова как scope Workbook. Если я удалю область Template те, предупреждение уходит на копию, однако, я получаю кучу ошибок #REF. Если я удалю область Workbook ones, все кажется в порядке, и предупреждения о копировании тоже уходят, так что, возможно, это ответ, но я нервничаю о том, какой эффект это удаление будет иметь и интересно, как Workbook они появились в первую очередь.

будет ли безопасно просто удалить Workbook Диспетчер имен ограниченные записи и как они могли бы появиться без моего ведома с самого начала?

как определять и использовать в формулах

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

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

Что означает имя в Excel?

В повседневной жизни имена широко используются для обозначения людей, предметов и географических местоположений. Например, вместо того, чтобы сказать «город, расположенный на широте 40,7128 ° северной широты и 74,0059 ° западной долготы, вы просто скажете« Нью-Йорк ».

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

Например, чтобы найти общий объем продаж (B2: B10) для определенного товара (E1), вы можете использовать следующую формулу:

= СУММЕСЛИ (2 доллара США: 10 австралийских долларов, 1 доллар E $, 2 доллара США: 10 млрд долларов США)

Или вы можете дать значимые имена диапазонам и отдельным ячейкам и указать эти имена в формуле:

= СУММЕСЛИ (список_товаров, товар, продажи)

Глядя на приведенный ниже снимок экрана, какую из двух формул вам легче понять?

Типы имен Excel

В Microsoft Excel вы можете создавать и использовать два типа имен:

Определенное имя — имя, которое относится к отдельной ячейке, диапазону ячеек, постоянному значению или формуле. Например, когда вы определяете имя для диапазона ячеек, оно называется именованным диапазоном или определенным диапазоном . Эти имена являются предметом сегодняшнего урока.

Имя таблицы — имя таблицы Excel, которая создается автоматически, когда вы вставляете таблицу в рабочий лист (Ctrl + T). Для получения дополнительных сведений о таблицах Excel см. Раздел Как создать и использовать таблицу в Excel.

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

В целом, существует 3 способа определения имени в Excel: Поле имени , Кнопка «Определить имя » и Диспетчер имен Excel .

Введите имя в поле «Имя»

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

  1. Выберите ячейку или диапазон ячеек, которым вы хотите присвоить имя.
  2. Введите имя в поле Имя .
  3. Нажмите клавишу Enter.

Вуаля, новый именованный диапазон Excel создан!

Создайте имя с помощью параметра «Определить имя»

Другой способ создать именованный диапазон в Excel:

  1. Выберите ячейку (и).
  2. На вкладке Формулы в группе Определить имена нажмите кнопку Определить имя .
  3. В диалоговом окне Новое имя укажите три вещи:
    • В поле Имя введите имя диапазона.
    • В раскрывающемся списке Scope задайте область имени ( Workbook по умолчанию).
    • В поле относится к отметьте ссылку и при необходимости исправьте ее.
  4. Нажмите ОК , чтобы сохранить изменения и закрыть диалоговое окно.
Примечание. По умолчанию Excel создает имя с абсолютными ссылками . Если вы предпочитаете относительный именованный диапазон, удалите знак $ из ссылки (прежде чем вы это сделаете, убедитесь, что вы полностью понимаете, как относительные имена ведут себя на листах).

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

Создайте именованный диапазон с помощью диспетчера имен Excel

Обычно Диспетчер имен в Excel используется для работы с существующими именами. Однако это также может помочь вам создать новое имя. Вот как:

  1. Перейдите на вкладку Формулы > группу Определенные имена , щелкните Диспетчер имен . Или просто нажмите Ctrl + F3 (мой предпочтительный способ).
  2. В верхнем левом углу диалогового окна Name Manager нажмите кнопку New… :
  3. Откроется диалоговое окно Новое имя , в котором вы настраиваете имя, как показано в предыдущем разделе.

Наконечник. Чтобы быстро проверить вновь созданное имя, выберите его в раскрывающемся списке Name Box . Как только вы отпустите кнопку мыши, диапазон на листе будет выбран.

Как создать имя в Excel для константы

В дополнение к именованным диапазонам Microsoft Excel позволяет определять имя без ссылки на ячейку, которое будет работать как именованная константа . Чтобы создать такое имя, используйте функцию определения имени в Excel или Диспетчер имен, как описано выше.

Например, вы можете присвоить имя USD_EUR (курс конвертации USD — EUR) и присвоить ему фиксированное значение. Для этого введите значение, которому предшествует знак равенства (=), в поле Относится к полю , например = 0,93:

И теперь вы можете использовать это имя в любом месте своих формул для преобразования долларов США в евро:

Как только обменный курс изменится, вы обновите значение только в одном центральном месте, и все ваши формулы будут пересчитаны за один шаг!

Как определить имя для формулы

Аналогичным образом вы можете дать имя формуле Excel, например, той, которая возвращает количество непустых ячеек в столбце A, исключая строку заголовка (-1):

= COUNTA (Sheet5! $ A: $ A) -1

Примечание. Если ваша формула ссылается на какие-либо ячейки на текущем листе, вам не нужно включать имя листа в ссылки, Excel сделает это за вас автоматически. Если вы ссылаетесь на ячейку или диапазон на другом листе, добавьте имя листа с восклицательным знаком перед ссылкой на ячейку / диапазон (как в примере формулы выше).

Теперь, когда вы хотите узнать, сколько элементов находится в столбце A на Листе 5, не включая заголовок столбца, просто введите знак равенства, за которым следует имя вашей формулы в любой ячейке, например: = Items_count

Как назвать столбцы в Excel (имена из выбора)

Если ваши данные организованы в табличной форме, вы можете быстро создать имена для каждого столбца и / или строки на основе их меток:

  1. Выберите всю таблицу, включая заголовки столбцов и строк.
  2. Перейдите на вкладку Formulas > группу Define Names и нажмите кнопку Create from Selection . Или нажмите сочетание клавиш Ctrl + Shift + F3.
  3. В любом случае откроется диалоговое окно Create Names from Selection . Вы выбираете столбец или строку с заголовками, или и то, и другое, и нажимаете OK .

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

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

  • Яблоки , Бананы , Лимоны и Апельсины для рядков и
  • янв , фев и мар для столбцов.

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

Динамический именованный диапазон Excel

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

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

Подробное руководство по созданию динамического именованного диапазона в Excel можно найти здесь:

Правила именования Excel

При создании имени в Excel следует помнить несколько правил:

  • Имя Excel не должно превышать 255 символов.
  • Имена Excel не могут содержать пробелы и большинство знаков препинания.
  • Имя должно начинаться с буквы, подчеркивания (_) или обратной косой черты (\). Если имя начинается с чего-нибудь еще, Excel выдаст ошибку.
  • Имена Excel не чувствительны к регистру. Например, «Яблоки», «яблоки» и «ЯБЛОКИ» будут обрабатываться как одно и то же имя.
  • Вы не можете называть диапазоны, как ссылки на ячейки. То есть диапазону нельзя присвоить имя «A1» или «AA1».
  • Вы можете использовать одну букву для обозначения диапазона, например «a», «b», «D» и т. Д., За исключением букв «r», «R», «c» и «C» (эти символы используются как ярлыки для выбора строки или столбца для выбранной в данный момент ячейки при вводе их в поле Имя Поле ).

Область имен Excel

С точки зрения имен Excel, область действия — это местоположение или уровень, в котором имя распознается. Это может быть:

  • Конкретный рабочий лист — локальный уровень рабочего листа
  • Workbook — глобальная рабочая тетрадь уровня
Имена уровней рабочего листа

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

Чтобы иметь возможность использовать имя на уровне рабочего листа в другом рабочем листе , вы должны префикс имени рабочего листа, за которым следует восклицательный знак (!), Например:

Лист1! Items_list

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

[Sales. xlsx] Sheet1! Items_list

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

‘[Продажи 2017.xlsx] Sheet1 ‘! items_list

Имена уровней книги

Имя на уровне книги распознается во всей книге, и вы можете ссылаться на него просто по имени с любого листа в той же книге.

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

Book1.xlsx! Items_list

Приоритет области действия

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

Если существует несколько диапазонов с одинаковыми именами и разными областями действия, и вы хотите использовать имя уровня книги, добавьте к имени префикс имени книги, как если бы вы ссылались на имя в другой книге, например: Book1. xlsx! Data . Таким образом, конфликт имен можно переопределить для всех листов, кроме первого листа, который всегда использует имя уровня локального листа.

Excel Name Manager — быстрый способ редактирования, удаления и фильтрации имен

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

Есть два способа попасть в диспетчер имен в Excel:

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

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

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

Чтобы изменить существующее имя Excel, откройте Диспетчер имен , выберите имя и нажмите кнопку Изменить… . Откроется диалоговое окно Edit Name , в котором вы можете изменить имя и ссылку. Объем имени не может быть изменен.

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

Как фильтровать имена в Excel

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

  • Имена на листе или в книге
  • Имена с ошибками или без них
  • Определенные имена или имена таблиц

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

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

Чтобы удалить несколько имен , щелкните первое имя, затем нажмите клавишу Ctrl и удерживайте ее, щелкая другие имена, которые вы хотите удалить.Затем нажмите кнопку Удалить , и все выбранные имена будут удалены за один раз.

Чтобы удалить все определенные имена в книге, выберите первое имя в списке, нажмите и удерживайте клавишу Shift, а затем щелкните фамилию. Отпустите клавишу Shift и нажмите Удалить .

Как удалить определенные имена с ошибками

Если у вас есть несколько недопустимых имен с ошибками ссылок, нажмите кнопку Фильтр > Имена с ошибками , чтобы отфильтровать их:

После этого выберите все отфильтрованные имена, как описано выше (с помощью клавиши Shift), и нажмите кнопку Удалить .

Примечание. Если какое-либо из ваших имен Excel используется в формулах, обязательно обновите формулы перед удалением имен, иначе ваши формулы вернут #NAME? ошибки.

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

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

1. Названия Excel упрощают составление и чтение формул

Вам не нужно вводить сложные ссылки или перемещаться вперед и назад, выбирая диапазоны на листе. Просто начните вводить имя, которое вы хотите использовать в формуле, и Excel покажет вам список подходящих имен на выбор. Дважды щелкните желаемое имя, и Excel сразу же вставит его в формулу:

2. Имена Excel позволяют создавать расширяемые формулы

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

3. Имена Excel упрощают повторное использование формул

Имена

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

Наконечник. Чтобы форма Excel не создавала новые имена на лету, скопируйте формулу как текст в строке формул вместо копирования ячейки формулы.

4.Именованные диапазоны упрощают навигацию

Чтобы быстро перейти к определенному именованному диапазону, просто щелкните его имя в поле «Имя». Если именованный диапазон находится на другом листе, Excel автоматически переведет вас на этот лист.

Примечание. Динамические именованные диапазоны не отображаются в поле Имя в Excel. Чтобы увидеть динамические диапазоны , откройте диспетчер имен Excel (Ctrl + F3), в котором отображаются полные сведения обо всех именах в книге, включая их область действия и ссылки.

5. Именованные диапазоны позволяют создавать динамические раскрывающиеся списки

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

Именованный диапазон в Excel — советы и рекомендации

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

Как получить список всех имен в книге

Чтобы получить более понятный список всех имен в текущей книге, выполните следующие действия:

  1. Выберите самую верхнюю ячейку диапазона, в котором должны отображаться имена.
  2. Перейдите на вкладку Formulas > группу Define Names , щелкните Use in Formulas , а затем щелкните Paste Names… Или просто нажмите клавишу F3.
  3. В диалоговом окне Вставить имена щелкните Вставить список .

Это вставит все имена Excel вместе со ссылками на них в текущий рабочий лист, начиная с выбранной ячейки.

Абсолютные имена Excel и относительные имена Excel

По умолчанию имена Excel ведут себя как абсолютные ссылки — привязаны к определенным ячейкам. Однако можно создать именованный диапазон относительно позиции активной ячейки во время определения имени. Относительные имена ведут себя как относительные ссылки — меняются при перемещении или копировании формулы в другую ячейку.

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

  1. Выберите ячейку B1.
  2. Нажмите Ctrl + F3, чтобы открыть Диспетчер имен Excel, и щелкните Новый…
  3. В поле Имя введите желаемое имя, например item_left .
  4. В поле Ссылается на поле , введите = A1 .
  5. Щелкните ОК .

Теперь давайте посмотрим, что произойдет, если мы используем имя item_left в формуле, например:

= СУММЕСЛИ (список_элементов, левый_элемент, продажи)

Где items_list относится к $ A $ 2: $ A $ 10 и продаж относится к $ B $ 2: $ B $ 10 в таблице ниже.

Когда вы вводите формулу в ячейку E2, а затем копируете ее в столбец, она вычисляет общие продажи для каждого продукта индивидуально, потому что item_left — это относительное имя , и его ссылка настраивается в зависимости от относительного положения столбца и строка, в которую скопирована формула:

Как применять имена Excel к существующим формулам

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

  1. Выберите одну или несколько ячеек формулы, которые вы хотите обновить.
  2. Перейдите на вкладку Формулы > группу Определить имена и нажмите Определить имя > Применить имена…
  3. В диалоговом окне Apply Names щелкните по именам, которые вы хотите применить, а затем щелкните OK . Если Excel сможет сопоставить любое из существующих имен со ссылками, используемыми в ваших формулах, имена будут выбраны для вас автоматически:

Дополнительно доступны еще две опции (выбраны по умолчанию):

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

Сочетания клавиш в Excel

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

Ошибки имени Excel (# ССЫЛКА и # ИМЯ)

По умолчанию Microsoft Excel делает все возможное, чтобы ваши определенные имена были согласованными и действительными, автоматически настраивая ссылки на диапазоны при вставке или удалении ячеек в существующем именованном диапазоне.Например, если вы создали именованный диапазон для ячеек A1: A10, а затем вставили новую строку в любое место между строками 1 и 10, ссылка на диапазон изменится на A1: A11. Точно так же, если вы удалите любые ячейки между A1 и A10, ваш именованный диапазон будет соответственно сокращаться.

Однако, если вы удаляете все ячейки , которые составляют именованный диапазон Excel, имя становится недействительным и отображает #REF! ошибка в диспетчере имен . Та же ошибка будет отображаться в формуле со ссылкой на это имя:

Если формула относится к несуществующему имени (ошибочно набрана или удалена), #NAME? появится ошибка .В любом случае откройте диспетчер имен Excel и проверьте правильность заданных вами имен (самый быстрый способ — отфильтровать имена с ошибками).

Вот как вы создаете и используете имена в Excel. Благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

Вас также может заинтересовать

Как совместить имя и фамилию в Excel

Из этого туториала Вы узнаете несколько различных способов комбинирования имен в Excel: формулы, Flash Fill и инструмент «Объединить ячейки».

Таблицы

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

Формула Excel для объединения имени и фамилии

Когда вам нужно объединить имя и фамилию в одну ячейку, самый быстрый способ — объединить две ячейки с помощью оператора амперсанда (&) или функции СЦЕПИТЬ, как показано в примерах ниже.

Формула 1. Объедините имя и фамилию в Excel

Допустим, на вашем листе у вас есть один столбец для имени и другой столбец для фамилии, и теперь вы хотите объединить эти два столбца в один.

В общей форме вот формулы для объединения имени и фамилии в Excel:

= first_name_cell & «» & last_name_cell

CONCATENATE ( имя_первого_ячейки , «», последнее_ячейка )

В первой формуле конкатенация выполняется с помощью символа амперсанда (&). Вторая формула основана на соответствующей функции (слово «объединить» — это просто еще один способ сказать «объединить»). Обратите внимание, что в обоих случаях вы вставляете пробел («») между ними для разделения частей имени.

Если имя указано в ячейке A2, а фамилия — в поле B2, реальные формулы выглядят следующим образом:

= A2 & "" & B2

= СЦЕПИТЬ (A2; ""; B2)

Вставьте формулу в ячейку C2 или любой другой столбец в той же строке, нажмите Enter, затем перетащите маркер заполнения, чтобы скопировать формулу на столько ячеек, сколько вам нужно.В результате столбцы имени и фамилии будут объединены в столбец полного имени:

.

Формула 2. Объедините фамилию и имя через запятую

Если вы хотите объединить имена в формате Last Name, Fist Name , используйте одну из следующих формул для объединения имени и фамилии через запятую:

= last_name_cell & «,» & first_name_cell

CONCATENATE ( last_name_cell , «,», first_name_cell )

Формулы в основном те же, что и в предыдущем примере, но здесь мы объединяем имена в обратном порядке и разделяем их запятой и пробелом («,»).

На скриншоте ниже ячейка C2 содержит эту формулу:

= B2 & "," & A2

И ячейка D2 содержит это:

= СЦЕПИТЬ (B2, ",", A2)

В Excel 2016 и Excel 2019 вы также можете использовать функцию СЦЕПИТЬ для объединения имен:

= СЦЕПИТЬ (B2, ",", A2)

Какую бы формулу вы ни выбрали, результаты будут одинаковыми:

Формула 3. Объедините имя, отчество и фамилию в одну ячейку

С разными частями имени, перечисленными в 3 отдельных столбцах, вот как вы можете объединить их все в одну ячейку:

= first_name_cell & «» & middle_name_cell & «» & last_name_cell

CONCATENATE ( first_name_cell , «», middle_name_cell , «», last_name_cell )

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

Предполагая, что имя находится в A2, отчество в B2 и фамилия в C2, следующие формулы будут работать лучше:

= A2 & "" & B2 & "" & C2

= СЦЕПИТЬ (A2, "", B2, "", C2)

На снимке экрана ниже показана первая формула в действии:

В ситуации, когда столбец B может содержать или не содержать отчество, вы можете обработать каждый случай индивидуально, а затем объединить две формулы в одну с помощью оператора IF:

= ЕСЛИ (B2 = "", A2 & "" & C2, A2 & "" & B2 & "" & C2)

Это предотвратит появление лишних пробелов между словами в строках, где отсутствует отчество:

Формула 4. Объединить первые инициалы и фамилию

В этом примере показано, как объединить два имени в одно в Excel и преобразовать полное имя в краткое.

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

Если имя указано в ячейке A2, а имя — в поле B2, формула принимает следующий вид:

= ЛЕВЫЙ (A2,1) & "" & B2

или

= СЦЕПИТЬ (СЛЕВА (A2; 1); ""; B2)

В зависимости от желаемого результата может пригодиться один из следующих вариантов приведенной выше формулы.

Добавьте точку после начальной:

= ЛЕВЫЙ (A2,1) & "." & B2

Объединить инициалы с фамилией без пробела:

= ЛЕВЫЙ (A2,1) и B2

Объедините имя и фамилию и преобразуйте объединенное имя в нижний регистр:

= НИЖНИЙ (ЛЕВЫЙ (A2,1)) И НИЖНИЙ (B2)

Для вашего удобства в следующей таблице показаны все формулы вместе с их результатами:

А В С D E
1 Имя Фамилия Комбинированное наименование Формула Описание
2 Джейн Самка Дж. Доу = ЛЕВЫЙ (A2,1) & «» & B2 Инициал + Фамилия через пробел
3 Дж.Доу = ЛЕВЫЙ (A2,1) & «.» & B2 Инициал + Фамилия, разделенные точкой и пробелом
4 JDoe = ЛЕВЫЙ (A2,1) и B2 Инициал + Фамилия без пробела
5 jdoe = НИЖНИЙ (ЛЕВЫЙ (A2,1)) И НИЖНИЙ (B2) Начальная буква + Фамилия строчными буквами без пробела

Советы и примечания по объединению имен в Excel

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

Обрезать лишнее

Если ваша информация поступает из внешней базы данных, велика вероятность, что в исходных столбцах есть конечные пробелы, невидимые человеческому глазу, но отлично читаемые Excel. В результате между объединенными именами могут появиться лишние пробелы, как в левой таблице ниже. Чтобы исключить лишние пробелы между словами до одного символа пробела, заключите каждую ссылку на ячейку в функцию TRIM, а затем объедините.Например:

= ОБРЕЗАТЬ (A2) & "" & ОБРЕЗАТЬ (B2)

Начинайте каждое имя с заглавной буквы

Если вы работаете со списком персонала, созданным кем-то другим, и этот кто-то не очень точный человек, некоторые имена могут быть написаны строчными буквами, а другие — прописными. Простое решение — использование функции ПРОПИСН, которая переводит первый символ в каждом слове в верхний регистр, а остальные в нижний регистр:

= НАДЛЕЖАЩИЙ (A2) & "" & ПРАВИЛЬНЫЙ (B2)

Заменить формулы значениями и удалить исходные столбцы

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

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

Как объединить имя и фамилию в Excel автоматически

При использовании формул результат и исходные данные тесно связаны — любые изменения, внесенные в исходные значения, немедленно отражаются в выходных данных формулы.Но если вы не ожидаете каких-либо обновлений для комбинированных имен, воспользуйтесь возможностью Excel Flash Fill для автоматического заполнения данных на основе шаблона.

Вот как вы можете объединить имена за секунду с помощью Flash Fill:

  1. Для первой записи введите вручную имя и фамилию в соседнем столбце.
  2. Начните вводить имя в следующей строке, и Excel немедленно предложит полные имена для всего столбца.
  3. Нажмите Enter, чтобы принять предложения.Готово!

Прелесть этого метода в том, что Excel идеально «имитирует» ваш шаблон, регистр букв и пунктуацию, поэтому вы можете соединять имена именно так, как вы хотите. Порядок частей имени в исходных столбцах не имеет значения! Только не забудьте ввести имя в первой ячейке точно так, как вы хотите, чтобы все имена отображались.

Например, посмотрите, как легко вы можете комбинировать имена с запятой:

Как объединить имя и фамилию путем объединения ячеек

Еще один быстрый способ объединения имен в Excel — объединение ячеек, содержащих части имени.Нет, я не говорю о встроенной функции слияния, потому что она сохраняет только значение верхней левой ячейки. Пожалуйста, познакомьтесь с инструментом Ablebits Merge Cells, который сохраняет все ваши значения при объединении ячеек 🙂

Чтобы объединить имя и фамилию путем объединения ячеек, выполните следующие действия:

  1. Выберите два столбца с именами, которые вы хотите объединить.
  2. На вкладке Ablebits в группе Объединить щелкните стрелку раскрывающегося списка Объединить ячейки и выберите Объединить столбцы в один :
  3. Откроется диалоговое окно «Объединить ячейки».Вы вводите пробел в поле «. Разделяйте значения с помощью » и оставляете все остальные варианты, предлагаемые по умолчанию:

    Совет. Если вы хотите сохранить исходные столбцы имени и фамилии, убедитесь, что установлен флажок Резервное копирование этого рабочего листа .

  4. Нажмите кнопку Объединить .

В результате имя и фамилия объединяются в одно и помещаются в левый столбец:

Если вам интересно попробовать это и еще более 60 инструментов для экономии времени, включенных в наш Ultimate Suite, вы можете загрузить пробную версию для Excel 2019-2010.

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

Получите промокод для Ultimate Suite — эксклюзивное предложение для читателей нашего блога!

Вас также может заинтересовать

разделяют имя и фамилию на разные столбцы

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

Это очень распространенная ситуация в Excel, когда ваш рабочий лист содержит столбец с полными именами, и вы хотите разделить имя и фамилию на отдельные столбцы. Эту задачу можно выполнить несколькими способами — с помощью функции «Текст в столбцы», формул и инструмента «Разделить имена». Ниже вы найдете полную информацию о каждой технике.

Как разделить имена в Excel с текстом на столбцы

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

  1. Выберите столбец с полными именами, которые вы хотите разделить.
  2. Перейдите на вкладку Data > группу Data Tools и щелкните Text to Columns .
  3. На первом шаге мастера Convert Text to Columns Wizard выберите опцию Delimited и нажмите Next .
  4. На следующем шаге выберите один или несколько разделителей и нажмите Далее .

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

    Наконечник. Если вы имеете дело с именами, разделенными запятой и пробелом , например Anderson, Ronnie , то установите флажки Comma и Space под Delimiters и установите Обрабатывать последовательные разделители как один флажок ( обычно выбирается по умолчанию).

  5. На последнем шаге вы выбираете формат данных и назначение и нажимаете Готово .

    Стандартный формат General в большинстве случаев работает хорошо. В качестве Destination укажите самую верхнюю ячейку в столбце, в котором вы хотите вывести результаты (помните, что при этом будут перезаписаны все существующие данные, поэтому обязательно выберите пустой столбец).

Готово! Имя, отчество и фамилия разделены на отдельные столбцы:

Разделите имя и фамилию в Excel формулами

Как вы только что видели, функция Text to Columns работает быстро и легко.Однако, если вы планируете вносить какие-либо изменения в исходные имена и ищете динамическое решение, которое будет обновляться автоматически, вам лучше разделить имена с помощью формул.

Как отделить имя и фамилию от полного имени с пробелом

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

Формула для получения имени

Имя можно легко извлечь с помощью этой общей формулы:

ЛЕВЫЙ ( ячейка , ПОИСК («», ячейка ) — 1)

Вы используете функцию ПОИСК или НАЙТИ, чтобы получить позицию символа пробела («») в ячейке, из которой вы вычитаете 1, чтобы исключить сам пробел.Это число передается в функцию LEFT как количество извлекаемых символов, начиная с левой стороны строки.

Формула для получения фамилии

Общая формула для извлечения фамилии следующая:

ВПРАВО ( ячейка , LEN ( ячейка ) — ПОИСК («», ячейка ))

В этой формуле вы также используете функцию ПОИСК, чтобы найти позицию символа пробела, вычесть это число из общей длины строки (возвращаемой LEN) и получить функцию ВПРАВО для извлечения такого количества символов с правой стороны. строки.

С полным именем в ячейке A2 формулы выглядят следующим образом:

Получить имя :

= ЛЕВЫЙ (A2; ПОИСК (""; A2) -1)

Получить фамилию :

= ВПРАВО (A2; LEN (A2) -ПОИСК ("", A2,1))

Вы вводите формулы в ячейки B2 и C2 соответственно и перетаскиваете маркер заполнения, чтобы скопировать формулы вниз по столбцам. Результат будет выглядеть примерно так:

Если некоторые из исходных имен содержат отчество или отчество , вам понадобится немного более сложная формула для извлечения фамилии:

= ВПРАВО (A2, LEN (A2) - ПОИСК ("#", ПОДСТАВИТЬ (A2, "", "#", LEN (A2) - LEN (ПОДСТАВИТЬ (A2, "", "")))))

Вот высокоуровневое объяснение логики формулы: вы заменяете последний пробел в имени знаком решетки (#) или любым другим символом, который не встречается ни в одном имени, и определяете положение этого символа. После этого вы вычитаете указанное выше число из общей длины строки, чтобы получить длину фамилии, а функция RIGHT извлекает это количество символов.

Итак, вот как можно разделить имя и фамилию в Excel, когда некоторые из исходных имен включают отчество:

Как отделить имя и фамилию от имени запятой

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

Формула для извлечения имени

ВПРАВО ( ячейка , LEN ( ячейка ) — ПОИСК («», ячейка ))

Как и в приведенном выше примере, вы используете функцию ПОИСК, чтобы определить позицию символа пробела, а затем вычитаете ее из общей длины строки, чтобы получить длину имени. Это число идет непосредственно в аргумент num_chars функции RIGHT, указывающий, сколько символов нужно извлечь из конца строки.

Формула для извлечения фамилии

ЛЕВЫЙ ( ячейка , ПОИСК («», ячейка ) — 2)

Чтобы получить фамилию, вы используете комбинацию ЛЕВОГО ПОИСКА, описанную в предыдущем примере, с той разницей, что вы вычитаете 2 вместо 1, чтобы учесть два дополнительных символа, запятую и пробел.

С полным именем в ячейке A2 формулы принимают следующую форму:

Получить имя :

= ВПРАВО (A2; LEN (A2) - ПОИСК ("", A2))

Получить фамилию :

= ЛЕВЫЙ (A2; ПОИСК (""; A2) - 2)

На скриншоте ниже показаны результаты:

Как разделить полное имя на имя, фамилию и отчество

Разделение имен, включающих отчество или инициалы отчества, требует немного разных подходов в зависимости от формата имени.

Если ваши имена указаны в формате Имя Отчество Фамилия , приведенные ниже формулы подойдут для вас:

А В С D
1 ФИО Имя Отчество Фамилия
2 Имя Отчество Фамилия = ЛЕВЫЙ (A2; ПОИСК (""; A2) -1) = MID (A2, ПОИСК ("", A2) + 1, ПОИСК ("", A2, ПОИСК ("", A2) +1) - ПОИСК ("", A2) -1) = ВПРАВО (A2, LEN (A2) - ПОИСК ("", A2, ПОИСК ("", A2,1) +1))
Результат: Дэвид Марк Уайт Дэвид Марка Белый

Чтобы получить имя , вы используете уже знакомую формулу ЛЕВОГО ПОИСКА.

Чтобы получить фамилию , определите позицию 2 и пробелов с помощью вложенных функций ПОИСК, вычтите позицию из общей длины строки и получите в результате длину фамилии. Затем вы передаете указанное выше число функции RIGHT, давая ей указание вытащить это количество символов из конца строки.

Чтобы извлечь отчество , вам необходимо знать положение обоих пробелов в имени. Чтобы определить позицию первого пробела, используйте простую функцию SEARCH («», A2), к которой вы добавляете 1, чтобы начать извлечение со следующего символа.Этот номер идет в аргумент start_num функции MID. Чтобы определить длину отчества, вы вычитаете позицию 1-го пробела из положения 2-го пробела, вычитаете 1 из результата, чтобы избавиться от конечного пробела, и помещаете это число в аргумент num_chars MID, сообщая ему, сколько символов нужно извлечь.

А вот формулы для разделения имен Фамилия, Имя Отчество тип:

А В С D
1 ФИО Имя Отчество Фамилия
2 Фамилия, Имя Отчество = MID (A2, ПОИСК ("", A2) + 1, ПОИСК ("", A2, ПОИСК ("", A2) + 1) - ПОИСК ("", A2) -1) = ВПРАВО (A2, LEN (A2) - ПОИСК ("", A2, ПОИСК ("", A2, 1) +1)) = ЛЕВЫЙ (A2; ПОИСК (""; A2,1) -2)
Результат: Белый, Дэвид Марк Дэвид Марка Белый

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

А В С D
1 ФИО Имя Фамилия Суффикс
2 Имя Фамилия, суффикс = ЛЕВЫЙ (A2; ПОИСК (""; A2) -1) = MID (A2, ПОИСК ("", A2) + 1, ПОИСК (",", A2) - ПОИСК ("", A2) -1) = ВПРАВО (A2; LEN (A2) - ПОИСК ("", A2, ПОИСК ("", A2) +1))
Результат: Роберт Фурлан-младший. Роберт Фурлан мл.

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

Отдельное имя в Excel 2013, 2016 и 2019 с Flash Fill

Всем известно, что программа Excel Flash Fill может быстро заполнить данные определенного шаблона. Но знаете ли вы, что он также может разделять данные? Вот как:

  1. Добавьте новый столбец рядом с столбцом с исходными именами и введите часть имени, которую вы хотите извлечь, в первую ячейку (первое имя в этом примере).
  2. Начните вводить имя во второй ячейке. Если Excel обнаруживает шаблон (в большинстве случаев это так), он автоматически заполняет имена во всех остальных ячейках.
  3. Все, что вам теперь нужно сделать, это нажать клавишу Enter 🙂

Наконечник. Обычно функция Flash Fill включена по умолчанию. Если это не работает в вашем Excel, нажмите кнопку Flash Fill на вкладке Data > Data tools group. Если это по-прежнему не работает, перейдите в Файл > Параметры , щелкните Расширенный и убедитесь, что в разделе Параметры редактирования установлен флажок Автоматически мигать заливкой .

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

В таких ситуациях вы можете передать работу нашему инструменту разделения имен, который отлично распознает составные имена, более 80 приветствий и около 30 различных суффиксов и без проблем работает со всеми версиями от Excel 2016 до Excel 2007.

Если в Excel установлен наш Ultimate Suite, столбец имен в различных форматах можно разделить за 2 простых шага:

  1. Выберите любую ячейку, содержащую имя, которое вы хотите разделить, и щелкните значок Разделить имена на вкладке Ablebits Data > группа Text .
  2. Выберите нужные части имен (в нашем случае все), щелкнув Split .

Готово! Различные части имен распределены по нескольким столбцам точно так, как должны, а заголовки столбцов добавляются автоматически для вашего удобства.Никаких формул, никакой возни с запятыми и пробелами, никакой боли.

Если вам интересно попробовать инструмент «Разделить имена» на своих рабочих листах, не стесняйтесь загрузить ознакомительную версию Ultimate Suite for Excel. Если инструмент соответствует вашим ожиданиям и вы решили получить лицензию, у нас есть для вас специальное предложение:

Получите промокод для Ultimate Suite — эксклюзивное предложение для читателей нашего блога!

Доступные загрузки

Формулы для разделения имен в Excel (файл .xlsx)
Ultimate Suite 14-дневная полнофункциональная версия (.zip-файл)

Вас также может заинтересовать

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

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

Имена Excel — Введение

В Excel вы можете создавать имена, относящиеся к:

  • Ячейки на листе
  • Удельное значение
  • Формула

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

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

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

ПРИМЕЧАНИЕ : Чтобы создать быстрый список всех имен в книге, см. «Краткий список имен — без макросов».

Как назвать ячейки

Посмотрите это короткое видео, чтобы узнать, как назвать группу ячеек.Затем перейдите к указанной группе ячеек или используйте это имя в формуле. Письменные инструкции находятся под видео. ТОП

Ячейки имени — поле имени

Вы можете быстро присвоить имя выбранным ячейкам, введя имя в поле «Имя». ПРИМЕЧАНИЕ : Существует несколько правил для имен Excel

.
  1. Выберите ячейку (я), которую нужно назвать
  2. Щелкните поле Имя слева от строки формул
  3. Введите допустимое имя из одного слова для списка, например.г. FruitList.
  4. Нажмите клавишу Enter.

Правила создания имен

На сайте Microsoft есть правила для имен Excel, которые кратко изложены ниже.

Подробно о том, какие символы можно использовать, см. В проекте Мартина Траммера на GitHub excel-names — в нем есть примеры и файл Excel для загрузки.

Следуйте этим правилам Microsoft при создании имени в Excel.

  • Первый символ имени должен быть одним из следующих символов:
    • письмо
    • подчеркивание (_)
    • обратная косая черта (\).
  • Остальные символы в имени могут быть
    • буквы
    • номера
    • периодов
    • символа подчеркивания
  • Следующие запрещены :
    • Пробелы не могут быть частью имени.
    • Имена не могут выглядеть как адреса ячеек , например 35 австралийских долларов или R2D2
    • C, c, R, r — нельзя использовать в качестве имен — Excel использует их как ярлыки выбора
  • Имена без учета регистра . Например, Север и СЕВЕР обрабатываются как одноименное.

См. Названия

ПРИМЕЧАНИЕ : Чтобы создать быстрый список всех имен в книге, см. «Краткий список имен — без макросов».

Чтобы увидеть некоторые из названных диапазонов на листе, используйте этот трюк:

  • В правом нижнем углу окна Excel щелкните настройку уровня масштабирования
  • В диалоговом окне Zoom выберите Custom
  • Введите 39 в поле процентов и нажмите ОК

Имена некоторых диапазонов появятся на листе синим текстом, как список месяцев на этом снимке экрана.

  • Имена, созданные с помощью формулы, например YearList, не отображаются.
  • Некоторые диапазоны могут быть слишком маленькими, чтобы отображать их имена

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

После создания именованного диапазона вам может потребоваться изменить ячейки что это относится. В этом коротком видеоролике показаны шаги, а под ним — записанные шаги.

Чтобы изменить эталонный диапазон, выполните следующие действия:

  1. На ленте щелкните вкладку Формулы
  2. Нажмите Диспетчер имен
  3. В списке нажмите на имя, которое вы хотите изменить
  4. В поле Refers To измените ссылку на диапазон или перетащите лист, чтобы выбрать новый диапазон.
  5. Щелкните галочку, чтобы сохранить изменение
  6. Нажмите «Закрыть», чтобы закрыть диспетчер имен TOP

Создание имен из текста ячейки

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

Создание имен из текста ячейки

Быстрый способ создания имен — основывать их на тексте ячейки заголовка (рабочий лист этикетки).В примере, показанном ниже, ячейки в столбце E будут назван на основе меток в столбце D.

ПРИМЕЧАНИЕ : Если метки содержат пробелы, они заменяются на подчеркивание. Другие недопустимые символы, такие как & и #, будут удалить или заменить символом подчеркивания.

Для именования ячеек или диапазонов на основе меток рабочего листа:

  1. Выберите метки и ячейки, которым необходимо присвоить имя.Этикетки может быть выше, ниже, слева или справа от именованных ячеек. В в этом примере метки находятся в столбце B слева от ячеек. который будет назван.

  2. На ленте щелкните вкладку Формулы, затем щелкните Создать из Выбор.

  3. В окне «Создать имена из выделенного» установите флажок для расположение меток, затем нажмите ОК.В этом примере метки находятся в левом столбце выбранных ячеек.

  4. Щелкните ячейку, чтобы увидеть ее имя. На снимке экрана ниже ячейка C4 выбран, и вы можете увидеть его имя в поле имени — полное_имя.
    ПРИМЕЧАНИЕ : Пробел заменен на подчеркивание.

Создать имя для значения

Большинство имен Excel относятся к диапазонам на листе, но имена также могут использоваться для хранения значения.

Часто используемые значения

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

  • Имя: TaxRate
  • относится к: = 0,5

Затем используйте это имя в формулах вместо ввода значения

.

Специальные значения

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

Вместо того, чтобы вводить это число в формулы, вы можете определить имя, используя это значение (скопируйте число с этой страницы, прежде чем создавать имя):

  • Имя: XL_Max
  • Обращается к: 9.99999999999999E + 307

Затем используйте имя XL_Max в формулах, как в этой формуле ПРОСМОТР, которая находит последнее число в столбце.

= ПРОСМОТР (9.99999999999999E + 307, данные о весе [вес])

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

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

Использовать имена для быстрой навигации

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

ПРИМЕЧАНИЕ : Если имя не отображается в раскрывающемся списке, вы можете ввести имя вместо

Использование имен в формулах

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

= СУММ (количество)

Уловки с именами

В дополнение к использованию поля имени для создания именованного диапазона или для выбора именованного диапазона есть еще несколько уловок с полем имени.

Изменение размера поля имени

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

  • Наведите указатель мыши на кнопку с тремя точками справа от поля имени
  • Когда указатель изменится на двунаправленную стрелку, перетащите влево или вправо, чтобы изменить ширину

Выбрать ячейки

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

Показать столбцы

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

  • Скрыть столбцы от A до J
  • Щелкните в поле имени
  • Введите a1, e1, h2 в поле имени и нажмите Enter.
  • Затем используйте команду Показать, чтобы показать выбранные столбцы
    • Вкладка «Главная»> Формат> Скрыть и показать> Показать столбцы

Заполнить ячейки

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

Иногда, однако, в соседнем столбце нет данных, поэтому автозаполнение не работает при двойном щелчке. Вы можете перетащить Fill Handle вниз, но это не очень эффективно, если вам нужно создать длинную серию.

Вот как создать список из 1000 номеров в столбце A:

  • Щелкните в поле имени
  • Введите a1: a1000 в поле имени и нажмите Enter
    • .
  • Выделив ячейки, введите число 1 и нажмите Ctrl + Enter.
  • Затем выберите ячейку A1 и введите 1-е число в своей серии, e.г. 5
  • Выберите ячейку A2 и введите 2-е число в своей серии, например 10
  • Выберите ячейки A1 и A2 и дважды щелкните маркер заполнения, чтобы создать серию из 1000 чисел

Создать динамический именованный диапазон

Если список, который вы хотите назвать, будет часто меняться, добавляемые и удаляемые элементы, следует создать динамический именованный диапазон. Динамический именованный диапазон автоматически изменять размер при изменении списка.Вот два способа создать динамический именованный диапазон:

Используйте именованную таблицу Excel

Используйте формулу

Использование именованной таблицы Excel

Самый простой способ создать динамический именованный диапазон — начать с создания именованного Таблица Excel. Затем определите диапазон на основе одного или нескольких столбцов в этой таблице.

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

Сначала создайте таблицу:
  1. Выберите ячейку в списке деталей
  2. На вкладке Вставка ленты щелкните Таблица
  3. Убедитесь, что выбран правильный диапазон, и добавьте проверку отметка для моей таблицы имеет заголовки
  4. Нажмите ОК, чтобы создать таблицу.

  5. (необязательно) Измените имя таблицы по умолчанию (например,г. Table1) на значащее имя, например tblParts

Затем создайте динамический список идентификаторов деталей:
  1. Выберите ячейки A2: A9, которые содержат идентификаторы деталей (не заголовок).
  2. Щелкните на панели формул и введите имя диапазона из одного слова: PartIDList
  3. Нажмите клавишу Enter, чтобы ввести имя.

Чтобы увидеть определение имени, выполните следующие действия:

  • Щелкните вкладку Формулы ленты и щелкните Диспетчер имен.
  • В списке два названных элемента:
    1. Детали table с именем по умолчанию, Table1 (или именем, которое вы дали таблице)
    2. PartIDList, который на основе поля PartID в Table1.

Текст для динамического диапазона

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

  • Добавить новый элемент в список идентификаторов деталей
  • В поле Имя выберите имя PartIDList
  • Выбран именованный диапазон, который включает новый идентификатор детали. ТОП

Динамический именованный диапазон — формула

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

Письменные инструкции под видео.

Динамический именованный диапазон на основе формулы

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

Примечание. Динамические именованные диапазоны не отображаются в раскрывающемся списке «Поле имени». список.Однако вы можете ввести имена в поле имени, чтобы выбрать диапазон на листе.

  1. На ленте щелкните вкладку Формулы
  2. Нажмите «Определить имя»
  3. Введите имя диапазона, например Список имен
  4. Оставьте Scope равным Workbook.

  5. В поле «Относится к» введите формулу смещения, которая определяет размер диапазона, основанный на количестве элементов в столбце, e.g .:
    = OFFSET (Sheet1! $ A $ 1,0,0, COUNTA (Sheet1! $ A: $ A), 1)
    В этом примере список находится на Sheet1, начиная с ячейки A1
    Аргументы в этой функции смещения используются:
  • Ссылочная ячейка: Sheet1! $ A $ 1
  • Строки для смещения: 0
  • Столбцы для смещения: 0
  • Количество строк: COUNTA (Sheet1! $ A: $ A)
  • Количество столбцов: 1
  • Примечание : для динамического количества столбцов замените 1 с:
    COUNTA (Sheet1! $ 1: $ 1)
  • Нажмите ОК TOP

    • Загрузить образец файла

    Чтобы следовать инструкциям на этой странице, загрузите образец файла имен Excel.Заархивированный файл имеет формат xlsx и не содержит макросов. ТОП

    Ссылки по теме

    Использование имен в формулах

    Именованные таблицы Excel

    Excel имена макросов

    Создание динамических диапазонов с помощью макроса

    Ошибка Excel #NAME — как найти и исправить ошибки #NAME в Excel

    Функции Excel, такие как СРЕДНЕЕ, СЧЁТЕСЛИ и СУММ, являются полезным способом сокращения вычислений, но это не означает, что вы не столкнетесь с неприятности.На самом деле существует множество типов ошибок, которые могут появляться в Excel, и наиболее распространенной из них является #NAME? ошибка. Вот почему может возникнуть эта ошибка и как ее исправить.

    Узнайте, как распознать и разрешить #NAME? ошибки в Excel.

    Об ошибке #NAME в Excel

    Ошибка #NAME возникает в Excel, когда программа не распознает что-то в вашей формуле. Наиболее частая причина — это простая ошибка в написании используемой функции. Например, на изображении ниже формула VLOOKUP написана неправильно в первом экземпляре (F5), поэтому она дает #NAME? ошибка.

    Когда это исправляется во втором случае (F6), он дает правильный результат. Еще лучше, ошибки больше нет.

    Хотя это простейшая и наиболее частая причина ошибки #NAME, существует несколько других.

    Неправильно введенный диапазон, вызывающий ошибку #NAME

    Если у вас есть формула в Excel с диапазоном, и он указан неправильно, программа вернет ошибку #NAME. Рассмотрим приведенный ниже пример с использованием функций MIN и MAX.Функция MIN введена правильно, а формула MAX — нет (F6).

    Когда формула в F6 фиксируется от MAX (CC: C9) до MAX (C5: C9), она работает правильно, и ошибка исчезла.

    Отсутствие двоеточия в диапазоне также может вызвать ошибку #NAME.

    Ошибочное написание именованного диапазона вызывает ошибку #NAME

    Если у вас есть именованный диапазон ячеек, это может ускорить создание формул в Excel. Однако вы получите сообщение об ошибке # ИМЯ, если ошибетесь в названии диапазона в формуле.Используя тот же пример MIN MAX из предыдущего, диапазон был назван «data», но в формуле был написан с ошибкой.

    После правильного написания данных в формуле ошибка #NAME исчезает и дается правильный ответ.

    Значения в формуле без кавычек, вызывающие ошибку #NAME

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

    В следующем примере функция LEN используется для получения длины слова «Лабрадор». В B5 формула предоставляется без включения слова в двойные кавычки («»). Поскольку лабрадор не является именованным диапазоном или функцией, Excel возвращает ошибку # NAME.

    Когда это исправлено путем добавления двойных кавычек вокруг слова «Лабрадор» в B5, дается длина слова, и ошибка #NAME исчезает.

    Предотвращение ошибок #NAME

    Лучший способ предотвратить ошибку #NAME в Excel — использовать Мастер формул.Когда вы начнете вводить имя функции в строке формул, в раскрывающемся списке появится ряд вариантов. Вместо того чтобы продолжать вводить текст, вы можете избежать ошибок при написании, выбрав имя функции из списка.

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

    Используйте мастер формул Excel, чтобы избежать распространенных причин ошибки #NAME.

    Как найти ошибки #NAME

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

    Первый способ — нажать Control-G (Перейти) или F5 и выбрать Special . Появится окно Перейти к специальному . Затем выберите Формулы и установите флажок с надписью Ошибки . Нажмите ОК . После этого вы увидите все ошибки формул в Excel.

    Другой способ найти ошибки #NAME — использовать функцию « Найти ». В разделе Редактирование выберите « Найти » или найдите его, нажав Ctrl-F . В поле Найти введите # ИМЯ, а затем выберите « Найти следующий » или « Найти все ». Затем вы можете работать с ошибками.

    Исправление # ИМЯ? Ошибки в Excel

    Исправить ошибку #NAME не так просто, как просто удалить ее и двигаться дальше.Каждый случай этой ошибки требует тщательной проверки, поскольку ее могут вызвать несколько причин. Инструмент «Найти», описанный выше, — отличный способ найти ошибки, но тогда вам нужно будет исправить каждую из них вручную, чтобы формулы работали так, как предполагалось.

    Другие проблемы с ошибками Excel #NAME

    Могут быть некоторые сложные проблемы, которые вызывают появление ошибки #NAME в Excel. Например, если вы используете функцию, которая была представлена ​​в более поздней версии Excel (например, IFNA), а затем пытаетесь открыть эту электронную таблицу в более ранней версии Excel, вы, вероятно, получите ошибку #NAME, потому что программа не t распознать функцию.

    Другая проблема может возникнуть при сохранении листа Excel с другим именем (Сохранить как). Если у вас есть макрос с формулами в первом файле, но в новом файле макросы не включены, вы, вероятно, получите ошибку #NAME.

    По-прежнему нужна помощь с Excel?

    Удалось ли вам найти и устранить ошибку Excel #NAME? Вне зависимости от того, помогло ли это руководство ответить на ваши вопросы или вам все еще нужна помощь по конкретным проблемам, Excelchat может помочь.

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

    именованных диапазонов в Excel | Exceljet

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

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

    Фактически, я постоянно использую именованные диапазоны при тестировании и создании прототипов формул. Они помогают мне быстрее работать с формулами. Я также использую именованные диапазоны, потому что я ленив и не люблю вводить сложные ссылки 🙂

    Основы именованных диапазонов в Excel

    Что такое именованный диапазон?

    Именованный диапазон — это просто удобочитаемое имя диапазона ячеек в Excel. Например, если я назову диапазон A1: A100 «данные», я могу использовать MAX, чтобы получить максимальное значение с помощью простой формулы:

    Красота именованных диапазонов заключается в том, что вы можете использовать значимые имена в своих формулах, не думая о ссылках на ячейки.Если у вас есть именованный диапазон, просто используйте его как ссылку на ячейку. Все эти формулы действительны для именованного диапазона «data»:

     
     = MAX (данные) // максимальное значение
= MIN (данные) // минимальное значение
= COUNT (данные) // итоговые значения
= СРЕДНЕЕ (данные) // минимальное значение

    Видео: как создать именованный диапазон

    Создать именованный диапазон просто

    Создать именованный диапазон быстро и легко. Просто выберите диапазон ячеек и введите имя в поле имени. При нажатии возврата создается имя:

    Чтобы быстро протестировать новый диапазон, выберите новое имя в раскрывающемся списке рядом с полем имени.Excel выберет диапазон на листе.

    Excel может создавать имена автоматически (ctrl + shift + F3)

    Если у вас есть хорошо структурированные данные с метками, вы можете настроить Excel для создания именованных диапазонов. Просто выберите данные вместе с метками и используйте команду «Создать из выделенного» на вкладке «Формулы» на ленте:

    Также можно использовать сочетание клавиш control + shift + F3.

    Используя эту функцию, мы можем создать именованные диапазоны для населения 12 штатов за один шаг:

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

    Созданные имена можно использовать в таких формулах.

    Обновление именованных диапазонов в диспетчере имен (Control + F3)

    После создания именованного диапазона используйте диспетчер имен (Control + F3) для обновления по мере необходимости. Выберите имя, с которым хотите работать, затем измените ссылку напрямую (т. Е. Редактировать «относится к») или нажмите кнопку справа и выберите новый диапазон.

    Нет необходимости нажимать кнопку «Изменить», чтобы обновить ссылку. Когда вы нажмете «Закрыть», название диапазона будет обновлено.

    Примечание: если вы выберете весь именованный диапазон на листе, вы можете перетащить его в новое место, и ссылка будет обновлена ​​автоматически. Однако я не знаю, как настроить ссылки на диапазоны, щелкнув и перетащив прямо на лист. Если вы знаете, как это сделать, сообщите об этом ниже!

    Просмотреть все названные диапазоны (Ctrl + F3)

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

    Если вы хотите увидеть более подробную информацию, откройте Диспетчер имен (Control + F3), в котором перечислены все имена со ссылками, а также есть фильтр:

    Примечание: на Mac нет диспетчера имен, поэтому вместо него вы увидите диалоговое окно «Определить имя».

    Скопируйте и вставьте все именованные диапазоны (F3)

    Если вам нужна более постоянная запись именованных диапазонов в книге, вы можете вставить полный список имен в любое место. Перейдите в «Формулы»> «Использовать в формуле» (или воспользуйтесь сочетанием клавиш F3), затем выберите «Вставить имена»> «Вставить список:

    ».

    Когда вы нажмете кнопку «Вставить список», вы увидите имена и ссылки, вставленные в рабочий лист:

    Смотрите имена прямо на листе

    Если вы установите уровень масштабирования менее 40%, Excel отобразит имена диапазонов непосредственно на листе:

    Спасибо за этот совет, Фелипе!

    Имена имеют правила

    При создании именованных диапазонов соблюдайте следующие правила:

    1. Имена должны начинаться с буквы, символа подчеркивания (_) или обратной косой черты (\).
    2. Имена не могут содержать пробелы и большинство знаков препинания.
    3. Имена не могут конфликтовать со ссылками на ячейки — вы не можете назвать диапазон «A1» или «Z100».
    4. Отдельные буквы подходят для имен («a», «b», «c» и т. Д.), Но буквы «r» и «c» зарезервированы.
    5. Имена не чувствительны к регистру — «home», «HOME» и «HoMe» одинаковы для Excel.

    Именованные диапазоны в формулах

    Именованные диапазоны легко использовать в формулах

    Например, допустим, вы назвали ячейку в своей книге «обновленной». Идея в том, что вы можете поместить текущую дату в ячейку (Ctrl +;) и указать дату в другом месте книги.

    Формула в B8 выглядит так:

     
     = "Обновлено:" & ТЕКСТ (обновлено, "ддд, мммм д, гггг")

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

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

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

    Именованные диапазоны могут работать как константы

    Поскольку именованные диапазоны создаются централизованно, их можно использовать как константы без ссылки на ячейку. Например, вы можете создать такие названия, как «MPG» (миль на галлон) и «CPG» (стоимость за галлон), и назначить фиксированные значения:

    Затем вы можете использовать эти имена где угодно в формулах и обновлять их значения в одном центральном месте.

    По умолчанию именованные диапазоны являются абсолютными

    По умолчанию именованные диапазоны действуют как абсолютные ссылки. Например, на этом листе формула для расчета топлива будет:

    Ссылка на D2 является абсолютной (заблокированной), поэтому формулу можно скопировать без изменения D2.

    Если мы назовем D2 «MPG», формула станет:

    Поскольку по умолчанию MPG является абсолютным, формулу можно скопировать в столбец D как есть.

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

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

    Например, вы можете создать общий именованный диапазон «CellAbove» следующим образом:

    1. Выбрать ячейку A2
    2. Control + F3, чтобы открыть диспетчер имен
    3. На вкладке «Относится к» введите: = A1

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

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

    Применить именованные диапазоны к существующим формулам

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

    Затем

    Excel заменит ссылки, имеющие соответствующий именованный диапазон, самим именем.

    Вы также можете применить имена с помощью поиска и замены:

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

    Ключевые преимущества именованных диапазонов

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

    Самым большим преимуществом именованных диапазонов является то, что они упрощают чтение и обслуживание формул. Это потому, что они заменяют загадочные ссылки значимыми именами.Например, рассмотрим этот рабочий лист с данными о планетах в нашей солнечной системе. Без именованных диапазонов формула ВПР для получения «Позиции» из таблицы выглядит довольно загадочной:

    Однако с B3: E11 с именем «данные» и h5 с именем «планета», мы можем написать следующие формулы:

     
     = ВПР (планета, данные, 2,0) // положение
= VLOOKUP (планета, данные, 3,0) // диаметр
= VLOOKUP (планета, данные, 4,0) // спутники

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

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

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

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

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

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

    Именованные диапазоны могут использоваться для навигации

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

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

    Именованные диапазоны упрощают создание гиперссылок. Например, если вы назовете A1 в Sheet1 «дом», вы можете создать гиперссылку в другом месте, которая вернет вас туда.

    Чтобы использовать именованный диапазон внутри функции ГИПЕРССЫЛКА, добавьте символ фунта перед именованным диапазоном:

    Примечание: как ни странно, вы не можете создать гиперссылку на таблицу, как обычное имя диапазона. Однако вы можете определить имя, равное таблице (например, = Table1), и гиперссылку на нее. Если кто-то знает способ напрямую связать таблицу, присоединяйтесь!

    Именованные диапазоны для проверки данных

    Диапазоны имен хорошо подходят для проверки данных, поскольку они позволяют использовать ссылку с логическим именем для проверки ввода с помощью раскрывающегося меню.Ниже диапазон G4: G8 называется «statuslist», затем примените проверку данных со списком, связанным следующим образом:

    Результатом является раскрывающееся меню в столбце E, которое допускает значения только в названном диапазоне:

    Динамические именованные диапазоны

    Диапазоны имен чрезвычайно полезны, когда они автоматически подстраиваются под новые данные на листе. Установленный таким образом диапазон называется «динамическим именованным диапазоном». Есть два способа сделать диапазон динамическим: формулы и таблицы.

    Динамический именованный диапазон с таблицей

    Таблица — это самый простой способ создать динамический именованный диапазон. Выберите любую ячейку в данных, затем используйте сочетание клавиш Control + T:

    .

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

    Динамический именованный диапазон с формулой

    Вы также можете создать динамический именованный диапазон с формулами, используя такие функции, как OFFSET и INDEX.Хотя эти формулы умеренно сложны, они обеспечивают легкое решение, когда вы не хотите использовать таблицу. По ссылкам ниже приведены примеры с полными пояснениями:

    Имена таблиц при проверке данных

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

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

    Видео: как использовать именованные диапазоны с таблицами

    Удаление именованных диапазонов

    Примечание. Если у вас есть формулы, которые относятся к именованным диапазонам, вы можете сначала обновить формулы, прежде чем удалять имена.В противном случае вы увидите # ИМЯ? ошибки в формулах, которые по-прежнему относятся к удаленным именам. Всегда сохраняйте лист перед удалением именованных диапазонов на случай, если у вас возникли проблемы и вам нужно вернуться к исходному состоянию.

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

    Когда вы удаляете * часть * именованного диапазона или вставляете ячейки / строки / столбцы внутри именованного диапазона, ссылка на диапазон изменяется соответствующим образом и остается действительной. Однако, если вы удалите все ячейки, содержащие именованный диапазон, именованный диапазон потеряет ссылку и отобразит ошибку #REF.Например, если я назову A1 «тест», а затем удалю столбец A, диспетчер имен покажет «относится к» как:

    Удалить имена с помощью диспетчера имен

    Чтобы вручную удалить именованные диапазоны из книги, откройте диспетчер имен, выберите диапазон и нажмите кнопку «Удалить». Если вы хотите удалить более одного имени одновременно, вы можете Shift + щелчок или Ctrl + щелчок, чтобы выбрать несколько имен, а затем удалить за один шаг.

    Удалить имена с ошибками

    Если у вас много имен с ошибками ссылок, вы можете использовать кнопку фильтра в диспетчере имен для фильтрации имен с ошибками:

    Затем нажмите Shift + щелкните, чтобы выбрать все имена и удалить.

    Именованные диапазоны и область действия

    Именованные диапазоны в Excel имеют так называемую «область», которая определяет, является ли именованный диапазон локальным для данного рабочего листа или глобальным для всей книги. Глобальные имена имеют область видимости «книга», а локальные имена имеют область действия, равную имени листа, на котором они существуют. Например, область для локального имени может быть «Sheet2».

    Назначение области

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

    Локальный охват

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

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

     
     Лист1! Total_revenue
Лист2! Total_revenue
Лист3! Total_revenue

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

    Глобальный охват

    Глобальная область действия означает, что имя будет работать в любом месте книги.Например, вы можете назвать ячейку «last_update», ввести дату в ячейку. Затем вы можете использовать формулу ниже, чтобы отобразить дату последнего обновления на любом листе.

    Глобальные имена в книге должны быть уникальными.

    Локальный охват

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

    Управление областью именованного диапазона

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

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

    Ян Карел Питерс и Чарльз Уильямс разработали служебную программу под названием Name Manager, которая предоставляет множество полезных операций для именованных диапазонов. Вы можете скачать утилиту Name Manager здесь.

    .

    Добавить комментарий

    Ваш адрес email не будет опубликован. Обязательные поля помечены *