Транспонированные числа для многих столбцов

Question

Входные данные:

Мне нужно подсчитать количество символов «1» и «0» в каждом столбце следующим образом:

Есть ли способ сделать это в Excel/макросах?

Answer 1

Если мы скажем, что столбцы начинаются с "D1"и мы печатаем до "A3", это может выглядеть примерно так:

Sub countAndTranspose()
Dim section As Range, count As Range, entry As Range, sectionPrint As Range

Set section = Range("D1", Cells(1, Columns.count).End(xlToLeft)) 'All headers
For Each entry In section
    Set sectionPrint = Cells(Rows.count, 1).End(xlUp) 'Last row in "A"
    Set count = Range(Cells(entry.Row + 1, entry.Column), Cells(Rows.count, entry.Column).End(xlUp))
    sectionPrint.Offset(1).Value = entry.Value 'Print header below last row in A
    sectionPrint.Offset(1, 1).Value = WorksheetFunction.CountIf(count, 1) 'Print sum of 1
    sectionPrint.Offset(1, 2).Value = WorksheetFunction.CountIf(count, 0) 'Print sum of 0
Next entry

End Sub

Мы начинаем с определения нашего диапазона столбцов, начиная с "D1"и до самого дальнего света строки.
Затем мы циклически проходим по этому диапазону, выбирая, где напечатать имя столбца (А3 и ниже должны быть ПУСТЫМИ)

Answer 2

Решение Excel Power Query

Результат:

---

Для профессионалов- полная процедура:

let
    Source = Excel.CurrentWorkbook(),
    #"Expanded Content" = Table.ExpandTableColumn(Source, "Content", {"OPERATION", "MEDIA", "IT"}, {"OPERATION", "MEDIA", "IT"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Content",{"Name"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Columns", {"Attribute", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Grouped Rows", {{"Value", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Grouped Rows", {{"Value", type text}}, "en-US")[Value]), "Value", "Count", List.Sum),
    #"Replaced Value" = Table.ReplaceValue(#"Pivoted Column",null,0,Replacer.ReplaceValue,{"Attribute", "1", "0"})
in
    #"Replaced Value"

---

Для других- подробные шаги.

Выберите таблицу входных данных (включая заголовки).
Вставить -> Таблица

ХОРОШО

Нажмите на двойные стрелки рядом с «Содержимым».
Снимите флажок.Использовать исходное имя столбца в качестве префикса

ХОРОШО

Щелкните правой кнопкой мыши по столбцу «Имя» -> Удалить

Выделить всю таблицу
Трансформировать -> Отменить сворачивание столбцов

Главная -> Группировать по

ХОРОШО

Выберите столбец «Значение»
Преобразование -> Сводной столбец
как значения Выберите столбец «Количество»

ХОРОШО

Выделить всю таблицу
Преобразовать -> Заменить значения
Заполнить следующим образом:

ХОРОШО

Главная -> Закрыть и загрузить

Answer 3

Под столбцами ЭКСПЛУАТАЦИЯ, СРЕДСТВА МАССОВОЙ ИНФОРМАЦИИ и ИТ введите две формулы:

COUNTIF ( A2:A10, 1 )для единиц
COUNTIF ( A2:A10, 0 )для нулей.

Скопируйте формулы ниже MEDIA и IT.

Answer 4

Для решения, не использующего VBA, вы можете воспользоваться LET, если у вас есть Excel 2016 или Microsoft 365. Это будет работать и в Excel для Mac (в отличие от Power BI).

=LET( array, A1:E4,
       hdr, INDEX( array, 1, ),
       rSeqBody, SEQUENCE( ROWS( array ) - 1 ),
       cSeq, SEQUENCE( 1, COLUMNS( array ) ),
       body, INDEX( array, rSeqBody + 1, cSeq ),
       onesCount, MMULT( TRANSPOSE( SIGN( rSeqBody ) ), body ),
       t1s, IFERROR( INDEX( TRANSPOSE( hdr ), TRANSPOSE( cSeq ), {1,2} ), TRANSPOSE( onesCount ) ),
       t10s, IFERROR( INDEX( t1s, TRANSPOSE( cSeq ), {1,2,3} ), ROWS( body ) - TRANSPOSE( onesCount ) ),
       t10s )

Вот пример приложения:

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

Как это работает

Сначала он получает имена полей и помещает их в массив с именемhdr. Затем он создает последовательности строк и столбцов для вычисления и формирования выходов. Затем он создает массив, содержащий 1 и 0, называемыйтело. Затем он суммирует единицы по столбцам вoneCountс помощью умножения матриц.

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

Чтобы доставить результат, он сшивает hdr(транспонировано)на oneCount(транспонировано)путем переиндексации столбцов транспонированного hdr для принудительного появления ошибок в столбце 2 каждой строки, а затем использует IFERROR для замены ошибок !REF# на транспонированное значение onesCount.

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

Если вам нужны заголовки 1COUNT и 0COUNT, их также можно вышить, но, вероятно, проще просто написать их на листе с любой желаемой формулировкой.

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