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 2013: ExcelSzkolenie.pl Cwiczenia Excel 2013.zip

Ta lekcja może być obejrzana lub przeczytana poniżej.

Film wygląda najlepiej jeśli będzie odtwarzany w rozdzielczości 720p HD, rozdzielczość można zmienić dopiero po uruchomieniu filmu klikając na ikonie trybika która pojawi się w prawym dolnym rogu poniższego ekranu. Po kilku sekundach od zmiany obraz wyostrzy się.

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ć, czyli na arkuszu ‘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 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 teraz do arkusza ‘POLSKA’, gdzie w komórce C3 wprowadzimy formułę sumującą dane z 4 pozostałych arkuszy.

Stajemy w komórce C3 i wciskamy kombinację klawiszy lewy ‘Alt’ + ‘=’.

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) lub ‘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()

For Each arkusz In ActiveWorkbook.Sheets

arkusz.Visible = xlSheetVisible

End Sub

Więcej o makrach przeczytasz lekcjach poświęconych Visual Basic for Aplication (VBA).