Sao chép tổng các ô đã chọn vào Clipboard

Đôi khi phải mất một thời gian rất dài để nghĩ ra một số điều. Nhưng khi chúng đã ALREADY được phát minh, sau thực tế, chúng có vẻ hiển nhiên và thậm chí là tầm thường. Từ loạt bài “điều gì, có thể xảy ra?”.

Từ những phiên bản đầu tiên, thanh trạng thái ở cuối cửa sổ Microsoft Excel hiển thị theo truyền thống tổng cho các ô đã chọn:

Sao chép tổng các ô đã chọn vào Clipboard

Nếu muốn, thậm chí có thể nhấp chuột phải vào các kết quả này và chọn từ menu ngữ cảnh chính xác các chức năng mà chúng tôi muốn xem:

Sao chép tổng các ô đã chọn vào Clipboard

Và chỉ gần đây, trong bản cập nhật Excel mới nhất, các nhà phát triển của Microsoft đã thêm một tính năng đơn giản nhưng khéo léo - bây giờ khi bạn nhấp vào các kết quả này, chúng sẽ được sao chép vào khay nhớ tạm!

Sao chép tổng các ô đã chọn vào Clipboard

Làm đẹp. 

Nhưng còn những người chưa (hoặc đã có?) Có một phiên bản Excel như vậy thì sao? Đây là nơi mà các macro đơn giản có thể trợ giúp.

Sao chép tổng các ô đã chọn vào Bảng tạm bằng macro

Mở trong tab nhà phát triển (Nhà phát triển) biên tập viên Visual Basic hoặc sử dụng phím tắt này Khác+F11. Chèn mô-đun trống mới qua menu Chèn - Mô-đun và sao chép mã sau vào đó:

Sub SumSelected() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection) .PutInClipboard End With End Sub

Logic của nó rất đơn giản:

  • Đầu tiên là "bảo vệ khỏi kẻ lừa đảo" - chúng tôi kiểm tra chính xác những gì được đánh dấu. Nếu không chọn ô (nhưng, ví dụ: biểu đồ), thì hãy thoát khỏi macro.
  • Sau đó, sử dụng lệnh nhận đối tượng chúng tôi tạo một đối tượng dữ liệu mới nơi tổng các ô đã chọn của chúng tôi sẽ được lưu trữ sau này. Trên thực tế, một đoạn mã chữ và số dài và khó hiểu là một liên kết đến chi nhánh đăng ký Windows nơi đặt thư viện Thư viện đối tượng Microsoft Forms 2.0, có thể tạo ra các đối tượng như vậy. Đôi khi thủ thuật này còn được gọi là ràng buộc muộn tiềm ẩn. Nếu bạn không sử dụng nó, thì bạn sẽ phải tạo một liên kết đến thư viện này trong tệp thông qua menu Công cụ - Tài liệu tham khảo.
  • Tổng các ô đã chọn được coi là một lệnh WorksheetFunction.Sum (Lựa chọn), và sau đó số tiền kết quả được đặt vào khay nhớ tạm bằng lệnh Đưa vào Clipboard

Tất nhiên, để dễ sử dụng, bạn có thể gán macro này cho một phím tắt bằng cách sử dụng nút Macros chuyển hướng nhà phát triển (Nhà phát triển - Macro).

Và nếu bạn muốn xem chính xác những gì đã được sao chép sau khi chạy macro, bạn có thể bật bảng Clipboard bằng cách sử dụng mũi tên nhỏ ở góc dưới bên phải của nhóm tương ứng trên Chính (Trang Chủ) tab:

Sao chép tổng các ô đã chọn vào Clipboard

Không chỉ số lượng

Nếu, ngoài số tiền tầm thường, bạn muốn thứ gì đó khác, thì bạn có thể sử dụng bất kỳ chức năng nào mà đối tượng cung cấp cho chúng tôi Bảng TínhChức Năng:

Sao chép tổng các ô đã chọn vào Clipboard

Ví dụ, có:

  • Sum - tổng
  • Trung bình - trung bình cộng
  • Đếm - số ô có số
  • CountA - số ô được lấp đầy
  • CountBlank - số ô trống
  • Min - giá trị nhỏ nhất
  • Max - giá trị lớn nhất
  • Median - trung vị (giá trị trung tâm)
  • … Vân vân.

Bao gồm các bộ lọc và cột hàng ẩn

Điều gì sẽ xảy ra nếu hàng hoặc cột bị ẩn (theo cách thủ công hoặc bằng bộ lọc) trong phạm vi đã chọn? Để không tính đến chúng trong tổng số, chúng tôi sẽ cần sửa đổi một chút mã của mình bằng cách thêm vào đối tượng Lựa chọn tài sản SpecialCells (xlCellTypeVosystem):

Sub SumVisible() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible)) . PutInClipboard kết thúc với End Sub

Trong trường hợp này, phép tính tổng bất kỳ hàm nào sẽ chỉ được áp dụng cho các ô hiển thị.

Nếu bạn cần một công thức sống

Nếu mơ ước, bạn có thể nghĩ ra các tình huống tốt hơn là sao chép không phải một số (hằng số), mà là một công thức sống vào bộ đệm, tính toán tổng số chúng ta cần cho các ô đã chọn. Trong trường hợp này, bạn sẽ phải dán công thức từ các mảnh, thêm vào đó là việc loại bỏ các dấu đô la và thay thế dấu phẩy (được sử dụng làm dấu phân cách giữa các địa chỉ của một số phạm vi đã chọn trong VBA) bằng dấu chấm phẩy:

Sub SumFormula() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Replace(Replace(Selection. Địa chỉ, ",", ";")), "$", "") & ")" .PutInClipboard End With End Sub

Tổng kết với các điều kiện bổ sung

Và, cuối cùng, đối với những người hoàn toàn cuồng tín, bạn có thể viết một macro sẽ tổng hợp không phải tất cả các ô đã chọn, mà chỉ những ô thỏa mãn các điều kiện đã cho. Vì vậy, ví dụ, một macro sẽ trông giống như vậy đặt tổng các ô đã chọn vào Bộ đệm, nếu giá trị của chúng lớn hơn 5 và đồng thời chúng được tô bằng bất kỳ màu nào:

 Sub CustomCalc() Dim myRange As Range Nếu TypeName(Selection) <> "Range" Sau đó thoát Sub cho từng ô trong vùng chọn Nếu cell.Value > 5 Và cell.Interior.ColorIndex <> xlNone Sau đó Nếu myRange không có gì thì Đặt myRange = ô Khác Đặt myRange = Union(myRange, cell) End If End If Next cell With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutInClipboard End With End Sub

Như bạn có thể dễ dàng hình dung, các điều kiện có thể được đặt hoàn toàn bất kỳ - tùy theo định dạng ô - và với bất kỳ số lượng nào (bao gồm bằng cách liên kết chúng với nhau bằng các toán tử logic hoặc hoặc và). Có rất nhiều chỗ cho trí tưởng tượng.

  • Chuyển đổi công thức thành giá trị (6 cách)
  • Macro là gì, cách sử dụng chúng, nơi chèn mã Visual Basic
  • Thông tin hữu ích trong thanh trạng thái của Microsoft Excel

Bình luận