Praca z dużymi zbiorami danych w MS Excel

Podczas wdrażania raportów czy dashboardów opartych na rozwiązaniach oferowanych przez MS Excel, nierzadko można usłyszeć pytania odnoszące się do ograniczeń, jakie występują w samym programie. Jedną z głównych obaw, o których mówią menedżerowie, jest strach przed analizą dużych zbiorów danych w Excelu (powyżej 1 GB). Użytkownicy programu, bazując na swoich doświadczeniach, opisują historię skoroszytów, które zawieszały się np. podczas prac związanych z odświeżaniem lub otwieraniem dużych zbiorów danych.

INFO_18_12.jpg

Na tym etapie warto zdiagnozować, czy wszystkie kłopoty, jakich doświadczają użytkownicy, wynikają z ograniczeń cechujących program, czy też część z nich można wytłumaczyć nie dość dobrymi schematami posługiwania się samym programem. W niniejszym artykule chciałbym przedstawić kilka prostych rozwiązań usprawniających pracę w Excelu na dużych wolumenach danych.

Microsoft Excel w wersji od 2010 r. dysponuje 1 048 576 wierszami i 16 384 kolumnami. Jeżeli w wersji z 2007 r. zapiszemy plik z rozszerzeniem xlsx, parametry te będą takie same. Dodatkowo możemy wyczytać, że jeśli dane, które przetwarzamy, dodamy do Excela jako model danych (Power Pivot), to w Excelu w wersji 32-bitowej będziemy dysponowali dwugigabajtową wirtualną przestrzenią adresową, współdzieloną przez program Excel, skoroszyty i inne dodatki Excela uruchomione w tym samym procesie. Udział modelu danych w tym zakresie mieści się w przedziale 500–700 MB, z możliwym zmniejszeniem zakresu, jeżeli załadowane są inne modele danych. Jeśli dysponujemy 64-bitową wersją programu, twarde ograniczenia dla wielkości pliku nie są z góry określone. Zakłada się, że rozmiar skoroszytu w tym wypadku limituje tylko dostępna pamięć systemowa i zasoby komputera. Tak przyjęte rozwiązanie pozwala już na analizę dziesiątków milionów wierszy bez uszczerbku na płynności działania programu.

Nowe rozwiązania

Znając ograniczenia, jakie występują w programie, warto przyjrzeć się rozwiązaniom stosowanym przez użytkowników pracujących na dużych zbiorach danych. Najczęstszy błąd pojawiający się przy ich analizie jest związany z formą zapisu, z którego czerpiemy dane.

Niejednokrotnie w firmach można zobaczyć „napuchnięte” do granic możliwości pliki Excela, w których źródłem danych są obszerne tabele zaszyte w programie. Przedstawione rozwiązanie poważnie zwiększa wagę pliku, znacząco opóźniając wszystkie czynności wykonywane w skoroszycie. Dla przykładu rozmiar pliku z danymi zapisanymi w programie w postaci obszernej tabeli (1 048 576 wierszy i 13 kolumn), bez tworzenia na jej podstawie tabeli przestawnej, wynosi 125 142 KB, podczas gdy ten sam rezultat zapisany w formie zapytania zmniejsza rozmiar pliku do 13 KB. Warto zatem pamiętać, aby źródło danych, z którego chcemy korzystać, zapisywać jako zapytanie poprzez dodatek Power Query, a nie jako tabelę zaszytą w pliku.

Wykorzystałeś swój limit bezpłatnych treści

Pozostałe 62% artykułu dostępne jest dla zalogowanych użytkowników portalu. Zaloguj się, albo wybierz plan abonamentowy.

Wybierz abonament już od 83 zł miesięcznie Pokaż abonament
Dwutygodniowy dostęp bez zobowiązań Wybieram

Abonament już od 83 zł miesięcznie

Dwutygodniowy dostęp bez zobowiązań

Pełen dostęp do wszystkich treści portalu
to koszt 83 zł miesięcznie
przy jednorazowej płatności za rok

WYBIERAM

Dwutygodniowy dostęp do wszystkich treści
portalu za 99 zł netto, które odliczymy od ceny
regularnej przy przedłużeniu abonamentu

WYBIERAM

Pełen dostęp do wszystkich treści portalu
to koszt 990 zł miesięcznie
przy jednorazowej płatności za rok

Dwutygodniowy dostęp do wszystkich treści
portalu za 99 zł netto, które odliczymy od ceny
regularnej przy przedłużeniu abonamentu

WYBIERAM