#excel #excel-formula #excel-2016
#преуспеть #excel-формула #excel-2016
Вопрос:
Я постоянно усложняю свои формулы, чтобы вместить большой объем данных. Я пытался упростить эту формулу Excel, но, похоже, не могу найти способ, который хорошо работает. Я был бы признателен за любой совет!
{=IFERROR(IF(INDEX(FullFlow_2,SMALL(IF(FullFlow_2[SN]=[@SN],ROW(FullFlow_2[SN])-ROW(INDEX(FullFlow_2[SN],1,1)) 1),COLUMN(M$2)-COLUMN($L$2)),4)="Complete",INDEX(FullFlow_2,SMALL(IF(FullFlow_2[SN]=[@SN],ROW(FullFlow_2[SN])-ROW(INDEX(FullFlow_2[SN],1,1)) 1),COLUMN(M$2)-COLUMN($L$2)),2),IF(VLOOKUP([@SN],FullSNList_2,8,FALSE)="None",IF(ISNUMBER(MATCH(SUBSTITUTE(TRIM(M4),"`",""),TRIM($L4:L4),0)),INDEX(FullFlow_2,SMALL(IF(FullFlow_2[SN]=[@SN],ROW(FullFlow_2[SN])-ROW(INDEX(FullFlow_2[SN],1,1)) 1),COLUMN(M$2)-COLUMN($L$2)),2)amp;"` ",INDEX(FullFlow_2,SMALL(IF(FullFlow_2[SN]=[@SN],ROW(FullFlow_2[SN])-ROW(INDEX(FullFlow_2[SN],1,1)) 1),COLUMN(M$2)-COLUMN($L$2)),2)amp;" "),IF(OR(VLOOKUP([@SN],FullSNList_2,8,FALSE)="Dev",VLOOKUP([@SN],FullSNList_2,8,FALSE)="ECO",VLOOKUP([@SN],FullSNList_2,8,FALSE)="Rework"),IF(ISNUMBER(MATCH(SUBSTITUTE(TRIM(M4),"`",""),TRIM($L4:L4),0)),INDEX(FullFlow_2,SMALL(IF(FullFlow_2[SN]=[@SN],ROW(FullFlow_2[SN])-ROW(INDEX(FullFlow_2[SN],1,1)) 1),COLUMN(M$2)-COLUMN($L$2)),2)amp;"` ",INDEX(FullFlow_2,SMALL(IF(FullFlow_2[SN]=[@SN],ROW(FullFlow_2[SN])-ROW(INDEX(FullFlow_2[SN],1,1)) 1),COLUMN(M$2)-COLUMN($L$2)),2)amp;" "),IF(ISNUMBER(MATCH(SUBSTITUTE(TRIM(M4),"`",""),TRIM($L4:L4),0)),INDEX(FullFlow_2,SMALL(IF(FullFlow_2[SN]=[@SN],ROW(FullFlow_2[SN])-ROW(INDEX(FullFlow_2[SN],1,1)) 1),COLUMN(M$2)-COLUMN($L$2)),2)amp;"` ",INDEX(FullFlow_2,SMALL(IF(FullFlow_2[SN]=[@SN],ROW(FullFlow_2[SN])-ROW(INDEX(FullFlow_2[SN],1,1)) 1),COLUMN(M$2)-COLUMN($L$2)),2)amp;" ")))),"_")}
Немного больше информации о коде. Он предназначен для сканирования таблицы и поиска имени шага (DOC ###). В зависимости от других критериев (если в одном столбце нет ни одного, переделать или сломать) пробелы добавляются в конец, чтобы различать их, при этом их все равно можно подсчитать на более позднем этапе. Кроме того, если шаг выполняется во 2-й раз, после этого будет добавлена точка.
Я помещал формулу в http://excelformulabeautifier.com / чтобы было немного легче читать!
ОБНОВЛЕНИЕ: я немного изменил формулу и добавил пояснения. Новый код заменил старый в теле. Я прикрепил фотографии, чтобы показать мое объяснение.
ПРИМЕЧАНИЯ: Чтобы это сработало для меня, из-за циклических ссылок я включил итеративные вычисления и установил максимальное количество итераций равным 2.
Комментарии:
1. Я не могу поверить, что это код, написанный человеком! Это безумие 🙂 — Я предлагаю вам начать с выяснения того, что он делает, а затем переписать его с нуля, даже используя VBA.
2. Убедитесь, что вы упомянули, почему вы хотите изменить формулу. Конечно, его нелегко поддерживать. Кроме того, упростите чтение формулы, заметив, что некоторые выражения часто повторяются, для целей чтения замените такие выражения, как: СТРОКА(ИНДЕКС(FullFlow_2[SN] с, возможно, XX, затем для СТРОКИ(FullFlow_2[SN])-XX,1,1)) 1) с, может быть, YY и т.д. Следующий шаг — сделать отступ в операторе IF. Тогда попытайтесь понять это.
3. И если вы поняли, что он должен делать, вы можете обогатить свой вопрос, сделав выборку с образцовыми данными и ожидаемым результатом. Упрощенный пример наверняка даст полезные ответы.
4. @NoChance в отношении того, что он создан человеком. У меня было несколько формул такого размера. Обычно они создаются вспомогательными столбцами, а затем вы объединяете все это в один, удаляя, скажем, C2 и делая его формулой, которая у вас есть в C2, и так далее.
5. Я бы оставил «вспомогательные столбцы»: они значительно упростят устранение неполадок, если у вас возникнет ошибка. «Недвижимость» в Excel в изобилии, и вы всегда можете скрыть столбцы или строки, чтобы они не мешали. Кроме того, если вы оставите этот лист и вернетесь к нему через 6 месяцев, будете ли вы иметь представление о том, что происходит?
Ответ №1:
Удаление VLookups из формулы значительно сократило время вычисления! Спасибо всем за ваши идеи! Любые другие предложения приветствуются!