Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Из трехмерной таблицы в двухмерную - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Из трехмерной таблицы в двухмерную (Сводные таблицы/Pivot Table)
Из трехмерной таблицы в двухмерную
linnet Дата: Среда, 24.01.2018, 17:17 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

Что я делаю не так? возможно ли это вообще?
Спасибо заранее за ответы!
К сообщению приложен файл: excel_.xlsx (8.8 Kb)
 
Ответить
СообщениеЕсть таблица с данными о продажах товаров за определенное число в четырех магазинах:

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

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

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

Автор - linnet
Дата добавления - 24.01.2018 в 17:17
Pelena Дата: Среда, 24.01.2018, 17:42 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19165
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
Придётся отменять объединение ячеек и заполнять пустые строки.
Как это делается, можно посмотреть здесь или почитать здесь
Тогда сводная построится без проблем
К сообщению приложен файл: 9425032.xlsx (13.0 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПридётся отменять объединение ячеек и заполнять пустые строки.
Как это делается, можно посмотреть здесь или почитать здесь
Тогда сводная построится без проблем

Автор - Pelena
Дата добавления - 24.01.2018 в 17:42
linnet Дата: Среда, 24.01.2018, 17:49 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

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

Автор - linnet
Дата добавления - 24.01.2018 в 17:49
bmv98rus Дата: Среда, 24.01.2018, 17:59 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеКросс

Автор - bmv98rus
Дата добавления - 24.01.2018 в 17:59
krosav4ig Дата: Среда, 24.01.2018, 18:13 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 2346
Репутация: 989 ±
Замечаний: 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
 
Ответить
СообщениеНу, видимость объединения можно оставить, например, если выполнить подобный макрос, то пустых ячеек не будет, но объединение останется
[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]

Автор - krosav4ig
Дата добавления - 24.01.2018 в 18:13
Manyasha Дата: Среда, 24.01.2018, 19:13 | Сообщение № 6
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 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
 
Ответить
СообщениеВариант решения макросом:
[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]
Результат см. на листе "Результат"

Автор - Manyasha
Дата добавления - 24.01.2018 в 19:13
Nic70y Дата: Среда, 24.01.2018, 22:10 | Сообщение № 7
Группа: Друзья
Ранг: Экселист
Сообщений: 8709
Репутация: 2260 ±
Замечаний: 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
 
Ответить
Сообщениес одной доп.ячейкой (чтоб не заморачиваться, с кол-вом магазов в т.ч.) (в примере 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 и правее - товар

Автор - Nic70y
Дата добавления - 24.01.2018 в 22:10
Светлый Дата: Пятница, 26.01.2018, 07:51 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 0% ±

Excel 2013, 2016
G$1 и правее - товар

Товар можно такой формулой искать:
Код
=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$25;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$25<>"";СТРОКА($A$1:$A$25));СТОЛБЕЦ(B1)));"")

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


Программировать проще, чем писать стихи.
 
Ответить
Сообщение
G$1 и правее - товар

Товар можно такой формулой искать:
Код
=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$25;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$25<>"";СТРОКА($A$1:$A$25));СТОЛБЕЦ(B1)));"")

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

Автор - Светлый
Дата добавления - 26.01.2018 в 07:51
Светлый Дата: Пятница, 26.01.2018, 22:31 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 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)


Программировать проще, чем писать стихи.
 
Ответить
СообщениеВсё-таки придумал формулу, которая учитывает пустые ячейки в объединённых.
Код
=ЕСЛИ(ИЛИ(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)))

Автор - Светлый
Дата добавления - 26.01.2018 в 22:31
Светлый Дата: Понедельник, 29.01.2018, 12:55 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1823
Репутация: 507 ±
Замечаний: 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
 
Ответить
СообщениеУдалось упростить формулу. Сделал через ПОИСКПОЗ. Заработало только когда 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 в 12:55
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Из трехмерной таблицы в двухмерную (Сводные таблицы/Pivot Table)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!