Excel ma szeroką gamę przydatnych wbudowanych funkcji, ale nie oznacza to, że należy ich używać chaotycznie. Jedna Co robić, gdy program Excel działa wolno Należy sprawdzić kilka formuł, aby upewnić się, że korzystasz z tych funkcji efektywnie. Nawet pozornie proste formuły mogą mieć znaczący wpływ na wydajność i powodować liczne problemy.
Chociaż istnieje wiele możliwych przyczyn, formuły wykorzystujące funkcje zmiennych, tablicowe i wyszukujące są najistotniejsze. Wyjaśnię, dlaczego mogą one spowalniać działanie arkuszy kalkulacyjnych, a także podam kilka wskazówek, jak je naprawić, aby poprawić wydajność, lub kiedy całkowicie z nich zrezygnować.
Szybkie linki
Formuły zawierające funkcje zmienne
Istnieje dobry powód, dla którego profesjonaliści używają go oszczędnie.
Jeśli zauważysz, że arkusz Excela przelicza się za każdym razem, gdy wprowadzasz zmianę, co wpływa na wydajność, przyczyną są prawdopodobnie funkcje zmiennych. Funkcje zmiennych wymuszają przeliczanie, aby wyniki były aktualne. Dzieje się tak nawet wtedy, gdy zmiana nie wpływa na żadną z komórek, do których się odwołujesz, ani na ich wyniki.
Jedną z powszechnych funkcji zmiennych jest NOW, która zwraca aktualną datę i godzinę i jest przydatna w takich sytuacjach, jak obliczanie terminów, wstawianie znaczników czasu i aktualizowanie pulpitów nawigacyjnych w czasie rzeczywistym. Inną funkcją jest Funkcja POŚR, który wykorzystuje ciągi tekstowe do tworzenia odwołań do komórek i jest przydatny między innymi do odwoływania się do zakresów dynamicznych.
Chociaż dobrze sprawdzają się w utrzymaniu dynamiki, należy ich używać tylko wtedy, gdy jest to konieczne. Dotyczy to zwłaszcza dużych formularzy zawierających dziesiątki arkuszy lub tysiące wierszy. Najlepiej jest również korzystać z alternatywnych rozwiązań nieulotnych, gdy tylko jest to możliwe (na przykład ręczne wprowadzanie daty zamiast korzystania z funkcji TERAZ).
Jeśli nie możesz uniknąć funkcji zmiennych i spowalniają one pracę skoroszytu, możesz wyłączyć automatyczne przeliczanie. W ten sposób możesz ręcznie uruchamiać przeliczanie, zamiast uruchamiać je przy każdej drobnej zmianie, ale wadą jest to, że łatwo o tym zapomnieć. F9 Przelicza wszystkie otwarte arkusze kalkulacyjne i Shift + F9 Przelicza tylko aktywny arkusz kalkulacyjny.
Stare wzory macierzowe
Przetwarzanie formuł macierzowych nie zawsze jest takie proste.
Aby utworzyć formuły macierzowe, wpisz formułę i naciśnij Ctrl + Shift + Enter Zamiast po prostu WchodzęBędziesz wiedział, że formuła została pomyślnie utworzona, jeśli zostanie automatycznie ujęta w nawiasy klamrowe.
Formuły tablicowe są szczególnie przydatne, gdy obliczenia obejmują wiele wartości, oszczędzając dodatkowego kroku tworzenia kolumn pomocniczych. Mogą również zwracać wiele wyników.
Formuły macierzowe to starsza funkcja, zwłaszcza teraz, gdy Microsoft wprowadził wydajniejsze dynamiczne funkcje macierzowe w programie Excel dla usługi Microsoft 365. Nadal jednak mają swoje zastosowanie, zwłaszcza gdy chcesz zapewnić zgodność ze starszymi wersjami programu Excel (2019 i starszymi). Dlatego nie znikną w najbliższym czasie.
Chociaż w przypadku formuł macierzowych wszystko jest uporządkowane w jednym wierszu, Excel przetwarza formułę wielokrotnie, aby uzyskać wyniki. Może to być kosztowne obliczeniowo, szczególnie w przypadku dużych arkuszy Excela.
Aby uniknąć formuł macierzowych, warto rozważyć użycie kolumn pomocniczych, które rozkładają obliczenia na wiele komórek. Jeśli nie możesz ich uniknąć, spróbuj nie używać ich z funkcjami zmiennych. Jeśli zgodność nie stanowi problemu, możesz po prostu używać dynamicznych funkcji macierzowych, gdziekolwiek to możliwe.
Wyszukaj formuły
To wygodne, ale może być kosztowne.
Jeśli potrzebujesz znaleźć konkretną wartość w arkuszu Excela, najszybszym sposobem jest użycie formuły wyszukiwania. Najczęściej używanymi funkcjami w tych formułach są WYSZUKAJ.PIONOWO i WYSZUKAJ.POZIOMO, które przeszukują odpowiednio kolumny i wiersze. Na przykład, łatwo jest znaleźć cenę produktu na liście pionowej, używając jego nazwy. WYSZUKAJ.PIONOWO.
Problem polega na tym, że te formuły wyszukiwania mogą powodować spadek wydajności, jeśli przeszukiwane są duże zbiory danych, zwłaszcza gdy przeszukiwane dane są rozproszone na wielu arkuszach. Jest to proces czasochłonny i może obciążać program Excel.
O ile nie korzystasz intensywnie z funkcji wyszukiwania, może to nie stanowić dużego problemu w wersji Excela dla Microsoft 365, ponieważ tworzy ona tymczasowy indeks, który przyspiesza cykliczne wyszukiwania. Posiada ona również wydajniejszą (i wszechstronniejszą) alternatywną funkcję wyszukiwania o nazwie XLOOKUP, z której możesz skorzystać.
Jeśli musisz używać formuł wyszukiwania w starszych wersjach programu Excel, bardziej efektywne jest użycie formuły w tym samym arkuszu, co macierz wyszukiwania. Powinieneś również ograniczyć zakres wyszukiwania tylko do niezbędnych komórek (na przykład: B2: B100) — Unikaj odwoływania się do całej kolumny (np. NOCLEG ZE ŚNIADANIEM).
). Ponadto należy używać dopasowania przybliżonego zamiast dokładnego, ponieważ w przypadku tego pierwszego nie jest wymagane przeszukiwanie każdej komórki w celu znalezienia potrzebnej wartości.
Uwolnij moc błyskawicznych plików Excel
Z mojego doświadczenia wynika, że te trzy problemy stanowią największe problemy, z jakimi borykają się użytkownicy. Należy również uważać na formuły zagnieżdżone, zwłaszcza te zawierające złożoną logikę warunkową. Ponownie, może to nie być poważny problem w programie Excel dla Microsoft 365, który wykorzystuje ukryte indeksy do przyspieszenia wyszukiwania rekurencyjnego.
Oprócz sprawdzania formuł, upewnij się, że Excel jest aktualny, usuń niepotrzebne dane i nieużywane dodatki. Wszystko, co możesz zrobić, aby przyspieszyć działanie pliku Excel, to jeden problem mniej. Możesz skupić się na wykonywaniu pracy, zamiast tracić czas na optymalizację.
