tupaj.com

Czasem może zajść potrzeba aby kilkaset lub więcej nazw zmienić tak aby zaczynały się z dużej litery. O ile przypadek że zmieniamy nazwy z samych małych liter może być dość rzadki o tyle zmiana z samych dużych może występować częściej. Do zamiany ciągów znaków w taki sposób służy funkcja tekstowa Z.WIELKIEJ.LITERY()

W wersji anglojezycznej excela funkcja ta nosi nazwę PROPER()

Ostatnio przeinstalowałem MS Office z wersji 2003 na wersję 2010. Oczywiście jedną z pierwszych czynności, którą zrobiłem po zainstalowaniu było sprawdzenie dostępu do funkcji które poprzednio mnie interesowały czyli formatowanie warunkowe, makra i reguły. Ponieważ menu jest znacząco przeorganizowane w stosunku do tego z wersji 2003 więc z początku miałem małe trudności, ale i tak trzeba przyznać że obsługa jest intuicyjna. Postaram się nieco o tym napisać w najbliższych dniach.
Na razie napiszę tylko jak dobrnąć do funkcji tworzenia makr.
Z funkcjami rejestracji makr można zacząć przygodę od użycia zakładki View

W tym przypadku brakuje jednak możliwości tworzenia przycisków które uruchamiają makro. Aby mieć dostęp do szerszego pakietu funkcji związanych z makrami należy uaktywnić zakładkę Developer znajdującą się w zakładce File - options

Szczegółowo pokazane jest to poniżej - kliknij obrazek aby powiększyć.

Warto korzystać z tej wersji. Sama obsługa makr nie różni się prawie niczym od wersji 2003.

Jedną rzeczą którą zauważyłem to rozdzielenie rodzajów plików wynikowych na te które są z makrami i na te które ich nie zawierają. Myślę że jest to dobre posunięcie ze strony Microsoftu.

Poniżej link do książki na temat poprzedniej wersji office. Kiedy pojawi się książka o aktualnej wersji dokonam podmiany linku.

Excel pozwala na wydajną pracę w pojedynkę, może się jednak zdarzyć że potrzeba aby kilka osób działało jednocześnie na jednym pliku.
Przy standardowych ustawieniach pliku w excelu jest to niemożliwe i przy próbie dostępu do pliku otrzymujemy komunikat że dany skoroszyt jest tylko do odczytu, a więc więcej niż jedna osoba nie może zapisywać zmian w arkuszach w tym samym czasie.
Funkcja jednoczesnego dostępu do edycji dla kilku osób znacząco rozszerza zakres możliwości Excela. Jest też jeden duży plus takiej funkcji (mam na myśli sieć wewnątrz-firmową) a polega on na tym że nie musisz dzwonić do kolegi czy koleżanki aby na chwilę wyszła z edycji arkusza bo akurat ty musisz coś super ważnego w nim zmienić :).
Po prostu edytujesz i zapisujesz. W przypadkach konfliktowych decydują kryteria zapisu skoroszytu. Kryteria te można ustalić na początku udostępniania a potem w razie potrzeby zmodyfikować.

Aby włączyć udostępnianie skoroszytu w Excelu 2000/2003 należy wejść do menu Narzędzia, a następnie kliknąć pozycję Udostępnij skoroszyt

Po otwarciu funkcji udostępniania arkusza widzimy okno

Zaznaczenie pola jak pokazano wyżej pozwala na równoczesną edycję tych samych danych przez kilku użytkowników naraz pozwala też na scalanie skoroszytu.

Poza tym zaznaczenie jej pozwala na zmianę bardziej szczegółowych opcji w następnej zakładce.

W powyższym przypadku zaznaczyłem opcję przechowywania historii zmian oraz opcję uaktualniania pliku po zapisie. Dobór opcji najlepiej dostosować do własnych wymagań. Ze względu na brak wewnętrznej sieci do przetestowania w trakcie pisania artykułu nie będę wnikał w szczegóły. W miarę możliwości wrócę do tego tematu w innym czasie. Uzupełniając artykuł odpowiednimi zrzutami ekranu.

Przykładem wielodostępnego skoroszytu jest skoroszyt obsługujący prosty sklep a w jego ramach magazyn i sprzedaż (oraz raporty dla managera). Gdy pojawia się nowy towar na magazynie wówczas dane o nowych produktach i ich cena są wprowadzane przez magazyniera, stają się one równocześnie (po zapisaniu skoroszytu) widoczne dla sprzedającego, który widzi że ilość danego produktu wzrosła. Managerowi aktualizują się tabele i wykresy obrazujące obrót w sklepie

Bardziej w tej roli widziałbym oczywiście Dokumenty Google jednak wymaga to aby wszyscy użytkownicy mieli konto w google i wątpliwe wydaje mi się bezpieczeństwo danych w przypadku ważnych informacji handlowych.

Kiedyś pisałem o prostej metodzie zliczania ilości lub wartości komórek która polega na odczytaniu wartości w dolnym prawym rogu arkusza po zaznaczeniu grupy komórek. Nie wszystkie jednak wartości dają się w ten sposób policzyć. Poniżej przedstawiam metodę skuteczną w 100% choć wymagającą odrobinkę pracy. Zlicza ona z powodzeniem zarówno liczby jak i tekst a polega na specyficznym wykorzystaniu możliwości funkcji JEŻELI. Mianowicie funkcja jeżeli daje 1-kę w przypadku wystąpienia danego tekstu lub wartości a 0 (zero) w przypadku niewystąpienia poszczególne warunki trzeba umieścić odrębnych kolumnach a sumę jedynek zliczyć. PROSTE nieprawda ?
Przykład:
W kolumnie A występują w różnej ilości 3 imiona (np. Jan, Piotr, Celina) począwszy od komórki A2.
Chcąc zliczyć ilość wystąpień imienia Jan piszemy w kolumnie B lub kolejnej takie wyrażenie
= JEŻELI(A2=”Jan”,1,0) lub = JEŻELI(A2=”Jan”,1,) w kolejnych kolumnach to samo możemy zrobić dla pozostałych imion pamiętając aby w pierwszym wierszu obliczeń (tym samym co dla “Jan”) odnosić się do komórki A2 (a nie np B2) bo wyjdzie nie to co chcemy.
Gotowe warunki “przeciągamy” w dół aby obliczenia obowiązywały dla wszystkich wystąpień wyrazów a następnie podliczamy. Pokazałem to na rysunku poniżej.

Powyższy przykład jest prosty ale możliwości tego typu rozwiązania są duże. Sam często stosowałem je w praktyce. Sama technika jest przejrzysta i łatwa do sprawdzenia. Polecam.

Sumowanie za pomocą reguł i autofiltry pomagają szybko zliczyć komórki jeśli zaznaczymy jakiś większy ich zakres. Są sytuacje w których taki sposób zliczania zaznaczonych komórek jest trochę nieopłacalny czasowo, jakiej więc funkcji excela użyć w sytuacji gdzie trzeba szybko zsumować lub zliczyć ilość zaznaczonych komórek. Z pomocą przychodzi funkcja widoczna w dolnym pasku Excela o nazwie NUM. Potrafi ona błyskawicznie zsumować, policzyć średnią, zliczyć ilość zaznaczonych - po prostu idealne rozwiązanie dla małych obliczeń. Poniższy rysunek przedstawia jak użyć tej funkcji.

Jakiś czas temu pisałem o tworzeniu makr i przycisków uruchamiających makra. Takie przypadki zakładały że nic złego nie może się stać i po prostu realizowały zadanie.

Może się zdarzyć - nawet często - że chcąc wykonać makro kasujemy jakieś dane zastępując je innymi. Taka sytuacja nie jest niczym szczególnym, ale jeśli użytkownik nie zdaje sobie sprawy że jego dane przepadną na skutek działania makra to może nam to przysporzyć problemów. Poinformowanie go o konsekwencjach uruchomienia i pozostawienie mu wyboru to pierwsze z zastosowań okienek msgbox

Drugim zastosowaniem msgbox jest poinformowanie użytkownika o tym że działanie makra zakończyło się powodzeniem (lub niepowodzeniem) i co trzeba robić dalej.

Okna msgbox mogą być wywoływane jako funkcja, która zwraca wartość do zmiennej (wówczas stosujemy nawiasy) lub po prostu jako funkcja z parametrami, która wyświetla okno i nic więcej się nie dzieje (wówczas nie stosujemy nawiasów dla parametrów).

Ogólna postać funkcji przedstawia się następująco:

Opiszę na początek prostszy przypadek (z drugiego zastosowania msgbox)

Makro informuje o tym co zrobiło i co teraz powinien zrobić korzystający z makra.

informacje w cudzysłowach mogą być zastąpione zmiennymi typu string które zmieniają wartość w zależności od przebiegu makra.

MsgBox “Sformatowałem obszar od A1 do F12. Kliknij Ok aby kontynuować”, vbInformation, “uwaga”

Taki tekst umieściłem w kodzie makra na samym końcu tuż przed słowami kluczowymi End Sub

Efektem wpisania kodu msgbox jest pojawienie się poniższego okienka po zakończeniu działania makra

Wyboru rodzaju okna dokonuje się przez wybranie odpowiedniego atrybutu w parametrze Buttons funkcji msgbox.

Teraz trudniejszy przypadek.

W poniższym przykładzie wykorzystujemy zmienną która od funkcji msgbox “przejmie” wynik “PRAWDA”(true) lub “FAŁSZ”(false) co odpowiada przyciskom OK i Cancel w poniższym przypadku.

Tworzymy zmienną potwierdzenie

Dim potwierdzenie

Teraz zmienna przejmuje Odpowiedź użytkownika z funkcji msgbox.

potwierdzenie = MsgBox(”Grozi skasowaniem danych. Czy kontynuować ?”, vbOKCancel, “Uwaga”)

Wyrażenie If potwierdzenie then jest równoważne If potwierdzenie=True then więc sobie skróciłem

Warunek if powinien obejmować w tej sytuacji całą oryginalną treść makra (czyli tą zarejestrowaną względnie zmodyfikowaną ręcznie bez funkcji msgbox)

If potwierdzenie Then 

….realizuje zawartość Makra…

    Range(”D4:D17″).Select
    Selection.Copy
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = “v”
    Range(”D4:D17″).Select
    Range(”D5″).Activate
End If

Jeżeli ktoś kliknie cancel makro nie wykona się a w każdym razie część makra objęta warunkiem if

Wstawianie okien msgbox daje spore pole do popisu. Główne zadania jakie widzę dla tej funkcji to lepsza informacja, nawigacja i ostrzeżenia. Statyczne napisy ostrzegawcze nie dają takiej pewności skupienia uwagi użytkownika jak msgbox. Jeśli jest rozważnie stosowane może zwiększać poczucie bezpieczeństwa i zaufanie do twórcy makra.

W przykładach wykorzystałem jedynie wąski wycinek możliwości tej funkcji

Więcej na temat funkcji msgbox można przeczytać na stronie http://dzono4.webpark.pl/basic/opisvb/msgbox.htm

Czasem mamy potrzebę aby zamienić wzajemnie położenie wierszy i kolumn. Co wówczas zrobić aby bezproblemowo przenieść dane.
Na początek radziłbym zrobić kopię pliku z tabelą i na niej dokonywać dalszych operacji.
Druga rzecz to zachowanie odpowiedniej kolejności, gdyż czasem może się zdarzyć że w wyniku transpozycji otrzymamy błędy związane z adresem komórki do której się odnosimy (problem jest rzadki ale istnieje).
Trzecia rzecz jeśli dane z przekształcanej tabeli są źródłem danych dla innych tabeli trzeba bezwzględnie operować na kopii tej tabeli - najlepiej znajdującej się w innym arkuszu.
Eksperymentowanie na źródle danych może być w tym przypadku przyczyną błędów w pozostałych częściach skoroszytu.

Dokonanie transpozycji
Aby dokonać transpozycji kopiujemy zaznaczamy i kopiujemy obszar tabeli a następnie chcąc wkleić transponowaną tabelę w innym miejscu stosujemy funkcję wklej specjalnie z zaznaczonymi opcjami wartości oraz transpozycja.

Nawiązując do uwagi powyżej czasem lepiej najpierw wkleić dane specjalnie (wartości) ale chwilowo bez opcji transpozycji.
Następnie zaznaczyć ponownie ten obszar, skopiować go i klikając na wolny obszar arkusza wkleić ponownie specjalnie tym razem z opcją transpozycji.

Wklejanie w tej samej komórce początkowej (lewy górny róg tabeli) nie jest dobrym pomysłem gdyż transponowana tabela ma odwrotną proporcję liczby wierszy do liczby kolumn i ryzykujemy zaśmiecanie  obszaru niewłaściwymi danymi.

Przykład

Zastosowanie transpozycji

tabele dla prezentacji parametrów urządzeń (np. silników, procesorów, itp).