Hızlı İstatistik Değerlerini Panoya Kopyala - Excel İpuçları

İçindekiler

Bir Sekme için ASCII kodu nedir?

Soru Tampa'daki bir Excel seminerinde geldi: İstatistikleri daha sonra bir aralığa yapıştırmak için durum çubuğundan panoya kopyalayabilseydiniz harika olmaz mıydı?

Soruyu soran kişiye macunun tam olarak nasıl çalışması gerektiğine bastım. Elbette, istatistikleri hemen yapıştıramazsınız, çünkü bir grup önemli hücre seçtiniz. Beklemeniz, elektronik tablonun başka bir boş aralığını seçmeniz, yapıştırmanız (Ctrl + V'deki gibi) yapmanız ve istatistikler 6 satıra 2 sütunluk bir aralıkta görünmeniz gerekir. Soruyu soran kişi bunların statik değerler olacağını önerdi.

Seminer sırasında soruyu cevaplamaya çalışmadım, çünkü bunu başarmanın biraz zor olacağını biliyordum.

Ancak, bunun yapılıp yapılamayacağını görmek için yakın zamanda bir makro başlattım. Benim fikrim, yapıştırılabilecek uzun bir metin dizisi oluşturmaktı. Öğeleri iki sütunda görünmeye zorlamak için, metin dizesinin 1. sütun etiketi (Toplam) ve ardından Sekme ve 2. sütun değeri olması gerekir. Daha sonra bir satırbaşı, satır 2, sütun 1, ardından başka bir sekme, değer vb.

Application.WorksheetFunction'ın Excel işlevlerinin sonuçlarını VBA'ya döndürmenin harika bir yolu olduğunu, ancak 400'den fazla Excel işlevinin tümünü desteklemediğini biliyordum. Bazen, VBA zaten benzer bir işleve sahipse (LEFT, RIGHT, MID), Application.WorksheetFunction bu işlevi desteklemeyecektir. VBA'yı Alt + F11 ile çalıştırdım, Anlık Bölmeyi Ctrl + G ile görüntüledim ve altı durum çubuğu işlevinin de desteklendiğinden emin olmak için bazı komutlar yazdım. Neyse ki, durum çubuğunda görünenlerle eşleşen altı döndürülen değerin tümü.

Makroyu kısaltmak için Application.WorksheetFunction öğesini bir değişkene atayabilirsiniz:

Set WF = Application.WorksheetFunction

Ardından, makroda daha sonra Application.WorksheetFunction'ı defalarca yazmak yerine WF.Sum (Seçim) 'e başvurabilirsiniz.

Bir Sekme için ASCII kodu nedir?

Metin dizesini oluşturmaya başladım. MyString için bir MS değişkeni seçtim.

MS = "Sum:" &

Bu, bir sekme karakterine ihtiyacım olduğu noktadır. Birkaç ASCII karakterini bilecek kadar geekyim (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), ancak Tab'ı hatırlayamadım. Bakmak için Bing'e gitmek üzereyken, satır besleme için kodunuzda vblf kullanabileceğinizi veya bir satırbaşı için kodunuzda vbcr kullanabileceğinizi hatırladım, bu yüzden küçük harfle vbtab yazdım. Daha sonra Excel VBA'nın anladığı kelimeleri büyük harfle yazmasına izin vermek için yeni bir satıra geçtim. Vbtab'ın bir başkent aldığını görmeyi umuyordum ve kesinlikle, satırın büyük harfle yazılması VBA'nın bana bir sekme karakteri vereceğini gösteriyordu.

VBA'nızı küçük harfle yazarsanız, yeni bir satıra gittiğinizde, doğru yazılmış tüm kelimelerin kelimenin herhangi bir yerinde büyük harf aldığını göreceksiniz. Aşağıdaki görselde vblf, vbcr, vbtab, yeni bir satıra geçtikten sonra vba olarak bilinir ve büyük harfle yazılır. Ancak, benim uydurduğum şey, vbampersand, VBA tarafından bilinen bir şey değil, bu yüzden büyük harfle yazılmıyor.

Bu noktada, 6 etiketi ve 6 değeri tek bir uzun dizide birleştirme meselesiydi. Aşağıdaki kodda, her satırın sonundaki _ işaretinin, kod satırının sonraki satırda devam ettiği anlamına geldiğini unutmayın.

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

Tüm etiketleri ve değerleri bir araya getirdikten sonra çalışmalarıma hayranlık duymak istedim, bu yüzden sonucu bir Mesaj Kutusunda gösterdim. Kodu çalıştırdım ve çok güzel çalıştı:

Evde özgür olduğumu sanıyordum. Panoya sadece MS yükleyebilseydim, Podcast 1894'ü kaydetmeye başlayabilirdim. Belki MS.Copy hile yapar?

Ne yazık ki o kadar kolay olmadı. MS.Copy geçerli bir kod satırı değildi.

Bu yüzden Google'a gittim ve "Excel VBA Değişkeni Panoya Kopyala" ifadesini aradım. En iyi sonuçlardan biri Mesaj Panosu'ndaki bu gönderi oldu. O gönderide eski arkadaşlarım Juan Pablo ve NateO, OP'ye yardım etmeye çalışıyorlardı. Asıl ipucu, Juan Pablo'nun Excel MVP Chip Pearson sitesinden bazı kodlar kullanmayı önerdiği yerdi. Değişkenin panoya nasıl alınacağını açıklayan bu sayfayı buldum.

Panoya bir şey eklemek için önce VBA penceresinin Araçlar menüsüne gitmeniz ve Referanslar'ı seçmeniz gerekir. Başlangıçta, varsayılan olarak kontrol edilen birkaç referans göreceksiniz. Microsoft Forms 2.0 Kitaplığı kontrol edilmeyecektir. Çok uzun listede bulup eklemeniz gerekiyor. Neyse ki, benim için yeşil okun gösterdiği yerle ilgili ilk seçenek sayfasındaydı. Referansın yanına onay işaretini eklediğinizde en üste taşınır.

Referansı eklemezseniz çipin kodu çalışmayacaktır, bu nedenle yukarıdaki adımı atlamayın!

Referansı ekledikten sonra, Chip'in kodunu kullanarak makroyu bitirin:

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

Podcast'i kaydetmeden önce çalıştığından emin olmak için bir test yaptım. Yeterince, makroyu çalıştırdığımda, ardından yeni bir aralık seçip yapıştırmak için Ctrl + V tuşlarına bastığımda, pano 6 satır x 2 sütun aralığına boşaltıldı.

Whoo-hoo! Bölüm için PowerPoint başlık kartını hazırladım, Camtasia Kaydediciyi açtım ve yukarıdaki her şeyi kaydettim. Ama … kapanış jeneriğini göstermek üzereyken, üzerime bir dırdır hissi geldi. Bu makro, istatistikleri statik değerler olarak yapıştırıyordu. Ya temeldeki veriler değişirse? Yapıştırılan bloğun güncellenmesini istemez misiniz? Podcast'te ne yapacağımı düşündüğüm uzun bir duraklama oldu. Son olarak, Camtasia Kaydı Duraklat simgesine tıkladım ve MS dizesinin içine bir formül koyup koyamayacağımı ve doğru yapıştırılıp yapıştırılmayacağını görmeye gittim. Tabii ki, yaptı. Kayıt cihazını tekrar açıp bu makrodan bahsettiğimde makroyu tamamen bitirmedim veya birden fazla test bile yapmadım. Podcast'te bunun bitişik olmayan seçimler için asla işe yaramayacağını teorileştirdim, ancak daha sonraki testlerde işe yarıyor.Formül olarak yapıştırılacak makro:

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

Videoyu gönderdikten sonra, düzenli izleyici Mike Fliss, seçilen aralık için istatistikleri göstermek için sürekli olarak güncellenen formülleri oluşturmanın bir yolu olup olmadığını sordu. Bu, bir adlandırılmış aralığı seçimle eşleşecek şekilde sürekli güncelleyen bir Çalışma Sayfası_SeçimDeğiştirme makrosu gerektirir. Bu biraz hoş bir hile olsa da, hücre işaretçisini her hareket ettirdiğinizde bir makroyu çalışmaya zorlar ve bu da UnDo yığınını sürekli olarak temizleyecektir. Bu nedenle, bu makroyu kullanırsanız, çalışmasını istediğiniz her çalışma sayfası kod bölmesine eklenmelidir ve bu çalışma sayfalarında Geri Al olmadan yaşamak zorunda kalacaksınız.

İlk olarak, Excel'den bir sayfa sekmesine Sağ Tıklayın ve Kodu Görüntüle'yi seçin. Ardından, bu kodu içine yapıştırın.

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

Excel'e geri dönün. Yeni bir hücre seçin ve formülü yazın =SUM(SelectedData). Başlangıçta döngüsel bir referans alacaksınız. Ancak, daha sonra başka bir sayısal hücre aralığı seçin ve az önce oluşturduğunuz formülün toplamı güncellenecektir.