Másolja a gyors statisztikai értékeket a vágólapra - Excel tippek

Tartalomjegyzék

Mi az a Tab ASCII kódja?

A kérdés egy tampai Excel-szeminárium során hangzott el: Nem lenne jó, ha át tudná másolni a statisztikákat az állapotsorról a vágólapra, hogy később be tudjon illeszteni egy tartományba?

Megnyomtam azt, aki feltette a kérdést, hogy pontosan hogyan kell működnie a pasztának. Természetesen nem lehet azonnal beilleszteni a statisztikákat, mert egy csomó fontos cellát választott ki. Várnia kell, ki kell választania egy másik üres tartományt a táblázatból, beilleszteni a beillesztést (mint a Ctrl + V esetében), és a statisztikák 6 sor / 2 oszlop tartományban jelennek meg. Az a személy, aki feltette a kérdést, azt javasolta, hogy statikus értékek legyenek.

A szeminárium során nem próbáltam megválaszolni a kérdést, mert tudtam, hogy kissé trükkös lehet ezt lehúzni.

De nemrég elindítottam egy makrót, hogy megtudjam, ezt meg lehet-e csinálni. Az volt az ötletem, hogy készítsek egy hosszú, beilleszthető szövegláncot. Annak érdekében, hogy az elemeket két oszlopban jelenítsék meg, a szöveges karaktersorozatnak tartalmaznia kell az 1. oszlop (Összeg), majd a tabulátor címkéjét, valamint a 2. oszlop értékét. Ezután szükség lesz egy kocsi-visszatérésre, a 2. sor, 1. oszlop, majd egy másik fül, az érték stb.

Tudtam, hogy az Application.WorksheetFunction nagyszerű módja az Excel-függvények eredményeinek visszaadására a VBA számára, de nem támogatja az összes 400+ Excel-függvényt. Néha, ha a VBA már rendelkezik hasonló funkcióval (BAL, JOBB, MID), akkor az Application.WorksheetFunction nem támogatja ezt a függvényt. Tüzeltem a VBA-t az Alt + F11 billentyűvel, megjelenítettem az Azonnali ablaktáblát a Ctrl + G billentyűkombinációval, majd beírtam néhány parancsot, hogy megbizonyosodjak arról, hogy mind a hat állapotsor-funkció támogatott-e. Szerencsére mind a hat olyan értéket adott vissza, amelyek megegyeztek az állapotsorban megjelenőkkel.

A makró rövidebbé tételéhez hozzá lehet rendelni az Application.WorksheetFunction változót:

Set WF = Application.WorksheetFunction

Ezután később a makróban egyszerűen hivatkozhat a WF.Sum (Kijelölés) -re ahelyett, hogy begépelné az Application.WorksheetFunction-t újra és újra.

Mi az a Tab ASCII kódja?

Elkezdtem építeni a szöveges karakterláncot. Az MS változóját választottam a MyStringhez.

MS = "Sum:" &

Ez az a pont, ahol egy tabulátorra volt szükségem. Elég géber vagyok ahhoz, hogy ismerjek néhány ASCII karaktert (10 = LineFeed, 13 = Carriage Return, 32 = szóköz, 65 = A, 90 = Z), de nem emlékeztem a Tab-ra. Amikor épp a Bing felé vettem az irányt, hogy utánanézzek, eszembe jutott, hogy a kódjában a vblf-t használhatod egy sorcsatornához, vagy a vbcr-t a kódodban a kocsi visszaadásához, ezért a vbtab-ot írtam kisbetűvel. Ezután egy új sorra léptem, hogy az Excel VBA nagybetűket írjon az általa megért szavakra. Reméltem, hogy látom, hogy a vbtab felvesz egy tőkét, és bizony a vonal nagybetűs lett, jelezve, hogy a VBA tabulátor karaktert fog adni nekem.

Ha kisbetűvel írja be a VBA-t, amikor új sorra lép, akkor azt látja, hogy az összes helyesen írt szó nagybetűt vesz fel valahova a szóba. Az alábbi képen a vblf, a vbcr, a vbtab ismert a vba számára, és nagybetűket kap, miután új sorra lépett. Azonban az a dolog, amit kitaláltam, a vbampersand, nem ismert dolog a VBA számára, ezért nem kap nagybetűket.

Ezen a ponton arról volt szó, hogy 6 címkét és 6 értéket egyesítsünk egy hosszú húrba. Ne feledje az alábbi kódban, hogy az egyes sorok végén lévő _ azt jelenti, hogy a kódsor a következő soron folytatódik.

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

Miután összekapcsoltam az összes címkét és értéket, meg akartam csodálni a munkámat, ezért az eredményt egy MsgBox-ban jelenítettem meg. Futtattam a kódot, és gyönyörűen működött:

Azt hittem, hogy szabadon vagyok otthon. Ha csak az MS-t tudnám felvenni a vágólapra, elkezdhetném a Podcast 1894. felvételét. Talán az MS.Copy csinálná a trükköt?

Sajnos ez nem volt olyan egyszerű. Az MS.Copy nem volt érvényes kódsor.

Tehát a Google-hez mentem, és az "Excel VBA Copy Variable to Clipboard" kifejezésre kerestem. Az egyik legnépszerűbb eredmény az üzenőfali bejegyzés volt. Ebben a bejegyzésben régi barátaim, Juan Pablo és NateO próbáltak segíteni az OP-nak. A tényleges tipp azonban az volt, ahol Juan Pablo azt javasolta, hogy használjon néhány kódot az Excel MVP Chip Pearson webhelyéről. Találtam ezt az oldalt, amely elmagyarázta, hogyan lehet a változót a vágólapra vinni.

Ahhoz, hogy valamit hozzáadhasson a vágólaphoz, először el kell mennie a VBA ablak Eszközök menüjébe, és ki kell választania a Hivatkozások lehetőséget. Kezdetben néhány referenciát alapértelmezés szerint bejelöl. A Microsoft Forms 2.0 könyvtár nem lesz ellenőrizve. Meg kell találnia a nagyon hosszú listában, és hozzá kell adnia. Szerencsére számomra ez volt a választások első oldalán, arról, ahol a zöld nyíl mutatja. Miután hozzáadta a pipát a hivatkozás mellé, az felfelé mozog.

A chip kódja nem fog működni, ha nem adja hozzá a hivatkozást, ezért ne hagyja ki a fenti lépést!

Miután hozzáadta a referenciát, fejezze be a makrót Chip kódjával:

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

A podcast felvétele előtt elvégeztem egy tesztet, hogy megbizonyosodjak arról, hogy működik-e. Valóban, amikor futtattam a makrót, majd kiválasztottam egy új tartományt, és a beillesztéshez nyomtam a Ctrl + V billentyűkombinációt, a vágólap 6 sor x 2 oszloptartományba ürült.

Húúúúúú! Előkészítettem az epizódhoz a PowerPoint címlapot, bekapcsoltam a Camtasia Recorder-t, és mindent fentebb rögzítettem. De … amikor a záró kreditet mutattam volna, nyaggató érzés támadt. Ez a makró statisztikai értékként illesztette be a statisztikákat. Mi lenne, ha az alapul szolgáló adatok megváltoznának? Nem szeretné, hogy a beillesztett blokk frissüljön? Hosszú szünet következett a podcastban, ahol megfontoltam, mit tegyek. Végül rákattintottam a Camtasia Pause Recording ikonra, és megnéztem, hogy be tudok-e tenni egy képletet az MS karakterláncba, és helyesen illeszkedik-e. Valóban, mégis. Még a makrót sem fejeztem be teljesen, vagy egynél több tesztet nem hajtottam végre, amikor visszakapcsoltam a felvevőt és beszéltem erről a makróról. A podcastban azt állítottam, hogy ez soha nem fog működni nem összefüggő kijelöléseknél, de a későbbi teszteléseknél működik.Itt van a képletekként beillesztendő makró:

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

A videó közzététele után Mike Fliss rendszeres néző megkérdezte, hogy lehet-e olyan képleteket felépíteni, amelyek folyamatosan frissülnek, hogy a kiválasztott tartomány statisztikái megjelenjenek. Ehhez egy Worksheet_SelectionChange makróra lenne szükség, amely folyamatosan frissít egy megnevezett tartományt, hogy megfeleljen a kijelölésnek. Bár ez egy nagyszerű trükk, ez arra kényszeríti a makrót, hogy fusson minden alkalommal, amikor áthelyezi a cellamutatót, és ez folyamatosan törli az UnDo veremét. Tehát, ha ezt a makrót használja, azt hozzá kell adni minden munkalap kód ablaktáblához, ahol azt szeretné, hogy működjön, és a Visszavonás nélkül kell élnie ezeken a munkalapokon.

Először az Excelből kattintson a jobb gombbal egy lap fülre, és válassza a Kód megtekintése lehetőséget. Ezután illessze be ezt a kódot.

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

Váltson vissza az Excel programra. Válasszon ki egy új cellát, és írja be a képletet =SUM(SelectedData). Kezdetben kör alakú referenciát kap. Ezután válasszon egy másik numerikus cellatartományt, és az imént létrehozott képlet összessége frissülni fog.