Operacje na Wielu Arkuszach
Przykłady opisane w
tej lekcji dostępne są w arkuszu Excela: Operacje na Wielu
Arkuszach.xlsx tylko ich samodzielne przerobienie daje gwarancję
zapamiętania tej lekcji
Przykłady dla wszystkich lekcji szkolenia
Excel 2007: ExcelSzkolenie.pl
Cwiczenia Excel 2007.zip
1. Operacje na wielu
arkuszach na raz.
2. Przenoszenie i
kopiowanie arkuszy pomiędzy plikami.
3. Ukrywanie arkuszy.
Przykład
1. Operacje na wielu arkuszach na raz.
Często pracując na
plikach, w których pewne dane powinny zostać przygotowane np. dla dużej ilości
oddziałów/kategorii w tym samym formacie dobrym rozwiązaniem jest założenie dla
każdego z nich osobnego arkusza i utrzymywanie takiej samej struktury i formatu
dla wszystkich tych arkuszy.
Jeżeli pracujemy na
pliku, w którym wiele arkuszy ma taką samą strukturę możemy wykonywać różne
czynności na raz na wszystkich bądź wybranych arkuszach tego pliku.
W tym przykładzie
sprzedaż dla 4 oddziałów jakie firma posiada w Polsce zapisywana jest na 4
arkuszach o nazwach: ‘Warszawa’, ‘Wrocław’, ‘Katowice’, ‘Poznań’.
Autor tego pliku
zauważył, że nagłówek Sprzedaż znajduje się tylko w arkuszu ‘Warszawa’.
Zamiast kopiować go
do wszystkich arkuszy po kolei, łatwiej będzie zaznaczyć arkusze klikając na
nazwie arkusza ‘Warszawa’, wciskając przycisk ‘Shift’ i klikając na ostatnim z
arkuszy który chcemy zaznaczyć ‘POLSKA’.
Nazwy wszystkich
arkuszy zostały podświetlone, co oznacza że wszystkie arkusze są wybrane i
zmiany wprowadzone w jednym z nich zostaną wprowadzone do wszystkich.
Ponieważ nagłówek ‘Sprzedaż’
jest już wprowadzony w arkuszu ‘Warszawa’ nie musimy go wpisywać ani kopiować,
wystarczy wejść do tej komórki (podwójnym kliknięciem) i zatwierdzić jej
zawartość Enterem. Excel traktuje takie wejście i zatwierdzenie tak samo jak
wprowadzenie danych do komórki, tekst ‘Sprzedaż’ pojawi się we wszystkich
zaznaczonych arkuszach.
Pozostańmy na arkuszu Warszawa i sformatujmy naszą tabelę
poprzez dodanie krawędzi i pokolorowanie wiersza z nagłówkiem na kolor zielony.
W komórce B5
proponuje wprowadzić tekst ‘TOTAL, a w komórce C5, sumę komórek C3 i C4.
Podczas wszystkich
wyżej wymienionych operacji wszystkie arkusze były zaznaczone.
Jeżeli wszystkie
arkusze pliku są zaznaczone, odznaczamy je poprzez kliknięcie na dowolny z nich
(za wyjątkiem pierwszego który zaznaczaliśmy, w tym przykładzie ‘Warszawa’).
Możemy teraz sprawdzić czy faktycznie wszystkie wprowadzone przez nas zmiany są
widoczne w każdym z arkuszy.
Przejdźmy terez do
arkusza ‘POLSKA’, gdzie w komórce C3 wprowadzimy formułę sumującą dane z 4
pozostałych arkuszy.
Stajemy w komórce C3
i klikamy symbol sumy.
Klikamy na nazwę
arkusza ‘Warszawa’ po czym klikamy na komórkę C3 na tym arkuszu.
W wierszu formuły
zostanie wyświetlone =SUMA(Warszawa!C3).
W tym momencie nasza
formuła sumuje jedynie daną z tego arkusza.
Wciskamy ‘Shift’ i
trzymając go wciśniętego klikamy na nazwę arkusza ‘Poznań’, ostatniego z
arkuszy które chcemy zsumować.
Jak widać na
poniższym rysunku formuła przyjęła postać: =SUMA(‘Warszawa:Poznań’!C3)
Taką formułę
zatwierdzamy enterem co spowoduje przejście do arkusza ‘POLSKA’ i odznaczenie
pozostałych arkuszy.
W arkuszu ‘POLSKA’
przechodzimy do komórki C3.
Formuła:
=SUMA(‘Warszawa:Poznań’!C3), sumuje dane z komórek C3 ze wszystkich arkuszy od
arkusza ‘Warszawa’ do arkusza ‘Poznań’.
Formułę tą skopiujemy
teraz do komórki C4.
Załóżmy teraz że
firma otworzyła nowy oddział w Gdańsku, który przejął część terenu wcześniej
obsługiwanego przez biura w Warszawie i Poznaniu.
Zamiast Tworzyć nowy
arkusz od początku wygodniej będzie skopiować jeden z już istniejących.
Klikamy na (np.)
Arkusz ‘Wrocław’ i przytrzymujemy wciśnięty lewy klawisz myszy, przy strzałce
pojawia się mały symbol kartki, przesuwając myszką w lewo lub prawo możemy
przenieść arkusz w inne miejsce, aby go skopiować wciskamy ‘Ctrl’ co powoduje
pokazanie się symbolu + na małej kartce, puszczamy klawisz myszki trzymając
kursor na prawo od arkusza ‘Wrocław’.
Do pliku zostaje
dodany nowy arkusz: ‘Wrocław (2)’
Zmieniamy jego nazwę
na ‘Gdańsk’ i wprowadzamy właściwe dane dla komórek ze ‘Sprzedażą’.
Suma sprzedaży w
arkuszu ‘POLSKA’ uwzględnia nowo dodany oddział ponieważ znajduje się on
pomiędzy arkuszami ‘Warszawa’ i ‘Poznań’.
Gdybyśmy chcieli
sprawdzić ile wynosi sprzedaż dla Polski z wyłączeniem Katowic, wystarczy
przenieść ten arkusz poza przedział Warszawa:Poznań.
Aby przenieść arkusz
należy kliknąć jego nazwę lewym klawiszem myszy i trzymając go wciśniętego,
przesunąć kursor w wybrane miejsce, podczas przesuwania myszy pomiędzy
arkuszami będzie pojawiać się mała czarna strzałka, zaznaczona na poniższym
rysunku czerwonym prostokątem, dzięki, której wiemy gdzie znajdzie się
przenoszony arkusz jeśli w tym momencie przestaniemy przytrzymywać lewy klawisz
myszy.
Suma sprzedaży w
arkuszu POLSKA zmieniła się i nie obejmuje już Katowic.
Przenieśmy teraz
arkusz ‘Katowice’ z powrotem.
Ponieważ dodaliśmy
nową filię Gdańsk, która przejęła obsługę części klientów do tej pory
przypisanych do Warszawy i Poznania, chcielibyśmy na arkuszach ‘Warszawa’ i
‘Poznań’ dodać informację że obszar przejęty przez odział w Gdańsku już nie
jest wliczany do ich sprzedaży.
Klikamy na arkusz
‘Poznań’, po czym wciskamy ‘Ctrl’ i trzymając ten przycisk wciśnięty klikamy
lewym klawiszem myszy na arkuszu ‘Warszawa’.
W komórce B7
wprowadzamy tekst: „Nie uwzględnia klientów przejętych przez oddział w
Gdańsku’.
W ten sposób
nauczyliśmy się wprowadzać zmiany na raz w wielu wybranych arkuszach.
Aby odznaczyć wybór
kilku arkuszy na raz, wystarczy kliknąć na dowolnym nie zaznaczonym arkuszu.
W analogiczny sposób możemy
kopiować bądź przenosić kilka wybranych arkuszy na raz.
Wskazówki:
Nazwy arkuszy powinny
być jak najkrótsze, zapewnia to łatwość poruszania się po pliku bez
konieczności przewijania by znaleźć odpowiedni arkusz.
Poruszanie się po pliku
może też ułatwić ustawienie innych kolorów dla poszczególnych nazw arkuszy,
bądź ich grup.
UWAGA: Częstym błędem
jest zapomnienie o odznaczeniu większej ilości arkuszy i wprowadzanie danych do
jednego z nich myśląc że tylko ten jest zmieniany, należy pamiętać aby po
wprowadzeniu zmian do wielu arkuszy zawsze je odznaczyć, w przeciwnym razie
możemy utracić dane w pozostałych zaznaczonych arkuszach.
Przykład 2.
Przenoszenie i kopiowanie arkuszy pomiędzy plikami
Kiedy pracujemy na
wielu plikach na raz warto zastanowić się czy nie byłoby wygodniej je połączyć.
W dość prosty sposób
można przenosić lub kopiować arkusze pomiędzy plikami.
Mając cały czas
otwarty plik ‘Operacje na Wielu Arkuszach’, utwórzmy nowy plik ‘Zeszyt1’.
Z karty ‘Widok’
wybieramy polecenie ‘Rozmieść wszystko’
Spowoduje to
wyświetlenie okna ‘Rozmieszczanie okien’, w którym możemy wybrać jak mają być
ustawione pliki. Do przenoszenia bądź kopiowania arkuszy najwygodniejsze będzie
rozmieszczenie ‘Poziomo’.
Pliki zostaną
pokazane jeden pod drugim.
Przenieśmy teraz arkusz
‘Gdańsk’ do pliku ‘Zeszyt1’.
Klikamy Gdańsk lewym
klawiszem myszy i trzymając go wciśniętego przeciągamy ‘Gdańsk’ do drugiego
pliku.
Miejsce gdzie pojawi
się arkusz oznaczone jest małą czarną strzałką.
Nie można przenieść
arkusza z pliku, w którym po przeniesieniu nie pozostałby żaden arkusz.
Jeśli podczas
przeciągania arkusza przytrzymamy przycisk ‘Ctrl’ arkusz zostanie skopiowany a
nie przeniesiony. Podczas przeciągania z wciśniętym klawiszem ‘Ctrl’ na symbolu
kartki widoczny jest +.
Możemy kopiować lub
przenosić wiele arkuszy na raz, wystarczy zaznaczyć wiele arkuszy przed tą
operacją, używając przycisków ‘Shift’ (zaznaczenie od do) i ‘Ctrl’ (zaznaczenie
klikniętych elementów).
Sugeruje poćwiczyć
kopiowanie i przenoszenie arkuszy, pojedynczo i po kilka na raz, dla nabrania
wprawy.
3. Ukrywanie arkuszy
Ukrawanie arkuszy
jest przydatne np., gdy tworzymy raporty składające się z wielu arkuszy z
danymi i jednym lub kilkoma arkuszami wynikowymi dla użytkowników.
Aby ukryć arkusz
należy jego nazwę kliknąć prawym przyciskiem myszy i wybrać polecenie Ukryj.
Możemy ukryć więcej
arkuszy na raz, wystarczy zaznaczyć kilka arkuszy i wybrać j.w.
Aby odkryć arkusz
należy kliknąć prawym przyciskiem dowolny arkusz i wybrać ‘Odkryj…’
A po pojawieniu się poniższego
okna, wybrać z niego arkusz, który chcemy odkryć.
W Excelu nie ma
możliwości odkrycia więcej niż jednego arkusza na raz, operacje tą musimy
powtarzać tak długo aż wszystkie arkusze które mają być widoczne zostaną
odkryte.
Osoby, które znają
podstawy VBA mogą wykorzystać do odkrywania wszystkich ukrytych arkuszy na raz,
poniższe proste makro:
Sub odkryj_arkusze()
Dim gg
For Each gg In ActiveWorkbook.Sheets
gg.Visible = xlSheetVisible
Next
End Sub
Więcej o makrach i
ich używaniu przeczytasz lekcjach poświęconych: Visual Basic for Aplication
(VBA).