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 - listy unikatów i sumowanie właściwych im wartości

lidanthyg 07 Cze 2014 19:24 3090 7
  • #1 07 Cze 2014 19:24
    lidanthyg
    Poziom 2  

    Szanowni,

    maksymalnie logicznie jak potrafię, mój problem wygląda tak:
    mam bazę danych tworzoną chronologicznie w zależności od daty i godziny wydarzenia. Wydarzeniom nadałam tytuły (w pliku to imiona). Wydarzenie może się powtarzać, ale na liście ma miejsce zależne od czasu (tzn., że nie mogę wydarzeń posortować). Wydarzenia odbywają się w salach, które mają różną liczbę miejsc. Dane wydarzenie nie zawsze odbywa się na tej samej sali. Wydarzenia mają też dystrybutora - dane wydarzenie zawsze tego samego. Ta baza dotyczy każdego z wydarzeń jednorazowo z osobna.
    Chcę mieć bazę zbiorczą, która:
    stworzy listę unikatów, umieszczając je kolejno pod sobą, bez pustych wierszy. Wydarzeniom przypisze dystrybutora w sąsiedniej kolumnie. Zliczy sumę udostępnionych miejsc dla danego wydarzenia na wszystkich jego odsłonach (chcę móc później liczyć frekwencję).
    Co "udało mi się" zrobić:
    mam dwie odsłony listy, obydwie tworzone na podstawie odrębnych kolumn pomocniczych. Jedna z list jest dobra, ale chciałabym móc jej kolumnę pomocniczą wykorzystać do zliczania dostępnych miejsc. Nie da się tego zrobić, bo unikaty mają za każdym razem nowe numerki, ale jeśli dane wydarzenie się powtarza, otrzymuje inny numerek niż za pierwszym razem. Druga lista nadaje zawsze te same numerki, ale unikaty nie dostają kolejnych numerków, tylko takie zależne od numeru wiersza, co przy liście unikatów tworzy puste miejsca.
    Z dystrybutorem chyba sobie poradziłam. Nie potrafię stworzyć idealnej formuły dla listy unikatów i dostępnych miejsc w oparciu o jedną kolumnę pomocniczą. (Na zliczającą miejsca w zasadzie wcale nie mam pomysłu). Potrzebne są formuły, które po wyczyszczeniu zmiennych, pozwolą arkuszowi funkcjonować jako szablon dla kolejnych.
    Załączam plik, w którym: zieloną czcionką oznaczono wprowadzane dane (zmienne), które potrzebuję wykorzystać. Czarny to wprowadzane dane (zmienne), których później nie wykorzystuję. Czerwonym formuły do zmiany albo do napisania. Na niebiesko podświetliłam unikaty/pierwsze wystąpienia. Plik jest opatrzony komentarzami wyjaśniającymi problemy.
    Proszę o pomoc, jeśli ktoś ma pomysł jak powyższe rozwiązać.
    Bardzo dziękuję i pozdrawiam.

    0 7
  • #2 07 Cze 2014 22:10
    marcinj12
    Poziom 40  

    Klasyczny przykład na zastosowanie tabeli przestawnej - o ile nie przeszkadza Ci konieczność jej odświeżania po każdej zmianie w danych źródłowych. Kwestia drobnej przeróbki danych i odpowiedniego sformatowania tabeli.

    0
  • Pomocny post
    #3 08 Cze 2014 15:29
    Maciej Gonet
    Poziom 31  

    Dzień dobry,
    Problem można też rozwiązać samymi formułami, nie potrzeba żadnych kolumn pomocniczych. Do znalezienia unikatów można użyć formuły tablicowej:

    Kod: text
    Zaloguj się, aby zobaczyć kod

    gdzie: $D$8:$D$57 - zakres danych (tytuły), 
             $F$63:$F63 - zakres wyników (unikaty).
    Początek zakresu wyników jest adresem absolutnym komórki powyżej pierwszego wyniku, koniec zakresu wyników jest adresem względnym lub mieszanym komórki bezpośrednio nad komórką, do której wprowadzana jest formuła.
    Formułę wprowadzamy do jednej komórki, zatwierdzamy przez Ctrl+Shift+Enter, następnie kopiujemy w dół. Formuły tablicowej nie można umieszczać w komórkach scalonych. Ewentualnie po wprowadzeniu formuły tablicowej do jednej komórki można ją scalić z innymi komórkami.
    Sumę miejsc obliczamy za pomocą formuły zwykłej:
    Kod: text
    Zaloguj się, aby zobaczyć kod

    gdzie: F64 - tytuł bieżący,
             $N$8:$N$57 - zakres danych do sumowania.
    Formuły wyznaczające dystrybutora są poprawne, tu już nic nie da się uprościć.
    Formuły nie są zabezpieczone przed wyświetlaniem błędów. Można dodać:
    Kod: text
    Zaloguj się, aby zobaczyć kod

    Zero pojawiające się na końcu listy unikatów jest związane z pustymi komórkami na liście danych. Mam nadzieję, że nie będzie bardzo przeszkadzać. Gdyby jednak, to można to oczywiście wyeliminować albo rezygnując z wyświetlania zer w arkuszu, albo komplikując trochę formuły.
    Mam nadzieję, że takie rozwiązanie jest do przyjęcia.
    P.S. W pliku są odwołania do niedostępnych skoroszytów. Proszę na to uważać.

    0
  • #4 08 Cze 2014 22:50
    lidanthyg
    Poziom 2  

    Bardzo dziękuję za odpowiedzi.

    Tabela przestawna ma chyba zbyt dużo ograniczeń i wymaga zbyt dużej ingerencji w kształt arkusza, który jest mocno powiązany z innymi. Obawiałabym się, że może wywołać lawinę zmian. Dodatkowo to nie ja będę w przyszłości obsługiwać ten plik, dlatego wolę formuły, które mogę zablokować i ograniczyć pracę na arkuszu do przepisania kilku danych. Mimo wszystko dziękuję.

    Z kolei zaproponowane formuły są cudowne i robią wszystko, czego potrzebowałam. Obejście kolumny pomocniczej też jest dużą wygodą. Nie wiem jak mogłam nie wpaść na tak prosty sposób liczenia miejsc. Chyba zrzucę to na zmęczenie materiału, żeby się nie załamać. Dobrze, że udała mi się chociaż formuła dla dystrybutora...
    Wszystkie błędy pousuwam sobie na końcu. Proszę nie martwić się o niedostępne skoroszyty - wszystko działa w pliku źródłowym. Ten załączony był tylko wycinkiem demonstracyjnym, a komórek z niedostępnymi odwołaniami nie było potrzeby na tę okoliczność poprawiać.

    Mam jeszcze jedno pytanie, ale to już raczej z ciekawości. Napisałam sobie formułę liczącą VAT, ale jest bardzo długa. Na załączonym arkuszu jeszcze da się przeżyć, ale mam podobne gdzie jest około 35 rodzajów wstępów od których trzeba policzyć ten podatek. Dodatkowo w jednym pliku jest ponad 50 takich arkuszy. Czy da się ją jakoś skrócić? Formuła odwołuje się do listy, która przyporządkowuje odpowiednią stawkę VAT do nazwy biletu. Nie kopiowałam jej do pliku, ale wydaje mi się, że nie przeszkadza to w analizie formuły. Działa to po prostu tak, że wybierając nazwę biletu z listy, poniżej wskakuje jego cena, która razem z liczbą takich biletów i wywołaniem z owej listy odpowiedniej stawki VAT liczy kwotę podatku dla danego rodzaju biletu i sumuje je dla wszystkich rodzajów sprzedanych biletów.

    0
  • #5 09 Cze 2014 02:20
    Maciej Gonet
    Poziom 31  

    Proszę o przykład z danymi, na tym nie da się porównać wyników.

    0
  • Pomocny post
    #7 09 Cze 2014 23:40
    Maciej Gonet
    Poziom 31  

    Dobry wieczór,
    Formułę można uprościć zastępując sumy podobnych wyrażeń formułą tablicową. Formułę wprowadza się do jednej komórki klawiszami Ctrl+Shift+Enter, a później kopiuje w dół. Mam wątpliwość co do celowości liczenia VAT w przypadku biletów bezpłatnych. O ile się nie mylę zawsze w wyniku otrzymuje się zero, a te obliczenia to połowa formuły. Szczegóły w załączniku.
    Pozdrowienia.

    0
  • #8 10 Cze 2014 15:07
    lidanthyg
    Poziom 2  

    Bardzo dziękuję - wszystko działa pięknie.
    Przerobiłam i zastosowałam formułę na wszystkich pozostałych arkuszach - wydaje mi się, że plik szybciej działa.
    Biletach bezpłatnych VAT wychodzi oczywiście 0zł, ale póki co formułę konstruowałam jako dotyczącą również tych wejść, bo nie wiem jeszcze jak finalnie rozstrzygnę ich kwestię. Jako bezpłatne te wejścia widać tylko w tym raporcie; niektóre z nich wykupuje się wcześniej jako karnet. Jeszcze nie mam pomysłu jak ugryźć to w raporcie podsumowującym. Przemyślę i jeśli do niczego nie będzie mi potrzebne to obliczenie, to oczywiście skorzystam z rady i usunę zbędną część formuły.
    Tymczasem jeszcze raz dziękuję i chylę czoła.
    Pozdrawiam

    0