☰

Подсчет столбца, если определенная строка находится в другом

tag-icon • tag-icon microsoft-excel worksheet-function
Подсчет столбца, если определенная строка находится в другом

У меня есть столбец рангов, и мне нужно определить количество каждого ранга (1-5), пока его тег (столбец 1) совпадает с тегом, указанным в выходной таблице. Это должно быть помещено в отдельную таблицу на другом листе, показывающую процентное содержание каждого ранга. Из приведенных ниже изображений я ввел проценты вручную, но хочу использовать формулу, чтобы сделать это автоматически. Любая помощь будет оценена по достоинству. Заранее спасибо.

Выходная таблица:

выходная таблица

Входной столбец:

входной столбец

решение1

Это было бы идеальным временем, чтобы узнать о PivotTables. Дайте вашим данным заголовки, которые описывают столбцы, например, "Team" и "Rank". Затем выберите данные и заголовки и перейдите в Вставка > PivotTable.

Затем перетащите «Команда» в область строк, «Ранг» в область столбцов, затем «Команда» в область значений (я предлагаю «Команда», поскольку это не числовое значение, поэтому по умолчанию будет подсчитываться, сколько раз каждая команда имела каждый рейтинг, что вам и нужно. Если вы выберете «Ранг», по умолчанию они будут суммироваться, что вам не нужно, и придется переключиться с «Сумма» на «Количество»).

Это просто подсчитывает, сколько раз каждая команда была 1-й, 2-й, 3-й и т. д. Щелкните правой кнопкой мыши по одному из результатов и выберите «Показать значения как >», затем «% от строки». Вы можете отключить общие итоги, но я оставляю это упражнением для вас, чтобы вы сами разобрались (подсказка: Сводные таблицы>Лента «Конструктор»). Надеюсь, это поможет вам и поможет начать работу с удивительной магией сводных таблиц без формул!

решение2

Для строк и столбцов в вашем примере, и предполагая, что ваш входной лист и ваш выходной лист называются «Вход» и «Выход» соответственно,

  • На листе «Выходные данные» заполните ячейки B2:F2с 1помощью 5, и введите AB1и CD2в A3и A4. Если вам нужно, чтобы они были сгенерированы автоматически из листа «Входные данные», пожалуйста, сообщите об этом.

  • в ячейке B3листа «Выходные данные» введите

    =COUNTIFS(Input!$A:$A, Output!$A3, Input!$B:$B, Output!B$2) / COUNTIFS(Input!$A:$A, Output!$A3)

  • перетащите/заполните вправо до столбца F и вниз до строки 4, и

  • форматировать как процент.

Объяснение:

  • Знаменатель (часть справа от /) равен

    COUNTIFS(Input!$A:$A, Output!$A3)

    Это подсчитывает количество строк на листе ввода, где столбец A содержит «тег» из столбца A текущей строки листа вывода. Для  Output!$A3 =  AB1это 6.

  • Числитель (часть слева от /) равен

    COUNTIFS(Input!$A:$A, Output!$A3, Input!$B:$B, Output!B$2)

    Это уточнение вышеизложенного; оно подсчитывает количество строк на листе ввода, где столбец A содержит «тег» из столбца A текущей строки листа вывода, а столбец B содержит число из строки 2 текущего столбца листа вывода. Для  Output!$A3 =  AB1и  Output!B$2 =  1это 3.

  • Итак, результат деления: 3/6 = 0,5 = 50%.

Связанный контент