Wybór współrzędnych

Masz duży monitor, ale stoły, z którymi pracujesz, są jeszcze większe. A patrząc na ekran w poszukiwaniu potrzebnych informacji, zawsze istnieje szansa, by „przesunąć” wzrok do następnej linii i spojrzeć w złym kierunku. Znam nawet osoby, które na takie okazje zawsze trzymają blisko siebie drewnianą linijkę, aby przymocować ją do linki na monitorze. Technologie przyszłości! 

A jeśli bieżący wiersz i kolumna są podświetlone, gdy aktywna komórka przesuwa się po arkuszu? Rodzaj wyboru współrzędnych, taki jak ten:

Lepiej niż linijka, prawda?

Istnieje kilka sposobów o różnym stopniu złożoności, aby to zaimplementować. Każda metoda ma swoje plusy i minusy. Przyjrzyjmy się im szczegółowo.

Metoda 1. Oczywiste. Makro, które podświetla bieżący wiersz i kolumnę

Najbardziej oczywisty sposób rozwiązania naszego problemu „na czole” – potrzebujemy makra, które będzie śledziło zmianę zaznaczenia na arkuszu i zaznacza cały wiersz i kolumnę dla bieżącej komórki. Pożądane jest również, aby móc włączyć i wyłączyć tę funkcję w razie potrzeby, aby taki wybór w kształcie krzyża nie przeszkadzał nam w wprowadzaniu np. formuł, a działał tylko wtedy, gdy przeglądamy listę w poszukiwaniu niezbędnych Informacja. To prowadzi nas do trzech makr (wybierz, włącz i wyłącz), które trzeba będzie dodać do modułu arkusza.

Otwórz arkusz z tabelą, w której chcesz uzyskać taki wybór współrzędnych. Kliknij prawym przyciskiem myszy kartę arkusza i wybierz polecenie z menu kontekstowego Tekst źródłowy (Kod źródłowy).Powinno się otworzyć okno Edytora Visual Basic. Skopiuj do niego ten tekst z tych trzech makr:

Dim Coord_Selection As Boolean 'Globalna zmienna do włączania/wyłączania wyboru Sub Selection_On() 'Makro przy wyborze Coord_Selection = True End Sub Selection_Off() 'Wyłączenie makro w wyborze Coord_Selection = False End Sub 'Główna procedura dokonująca wyboru Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim WorkRange As Range Jeśli Target.Cells.Count > 1 Then Exit Sub 'jeśli wybrano więcej niż 1 komórkę, wyjdź If Coord_Selection = False Then Exit Sub 'jeśli zaznaczenie jest wyłączone, wyjdź z Application.ScreenUpdating = False Ustaw WorkRange = Range („A6:N300”) „adres zakresu roboczego, w którym widoczny jest wybór”

Zmień adres zakresu roboczego na własny – w tym zakresie sprawdzi się nasz wybór. Następnie zamknij Edytor Visual Basic i wróć do programu Excel.

Naciśnij skrót klawiaturowy ALT + F8aby otworzyć okno z listą dostępnych makr. Makro Wybór_wł., jak można się domyślić, zawiera wybór współrzędnych na bieżącym arkuszu oraz makro Wybór_Wył – wyłącza go. W tym samym oknie, klikając przycisk parametry (Opcje) Do tych makr można przypisać skróty klawiaturowe, aby ułatwić ich uruchamianie.

Zalety tej metody:

  • względna łatwość wykonania
  • selekcja – operacja jest nieszkodliwa i nie zmienia w żaden sposób zawartości ani formatowania komórek arkusza, wszystko pozostaje bez zmian

Wady tej metody:

  • takie zaznaczenie nie działa poprawnie, jeśli w arkuszu są scalone komórki – wszystkie wiersze i kolumny zawarte w unii są zaznaczane jednocześnie
  • jeśli przypadkowo naciśniesz klawisz Delete, to nie tylko aktywna komórka zostanie wyczyszczona, ale cały zaznaczony obszar, tj. usuniesz dane z całego wiersza i kolumny

Metoda 2. Oryginał. KOMÓRKA + funkcja formatowania warunkowego

Ta metoda, choć ma kilka wad, wydaje mi się bardzo elegancka. Aby zaimplementować coś przy użyciu tylko wbudowanych narzędzi Excela, minimalnym zajęciem się programowaniem w VBA jest akrobacja 😉

Metoda opiera się na wykorzystaniu funkcji KOMÓRKA, która może podać wiele różnych informacji o danej komórce – wysokość, szerokość, numer wiersza-kolumny, format liczb itp. Ta funkcja ma dwa argumenty:

  • słowo kodowe parametru, takie jak „kolumna” lub „wiersz”
  • adres komórki, dla której chcemy określić wartość tego parametru

Sztuczka polega na tym, że drugi argument jest opcjonalny. Jeśli nie jest określony, pobierana jest bieżąca aktywna komórka.

Drugim składnikiem tej metody jest formatowanie warunkowe. Ta niezwykle przydatna funkcja Excela umożliwia automatyczne formatowanie komórek, jeśli spełniają określone warunki. Jeśli połączymy te dwa pomysły w jeden, otrzymamy następujący algorytm implementacji naszego wyboru współrzędnych poprzez formatowanie warunkowe:

  1. Wybieramy naszą tabelę, czyli te komórki, w których w przyszłości ma być wyświetlany wybór współrzędnych.
  2. W programie Excel 2003 i starszym otwórz menu Format – Formatowanie warunkowe – Formuła (Format — Formatowanie warunkowe — Formuła). W Excel 2007 i nowszych – kliknij na zakładkę Strona główna (Dom)przycisk Formatowanie warunkowe – Utwórz regułę (Formatowanie warunkowe — Utwórz regułę) i wybierz typ reguły Użyj formuły, aby określić, które komórki należy sformatować (Użyj formuły)
  3. Wprowadź wzór na nasz wybór współrzędnych:

    =LUB(KOMÓRKA(„wiersz”)=WIERSZ(A2),KOMÓRKA(„kolumna”)=KOLUMNA(A2))

    =LUB(KOMÓRKA(«wiersz»)=WIERSZ(A1),KOMÓRKA(«kolumna»)=KOLUMNA(A1))

    Ta formuła sprawdza, czy numer kolumny każdej komórki w tabeli jest taki sam jak numer kolumny bieżącej komórki. Podobnie z kolumnami. Zatem tylko te komórki, które mają numer kolumny lub numer wiersza, który pasuje do bieżącej komórki, zostaną wypełnione. I jest to wybór współrzędnych w kształcie krzyża, który chcemy osiągnąć.

  4. Naciśnij przycisk Framework (format) i ustaw kolor wypełnienia.

Wszystko jest prawie gotowe, ale jest jeden niuans. Faktem jest, że Excel nie traktuje zmiany wyboru jako zmiany danych w arkuszu. W rezultacie nie powoduje to ponownego przeliczania formuł i ponownego kolorowania formatowania warunkowego tylko wtedy, gdy zmienia się pozycja aktywnej komórki. Dlatego dodajmy proste makro do modułu arkusza, które to zrobi. Kliknij prawym przyciskiem myszy kartę arkusza i wybierz polecenie z menu kontekstowego Tekst źródłowy (Kod źródłowy).Powinno się otworzyć okno Edytora Visual Basic. Skopiuj do niego ten tekst tego prostego makra:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveCell.Calculate End Sub

Teraz, gdy wybór się zmieni, rozpocznie się proces przeliczania formuły z funkcją KOMÓRKA w formatowaniu warunkowym i zalać bieżący wiersz i kolumnę.

Zalety tej metody:

  • Formatowanie warunkowe nie przerywa niestandardowego formatowania tabeli
  • Ta opcja wyboru działa poprawnie w przypadku scalonych komórek.
  • Brak ryzyka usunięcia całego wiersza i kolumny danych po przypadkowym kliknięciu Usuń.
  • Makra są używane w minimalnym stopniu

Wady tej metody:

  • Formułę formatowania warunkowego należy wprowadzić ręcznie.
  • Nie ma szybkiego sposobu na włączenie/wyłączenie takiego formatowania – jest ono zawsze włączone, dopóki reguła nie zostanie usunięta.

Metoda 3. Optymalna. Formatowanie warunkowe + makra

Złoty środek. Używamy mechanizmu śledzenia zaznaczenia na arkuszu za pomocą makr z metody-1 i dodajemy do niego bezpieczne podświetlanie za pomocą formatowania warunkowego z metody-2.

Otwórz arkusz z tabelą, w której chcesz uzyskać taki wybór współrzędnych. Kliknij prawym przyciskiem myszy kartę arkusza i wybierz polecenie z menu kontekstowego Tekst źródłowy (Kod źródłowy).Powinno się otworzyć okno Edytora Visual Basic. Skopiuj do niego ten tekst z tych trzech makr:

Dim Coord_Selection As Boolean Sub Selection_On() Coord_Selection = True End Sub Selection_Off() Coord_Selection = False End Sub Private Sub Worksheet_SelectionChange(ByVal Target jako zakres) Dim WorkRange jako zakres, CrossRange jako zakres Ustaw WorkRange = Zakres("A7:N300") 'адрес рабочего диапазона с таблицей If Target.Count > 1 Then Exit Sub If Coord_Selection = False Then WorkRange.FormatConditions.Delete Exit Sub End If Application.ScreenUpdating = False If Not IntersectR(TargTarget) WorkRange, Union(Target.EntireRow, Target.EntireColumn)) WorkRange.FormatConditions.Delete CrossRange.FormatConditions.Add Type:=xlExpression, Formula1:="=1" CrossRange.FormatConditions(1).Interior.ColorIndex = 33 Target.FormatConditions .Delete End If End Sub

Nie zapomnij zmienić adresu zakresu roboczego na adres tabeli. Zamknij Edytor Visual Basic i wróć do programu Excel. Aby użyć dodanych makr, naciśnij skrót klawiaturowy ALT + F8  i postępuj w taki sam sposób, jak w przypadku metody 1. 

Metoda 4. Piękna. Dodatek FollowCellPointer

Excel MVP Jan Karel Pieterse z Holandii rozdaje darmowy dodatek na swojej stronie internetowej ŚledźWskaźnikKomórki(36Kb), który rozwiązuje ten sam problem, rysując linie strzałek graficznych za pomocą makr do podświetlenia bieżącego wiersza i kolumny:

 

Ładne rozwiązanie. Nie bez zgrzytów w miejscach, ale zdecydowanie warto spróbować. Pobierz archiwum, rozpakuj je na dysk i zainstaluj dodatek:

  • w Excelu 2003 i starszym – poprzez menu Usługa – Dodatki – Przegląd (Narzędzia — Dodatki — Przeglądaj)
  • w programie Excel 2007 i nowszych, poprzez Plik – Opcje – Dodatki – Idź – Przeglądaj (Plik — Opcje programu Excel — Dodatki — Idź do — Przeglądaj)

  • Co to są makra, gdzie wstawić kod makra w Visual Basic

 

Dodaj komentarz