Welke formule gebruik ik hier best

[Jos.A.M.]

Ik probeer - als oefening - een excel-bestand te maken voor het berekenen van de uitslagen van de gemeenteraadsverkiezingen (in België) van 13 oktober.
In bijgevoegd bestand vind je volgende info:
Rij 1: vanaf B1: namen van de partijen die deelnemen. Uiteraard gebruik we hier fictieve namen.
Rij 1: K1: aantal te verdelen zetels
Rij 2: B2:G2: aantal geldige stemformulieren voor elke partij
Kolom A: A3:A25: deler, te beginnen met 2 en telkens vermeerderd met 1, tot het aantal, vermeld in K1
In het bereik B3:G24: het resultaat van de deling van het aantal stemformulieren (Rij 2) door de delers in kolom A. Afronden naar beneden.

In het voorbeeld zijn er 23 zetels te verdelen.
We moeten dus op zoek naar de 23 hoogste cijfers.
Dat kunnen dezelfde cijfers zijn. Het aantal cijfers mag de 23 niet overschrijden.
Hier zien we dat het 23e cijfer 151 is (A9), maar 151 komt nog eens voor in G3, en dat is dan het 24e cijfers.
In dit geval kijken we naar het hoogste aantal lijststemmen van de 2e partijen (A1 en F1).
Het quotiënt onder het hoogste aantal lijststemmen, blijft behouden. In dit geval krijgt dus partij A de 23e zetel.

Wie kan me een formule bedenken om dit probleem via excel op te lossen?

Alvast dank.

Jos

 

[AHulpje]

Hierbij een poging

 

[Jos.A.M.]

Dank je wel, AHulpje.
Maar als ik de waarde in B2 wijzig van 1.800 naar bijvoorbeeld 1.500, dan komt het 23e getal toch maar één keer voor (220 in C6). Dus in deze situatie is er geen probleem. Ik probeer op dit ogenblik om alle getallen (de quotiënten) in één kolom te plaats en vandaar uit verder te werken. Maar ik betwijfel of dat zo gaar. Ben ook geen specialist in Excel.

 

[AlexCEL]

Een Excel 365 optie die de uitslag weergeeft met zetelverdeling per partij.

=LET(a;A3:A24;ax;AANTALARG(a);
b;B1:G1;bx;AANTALARG(b);
k;K1;m;NAAR.KOLOM(B3:G24);
n;INDEX(a;1+REST(REEKS((k-1)*bx)-1;ax));
o;NAAR.KOLOM(INDEX(b;;1+REST(REEKS(;(k-1)*bx)-1;bx)));
x;HOR.STAPELEN(m;n;o);
y;INDEX(NEMEN(SORTEREN.OP(x;INDEX(x;;1);-1;INDEX(x;;2);-1);k);;3);
GROEPEREN.PER(y;y;AANTALARG))

In bijlage ook nog VO ingesteld.

 

[JEC.]

Als je deze in de voorwaardelijke opmaak van B3:24 zet

=LET(t;$B$3:$G$24;m;LARGE(t;$K$1);c;COUNTIF(t;m);(ISNUMBER(MATCH(B3;TAKE(SORT(TOCOL(t);;-1);IF(c>1;$K$1-1;$K$1));0)))+IF(c>1;(B3=m)*(B$2=MAX(IF(B3=m;$B$2:$G$2)));0))

 

[Jos.A.M.]

Beste AlexCel

Dank voor de moeite, maar ik begrijp niets van deze code, ook niet hoe ik die kan doen werken. Ben een leek in excel.

Groetjes
Jos

 

[AlexCEL]

Hij werkt al in de toegevoegde bijlage, maar mogelijk gebruik je geen Excel 365.

 

[Jos.A.M.]

Ik gebruik wel degelijk Microsoft 365 en in de bijlage worden de resultaten (zetels) inderdaad weergegeven maar zodra ik een getal in rij 2 wijzig krijg ik als resultaat enkel in Cel J3 #NAAM? Ik stuur het je als bijlage.

 

[AlexCEL]

Het werkt gewoon bij mij. Wat mogelijk een oorzaak kan zijn is dat een functie nog niet beschikbaar is in jouw versie van Excel. Wil je eens kijken of deze de functies NEMEN en GROEPEREN.PER kent?

 

[popipipo]

krijg ik als resultaat enkel in Cel J3 #NAAM?

Dit betekent dat je een formule in het Engels gebruikt in een excel versie die Nederlands is.

 

[Jos.A.M.]

AlexCEL,
NEMEN kent hij, GROEPEREN.PER (nog) niet.
In de formulebalk zie ik in Cel J3 ook enkel =LET( staan.

 

[AlexCEL]

Daar komt dan de #NAAM fout vandaan, een onbekende functie.

Ik zal even nadenken hoe dit op te lossen. De formulebalk kun je wat groter maken, dan zie je meer dan 1 regel als het goed is (in het voorbeeld in #4)

 

[Jos.A.M.]

popipipo,

Dank voor de reactie. Ik ga de Engelse termen vervangen door de Nederlandse. Ik had het moeten gezien hebben...

 

[Jos.A.M.]

AlexCEL
Heel veel jaren geleden volgde ik een cursus Excel in de avondles. Ik gebruikte dat later redelijk veel op het werk. Af en toe kwam er eens een nieuwe versie uit (jaarlijks) met een paar nieuwe functies.
Het is opvallend dat tegenwoordig er heel wat nieuwe en betere functies geïmplementeerd worden.
Dank alvast voor de moeite.
Jos

 

[JEC.]

#5 geprobeerd? Die functies heb je

 

[Jos.A.M.]

Beste JEC

#5. Ik heb eerst de vertaler-invoegtoepassing geinstalleerd en dan de functies omgezet naar Nederlands. Maar ik stoot op de functie TOCOL. Die is in mijn Nederlandstalige Excel blijkbaar nog niet beschikbaar.

 

[JEC.]

Jawel, dat is NAAR.KOLOM

TAKE = NEMEN
SORT = SORTEREN

 

[Jos.A.M.]

Beste AlexCEL
Ik had duidelijk problemen met updates. Ondertussen heb ik een Excelversie waar de door jou gebruikte functies wél te zien zijn.
Maar ik krijg dan nog altijd #NAAM als resultaat. Wil jij het nog eens bekijken aub?
Ik stuur je bijlage.
Alvast dank.
Jos

 

[AlexCEL]

Bij mij gaat het voorbeeld gewoon goed!

Je kan via menu Formules > Formule evalueren erachter te komen wanneer de foutmelding optreedt in de berekening. LET, NAAR.KOLOM, HOR.STAPELEN zijn ook allemaal bekend in jouw versie?

Zie bijlage, daar heb ik alle delen van de formule apart in kolommen gezet. Welke kolom geeft een foutmelding?

 

[Jos.A.M.]

Beste JEC
Ik heb de voorwaardelijke opmaak toegepast (opmaak blauwe arcering heb ik gekozen). Maar dan duidt hij het laagste getal aan dat hij eerst tegenkomt. In dit geval de 225 in kolom B.
In kolom E staat nog eens 225 en deze zou moeten verdwijnen omdat de waarde in E2 lager is dan de waarde in B2 (lijststemmen).
Bij identieke waarden krijgt de partij met de meeste lijsstemmen de zetel toegewezen.
Met vriendelijke groet
Jos