Реализация анализа данных в MS Excel

В рамках табличного процессора MS Excel возможна реализация алгоритмов, направленных на анализ данных. Для реализации таких алгоритмов в раках MS Excel используется формулы, записанные в рамках синтаксиса программы с использованием функции СУММЕСЛИ, СЧЁТЕСЛИ т т.д. Если в алгоритме реализуется одновременно более одного условия, тогда можно воспользоваться математическими операциями с функциями или аналогичными функциям, позволяющими использовать несколько условий СУММЕСЛИМН и СЧЁТЕСЛИМН (см. справку MS Excel). Рассмотрим более подробно функции СЧЁТЕСЛИ и СУММЕСЛИ.

СЧЁТЕСЛИ (диапазон; критерий) – подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.

Диапазон – это одна или несколько ячеек, проверяемых на выполнение заданного критерия. Ячейки могут содержать числа, имена, массивы, ссылки.

Критерий – условие в форме числа, выражения, текста или ссылки на ячейку, который определяет, какие ячейки нужно подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки" или B4. Примечание: синтаксис критерия должен совпадать с синтаксисом ячеек Диапазона.

Пример использования функции СЧЁТЕСЛИ

Пусть даны сведения по новорожденным в Нытвенском районе Пермского края за май месяц 2016 г. Необходимо подсчитать количество мальчиков и девочек, рожденных в отчетный период, а также найти количество детей с ростом от 53 до 54 см.

Таблица 3

Данные по новорожденным в Нытвенском районе Пермского края за май месяц 2016 г.

	A	B	C	D
	ФИО	Пол ребенка	Вес, кг	Рост, см
	Макова Д.Д.	Жен.	2,952	52,0
	Анисимов А.С.	Муж.	3,693	53,5
	Пузко А.Д.	Жен.	3,206	54,8
	Ас В.А.	Жен.	2,562	52,5
	Харин А.В.	Муж.	2,569	54,7
	Зюзин М.И.	Муж.	3,012	52,9
	Илюков И.И.	Муж.	3,120	53,3
…	…	…	…	…
	Закова О.А.	Жен.	2,896	54,4

Функции:

1. Функции для определения количества мальчиков и девочек рожденных в отчетный период:

«=СЧЁТЕСЛИ(B2:B121; "Жен.")» – функция определяет количество девочек, рожденных в мае 2016 г.

«=СЧЁТЕСЛИ(B2:B121; "=Муж.")» – функция определяет количество мальчиков, рожденных в мае 2016 г.

2. Формула для определения количества детей с ростом от 53 до 54 см:

«=СЧЁТЕСЛИ(D2:D121; "<=54") - СЧЁТЕСЛИ(D2:D121; "<53")» – формула определяет количество детей с ростом от 53 до 54 см. С использованием одной функции СЧЁТЕСЛИ определить количество ячеек соответствующих заданным условиям не возможно, так как функция позволяет проверить только одно условие. Таким образом сначала находятся все дети с ростом менее 54 см и из их количества вычитается количество детей с ростом менее 53 см. Так же для определения количества детей с ростом от 53 до 54 см можно использовать функцию СЧЁТЕСЛИМН (см. справку MS Excel).

СУММЕСЛИ (диапазон; критерий; диапазон_суммирования) – суммирует ячейки из диапазона суммирования, при выполнении заданныого условия, соответствующей ячейки первого диапазона.

Диапазон – диапазон ячеек, который оценивается относительно выполнения заданного критерия. Ячейки могут содержать числа, имена, массивы, ссылки.

Критерий – условие в форме числа, выражения или текста, определяющий, какие ячейки должны суммироваться. Например, аргумент «условие» может быть выражен как 32, "32", ">32" или "яблоки".

Диапазон_суммирования – фактические ячейки, которые необходимо просуммировать, если соответствующие им ячейки в первом диапазоне отвечают заданному условию.

Пример использования функции СУММЕСЛИ

Используя таблицу 3 с данными по новорожденным в Нытвенском районе Пермского края за май месяц 2016 г. найти средний вес мальчиков, рожденных в отчетный период, а также найти средний вес детей с ростом от 53 до 54 см.

Функции:

1. Формула для определения среднего веса мальчиков рожденных в отчетный период:

«=СУММЕСЛИ(B2:B121;"Муж.";C2:C121)/СЧЁТЕСЛИ(B2:B121;"Муж.")» – формула позволяет определить средний вес мальчиков, рожденных в мае 2016 г. Функция СУММЕСЛИ проверяет пол ребенка в столбце B2:B121 и складывает ячейки из столбца C2:C121 при выполнении заданного условия. В дальнейшем найденный суммарный вес делиться на количество мальчиков, рожденных в отчетный период, которое найдено с использованием функции СЧЁТЕСЛИ.

2. Формула для определения среднего веса детей с ростом от 53 до 54 см:

«=(СУММЕСЛИ(D2:D121;"<=54";С2:С121) - СУММЕСЛИ(D2:D121; "<53"; С2:С121))/(СЧЁТЕСЛИ(D2:D121; "<=54") - СЧЁТЕСЛИ(D2:D121; "<53"))» – формула определяет средний вес детей с ростом от 53 до 54 см. С использованием одной функции СУММЕСЛИ определить общий вес детей, соответствующих заданным условиям не возможно, так как функция позволяет проверить только одно условие. Таким образом, сначала находим общую сумму веса детей с ростом менее 54 см, а затем вычитаем общий вес детей с ростом менее 53 см. Для нахождения среднего веса детей нам необходимо общий вес детей с ростом от 53 до 54 см поделить на количество детей удовлетворяющих заданным условиям. Так же для определения среднего веса детей с ростом от 53 до 54 см можно использовать функции СУММЕСЛИМН и СЧЁТЕСЛИМН (см. справку MS Excel).

 

Пример реализации алгоритма анализа данных в MS Excel

Рассмотрим на примере реализацию алгоритма анализа данных в MS Excel (задание 5). Необходимо выполнить ряд операций над данными склада магазина «Канцтоваров-много», приведенными в таблице 4:

1. Вычислить количество товара на складе от поставщика ООО"Трейд" и количество наименований ручек на складе (СЧЁТЕСЛИ, СУММЕСЛИ);

2. Найти количество товара на складе со стоимостью за единицу товара от 10 до 25 руб.

Таблица 4

Данные о складе магазина «Канцтоваров-много»

№ п/п	Поставщик	Наименование товара	Стоимость единицы товара, руб.	Количество товара на складе
	ООО Трейд	Ручка	12,56
	ОАО Тигр	Доска меловая	5256,3
	ОАО Тигр	Доска маркерная	7856,75
	ООО Трейд	Карандаш	8,56
	ИП Иванов И.А.	Бумага A4	156,3
	ОАО Тигр	Ручка	28,96
	ИП Иванов И.А.	Бумага A5	148,5
	ОАО Тигр	Крепеж	1564,5
	ИП Иванов И.А.	Тетрадь 16 лист.	3,5
	ООО Трейд	Карандаш	15,26
	ООО Трейд	Карандаш	14,56
	ОАО Тигр	Губка	256,12
	ООО Трейд	Ручка	25,6
	ООО Трейд	Ручка	27,8
	ИП Иванов И.А.	Тетрадь 32 лист.	5,6

 

Реализация алгоритма с анализом данных в MS Excel:

1. Перенесем данные таблицы 4 в MS Excel (рис. 9).

Рис. 9. Таблица данных в MS Excel

3. В ячейке D19 запишем формулу для вычисления количества единиц товара на складе от поставщика ООО"Трейд" с использование функции СУММЕСЛИ. Выражение в ячейке D19 имеет вид (рис. 10): =СУММЕСЛИ(C3:C17;"ООО Трейд";F3:F17).

Рис. 10. Вычисление количества товара на складе от поставщика ООО"Трейд"

4. В ячейке F19 запишем формулу для вычисления количества наименований ручек на складе с использованием функции СЧЁТЕСЛИ. Выражение в ячейке F19 имеет вид (рис. 11): =СЧЁТЕСЛИ(D3:D17;"Ручка").

Рис. 11. Вычисление количества наименований ручек

на складе

5. В ячейке H19 запишем формулу для вычисления количества товара на складе со стоимостью за единицу товара от 10 до 25 руб. с использованием функции СУММЕСЛИ. Выражение в ячейке H19 имеет вид (рис. 12): =СУММЕСЛИ(E3:E17;"<=25";F3:F17)-СУММЕСЛИ(E3:E17;"<10";F3:F17).

Рис. 12. Вычисление количества товара на складе со стоимостью за единицу товара от 10 до 25 руб.

6. В итоге получаем строку 19 в MS Excel, которая содержит ответы на ранее поставленные вопросы для анализа данных с использованием функций СЧЁТЕСЛИ и СУММЕСЛИ (таблица 5).

Таблица 5

Результат анализа данных из таблицы 4

Номер столбца→	С	D	E	F	G	H
Номер строки ↓
	Количество товара от поставщика ООО Трейд		Количество наименований ручек на складе		Количество товара(стоим. ед. от 10 до 25 руб.)

 

Рассмотренный выше анализ данных в MS Excel, является примером реализации части задания № 5 контрольной работы, в рамках задания 5 еще необходимо построить и отформатировать диаграмму, что будет рассмотрено в разделе 4.