1

У меня есть электронная таблица с сотнями тысяч строк на нескольких листах, использующая несколько столбцов VLOOKUP на каждом листе, а также некоторые столбцы формул, такие как COUNTIFS и SUMIFS, которые просматривают каждую строку на листе при каждом вычислении. Он загружает мой процессор каждый раз, когда рассчитывает, и иногда это занимает так много времени, что я могу приготовить чашку кофе, прежде чем это будет сделано.

Я уже сделал это намного менее ужасным, используя MATCH("*", [column], - 1), чтобы найти последнюю строку и динамически генерировать диапазон вместо простой ссылки на весь столбец, и заменяя VLOOKUPs на INDEX. Матчи. Осталось только две формулы, которые я не могу понять, как их улучшить.

Вот примерные данные, с которыми я работаю:

+--------+-------+----------+--------+
| Group  | Item  | Quantity | Round  |
+--------+-------+----------+--------+
| Group1 | Item1 |        2 | 1      |
| Group1 | Item2 |        2 | 2      |
| Group2 | Item1 |        2 | 1      |
| Group2 | Item2 |        3 | 2      |
| Group2 | Item3 |        2 | unused |
+--------+-------+----------+--------+


Что касается первой проблемы, я хочу посмотреть, все ли элементы в группе имеют одинаковое количество. Старая версия использовала эту формулу:
=COUNTIFS(A:A,A2)=COUNTIFS(A:A,A2,C:C,C2)

Могу ли я выполнить то же самое без использования COUNTIFS? Может ли работа с базой данных повысить производительность по сравнению с самими необработанными данными? Или это лучшее, что я могу сделать, чтобы заменить ссылки на весь столбец на динамические диапазоны, рассчитанные MATCH?


Для второй проблемы я пытаюсь найти минимальное «круглое» значение для группы. Старая формула:
=MINIFS(D:D,A:A,A2)

Раньше это была формула массива -

={MIN(IF(A:A,A2),IF(P:P>0,P:P))}

- поэтому я был очень взволнован, когда MINIFS был добавлен в Excel. Но есть ли что-то лучше МИНИФОВ, чтобы я мог получить этот ответ?

1 ответ1

0

Вы можете полностью сделать это с помощью сводной таблицы. По моему опыту сводная таблица (однажды созданная и кэшированная) намного быстрее, чем формулы sumif/countif, но это зависит от размера вашего набора данных.

Создайте сводную таблицу для ваших данных следующим образом:

  • Выберите ваши данные, нажмите кнопку Сводная таблица, нажмите ОК
  • Перетащите "Группа" в поле строк
  • Перетащите "Округление" в поле столбцов, щелкните его, нажмите "Параметры поля значения", выберите "Мин" и нажмите "ОК" - для каждой группы есть минимум
  • Перетащите "Количество" в поле столбцов, щелкните по нему, нажмите "Параметры поля значения", выберите "Мин" и нажмите "ОК".
  • Перетащите «Кол-во» в поле столбцов, щелкните его, нажмите "Настройки поля значения", выберите "Макс" и нажмите "ОК".

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

Скриншот того, как это должно выглядеть в Excel

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .