Как сделать эпюру пассажиропотока в экселе
Эксель можно использовать для решения широкого спектра задач, в том числе, для нахождения наилучшего способа осуществления перевозок от производителя (продавца) к потребителю (покупателю). Давайте посмотрим, каким образом это можно реализовать в программе.
Транспортная задача: описание
С помощью транспортной задачи можно найти наилучший вариант перевозки с минимальными издержками между двумя взаимодействующими контрагентами (в рамках данной статьи будем рассматривать покупателей и продавцов). Чтобы приступить к решению, нужно представить исходные данные в схематичном или матричном виде. Последний вариант применяется в Эксель.
Транспортные задачи бывают двух типов:
- Закрытая – совокупное предложение продавца равняется общему спросу.
- Открытая – спрос и предложение не равны. Чтобы решить такую задачу, нужно сначала привести ее к закрытому типу. В этом случае добавляется условный покупатель или продавец с недостающим количеством спроса или предложения. Также в таблицу издержек следует внести соответствующую запись (с нулевыми значениями).
Подготовительный этап: включение функции “Поиск решения”
Чтобы решить транспортную задачу в Эксель, нужно воспользоваться функцией “Поиск решения”, которую нужно предварительно активировать, т.к. изначально она не включена. Алгоритм действий следующий:
Пример задачи и ее решение
Чтобы лучше понять, как решать транспортные задачи в Excel, давайте рассмотрим конкретный практический пример.
Условия задачи
Допустим, у нас есть 6 продавцов и 7 покупателей. Предложение продавцов составляет 36, 51, 32, 44, 35 и 38 единиц. Спрос покупателей следующий: 33, 48, 30, 36, 33, 24 и 32 единицы. Суммарные количества по спросу и предложению равны, следовательно, это транспортная задача закрытого типа.
Также, мы имеем данные по издержкам перевозок из одного пункта в другой (ячейки с желтым фоном).
Алгоритм решения
Итак, приступи к решению нашей задачи:
- в поле для ввода значения напротив первого аргумента “Массив1” указываем координаты диапазона ячеек матрицы затрат (с желтым фоном). Сделать это можно, используя клавиши на клавиатуре, или просто выделив нужную область в самой таблице с помощью зажатой левой кнопки мыши.
- в качестве значения второго аргумента “Массив2” указываем диапазон ячеек новой таблицы (либо вручную, либо выделив нужные элементы на листе).
Заключение
Таким образом, с помощью программы Эксель достаточно просто решить транспортную задачу. Самое главное – правильно заполнить начальные данные и четко следовать плану действий, и тогда проблем быть не должно, т.к. программа все расчеты выполнит сама.
1. Настоящий стандарт устанавливает правила нанесения надписей, технических требований и таблиц на чертежи изделий всех отраслей промышленности.
2. Кроме изображения предмета с размерами, чертеж может содержать:
а) текстовую часть, состоящую из технических требований и (или) технических характеристик;
б) надписи с обозначением изображений, а также относящиеся к отдельным элементам изделия;
в) таблицы с размерами и другими параметрами, техническими требованиями, контрольными комплексами, условными обозначениями и т. д.
3. Выполнение основной надписи чертежа должно производиться в соответствии с требованиями ГОСТ 2.104-68 и ГОСТ 2.109-73.
4. Текстовую часть, надписи и таблицы включают в чертеж в тех случаях, когда содержащиеся в них данные, указания и разъяснения невозможно или нецелесообразно выразить графически или условными обозначениями.
5. Содержание текста и надписей должно быть кратким и точным. В надписях на чертежах не должно быть сокращений слов, за исключением общепринятых.
6. Текст на поле чертежа, таблицы, надписи с обозначением изображений, а также надписи, связанные непосредственно с изображением, как правило, располагают параллельно основной надписи чертежа.
… 11. Текстовую часть, помещенную на поле чертежа, располагают над основной надписью.
12… На чертеже изделия, для которого стандартом установлена таблица параметров, ее помещают по правилам, установленным соответствующим стандартом. Все другие таблицы размещают на свободном месте поля чертежа справа от изображения или ниже его и выполняют по ГОСТ 2.105-95.
Между текстовой частью и основной надписью не допускается помещать изображения, таблицы и т. п.
… 19. Размер шрифта буквенных обозначений должен быть больше размера цифр размерных чисел, применяемых на том же чертеже, приблизительно в два раза.
- Буквенные обозначения не подчеркивают.
Эпюра грузопотока.
Графически грузопотоки могут быть представлены в виде эпюр.
Эпюры строятся по видам грузов и направлениям (или общие -- только по направлениям, без разделения по видам грузов).
Эпюру чертят в двух координатах:
1- «Объем перевозки»
«Объем перевозки» Q, т, откладывается по оси ординат (вертикальная ось),
а «Расстояние перевозки» l, км, — по оси абсцисс (горизонтальная ось).
Эпюра строится в соответствии с выбранным масштабом (указать какой)
- Сплошной толстой линией чертят оси со стрелками, буквенно обозначают возле стрелки учитывающийся показатель, указывая через запятую единицы измерения : « Q ,т», « L ,км»;
– Откладывают на горизонтальной оси последовательно в определенном масштабе протяжённость участков трассы между пунктами, по которым осуществляются перевозки. Причём криволинейная форма трассы заменяется прямолинейной;
– Перпендикулярно к этим участкам откладывают величину « Q » с учетом расстояний перевозок и проводят на этом уровне горизонтальную линию (сплошную толстую).
- Величина « Q » в прямом направление откладывается вверх относительно оси, в обратном-вниз ( прямым направлением считается то, по которому следует наибольшее количество грузов ).
- В первую очередь изображают грузы, следующие в пункты получения, наиболее удаленные от пункта отправления, а затем - остальные.
- Полученное пространство между осевой и проведенной линией заштриховывается тонкими линиями. Штриховки, символизирующие разные грузы должна различаться. Эпюру следует сопроводить расшифровкой условных обозначений.
-Указывают стрелками прямое и обратное направления движения грузов, сопровождая соответствующей надписью над стрелкой.
- Выполняют необходимые надписи ( заголовок, числовые показатели, обозначение контрольных пунктов, информацию в табличной форме ит.д).
Если числовой показатель располагают на заштрихованном поле, то в этом месте штриховка прерывается.
- Заполняется основная надпись
Рис.1 Пример эпюры грузопотока
Эпюра пассажиропотока.
Пассажиропотоки аналогично изображаются в виде эпюр.
Эпюра строится в двух координатах:
1- «Объем перевозки пассажиров»
1- «Объем перевозки пассажиров»
2- «Часы суток / Дни»
«Объем перевозки» ( Q, чел.) откладывается по оси ординат (вертикальная ось),
а «Расстояние перевозки» ( L , км) или «Часы суток» ( t , часы ) - по оси абсцисс (горизонтальная ось).
Эпюра строится в соответствии с выбранным масштабом .
- Сплошной толстой линией чертят оси со стрелками,
- буквенно обозначают возле стрелки учитывающийся показатель, указывая через запятую единицы измерения: « Q ,т» , « L ,км» / « t , часы»
– откладывают на горизонтальной оси ( L ,км) последовательно в определенном масштабе расстояния между начальным, промежуточными и конечным пунктами (криволинейная форма маршрута заменяется прямолинейной).
–перпендикулярно каждому отрезку маршрута откладывают ( по вертикальной оси) величину « Q » -количество пассажиров и проводят горизонтальные линии (сплошной толстой линией) на уровнях, соответствующих расчётным показателям.
Величина « Q » в прямом направление откладывается вверх относительно оси, в обратном-вниз.
-Указывают стрелками прямое и обратное направление маршрута, сопровождая соответствующей надписью над стрелкой.
- Выполняют необходимые надписи ( заголовок, числовые показатели, обозначение контрольных пунктов, информацию в табличной форме ит.д), в соответствии с ГОСТ 2.316-68
В прошлой статье я описал использование когортного анализа для выяснения причин динамики клиентской базы. Сегодня пришло время поговорить про трюки подготовки данных для когортного анализа.
Легко рисовать картинки, но для того, чтобы они считались и отображались правильно “под капотом” нужно проделать немало работы. В этой статье мы поговорим о том, как реализовать когортный анализ. Я расскажу про реализацию при помощи Excel, а в другой статье при помощи R.
Хотим мы этого или нет, но по факту Excel это инструмент анализа данных. Более “высокомерные” аналитики будут считать, что это слабый и не удобный инструмент. С другой стороны по факту сотни тысяч людей делают анализ данных в Excel и в этом отношении он легко побьет R / python. Конечно, когда мы говорим о advances analytics и машинном обучении, мы будем работать на R / python. И я был бы за то, чтобы большая часть аналитики делалась именно этими инструментами. Но стоит признать факты, в Excel обрабатывают и представляют данные подавляющее большинство компаний и именно этим инструментом пользуются обычные аналитики, менеджеры и product owners. Вдобавок Excel трудно победить в части простоты и наглядности процесса, т.к. вы мастерите свои расчеты и модельки буквально руками.
И так, как же нам сделать когортный анализ в Excel? Для того, чтобы решать подобные задачи нужно определить 2 вещи:
Какие данные у нас в начале процесса
Как должны выглядеть наши данные в конце процесса.
Чтобы собрать когортный анализ нам не будет достаточно только оборотный данных по датам и подразделениям. Нам нужны данные на уровне отдельных клиентов. В начале процесса нам понадобится:
Дата регистрации клиента
Объем продаж этого клиента в эту календарную дату
Первая сложность, которую предстоит преодолеть — это получить эти данные. Если у вас правильное хранилище, то они уже должны быть у вас. С другой стороны, если пока реализовали только запись данных о совокупных продажах по дням, то данные по клиентам у вас есть только на “проде”. Для когортного анализа вам придется реализовать ETL и сложить в ваше хранилище данные в разрезе клиентов, иначе у вас ничего не выйдет. И лучше всего если вы разделите “прод” и аналитику в разные базы, т.к. У аналитических задач и задач функционирования вашего продукта разные цели конкуренция за ресурсы. Аналитикам нужны быстрые агрегаты и расчеты на по многим пользователям, продукту нужно быстро обслужить конкретного пользователя. Об организации хранилища я напишу отдельную статью.
Итак, вы имеете стартовые данные:
Первое, что нам нужно сделать это преобразовать их в “лесенки”. Для этого нужно над этой таблицей построить сводную таблицу, по строкам — дата регистрации, по столбцам — календарная дата, в качестве значений — кол-во id клиентов. Если вы верно извлекли данные, то у вас должен получится вот такой треугольник/лесенка:
В целом лесенка это наш когортный график, в котором каждая строка отображает динамику отдельной когорты. Клиенты во времени в этой отображении двигаются только внутри одной строки. Таким образом динамика когорты отображает развитие отношений с группой клиентов пришедший в один период времени. Часто для удобства и без потери качества, можно объединить когорты в “блоки” строк. Например, вы можете сгруппировать их по неделям и месяцам. Точно так же вы можете сгруппировать и колонку, т.к. Возможно ваш темп развития продукта не требует детализации до дней.
На основе этой лесенки вы можете влоб построить график из моей статьи (я правда указывал, что сгруппировал несколько строк в одну, чтобы когорт было поменьше):
Это график с накопительными областями, где каждый ряд — это строка, по горизонтали даты.
Чуть сложнее логика для реализации графика “потоков”. Для потоков мы должны сделать некоторые дополнительные вычисления. В логике потоков каждый клиент прибывает в различных состояниях:
- Новый — любой клиент, у кого разница между датой регистрации и календарной дате <7 дней
- Реактивированный — любой клиент, кто уже не новый, но в прошлом календарном месяце не генерировал выручку
- Действующий — любой клиент, кто не новый, но в в календарном месяце генерировал выручку
- Ушедший — любой клиент, кто не генерирует выручку 2 месяца подряд
Во-первых вам стоит в компании закрепить эти определения, чтобы вы могли корректно реализовать эту логику и автоматически рассчитывать состояния. Эти 4 определения имеют далеко идущие последствия в целом и для маркетинга. Ваши стратегии по привлечению, удержанию и возвращению будут базироваться на том, в каком состоянии вы считаете находится клиент. А если вы начнете внедрять модели машинного обучения в прогнозировании ухода клиентов, то определения станут вашим краеугольным камнем успешности этих моделей. Вообще про организацию работы и важность аналитической методологии я напишу отдельную статью. Выше я привел просто пример того, какими могут быть эти определения.
В Excel вам нужно создать дополнительную колонку, куда вписать описанную выше логику. В нашей случае нам придется “попотеть”. У нас есть 2 типа критериев:
- Разница между датой регистрацией и календарной датой — эти данные есть у каждой строки и тут просто нужно ее посчитать (вычитание дат в Excel просто дает разницу в днях)
- Данные о выручке в текущем и прошлом месяце. Эти данные нам не доступны в строке. Более того, с учетом того, что в нашей таблице не гарантирован порядок, то вы не можете точно сказать, где у вас данные по другим дням месяца для этого клиента.
Решить проблему 2 типа критериев можно 2 способами:
- Попросите сделать это в базе данных. SQL позволяет при помощи аналитической функции вычислить для каждого клиента сумму выручки за текущий и прошлый месяц (для текущего месяца SUM(revenue) OVER (PARTITION BY client_id, calendar_month, а потом LAG, чтобы получить смещение по прошлому месяцу):
- В экселе вам придется реализовать это так:
- Для текущего месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня
- Для прошлого месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня минус ровно 1 календарный месяц. При этом обращу внимание, что вы должны вычесть именно календарный месяц, а не 30 дней. Иначе вы рискуете получить смазанную картину из-за неодинакового числа дней в месяцах. Также используйте функцию ЕСЛИОШИБКА, чтобы заменить ошибочные значения для клиентов у кого не было прошлого месяца.
Добавив колонки выручки текущего месяца, прошлого месяца вы можете построить вложенное условие ЕСЛИ, учитывающие все факторы (разницу дат и суммы выручки в текущем/прошлом месяце):
ЕСЛИ( разница дат <7; “новый”;
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца > 0); “реактивация”;
ЕСЛИ( И (выручка прошлого месяца > 0; выручка текущего месяца > 0); “действующий”
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца = 0); “ушедший”; “ошибка”))))
У вас должно получится вот так:
Теперь эту таблицу можно пересобрать при помощи сводной таблицы в таблицу для построения графика. Вам нужно трансформировать ее в таблицу:
Календарная дата (колонки)
Состояние (строки)
Кол-во id клиентов (значения в ячейках)
Далее мы просто должны на основе данных построить диаграмму столбчатую диаграмма с накоплениями, по оси Х календарная дата, ряды это состояния, кол-во клиентов это высота столбцов. Вы можете поменять порядок состояний на графике, изменив порядок рядов в меню “выбрать данные”. В итоге мы получим такую картину:
Большую роль при организации движения пассажирского транспорта играет неравномерность распределения пассажиропотоков во времени и по отдельным участкам действующих маршрутов. Поэтому для формирования рациональной маршрутной сети, равно как и для эффективного использования подвижного состава и обеспечения высокого уровня обслуживания пассажиров, необходимо знать направления, размеры и степень неравномерности пассажиропотоков.
Пассажиропотоки характеризуют нагрузку транспортной сети по направлениям перемещений в определенный период времени (час, сутки, месяц). Как было отмечено раньше, пассажиропотоки схематически изображаются в виде эпюр и определяют напряженность маршрута, участка дороги, линии. Характер изменения пассажиропотоков по часам суток, дням недели, представлен на рисунке 9.3.
Изменение пассажиропотока по часам суток и дням недели
В общем виде неравномерность пассажиропотока определяется как отношение максимальной мощности пассажиропотока Qmax за определенный период времени к средней мощности пассажиропотока Qcр за тот же период:
Неравномерность пассажиропотоков по часам суток, а также по участкам маршрута и направлениям движения оценивают при помощи соответствующих коэффициентов.
Неравномерность пассажиропотока по часам суток
Qпик и Qcр.с. _ соответственно максимальная мощность пассажиропотока в час- пик и среднечасовая мощность в течение суток.
Неравномерность пассажиропотока по дням недели
Qдн и Qср.дн. - соответственно максимальный пассажиропоток за один из дней недели и среднедневной пассажиропоток за неделю.
Неравномерность пассажиропотока по направлениям
Qнап - среднечасовая мощность пассажиропотока за день в наиболее загруженном направлении
Qнап-пр. _ среднечасовая мощность пассажиропотока в противоположном направлении.
Значения коэффициентов неравномерности для крупных городов России находится в пределах:
Для выявления пассажиропотоков и характера их распределения по направлениям, сбора данных об изменениях пассажиропотоков во времени, участкам и направлениям движения проводят обследования пассажиропотоков.
Читайте также: