Создать таблицу расчета дохода сотрудников организации

Упражнение 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. Таким образом, мы задали абсолютные имена ячейкам.

Формулы для расчета:

1. Подоходный налог = (Оклад – Необлагаемый налогом доход)*% подоходного налога. Обратите внимание, что когда вы будете щелкать по ячейкам С3 и С4 вместо адреса будут выходить присвоенное имя! Для копирования формулы в остальные ячейки столбца D используйте маркер заполнения
2. Отчисления в благотворительный фонд = Оклад * % отчисления в благотворительный фонд (ячейка С5 уже имеет имя)! Для копирования формулы в остальные ячейки столбца Е используйте маркер заполнения
3. Всего удержано = Подоходный налог + Отчисления в благотворительный фонд
4. К выдаче = Оклад – Всего удержано

Постройте объемную гистограмму по данным столбца «К выдаче».

Упражнение 3. Составить таблицу, вычисляющую n-й член и сумму арифметической прогрессии.

Ключ к заданию

Формула суммы n первых членов арифметической прогрессии: S_n = (a₁+a_n)*n/2.

Здесь: а₁ – первый член прогрессии;

d – разность прогрессии.

Ø Ввод заголовка таблицы. В ячейку А1 вводим заголовок таблицы. Длина текста превышает ширину таблицы, поэтому нужно сформатировать заголовок. Выделяем ячейки А1 – D1 и вызываем диалоговое окно Формат ячеек (из контекстного меню). На вкладке Выравнивание устанавливаем переключатель Переносить по словам и Объединение ячеек. Выравнивание по горизонтали – по центру, по вертикали – по центру. Можно выбрать шрифт для заголовка, его цвет и начертание (вкладка – Шрифт).

Ø Форматирование строки заголовков (d, n, a_n, S_n). Для набора нижних индексов: вызываем диалоговое окно Формат ячеек, вкладка Шрифт, переключатель Подстрочный в группе Видоизменение.

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

Ø Ввод значений n. Заполнить первые две ячейки, затем выделить их и протянуть маркер заполнения вниз на требуемое количество ячеек.

Ø Получение значений а_n. Ввести в первую ячейку значение первого члена арифметической прогрессии, во вторую – формулу для вычисления n-го члена прогрессии. Поскольку формулы содержат ссылку на ячейку С3, адрес которой при копировании должен оставаться постоянным, этот адрес нужно сделать абсолютным. Для этого в строке формул курсор ставим перед адресом С3 и нажимаем клавишу F4, адрес примет вид $C$3, что означает, что ссылка на ячейку С3 не будет меняться при копировании. Затем, выделив ячейку с формулой, заполняем остальные ячейки данной формулой, протянув маркер заполнения вниз на требуемое количество ячеек.

Ø Аналогично вводится формула для вычисления суммы n членов прогрессии.

Ø Оформить таблицу: выполнить форматирование данных и «шапки» таблицы.

Лабораторная работа № 3

Консолидация данных и сводные таблицы

Цель работы: изучение информационной технологии объединения данных в MS Excel.

Теоретические сведения

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

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

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

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

Основной недостаток консолидации в том, что в итоговой таблице не производится автоматический перерасчет данных. Чтобы обновить таблицу при изменении исходных данных, необходимо повторно запускать консолидацию. Исходные данные в окне Консолидация сохраняются. Второй недостаток в том, что процедура консолидации не привязывается к ячейке и не является объектом на рабочем листе, следовательно, на каждом рабочем листе в данный момент можно иметь только один набор консолидируемых данных.

Сводная таблица – это еще один инструмент обработки больших списков с данными. При создании сводных таблиц решаются три задачи одновременно: сразу подводятся итоги, выполняется сортировка и фильтрация данных. Для построения сводной таблицы выделяется вся БД обязательно с заголовками и выбирается командная вкладка Вставка, Сводная таблица. В появившемся диалоговом окне необходимо указать, куда следует поместить отчет сводной таблицы: на новый лист или на существующий. При выборе второго варианта активизируется поле, где нужно указать диапазон размещения сводной таблицы, ОК.

После этого на экране с левой стороны появляется шаблон внешнего вида сводной таблицы, а с правой – список полей сводной таблицы.

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

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

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

В области «Страница», «Строка» и «Столбец» каждое поле может помещаться только один раз.

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

Для того чтобы удалить поле из области построения, его кнопку нужно перетащить за пределы области построения таблицы.

Для дальнейшей работы со сводной таблицей предназначены две командные вкладки: Параметры и Конструктор. Кнопка Обновить данныенеобходима в том случае, когда в источник данных для Сводной таблицы вносятся изменения и необходимо, чтобы данные в таблице были пересчитаны.

Организация стока поверхностных вод: Наибольшее количество влаги на земном шаре испаряется с поверхности морей и океанов (88‰).

Папиллярные узоры пальцев рук — маркер спортивных способностей: дерматоглифические признаки формируются на 3-5 месяце беременности, не изменяются в течение жизни.

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

Источник

Задание 2. Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel (Пуск/Программы/Microsoft Excel).

2. Откройте файл «Расчеты», созданный в предыдущих Практических работах (Файл/Открыть).

3. На новом листе электронной книги «Расчеты» создайте таблицу констант (отпускная цена одного изделия) и основную расчетную таблицу по заданию.

4. Введите исходные данные. При вводе номеров в колонку «А» (числа 1, 2, 3 и т.д.) используйте прием автозаполнения ряда чисел. Для этого наберите два первых числа ряда (числа 1 и 2), выделите их мышкой и подведите курсор к правому нижнему углу выделенных ячеек до изменения вида курсора им черный крестик. Прихватите мышью маркер автозаполнения и протяните его вниз до нужного значения — произойдет создание ряда натуральных чисел (арифметическая прогрессия).

5. Выделите цветом ячейку со значением константы — отпускной цены 57,00 р.

GРекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации, рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши F4).

6. Произведите расчеты во всех строках таблицы. Формулы для расчета:

Выпуск продукции = Количество выпущенных изделий * Отпускная цена одного изделия,

в ячейку С7 введите формулу = С5*$Е$2 (ячейка Е2 задана в виде абсолютной адресации);

Себестоимость выпускаемой продукции = Количество выпущенных изделий * Себестоимость одного изделия, в ячейку С8 введите формулу = С5*С6;

Прибыль от реализации продукции = Выпуск продукции — Себестоимость выпускаемой продукции, в ячейку С9 введите формулу = С7-С8;

Рентабельность продукции = Прибыль от реализации продукции/Себестоимость выпускаемой продукции, в ячейку С10 вве­дите формулу = С9/С8.На строку расчета рентабельности продукции наложите Процентный формат чисел. Остальные расчеты производите в Денежном формате.

Формулы из колонки «С» скопируйте автокопированием (маркер автозаполнения) вправо по строке в колонки «D» и «Е».

7. Выполните текущее сохранение файла (Файл/Сохранить).

Задание 2. Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

Источник

Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

Тема: Относительная и абсолютная адресация MS EXCEL

Цель занятия. Изучение информационной технологии организации расчетов с абсолютной адресацией данных (при работе с константами) в таблицах MS EXCEL.

Задание 1.

Создать таблицу расчета рентабельности продукции. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены ниже.

1. Запустите редактор электронных таблиц MS Excel (Пуск/Программы/ MS Office/ MS Excel).
2. На Листе1 электронной книги создайте таблицу констант (отпускная цена одного изделия) и основную расчетную таблицу по заданию.
3. Введите исходные данные. При вводе номеров в колонку «А» (числа 1, 2, 3 и т. д.) используйте прием автозаполнения ряда чисел. Для этого наберите два первых числа ряда (числа 1 и 2), выделите их мышкой и подведите курсор к правому нижнему углу выделенных ячеек до изменения вида курсора на черный крестик. Прихватите мышью маркер автозаполнения и протяните его вниз до нужного значения – произойдет создание ряда натуральных чисел (арифметическая прогрессия).

4. Выделите цветом ячейку со значением константы – отпускной цены 57,00 р. (Щелчок правой кнопкой мыши по ячейке в контекстном меню выбрать Формат ячеек откроется диалоговое окно Формат ячеек выбрать вкладку Заливка и выбрать цвет.) Рекомендации. Для удобства работы и форматирования навыков работы с абсолютным видом адресации, рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул окрашенная ячейка (т. е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши [F4]).

1. Произведите расчеты во всех строках таблицы. Формулы для расчета:

а) Выпуск продукции = Количество выпущенных изделий х Отпускная цена одного изделия, в ячейку C7 введите формулу =C5*$E$2 (ячейка E2задана в виде абсолютной адресации);

б) Себестоимость выпускаемой продукции = Количество выпущенных изделий х Себестоимость одного изделия, в ячейку C8 введите формулу = C5*C6;

в) Прибыль от реализации продукции = Выпуск продукции – Себестоимость выпускаемой продукции, в ячейку C9 введите формулу = C7 – C8;

г) Рентабельность продукции = Прибыль от реализации продукции / Себестоимость выпускаемой продукции, в ячейку C10 введите формулу = C9/ C8.

На строку расчета рентабельности продукции наложите Процентный формат чисел (Главная/Число). Остальные расчеты производите в Денежном формате (Главная/Число).

Формулы из колонки «C» скопируйте автокопированием (за маркер автозаполнения) вправо по строке в колонки «D» и «E».

1. Выполните текущее сохранение файла .

Задание 2.

Создать таблицу расчета дохода сотрудников организации. Константы вводить в расчетные формулы в виде абсолютной адресации.

Исходные данные представлены ниже.

1. Переименуйте ярлычок Лист2 , присвоив ему имя «Заработная плата».
2. На листе «Заработная плата» создайте таблицу по образцу.
3. Введите значения констант и исходные данные. Форматы данных (денежный или процентный) задайте по образцу задания.
4. Произведите расчеты по формулам, применяя к константам абсолютную адресацию.

Формулы для расчетов:

а) Подоходный налог = (Оклад – Необлагаемый налогом доход) х % подоходного налога, в ячейку D10 введите формулу =(C10 — $C$3)*$C$4;

б) Отчисления в благотворительный фонд = Оклад х % отчисления в благотворительный фонд, в ячейку E10 введите формулу =C10*$C$5;

в) Всего удержано = Подоходный налог + Отчисления в благотворительный фонд, в ячейку F10 введите формулу =D10 + E10;

г) К выдаче = Оклад – Всего удержано, в ячейку G10 введите формулу =C10 – F10.

д) Итого находим автосумму по каждому из параметров(Формулы/Автосумма)

1. Постройте объемную гистограмму по данным столбца «К выдаче», проведите форматирование диаграммы.
2. Выполните текущее сохранение файла

Источник