OPGELOST: OpenOffice Spreadsheet-probleem

[10o]

Bij OpenOffice blijken sommige functies na update niet meer downward compatible te zijn. Dit heb ik al eens eerder meegemaakt, maar deze keer komt het echt heel ongelukkig uit.

Zo werk ik al jaren met een zelfgebouwd spreadsheet voor een stuk administratie. Sinds de update (naar 3.2 via Maverick) is mijn spreadsheet - met een tabblad voor iedere week van het jaar - plotseling onbruikbaar geworden. Het staat vol met #WAARDE!-meldingen.  En aangezien ieder tabblad voortborduurt op het vorige is het niet zo 1-2-3 aan te passen.

Het gaat mis bij velden die als volgt zijn opgebouwd:

Code: [Selecteer]

=ALS(L8;L8*C8;"")
De bedoeling achter dit veld is: Als er een waarde staat in L8, vermenigvuldig dan L8 en C8. Staat er niets, geef dat een leeg veld terug (niet het cijfer 0). Het gaat blijkbaar mis bij de eerste beoordeling: ALS(L8), wat voorheen zoveel betekende als "Als het veld een waarde heeft". Als ik simpelweg =L8*C8 zou gebruiken, geeft hij overigens ook #WAARDE! terug indien L8 leeg is...

Op ieder tabblad komt deze declaratie tig keer voor (van week 1 tot week 53). Weet iemand misschien een manier waarop ik dit lopende spreadsheet weer aan de praat kan krijgen?

Edit: Als iemand weet hoe ik kan downgrade naar de vorige versie, hou ik mee ook aanbevolen!

[Rachid]

Ik heb dat #WAARDE probleem wel eens gehad als de kolom niet breed genoeg was. Wat gebeurt er als je de betreffende kolom een stuk breder maakt?
Het klinkt heel stom, maar ik hoop dat dit werkt.

[10o]

@Wazzzaaa, bedankt voor je reactie, maar daar zit het 'm niet in.

[Bloom]

Probeer eens de formule

Code: [Selecteer]

=als(isgetal(L8);L8*C8;"")Werkt het dan wel?
Zo ja, dan is het probleem dat L8 geen logische waarde oplevert als dat veld niet ingevuld is.

[10o]

@Bloom, dat werkt!
Maar handmatig aanpassen op alle tabbladen en cellen is natuurlijk geen doen. Dat valt definitely in de categorie monnikenwerk en daarmee schieten we het hele idee van de spreadsheet (die voor ons rekent) volledig voorbij. 
Zijn er mogelijkheden qua zoeken/vervangen misschien?

[Bloom]

Ja. Je kunt bij zoeken/vervangen onderaan links op "Meer" klikken en dan zie je een optie om regulier expressies aan te vinken.
Je kunt zoeken en vervangen met reguliere expressies om alle gevallen van "ALS(iets;" te vervangen door "ALS(ISGETAL(iets);"
En als je meerdere andere ALS-formules in je spreadsheet hebt staan die niet allemaal aangepast moeten worden, kun je ook uitgebreider zoeken op de vorm ALS(iets;iets*iets;"") en dan de eerste 'iets' vervangen door 'isgetal(iets)'.
Maar je moet natuurlijk wel iets afweten van reguliere expressies.

[10o]

Mooi dat het kan!
Helaas krijg ik nog de melding "Zoekbegrip niet gevonden" als ik op "Alles vervangen" druk.
Zie afbeelding... Enig idee hoe dat kan?

[Bloom]

Je hebt geen reguliere expressies gebruikt!

[10o]

  Ik snap je uitleg blijkbaar niet...
Onder de button "Meer" heb ik "Regulier uitdrukkingen" aangevinkt...

Wat bedoel je in de uitleg met "iets"? Moet ik daar een concrete celaanduiding gebruiken of is "iets" een soort wildcard binnen OpenOffice?

Op ieder tabblad heb ik vele ALS formules staan, bijvoorbeeld:

Code: [Selecteer]

=ALS('01'.C10;'01'.C10;"")
=ALS('01'.C11;'01'.C11;"")
=ALS('01'.D9;'01'.D9;"")
=ALS(L10;L10*C10;"")
=ALS(L10;L10*D10;"")

En op ieder volgende tabblad wordt weer verwezen naar het tabblad daarvoor. Dus daar staat:

Code: [Selecteer]

=ALS('02'.C10;'01'.C10;"")
=ALS('02'.C11;'01'.C11;"")
=ALS('02'.D9;'01'.D9;"")
=ALS(L10;L10*C10;"")
=ALS(L10;L10*D10;"")


Graag zou ik deze allemaal in één klap vervangen. Binnen die logica dacht ik aan "=ALS(" naar "=ALS(ISGETAL(", maar dat kan kennelijk niet. Bovendien ontbreekt dan een sluithaakje ")".

Zou je mij misschien een concreet voorbeeld in dezen kunnen geven? Welke twee waarden dien ik in te vullen?

Bedankt voor de hulp!

[Bloom]

Doe Bewerken/Zoeken en vervangen... (of tik ctrl+F).
Gebruik als zoekveld:

Code: [Selecteer]

^=ALS\((.+)\;(.+)\;(.+)\)Gebruik als vervangveld:

Code: [Selecteer]

=ALS(ISGETAL($1);$2;$3)Klik op het veld 'Meer' en vink aan:
+ Reguliere expressies
en helemaal onderaan:
+ zoek in alle bladen
Klik nu op "Vervang alles". Klaar!

[10o]

Fantastisch, zo werkt het. Bedankt!
Maar nu loop ik alweer tegen het volgende probleem aan. Was blijkbaar nog een paar relevante voorbeelden vergeten:

Code: [Selecteer]

=ALS(SOM(E8:K8)>0;SOM(E8:K8);"") is na vervanging geworden

Code: [Selecteer]

=ALS(ISGETAL(SOM(E8:K8)>0);SOM(E8:K8);"")
In de vervangen variant levert dat "0" op i.p.v. een leeg veld, waardoor een vermenigvuldiging verderop vastloop omdat het andere veld dat daarbij betrokken is, wel  leeg is gebleven (en niet 0)...

Verder heb ik nog een aantal "vervelende" totaalvelden in ieder kolom die ook niet meegepakt worden in de vervanging, zoals:

Code: [Selecteer]

=SOM(ALS($C7;E7*$C7;0);ALS($C8;E8*$C8;0);ALS($C9;E9*$C9;0);ALS($C10;E10*$C10;0);ALS($C11;E11*$C11;0);ALS($C12;E12*$C12;0);ALS($C13;E13*$C13;0);ALS($C14;E14*$C14;0);ALS($C15;E15*$C15;0);ALS($C16;E16*$C16;0);ALS($C17;E17*$C17;0);ALS($C18;E18*$C18;0);ALS($C19;E19*$C19;0);ALS($C20;E20*$C20;0);ALS($C21;E21*$C21;0);ALS($C22;E22*$C22;0);ALS($C23;E23*$C23;0);ALS($C24;E24*$C24;0);ALS($C25;E25*$C25;0))
Vreselijk balen dat deze nieuwe versie er plotseling andere spelregels op nahoudt!
Ik kan me voorstellen dat het door bovenstaande onherstelbaar wordt. Is er een mogelijkheid om te downgraden naar 3.1.1? Ik heb net een bestand gedownload OOo_3.1.1_Linux_x86-64_install-deb_nl.tar.gz, maar daar zitten zoveel DEB'jes in, dat ik niet weet waar ik beginnen moet. Als dat zou kunnen, kan ik in ieder geval dit kalenderjaar afmaken en 1 januari met een schone lei beginnen en een 3.2 compatible opzet maken...

[Bloom]

Code: [Selecteer]

=ALS(SOM(E8:K8)>0;SOM(E8:K8);"") is na vervanging geworden

Code: [Selecteer]

=ALS(ISGETAL(SOM(E8:K8)>0);SOM(E8:K8);"")

Ja, aan zoiets moet je natuurlijk wel denken. Je kunt na de vervanging een nieuwe zoek&vervang uitvoeren om die als(som(-formules weer te herstellen:
zoekveld:

Code: [Selecteer]

^=ALS\(ISGETAL\(SOM\((.+)\;(.+)\;(.+)\)vervangveld:

Code: [Selecteer]

=ALS(SOM($1);$2;$3)Dit zou de foute wijzigingen weer ongedaan moeten maken.

Verder heb ik nog een aantal "vervelende" totaalvelden in ieder kolom die ook niet meegepakt worden in de vervanging, zoals:

Code: [Selecteer]

=SOM(ALS($C7;E7*$C7;0);ALS($C8;E8*$C8;0);ALS($C9;E9*$C9;0);ALS($C10;E10*$C10;0);ALS($C11;E11*$C11;0);ALS($C12;E12*$C12;0);ALS($C13;E13*$C13;0);ALS($C14;E14*$C14;0);ALS($C15;E15*$C15;0);ALS($C16;E16*$C16;0);ALS($C17;E17*$C17;0);ALS($C18;E18*$C18;0);ALS($C19;E19*$C19;0);ALS($C20;E20*$C20;0);ALS($C21;E21*$C21;0);ALS($C22;E22*$C22;0);ALS($C23;E23*$C23;0);ALS($C24;E24*$C24;0);ALS($C25;E25*$C25;0))

Dit kan ook, maar dan moet je zoeken op respectievelijk "=SOM(ALS..." en ";ALS..." en vervangen door een licht aangepaste string (resp. =SOM( en : voor de ALS toevoegen), dan wordt dat met twee zoek&vervangopdrachten opgelost.

Vreselijk balen dat deze nieuwe versie er plotseling andere spelregels op nahoudt!

Ik moet bekennen dat ik niet weet waarom het bij jou fout gaat. Ik heb zelf ook versie 3.2 draaien en bij mij gaat een ctrole op een cel zonder die isgetal-functie wel goed zonder een waardefout.
Maar met die isgetal-functie zal het onder alle omstandigheden en met alle versies werken. Het is best om nooit zomaar getallen te veronderstellen maar dat altijd te controleren als je geen uitvoeringsfouten wil krijgen.

Ik kan me voorstellen dat het door bovenstaande onherstelbaar wordt. Is er een mogelijkheid om te downgraden naar 3.1.1? Ik heb net een bestand gedownload OOo_3.1.1_Linux_x86-64_install-deb_nl.tar.gz, maar daar zitten zoveel DEB'jes in, dat ik niet weet waar ik beginnen moet. Als dat zou kunnen, kan ik in ieder geval dit kalenderjaar afmaken en 1 januari met een schone lei beginnen en een 3.2 compatible opzet maken...

Die bende deb's bevatten alle onderdelen van OpenOffice. In feite hoef je voor jouw toepassingen alleen Calc te downgraden.
Ik zou dat echter niet doen. Pas gewoon je formules aan zodat ze numeriek correct zijn, dan heb je ook in de toekomst geen problemen meer.

[10o]

@Bloom, ik ga me morgen eens buigen over je uitgebreide uitleg en kijken hoever ik kom met herstel middels zoeken/vervangen. Uitdagend puzzeltje...
Ik had de oude versie van het bestand (van voor de vervangingen) natuurlijk gebackupt, dus ik kan nog alle kanten op.

[10o]

Beste Bloom,

Stap voor stap kom ik iets verder, maar de voorgestelde (her)vervanging levert een Fout:508 op.

zoekveld:

Code: [Selecteer]

^=ALS\(ISGETAL\(SOM\((.+)\;(.+)\;(.+)\)vervangveld:

Code: [Selecteer]

=ALS(SOM($1);$2;$3)

Die foutmelding komt voort uit twee sluithaakjes teveel in het resultaat: =ALS(SOM(E8:K8)>0));SOM(E8:K8);"")

Heb het e.e.a. gestoeid met het vervangveld, maar ik kom vooralsnog niet tot het gewenste resultaat zonder die twee overbodige sluithaakjes...

Als ik dit resultaat kan bereiken door deze SOM-formules bij de eerst voorgestelde vervanging uit te sluiten, ben ik ook content.  
Dat was deze:

Gebruik als zoekveld:

Code: [Selecteer]

^=ALS\((.+)\;(.+)\;(.+)\)Gebruik als vervangveld:

Code: [Selecteer]

=ALS(ISGETAL($1);$2;$3)Klik nu op "Vervang alles". Klaar!

[Bloom]

Het vervangveld moet dit zijn:
^=ALS\(ISGETAL\(SOM\((.+)\)\;(.+)\;(.+)\)
Het is inderdaad ook mogelijk om alles in één keer te doen, maar ik wou je niet overbelasten...

[10o]

Bedankt voor de supersnelle reactie!!!

Je bedoelde waarschijnlijk het zoekveld, dus heb ik nu gedaan:
Zoekveld:

Code: [Selecteer]

^=ALS\(ISGETAL\(SOM\((.+)\)\;(.+)\;(.+)\)vervangveld:

Code: [Selecteer]

=ALS(SOM($1);$2;$3)
maar dan hou ik nog een haakje teveel over: =ALS(SOM(E8:K8)>0);SOM(E8:K8);"")

Dus dacht ik, ik probeer als zoekveld ^=ALS\(ISGETAL\(SOM\((.+)\)\)\;(.+)\;(.+)\) maar die wordt niet gevonden...

[Bloom]

Ik bedoelde inderdaad het zoekveld. Probeer dit eens:

Code: [Selecteer]

^=ALS\(ISGETAL\(SOM\((.+)\)\;(.+)\;(.+)\)Vervangveld:

Code: [Selecteer]

=ALS(SOM($1;$2;$3)

[10o]

Fantastisch. That's the one!  

Durf het bijna niet te vragen... er is nog een laatste uitdaging over:

Code: [Selecteer]

=SOM(ALS($C7;E7*$C7;0);ALS($C8;E8*$C8;0);ALS($C9;E9*$C9;0);ALS($C10;E10*$C10;0);ALS($C11;E11*$C11;0);
ALS($C12;E12*$C12;0);ALS($C13;E13*$C13;0);ALS($C14;E14*$C14;0);ALS($C15;E15*$C15;0);ALS($C16;E16*$C16;0);
ALS($C17;E17*$C17;0);ALS($C18;E18*$C18;0);ALS($C19;E19*$C19;0);ALS($C20;E20*$C20;0);ALS($C21;E21*$C21;0);
ALS($C22;E22*$C22;0);ALS($C23;E23*$C23;0);ALS($C24;E24*$C24;0);ALS($C25;E25*$C25;0))
tot en met

Code: [Selecteer]

=SOM(ALS($C7;K7*$C7;0);ALS($C8;K8*$C8;0);ALS($C9;K9*$C9;0);ALS($C10;K10*$C10;0);ALS($C11;K11*$C11;0);
ALS($C12;K12*$C12;0);ALS($C13;K13*$C13;0);ALS($C14;K14*$C14;0);ALS($C15;K15*$C15;0);ALS($C16;K16*$C16;0);
ALS($C17;K17*$C17;0);ALS($C18;K18*$C18;0);ALS($C19;K19*$C19;0);ALS($C20;K20*$C20;0);ALS($C21;K21*$C21;0);
ALS($C22;K22*$C22;0);ALS($C23;K23*$C23;0);ALS($C24;K24*$C24;0);ALS($C25;K25*$C25;0))

[10o]

Ik heb geprobeerd:

Zoeken:

Code: [Selecteer]

;ALS\((.+)\;(.+)\;(.+)\)Vervangen:

Code: [Selecteer]

;ALS(ISGETAL($1);$2;$3)
Het resultaat is dat alleen de tweede ALS vervangen is:

Code: [Selecteer]

=SOM(ALS($C7;E7*$C7;0);ALS(ISGETAL($C8;E8*$C8;0);ALS($C9;E9*$C9;0);ALS($C10;E10*$C10;0);ALS($C11;E11*$C11;0);
ALS($C12;E12*$C12;0);ALS($C13;E13*$C13;0);ALS($C14;E14*$C14;0);ALS($C15;E15*$C15;0);ALS($C16;E16*$C16;0);
ALS($C17;E17*$C17;0);ALS($C18;E18*$C18;0);ALS($C19;E19*$C19;0);ALS($C20;E20*$C20;0);ALS($C21;E21*$C21;0);
ALS($C22;E22*$C22;0);ALS($C23;E23*$C23;0);ALS($C24;E24*$C24;0);ALS($C25);E25*$C25;0))
Dat hij de eerste =SOM(ALS...); niet vervangt is duidelijk, maar het lijkt alsof de rest van de formule niet recursief meegepakt wordt door de vervangopdracht...

[10o]

Ook met mijn vervangpoging van het SOM gedeelte mis ik wat sluithaakjes in het resultaat.
Mijn poging:
Zoeken:

Code: [Selecteer]

^=SOM\(ALS\((.+)\)\;(.+)\;(.+)\);Vervangen:

Code: [Selecteer]

=SOM(ALS(ISGETAL($1);$2;$3));
Resultaat:

Code: [Selecteer]

=SOM(ALS(ISGETAL($C7;E7*$C7;0);Terwijl ik aan dacht te sturen op:
=SOM(ALS(ISGETAL($C7);E7*$C7;0));

Het hele principe achter de vervangopdrachten is me blijkbaar nog niet helemaal duidelijk (understatement)...

[Bloom]

Ook met mijn vervangpoging van het SOM gedeelte mis ik wat sluithaakjes in het resultaat.
Mijn poging:
Zoeken:

Code: [Selecteer]

^=SOM\(ALS\((.+)\)\;(.+)\;(.+)\);Vervangen:

Code: [Selecteer]

=SOM(ALS(ISGETAL($1);$2;$3));

Je bent een paar dingen door elkaar aan het halen. De eerste zoek-vervangoperatie die ik gaf hoefde je niet te wijzigen. Die extra \) in het zoekveld moet er dus uit of je moet hem toevoegen in het vervangveld (met name ISGETAL($1)) dus een haakje extra). Eén van deze twee kiezen en dan is het weer in orde.

[Bloom]

Code: [Selecteer]

=SOM(ALS($C7;E7*$C7;0);ALS(ISGETAL($C8;E8*$C8;0);ALS($C9;E9*$C9;0);ALS($C10;E10*$C10;0);ALS($C11;E11*$C11;0);
ALS($C12;E12*$C12;0);ALS($C13;E13*$C13;0);ALS($C14;E14*$C14;0);ALS($C15;E15*$C15;0);ALS($C16;E16*$C16;0);
ALS($C17;E17*$C17;0);ALS($C18;E18*$C18;0);ALS($C19;E19*$C19;0);ALS($C20;E20*$C20;0);ALS($C21;E21*$C21;0);
ALS($C22;E22*$C22;0);ALS($C23;E23*$C23;0);ALS($C24;E24*$C24;0);ALS($C25);E25*$C25;0))

Dit is veel ingewikkelder dan nodig. Ik stel voor dat je de formule vervangt door:

Code: [Selecteer]

=SOMPRODUCT(E7:E25;$C7:$C25)De controle op 'is het wel een getal?' is hier overbodig omdat de sommage-opdracht automatisch alle niet-numerieke waarden overslaat.

[10o]

Je bent een paar dingen door elkaar aan het halen. De eerste zoek-vervangoperatie die ik gaf hoefde je niet te wijzigen. Die extra \) in het zoekveld moet er dus uit of je moet hem toevoegen in het vervangveld (met name ISGETAL($1)) dus een haakje extra). Eén van deze twee kiezen en dan is het weer in orde.

Oké, dan probeer ik
Zoeken:

Code: [Selecteer]

^=SOM\(ALS\((.+)\;(.+)\;(.+)\);Vervangen:

Code: [Selecteer]

=SOM(ALS(ISGETAL($1);$2;$3));
Of
Zoeken:

Code: [Selecteer]

^=SOM\(ALS\((.+)\)\;(.+)\;(.+)\);Vervangen:

Code: [Selecteer]

=SOM(ALS(ISGETAL($1));$2;$3));;
Maar in beide gevallen is het resultaat:

Code: [Selecteer]

=SOM(ALS(ISGETAL($C7;E7*$C7;0);Dus mis ik nog steeds twee sluithaakjes...

Misschien kan je mij de code geven van hoe het wel moet? Da's voor mij makkelijker te begrijpen dan een tekstuele uitleg.

[10o]

Code: [Selecteer]

=SOM(ALS($C7;E7*$C7;0);ALS(ISGETAL($C8;E8*$C8;0);ALS($C9;E9*$C9;0);ALS($C10;E10*$C10;0);ALS($C11;E11*$C11;0);
ALS($C12;E12*$C12;0);ALS($C13;E13*$C13;0);ALS($C14;E14*$C14;0);ALS($C15;E15*$C15;0);ALS($C16;E16*$C16;0);
ALS($C17;E17*$C17;0);ALS($C18;E18*$C18;0);ALS($C19;E19*$C19;0);ALS($C20;E20*$C20;0);ALS($C21;E21*$C21;0);
ALS($C22;E22*$C22;0);ALS($C23;E23*$C23;0);ALS($C24;E24*$C24;0);ALS($C25);E25*$C25;0))

Dit is veel ingewikkelder dan nodig. Ik stel voor dat je de formule vervangt door:

Code: [Selecteer]

=SOMPRODUCT(E7:E25;$C7:$C25)De controle op 'is het wel een getal?' is hier overbodig omdat de sommage-opdracht automatisch alle niet-numerieke waarden overslaat.

Dat is inderdaad een veel elegantere oplossing! Maar kan dit ook met zoeken/vervangen? Anders moet ik een rij met 7 kolommen in 53 tabbladen aanpassen...

[Bloom]

Oké, dan probeer ik
Zoeken:

Code: [Selecteer]

^=SOM\(ALS\((.+)\;(.+)\;(.+)\);Vervangen:

Code: [Selecteer]

=SOM(ALS(ISGETAL($1);$2;$3));
Of
Zoeken:

Code: [Selecteer]

^=SOM\(ALS\((.+)\)\;(.+)\;(.+)\);Vervangen:

Code: [Selecteer]

=SOM(ALS(ISGETAL($1));$2;$3));;
Maar in beide gevallen is het resultaat:

Code: [Selecteer]

=SOM(ALS(ISGETAL($C7;E7*$C7;0);Dus mis ik nog steeds twee sluithaakjes...

Misschien kan je mij de code geven van hoe het wel moet? Da's voor mij makkelijker te begrijpen dan een tekstuele uitleg.

Je hebt overal een ';' aan het einde bijgeplakt en dat is dus fout.
zoekveld:

Code: [Selecteer]

^=ALS\((.+)\;(.+)\;(.+)\)vervang:

Code: [Selecteer]

=SOM(ALS(ISGETAL($1);$2;$3))is de juiste opdracht.