Moje doświadczenia z Excelem, Accesem i programowaniem w VBA

Wpisy z tagiem: wstawianie funkcji

czwartek, 06 września 2012

Zeszyt makr osobistych to taki skoroszyt Excela, w którym można zapisać funkcje i makra uniwersalne czyli dostępne w dowolnym skoroszycie. Taki zeszyt makr osobistych ma nazwę Personal.xlsb i jest otwierany w tle, w sposób ukryty. Pod warunkiem, że wcześniej go aktywujemy...

Aktywację skoroszytu makr osobistych wykonujemy raz. Wystarczy nagrać proste makro, zapisując je właśnie w zeszycie makr osobistych.

skoroszyt makr osobistych

Wystarczy to zrobić raz i od tej pory każde wejście do Excela będzie jednoznaczne z załadowaniem tego skoroszytu. Wprawdzie będzie to to tylko w tle, ale makra i funkcje będą do dyspozycji.

Samo wejście do skoroszytu - w tym do edycji modułów wykonujemy przez wstęgę Widok --> Odkryj:

skoroszyt makr osobistych


W nowym oknie wystarczy zaznaczyć skoroszyt Personal. 

skoroszyt makr osobistych

Po wejściu do arkusza można w normalny sposób przejść do edycji czy dopisywania makr w kodzie VBA.


wtorek, 09 marca 2010

Pierwsza własna funkcja już napisana (notka: Piszemy funkcję VBA) , teraz pora na jej wykorzystanie w arkuszu Excela. Ja kto zrobić? Identycznie jak wstawianie każdej innej funkcji, wybierając z listy rodzajów funkcji - funkcje użytkownika:

wstawianie funkcji użytkownika

 Po wybraniu kategorii Użytkownika pojawia się lista wszystkich funkcji tej kategorii:

funkcje użytkownika

Lista jest alfabetyczna. W tym przypadku na jej końcu znajduje się funkcja PodwojonaSumaKomorek, której kod VBA opisywałam tu:
Piszemy funkcję w VBA

Wybieramy tę funkcję i wstawiamy dane jak do każdej zwykłej funkcji

wstawianie funkcji własnej użytkownika

Argumentami funkcji są PierwszySkladnik i DrugiSkladnik - czyli te zmienne, które w kodzie funkcji były wpisane w w pierwszej linii, przy deklaracji funkcji:

Public Function PodwojonaSumaKomorek(PierwszySkladnik As Integer, DrugiSkladnik As Integer) As Integer

Wstawiamy komórki w poszczególne pola funkcji i już - funkcja działa i oblicza.
Warto przy tym zwrócić uwagę, że w ramach kodu funkcji PodwojonaSumaKomorek - argumenty funkcji zostały zdeklarowane jako liczby typu Integer czyli całkowite. Jeżeli w komórkę wstawioną do funkcji wpiszemy liczbę rzeczywista - Excel odrzuci to co po przecinku i obliczy wartość funkcji tylko dla części całkowitej. Jeżeli pojawią się jednak np. wartość typu String - wynikiem działania funkcji będzie błąd.
Ale wszsytko przed nami - na obsługę błędów i zabezpieczenia przed nieprzewidywalnymi działaniami użytkowników też przyjdzie pora.....

piątek, 19 lutego 2010

Poprzednia notka Funkcje logiczne Excela: Jeżeli... to.... opisywała wstawianie pojedyńczej formuły. Prosty przykład ustalający znak liczby w sąsiedniej komórce z matematycznego punktu widzenia daje nam jednak nieco zafałszowany wynik. Wstawienie w komórce liczby "0" powoduje otrzymanie wyniku "+", a tak naprawdę to wprawdzie zero jest liczbą nieujemną, ale nie jest to wcale tożsame z tym, że jest to liczba dodatnia. Konieczne jest więc rozbudowanie wprowadzonej formuły.
Podstawowe pytanie: B1<0 pozostaje bez zmiany, podobnie jak wartość_jeżeli_prawda. Zmiany dokonujemy w polu wartość_jeżeli_fałsz. W oknie dialogowym formuły wstawiamy więc kolejną funkcję - wybierając ją z okienka po lewej stronie. Domyśłnie pokazują sie tam ostatnio używane funkcje, ale jest też opcja "więcej funkcji".  Wybieramy funkcję "Jeżeli":


funkcja zagnieżdżona

Otwiera się nowe okno dialogowe. Z punktu widzenia algorytmu - uściślamy teraz warunek - jaki ma być wynik formuły, jeżeli w komórce nie ma liczby ujemnej. Najprościej więc ocenić wartość logiczną zdania: B1=0. Jeżeli jest to prawda - to wynik ma być "+/-", jeżeli fałsz - "+".

funkcja zagnieżdżona

W okienku formuły zapis ten wygląda tak:
=JEŻELI(B1<0;"-";JEŻELI(B1=0;"+/-";"+")).

Oczywiście jest to tylko przykład, ilustrujący mechanizm wstawiania funkcji w funkcjach. Takich zagnieżdżeń funkcji i to różnych - można wstawiać więcej. Również w polu test_logiczny.
I najlepiej pobawić się tym samemu. To najlepszy sposób nauki.

czwartek, 18 lutego 2010

Funkcja logiczna "Jeżeli...to..." jest jedną z moich ulubionych i czesto wykorzystywanych. Zsada jej działania jest prosta. Jeżeli jakiś warunek jest spełniony - to komórką z wstawiona formułą ma jedną wartość , jeśli nie jest - inną.

Jeżeli..to...

Wyobraźmy sobie prosty przykład, że komórka A1 ma sprawdzać znak liczby w komórce B1. W  Test_logiczny wpisujemy (można też zaznaczyć komórkę) B1<0. W polu Wartość_jeżeli_prawda "-" (znak minusa w cudzysłowie, gdyż ten minus stanowi w tym przypadku tekstowy znak graficzny. Analogicznie w polu Wartość_jeżeli_fałsz "+".  Potem wystarczy tylko kliknąć przycisk OK.

Zapis  formuły wyglądać będzie tak:
=JEŻELI(B1<0;"-";"+")
Na czerwono zaznaczyłam średniki - oznaczają one koniec poszczególnych pól formuły. Widać ten zapis także w arkuszu, w polu formuły.

Funkcja Jeżeli to

A od strony praktycznej - lubię tę funkcję i często z niej korzystam do tworzenia raportów. W jaki sposób? W pracy sporo pracujemy na udostępnionych plikach Excela na serwerze. Są to arkusze z dużą ilością modyfikowanych i dodawanych danych, nawet ilośc wierszy ulega zmianie. Dołożenie jeszcze dodatkowych raportów - stanowiłoby niepotrzebne obciążenie. Tabeli przestawnej w ogóle nie da się stworzyć i uaktualnić w arkuszu udostępnionym. W takich przypadkach na swoim komputerze tworzę osobny skoroszyt raportów i wstawiam łącza do poszczególnych komórek pliku źródłowego. Problem jednak w tym, że proste łącze:
=[Plik_zrodlowy.xls]Arkusz!$A$1
i analogicznie - w pozostałych komórkach - wszędzie tam, gdzie w pliku źródłowym jest pusta komórka - łącze wskaże "0". Zero jednak też jest wartością i może to całkowicie zafałszować raport. I w tym momencie - korzystam właśnie z funkcji Jeżeli... to...
W pliku raportu, w komórce A1 wstawiam funkcję:
=Jeżeli(Plik_zrodlowy.xls]Arkusz!A1="";"",[Plik_zrodlowy.xls]Arkusz!A1)
Jak to czytać? Jeżeli komórka A1 w pliku żródłowym jest pusta (dwa znaki cudzysłowu obok siebie - "" - to oznaczenie pustego łańcucha tekstowego) - to wynik formuły jest też pusty. W przeciwnym przypadku (wartość jeśli fałsz) - wynkiem komóki jest wartość komórki pliku źródłowego.
Metoda wielokrotnie sprawdzona i wykorzystywana. Nawet nie muszę pilnować ilości wierszy - i tak pokażą się tylko te potrzebne.

wtorek, 16 lutego 2010

Jak wstawić funkcję do komórki?

 W starszych wersjach Excela wygląda to tak:

 wstawianie funkcji

W Excelu 2007 - bardzo podobnie:

wstawianie funkcji

Wybieramy kategorię funkcji, a następnie interesująca nas funkcję.
Spróbujmy zobaczyć to na przykładzie funkcji średniej arytmetycznej

 wstawianie funkcji

W komórce B3 wstawiamy funkcję ŚREDNIA (jedna z funkcji statystycznych). Komórki, z których chcemy wyznaczyć możemy wpisać ręcznie lub - szybciej i wygodniej - zaznaczyć myszką. Wciskając przycisk przejścia do arkusza (na rysunku zaznaczony) - możemy wybrać potrzebną komórkę (lub zakres komórek).

wstawianie funkcji

Jeżeli komórki są obok siebie - można wszystkie je zaznaczyć od razu. Powrót do okna funkcji - poprzez przycisk z prawej strony.

wstawianie funkcji

Teraz wystarczy nacisnać przycisk OK - w komórce już pojawia się obliczona wartość.

wstaw funkcję

Do okna dialogowego funkcji można w każdej chwili wrócić naciskając zaznaczony na rysunku przycisk. Z jego prawej strony widoczny jest zapis formuły. Można ją także bezpośrednio edytować - np. zmienic adresy względne na bezwzględne.

 

poniedziałek, 15 lutego 2010

Jak wstawić wiele danych do formuły? Ręczne wpisywanie adresów może być bardzo żmudne, w dodatku łatwo o pomyłki. Od czego jednak myszka? Obliczmy sumę danych korzystając z autosumowania:

 zaznaczanie zakresu danych w formule

Proste, gdy zakres jest w jednym arkuszu i  dodatku ciągły. A gdy tak nie jest? Wówczas zaznaczamy każdą z pojedyńczych komórek (lub zakresów) trzymając jednocześnie wciśnięty klawisz Ctrl

 wstawianie danych do formuły

Dla przypadków dowolnej funkcji, wstawiając formułę (w menu wybieramy Wstaw - Funkcja i z listy wybieramy konkretną funkcję) i to złożoną z danych w innym arkuszu czy nawet skoroszycie korzystamy z okna dialogowego. Klikając przycisk przejścia do arkusza - okienko zostanie zminimalizowane i mamy możliwość zaznaczenia intersujacych nas komórek arkusza.

wstawiane danych do formuły

W ten sposób możemy zaznaczyć także dane z innego arkusza tego samego skoroszuty (oo prostu przechodząc do niego) lub innego skoroszytu (musi być wcześniej otwarty w tym samym oknie). Powrót - poprzez przycisk z prawej strony oknienka dialogowego:

wstawianie danych do formuły

Pełne okno dialogowe pozwala w tym momencie innego, niezależnego zakresu (tym razem wybieramy "Liczba2" - analogicznie - dalej. Jesli sa takie potrzeby.

wstawianie danych do formuły

Ostatecznie poprzez przycisk OK - wracamy do komórki, w której wpisywaliśmy formułę. Wynik gotowy.

Warto przy tym pamiętać, ze  w ramach tego samego skoroszytu - Excel wstawia adresy względne. Jeżeli w formule mamy dane z innego arkusza tego samego skoroszytu - to zmiana nazwy tego arkusza - spowoduje autmatyczną zmianę jego nazwy również w formułach. Nie mam potrzeby nic poprawiać.
Jeżeli korzystamy z danych innego skoroszytu - adresy komórek defaultowo są bezwzględne. I nie ma tu automatycznej korekty formuł w przypadku zmiany nazwy.

| < Lipiec 2017 > |
Pn Wt Śr Cz Pt So N
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31            


Książki warte polecenia
zobacz szczegóły...


A tu oferta na dziś:





ministat liczniki.org



Napisz do mnie!