Formuła sumująca komórki z danej kategorii

[chris_]

Cześć koledzy,

żeby się nie rozpisywać załączam arkusz z danymi i opisem, co chcę osiągnąć. Szukam dwóch formuł (zaznaczone na żółto), które rozwiążą mój problem, przy czym uprzedzam, że tabela jest na bieżąco uzupełniania i chciałbym, aby formuła była o prostej składni, tak aby w przyszłości można było dowiązać zależną "podkategorię".

przyklad.ods

(55 KiB) Pobrany 281 razy

Z góry dzięki za pomoc.

[Minio]

Innymi słowy, chcesz za pomocą formuł odtworzyć tabele przestawne.

Według mnie — nie warto. Strukturę danych masz prawie dobrą, ale zanieczyszczoną przez scalone komórki. Zacznij od ich usunięcia. Niech każdy wiersz w tabeli zawiera wszystkie informacje o danej obserwacji — kategorię, kwotę, miesiąc oraz rok. W ten sposób nie tylko przygotujesz dane do obróbki przy pomocy wyspecjalizowanych narzędzi, ale także pozwolisz na ich wprowadzanie w dowolnej kolejności — dane nie muszą być ułożone w żadnym konkretnym porządku.

Następnie obie poszukiwane informacje (suma kwot w podziale na kategorie oraz miesiąc w roku) uzyskasz przy pomocy odpowiednio zaprojektowanej tabeli przestawnej. Jeżeli tabela ta będzie czerpała informacje z nazwanego zakresu, to może automatycznie uwzględniać nowe dane.

[chris_]

Innymi słowy, chcesz za pomocą formuł odtworzyć tabele przestawne.

Pytanie czy to ma sens? Tymi danymi nie będę manipulować. Okey, ograniczmy się tylko do jednej kategorii zależnej. Bardziej chodzi mi o zautomatyzowanie sobie pojawiających się danych w drugim arkuszu (i kolejnym też). Bo też pod tamtą tabelą z sumowanymi wartościami poszczególnych kategorii pojawiają się proporcje, do których z kolei odnosi się wykres. Dotychczas robiłem to ręcznie (sumowanie wartości dla poszczególnych kategorii), teraz chciałbym sobie to zautomatyzować. Druga sprawa to fakt, że w tej tabeli (pierwszym arkuszu) mam odrobinę bardziej skomplikowane formatowanie (formatowanie warunkowe oparte na stylach, krawędzie),a te scalone komórki spełniają rolę przejrzystości tej tabeli i pewnej ekonomii, bo też według nich automatyzuje sobie zakresy wydruku i czasami drukuje raport za poszczególne lata. Zdaję sobie sprawę, że te scalone komórki one mogą stanowić problem dla poszukiwanej formuły sumującej kategorie (?).

Mateusz, poza tym powiem Ci szczerze, że nie za bardzo mam wyobrażenie, jak taka tabela przestawna miałaby funkcjonować, kiedy zależy mi na zachowaniu tego formatowania i chronologii wierszy. Czy tabela przestawna z automatu będzie się aktualizowała? I w końcu czy będzie ona obsługiwała formatowanie warunkowe?

[chris_]

Przedstawiłem swój "problem" na forum, bo każdy może popatrzeć na to z innej strony. Mnie przez myśl nie przeszła tabela przestawna, a może to i jakieś rozwiązanie jest.

[Jan_J]

za pomocą formuł odtworzyć tabele przestawne.

Albo emulować zapytania grupujące.
Dobrze zaprojektowany arkusz może służyć jako źródło danych do kwerend SQL-owych, z pewnymi ograniczeniami: tylko jedna tabela (więc operacje takie jak JOIN odpadają); bez możliwości modyfikacji z poziomu interfejsu bazy danych (z poziomu arkusza się da).
Odpowiedzi kwerend z zarejestrowanego źródła danych da się wklejać do arkusza. Nie upieram się przy tym rozwiązaniu; tabele przestawne są dobre, pamiętają własną konfigurację. Może się jednak zdarzyć konieczność zmiany skali problemu. Wtedy baza będzie OK.

[chris_]

Testowałem rozwiązania z tabelą przestawna, owszem, super szybkie rozwiązanie, ale ten brak odświeżania (ew. do przejścia) i nieobsługiwanie formatowania warunkowego nałożonego na wyjściowe wyniki jest problematyczny.

za pomocą formuł odtworzyć tabele przestawne.

Albo emulować zapytania grupujące.
Dobrze zaprojektowany arkusz może służyć jako źródło danych do kwerend SQL-owych, z pewnymi ograniczeniami: tylko jedna tabela (więc operacje takie jak JOIN odpadają); bez możliwości modyfikacji z poziomu interfejsu bazy danych (z poziomu arkusz się da).
Odpowiedzi kwerend z zarejestrowanego źródła danych da się wklejać do arkusza. Nie upieram się przy tym rozwiązaniu; tabele przestawne są dobre, pamiętają własną konfigurację. Może się jednak zdarzyć konieczność zmiany skali problemu. Wtedy baza będzie OK.

Moja wiedza na temat SQL-a jest mniej niż elementarna. Ja nie wiem, czy jest sens zaprzęgać taką armatę do w gruncie rzeczy prostego działania?


Szukam dalej i poprzeglądam sobie fora anglojęzyczne, może coś się uda stworzyć ze scenariuszy zadań Walkenbacha. Jakby ktoś miał chęć i pomysł na uporanie się z tym zadaniem, piszcie.

[chris_]

Z pierwszą formułą poradziłem sobie tak:

przyklad2.ods

(60.22 KiB) Pobrany 323 razy

Spełnia zadanie idealnie. Ktoś ma jakieś uwagi do tego sposobu, może lepsze rozwiązanie?

[chris_]

Koncepcję (zarys) drugiej formuły skonstruowałem w ten sposób:

Kod: Zaznacz cały

=INDEKS(Problem1.$E$1:$E$229;PODAJ.POZYCJĘ(E15;Problem1.$F$1:$F$124;0))

(dla komórki F15 w ostatnim arkuszu)

Trudno mi do niej dokoptować drugie kryterium i pytanie czy nie lepiej skorzystać z tablicy nazwanej globalnie?

[arras_1]

Próba rozwiązania za pomocą dodatkowego arkusza

[chris_]

Próba rozwiązania za pomocą dodatkowego arkusza

Właśnie próbowałem w podobny sposób połączyć te formuły, które rozbiłeś na osobne komórki, w jedną megaformułę, ale arkusz kalkulacyjny kompletnie ignoruje scalone komórki i tu faktycznie nie ma innego wyjścia jak rozbicie ich, i tak też to zrobiłeś, tworząc osobny arkusz. Dzięki za włożony trud.

Rozbiłem te scalone komórki miesiąca i roku, połączyłem je w jedną komórkę i tutaj już proste WYSZUKAJ.PIONOWO rozwiązało mój problem. I tak irytowałem się, bo przy zagnieżdżaniu formuł pokroju indeks, wyszukaj regularnie zamykało mi (z logiem) Calca. To co z nim zrobili w 4.2 woła o pomstę do nieba :/


Ale mam jeszcze jedno pytanie o formułę. Formuła sumująca komórki z wybranego zakresu, ale tylko te, które mają poniżej lub powyżej określoną ilość znaków (bądź cyfr, z przecinkiem lub bez). Można takie coś ułożyć?

[arras_1]

Podejrzewam że trzeba też rozbić to na dwie formuły,
1. DŁ
2. LICZ.JEŻELI lub SUMA.JEŻELI

[chris_]

Podejrzewam że trzeba też rozbić to na dwie formuły,
1. DŁ
2. LICZ.JEŻELI lub SUMA.JEŻELI

Okey, dzięki. Poradziłem sobie.

Jeszcze pytanie pośrednio związane z tematem. Czy nadając nazwy dla całych kolumn bądź wierszy jest różnica czy odnosi się do nich względnie czy bezwzględnie?

[Jan_J]

Odwołania do nazwanych zakresów mają charakter odwołań bezwzględnych.