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ą).
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
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).
Trzeba by zmieniać argumenty w zależności od przypadku...
Zamiast szukać znaku rozdzielającego lepiej szukać 2 znaków przed i 4 znaków za /.
@fascynat Bo musisz uwzględnić wcześniej wszystkie możliwe "znaki rozdzielające"?
Twoja formuła działa, ale wyobraź sobie, że ktoś wpisze "10/2011 i 11/2011, no i 12/2011, dodam też .... itd.".
Zrobisz formułę szukającą znaku rozdzielającego?
Jasne ale każdy program robi się do pracy przy pewnych założeniach, przedstawiłem propozycję do rozwinięcia, a nie gotowca.
Ojj.
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ę.
fascynat wrote:
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?
Widzę, że też przyjęłeś pewne założenie.
Dlaczego 99?
Założenie przyjąłem takie, że oznaczenie faktury jest jednolite, a możliwości rozdzielania sporo.
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.
Też prawda.
Niestety trzeba przyjąć jednolite oznaczenie faktur, ale to dzieje się po stronie sprzedającego i jest łatwe do zrobienia.
Dodano po 2 [minuty]:
fascynat wrote:
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.
Hmm. Nie pomyślałem o tym. Masz rację.
Wniosek jest taki, że numer faktury to za mało danych do w pełni poprawnej weryfikacji.
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:
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ę.
Problem leży po stronie wystawiającego.
Nie przewidział "twórczości" klientów.
Na szczęście problem dotyczy tylko płatności zbiorowych.
Teoretycznie dość łatwo go rozwiązać, bo wystarczy rozbić płatność zbiorową, sprawdzić pojedyncze numery faktur i oznakować faktury pojedyncze i płatność zbiorową jako poprawną.
Jak to zrobić w VBA nie mam pojęcia, na kombinowanie bez VBA nie mam teraz zbytnio czasu, ale jest to ciekawe zagadnienie i zapewne do niego wrócę
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)
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.
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.
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 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.
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".
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.
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ę...
Żeby było dobrze, to nie można weryfikować jedynie po numerze faktury.
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.
Ale... powiązać wystarczy kontrahenta, numer faktury i kwotę, a już jest łatwiej.
Ciekawe jak załatwiają zbiorówki automaty w profesjonalnych programach. WF-MAG nie miał takiej funkcji o ile pamiętam.
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ę.
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".
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.
riiv poniższe pytania były skierowane do adamas_nt:
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?
Jeżeli zastosuje się jednolity system znakowania faktur, to panowie od VBA mogą doradzić coś prostego w miarę.
Załóżmy, że znakujemy FV-xxxx/yyyy (xxxx- numer kolejny, yyyy - oznaczenie stałe, przykładowo rok), zarówno x jak i y to cyfry.
1. Szukanie faktury-
Przeszukujesz ciąg płatności pod kątem "FV". Jakiekolwiek coś pomiędzy dwoma "FV" oznacza fakturę.
2. Szukanie numeru faktury-
Wyciągnięte w punkcie 1 dane oznaczają jakąś fakturę, mogą tam być dodatkowe oznaczenia kontrahenta lub jego błędy, ale faktura oznaczany jest jako FV-xxxx/yyyy (cyfry), zatem przeszukujesz ciąg pod kątem cyfr biorąc pod uwagę dodatkowy warunek jakim jest "/", a raczej ilość cyfr przed ukośnikiem i za ukośnikiem (dla niwelacji błędu można przyjąć / lub \).
Jeżeli przed ukośnikiem i za nim brakuje cyfr w odpowiedniej ilości, to skrypt wskazuje błąd i jednocześnie zwraca pełny "numer" (nawet błędny) żeby łatwo było go poprawić ręcznie. Jeżeli numeracja jest poprawna i zgodna ze wzorcem, to ją uznaje i zostawia do zatwierdzenia.
Pętla do zakończenia.
Żeby sobie polepszyć dodatkowo można zastosować wcześniejsze propozycje (trim), można przyjąć, że "o" i "0" jest podobne i jeśli się trafi litera "o" w miejscu cyfry, to jest to faktycznie cyfra.
marek003 Na razie mam skonstruowane funkcje na 6 rozdzieleń. 4 i 5 w tym przypadku mają po pół metra 