ТЕМА: Составление формул с относительными и абсолютными адресами. Форматирование данные.

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

Пусть по каждому рабочему известны следующие данные: табельный номер, ФИО, размер заработной платы, норма выработки, фактическая ежедневная выработка (шт.), количество брака (шт.). Пример таблицы 3.1.

Определить за указанные даты:

· фактическую общую и среднюю выработки для каждого рабочего;

· процент брака для каждого рабочего;

· общий выпуск и брак за каждую дату;

· общий выпуск за указанные даты;

· минимальный, максимальный и общий процент брака;

· фамилии рабочих, допустивших минимальный процент брака;

· начислить рабочим, допустившим минимальный процент брака премию в размере указанного процента от заработной платы;

· начислить сумму, необходимую для выплаты с учетом заработной платы и премии;

· построить гистограмму соотношения выработки и брака для каждого рабочего;

· построить график выработки каждого рабочего.

Таблица  3.1.

Учет выработки рабочих с даты 1 по дату 5

Задание 3.1. Заполнение предложенной таблицы.

1. Заполните 1 строку заголовка таблицы:

1.1. Табельный №, Фамилия И.О., Оплата, Норма;

1.2. Заголовки дат, в ячейку Дата 1 введите текущую дату, объедините со следующей ячейкой, используя Формат ячеек, сделайте выравнивание по Центру по горизонтали и вертикали;

1.3. Используя Автозаполнение введите конкретные даты, (дат должно быть пять);

1.3.1. Наберите заголовок следующей ячейки Итого единиц, объедините со следующей ячейкой, используя Формат ячеек: сделайте выравнивание по Центру по горизонтали и вертикали; выполните Перенос по словам;

1.4. Заголовок Среднее по факту, объединить с нижней ячейкой;

1.5. Заголовок Процент брака, объединить с нижней ячейкой.

2. Заполните 2 строку таблицы:

2.1. Объедините ячейки столбцов 2 строки с ячейками с именами Табельный №, Фамилия И.О., Оплата, Норма, используя Формат ячеек;

2.2. Каждая дата делится на Факт и Брак;

2.3. Под ячейкой с именем Итого единиц, также делятся на Факт и Брак;

3. После заполнения заголовка таблицы, заполнить своими данными таблицу, первая фамилия в столбце должна быть ваша.

4. Выполнить расчеты в столбцах, написав формулу расчета для первого рабочего, копируем для всех рабочих:

4.1. Брак = Норма – Факт, столбец с заголовком Норма в формуле, указать как абсолютный адрес столбца ($D3-E3);

4.2. C копировать  формулу для вычисления столбца Брак для следующих четырех дат;

4.3. Итого единиц Факт = сумма значений по каждой колонке (факт), используя клавишу Ctrl, можно выделить ячейки со значением Факт в каждой дате;

4.4.  Среднее по факту = произвести расчет средних значений по столбцам с использованием функции СРЗНАЧ;

4.5. Процент брака = Итоговый брак делится на Итоговый факт, затем ячейке присвоить Процентный формат.

5. Всего выпущено найти самостоятельно.

6. Миним.% брака = Минимальное значение среди значений Процент брака, используя функцию МИН;

7. Максим.%брака= Максимальное значение среди значений Процент брака, используя функцию МАКС;

7.1. Общий.%брака = = произвести расчет средних значений по столбцам с использованием функции СРЗНАЧ.

8. Переименуйте Лист1 в Учет.

9. Выполните сохранение файла (Файл/Сохранить Как) в папку Мой Excel c именем Лаб3.

Лабораторная работа 4

Тема: Работа с несколькими листами

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

Задание 4.1. Создание таблицы с использованием копирования на свободный лист.

1. На свободном листе (Лист2) электронной книги «Лаб3» создайте таблицу для начисления оплаты, переименуйтеЛист2 в Оплата, пример таблицы приведен в таблице 4.1.

Таблица 4.1.

1. Скопируйте необходимые данные с листа Учет.

перейдите на лист Учет

выделите необходимые данные

выполните команду Правка – Копировать

перейдите на лист Оплата используйте Специальную вставку / Вставить связь.

Рекомендации: Для обеспечения связи с данными одного Листа с данными на другом необходимо использовать Специальную вставку. Сначала копируем необходимый фрагмент данных Г лавная/Копировать затем используем Специальную Вставку/Вставить связь (что обеспечивает связь с данными одного Листа с другим Листом).

2. Установите процент премии самостоятельно и укажите Процентный формат ячейки.

3. Для расчета Премии установите курсор в ячейку начисления Премии первого рабочего запустите мастер функций (кнопкой Вставка функции f_x или командой Вставка/ Функция) и выберите функцию,  (категория — Логические/ ЕСЛИ).

Рис. 4.1.

 

3..1. Задайте условие и параметры функции ЕСЛИ как показано на рис. 4.1.

В первой строке «Логическое выражение» задайте условие:

Процент брака рабочего = значению абсолютного адреса ячейки Миним. % брака;

Во второй строке задайте формулу расчета премии, если условие выполняется

Размер оплаты * Процент премии;

В третьей строке задайте значение 0, поскольку в этом случае (невыполнение условия) премия не начисляется.

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

4. Произвести расчет Сумма премии на листе Оплата.

5. Произведите расчет Итоговые начисления начислить итоговую оплату.

6. Выполните сохранение файла (Файл/Сохранить Как) в папку Мой Excel c именем Лаб4.

7. Открыть электронную книгу с именем Лаб4, добавить столбец с заголовком Фамилия рабочего, получившего премию.

8. Вывести самостоятельно ФИО рабочего, имеющего наименьший % брака по результатам учета выработки листа Учет.

9. Построить гистограммы исходных данных Учет выработки рабочих с даты 1 по дату 5 и круговые диаграммы для листов Учет и Оплата.

10. Выполните текущее сохранение файла (Файл/Сохранить Как) в папку Мой Excel c именем Лаб4.

Рис.4.2.

Лабораторная работа 5