 |
Тема: «Работа с табличным редактором Microsoft Excel»
|
|
|
|
Цель работы:
Задание 3.1 Создать таблицу финансовой сводки за неделю,произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных.
Исходные данные представлены на рисунке 3.1.
Рисунок 3.1 – Исходные данные для «Задания 3.1»
Порядок работы:
- Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу (при стандартной установке MS Office
выполните Пуск/Программы/Microsoft Excel).
- Введите заголовок таблицы «Финансовая сводка за неделю (тыс.р.)», начиная с ячейки А1.
- Для оформления шапки таблицы выделите ячейки на третьей строке A3:D3 и создайте стиль для оформления. Для этого выполните команду Формат/Стиль и в открывшемся окне Стиль (рисунок 3.2) наберите имя стиля «Шапка таблиц» и нажмите кнопку Изменить. В открывшемся окне на вкладке Выравнивание задайте Переносить по словам и выберите горизонтальное и вертикальное выравнивание–поцентру (рисунок 3.3), на вкладке Число укажите формат – Текстовый. После этого нажмите кнопку Добавить.
67
Рисунок 3.2 – Создание стиля оформления шапки таблицы
Рисунок 3.3 – Форматирование ячеек – задание переноса по словам
- На третьей строке введите названия колонок таблицы – «Дни недели», «Доход», «Расход», «Финансовый результат», далее заполните таблицу исходными данными согласно Заданию 3.1.
Краткая справка. Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (левой кнопкой мыши за маркер автозаполнения в правом нижнем углу ячейки)
68
- Произведите расчеты в графе «Финансовый результат» по сле-дующей формуле:
|
|
|
Финансовый результат = Доход – Расход,
для этого в ячейке D4 наберите формулу = В4 – С4.
Краткая справка. Введите расчетную формулу только для расчета по строке «Понедельник», далее произведите автокопирование формулы (так как в графе «Расход» нет незаполненных данными ячеек, можно производить автокопирование двойным щелчком мыши по маркеру автозаполнения в правом нижнем углу ячейки).
в Для ячеек с результатом расчетов задайте формат – «Де-нежный» с выделением отрицательных чисел красным цветом (рисунок 3.4) (Формат/Ячейки/ вкладка Число / формат – Денежный/ отрицательные числа – красные. Число десятичных знаков задайте равное 2). Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный.
Рисунок 3.4 – Задание формата отрицательных чисел красным цветом
и Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка fx). Функция «Среднее значение» (СРЗНАЧ) находится в разделе «Статистические». Для расчета функции СРЗНАЧ дохода установите курсор в соответствующей ячейке для расчета среднего
69
значения (В11), запустите мастер функций (Вставка/Функция/ категория – Статистические/СРЗНАЧ) (рисунок3.5).В качестве первого числавыделите группу ячеек с данными для расчета среднего значения – В4:В10. Аналогично рассчитайте «Среднее значение» расхода.
- В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Для выполнения автосуммы удобно пользоваться кнопкой Автосуммирования (å) на панели инструментов или функцией СУММ (рисунок 3.6). В качестве первого числа выделите группу ячеек с данными для расчета суммы – D4:D10.
Рисунок 3.5 – Выбор функции расчета среднего значения
Рисунок 3.6 – Задание интервала ячеек при суммировании функцией СУММ
|
|
|
70
- Проведите форматирование заголовка таблицы. Для этого вы-делите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Формат/Ячейки/ вкладка Выравнивание/ отображение – Объединение ячеек). Задайте начертание шрифта–полужирное;цвет–по вашемуусмотрению. Конечный вид таблицы приведен на рисунке 3.7.
Рисунок 3.7 – Таблица расчета финансового результата «Задание 3.1»
Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с использованием мастера диаграмм. Для этого выделите интервал ячеек с данными финансового результата и выберите команду Вставка/Диаграмма. На первом шаге работы с мастером диаграмм выберите тип диаграммы – линейчатая; на втором шаге на вкладке Ряд в окошке Подписи оси Х укажите интервал ячеек с днями недели – А4:А10 (рисунок 3.8). Далее введите название диаграммы
- подписи осей; дальнейшие шаги построения диаграммы осуществляются автоматически по подсказкам мастера. Конечный вид диаграммы приведен на рисунке 3.9.
Произведите фильтрацию значений дохода, превышающих 4000
р.
Краткая справка. В режиме фильтра в таблице видны только те данные, которые удовлетворяют некоторому критерию, при этом остальные строки скрыты. В этом режиме все операции форматирования, копирования, автозаполнения, автосуммирования и т.д. применяются только к видимым ячейкам листа. Для установления режима фильтра установите курсор внутри таблицы и воспользуйтесь командой
71
Данные/Фильтр/Автофильтр. В заголовках полей появятся стрелкивыпадающих списков.
Рисунок 3.8 – Задание Подписи оси X при построении диаграммы
Рисунок 3.9 – Конечный вид диаграммы «Задания 3.1»
Щелкните по стрелке в заголовке поля, на которое будет наложено условие (в столбце «Доход»), и вы увидите список всех неповторяющихся
72
значений этого поля. Выберите команду для фильтрации – Условие (рисунок 3.10, а).
- открывшемся окне Пользовательский автофильтр задайте условие «Больше 4000» (рисунок 3.10, б).
а) б)
|
|
|
Рисунок 3.10 – а) Выбор условия фильтрации; б) – Пользовательский автофильтр
Произойдет отбор данных по заданному условию. Проследите, как изменились вид таблицы и построенная диаграмма (рисунок 3.11).
Рисунок 3.11 – Вид таблицы после фильтрации данных
15. Сохраните созданную электронную книгу в своей папке.
73
Дополнительные задания:
Задание 3.2 Заполнить таблицу,произвести расчеты,выделитьминимальную и максимальную суммы покупки (рисунок 3.12); по ре-зультатам расчета построить круговую диаграмму суммы продаж.
Рисунок 3.12- Исходные данные для «Задания 3.2»
Используйте созданный стиль (Формат/Стиль/Шапка таблиц).
Формулы для расчета:
Сумма = Цена ´ Количество;
Всего = сумма значений колонки «Сумма».
Краткая справка. Для выделения максимального/минимального значений установите курсор в ячейке расчета, выберите встроенную функцию Excel МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма» (ячейки ЕЗ:Е10).
Задание 3.3 Заполнить ведомость учета брака,произвести расчеты,выделить минимальную, максимальную и среднюю суммы брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 8 %, построить график отфильтрованных значений изменения суммы брака по месяцам (рисунок 3.13).
Формула для расчета:
Сумма брака = Процент брака ´ Сумма зарплаты.
74
Краткая справка. В колонке «Процент брака» установите процентный формат чисел (Формат/Ячейки/ вкладка Число/ формат – Процентный).
Рисунок 3.13 – Исходные данные для «Задания 3.3»
|
|
|
