Из трехмерной таблицы в двухмерную

Страница 1 из 1 1
Модератор форума: китин, _Boroda_, DrMini

Из трехмерной таблицы в двухмерную (Сводные таблицы/Pivot Table)

linnet

Дата: Среда, 24.01.2018, 17:17 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Есть таблица с данными о продажах товаров за определенное число в четырех магазинах:

Нужно преобразовать ее в таблицу, где в строке - дата, в столбцах - название товаров, а в ячейках - сумма продажи товара по всем магазинам:

И так и так крутила сводные таблицы, не хочет у меня суммировать продажи по магазинам, показывает только продажи Магазина 1:

Что я делаю не так? возможно ли это вообще?
Спасибо заранее за ответы!

К сообщению приложен файл: excel_.xlsx (8.8 Kb)

Ответить

Pelena

Дата: Среда, 24.01.2018, 17:42 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 19511

Репутация: 4620 ±

Замечаний: ±

Excel 365 & Mac Excel

Придётся отменять объединение ячеек и заполнять пустые строки.
Как это делается, можно посмотреть здесь или почитать здесь
Тогда сводная построится без проблем

К сообщению приложен файл: 9425032.xlsx (13.0 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816

Ответить

linnet

Дата: Среда, 24.01.2018, 17:49 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация: 0 ±

Замечаний: 0% ±

Excel 2016

Pelena, спасибо! Видимо, не обойтись без объединения, не хотелось исходную изменять... но придется.

Ответить

bmv98rus

Дата: Среда, 24.01.2018, 17:59 | Сообщение № 4

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4147

Репутация: 772 ±

Замечаний: 0% ±

Excel 2013/2016

Кросс

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

krosav4ig

Дата: Среда, 24.01.2018, 18:13 | Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 997 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Ну, видимость объединения можно оставить, например, если выполнить подобный макрос, то пустых ячеек не будет, но объединение останется
[vba]

Код

Sub dd()
    With Application
    .ScreenUpdating = 0: .EnableEvents = 0: .DisplayAlerts = 0
    With Sheets("Ëèñò1")
        .Copy Sheets(1)
        With .[A1].CurrentRegion
            .UnMerge
            On Error Resume Next
            .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
            On Error GoTo 0
            Sheets(1).Range(.Address).Copy
            .PasteSpecial xlPasteFormats
            Sheets(1).Delete
        End With
    End With
    .ScreenUpdating = 1: .EnableEvents = 1: .DisplayAlerts = 1
    End With
End Sub

[/vba]

К сообщению приложен файл: excel_.xlsm (16.4 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Среда, 24.01.2018, 18:15

Ответить

Manyasha

Дата: Среда, 24.01.2018, 19:13 | Сообщение № 6

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация: 902 ±

Замечаний: 0% ±

Excel 2010, 2016

Вариант решения макросом:
[vba]

Код

Sub test()
    Dim data, lr&, i&, j&, d As Date, prod$
    Dim dicDate As Object, dicProduct As Object, dicTemp As Object, kDate, kProd
    Dim inp As Worksheet, res As Worksheet

    Set inp = ThisWorkbook.Sheets(1)
    Set res = ThisWorkbook.Sheets(2)

    With inp.[a1].CurrentRegion
        data = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    Set dicDate = CreateObject("scripting.dictionary")
    Set dicProduct = CreateObject("scripting.dictionary")

    For i = 1 To UBound(data, 1)
        If data(i, 1) <> "" Then prod = data(i, 1)
        If data(i, 2) <> "" Then d = data(i, 2)
        If dicDate.exists(d) Then
            dicDate.Item(d).Item(prod) = dicDate.Item(d).Item(prod) + data(i, 4)
        Else
            Set dicTemp = CreateObject("scripting.dictionary")
            dicTemp(prod) = data(i, 4)
            Set dicDate.Item(d) = dicTemp
        End If
        dicProduct(prod) = i
    Next i

    With res
        .UsedRange.ClearContents
        .[a2].Resize(dicDate.Count) = Application.Transpose(dicDate.keys)
        .[b1].Resize(, dicProduct.Count) = dicProduct.keys
        i = 2
        For Each kDate In dicDate.keys
            For j = 1 To dicProduct.Count
                .Cells(i, j + 1) = dicDate.Item(kDate).Item(Trim(.Cells(1, j + 1)))
            Next j
            i = i + 1
        Next kDate
    End With
End Sub

[/vba]
Результат см. на листе "Результат"

К сообщению приложен файл: excel-1.xlsm (20.5 Kb)

ЯД: 410013299366744 WM: R193491431804

Ответить

Nic70y

Дата: Среда, 24.01.2018, 22:10 | Сообщение № 7

Группа: Друзья

Ранг: Экселист

Сообщений: 9130

Репутация: 2415 ±

Замечаний: 0% ±

Excel 2010

с одной доп.ячейкой (чтоб не заморачиваться, с кол-вом магазов в т.ч.) (в примере A14)

Код

=СУММ(ИНДЕКС($D$1:$D$17427;ПОИСКПОЗ(G$1;ИНДЕКС($A$1:$A$17427;ПОИСКПОЗ($F2;$B$1:$B$17427;)):$A$17427;)+ПОИСКПОЗ($F2;$B$1:$B$17427;)-1):ИНДЕКС($D$1:$D$17427;ПОИСКПОЗ("*";ИНДЕКС($A$1:$A$17427;ПОИСКПОЗ(G$1;ИНДЕКС($A$1:$A$17427;ПОИСКПОЗ($F2;$B$1:$B$17427;)):$A$17427;)+ПОИСКПОЗ($F2;$B$1:$B$17427;)):$A$17427;)+ПОИСКПОЗ(G$1;ИНДЕКС($A$1:$A$17427;ПОИСКПОЗ($F2;$B$1:$B$17427;)):$A$17427;)+ПОИСКПОЗ($F2;$B$1:$B$17427;)-2))

$F2 и ниже - даты
G$1 и правее - товар

К сообщению приложен файл: 7725372.xlsx (12.6 Kb)

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Среда, 24.01.2018, 22:26

Ответить

Светлый

Дата: Пятница, 26.01.2018, 07:51 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1867

Репутация: 536 ±

Замечаний: 0% ±

Excel 2013, 2016

Цитата Nic70y, 24.01.2018 в 22:10, в сообщении № 7 ()

G$1 и правее - товар

Товар можно такой формулой искать:

Код

=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$25;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$25<>"";СТРОКА($A$1:$A$25));СТОЛБЕЦ(B1)));"")

А если добавить в исходную таблицу другую дату, то Ваша формула некорректна.

Программировать проще, чем писать стихи.

Ответить

Светлый

Дата: Пятница, 26.01.2018, 22:31 | Сообщение № 9

Группа: Друзья

Ранг: Старожил

Сообщений: 1867

Репутация: 536 ±

Замечаний: 0% ±

Excel 2013, 2016

Всё-таки придумал формулу, которая учитывает пустые ячейки в объединённых.

Код

=ЕСЛИ(ИЛИ(G$1="";$E2=0);"";СУММ((Ч(СМЕЩ($B$1;LOG(МУМНОЖ((СТРОКА($2:$215)>=ТРАНСП(СТРОКА($2:$215)))*ТРАНСП($B$2:$B$215<>"");10^СТРОКА($1:$214)));0))=$E2)*$D$2:$D$215*(Т(СМЕЩ($A$1;LOG(МУМНОЖ((СТРОКА($2:$215)>=ТРАНСП(СТРОКА($2:$215)))*ТРАНСП($A$2:$A$215<>"");10^СТРОКА($1:$214)));0))=G$1)))

К сообщению приложен файл: 7725372-1.xlsx (16.5 Kb)

Программировать проще, чем писать стихи.

Ответить

Светлый

Дата: Понедельник, 29.01.2018, 12:55 | Сообщение № 10

Группа: Друзья

Ранг: Старожил

Сообщений: 1867

Репутация: 536 ±

Замечаний: 0% ±

Excel 2013, 2016

Удалось упростить формулу. Сделал через ПОИСКПОЗ. Заработало только когда 0 заменил на ""

Код

=СУММ((Ч(СМЕЩ($B$1;ПОИСКПОЗ(СТРОКА($2:$21);ЕСЛИ($B$2:$B$21="";"";СТРОКА($2:$21)));))=$F2)*(Т(СМЕЩ($A$1;ПОИСКПОЗ(СТРОКА($2:$21);ЕСЛИ($B$2:$B$21="";"";СТРОКА($2:$21)));))=G$1)*$D$2:$D$21)

И ещё упростил:

Код

=ЕСЛИ(ИЛИ(G$1="";$F2=0);"";СУММ((Ч(СМЕЩ($B$1;ПОИСКПОЗ(СТРОКА($2:$210);ЕСЛИ($B$2:$B$210;СТРОКА($2:$210)));))=$F2)*(Т(СМЕЩ($A$1;ПОИСКПОЗ(СТРОКА($2:$210);ЕСЛИ($B$2:$B$210<>"";СТРОКА($2:$210)));))=G$1)*$D$2:$D$210))

Программировать проще, чем писать стихи.

Сообщение отредактировал Светлый - Понедельник, 29.01.2018, 15:35

Ответить

Из трехмерной таблицы в двухмерную (Сводные таблицы/Pivot Table)

Страница 1 из 1
1