Успешно изучив материал, Вы будете знать:

1. принципы создания формул в MS Excel 2007;

2. требования к аргументам функции;

3. виды диаграмм и их назначение;

4. порядок работы с мастером функций и мастером диаграмм;

5. основные операции, необходимые при работе с базой данных;

6. принципы использования форм для оптимизации работы с БД;

7. принципы создания сводных таблиц.

После изучения данной темы Вы будете уметь:

1. использовать встроенные функции и создавать собственные формулы;

2. работать с мастером функций и с мастером диаграмм;

3. применять сортировку и фильтр к содержимому БД;

4. работать с мастером сводных таблиц и диаграмм.

После изучения материала Вы будете обладать навыками:

1. создания сводных таблиц, используя различные источники;

2. использования сводных таблиц для оптимизации работы с БД;

3. создания и использования формы для поиска по содержимому БД, ввода и редактирования значений;

4. работы с мастером функций и мастером диаграмм;

5. создания собственных формул.

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

База данных

Гистограмма, диаграмма, график

Сводная таблица

Сортировка

Фильтр

Форма

Формула

Функция

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

Данные, используемые в Excel, делятся на числовые и текстовые.

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

Числовые данные для удобства пользователя могут быть представлены в разных форматах (рис. 8.1). Для того чтобы число приобрело привычный вид даты, нужно выделить ячейку с этим числом и на Главной лентев группе Число выбрать в верхнем поле «Дата» и формат ее представления. Если вы удалите число из ячейки, новое число все равно будет иметь формат «Дата». Чтобы в ячейку или диапазон можно было вводить произвольные форматы данных, нужно в группе Редактированиевыбрать из выпадающего списка кнопки «Ластик» команду Очистить формат.

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

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

Например, чтобы вычислить разность чисел в ячейках А1 и В1, следует ввести формулу «=А1-В1». Для этого после ввода знака «=» нужно щелкнуть мышью по ячейке А1, нажать клавишу «–», щелкнуть мышью по ячейке В1 и нажать < Enter >.

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

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

Можно вводить как отдельные значения, так и целые диапазоны (они обозначаются так: имя левой верхней ячейки и после «:» — имя правой нижней ячейки диапазона). Чтобы ввести диапазон, достаточно выделить его мышью.

Формулы можно копировать из ячейки в ячейку, при этом происходит соответствующее изменение имен ячеек: например, если формулу «=А1+А2» из ячейки А3 скопировать в В3, получим формулу «=В1+В2». Для того чтобы, например, имя А1 не изменилось на В1, используются абсолютные ссылки. В абсолютной ссылке каждому элементу предшествует знак доллара: $A$1. Знак доллара «запирает» ячейку, и Excel не меняет ее при заполнении формулой или копировании ее в другую ячейку. Знак $, поставленный только перед буквой А, закрепляет только столбец, а только перед 1 — закрепляет только строку. На рис. 8.4 показана таблица, в которой по отработанному времени сотрудникам начисляются выплаты согласно единому тарифу, записанному в 1-й ячейке таблицы «Тариф».

Для упрощения ввода данных, проведения расчетов и анализа результатов в Excel 2007 используются настраиваемые стили таблиц (рис. 8.3).

Использование стилей позволяет (рис. 8.4):

• создать эффектное оформление таблицы;

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

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

• использовать условное форматирование для анализа введенных данных и результатов расчетов (рис. 8.5);

• сортировать и отбирать данные в разных столбцах по определенному условию. Эту возможность мы обсудим, рассматривая работу с базами данных.

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

Для создания диаграмм выделите требуемый диапазон или произвольную ячейку таблицы и на ленте Вставка в группе Диаграммавыберите тип и вид диаграммы. На рис. 8.6 показаны стандартные типы диаграмм.

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

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

Чтобы отредактировать готовую диаграмму, нужно щелкнуть мышью по любому месту диаграммы (выделить ее) и воспользоваться дополнительными вкладками из группы «Работа с диаграммами».

Вкладка «Конструктор» позволяет редактировать исходные данные, тип диаграммы и применять экспресс-стили для ее форматирования. Там также есть кнопка выбора места размещения диаграммы.

Вкладка «Макет» позволяет редактировать и форматировать части диаграммы (рис. 8.7): оси, сетку, ряды данных.

Вкладка «Формат»позволяет управлять форматом оформления надписей.

Excel 2007 позволяет также работать с базами данных. База данных — список с установленной структурой, где каждой записи соответствует строка рабочего листа (рис. 8.8).

При работе с базой данных обычно необходимо иметь возможность сортировать и фильтровать данные.

Для сортировки следует сделать активной одну из ячеек базы и воспользоваться кнопкой «Сортировка и фильтры» на вкладке Редактирование Главной ленты.

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

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

Производить фильтрацию возможно по нескольким полям. Чтобы вновь отобразить всю базу данных, выберите пункт Все в качестве критерия во всех полях или просто воспользуйтесь командой «Данные» — «Фильтр» — «Отобразить все».

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

• содержащие часть текста («*»);

• содержащие любую букву («?»);

• не содержащие заданного текста («кроме»);

• удовлетворяющие одновременно нескольким условиям («и»).

Например, для отбора всех записей о молоке и сметане фирмы «Домик в деревне» (рис. 8.10) можно применить фильтр, показанный на рис. 8.11.

Сводные таблицы и сводные диаграммы — это мощный инструмент для анализа данных. Они обобщают сведения в столбцах базы данных в их связи друг с другом.

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

Для начала следует указать источник данных (рис. 8.12): имеется возможность выбрать не открытый файл («внешний источник»).

Потом нужно разработать макет. Сводная таблица содержит четыре области: фильтр, названия столбцов, названия строк и суммарные значения (рис. 8.13).

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

В полях «Строка» и «Столбец» записывают сравниваемые данные, в поле «Значение» — те данные, которые надо объединить.

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

1. MS Excel 2007 является мощным инструментом для проведения расчетов и анализа результатов. Расчеты в таблицах можно производить с помощью формул или разнообразных встроенных функций.

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

3. MS Excel 2007 также предоставляет широчайшие возможности для простого и быстрого построения диаграмм и графиков.

4. Функция автоматического форматирования таблиц позволяет создавать эффектные отчеты. Интегрированные в MS Office графические функции дают возможность вставлять в отчеты готовые рисунки или создавать их самостоятельно.

Выполните задания к теме 8 в тетради-практикуме.