Lựa chọn tọa độ

Bạn có một màn hình lớn, nhưng các bảng bạn làm việc với nó thậm chí còn lớn hơn. Và, nhìn qua màn hình để tìm kiếm thông tin cần thiết, luôn có cơ hội để bạn “trượt” mắt sang dòng tiếp theo và nhìn sai hướng. Tôi thậm chí biết những người, trong những dịp như vậy, luôn giữ một cây thước gỗ gần họ để gắn nó vào vạch trên màn hình. Công nghệ của tương lai! 

Và nếu hàng và cột hiện tại được đánh dấu khi ô hiện hoạt di chuyển trên trang tính? Một loại lựa chọn tọa độ như thế này:

Tốt hơn một cái thước, phải không?

Có một số cách có độ phức tạp khác nhau để thực hiện điều này. Mỗi phương pháp đều có ưu và nhược điểm của nó. Chúng ta hãy xem xét chúng một cách chi tiết.

Phương pháp 1. Hiển nhiên. Macro đánh dấu hàng và cột hiện tại

Cách rõ ràng nhất để giải quyết vấn đề của chúng ta “trên trán” - chúng ta cần một macro sẽ theo dõi sự thay đổi trong vùng chọn trên trang tính và chọn toàn bộ hàng và cột cho ô hiện tại. Chúng tôi cũng mong muốn có thể bật và tắt chức năng này nếu cần, để lựa chọn hình chữ thập như vậy không ngăn chúng tôi nhập, ví dụ, công thức, nhưng chỉ hoạt động khi chúng tôi xem qua danh sách để tìm kiếm thông tin. Điều này đưa chúng ta đến ba macro (chọn, bật và tắt) sẽ cần được thêm vào mô-đun trang tính.

Mở trang tính có bảng mà bạn muốn chọn tọa độ như vậy. Nhấp chuột phải vào tab trang tính và chọn lệnh từ trình đơn ngữ cảnh Văn bản nguồn (Mã nguồn).Cửa sổ Visual Basic Editor sẽ mở ra. Sao chép văn bản này của ba macro này vào nó:

Dim Coord_Selection As Boolean 'Biến toàn cục để bật / tắt lựa chọn Sub Selection_On ()' Macro khi lựa chọn Coord_Selection = True End Sub Selection_Off () 'Tắt lựa chọn Macro Coord_Selection = False End Sub' Quy trình chính thực hiện lựa chọn Private Sub Worksheet_SelectionChange (ByVal Target As Range) Dim WorkRange As Range If Target.Cells.Count> 1 Then Exit Sub 'nếu chọn nhiều hơn 1 ô, thoát If Coord_Selection = False Then Exit Sub' nếu tắt lựa chọn, thoát khỏi Application.ScreenUpdating = False Set WorkRange = Range ("A6: N300") 'địa chỉ của phạm vi làm việc mà lựa chọn được hiển thị

Thay đổi địa chỉ của phạm vi làm việc thành địa chỉ của riêng bạn - chính trong phạm vi này lựa chọn của chúng tôi sẽ hoạt động. Sau đó, đóng Trình soạn thảo Visual Basic và quay lại Excel.

Nhấn phím tắt ALT + F8để mở một cửa sổ với danh sách các macro có sẵn. Macro Lựa chọn_Bật, như bạn có thể đoán, bao gồm lựa chọn tọa độ trên trang tính hiện tại và macro Lựa chọn_Tắt - tắt nó đi. Trong cùng một cửa sổ, bằng cách nhấp vào nút Thông số (Tùy chọn) Bạn có thể gán phím tắt cho các macro này để khởi chạy dễ dàng.

Ưu điểm của phương pháp này:

  • tương đối dễ thực hiện
  • lựa chọn - thao tác này là vô hại và không thay đổi nội dung hoặc định dạng của các ô trang tính theo bất kỳ cách nào, mọi thứ vẫn như cũ

Nhược điểm của phương pháp này:

  • lựa chọn như vậy không hoạt động chính xác nếu có các ô được hợp nhất trên trang tính - tất cả các hàng và cột được bao gồm trong liên kết được chọn cùng một lúc
  • nếu bạn vô tình nhấn phím Delete, thì không chỉ ô hiện hoạt sẽ bị xóa, mà toàn bộ vùng đã chọn, tức là xóa dữ liệu khỏi toàn bộ hàng và cột

Phương pháp 2. Bản gốc. CELL + Chức năng định dạng có điều kiện

Phương pháp này, mặc dù nó có một vài nhược điểm, đối với tôi có vẻ rất thanh lịch. Để thực hiện một cái gì đó chỉ bằng các công cụ Excel tích hợp sẵn, việc tham gia lập trình trong VBA tối thiểu là nhào lộn trên không 😉

Phương pháp này dựa trên việc sử dụng hàm CELL, hàm này có thể cung cấp nhiều thông tin khác nhau trên một ô nhất định - chiều cao, chiều rộng, số hàng-cột, định dạng số, v.v. Hàm này có hai đối số:

  • một từ mã cho thông số, chẳng hạn như “cột” hoặc “hàng”
  • địa chỉ của ô mà chúng tôi muốn xác định giá trị của tham số này

Bí quyết là đối số thứ hai là tùy chọn. Nếu nó không được chỉ định, thì ô hoạt động hiện tại sẽ được lấy.

Thành phần thứ hai của phương pháp này là định dạng có điều kiện. Tính năng Excel cực kỳ hữu ích này cho phép bạn tự động định dạng các ô nếu chúng đáp ứng các điều kiện cụ thể. Nếu chúng tôi kết hợp hai ý tưởng này thành một, chúng tôi nhận được thuật toán sau để triển khai lựa chọn tọa độ của chúng tôi thông qua định dạng có điều kiện:

  1. Chúng tôi chọn bảng của chúng tôi, tức là những ô mà lựa chọn tọa độ sẽ được hiển thị trong tương lai.
  2. Trong Excel 2003 trở lên, hãy mở menu Định dạng - Định dạng có điều kiện - Công thức (Định dạng - Định dạng có điều kiện - Công thức). Trong Excel 2007 và mới hơn - nhấp vào tab Trang Chủ (Trang Chủ)nút Định dạng có điều kiện - Tạo quy tắc (Định dạng có điều kiện - Tạo quy tắc) và chọn loại quy tắc Sử dụng công thức để xác định ô cần định dạng (Sử dụng công thức)
  3. Nhập công thức cho lựa chọn tọa độ của chúng tôi:

    = HOẶC (CELL (“hàng”) = ROW (A2), CELL (“cột”) = COLUMN (A2))

    = HOẶC (CELL («hàng») = ROW (A1), CELL («cột») = COLUMN (A1))

    Công thức này kiểm tra xem số cột của mỗi ô trong bảng có giống với số cột của ô hiện tại hay không. Tương tự như vậy với các cột. Do đó, chỉ những ô có số cột hoặc số hàng khớp với ô hiện tại mới được điền vào. Và đây là lựa chọn tọa độ hình chữ thập mà chúng ta muốn đạt được.

  4. Ấn nút Khung (Định dạng) và đặt màu tô.

Mọi thứ gần như đã sẵn sàng, nhưng có một sắc thái. Thực tế là Excel không coi thay đổi trong vùng chọn là thay đổi dữ liệu trên trang tính. Và kết quả là nó không kích hoạt tính toán lại các công thức và định dạng có điều kiện chỉ khi vị trí của ô hoạt động thay đổi. Do đó, hãy thêm một macro đơn giản vào mô-đun trang tính sẽ thực hiện việc này. Nhấp chuột phải vào tab trang tính và chọn lệnh từ trình đơn ngữ cảnh Văn bản nguồn (Mã nguồn).Cửa sổ Visual Basic Editor sẽ mở ra. Sao chép văn bản của macro đơn giản này vào nó:

Private Sub Worksheet_SelectionChange (ByVal Target As Range) ActiveCell. Tính toán End Sub

Bây giờ, khi lựa chọn thay đổi, quá trình tính toán lại công thức với hàm sẽ được khởi chạy BÁN ở định dạng có điều kiện và làm ngập hàng và cột hiện tại.

Ưu điểm của phương pháp này:

  • Định dạng có điều kiện không phá vỡ định dạng bảng tùy chỉnh
  • Tùy chọn lựa chọn này hoạt động chính xác với các ô đã hợp nhất.
  • Không có nguy cơ xóa toàn bộ hàng và cột dữ liệu khi nhấp chuột tình cờ Xóa bỏ.
  • Macro được sử dụng tối thiểu

Nhược điểm của phương pháp này:

  • Công thức cho định dạng có điều kiện phải được nhập theo cách thủ công.
  • Không có cách nào nhanh chóng để bật / tắt định dạng như vậy - nó luôn được bật cho đến khi quy tắc bị xóa.

Phương pháp 3. Tối ưu. Định dạng có điều kiện + Macro

Ý nghĩa vàng. Chúng tôi sử dụng cơ chế để theo dõi lựa chọn trên trang tính bằng cách sử dụng macro từ phương pháp-1 và thêm đánh dấu an toàn vào đó bằng cách sử dụng định dạng có điều kiện từ phương pháp-2.

Mở trang tính có bảng mà bạn muốn chọn tọa độ như vậy. Nhấp chuột phải vào tab trang tính và chọn lệnh từ trình đơn ngữ cảnh Văn bản nguồn (Mã nguồn).Cửa sổ Visual Basic Editor sẽ mở ra. Sao chép văn bản này của ba macro này vào nó:

Dim Coord_Selection As Boolean Sub Selection_On () Coord_Selection = True End Sub Sub Selection_Off () Coord_Selection = False End Sub Private Sub Worksheet_SelectionChange (ByVal Target As Range) Dim WorkRange As Range, CrossRange As Range Set WorkRange = Range ("A7: N300") 'адрес рабочего диапазона с таблицей If Target.Count> 1 Then Exit Sub If Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False Nếu không phải Giao nhau WorkRange, Union (Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= 1" CrossRange.FormatConditions (1) .Interior Target.ColorIndexat = 33 .Xóa kết thúc nếu kết thúc phụ

Đừng quên thay đổi địa chỉ phạm vi làm việc thành địa chỉ bảng của bạn. Đóng Visual Basic Editor và quay lại Excel. Để sử dụng các macro đã thêm, hãy nhấn phím tắt ALT + F8  và tiến hành tương tự như phương pháp 1. 

Phương pháp 4. Đẹp. Tiện ích bổ sung FollowCellPointer

Excel MVP Jan Karel Pieterse từ Hà Lan tặng miễn phí một tiện ích bổ sung trên trang web của anh ấy Theo Di ĐộngCon Trỏ(36Kb), giải quyết vấn đề tương tự bằng cách vẽ các đường mũi tên đồ họa sử dụng macro để đánh dấu hàng và cột hiện tại:

 

Giải pháp tốt. Không phải không có trục trặc ở các vị trí, nhưng chắc chắn đáng để thử. Tải xuống tệp lưu trữ, giải nén nó vào đĩa và cài đặt tiện ích bổ sung:

  • trong Excel 2003 trở lên - thông qua menu Dịch vụ - Tiện ích bổ sung - Tổng quan (Công cụ - Phần bổ trợ - Duyệt qua)
  • trong Excel 2007 trở lên, thông qua Tệp - Tùy chọn - Tiện ích bổ sung - Đi - Duyệt (Tệp - Tùy chọn Excel - Phần bổ trợ - Đi tới - Duyệt qua)

  • Macro là gì, nơi chèn mã macro trong Visual Basic

 

Bình luận