Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

Именованные диапазоны в Excel — несколько трюков использования

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

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

Многоразовое создание именованного диапазона в один прием

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

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

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

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

Использование формулы СМЕЩ

Именованные диапазоны и вполовину не были бы такими полезными и интересными без формулы СМЕЩ. Функция СМЕЩ помогает позиционировать и расширять данный диапазон. Результатом использования ее может стать мощный динамический диапазон, который имеет способность расширяться и изменяться.

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

На самом деле не уверен, это конструктивная особенность или ошибка. Используя относительные ссылки (A1 вместо $A$1) при определении именованного диапазона, они не остаются на том же месте, как бы вы этого не хотели. Давайте рассмотрим этот случай на примере. Предположим, вы хотите создать диапазон, который смещается вниз на 10 строк от ячейки A1. Первое, что приходит в голову, это написать формулу =СМЕЩ(A1;10;0).

Пока все хорошо. Если вы захотите воспользоваться этим именованным диапазоном, необходимо подобрать для нее ячейку (скажем B1) и ввести что-то типа =мой_имен_диап. Где мой_имен_диап — это имя, которое вы дали диапазону на предыдущем шаге.

Использование F2 для изменения именованного диапазона

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

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

Возможно у вас имеются свои трюки по использованию именованных диапазонов?! Не хотите поделиться?)

Вам также могут быть интересны следующие статьи

2 комментария

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

Здравствуйте, Ренат! Очень интересная диаграмма, даже при том, что и не классический тримап. Скажите, а можно построить подобную диаграмму так, чтобы её составляющие были положительные и отрицательные. Чтобы их размер зависел, насколько далеко их значение от 0, а располагались они справа и слева от оси, в зависимости от знака? Была бы очень интересная диаграмма весов.

P.S. У меня работало с автофильтром по одному столбцу, с несколькими столбцами не работает — ошибка «Данная команда не работает для несвязных диапазонов» или не вставляет в неотображаемые (не попавшие в фильтр) ячейки.

Удаление (скрытие) строк по условию | Макросы Excel — Страница 2

СОВЕТ: Если выделить ячейку, содержащую формулу с именем диапазона, и нажать клавишу F2 , то соответствующие ячейки будут обведены синей рамкой (визуальное отображение Именованного диапазона ).

Excel. Выделение, копирование, вставка

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

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

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

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

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

Это необходимо учитывать при выборе первой ячейки для выделения.

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

Выделение мышью удобно использовать для небольших диапазонов.

Для выделения диапазонов ячеек с помощью клавиатуры, используются клавиши “SHIFT”, “CTRL”, а также клавиши стрелок в различных комбинациях.

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

– Для выделения ячейки, соседней с активной, необходимо, удерживая нажитой клавишу “SHIFT”, нажать клавишу стрелки соответствующего направления, при этом будет выделен диапазон из соседних ячеек по горизонтали или по вертикали (рис. 15 а, б). При нажатии на стрелку противоположного направления, выделение с крайних ячеек будет поступательно убираться;

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

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

При использовании только клавиатуры или только мыши возможно выделить только прямоугольный (квадратный) диапазон ячеек – связанный диапазон, т.е. диапазон, в котором все ячейки “связаны” (примыкают друг к другу).

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

в) Не отпуская клавишу “CTRL”, мышью выделить остальные ячейки.

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

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

Как в excel скопировать несвязанные диапазоны — Компьютерный справочник

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