Kopier summen av valgte celler til utklippstavlen

Noen ganger tar det veldig lang tid å finne på noen ting. Men når de ALLEREDE er oppfunnet, virker de i ettertid åpenbare og til og med banale. Fra serien "hva, var det mulig?".

Fra de aller første versjonene viste statuslinjen nederst i Microsoft Excel-vinduet tradisjonelt totaler for utvalgte celler:

Kopier summen av valgte celler til utklippstavlen

Om ønskelig var det til og med mulig å høyreklikke på disse resultatene og velge fra kontekstmenyen nøyaktig hvilke funksjoner vi ønsker å se:

Kopier summen av valgte celler til utklippstavlen

Og nylig, i de siste Excel-oppdateringene, la Microsoft-utviklere til en enkel, men genial funksjon – nå når du klikker på disse resultatene, blir de kopiert til utklippstavlen!

Kopier summen av valgte celler til utklippstavlen

Skjønnhet. 

Men hva med de som ennå ikke (eller allerede?) har en slik versjon av Excel? Det er her enkle makroer kan hjelpe.

Kopier summen av valgte celler til utklippstavlen ved hjelp av en makro

Åpne i fane utvikler (Utvikler) redaktør Visual Basic eller bruk denne hurtigtasten andre+F11. Sett inn ny tom modul via menyen Sett inn – modul og kopier følgende kode dit:

Sub SumSelected() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection) .PutInClipboard End With EndClipboard

Dens logikk er enkel:

  • Først kommer "beskyttelsen fra narren" - vi sjekker nøyaktig hva som er fremhevet. Hvis ikke celler er valgt (men for eksempel et diagram), avslutter du makroen.
  • Bruk deretter kommandoen Getobjekt vi lager et nytt dataobjekt hvor summen av utvalgte celler vil bli lagret senere. En lang og uforståelig alfanumerisk kode er faktisk en lenke til Windows-registergrenen der biblioteket ligger Microsoft Forms 2.0 objektbibliotek, som kan lage slike objekter. Noen ganger kalles dette trikset også implisitt sen binding. Hvis du ikke bruker det, må du lage en lenke til dette biblioteket i filen gjennom menyen Verktøy — Referanser.
  • Summen av de valgte cellene betraktes som en kommando WorksheetFunction.Sum(Utvalg), og deretter plasseres den resulterende mengden på utklippstavlen med kommandoen PutInClipboard

For enkel bruk kan du selvfølgelig tilordne denne makroen til en hurtigtast ved å bruke knappen Makroer tab utvikler (Utvikler – Makroer).

Og hvis du vil se nøyaktig hva som ble kopiert etter å ha kjørt makroen, kan du slå på utklippstavlepanelet ved å bruke den lille pilen i nedre høyre hjørne av den tilsvarende gruppen på Den viktigste (Hjem) tab:

Kopier summen av valgte celler til utklippstavlen

Ikke bare beløpet

Hvis du i tillegg til den banale mengden ønsker noe annet, kan du bruke hvilken som helst av funksjonene som objektet gir oss ArbeidsarkFunksjon:

Kopier summen av valgte celler til utklippstavlen

For eksempel er det:

  • Sum – sum
  • Gjennomsnitt – aritmetisk gjennomsnitt
  • Antall – antall celler med tall
  • CountA – antall fylte celler
  • CountBlank – antall tomme celler
  • Min – minimum verdi
  • Maks – maksimal verdi
  • Median – median (sentral verdi)
  • … Etc.

Inkludert filtre og skjulte rad-kolonner

Hva om rader eller kolonner er skjult (manuelt eller av et filter) i det valgte området? For ikke å ta hensyn til dem i totalsummen, må vi endre koden litt ved å legge til objektet utvalg eiendom SpecialCells(xlCellTypeVisible):

Sub SumVisible() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.eVisellTs)yp(SpecialCellTs. PutInClipboard End With End Sub

I dette tilfellet vil beregningen av en totalfunksjon kun brukes på synlige celler.

Hvis du trenger en levende formel

Hvis du drømmer opp, kan du komme opp med scenarier når det er bedre å kopiere ikke et tall (konstant), men en levende formel inn i bufferen, som beregner summene vi trenger for de valgte cellene. I dette tilfellet må du lime formelen fra fragmenter, legge til fjerning av dollartegn og erstatte kommaet (som brukes som skilletegn mellom adressene til flere utvalgte områder i VBA) med et semikolon:

Sub SumFormula() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Replace(Replace(Selection. Adresse, ",", ";"), "$", "") & ")" .PutInClipboard End With End Sub

Oppsummering med tilleggsbetingelser

Og til slutt, for helt galninger, kan du skrive en makro som vil oppsummere ikke alle de valgte cellene, men bare de som tilfredsstiller de gitte betingelsene. Så, for eksempel, vil en makro se slik ut som legger summen av valgte celler inn i bufferen, hvis verdiene deres er større enn 5 og samtidig er de fylt med en hvilken som helst farge:

 Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Deretter Avslutt Sub For hver celle I Selection If cell.Value > 5 And cell.Interior.ColorIndex <> xlNone Then If myRange Is Nothing Then Set myRange = celle Else Sett myRange = Union(myRange, cell) End If End If Next celle Med GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutIn SubClipboard

Som du lett kan forestille deg, kan betingelser settes absolutt alle – opptil celleformater – og i hvilken som helst mengde (inkludert ved å koble dem sammen med logiske operatorer eller eller og). Det er mye rom for fantasi.

  • Konverter formler til verdier (6 måter)
  • Hva er makroer, hvordan du bruker dem, hvor du skal sette inn Visual Basic-kode
  • Nyttig informasjon i statuslinjen til Microsoft Excel

Legg igjen en kommentar