Opublikowano

Blokowanie kolumn i wierszy w arkuszu Excela

Blokowanie górnego wiersza

  1. Kliknij na dowolną komórkę w arkuszu.
  2. Upewnij się, że jako pierwszy na ekranie widzisz wiersz, który ma zostać zablokowany.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38ac0f6a.PNG

  1. Na wstążce programu przejdź do karty Widok. Kliknij ikonkę Zablokuj okienka, a następnie z wyświetlonej listy wybierz Zablokuj górny wiersz.

  1. Między zablokowanym wierszem, a kolejnym pojawi się cienka czarna linia.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38adfdbd.PNG

  1. Przesuń zawartość arkusza w dół. Górny wiersz powinien być zawsze widoczny.

Blokowanie pierwszej kolumny

  1. Kliknij na dowolną komórkę w arkuszu.
  2. Upewnij się, że jako pierwszą na ekranie widzisz kolumnę, która ma zostać zablokowana.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38ba5efd.PNG

  1. Na wstążce programu przejdź do karty Widok. Kliknij ikonkę Zablokuj okienka, a następnie z wyświetlonej listy wybierz Zablokuj pierwszą kolumnę.

  1. Między zablokowaną kolumną, a kolejną pojawi się cienka czarna linia.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38be74e3.PNG

  1. Przesuń arkusz w prawo, pierwsza kolumna będzie zawsze widoczna.

Jednoczesne blokowanie wiersza i kolumny

Zasada blokowania

Podczas jednoczesnego blokowania wierszy i kolumn, zablokowaniu ulegają wiersze powyżej aktywnej komórki oraz kolumny z lewej strony aktywnej komórki.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38c7ffc2.PNG

Przykłady

  • Aby zablokować jednocześnie pierwszy wiersz i pierwszą kolumnę należy ustawić się na komórce B2.
  • Aby zablokować jednocześnie dwie pierwsze kolumny (A i B) oraz wiersze od 1 do 3 należy ustawić się na komórce C4.
  • Aby zablokować jedynie cztery pierwsze kolumny (A, B, C, D) należy ustawić się na komórce E1.
  • Aby zablokować jedynie dziesięć pierwszych wierszy należy ustawić się na komórce A11.

Jednoczesne blokowanie pierwszego wiersza i pierwszej kolumny

  1. Ustaw się na komórce B2.

C:\Users\Marcin\AppData\Local\Temp\SNAGHTML38cd4f32.PNG

  1. Na wstążce programu przejdź do karty Widok. Kliknij ikonkę Zablokuj okienka, a następnie z wyświetlonej listy wybierz Zablokuj okienka.

  1. W arkuszu pojawią się cienkie czarne linie oddzielające zablokowaną kolumnę i wiersz od niezablokowanego obszaru.

  1. Przesuń treść arkusza w dół, a następnie w prawą stronę. Pierwszy wiersz i pierwsza kolumna będą zawsze widoczne na ekranie.

Odblokowanie

  1. Kliknij na dowolną komórkę, w arkuszu z zablokowanym widokiem.
  2. Na wstążce programu przejdź do karty Widok. Kliknij ikonkę Zablokuj okienka, a następnie z wyświetlonej listy wybierz Odblokuj okienka.

Uwaga ! Opcja Odblokuj okienka jest aktywna tylko w arkuszu z zablokowanym widokiem.

 

Opublikowano

Sumowanie na podstawie koloru komórki – proste rozwiązanie

Istnieje kilka rozwiązań w Excelu umożliwiających wykonywanie obliczeń na podstawie koloru wypełnienia komórki jedne są bardziej skomplikowane i wymagają umiejętności programowania inne natomiast prostsze, ale obarczone koniecznością wykonywania wielu kroków.

Sumowanie ze względu na kolor wypełnienia komórek

Poniżej opisana metoda pozwoli w prosty sposób na sumowanie po kolorze wypełnienia komórki. Zaletą tego rozwiązania jest to, że komórki z sumowanymi wartościami mogą być losowo rozmieszczone w arkuszu. W analogiczny sposób będzie można zliczyć komórki o tym samym kolorze oraz wykonywać inne obliczenia, których kryterium jest format komórki.

Krok 1 – wybierz komórki do podsumowania

Na wstążce programu przejdź do karty Narzędzia główne, kliknij ikonkę Znajdź i zaznacz, a następnie z listy wybierz opcję Znajdź. Na ekranie zostanie wyświetlone okienko Znajdowanie i zamienianie z aktywną kartą Znajdź. Okienko to można wywołać także za pomocą skrótu klawiaturowego CTRL+F. Kliknij znajdujący się w dolnym prawym roku okienka przycisk Opcje. Z pawiej strony przycisku Format rozwiń listę i kliknij opcję Wybierz format z komórki.
Okienko zostanie zminimalizowane, a przy kursorze pojawi się symbol pipety. Najedź kursorem i kliknij na jedną z komórek, która ma być sumowana. W efekcie w okienku Znajdowania i zamieniania w polu podglądu pojawi się wybrany format komórki.
Kliknij teraz przycisk Znajdź wszystko. Na dole okienka zostanie wyświetlona lista wszystkich komórek sformatowanych w ten sam sposób. Zaznaczając wszystkie pozycje na tej liście zaznaczysz jednocześnie komórki w arkuszu, najłatwiej zrobisz to wciskając na klawiaturze skrót CTRL+A.
Zamknij okienko Znajdowanie i zamienianie. od tej pory nie klikaj na komórki w arkuszu, aby nie stracić zaznaczenia.

Zauważ, że suma liczb z zaznaczonych komórek o tym samym kolorze wypełnienia pojawiła się już na dole ekranu na pasku stanu.

Krok 2 – zdefiniuj nazwę

Dla zaznaczonego zakresu komórek z tym samym kolorem wypełnienia należy zdefiniować nazwę, którą później będzie można używać w formułach. Na wstążce programu przejdź do karty Formuły i kliknij ikonkę Definiuj nazwę.

Na ekranie zostanie wyświetlone okienko Nowa nazwa. W polu Nazwa wpisz nazwę dla zaznaczonego zakresu komórek np. czerwone. W dolej części okienka w polu Odwołuje się do wymienione są wszystkie komórki wchodzące w skład definiowanej nazwy. Zamknij okienko klikając przycisk Ok.

Krok 3 – sumowanie komórek ze względu na kolor wypełnienia

Kliknij dowolną pustą komórkę i wprowadź do niej formułę Suma. Po otwarciu nawiasu zacznij wpisywać nazwę zdefiniowaną w poprzednim kroku. Zauważ, że Excel podpowiada zdefiniowaną nazwę. Wprowadzoną formułę zatwierdź wciskając na klawiaturze przycisk ENTER.

Operację z zaznaczaniem komórek i nadawaniem nazw należy powtórzyć dla każdego koloru wypełnienia komórek.

 

 

Opublikowano

Metry kwadratowe – formatowanie liczb

Excel nie dysponuje wbudowanym formatem liczb dla przedstawienia takich jednostek jak metry kwadratowe lub metry sześcienne. Jedynym rozwiązaniem jest zastosowanie niestandardowego formatu liczb i napisanie własnego kodu.

Do pustej komórki wprowadź symbol liczby 2 w indeksie górnym. W tym celu a wstążce programu przejdź do karty Wstawianie i kliknij ikonkę Symbol.

W okienku zawierającym różne symbole wybierz liczbę dwa w indeksie górnym,kliknij przycisk Wstaw, a następnie przycisk Zamknij.

W komórce pojawi się liczba dwa w indeksie górnym. Zaznacz liczbę na Pasku formuły, skopiuj ją wciskając na klawiaturze CTRL+C, a następnie wciśnij przycisk ESC, aby wyjść z Paska formuły.

Zaznacz komórki z liczbami, które chcesz sformatować i wywołaj okienko formatowania komórek. W okienku w karcie Liczby przejdź do kategorii Niestandardowe. W polu Typ wpisz następujący kod

# ##0,00" m²"

Pamiętaj, że dwójkę do podawanego wprowadzanego kodu należy wkleić wciskając na klawiaturze CTRL+V. Zatwierdź nowy format liczb klikając przycisk OK.

W efekcie wszystkie zaznaczone wcześniej komórki mają format metrów kwadratowych. W analogiczny sposób można sformatować liczby jako metry sześcienne, miligramy i inne wymagające specjalnego symbolu.

 

Opublikowano

Zadania z Excela

W czasie szkoleń z Excela, wielu uczestników pyta mnie jak to wszystko zapamiętać i jak się tego wszystkiego nauczyć. Jedyną odpowiedzią na to pytanie jest trening.
Należy rozwiązywać zadania, które są jak najbardziej zbliżone do problemów z jakimi spotykają się w swojej codziennej pracy użytkownicy Excela.

Z myślą o wszystkich, którzy potrzebują treningu i poszukują zbioru zadań z Excela, a w szczególności o osobach przygotowujących się do testu z Excela podczas rozmowy o pracę został przygotowany e-book Excel 32 Zadania Rekrutacyjne z rozwiązaniami krok po kroku.

Prezentowany w tym wpisie e-book zawiera 32 typowe zadania z jakimi mogą spotkać się kandydaci do pracy na stanowiska, na których wymagana jest znajomość Excela.
Wszystkie zadania zawierają opisane krok po kroku rozwiązania. Książka jest bogato ilustrowana pritscreenami.
Opracowaniu towarzyszy plik Excela z danymi do samodzielnego rozwiązywania zadań oraz plik Excela z rozwiązanymi zadaniami.
Co więcej jest to jedyne takie opracowanie, w którym użytkownicy angielskiej wersji językowej programu znajdą obok polskich także angielskie nazwy formuł i wszystkich omawianych narzędzi.

Rozwiązując zawarte w e-booku zadania można przećwiczyć następujące zagadnienia:

  • Podstawowe obliczenia matematyczne
  • Adresowanie
  • Skróty klawiaturowe
  • Sortowane i sortowanie niestandardowe
  • Formatowanie warunkowe
  • Formatowanie komórek
  • Formuły logiczne (JEŻELI, ORAZ, LUB, JEŻELI.BŁĄD)
  • Zagnieżdżanie formuł
  • Formuły statystyczne (LICZ.JEŻELI, ŚREDNIA)
  • Formuły matematyczne (SUMA, SUMA.JEŻELI)
  • Formuły wyszukiwania (WYSZUKAJ.PIONOWO)
  • Formuły tekstowe (ZŁĄCZ.TEKST, LEWY, FRAGMENT.TEKSTU, PODSTAW, LITERY.MAŁE)
  • Rozdzielanie tekstu z jednej komórki na kolumny
  • Analizowanie danych przy pomocy Tabel Przestawnych
  • Wstawianie i formatowanie wykresów
  • Znajdowanie i poprawianie błędów
Opublikowano

Listy zależne w Excelu – prosty sposób

W Excelu pomocą narzędzia Sprawdzanie Poprawności można w prosty sposób wstawić do komórki listę rozwijaną, z której użytkownik może wybierać interesujące go pozycje. Co jednak jeżeli elementy na liście są uzależnione od wartości wpisanej w innej komórce. Na przykład przy uzupełnianiu formularza zamówienia z pierwszej listy wybieram kategorię produktową, a z drugiej listy wybieram konkrety produkt. W zależności od wybranej kategorii z pierwszej listy, na drugiej liście prezetowane są tylko produkty wchodzące w skład wybranej kategorii.

W internecie można zaleźć wiele sposobów na tworzenie list zależnych. Niektóre rozwiązania sugerują wykorzystanie w tym celu formuł index i przesunięcie, inne sugerują wykorzystanie makr.
W tym artykule została opisana chyba najprostsza metoda bazująca na nazwach zakresów i formule WARUNKI (użytkownicy starszych wersji Excela zamiast formuły WARUNKI mogą ten sam efekt osiągnąć zagnieżdżając formułę JEŻELI).

Krok pierwszy

W omawianym przykładzie wykorzystane zostaną dwie listy rozwijane. Lista główna zawiera nazwy kategorii produktowej oraz listy zależne zawierające nazwy poszczególnych produktów. Przykładowe listy prezentuje poniższy rysunek.

W arkuszu Formularz do komórki B3 należy wstawić listę pozwalającą na wybór kategorii produktowej. W tym celu na wstążce programu przejdź do karty Dane, a następnie kliknij ikonkę Poprawności danych.

Na ekranie pojawi się okienko Sprawdzania poprawności danych. W okienku tym z pośród wszystkich dozwolonych opcji wybierz Listę, a następnie w polu źródło zaznacz zakres komórek zawierających nazwy kategorii produktowych, tak jak pokazano to na poniższym rysunku.

Po kliknięciu przycisku Ok, w komórce pojawi się rozwijana lista, z której można wybrać nazwę kategorii produktowej.

Krok druginazwanie zakresów

Kolejnym krokiem jest nadanie nazw zakresom zawierającym listy produktów. W tym celu należy zaznaczyć listę wraz z jej tytułem, a następnie na wstążce w karcie Formuły kliknąć ikonkę Utwórz z zaznaczenia. Na ekranie pojawi się okienko Tworzenia nazwy z zaznaczenia. Jeżeli tytuł kolumny znajduje się w pierwszej komórce zaznaczonego zakresu, to upewnij się, że zaznaczona jest opcja Górny wiersz. Zatwierdź wybór klikając przycisk Ok.

Od tej chwili zaznaczonemu zakresowi komórek została przyporządkowana nazwa nabiał. Po zaznaczeniu komórek od C2 do C36 nazwa ta będzie widoczna w polu nazwy nad arkuszem.

Te same kroki powtarzamy w celu nadania nazw zakresom pozostałym listą z nazwami produktów.
Nazwy można później sprawdzić i edytować w okienku menadżera nazw.

Krok trzeci – formuła

Następnym etapem w tworzeniu listy zależnej jest napisanie formuły działającej na następującej zasadzie: jeżeli w komórce B3 wybrana została kategoria nabiał, wtedy wyświetl listę produktów wchodzących w skład tej kategorii, jeżeli natomiast została wybrana pieczywo, wtedy wyświetl listę produktów wchodzącą w skald tej kategorii i tak dalej aż w formule zostaną uwzględnione wszystkie kategorie produktowe i listy.
Użytkownicy Excela 365 i Excela 2019 mogą wykorzystać formułę WARUNKI (IFS), lub tak jak użytkownicy wcześniejszych wersji programu Excel mogą zagnieździć odpowiednią ilość razy formułę JEŻELI (IF).

Ustaw się w dowolnej pustej komórce w arkuszu i wprowadź do niej formułę. Zapis przykładowych formuł podano poniżej.

Formuła WARUNKI

=WARUNKI($B$3=”nabiał”;nabiał;$B$3=”pieczywo”;pieczywo;$B$3=”makaron”;makaron;$B$3=”produkty zbożowe”;produkty_zbożowe;$B$3=”przetwory”;przetwory;$B$3=”sosy i pasty”;sosy_i_pasty;$B$3=”zboża”;zboża;$B$3=”zioła i przyprawy”;zioła_i_przyprawy)

Formuła JEŻELI

=JEŻELI($B$3=”nabiał”;nabiał;JEŻELI($B$3=”pieczywo”;pieczywo;JEŻELI($B$3=”makaron”;makaron;JEŻELI($B$3=”produkty zbożowe”;produkty_zbożowe;JEŻELI($B$3=”przetwory”;przetwory;JEŻELI($B$3=”sosy i pasty”;sosy_i_pasty;JEŻELI($B$3=”zboża”;zboża;zioła_i_przyprawy)))))))

Krok czwarty – wstawienie listy zależnej

Skopiuj z paska formuły wpisaną wcześniej funkcję. Kliknij na komórkę C3 lub na inną komórkę, do której chcesz wstawić listę zależną.
Na wstążce programu przejdź do karty Dane i kliknij ikonkę Poprawność Danych.

W okienku sprawdzania poprawności danych z dozwolonych opcji wybierz listę, a następnie do pola Źródło wklej skopiowaną wcześniej formułę, pamiętaj, żeby formuła zaczynała się od znaku równości. Zatwierdź zmiany klikając przycisk OK.

Uwaga
Pole Źródło ma ograniczenie co do ilości wpisywanych znaków. Ograniczenie to wynosi 255 znaków.

Od tej pory po wybraniu w komórce B3 nazwy kategorii produktowej, w komórce C3 będzie wyświetlała się lista z nazwami produktów należącymi do wybranej kategorii.

Opublikowano

Liczba porządkowa – sortowanie i filtrowanie

Wpisanie na sztywno liczby porządkowej do tabeli sprawia problemy zarówno podczas sortowania jak i flitowania danych. Podczas sortowania tabeli liczby porządkowe układają się w niepoprawnej kolejności, natomiast podczas filtrowania, liczby porządkowe są co prawda w kolejności rosnącej, ale brak im ciągłości. Problem ten można rozwiązać stosując formuły ILE.WIERSZY lub SUMY.CZĘŚCIOWE.

Zastosowanie formuły ILE.WIERSZY

Formułę ILE.WIERSZY możemy zastosować jedynie w przypadku sortowania tabeli.

Przykład zastosowania

Do tabeli wstaw kolumnę Liczba porządkowa (w przykładzie jest to kolumna A), następnie do pierwszej pustej komórki w tej kolumnie wprowadź formułę:

=ILE.WIERSZY($A$2:A2)

Wpisana formuła oblicza ile jest wierszy w zaznaczonym zakresie komórek. W pierwszej komórce kolumny formuła zwróci wartość 1. Należy zwrócić uwagę na zablokowany pierwszy adres w zaznaczonym zakresie komórek. Zastosowanie tu odwołania bezwzględnego, sprawi, że formuła podczas kopiowania do kolejnych komórek w kolumnie będzie zwiększała zakres o kolejną komórkę, a tym samym wynik formuły będzie zwiękaszał się o 1 i w ten sposób utworzona zostanie seria liczb porządkowych.

Sortowanie danych w tabeli nie będzie miało wpływu na wartości w kolumnie Liczba porządkowa, gdyż za każdym razem formuła zostanie ponownie przeliczona i zwróci poprawną serię danych.

Zastosowanie formuły SUMY.CZĘŚCIOWE

Formuła SUMY.CZĘŚCIWOE może być zastosowana zarówno do tworzenia liczby porządkowej podczas sortowania jak i fitrowania, jednak dla poprawnego działania wymaga, aby w tabeli była przynajmniej jedna kolumna z wypełnionymi wszystkimi komórkami.

Przykład zastosowania

Podobnie jak w poprzednim przykładzie wstaw do tabeli kolumnę Liczba porządkowa i wprowadź do pierwszej pustej komórki formułę:

=SUMY.CZĘŚCIOWE(3;$B$2:B9)

Formułą SUMY.CZĘŚCIOWE z parametrem 3 obliczy ile jest nie pustych komórek w kolumnie B. Dzięki zablokowaniu symbolem dolara pierwszego adresu w zaznaczonym zakresie, podczas kopiowania formuły do kolejnych komórek, będzie ona zwiększała swój wynik o 1.

Poniższy rysunek pokazuje wynik działania formuły na oryginalnej tabeli i na tabeli z zastosowanymi filtrami oraz sortowaniem.

Opublikowano

Drukowanie siatki Excela, nagłówków kolumn i wierszy

Drukując dokument przygotowany w Excelu na wydruku nie pojawi się siatka komórek, nagłówki kolumn ani też nagłówki wierszy. Po wydrukowaniu prostego zestawienia będzie ono wyglądało tak jak na rysunku poniżej.

Prosta tabela
Podgląd wydruku

Istnieje jednak możliwość takiego ustawienia wydruku, aby pojawiła się na nim siatka komórek oraz nagłówki kolumn i wierszy. W tym celu na wstążce programu Excel należy przejść do karty Układ strony i kliknąć ikonkę Tytuły wydruku.

Na ekranie zostanie wyświetlone okienko Ustawienia strony. W oknie tym, w sekcji Drukuj należy zaznaczyć opcje: Linie siatki oraz Nagłówki wierszy i kolumn.

Po zatwierdzeniu wyboru i wydrukowaniu dokumentu lub włączeniu podglądu wydruku, na wydruku będą widoczne linie siatki oraz nagłówki kolumn i wierszy, tak jak na poniższym rysunku.

Wydruk z liniami siatki oraz nagłówkami wierszy i kolumn
Opublikowano

Dodawanie i usuwanie komórek z zaznaczonych zakresów

Każdy z nas potrafi zaznaczać komórki w Excelu za pomocą myszko albo klawiatury. Niesąsiadujące zakresy zaznaczane są przez przytrzymanie na klawiaturze przycisku Ctrl. Co jedank zrobić, gdy do istniejących już zaznaczonych obszarów należy dodać nowe, lub usunąć niechciane zaznaczone zakresy lub chemy zapamiętać zaznaczone już zakresy, żeby dodając nowe przez przyadek nie utracić istniejących.

W celu stosujemy opcję rozszerzonego zaznaczania. W celu uruchomienia tego trybu należy na klawiaturze wcisnąć przyciski Shift F8.

Zaznaczanie dodatkowych zakresów komórek

W celu dodania do zaznaczonego zakresu nowych niesąsiadujących ze sobą zakresów komórek należy:

  1. Zaznaczyć pierwszy zakres komórek (jeżeli nie był wcześniej zaznaczony).
  2. Na klawaiturze wcisnąć klawisze Shift F8.
  3. Zaznaczyć kolejne zakresy przy użyciu myszki lub klawiatury.
  4. Aby wyłączyć zapamiętanie zaznaczonych komórek na klawiaturze ponownie należy wcisnąć przyciski Shift F8 lub przycisk Esc.

Usuwanie zbędnego zaznaczenia

Jeżeli chcemy usunąć zaznaczenie z niektórych komórek, pozostawiając inne komórki zaznaczone należy:

  1. Na klawaiturze wcisnąć klawisze Shift F8.
  2. W ramch zaznaczonego zakresu przy użyciu myszki lub klawiatury wybrać komórki, które nie mają być zazanczone.
  3. Wyłączyć tryb rozszerzonego zaznacznania wciskając na klawiaturze przyciski Shift F8 lub przycisk Esc.
Opublikowano

Blokowanie hasłem wybranych komórek

Włączając w Excelu ochronę arkusza następuje zablokowanie do edycji wszystkich komórek, w tym momencie nasuwa się pytanie co zrobić, aby zablokować do edycji tylko kilka wybranych komórek, a wszystkie pozostałe pozostawić odblokowane z możliwością edycji. Rozwiązanie tego problemu przebiega dwuetapowo.

Pierwszym etapem jest odblokowanie do edycji całego arkusza. Drugi krok polega na zablokowaniu tylko wybranych komórek.

Odblokowanie całego arkusza

Zaznacz cały arkusz, możesz to zrobić za pomocą skrótu klawiaturowego CTRL + A lub klikając przycisk w kształcie trójkąta znajdujący się w lewym górnym rogu arkusza.

Następnie wywołaj okienko Formatowania komórek. Kliknij prawym klawiszem myszy na zaznaczony arkusz i z wyświetlonego menu wybierz opcję Formatuj komórki… możesz także użyć skrótu klawiaturowego CTRL+1.

W oknieku Formatowanie komórek przejdź do karty Ochrona i odznacz opcję Zablokuj, tak jak pokazano to na poniższym rysunku. Następnie kliknij przycisk OK. W efekcie po założeniu ochrony na arkusz wszystkie komórki będą dostępne do edycji.

Zablokowanie wybranych komórek

Zaznacz komórki, które chcesz zablokować do edycji. Jeżeli komórki nie sąsiadują ze sobą , podczas zaznaczania wciśnij na klawiaturze przycisk CTRL.

Następnie wywołaj okienko Formatowania komórek. Kliknij prawym klawiszem myszy na jedną z zaznaczonych komórek i z wyświetlonego menu wybierz opcję Formatuj komórki… możesz także użyć skrótu klawiaturowego CTRL+1.

W oknieku Formatowanie komórek przejdź do karty Ochrona i zaznacz opcję Zablokuj, tak jak pokazano to na poniższym rysunku. Następnie kliknij przycisk OK. W efekcie po założeniu ochrony na arkusz wybrane komórki będą zablokowane do edycji.

Teraz przejdź na wstążce do karty Recenzja i kliknij ikonkę Chroń Arkusz. Ustal hasło do ochrony arkusza i potwierdź je w kolejnym okienku.

Teraz wybrane przez Ciebie komórki są chronione hasłem, pozostałe komórki w arkuszu są dostępne do edycji.