Excel 2007: именованные диапазоны
 | Сегодня мы продолжаем работу с формулами — главным интеллектуальным наполнением электронной таблицы. От формул зависит практически все: и полезность документа, и удобство его использования. Одна неправильно составленная формула способна перечеркнуть все усилия по разработке бухгалтерского отчета, исказить результаты длинной последовательности вычислений. Создавать и редактировать формулы мы уже научились. Теперь пора сосредоточить усилия на работе со сложными формулами. Прежде всего мы должны выяснить ключевые проблемы при их создании, обозначить круг инструментов Excel 2007 для решения этих проблем и последовательно познакомиться с каждым из этих инструментов на практическом примере. А начнем мы с важнейшего вопроса — способов адресации ячеек на базе именованных диапазонов. Николай КАРПЕНКО, канд. техн. наук, доцент кафедры прикладной математики и информационных технологий Харьковской национальной академии городского хозяйства |
Ошибочно полагать, что в бухгалтерских расчетах сложные формулы не нужны, что достаточно научиться суммировать ячейки, перемножать колонки — и все задачи бухучета будут решены. Формулы не только выполняют конкретные арифметические действия — это прежде всего средство для реализации алгоритма решения задачи. А этот алгоритм может быть любым — от примитивного перемножения таблиц до расчетов с множеством проверок, с анализом нескольких вариантов вычислений и т. д. Основываясь на своем опыте, могу сказать, что практически в любой таблице можно (и нужно!) использовать формулы для контроля вводимых значений, для проверки промежуточных результатов, для анализа вычислений и вывода предупреждений о возможных ошибках. Для составления таких формул могут понадобиться и встроенные функции, и логические операции, и работа с массивами ячеек. Бояться сложных формул не стоит. Составить любую формулу в Excel 2007 не составит труда. Нужно только разобраться в некоторых моментах.
Основные проблемы при работе
с формуламиЯ обозначил бы три проблемы, которые возникают при создании формул. Особенно если речь идет о любом мало-мальски продвинутом выражении.
1. Работа с адресами.
Большинство ошибок в электронных таблицах связано с неправильным использованием адресов. Это могут быть ошибочная ссылка, диапазон ячеек или некорректное применение абсолютной и относительной адресации. В вопросе работы с адресами есть и другая проблема: длинную формулу с множеством ссылок трудно читать и анализировать. Особенно если эти ссылки указывают на разные листы рабочей книги. Каждый раз, встретив выражение «=Лист1!A1*1,2-Лист2!A1*Лист2!B1+Лист3!A1», вам придется вспомнить, какие данные записаны в ячейку «A1» на листе «Лист1»? Что находится в ячейке «B1» листа «Лист2»? Способом решения большинства проблем с адресами является использование именованных диапазонов.2. Использование встроенных функций.
Думаю, здесь уместнее сказать о недостаточном их применении. Excel 2007 предлагает своим пользователям несколько сотен встроенных функций. Многие из них очень полезны в работе бухгалтера. К сожалению, во многих случаях дальше функции суммирования дело не идет… И это действительно проблема! Есть масса бухгалтерских задач, которые в принципе нельзя решить без встроенных функций. В качестве выхода из ситуации Excel предлагает великолепный инструмент — построитель формульных выражений, или Мастер функций. Его применение максимально упрощает работу с функциями и минимизирует возможные ошибки при их написании.3. Контроль за промежуточными вычислениями.
Как только формула готова, тут же возникает вопрос — а правильно ли она работает? Как только в длинной формуле возникла ошибка, сразу встает проблема: где именно, в какой части выражения это произошло? Справиться с этой ситуацией можно двумя способами. Первый — применить прием формирования формулы по частям. Второй — использовать окно контрольного значения.Основные проблемы при работе с формулами мы очертили. Думаю, план действий на ближайшее время понятен. И первым в нашем списке стоит проблема
адресации. Чтобы познакомиться с одним из способов ее решения, мы возьмем в качестве примера документ прайс-лист из предыдущей статьи.Напомню, что этот документ состоит из двух таблиц. Основная таблица расположена на листе «
Прайс», ее форма показана на рис. 1. В этой таблице исходными данными служат гривневые цены (колонка «С»). Цены в валюте пересчитаны с учетом текущих курсов. Их значения записаны во вспомогательной таблице на листе «Спр» (рис. 2, ячейки «B4», «B5»). Мелкооптовая, оптовая и специальная цена определяется с учетом скидок, которые также находятся на листе «Спр» (рис. 2, ячейки «B7», «B8», «B9»). Кроме того, в рабочей таблице «Спр» есть данные о текущей дате (ячейка «A1») и дате корректировки курсов валют (ячейка «A2»). В прошлый раз мы построили прайс, комбинируя в расчетных формулах абсолютную и относительную адресации. Например, выражение для расчета специальной цены товара «Блокнот» (ячейка «H2» на листе «Прайс») выглядела так: «=C2*(1-Спр!$B$9)». Анализировать даже такую простую формулу уже становится неудобно. Намного проще было бы прочитать запись:«
=C2*(1-СкидкаОпт)». 
Или такой пример. На листе «
Доход» в ячейке «D50» записана сумма дохода. На листе «Расход» в ячейке «D175» находится значение расхода. Тогда формула для расчета прибыли выглядит так: «=Доход!D50-Расход!D175». Гораздо приятнее было бы привести формулу к виду: «=Доход-Расход». Читать и понимать ее будет гораздо проще. А добиться этого очень просто: нужно присвоить ячейкам таблицы имена.
Работа с именованными диапазонами
Человеку свойственно помнить не обезличенные координаты, а их понятные
названия. Как вы отнесетесь, например, к покупателю, который спросит: «Какая оптовая цена на 27-ю позицию в вашем прайсе?» Так же и в электронной таблице. Присвоив ячейкам и блокам мнемоничные* имена, можно существенно упростить восприятие формул. Использование именованных ячеек и диапазонов имеет много преимуществ, а именно:1. Имена позволяют легче понять смысл формулы. Это особенно важно для тех, кто не участвовал в создании таблицы, но вынужден корректировать ее.
2. Имена ячеек и диапазонов запоминаются легче, чем их физические адреса на рабочем листе.
3. Вероятность опечатки при написании имен меньше, чем при вводе адреса ячейки или диапазона.
4. Используя имена, можно быстро перемещаться по рабочему листу. Для этого нужно обратиться к инструменту
«Перейти…» из группы «Найти и выделить». Например, нажимаем комбинацию «Ctrl+G» (или клавишу «F5» ), появится список имен, щелкаем на нужном имени — и Excel мгновенно установит текущую ячейку на указанный адрес.5. При выделении именованной ячейки или диапазона ее имя появляется в поле
«Имя». Так можно лишний раз проконтролировать правильность адресации, а значит, ошибок будет меньше.6. Однажды определив имя, его легко внедрить в формулу, нажав комбинацию
«F3». Это очень удобный способ указывать адреса ссылок на ячейки независимо от того, на каком листе они расположены.*
Мнемоника, мнемотехника (от греч. mneme — память) — совокупность специальных приемов и способов, облегчающих запоминание нужной информации и увеличивающих объем памяти путем образования ассоциаций (связей).Для работы с именами в Excel 2007 есть специальный раздел основного меню — «
Определенные имена». В этом разделе любой ячейке или блоку ячеек можно присвоить имя, которое затем использовать в качестве ссылки.Имена диапазонов должны начинаться с буквы или символа «
_» (подчеркивание). Имя ячейки не может совпадать по написанию с адресом ссылки на ячейку (или на блок ячеек). Например, имя «A1» недопустимо, так как оно совпадает с адресом ячейки «A1».В именах нельзя использовать пробелы.
Если имя состоит из нескольких слов, я вместо пробела ставлю символ «_» (подчеркивание). Вот несколько примеров правильных имен: «Ставка_За_Кредит», «Сумма_Налога», «База_НДС».По умолчанию имена являются абсолютными ссылками.
Совет
Создавая имена, старайтесь делать их максимально понятными. В имени «абв» не больше толку, чем в обычной ссылке на ячейку. Имя должно четко отражать смысл данных в соответствующей ячейке. В то же время имя должно быть кратким. Эти противоречивые требования иногда сложно совместить. Здесь может выручить такой прием: в длинных названиях используйте сокращения. Создавая сокращение, помните, что согласные буквы несут основную информацию, их нужно оставлять. Гласные можно опустить. Одно из возможных правил сокращения — скомбинировать основу из четырех первых и трех последних согласных длинного названия и только затем дополнить сокращение другими символами. Например, фразу «Ставка Налога» по этому правилу можно написать так: «СтвкНлг». На основании этого уже можно получить «СтвкНал», «СтНал» и т. д. По-моему, вполне приемлемо.
Создание именованных диапазонов
Работу с именованными диапазонами начнем с листа «
Спр» (рис. 2). Наша задача — присвоить названия ячейкам с курсами валют и значениями скидок. Вызываем Excel 2007, загружаем файл с прайсом, переходим на лист «Спр». Щелкаем на разделе «Формулы» главного меню. Появится лента из пяти групп иконок: «Библиотека функций», «Определенные имена», «Зависимости формул», «Вычисление», «Решения». Нам нужна группа «Определенные имена» (рис. 3). 
Совет
Быстро попасть в раздел меню «Формулы» можно по комбинации клавиш «Alt+E». Нажимать клавиши нужно на английском регистре.Работу начнем с Диспетчера имен.
1. Делаем активной ячейку «B4», в ней находится значение курса доллара.
2. Щелкаем на иконке «Диспетчер имен». Появится окно, изображенное на рис. 4.
В верхней части окна расположены три кнопки: «
Создать…», «Изменить…», «Удалить». Основную часть окна занимает таблица с перечнем имеющихся именованных диапазонов. Для каждого диапазона можно увидеть его имя, значение в соответствующей ячейке, адрес в рабочей книге, примечание. Диапазонов в рабочей книге может быть много. Все они будут показаны в виде таблицы в центральной части окна. Перемещаться по таблице можно обычным способом: навигационными клавишами или с помощью мыши. В любой момент одна строка таблицы (одно имя диапазона) является текущей. Она выделена инверсным цветом. В нижней части окна находится поле «Диапазон:». В нем Excel показывает адрес в рабочей книге для текущего имени. Кнопка «Фильтр» в правом верхнем углу нужна для отбора имен по условию. Кнопка «Закрыть» завершает работу с Диспетчером имен.3. Нажимаем кнопку «
Создать». Excel покажет окно «Создание имени», изображенное на рис. 5. В нем четыре поля, часть из них уже заполнена. В области «Имя:» Excel подставил значение «USD». Это будет названием диапазона. При желании это имя можно изменить, введя в область «Имя:» новое значение. В поле «Область» Excel установил значение «Книга». Это означает, что имя действует в пределах любого листа текущей рабочей книги. То есть в пределах документа «Прайс». Область с примечанием пока пустует. Сюда мы может напечатать любой комментарий. В нижней части окна расположено поле «Диапазон:», в нем стоит значение «=Спр!$B$4». Это адрес текущей ячейки на рабочем листе. В нашем случае — адрес ячейки со значением курса доллара на листе «Спр». 
4. Нажимаем «
ОК» — имя присвоено.Посмотрим, что мы получили. Переходим на лист «
Прайс», делаем активной ячейку «D2». В ней записана формула пересчета стоимости товара «Блокнот» в доллары. Формула осталась прежней: «=C2/Спр!$B$4».Важно!
Адреса в формулах имеют приоритет перед именами. Если вы создали новое имя, имеющиеся адреса автоматически изменяться не будут.Попробуем заново построить формулу расчета долларовой цены в ячейке «
D2» на листе «Прайс». Для этого делаем так:1. Нажимаем «
=».2. Щелкаем на «
С2», нажимаем «/».3. Щелкаем на ярлычке листа «
Спр», затем на ячейке «B4».4. Нажимаем «
Enter».Смотрим на новую формулу, она выглядит так: «
=C2/USD».Важно!
Excel автоматически подставляет в формулу имеющиеся именованные диапазоны.Продолжим эксперимент.
1. Переходим на лист «
Спр». Становимся на ячейку «B4».2. Щелкаем на иконке «
Диспетчер имен» (рис. 3).3. В окне Диспетчера (рис. 4) переходим внутрь таблицы с именами и щелкаем на строке «
USD». Станет доступной кнопка «Изменить»4. Щелкаем на кнопке «
Изменить». Появится окно, как на рис. 5, с единственным отличием: оно называется «Изменение имени».5. Изменяем значение в поле «
Имя:», печатаем сюда строку «КурсДоллара».6. Нажимаем «
ОК».Мы изменили название диапазона. Переходим на лист «
Прайс». Формула в ячейке «D2» изменилась. Теперь она выглядит так: «=C2/КурсДоллара».Важно!
При изменении названий диапазонов Excel автоматически корректирует соответствующие формулы.Возвращаем ячейке «
Спр!$B$4» название «USD». Теперь прокомментируем несколько важных моментов.Вопрос первый: откуда Excel взял название диапазона «
USD»? Он его нашел в левом соседнем столбце относительно текущей ячейки (применительно к «B4» — это ячейка «A4» (колонка «A»)). Заголовки могут располагаться и над текущей ячейкой. Тогда вступает в силу приоритет их присвоения: вначале Excel пытается взять имена в ячейке слева от текущей. Если имени там обнаружить не удалось, Excel берет его из ячейки сверху (над текущей). В противном случае он оставляет поле имени пустым и предлагает заполнить его с клавиатуры.Вопрос второй касается параметров окна «
Создание имени» (рис. 5). Таких параметров три: «Область», «Примечание» и «Диапазон». С примечанием все понятно, здесь может находиться любой поясняющий текст. С остальными параметрами нужно разобраться.Поле «
Область» представляет собой список. В нашем примере он состоит из таких значений: «Книга», «Прайс», «Спр». То есть в этом списке находится один глобальный параметр «Книга» и затем перечислены все имеющиеся в ней листы. Параметр «Область» определяет сферу действия созданного имени. Если мы укажем «Книга», имя будет действовать в пределах любого листа рабочей книги. Если выбрать конкретный лист, имя действует только в пределах этого листа.
Пример
Мы создали имя «
USD» и указали область действия этого имени — «Книга». Становимся в ячейку «B5» (курс евро) и с помощью Диспетчера формируем для этой ячейки имя «EUR». Но для этого имени ограничим область действия листом «Прайс». Становимся на лист «Спр». В любой ячейке этого листа вводим формулу «=USD». В ячейке появится значение курса доллара (в примере оно равно 8,3 грн. за один доллар). Вводим формулу «=EUR». Результат будет «#ИМЯ?». Это означает, что Excel не распознал введенного имени. Все правильно: на листе «Спр» оно недоступно.Переходим к полю «
Диапазон». При создании имени по умолчанию в это поле Excel ставит адрес текущей ячейки. В большинстве случаев это значение менять не нужно. Но бывают исключения.Важно!
«Диспетчер имен» — единственный инструмент, в котором можно построить имя для трехмерной ссылки. Посмотрим, как это реализовать практически.
Пример
Открываем новую рабочую книгу, становимся в ячейку «
A1».Вызываем «
Диспетчер имен».В поле «
Имя:» вводим название «Ссылка3D».Переходим к полю «
Диапазон:». Сейчас в нем находится адрес текущей ячейки. В нашем случае — это «=Лист1!$A$1».Аккуратно щелкаем мышкой в строке с адресом в конце текста
«=Лист1» и допечатываем его, чтобы получилась такая ссылка: «=Лист1: Лист3!$A$1».Становимся в ячейку «
А2» и вводим формулу для суммирования значений в диапазоне «Ссылка3D». Формула будет такой: «=СУММ(Cсылка3D)».Теперь на листе «
Лист1» в ячейку «A1» пишем любое число, например «10». На листе «Лист2» в ячейку «A1» пишем «20». На листе «Лист3» в ячейку «A1» пишем «30».Переходим на лист «
Лист1». Результат суммирования в «А2» равен «60». Это означает, что функция суммирования выполнила обработку трехмерной ссылки между разными листами.Напишем в любой ячейке листа ссылку в виде «
=Ссылка3D». Результатом будет «#ЗНАЧ!». Этого и следовало ожидать: Excel 2007 не может выполнить операцию над трехмерной ссылкой без специальной обработки (например, в виде функции суммирования).Чтобы удалить имя, вызываем Диспетчер имен. В центральной части окна находим строку с нужным именем, выделяем ее и нажимаем кнопку «
Удалить» (рис. 4).Важно!
Прежде чем двигаться дальше, удалите имя «EUR» на листе «Спр».Кроме Диспетчера имен, в группе «
Определенные имена» есть еще три инструмента. Посмотрим, как они работают. Переходим на лист «Спр», ставим указатель текущей ячейки на «B5» (курс евро). Щелкаем левой кнопкой на иконке «Присвоить имя» (рис. 3). Раскроется окно «Создание имени», изображенное на рис. 5. Все параметры этого окна мы уже знаем, их можно оставить по умолчанию или подкорректировать прямо на месте. Чтобы создать имя, щелкаем «ОК». В левой части иконки «Присвоить имя» находится значок выпадающего списка. Щелкаем на нем — появится меню из двух пунктов: «Присвоить имя» и «Применить имена…». С первым мы знакомы, а вот на втором нужно остановиться подробнее.Возможность применения имен работает с некоторым диапазоном ячеек. По команде «
Применить имена…» Excel просматривает формулы в выделенном диапазоне. Если ссылка в формуле совпадает с адресом имеющегося имени, Excel автоматически заменит ссылку на именованный диапазон. Этот способ экономит время по корректировке формул. Например, вместо того, чтобы вручную изменят ссылки в формулах прайс-листа и затем копировать их, можно было выделить весь диапазон ячеек и выполнить команду «Применить имена…».Иконка «
Использовать в формуле» (рис. 3) нужна для подстановки имен диапазонов в текст формулы. Иконка представляет собой раскрывающийся список. В нем представлены все доступные названия диапазонов. Кроме того, в списке есть специальный пункт «Вставить имена». Вот как пользоваться этими инструментами. Я хочу построить формулу в ячейке «А10» листа «Прайс» (рис. 1). Формула должна показать информацию о текущем курсе доллара относительно гривни. Делаем так:1. Переходим к листу «
Прайс». Ставим указатель текущей ячейки на «А10».2. Печатаем текст «
="Текущий курс USD: "&». Теперь в этом месте нужно вставить ссылку на ячейку «B4» листа «Спр». Я помню, что эта ячейка называется «USD». Щелкаем на иконке «Использовать в формуле», из раскрывшегося списка выбираем «USD».3. Завершаем формулу, напечатав текст
«&" грн. за 1$"&"."».4. Нажимаем «
ОК».В результате мы получили такое выражение: «
="Текущий курс USD: "&USD&" грн. за 1$"&"."».Имена не всегда можно показать в виде выпадающего списка. Поэтому среди пунктов меню «
Использовать в формуле» есть строка «Вставить имена». Щелкаем на ней левой кнопкой мыши. Раскроется окно, изображенное на рис. 6. В нем находим имя, щелкаем на нем левой кнопкой мыши. Название имени попадет в формулу. 
Последний инструмент в группе «
Определенные имена» называется «Создать из выделенного фрагмента» (рис. 3). Он удобен для группового присвоения имен сразу нескольким ячейкам. Действие этой иконки похоже на работу Диспетчера имен, но с несколько другим интерфейсом. Вот как ею воспользоваться.
Пример
Мне нужно создать имена для трех видов скидок. Данные записаны на листе «
Спр» в ячейках «B7», «B8», «B9». Слева в ячейках расположены названия скидок. Причем эти названия имеет смысл использовать в качестве имен для создаваемых диапазонов. Делаем так:1. Выделяем блок «
A7: B9». Названия диапазонов в выделенную область нужно включить обязательно!2. Щелкаем на иконке «
Создать из выделенного фрагмента». Появится окно, как на рис. 7. 
3. Для нашего примера включаем флажок «
в столбце слева» (действует по умолчанию). Это означает, что имена Excel возьмет из ячеек, расположенных в левой части выделенного диапазона (у нас это колонка «А», ячейки «A7», «A8», «A9»).4. Нажимаем «
ОК». Просматриваем адреса ячеек. Теперь «B7» называется «МО», «B8» — «Опт», «B9» — «Спец».Завершая тему работы с именованными диапазонами, хочу сказать о незначительном отличии между Excel 2007 и Excel 2003. При написании формул в Excel 2003 в качестве имен можно было использовать заголовки строк или столбцов. Например, в табличном документе есть колонка с названием «
Сумма». В ячейках по этой колонке стоят числовые значения. Для определенности будем считать, что это блок «B2:B4». Тогда формула «=СУММ(B2:B4)» сможет посчитать сумму по данному диапазону. Эту же формулу в Excel 2003 можно было написать так: «=СУММ(Сумма)», результат получим одинаковый. В Excel 2007 такой способ адресации применять нельзя. На мой взгляд, это не является недостатком программы. Зная о такой возможности я, например, применил ее на практике всего несколько раз за многие годы работы с Excel. И то скорее из спортивного интереса… К тому же способ адресации через заголовки строк и колонок чреват проблемами. Кто может гарантировать правильный результат, если в таблице появится еще одна колонка с названием «Сумм»?.. Отказавшись от такого экзотического способа адресации, разработчики Excel фактически устранили один из источников потенциальных ошибок.Итак, мы разобрались с возможностями применения именованных диапазонов в Excel 2007. Такая адресация ячеек обладает целым рядом преимуществ. Запись формул упрощается. Абсолютная адресация ссылок минимизирует ошибки в создании формул. Excel 2007 для работы с диапазонами появились удобные средства, из которых наиболее функциональным является Диспетчер имен. При необходимости создать большое количество имен можно воспользоваться инструментом «
Создать из выделенного фрагмента». Словом, я не вижу причин, чтобы не применять на практике именованные диапазоны как можно чаще. По себе скажу: когда я начал пользоваться именами, мои таблицы стали намного понятнее. Одного этого аргумента для меня было достаточно, чтобы именованные диапазоны стали постоянным спутником каждой создаваемой мной таблицы.А нам для завершения темы работы с формулами осталось познакомиться с работой встроенных функций, методами контроля вычислений и овладеть приемами быстрого создания сложных формул. Это будет темой нашей следующей статьи.
Жду ваших писем, предложений и замечаний на
bk@id.factor.ua, nictomkar@rambler.ru или на форуме редакции www.bk.factor.ua/forum . Успешной работы!
