Страницы: 1
RSS
Перевернуть таблицу и посчитать сумму
 
Добрый день.
Есть исходные данные в виде списка пройденных блоков материала, за один блок в одну и туже дату может быть несколько результатов.
Первоначально они предоставлены, как в примере. Сверху блоки, справа даты, некоторые из дат повторяются по несколько раз.

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

Прошу помощи.  
 
Цитата
Katerina Larionova написал:
Первоначально они предоставлены, как в примере. Сверху блоки, справа даты
слева даты?
Цитата
Katerina Larionova написал:
но как посчитать сумму за каждую дату
т.е. в итоговом варианте должны быть уникальные даты с общей суммой из первой таблицы?
Лень двигатель прогресса, доказано!!!
 
Сергей, Да, конечно опечатка :oops: , слева в столбец в исходных данных стоят даты
во вложении там два листа, первый исходник, а второй, что должно получиться.
На то что там в 1 дате 2018 год стоит, не смотрите, такие мелкие косяки я вижу.  
 
1. тянем блоки в столбец в ячейку А2 и тянем вниз
Код
=ИНДЕКС(пример!$B$1:$Q$1;СТРОКА(A1))
2. проставляем даты в В1 пишем 01.01.2019 в С1 пишем 02.01.2019 выделяем обе ячейки и тянем за крестик вправо пока не кончится эксель

3. суммируем в В2 и тянем вниз и вправо
Код
=СУММЕСЛИ(пример!$A$2:$A$63;B$1;ИНДЕКС(пример!$B$2:$Q$63;;ПОИСКПОЗ($A2;пример!$B$1:$Q$1;0)))
Изменено: Сергей - 21.01.2020 15:58:58
Лень двигатель прогресса, доказано!!!
 
Сергей, волшебник!
Благодарю от всей души
 
Katerina Larionova,
Если исходник надо оставить нетронутым, то скопируйте содержимое в новый лист
и запустите макрос
Код
Sub Perevernut()
Dim i As Long
Dim iLastRow As Long
Dim Itog As Worksheet
 Set Itog = ThisWorkbook.Worksheets("итог")
 iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
 With Itog
   .Cells.Clear
  For i = iLastRow To 3 Step -1
    If Cells(i - 1, "A") = Cells(i, "A") Then
      Range("B" & i & ":Q" & i).Copy
      Range("B" & i - 1).PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
      Rows(i).Delete Shift:=xlUp
    End If
  Next
 iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
   Range("A1:Q" & iLastRow).Copy
   .Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
   .Activate
   Range("A1").Select
 End With
End Sub
 
ну и PQ
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дата мероприятия", type date}}),
    unpivot = Table.UnpivotOtherColumns(#"Измененный тип", {"Дата мероприятия"}, "Блок", "Значение"),
    result = Table.Pivot(Table.TransformColumnTypes(unpivot, {{"Дата мероприятия", type text}}, "ru-RU"), List.Distinct(Table.TransformColumnTypes(unpivot, {{"Дата мероприятия", type text}}, "ru-RU")[#"Дата мероприятия"]), "Дата мероприятия", "Значение", List.Sum)
in
    result
 
 OFF все хотят быть как Сергей
Цитата
Katerina Larionova написал:
волшебниками!
Не бойтесь совершенства. Вам его не достичь.
 
Stics,Спасибо. Это для меня пока очень сложно. файл с примером, я делала именно для примера. в реальности исходные данные намного шире и по 4 файла на каждый месяц за 2 года)))) . Формулу я могу развернуть на другие исходники, а вот с макросом не соображу)  
 
Не быть мне волшебником  :(
 
Цитата
Сергей написал:
суммируем в В2 и тянем вниз и вправо
Сергей, добрый день. прошу помощи у вас снова ((((

иногда у меня формула выдает #H/Д.

Это правильно, тк в некоторых месяцах у меня не полный перечень из списка, который мы сформировали в первый столбец. Помогите пожалуйста дополнить формулу
1=СУММЕСЛИ(пример!$A$2:$A$63;B$1;ИНДЕКС(пример!$B$2:$Q$63;;ПОИСКПОЗ($A2;пример!$B$1:$Q$1;0)))
что бы вместо  #H/Д, или пусто было или ноль.

Заранее спасибо.  
 
Цитата
Katerina Larionova написал:
волшебник!
ХА ХА волшебники эт которые Kuzmich, и Stics, пишут заклинания на непонятном языке, хотя и мы можем
трах ти би дох сим салабим
Код
=ЕСЛИОШИБКА(СУММЕСЛИ(пример!$A$2:$A$63;B$1;ИНДЕКС(пример!$B$2:$Q$63;;ПОИСКПОЗ($A2;пример!$B$1:$Q$1;0)));)
Изменено: Сергей - 22.01.2020 16:29:42 (смайлик гаденыш спрятал ;))
Лень двигатель прогресса, доказано!!!
 
Цитата
Сергей написал:
ХА ХА волшебники эт которые  Kuzmich , и  Stics , пишут заклинания на непонятном языке
Это уже волшебство седьмого измерения)))))

Спасибо :-)  
 
Katerina Larionova,
На листе "пример" всего 16 блоков в первой строке.
Откуда на листе "итог", который получается из листа "пример" появились блоки 17-23 ?
 
Цитата
Kuzmich написал:
Откуда
Пример сделан только для примера. упрощенный урезанный вариант.
в Реальности это 4 файла. в каждом 24 вкладки ( по 2 на каждый месяц)
Мне надо из них сделать один общий список, и разнести суммы по списку. Бывает так, что в 1 месяце есть эта услуга, а во втором нет.  А еще в названии всякие лишние пробелы, кавычки и прочий гемор, так что пока справляюсь тем, что могу понять по формулам. Не без помощи форума, конечно, а то бы пальцем по экрану сидела считала))))
За что вам и другим коллегам "по цеху" очень-очень благодарна за отзывчивость.
 
Katerina Larionova,
Цитата
в Реальности это 4 файла. в каждом 24 вкладки ( по 2 на каждый месяц)
Так приведите пример реального файла и что вы хотите получить в итоге
 
Kuzmich, А так можно было? )
не знаю как прикрепить файл... он больше 100 кб
 
Цитата
он больше 100 кб
Можно до 300 Кб
 
Kuzmich, во вложении исходный файл с данными. на последнем листе "НАДО" выведено то, что я успела сделать с помощью формул и головы. Из рабочего файла в пример вставила.

1. сняла объединение ячеек в полях дат и протянула дату в каждую ячейку с помощью макроса
Код
Sub Fill_Blanks()
   Range("A1:A150").Select
    Selection.UnMerge
    For Each cell In Selection
        If IsEmpty(cell) Then cell.Value = cell.Offset(-1, 0).Value
    Next cell
End Sub
2. Наименования блоков с каждого листа притянула в один столбец на лист "НАДО" с помощью формулы
Код
=ИНДЕКС(ИМЯЛИСТА!$B$1:$Q$1;СТРОКА(A1))

3. Удалила все повторяющиеся наименования блоков через выделение условным форматированием и удалением не нужного. Самые тупые моменты типа лишнего пробела или кавычек в логически одинаковых блоках удалила ручками, а на листах, где они были через автозамену поменяла на актуальные.

4. Протянула даты в строку. Разделила из по месяцам и добавила срез, что бы суммировать по будням и выходным.

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

Листы в исходнике двух типов. Ш-шоколад П-Пряники. Затем туда добавится стоимость каждого блока.
Пример прикрепила
Изменено: Katerina Larionova - 23.01.2020 14:13:09
 
Цитата
Kuzmich написал:
Можно до 300 Кб
Благодарю. получилось уменьшить
 
Katerina Larionova,
Цитата
на последнем листе "НАДО" выведено то, что я успела сделать с помощью формул
Я формулами не умею, могу помочь только с макросами.
Я так понимаю, что у вас в каждый месяц года есть два листа с данными (листы п и ш).
На них есть объединенные ячейки и вам надо их удалить и просуммировать данные за каждую дату.
А затем собрать данные со всех листов в лист "НАДО"
Если готовы работать с макросами, то можно обсудить детали. Всего доброго!
 
Kuzmich, Ок, спасибо.
Дорасту до макросов. обязательно обращусь. Пока готова делать то, что сама понимаю как исправить , Всего доброго!  
Страницы: 1
Наверх