Сводка данных из таблицы Excel, которая имеет подзаголовки и частичные итоги.

Содержание

Вопрос или проблема
Ответ или решение
Как обобщить данные из таблицы Excel с подзаголовками и промежуточными итогами
1. Использование формул Excel
2. Использование Power Query
3. Использование сводных таблиц
Заключение

Вопрос или проблема

У меня есть таблица в Excel, в которой есть несколько строк, определяющих подсекцию данных. Эти подсекции содержат промежуточный итог. Я хотел бы сделать сводку данных из этой таблицы (по сути, сводную таблицу), но использовать заголовки подсекций. Поскольку все это находится в одних и тех же колонках, я не знаю, как это сделать. Это то, что я хочу добавить во множество файлов, которые я получаю, поэтому не хочу заниматься форматированием файла Excel. Я надеюсь на формулу, которую я смогу просто вырезать и вставить, чтобы получить то, что мне нужно. Не уверен, что это вообще осуществимо. Эти данные выглядят примерно так:

Описание
Количество
Ставка
Сумма

Отдел 1

Товар 1
10
$2
$20

Товар 2
5
$3
$15

Промежуточный итог
$35

Отдел 2

Товар 1
12
$2
$24

Товар 2
6
$3
$18

Промежуточный итог
$42

То, что я хотел бы обобщить, выглядело бы так:

Отдел
Общее количество
Общая сумма

Отдел 1
15
$35

Отдел 2
18
$42

На самом деле, мой пример данных немного сложнее, чем это, но если я смогу это сделать, то смогу решить более сложную задачу, как я думаю.

Существует много способов достижения желаемого результата, вот несколько альтернативных способов, которые работают в зависимости от версии Excel, которую вы используете (обновленные версии MS365):

• Формула, используемая в ячейке F2
=LET(
a, A2:D9,
b, SCAN(,IF(INDEX(a,,2)=””,TAKE(a,,1),””),LAMBDA(x,y,IF(y=””,x,y))),
c, GROUPBY(b,CHOOSECOLS(a,2,4),SUM,,0,,b<>0),
VSTACK({“Отдел”,”Общее Количество”,”Общая Сумма”}, c))

Это также можно сделать с помощью Power Query, доступного в Excel 2010+ для Windows и Excel 365 (для Windows или Mac).

Чтобы использовать Power Query, выполните следующие шаги:

Сначала преобразуйте исходные диапазоны в таблицу и дайте ей соответствующее имя, в этом примере я назвал ее Table1.

Затем откройте пустой запрос на вкладке Данные -> Получить и преобразовать данные -> Получить данные -> Из других источников -> Пустой запрос.

Это откроет окно Power Query, теперь на вкладке Главная -> Расширенный редактор -> И вставьте следующий M-Code, удалив все, что вы видите, и нажмите Готово.

let
Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content],
AddedCondition = Table.AddColumn(Source, “Dept”, each if [Quantity] = null then [Description] else null),
FillDown = Table.FillDown(AddedCondition,{“Dept”}),
Filter = Table.SelectRows(FillDown, each [Description] <> null and [Description] <> “”),
Answer = Table.Group(Filter, {“Dept”}, {{“Общее Количество#(tab)”, each List.Sum([Quantity]), type nullable number}, {“Общая Сумма”, each List.Sum([Amount]), type nullable number}})
in
Answer

Наконец, чтобы импортировать его обратно в Excel -> Нажмите Закрыть и загрузить или Закрыть и загрузить в -> Первый вариант создаст новый лист с необходимым выводом, а второй предложит окно, в котором спросит, куда поместить результат.

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

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

=IF(K2=””,J2,N1)

Выберите любую ячейку в данных и нажмите ALT+D+P, это откроет Мастера сводных таблиц и диаграмм сводных таблиц.
Первый шаг -> Не нажимайте ничего по умолчанию, будет выбрана сводная таблица, нажмите только Далее.
Второй шаг -> Убедитесь, что мастер выбрал необходимый диапазон, и нажмите Далее.
Третий шаг -> Вы можете выбрать новый лист или существующий лист с предпочитаемыми ссылками на ячейки, чтобы поместить сводную таблицу, и нажмите Готово.
В области полей сводной таблицы добавьте Вспомогательный в области строк, а Количество и Сумма в области значений.
Измените макет сводной таблицы на вкладке Дизайн на Табличный формат и измените заголовок вспомогательного столбца на Отдел или на то, что подходит вам лучше всего. Остальное форматирование вы можете сделать в соответствии с вашими требованиями. Это даст вам точный вывод, который вам нужен.

Ответ или решение

Как обобщить данные из таблицы Excel с подзаголовками и промежуточными итогами

Если у вас есть таблица в Excel, содержащая подзаголовки и промежуточные итоги, и вы хотите создать сводную таблицу на основании этих промежуточных данных, это возможно сделать несколькими способами. В данном руководстве мы рассмотрим три основных метода: использование формул, Power Query и сводных таблиц.

1. Использование формул Excel

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

Пример формулы:

=LET(
     a, A2:D9,
     b, SCAN(,IF(INDEX(a,,2)="",TAKE(a,,1),""),LAMBDA(x,y,IF(y="",x,y))),
     c, GROUPBY(b,CHOOSECOLS(a,2,4),SUM,,0,,b<>0),
     VSTACK({"Отдел","Общее Количество","Общая Сумма"}, c))

Данная формула создаёт массив, который включает названия отделов, общее количество и сумму. Просто вставьте эту формулу в нужную ячейку, и данные будут автоматически обобщены.

2. Использование Power Query

Если вам необходимо работать с таблицами в Excel 2010 и выше или в версии 365, Power Query станет отличным инструментом для обработки данных.

Шаги для работы с Power Query:

Преобразуйте диапазон данных в таблицу и присвойте ей имя (например, Table1).
Перейдите на вкладку "Данные" и выберите "Получить данные" → "Из других источников" → "Пустой запрос".
В открывшемся окне выберите "Редактор запросов" и вставьте следующий код:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedCondition = Table.AddColumn(Source, "Dept", each if [Quantity] = null then [Description] else null),
    FillDown = Table.FillDown(AddedCondition,{"Dept"}),
    Filter = Table.SelectRows(FillDown, each [Description] <> null and [Description] <> ""),
    Answer = Table.Group(Filter, {"Dept"}, {{"Общее Количество", each List.Sum([Quantity]), type nullable number}, {"Общая Сумма", each List.Sum([Amount]), type nullable number}})
in
    Answer

Нажмите "Закрыть и загрузить", чтобы вернуть результат в Excel.

3. Использование сводных таблиц

Третий способ — создать сводную таблицу с использованием вспомогательного столбца. Это подходит для пользователей, не знакомых с Power Query.

Шаги:

Добавьте вспомогательный столбец после столбца суммы с формулой:
```
=IF(K2="",J2,N1)
```
Заполните формулу вниз по всей колонке.
Выделите любые ячейки данных и нажмите ALT+D+P, чтобы открыть мастер сводных таблиц.
Убедитесь, что диапазон данных выбран правильно, и нажмите "Далее".
Выберите, где вы хотите расположить сводную таблицу (в новом или существующем листе).
В области полей сводной таблицы добавьте вспомогательный столбец к строкам, а количество и сумму к значениям.
Установите вертикальный табличный формат и отформатируйте заголовок по своему усмотрению.

Заключение

Представленные методы помогут вам создать обобщенные отчеты по данным с подзаголовками и промежуточными итогами в Excel. Выберите тот подход, который лучше всего соответствует вашим нуждам и навыкам. Удачи в обработке данных!