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: 54340 Skopiuj do schowka formuła średnia.warunków - pomijanie wartości ujemnych
Autor Wiadomość
mygen
Starszy Forumowicz


Posty: 38
Wysłany: 17-10-2016, 11:28   formuła średnia.warunków - pomijanie wartości ujemnych

Witam

Próbuję za pomocą formuły średnia.warunków obliczyć średni czas (o ile został skrócony) dla konkretnego kodu (kolumna A), konkretnej maszyny (kolumna B) i konkretnego czasu (kolumna I1).

Kolumna I to tylko jeden przykład z wielu. Kolumna B - strona maszyny nie ma znaczenia.

O ile dla maszyny 0101 zwraca mi wynik to już dla maszyny 0102 mam same ####. Podejrzewam, że większość ze średniej daje wynik ujemny i tak się dzieje.

Problem zaczyna się dla mnie w momencie kiedy wartości są ujemne a tych nie chcę, żeby były brane pod uwagę do wyliczenia średniej. Można to jakoś wyeliminować?

Plik w załączniku.

Z góry dziękuję za pomoc.

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

ID posta: 305169 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1230 razy
Posty: 4264
Wysłany: 17-10-2016, 12:44   

A nie lepiej formuła dla F2
Kod:

=JEŻELI($I$1>E2;$I$1-E2;"")

?
ID posta: 305176 Skopiuj do schowka
 
 
kuma 
Excel Expert


Pomógł: 1178 razy
Posty: 3518
Wysłany: 17-10-2016, 17:36   

Co powiesz nt formuły tablicowej
Kod:
=ŚREDNIA(JEŻELI(Tabela_Domyślna__GTFLOW[Time_Reduction]>=0;Tabela_Domyślna__GTFLOW[Time_Reduction];""))
?
Pozdrawiam.
ID posta: 305217 Skopiuj do schowka
 
 
bodek 


Wersja: Win Office 2016
Pomógł: 940 razy
Posty: 2662
Wysłany: 17-10-2016, 23:02   

może tak, dla I3
Kod:
=ŚREDNIA.WARUNKÓW(F:F;F:F;">0";A:A;H3)
_________________
Szkolenia z Excela, Power Query, Power Pivot, wizualizacje danych w Excelu
ID posta: 305243 Skopiuj do schowka
 
 
mygen
Starszy Forumowicz


Posty: 38
Wysłany: 18-10-2016, 09:59   

Dziękuję za wszystkie odpowiedzi. Po podstawieniu formułki appollo do komórki F2 wszystko fajnie, tylko żeby liczyło ten czas to muszę za każdy razem wpisywać inny czas w komórce I2. Po podstawieniu formuł kuma i bodek daje mi dziwne wyniki. Stanowczo za duże.

Wyfiltrujcie proszę kolumnę B2 dla 0101L i 0101R i średnia dla Time Reduction to 24 sekundy. O to właśnie mi chodzi.

W pliku w kolumnach od H do J wpisałem przykładowo jeśli mamy konkretny gt_code, podstawowy czas to żeby do kolumny F2 obliczało jaki jest czas redukcji a potem na podstawie tego czasu dla konkretnego gt_code obliczało średni czas redukcji (na tą chwilę powinno to być w sekundach a nie jak wychodzi z podstawionego wzoru w minutach). Wydaję mi się, że w np. arkuszu 2 będę musiał stworzyć tabelkę z tymi wszystkimi danymi, żeby obliczało czas redukcji a potem z tego czasu wyciągało średni czas tej redukcji.

Generalnie chodzi mi oto, żeby to zautomatyzować jak najbardziej.

Zeszyt2.xlsx
Pobierz Plik ściągnięto 17 raz(y) 141.53 KB

ID posta: 305261 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1230 razy
Posty: 4264
Wysłany: 18-10-2016, 14:24   

Nie rozumiem. Moje wyniki w kolumnie F różnią się od Twoich tylko tym, że gdzie masz niedodatnie wyniki to ja mam puste.
Nie rozumiem, co masz na myśli z I2.
W I2 w pierwszym pliku jest formuła
Kod:

=ŚREDNIA.JEŻELI(Tabela_Domyślna__GTFLOW[GT_KOD];H2;Tabela_Domyślna__GTFLOW[CZAS_Reduction])

I taka formuła ma być nadal, tylko przekopiuj/przeciągnij w dół. I za każdym razem jeśli zmieniasz I1 to wyniki w kolumnie F i kolumnie I od I2 automatycznie się zmieniają.
---------
W pierwszym pliku masz
Kod:

=$I$1-E2

Czyli Fk zależy tylko od I1 i Ek. Tymczasem pisałeś
Kod:

W pliku w kolumnach od H do J wpisałem przykładowo jeśli mamy konkretny gt_code, podstawowy czas [b][color=red]to żeby do kolumny F2 obliczało jaki jest czas redukcji[/color][/b]

Nie rozumiem tego. Teraz to Fk zależy od Ik? Zmieniasz zasadę?

Na marginesie: F2 to komórka. F to kolumna

Rezygnuję.
ID posta: 305317 Skopiuj do schowka
 
 
mygen
Starszy Forumowicz


Posty: 38
Wysłany: 18-10-2016, 15:27   

Apollo rozumiem, że mogłem napisać coś niezrozumiale za co przepraszam. Ciągle chodzi mi o to samo.

Co do tego jak napisałeś, że nie rozumiesz. Każdy GT_CODE ma swój stały czas procesowy, który jest skracany. I tak na przykład VC67 czas wyjściowy to 49.5 minuty. Odpowiedni sprzęt decyduje na podstawie warunków czy może coś skrócić ten czas czy nie. I mi właśnie chodzi o ile ten czas się skrócił dla tego konkretnego GT_CODE i jaki był średni czas tych skróconych wartości.

Np. jeden cykl skrócił się o 5 sekund, drugi o 22, trzeci o 10 itd. Z tego wyliczam średnią skróconego czasu, czyli z tego wiem ile skracał średnio dla konkretnego GT_Code czas.

Dołączyłem arkusz 2 w pliku z prasy 0101 bo na tej prasie akurat skraca czas. Średnia to 24 sekundy a po podstawieniu Twojego wzoru daje czas 43:22. Nie mam pojęcia co to za czas. Tak jakby sumował coś za dużo.

W arkuszu 1 w kolumnie J od komórek J3 do J5 podałem czasy procesowe (wyjściowe) dla rozmiarów w kolumnie H (od H3 do H5). Ten procesowy czas jest skracany.

Zeszyt2.xlsx
Pobierz Plik ściągnięto 16 raz(y) 144.7 KB

ID posta: 305330 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1230 razy
Posty: 4264
Wysłany: 18-10-2016, 19:58   

I tak nic z tego nie rozumiem.
Dane wprowadzone ręczne są w kolumnach H i J? A formuły w kolumnie I od I3? Powiedz więc, jak formuła w Ik, dla k = 3, 4, 5, ..., ma obliczyć wynik mając Hk i Jk. Posługuj się tylko konkretnymi danymi, nazwami komórek, wierszy i kolumn.

O ile teraz rozumiem to nie moze być dla F2 jak podałeś w pierwszym pliku
Kod:

=$I$1-E2

bo zamiast $I$1 ma być inny czas dla innego B2. Coś jak
Kod:

=(Czas procesowy, wyjściowy, podstawowy dla A2) - E2

Jeśli tak to:
1. Formuła w I3 tak jak w pierwszym pliku
2. Formuła dla F2
Kod:

=JEŻELI.BŁĄD(JEŻELI(WYSZUKAJ.PIONOWO(A2;H$3:J$1000;3;0)-E2>10^-5;WYSZUKAJ.PIONOWO(A2;H$3:J$1000;3;0)-E2;"");"")


10^-5 bierze się stąd, że np. w Jk wprowadzasz dokładny czas np. 49:30, a w En widzisz 49:30 ale obie nie są równe, bo En jest wynikiem odejmowania i może faktycznie = 49:30,1 czy 49:30,01. Inaczej będziesz miał w Fn zero (0) czy ~0, i to zero będzie brało udział w obliczeniu średniej.
ID posta: 305352 Skopiuj do schowka
 
 
kuma 
Excel Expert


Pomógł: 1178 razy
Posty: 3518
Wysłany: 18-10-2016, 20:43   

To może i z mojej strony podpowiedź.
Mygen, w arkuszu 1, w kom. 'H3' masz VC67, w 'J3' jest czas podstawowy, natomiast w 'I2' moja formuła tablicowa
Kod:
=JEŻELI.BŁĄD(ŚREDNIA(JEŻELI(($E$2:$E$1000<$J3)*($A$2:$A$1000=$H3);$J3-$E$2:$E$1000;""));"")
Skopiuj ją w dół wg potrzeb.
Mam uwagę. W arkuszu 2 wypisałeś i wyliczyłeś średni czas skrócony, który wynosi 24s.
Jest on wyliczony dla 'VC67', ale nie dla wszystkich CURING_PRESS' ów, lecz tylko dla '0101L' i '0101R', a przecież 'VC67' ma jeszcze inne CURING_PRESS'y.
Nie wiem czy na to zwróciłeś uwagę.
Pozdrawiam.
ID posta: 305357 Skopiuj do schowka
 
 
mygen
Starszy Forumowicz


Posty: 38
Wysłany: 18-10-2016, 21:48   

Tak kuma zgadza się co do VC67. Póki co system skracania czasu działa na kilku maszynach. Stopniowo będą dochodzić kolejne. Dziękuję za odpowiedź. Jutro sprawdzę jak będę w pracy jak to działa.
Dobrej nocy :-)
ID posta: 305362 Skopiuj do schowka
 
 
kuma 
Excel Expert


Pomógł: 1178 razy
Posty: 3518
Wysłany: 18-10-2016, 22:46   

W niektórych przypadkach różnice 'czasu podstawowego' i "CURING_Time' są większe od zera, ale mniejsze od 1s i dlatego wyświetlane są jako '00:00:00', a w formule wchodzą do obliczeń średniej i ją zaniżają!
Można wyeliminować to tak: wprowadziłem zmienną nazwaną (Menedżer nazw) 'OneSec', która odnosi się do
Kod:
=1/(24*60*60)
Teraz formuła tablicowa w 'I2' z kopią w dół
Kod:
=JEŻELI.BŁĄD(ŚREDNIA(JEŻELI(($A$2:$A$1000=$H3)*($J3-$E$2:$E$1000>=OneSec);$J3-$E$2:$E$1000;""));"")
Pozdrawiam.
ID posta: 305364 Skopiuj do schowka
 
 
mygen
Starszy Forumowicz


Posty: 38
Wysłany: 19-10-2016, 16:00   

Witam

Bardzo dziękuję apollo oraz kuma za pomoc i cierpliwość. To co zaproponowaliście działa. W tej chwili widzę tylko jeden problem. Zawyża mi czasy ponieważ liczy mi do średniej komórki z zerami, które są na samym końcu danych po zaciągnięciu ich z bazy. Jest tam sporo pustych komórek, które uzupełnią się po kolejnym zaciągnięciu danych ale zawsze będzie część pusta.

Załączam plik, w którym mam już ustwione to wg formuły kuma. Ostatnia komórka dla której są wszystkie dane to 69501 (na moment, w którym zaciągnąłem dane). Później są już puste miejsca tylko w kolumnie E są wartoci 00:00:00. Jak ustawię zakres powyżej 69501 w formule to wówczas zalicza mi te "zera" do średniej i zawyża wynik. Wyciąłem z pliku środek bo za dużo by tego było. Zostawiłem końcówkę dla zobrazowania.

Podsumowując jak ręcznie będę zmieniał zakres to wynik średniej jest ok. Jednak nie o to chodzi, żeby za każdym razem to robić.

Można to obejść?

Nie jestem pewien czy dobrze dodałem ten OneSec do menadżera. ;-)

Zeszyt3.xlsx
Pobierz Plik ściągnięto 17 raz(y) 154.93 KB

ID posta: 305439 Skopiuj do schowka
 
 
kuma 
Excel Expert


Pomógł: 1178 razy
Posty: 3518
Wysłany: 19-10-2016, 17:29   

Zaproponuję Ci wprowadzenie dodatkowej zmiennej nazwanej (Menedżer nazw), która będzie odzwierciedlała ilość niepustych komórek zakresu - przyjąłem, że będzie to kolumna 'C'.
Zmienna 'Npste', która odnosi się do
Kod:
=ILE.NIEPUSTYCH(Arkusz1!$C$2:$C$70000)
W formule tablicowej dla 'I3'
Kod:
=JEŻELI.BŁĄD(ŚREDNIA(JEŻELI((ADR.POŚR("A2:A"&Npste)=$H3)*($J3-ADR.POŚR("E2:E"&Npste)>=OneSec);$J3-ADR.POŚR("E2:E"&Npste);""));"")
Myślę, że o to chodziło.
Pozdrawiam.
ID posta: 305454 Skopiuj do schowka
 
 
apollo
ExcelSpec


Pomógł: 1230 razy
Posty: 4264
Wysłany: 19-10-2016, 21:03   

Przecież sam możesz poprawić formułę. Coś na ten deseń
Kod:

=JEŻELI(C2="";"";STARA FORMUŁA)
ID posta: 305473 Skopiuj do schowka
 
 
mygen
Starszy Forumowicz


Posty: 38
Wysłany: 20-10-2016, 12:03   

Witam

Dziękuję za Wasze odpowiedzi. Póki co wygląda, że jest b.dobrze.

Mam jeszcze jedno pytanie. Gdybym chciał widzieć to po maszynach (Curing Press) a nie po GT CODE to jak należałby zmodyfikować formułę?

Z tego co widzę to problemem może być wyjściowy czas, gdyż to od niego jest uzależnione GT CODE natomiast maszyna już nie, gdyż na niej mogą pracować różne GT CODE.
ID posta: 305552 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