Что такое диапазон данных в excel
Определение диапазона и использование в электронных таблицах Excel
Информация в этой статье относится к версиям Excel 2019, 2016, 2013, 2010, Excel Online и Excel для Mac.
Смежные и несмежные диапазоны
Смежный диапазон ячеек — это группа выделенных ячеек, которые расположены рядом друг с другом, например, диапазон С1-С5, показанный на изображении выше.
Несмежный диапазон состоит из двух или более отдельных блоков ячеек. Эти блоки могут быть разделены строками или столбцами, как показано диапазонами от А1 до А5 и от С1 до С5.
Как смежные, так и несмежные диапазоны могут включать в себя сотни или даже тысячи ячеек и охватывать рабочие листы и рабочие тетради.
Имена диапазонов
Диапазоны настолько важны в таблицах Excel и Google, что имена могут присваиваться определенным диапазонам, чтобы с ними было проще работать и использовать их повторно при ссылках на них в диаграммах и формулах.
Выберите диапазон на рабочем листе
Если выбран диапазон из более чем одной ячейки, изменения в рабочей таблице, за некоторыми исключениями, такими как ввод и редактирование данных, влияют на все ячейки в выбранном диапазоне.
Существует несколько способов выбора диапазона на листе. К ним относятся использование мыши, клавиатуры, поля имени или их комбинации.
Выберите диапазон для использования в формуле или диаграмме
При вводе диапазона ссылок на ячейки в качестве аргумента для функции или при создании диаграммы, помимо ввода диапазона вручную, диапазон также можно выбрать с помощью наведения.
Диапазоны идентифицируются по ссылкам или адресам ячеек в верхнем левом и нижнем правом углах диапазона. Эти две ссылки разделены двоеточием. Двоеточие указывает Excel включить все ячейки между этими начальной и конечной точками.
Range vs. Array
Иногда кажется, что термины «диапазон» и «массив» взаимозаменяемы для Excel и Google Sheets, поскольку оба термина связаны с использованием нескольких ячеек в книге или файле.
Чтобы быть точным, разница в том, что диапазон относится к выбору или идентификации нескольких ячеек (например, A1: A5), а массив относится к значениям, расположенным в этих ячейках (например, <1; 2; 5; 4; 3) >).
Это не означает, что диапазон ссылок на ячейки не может быть введен в качестве аргументов для SUMPRODUCT и INDEX. Эти функции извлекают значения из диапазона и переводят их в массив.
Например, обе следующие формулы возвращают результат 69, как показано в ячейках E1 и E2 на изображении.
С другой стороны, SUMIF и COUNTIF не принимают массивы в качестве аргументов. Таким образом, хотя приведенная ниже формула возвращает ответ 3 (см. Ячейку E3 на рисунке), та же формула с массивом не будет принята.
В результате программа отображает окно сообщения со списком возможных проблем и исправлений.
Диапазон Excel
Имя ячейки
Начнем с простого – присвоим имя ячейке. Для этого просто выделяем ее (1) и в поле имени (2) вместо адреса ячейки указываем произвольное название, которое легко запомнить.
Длина имени ограничена 255 символами, что более чем достаточно. Также в имени не должно быть пробелов, поэтому если оно состоит из нескольких слов, то их можно разделять знаком подчеркивания.
Если теперь на других листах книги нам нужно будет вывести данное значение или использовать его в дальнейших расчетах, то не обязательно переключаться на первый лист и указывать ячейку вручную. Достаточно просто ввести имя ячейки и ее значение будет подставлено.
Ячейки, строки, столбцы
Давайте начнем с выбора ячеек, строк и столбцов.
Примеры диапазона
Диапазон представляет собой набор из двух и более ячеек.
Заполнение диапазона
Чтобы заполнить диапазон, следуйте инструкции ниже:
Именованный диапазон
Аналогичным образом можно задать имя и для диапазона ячеек, то есть выделим диапазон (1) и в поле имени укажем его название (2):
Далее это название можно использовать в формулах, например, при вычислении суммы:
Также создать именованный диапазон можно с помощью вкладки Формулы, выбрав инструмент Задать имя.
Появится диалоговое окно, в котором нужно указать имя диапазона, выбрать область, на которую имя будет распространяться (то есть на всю книгу целиком или на отдельные ее листы), при необходимости заполнить примечание, а далее выбрать соответствующий диапазон на листе.
Для работы с существующими диапазонами на вкладке Формулы есть Диспетчер имен.
С его помощью можно удалять, изменять или добавлять новые имена ячейкам или диапазонам.
При этом важно понимать, что если вы используете именованные диапазоны в формулах, то удаление имени такого диапазона приведет к ошибкам.
Задача
Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):
Для создания динамического диапазона:
ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков.
Теперь создадим еще один динамический диапазон для суммирования продаж.
Для создания динамического диапазона :
Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).
И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.
Или, например, в апреле.
Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .
Визуальное отображение динамического диапазона
Как посчитать количество ссылок на столбцы таблицы Excel
Пример 2. Определить количество столбцов в таблице и записать это значение в ячейку A16.
Используем формулу ОБЛАСТИ, поочередно выделяя каждый столбец ячейки в качестве параметра. Перед выбором последующего столбца нажимаем и удерживаем кнопку Ctrl. Если добавить символ «)» и нажать Enter, появится диалоговое окно с сообщением о том, что было введено слишком много аргументов. Добавим дополнительные открывающую и закрывающую скобки.
Определение принадлежности ячейки к диапазону таблицы
Пример 3. Определить, принадлежит ли ячейка заданному диапазону ячеек.
Рассматриваемая функция также позволяет определить, принадлежит ли ячейка выделенной области. Выполним следующие действия:
Данная ошибка означает, что ячейка не принадлежит выделенной области.
Если выделить несколько ячеек внутри диапазона, функция ОБЛАСТИ вернет количество выделенных ячеек:
Описанные особенности работы данной функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
Смотрите также =ЕСЛИ(ЕЧИСЛО(A2);A2;B2) протягиваете копируете соответственно макрос “Фильтра” хамом станешь…Оно кому только в таблице.: Думаю возможно если столбец. Т.е. получается, ВСЕ”, затем произведите и нажатьВставить более сложная процедура,Нажмите иконку Вставить на указанных ниже действий.Вставить, можно выбрать временно отобразить данные, выделенный фрагмент наПримечание: вставляете как значения выводит таблицу с надо?
The_Prist копировать выделенный диапазон что выделяем для описанные выше действияCtrl+Space. Только имейте в чем хотелось бы. вкладке Главная илиЧтобы переместить ячейки, нажмите. параметры вставки, которые которые не нужно другой лист или Мы стараемся как можно
grablik заданными параметрами дат.Юрий М: В примере все по одной ячейке.
копирования не Range(“7:7″ с формой.(Пробел). Таким способом виду, что здесь На самом деле, нажмите Ctrl+V на кнопкуСочетание клавиш
следует применить к
будут выделены только существует несколько особенностей, это один из
Вырезанные ячейки переместятся на. клавиш CTRL+V.При копировании значения последовательно ячеек с данными, листа или выберите
Диапазон Excel
Имя ячейки
Начнем с самого простого: дайте ячейке имя. Для этого просто выделите ее (1) и в поле имени (2) вместо адреса ячейки введите любое легко запоминающееся имя.
Имя ограничено 255 символами, что более чем достаточно. Имя также не должно содержать пробелов, поэтому, если оно состоит из нескольких слов, можно разделить их символом подчеркивания.
Если теперь мы хотим вывести это значение на другие листы книги или использовать его в дальнейших расчетах, нет необходимости переходить на первый лист и вручную указывать ячейку. Просто введите имя ячейки, и ее значение будет заменено.
Использование имени ячейки
Ячейки, строки, столбцы
Начнем с выделения ячеек, строк и столбцов.
Примеры диапазона
Заполнение диапазона
Следуйте приведенным ниже инструкциям, чтобы заполнить диапазон:
Эта техника перетаскивания очень важна, вы будете часто использовать ее в Excel. Вот еще один пример:
Именованный диапазон
Диапазон ячеек можно назвать аналогичным образом, т.е. выделить диапазон (1) и ввести его имя в поле имени (2):
Создание именованного диапазона
Затем это имя можно использовать в формулах, например, для вычисления суммы:
Использование именованного диапазона в формуле
Именованный диапазон также можно создать на вкладке Формулы, выбрав инструмент Задать имя.
Создание именованного диапазона с помощью панели инструментов
Появится диалоговое окно, в котором нужно ввести имя диапазона, выбрать область, к которой будет применяться имя (т.е. ко всей книге или к отдельным листам), при необходимости заполнить примечание, а затем выбрать соответствующий диапазон на листе.
Создание имени с помощью диалогового окна
Для работы с существующими диапазонами на вкладке Формулы есть Менеджер имен.
Используйте этот инструмент для удаления, изменения или добавления новых имен к ячейкам или диапазонам.
Управление именованными диапазонами
Однако важно понимать, что если вы используете именованные диапазоны в формулах, удаление имени такого диапазона приведет к ошибкам.
Задача
У вас есть таблица продаж некоторых товаров по месяцам (см. файл-образец ):
Задача состоит в том, чтобы найти общий объем продаж продукции в данном месяце. Пользователь должен иметь возможность выбрать месяц и получить общую сумму продаж. Пользователь должен выбрать месяц, используя выпадающий список.
Чтобы решить эту проблему, нам нужно создать два динамических диапазона: один для выпадающего списка, содержащего месяцы, а другой для диапазона суммы.
Для генерации динамических диапазонов мы будем использовать функцию HUMMING(), которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Вы можете указать высоту и ширину диапазона, а также смещение по строкам и столбцам.
Создайте динамический диапазон для выпадающего списка, содержащего месяцы. С одной стороны, необходимо учесть, что пользователь может добавить продажи за месяцы после апреля (май, июнь. ), с другой стороны, выпадающий список не должен содержать пустых строк. Динамический диапазон как раз и является решением этой проблемы.
Для создания динамического диапазона:
Когда вы заполните таблицу данными о продажах за май, июнь и так далее, формула READ(sheet1!$B$5:$I$5) вернет количество заполненных ячеек (количество названий месяцев) и таким образом определит новую ширину динамического диапазона, который в свою очередь создаст выпадающий список.
ПРИМЕЧАНИЕ: При использовании функции SCRETZ() убедитесь, что нет пустых ячеек! Т.е. вы должны заполнить список месяцами без пробелов.
Теперь создадим еще один динамический диапазон для подведения итогов продаж.
Для создания динамического диапазона :
И, наконец, если вы введете формулу = SUMM(Sales_over_month) в ячейку C2, вы получите сумму продаж в выбранном месяце.
Например, в мае месяце.
Или, например, в апреле месяце.
Визуальное отображение динамического диапазона
Условное форматирование автоматически выделило серым цветом продажи текущего месяца, который был выбран в выпадающем списке.
Как посчитать количество ссылок на столбцы таблицы Excel
Пример 2. Определите количество столбцов в таблице и введите это значение в ячейку A16.
Мы используем формулу OVERALL, выбирая в качестве параметра поочередно каждый столбец ячейки. Нажмите и удерживайте клавишу Ctrl перед выбором следующего столбца. Если вы добавите «)» и нажмите Enter, появится диалоговое окно, указывающее на то, что вы ввели слишком много аргументов. Добавьте дополнительные открывающие и закрывающие скобки.
Определение принадлежности ячейки к диапазону таблицы
Пример 3 Определяет, принадлежит ли данная ячейка заданному диапазону ячеек.
Рассмотренная здесь функция также позволяет определить, принадлежит ли ячейка выбранному диапазону. Процедура выполняется следующим образом:
Эта ошибка указывает на то, что ячейка не принадлежит выбранному диапазону.
Если вы выделите более одной ячейки в диапазоне, функция VARIABLE вернет количество выделенных ячеек:
Описанные возможности этой функции могут быть полезны при работе с большим количеством таблиц данных.
Перемещение и копирование ячеек и их содержимого
См. также = IF(ANSWER(A2);A2;B2) вы копируете макрос «Фильтр» соответствующим образом. Это будет работать только в таблице: Я думаю, что это возможно, если колонна. Т.е. получается, что ВСЕ», затем выполните и нажмите кнопку Вставить более сложную процедуру, щелкните значок Вставить на следующих действиях.Вставить, вы можете выбрать для временного отображения данных, выбранный раздел наПримечание: вставить как выходные значения таблицы с необходимостью?
The_Prist скопируйте выделенный диапазон, который вы выбрали для вышеуказанных действийCtrl+Paste. Чтобы переместить ячейки, щелкните мышью. Вставьте варианты, которые не требуют другого рабочего листа или Попробуйте как можно чаще
Юрий М: В примере все по порядку.
Copy not Range(«7:7″ с form. Таким образом, вы можете нажать Ctrl+V, чтобы
OLEGOFF ), и Range(«$A$7:$V$7,$X$7:$IV$7).The_Prist
будет выделено лишь несколько особенностей, это один из
в зависимости от того, в каких случаях
При копировании значений ячеек данных по одному, листайте или выделяйте с помощью CTRL+V.
Как в Excel задать диапазон значений
В ходе использования Microsoft Excel очень часто пользователь не знает заблаговременно, какое количество информации будет по итогу в таблице. Следовательно, мы не во всех ситуациях понимаем, какой диапазон должен быть охвачен. Ведь набор ячеек – понятие изменчивое. Чтобы избавиться от этой проблемы необходимо сделать формирование диапазона автоматическим, чтобы он опирался исключительно на количество данных, которое было введено пользователем.
Автоматически изменяемые диапазоны ячеек в Excel
Преимущество автоматически изменяемых диапазонов в Excel заключается в том, что они позволяют значительно облегчить использование формул. Кроме того, они дают возможность существенно упростить анализ сложных данных, которые содержат большое количество формул, в состав которых входит множество функций. Можно присвоить такому диапазону имя, и дальше он будет обновляться автоматически в зависимости от того, какие данные в нем содержатся.
Как сделать автоматическое изменение диапазона в Excel
Предположим, вы – инвестор, которому надо вложить средства в какой-то объект. В результате мы хотим получить информацию о том, сколько можно суммарно заработать за все время, пока деньги будут работать на этот проект. Тем не менее, чтобы получить эту информацию, нам надо регулярно следить за тем, сколько суммарно прибыли нам приносит этот объект. Сделайте такой же отчет, который есть на этом скриншоте.
На первый взгляд решение очевидно: нужно просто суммировать целый столбец. Если в нем появляются записи, то сумма будет обновляться самостоятельно. Но этот метод имеет множество недостатков:
Следовательно, нужно решать эту задачу через динамические имена. Чтобы их создать, необходимо выполнить следующую последовательность действий:
После выполнения этих действий мы можем увидеть, как охват ячеек, принадлежащих к диапазону «доход», обновляется по мере того, как мы добавляем туда новые элементы.
Функция СМЕЩ в Excel
Давайте рассмотрим функции, которые были нами записаны в поле «диапазон» ранее. С помощью функции СМЕЩ мы можем определять величину диапазона, учитывая то, сколько ячеек в колонке B заполнено. Аргументы функции следующие:
Указывать последние два аргумента не нужно, если в этом нет необходимости. В этом случае величина диапазона будет составлять всего одну ячейку. Например, если указать формулу =СМЕЩ(A1;0;0), эта формула будет ссылаться на ту же ячейку, которая в первом аргументе. Если же смещение по вертикали поставить 2 единицы, то в этом случае ячейка будет ссылаться на ячейку A3. Теперь давайте детально распишем, что означает функция СЧЕТ.
Функция СЧЕТ в Excel
С помощью функции СЧЕТ мы определяем, сколько ячеек в колонке B у нас по итогу заполнено. То есть, мы определяем с помощью двух функций то, сколько ячеек в диапазоне заполнено, и исходя из полученных сведений определяет величину диапазона. Следовательно, итоговая формула получится следующей: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
Давайте разберем, как правильно понимать принцип работы этой формулы. Первый аргумент показывает на то, где начинается наш динамический диапазон. В нашем случае это ячейка B2. Дальнейшие параметры у нас имеют нулевые координаты. Это говорит о том, что смещения относительно верхней левой ячейки нам не нужно. Все, что мы заполняем – это размер диапазона по вертикали, в качестве которого мы использовали функцию СЧЕТ, которая определяет количество ячеек, в которых есть какие-то данные. Четвертый параметр, который мы заполнили – это единица. Таким образом мы показывает то, что общая ширина диапазона должна составлять одну колонку.
Таким образом, с помощью функции СЧЕТ пользователь может использовать память максимально эффективно, загружая туда только те ячейки, которые содержат какие-то значения. Соответственно, не будет дополнительных ошибок в работе, связанных с плохой производительностью компьютера, на котором будет работать электронная таблица.
Соответственно, чтобы определять размер диапазона в зависимости от количества столбцов, нужно выполнять аналогичную последовательность действий, только в таком случае нужно в третьем параметре указать единицу, а в четвертом – формулу СЧЕТ.
Видим, что с помощью формул Excel можно не только автоматизировать математические вычисления. Это всего лишь капля в море, а на деле они позволяют автоматизировать почти любую операцию, которая придет человеку в голову.
Динамические диаграммы в Excel
Итак, мы на прошлом этапе смогли создать динамический диапазон, размер которого полностью зависит от того, сколько заполненных ячеек он содержит. Теперь можно на основании этих данных создавать динамические диаграммы, которые будут автоматически изменяться, как только пользователь внесет какие-то изменения или добавит дополнительную колонку или строку. Последовательность действий в этом случае следующая:
Полюбуемся теперь на нашу диаграмму.
Давайте подведем итоги, как мы действовали. Мы на предыдущем этапе создали динамический диапазон, размер которого зависит от того, сколько элементов в него входит. Для этого мы использовали комбинацию функций СЧЕТ и СМЕЩ. Мы этот диапазон сделали именным, и потом ссылку на это имя использовали в качестве диапазона нашей гистограммы. Какой конкретно диапазон выбирать в качестве источника данных на первом этапе, не столь важно. Главное – заменить его на имя диапазона потом. Так можно существенно сэкономить оперативную память.
Именованные диапазоны и их использование
Давайте поговорим теперь более подробно про то, как правильно создавать именованные диапазоны и их использовать для выполнения тех задач, которые ставятся перед пользователем Excel.
По умолчанию мы используем обычные адреса ячеек для того, чтобы сэкономить время. Это удобно, когда нужно прописать диапазон один или несколько раз. Если же его нужно использовать постоянно или же необходимо, чтобы он был адаптивным, то тогда надо использовать именованные диапазоны. Они позволяют сделать создание формул существенно легче, а также пользователю будет не так сложно анализировать сложные формулы, в состав которых входит большое количество функций. Давайте опишем некоторые этапы создания динамических диапазонов.
Начинается все с присвоения имени ячейке. Чтобы это сделать, достаточно просто выделить ее, после чего в поле ее имени написать то название, которое нам нужно. Важно, чтобы оно было легким для запоминания. Есть некоторые ограничения, которые надо учитывать во время присвоения имени:
Если потом на других листах этого файла нам нужно будет отобразить это значение или применять его для выполнения дальнейших расчетов, то нет необходимости переключаться на самый первый лист. Вы можете просто записать имя этой ячейки диапазона.
Следующий этап – создание именованного диапазона. Процедура в целом точно такая же. Сначала необходимо выделять диапазон, после чего указывать его имя. После этого данное название можно использовать во всех остальных операциях с данными в Excel. Например, именованные диапазоны часто используются для определения суммы значений.
Кроме этого, возможно создание именованного диапазона с помощью вкладки «Формулы», воспользовавшись инструментом «Задать имя». После того, как мы выберем его, появится окно, где надо выбрать имя для нашего диапазона, а также указать область, на которую он будет распространяться, вручную. Также можно задать где будет действовать этот диапазон: в рамках одного листа или на всей книге.
Если именной диапазон уже создан, то для того, чтобы его использовать, существует специальный сервис, который называется диспетчером имен. Он позволяет не только редактировать или добавлять новые имена, но и удалять их, если они уже не нужны.
При этом нужно учитывать, что при использовании именованных диапазонов в формулах, то после того, как его удалить, формулы автоматически не перезапишутся правильными значениями. Следовательно, возможно возникновение ошибок. Поэтому перед удалением именованного диапазона нужно убедиться, что он не используется ни в одной из формул.
Еще один способ создания именованного диапазона – получать его из таблицы. Для этого существует специальный инструмент, который называется «Создать из выделенного». Как мы понимаем, чтобы его использовать, необходимо сначала выделить тот диапазон, который мы будем редактировать, после чего задать место, в котором у нас располагаются заголовки. В результате, основываясь на этих данных Excel автоматически обработает все данные, и заголовки будут автоматически присвоены.
В случае, если в состав заголовка входит несколько слов, Excel автоматически их будет разделять с помощью знака подчеркивания.
Таким образом, мы разобрались, как создавать динамические именные диапазоны и как они позволяют автоматизировать работу с большими объемами данных. Как видим, достаточно использовать несколько функций и встроенных в функционал инструментов программы. Вовсе нет ничего сложного, хотя новичку может так показаться на первый взгляд.