Файл: Лабораторные работы.docx

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

Отступом от начала таблицы вниз на заданное количество строк, т.е. отступом по временной шкале прошлое-будущее (синяя ячейка)
Количеством ячеек по высоте, т.е. масштабом (зеленая ячейка)

Этот именованный диапазон мы позже будем использовать как исходные данные для построения диаграммы.

Для создания такого диапазона будем использовать функцию СМЕЩ (OFFSET) из категории Ссылки и массивы (Lookup and Reference) - эта функция умеет создавать ссылку на диапазон заданного размера в заданном месте листа и имеет следующие аргументы:

В качестве точки отсчета берется некая стартовая ячейка, затем задается смещение относительно нее на заданное количество строк вниз и столбцов вправо. Последние два аргумента этой функции – высота и ширина нужного нам диапазона. Так, например, если бы мы хотели иметь ссылку на диапазон данных с курсами за 5 дней, начиная с 4 января, то можно было бы использовать нашу функцию СМЕЩ со следующими аргументами:

=СМЕЩ(A3;4;1;5;2)

Хитрость в том, что константы в этой формуле можно заменить на ссылки на ячейки с переменным содержимым – в нашем случае, на синюю и зеленую ячейки. Сделать это можно, создав динамический именованный диапазон с функцией СМЕЩ (OFFSET). Для этого:

В Excel 2007/2010 нажмите кнопку Диспетчер имен (Name Manager) на вкладке Формулы (Formulas)
В Excel 2003 и старше – выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define)

Для создания нового именованного диапазона нужно нажать кнопку Создать (Create) и ввести имя диапазона и ссылку на ячейки в открывшемся окне.

Сначала создадим два простых статических именованных диапазона с именами, например, Shift и Zoom, которые будут ссылаться на синюю и зеленую ячейки соответственно:

Теперь чуть сложнее – создадим диапазон с именем Euros, который будет ссылаться с помощью функции СМЕЩ (OFFSET) на данные по курсам евро за выбранный отрезок времени, используя только что созданные до этого диапазоны Shift и Zoom и ячейку E3 в качестве точки отсчета:

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

Аналогичным образом создается именованный диапазон Dollars для данных по курсу доллара:

И завершает картину диапазон Labels, указывающий на подписи к оси Х, т.е. даты для выбранного отрезка:

Общая получившаяся картина должна быть примерно следующей:

Шаг 6. Строим диаграмму

Выделим несколько строк в верхней части вспомогательной таблицы, например диапазон E3:G10 и построим по нему диаграмму типа График (Line). Для этого в Excel 2007/2010 нужно перейти на вкладку Вставка (Insert) и в группе Диаграмма (Chart) выбрать тип График (Line), а в более старших версиях выбрать в меню Вставка – Диаграмма (Insert – Chart). Если выделить одну из линий на созданной диаграмме, то в строке формул будет видна функция РЯД (SERIES), обслуживающая выделенный ряд данных:

Эта функция задает диапазоны данных и подписей для выделенного ряда диаграммы. Наша задача – подменить статические диапазоны в ее аргументах на динамические, созданные нами ранее. Это можно сделать прямо в строке формул, изменив

=РЯД(Лист1!$F$3;Лист1!$E$4:$E$10;Лист1!$F$4:$F$10;1)

на

=РЯД(Лист1!$F$3;Лист1!Labels;Лист1!Euros;1)

Выполнив эту процедуру последовательно для рядов данных доллара и евро, мы получим то, к чему стремились – диаграмма будет строиться по динамическим диапазонам Dollars и Euros, а подписи к оси Х будут браться из динамического же диапазона Labels. При изменении положения ползунков будут меняться диапазоны и, как следствие, диаграмма. При включении-выключении флажков – отображаться только те валюты, которые нам нужны.

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

Ссылки по теме

Умные таблицы Excel 2007/2010

Задание. Составить инструкцию - отчет по созданию деловой графики в виде презентации или с помощью Camtasia Studio, затем конвертирую в анимированный gif.

Лабораторная работа №8. Обработка данных.

Анализ детализации мобильной связи (Билайн)

Постановка задачи для принятия решения.

Какой тариф мобильной связи наболее выгодный? Выбор, однако, не прост. У каждого из операторов "большой тройки" десятки активных тарифов: лимитные, безлимитные, пред- и постоплатные, с абонентской платой и без. И к каждому из них можно добавить дополнительные услуги в виде пакетов SMS и интернет-траффика, подключения "любимых номеров", внутрисетевого и международного роуминга и т.д.

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

сколько минут входящих и исходящих звонков вы совершаете
на телефоны каких операторов вы чаще всего звоните
есть ли номера, на которые вы звоните заметно чаще других (их можно сделать "любимыми")
какую долю составляют звонки во внутрисетевом и международном роуминге
сколько Мб мобильного интернет-траффика вы тратите

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

Все дальнейшие действия будут показаны на примере "Билайна". Для других операторов картина аналогичная, но может отличаться деталями. Телефоны во всех скриншотах и видео изменены, любые совпадения - случайны :)

Шаг 1. Выгружаем детализацию

Идем на www.beeline.ru в Личный Кабинет - Финансовая информация и просим создать детализацию за последний, например, месяц.

Скачиваем детализацию (или она приходит вам по почте) и открываем ее в Microsoft Excel. И вот сюда, как видно, нормальные люди с прямыми руками еще не добрались:

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

непонятная многострочная шапка (Excel понимает только однострочные, без пустых и объединенных ячеек)
длительность звонка и объем потраченного интернет-траффика смешаны в одном столбце и этот столбец содержит не время в формате Excel, а текст, т.е. нельзя посчитать суммарное количество минут входящих и исходящих звонков и суммарный траффик
аналогично, три последних столбца с данными по балансу на самом деле содержат не числа, а текст (стоит текстовый формат ячеек, т.е. нельзя посчитать суммарные расходы)

Для окончательного превращения КАМАЗа в истребитель придется-таки "после сборки доработать напильником".

Шаг 2. Доработка детализации

Во-первых, убираем все лишние строки в верхней части листа, оставляя однострочную шапку:

Во-вторых, превратим псевдочисла в трех последних колонках в нормальные числа, с которыми можно работать. Для этого выделяем все данные в столбцах с балансом и жмем на всплывающий значок с восклицательным знаком - Преобразовать в число (Convert to number):

В-третьих, добавим справа от таблицы столбец с формулой

=ЕСЛИОШИБКА(ВРЕМЗНАЧ(D2);0)

=IFERROR(TIMEVALUE(D2);0)

которая будет превращать длительность разговора в текстовом виде из столбца D в нормальный формат времени, который понимает Excel. В случае возникновения ошибки преобразования (например, когда в ячейке вместо времени стоит объем траффика), формула будет выдавать ноль.

В-четвертых, выдернем в еще один дополнительный столбец из номера абонента три цифры кода мобильного оператора или города-области с помощью формулы:

=ЕСЛИ(C2="Internet'";0;ПСТР(C2;2;3))

=IF(C2="Internet";0;MID(C2;2;3))

Если в ячейке слово "Internet", то функция ЕСЛИ выдаст ноль. В противном случае текстовая функция ПСТР выдернет из номера три цифры, начиная со второй.

В-пятых, добавим столбец, куда будет выводится название оператора или города, куда мы звонили. Для этого придется использовать небольшой самодельный справочник по кодам на отдельном листе вот такого вида:

Чтобы подтянуть из него названия операторов и городов по кодам, придется использовать функцию ВПР (VLOOKUP) в отдельном столбце:

=ЕСЛИОШИБКА(ВПР(J2;Лист5!A:B;2;0);0)

=IFERROR(VLOOKUP(J2;Справочник!A:B;2;0);0)

Функция ЕСЛИОШИБКА (IFERROR) нужна, чтобы перехватить ошибку #Н/Д и заменить ее на ноль.

В итоге, после обработки наша таблица должна выглядеть примерно так:

С подготовительной частью все, можем переходить к отчетам.

Шаг 3. Создание отчетов

Для отчетов проще и удобнее всего будет использовать один из самых мощных и красивых инструментов Microsoft Excel - сводные таблицы. Ставим активную ячейку в нашу таблицу с данными и идем на вкладку Вставка - Сводная таблица (Insert - Pivot Table). В следующем окне жмем ОК и формируем отчет сводной таблицы, перетаскивая названия столбцов (поля) в одну из четырех областей отчета (названия строк, названия столбцов, значения или фильтр отчета):

Отчет 1. Общие расходы по типу

Забрасываем мышью поле Сервис в область Строки (Row Labels), а поле Изменение баланса в область Значения (Values). Получаем суммарные расходы по типам:

Из такого отчета понятно сколько денег тратится в общем и на что. На моем тарифном плане входящие и мобильный интернет бесплатны, но у вас картина может быть совсем другой (и весьма неожиданной, кстати). Также по такому отчету хорошо видны расходы на всякие непонятные услуги, которые вы когда-то подключили и - забыли. И возможно найдутся те, которые вы точно не подключали, но они у вас почему-то есть (сюрприз!) Выглядеть они будут, скорее всего, как непонятные аббревиатуры, которые надо обязательно гуглить и проверять. Я недавно нашел у себя парочку таких, кушающих по нескольку рублей в день и успешно их отключил.

Отчет 2. Тайминг входящих и исходящих звонков

Забрасываем в сводной таблице поля Сервис и Место в область Строки, а поля Изменение баланса и Время в область Значения. Щелкаем правой кнопкой мыши по значениям поля Время и выбираем Итоги по - Сумма (Summarize By - Sum). Также для столбца с временем можно задать пользовательский формат [мм]:сс, при котором минуты не обнуляются при превышении 60, а накапливаются. Получим:

Для наглядности дополнительно можно:

Отсортировать таблицу, т.е. встать в любую ячейку столбца С и выбрать Данные - Сортировать по возрастанию/убыванию (Data - Sort Ascending/Descending)
Применить к числам гистограммы на вкладке Главная - Условное форматирование - Гистограммы (Home - Conditional Formatting - Data Bars).
Отфильтровать лишние сервисы и услуги с помощью фильтра в А3.

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