Упражнение 1. Создать таблицу расчета рентабельности продукции.
Ключ к заданию
Исходные данные:
Константы вводить в расчетные формулы в виде абсолютной адресации.
Ячейка Е2 во всех формулах будет иметь абсолютный адрес, который задается двумя способами:
1) Нажатием клавиши F4
2) командная вкладка Формулы – Присвоить имя
Формулы для расчета данных в таблице:
1) Выпуск продукции = Количество выпущенных изделий * Отпускная цена одного изделия
Формула в ячейке С7 будет следующая: =С5*$E$2 (знаки «доллара» появятся после нажатия клавиши F4).
2) Себестоимость выпускаемой продукции = Количество выпущенных изделий * Себестоимость одного изделия. Для этого в ячейку С8 ввести формулу =С5*С6
3) Прибыль от реализации продукции = Выпуск продукции – Себестоимость выпускаемой продукции, для этого в ячейку С9 введите формулу = С7-С8
4) Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции. В ячейку С10 введите формулу = С9/С8, после чего задайте процентный формат.
Упражнение 2. Создать таблицу расчета дохода сотрудников организации.
Ключ к заданию
Константы вводить в расчетные формулы в виде абсолютной адресации.
Исходные данные:
Прежде всего задаем абсолютные ссылки на ячейки С3, С4 и С5 следующим способом:
Установить курсор в ячейку С3 и выбрать на командной вкладке Формулы – Присвоить имя, появится окно:
Нажать ОК. Аналогичную операцию повторить в ячейках С4 и С5. Таким образом, мы задали абсолютные имена ячейкам.
Формулы для расчета:
- Подоходный налог = (Оклад – Необлагаемый налогом доход)*% подоходного налога. Обратите внимание, что когда вы будете щелкать по ячейкам С3 и С4 вместо адреса будут выходить присвоенное имя! Для копирования формулы в остальные ячейки столбца D используйте маркер заполнения
- Отчисления в благотворительный фонд = Оклад * % отчисления в благотворительный фонд (ячейка С5 уже имеет имя)! Для копирования формулы в остальные ячейки столбца Е используйте маркер заполнения
- Всего удержано = Подоходный налог + Отчисления в благотворительный фонд
- К выдаче = Оклад – Всего удержано
Постройте объемную гистограмму по данным столбца «К выдаче».
Упражнение 3. Составить таблицу, вычисляющую n-й член и сумму арифметической прогрессии.
Ключ к заданию
Формула n-го члена: аn = a1 + d*(n-1).
Формула суммы n первых членов арифметической прогрессии: Sn = (a1+an)*n/2.
Здесь: а1 – первый член прогрессии;
d – разность прогрессии.
Ø Ввод заголовка таблицы. В ячейку А1 вводим заголовок таблицы. Длина текста превышает ширину таблицы, поэтому нужно сформатировать заголовок. Выделяем ячейки А1 – D1 и вызываем диалоговое окно Формат ячеек (из контекстного меню). На вкладке Выравнивание устанавливаем переключатель Переносить по словам и Объединение ячеек. Выравнивание по горизонтали – по центру, по вертикали – по центру. Можно выбрать шрифт для заголовка, его цвет и начертание (вкладка – Шрифт).
Ø Форматирование строки заголовков (d, n, an, Sn). Для набора нижних индексов: вызываем диалоговое окно Формат ячеек, вкладка Шрифт, переключатель Подстрочный в группе Видоизменение.
Ø Ввод величины разности арифметической прогрессии. Для заполнения всего столбца одинаковыми значениями ввести значение в первую ячейку, затем выделить ее, подвести указатель мыши к маркеру заполнения и когда он примет форму крестика, протянуть его вниз на требуемое количество ячеек.
Ø Ввод значений n. Заполнить первые две ячейки, затем выделить их и протянуть маркер заполнения вниз на требуемое количество ячеек.
Ø Получение значений аn. Ввести в первую ячейку значение первого члена арифметической прогрессии, во вторую – формулу для вычисления n-го члена прогрессии. Поскольку формулы содержат ссылку на ячейку С3, адрес которой при копировании должен оставаться постоянным, этот адрес нужно сделать абсолютным. Для этого в строке формул курсор ставим перед адресом С3 и нажимаем клавишу F4, адрес примет вид $C$3, что означает, что ссылка на ячейку С3 не будет меняться при копировании. Затем, выделив ячейку с формулой, заполняем остальные ячейки данной формулой, протянув маркер заполнения вниз на требуемое количество ячеек.
Ø Аналогично вводится формула для вычисления суммы n членов прогрессии.
Ø Оформить таблицу: выполнить форматирование данных и «шапки» таблицы.
Лабораторная работа № 3
Консолидация данных и сводные таблицы
Цель работы: изучение информационной технологии объединения данных в MS Excel.
Теоретические сведения
Консолидацияпозволяет объединять данные из областей-источников и помещать их в область назначения. При консолидации могут использоваться различные функции: суммирования, расчета среднего арифметического, подсчета максимальных и минимальных значений и т.д.
Если таблица достаточно сложная, то диапазоны данных приходится указывать вручную. Перед выполнением консолидации необходимо определить в каких исходных областях данных располагаются интересующие нас сведения. Данные в этих областях должны быть единообразно организованы. Кроме того области данных должны представлять собой блоки строк или столбцов с заголовками.
Для вызова диалогового окна Консолидация выбирается командная вкладка Данные – Консолидация. Перед вызовом этого окна выделяют ячейку, которая будет соответствовать левому верхнему углу области назначения, где будет размещена итоговая таблица. Справа и снизу от этой ячейки должно быть достаточно места.
После вызова окна консолидации в списке Функцияукажем нужная (наиболее распространенная функция Сумма). Курсор устанавливается в поле Ссылка. В этой строке есть кнопка, позволяющая сворачивать и разворачивать окно. После этого выделяется первая исходная область, затем нажимают кнопку Добавить. После этого выделяют другие диапазоны. Диапазон имен задается либо как подписи первой строки, либо как значения левого столбца, смотря как организована таблица. После чего нажимаем ОК. Строка Итого также может участвовать в консолидации.
Основной недостаток консолидации в том, что в итоговой таблице не производится автоматический перерасчет данных. Чтобы обновить таблицу при изменении исходных данных, необходимо повторно запускать консолидацию. Исходные данные в окне Консолидация сохраняются. Второй недостаток в том, что процедура консолидации не привязывается к ячейке и не является объектом на рабочем листе, следовательно, на каждом рабочем листе в данный момент можно иметь только один набор консолидируемых данных.
Сводная таблица – это еще один инструмент обработки больших списков с данными. При создании сводных таблиц решаются три задачи одновременно: сразу подводятся итоги, выполняется сортировка и фильтрация данных. Для построения сводной таблицы выделяется вся БД обязательно с заголовками и выбирается командная вкладка Вставка, Сводная таблица. В появившемся диалоговом окне необходимо указать, куда следует поместить отчет сводной таблицы: на новый лист или на существующий. При выборе второго варианта активизируется поле, где нужно указать диапазон размещения сводной таблицы, ОК.
После этого на экране с левой стороны появляется шаблон внешнего вида сводной таблицы, а с правой – список полей сводной таблицы.
Для создания структуры сводной таблицы следует перетащить мышью кнопки нужных полей в область построения.
В область «Страница» помещают поля, в которых нужно производить отбор нужных записей. Область «Страница» может оставаться незаполненной.
В области «Строка» и «Столбец» помещают поля, которые должны быть представлены в Сводной таблице.
В области «Страница», «Строка» и «Столбец» каждое поле может помещаться только один раз.
В область «Данные» помещают поля, по которым при создании Сводной таблицы будут производиться вычисления с помощью одной из функций: сумма; количество значений; среднее; максимум и др.
Для того чтобы удалить поле из области построения, его кнопку нужно перетащить за пределы области построения таблицы.
Для дальнейшей работы со сводной таблицей предназначены две командные вкладки: Параметры и Конструктор. Кнопка Обновить данныенеобходима в том случае, когда в источник данных для Сводной таблицы вносятся изменения и необходимо, чтобы данные в таблице были пересчитаны.