SzkoleniaMi.pl - Szkolenia WordPress, MS Excel, MS Project, Adobe Photoshop, SEO/SEM

Tworzenie oraz wykorzystanie własnych funkcji obliczeniowych w formułach.

MS Excel oferuje ciekawą funkcjonalność tworzenia i używania własnych funkcji w procesie edycji formuł. Wydawałoby się, że funkcje wbudowane powinny w pełni zaspokoić nasze potrzeby, jednak okazuje się, że czasami zamiast powielać kilkaset razy te same formuły wygodniej byłoby zastosować funkcję, która mogłaby wykorzystać te formuły.

W takim przypadku przychodzi nam z pomocą Visual Basic for Applications, czyli język programowania dla pakietu Office. Możemy go uruchomić bezpośrednio z zakładki Developer lub za pomocą skrótu klawiszowego: Alt+F11.

Programowanie w VB wymaga znajomości składni tego języka, możliwych do zastosowania funkcji i ich argumentów oraz zmiennych i wyrażeń.

Celem tego tutoriala nie jest nauka VB, a jego wykorzystanie w procesie tworzenia nowych funkcji, dlatego od razu zajmiemy się konkretnym przykładem stworzenia prostej funkcji liczącej prowizję dla PH na podstawie wartości sprzedaży [zł] oraz realizacji planu [%].

Założenia

Przyjmijmy, że wartość prowizji jest uzależniona od realizacji planu i wygląda następująco:

• przy ponad 100% realizacji planu – prowizja wynosi 5% wartości sprzedaży
• przy 100% realizacji planu – prowizja wynosi 2% wartości sprzedaży
• przy realizacji planu powyżej 80% (ale poniżej 100%) – prowizja wynosi 0,5% wartości sprzedaży
• poniżej 80% – brak prowizji

Budowa formuły

Moglibyśmy zbudować następującą formułę:

=JEŻELI(realizacja_planu>100%;sprzedaż*5%;JEŻELI(realizacja_planu=100%;sprzedaż*2%;JEŻELI(ORAZ(realizacja_planu>80%; realizacja_planu<100%);sprzedaż*0,5%;0)))

a następnie powielać ja do wszystkich komórek w których będzie potrzeba obliczenia prowizji.

Formuła została zagnieżdżona do 2-go poziomu, może to doprowadzić do łatwego popełnienia błędu, natomiast w sytuacji zmian w systemie prowizyjnym będzie wymagało wprowadzenia zmian we wszystkich komórkach w jakich została wpisana.
Lepszym rozwiązaniem będzie stworzenie funkcji o nazwie: PROWIZJA, która będzie w taki sam sposób obliczała wartość prowizji dla PH, jednak wszystkie warunki zostaną zapisane w VB.

Definiowanie funkcji

Otwieramy edytor VB i dodajemy nowy Modul. Następnie tworzymy funkcję, której kod prezentuje się następująco:

Option Explicit
Function Prowizja(sprzedaz As Double, realizacja_planu As Double)
	If realizacja_planu > 1 Then
		'realizacji planu > 100% - prowizja 5%
		Prowizja = sprzedaz * 0.05
	ElseIf realizacja_planu = 1 Then
		'realizacji planu = 100% - prowizja 2%
		Prowizja = sprzedaz * 0.02
	ElseIf (realizacja_planu > 0.8 And realizacja_planu < 1) Then
		'realizacji planu 80%-100% - prowizja 0.5%
		Prowizja = sprzedaz * 0.005
	Else
		'realizacji planu <= 80% - prowizja 0
	End If
End Function

Użycie nowej funkcji

Aby zastosować w formule nową funkcję wystarczy użyć jej analogicznie jak każdą inną funkcję.

=Prowizja(sprzedaż; realizacja_planu)

Zaletą takiego rozwiązania jest elastyczność pozwalająca na szybką zmianę warunków obliczania prowizji poprzez zmianę jej parametrów jedynie w jednym miejscu – w definicji funkcji Prowizja w VB, nie zaś we wszystkich komórkach w których jest użyta formuła.
Warto zauważyć, że w przykładzie argumentami funkcji są nazwy zakresów komórek w arkuszu, ale można użyć zamiast nich także odwołań do komórek.

Kolejnym plusem takiego rozwiązania jest możliwość ukrycia obliczeń przed użytkownikiem arkusza (pracownikiem) poprzez stworzenie hasła uniemożliwiającego edycję kodu VB.

Możliwe jest także użycie funkcji w innych arkuszach i zapisanie jej w szablonie głównym MS Excel.