#google-sheets
Вопрос:
У меня есть база данных оценок (C2:C937), которые варьируются от 4000-6500. У меня есть записи для каждого балла (D2:D937) и разница между 2 (E2:E937). Я пытаюсь найти формулу, которая подскажет мне, какой балл ближе всего к 5000. Я думал о том, чтобы иметь формулу, которая проверяет суммы значений столбца C и соответствующих им значений столбца E, а затем сортирует, какие значения больше всего нуждаются в улучшении. Я не знаю, возможно ли создать своего рода цикл в листах (что-то вроде цикла for в Java).
На изображении ниже показан набор значений, как описано выше. Результат, который я бы искал, вернул бы то значение, которое имеет наибольший шанс превысить 5000, то есть уже самое близкое.
Вот набор ожидаемых результатов. В этом наборе данных «Yanmega 4» никогда не будет возвращен, так как в столбце C его уже более 5000. «Зангуз 1» и «Янмега 3» будут первыми, так как они близки к превышению 5000 и имеют достаточную свободу действий выше 5000 (~400 точек свободы действий выше нее). «Зангуз 2» будет следующим, так как у него ~300 на 5000, что означает, что получить более 5000 должно быть проще, чем у некоторых других. «Зангуз 4» будет следующим, так как для получения более 5000 нужно набрать всего 28 очков, а также 105 очков пространства для маневра. «Зангуз 3» — последний, так как у него всего 15 точек пространства для маневра, основанных на текущем рекорде.
Вот с чего я начал. Первая строка запутана и была моей попыткой создать петлю. Вторая линия имеет хорошее начало, я просто не знаю, куда идти дальше. Обе строки не закончены.
=IF(IF(LARGE($C$2:$C$937,1)lt;5000,LARGE($C$2:$C$937,1),IF(LARGE($C$2:$C$937,2)lt;5000,LARGE($C$2:$C$937,2),LARGE($C$2:$C$937,3)) INDIRECT("E"amp;match(E954,$E$1:$E$940,0))lt;=5000,)) =IF(LARGE($C$2:$C$937,1)lt;5000,) Комментарии:
1. попробуйте загрузить демонстрационную версию с некоторыми данными и формулами, которые вы планируете использовать, чтобы помочь вам продвинуться дальше, будет легко.
2. поделитесь копией своего листа с примером желаемого результата
3. Пожалуйста, предоставьте достаточно кода, чтобы другие могли лучше понять или воспроизвести проблему.
4. @Apostolos55 Я добавил некоторые данные, желаемый результат и запланированный код. Надеюсь, это поможет 🙂
5. @player0 Я добавил некоторые данные, желаемый результат и запланированный код. Надеюсь, это поможет 🙂
Ответ №1:
1-й Я думаю, что ваша логика неверна. Я бы предпочел, чтобы Зангоз 4 был 1-м, затем Янмега 3, затем Зангоз 2 или Зангуз 3… вы можете использовать такую формулу, как:
=INDEX(B3:B8,MATCH( SMALL( IF($C$3:$C$8lt;5000,(5000-$C$3:$C$8)*100/$D$3:$D$8,100),*ROW($A$1:$A$6)* ), IF($C$3:$C$8lt;5000,(5000-$C$3:$C$8)*100/$D$3:$D$8,100),0),) и вставьте функцию как массив (с помощью ctrl sht enter) в нужное количество ячеек (вставьте формулу в верхнюю ячейку, исправьте ее, затем введите как массив, затем выберите все ячейки назначения, нажмите F2 для редактирования формулы, затем ectrl sht enter)
подстраивая c3:c8 к вашему c928:c933 и так далее, затем ПОСТРОЙТЕ($A$1:$A$6) столько, сколько у вас данных.
Проблема в том, что слишком много вызовов выполняется рекурсивно, и в большом количестве строк все будет происходить очень медленно… Вам лучше использовать дополнительный столбец с формулой
=IF($C$3:$C$8lt;5000,(5000-$C$3:$C$8)*100/$D$3:$D$8,100) а затем отфильтруйте/отсортируйте данные с помощью VBA или фильтров (на эту тему есть отличные видеоролики на YouTube, если политика компании (если таковая имеется) разрешает использование VBA).