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

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

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

=(145*1,05)/12

Такая формула всегда возвращает один и тот же результат, поэтому имеет смысл сохранить результат вычисления, а не формулу. Нажмите <F2> и отредактируйте содержимое ячейки. Затем нажмите <F9> и <Enter>. Excel сохранит результат вычисления (12,6875), а не саму формулу. Эта техника работает и в том случае, если в формуле используются ссылки на ячейки.

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

те = К О Р Е Н Ь ( 2 2 1 ) , нажмите <F9> и <Enter>. Excel возвратит результат: 14,8660687473185. Таким способом вы можете вычислить значение определенной части формулы. Рассмотрим такой пример:

= ( 1 4 5 * 1 , 0 5 ) / А 1

Если вы хотите заменить выражение в скобках определенным значением, войдите в режим редактирования ячеек и выделите ту часть формулы, значение которой необходимо вычислить. В данном случае следует выделить 1 4 5 * 1 , 05. Затем нажмите <F9> и <Enter>. Excel изменит формулу такимобразом:

=(152,25)/А1

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

Применение операторов в формулах

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

+Сложение

-Вычитание

/Деление

*Умножение

%Процент

&Объединение последовательностей символов в одну строку

ЛВозведение в степень

=Логическое сравнение (равно)

>Логическое сравнение (больше)

<- Логическое сравнение (меньше)

оЛогическое сравнение (не равно)

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

Операторы ссылки

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

Примеры формул с операторами

Вэтих примерах формул присутствуют различные операторы:

•Приведенная ниже формула объединяет две строки текста в новую строку—Part-23A:

="Part-n &"23A"

•Следующая формула объединяет содержимое ячеек А1 и А2:

=А1&А2

Обычно объединение применяется при управлении текстовыми строками. Однако этот оператор может быть применен и для объединения цифровых значений. Например, если ячейка А1 содержит значение 123, а ячейка А2 — значение 456, приведенная выше формула возвратит значение 123456. Обратите внимание: формально результат представляет собой текстовую строку, и тем не менее эта строка воспринимается как цифровое значение.

•В следующей формуле оператор возведения в степень используется для возведения 6 в третью степень. Результат операции — 216.

•Приведенная выше формула имеет и более удобный вариант, в котором вместо цифрового значения используется ссылка на ячейку. В этом примере значения ячейки А1 возводится в третью степень:

=А1Л 3

•А эта формула возвращает кубический корень из 216 (т.е. 6):

=21бА(1/3)

•Следующая формула возвращает ИСТИНА, если значение ячейки А1 меньше значения ячейки А2. В противном случае результат — ЛОЖЬ.

=А1<А2

Операторы логического сравнения могут быть использованы и при работе с текстом. Если А1 содержит значение Alpha, a A2 — значение Gamma, формула возвращает ИСТИНА, поскольку в алфавитном порядке Alpha стоит раньше Gamma.

•Следующая формула возвращает ИСТИНА, если значение ячейки А1 меньше или равно значению ячейки А2. В противном случае результат будет — ЛОЖЬ.

=АК=А2

• Следующая формула возвращает ИСТИНА, если значение ячейки А1 не равно значению ячейки А2. В противном случае результат будет — ЛОЖЬ.

=А1<>А2

• В отличие от некоторых других электронных таблиц (например, Lotus 1-2-3), в Excel нет логических операторов И или ИЛИ. Вместо них используются функции. Следующая формула, например, возвращает ИСТИНА,если ячейка А1 содержит значение 100 или 1000:

=ИЛИ(А1=100;А1=1000)

Последняя формула возвращает ИСТИНАтолько в том случае, если обе ячейки — А1 и А2 — содержат значения, меньше 100:

=И(АК100;А2<100)

Приоритет операторов

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

=Income-Expenses*TaxRate

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

=(Income-Expenses)*TaxRate

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

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

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

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

Вложенные скобки

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

=((В2*С2)+(ВЗ*СЗ)+(В4*С4))*В6

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

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

=А1*А2+1

Однако, порядок выполнения действий в таком варианте записи формулы (с лишними скобками) будет более очевиден:

=(А1*А2)+1

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

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

Не задавайте строгих значений

Создавая формулу, старайтесь не использовать строгих цифровых значений. Например, если формула предназначена для вычислениясуммы 7,5-процентного налога с оборота,вы, вероятно, захотите записать формулу так:

=А1*/075

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

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

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

=СУММ(Январь/СУММ(Всего)

Вдействительности формула должна иметь следующий вид:

=СУММ(Январь)/СУММ(Всего)

(ХОЗЯЕВ

SBP ЦК

Рис. 2.3. Функция автоматического исправления ошибок Excelпредлагает пути устранения ошибокв формулах

Вычисление формул

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

•Когда вы вносите изменения (например, вводите новые или редактируете данные или формулы), Excel сразу же пересчитывает те формулы, которые зависят от новых или измененных данных.

•Производя длительные расчеты, Excel временно приостанавливает вычисление в том случае, если вам необходимо выполнить какие-либо действия в программе; вычисление возобновляется, когда вы закончите свои действия.

•Формулы вычисляются в соответствии с последовательностью натуральных чисел. Например, если формула, расположенная в ячейке D12 зависит от результата вычисления формулы ячейки D11, ячейка D11 будет вычисляться прежде ячейки D12.

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

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

•<F9>. Вычисляет формулы во всех открытых рабочих книгах.

•<Shift+F9>. Вычисляет формулы только активной рабочей книги.

•<Ctrl+Alt+F9>. Проводит полный пересчет во всех открытых рабочих книгах. Используйте эту комбинацию клавиш, если Excel (по той или иной причине) возвращает неправильныезначения.

•<Ctrl+Shift+Alt+F9>. Перепроверяет все зависимые формулы, а затем проводит полный пересчет во всех открытых рабочих книгах.

Комбинация клавиш <Ctrl+Shift+Alt+F9> работает только в Excel 2002.

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

Ссылки на ячейки идиапазоныячеек

Во многих формулах используются ссылки на одну и более ячеек. Для ссылки используется адрес ячейки или диапазона (или имя, если это — именованная ячейка или диапазон). Существует четыре типа ссылок. Различать эти типы помогает знак доллара:

•Относительные: ссылка полностью относительна. При копировании формулы ссылка на ячейку вставляется в новое место. Пример: А1

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

•Абсолютная строка: ссылка частично абсолютна. При копировании формулы изменяется часть ссылки, относящаяся к столбцу. Та часть, которая относится к строке, остается неизменной.Пример: А$1

•Абсолютный столбец: ссылка частично абсолютна. При копировании формулы изменяется часть ссылки, относящаяся к строке. Та часть, которая относится к столбцу, остается неизменной.Пример: $А1

Создание абсолютной ссылки

Когда вы создаете формулу, указывая на ячейки, все ссылки на ячейки и диапазоны будут относительными. Для того чтобы ссылка стала абсолютной, вам придется вручную расставить знаки доллара. Есть и еще один способ: когда вы вводите адреса ячеек или диапазонов,используйте клавишу <F4>. С ее помощью можно перебирать все возможные режимы ссылки.

Режим ссылки целесообразно изменять только в том случае, если вы планируете копировать формулу. На рис. 2.4 приведен пример использования абсолютных ссылок в формуле. Обратите внимание на формулу в ячейке С4:

=С$3*$В4

- IL

~Hv. -. I -МП?;

Рис. 2.4.Примериспользования абсолютныхссылокв формуле

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

Сравнение типов ссылок А1 иR1C1

По умолчанию Excel использует тип ссылок А1. Адрес каждой ячейки состоит из буквы столбца и номера строки. Однако программа поддерживает и другую систему записи — R1C1. В соответствии с этой системой,ячейка А1 имеет адрес R1C1, А2 — R2C1 и т.д.

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

Обратите внимание на примеры записи формул в стандартной системе и в системе R1C1. Предположим, что формула расположена в ячейке В1 (илиже R1C2).

Система R1C1 окончательно сбила вас с толку? И не только вас. Тип ссылок R1C1приемлем приработе с абсолютными ссылками. Но когда речь идет об относительных ссылках — в этих скобках и самчерт ногу сломит!

Числа в скобках означают относительное расположение. R[-5]C[-3], например, означает, что ячейка находится на пять строк выше и на три столбца левее. Соответственно, R[5]C[3] ссылается на ячейку, расположенную на пять строк ниже и на тристолбца правее. Если вы опустите какую-либо из скобок, запись будет означать ячейку в томже столбце илистроке. Например, R[5]C означает ячейку, расположенную на пять строк ниже в томжестолбце.

Вы, вероятно, не будете использовать R1C1 в качестве основной системы представления ячеек. Однако у этой системы есть по крайней мере одно преимущество: в R1C1 очень легко обнаружить формулу с ошибкой. Прикопировании формула в R1C1остается неизменной вне зависимости от типа ссылок (относительного, абсолютного или смешанного). Поэтому

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

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

Если вы работаете вExcel 2002, воспользуйтесь функцией фоновой проверки формул. Эта функция поможет найти формулы, которые, вероятно, содержат ошибки. О фоновой проверке формул я расскажу в главе 21.

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

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

=Лист2!

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

= [Budget .X1S]JTMCT1 IA1 + 1

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

= ' [Budget Analysis .X1S]JIMCT1 ' !А1+ 1

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

='C:\MSOffice\Excel\[Budget Analysis.х1з]Лист1'!А1+1

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

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

Создание точной копии формулы

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

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

Использование ссылок для восстановления данных поврежденногофайла

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

= [Badfile . xls№crl!Al

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

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

1.Дважды щелкните кнопкой мыши, расположив курсор в ячейке А1 (или нажмите <F2>); включится режим редактирования.

2.Для того чтобы выделить всю формулу, нажмите клавишу <End>, а затем комбинацию <Shift+Home>. Вы также можете перетащить указатель мыши, выделив таким образом всю формулу.

3.Щелкните накнопке Копировать, расположенной на панели Стандартная (или нажмите комбинацию клавиш <Ctrl+C>). Выделенный текст будет скопирован вбуфер обмена.

4.Длявыхода изрежима редактирования нажмите <Enter>.

5.Активизируйте ячейку А2.

6.Щелкните на кнопке Вставить, расположенной на панели Стандартная (или нажмите комбинацию клавиш <Ctrl+V>). В результате точная копия формулы будет вставлена в ячейку А2.

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

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

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

Преобразование формул взначения

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

Предположим, чтодиапазон Al: A10 содержит формулы, возвращающие неизменный результат. Для того чтобы преобразовать этиформулы в значения, сделайте следующее:

1.Выделите диапазон Al:A10.

2.Щелкните на кнопке Копировать, расположенной на панели Стандартная (или нажмите комбинацию клавиш <Ctrl+C>).

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

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

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

Если вы работаете в Excel 2002, воспользуйтесь смарт-тэгом. Третьим пунктом приведенной выше последовательности будет выполнение команды Правка^Вставить (или нажатие <Ctrl+V>). В правом нижнем углу диапазона появится смарт-тэг. Щелкните на немивыберите опцию Только значения (рис. 2.5).

*2£

30

зГ

32

зз*

34ф

Н 4 > »(\sheetl/a

Pwc. 2.5. 5 £хс^/2Ш2 после операции вставки данных появляется смарт-тэг

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

изаменить ими исходные значения столбца А. Выполнять этонужно следующим образом:

1.Вставьте новый столбец после столбца А.

2.В ячейку В1 поместите следующую формулу:

=ПРОПИСН(А1)

3.Скопируйте формулу вниз по столбцу В столько раз, сколько элементов содержит столбец А. Теперь столбец В содержит значения столбца А, нов нужном регистре.