Nukopijuokite „Quick Stats“ vertes į mainų sritį - „Excel“ patarimai

Turinys

Kas yra ASCII skirtukas?

Klausimas kilo per „Excel“ seminarą Tampoje: ar nebūtų šaunu, jei galėtumėte nukopijuoti statistiką iš būsenos juostos į mainų sritį, kad vėliau galėtumėte įklijuoti į diapazoną?

Spaudžiau asmenį, kuris uždavė klausimą, kaip tiksliai turėtų veikti pasta. Žinoma, jūs negalite iš karto įklijuoti statistikos, nes turite daugybę svarbių langelių. Turėtumėte palaukti, pasirinkti kitą tuščią skaičiuoklės diapazoną, įklijuoti (kaip ir „Ctrl“ + V) ir statistika bus rodoma 6 eilių po 2 stulpelius diapazone. Asmuo, uždavęs klausimą, pasiūlė, kad tai būtų statinės vertės.

Seminaro metu nebandžiau atsakyti į klausimą, nes žinojau, kad gali būti šiek tiek keblu tai ištraukti.

Tačiau neseniai pradėjau makrokomandą, norėdamas sužinoti, ar tai galima padaryti. Mano idėja buvo sukurti ilgą teksto eilutę, kurią būtų galima įklijuoti. Norint priversti elementus pasirodyti dviejuose stulpeliuose, teksto eilutėje turi būti 1 stulpelio etiketė (suma), tada tabuliavimo žymė ir 2 stulpelio vertė. Tada jums reikės vežimo grąžinimo, 2 eilutė, 1 stulpelis, tada kitas skirtukas, vertė ir pan.

Aš žinojau, kad „Application.WorksheetFunction“ yra puikus būdas grąžinti „Excel“ funkcijų rezultatus į VBA, tačiau jis nepalaiko visų daugiau nei 400 „Excel“ funkcijų. Kartais, jei VBA jau turi panašią funkciją (LEFT, RIGHT, MID), tada Application.WorksheetFunction šios funkcijos nepalaikys. Aš suaktyvinau VBA naudodamas „Alt + F11“, su „Ctrl + G“ rodžiau „Immediate“ langelį ir tada įvedžiau keletą komandų, kad įsitikinčiau, jog palaikomos visos šešios būsenos juostos funkcijos. Laimei, visos šešios grąžintos reikšmės atitiko tai, kas buvo rodoma būsenos juostoje.

Norėdami sutrumpinti makrokomandą, galite priskirti kintamąjį „Application.WorksheetFunction“:

Set WF = Application.WorksheetFunction

Tada, vėliau makrokomandoje, galite tiesiog kreiptis į WF.Sum (Selection), užuot vedę Application.WorksheetFunction vėl ir vėl.

Kas yra ASCII skirtukas?

Aš pradėjau kurti teksto eilutę. „MyString“ pasirinkau MS kintamąjį.

MS = "Sum:" &

Čia reikėjo skirtuko simbolio. Esu pakankamai įžūlus, kad galėčiau žinoti kelis ASCII simbolius (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), bet negalėjau prisiminti skirtuko. Kai ketinau vykti į „Bing“ jos ieškoti, prisiminiau, kad galite naudoti „vblf“ savo kode eilutės tiekimui arba „vbcr“ savo kode, jei norite grąžinti vežimą, todėl įvedžiau „vbtab“ mažosiomis raidėmis. Tada perėjau prie naujos eilutės, kad „Excel VBA“ galėtų rašyti didžiąsias žodžius, kuriuos suprato. Aš tikėjausi pamatyti, kaip „vbtab“ pasiima didelę knygą, ir tikrai, eilutė tapo didžiosiomis raidėmis, o tai rodo, kad VBA man suteiks skirtuko simbolį.

Jei įvesite VBA mažosiomis raidėmis, eidami į naują eilutę pamatysite, kad visi teisingai parašyti žodžiai kažkur žodyje paima didelę raidę. Žemiau esančiame paveikslėlyje vblf, vbcr, vbtab yra žinomi vba ir didžiosiomis raidėmis rašomi perėjus prie naujos eilutės. Tačiau mano sugalvotas dalykas „vbampersand“ VBA nėra žinomas dalykas, todėl jis nėra rašomas didžiosiomis raidėmis.

Šiuo metu reikėjo sujungti 6 etiketes ir 6 reikšmes į vieną ilgą eilutę. Nepamirškite žemiau esančiame kode, kad _ kiekvienos eilutės pabaigoje reiškia, kad kodo eilutė tęsiama kitoje eilutėje.

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

Sujungęs visas etiketes ir vertybes, norėjau pasigrožėti savo darbu, todėl rezultatą parodžiau „MsgBox“. Aš paleidau kodą ir jis puikiai veikė:

Maniau, kad esu laisva namuose. Jei galėčiau tiesiog pakelti MS į mainų sritį, galėčiau pradėti įrašyti „Podcast 1894“. Gal „MS.Copy“ atliktų triuką?

Deja, tai nebuvo taip lengva. MS.Copy nebuvo galiojanti kodo eilutė.

Taigi, nuėjau į „Google“ ir ieškojau „Excel VBA Copy Variable to Clipboard“. Vienas iš geriausių rezultatų buvo šis pranešimas žinučių lentoje. Tame įraše mano seni draugai Juanas Pablo ir NateO bandė padėti OP. Tačiau tikrasis patarimas buvo tas, kur Juanas Pablo pasiūlė naudoti kodą iš „Excel MVP Chip Pearson“ svetainės. Radau šį puslapį, kuriame paaiškinta, kaip gauti kintamąjį į mainų sritį.

Norėdami ką nors pridėti prie mainų srities, pirmiausia turite eiti į VBA lango meniu Įrankiai ir pasirinkti Nuorodos. Iš pradžių pamatysite keletą nuorodų, patikrintų pagal numatytuosius nustatymus. „Microsoft Forms 2.0“ biblioteka nebus tikrinama. Jūs turite jį rasti labai ilgame sąraše ir pridėti. Laimei, man tai buvo pirmajame pasirinkimų puslapyje apie tai, kur tai rodo žalia rodyklė. Pridėjus varnelę šalia nuorodos, ji pereina į viršų.

Lusto kodas neveiks, jei nepridėsite nuorodos, todėl nepraleiskite aukščiau nurodyto veiksmo!

Pridėję nuorodą, užbaikite makrokomandą naudodami „Chip“ kodą:

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

Prieš įrašydamas tinklalaidę, atlikau testą, kad įsitikinčiau, ar jis veikia. Tikrai, kai paleidžiau makrokomandą, tada pasirinkau naują diapazoną ir paspaudžiau „Ctrl“ + V, kad įklijuotumėte, iškarpinė buvo ištuštinta į 6 eilių x 2 stulpelių diapazoną.

Whoo-hoo! I prepared the PowerPoint title card for the episode, turn on Camtasia Recorder, and recorded everything above. But… as I was about to show the closing credits, a nagging feeling came over me. This macro was pasting the statistics as static values. What if the underlying data changed? Wouldn't you want the pasted block to update? There was a long pause in the podcast where I considered what to do. Finally, I clicked the Camtasia Pause Recording icon and went to see if I could put a formula inside the MS string and if it would get pasted correctly. Sure enough, it did. I did not even completely finish the macro or do more than one test when I turned the recorder back on and talked about this macro. In the podcast, I theorized that this would never work for non-contiguous selections, but in later testing, it does work. Here is the macro to paste as formulas:

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

After posting the video, regular viewer Mike Fliss asked is there is a way to build the formulas that would constantly update to show the statistics for whatever range is selected. This would require a Worksheet_SelectionChange macro that would constantly update a named range to match the selection. While this is a cool bit of trickery, it forces a macro to run every time you move the cell pointer, and that is going to constantly clear the UnDo stack. So, if you use this macro, it has to be added to every worksheet code pane where you want it to work, and you will have to live without Undo on those worksheets.

First, from Excel, Right-Click on a sheet tab and choose View Code. Then, paste this code in.

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

Grįžkite atgal į „Excel“. Pasirinkite naują langelį ir įveskite formulę =SUM(SelectedData). Iš pradžių gausite apskritą nuorodą. Bet tada pasirinkite kitą skaitinių langelių diapazoną ir visa jūsų ką tik sukurta formulė bus atnaujinta.