Berekening op basis van cellen met een bepaalde achtergrondkleur

[cheld33]

Hallo,

Ik heb een excel vraag waar ik online nog niet echt antwoord op heb kunnen vinden.
Ik wil een optelling doen van een aantal waardes in een kolom, maar enkel de waardes van de cellen met 1 specifieke achtergrondkleur mogen worden opgeteld.
In Google spreadsheets krijg ik dat supereenvoudig voor elkaar met bijvoorbeeld =COLOR_SUM(D3;$C$1), waar het eerste segment de range aangeeft en het tweede segment de cel met de specifiek achtergrond kleur. Echter, Excel lijkt niet zo'n eenvoudig mechanisme hiervoor te kennen of ik heb het nog niet kunnen vinden.
Een Excel guru die hier een antwoord op weet?

Groeten,

Chris

 

[edmoor]

Zoek hier eens op kleuren tellen, dan krijg je voorbeelden te over.

 

[cheld33]

Dank, edmoor. Dat had ik reeds gedaan, maar ik vind nergens zo´n simpele kant en klare oplossing als bij Google Spreadsheets: Formule in 1 cel met range en kleuraanduiding en klaar. Excel moet dat toch ook kunnen? Ik zie alleen maar antwoorden met UDF's. Is dat echt de manier waarop dit werkt in Excel?

 

[edmoor]

Dat gaat alleen maar met een UDF en er staan een paar heel simpele met dezelfde werking als in je voorbeeld. In Google spreadsheets is het ook een script die door de community is geleverd.

Ik heb er ooit iets als dit voor gemaakt:

Function COLOR_SUM(Bereik As Range, Kleur As Range) As Variant
    Dim cl As Object
    
    Application.Volatile
    For Each cl In Bereik
        If cl.Interior.Color = Kleur.Interior.Color Then COLOR_SUM = COLOR_SUM + cl.Value
    Next cl
End Function

 

[cheld33]

Klopt, maar in Google zit het nu lekker standaard ingebakken, en dat mag bij Excel ook wel inmiddels.
Maar ik moet niet zeuren, ik heb m'n antwoord en ga dat eens uitproberen. Nogmaals dank.

 

[edmoor]

Dat mag je aan Microsoft vertellen

De functie moet overigens in een module worden geplaatst en ziet er een stuk eenvoudiger uit dan het Google script:

/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();

  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();

  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();

  var count = 0;

  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};

 

[cheld33]

Wow! Nou ik hoop dat ik daar uit ga komen. Ik ga wel even proberen een van de UDF tutorials te volgen. Nooit eerder gedaan, dus dat wordt spannend.

 

[edmoor]

Voorbeeldje:
Bekijk bijlage KLeur tellen.xlsm

 

[cheld33]

Ahaaa, je moet 'm daarna opslaan als xlsm. Dank voor de tip. Dat werkt gelijk inderdaad.
En als je het nou default voor al je nieuwe excel docs zo wil hebben?

 

[cheld33]

Oh wacht duh....dan moet je 'm dus in een module wegzetten. Hoe dat werkt, moet ik dan nog maar even uitzoeken, maar hier ben ik al hartstikke blij mee. Super veel dank!

 

[edmoor]

En als je het nou default voor al je nieuwe excel docs zo wil hebben?

Dan kan je er een invoegtoepassing van maken, maar ik denk dat dit eenvoudiger voor je is:
https://support.office.com/nl-nl/ar...-opslaan-aa439b90-f836-4381-97f0-6e4c3f5ee566

 

[cheld33]

Ah, ja ik had zojuist al iets van een tutorial gevonden, maar die van jouw is een heel stuk duidelijk. Wederom dank!