Формулы Excel — создание массива ИЛИ в СУММЕ

Формула массива, которая у меня есть в настоящее время, очень близка к тому, что я хочу, она ограничена только тем фактом, что у меня может быть только два условия (если я не хочу вручную добавлять больше). Что я хотел бы сделать, так это иметь возможность использовать имя, полученное из столбца таблицы, для соответствия критериям, аналогично использованию OR() с диапазоном.

В нынешнем виде моя формула массива выглядит примерно так (с использованием имен или ссылок на диапазоны)

=SUM((JOB_HRS)*(TEXT($E4,"0")=TEXT(EMP_ID,"0"))*(JOB_DATE>=$M4)*(JOB_DATE<=$N4)*((JOB_TYPE=CONFIG!$F$8)+(JOB_TYPE=CONFIG!$F$9)))

Я получаю лист отчета с примерно 5000 строками данных. Эта формула точно анализирует лист и суммирует общее количество часов, отработанных сотрудником (по идентификатору, который иногда сохраняется в виде числа или текста на основе ощущений программ вывода в тот день).

Последняя часть - это то, что я хочу упростить

((JOB_TYPE=CONFIG!$F$8)+(JOB_TYPE=CONFIG!$F$9))

Это работает, но я немного ограничен количеством ячеек, плюс формула выглядит слишком длинной. Я хотел бы упростить его до чего-то подобного, но не могу найти хорошую ссылку для работы с массивом SUM. есть идеи?

(JOB_TYPE=RNG_OF_JOB_TYPES)

Редактировать: Добавлено изображение

Ожидаемые результаты


arrays conditional-statements excel sum
person Karl    schedule 29.11.2017    source источник
comment
Есть ли причина, по которой вы просто не используете сводную таблицу?   -  person jeffreyweir    schedule 29.11.2017
comment
Из того, что я обнаружил, сводные таблицы используют жестко закодированную ссылку для фильтров, и мне нужно иметь возможность динамически обновлять мою с помощью ссылок на ячейки.   -  person Karl    schedule 29.11.2017
comment
Используйте слайсер для фильтрации Pivot. Конечный результат тот же, реализация и взаимодействие с пользователем намного проще.   -  person jeffreyweir    schedule 29.11.2017
comment
Я полагаю, что часть моего резервирования заключается в форматировании. У меня лист отформатирован идеально для печати на одном листе. Я не хочу, чтобы сводная таблица увеличивалась и уменьшалась. Кроме того, я пытаюсь ограничить то, что конечный пользователь может настроить на самом листе, используя защищенные ячейки, и это очень сложно с условным форматированием. Я мог бы сделать это с помощью VBA очень легко, но я даже ограничен политиками безопасности на компьютерах, на которых это будет использоваться.   -  person Karl    schedule 29.11.2017
comment
Не будет ли сводная таблица отображать то же количество записей, что и ваша формула? Кроме того, будет ли эта формула выполняться для каждого Emp_ID? Сколько из них у вас есть? Вы смотрели на функцию СУММЕСЛИМН? Обратите внимание, что функции СУММЕСЛИ/СУММЕСЛИМН требуют больших вычислительных ресурсов и их следует использовать с осторожностью. Последнее, что вам нужно, это рабочий лист с десятками тысяч СУММЕСЛИМН.   -  person jeffreyweir    schedule 30.11.2017
comment
Emp_ID — это то, что будет меняться в каждой строке. Формула встречается только в 1 столбце, у меня есть много других сводных данных, которые должны быть встроены в нее как единый исходный документ, в который сводная таблица не вписывается. Это то, что нужно легко скопировать в PowerPoint и проинформировать людей типа генерального директора, у которых нет времени, чтобы расшифровать это. Мне буквально нужен только 1 результат формулы, чтобы поместиться в одну ячейку.   -  person Karl    schedule 30.11.2017
comment
Если у вас есть Emp_ID на панели строк и любые другие конкретные столбцы, которые вы хотите на панели строк или столбцов, то как сводная таблица займет больше места, чем, скажем, решение формул? Я думаю, что я что-то упускаю...   -  person jeffreyweir    schedule 30.11.2017
comment
Собираетесь ли вы жестко запрограммировать эти Emp_ID? Есть ли вероятность, что они изменятся в будущем? Не лучше ли в этом случае использовать сводную таблицу, чтобы получить полный список Emp_ID, а не те, которые могут быть избыточными? Извините, что заостряю внимание на сводных таблицах... просто пытаюсь понять ваши требования и убедиться, что вы получаете наилучший подход к ним :-)   -  person jeffreyweir    schedule 30.11.2017
comment
Я использую столбец с проверкой данных, чтобы выбрать Emp по имени, который заполняет идентификатор в скрытом столбце. Лист упорядочен по жестко закодированным должностям сотрудников, из которых сотрудники будут периодически меняться. Я загружу фрагмент того, как должен выглядеть результат. Столбцы справа — это вычисляемые столбцы, основанные на моей исходной формуле. Я просто хотел бы обновить его, чтобы динамически учитывать больше критериев.   -  person Karl    schedule 30.11.2017
comment
Итак, у вас есть около 5000 сотрудников, которых нужно показать? Или это скорее специальный отчет, в котором пользователи выбирают несколько сотрудников, представляющих интерес?   -  person jeffreyweir    schedule 30.11.2017
comment
Более специальное. У меня есть только 42 сотрудников для отслеживания. Что длинно, у меня есть 5000 строк часов для полетов   -  person Karl    schedule 30.11.2017
comment
Я просто действительно предпочитаю конкретный контроль над форматированием и макетом.   -  person Karl    schedule 30.11.2017
comment
Я использую пользовательские стили сводных таблиц, чтобы сводные таблицы выглядели именно так, как я хочу. Но я безнадежно предвзят... Я думаю, что сводные таблицы - лучший способ сделать практически все, и использовать их вместо VBA или формул везде, где я могу.   -  person jeffreyweir    schedule 30.11.2017
comment
Еще один способ получить лучшее из обоих миров — использовать сводную таблицу на скрытом листе для обработки чисел, а затем получить конкретные ответы, которые вы хотите, используя потрясающую функцию GETPIVOTDATA. Включил это в мой ответ.   -  person jeffreyweir    schedule 30.11.2017
comment
Я предполагаю, что предвзято отношусь к ним. На самом деле я предпочитаю Access, но мне нужно, чтобы этот трекер был простым... Я полагаю, что ищу что-то похожее на *(IF(MATCH(FLTD_ACFT_MDS,CFG_PRIACFT,0),1,0)) но это возвращает ошибку, хотя он работает в массиве count.   -  person Karl    schedule 30.11.2017
comment
MAX(--(JOB_TYPE=RNG_OF_JOB_TYPES)) должен давать 0, если ни один из них не является истинным, и 1, если любой из них верен.   -  person MacroMarc    schedule 30.11.2017
comment
Есть ли способ использовать MAX в сочетании с SUM()*()? Я получаю сообщение об ошибке.   -  person Karl    schedule 01.12.2017

Ответы (1)


arrow_upward
1
arrow_downward

Лучше всего использовать формулу СУММЕСЛИМН. Вам все равно нужно будет указать каждый критерий по отдельности, но синтаксис намного проще:

=SUMIFS(sum_range,criteria_range,criteria,...)

Но, как уже говорилось, лично я предпочитаю использовать сводные таблицы для обработки чисел. Иногда я помещаю сводную таблицу на скрытый лист и использую функцию GETPIVOTDATA на листе, доступном пользователю, чтобы получить входные данные и получить агрегированное значение из сводной таблицы. Это дает вам полную гибкость при форматировании.

person jeffreyweir    schedule 29.11.2017