Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Diapazon yacheek 1 Как присвоить диапазону ячеек имя в формулах Excel Доброго времени суток, уважаемый читатель!

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

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

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

  • 1 способ:простой и очень доступен, вы просто выделяете нужный вам диапазон и вводите его имя в поле «Имя», которое размещено с панели управления.Diapazon yacheek 2 Как присвоить диапазону ячеек имя в формулах Excel
  • 2 способ:с помощью меню, на вкладке «Формулы» (Formulas) вы выбираете команду «Присвоить имя» (Define Name). Вводите в поле «Имя», название диапазона и в поле «Диапазон» указываете диапазон, для которого вы присваиваете имя. При необходимости прописываете примечание, если есть необходимость объяснить ваши действия.Diapazon yacheek 3 Как присвоить диапазону ячеек имя в формулах Excel

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

Кроме того, именование константы довольно простое:

Diapazon yacheek 4 Как присвоить диапазону ячеек имя в формулах Excel

  1. Проходим, по указанном, выше пути «Формула» — «Присвоить имя»;
  2. В появившемся окне, как и раньше в поле «Имя» (Name) вводите имя константы, а вот туда где раньше вы вводили диапазон для имени, вводите значение вашей константы;
  3. Всё, теперь в формулах вы можете использовать имя вашей константы для расчётов в формулах. Вы можете изменить значение константы в любое время, но должны помнить, что Excel автоматически пересчитает все значения в формулах, где вы используете константу.

Чтобы изменить или удалить присвоенное имя, необходимо использовать «Менеджер имен»:

Diapazon yacheek 5 Как присвоить диапазону ячеек имя в формулах Excel

  1. На вкладке «Формулы» необходимо выбрать пункт меню «Менеджер имен»;
  2. В появившемся окне установите курсор на нужное имя и выберите нужное действие «Изменить» или «Удалить».

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

Я буду рада вашим лайкам и комментариям!

Читайте так же:
Как скопировать лист в конец книги в Excel?

До встречи.

«Богатство очень хорошо, когда оно служит нам, и очень плохо – когда повелевает нами.
»
Ф. Бэкон

Функция СМЕЩ в Excel

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

Описание функции СМЕЩ

СМЕЩ(ссылка; смещ_по_строкам; смещ_по_столбцам; [высота]; [ширина])
Возвращает ссылку на диапазон, смещенный относительно заданной ссылки на указанное количество строк и столбцов.

  • Ссылка(обязательный аргумент) — ссылка на ячейку или диапазон смежных ячеек;
  • Смещение по строкам(обязательный аргумент) и по столбцам(обязательный аргумент) — число строк и столбцов, на которое результирующий диапазон смещен относительно исходной ссылки; Например, аргументы 4; 3 сместят ссылку на 4 строчки вниз и 3 столбца вправо. При этом оба параметра могут принимать различные значения — положительные (смещение вниз по строкам/вправо по столбцам), нулевые или отрицательные (смещение вверх по строкам/влево по столбцам).
  • Высота(необязательный аргумент) и ширина(необязательный аргумент) — высота (в строках) и ширина (в столбцах) возвращаемого диапазона, по умолчанию высота и ширина совпадают с размерами исходной ссылки; Например, аргументы 5; 2 расширят ссылку до диапазона в 5 ячеек высотой и 2 ячейки шириной.

Давайте рассмотрим несколько примеров для понимания того, как работает функция HALV:

Принцип работы функции СМЕЩ

К примеру, формула =СМЕЩ(A1;0;0;5;4) (на рисунке выделена красным цветом) сдвигает ячейку A1 (аргумент функции №1) на (№2) вниз, на (№3) вправо, получаем диапазон A1 (состоящий из одной ячейки), а затем расширяет его до размера 5 (№4) на 4 (№5), т.е. возвращаемая ссылка принимает вид A1:D5 (на рисунке область также выделена красным цветом).
Аналогично, формула =СМЕЩ(A1;1;2;8;3) (выделена синим цветом) сдвигает ячейку A1 на 1 вниз, на 2 вправо, получаем диапазон C2 и расширяет его до размера 8 на 3, т.е. в результате получаем ссылку C2:E9.

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

Читайте так же:
Как сделать выпадающий список, позволяющий вводить произвольный текст (ввод вручную) в Excel?

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

Пример использования функции СМЕЩ

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

Пример 1. Функция ПОИСКПОЗ

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

Таблица с данными
Воспользуемся функцией ПОИСКПОЗ для поиска указанной даты (ячейка D2) в диапазоне с датами (A2:A10).
После чего сместим начальную ячейку (в данном случае B2) на рассчитанную величину вниз за вычетом единицы.
Мы дополнительно вычитаем единицу так как показываем именно смещение относительно начальной ячейки, например, чтобы перейти с первой строки на шестую мы смещаемся ровно на пять строк.
В итоге получаем следующий результат:

Пример формул СМЕЩ и ПОИСКПОЗ

Идентичного результата можно добиться и с помощью функции ИНДЕКС — формула =ИНДЕКС(B2:B10;ПОИСКПОЗ(D2;A2:A10;0)) вернет точно такой же результат.

Пример 2. Функция СУММ

Возьмем начальные условия как в предыдущем примере, однако теперь мы посчитаем сумму продаж за последние 7 дней.
Можно воспользоваться стандартной формулой СУММ(B4:B10), но при добавлении новых строчек расчет становится неверным и нам придется каждый раз изменять формулу, поэтому мы пойдем по другому пути.
С помощью функции СЧЁТЗ находим последнюю введенную дату (указываем достаточно большой диапазон A2:A100, чтобы была возможность добавлять новые данные).
Из полученного результата вычитаем 7, чтобы найти первую дату искомого диапазона, поэтому производя сдвиг начальной ячейки (B2) на найденную величину и расширяя диапазон до размеров 7 на 1, мы получим данные за 7 последних дней.
Просуммируем их воспользовавшись функцией СУММ:

Пример формул СМЕЩ и СУММ

При добавлении в таблицу новых данных результат автоматически пересчитывается:

Пример формул СМЕЩ и СУММ

Особенности применения

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

Читайте так же:
Как сделать, чтобы в группе флажков в Excel был выбран только один флажок?

Диапазон Excel. Выделение, сравнение, изменение диапазонов значений

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

Что такое диапазон ячеек в Excel?

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

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

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

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

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

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

Для имен действует ряд ограничений:

— Это название может содержать до 255 символов;

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

Читайте так же:
Как скрыть ранее использованные элементы в выпадающем списке?

— Имена не могут совпадать с ссылками на ячейки;

— Пробелы в именах не допускаются;

— Строчные и прописные буквы не различаются.

В диспетчере имен можно просматривать и редактировать существующие именованные диапазоны (созданные, просмотренные, измененные). Диспетчер можно найти в Excel 2007 на вкладке Формулы, группа кнопок Определенные имена.

Основные действия с диапазонами

Выделение диапазонов

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

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

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

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

Сравнение диапазонов

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

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

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

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

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

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

Изменение (преобразование) диапазонов значений

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

Транспонирование можно выполнить с помощью функции «=TRANSP(Range)», которую можно найти в категории «Ссылки и таблицы». Существует и другой способ копирования диапазона значений, после которого следует специальная вставка, при которой в поле «Транспозиция» ставится галочка.

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

Надстройка для работы с диапазонами в Excel

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector