Office 2007. Самоучитель
.pdf
252 |
Занятие 10. Формулы и функции |
Таблица 10.7 (продолжение) |
|
|
|
Сообщение |
Описание ошибки |
|
|
#Í/Ä (#N/A) |
Данные ячейки одного из аргументов формулы в данный момент недоступны |
# ÑÑÛË! (#REF!) |
Неверная ссылка на ячейку |
#ЧИСЛО! (#NUM!) |
Невозможно вычислить результат формулы, либо он слишком велик или |
|
мал для корректного отображения в ячейке |
#ПУСТО! (#NULL!) |
Результат поиска пересечения двух непересекающихся областей, то есть |
|
неверная ссылка |
|
|
Ðèñ. 10.11. Обнаружение ошибки
4.Измените содержимое ячейки так, чтобы получилась формула
=СРЗНАЧ(B11:F11) (=AVERAGE(B11:F11))
ПРИМЕЧАНИЕ
Инструмент автокоррекции формул сам исправляет наиболее очевидные ошибки. Например, если в рассматриваемой формуле вы забудете ввести только закрывающую скобку, программа сама добавит ее.
Упражнение 6. Применение функций
До сих пор вы пользовались только функциями ÑÓÌÌ (SUM), СРЗНАЧ (AVERAGE), Ñ×ÅÒ (COUNT) è ÌÀÊÑ (MAX). Давайте рассмотрим на примере некоторые функции
Формулы |
253 |
из разряда текстовых и логических, а также функции для работы с датой и временем. Результат всех вычислений, которые будут выполнены в этом упражнении, представлен на рис. 10.12.
Ðèñ. 10.12. Результат работы функций Excel
1.На листе Формулы выделите и скопируйте ячейки B1:G1.
2.Разверните лист Ëèñò3 (Sheet3).
3.Щелкните правой кнопкой мыши в ячейке A1 и выберите в контекстном меню команду Вставить (Paste).
4.Введите в ячейку A3 формулу:
=ЛЕВСИМВ(A1;3) (=LEFT(A1;3))
Эта формула возвращает три левых символа ячейки A1.
5.Перетащите угловой маркер выделения вправо, чтобы рамка охватила ячейки A3:F3. В результате в третью строку будут выводиться сокращенные варианты названий месяцев из ячеек первой строки.
254 |
Занятие 10. Формулы и функции |
6.Щелкните в ячейке H3 и введите формулу:
=СЦЕПИТЬ(B3;D3) (=CONCATENATE (B3;D3))
Âячейке H3 появится объединение строк Ôåâ è Àïð.
7.В ячейку A8 введите формулу
=ТДАТА() (=NOW())
После нажатия клавиши Enter в ячейке появятся текущие дата и время.
8.Введите в ячейки с B8 ïî B13 следующие формулы:
=ÃÎÄ(A8) (=YEAR(A8))
=МЕСЯЦ(A8) (=MONTH (A8))
=ÄÅÍÜ(A8) (=DAY(A8))
=×ÀÑ(A8) (=HOUR(A8)),
=МИНУТЫ(A8) (=MINUTE(A8))
=СЕКУНДЫ(A8) (=SECOND(A8))
Âэтих ячейках появятся по отдельности все шесть компонентов текущих даты и времени.
9.Дважды щелкните в ячейке A8, чтобы перевести ее в режим редактирования.
10.Щелкните на кнопке ввода слева от строки формул. Эта операция приведет к обновлению значения в ячейке A8, что повлияет и на числа в ячейках
B8:B13.
Теперь давайте выполним логическую операцию.
11.Введите в ячейку A4 формулу:
=ÅÑËÈ(A3="Ôåâ";"Äà";"Íåò") (=IF(A3="Ôåâ";"Äà";"Íåò") )
Эта операция сравнивает значение ячейки A3 с текстовой строкой Ôåâ. В слу- чае равенства выводится текст второго аргумента — Äà. В случае неравенства выводится текст третьего аргумента — Íåò. Так как в ячейке A3 присутствует текст ßíâ, то результатом этой операции будет значение Íåò.
12.Растяните маркер ячейки A4 вправо, чтобы рамка охватила диапазон A4:F4.
Âрезультате формулы строки 4 будут проверять ячейки строки 3 на совпадение их содержимого со строкой Ôåâ. Как видите, слово Äà появилось только в столбце B.
Знаки, которые используются в Excel для сравнения величин, перечислены в табл. 10.8. Обратите внимание, что текстовые строки, выступающие в качестве аргументов функций, должны заключаться в двойные кавычки.
ПРИМЕЧАНИЕ
Текстовые строки равны, если попарно совпадают все их символы, в противном случае строки не равны. Одна текстовая строка больше другой, если в ней код первого символа, несовпадающего в обеих строках, больше кода того же символа во второй строке.
Контрольное упражнение |
255 |
Таблица 10.8. Знаки сравнения
Знак Значение
=Равно
>Больше
<Меньше
>= |
Больше или равно |
<= |
Меньше или равно |
<> |
Не равно |
|
|
Контрольное упражнение
На этом занятии вы познакомились лишь с незначительным числом функций, доступных в Excel 2007. Остальные функции вам придется изучать самостоятельно с помощью справочной системы Excel. Выполняя это контрольное упражнение, проверьте, насколько хорошо вы освоили приемы выполнения расчетов в электронных таблицах.
1.В файле Электронная таблица.xls раскройте лист Календарь, созданный в контрольном упражнении предыдущего занятия (рис. 10.13).
Ðèñ. 10.13. Лист Календарь
256 |
Занятие 10. Формулы и функции |
2.Щелкните в ячейке G3, введите формулу, вычисляющую среднее значение чисел строки 3 календаря, и нажмите клавишу Enter.
3.В ячейку H3 выведите целую часть этого среднего значения.
С помощью какой функции выделяется целая часть числа?
4.Продублируйте две полученные формулы во всех строках календаря.
Как продублировать формулы двух ячеек в нескольких строках?
5.В ячейке G11 сосчитайте сумму средних значений, а в ячейке H11 с помощью функции ПРОИЗВЕД (PRODUCT) — произведение целых частей средних значений.
6.В ячейке F11 с помощью функции ÑÓÌÌ (SUM) подсчитайте сумму всех ячеек строк 3 и 4 листа Excel.
Как задать в качестве аргумента функции две строки листа?
7.Скопируйте содержимое ячейки F11.
8.Щелкните в ячейке F13 и вставьте в нее формулу из буфера обмена.
Сумму каких ячеек подсчитывает вставленная формула?
Подведение итогов
С помощью упражнений этого занятия вы научились вводить формулы, пользоваться функциями, копировать формулы, находить и исправлять ошибки. Вы узнали о механизме относительной адресации ячеек и изучили некоторые функции, работающие с текстовыми строками и величинами типа даты и времени.
Чтобы наглядно представить результаты вычислений, их нужно оформить в виде диаграммы или графика. Именно этому способу отображения данных Excel и посвящено следующее занятие.
Занятие 11
Графики и диаграммы
Тема занятия
На этом занятии изучаются приемы построения диаграмм, иллюстрирующих данные листа Excel. Вы познакомитесь со следующими операциями:
выбор типа диаграммы;
подписи маркеров данных;
форматирование текста;
совмещение данных разных листов;
построение объемной диаграммы;
форматирование рядов данных.
Модуль диаграмм приложения Excel автоматически генерирует диаграммы, отображающие данные листа в графической форме. Вам остается только выбрать нужный диапазон ячеек и указать тип диаграммы, а затем оформить полученный графический объект по своему вкусу.
В новой версии Office способ работы с диаграммами изменился. Если вы работали
ñпредыдущими версиями Office, то вы поймете, что изменения существенные. Теперь нет мастера диаграмм, с помощью которого и производилась вся работа
ñдиаграммами в предыдущих версиях. В Office 2007 в соответствие с концепцией нового интерфейса для работы с диаграммами используются три вкладки. Они позволяют выполнять все те же действия (и даже больше), что и мастер диаграмм, но более удобным и наглядным способом.
Добавление диаграммы
Давайте продолжим работать с файлом Электронная таблица.xls. Откройте его в Excel и разверните лист Клиенты. В упражнениях этого занятия будет добавлена и отформатирована диаграмма, представляющая данные этого листа в графической форме.
258 |
Занятие 11. Графики и диаграммы |
Упражнение 1. Создание диаграммы
Для создания диаграммы проще всего воспользоваться инструментами, расположенными в группе Диаграммы (Charts) вкладки Вставка (Insert). Вы уже познакомились с ними на занятии 3.
1.Чтобы создать диаграмму, следует сначала указать источник данных. Для этого выделите прямоугольную область ячеек A1:G7.
ПРИМЕЧАНИЕ
Источник данных можно задать и после создании диаграммы, но удобнее делать это перед созданием диаграммы.
СОВЕТ
Исходные числовые данные для диаграммы следует выделять вместе со строкой и столбцом заголовков таблицы, чтобы соответствующие названия автоматически появлялись в легенде и на оси категорий диаграммы.
2.Тип диаграммы можно выбрать в коллекциях, вызываемых с помощью кнопок в группе Диаграммы (Charts) на вкладке Вставка (Insert). Выберем линейча- тый тип диаграммы (clustered bar).
Ðèñ. 11.1. Выбор типа диаграммы
Добавление диаграммы |
259 |
ПРИМЕЧАНИЕ
Чтобы просмотреть сразу все типы диаграмм, вы можете воспользоваться командой Все типы диаграмм (All Chart Types), которая расположена внизу коллекции с типами диаграмм. После выполнения этой команды откроется диалоговое окно.
3.Щелкните на кнопке Линейчатая (Bar), как показано на рис. 11.1.
4.В появившейся коллекции выберите пункт Линейчатая с группировкой (Clustered Bar).
5.После выбора диаграммы на листе появится область диаграммы с вашей диаграммой.
Обратите внимание, что на линейке появились несколько дополнительных вкладок для работы с диаграммой. С помощью этих вкладок мы и будем настраивать параметры нашей диаграммы.
Созданная нами диаграмма содержит лишний ряд данных, появившийся изза наличия в выделенной области пустой строки 2.
6.Чтобы скорректировать источник данных, щелкните на кнопке Выбрать данные (Select Data), расположенной на вкладке Конструктор (Design) в группе Данные (Data), как показано на рис. 11.2.
Ðèñ. 11.2. Скорректированный источник данных
7.Появится диалоговое окно Выбор источника данных (Select Data Source).
8.Щелкните на кнопке, расположенной в правой части поля Диапазон данных для диаграммы (Chart data range).
260 |
Занятие 11. Графики и диаграммы |
9.Диалоговое окно свернется в одну строку, открыв доступ к листу Excel.
10.Выделите ячейки A1:G1.
11.Нажмите клавишу Ctrl и, не отпуская ее, перетащите мышь из ячейки A3 в ячейку G7. Эта операция добавит к выделению вторую группу ячеек, как показано на рис. 11.2.
12.Щелкните еще раз на кнопке, расположенной в правой части поля Диапазон данных для диаграммы (Chart data range), чтобы вновь развернуть диалоговое окно. После этого в поле Диапазон данных для диаграммы (Chart data range) появится формула
=Клиенты!$A$1:$G$1; Клиенты!$A$3:$G$7
В этой формуле с помощью абсолютных ссылок задаются две области данных из листа Клиенты. Немного потренировавшись, можно научиться вручную вводить формулы источников данных любых конфигураций.
Ðèñ. 11.3. Настройка параметров диаграммы
13.Чтобы настроить макет диаграммы, перейдите на вкладку Макет (Layout), как показано на рис. 11.3.
Добавление диаграммы |
261 |
14.Чтобы добавить название диаграммы на вкладке Макет (Layout) в группе Подписи (Labels), щелкните на кнопке Название диаграммы (Chat Title).
15.В появившейся коллекции выберите элемент Над диаграммой (Above Title). В верхней части области диаграммы появится название диаграммы.
16.Чтобы изменить его, наведите указатель мыши на область названия диаграммы и щелкните левой кнопкой мыши.
17.Введите в качестве названия текст Активность клиентов.
18.Чтобы добавить подписи для осей, щелкните на кнопке Названия осей (Axis Titles). В появившемся меню можно задать режим вывода вертикальной и горизонтальной осей.
19.Для основной горизонтальной оси выберите вариант Название под осью (Title Below Axis).
20.Для основной вертикальной оси выберите вариант Вертикальное название
(Vertical Title).
21.После добавления подписей осей введите текст для них. Для горизонтальной оси введите текст Объем продаж, а для вертикальной — Месяцы (ñì. ðèñ. 11.3).
22.Чтобы поместить диаграмму на отдельный лист, на вкладке Конструктор
(Design) в группе Расположение (Location) щелкните на кнопке Переместить диаграмму (Move Chart).
23.В появившемся диалоговом окне Перемещение диаграммы (Move Chart) установите переключатель На отдельном листе (New Sheet), как показано на рис. 11.4.
24.В текстовое поле имени нового листа введите название Диаграмма. Результат показан на рис. 11.5.
Ðèñ. 11.4. Выбор расположения диаграммы
Упражнение 2. Выбор типа диаграммы
Для лучшего отражения той или иной природы данных следует подобрать соответствующий тип диаграммы. Его, так же как и любые другие параметры, заданные при создании диаграммы, всегда можно сменить позже в процессе форматирования документа.
1.Чтобы выбрать другой тип диаграммы, выполните команду Конструктор Тип Изменить тип диаграммы (Design Type Change Chart Type). Откроется диалоговое окно, в котором вы можете выбрать нужный тип диаграммы (рис. 11.6).