Лабораторная работа 11
Тема: Расчетные операции в MS Excel. Работа с функциями. Статистические функции.
Цель: Научиться производить расчеты в электронных таблицах с использованием статистических функций.
Теоретические сведения
РАСЧЕТНЫЕ ОПЕРАЦИИ, РАБОТА С ФУНКЦИЯМИ. СТАТИСТИЧЕСКИЕ ФУНКЦИИ.
Для проведения статистическое обработки информации табличный процессор MS Excel включает в себя программную надстройку «Пакет анализа» и библиотеку из 78 статистических функций, которые позволяют произвести полный и качественный статистический анализ информации.
Для использования статистических функций требуется на рабочем листе в отдельных ячейках подготовить значения основных аргументов функции и вызвать Мастер функции лента Формулы пункт Вставить Функцию fx. Категория статистические.
Для того, чтобы использовать в работе надстройки Пакет анализа, необходимо воспользоваться лента Данные пункт Анализ данных.
Таблица наиболее используемых статистических функций
| № | Функция | Назначение(Операции) | Синтаксис | Пример |
| МАКС() | Максимум из указанного диапазона | МАКС(число1;число2;...) | =МАКС(А3:А7) | |
| МИН() | Минимум из указанного диапазона | МИН(число1;число2;...) | =МИН(A3:A7) | |
| СРЗНАЧ() | Вычисление среднего арифметического значения для ячеек из указанного диапазона | СРЗНАЧ(число1; число2;...) | =СРЗНАЧ(B7:B10) | |
| СУММ() | Суммирование значений из ячеек указанного диапазона | СУММ(число1;число2;...) | =СУММ(A1:E1) | |
| СЧЁТ() | Подсчёт количества числовых значений в указанном диапазоне ячеек | СЧЁТ(значение1; значение2;) | =СЧЁТ(E2:E12) | |
| ОСТАТ() | Возвращает остаток от деления аргумента (число) на делитель | ОСТАТ(число; делитель) | =ОСТАТ(А1; 3) | |
| СУММЕСЛИ() | Суммирует ячейки, заданные критерием | СУММЕСЛИ(диапазон;критерий; диапазон_ суммирования) | =СУММЕСЛИ(A1:A9;>5;B1:B9) | |
| СЧЁТЕСЛИ() | Подсчет количества ячеек в указанном диапазоне, удовлетворяющих заданному условию | СЧЁТЕСЛИ(диапазон; критерий) | =СЧЁТЕСЛИ(А1:А10;"ВЫПОЛНЕНО") | |
| ЕСЛИ() | Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ | ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) | =ЕСЛИ(x>0;5;7) |
Ÿ СРЗНАЧ(зн1, зн2, …, знN) – возвращает среднее арифметическое значение диапазона ячеек. Если в диапазоне находятся пустые ячейки или ячейки, содержащие текст, то они игнорируются. Можно использовать любое число аргументов.
Ÿ СРЗНАЧА(значение1,значение2,...) – вычисляет среднее арифметическое значений аргументов, которые, помимо чисел, могут быть текстом или логическими значениями, такими как ИСТИНА и ЛОЖЬ. Массивы и ссылки, содержащие текст, а также пустой текст (" "), интерпретируются как 0 (ноль). Аргументы, содержащие значение ИСТИНА, интерпретируются как 1, аргументы, содержащие значение ЛОЖЬ, интерпретируются как 0 (ноль).
Пример:
В2:В5 ® {428; 356; 280; 250} – сдельный тарифный заработок, тыс. руб.
С2:С5 ® {165; 160; 170; -} – количество отработанных часов.
Найти средний тарифный заработок по бригаде и среднее количество часов, отработанных каждым членом бригады.
Среднее значение сдельного тарифного заработка:
=СРЗНАЧ(B2:B5)Þ325,5.
Так как столбец Количество отработанных часов содержит текстовое значение «-», то для вычисления среднего значения воспользуемся функцией СРЗНАЧА:
=СРЗНАЧА(C2:C5)Þ123,75.
Ÿ МЕДИАНА(число1;число2;...) – возвращает медиану заданных чисел, т.е. число, которое является серединой множества чисел. Иначе говоря, половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана.
Ÿ МОДА(число1;число2;...) – возвращает наиболее часто встречающееся или повторяющееся значение в массиве или интервале данных. Мода – это элемент с наибольшим числом повторений в выборке.
Ÿ СРГЕОМ(число1;число2;...) – возвращает среднее геометрическое значений массива или интервала положительных чисел.
Ÿ СЧЕТЕСЛИ (интервал, критерий) - количество удовлетворяющих заданному критерию ячеек внутри интервала.
Критерий может задаваться в виде числа, выражения или текста.
Например:
СЧЕТЕСЛИ(А1:А5,”>100”) – возвратит число ячеек, содержимое которых будет более 100.
Ÿ СЧЕТ(зн1, зн2, …, знN) – возвращает количество чисел в списке аргументов
Значение1, значение2,... - это от 1 до 30 аргументов, которые могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа. Учитываются аргументы, которые являются числами, пустыми значениями, логическими значениями, датами, или текстами, изображающими числа. Аргументы, которые являются значениями ошибки или текстами, которые нельзя интерпретировать как числа, игнорируются.
Пример:
| А | |
| Продажи | |
| 21.08.09 | |
| 14.04 | |
| ИСТИНА | |
| #ДЕЛ!/0 |
СЧЁТ(A1:A7) возвращает 3
СЧЁТ(A4:A7) возвращает 2
СЧЁТ(A1:A7, 2) возвращает 4
Ÿ СЧЕТЗ(зн1, зн2, …, знN) – Подсчитывает количество непустых значений в списке аргументов. Функция СЧЁТЗ используется для подсчета количества ячеек с данными в интервале или массиве.
Значение1, значение2,... - это от 1 до 30 аргументов, количество которых требуется сосчитать. В данном случае значением считается значение любого типа, включая пустую строку (""), но не включая пустые ячейки. Если аргументом является массив или ссылка, то пустые ячейки в массиве или ссылке игнорируются.
Пример:
| А | |
| Продажи | |
| 21.08.09 | |
| 14.04 | |
| ИСТИНА | |
| #ДЕЛ!/0 |
СЧЕТЗ (A1:A7) возвращает 6
СЧЕТЗ (A4:A7) возвращает 4
СЧЕТЗ (A1:A7, 2) возвращает 7
СЧЕТЗ (A1:A7, "Два") возвращает 7
Ÿ МАКС(зн1, зн2, …, знN) и МИН(зн1, зн2, …, знN) – используют для определения наибольшего и наименьшего значений в диапазоне. Можно задавать аргументы, которые являются числами, пустыми ячейками, логическими значениями или текстовыми представлениями чисел. Аргументы, которые являются значениями ошибки или текстами, не преобразуемыми в числа, вызывают значения ошибок.
Ÿ MAКСA(значение1;значение2,...) и МИНА(значение1;значение2,...) – возвращает наибольшее и наименьшее значение в списке аргументов, причем, наряду с числовыми значениями, выполняется также сравнение текстовых и логических значений. Пустые ячейки, логические значения или тексты в массиве или ссылке не игнорируются.
Ÿ НАИБОЛЬШИЙ (массив; k)и НАИМЕНЬШИЙ (массив; k) - возвращает k-ое наибольшее значение из множества данных. Эта функция используется, чтобы выбрать значение по его относительному местоположению. Например, функцию НАИБОЛЬШИЙ можно использовать, чтобы определить наилучший, второй или третий результат в баллах, показанный при тестировании.
Индивидуальные задания
Краткая справка. Переключение режимов просмотра формул и просмотра значений формул на листе - Нажмите CTRL + ` (апостроф). Или на ленте - вкладка Формулы, группа Зависимости формул, кнопка Показать формулы.
Задание 1.
Создать таблицу, показанную на Рис. 1.
| A | B | C | D | E | F | ||||||
| Виды преступных деяний | 2012 год | 2013 год | 2014 год | 2015 год | Прогноз на 2016 год | ||||||
| Кол-во | % | Кол-во | % | Кол-во | % | Кол-во | % | Кол-во | % | ||
| кража | |||||||||||
| грабёж | |||||||||||
| разбой | |||||||||||
| мошенничество | |||||||||||
| ИТОГО по преступлениям против личности | |||||||||||
| Проникновение в чужие компьютерные сети | |||||||||||
| Распространение вредоносных программ | |||||||||||
| Нарушение работы ЭВМ и баз данных | |||||||||||
| ИТОГО по преступлениям в сфере высоких технологий | |||||||||||
| ИТОГО по всем преступным деяниям |
Рис. 1 Исходные данные для решения Задания 1.
Произвести подсчёт данных по промежуточным ИТОГО и по ИТОГО по всем преступным деяниям, применяя функцию (СУММ).
Вычислите прогноз на 2016 год по среднему значению четырёх предыдущих годов, применяя функцию (СРЗНАЧ).
Порядок выполнения Задания 1.
1. Если вам необходимо ввести в вашу таблицу еще один столбец, то можно, выделить строку на месте которой будет новая, войти в лента Главная пункт Вставить Вставить столбцы на лист.
2. Чтобы удалить ненужную вам строку или столбец войдите в лента Главная пункт Удалить.
3. Лист данных Excel может быть столь широк, что невозможно его разместить на всю ширину страницы, для этого в лента Разметка страницы пункт Ориентация установите «Альбомную ориентацию». Нажмите ОК. Откройте режим «Предварительный просмотр», чтобы посмотреть ваш лист полностью.
4. Проценты вычисляются по формуле (Пп *100)/Итого по всем видам, где Пп – первичная переменная (кол-во преступлений по кражам), Итого по всем видам устанавливается абсолютной ссылкой с помощью символа $.
5. Прогноз просчитывается по формуле среднего значения (формула – СРЕДЗНАЧ).
Переименуйте ярлычок Лист 1, присвоив ему имя «Преступные деяния».
Задание 2.
Перейти на новый лист рабочей книги.
Создать таблицу, показанную на Рис. 2
| A | B | C | D | E | F | G | H | |
| Продажа комплектующих к персональным компьютерам | ||||||||
| Месяц | Центр ЭВМ | ЭВМ-сервис | Дом бизнеса | Техноцентр | Среднее | Максимум | Минимум | |
| Январь | ||||||||
| Февраль | ||||||||
| Март | ||||||||
| Апрель | ||||||||
| Май | ||||||||
| Июнь | ||||||||
| Июль | ||||||||
| Август | ||||||||
| Сентябрь | ||||||||
| Октябрь | ||||||||
| Ноябрь | ||||||||
| Декабрь | ||||||||
| Итого: | ||||||||
| Максимум | ||||||||
| Минимум |
Рис. 2 Исходные данные для решения Задания 2.