Информационные технологии организации, обработки и анализа экономической информации в Excel. Статистические функции

В этом аргументе можно  использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу; звездочка — любой последовательности символов. Если нужно найти сам вопросительный знак или звездочку, то перед ними следует поставить знак тильды (~).

Значение «диапазон_усреднения»  не обязательно должно совпадать  по размеру и форме с диапазоном. При определении фактических ячеек, для которых вычисляется среднее, в качестве начальной используется верхняя левая ячейка в «диапазон_усреднения», а затем добавляются ячейки с совпадающим размером и формой. Например:

Если диапазон равен	И «диапазон_усреднения»	Обрабатываемые ячейки
A1:A5	B1:B5	B1:B5
A1:A5	B1:B3	B1:B5
A1:B4	C1:D4	C1:D4

 

Пример: Вычисление среднего значения доходов региональных представительств

 

Рисунок 1.5.2 - Поиск значения в диапазоне с помощью функции  СРЗНАЧЕСЛИ

6. Стандартное отклонение и дисперсия

1. Функция СТАНДОТКЛОН

Оценивает стандартное  отклонение по выборке. Стандартное отклонение — это мера того, насколько широко разбросаны точки данных относительно их среднего.

СТАНДОТКЛОН(число1; число2; ...)

Число1, число2,... — от 1 до 255 числовых аргументов, соответствующих  выборке из генеральной совокупности. Вместо аргументов, разделенных точкой с запятой, можно использовать массив или ссылку на массив.

Функция СТАНДОТКЛОН  предполагает, что аргументы являются только выборкой из генеральной совокупности. Если данные представляют всю генеральную совокупность, то стандартное отклонение следует вычислять с помощью функции СТАНДОТКЛОНП.

Чтобы включить логические значения и текстовые представления  чисел в ссылку как часть вычисления, используйте функцию СТАНДОТКЛОНА.

Стандартное отклонение вычисляется с использованием «n-1» метода. Аргументы могут быть либо числами, либо содержащими числа именами, массивами или ссылками. Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов. Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются. Аргументы, которые представляют собой значения ошибок или текст, не преобразуемый в числа, вызывают ошибку.

Функция СТАНДОТКЛОН вычисляется по следующей формуле:

где x — выборочное среднее СРЗНАЧ(число1,число2,…),

n — размер выборки.

Пример: Предположим, что из инструментов, отштампованных одной и той же машиной, выбраны наугад 10 штук и испытаны на излом.

Рисунок 1.6.1 - Поиск значения в диапазоне с помощью функции  СТАНДОТКЛОН

2. Функция ДИСП

Оценивает дисперсию  по выборке.

ДИСП(число1;число2; ...)

Число1, число2,... — от 1 до 255 числовых аргументов, соответствующих выборке из генеральной совокупности.

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

Чтобы включить логические значения и текстовые представления  чисел в ссылку как часть вычисления, используйте функцию ДИСПА.

Аргументы могут быть либо числами, либо содержащими числа  именами, массивами или ссылками. Учитываются логические значения и текстовые представления чисел, которые непосредственно введены в список аргументов. Если аргумент является массивом или ссылкой, то учитываются только числа в массиве или ссылке. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются. Аргументы, которые представляют собой значения ошибок или текст, не преобразуемый в числа, вызывают ошибку.

Функция ДИСП вычисляется  по следующей формуле:

где x — выборочное среднее СРЗНАЧ(число1;число2;…),

n — размер выборки.

Пример: Предположим, что из инструментов, отштампованных одной и той же машиной, выбраны наугад 10 штук и испытаны на прочность.

Рисунок 1.6.2 - Поиск значения в диапазоне с помощью функции  ДИСП

exceРРрррррррстатистическая функция

 

2. Практическая часть

Индивидуальное задание  для Воробьевой Оксаны Павловны:

Задача 1 – В – 3;

Задача 2 – О – 44;

Задача 3 – Р – 76;

Задача 4 – О – 104;

Задача 5 – О – 134;

Задача 6 – К – 160;

Задача 7 – С – 197;

Задача 8 – П – 225;

Задача 9 – А – 241;

Задача 10 – В – 273

1. Задача 1 (В – №3)

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

Решение

Для решения задачи необходимо воспользоваться функцией СЧЕТЕСЛИ, для того чтобы определить общее  количество людей, получающих зарплату. Синтаксис функции выглядит следующим образом: =СЧЁТЕСЛИ(C2:C10;"<>-")

Таблица выглядит следующим  образом:

№	ФИО	Сумма
1	Ахметдянова Наталья Анатольевна	12460
2	Бервинов  Денис Александрович	65789
3	Великосельская  Наталья Олеговна	23465
4	Вялых Любовь Александровна	65123
5	Гайдатулин  Евгений Олегович	-
6	Какунин Владислав Андреевич	7564
7	Качакова  Ольга Юрьевна	26374
8	Корягин Дмитрий Анатольевич	-
9	Лагута  Максим Сергеевич	16367
	количество	7

2. Задача 2 (О – №44)

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

Решение

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

Получившиеся формулы  имеют следующий вид: =СРЗНАЧ(B2:B32) и =СРЗНАЧ(D2:D29)

Таблица выглядит следующим  образом:

Январь		Февраль
1	13	1	45
2	23	2	3
3	43	3	23
4	23	4	45
5	43	5	65
6	3	6	3
7	23	7	34
8	34	8	45
9	54	9	65
10	2	10	4
11	2	11	5
12		12	56
13	23	13	3
14	34	14	34
15		15	54
16	2	16	56
17		17	2
18	34	18	2
19	43	19	3
20	23	20	4
21	32	21	54
22		22	6
23	23	23	67
24	23	24	78
25	23	25	56
26	43	26	4
27	12	27	34
28	34	28	23
29	54
30	564
31	43
среднее	47,14815		31,17857

 

3. Задача 3 (Р – №76)

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

Решение

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

Получившаяся формула  имеет следующий вид: =ЕСЛИ(A2:C2>0;СУММ(A2:C2);A2)

Таблица имеет следующий  вид:

а	б	в
3	2	1
Итог	6

4. Задача 4 (О – №104)

Известна сумма баллов, набранных абитуриентами колледжа, допущенных к конкурсу на поступление. Определить количество абитуриентов, принятых в учебное заведение, если «проходной балл» (минимально необходимая сумма баллов) для поступления равна 12.

Решение

Для решения задачи воспользуемся  функцией СЧЕТЕСЛИ, которая подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.

Формула имеет следующий  вид: =СЧЁТЕСЛИ(C2:C10;">=12")

Таблица имеет следующий  вид:

1	Ахметдянова Наталья Анатольевна	12
2	Бервинов  Денис Александрович	11
3	Великосельская  Наталья Олеговна	11
4	Вялых Любовь Александровна	12
5	Гайдатулин  Евгений Олегович	23
6	Какунин Владислав Андреевич	12
7	Качакова  Ольга Юрьевна	13
8	Корягин Дмитрий Анатольевич	14
9	Лагута  Максим Сергеевич	15
	количество	7

 

5. Задача 5 (О – №134)

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

а) одну вещь;

б) более двух вещей.

Решение

Для решения задачи воспользуемся  функцией СУММЕСЛИ, которая суммирует ячейки, заданные критерием.

Функция выглядит следующим  образом:

А) =СУММЕСЛИ(C2:C9;"=1";D2:D9)

Б) =СУММЕСЛИ(C2:C9;">1";D2:D9)

Таблица выглядит следующим  образом:

№	ФИО	Кол-во	Вес
1	Ахметдянова Наталья Анатольевна	1	12
2	Бервинов  Денис Александрович	2	34
3	Великосельская  Наталья Олеговна	2	23
4	Вялых Любовь Александровна	3	4
5	Гайдатулин  Евгений Олегович	4	2
6	Какунин Владислав Андреевич	3	34
7	Качакова  Ольга Юрьевна	2	23
8	Корягин Дмитрий Анатольевич	1	43
			55
			120

6. Задача 6 (К – №160)

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

Известны сведения о крупнейших реках Африки:

 

 

Решение

Для решения задачи воспользуемся  функцией МАКС, которая возвращает наибольшее значение из набора значений.

Функция выглядит следующим  образом:

=МАКС(C2:C13)

Полученная таблица  выглядит так:

Название	Длина, км	Площадь бассейна
Вольта	1600	394
Замбези	2660	1330
Конго ( с Луабалой)	4370	3820
Лимпопо	1600	440
Нигер	4160	2092
Нил (с Кагерой)	6671	2870
Окаванго (Кубанго)	1800	785
Оранжевая	1860	1020
Рувума	800	145
Руфиджи	1400	178
Сенегал	1430	441
Шари	1450	700
	макс	3820