Voorwaardelijke opmaak en maximale waarde indien....in Excel

[Stitchie]

Ik ben op zoek naar het volgende maar krijg het niet voor elkaar.

Ik heb een printscreen toegevoegd bij deze vraag (gen)

Vraag 1:
De cel in A die voldoet aan de volgende 2 criteria mag een kleur krijgen:
de hoogste waarde van kolom A waarbij kolom C leeg is.
Dus in dit voorbeeld zou cel A4 rood moet worden.

Vraag 2:
ik zou het resultaat elders in mijn sheet opgesomd willen zien worden.
dus bv in cel E1. de hoogste waarde van kolom A waarbij kolom C leeg is.
en in cel F1 dan de bijbehorende datum die hoort bij cel E1
Dus in dit voorbeeld in cel E1 komt dan 16 en in cel F1 komt dan 1-4-2018

Voor de kleuring van de cel krijg ik het wel voor elkaar zonder de combinatie met kolom C
en ik krijg elders in het sheet de hoogste waarde icm kolom C ook voor elkaar.
maar de rest niet.

Kan iemand mij helpen?


...

[ Voor 1% gewijzigd door Hero of Time op 07-02-2019 18:41 . Reden: Volledig plaatje embed ]

[Hero of Time]

Ik ben zo vrij geweest om de link naar de thumbnail te vervangen voor de volledige grootte en gelijk zichtbaar in je post te zetten.

Vraag 1 kan je oplossen met 'conditional formatting'. Dit is iets wat al vele malen vaker is gevraagd hier en met Google vind je heel veel tutorials en andere uitleg hoe dit werkt. Ik raad je aan hier naar te kijken en dan om hulp vragen als het niet helemaal wil lukken.

Vraag 2 klinkt als iets waarbij je een 'if' combineert met max. Of als je een Nederlandse Office hebt is het 'als'. Ik ben zelf geen expert in spreadsheets, maar als je eerst zoekt hoe je de hoogste waarde kan vinden in een kolom, kan je dit combineren om het resultaat te tonen als kolom C die erbij hoort leeg is. Maar ook hier, laat even zien wat je hebt en waar je tegenaan loopt.

[F_J_K]

Het kan ook heel simpel met een hulpkolom: D1 is 0 als C1 leeg, anders A1. En dan daar de voorwaardelijke opmaak aan relateren.

[dix-neuf]

Het kan eenvoudig zonder hulpkolom.
Beantwoord eerst vraag2 in E1, dat kan met =Max(als(....
Als je dat getal hebt, kun je in F1 de datum die bij E1 hoort, berekenen met (bv.) vert.zoeken.
Dan wordt de voorw. opmaak voor kolom A erg eenvoudig: je moet alleen maar kijken of de celwaarden in die kolom gelijk zijn aan E1 (voor A1 dus: =A1=E$1).

[Stitchie]

Nav bovenste reacties ben ik aan de gang gegaan.

Vraag 2a e1 de hoogste waarde lukt me dmv =max. Als. Voorwaarden(a1:a7;c1:c7;"";a1:a7;">0").
Vraag 2b f1 doet het voor de helft hij pakt niet altijd de juiste datum dmv =vert.zoeken(e1;a1:b7;2).
Nu is het resultaat 16 en 01-04-18 als ik vervolgens A2 aanaps naar 18 waardoor dat de hoogste waarde is wordt de datum 09-07-18 ipv de goede.

Vraag 1 kleuring hoogste waarde.
Begrijp ik het goed dat ik dat moet instellen per cel en kan het niet in reeks? Dus de hoogste waarde van a1tot a7 is cel A4 en maak deze rood. Of kleur de cellen die gelijk zijn aan E1.

Kan iemand me verder helpen?

[dix-neuf]

Vraag 2a e1 de hoogste waarde lukt me dmv =max. Als. Voorwaarden(a1:a7;c1:c7;"";a1:a7;">0").

Laat eens zien hoe je functie er precies uitziet !

Vraag 2b f1 doet het voor de helft hij pakt niet altijd de juiste datum dmv =vert.zoeken(e1;a1:b7;2)

Je gebruikt de functie vert.zoeken niet goed, er hoort nog een 0 achter.
Kijk in de helpfile van Excel, het gebruik van alle functies wordt daar zeer goed uitgelegd.

Begrijp ik het goed dat ik dat moet instellen per cel en kan het niet in reeks?

Dat kan per reeks, maar als je alle cellen selecteert en je stelt de vw. opmaak in voor 1 cel, dan past Excel voor de andere cellen de vw. opmaak automatisch aan.

[ Voor 7% gewijzigd door dix-neuf op 08-02-2019 11:47 ]

[Lustucru]

dix-neuf schreef op vrijdag 8 februari 2019 @ 11:46:
[...]
Laat eens zien hoe je functie er precies uitziet !

Dat doet hij al -als je die spaties wegdenkt; lamme autocorrectie tikkert vermoed ik-.

Vanaf O2016/365:

code:

=Max.Als.Voorwaarden([bereik];[criteriumbereik1];[criterium1];[criteriumbereik2];[criterium2])

[Stitchie]

Vraag 2a is zoals het er staat zonder hier en daar een spatie maar mijn formule werkt.

Vraag 2b heb ik de eerder genoemde formule uitgebreid met ;0 tussen de 2 en). Lijkt te werken.

Vraag 1 daar moet ik nog ff mee knutselen want dat doet het nog niet zoals ik het wil.

Het voorbeeld bestaat nl uit 7 regels het werkelijke bestand telt 300 regels.

[Lustucru]

Stitchie schreef op vrijdag 8 februari 2019 @ 12:43:
Vraag 1 daar moet ik nog ff mee knutselen want dat doet het nog niet zoals ik het wil.

Selecteer het hele bereik, klik op voorwaardelijke opmaak, formule gebruiken.

De formule wordt =(A1=$E$1). Omdat A1 een relatieve verwijzing en $E$1 een absolute zal Excel dit voor elke cel correct vertalen in An=$E$1.

[Stitchie]

@Lustucru bedankt voor je toevoeging gaat ik proberen.

Kan verticaal zoeken ook over meerdere tabbladen... Ik krijg nu # n/b.

[Stitchie]

Het begint ergens op te lijken... Ik heb het bijna voor elkaar maar kan dit ook?
In kolom a de cel met hoogste waarde rood kleuren en de cel met de laagste waarde groen.
Dit in 1 formule via de voorwaardelijke opmaak

[dix-neuf]

Stitchie schreef op vrijdag 8 februari 2019 @ 21:22:
In kolom a de cel met hoogste waarde rood kleuren en de cel met de laagste waarde groen.
Dit in 1 formule via de voorwaardelijke opmaak

Dat minimum kan (uiteraard) op dezelfde manier als het maximum, maar in vw. opmaak wel met 2 formules, niet met 1. Maar dat maakt toch niet uit ? Het eenvoudigst is om ook het minumum in een cel te berekenen, bv. in E2. De bijbehorende datum in F2 is weer teberekenen met. vert.zoeken. De vw. opmaak voor zowel min als max ziet er dan uit zoals je ziet als je op deze link klikt: https://imgur.com/a/G65RU6b

[Stitchie]

Vraag 2b heb ik de eerder genoemde formule uitgebreid met ;0 tussen de 2 en). Lijkt te werken.

Dit lukt me tot dusverre alleen wanneer ik niet naar een andere tabblad ga... Dus als cel E1 niet vergeleken moet worden met kolom a op tabblad 1 maar met kolom a van tabblad 2. Ik krijg nu als resultaat #n/b.

De rest is inmiddels gelukt. Dank daar voor

[dix-neuf]

Als je in een blad met formules naar een ander blad verwijst, dan moet je (uiteraard) de naam van het blad vermelden, anders kan Excel onmogelijk 'weten' op welk blad de formules van toepassing zijn.
Voor berekening van maximum en minimum kun je ook een kortere functie gebruiken dan hierboven vermeld (zie hieronder voor blad2). De invoer daarvan moet je afsluiten met Ctrl-Shift-Enter (dwz: de Ctrl- en Shifttoets ingedrukt houden terwijl je op Enter drukt).

code:

=MAX(ALS(Blad2!C$1:C$7="";Blad2!A$1:A$7))
en
=MIN(ALS(Blad2!C$1:C$7="";Blad2!A$1:A$7))

[Stitchie]

De formule voor min en maximale waarde werkt prima. Wat nog niet lukt is dat de datum van de min of max waarde automatisch in de cel er achter komt te staan.... In mijn test excel wel maar het originele nog niet. Hij laat hier zien N/B.

Ook staan er een aantal cellen die op basis van de formule nog geen waarde kunnen geven en hierbij geeft excel ###### weer. Is het met een voorwaardelijke opmaak voor elkaar te krijgen dat zolang de uitkomst vd formule ####### is de letterkleur wit wordt en dat wanneer de formule en werkelijke waarde geeft weer zwart kleurt?

[dix-neuf]

Stitchie schreef op dinsdag 19 februari 2019 @ 14:16:
Wat nog niet lukt is dat de datum van de min of max waarde automatisch in de cel er achter komt te staan.... In mijn test excel wel maar het originele nog niet. Hij laat hier zien N/B.

Een reden zou KUNNEN zijn dat je in de vert.zoekenformule geen dollartekens hebt gebruikt voor het bereik (in het voorbeeld hierboven: $A$1:$A$7). Bij het kopiëren naar beneden van de functie loopt het dan fout.

Ook staan er een aantal cellen die op basis van de formule nog geen waarde kunnen geven en hierbij geeft excel ###### weer. Is het met een voorwaardelijke opmaak voor elkaar te krijgen dat zolang de uitkomst vd formule ####### is de letterkleur wit wordt en dat wanneer de formule en werkelijke waarde geeft weer zwart kleurt?

Als in cellen ##### verschijnt, dan kan dat verschillende redenen hebben. Eerst moet je weten wat er achter die hekjes staat. Daarvoor moet je de kolom waarin die hekjes staan verbreden. Als de tekst dan verschijnt en je ziet een correct resultaat, dan is je probleem daarmee opgelost.
Staat er een foutmelding, bv. (#WAARDE), dan moet je nagaan waaraan die foutmelding ligt: een fout in de gebruikte formule, of een foutmelding omdat bepaalde cellen (nog) geen gegevens bevatten. Is dat laatste het geval (dat is bv. zo bij het door jou vermelde #NB) , dan kun je dat wegwerken door de formule ALS.FOUT aan je formule(s) toe te voegen. Kijk in de helpfile van Excel hoe die werkt. Fouten maskeren door de tekstkleur wit te maken, dat moet je niet willen (vind ik), dat is geen echte oplossing.

[Stitchie]

De ##### zijn het gevolg van cellen die in de toekomst gevuld worden en dus de formule nu nog geen resultaat geeft. Deze cellen worden in de loop vh jaar gevuld en worden de #### automatisch werkelijke waardes.
Ik vind de ##### niet zo mooi in het excel sheet.
Daarom dacht ik aan het wit maken.

De oossibg met isfout formule vind ik een goede krijg ik alleen niet voor elkaar.
=som. Als($m$24:$m$284;"=9";$j$24:$j$284) is de huidige formule.... Deze wil ik graag ombouwen naar een isfout variant.

Wat de #nb betreft ik gebruik $ en verwijs naar bladen maar blijft het weergeven. Zou het kunnen omdat het geen getallen zijn maar tijdsaanduiding betreft.

[Reptile209]

Stitchie schreef op woensdag 20 februari 2019 @ 08:52:
[..]
De oossibg met isfout formule vind ik een goede krijg ik alleen niet voor elkaar.
=som. Als($m$24:$m$284;"=9";$j$24:$j$284) is de huidige formule.... Deze wil ik graag ombouwen naar een isfout variant.

Heb je al eens in de help naar ISFOUT() gekeken? De help voor functies is uitstekend, met duidelijke voorbeelden.

Wat de #nb betreft ik gebruik $ en verwijs naar bladen maar blijft het weergeven. Zou het kunnen omdat het geen getallen zijn maar tijdsaanduiding betreft.

Maak eens in een aparte cel een verwijzing naar je andere blad (= typen en dan met je muis op de doelcel); vergelijk die verwijzing dan eens met degene die je nu in je formule gebruikt.
Herstel: je krijgt #n/b als je bijvoorbeeld een VERT.ZOEKEN gebruikt, maar de waarde waar je naar zoekt niet in het opgegeven bereik voorkomt. Dat kan komen omdat hij er echt niet in staat, of omdat je bijvoorbeeld de 3e parameter (true/false) verkeerd hebt gekozen of hebt weggelaten terwijl je hem wel nodig had.

[ Voor 16% gewijzigd door Reptile209 op 20-02-2019 09:15 ]

[Lustucru]

Stitchie schreef op woensdag 20 februari 2019 @ 08:52:
Wat de #nb betreft ik gebruik $ en verwijs naar bladen maar blijft het weergeven. Zou het kunnen omdat het geen getallen zijn maar tijdsaanduiding betreft.

Zoiets dergelijks... dikke kans dat die tijdsaanduiding in bv de zoekcel wel een tijdsaanduiding is en in de opzoektabel gewoon tekst. Let eens op de uitlijning: staat het soms rechts, soms links in de cel?

Simpele test is om in de opmaak van de cel numerieke weergave af te dwingen. Bij een echte datum-tijd lukt dat, bij een tekst die een tijdsnotatie heeft lukt dat niet.

[dix-neuf]

Stitchie schreef op woensdag 20 februari 2019 @ 08:52:De ##### zijn het gevolg van cellen die in de toekomst gevuld worden en dus de formule nu nog geen resultaat geeft. Deze cellen worden in de loop vh jaar gevuld en worden de #### automatisch werkelijke waardes. Ik vind de ##### niet zo mooi in het excel sheet.

Zoals ik al schreef kun je in plaats van wit maken beter ALS.FOUT (mét punt tussen ALS en FOUT) gebruiken (dat is NIET hetzelfde als ISFOUT (zonder punt), dat werkt op een andere manier, maar is ook mogelijk). Uitgaande van de gegevens in A1:B7 in je eerste bericht en met het maximum (16) in E1, wordt de datum die bij E1 hoort dan in F1, inclusief als.fout):

code:

=ALS.FOUT(VERT.ZOEKEN(E1;$A$1:$B$7;2;0);"")

Aan E1 moet je mogelijk 1 of 2 dollartekens toevoegen, dat hangt af van het feit of je bv. met je formule onder F1 naar E1 wil blijven verwijzen of naar E2, E3, enz. (is het eerste het geval (naar E1 blijven verwijzen), dan moet je er E$1 van maken). Als bovenstaande formule in een foutmelding resulteert, blijft de cel blanco (wit maken van tekst is dan niet nodig).

De oossibg met isfout formule vind ik een goede krijg ik alleen niet voor elkaar.
=som. Als($m$24:$m$284;"=9";$j$24:$j$284) is de huidige formule.... Deze wil ik graag ombouwen naar een isfout variant.

Als je met die formule tijden optelt en de som daarvan wordt meer dan 24 uur, dan kun je best de celeigenschappen instellen als: "Aangepast" -> en onder "Type" invullen: [u]:mm .
Verder moet je geen spaties in je formules gebruiken (dus niet som. Als maar SOM.ALS
Met de juiste celopmaak en juiste invoer kan de formule SOM.ALS normaliter geen foutmeldingen geven.
Wat je met 'oossibg' bedoelt, begrijp ik niet.

Wat de #nb betreft ik gebruik $ en verwijs naar bladen maar blijft het weergeven. Zou het kunnen omdat het geen getallen zijn maar tijdsaanduiding betreft.

Dat zou kunnen, zie mijn tekst hierboven voor de juiste celopmaak bij optelling van tijden.

[Stitchie]

oossibg is typ foutje had oplossing moeten zijn....