10 Tricks to Fix Slow Excel Spreadsheets (Speed-up Excel)

Jeśli jest jedna rzecz, która łączy nas wszystkich, to musi być frustracja, aby nadążyć z powolnym arkuszy kalkulacyjnych Excel.

Choć wpływ na wydajność może być nieistotny, gdy jest mniej danych, staje się głębszy, gdy dodajesz coraz więcej danych/obliczeń do skoroszytu.

9 na 10 razy, użytkownik Excela będzie narzekał na powolne arkusze Excela. I prawie nic nie można z tym zrobić.

Cóż, to NIE jest do końca prawda.

Sposób, w jaki Excel został stworzony, jest powolny przy dużych zestawach danych. Jednakże, istnieje wiele sztuczek przyspieszających, których możesz użyć, aby poprawić wydajność powolnego arkusza kalkulacyjnego Excel.

10 wskazówek, jak radzić sobie z powolnym arkuszem kalkulacyjnym Excel

Oto 10 wskazówek, aby dać powolnemu arkuszowi kalkulacyjnemu Excel mały wzrost prędkości i zaoszczędzić trochę czasu i frustracji (kliknij, aby przejść do tej konkretnej sekcji).

1. Unikaj lotnych funkcji (musisz).
2. Używaj kolumn pomocniczych.
3. Unikaj formuł tablicowych (jeśli możesz).
4. Stosuj formatowanie warunkowe z rozwagą.
5. Używaj tabel Excela i nazwanych zakresów.
6. Przekształć nieużywane formuły na wartości.
7. Przechowuj wszystkie dane w jednym arkuszu.
8. Avoid Using Entire Row/Column in References.
9. Use Manual Calculation Mode.
10. Use Faster Formula Techniques.

Avoid Volatile Formulas

Volatile formulas are called so because of a reason. Funkcje takie jak NOW, TODAY, INDIRECT, RAND, OFFSET itp. przeliczają się za każdym razem, gdy następuje zmiana w skoroszycie.

Na przykład, jeśli użyjesz funkcji NOW w komórce, za każdym razem, gdy nastąpi zmiana w arkuszu, formuła zostanie przeliczona, a wartość komórki zaktualizowana.

To wymaga dodatkowej prędkości przetwarzania i kończysz z powolnym skoroszytem Excela.

Jako zasada kciuka, unikaj zmiennych formuł. A jeśli nie możesz, spróbuj zminimalizować ich użycie.

Używaj kolumn pomocniczych

Kolumny pomocnicze są jedną z najbardziej niedocenianych konstrukcji projektowych w Excelu. Widziałem, jak wielu ludzi unika tworzenia kolumn pomocniczych.

Nie rób tego.

Największą korzyścią z używania 'Kolumn pomocniczych’ jest to, że mogą one pomóc Ci uniknąć formuł tablicowych.

Nie zrozum mnie źle. Nie jestem przeciwny formułom tablicowym. Raczej wierzę, że mogą one być świetne w niektórych sytuacjach. Ale to, kiedy próbujesz zrobić to wszystko z jednym długim formuły, to ma wpływ na wydajność swojego skoroszytu programu Excel. Kilka formuł tablicowych tu i tam nie powinno zaszkodzić, ale w przypadku, gdy musisz użyć go w wielu miejscach, rozważ użycie kolumn pomocniczych.

Oto kilka przykładów, w których kolumny pomocnicze są używane:

• Automatyczne sortowanie danych w porządku alfabetycznym za pomocą formuły.
• Dynamiczny filtr Excela – Wyodrębnij dane, jak pisać.
• Tworzenie wielu list rozwijanych w Excelu bez powtarzania.

Unikaj formuł tablicowych

Formuły tablicowe mają swoje zalety – ale szybkość nie jest jedną z nich.

Jak wyjaśniono powyżej, formuły tablicowe mogą pobierać wiele danych (odwołań do komórek), analizować je i podawać wynik. Ale to wymaga czasu.

Jeśli istnieje sposób na uniknięcie formuł tablicowych (np. użycie kolumny pomocniczej), zawsze wybieraj tę drogę.

Używaj formatowania warunkowego z rozwagą

Bardzo lubię formatowanie warunkowe. Sprawia ono, że nijakie dane wyglądają tak pięknie. Zamiast samodzielnie dokonywać porównań, teraz możesz po prostu spojrzeć na kolor komórki lub ikonę i będziesz wiedział, jak wypada ona na tle innych.

Ale… tu jest problem.

Niewielu użytkowników Excela wie, że formatowanie warunkowe w Excelu jest zmienne. Podczas gdy możesz nie zauważyć różnicy z małymi zestawami danych, może to spowodować powolne działanie arkusza kalkulacyjnego Excela, jeśli jest stosowane na dużych zestawach danych lub stosowane wielokrotnie.

Słowo porady – używaj go ostrożnie.

Używaj tabel Excela i nazwanych zakresów

Tabela Excela i nazwane zakresy są dwiema niesamowitymi funkcjami, które przechowują twoje dane i sprawiają, że odwoływanie się jest super łatwe. Przyzwyczajenie się do nich może zająć trochę czasu, ale kiedy zaczniesz ich używać, życie stanie się łatwe i szybkie.

W tworzeniu pulpitów nawigacyjnych opartych na danych, prawie zawsze dobrym pomysłem jest przekonwertowanie danych do tabeli Excela.

Ma to również dodatkową zaletę, czyniąc Twoje formuły bardziej zrozumiałymi.

Na przykład, co jest łatwiejsze do zrozumienia?

=Cena sprzedaży-Cena kosztu OR =SUM(A1:A10)-SUM(G1:G10)

Przekształć nieużywane formuły na wartości statyczne

To nie jest oczywistość. Jeśli jej nie potrzebujesz, nie przechowuj jej.

Wiele formuł spowodowałoby powolne działanie skoroszytu programu Excel. A jeśli masz formuły, które nie są nawet używane – wiesz, kogo za to winić. Z reguły, jeśli nie potrzebujesz formuł, lepiej zamienić je na wartości statyczne (wklejając jako wartości).

Read More: How to quickly convert formulas to values.

Zachowaj wszystkie dane w jednym arkuszu

Nie zawsze jest to możliwe, ale jeśli możesz to zrobić, gwarantuję, że Twój arkusz Excela będzie szybszy.

Logika jest prosta – formuły w twoim arkuszu nie muszą iść daleko, aby uzyskać dane, gdy są one tuż obok w tym samym arkuszu.

Unikaj używania całego wiersza/kolumny jako odniesienia (A:A)

Jedynym powodem, dla którego umieściłem to na liście, jest to, że widzę wielu ludzi używających całego wiersza/kolumny jako odniesienia w formułach. Jest to zła praktyka i należy jej unikać.

Mimo że możesz myśleć, że wiersz/kolumna ma tylko kilka komórek z danymi, Excel nie myśli w ten sposób. Kiedy odwołujesz się do całego wiersza/kolumny, Excel działa jak dobry sługa i sprawdza go tak czy inaczej. To zajmuje więcej czasu na obliczenia.

9. Użyj trybu obliczeń ręcznych

Jestem po prostu powtarzając to, co miliony ludzi już powiedział na różnych forach i blogach. Korzystanie z trybu obliczeń ręcznych daje Ci elastyczność, aby powiedzieć Excelowi, kiedy ma obliczyć, a nie Excel podejmuje własne decyzje. To nie jest coś, co przyspiesza Twój skoroszyt Excela, ale jeśli masz powolny arkusz Excela, to zdecydowanie oszczędza czas, nie zmuszając Excela do wielokrotnego przeliczania.

• Aby przełączyć się na tryb ręczny, przejdź do zakładki Formuły -> Opcje obliczeń -> Ręczne (naciśnij klawisz F9, aby ponownie obliczyć)

Używaj szybszych technik formuł

Excel daje Ci wiele formuł i kombinacji formuł, aby zrobić to samo. Najlepiej jest zidentyfikować i używać najszybszych z nich.

Oto kilka przykładów:

• Użyj IFERROR zamiast IF i ISERROR combo (chyba że używasz Excel 2003 lub wcześniejszego, który nie ma IFERROR).
• Użyj MAX(A1,0) zamiast IF(A1>0,A1,0) – Jest to fajna wskazówka, której nauczyłem się od Pana Excela aka Bill Jelen. Jego badania pokazują, że opcja MAX jest o 40% szybsza niż opcja IF (i jestem gotów wziąć ten stat na jego wartość nominalną).
• Użyj INDEX/MATCH combo, zamiast VLOOKUP – To może podnieść wiele brwi, ale prawda jest taka, że nie ma mowy, VLOOKUP może być szybszy, jeśli masz 100’s kolumn danych. Świat zmierza w kierunku INDEKSÓW/MATCH i ty też powinieneś dokonać tej zmiany.
  .
• Użyj – (podwójne negacje) do konwersji TRUE’s i FALSE’s na 1’s i 0’s (zamiast mnożenia przez 1 lub dodawania 0 do niego). Poprawa szybkości jest zauważalna w dużych zestawach danych.

Czy jest to wyczerpująca lista? Absolutnie NIE. To kilka dobrych, które moim zdaniem warto udostępnić jako punkt wyjścia. Jeśli szukasz opanować techniki Excel-Speed-Up, jest trochę dobrej pracy wykonanej przez wielu ekspertów Excel.

Oto kilka źródeł, które możesz uznać za przydatne:

• 75 Wskazówki dotyczące przyspieszenia przez Chandoo (sprytnie wykonane przez crowdsourcing).
• Decision Models Website.
• Mr. Excel Message Board (zbadaj to i można znaleźć mnóstwo wskazówek).

Jestem pewien, że masz również wiele wskazówek, które mogą pomóc uporać się z powolnym arkuszy kalkulacyjnych Excel. Czy podzielić się nim z nami tutaj w sekcji komentarzy.

Mam również jedną prośbę. Ból pracy z powolnym arkuszem kalkulacyjnym Excela jest czymś, czego wielu z nas doświadcza na co dzień. Jeśli uważasz, że te techniki są przydatne, podziel się nimi z innymi. Ease their pain, and earn some goodness 🙂

You May Also Like the Following Excel Links:

• 24 Excel Tricks to Make You Sail through Day-to-day work.
• 10 Super Neat Ways to Clean Data in Excel Spreadsheets.
• 10 porad dotyczących wprowadzania danych w programie Excel, których nie możesz przegapić.
• Tworzenie i korzystanie z listy rozwijanej w programie Excel.
• Zmniejszanie rozmiaru plików programu Excel.
• Jak odzyskać niezapisane pliki programu Excel.
• Bezpłatne szkolenie online z obsługi programu Excel (7-częściowy kurs wideo)

.