ПЗ-08

ФЕДЕРАЛЬНОЕ АГЕНТСТВО ЖЕЛЕЗНОДОРОЖНОГО ТРАНСПОРТА Ярославский филиал федерального государственного бюджетного образовательного учреждения высшего профессионального образования «Московский государственный университет путей сообщения»
РАССМОТРЕНО Цикловой комиссией «Экономики и управления» Протокол № __ от __.__.201_ г.	ИНСТРУКЦИОННАЯ КАРТА Практическая работа № 08 Учебная дисциплина МДК.01.03.«Организация секретарского обслуживания» 034702 «Документационное обеспечение управления и архивоведение»	УТВЕРЖДАЮ Заместитель директора филиала по учебно-методической работе _________ М.К. Лебедев «__» ___________ 201__ г.
Тема: «Использование формул и функций для статистических расчетов в таблицах»
Цель выполнения работы: совершенствование знаний и умений при работе Microsoft Excel
Перед началом выполнения работы необходимо знать:
интерфейс Microsoft Excel; основные правила работы с формулами; правила работы с мастером функций; основные функции Microsoft Excel
После окончания выполнения работы необходимо уметь:
вводить формулы; выполнять расчёты по формулам с использованием электронных таблиц; выполнять расчеты в таблицах с помощью функций
Оборудование (приборы, материалы, дидактическое обеспечение): ПК Н.Макарова, Г. Николайчук, Ю. Титова. Компьютерное делопроизводство: учебный курс, Питер, 2005 г

Теория

Перед выполнением заданий практического занятия необходимо вспомнить правила работы с формулами и функциями в Microsoft Excel

Основные правила работы с формулами:

• формула начинается знаком равенства;

• формулы записываются в строку;

• порядок действий такой же, как при вычислении математических выражений;

• основные операции:

-	Унитарный минус (например, –1)
^	Возведение в степень
* и /	Умножение и деление
+ и-	Сложение и вычитание

• в формулах на месте переменных используются адреса тех ячеек, где хранятся данные для подстановки.

Мастер функций.

Чтобы вызвать “Мастера функций” нужно щёлкнуть по специальной кнопке. Появится окно “Мастер функций – шаг 1 из 2”. Во втором окне выбираем категория – список групп функций. В третьем окне перечисляются названия всех функций, входящих в выделенную категорию. Если вы не знаете, к какой категории относится функция, то обратитесь к пункту “Полный алфавитный перечень”.

Логические функции. Выражение называется логическим, если в нем имеется знак отношения. Отношения бывают простые (если используются операции: > - больше, < - меньше, >= - больше или равно, <= - меньше или равно, = - равно, <> - не равно) или составные, если состоят из нескольких простых, соединённых служебным словом.

Логические функции могут принимать в качестве аргументов только два значения: ИСТИНА и ЛОЖЬ.

• ЕСЛИ(логическое_выражение, значение_если_истина, значение_если_ложь). Функция работает следующим образом: проверяется условие, в зависимости от истинности условия, выполняется соответствующее значение. Поместим в ячейку В7 число 78. Введем в ячейку В8 формулу: =ЕСЛИ (В7>60, “Превышено итоговое значение”, В7).

• И(х, у,..). На месте аргументов должны стоять логические выражения. Чаще всего эта функция используется для составных условий. Функция принимает значение ИСТИНА, если истинны все логические выражения. Иначе – ЛОЖЬ.

• ИЛИ(х, у,..). На месте аргументов должны стоять логические выражения. Чаще всего эта функция используется для составных условий. Функция принимает значение ИСТИНА, если истинно хотя бы одно логическое выражение. Иначе – ЛОЖЬ.

• НЕ(х). Данная функция может иметь лишь один аргумент – логическое выражение. Функция принимает значение ИСТИНА, если ложно логическое выражение. Иначе – ЛОЖЬ.

Итоговые функции. Аргументами таких функций служит блок или несколько блоков. Значение всегда единственное.

• СУММ(х, у,..). Допускается до 30 аргументов. Аргументом может служить блок. Для этой функции существует специальная кнопка на панели “Стандартная”. Если в блоке в какой-нибудь ячейке содержится текстовое значение, то оно считается равным нулю. Введите в ячейку А3 число –60. Заполните арифметической прогрессией блок А3:К4 с шагом 5. Сосчитать сумму всех чисел в ячейке А5. Для этого поставьте курсор в ячейку А5, щелкните по кнопке суммы, исправьте предложенную формулу, указав блок А3:К4.

• МАКС(х, у,..) – вычисляет максимальное значение среди указанных аргументов. Аргументов может быть до 30 (числа, ссылки на ячейки, блоки). Текстовые и логические аргументы игнорируются. Если в конце названия функции стоит буква А – МАКСА, то выбирают максимальное среди всех значений, в том числе и числовых, логических. Текстовая строка имеет нулевое значение, если в диапазон входит ИСТИНА, то значение эквивалентно 1.

• МИН(х, у,..) – то же, что и МАКС, но находит минимальное значение. Существует и функция МИНА (см. МАКСА).

• СРЗНАЧ(х, у,..) – вычисляет среднее арифметическое аргументов. Работает как и две предыдущие.

• СЧЕТЕСЛИ(интервал, критерий). Подсчитывает в интервале (блоке) количество значений, удовлетворяющих критерию. Критерий не должен быть сложным. Введите в ячейки А10:К10 любые числа (положительные и отрицательные). Введём в ячейку А11 формулу: =СЧЕТЕСЛИ(А10:К10, “>0”).

• СУММЕСЛИ(интервал, критерий, сумм_интервал). Значения, удовлетворяющие критерию, выбираются из блока, заданного первым аргументом, суммируются соответствующие значения из сумм_интервал, заданного третьим аргументом. Если третий аргумент опущен, то суммируются ячейки в аргументе интервал. Эта функция позволяет решать более сложные задачи. Пример. Составим таблицу. Введём в ячейки А1:А4 значения: рост, вес, год рождения, количество баллов; в ячейки В1:Е1 числа: 173, 156, 172,166; в ячейки В2:Е2 числа: 70, 60, 77, 61; в ячейки В3:Е3 числа: 1988, 1990, 1986, 1993; в ячейки В4:Е4 числа: 4, 5, 2, 3. Сосчитаем, какое количество баллов набрали спортсмены, вес которых меньше 66 кг. Для этого в ячейке А5 запишем формулу: =СУММЕСЛИ(В2:Е2,”<66”,B4:E4).

• СЧЕТ(х, у,..) – подсчитывает количество числовых значений. Аргументы – числа, ссылки на ячейки, блоки. Может быть до 30 аргументов. СЧЕТЗ – подсчитывает количество всех значений, не различая числовых и текстовых.

• НАИБОЛЬШИЙ(блок, k). Эта функция находит наибольшее по величине значение. Число k обозначает, какое по величине наибольшее нужно найти. (Например, в блоке числа: 6;7;12;98;45;8;12. Если k=1, то результат равен 98. Если k=2, то результат равен 45. Если k=5, то результат равен 8. Так как в блоке есть равные числа, то при k=3 и k=4 результат один и тот же 12.)

• НАИМЕНЬШИЙ(блок, k). Эта функция находит наименьшее по величине значение. Работает так же, как и предыдущая функция.

Порядок выполнения

1. Выполнить на ПК следующее задание:

Задание 1

Создайте таблицу. Заполните ее учитывая следующие моменты:

1. "Начислено" вычисляется, как произведение "Отработано дней" и "Ставка в день"

2. Подоходный налог взимается из расчета 13% от "Начислено".

3. Взнос в Пенсионный фонд - как 1% от "Начислено".

4. "На руки" вычисляется , как "Начислено" - "Под.налог" и "Пенс.фонд".

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

фамилии сотрудников	Отработано дней	Ставка в день	Начислено (в руб)	Под.налог	Пенс.фонд	На руки
Иванов	22	120 000р.
Смирнов	23	100 000р.
Сергеев	25	97 000р.
Васильев	27	124 000р.
Семенов	22	132 000р.
Войнов	21	150 000р.
Миронов	20	86 000р.
Токарев	28	76 000р.
Шемуранов	30	100 000р.
Столяров	26	98 000р.

Задание 2

Создайте таблицу по образцу.

В данной таблице следует начислить налог из расчета 12% от "Начислено", если сумма не превышает 1700 рублей и 20% в противном случае. Используйте функцию ЕСЛИ, вычислите ИТОГО - данные для кассира.

Задание 3

Создать таблицу динамики розничных цен и произвести расчёт средних значений.

1. Переименуёте ярлычок Лист 1, присвоив ему имя «Динамика цен».

2. На листе «Динамика цен» создайте таблицу по образцу.

3. Произведите расчёт изменения цены в колонке «E» по формуле Изменение цены = Цена на 01.06.2005/Цена на 01.04.2005. Задайте процентный формат чисел в колонке «E».

4. Рассчитайте средние значения по колонкам, пользуясь мастером функций. Для расчёта функции среднего значения установите курсор в соответствующей ячейке для расчёта среднего значения (B14), запустите мастер функций (Кнопкой Вставка функции или командой Вставка/Функция) и на первом шаге мастера выберите функцию СРЗНАЧ (категория Статистические/СРЗНАЧ). После нажатия на кнопку OK откроется окно для выбора диапазона данных для вычисления заданной функции. В качестве первого числа выделите группу ячеек с данными для расчёта среднего значения B6:B13 и нажмите кнопку OK. В ячейке B14 появится среднее значение данных колонки «B». Аналогично рассчитайте среднее значение в других колонках.

5. В ячейке A2 задайте функцию СЕГОДНЯ, отображающую текущую дату, установленную в компьютере (Вставка/Функция/Дата и Время/Сегодня).

2. Выполнить на ПК контрольное задание:

Контрольное задание

Создать таблицу «Расчёт заработной платы».