Что такое диапазон в Excel. - EXCEL-OFFICE.RU. Диапазон в excel


Диапазон в Excel. - EXCEL-OFFICE.RU

gototopgototop Диапазон таблицы Excel. Диапазон в Excel.       Диапазон в Excel - это несколько ячеек таблицы. Диапазон ячеек можно просто выделить, чтобы настроить формат, цвет ячеек, написать формулу, т.д. Можно присвоить имя этому диапазону, чтобы использовать его в формулах, условном форматировании, поиске, т.д. - это именованный диапазон Excel. В диапазон можно выделить всю таблицу и присвоить ей имя.        Подробнее о применении диапазона в Excel, смотрите в статье "Что такое диапазон в Excel".С помощью диапазона можно защитить ячейки. Читайте в статье "Пароль на Excel. Защита Excel" тут.       В Excel можно выделить как смежные ячейки (расположенные рядом друг с другом), так и не смежные ячейки (расположены не рядом).       Чтобы быстро найти определенные ячейки, их можно объединить в диапазоны, присвоить имя диапазонам, сделать закладку на определенную часть таблицы. И, затем, при необходимости выбрать нужный диапазон, закладку. Как сделать закладки в таблице, читайте в статье "Сделать закладки в таблице Excel".      Как выделить не смежные ячейки в Excel.Если ячейки расположены не рядом, то выделяем первую ячейку будущего диапазона. Затем нажимаем клавишу «Ctrl» и, удерживая её, нажимаем на остальные ячейки. Отпускаем клавишу «Ctrl».      О других способах выделения ячеек, столбцов, строк, листов, т.д, читайте в статье "Как выделить в Excel ячейки, таблицу, др.".      Присвоить имя диапазону в Excel.      Нажимаем на выделенный диапазон правой мышью, выбираем из контекстного меню «Имя диапазона». В вышедшем диалоговом окне пишем имя диапазона. Мы написали - "январь".     Первый символ имени диапазона должен быть буквой или символ подчерквания. Затем можно писать и буквы, и цифры, и подчеркивание. Длина названия диапазона не должна превышать 225 символов. Если в имени диапазона больше обного слова, то соединяем слова знаком нижнего тире так. Например: "Число_месяцев". Пропусков в имени не должно быть.     В  строке "Область" указываем область, на которую будет распространяться это имя. Например, на всю книгу (на все её листы), или только на этот лист, т.д.     В строке "Примечание" можно описать этот диапазон, что в нем, т.д.     В строке "Диапазон" указываем адрес диапазона. Если диапазон находится на другом листе книги, то указываем название листа. Нажимаем «ОК».
    Другой вариант присвоить имя диапазону Excel – это на закладке «Формулы» в разделе «Определенные имена» нажимаем на кнопку «Присвоить имя».     Как удалить диапазон Excel. Заходим на закладку «Формулы» -> «Определенные имена» -> «Диспетчер имен». Из списка выделяем диапазон, который хотим удалить, нажимаем кнопку вверху окна «Удалить».  Здесь же можно изменить имя и состав диапазона.                     Найти диапазон в Excel можно через кнопку в строке адреса ячейки.
      Второй вариант, как найти диапазон в Excel - на закладке Формулы" в разделе "Определенные имена" нажимаем на кнопку "Диспетчер имен". В появившемся окне нажимаем на нужный диапазон. Адрес этого диапазоно появится в строке диалогового окна в строке "Диапазон". Нажимаем на это адрес и этот диапазон в таблице будет выделен пунктирными линиями.       Диапазон может пригодиться еще в том случае, когда нужно найти скрытый текст в таблице. Подробнее об этом, читайте в статье "Как найти скрытый текст в Excel".      В диапазон ячеек можно вставить формулу массива. Что это за формулы, где применяются, смотрите в статье "Формулы массива Excel".      С помощью имени диапазона легко найти и очистить ячейки таблицы. Например, таблица в Excel "Домашний, семейный бюджет в Excel" здесь. Заполняли год. Затем копируем эту таблицу для следующего года. В скопированной таблице по имени диапазона можно сразу очистить все ячейки таблицы (смежные и не смежные), не задевая формул.     О том, как создать таблицу в Excel, читайте в статье "Как сделать таблицу в Excel" тут.Таблицу Excel можно быстро заполнить данными. Смотрите статью "Как заполнить таблицу в Excel повторяющимися данными".

www.excel-office.ru

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

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

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

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

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

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

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

Имя диапазона строке имен в Microsoft Excel

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

Имя диапазону не присвоено в Microsoft Excel

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

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

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

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

Наименование диапазону через поле имен присвоено в Microsoft Excel

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

  1. Выделяем массив, над которым требуется выполнить операцию. Клацаем по выделению правой кнопкой мыши. В открывшемся списке останавливаем выбор на варианте «Присвоить имя…».
  2. Переход к присвоению имени диапазону ячеек через контекстное меню в Microsoft Excel

  3. Открывается окошко создания названия. В область «Имя» следует вбить наименование в соответствии с озвученными выше условиями. В области «Диапазон» отображается адрес выделенного массива. Если вы провели выделение верно, то вносить изменения в эту область не нужно. Жмем по кнопке «OK».
  4. Окно создания имени в Microsoft Excel

  5. Как можно видеть в поле имён, название области присвоено успешно.

Наименование диапазону через контекстное меню присвоено в Microsoft Excel

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

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

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

Окно создания имени в программе Microsoft Excel

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

  1. Выделяем массив. На вкладке «Формулы», клацаем по крупному значку «Диспетчер имен», расположенному всё в той же группе «Определенные имена». Или же можно вместо этого применить нажатие сочетания клавиш Ctrl+F3.
  2. Переход в Диспетчер имен в Microsoft Excel

  3. Активируется окно Диспетчера имён. В нем следует нажать на кнопку «Создать…» в верхнем левом углу.
  4. Переход к созданию имени в Диспетчере имён в Microsoft Excel

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

Закрытие окна Диспетчера имён в Microsoft Excel

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

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

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

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

  1. Прежде всего, на листе со списком присваиваем диапазону наименование любым из тех способов, о которых шла речь выше. В итоге, при выделении перечня в поле имён у нас должно отображаться наименование данного массива. Пусть это будет наименование «Модели».
  2. Наименование диапазону моделей присвоено в Microsoft Excel

  3. После этого перемещаемся на лист, где находится таблица, в которой нам предстоит создать выпадающий список. Выделяем область в таблице, в которую планируем внедрить выпадающий список. Перемещаемся во вкладку «Данные» и щелкаем по кнопке «Проверка данных» в блоке инструментов «Работа с данными» на ленте.
  4. Переход в окно проверки данных в Microsoft Excel

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

    =Модели

    Жмем по «OK».

  6. Окно проверки вводимых значений в Microsoft Excel

  7. Теперь при наведении курсора на любую ячейку диапазона, к которой мы применили проверку данных, справа от неё появляется треугольник. При нажатии на этот треугольник открывается список вводимых данных, который подтягивается из перечня на другом листе.
  8. Выпадающий список в Microsoft Excel

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

Значение из выпадающего списка выбрано в Microsoft Excel

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

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

Таблица выручки по филиалам предприятия в Microsoft Excel

  1. Прежде всего, каждой строке соответствующего филиала в таблице присвоим название. Для Филиала 1 выделяем область с ячейками, в которых содержатся данные о выручке по нему за 3 месяца. После выделения в поле имен пишем наименование «Филиал_1» (не забываем, что название не может содержать пробел) и щелкаем по клавише Enter. Наименование соответствующей области будет присвоено. При желании можно использовать любой другой вариант присвоения наименования, о котором шел разговор выше.
  2. Имя диапазону Филиал 1 присвоено в Microsoft Excel

  3. Таким же образом, выделяя соответствующие области, даем названия строкам и других филиалов: «Филиал_2», «Филиал_3», «Филиал_4», «Филиал_5».
  4. Имя всем диапазонам таблицы пррисвоено в Microsoft Excel

  5. Выделяем элемент листа, в который будет выводиться итог суммирования. Клацаем по иконке «Вставить функцию».
  6. Переход в Мастер функций в Microsoft Excel

  7. Инициируется запуск Мастера функций. Производим перемещение в блок «Математические». Останавливаем выбор из перечня доступных операторов на наименовании «СУММ».
  8. Переход в окно аргументов функции СУММ в Microsoft Excel

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

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

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

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

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

  10. Окно аргументов функции СУММ в Microsoft Excel

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

Результат вычисления функции СУММ в Microsoft Excel

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

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

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

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

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

  1. Чтобы перейти в Диспетчер, перемещаемся во вкладку «Формулы». Там следует кликнуть по иконке, которая так и называется «Диспетчер имен». Указанная иконка располагается в группе «Определенные имена».
  2. Переход в Диспетчер имен в программе Microsoft Excel

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

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

  4. Фильтрация в Диспетчере имён в Microsoft Excel

  5. Для изменения границ, названия или других свойств именованного диапазона следует выделить нужный элемент в Диспетчере и нажать на кнопку «Изменить…».
  6. Переход к изменению именованного диапазона через Диспетчер имен в Microsoft Excel

  7. Открывается окно изменение названия. Оно содержит в себе точно такие же поля, что и окно создания именованного диапазона, о котором мы говорили ранее. Только на этот раз поля будут заполнены данными.

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

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

Окно изменения имени именнованного диапазона в Microsoft Excel

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

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

  1. Чтобы провести процедуру удаления, выделяем нужный элемент из перечня и жмем на кнопку «Удалить».
  2. Удаление именованного диапазона через Диспетчер имен в Microsoft Excel

  3. После этого запускается диалоговое окно, которое просит подтвердить свою решимость удалить выбранный элемент. Это сделано во избежание того, чтобы пользователь по ошибке не выполнил данную процедуру. Итак, если вы уверены в необходимости удаления, то требуется щелкнуть по кнопке «OK» в окошке подтверждения. В обратном случае жмите по кнопке «Отмена».
  4. Подтверждение удаления имени в Microsoft Excel

  5. Как видим, выбранный элемент был удален из перечня Диспетчера. Это означает, что массив, к которому он был прикреплен, утратил наименование. Теперь он будет идентифицироваться только по координатам. После того, как все манипуляции в Диспетчере завершены, клацаем по кнопке «Закрыть», чтобы завершить работу в окне.

Закрытие окна Диспетчера имен в Microsoft Excel

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

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

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

Да Нет

lumpics.ru

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

Диапазоны в Excel раньше назывался блоками. Диапазон – это выделенная прямоугольная область прилегающих ячеек. Данное определение понятия легче воспринять на практических примерах.

В формулах диапазон записывается адресами двух ячеек, которые разделенные двоеточием. Верхняя левая и правая нижняя ячейка, которая входит в состав диапазона, например A1:B3.

Обратите внимание! Ячейка, от которой начинается выделение диапазона, остается активной. Это значит, что при выделенном диапазоне данные из клавиатуры будут введены в его первую ячейку. Она отличается от других ячеек цветом фона.

К диапазонам относятся:

  1. Несколько ячеек выделенных блоком (=B5:D8).
  2. Одна ячейка (=A2:A2).
  3. Целая строка (=18:18) или несколько строк (=18:22).
  4. Целый столбец (=F:F) или несколько столбцов (=F:K).
  5. Несколько несмежных диапазонов (=N5:P8;E18:h35;I5:L22).
  6. Целый лист (=1:1048576).

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



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

Выделение диапазонов – это одна из основных операций при работе с Excel. Диапазоны используют:

Способы выделения диапазонов:

  1. Чтобы выделить диапазон, например A1:B3, нужно навести курсор мышки на ячейку A1 и удерживая левую клавишу мышки провести курсор на ячейку B3. Казалось бы, нет ничего проще и этого достаточно для практических знаний. Но попробуйте таким способом выделить блок B3:D12345.
  2. Теперь щелкните по ячейке A1, после чего нажмите и удерживайте на клавиатуре SHIFT, а потом щелкните по ячейке B3. Таким образом, выделился блок A1:B3. Данную операцию выделения условно можно записать: A1 потом SHIFT+B3.
  3. Диапазоны можно выделять и стрелками клавиатуры. Щелкните по ячейке D3, а теперь удерживая SHIFT, нажмите клавишу «стрелка вправо» три раза пока курсор не переместится на ячейку G3. У нас выделилась небольшая строка. Теперь все еще не отпуская SHIFT, нажмите клавишу «стрелка вниз» четыре раза, пока курсор не перейдет на G7. Таким образом, мы выделили блок диапазона D3:G7.
  4. Как выделить несмежный диапазон ячеек в Excel? Выделите мышкой блок B3:D8. Нажмите клавишу F8 чтобы включить специальный режим. В строке состояния появится сообщение: «Расширить выделенный фрагмент». И теперь выделите мышкой блок F2:K5. Как видите, в данном режиме мы имеем возможность выделять стразу несколько диапазонов. Чтобы вернутся в обычный режим работы, повторно нажмите F8.
  5. Как выделить большой диапазон ячеек в Excel? Клавиша F5 или CTRL+G. В появившемся окне, в поле «Ссылка» введите адрес: B3:D12345 (или b3:d12345) и нажмите ОК. Таким образом, вы без труда захватили огромный диапазон, всего за пару кликов.
  6. В поле «Имя» (которое расположено слева от строки формул) задайте диапазон ячеек: B3:D12345 (или b3:d12345) и нажмите «Enter».

Способ 5 и 6 – это самое быстрое решение для выделения больших диапазонов. Небольшие диапазоны в пределах одного экрана лучше выделять мышкой.

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

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

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

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

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

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

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

Чтобы их выделять просто удерживайте нажатие клавиши CTRL, а дальше как при обычном выделении. Также в данной ситуации особенно полезным будет режим после нажатия клавиши F8: «Расширить выделенный фрагмент».

exceltable.com

Диапазон в Excel - Microsoft Excel для начинающих

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

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

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

  1. Для выбора ячейки C3 кликните по полю на пересечении столбца C и строки 3.

    Диапазон в Excel

  2. Чтобы выбрать столбец C, кликните по заголовку столбца C.

    Диапазон в Excel

  3. Чтобы выбрать строку 3, кликните по заголовку строки 3.

    Диапазон в Excel

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

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

  1. Для выбора диапазона B2:C4 кликните по нижнему правому углу ячейки В2 и протяните указатель мыши до ячейки C4.

    Диапазон в Excel

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

    Диапазон в Excel

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

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

  1. Введите значение 2 в ячейку B2.

    Диапазон в Excel

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

    Диапазон в Excel

    Результат:

    Диапазон в Excel

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

  3. Введите значение 2 в ячейку В2 и значение 4 в ячейку B3.

    Диапазон в Excel

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

    Диапазон в Excel

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

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

    Диапазон в Excel

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

    Диапазон в Excel

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

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

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

    Диапазон в Excel

  2. Перетащите диапазон на новое место.

    Диапазон в Excel

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

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

  1. Выделите диапазон, кликните по нему правой кнопкой мыши и нажмите Copy (Копировать) или сочетание клавиш Ctrl+C.

    Диапазон в Excel

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

    Диапазон в Excel

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

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

  1. Выделите строку 3.

    Диапазон в Excel

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

    Диапазон в Excel

    Результат:

    Диапазон в Excel

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

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

office-guru.ru

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

gototopgototop Диапазон таблицы Excel. Что такое диапазон в Excel?      Диапазон в Excel – это несколько ячеек из таблицы или вся таблица как один диапазон. Выделяют диапазон ячеек, присваивают этому диапазону имя - это именованный диапазон в Excel. Это имя используют при работе с таблицей, при поиске, в формулах, т.д.      Ячейки в диапазоне могут быть расположенны рядом в одном столбце, в одной строке, в нескольких строках или столбцах - это смежные ячейки Excel. Здесь в черном квадрате выделен диапазон, состоящий из смежных ячеек.      А могут ячейки диапазона располагаться не рядом, а в разброс по всей таблице - это не смежные ячейки Excel. Например: ячейки в первом, третьем и пятом столбцах из первой, седьмой, девятой строки. То есть, любые ячейки в любом месте и количестве.     Диапазон в Excel нужен для того,      чтобы найти определенные ячейки для дальнейшей работы в таблице,     чтобы вставить этот диапазон в формулу,     чтобы выделить и удалить данные из этих ячеек,     чтобы изменить ссылку в формулах, саму формулу,     чтобы вставить этот диапазон в выпадающий список. Смотрите статью "Выпадающий список в Excel".     чтобы вставить, скрытый от посторонних взглядов, текст. Как скрыть текст, значение ячейки в Excel, смотрите в статье "Как скрыть текст в ячейке Excel" тут.     Перед тем, как присвоить имя диапазону или ввести формулу, настроить формат, т.д., нужно выделить эти ячейки. О разных способах быстро выделить определенные ячейки, диапазон, столбец, строку, лист, т.д, смотрите в статье "Как выделить в Excel ячейки, таблицу, др.".     Как задать диапазон в Excel.Можно в формуле написать адрес диапазона через двоеточие. А можно присвоить диапазону имя и указывать это имя в формуле или искать диапазон в диспетчере задач, т.д..     Как указать диапазон в формуле Excel.Можно записать адрес диапазона, если ячейки смежные. Например, A1: D2. Значит, в этом диапазоне ячейки из столбцов А, B, C, D первой и второй строки.      А можно сделать именованный диапазон Excel. Как присвоить имя диапазону Excel, смотрите в статье "Присвоить имя в Excel ячейке, диапазону, формуле".      Динамический диапазон в Excel.В Excel можно настроить таблицу, диапазон так, что он будет автоматически меняться при добавлении или удалении строк или столбцов. Например, в формуле стоит диапазон А1:В21. Если добавим строку в таблицу, то в формуле нужно менять диапазон на А1С21. Динамический диапазон сам все исправляет. Читайте об этом статью "Чтобы размер таблицы Excel менялся автоматически".      Как найти диапазон в Excel.Если в таблице Excel уже есть именованные диапазоны и нам нужно найти один из них, то найти его можно несколькими способами.      Первый способ.Найти быстро диапазон можно в строке "Имя", рядом со строкой формул. Нажимаем на стрелку (обведена на рисунке кругом). Здесь диапазон называется «Диапазон Excel».      Второй вариант.   Найти диапазон можно в закладке «Формулы» -> «Определенные имена» -> «Диспетчер имен».       С помощью диапазона можно сделать закладки в таблице, чтобы найти быстро нужное место в таблице. Смотрите статью "Сделать закладки в таблице Excel" здесь.       Подробнее о том, как создать диапазон, как правильно выделить несмежные ячейки, присвоить имя диапазону, как быстро найти диапазон, как удалить диапазон, изменить его, др., смотрите в статье «Диапазон в Excel».

www.excel-office.ru

Диапазоны

Сравнение двух таблиц

Как сравнить между собой две таблицы (прайс-листа) и наглядно отобразить отличия между ними - добавленные и удаленные товары, изменение цены и т.д. Разбор 3 способов: с помощью функции ВПР (VLOOKUP), сводной таблицей и через надстройку Power Query.

Трансформация столбца в таблицу

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

Самая длинная победная серия

Как подсчитать максимальное количество идущих подряд значений, удовлетворяющих заданному условию? Например, самое большое количество рабочих дней подряд в табеле? Или самую длинную победную серию в спорте?

Слияние двух списков без дубликатов

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

Левый ВПР

Как реализовать в Excel аналог функции ВПР (VLOOKUP), который будет выдавать значения левее поискового столбца.

5 вариантов использования функции ИНДЕКС (INDEX)

Подробный разбор всех пяти вариантов применения мегамощной функции ИНДЕКС (INDEX): от простого поиска данных в столбце, до двумерного поиска в нескольких таблицах и создания авторастягивающихся диапазонов.

Создание базы данных в Excel

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

Обратный порядок элементов в списке

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

Поиск и подстановка по нескольким условиям

Как искать в одной таблице и подставлять в другую данные по нескольким критериям, т.е., другими словами, реализовать поиск аналогичный функции ВПР (VLOOKUP) но сразу по нескольким условиям.

Суммирование только видимых ячеек

Как при помощи функций ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ просуммировать (а также вычислить среднее, минимум, максимум, количество и т.д.) только видимых ячеек оставшихся после применения фильтра, скрытия строк/столбцов, сворачивания группировкой, а также проигнорировать ячейки с ошибками при расчетах.

Подстановка "из ниоткуда"

Как использовать массивы констант и имена, чтобы встроить поисковые таблицы прямо внутрь функций ВПР, ИНДЕКС, ПОИСКПОЗ и им подобных, чтобы подставлять данные на лету и без использования отдельных таблиц.

Динамический диапазон с автоподстройкой размеров

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

Суммирование каждой 2-й, 3-й... N-й ячейки

Несколько способов произвести вычисления (просуммировать, выбрать максимум/минимум, посчитать среднее арифметическое) с каждой 2-ой, 3-ей... n-ой ячейкой в заданном диапазоне, т.е. например сложить каждую четную ячейку или ячейки через одну.

Расширенный фильтр и немного магии

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

Подробнее... Хитрости сортировки

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

Суперфильтр на VBA

Быстрая и удобная альтернатива стандартному Автофильтру с помощью небольшого макроса. Умеет искать по маске и одному/двум сложным условиям (с И и ИЛИ). Без мыши. В реальном времени.

Умные таблицы Excel 2007-2013

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

ВПР (VLOOKUP) с интервальным просмотром

Как проверить попадание параметра в один из заданных диапазонов и, при этом, обойтись без громоздких вложенных проверок с помощью кучи функций ЕСЛИ (IF)?

Улучшаем функцию ВПР (VLOOKUP)

Встроенная функция ВПР (VLOOKUP) имеет один существенный недостаток - ищет только первое вхождение искомого значения в таблицу и только в крайнем левом столбце. А если нужно найти 2-е, 3-е и не в крайнем левом?

Подробнее... Случайная выборка

Как случайным образом выбрать из большого массива данных заданное количество элементов?

Поиск нужных данных в диапазоне

О двух ближайших "родственниках" функции ВПР (VLOOKUP) - функциях поиска и выборки ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), позволяющих выбирать данные из списка по заданным условиям и реализовать "левый ВПР".

Подробнее... Формулы массива в Excel

Что такое формула массива? Как ее вводить, редактировать и удалять? Как формулы массива могут упростить и облегчить жизнь пользователя в больших таблицах?

Типы ссылок на ячейки в формулах Excel

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

Многоразовый ВПР (VLOOKUP)

Как извлечь ВСЕ данные из таблицы по заданному критерию. Функция ВПР (VLOOKUP) находит значение только по первому совпадению, а нужно вытащить все. Поможет хитрая формула массива!

Подробнее... Сортировка формулой

Несколько способов отсортировать числовой или текстовый список не через меню Данные - Сортировка, а с помощью формул "на лету".

Подробнее... ВПР (VLOOKUP) с учетом регистра

Как при помощи формулы массива сделать аналог функции ВПР (VLOOKUP), который при поиске будет учитывать регистр и различать строчные и прописные символы.

Двумерный поиск в таблице (ВПР 2D)

Как искать и выбирать нужные данные из двумерной таблицы, т.е. производить выборку не по одному параметру (как функции ВПР или ГПР), а по двум сразу.

Редизайнер таблиц

Специальный макрос, позволяющий переконструировать Вашу "красивую" таблицу в тот вид, который понимает и с которым может работать Excel.

Динамическая гиперссылка

Как с помощью функций ГИПЕРССЫЛКА и ПОИСКПОЗ сделать "живую" ссылку на определенную строку в другой таблице, содержащую нужные вам данные, чтобы потом быстро переходить из одной таблицы в другую.

www.planetaexcel.ru

Excel. Имена диапазонов

Возможно, вам приходилось работать с листами, в которых использовалась, формула типа: =СУММ(А5000:А5078). Вы гадали, что же находится в ячейках А5000:А5078!? Если в ячейках А5000:А5078 содержатся объемы продаж по регионам, не кажется ли вам формула =СУММ(ПродажиРегионы) более понятной? В данной главе описываются способы присвоения имен отдельным ячейкам и диапазонам ячеек, а также способы вставки имен диапазонов в формулы. [1]

Как создать именованный диапазон?             

Существуют три способа создания именованных диапазонов:

Для создания имени диапазона с помощью поля Имя (рис. 1.1) выделите ячейку или диапазон ячеек, которым требуется присвоить имя, установите курсор в поле Имя, введите имя диапазона, и нажмите клавишу <Enter>. На рис. 1.1 ячейке В3 присвоено имя Старт.

Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя

Рис. 1.1. Создание имени диапазона путем выбора диапазона ячеек и ввода имени в поле Имя

Скачать заметку в формате Word или pdf, примеры в формате Excel

При нажатии в поле Имя на стрелку раскрывающегося списка появятся имена диапазонов, определенные в текущей книге (рис. 1.2). При выборе в поле Имя имени диапазона все ячейки, соответствующие этому диапазону, отмечаются автоматически. Это позволяет убедиться в правильности выбора ячейки или диапазона ячеек для указанного имени. В именах диапазонов регистр не учитывается. Например, если выбрать имя Финиш, будет отмечена ячейка Е8 (рис. 1.3).

Рис. 1.2. Список имен диапазонов

Рис. 1.2. Список имен диапазонов

Рис. 1.3. При выборе имени диапазона отмечаются все ячейки, соответствующие этому диапазону

Рис. 1.3. При выборе имени диапазона отмечаются все ячейки, соответствующие этому диапазону

При нажатии клавиши <F3> открывается диалоговое окно Вставка имени, в котором отображаются имена всех диапазонов.

Присвоение имени означает, что вместо любой ссылки Старт в формуле будет автоматически подставлено значение из ячейки В3.

Предположим, что необходимо присвоить имя Данные прямоугольному диапазону ячеек A1:B5. Выделите диапазон ячеек A1:B5, введите с клавиатуры Данные в поле Имя и нажмите клавишу <Enter>. Теперь с помощью формулы =СРЗНАЧ(Данные) можно вычислить среднее значение содержимого ячеек A1:B4 (рис. 1.4).

Рис. 1.4. Присвоение диапазону A1_B4 имени Данные

Рис. 1.4. Присвоение диапазону A1:B5 имени Данные и нахождение среднего значения именованного диапазона

Иногда требуется присвоить имя диапазону ячеек, состоящему из нескольких несмежных прямоугольных диапазонов. Например, B3:C4, E6:G7 и B10:C10 (рис. 1.5). Для присвоения имени выделите любой из трех прямоугольников. Удерживая клавишу <Ctrl>, выделите остальные два диапазона. Отпустите клавишу <Ctrl>, введите имя Несмежный в поле Имя и нажмите клавишу <Enter>. Теперь имя Несмежный в любой формуле указывает на содержимое ячеек B3:C4, E6:G7 и B10:C10.

Рис. 1.5. Присвоение имени несмежному диапазону ячеек

Рис. 1.5. Присвоение имени несмежному диапазону ячеек

Создание имен с помощью инструмента Создать из выделенного. На листе «Рис. 1.6» Excel-файла с примерами содержатся продажи за март для каждого из 50 штатов США (рис. 1.6). Требуется присвоить каждой ячейке в диапазоне B2:B51 сокращенное название штата. Выделите диапазон A2:B51 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного, и затем в открывшемся диалоговом окне установите флажок в столбце слева.

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

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

Теперь имена в первом столбце выделенного диапазона связаны с ячейками во втором столбце выделенного диапазона. Таким образом, ячейке B6 присвоено имя диапазона СА, ячейка B7 имеет имя СО и т.д. Создавать имена таких диапазонов с помощью поля Имя было бы невероятно утомительно! Нажмите на стрелку раскрывающегося списка в поле Имя и убедитесь, что все имена диапазонов созданы.

Создание имен диапазонов с помощью инструмента Присвоить имя. Если на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Диспетчер имен (и затем нажать кнопку Создать) или инструмент Присвоить имя, откроется диалоговое окно Создание имени (рис. 1.7).

Предположим, требуется присвоить имя область1 диапазону ячеек A2:B7. Введите область1 в поле Имя, переместите курсор в поле Диапазон, и выделите диапазон на листе или введите с клавиатуры =A2:B7. Нажмите кнопку OK для завершения присваивания.

Рис. 1.7. Диалоговое окно Создание имени

Рис. 1.7. Диалоговое окно Создание имени

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

Рис. 1.8. Выбор области действия имени

Рис. 1.8. Выбор области действия имени

Диспетчер имен

В Microsoft Excel 2013 существует простой способ изменения или удаления имен диапазонов. Перейдите на вкладку ФОРМУЛЫ, выберите группу Определенные имена и откройте Диспетчер имен. Появится список имен всех диапазонов (рис. 1.9).

Рис. 1.9. Диспетчер имен

Рис. 1.9. Диспетчер имен

Для изменения имени диапазона дважды щелкните кнопкой мыши на имени этого диапазона или выделите его и нажмите кнопку Изменить; после этого можно изменить имя диапазона, ячейки в диапазоне и примечания. Область действия не подлежит изменению. Для удаления какого-либо подмножества имен диапазонов сначала выделите имена диапазонов, которые требуется удалить. Если имена диапазонов перечислены последовательно, выделите первое имя в группе имен, которую требуется удалить, затем, удерживая клавишу <Shift>, выделите последнее имя в группе. Если требуемые имена не перечислены друг за другом, можно выделить любое из имен, которое необходимо удалить, а далее, удерживая клавишу <Ctrl>, выделить остальные требуемые имена диапазонов. Затем для удаления выбранных имен диапазонов нажмите кнопку Удалить.

Редактирование формул в диалоговых окнах

Когда Excel отображает диалоговое окно (например, как на рис. 1.7 или 1.9), в котором можно записать ссылку на диапазон, поле, содержащее такую ссылку, всегда находится в режиме указания. Если активизировать поле Диапазон и воспользоваться стрелками для редактирования ссылки на диапазон, то вы обнаружите, что при этом вы именно указываете на диапазон, а не редактируете текст ссылки. Если на рисунке ниже вы поместите курсор в поле Диапазон, то попытка двинуть курсор влево с помощью стрелки даст неожиданный результат. Вместо движения курсора произошло изменение ссылки (обратите внимание: актуальный режим указан в левой части статусной панели):

%d1%80%d0%b8%d1%81-1-9%d0%b0-%d1%80%d0%b5%d0%b6%d0%b8%d0%bc-%d0%b2%d0%b2%d0%be%d0%b4%d0%b0

Что делать? Нажмите F2. [2] Клавиша F2 позволяет переключаться между режимом указания (ввод) и режимом редактирования (правка). В режиме редактирования стрелки действуют именно так, как при редактировании формулы. На рисунке ниже попытка двинуть курсор влево увенчалась успехом:

%d1%80%d0%b8%d1%81-1-9%d0%b1-%d1%80%d0%b5%d0%b6%d0%b8%d0%bc-%d0%bf%d1%80%d0%b0%d0%b2%d0%ba%d0%b8

Несколько конкретных примеров использования имен диапазонов

1. Необходимо вычислить общий объем продаж в штатах Аризона, Калифорния, Монтана, Нью-Йорк и Нью-Джерси.

Если вы помните наизусть сокращенные наименования штатов, то можно использовать формулу =AZ+CA+MT+NY+NJ (рис. 1.10)

Рис. 1.10. Использование имен вычисления объема продаж в отдельных штатах

Рис. 1.10. Использование имен вычисления объема продаж в отдельных штатах

2. Необходимо определить среднюю доходность акций, казначейских векселей и облигаций.

Выделите диапазон ячеек B2:D84 (рис. 1.11, часть строк на рис. скрыта), перейдите на вкладку ФОРМУЛЫ в группе Определенные имена выберите инструмент Создать из выделенного. В этом примере имена диапазона указаны в строке выше. Диапазон B3:B84 получает имя Акции, диапазон C3:C84 — имя Векселя и диапазон D3:D84 — имя Облигации. Таким образом, необходимость помнить, где находятся данные, отпадает. Например, если после начала ввода в ячейку B86 формулы нажать клавишу <F3>, откроется диалоговое окно Вставка имени. Кроме того, можно вызвать на экран список доступных имен диапазонов, если после начала ввода на вкладке ФОРМУЛЫ в группе Определенные имена выбрать инструмент Использовать в формуле. И, наконец, если вы помните первые буквы имени диапазона, и начнете их вводить в формуле, Excel выдаст подсказку (рис. 1.12). Эта опция Excel называется автозавершение формул. Для завершения ввода имени диапазона дважды щелкните на имени Векселя. Удобство использования имен диапазонов заключается в том, что, не зная точно, где находятся данные, можно работать с данными в любом месте книги!

Рис. 1.11. Исторические данные по инвестициям

Рис. 1.11. Исторические данные по инвестициям

Рис. 1.12. Подсказка при вводе в формуле имени диапазона

Рис. 1.12. Подсказка при вводе в формуле имени диапазона

3. Использование имен столбца и строки

При использовании в формуле имени столбца (в формате A:A, C:C и т.д.) весь столбец обрабатывается в Excel как именованный диапазон. Например, по формуле =СРЗНАЧ(A:A) вычисляется среднее значение всех чисел в столбце А. Использование имени диапазона для целого столбца очень эффективно при частом вводе новых данных в столбец. Например, если столбец A содержит данные о ежемесячных продажах продукта, то новые данные добавляются каждый месяц, и по такой формуле вычисляется актуальное среднее значение ежемесячных продаж. Однако будьте осторожны: если ввести формулу =СРЗНАЧ(А:А) в столбец А, то появится сообщение о циклической ссылке, т.к. значение в ячейке, содержащей формулу расчета среднего, будет зависеть от ячейки, содержащей среднее значение. Способ разрешения циклических ссылок см. Excel. Как найти циклическую ссылку. Аналогично, по формуле =СРЗНАЧ(1:1) рассчитывается среднее значение всех чисел в строке 1.

4. Имена с областью действия книга и лист

При создании имен с помощью поля Имя областью действия имен по умолчанию становится Книга. Однако, можно присвоить одно и тоже имя на разных листах, выбрав область действия Лист. Например, создайте новую книгу Excel, содержащую три листа, и введите числа 4, 5, 6 в ячейки E4:E6 на листе Лист1 и 3, 4, 5 в ячейки E4:E6 на листе Лист2. Затем откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 на листе Лист1 и определите область действия для этого имени как Лист1. Далее перейдите на Лист2, откройте окно Диспетчер имен, присвойте имя jam ячейкам E4:E6 и определите область действия для этого имени как Лист2. Диалоговое окно Диспетчер имен показано на рис. 1.13.

Рис. 1.13. Имена на уровне Листа

Рис. 1.13. Имена на уровне Листа

Что произойдет, если ввести формулу =СУММ(jam) на каждом из трех листов? На листе Лист1 по формуле =СУММ(jam) будут просуммированы значения ячеек E4:E6 листа Лист1. Так как в этих ячейках содержатся числа 4, 5 и 6, в сумме получится 15. На листе Лист2 по формуле =СУММ(jam) будут просуммированы значения ячеек E4:E6 листа Лист2, что в сумме даст 3 + 4 + 5 = 12. Однако на листе Лист3 вычисление по формуле =СУММ(jam) приведет к появлению сообщения об ошибке #имя?, поскольку на этом листе отсутствует диапазон с именем jam. Если где-либо на листе Лист3 ввести формулу =СУММ(лист2!jam), Excel распознает имя на уровне листа, которое представляет диапазон ячеек E4:E6 листа Лист2, и в результате получится 3 + 4 + 5 = 12. Таким образом, указав перед именем диапазона соответствующее имя листа с восклицательным знаком (!), можно обратиться к диапазону на листе, отличном от того листа, где диапазон был определен.

5. Как добиться отображения недавно созданных имен диапазонов в ранее созданных формулах?

Рассмотрим небольшую таблицу, содержащую формулы (рис. 1.14).

Рис. 1.14. Новые имена диапазонов в старых формулах

Рис. 1.14. Новые имена диапазонов в старых формулах

Ячейка F3 содержит цену продукта, а ячейка F4 — потребность в продукте =10000–300*F3. В ячейки F5 и F6 введена себестоимость единицы продукции и постоянные затраты, соответственно. Прибыль вычисляется в ячейке F7 по формуле =F4*(F3–F5)–F6. Выделите диапазон E3:F7, затем для присвоения ячейке F3 имени цена, ячейке F4 имени потребность, ячейке F5 имени себестоимость, ячейке F6 имени затраты и ячейке F7 имени прибыль используйте вкладку ФОРМУЛЫ, инструмент Создать из выделенного и флажок в столбце слева. Теперь имена созданных диапазонов необходимо отобразить в формулах ячеек F4 и F7. Для применения имен сначала выделите диапазон, для которого они создаются (в данном случае F3:F7). Затем на вкладке ФОРМУЛЫ в группе Определенные имена нажмите стрелку раскрывающегося списка Присвоить имя и выберите инструмент Применить имена. Выделите в окне имена, которые требуется применить, и нажмите кнопку OK. Обратите внимание, что в ячейке F4 теперь находится формула =10000-300*цена, а в ячейке F7 формула =потребность*(цена–себестоимость)–затраты, что и требовалось. [3]

6. Можно ли вывести на лист Excel список всех имен диапазонов (и представляемых ими ячеек)?

Откройте окно Вставка имени с помощью клавиши <F3> и нажмите кнопку Все имена (рис. 1.15). На листе, начиная с текущей ячейки, появится список имен диапазонов и соответствующих им ячеек.

Рис. 1.15. Вывод на лист Excel список всех имен диапазонов (и представляемых ими ячеек)

Рис. 1.15. Вывод на лист Excel список всех имен диапазонов (и представляемых ими ячеек)

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

Пример 1. Предполагаемый годовой доход вычисляется как кратный прошлогоднему доходу (рис. 1.16). Воспользуемся формулу =(1+прирост)*предыдущий_год (имя диапазона не может содержать пробел). Требуется вычислить доходы за 2012–2018 гг. с приростом 10% в год, начиная с базового уровня 300 млн. долларов в 2011 г.

Сначала в поле Имя присвойте ячейке B3 имя прирост. Теперь самое интересное! Переместите курсор в ячейку B7 и на вкладке ФОРМУЛЫ в группе Определенные имена выберите инструмент Присвоить имя для открытия диалогового окна Создание имени. Введите данные, как показано на рис. 1.16. Поскольку активной является ячейка B7, Excel всегда будет интерпретировать имя диапазона как указывающее на ячейку, находящуюся над текущей ячейкой. Это не будет работать, если в ссылке на ячейку B6 останется знак доллара, поскольку он не позволит изменить ссылку на строку и указать строку непосредственно над активной ячейкой (подробнее см. Относительные, абсолютные и смешанные ссылки на ячейки в Excel. Если в ячейку B7 ввести формулу =предыдущий*(1+прирост) и скопировать ее в диапазон B8:B13, каждая ячейка будет содержать требуемую формулу, по которой содержимое ячейки непосредственно над активной ячейкой будет умножаться на 1,1.

Рис. 1.16. Для любой ячейки это имя указывает на ячейку, находящуюся над активной ячейкой

Рис. 1.16. Для любой ячейки это имя указывает на ячейку, находящуюся над активной ячейкой

Пример 2. Для каждого дня недели дана почасовая оплата и количество отработанных часов (рис. 1.17). Вычислим зарплату за каждый день по формуле почасовая*часы.

Выберите строку 12 (щелкните слева на 12) и в поле Имя (рядом со строкой формул) введите имя почасовая. Выберите строку 13 и введите в поле Имя – часы. Если теперь в ячейку F14 ввести формулу =почасовая*часы и скопировать эту формулу в диапазон G14:L14, то в каждом столбце автоматически появится результат перемножения значений почасовой оплаты и отработанных часов.

Рис. 1.17. Расчет зарплаты по дням недели

Рис. 1.17. Расчет зарплаты по дням недели

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

Некоторые замечания:

Задания для самостоятельной работы

Исходные данные находятся в файле Имена диапазонов. Задания.xlsx

  1. На листе Задание 1 содержатся данные о ежемесячной доходности акций General Motors и Microsoft. Присвойте имена диапазонам, содержащим ежемесячную доходность для каждой акции, и вычислите среднемесячную доходность каждой акции.
  2. На листе Задание 2 присвойте имя Красный диапазону, содержащему ячейки A1:B3 и A6:B8.
  3. На листе Задание 3 в ячейки G5 и G6 введите широту и долготу любого города, а в ячейки G7 и G8 широту и долготу другого города. В ячейке G10 вычисляется расстояние между двумя городами. Определите имена диапазонов для широты и долготы каждого города и убедитесь, что эти имена отображаются в формуле для расчета расстояния.
  4. На листе Задание 4 содержится количество акций для каждого вида акций и цена одной акции. Вычислите стоимость акций для каждого вида по формуле =количество*цена.
  5. На листе Задание 5 создайте имя диапазона для расчета среднего значения продаж за последние пять лет. Измените формулы в ячейках Е14:Е20.

 

[1] При написании заметки использованы материалы книги Уэйн Л. Винстон. Microsoft Excel 2013. Анализ данных и бизнес-моделирование, глава 1.

[2] При написании этого раздела использованы идеи книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 156.

[3] У меня не получилось воспользоваться указанным методом, поэтому пришлось перенабрать формулы после присвоения имен.

baguzin.ru