Что такое диапазон и как он оформляется в ms excel

Диапазон Excel. Выделение, сравнение, изменение диапазонов значений

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

Что такое диапазон ячеек в Excel?

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

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

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

Что такое именованный диапазон ячеек в Excel?

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

По умолчанию имена диапазонов ячеек автоматически считаются абсолютными ссылками.

Для имен действует ряд ограничений:

— имя может содержать до 255 символов;

— первым символом в имени должна быть буква, знак подчеркивания (_) либо обратная косая черта (\), остальные символы имени могутбыть буквами, цифрами, точками и знаками подчеркивания;

— имена не могут быть такими же, как ссылки на ячейки;

— пробелы в именах не допускаются;

— строчные и прописные буквы не различаются.

Управление существующими именованными диапазонами (создание, просмотр и изменение) можно осуществлять при помощи диспетчера имен. В Excel 2007 диспетчер находится на вкладке "Формулы", в группе кнопок "Определенные имена".

Основные действия с диапазонами

Выделение диапазонов

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

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

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

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

Сравнение диапазонов

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

Например, для построчного сравнения часто используется логическая функция "ЕСЛИ" и какой-либо из операторов сравнения (также можно использовать и другие функции, например "СЧЕТЕСЛИ" из категории статистические для проверки вхождения элементов одного списка в другой).

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

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

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

Изменение (преобразование) диапазонов значений

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

Транспонирование можно осуществить при помощи функции "=ТРАНСП(Диапазон)", которая находится в категории "Ссылки и массивы". Есть и другой способ — копирование диапазона значений с последующей специальной вставкой, при которой ставится флажок в поле "Транспонировать".

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

Надстройка для работы с диапазонами в Excel

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

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

history 21 января 2013 г.
    Группы статей

  • Имена

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

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

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

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

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

Задача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 .

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

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

Мы использовали смешанную адресацию B$2:B$10 (без знака $ перед названием столбца). Такая адресация позволяет суммировать значения находящиеся в строках 2 , 3 ,… 10 , в том столбце, в котором размещена формула суммирования. Формулу суммирования можно разместить в любой строке ниже десятой (иначе возникнет циклическая ссылка).

Теперь введем формулу =СУММ(Сезонные_Продажи) в ячейку B11. Затем, с помощью Маркера заполнения , скопируем ее в ячейки С11 , D 11 , E 11 , и получим суммы продаж в каждом из 4-х сезонов. Формула в ячейках B 11, С11 , D 11 и E 11 одна и та же!

СОВЕТ: Если выделить ячейку, содержащую формулу с именем диапазона, и нажать клавишу F2 , то соответствующие ячейки будут обведены синей рамкой (визуальное отображение Именованного диапазона ).

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

Предположим, что имеется сложная (длинная) формула, в которой несколько раз используется ссылка на один и тот же диапазон:

Если нам потребуется изменить ссылку на диапазон данных, то это придется сделать 3 раза. Например, ссылку E2:E8 поменять на J14:J20 .

Но, если перед составлением сложной формулы мы присвоим диапазону E2:E8 какое-нибудь имя (например, Цены ), то ссылку на диапазон придется менять только 1 раз и даже не в формуле, а в Диспетчере имен !

Более того, при создании формул EXCEL будет сам подсказывать имя диапазона! Для этого достаточно ввести первую букву его имени.

Excel добавит к именам формул, начинающихся на эту букву, еще и имя диапазона!

Ссылка на основную публикацию