Definiowane Funkcje Użytkownika w Arkuszu Kalkulacyjnym

Arkusze Kalkulacyjne są wygodnym i często wykorzystywanym narzędziem analitycznym stosowanym przez analityków, księgowych, kierowników finansowych, itp.

Często dane do arkusza wprowadzane są ręcznie na podstawie raportów otrzymanych z Księgowego systemu ERP, lub ładowane z Rejestrów księgowych w formie tabel, które następnie są ręcznie formatowane (kopiowane i wklejane w odpowiednie miejsca arkusza wynikowego).

Metoda ręcznego przetwarzania arkuszy obarczona jest ryzykiem popełnienia błędu przy kopiowaniu lub przepisywaniu danych, a dodatkowo wymaga dużego nakładu czasu. Wreszcie dane w systemie ERP szybko się zmieniają, więc arkusze w większości przypadków są nieaktualne i przedstawiają analizy „na wczoraj”.

Niektóre systemy ERP posiadają dedykowane dodatki do MS Excel, jednak rozwiązania takie są drogie i niedostępne dla użytkowników mniejszych systemów księgowych.

Program-JPK PRO posiada wbudowany Arkusz Kalkulacyjny, który obsługuje standardowe funkcje zgodne z MS Excel. Dodatkowo zawansowany użytkownik (programista) otrzymuje mechanizm do tworzenia własnych funkcji, które mogą realizować dowolne dynamiczne obliczenia w trybie rzeczywistym, w tym pobierać dane z różnych baz danych SQL systemów ERP. Jeden arkusz może prezentować dynamiczne analizy porównawcze np. z programu handlowego opartego na bazie MySQL (np. sklep internetowy) i jednocześnie programu Finansowo Księgowego opartego na bazie MS SQL Server.

Analizy dostępne są nawet dla użytkowników starszych aplikacji pracujących w oparciu o tabele DBF (Clipper).

Opis mechanizmu tworzenia własnych funkcji rozpoczniemy od bardzo prostego przykładu.

Edytor z funkcjami otwieramy w Menu/Plik/Funkcje C#

Jeżeli w programie nie ma jeszcze funkcji użytkownika, to automatycznie tworzona jest pusta klasa. W powyższym przykładzie dodano dwie funkcje JPK() oraz F1().

Funkcje nie muszą mieć parametrów, w takim przypadku wykonują tylko stałe obliczenia i zwracają dowolne wartości. W przykładzie funkcja JPK() zwraca tylko tekst Program-JPK PRO (który wcześniej mógłby zostać odpowiednio sformatowany przez funkcję).

Zazwyczaj funkcje będą potrzebować parametrów i taki przykład prezentuje funkcja F1(), która ma dwa parametry, ale ich liczba może być dowolna. Zalecamy definiować parametry jako typ object i wewnątrz funkcji (w kodzie C#) wykonywać odpowiednie konwersje. Uchroni nas to przed błędami kompilatora: Typy niezgodne lub funkcja nie została znaleziona z powodu innej definicji typów. Takie przypadki mogą wystąpić gdy w komórkach arkusza wprowadzimy tekst, a funkcja spodziewa się liczby. W naszym przypadku konwersja ToDouble() automatycznie zwróci zero, jeżeli wartość przekazanego parametru jest błędna (np. tekst).

Wprowadzanie funkcji w komórce Arkusza wykonujemy wg stałego schematu:

=UDF("Nazwa funkcji") jeżeli zdefiniowana funkcja nie posiada parametrów, w przykładzie =UDF("JPK")

lub

=UDF("F1";p1)

=UDF("F1";p1;p2)

=UDF("F1";p1;p2;p3) itd. Jeżeli funkcja posiada parametry.

UWAGA! Nazwy funkcji oraz nazwy parametrów są dowolne. Liczba funkcji wewnątrz skryptu również jest dowolna.

Po utworzeniu rozszerzenia C# z definicją własnych funkcji, zmiany należy zapisać.

Podczas startu Arkusza funkcje są automatycznie ładowane i użytkownik nie musi wykonywać żadnych czynności. Od razu można je stosować przy definiowaniu nowych analiz lub załadować arkusz XLS/XLSX, w którym używane są funkcje użytkownika.

Zapoznaj się z następnym dokumentem: Definiowane Funkcje Użytkownika - obliczenia SQL