Количество различных значений с несколькими критериями

Ищете решение, позволяющее считать различные значения с несколькими критериями. Как показано ниже на скриншоте, я хотел бы знать, сколько POs у меня есть в месяц в зависимости от типа PO (сервис или материал).

Исходные данные:

введите описание изображения здесь

Желаемый результат:

введите описание изображения здесь

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

Спасибо всем заранее!!!

Предполагая, что ваши данные находятся в столбцах J: L, вы можете добавить вспомогательный столбец в M, введя эту формулу в M2 и перетаскивая вниз:

=COUNTIF(J:J,J2) 

Затем, если ваша желаемая таблица результатов находится в столбцах N: P, введите эту формулу в O2:

 =COUNTIFS($K:$K,O$1,$L:$L,$N2,$M:$M,">0")-MAX((COUNTIFS($K:$K,O$1,$L:$L,$N2,$M:$M,">1")-1),0) 

Затем вы можете перетащить эту ячейку на столбец P и перетащить их вниз.

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

Решение, которое я предлагаю, включает в себя 1 вспомогательную колонку, но вы сможете использовать ее довольно гибко. Я делаю следующие предположения:

1) Вы можете добавить столбцы в свою исходную таблицу. 2) Таблица начинается с ячейки A1

шаги:

  1. Преобразуйте диапазон данных в динамическую таблицу excel (выберите диапазон, нажмите Ctrl + t, отметьте «Таблица имеет заголовки» – в результате вы получите синюю таблицу). Таким образом, если вы добавите больше данных, формулы будут тянуться вниз.
  2. введите «HELPER» в ячейку D1 и «Количество PO» в ячейку E1 – это создаст 2 новых столбца (они должны стать синими, как и остальные таблицы)
  3. вставьте эту формулу в ячейку D2:

    = [@ [Месяц / год]] и [@ [Mate / Serv]]

  4. Вставьте эту формулу в ячейку E2:

      =SUMPRODUCT( ( [HELPER]=[@[Month/Year]] & [@[Mate/Serv]] ) / COUNTIFS([Month/Year],[Month/Year], [Mate/Serv],[Mate/Serv], [PO No.],[PO No.] ) ) 
  5. Добавьте сводную таблицу, поместите количество PO в область значений, она будет суммировать значения по умолчанию.

Как на этой картинке:

pic1

  1. Щелкните правой кнопкой мыши внутри области значений, выберите Summarize Values ​​By .. => MAX (как на втором рисунке ниже).

pic2

Вы увидите, что значения в стержне изменились.

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

ЕСЛИ ВЫ ХОТИТЕ СДЕЛАТЬ К ВАШЕМУ ОРИГИНАЛЬНОМУ ДИЗАЙНУ:

можно использовать ту же формулу (с незначительными изменениями, чтобы добавить имя таблицы в имена столбцов), но ей все еще нужна таблица и помощник – выполните шаги 1-3. Убедитесь, что вы настроены так, как показано на рисунке ниже:

 =SUMPRODUCT( ( Table2[HELPER]=H$1&$G2 ) / COUNTIFS(Table2[Month/Year],Table2[Month/Year], Table2[Mate/Serv],Table2[Mate/Serv], Table2[PO No.],Table2[PO No.] ) ) 

pic3

Если кто-то может изменить мою формулу, чтобы избежать создания HELPER – выбивай себя, 2 часа – мой порог для работы над 1 штукой.

Пожалуйста, не забудьте отметить наиболее подходящий ответ как таковой в конце.

Я создал фиктивную таблицу, поэтому ваши результаты не будут похожи на мои.

Как проверить:

  • Скопируйте свои данные на новый лист
  • Перейдите в меню Data ribbon => Удалить дубликаты
  • Поверните остаток
  • Поместите met / serve в строки, месяц / год в столбцы, Помощник в значения, который даст вам счет, который вы должны ожидать
  • Игнорировать время из ячейки DateTime в Excel
  • Проблема с DLL в одном из трех экземпляров, в которых используется тот же справочник
  • Копирование и вставка данных с использованием ссылки на ячейки / строки колонки в VBA
  • Получить количество дубликатов между двумя разными столбцами и рабочими листами
  • Удалить строки в excel
  • VLOOKUP не возвращается для datetime
  • Excel 2013 Откройте файл и попросите пользователя: «Автору хотелось бы, чтобы вы открывали его только для чтения»
  • Марко. После выполнения вычисления я хочу вставить значения в вычисленные строки, но сохранить формулы в строках ниже
  • Формула с утверждением IF и условием
  • Совместная рабочая книга Excel - распознавание следующей доступной строки
  • VBA for Excel выбрасывает «Объектная переменная или с переменной блока не задана», когда нет объекта
  • Interesting Posts

    Использование данных столбца Excel для создания SQL-запроса, запрашивающего базу данных

    Можно ли использовать отдельные ссылки на ячейки в массиве Excel внутри функции?

    Apache Poi 3.13 не может найти классы для открытия файлов XLSX

    Специальная функция excel для цикла

    листы с перечнем от наивысшего до самого низкого

    Автоматически изменять размер таблицы данных для соответствия сводной таблице

    VBA Excel с использованием подстановочного знака и справки об объекте

    Ошибка VBA «Запросить слишком сложную» с использованием rs.UpdateBatch (MS SQL)

    Чтение и запись открытого файла excel в C # во время его редактирования

    VBA: как копировать + вставлять две строки по ячейкам. Цикл через вложенный для каждой петли между рабочими листами

    Номер, сохраненный как текст, дает ошибку

    Как сортировать столбцы столбца таблицы ST с нулевой / пустой ячейкой столбца вверх?

    Как получить доступ к интерфейсу DropDown?

    Надстройка VB.NET VSTO Excel для запроса базы данных оракула

    Код для копирования нескольких строк с одного листа на другой в Excel

    Давайте будем гением компьютера.