Zkopírujte hodnoty rychlých statistik do schránky - Excel tipy

Obsah

Co je kód ASCII pro kartu?

Otázka přišla během semináře Excel v Tampě: Nebylo by skvělé, kdybyste mohli zkopírovat statistiky ze stavového řádku do schránky pro pozdější vložení do rozsahu?

Stiskl jsem osobu, která položila otázku, jak přesně by pasta měla fungovat. Statistiky samozřejmě nemůžete vložit okamžitě, protože jste vybrali spoustu důležitých buněk. Budete muset počkat, vybrat další prázdný rozsah tabulky, vložit (jako v Ctrl + V) a statistiky se objeví v rozsahu 6 řádků a 2 sloupců. Osoba, která položila otázku, navrhla, že by to byly statické hodnoty.

Během semináře jsem se nepokoušel na otázku odpovědět, protože jsem věděl, že by mohlo být trochu složité to vyřešit.

Nedávno jsem ale spustil makro, abych zjistil, zda by to bylo možné. Mým nápadem bylo vytvořit dlouhý textový řetězec, který by bylo možné vložit. Aby bylo možné vynutit, aby se položky zobrazovaly ve dvou sloupcích, musel by mít textový řetězec popisek pro sloupec 1 (Součet) a poté tabulátor a hodnotu pro sloupec 2. Pak byste potřebovali návrat vozíku, popisek pro řádek 2, sloupec 1, pak další karta, hodnota atd.

Věděl jsem, že Application.WorksheetFunction je skvělý způsob, jak vrátit výsledky funkcí aplikace Excel do VBA, ale že nepodporuje všech 400+ funkcí aplikace Excel. Někdy, pokud VBA již má podobnou funkci (LEFT, RIGHT, MID), pak Application.WorksheetFunction tuto funkci nepodporuje. Vystřelil jsem VBA s Alt + F11, zobrazil Okamžité podokno s Ctrl + G a poté zadal několik příkazů, abych se ujistil, že je podporováno všech šest funkcí stavového řádku. Naštěstí všech šest vrátilo hodnoty, které odpovídaly tomu, co se objevovalo ve stavovém řádku.

Chcete-li makro zkrátit, můžete proměnné přiřadit Application.WorksheetFunction:

Set WF = Application.WorksheetFunction

Pak, později v makru, můžete jednoduše odkazovat na WF.Sum (výběr) namísto opakovaného zadávání Application.WorksheetFunction.

Co je kód ASCII pro kartu?

Začal jsem vytvářet textový řetězec. Pro MyString jsem zvolil proměnnou MS.

MS = "Sum:" &

V tomto bodě jsem potřeboval znak tabulátoru. Jsem dost podivínský, abych znal několik znaků ASCII (10 = LineFeed, 13 = návrat vozíku, 32 = mezera, 65 = A, 90 = Z), ale nemohl jsem si pamatovat kartu. Když jsem se chystal zamířit do Bingu, abych to vyhledal, vzpomněl jsem si, že ve svém kódu můžete použít vblf pro linefeed nebo vbcr v kódu pro návrat vozíku, tak jsem zadal vbtab malými písmeny. Poté jsem se přesunul na nový řádek, aby Excel VBA umožnil velká písmena slovům, kterým rozuměl. Doufal jsem, že uvidím, jak vbtab vyzvedne kapitál, a jistě, linka se stala velkými písmeny, což naznačuje, že VBA mi dá znak tabulátoru.

Pokud zadáte VBA malými písmeny, při přechodu na nový řádek uvidíte, že všechna správně napsaná slova vyzvednou někde ve slově velké písmeno. Na obrázku níže je známo, že vblf, vbcr, vbtab jsou vba a po přesunu na nový řádek se kapitalizují. Věc, kterou jsem vytvořil, vbampersand však není pro VBA známá, takže se nedostává kapitalizovaným způsobem.

V tomto okamžiku šlo o spojení 6 štítků a 6 hodnot do jednoho dlouhého řetězce. V níže uvedeném kódu si pamatujte, že _ na konci každého řádku znamená, že řádek kódu pokračuje na dalším řádku.

Sub CopyQuickStatsToClipboard1() Set WF = Application.WorksheetFunction MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _ & "Count: " & vbTab & WF.CountA(Selection) & vbCr _ & "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _ & "Min: " & vbTab & WF.Min(Selection) & vbCr _ & "Max: " & vbTab & WF.Max(Selection) & vbCr _ & "Sum: " & vbTab & WF.Sum(Selection) & vbCr MsgBox MS End Sub

Po spojení všech štítků a hodnot dohromady jsem chtěl obdivovat svou práci, takže jsem výsledek zobrazil v MsgBoxu. Spustil jsem kód a fungovalo to krásně:

Myslel jsem, že jsem doma zdarma. Kdybych mohl dostat MS do schránky, mohl bych začít nahrávat Podcast 1894. Možná by to udělal MS.Copy?

Bohužel to nebylo tak snadné. MS.Copy nebyl platný řádek kódu.

Šel jsem tedy na Google a hledal „Excel VBA Copy Variable to Clipboard“. Jedním z nejlepších výsledků byl tento příspěvek ve fóru. V tomto příspěvku se moji staří přátelé Juan Pablo a NateO snažili OP pomoci. Skutečný tip však byl tam, kde Juan Pablo navrhl použít nějaký kód z webu Excel MVP Chip Pearson. Našel jsem tuto stránku, která vysvětlovala, jak získat proměnnou do schránky.

Chcete-li něco přidat do schránky, musíte nejprve přejít do nabídky Nástroje okna VBA a zvolit Odkazy. Zpočátku uvidíte ve výchozím nastavení zaškrtnuto několik odkazů. Knihovna Microsoft Forms 2.0 nebude zaškrtnuta. Musíte ji najít ve velmi dlouhém seznamu a přidat ji. Naštěstí to pro mě bylo na první stránce možností, kde to ukazuje zelená šipka. Jakmile přidáte zaškrtnutí vedle odkazu, přesune se nahoru.

Pokud nepřidáte odkaz, čipový kód nebude fungovat, takže výše uvedený krok nevynechávejte!

Jakmile přidáte odkaz, dokončete makro pomocí Chipova kódu:

Sub CopyQuickStatsToClipboard() Set WF = Application.WorksheetFunction MS = "Average: " & vbTab & WF.Average(Selection) & vbCr _ & "Count: " & vbTab & WF.CountA(Selection) & vbCr _ & "Numerical Count: " & vbTab & WF.Count(Selection) & vbCr _ & "Min: " & vbTab & WF.Min(Selection) & vbCr _ & "Max: " & vbTab & WF.Max(Selection) & vbCr _ & "Sum: " & vbTab & WF.Sum(Selection) & vbCr ' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx Dim DataObj As New MSForms.DataObject DataObj.SetText MS DataObj.PutInClipboard End Sub

Před nahráváním podcastu jsem provedl test, abych se ujistil, že funguje. Jistě, když jsem spustil makro, poté vybral nový rozsah a stiskl Ctrl + V pro vložení, schránka byla vyprázdněna do rozsahu sloupců 6 řádků x 2.

Whoo-hoo! Připravil jsem titulní kartu PowerPointu pro epizodu, zapnul jsem Camtasia Recorder a zaznamenal vše výše. Ale … když jsem se chystal ukázat závěrečné titulky, dostavil se na mě nepříjemný pocit. Toto makro vkládalo statistiky jako statické hodnoty. Co když se podkladová data změnila? Nechcete, aby se vložený blok aktualizoval? V podcastu nastala dlouhá pauza, kdy jsem zvažoval, co dělat. Nakonec jsem klikl na ikonu Camtasia Pause Recording a šel se podívat, jestli můžu vložit vzorec do řetězce MS a jestli se to vloží správně. Určitě ano. Když jsem znovu zapnul rekordér a mluvil o tomto makru, nedokončil jsem ani úplně makro ani neprovedl více než jeden test. V podcastu jsem se domníval, že to nikdy nebude fungovat pro nesouvislé výběry, ale v pozdějším testování to funguje.Zde je makro, které lze vložit jako vzorce:

Sub CopyQuickStatsAsFormulas() Set WF = Application.WorksheetFunction MA = Selection.Address MS = "Average: " & vbTab & "=AVERAGE(" & MA & ")" & vbCr _ & "Count: " & vbTab & "=CountA(" & MA & ")" & vbCr _ & "Numerical Count: " & vbTab & "=Count(" & MA & ")" & vbCr _ & "Min: " & vbTab & "=Min(" & MA & ")" & vbCr _ & "Max: " & vbTab & "=Max(" & MA & ")" & vbCr _ & "Sum: " & vbTab & "=Sum(" & MA & ")" & vbCr _ ' Thanks to Chip Pearson http://www.cpearson.com/excel/Clipboard.aspx Dim DataObj As New MSForms.DataObject DataObj.SetText MS DataObj.PutInClipboard End Sub

Po zveřejnění videa se běžný divák Mike Fliss zeptal, zda existuje způsob, jak vytvořit vzorce, které by se neustále aktualizovaly, aby zobrazovaly statistiky pro jakýkoli vybraný rozsah. To by vyžadovalo makro Worksheet_SelectionChange, které by neustále aktualizovalo pojmenovaný rozsah tak, aby odpovídal výběru. I když se jedná o skvělý trik, nutí makro, aby se spustilo pokaždé, když přesunete ukazatel buňky, a to bude neustále vymazávat zásobník UnDo. Pokud tedy používáte toto makro, musí být přidáno do každého podokna kódu listu, kde chcete, aby fungovalo, a na těchto listech budete muset žít bez funkce Zpět.

Nejprve v aplikaci Excel klepněte pravým tlačítkem na kartu listu a vyberte Zobrazit kód. Poté vložte tento kód.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Selection.Name = "SelectedData" End Sub

Přepněte zpět do aplikace Excel. Vyberte novou buňku a zadejte vzorec =SUM(SelectedData). Nejprve získáte kruhový odkaz. Poté však vyberte jinou oblast číselných buněk a aktualizuje se součet vzorce, který jste právě vytvořili.