Формула для динамического выделения диапазона ячеек в Excel
Автоматическое определение диапазона «от-до» в исходной таблице моно применять для автоматизации многих задач связанных с динамической выборкой значений. Рассмотрим один из простых для понимания способов реализации данной задачи.
Как автоматически выделять диапазоны для выборки ячеек из таблицы?
Задание является следующим. В одном из столбцов в разных ячейках находятся какие-то значения (в данном случае текстовые строки «граница»). Они определяют начало и конец секторов (диапазонов). Эти значения вставлены автоматически и могут появляться в разных ячейках. Их размеры и количество в них ячеек также может быть разным. Например, на рисунке ниже выбран сектор данных (диапазон) номер 2.
Динамическое определение границ выборки ячеек
Для наглядности приведем решение этой задачи с использованием вспомогательного столбца. В первую ячейку в вспомогательном столбце (A7) вводим формулу:
и копируем ее вниз в оставшиеся ячейки. Везде, где в ячейках соседнего столбца находится значение «граница», функция возвращает номер строки. В противном случае возвращает пустую строку.
Следующим шагом будет динамическое определение адреса для выборки диапазона данных из исходного списка в соответствии с критерием отбора.
Изменяемое значение критерия для управления выборкой данных из таблицы будет указано в ячейке C1. Там мы указываем порядковый номер диапазона, данные которого нас интересуют в конкретный момент.
Как получить адрес диапазона ячеек в Excel?
После чего динамически определим адрес начальной ячейки, с которой будет начинаться диапазон. В C2 вводим следующую формулу:
Аналогичным образом динамически определяем адрес последней ячейки, где должна заканчивается выборка. Для этого в C3 водим формулу:
Это практически все. Дальше используйте свое воображение для применения этой полезной функции автоматической выборки диапазонов данных из таблицы по условию пользователя. Например, воспользуемся условным форматированием.
Автоматическая подсветка цветом диапазонов ячеек по условию
Будем подсвечивать цвет диапазона, который соответствует порядковому номеру указанном в критериях выборки C1.
Теперь мы изменим критерий выборки, например, на 1. Автоматически подсветился зеленым цветом весь первый диапазон. Обратите внимание в нем на одну ячейку больше чем во втором, но все работает безошибочно.
Проверка вводимых значений в Excel на ошибки
Наконец, вы можете предупредить ошибку в случае ввода неверных (не соответствующего формата) значений (числа меньшего или равного нулю, большего, чем общее количество диапазонов) в качестве номера диапазона, который вы ищете. Все это можете сделать при помощи проверки данных. Перейдите на ячейку для ввода критериев выборки C1 и выберите инструмент: «ДАННЫЕ»-«Проверка данных».
Соответствующая формула «обеспечивающая безопасность» могла бы выглядеть так:
0;$D$7 
Нажмите ОК, после внесения всех изменений как показано выше на рисунке.
Теперь при попытке ввода в критерий выборки числа больше чем количество диапазонов в пределах «границ», будет предупреждение для пользователя:
Такая выборка может использоваться в автоматизации других различного рода интересных задач. Здесь же приведен только базовый пример возможностей динамической выборки данных из исходной таблицы.
Excel. Диапазон ячеек в формуле
Безусловно, понятие диапазона в Excel – одно из ключевых. Что это такое? Все мы знаем, что лист состоит из ячеек. Вот если несколько из них содержат какую-то информацию, то это диапазон. Простыми словами, это две и более ячейки в документе.
Диапазоны активно применяются в формулах, а также могут использоваться как источник данных для графиков, диаграмм и других визуальных способов отображения информации. Давайте более подробно рассмотрим способы работы с диапазоном.
Как выбирать ячейки, строки и столбцы
Ячейка – это элемент, содержащий определенную информацию или который может ее содержать. Строка – это ячейки в ряд. Столбец, соответственно, в колонку. Все просто.
Перед тем, как осуществлять ввод данных или выполнять определенные данные с диапазоном, нужно научиться выбирать ячейки, столбцы и строки.
Чтобы выбрать ячейку, нужно нажать по ней. Каждая ячейка имеет адрес. Например, расположенная на пересечении колонки C и ряда 3 называется C3.
Соответственно, чтобы выбрать колонку, необходимо нажать на букву, отображающую название столбца. В нашем случае это колонка C.
2
Как можно догадаться, чтобы выбрать строчку, нужно сделать то же самое, только с названием ряда.
3
Диапазон ячеек: пример
Теперь рассмотрим некоторые операции, которые можно осуществлять непосредственно с диапазоном. Так, чтобы выбрать диапазон B2:C4, необходимо найти правый угол ячейки B2, которая в нашем случае служит верхней левой ячейкой и протянуть курсор до C4.
Важно! Не квадратик в правом нижнем углу, а просто как бы потянуть за эту ячейку. Квадратик – это маркер автозаполнения, это немного другое.
Диапазон не всегда состоит из ячеек, находящихся в непосредственной близости друг к другу. Чтобы выделить его, нужно нажать клавишу Ctrl и, не отпуская ее, делать клик по каждой ячейке, которая должна входить в этот диапазон.
4
Как заполнить диапазон
Для заполнения диапазона определенными значениями необходимо предпринимать такие действия:
В результате получится следующее. Здесь мы заполнили все нужные ячейки цифрами 2.
6
Автозаполнение – одна из самых востребованных функций Excel. Она позволяет осуществлять запись в ячейки диапазона не только одного значения, но и целого набора данных, которые соответствуют определенной закономерности. Например, числовой ряд 2, 4, 6, 8, 10 и так далее.
Для этого нам нужно ввести первые два значения последовательности в расположенные рядом по вертикали ячейки и передвинуть маркер автозаполнения на необходимое количество ячеек.
7 
8
Аналогично можно заполнять диапазон нужными датами, которые также подчиняются определенной закономерности. Для этого, давайте, введем дату 13 июня 2013 года и дату 16 июня 2013 года в американском формате.
9
После этого осуществляем уже знакомое нами перетаскивание.
10
Перемещение диапазона
Чтобы переместить диапазон, достаточно выполнить всего несколько простых действий. Сначала нужно выделить требуемый диапазон и зажать одну из его границ. В нашем случае – правую.
Потом нужно просто переместить его в нужное место и отпустить мышь.
11 
12
Копирование и вставка диапазона
Это тоже одна из достаточно частых операций, которую пользователи Excel осуществляют с диапазонами.
Чтобы это сделать, нужно выделить диапазон, сделать по нему правый клик мыши и нажать «Копировать». Также можно воспользоваться комбинацией клавиш Ctrl + C.
13
Также можно найти специальную кнопку на вкладке «Главная» в группе «Буфер обмена».
Следующий этап – вставить нужную информацию в другое место. Для этого необходимо найти ячейку, которая будет служить левым верхним углом диапазона, после чего аналогично вызвать контекстное меню, но при этом найти пункт «Вставить». Также можно воспользоваться стандартной комбинацией Ctrl + V, которая работает абсолютно в любых программах.
14
Как вставить определенную строку или столбец
Осуществление вставки строки или столбца осуществляется похожим образом. Сначала нужно их выделить.
15
Только после этого нужно сделать правый клик мыши и нажать на кнопку «Вставить», которая находится чуть ниже.
16
Таким вот способом у нас получилось вставить строку.
17
Именованные диапазоны
Как может быть понятно из названия, под именованным подразумевается тот диапазон, которому было присвоено имя. Это значительно удобнее, поскольку повышается его информативность, что особенно полезно, например, если над одним документом работает сразу несколько человек.
Назначить имя диапазону можно через Диспетчер имен, который можно найти в меню Формулы – Определенные имена – Диспетчер имен.
Но вообще, способов несколько. Давайте рассмотрим некоторые примеры.
Пример 1
Предположим, перед нами стоит задача определить объем продажи товаров. Для этой цели у нас отведен диапазон B2:B10. Чтобы присвоить имя, необходимо использовать абсолютные ссылки.
18
В общем, наши действия следующие:
Теперь можно вместо адреса диапазона вводить его имя. Так, с помощью формулы =СУММ(Продажи) можно вычислить сумму продаж для всех товаров.
20
Почему мы использовали абсолютную адресацию? Потому что она позволяет Excel жестко определить диапазон, который не будет меняться при копировании.
В некоторых случаях лучше использовать относительную ссылку.
Пример 2
Давайте теперь определим сумму продаж для каждого из четырех времен года. Ознакомиться с информацией о продажах можно на листе 4_сезона.
На этом скриншоте диапазоны следующие.
Соответственно, нам нужно разместить формулы в ячейках B11, C11, D11 и E11.
21
Конечно, чтобы воплотить эту задачу в реальность, вы можете создать несколько диапазонов, но это немного неудобно. Значительно лучше воспользоваться одним. Чтобы упростить так себе жизнь, необходимо использовать относительную адресацию. В таком случае достаточно просто иметь один диапазон, который в случае с нами получит название «Сезонные_продажи»
Для этого необходимо открыть диспетчер имен, ввести имя в диалоговом окне. Механизм тот же самый. Перед тем, как нажимать «ОК», нужно убедиться, что в строку «Диапазон» введена формула =’4сезона’!B$2:B$10
В этом случае адресация смешанная. Как вы видите, перед названием колонки не указано знака доллара. Это позволяет суммировать значения, которые находятся в одинаковых строках, но разных столбцов.
Далее методика действий та же самая.
22
Рекомендация: если нажать клавишу F2 при имеющемся выделении содержащей формулу с именем диапазона ячейки, правильные ячейки будут выделены синей рамкой.
23
Пример 3
В сложной формуле также допустимо применение именованного диапазона. Допустим, у нас есть большая формула, где именованный диапазон используется несколько раз.
Если потребуется внести изменения в используемый массив данных, то придется три раза это делать. Но вот если дать имя диапазону перед осуществлением непосредственно изменений, то достаточно в диспетчере имен его изменить, а имя останется то же самое. Это значительно удобнее.
Более того, если начать вводить имя диапазона, Excel его автоматически предложит вместе с другими формулам.
24
Автоматически изменяемые диапазоны
Нередко во время работы с информацией в электронной таблице не получается заранее узнать, какое количество данных будет собрано. Поэтому не всегда мы знаем, какой диапазон присваивать определенному имени. Поэтому можно сделать так, чтобы диапазон автоматически изменялся в зависимости от того, сколько данных было введено.
Предположим, вы – инвестор, и вам требуется знать, сколько всего денег вы получили за время инвестиций в определенный объект. И предположим, у вас есть такой отчет.
25
Для этого есть функция «Динамические имена». Чтобы присвоить его, необходимо осуществить следующие действия:
Теперь нужно ввести функцию СУММ с именем диапазона в качестве аргумента. После того, как вы попробуете на практике это, вы сможете увидеть, как изменяется сумма в зависимости от количества введенных элементов.
Как видим, есть множество интересных способов взаимодействия с диапазонами. Надеемся, что эта инструкция от азов до профессионализма вам пришлась по душе и оказалась полезной.
Excel 10. Диапазон и вычисления в нем
По окончании этого урока вы сможете:
Скачайте файл тут. Откройте файл. (Признаюсь – не владею ни одним из этих видов творчества. Просто взяла из Интернета)
1. Диалоговые окна «Вставка функции» и «Аргументы функции»
Шаг 1. Вводим в ячейку А7 Максимум и переходим в ячейку В7:
Шаг 2. Открываем диалоговое окно «Вставка функции» (лента Формулы → команда Вставить функцию):
Как только мы вызвали диалоговое окно «Вставка функции», в вычисляемой ячейке появился знак «=».
Шаг 3. Выбираем в списке из 10 последних использовавших функций «МАКС» (поле «Выберите функцию»).
Это ещё один способ вставить функцию в дополнение к уже известным по прошлой статье (Excel 9). В Excel встроено около 300 функций. Вряд ли вы будете использовать все функции. Поэтому полезно знать, где находится список из самых вами любимых функций, чтобы избежать длительных поисков.
В поле «Поиск функции» вы можете ввести описание нужной вам функции, и Excel услужливо поможет вам в поиске.
При нажатии кнопки выпадающего меню поля «Категории» вы увидите перечень всех категорий функций:
Нашли функцию МАКС? ОК!
Но у нас с вами мелкая неприятность – диалоговое окно «Аргументы функции» перекрывает ряд чисел:
Шаг 4. Щелкаем по значку со стрелкой справа от поля «Число!». Диалоговое окно свернется и вам надо выбрать диапазон, в котором будет искаться максимум.
Шаг 5. Щелкаем по значку со стрелкой справа от поля «Число!» – диалоговое окно «Аргументы функции» развернется. Нажимаем ОК.
В предыдущих версиях Excel эта операция носила название «Мастер функций.
| Понять и запомнить! | Кнопка со стрелкой, которая позволяет свернуть и развернуть диалоговое окно, встречается очень часто, так что рекомендую запомнить эту полезную операцию |
2. Имя диапазона
Хорошо, если диапазон для вычислений у нас небольшой, хорошо, если вычисляемая ячейка находится по вычисляемым диапазоном. Но мечты и реальность не всегда совпадают. Вот тут на помощь придет полезная возможность, которая называется «Имя диапазона».
1 способ.
Шаг 1. Выделяем диапазон ячеек В1:В6.
Шаг 2. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Создать из выделенного):
Откроется диалоговое окно «Создание имен из выделенного диапазона». Оставим активным параметр «в строке выше». ОК.
2 способ.
Шаг 3. Выделите диапазон ячеек А1:А6.
Шаг 4. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Присвоить имя):
Откроется диалоговое окно «Создание имени».
Шаг 5. Нажимаем на кнопку выпадающего меню имени ячейки (неважно, где находится активная ячейка):
Шаг 6. Щелкаем ЛМ по первому имени в этом списке:
Диапазон с именем «Наименование_курса» выделился полностью.
| Понять и запомнить! | Разница между двумя способами: 1. Имя диапазона определяется автоматически 2. Имя диапазона можно задать по своему желанию |
Шаг 7. Щелкните в любой ячейке по вашему выбору. Набираем «=су»:
Двойной щелчок ЛМ по «СУММ».
Шаг 8. Выбираем диапазон по имени (набираем непосредственно в ячейке «ц». сразу появляется выпадающий список, в котором присутствуют функции, начинающие на «ц», и имя нашего диапазона). Двойной щелчок ЛМ по имени диапазона:
Шаг 9. И обязательно закрывающая скобка! Требования Excel по части синтаксиса написания формул надо соблюдать.
Шаг 10. Нажимаем Enter:
Смотрим на строку формул: =СУММ(Цена). То есть по имени определил соответствующий диапазон.
Шаг 11. Лента Формулы → группа команд Определенные имена → команда Диспетчер имен. Откроется диалоговое окно «Диспетчер имен»:
В этом окне вы можете создать новое имя для диапазона, изменить или удалить имеющееся имя диапазона. В поле «Диапазон» прописано: на каком листе книги расположен диапазон и адреса граничных ячеек. Кнопка с стрелкой, которая находится справа от этого поля, позволяет свернуть диалоговое окно и изменить диапазон.
3. Абсолютный и относительный адрес ячейки
Скопируем одну таблицу. Вот тут внимание! Если вы просто выделите весь диапазон и перенесете на другое место листа (Excel 5), то вы увидите следующую картину:
Потом потратим драгоценное время на настройки ширины и высоты ячеек. Так что не торопитесь.
Шаг 1. Выделяем таблицу со значениями и копируем в буфер обмена:
Шаг 2. Вставляем содержимое буфера обмена в ячейку F1 специальной вставкой с сохранение ширины столбцов:
Советую постепенно заучивать кнопки специальной вставки – сбережете время и нервы.
Сейчас мы подсчитаем стоимость курса для группы из 20 человек (больше набирать смысла нет). Для первой таблицы введите в диапазоне С2:С6 число «20» в каждую ячейку. Для второй таблицы введите число «20» в ячейку Н1:
Шаг 3. В ячейку D2 введите формулу «=ПРОИЗВЕД(В2;С2). Можно проще: «=В2*С2»:
Шаг 4. А теперь распространим эту формулу по всему столбцу стоимости курсов. Это можно сделать двумя способами.
1 способ.
2 способ.
Посмотрим на формулы в каждой ячейке.
Шаг 5. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):
Несмотря на наше недвусмысленное указание, какие ячейки будут перемножены, при копировании формулы вниз по диапазону адрес ячейки учитывает номер строки.
| Понять и запомнить! | Относительный адрес ячейки адрес ячейки соотносится с номером строки, если мы идем вниз и адрес ячейки соотносится с именем столбца, если мы идем вправо |
Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.
Поработаем со второй таблицей.
Шаг 6. В ячейку Н2 вводим формулу «=G2*H1»:
Теперь нам надо указать, что при копировании формулы ссылка будет только на ячейку Н1, то есть адрес этой ячейки будет абсолютным. Для указания абсолютного адреса в Excel служит знак «$» или знак доллара. Если не лень, ставьте вручную (Shift+4 в английской раскладке клавиатуры). Но лучше воспользоваться функциональной клавишей F4, которой все равно какая раскладка клавиатуры):
Если вы работаете за ноутбуком, то не забудьте в дополнение одновременно нажить клавишу Fn.
Шаг 7. Поместите курсор на имя ячейки «Н1» в формуле и нажмите Fn на клавиатуре:
Шаг 8. Нажмем Enter и скопируем формулу по диапазону:
Шаг 9. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):
При копировании формулы адрес с ценой курса соответствующим образом меняется, а адрес ячейки Н1 повторяется – это абсолютный адрес.
Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.
Теперь вы сможете:
По ходу дела мы узнали, что есть: