Вычисления в таблицах Excel 2010

• Вставка формул в таблицу.
• Ссылки и имена.
• Функции.  Массивы.
• Сложные формулы.

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

• возможность вставки в таблицы сложных формул и функций (в том числе вложенных);
• гибкая система ссылок;
• возможность организации сценариев и итераций.

Вложенные формулы (функции) делают программирование в Excel в чем-то похожим на программирование на языке C. Немалую долю привлекательности Excel обеспечивает возможность немедленно отображать результаты расчетов в виде диаграмм практически любого типа.

Вставка формул в таблицу

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

Рис. 5.1. Формула в ячейке Рис. 5.2. Текст ячейке

При наборе формулы важно знать, что формула обязательно должна начинаться со знака равенства (=). Если вы не введете этот символ, то вся остальная последовательность символов будет воспринята Excel как текст и, соответственно, отображена в ячейке (рис. 5.2).

В формуле, показанной на рис. 5.1, символы + и * называются операторами (то есть указывают на выполняемые действия), а числа 12 и 5 — операндами (то есть объектами, над которыми будут выполнены действия). В формулах Excel применяется ограниченный и малочисленный набор операторов, которые можно объединить в четыре основные группы:

• арифметические операторы;
• операторы сравнения;
• текстовый оператор;  операторы ссылок.

Арифметические операторы

Оператор сложения (+) складывает операнды, между которыми он находится (рис. 5.3).

Рис. 5.3. Сложение

Вставка формул в таблицу

Оператор вычитания (­) вычитает второй операнд из первого

(рис. 5.4).

Рис. 5.4. Вычитание

Оператор умножения (*) перемножает операнды, между которыми находится (рис. 5.5).

Рис. 5.5. Умножение

Оператор деления (/) делит первый операнд на второй (рис. 5.6).

Рис. 5.6. Деление

Оператор процента (%) делит операнд на 100 (рис. 5.7).

Рис. 5.7. Определение процента

Оператор возведения в степень (^) возводит операнд в степень

(рис. 5.8).

Рис. 5.8. Возведение в степень

Возведение в степень имеет свои нюансы. Среди операторов нет знака извлечения корня, но не все пользователи знают, что извлекать корни любой степени можно путем возведения в степень. Для этого достаточно возвести операнд в степень в виде правильной дроби, где числитель равен единице, а знаменатель — степени корня, который вы хотите извлечь. Например, чтобы извлечь квадратный корень (корень второй степени) из 25, нужно возвести 25 в степень 1/2 (рис. 5.9). А для того, чтобы извлечь кубический корень (корень третьей степени) из 27, нужно возвести 27 в степень 1/3 (рис. 5.10).

Рис. 5.9. Извлечение квадратного корня

Рис. 5.10. Извлечение кубического корня

Операторы сравнения

Операторы сравнения позволяют выполнить сравнение двух операндов, результатом которого является логическое значение ИСТИНА или ЛОЖЬ.

Если при сравнении с помощью оператора = (равно) сравниваемые значения равны, то результатом является ИСТИНА, иначе — ЛОЖЬ

(рис. 5.11).

Рис. 5.11. Сравнение на равенство

Если при сравнении с помощью оператора > (больше) левый операнд больше правого, то результатом является ИСТИНА, иначе — ЛОЖЬ (рис. 5.12).

Вставка формул в таблицу

Рис. 5.12. Использование оператора «больше»

Если при сравнении с помощью оператора < (меньше) левый операнд меньше правого, то результатом является ИСТИНА, иначе — ЛОЖЬ (рис. 5.13).

Рис. 5.13. Использование оператора «меньше»

Если при сравнении с помощью оператора >= (больше или равно) левый операнд больше правого операнда или равен ему, то результатом является ИСТИНА, иначе — ЛОЖЬ (рис. 5.14).

Рис. 5.14. Использование оператора «больше или равно»

Если при сравнении с помощью оператора <= (меньше или равно) левый операнд меньше правого или равен ему, то результатом является ИСТИНА, иначе — ЛОЖЬ (рис. 5.15).

Рис. 5.15. Использование оператора «меньше или равно»

Если при сравнении с помощью оператора <> (не равно) сравниваемые значения не равны, то результатом является ИСТИНА, иначе — ЛОЖЬ (рис. 5.16).

Рис. 5.16. Использование оператора «не равно»

текстовый оператор

Текстовый оператор в Excel всего один — это & (амперсанд). Он служит для объединения (конкатенации) нескольких текстовых значений в одно (рис. 5.17).

Рис. 5.17. Объединение строк

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

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

Операторов ссылок в Excel два — это оператор диапазона (:) и оператор объединения (;). Подробнее о них мы поговорим в следующем разделе.

Ссылки и имена

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

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

Рис. 5.18. Расчет удерживаемой суммы налогов с зарплаты

В ячейке D6 вычисляется значение формулы, в которой нет ни одной константы, все операнды этой формулы являются ссылками. Две одинаковые ссылки С6 указывают на соответствующую ячейку в столбце Начислено для данного работника, а ссылка B9 одинакова во всех формулах и содержит процент налога. Если мы изменим число в ячейке B9 на 20, то немедленно будут пересчитаны все значения в ячейках D2…D7 (рис. 5.19).

Рис. 5.19. Пересчет удерживаемой суммы налогов с зарплаты

Относительные и абсолютные ссылки

Те ссылки, которые были использованы в данном примере, называются относительными, они прямо указывают на определенную ячейку по ее адресу (C6, D9), однако если вы скопируете такую ссылку в другую ячейку, то адрес изменится. Он фиксирован относительно ячейки, в которой находится формула, и всегда будет указывать выше или ниже, правее или левее на одно и то же количество столбцов и строк. Например, на рис. 5.20 показана ситуация, когда в ячейке C1 находится число 10, а в ячейке E3 ссылка на ячейку C1. Ячейка C1 расположена на два столбца левее и на две строки выше ячейки E3. Значение, отображаемое в ячейке E3 равно, соответственно, значению в ячейке C1, то есть 10.

Рис. 5.20. Относительная ссылка

Если мы скопируем содержимое ячейки E3 в ячейку G6, то ссылка будет указывать на ячейку E4, расположенную на два столбца левее и на две строки выше, чем ячейка G6. Ячейка E4 содержит 20, и в ячейке G6 также отобразится число 20 (рис. 5.21).

Рис. 5.21. Копирование относительной ссылки

ВНИМАНИе

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

Абсолютная ссылка — это ссылка, которая всегда указывает на одну и ту же фиксированную ячейку независимо от того, куда и каким образом вы ее копируете или перемещаете. Если вы хотите, чтобы ссылка была абсолютной, то вам нужно при вводе ссылки в строку формул указать это при помощи знака доллара ($) перед каждой из координат ссылки, например, $A$1. Кроме того, вы можете применить этот знак только к одной из координат ссылки. Тогда часть ссылки станет относительной, а часть останется абсолютной, например, $A1 или A$1. В случае, когда вы делаете абсолютной одну из координат ссылки, ссылка будет всегда указывать на один и тот же фиксированный столбец или же на одну и ту же фиксированную строку.

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

A1$A$1$A1A$1A1

Диапазоны ячеек и операторы ссылок

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

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

Оператор диапазона (:) используется для задания диапазона ячеек. В операторе указывается верхняя левая и нижняя правая ячейки выделяемого диапазона (рис. 5.22).

Рис. 5.22. Использование оператора диапазона

Оператор объединения (;) используется для того, чтобы объединить в одной ссылке несколько диапазонов или ячеек (рис. 5.23).

Рис. 5.23. Использование оператора объединения

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

Стили ссылок A1 и R1C1

Все ссылки, которые мы до сих пор рассматривали, были оформлены в стиле A1, когда на первом месте в ссылке указывается имя столбца, а на втором — порядковый номер строки. Такой способ однозначно определяет координаты конкретной ячейки в таблице. Но в Excel возможен и другой стиль оформления ссылок, который обозначается R1C1. В этом случае вы указываете после буквы R (row — строка) количество строк, а после буквы C (column — столбец) — количество столбцов, отсчитываемых от ячейки, в которой расположена формула, до ячейки, на которую указывает ссылка. В этом стиле также возможны как относительные, так и абсолютные ссылки. Как и в случае использования стиля A1, по умолчанию все ссылки относительны, то есть записываются в формате R[1]C[1]. Квадратные скобки как раз и служат признаком относительности ссылки, а ссылки на ячейку, находящуюся левее или выше текущей, могут иметь отрицательные индексы (R[­2]C[­3]).

ВНИМАНИе

Относительные ссылки в стиле R1C1, в отличие от стиля А1, сохраняют свою относительность не только при копировании, но и при перемещении (в том числе перетаскиванием) формулы, содержащей ссылку.

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

Имена

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

1. В таблице выделите ячейку (или диапазон ячеек, или несколько диапазонов ячеек), для которой вы хотите задать имя.
2. Щелкните на выделенном фрагменте правой кнопкой мыши и выберите в контекстном меню команду Имя диапазона, чтобы открыть диалоговое окно, показанное на рис. 5.24.

Рис. 5.24. Создание имени

1. В поле Имя введите имя для выбранной вами ячейки или диапазона и щелкните на кнопке OK. В окне Создание имени можно задать новый диапазон для назначаемого имени, введя его в поле Диапазон.

После присвоения имени вы можете вместо адреса ячейки или ссылки на диапазон ячеек использовать присвоенное имя. Это иллюстрирует рис. 5.25.

Рис. 5.25. Ссылка на именованный диапазон

На рисунке показано, что в ячейку A1 вставлена формула, суммирующая все значения ячеек в диапазоне B3:D5, имеющем имя Тест. Как видите, в формуле используется ссылка не на сам диапазон, а на его имя.

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

Рис. 5.26. Список имен

Этот же список можно задействовать для быстрого создания имени. Просто выделите нужный диапазон ячеек, ведите новое имя в поле списка и нажмите клавишу Enter.

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

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

Имя_листа!Адрес_ячейки

Например, ссылка, расположенная в ячейке на листе Лист1 и указывающая на диапазон ячеек, находящийся на листе Лист2, выглядит так, как показано на рис. 5.27.

Функции

Рис. 5.27. Ссылка на диапазон ячеек из другого листа

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

[Имя_книги]Имя_листа!Адрес_ячейки

Например, ссылка, расположенная в ячейке на листе Лист1 в книге Книга1 и указывающая на диапазон ячеек, находящийся на листе Contacts в книге test, выглядит так, как показано на рис. 5.28.

Рис. 5.28. Ссылка на диапазон ячеек из другой книги

Такая запись подразумевает, что книга test находится в том же каталоге, что и книга Книга1. Однако если книга, на которую вы ссылаетесь, находится в другом каталоге, то следует указать полный путь доступа к ней непосредственно в формуле. Вот как будет выглядеть предыдущая ссылка на ячейку в книге, которая находится в каталоге C:\My\Docum:

=C:\My\Docum\[test]Contacts!С1:С3

Функции

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

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

Рис. 5.29.

Ввод функции

Рис. 5.30.

Первое окно мастера функций

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

Рассмотрим несколько примеров применения функций Excel.

Функции

Упражнение

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

АСЧ(начальная_стоимость;остаточная_стоимость;время_ эксплуатации;период)

Здесь

• начальная_стоимость — та сумма, за которую вы купили имущество;
• остаточная_стоимость — стоимость, которой имущество достигает к моменту списания в результате износа при эксплуатации;
• время_эксплуатации — время, за которое имущество полностью вы-

рабатывает свой эксплуатационный ресурс и достигает полного износа;

• период — период, за который вы хотите подсчитать сумму амортизации.

Вот как выглядит процедура вставки функции:

1. Для ввода аргументов выделите ячейки D1…D4. Введите в ячейку D1 значение 50 000, в ячейку D2 — значение 5 000, в ячейку D3 — значение 10, в ячейку D4 — значение 1.
2. Выделите для вставки функции ячейку C1, щелкнув на ней мышью.
3. Щелкните на кнопке Вставить функцию панели инструментов Библиотека функций вкладки Формулы, на экран будет выведено окно мастера функций. Выберите в списке Категория пункт Финансовые, а в списке Выберите функцию — пункт АСЧ. Щелкните на кнопке OK. На экран будет выведено второе окно мастера функций (рис. 5.31).

ПРИМеЧАНИе

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

Рис. 5.31. Второе окно мастера функций

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

Рис. 5.32. Работающая функция АСЧ

В ячейке C1 отображается сумма амортизации за первый год (D4) имущества, стоимость которого составляет 50 000 рублей (D1), срок амортизации — десять лет (D3) и ликвидная (остаточная) стоимость — 5 000 рублей (D2). Любое из значений в ячейках D1…D4 вы можете изменить, при этом соответствующим образом будет пересчитана сумма амортизации за год. Естественно, изменение значений должно соответствовать некоторой логике рассчитываемого

Массивы

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

Упражнение

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

СЕГОДНЯ()

Вот как происходит вставка функции:

1. Выделив столбцы C и D, нажмите клавишу Delete, тем самым удалите результаты выполнения предыдущего упражнения.
2. Щелкните в ячейке C1, а затем — на кнопке Вставка функции.
3. В окне мастера функций выберите категорию Дата и время, функцию СЕГОДНЯ и щелкните на кнопке OK.

Результат выполнения этой процедуры вы можете видеть на рис. 5.33.

Рис. 5.33. Отображение в ячейке текущей даты

Формат, в котором будет представлена текущая дата, зависит от формата даты, назначенного ячейке C1 (см. раздел «Формат ячеек» в главе 4).

Массивы

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

Упражнение

Примером специальной функции для работы с массивом может служить функция транспонирования массива ТРАНСП (транспонированием называют такое преобразование массива, при котором его строки становятся столбцами, а столбцы — строками).

1. Введите в таблицу значения массива так, как это показано на рис. 5.34.

Рис. 5.34. Исходный массив

1. Выделите целевой диапазон ячеек E1:K2. Обратите внимание на то, что целевой диапазон (то есть диапазон, в который будет вставлен результат транспонирования) должен соответствовать количеству строк и столбцов ожидаемого результата. Если вы выделите иное количество строк или столбцов, то получите сообщение об ошибке.
2. Вызовите мастер функций щелчком на кнопке Вставка функции.
3. В первом окне мастера функций выберите категорию Ссылки и массивы и функцию ТРАНСП. Щелкните на кнопке OK.
4. Во втором окне мастера функций введите вручную или выделите мышью (как вы это делали в первом упражнении раздела «Функции») диапазон C1:D7 (то есть входным значением функции, ее аргументом будет созданный вами массив). Щелкните на кнопке OK. Вы получите сообщение об ошибке в ячейке E1, как это показано на рис. 5.35, которое объясняется тем, что для формул, обрабатывающих массивы, применяется специальный способ ввода.
5. Щелкните мышью в строке формул и нажмите сочетание клавиш Ctrl+Shift+Enter. Вы получите результат, показанный на рис. 5.36.

Это и есть транспонированный массив.

Сложные формулы

Рис. 5.35. Ошибка в ячейке E1

Рис. 5.36. Транспонированный массив

ВНИМАНИе

Отметьте, что формула в строке формул оказалась в фигурных скобках. Эти скобки появляются при нажатии клавиш Ctrl+Shift+Enter и служат признаком того, что формула обрабатывает массив. Вводить эти скобки вручную не надо, это приведет к ошибке.

Сложные формулы

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

Упражнение

Для введения сложной формулы выполните описанную ниже процедуру.

1. Введите в таблицу два числовых массива так, как это показано на рис. 5.37.

Рис. 5.37. Исходные числовые массивы

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

=СУММ(ЕСЛИ(ОСТАТ((H1:J5);2)=0;D1:F5))

1. Закончив ввод формулы, нажмите сочетание клавиш Ctrl+Shift+ +Enter. Формула должна быть взята в фигурные скобки, при этом в ячейке C1 отобразится нулевое значение.

Давайте выясним, что делает эта формула. Функция СУММ указывает на то, что происходит суммирование значений. Аргументом суммирования являются ячейки массива D1:F5. Функция ЕСЛИ говорит нам, что суммирование условное и в сумму включаются не все ячейки. В условие функции ЕСЛИ входит функция ОСТАТ, аргументом которой являются ячейки массива H1:J5. Суммирование происходит только тогда, когда остаток от деления соответствующей ячейки на 2 равен нулю. Теперь мы можем подвести итог анализа формулы: значение каждой из ячеек массива D1:F5 добавляется к сумме только в том случае, если значение соответствующей ячейки из массива H1:J5 является четным. Поскольку все ячейки массива H1:J5 содержат нечетные значения, сумма в ячейке C1 равна нулю. Проверьте работу формулы, вводя в ячейки массива H1:J5 четные числа и наблюдая за тем, как изменяется сумма в ячейке C1.

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