Waarde vervangen Excel

[mchoffa]

Beste allemaal,

Na een hele dag googlen en zelf een en ander proberen kom ik er helaas niet uit, vandaar dat ik jullie hulp vraag!

Ik werk in een groot bestand met ±50.000 datapunten, 25 regels onder elkaar met verschillende fondsen en een dagelijkse tijd lijn van juni 2010 tot augustus 2015 waar voor elk fonds de slotkoers van die dag is weergeven. Mijn probleem is dat er ongeveer 9.000 van deze datapunten de waarde nul hebben waardoor ik met berekeningen later hele rare waardes krijg. Nu probeer ik deze 0 waardes te vervangen door de eerste waarde links van de 0 waarde (de waarde van 1/2/3 dagen voor de datum van de 0 waarde dus).
In eerste instantie dacht ik om ctr-H te gebruiken en dan bij zoeken naar 0 te laten vervangen door een formule die deze functie uitvoert. Ik kan deze formule echter nergens vinden. Weet een van jullie hem? Of heeft iemand een ander idee hoe ik dit kan oplossen?

Vriendelijk bedankt!

[jackaubrey]

ik denk dat je hier iets mee zou kunnen doen https://support.office.co...38-4dbe-a33f-955d67c2b2cf

[F_J_K]

Opties:
1) VBA-scriptje maken

2) Hulpkolom. Pseudocode: IF cel links = 0 THEN waarde cel twee posities naar links ELSE waarde cel links. En die formule in de hele kolom kopiëren en voortaan die kolom gebruiken. Dus met de formule die captain Jack hierboven linkt.

[mchoffa]

Die tweede optie dacht ik ook aan, het probleem is alleen dat ik dan 9000 data punten zou moeten invullen. Is het niet mogelijk om door middel van ctr-H alle 0 waardes te vervangen door een universele formule? iets zoals ''=waardecellinks''?

[F_J_K]

Dat zeg ik: in 1 nieuwe cel een werkende formule zetten, daarna naar beneden kopiëren om een geheel gevulde hulpkolom te maken.

offtopic:
Niet vervelend maar behulpzaam bedoeld: ga geen financiële beslissingen nemen o.b.v. Excelsheets als je niet zeker weet hoe de formules werken. Wees eerst zeker dat het klopt, en test en documenteer dat ook.

[Tazzios]

Hier heb je een zoeken vervangen voor de cel rechts: "=VERSCHUIVING(A1;RIJ()-1;KOLOM())" links wilde 1,2,3 niet lukken.

Edit:
Hebbes!
=VERSCHUIVING(A1;RIJ()-1;KOLOM()-2;1;1)

[ Voor 21% gewijzigd door Tazzios op 03-09-2015 14:04 ]

[F_J_K]

Dat is inderdaad een manier waarop het met zoeken en vervangen wel gaat lukken Dan overigens KOLOM()-2 als je een links van de huidige kolom wilt komen. (edit: zegt spuit11)

Dit zou ik overigens ook zeker goed documenteren. En er op letten dat je niet 10 vervangt door 1VERSCHUIVING, etc. Immers worden alle 0'en vervangen als je niet oplet

[ Voor 3% gewijzigd door F_J_K op 03-09-2015 14:10 ]

[mchoffa]

Ik heb geen idee hoe maar het werkt! (THANKS)
Zou je dat nog kunnen uitleggen?
De uitleg page van office zelf wordt ik ook niet veel wijzer van (https://support.office.co...79-4b9b-a14e-b4d906d11b66). Ook komt er bij 1 regel automatisch een apostrof (') voor waardoor hij het in die regel niet doet. Wat kan hiervan de oorzaak zijn?

[Tazzios]

Verschuiven; Ten opzichte van A1 moet je de cel pakken die x rijen lager ligt en x kolommen op zijn.
Verder de Rij en kolom formule; beide -1 i.v.m. de nummering van de cellen die net wat afwijkt, en kolom dan -2 omdat je geen kringverwijzing wil hebben maar de cel links van de formule cel. De 2 '1'en aan het eidn weet ik ook niet.
Vervang de rij() en de kolom() even anders voor een getal als je de werking beter wilt begrijpen.

Cel ingesteld als tekst veld?

[ Voor 18% gewijzigd door Tazzios op 03-09-2015 15:56 ]

[mchoffa]

Ik snap het, maar waarom A1?

Staat gewoon als getal, wat ook heel gek is, is dat hij het soms wel doet en af en toe niet (ook zonder apostrof)

[F_J_K]

Waarom niet? Omdat je in iedere cel dezelfde formule hebt staan vanwege find&replace, kan je niet uitgaan van een variabele cel. Je kunt iedere cel als uitgangspunt nemen, maar A1 is het makkelijkste om mee te rekenen. Zoals Tazzios in "Waarde vervangen Excel" is 'spelen' met de formule goed om te begrijpen hoe het werkt.

Vwb de rest zou het helpen als je meer info gaf over de precieze inrichting vd sheet. Wat is er anders dan een paar regels erboven of beneden?

[Verwijderd]

Dit kan je ook eenvoudig oplossen met een macro (in dit voorbeeld is de kolom met data B en begint vanaf rij 2):

code:

Sub Macro1()
Dim cell As Range
Range("B2", Range("B2").End(xlDown)).Select
For Each cell In Selection
If cell = 0 Then
    cell.Value = cell.Offset(0, -1).Value
End If
Next cell
End Sub

Waarbij je B2 in regel 3 vervangt voor de eerste cel met waarde in de kolom waarin je naar onder toe wilt gaan vervangen. Vervolgens selecteert de code in deze alle cellen met data in de rest van de kolom. Hierna loopt hij door alle cellen heen en vervangt alle cellen met waarde 0 met de waarde van de cel die hier links naast staat.
Als je dus steeds dezelfde kolom gebruik kunt je met deze macro met 1 keer klikken al je data aanpassen in de toekomst.

[F_J_K]

Ook hier geldt trouwens; enkel gebruiken als je weet wat het doet!

[The Eagle]

Ik neem aan dat jij met een soort van financiele analyse aan de slag moet?
If so, verdiep je eens in R studio (en uberhaupt in R).
Daar lees je dat bestand zo in en kun je er relatief eenvoudig allerlei berekeningen op loslaten, waar Excel stevig moet gaan zitten crunchen. Ook kun je aangeven wat er met NULL waardes moet gebeuren. Tip: dat is bij statistische analyses niet zomaar te vervangen door een harde waarde. Vaak zie je dat deze regels gewoon geheel genegeerd worden, of men die NULL vervangt door een mediaan / gemiddelde. Maar wat de beste optie is, is geheel afhankelijk van wat je uiteindelik met die data wilt

[mchoffa]

@F_J_K
Het rare is, is dat er niks anders is aan de regel erboven of onder en dat de formule het binnen die regel in sommige kolommen wel doet en in andere niet.

@The Eagle
Het is vooralsnog een eenmalige opdracht, maar ik zal er even naar kijken!

EDIT
Ik snap nu hoe het komt dat hij het af en toe wel en niet doet, was een foutje van mij. Echter als ik het probeer op te lossen met vervangen, zelfs als ik de opmaak als getal doe, komt er een apostrof voor, waardoor het dus niet meer werkt. Iemand oplossing?

[ Voor 35% gewijzigd door mchoffa op 04-09-2015 10:14 ]

[F_J_K]

Misschien zou je de relevante delen van de sheet kunnen uploaden.

[mchoffa]

Ik heb het nu zo ver dat de apostrof weg is, maar hij gaat de formule pas laten werken als ik er op ga staan en op enter druk, net alsof ik hem net typ.

Ik zal even een screenshot maken en z.s.m. uploaden.

edit


Het gaat om regel 40, in regel ''I40'' staat zoals jullie zien de formule zonder apostrof, in de kolom links er van staat precies dezelfde formule alleen heb ik daar op ''ENTER'' gedrukt.

edit 2

Alle '='jes laten vervangen door nieuwe '='jes deed de job!
Bedankt voor jullie hulp, wat mij betreft een slotje.

[ Voor 90% gewijzigd door mchoffa op 04-09-2015 11:15 ]