Вопрос или проблема
У меня есть таблица с примерно 4000 записей. Для каждой записи у меня есть идентификатор, время начала и время окончания. Данные представляют собой расписание поездов на определенный день.
Я хотел бы получить подсчет работающих поездов по минутам и визуализировать это с помощью графика, чтобы легко увидеть, в какое время дня самая большая нагрузка и как выглядит пик работающих поездов.
Я натворил дел. У меня 1440 столбцов, по одному для каждой минуты в 24-часовом периоде, и у меня более 4000 строк. Для всех пересечений у меня есть формула, подобная следующей.
=IF(AND(данная_минута >= время_начала, данная_минута <= время_окончания), 1, 0)
Где данная_минута - это значение столбца минут, а время_начала и время_окончания - это значения строки.
Мне не стоит удивляться такому излишеству в Excel, у меня на одном листе 1,440 x 4,000 = 5.7M формул!
Я получил то, что мне нужно (едва ли), но мне хотелось бы, чтобы кто-то поделился более оптимальным способом сделать это, я хотел бы научиться менее грубому методу!
Спасибо
Вам на самом деле нужна только одна формула для каждого минутного интервала.
- Создайте список минутных интервалов
- Назовите свои столбцы Min и Max, чтобы упростить формулы
- Рядом с каждым временным интервалом введите следующую формулу
=COUNTIFS(MinRange,"<="&F2,MaxRange,">="&F2)
Пример:
Ответ или решение
Создание временной шкалы данных в Excel для анализа движения поездов
Введение
Ваша задача заключается в анализе данных, связанных с курсированием поездов, представленных в виде таблицы с 4000 записями. Каждая запись содержит уникальный идентификатор их курсирования, а также время отправления и прибытия. Вам необходимо создать минутный учет текущих поездов и визуализировать их на графике для более наглядного понимания загруженности в течение дня.
Анализ текущего подхода
На данный момент у вас есть 1440 колонок, соответствующих каждой минуте суток, и при этом вы используете формулы для вычисления активных поездов в каждой из них. С каждой формулой, состоящей из IF и AND, получается 5.7 миллиона формул, что приводит к снижению производительности и затруднениям с управлением данными.
Оптимизация подхода
Чтобы упростить процесс и улучшить производительность, рекомендуется следующий метод:
Шаг 1: Создание списка минутных интервалов
Создайте отдельный столбец, который будет содержать каждую минуту с 00:00 до 23:59. Это может быть сделано с помощью формулы, начиная с 00:00 в ячейке, и протягивая эту формулу вниз до 1440 минут (или 24 часов).
Шаг 2: Переименование колонок
Обозначьте ваши колонки с временем начала и конца для упрощения формул. Например, назовите их MinRange и MaxRange. Это позволит вам избежать путаницы и упрощает написание формул.
Шаг 3: Использование формулы COUNTIFS
Вместо объема формул, которые вы уже использовали, можно воспользоваться формулой COUNTIFS. Эта формула позволяет подсчитывать количество записей, соответствующих заданным критериям, что значительно увеличивает эффективность. В ячейке рядом с каждой минутной отметкой введите следующую формулу:
=COUNTIFS(MinRange, "<=" & F2, MaxRange, ">=" & F2)Где F2 - это ячейка с текущей минутой. Эта формула подсчитает количество поездов, которые находятся в пути в течение данной минуты.
Шаг 4: Визуализация данных
Когда вы получите минутные данные о количестве поездов, создайте график, чтобы визуализировать информацию. Выделите данные, после чего выберите «Вставить» > «Графики», чтобы построить линейный график. График позволит вам наглядно увидеть пики загрузки в различное время суток.
Заключение
Применение предложенных методик не только значительно уменьшит количество формул в вашем Excel файле, но и повысит его производительность. Упрощенный подход по использованию формулы COUNTIFS и визуализация через график помогут получить ценные инсайты о движении поездов, а также выделить пиковые часы нагрузки. Такой метод является более эффективным и лучшим вариантом для выполнения подобной задачи в будущем.
Если у вас остались вопросы или требуется дополнительная помощь в оптимизации работы с данными, не стесняйтесь обращаться.