II. Решение системы уравнений

I. Финансовая функция

Решить следующую задачу с использованием финансовой функции.

Через сколько лет произойдет полное погашение займа размером 6 млн. руб., если выплаты по 500 тыс. руб. и начисление процентов производятся каждые полгода, а ставка процента – 20% годовых. При расчете количества лет используйте функцию округления с избытком.

1. На листе “I” в ячейках A1:B8 в соответствие с примером на рисунке составить таблицу с исходными данными и результатами расчета с использованием финансовой функции.

2. Скопировать таблицу из ячеек A1:B8 в ячейки A10:B8. С помощью ПОДБОРА ПАРАМЕТРА найдите сколько нужно выплачивать ежемесячно, если погашать заём можно на два года дольше.

3. В ячейках C26:H32 построить таблицу данных (двухвходовую таблицу) по размеру накапливаемой суммы и выплатам. Задать по 6 значений, выбранных самостоятельно. В таблице не должно быть некорректных результатов.

II. Решение системы уравнений

Найти решение системы уравнений

на отрезке

1. Построить графики обеих функций на заданном отрезке в одной системе координат и найти точки пересечения кривых:

• х изменяется с шагом 0,24;
• визуально определить интервал/интервалы, в которых находятся решения системы;
• построить вторую диаграмму с графиками функций в одном из интервалов, содержащих решение системы. Шаг изменения x 0.0001.
• добавить на диаграмму сетки промежуточных делений

2. Решить систему уравнений с помощью надстройки ПОИСК РЕШЕНИЯили ПОДБОР ПАРАМЕТРА.

III. Частотная таблица и гистограмма

4. Лист RAW DATA содержит аналитический отчет по инвестициям в разработку игр. Поименовать все поля отчета названиями полей, используя команду Создать из выделенного. ИСПОЛЬЗОВАТЬ ЭТИ ИМЕНА В ФУНКЦИЯХ!!!!

5. Рассчитать необходимые показатели описательной статистики поля InvestmentUSD.

6. Для расчета количества интервалов группировки m поля InvestmentUSD использовать формулу , где n – размер числового ряда. Количество интервалов округлить с избытком до целых. Рассчитать ширину интервала и округлить ее с избытком до 0,001.

, где n – размер числового ряда, h – ширина интервала, x_max -максимальное значение поля InvestmentUSD, x_min – минимальное значение поля InvestmentUSD. Все вычисления должны быть выполнены с помощью функций округления, расчета максимума, минимума и подсчета количества числовых ячеек в диапазоне.

7. Вычислить верхние границы интервалов.

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

9. Отформатировать частотную таблицу: процентный формат, 2 знака после запятой для относительных и кумулятивных частот, заголовок таблицы, рамки, объединение ячеек, выравнивания, перенос текста по словам.

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

a. Добавить подписи значений данных для каждой точки гистограммы, произвольно изменив фон подписей.

b. Для гистограммы сблизить столбики.

c. Оставить только вертикальные линии сетки

d.  Легенда с перекрытием и с заливкой.

e.  Подписи оси х отформатировать 2-мя знаками после запятой и с горизонтальным выравниванием на
-45⁰.

f. Диаграмму разместить на отдельном листе, поименовав лист своей фамилией.

Рис. 1.

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

1. Разбить данные листа RAW DATA с помощью функции ЕСЛИ на 4 группы в соответствии с описанием группы, представленным ниже. С помощью функции СЧЁТЕСЛИ рассчитать количество записей отчета, относящихся к каждой группе (возможно перекрытие групп):

· Gr 1 – нулевой объем инвестиций за 2012 стартовый год(поле StartDate);

· Gr 2 – объем инвестиций в игры StrategyGames менее среднего значения объемов инвестиций по всем записям отчета;

· Gr 3 – записи отчета по инвестициям в игры MMO для смартфонов (CodeDevice =3);

· Gr 4 – остальные записи, не вошедшие в группы 1-3.

2. Построить объемную нормированную гистограмму с накоплениями по результатам расчета функции СЧЁТЕСЛИ. Оформить диаграмму в соответствии с образцом (заголовок, заливка только области диаграммы, подписи данных, отсутствие сетки и т.д.).

Рис. 2.

V. Задания на списки.

1. На листе RAW DATA содержится таблица с названиями устройств. Поименовать ее своими инициалами, а таблицу с отчетом - словом “ Summer2012 ” в сочетании со своей фамилией.

2. При помощи функции ВПР справа от столбца CodeDevice добавить колонку Device с названиями устройств, используя имя таблицы устройств.

3. Отсортировать отчет по 4-м ключам: Investment USD по убыванию, StartDate по убыванию (от новых к старым), Geo по алфавиту, Queries по убыванию.

4. На новом листе записать критерий для отбора информации по смартфонам и планшетам за второй квартал 2012 года. Имя листа “ V ”. На этом листе подсчитать количество строк отчета, для которых критерий выполнен.

5. На листе RAW DATA окрасить желтым цветом начальные даты (StartDate) строк отчета по смартфонам за второй квартал 2012 года.

6. На листе “ V ” рассчитать максимальное количество запросов (Queries) на игры для смартфонов и планшетов(Tablets) за второй квартал 2012 года при количестве кликов, превышающих 10.

7. Создать лист с именем “ V Вычисляемый критерий”. На этом листе подготовить вычисляемый критерий для отбора с листа RAW DATA данных отчета при условии, что цена за клик, рассчитанная по формуле Investment USD / Clicks, превышает 0,1

8. На этом листе подсчитать средний объем инвестиций в игры для смартфонов и планшетов, удовлетворяющих вычисляемому критерию.

9. Извлечь на лист с именем “ V ” данные, удовлетворяющие условиям п. 4. Поля Device, Category, Queries, Clicks, InvestmentUSD.

VI. Сводные таблицы.

1. По данным листа RAW DATA построить сводную таблицу (рис. 3) с числовой группировкой по полю Clicks и по полю StartDate для расчета среднего объема инвестиций за 2-ой квартал 2012 года.

Рис. 3.

2. Скопировать повторно лист RAW DATA в свой файл. Построить по данным этого листа сводную таблицу (рис. 4) с вычисляемым полем, рассчитывающим разность между показом банеров и кликами по банерам.

Рис. 4.

3. По сводной таблице на рис. 4 построить сводную диаграмму типа «Объемная гистограмма с группировкой» (рис. 5).