Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Sumowanie wartości w kolumnie dla symbolu

Adamus25 · Fan Excela **Pomógł:** 5 razy Posty: 64

Witam,

Otrzymuję dane w nietypowej tabeli. W kolumnie znajdują się symbole a wiersz poniżej wartości.
Jak zsumować wartości z danej kolumny dla konkretnego symbolu. Ilość wierszy tabeli zmienna - nie przekracza 150. Ilość symboli stała od 1 do 34.

Czy istnieje możliwość przebudowy tabeli w Power Query tak żeby można wykorzystać sumę warunków ?
Szczegóły w pliku.

Z góry dziękuję za pomoc
Pozdrawiam.

Dane_test.xlsx

Pobierz Plik ściągnięto 8 raz(y) 20.78 KB

Maciej Gonet · Wysłany: 21-02-2021, 01:25

Propozycja formuł w dwóch wariantach. Pierwszy tablicowe z funkcjami SUMA(JEŻELI, drugi zwykłe z SUMĄ.ILOCZYNÓW.
Sprawdź, który wariant bardziej Ci odpowiada.

Kod:

=SUMA(JEŻELI((PRZESUNIĘCIE(tbl_1;;B$21;;1)=$A23)*(MOD(WIERSZ(tbl_1);2)=0);PRZESUNIĘCIE(tbl_1;1;B$21;;1);0))

Kod:

=SUMA.ILOCZYNÓW((PRZESUNIĘCIE(tbl_1;;B$60;;1)=$A62)*(MOD(WIERSZ(tbl_1);2)=0)*PRZESUNIĘCIE(tbl_1;1;B$60;;1))

Wartości zerowe (i ujemne) zamaskowane formatowaniem.

Dane_test_MG.xlsx

Pobierz Plik ściągnięto 6 raz(y) 53.66 KB

DwaNiedźwiedzie · Wysłany: 21-02-2021, 11:33

Rozwiązanie z użyciem PQ.

Kod:

let
Źródło = Table.Buffer(Excel.CurrentWorkbook(){[Name="tbl_1"]}[Content]),
#"Dodano indeks" = Table.AddIndexColumn(Źródło, "Indeks", 1, 1),
pierwsza = Table.ColumnNames(Źródło){0},
kolumny = List.Skip(Table.ColumnNames(Źródło), 1),
wiersze = List.FirstN(Table.ToList(Table.SelectColumns(Źródło, pierwsza)), 2),
grupowanie = List.Transform(kolumny, each {_, (tbl) => List.Sum(Table.Column(tbl, _))}),

funkcja = (nazwa) =>
let
#"Usunięto inne kolumny" = Table.SelectColumns(#"Dodano indeks", {"Indeks", pierwsza, nazwa}),
#"Dodano kolumnę wartość" = Table.AddColumn(#"Usunięto inne kolumny", "wartość", each Record.Field(#"Usunięto inne kolumny"{[Indeks]}, nazwa)),
#"Zmieniono nazwy kolumn" = Table.RenameColumns(#"Dodano kolumnę wartość",{{nazwa, "symbol"}, {"wartość", nazwa}}),
#"Przefiltrowano wiersze" = Table.SelectRows(#"Zmieniono nazwy kolumn", each [Dane] = "Symbol" and not List.Contains({null, 0}, [symbol])),
#"Usunięto inne kolumny1" = Table.SelectColumns(#"Przefiltrowano wiersze",{"symbol", nazwa})
in #"Usunięto inne kolumny1",

#"Dołączone zapytanie" = List.Accumulate(kolumny, #table({"symbol"},{}), (st, cur) => Table.Combine({st, funkcja(cur)})),
#"Pogrupowano wiersze" = Table.Group(#"Dołączone zapytanie", {"symbol"}, grupowanie),
#"Posortowano wiersze" = Table.Sort(#"Pogrupowano wiersze",{{"symbol", Order.Ascending}})
in
#"Posortowano wiersze"

Adamus25 · Fan Excela **Pomógł:** 5 razy Posty: 64

Witam,

Panie Macieju - formuły extra. Dziękuję bardzo.

DwaNiedźwiedzie - kod oczywiście działa.
Dane do tbl_1 pobieram z plików również za pomocą PQ. Spróbuję połączyć obydwa kody. Jeżeli polegnę to poproszę o pomoc.

Punkty dodałem.

Panowie jeszcze raz dziękuję.