Суммирование нескольких таблиц в Excel

Question

Я надеюсь, что это правильный сайт для этого вопроса

У меня есть квитанции о доставке за годы (более 60 таблиц, каждая на отдельном листе) с одинаковыми заголовками, и я хотел бы добавить их в сводную таблицу по годам.

Все, что мне нужно, это Art-No. , Description и общее Delivered Quantity , но прохождение каждого отдельного листа и их индивидуальное суммирование займет много времени. Не каждый заказ будет иметь одинаковые продукты или в том же порядке. Некоторые продукты могут появляться только один раз в год, в то время как другие на каждом заказе. Это немного беспорядок.

Art-No. по сути является идентификатором продукта и может использоваться для идентификации продукта (а также Description).

Пример таблицы 1

Пример таблицы 2

Пример таблицы 3

Пример таблицы желаемых результатов

Я пытался использовать Power Query для слияния, но это не консолидирует данные, а, кажется, помогает.

Я пробовал сводные таблицы, но они не хотят, чтобы они звучали как "Ссылка на источник данных недействительна"

Есть ли более простой способ сделать это?

Answer 1

Если бы все данные были на одном листе, было бы довольно легко применить к нему сводную таблицу для генерации сводки. Копирование и вставка 60+ листов в один заняло бы несколько минут вручную, но это можно сделать.

Кроме того, вы можете использовать макрос для копирования и вставки. Предполагая, что каждый лист начинается в A1 и все листы содержат таблицы данных, этот макрос должен работать:

Sub Macro1()

NumSheets = Application.Sheets.Count
Sheets.Add After:=Sheets(Sheets.Count)

i = 1
Do While i <= NumSheets
    Sheets(i).Select
    If (i = 1) Then
      Range("A1").Select
    Else
      Range("A2").Select
    End If
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets(NumSheets + 1).Select
    If (i = 1) Then
       Range("A1").Select
    Else
       Range("A2").Select
       Selection.End(xlDown).Select
       ActiveCell.Offset(1, 0).Range("A1").Select
    End If
    ActiveSheet.Paste
    i = i + 1
    Loop


End Sub

Answer 2

Я думаю, что вы были на правильном пути с Power Query. Как правило, следующие шаги:

1. Загрузите каждую таблицу в Power Query (это неприятно, но я не знаю ни одного варианта массовой загрузки). Вы можете установить опцию по умолчанию, чтобы загружать только соединение, чтобы у вас не было дубликатов листов с версией Power Query для каждой таблицы.

2. Откройте свою первую таблицу в Power Query Editor. Выберите « Добавить запросы» (не объединять) в разделе "Объединение" ленты.

3. В диалоговом окне "Добавить" выберите вторую таблицу и нажмите "ОК". Повторите эти действия для каждой таблицы, которую вы загрузили в Power Query. Теперь вы добавляете строки из каждой из 60 таблиц в одну "супер таблицу".

4. Когда все ваши загруженные таблицы будут добавлены, загрузите ваш запрос на добавление в Excel.

5. Используйте таблицу запросов добавления в качестве источника данных для создания сводной таблицы. Выберите Арт-№. и Description в качестве меток строк (в формате Tablular Layout, без промежуточных итогов) и Количество в качестве значений.

Answer 3

Вот подход, который не использует Power Query.

Для любой таблицы вы можете суммировать доставленное количество определенного продукта (Арт. №), используя SUMIF. Например, следующий формула даст общее количество доставленного Арт-№. 59792 в таблице под названием Table1.

`=SUMIF(Table1[Art-No.],"=59792",Table1[Delivered Quantity])`

Вы можете использовать функцию INDIRECT, чтобы извлечь имя таблицы из другой ячейки. Например, если ячейка A2 содержит "Table1", то следующая формула даст тот же результат, что и выше

`=SUMIF(INDIRECT(A2"[Art-No.]"),"=59792",INDIRECT(A2&"[Delivered Quantity]"))`

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

1. Поместите все номера артикулов в первый ряд, начиная с B1.
2. Поместите все имена таблиц в первый столбец, начиная с A2.
3. Скопируйте следующее в B2

= SUMIF(НЕПОСРЕДСТВЕННО ($ A2 & "[Art-No.]"), B $ 1, НЕПОСРЕДСТВЕННО ($ A2 & "[Доставленное количество]"))

4. Скопируйте B2 и вставьте в оставшуюся часть таблицы.

Answer 4

Вот вариант, который не использует Power Query или Pivot Tables:

Как вы указали в комментариях, вы можете создать желаемую таблицу результатов со всем, кроме Ordered qty. и Delivered qty. поля. Это означает, что все, что нам нужно сделать, это суммировать значения для каждого листа, используя Art-no. как уникальный идентификатор.

Шаг 1: Создайте список со всеми именами листа. Это можно сделать с помощью небольшого VBA, поскольку в вашем файле так много листов.

Sub SheetNames()
    Dim ws As Worksheet
    Dim r As Integer
    Set ws = Worksheets.Add
    For r = 1 To Worksheets.Count
        ws.Cells(r, 1).Value = Worksheets(r).Name
    Next
End Sub

Шаг 2: Создайте именованный диапазон, который ссылается на список имен рабочих листов. Я превратил список в таблицу и основал диапазон имен на этом, но это не обязательно.

Шаг 3: Создайте свою базовую таблицу результатов без Ordered qty. и Delivered qty. заполненные значения Если у вас где-то этого нет, может быть проще всего использовать что-то вроде того, что опубликовал сэр Аделаида, чтобы объединить все листы, удалить ненужные поля, стереть все количество. значения и удалить дубликаты. Вы можете задаться вопросом, зачем вам это нужно, если вы просто будете следовать этому методу, но уловка на следующем шаге слишком крута, чтобы не делиться ею.

Шаг 4: Напишите свои формулы для суммирования данных со всех листов одновременно. Во-первых, вот формула, которую вы можете использовать для Ordered qty.:

=SUMPRODUCT(SUMIF(INDIRECT("'"&sheets&"'!"&"B2:B10000"),[@[Art-no.]],INDIRECT("'"&sheets&"'!"&"D2:D10000")))

Это специальный SUMIF завернутый в SUMPRODUCT . Ключом к тому, чтобы заставить SUMIF работать с трехмерной ссылкой, является функция INDIRECT и тот список имен листов, который мы создали. Вот части формулы:

• INDIRECT("'"&sheets&"'!"&"B2:B10000") Это стандартная INDIRECT функция , за исключением того , что мы ссылающийся на именованный диапазон мы сделали в шаге 2 , что указывает на список имен листов. Это то, что делает его эталоном 3D. Я выбрал B2:B10000 но вы можете настроить / расширить его по мере необходимости. Легко сделать эталонный путь больше, чем требуется, чтобы убедиться, что ничего не пропущено, если диапазоны в обеих функциях INDIRECT имеют одинаковый размер. Нужно указать на колонку с Art-no. в других листах.
• [@[Art-no.]] Поскольку я превратил свою таблицу результатов в настоящую таблицу в Excel, это просто указывает на Art-no. значение в текущей строке по формуле.
• INDIRECT("'"&sheets&"'!"&"D2:D10000") Это то же самое, что и другой INDIRECT но оно указывает на столбец Ordered qty. .
• SUMIF(~) Теперь он становится SUMIF(Art-no. from every sheet, Art-no. in this row, Ordered qty. from every sheet)
• SUMPRODUCT(~) SUMIF вернет массив вместо одного значения, поэтому мы используем это, чтобы просто сложить все значения в этом массиве.

Формула для Delivered qty. то же самое, за исключением того, что вы должны изменить D2:D10000 на G2:G10000 или все, что требуется для указания на столбец с Delivered qty. в других листах.

---

_{Кредит, где кредит должен, я нашел этот метод на CreditJet.net и подумал, что это было круто .}