Niedawno brałem udział w kilku seminariach zaawansowanych w programie Excel. Kiedy masz 150 księgowych w jednym pokoju na pełen śmiechu poranek ze wskazówkami i sztuczkami Excela, zawsze uczę się czegoś nowego. Ktoś na widowni może podzielić się fajną sztuczką z resztą sali.
W dzisiejszym odcinku mam zbiór nowych sztuczek. W rzeczywistości są to sztuczki, które są lepsze lub inne niż równoważna metoda omówiona w książce. Na pewno będą w następnej wersji książki.
Przy okazji, bardzo chciałbym przyjechać do Twojego miasta na seminarium Power Excel. Jeśli należysz do grupy zawodowej, takiej jak lokalny oddział Instytutu Księgowych Menedżerskich, Instytut Audytorów Wewnętrznych, AICPA, MŚP itp., Dlaczego nie zaproponować, aby zarezerwowali mnie na jeden z nadchodzących dni CPE? Wyślij przewodniczącego programu oddziału na tę stronę, aby uzyskać szczegółowe informacje.
Znajdź różnicę między dwiema datami
Zwykle mówimy o sposobach korzystania =YEAR(), =MONTH(), =DAY()funkcji, ale jest fajne stare funkcja ukrywanie w programie Excel.
Funkcja DATEDIF jest pozostałością po Lotus. Chociaż pomoc programu Excel nie mówi o tej funkcji, jest to świetny sposób na znalezienie różnicy między dwiema datami.
Składnia to =DATEDIF(EarlierDate,LaterDate,Code)
Oto prawidłowe wartości, których możesz użyć dla kodu.
- Y - podaje liczbę pełnych lat między dwiema datami.
- YM - poda liczbę pełnych miesięcy, z wyłączeniem lat, między dwiema datami.
- MD - poinformuje Cię o liczbie pełnych dni, z wyłączeniem pełnych miesięcy, między dwiema datami.
- M - poda liczbę pełnych miesięcy. Na przykład żyję przez 495 miesięcy
- D - powie ci liczbę dni. Na przykład żyję 15 115 dni. Jest to trywialne zastosowanie, ponieważ wystarczy odjąć jedną datę od drugiej i sformatować ją jako liczbę, aby zduplikować ten kod.
Przydatne kody to pierwsze trzy kody. W programie pokazałem ten arkusz. Identyczne formuły w kolumnach D, E i F obliczają DATEDIF w latach, miesiącach i dniach.
Formuła w kolumnie G łączy to razem, aby utworzyć tekst z długością czasu w latach, miesiącach i dniach.
Możesz połączyć to w jedną formułę. Jeśli komórka A2 zawiera datę przyłączenia, użyj następującej formuły w B2:
=DATEDIF($A2,TODAY(),"Y")&" years, "&DATEDIF($A2,TODAY(),"YM")&" months & "&DATEDIF($A2,TODAY(),"MD")&" days"
Suma widocznych komórek
Dodaj funkcję SUMA poniżej bazy danych, a następnie użyj Autofiltru do filtrowania bazy danych. Excel irytująco uwzględni ukryte wiersze w sumie!
Zamiast tego wykonaj następujące kroki:
- Użyj opcji Dane - Filtr - Autofiltr, aby dodać listy rozwijane Autofiltru.
- Wybierz filtr dla jednego pola
- Przejdź do pustej komórki poniżej jednej z kolumn liczbowych w bazie danych.
- Kliknij grecką literę E (Sigma) na standardowym pasku narzędzi. Zamiast wprowadzać
=SUM(), program Excel wprowadzi=SUBTOTAL()i użyje kodów, aby zapobiec dołączaniu ukrytych wierszy.
Klawisz skrótu, aby powtórzyć ostatnie polecenie
Klawisz F4 powtórzy ostatnie polecenie, które wykonałeś.
Na przykład wybierz komórkę i kliknij ikonę B, aby ją pogrubić.
Teraz wybierz inną komórkę i naciśnij F4. Excel sprawi, że ta komórka będzie pogrubiona.
F4 zapamięta ostatnie polecenie. Możesz więc napisać komórkę kursywą, a następnie użyć klawisza F4, aby uczynić wiele komórek kursywą.
Wybierz wstępnie zakres komórek do wprowadzenia
W książce pokazuję, jak korzystać z Narzędzia - Opcje - Edycja - Przenieś zaznaczenie po wprowadzeniu kierunku - W prawo, aby wymusić przesunięcie programu Excel w prawo po naciśnięciu klawisza Enter. Jest to dobre, gdy musisz wprowadzić dane przechodzące przez wiersz.
Jest to szczególnie przydatne, jeśli wpisujesz liczby na klawiaturze numerycznej. Sztuczka pozwala wpisać 123 Enter i znaleźć się w następnej komórce. Trzymając ręce na klawiaturze numerycznej, możesz szybciej wprowadzać liczby.
Ktoś zasugerował ulepszenie tej techniki. Wstępnie wybierz zakres, w którym będziesz wprowadzać dane. Zaletą jest to, że kiedy dojdziesz do ostatniej kolumny i naciśniesz Enter, Excel przeskoczy na początek następnego wiersza.
Na poniższym obrazku naciśnięcie klawisza Enter przeniesie Cię do komórki B6.
Ctrl + przeciągnij uchwyt wypełnienia
Pokazałem sztuczkę Wypełnij uchwyt wiele razy w programie. Wpisz poniedziałek na A1. Jeśli wybierzesz komórkę A1, w prawym dolnym rogu komórki znajduje się kwadratowa kropka. Ta kropka to uchwyt wypełnienia. Kliknij uchwyt wypełnienia i przeciągnij w dół lub w prawo. Excel wypełni wtorek, środę, czwartek, piątek, sobotę, niedzielę. Jeśli przeciągniesz więcej niż 7 komórek, program Excel rozpocznie się od nowa w poniedziałek.
Excel jest naprawdę dobry. Może automatycznie przedłużyć wszystkie te serie:
- Poniedziałek - wtorek, środa, czwartek, piątek itp.
- Styczeń - luty, marzec, kwiecień itp.
- Styczeń - luty, marzec itd.
- Q1 - Q2, Q3, Q4 itd.
- Kw 1 - Kw 2, Kw 3, Kw 4, Kw 1 itd.
- I okres - II okres, III okres, IV okres itd.
- 23 października 2006-24 października 2006, 25 października 2006 itd.
Ponieważ program Excel może wykonać WSZYSTKIE te niesamowite serie, czego byś się spodziewał, wprowadzając 1 i przeciągając uchwyt wypełniania?
Możesz oczekiwać, że otrzymasz 1, 2, 3,…
Ale naprawdę dostajesz 1, 1, 1, 1, 1,…
Książka mówi o zawiłej metodzie. Wpisz 1 w A1. Wpisz 2 w A2. Wybierz A1: A2. Przeciągnij uchwyt wypełnienia. Jest lepszy sposób.
Wystarczy wpisać 1 w A1. Ctrl + przeciągnij uchwyt wypełnienia. Excel wypełni 1, 2, 3. Przytrzymanie klawisza Ctrl wydaje się nadpisywać normalne zachowanie uchwytu wypełniania.
Ktoś na seminarium powiedział, że chciałby wprowadzić datę, przeciągnąć datę i poprosić Excela o zachowanie tej samej daty. Jeśli przytrzymasz klawisz Ctrl podczas przeciągania uchwytu wypełniania, program Excel nadpisze normalne zachowanie (zwiększając datę) i poda tę samą datę we wszystkich komórkach.
