Диапазон в программе excel это

Обычно ссылки на диапазоны ячеек вводятся непосредственно в формулы, например =СУММ(А1:А10) . Другим подходом является использование в качестве ссылки имени диапазона. В статье рассмотрим какие преимущества дает использование имени.

Назовем Именованным диапазоном в MS EXCEL, диапазон ячеек, которому присвоено Имя (советуем перед прочтением этой статьи ознакомиться с правилами создания Имен ).

Преимуществом именованного диапазона является его информативность. Сравним две записи одной формулы для суммирования, например, объемов продаж: =СУММ($B$2:$B$10) и =СУММ(Продажи) . Хотя формулы вернут один и тот же результат (если, конечно, диапазону B2:B10 присвоено имя Продажи ), но иногда проще работать не напрямую с диапазонами, а с их именами.

Совет : Узнать на какой диапазон ячеек ссылается Имя можно через Диспетчер имен расположенный в меню Формулы/ Определенные имена/ Диспетчер имен .

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

Как изменить имя ячейки в Excel, а также что такое именованный диапазон и как с ним работать

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

Пусть необходимо найти объем продаж товаров (см. файл примера лист 1сезон ):

Присвоим Имя Продажи диапазону B2:B10 . При создании имени будем использовать абсолютную адресацию .

• выделите, диапазон B2:B10 на листе 1сезон ;
• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
• в поле Имя введите: Продажи ;
• в поле Область выберите лист 1сезон (имя будет работать только на этом листе) или оставьте значение Книга , чтобы имя было доступно на любом листе книги;
• убедитесь, что в поле Диапазон введена формула =’1сезон’!$B$2:$B$10
• нажмите ОК.

Теперь в любой ячейке листа 1сезон можно написать формулу в простом и наглядном виде: =СУММ(Продажи) . Будет выведена сумма значений из диапазона B2:B10 .

Также можно, например, подсчитать среднее значение продаж, записав =СРЗНАЧ(Продажи) .

Обратите внимание, что EXCEL при создании имени использовал абсолютную адресацию $B$1:$B$10 . Абсолютная ссылка жестко фиксирует диапазон суммирования: в какой ячейке на листе Вы бы не написали формулу =СУММ(Продажи) – суммирование будет производиться по одному и тому же диапазону B1:B10 .

Иногда выгодно использовать не абсолютную, а относительную ссылку, об этом ниже.

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

Теперь найдем сумму продаж товаров в четырех сезонах. Данные о продажах находятся на листе 4сезона (см. файл примера ) в диапазонах: B2:B10 , C 2: C 10 , D 2: D 10 , E2:E10 . Формулы поместим соответственно в ячейках B11 , C 11 , D 11 , E 11 .

создание диапазона ячеек в EXCEL

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

• выделите ячейку B11 , в которой будет находится формула суммирования (при использовании относительной адресации важно четко фиксировать нахождение активной ячейки в момент создания имени );
• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя ;
• в поле Имя введите: Сезонные_Продажи ;
• в поле Область выберите лист 4сезона (имя будет работать только на этом листе);
• убедитесь, что в поле Диапазон введена формула =’4сезона’!B$2:B$10
• нажмите ОК.

Диапазон ячеек Excel

Начнем с самого простого: дайте ячейке имя. Для этого просто выделите ее (1) и в поле имени (2) вместо адреса ячейки введите любое легко запоминающееся имя.

Именная ячейка C7

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

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

Использование имени ячейки

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

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

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

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

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

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

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

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

1. В ячейку B2 введите значение 2.
2. Выделите ячейку B2, зажмите ее правый нижний угол и перетащите вниз к ячейке B8. Результат: Эта техника перетаскивания очень важна, вы будете часто использовать ее в Excel. Вот еще один пример:
3. Введите значение 2 в ячейку B2 и значение 4 в ячейку B3.
4. Выделите ячейки B2 и B3, зажмите правый нижний угол этого диапазона и перетащите его вниз. Excel автоматически заполнит диапазон на основе формулы из первых двух значений. Довольно круто, правда? Вот еще один пример:
5. Введите дату 13/6/2013 в ячейку B2 и дату 16/6/2013 в ячейку B3 (на рисунке показан американский эквивалент этих дат).
6. Выделите ячейки B2 и B3, зажмите правый нижний угол этого диапазона и перетащите его вниз.

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

Диапазон ячеек можно назвать аналогичным образом, т.е. выделить диапазон (1) и ввести его имя в поле имени (2):

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

Затем это имя можно использовать в формулах, например, для вычисления суммы:

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

Именованный диапазон также можно создать на вкладке Формулы, выбрав инструмент Задать имя.

Создание именованного диапазона с помощью панели инструментов

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

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

Для работы с существующими диапазонами на вкладке Формулы есть Менеджер имен.

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

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

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

Задача

У вас есть таблица продаж некоторых товаров по месяцам (см. файл-образец ):

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

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

Для генерации динамических диапазонов мы будем использовать функцию HUMMING(), которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Вы можете указать высоту и ширину диапазона, а также смещение по строкам и столбцам.

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

Для создания динамического диапазона:

• на вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
• В поле Имя введите: Месяц ;
• В поле Область выберите Книжный лист ;
• В поле Range введите формулу =MEMBERSHIP(sheet1!$B$5;;;1;ACCOUNT(sheet1!$B$5:$I$5)).
• Нажмите OK.

Теперь подробнее. Любой диапазон в EXCEL определяется координатами верхней левой и нижней правой ячеек диапазона. Начальной ячейкой, от которой рассчитывается положение нашего динамического диапазона, является ячейка B5 . Если аргументы offset_by_rows, offset_by_columns не заданы (как в нашем случае), то эта ячейка является верхней левой ячейкой диапазона. Правая нижняя ячейка диапазона задается аргументами height и width . В нашем случае значение высоты =1, а значение ширины диапазона равно результату расчета формулы SCHOTZ(sheet1!$B$5:$I$5), который равен 4 (строка 5 содержит 4 месяца с января по апрель). Итак, адрес правой нижней ячейки нашего динамического диапазона определен — это E 5 .

Когда вы заполните таблицу данными о продажах за май, июнь и так далее, формула READ(sheet1!$B$5:$I$5) вернет количество заполненных ячеек (количество названий месяцев) и таким образом определит новую ширину динамического диапазона, который в свою очередь создаст выпадающий список.

ПРИМЕЧАНИЕ: При использовании функции SCRETZ() убедитесь, что нет пустых ячеек! Т.е. вы должны заполнить список месяцами без пробелов.

Теперь создадим еще один динамический диапазон для подведения итогов продаж.

Для создания динамического диапазона :

• На вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
• В поле Имя введите: Продажи_в_месяц;
• В поле Диапазон введите формулу = AMOUNT(worksheet1!$A$6;;SCHEDULE(worksheet1!$C$1;worksheet1!$B$5:$I$5;0);12).
• нажмите OK.

Функция ПОИСКПОЗ() ищет в строке 5 (список месяцев) месяц, выбранный пользователем (ячейка C1 с выпадающим списком), и возвращает соответствующий номер элемента из диапазона поиска (названия месяцев должны быть уникальными, т.е. этот пример не подходит для нескольких лет). Левый верхний угол нашего динамического диапазона (начиная с ячейки A6) перемещается на это количество столбцов, высота диапазона остается фиксированной — 12 (при желании вы можете сделать ее динамической, в зависимости от количества товаров в диапазоне).

И, наконец, если вы введете формулу = SUMM(Sales_over_month) в ячейку C2, вы получите сумму продаж в выбранном месяце.

Например, в мае месяце.

Или, например, в апреле месяце.

Примечание: Вместо формулы SMUM() можно использовать формулу INDEX() : = $B$5:INDEX(B5:I5;AMOUNT($B$5:$I$5)) для расчета количества завершенных месяцев.

Формула подсчитывает количество элементов в строке 5 (SCRUTZ() ) и определяет ссылку на последний элемент в строке (INDEX() ), таким образом возвращая ссылку на диапазон B5:E5 .

Визуальное отображение динамического диапазона

Текущий динамический диапазон можно выделить с помощью условного форматирования . В файле примера правило условного форматирования применяется к ячейкам диапазона B6:I14 с помощью формулы: = столбец(B6)= столбец(Продажи_в_месяц)

Условное форматирование автоматически выделило серым цветом продажи текущего месяца, который был выбран в выпадающем списке.

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

Пример 2. Определите количество столбцов в таблице и введите это значение в ячейку A16.

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

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

Статьи по теме:

• Как задать диапазон в Excel
• Как в Excel переименовать столбец
• Как выбрать максимальное значение из таблицы

Вам понадобится

• — табличный редактор Microsoft Office Excel.

Инструкция

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

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

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

Можно вписать диапазон ячеек «вручную», то есть не использовать возможность табличного редактора автоматически определять и преобразовывать в соответствующую запись выделенную с помощью мыши область. Для этого, включив режим редактирование содержимого ячейки с формулой (F2), установите курсор в то ее место, куда должно быть помещено указание диапазона. Затем введите ссылку на первую (левую верхнюю) его ячейку, поставьте двоеточие и введите ссылку на последнюю (правую нижнюю) ячейку.

Обычно ссылка содержит одну или две буквы латинского алфавита (указывает колонку) и число (указывает строку). Однако если в настройках задан другой стиль ссылок, то обе ее части будут числами, но перед номером столбца надо будет ставить литеру C (это английская буква), а перед номером строки — R. Для ссылки на все ячейки строки или колонки указывать оба параметра не требуется — например, всю колонку D можно обозначить записью D:D.

Совет полезен?
Статьи по теме:

• Как в Excel сделать нумерацию
• Как разбить ячейки в экселе
• Как в Excel выделить ячейки

Добавить комментарий к статье
Похожие советы

• Как в Excel пронумеровать ячейки
• Как в таблице Excel автоматически рассортировать фамилии по алфавиту
• Как в Excel прибавить ячейки
• Как в Excel найти совпадения
• Как отобразить ячейки в Excel
• Как нумеровать таблицы
• Как в excel построить зависимость
• Как сделать выпадающий список в экселе
• Как разделить в экселе
• Как построить таблицу в экселе
• Как нарисовать таблицу в Экселе
• Как суммировать ячейки
• Как изменить параметры в Excel
• Как в Excel поменять столбцы
• Как в excel добавить столбец
• Как изменить размер ячейки
• Как выделить столбец
• Как в Excel пронумеровать
• Как посчитать сумму столбца в Excel
• Как отсортировать таблицу
• Как задать размер таблицы
• Как в Excel скрыть строки
• Как в Excel перемножить столбец

Источник: www.kakprosto.ru