Уокенбах Формулы в Excel

• При вводе имен регистр значения не имеет. AnnualTotalзначит то же самое, что и annualtotal. Excel сохраняет имяточно в таком же виде, в каком вы вводите его первоначально. Но записывая имяв формуле, вы можете использовать любой регистр.

Excel использует несколько так называемых внутренних имен. Вы можете присваивать внутренние имена Excel ячейкам и диапазонам. Но я бы не рекомендовал вам этого делать. Итак, присваивая имя, вам следует избегать следующих имен: Print_Area, Print_Wes, Consolidate_Area, Database, Criteria, Extract, FilterDatabase и SheetJTrtle.

Поле Имя имеет и еще одну функцию. С его помощью вы можете быстро активизировать именованную ячейку или диапазон, как показано на рис. 3.2. Для того чтобы выделить ячейку или диапазон, щелкните в поле Имя и выберите нужное имя из списка. Будет выделена соответствующая ячейка или диапазон. Как ни странно, для активизации поля Имя не предусмотрена комбинация клавиш. Иными словами, вы не сможете получить доступ к этому полю с помощью клавиатуры; сделать это можно только используя мышь. А вот после того, как вы щелкните в поле Имя, для указания имени достаточно использовать клавиши со стрелками и <Enter>.

Автоматическое присвоение имени

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

Для того чтобы присвоить имена, используя текст смежных ячеек, выделите этот текст и ячейки, которым должно быть присвоено имя (это могут быть как отдельные ячейки, так и диапазоны ячеек). Текст имен должен находиться в смежных ячейках относительно ячеек, которым вы хотите назначить имена (вы можете выделить несколько ячеек). Выберите Вставка^Имя^Создать или нажмите комбинацию клавиш <Ctrl+Shift+F3>. Появится диалоговое окно Создать имена, показанное на рис. 3.4.

Pwc. 3.4. Диалоговое окно Создать имена

Опции в диалоговом окне расставлены на основе проведенного программой анализа выделенного диапазона. Например, если Excel обнаруживает текст в первой строке выделенного диапазона, программа предлагает вам создать имена в строке выше. Если Excel задал опции неправильно, то вы можете изменить предложенный вариант вручную. Щелкните на кнопке ОК, и Excel создаст имена. Обратите

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

Если в результате использования текста смежной ячейки получится некорректное имя, Excel изменить это имя так, чтобы оно было корректным. Например, если ячейка содержит текст Net Income (использование такого текста в качестве имени некорректно, поскольку в нем присутствует пробел), Excel преобразует пробел в символ подчеркивания и создаст имя Net_lncome. Если же вместо текста

смежная ячейка содержит значение или формулу, программа не сможет создать корректное имя. В таком случае Excel просто не создаст никакого имени.

Внимательно проверяйте имена, автоматически создаваемые программой. Иногда применение команды Вставка^Имя^Создать дает вовсе не тот результат, который вы ожидали. На рис. 3.5представлена небольшая таблица, состоящая из текста и значений. Выделите всю таблицу, выберите ВставкамИмя^Создать и примите предложения программы (опции в строке выше и в столбце слева). В результате имя Products будет относиться не к диапазону А2 :А6, как вы ожидали, а к диапазону В2:Сб. Если верхняя левая ячейка выделенного диапазона содержит текст, и вы выберете опции в строке выше и в столбце слева, Excel использует это имя для всего набора данных, включая верхнюю строку и левый столбец. Поэтому, прежде, чем принять опции, предлагаемые командой, убедитесь, что этот выборсоответствует вашим замыслам.

Имя столбца или строки

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

Столбец В вы можете назвать, например, DailySales. Если этот диапазон расположен на рабочем листе ЛистЗ, ссылка будет выглядеть следующим образом:

=ЛистЗ!$В:$В

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

=CyMM(DailySales)

Имена, созданные программой Excel

Excel создает и свои собственные имена. Например, если вы зададите область печати для листа, Excel создаст имя Print_Агеа. Если же вы назначите повторяющиеся строки или столбцы, которые должны быть напечатаны, программа создаст имя Print_Titles на уровне рабочего листа. Когда вы выполняете запрос, который возвращает данные в рабочий лист, Excel назначает этим данным свои имена. Множество надстроек, входящих в пакет Excel, создают скрытые имена (см. врезку "Скрытые имена").

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

Скрытыеимена

Некоторые макросы и надстройки Excel создают скрытые имена Эти имена присутствуют в рабочей книге, но не появляются в диалоговом окне Присвоение имени или в поле Имя. Надстройка "Поиск решения", например, создает целый ряд скрытых имен. Чаще всего скрытые имена можно просто игнорировать. Но они могут стать причиной появления некоторых проблем. Если вы копируете лист из одной рабочей книги в другую, вместе с листом копируются и скрытые имена. При этом скрытые имена могут образовать ссылку, которую очень трудно обнаружить.

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

Если вам необходим эффективный инструмент для работы с именами, используйте утилиту Name Lister, которая является частью пакета Power Utility Pak. Эта утилита отображает полный список имен. Кроме того, Name Lister позволяет фильтровать имена несколькими способами. Вы можете просмотреть только имена уровня рабочего листа или же только связанные имена. Данная утилита поможет найти и удалить имена, присвоенные неправильному диапазону.

May

Notepads

&**«

Присваивание имен внескольких листах

Имена ячейкам и диапазонам могут быть присвоены в нескольких рабочих листах с помощью "трехмерной" ссылки. Но вы не можете просто выделить диапазон и ввести имя в поле Имя. Для того чтобы назначить имя в нескольких листах, необходимо использовать диалоговое окно Присвоение имени. "Трехмерная" ссылка будет выглядеть следующим образом:

ПервыйЛист:ПоследнийЛист!СсылкаНаДиапазон

На рис. 3.7 продемонстрирован пример, в котором имя {DataCube),назначенное диапазону А1: СЗ,распространяется на листы Лист1,Лист2 иЛистЗ.

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

Для того чтобы назначить имя DataCube диапазону А1: СЗ, который распространяется на листы Лист1, Лист2 иЛистЗ, следуйте приведенным ниже инструкциям:

1.Активизируйте Лист1.

2.Выберите Вставка^Имя^Присвоить или нажмите комбинацию клавиш <Ctrl+F3>. Появится диалоговое окно Присвоение имени.

3.Введите DataCube в текстовое поле Имя.

4.Активизируйте поле Формула и нажмите клавишу <Del>, чтобы удалить ссылку на диапазон.

5.Выделите диапазон А1: СЗ на листе Лист1. В поле Формула появится следующая запись:

=Лист1!$А$1:$С$3

6.Удерживая нажатой клавишу <Shift>, щелкните на ЛистЗ. Вы заметите, что программа по непонятным причинам меняет ссылку на диапазон ссылкой на одну ячейку. На данном этапе запись будет выглядеть так:

='Лист!:ЛистЗ'!$А$1

7.Выделите диапазон Al :C3 на листе Лист1 еще раз. В поле Формула появится следующая запись:

='Лист1:ЛистЗ'!$А$1:$С$3

8.Теперь, когда поле Формула содержит нужную ссылку на диапазон, щелкните на кнопке ОК. Диалоговое окно Присвоение имени будет закрыто.

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

=CYMM(DataCube)

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

Если вы вставите новый лист в рабочую книгу, в которой используются имена, относящиесяк нескольким листам, эти имена будут распространяться и на новый лист в том случае, если этот лист находится между первым и последним листом, заданным в определении имени. В предыдущем примере рабочий лист, вставленный между Лист1 и Лист2, будет включен в диапазон DataCube. Лист, вставленный перед Лист1 или после ЛистЗ, не будет включен вдиапазон.

Если вы удалите первый или последний лист, включенный в определение имени, Excel автоматически изменит запись в поле Формула. В рассматриваемом нами примере удаление Лист1 приведет к следующим изменениям в поле Формула имени DataCube:

='Лист2:ЛистЗ'!$А$1:$С$3

Область действия имен

Имя, которое вы присваиваете ячейке или диапазону, обычно может использоваться в любом из листов рабочей книги. Например, если вы присвоили имя RegionTotal ячейке А1 листа Лист1, это имя может использоваться в любой формуле на любом рабочем листе. Такое имя называется именем уровня рабочей книги (или глобальным именем). По умолчанию все создаваемые имена ячеек и диапазонов являются именами уровня рабочей книги.

Создание имен уровня рабочего листа

А что, если вам нужно использовать какое-либо имя (например, RegionTotal) в каждом из листов рабочей книги? В таком случае вы должны создать имена уровня рабочего листа (которые иногда называют локальнымиименами).

Для того чтобы создать имя RegionTotal на уровне рабочего листа, активизируйте лист, в котором должно задаваться это имя, и выберите Вставка^Имя^Присвоить. Появится диалоговое окно Присвоение имени. В поле Имя перед именем уровня рабочего листа следует задать имя листа, за которым следует восклицательный знак. Например, для того чтобы назначить имя RegionTotal на рабочем листе Лист2, необходимо активизировать Лист2 и ввести в поле Имя диалогового окна Присвоение имени следующуюзапись:

Лист2!RegionTotal

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

'Marketing Dept'!RegionTotal

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

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

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

Комбинирование имен уровня рабочего листа с именами уровня рабочей книги

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

Например, вы можете задать имя Сумма на уровне рабочей книги для ячейки листа Лист1 и имя Лист2!Сумма на уровне рабочего листа. Когда Лист2 активен, Сумма— это имя на уровне рабочего листа. Когда активны другие листы книги, Сумма является именем, заданным на уровне рабочей книги. Совсем запутались? Неудивительно. Дабы избежать путаницы, не применяйте одно и то же имя на уровне листа и рабочей книги.

Ссылка на имя из другой рабочей книги

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

Например, в следующей формуле используется имя MonthlySales, назначенное в рабочей книге под именем Budget. x l s (предполагается, что эта рабочая книга открыта):

=СРЗНАЧ(Budget.xls!MonthlySales)

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

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

Создание списка имен

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

Вероятно, вы захотите создать список всех имен (и соответствующих им адресов) рабочей книги. Для того чтобы создать такой список, сначала выделите ячейку в пустой области рабочего листа (если вы создадите список имен, состоящий из двух столбцов, то он отображается поверх находящейся в ячейках информации). Затем выберите ВставкаОИмя^Вставить (или нажмите клавишу <F3>). Появится диалоговое окно Вставка имени (рис. 3.8), в котором будут перечислены все заданные имена. Для того чтобы вставить

список имен, щелкните на кнопке Все имена.

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

(нажмите <F2>, а затем <Enter>). В результате строка становится формулой. Если имя относится к отдельной ячейке, формула отображает текущее значение этой ячейки. Если же имя относится к диапазону, формула возвращает ошибку #ЗНАЧ !.

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

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

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

=CyMM(UnitsSold)

Когда имя, заданное на уровне рабочего листа, используется в формуле того листа, в котором это имя было задано, имя рабочего листа не следует указывать перед именем диапазона. Если же такое имя используется в другом листе рабочей книги, необходимо задавать полное имя (имя рабочего листа, восклицательный знак и имя диапазона). Например, если UnitsSold— имя, заданное на уровне рабочего листа Лист1, формула для вычисления суммы диапазона на любом другом рабочем листе будет выглядеть так:

=СУММ(Лист1!UnitsSold)

Вводя формулу, вы можете выбрать Вставка^Имя1^Вставить (или просто нажать клавишу <F3>). Появится диалоговое окно Вставка имени. Выберите имя из списка, щелкните на кнопке ОК, и Excel вставит это имя в формулу. Как я уже говорил, в списке диалогового окна Вставка имени представлены все имена уровня рабочей книги и имена уровня рабочего листа для активного листа.

Если в формуле вы использовали имя, которого не существует, Excel выдаст ошибку #ИМЯ?, тем самым сообщая вам, что не может найти имя, которое вы хотите использовать в формуле. Чаще всего появление такого сообщения свидетельствует о допущенной вами ошибке при написании имени.

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

В качестве оператора пересечения диапазонов в Excel служит единичный символ пробела. Например, следующая формула служит для вычисления суммы ячеек на пересечении двух диапазонов — В 1 : С20 и А8 :D8:

=СУММ(В1:С20 A8:D8)

Данные диапазоны пересекаются в ячейках В8 и С8.

Оператор пересечения работает и для именованных диапазонов. На рис. 3.9 представлена таблица, содержащая именованные диапазоны, в которых заданы названия для столбцов и строк таблицы. Например, имя January относится к диапазону В2 : Е2, а имя North — к диапазону В2 : В13. Следующая формула возвращает содержимое ячейки на пересечении диапазонов January и North:

=January North

Интуитивно понятные формулы? Почему бы инет!

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

И тем не менее, Excel воспринимает названия строк и столбцов. Следующая формула, например, возвращает сумму значений строки, которая носит название January.

=СУММ(January)

Можно использовать и названия столбцов. Приведенная ниже формула возвращает сумму значений в столбце Region1:

=СУММ(Region 1)

В формуле можно использовать и несколько таких названий одновременно. Следующая формула возвращает 2787 — значение на пересечении диапазонов Februarys Region2.

=February Region 2

Работая с интуитивно понятными формулами, вы не тратите времени и сил на то, чтобы назначить диапазонам имена. Но эта функция не всегда работает так, как требуется. Так, формулы с "псевдоименами" иногда не вычисляются при изменении данных. Более того, случается, что две совершенно одинаковые формулы возвращают разные результаты. Существует и еще одна проблема: невозможно предсказать наверняка, как Excel интерпретирует конкретное название. И наконец, в Excel нельзя использовать более 32 764 интуитивных формул. Попробуйте превысить допустимый предел — и программа, вероятно, выйдет из строя.

Я настоятельно рекомендую не прибегать к этой функции программы и всегда использовать действительные имена ячеек и диапазонов. Для того чтобы отключить функцию использования названий диапазонов, выберите Сервис^ Параметры. Затем перейдите на вкладку Вычисления диалогового окна Параметры и отключите параметрдопускать названиядиапазонов.

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

Обратите внимание на то, что в ячейке D5 отображено значение из диапазона MyData,которое соответствует строке формулы. Точно так же, если вы введете ту же формулу в строки с 3 по 8, формула будет отображать соответствующее значение из диапазона MyData. Excel производит неявное пересечение, используя диапазон MyData и строку, содержащую формулу. Это то же самое,

как если бы вычислялась следующая формула: =MyData 5:5

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

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

тот же результат, если в ячейке D5, будет находиться следующая формула (в которой не используется имя диапазона):

=$В$2:$В$8

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

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

и строк от January до March:

=СУММ((North January):(West March))

Часть I. Основные сведения

Ссылка наотдельную ячейку именованного диапазона

Для того чтобы возвратить значение ячейки диапазона, можно использовать функцию Excel ИНДЕКС. Предположим, некий диапазон А1.-А50 носит имя DataRange. Следующая формула возвращает второе значение (значение ячейки А1) диапазонаDataRange:

=ИНДЕКС(DataRange;2)

Второй и третий аргументы функции ИНДЕКС — дополнительные. Однако один из этих аргументов всегда должен быть задан. Второй аргумент (используемый в приведенной формуле) обозначает смещение строки в диапазоне DataRange.

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

= И Н Д Е К С ( D a t a R a n g e; ; 2 )

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

=ИНДЕКС(DataRange;4;5)

Применение имен вуже существующих формулах

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

Для этого выделите диапазон, содержащий формулы, которые должны быть преобразованы. Затем выберите Вставка^Имя^Применить. Появится диалоговое окно Применение имени, показанное на рис. 3.11. В диалоговом окне Применение имени выберите те имена, которые вы хотите применить в формулах. В формулах будут применены только те имена, которые вы указали.

Для того чтобы применить новое имя ко всем формулам рабочего листа, выделите одну ячейку перед тем, как выполнить команду ВставкамИмя"=>Применить.

Параметр Игнорировать тип ссылки отвечает за то, как Excel заменяет адрес диапазона именем. Обычно, имя ячейки или диапазона задается как абсолютная ссылка. Если параметр Игнорировать тип ссылки включен, Excel применит имя только в том случае, если ссылка в формуле полностью соответствует типу диапазона. В большинстве случаев, когда вы применяете имена, удобнее игнорировать тип ссылки.

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