Lord of the Servers

A héten egy viszonylag nagy táblázatot kellett összeállítnom az AIS middleware robosztusság tesztek eredményeiből. A viszonylag nagy azt jelenti, hogy volt kb 145000 sorom, amiknek nagy részében képlettel kellett kiszámolni dolgokat, szóval nem mindegy a sebesség.

1. Keresés

Értékek keresésére viszonylag sok függvény áll rendelkezésünkre. Ezek közül az FKERES (VLOOKUP) a legadvancedabb. És az esetek többségében elég is, de lineárisan keres. Emiatt nagy tábláknál használhatatlan.

A KERES (LOOKUP) függvény ezzel szember binárisan keres. A keresett érték tömbnek növekvő sorrendben rendezettnek kell lennie emiatt.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

LOOKUP(lookup_value,lookup_vector,result_vector)

Még így is egy 1100000 (hivatkozások) * 43000-s (kulcsok) update eltart egy pár másodpercig. FKERES-sel viszont kivárhatatlan.

2. Automatikus kitöltés

Nagyon hasznos funkció, hogy a jobb alsó sarokban levő kis négyzettel automatikusan kitölthetünk cellákat. Többtízezer cella esetén azonban még ez sem elég segítség.Mivel API-ból is elérhető ez a funkció, írtam egy kis makrót, amivel bármennyi cellát automatikusan kitölthetünk lefelé:


Sub filldown()
maxindex = Cells(2, 1).End(xlDown).Row
copyFormula = ActiveCell.Formula
Set destinationRange = ActiveSheet.Range(ActiveCell, Cells(maxindex, ActiveCell.Column))
ActiveCell.AutoFill destinationRange
End Sub

A maxindex képletét megváltoztatva igazítani kell az igényeidhez, de másra nincs szükség. Az éppen aktuális cellában levő formulát sokszorozza lefelé a megfelelő számú cellába.

Most ennyi jutott eszembe. Még ráadásként itt  egy listát az angol függvények magyar megfelelőivel, meg még egy csomó más nyelvvel.

Continue reading Excel optimalizáció