Kopier summen af ​​markerede celler til Udklipsholder

Nogle gange tager det meget lang tid at finde på nogle ting. Men når de ALLEREDE er opfundet, virker de bagefter indlysende og endda banale. Fra serien "hvad, det var muligt?".

Fra de allerførste versioner viste statuslinjen i bunden af ​​Microsoft Excel-vinduet traditionelt totaler for udvalgte celler:

Kopier summen af ​​markerede celler til Udklipsholder

Hvis det ønskes, var det endda muligt at højreklikke på disse resultater og vælge fra kontekstmenuen præcis, hvilke funktioner vi vil se:

Kopier summen af ​​markerede celler til Udklipsholder

Og for nylig, i de seneste Excel-opdateringer, tilføjede Microsoft-udviklere en enkel, men genial funktion – når du nu klikker på disse resultater, kopieres de til udklipsholderen!

Kopier summen af ​​markerede celler til Udklipsholder

Skønhed. 

Men hvad med dem, der endnu ikke (eller allerede?) har sådan en version af Excel? Det er her simple makroer kan hjælpe.

Kopiering af summen af ​​markerede celler til udklipsholderen ved hjælp af en makro

Åbn i fanen developer (Udvikler) editor Visual Basic eller brug denne tastaturgenvej andre+F11. Indsæt nyt tomt modul via menu Indsæt – Modul og kopier følgende kode dertil:

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

Dens logik er enkel:

  • Først kommer "beskyttelsen fra fjolsen" - vi tjekker, hvad der præcist er fremhævet. Hvis der ikke er markeret celler (men f.eks. et diagram), skal du afslutte makroen.
  • Brug derefter kommandoen Getobjekt vi opretter et nyt dataobjekt, hvor vores sum af udvalgte celler vil blive gemt senere. En lang og uforståelig alfanumerisk kode er i virkeligheden et link til Windows registreringsdatabasen, hvor biblioteket er placeret Microsoft Forms 2.0-objektbibliotek, som kan skabe sådanne objekter. Nogle gange kaldes dette trick også implicit sen binding. Hvis du ikke bruger det, så skal du lave et link til dette bibliotek i filen gennem menuen Værktøjer — Referencer.
  • Summen af ​​de valgte celler betragtes som en kommando ArbejdsarkFunction.Sum(Udvalg), og derefter placeres den resulterende mængde på udklipsholderen med kommandoen PutInClipboard

For at lette brugen kan du selvfølgelig tildele denne makro til en tastaturgenvej ved hjælp af knappen Makroer fanen developer (Udvikler – Makroer).

Og hvis du vil se, hvad der præcist blev kopieret efter at have kørt makroen, kan du tænde for udklipsholderpanelet ved at bruge den lille pil i nederste højre hjørne af den tilsvarende gruppe på Den væsentligste (Hjem) fane:

Kopier summen af ​​markerede celler til Udklipsholder

Ikke kun beløbet

Hvis du ud over den banale mængde vil have noget andet, så kan du bruge enhver af de funktioner, som objektet giver os Arbejdsark Funktion:

Kopier summen af ​​markerede celler til Udklipsholder

For eksempel er der:

  • Sum – sum
  • Gennemsnit – aritmetisk gennemsnit
  • Antal – antal celler med tal
  • CountA – antal udfyldte celler
  • CountBlank – antal tomme celler
  • Min – minimum værdi
  • Max – maksimum værdi
  • Median – median (central værdi)
  • ... osv.

Inklusive filtre og skjulte række-kolonner

Hvad hvis rækker eller kolonner er skjult (manuelt eller af et filter) i det valgte område? For ikke at tage højde for dem i totalerne, bliver vi nødt til at ændre vores kode lidt ved at tilføje til objektet Udvælgelse ejendom SpecialCells(xlCellTypeVisible):

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

I dette tilfælde vil beregningen af ​​enhver samlet funktion kun blive anvendt på synlige celler.

Hvis du har brug for en levende formel

Hvis du drømmer om, kan du komme med scenarier, når det er bedre at kopiere ikke et tal (konstant), men en levende formel ind i bufferen, som beregner de totaler, vi har brug for for de valgte celler. I dette tilfælde bliver du nødt til at lime formlen fra fragmenter, tilføje fjernelse af dollartegn og erstatte kommaet (som bruges som en separator mellem adresser for flere udvalgte områder i VBA) med et semikolon:

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

Opsummering med yderligere betingelser

Og endelig, for helt gale, kan du skrive en makro, der ikke opsummerer alle de valgte celler, men kun dem, der opfylder de givne betingelser. Så for eksempel vil en makro se sådan ud, der sætter summen af ​​valgte celler i bufferen, hvis deres værdier er større end 5 og samtidig er de fyldt med en hvilken som helst farve:

 Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Så Afslut 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 Set myRange = Union(myRange, cell) End If End If Next celle With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutIn SubClipboard

Som du nemt kan forestille dig, kan betingelser indstilles absolut alle – op til celleformater – og i enhver mængde (inklusive ved at forbinde dem med logiske operatorer eller eller og). Der er masser af plads til fantasi.

  • Konverter formler til værdier (6 måder)
  • Hvad er makroer, hvordan man bruger dem, hvor skal man indsætte Visual Basic-kode
  • Nyttig information i statuslinjen i Microsoft Excel

Giv en kommentar