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

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

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

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

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

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

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

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

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

1. Перейдите на вкладку «Формулы», которая находится в главном меню. Там будет раздел «Определенные имена», где есть кнопка «Назначить имя», на которую нам нужно нажать.
2. Далее появится диалоговое окно, в котором необходимо заполнить поля, как показано на скриншоте. Важно отметить, что нам нужно применить функцию = ПЕРЕМЕЩЕНИЕ вместе с функцией ПРОВЕРИТЬдля создания автообновляемого диапазона. 
3. После этого нам нужно использовать функцию SUM, который использует наш динамический диапазон в качестве аргумента. 

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

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

Давайте посмотрим на функции, которые мы записали в поле «диапазон» ранее. Использование функции УДАЛЕНИЕ мы можем определить размер диапазона, учитывая, сколько ячеек в столбце B заполнено. Аргументы функции следующие:

1. Стартовая ячейка. С помощью этого аргумента пользователь может указать, какая ячейка диапазона будет считаться верхней левой. Он будет сообщать вниз и вправо.
2. Смещение диапазона по строкам. Используя этот диапазон, мы задаем количество ячеек, на которое должно произойти смещение от верхней левой ячейки диапазона. Вы можете использовать не только положительные значения, но ноль и минус. При этом смещение может вообще не произойти или будет осуществляться в обратном направлении.
3. Смещение диапазона по столбцам. Этот параметр аналогичен предыдущему, только позволяет задать степень горизонтального смещения диапазона. Здесь также можно использовать как нулевые, так и отрицательные значения.
4. Величина диапазона по высоте. Фактически, название этого аргумента проясняет нам, что он означает. Это количество ячеек, на которое должен увеличиться диапазон.
5. Значение диапазона по ширине. Аргумент аналогичен предыдущему, только касается столбцов.

Вам не нужно указывать последние два аргумента, если в этом нет необходимости. В этом случае значение диапазона будет состоять только из одной ячейки. Например, если вы укажете формулу =СМЕЩ(A1;0;0), эта формула будет относиться к той же ячейке, что и ячейка в первом аргументе. Если вертикальное смещение установлено на 2 единицы, то в этом случае ячейка будет ссылаться на ячейку А3. Теперь подробно опишем, что означает функция ПРОВЕРИТЬ.

СЧЁТ в Excel

Используя функцию ПРОВЕРИТЬ мы определяем, сколько ячеек в столбце B мы в общей сложности заполнили. То есть с помощью двух функций мы определяем, сколько ячеек в диапазоне заполнено, и на основе полученной информации определяем размер диапазона. Таким образом, окончательная формула будет следующей: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Давайте разберемся, как правильно понять принцип этой формулы. Первый аргумент указывает на то, где начинается наш динамический диапазон. В нашем случае это ячейка B2. Дальнейшие параметры имеют нулевые координаты. Это говорит о том, что нам не нужны смещения относительно левой верхней ячейки. Все, что мы заполняем, это размер диапазона по вертикали, который мы использовали в функции как ПРОВЕРИТЬ, который определяет количество ячеек, содержащих некоторые данные. Четвертый параметр, который мы заполнили, — это единица измерения. Таким образом мы показываем, что общая ширина диапазона должна составлять один столбец.

Таким образом, используя функцию ПРОВЕРИТЬ пользователь может использовать память максимально эффективно, загружая только те ячейки, которые содержат какие-либо значения. Соответственно, не будет дополнительных ошибок в работе, связанных с плохой производительностью компьютера, на котором будет работать электронная таблица.

Соответственно, чтобы определить размер диапазона в зависимости от количества столбцов, необходимо выполнить аналогичную последовательность действий, только в этом случае в третьем параметре нужно указать единицу измерения, а в четвертом - формулу. ПРОВЕРИТЬ.

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

Динамические диаграммы в Excel

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

1. Выбираем наш диапазон, после чего вставляем график типа «Гистограмма с группировкой». Найти этот пункт можно в разделе «Вставка» в разделе «Графики – Гистограмма».
2. Делаем щелчок левой кнопкой мыши по произвольному столбцу гистограммы, после чего в строке функции отобразится функция =РЯД(). На скриншоте вы можете увидеть подробную формулу. 
3. После этого в формулу необходимо внести некоторые изменения. Вам необходимо заменить диапазон после «Лист1!» к названию диапазона. Это приведет к следующей функции: =СТРОКА(Лист1!$B$1;;Лист1!доход;1)
4. Теперь осталось добавить в отчет новую запись, чтобы проверить, обновляется ли диаграмма автоматически или нет.

Давайте посмотрим на нашу схему.

Подведем итоги, как мы это сделали. На предыдущем шаге мы создали динамический диапазон, размер которого зависит от количества элементов, которые он содержит. Для этого мы использовали комбинацию функций ПРОВЕРИТЬ и УДАЛЕНИЕ. Мы дали этому диапазону имя, а затем использовали ссылку на это имя в качестве диапазона нашей гистограммы. Какой конкретно диапазон выбрать в качестве источника данных на первом этапе не столь важно. Главное, потом заменить его на название диапазона. Таким образом вы сможете сэкономить много оперативной памяти.

Именованные диапазоны и их использование

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

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

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

1. Максимальная длина — 255 символов. Этого вполне достаточно, чтобы присвоить имя, какое душе угодно.
2. Имя не должно содержать пробелов. Поэтому, если оно содержит несколько слов, то разделить их можно с помощью символа подчеркивания.

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

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

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

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

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

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

Если заголовок содержит несколько слов, Excel автоматически разделит их подчеркиванием.

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