Skopírujte súčet vybratých buniek do schránky

Niekedy trvá veľmi dlho prísť na niektoré veci. Ale keď už boli vynájdené, potom sa zdajú byť zrejmé a dokonca banálne. Zo série „Čo, to bolo možné?“.

Od úplne prvých verzií stavový riadok v spodnej časti okna programu Microsoft Excel tradične zobrazoval súčty pre vybrané bunky:

Skopírujte súčet vybratých buniek do schránky

V prípade potreby bolo dokonca možné kliknúť pravým tlačidlom myši na tieto výsledky a vybrať z kontextového menu presne tie funkcie, ktoré chceme vidieť:

Skopírujte súčet vybratých buniek do schránky

A len nedávno, v najnovších aktualizáciách Excelu, vývojári Microsoftu pridali jednoduchú, ale dômyselnú funkciu – teraz, keď kliknete na tieto výsledky, skopírujú sa do schránky!

Skopírujte súčet vybratých buniek do schránky

Krásu. 

Ale čo tí, ktorí ešte (alebo už?) takúto verziu Excelu nemajú? Tu môžu pomôcť jednoduché makrá.

Kopírovanie súčtu vybratých buniek do schránky pomocou makra

Otvoriť na karte vývojka (vývojár) editor Visual Basic alebo použite túto klávesovú skratku ostatné+F11. Vložiť nový prázdny modul cez menu Vložiť – Modul a skopírujte tam nasledujúci kód:

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

Jeho logika je jednoduchá:

  • Najprv prichádza „ochrana pred bláznom“ – kontrolujeme, čo presne je zvýraznené. Ak nie sú vybraté bunky (ale napríklad graf), ukončite makro.
  • Potom pomocou príkazu GetObject vytvoríme nový dátový objekt, kde sa náš súčet vybraných buniek neskôr uloží. Dlhý a nezrozumiteľný alfanumerický kód je v skutočnosti odkazom na pobočku registra Windows, kde sa knižnica nachádza Objektová knižnica Microsoft Forms 2.0, ktorá dokáže takéto objekty vytvárať. Niekedy sa tento trik nazýva aj tzv implicitná neskorá väzba. Ak ju nepoužívate, potom by ste museli urobiť odkaz na túto knižnicu v súbore cez menu Nástroje — Referencie.
  • Súčet vybratých buniek sa považuje za príkaz WorksheetFunction.Sum(Výber)a potom sa výsledná suma umiestni do schránky s príkazom PutInClipboard

Pre jednoduchosť používania môžete toto makro samozrejme priradiť ku klávesovej skratke pomocou tlačidla Makrá pútko vývojka (Vývojár – makrá).

A ak chcete vidieť, čo presne sa po spustení makra skopírovalo, môžete zapnúť panel Schránka pomocou malej šípky v pravom dolnom rohu príslušnej skupiny na Hlavné (Domov) Karta:

Skopírujte súčet vybratých buniek do schránky

Nielen suma

Ak okrem banálnej sumy chcete niečo iné, môžete použiť ktorúkoľvek z funkcií, ktoré nám objekt poskytuje Funkcia pracovného listu:

Skopírujte súčet vybratých buniek do schránky

Existuje napríklad:

  • Suma – suma
  • Priemer – aritmetický priemer
  • Počet – počet buniek s číslami
  • CountA – počet vyplnených buniek
  • CountBlank – počet prázdnych buniek
  • Min – minimálna hodnota
  • Max – maximálna hodnota
  • Medián – medián (centrálna hodnota)
  • … Atď.

Vrátane filtrov a skrytých stĺpcov riadkov

Čo ak sú riadky alebo stĺpce skryté (manuálne alebo pomocou filtra) vo vybranom rozsahu? Aby sme ich nezohľadnili v súčtoch, budeme musieť mierne upraviť náš kód pridaním do objektu Výber majetok SpecialCells(xlCellTypeVisible):

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

V tomto prípade sa výpočet akejkoľvek celkovej funkcie použije len na viditeľné bunky.

Ak potrebujete živý vzorec

Ak snívate, môžete si vymyslieť scenáre, kedy je lepšie skopírovať do vyrovnávacej pamäte nie číslo (konštantu), ale živý vzorec, ktorý vypočíta súčty, ktoré potrebujeme pre vybrané bunky. V tomto prípade budete musieť prilepiť vzorec z fragmentov, pridať k nemu odstránenie znakov dolára a nahradiť čiarku (ktorá sa používa ako oddeľovač medzi adresami niekoľkých vybraných rozsahov vo VBA) bodkočiarkou:

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

Zhrnutie s dodatočnými podmienkami

A na záver, pre úplných maniakov môžete napísať makro, ktoré zráta nie všetky vybrané bunky, ale len tie, ktoré spĺňajú dané podmienky. Napríklad makro bude vyzerať tak, že vloží súčet vybraných buniek do vyrovnávacej pamäte, ak sú ich hodnoty väčšie ako 5 a zároveň sú vyplnené ľubovoľnou farbou:

 Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Then Exit Sub For Every cell In Selection If cell.Value > 5 And cell.Interior.ColorIndex <> xlNone Then If myRange Is Nothing Then Set myRange = bunka Else Set myRange = Union(myRange, cell) End If End If Ďalšia bunka With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutIn SubClipboard End With

Ako si viete ľahko predstaviť, podmienky je možné nastaviť úplne ľubovoľne – až po formáty buniek – a v akomkoľvek množstve (aj ich prepojením s logickými operátormi alebo alebo a). Je tu veľký priestor pre fantáziu.

  • Previesť vzorce na hodnoty (6 spôsobov)
  • Čo sú makrá, ako ich používať, kam vložiť kód Visual Basic
  • Užitočné informácie v stavovom riadku programu Microsoft Excel

Nechaj odpoveď