Лабораторная работа No.9 Excel (Работа с таблиц..

Лабораторная работа №9. Работа с таблицами: форматирование, формулы расчета, построение диаграмм.

Часть 1.
Порядок выполнения работы
В отделе работает 8 сотрудников. За выполненную работу отдел получил премию. Необходимо распределить премию между сотрудниками согласно коэффициенту трудового участия в работе.
Известен общий размер премии. Для каждого из сотрудников известно время, затраченное на выполнение работы, (2 – 20дней) и квалификационный разряд (1 – 16).
При заполнении столбцов таблицы пользуйтесь следующими положениями.
Коэффициент трудового участия каждого сотрудника в общей работе вычисляется произведением времени, затраченного сотрудником на выполнение работы, на квалификационный разряд.
Процент участия вычисляется как результат деления коэффициента трудового участия сотрудника на сумму КТУ всех сотрудников. Формула содержит абсолютный адрес ячейки D12.
Сумма вычисляется путем умножения процента участия сотрудника на общий размер премии. Формула содержит абсолютный адрес ячейки D1.
Порядок выполнения работы
Введите исходные данные и расчетные формулы (рис. 1).

Рис. 13 SEQ Рис. \* ARABIC 14115. Вид рабочего листа 1
Отформатируйте таблицу, используя процентный формат для столбца %участия и денежный формат для столбца Сумма.
Отсортируйте таблицу по полю ФИО. Для этого выделите столбец ФИО без заголовка, затем нажмите на верхней панели инструментов кнопку Сортировка по возрастанию, в открывшемся окне нажмите кнопку Сортировка.
Выделите столбец ФИО без заголовка и выполните команду Сервис – Параметры. На вкладке Списки нажмите кнопку Импорт, затем ОК.
Переименуйте рабочий лист (РЛ) в лист Январь.
Создайте копии листа Январь на листах 2 и 3, переименовав их соответственно в Февраль и Март. Поменяйте на этих листах размер премии, введя 20 000 и 18 000 соответственно.
Создайте лист Квартал, щелкнув правой кнопкой мыши по ярлычку последнего листа и выбрав из контекстного меню команду Добавить – Лист. Переименуйте вставленный лист и переместите лист Март (“зацепите” ярлычок и перетащите его влево, поместив за листом Февраль).

ФИО
Сумма
Январь
Сумма
Февраль
Сумма
Март
Сумма
Квартал













Введите заголовок ФИО и первую фамилию списка. Выделите первую фамилию и используя маркер автозаполнения, введите остальные фамилии.
Данные с листов Январь, Февраль, Март перенести на лист Квартал, используя формулы связывания листов. Например: =Январь!F4
Чтобы ввести формулу связывания выполните следующие действия:
введите знак равно;
щелкните по ярлычку листа, данные с которого необходимо использовать в формуле;
щелкните по ячейке, значение которой необходимо использовать в формуле.
Нажмите Enter.
Затем на листе Квартал растяните эту формулу, используя маркер автозаполнения.
Таблицу на листе Январь отформатируйте, используя условное форматирование. Выполните следующие действия:
выделите числовые значения столбца Сумма;
выполните команду Формат – Условное форматирование;
в диалоге команды выберите из списка вариант Больше или равно и введите число 2 000;
нажмите кнопку Формат и выберите зеленый цвет и курсивное начертание.
Нажмите Ok.
Таблицы на листах Февраль и Март отформатируйте, используя команду Автоформат меню Формат. Выберите разные форматы для листов Февраль и Март.
Таблицу листа Квартал отформатируйте, используя команду Формат – Ячейки.
Выполните команду Формат ячеек, выбрав последнюю из контекстного меню, открытого на выделенных ячейках с заголовками столбцов таблицы.
В окне команды:
на вкладке Выравнивание выберите вариант По центру для вертикального и горизонтального выравнивания и установите флажок Переносить по словам;
на вкладке Граница выберите цвет линии (красный), тип линии (двойная черта) и тип границ (внешние и внутренние).
Постройте круговую диаграмму “Доли участия сотрудников в выполнении работы” на листе Январь:
выделите столбец %участия с заголовком и вызовите мастер диаграмм;
на первом шаге мастера выберите Тип – круговая, Вид – объемная;
на втором шаге мастера на вкладке Ряд щелкните поле Подписи категорий и выделите в исходной таблице столбец ФИО без заголовка;
на третьем шаге мастера на вкладке Подписи данных установите флажок Доли;
на четвертом шаге мастера нажмите кнопку Готово.
Графически представьте изменение размера премии сотрудников по месяцам:
выделите три столбца, соответствующие суммам по месяцам, с заголовками и вызовите мастер диаграмм;
на первом шаге мастера выберите Тип – график, Вид – по умолчанию;
на втором шаге мастера проверьте диапазон и перейдите на вкладку Ряд; щелкните поле Подписи оси Х и выделите в исходной таблице столбец ФИО без заголовка;
на третьем шаге мастера на вкладке Заголовки введите название диаграммы Премия;
на четвертом шаге мастера установите переключатель На отдельном листе и нажмите кнопку Готово.
Отформатируйте диаграмму:
откройте контекстное меню на области построения и выберите команду Формат области построения. В диалоге команды выполните следующие действия:
нажмите кнопку Способы заливки;
установите переключатель Два цвета и выберите цвета;
поменяйте заливку области диаграммы, цвет и толщину линий графиков (команды Формат области диаграммы, Формат рядов данных из контекстного меню).
На листе Январь добавьте примечание к ячейке заголовка КТУ. Выполните команду Вставка – Примечание и введите текст из пункта 1 положений, используемых при заполнении таблицы формулами.

Часть 2.
Порядок выполнения работы
Введите исходные данные и расчетные формулы. Для ввода списка фамилий используйте автозаполнение (рис. 2).

Рис. 13 SEQ Рис. \* ARABIC 14215. Вид рабочего листа 1
Районный коэффициент = 15% * оклад
Дополнительные выплаты берутся из таблицы листа 2 (рис. 3) и проставляются в таблице листа 1 с помощью функции ПРОСМОТР. Выполните пункты 2 и3.
Присвойте двум диапазонам символьные имена с помощью команды Вставка – Имя – Присвоить:
выделите диапазон А1:A9 листа 2 и выполните команду;
введите в поле диалога текст ФИО;
аналогично присвойте имя ДопВыплаты диапазону B1:B9 листа 2.
Примечание. Применение в формулах символьных имен диапазонов обеспечивает автоматическое использование абсолютных ссылок.
Используйте кнопку Мастер функций для ввода функции ПРОСМОТР. Заполните диалог команды:
первый аргумент (искомое значение) – фамилия сотрудника из ячейки А2 листа 1;
второй аргумент (просматриваемый вектор) – диапазон А1:A8 листа 2 (абсолютная ссылка);
третий аргумент (вектор результата) – диапазон B1:B8 листа 2 (абсолютная ссылка).
Примечание: символьные имена ФИО и ДопВыплаты в аргументах функции Просмотр появляются автоматически после выделения мышью соответствующего диапазона ячеек: Просмотр (А2; ФИО; ДопВыплаты).


Рис. 13 SEQ Рис. \* ARABIC 14315. Вид рабочего листа 2 (первая таблица)
Перенесите данные столбца Сумма с листа Январь книги Расчет премии в столбец Премия книги Заработная плата. Используйте формулы связи.
Подоходный налог равен 13 % от общей суммы заработка, в которую входят оклад, районный коэффициент, дополнительные выплаты и премия.
Сумма к выдаче – это общая сумма заработка минус подоходный налог.
Поместите на листе 2 таблицу Задолженность по кредиту (рис. 4) и используйте ее для расчета вычетов в исходной таблице.


Рис. 13 SEQ Рис. \* ARABIC 14415. Вид рабочего листа 2 (вторая таблица)
Вставьте в исходной таблице на листе 1 перед столбцом Сумма к выдаче столбец Вычеты. Введите формулы, используя функции ЕСЛИ, И, ИЛИ, учитывая следующие условия:
если имеется задолженность по потребительскому и жилищному кредиту, удержать 20 % от суммы к выдаче;
если имеется задолженность по потребительскому либо жилищному кредиту, удержать 10 % от суммы к выдаче;
если задолженность отсутствует, оставить ячейку столбца Вычеты пустой.
Выведите фамилии сотрудников, получивших максимальную сумму (минимальную сумму), используя функции МАКС (МИН) и функцию ПРОСМОТР.
Выведите в диапазон I2:I8 листа 1 фамилии сотрудников, получивших премию выше общей средней. Используйте функцию СРЗНАЧ и функцию ЕСЛИ.
Отформатируйте таблицу, используя команду Формат – Ячейки.
Постройте гистограмму для просмотра значений выплаченных сумм сотрудникам:
выделите столбец Сумма к выдаче и вызовите Мастер диаграмм;
на первом шаге мастера выберите Тип – гистограмма, Вид – по умолчанию;
на втором шаге мастера проверьте диапазон и перейдите на вкладку Ряд; щелкните поле Подписи оси Х, выделите в исходной таблице столбец ФИО без заголовка и нажмите кнопку Готово.
Отредактируйте готовую диаграмму:
откройте контекстное меню на подписях оси Х и выберите команду Формат оси;
на вкладке Выравнивание в группе Ориентация установите 90о;
на вкладке Шрифт при необходимости измените размер шрифта;
добавьте в гистограмму новые ряды данных (Оклад и Дополнительные выплаты), выделив и перетащив их из исходной таблицы в область построения диаграммы;
удалите из диаграммы ряд Оклад, промаркировав его щелчком по любому элементу ряда и нажав клавишу Delete на клавиатуре.
Отформатируйте диаграмму – поменяйте фон области построения и области диаграммы, используя для заливки несколько цветов (два и более).
Представьте на листе формулы, по которым были выполнены вычисления. Для этого выполните команду Сервис – Параметры. На вкладке Вид в группе элементов Параметры Окна установите флажок Формулы.

Заголовок 115

Приложенные файлы

  • doc 15370556
    Размер файла: 84 kB Загрузок: 0

Добавить комментарий