Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Именованные диапазоны в Excel — несколько трюков использования
Именованные диапазоны, вероятно, один из самых полезных инструментов в Excel. Именованные диапазоны добавляют интерактивность в книгу, делают длинные формулы короткими и, при правильном использовании, обеспечивают механизм обмена информации по всей книге. Учитывая, какую пользу несут именованные диапазоны в Excel, решил уделить немного внимания им на этом блоге.
Итак, несколько советов, которые сделают вашу работу с именованными диапазонами в Excel более быстрой и продуктивной.
Многоразовое создание именованного диапазона в один прием
Обычно, при создании именованного диапазона из заданного набора данных, необходимо написать (или выбрать) адрес именованного диапазона и дать ему имя. Тем не менее, во многих случаях, когда у вас уже имеются заголовки для данных на листе, существует более простой вариант создания.
Когда вы щелкните ОК, Excel создаст четыре именованных диапазона. Заголовок каждого диапазона будет служить его названием. При необходимости вы можете легко отредактировать любой атрибут диапазонов.
Доступ к управлению именованными диапазонами
Чтобы открыть диалоговое окно Диспетчер имен, перейдите по вкладке Формулы в группу Определенные имена и щелкните по кнопке Диспетчер имен. Либо нажатием сочетаний клавиш Ctrl + F3.
Использование формулы СМЕЩ
Именованные диапазоны и вполовину не были бы такими полезными и интересными без формулы СМЕЩ. Функция СМЕЩ помогает позиционировать и расширять данный диапазон. Результатом использования ее может стать мощный динамический диапазон, который имеет способность расширяться и изменяться.
Использование абсолютных ссылок при работе с именованными диапазонами
На самом деле не уверен, это конструктивная особенность или ошибка. Используя относительные ссылки (A1 вместо $A$1) при определении именованного диапазона, они не остаются на том же месте, как бы вы этого не хотели. Давайте рассмотрим этот случай на примере. Предположим, вы хотите создать диапазон, который смещается вниз на 10 строк от ячейки A1. Первое, что приходит в голову, это написать формулу =СМЕЩ(A1;10;0).
Пока все хорошо. Если вы захотите воспользоваться этим именованным диапазоном, необходимо подобрать для нее ячейку (скажем B1) и ввести что-то типа =мой_имен_диап. Где мой_имен_диап — это имя, которое вы дали диапазону на предыдущем шаге.
Использование F2 для изменения именованного диапазона
Еще одна полезная вещь, использование F2 при изменении именованного диапазона. Попробуйте воспользоваться кнопками стрелок на клавиатуре для навигации по формуле именованного диапазона, вы увидите замечательные преобразования.
Чтобы избежать недоразумений при использовании стрелок, нажмите клавишу F2.
Возможно у вас имеются свои трюки по использованию именованных диапазонов?! Не хотите поделиться?)
Вам также могут быть интересны следующие статьи
2 комментария
Подскажите, а использование именованных диапазонов влияет на размер файла? Если использовать имена это влияет на производительность формул? Спасибо.
Здравствуйте, Ренат! Очень интересная диаграмма, даже при том, что и не классический тримап. Скажите, а можно построить подобную диаграмму так, чтобы её составляющие были положительные и отрицательные. Чтобы их размер зависел, насколько далеко их значение от 0, а располагались они справа и слева от оси, в зависимости от знака? Была бы очень интересная диаграмма весов.
[expert_bq id=»1570″]Как показано на скриншоте, формула успешно отсекает слово продукты 8 букв, разделитель и 2 пробела из текстовых значений в столбце A. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Если ваша формула возвращает ошибку #ЗНАЧ!, то первое, что вам нужно проверить, — это значение аргумента количество_знаков. Если вы видите отрицательное число, просто удалите знак минус, и ошибка исчезнет (конечно, очень маловероятно, что кто-то намеренно поставит отрицательное число, но человек может ошибиться
Функция ЛЕВСИМВ в EXCEL с примерами формул и пояснениями.
=АДРЕС(1;1) – возвращает $A200.
=АДРЕС(1;1;4) – возвращает A1.
=АДРЕС(1;1;4;ЛОЖЬ) – результат R[1]C[1].
=АДРЕС(1;1;4;ЛОЖЬ;»Лист1″) – результат выполнения функции Лист1!R[1]C[1].
Именованные диапазоны в Excel
Когда же имя еще не присвоено диапазону ячеек, то в соответствующем окне будет отображаться первая вверху ячейка данного массива данных. Что хорошо видно на рисунке.
Что бы Excel зафиксировал присвоенное имя, осталось нажать на Enter. Все имя легло в реестр программы и excel готов оперировать им.
Остается только ввести придуманное имя и проверить точность выделенного диапазона в ячейке «диапазон». Если все верно, заканчиваем операцию по присвоению имени диапазону и нажимаем «ОК».
Выделяем мышкой наш диапазон и в окне имен видим присвоенное название. То есть, все сделано правильно.
Еще одни вариант присвоения имени диапазону ячеек это использование команд расположенных на ленте.
Вначале выделяем нужные ячейки, затем идем по вкладке формулы. Открываем «Определенные имена» и активируем команду «Присвоить имя»
Как видим на рисунке, откроется уже знакомое нам окно, в котором проводим те же действия что и во втором варианте.
И заканчивается наш список вариантов присвоения имен диапазонам на способе использующим функцию «Диспетчер имен» .
Уже ясно что необходимо выделить ячейки, потом задействовать инструмент под названием «Формулы»,
и активировать «Диспетчер имен».
Как и показано на рисунке ниже, в открывшемся окне в графе создать, при нажатии на одноименную кнопку выскочит окошко, в котором необходимо провести уже знакомые действия.
Присвоенное название диапазону, после всех проведенных необходимых манипуляций, вы увидите в самом диспетчере.
Жмем на крестик, выделенный на рисунке и закрываем окно диспетчера.
На этом лимит данной статьи исчерпан. Надеемся, что данная информация поможет вам разобраться в вопросах,что такое именной диапазон в Excel и как его устанавливать.
О том какие операции, возможно, проводить с именными диапазонами будет рассказано в следующей статье.
Именованные диапазоны в Excel » Компьютерная помощь
- Текст (обязательно) — это текст, из которого вы хотите извлечь подстроку. Обычно предоставляется как ссылка на ячейку, в которой он записан.
- Второй аргумент (необязательно) — количество знаков для извлечения, начиная слева.
- Если параметр опущен, то по умолчанию подразумевается 1, то есть возвращается 1 знак.
- Если введенное значение больше общей длины ячейки, формула вернет всё ее содержимое.
Как видно в примере выше, у нас есть число 12522, которое представлено в виде текста, при помощи функции ЗНАЧЕН мы преобразовали его в число 12 522, с которым в дальнейшем можем работать, как с любыми другими числами.
[expert_bq id=»1570″]Поэтому, перед применением функции с данным интервальным просмотром, предварительно отсортируйте первый столбец таблицы по возрастанию, так как, если это не сделать, функция может вернуть неправильный результат. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Он подобного эффекта можно избавиться путем определения категории из наименования товара используя текстовые функции ЛЕВСИМВ(C11;ПОИСК(» «;C11)-1), которые вернут все символы до первого пробела, а также изменить интервальный просмотр на точный.Как найти диапазон в Excel (простые формулы)
Пример 1. Есть две одинаковые (на первый взгляд) таблицы данных, которые содержат наименования продукции. Одну из них предположительно редактировал уволенный работник. Необходимо быстро сравнить имеющиеся данные и выявить несоответствия.
