Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

[Excel] sumowanie max nie sortowanych danych

Korek_123 13 Gru 2014 13:52 903 6
  • #1 13 Gru 2014 13:52
    Korek_123
    Poziom 11  

    Witam,
    Mam prośbę o pomoc w zoptymalizowaniu formuły.

    Otrzymuje arkusze z danymi – w kolumnie E, są nieposortowane dane może ich być kilkaset. Potrzebuje obliczyć jaki jest udział wyznaczonej liczby np. 1,5,10,20 (to się może zmieniać) największych wartości w sumie wszystkich danych.

    Można to oczywiście robić przez ręczne posortowanie i potem sumowanie – ale jeśli trzeba to robić kilkanaście razu, to moim zdaniem warto jakoś zautomatyzować pracę.

    W załączonym przykładzie stworzyłem taki mechanizm, który automatycznie liczy dla wyznaczonych progów i listy danych. Niestety jest to zrobione w mojej ocenie nie optymalnie (z wykorzystaniem, adr.pośr, max.k, wyszukaj.pionowo, przesunięcie).

    Dlatego mam uprzejmą prośbę, czy ktoś lepiej znający się na excel’u może rzucić okiem na załączony przykład i zaproponować optymalizację – czego szukam – zapisania w jak najmniejszej liczbie komórek wszystkich zmiennych i formuł obliczeniowych – tak aby taki „blok” móc łatwo wklejać do kolejnych arkuszy i zakładek (zmieniać kilka parametrów) i szybko generować obliczenie.

    Pozdrawiam,

    0 6
  • #2 13 Gru 2014 14:39
    marek003
    Poziom 40  

    Tak się zapytam przy temacie:
    A nie lepiej zamiast wklejać te obliczenia do innych arkuszy to pozostawić ten arkusz bez zmian (jako tzw czysty) i wklejać tylko kolumnę z danymi zapisując później pod inną nazwą.?

    A może zrobić arkusz wyliczeniowy który pobiera zwykłymi "=" dane z arkusza w którym są dane źródłowe. Potem tylko podmieniać plik/skoroszyt z danymi, oczywiście podmieniać dopiero po wyłączeniu arkusza z obliczeniami i plik podmieniany musiał by mieć taką samą nazwę.

    0
  • #3 13 Gru 2014 19:12
    Korek_123
    Poziom 11  

    Witam,

    Choć to najprostsze rozwiązanie - niestety nie, ten element to tylko jeden z kilku elementów analizy i niestety obliczenie musi zostać umieszczone (wklejone) w źródłowym pliku z danymi ;-(

    Pozdrawiam,

    0
  • Pomocny post
    #4 15 Gru 2014 08:18
    Maciej Gonet
    Poziom 32  

    Dzień dobry,
    Trudno określić, jakie dane i obliczenia mają się znaleźć w końcowej wersji pliku. W załączonym pliku obliczenia udziałów bez kolumn pomocniczych z formułą wn, zdefiniowaną w menedżerze nazw. Formuły są tablicowe jednokomórkowe z możliwością kopiowania w dół. Przy przenoszeniu danych proszę zwrócić uwagę na położenie ciągu liczb określających sposób grupowania. W zależności w którym wierszu ma się zaczynać kolumna z wynikami, trzeba odpowiednio zmodyfikować adres w formule wn.
    Pozdrowienia

    0
  • #5 17 Gru 2014 23:05
    Korek_123
    Poziom 11  

    Witam
    @Maciej Gonet

    Bardzo dziękuje za pomoc !!! – bardzo fajne rozwiązanie ;-) ale mam kilka pytań ;-0

    1. Jak wpisać „formułę tablicową” do Formuły / Menedżer nazw ?

    2. Czy bazując na tej samej metodzie można stworzyć mechanizm, który będzie pozwalał na „automatyczne” tworzenie wykresów w zależności od wybranych zakresów danych ?

    Próbowałem ale mi się nie udało ;-( … prośba o pomoc.

    W załączeniu przykład z danymi, plik: „definiowany_wykres_17122014”.

    W pliku zdefiniowałem nazwy w „Menedżerze nazw” i zakresy danych w arkuszu.[dla wykresu otoczonego grubą czerwoną ramką w przykładzie [w okolicy T31 ;-D] - gdy zmieniam parametry zakresu danych w E55 i w E68 (wpisuje np. odpowiednio „5” i „12”) a następnie:

    a. klikam na wykres w czerwonej ramce (prawy przycisk myszy),
    b. wybieram „Zaznacz dane”,
    c. zamiast „=testowy_1!$C$9:$N$13” wpisuję” =testowy_1!wykres_1” (nazwa jest już zdefiniowana)

    wtedy wykres rysuje się z nowo zdefiniowanym zakresem danych.

    Ale niestety jeżeli tylko zmieniam wartość wE68 np. z „12” na „13” – to wykres nie aktualizuje się automatycznie! – Jak zrobić aby wykres aktualizował się automatycznie – po zmianie wartości w E55 albo E68? – bardzo proszę o podpowiedź.

    3. Czy można „mechanizm” usprawnić tak aby pozwalał na zmianę arkuszy z danymi do wykresu pomiędzy: testowy_1 / testowy_2 / testowy_3 – jak to zrobić ?

    4. W formułach które napisałeś do wcześniejszego przykładu nie rozumiem działania:

    wn.1 =WIERSZ(ADR.POŚR("1:"&sumowanie!$K10))
    wn= =WIERSZ(ADR.POŚR("1:"&sumowanie!$K21))

    nazwa zakładki danych „sumowanie” jest wpisana na „sztywno” czy to można wpisać jako parametr / zmienną?

    Jeszcze raz bardzo dziękuję za pomoc !!! i proszę o jeszcze ;-)

    Pozdrawiam

    0
  • #6 18 Gru 2014 17:47
    Maciej Gonet
    Poziom 32  

    Dobry wieczór,
    Trochę dużo tych pytań jak na jeden raz, ale spróbuję.
    Ad 1) Nie bardzo rozumiem pytanie. Każdą formułę wpisuje się do Menedżera nazw tak samo i zatwierdza przez Enter. Każda formuła wpisana do Menedżera nazw jest traktowana jak formuła tablicowa.
    Ad 2) Wykresy można zautomatyzować, ale jest to trochę żmudne i gdy konstrukcja jest zbyt złożona lubi się czasem "posypać". Wszystkie zakresy w wykresie dynamicznym muszą być zdefiniowane przez nazwy kwalifikowane (poprzedzone nazwą skoroszytu lub arkusza i wykrzyknikiem). Nie należy wpisywać nazwy do "Zanacz dane", bo to nic nie da. Ten zakres jest "tłumaczony" na adresy i działa tylko jednorazowo. Definicje muszą być wpisane osobno do każdej serii wykresu - liczba serii powinna być stała, ewentualnie można przypisać do serii pusty zakres. Czyli dla każdej serii trzeba zdefiniować nazwę (jeśli potrzebna), wartości serii i wartości etykiet.
    Ad 3) Tak jak Pan robił jest dobrze, te zakresy są poprawnie zdefiniowane i to powinno działać, jak nada się nazwy. Problem tylko w tym, że Pan to chciał robić "hurtem" dla całego wykresu, a trzeba osobno dla każdej serii.
    Ad 4) Formuły cytowane zwracają kolumny liczb (formalnie numery wierszy) od 1 do liczby wpisanej do komórki np. sumowanie!$K10. Nazwa arkusza nie musi być wpisana "na sztywno" - można by było posłużyć się zagnieżdżoną formułą ADR.POŚR, np. tak:

    Kod: text
    Zaloguj się, aby zobaczyć kod
    Ale ideą tej formuły było, żeby adres wiersza był względny i zmieniał się przy kopiowaniu w dół. W tym wzorze "10" jest już tekstem, więc nie będzie modyfikowane. Trzeba użyć bardziej złożonej formuły, coś w tym rodzaju:
    Kod: text
    Zaloguj się, aby zobaczyć kod

    Proszę sobie to sprawdzić, bo arkusz jest rozbudowany i nie miałem czasu, żeby to wszystko sprawdzać w działaniu. Jakby coś nie działało, to proszę pytać, ale raczej nie o wszystko naraz.
    Pozdrowienia.

    0
  • #7 26 Sty 2016 21:20
    Korek_123
    Poziom 11  

    Bardzo dziękuje za pomoc !

    0