Методика и порядок проведения работы. 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. Выполните команду Вставка – Функция или щелкните на пиктограмму f_x . В появившемся окне Мастер функций выберите Категория – Статистические, найдите функцию СРЗНАЧ и нажмите ОК.

3. Выделите мышью диапазон ячеек B4:B7. В поле Число1 окна Мастер функций появится нужный диапазон. Снова нажмите ОК.

4. Курсором мыши Черный крестик (+) произведите автозаполнение ячеек C9:G9, чтобы вычислить среднее значение нагрузки преподавателей за февраль – июнь.

Аналогично вычислите максимум и минимум часов по месяцам.

Задание . Логическая функция ЕСЛИ

Допустим, что почасовик должен отработать норму – 400 часов. Переработка оплачивается дополнительно. Заполните столбец Отработано сверх с помощью логической функции ЕСЛИ.

Замечание. Функция ЕСЛИ имеет три аргумента. Первый аргумент функции – заданное логическое условие. Если оно при вычислении дает значение ИСТИНА, то функция возвращает значение, равное второму аргументу; если заданное логическое условие при вычислении дает значение ЛОЖЬ, то функция возвращает значение, равное третьему аргументу.

1. Установите курсор на I4.

2. Щелкните на f_x .

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).

Рисунок 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. Как провести вычисления с помощью функции ОКРУГЛ (математическая); ЕСЛИ(логическая)?

• ⇐ Назад
• 12