Waarden uit zin als gemiddelde optellen en automatisch updaten

Instel · 25 jan 2021

Hoi allemaal,

Ik zit met een vraag. Ik probeer het gemiddelde van waarden die horen bij een specifiek woord uit een zin als gemiddelde te berekenen, het woord kan zowel zonder als geheel met hoofdletters voorkomen. De reeks waar ik uit moet zoeken wordt iedere week geüpdate waardoor de zoeklijst steeds langer wordt.

Wat ik probeer is de waarden horende bij het woord uit de zin als gemiddelde in een cel te laten verschijnen.

Tot slot wil ik een functie inbouwen waarmee ik eenmaal per dag opnieuw automatisch het gemiddelde van de zoekwoorden kan laten berekenen.

Om mijn voorbeeld te visualiseren heb ik een voorbeeld bestand bijgevoegd.

Iemand die mij hierbij kan helpen?

AlexCEL · 25 jan 2021

Bedoel je het gemiddelde of het gewogen gemiddelde? Gemiddelde van aantal bestellingen met spek erin? Of totaal van aantal bestelde maaltijden met spek erin?

Code:

=SOM(ALS.FOUT(INDEX(Zoekgebied!$B:$B;ALS(ISGETAL(VIND.SPEC("spek";Zoekgebied!$A$2:$A$1000));RIJ($2:$1000);EXP(99)));0))/SOM(Zoekgebied!$B$2:$B$1000)

Opmerkingen
- dit is een matrixfunctie, d.w.z. afsluiten met Control+Shift+Enter als je geen Excel 365 gebruikt.
- Uitgangspunt: 3 van de 10 bestelde maaltijden bevatten spek = gemiddelde van 0,3.
- Als dit niet is wat je zoekt kun je dan aangeven wat je als uitkomst verwacht in de gele vakken?
- De knop is overbodig. De functie zorgt ervoor dat er altijd het juiste gemiddelde staat, hoeft niet apart geüpdatet te worden (mits je automatisch berekenen niet uitgezet hebt).
- het is handiger om de "zoekterm" ook in aparte cel te zetten waarnaar je kan verwijzen. Voorkomt handmatig aanpassen.
- mocht je wel excel 365 gebruiken dan kan het wat simpeler:

Code:

=SOM(FILTER(Zoekgebied!$B$2:$B$1000;ISGETAL(VIND.SPEC("spek";Zoekgebied!$A$2:$A$1000));""))/SOM(Zoekgebied!$B$2:$B$1000)

Instel · 25 jan 2021

Hoi AlexCEL,
Bedankt voor je snelle reactie, dit is inderdaad wat ik bedoel. Ik probeer het te implementeren in mijn echte bestand maar ik krijg het niet voor elkaar. Ik heb dit nieuwe testbestand gemaakt, zou je het hier is in kunnen zetten?

Nu ik toch aan het vragen ben. Zoals je kunt zien zijn mijn prijzen onderverdeeld in prijzen per liter maar ook per 100 liter, dit komt door de queries die ik heb opgevraagd en niet allemaal hetzelfde zijn. Heb jij enig idee hoe ik de prijzen gelijk kan trekken en dit voor de komende updates makkelijk kan handhaven? Het mooiste zou zijn dat de prijzen in de kolom blijven waar ze nu staan. Ik heb geprobeerd een soortgelijk iets d.m.v. =*100 of =/100 via een macro in Query te vinden, maar dat is niet gelukt.

AlexCEL · 25 jan 2021

Opm:
- Je formule verwijst naar tabblad "zoekgebied", die bestaat niet. Daarom foutmelding in je nieuwe voorbeeld. In bijlage aangepast.
- "Is er ook een mogelijkheid om de formule alles met "HVO" en "renewable" als uitkomst te laten kiezen?" Ja, door in de FILTER functie voorwaarden bij elkaar op te tellen (werkt als OF).
- Ik weet niet hoe of van waar je de query doet, dus dat is lastig vanaf hier in te schatten. Je zou kunnen denken aan een extra kolom met als voorwaarde "als prijs > 100 dan delen door 100" of zo.

Instel · 26 jan 2021

Dit werkt perfect zo, bedankt AlexCEL. You saved my day.

Instel · 26 jan 2021

Toch nog een vraagje, kan ik ook met de formules een bepaalde brandstof uitsluiten? Renewable diesel is namelijk HVO maar wordt wel met diesel meegerekend..

AlexCEL · 26 jan 2021

Als er niet teveel uitzonderingen zijn kan het bijv. zo:
*knip* *error*

Instel · 26 jan 2021

En hoe zou deze formule eruitzien als ik het gemiddelde zou willen berekenen?

AlexCEL · 26 jan 2021

Het gemiddelde van ...?

Instel · 26 jan 2021

Excuus. Het gemiddelde van de prijzen voor de brandstoffen. Dus voor diesel het gemiddelde maar dan zonder renewable diesel meegerekend.

Voor de overige berekeningen heb ik dit gemiddelde kunnen makkelijk kunnen berekenen middels:
=GEMIDDELDE(FILTER('GLV Historie'!$G$7:$G$9889;ISGETAL(VIND.SPEC("diesel";'GLV Historie'!$C$7:$C$9889));""))

Echter voor de versie waarbij renewable diesel wordt afgetrokken kom ik hier niet uit..

AlexCEL · 26 jan 2021

Zo, voor je voorbeeld:

Code:

=GEMIDDELDE(FILTER($B$2:$B$1000;ISGETAL(VIND.SPEC("diesel";$A$2:$A$1000))*NIET(ISGETAL(VIND.SPEC("renewable";$A$2:$A$1000)));0))

Moet je even aanpassen naar je eigen sheet.

En vergeet de formule in #7 maar, die klopt niet bij nader inzien (ik knip hem weg dus)...

Instel · 26 jan 2021

Werkt perfect, nogmaals bedankt!

Instel · 28 jan 2021

Hoi ALexCel,

Ik heb toch nog een laatste vraagje als dat mag. Wat ik probeer is alles met merk "GLA" en het bijbehorende kwartaal "Q1" als gemiddelde te laten optellen. Het mooiste zou zijn dat dit gekoppeld is aan het huidige jaar, dus als 20222 dan worden alleen die kwartalen meegerekend.

Ik heb het geprobeerd met de formules die je gaf maar het lukt mij niet met deze combi. Zou je hier nog eens naar kunnen kijken?

Als voorbeeld heb ik het testbestand bijgevoegd.

AlexCEL · 28 jan 2021

Je bent bezig met data-analyse en je hebt al een database met gegevens. Ik zou daarmee afstappen van het idee van formules. Excel heeft een mooi en krachtig stuk gereedschap voor data-analyse: de draaitabel.

Ik heb in bijlage een opzetje van een draaitabel gemaakt aan de hand van je voorbeeld, volgens mij met de gewenste uitkomsten. Ik heb een extra tabel met brandstofcategorieën aangemaakt, waarmee je kunt aangeven welke brandstof bij welke categorie geteld moet worden.

De draaitabel is uiteraard geheel naar eigen wensen in te richten: wel of geen eindtotalen, welke jaren of groepen maanden, gemiddelde of maximale prijzen, filters, aanpassen categorieën, opmaak, sommen, gemiddelden, maxima etc. etc.

Bekijk maar eens.

Instel · 28 jan 2021

Weer wat geleerd, Bedankt!!!

pixcel · 28 jan 2021

Je haalt deze gegevens toch op met Power Query? Dan kun je ze beter daar verder bewerken en voorbereiden, daarna verbind je ze met een draaitabel.

Instel · 2 feb 2021

Ik heb nog een vraagje. Ik heb mijn bestand helemaal goed, behalve het feit dat telkens ik de data vernieuw, de formules niet worden meegenomen. Er komt #N/B te staan. Iemand enig idee hoe dit op te lossen?

@Pixcel, ik heb de gegevens uit power query zover mogelijk bewerkt en voorbereid als mogelijk (denk ik)

VenA · 2 feb 2021

Je vernagelt de tabelstructuur.

Code:

.HeaderRowRange.Offset(.ListRows.Count + 1).Resize(UBound(v)[COLOR="#FF0000"][SIZE=4], 4[/SIZE][/COLOR]).Value = v

HSV · 2 feb 2021

Code:

.ListRows.add.range.Resize(UBound(v), 4) = v

Instel · 3 feb 2021

Werkt nog niet, ik heb de volgende code gebruikt

Code:

Sub Rechthoekafgerondehoeken1_Klikken()
    Dim v As Variant
    ActiveWorkbook.RefreshAll
    v = Blad4.ListObjects(1).DataBodyRange.Value
    With Blad5.ListObjects(1)
        .HeaderRowRange.Offset.ListRows.Add.Range.Resize(UBound(v), 4) = v
        .Range.EntireColumn.AutoFit
    End With
End Sub

Waarden uit zin als gemiddelde optellen en automatisch updaten

Gebruiker

Bijlagen

Meubilair

Gebruiker

Bijlagen

Meubilair

Bijlagen

Gebruiker

Gebruiker

Meubilair

Gebruiker

Meubilair

Gebruiker

Meubilair

Gebruiker

Gebruiker

Bijlagen

Meubilair

Bijlagen

Gebruiker

Gebruiker

Gebruiker

Bijlagen

Inventaris

Inventaris

Gebruiker

Wij waarderen jouw privacy