Zależność wpisów w kolumnach od siebie

[diam0nd]

Na Wstępie chciałbym się serdecznie ze wszystkimi przywitać.
Jestem samoukiem, staram się do wszystkiego sam dotrzeć, jednak niektóre funkcje, zakresy mnie przerażają, a więc do rzeczy:


Mam 4 kolumny:
- W pierwszej kolumnie "kapitał"
- W drugiej "koszt"
- W trzeciej "wpłata"
- w czwartek "zaległość"

więc tak "kapitał" - 500
"koszt" - 100
"wpłata" - 100
"zaległość" - pusta kolumna

Chciałbym aby dwie kolumny ("kapitał" i "zaległość") się zmieniały względem wpisu w kolumnie "wpłata" i żeby kolumna "wpłata" była zależna od kolumny "koszt" (jest zmienna)
Mianowice gdy przy "wpłata" będzie wpis 200 , to "kapitał" się zrobi 400 ( 100 to stała z kolumny "koszt" ) - 100, bo było wpisane 200, więc te drugie 100 będzie odjęte w kolumnie "kapitał"

Z kolei przy wpisaniu w kolumnie "wpłata" - 50 , w kolumnie "zaległość" zrobi się wpis 50, bo winno być 100, które jest w kolumnie "koszt",

Pomoże ktoś ? Podesłać wzór arkuszu ?

[Jermor]

Skoro jak sam napisałeś:

Jestem samoukiem, staram się do wszystkiego sam dotrzeć, jednak niektóre funkcje, zakresy mnie przerażają,

przeczytaj cykl dla absolutnie zielonych, który zaczyna się pod adresem https://yestok.pl/gen/yak01.php i poprowadzi cię przez problematykę arkuszy kalkulacyjnych lub znajdź podobne tematy w zasobach Internetu.
Bardzo nieprecyzyjnie opisujesz swój problem. Dlaczego w twoim opisie dokonanie wpłaty obniża kapitał? Chyba powinna go podwyższyć.
Całe działanie, jakie chcesz tu wykonać, sprowadza się do zwykłych działań matematycznych, z jednym zastrzeżeniem: powinien gdzieś wystąpić "kapitał początkowy" w stosunku do którego wyliczany będzie kapitał bieżący, czyli ten określony przez ciebie a wynikający z wpłat i kosztów obsługi.
Poza tym w jaki sposób pozycja "Wpłata" ma zależeć od pozycji "Koszt"? To raczej pozycja "Kapitał" i "Zaległość" zależą od tych dwóch wartości.

[diam0nd]

Nie rozumiemy się Kapitał wynosi 500 zł, klient musi wpłacać minimum 100 zł. Jeśli wpłaci 200 zł, to 100 zł idzie na te minimum, a drugie 100 odejmuje mu z kapitału, czyli zostaje 400 zł Jest zależne, ponieważ przy kapitale 500 zł, koszt wynosi 100 zł, ale przy 1000 zł już 150 zł kapitał początkowy, to kolumna "kapitał". Ok dzięki poczytam artykuły

Edit: Poczytałem , to formuły JEZELI, trudna dla mnie sprawa, ktoś pomoże?

[Rafkus]

To będzie jakoś tak:

Kod: Zaznacz cały

Kapitał  |koszt    |wpłata   |zaległość
	500	|	100	|	   	|	   
	400	|	100	|	200	|	  0

Wiersz drugi przedstawia stan początkowy. Od wiersza 3 zaczynają się kolejne wpłaty / opłaty. Formuła w komórce zaległość (w trzecim wierszu) będzie wyglądać następująco:

Kod: Zaznacz cały

=JEŻELI(wpłata < koszt;  koszt - wpłata;  0)
lub w "języku arkuszowym":
=JEŻELI(C3<B3;   B3-C3;  0)

Zamiast 0 można wpisać "" (podwójny cudzysłów) wtedy komórka będzie wyglądać na pustą. Jeśli chodzi o komórkę kapitał, to tam należy wpisać:

Kod: Zaznacz cały

=JEŻELI(wpłata > koszt;  kapitał_poprzedni - (wpłata - koszt);  kapitał_poprzedni)
lub w "języku arkuszowym":
=JEŻELI(C3>B3;   A2-(C3-B3);   A2)

Przynajmniej ja tak to zrozumiałem.

[diam0nd]

Bingo jesteś mistrzem, właśnie o to mi chodziło, lecz jeszcze jedna sprawa. Gdy klient ma zaległość powiedzmy 100 ( i koszt 100 ) , to przy wpłacie 200, tak żeby nie schodziło z kapitału, tylko w pierwszej kolejności z zaległości ? To jak ?

Krótko mówiąc - gdy koszt 100, kapitał 500, a zaległość 0 , co następuje;
- gdy wpłata 100 nic się nie dzieje;
- gdy wpłata 200 schodzi 100 z kapitału;
- gdy wpłata 50, robi się zaległość 50
- gdy jest zaległość 50, a wpłata 100 , to nic się nie dzieje, bo priorytet to kolumna koszt, gdzie 100
- gdy wpłata 150, pokrywa koszt (100), reguluje zaległość (50), wraca do zaległość 0
- gdy wpłata 200, pokrywa koszt (100), reguluje zaległość (50), schodzi z kapitału (50)

Podsumują:

-priorytety: koszt > zaległość > kapitał

W kolumnie zaległość jest formuła, a co zrobić gdy mam listę gdzie te zaległości już są ? I trzeba ręcznie wpisać zachowując formułę ? Listy są już zrobione po prostu chce to wprowadzić w życie, a osób jest 100 na liście i już są jakieś wpisy dokonane

[Rafkus]

W takim przypadku trzeba zmodyfikować formułę w komórce kapitał:

Kod: Zaznacz cały

=JEŻELI(wpłata > koszt;  kapitał_poprzedni - (suma(wpłat) - suma(koszt));  kapitał_poprzedni)
lub w "języku arkuszowym":
=JEŻELI(C3>B3;   A2-(SUMA($C$3:C3)-SUMA($B$3:B3));   A2)

Funkcja SUMA($C$3:C3) w tej postaci oznacza że mają być zsumowane wartości od komórki $C$3 do C3.
Znaki $ Przed literą i cyfrą oznacza że jest to adres bezwzględny, ich wartości nie zmienią się podczas przeciągania formuły w dół.
Zapis adresu komórki w takiej postaci C3 jest adresem względnym, podczas przeciągania (tutaj) formuły w dół numer wiersza będzie się zwiększał.
Np.: Po przeciągnięciu komórki z samą sumą o jeden wiersz w dół otrzymasz formułę: SUMA($C$3:C4)

PS. Mam nadzieję, że wiesz co mam na myśli pisząc o przeciąganiu formuły...

[diam0nd]

ok, a zaległości ?

[Rafkus]

a co ma być z nimi? Wpisy zaległości też mają się sumować i przenosić do nowego wiersza?

Kod: Zaznacz cały

=JEŻELI(C3<B3;   B3-C3;  0)+D2

D2 jest to wcześniejsza zaległość

[diam0nd]

Tak jak wspomniałem mam gotowe listy już z góry wpisanymi zaległościami , wyśle może plik w calc , jak to wygląda i jak by miało wyglądać. Tutaj akurat mam 3 pozycje, ale na bywa też po 100 wierszy. Jak to "wprowadzić w życie" , żeby grało tak jak omówiłem problem we wcześniejszych postach. Po prostu , żeby z marszu można było edytować kapitał, koszt ( bo są zmienne), aby po wpisie w pole wpłata zachować priorytety, które omówiłem wcześniej i żeby brać pod uwagę, że zaległości już są wpisane i żeby od tego momentu podliczało należycie, zachowując wszystkie należyte formuły.
https://www.szybkiplik.pl/PGyts6WGGh

[Rafkus]

Auć, teraz chyba załapałem. Czyżbyś chciał trwale zmienić te dwie wartości (kapitał i zaległość) czyszcząc pole wpłata aby uzyskać "nowy" arkusz do zbiórek w kolejnym okresie?
W takim przypadku to byłoby do osiągnięcia tylko z poziomu makr.

Wcześniej myślałem, że to miały być tylko kolejne wpisy.

[diam0nd]

Tak. Do tej pory robiłem to ręcznie, nastepnie poprawiałem i liczyłem i wprowadzałem sam. Bywały błędy, a z racji, że mam tego dość sporo, muszę to z komuteryzować. Listy są aktualizowane co miesiąc. W razie co napisałem na priv

[Rafkus]

Dodałem makro do twojego pliku, możesz pobrać i przetestować.

W celu łatwiejszego wywołania makra do twojego pliku dodałem przycisk, ale radziłbym go usunąć i makro wywoływać ręcznie (aby przypadkiem nie zmienić za wcześnie pliku).
Wywołanie ręczne to z menu: Narzędzia -----> Makra -----> Wykonaj makro

[diam0nd]

Kolego to co zrobiłeś jest niesamowite ! Właśnie o to chodziło, działa, hula, coś pięknego ostatnie pytanie. Jak to wprowadzić w oryginalne pliki ? Wszystkie są tak samo
skontsruowane, więc tylko kwestia "przerzucic" w każdy plik. Kolejne miesiące będą, robione metodą kopiuj wklej, więc myślę, że wszystko powinno działać. Kwestia przysłowiowej flaszki to się dogadamy

[Rafkus]

Troszkę o makrach możesz poczytać tu: https://yestok.pl/ooo/y58.php, polecam zaznajomić się z tym opracowaniem.
lub tutaj: http://przepis-na-lo.pl/2012/12/urucham ... e-makrami/
A to makro napisane dla twoich danych:

Kod: Zaznacz cały

Sub Zeruj
  oDoc = ThisComponent
REM  oArk = oDoc.Sheets.getByIndex(0)     'ta linijka dotyczy tylko jednego, zawsze pierwszego arkusza
REM poniżej dwie linijki aby makro działało na aktywnym arkuszy
  aArk = oDoc.CurrentSelection.cellAddress.Sheet 'aktywny arkusz
  oArk = oDoc.Sheets.getByIndex(aArk)
  nr = 6	' Wprowadzane dane zaczynają się w 6 wierszu, nr będzie przyjmował kolejne wartości

REM : początek pętli, działania będą przeprowadzone aż do pierwszeko pustego pola Imie i nazwisko
  do while oArk.getCellRangeByName("B"& nr).getString()<>""
REM : pobierz dane z pierwszego arkusza  
    kapital = oArk.getCellRangeByName("D"& nr).getValue()	'dane z komórki początkowej D6
    koszt = oArk.getCellRangeByName("E"& nr).getValue()		'E6
    wplata = oArk.getCellRangeByName("F"& nr).getValue()	'F6
    zaleglosc = oArk.getCellRangeByName("G"& nr).getValue()	'G6   

    if wplata < koszt then
      zaleglosc = zaleglosc + koszt - wplata
      oArk.getCellRangeByName("G"& nr).Value = zaleglosc 	'wpisz nową wartość zaległości do arkusza
    elseif wplata > koszt and zaleglosc = 0 then
      kapital = kapital -(wplata - koszt)
      oArk.getCellRangeByName("D"& nr).Value = kapital		'wpisz nową wartość kapitału do arkusza
    else
      zaleglosc = zaleglosc - (wplata - koszt)	'oblicz wartość zaległości
      if zaleglosc < 0 then	'obliczona zaległość jest mniesza od 0, dokonano nadpłaty
        oArk.getCellRangeByName("G"& nr).Value = 0		'wyzeruj zaległość
        oArk.getCellRangeByName("D"& nr).Value = (kapital+zaleglosc)	'zostanie zmniejszony kapitał
      else 
        oArk.getCellRangeByName("G"& nr).Value = zaleglosc	'wpisz nową wartość zaległości do arkusza
      endif
    endif
    oArk.getCellRangeByName("F"& nr).string = ""	'wyczyść wpłatę
    nr = nr+1
  loop		'koniec pętli
End Sub

[Rafkus]

Kolejne miesiące będą, robione metodą kopiuj wklej,więc myślę, że wszystko powinno działać.

Otóż w takiej postaci może nie działać za dobrze, a to za sprawą tej linijki kodu:

Kod: Zaznacz cały

oArk = oDoc.Sheets.getByIndex(0) 'zawsze pierwszy arkusz

tu jest odwołanie "na sztywno", zawsze do pierwszego arkusza. Dopiero kolejne wartości zamiast 0 będą dotyczyły następnych arkuszy.

Rozwiązanie: zamień tą powyższą linijkę kodu następującymi:

Kod: Zaznacz cały

  aArk = oDoc.CurrentSelection.cellAddress.Sheet 'aktywny arkusz
  oArk = oDoc.Sheets.getByIndex(aArk)   

[Rafkus]

Po zastanowieniu można zastosować nieco mniej skomplikowane operacje:

Kod: Zaznacz cały

Sub Zeruj
  oDoc = ThisComponent
  aArk = oDoc.CurrentSelection.cellAddress.Sheet 'aktywny arkusz
  oArk = oDoc.Sheets.getByIndex(aArk) 
  
REM : Zapytaj się czy na pewno to makro ma wykonać swoje zadanie
  tekst = "Czy chcesz dokonać zmian w polach: WPŁATA, KAPITAŁ i ZALEGŁOŚĆ?"
  If  MsgBox  ( tekst , 36, "Uwaga" ) <> IDYES Then exit sub

REM : początek pętli, działania będą przeprowadzone aż do pierwszeko pustego pola Imie i nazwisko
  nr = 6	' Wprowadzane dane zaczynają się w 6 wierszu
  do while oArk.getCellRangeByName("B"& nr).getString()<>""
REM : pobierz dane z arkusza  
    kapital = oArk.getCellRangeByName("D"& nr).getValue()
    koszt = oArk.getCellRangeByName("E"& nr).getValue()
    wplata = oArk.getCellRangeByName("F"& nr).getValue()
    zaleglosc = oArk.getCellRangeByName("G"& nr).getValue()    
    
    zaleglosc = zaleglosc - (wplata - koszt)	'oblicz wartość zaległości
    if wplata = koszt then
      'nic nie zmieniaj, zostanie tylko wyczyszczone pole wpłata
    elseif zaleglosc < 0 then	'obliczona zaległość jest mniesza od 0, dokonano nadpłaty
      oArk.getCellRangeByName("G"& nr).Value = 0	'wyzeruj zaległość
      oArk.getCellRangeByName("D"& nr).Value = (kapital+zaleglosc)	'wpisz nowy zmniejszony kapitał
    else 
      oArk.getCellRangeByName("G"& nr).Value = zaleglosc	'wpisz nową wartość zaległości do arkusza
    endif
    oArk.getCellRangeByName("F"& nr).string = ""	'wyczyść wpłatę
    nr = nr+1		'numer następnego wierszsa
  loop
End Sub

Dodatkowo dodałem okno komunikatu, które może zapobiec zmianom przypadkowego uruchomienia makra.

[diam0nd]

Wszystko działa jak należy, a istnieje możliwość anulowania operacji ? tzn. cofnięcia się wstecz, gdybym jednak źle wypełnił i uruchomił makro? I ostatnie pytanie. Jak zablokować możliwość edytowania pliku calc ? Będą wypełniać osoby trzecie i chciałbym tylko żeby mogli tylko w kolumnie WPŁATA wpisywać i nigdzie więcej , ewentualnie to co dozwolę, coś a'la formularz, nawet z oknem w stylu Imię Nazwisko - ........ (gdzie kropki, to wpłata) Kolego skróciłeś mi czas rozliczeń z 3 dni do ... 30 minut Dziękuje

[Rafkus]

tzn. cofnięcia się wstecz, gdybym jednak źle wypełnił i uruchomił makro?

Przy pomocy makra? Nie wiem. Możesz cofać przy pomocy przycisku na pasku lub wciskając CTRL+z, ale widomo to cofa 1 krok więc trzeba kilka(naście) razy powtórzyć.

Jak zablokować możliwość edytowania pliku calc ?

Zaznacz wybrane komórki, które będą mogły być edytowane kliknij Prawy Przycisk Myszy, z wywołanego menu wybierz Formatuj komórki, przejdź na kartę Ochrona komórek i na niej odznacz Chronione. Następnie z menu Narzędzia wybierz Chroń dokument -----> Arkusz (albo cały dokument) i tam wybierasz opcje ochrony, miedzy innymi możesz ustawić hasło bez którego nikt nie zdejmie ochrony.
Tu znajdziesz więcej informacji o ochronie: https://yestok.pl/ooo/y47.php

UWAGA: W takim przypadku pamiętaj żeby zdjąć ochronę przed uruchomieniem makra - inaczej zostaną wyczyszczone pola WPŁAT, ale nie zmienia się (bo będą chronione) pola ZALEGŁOŚCI i KAPITAŁU. Chyba że poprawię to makro i tam dodam możliwość dezaktywowania ochrony.

[Rafkus]

I kolejna wersja makra.
Tym razem zostało ono wzbogacone o sprawdzenie czy dany arkusz jest chroniony. Jeśli tak to pojawi się okno z prośbą o wpisanie hasła potrzebnego do dezaktywacji ochrony. Po dokonaniu zmian ochrona zostanie automatycznie przywrócona.

Kod: Zaznacz cały

Sub Zeruj
  oDoc = ThisComponent
  aArk = oDoc.CurrentSelection.cellAddress.Sheet 'aktywny arkusz
  oArk = oDoc.Sheets.getByIndex(aArk) 

  ochrona = oArk.isProtected()	'czy arkusz ma ochronę
  if ochrona then
    tekst = "Ten dokument jest chroniony przed pewnymi zmianami."& CHR$(10)_ 
      & "Aby dokonać w arkuszu zmiany podaj hasło:"
    haslo = InputBox(tekst, "UWAGA")		'prośba o hasło
    On Error GoTo blad 			'jeśli hasło będzie złe wystąpi błąd, dlatego włączam obsługę błędów
    oArk.unprotect(haslo)
  else
REM : Zapytaj się czy napewno to makro ma wykonać swoje zadanie
    tekst = "Czy chcesz dokonać zmian w polach: WPŁATA, KAPITAŁ i ZALEGŁOŚĆ?"
    If  MsgBox  ( tekst , 36, "Uwaga" ) <> IDYES Then exit sub  
  endif

REM : początek pętli, działania będą wykonywane aż do pierwszego pustego pola Imie i nazwisko (kolumna B)
  nr = 6	' Wprowadzane dane zaczynają się w 6 wierszu
  do while oArk.getCellRangeByName("B"& nr).string<>""
REM : pobierz dane z arkusza  
    kapital = oArk.getCellRangeByName("D"& nr)	'początkowa komórka D6
    koszt = oArk.getCellRangeByName("E"& nr)	'E6
    wplata = oArk.getCellRangeByName("F"& nr)	'F6
    zaleglosc = oArk.getCellRangeByName("G"& nr)'G6   
    
    nowaZ = zaleglosc.Value - (wplata.Value - koszt.Value)	'oblicz wartość zaległości
    if wplata.Value = koszt.Value then
      'nic nie zmieniaj, zostanie tylko wyczyszczone pole wpłata
    elseif nowaZ < 0 then	'obliczona zaległość mniejsza od 0 oznacza, że dokonano nadpłatę
      zaleglosc.Value = 0	'wyzeruj zaległość w arkuszu
      kapital.Value = (kapital.Value + nowaZ)	'wpisz nowy zmniejszony kapitał
    else 
      zaleglosc.Value = nowaZ	'wpisz nową wartość zaległości do arkusza
    endif
    wplata.string = ""	'wyczyść komórkę wpłaty
    nr = nr+1		'numer następnego wiersza
  loop
  if ochrona then oArk.protect(haslo)	'jeśli dokument był chroniony zabezpiecz go ponownie
  exit sub
blad:
  beep		'sygnał dźwiękowy
  if Err = 1 then 
    MsgBox "Zostało wprowadzone złe hasło, nie można dokonać zmian", 16, "Uwaga"
  else 
    MsgBox "Error " & Err & ": " & Error$ & CHR$(10) & "(line : " & Erl & ")"
  endif     
End Sub 

[diam0nd]

Rewelacyjna robota ! Wszystko działa jak należy. Jest możliwość z kombatybilizować to z Excel ? Okazuje się, że pracownicy mają pakiety z innej firmy i u nich niestety nie działa makro Basic i czy da radę zachować stałe rozmiary kolumn i ogólnie całego dokumentu, bo u niektórych się, nie wiedzieć czemu, rozjeżdżają kolumny, a zrobione jest pod wydruk.

[Rafkus]

Nie mam exela, od co najmniej 15 lat go nie używam, więc w tym nie pomogę.

[diam0nd]

Ok spoko i tak mi bardzo bardzo pomogłeś. Jeszcze raz dziękuję!