26 wskazówek Excela, jak zostać profesjonalistą w zakresie arkuszy kalkulacyjnych

Załóżmy, że zmieniasz nie tylko zawijanie w komórce, ale także cały wygląd — czcionkę, kolor, cokolwiek. I chcesz go zastosować do wielu, wielu innych komórek. Sztuczka to narzędzie Malarz formatów , które znajduje się na karcie Strona główna, które wygląda jak pędzel.

Wybierz interesującą Cię sprzedaż, kliknij ikonę , a następnie kliknij inną komórkę, aby pomalować format — będą one pasować wyglądem, a nie treścią. Chcesz zastosować go do wielu kart? Kliknij dwukrotnie ikonę pędzla, a następnie kliknij kilka komórek.

Wpisywanie do komórek arkusza kalkulacyjnego może być frustrujące, ponieważ domyślnie wpisywany tekst jest kontynuowany w nieskończoność, bez przewijania z powrotem do nowego wiersza. Możesz to zmienić. Utwórz nową linię, wpisując Alt + Enter (samo naciśnięcie Enter zabierze Cię z komórki). Lub kliknij opcję Zawijaj tekst na karcie Strona główna u góry ekranu, co oznacza, że ​​cały tekst zawija się bezpośrednio na krawędzi komórki, w której się znajdujesz. Zmień rozmiar wiersza/kolumny, a tekst zostanie ponownie zawinięty, aby dopasować.

Jeśli masz wiele komórek z przepełnieniem tekstu, zaznacz je wszystkie przed kliknięciem opcji Zawijaj tekst. Lub zaznacz wszystkie komórki, zanim jeszcze je wpiszesz, i kliknij Zawijaj tekst. Wtedy to, co wpiszesz, zostanie zawinięte w przyszłości.

To oczywiste, ale tak łatwo przeoczyć. Zaczynasz wpisywać serię powtarzających się rzeczy, takich jak daty (1.01.2020, 1/2/20, 1/3/20 itd.) i wiesz, że czeka Cię długi dzień. Zamiast tego rozpocznij serię i przesuń kursor na ekranie do prawej dolnej części ostatniej komórki — uchwytu wypełniania. Gdy zmieni się w znak plus ( + ), kliknij i przeciągnij w dół, aby zaznaczyć wszystkie komórki, które chcesz wypełnić. W magiczny sposób wypełnią się wzorem, który zacząłeś. Może również iść w górę kolumny lub w lewo lub w prawo w rzędzie.

Co więcej — możesz automatycznie wypełniać bez większego wzoru. Ponownie wybierz komórkę lub komórki, przejdź do uchwytu wypełniania, kliknij prawym przyciskiem myszy i przeciągnij. Otrzymasz menu opcji. Im więcej danych wprowadzisz na początku, tym lepsza będzie opcja Wypełnij serię, tworząc opcje autouzupełniania. Sprawdź ten samouczek firmy Microsoft.

Flash Fill inteligentnie wypełni kolumnę na podstawie wzorca danych, który widzi w pierwszej kolumnie (pomaga, jeśli górny wiersz jest unikalnym wierszem nagłówka). Jeśli na przykład pierwsza kolumna zawiera wszystkie numery telefonów w formacie „2125034111” i chcesz, aby wszystkie wyglądały jak „(212)-503-4111”, zacznij pisać. W drugiej komórce Excel powinien rozpoznać wzorzec i wyświetlić to, co według niego chcesz. Po prostu naciśnij Enter, aby ich użyć.

Działa to z liczbami, nazwiskami, datami itp. Jeśli druga komórka nie podaje dokładnego zakresu, wpisz więcej — wzorzec może być trudny do rozpoznania. Następnie przejdź do zakładki Dane i kliknij przycisk Wypełnienie flashowe. Sprawdź ten samouczek firmy Microsoft, aby uzyskać więcej informacji.

Istnieją znacznie szybsze sposoby wybierania zestawu danych niż używanie myszy i przeciąganie kursora, zwłaszcza w arkuszu kalkulacyjnym, który może zawierać setki tysięcy wierszy lub kolumn. Kliknij pierwszą komórkę, którą chcesz zaznaczyć, i przytrzymaj Ctrl+Shift , a następnie naciśnij strzałkę w dół, aby wyświetlić wszystkie dane w kolumnie poniżej, strzałkę w górę , aby wyświetlić wszystkie dane powyżej, lub strzałkę w lewo lub w prawo , aby wyświetlić wszystkie dane rząd (oczywiście w lewo lub w prawo). Połącz kierunki, a otrzymasz całą kolumnę, a także wszystko w rzędach po lewej lub prawej stronie. Wybiera tylko komórki z danymi (nawet niewidocznymi danymi).

Jeśli użyjesz Ctrl+Shift+End , kursor przeskoczy do najniższej prawej komórki z danymi, zaznaczając wszystko pomiędzy, nawet puste komórki. Więc jeśli kursor znajduje się w lewej górnej komórce (A1), to wszystko.

Ctrl+Shift+* (gwiazdka) może działać szybciej, ponieważ zaznacza cały ciągły zestaw danych komórki, ale zatrzyma się na komórkach, które są puste.

Załóżmy, że masz kolumnę pełną nazwisk, od pierwszej do ostatniej, ale potrzebujesz dwóch kolumn, które je rozdzielają. Wybierz dane, a następnie na karcie Dane (u góry) kliknij opcję Tekst do kolumn. Wybierz, aby oddzielić je ogranicznikami (na podstawie spacji lub przecinków — świetne w przypadku wartości danych CSV) lub ustaloną szerokością. Stała szerokość jest używana, gdy wszystkie dane są upchane w pierwszej kolumnie, ale oddzielone stałą liczbą spacji lub kropki. Reszta jest jak magia, z dodatkowymi opcjami dla niektórych liczb.

Masz kilka wierszy. Chcesz, żeby były kolumnami. Lub odwrotnie. Zwariowałbyś, przenosząc rzeczy komórka po komórce. Skopiuj te dane, wybierz opcję Wklej specjalnie , zaznacz pole Transpozycja i kliknij przycisk OK, aby wkleić w innej orientacji. Kolumny stają się rzędami, rzędy stają się kolumnami.

Z jakiegoś powodu może być konieczne ciągłe pisanie tego samego w komórkach w arkuszu. To rozdzierające. Wystarczy kliknąć cały zestaw komórek, przeciągając kursor lub przytrzymując klawisz Ctrl podczas klikania każdej z nich. Wpisz go w ostatniej komórce, a następnie naciśnij Ctrl + Enter (nie tylko Enter) — to, co wpisałeś, trafia do każdej zaznaczonej komórki.

Działa to również z formułami i zmieni odwołania do komórek, aby działały z dowolnym wierszem/kolumną, w której znajdują się inne komórki.

Załóżmy, że masz ogromną liczbę liczb w formacie dziesiętnym, które chcesz pokazać jako wartości procentowe. Problem polega na tym, że cyfra 1 nie powinna oznaczać 100%, ale to właśnie daje Excel, jeśli po prostu klikniesz przycisk Procent stylu (lub naciśniesz Ctrl-Shift-% ).

Chcesz, żeby 1 to 1%. Musisz więc podzielić to przez 100. W tym miejscu pojawia się Paste Special.

Najpierw wpisz 100 w komórce i skopiuj ją. Następnie wybierz wszystkie liczby, które chcesz ponownie sformatować, wybierz opcję Wklej specjalnie , kliknij przycisk opcji „ Podziel ” i boom idzie w dal : masz liczby zamienione na wartości procentowe. Oczywiście działa to również w celu natychmiastowego dodawania, odejmowania lub mnożenia liczb.

Możesz umieścić grafikę w dowolnym elemencie wykresu Excel. Dowolny element. Każdy pasek, kawałek ciasta itp. może wspierać swój własny obraz. Na przykład powyżej na wykresie kołowym znajduje się flaga stanu Dakota Południowa (umieszczana przez wybranie wycinka, za pomocą menu wysuwanego Opcje serii i wybranie opcji „ Wypełnienie obrazem lub teksturą ”) oraz osadzone logo PCMag (umieszczone za pomocą Przycisk Zdjęcia na karcie Wstaw ). Możesz nawet w ogóle przejść z „ bez wypełnienia ”, co spowodowało brakujący kawałek.

Clipart można wyciąć i wkleić do elementu — banknoty dolarowe, aby pokazać wydane dolary, woda kapie na koszty kanalizacji i tego typu rzeczy. Mieszanie i dopasowywanie zbyt wielu elementów graficznych uniemożliwia odczytanie, ale opcje, które masz, są warte cyfrowego majsterkowania. Pozwól swojemu projektantowi graficznemu sprawdzić je przed ich użyciem.

Excel ma więcej typów wykresów niż Jimmy Carter ma orzeszki ziemne, ale prawie niemożliwe jest znalezienie domyślnego wykresu idealnego do prezentacji. Na szczęście zdolność programu Excel do dostosowywania wszystkich wykresów jest wzorowa. Ale kiedy musisz je odtworzyć , to jest ból. Nie musi tak być. Zapisz oryginalny wykres jako szablon.

Gdy wykres zostanie udoskonalony, kliknij go prawym przyciskiem myszy . Wybierz Zapisz jako szablon . Zapisz plik z rozszerzeniem CRTX w domyślnym folderze szablonów programu Microsoft Excel. Gdy to zrobisz, nałożenie szablonu to ciasto. Wybierz dane, które chcesz wyświetlić na wykresie, przejdź do karty Wstaw , kliknij Polecane wykresy , a następnie kartę Wszystkie wykresy i folder Szablony . W polu Moje szablony wybierz ten, który chcesz zastosować, a następnie kliknij OK .

Niektóre elementy, takie jak rzeczywisty tekst w legendach i tytułach, nie zostaną przetłumaczone, chyba że są częścią wybranych danych. Otrzymasz wszystkie wybrane czcionki i kolory, osadzone grafiki, a nawet opcje serii (takie jak cień lub poświata wokół elementu wykresu).

Ta, o nazwie Suma 3D, działa, gdy w skoroszycie znajduje się wiele arkuszy, które mają ten sam układ podstawowy, na przykład zestawienia kwartalne lub roczne. Na przykład w komórce B3 zawsze masz kwotę w dolarach za ten sam odpowiedni tydzień w czasie.

W nowym arkuszu w skoroszycie przejdź do komórki i wpisz formułę, taką jak =sum('Y1:Y10'!B3). Wskazuje to na formułę SUMA (dodawanie rzeczy) dla wszystkich arkuszy zatytułowanych od Y1 do Y10 (czyli o wartości 10 lat) i patrząc na komórkę B3 w każdym. Wynik będzie sumą wszystkich 10 lat. To dobry sposób na stworzenie głównego arkusza kalkulacyjnego, który odwołuje się do ciągle zmieniających się danych.

Łatwo jest ukryć wiersz lub kolumnę — wystarczy zaznaczyć całość, klikając nagłówek litery lub cyfry, kliknąć prawym przyciskiem myszy i wybrać „Ukryj”. (Możesz odkryć, wybierając jednocześnie kolumny po obu stronach, klikając prawym przyciskiem myszy i wybierając opcję „Odkryj”). Ale co, jeśli masz tylko małą część niewygodnie umieszczonych danych, które chcesz ukryć, ale nadal chcesz mieć możliwość pracy? Łatwo. Podświetl komórki, kliknij prawym przyciskiem myszy i wybierz Formatuj komórki . Na karcie Numer u góry przejdź do kategorii i wybierz „ Niestandardowe ”. Wpisz trzy średniki ( ;;; ) w polu Typ:. Kliknij OK. Teraz liczby nie są widoczne, ale nadal możesz ich używać w formułach.

Twój typowy skoroszyt programu Excel — plik, w którym pracujesz — może zostać załadowany wieloma arkuszami roboczymi (każdy arkusz jest oznaczony zakładką na dole, którą możesz nazwać). Jeśli chcesz, ukryj arkusz, zamiast go usuwać, dzięki czemu jego dane będą nadal dostępne nie tylko w celach informacyjnych, ale także dla formuł w innych arkuszach w skoroszycie. Kliknij prawym przyciskiem myszy dolną kartę arkusza i wybierz Ukryj . Gdy będziesz musiał go znaleźć ponownie, musisz przejść do karty Widok u góry, kliknąć Odkryj i wybrać nazwę arkusza z listy, która się pojawi.

W menu zakładki Widok u góry znajduje się również przycisk Ukryj . Co się stanie, gdy to klikniesz? Ukrywa cały skoroszyt , którego używasz. Wygląda na to, że plik został zamknięty, ale program Excel nadal działa. Gdy zamkniesz program, zapyta, czy chcesz zapisać zmiany w ukrytym skoroszycie. Gdy przejdziesz do otwarcia pliku, program Excel wyświetli coś, co wydaje się być pustym skoroszytem — do momentu ponownego kliknięcia przycisku Odkryj.

Kiedy odkryjesz cały skoroszyt, prawdopodobnie zobaczysz listę skoroszytów, o których nie wiedziałeś, że ukryłeś: plik Personal.XLSB. W rzeczywistości jest to osobisty skoroszyt utworzony dla Ciebie w programie Excel; jest otwierany jako ukryty skoroszyt przy każdym uruchomieniu programu Excel. Powód, aby go używać? Makra .

Gdy tworzysz makro, nie działa ono domyślnie w każdym arkuszu kalkulacyjnym, który tworzysz (tak jak w programie Microsoft Word) — makro jest powiązane ze skoroszytem, ​​w którym zostało utworzone. Jeśli jednak przechowujesz makro w Personal. XLSB, będzie dostępny przez cały czas we wszystkich plikach arkuszy kalkulacyjnych.

Sztuczka polega na tym, że podczas rejestrowania makra w polu „Przechowuj makro w” wybierz „Skoroszyt makr osobistych”. (Nagraj makro, włączając kartę Deweloperzy — przejdź do karty Plik , wybierz Opcje , kliknij Dostosuj Wstążkę , a następnie w polu Karty główne zaznacz Deweloperzy , kliknij OK .)

Całe książki zostały poświęcone tabelom przestawnym. Są to podsumowania gigantycznego zbioru danych, które znacznie ułatwiają analizowanie informacji na podstawie punktów odniesienia. Na przykład, jeśli masz cały zestaw ocen wszystkich uczniów ze wszystkich testów z całego roku, tabela przestawna może pomóc Ci zawęzić zakres do jednego ucznia na jeden miesiąc. Każdy, kto ma duże zbiory danych, powinien się nimi bawić (najpierw utwórz kopię oryginalnych danych do zabawy).

Aby je utworzyć, sprawdź, czy wszystkie kolumny i wiersze są zatytułowane tak, jak powinny, a następnie wybierz opcję Tabela przestawna na karcie Wstawianie . Co więcej, wypróbuj opcję Polecane tabele przestawne , aby sprawdzić, czy program Excel może wybrać dla Ciebie odpowiedni rodzaj. Lub wypróbuj wykres przestawny , który tworzy tabelę przestawną z dołączonym wykresem, aby ułatwić zrozumienie.

Patrzysz na ogromną ilość danych i zastanawiasz się, gdzie są najważniejsze? Kto ma najwyższy (lub najniższy) wynik, jaka jest pierwsza piątka itd.? Formatowanie warunkowe programu Excel zrobi wszystko, od umieszczenia obramowania wokół podświetleń po kodowanie kolorami całej tabeli. Zbuduje nawet wykres w każdej komórce, dzięki czemu możesz szybko zwizualizować górną i dolną część zakresu liczb. (Powyżej najwyższe liczby są zaznaczone na zielono, najniższe na czerwono, z widmem pomiędzy.) Użyj podmenu Reguły wyróżnionych komórek, aby utworzyć więcej reguł wyszukiwania rzeczy, takich jak tekst zawierający określony ciąg znaków słowa, powtarzające się daty, zduplikowane wartości itp. Dostępna jest nawet opcja większa niż/mniejsza niż, dzięki czemu można porównać zmiany liczby.

Tworzysz arkusz kalkulacyjny, z którego mogą korzystać inni? Jeśli chcesz utworzyć rozwijane menu wyborów do użycia w poszczególnych komórkach (aby nie mogły tego schrzanić!), to jest to łatwe. Zaznacz komórkę, przejdź do karty Dane i kliknij Sprawdzanie danych . W sekcji „Zezwól:” wybierz „ Lista ”. Następnie w polu „Źródło:” wpisz listę , z przecinkami między opcjami. Możesz też kliknąć przycisk obok pola Źródło i wrócić do tego samego arkusza, aby wybrać serię danych — jest to najlepszy sposób obsługi dużych list. Możesz później ukryć te dane, nadal będą działać. Sprawdzanie poprawności danych to również dobry sposób na ograniczenie wprowadzanych danych — na przykład podaj zakres dat, a użytkownicy nie mogą wprowadzać żadnych dat przed lub po tym, co określisz. Możesz nawet utworzyć komunikat o błędzie, który zobaczą.

Excel bardzo ułatwia zrobienie zrzutu ekranu dowolnego innego otwartego programu na pulpicie i wstawienie go do arkusza roboczego. Po prostu przejdź do zakładki Wstaw , wybierz Zrzut ekranu , a otrzymasz rozwijane menu z miniaturą wszystkich otwartych programów. Wybierz jeden, aby wstawić obraz w pełnym rozmiarze. Zmień rozmiar, jak chcesz.

Trzydzieści pięć lat temu myśl o umieszczeniu danych z programu Excel w programie Word lub PowerPoint była oszałamiająca w świecie pakietów biurowych. Dziś nic do tego nie ma. Niezależnie od tego, czy pobierasz komórki danych, czy pełnowymiarowy wykres graficzny, skopiuj i wklej do innego programu. Należy pamiętać, że jest to proces łączenia i osadzania — jeśli zmienisz dane w arkuszu kalkulacyjnym, zmienią się one również w Word DOC lub PowerPoint PPT. Jeśli tego nie chcesz, wklej jako grafikę. Użyj do tego własnego narzędzia Worda do wklejania. Lub, pobierając go z programu Excel, przejdź do karty Strona główna u góry, wybierz menu Kopiuj i użyj opcji Kopiuj jako obraz . Następnie możesz wkleić grafikę do dowolnego programu.

Pisząc formułę, odwołujesz się do komórek według ich pozycji, na przykład A1. Jeśli skopiujesz formułę i wkleisz ją w następnej komórce w dół, program Excel przesunie tę komórkę, do której istnieje odwołanie, tak aby zamiast tego powiedział A2. Aby zapobiec przesuwaniu, użyj znaku dolara ($). Wpisz $A1 i wytnij go i wklej do nowej komórki, na przykład, co zapobiega przesunięciu kolumny (A); A$1 zapobiega przesunięciu wiersza (1), a $A$1 zapobiega zmianie przesunięcia w dowolnym kierunku podczas kopiowania formuły.

Jest to przydatne, gdy masz pojedynczą komórkę do użycia w całej masie formuł. Załóżmy, że chcesz podzielić wszystko przez 100. Możesz użyć wzoru =(A1/100), ale oznacza to, że nie możesz łatwo zmienić 100 na całej tablicy. Umieść 100 w komórce B1 i użyj =(A1/B1) — ale po wycięciu i wklejeniu zmieni się na =(A2/B2), następnie =(A3/B3) itd. $ naprawia to: =(A1/$B$1) można wyciąć i wkleić w wierszu, ale odwołanie do $B$1 nigdy się nie zmienia. Następnie możesz zmienić wartość 100 w komórce, aby poeksperymentować z innymi zmianami.

Jeśli nie wiesz dokładnie, jakie informacje chcesz zastosować do danych w programie Excel, wypróbuj menu Szybka analiza, aby szybko przeglądać opcje. Wybierz dane i kliknij pole Szybka analiza , które pojawi się w prawym dolnym rogu. Pojawi się menu z opcjami szybkiego stosowania formatowania warunkowego, tworzenia wykresów, obsługi sum, wyświetlania wykresów przebiegu w czasie i nie tylko.

Excel, jak każde świetne oprogramowanie, ma wiele doskonałych skrótów klawiaturowych. Oto niektóre z najlepszych.

Ctrl+; — Wstawia dzisiejszą datę.
Ctrl+Shift+: — Wstawia aktualny czas (dwukropek jest tym, co odczytuje zegar, np. 12:00).
Ctrl+Shift+# — zmienia format daty.
Ctrl+5 — stosuje przekreślenie tekstu w komórce.
Ctrl+0 — Ukrywa bieżącą kolumnę.
Ctrl+9 — ukrywa bieżący wiersz.
Ctrl+F6 — Przełącza między otwartymi skoroszytami (tzn. otwieraj pliki Excela w różnych oknach).
Ctrl+` — to znak akcentu w górę o klawisz 1. Ta kombinacja przełącza widok w arkuszu, aby wyświetlić wszystkie formuły.
Ctrl+PageUp lub PageDown — szybkie przechodzenie między arkuszami w aktualnie otwartym skoroszycie.
F2 — Rozpocznij edycję aktualnie zaznaczonej komórki (znacznie szybciej niż dwukrotne kliknięcie).
Shift+F10 — otwiera menu prawego przycisku myszy dla komórki, w której się znajdujesz.

Masz liczby w arkuszu kalkulacyjnym, na którym chcesz wykonać szybkie obliczenia, bez kłopotów z przechodzeniem do nowej komórki i tworzeniem formuły SUMA do pracy? Excel oferuje teraz szybki sposób na zrobienie tego. Kliknij pierwszą komórkę, przytrzymaj klawisz Ctrl i kliknij drugą komórkę. Spójrz na pasek stanu na dole, a zobaczysz obliczoną dla Ciebie sumę komórek.

Trzymaj palec na Ctrl i kliknij tyle komórek, ile chcesz, pasek stanu będzie nadal pokazywał sumę dla wszystkich komórek. (Kliknij komórkę z literami/słowami jako zawartością, zostanie zignorowana.) Jeszcze lepiej, kliknij prawym przyciskiem myszy pasek stanu, aby uzyskać menu Dostosuj pasek stanu i możesz dodać inne elementy, które można szybko obliczyć w ten sposób, takie jak jako średnią lub liczbę klikniętych komórek (lub liczbę liczbową, czyli liczbę klikniętych komórek, które faktycznie mają liczby).

Praca z ogromnym zestawem danych w arkuszu kalkulacyjnym może być trudna, zwłaszcza gdy przewijasz w górę/w dół lub w lewo/w prawo, a śledzenie wierszy i kolumn może być trudne. Jest na to prosta sztuczka, jeśli masz wiersz lub kolumnę nagłówka, gdzie pierwszy wiersz/kolumna ma deskryptor. Blokujesz go, aby podczas przewijania ten wiersz i/lub kolumna (lub wiele wierszy i/lub kolumn) się nie poruszały.

Przejdź do zakładki Widok i poszukaj zamrożonych okienek . Możesz łatwo po prostu zamrozić górny wiersz (wybierz Freeze Top Row ) lub pierwszą kolumnę (wybierz Freeze First Column ). Możesz zrobić jedno i drugie naraz, klikając komórkę w B2 i po prostu wybierając Zablokuj okienka . Tutaj jest fajnie — wybierz dowolną inną komórkę, a także zamroź wszystkie panele powyżej i po lewej stronie. Wybierz na przykład komórkę C3, a dwa wiersze powyżej i dwie kolumny po lewej stronie nie zostaną przewinięte. Możesz to zobaczyć na powyższym zrzucie ekranu, oznaczonym przyciemnionymi liniami siatki.

Jeśli chcesz pozbyć się zamrożenia, możesz po prostu wybrać z menu opcję Odblokuj okienka .

Arkusze kalkulacyjne mogą być ogromne i być może będziesz musiał wchodzić w interakcje z różnymi obszarami arkusza kalkulacyjnego w tym samym czasie, na przykład wycinać i wklejać informacje od góry do dołu w kółko. Jeśli to setki tysięcy komórek, przewijanie, które zajmie, może wywołać mdłości. Możesz też po prostu otworzyć drugie okno na pulpicie z widokiem dokładnie tego samego arkusza kalkulacyjnego. To jest łatwe. Na karcie Widok kliknij Nowe okno . Możesz także kliknąć Rozmieść wszystko , aby uporządkować je na ekranie w sposób, który Ci odpowiada. Możesz je zobaczyć ułożone poziomo powyżej. Następnie wpisz coś w komórce w jednym oknie, zobaczysz to w drugim oknie. Ta sztuczka jest szczególnie przydatna, jeśli masz dwa monitory.

Myślisz, że wiesz wszystko o Microsoft Word? Oto kilka ukrytych sztuczek i hacków oszczędzających czas, które musisz wiedzieć o aplikacji do edycji tekstu Redmond.