Wszystko o Excelu i VBA
Funkcja USUŃ.ZBĘDNE.ODSTĘPY() to jedna z funkcji tekstowych Excela. Zwraca wartość tekstową z usuniętymi niepotrzebnymi spacjami (na początku, na końcu i podwójne spacje w środku).
Funkcja ma jeden argument:
Odpowiednikiem funkcji w VBA jest funkcja Trim()
Poprawność danych to kolejne, bardzo przydatne narzędzie Excela. Zabezpiecza przed wstawieniem błędnych wartości w komórce, w wyniku których pojawią się błędy w formułach. Najczęściej jest wykorzystywane tam, gdzie powinna być data, a nie tekst przypominający datę czy wartości z określonego zakresu liczb. Umożliwia także wstawienie list rozwijalnych bezpośrednio w komórce.
Poprawność danych znajduje się na karcie Dane w grupie opcji Narzędzia danych.
Zaznaczamy komórkę lub zakres komórek, klikamy w przycisk
i wybieramy regułę poprawności:
Jest tu kilka możliwości ustawienia kryterium:
Konieczne jest także ustawienie zakresu precyzującego zakres, w którym muszą się mieścić liczby:
Mogą być tu nie tylko liczby, ale także odwołania do adresów komórek z liczbami
tu możemy wpisać własną regułę – opartą na formule
Niezależnie od rodzaju wprowadzonej reguły, Poprawność danych ma też 2 dodatkowe zakładki:
Ta akcja jest widoczna jeżeli wartość w komórce nie spełnia zasady wprowadzonej do niej reguły poprawności. Warto tu wpisać treść komunikatu informującego jakie wartości są tu dopuszczalne.
Możliwe są tu 3 warianty:
Zarówno Ostrzeżenia jak i Informacje pozwalają na wprowadzenie wartości niezgodnych w regułą poprawności. Chcąc sprawdzić, gdzie taka sytuacja występuje – wystarczy zaznaczyć opcję:
Błędne dane są zaznaczone. W tym miejscu można też usunąć zakreślenia błędnych danych.
Choć oczywiście te zaznaczenia znikają również po wprowadzeniu prawidłowych danych.
Często korzystam z tego narzędzia Excela – naprawdę warto jet stosować.
A tu możesz mi postawić kawę:
Menedżer nazw to jedno z narzędzi Excela, bardzo przydatne w codziennej pracy. Służy głównie do nadawania własnych nazw wyznaczonym zakresom komórek, obiektom, wartościom czy formułom.
Menedżer nazw znajduje się na karcie Formuły w grupie opcji Nazwy zdefiniowane.
Najszybszym i najprostszym sposobem na zdefiniowanie zakresu jest zaznaczenie obszaru, naciśnięcie przycisku Utwórz z zaznaczenia.
Nadana w ten sposób nazwa jest widoczna na liście nazw Menedżera nazw.
Na screenie widoczna jest także nazwa Tabela1 – wszystkie obiekty tabel od razu też są tu widoczne, automatycznie wstawiane przez Excela.
Zadeklarowana nazwa Kurs_Euro jest dostępna w każdym arkuszu tego skoroszytu i może być wykorzystana w każdej formule np.: =A2*Kurs_Euro
Można też przypisać nazwę do dynamicznego zakresu poprzez funkcję np.
=PRZESUNIĘCIE($E$2;;;ILE.NIEPUSTYCH($E$2:$E$100);1)
W tym przypadku wysokość zakresu jest uzależniona od ilości niepustych komórek w zakresie. Idealnie nadaje się do tworzenia list w Poprawności danych.
Przy tworzeniu nazw warto zwrócić uwagę na to, że dana nazwa może obowiązywać zarówno w całym skoroszycie jak i pojedynczym arkuszu. Domyślnym ustawieniem jest Skoroszyt:
Wszystkie nazwy muszą zaczynać się od znaku _ lub litery. Nie mogą też zawierać spacji ani niedozwolonych znaków. No i oczywiście – muszą być unikalne, nie mogą być takie same jak inne nazwy (np.oznaczenia komórek typu A1, b10 itp.)
Zdefiniowane w Excelu nazwy są też dostępne w edytorze VBA:
Range(„Kurs_Euro”)
lub
[Kurs_Euro]
A tu możesz mi postawić kawę:
Każde makro (również to nagrane) czy zdefiniowaną w kodzie VBA procedurę publiczną możemy uruchomić na kilka sposobów.
Najczęściej stosowane metody to:
i dalej:
Wybieramy makro/procedurę i naciskamy przycisk Uruchom.
Po przypisaniu makra – potem wystarczy kliknąć w kształt i makro zostanie uruchomione.
Chcąc przypisać skrót klawiaturowy do własnego makra zaznaczamy je na liście makr i przechodzimy do Opcji.
W nowym oknie przypisujemy skrót
Wielkość liter (mała/wielka) nie ma znaczenia, ale należy się poważnie zastanowić nad tym, czy nie nadpiszemy w ten sposób jakiegoś standardowego skrótu Excela. Tu niestety nie ma żadnych zabezpieczeń ani ostrzeżeń i w efekcie możemy stracić jakąś wbudowaną przydatną procedurę.
W VBA skrót klawiaturowy możemy przypisać za pomocą metody: Aplication.OnKey.
Standardowe, najczęściej używane skróty klawiaturowe Excela to:
| CTRL + N | nowy skoroszyt |
| CTRL + O | otwórz plik |
| CTRL + S | zapisz |
| CTRL + P | drukuj |
| CTRL + W / CTRL + F4 | zamknij skoroszyt |
| CTRL + Z | cofnięcie ostatniej operacji |
| CTRL + Y | ponowienie operacji |
| CTRL + A | zaznacz wszystko |
| CTRL + Strzałka | przejście do końca danych w kolumnie/wierszu |
| CTRL + Home | przejście do komórki A1 |
| CTRL + End | przejście do ostatniej używanej komórki |
| CTRL + C | kopiuj |
| CTRL + X | wytnij |
| CTRL + V | wklej |
| CTRL + D | wypełnij w dół |
| CTRL + R | wypełnij w prawo |
| CTRL + F | znajdź |
| CTRL + H | zamień |
| CTRL + T | utwórz tabelę |
| CTRL + K | wstaw hiperłącze |
| CTRL + ; | wstaw dzisiejszą datę |
| CTRL + SHIFT + : | wstaw aktualną godzinę |
| CTRL + SHIFT + L | włącz/wyłącz filtry |
| CTRL + Page Up / Page Down | przełączanie między arkuszami |
| CTRL + Tab | przełączanie między otwartymi skoroszytami |
Tych skrótów lepiej jednak nie używać do własnych procedur.
A tu możesz mi postawić kawę:
Dawno nie było nic o czasie, pora na przykładowe rozwiązanie. Tym bardziej, że zawsze warto przypominać, że Data i czas to liczba
Załóżmy, że mamy taką tabelkę:
Kolumna B – to czas Start, kolumna C– czas Stop.
Obie kolumny są sformatowane tak:
Kolumna D to formuła:
D2=JEŻELI(C2<B2;1+C2-B2;C2-B2)
formatowanie tej kolumny:
Takie formatowanie pozwala na wyświetlanie czasu w formie godzin i minut, ale pozwala na wyświetlenie również czasu powyżej 24 godzin (widać to w wierszu Sumy).
Kolumna E to formuła:
E2=JEŻELI(C2<B2;24*(1+C2-B2);24*(C2-B2))
formatowanie tej kolumny:
Tu mamy po prostu czas w liczbie godzin.
Kolumny D i E to kolumny, w których uwzględniono przypadek, gdy czas Stop jest mniejszy od czasu Start (czyli jest przejście przez północ). Gdyby nie było sprawdzenia tego warunku – czas byłby w tych przypadkach ujemny czyli w kolumnie E widoczna byłaby liczba ujemna, a w kolumnie D – widoczne były tylko „krzaczki” – czas nie może być ujemny.
A tu możesz mi postawić kawę:
