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

Excel 10. Диапазон и вычисления в нем

1. Диалоговые окна «Вставка функции» и «Аргументы функции»

Шаг 1. Вводим в ячейку А7 Максимум и переходим в ячейку В7:

Шаг 2. Открываем диалоговое окно «Вставка функции» (лента Формулы → команда Вставить функцию):

Как только мы вызвали диалоговое окно «Вставка функции», в вычисляемой ячейке появился знак «=».

Шаг 3. Выбираем в списке из 10 последних использовавших функций «МАКС» (поле «Выберите функцию»).

Это ещё один способ вставить функцию в дополнение к уже известным по прошлой статье (Excel 9). В Excel встроено около 300 функций. Вряд ли вы будете использовать все функции. Поэтому полезно знать, где находится список из самых вами любимых функций, чтобы избежать длительных поисков.

В поле «Поиск функции» вы можете ввести описание нужной вам функции, и Excel услужливо поможет вам в поиске.

При нажатии кнопки выпадающего меню поля «Категории» вы увидите перечень всех категорий функций:

Нашли функцию МАКС? ОК!

Но у нас с вами мелкая неприятность – диалоговое окно «Аргументы функции» перекрывает ряд чисел:

Шаг 4. Щелкаем по значку со стрелкой справа от поля «Число!». Диалоговое окно свернется и вам надо выбрать диапазон, в котором будет искаться максимум.

Шаг 5. Щелкаем по значку со стрелкой справа от поля «Число!» – диалоговое окно «Аргументы функции» развернется. Нажимаем ОК.

В предыдущих версиях Excel эта операция носила название «Мастер функций.

Понять и запомнить!

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

2. Имя диапазона

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

1 способ.

Шаг 1. Выделяем диапазон ячеек В1:В6.

Шаг 2. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Создать из выделенного):

Откроется диалоговое окно «Создание имен из выделенного диапазона». Оставим активным параметр «в строке выше». ОК.

2 способ.

Шаг 3. Выделите диапазон ячеек А1:А6.

Шаг 4. Задаем имя диапазону (лента Формулы → группа команд Определенные имена → Присвоить имя):

Откроется диалоговое окно «Создание имени».

1. По умолчанию содержимое первой ячейки выделенного диапазона становится именем диапазона. Но никто не мешает нам задать другое имя. Обратите внимание, что пробел заменился на нижнее тире. Что поделаешь? Требование Excel.
2. Определение области действия имени: вся книга или конкретный лист.
3. Пояснения, которые мы сочтем нужным дать этому диапазону. Пояснения не участвуют в вычислениях, операциях и во всем другом прочем. Это «напоминалка» для нас.
4. Определенная величина диапазона. Но мы можем скорректировать величину диапазона. Например, у нас прибавились записи, которые следует отнести к этому диапазону. Тем более, что рядом присутствует кнопка со стрелкой, которая позволит нам на время свернуть диалоговое окно.

Шаг 5. Нажимаем на кнопку выпадающего меню имени ячейки (неважно, где находится активная ячейка):

Шаг 6. Щелкаем ЛМ по первому имени в этом списке:

Диапазон с именем «Наименование_курса» выделился полностью.

Понять и запомнить!

Разница между двумя способами: 1. Имя диапазона определяется автоматически 2. Имя диапазона можно задать по своему желанию

Шаг 7. Щелкните в любой ячейке по вашему выбору. Набираем «=су»:

Двойной щелчок ЛМ по «СУММ».

Шаг 8. Выбираем диапазон по имени (набираем непосредственно в ячейке «ц». сразу появляется выпадающий список, в котором присутствуют функции, начинающие на «ц», и имя нашего диапазона). Двойной щелчок ЛМ по имени диапазона:

Шаг 9. И обязательно закрывающая скобка! Требования Excel по части синтаксиса написания формул надо соблюдать.

Шаг 10. Нажимаем Enter:

Смотрим на строку формул: =СУММ(Цена). То есть по имени определил соответствующий диапазон.

Шаг 11. Лента Формулы → группа команд Определенные имена → команда Диспетчер имен. Откроется диалоговое окно «Диспетчер имен»:

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

3. Абсолютный и относительный адрес ячейки

Скопируем одну таблицу. Вот тут внимание! Если вы просто выделите весь диапазон и перенесете на другое место листа (Excel 5), то вы увидите следующую картину:

Потом потратим драгоценное время на настройки ширины и высоты ячеек. Так что не торопитесь.

Шаг 1. Выделяем таблицу со значениями и копируем в буфер обмена:

Шаг 2. Вставляем содержимое буфера обмена в ячейку F1 специальной вставкой с сохранение ширины столбцов:

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

Сейчас мы подсчитаем стоимость курса для группы из 20 человек (больше набирать смысла нет). Для первой таблицы введите в диапазоне С2:С6 число «20» в каждую ячейку. Для второй таблицы введите число «20» в ячейку Н1:

Шаг 3. В ячейку D2 введите формулу «=ПРОИЗВЕД(В2;С2). Можно проще: «=В2*С2»:

Шаг 4. А теперь распространим эту формулу по всему столбцу стоимости курсов. Это можно сделать двумя способами.

1 способ.

1. Подвести курсор к зеленому квадратику в правом нижнем углу ячейки с формулой – курсор превратиться в черный крест (курсор заполнения таблицы – Excel 3)
2. Нажать ЛМ и, не отпуская, протянуть на весь диапазон:

2 способ.

1. Подвести курсор к зеленому квадратику в правом нижнем углу ячейки с формулой – курсор превратиться в черный крест (курсор заполнения таблицы – Excel 3)
2. Дважды щелкнуть ЛМ:

Посмотрим на формулы в каждой ячейке.

Шаг 5. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):

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

Понять и запомнить!

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

Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.

Поработаем со второй таблицей.

Шаг 6. В ячейку Н2 вводим формулу «=G2*H1»:

Теперь нам надо указать, что при копировании формулы ссылка будет только на ячейку Н1, то есть адрес этой ячейки будет абсолютным. Для указания абсолютного адреса в Excel служит знак «$» или знак доллара. Если не лень, ставьте вручную (Shift+4 в английской раскладке клавиатуры). Но лучше воспользоваться функциональной клавишей F4, которой все равно какая раскладка клавиатуры):

Если вы работаете за ноутбуком, то не забудьте в дополнение одновременно нажить клавишу Fn.

Шаг 7. Поместите курсор на имя ячейки «Н1» в формуле и нажмите Fn на клавиатуре:

1. Относительный адрес. Относительная адресация – это изменяющийся при копировании и перемещении формулы адрес ячейки, содержащей исходное данное (используется по умолчанию).
2. Абсолютный адрес. Абсолютная адресация – это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащей исходное данное. Выглядит такиой адрес следующим образом: $А$1. Знак доллара обозначает, что значения строки и столбца «закреплены».
3. Смешанный или частичный адрес. Частичная абсолютная адресация указывается, если при копировании формулы не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором – перед наименованием столбца: B$5; D$12.

Шаг 8. Нажмем Enter и скопируем формулу по диапазону:

Шаг 9. Покажем формулы (лента Формулы → группа команд Зависимости формул → команда Показать формулы):

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

Если вы ещё раз щелкните ЛМ по команде «Показать формулы», то в ячейках мы увидим значение формулы.

Определить диапазон в Excel

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

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

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

• путем ввода имени диапазона в поле Имя;
• путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструмента Создать из выделенного;
• путем выбора на вкладке ФОРМУЛЫ в группе Определенные имена инструментов Присвоить имя или Диспетчер имен.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 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. Имена на уровне Листа

Что произойдет, если ввести формулу =СУММ(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. Новые имена диапазонов в старых формулах

Ячейка 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 список всех имен диапазонов (и представляемых ими ячеек)

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. Для любой ячейки это имя указывает на ячейку, находящуюся над активной ячейкой

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

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

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

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

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

• В Excel невозможно использовать в качестве имен диапазонов буквы r и c.
• Единственными символами, которые можно использовать в именах диапазонов, являются точка (.) и подчеркивание (_).
• При использовании инструмента Создать из выделенного пробелы в созданном имени автоматически будут заменены символами подчеркивания (_). Например, имя Product 1 будет создано как Product_1.
• Имена диапазонов не могут начинаться с цифр или выглядеть как ссылка на ячейку. Например, в качестве имен диапазонов невозможно использовать имена 3Q и A4. Кроме того, в Microsoft Excel 2013 имеется более 16 000 столбцов, и такие имена, как cat1, являются недопустимыми, поскольку существует ячейка с именем CAT1. Если попытаться присвоить ячейке имя CAT1, появится сообщение о том, что введено недопустимое имя. В случае необходимости используйте подчеркивание (_) и назовите ячейку cat1_.

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

Исходные данные находятся в файле Имена диапазонов. Задания.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] У меня не получилось воспользоваться указанным методом, поэтому пришлось перенабрать формулы после присвоения имен.

Работа с именованным диапазоном в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Диапазон Excel

Начнем с простого – присвоим имя ячейке. Для этого просто выделяем ее (1) и в поле имени (2) вместо адреса ячейки указываем произвольное название, которое легко запомнить.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задача

Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):

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

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

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

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

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

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя </em>;
• в поле Имя введите: Месяц </em>;
• в поле Область выберите лист Книга </em>;
• в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
• нажмите ОК.

Теперь подробнее. Любой диапазон в EXCEL задается координатами верхней левой и нижней правой ячейки диапазона. Исходной ячейкой, от которой отсчитывается положение нашего динамического диапазона, является ячейка B5 . Если не заданы аргументы функции СМЕЩ() смещ_по_строкам, смещ_по_столбцам (как в нашем случае), то эта ячейка является левой верхней ячейкой диапазона. Нижняя правая ячейка диапазона определяется аргументами высота и ширина . В нашем случае значение высоты =1, а значение ширины диапазона равно результату вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5) , т.е. 4 (в строке 5 присутствуют 4 месяца с января по апрель ). Итак, адрес нижней правой ячейки нашего динамического диапазона определен – это E 5 .

При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .

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

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

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

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя </em>;
• в поле Имя введите: Продажи_за_месяц </em>;
• в поле Диапазон введите формулу = СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)
• нажмите ОК.

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

И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.

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

Примечание: Вместо формулы с функцией СМЕЩ() для подсчета заполненных месяцев можно использовать формулу с функцией ИНДЕКС() : = $B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))

Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .

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

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

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

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

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

Используем формулу ОБЛАСТИ, поочередно выделяя каждый столбец ячейки в качестве параметра. Перед выбором последующего столбца нажимаем и удерживаем кнопку Ctrl. Если добавить символ «)» и нажать Enter, появится диалоговое окно с сообщением о том, что было введено слишком много аргументов. Добавим дополнительные открывающую и закрывающую скобки.

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

Пример 3. Определить, принадлежит ли ячейка заданному диапазону ячеек.

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

1. 1. В какой-либо ячейке введем часть формулы «=ОБЛАСТИ((» и выделим произвольную область ячеек для заполнения аргументов:

   

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

      

      1. 1. Закроем обе скобки и нажмем Enter. В результате получим:

         

         1. 1. Если выбрать ячейку не из указанного диапазона, получим ошибку #ПУСТО!.

            

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

            Если выделить несколько ячеек внутри диапазона, функция ОБЛАСТИ вернет количество выделенных ячеек:

            

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

            Перемещение и копирование ячеек и их содержимого

            ​Смотрите также​​ =ЕСЛИ(ЕЧИСЛО(A2);A2;B2) протягиваете копируете​ соответственно макрос “Фильтра”​ хамом станешь…Оно кому​ только в таблице.​: Думаю возможно если​ столбец. Т.е. получается,​ ВСЕ”, затем произведите​ и нажать​Вставить​ более сложная процедура,​Нажмите иконку Вставить на​ указанных ниже действий.​Вставить​, можно выбрать​ временно отобразить данные,​ выделенный фрагмент на​Примечание:​ вставляете как значения​ выводит таблицу с​ надо?​

            ​The_Prist​ копировать выделенный диапазон​ что выделяем для​​ описанные выше действия​​Ctrl+Space​​. Только имейте в​​ чем хотелось бы.​​ вкладке Главная или​​Чтобы переместить ячейки, нажмите​.​ параметры вставки, которые​ которые не нужно​ другой лист или​ Мы стараемся как можно​

            ​grablik​ заданными параметрами дат.​Юрий М​: В примере все​ по одной ячейке.​

            ​ копирования не Range(“7:7″​ с формой.​(Пробел). Таким способом​ виду, что здесь​​ На самом деле,​​ нажмите Ctrl+V на​​ кнопку​​Сочетание клавиш​

            ​ следует применить к​

            1. ​ копировать.​ в другую книгу,​ оперативнее обеспечивать вас​
            2. ​: Сергей, спасибо, но​2. Если необходимо​
            3. ​: Нет уж! Сказав​ работает – зачем​​OLEGOFF​ ), а Range(“$A$7:$V$7,$X$7:$IV$7).​The_Prist​

            ​ будут выделены только​ существует несколько особенностей,​ это один из​

            1. ​ клавиатуре.​Вырезать​
            2. ​ Можно также нажать сочетание​​ выделенному диапазону.​​Выделите ячейку или диапазон​​ щелкните ярлычок другого​​ актуальными справочными материалами​ это не то​
            3. ​ в сформированной таблице​ “а”,- говорите и​ тогда такой пример?​
            4. ​: Я так делаю​​ Выделите строку, скопируйте​​: sofi, честно -​​ ячейки с данными,​​ в зависимости от​ тех случаев, когда​

            ​Вырезанные ячейки переместятся на​.​​ клавиш CTRL+V.​​При копировании значения последовательно​​ ячеек с данными,​​ листа или выберите​