Текст книги "Excel 2007. Компьютерная шпаргалка"

Глава 4
Использование формул и функций

Что такое формула

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

Для заполнения колонки Доход можно воспользоваться калькулятором и последовательно вычислить значения 25848x2, 33084x3 и т.д. Однако Microsoft Excel позволяет автоматизировать этот процесс.

Рис. 4.1. Исходная таблица «Продажа телевизоров»

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

Для того чтобы рассчитать значения колонки Доход, необходимо:

1. Установить курсор в ячейку D2 и ввести последовательность символов: =B2*C2 (рис. 4.2).

2. Нажать клавишу Enter.

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

Рис. 4.2. Ввод формулы в ячейку

Microsoft Excel особым образом обрабатывает содержимое ячейки, которое начинается со знака =, и рассматривает его как формулу.

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

Рис. 4.3. Отображение формулы в строке формул

Операндами формулы могут быть:

• постоянные значения;

• ссылки на ячейки (диапазон ячеек);

• имена;

• функции.

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

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

Для того чтобы осуществить протягивание, необходимо:

1. Установить курсор в ячейку D2.

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

3. Нажать левую кнопку мыши и перемесить указатель в ячейку D7 (рис. 4.5).

4. Отпустить левую кнопку мыши.

Рис. 4.4. Начало операции протягивания

Рис. 4.5. Выделение ячеек для протягивания

В каждой ячейке столбца Доход будет создана формула (рис. 4.6). При этом текст формулы ячейки D2 берется как образец. В результате в ячейке D3 в формулу подставляются данные из B3 и С3, в D4 – из C4 и D4 и т.д. Это позволяет быстро производить однотипные операции в больших таблицах.

Рис. 4.6. Результат протягивания

Примечание. Более подробно о механизме автокоррекции формул рассказано в разделе «Используем относительные ссылки».

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

,

Рис. 4.7. Панель Библиотека функций

Для суммирования ячеек колонки Доход необходимо:

1. Выбрать ячейку D8.

2. Перейти на вкладку Формулы и нажать кнопку Автосумма панели Библиотека функций.

3. Нажать клавишу Enter.

В результате в ячейке D8 будет отображен суммарный доход от продаж телевизоров (рис. 4.8), а в строке формул – формула для расчета этого показателя. В данном случае здесь была использована функция СУММ, которая возвращает сумму значений диапазона ячеек.

Примечание. Более подробно об автосумме и функциях читайте в разделе «Использование мастера функций для создания формул» данной главы.

В Microsoft Excel можно применять и более сложные формулы. Например, рассчитать в процентах долю дохода от продажи двух телевизоров Panasonic TX-R32LM70 в общей сумме можно по формуле

=В2*С2*100/СУММ(D2:D7)

Формула в Microsoft Excel может содержать элементы, представленные на рис. 4.9.

Рис. 4.8. Нахождение суммарного дохода

Рис. 4.9. Элементы формулы

Что такое ссылка

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

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

Ссылки на ячейки используют заголовки соответствующих строк и столбцов рабочего листа. В Excel столбцы помечены латинскими буквами (A, B, C,…, Z; AA, AB, AC,…, AZ; BA, BB, BC и т.д.), а строки пронумерованы. Такие ссылки называются ссылками типа A1. Ссылаться можно как на отдельные ячейки, так и на диапазоны ячеек.

Когда в диапазон входят смежные ячейки, например А1, А2 и A3 или А1, B1 и C1, такой диапазон обозначается в формуле при помощи ссылок на первую и последнюю его ячейки, между которыми ставится знак двоеточия. В данном случае приведенные диапазоны будут обозначены А1: А3 и A1:C1. Вместе с тем, если ячейки диапазона являются несмежными, т. е. они были выделены с помощью клавиши Ctrl, то ссылки на все ячейки диапазона перечисляются в формуле через точку с запятой.

В формуле, приведенной на рис. 4.10, суммируются ячейки В2, В3, В4, В5 и Е6.

Рис. 4.10. Суммирование группы ячеек

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

Например, если в ячейке А3 была записана формула =А1*А2 (рис. 4.11), то при копировании содержимого А3 в ячейки В3 и С3 новые формулы с обновленными ссылками примут следующий вид: =В1*В2, =С1*С2.

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

Например, если необходимо зафиксировать в формуле =А1*В1 значение ячейки А1 (рис. 4.12), которое не должно изменяться в случае копирования данной формулы, то абсолютная ссылка на эту ячейку будет иметь следующий вид: $A$1. Таким образом, при копировании формулы из ячейки В2 в ячейку С2 формула примет вид =$А$1*С1.

Рис. 4.11. Использование относительных ссылок

Рис. 4.12. Использование абсолютных ссылок

Использование абсолютных ссылок можно проиллюстрировать на следующем примере. Предположим, имеется прайс-лист телевизоров, цена которых представлена в рублях (рис. 4.13). Необходимо рассчитать стоимость телевизоров в евро относительно текущего курса рубль/евро.

Если попробовать ввести в ячейку C3 формулу =B3/B1, а затем произвести ее протягивание в интервале C3: C8, то получится неверный результат (рис. 4.14).

Действительно, как уже отмечалось выше, при копировании происходит автокоррекция формулы, поэтому в ячейке C4 записано =B4/B2, в C5 записано =B5/B3 и т.д., т.е. для ячейки C4 вычисляется деление числового значения на строковое, что приводит к ошибке, а в C5 цена третьего телевизора делится на цену первого и т.д.

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

Чтобы избежать этого, необходимо зафиксировать значение B1, т.е. формула C3 должна иметь вид =B3/$B$1. Тогда при протягивании изменяться будет только первая ссылка и получится корректный результат (рис. 4.15).

Если необходимо зафиксировать в ссылке только строку или только столбец, в котором находится используемая ячейка, в частности столбец А или строку 1, то ссылка примет вид $A1 или A$1 соответственно.

Рис. 4.13. Прайс-лист товаров

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

Рис. 4.15. Корректный результат расчета цены в евро

Что такое функция в Excel

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

Функции в Excel для удобства работы разбиты по категориям: финансовые, логические, текстовые, функции даты и времени, математические, статистические и т. д.

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

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

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

Рис. 4.16. Запись функции

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

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

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

=СУММ(ОКРУГЛ(А1;2);ОКРУГЛ(А2;2))

Здесь функция ОКРУГЛ является вложенной. В формулах Excel можно использовать до 64 уровней вложенности функций.

Задаваемые исходные аргументы должны иметь допустимые для них значения. Однако существуют функции, которые не имеют аргументов. Примерами таких функций являются ПИ (возвращает значение трансцендентного числа ?, округленное до 15 знаков) или СЕГОДНЯ (возвращает текущую дату). При использовании подобных функций следует в строке формул сразу после названия функции ставить круглые скобки. Другими словами, чтобы получить в ячейках значение числа π или текущую дату, следует ввести формулы такого вида:

=ПИ()

=СЕГОДНЯ()

В качестве примера работы функции рассмотрим функцию ОКРУГЛ(число; число_разрядов), которая возвращает число, округленное до заданного количества знаков после запятой, и имеет два аргумента:

• число – адрес ячейки с числом (или само число), которое нужно округлить;

• число разрядов – количество цифр после запятой у числа после округления.

Допустим, необходимо вычислить площадь помещения, длина которого равна 4,25 м, а ширина – 3,73 м (рис. 4.17).

Площадь равна произведению ширины на длину, что и представлено формулой ячейки B4.

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

Рис. 4.17. Исходные данные для функции округления

Чтобы округлить число 15,8525, находящееся в ячейке A1, с точностью до целого, одного и двух знаков после запятой и записать результат вычисления соответственно в ячейки B6, B7 и B8, нужно ввести в эти ячейки формулы следующего вида:

=ОКРУГЛ(В6;0)

=ОКРУГЛ(В7;1)

=ОКРУГЛ(В8;2)

Результаты представлены на рис. 4.18.

Рис. 4.18. Использование функции округления

Примечание. Отличием функции ОКРУГЛ является то, что она действительно сокращает число десятичных знаков, а не скрывает их, как это происходит при изменении формата ячейки. Следовательно, если произвести вычисления по формуле =B7-0,1, то получим 15,8 а не 15,7525, как это было бы при изменении числа десятичных знаков формата ячейки.

Как создать формулу с помощью мастера функций

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

Мастер функций можно вызвать несколькими способами:

• кнопкой Вставить функцию панели Библиотека функций вкладки Формулы (см. рис. 4.7);

• кнопкой

в строке формул;

• путем выбора функции из выпадающего списка кнопки соответствующей категории или команды Вставить функцию, присутствующей в каждой категории;

• кнопкой Автосумма или Недавно использовались (см. рис. 4.7).

Рис. 4.19. Подсчет суммарного значения столбца

Например, требуется вычислить суммарное значение столбца Заготовка яблок таблицы:, представленной на рис. 4.19.

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

1. Установить курсор в ячейку, в которую требуется ввести формулу (В6).

2. Воспользоваться инструментом Вставить функцию панели Библиотека функций либо нажать кнопку

 в строке формул.

3. В открьшшемся диалоговом окне (рис. 4.20) выбрать категорию Математические, в поле ниже указать требуемую функцию – СУММ и нажать OK.

Рис. 4.20. Мастер функций: выбор функции

4. В диалоговом окне Аргументы функции (рис. 4.21) ввести исходные данные. В качестве аргументов функция СУММ использует ссылки на ячейки таблицы. Ввод ссылок в поля диалогового окна можно осуществить двумя способами:

• непосредственно указывая адрес используемой ячейки или диапазона ячеек;

• путем выбора ячейки (диапазона ячеек) с помощью мыши.

Рис. 4.21. Диалоговое окно Аргументы функции

Второй способ заключается в следующем:

• свернуть окно мастера функций с помощью кнопки

 , находящейся справа от того поля, в которое требуется ввести ссылку на очередную ячейку (при этом окно будет свернуто в строку);

• выделить с помощью указателя мыши ячейки В2:В5 (рис. 4.22);

• развернуть окно мастера функций с помощью кнопки

, расположенной там же, где и кнопка сворачивания.

Рис. 4.22. Ввод ссылок в качестве аргументов функции

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

5. Нажать кнопку ОК в окне мастера функций. При этом готовая формула вставляется в редактируемую ячейку.

Требуемую функцию можно выбрать непосредственно из списка, нажав кнопку соответствующей категории на панели Библиотека функций вкладки Формулы (см. рис. 4.7 в разделе «Что такое формула»). Это немного ускорит процесс создания формулы, так как при этом сразу открывается диалоговое окно Аргументы функции. Для использования категорий необходимо сначала хорошо изучить встроенную библиотеку функций. Даже если вы запутаетесь с выбором категорий, можно всегда активировать мастер функций, который находится в самом конце списка.

Кроме кнопок категорий, на панели Библиотека функций имеются еще две дополнительные кнопки: Недавно использовались и Автосумма.

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

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

Рис. 4.23. Кнопка Автосумма

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

Сначала необходимо определить суммарное значение. Для этого следует выполнить такие действия:

1. Установить курсор в позицию B7.

2. Нажать кнопку Автосумма панели Библиотека функций. В результате в выбранной ячейке появится формула следующего вида: =СУММ(В3:В6).

3. Нажать клавишу Enter.

4. С помощью протягивания заполнить ячейки C7 и D7.

Рис. 4.24. Исходные данные для вставки автосуммы

Рис. 4.25. Результат применения автосуммы

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

1. Установить курсор в ячейку E3.

2. Раскрыть список кнопки Автосумма.

3. Выбрать пункт Среднее. В результате в выбранной ячейке появится формула =СРЗНАЧ(В3:D3).

4. Нажать клавишу Enter.

5. С помощью протягивания заполнить ячейки E4:E6.

Полученный результат приведен на рис. 4.25.

Глава 5
Построение диаграмм

Предварительные сведения о построении диаграмм

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

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

Построение диаграммы начинается с выбора диапазона тех данных, которые следует отобразить. Затем на вкладке Вставка в группе Диаграммы выбирается кнопка типа создаваемой диаграммы (Гистограмма, Круговая, Линейчатая и т.д.), которая представляет собой выпадающее меню с вариантами диаграмм.

После того как созданная диаграмма отобразится в рабочем листе, станет доступна контекстная вкладка Работа с диаграммами (рис. 5.1) с тремя наборами инструментов (вкладками) для изменения диаграмм:

Рис. 5.1. Вкладка Работа с диаграммами

• Конструктор – содержит параметры, определяющие тип диаграммы, источник данных и их упорядочивание, макеты диаграмм, а также команду Переместить диаграмму;

• Макет – позволяет указывать свойства диаграмм, добавлять или редактировать элементы диаграммы (подписи данных и осей, добавление легенды и элементов рисования), а также выбирать параметры, связанные с трехмерными графиками;

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

Как построить диаграмму

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

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

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

Рассмотрим пример построения диаграммы по данным таблицы, представленной на рис. 5.2. Создаваемая диаграмма должна содержать сравнительную информацию о стоимости различных моделей телевизоров в двух магазинах: «Грант» и «Электрон». В качестве исходных данных следует указать диапазон, включающий подписи строк и столбцов А3:С8, без столбца средних значений стоимости.

Рис. 5.2. Данные для построения диаграммы

Рис. 5.3. Выбор типа диаграммы

Теперь необходимо перейти на вкладку Вставка и выбрать тип будущей диаграммы, воспользовавшись командой Гистограмма с группировкой на панели Диаграммы в выпадающем списке кнопки Гистограмма (рис. 5.3).

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

 , затем в открывшемся окне Вставка диаграммы (рис. 5.4) выбрать необходимый вариант.

Диаграмма выбранного типа, построенная по указанным данным, будет отображена на листе Excel (рис. 5.5).

Рис. 5.4. Окно Вставка диаграммы

Рис. 5.5. Итоговая диаграмма