Skopiuj sumę wybranych komórek do schowka

Czasami wymyślenie pewnych rzeczy zajmuje bardzo dużo czasu. Ale kiedy już zostały wynalezione, po fakcie wydają się oczywiste, a nawet banalne. Z cyklu „co było możliwe?”.

Od pierwszych wersji pasek stanu u dołu okna programu Microsoft Excel tradycyjnie wyświetlał sumy dla wybranych komórek:

Skopiuj sumę wybranych komórek do schowka

W razie potrzeby można było nawet kliknąć prawym przyciskiem myszy te wyniki i wybrać z menu kontekstowego dokładnie te funkcje, które chcemy zobaczyć:

Skopiuj sumę wybranych komórek do schowka

Niedawno, w najnowszych aktualizacjach programu Excel, programiści Microsoft dodali prostą, ale pomysłową funkcję – teraz po kliknięciu tych wyników są one kopiowane do schowka!

Skopiuj sumę wybranych komórek do schowka

Piękno. 

Ale co z tymi, którzy jeszcze (lub już?) nie mają takiej wersji Excela? Tutaj mogą pomóc proste makra.

Kopiowanie sumy wybranych komórek do schowka za pomocą makra

Otwórz w zakładce wywoływacz (Deweloper) redaktor Visual Basic lub użyj tego skrótu klawiaturowego inny+F11. Wstaw nowy pusty moduł za pomocą menu Wstaw – moduł i skopiuj tam następujący kod:

Sub SumSelected() If TypeName(Selection) <> "Range" Następnie zakończ Sub za pomocą GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(selection) .PutInClipboard Koniec z End Sub

Jego logika jest prosta:

  • Najpierw jest „ochrona przed głupcem” – sprawdzamy, co dokładnie jest podświetlone. Jeśli nie zaznaczono komórek (ale na przykład wykres), zamknij makro.
  • Następnie za pomocą polecenia Getobiekt tworzymy nowy obiekt danych, w którym później będzie przechowywana nasza suma wybranych komórek. Długi i niezrozumiały kod alfanumeryczny to w rzeczywistości link do gałęzi rejestru Windows, w której znajduje się biblioteka Biblioteka obiektów Microsoft Forms 2.0, który może tworzyć takie obiekty. Czasami ta sztuczka jest również nazywana niejawne późne wiązanie. Jeśli go nie używasz, musisz utworzyć link do tej biblioteki w pliku za pomocą menu Narzędzia — Referencje.
  • Suma wybranych komórek jest uważana za polecenie WorksheetFunction.Sum(Wybór), a następnie otrzymaną kwotę umieszczamy w schowku poleceniem Umieść w schowku

Dla ułatwienia można oczywiście przypisać to makro do skrótu klawiaturowego za pomocą przycisku Makra Zakładka wywoływacz (Deweloper — Makra).

A jeśli chcesz zobaczyć, co dokładnie zostało skopiowane po uruchomieniu makra, możesz włączyć panel Schowka za pomocą małej strzałki w prawym dolnym rogu odpowiedniej grupy na Głównym (Dom) zakładka:

Skopiuj sumę wybranych komórek do schowka

Nie tylko kwota

Jeśli oprócz banalnej kwoty chcesz coś jeszcze, to możesz skorzystać z dowolnej funkcji, którą zapewnia nam obiekt Funkcja arkusza roboczego:

Skopiuj sumę wybranych komórek do schowka

Na przykład jest:

  • Suma – suma
  • Średnia – średnia arytmetyczna
  • Count – liczba komórek z liczbami
  • CountA – liczba wypełnionych komórek
  • CountBlank – liczba pustych komórek
  • Min – wartość minimalna
  • Max – maksymalna wartość
  • Mediana – mediana (wartość środkowa)
  • ... itd.

W tym filtry i ukryte wiersze-kolumny

Co się stanie, jeśli wiersze lub kolumny są ukryte (ręcznie lub przez filtr) w wybranym zakresie? Aby nie uwzględniać ich w sumach, będziemy musieli nieco zmodyfikować nasz kod, dodając do obiektu Wybór własność SpecialCells (xlCellTypeVisible):

Sub SumVisible() If TypeName(Selection) <> "Range" Następnie zakończ Sub za pomocą GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible)) . PutInClipboard Koniec z napisem końcowym

W takim przypadku obliczenia dowolnej funkcji całkowitej zostaną zastosowane tylko do widocznych komórek.

Jeśli potrzebujesz żywej formuły

Jeśli marzysz, możesz wymyślić scenariusze, w których lepiej skopiować do bufora nie liczbę (stałą), ale żywą formułę, która oblicza sumy, których potrzebujemy dla wybranych komórek. W takim przypadku będziesz musiał skleić formułę z fragmentów, dodając do niej usunięcie znaków dolara i zastępując przecinek (który jest używany jako separator między adresami kilku wybranych zakresów w VBA) średnikiem:

Sub SumFormula() If TypeName(Selection) <> "Range" Następnie zakończ Sub za pomocą GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Replace(Replace(Selection. Adres, ",", ";"), "$", "") & ")" .PutInClipboard Koniec z końcem Sub

Podsumowanie z dodatkowymi warunkami

I na koniec dla zupełnych maniaków można napisać makro, które zsumuje nie wszystkie zaznaczone komórki, a tylko te, które spełniają podane warunki. Czyli np. makro będzie wyglądało tak, że umieszcza sumę wybranych komórek w Buforze, jeśli ich wartości są większe niż 5 i jednocześnie są wypełnione dowolnym kolorem:

 Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Then Exit Sub Dla każdej komórki w zaznaczeniu If cell.Value > 5 And cell.Interior.ColorIndex <> xlNone Wtedy jeśli myRange jest niczym, a następnie ustaw 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

Jak łatwo sobie wyobrazić, warunki można ustawić absolutnie w dowolnych – aż do formatów komórek – iw dowolnej ilości (w tym łącząc je operatorami logicznymi lub lub i). Jest dużo miejsca na wyobraźnię.

  • Konwertuj formuły na wartości (6 sposobów)
  • Czym są makra, jak ich używać, gdzie wstawić kod Visual Basic
  • Przydatne informacje na pasku stanu programu Microsoft Excel

Dodaj komentarz