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

4.Выделите всеимена столбца В.

5.Щелкните накнопке Копировать, расположенной напанели Стандартная.

6.Выделите ячейкуА1.

7.Выполните команду Правка^Специальная вставка. Появится диалоговое окно Специальная вставка.

8.Выставьте переключатель Значения и щелкните накнопке ОК.

9.Отмените режим вставки, нажав <Enter> или <Esc>.

10.Удалите столбец В.

Формулы против функции автозаполнения

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

Например, расположить список значений от 1 до 100 в диапазоне AirAlOO МОЖНО, применив формулу. Вячейку А1 ВЫвводите 1, в ячейку А2— формулу =А1+1, а затем копируете эту формулу в оставшиеся 98 ячеек диапазона.

Используя функцию автозаполнения, высможете обойтись без формул. Для того чтобы заполнить упомянутый диапазон последовательностью от 1 до100, введите 1 в ячейку А1, ав ячейку А2 — 2. Затем выделите диапазон А1:А2 и перемешайте маркер диапазона вниз до ячейки АЮ0. (Маркер диапазона — это небольшой квадратик в правом нижнем углу активной ячейки.) Когда вы проводите автозаполение таким образом, Excel анализирует содержимое выделенных ячеек ииспользует полученную информацию для заполнения остальных ячеек диапазона. Если ячейка А1 содержит 1, а ячейка А2—3, программа улавливает принцип, по которому составляется последовательность, и заполняет остальные ячейки таким образом: 5, 7, 9 и т.д. Та жетехника применяется и длясоздания убывающих последовательностей (10, 9, 8 ит.д.), и списков дат. Если жепрограмме неудается уловить принцип, по которому составляется последовательность, Excel выполняет линейную регрессию и вставляет значения всоответствии с полученной тенденцией.

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

Скрытие формул

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

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

1.Выделите формулу или формулы.

2.В меню Формат выберите команду Ячейки, а затем перейдите навкладку Защита.

3.Установите флажок Скрыть формулы, как показано нарис. 2.6.

4.В меню Сервис откройте подменю Защита и выберите команду Защитить лист. Для того чтобы никто кроме вас не мог отменить защиту листа, не забудьте установить

Нередко программа обнаруживает ошибку во вводимых формулах. В табл. 2.2. представлены типичные ошибки, которые появляются в ячейке, содержащей формулу. Формулы могут возвращать значения ошибки и в том случае, если в ячейке, ссылку на которую содержитэта формула, присутствует значение ошибки. Это — так называемый волновой эффект: одна единственная ячейка, содержащая ошибку, может повлиять на множество ячеек, в которых расположены зависимые от этой ячейки формулы.

Таблица 2.2.Типичные ошибкивExcel

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

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

Работа сциклическими ссылками

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

Циклическая ссылка появляется в случае, если формула содержит прямую или косвенную ссылку на свое собственное значение. Например, если в ячейку A3 вы введете формулу =А1+А2+А3, то создадите циклическую ссылку, поскольку в формуле, расположенной в ячейке A3, присутствует ссылка на эту ячейку. В результате после каждого вычисления значения ячейки A3 это значение должно вычисляться снова и снова, поскольку меняется значение A3. Таким образом вычисление происходило бы бесконечно долго и формула никогда не могла бы возвратить окончательный ответ.

Рис. 2.7. Вот так Excel сообщает пользователю о появлении циклической ссылки

Если вы ввели формулу, содержащую циклическую ссылку, Excel отображает диалоговое окно с тремя вариантами действий:

•Если циклическая ссылка создана случайно, щелкните на кнопке ОК. Будет открыта панель инструментов Циклические ссылки со справкой по ее использованию.

•Для получения сведений о циклических ссылках щелкните на кнопке Справка.

•Чтобы оставить формулу без изменений, щелкните на кнопке Отмена.

Вы, конечно же, предпочтете исправить циклическую ссылку и щелкните на кнопке ОК. Excel откроет панель инструментов Циклические ссылки (рис. 2.8). На панели Циклические ссылки выберите первую ячейку из раскрывающегося списка Найти циклическую ссылку. Затем проанализируйте формулу, которую содержит эта ячейка. Если вы не уверены в том, что именно эта формула привела к появлению циклической ссылки, выберите в списке Найти циклическую ссылку следующую ячейку. Продолжайте перебирать варианты формул, пока в строке состояния не исчезнет сообщение о цикличности.

_№jjE3

Рис. 2.8. Панель инструментов Циклическиессылки

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

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

Excel не оповещает пользователя о появлениициклической ссылки, если активна опция Итерации. Проверитьсостояние этой опции можно на вкладкеВычисления диалогового окна Параметры. Если опция Итерации активна, Excel выполняет циклические вычисления столько раз, сколько задано в поле Предельное число итераций, или же пока значение не изменится на меньше, чем 0,001 (или любое другое значение, указанное в поле Относительная погрешность). Рекомендую держать опцию Итерации отключенной. Тогда программа будет предупреждать вас о появлении циклических ссылок. Ведь чаще всего существование циклической ссылкисвидетельствует об ошибке, которую необходимо исправить.

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

Функция подбора параметра

Многие электронные таблицы содержат формулы, которые позволяют задавать вопросы типа: "Какова будет сумма общей прибыли, если объем продаж вырастет на 20 процентов?" Если вы задали формулу правильно, то можете изменить значение одной ячейки и увидеть, как изменится значение объема продаж в другой ячейке.

Функция подбора параметра очень эффективна при совместном применении с формулами. Если вы знаете, какой должен быть результат, программа сама подскажет вам значения одной и более ячеек, необходимые для достижения этого результата. Иными словами, вы можете задать такой вопрос: "На сколько процентов должен вырасти объем продаж, чтобы общая прибыль составила $1,2 миллиона?"

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

Пример подбора параметров

На рис. 2.9 представлена таблица, предназначенная для расчета суммы займа под залог недвижимости. В этой таблице есть четыре ячейки для ввода данных (С4 : С7) и четыре ячейки с формулами (СЮ : С13). Формулы вычисляют различные значения, используя ячейки для ввода значений. В ячейках расположены следующие формулы:

Выберите Сервис^Подбор параметра. Появится диалоговое окно Подбор параметра, как показано на рис. 2.10. Заполнить окна этого диалогового окна— все равно, что составить предложение: Установить в ячейке СИ значение 1200, Изменяя значение ячейки С4. Введите в диалоговое окно всю необходимую информацию, задав ссылки на ячейки или указав их мышью.

Excel мгновенно сообщает о том, что решение найдено, отображая окно Результат подбора параметра. В окне вы увидите подбираемое значение и текущее значение. В нашем примере программа нашла точное значение. Подбираемое значение было помещено в ячейке С4 ($204,425). В результате получается желаемая оплата $1200 в месяц. Окно Результат подбора параметра предоставляет следующие варианты выбора:

•Щелкните на кнопке ОК, чтобы заменить исходное значение найденнымзначением.

•Щелкните на кнопке Отмена, и ваша таблица вернется к своему исходному состоянию, в котором она находилась до применениякоманды Сервис^Подбор параметра.

Еще немного оподборе параметра

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

Как и любая другая программа, Excel имеет ограниченную точность вычислений.Для того чтобы в этом убедиться, введите в ячейку А2 формулу =А1Л2. Затем выберите Сервис^Подбор параметра и найдите такое значение ячейки А1, чтобы результатом вычисления стало число 16. Excel возвратит параметр 4,00002269, что лишь приблизительно равно квадратному корню из 16. Изменить точность вычислений можно на вкладке Вычисления диалогового окна Параметры (следует уменьшить значение в поле Относительная погрешность).

В некоторых случаях желаемый результат дают несколько значений изменяемой ячейки. Например, формула =А1Л2 возвращает 16, если ячейка А1 содержит -4 или +4. В том случае, если существует несколько подходящих значений параметра, Excel отображает то из них, которое имеет тот же знак, что и текущее значение в ячейке, или то, которое наиболее приближено к этому значению.

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

Резюме

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

В следующей главе я расскажу о том, как работать с именами в Excel.

Глава3

Работаем с именами

Вэтой главе...

•Что такое Имя?

•Способы присвоения имен ячейкам и диапазонам

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

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

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

•Поддержка имен ячеек и диапазонов

•Проблемы при работе с именами

•Ключ к пониманию термина "имя"

•Решение сложных задач с помощью формул

•Резюме

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

Что такое Имя?

Имя можно рассматривать как идентификатор какого-либо элемента рабочей книги. Имя может быть присвоено таким элементам, как ячейка, диапазон, диаграмма, фигура и т.д. Имя, присвоенное, например, диапазону, может затем использоваться в формулах. Предположим, что диапазон В2 : В2 00 рабочего листа содержит информацию о дневном обороте компании. В ячейке С1 задана ставка комиссионного сбора с продажи. В следующей формуле сумма дневного оборота умножается на ставку комиссионного сбора:

=СУММ(В2:В200)*С1

Такая формула будет работать, однако смысл ее не очевиден. Для того чтобы формула стала понятной, можно назначить описательные имена диапазону, содержащему информацию о дневном обороте, и ячейке, в которой указана ставка комиссионного сбора. Например, диапазону В2 : В2 00" можно присвоить имя DailySales, а ячейке С1 — имя ComissionRate. Теперь формулу можно переписать, используя имена вместо адресов диапазона иячейки:

=CyMM(DailySales)*ComissionRate

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

Использование именованных ячеек и диапазонов имеет ряд преимуществ:

•Имена позволяют легче понять смысл формулы и использовать ее. Этоособенноважно длятех, кто неучаствовал в создании рабочего листа. Очевидно, что такую формулу как, например, =Income-Taxes, понять значительно легче, чем=D20-D40.

•Описательное имядиапазона (например, Total_lncome) запоминается легче, чем адреса ячеек (например, А21). Кроме того, вероятность опечатки при написании имени меньше, чем привводе адреса ячейки или диапазона.

•Вы можете легко перемещаться по рабочему листу, используя поле Имя, расположенное в левой части строки формул (щелкните на кнопке со стрелкой, и появится

список всех назначенных имен). Перейти от одного элемента листа к другому можно и выбрав Правка^Перейти или нажав клавишу <F5>. Появится диалоговое окно, в которое следует ввести имя диапазона или ячейки.

•Когда вы выделяете именованную ячейку илидиапазон, имяэтого элемента появляется в поле Имя.

•Возможно, вам будет легче записывать формулы, используя имена ячеек идиапазонов. Эти имена выможете вставлять в формулу, применяя команду Вставка^Имя^Вставить или нажимая клавишу <F3>.

• Использование имен вместо адресов ячеек и диапазонов облегчит создание макросов и дальнейшую работу сними.

Способы присвоения имен ячейкам

идиапазонам

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

Присвоение имен вокне Присвоение имени

наться со знака равенства), либо указать на нужную ячейку рабочего листа.

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

Имя может задаваться и прерывному диапазону ячеек. Выделить все ячейки и диапазоны прерывистого диапазона можно с помощью мыши, нажав клавишу <Ctrl>.

Редактируя содержимое поля Формула вручную, убедитесь втом, что это поле работает в режиме "указания". Этоозначает, чтопри редактировании нельзя использовать клавиши <End> и<Ноте>.Для того чтобы переключиться из режима "указания" в стандартный режим редактирования, нажмите <F2>. Активизировав поле Формула врежиме редактирования, вы сможете использовать все клавиширедактирования.

Присвоение имени спомощью поля Имя

В поле Имя вы сможете присвоить имя ячейке или диапазону значительно быстрее. Поле Имя — это раскрывающееся меню, расположенное в левой части строки формул. Для того чтобы присвоить имя,выделите нужную ячейку или диапазон, затем щелкните на поле Имя и введите имя. Для подтверждения выбранного имени нажмите <Enter>. Если такое имя уже существует, поле Имя нельзя использовать для переназначенияэтого имени другому диапазону или ячейке. Если вы попытаетесь сделать это, программа выделит исходный диапазон. Назначить существующее имя другому диапазону или ячейке можно только с помощью диалогового окна Присвоение имени.

Создавая имя в поле Имя, не забудьте нажать <Enter>. Если вы введете имя, а затем щелкните в области рабочего листа, новое имя не будет создано.

Правила создания имен

Что касается назначаемых вами имен, Excel достаточно неприхотлив. И все же есть некоторые правила создания имен:

•Имена немогут содержать пробелов. Для разделения слов внутри имени можно использовать символ подчеркивания или точку (например, AnnuaIJTotal \лп\л Annual.Total).

•Вкачестве имени можно использовать любую комбинацию букв ицифр. Однако любое имя должно начинаться с буквы или символа подчеркивания. Имя неможет начинаться сцифры (как, например, 3rdQuartet) или выглядеть как ссылка на ячейку (как, например, Q3).

•Для разделения слов внутри имени нельзя использовать иные символы, кроме символа подчеркивания и точки. Хотя этои не указано в документации к программе, Excel воспринимает и такие символы, какобратная косая черта (\) и вопросительный знак(?). Заметьте — имя неможет начинаться сэтих символов.

•Количество символов имени не может превышать 255. Поверьте моему опыту— использовать имена такой длины не имеет смысла. Использование чрезмерно длинных имен лишает всякого смысла операцию присвоения имени диапазону.

•ВКачестве имени выможете использовать отдельные буквы (кроме R иС). Однако я бы не рекомендовал давать такие имена, поскольку имя должно нести информацию о содержимом ячейки или диапазона.