С помощью функции Частота рассчитайте абсолютную, относительную и кумулятивную частоты для п.1а и 1б

Группировка данных

Задание 1

По файлу Группы_частоты_отбор разбейте банки на группы по полю «вложения в госбумаги» двумя способами.

a. на n групп по формуле формула Стерджесса: n = 1 + (3,322× lgN), где N — количество наблюдений.

b. С помощью функции ЕСЛИ на 4 группы:

Группа 1-меньше 3000

Группа 2- от 3000 до 12000 включительно

Группа 3- больше 12000 меньше 500000

Группа 4- больше 500000 включительно

А

При построении вариационного ряда с интервальными значениями прежде всего необходимо установить величину интервала i, которая определяется как отношение размаха вариации R к числу групп m:

где R = x_max - x_min; m = 1 + 3,322 lgn (формула Стерджесса); n - общее число единиц совокупности.

1. Вычисляем N (если нумерация несплошная)
2. Расчет количества интервалов по формуле Стерджесса
3. Округляем
4. Рассчитываем ранги для банков по вложениям.
5. Сортируем.
6. Есть отдельная группа, где вложения равны 0.
7. Оставшихся рангов - 240, групп - 8
8. Рассчитываем шаг
9. Отсчитываем в столбце № группы по 30 банков (столбец Y - вспомогательный)

Б

Функция ЕСЛИ

Как задать больше условий, например:

2 - двойка
3 - удовлетворительно
4 - хорошо
5 - отлично
х - нет
з - зачёт

=ЕСЛИ(A3=5;"отлично";ЕСЛИ(A3=4;"хорошо";ЕСЛИ(A3=3;"удовлетворительно";ЕСЛИ(A3=2;"двойка";ЕСЛИ(A3="з";"зачет";"нет")))))

Еще вариант, который можно переделать и для 12-балльной системы:
=ЕСЛИ(И(A1>0;A1<6); ВЫБОР(A1;"нет";"неуд";"уд";"хор";"отл"); ЕСЛИ(A1="з";"зачет";"нет"))

Частоты

Задание 2

С помощью функции Частота рассчитайте абсолютную, относительную и кумулятивную частоты для п.1а и 1б

А

Создаем столбец Интервалы и вносим туда список групп.

Формулы, приведенные в данном примере, должны быть введены как формулы массива (Формула массива. Формула, выполняющая несколько вычислений над одним или несколькими наборами значений, а затем возвращающая один или несколько результатов. Формулы массива заключены в фигурные скобки { } и вводятся нажатием клавиш CTRL+SHIFT+ВВОД.). Выделите ячейки, содержащие формулу, нажмите клавишу F2, а затем нажмите клавиши CTRL+SHIFT+ВВОД.

Б

Для подсчёта количества искомого значения достаточно использовать стандартную функцию рабочего листа =СЧЁТЕСЛИ()

ЧАСТОТА, для подсчета числа уникальных значений. Она игнорирует текстовые и нулевые значения. Для первого появления конкретного значения эта функция возвращает количество экземпляров данного значения. Для каждого последующего экземпляра того же значения, эта функция возвращает ноль.

ПОИСКПОЗ, для возращения местонахождения текстового значения в диапазоне. Это число затем используется в качестве аргумента функции ЧАСТОТА для обработки соответствующих текстовых значений.

ДЛСТР, для поиска пустых ячеек. Длина пустых ячеек равна 0

Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (апостроф) или в меню Сервис выделите пункт Зависимости формул и выберите команду Режим проверки формул.

Дополнения:

Формула Описание (результат)

=СУММ(ЕСЛИ(ЧАСТОТА(A2:A10,A2:A10)>0,1)) Подсчитывает количество уникальных числовых значений в ячейках A2:A10, не учитывая ни пустых ячеек, ни текстовых значений (4)

=СУММ(ЕСЛИ(ЧАСТОТА(ПОИСКПОЗ(B2:B10,B2:B10,0),ПОИСК ПОЗ(B2:B10,B2:B10,0))>0,1)) Подсчитывает количество уникальных текстовых и числовых значений в ячейках B2:B10 (среди которых не должно быть пустых ячеек) (7)

=СУММ(ЕСЛИ(ЧАСТОТА(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),""), ЕСЛИ(ДЛСТР(A2:A10)>0,ПОИСКПОЗ(A2:A10,A2:A10,0),""))>0,1)) Подсчитывает количество уникальных текстовых и числовых значений в ячейках A2:A10, не учитывая пустых ячеек (6)

Отбор данных

Задание 3а

С помощью функции БД найдите банк, у которого суммарные обязательства больше 2200000 и меньше 2800000 и вложения в госбумаги или равны нулю или больше 200000 (ответ ИСТ БРИДЖ БАНК).

БДИЗВЛЕЧЬ Суммирует числа в столбце списка или базы данных, которые удовлетворяют заданным условиям.

Синтаксис

БДИЗВЛЕЧЬ (база_данных; поле; критерий)

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

Поле определяет столбец, используемый функцией. Аргумент «поле» может быть задан как текст с названием столбца в двойных кавычках, например «Возраст» или «Урожай» в приведенном ниже примере базы данных, или как число, задающее положение столбца в списке: 1 — для первого поля, 2 — для второго поля и так далее.

Критерий — это интервал ячеек, который содержит задаваемые условия. Любой интервал, который содержит по крайней мере одно название столбца и по крайней мере одну ячейку под названием столбца с условием, может быть использован как аргумент критерий БДФункции.

Советы

• Любой интервал, который содержит по крайней мере одно название столбца и по крайней мере одну ячейку под названием столбца с условием, может быть использован как аргумент «критерий».

Например, если интервал G1:G2 содержит название столбца «Доход» в ячейке G1 и величину 10 000 в ячейке G2, то можно определить интервал «СоответствуетДоходу» и использовать это имя как аргумент «критерий» в функции баз данных.

• Несмотря на то, что диапазон критериев может располагаться в любом месте таблицы, не стоит помещать его внизу списка. Это связано с тем, что данные, добавляемые в список командой Форма меню Данные, вставляются с первой строки после списка. Если эта строка не пустая, Microsoft Excel не сможет добавить данные в список.
• Диапазон критериев не должен перекрываться со списком.
• Чтобы выполнить операцию над целым столбцом базы данных, нужно ввести пустую ячейку под названием столбца в диапазоне критерия.

Задание 3б

С помощью расширенного фильтра и вычисляемого критерия отберите на новый лист все банки, работающие активы которых отличаются от минимального более чем на 10млн. Выборка должна содержать только 2 поля «краткое наименование банка» и «работающие активы».

Примеры условий

Несколько условий для одного столбца

При наличии для одного столбца двух и более условий отбора введите эти условия отбора непосредственно друг под другом в отдельные строки. Например, следующий диапазон условий отбора отбирает строки, содержащие в столбце «Продавец» значения «Белов», «Батурин» или «Рощин».

Одно условие для нескольких столбцов

Для того чтобы найти в нескольких столбцах данные, отвечающие одному условию отбора, введите все условия отбора в одну строку диапазона условий отбора. Например, следующий диапазон условий отбора возвращает все строки, содержащие значения «Продукты» в столбце «Товар», «Белов» в столбце «Продавец» и объем продаж более 1000 руб.

Разные условия для разных столбцов

Для того чтобы найти данные, отвечающие одному условию, в одном столбце, или отвечающие другому условию, в другом столбце, введите условия отбора в разные строки диапазона условий отбора. Например, следующий диапазон условий отбора отображает все строки, содержащие значение «Продукты» в столбце «Товар», «Белов» в столбце «Продавец», либо объем продаж, превышающий 1000 руб.

Один из двух наборов условий для двух столбцов

Для того чтобы найти строки, отвечающие одному из двух наборов условий, каждый из которых содержит условия более чем для одного столбца, введите эти условия отбора в отдельные строки. Например, следующий диапазон условий отбора отображает строки, содержащие как значение «Белов» в столбце «Продавец», так и объем продаж, превышающий 3000 руб., а также строки по продавцу Батурину с объемами продаж более 1500 руб.

Более двух наборов условий для одного столбца

Для того чтобы найти строки, отвечающие более чем двум наборам условий, включите несколько столбцов с одинаковыми заголовками. Например, следующий диапазон условий отбора возвращает продаж на сумму от 5000 до 8000, а также продаж на сумму менее 500.

Условия, создаваемые как результат выполнения формулы

В качестве условия отбора можно использовать вычисляемое значение, являющееся результатом выполнения формулы. При создании условия отбора с помощью формулы не используйте заголовок столбца в качестве заголовка условия; либо оставьте условие отбора без заголовка, либо используйте заголовок, не являющийся заголовком столбца в списке. Например, следующий диапазон условий отбора отображает строки, которые содержат в столбце C значение, превышающее среднее значение ячеек диапазона C7:C10.

=C7>СРЕДНЕЕ($C$7:$C$10)

Примечания

· Формула, используемая для создания условия отбора, должна использовать для ссылки на подпись столбца (например, «Продажи») или на соответствующее поле в первой записи относительную ссылку. Все остальные ссылки в формуле должны быть абсолютными, а формула должна возвращать результат ИСТИНА или ЛОЖЬ. В примере формулы «C7» является ссылкой на поле (столбец C) первой записи (строка 7) списка.

· При использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

· При вычислениях Microsoft Excel не учитывает регистр букв.

Задание 4

Создайте следующую сводную таблицу по 20 банкам, имеющим самый низкий рейтинг:

Не нашли, что искали? Воспользуйтесь поиском: