Salin Nilai Statistik Pantas ke Papan Keratan - Petua Excel

Isi kandungan

Apakah kod ASCII untuk Tab?

Pertanyaan itu muncul semasa seminar Excel di Tampa: Bukankah lebih keren jika anda dapat menyalin statistik dari bar status ke papan keratan untuk kemudian ditampal ke julat?

Saya menekan orang yang mengemukakan soalan mengenai bagaimana pasta itu berfungsi. Sudah tentu, anda tidak dapat menampal statistik dengan segera, kerana anda mempunyai banyak sel penting yang dipilih. Anda harus menunggu, pilih julat spreadsheet lain yang kosong, pasta (seperti dalam Ctrl + V) dan statistik akan muncul dalam julat 6-baris dengan 2-lajur. Orang yang mengemukakan soalan itu mencadangkan nilai-nilai statik.

Saya tidak cuba menjawab soalan semasa seminar, kerana saya tahu mungkin agak sukar untuk menyelesaikannya.

Tetapi, baru-baru ini saya memulakan makro untuk melihat apakah ini dapat dilakukan. Idea saya adalah membina rentetan teks panjang yang boleh ditampal. Untuk memaksa item muncul dalam dua lajur, rentetan teks harus mempunyai label untuk lajur 1 (Jumlah) dan kemudian Tab, dan nilai untuk lajur 2. Anda kemudian memerlukan pengembalian kereta, label untuk baris 2, lajur 1, kemudian tab lain, nilainya dan sebagainya.

Saya tahu bahawa Application.WorksheetFunction adalah cara yang baik untuk mengembalikan hasil fungsi Excel ke VBA, tetapi ia tidak menyokong semua 400+ fungsi Excel. Kadang-kadang, jika VBA sudah mempunyai fungsi serupa (KIRI, KANAN, MID), maka Application.WorksheetFunction tidak akan menyokong fungsi tersebut. Saya menghidupkan VBA dengan Alt + F11, memperlihatkan Panel Segera dengan Ctrl + G, dan kemudian mengetik beberapa arahan untuk memastikan semua enam fungsi bar status disokong. Nasib baik, keenam-enam nilai yang dikembalikan itu sepadan dengan apa yang muncul di bar status.

Untuk menjadikan makro lebih pendek, anda boleh menetapkan Application.WorksheetFungsi ke pemboleh ubah:

Set WF = Application.WorksheetFunction

Kemudian, kemudian di makro, anda hanya boleh merujuk pada WF.Sum (Selection) dan bukannya mengetik Application.WorksheetFungsi berulang-ulang.

Apakah kod ASCII untuk Tab?

Saya mula membina rentetan teks. Saya memilih pemboleh ubah MS untuk MyString.

MS = "Sum:" &

Ini adalah titik di mana saya memerlukan watak tab. Saya cukup arif untuk mengetahui beberapa watak ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), tetapi saya tidak dapat mengingati Tab. Semasa hendak menuju ke Bing untuk mencarinya, saya teringat bahawa anda boleh menggunakan vblf dalam kod anda untuk feed line atau vbcr dalam kod anda untuk perjalanan pulang, jadi saya menaip vbtab dengan huruf kecil. Saya kemudian beralih ke baris baru untuk membolehkan Excel VBA memanfaatkan perkataan yang difahami. Saya berharap dapat melihat vbtab mengambil modal, dan cukup pasti, garis itu menjadi huruf besar, menunjukkan bahawa VBA akan memberi saya watak tab.

Sekiranya anda menaip VBA dengan huruf kecil, apabila anda memasuki baris baru, anda akan melihat semua perkataan yang dieja dengan betul mengambil huruf besar di suatu tempat. Dalam gambar di bawah, vblf, vbcr, vbtab dikenali sebagai vba dan menggunakan huruf besar setelah beralih ke barisan baru. Walau bagaimanapun, perkara yang saya buat, vbampersand bukan perkara yang diketahui oleh VBA, jadi ia tidak menjadi huruf besar.

Pada ketika ini, adalah soal menggabungkan 6 label dan 6 nilai menjadi satu tali panjang. Ingat dalam kod di bawah bahawa _ di hujung setiap baris bermaksud bahawa garis kod dilanjutkan pada baris seterusnya.

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

Setelah menggabungkan semua label dan nilai bersama, saya ingin mengagumi karya saya, jadi saya memaparkan hasilnya dalam Kotak Msg. Saya menjalankan kodnya, dan ia berfungsi dengan baik:

Saya berfikir bahawa saya bebas dari rumah. Sekiranya saya dapat memasukkan MS ke papan keratan, saya boleh mula merakam Podcast 1894. Mungkin MS.Copy akan melakukan helah?

Malangnya, ia tidak semudah itu. MS.Copy bukan baris kod yang sah.

Oleh itu, saya pergi ke Google dan mencari "Excel VBA Copy Variable to Clipboard". Salah satu keputusan teratas adalah catatan ini di Dewan Papan. Dalam catatan itu, rakan lama saya Juan Pablo dan NateO berusaha membantu OP. Petua sebenarnya adalah tempat Juan Pablo mencadangkan untuk menggunakan beberapa kod dari laman Excel MVP Chip Pearson. Saya menjumpai halaman ini yang menerangkan cara memasukkan pemboleh ubah ke papan keratan.

Untuk menambahkan sesuatu ke papan keratan, anda perlu pergi ke menu Alat tetingkap VBA terlebih dahulu dan pilih Rujukan. Pada mulanya anda akan melihat beberapa rujukan yang diperiksa secara lalai. Perpustakaan Microsoft Forms 2.0 tidak akan diperiksa. Anda perlu mencarinya dalam senarai yang sangat panjang dan menambahkannya. Nasib baik, bagi saya, ia berada di halaman pilihan pertama, di mana anak panah hijau menunjukkannya. Sebaik sahaja anda menambahkan tanda semak di sebelah rujukan, ia akan bergerak ke bahagian atas.

Kod cip tidak akan berfungsi jika anda tidak menambah rujukan, jadi jangan lewatkan langkah di atas!

Setelah anda menambah rujukan, selesaikan makro menggunakan kod Chip:

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

Sebelum merakam podcast, saya melakukan ujian untuk memastikannya berfungsi. Sudah tentu, ketika saya menjalankan makro, kemudian memilih julat baru dan tekan Ctrl + V untuk ditampal, papan keratan dikosongkan ke julat lajur 6 baris x 2.

Aduhai! Saya menyediakan kad tajuk PowerPoint untuk episod, menghidupkan Camtasia Recorder, dan merakam semua perkara di atas. Tetapi … ketika saya hendak menunjukkan kredit penutup, perasaan mengecewakan muncul di atas saya. Makro ini menampal statistik sebagai nilai statik. Bagaimana jika data asas berubah? Tidakkah anda mahu blok yang ditampal dikemas kini? Terdapat jeda lama di podcast di mana saya mempertimbangkan apa yang harus dilakukan. Akhirnya, saya mengklik ikon Rakaman Jeda Camtasia dan pergi untuk melihat apakah saya dapat memasukkan formula di dalam rentetan MS dan apakah ia akan ditampal dengan betul. Sudah tentu, betul. Saya tidak menyelesaikan makro sepenuhnya atau melakukan lebih daripada satu ujian ketika saya menghidupkan semula perakam dan bercakap mengenai makro ini. Dalam podcast, saya berteori bahawa ini tidak akan berfungsi untuk pilihan yang tidak bersebelahan, tetapi dalam pengujian kemudian, ia berfungsi.Berikut adalah makro yang akan ditampal sebagai formula:

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

Setelah menyiarkan video tersebut, penonton biasa Mike Fliss bertanya apakah ada cara untuk membina formula yang akan terus diperbaharui untuk menunjukkan statistik untuk julat apa pun yang dipilih. Ini memerlukan makro Worksheet_SelectionChange yang sentiasa mengemas kini julat bernama agar sesuai dengan pilihan. Walaupun ini adalah tipu muslihat yang hebat, ia memaksa makro dijalankan setiap kali anda menggerakkan penunjuk sel, dan itu akan sentiasa membersihkan timbunan UnDo. Oleh itu, jika anda menggunakan makro ini, ia mesti ditambahkan ke setiap panel kod lembaran kerja di mana anda mahu ia berfungsi, dan anda harus hidup tanpa Buat asal pada lembaran kerja tersebut.

Pertama, dari Excel, Klik kanan pada tab helaian dan pilih Lihat Kod. Kemudian, tampal kod ini di.

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

Beralih kembali ke Excel. Pilih sel baru dan taipkan formula =SUM(SelectedData). Pada mulanya anda akan mendapat rujukan pekeliling. Tetapi, kemudian pilih rangkaian sel angka yang lain dan jumlah formula yang baru anda buat akan dikemas kini.