[solved] Zliczanie ciągów liczb i systematyzowanie danych.

[JedenGość]

Mam kolejną rozkminkę, z którą sobie nie mogę dać rady.

Chodzi o zliczanie ilości liczb występujących w ciągu oraz zamieszczanie wyniku w odpowiednich miejscach (systematyzowanie danych).

Szczegóły w załączniku.

Wiem, że na tym forum są prawdziwi specjaliści od tego typu zagadek

Z góry dziękuję za pomoc.

[Jermor]

Rozwiązałem ten problem następująco:
W pierwszym arkuszu zamieniłem wpisy w kolumnie D na wartości 0/1
Nadałem nazwy danym: "nazwy" przypisałem do wpisów w kolumnie A, "yn" do wpisów w kolumnie D.
W "Arkusz2" w kolumnie B utworzyłem, przy pomocy formuły macierzowej, ciągi tekstowe odpowiadające układowi zer i jedynek.
Od kolumny E do K znajdują się formuły obliczeniowe.
Odrębna formuła znajduje się w kolumnie E, wyznacza ona jaki długi jest ciąg znaków takich samych jak pierwszy znak.
W kolumnie F jest formuła, którą można kopiować do kolejnych kolumn. Wyodrębnia ona z całego ciągu pozostałą część, za ciągiem już przeanalizowanym i znajduje długość ciągu, którego każdy znak jest taki sam jak pierwszy znak tego skróconego ciągu.
Do tego obszaru zastosowałem formatowanie warunkowe, wykorzystując wbudowane już style "Bad" i "Good" do wyróżnienia wartości reprezentujących ciągi zer i jedynek.

[JedenGość]

Wiem, że na tym forum są prawdziwi specjaliści od tego typu zagadek

Wiedziałem, że na Ciebie mogę liczyć

Przekładam wszystko to co stworzyłeś na czynniki pierwsze "po mojemu".

Pierwsze co to osobiście ni lubię stosować nazw, ponieważ bardziej do mnie przemawia jak widzę konkretne określenia odwołań, kolumn, wierszy etc.

Na szybko zmieniłem formułę w B1 Arkusza 2 na taką:

Kod: Zaznacz cały

=POŁĄCZ.TEKSTY("";1;JEŻELI($A1=Arkusz1.$A$2:$A$463 ;Arkusz1.$D$2:$D$463 ))

I błąd Value. Możesz mi wyjaśnić dlaczego?

Edit. Sorry, już wiem. ctrl+shift+enter...

[Jermor]

Tak w ogóle, jeżeli zamieniasz nazwy, to nie przerabiaj formuł. Skorzystaj z możliwości "Znajdź i zamień".
Zaznacz obszar, w którym znajdują się formuły. Wybierz "Znajdź i zamień" (albo CTRL+H). W polu "Znajdź" wpisz nazwę obszaru, w polu "Zamień" potrzebny obszar, np "$Arkusz1.$A$2:$A$463", w opcjach upewnij się, że zmiany są tylko dla bieżącego zaznaczenia a w polu "Szukaj w" wybierz "Formuły".
W takim przypadku warto wyłączyć także opcję "Wyrażenia regularne", gdyż niektóre znaki i te wyszukiwane, i zamienniki, mogą mieć inne działanie.

[JedenGość]

Racja. Znam tę opcję, wiele razy jej używałem. Faktycznie, w ten sposób uniknę przypadkowego skasowania np. jakiegoś średnika.

Przekonwertowałem Twoje rozwiązanie pod swoje potrzeby. Prawie wszystko gra.

Kiedyś pomogłeś mi rozwiązać problemy w tej części tego wątku.
https://forum.openoffice.org/pl/forum/v ... 935#p21296

Stworzyłeś rozwiązanie, które opiera się na ujemnych liczbach czerwonych. Gdybym chciał rozwiązanie z tego tematu łączyć z tamtym to jest problem, ponieważ tu liczby czerwone nie są ujemne.

Inną kwestią jest, że chciałbym wykonać dwa dodatkowe działania:
1) Sumę liczb np. w wierszu E1-K1. Tu jest ok. Wynik: 21
2) Sumę liczb zielonych. Prostą metodą F1+H1+J1. Wynik: #VALUE!
Jednak czasami liczba zielona będzie jako pierwsza w kolumnie E. Jaką formułę zastosować, aby sumowało same liczby zielone?

[Jermor]

Nie wykorzystałem możliwości wstawiania liczb ujemnych dla rozróżnienia ciągów 0. Dlatego, że nie chciało mi się komplikować formuł, a o ewentualnych próbach sumowania nic nie napisałeś. Obliczona liczba zer i jedynek jest w formule wykorzystana, jako suma dotychczasowych liczb, do wyznaczenia początku nowego podciągu znakowego. Dlatego ta wartość nie może być ujemna.
Wynik #VALUE! pojawia się gdyż jednym ze składników dodawania jest tekst. Funkcja JEŻELI.BŁĄD wstawia "" do tych komórek, które nie mogą już być wyliczone.
W formułach nie można wykorzystać koloru czcionki lub koloru tła jako elementu pozwalającego warunkować obliczenia. Dlatego zmodyfikowałem rozwiązanie.
Usunąłem z niego wszystkie formatowania warunkowe. Zmodyfikowałem formuły następująco:
Formuły w kolumnie E: wynik formuły jest mnożony przez -1 wtedy gdy pierwszym znakiem ciągu jest 0, a przez 1, gdy jest nim jedynka. Dzięki temu wynik jest ujemny, gdy pierwszym ciągiem są zera albo dodatni, gdy tym ciągiem są jedynki.
W kolumnach F do K: Wszystkie formuły są teraz formułami macierzowymi. Musiałem tak zrobić bowiem aby uwzględnić wszystkie liczby jako dodatnie zastosowałem funkcję MODUŁ.LICZBY wewnątrz funkcji SUMA, natomiast wynik obliczenia mnożę przez 1 albo -1 w zależności od tego jaką liczbą jest liczba w poprzedzającej komórce. Z twojego algorytmu wynika bowiem, że zawsze liczby występują przemiennie.
W tej sytuacji długości ciągów zer są obliczone jako liczba ujemna a ciągów jedynek - jako dodatnia. Do formatowania tego obszaru wykorzystałem własne kody formatu liczbowego. Niestety w tych formatach można skorzystać tylko z kilku kolorów.
W końcu w kolumnach L i M funkcją SUMA.JEŻELI sumuję liczby ujemne o liczby dodatnie.
Wynik w załączenie.

[JedenGość]

Rozumiem. Oczywiście rozumiem co do mnie piszesz, bo takiego operowania funkcjami jak ty to pewnie nigdy nie ogarnę.

Wszystko fajnie, ale jeżeli ten zielony może być tylko taki (razi po oczach), bo jak pisałeś jest ograniczona liczba kolorów, to muszę cofnąć się do poprzedniego rozwiązania. Nawet za cenę utraty możliwości kompatybilności tego rozwiązania z tym ze wspomnianego wcześniej innego wątku.

Wracając do poprzedniego rozwiązania spróbowałem funkcji SUMA:

Kod: Zaznacz cały

=SUMA(F1;H1;J1)

i tu Calc nie wywala błędu. Wynik jest poprawny. Nie wiem tylko jak napisać formułę, aby liczył tylko liczby zielone w wierszu, niezależnie od tego czy liczba zielona jest pierwsza, czy druga...?

[JedenGość]

Chyba sobie poradziłem. Zastosowałem taką formułę:

Kod: Zaznacz cały

=JEŻELI(FRAGMENT.TEKSTU(B1;1;1)="1";SUMA(E1;G1;I1;K1);SUMA(F1;H1;J1;L1))

Wyniki obliczeń są poprawne.

[Jan_J]

Zliczane są długości serii. A seria mają to do siebie, że sąsiadujące muszą być różnych rodzajów.
Zaś jeśli są tylko dwa rodzaje, to cóż: występują na przemian.
Dlatego kolumny E, G, I, K przechowują ten sam rodzaj wyników („zielone”), a F, H, J, L – drugi („czerwone”).
Ale...

1. skoro tak, to po co używać SUMA(), skoro da się prościej sumować za pomocą + ?

2. kłopoty się zaczną, kiedy okaże się, że w wierszu było więci niż 8 serii. Czy funkcją, czy działaniem +, nie dosumujesz wtedy do końca.

3. ogólnie zadanie polega na zsumowaniu co drugiej wartości w wierszu, poczynając albo od kolumny 1 (czyli nieparzyste), albo 2 (czyli parzyste indeksy), zależnie od wartości z kolumny B.

Kod: Zaznacz cały

=SUMA((E1:L1)*(CZY.PARZYSTA(KOLUMNA(E1:L1))+B1))

z zatwierdzeniem <Ctrl+Shift+Enter> bo to formuła wektorowa.
Jeśli kolumn wynikowych będzie więcej, wystarczy rozszerzyć adres w formule, bez zmiany jej treści.

[Jermor]

Teraz to już nie wiem na jakiej wersji pracujesz. Ta druga liczy sumy bez problemowo. Jeśli nie odpowiadają ci kolory, to zrób to, co zrobiłeś już kiedyś. Utwórz swój zielony i czerwony styl komórki. Z mojego formatowania usuń określenia kolorów. Zbuduj dla obszaru formatowanie warunkowe w rodzaju, jeśli liczba jest mniejsza od zera czerwony, jeśli większa od 0 zielony. Style te, już bez formatowania warunkowego możesz przypisać do kolumn L (zielony) i M (czerwony). Pisałem już ci o tym, że to style decydują o wyglądzie dokumentu.
Podsyłam poprawiony dokument. Utworzyłem w nim style o nazwie "czerwony" i "zielony". Możesz je modyfikować aby dobrać potrzebne barwy.

[Jermor]

Do @Jan_J. Tu problem sumowania polega na tym, że nie wiadomo jaka seria wystąpi pierwsza.

[Jan_J]

Tak wiem, stąd shift o B1 w liczeniu parzystości. Tak/siak, skoro nie ma funkcji „sumuj co n-ty”, to iloczyny skalarne górą.

[Jermor]

Wracając do poprzedniego rozwiązania spróbowałem funkcji SUMA:

KOD: ZAZNACZ CAŁY   ROZSZERZ WIDOK
=SUMA(F1;H1;J1)
i tu Calc nie wywala błędu.

Gdy używasz funkcji SUMA ignorowane są komórki zawierające tekst.

[JedenGość]

Teraz to już nie wiem na jakiej wersji pracujesz.

Pisałem wyżej, że z powodu kolorystyki wracam do pierwszej wersji i w niej poprzez formułę z SUMĄ i FRAGMENT.TEKSTU osiągnąłem poprawny wynik sumowania.

Z Twojej wypowiedzi zrozumiałem, że kolorów w kodzie formatu nie mogę sobie zmieniać. Ale jeżeli mogę je ustawić w formatowaniu warunkowym to zmienia postać rzeczy.

Myślę, że teraz już będzie wszystko ok. Dzisiaj już kończę, ale jutro przyjrzę się ostatniej proponowanej przez Ciebie wersji.

Dziękuję.

[Jermor]

Kolory w formatowaniu liczby mogą być następujące:
[BLACK], [BLUE], [BROWN], [CYAN], [GREEN], [GREY], [MAGENTA], [RED], [WHITE], i [YELLOW]
Kolory w formatowaniu właściwości czcionki mogą być dowolne z całej palety barw.

[JedenGość]

Wcześniej pisałeś, że liczba kolorów w kodzie formatu jest ograniczona, a zielony jest zbyt dla mnie jaskrawy. Natomiast myślałem, że kolory w nim muszą być ustawione tak jak w pliku z rozwiązaniem z innego wątku. Teraz widzę, że kod formatu jest standardowy, a kolory faktycznie ustawione w formatowaniu warunkowym. Pewne kwestie nie są dla mnie oczywiste od razu, dlatego dziękuję za wyczerpujące odpowiedzi.

P.S. Nie spotkałem się wcześniej z funkcją REGEX. Pomoc LO opisuje ją tylko po angielsku, a mój poziom tego języka nie jest na tyle dobry, aby ten opis dobrze zrozumieć. Może masz na swoim blogu jakiś tekst na jej temat?

[JedenGość]

Odzywam się jeszcze raz, ponieważ próbując powiązać formuły z ostatniego pliku z formułami zaproponowanymi przez Jermora w tym wątku: https://forum.openoffice.org/pl/forum/v ... =30#p21573
pojawiają się błędy.

Szczegóły w załączonym pliku.
Proszę o rozwiązanie lub pomoc w dojściu do rozwiązania tego problemu.

[Jermor]

Przykro mi, ale nie mam czasu i siły przebijać się przez post sprzed roku, składający się z trzech stron dyskusji żeby odnaleźć jakiś fragment.
Jeżeli faktycznie chodzi o policzenie ile razy w każdym wierszu Arkusz2.G2:Q23 występują liczby >1, =-1, =-2, to wystarczy funkcja LICZ.JEŻELI.
W komórce Arkusz3.B2 można wpisać

Kod: Zaznacz cały

=LICZ.JEŻELI(Arkusz2.$G2:$Q2;B$1)

Funkcja oblicza ilość biorąc pod uwagę warunek wpisany w pierwszym wierszu. Można ją powielić do pozostałych komórek. Gdy w G1 wpiszesz <=-5, pojawi się tam liczba pozycji pozostałych (mniejszych od -4).
Do czego w tym arkuszu jest kolumna Green, nie wiem.
W Arkusz2 wpisałeś formuły i wygenerowały one błąd #VALUE!. Stało sie to przez kolumnę D i formułę macierzową:

Kod: Zaznacz cały

=SUMA(MODUŁ.LICZBY(G2:Q2))

Ona sama w sobie jest dobra, ale w zakresie jej działania znajdują się komórki tekstowe. Pisałem już o tym, i chociaż funkcja SUMA ignoruje takie komórki, to nie robi tego funkcja MODUŁ.LICZBY. Zresztą liczby potrzebne do tej sumy masz już wyliczone, jako suma zielonych i suma czerwonych.

[JedenGość]

W porządku. Rozumiem> I tak już wiele Ci zawdzięczam. Twoje uwagi, także te z ostatniego postu "otwierają mi oczy" na pewne kwestie i chyba już sobie z tym poradzę. Bynajmniej mam taką nadzieję. Dziękuję za zaangażowanie.

[Jermor]

REGEX( Tekst ; Wzorzec [ ; [ Zamiennik ] [ ; Flagi|Powtórzenie ] ] )
Funkcja REGEX wykonuje działanie na tekście źródłowym, który jest pierwszym argumentem tej funkcji. Funkcja odnajduje w tym tekście fragment dopasowany do wzorca podanego jako drugi argument.
Drugi argument funkcji jest traktowany jak wyrażenie regularne, dlatego, gdy wzorcem jest tekst podawany wprost, wszystkie znaki tego tekstu, które mogłyby zostać zinterpretowane jako znaki wyrażenia regularnego, należy poprzedzić znakiem odwrotnego ukośnika (\).
Znakami o specjalnym znaczeniu w wyrażeniach regularnych są: * ? + [ ] ( ) { } ^ $ | \ . (kropka też jest znakiem specjalnym). Wielkość liter ma znaczenie.
Gdy funkcja ma podane tylko dwa parametry, zwraca ten fragment tekstu źródłowego, który jest dopasowany do wzorca.
REGEX("Jana Pawła II 1234/6";"23") zwróci ciąg 23, gdyż takie dopasowanie zostało znalezione.
REGEX("Jana Pawła II 1234/6";"\d{2}"), zwróci ciąg 12, gdyż wzorzec określa konieczność znalezienia ciągu złożonego z dwóch dowolnych cyfr, a 12 jest pierwszym takim ciągiem.
Jeśli żaden fragment tekstu źródłowego nie pasuje do wzorca, zwracany jest wynik #N/D.
Trzeci argument funkcji to zamiennik dla dopasowanego fragmentu. Funkcja zwróci tekst źródłowy, zastępując dopasowany do wzorca fragment – zamiennikiem.
REGEX("Jana Pawła II 1234/6";"\d{2}";"xxxx") zwróci: Jana Pawła II xxxx34/6, zastępując dopasowany ciąg (12) zamiennikiem (xxxx). Jeżeli żaden fragment tekstu źródłowego nie zostanie dopasowany do wzorca, zwrócony zostanie oryginalny tekst źródłowy.
Czwarty argument funkcji może określać flagę albo powtórzenie. Flagą może być litera „g” (tylko mała) oznaczająca, że zamiennik ma zostać zastosowany do każdego dopasowanego fragmentu tekstu źródłowego.
REGEX("Jana Pawła II 1234/6";"\d{2}";"xxxx";"g") zwróci: Jana Pawła II xxxxxxxx/6. Najpierw zostanie zamieniony pierwszy dopasowany fragment (12), a następnie drugi (34).
Jeśli ostatnim argumentem jest liczba, to określa ona, który dopasowany do wzorca fragment ma zostać zwrócony albo zamieniony. Gdy liczbą jest zero, zawsze zwrócony zostanie ciąg źródłowy bez żadnych zmian.
REGEX("Jana Pawła II 1234/6";"\d{2}";;2) zwróci ciąg 34, gdyż jest to drugi ciąg, który jest dopasowany do wzorca. Jeśli taki ciąg nie zostanie dopasowany, zostanie zwrócony wynik #N/D.
REGEX("Jana Pawła II 1234/6";"\d{2}";"xxxx";2) zwróci Jana Pawła II 12xxxx/6, gdyż drugim dopasowanym ciągiem jest 34 i to ten ciąg zostanie zastąpiony zamiennikiem. Gdy wzorzec nie zostanie dopasowany, zwrócony będzie oryginalny tekst źródłowy.

[JedenGość]

Mam jeszcze zagadkę odnośnie podanego przez Jermora rozwiązania. Implementuję je w swoich plikach, ale pewnej kwestii nie mogę przeskoczyć. Chociaż muszę wspomnieć, że wcześniej o niej nie wspominałem. Jeśli mogę to proszę o podpowiedź rozwiązania problemu. Szczegóły w załączniku.

Edit.
Wydaje się, że część problemu rozwiązałem poprzez użycie formuły:

Kod: Zaznacz cały

=INDEKS(G16:R16;ILE.LICZB(G16:R16))

[Jermor]

W tym projekcie jest stała ilość kolumn z wynikowymi danymi. Od G do R.
Dane wynikowe zawierają albo liczbę (jeśli jest obliczona), albo pusty ciąg znakowy.
Wykorzystałem pomocnicze kolumny obliczeniowe. Poniżej formuły tych kolumn dla pierwszego wiersza zestawionych danych.

• Położenie ostatniej liczby w wierszu danych wyznacza formuła w kolumnie V:

  Kod: Zaznacz cały

  =JEŻELI.BŁĄD((PODAJ.POZYCJĘ("";G2:Q2;0)-1);11)

  Zastosowanie formuły JEŻELI.BŁĄD wynika z faktu, że jeśli wszystkie pozycje zawierać będą jakąś liczbę, to PODAJ.POZYCJĘ zwróci wynik #N/D. Dlatego w takiej sytuacji należy wstawić wynik liczbowy 11.
• Wartość ostatniej pozycji w wierszu (kolumnaW):

  Kod: Zaznacz cały

  =INDEKS(G2:Q2;1;V2)

  w której V2 zawiera położenie ostatniej wartości liczbowej.
• W zaproponowanym rozwiązaniu utworzyłem jeszcze jedną dodatkową kolumnę, zawierająca wynik w którym -1 oznacza, że ostatnia liczba w wierszu jest ujemna albo 0, gdy ostatnia liczba w wierszu jest liczba większa od 0 (kolumna X).

  Kod: Zaznacz cały

  =SPRAWDŹ.PRÓG(INDEKS(G2:Q2;1;V2);0)-1

• Na podstawie obliczonej ostatniej pozycji i liczby mówiącej o tym, czy jest to liczba ujemna, generuję zakres danych potrzebnych do wyliczenia średniej. Oto formuła (kolumna Y):

  Kod: Zaznacz cały

  ="r"&WIERSZ()&"C7:r"&WIERSZ()&"c"&7+V2-1+X2

  Teraz mogę policzyć średnią z tego obszaru uwzględniająca tylko liczby ujemne (kolumna Z):

  Kod: Zaznacz cały

  =ŚREDNIA.JEŻELI(ADR.POŚR(Y2;0);"<0";ADR.POŚR(Y2;0))

Nie można jednak w ten sposób wyznaczyć mediany, bo nie ma funkcji MEDIANA.JEŻELI. Dlatego wykorzystałem inny sposób zorganizowania danych pomocniczych a na ich podstawie można obliczyć medianę oraz średnią.
Poniżej danych z kolumn G:R utworzyłem ich odpowiednik, ale zawierający tylko wartości ujemne oraz nie zawierający ostatniej wartości w wierszu, jeśli ta wartość jest ujemna.
Pierwsza komórka tego zakresu zawiera formułę:

Kod: Zaznacz cały

=JEŻELI(I((KOLUMNA()-KOLUMNA($F$1)<$V2+$X2);G2<0);G2;"")

Można ja skopiować w prawo do położenia kolumny Q a następnie w dół do ostatniego potrzebnego wiersza.
Obliczenie mediany i średniej jest teraz banalnie proste, gdyż te obliczenia ignorują komórki puste, a sam obszar zawiera tylko wartości niezbędne do wykonania obliczeń.
Jest oczywiście jeden problem, który należy rozwiązać np. korzystając z funkcji JEŻELI.BŁĄD. Problem dotyczy sytuacji, gdy w danych znajduje się tylko jedna wartość i jest to wartość ujemna. Zgodnie z założeniem ostatnia wartość ujemna ma nie być uwzględniana, więc nie jest przenoszona do pomocniczej tabeli i ten pusty wiersz generuje błąd.
Zmodyfikowany plik odsyłam.

[Rafkus]

W moich rozmyślaniach poszedłem tropem @JedenGość, a mianowicie ostatnią liczbę wyznaczyć według wzoru:

Kod: Zaznacz cały

=INDEKS(G2:R2;   ILE.LICZB(G2:R2))

Jeśli chodzi o sumę liczb czerwonych (czyli ujemnych) to proponuję wykorzystać funkcję SUMA.JEŻELI( Zakres; "<0").
Odpowiedni zakres stworzę przy pomocy funkcji PRZESUNIĘCIE:

Zwraca wartość przesunięcia komórki o pewną liczbę wierszy i kolumn względem danego punktu odniesienia.
Składnia

Kod: Zaznacz cały

PRZESUNIĘCIE(odwołanie;  wiersze;  kolumny;  wysokość;  szerokość)

• - Odwołanie - w tym przypadku ma być to punkt startowy,
  - Wiersze; Kolumny; - o ile wierszy i kolumn ma być przesunięty punkt startowy: tutaj w obu przypadkach o 0
  - Wysokość - parametr opcjonalny, zakres ma być jednowierszowy, więc go pominę,
  - Szerokość - parametr opcjonalny, ile kolumn ma mieć zakres danych, tutaj obliczony on zostaje z formuły:

  Kod: Zaznacz cały

   ILE.LICZB(G2:R2)-1
   - 1 na końcu bo ma być wykluczona ostatnia liczba ujemna

  NP: PRZESUNIĘCIE(G3; 0; 0; ; 6) jest równoznaczne z obszarem danych G3:L3

Argumenty: Wysokość i Szerokość nie mogą być <= 0, bo funkcja zwróci błąd. Zatem ostateczny wzór wygląda następująco:

Kod: Zaznacz cały

=JEŻELI.BŁĄD(SUMA.JEŻELI(PRZESUNIĘCIE(G2; 0; 0; ;   ILE.LICZB(G2:R2)-1);  "<0");   0)

Jeśli chodzi o Medianę to proponuję wykorzystać funkcję macierzową (zatwierdzić ją wciskając jednocześnie klawisze CTRL+SHIFT+ENTER):

Kod: Zaznacz cały

=JEŻELI.BŁĄD(MEDIANA(JEŻELI(PRZESUNIĘCIE(G2;0;0; ;ILE.LICZB(G2:R2)-1)<0;  PRZESUNIĘCIE(G2;0;0; ;ILE.LICZB(G2:R2)-1)));0)

Jak działa powyższa formuła, upraszczając wygląda ona tak:

Kod: Zaznacz cały

=MEDIANA(JEŻELI(Zakres<0;  Zakres))

Odpowiedni Zakres danych tworzę znowu przy pomocy funkcji PRZESUNIĘCIE ,
Funkcją JEŻELI wybieram tylko liczby ujemne, pomijam wartości dodatnie
Funkcja MEDINA daje gotowy wynik...

PS. W celu obliczenia średniej w funkcji macierzowej zamiast MEDIANA wpisz ŚREDNIA

[JedenGość]

Na początek napiszę, że chyba tylko na tym forum dxiałają tak wyspecjalizowani w temacie profesjobaliści jak Jermor i Rafkus. Zawsze można na nich liczyć. Panowie, ja już nie wiem jak mam dziękować, bo zdaje się, że nadużywam tego słowa...

Przechodząc do rzeczy. Jermor, wszystko jak zawsze super. Zaimplementowałem to w swoich plikach i jest bardzo dobrze. Trochę tylko mi przeszkadza to, że aby wyliczyć średnią i medianę potrzebuję dodatkowe komórki w wierszach G32-AT53 (wiem, że mogę je ukryć), ale to nie jest jakiś wielki problem. Próbowałem tę tabelę skopiować do kolumn od BA dalej, ale pojawiały się błędy (chyba, że coś namieszałem...).

Rafkus, Twoje rozwiązanie wydaje się prostsze. Jest super, tylko, że już zaimplementowałem rozwiązanie Jermora...

Jednak przyglądając się mu zauważyłm, że

Kod: Zaznacz cały

=JEŻELI.BŁĄD(SUMA.JEŻELI(PRZESUNIĘCIE(G2; 0; 0; ;   ILE.LICZB(G2:R2)-1);  "<0");   0)

tu chyba powinna być ŚREDNIA.JEŻELI bo chodziło mi o średnią. Zamieniłem SUMA.JEŻELI na ŚREDNIA.JEŻELI i jest ok.

Tutaj

Kod: Zaznacz cały

=JEŻELI.BŁĄD(MEDIANA(JEŻELI(PRZESUNIĘCIE(G2;0;0; ;ILE.LICZB(G2:R2)-1)<0;  PRZESUNIĘCIE(G2;0;0; ;ILE.LICZB(G2:R2)-1)));0)

coś nie gra. Skopiowałem, wkleiłem, ale wyskakuje błąd:508. W tym kodzie są chyba zbędne spacje, bo je usunąłem, ale i tak pojawia się w/w błąd... (???).

Kolejny raz się powtórzę, ale... DZIĘKUJĘ!

Edit.
Rafkus, wszystko w Twojej formule jest ok. Już wiem gdzie robiłem błąd.

[Rafkus]

błąd 507, 508 - Błąd ustawienia nawiasów
Brakujący nawias - na przykład brak nawiasu otwierającego, choć podano nawias zamykający

Jeszcze raz sprawdziłem kod i otrzymuję bezbłędne wyniki - spacje nie przeszkadzają.

[JedenGość]

Przed chwilą edytowałem poprzedni post. Już wiem, gdzie robiłem błąd. Jest wszystko OK. Dziękuję!

[JedenGość]

Rafkus, próbuję Twoje rzowiązanie z funkcją PRZESUNIĘCIE powiązać np. z WYST.NAJCZĘŚCIEJ

Kod: Zaznacz cały

=WYST.NAJCZĘŚCIEJ(PRZESUNIĘCIE($N2;0;0;;ILE.LICZB($N2:$BA2)-1);"<0")

jednak wyskakuje błąd:504.

Jak tu nie mogę sobie z nią poradzić to tak samo mam z funkcjami:
- CZĘSTOŚĆ (chcę określić częstość występowania liczb od -1 do -3 z pominięciem ostatniej liczby)
- zwykłe LICZ.WARUNKI (chcę policzyć wszystkie liczby <0 z pominięciem ostatniej liczby).

Możesz mi jeszcze raz wytłumaczyć dokładnie o co chodzi z tym PRZESUNIĘCIEM (czytam hepa Calca i Excela i nie mogę pojąć).

[Jermor]

Kolega @Rafkus przypomniał o funkcji PRZESUNIĘCIE. Dziękuję. Ale też ILE.LICZB, za to jeszcze bardziej dziękuje, gdyż moja propozycja formuły (PODAJ.POZYCJĘ) jest zupełnie zbędna i niepotrzebnie tak złożona.
W rozwiązaniu @Rafkusa nie rozumiem tego:

Kod: Zaznacz cały

ILE.LICZB(G2:R2)-1
- 1 na końcu bo ma być wykluczona ostatnia liczba ujemna

Wydaje mi się bowiem, że obszar ma być skracany, tylko wówczas gdy ostatnia liczba jest ujemna, a ta formuła skraca każdy obszar.
No i rzeczywiście lepszym rozwiązaniem jest MEDIANA w w formule macierzowej, nie potrzeba wówczas obszaru pomocniczego.
Dodałem tez kolumnę z funkcją WYST.NAJCZĘŚCIEJ. Wyjaśniam jednocześnie, że wynik #VALUE! oznacza, że żadna wartość nie wystąpiła przynajmniej dwukrotnie.
Takie poprawione rozwiązanie dołączam.

[Jermor]

Odzywam się nie pytany, ale co tam!
PRZESUNIĘCIE pozwala odwołać się do komórki albo obszaru, który leży w innym miejscu, względem wybranek komórki.
Załóżmy, że komórką odniesienia będzie D4.
Funkcja PRZESUNIĘCIE ma 5 argumentów. Pierwszy mówi względem jakiej komórki będziemy się orientować. Drugi parametr mówi o ile wierszy chcemy się względem tej komórki przesunąć w dół (liczba dodatnia) albo w górę (liczba ujemna).
Trzeci parametr mówi o ile kolumn chcemy się względem tej komórki przesunąć w prawo (liczba dodatnia) albo w lewo (liczba ujemna).
Gdy zatem użyjemy funkcji w następujący sposób:

Kod: Zaznacz cały

=PRZESUNIĘCIE(D4;2;3)

to w rzeczywistości odwołamy się do komórki G6 dwa wiersze niżej i trzy kolumny w prawo. Gdy argument jest zerem, to przesunięcie w odpowiadającym kierunku nie następuje.
Wykorzystanie tylko tych trzech argumentów oznacza odwołanie się do pojedynczej komórki.
Argument czwarty i piąty określają szerokość i wysokość nowego obszaru, przesuniętego względem komórki odniesienia. Te argumenty są opcjonalne, lecz jeśli wystąpią to muszą to być wartości większe od 0.
Czwarty argument określa wysokość obszaru, czyli z ilu wierszy będzie się składał.
Piąty argument określa szerokość obszaru, czyli z ilu kolumn będzie się on składał.
Zatem zapis PRZESUNIĘCIE(D4;2;3;5;7) oznacza odwołanie się do obszaru G6:M10. 5 wierszy, od 6 do 10 i 7 kolumn, od G do M.
Ponieważ funkcja przy takich argumentach zwraca obszar, to może zostać użyta w innych funkcjach, które wymagają obszaru jako swojego argumentu.
W twoim problemie chcesz obliczyć średnią i medianę z obszaru,(wiersza od G:R) którego długość zależy od ilości liczb.
Wiesz tylko ile liczb jest w wierszu. Funkcje ŚREDNIA, SUMA czy MEDIANA pozwalają jako argument wskazać obszar, zawierający liczby do obliczeń. Dlatego jako obszar możesz podać PRZESUNIĘCIE(G2;0;0;1;ilość liczb)
Obszar wyznaczany będzie względem G2 z przesunięciami zerowymi, czyli będzie się zaczynał właśnie w G2. Będzie miał wysokość 1, czyli składał się z jednego wiersza i szerokość wynikającą z ilości liczb w tym obszarze.
Możesz dzięki temu napisać formułę: ŚREDNIA(PRZESUNIĘCIE(G2;0;0;1;ilość liczb)) a ona zwróci wyliczoną wartość.

[Rafkus]

Wydaje mi się bowiem, że obszar ma być skracany, tylko wówczas gdy ostatnia liczba jest ujemna, a ta formuła skraca każdy obszar.

Jeżeli liczba ostatnia jest dodatnia to tak czy siak nie jest ona brana pod uwagę podczas obliczenia średniej z liczb ujemnych i ich mediany. Dlatego w tym przypadku można nie przejmować ostatnią liczbą (można skracać każdy obszar).

Co do PRZESUNIĘCIA funkcja ta pozwala na pobranie jakiejś wartości z określonego pola przesuniętego o zadane wartości. Np:

Kod: Zaznacz cały

= PRZESUNIĘCIE(B2; 1; 1)

oznacza, że ma być pobrana pobrana wartość z pola przesuniętego o jeden wiersz i jedną kolumnę od B2 czyli z C3. Dwa następne opcjonalne parametry mogą dane z pola zamienić w pewien obszar danych, np:

Kod: Zaznacz cały

= PRZESUNIĘCIE(D4; -2; -1; 3; 3)

Powyższa formuła została wpisana do przykładowej komórki F10 i zatwierdzono przy pomocy klawiszy CTRL+SHIFT+ENTER. Wynikiem tej formuły będzie pewien obszar danych - tablica o wymiarach 3x3. Pierwsza wartość wartość w tej tabeli będzie pochodziła z pola znajdującego się o 2 wiersze wcześniej i 1 kolumnę wcześniej czyli z pola C2.
Sorki nie potrafię już prościej wytłumaczyć