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: 68619 Skopiuj do schowka Formuła znajdująca pierwszą i ostatnia niepustą komórkę
Autor Wiadomość
miklas 
forumowicz


Wersja: Win Office 2013
Posty: 18
Wysłany: 30-07-2020, 08:41   Formuła znajdująca pierwszą i ostatnia niepustą komórkę

Witam, poszukuję formuły która znajdzie pierwszą i ostatnia niepustą komórkę w wierszu, odejmie dwie skrajne i wyliczy średnią, max, min i ilość niepustych.
Załączam pliczek który w prosty sposób obrazuje to co chcę wykonać.
Interesuje nas obszar na niebiesko, a obszar na czerwono chcemy odrzucić.

Pozdrawiam!

znajdz zakres.xlsx
Pobierz Plik ściągnięto 11 raz(y) 8.92 KB

ID posta: 390306 Skopiuj do schowka
 
 
Asia_@. 
Excel Expert


Pomogła: 2656 razy
Posty: 4476
Wysłany: 30-07-2020, 09:41   

moja propozycja formułami tablicowymi

znajdz zakres.xlsx
Pobierz Plik ściągnięto 12 raz(y) 9.2 KB

_________________
Pozdrawiam
Asia
ID posta: 390310 Skopiuj do schowka
 
 
Marecki 
Excel Expert



Wersja: Win Office 2019
Pomógł: 2385 razy
Posty: 7806
Wysłany: 30-07-2020, 10:29   

miklas, będziesz musiał poprawić formuły Asi, bo chyba zapomniała o
Cytat:
odejmie dwie skrajne i wyliczy średnią, max, min i ilość niepustych.

Jeszcze jedno pytanie się nasuwa, co rozumiesz pod pojęciem skrajne?
Pierwsza pełna z lewej i z prawej strony, czy chodzi o skrajne wartości min i max ?
_________________
Hardware - ta część komputera, którą można kopnąć kiedy software przestanie funkcjonować.

Szkolenia z Excela , FB
Office 2019 Professional Plus , Windows 10 x64
Pozdrawiam, były mkkk23 teraz Marecki.
ID posta: 390312 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Wersja: Win Office 365
Pomógł: 886 razy
Posty: 3583
Wysłany: 30-07-2020, 10:37   

miklas, inna propozycja ode mnie (bez funkcji ulotnych)
Mam excela 365 więc nie musiałem zatwierdzac tych formuł tablicowo - u Ciebie być może trzena będzie je zatwierdzic tablicowo (CTRL+SHIFT+ENTER)
Marecki, skrajne to te na czerwono w pliku przykładowym :-P ;-)

znajdz zakres_BS.xlsx
Pobierz Plik ściągnięto 13 raz(y) 10.51 KB

_________________
Zlecenia, konsultacje, doradztwo i szkolenia z Power Query, Power BI i Excela - Raporty, Analizy, Projekty
Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 390313 Skopiuj do schowka
 
 
miklas 
forumowicz


Wersja: Win Office 2013
Posty: 18
Wysłany: 30-07-2020, 11:37   

Dziękuję wam bardzo! :) Wszystko śmiga, jedynie zauważyłem że jak są tylko puste pola to pokazuje nie 0 tylko 1 ale to nie ma znaczenia. Troszkę większy problem jaki z tym mam, to że jak te funkcje liczą np. średnią, a dane są w tabeli przestawnej, to nie wychodzi ona taka sama jak wyliczona "ręcznie", natomiast wszystko działa w porządku jak wartości z tabeli skopiuję gdzie indziej. Ilość niepustych pokazuje że gdzieś dodają się dwie dodatkowe wartości do wyliczeń i nie wiem skąd sie biorą :oops: jakby ktoś z was zechciał zerknąć to wrzuciłem plik na dysk googla bo jest za duży na forum.

https://drive.google.com/file/d/1QIFOuog6BgMWs3fmixPlWf6b_gGguAJa/view?usp=sharing
ID posta: 390314 Skopiuj do schowka
 
 
umiejead 
Excel Expert


Wersja: Win Office 2013
Pomógł: 658 razy
Posty: 3465
Wysłany: 30-07-2020, 12:48   

Moja propozycja (VBA) w zał. (zakres: <-100,0>).
.

Kopia znajdz zakres.xlsm
Pobierz Plik ściągnięto 7 raz(y) 21.11 KB

_________________
.
Jak poprawnie opisać problem: http://www.excelforum.pl/...ika-vt59262.htm
Chcesz precyzyjną odpowiedź - zadaj precyzyjne pytanie.
  
ID posta: 390315 Skopiuj do schowka
 
 
miklas 
forumowicz


Wersja: Win Office 2013
Posty: 18
Wysłany: 30-07-2020, 13:14   

WOW! jesteście niesamowici! :shock: :) Dziękuję bardzo!
Przeczytam i zrozumiem jeszcze z tysiąc takich przykładów i wpisuję sobie excela do CV :lol:
A jeśli chodzi o zrozumienie to Bill Mógłbys opisac troszkę swoją funkcję bo próbuję ja rozkminic od dwóch godzin i nie daje rady ;) Zakres sobie pozmieniałem elegancko, ale nie wiem przede wszystkim skąd ta Szóstka w drugiej "granicy", i co jeśli bym chciał zmienić zakres odcięcia skrajnych np. z 2 na 4 lub inny. Zauważyłem że pobierając dane z tabeli przestawnej "ucina" tylko przód, i z np 19 próbek bierze pod uwagę ostatnie 17 a nie środkowe 15. co ciekawe skopiowane dane na inny akrusz liczą sie elegancko hmmm...

Niemniej dziękuję ślicznie, jestescie wspaniali!
ID posta: 390322 Skopiuj do schowka
 
 
Bill Szysz 
Excel Expert


Wersja: Win Office 365
Pomógł: 886 razy
Posty: 3583
Wysłany: 31-07-2020, 10:18   

miklas, ta 6 była dla przykładowego pliku - NR.KOLUMNY zwraca nam liczbę która mówi nam w której kolumnie znajduje sie ostatnia wypełniona komórka. Ale ta liczba jest liczona od kolumny A (nr 1) więc musieliśmy odjąć 4 kolumny (bo nie chcielismy brać pod uwage kolumn od A do D) i dodatkowo odjąc 2 ( bo nie chcielismy dwóch ostatnich wyników) czyli razem 6. Ale tak było tylko dla przykładowego pliku.
W Twoim prawdziwym pliku dane które mają być zliczane zaczynają sie od kolumny M. zatem zamiast 6 powinniśmy podstawić 14 ( 12 kolumn przed danymi + 2 kolumny ze skrajnymi prawymi danymi).
Możemy też zapisać formułę w postaci (dla Twojego pliku, dla średniej - komórka G6)
Kod:
=ŚREDNIA(INDEKS(M6:CCC6;PODAJ.POZYCJĘ(0;--CZY.PUSTA(M6:CCC6);0)+2):INDEKS(M6:CCC6;WYSZUKAJ(10^10;M6:CCC6;NR.KOLUMNY(M6:CCC6))-NR.KOLUMNY($L6) -2))

i analogicznie dla pozostałych kolumn w których masz formuły.
Teraz powinno być wszystko ok :-)
_________________
Zlecenia, konsultacje, doradztwo i szkolenia z Power Query, Power BI i Excela - Raporty, Analizy, Projekty
Pozdrawiam, były szbill62 aktualnie Bill Szysz
ID posta: 390341 Skopiuj do schowka
 
 
Czeslaw
ExcelSpec


Pomógł: 191 razy
Posty: 674
Wysłany: 01-08-2020, 08:09   

Patrz zalacznik.
Formula
Kod:
=AVERAGE(INDEX(E1:AZ1;;AGGREGATE(15;6;COLUMN($A:$AZ)/($E1:$AZ1<>"");1)+2):INDEX(E1:AZ1;;AGGREGATE(14;6;COLUMN($A:$AZ)/($E1:$AZ1<>"");1)-2))


znajdz zakres.xlsx
Pobierz Plik ściągnięto 2 raz(y) 8.86 KB

ID posta: 390381 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

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe