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

ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)

Искомое_значение — значение, которое требуется найти в  первой строке таблицы. «Искомое_значение»  может быть значением, ссылкой или  текстовой строкой.

Таблица — таблица  с информацией, в которой производится поиск данных. Можно использовать ссылку на интервал или имя интервала.

Номер_строки — номер  строки в массиве «таблица», из которой  будет возвращено сопоставляемое значение. Если значение аргумента «номер_строки» равно 1, то возвращается значение из первой строки аргумента «таблица», если оно равно 2 — из второй строки и т. д. Если значение аргумента «номер_строки» меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе «таблица», возвращается значение ошибки #ССЫЛ!.

Интервальный_просмотр —  логическое значение, которое определяет, какое соответствие должна искать функция ГПР — точное или приблизительное. Если этот аргумент имеет значение ИСТИНА или опущен, возвращается приблизительно соответствующее значение; при отсутствии точного соответствия возвращается наибольшее из значений, меньших, чем «искомое_значение». Если этот аргумент имеет значение ЛОЖЬ, функция ГПР ищет точное соответствие. Если оно не найдено, возвращается значение ошибки #Н/Д.

Пример: Поиск значения 78 658р. в строке 1, нахождение наибольшего значения, меньшего 78 658р. (50 000р.), и возвращение значения из строки 2, находящегося в том же столбце, что и 50 000р. (20%)

Рисунок 1.2.2 – Поиск  значения с помощью функции ГПР

3. Ранг, процентный ранг, персентиль, квартиль

1. Функция РАНГ

Возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если отсортировать список, то ранг числа будет его позицией.)

РАНГ(число;ссылка;порядок)

Число — число, для  которого определяется ранг.

Ссылка — массив или  ссылка на список чисел. Нечисловые значения в ссылке игнорируются.

Порядок — число, определяющее способ упорядочения.

Если значение аргумента  «порядок» равно 0 или опущено, ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке убывания.

Если значение аргумента  «порядок» — любое число, кроме  нуля, то ранг числа определяется в Microsoft Excel так, как если бы ссылка была списком, отсортированным в порядке возрастания.

Функция РАНГ присваивает  повторяющимся числам одинаковые значения ранга. Однако наличие повторяющихся чисел влияет на ранги последующих чисел. Например, если в списке целых чисел, отсортированных по возрастанию, дважды встречается число 10, имеющее ранг 5, число 11 будет иметь ранг 7 (ни одно из чисел не будет иметь ранга 6).

Может потребоваться  использование определения ранга  с учетом связей в учетной записи. В предыдущем примере был взят пересмотренный ранг 5.5 для числа 10. Для этого следует добавить поправочный  коэффициент (см. ниже) к значению, возвращаемому функцией РАНГ. Данный поправочный коэффициент может применяться в обоих случаях: когда ранг вычисляется в порядке убывания (аргумент «порядок» имеет нулевое значение или опущен) и в порядке возрастания (значение аргумента «порядок» не равно нулю).

Поправочный коэффициент для связанных рангов = [СЧЕТ(ссылка) + 1 – РАНГ(число, ссылка, 0) – РАНГ(число, ссылка, 1)]/2.

Пример: РАНГ(A2,A1:A5,1) равен 3. Поправочный коэффициент равен (5 + 1 – 2 – 3)/2 = 0,5, а ранг, пересмотренный с учетом связей в учетной записи, равен 3 + 0,5 = 3,5. Если то или иное число появляется в ссылке только один раз, поправочный коэффициент будет равен 0, поскольку РАНГ для связи не будет изменяться.

 

Пример 1.3.1 – Поиск значения в диапазоне с помощью функции РАНГ

2. Функция ПРОЦЕНТРАНГ

Возвращает категорию значения в наборе данных как процентное содержание в наборе данных. Эта функция используется для оценки относительного положения точки данных в множестве данных. Например, c помощью функции ПРОЦЕНТРАНГ можно оценить положение подходящего результата тестирования среди всех результатов тестирования.

ПРОЦЕНТРАНГ(массив;x;разрядность)

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

x — значение, для которого  определяется процентное содержание.

Разрядность — необязательное значение, определяющее количество значащих цифр для возвращаемого процентного значения. Если этот аргумент опущен, то функция ПРОЦЕНТРАНГ использует три цифры (0,xxx).

Если массив пуст, функция  ПРОЦЕНТРАНГ возвращает значение ошибки #ЧИСЛО!. Если разрядность < 1, функция ПРОЦЕНТРАНГ возвращает значение ошибки #ЧИСЛО!. Если x не соответствует ни одному из значений аргумента «массив», функция ПРОЦЕНТРАНГ производит интерполяцию и возвращает корректное значение процентного содержания.

 

 

 

 

Пример:

Рисуно 1.3.2 – Поиск значения в диапазоне с помощью функции ПРОЦЕНТРАНГ

3. Функция ПЕРСЕНТИЛЬ

Возвращает k-ую персентиль для значений из интервала. Эта функция  используется для определения порога приемлемости. Например, можно принять решение экзаменовать только тех кандидатов, которые набрали большее количество баллов, чем 90-ая персентиль.

ПЕРСЕНТИЛЬ(массив;k)

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

k — значение персентили  в интервале от 0 до 1 включительно.

Если массив пуст или  содержит более 8191 точек данных, функция  ПЕРСЕНТИЛЬ возвращает значение ошибки #ЧИСЛО!. Если k не является числом, функция ПЕРСЕНТИЛЬ возвращает значение ошибки #ЗНАЧ!. Если k < 0 или k > 1, функция ПЕРСЕНТИЛЬ возвращает значение ошибки #ЧИСЛО!. Если k не кратно 1/(n - 1), функция ПЕРСЕНТИЛЬ производит интерполяцию для определения значения k-ой персентили.

 

Пример:

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

4. Функция КВАРТИЛЬ

Возвращает квартиль множества данных. Квартиль часто используются при анализе продаж для разбиения генеральной совокупности на группы. Например, можно воспользоваться функцией КВАРТИЛЬ, чтобы найти среди всех предприятий 25 процентов наиболее доходных.

КВАРТИЛЬ(массив;часть)

Массив — массив или  интервал ячеек с числовыми значениями, для которых определяются значения квартилей.

Часть — значение, которое  требуется вернуть.

 

Если часть равна	КВАРТИЛЬ возвращает
0	Минимальное значение
1	Первую квартиль (25-ю персентиль)
2	Значение медианы (50-ю персентиль)
3	Третью квартиль (75-ю персентиль)
4	Максимальное значение

 

Если массив пуст, функция КВАРТИЛЬ возвращает значение ошибки #ЧИСЛО!. Если значение аргумента «часть» не является целым числом, то оно усекается. Если часть < 0 или часть > 4, функция КВАРТИЛЬ возвращает значение ошибки #ЧИСЛО!. Функции МИН, МЕДИАНА и МАКС возвращают то же значение, что и функция КВАРТИЛЬ, если аргумент «часть» равен соответственно 0, 2 или 4.

 

Пример:

Рисунок 1.3.4 - Поиск значения в диапазоне с помощью функции  КВАРТИЛЬ

4. Функция ПЕРЕСТ: вычисление количества размещений

Возвращает количество размещений для заданного числа  объектов, которые выбираются из общего числа объектов. Размещения — это  любое множество или подмножество объектов или событий, в котором важен внутренний порядок. Размещения отличаются от сочетаний, для которых внутренний порядок не имеет значения. Эта функция используется, например, для вычисления вероятностей в лотереях.

ПЕРЕСТ(число;число_выбранных)

Число — целое число, задающее количество объектов.

Число_выбранных —  целое число, задающее количество объектов в каждом размещении.

Оба аргумента усекаются  до целых. Если число или число_выбранных  не является числом, то функция ПЕРЕСТ возвращает значение ошибки #ЗНАЧ!. Если число ≤ 0 или число_выбранных < 0, функция ПЕРЕСТ возвращает значение ошибки #ЧИСЛО!. Если число < число_выбранных, то функция ПЕРЕСТ возвращает значение ошибки #ЧИСЛО!.

Уравнение для числа  размещений имеет следующий вид:

Пример: Предположим, что нужно определить шансы на получение выигрышного лотерейного номера. Лотерейный номер состоит из трех чисел, каждое из которых может находиться в диапазоне от 0 (ноль) до 99 включительно. В этом случае количество возможных размещений определяется с помощью следующей функции.

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

5. Вычисление средних значений

1. Функция СРЗНАЧ

Возвращает среднее  значение (среднее арифметическое) аргументов. СРЗНАЧ(число1, [число2],...)

Число1. Обязательный аргумент. Первое число, ссылка на ячейку или диапазон, для которого требуется вычислить среднее значение.

Число2, ... Необязательный аргумент. Дополнительные числа, ссылки на ячейки или диапазоны, для которых  требуется вычислить среднее значение. Аргументов может быть не более 255.

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

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

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

Среднее значение — это  среднее арифметическое, которое  вычисляется путем сложения набора чисел с последующим делением полученной суммы на их количество. Например, средним значением для чисел 2, 3, 3, 5, 7 и 10 будет 5, которое является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана — это число, которое является серединой множества чисел, то есть половина чисел имеют значения большие, чем медиана, а половина чисел имеют значения меньшие, чем медиана. Например, медианой для чисел 2, 3, 3, 5, 7 и 10 будет 4.

Мода — это число, наиболее часто встречающееся в  данном наборе чисел. Например, модой для чисел 2, 3, 3, 5, 7 и 10 будет 3.

При симметричном распределении  множества чисел эти величины оценки степени централизации равны. При ассиметричном распределении  множества чисел они могут отличаться.

 

 

 

 

 

 

 

 

 

Пример:

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

2. Функция СРЗНАЧЕСЛИ

Если требуется вычислить  среднее значение только для тех  значений, которые удовлетворяют определенным критериям, используют функцию СРЗНАЧЕСЛИ.

Возвращает среднее  значение (среднее арифметическое) всех ячеек в диапазоне, которые соответствуют данному условию.

СРЗНАЧЕСЛИ(диапазон, условие, «диапазон_усреднения»)

Диапазон — одна или  несколько ячеек для вычисления среднего, включающих числа или имена, массивы, или ссылки, содержащие числа.

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

Диапазон_усреднения —  фактическое множество ячеек для вычисления среднего. Если он не указан, используется диапазон.

Ячейки в диапазоне, которые содержат значения ИСТИНА или  ЛОЖЬ, игнорируются. Если ячейка в «диапазоне_усреднения» пустая, функция СРЗНАЧЕСЛИ игнорирует ее. Если диапазон является пустым или текстовым значением, то функция СРЗНАЧЕСЛИ возвращает значение ошибки #ДЕЛ/0!. Если ячейка в условии пустая, «СРЗНАЧЕСЛИ» обрабатывает ее как ячейки со значением 0. Если ни одна ячейка в диапазоне не соответствует условию, функция СРЗНАЧЕСЛИ возвращает ошибку «#ДЕЛ/0!».