• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Formule automatisch overnemen bij invoegen extra rijen

Status
Niet open voor verdere reacties.
W

Wocky

Gebruiker
Lid geworden
22 feb 2014
Berichten
192
Beste,

Ik heb een tabel gemaakt met in 1 kolom een formule.
Om de "zoveel" rijen dient die formules een klein beetje te veranderen.

Als ik nu rijen invoeg, dan plaatst excel niet de juiste formule in de cel.

Ik heb al gevonden dat als je een "tabel" maakt van de cellen, dan kan je eventueel een bepaalde formule automatisch laten invullen in een bepaalde kolom.
Maar in mijn geval dient de formule dus om de "x-aantal" rijen te veranderen.

Is er een mogelijkheid om dit wel juist te laten gebeuren?


In bijlage een voorbeeld.
Alvast bedankt!
Wocky.
 

Bijlagen

Zet deze in F1 en trek die naar beneden.
Nu kan je rijen verwijderen en/of invoegen.

Code: 
=INDIRECT("E"&RIJ())-INDIRECT("B"&RIJ())
 
Of in F1 en naar beneden:
Code: 
=E1-INDEX(B$1:B1;VERGELIJKEN(9,99999999999999E+307;B$1:B1))
 
Of nog iets compacter:
Code: 
=E1-ZOEKEN(9,99999999999999E+307;$B$1:B1)
 
PHP: 
=E1-offset(B$1;10*int(row()/11);0)
 
Beste hulpverleners,

Alvast bedankt voor jullie denkwerk en oplossingen.
Bij mij werkt echter enkel oplossing 2 en 3, maar ik weet eigenlijk niet goed wat er juist gebeurd... dat met die 9.999€+307 begrijp ik ook niet zo goed.

Ik ga er vanuit dat ik iets essentieels miszie bij de andere oplossingen.
Moesten jullie tijd en zin hebben om een woordje uitleg, dan lees ik het graag.

In bijlage alvast de formules ingevuld.

Alvast bedankt.
Wocky.
 

Bijlagen

Die 9,99999999999999E+307 is het grootst mogelijke getal dat je in Excel kunt opgeven.
In beide gevallen is de essentie dat je zoekt naar een groter getal dan het grootste getal dat in de data kan voorkomen; als je weet dat dat maximaal 1000 kan zijn, dan kun je ook zoeken naar 1001 i.p.v. naar 9,9999 etc.
Die 9,99999999999999E+307 is vooral een kwestie van principe.

Het resultaat is de plek waar het laatste getal voorkomt (met VERGELIJKEN), respectievelijk het laatste getal zelf (met ZOEKEN).
 
Kleine aanpassing

PHP: 
=E1-OFFSET(B$1;10*INT((ROW()-1)/10);0)

NB. te beginnen in rij 1
 
Laatst bewerkt:
Allen,

Nogmaals bedankt voor de reacties.
De werking van de formules heb ik ondertussen door, na jullie uitleg te lezen en wat f1 opzoekhulp.

Maar ik heb volgende bevindingen.
Enkel de 2de en de 3de oplossing werken bij mij, en ze werken enkel als ze worden toegepast in een tabel (waar de formule automatisch verder gekopieerd wordt in de gehele kolom).
Het punt is dat ik rijen moet kunnen toevoegen (of verwijderen), terwijl de formule automatisch ingevuld wordt (blijkbaar enkel mogelijk in een tabel...?)

In bijlage heb ik 3 bestandjes waar ik rijen (geel) heb toegevoegd en getallen (rood) heb ingevuld.
1: lijnen ingevoegd zonder toepassing van tabel (geen enkele formule werkt)
2: lijnen toegevoegd met toepassing van tabel (enkel 2 en 3 werkt)
3: lijnen toegevoegd met toepassing van tabel+aanpassing formule 4 (2 en 3 werkt. 4 wil werken maar heeft een probleem met de automatische toekenning v/d formule op de gehele kolom). Ik heb de formule zelf ook wat aangepast... die *10 en /10 verander naar *RIJ() en /RIJ()... hoewel ik ook niet goed begrijp waarom dit te vermenigvuldigen en te delen...

Dit zijn althans mijn bevindingen. Maar zoals gezegd.. ik ben geen pro.

Anyway, ik denk dat ik voorlopig hiermee wel verder kan!

Alvast bedankt!
Wocky.
 

Bijlagen

Beste,

Ik heb nog eens nagedacht..
Ik begrijp eigenlijk toch niet zo goed hoe opl. 2&3 werken.

Je laat ze telkens zoeken naar een gelijk of dichtstbij zijnde kleinere getal dan 9,99999999999999E+307 (of bvb 1000).
Maar hoe weet excel bij bvb de middelste reeks rijen dat hij de "3" moet nemen? Want die "5" valt toch ook in het opgegeven zoekbereik?
En 5 ligt toch dichter bij 9,99999999999999E+307 dan 3?

Kan je nog even verklaren?

Alvast bedankt!
Wocky.
 
Wees niet verbaasd dat een formule andere resultaten geeft:

- als je de struktuur van het werkblad wijzigt
- je de formule niet exact overneemt.
 

Bijlagen

De functie ZOEKEN is eigenlijk gemaakt om te zoeken in een bereik met aaneengesloten, oplopend gesorteerde waarden.
Het resultaat van ZOEKEN is dan de laatste waarde in het bereik die kleiner is dan - of gelijk aan - de zoekwaarde.
Als je zoekt in een ongesorteerd bereik met lege cellen er in, dan geeft ZOEKEN nog steeds de laatste numerieke waarde die kleiner is dan - of gelijk aan - de zoekwaarde.
Een eigenschap van ZOEKEN is dat het bereik van alles mag bevatten, inclusief foutwaarden; alles dat niet numeriek is, wordt genegeerd.

Als in jouw voorbeeld (getallen 5 3 en 10 met lege cellen er tussen), de zoekwaarde 9 zou zijn, zou je 3 als resultaat krijgen, het laatste getal kleiner/gelijk 9.
 
Dag Marcel,

Waarom geeft hij dan in het 2de deel (de B-sectie) "3" i.p.v. 5? 5 ligt toch dichter bij 999..enz.. dan 3?
Ik zou denken dat het resultaat dan "5" zou zijn...
Of komt dat omdat de data niet aaneensluitend is? en hij dan maar gewoon de laatste waarde die hij tegenkomt neemt?


@snb.
Het punt is dat het werkblad steeds zal wijzigen.
Ik dien te pas en te onpas rijen in te voegen.
Het doel is om de formule niet telkens te moeten doorvoeren dan (en ze correct blijft werken).


Alvast bedankt voor jullie inbreng!
mvg,
Bernd.
 
Wocky zei:
... en hij dan maar gewoon de laatste waarde die hij tegenkomt neemt? ...
Klik om te vergroten...

Zoals ik uitlegde in post #13:
"Als je zoekt in een ongesorteerd bereik met lege cellen er in, dan geeft ZOEKEN nog steeds de laatste numerieke waarde die kleiner is dan - of gelijk aan - de zoekwaarde."

Dus hij neemt niet "gewoon de laatste waarde", maar de laatste waarde die kleiner is dan - of gelijk aan - de zoekwaarde.
 
Beste,

Mijn probleem is blijkbaar nog niet helemaal opgelost...
Het is eigenlijk ook de bedoeling dat ik gegevens kan verslepen in te tabel, terwijl de formules blijven werken.
En ik zou ook nog graag voorwaardelijke opmaak willen gebruiken met een soortgelijke formule.

Zie in bijlage mijn voorbeeld.

Ik heb een versleepbaar gedeelte en een niet versleepbaar gedeelte.
Als ik nu gegevens van sectie A naar sectie B wil verslepen, dan gaat de formule alsnog kapot.
Ook de voorwaardelijke opmaak wordt overschreven en geraakt zo ontregeld.
Weet iemand hiervoor een oplossing?

N.B.: Ik weet niet of dit nog bij dit topic hoort?
Als ik beter een nieuw onderwerp aanmaak hoor ik het graag.


Alvast bedankt!
Wocky.
 

Bijlagen

Wat mij betreft is dit problematiek van een hele andere orde en dus iets voor een nieuw onderwerp.
Of zoek eerst eens op dit forum, want ik krijg nu een sterk déjà-vue gevoel, voorbeelden:
http://www.helpmij.nl/forum/showthread.php/877513-Cellen-als-één-object-verslepen
http://www.helpmij.nl/forum/showthread.php/880889-Voorwaardelijke-opmaak-vastzetten

In het algemeen is slepen redelijk "killing" in een Excel werkblad met formules en zeker met voorwaardelijke opmaak, dat nogal een sterke neiging heeft om te fragmenteren. :rolleyes:
Volgens mij moet je dan zoeken in de richting van VBA dat formules en voorwaardelijke opmaak weer herstelt na een sleepactie.
Mijn advies zou zijn om een alternatief te zoeken voor slepen (bijvoorbeeld kopiëren/plakken) of om toch een nieuw onderwerp te starten.
Overigens zal ik persoonlijk niet in staat zijn om je te helpen met een eventuele VBA oplossing, want daar ben ik vooralsnog onvoldoende in thuis.
 
Dag Marcel,

Mja inderdaad de 2de link geeft wat betreft de opmaak precies weer wat mijn probleem is.
Ik begrijp ook gewoon niet waarom je die voorwaardelijke opmaak niet kan beveiligen...
Ik denk dat er heel veel gebruikers zijn van excel die het blad als een groot magneetbord beschouwen, maar dan digitaal.
Vooral voor planning.
En op een magneetbord wil je gewoon dingen verschuiven. Knippen werkt ook niet.. formule gaat dan blijkbaar ook kapot. Kopieren gaat wel.. maar dan moet je telkens je oorspronkelijke data wissen... wat niet gebruiksvriendelijk is als je veel moet schuiven.

Misschien moet het inderdaad wel zoiets zijn dat de formules en opmaak zichzelf herstellen na elke schuifbeweging.. ik zou daar een macro voor kunnen opnemen.
Maar.. mja dan moet telkens op een knop klikken om het te laten gebeuren.. zou realtime moeten zijn dan.. misschien kan ik wel eens vragen op het forum.

Ben trouwens om deze reden vba aan het leren.. maar ja.. dat vergt natuurlijk tijd.

Maar toch alvast voor je hulp!

Mvg,
Wocky.

N.B.: Ik heb al verschillende keren gezocht naar een oplossing voor het vastzetten van die voorwaardelijke opmaak.. al een paar jaren.
Ik heb enkel nog maar mensen gevonden met hetzelfde probleem...
 
Excuseer.. kopiëren gaat ook niet.
Voorwaardelijke opmaak fragmenteert dan.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan