Excel имя ячейки в другой ячейке

Содержание

  • Присвоение наименования
    • Способ 1: строка имен
    • Способ 2: контекстное меню
    • Способ 3: присвоение названия с помощью кнопки на ленте
    • Способ 4: Диспетчер имен
  • Вопросы и ответы

Наименование ячейки в Microsoft Excel

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

Присвоение наименования

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

  • начинаться с буквы, с подчеркивания или со слеша, а не с цифры или другого символа;
  • не содержать пробелов (вместо них можно использовать нижнее подчеркивание);
  • не являться одновременно адресом ячейки или диапазона (то есть, названия типа «A1:B2» исключаются);
  • иметь длину до 255 символов включительно;
  • являться уникальным в данном документе (одни и те же буквы, написанные в верхнем и нижнем регистре, считаются идентичными).

Способ 1: строка имен

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

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

    2. Выделение диапазона в Microsoft Excel

  2. В строку имен вписываем желаемое наименование области, учитывая правила написания названий. Жмем на кнопку Enter.

Строка имени в Microsoft Excel

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

Способ 2: контекстное меню

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

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

    2. Переход к присвоению имени в Microsoft Excel

  2. Открывается небольшое окошко. В поле «Имя» нужно вбить с клавиатуры желаемое наименование.

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

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

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

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

Присвоение имени имени в Microsoft Excel

Название выбранному массиву присвоено.

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

Также название диапазону можно присвоить с помощью специальной кнопки на ленте.

Lumpics.ru

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

    2. Присвоение имени через ленту в Microsoft Excel

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

Способ 4: Диспетчер имен

Название для ячейки можно создать и через Диспетчер имен.

  1. Находясь во вкладке «Формулы», кликаем по кнопке «Диспетчер имен», которая расположена на ленте в группе инструментов «Определенные имена».

    2. Переход в Диспетчер имен в Microsoft Excel

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

    3. Переход к созданию имени из диспетчера имен в Microsoft Excel

  3. Открывается уже хорошо нам знакомое окно добавления имени. Наименование добавляем так же, как и в ранее описанных вариантах. Чтобы указать координаты объекта, ставим курсор в поле «Диапазон», а затем прямо на листе выделяем область, которую нужно назвать. После этого жмем на кнопку «OK».

Создание имени через Диспетчер имен в Microsoft Excel

На этом процедура закончена.

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

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

Редактирование записи в Диспетчере имен в Microsoft Excel

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

Для удаления записи выделяем элемент и жмем на кнопку «Удалить».

Удаление записи в Диспетчере имен в программе Microsoft Excel

После этого открывается небольшое окошко, которое просит подтвердить удаление. Жмем на кнопку «OK».

Подтверждение удаления в Microsoft Excel

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

Фильтр в Диспетчере имен в Microsoft Excel

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

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

Skip to content

Как использовать функцию ДВССЫЛ – примеры формул

В этой статье объясняется синтаксис функции ДВССЫЛ, основные способы ее использования и приводится ряд примеров формул, демонстрирующих использование ДВССЫЛ в Excel.

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

Итак, что такое функция ДВССЫЛ (INDIRECT) в Excel и для чего ее можно использовать? Это очень хороший вопрос, и, надеюсь, вы получите исчерпывающий ответ через несколько минут, когда закончите чтение.

Функция ДВССЫЛ в Excel — синтаксис и основные способы использования

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

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

Функция ДВССЫЛ в Excel возвращает ссылку на ячейку, используя текстовую строку. Она имеет два аргумента, первый является обязательным, а второй – нет:

ДВССЫЛ(ссылка_на_ячейку; [a1])

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

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

  • Если значение ИСТИНА или опущено, то используется ссылка на ячейку в стиле A1.
  • Если ЛОЖЬ, то возвращается ссылка в виде R1C1.

Таким образом, ДВССЫЛ возвращает либо ссылку на ячейку, либо ссылку на диапазон.

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

Как работает функция ДВССЫЛ

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

Предположим, у вас есть число 5 в ячейке A1 и текст «A1» в ячейке C1. Теперь поместите формулу =ДВССЫЛ(C1) в любую другую ячейку и посмотрите, что произойдет:

  • Функция ДВССЫЛ обращается к значению в ячейке C1. Там в виде текстовой строки записан адрес «A1».
  • Функция ДВССЫЛ направляется по этому адресу в ячейку A1, откуда извлекает записанное в ней значение, то есть число 555.

Итак, в этом примере функция ДВССЫЛ преобразует текстовую строку в ссылку на ячейку.

Аналогичным образом можно получить ссылку на диапазон. Для этого просто нужно функции ДВССЫЛ указать два адреса – начальный и конечный. Вы видите это на скриншоте ниже.

Формула   ДВССЫЛ(C1&»:»&C2) извлекает адреса из указанных ячеек и превращается в =ДВССЫЛ(«A1:A5»).

В итоге мы получаем ссылку =A1:A5

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

Как использовать ДВССЫЛ в Excel — примеры формул

Как показано в приведенном выше примере, вы можете использовать функцию ДВССЫЛ, чтобы записать адрес ячейки как обычную текстовую строку и получить в результате значение этой ячейки. Однако этот простой пример — не более чем намек на возможности ДВССЫЛ.

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

Создание косвенных ссылок из значений ячеек

Как вы помните, функция ДВССЫЛ в Excel позволяет использовать стили ссылок A1 и R1C1. Обычно вы не можете использовать оба стиля на одном листе одновременно. Вы можете переключаться между двумя типами ссылок только с помощью опции «Файл» > «Параметры» > «Формулы» > R1C1 . По этой причине пользователи Excel редко рассматривают использование R1C1 в качестве альтернативного подхода к созданию ссылок.

В формуле ДВССЫЛ вы можете использовать любой тип ссылки на одном и том же листе, если хотите. Прежде чем мы двинемся дальше, давайте более подробно рассмотрим разницу между стилями ссылок A1 и R1C1.

Стиль A1 — это обычный и привычный всем нам тип адресации в Excel, который указывает сначала столбец, за которым следует номер строки. Например, B2 обозначает ячейку на пересечении столбца B и строки 2.

Стиль R1C1 является обозначает координаты ячейки наоборот – за строками следуют столбцы, и к этому нужно привыкнуть:) Например, R5C1 относится к ячейке A5, которая находится в строке 5, столбце 1 на листе. Если после буквы не следует какая-либо цифра, значит, вы имеете в виду ту же строку или столбец, в которых записана сама формула.

А теперь давайте сравним на простом примере, как функция ДВССЫЛ обрабатывает адреса вида A1 и R1C1:

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

  • Формула в ячейке D1:   =ДВССЫЛ(C1)

Это самый простой вариант. Формула обращается к ячейке C1, извлекает ее значение — текстовую строку «A2» , преобразует ее в ссылку на ячейку, переходит к ячейке A2 и возвращает ее значение, равное 456.

  • Формула в ячейке D3:  =ДВССЫЛ(C3;ЛОЖЬ)

ЛОЖЬ во втором аргументе указывает, что указанное значение (C3) следует рассматривать как ссылку на ячейку в формате R1C1, т. е. сначала идет номер строки, за которым следует номер столбца. Таким образом, наша формула ДВССЫЛ интерпретирует значение в ячейке C3 (R2C1) как ссылку на ячейку на пересечении строки 2 и столбца 1, которая как раз и является ячейкой A2.

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

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

В следующем примере формула =ДВССЫЛ(«А»&C1) возвращает значение из ячейки А1 на основе следующей логической цепочки:

Функция ДВССЫЛ объединяет элементы в первом аргументе ссылка_на_ячейку — текст «А» и значение из ячейки C1. Значение в C1 – это число 1, что в результате формирует адрес А1. Формула переходит к ячейке А1 и возвращает ее значение – 555.

Использование функции ДВССЫЛ с именованными диапазонами

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

Предположим, у вас есть следующие именованные диапазоны на вашем листе:

  • Яблоки – С2:E2
  • Лимоны — C3: E3
  • Апельсины – C4:E4 и так далее по каждому товару.

Чтобы создать динамическую ссылку Excel на любой из указанных выше диапазонов с цифрами продаж, просто запишите его имя, скажем, в H1, и обратитесь к этой ячейке при помощи формулы =ДВССЫЛ(H1).

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

  • =СУММ(ДВССЫЛ (H1))
  • =СРЗНАЧ(ДВССЫЛ (H1))
  • =МАКС(ДВССЫЛ (H1))
  • =МИН(ДВССЫЛ (H1))

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

ДВССЫЛ для ссылки на другой рабочий лист

Полезность функции Excel ДВССЫЛ не ограничивается созданием «динамических» ссылок на ячейки. Вы также можете использовать ее для формирования ссылки на другие листы.

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

Нам поможет формула:

=ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

Давайте разбираться, как работает эта формула.

Как вы знаете, обычным способом сослаться на другой лист в Excel является указание имени этого листа, за которым следуют восклицательный знак и ссылка на ячейку или диапазон, например Лист1!A1:С10. Так как имя листа часто содержит пробелы, вам лучше заключить его (имя, а не пробел :) в одинарные кавычки, чтобы предотвратить возможную ошибку, например,

‘Лист 1!’$A$1 или для диапазона – ‘Лист 1!’$A$1:$С$10 .

Наша задача – сформировать нужный текст и передать его функции ДВССЫЛ. Все, что вам нужно сделать, это:

  • записать имя листа в одну ячейку,
  • букву столбца – в другую,
  • номер строки – в третью,  
  • объединить всё это в одну текстовую строку,
  • передать этот адрес функции ДВССЫЛ. 

Помните, что в текстовой строке вы должны заключать каждый элемент, кроме номера строки, в двойные кавычки и затем связать все элементы в единое целое с помощью оператора объединения (&).

С учетом вышеизложенного получаем шаблон ДВССЫЛ для создания ссылки на другой лист:

ДВССЫЛ («‘» & имялиста & «‘!» & имя столбца нужной ячейки & номер строки нужной ячейки )

Возвращаясь к нашему примеру, вы помещаете имя листа в ячейку A2 и вводите адреса столбца и строки в B2 и С2, как показано на скриншоте выше. В результате вы получите следующую формулу:

ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

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

Замечание.

  • Если какая-либо из ячеек, содержащих имя листа и адреса ячеек (A2, B2 и c2 в приведенной выше формуле), будет пуста, ваша формула вернет ошибку. Чтобы предотвратить это, вы можете обернуть функцию ДВССЫЛ в функцию ЕСЛИ :

ЕСЛИ(ИЛИ(A2=»»;B2=»»;C2-“”); «»; ДВССЫЛ(«‘»&A2&»‘!»&B2&C2)

  • Чтобы формула ДВССЫЛ, ссылающаяся на другой лист, работала правильно, указанный лист должен быть открыт в Экселе, иначе формула вернет ошибку #ССЫЛКА. Чтобы не видеть сообщение об ошибке, которое может портить вид вашей таблицы, вы можете использовать функцию ЕСЛИОШИБКА, которая будет отображать пустую строку при любой возникшей ошибке:

ЕСЛИОШИБКА(ДВССЫЛ(«‘»&A2&»‘!»&B2&C2); «»)

Формула ДВССЫЛ для ссылки на другую книгу Excel

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

Чтобы упростить задачу, давайте начнем с создания ссылки на другую книгу обычным способом (апострофы добавляются, если имена вашей книги и/или листа содержат пробелы):
‘[Имя_книги.xlsx]Имя_листа’!Арес_ячейки

Но, чтобы формула была универсальной, лучше апострофы добавлять всегда – лишними не будут .

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

=ДВССЫЛ(«‘[«&$A$2&».xlsx]»&$B$2&»‘!»&C2&D2)

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

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

=ДВССЫЛ(«‘[INDIRECT.xlsx]Продажи’!D3»)

Ну а итоговый результат вы видите на скриншоте ниже.

Hbc6

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

=ДВССЫЛ(«‘[» & Название книги & «]» & Имя листа & «‘!» & Адрес ячейки )

Примечание. Рабочая книга, на которую ссылается ваша формула, всегда должна быть открыта, иначе функция ДВССЫЛ выдаст ошибку #ССЫЛКА. Как обычно, функция ЕСЛИОШИБКА может помочь вам избежать этого:

=ЕСЛИОШИБКА(ДВССЫЛ(«‘[«&$A$2&».xlsx]»&$B$2&»‘!»&C2&D2); «»)

Использование функции Excel ДВССЫЛ чтобы зафиксировать ссылку на ячейку

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

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

  1. Введите любое значение в любую ячейку, например, число 555 в ячейку A1.
  2. Обратитесь к A1 из двух других ячеек тремя различными способами: =A1, =ДВССЫЛ(«A1») и ДВССЫЛ(С1), где в С1 записан адрес «А1».
  3. Вставьте новую строку над строкой 1.

Видите, что происходит? Ячейка с логическим оператором =А1 по-прежнему возвращает 555, потому что ее формула была автоматически изменена на =A2 после вставки строки. Ячейки с формулой ДВССЫЛ теперь возвращают нули, потому что формулы в них не изменились при вставке новой строки и они по-прежнему ссылаются на ячейку A1, которая в настоящее время пуста:

После этой демонстрации у вас может сложиться впечатление, что функция ДВССЫЛ больше мешает, чем помогает. Ладно, попробуем по-другому.

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

=СУММ(A2:A5)

Однако вы хотите, чтобы формула оставалась неизменной, независимо от того, сколько строк было удалено или вставлено. Самое очевидное решение — использование абсолютных ссылок — не поможет. Чтобы убедиться, введите формулу =СУММ($A$2:$A$5) в какую-нибудь ячейку, вставьте новую строку, скажем, в строку 3, и увидите формулу, преобразованную в =СУММ($A$2:$A$6).

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

Решение состоит в использовании функции ДВССЫЛ, например:

=СУММ(ДВССЫЛ(«A2:A5»))

Поскольку Excel воспринимает «A1: A5» как простую текстовую строку, а не как ссылку на диапазон, он не будет вносить никаких изменений при вставке или удалении строки (строк), а также при их сортировке.

Использование ДВССЫЛ с другими функциями Excel

Помимо СУММ, ДВССЫЛ часто используется с другими функциями Excel, такими как СТРОКА, СТОЛБEЦ, АДРЕС, ВПР, СУММЕСЛИ и т. д.

Пример 1. Функции ДВССЫЛ и СТРОКА

Довольно часто функция СТРОКА используется в Excel для возврата массива значений. Например, вы можете использовать следующую формулу массива (помните, что для этого нужно нажать Ctrl + Shift + Enter), чтобы вернуть среднее значение трех наименьших чисел в диапазоне B2:B13

{=СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(1:3)))}

Однако, если вы вставите новую строку в свой рабочий лист где-нибудь между строками 1 и 3, диапазон в функции СТРОКА изменится на СТРОКА(1:4), и формула вернет среднее значение четырёх наименьших чисел вместо трёх.

Чтобы этого не произошло, вставьте ДВССЫЛ в функцию СТРОКА, и ваша формула массива всегда будет оставаться правильной, независимо от того, сколько строк будет вставлено или удалено:

={СРЗНАЧ(НАИМЕНЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}

Аналогично, если нам нужно найти сумму трёх наибольших значений, можно использовать ДВССЫЛ вместе с функцией СУММПРОИЗВ.

Вот пример:

={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:3»))))}

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

={СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B13;СТРОКА(ДВССЫЛ(«1:»&C1))))}

Согласитесь, что получается достаточно гибкий расчёт.

Пример 2. Функции ДВССЫЛ и АДРЕС

Вы можете использовать Excel ДВССЫЛ вместе с функцией АДРЕС, чтобы получить значение в определенной ячейке на лету.

Как вы помните, функция АДРЕС используется в Excel для получения адреса ячейки по номерам строк и столбцов. Например, формула =АДРЕС(1;3) возвращает текстовую строку «$C$1», поскольку C1 — это ячейка на пересечении 1-й строки и 3-го столбца.

Чтобы создать ссылку на ячейку, вы просто встраиваете функцию АДРЕС в формулу ДВССЫЛ, например:

=ДВССЫЛ(АДРЕС(1;3))

Конечно, эта несложная формула лишь демонстрирует технику. Более сложные примеры использования функций ДВССЫЛ И АДРЕС в Excel см. в статье Как преобразовать строки в столбцы в Excel .

И вот еще несколько примеров формул в которых используется функция ДВССЫЛ, и которые могут оказаться полезными:

  • ВПР и ДВССЫЛ — как динамически извлекать данные из разных таблиц (см. пример 2).
  • Excel ДВССЫЛ и СЧЁТЕСЛИ — как использовать функцию СЧЁТЕСЛИ в несмежном диапазоне или нескольких выбранных ячейках.

Использование ДВССЫЛ для создания выпадающих списков

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

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

В ячейке А1 вы создаете простой выпадающий список с названиями имеющихся именованных диапазонов. Для второго зависимого выпадающего списка в ячейке В2 вы используете простую формулу  =ДВССЫЛ(A1), где A1 — это ячейка, в которой выбрано имя нужного именованного диапазона.

К примеру, выбрав в первом списке второй квартал, во втором списке мы видим месяцы этого квартала.

Рис9

Чтобы сделать более сложные трехуровневые меню или раскрывающиеся списки с многоуровневыми записями, вам понадобится немного более сложная формула ДВССЫЛ в сочетании с вложенной функцией ПОДСТАВИТЬ.

Подробное пошаговое руководство по использованию ДВССЫЛ с проверкой данных Excel смотрите в этом руководстве: Как создать зависимый раскрывающийся список в Excel.

Функция ДВССЫЛ Excel — возможные ошибки и проблемы

Как показано в приведенных выше примерах, функция ДВССЫЛ весьма полезна при работе со ссылками на ячейки и диапазоны. Однако не все пользователи Excel охотно принимают этот подход, в основном потому, что постоянное использование ДВССЫЛ приводит к отсутствию прозрачности формул Excel и несколько затрудняет их понимание. Функцию ДВССЫЛ сложно просмотреть и проанализировать ее работу, поскольку ячейка, на которую она ссылается, не является конечным местоположением значения, используемого в формуле. Это действительно довольно запутанно, особенно при работе с большими сложными формулами.

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

Ошибка #ССЫЛКА! 

Чаще всего функция ДВССЫЛ возвращает ошибку #ССЫЛКА!  в следующих случаях:

  1. Аргумент ссылка_на_ячейку не является допустимой ссылкой Excel. Если вы пытаетесь передать функции текст, который не может обозначать ссылку на ячейку (например, «A1B0»), то формула приведет к ошибке #ССЫЛКА!. Во избежание возможных проблем проверьте аргументы функции ДВССЫЛ .
  2. Превышен предел размера диапазона. Если аргумент ссылка_на_ячейку вашей формулы ДВССЫЛ ссылается на диапазон ячеек за пределами строки  1 048 576 или столбца  16 384, вы также получите ошибку #ССЫЛКА в Excel 2007 и новее. Более ранние версии Excel игнорируют превышение этого лимита и действительно возвращают некоторое значение, хотя часто не то, что вы ожидаете.
  3. Используемый в формуле лист или рабочая книга закрыты.Если ваша формула с ДВССЫЛ адресуется на другую книгу или лист Excel, то эта другая книга или электронная таблица должны быть открыты, иначе ДВССЫЛ возвращает ошибку #ССЫЛКА! . Впрочем, это требование характерно для всех формул, которые ссылаются на другие рабочие книги Excel.

Ошибка #ИМЯ? 

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

Ошибка из-за несовпадения региональных настроек.

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

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

В стандартной конфигурации Windows для Северной Америки и некоторых других стран разделителем списка по умолчанию является запятая. 

В результате при копировании формулы между двумя разными языковыми стандартами Excel вы можете получить сообщение об ошибке « Мы обнаружили проблему с этой формулой… », поскольку разделитель списка, используемый в формуле, отличается от того, что установлен на вашем компьютере. Если вы столкнулись с этой ошибкой при копировании какой-либо НЕПРЯМОЙ формулы из этого руководства в Excel, просто замените все запятые (,) точками с запятой (;) (либо наоборот). В обычных формулах Excel эта проблема, естественно, не возникнет. Там Excel сам поменяет разделители исходя из ваших текущих региональных настроек.

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

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

Вот еще несколько статей по той же теме:

Как удалить сразу несколько гиперссылок — В этой короткой статье я покажу вам, как можно быстро удалить сразу все нежелательные гиперссылки с рабочего листа Excel и предотвратить их появление в будущем. Решение работает во всех версиях Excel,…
Как использовать функцию ГИПЕРССЫЛКА — В статье объясняются основы функции ГИПЕРССЫЛКА в Excel и приводятся несколько советов и примеров формул для ее наиболее эффективного использования. Существует множество способов создать гиперссылку в Excel. Чтобы сделать ссылку на…
Гиперссылка в Excel: как сделать, изменить, удалить — В статье разъясняется, как сделать гиперссылку в Excel, используя 3 разных метода. Вы узнаете, как вставлять, изменять и удалять гиперссылки на рабочих листах, а также исправлять неработающие ссылки. Гиперссылки широко используются…
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…

Источник

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

Содержание

  • Требования к именам ячеек
  • Метод 1: строка имен
  • Метод 2: использование контекстного меню
  • Метод 3: применение инструментов на ленте
  • Метод 4: работа в Диспетчере имен
  • Заключение

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

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

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

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

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

Имя ячейки в Эксель

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

Формула с именем ячейки в Эксель

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

Формула с именем ячейки в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Источник

Значение ячейки как имя другой ячейки

Snomv

 Дата: Пятница, 15.03.2013, 08:16 |
Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9


Репутация:
0
±

Замечаний:
0% ±


Допустим есть именованные ячейки (я1…я10). Путем некоторых манипуляций, например, «я»&»7» получаю в ячейке значение я7. Как мне воспользоваться этим значением, чтобы взять данные из я7? Заранее благодарен.
 

Ответить

Pelena

 Дата: Пятница, 15.03.2013, 08:19 |
Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 18797


Репутация:
4284
±

Замечаний:
±


Excel 2016 & Mac Excel

С помощью функции ДВССЫЛ()

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

 

Ответить

Snomv

 Дата: Пятница, 15.03.2013, 09:02 |
Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9


Репутация:
0
±

Замечаний:
0% ±


Огромное спасибо. То что надо.
 

Ответить
Источник

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

​Смотрите также​ алгоритме. Для этого:​ Вы без проблем​ и его используют​Учет регистра​Вставка​.​$2,633​ в Excel?​ имя​ НДС в имени​ скобки. Всё. Формула​ название листа. =Лист1!А1:В16.​. Затем можно создать,​ ли она вам,​В поле​Выделите требуемую область (на​

Используем поле Имя

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

  1. ​Вы можете создать список​Север​Урок подготовлен для Вас​Комиссионные​ формулы пишем такую​Присвоить имя в Excel
  2. ​ получилась такая. =СУММ(Итог[Столбец1])​​ Нажимаем «ОК».​​ изменить, удалить и​ с помощью кнопок​Диапазон​ данном этапе можно​​ присвоить имя ячейке​​ C8, а именно​Присвоить имя в Excel
  3. ​ валют (изменяя значения​​ Преимущества абсолютных ссылок​​ и строчных букв,​имя​
  4. ​ имен всех имеющихся​500 долл. США​​ командой сайта office-guru.ru​​. Теперь нашу формулу​ =Лист!$A9/100*18 Получилось так,​ Получилось.​Второй способ.​ найти все имена,​ внизу страницы. Для​отображается адрес активной​ выделить любую область,​Присвоить имя в Excel
  5. ​ или диапазону. Мы​ так: =СУММ(ДВССЫЛ(A8) ДВССЫЛ(B8)).​​ ячеек F2 и​​ очевидны. При изменении​ но Excel не​и выберите команду​ ячеек в книге.​Присвоить имя в Excel
  6. ​$1,211​Автор: Антон Андронов​ можно записать в​ смотрите столбец С.​В ячейке А5 стоит​Если таблицу сделали​

Используем диалоговое окно Создание имени

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

  1. ​ же в рамках​ И нажмите Enter.​ G2), а цены​ только одной ячейки​ различать их. Например​определить​ Выберите место с​Средний Запад​Присвоить имя в Excel
  2. ​Автор: Антон Андронов​​ следующем виде:​​Так можно использовать имя​​ сумма. Обратите внимание.​​ через функцию «Таблица»​Присвоить имя в Excel
  3. ​Дополнительные сведения об именах​​ ссылку на оригинал​​ ячейки или диапазона,​Присвоить имя в Excel
  4. ​ сможете ее перезадать).​​ данного урока рассмотрим​​ В результате формула​ будут автоматически пересчитаны.​ автоматически пересчитывается целый​​ если с именем​​.​ двумя пустыми столбцами​$3,500​Примечание:​Как видите, новая форма​ для длинных, сложных​ Когда эта ячейка​ на закладке «Вставка»,​ в формулах​ (на английском языке).​ которые мы выбрали​ Мы выделим ячейку​
  5. ​ только 2 самых​​ выдала ошибку: #ССЫЛКА!​​Примечание. Курсы валют можно​ диапазон ячеек без​ «Продажи» на одну​Вы можете создать список​ на листе (список​$3,322​Мы стараемся как​​ записи формулы стала​​ формул.​ с результатом выделена,​ то имя этой​Использование структурированных ссылок в​Использование имен позволяет значительно​ ранее. При необходимости​ С3, а затем​ распространенных, думаю, что​ Не переживайте по​ хранить не только​​ лишних изменений.​​ ячейку и введите​
  6. ​ имен всех имеющихся​​ будет содержать два​​Центральное​ можно оперативнее обеспечивать​ более очевидной и​В Excel есть​ то появился значок​ таблицы уже присвоено​ формулах таблиц Excel​ упростить понимание и​ данный диапазон можно​ ее перезададим.​Присвоить имя в Excel
  7. ​ каждый из них​​ этому поводу, все​​ в значениях ячеек,​Главным недостатком абсолютных ссылок​ его имя другой​ ячеек в книге.​ столбца: один для​2000 долл. США​ вас актуальными справочными​ простой для восприятия.​ несколько способов прибавить​​ фильтра (треугольник). Нажимаем​​ в момент её​Общие сведения о формулах​Присвоить имя в Excel​ изменение формул. Вы​ перезадать. Для этого​Перейдите на вкладку​ Вам обязательно пригодится.​ под контролем, делаем​ но и в​ является плохая читабельность​

    Присвоить имя в Excel

  8. ​ ячейке «Продажи» в​ Выберите место с​​ имен и один​​$2,120​Присвоить имя в Excel

​ материалами на вашем​ Можно пойти еще​ или отнять проценты.​ на стрелку и​ создания.​ в Excel​ можете задать имя​

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

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

​ поле​и выберите команду​
​ рассматривать способы присвоения​

​Создадим еще 2 имени.​

office-guru.ru

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

​ в поле диапазон​​ для долгосрочного использования​ второе имя заменит​ на листе (список​ на которые ссылается​+C.​ переведена автоматически, поэтому​ значения комиссионных создать​ или скидку цены.​ – можно выбрать​ через функцию «Таблица».​ на ячейку​ функции, константы или​Диапазон​Присвоить имя​ имен в Excel,​ Выделите диапазон A2:A5​ введите значение текущего​ вместо абсолютных ссылок​ первое имя.​ будет содержать два​

​ на имя). Выделите​Выделите на листе ячейку​ ее текст может​ именованную константу. В​ Смотрите статью «Как​ среднее значение в​ Автоматически присвоилось название​Можно​ таблицы. Начав использовать​, вокруг указанной области​.​ обратитесь к этому​

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

  1. ​ чтобы присвоить ему​

  2. ​ курса.​​ лучше использовать имена.​​В основном пользователи Excel​

    Поле

  3. ​ столбца: один для​

Присвоение имен из выделенного диапазона

  1. ​ ячейку, которая будет​ A1, а затем​ содержать неточности и​ этом случае исчезнет​

  2. ​ умножить в Excel​​ столбце А, максимальное,​​ этой таблицы «Таблица1».​​в​

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

  4. ​ необходимость выделять под​​ несколько ячеек на​​ минимальное, т.д.​

    ​ Имя этой таблицы​Excel присвоить имя​ вы сможете с​

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

  1. ​Мышкой выделите новую область​Создание имени​

  2. ​ несколько простых, но​ этого выберите инструмент:​Теперь приведем более наглядный​

  3. ​ же преимуществами, но​ имен диапазонов. При​ для этих ячеек,​

    ​ списка. В меню​​+V.​​ нас важно, чтобы​​ нее отдельную ячейку​​ число, проценты».​Чтобы вспомнить, как​

  4. ​ нельзя удалить через​

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

  1. ​ ячейке, диапазону, таблице,​​ легкостью обновлять, проверять​​ или укажите эту​​.​​ полезных правил по​ «Формулы»-«Присвоить имя». Заполните​ пример существенного преимущества​ и улучшают читабельность​

См. также

​ использовании имени в​ на которые ссылается​

​Вставка​Более новые версии​

​ эта статья была​ на рабочем листе​

​Создавать и применять формулы​ правильно и быстро​

support.office.com

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

​ функцию «Диспетчер задач»,​​ формуле​​ имена и управлять​ область, введя диапазон​В поле​​ созданию имени.​ диалоговое окно как​ использования имен.​ формул. Это существенно​ формулах, к нему​ на имя). Выделите​наведите указатель мыши​ Office 2011 г.​ вам полезна. Просим​
​ Excel.​ в Excel гораздо​ написать имя диапазона​ пока есть сама​, т.д. Это имя​ ими.​ прямо в текстовое​
​Имя​Данный способ является самым​ на рисунке. А​Создайте отчет по продажам​ повысит производительность пользователя​ обращаются как к​ ячейку, которая будет​
​имя​ ​
​ вас уделить пару​
​Назначая имена ячейкам и​ проще, когда вместо​ в формуле, смотрим​ таблица. Но можно​ поможет при составлении​
​Выделите ячейку.​ поле. В нашем​введите требуемое имя.​ быстрым способом присвоить​ потом выделите диапазон​ за первый квартал​ при редактировании формул​ абсолютной ссылке на​ левый верхний угол​и выберите команду​Выделите ячейки от C2​ секунд и сообщить,​ диапазонам в Excel,​ адресов ячеек и​ статью «Как написать​ изменить это имя.​ длинной формулы, поможет​Введите имя в поле​ случае мы выберем​ В нашем случае​ имя ячейке или​ B1:D1 и присвойте​
​ по 4-ем магазинам​
​ для внесения поправок​ диапазон ячеек. Хотя​ списка. В меню​Вставить​ до C5, какие​ помогла ли она​ мы приобретаем еще​​ диапазонов в них​ формулу в Excel».​ Нажимаем на таблицу.​ ввести все правильно,​Имя​ ячейку D2.​ это имя​ диапазону в Excel.​ ему имя «месяцы».​ как на рисунке:​ или изменения порядка​ в предыдущем уроке​Вставка​. В диалоговом окне​ фактические продажи для​ вам, с помощью​ одно очень полезное​ используются имена. Имя​​Как присвоить имя формуле​ На закладке «Работа​ не ошибившись. Ещё​
​.​
​Если Вас все устраивает,​Коэффициент​ Чтобы воспользоваться им,​Создаем выпадающий список для​С помощью формулы и​ аргументов вычислений. Даже​ мы присвоили имя​
​наведите указатель мыши​Вставка имен​ Восточной Запад Юг,​ ​ кнопок внизу страницы.​​ преимущество – возможность​
​ выступает как бы​ Excel.​ с таблицами» ->​ – это подстраховка,​Нажмите клавишу ВВОД.​ смело жмите​. В ряде случаев​ выполните следующие шаги:​ безошибочного запроса к​
​ оператора пересечения множеств​ спустя несколько лет​ не диапазону, а​имя​нажмите кнопку​ и Северной областей.​ Для удобства также​ быстро выделять эти​ идентификатором какого-либо элемента​Присваивают имя формуле​
​ «Конструктор» в разделе​ чтобы нечаянно не​Выберите диапазон, которому вы​ОК​​ Excel автоматически подставляет​Выделите ячейку или диапазон,​ нашей мини базе​ мы будем работать​​ Вы откроете такой​ числу (значению).​и выберите команду​Вставить список​В левой части строка​ приводим ссылку на​ области. Например, чтобы​ рабочей книги. Имя​ в том случае,​ «Свойства» в строке​ удалить в формуле​ хотите присвоить имя,​
​. Имя будет создано.​ имя на основе​ которым необходимо присвоить​ данных. Перейдите в​ с этим отчетом​ документ и быстро​
​Приготовьте лист, на котором​Вставить​
​.​ формул, в поле​ оригинал (на английском​ выделить область, у​ может присваиваться ячейкам,​ если формула большая​​ «имя таблицы» убираем​​ символ, т.д.​ включая заголовки строк​Помимо присвоения имен ячейкам​ данных в соседних​ имя. В нашем​
Как присвоить имя формуле в Excel.​ ячейку А8 и​ как с базой​ сориентируетесь в алгоритмах​ расходы будут пересчитаны​. В диалоговом окне​Выделите ячейки от C2​ имени введите​ языке) .​ которой есть имя,​ диапазонам, таблицам, диаграммам,​ и часто применяется.​ старое имя («Таблица1»)​Как присвоить имя​ и столбцов.​ и диапазонам, иногда​ ячейках. В нашем​ случае это диапазон​​ выберите инструмент: «Данные»-«Работа​ данных. В ячейках​ расчетов в сложных​ из одной валюты​Вставка имен​ до C5, какие​MyRegions​Можно создавать формулы, которые​ достаточно щелкнуть по​ фигурам и т.д.​ Например, присвоим имя​ и пишем свое.​​ ячейке (ячейка –​На вкладке​ полезно знать, как​ случае так и​ B2:B13.​ с данными»-«Проверка данных».​ A8 и B8​ отчетах. Тем более​
​ в другую.​нажмите кнопку​ фактические продажи для​
​и нажмите клавишу​ легко понять, используя​ полю​ Мы же рассмотрим​ формуле, которая будет​Мы заменили имя этой​ это тоже диапазон,​Формулы​ присвоить имя константе.​

excel-office.ru

Знакомство с именами ячеек и диапазонов в Excel

​ произошло. Если Excel​Щелкните по полю​ В появившемся окне:​ мы создаем запрос​ это важно, если​Пересчет должен выполняться соответственно​Вставить список​ Восточной Запад Юг,​ RETURN.​ описательные имена для​Имя​ только имена, назначаемые​рассчитывать НДС​ таблицы на имя​ состоящий из одной​нажмите кнопку​ Как это сделать​

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

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

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

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

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

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

​Описание​ формул, в поле​ «MyRegions».​ значений константа.​Диапазон будет выделен:​ формулам Excel.​ А. Вынем НДС​Можно воспользоваться шаблоном​ в Excel».​Создание имен из выделенного​Итак, в данном уроке​ введите требуемое Вам​ рассмотренные здесь. Пусть​

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

​ рисунке. И нажмите​ Сначала создадим все​Теперь рассмотрим использование имен​ формулы, чтобы при​Допустимые символы​ имени введите​Выделите ячейку C9, а​Чтобы узнать, как присваивать​Существует несколько способов вставить​Приведем небольшой пример. Представим,​ из суммы. Таблица​​ готовой таблицы Excel.​​Как присвоить имя​ диапазона​

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

​ Вы узнали, как​

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

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

​ имя самостоятельно.​ это будет имя​ ОК.​ имена:​ как альтернативный вариант​

  1. ​ их изменении не​Первый символ имени должен​MyRegions​Имена ячеек и диапазонов в Excel
  2. ​ затем введите​ имена ссылки на​ имя в формулу​ что мы продаем​Имена ячеек и диапазонов в Excel
  3. ​ такая.​ Подробнее о такой​​ сразу нескольким диапазонам,​​укажите место, содержащее​ присвоить имя ячейке​​В раскрывающемся списке​​Продажи_по_месяцам​Имена ячеек и диапазонов в Excel

​Таким же образом создайте​Выделите диапазон ячеек A2:D5​ для выше описанной​ пришлось редактировать каждую​ быть букву или​и нажмите клавишу​средних продаж для моих​ ячейки и создавать​

  • ​ Excel, Вы можете​ элитную косметику и​Нажимаем на ячейку В9.​
  • ​ таблице и ее​ а не каждому​ заголовки, установив флажок​
  • ​ или диапазону в​
  • ​Область​.​

​ второй список с​ и выберите инструмент:​
​ задачи:​

​ ячейку.​

office-guru.ru

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

​ символ подчеркивания (_).​​ RETURN.​ регионов​ формулы, которые могут​ использовать любой из​ получаем фиксированный процент​ Вызываем функцию «Присвоить​ функциях, читайте в​ в отдельности, смотрите​в строке выше​ Excel. Если желаете​Вы можете указать​Нажмите клавишу​ месяцами в ячейке​ «Формулы»-«Создать из выделенного».​Выделите диапазон ячеек F1:G2​Для решения данной задачи​ Остаточная символы в​В ячейках C2 –​.​ использовать их, используйте​ предложенных ниже:​

​ комиссионных с продаж.​ имя». Заполняем диалоговое​ статье «Готовые таблицы​ в статье «Связанные​в столбце слева​ получить еще больше​

​ область видимости создаваемого​Enter​ B8.​ В появившемся окне​ и выберите инструмент:​ мы можем обойтись​ имени могут быть​ C5 назначается имя​

Копирование примера данных

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

  1. ​ окно. Мы, в​ Excel».​

  2. ​ выпадающие списки в​,​

    ​ информации об именах,​​ имени. Область видимости​, и имя будет​Готово! В результате мы​ отмечаем вторую опцию​ «Формулы»-«Определенные имена»-«Создать из​

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

    Выделите образец данных

    ​ «MyRegions».​

    ​= AVERAGE​

    ​ и описанные ниже​

    ​ и имя подставится​

    ​ представлена таблица, которая​

    ​ примере, написали имя​

    ​Как применить имя таблицы​

    ​ Excel» тут.​

    ​в строке ниже​

    ​ читайте следующие статьи:​

    ​ – это область,​

    ​ создано.​

    ​ теперь можем уверенно​

    ​ сверху: «в столбце​

    ​ выделенного».​

    ​ с помощью абсолютных​

    ​ и символы подчеркивания.​

    ​Выделите ячейку C9, а​

    ​(MyRegions)​

    ​ процедуры.​

    ​ в формулу автоматически.​

  3. ​ содержит объем продаж​COMMAND​ формуле «НДС», указали​

  4. ​ и диапазона в​Как присвоить имя таблице​или​COMMAND​Знакомство с именами ячеек​

Создание формулы с назначением имени для ячейки

​ где вы сможете​Если нажать на раскрывающийся​ работать с нашей​

  1. ​ слева».​В появившемся окне «Создание​ ссылок. Ниже приведем​ В некоторых языках​ затем введите​

  2. ​и нажмите клавишу RETURN.​Чтобы лучше понять описываемые​Начните вводить название имени​​ по месяцам, а​​ область – «Книга».​ формуле​

    ​ Excel.​в столбце справа​ и диапазонов в​

  3. ​ использовать созданное имя.​ список поля​​ базой данных. Указываем​Выделите диапазон ячеек B1:D5​​ имен из выделенного​

  4. ​ пример. Но имена​​ Excel может заменить​​средних продаж для моих​​Результат — 2661.5.​

    ​ действия, скопируйте пример​

  5. ​ вручную, и оно​

  6. ​ в ячейке D2​​ В строке «Диапазон»​​Excel​​Первый способ.​​.​​ Excel​ Поле ​ Если вы укажете​​Имя​​ параметры запроса, а​

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

    ​Выделите ячейку, содержащую 2661.5.​​ данных в ячейку​

    • ​ отобразится в списке​ хранится процент комиссионных.​ пишем формулу. =Лист!$А9/118*18​​.​​Если таблицу нарисовали​​Выберите​​5 полезных правил и​​Книга​​, Вы сможете увидеть​

    • ​ в ячейке теперь​ «Формулы»-«Создать из выделенного».​ опцию: «в строке​ решение данной задачи.​Синтаксис​.​На вкладке​ A1 пустого листа.​ автозавершения формул.​ Наша задача подсчитать​ Адрес ячейки ставим​В нашей таблице,​ сами, обведя границы​OK​ рекомендаций по созданию​​, то сможете пользоваться​​ все имена, созданные​​ вместо ошибки #ССЫЛКА!​​ В появившемся окне​​ выше», как на​​ Для сравнения рассмотрим​​Пробелы не допускаются. Знаки​​Выберите ячейку C10, введите​​Главная​​Создайте пустую книгу или​

  1. ​Вставьте имя из раскрывающегося​ сколько мы заработали​ смешанный. Столбец А​ мы присвоили имена​ ячеек, то, выделяем​

  2. ​.​ имен в Excel​ именем по всей​​ в данной рабочей​​ отображается правильный результирующий​ отмечаем вторую опцию​

    ​ рисунке. Это значит,​ оба варианта.​ подчеркивания и точки​

  3. ​= AVERAGE​в группе​​ лист.​ списка​​ за прошедший год.​

  4. ​ в формуле –​​ всей таблицы и​​ таблицу, присваиваем имя​​Excel присвоит ячейкам имена​

    ​Диспетчер имен в Excel​

  5. ​ книге Excel (на​

  6. ​ книге Excel. В​​ ответ.​​ сверху: «в столбце​​ что значения в​​Допустим, мы решаем данную​​ можно использовать в​ Кнопка ​(MyRegions)​

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

    ​ имена диапазонов столбцов.​​ как обычному диапазону.​

    • ​ на основе заголовков​Как присваивать имена константам​ всех листах), а​​ нашем случае это​​Примечание. Хотя списки​​ сверху». Таким образом,​​ верхних строках будут​​ задачу с помощью​​ качестве разделителей —​

    • ​и нажмите клавишу RETURN.​нажмите кнопку меню​ данных.​, который находится на​ наш заработок, необходимо​ а номер ячейки​ Теперь составляем формулу.​На закладке «Формулы»​ из указанного диапазона.​ в Excel?​ если конкретный лист​ всего лишь одно​ можно и не​ у нас создались​ использованы для названия​​ абсолютной ссылки на​​ например, Налог_на_продажи или​​Результат — 2661.5.​​Формат номера​​Примечание:​​ вкладке​​ просуммировать объемы продаж​​ относительный. Так можно​​ Мы составим простую​​ в разделе «Определенные​

Рекомендации по созданию имена

​Выделите ячейку и введите​

​Урок подготовлен для Вас​

​ – то только​

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

​ за весь год,​

​ формулу копировать по​ формулу, чтобы понять​ имена» нажимаем на​ формулу.​ командой сайта office-guru.ru​ в рамках данного​

​ только что создали.​

​ названия магазинов и​ имена. Чтобы убедиться​ нижних строках. Будет​ текущего курса. Тогда​Ячейка ссылку конфликты​

​На вкладке​

​денежных единиц​ или столбцов (1, 2, 3…  A,​.​ а затем полученный​ столбцу.​ принцип применения в​ кнопку «Присвоить имя».​Поместите курсор туда, где​Автор: Антон Андронов​ листа. Как правило​

​В качестве примера, создадим​

​ месяцев вручную. Списки​ в этом выберите​ создано одновременно сразу​ нам нужно сделать​Имена не могут быть​Главная​.​ B, C…) при​Итак, в данном уроке​ результат умножить на​Нажимаем «ОК». Ячейка получилась​ формулах имен.​

support.office.com

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

​ В строке «Имя»​ вы хотите использовать​Автор: Антон Андронов​ выбирают область видимости​ формулу, использующую имя​ нужны для удобства​ инструмент: «Диспетчер имен».​ 2 имени. Ячейка​ так:​ так же, как​в группе​Результат — $2,661.50, расположенный​

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

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

Услуги.

​Например, посчитаем сумму​ диалогового окна пишем​ в формуле имя.​Примечание:​ –​Продажи_по_месяцам​ ввода и исключения​Перейдите в ячейку C8​

​ F2 получит имя​Запишем курсы ЕВРО и​ ссылки на ячейки,​число​ среднее фактические продажи​ на пустой лист.​ такое имена ячеек​ будет выглядеть следующим​ написано? Правильно. Это​ столбца А. В​

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

  1. ​ «Евро», а ячейка​ ДОЛЛАРА в отельных​ например Z$ 100​нажмите кнопку​Цены в других валютах.
  2. ​ для Восточной Запад,​Выбор примеров данных в​ и диапазонов в​ образом:​ мы только присвоили​ ячейку А5 начинаем​ Например, мы назвали​ и выберите его​
  3. ​ оперативнее обеспечивать вас​.​

Услуги.

​ формула, подсчитывающая общую​ при ручном вводе​ СУММ со следующими​ G2 – «Доллар».​ ячейках F2 и​ или R1C1 BIN9.​валюты​ Юг, и Северной​ справке​

​ Excel. Если желаете​Такая формула будет вычислять​ имя формуле. Теперь​ писать формулу. Когда​ нашу таблицу «Товары».​ из появившегося списка.​ актуальными справочными материалами​В поле​ сумму продаж за​ значений. Результат будет​ аргументами: =СУММ(Магазин3 февраль)​Выделите диапазон C2:D5 и​ G2.​Максимальное количество символов​.​ областей.​Регион​ получить еще больше​ правильный результат, но​ нужно ввести это​ в скобках начинаем​ Пишем слово Товары​Или выберите​ на вашем языке.​Примечание​

​ прошедший год:​ тот же.​ и нажмите Enter.​ выберите инструмент из​

  1. ​В ячейки C2 и​Имя может содержать до​Результат — $2,661.50, расположенный​Советы:​Имена.
  2. ​Планируемая​ информации об именах,​ аргументы, используемые в​ имя формулы в​ вводить имя таблицы,​ (без кавычек). В​Формулы​ Эта страница переведена​Вы можете ввести​Как видите, если ячейке​В некоторой степени решение​Отлично! В результате мы​ выпадающего меню: «Формулы»-«Определенные​ D2 введем формулы,​ 255 символов. Если​ среднее фактические продажи​
  3. ​ ​Фактические​ читайте следующие статьи:​ ней, не совсем​Применить имена.
  4. ​ ячейку. В ячейке​ выходит список. Выбираем​ строке «Область» выбираем​>​ автоматически, поэтому ее​

​ пояснение к создаваемому​ или диапазону, на​ данной задачи можно​ видим значение 500​ имена»-«Присвоить имя»-Применить имена».​ которые ссылаются к​ имя, определенное для​ для Восточной Запад,​Для просмотра и управления​

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

​ имени. В ряде​

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

​ которые ссылается формула,​ выполнить и без​ ¬– прибыль магазина3​

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

Отчет за 1 квартал.

​ имена, которые будут​5000 долл. США​ или диапазону в​ стала более понятной,​ =НДС Всё.)) Копируем​ (нажимаем 2 раза​ «Диапазон» пишем диапазон​и выберите имя,​ неточности и грамматические​ случаев это делать​ дать осмысленные имена,​ имен используя плохо-читаемые​ за февраль месяц.​

  1. ​ сразу 2 имени,​ через относительную ссылку,​ 253 знаков, вам​ областей.​ назначены, в меню​$5,325​ Excel?​Создать из выделенного.
  2. ​ необходимо назначить областям,​ формулу по столбцу.​ левой мышкой).​ нашей таблицы. Сначала​ которое хотите использовать.​ ошибки. Для нас​ рекомендуется, особенного, когда​ то формула станет​ абсолютные адреса ссылок​ Теперь нам только​ а остальное оставьте​ а к другим​Диспетчер имен.
  3. ​ не удается выбрать​Советы:​Вставка​Западный​5 полезных правил и​

500.

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

  1. ​ валютам через абсолютную​ из поле имени,​ ​наведите указатель мыши​1 500 ​ рекомендаций по созданию​ имена. Например, назначим​А если нужно накрутить​ квадратную скобку. Появился​ Напишем так. =A1:В16​
  2. ​На ленте выберите​ статья была вам​ много или, когда​Чтобы присвоить имя ячейке​ Но вот такой​ функции сделать обработчик​ и нажмите ОК.​ ссылку.​ но вы по-прежнему​Для просмотра и управления​имя​Месяцы и магазины.
  3. ​$1,477​ имен в Excel​ диапазону B2:В13 имя​ НДС на сумму​ список диапазонов. Выбираем​ Если листов у​формулы > определенные имена​ полезна. Просим вас​ Вы ведете совместный​ или диапазону этим​ обработчик запросов без​ запросов, который так​Это только примитивный пример​Выпадающие списки.
  4. ​Скопируем диапазон ячеек C2:D2​ можно использовать в​ имена, которые будут​и выберите команду​

Пример.

​Юг​Диспетчер имен в Excel​Продажи_по_месяцам​ из столбца А,​ – «Столбец1».​ книги много, то​>​ уделить пару секунд​ проект с другими​

​ способом, проделайте следующие​ использования имен сделать​ же будет использовать​ преимущества использования имен​ в C3:D5.​ формулах.​ назначены, в меню​определить​2500 долл. США​Как присваивать имена константам​, а ячейке В4​

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

exceltable.com

​Данное решение вполне рабочее​

Источник

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

  1. Выделите ячейку.

  2. Введите имя в поле Имя.

    Поле "Имя"

  3. Нажмите клавишу ВВОД.

Чтобы ссылаться на это значение в другой таблице, введите знак равенства (=) и Имя, а затем нажмите клавишу ВВОД. 

Присвоение имен из выделенного диапазона

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

  2. На вкладке Формулы нажмите кнопку Создать из выделенного

  3. В диалоговом окне Создание имен из выделенного диапазона укажите местоположение, содержащее метки, установив флажок в строке выше, в столбце слева, в строке ниже или в столбце справа.

  4. Нажмите кнопку ОК.

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

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

  1. Выделите ячейку и введите формулу.

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

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

    Или выберите Формулы > Использовать в формуле и выберите имя, которое хотите использовать.

  4. Нажмите клавишу Ввод.

Управление именами в книге с помощью Диспетчера имен

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

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

  1. Выделите ячейку.

  2. Введите имя в поле Имя.

    Поле "Имя"

  3. Нажмите клавишу ВВОД.

Присвоение имен из выделенного диапазона

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

  2. На вкладке Формулы нажмите кнопку Создать из выделенного

  3. В диалоговом окне Создание имен из выделенного диапазона укажите место, содержащее заголовки, установив флажок в строке вышев столбце слева, в строке ниже или в столбце справа.

  4. Выберите OK.

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

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

  1. Выделите ячейку и введите формулу.

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

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

    Или выберите Формулы > Использовать в формуле и выберите имя, которое хотите использовать.

  4. Нажмите клавишу Ввод.

Управление именами в книге с помощью Диспетчера имен

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

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

На данный момент создание нового именованного диапазона в Excel в Интернете недоступно.

Источник

Перейти к содержанию

На чтение 3 мин Опубликовано 18.01.2020

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

Используем поле Имя

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

  1. Выделите ячейку или диапазон, которым необходимо присвоить имя. В нашем случае это диапазон B2:B13.Присвоить имя в Excel
  2. Щелкните по полю Имя и введите необходимое имя, соблюдая правила, рассмотренные здесь. Пусть это будет имя Продажи_по_месяцам.Присвоить имя в Excel
  3. Нажмите клавишу Enter, и имя будет создано.
  4. Если нажать на раскрывающийся список поля Имя, Вы сможете увидеть все имена, созданные в данной рабочей книге Excel. В нашем случае это всего лишь одно имя, которое мы только что создали.Присвоить имя в Excel
  5. В качестве примера, создадим формулу, использующую имя Продажи_по_месяцам. Пусть это будет формула, подсчитывающая общую сумму продаж за прошедший год:Присвоить имя в Excel
  6. Как видите, если ячейке или диапазону, на которые ссылается формула, дать осмысленные имена, то формула станет гораздо понятнее.

Используем диалоговое окно Создание имени

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

  1. Выделите требуемую область (на данном этапе можно выделить любую область, в дальнейшем вы сможете ее перезадать). Мы выделим ячейку С3, а затем ее перезададим.Присвоить имя в Excel
  2. Перейдите на вкладку Формулы и выберите команду Присвоить имя.Присвоить имя в Excel
  3. Откроется диалоговое окно Создание имени.Присвоить имя в Excel
  4. В поле Имя введите требуемое имя. В нашем случае это имя Коэффициент. В ряде случаев Excel автоматически подставляет имя на основе данных в соседних ячейках. В нашем случае так и произошло. Если Excel этого не сделал или такое имя Вас не устраивает, введите требуемое Вам имя самостоятельно.
  5. В раскрывающемся списке Область Вы можете указать область видимости создаваемого имени. Область видимости – это область, где вы сможете использовать созданное имя. Если вы укажете Книга, то сможете пользоваться именем по всей книге Excel (на всех листах), а если конкретный лист – то только в рамках данного листа. Как правило выбирают область видимости – Книга.
  6. В поле Примечание Вы можете ввести пояснение к создаваемому имени. В ряде случаев это делать рекомендуется, особенного, когда имен становится слишком много или, когда Вы ведете совместный проект с другими людьми.Присвоить имя в Excel
  7. В поле Диапазон отображается адрес активной области, т.е. адрес ячейки или диапазона, которые мы выбрали ранее. При необходимости данный диапазон можно перезадать. Для этого поместите курсор в поле Диапазон, вокруг указанной области появится динамическая граница.Присвоить имя в ExcelМышкой выделите новую область или укажите эту область, введя диапазон прямо в текстовое поле. В нашем случае мы выберем ячейку D2.Присвоить имя в Excel
  8. Если Вас все устраивает, смело жмите ОК. Имя будет создано.Присвоить имя в Excel

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

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

  • Знакомство с именами ячеек и диапазонов в Excel
  • 5 полезных правил и рекомендаций по созданию имен в Excel
  • Диспетчер имен в Excel
  • Как присваивать имена константам в Excel?

Оцените качество статьи. Нам важно ваше мнение:

Источник

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

Фамилия, имя и отчество в разных ячейках

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

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

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

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

Здесь можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";B1;" ";C1), или просто соединить строки с помощью & (амперсандов) =A1&" "&B1&" "&C1, не забыв добавить между словами пробелы.

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

Объединение фамилии и инициалов из разных ячеек в одну

Здесь также можно использовать формулу «СЦЕПИТЬ», как в примере =СЦЕПИТЬ(A1;" ";ЛЕВСИМВ(B1;1);".";ЛЕВСИМВ(C1;1);".") или & (амперсанды) =A1&" "&ЛЕВСИМВ(B1;1)&"."&ЛЕВСИМВ(C1;1)&".", не забыв добавить между фамилией и инициалами пробел, а к инициалам точки. В этом примере мы извлекаем левые символы из имени и отчества для присоединения их к фамилии в виде инициалов.

Фамилия, имя и отчество в одной ячейке

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

  • уменьшается количество колонок в таблице;
  • в большинство документов* требуется внесение фамилии, имени и отчества в полном написании.

*Если документы генерируются на основе этой таблицы, то вставка ФИО не потребует дополнительных преобразований.

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

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

  • ячейка B1 — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1)-1)
  • ячейка C1 — =ПСТР(A1;НАЙТИ(" ";A1;1)+1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)-НАЙТИ(" ";A1;1)-1)
  • ячейка D1 — =ПРАВСИМВ(A1;ДЛСТР(A1)-НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1))

Чтобы определить начало имени и начало отчества используются позиции первого и второго пробелов, найденных с помощью функции «НАЙТИ».

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

Для этого преобразования используется следующая формула в ячейке B1 — =СЦЕПИТЬ(ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1));" ";ПСТР(A1;НАЙТИ(" ";A1;1)+1;1);".";ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1);".")

Здесь тоже функцию «СЦЕПИТЬ» можно заменить & (амперсандами) — =ЛЕВСИМВ(A1;НАЙТИ(" ";A1;1))&" "&ПСТР(A1;НАЙТИ(" ";A1;1)+1;1)&"."&ПСТР(A1;НАЙТИ(" ";A1;НАЙТИ(" ";A1;1)+1)+1;1)&"."

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

Источник

Как создать имя для ячейки или диапазона ячеек, используя поле имени

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

02_selecting_cell_range

Нажмите кнопку мыши в поле «Имя» над сеткой ячеек.

03_name_box

Введите имя диапазона ячеек в поле и нажмите «Ввод». Например, мы назвали выбранные ячейки в нашей таблице «База данных продуктов» «Продукты». Существуют правила синтаксиса, которые вы должны соблюдать при выборе имени. Имя можно начинать только с буквы, подчеркивания (_) или обратной косой черты (). Остальная часть имени может состоять из букв, цифр, точек и подчеркиваний. Существуют дополнительные правила синтаксиса о том, что является действительным, а не при определении имен.

04_entering_name_in_name_box

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

  = ЕСЛИ (ISBLANK (A11), "", VLOOKUP (ALL, Products , 2, FALSE))

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

16_using_name_in_formula

Как редактировать имена с помощью диспетчера имен

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

05_clicking_formulas_tab

В разделе «Определенные имена» вкладки «Формулы» нажмите «Диспетчер имен».

06_clicking_name_manager

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

07_clicking_edit_on_name_manager

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

ПРИМЕЧАНИЕ. Вы увидите, что раскрывающийся список «Область» недоступен. Когда вы редактируете существующее имя, вы не можете изменить «Scope» этого имени. Вы должны выбрать область при первом создании имени. Если вы хотите, чтобы областью действия была конкретная рабочая таблица, а не вся рабочая книга, вы можете создать имя таким способом, который позволит вам изначально указать область. Мы покажем вам, как это сделать, в следующем разделе.

08_edit_name_dialog

Например, скажем, мы добавили еще один продукт в нашу «Базу данных продуктов» и хотим включить его в диапазон ячеек под названием «Продукты». Когда мы нажимаем кнопку «Развернуть диалог», диалоговое окно «Редактировать имя» сжимается и содержит только поле редактирования «Относится к». Мы выбираем диапазон ячеек непосредственно на листе «База данных продуктов», включая строку, содержащую добавленный продукт. Имя листа и диапазон ячеек автоматически вводятся в поле «Относится к». Чтобы подтвердить свой выбор и вернуться в полное диалоговое окно «Редактировать имя», нажмите кнопку «Свернуть диалоговое окно». Нажмите «ОК» в диалоговом окне «Изменить имя», чтобы принять изменения в имени.

08a_selecting_new_cell_range

Как удалить имя с помощью диспетчера имен

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

09_deleting_name

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

10_delete_confirmation

Как создать имя с помощью диалогового окна «Новое имя»

Когда вы создаете новое имя, выбирая одну или несколько ячеек и затем вводя имя в поле «Имя», область имени по умолчанию — вся книга. Итак, что вы будете делать, если вы хотите ограничить область имени только конкретной рабочей таблицей?

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

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

12_clicking_define_name

Откроется диалоговое окно «Новое имя». Обратите внимание, что это очень похоже на диалоговое окно «Редактировать имя», упомянутое ранее. Основное отличие заключается в том, что теперь вы можете изменить область названия. Допустим, мы хотим ограничить область действия имени только листом «Счет-фактура». Мы сделали бы это, если бы хотели иметь возможность использовать одно и то же имя для диапазона ячеек на другом листе.

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

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

13_selecting_scope

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

14_clicking_ok_on_new_name_dialog

Имя автоматически вставляется в то же «поле имени», которое мы использовали для присвоения имени диапазону ячеек в начале этой статьи. Теперь мы можем заменить ссылку на диапазон ячеек («База данных продуктов»! $ A $ 2: $ D: 7) на имя (Продукты) в формулах на листе «Счет-фактура», как мы делали ранее в этой статье.

15_name_inserted_into_name_box

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

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

17_exchange_rate_on_worksheet

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

18_formula_with_cell_reference

Чтобы создать имя, которое будет присвоено постоянному значению, откройте диалоговое окно «Новое имя», щелкнув вкладку «Формулы», а затем нажмите «Определить имя» в разделе «Определенные имена». Введите имя для представления значения константы, например «ExchangeRate». Чтобы присвоить значение этому имени, введите знак равенства (=) в поле редактирования «Относится к», а затем значение. Между знаком равенства и значением не должно быть пробела. Нажмите «ОК», чтобы завершить создание имени.

ПРИМЕЧАНИЕ. Если в вашей книге есть формула, которую вы используете во многих местах, вы можете ввести эту формулу в поле «Относится к», чтобы вы могли просто ввести имя в каждую ячейку, где вам нужно использовать формулу.

19_new_name_dialog_for_constant

СВЯЗАННЫЕ: Относительная и абсолютная ссылка на ячейку и форматирование

Теперь мы можем использовать новое имя в формулах, где мы хотим использовать обменный курс. Когда мы нажимаем на ячейку с формулой, которая содержит абсолютную ссылку на ячейку , обратите внимание, что результат равен «0,00». Это потому, что мы удалили обменный курс из указанной ячейки. Мы заменим эту ссылку на ячейку новым именем, которое мы создали.

20_cell_reference_results

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

21_selecting_name

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

22_name_inserted

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

23_formula_using_constant_name

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

Источник

Понравилась статья? Поделить с друзьями:

А вот еще интересные статьи:

  • Excel имя ячейки r1c1
  • Excel имя файла в формуле
  • Excel имя файла в макросе
  • Excel имя уже существует чтобы использовать эту версию имени нажмите да как избавиться
  • Excel имя уже существует как убрать

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии