if и sumifs с несколькими именованными критериями диапазона и между (‹›) массивом с данными, полученными из системы

Я работаю над формулой, которая должна суммировать общую стоимость на основе трех критериев.

Работает следующая формула:

=IF($D$4<=4,IF($D$4=1,SUMIFS(Name1,Nmd1,189,Nmd2,'Spreadsheet'!$A75),IF($D$4=2,SUMIFS(Name2,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75),IF($D$4=3,SUMIFS(Name3,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75),SUMIFS(Name4,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75)))),IF($D$4<=8,IF($D$4=5,SUMIFS(Name5,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75),IF($D$4=6,SUMIFS(Name6,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75),IF($D$4=7,SUMIFS(Name7,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75),SUMIFS(Name8,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75)))),IF($D$4=9,SUMIFS(Name9,Nmd1,189,Nmd2,'Spreadsheet '!$A75),IF($D$4=10,SUMIFS(Name10,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75),IF($D$4=11,SUMIFS(Name11,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75),SUMIFS(Name12,Nmd1,189,Nmd2, ‘Spreadsheet '!$A75))))))

Name1 до Name12 — столбцы с разными именами, заполненные числовыми значениями.

Nmd1 – это именованный диапазон, состоящий из числовых значений.

Nmd2 – это именованный диапазон, состоящий из числовых значений.

$A75 содержит числовое значение (фиксированное и не затронутое извлечением данных)

Все они основаны на опорной точке, которая извлекает оперативные данные из базы данных, поэтому формула должна работать с этим ограничением.

Это выдержка из ячейки H12 листа, содержащего все указанные выше диапазоны.

=+'DATA PULL DOWN FROM SYSTEM'!H12

Вопрос:

Моя задача состоит в том, что мне нужно изменить все 189 цифр на большее, 200 (">=200") и меньшее, чем 300 ("<300"). Я попытался задействовать следующий массив {">=200","<300"}, но мне не удалось заставить его работать со всеми критериями.

Ваша помощь очень ценится.


arrays database excel sumifs
person Neil    schedule 20.05.2016    source источник
comment
Привет, Нил, ты пробовал разбить свою большую проблему на более мелкие части? Это займет у всех гораздо меньше времени. например, если вы хотите изменить 189 цифр, покажите нам, как вы меняете одну из них. Что вы пробовали и как это показало, что это не работает?   -  person J. Chomel    schedule 20.05.2016
comment
Привет Дж. Хомель - спасибо. Он уже разделен (моя первая формула превышает ограничение в 8200 символов...). Следующее работает отдельно, но не в более широкой формуле СУММ(СУММЕСЛИМН(Имя1,Nmd1,{›=200,‹300})*{1,-1})   -  person Neil    schedule 20.05.2016
comment
Я до сих пор не понимаю, что случилось. Вы пытались использовать найти/заменить в текстовом редакторе для редактирования формулы? Если после замены формула становится слишком большой, то это ваша проблема, но очень сложно понять это из вашего вопроса. Вот почему я советую вам разделить вашу проблему на более мелкие части, текущая из которых — «Что делать, если моя формула слишком длинная для Excel?». Затем вы можете описать, как вы туда попали - что вы сделали отлично   -  person J. Chomel    schedule 20.05.2016
comment
Хорошо, Дж. Чомел, пожалуйста, позвольте мне попытаться внести ясность. в Nmd2 есть несколько значений. Наименьшее значение — 109, а максимальное — 663. Работающая формула выбирает конкретное значение 189. Новая формула должна возвращать значения всех чисел от 200 до 300. В этом диапазоне есть только следующие значения — 201. , 202, 203, 211, 212, 221, 222, 249, 259, 269. Надеюсь, в этом есть смысл?   -  person Neil    schedule 20.05.2016
comment
Да, наверное, для меня это слишком сложно. Что вам нужно, так это попробовать небольшой пример, на котором мы можем вам помочь. Не пример с формулой из 800 символов! И вы говорите, что не справились, но что происходит? Есть ли сообщение об ошибке?   -  person J. Chomel    schedule 20.05.2016
comment
Благодарю за помощь Дж. Чомеля. Я буду продолжать пытаться взломать его (и опубликую решение, если мне удастся). У меня было сообщение об ошибке с подробным описанием ограничения формул около 8200 символов (постараюсь воспроизвести и опубликовать).   -  person Neil    schedule 20.05.2016

Ответы (1)


arrow_upward
0
arrow_downward

Если ваша проблема превысила 8192 Ограничение Excel для длины формулы, то вот что вам нужно сделать:

[189] — 3 символа, а [">=200","<300"})*{1,-1}] — 23 символа. Таким образом, вы должны найти 20 символов на каждый экземпляр [189] в своей формуле. Сначала вы можете попробовать переименовать Spreadsheet в S, затем Name1/Name2/Name3/... в N1/N2/N3/..., Nmd1/Nmd2/Nmd3/... в d1/d2/d3/...

Другой вариант для вас — изучить Excel vba и есть программа сделать это. Я думаю, что будет легче поддерживать в долгосрочной перспективе.

person J. Chomel    schedule 20.05.2016
comment
Привет J. Chomel, спасибо за вашу помощь в этом. В конце концов мне пришлось ввести новый столбец, который избавил от необходимости искать диапазон между 200 и 300. Это менее элегантный обходной путь, чем я надеялся. - person Neil; 10.06.2016