Как сделать именованную ячейку в excel

Добавил пользователь Алексей Ф.
Обновлено: 04.10.2024

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

Однако формула выглядит так (стоит три строки):

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

Где count, var1, var2 и var3 автоматически обновляются до определенного столбца текущей строки. Я могу создать именованный диапазон для каждой ячейки, но это бесполезно. Я могу назвать диапазон столбца, но тогда я не могу найти способ ввести смещение в формулу.

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

Предположим, у меня установлены следующие числа в столбцах с D по F в строках со 2 по 4:

Теперь предположим, что я хочу, чтобы значение в столбце D называлось input1 , столбец E - input2 , а столбец F - input3 :

В Вставить > Имя > Определить .

Теперь, если я напишу свою формулу в столбце G следующим образом, я получу правильные ответы:

Просто, по крайней мере, при использовании Excel 2010:

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

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

По крайней мере, я не знаю о возможности выразить что-то вроде (row + 1) .

Я бы предложил создать таблицу . Выберите диапазон A1:H4 , затем перейдите к виджету Таблицы> Создать> Вставить таблицу с заголовками (на Mac). Это пометит A2:H4 как тело таблицы, а A1:H4 как заголовок.

Я действительно могу порекомендовать видео You Suck at Excel with Joel Spolsky, в котором все это объясняется.

Я не полностью просмотрел предыдущие ответы, но я думаю, что это ближе к тому, что искал @Jason Coyne OP. Итак, я надеюсь, что получу много голосов. ;-)

FWIW, похоже, эта функция доступна с Excel 2007.

Вот скриншот примера:

Вы должны увидеть формулу в E2: =[@Count] * (10*[@Var1] + 20*[@Var2] + 5*[@Var3]) , что довольно близко к тому, что хотел ввести @ jason-coyne.

Мне не нравится, что вас заставляют выбирать стиль (или определять новый, если вы не видите стиль, который вам нравится). Хорошая новость в том, что вы можете переформатировать ячейки как хотите, не отменяя "табличность".

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

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

Если вы используете VBA, вы можете выделить весь столбец и назвать его, скажем, MyCol в поле имени (верхнее левое поле ввода). В вашем коде вы можете ссылаться на ячейку в столбце MyCol (строка 12), используя следующий код:

Возможно, вы сможете использовать функцию row (). Это возвращает текущую строку, в которой вы находитесь. Итак, в зависимости от макета электронной таблицы вы можете использовать ее следующим образом:

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

Используйте функцию Excel под названием именованные ссылки.

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

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

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

Я хотел бы предложить небольшое изменение ссылки на ячейку производства Dror. Это тоже сработает:

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

Все это в сумме не даст вам скучать ;)

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .

Такие ссылки замечательно работают в формулах, например:

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

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

При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT) , которая превращает текст в ссылку:

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

Способ 2. Динамический именованный диапазон

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

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

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

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

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

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Формируем ссылку с помощью ИНДЕКС

Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.

И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:

=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР("я";255) ;A2:A100))

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

Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager) . В открывшемся окне нажмите кнопку Создать (New) , введите имя нашего диапазона и формулу в поле Диапазон (Reference) :

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

Дайте описательные имена определенным ячейкам или диапазонам ячеек

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

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

Эти инструкции относятся к Excel 2019, 2016, 2013, 2010, 2007 и Excel для Office 365.

Одним из, и, возможно, самым простым способом определения имен является использование Именного поля , расположенного над столбцом A на листе. Вы можете использовать этот метод для создания уникальных имен, которые распознаются каждым листом в книге. Чтобы создать имя с помощью поля имени, как показано на рисунке выше:

Выделите требуемый диапазон ячеек на листе.

Введите нужное имя для этого диапазона в Имя окна , например Jan_Sales .

Нажмите клавишу Enter на клавиатуре.

Имя отображается в поле имени .

Имя также отображается в поле Имя , если на листе выделен одинаковый диапазон ячеек. Он также отображается в Менеджере имен .

Правила именования и ограничения

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

Имя не может содержать пробелы.
Первый символ имени должен быть буквой, подчеркиванием или обратной косой чертой.
Остальные символы могут быть только буквами, цифрами, точками или символами подчеркивания.
Максимальная длина имени составляет 255 символов.
Прописные и строчные буквы неотличимы от Excel, поэтому в Excel Jan_Sales и jan_sales рассматриваются как одно и то же имя.
Ссылка на ячейку не может использоваться в качестве имен, таких как A25 или R1C4 .

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

Выделите требуемый диапазон ячеек на листе.

Нажмите на вкладку Формулы на ленте .

В диалоговом окне необходимо указать Имя , Область и Диапазон .

По завершении нажмите ОК , чтобы вернуться на лист.

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

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

Нажмите вкладку Формулы на ленте .

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

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

В этом диалоговом окне вы должны определить Имя , Область и Диапазон .

Нажмите ОК , чтобы вернуться в Менеджер имен , где новое имя будет указано в окне.

Нажмите Закрыть , чтобы вернуться на лист.

Удаление или редактирование имен

Когда менеджер имен открыт:

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

Чтобы удалить имя, нажмите кнопку Удалить над окном списка.

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

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

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

Кнопка Фильтр в Менеджере имен позволяет легко:

Найти имена с ошибками – например, недопустимый диапазон.
Определите область имени – будь то уровень рабочего листа или книга.
Сортировать и фильтровать перечисленные имена – определенные (диапазон) имена или имена таблиц.

Отфильтрованный список отображается в окне списка в Диспетчере имен .

Определенные имена и область действия в Excel

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

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

Область уровня локального рабочего листа

Имя с областью действия уровня листа действительно только для листа, для которого оно было определено. Если имя Total_Sales имеет область действия лист 1 книги, Excel не распознает имя на листе 2 , листе 3 или любой другой лист в книге. Это позволяет определить одно и то же имя для использования на нескольких рабочих листах – при условии, что область действия для каждого имени ограничена его конкретной рабочей таблицей.

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

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

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

Имя: Jan_Sales, Scope – глобальный уровень рабочей книги
Имя: Sheet1! Jan_Sales, Scope – уровень локального листа

Глобальная область уровня рабочей книги

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

Однако имя области действия уровня книги не распознается любой другой книгой, поэтому имена глобального уровня могут повторяться в разных файлах Excel. Например, если имя Jan_Sales имеет глобальную область действия, одно и то же имя можно использовать в разных книгах под названием 2012_Revenue , 2013_Revenue и 2014_Revenue .

Конфликты области и приоритетность области

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

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

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

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

Для чего вообще нужны именованные диапазоны? Обращение к именованному диапазону гораздо удобнее, чем прописывание адреса в формулах и VBA:

Предположим, что в формуле мы ссылаемся на диапазон A1:C10 (возможно даже не один раз). Для примера возьмем простую функцию СУММ(суммирует значения указанных ячеек):
=СУММ( A1:C10 ; F1:K10 )
Затем нам стало необходимо суммировать другие данные(скажем вместо диапазона A1:C10 в диапазоне D2:F11 ). В случае с обычным указанием диапазона нам придется искать все свои формулы и менять там адрес диапазона на новый. Но если назначить своему диапазону A1:C10 имя(к примеру ДиапазонСумм ), то в формуле ничего менять не придется - достаточно будет просто изменить ссылку на ячейки в самом имени один раз. Я привел пример с одной формулой - а что, если таких формул 10? 30?
Примерно такая же ситуация и с использованием в кодах: указав имя диапазона один раз не придется каждый раз при изменении и перемещении этого диапазона прописывать его заново в коде.
Именованный диапазон не просто так называется именованным. Если взять пример выше - то отображение в формуле названия ДиапазонСумм куда нагляднее, чем A1:C10 . В сложных формулах куда проще будет ориентироваться по именам, чем по адресам. Почему удобнее: если сменить стиль отображения ссылок (подробнее про стиль), то диапазон A1:C10 будет выглядеть как-то вроде этого: R1C1:R10C3 . А если назначить имя - то оно как было ДиапазонСумм , так им и останется.
При вводе формулы/функции в ячейку, можно не искать нужный диапазон, а начать вводить лишь первые буквы его имени и Excel предложит его ко вводу:

Данный метод доступен лишь в версиях Excel 2007 и выше

MsgBox Range("ДиапазонСумм").Address MsgBox [ДиапазонСумм].Address

Обращение к именованному диапазону в формулах/функциях

=СУММ( ДиапазонСумм )
=ВПР("Критерий"; ДиапазонСумм ;2;0)
Читать подробнее про функцию ВПР

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

Ограничения, накладываемые на создание имен

В качестве имени диапазона не могут быть использованы словосочетания, содержащие пробел. Вместо него лучше использовать нижнее подчеркивание _ или точку: Name_1, Name.1
Первым символом имени должна быть буква, знак подчеркивания (_) или обратная косая черта (\). Остальные символы имени могут быть буквами, цифрами, точками и знаками подчеркивания
Нельзя в качестве имени использовать зарезервированные в Excel константы - R, C и RC(как прописные, так и строчные). Связано с тем, что данные буквы используются самим Excel для адресации ячеек при использовании стиля ссылок R1C1 (читать подробнее про стили ссылок)
Нельзя давать именам названия, совпадающие с адресацией ячеек: B$100, D2(для стиля ссылок А1) или R1C1, R7(для стиля R1C1). И хотя при включенном стиле ссылок R1C1 допускается дать имени название вроде A1 или D130 - это не рекомендуется делать, т.к. если впоследствии стиль отображения ссылок для книги будет изменен - то Excel не примет такие имена и предложит их изменить. И придется изменять названия всех подобных имен. Если очень хочется - можно просто добавить нижнее подчеркивание к имени: _A1
Длина имени не может превышать 255 символов

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

Способ второй
Выделяем ячейку или группу ячеек. Жмем правую кнопку мыши для вызова контекстного меню ячеек. Выбираем пункт:

Excel 2007: Имя диапазона (Range Name)
Excel 2010: Присвоить имя (Define Name)

либо:
Жмем Ctrl + F3
либо:

2007-2016 Excel : вкладка Формулы (Formulas) -Диспетчер имен (Name Manager) -Создать (New) (либо на той же вкладке сразу - Присвоить имя (Define Name) )
2003 Excel : Вставка -Имя -Присвоить

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

Имя (Name) - указывается имя диапазона. Необходимо учитывать ограничения для имен, которые я описывал в начале статьи.
Область (Scope) - указывается область действия создаваемого диапазона - Книга , либо Лист1 :

Лист1 (Sheet1) - созданный именованный диапазон будет доступен только из указанного листа. Это позволяет указать разные диапазоны для разных листов, но указав одно и тоже имя диапазона
Книга (Workbook) - созданный диапазон можно будет использовать из любого листа данной книги

Примечание (Comment) - здесь можно записать пометку о созданном диапазоне, например для каких целей планируется его использовать. Позже эту информацию можно будет увидеть из диспетчера имен ( Ctrl + F3 )
Диапазон (Refers to) - при данном способе создания в этом поле автоматически проставляется адрес выделенного ранее диапазона. Его можно при необходимости тут же изменить.

Изменение диапазона
Чтобы изменить имя Именованного диапазона, либо ссылку на него необходимо всего лишь вызывать диспетчер имен( Ctrl + F3 ), выбрать нужное имя и нажать кнопку Изменить (Edit. ) .
Изменить можно имя диапазона (Name) , ссылку (RefersTo) и Примечание (Comment) . Область действия (Scope) изменить нельзя, для этого придется удалить текущее имя и создать новое, с новой областью действия.

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

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

Читайте также: