Skopírujte hodnoty rýchlych štatistík do schránky - Tipy pre Excel

Obsah

Aký je kód ASCII pre kartu?

Otázka prišla počas semináru Excel v Tampe: Nebolo by v pohode, keby ste mohli skopírovať štatistiku zo stavového riadku do schránky na neskoršie vloženie do rozsahu?

Stlačil som osobu, ktorá položila otázku, ako má pasta presne fungovať. Štatistiky samozrejme nemôžete vložiť okamžite, pretože máte vybratých veľa dôležitých buniek. Museli by ste počkať, zvoliť ďalší prázdny rozsah tabuľky, vložiť (ako v Ctrl + V) a štatistika by sa objavila v rozmedzí 6-riadkov po 2-stĺpcoch. Osoba, ktorá položila otázku, navrhla, že by išlo o statické hodnoty.

Počas seminára som sa nesnažil odpovedať na otázku, pretože som vedel, že to môže byť trochu zložité.

Nedávno som však spustil makro, aby som zistil, či je to možné. Mojou myšlienkou bolo vytvoriť dlhý textový reťazec, ktorý by sa dal prilepiť. Aby bolo možné vynútiť zobrazovanie položiek v dvoch stĺpcoch, musel by mať textový reťazec štítok pre stĺpec 1 (Súčet) a potom kartu a hodnotu pre stĺpec 2. Potom by ste potrebovali návrat vozíka, štítok pre riadok 2, stĺpec 1, potom ďalšia karta, hodnota atď.

Vedel som, že Application.WorksheetFunction je skvelý spôsob, ako vrátiť výsledky funkcií programu Excel do VBA, ale že nepodporuje všetkých viac ako 400 funkcií programu Excel. Niekedy, ak VBA už má podobnú funkciu (LEFT, RIGHT, MID), potom Application.WorksheetFunction túto funkciu nepodporuje. Spustil som VBA pomocou Alt + F11, zobrazil Okamžité podokno s Ctrl + G a potom napísal niektoré príkazy, aby som sa ubezpečil, že je podporovaných všetkých šesť funkcií stavového riadku. Našťastie všetkých šesť vrátilo hodnoty, ktoré zodpovedali tomu, čo sa objavovalo v stavovom riadku.

Ak chcete makro skrátiť, môžete premennej priradiť Application.WorksheetFunction:

Set WF = Application.WorksheetFunction

Potom, neskôr v makre, môžete namiesto písania Application.WorksheetFunction jednoducho odkazovať na WF.Sum (výber).

Aký je kód ASCII pre kartu?

Začal som stavať textový reťazec. Vybral som premennú MS pre MyString.

MS = "Sum:" &

V tomto bode som potreboval znak tabulátora. Som dosť výstredný na to, aby som poznal niekoľko znakov ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), ale nemohol som si spomenúť na Tab. Keď som sa chystal ísť do Bingu, aby som to vyhľadal, spomenul som si, že v kóde môžete použiť vblf pre riadkový posuv alebo vbcr v kóde pre návrat vozíka, takže som zadal vbtab malými písmenami. Potom som prešiel na nový riadok, aby som umožnil programu Excel VBA používať slová, ktorým rozumel, veľké písmená. Dúfal som, že uvidím, že vbtab vyzdvihne veľké písmeno, a samozrejme, riadok sa zmenil na veľké, čo naznačuje, že VBA mi dá znak tabuľky.

Ak zadáte VBA malými písmenami, pri prechode na nový riadok uvidíte, že všetky správne napísané slová zachytia niekde v slove veľké písmeno. Na obrázku nižšie je známe, že vblf, vbcr, vbtab sú vba a po prechode na nový riadok sa zväčšia. Avšak vec, ktorú som vymyslel, vbampersand nie je pre VBA známa, takže sa nedostáva veľkými písmenami.

V tomto okamihu išlo o spojenie 6 štítkov a 6 hodnôt do jedného dlhého reťazca. V nižšie uvedenom kóde nezabudnite, že znak _ na konci každého riadku znamená, že riadok kódu pokračuje v ďalšom riadku.

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šetkých označení a hodnôt som chcel obdivovať moju prácu, takže som výsledok zobrazil v MsgBoxe. Spustil som kód a fungovalo to nádherne:

Myslel som si, že som doma zadarmo. Keby som mohol dostať MS do schránky, mohol by som začať nahrávať Podcast 1894. Možno by to spravil MS.Copy?

Bohužiaľ to nebolo také ľahké. MS.Copy nebol platný riadok kódu.

Išiel som teda na Google a hľadal som „Excel VBA Copy Variable to Clipboard“. Jedným z najlepších výsledkov bol tento príspevok v diskusnej skupine. V tomto príspevku sa moji starí priatelia Juan Pablo a NateO snažili pomôcť OP. Skutočným tipom však bolo, keď Juan Pablo navrhol použiť nejaký kód z webu Excel MVP Chip Pearson. Našiel som túto stránku, ktorá vysvetľovala, ako dostať premennú do schránky.

Ak chcete do schránky niečo pridať, musíte najskôr prejsť do ponuky Nástroje v okne VBA a zvoliť položku Odkazy. Na začiatku uvidíte predvolene niekoľko začiarknutých odkazov. Knižnica Microsoft Forms 2.0 nebude začiarknutá. Musíte ju nájsť vo veľmi dlhom zozname a pridať ju. Našťastie pre mňa to bolo na prvej stránke možností, kde to ukazuje zelená šípka. Po pridaní začiarknutia vedľa referencie sa posunie na začiatok.

Ak nepridáte referenciu, čipový kód nebude fungovať, takže vyššie uvedený krok nepreskočte!

Po pridaní referencie dokončite makro pomocou Chipovho 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

Pred nahrávaním podcastu som urobil test, či je funkčný. Iste, keď som spustil makro, potom vybral nový rozsah a vložil Ctrl + V, schránka sa vyprázdnila do rozsahu stĺpcov 6 riadkov x 2.

Fúha! Pripravil som titulnú kartu PowerPointu pre epizódu, zapol som Camtasia Recorder a zaznamenal som všetko vyššie. Ale … keď som sa chystal ukázať záverečné titulky, zmocnil sa ma nepríjemný pocit. Toto makro prilepovalo štatistiku ako statické hodnoty. Čo keby sa zmenili základné údaje? Nechcete, aby sa vložený blok aktualizoval? V podcaste nastala dlhá pauza, kde som zvažoval, čo robiť. Nakoniec som klikol na ikonu Camtasia Pause Recording a išiel som zistiť, či dokážem vložiť vzorec do reťazca MS a či sa správne prilepí. Určite áno. Keď som rekordér znova zapol a hovoril o tomto makre, nedokončil som ani úplne makro ani neurobil viac ako jeden test. V podcaste som tvrdil, že to nikdy nebude fungovať pre nesúvislý výber, ale pri neskoršom testovaní to bude fungovať.Toto je makro, ktoré sa má vložiť ako 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 zverejnení videa sa bežný divák Mike Fliss pýtal, či existuje spôsob, ako vytvoriť vzorce, ktoré by sa neustále aktualizovali, aby zobrazovali štatistické údaje pre akýkoľvek vybraný rozsah. To by vyžadovalo makro Worksheet_SelectionChange, ktoré by neustále aktualizovalo pomenovaný rozsah tak, aby zodpovedal výberu. Aj keď ide o trochu triku, vynúti to, aby sa makro spustilo zakaždým, keď pohnete ukazovateľom bunky, a tým sa neustále vymaže zásobník UnDo. Ak teda použijete toto makro, musí sa pridať na každú tablu s kódom pracovného hárka, kde chcete, aby fungoval, a na týchto pracovných hárkoch budete musieť žiť bez možnosti Späť.

Najskôr v programe Excel kliknite pravým tlačidlom myši na kartu hárka a vyberte príkaz Zobraziť kód. Potom vložte tento kód do.

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

Prepnite späť do Excelu. Vyberte novú bunku a zadajte vzorec =SUM(SelectedData). Spočiatku dostanete kruhový odkaz. Potom však vyberte iný rozsah číselných buniek a aktualizuje sa súčet vzorca, ktorý ste práve vytvorili.