Elektroda.pl
Elektroda.pl
X

Search our partners

Find the latest content on electronic components. Datasheets.com
Elektroda.pl
Please add exception to AdBlock for elektroda.pl.
If you watch the ads, you support portal and users.

EXCEL - Kontrola płatności faktury zbiorcze

riiv 02 Jan 2012 13:34 4318 27
  • #1
    riiv
    Level 2  
    Witam,

    na wstępie zaznaczę, że jestem średnio zaawansowany w excelu. A teraz do konkretów.
    W swojej firmie wystawiam faktury w excelu. W bankowości internetowej ściągam historię rachunku w .cvs który potem konwertuję na excela. Stworzyłem plik który porównuje te dwa pliki (historia rachunku oraz wystawione faktury) i kontroluje płatności (zaznacza zapłacone, spóźnione itd.). Wszystko działało doskonale dopóki jednak jeden z klientów nie zrobił płatności zbiorczej (3 faktury w jednym przelewie).
    Moje pytanie brzmi czy można jakoś zrobić funkcję która będzie wyszukiwać numery faktur z tytułu płatności, konwertować na kwoty a na końcu zliczać czy suma się zgadza. Zaznaczę, że za każdym razem w tytule płatności znajduje się numer każdej zapłaconej faktury ale są one w inny sposób oddzielone od siebie (np. znakiem _ albo spacją).

    Mam nadzieję, że plik wyjaśni o co mi chodzi.

    Z góry dziękuję za pomoc.
  • #2
    fascynat
    Level 24  
    Excela używam raczej rzadko ale napisałem w VBA kawałek kodu, który rozdziela nr rachunków z ciągu tekstowego, w celu ich dalszego porównania z już istniejącymi nr rachunków.
    Możesz spróbować wykorzystać go (rozwinąć, dostosować) do własnych potrzeb.

    Code: vb
    Log in, to see the code

    W wyniku otrzymasz rozdzielone nr rachunków:
    11/2011
    12/2011
    13/2011

    albo komunikat o ich braku:
    Brak zdefiniowanych znaków rozdzielających nr rachunków
  • #3
    adamas_nt
    Moderator of Programming
    Można i bez VBA, rozdzielając ciągi i dodając warunkowo kolejne WYSZUKAJ.PIONOWO. Problem w tym, że w 1-szym przypadku znakiem rozdzielającym jest podkreślnik, a w drugim spacja (dotyczy także VBA).

    EXCEL - Kontrola płatności faktury zbiorcze

    Trzeba by zmieniać argumenty w zależności od przypadku...
  • #4
    fascynat
    Level 24  
    adamas_nt
    Czy napisana przez Ciebie funkcja ma ograniczenia co do długości ciągu tekstowego?
    Tzn. ile max nr rachunków może zawierać ciąg tekstowy?

    adamas_nt wrote:
    Problem w tym, że w 1-szym przypadku znakiem rozdzielającym jest podkreślnik, a w drugim spacja (dotyczy także VBA).

    Możesz wyjasnić dlaczego "(dotyczy także VBA)"?
  • #5
    User removed account
    User removed account  
  • #6
    fascynat
    Level 24  
    Jasne ale każdy program robi się do pracy przy pewnych założeniach, przedstawiłem propozycję do rozwinięcia, a nie gotowca.

    Marcin230 wrote:
    Zamiast szukać znaku rozdzielającego lepiej szukać 2 znaków przed i 4 znaków za /.

    Przy założeniu, ze rachunków będzie nie więcej niż 99 w roku?
    Widzę, że także przyjąłeś pewne założenie. :D
    Z chęcią zapoznam się z przykładowym kodem.
  • #7
    User removed account
    User removed account  
  • #8
    fascynat
    Level 24  
    Marcin230 wrote:
    Dlaczego 99?

    Dlatego, że napisałeś 2 znaki przed "/".
    A co jeżeli nr faktury będzie zapisany np. tak "10 /2011", bo nie da się ludziom narzucić reguły jak mają zapisywać.
    Tak można bez końca. :D
  • #9
    User removed account
    User removed account  
  • #10
    fascynat
    Level 24  
    Marcin230 wrote:
    fascynat wrote:
    Marcin230 wrote:
    Dlaczego 99?

    Dlatego, że napisałeś 2 znaki przed "/"

    :D
    Też prawda.
    Niestety trzeba przyjąć jednolite oznaczenie faktur, ale to dzieje się po stronie sprzedającego i jest łatwe do zrobienia.


    Coś szybko zmieniasz zdanie: :D
    Marcin230 wrote:

    Możliwości wyboru znaków rozdzielających jest dużo, a nie narzucisz ludziom jedynego słusznego sposobu rozdzielania, tak jak nie narzucisz konieczności płacenia tylko za jedną fakturę.
  • #11
    User removed account
    User removed account  
  • #12
    fascynat
    Level 24  
    Marcin230 wrote:
    Nie zmieniam, tylko dostosowuję.
    Zakładam możliwość zmian tylko po stronie wystawiającego faktury.

    Ale nie o to chodzi w tym zagadnieniu:
    riiv wrote:
    Wszystko działało doskonale dopóki jednak jeden z klientów nie zrobił płatności zbiorczej (3 faktury w jednym przelewie).

    "Problem" leży po stronie klientów, a nie wystawiającego.
    Dobra wystarczy tej dyskusji o teorii, wybór rozwiązania zależy od autora tematu.
  • #13
    User removed account
    User removed account  
  • #14
    marek003
    Level 40  
    A ja na koniec dorzucę swoje 3 grosze.
    Jako klient mam prawo się pomylić w opisie płatności - ważne że zapłaciłem na czas - a jeszcze jak dojdzie do tego płatność zbiorowa z pomyłkowym numerem płatności, to co wtedy? dostawca obciąży mnie odsetkami które mu się nie należą bo nie będzie "umiał" przyporządkować płatności?

    Niestety człowiek jest najbardziej "nieprzewidywalnym" ogniwem oprogramowania więc nie ustawiał bym do wszystkiego automatu.

    Z tym że wracając do tematu poprę kolegę marcina z tym wyszukiwaniem "/" i znakami przed i po. Jest to w miarę logiczne.
    A opis nie może być za długi więc i tych faktur nie będzie za dużo w jednym opisie. Banki z reguły przy wprowadzaniu opisu ograniczają to pole (z reguły poniżej 200 znaków)
  • #15
    fascynat
    Level 24  
    marek003 wrote:
    Z tym że wracając do tematu poprę kolegę marcina z tym wyszukiwaniem "/" i znakami przed i po. Jest to w miarę logiczne.
    A opis nie może być za długi więc i tych faktur nie będzie za dużo w jednym opisie. Banki z reguły przy wprowadzaniu opisu ograniczają to pole (z reguły poniżej 200 znaków)

    Ładnie i logicznie napisane.
    Np. "faktura nr 1 23 / 2011".
    Po garści teorii czas na praktyczne przykłady rozwiązań, bo na tym zależy autorowi tematu.
  • #16
    adamas_nt
    Moderator of Programming
    Ale się temperatura podniosła :)
    A wydawało się takie proste.

    Spacje można "wytrymować", jeśli nie jest to znak rozdzielający i tu mamy dylemat.
    Pomysł z odszukaniem "/" jest OK przy pewnych, poruszonych już założeniach. Czekam odpowiedzi autora.

    :arrow: marek003 Na razie mam skonstruowane funkcje na 6 rozdzieleń. 4 i 5 w tym przypadku mają po pół metra :) W miarę do ogarnięcia w razie podstawiania różnych "rozdzielników" to cztery.

    Zdaje się, że najbardziej elastyczne będzie jednak VBA. Przy kilku/nastu JEŻELI funkcje arkuszowe będziemy mierzyć na kroki :D Tu uwaga kolegi Marcin230, moim zdaniem bardzo trafna. W pierwszej kolejności należałoby znormalizować string do określonej formy, następnie brać się za rozdzielanie i liczenie.
  • #17
    Aldrin
    Level 22  
    Proponuję najpierw ujednolicić zapis - od strony programowej oczywiście, żeby do minimum zredukować szanse na błędy. Moim zdaniem można w VBA wykorzystać REPLACE i wykonać na samym początku serię zamian, np.:

    TRIM - na dzień dobry
    " /" -> "/"
    "/ " -> "/"
    " " -> " " (dwie spacje na jedną)
    " " -> "_"

    Myślę, że VBA da większe możliwości. Można chociażby po odszukaniu "/" zlokalizować rok (4 znaki) -> to co jest po nim będzie następną fakturą.

    Nie dzieliłbym też włosa na czworo w tych wszystkich dywagacjach. Jeśli nazwa faktury trzyma się jakiegoś wzorca i jest takich wystąpień więcej, to można to dołączyć do kodu, a jeśli będzie wydumana, to VBA wyrzuci to "do ręcznej korekty".
  • #18
    adamas_nt
    Moderator of Programming
    :arrow: Aldrin
    4/12 5/12
    i na dzień dobry nie wiemy na czym stoimy...
  • #19
    Aldrin
    Level 22  
    dlaczego nie wiemy??
    Rozdzielamy wg "_".
    EDIT: juz zmieniłeś ;)
  • #20
    adamas_nt
    Moderator of Programming
    Możliwości zapisu jest wiele. Poczekajmy na zdanie Autora.
  • #21
    Aldrin
    Level 22  
    Trim obcina spacje z zewnątrz

    EDIT: znowu zmieniłeś ;)

    Nie da się przewidzieć wszystkich możliwych pomysłów użytkowników. Program powinien uwzględniać różne możliwości. Na początku mamy zestaw tych wszystkich faktur, więc można na dzień dobry przejrzeć je i do nich zastosować odpowiednie algorytmy. Jaki to problem wyświetlić tylko te, które nie spełniają dotychczasowych (już istniejących) kryteriów i przyjrzeć się im szczególnie? Jeśli poddają się wzorcowi, to dołączyć do kryteriów.

    Nie dzielmy włosa na czworo.
  • #22
    adamas_nt
    Moderator of Programming
    Yyy, no skoro można, to zmieniam :)

    Zgadzam się z Twoim zdaniem. Trzeba string sprowadzić do jednolitej postaci. Trudniejsze pytanie: "od której strony ugryźć?" Najpierw musimy dowiedzieć się do jakiego stopnia może posunąć się fantazja klientów...

    Zmieniam :)
    Spotkałem się już z ukośnikiem w różne strony, pionową kreską, "O" zamiast zera, etc. O wielu spacjach w różnych miejscach nawet nie wspomnę...
  • #23
    User removed account
    User removed account  
  • #24
    marek003
    Level 40  
    Przepraszam że na chwilkę wrócę do postu wyżej

    fascynat wrote:
    marek003 wrote:
    Z tym że wracając do tematu poprę kolegę marcina z tym wyszukiwaniem "/" i znakami przed i po. Jest to w miarę logiczne.
    A opis nie może być za długi więc i tych faktur nie będzie za dużo w jednym opisie. Banki z reguły przy wprowadzaniu opisu ograniczają to pole (z reguły poniżej 200 znaków)

    Ładnie i logicznie napisane.
    Np. "faktura nr 1 23 / 2011".
    Po garści teorii czas na praktyczne przykłady rozwiązań, bo na tym zależy autorowi tematu.


    Myślę że kto jak kto ale Ty zrozumiałeś o co mi chodziło. Masz wiedzę na temat VBA i zapewnę zrozumiałeś moje skróty myślowe.
    Nie rozumiem jednak (tak jak inni) po co to wzburzenie.
    Podałeś jedno z rozwiązań [na marginesie nie głupie i nie ma tu nic do zarzucenia - ewentualnie ilości ElseIf określających dany separator].
    Dlatego [ja] uważałem że wyszukiwanie "/" jest lepszym rozwiązaniem (ale to moje zdanie i jak najbardziej możesz się nie zgadzać).
    (najlepiej by było gdyby autor wszystkie faktury rozpoczynał od jakiegoś ciągu znaków, np "FU" [faktura usługowa] lub coś podobnego - było by łatwiej. )
    A napisanie kodu makra to najmniejszy problem jak już się wie co się chce i jakie są ograniczenia i możliwości dotyczące danego rozwiązania.

    Poniżej przykład w VBA (który trzeba by było rozszerzyć o powyższe przemyślenia kolegów usuwające niepotrzebne spacje itd. ) z poszukiwaniem "/".

    Code: vb
    Log in, to see the code


    Niestety tak jak napisałem każdy ma prawo się pomylić i w tym momencie nic nie zrobimy oprócz "naocznego" sprawdzenia.

    Na marginesie: Wszystkie księgowe systemy które znam czekają na księgową która wskaże jaką płatność przyporządkować do jakiej faktury jeżeli system nie rozpozna danej faktury. A nawet jak rozpozna to i tak czeka na księgową by poparła jego sugestię.
  • #25
    marek003
    Level 40  
    Z porankiem coś mi jeszcze przyszło do głowy.
    A może podejść "odwrotnie" do zagadnienia.
    Np "pobrać" wszystkie nierozliczone numery faktur wystawione dla danego odbiorcy (na pewno będą prawidłowo wypisane bo to nasz system) i po ich numerach "przepatrzeć" dany opis zapłaty?

    Oczywiście cały czas pozostaje ujednolicenie opisu od klienta lub pozostawienie tego jako "do obróbki ręcznej".
  • #26
    riiv
    Level 2  
    Wow,

    dzięki wszystkim za taką pomoc. Nie spodziewałem się, że zrobi się taka zażarta dyskusja. Postaram teraz odnieść się do wszystkich pytań i odpowiedzi.

    Quote:
    Czy napisana przez Ciebie funkcja ma ograniczenia co do długości ciągu tekstowego?
    Tzn. ile max nr rachunków może zawierać ciąg tekstowy?


    Użyłem funkcji INDEKS,PODAJ POZYCJĘ oraz WYSZUKAJ PIONOWO więc chyba nie będzie problemu z ilością przeszukiwanych znaków. Zakładam, że nie zdarzy się płatność zbiorcza z więcej niż 6 fakturami.

    Quote:
    Problem w tym, że w 1-szym przypadku znakiem rozdzielającym jest podkreślnik, a w drugim spacja (dotyczy także VBA).


    Problem też jest taki, że klienci często nie wpisują tylko numeru faktury a tytuły na przykład wyglądają tak "Płatność_za_fakturę_11/2011".

    Quote:
    Zamiast szukać znaku rozdzielającego lepiej szukać 2 znaków przed i 4 znaków za /.


    To jest bardzo proste i dobre rozwiązanie. Niestety musiał bym do niego wprowadzić numerację faktury od numeru 1000 bo w firmie jest ponad 100 faktury i aby ujednolicić szukanie 4 znaki przed i 4 znaki po "/". Dodatkowo klienci dorzucają swoje znaki "/" w tytułach przelewu.

    Quote:

    Problem leży po stronie wystawiającego.
    Nie przewidział "twórczości" klientów.


    Tu się zgodzę :) nie przewidziałem twórczości klientów, a raczej tego, że cześć płatności będzie robiona przez centralę płatności w Indiach

    Quote:
    Jako klient mam prawo się pomylić w opisie płatności - ważne że zapłaciłem na czas


    Nie szukam systemu który będzie idealny (do tego musiał bym kupić jakiś płatny i drogi program do księgowości). Bardziej chodzi mi o to, żeby przyśpieszyć sobie i księgowości prace. Aby wszelkie pomyłki wyeliminować mój program sprawdza dane wpłacającego, (w tytule płatności) numer faktury uregulowanej oraz kwotę. Jeśli którakolwiek z tych 3 rzeczy się nie zgadza to wyskakuje mi prosty komunikat "kwota" lub "klient" i wtedy sprawdzam to ręcznie. Dlatego też klienci którzy się pomylą (a często wpisują np. 8 2/2011 zamiast 82/2011) są zaznaczani, że wpłacili za dużo pieniędzy i sam już modyfikuję za nich tytuł płatności w systemie.

    Quote:
    TRIM - na dzień dobry
    " /" -> "/"
    "/ " -> "/"
    " " -> " " (dwie spacje na jedną)
    " " -> "_"


    To jest ciekawe uproszczenie i na pewno użyję go. Musze być jednak wyjątkowo ostrożny bo tak jak powiedziałem często znajdują się dziwne dodatkowe znaki i liczby.

    Quote:
    Zastanawiałem się nad tym ciut i praktycznie niema możliwości wychwycenia błędu i/lub zrobienia automatu do tego. Po prostu zbyt mało możliwości naprawy.


    Zgadzam się nie ma możliwości zrobienia automatu działającego w 100%. Jeżeli jednak to co ja wymyślę będzie działać w 90% to zaoszczędzi mi i innym to kupę czasu.

    Quote:
    Np "pobrać" wszystkie nierozliczone numery faktur wystawione dla danego odbiorcy (na pewno będą prawidłowo wypisane bo to nasz system) i po ich numerach "przepatrzeć" dany opis zapłaty?


    Tak na wstępie mój system działał. Wyszukiwałem niezapłacone faktury i robiłem najprostsze wyszukaj "*"&11/2011&"*" w tytułach przelewów. Potem podaj pozycję i sprawdzał kwotę wraz z nazwą klienta. Poza fakturami od 1-9 i ponad 100 cały system działał.

    Quote:
    Poniżej przykład w VBA (który trzeba by było rozszerzyć o powyższe przemyślenia kolegów usuwające niepotrzebne spacje itd. ) z poszukiwaniem "/".


    Dzięki za tak czytelne opisanie wszystkiego. Zaraz sprawdzę jak to się sprawdza na podstawie starych faktur.

    Reasumując.

    Możliwości jest parę i z tego co widzę kluczem do sukcesu była by zmiana nazw faktur na dość unikalne np. "FU/2011/numer faktury". Wtedy dużo łatwiej było by zlokalizować taką fakturę. W końcu jest bardzo małe prawdopodobieństwo, że ktoś doda do tytułu przelewu własne oznaczenie płatności zaczynające się na "FU/2011/".


    VBA jest bardzo ciekawym pomysłem ale tak jak zaznaczyłem na wstępie jestem średni zaawansowany (a może raczej mało zaawansowany) i na pewno pobawię się tym ale nie wiem czy mam wystarczająco umiejętności, żeby to opanować. Ma jednak tą zaletę, że pozwala dodawać nieskończoną liczbę znaków rozdzielających.

    Jeszcze raz bardzo dziękuję wszystkim za pomoc.
  • #27
    fascynat
    Level 24  
    riiv poniższe pytania były skierowane do adamas_nt: :D
    fascynat wrote:
    adamas_nt
    Czy napisana przez Ciebie funkcja ma ograniczenia co do długości ciągu tekstowego?
    Tzn. ile max nr rachunków może zawierać ciąg tekstowy?

    adamas_nt wrote:
    Problem w tym, że w 1-szym przypadku znakiem rozdzielającym jest podkreślnik, a w drugim spacja (dotyczy także VBA).

    Możesz wyjasnić dlaczego "(dotyczy także VBA)"?

    Dla pełnej jasności, pod pojęciem ciągu tekstowego mam na myśli długość (ilość) znaków potrzebnych do napisania funkcji, a nie stringu w którym szukane są znaki. Są pod tym względem jakieś ograniczenia ze strony Excela?
  • #28
    User removed account
    User removed account