Методика и порядок проведения работы. 3.1 Задание 1.Сформировать таблицуВедомость (Лаб.раб.9) с использованием функций и выполните расчеты на листе Ведомость
3.1 Задание 1.Сформировать таблицуВедомость (Лаб.раб.9) с использованием функций и выполните расчеты на листе Ведомость.
Задание. Вычисления по формулам. Автосумма. Выполните расчеты на листе Ведомость:
1. Выделите ячейки B4:G4 и нажмите на пиктограмму Σ (Автосумма) на панели инструментов. В ячейке H4 появится результат формулы. Установите курсор на ячейку с результатом. В строке формул вверху рабочей области окна должна быть видна формула =СУММ(B4:G4), по которой производился расчет.
2. Курсором черный крестик в правом нижнем углу ячейки Н4 произведите автозаполнение формулами ячеек H5:H7.
3. Аналогично заполните строку Итого.
4. В ячейку К4 столбца Заработано введите формулу =H4*J4 (Заработано =Всего часов*Стоимость часа).
Замечание 1. При вводе адресов ячеек в формулу не пользуйтесь клавиатурой, а щелкните левой клавишей мыши на нужную ячейку. Замечание 2. Появление символов ####### в ячейке означает, что результат формулы не помещается в ячейку и необходимо увеличить ширину столбца (2ЛМ на границе между буквенными обозначениями столбцов).
5. Произведите автозаполнение формулами ячеек K5:K7.
6. Введите в ячейку M4 столбца Премия за переработку формулу = I4*J4 для оплаты сверхурочных часов (Премия за переработку =Отработано сверх*Стоимость часа) и черным крестиком заполните формулами ячейки M5:M7.
7. В ячейку N4 столбца К выдаче введите формулу=K4-L4+M4( К выдаче =Заработано-Налоги+Премия за переработку).
8. Курсором черный крестик произведите автозаполнение формулами ячеек N5:N7.
9. Введите данные в ячейки J4:J7 столбца Стоимость часа, вставьте их из листа Список, для этого:
– установите курсор на J4 и нажмите на клавишу =,
– щелкните левой клавишей мыши по листу Список,
– щелкните на листе Список по ячейке D2,
– завершите ввод формулы, щелкнув левой клавишей мыши на галочку в строке формул. В ячейке J4 появится формула = Список!D2 ,
– произведите автозаполнение формулами ячеек J5:J7.
Задание Относительные, абсолютные ссылки. Имена ячеек. Прочитайте следующее пояснение:
Относительные адреса (в формулах – ссылки), например A1, B2, C3, изменяются при копировании и перемещении формул. Абсолютные адреса, например $A$1, $D$12, не изменяются при копировании и перемещении формул. Если ячейке присвоено имя, то при копировании и перемещении формул имя не изменяется. Сделать адрес абсолютным можно тремя способами: 1) установить курсор в строке формул на адрес ячейки и нажать функциональную клавишу F4 – появятся значки $; 2) впечатать значки $ с клавиатуры; 3) дать ячейке имя по команде Вставка – Имя – Присвоить.
Выполните следующие действия:
1. Задайте имена ячейкам B13 и B14: – установите курсор на ячейку B13,
– щелкните мышью на адрес ячейки в поле Имя (см. рис. 1, Лаб. Раб 8), – введите имя Подоходный, – нажмите Enter, – аналогично задайте имя Норма ячейке B14.
Замечание. Имя ячейки не должно содержать пробелов, ввод имени завершается нажатием на клавишу Enter.
2. Введите формулу в ячейки L4:L7 столбца Налоги: – установите курсор на ячейку L4, – наберите знак =, – щелкните мышью на ячейку K4, – наберите знак *, – щелкните мышью на ячейку B13, – завершите ввод формулы, щелкнув левой клавишей мыши на галочку в строке формул, в ячейке L4 появится формула =K4*Подоходный, – произведите автозаполнение ячеек L5:L7.
3. Присвойте диапазону ячеек K4:K7 имя Заработано_всего: – выделите диапазон ячеек K4:K7, – в поле Имя в строке формул напечатайте Заработано_всего, – нажмите Enter. – В ячейке K8 подсчитайте сумму:– установите курсор на ячейку K8, – нажмите на панели инструментов на пиктограмму Σ (Автосумма), – завершите ввод формулы, щелкнув левой клавишей мыши на галочку в строке формул, появится формула = СУММ (Заработано_всего).
Задание . Статистические функции СРЗНАЧ, МАКС, МИН Вычислите среднее значение часов, отработанных в январе. Для этого:
1. Щелкните мышкой по ячейке B9.
2. Выполните команду Вставка – Функция или щелкните на пиктограмму fx . В появившемся окне Мастер функций выберите Категория – Статистические, найдите функцию СРЗНАЧ и нажмите ОК.
3. Выделите мышью диапазон ячеек B4:B7. В поле Число1 окна Мастер функций появится нужный диапазон. Снова нажмите ОК.
4. Курсором мыши Черный крестик (+) произведите автозаполнение ячеек C9:G9, чтобы вычислить среднее значение нагрузки преподавателей за февраль – июнь.
Аналогично вычислите максимум и минимум часов по месяцам.
Задание . Логическая функция ЕСЛИ
Допустим, что почасовик должен отработать норму – 400 часов. Переработка оплачивается дополнительно. Заполните столбец Отработано сверх с помощью логической функции ЕСЛИ.
Замечание. Функция ЕСЛИ имеет три аргумента. Первый аргумент функции – заданное логическое условие. Если оно при вычислении дает значение ИСТИНА, то функция возвращает значение, равное второму аргументу; если заданное логическое условие при вычислении дает значение ЛОЖЬ, то функция возвращает значение, равное третьему аргументу.
1. Установите курсор на I4.
2. Щелкните на fx .
3. В появившемся окне Мастер функций выберите Категория – Логические, найдите функцию ЕСЛИ и нажмите ОК.
4. В поле Логическое выражение окна Мастер функций введите H4>=Норма (щелкайте мышью на нужные ячейки).
5. В поле Значение_если_истина введите H4–Норма.
6. В поле Значение_если_ложь введите цифру 0.
7. Нажмите OK, в строке формул в ячейке I4 увидите формулу =ЕСЛИ(H4>=Норма;H4–Норма;0).
8. Произведите автозаполнение формулами ячеек I5:I7.
Сделайте Предварительный просмотр 
листов Список и Ведомость. Если Ведомость не поместилась на одной странице, измените поля книги и ориентацию листов на Альбомную, снова сделайте Предварительный просмотр.
Измените Норму часов на 450 и посмотрите изменения в столбцах Отработано сверх и Премия.
На листе Список введите дополнительные данные (рис. 4)
В первую ячейку в столбце Ставка введите функцию, которая будет выдавать размер ставки в зависимости от должности.
Рисунок 4. Ставка.
Для этого:
1. Ячейкам G2, G3, G4 задайте соответственно имена Ставка_асс, Ставка_доц, Ставка_проф.
2. В ячейку D2 введите вложенные функции ЕСЛИ: =ЕСЛИ(C2<>"профессор";ЕСЛИ(C2<>"доцент";Ставка_асс;Ставка_доц);Ставка_проф)
Замечание. Значки < > означают не равно; >= означают больше или равно. Адреса и имена ячеек (C2, Ставка_асс и т. д.) не печатайте с клавиатуры, а 1ЛМ на соответствующую ячейку.
3. Переведите Зингеля в профессоры и посмотрите результат.
4. Измените ставку профессора в табличке Ставка – исходные данные и посмотрите изменения в столбце Ставка.
5. Закрасьте ячейки в диапазоне D2:D5 желтым цветом.
6. К ячейке D5 добавьте Примечание (Вставка – Примечание) и сделайте примечание видимым (Сервис – Параметры – Вид).
Замечание. Ячейка с примечанием имеет красный индикатор в правом верхнем углу.
7. Сравните полученный результат с образцом (см. рис. 5)
Рисунок 5. Автозаполнение таблицы.
3.2 Задание 1.Сформировать таблицу ОЦЕНКА КАЧЕСТВА ТОВАРОВ с использованием функций: ОКРУГЛ(математическая); ЕСЛИ(логическая); МИН, МАКС, СРЗНАЧ (статистические).
3.2.1 Рекомендации по выполнению:
1. Откройте программу MS Excel.
2. Создайте таблицу и заполните её исходными данными (рис. 6).
| A | B | C | D | E | F | G | H | |
| Оценка качества товара | ||||||||
| № | Наименование товара | Выдержка | Цвет | Запах | Стоимость | Средний бал | Градуиро-ванная оценка | |
| Коньяк "Юбилейный" | ||||||||
| Коньяк "Виктория" | ||||||||
| Коньяк "Дачиа" | ||||||||
| Коньяк "Солнечный" | ||||||||
| Коньяк "Сюрпризный" | ||||||||
| Коньяк "Молдова" | ||||||||
| Коньяк "Белый аист" | ||||||||
| Низшая оценка | ||||||||
| Средняя оценка | ||||||||
| Высшая оценка |
Рисунок 6 Таблица ОЦЕНКА КАЧЕСТВА ТОВАРОВ
3. В первую ячейку столбца Средний бал введите формулу =ОКРУГЛ(СРЗНАЧ(C3:F3);0). С помощью метода перетаскивания размножьте формулу по столбцу (G3:G9).
4. В первую ячейку столбца Градуированная оценка введите формулу
=ЕСЛИ(G3<60;1;ЕСЛИ(G3<70;2;ЕСЛИ(G3<85;3;ЕСЛИ(G3<94;4;5)))). С помощью метода перетаскивания размножьте формулу по столбцу (Н3:Н9);
5. В первую ячейку строки Низшая оценка введите формулу: =ОКРУГЛ(МИН(C3:C9);0) и размножьте по строке в диапазоне D10:Н10.
6. В первую ячейку строки Средняя оценка введите формулу: =ОКРУГЛ(CРЗНАЧ(C3:C9);0) и размножьте по строке в диапазоне D11:Н11.
7. В первую ячейку строки Высшая оценка введите формулу: =ОКРУГЛ(МАКС(C3:C9);0) и размножьте по строке в диапазоне D12:Н12.
8. В результате получиться таблица изображенная на рисунке 7.
9. Сохраните таблицу.
| Оценка качества товара | |||||||
| № | Наименование товара | Выдержка | Цвет | Запах | Стоимость | Средний бал | Градуированная оценка |
| Коньяк "Юбилейный" | |||||||
| Коньяк "Виктория" | |||||||
| Коньяк "Дачиа" | |||||||
| Коньяк "Солнечный" | |||||||
| Коньяк "Сюрпризный" | |||||||
| Коньяк "Молдова" | |||||||
| Коньяк "Белый аист" | |||||||
| Низшая оценка | |||||||
| Средняя оценка | |||||||
| Высшая оценка |
Рис. 7. Результирующая таблица ОЦЕНКА КАЧЕСТВА ТОВАРОВ
4 Аппаратура и материалы:IBM PC, табличный процессор MS Excel.
5. Содержание отчета и его форма: Форма отчёта письменная. Тема, цель лабораторной работы Описание выполнения работы. Продемонстрировать электронный вариант таблиц. Отчет предоставить в тетради или в виде распечатки.
6 Контрольные вопросы:
1. Способы ввода функции и категории функций в Excel.
2. Назначение и способы запуска инструмента Мастер функций.
3. Опишите создание вложенной функции.
4. Абсолютные и относительные ссылки на ячейки.
5. Перечислите числовые форматы ячеек и способы их установки.
6. Значение ошибки на рабочем листе. Причины и способы устранения.
7. Как задать имена ячеек?
8. Как провести вычисления с помощью статистические функции СРЗНАЧ, МАКС, МИН?
9. Как провести вычисления с помощью функции ОКРУГЛ (математическая); ЕСЛИ(логическая)?