verschilberekening maken

[Plotinus]

L.S.

Ik heb zo'n 36 kolommen en 3.000 regels met getallen. Per rij wil ik nu de cel zoeken (en apart zetten achter de kolom, met kolomnaam en absolute waarde) die het grootste positieve verschil met zijn twee directe buurcellen (links en recht dus) heeft, waarbij van die twee het kleinste verschil telt:

A B C D E verschil kolomtitel absolute waarde
10 14 6 11 1 --> 5 D 11

Het gaat dus om de relatieve grootste waarde ten opzichte van de directe buurcellen.

Dit is heel omslachtig op te lossen door een verschiltabel te maken, waarbij elke cel per rij geëvalueerd wordt en dan op het eind van de tabel de grootste waarde wordt opgezocht; kolomtitel en absolute waarde van de cel zijn vervolgens makkelijk gevonden.

Vraag is dus: is er geen makkelijkere en snellere manier, waarbij de data toename beperkt kan worden? Bijvoorbeeld een functie als: 'maximaal verschil in rij(x)'

Iemand een idee, of aanzet tot...?

gegroet,
mrt

 

[Thoralf]

Heb je ook even een voorbeeld bestandje?
Dan hoeven helpers niet zelf een bestandje in elkaar te zetten wat mogelijk net weer niet aansluit bij wat jij wilt.

 

[Plotinus]

Sorry Thoralf; hierbij een deel (anders te groot) van het bestand. Het gaat om de kolommen D t/m AM

gegroet,
mrt

 

[Thoralf]

Hallo Plotinus,

Bijgaand een oplossing.

In kolom AO de maximale afwijking, in kolom AP, met daarin kolom AO verwerkt, het kolomnummer van de kolom met de maximale afwijking en in kolom AQ het kleinste verschil met de naastgelegen kolommen.

 

[Plotinus]

Dank Thoralf voor alle moeite; in zo'n korte tijd zoiets voor elkaar boxen, daar neem ik mijn petje voor af.
Maar toch zit er een foutje in; misschien ben ik niet duidelijk genoeg geweest. Ik heb een stukje van mijn uitgebreide analyse toegevoegd; laatste kolom in rij 2 geeft de gezochte waarde.

Verder nog een vraagje, in lijn met mijn oorspronkelijke vraag:
Zou je de kolomtitel (rij 1) kunnen plaatsen naast de maximale afwijking, in plaats van kolomnummer? Ook snap ik de 'gezochte waarde' niet. Bedoeld is de celinhoud te geven van de cel die de maximale afwijking (uitschieter ten opzichte van de buren) heeft. In rij twee wordt het dus:
1,48, LZeq 31,5Hz, 56,04

Voor het goede begrip:
Het gaat hier om geluidspectra. Als in een bepaalde tertsband (kolom) het geluidniveau (fors) uitsteekt boven de buren, dan kan er sprake zijn van tonaal geluid (horen van scherpe toon wat erg hinderlijk is). Het gaat dus om de maximale uitschieter op de hele rij die bepaald wordt door de kleinste afstand met de buren. Het gaat dus ook altijd om positieve uitschieters: een toon die er ten opzichte van de buren uitspringt.

gegroet,
mrt

 

[Thoralf]

Hallo Plotinus,

Als ik jouw vraag lees en die vergelijk met jouw voorbeeld dan mis ik de overeenkomst.
In jouw vraag, zie #1, stel je:

Per rij wil ik nu de cel zoeken (en apart zetten achter de kolom, met kolomnaam en absolute waarde) die het grootste positieve verschil met zijn twee directe buurcellen (links en recht dus) heeft, waarbij van die twee het kleinste verschil telt

in jouw voorbeeld:
bepaal je per cel eerst het minimale verschil met zijn buurcellen, vervolgens kies je de cel die het maximum heeft van de minimale verschillen. tot hiertoe lijkt alles goed te gaan, echter je doet dit niet met de absolute waardes van de verschillen.

Als ik dan jouw voorbeeld aanpas naar de absolute waardes (zie aanpassing voor AU2:CB2) dan wordt het resultaat net even anders.
Daarbij heb ik dan de formules voor de gezochte ontbrekende waardes in CF2 en CG2 gezet.

Ik heb op dit moment nog geen oplossing weten te realiseren zonder de hulptabel die jij ook reeds gemaakt had in je voorbeeld.

 

[Plotinus]

Dag Thoralf,

Ik denk dat ik het goed heb geformuleerd, maar dat je het nog niet helemaal goed op het netvlies hebt (geen verwijd!); het is ook heel lastig met maximaal en minimaal door elkaar.

Ingaand op jouw rekenvoorbeeld zie ik in één keer dat het niet goed kan zijn, omdat de waarde van de gevonden cel onder die van zijn linker buurman ligt (22,26). Bij de tertsband van LZeq 2kHz kan dus nooit sprake zijn van een zuivere toon. Het gaat dus echt om tertsbanden (de kolomtitels) waarbij de uitschieter ten opzichte van beide buurcellen is. Bepalend is dan de kleinste afstand tot de buren. Absolute waarden spelen dus m.i. geen rol - de uitschieter moet altijd positief zijn.

Een voorbeeld: celwaarde is bijvoorbeeld 20; linkerbuur is 15, rechterbuur is 10. Dan is de 'uitschieter' 5; dit kleinste verschil bepaalt de ervaring toon/geen toon.

Denk aan een equalizer; die golvende kolommen op je stereo/computer; een zuivere sinus geeft dan één kolom te zien. Wordt er veel - en luider - ander geluid bijgemengd, dan verdringt die zuivere toon en is niet meer afzonderlijk waarneembaar.


Kolomtitel en celwaarde is wel perfect opgehaald!

gegroet,
mrt

 

[Thoralf]

O ja,

Je schrijft:

Het gaat dus om de maximale uitschieter op de hele rij die bepaald wordt door de kleinste afstand met de buren.

In jouw voorbeelduitwerking en in het eerste verhaal vertaal je de kleinste afstand met de buren door het minimum van de gemeten waardes te bepalen.
Dit komt mij wat vreemd over: ik zou hier eerder het verschil in Hz verwachten.
Echter dan blijft de vraag hoe vertaal je de meetverschillen tussen de verschillende tertsbanden naar uniforme waardes, waardoor ze vergelijkbaar worden?

 

[Thoralf]

Zo'n vraag als deze blijft gewoon uitdagen.
Hierbij dan een volgende poging. Heb wel even je bestandje iets moeten inkorten, maar dat doet niet af aan het resultaat: een oplossing met slechts 1 hulpkolom.

 

[Plotinus]

Prachtig Thoralf; zeer elegant, kort en krachtig. Heel hartelijk dank

En toch heb je het nog niet helemaal begrepen... Het gaat hier om tertsband middenfrequenties (titels kolom). De waarden in de cellen zijn de dB's. Hoe hoger het getal, hoe meer dB's; hoe sterker ten opzichte van de buren, hoe meer het opvalt; tonaal.

Om het overzichtelijk te houden heb ik het probleem zo vertaald dat het 'voor een leek' te begrijpen valt. Wat ik nu echter moet gaan doen is het rij voor rij toetsen aan bepaalde definities: Ik citeer even uit eigen werk:

Volgens DIN 45 645-2 is er sprake van tonaal geluid indien een niveau in een bepaalde tertsband tenminste 5 dB boven de niveaus van de naburige tertsbanden ligt. Volgens het ISO 1996-2 bijlage D (simpele methode) is er sprake van tonaal geluid als de piek ten opzichte van de naastliggende tertsbanden tenminste de volgende waarden heeft:
• +15 dB van 25 Hz tot 125 Hz
• +8 dB van 160 Hz tot 400 Hz
• +5 dB van 500 Hz tot 10 kHz

Ik moet jouw aangedragen oplossingen dus zodanig gaan omwerken dat ik bovenstaande toetsen (de ISO-methode) kan gaan uitvoeren. Het gaan dus drie keer drie kolommen worden. Vervolgens ga ik de grootste (vijf tot tien) uitschieters per dag mooi in een tabel (drie tabellen onder elkaar; 15,8,5) presenteren met tijdstip, tertsband, geluidniveau van cel en dan bovenstaande toets uitvoeren. Meestal zal aan bovenstaande voorwaarden niet voldaan worden en gelukkig maar.Maar via voorwaardelijke opmaak heb ik er al wel opgespoord. Alleen kon ik ze vervolgens niet 'te pakken' krijgen.
De vervolgstappen kan ik nu in principe zelf, al zal het nog wat tijd kosten en gaan we 'helaas' zeer binnenkort op vakantie.

Toch nog één vraag: ik wil liever niet zien 'LZeq 31,5 HZ', maar 31,5; stuk voor en erachter dus af. Ik weet dat het kan, heb het al gedaan, maar lang geleden. Weet je misschien toevallig welke commando/formule ik daarvoor moet hebben?

hartelijke groet,
mrt

 

[Thoralf]

Je kunt gebruikmaken van de tekst-functie DEEL, in combinatie met de functie VIND.SPEC.
Daarbij zoeken op spatie.
Of, als het begin van het getal op een vaste plek staat, kun je wellicht ook zonder de VIND.SPEC

 

[Thoralf]

En om je een stukje op weg te helpen.

 

[Plotinus]

Dat is het helemaal; prachtig.

Omdat ik de formules nog niet helemaal kan doorgronden en me de tijd op het werk ontbreekt om daar uitgebreid in te verdiepen (gaat vanavond gebeuren), zou ik graag van je willen weten wat je als grenzen hebt gehanteerd. Voor alle gedefinieerde frequentiebereiken dient namelijk ook over de grenzen gekeken te worden (vergelijken met de buitenste buren). Voor bijvoorbeeld het eerste criterium van 25 t/m 125 Hz moet voor de 25 Hz tertsband met 20 Hz links en rechts 31,5 tertsband vergeleken worden; voor de 125 Hz met de links 100 Hz en rechts 250 Hz. Kun je me laten weten of dat is gedaan?

Nogmaals hartelijk dank zover.

gegroet,
mrt

 

[Thoralf]

Dat had ik al ingeschat dat dat moest gebeuren.
Verder heb ik de Hz en kHz aanduiding in kolomtitels wel gehandhaafd, de voorlooptekst niet.
Mocht je verder nog vragen hebben, je kunt mij ook rechtstreeks mailen op thoralf en dan hete post punt com.

 

[Plotinus]

Dag Thoralf,

Ik heb je privé gepost, maar of het is aangekomen???

gegroet,
mrt

 

[Thoralf]

Nee; adres is Thoralf @ hotmail.com

verwijder de spaties.