[lightbluefox]

Oleks tore, kui keegi oskaks soovitada kuidas excelit kiirendada, mõni hea nipp nt.


Põhimõtteliselt on mul sirka 10 veergu infot erineva tekstiga, ja siis seal kõrval teine 10 veergu, mis tegelevad eelnevatest veergudest märksõnade väljavõtmisega.

A la ühes veerus on tekst "10 kotti kartuleid", siis seal on üks veerg, mis sellest veergust võtab sealt väärtuse "kartul" välja, lisaks ka kontrollid teise töölehega.

infot korjab välja "mid" ja left" peamiselt. teiste lehtede pealt otsib välja infot INDEX MATCH.



Palju on infot? Sirka 100 000 rida ja tuleb kogu aeg juurde. See tähendab, et kui ma muudan excelis ühte numbrit, siis excel arvutab seda muudatust ka 50 sekundit, core i5 pro 4 tuuma täiega töös.

Mul on mõtteid kuidas seda parandada, aga kuna excelist väga palju ei tea, siis tahaks neid mõtteid kellegi vastu põrgatada.

1. Exelis kasutusel olevad index match kasutab lookupi jaoks terveid veerge "Tabel1!K:S". Ma arvan, et kui see vahemik oleks dünaamiline (ehk kasvaks vastavalt lisatud ridade arvule), oleks arvestus kiirem ja excel ei peaks proovima iga rea pealt midagi otsida.
2. Kasutada "insert table" süsteemi, ehk konverteerida olemasolev info excelis tabeliks, nii saab sellele andmehulgale ka nime anda nt "Table1", siis saaks ka valemeid luues anda neile väärtused tabeliveergude alusel mis ehk lihtsustab ka valemitest arusaamist. Ma ei oska ainult tabeli põhjal uusi index match valemeid teha Ja on ka küsimus kas sellest oleks kiirusevõitu.
3. Olen kuulnud, et tabeli sorteerimine mingis kahanevas-kasvavas järjekorras kiirendab arvutamise protsessi? Kuna ma nagunii filtreerin seda pidevalt, siis ehk oleks see mõeldav, kui keegi oskaks oma praktika järgi seda kommenteerida.

Veel mõni hea nipp kuidas suurt andmehulka excelis paremini hallata?

[napoleon]

Üks variant on keerata ümberarvutus kinni selleks ajaks kui andmeid sisestad:
https://www.howtogeek.com/162219/how-to-change-the-automatic-calculation-and-multi-threading-features-in-excel-2013/

Muu tuunimise jaoks peaks andmetele otsa vaatama. Aga index ja match osas on esimene google vaste selline:
https://superuser.com/questions/547083/how-do-you-speed-up-searching-large-columns-in-excel
ehk sorteerimine aitab küll, aga valemid on siis mõnevõrra keerulisemad. Siin on suur miinus muidugi see, et kui unustad peale andmete lisamist tabeli uuesti sorteerida, siis hakkab aiateibaid näitama.

Professionaalsest kretinismist mõtleks mina muidugi kohe, kas sellise andmehulgaga peab ilmtingimata excelis toimetama, aga noh.... muud variandid eeldavad teatavat kokkupuudet programmeerimise ja andmebaasidega.

[lightbluefox]

napoleon, ilmselgelt on automaatne arvutamine mahakeeratud, sest on kohati vaja muuta mitmeid väärtuseid korraga ja ei oleks mõeldav, et iga kord 50s oodata.
Andmebaasi ei kannata tõsta, kuna algandmeid on vaja muuta, ehk mul ei ole vaja neid olemasolevaid andmeid vaid pivotiteks teha vaid ka täiendada ja see võiks olla ka teistele kasutajatele arusaadav, ehk mitte ühe patsiga poisi tabel, vaid hästitöötavate valemitega exceli tabel mis on nii kiire oma arvutustes kui mõistlikult saavutatav.

[tahanteada]

[napoleon]

Aga proovi MS Accessi lükata andmed. Seal saad suht drag&drop meetodil vormid sisestamiseks tekitada ja teha view, mille abil pärast kuvad neid nii nagu vaja.
Võimalik et kannatab seda excelit ka tuunida, aga olemasoleva info põhjal ei oska öelda, selleks peaks ikka korra otsa vaatama mis seal hetkel tehtud on ja kas mõnda valemit kannatab kuidagi mõistlikumalt kirjutada. Teisel lingil mis andsin on ju kirjeldatud, kuidas sorteerimise abil asja kiiremaks saad. ~100 000 rea korral vast mingit mõistlikumat lahendust polegi. Kui sul on näiteks tabelis A 1000 rida ja tabelis B 100 000 rida ja nüüd peab iga tabeli A rea kohta midagi tabelist B otsimas käima, siis kokku tehakse 100 000 000 võrdlust kui sorteerimist/indekseerimist pole kasutatud. Selline päring lihtsalt on aegalne ja seda isegi mõistlikumate baaside kui excel puhul.

[Mnator]

[lightbluefox]

Ehk tõesti, aga ma ei usu et ehk max 200 000 rea pärast oleks vaja access appi võtta, Excel peaks suutma miljonit rida töölehe kohta händlida ja kui valemeid mõistlikult teha, siis ka täiesti kasutatavalt. Praegugi on ju täiesti kasutatav, aga olen kindel, et saab lihtsalt märkimisväärselt paremini, kuna mingile efektiivsusele arvutamistes rõhku pandud pole.

[napoleon]

lightbluefox, kui seal excelis mingit suurt äri- või muud saladust pole, siis ehk viskad kuhugi üles selle tabeli. Su esimeses postis olev jutt on selleks liiga üldine, et keegi midagi konkreetset oskaks arvata.

Aga nagu aru saan, otsitakse sul ühes tabelis vasteid teisest tabelist. Selline loogika pole päris tavaline valem, mis lihtsalt midagi arvutab vms. Kui sul on kaks 200 000 reaga tabelit ja ühes tabelis tehakse lookup teise tabelisse, siis kõhus peab excel käima läbi 200 000 x 200 000 rida ehk jutt pole enam mitte miljonist vaid 40 miljardist võrdlusest. No ok, tegelikult võime ka öelda keskmiselt 20 miljardit kuna otsid teisest tabelist ühte vastavat väärtust mitte kõiki, aga see on ikkagi juba päris palju. Kindlasti saab midagi teha, kui muu ei aita siis kasvõi mõne makro kirjutada, iseasi kas see vaev ennast õigustab. Võimalik et lükata andmed baasi ja teha näiteks lihtne veebiliides nende haldamiseks või lahendada asi accessi abil võtaks vähem aega kui see exceliga pusimine.

[lightbluefox]

Kõpitsesin midagi valmis
https://www7.zippyshare.com/v/oYnSugEe/file.html

Spoiler

Parool: parool

Üldiselt vist peaks olema loogika hoomatav

Outgoing lehel on kollastes väljades raw data, mida valgetes ruutudes olevad lahtrid pulkadeks võtavad, et töödelda saaks ja vajadusel muuta, nii et algandmeid muuta pole vaja. Nt kuskil valem ära kustutada ja lisada uus.

- igdata, sdr, ugo on tulemuste näitamiseks (pivotid), codemapping ja pcmapping vastutavad loogika eest.

- ntrul check siis kontrollib, kas tabelis olev info klapib.

Failis on ridu vähe (arvuti võimsust testida ei saa), aga eks vajadusel saab neid ju ise juurde copy-peistida, aga siiski peaks excelit tundev inimene aru saama, kas midagi tundub, et saaks paremini kirjutada.


Tänud.

EDIT: Selgub, et oleks võinud alustada kohe algusest, et testida ilma valemiteta
Sain teada, et aeglus tuleb vaid ühest lehest sumif valemitest ehk see check leht on probleemiks.