Как составить диапазон в эксель

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция СЧЕТ в 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 автоматически их будет разделять с помощью знака подчеркивания.

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

Что такое диапазон в Excel.

​Смотрите также​​ ячейка не принадлежит​ используется для подсчета​ специальный режим. В​ его первую ячейку.​ проект с другими​ прошедший год:​Перейти​​ ячейку, или​​ вводя их имена​ ячейки.​ – подсчитывает число​.​
​B11​ группе Определенные имена​ найти быстро нужное​ таблицу, др.».​Диапазон в Excel​ выделенной области.​​ числа областей, содержащихся​​ строке состояния появится​ Она отличается от​ людьми.​Как видите, если ячейке​​.​B1:B3​ и ссылок на​Чтобы​ значений в столбце​​Но, если перед составлением​, в которой будет​​ выберите команду Присвоить​ место в таблице.​Как задать диапазон в​– это несколько​Если выделить несколько ячеек​
​ в переданной ссылке,​ сообщение: «Расширить выделенный​ других ячеек цветом​
​В поле​ или диапазону, на​
​Совет:​, чтобы выделить диапазон​ ячейки можно использовать​
​скопировать значение, формулу на​ А. Когда вы​
​ сложной формулы мы​ находится формула суммирования​ имя;​
​ Смотрите статью «Сделать​ Excel.​ ячеек из таблицы​
​ внутри диапазона, функция​ и возвращает соответствующее​ фрагмент». И теперь​ фона.​Диапазон​
​ которые ссылается формула,​ Чтобы быстро найти и​ из трех ячеек.​ поле имени.​ диапазон, не зависимо​ добавляете значение к​ присвоим диапазону ​ (при использовании относительной​
​в поле Имя введите:​ закладки в таблице​Можно в формуле​ или вся таблица​ ОБЛАСТИ вернет количество​ значение. В Excel​ выделите мышкой блок​К диапазонам относятся:​отображается адрес активной​ дать осмысленные имена,​ выделите все ячейки,​Примечание:​Поле «Имя» расположено слева​
​ от пустых ячеек​ диапазону, количество элементов​
​E2:E8​ адресации важно четко​ Продажи;​ Excel» здесь.​ написать адрес диапазона​ как один диапазон.​ выделенных ячеек:​ областью является одна​ F2:K5. Как видите,​
​Несколько ячеек выделенных блоком​ области, т.е. адрес​
​ то формула станет​ содержащие определенных типов​ В поле​ от строки формул.​, делаем так.​ увеличивается. В результате,​ какое-нибудь имя (например, Цены),​ фиксировать нахождение активной​в поле Область выберите​
​Подробнее о том,​​ через двоеточие. А​​ Выделяют диапазон ячеек,​
​Описанные особенности работы данной​ ячейка либо интервал​ в данном режиме​ (=B5:D8).​ ячейки или диапазона,​
​ гораздо понятнее.​
​ данных (например, формулы)​Имя​Кроме того, для выделения​Пишем формулу в​ именованный диапазон расширяется.​ то ссылку на​ ячейки в момент​ лист​ как создать диапазон,​ можно присвоить диапазону​ присваивают этому диапазону​ функции могут быть​ смежных ячеек.​ мы имеем возможность​Одна ячейка (=A2:A2).​ которые мы выбрали​Чтобы присвоить имя ячейке​ или только ячейки,​
​невозможно удалить или​ именованных и неименованных​​ ячейке, у нас,​
​Нажмите​ диапазон придется менять​ создания имени);​1сезон​ как правильно выделить​ имя и указывать​ имя​
​ полезны при работе​
​Пример 1. Вернуть число,​ выделять стразу несколько​Целая строка (=18:18) или​ ранее. При необходимости​
​ или диапазону этим​ которые удовлетворяют определенным​
​ изменить имена, определенные​ ячеек и диапазонов​
​ в примере, ячейка​
​ОК​только 1 раз​на вкладке Формулы в​(имя будет работать​​ несмежные ячейки, присвоить​ это имя в​-именованный диапазон в Excel​ с большим количеством​ соответствующее количеству областей​ диапазонов. Чтобы вернутся​ несколько строк (=18:22).​ данный диапазон можно​
​ способом, проделайте следующие​ критериям (например, только​ для ячеек и​ можно использовать команду​ L1.​, а затем​и даже не​ группе Определенные имена​ только на этом​ имя диапазону, как​

excel-office.ru

Именованный диапазон в MS EXCEL

​ формуле или искать​. Это имя используют​ таблиц данных.​ в диапазонах A1:B7,​ в обычный режим​Целый столбец (=F:F) или​ перезадать. Для этого​ действия:​ видимые ячейки или​

​ диапазонов. Имена можно​Перейти​=H1+K1​Close​ в формуле, а​ выберите команду Присвоить​ листе) или оставьте​

​ быстро найти диапазон,​ диапазон в диспетчере​ при работе с​Функция находиться в категории​ C14:E19, D9, Пример2!A4:C6.​ работы, повторно нажмите​ несколько столбцов (=F:K).​ поместите курсор в​Выделите требуемую область (на​ последнюю ячейку на​​ удалять и изменять​​.​Выделяем эту ячейку.​(Закрыть).​ в Диспетчере имен!​ имя;​

​ значение Книга, чтобы​​ как удалить диапазон,​ задач, т.д..​ таблицей, при поиске,​ формул «Ссылки и​Исходные данные на листе​ F8.​

​Несколько несмежных диапазонов (=N5:P8;E18:H25;I5:L22).​ поле​ данном этапе можно​ листе, содержащую данные​ только в диалоговом​Важно:​ Нажимаем «Копировать». Как​

Задача1 (Именованный диапазон с абсолютной адресацией)

​Теперь, когда вы добавляете​=СУММ(Цены)+СРЗНАЧ(Цены)/5+10/СУММ(Цены)​в поле Имя введите:​ имя было доступно​

​ изменить его, др.,​​Как указать диапазон в​​ в формулах, т.д.​ Массивы». Она имеет​ «Пример1»:​

​Как выделить большой диапазон​

• ​Целый лист (=1:1048576).​​Диапазон​​ выделить любую область,​​ или форматирование), нажмите​​ окне​
• ​ Чтобы выделить именованные ячейки​ вызвать функции, смотрите​ значение в диапазон,​Более того, при создании​
• ​ Сезонные_Продажи;​ на любом листе​
• ​ смотрите в статье​ формуле Excel.​​Ячейки в диапазоне​​ следующую форму синтаксической​Для подсчета количества областей​ ячеек в Excel?​Все выше перечисленные виды​, вокруг указанной области​ в дальнейшем вы​ кнопку​
• ​Диспетчер имен​ и диапазоны, необходимо​ в статье «Функции​
• ​ Excel автоматически обновляет​

​ формул EXCEL будет​в поле Область выберите​​ книги;​​ «Диапазон в Excel».​Можно записать адрес​ могут быть расположенны​ записи:​ используем формулу:​​ Клавиша F5 или​​ блоков являются диапазонами.​

​ появится динамическая граница.​ сможете ее перезадать).​Выделить​

​(вкладка​ сначала определить их​ Excel. Контекстное меню»​ сумму.​ сам подсказывать имя​​ лист​убедитесь, что в поле​Обычно ссылки на диапазоны​​ диапазона, если ячейки​ рядом в одном​=ОБЛАСТИ(ссылка)​Результат вычисления функции является​​ CTRL+G. В появившемся​​​

​Мышкой выделите новую область​ Мы выделим ячейку​в​Формулы​

Задача2 (Именованный диапазон с относительной адресацией)

​ имена на листе.​ тут. В строке​Урок подготовлен для Вас​ диапазона! Для этого​4сезона​​ Диапазон введена формула​​ ячеек вводятся непосредственно​ смежные. Например, A1:​​ столбце, в одной​​Описание аргумента:​ ошибка #ЗНАЧ!, поскольку​​ окне, в поле​​Выделение диапазонов – это​

​ или укажите эту​ С3, а затем​Перейти к​, группа​ Сведения об именовании​ адреса ячейки пишем​ командой сайта office-guru.ru​ достаточно ввести первую​(имя будет работать​ =’1сезон’!$B$2:$B$10​​ в формулы, например​​ D2. Значит, в​

​ строке, в нескольких​

• ​ссылка – обязательный для​​ диапазон «Пример2!A4:C6» находится​​ «Ссылка» введите адрес:​ одна из основных​ область, введя диапазон​ ее перезададим.​всплывающего окна и​Определенные имена​ ячеек и диапазонов​
• ​ диапазон, в который​Источник: http://www.excel-easy.com/examples/dynamic-named-range.html​ букву его имени.​ только на этом​
• ​нажмите ОК.​ =СУММ(А1:А10). Другим подходом​
• ​ этом диапазоне ячейки​ строках или столбцах​​ заполнения аргумент, который​​ на другом листе.​ B3:D12345 (или b3:d12345)​ операций при работе​
• ​ прямо в текстовое​Перейдите на вкладку​ выберите нужный вариант.​
• ​). Дополнительные сведения см.​

​ см. в статье​ хотим скопировать формулу.​Перевел: Антон Андронов​Excel добавит к именам​ листе);​Теперь в любой ячейке​​ является использование в​​ из столбцов А,​-смежные ячейки Excel​ принимает ссылку на​Для решения задачи используем​ и нажмите ОК.​ с Excel. Диапазоны​ поле. В нашем​

​Формулы​Excel предлагает несколько способов​​ в статье Определение​​ Определение и использование​ У нас стоит​Автор: Антон Андронов​​ формул, начинающихся на​​убедитесь, что в поле​ листа​ качестве ссылки имени​ B, C, D​​. Здесь в черном​​ одну или несколько​ формулу с помощью​

​ Таким образом, вы​
​ используют:​ случае мы выберем​и выберите команду​ присвоить имя ячейке​​ и использование имен​​ имен в формулах.​ диапазон L1:M8 (т.е.​Есть несколько способов​ эту букву, еще​

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

​ Диапазон введена формула​1сезон​ диапазона. В статье​ первой и второй​ квадрате выделен диапазон,​ ячеек из указанного​

​ функции СУММ:​

​ без труда захватили​при заполнении данных;​ ячейку D2.​Присвоить имя​ или диапазону. Мы​​ в формулах.​​В поле​​ два столбца).​​ быстро​

​ и имя диапазона!​ =’4сезона’!B$2:B$10​можно написать формулу​​ рассмотрим какие преимущества​​ строки.​ состоящий из смежных​ диапазона.​​Данная функция вычисляет сумму​​ огромный диапазон, всего​при форматировании;​Если Вас все устраивает,​

​.​

​ же в рамках​На вкладке «​Имя​Нажимаем «Enter». У нас​заполнить диапазон в Excel​Динамический именованный диапазон автоматически​

​нажмите ОК.​ в простом и​ дает использование имени.​А можно сделать​

excel2.ru

Динамический именованный диапазон в Excel

​ ячеек.​Примечания:​ полученных значений в​

1. ​ за пару кликов.​​при очистке и удалении​​ смело жмите​Откроется диалоговое окно​​ данного урока рассмотрим​​Главная​
2. ​, которое расположено слева​
3. ​ в таблице выделился​ различными данными, формулами​ расширяется при добавлении​

​Мы использовали смешанную адресацию​ наглядном виде: =СУММ(Продажи).​Назовем Именованным диапазоном в​именованный диапазон Excel​

1. ​А могут ячейки​​Аргументом рассматриваемой функции может​​ результате выполнения функций​​В поле «Имя» (которое​​ ячеек;​
2. ​ОК​​Создание имени​​ только 2 самых​
3. ​» в группе​​ от строка формул,​​ этот диапазон. Теперь​, т.д​

   ​ значения в диапазон.​
​ B$2:B$10 (без знака​

   

   ​ Будет выведена сумма​​ MS EXCEL, диапазон​​.​​ диапазона располагаться не​ являться только ссылка​

   • ​ ОБЛАСТИ для подсчета​​ расположено слева от​​при создании графиков и​
   • ​. Имя будет создано.​​.​​ распространенных, думаю, что​
   • ​ «​​ выполните одно из​​ из контекстного меню​
   • ​.​​Например, выберите диапазон​​ $ перед названием​​ значений из диапазона​​ ячеек, которому присвоено​
   • ​Как присвоить имя​​ рядом, а в​​ на диапазон ячеек.​

   ​ количества областей в​ строки формул) задайте​ диаграмм и т.п.​Помимо присвоения имен ячейкам​В поле​ каждый из них​Редактирование​ указанных ниже действий.​

4. ​ нажимаем «Вставить», затем​​Например у нас​​A1:A4​​ столбца). Такая адресация​​B2:B10​
5. ​ Имя (советуем перед​ диапазону Excel, смотрите​ разброс по всей​ Если было передано​

​ диапазонах A1:B7;C14:E19;D9 и​ диапазон ячеек: B3:D12345​
​Способы выделения диапазонов:​
​ и диапазонам, иногда​

​Имя​

office-guru.ru

Заполнить быстро диапазон, массив в Excel.

​ Вам обязательно пригодится.​» нажмите кнопку​​Чтобы выделить именованную ячейку​ «Enter». Получилось так.​​ диапазон А1:С800. Если​​и присвойте ему​​ позволяет суммировать значения​.​ прочтением этой статьи​ в статье «Присвоить​ таблице​ текстовое или числовое​ Пример2!A4:C6 соответственно. Результат:​ (или b3:d12345) и​Чтобы выделить диапазон, например​
​ полезно знать, как​введите требуемое имя.​ Но прежде чем​Найти и выделить​ или диапазон, введите​Так можно копировать и​ будем протягивать формулу​ имя​ находящиеся в строках​
​Также можно, например, подсчитать​ ознакомиться с правилами​
​ имя в Excel​- не смежные ячейки​ значение, функция выполнена​С помощью такой не​ нажмите «Enter».​ A1:B3, нужно навести​ присвоить имя константе.​ В нашем случае​ рассматривать способы присвоения​и нажмите кнопку​ имя и нажмите​ значения ячеек. Здесь​ вниз по столбцу,​
​Prices​
​2 310​
​ среднее значение продаж,​ создания Имен).​
​ ячейке, диапазону, формуле».​ Excel​ не будет, Excel​ хитрой формулы мы​Способ 5 и 6​ курсор мышки на​ Как это сделать​ это имя​ имен в Excel,​Перейти​
​ клавишу ВВОД.​ скопировали формулу в​ удерживая правую мышку​.​
​, в том столбце,​​ записав =СРЗНАЧ(Продажи).​Преимуществом именованного диапазона является​Динамический диапазон в Excel.​​. Например: ячейки в​
​ отобразит диалоговое «В​ получили правильный результат.​ – это самое​ ячейку A1 и​
​ Вы можете узнать​
​Коэффициент​ обратитесь к этому​. Можно также нажать​Совет:​ нижнюю строку.​ нажатой, то это​Рассчитайте сумму.​ в котором размещена​Обратите внимание, что EXCEL при создании​ его информативность. Сравним​В Excel можно​ первом, третьем и​
​ этой формуле обнаружена​​ быстрое решение для​ удерживая левую клавишу​ из этого урока.​. В ряде случаев​
​ уроку, чтобы запомнить​ сочетание клавиш CTRL​ Кроме того, можно щелкнуть​Как заполнить ячейки таблицы​
​ долго и не​Когда вы добавляете значение​ формула суммирования. Формулу​ имени использовал абсолютную адресацию​ две записи одной​
​ настроить таблицу, диапазон​ пятом столбцах из​ ошибка».​Пример 2. Определить количество​ выделения больших диапазонов.​ мышки провести курсор​Итак, в данном уроке​ Excel автоматически подставляет​ несколько простых, но​ + G на​ стрелку рядом с​

excel-office.ru

Выделение отдельных ячеек или диапазонов

​ быстро списком, смотрите​​ удобно. Проще сделать​ к диапазону, Excel​ суммирования можно разместить​ $B$1:$B$10. Абсолютная ссылка​ формулы для суммирования,​ так, что он​ первой, седьмой, девятой​В качестве аргумента ссылка​ столбцов в таблице​ Небольшие диапазоны в​ на ячейку B3.​ Вы узнали, как​ имя на основе​ полезных правил по​ клавиатуре.​ полем​ в статье «Заполнить​ по другому.​ не обновляет сумму.​ в любой строке​ жестко фиксирует диапазон​

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

​В списке​Имя​

​ автоматически список в​Ниже приведенными способами​Чтобы автоматически расширять именованный​ ниже десятой (иначе​​ суммирования:​​ =СУММ($B$2:$B$10) и =СУММ(Продажи).​

​ при добавлении или​​То есть, любые​ несколько диапазонов ячеек.​ значение в ячейку​ лучше выделять мышкой.​ ничего проще и​ или диапазону в​ ячейках. В нашем​Данный способ является самым​Перейти​

Выделение именованных и неименованных ячеек и диапазонов с помощью поля «Имя»

​, чтобы открыть список​​ Excel».​​ можно копировать диапазоны​ диапазон при добавлении​ возникнет циклическая ссылка).​в какой ячейке на​

• ​ Хотя формулы вернут​ удалении строк или​ ячейки в любом​ Для этого необходимо​

  ​ A16.​​Чтобы выделить диапазон нескольких​ этого достаточно для​ Excel. Если желаете​​ случае так и​​ быстрым способом присвоить​щелкните имя ячейки​ именованных ячеек или​В Excel можно​ как отдельно по​

• ​ значения, выполните следующие​Теперь введем формулу =СУММ(Сезонные_Продажи)​ листе Вы бы​ один и тот​ столбцов. Например, в​​ месте и количестве.​​ использовать еще по​Таблица:​ столбцов нужно подвести​ практических знаний. Но​ получить еще больше​ произошло. Если Excel​ имя ячейке или​ или диапазона, который​ диапазонов, и выбрать​​ установить формулу быстро​​ столбцам, по стокам,​

  ​ несколько шагов:​​ в ячейку​ не написали формулу​ же результат (если,​ формуле стоит диапазон​​Диапазон в Excel нужен​​ одной открывающей и​Используем формулу ОБЛАСТИ, поочередно​

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

  ​ в нем нужное​​ в большой диапазон,​​ так и по​​На вкладке​B11.​​=СУММ(Продажи) – суммирование​​ конечно, диапазону​ А1:В21. Если добавим​

​ для того,​​ закрывающей скобки (в​​ выделяя каждый столбец​​ заголовок первого столбца​ выделить блок B3:D12345.​ читайте следующие статьи:​ или такое имя​ Чтобы воспользоваться им,​ введите ссылку на​ имя.​​ не прибегая к​​ диапазону, состоящему из​​Formulas​​Затем, с помощью​​ будет производиться по​​B2:B10​ строку в таблицу,​чтобы найти определенные​ этом случае Excel​

Выделение именованных и неименованных ячеек и диапазонов с помощью команды «Перейти»

1. ​ ячейки в качестве​​ и удерживая левую​​Теперь щелкните по ячейке​Знакомство с именами ячеек​​ Вас не устраивает,​​ выполните следующие шаги:​​ ячейку в поле​​Чтобы выбрать две или​​ копированию. Например, увеличить​​ нескольких строк и​(Формулы) выберите​ Маркера заполнения, скопируем​ одному и тому​

   

2. ​присвоено имя Продажи),​​ то в формуле​​ ячейки для дальнейшей​ не будет распознавать​ параметра. Перед выбором​ клавишу протянуть его​ A1, после чего​​ и диапазонов в​​ введите требуемое Вам​

   ​Выделите ячейку или диапазон,​​Ссылка​​ более ссылки на​​ цену в прайсе​​ столбцов. Для примера​Name Manager​​ ее в ячейки​​ же диапазону​ но иногда проще​ нужно менять диапазон​ работы в таблице,​ символ «;» как​ последующего столбца нажимаем​​ до заголовка последнего​​ нажмите и удерживайте​

   ​ Excel​​ имя самостоятельно.​​ которым необходимо присвоить​​.​ именованные ячейки или​ на 6%. Как​ возьмем такой столбец.​(Диспетчер имен).​С11D11E11​​B1:B10​​ работать не напрямую​ на А1С21. Динамический​чтобы вставить этот​ разделитель аргументов в​ и удерживаем кнопку​ столбца. В процессе​ на клавиатуре SHIFT,​​5 полезных правил и​​В раскрывающемся списке​

​ имя. В нашем​​Например, введите в поле​ диапазоны, щелкните стрелку​ это сделать, смотрите​Копировать значение в диапазон​Нажмите кнопку​, и получим суммы​.​ с диапазонами, а​ диапазон сам все​ диапазон в формулу,​ функции. Например, результатом​ Ctrl. Если добавить​​ мы наблюдаем подсказку​​ а потом щелкните​​ рекомендаций по созданию​​Область​ случае это диапазон​

support.office.com

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

​Ссылка​ рядом с полем​ в статье «Как​ Excel.​Edit​ продаж в каждом​Иногда выгодно использовать не​ с их именами.​ исправляет. Читайте об​чтобы выделить и​ выполнения функции с​ символ «)» и​ Excel: количество выделенных​ по ячейке B3.​ имен в Excel​Вы можете указать​ B2:B13.​

Используем поле Имя

​значение​имя​ умножить столбец на​В ячейку I1​(Изменить).​ из 4-х сезонов.​

1. ​ абсолютную, а относительную​Совет​ этом статью «Чтобы​ удалить данные из​ указанными аргументами: ((A1:C5;E1:H12))​
2. ​ нажать Enter, появится​​ столбцов.​​ Таким образом, выделился​Диспетчер имен в Excel​ область видимости создаваемого​Щелкните по полю​​B3​​и нажмите кнопку​
3. ​ число в Excel».​​ пишем значение (слово,​​Кликните по полю​ Формула в ячейках​
4. ​ ссылку, об этом​: Узнать на какой диапазон​​ размер таблицы Excel​​ этих ячеек,​ будет значение 2,​ диалоговое окно с​Выделение строк выполняется аналогичным​ блок A1:B3. Данную​Как присваивать имена константам​ имени. Область видимости​Имя​
5. ​, чтобы выделить эту​ имя первого ссылку​​Примечание:​​ цифры, т.д.). Мы​Refers to​B11, С11D11E11​ ниже.​
6. ​ ячеек ссылается Имя можно​ менялся автоматически».​чтобы изменить ссылку​ поскольку в качестве​ сообщением о том,​ способом только курсор​

Используем диалоговое окно Создание имени

​ операцию выделения условно​ в Excel?​ – это область,​и введите необходимое​

1. ​ ячейку, или​ на ячейку или​Мы стараемся как​ написали слово «столбец».​(Диапазон) и введите​одна и та​Теперь найдем сумму продаж​ через Диспетчер имен​
2. ​Как найти диапазон в​​ в формулах, саму​​ аргумента переданы два​​ что было введено​​ мышки с нажатой​
3. ​ можно записать: A1​​Урок подготовлен для Вас​​ где вы сможете​
4. ​ имя, соблюдая правила,​​B1:B3​​ диапазон, который требуется​ можно оперативнее обеспечивать​ Выделяем эту ячейку​​ формулу:​​ же!​ товаров в четырех​ расположенный в меню​ Excel​ формулу,​ диапазона ячеек.​ слишком много аргументов.​ левой клавишей нужно​ потом SHIFT+B3.​ командой сайта office-guru.ru​ использовать созданное имя.​ рассмотренные здесь. Пусть​
5. ​, чтобы выделить диапазон​​ выделить. Затем удерживая​​ вас актуальными справочными​ и нажимаем два​=OFFSET($A$1,0,0,COUNTA($A:$A),1)​СОВЕТ:​ сезонах. Данные о​  Формулы/ Определенные имена/​.​​чтобы вставить этот​​Если аргумент рассматриваемой функции​ Добавим дополнительные открывающую​ вести вдоль нумерации​Диапазоны можно выделять и​Автор: Антон Андронов​ Если вы укажете​ это будет имя​ из трех ячеек.​ клавишу CTRL, щелкните​ материалами на вашем​​ раза левой мышкой​​=СМЕЩ($A$1;0;0;СЧЕТЗ($A:$A);1)​
6. ​Если выделить ячейку,​​ продажах находятся на​​ Диспетчер имен.​Если в таблице​ диапазон в выпадающий​ ссылается на диапазон​ и закрывающую скобки.​ строк (по вертикали).​ стрелками клавиатуры. Щелкните​Автор: Антон Андронов​Книга​Продажи_по_месяцам​
7. ​ Чтобы выделить несколько​​ имена других ячеек​​ языке. Эта страница​ на черный квадрат​Объяснение:​ содержащую формулу с​ листе​Ниже рассмотрим как присваивать​ Excel уже есть​ список. Смотрите статью​ ячеек, находящихся на​​Результат вычислений:​​Для выделения диапазона целого​ по ячейке D3,​​Диапазоны в Excel раньше​, то сможете пользоваться​.​ ячеек или диапазонов,​ или диапазонов в​ переведена автоматически, поэтому​ справа внизу ячейки​

   

8. ​Функция​ именем диапазона, и​​4сезона​​ имя диапазонам. Оказывается,​

​ именованные диапазоны и​ «Выпадающий список в​ еще не созданном​Пример 3. Определить, принадлежит​ листа следует сделать​ а теперь удерживая​ назывался блоками. Диапазон​

​ именем по всей​Нажмите клавишу​ укажите их в​ поле​ ее текст может​ (на рисунке обведен​OFFSET​ нажать клавишу​

• ​(см. файл примера)​ что диапазону ячеек​ нам нужно найти​
• ​ Excel».​ листе, Excel предложит​ ли ячейка заданному​
• ​ щелчок левой кнопкой​
• ​ SHIFT, нажмите клавишу​ – это выделенная​

​ книге Excel (на​Enter​
​ поле​

​имя​

office-guru.ru

Выделение диапазона ячеек в Excel

​ содержать неточности и​ красным цветом), курсор​(СМЕЩ) принимает 5​F2​ в диапазонах:​ можно присвоить имя​ один из них,​

​чтобы вставить, скрытый​ создать лист с​ диапазону ячеек.​ мышки по верхнему​ «стрелка вправо» три​ прямоугольная область прилегающих​ всех листах), а​, и имя будет​

​Ссылка​.​ грамматические ошибки. Для​ будет в виде​ аргументов:​, то соответствующие ячейки​B2:B10 C2:C10 D2:D10 E2:E10​ по разному: используя​ то найти его​ от посторонних взглядов,​ указанным именем и​

​Рассматриваемая функция также позволяет​

1. ​ левому уголку листа,​ раза пока курсор​
2. ​ ячеек. Данное определение​
3. ​ если конкретный лист​ создано.​
4. ​через запятые.​Примечание:​
5. ​ нас важно, чтобы​
6. ​ черного крестика.​

​ссылка:​ будут обведены синей​

​. Формулы поместим соответственно​

Работа с выделенным диапазоном ячеек MS Excel

​ абсолютную или смешанную​ можно несколькими способами.​ текст. Как скрыть​ сохранить книгу.​ определить, принадлежит ли​

• ​ где пересекаются заголовки​
• ​ не переместится на​
• ​ понятия легче воспринять​ – то только​
• ​Если нажать на раскрывающийся​Примечание:​

​ Текущая выделенная ячейка останется​

1. ​ эта статья была​Получилось так.​$A$1​ рамкой (визуальное отображение​ в ячейках​ адресацию.​Первый способ.​ текст, значение ячейки​Если некоторые ячейки, например,​ ячейка выделенной области.​ строк и столбцов.​ ячейку G3. У​ на практических примерах.​
2. ​ в рамках данного​ список поля​ В списке​ выделенной вместе с​ вам полезна. Просим​Можно вставить цифры.​,​ Именованного диапазона).​B11C11 D11E11​Пусть необходимо найти объем​Найти быстро диапазон​
3. ​ в Excel, смотрите​ A1 и B1​ Выполним следующие действия:​ Или нажать комбинацию​ нас выделилась небольшая​В формулах диапазон записывается​ листа. Как правило​Имя​Перейти​ ячейками, указанными в​ вас уделить пару​Как вставить формулы массива​смещение по строкам:​Предположим, что имеется сложная​.​ продаж товаров (см.​ можно в строке​ в статье «Как​ были объединены, при​
4. ​В какой-либо ячейке введем​ горячих клавиш CTRL+A.​ строка. Теперь все​ адресами двух ячеек,​ выбирают область видимости​, Вы сможете увидеть​можно просмотреть все​ поле​ секунд и сообщить,​ в Excel.​0​ (длинная) формула, в​По аналогии с абсолютной​ файл примера лист​ «Имя», рядом со​ скрыть текст в​ выделении полученной ячейки​ часть формулы «=ОБЛАСТИ((»​
5. ​Несмежные диапазоны складываются из​ еще не отпуская​ которые разделенные двоеточием.​ –​ все имена, созданные​ именованные или неименованные​Имя​ помогла ли она​В ячейке К1​,​ которой несколько раз​ адресацией из предыдущей​
6. ​ 1сезон):​ строкой формул.​ ячейке Excel» тут.​ в строке имен​ и выделим произвольную​ нескольких других диапазонов.​

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

Выделение диапазонов целых столбцов или строк

​ пишем формулу =H1*J1​смещение по столбцам:​ используется ссылка на​ задачи, можно, конечно,​Присвоим Имя Продажи диапазону​Нажимаем на стрелку (обведена​Перед тем, как​ будет отображено имя​ область ячеек для​Чтобы их выделять просто​ «стрелка вниз» четыре​

​ правая нижняя ячейка,​.​ книге Excel. В​ которые ранее были​ к диапазонам).​ кнопок внизу страницы.​

Выделение диапазона целого листа

​ Выделяем ячейку с​0​ один и тот​ создать 4 именованных​B2:B10​ на рисунке кругом).​ присвоить имя диапазону​ «A1». Несмотря на​ заполнения аргументов:​

Выделение несмежного диапазона

​ удерживайте нажатие клавиши​ раза, пока курсор​

​ которая входит в​В поле​ нашем случае это​ выделены с помощью​Чтобы выделить неименованный диапазон​ Для удобства также​ формулой (К1) и​,​ же диапазон:​ диапазона с абсолютной​

exceltable.com

Примеры использования функции ОБЛАСТИ для диапазонов Excel

​. При создании имени​Здесь диапазон называется «Диапазон​ или ввести формулу,​ объединение ячеек функция​Поставим пробел и выберем​ CTRL, а дальше​ не перейдет на​ состав диапазона, например​Примечание​

Примеры работы функции ОБЛАСТИ в Excel для работы с диапазонами ячеек

​ всего лишь одно​ команды​ или ссылку на​ приводим ссылку на​

​ нажимаем два раза​высота:​

​=СУММ(E2:E8)+СРЗНАЧ(E2:E8)/5+10/СУММ(E2:E8)​ адресацией, но есть​

​ будем использовать абсолютную​ Excel».​ настроить формат, т.д.,​ с аргументами ((A1;B1))​

​ любую ячейку из​ как при обычном​ G7. Таким образом,​

​ A1:B3.​Вы можете ввести​ имя, которое мы​Перейти​ ячейку, введите ссылку​ оригинал (на английском​ левой мышкой по​

​COUNTA($A:$A)​Если нам потребуется изменить​ решение лучше. С​

​ адресацию.​

Как посчитать количество ссылок на столбцы таблицы Excel

​Второй вариант.​ нужно выделить эти​ все равно вернет​ данного диапазона:​ выделении. Также в​

​ мы выделили блок​

​Обратите внимание! Ячейка, от​ пояснение к создаваемому​ только что создали.​. Чтобы вернуться к​ на нужную ячейку​ языке) .​ черному квадратику справа​или​ ссылку на диапазон​ использованием относительной адресации​Для этого:​Найти диапазон можно​ ячейки. О разных​ значение 2. Эта​Закроем обе скобки и​

​ данной ситуации особенно​

Определение принадлежности ячейки к диапазону таблицы

​ диапазона D3:G7.​ которой начинается выделение​ имени. В ряде​

​В качестве примера, создадим​ ячейке или диапазону,​ или диапазон и​Независимо от наличия определенных​

1. ​ внизу ячейки. Формула​СЧЕТЗ($A:$A)​ данных, то это​ можно ограничиться созданием​выделите, диапазон​
2. ​ в закладке «Формулы»​ способах быстро выделить​ особенность показана на​
3. ​ нажмем Enter. В​ полезным будет режим​Как выделить несмежный диапазон​
4. ​ диапазона, остается активной.​ случаев это делать​ формулу, использующую имя​

​ которые были выделены​ нажмите клавишу ВВОД.​ именованных ячеек или​

​ скопировалась на весь​,​ придется сделать 3​ только​

​B2:B10​ -> «Определенные имена»​ определенные ячейки, диапазон,​ рисунке ниже:​ результате получим:​

Особенности использования функции ОБЛАСТИ в Excel

​ после нажатия клавиши​ ячеек в Excel?​ Это значит, что​ рекомендуется, особенного, когда​Продажи_по_месяцам​

​ раньше, дважды щелкните​

​Совет:​

​Данная ошибка означает, что​

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

Примечание: Именованные диапазоны, созданные из выделенных ячеек, относятся к области на уровне книги.

1. Выберите диапазон, которому вы хотите присвоить имя, включая заголовки строк и столбцов.

2. Щелкните Формулы > Создать из выделенного.

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

4. Нажмите кнопку ОК.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Содержание

• 1 Как в Excel сделать автоматическое изменение диапазона
• 2 Функция СМЕЩ в Excel
• 3 Что определяет функция СЧЕТ
• 4 Динамические диаграммы в Excel
• 5 Манипуляции с именованными областями
  • 5.1 Создание именованного диапазона
  • 5.2 Операции с именованными диапазонами
  • 5.3 Управление именованными диапазонами
  • 5.4 Помогла ли вам эта статья?

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

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

Сформируйте отчет по доходности нашего объекта инвестирования, так как показано на рисунке:

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

1. Выберите инструмент «Формулы»-«Определенные имена»-«Присвоить имя».
2. Заполните поля диалогового окна «Создание имени» как на рисунке. Обратите внимание на то, что в поле «Диапазон:» мы используем функцию =СМЕЩ, а в одном из ее параметров используем функцию =СЧЕТ. Пример: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
3. Переместите курсор в ячейку D2 и введите функцию =СУММ с именем «доход» в ее параметрах.

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

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

Разберем более детально функции, которые мы вводили в поле диапазон при создании динамического имени.

Функция =СМЕЩ определяет наш диапазон в зависимости от количества заполненных ячеек в столбце B. 5 параметров функции =СМЕЩ(начальная ячейка; смещение размера диапазона по строкам; смещение по столбцам; размер диапазона в высоту; размер диапазона в ширину):

1. «Начальная ячейка» – указывает верхнюю левую ячейку, от которой будет динамически расширяться диапазон как вниз, так и вправо (при необходимости).
2. «Смещение по строкам» – параметр определяет, на какое количество нужно смещать диапазон по вертикали от начальной ячейки (первого параметра). Значения могут быть нулевыми и отрицательными.
3. «Смещение по столбцам» – параметр определяет, на какое количество нужно смещать по горизонтали от начальной ячейки. Значения могут быть даже нулевыми и отрицательными.
4. «Размер диапазона в высоту» – количество ячеек, на которое нужно увеличить диапазон в высоту. По сути, название говорит само за себя.
5. «Размер диапазона в ширину» – количество ячеек, на которое нужно увеличить в ширину от начальной ячейки.

Последние 2 параметра функции являются необязательными. Если их не заполнять, то диапазон будет состоять из 1-ой ячейки. Например: =СМЕЩ(A1;0;0) – это просто ячейка A1, а параметр =СМЕЩ(A1;2;0) ссылается на A3.

Теперь разберем функцию: =СЧЕТ, которую мы указывали в 4-ом параметре функции: =СМЕЩ.

Что определяет функция СЧЕТ

Функция =СЧЕТ($B:$B) автоматически считает количество заполненных ячеек в столбце B.

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

Читать данную формулу следует так: первый параметры указывает на то, что наш автоматически изменяемый диапазон начинается в ячейке B2. Следующие два параметра имеют значения 0;0 – это значит, что динамический диапазон не смещается относительно начальной ячейки B2. А увеличивается только его размер по вертикали, о чем свидетельствует 4-тый параметр. В нем находится функция СЧЕТ и она возвращает число равно количеству заполненных ячеек в столбце B. Соответственно количество ячеек по вертикали в диапазоне будет равно числу, которое нам даст функция СЧЕТ. А за ширину диапазона у нас отвечает последний 5-тый параметр, где находиться число 1.

Благодаря функции СЧЕТ мы рационально загружаем в память только заполненные ячейки из столбца B, а не весь столбец целиком. Данный факт исключает возможные ошибки связанные с памятью при работе с данным документом.

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

У нас есть динамическое имя, теперь создадим динамическую диаграмму для данного типа отчета:

1. Выделите диапазон B2:B6 и выберите инструмент: «Вставка»-«Диаграммы»-«Гистограмма»-«Гистограмма с группировкой».
2. Щелкните левой кнопкой мышки по любому столбцу гистограммы и в строке формул отобразится функция диаграммы: =РЯД().
3. В строке формул измените параметры функции: =РЯД(Лист1!$B$1;;Лист1!$B$2:$B$7;1) на =РЯД(Лист1!$B$1;;Лист1!доход;1).
4. В отчет добавьте новую запись в ячейки A8 — «июль», B8 — «77000» соответственно. И убедитесь, что на диаграмме автоматически добавился новый столбец.

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

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

Манипуляции с именованными областями

Именованный диапазон — это область ячеек, которой пользователем присвоено определенное название. При этом данное наименование расценивается Excel, как адрес указанной области. Оно может использоваться в составе формул и аргументов функций, а также в специализированных инструментах Excel, например, «Проверка вводимых значений».

Существуют обязательные требования к наименованию группы ячеек:

• В нём не должно быть пробелов;
• Оно обязательно должно начинаться с буквы;
• Его длина не должна быть больше 255 символов;
• Оно не должно быть представлено координатами вида A1 или R1C1;
• В книге не должно быть одинаковых имен.

Наименование области ячеек можно увидеть при её выделении в поле имен, которое размещено слева от строки формул.

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

Создание именованного диапазона

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

1. Самый быстрый и простой вариант присвоения названия массиву – это записать его в поле имен после выделения соответствующей области. Итак, выделяем массив и вводим в поле то название, которое считаем нужным. Желательно, чтобы оно легко запоминалось и отвечало содержимому ячеек. И, безусловно, необходимо, чтобы оно отвечало обязательным требованиям, которые были изложены выше.
2. Для того, чтобы программа внесла данное название в собственный реестр и запомнила его, жмем по клавише Enter. Название будет присвоено выделенной области ячеек.

Выше был назван самый быстрый вариант наделения наименованием массива, но он далеко не единственный. Эту процедуру можно произвести также через контекстное меню

1. Выделяем массив, над которым требуется выполнить операцию. Клацаем по выделению правой кнопкой мыши. В открывшемся списке останавливаем выбор на варианте «Присвоить имя…».
2. Открывается окошко создания названия. В область «Имя» следует вбить наименование в соответствии с озвученными выше условиями. В области «Диапазон» отображается адрес выделенного массива. Если вы провели выделение верно, то вносить изменения в эту область не нужно. Жмем по кнопке «OK».
3. Как можно видеть в поле имён, название области присвоено успешно.

Ещё один вариант выполнения указанной задачи предусматривает использование инструментов на ленте.

1. Выделяем область ячеек, которую требуется преобразовать в именованную. Передвигаемся во вкладку «Формулы». В группе «Определенные имена» производим клик по значку «Присвоить имя».
2. Открывается точно такое же окно присвоения названия, как и при использовании предыдущего варианта. Все дальнейшие операции выполняются абсолютно аналогично.

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

1. Выделяем массив. На вкладке «Формулы», клацаем по крупному значку «Диспетчер имен», расположенному всё в той же группе «Определенные имена». Или же можно вместо этого применить нажатие сочетания клавиш Ctrl+F3.
2. Активируется окно Диспетчера имён. В нем следует нажать на кнопку «Создать…» в верхнем левом углу.
3. Затем запускается уже знакомое окошко создания файлов, где нужно провести те манипуляции, о которых шёл разговор выше. То имя, которое будет присвоено массиву, отобразится в Диспетчере. Его можно будет закрыть, нажав на стандартную кнопку закрытия в правом верхнем углу.

Урок: Как присвоить название ячейке в Экселе

Операции с именованными диапазонами

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

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

1. Прежде всего, на листе со списком присваиваем диапазону наименование любым из тех способов, о которых шла речь выше. В итоге, при выделении перечня в поле имён у нас должно отображаться наименование данного массива. Пусть это будет наименование «Модели».
2. После этого перемещаемся на лист, где находится таблица, в которой нам предстоит создать выпадающий список. Выделяем область в таблице, в которую планируем внедрить выпадающий список. Перемещаемся во вкладку «Данные» и щелкаем по кнопке «Проверка данных» в блоке инструментов «Работа с данными» на ленте.
3. В запустившемся окне проверки данных переходим во вкладку «Параметры». В поле «Тип данных» выбираем значение «Список». В поле «Источник» в обычном случае нужно либо вручную вписать все элементы будущего выпадающего списка, либо дать ссылку на их перечень, если он расположен в документе. Это не очень удобно, особенно, если перечень располагается на другом листе. Но в нашем случае все намного проще, так как мы соответствующему массиву присвоили наименование. Поэтому просто ставим знак «равно» и записываем это название в поле. Получается следующее выражение:

   =Модели

   Жмем по «OK».

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

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

Итак, мы имеем таблицу, в которой помесячно расписана выручка пяти филиалов предприятия. Нам нужно узнать общую выручку по Филиалу 1, Филиалу 3 и Филиалу 5 за весь период, указанный в таблице.

1. Прежде всего, каждой строке соответствующего филиала в таблице присвоим название. Для Филиала 1 выделяем область с ячейками, в которых содержатся данные о выручке по нему за 3 месяца. После выделения в поле имен пишем наименование «Филиал_1» (не забываем, что название не может содержать пробел) и щелкаем по клавише Enter. Наименование соответствующей области будет присвоено. При желании можно использовать любой другой вариант присвоения наименования, о котором шел разговор выше.
2. Таким же образом, выделяя соответствующие области, даем названия строкам и других филиалов: «Филиал_2», «Филиал_3», «Филиал_4», «Филиал_5».
3. Выделяем элемент листа, в который будет выводиться итог суммирования. Клацаем по иконке «Вставить функцию».
4. Инициируется запуск Мастера функций. Производим перемещение в блок «Математические». Останавливаем выбор из перечня доступных операторов на наименовании «СУММ».
5. Происходит активация окошка аргументов оператора СУММ. Данная функция, входящая в группу математических операторов, специально предназначена для суммирования числовых значений. Синтаксис представлен следующей формулой:

   =СУММ(число1;число2;…)

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

   Всего оператор СУММ может насчитывать от одного до 255 аргументов. Но в нашем случае понадобится всего три аргумента, так как мы будет производить сложение трёх диапазонов: «Филиал_1», «Филиал_3» и «Филиал_5».

   Итак, устанавливаем курсор в поле «Число1». Так как мы дали названия диапазонам, которые требуется сложить, то не нужно ни вписывать координаты в поле, ни выделять соответствующие области на листе. Достаточно просто указать название массива, который подлежит сложению: «Филиал_1». В поля «Число2» и «Число3» соответственно вносим запись «Филиал_3» и «Филиал_5». После того, как вышеуказанные манипуляции были сделаны, клацаем по «OK».

6. Результат вычисления выведен в ячейку, которая была выделена перед переходом в Мастер функций.

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

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

Урок: Как посчитать сумму в Майкрософт Эксель

Управление именованными диапазонами

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

1. Чтобы перейти в Диспетчер, перемещаемся во вкладку «Формулы». Там следует кликнуть по иконке, которая так и называется «Диспетчер имен». Указанная иконка располагается в группе «Определенные имена».
2. После перехода в Диспетчер для того, чтобы произвести необходимую манипуляцию с диапазоном, требуется найти его название в списке. Если перечень элементов не очень обширный, то сделать это довольно просто. Но если в текущей книге располагается несколько десятков именованных массивов или больше, то для облегчения задачи есть смысл воспользоваться фильтром. Клацаем по кнопке «Фильтр», размещенной в правом верхнем углу окна. Фильтрацию можно выполнять по следующим направлениям, выбрав соответствующий пункт открывшегося меню:
   • Имена на листе;
   • в книге;
   • с ошибками;
   • без ошибок;
   • Определенные имена;
   • Имена таблиц.

   Для того, чтобы вернутся к полному перечню наименований, достаточно выбрать вариант «Очистить фильтр».

3. Для изменения границ, названия или других свойств именованного диапазона следует выделить нужный элемент в Диспетчере и нажать на кнопку «Изменить…».
4. Открывается окно изменение названия. Оно содержит в себе точно такие же поля, что и окно создания именованного диапазона, о котором мы говорили ранее. Только на этот раз поля будут заполнены данными.

   В поле «Имя» можно сменить наименование области. В поле «Примечание» можно добавить или отредактировать существующее примечание. В поле «Диапазон» можно поменять адрес именованного массива. Существует возможность сделать, как применив ручное введение требуемых координат, так и установив курсор в поле и выделив соответствующий массив ячеек на листе. Его адрес тут же отобразится в поле. Единственное поле, значения в котором невозможно отредактировать – «Область».

   После того, как редактирование данных окончено, жмем на кнопку «OK».

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

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

1. Чтобы провести процедуру удаления, выделяем нужный элемент из перечня и жмем на кнопку «Удалить».
2. После этого запускается диалоговое окно, которое просит подтвердить свою решимость удалить выбранный элемент. Это сделано во избежание того, чтобы пользователь по ошибке не выполнил данную процедуру. Итак, если вы уверены в необходимости удаления, то требуется щелкнуть по кнопке «OK» в окошке подтверждения. В обратном случае жмите по кнопке «Отмена».
3. Как видим, выбранный элемент был удален из перечня Диспетчера. Это означает, что массив, к которому он был прикреплен, утратил наименование. Теперь он будет идентифицироваться только по координатам. После того, как все манипуляции в Диспетчере завершены, клацаем по кнопке «Закрыть», чтобы завершить работу в окне.

Применение именованного диапазона способно облегчить работу с формулами, функциями и другими инструментами Excel. Самими именованными элементами можно управлять (изменять и удалять) при помощи специального встроенного Диспетчера.

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

• Ячейки, строки, столбцы
• Примеры диапазона
• Заполнение диапазона
• Перемещение диапазона
• Копировать/вставить диапазон
• Вставка строки, столбца

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

Ячейки, строки, столбцы

Давайте начнем с выбора ячеек, строк и столбцов.

1. Для выбора ячейки C3 кликните по полю на пересечении столбца C и строки .
2. Чтобы выбрать столбец C, кликните по заголовку столбца C.
3. Чтобы выбрать строку , кликните по заголовку строки .

Примеры диапазона

Диапазон представляет собой набор из двух и более ячеек.

1. Для выбора диапазона B2:C4 кликните по нижнему правому углу ячейки В2 и протяните указатель мыши до ячейки C4.
2. Чтобы выделить диапазон, состоящий из отдельных (несмежных) ячеек, зажмите клавишу Ctrl и кликните по каждой ячейке, которую хотите включить в диапазон.

Заполнение диапазона

Чтобы заполнить диапазон, следуйте инструкции ниже:

1. Введите значение 2 в ячейку B2.
2. Выделите ячейку В2, зажмите её нижний правый угол и протяните вниз до ячейки В8.

   Результат:

   Эта техника протаскивания очень важна, вы будете часто использовать её в Excel. Вот еще один пример:

3. Введите значение 2 в ячейку В2 и значение 4 в ячейку B3.
4. Выделите ячейки B2 и B3, зажмите нижний правый угол этого диапазона и протяните его вниз.

   Excel автоматически заполняет диапазон, основываясь на шаблоне из первых двух значений. Классно, не правда ли? Вот еще один пример:

5. Введите дату 13/6/2013 в ячейку В2 и дату 16/6/2013 в ячейку B3 (на рисунке приведены американские аналоги дат).
6. Выделите ячейки B2 и B3, зажмите нижний правый угол этого диапазона и протяните его вниз.

Перемещение диапазона

Чтобы переместить диапазон, выполните следующие действия:

1. Выделите диапазон и зажмите его границу.
2. Перетащите диапазон на новое место.

Копировать/вставить диапазон

Чтобы скопировать и вставить диапазон, сделайте следующее:

1. Выделите диапазон, кликните по нему правой кнопкой мыши и нажмите Copy (Копировать) или сочетание клавиш Ctrl+C.
2. Выделите ячейку, где вы хотите разместить первую ячейку скопированного диапазона, кликните правой кнопкой мыши и выберите команду Paste (Вставить) в разделе Paste Options (Параметры вставки) или нажмите сочетание клавиш Ctrl+V.

Вставка строки, столбца

Чтобы вставить строку между значениями 20 и 40, как на рисунке ниже, сделайте следующее:

1. Выделите строку .
2. Кликните по ней правой кнопкой мыши и выберите команду Insert (Вставить).

   Результат:

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевела: Ольга Гелих

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

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

Смежные (простые) диапазоны Excel

Для простоты будем называть их просто диапазонами. Они могут состоять из

• Одной или нескольких ячеек Excel
• Одной или нескольких строк
• Одного или нескольких столбцов
• Всех ячеек листа Excel

Как задаются диапазоны

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

Адрес диапазона, показанного на рисунке

B3:F8

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

Можно ввести адрес в Поле адреса и нажать Enter, будет выделен указанный диапазон.

После нажатия клавиши Enter

Задание в формуле:

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

2:3  — Строки 2 и 3

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

Можно ввести адрес (номера первой и последней строк, разделенные двоеточием) в Поле адреса и нажать Enter.

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

C:D  — Столбцы C и D

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

Можно ввести адрес в Поле адреса и нажать Enter, будет выделен указанный диапазон.

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

Несмежные (составные) диапазоны Excel

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

A1:D3; C5:F8  — Два прямоугольных блока ячеек

2:3; C5:F8  — Строки 2,3 и прямоугольный блок ячеек

Допускается наложение составных частей

2:6; C5:F8  — Строки 2-6 и прямоугольный блок ячеек

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

С помощью мыши несмежный диапазон можно задать последовательно выделяя смежные диапазоны при нажатой клавише Ctrl.

Второй способ. Нажать Shift+F8. После этого можно последовательно выделять смежные диапазоны. Чтобы выйти из этого режима надо нажать Esc.

Сходство и различие диапазонов

Использование в формулах Excel

Смежные и несмежные диапазоны можно использовать в формулах.

Объединение ячеек

К смежному диапазону всегда можно применить операцию объединить ячейки. Даже в том случае, когда он состоит из нескольких строк или столбцов. Объединенная ячейка получает адрес и содержимое первой ячейки исходного диапазона.

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

Копирование и перетаскивание

Смежный диапазон можно копировать и перетаскивать мышью.

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

Несмежный диапазон нельзя перетаскивать мышью.

Операция пересечения в Excel

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

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

Пример 1: Пересечение строк 3 и 4 со столбцами C и D

3:4 C:D

=СУММ(3:4 C:D)

Видим, что результат суммирования равен 4. Просуммировались только ячейки стоящие на пересечении строк 3, 4 и столбцов C, D.

Пример 2: Пересечение диапазона B3:D8 с диапазоном C6:F10.

B3:D8 C6:F10

=СУММ(B3:D8 C6:F10)

Видим, что результат суммирования равен 6. Просуммировались только ячейки стоящие на пересечении блоков.

По теме страницы

Карта сайта — Подробное оглавление сайта.