Методика и порядок проведения работы. 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.

Сделайте Предварительный просмотр Методика и порядок проведения работы. 3.1 Задание 1.Сформировать таблицуВедомость (Лаб.раб.9) с использованием функций и выполните расчеты на листе Ведомость - student2.ru листов Список и Ведомость. Если Ведомость не поместилась на одной странице, измените поля книги и ориентацию листов на Альбомную, снова сделайте Предварительный просмотр.

Измените Норму часов на 450 и посмотрите изменения в столбцах Отработано сверх и Премия.

На листе Список введите дополнительные данные (рис. 4)

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

Методика и порядок проведения работы. 3.1 Задание 1.Сформировать таблицуВедомость (Лаб.раб.9) с использованием функций и выполните расчеты на листе Ведомость - student2.ru

Рисунок 4. Ставка.

Для этого:

1. Ячейкам G2, G3, G4 задайте соответственно имена Ставка_асс, Ставка_доц, Ставка_проф.

2. В ячейку D2 введите вложенные функции ЕСЛИ: =ЕСЛИ(C2<>"профессор";ЕСЛИ(C2<>"доцент";Ставка_асс;Ставка_доц);Ставка_проф)

Замечание. Значки < > означают не равно; >= означают больше или равно. Адреса и имена ячеек (C2, Ставка_асс и т. д.) не печатайте с клавиатуры, а 1ЛМ на соответствующую ячейку.

3. Переведите Зингеля в профессоры и посмотрите результат.

4. Измените ставку профессора в табличке Ставка – исходные данные и посмотрите изменения в столбце Ставка.

5. Закрасьте ячейки в диапазоне D2:D5 желтым цветом.

6. К ячейке D5 добавьте Примечание (Вставка – Примечание) и сделайте примечание видимым (Сервис – Параметры – Вид).

Замечание. Ячейка с примечанием имеет красный индикатор в правом верхнем углу.

7. Сравните полученный результат с образцом (см. рис. 5)

Методика и порядок проведения работы. 3.1 Задание 1.Сформировать таблицуВедомость (Лаб.раб.9) с использованием функций и выполните расчеты на листе Ведомость - student2.ru

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

Наши рекомендации