Контекст
Мне нужно взять несколько действий с желаемыми датами начала и окончания и проверить, являются ли они разумными, учитывая ограничение, что только 3 (например) могут быть выполнены одновременно. Поскольку никогда не может быть выполнено больше 3, в моем расписании мне нужно учесть задержки действий. Хотя я знаю, что это можно сделать в MS Project, мне нужно, чтобы это было выполнено конкретно в Excel.
Проблема
Мне удалось сформулировать это в Excel, и это работает абсолютно так, как и предполагалось, для небольшого количества задач в течение небольшого периода (примерно до 30 000 ячеек). Однако мне нужно применить это к гораздо большему количеству действий в течение гораздо более длительного периода (потенциально до 500 000 ячеек в общей сложности — что в настоящее время занимает около 10 минут!).Можете ли вы порекомендовать какие-либо улучшения моей формулы?Я уже провел большую работу по оптимизации и рассмотрел несколько других вариантов (см. ниже):
Мой текущий метод
Скриншот текущего метода с индикативным профилем
Обратите внимание, что мой метод основан на присвоении видам деятельности приоритетного номера и их упорядочивании в соответствии с ним (это будет просто самая ранняя «стартовая» продолжительность).
Объяснение формулы для создания профиля/диаграммы Ганта:
- Если это первая строка ($B9 = 1) на диаграмме Ганта, не думайте слишком много, поскольку перед вами нет других действий: просто поставьте 1 в каждой из ячеек, если вы находитесь в требуемых датах (IF(AND(AO$3>=$C9,AO$3<=$D9),1,"")).
- Для других строк... Формула проверяет, (A) находитесь ли вы в правильном диапазоне дат, (B) выполняются ли уже 3 вида деятельности выше вашего, (C) поставили ли вы уже достаточно единиц в этом виде деятельности.
(Я использую таблицы Excel, где мне нужно, чтобы формула была единообразной в каждой строке, поэтому у меня 1-й и 2-й пункты в одной и той же формуле)
Текущие попытки улучшения:
- Вместо суммирования всего диапазона я попробовал задать SUM(OFFSET(...)), чтобы суммировать меньшее количество ячеек. Однако после попытки это привело к тому, что формула просто пропускала некоторые действия, и, следовательно, некоторые действия начинались, несмотря на ограничение, или продолжались дольше предполагаемой продолжительности.
- Вместо того, чтобы делать это на уровне день за днем, я попробовал делать это еженедельно/ежемесячно. Хотя этот уровень детализации, безусловно, ускорял расчет, он не давал точных результатов, поэтому мне нужно придерживаться ежедневного уровня.
TL:DR:
Есть ли способ еще больше оптимизировать =IF($B10=1, IF(AND(AO$3>=$C10,AO$3<=$D10),1,""), IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")). Спасибо.
решение1
У вас есть огромное количество ячеек для работы. Я бы использовал вспомогательные столбцы для расчета фактических дат начала и окончания и значительно упростил бы расчеты в правой части.
- Формула для фактического старта:
=IF(COUNTIF($F$1:F1,">="&B2)<$C$11,B2,LARGE($F$1:F1,$C$11)+1) - формула для фактического конца:
=E2+D2-1
Теперь в календарной части вы можете использовать такую формулу:=IF(AND(X$1>=$E2,X$1<=$F2),1,"")
Обновлять
Как работает формула:
COUNTIF($F$1:F1,">="&B2)- подсчитывает предыдущие действия, завершающиеся после желаемого началаCOUNTIF(... )<$C$11- сравнивает его с ограничением=IF(.... ,B2,...- сохранить желаемое начало возможного=IF(... ,... ,LARGE($F$1:F1,$C$11)+1)- если нужно отложить, проверьте, какое занятие заканчивается раньше из тех, что ограничивают ваш старт, +1 к началу на следующий день
решение2
Вы бы выиграли немного процессорного времени, удалив изолированный случай строки 1, поскольку постоянное сравнение не требуется для 99,9% строк. Просто вставьте одну пустую строку сразу под заголовками и отбросьте первый IF:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")
Более того, похоже, что AO$3<=$D10и SUM($F10:AN10)<$E10выполняет то же самое сравнение конечной даты. Можно также придерживаться быстрого одиночного сравнения без суммы:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,AO$3<=$D10,1,"")
Затем определите именованное «ограничение» диапазона как константу (относится к =3) для того, чтобы избежать поиска в ячейке:
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<constraint,AO$3<=$D10,1,"")
Далее выберите вложенные ЕСЛИ вместо И, так что Excel не придется каждый раз вычислять ресурсоемкую "СУММУ" ЦП, когда условия окна уже ЛОЖЬ. Расположите ЕСЛИ в порядке наибольшей вероятности ложности:
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(SUM(AO$4:AO9)<constraint,1,""),""),"")
И наконец замените СУММУ на СЧЕТЧИК
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,1,""),""),"")
Потому что внешность тоже имеет значение:
Как мы уже обсуждали, условное форматирование не рассматривается. Однако вы можете использовать символы ascii, например, "█" вместо 1:
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(ROW(AO9)-ROW(AO$4)+1-COUNTBLANK(AO$4:AO9)<constraint,1,"█"),""),"")
Но это происходит за счет более сложного подсчета количества предыдущих "█". Для более быстрой альтернативы используйте число 4, отформатированное шрифтом Webdings (выглядит почти так ►):
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,4,""),""),"")
Используйте любое число от 0 до 9 и измените шрифт, чтобы получить более наглядный эффект "столбиковой диаграммы". Это должно быть число, иначе COUNT не будет работать, и вам придется прибегнуть к более медленной формуле COUNTBLANK типа "█".
решение3
Всего пара простых вещей должны дать большой прирост скорости. Главное — это переупорядочение того, как вы анализируете ячейку.
Excel вычисляет, насколько это возможно, слева направо, кусок за куском, отклоняясь только тогда, когда логика формулы заставляет это делать. Одно отклонение заключается в том, что если он выполняет тест, он переходит прямо к последующему результату, ЕСЛИ это возможно.
Таким образом, если у вас есть IF()проверка в качестве первой части длинной формулы, а результат проверки имеет простой результат, она никогда не оценивает другие ветки.You have such a thing that would cut away almost all of the calculating you are doing.
Это AO$4:AO4проверка на ограничение. Если этот расчет не пройден, то вы получите немедленный вывод, больше никакой обработки. Он просто заканчивается для этой ячейки. Поэтому измените порядок формул, чтобы проверить ее в первую очередь. Таким образом, только три строки увидят дальнейшие вычисления, чем эта проверка, а не каждая строка.
(Говоря о «первом»: как Mobusговорится, прекратите выполнять вычисления «первой строки» в каждой ячейке. Используйте диапазон, который я показал выше AO$4:AO4(очевидно, разный для каждого столбца), и работайте с первой строкой так же, как с любой другой строкой. Тогда она никогда не будет частью вычислений в строках, отличных от нее самой. Диапазон закрепляется и расширяется так же, как вы делаете сейчас, и вы просто работаете со «строкой 1», как с любой другой строкой. Оптимизация для ее уникальности НЕ помогает в этой проблеме.)
Далее выполните расчет SUM()или COUNT()для строки, проверяющей существующее завершение. Похоже, у вас будет достаточно строк, чтобы почти все строки существовали в завершенном состоянии, поэтому если вы сначала проверите, делает ли дата ее интересной, вам все равно придется проверить эти. Сделайте это в первую очередь и сократите даты для проверки до очень немногих. Опять же, этот расчет будет выполняться в любом случае, перемещение не принесет ни выигрыша, ни проигрыша по отношению к нему, но выполнение этого в первую очередь отсекает множество других расчетов.
В других ответах и/или комментариях к ним упоминается идея именованного диапазона. Я полностью за именованные диапазоны и вспомогательные столбцы (даже вспомогательные страницы), но ваше ограничение, существующее в ячейке вместо именованного диапазона, просто не имеет значения... если вы переместите его в место, где оно не будет менять положение все время. Excel создает схему вычислений и после первого вычисления запускает вычисление только для ветвей того, в котором что-то меняется. Поместите ограничение в неизменное место и не редактируйте его... Excel никогда не придется тратить время на повторный расчет и т. д. Поместите его под областью данных и заставьте его менять строки каждый раз, когда вы вставляете новую задачу, и он будет. Поместите его где-нибудь, где не будет такого постоянного изменения, и ему не придется его пересчитывать. Именованный диапазон — это простой и замечательный способ сделать это, но вы можете просто переупорядочить этот аспект вашей электронной таблицы, если захотите, и вы можете, если захотите изменить его время от времени, или больше, можете захотеть, чтобы пользователь сделал это. Однако в этом случае ваша формула полностью изменит результаты вашей электронной таблицы, если вы измените ограничение, и нет практически никакого способа, которым вы хотели бы этого, так зачем хранить его в таблице вместо Именованного диапазона? Именованные диапазоны имеют другие преимущества, просто не важные для этой проблемы. Но если вы это сделаете, поместите его где-нибудь, его адрес не будет постоянно меняться (в основном, «изменение есть изменение» независимо от того, что изменилось), и это не будет иметь значения для проблемы скорости.
Я никогда не читал ничего, напрямую адресованного арифметике, как SUM()против строковых операций, как COUNT()для скорости. Похоже, другие читали такое и что строковые операции быстрее. В таком случае измените все using SUM()на use COUNT()COUNTA()Я бы предположил, что арифметика победит, но вот вам и все.
Да, как вы и говорите в своем комментарии, только дата начала задачи, которая уже прибыла, имеет значение, а не дата ее ожидаемого завершения. Поэтому важно проверять только это. Это потому, что ваша проблема не включает отказ от задачи, если она слишком старая. Поэтому, независимо от того, что она не была завершена и это задача 1, 2 или 3, она будет выполнена без заботы о ее ожидаемой дате завершения.
Однако самое большое изменение из всех, которые вы можете сделать, по сути, затмевающее все вышеперечисленное (включая другие ответы), — это...STOP recalculating every line over and over again.
После того, как задача выполнена, она НИКОГДА не будет рассмотрена снова в представленной логике. Так чтоwhy keep addressing it again and again???
Периодически (еженедельно или ежемесячно, как предлагают сами) копируйте все завершенные строки и вставляйте их значения. Так что они сделаны и закончены навсегда. Возможно, у вас осталось 100 строк с формулами вместо 15 000, которые вы накопили. (Выполняя 3 задачи одновременно, вы, очевидно, не можете иметь 2000 задач в очереди, поэтому я выбрал 100, но даже 20 кажутся более правдоподобными.) Так что эти вычисления НИКОГДА, НИКОГДА, НИКОГДА не будут выполнены снова. Просто подумайте об улучшении скорости.
По сравнению с нынешней ситуацией это просто уму непостижимо.
Тогда также были бы интересные преимущества. Самым большим было бы то, что ограничение могло бы измениться, если бы емкость увеличилась/уменьшилась. Как упоминалось выше, в настоящее время оно полностью уничтожило бы прошлые результаты, если бы было изменено. Но с переходом к значениям старый материал не будет затронут. Хотите изменить его? Скопируйте и вставьте значения для всех строк выше точки изменения, измените его и продолжайте, пока не измените его снова.
Другой вариант заключается в том, что меньшая вычислительная нагрузка позволит вам более широко использовать такие вещи, как динамическая адресация, хотя теперь они будут иметь гораздо меньший смысл.
Кстати, с вспомогательными столбцами, которые используются для ускорения работы, идея отличается от обычного «выполнения сложной задачи изолированно», чтобы облегчить формулу в другом столбце. В этом случае смысл будет в том, чтобы изолировать неизменные части основной формулы во вспомогательном столбце, чтобы Excel вычислял их один раз и только если они каким-то образом изменились. Так что в общем, если у вас 11 параметров в формуле, но 7 из них никогда или редко меняются, переделайте свою формулу так, чтобы их эффекты проявлялись только во вспомогательных столбцах, а оставшаяся формула считывала их результаты как один параметр. Это... может быть много переделки, включающей совершенно другой подход к обработке параметров, но обычно вы можете это сделать, и тогда эти части никогда не пересчитываются, так что все работает намного быстрее. Иногда вы не можете {так же, как иногда IFERROR()просто не работает, и вы должны использовать `IF(ISERROR())}, так что то, что вы можете сделать, выигрывает лишь немного. Но более 500 000 формул, немного все еще имеет большое значение.
Что касается аспекта диаграммы Ганта, я полагаю, что именно здесь Условное форматирование («УФ») поднимает свою замедляющую голову. (Я не видел ссылок на это, но можно предположить, что диаграмма Ганта с единицами не так вероятна, как использование УФ для создания красивых линий. Как уже упоминалось Mobus, есть способы получше, чем использование УФ. В дополнение к сказанному Mobus, можно выбрать любой подходящий «блочный» символ и, при необходимости, просто использовать функцию «Заливка» в разделе Выравнивание|По горизонтали, чтобы он заполнил ячейку, поэтому соответствие размера и формы будет менее важным, хотя вам все равно придется настраивать шрифт, чтобы символ соответствовал высоте строки. Даже REPT()сервер.
Но чтобы заставить его "петь" (особенно, если вы использовали вышеизложенное, чтобы значительно сократить количество вычислений), вы могли бы представить вывод через функцию TEXT()и построить ее форматирующую строку, включающую цвет шрифта. Это позволило бы вам указывать разные цвета для каждой строки строки, отличающиеся от строк выше и ниже, так что цвета никогда не будут располагаться рядом друг с другом, что улучшит читаемость. Обратите внимание, что эта часть находится в формуле на стороне ячейки, а не в CF со всеми ее другими проблемами, а не только с ее эффектами скорости.
Переход на Access или подобные программы, помимо того, что они не обязательно будут доступны вам и вашим пользователям, вряд ли станет невероятной помощью, поскольку 500 000 ячеек, похоже, будут в основном «ячейками справа, нагромождающимися», а не строками. Наличие 100 000 задач с пятью ячейками в каждой с вычислениями вопиет о необходимости Access или чего-то подобного или, возможно, выделенного для проектов. Но наличие 500 строк с тремя годами дат справа (1000-1100 вычислений на строку) НЕ вопиет о необходимости программы для работы с базами данных (хотя все еще хочется специального программного обеспечения... но это просто не всегда возможно, болит или нет). Вопреки распространенному «знанию», Excel — это НЕ какая-то «патока в январе», медленная как собака вычислительная машина, а SQL — не всегда какой-то яркий, блестящий вундеркинд 41-го века, каким-то образом существующий в нашем мире сегодня. Вы в любом случае делаете все вышеперечисленное, поэтому вычислительная нагрузка сокращается до очень малой части от того, что есть сегодня...