Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 55457 Skopiuj do schowka Średnia z wybranych pozycji wektora
Autor Wiadomość
orysh 
świeżak


Posty: 4
Wysłany: 03-01-2017, 15:22   Średnia z wybranych pozycji wektora

Dzień dobry,

Próbuję napisać formułę która obliczałaby średnią z określonych pozycji wektora wyznaczanego jako iloczyn tabel.
Pozycje te wyznaczane są jako zadana liczba największych wartości innego wektora, wyznaczanego podobnie.
O co chodzi:
Mam tabelę "Tab" która składa się z czterech kolumn K1, K2, A, B
Wyznaczam sobie wektor "tab_a" z tablicy A który zawiera tylko elementy spełniające zadane kryteria w kolumnach K1 i K2

Kod:
tab_a   -   (Tab[K1]="a")*(Tab[K2]="e")*Tab[A]
następnie wyznaczam z niego "ile_max" największych wartości "max_k". Ich ilość może się zmieniać dlatego potrzebuję wektora pomocniczego

Kod:
ile_max   -   WIERSZ(ADR.POŚR("1:"&$B$3))

max_k   -   MAX.K(tab_a;ile_max)


określam ich "pozycje" w wektorze "tab_a"

Kod:
pozycje   -   PODAJ.POZYCJĘ(max_k;tab_a;0)
i dla tych pozycji próbuję policzyć średnią w wektora "tab_b"

Kod:
tab_b   -   (Tab[K1]=$B$1)*(Tab[K2]=$B$2)*Tab[B]

ŚREDNIA(INDEKS(tab_b; pozycje))


Problem polega na tym że funkcja indeks bierze pod uwagę tylko pierwszą pozycję z wektora "pozycje" i średnia nie jest liczona :-(

Jak sobie z tym poradzić? Nie chciałbym zaprzęgać do tego VB.
Wszystkie formuły zatwierdzam tablicowo, do momentu wyznaczania pozycji wszystko jest ok.

Z góry dziękuję za pomoc i pozdrawiam
Grzegorz

Zeszyt1.xlsx
Pobierz Plik ściągnięto 18 raz(y) 13.9 KB

ID posta: 311955 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1107 razy
Posty: 3818
Wysłany: 03-01-2017, 23:56   

Nie opisujesz swoich danych, więc pytam: widzę, że wartości w kolumnie B = wartości w kolumnie A * 100. Tak zawsze jest? No bo trudno przyjąć, że tak wyszło, takie niezamierzone przypadki w pisaniu dla 31 wierszy. Jeśli tak faktycznie jest to zamiast obliczyc średnią dla B obliczymy średnią dla A pomnożoną przez 100.
Kod:

=100*SUMA(MAX.K((Tab[K1]=$B$1)*(Tab[K2]=$B$2)*Tab[A];WIERSZ(ADR.POŚR("1:"&$B$3))))/$B$3

zakończona Ctrl+Shift+Enter
----------
Tak na marginesie: wartości w A to zawsze liczby całkowite dodatnie? Zawsze opisuj dane bo być może takie informacje są pożyteczne. Np. ktoś ma jakiś pomysł, który akceptuje tylko liczby całkowite dodatnie. Gdybyś opisał dane to ten ktoś wiedziałby, czy jego pomysł może być zrealizowany
ID posta: 311974 Skopiuj do schowka
 
 
magbo 
Excel Expert



Pomogła: 212 razy
Posty: 614
Wysłany: 04-01-2017, 00:13   

W przykładowych danych wartości w kolumnie/wektorze A są unikalne - jeśli tak nie będzie , twój algorytm może wyliczyć zła średnią.
Na przykładowych danych zadziała:
Kod:
=ŚREDNIA(JEŻELI(((Tab[K1]=$B$1)*(Tab[K2]=$B$2)*(Tab[A]>=MAX.K(Tab[A]*(Tab[K1]=$B$1)*(Tab[K2]=$B$2);$B$3)));Tab[B];""))
ID posta: 311976 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 888 razy
Posty: 3032
Wysłany: 04-01-2017, 10:26   

Nie odnoszę się do dwóch ostatnich wypowiedzi, te pomysły pewnie są dobre (nie sprawdzałem), ale realizują nieco inną koncepcję obliczeń. Autor przedstawił przykład swojego rozumowania, które zapewne chce wykorzystać do innych danych o podobnym układzie. Nie działa poprawnie tylko jeden element - funkcja INDEKS. Ta funkcja tak ma, została napisana, nie wiem czy celowo, czy "tak wyszło", że źle sobie radzi z argumentami indeksowymi w postaci tablic. Ale jest na to rada. Pierwszy podpowiedział mi ją szbill62, któremu winienem za to dozgonną wdzięczność :-) , ale teraz mogę się nią podzielić. Należy mianowicie "opakować" tablicę pozycje w funkcję N (od 2010, w starszych Excelach L):
Kod:
=ŚREDNIA(INDEKS(tab_b; N(pozycje)))

To zadziała, gdy pozycje są nazwą tablicy, a nie zakresu. W przypadku zakresu lub tablicy nienazwanej, formułka bardziej się komplikuje, ale też można ją zastosować.

Poz_śr.xlsx
Pobierz Plik ściągnięto 10 raz(y) 14.39 KB

ID posta: 311994 Skopiuj do schowka
 
 
orysh 
świeżak


Posty: 4
Wysłany: 04-01-2017, 17:18   

Dziękuję za podpowiedzi.

Jeśli chodzi o dane w tabeli to w kolumnach A i B wartości nie są unikatowe. Na pewno są posortowane najpierw malejąco w kolumnie A potem malejąco w kolumnie B.

Powtarzanie się wartości w obu kolumnach powoduje problemy w obu popozycjach

przy wykorzystaniu SZUKAJ i JEŻELI, w przypadku gdy k-ta największa wartość w kolumnie A powtarza się do średniej brana jest większa ilość pozycji z kolumny B niż ta zadana w komórce B3.

Z kolei przy wykorzystaniu funkcji INDEKS i w moim przypadku L, problem stanowi funkcja PODAJ.POZYCJĘ która zawsze zwraca pozycję pierwszego wystąpienia danej wartości.

Patrząc na obecnie uzyskiwane wyniki zastanawiam się czy dałoby się z wektora wynikowego funkcji SZUKAJ i JEŻELI policzyć średnią z zadanej ilość pierwszych, niepustych elementów.

Zeszyt1.xlsx
Pobierz Plik ściągnięto 10 raz(y) 14.17 KB

ID posta: 312073 Skopiuj do schowka
 
 
orysh 
świeżak


Posty: 4
Wysłany: 04-01-2017, 17:37   

Dziękuję za podpowiedzi.

Jeśli chodzi o dane w tabeli to w kolumnach A i B wartości nie są unikatowe i nie są powiązane ze sobą np. B=A*100. Na pewno są posortowane najpierw malejąco w kolumnie A potem malejąco w kolumnie B.

Powtarzanie się wartości w kolumnie A powoduje problemy w obu propozycjach

przy wykorzystaniu SZUKAJ i JEŻELI, w przypadku gdy k-ta największa wartość w kolumnie A powtarza się do średniej brana jest większa ilość pozycji z kolumny B niż ta zadana w komórce B3.

Z kolei przy wykorzystaniu funkcji INDEKS i w moim przypadku L, problem stanowi funkcja PODAJ.POZYCJĘ która zawsze zwraca pozycję pierwszego wystąpienia danej wartości.

Patrząc na obecnie uzyskiwane wyniki zastanawiam się czy dałoby się z wektora wynikowego funkcji SZUKAJ i JEŻELI policzyć średnią z zadanej ilość pierwszych, niepustych elementów.

Zeszyt1.xlsx
Pobierz Plik ściągnięto 5 raz(y) 14.17 KB

ID posta: 312074 Skopiuj do schowka
 
 
magbo 
Excel Expert



Pomogła: 212 razy
Posty: 614
Wysłany: 05-01-2017, 00:27   

Tak naprawdę pytanie brzmi: dlaczego formuła?
Stosunkowo proste byłoby zastosowanie filtru zaawansowanego, żeby w pomocniczym arkuszu uzyskać dane spełniające warunki i posortowac je malejąco. Wtedy obliczanie średniej z n pierwszych pozycji byłoby banalnie.
ID posta: 312108 Skopiuj do schowka
 
 
orysh 
świeżak


Posty: 4
Wysłany: 05-01-2017, 15:06   

właśnie do tej pory robię to za pomocą autofiltra, ale z uwagi na duża ilość różnych kryteriów i wartości do policzenia, takie liczenie na piechotę zajmuje dużo czasu i chciałem to sobie chociaż trochę zautomatyzować.
ID posta: 312223 Skopiuj do schowka
 
 
magbo 
Excel Expert



Pomogła: 212 razy
Posty: 614
Wysłany: 10-01-2017, 23:19   

Sorry że późno odpowiadam, mam sporo zajęć.
Skoro chodzi o czas realizacji zadania, to zamiast kombinować z formułami, zrób prostą tabelę przestawną. przykład masz w załączniku. Wystarczy ustawić filtry dla kolumn K1 i K2 , a wyświetlanie dla etykiet wierszy ograniczyć do odpowiedniej liczby największych wartości - domyślnie jest 10, można ustawić dowolną liczbę, kolumna "średnia z A" dodana jest tylko po to, żeby wg niej wybierać.
Aha, zdecydowałam za ciebie - w przypadku wielokrotnych wartości do średniej wejdą wszystkie powtórzenia. Jak chcesz mieć inaczej - podaj algorytm wybierania spośród powtarzających się wartości kol. A tych , które mają trafić do średniej

Zeszyt1s.xlsx
Pobierz Plik ściągnięto 5 raz(y) 19.25 KB

ID posta: 312619 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum