Текст книги "Office 2007: самоучитель"

Контрольное упражнение

Попробуйте самостоятельно заполнить данными один лист электронной таблицы.

1. Переключитесь на лист Лист5.

2. Измените название этого листа на Календарь.

3. Введите в ячейку A3 символы Пн.

4. Перетащите маркер выделения вниз, чтобы ячейки с A3 по А9 заполнились сокращенными названиями дней недели.

5. В прямоугольную область B3:F9 введите числа календаря на текущий месяц.

6. В строке 1 введите заголовок Календарь на текущий месяц, объедините для него несколько ячеек и выровняйте заголовок по правому краю диапазона.

7. Добавьте обрамление жирной линией вокруг блока ячеек A3:F9.

8. Отделите границей столбец А от столбца В.

Как быстро добавить вертикальную границу?

9. Залейте фон ячеек строк 8 и 9 красным цветом, а шрифт текста этих строк выделите жирным начертанием.

Как изменить фон ячеек?

10. Объедините ячейки А11:С11 и введите в них текст Всего дней в месяце.

11. В ячейку D11 вставьте формулу, подсчитывающую число дней месяца.

Как подсчитать число дней месяца?

Совет

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

12. Скопируйте текущий лист в новую книгу Excel с одним листом и сохраните новую книгу в файле Календарь.

13. В книге Электронная таблица раскройте лист Клиенты и скопируйте его, расположив копию перед листом Календарь.

14. Перейдите на созданный лист Клиенты (2) и удалите строку 9.

15. Переименуйте этот лист, дав ему название Формулы. Затем сохраните документ.

Подведение итогов

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

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

Занятие 10
Формулы и функции

Тема занятия

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

• вводить формулы;

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

• ссылаться на диапазон ячеек;

• выполнять сложные вычисления;

• копировать формулы;

• исправлять ошибки в формулах.

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

Вычисления в Excel

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

Упражнение 1. Ввод формул

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

1. Откройте файл Электронная таблица. xls, созданный на предыдущем занятии. В этой рабочей книге есть лист Формулы, измененный в контрольном упражнении предыдущего занятия. Раскройте его. Первые семь строк листа должны выглядеть так, как показано на рис. 10.1, а остальные ячейки оставаться пустыми. Если это не соответствует действительности, отредактируйте лист.

Рис. 10.1. Ввод формулы

2. Щелкните в ячейке В10 и введите формулу =(C3-В3)/В3*100

Знак равенства указывает программе Excel, что ячейка содержит формулу. C3 и B3 – это ссылки на содержимое соответствующих ячеек. Если предположить, что таблица содержит объем продаж продукции различным клиентам, то величина (C3-B3) – это прирост продаж для фирмы Фантом с января по февраль. Разделив полученную величину на B3 (продажи за январь), получаем относительный прирост продаж за месяц. Результат умножается на 100, что дает прирост в процентах. Как видите, в ячейки можно вводить простые арифметические выражения со знаками +, -, * и /, а также скобками, определяющими последовательность выполнения операций.

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

3. Щелкните в ячейке В10 и перетащите угловой маркер выделения вправо, чтобы оказались охваченными ячейки с В10 по F10.

Совет

Чтобы результат выводился в процентах, не обязательно умножать его на 100, достаточно сменить формат данных, выделив ячейку и щелкнув на кнопке Процентный формат (Percent Style), расположенной на вкладке Главная (Home) в группе Число (Number)

На первый взгляд кажется, что в ячейки C10:F10 должна дублироваться формула ячейки В10, но это не так. Excel поддерживает механизм относительных ссылок на ячейки. Когда формула переносится на одну клетку вправо, точно так же смещаются все ссылки этой формулы (это верно и для смещения формулы в любых других направлениях на любое число ячеек). То есть в ячейке СП появится формула =(D3-C3)/C3*100, в ячейке D10 – формула =(E3-D3)/D3*100 и т. д. Чтобы проверить это, щелкните на каждой ячейке и изучите содержимое строки формул. В итоге ячейки строки 10 будут содержать относительный прирост продаж компании Фантом по месяцам.

4. Выделите строки с третьей по седьмую.

5. Щелчком на кнопке Вырезать (Cut) в группе Буфер обмена (Clipboard) на вкладке Главная (Home) вырежьте их содержимое.

6. Щелкните в ячейке А2 правой кнопкой мыши и выберите в контекстном меню команду Вставить (Paste). Данные таблицы переместятся вверх на одну строку. Теперь снова изучите формулы ячеек в строке 10.

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

Совет

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

Упражнение 2. Функции

Excel поддерживает множество стандартных математических функций, которые можно вставлять в формулы. С некоторыми из них, такими как СУММ (SUM), СРЗНАЧ (AVERAGE), МАКС (МАХ) и СЧЕТ (COUNT), вы познакомились на предыдущем занятии. Теперь мы будем учиться работать с другими функциями.

1. Щелкните в ячейке Н8 и введите текст Максимум. Нажмите клавишу Tab.

2. Щелкните на кнопке Вставить функцию (Insert Function), которая находится на вкладке Формулы (Formulas) в группе Библиотека функций (Function Library). Откроется диалоговое окно, показанное на рис. 10.2.

Рис. 10.2. Вставка функции

Чтобы найти нужную функцию, можно кратко описать желаемую процедуру в текстовом поле Поиск функции (Search for a function) или же выбрать ее категорию в списке Категория (Or a select a category) этого диалогового окна, а затем прокрутить список Выберите функцию (Select a function). Выделив имя одной из функций, вы увидите ее описание в нижней части диалогового окна. Excel делит все функции на следующие категории:

• Финансовые (Financial) – функции для расчета амортизации имущества, стоимости основных фондов, нормы прибыли, величины выплат на основной капитал и других финансовых показателей.

• Дата и время (Date & Time) – операции прямого и обратного преобразования даты и времени в текстовые строки. Функции этой группы перечислены в табл. 10.1.

Таблица 10.1. Функции обработки даты и времени

• Математические (Math & Trig) – математические и тригонометрические функции; некоторые из них приведены в табл. 10.2.

Таблица 10.2. Математические и тригонометрические функции

• Статистические (Statistical) – функции для расчета среднего значения, дисперсии, статистических распределений и других вероятностных характеристик. Некоторые статистические функции перечислены в табл. 10.3.

Таблица 10.3. Статистические функции

• Ссылки и массивы (Lookup & Reference) – операции преобразования ссылки на ячейку в число, расчета ссылок на основе числовых аргументов, вычисления числа строк и столбцов диапазона и других параметров, связанных с адресацией ячеек листа Excel.

• Работа с базой данных (Database) – функции формирования выборки из базы данных и расчета статистических параметров величин, расположенных в базе данных.

• Текстовые (Text) – функции для работы с текстовыми строками. Часть этих функций перечислена в табл. 10.4.

Таблица 10.4. Текстовые функции

• Логические (Logical) – шесть функций для работы с данными логического типа, то есть величинами или условиями, принимающими истинное или ложное значение. Эти функции перечислены в табл. 10.5.

Таблица 10.5. Логические функции

• Проверка свойств и значений (Information) – функции проверки типа данных аргумента, режима форматирования ячейки, типа сгенерированной ошибки и других специальных условий.

• Инженерные (Engineering) – функции для проведения различных инженерных расчетов.

В списке Категория (Function Category) есть также пункты Полный алфавитный перечень (АН) и 10 недавно использовавшихся (Most Recently Used), которые выводят соответственно список всех функций и 10 функций, применявшихся последними.

3. Выберите в списке Категория (Or a select a category) диалогового окна вставки функций пункт Статистические (Statistical), как показано на рис. 10.2.

4. Выберите в списке Функция (Function Name) пункт МАКС (МАХ).

5. Щелкните на кнопке ОК. Откроется окно ввода диапазона ячеек, показанное на рис. 10.3.

Некоторые функции, например SIN, имеют только один аргумент, поскольку нельзя вычислить синус сразу двух чисел. Однако многие функции, подобные МАКС (МАХ), способны обрабатывать практически неограниченные массивы данных. Такие функции могут воспринимать до 30 аргументов, каждый из которых является числом или ссылкой на одну или несколько ячеек.

Рис. 10.3. Окно ввода аргументов

Примечание

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

С помощью функции МАКС (МАХ) вы сейчас найдете максимальный уровень продаж за один месяц для компаний Фантом, РИФ и Викинг.

6. Щелкните на кнопке в правой части поля Число 1 (Number 1). Диалоговое окно свернется в строку, открывая доступ к ячейкам листа.

7. Выделите все числовые ячейки строки Фантом. Обозначение соответствующего диапазона ячеек появится в строке свернутого диалогового окна ввода аргументов (рис. 10.4).

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

8. Щелкните на кнопке в строке аргумента. На экране снова развернется окно ввода аргументов.

9. Щелкните на кнопке в правой части поля Число 2 (Number 2).

10. Выделите ячейки B5:G5 и снова щелкните на кнопке в строке ввода аргумента. Поскольку вы ввели уже два аргумента, Excel автоматически добавит поле ввода третьего.

11. Введите в поле Число 3 (Number 3), как показано на рис. 10.5, текст B7:G7. Это диапазон нужных ячеек строки Викинг. В диалоговом окне правее полей с аргументами демонстрируются их реальные значения. Ниже списка чисел отображается результат вычислений.

Рис. 10.5. Три диапазона ячеек в качестве аргументов функции МАКС

12. Щелкните на кнопке ОК. В ячейке 18 появится максимальное число из диапазона, записанного в строках 2, 4 и 6. Сама формула появится в строке формул в верхней части окна Excel. Щелкните в этой строке. Три аргумента функции МАКС (МАХ), задающие три диапазона ячеек, будут выделены разными цветами, а соответствующие группы ячеек листа Excel окажутся обведенными рамками соответствующих цветов, как показано на рис. 10.6.

Совет

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

Упражнение 3. Диапазон ячеек

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

1. Щелкните в ячейке Н9 и введите текст Сумма, как показано на рис. 10.7. Затем нажмите клавишу Tab.

Рис. 10.6. Группы ячеек, выступающие в качестве аргументов формулы

2. Введите формулу =СУММ() (=SUM())

Эта формула вычисляет сумму всех величин, указанных в скобках в качестве аргумента.

3. Щелкните в строке формул и поместите курсор между двумя скобками.

4. Введите в скобки текст В: В.

5. Щелкните на кнопке Ввод слева от строки формул. В ячейке 19 появится сумма всех ячеек столбца В, так как символы В: В обозначают все ячейки столбца В.

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

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

Рис. 10.7. Столбец В в качестве аргумента функции

Таблица 10.6. Способы ссылки на группы ячеек

8. Перетаскиванием мыши выделите ячейки B3:G3 (рис. 10.8).

9. Нажмите клавишу Ctrl и перетащите указатель мыши по ячейкам B5:G5.

10. Введите в поле Имя (Name Box) слово Строки, как показано на рис. 10.8.

Рис. 10.8. Именованная группа ячеек

11. Нажмите клавишу Enter.

12. Щелкните в ячейке 19 и введите формулу: =СУММ (Строки) (=SUM (Строки))

После нажатия клавиши Enter в ячейке 19 появится сумма двенадцати ячеек строк 3 и 5.

13. Щелкните на стрелке раскрывающегося списка Имя (Name Box).

14. Выберите пункт Строки. Окажутся выделенными ячейки диапазона Строки.

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

Формулы

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

Упражнение 4. Копирование формул

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

1. Щелкните в любой непустой ячейке в строке 10. Вы увидите, что выбранная формула ссылается на ячейки строки 3.

2. Выделите диапазон B10:F10.

3. Нажатием клавиш Ctrl+C скопируйте ячейки.

4. Щелкните в ячейке В11, затем – на кнопке Вставить (Paste), расположенной на вкладке Главная (Home) в группе Буфер обмена (Clipboard). Появится еще одна строка ячеек с формулами. Щелкните в любой из них и посмотрите на строку формул. Вы увидите, что формулы новых ячеек ссылаются на данные строки 4 (рис. 10.9).

Рис. 10.9. Копирование формул

5. Щелкните в ячейке В12 и нажмите клавиши Ctrl+V, чтобы вставить еще одну строку. Формулы этой строки уже будут ссылаться на данные строки 5. Таким образом, номера ячеек данных изменяются ровно на столько ячеек, на сколько смещается вставляемая формула.

Примечание

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

6. Вставьте тот же самый фрагмент в ячейки В13 и В14.

7. Скопируйте заголовки строк из ячеек A3:А7 в ячейки А10:А14.

Совет

Обычно Excel формирует относительные ссылки на ячейки. Но иногда нужно сослаться в формуле на ячейку с константой, которая не должна меняться при копировании формулы в другую ячейку или при перемещении исходных данных. В этом случае пользуйтесь абсолютными ссылками, которые отличаются от относительных наличием символа $ перед буквой столбца, перед номером строки или перед тем и другим, например $Е$15. В такой ссылке не изменяется та часть, перед которой стоит знак $. Например, копируя формулу =CYMM ($B$2:$G$6) (=SUM ($B$2:$G$6)) в другую ячейку, вы получите тот же самый результат, что и в исходной ячейке формулы.

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

8. Выделите группу ячеек 13:17, как показано на рис. 10.10.

Рис. 10.10. Автосумма

9. Щелкните на кнопке Автосумма (AutoSum), расположенной на вкладке Формулы (Formulas) в группе Библиотека функций (Function Library).

Новое в Office

В новой версии Office вы может воспользоваться не только кнопкой Автосумма (AutoSum), для того чтобы заполнить формулами сразу несколько ячеек. Теперь вы можете аналогичным образом использовать и другие функции. Для того чтобы сделать это, щелкните на стрелке, расположенной в правой части кнопки Автосумма (AutoSum), после чего появится меню с еще несколькими функциями и пунктом Другие функции (More Functions), при выборе которого отображается окно мастера функций, позволяющее выбрать любую функцию.

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

Примечание

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

Упражнение 5. Поиск ошибок

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

1. Щелкните в ячейке 111 и введите формулу: =CP3HAЧB11:F11 (=AVERAGEB11:F11)

2. Нажмите клавишу Enter. В ячейке появится сообщение #ИМЯ? (#NAME?), которое указывает на ошибку в имени функции. Слева от него находится маленький значок помощника, содержащий раскрывающийся список, в котором приводятся возможные способы устранения ошибки (рис. 10.11). В табл. 10.7 приведен список сообщений об ошибках, которые можно встретить в ячейках при работе с формулами.

3. Чтобы исправить формулу, двойным щелчком в ячейке 111 перейдите в режим редактирования формулы.

Таблица 10.7. Сообщения об ошибках в формулах

Рис. 10.11. Обнаружение ошибки

4. Измените содержимое ячейки так, чтобы получилась формула =CP3HAЧ(B11:F11) (=AVERAGE(B11:F11))

Примечание

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

Упражнение 6. Применение функций

До сих пор вы пользовались только функциями СУММ (SUM), СРЗНАЧ (AVERAGE), СЧЕТ (COUNT) и МАКС (МАХ). Давайте рассмотрим на примере некоторые функции из разряда текстовых и логических, а также функции для работы с датой и временем. Результат всех вычислений, которые будут выполнены в этом упражнении, представлен на рис. 10.12.

Рис. 10.12. Результат работы функций Excel

1. На листе Формулы выделите и скопируйте ячейки B1:G1.

2. Разверните лист Лист3 (Sheet3).

3. Щелкните правой кнопкой мыши в ячейке А1 и выберите в контекстном меню команду Вставить (Paste).

4. Введите в ячейку A3 формулу: =ЛЕВСИМВ(А1;3) (=LEFT(A1;3))

Эта формула возвращает три левых символа ячейки А1.

5. Перетащите угловой маркер выделения вправо, чтобы рамка охватила ячейки A3:F3. В результате в третью строку будут выводиться сокращенные варианты названий месяцев из ячеек первой строки.

6. Щелкните в ячейке H3 и введите формулу: =СЦЕПИТЬ(B3;03) (CONCATENATE(B3;D3))

В ячейке H3 появится объединение строк Фев и Апр.

7. В ячейку А8 введите формулу =ТДАТА() (=N0WO)

После нажатия клавиши Enter в ячейке появятся текущие дата и время.

8. Введите в ячейки с В8 по В13 следующие формулы:

=Г0Д(А8) (=YEAR(A8))

=МЕСЯЦ(А8) (=M0NTH(А8))

=ДЕНЬ(А8) (=DAY(A8))

=ЧАС(А8) (=H0UR(A8)),

=МИНУТЫ(А8) (=MINUTE(A8))

=СЕКУНДЫ(А8) (=SEC0ND(A8))

В этих ячейках появятся по отдельности все шесть компонентов текущих даты и времени.

9. Дважды щелкните в ячейке А8, чтобы перевести ее в режим редактирования.

10. Щелкните на кнопке ввода слева от строки формул. Эта операция приведет к обновлению значения в ячейке А8, что повлияет и на числа в ячейках В8:В13.

Теперь давайте выполним логическую операцию.

11. Введите в ячейку А4 формулу: =ЕСЛИ(A3="Фев"; «Да»; «Нет») (=IF(A3="Фев"; «Да»;"Нет"))

Эта операция сравнивает значение ячейки A3 с текстовой строкой Фев. В случае равенства выводится текст второго аргумента – Да. В случае неравенства выводится текст третьего аргумента – Нет. Так как в ячейке A3 присутствует текст Янв, то результатом этой операции будет значение Нет.

12. Растяните маркер ячейки А4 вправо, чтобы рамка охватила диапазон A4:F4.

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

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

Примечание

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

Таблица 10.8. Знаки сравнения