Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

EXCEL - Kontrola płatności faktury zbiorcze

riiv 02 Sty 2012 13:34 3388 27
  • #1 02 Sty 2012 13:34
    riiv
    Poziom 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.

    0 27
  • #2 02 Sty 2012 18:43
    fascynat
    Poziom 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.

    Kod: vb
    Zaloguj się, aby zobaczyć kod

    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

    1
  • #3 02 Sty 2012 19:14
    adamas_nt
    Moderator Programowanie

    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...

    0
  • #4 02 Sty 2012 19:18
    fascynat
    Poziom 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 napisał:
    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)"?

    0
  • #5 02 Sty 2012 19:21
    447988
    Użytkownik usunął konto  
  • #6 02 Sty 2012 19:28
    fascynat
    Poziom 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 napisał:
    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.

    0
  • #7 02 Sty 2012 19:33
    447988
    Użytkownik usunął konto  
  • #8 02 Sty 2012 19:35
    fascynat
    Poziom 24  

    Marcin230 napisał:
    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

    0
  • #9 02 Sty 2012 19:40
    447988
    Użytkownik usunął konto  
  • #10 02 Sty 2012 19:40
    fascynat
    Poziom 24  

    Marcin230 napisał:
    fascynat napisał:
    Marcin230 napisał:
    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 napisał:

    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ę.

    0
  • #11 02 Sty 2012 19:42
    447988
    Użytkownik usunął konto  
  • #12 02 Sty 2012 19:44
    fascynat
    Poziom 24  

    Marcin230 napisał:
    Nie zmieniam, tylko dostosowuję.
    Zakładam możliwość zmian tylko po stronie wystawiającego faktury.

    Ale nie o to chodzi w tym zagadnieniu:
    riiv napisał:
    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.

    0
  • #13 02 Sty 2012 19:56
    447988
    Użytkownik usunął konto  
  • #14 02 Sty 2012 20:15
    marek003
    Poziom 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)

    0
  • #15 02 Sty 2012 20:56
    fascynat
    Poziom 24  

    marek003 napisał:
    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.

    0
  • #16 02 Sty 2012 20:58
    adamas_nt
    Moderator Programowanie

    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.

    0
  • #17 02 Sty 2012 21:06
    Aldrin
    Poziom 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".

    0
  • #18 02 Sty 2012 21:10
    adamas_nt
    Moderator Programowanie

    :arrow: Aldrin
    4/12 5/12
    i na dzień dobry nie wiemy na czym stoimy...

    0
  • #19 02 Sty 2012 21:11
    Aldrin
    Poziom 22  

    dlaczego nie wiemy??
    Rozdzielamy wg "_".
    EDIT: juz zmieniłeś ;)

    0
  • #20 02 Sty 2012 21:13
    adamas_nt
    Moderator Programowanie

    Możliwości zapisu jest wiele. Poczekajmy na zdanie Autora.

    0
  • #21 02 Sty 2012 21:20
    Aldrin
    Poziom 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.

    0
  • #22 02 Sty 2012 21:22
    adamas_nt
    Moderator Programowanie

    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ę...

    0
  • #23 02 Sty 2012 21:50
    447988
    Użytkownik usunął konto  
  • #24 03 Sty 2012 00:11
    marek003
    Poziom 40  

    Przepraszam że na chwilkę wrócę do postu wyżej

    fascynat napisał:
    marek003 napisał:
    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 "/".

    Kod: vb
    Zaloguj się, aby zobaczyć kod


    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ę.

    0
  • #25 03 Sty 2012 08:41
    marek003
    Poziom 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".

    0
  • #26 03 Sty 2012 10:27
    riiv
    Poziom 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.

    Cytat:
    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.

    Cytat:
    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".

    Cytat:
    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.

    Cytat:

    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

    Cytat:
    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.

    Cytat:
    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.





    Cytat:
    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.

    Cytat:
    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ł.

    Cytat:
    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.

    0
  • #27 03 Sty 2012 18:10
    fascynat
    Poziom 24  

    riiv poniższe pytania były skierowane do adamas_nt: :D

    fascynat napisał:
    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 napisał:
    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?

    0
  • #28 04 Sty 2012 01:03
    447988
    Użytkownik usunął konto