Formuła łącząca pola

Użytkowanie arkusza kalkulacyjnego

Formuła łącząca pola

Postprzez Macif » Wt wrz 15, 2020 1:26 pm

Witam,
Proszę o pomoc w napisaniu formuły, która będzie brała pod uwagę kilka warunków. Szczegóły w pliku.
Formuła ma brać pod uwagę narodowość, imię i nazwisko, ilość przepracowanych godzin oraz stawkę. Wynik ma być sumą odpowiedniej stawki * h.
Załączniki
przykład.ods
(13.3 KiB) Pobrane 8 razy
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Jermor » Wt wrz 15, 2020 3:28 pm

Imiona i nazwiska pracowników nie mogą się powtórzyć. Gdyby nagle okazało się, że jest dwóch Janów Nowaków to trzeba to jakoś rozróżnić.
Załączniki
Macif.ods
Poprawiłem formułę. Błąd odkrył Rafkus.
(17.83 KiB) Pobrane 11 razy
AOO 4.1.7, LO 6.4.6 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Avatar użytkownika
Jermor
 
Posty: 1211
Dołączył(a): So paź 12, 2013 11:09 am

Re: Formuła łącząca pola

Postprzez Rafkus » Wt wrz 15, 2020 5:02 pm

Myślę, że w formułę przedstawioną przez @Jermora wkradł się błąd. Chodzi o część obliczająca z której kolumny obszaru "Stawki" ma być pobrana wartość:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
jest: 2+(A3>10)
Powinno być: 2+(C3>10)


Poprawna formuła z komórki E3:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=JEŻELI(B3="";"";WYSZUKAJ.PIONOWO(INDEKS(kraj;PODAJ.POZYCJĘ(B3;pracownik;0);1);stawki;2+(C3>10))*C3)
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Jermor » Wt wrz 15, 2020 6:03 pm

Wkradł się. Sorry
AOO 4.1.7, LO 6.4.6 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Avatar użytkownika
Jermor
 
Posty: 1211
Dołączył(a): So paź 12, 2013 11:09 am

Re: Formuła łącząca pola

Postprzez Macif » Wt wrz 15, 2020 7:57 pm

a czy jesteście w stanie zrobić poniżej każdej zmiany podsumowanie ilu pracowników jest na zmianie z jakiego kraju?
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Macif » Wt wrz 15, 2020 8:49 pm

tutaj mam to w wersji Excel...ale w open office są inne formuły...
Załączniki
gotowe.xls
(5.87 KiB) Pobrane 8 razy
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Rafkus » Śr wrz 16, 2020 9:56 am

W AOO nie ma funkcji IFERROR() (jest w libre), dlatego stosuje się obejście wykorzystując funkcję JEŻELI.
Załączniki
Macif2.ods
(17.23 KiB) Pobrane 7 razy
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Jermor » Śr wrz 16, 2020 11:37 am

Myślałem nad rozwiązaniem, które nie wymaga dopisywania symbolu kraju obok nazwisk pracowników. Znalazłem takie i przedstawiam je w niebieskich komórkach w załączonym pliku.
Rozwiązanie wymaga zastosowania formuł macierzowych oraz jest wrażliwe na wielkość liter symbolu kraju, dlatego są w nim funkcje LITERY.WIELKIE().
Ostatnio edytowano Śr wrz 16, 2020 1:58 pm przez Jermor, łącznie edytowano 1 raz
Powód: Usunąłem załącznik. Kolega Rafkus zbadał, że nie działa prawidłowo w niektórych wersjach.
AOO 4.1.7, LO 6.4.6 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Avatar użytkownika
Jermor
 
Posty: 1211
Dołączył(a): So paź 12, 2013 11:09 am

Re: Formuła łącząca pola

Postprzez Rafkus » Śr wrz 16, 2020 12:57 pm

Przyznam, że również myślałem nad takim rozwiązaniem, moja formuła macierzowa to:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=SUMA(LICZ.WARUNKI(pracownik; $B$3:$B$11;  kraj; "PL"))

Zamiast tekstu "PL" można wstawić komórkę w której znajduje się oznaczenie danego kraju.

@Jermor, niestety twoja formuła nie działa w AOO - generuje błąd "#N/D", natomiast w Libre 4.2. otrzymane wartości są ujemne (i błędne).
Wcześniej testowałem formułę macierzowa:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=LICZ.JEŻELI(INDEKS(kraj;  PODAJ.POZYCJĘ($B$3:$B$11;pracownik;  0);  1);  B12)

i również w AOO otrzymałem błąd "#N/D", a w starszym LO wyniki były poprawne.
Najwyraźniej AOO przeszkadzają puste komórki w zakesie: $B$3:$B$11oraz odwołanie do nieistniejących danych.
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Jermor » Śr wrz 16, 2020 1:56 pm

I twoja @Rafkus formuła jest zgrabniejsza. Gratulacje.
U mnie także miałem problem z #N/D. Tyle, że czy zastosowałem CZY.BŁ(), czy NIE(CZY.BŁ()), zawsze dostawałem taki sam wynik.
AOO 4.1.7, LO 6.4.6 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Avatar użytkownika
Jermor
 
Posty: 1211
Dołączył(a): So paź 12, 2013 11:09 am

Re: Formuła łącząca pola

Postprzez Macif » Śr wrz 16, 2020 7:07 pm

ostatnia modyfikacja pliku i poskładam sobie wszystko razem.
Załączniki
Macif.ods
(16.37 KiB) Pobrane 4 razy
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Rafkus » Śr wrz 16, 2020 7:53 pm

Należy z wcześniejszego równania pozbyć się części sprawdzającej ile godzin przepracowano (+(C3>10)):
Kod: Zaznacz cały   Rozszerz widokZwiń widok
JEŻELI(B3="";"";WYSZUKAJ.PIONOWO(INDEKS(kraj;PODAJ.POZYCJĘ(B3;pracownik;0);1);stawki;2)*C3)
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Macif » Śr wrz 16, 2020 7:58 pm

a w jaki sposob przyporządkować odpowienią wartość osobom po prawej stronie? chodzi o plik wyzej tak aby powstała lista z całego dnia
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Macif » Śr wrz 16, 2020 9:31 pm

da rade usunąć błędy po prawej oraz aby pojawiala sie godzina obok pracownika dopiero po wybraniu go z listy? Teraz mam wszędzie przy nazwisku czy pustym np 7... jest to dobry tok rozumowania?
Załączniki
macif.ods
(18.47 KiB) Pobrane 6 razy
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Rafkus » Cz wrz 17, 2020 6:57 am

:? Widzę, że układ arkusza się zmienił...
Macif napisał(a):aby pojawiala sie godzina obok pracownika dopiero po wybraniu go z listy?

Trzeba wykorzystać formułę testującą dane - funkcję JEŻELI:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
JEŻELI(warunek;    gdy_warunek_prawdziwy;    gdy_warunek_fałszywy )

Warunek - w twoim przypadku będzie sprawdzenie czy pole Imię i nazwisko jest puste (BX="")(X jest numerem jakiegoś wiersza)
gdy_warunek_prawdziwy - jeżeli pole Imię i nazwisko jest puste to wynikiem formuły ma być puste pole ("")
gdy_warunek_fałszywy - jeżeli pole Imię i nazwisko nie jest puste to mają zostać przeprowadzone jakieś działania (twoja formuła $I$1*24)
Zatem w komórce C4 powinna znaleźć się formuła:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=JEŻELI(B4="";   "";   $I$1*24)

Macif napisał(a):da rade usunąć błędy po prawej

Jest to możliwe, tak jak wcześniej należy użyć funkcji JEŻELI, z tym że teraz jako warunku użyj funkcji sprawdzającej czy w wyniku obliczeń twojej formuły powstanie błąd, do komórki O4 wklej formułę:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=JEŻELI( CZY.BŁĄD(WYSZUKAJ.PIONOWO(N4;$B$4:$C$12;2;0)/24 );    gdy_warunek_prawdziwy;    gdy_warunek_fałszywy )

Wynikiem funkcji CZY.BŁĄD(...) jest PRAWDA lub FAŁSZ.
UWAGA: Sam spróbuj wymyślić co należy wpisać zamiast gdy_warunek_prawdziwy oraz gdy_warunek_falszywy
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Macif » Cz wrz 17, 2020 9:35 am

dziękuję,
Mam nadzieje, że dobrze wymyśliłem.... nie mam narazie pomysłu jak ugryźć kolumnę "zł"....dosyć skomplikowana formuła.
Załączniki
Macif.ods
(19.03 KiB) Pobrane 4 razy
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Rafkus » Cz wrz 17, 2020 10:15 am

Oglądam właśnie twój nowy arkusz i mam pewne spostrzeżenia:

1. Widzę że masz problemy z nazwami obszarów.
Wybierz z menu Wstaw ---> Nazwy ---> Definiuj. W nowo otwartym oknie wybierz przypisaną nazwę obszaru i zwiększ jego rozmiar, nastepnie kliknij Modyfikuj. Chodzi mi tu głównie o "kraj" i "pracownik"-a, masz ich obecnie więcej niż zdefiniowany zakres.

2. Przemyśl swój układ: czy naprawdę potrzebujesz 2 razy tworzyć listę pracowników? Listę wszystkich pracowników masz w Arkuszu2 i jeszcze tworzysz ją w Arkuszu1

3. Jeśli chodzi o formułę z kolumny "zł"
Masz tam formułę:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=JEŻELI(B4="";"";WYSZUKAJ.PIONOWO(INDEKS(kraj;PODAJ.POZYCJĘ(B4;pracownik;0);1);stawka;2)*O4)*24

Moje uwagi:
JEŻELI(B4=""... - w tym przypadku sprawdzałbym co jest we wcześniejszej kolumnie "h" czyli : JEŻELI(O4=""...
...PODAJ.POZYCJĘ(B4;... - weź tu pracownika ze twojego zestawienia, czyli z kolumny N: ...PODAJ.POZYCJĘ(N4;...
...*O4)*24 - te *24 wstaw do nawiasu zaraz po O4. W skróconej wersji twojej formuły otrzymałeś:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=JEŻELI(O4="";"";O4)*24
- jeżeli komórka O4 jest pusta niepotrzebnie jest mnożona przez 24, czasami taka pusta komórka jest traktowana tak jakby było tam wpisane 0

Formuła z komórki P4 powinna wyglądać tak:
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=JEŻELI(O4="";  "";  WYSZUKAJ.PIONOWO(INDEKS(kraj; PODAJ.POZYCJĘ(N4; pracownik; 0); 1); stawka; 2)*O4*24)
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Macif » Cz wrz 17, 2020 10:30 am

dziękuję za spostrzeżenia. Wezmę je pod uwagę.
Czy w zł reguła
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=JEŻELI(O4="";"";WYSZUKAJ.PIONOWO(INDEKS(kraj;PODAJ.POZYCJĘ(N4;pracownik;0);1);stawka;2)*O4)*24

jest prawidłowa?
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Rafkus » Cz wrz 17, 2020 10:50 am

Odpowiedź jest we wcześniejszym poście
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Jermor » Cz wrz 17, 2020 10:53 am

Przykro mi @Macif, że muszę ci to napisać. Problemy, które przedstawiasz należą do elementarnej wiedzy o arkuszach kalkulacyjnych. No może oprócz działań na czasie, w których gubi się sporo użytkowników arkusza.
Poza tym powinieneś najpierw zastanowić się nad koncepcją tego co chcesz osiągnąć. Np. skoro i tak powstał spis wszystkich pracowników, to po co w innym arkuszu tworzyć taką samą listę? Jeżeli chcesz przeszukiwać listę wg nazwisk, to zakładając wykorzystanie funkcji WYSZUKAJ.PIONOWO(), powinna to być pierwsza kolumna takiego zestawienia.
Trochę ci zmodyfikowałem arkusz. Nazwa "kraj" nie jest już potrzebna. Nazwa "pracownik" obejmuje teraz listę nazwisk w wykazie wszystkich pracowników w związku z tym ta lista w Akusz2 nie jest już potrzebna. W Akusz2 znajduje się tabela stawek.
Załączniki
Macif (2).ods
(20.43 KiB) Pobrane 5 razy
AOO 4.1.7, LO 6.4.6 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Avatar użytkownika
Jermor
 
Posty: 1211
Dołączył(a): So paź 12, 2013 11:09 am

Re: Formuła łącząca pola

Postprzez Macif » Cz wrz 17, 2020 11:02 am

ok, rozumiem.
W rozwiązaniu Jermora nie liczy dokładnie czasu - gdy pracujemy od 18.30 do 22.00 to pokazuje 3h....
Pytanie jak zrobić, aby 40 minut przeliczało jako 0,75 części godziny gdy ktoś pracował od 18:00 do 22:40
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Rafkus » Cz wrz 17, 2020 11:07 am

Korzystałeś już z tego rozwiązania
Kod: Zaznacz cały   Rozszerz widokZwiń widok
=JEŻELI(B4="";   "";   $I$1*24)
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Macif » Cz wrz 17, 2020 11:09 am

jeśli ktoś pracował 5 godzin i 40 minut to jako liczba pokazuje 5,67 a nie powinno być 5,75?
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm

Re: Formuła łącząca pola

Postprzez Rafkus » Cz wrz 17, 2020 11:12 am

60 min = 1 godzina
1 min = 1/60 godziny
40 min = 40 * 1/60 godziny = 2/3 = 0,66(6) godziny
OpenOffice 4.1.6. oraz LibreOffice 6.4 Widows 10
OpenOffice 4.1.3. oraz Libre 4.2.5.2 Windows XP
Avatar użytkownika
Rafkus
 
Posty: 164
Dołączył(a): Cz kwi 12, 2018 10:26 pm

Re: Formuła łącząca pola

Postprzez Jermor » Cz wrz 17, 2020 3:29 pm

Szanowny @Macif. To jest własnie precyzja przedstawionego przez ciebie problemu. Nigdzie nie sugerowałeś nawet, że ktoś może pracować na zmianie w przez inny czas niż wynikający z opisu zmiany. Wszyscy pracują przez taki sam czas, wynikający z pozycji początek i koniec zmiany a w powszechnym przekonaniu te godziny są zawsze stałe. Nigdzie w twoim arkuszu nie ma nawet sugestii, że czas pracy będzie rozliczany indywidualnie wg godzin przyjścia i wyjścia. Czas pracy w godzinach, z ułamkiem dziesiętnym odpowiadającym minutom, to (wyjście - przyjście)*24
Jak ma być rozliczana zapłata za czas pracy? Z dokładnością do każdej minuty? Dziesięciu minut?, półgodziny, czy całej godziny? A jeśli tak to jak te czasy mają być zaokrąglane? W górę, czy w dół?
AOO 4.1.7, LO 6.4.6 (x64) na Windows 10 64bit
Ważne!
Jeśli twój problem został rozwiązany, wróć do swojego pierwszego postu, przejdź do edycji i dopisz [SOLVED] w temacie.
Inni, którzy mają podobny problem, będą wiedzieli, że istnieje jego rozwiązanie.
Avatar użytkownika
Jermor
 
Posty: 1211
Dołączył(a): So paź 12, 2013 11:09 am

Re: Formuła łącząca pola

Postprzez Macif » Cz wrz 17, 2020 6:09 pm

Na razie wystarczy Waszej pomocy za co jestem niezmiernie wdzięczny :) spróbuję trochę sam podziałać z resztą zadań.
OpenOffice 4 na Windows 7
Macif
 
Posty: 11
Dołączył(a): Wt wrz 15, 2020 1:23 pm


Powrót do Calc

Kto przegląda forum

Użytkownicy przeglądający ten dział: Brak zidentyfikowanych użytkowników i 1 gość

cron