Использование опции Создать Из Выделенного для определения именованного диапазона

Для примера возьмем рабочий лист файла CitySales.xls с данными о продажах по городам России (рис. 1.5).

Рис. 1.5 Присвоение ячейкам имен городов позволит в дальнейшем использовать в формулах названия этих населенных пунктов

Допустим, что мы хотим присвоить имя диапазону B2:B15. Чтобы сделать это, необходимо выделить ячейки A2:B15 и кликнуть Создать из выделенного на ленте во вкладке Формулы в группе Определенные имена (рис. 1.6).

 

Рис. 1.6 Выбрать Создать Из Выделенного

Появится диалоговое окно, в котором необходимо выбрать пункт В cтолбце cлева(рис. 1-7).

Рис. 1.7 Выбрать В Столбце Слева

Excel использует значения ячеек первого столбца в качестве имен для соответствующих ячеек второго столбца выделенного диапазона. Ячейке B2 присвоено имя Владимир, B3 — Иркутск и т.д. Согласитесь, что в данном примере присвоение имени каждой ячейке с помощью поля «Имя» было бы гораздо более утомительным занятием (особенно при большом количестве ячеек). Кликнув стрелочку в правой части поля «Имя», вы увидите список всех созданных имен.

Использование Диспетчера имен для создания имен диапазонов

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

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

Допустим, вы хотите назначить имя диапазон1 группе ячеек A2:B7. Чтобы сделать это, наберите диапазон1 в поле «Имя» и укажите нужные ячейки в поле «Диапазон» (или наберите =A2:B7). После нажатия ОК имя будет присвоено. Диалоговое окно Создание имени будет выглядеть следующим образом (рис. 1.9).

Рис. 1.9 Диалоговое окно «Создание имени» после присвоения имени диапазону

Поле «Область» позволяет выбрать область действия имени (книга или любой ее лист). Мы обсудим эту опцию далее (см. В чем различие между именем в пределах книги и в пределах рабочего листа?). Сейчас выберите область «Книга». Вы также можете добавлять комментарии в поле «Примечание».

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

В Microsoft Excel 2007, 2010 предусмотрен простой способ редактирования или удаления имен диапазонов, которого не было ранее. Просто откройте Диспетчер имен на вкладке Формулы. Вы увидите список всех созданных имен. К примеру, для файлаCitySales.xls список будет выглядеть следующим образом (рис. 1.10).

Рис. 1.10 Диалоговое окно Диспетчер имен для файла CitySales.xls

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

Пример совместного использования функций Excel поискпоз, макс и впр

Начало формы

Рейтинг пользователей:  / 3  Худший Лучший 

Конец формы

Файл taxpayers.xls (рис. 4.3) содержит список налогоплательщиков с данными об уровне их годового дохода. Данные не отсортированы по доходу. Необходимо составить формулу, которая возвращает имя налогоплательщика с наивысшим уровнем дохода и с пятым наибольшим по величине уровнем дохода.

Рис. 4.3 Использование функций Excel МАКС, ПОИСКПОЗ и ВПР для поиска и отображения наибольшего значения в списке

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

• Используем функцию МАКС для определения наибольшего уровня дохода.

• Используем функцию ПОИСКПОЗ для определения номера строки, которая содержит имя налогоплательщика с наибольшим уровнем дохода.

• Используем функцию ВПР для вывода имени налогоплательщика.

Диапазону с данными о доходах (C12:C412) присвоим имя Доходы. Диапазону с данными для функции ВПР (A12:C412) присвоим имя Просмотр.

В ячейке C9 вводим формулу МАКС(Доходы) и определяем наивысший уровень дохода (22 млн. руб.).

Затем в ячейке C8 используем формулу ПОИСКПОЗ(C9;Доходы;0) и определяем номер налогоплательщика с наивысшим доходом (345). Мы используем тип сопоставления = 0, т.к. доходы никак не упорядочены.

Наконец, в ячейке C6 используем формулу ВПР(C8;Просмотр;2), которая возвращает имя налогоплательщика из второй колонки просматриваемого диапазона. Это Alex Rodriguez.

Чтобы вывести имя налогоплательщика с пятым наибольшим по величине уровнем дохода, необходимо воспользоваться функцией НАИБОЛЬШИЙ. Синтаксис функции:НАИБОЛЬШИЙ(массив;k). В таком виде функция выводит k-й наибольший элемент массива.

Таким образом, формула НАИБОЛЬШИЙ(Доходы;5) в ячейке D9 выдает пятый наибольший по величине уровень дохода (12,6 млн. руб.).

Далее действуем, как описано выше и получаем имя Derek Jeter в качестве налогоплательщика с пятым наибольшим по величине уровнем дохода.

Если необходимо определить пятый наименьший по величине уровень дохода, то следует воспользоваться функцией НАИМЕНЬШИЙ(Доходы;5).