Практическое занятие №15
Тема: MS Excel.Статистическая обработка данных. Условная функция и логические выражения. MS Excel.
Цель работы: Использование возможностей MS EXCEL, как профессиональное средство для расчетов и представления необходимой информации
Время работы: 2 часа.
Оборудование: Компьютер с установленным программным обеспечением и подключенный к Internet
Ход работы
Краткие теоретические сведения:
Электронная таблица (ЭТ) – инструмент для табличных расчетов на ПК. Электронная таблица состоит из прямоугольных клеток – ячеек. Горизонтальные ряды
клеток образуют строки, а вертикальные ряды – столбцы. Иными словами, ячейка – это область, определяемая пересечением столбца и строки электронной таблицы. Строки имеют числовую нумерацию, а столбцы имеют буквенные обозначения (имена). Максимальное число строк, которое может иметь ЭТ – 65 536 и максимальное число столбцов – 256.
Для именования столбцов используются буквы латинского алфавита: A, B, C… . после
столбца с именем Z следуют столбцы: AA, AB, AC … AZ, BA, BB и т.д. в алфавитном порядке.
Для указания на конкретную ячейку таблицы используют адрес, составляемый из обозначения столбца и номера строки, на пересечении которых эта ячейка находится (например, A1, F8, C24, AA2 и т.д.).
Выполнение базовых арифметических операций
Помимо создания обычных таблиц, Excel можно использовать для выполнения в них арифметических операций, таких как: сложение, вычитание, умножение и деление.
Для выполнения расчетов в любой ячейке таблицы необходимо создать внутри нее простейшую формулу, которая всегда должна начинаться со знака равенства (=). Для указания математических операций внутри формулы используются обычные арифметические операторы:
Ввод простой формулы:
Поставьте знак арифметической операции (+, -, *, /).
Выделите ячейку со вторым значением.
Автосумма:
Для подсчета суммы для более двух слагаемы, как правило, используют автосумму. Для этого:
Выделите все ячейки, значения которых нужно суммировать, и ячейку, которой должен появиться результат.
нажмите на кнопку «Сумма»
, расположенная на вкладке «Главная» - в блоке «Редактирование».
Но на этом возможности кнопки Сумма не заканчиваются. Щелкните на стрелочку рядом с ней и откроется список, содержащий функции для вычисления средних значений (Среднее), количества введенных данных (Число), максимальных (Максимум) и минимальных (Минимум) значений.
Мастер функций:
Сначала необходимо выделить ячейку, в которой должен появиться результат вычислений. Для вызова мастера функций пользуется:
способ:
кнопка в строке формул:
Строка формул располагается над рабочим листом (над таблицей).
способ:
Вкладка «Формулы» – блок «Библиотека функций» – кнопка «Вставить функцию».
Работа с Мастером функций:
после вызова Мастера функции появляется диалоговое окно.
для того чтобы найти нужную функцию, необходимо в поле Категория указать нужную категорию (лучше всего использовать Полный алфавитный перечень).
Затем в поле Выберите функцию найдите нужную и нажмите кнопку ОК.
Появится диалоговое окно функции в поле Диапазон или Число1 (в зависимости от функции может использоваться то или иное поле) выделите все числа, среди которых нужно найти какое – то значение др.
Нажмите кнопку ОК.
Если функция с условием, например, нужно найти количество нулей, то в диалоговом окне функции СЧЕТЕСЛИ заполняется первая строка (Диапазон или Число1 выделенным диапазоном чисел), после заполняется дополнительная строка Критерий, в котором указывается условие подсчета (в нашем случаи =0).
Нажимаем кнопку ОК.
Задания для самостоятельной работы:
Задание 1. Создайте на Листе 1 таблицу учета товаров, пустые столбцы сосчитайте по формулам.
курс доллара | 31,80 | | | | | |
| | | | | | | |
| | Таблица учета проданного товаров | | |
| | | | | цена в | цена в | |
№ | название | поставлено | продано | осталось | рублях | долларах | всего в |
п\п | за 1 | за 1 | рублях |
| | | |
| | | | | товар | товар | |
1 | товар 1 | 50 | 43 | | 170 | | |
| | | | | | | |
2 | товар 2 | 65 | 65 | | 35 | | |
| | | | | | | |
3 | товар 3 | 50 | 43 | | 56 | | |
| | | | | | | |
4 | товар 4 | 43 | 32 | | 243 | | |
| | | | | | | |
5 | товар 5 | 72 | 37 | | 57 | | |
| | | | | | | |
| | | Всего | | | | |
Отформатируйте таблицу по образцу.
Переименуйте Лист 1 в Учет товара.
Задание 2.Составьте таблицу для выплаты заработной платы для работников предприятия на Листе 2.
| 1. Сосчитайте | по | формулам | пустые | столбцы. |
| | | | | | | | | | |
| | | Расчет заработной платы. | | | | | |
| | | | | | | | | | |
№ | | Фамилия, И.О. | Полученный | Налоговые | Налогообла | | | Сумма | К выплате | |
п/п | | доход | вычеты | гаемый | | | налога, | |
| | доход | | | НДФЛ | | |
| | | | | | | | |
| | | | | | | | | | |
1 | | Молотков А.П. | 18000 | 1400 | | | | | | |
2 | | Петров А.М. | 9000 | 1400 | | | | | | |
3 | | Валеева С. Х. | 7925 | 0 | | | | | | |
4 | | Гараев А.Н. | 40635 | 2800 | | | | | | |
5 | | Еремин Н.Н. | 39690 | 1400 | | | | | | |
6 | | Купцова Е.В. | 19015 | 2800 | | | | | | |
| | | Итого | | | | | | |
Налогооблагаемый доход = Полученный доход – Налоговые вычеты. Сумма налога = Налогооблагаемый доход*0,13.
2. Переименуйте Лист 2 в Расчет.
Задание 3.Создайте на Листе 3 таблицу оклада работников предприятия.
Оклад работников предприятия
статус | категория | оклад | премии |
начальник | 1 | 15 256,70р. | 5 000,00р. |
инженеры | 2 | 10 450,15р. | 4 000,00р. |
рабочие | 3 | 5 072,37р. | 3 000,00р. | |
Ниже создайте таблицу для вычисления заработной платы работников предприятия.
Заработная плата работников предприятия
№ | фамилия | категория | оклад | ежемесячны | подоходный | заработная | |
п/п | рабочего | рабочего | рабочего | е премии | налог (ПН) | плата (ЗП) | |
1 | Иванов | 3 | | | | | |
2 | Петров | 3 | | | | | |
3 | Сидоров | 2 | | | | | |
4 | Колобков | 3 | | | | | |
5 | Пентегова | 3 | | | | | |
6 | Алексеева | 3 | | | | | |
7 | | Королев | 2 | | | | |
8 | | Бурин | 2 | | | | |
9 | | Макеев | 1 | | | | |
10 | | Еремина | 3 | | | | |
| Итого | | | | | |
Оклад рабочего зависит от категории, используйте логическую функцию ЕСЛИ. Ежемесячная премия рассчитывается таким же образом. Подоходный налог считается по формуле:
ПН=(оклад+премия)*0,13. Заработная плата по формуле: ЗП=оклад+премия-ПН.
Отформатируйте таблицу по образцу.
Отсортируйте таблицу 2 в алфавитном порядке.
На предприятии произошли изменения, внесите данные изменения в таблицу:
ежемесячные премии в не зависимости от статуса и категории выплачиваются всем по 3000
рублей;
оклад рабочего вырос на 850 рублей;
Макеев вышел на пенсию;
Иванов поднялся по службе и стал инженером, Королев – начальником, а вот Бурина за нарушение дисциплины сократили до рабочего.
Найдите максимальную и минимальную зарплату сотрудников с помощью функции МИН(МАКС).
С помощью условного форматирования выделите ячейки красным цветом тех сотрудников, чья зарплата РАВНА МАКСИМАЛЬНОЙ.
Переименуйте Лист 3 в Зарплата.
Контрольные вопросы:
1.Как задается имя ячейки в электронных таблицах?
2.Каковы основные типы данных в электронных таблицах?