24 sztuczki Excela, które chciałbyś znać wczoraj

Excel na wiele sposobów ułatwił nam życie. Jednak istnieją pewne problemy z programem Excel, które nadal marnują nasz czas i zwiększają poziom frustracji. Są to małe problemy, które można naprawić w kilka sekund, ale nieznajomość właściwego sposobu może kosztować dużo czasu.

24 sztuczki Excela do codziennej pracy

W tym poście na blogu przedstawię 24 sztuczki Excela, które pozwolą Ci zaoszczędzić mnóstwo czasu i energii. Jeśli pracujesz z Excelem, prędzej czy później wpadniesz w kłopoty z czymś, co wydaje się niemożliwe.

Oto kilka szybkich poprawek, które to umożliwią.

Sztuczka Excela nr 1 - Kopiuj i wklej formuły bez zmiany odniesień

To się zdarza cały czas. Próbujesz skopiować komórkę z formułą do innej komórki i wszystko się miesza. Dzieje się tak, ponieważ odniesienia zmieniają się, gdy kopiujesz do innej lokalizacji (chyba że będziesz surowy i naprawisz wszystkie odniesienia).

Oto szybka poprawka:

1. Zaznacz komórki, które chcesz skopiować.
2. Przejdź do Strona główna -> Numer. Z listy rozwijanej Format liczb wybierz Tekst. Spowoduje to zmianę formatu komórki na tekst.
3. Naciśnij F2, aby przejść do trybu edycji. Teraz przytrzymaj klawisz Control i naciśnij Enter.
4. Skopiuj komórki.
5. Przejdź do komórki docelowej i wklej.
6. Zmień format z tekstu na ogólny.
7. Naciśnij F2 i naciśnij Control + Enter.
8. I gotowe!!.

Oto kolejna świetna metoda programu Excel MVP Toma Urtisa (przy użyciu funkcji Znajdź i zamień) [kredyt dla Dave'a Brunsa z ExcelJet za pokazanie mi tej metody]

Sztuczka Excela nr 2 - Nie wyświetla się uchwyt napełniania

Widzę, że tak wiele osób boryka się z tym problemem. Uchwyt Fill po prostu nie pojawia się w pracy.

Oto poprawka:

1. Przejdź do Plik -> Opcje.
2. W oknie dialogowym Opcje wybierz Zaawansowane.
3. Zaznacz opcję „Włącz uchwyt wypełniania i przeciąganie i upuszczanie komórek”.

Sztuczka programu Excel nr 3 - liczba jako tekst

Utykam z tym wiele razy. Liczby są sformatowane jako tekst i nie działają dobrze w formułach.

Oto szybka poprawka:

• Dodaj 0 do komórki. Dzięki temu liczba pozostanie nienaruszona i może być używana w formułach.

Wiele osób używa apostrofu (‘) przed liczbą, aby tworzyć liczby jako tekst. Ta sztuczka Excela zadziała również w tym przypadku.

Sztuczka programu Excel nr 4 - trzeba przekonwertować formuły na wartości

To jest proste. Jeśli masz formuły, które należy przekonwertować na wartości, wykonaj następujące czynności:

1. Skopiuj komórki.
2. Kliknij prawym przyciskiem myszy i wybierz opcję Wklej specjalnie.
3. W oknie dialogowym Wklej specjalne wybierz Wartości (Skrót klawiaturowy - Alt + E + S + V).
4. Kliknij OK.

Zobacz też: Dowiedz się więcej o wklejaniu skrótów specjalnych.

Sztuczka Excela nr 5 – Zablokowany kursor – Nie można zmienić aktywnej komórki za pomocą klawiszy strzałek

Gdy mi się to przydarzyło po raz pierwszy, zamknąłem skoroszyt programu Excel i ponownie uruchomiłem system. Problem można oczywiście łatwo rozwiązać bez ponownego uruchamiania.

Problem w tym przypadku polega na tym, że przypadkowo aktywowałeś Scroll Lock.

Wyłącz go i ruszaj w drogę.

Sztuczka Excela nr 6 – WYSZUKAJ.PIONOWO/MATCH nie działa, nawet jeśli istnieje dopasowanie

Widać, że 2 wartości pasują idealnie, ale formuła WYSZUKAJ.PIONOWO lub PODAJ.POZYCJĘ nadal mówi, że nie ma dopasowania.

Arghhhhh…

Tutaj winowajcami są dodatkowe przestrzenie. Jeśli tekst zawiera spacje wiodące lub końcowe (lub więcej niż jedną spację między wyrazami), program Excel nie uzna go za dokładne dopasowanie, a formuła wyświetli nieoczekiwane wyniki.

Obejście - użyj PRZYCINAĆ funkcjonować. Usuwa wszelkie spacje wiodące i końcowe oraz wszelkie dodatkowe spacje między słowami.

Zobacz też: Wiele problemów można rozwiązać, jeśli wiesz, jak wyczyścić dane w programie Excel.

Sztuczka Excela nr 7 - chcesz ukryć tekst w komórkach

Czasami może być konieczne ukrycie tekstu / liczb w komórce, aby użytkownik nie mógł ich zobaczyć.

Można to zrobić na 2 sposoby:

• Ten dobry: Zmień kolor czcionki tekstu na biały (lub inny kolor tła).
• Lepszy: Wybierz komórkę i naciśnij Control + 1. Spowoduje to otwarcie okna dialogowego Formatowanie komórek. Na karcie liczb wybierz Niestandardowy i wpisz format ;;;

Sztuczka programu Excel nr 8 - trzeba skopiować tylko widoczne komórki

• Pytanie: Co się stanie, gdy skopiujesz zakres komórek, który ma ukryte wiersze/kolumny i wkleisz go gdzieś?
• Odpowiadać: Wszystkie wartości komórek (widoczne + ukryte) są kopiowane i wklejane.
• Sprawa: Nie chcę kopiować wartości ukrytych komórek.
• Rozwiązanie: Wybierz komórki i naciśnij Alt +; (wybiera tylko widoczne komórki). Teraz wklej gdziekolwiek i tylko widoczne komórki zostaną wklejone.

Sztuczka programu Excel nr 9 – Zaznacz wszystkie puste komórki za jednym razem

Często importuję dane z różnych baz danych. Wiele komórek w tych danych jest pustych, gdzie muszę wstawić 0 lub N/A. Szybki sposób na zaznaczenie wszystkich pustych komórek:

1. Zaznacz wszystkie komórki w zakresie.
2. Przejdź do Strona główna -> Edycja -> Znajdź i wybierz -> Przejdź do specjalnych.
3. Wybierz Puste w oknie dialogowym Przejdź do specjalnego.
4. Kliknij OK.

Spowoduje to wybranie wszystkich pustych komórek. Aby wprowadzić 0 we wszystkich komórkach, wpisz 0 (wprowadzi to wartość w aktywnej komórce) i naciśnij Control + Enter.

Sztuczka Excela nr 10 - Musisz usunąć podziały wierszy z tekstu

Jeśli masz dużo łamania wierszy i chcesz je usunąć, wykonanie tego ręcznie zajmie dużo czasu.

Szkoda byłoby, gdybyś musiał to zrobić ręcznie.

Wypróbuj to szybkie rozwiązanie:

1. Wybierz dane
2. Przejdź do strony głównej -> Znajdź i wybierz -> Zamień (skrót klawiaturowy - Kontrola + H)
3. W oknie dialogowym Znajdź i zamień:
   • Znajdź co: naciśnij Control + J (możesz nie widzieć niczego poza migającą kropką)
   • Zamień na: znak spacji (naciśnij raz spację)
4. Kliknij Zamień wszystko (upewnij się, że zawijanie słów jest włączone)

https://www.youtube.com/watch?v=zj_OTwwt7Tg

Zobacz też: Przeczytaj więcej o niesamowitych rzeczach, które funkcja Znajdź i zamień może dla Ciebie zrobić.

Sztuczka programu Excel nr 11 – sortowanie nie działa, gdy komórki są scalone

Oczywiście, że tak nie jest.

To nie zostało zrobione w ten sposób. Ale to nie znaczy, że nie możesz mieć tego, czego chcesz. Oto substytut dla Scal i wyśrodkuj i nazywa się Wyśrodkuj przez zaznaczenie.

Oto właściwy sposób na scalanie komórek w programie Excel:

1. Wybierz komórki, które chcesz scalić (tylko kolumny. W przypadku wierszy użyj opcji scalania i wyśrodkowania).
2. Naciśnij Control + 1, aby otworzyć okno dialogowe Formatowanie komórek.
3. Przejdź do zakładki Wyrównanie.
4. Z listy rozwijanej Poziomo wybierz opcję Wyśrodkuj przez zaznaczenie.
5. Kliknij OK.

Teraz masz coś, co wygląda tak samo i działa świetnie z sortowaniem.

Sztuczka Excela nr 12 - dane mają duplikaty

Ze zduplikowanymi danymi można zrobić dwie rzeczy: Zaznacz to lub Usuń to

• Wyróżnij zduplikowane dane: Wybierz dane i przejdź do Strona główna -> Formatowanie warunkowe -> Reguły podświetlania komórek -> Zduplikowane wartości.
  • Określ formatowanie, a wszystkie zduplikowane wartości zostaną podświetlone.
• Usuń duplikaty w danych: Wybierz dane i przejdź do Dane -> Usuń duplikaty.
  • Wybierz, czy dane mają nagłówki, a następnie wybierz kolumnę i kliknij OK. Spowoduje to usunięcie duplikatów z listy. Jeśli chcesz, aby oryginalna lista była nienaruszona, skopiuj i wklej dane w innym miejscu, a następnie zrób to.

Związane z: Kompletny przewodnik po znajdowaniu i usuwaniu duplikatów w programie Excel

Sztuczka Excela nr 13 – trzeba sprawdzić błędy pisowni

Naciśnij klawisz F7, aby uruchomić sprawdzanie pisowni.

Oto szczegółowy samouczek dotyczący sprawdzania pisowni w programie Excel.

Sztuczka Excela nr 14 - Przewijanie w dół w dużych zestawach danych ukryj tytuły kolumn

Istnieją 2 sposoby rozwiązania tego problemu.

• Użyj funkcji tabeli Excel: Korzystanie z funkcji tabeli Excel zapewnia, że ​​podczas przewijania w dół nagłówki są zawsze widoczne. Jako dobrą praktykę, tabelę Excel należy zawsze używać z danymi tabelarycznymi, ponieważ ma bardzo potężną i przydatną funkcję. Dowiedz się więcej o tabelach Excel
• Korzystanie z opcji bezpłatnych okienek: Jeśli chcesz, aby było widoczne więcej niż nagłówek (na przykład w przypadku podtytułu lub wartości, takich jak suma, średnia na początku), użyj funkcji zamrożenia okienka programu Excel. Aby to zrobić:
  • Wybierz cały wiersz, powyżej którego chcesz zablokować wiersze (lub skrajną lewą komórkę w tym wierszu)
  • Przejdź do Widok -> Zablokuj okienka -> Zablokuj okienka
    Dzięki temu zawsze będziesz widzieć stałe wiersze podczas przewijania w dół.

Sztuczka Excela nr 15 – Marnowanie czasu na przewijanie tam i z powrotem w dużych zestawach danych

Jeśli pracujesz z ogromnymi zestawami danych i musisz przewijać w górę i w dół (lub w prawo i w lewo), aby odnieść się do niektórych punktów danych, istnieje sposób na zaoszczędzenie dużo czasu i przewijania - za pomocą Podziel okno funkcja.

Ta funkcja może podzielić okno na dwie lub cztery części, a każda część ma własne włączone przewijanie. Więc jeśli musisz odwoływać się do danych w kolumnie CZ, podczas gdy pracujesz w kolumnie A, ta funkcja jest dla Ciebie idealna.

1. Przejdź do Widok -> Okno -> Podziel.

Dzieli to arkusz na cztery części. Każdą częścią można sterować za pomocą dodatkowego paska przewijania (w pionie i poziomie). Jeśli chcesz tylko pionowego podziału, przeciągnij poziomą linię podziału do dołu (a zniknie).

Sztuczka Excela nr 16 - Formuła debugowania

Pominięto przecinek, błędne odwołanie, brakujący argument, niewłaściwą pozycję nawiasów itd. Mogą istnieć setki przyczyn błędnego wyniku formuły.

Debugowanie formuły może być bolesne, zwłaszcza jeśli została stworzona przez kogoś innego. Oto sposób, aby było to łatwe i proste. Aby debugować formułę:

1. Wybierz komórkę zawierającą formułę.
2. Przejdź do Formuły -> Audyt formuł -> Oceń formułę (Skrót klawiaturowy Alt + TUF).
3. Kliknij Oceń, aby zobaczyć kroki, w których formuła jest oceniana przez program Excel.

Chcesz debugować część formuły - Oto inny sposób użycia klawisza F9

Sztuczka programu Excel nr 17 - program Excel wstawia odwołanie do komórki podczas edytowania formuły

Jest to zdecydowanie jeden z najbardziej irytujących problemów z programem Excel, z którymi przechodzę prawie codziennie. Wyobraź sobie, że masz ogromną formułę, naciskasz F2, aby edytować formułę (w komórce lub formatowaniu warunkowym lub nazwanym zakresie), a gdy tylko naciśniesz lewy klawisz, aby edytować jej część, Excel wstawi niechciane odwołanie do komórki. Excel czasami to robi, ponieważ nie wie, że jesteś w trybie edycji. Możesz zobaczyć bieżący tryb w lewym dolnym rogu ekranu.

Droga dookoła jest prosta, naciśnij F2 raz aby przejść do trybu edycji, a teraz możesz swobodnie poruszać się po swojej formule.

Sztuczka Excela nr 18 – Zbyt wiele nazwanych zakresów (nie pamiętam nazw)

Często używam nazwanych zakresów. Ma wiele zalet i jest łatwy w użyciu. Ale co, jeśli jest zbyt wiele nazwanych zakresów? Czy musiałbyś wracać do menedżera nazw raz za razem, aby uzyskać imię?

NIE!!

Po prostu użyj skrótu F3. Wyświetli nazwy wszystkich nazwanych zakresów w skoroszycie. Wystarczy dwukrotnie kliknąć ten, którego chcesz użyć, a zostanie on wklejony (w formule, jeśli ją tworzysz, lub w arkuszu)

Pytanie dla Ciebie? Co się stanie, jeśli masz wiele nazwanych zakresów i zmniejszysz powiększenie poniżej 40%? Zostawię to do rozgryzienia.

Sztuczka Excela nr 19 - Błąd błędu wszędzie

Błąd to jedna brzydka plama w arkuszu kalkulacyjnym. Ale czasami błędy są złem koniecznym. Możesz chcieć, aby formuła zwracała błąd (powiedzmy, gdy wartość wyszukiwania nie zostanie znaleziona w funkcji WYSZUKAJ.PIONOWO). Ale nie musisz przechodzić przez trudy wpatrywania się w jeden. Po prostu użyj IFERROR.

IFERROR nie działa w programie Excel w wersji 2003 i wcześniejszych. W takim przypadku użyj kombinacji JEŻELI i ISERROR.

Sztuczka Excela nr 20 – Musisz szybko usunąć wszystkie komentarze

Często ponownie wykorzystuję szablony z komentarzami. Usunięcie wszystkich komentarzy to jedna z operacji na arkuszu, którą wykonuję za każdym razem, gdy zaczynam od nowa. Oto szybki sposób na wybranie wszystkich komentarzy za jednym razem i usunięcie wszystkich:

1. Zaznacz wszystkie komórki.
2. Przejdź do Strona główna -> Edycja -> Znajdź i wybierz -> Przejdź do opcji specjalnych.
3. Wybierz Komentarze w oknie Przejdź do specjalnego okna dialogowego.
4. Kliknij OK.

Spowoduje to wybranie wszystkich komórek, które mają w sobie komentarze. Teraz przejdź do dowolnej z zaznaczonych komórek, kliknij prawym przyciskiem myszy i wybierz Usuń komentarz

Sztuczka Excela nr 21 – Przełączaj się tylko między arkuszami kalkulacyjnymi Excel

Czy jesteś jednym z tych, którzy otwierają miliony aplikacji jednocześnie.

Często mam otwarte jednocześnie Google Chrome, Excel, PowerPoint, Kindle, Mozilla Firefox, Email i wiele innych aplikacji.

Większość z nas używa Alt + Tab do przełączania się między różnymi otwartymi aplikacjami, ale co, jeśli chcesz przechodzić tylko przez otwarte skoroszyty programu Excel. Posługiwać się Control + Tab zamiast.

Sztuczka Excela #22 - Wyświetl numer z tekstem

Chcesz wyświetlić liczbę 100 jako 100 milionów lub 100 gramów? Możesz to zrobić i nadal używać tej liczby w obliczeniach. Sztuką jest znajomość właściwego formatu liczb. Aby wyświetlić 100 jako 100 milionów:

1. Przejdź do komórki zawierającej liczby (lub zaznacz wszystkie komórki, w których chcesz to zastosować).
2. Naciśnij Ctrl + 1.
3. Na karcie Numer przejdź do Niestandardowe (w lewym okienku).
4. Zmień Ogólne na Ogólne „Milion”.

Niestandardowe formatowanie liczb to przyjemność sama w sobie. Naucz się niesamowitej sztuczki, używając niestandardowego formatu liczb

Sztuczka Excela nr 23 – Szybko przewiń w prawo w ogromnym zestawie danych

Załóżmy, że masz ogromny zestaw danych, który obejmuje wiele kolumn (powiedzmy 200 kolumn). I musisz często przewijać swoje dane. Jakie są sposoby, w jakie możesz to zrobić.

• Zwykły sposób 1: Użyj klawisza strzałki w prawo (i przykryj każdą kolumnę jedna po drugiej).
• Zwykły sposób 2: Zostaw klawiaturę, chwyć mysz, wybierz pasek przewijania i przewijaj.
• Oto trzeci nietypowy sposób - Użyj Alt + klawisz PageDown. Robi to, co Page down robi dla wierszy. Przeskakuje 20 kolumn na raz.

Sztuczka Excela nr 24 – Cierpienie na powolny arkusz kalkulacyjny

Wszyscy przez to przeszliśmy. Arkusze kalkulacyjne Excela mają tendencję do bycia cholernie wolnymi.

Niektóre poniższe kroki mogą pomóc przyspieszyć. [Napisałem bardziej szczegółowy artykuł o tym, jak przyspieszyć arkusze kalkulacyjne. Kliknij tutaj, aby przeczytać]

• Użyj kolumn pomocnika.
• Unikaj lotnej formuły.
• Użyj ręcznego trybu obliczania zamiast automatycznego.
• Użyj tabel programu Excel do przechowywania danych.
• Unikaj formuły tablicowej.

Oto moje 24 najlepsze sztuczki Excela (w dowolnej kolejności). Jestem pewien, że jest ich setki więcej, ale jest tylko tyle osób, które można napisać za jednym razem! 😉

Podziel się listą najlepszych codziennych sztuczek rozwiązywania problemów, których używasz, aby wykonać pracę 🙂

Przydatne zasoby programu Excel:

• Przydatne pytania do rozmowy kwalifikacyjnej Excel + odpowiedzi.
• Bezpłatne szkolenie online z programu Excel.
• Darmowe szablony Excela.
• Jak skopiować formatowanie wykresu w programie Excel