GoldenLine
Zaloguj się
Tomasz Orlikiewicz

Tomasz Orlikiewicz Student, Uniwersytet
Warszawski

Temat: Sumowanie z nałożonym filtrem

SZybkie pytanie: czy istnieje jakaś formuła (Office 2010), która pozwalałaby na stałe sumowanie danych, na które został założony filtr, tj. jeśli usunę filtr to nadal będę miał sumę dla określonych grup komórek?
Załóżmy, że mam tabelę, w które znajduje się 300 wierszy. Część produktów mam oznaczonych odpowiednim kolorem. Chciałbym pod tabelą stworzyć podsumowanie, gdzie znalazłaby się informacja o: wartości sprzedaży produktów A (kolor czerwony), wartości sprzedaży produktów B (niebieski), wartości sprzedaży produktów G (zielony) i pozostałej wartości sprzedaży. Przy czym ważna informacja: dane muszą znajdować sie w takiej samej kolejności (nie mogę ich uszeregować od A do J).

Funkcje Suma.częściowa oraz Agreguj działają wyłącznie do momentu, w którym założony jest filtr. Tj. jeśli założę filtr, który będzie mi pokazywał tylko produkty z grupy A i użyję jednej z tych funkcji to widzę prawidłowy wynik. Jeśli usunę filtr lub założę filtr na produkty z grupy B, to od razu wyliczenia się zmieniają.

Stąd moje pytanie - czy istnieje jakakolwiek funkcja, ewentualnie inny sposób (poza makrem), niż ręczne sumowanie na zasadzie: wpisanie =Suma( , przytrzymanie klawisza ctrl i ręczny wybór komórek z które będą sumowane.
Myślałem nad dostawieniem kolumny w której znalazłaby się informacja, że np. wiersz z kolorem czerwonym to 1, z zielonym 2, z niebieskim 3, a pozostałe mają wartość 0. I nastepnie sumowanie przy użyciu funkcji Suma.jeżeli. Tylko że nie widzę możliwości zautomatyzowania tego (musiałbym ręcznie szukać czerwonych i wpisywać stosowną liczbę, itd.)Ten post został edytowany przez Autora dnia 19.07.15 o godzinie 13:02
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

może parametr 109 ci pomoże
ewentualnie SUMIF()Ten post został edytowany przez Autora dnia 19.07.15 o godzinie 15:25
Link do wypowiedziLink
Tomasz Orlikiewicz

Tomasz Orlikiewicz Student, Uniwersytet
Warszawski

Temat: Sumowanie z nałożonym filtrem

Po zdjęciu filtra pokazuje błedny wynik.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

to SUMIF() z nazwą grupy jako kryterium
lub


Function SumVisible(WorkRng As Range) As Double



Dim rng As Range

Dim total As Double

For Each rng In WorkRng

    If rng.Rows.Hidden = False And rng.Columns.Hidden = False Then

        total = total + rng.Value

    End If

Next

SumVisible = total

End Function


edit:
tak naprawdę to twoim problem jest to, że definiujesz sumowanie PO filtrowaniu a nie przed. Zakres do którego ma odnosić się SUBTOTAL() lub SUMIF() to zakres całych danych (wszystkie 300 wierszy) przed filtrowaniem a nie po.
I wtedy zwykła SUBTOTAL(109, range) działa jak należy.

Excel to złota rybka spełniająca życzenia o ile się potrafi to życzenie zdefiniować poprawnie a nie tak jak w opowieści o facecie, który chciał miec penisa do samej ziemi. I co zrobiła rybka? Zabrała mu nogi :PTen post został edytowany przez Autora dnia 19.07.15 o godzinie 16:46
Link do wypowiedziLink
Tomasz Orlikiewicz

Tomasz Orlikiewicz Student, Uniwersytet
Warszawski

Temat: Sumowanie z nałożonym filtrem

Nie zrozumiałeś mnie. Wytłumaczę trochę inaczej.
Musze zsumowac wartości z kolumny G, wiedząc, że moim jedynym kryterium jest kolor komórki w kolumnie C, np. mam zsumowac wartości z kolumny G, które odpowiadają pomalowanym na czerwono komórkom z nazwą z kolumny C.

SUMIF tego nie zrobię. A makro, które wrzuciłeś zasadniczo działa, ale do pewnego momentu. Jeśli założę inny filtr, to pokazuje wartości 0. Ten post został edytowany przez Autora dnia 19.07.15 o godzinie 18:52
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

a może tak link do pliku excela z odpowiednim przykładem? (nie obrazek)
stan przed
stan po - czyli to co chcesz uzyskać
plus ewentualnie uwagi.

możesz wykorzystać UDF, ColorIndex jako definicje kolorów i wg. indeksów warunkować sumę odpowiednich, kolorowych komórek.


Function ColorIndex(CellColor As Range)

ColorIndex = CellColor.Interior.ColorIndex

End Function

ale czekam na plik, bo tak to jest tylko gdybanie...Ten post został edytowany przez Autora dnia 19.07.15 o godzinie 19:25
Link do wypowiedziLink
Tomasz Orlikiewicz

Tomasz Orlikiewicz Student, Uniwersytet
Warszawski

Temat: Sumowanie z nałożonym filtrem

Pliku na którym pracuję przesłać Ci nie mogę, z wiadomych względów. Jest tam mnóstwo danych wrażliwych moich klientów oraz dokładne obroty. Stworzyłem przykładowy plik z danymi z kosmosu. Tutaj jest mało wierszy, więc możnaby to ręcznie zrobić, ale mówimy tutaj o pliku, który ma ponad 300 wierszy.

Wyobraź sobie taką sytuację: szef ma taką tabelę, przegląda dane na temat sprzedaży w ciągu roku i oznacza sobie nazwy firmy kolorkami, np. czerwony oznacza, że ten klient jest do usunięcia (bo np. nic nie kupił w ciągu roku), kolorem niebieskim oznacza klienta z którym musimy się skontaktować i doprecyzować pewne rzeczy, itd.

Teraz ja muszę zrobić podsumowanie takie jak na dole tego pliku. Dzisiaj robię to ręcznie, bo suma.częściowa i agreguj nie działa tak jak chciałbym, tj. Wpisuje =suma i na włączonym fltrze z controlem zaznaczam po kolei te komórki które mnie interesują. Na początku dla stycznia i potem kopiuje w prawo. To samo robię dla innych kolorów. Tych bez koloru obliczam odejmując od sumy całego zakresu to co miałem w kolorach.

Tak jak wcześniej pisałem: dla takiego małego zakresu można to robić ręcznie, ale jak ja mam 50 komórek oznaczoonych na niebiesko, 20 na czerwono i 32 na żółto to ręcznie wybierając te komórki traci się sporo czasu i energii.

Przykładowy plik - link na sendspace

W pliku jest już stan po. Stan przed wygląda podobnie tyle że bez tego podsumowania na dole.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

1.
ColorIndex() jako kolumna pomocnicza (można ukryć) i SUMIF() z warunkiem indeksu
bazując na przykładzie, gdzie col. A jest kol. pomocniczą z ColorIndex():


=SUMIF($A$2:$A$23,$A27,C$2:C$23)

Przyklad
jako, że VBA nie chce działać online to trzeba ściągnąć :) Nie edytować on-line.

2. [error był :))))))) bo uparłem sie, że komórki z wartościami liczbowymi też są kolorowe a tu patrz - nie są :-) ]Ten post został edytowany przez Autora dnia 20.07.15 o godzinie 02:26
Link do wypowiedziLink
Tomasz Zarzyka

Tomasz Zarzyka X-mart, właściciel

Temat: Sumowanie z nałożonym filtrem

Jeśli nie chcesz używać VBA to masz jeszcze jedną możliwość.
skopiuj sobie kolumnę, w której masz komórki z kolorami
W NARZĘDZIA wybierz ZAMIEŃ (szybciej skrót ctrl H)
Pole Znajdź zostaw puste, ale wybierz format, wybierając format z komórki (wybierz kolor, który chcesz oznaczyć).
w Polu zamień wpisz np. 1 - będzie to oznaczenie danego koloru.
Wybierz zamień wszystko.
Następnie wykonaj tą samą operację dla pozostałych kolorów.
dzięki temu będziesz miał w tej kolumnie numery kolorów - które dalej zastosuj w formule
suma.jeżeli. albo suma warunków.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

Tomasz,

cyt.: "szef ma taką tabelę, przegląda dane na temat sprzedaży w ciągu roku i oznacza sobie nazwy firmy kolorkami, np. czerwony oznacza, że ten klient jest do usunięcia (bo np. nic nie kupił w ciągu roku), kolorem niebieskim oznacza klienta z którym musimy się skontaktować i doprecyzować pewne rzeczy, itd. "

czyli szef dowolnie zaznacza te komórki kolorami, co wiecej:

cyt.: "Przy czym ważna informacja: dane muszą znajdować sie w takiej samej kolejności (nie mogę ich uszeregować od A do J). "

czyli sortowanie odpada ale za to można filtrować. Tylko, że kopiowanie do innej kolumny i przyporządkowywanie numerków do każdego koloru po każdej zmianie to raczej pomysł taki sobie.

Też miałem taki pomysł na poczatku jako wstęp do rozważań ale:
1. duża dowolność zmian (szef ma zawsze rację... itd.)
2. było nie było za każdym razem ręczne sterowanie.
3. 300 wierszy to niby mało ale przy kilku kolorkach w jednej kolumnie, powtarzających sie z różną częstotliwościa to można dostac oczopląsu.

Jeżeli kolorów masz dwa, trzy to można się pokusić na twoje rozwiązanie ale co jeśli masz tych kolorów więcej? Zamienił stryjek siekierke na kijek. Handjob cały czas.Ten post został edytowany przez Autora dnia 20.07.15 o godzinie 10:24
Link do wypowiedziLink
Tomasz Zarzyka

Tomasz Zarzyka X-mart, właściciel

Temat: Sumowanie z nałożonym filtrem

Andi,
zgadzam sie w 100% - to co proponujesz jest z punktu widzenia funkcjonalnego najlepszym rozwiązaniem, ja zaznaczyłem tylko, że jest to rozwiązanie dla kogoś, kto nie działa w VBA.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

Tomasz Z.:
ja zaznaczyłem tylko, że jest to rozwiązanie dla kogoś, kto nie działa w VBA.

Nie ma problemu :) Ja mam awersję do VBA ale... są przypadki, że inaczej się nie da. Może w kolejnej wersji excela powiążą jakąś funkcję z kolorkami co nie było by takim złym pomysłem.Ten post został edytowany przez Autora dnia 20.07.15 o godzinie 11:00
Link do wypowiedziLink
Tomasz Zarzyka

Tomasz Zarzyka X-mart, właściciel

Temat: Sumowanie z nałożonym filtrem

Nie ma problemu :) Ja mam awersję do VBA ale... są przypadki,

Ja wręcz przeciwnie - jak się tylko da odchodzę od formuł na rzecz kodu.
Wtedy pliki są znacznie mniejsze.
Ostatnio robiłem programy do rozliczania produkcji, w których nie ma żadnej formuły, żadnego formatowania warunkowego.
Przynajmniej nikt mi niczego nie zepsuje :)
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

Tomasz Z.:
Przynajmniej nikt mi niczego nie zepsuje :)

to ci się tylko tak wydaje :-))))))
Link do wypowiedziLink
Tomasz Orlikiewicz

Tomasz Orlikiewicz Student, Uniwersytet
Warszawski

Temat: Sumowanie z nałożonym filtrem

@Andy Levi

Dzięki za pomoc. Twoje rozwiązanie usprawni mi pracę.

@Tomasz Zarzyka

Twoje rozwiązanie jest logiczne i byłoby ok, ale problem polega na tym, że szef może sobie taki plik odpalić dwa dni później i stwierdzić, że klient X, do którego nie miał zastrzeżeń jednak będzie usunięty bo się okazuje, że co prawda kupuje dużo, ale mocno zalega z płatnościami. I wtedy należałoby w zasadzie całą procedurę przechodzić od nowa.

Napisałem na początku, że wolałbym rozwiązanie bez VBA bo są to pliki na których pracuję ja i mój szef. A mój szef to człowiek starej daty i on po prostu pewnych rzeczy nie ogarnia. Jak mam do niego co 5 minut biegać i mu tłumaczyć np. że żeby makro działało to trzeba po otworzeniu pliku włączyć makra, etc. to po prostu mija się to z celem.
Sam napisałem makro, które tworzy funkcję pozwalającą sumować po kolorze, tyle że ja mam w pliku oznaczoną kolorem tylko kolumnę z nazwą klienta. Gdybym chciał zastosować swoje makro, to musiałbym najpierw ręcznie pokolorować też odpowiednie kolumny i wiersze z danymi liczbowymi, a to trochę trwa (nawet z założonym filtrem, bo co prawda pisałem o 300 wierszach, ale w jednym arkuszu. A arkuszy jest kilka. W jednym skoroszycie. Bo skoroszytów jest kilkanaście :D).

Jeszcze raz dzięki wielkie za pomoc.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

Zapomniałem usunąc przycisk do odświeżania - nie jest potrzebny bo po każdej zmianie koloru i kliknieciu innej komórki arkusz odświeża sie sam.

Co do VBA nigdzie nie zauważyłem aby VBA było w jakikolwiek sposób oprotestowane, zresztą przykładowy plik jest plikiem .xlsm (macro workbook) co sugeruje mozliwość uzycia vba.

Jeżeli moje rozwiązanie pomogło to OK. :)
Link do wypowiedziLink
Tomasz Orlikiewicz

Tomasz Orlikiewicz Student, Uniwersytet
Warszawski

Temat: Sumowanie z nałożonym filtrem

Zauważyłem tylko jeden drobny problem: czasem pokazuje mi, że komórka w ogóle niepokolorowana ma numer 2, a raz się zdarzyło, że miała numer -4320. A to powodowało, że nie zliczało mi danych z jednej komórki. Myślałem, że jest jakieś inne formatowanie nałożone, więc ręcznie zmieniłem kolor, ale nic to nie dało. Dopiero jak całkowicie usunąłem to co znajdowało się w komórce i wpisałem od nowa "z palca" to ColorIndex zmienił się na 2.
Link do wypowiedziLink
Andy L.

Andy L. ITM, VUB

Temat: Sumowanie z nałożonym filtrem

2 to kolor biały
-4142 to kolor "brak koloru" (co nie jest równoznaczne z białym)
niektóre kolory mają ten sam numerek, np. ciemnoczerwony (powiedzmy: brązowy) i czerwony [3]
trudno z twojego opisu wywnioskować dlaczego tak jest.
Zdefiniuj sobie palete kolorów gdzieś w osobnym arkuszu, użyj funkcji ColorIndex() i spróbuj powtórzyć błąd o którym piszesz.Ten post został edytowany przez Autora dnia 21.07.15 o godzinie 20:53
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

sumowanie wartosci powyzej np8




Wyślij zaproszenie do
Anuluj