Тема 8. Статистические функции

Приведем некоторые статистические функции.

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

Например, функция

=СРЗНАЧ(В5:В10)

вычислит среднее арифметическое значение содержимого блока ячеек В5:В10.

МАКС(числа) – находит наибольшее значение среди заданных чисел.

Например, функция

=МАКС(С3:С13)

найдет максимальное значение в блоке ячеек С3:С13.

МИН(числа) – находит наименьшее значение среди заданных чисел.

Например, функция

=МИН(С10:С20)

найдет минимальное значение в блоке ячеек С10:С20.

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

Например,

=СЧЕТ(B1:D3)

возвратит 6 рисунок 258.

Рисунок 258

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

Например,

=СЧЕТЗ(B1:D3)

возвратит 8 рисунок 259.

Рисунок 259

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

Например,

=СЧЕТЕСЛИ(B1:D3;”>3”)

возвратит 2 рисунок 260.

=СЧЕТЕСЛИ(B1:D3;”=ж”)

Рисунок 260

возвратит 1 рисунок 261.

Рисунок 261

Примеры.

1. Найти минимальное и максимальное значения из чисел, находящихся в блоке ячеек А2:D2 как показано на рисунках 262 – режим показа формул, и 263 – режим вычислений.

Рисунок 262

Рисунок 263

2. Вычислить средние арифметические значения содержимого блока ячеек А1:А5 и блока ячеек В1:В5 как показано на рисунках 264 – режим формул, и 265 – режим решения.

Рисунок 264

Рисунок 265

СРОТКЛ(число1; число2; …) – возвращает среднее абсолютных значений отклонений чисел от их среднего значения.

Среднее отклонение вычисляется по формуле :

где – среднее арифметическое,n – количество чисел.

Например,

СРОТКЛ(3;2;1;4;2;3;3) равняется 0,77551, т.е.

(3 + 2 + 1 + 4 + 2 + 3 + 3)/7 = 18/7 = 2,571429;

(|(3 – 2,57)|+|(2 – 2,57)|+|(1 – 2,57)|+|(4 – 2,57)|+|(2 – 2,57)|+|(3 – 2,57)|+|(3 – 2,57)|) = 0,77551.

3. Вычислить среднее отклонение чисел 3, 2, 1, 4, 2, 3, 3 как показано на рисунках 266 – режим формул, и 267 – режим решения.

Рисунок 266

Рисунок 267

Тема 9. Логические функции

Excel содержит разные логические функции. Некоторые из них проверяют условия, в зависимости от которых выполняются те или иные действия. Условие представляет собой математическое выражение, в котором сравниваются две величины: числа или числовые значения формул, текстовые строки, логические значения. Результатом проверки условия является логическое значение ИСТИНА (условие выполняется) или ЛОЖЬ (условие не выполняется). Рассмотрим некоторые из них.

Функции проверки условия.

Функция ЕСЛИ(логическое выражение; значение да; значение нет). Эта функция выполняет проверку условия, задаваемого первым аргументом. Если условие выполняется (ИСТИНА), то значением функции будет значение да, если не выполняется - то значением функции будет значение нет.

Рассмотрим примеры:

=ЕСЛИ(А6<0;15;25)

Если значение содержимого ячейки А6 будет меньше 0, то результат вычисления равен 15, в противном случае (т.е. если значение содержимого ячейки А6>=0) результат равен 25.

В аргументах функции можно использовать другие функции, например,

=ЕСЛИ(СУММ(D1:D6)>0; СУММ(D1:D6);0)

Результатом выполнения этой функции будет сумма значений содержимого ячеек D1:D6, если эта сумма положительная, и нулю в противном случае, то есть если эта сумма отрицательная или равна нулю как показано на рисунках 268, 269.

Рисунок 268

Рисунок 269

Аргументами функции, а также результатом выполнения функции могут быть текстовые константы.

Например,

=ЕСЛИ(В5>100;“Принять”;“Отказать”)

Если содержимое ячейки больше 100, то результатом выполнения функции будет значение “Принять”, в противном случае – “Отказать” как показано на рисунках 270, 271.

Рисунок 270

Рисунок 271

Текстовые значения могут быть и в логическом выражении:

=ЕСЛИ(В10=”отлично”;“100%”;“70%”)

Функции И, ИЛИ, НЕ.

=И(логическое выражение1; логическое выражение2; …)

=ИЛИ(логическое выражение1; логическое выражение2; …)

=НЕ(логическое выражение)

Эти функции дают возможность строить сложные логические выражения. Функции И() и ИЛИ() могут иметь в качестве аргументов до 30 логических выражений, функция НЕ() – только один аргумент. Аргументами этих функций могут быть логические значения, массивы, адреса ячеек.

Результаты, получаемые с помощью функций И() и ИЛИ() существенно отличаются.

Рассмотрим примеры:

=ЕСЛИ(И(А3>0;D3>0);“Решение есть”;“Решения нет”))

=ЕСЛИ(ИЛИ(А3<0; D3<0);“Решения нет”;“Решение есть”))

В первом случае: если и содержимое ячейки А3>0, и содержимое ячейки D3>0, результатом будет “Решение есть”, если содержимое хотя бы одной из ячеек (А3 или В3) <=0, результатом будет - “Решения нет” как показано на рисунках 272, 273, 274.

Рисунок 272

Рисунок 273

Рисунок 274

Примечание. Формулу можно сделать короче, если вместо текстовых констант ввести ссылки на них рисунок 275.

Рисунок 275

Во втором случае: если или содержимое ячейки А3<0, или содержимое ячейки D3<0, результатом будет “Решения нет”, в противном случае “Решение есть” как показано на рисунках 276, 277, 278.

Рисунок 276

Рисунок 277

Рисунок 278

Часто вместо функций И, ИЛИ нагляднее использовать операторы умножения и сложения. Например, рассматриваемые формулы можно записать:

=ЕСЛИ(А3>0*D3>0;“Решение есть”;“Решения нет”)) рисунок 279

Рисунок 279

=ЕСЛИ((А3<0)+(D3<0);“Решения нет”;“Решение есть”)) рисунок 280.

Рисунок 280

Функция НЕ() выдает логическое значение противоположное логическому значению своего аргумента:

=НЕ(А1=А2) эквивалентна =А1<>A2

=НЕ(А1>А2) эквивалентна =А1<=A2

Например

=НЕ(2*2=4) вернет ЛОЖЬ, так как условие 2*2=4 истинно рисунок 281.

Рисунок 281

=НЕ(2*2=5) вернет ИСТИНА, так как условие 2*2=5 ложно рисунок 282.

Рисунок 282

Вложение функций ЕСЛИ().

Функции ЕСЛИ() могут вкладываться друг в друга. Можно вложить друг в друга в качестве второго и третьего аргументов до 7 функций ЕСЛИ().

=ЕСЛИ(В10=25;“Отлично”;ЕСЛИ(И(В10<25;В10>22);“Хорошо”;ЕСЛИ(И(В10<=22;B10>19);“Удовлетворительно”;“Неудовлетворительно”))).

Выполняется функция следующим образом: если число, находящееся в ячейке В10, равно 25, то значением функции будет “Отлично”; иначе – если число, находящееся в ячейке В10 меньше 25, но больше 22, то функция примет значение “Хорошо”, иначе – если В10 меньше или равно 22 и больше 19, функция примет значение “Удовлетворительно”, иначе “Неудовлетворительно” как показано на рисунках 283, 284, 285.

Рисунок 283

Рисунок 284

Рисунок 285

Следует иметь в виду, что вложенных функций ЕСЛИ() должно быть на единицу меньше, чем возможных вариантов принимаемых значений. Иногда аргументы логических функций становятся громоздкими, если приходится проверять выполнение одного и того же условия элементами блоков ячеек, например:

=ЕСЛИ(И(А4>=B2;B4>=B2;C4>=B2;D4>=B2;E4>=B2;F4>=B2); “Да”; “Нет”) рисунок 286.

Рисунок 286

Условие можно записать короче, если в аргументе функции использовать блок ячеек:

=ЕСЛИ(И(А4:F4>=B2);“Да”;“Нет”) рисунок 287.

Рисунок 287