Kopiraj vsoto izbranih celic v odložišče

Včasih traja zelo dolgo, da pridemo do nekaterih stvari. Ko pa so ŽE izmišljene, se po dejstvu zdijo očitne in celo banalne. Iz serije "Kaj, je bilo mogoče?".

Že od prvih različic je vrstica stanja na dnu okna programa Microsoft Excel tradicionalno prikazovala vsote za izbrane celice:

Kopiraj vsoto izbranih celic v odložišče

Po želji je bilo mogoče te rezultate celo klikniti z desno miškino tipko in v kontekstnem meniju izbrati točno tiste funkcije, ki jih želimo videti:

Kopiraj vsoto izbranih celic v odložišče

In šele pred kratkim so Microsoftovi razvijalci v najnovejših posodobitvah programa Excel dodali preprosto, a domiselno funkcijo – zdaj, ko kliknete te rezultate, se ti kopirajo v odložišče!

Kopiraj vsoto izbranih celic v odložišče

Lepota. 

Kaj pa tisti, ki takšne različice Excela še nimate (ali že?)? Tukaj lahko pomagajo preprosti makri.

Kopiranje vsote izbranih celic v odložišče z uporabo makra

Odpri v zavihku razvijalec (Razvijalec) urednik Visual Basic ali uporabite to bližnjico na tipkovnici druga+F11. Vstavite nov prazen modul preko menija Vstavi – Modul in tja kopirajte naslednjo kodo:

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

Njegova logika je preprosta:

  • Najprej pride "zaščita pred norcem" - preverimo, kaj točno je poudarjeno. Če celice niso izbrane (ampak na primer grafikon), zapustite makro.
  • Nato uporabite ukaz Getobjekt ustvarimo nov podatkovni objekt, kamor bo kasneje shranjena naša vsota izbranih celic. Dolga in nerazumljiva alfanumerična koda je pravzaprav povezava do veje registra Windows, kjer se nahaja knjižnica Knjižnica predmetov Microsoft Forms 2.0, ki lahko ustvari takšne predmete. Včasih se ta trik imenuje tudi implicitna pozna vezava. Če je ne uporabljate, bi morali narediti povezavo do te knjižnice v datoteki prek menija Orodja — Reference.
  • Vsota izbranih celic se šteje za ukaz WorksheetFunction.Sum(Selection), nato pa se dobljeni znesek z ukazom postavi v odložišče PutInClipboard

Za lažjo uporabo lahko ta makro seveda dodelite bližnjici na tipkovnici z gumbom Makri tab razvijalec (Razvijalec — Makri).

In če želite videti, kaj natančno je bilo kopirano po zagonu makra, lahko vklopite ploščo Odložišče z majhno puščico v spodnjem desnem kotu ustrezne skupine na Glavni (Domov) Kartica:

Kopiraj vsoto izbranih celic v odložišče

Ne samo znesek

Če želite poleg banalne količine še kaj drugega, potem lahko uporabite katero koli od funkcij, ki nam jih ponuja predmet Funkcija delovnega lista:

Kopiraj vsoto izbranih celic v odložišče

Na primer, obstaja:

  • Vsota – vsota
  • Povprečje – aritmetična sredina
  • Število – število celic s številkami
  • CountA – število zapolnjenih celic
  • CountBlank – število praznih celic
  • Min – najmanjša vrednost
  • Max – največja vrednost
  • Mediana – mediana (osrednja vrednost)
  • ... itd.

Vključno s filtri in skritimi vrsticami-stolpci

Kaj pa, če so vrstice ali stolpci skriti (ročno ali s filtrom) v izbranem obsegu? Da jih ne bi upoštevali pri vsotah, bomo morali nekoliko spremeniti našo kodo tako, da dodamo objektu izbor nepremičnine Posebne celice (xlCellTypeVisible):

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

V tem primeru bo izračun katere koli skupne funkcije uporabljen samo za vidne celice.

Če potrebujete živo formulo

Če sanjate, lahko pridete do scenarijev, ko je bolje kopirati ne številko (konstanto), ampak živo formulo v medpomnilnik, ki izračuna vsote, ki jih potrebujemo za izbrane celice. V tem primeru boste morali formulo zlepiti iz fragmentov, ji dodati odstranitev znakov za dolar in zamenjati vejico (ki se uporablja kot ločilo med naslovi več izbranih obsegov v VBA) s podpičjem:

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

Seštevanje z dodatnimi pogoji

In končno, za popolnoma manijake lahko napišete makro, ki ne bo seštel vseh izbranih celic, ampak samo tiste, ki izpolnjujejo dane pogoje. Tako bo na primer izgledal makro, ki postavi vsoto izbranih celic v medpomnilnik, če so njihove vrednosti večje od 5 in so hkrati napolnjene s katero koli barvo:

 Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Then Exit Sub Za vsako celico v izboru If cell.Value > 5 And cell.Interior.ColorIndex <> xlNone Then If myRange Is Nothing Then Set myRange = cell Else Set myRange = Union(myRange, cell) End If End If Next cell With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutInClipboard End With End Sub

Kot si zlahka predstavljate, lahko pogoje nastavite popolnoma vse – do formatov celic – in v poljubni količini (vključno s povezovanjem z logičnimi operatorji ali ali in). Obstaja veliko prostora za domišljijo.

  • Pretvorite formule v vrednosti (6 načinov)
  • Kaj so makri, kako jih uporabljati, kam vstaviti kodo Visual Basic
  • Koristne informacije v statusni vrstici programa Microsoft Excel

Pustite Odgovori