Kopirajte vrijednosti brze statistike u međuspremnik - Excel savjeti

Sadržaj

Što je ASCII kod za karticu?

Pitanje se pojavilo tijekom Excel seminara u Tampi: Zar ne bi bilo cool kad biste mogli kopirati statistiku sa statusne trake u međuspremnik za kasnije lijepljenje u raspon?

Pritisnuo sam osobu koja je postavila pitanje kako točno pasta treba raditi. Statistiku naravno ne možete odmah zalijepiti jer ste odabrali hrpu važnih stanica. Morali biste pričekati, odabrati drugi prazan raspon proračunske tablice, zalijepiti (kao u Ctrl + V) i statistika će se pojaviti u rasponu od 6 redaka s 2 stupca. Osoba koja je postavila pitanje sugerirala je da bi to bile statičke vrijednosti.

Tijekom seminara nisam pokušao odgovoriti na pitanje, jer sam znao da bi to moglo biti malo nezgodno.

No, nedavno sam pokrenuo makronaredbu da vidim može li se to učiniti. Moja ideja bila je izgraditi dugački tekstualni niz koji se može zalijepiti. Da bi se stavke prisilile da se prikazuju u dva stupca, tekstualni niz trebao bi imati oznaku za stupac 1 (Zbroj), a zatim Tab i vrijednost za stupac 2. Tada bi vam trebao povratak kočije, oznaka za redak 2, stupac 1, zatim druga kartica, vrijednost itd.

Znao sam da je Application.WorksheetFunction izvrstan način za vraćanje rezultata Excel funkcija u VBA, ali da ne podržava svih 400+ Excel funkcija. Ponekad, ako VBA već ima sličnu funkciju (LIJEVO, DESNO, MID), tada Application.WorksheetFunction neće podržati tu funkciju. Zapalio sam VBA pomoću Alt + F11, prikazao neposredno okno s Ctrl + G, a zatim sam otkucao neke naredbe kako bih bio siguran da je podržanih svih šest funkcija statusne trake. Srećom, svih šest vratilo je vrijednosti koje se podudaraju s onim što se pojavilo na statusnoj traci.

Da biste makro skratili, varijabli možete dodijeliti Application.WorksheetFunction:

Set WF = Application.WorksheetFunction

Zatim, kasnije u makronaredbi, možete jednostavno uputiti na WF.Sum (Odabir), umjesto da iznova upisujete Application.WorksheetFunction.

Što je ASCII kod za karticu?

Počeo sam graditi tekstualni niz. Odabrao sam varijablu MS za MyString.

MS = "Sum:" &

Ovo je točka u kojoj sam trebao znak tabulatora. Dovoljno sam geeky da znam nekoliko ASCII znakova (10 = LineFeed, 13 = Povratak kočije, 32 = razmak, 65 = A, 90 = Z), ali nisam se mogao sjetiti kartice. Kad sam se spremao krenuti u Bing da je potražim, sjetio sam se da biste mogli koristiti vblf u svom kodu za linefeed ili vbcr u svom kodu za povratak kočije, pa sam upisao vbtab malim slovima. Zatim sam prešao na novi redak kako bih Excel VBA omogućio velika slova koja razumije. Nadao sam se da ću vidjeti kako vbtab podiže kapital, i sasvim sigurno, crta je postala velika, što ukazuje da će mi VBA dati znak kartice.

Ako svoj VBA upišete malim slovom, kad prijeđete na novi redak, vidjet ćete kako su sve ispravno napisane riječi negdje u riječi pokupile veliko slovo. Na donjoj slici poznato je da vblf, vbcr, vbtab vba i nakon prelaska na novi redak postaju velika i mala. Međutim, stvar koju sam izmislio, vbampersand, VBA-u nije poznata, pa se zato ne koristi velikim slovima.

U ovom se trenutku radilo o spajanju 6 oznaka i 6 vrijednosti u jedan dugački niz. U donjem kodu imajte na umu da _ na kraju svakog retka znači da se redak koda nastavlja u sljedećem retku.

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

Nakon što sam se pridružio svim oznakama i vrijednostima, želio sam se diviti svom radu, pa sam rezultat prikazao u MsgBoxu. Pokrenuo sam kôd i učinio je lijepo:

Mislio sam da sam slobodan od kuće. Kad bih MS jednostavno uspio povezati u međuspremnik, mogao bih početi snimati Podcast 1894. Možda bi MS.Copy učinio trik?

Nažalost, nije bilo tako lako. MS.Copy nije bio važeći redak koda.

Dakle, otišao sam na Google i tražio "Excel VBA Varijabla za kopiranje u međuspremnik". Jedan od najboljih rezultata bio je ovaj post na forumu za poruke. U toj objavi moji stari prijatelji Juan Pablo i NateO pokušavali su pomoći OP-u. Stvarni savjet ipak je bio gdje je Juan Pablo predložio da koristi neki kod sa stranice Excel MVP Chip Pearson. Pronašao sam ovu stranicu koja je objasnila kako dodati varijablu u međuspremnik.

Da biste nešto dodali u međuspremnik, najprije morate otvoriti izbornik Alati VBA prozora i odabrati Reference. Prvotno ćete vidjeti nekoliko referenci provjerenih prema zadanim postavkama. Knjižnica Microsoft Forms 2.0 neće biti provjerena. Morate ga pronaći na vrlo dugom popisu i dodati. Srećom, za mene je to bilo na prvoj stranici izbora, o mjestu gdje ga prikazuje zelena strelica. Jednom kada dodate kvačicu pored reference, pomaknut će se na vrh.

Čipov kod neće raditi ako ne dodate referencu, zato nemojte preskočiti gornji korak!

Nakon što dodate referencu, dovršite makronaredbu pomoću Čip-ovog koda:

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

Prije snimanja podcasta, napravio sam test kako bih se uvjerio da radi. Svakako, kad sam pokrenuo makronaredbu, a zatim odabrao novi raspon i pritisnuo Ctrl + V za lijepljenje, međuspremnik se ispraznio u raspon stupaca od 6 redaka x 2.

Opa! Pripremio sam naslovnu karticu PowerPointa za epizodu, uključio Camtasia Recorder i snimio sve gore. Ali … kad sam htio pokazati završne špice, obuzeo me mučan osjećaj. Ova je makronaredba lijepila statistiku kao statičke vrijednosti. Što ako se temeljni podaci promijene? Ne biste li željeli da se zalijepljeni blok ažurira? Nastala je duga stanka u podcastu gdje sam razmišljao što učiniti. Napokon, kliknuo sam ikonu Camtasia Pauza snimanja i otišao vidjeti mogu li staviti formulu u niz MS-a i hoće li se pravilno zalijepiti. Svakako, jeste. Nisam čak ni do kraja dovršio makronaredbu ili napravio više od jednog testa kad sam ponovno uključio snimač i razgovarao o ovoj makronaredbi. U podcastu sam teoretizirao da to nikad neće uspjeti za neprekidne odabire, ali u kasnijim testiranjima to ipak uspije.Evo makronaredbe koju treba zalijepiti kao formule:

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

Nakon objavljivanja videozapisa, redoviti gledatelj Mike Fliss pitao je postoji li način za izradu formula koje bi se stalno ažurirale kako bi se prikazale statistike za bilo koji odabrani raspon. To bi zahtijevalo makronaredbu Worksheet_SelectionChange koja bi stalno ažurirala imenovani raspon tako da odgovara odabiru. Iako je ovo cool podvala, prisiljava makronaredbu na pokretanje svaki put kad pomaknete pokazivač ćelije, a to će stalno brisati UnDo stog. Dakle, ako koristite ovu makronaredbu, ona se mora dodati u svako okno koda radnog lista tamo gdje želite da radi i morat ćete živjeti bez Poništavanja na tim radnim listovima.

Prvo u programu Excel kliknite desnom tipkom miša na karticu lista i odaberite Prikaži kod. Zatim zalijepite ovaj kod.

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

Vratite se na Excel. Odaberite novu ćeliju i unesite formulu =SUM(SelectedData). U početku ćete dobiti kružnu referencu. Ali, zatim odaberite drugi raspon numeričkih ćelija i ažurirat će se ukupan iznos formule koju ste upravo stvorili.