voorwaardelijke opmaak

[Plotinus]

L.S.

Ik heb de bedoeling om uit een berg data te onderzoeken van welke cellen de inhoud tenminste 5,00 hoger is dan de twee naast gelegen cellen (links en rechts dus) en die cel dan een kleurtje te geven. Ik dacht dit te doen met voorwaardelijke opmaak, maar met formules in de opmaak heb ik helaas geen enkele ervaring. Weet iemand een oplossing? Of moet het misschien via VBA opgelost worden? In het voorbeeld bestandje gaat het om de kolommen R t/m BA.

De volgende stap is om in aflopende grootte de eerste 5 uitschieters in een tabel op een andere sheet op te halen met (eronder) vermelding van de datum/tijd (kolom P) en daar weer onder de kolomtitel van de betreffende cel.

Ik ga er daarbij vanuit dat de tweede stap uitgevoerd kan worden op basis van een kleuring van de betrokken cellen, maar zeker ben ik daar niet van.

Ik werk o.a. met Excel 2007.

gegroet,
mrt

 

[RaymondC]

Welke kolom moet er met welke kolom vergeleken worden?

Kolom S met R en T?
Kolom T met S en U
Kolom U met T en V
enz enz

En moet aan beide kanten een verschil van 5 zijn of maar aan 1 kant?

bv: R2= 50 S2= 69.75 T2=67.52

 

[Plotinus]

klopt helemaal:
voor de hele tabel voor iedere cel de twee naastliggende beoordelen, dus voor S2 kijken naar R2 en T2; en zo schuift het venster steeds op.
verder aan beide zijden dezelfde voorwaarde van >= 5

Korte inhoudelijke toelichting:
Het gaat om geluidspectra gemeten in terstbanden (middenfrequenties zijn de kolomtitels. Er kan sprake zijn van 'tonaal geluid' (een hoorbare toon) in het gemeten niveau als in één tertsband (zijn dus de kolommen) het signaal met tenminste 5 dB boven de direct naastliggende tertsbanden uit steekt.

gegroet,
mrt

 

[RaymondC]

en moeten ze ook aan beide voorwaarden voldoen?

als R2+5<S2;"NIKS"

als T2+5<S2;"NIKS"

 

[Plotinus]

klopt, beide voorwaarden.

Ik kan de formule in een cel wel maken, maar het gaat mij om de notatie in de voorwaardelijke opmaak; dat lijkt iets anders te werken dan voor een cel

 

[zapatr]

De volgende stap is om in aflopende grootte de eerste 5 uitschieters in een tabel op een andere sheet op te halen met (eronder) vermelding van de datum/tijd (kolom P) en daar weer onder de kolomtitel van de betreffende cel.

1.
Wat bedoel je precies met de eerste 5 uitschieters?
Uitschieters per kolom of per rij?
2.
De voorw. opmaak is erg simpel.

 

[Plotinus]

Dag Zapatr,

Bedoeling is om per dag van de cellen die aan de voorwaarde (zie hierboven) voldoen, de sterkste uitschieters (ten opzichte van de beide buurcellen, maar tenminste 5) op te halen en te presenteren in een tabel. Dat lukt mij allemaal ondertussen gezien oplossingen van aanverwante recente vragen die ik hier heb gepost (matrixformules gebruiken etc.). Wat ik echter niet weet of ik kan selecteren/evalueren op een cel die een kleur heeft gekregen. Met andere woorden: ik kan wel een formule maken waarbij twee cellen onderling worden vergeleken en de grootste wordt geselecteerd, maar of dat ook kan met een gemarkeerde cel???

Dus alle gemarkeerde cellen wil ik per dag kunnen evalueren en rangschikken op grootste uitschieter ten opzichte van zijn buren.

gegroet,
mrt

 

[zapatr]

Hier alvast een bestand met de voorw. opmaak die je wenste.
En vertel aan de hand daarvan eens hoe je die blauwe cellen gepresenteerd wil zien.

 

[Plotinus]

Bij deze het aangepaste bestand. Ik heb een onderscheid gemaakt in mate van uitschieter: >8,5, >8 ...,>5. Het is nu de bedoeling om een tabel te vullen zoals aangegeven op blad 2 waarbij wordt afgepeld van alle 8,5 dB uitschieters tot 5 dB waarbij de interesse uitgaat naar tenminste de eerste 10 grootste uitschieters. Dat kunnen dus 10 cellen zijn met >8,5 dB uitschieters. Maar de kans is groter (afgaande op andere datareeksen) dat er geen enkele cel op een bepaalde dag boven de 5 dB uischieter (minimumvoorwaarde) uitkomt.
Het begint dus met 'afpellen vanaf 8,5 dB naar beneden toe tot er tenminste 10 zijn 'getabelleerd', met tertsband (kolomtitel), datum en waarde in de cel.

Bedankt overigens met de aangereikte methode van voorwaardelijke opmaak!

gegroet,
mrt

 

[Plotinus]

en nu met bijlage

 

[zapatr]

Plotinus,
Als je een voorbeeldbestand toevoegt, dan moet je dat vanzelfsprekend zo realistisch mogelijk maken en er niet een plaatsen (zoals je gedaan hebt) waarbij geen enkele cel aan de voorwaarden voldoet, waarbij alle rijen dezelfde datum hebben, en alle projectnamen hetzelfde zijn, anders is een oplossing niet op zijn juistheid te controleren. Je verwacht kennelijk dat degenen die naar je vraag kijken dat allemaal zelf wel zullen aanpassen.
Ik vind dat je je er wel heel erg gemakkelijk vanaf maakt en zelf erg weinig inspanning hebt gedaan om tot een (begin van) een oplossing te komen. En je omschrijving vind ik ook erg gebrekkig met dat 'afpellen'.
Plaats eerst maar eens een bestand waarin aan bovenstaande wel voldaan is, dan wil ik er misschien nog eens naar kijken. Maar ik kan je nu wel al zeggen dat als je elke cel in je bereik op 6 voorwaarden wil controleren (wat het geval is), dat een macro daar lang over kan doen.

 

[Plotinus]

Zapatr, het lijkt erop dat je gepikeerd bent en dat is jammer, zeker nu je mij al zo goed geholpen hebt. Mijn probleem is dat het hier om heel veel meetdata gaat, samen goed voor een werkmap van bijna 4 MB (in compatibiliteitsmodus bijna 9 MB). En zoals je weet is het uploadvolume beperkt. Ik moet dus iedere keer weer veel 'sloopwerk' doen.
Jouw oplossing van voorwaardelijke opmaak heb ik direct uitgebreid, waardoor ik al een visuele rangorde krijg: er zijn uitschieters van meer dan 8 dB, van 7,5 etc. Dat is voor mij vitale informatie, omdat hoe hoe hoger hoe belangrijker (inclusief dag en tijdstip en tertsband dat het zich voordoet) dat is. Volgens mij is dat goed zichtbaar in het bestandje wat ik je toestuurde: verschillende cellen zijn verschillend gekleurd en voldoen aan de basisvoorwaarde van >5 (zie kolom AA, rij 44 en volgende).

Centrale vraag is daarom nog steeds of eerst op bijvoorbeeld kleurtje rood gezocht kan worden, dan op kleurtje oranje etc. dus beginnend met de kleur die voor de grootste uitschieter staat. Ik vrees dat ik het niet duidelijker uit kan leggen.

Voor de duidelijkheid heb ik er nog een stukje van een andere dag bijgedaan; in werkelijkheid gaat het om ca. 2.700 rijen, verdeelt over 10 dagen.

 

[zapatr]

Als je een Excelbestand op het forum plaatst, kun je dat het best doen in de vorm van een Excel 1997-2003-bestand, anders kunnen velen (waaronder ik) het niet openen.

 

[Plotinus]

ah, sorry; recent ben ik overgegaan naar 2007 wegens compatibiliteitswaarschuwingen bij andere acties in het werkmap.

gegroet,
mrt

 

[zapatr]

Plotinus:

1.
In het ene voorbeeldbestand staan de datums in kolom P en beginnen de meetgegevens in kolom R, terwijl in het andere voorbeeldbestand dat resp. de kolommen B en D zijn. Hoe is dat in het werkelijke bestand waarmee je werkt? Je moet begrijpen: als er een macro wordt geschreven om de gegevens te analyseren en die kolommen zouden per bestand verschillend zijn, dan zou de macro steeds moeten worden aangepast; dat is onwerkbaar. Dus je moet ervoor zorgen dat je bestanden er steeds uniform uitzien. Als dat niet zo is of niet mogelijk is, dan heeft het schrijven van een macro niet zoveel zin, lijkt mij.

2.
In het ene bestand hanteer je als grootste uitschieter >9, in het andere >8,5 ?
Is die uiterste waarde waarnaar je wil kijken niet steeds hetzelfde ?

Is het altijd tot uitschieter >= 5 dat moet worden teruggeteld of kan dat ook een andere waarde zijn ?
En is dat altijd met tussenstappen van -0,5 ?

3.
Bestaan de meetgegevens altijd uit 36 kolommen ?

 

[Plotinus]

even kort en duidelijk:
1: om het bestand te verkleinen heb ik de tweede keer inderdaad wat kolommen verwijderd. In werkelijkheid staat alles exact in dezelfde kolom: de datum in kolom P

2: Ik heb slechts snel geprobeerd een eerste sortering in grote te vinden in stapjes van 0,5. Blijkt een uitschieter 15 dB boven de buren te liggen dan wil ik dat ook graag weten. 15 (dB) is echter wel een uiterste van wat verwacht mag worden. Dus ik zou beginnen met zoeken vanaf 15 dB en dan met stapjes van 0,5 omlaag tot 5 dB (dus 20 zoekcycli).

3: Yes, zoals hierboven gesteld is de input altijd gelijk van vorm. Het aantal regels varieert wel aan de hand van de lengte van de metingen van enkele dagen tot weken. Maar dat is mogelijk eenvoudig in te stelen in VBA (ik ben er wel wat vertrouwd met VBA al is het al jaren geleden).


Ik zie mijn huidige project als een soort sjabloon die ik helemaal perfect wil hebben. Daarna kunnen andere metingen erin worden gegoten waarna vrijwel alles (tabellen en grafieken) er automatisch 'uitrollen'.

Tof dat je er nog mee bezig bent!

gegroet,
mrt

p.s. van het weekend ben ik afwezig en kan dus niet meer reageren

 

[zapatr]

Plotinus,
ik hoop dat je dit nog leest voordat je op weekend vertrekt.

Blijkt een uitschieter 15 dB boven de buren te liggen dan wil ik dat ook graag weten. 15 (dB) is echter wel een uiterste van wat verwacht mag worden. Dus ik zou beginnen met zoeken vanaf 15 dB en dan met stapjes van 0,5 omlaag tot 5 dB (dus 20 zoekcycli).

Je moet elke cel met 2 omliggende cellen vergelijken, dus dat betekent niet 20, maar 40 testen per cel !!
Dat is in totaal: 2700 x 36 x 40 = 3.888.000 (een kleine 4 miljoen) testen per keer dat je de meetgegevens doorloopt (dat wordt dan voor jou wel een heel erg lange koffiepauze). Je moet nog wel aan de praktische bruikbaarheid denken. Als een uitschieter van 15 slechts zeer zelden voorkomt, dan kun je de uiterste grens beter wat lager leggen, want ontdekt wordt die 15 toch wel (ligt de uiterste waarde bv. op 9, dan zou die 15 in de categorie >9 komen te liggen en wordt dus altijd getoond).

...ik ben wel wat vertrouwd met VBA...

Als dat zo is, dan is mijn vraag toch: waarom schrijf je die macro dan niet zelf ? ('geen tijd' is uiteraard geen excuus). Ik ben nl. geen beroepsprogrammeur maar slechts een amateur die met trial and error zijn weg doorheen vba zoekt. Kijk: de cellen op het eerste blad de gewenste kleur geven met vba is tamelijk eenvoudig, maar om op het tweede blad er alles precies uit te laten zien zoals je wenst (met zoals in je voorbeeld niet de uitschieters zonder meer, maar ook nog eens in aflopende volgorde per datum), dat is niet zozeer moeilijk, maar wel een heel gepuzzel hoor, zelfs voor professionelen. Wellicht zijn er lezers op dit forum die de klus in korte tijd kunnen klaren, maar mij kost dit minstens 20 uur. We zullen zien hoe ver ik kom (als anderen met een oplossing komen, ga ik er uiteraard niet verder mee).

 

[Plotinus]

Ja zapatr ik ben er nog (tot vanavond laat); ik werk hard aan de bijbehorende concept-rapportage.

Je eerste opmerking klopt uiteraard helemaal. Beginnen met 10 is een veilige keuze. Eventueel kan een verkenning met 'voorwaardelijke opmaak' worden gedaan voordat de makro aan wordt gezet.

Wat je tweede opmerking betreft wil ik je graag laten weten dat ik het heel tof vind dat je belangeloos helpt en je hebt me al een heel eind geholpen. Maar voel je a.u.b. tot niets verplicht.
Mijn VBA-ervaring dateert van zo'n 8 tot 10 jaar geleden en toen was ik er zeer bedreven in; met vallen en opstaan een geluidrekenprogramma gemaakt in Excel. Ik weet dus dat bijna alles mogelijk is, maar het kost me nu inderdaad te veel tijd om al die kennis weer op te halen en deze klus nog tijdig te klaren.
De klant wacht op de analyse van de geluidmetingen en rekent niet eens op de analyse naar tonaal geluid; ik ben gaande het proces op deze mogelijkheid gestuit. De analyseresultaten via voorwaardelijke opmaak zijn al een grote stap voorwaarts en een cadeautje voor de klant.
Na de vakantie ga ik uiteraard verder met zoeken om dit te automatiseren, omdat dit soort klussen vaker zullen voorkomen. Het kan en het is een uitdaging. En als je helpt vind ik dat alleen maar heel sympathiek.


Zapatr, ik bedenk me ineens dat als het jou lukt om de cellen die aan de voorwaarde voldoen in kolom BC te kopiëren met ernaast de overschrijding ten opzichte van de buurcellen, dan ben kan ik de vervolgstappen wel zetten. Ik iets dergelijk met andere dat al uitgevoerd voor dit project. Hiervoor is ook geen VBA nodig, maar slechts een grote matrixformule

=ALS(C$59>0;GROOTSTE(ALS((Datum_tijd_LF>=C$6)*(Datum_tijd_LF<C$7)*(LAeq_LF>ondergrens)*(LAeq_LF<bovengrens)*(Overload_LF<1)*(Elapsed_time_LF>=tijdsinterval_onder)*(Elapsed_time_LF>=tijdsinterval_LF)*(Elapsed_time_LF<=tijdsinterval_boven);LF_DIN;2);"-")

is ophalen tweede grootste.

=ALS(C$59>0;VERSCHUIVING(Laagfrequent!$P$2;VERGELIJKEN(GROOTSTE(ALS((Datum_tijd_LF>=C$6)*(Datum_tijd_LF<C$7)*(LAeq_LF>ondergrens)*(LAeq_LF<bovengrens)*(Overload_LF<1)*(Elapsed_time_LF>=tijdsinterval_onder)*(Elapsed_time_LF>=tijdsinterval_LF)*(Elapsed_time_LF<=tijdsinterval_boven);LF_DIN;2);LF_DIN;"")

is ophalen bijbehorende oktaafband

=ALS(C$59>0;VERSCHUIVING(Laagfrequent!$A$2;VERGELIJKEN(GROOTSTE(ALS((Datum_tijd_LF>=C$6)*(Datum_tijd_LF<C$7)*(LAeq_LF>ondergrens)*(LAeq_LF<bovengrens)*(Overload_LF<1)*(Elapsed_time_LF>=tijdsinterval_onder)*(Elapsed_time_LF>=tijdsinterval_LF)*(Elapsed_time_LF<=tijdsinterval_boven);LF_DIN;2);LF_DIN;;"-")

is ophalen van de het tijdstip.


(de smileis zijn 'punt komma's'; weet niet hoe dit op te lossen)

hartelijke groet,
mrt

 

[zapatr]

Ik ben met je vraag nog langdurig bezig geweest, maar zoals eerder gezegd: dat is iets van lange adem. Nu zie ik dat je over dit onderwerp op dit forum nieuwe vragen hebt gesteld, dus mijn eerste vraag is: heeft het nog zin om hiermee verder te gaan?
Als het antwoord ja is, dan heb ik nog een vraag:
Je wenst de resultaten in deze vorm te zien (voorbeeld):

Geluidsniveau: 61,20
Uitschieter: >= 5 db
Terstband: 50 Hz.
Tijdstip: 04:45

Bovenstaande geldt als de afwijking zowel t.o.v. de linker- als de rechterwaarde groter is dan 5 db, waarbij er vanaf 10 teruggeteld is. Maar het is heel goed mogelijk dat de afwijking t.o.v. de linkerwaarde bv. 5,2 is, terwijl die t.o.v. de rechterwaarde bv. 9,4 is. Volgens jouw uitleg zou de uitschieter dan toch in de categorie >= 5 db vallen en niet in die van >=9 ? Is dat juist?

 

[Plotinus]

Dag zapatr,

Dank voor alle moeite, maar ik ben er inmiddels grotendeels - met uitstekende hulp - uit. Het is nu merendeels een kwestie van netjes verwerken in tabellen voor de korte termijn. Voor de langere termijn zijn er nog wel wat uitdagingen voor dit onderwerp, dus wellicht tot horens.

Mocht je inhoudelijk geïnteresseerd zijn in het vervolg, zie mijn andere vraag en reacties. Het is daar wel iets gewijzigd ingestoken; er bestaan namelijk meerdere mogelijkheden om naar tonaal geluid te zoeken, vandaar.

gegroet,
mrt