Kopioi pikatilastojen arvot leikepöydälle - Excel-vinkit

Sisällysluettelo

Mikä on välilehden ASCII-koodi?

Kysymys tuli Excel-seminaarissa Tampassa: Eikö olisi hienoa, jos voisit kopioida tilastot tilariviltä leikepöydälle myöhempää liittämistä varten alueelle?

Painoin henkilöä, joka esitti kysymyksen siitä, miten tahnan pitäisi toimia. Tietysti et voi liittää tilastoja välittömästi, koska sinulla on joukko tärkeitä soluja valittu. Sinun pitäisi odottaa, valita toinen laskentataulukon tyhjä alue, liitä (kuten Ctrl + V) ja tilastot näkyvät 6 rivin 2 sarakkeella. Kysymyksen esittänyt henkilö ehdotti, että ne olisivat staattisia arvoja.

En yrittänyt vastata kysymykseen seminaarin aikana, koska tiesin, että saattaa olla hieman hankalaa vetää tämä pois.

Mutta aloitin äskettäin makron nähdäksesi, voisiko tämä tehdä. Ideani oli rakentaa pitkä tekstimerkkijono, joka voidaan liittää. Kohteiden pakottamiseksi näkymään kahdessa sarakkeessa tekstimerkkijonossa on oltava sarakkeen 1 (summa) ja sitten sarkain sekä sarakkeen 2 arvo. Tarvitset sitten rivinpalautuksen, rivi 2, sarake 1, sitten toinen välilehti, arvo ja niin edelleen.

Tiesin, että Application.WorksheetFunction on loistava tapa palauttaa Excel-toimintojen tulokset VBA: lle, mutta se ei tue kaikkia yli 400 Excel-toimintoa. Joskus, jos VBA: lla on jo samanlainen toiminto (VASEN, OIKEA, MID), Application.WorksheetFunction ei tue tätä toimintoa. Käynnistin VBA: n Alt + F11: llä, näytin välittömän ruudun näppäimillä Ctrl + G ja kirjoitin sitten joitain komentoja varmistaaksesi, että kaikkia kuutta tilarivin toimintoa tuetaan. Onneksi kaikki kuusi palautettua arvoa vastasivat tilarivillä näkyvää.

Makron lyhentämiseksi voit määrittää Application.WorksheetFunction-muuttujalle:

Set WF = Application.WorksheetFunction

Sitten myöhemmin makrossa voit yksinkertaisesti viitata WF.Sum (valinta) -sovellukseen sen sijaan, että kirjoitat Application.WorksheetFunction uudestaan ja uudestaan.

Mikä on välilehden ASCII-koodi?

Aloin rakentaa tekstimerkkijonoa. Valitsin MS-muuttujan MyStringille.

MS = "Sum:" &

Tarvitsin välilehden merkin tässä kohdassa. Olen tarpeeksi nörtti tuntemaan muutaman ASCII-merkin (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), mutta en voinut muistaa välilehteä. Kun olin menossa Bingiin etsimään sitä, muistan, että voit käyttää koodissasi vblf: tä rivinvaihtoon tai vbcr: tä koodissasi vaunupalautukseen, joten kirjoitin vbtab pienillä kirjaimilla. Sitten muutin uudelle riville, jotta Excel VBA voisi isoin sanoin käyttää isoja kirjaimia. Toivoin, että vbtab poimii pääoman, ja tosiaan, viiva tuli isoksi, mikä osoittaa, että VBA aikoi antaa minulle sarkainmerkin.

Jos kirjoitat VBA: n pienillä kirjaimilla, kun siirryt uudelle riville, näet, että kaikki oikein kirjoitetut sanat poimivat ison kirjaimen jonnekin sanassa. Alla olevassa kuvassa vblf: n, vbcr: n ja vbtab: n tiedetään olevan vba: n ja isot kirjaimet siirrettyään uudelle riville. Se, jonka keksin, vbampersand, ei kuitenkaan ole VBA: n tiedossa oleva asia, joten sitä ei tule isoin kirjaimin.

Tässä vaiheessa oli kyse yhdistää 6 tunnistetta ja 6 arvoa yhdeksi pitkäksi merkkijonoksi. Muista alla olevasta koodista, että jokaisen rivin lopussa oleva _ tarkoittaa, että koodiriviä jatketaan seuraavalla rivillä.

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

Yhdistettyään kaikki tarrat ja arvot yhteen halusin ihailla työtäni, joten näytin tuloksen MsgBoxissa. Juoksin koodin, ja se toimi kauniisti:

Luulin, että olin vapaa kotona. Jos voisin vain saada MS: n leikepöydälle, voisin alkaa tallentaa Podcast 1894. Ehkä MS.Copy tekisi tempun?

Valitettavasti se ei ollut niin helppoa. MS.Copy ei ollut kelvollinen koodirivi.

Joten menin Googleen ja etsin "Excel VBA Copy Variable to Clipboard". Yksi parhaista tuloksista oli tämä viesti viestitaululla. Tässä viestissä vanhat ystäväni Juan Pablo ja NateO yrittivät auttaa OP: ta. Todellinen vinkki oli kuitenkin se, missä Juan Pablo ehdotti käyttävän koodia Excel MVP Chip Pearsonin sivustolta. Löysin tämän sivun, joka selitti kuinka muuttuja viedään leikepöydälle.

Jos haluat lisätä jotain leikepöydälle, sinun on ensin siirryttävä VBA-ikkunan Työkalut-valikkoon ja valittava Viitteet. Aluksi näet muutaman viitteen oletuksena tarkistettuna. Microsoft Forms 2.0 -kirjastoa ei tarkisteta. Sinun täytyy löytää se hyvin pitkästä luettelosta ja lisätä se. Onneksi se oli minulle valintojen ensimmäisellä sivulla, missä vihreä nuoli osoittaa sen. Kun olet lisännyt valintamerkin viitteen viereen, se siirtyy ylös.

Chipin koodi ei toimi, jos et lisää viitteitä, joten älä ohita yllä olevaa vaihetta!

Kun olet lisännyt viitteen, viimeistele makro Chipin koodilla:

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

Ennen podcastin nauhoittamista tein testin varmistaakseni, että se toimi. Tosiaankin, kun suoritin makron, valitsin sitten uuden alueen ja painoin Ctrl + V liittääksesi, leikepöytä tyhjennettiin 6 rivin x 2 sarakealueelle.

Vau-hoo! Valmistelin jaksolle PowerPoint-otsikkokortin, käynnistin Camtasia Recorderin ja äänitin kaiken yllä olevan. Mutta … kun olin aikeissa näyttää lopputekstit, minua tunsi kiusallinen tunne. Tämä makro liitti tilastot staattisina arvoina. Entä jos taustalla olevat tiedot muuttuvat? Etkö halua liitetyn lohkon päivittyvän? Podcastissa oli pitkä tauko, jossa mietin mitä tehdä. Lopuksi napsautin Camtasia Pause Recording -kuvaketta ja menin katsomaan, voinko laittaa kaavan MS-merkkijonoon ja liittyykö se oikein. Tosiaan, niin. En edes viimeistellyt makroa kokonaan tai tehnyt useampaa kuin yhtä testiä, kun käynnistin tallentimen uudelleen ja puhuin tästä makrosta. Podcastissa teoreetoin, että tämä ei koskaan toimi ei-vierekkäisissä valinnoissa, mutta myöhemmässä testauksessa se toimii.Tässä on makro, joka liitetään kaavoina:

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

Videon lähettämisen jälkeen tavallinen katsoja Mike Fliss kysyi, onko olemassa tapa rakentaa kaavoja, jotka päivittyvät jatkuvasti näyttämään valitun alueen tilastot. Tämä vaatii Worksheet_SelectionChange-makron, joka päivittää jatkuvasti nimettyä aluetta vastaamaan valintaa. Vaikka tämä on hieno temppu, se pakottaa makron ajamaan joka kerta, kun siirrät solun osoitinta, ja se tyhjentää UnDo-pinon jatkuvasti. Joten jos käytät tätä makroa, se on lisättävä jokaiseen laskentataulukon koodiruutuun, jossa haluat sen toimivan, ja sinun on elettävä ilman kumoa kyseisillä laskentataulukoilla.

Ensinnäkin napsauta Excelissä hiiren kakkospainikkeella välilehteä ja valitse Näytä koodi. Liitä sitten tämä koodi.

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

Vaihda takaisin Exceliin. Valitse uusi solu ja kirjoita kaava =SUM(SelectedData). Aluksi saat pyöreän viitteen. Mutta valitse sitten toinen numeeristen solujen alue ja juuri luomasi kaavan kokonaismäärä päivittyy.