При использовании Microsoft Excel очень часто пользователь заранее не знает, сколько информации в результате окажется в таблице. Следовательно, мы не всегда понимаем, какой диапазон должен быть покрыт. В конце концов, набор ячеек — это меняющееся понятие. Для устранения этой проблемы необходимо автоматизировать формирование диапазона, чтобы он основывался исключительно на объеме данных, введенных пользователем.
Автоматически изменяемые диапазоны ячеек в Excel
Преимущество автоматического изменения диапазонов в Excel состоит в том, что они значительно упрощают использование формул. Кроме того, они позволяют значительно упростить анализ сложных данных, содержащих большое количество формул, включающих множество функций. Вы можете дать этому диапазону имя, чтобы он автоматически обновлялся в зависимости от содержащихся в нем данных.
Как сделать автоматическое изменение диапазона в Excel
Предположим, вы инвестор, которому необходимо вложить деньги в объект. Следовательно, мы хотели бы получать информацию о том, сколько в целом вы можете заработать, пока деньги будут работать на этот проект. Однако для получения этой информации нам необходимо регулярно отслеживать, сколько общей прибыли приносит нам эта статья. Сделайте тот же отчет, что и на этом скриншоте.
На первый взгляд решение очевидно: нужно просто сложить весь столбец. Если отображаются какие-либо записи, сумма будет обновляться независимо. Но у этого метода масса недостатков:
- Если проблема решена таким образом, вы не сможете использовать ячейки в столбце B для других целей.
- Такая таблица будет потреблять много оперативной памяти, что делает невозможным использование документа на слабых компьютерах.
Следовательно, необходимо решить эту проблему с помощью динамических имен. Для их создания необходимо выполнить следующую последовательность действий:
- Перейдите на вкладку «Формулы» в главном меню. Будет раздел «Определенные имена», где есть кнопка «Имя», которую нам нужно нажать.
- После этого появится диалоговое окно, в котором необходимо заполнить поля, как показано на скриншоте. Важно отметить, что вам необходимо использовать функцию = OFFSET вместе с функцией COUNT для создания автоматически обновляемого диапазона.
- После этого нам нужно использовать функцию СУММ в качестве аргумента, для которого мы используем наш динамически изменяющийся диапазон.
После выполнения этих шагов мы можем увидеть, как обновляется покрытие ячеек, принадлежащих к «доходному» диапазону, когда мы добавляем туда новые элементы.
Функция СМЕЩ в Excel
Давайте посмотрим на функции, которые мы записали в поле «диапазон» ранее. Используя функцию OFFSET, мы можем определить размер диапазона, учитывая, сколько ячеек в столбце B заполнено. Аргументы функции следующие:
- Стартовая ячейка. С помощью этого аргумента пользователь может указать, какая ячейка диапазона будет учитываться в верхнем левом углу. От него отношения будут происходить внизу и справа.
- Диапазон смещения в строке. С помощью этого диапазона мы устанавливаем количество ячеек, на которое должно происходить смещение от верхней левой ячейки диапазона. Можно использовать не только положительные значения, но и ноль и минус. В этом случае движение может вообще не произойти или оно будет выполнено в обратном направлении.
- Смещение диапазона по столбцам. Этот параметр аналогичен предыдущему, только позволяет установить степень смещения диапазона по горизонтали. Здесь также можно использовать как нулевые, так и отрицательные значения.
- Размер интервала по высоте. Действительно, название этой темы дает нам четкое представление о том, что она означает. Это количество ячеек, на которое нужно увеличить диапазон.
- Размер ассортимента большой. Рассуждение аналогично предыдущему, касается только столбцов.
Нет необходимости приводить последние два аргумента, если в этом нет необходимости. В этом случае значение диапазона будет всего одна ячейка. Например, если вы укажете формулу = OFFSET (A1; 0; 0), эта формула будет ссылаться на ту же ячейку, что и первый аргумент. Если вертикальное смещение установлено на 2 единицы, то в этом случае ячейка будет относиться к ячейке A3. Теперь подробно опишем, что означает функция СЧЁТ.
Функция СЧЕТ в Excel
Используя функцию COUNT, мы определяем, сколько ячеек в столбце B мы заполнили всего. То есть мы определяем с помощью двух функций, сколько ячеек в диапазоне заполнено, и на основе полученной информации определяем размер диапазона. Следовательно, окончательная формула будет следующей: = OFFSET (Sheet1! $ B $ 2; 0; 0; COUNT (Sheet1! $ B: $ B); 1)
Давайте посмотрим, как правильно понять, как работает эта формула. Первый аргумент указывает, где начинается наш динамический диапазон. В нашем случае это ячейка B2. Дополнительные параметры имеют нулевые координаты. Это означает, что нам не нужно смещение относительно верхней левой ячейки. Все, что мы заполняем, — это вертикальный размер диапазона, для которого мы использовали функцию COUNT, которая определяет количество ячеек, в которых присутствуют некоторые данные. Четвертый параметр, который мы ввели, — это единица. Таким образом, мы показываем, что общая ширина диапазона должна составлять один столбец.
Следовательно, с помощью функции COUNT пользователь может использовать память максимально эффективно, загружая только те ячейки, которые содержат некоторые значения. В результате больше не будет ошибок в задании, связанных с низкой производительностью компьютера, на котором будет выполняться электронная таблица.
Соответственно, чтобы определить размер диапазона по количеству столбцов, нужно выполнить аналогичную последовательность действий, только в этом случае в третьем параметре нужно указать единицу измерения, а в четвертом — формулу COUNT.
Мы видим, что с помощью формул Excel можно не только автоматизировать математические вычисления. Это всего лишь капля в море, но на самом деле они позволяют автоматизировать практически любую задачу, которая приходит в голову человеку.
Динамические диаграммы в Excel
Итак, на последнем шаге мы смогли создать динамический диапазон, размер которого полностью зависит от того, сколько заполненных ячеек он содержит. Теперь вы можете создавать динамические диаграммы на основе этих данных, которые будут автоматически меняться, как только пользователь внесет некоторые изменения или добавит дополнительный столбец или строку. Последовательность действий в этом случае следующая:
- Выберите наш диапазон, затем вставьте диаграмму типа «Сгруппированная гистограмма». Вы можете найти эту статью в разделе «Вставка» в разделе «Графики-гистограммы».
- Щелкните левой кнопкой мыши случайный столбец гистограммы, после чего в строке функции отобразится функция = СТРОКА (). На скриншоте вы можете увидеть подробную формулу.
- Далее вам нужно внести некоторые изменения в формулу. Вам необходимо заменить диапазон после «Sheet1!» к названию диапазона. Результатом является следующая функция: = SERIES (Sheet1! $ B $ 1 ;; Sheet1! Income; 1)
- Теперь осталось добавить новую запись в отчет, чтобы проверить, обновляется ли график автоматически или нет.
Давайте теперь полюбуемся нашей диаграммой.
Подведем итоги, как мы действовали. На предыдущем шаге мы создали динамический диапазон, размер которого зависит от того, сколько элементов в него включено. Для этого мы использовали комбинацию функций COUNT и OFFSET. Мы назвали этот диапазон, поэтому мы использовали ссылку на это имя в качестве диапазона нашей гистограммы. Какой именно диапазон выбрать в качестве источника данных на первом этапе, не так важно. Главное, потом заменить его на название диапазона. Это может значительно сэкономить оперативную память.
Именованные диапазоны и их использование
Теперь поговорим более подробно о том, как правильно создавать именованные диапазоны и использовать их для выполнения задач, которые ставятся перед пользователем Excel.
По умолчанию мы используем обычные адреса ячеек для экономии времени. Это полезно, когда вам нужно написать диапазон один или несколько раз. Если вам нужно использовать его постоянно или чтобы он был адаптивным, вам нужно использовать именованные диапазоны. Они значительно упрощают создание формул, и пользователю не составит труда анализировать сложные формулы, включающие большое количество функций. Мы описываем некоторые этапы создания динамических диапазонов.
Все начинается с присвоения имени ячейке. Для этого выделите его и впишите нужное нам имя в поле его имени. Важно, чтобы это было легко запомнить. При именовании следует учитывать некоторые ограничения:
- Максимальная длина — 255 символов. Этого достаточно, чтобы присвоить имя, которое душе угодно.
- Имя не должно содержать пробелов. Поэтому, если он содержит несколько слов, вы можете разделить их с помощью подчеркивания.
Если позже на других листах этого файла нам потребуется отобразить это значение или применить его для выполнения дальнейших вычислений, нет необходимости переключаться на самый первый лист. Вы можете просто написать это имя ячейки в диапазоне.
Следующим шагом будет создание именованного диапазона. Процедура в основном такая же. Сначала вам нужно выбрать диапазон, а затем указать его имя. Позже это имя можно будет использовать во всех других операциях с данными в Excel. Например, именованные диапазоны часто используются для суммирования значений.
Кроме того, вы можете создать именованный диапазон на вкладке «Формулы» с помощью инструмента «Указать имя». После его выбора появится окно, в котором вам нужно выбрать имя для нашего диапазона, а также указать область, к которой он будет применяться, вручную. Вы также можете указать, где будет действовать этот диапазон: на листе или по всей книге.
Если вы уже создали диапазон имен, для его использования существует специальная служба, называемая диспетчером имен. Он позволяет не только редактировать или добавлять новые имена, но и удалять их, если они больше не нужны.
Следует учитывать, что при использовании именованных диапазонов в формулах после их удаления формулы не будут автоматически перезаписаны правильными значениями. Следовательно, могут возникнуть ошибки. Поэтому перед удалением именованного диапазона необходимо убедиться, что он не используется ни в одной из формул.
Другой способ создать именованный диапазон — извлечь его из таблицы. Для этого есть специальный инструмент «Создать из выделенного». Как мы выяснили, чтобы использовать его, вам нужно сначала выбрать диапазон, который мы будем изменять, а затем указать место, где у нас будут заголовки. Следовательно, на основе этих данных Excel автоматически обработает все данные, и заголовки будут присвоены автоматически.
Если заголовок состоит из нескольких слов, Excel автоматически разделит их подчеркиванием.
Поэтому мы разобрались, как создавать динамические именованные диапазоны и как с их помощью можно автоматизировать работу с большими объемами данных. Как видите, достаточно использовать несколько функций и инструментов, встроенных в функционал программы. Здесь нет ничего сложного, хотя новичку так может показаться на первый взгляд.