nauka excela

no więc dostałem zadanie ;)

Witaj,
myślę, że będziesz w stanie mi pomóc. Przy eksporcie bazy danych otrzymuje plik w csv/xls i potrzebowałbym makra które kasowałoby wybrane kolumny. Zawsze jest to ten sam układ i dokładnie te same kolumny należy wykasować ( dla przykładu A, C, F, G ) Jeżeli udałoby Ci się mi pomóc będę wdzięczny, wystarczy mi przykładowe makro potem przerobie je sobie aby kasowało odpowiednie kolumny.
Drugi problem jaki się pojawia to przy eksporcie innej bazy danych zmieniają mi sie polskie znaki na różne "krzaczki". Mam listę znaków które należy zamienić na jaką literę, i w chwili obencje robie to na zasadzie Znajdź -- zamień wszystkie, co jest niezwykle irytujące. 

usuwanie kolumny jest banalnie proste:

Sub Makro1()
    Columns("D:D").Delete
End Sub

powyższy kod usuwa kolumnę D. Jeżeli chcesz usunąć kilka kolumn, zwróć uwagę na to, że po usunięciu kolumny D, zawartość kolumny E przesuwa się do kolumny D, co się wiąże z tym, że jeżeli chcesz usuwać kilka kolumn jedna po drugiej, musisz albo kasować kolumny od końca (najpierw G, potem C, potem B), albo w wypadku usuwania kolumn właśnie B,C i G kasować kolumnę B, potem B, a potem E.

dodam, że można usuwać jednocześnie kilka kolumn w ten sposób:

Sub Makro1()
    Columns("D:D, E:E, F:F").Delete
End Sub

w powyższym przykładzie kolumny usuwane są jednocześnie i nie trzeba się przejmować, czy coś się przesunie przed usunięciem kolejnej kolumny

I jeszcze inne rozwiązanie:

Sub czysc()

tablica = Array(14, 12, 10, 8, 5, 4) 'tutaj wymienione numery kolumn

'(od tyłu, gdyż usuwanie będzie przebiegało kolumna po kolumnie,

'czyli trzeba by uwzględnić przesuwanie się kolumn podczas usuwania)

For i = LBound(tablica) To UBound(tablica) 'ta linijka oznacza ni

'mniej ni więcej jak: od początku do końca tablicy
    Cells(1, tablica(i)).EntireColumn.Delete 'usuwanie całej kolumny
Next
End Sub

Co do zamiany kolumn miejscami - ja niestety nie znam takiej funkcji - jedyny sposób na przesunięcie kolumny jaki znam:

Sub Makro3()
    Columns("F:F").Cut
    Columns("D:D").Insert Shift:=xlToRight
End Sub


kolumna F zostanie przeniesiona w miejsce kolumny D, a wszystko po prawej od kolumny D z dotychczasową kolumną D włącznie przesunie się w prawo

Co do bazy danych - najprostszym sposobem jest import danych do pliku xls prosto z bazy danych, tak byłoby najszybciej.

Najpierw należy mieć skonfigurowane połączenie DSN i zainstalowany sterownik ODBC do bazy. To u Ciebie może być już skonfigurowane, skoro korzystasz z bazy danych, chyba że używasz php admina, to wówczas nie jest to konieczne. Informacji o ODBC można szukać (zakładam że używasz systemu Windows) w panelu sterowania -> narzędzia administracyjne -> źródła danych (ODBC). Tutaj trzeba zainstalować sterownik ODBC do odpowiedniej bazy danych, a później skonfigurować samo połączenie, czyli adres bazy login i hasło i takie tam.. i dalej:

sub Marko1()

sq = "select * from nazwa_tabeli"

' powyżej zapytanie sql, tutaj od razu w zapytaniu możesz wybrać

'interesujące Cię kolumny, z pominięciem tych niepotrzebnych

Const connstr = "ODBC;DSN=nazwa_DSN;UID=login;PWD=haslo"

'wyżej należy wpisać nazwę skonfigurowanego DSN'a, login i hasło bodaj

'nie są wymagane, jeśli są zapisane w DSN'ie
With ActiveSheet.QueryTables.Add(Connection:=connstr, Destination:=Range("A1"), Sql:=sq)
        .Refresh BackgroundQuery:=False

        .delete
End With

End Sub

jeżeli jednak importujesz dane z pliku csv i jest problem z 'krzakami', czyli tak naprawde kodowaniem, sugeruje zrobić to tak (opis dla office'a 2003):

narzędzia -> importuj dane zewnętrzne -> importuj dane...

następnie wskazujesz plik csv i na pierwszym ekranie masz wybór kodowania... tutaj musisz wskazać jakie kodowanie użyte jest przy pliku csv. Informację o kodowaniu powinieneś widzieć przy eksporcie danych z bd, najprawdopodobniej będzie to UTF-8 (przynajmniej ja mam szczęście korzystać z baz, które zrzucają dane domyślnie w takim formacie).

Po kliknięciu dalej zaznaczasz czym są w pliku csv poodzielane kolumny, jeszcze 2 razy 'dalej' i powinno być ok.

Jeżeli nadal nie wiesz jakie kodowanie użyte jest dla pliku csv to możesz to sprawdzić wykorzystując dowolny bardziej zaawansowany edytor tekstu od windowsowego notatnika. poszukaj w google programów typu notepad+, notepad2, programmers notepad albo czegoś podobnego. Po otwarciu pliku w takim notatniku powinieneś w opcjach odnaleźć informacje o kodowaniu.

Inny sposób podmiany znaków - gdy inne sposoby zawodzą:

Sub Makro()

Cells.Replace What:="+", Replacement:="ś", LookAt:=xlPart, MatchCase:=True

End Sub

Jeżeli zamieniane są litery, to końcowy parametr - MatchCase decyduje czy przy zamianie makro będzie zwracało uwagę na rozmiar liter, czy też nie. w przypadku zamiany z 'plusa' nie ma to znaczenia, ale gdyby zamieniać litery to w zależności czy warość MatchCase będzie wynosić true czy false, zależy np czy zmieniana będzie przykładowo tylko literka 'a' czy również 'A'.

Jeżeli chcemy zamienić większą liczbę znaków sugeruję posłużyć się pętlą jak poniżej:

Sub Makro()
zamienZ = Array("/", "@", "˛", "+", "ˇ")
zamienNa = Array("ą", "ć", "ź", "ł", "ń")
For i = LBound(zamienZ) To UBound(zamienZ)
    Cells.Replace What:=zamienZ(i), Replacement:=zamienna(i), LookAt:=xlPart, MatchCase:=True
Next
End Sub

Witajcie,

wiem że dawno nic nie pisałem, widziałem jednak, że blog jest nawet często odwiedzany. Jeżeli ktoś ma kłopot z excelem to chętnie pomogę, wystarczy że skomentujecie ten wpis, postaram się w miarę możliwości pomóc dodając kolejny wpis z rozwiązaniem problemu. W excelu pracuję już prawie dwa lata i doświadczenie w tym czasie nabyłem spore.

Ukrywanie arkuszy jest przydatne, kiedy mamy w excelu dużo arkuszy i ciężko się w nich połapać, bądź też chcemy ukryć pewne dane przed użytkownikiem arkusza.

Ukryć aktywny arkusz można poprzez wybranie z menu Format opcji Arkusz a potem Ukryj. Innym sposobem są skróty klawiszowe, które są kluczowe przy częstej pracy z Excelem. Aby ukryć aktywny arkusz należy naciskać klawisze (jeden po drugim) lewy alt, F, R, U.

Innym sposobem ukrycia arkusza jest visual basic. Poniżej procedura ukazująca możliwości vba, w komentarzach jest częściowo omówiona, poniżej opiszę ją bardziej dokładnie.

Na początek zwrócę uwagę, iż każdy arkusz ma dwie przypisane do siebie nazwy, jedna z nich, to ta widoczna w samym excelu (podana w nawiasach w oknie project explorera [lewy górny róg powyższego screena], druga nazwa, to nazwa obiektu, ktorej można użyc do odwoływania się do tego samego arkusza w vba (można oczywiście odwoływać się za pomocą obu nazw).

Powracając do kodu:

linia1 - powoduje ukrycia arkusza o nazwie Arkusz1, tutaj użyte jest odwołanie do arkusza poprzez jego nazę widoczną w Excelu i właściwość visible ustawianą na false.

linia2 - działa tak samo jak pierwsza linia, z tą różnicą, że odwołanie następuje poprzez nazwę obiektu worksheet w vba, stała xlSheetHidden ma taką samą wartość jak false - 0, dlaczego więc stworzono xlSheetHidden i podobne? Czasami łatwiej zapamiętać jakąś nazwę, aniżeli numerek, tym bardziej, że sama nazwa sugeruje do czego może służyć, a edytor vba potrafi podpowiedzieć dozwolone wartości. Niezależnie więc czy zostanie użyta wartość false, xlsheethidden czy 0 efekt będzie ten sam.

linia3 - służy do ukrycia aktywnego arkusza.

linia4 - to ciekawy sposób ukrycia arkusza - ukrywa go w taki sposób, że nie będzie możliwe odkrycie tego arkusza bez wykorzystania vba. w menu format -> arkusz -> odkryj arkusz nie będzie w ogóle widoczny. przy okazji wykorzystane jest odwólanie do arkusza poprzez jego nazwę z vba (można ją zmienić w oknie properties (lewa dolna część screena).

linia5 - odkrywa arkusz2

linia6 - okrywa arkusz1

w efekcie po wywołaniu całej procedury zostaną odkryte arkusze1 i 2. Jeżeli więc wywoła się tą procedurę w nowym arkuszu efektu nie będzie żadnego. Kod napisany był tylko aby przybliżyć możliwości ukrywania arkuszy.

Dzisiaj opiszę wykorzystaną przeze mnie w poprzedniej notatce możliwość mieszania typów wykresów i umieszczania na jednym wykresie dwóch serii danych o różnych wartościach na osi y.

Dla przykładu stworzyłem niewielką tabelę z danymi, załóżmy że mam stację benzynową i chciałem sprawdzić zależność pomiędzy ceną paliwa na stacji, a ilością sprzedawanej benzyny.

pierwszy wiersz to numery tygodni (docelowo mają być na osi x). Dwa kolejne wiersze chyba nie wymagają opisu.

Teraz należy stworzyć wykres, najprościej zrobić to zaznaczając dokładnie cały obszar, który ująłem w rampce powyżej i naciskając klawisz F11, można też przez Wstaw -> wykres (rys. poniżej), albo poprzez przycisk na pasku standardowym ze słupkami (). Przy wyborze dwóch ostatnich opcji będzie należało jeszcze 3 razy nacisnąć dalej, a potem zakończ.

Ostatecznie powstaje wykres jak poniżej:

Wykres w zależności od wyboru opcji jego wstawienia pojawi się jako nowy arkusz, albo obiekt w istniejącym arkuszu, nie jest to jednak tematem moich dzisiejszych rozważań. Jak widać na wykresie, widoczna jest tylko jedna seria danych, druga ma tak nieznaczne wartości w porównaniu do pierwszej, że w ogóle jej nie widać. Postaram się to zmienić. Klikam prawym klawiszem w odpowiednią serię (niebieski słupek) i wybieram 'Formatuj serie danych...', a następnie w zakładce 'Oś' zmieniam zaznaczenie na 'Oś pomocnicza', w ten sposób seria 'sprzedanych litrów' wartości y będzie miała po prawej stronie wykresu, na nowo powstałej drugiej osi (rysunki poniżej).

fd

Niestety jak widać niebieskie słupki zasłaniają słupki fioletowe. W takim wypadku postanowiłem zmienić typ serii 'średnia cena za litr' na inny - liniowy.

W tym celu klikam prawym klawiszem w wystającą szęść fioletowego słupka, wybieram 'Typ wykresu...' i zmieniam typ na liniowy jak na rysunkach poniżej.

Ostatecznie wykres wygląda następująco:

Jak widać na mojej stacji nie ma specjalnie zależności między ceną a ilością sprzedawanego paliwa.

Na koniec życzę wszystkim, żeby ceny paliw spadały jak na moim wykresie :)

Tak się dziś zastanawiałem nad pewnym problemem - przygotowuję raport, który ma być uzupełniany na bierząco (docelowo ma przedstawiać pewne dane za pół roku). Raport ten zawiera pewien wykres, w którym dane na koniec zadanego okresu będą wypełniały całą szerokość wykresu, natomiast po dwóch tygodniach wygląda to mało interesująco. Oczywiście na bierząco mogę zmieniać zakres wykresu, co nie zabiera w sumie wiele czasu, jednak wolałbym, żeby nie zostawała mi robota na potem. Lepiej byłoby, gdyby zakres danych do wykresu zmieniał się wraz z przyrostem danych w arkuszu.

Tak jak to sobie wymyśliłem, tak też zrobiłem, nie wiem czy jest to najbardziej optymalne rozwiązanie, ale jedyne jakie przyszło mi do głowy. Wykorzystane zostały przeze mnie definiowane nazwy zakresów, funkcje ILE.NIEPUSTYCH i PRZESUNIĘCIE.

Dane użyte przeze mnie nie mają większego sensu, służą tylko do zilustrowania przykładu. Tabela danych zawiera 3 kolumny, dni, kwota i pracownicy, załóżmy że są to kwoty znikające z kasy w sklepie i ilość pracowników sklepu pracujących (wiem, słabe to :P) w danym dniu. Dane prezentują się tak:

Liczba 6 po prawej stronie nie jest przypadkowa, jest tam wykorzystana funkcja ile.niepustych, sprawdzająca ile pól w danym zakresie ma wartości różne od "".

Do tego zdefiniowałem trzy nazwy zdefiniowane w poniższy sposób:

w funkcji przesunięcie zakres jest zależny od wartości w komórce I7 - z jej zmianą zmienia się zakres zwracany przez tą funkcję. Troche w przykładzie się pomyliłem, ale nie mam siły poprawiać błędu - będzie widoczny na wykresach poniżej, tak czy owak - idea myślę że zostanie przekazana ;)

Nazwy kwota i pracownicy różnią się od dni tylko odwoładniem (zamiast D6 są tam odpowiednio komórki E6 dla kwoty i F6 dla pracownicy, a tak naprawdę wszędzie powinno być 7 :P

serie danych dla wykresu wyglądają następująco:

No i to by było wszystko, zależnie teraz od ilości wprowadzonych informacji zakres wykresu zmienia się dynamicznie, poniżej dwa przykłady:

Jak widać w wypadku obu wykresów skopana jest pierwsza wartość, o sposobie naprawy błędu pisałem wcześniej. Niemniej problem został rozwiązany i wykres zmienia się dynamicznie wraz z ilością wprowadzonych danych.

Poniżej zaprezentuję kod VBA, który umożliwia śledzenie, kto zmieniał plik. Może zdarzyć się sytuacja, że jakiś plik znajdujący się np. na serwerze dostępny jest dla wielu osób i chcielibyśmy wiedzieć, kto dokonuje zmian w tym pliku. Można to łatwo zrobić z wykorzystaniem poniższego kodu, tutaj mała uwaga - wyremowana linijka (fs.CreateTextFile "d:\xxx.txt") służy do utworzenia pliku gdzie będą zapisywane informacje o osobach zapisujących zmiany. Jeżeli zostawiłbym tą linijke w kodzie, to każdorazowy zapis usuwałby poprzedni plik (zostawałyby wówczas informacje tylko o ostatnim zapisie), dlatego też konieczne jest wyrzucenie tej linijki, kiedy plik jest już utworzony.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Const ForAppending = 8, TristateUseDefault = -2
Dim fs, f, ts
Set fs = CreateObject("Scripting.FileSystemObject")
'fs.CreateTextFile "d:\xxx.txt"
Set f = fs.GetFile("d:\xxx.txt")
Set ts = f.OpenAsTextStream(ForAppending, TristateUseDefault)
ts.writeline ("zapis" & Chr(9) & ThisWorkbook.Path & "\" & ThisWorkbook.Name & Chr(9) & Date & Chr(9) & Time & Chr(9) & Application.UserName)
ts.Close
End Sub

Powyższy kod można również użyć np do logowania informacji o tym kto plik otwiera, by tego dokonać, należałoby pierwszą linijkę podmienić na:

Private Sub Workbook_Open()

Formatowanie warunkowe to ciekawa sprawa, jeżeli chcemy wyróżnić komórki zadając pewne kryteria. Problem z excelem 2003 jest taki, że zmiany formatowania mogą być maksymalnie 3 (w excelu 2007 z tego co pamiętam może być więcej warunków). Co w takim razie w wypadku, kiedy chcemy więcej formatów formatowania warunkwego? Pozostaje zabawa z VBA :)

Krótki kod wstawiony do kodu konkretnego arkusza powiniem problem rozwiązać:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "d" And Target.Row < 20 And Target.Column < 20 Then
Target.Interior.ColorIndex = 5
Else
Target.Interior.ColorIndex = 2
End If
End Sub

W przypadku użycia tego kodu, jeżeli w komórkę w zakresie A1:S19(target.row <20 i target.column < 20) zostanie wpisana tylko literka d (target.value = "d"), tło komórki zmieni się na kolor niebieski (colorindex = 5), jeżeli natomiast komórka nie zawiera wartości d, to kolor zmienia się na biały (colorindex = 2).

Pozostaje niestety pewna niewygoda - z góry założony zakres formatowanych komórek, który by zmienić, trzeba edytować kod, poza tym tego typu formatowanie warunkowe ma mniejszy priorytet od prawdziwego formatowania warunkowego w excelu - jeżeli zostaną spełnione dwa kryteria (jedno z formatowaina warunkowego, a drugie z z kodu VBA), wówczas widocznym efektem będzie formatowanie z formatowania warunkowego.

Niemniej wydaje mi się, że takie rozwiązanie może być przydatne.

Żeby poznać się na makrach, trzeba trochę poczytać, jak również sporo poćwiczyć. Ja jestem w trakcie ;) Makra generalnie potrafią uprościć wykonywanie pewnych czynności (z góry przewidzianych), zwykle powtarzających się.
Może nie za ładnie z mojej strony, że liznę kilka różnych tematów jednocześnie, ale właśnie wpadło mi do głowy jaką sytuację chciałbym opisać, a liczę przy okazji, że osoby które mogą czytać tego bloga posiadają już pewne informacje na temat samego excela jak również przynajmniej podstaw VBA.

Załóżmy, że bardzo często robimy tabelę, której nagłówki trzeba scalać. najkrótszy sposób to zaznaczenie obszaru, ctrl+1 (wyświetlenie okna 'formatowanie komórek'), przejście do drugiej zakładki, gdzie należy zaznaczyć 'scalaj'. Gdyby do tego dołożyć, by w scalonym polu czcionka była pogrubiona, do tego wyrównanie tekstu do środka (w poziomie  pionie), będzie to proste, jednak tylko w wypadku, kiedy należy to wykonać klika, czy też kilkanaście razy.
Jeżeli natomiast chciałbym tę czynność powtórzyć więcej razy, wówczas problem wydaje się odrobinę uciążliwy i miło byłoby sobie rozwiązanie uprościć.
Zakładam, że z uproszczonego scalania chciałbym korzystać w wielu skoroszytach.

W tym celu zamierzam stworzyć makro, a następnie dodać je jako dodatek excela, aby móc używać go z każdym skoroszytem. W tym celu otwieram nowy, pusty skoroszyt i przechodzę do edytora visual basic (alt+F11). Wstawiam nowy moduł (insert -> module).
Teraz pora na kod.. będzie to mniej więcej tak:

Sub scal()
With Selection
.Font.Bold = True
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
End Sub

czyli po kolei pogrubienie czcionki, scalenie komórek, i wyśrodkowanie tekstu. Oczywiście wszystkie operacje dokonywane są na zaznaczonych komórkach w arkuszu.
Przechodzę teraz do okna excela i otwieram okno makr (alt + F8). zaznaczam moe makro (scal) i klikam po prawej w 'opcje...'. Następnie wpisuje klawisz skrótu dla mojego makra (wiele skrótów z małą literą jest już zajętych, więc lepiej wykorzystywać duże litery - ja wybieram Y).

Teraz w moim skoroszycie, za pomocą skrótu ctrl+shift+Y mogę dowolnie scalać komórki, teraz tylko chciałbym, aby działało to z każdym skoroszytem. W tym celu zapisuję dokument w formacie .xla plik - > zapisz jako -> dodatek programu miscrosoft office excel (.xla).
Następnie w oknie excela wybieram narzędzia - > dodatki... -> przeglądaj...  i wskazuję lokalizację mojego niedawno zapisanego pliku. Teraz tylko zaznaczyć checkbox przy moim nowym dodatku i już mogę się cieszyć na stałe nowym skrótem klawiszowym scalającym komórki.

Blog założyłem, jako że ciężko w internecie znaleźć ciekawe informacje na temat excela w języku polskim. Od kilku dni zacząłem pracę w firmie, gdzie znajomość excela jest wymagana w stopniu bardzo dobrym, co wiąże się z przymusową nauką obsługi tego programu. Wersja Excela, którą przychodzi mi przyswoić nie jest najnowsza (wersja 2003), jednak mimo wszystko spełnia wymagania jej stawiane.

Postaram się na bierząco pisać informacje na temat excela, opierając je jak najczęściej na przykładach. Postaram się, aby równiez nie zabrakło informacji o makrach w VBA, ale to one są dla mnie w chwili obecnej największą zagadką.

Na początek może prosta, ale przydatna formuła, służąca do wyświetlania danych w przyjaznej dla użytkownika formie.

Często zdarza się, że należy wykonać pewne działania w arkuszu, które nie zawsze będą w stanie zwrócić prawidłowe wartości (np. w przypadku dzielenia przez 0, czy też poprzez wykorzystanie innych formuł, np. WYSZUKAJ.PIONOWO, które nie zawsze odnajdują szukaną wartość), z pomocą przychodzi wówczas prosta, ale bardzo funkcjonalna formuła JEŻELI, oraz CZY.BŁĄD:

=JEŻELI(CZY.BŁĄD(A1/B1);"x";A1/B1)

w ten sposób, jeżeli dzielenie A1/B1 zwróci błąd (np. gdy w komórce B1 znajdzie się wartość 0), wówczas zamiast wpisywać wynik dzielenia w komórce pojawi się wartość x, która będzie oznaczać brak danych (można oczywiście x zastąpić dowolnym znakiem (ciągiem znaków), np. 0, "-", lub można wymusić pozostawienie pola bez żadnej wartości - wówczas zamiast "x" należałoby wpisać "" - pusty string. Jeżeli natomiast formuła CZY.BŁĄD nie zwróci błędu, wówczas w komórce pojawi się normalny wynik dzielenia komórki A1 przez B1.

Jest to jeden z prostszych sposobów radzenia sobie ze wspomnianym problemem.