Sao chép Giá trị Thống kê Nhanh vào Bảng tạm - Mẹo Excel

Mục lục

Mã ASCII cho một Tab là gì?

Câu hỏi được đặt ra trong một buổi hội thảo về Excel ở Tampa: Sẽ không tuyệt nếu bạn có thể sao chép thống kê từ thanh trạng thái vào khay nhớ tạm để dán vào một phạm vi sau này?

Tôi đã nhấn mạnh người đặt câu hỏi về cách thức dán sẽ hoạt động chính xác. Tất nhiên, bạn không thể dán số liệu thống kê ngay lập tức, vì bạn có một loạt các ô quan trọng được chọn. Bạn sẽ phải đợi, chọn một phạm vi trống khác của bảng tính, dán (như trong Ctrl + V) và thống kê sẽ xuất hiện trong phạm vi 6 hàng x 2 cột. Người đặt câu hỏi gợi ý chúng sẽ là các giá trị tĩnh.

Tôi đã không cố gắng trả lời câu hỏi trong buổi hội thảo, bởi vì tôi biết có thể hơi khó để giải quyết vấn đề này.

Tuy nhiên, gần đây tôi đã bắt đầu một macro để xem liệu điều này có thể được thực hiện hay không. Ý tưởng của tôi là xây dựng một chuỗi văn bản dài có thể được dán. Để buộc các mục xuất hiện trong hai cột, chuỗi văn bản sẽ phải có nhãn cho cột 1 (Tổng) rồi đến một Tab và giá trị cho cột 2. Sau đó, bạn sẽ cần một ký tự xuống dòng, nhãn cho hàng 2, cột 1, sau đó là một tab khác, giá trị, v.v.

Tôi biết rằng Application.WorksheetFunction là một cách tuyệt vời để trả về kết quả của các hàm Excel sang VBA, nhưng nó không hỗ trợ tất cả hơn 400 hàm Excel. Đôi khi, nếu VBA đã có một chức năng tương tự (LEFT, RIGHT, MID), thì Application.WorksheetFunction sẽ không hỗ trợ chức năng đó. Tôi đã kích hoạt VBA bằng Alt + F11, hiển thị Ngăn ngay lập tức bằng Ctrl + G, sau đó nhập một số lệnh để đảm bảo tất cả sáu chức năng của thanh trạng thái đều được hỗ trợ. May mắn thay, tất cả sáu giá trị trả về đều khớp với những gì đang xuất hiện trên thanh trạng thái.

Để làm cho macro ngắn hơn, bạn có thể gán Application.WorksheetFunction cho một biến:

Set WF = Application.WorksheetFunction

Sau đó, sau này trong macro, bạn có thể chỉ cần tham khảo WF.Sum (Selection) thay vì gõ lặp đi lặp lại Application.WorksheetFunction.

Mã ASCII cho một Tab là gì?

Tôi bắt đầu xây dựng chuỗi văn bản. Tôi đã chọn một biến MS cho MyString.

MS = "Sum:" &

Đây là điểm mà tôi cần một ký tự tab. Tôi đủ sành sỏi để biết một vài ký tự ASCII (10 = LineFeed, 13 = Carriage Return, 32 = Space, 65 = A, 90 = Z), nhưng tôi không thể nhớ Tab. Khi tôi chuẩn bị đến Bing để tra cứu nó, tôi nhớ rằng bạn có thể sử dụng vblf trong mã của mình cho nguồn cấp dữ liệu dòng hoặc vbcr trong mã của bạn cho dấu xuống dòng, vì vậy tôi đã nhập vbtab bằng chữ thường. Sau đó, tôi chuyển sang một dòng mới để cho phép Excel VBA viết hoa những từ mà nó hiểu được. Tôi đã hy vọng thấy vbtab thu được một khoản vốn, và chắc chắn, dòng được viết hoa, cho thấy rằng VBA sẽ cung cấp cho tôi một ký tự tab.

Nếu bạn nhập VBA của mình bằng chữ thường, khi bạn chuyển sang một dòng mới, bạn sẽ thấy tất cả các từ được viết đúng chính tả chọn một ký tự hoa ở đâu đó trong từ. Trong hình ảnh bên dưới, vblf, vbcr, vbtab được biết đến là vba và được viết hoa sau khi chuyển sang một dòng mới. Tuy nhiên, thứ mà tôi đã tạo ra, vbampersand không phải là một thứ được biết đến với VBA, vì vậy nó không được viết hoa.

Tại thời điểm này, vấn đề là nối 6 nhãn và 6 giá trị thành một chuỗi dài. Hãy nhớ trong đoạn mã dưới đây, dấu _ ở cuối mỗi dòng có nghĩa là dòng mã được tiếp tục trên dòng tiếp theo.

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

Sau khi kết hợp tất cả các nhãn và giá trị với nhau, tôi muốn chiêm ngưỡng công việc của mình, vì vậy tôi đã hiển thị kết quả trong MsgBox. Tôi đã chạy mã và nó hoạt động rất đẹp:

Tôi nghĩ rằng tôi đang ở nhà tự do. Nếu tôi có thể đưa MS vào khay nhớ tạm, tôi có thể bắt đầu ghi Podcast 1894. Có lẽ MS.Copy sẽ thực hiện thủ thuật?

Thật không may, nó không phải là dễ dàng. MS.Copy không phải là một dòng mã hợp lệ.

Vì vậy, tôi đã vào Google và tìm kiếm "Excel VBA Copy Variable to Clipboard". Một trong những kết quả hàng đầu là bài đăng này tại Bảng tin. Trong bài đăng đó, những người bạn cũ của tôi Juan Pablo và NateO đang cố gắng giúp OP. Tuy nhiên, mẹo thực tế là nơi Juan Pablo đề xuất sử dụng một số mã từ trang web của Excel MVP Chip Pearson. Tôi đã tìm thấy trang này giải thích cách đưa biến vào khay nhớ tạm.

Để thêm thứ gì đó vào khay nhớ tạm, trước tiên bạn cần đi tới trình đơn Công cụ của cửa sổ VBA và chọn Tham khảo. Ban đầu bạn sẽ thấy một số tham chiếu được chọn theo mặc định. Thư viện Microsoft Forms 2.0 sẽ không được chọn. Bạn cần tìm nó trong danh sách rất dài và thêm nó vào. May mắn thay, đối với tôi, nó nằm ở trang đầu tiên của sự lựa chọn, về nơi mũi tên màu xanh lá cây hiển thị nó. Sau khi bạn thêm dấu kiểm bên cạnh tham chiếu, dấu kiểm sẽ chuyển lên đầu.

Mã của Chip sẽ không hoạt động nếu bạn không thêm tham chiếu, vì vậy đừng bỏ qua bước trên!

Khi bạn thêm tham chiếu, hãy hoàn thành macro bằng cách sử dụng mã của 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

Trước khi ghi podcast, tôi đã thực hiện một bài kiểm tra để đảm bảo rằng nó đang hoạt động. Chắc chắn rồi, khi tôi chạy macro, sau đó chọn một phạm vi mới và nhấn Ctrl + V để dán, khay nhớ tạm được làm trống thành phạm vi 6 hàng x 2 cột.

Ôi chao! Tôi đã chuẩn bị thẻ tiêu đề PowerPoint cho tập, bật Camtasia Recorder và ghi lại mọi thứ ở trên. Nhưng… khi tôi chuẩn bị trình chiếu phần tín dụng kết thúc, một cảm giác khó chịu ập đến trong tôi. Macro này đã dán thống kê dưới dạng giá trị tĩnh. Điều gì sẽ xảy ra nếu dữ liệu cơ bản thay đổi? Bạn có muốn khối đã dán cập nhật không? Có một khoảng thời gian dài tạm dừng trong podcast, nơi tôi cân nhắc xem mình phải làm gì. Cuối cùng, tôi nhấp vào biểu tượng Tạm dừng Ghi âm của Camtasia và đi xem liệu tôi có thể đặt một công thức bên trong chuỗi MS và nó có được dán chính xác hay không. Chắc chắn, nó đã làm. Tôi thậm chí đã không hoàn thành hoàn toàn macro hoặc thực hiện nhiều hơn một bài kiểm tra khi tôi bật lại máy ghi và nói về macro này. Trong podcast, tôi đã giả thuyết rằng điều này sẽ không bao giờ hoạt động đối với các lựa chọn không liền nhau, nhưng trong thử nghiệm sau này, nó hoạt động.Đây là macro để dán làm công thức:

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

Sau khi đăng video, người xem thường xuyên Mike Fliss đã hỏi liệu có cách nào để xây dựng các công thức liên tục cập nhật để hiển thị số liệu thống kê cho bất kỳ phạm vi nào được chọn hay không. Điều này sẽ yêu cầu macro Worksheet_SelectionChange sẽ liên tục cập nhật một dải ô đã đặt tên để phù hợp với lựa chọn. Mặc dù đây là một chút thủ thuật thú vị, nhưng nó buộc macro phải chạy mỗi khi bạn di chuyển con trỏ ô và điều đó sẽ liên tục xóa ngăn xếp UnDo. Vì vậy, nếu bạn sử dụng macro này, nó phải được thêm vào mọi ngăn mã trang tính mà bạn muốn nó hoạt động và bạn sẽ phải sống mà không có Hoàn tác trên các trang tính đó.

Đầu tiên, từ Excel, Nhấp chuột phải vào tab trang tính và chọn Mã Chế độ xem. Sau đó, dán mã này vào.

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

Chuyển về Excel. Chọn một ô mới và nhập công thức =SUM(SelectedData). Ban đầu bạn sẽ nhận được một tham chiếu vòng tròn. Tuy nhiên, sau đó chọn một dải ô số khác và tổng công thức bạn vừa tạo sẽ cập nhật.