Hallo

Welkom, Gast. Alsjeblieft inloggen of registreren.

Recent

203 gasten, 0 leden

Welkom, Gast. Alsjeblieft inloggen of registreren.

28 maart 2024, 11:35:39

Login met gebruikersnaam, wachtwoord en sessielengte

Nieuws

Welkom op het vernieuwde NL Computer Forum!

Auteur Topic: Excel: Laatste cel met waarde > 0  (gelezen 17264 keer)

0 leden en 1 gast bekijken dit topic.

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Excel: Laatste cel met waarde > 0
« Gepost op: 21 maart 2013, 23:59:15 »
Hoi allemaal!

(blaast stof van account)
Volgens mij is het best wel een tijdje geleden dat ik hier voor het laatst was. Benieuwd hoeveel mensen me nog herkennen...

Anyway, ik heb dus een probleempje met Excel. Excel 2010, om precies te zijn, al hoop ik op een oplossing die op alle versies werkt.

Ik heb een rij cellen (J16:AN16). In elk van die cellen staat een formule die soms op 0 uitkomt en soms op een waarde groter dan nul (altijd een geheel getal, al denk ik niet dat dit uitmaakt). Nu moet ik het volgende doen:
1: Zoek de laatste (meest rechtse) cel in de rij die een waarde > 0 bevat.
2: Bepaal de kolom van die cel
3: Zoek de overeenkomstige cel binnen een andere rij (J2:AN2)
4: Haal de waarde uit die cel

Ik wil dit doen in een formule in een cel. Dus als ik die formule bv intik in cel A1, dan komt in A1 dezelfde waarde te staan als in de cel in rij 2 die boven de laatste waarde >0 van rij 16 staat. Duidelijk?

Ik heb het gevoel dat ik er bijna ben. Gisteravond had ik op een gegeven moment (met dank aan Google, wat proberen, en een heleboel geduld) de volgende formule:
=INDEX(A$2:AN$2;1;MAX(ALS((J16:AN16)>0;KOLOM(J16:AN16);0)))
Als ik de cursor ergens op het woord INDEX zet en dan op de Fx knop klik, dan verschijnt het hulpvenster voor functies waarin de argumenten worden beschreven en waarin ook het resultaat van de functie wordt getoond. Het getoonde resultaat is correct. Maar helaas - dit resultaat staat niet in de cel; in de cel staat de enorm specifieke en behulpzame foutcode #WAARDE.

Ik heb ook vanavond alweer een paar uur gezocht, en toen herinnerde ik me het NL Computer Forum. Heeft iemand hier een idee hoe ik dit kan oplossen?

Bijlage: Een vereenvoudigde versie van mijn spreadsheet waarin ik het probleem reproduceer.
--
Hugo Kornelis, SQL Server MVP

Offline Peter

  • Sysop
  • *****
  • Berichten: 5.683
  • Geslacht: Man
Re: Excel: Laatste cel met waarde > 0
« Reactie #1 Gepost op: 22 maart 2013, 01:06:39 »
Hoi Hugo,

Een voorbeeldje met een macro:
Sub Zoek()
Dim col As Integer

'stap1+2
Dim i As Integer
'doorloop cel AN16 tot J16
For i = 40 To 10 Step -1
    If Cells(16, i).Value > 0 Then
        col = i
        Exit For
    End If
Next

'stap3+4
If col > 0 Then
    If col > 26 Then
        b = "A" & Chr(64 + col - 26)
    Else
        b = Chr(64 + col)
    End If
    w = Cells(2, col).Value
    MsgBox "Het antwoord staat in kolom " & col & "(=" & b & ") en geeft de waarde " & w
Else
    MsgBox "Sorry, geen kolom gevonden."
End If
End Sub


Groeten, Peter

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: Excel: Laatste cel met waarde > 0
« Reactie #2 Gepost op: 22 maart 2013, 10:15:49 »
Hoi Peter,

Hartelijk dank voor je antwoord! Helaas denk ik niet dat ik deze oplossing kan gebruiken. Ik wilde mijn eerste bericht niet meteen langer maken dan Oorlog & Vrede, dus ik had wat dingen weggelaten waarvan ik dacht dat het geen verschil zou maken.

1. Het bereik is niet altijd constant. Ik ben nu bezig met een model spreadsheet waar ik elke maand een kopie van maak voor de gegevens van die maand. In maanden met minder dan 31 dagen verwijder ik één (of meer, in februari) kolommen uit het deel waar ik de dagelijkse gegevens invoer. Als ik dit probleem met een formule in een cel oplos, dan zal Excel, automatisch alle verwijzingen naar een bereik aanpassen (dus bv J16:AN16 wordt J16:AM16). Met een macro lukt dat niet. Dat is nog wel op te lossen (vervang de FOR lus door een WHILE lus en klaar), maar om de TV programma's van de verkoopkanalen te citeren: er is meer.

2. Het gaat in het echt niet om één formule. De formule waar ik nu mee bezig ben komt uiteindelijk in cel AP16. En in cel AO16 komt een vergelijkbare formule, die echter de waarde niet uit rij 2 haalt maar uit rij 16 zelf.
En dan, als de formules correct werken, ga ik ze kopiëren naar een heleboel andere rijen (vandaar de combinatie van absolute verwijzingen naar rij 2 en relatieve verwijzingen naar de andere rijen). Totaal 70 keer. Dus 70 rijen x 2 formules per rij = 140 exemplaren van deze formule.

3. Een macro die ik expliciet moet starten en dan een venster toont met de cel waar ik moet kijken is een goed begin, maar kan niet mijn einddoel zijn. Denk aan die 140 keer! Ik wil dat Excel helemaal zelf de waarde opzoekt en in de doelcel plaatst. Het liefst helemaal automatisch als ik iets wijzig (zoals met normale Excel formules).

Vandaar dus mijn behoefte om dit met een formule op te lossen die alleen de standaard functies van Excel gebruikt. Het stomme is, ik heb deze formule (in iets andere vorm, dat wel) ergens van internet geplukt, en als ik het venster gebruik dat me helpt de parameters te vullen dan lijkt de functie gewoon het correcte resultaat te geven - alleen hij weigert het ook in de cel te zetten. Kennelijk heb ik iets fout gedaan bij het kopiëren van de formule - maar wat?

Als het écht niet kan met een formule, dan zal ik inderdaad naar VBA moeten uitwijken. Maar dan nog zal ik verder moeten werken vanaf jouw code. Daar heb ik dan vast ook wel weer vragen over, maar ik geef er op dit moment de voorkeur aan om nog even langer te zoeken naar een oplossing met formules.

Groetjes,
Hugo
--
Hugo Kornelis, SQL Server MVP

Offline Hugo

  • Erelid
  • *****
  • Berichten: 101
  • Geslacht: Man
Re: Excel: Laatste cel met waarde > 0
« Reactie #3 Gepost op: 22 maart 2013, 16:53:57 »
Aha! Ik heb de oplossing gevonden. Kennelijk maak ik hier gebruik van een zogenaamde "matrix formule". Dat stond weliswaar erbij vermeld op een site waar ik deze had gevonden, maar wat er niet bij stond is dat je die moet invoeren met Ctrl-Shift-Enter in plaats van gewoon Enter.

Om het te laten werken moest ik wel de formule in twee stukken knippen, want kennelijk kan die Ctrl-Shift-Enter truc niet voor de hele functie (is vast een logische verklaring en/of een workaround voor, maar het werkt nu dus ik ben allang blij). In cel AV15 staat nu deze formule, met Ctrl-Shift-Enter ingevoerd:
=MAX(ALS((J16:AU16)>0;KOLOM(J16:AU16);""))

Dan heb in AW15 een extra formule (nog een eerder weggelaten detail - het eindresultaat mag alleen onder bepaalde voorwaarden getoond worden; de buitenste IF is voor als er op de hele rij nog niks is ingevuld en de binnenste IF is de voorwaarde):
=ALS(AV15=0;"";ALS(INDEX(A$1:AU$1;AV15) < $A$1;"";AV15))

En in de twee cellen daaronder, AV16 en AW16, staan dan de formules die me het eindresultaat geven:
=ALS(AW15="";"";INDEX(A16:AU16;AW15))
=ALS(AW15="";"";INDEX(A$2:AU$2;AW15))

In de bovenste twee cellen van dit blokje heb ik de kleur van het lettertype gelijk gemaakt aan de achtergrond zodat de tussenresultaten niet te zien zijn, en daarna heb ik het blokje geselecteerd, met doorvoeren naar de andere relevante cellen in het werkblad overgenomen, en toen nog de kolommen gekopieerd naar het andere werkblad.
Eindresultaat: Eindelijk werkt het zoals ik wil! 't Is een lang gevecht geweest, maar het resultaat is de moeite waard!

Nogmaals hartelijk dank voor het meedenken!
--
Hugo Kornelis, SQL Server MVP