Inhoud
Soms duurt het erg lang voordat er iets bedacht wordt. Maar als ze AL zijn uitgevonden, lijken ze achteraf voor de hand liggend en zelfs banaal. Uit de serie "wat, was het mogelijk?".
Vanaf de allereerste versies gaf de statusbalk onderaan het Microsoft Excel-venster traditioneel totalen weer voor geselecteerde cellen:
Indien gewenst was het zelfs mogelijk om met de rechtermuisknop op deze resultaten te klikken en in het contextmenu precies te selecteren welke functies we willen zien:
En onlangs, in de nieuwste Excel-updates, hebben Microsoft-ontwikkelaars een eenvoudige maar ingenieuze functie toegevoegd - wanneer u nu op deze resultaten klikt, worden ze naar het klembord gekopieerd!
Schoonheid.
Maar hoe zit het met degenen die nog niet (of al?) over zo'n versie van Excel beschikken? Dit is waar eenvoudige macro's kunnen helpen.
De som van geselecteerde cellen naar het klembord kopiëren met een macro
Openen in tabblad ontwikkelaar (Ontwikkelaar) editor Visual Basic of gebruik deze sneltoets anders+F11. Nieuwe lege module invoegen via menu Invoegen – Module en kopieer daar de volgende code:
Sub SumSelected() If TypeName(Selection) <> "Range" Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection) .PutInClipboard End With End Sub De logica is eenvoudig:
- Eerst komt de "bescherming tegen de dwaas" - we controleren wat er precies is gemarkeerd. Als er geen cellen zijn geselecteerd (maar bijvoorbeeld een diagram), verlaat dan de macro.
- Gebruik vervolgens het commando Krijg object we maken een nieuw gegevensobject waarin onze som van geselecteerde cellen later wordt opgeslagen. Een lange en onbegrijpelijke alfanumerieke code is in feite een link naar de Windows-registertak waar de bibliotheek zich bevindt Microsoft Forms 2.0-objectbibliotheek, die dergelijke objecten kan maken. Soms wordt deze truc ook wel impliciete late binding. Als je het niet gebruikt, dan zou je via het menu een link naar deze bibliotheek in het bestand moeten maken Hulpmiddelen — Referenties.
- De som van de geselecteerde cellen wordt als een commando beschouwd WerkbladFunctie.Sum(Selectie), en dan wordt het resulterende bedrag op het klembord geplaatst met het commando ZetInKlembord
Voor gebruiksgemak kun je deze macro natuurlijk toewijzen aan een sneltoets met behulp van de knop Macro's tab ontwikkelaar (Ontwikkelaar — Macro's).
En als u wilt zien wat er precies is gekopieerd na het uitvoeren van de macro, kunt u het Klembord-paneel inschakelen met behulp van de kleine pijl in de rechterbenedenhoek van de overeenkomstige groep op De belangrijkste (Home) tab:
Niet alleen het bedrag
Als u naast het banale bedrag iets anders wilt, dan kunt u een van de functies gebruiken die het object ons biedt WerkbladFunctie:
Er is bijvoorbeeld:
- Som – som
- Gemiddeld - rekenkundig gemiddelde
- Tellen - aantal cellen met getallen
- CountA – aantal gevulde cellen
- CountBlank – aantal lege cellen
- Min - minimale waarde
- Max – maximale waarde
- Mediaan – mediaan (centrale waarde)
- ... etc.
Wat als rijen of kolommen zijn verborgen (handmatig of door een filter) in het geselecteerde bereik? Om er geen rekening mee te houden in de totalen, moeten we onze code iets aanpassen door aan het object toe te voegen Selectie eigendom SpecialeCellen(xlCellTypeVisible):
Sub SumVisible() If TypeName(Selection) <> "Range" Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible)). PutInClipboard End With End Sub In dit geval wordt de berekening van een totale functie alleen toegepast op zichtbare cellen.
Als je een levende formule nodig hebt
Als je het verzint, kun je scenario's bedenken waarin het beter is om geen getal (constante), maar een levende formule in de buffer te kopiëren, die de totalen berekent die we nodig hebben voor de geselecteerde cellen. In dit geval moet u de formule uit fragmenten lijmen, het verwijderen van dollartekens toevoegen en de komma (die wordt gebruikt als scheidingsteken tussen adressen van verschillende geselecteerde bereiken in VBA) vervangen door een puntkomma:
Sub SumFormula() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Replace(Replace(Selection. Adres, ",", ";"), "$", "") & ")" .PutInClipboard End With End Sub Sommatie met aanvullende voorwaarden
En ten slotte, voor volledig maniakken, kun je een macro schrijven die niet alle geselecteerde cellen opsomt, maar alleen die die aan de gegeven voorwaarden voldoen. Dus een macro ziet er bijvoorbeeld zo uit dat de som van geselecteerde cellen in de buffer wordt geplaatst, als hun waarden groter zijn dan 5 en tegelijkertijd zijn ze gevuld met elke kleur:
Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Exit Sub voor elke cel in selectie If cell.Value > 5 En cell.Interior.ColorIndex <> xlNone Als myRange niets is, stel dan myRange = in cel Else Set myRange = Union(myRange, cel) End If End If Next cell With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutInClipboard End With End Sub Zoals u zich gemakkelijk kunt voorstellen, kunnen voorwaarden absoluut worden ingesteld - tot celindelingen - en in elke hoeveelheid (inclusief door ze aan elkaar te koppelen met logische operators of of en). Er is veel ruimte voor fantasie.
- Formules converteren naar waarden (6 manieren)
- Wat zijn macro's, hoe ze te gebruiken, waar Visual Basic-code in te voegen?
- Nuttige informatie in de statusbalk van Microsoft Excel