Лабораторные работы. Лабораторная работа 1. Изготовление визитной карточки в редакторе Word

Название	Лабораторная работа 1. Изготовление визитной карточки в редакторе Word
Дата	02.02.2023
Размер	3.25 Mb.
Формат файла
Имя файла	Лабораторные работы.docx
Тип	Лабораторная работа #916869
страница	8 из 16

1 ... 4 5 6 7 8 9 10 11 ... 16

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

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

Какой тариф мобильной связи наболее выгодный? Выбор, однако, не прост. У каждого из операторов "большой тройки" десятки активных тарифов: лимитные, безлимитные, пред- и постоплатные, с абонентской платой и без. И к каждому из них можно добавить дополнительные услуги в виде пакетов 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.

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

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

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

Лабораторная работа № 9. Оптимизация бизнес-модели

Работа с дополнением Пакет анализа Подбор параметра.

Цель занятия: изучение технологии подбора параметра при обратных расчетах.

Подбор параметра — это способ прогнозирования значения с помощью анализа «что – если»

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

С помощью этого средства Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки не возвращает нужный результат

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

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

Использование операции Подбор параметра в MSExcel позволяет производить обратный расчет. Задается конкретное значение рассчитываемого параметра. По этому значению подбирается некоторое, удовлетворяющее заданным условиям, значение исходного параметра расчета. Изменение исходных данных приводит к изменению результатов расчетов.
Задание 1. Используя режим подбора параметра, определить штатное расписания фирмы. Исходные данные приведены на рис. 1

Известно, что в штате фирмы состоит:

6 курьеров;
8 младших менеджеров;
10 менеджеров;

3 заведующих отделами;
1 главный бухгалтер;
1 программист;
1 системный аналитик;
1 генеральный директор фирмы.

Рис. 1. Исходные данные для Задания

Общий месячный фонд зарплаты составляет 100000 р. Необходимо определить, какими должны быть оклады сотрудников фирмы.

Каждый оклад является линейной функцией от оклада :

зарплата_i = А_i*х + В_i

где х — оклад курьера; А_i и В_i, -коэффициенты, показывающие:

А_i— во сколько раз превышается значение х;

В_i— на сколько превышается значение х.

Действия:

Запустить редактор электронных таблиц Microsoft Excel.

Открыть файл штатного расписания (штатное_расп.xls).
Присвоить имя рабочему листу Подбор параметра
Ввести в исходную таблицу данные и формулы.
Выделить отдельную ячейку D3 для зарплаты курьера (переменная х).

Все расчеты задать с учетом оплаты курьера. В ячейкуD3временно ввести произвольное число (например, 900 р.).

В столбце D ввести формулу для расчета заработной платы по каждой должности. Для ячейки D6 формула расчета имеет вид: = В6 * $D$3 + С6 (ячейка D3 задана в виде абсолютной адресации).
Скопировать формулу по столбцу D автокопированием.
В столбце F рассчитать заработную плату всех работающих в данной должности. и скопировать формулу по столбцу F автокопированием.
В ячейке F14 автосуммированием вычислить фонд заработной платы фирмы.
Произвести подбор зарплат сотрудников фирмы для фонда для фонда заработной платы, равной 100000 р.
Установить курсор в ячейкуF14
Активизировать команду пункта меню СервисПодбор параметра.
В окне Подбор параметра выполнить:

в поле Установить в ячейке окна введите ссылку на ячейку F14 (с формулой расчета фонда заработной платы);
в поле Значение набрать искомый результат 100000;
в поле Изменяя значение ячейки ввести ссылку на изменяемую ячейку D3 (с значением зарплаты курьера);
ОК.

Сохранить изменения в электронной книге в своей папке.
Записать подобранное значение заработной платы курьера.
Распечатать результат в режиме отображения формул.

Анализ задач по заданию 2, 3 показывает, что с помощью MSExcel можно решать линейные уравнения.

Поиск значения параметра формулы — это не что иное, как численное решение уравнений. Другими словами, используя возможности программы MS Excel, можно решать любые уравнения с одной переменной.

Работа с дополнением Пакет анализа. Поиск решения

Цель занятия. Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).

Задание 2. Выполнить минимизацию фонда заработной платы фирмы.

Известно, что для нормальной работы фирмы требуется от 5 до 7 курьеров, от 8 до 10 младших менеджеров, 10 менеджеров, 3 заведующих отделами, 1 главный бухгалтер, 1 программист, 1 системный аналитик, генеральный директор фирмы.

Общий месячный фонд зарплаты должен быть минимален. Необходимо определить, какими должны быть оклады сотрудников фирмы, при условии, что оклад курьера не должен быть меньше 1400 р.

В качестве модели решения этой задачи применима линейная модель. Условие задачи имеет вид:

N₁*А₁*х + N₂ * (А₂*х + В₂) + . . . + N₈*(А₈*х + В₈) = Минимум фонда зарплаты,

Где: Ni — количество работников данной специальности;

х — зарплата курьера;

А, и В, — коэффициенты заработной платы сотрудников фирмы.
Действия:

Запустить ЭТ MS Excel.
Открыть, сохраненный в задании 3, файл штатное_расп.xls
Выполнить копию листа Подбор параметра
Присвоить копии листа имя Поиск решения
Установить курсор в ячейкуF14
Активизировать команду Поиск решения пункта меню Сервис
В окне Поиск решения(рис. 2) выполнить:

в поле Установить целевую ячейку указать ячейку F14, (содержащую модель — суммарный фонд заработной платы)
активизировать переключатель равной — Минимальному значению (необходимо минимизировать общий месячный фонд зарплаты)
в поле Изменяя ячейки указать адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера — $E$6;$E$7;$D$3 (при задании ячеек Е6, Е7 и D3 следует удерживать клавишу Ctrl).
в поле Ограничения, используя кнопку Добавить (открывается окно Добавление ограничений – см. рис. 6.6) описать все ограничения:

$D$3 > = 1400		(зарплата курьера не менее 1400)
$Е$6 > = 5	  	(количество курьеров изменяется от 5 до 7);
$Е$6 < = 7
$Е$7 > = 8 $Е$7 < = 10	  	(количество младших менеджеров от 8 до 10);

Рис. 2 Окно Поиск решения с заданными условиями для минимизации фонда заработной платы

Рис. 3 Окно Добавление ограничений для описания условия: зарплата курьера не менее 1400 р.
После ввода одного условия в окне Добавление ограничений следует активизировать кнопку Добавить.

Завершение ввода условий ограничений и переход в окно Поиск решения осуществляется нажатием кнопки ОК.

8. Ввести параметры поиска, активизировав кнопку Параметры. Параметры установить согласно рис. 4.

Рис. 4 Задание параметров поиска решения по минимизации фонда зарплаты.

Запустить процесс поиска решения нажатием кнопки Выполнить.
В открывшемся диалоговом окне Результаты поиска решения установить переключатель Сохранить найденное решение (рис. 5).

Рис. 5. Сохранение найденного при поиске решения

Решение задачи приведено на рис. 6.. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.

Рис. 6. Минимизация

Оптимизационные задачи

Предположим, что компания, где вы работаете, имеет два складских помещения, откуда товар поступает в пять ваших магазинов, разбросанных по всей Москве.

Каждый магазин в состоянии реализовать определенное, известное нам количество товара. Каждый из складов имеет ограниченную вместимость. Задача состоит в том, чтобы рационально выбрать – с какого склада в какие магазины нужно доставлять товар, чтобы минимизировать общие транспортные расходы.

Перед началом оптимизации необходимо будет составить несложную таблицу на листе Excel – нашу математическую модель, описывающую ситуацию:

Подразумевается, что:

Серая таблица (B3:G5) описывает стоимость доставки единицы от каждого склада до каждого магазина.
Лиловые ячейки (C14:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
Красные ячейки (J10:J11) отображают емкость каждого склада – предельное количество товара, которое склад может вместить.
Желтые (C12:G12) и синие (H10:H11) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
Общая стоимость доставки (E17) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки.

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

Решение

В математике подобные задачи выбора оптимального распределения ресурсов сформулированы и описаны уже давно. И, конечно же, давно разработаны способы их решения. Excel предоставляет пользователю один из них – с помощью мощной надстройки Поиск решения(Solver) , доступной в Excel 2003 через в меню Сервис(Tools) или с вкладки Данные(Data) в новых версиях Excel.

Если в меню Сервис или на вкладке Данные вашего Excel такой команды нет – ничего страшного - значит надстройка просто еще не подключена. Для ее подключения:

в Excel 2003 и старше - откройте меню Сервис – Надстройки(Tools–Add-Ins), в появившемся окне установите флажок Поиск решения(Solver) и нажмите ОК. Excel активирует выбранную надстройку и в меню Сервис (Tools) появится новая команда – Поиск решения (Solver).
в Excel 2007 и новее - нажать кнопку Офис, далее выбрать ПараметрыExcel – Надстройки – Перейти(Excel Options - Add-Ins - Go To).

Запустим надстройку. Откроется вот такое окно:

В этом окне нужно задать следующие настройки:

Целевая ячейка(Targetcell) – тут необходимо указать конечную главную цель нашей оптимизации, т.е. розовую ячейку с общей стоимостью доставки (E17). Целевую ячейку можно минимизировать (если это расходы, как в нашем случае), максимизировать (если это, например, прибыль) или попытаться привести к заданной константе.
Изменяемые ячейки(Bychangingcells) – здесь укажем зеленые ячейки (C10:G11), варьируя значения которых мы хотим добиться нашего результата – минимальных затрат на доставку.
Ограничения(SubjecttotheConstraints) – список ограничений, которые надо учитывать при проведении оптимизации. В нашем случае это ограничения на вместимость складов и потребности магазинов. Для добавления ограничений в список нужно нажать кнопку Добавить(Add) и ввести условие в появившееся окно:

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

Оно дополнительно уточнит, что объем перевозимого товара (зеленые ячейки) не может быть отрицательным – для человека такое само собой очевидно, но для компьютера это надо прописать явно.

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

Теперь, когда данные для расчета введены, нажмем кнопку Выполнить(Solve), чтобы начать оптимизацию. В тяжелых случаях с большим количеством изменяемых ячеек и ограничений нахождение решения может занять продолжительное время, но наша задача для Excel проблемы не составит – через пару мгновений мы получим следующие результаты:

Обратите внимание на то, как интересно распределились объемы поставок по магазинам, не превысив при этом емкости наших складов и удовлетворив все запросы по требуемому количеству товаров для каждого магазина.

Если найденное решение нам подходит, то можно его сохранить, либо откатиться назад к исходным значениям и попробовать еще раз с другими параметрами. Также можно сохранить подобранную комбинацию параметров как Сценарий. По желанию пользователя Excel может построить три типаОтчетов по решаемой задаче на отдельных листах: отчет по результатам, отчет по математической устойчивости решения и отчет по пределам (ограничениям) решения, однако они, в большинстве случаев, интересны только специалистам.

Бывают, однако, ситуации, когда Excel не может найти подходящего решения. Имитировать такой случай можно, если указать в нашем примере требования магазинов в сумме большие, чем общая вместимость складов. Тогда при выполнении оптимизации Excel попытается приблизиться к решению, насколько это возможно, а затем выдаст сообщение о невозможности найти решение. Тем не менее, даже в этом случае мы имеем массу полезной информации – в частности можем видеть «слабые звенья» наших бизнес-процессов и понять направления совершенствования.

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

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

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

Лабораторная работа № 10. Разработка инфологической модели и создание структуры реляционной базы данных