19 łatwych porad, sztuczek i skrótów programu Excel

Czasami program Excel wydaje się zbyt piękny, aby mógł być prawdziwy. Wszystko, co muszę zrobić, to wprowadzić formułę, a prawie wszystko, co kiedykolwiek musiałbym zrobić ręcznie, może zostać zrobione automatycznie.

Chcesz scalić dwa arkusze z podobnymi danymi? Excel to potrafi.

Potrzebujesz prostej matematyki? Excel to potrafi.

Chcesz połączyć informacje w wielu komórkach? Excel to potrafi.

W tym poście omówię najlepsze wskazówki, triki i skróty, których możesz teraz użyć, aby przenieść swoją grę Excel na wyższy poziom. Nie jest wymagana zaawansowana znajomość Excela.

Co to jest Excel?

Microsoft Excel to potężne oprogramowanie do wizualizacji i analizy danych, które wykorzystuje arkusze kalkulacyjne do przechowywania, organizowania i śledzenia zestawów danych za pomocą formuł i funkcji. Z Excela korzystają marketerzy, księgowi, analitycy danych i inni profesjonaliści. Jest częścią pakietu produktów Microsoft Office. Alternatywy obejmują Arkusze Google i Liczby.

Znajdź więcej alternatyw dla Excela tutaj.

Excel jest używany głównie do tworzenia dokumentów finansowych ze względu na jego duże moce obliczeniowe. Oprogramowanie to często można znaleźć w biurach rachunkowych i zespołach, ponieważ umożliwia księgowym automatyczne wyświetlanie sum, średnich i sum. Dzięki Excelowi mogą łatwo zrozumieć dane biznesowe.

Chociaż Excel jest znany przede wszystkim jako narzędzie księgowe, profesjonaliści z każdej dziedziny mogą korzystać z jego funkcji i formuł — zwłaszcza marketerzy — ponieważ można go używać do śledzenia dowolnego rodzaju danych. Eliminuje to konieczność spędzania wielu godzin na liczeniu komórek lub kopiowaniu i wklejaniu liczb wydajności. Excel zwykle ma skrót lub szybką poprawkę, która przyspiesza proces.

Możesz również pobrać poniższe szablony Excel dla wszystkich Twoich potrzeb marketingowych.

Po pobraniu szablonów czas zacząć korzystać z oprogramowania. Omówmy najpierw podstawy.

Podstawy programu Excel

Jeśli dopiero zaczynasz pracę z programem Excel, zalecamy zapoznanie się z kilkoma podstawowymi poleceniami. Są to takie rzeczy jak:

• Tworzenie nowego arkusza kalkulacyjnego od podstaw.
• Wykonywanie podstawowych obliczeń, takich jak dodawanie, odejmowanie, mnożenie i dzielenie.
• Pisanie i formatowanie tekstu i tytułów kolumn.
• Korzystanie z funkcji automatycznego wypełniania programu Excel.
• Dodawanie lub usuwanie pojedynczych kolumn, wierszy i arkuszy kalkulacyjnych. (Poniżej dowiemy się, jak dodawać elementy, takie jak wiele kolumn i wierszy).
• Utrzymywanie widocznych tytułów kolumn i wierszy, gdy przewijasz je w arkuszu kalkulacyjnym, dzięki czemu wiesz, jakie dane wypełniasz, gdy przechodzisz dalej w dół dokumentu.
• Sortowanie danych w kolejności alfabetycznej.

Przyjrzyjmy się bliżej kilku z nich.

Na przykład, dlaczego autouzupełnianie ma znaczenie?

Jeśli masz podstawową wiedzę o programie Excel, prawdopodobnie znasz już tę szybką sztuczkę. Ale aby pokryć nasze podstawy, pozwól, że pokażę ci chwałę autouzupełniania. Pozwala to szybko wypełnić sąsiednie komórki kilkoma typami danych, w tym wartościami, seriami i formułami.

Istnieje wiele sposobów wdrożenia tej funkcji, ale uchwyt wypełniania jest jednym z najłatwiejszych. Wybierz komórki, które mają być źródłem, zlokalizuj uchwyt wypełniania w prawym dolnym rogu komórki i przeciągnij uchwyt wypełniania, aby zakryć komórki, które chcesz wypełnić, lub po prostu kliknij dwukrotnie:

Podobnie sortowanie jest ważną funkcją, o której warto wiedzieć podczas organizowania danych w programie Excel.

Czasami możesz mieć listę danych, która nie ma żadnej organizacji. Może wyeksportowałeś listę swoich kontaktów marketingowych lub postów na blogu. Niezależnie od przypadku, Funkcja sortowania w programie Excel pomoże Ci uporządkować dowolną listę w kolejności alfabetycznej.

Kliknij dane w kolumnie, którą chcesz posortować. Następnie kliknij kartę „Dane” na pasku narzędzi i poszukaj opcji „Sortuj” po lewej stronie. Jeśli „A” znajduje się nad „Z”, możesz po prostu kliknąć ten przycisk raz. Jeśli „Z” znajduje się nad „A”, kliknij przycisk dwukrotnie. Gdy „A” znajduje się na górze „Z”, oznacza to, że lista zostanie posortowana w kolejności alfabetycznej. Jeśli jednak „Z” znajduje się na górze „A”, oznacza to, że lista zostanie posortowana w odwrotnej kolejności alfabetycznej.

Przyjrzyjmy się dalej podstawom programu Excel (wraz z zaawansowanymi funkcjami).

Jak korzystać z programu Excel

Aby korzystać z programu Excel, wystarczy wprowadzić dane do wierszy i kolumn. A następnie użyjesz formuł i funkcji, aby przekształcić te dane w wnioski.

Omówimy najlepsze formuły i funkcje, które musisz znać. Ale najpierw przyjrzyjmy się rodzajom dokumentów, które możesz tworzyć za pomocą oprogramowania. W ten sposób masz nadrzędną wiedzę na temat korzystania z programu Excel na co dzień.

Dokumenty, które możesz tworzyć w Excelu

Nie wiesz, jak właściwie możesz używać programu Excel w swoim zespole? Oto lista dokumentów, które możesz utworzyć:

• Oświadczenia o dochodach: Możesz użyć arkusza kalkulacyjnego Excel, aby śledzić sprzedaż i kondycję finansową firmy.
• Bilanse: Bilanse należą do najpopularniejszych typów dokumentów, które można tworzyć w programie Excel. Pozwala uzyskać całościowy obraz sytuacji finansowej firmy.
• Kalendarz: Możesz łatwo utworzyć miesięczny kalendarz w arkuszu kalkulacyjnym, aby śledzić wydarzenia lub inne informacje ważne dla daty.

Oto kilka dokumentów, które możesz stworzyć specjalnie dla marketerów.

To tylko niewielka próbka typów dokumentów marketingowych i biznesowych, które możesz tworzyć w programie Excel. Stworzyliśmy obszerną listę szablonów programu Excel, których możesz teraz używać do celów marketingowych, fakturowania, zarządzania projektami, budżetowania i nie tylko.

W duchu wydajniejszej pracy i unikania żmudnej, ręcznej pracy, oto kilka formuł i funkcji Excela, które musisz znać.

Formuły Excela

Łatwo jest przytłoczyć szeroką gamą formuł programu Excel, których możesz użyć, aby nadać sens swoim danym. Jeśli dopiero zaczynasz korzystać z programu Excel, możesz polegać na poniższych formułach, aby wykonać niektóre złożone funkcje — bez zwiększania złożoności ścieżki szkoleniowej.

• Znak równości: przed utworzeniem jakiejkolwiek formuły musisz wpisać znak równości (=) w komórce, w której ma się pojawić wynik.
• Dodawanie : aby dodać wartości dwóch lub więcej komórek, użyj znaku + . Przykład: =C5+D3 .
• Odejmowanie : aby odjąć wartości dwóch lub więcej komórek, użyj znaku – . Przykład: =C5-D3 .
• Mnożenie : aby pomnożyć wartości dwóch lub więcej komórek, użyj znaku * . Przykład: =C5*D3 .
• Podział : Aby podzielić wartości dwóch lub więcej komórek, użyj znaku / . Przykład: =C5/D3 .

Łącząc je wszystkie razem, możesz utworzyć formułę, która dodaje, odejmuje, mnoży i dzieli wszystko w jednej komórce. Przykład: =(C5-D3)/((A5+B6)*3) .

W przypadku bardziej złożonych formuł należy używać nawiasów wokół wyrażeń, aby uniknąć przypadkowego użycia kolejności operacji PEMDAS. Pamiętaj, że w swoich formułach możesz używać zwykłych liczb.

Funkcje Excela

Funkcje programu Excel automatyzują niektóre zadania, których można użyć w typowej formule. Na przykład zamiast używać znaku + do dodawania zakresu komórek, użyjesz funkcji SUMA. Przyjrzyjmy się jeszcze kilku funkcjom, które pomogą zautomatyzować obliczenia i zadania.

• SUMA: Funkcja SUMA automatycznie dodaje zakres komórek lub liczb. Aby uzupełnić sumę, należy wprowadzić komórkę początkową i komórkę końcową z dwukropkiem pomiędzy. Oto, jak to wygląda: SUM(Cell1:Cell2) . Przykład: =SUMA(C5:C30) .
• ŚREDNIA: Funkcja ŚREDNIA uśrednia wartości z zakresu komórek. Składnia jest taka sama jak w przypadku funkcji SUMA: ŚREDNIA(Komórka1:Komórka2). Przykład: =ŚREDNIA(C5:C30) .
• JEŻELI: Funkcja JEŻELI umożliwia zwrócenie wartości na podstawie testu logicznego. Składnia jest następująca: JEŻELI(test_logiczny, wartość_jeśli_prawda, [wartość_jeśli_fałsz]) . Przykład: =JEŻELI(A2>B2,”Przekroczenie budżetu”,”OK”) .
• WYSZUKAJ.PIONOWO: funkcja WYSZUKAJ.PIONOWO pomaga wyszukać wszystko w wierszach arkusza. Składnia jest następująca: WYSZUKAJ.PIONOWO(wartość wyszukiwania, tablica tabeli, numer kolumny, dopasowanie przybliżone (PRAWDA) lub dopasowanie dokładne (FAŁSZ)) . Przykład: =WYSZUKAJ.PIONOWO([@Prawnik],tbl_Prawnicy,4,FAŁSZ).
• INDEKS: Funkcja INDEKS zwraca wartość z zakresu. Składnia jest następująca: INDEX(tablica, numer_wiersza, [numer_kolumny]) .
• PODAJ.POZYCJĘ: Funkcja PODAJ.POZYCJĘ wyszukuje określony element w zakresie komórek i zwraca pozycję tego elementu. Może być używany w połączeniu z funkcją INDEX. Składnia to: PODAJ.POZYCJĘ(wyszukiwana_wartość, szukana_tablica, [typ_pasowania]) .
• LICZ.JEŻELI: funkcja LICZ.JEŻELI zwraca liczbę komórek, które spełniają określone kryteria lub mają określoną wartość. Składnia to: LICZ.JEŻELI(zakres; kryteria). Przykład: =LICZ.JEŻELI(A2:A5”Londyn”).

OK, gotowy, aby przejść do sedna sprawy? Weźmy się za to. (I do wszystkich fanów Harry'ego Pottera… z góry zapraszamy.)

Uwaga : pliki GIF i wizualizacje pochodzą z poprzedniej wersji programu Excel. W stosownych przypadkach kopia została zaktualizowana, aby zapewnić instrukcje dla użytkowników zarówno nowszych, jak i starszych wersji programu Excel.

1. Użyj tabel przestawnych, aby rozpoznać i zrozumieć dane.

Tabele przestawne służą do reorganizacji danych w arkuszu kalkulacyjnym. Nie zmienią danych, które masz, ale mogą sumować wartości i porównywać różne informacje w arkuszu kalkulacyjnym, w zależności od tego, co chcesz, aby zrobili.

Spójrzmy na przykład. Powiedzmy, że chcę się przyjrzeć, ile osób jest w każdym domu w Hogwarcie. Być może myślisz, że nie mam za dużo danych, ale przy dłuższych zestawach danych przyda się to.

Aby utworzyć tabelę przestawną, idę do Dane > Tabela przestawna . Jeśli używasz najnowszej wersji programu Excel, przejdź do Wstaw > Tabela przestawna . Excel automatycznie wypełni Twoją tabelę przestawną, ale zawsze możesz zmienić kolejność danych. Następnie masz cztery opcje do wyboru.

• Filtr raportów : umożliwia przeglądanie tylko określonych wierszy w zestawie danych. Na przykład, gdybym chciał utworzyć filtr według domu, mógłbym wybrać tylko uczniów z Gryffindoru, a nie wszystkich uczniów.
• Etykiety kolumn : będą to Twoje nagłówki w zbiorze danych.
• Etykiety wierszy : mogą to być Twoje wiersze w zbiorze danych. Zarówno etykiety wiersza, jak i kolumny mogą zawierać dane z kolumn (np. Imię można przeciągnąć do etykiety wiersza lub kolumny — zależy to tylko od tego, jak chcesz wyświetlić dane).
• Wartość : w tej sekcji możesz inaczej spojrzeć na swoje dane. Zamiast pobierać dowolną wartość liczbową, możesz sumować, liczyć, uśredniać, maksymalizować, minimalizować, liczyć liczby lub wykonywać kilka innych manipulacji danymi. W rzeczywistości, domyślnie, gdy przeciągasz pole do wartości, zawsze się liczy.

Ponieważ chcę policzyć liczbę uczniów w każdym domu, przejdę do konstruktora tabel przestawnych i przeciągnę kolumnę Dom do etykiet wierszy i wartości. To zsumuje liczbę studentów związanych z każdym domem.

2. Dodaj więcej niż jeden wiersz lub kolumnę.

Gdy bawisz się swoimi danymi, może się okazać, że ciągle musisz dodawać więcej wierszy i kolumn. Czasami może być konieczne dodanie setek wierszy. Robienie tego jeden po drugim byłoby bardzo żmudne. Na szczęście zawsze jest prostszy sposób.

Aby dodać wiele wierszy lub kolumn w arkuszu kalkulacyjnym, zaznacz tę samą liczbę istniejących wierszy lub kolumn, które chcesz dodać. Następnie kliknij prawym przyciskiem myszy i wybierz „Wstaw”.

W poniższym przykładzie chcę dodać dodatkowe trzy wiersze. Zaznaczając trzy wiersze, a następnie klikając Wstaw, mogę szybko i łatwo dodać kolejne trzy puste wiersze do mojego arkusza kalkulacyjnego.

3. Użyj filtrów, aby uprościć dane.

Kiedy patrzysz na bardzo duże zestawy danych, zwykle nie musisz jednocześnie patrzeć na każdy wiersz. Czasami chcesz spojrzeć tylko na dane, które pasują do określonych kryteriów.

Tu właśnie wkraczają filtry.

Filtry pozwalają zmniejszyć dane, aby jednocześnie przeglądać tylko określone wiersze. W programie Excel do każdej kolumny w danych można dodać filtr — i stamtąd możesz wybrać komórki, które chcesz wyświetlić jednocześnie.

Spójrzmy na poniższy przykład. Dodaj filtr, klikając kartę Dane i wybierając „Filtr”. Klikając strzałkę obok nagłówków kolumn, możesz wybrać, czy chcesz uporządkować dane w porządku rosnącym, czy malejącym, a także konkretne wiersze, które chcesz wyświetlić.

W moim przykładzie z Harrym Potterem, powiedzmy, że chcę zobaczyć tylko uczniów w Gryffindorze. Wybierając filtr Gryffindor, pozostałe wiersze znikają.

Porada dla profesjonalistów: Skopiuj i wklej wartości z arkusza kalkulacyjnego, gdy filtr jest włączony, aby przeprowadzić dodatkową analizę w innym arkuszu kalkulacyjnym.

4. Usuń zduplikowane punkty danych lub zestawy.

Większe zbiory danych mają zwykle zduplikowaną zawartość. Możesz mieć listę wielu kontaktów w firmie i chcesz zobaczyć tylko liczbę posiadanych firm. W takich sytuacjach bardzo przydatne jest usuwanie duplikatów.

Aby usunąć duplikaty, zaznacz wiersz lub kolumnę, których duplikaty chcesz usunąć. Następnie przejdź do zakładki Dane i wybierz „Usuń duplikaty” (znajduje się pod nagłówkiem Narzędzia w starszej wersji Excela). Pojawi się wyskakujące okienko, aby potwierdzić, z którymi danymi chcesz pracować. Wybierz „Usuń duplikaty” i gotowe.

Możesz również użyć tej funkcji, aby usunąć cały wiersz na podstawie zduplikowanej wartości kolumny. Jeśli więc masz trzy wiersze z informacjami o Harrym Potterze i musisz zobaczyć tylko jeden, możesz wybrać cały zestaw danych, a następnie usunąć duplikaty na podstawie wiadomości e-mail. Twoja lista wynikowa będzie miała tylko unikalne nazwy bez żadnych duplikatów.

5. Transponuj wiersze do kolumn.

Gdy w arkuszu kalkulacyjnym masz wiersze danych, możesz zdecydować, że chcesz przekształcić elementy w jednym z tych wierszy w kolumny (lub odwrotnie). Kopiowanie i wklejanie każdego pojedynczego nagłówka zajęłoby dużo czasu — ale funkcja transpozycji pozwala po prostu przenieść dane wiersza do kolumn lub odwrotnie.

Zacznij od podświetlenia kolumny, którą chcesz przetransponować na wiersze. Kliknij go prawym przyciskiem myszy, a następnie wybierz "Kopiuj". Następnie zaznacz komórki w arkuszu kalkulacyjnym, od których ma się zaczynać pierwszy wiersz lub kolumna. Kliknij komórkę prawym przyciskiem myszy, a następnie wybierz "Wklej specjalnie". Pojawi się moduł — na dole zobaczysz opcję transpozycji. Zaznacz to pole i wybierz OK. Twoja kolumna zostanie teraz przeniesiona do wiersza lub odwrotnie.

W nowszych wersjach programu Excel zamiast wyskakującego okienka pojawi się menu rozwijane.

6. Podziel informacje tekstowe między kolumny.

Co zrobić, jeśli chcesz podzielić informacje znajdujące się w jednej komórce na dwie różne komórki? Na przykład, być może chcesz pobrać nazwę czyjejś firmy z jej adresu e-mail. A może chcesz podzielić czyjeś imię i nazwisko na imię i nazwisko w szablonach e-mail marketingu.

Dzięki Excelowi oba są możliwe. Najpierw zaznacz kolumnę, którą chcesz podzielić. Następnie przejdź do zakładki Dane i wybierz „Tekst do kolumn”. Pojawi się moduł z dodatkowymi informacjami.

Najpierw musisz wybrać „Rozdzielany” lub „Stała szerokość”.

• „Rozdzielany” oznacza, że ​​chcesz podzielić kolumnę na podstawie znaków, takich jak przecinki, spacje lub tabulatory.
• „Stała szerokość” oznacza, że ​​chcesz wybrać dokładną lokalizację we wszystkich kolumnach, w których ma nastąpić podział.

W poniższym przykładzie wybierzmy „Rozdzielone”, aby móc rozdzielić pełne imię i nazwisko na imię i nazwisko.

Następnie nadszedł czas, aby wybrać ograniczniki. Może to być tabulator, średnik, przecinek, spacja lub coś innego. („Coś innego” może być znakiem „@” używanym na przykład w adresie e-mail). W naszym przykładzie wybierzmy spację. Excel pokaże Ci wtedy podgląd tego, jak będą wyglądać Twoje nowe kolumny.

Gdy jesteś zadowolony z podglądu, naciśnij „Dalej”. Ta strona pozwoli Ci wybrać Zaawansowane formaty, jeśli zdecydujesz się. Kiedy skończysz, kliknij „Zakończ”.

7. Używaj wzorów do prostych obliczeń.

Oprócz wykonywania dość skomplikowanych obliczeń, Excel może pomóc w wykonywaniu prostych operacji arytmetycznych, takich jak dodawanie, odejmowanie, mnożenie lub dzielenie dowolnych danych.

• Aby dodać, użyj znaku +.
• Aby odjąć, użyj znaku –.
• Aby pomnożyć, użyj znaku *.
• Aby podzielić, użyj znaku /.

Możesz także użyć nawiasów, aby upewnić się, że pewne obliczenia są wykonywane jako pierwsze. W poniższym przykładzie (10+10*10), druga i trzecia 10 zostały pomnożone przez siebie przed dodaniem kolejnych 10. Jednak gdybyśmy to zrobili (10+10)*10, pierwsza i druga 10 zostałyby dodane razem jako pierwsze .

8. Uzyskaj średnią liczb w swoich komórkach.

Jeśli chcesz uzyskać średnią zestawu liczb, możesz użyć formuły =ŚREDNIA(Komórka1:Komórka2) . Jeśli chcesz zsumować kolumnę liczb, możesz użyć formuły =SUMA(Komórka1:Komórka2) .

9. Użyj formatowania warunkowego, aby komórki automatycznie zmieniały kolor na podstawie danych.

Formatowanie warunkowe umożliwia zmianę koloru komórki na podstawie informacji w komórce. Na przykład, jeśli chcesz oznaczyć pewne liczby, które są powyżej średniej lub w górnych 10% danych w arkuszu kalkulacyjnym, możesz to zrobić. Jeśli chcesz pokolorować wspólne kody między różnymi wierszami w programie Excel, możesz to zrobić. Pomoże Ci to szybko zobaczyć informacje, które są dla Ciebie ważne.

Aby rozpocząć, zaznacz grupę komórek, w których chcesz użyć formatowania warunkowego. Następnie wybierz "Formatowanie warunkowe" z menu głównego i wybierz swoją logikę z menu. (Możesz także utworzyć własną regułę, jeśli chcesz coś innego). Pojawi się okno z prośbą o podanie dodatkowych informacji o regule formatowania. Po zakończeniu wybierz „OK”, a wyniki powinny pojawić się automatycznie.

10. Użyj formuły IF Excel, aby zautomatyzować niektóre funkcje programu Excel.

Czasami nie chcemy liczyć, ile razy pojawia się wartość. Zamiast tego chcemy wprowadzić inne informacje do komórki, jeśli istnieje odpowiadająca komórka z tymi informacjami.

Na przykład w poniższej sytuacji chcę przyznać dziesięć punktów każdemu, kto należy do domu Gryffindoru. Zamiast ręcznie wpisywać dziesiątki obok nazwiska każdego ucznia Gryffindoru, mogę użyć formuły IF Excel, aby powiedzieć, że jeśli uczeń jest w Gryffindorze, powinien otrzymać dziesięć punktów.

Formuła to: JEŻELI(test_logiczny, wartość_jeśli_prawda, [wartość_jeśli_fałsz])

Przykład pokazany poniżej: =JEŻELI(D2=”Gryffindor”,”10″”,0″)

Ogólnie rzecz biorąc, formuła to JEŻELI(Test logiczny, wartość prawda, wartość fałsz). Przyjrzyjmy się każdej z tych zmiennych.

• Test_logiczny : Test logiczny to część instrukcji „JEŻELI”. W tym przypadku logika to D2 = „Gryffindor”, ponieważ chcemy się upewnić, że komórka odpowiadająca uczniowi mówi „Gryffindor”. Upewnij się, że Gryffindor wstawiasz tutaj w cudzysłowie.
• Value_if_True : To jest to, co chcemy, aby komórka pokazywała, jeśli wartość jest prawdziwa. W tym przypadku chcemy, aby komórka wyświetlała „10”, aby wskazać, że uczeń otrzymał 10 punktów. Używaj cudzysłowów tylko wtedy, gdy chcesz, aby wynik był tekstem, a nie liczbą.
• Value_if_False : To jest to, co chcemy, aby komórka pokazywała, jeśli wartość jest fałszywa. W tym przypadku dla każdego ucznia spoza Gryffindoru chcemy, aby komórka pokazywała „0”. Używaj cudzysłowów tylko wtedy, gdy chcesz, aby wynik był tekstem, a nie liczbą.

Uwaga : W powyższym przykładzie przyznałem 10 punktów wszystkim w Gryffindor. Gdybym później chciał zsumować całkowitą liczbę punktów, nie byłbym w stanie tego zrobić, ponieważ 10 są w cudzysłowie, dzięki czemu są tekstem, a nie liczbą, którą Excel może zsumować.

Prawdziwa moc funkcji JEŻELI pojawia się, gdy ciągniesz wiele instrukcji JEŻELI razem, lub zagnieździć je. Pozwala to ustawić wiele warunków, uzyskać bardziej szczegółowe wyniki i ostatecznie uporządkować dane w łatwiejsze do zarządzania porcje.

Zakresy to jeden ze sposobów segmentacji danych w celu lepszej analizy. Na przykład możesz kategoryzować dane na wartości mniejsze niż 10, 11 do 50 lub 51 do 100. Oto, jak to wygląda w praktyce:

=JEŻELI(B3<11,„10 lub mniej”,JEŻELI(B3<51,„11 do 50”,JEŻELI(B3<100,„51 do 100”)))

Może to zająć trochę prób i błędów, ale gdy już to opanujesz, formuły IF staną się Twoim nowym najlepszym przyjacielem Excela.

11. Użyj znaków dolara, aby formuła jednej komórki była taka sama, niezależnie od tego, gdzie się ona porusza.

Czy kiedykolwiek widziałeś znak dolara w formule programu Excel? Użyty w formule nie reprezentuje dolara amerykańskiego; zamiast tego upewnia się, że dokładna kolumna i wiersz są takie same, nawet jeśli skopiujesz tę samą formułę w sąsiednich wierszach.

Widzisz, odwołanie do komórki — gdy na przykład odwołujesz się do komórki A5 z komórki C5 — jest domyślnie względne. W takim przypadku w rzeczywistości odnosisz się do komórki, która ma pięć kolumn po lewej (C minus A) iw tym samym wierszu (5). Nazywa się to formułą względną. Gdy skopiujesz formułę względną z jednej komórki do drugiej, dostosuje ona wartości w formule na podstawie miejsca jej przeniesienia. Ale czasami chcemy, aby te wartości pozostały takie same bez względu na to, czy są przesuwane, czy nie — i możemy to zrobić, przekształcając formułę w formułę bezwzględną.

Aby zmienić formułę względną (=A5+C5) na formułę bezwzględną, poprzedzimy wartości wierszy i kolumn znakami dolara, w następujący sposób: (=$A$5+$C$5) . (Dowiedz się więcej na stronie pomocy technicznej pakietu Microsoft Office tutaj.)

12. Użyj funkcji WYSZUKAJ.PIONOWO, aby przeciągnąć dane z jednego obszaru arkusza do drugiego.

Czy kiedykolwiek miałeś dwa zestawy danych w dwóch różnych arkuszach kalkulacyjnych, które chcesz połączyć w jeden arkusz kalkulacyjny?

Na przykład możesz mieć listę nazwisk osób obok ich adresów e-mail w jednym arkuszu kalkulacyjnym, a listę adresów e-mail tych samych osób obok nazw ich firm w drugim — ale chcesz mieć nazwiska, adresy e-mail i nazwy firm tych ludzi do pojawienia się w jednym miejscu.

Muszę często łączyć takie zestawy danych — a kiedy to robię, WYSZUKAJ.PIONOWO jest moją najważniejszą formułą.

Zanim jednak użyjesz formuły, upewnij się, że masz co najmniej jedną kolumnę, która pojawia się identycznie w obu miejscach. Przeszukaj swoje zbiory danych, aby upewnić się, że kolumna danych używana do łączenia informacji jest dokładnie taka sama, bez dodatkowych spacji.

Formuła: = WYSZUKAJ.PIONOWO(wartość wyszukiwania, tablica tabeli, numer kolumny, dopasowanie przybliżone (PRAWDA) lub dopasowanie dokładne (FAŁSZ))

Formuła ze zmiennymi z naszego przykładu poniżej: =WYSZUKAJ.PIONOWO(C2,Arkusz2!A:B,2,FAŁSZ)

W tej formule jest kilka zmiennych. Poniższe informacje są prawdziwe, gdy chcesz połączyć informacje z Arkusza 1 i Arkusza 2 w Arkusz 1.

• Wartość wyszukiwania : jest to identyczna wartość, jaką masz w obu arkuszach kalkulacyjnych. Wybierz pierwszą wartość w pierwszym arkuszu kalkulacyjnym. W poniższym przykładzie oznacza to pierwszy adres e-mail na liście lub komórkę 2 (C2).
• Tablica tabeli : tablica tabeli to zakres kolumn w arkuszu 2, z których zamierzasz pobrać dane, w tym kolumna danych identyczna z wartością wyszukiwania (w naszym przykładzie adresy e-mail) w arkuszu 1, a także kolumna danych, które próbujesz skopiować do Arkusza 1. W naszym przykładzie jest to „Arkusz2!A:B”. „A” oznacza kolumnę A w arkuszu 2, czyli kolumnę w arkuszu 2, w której wymienione są dane identyczne z naszą wartością wyszukiwania (e-mail) w arkuszu 1. „B” oznacza kolumnę B, która zawiera informacje dostępne tylko w arkuszu 2, które chcesz przetłumaczyć na arkusz 1.
• Numer kolumny : informuje program Excel, w której kolumnie znajdują się nowe dane, które chcesz skopiować do arkusza 1. W naszym przykładzie będzie to kolumna, w której znajduje się „Dom”. „Dom” to druga kolumna w naszym zakresie kolumny (tablica tabeli), więc nasz numer kolumny to 2. [ Uwaga : Twój zakres może obejmować więcej niż dwie kolumny. Na przykład, jeśli na Arkuszu 2 są trzy kolumny — E-mail, Wiek i Dom — i nadal chcesz przenieść Dom na Arkusz 1, nadal możesz użyć funkcji WYSZUKAJ.PIONOWO. Wystarczy zmienić „2” na „3”, aby wycofała wartość z trzeciej kolumny: = WYSZUKAJ.PIONOWO(C2:Arkusz2!A:C,3,false).]
• Przybliżone dopasowanie (PRAWDA) lub Dokładne dopasowanie (FAŁSZ) : Użyj FAŁSZ, aby mieć pewność, że pobierasz tylko dokładne dopasowania wartości. Jeśli użyjesz TRUE, funkcja pobierze przybliżone dopasowania.

W poniższym przykładzie arkusze Arkusz 1 i Arkusz 2 zawierają listy opisujące różne informacje o tych samych osobach, a wspólnym wątkiem między nimi są ich adresy e-mail. Załóżmy, że chcemy połączyć oba zestawy danych, aby wszystkie informacje o domu z arkusza 2 zostały przeniesione na arkusz 1.

Więc kiedy wpiszemy formułę = WYSZUKAJ.PIONOWO(C2,Arkusz2!A:B,2,FAŁSZ) , przenosimy wszystkie dane domu do Arkusza 1.

Pamiętaj, że WYSZUKAJ.PIONOWO wycofa tylko wartości z drugiego arkusza, które znajdują się po prawej stronie kolumny zawierającej identyczne dane. Może to prowadzić do pewnych ograniczeń, dlatego niektórzy ludzie wolą zamiast tego używać funkcji INDEKS i PODAJ.POZYCJĘ.

13. Użyj formuł INDEKS i PODAJ.POZYCJĘ, aby pobrać dane z poziomych kolumn.

Podobnie jak WYSZUKAJ.PIONOWO, funkcje INDEKS i PODAJ.POZYCJĘ pobierają dane z innego zestawu danych do jednej centralnej lokalizacji. Oto główne różnice:

• WYSZUKAJ.PIONOWO to znacznie prostsza formuła. Jeśli pracujesz z dużymi zestawami danych, które wymagałyby tysięcy wyszukiwań, użycie funkcji INDEKS i DOPASUJ znacznie skróci czas ładowania w programie Excel.
• Formuły INDEKS i PODAJ.POZYCJĘ działają od prawej do lewej, podczas gdy formuły WYSZUKAJ.PIONOWO działają tylko jako wyszukiwanie od lewej do prawej. Innymi słowy, jeśli chcesz wykonać wyszukiwanie, które ma kolumnę wyszukiwania po prawej stronie kolumny wyników, musisz zmienić kolejność tych kolumn, aby wykonać WYSZUKAJ.PIONOWO. Może to być uciążliwe w przypadku dużych zbiorów danych i/lub prowadzić do błędów.

Jeśli więc chcę połączyć informacje z Arkusza 1 i Arkusza 2 w Arkusz 1, ale wartości kolumn w Arkuszach 1 i 2 nie są takie same, to aby wykonać WYSZUKAJ.PIONOWO, musiałbym przełączać się między kolumnami. W takim przypadku wybrałbym zamiast tego opcję INDEKS i DOPASUJ.

Spójrzmy na przykład. Załóżmy, że Arkusz 1 zawiera listę nazwisk osób i ich adresów e-mail w Hogwarcie, a Arkusz 2 zawiera listę adresów e-mail osób i Patronusa, którego ma każdy uczeń. (Dla fanów, którzy nie są Harrym Potterem, każda wiedźma lub czarodziej ma powiązanego z nią opiekuna zwierząt zwanego „Patronusem”.) Informacje, które znajdują się w obu arkuszach, to kolumna zawierająca adresy e-mail, ale ta kolumna adresów e-mail jest w różnych numerach kolumn na każdym arkuszu. Używałbym formuł INDEKS i PODAJ.POZYCJĘ zamiast funkcji WYSZUKAJ.PIONOWO, więc nie musiałbym przełączać żadnych kolumn.

Więc jaka jest formuła? Formuła jest w rzeczywistości formułą PODAJ.POZYCJĘ zagnieżdżoną w formule INDEKS. Zobaczysz, że zróżnicowałem formułę DOPASUJ za pomocą innego koloru tutaj.

Formuła: = INDEKS (tablica tabeli, formuła PODAJ.POZYCJĘ)

Staje się to: =INDEX(tablica tabeli, PODAJ.POZYCJĘ ( przeszukiwana_wartość, przeszukiwana_tablica))

Formuła ze zmiennymi z naszego przykładu poniżej: =INDEX(Arkusz2!A:A,(MATCH(Arkusz1!C:C,Arkusz2!C:C,0)))

Oto zmienne:

• Tablica tabeli : zakres kolumn w Arkuszu 2 zawierających nowe dane, które chcesz przenieść do Arkusza 1. W naszym przykładzie „A” oznacza kolumnę A, która zawiera informacje „Patronus” dla każdej osoby.
• Wartość wyszukiwania : jest to kolumna w Arkuszu 1, która zawiera identyczne wartości w obu arkuszach kalkulacyjnych. W poniższym przykładzie oznacza to kolumnę „e-mail” w arkuszu 1, która jest kolumną C. A więc: Arkusz1!C:C.
• Tablica wyszukiwania : jest to kolumna arkusza 2, która zawiera identyczne wartości w obu arkuszach kalkulacyjnych. W poniższym przykładzie odnosi się to do kolumny „e-mail” w arkuszu 2, która jest również kolumną C. A więc: Arkusz2!C:C.

Gdy masz już proste zmienne, wpisz formuły INDEKS i PODAJ.POZYCJĘ w najwyższej komórce pustej kolumny Patronus na Arkuszu 1, w której chcesz umieścić połączone informacje.

14. Użyj funkcji LICZ.JEŻELI, aby program Excel liczył słowa lub liczby w dowolnym zakresie komórek.

Zamiast ręcznie liczyć, jak często pojawia się określona wartość lub liczba, pozwól, aby program Excel wykonał pracę za Ciebie. Dzięki funkcji LICZ.JEŻELI program Excel może policzyć, ile razy słowo lub liczba pojawia się w dowolnym zakresie komórek.

Załóżmy na przykład, że chcę policzyć, ile razy słowo „Gryffindor” pojawia się w moim zestawie danych.

Formuła: =LICZ.JEŻELI(zakres, kryteria)

Formuła ze zmiennymi z naszego przykładu poniżej: =LICZ.JEŻELI(D:D”,”Gryffindor”)

W tej formule jest kilka zmiennych:

• Zakres : zakres, który ma objąć formuła. W tym przypadku, ponieważ skupiamy się tylko na jednej kolumnie, używamy „D: D”, aby wskazać, że zarówno pierwsza, jak i ostatnia kolumna to D. Gdybym patrzyła na kolumny C i D, użyłbym „C: D ”.
• Kryteria : dowolna liczba lub fragment tekstu, który program Excel ma policzyć. Używaj cudzysłowów tylko wtedy, gdy chcesz, aby wynik był tekstem, a nie liczbą. W naszym przykładzie kryterium to „Gryffindor”.

Po prostu wpisanie formuły LICZ.JEŻELI w dowolnej komórce i naciśnięcie „Enter” pokaże mi, ile razy słowo „Gryffindor” pojawia się w zbiorze danych.

15. Połącz komórki za pomocą &.

Bazy danych mają tendencję do dzielenia danych, aby były jak najdokładniejsze. Na przykład zamiast kolumny zawierającej imię i nazwisko osoby, baza danych może zawierać dane w postaci imienia, a następnie nazwiska w osobnych kolumnach. Lub może mieć lokalizację osoby oddzieloną miastem, stanem i kodem pocztowym. W programie Excel możesz łączyć komórki z różnymi danymi w jedną komórkę, używając znaku „&” w swojej funkcji.

Formuła ze zmiennymi z naszego przykładu poniżej: =A2&” „&B2

Przeanalizujmy razem formułę na przykładzie. Udawaj, że chcemy połączyć imiona i nazwiska w pełne imiona w jednej kolumnie. Aby to zrobić, najpierw umieścilibyśmy kursor w pustej komórce, w której chcemy, aby pojawiła się pełna nazwa. Następnie zaznaczamy jedną komórkę zawierającą imię, wpisujemy znak „&”, a następnie zaznaczamy komórkę z odpowiadającym jej nazwiskiem.

Ale to jeszcze nie koniec — jeśli wpiszesz tylko =A2&B2, między imieniem i nazwiskiem osoby nie będzie spacji. Aby dodać niezbędną przestrzeń, użyj funkcji =A2&” „&B2 . Znaki cudzysłowu wokół spacji informują program Excel, aby wstawił spację między imieniem i nazwiskiem.

Aby było to prawdą dla wielu wierszy, po prostu przeciągnij róg pierwszej komórki w dół, jak pokazano w przykładzie.

16. Dodaj pola wyboru.

If you're using an Excel sheet to track customer data and want to oversee something that isn't quantifiable, you could insert checkboxes into a column.

For example, if you're using an Excel sheet to manage your sales prospects and want to track whether you called them in the last quarter, you could have a “Called this quarter?” column and check off the cells in it when you've called the respective client.

Oto jak to zrobić.

Highlight a cell you'd like to add checkboxes to in your spreadsheet. Then, click DEVELOPER. Then, under FORM CONTROLS, click the checkbox or the selection circle highlighted in the image below.

Once the box appears in the cell, copy it, highlight the cells you also want it to appear in, and then paste it.

17. Hyperlink a cell to a website.

If you're using your sheet to track social media or website metrics, it can be helpful to have a reference column with the links each row is tracking. If you add a URL directly into Excel, it should automatically be clickable. But, if you have to hyperlink words, such as a page title or the headline of a post you're tracking, here's how.

Highlight the words you want to hyperlink, then press Shift K. From there a box will pop up allowing you to place the hyperlink URL. Copy and paste the URL into this box and hit or click Enter.

If the key shortcut isn't working for any reason, you can also do this manually by highlighting the cell and clicking Insert > Hyperlink .

18. Add drop-down menus.

Sometimes, you'll be using your spreadsheet to track processes or other qualitative things. Rather than writing words into your sheet repetitively, such as “Yes”, “No”, “Customer Stage”, “Sales Lead”, or “Prospect”, you can use dropdown menus to quickly mark descriptive things about your contacts or whatever you're tracking.

Here's how to add drop-downs to your cells.

Highlight the cells you want the drop-downs to be in, then click the Data menu in the top navigation and press Validation.

From there, you'll see a Data Validation Settings box open. Look at the Allow options, then click Lists and select Drop-down List. Check the In-Cell dropdown button, then press OK.

19. Use the format painter.

As you've probably noticed, Excel has a lot of features to make crunching numbers and analyzing your data quick and easy. But if you ever spent some time formatting a sheet to your liking, you know it can get a bit tedious.

Don't waste time repeating the same formatting commands over and over again. Use the format painter to easily copy the formatting from one area of the worksheet to another. To do so, choose the cell you'd like to replicate, then select the format painter option (paintbrush icon) from the top toolbar.

Excel Keyboard Shortcuts

Creating reports in Excel is time-consuming enough. How can we spend less time navigating, formatting, and selecting items in our spreadsheet? Glad you asked. There are a ton of Excel shortcuts out there, including some of our favorites listed below.

Create a New Workbook

PC: Ctrl-N | Mac: Command-N

Select Entire Row

PC: Shift-Space | Mac: Shift-Space

Select Entire Column

PC: Ctrl-Space | Mac: Control-Space

Select Rest of Column

PC: Ctrl-Shift-Down/Up | Mac: Command-Shift-Down/Up

Select Rest of Row

PC: Ctrl-Shift-Right/Left | Mac: Command-Shift-Right/Left

Add Hyperlink

PC: Ctrl-K | Mac: Command-K

Open Format Cells Window

PC: Ctrl-1 | Mac: Command-1

Autosum Selected Cells

PC: Alt-= | Mac: Command-Shift-T

Other Excel Help Resources

Use Excel to Automate Processes in Your Team

Even if you're not an accountant, you can still use Excel to automate tasks and processes in your team. With the tips and tricks we shared in this post, you'll be sure to use Excel to its fullest extent and get the most out of the software to grow your business.

Editor's Note: This post was originally published in August 2017 but has been updated for comprehensiveness.