Funkcja Excela określa, czy komórka jest widoczna

W formatowaniu warunkowym chcę wykryć, czy wiersz nad bieżącą komórką jest ukryty lub widoczny. Jak mogę wykryć, czy komórka jest widoczna, czy nie?

Jedyny hack, jaki mogę wymyślić, to:

1. Utwórz kolumnę ze wszystkimi 1wartościami.
2. Użyj formuły podobnej subtotal(109,c2:c2)=1(tj. Tylko na komórce, którą chcę sprawdzić), aby ustalić, czy jest widoczna, czy ukryta.

Czy istnieje sposób, aby to zrobić bez tymczasowej kolumny, która musi pozostać widoczna podczas wyświetlania wiersza?

Aby uniknąć problemu XY , chcę, aby kolumna była kategorią dla wiersza. Pierwszy WIDOCZNY wiersz z określoną kategorią powinien mieć inny styl; późniejsze wiersze z tej samej kategorii są nieco inne. W ASCII:

cat.   item
+AAA+  aaaa
(AAA)  bbbb
(AAA)  cccc
+BBB+  dddd
(BBB)  eeee
(BBB)  ffff

Jeśli mój filtr ukrywa wiersz, ddddwtedy chcę, aby wiersz eeeemiał +BBB+styl.

Zamiast sumy częściowej za pomocą sumy w innej kolumnie, można użyć sumy częściowej za pomocą, countaaby sprawdzić, czy (znana-niepusta) komórka jest ukryta, czy nie. Na przykład, jeśli kolumna Abędzie normalnie widoczna (chyba że wiersz jest ukryty)

= IF( SUBTOTAL(103,A2)=1, "VISIBLE", "HIDDEN (or blank)" )

Możesz umieścić tę formułę w kolumnie, która może być ukryta, i nadal będzie działać.

W formatowaniu warunkowym możesz po prostu użyć:, = SUBTOTAL(103,$A2)=1aby ustalić, czy wiersz jest widoczny.

Jako dodatek do odpowiedzi Phrogza, jeśli chcesz sprawdzić, czy komórka w kolumnie jest ukryta, spróbuj wykonać jedną z następujących czynności,

Formatowanie warunkowe

=CELL("width",TargetCell)=0

To aktualizuje się automatycznie, gdy tylko kolumna zostanie ukryta.

Formula Check

=IF(CELL("width",TargetCell)=0, "Cell is hidden.", "Cell is visible.")

Ta formuła nie aktualizuje się automatycznie i należy skierować program Excel do „Oblicz teraz”, wybierając opcję menu lub naciskając „F9”.

Jest to podobne do podejścia ucznia Gary'ego. Zdefiniuj następującą funkcję VBA:

Function MyRowHidden(ref As Range)
    MyRowHidden = Rows(ref.Row).Hidden
End Function

Zobacz Jak dodać VBA w MS Office? jeśli potrzebujesz pomocy. Teraz możesz użyć, aby sprawdzić, czy wiersz zawierający jest ukryty.MyRowHidden(cell)cell

Sposób, w jaki postanowiłem rozwiązać problem, wykorzystuje kolumnę pomocniczą, ale możesz ją ukryć. Zakładając, że dane zaczynają się w wierszu 2, z kategoriami w kolumnie A, wprowadź

=OR($A1<>$A2, AND(H1,MyRowHidden(H1)))

do komórki H2i przeciągnij w dół. Ta formuła ma wartość PRAWDA, jeśli

• kategoria w tym wierszu ( A2) różni się od kategorii w poprzednim wierszu ( A1); tzn. jest to pierwszy wiersz nowej kategorii, lub
• poprzedni wiersz powinien być podświetlony, ale jest ukryty.

Następnie po prostu użyj Formatowania warunkowego, aby podświetlić komórkę, A2jeśli =H2jest to prawda.

Przykład: surowe dane:

Tak, jestem tradycjonalistą; Nadal uważam Plutona za planetę. Tutaj znowu jest ukryte pierwsze rzędy (2, 3, 5, 7, 11 i 13):

Oczywiście będziesz musiał włączyć makra w swoim skoroszycie.

Aby wykryć, czy wiersz nad aktywną komórką jest Ukryty , uruchom to makro:

Sub WhatsAboveMe()
Dim r As Range
Set r = Selection
With r
    If .Row = 1 Then
        Exit Sub
    End If
    If .Offset(-1, 0).EntireRow.Hidden = True Then
        MsgBox "the row above is hidden"
    Else
        MsgBox "the row above is visible"
    End If
End With
End Sub

Ten wątek jest trochę stary, ale na wypadek, gdyby był pomocny dla każdego, oto sposób warunkowego formatowania duplikatów na filtrowanej tabeli bez konieczności używania VBA.

1. Zrób kolumnę wypełnioną 1

2. Utwórz kolejną kolumnę i umieść w niej taką formułę

   =IF(SUBTOTAL(103, [@ColumnWithOnlyOnesInIt])=1, [@ColumnYouWantToCheckForDuplicates], "")

3. Wprowadź normalne zduplikowane formatowanie warunkowe w kolumnie, którą chcesz sprawdzić.

Formuła z kroku 2 skopiuje wartość z kolumny, którą chcesz sprawdzić, ale tylko wtedy, gdy wiersz będzie widoczny. W ten sposób, gdy sprawdzane są duplikaty, otrzymujesz tylko te, które dotyczą tabeli filtrowanej. Myślę, że to może nie działać dla zer (lub „” lub cokolwiek, co wybierzesz jako wartość „else” w instrukcji if). Może więc być możliwe uzyskanie na liście wartości zerowej linii, która jest podświetlona jako duplikat. Poza tym mam szczęście z tą metodą.

Proponuję zastosować następującą formułę (w zakresie np. $ A: $ A):

=AND(A1=OFFSET(A1;-1;0);SUBTOTAL(103;OFFSET(A1;-1;0))=1)

Co to robi:

Jeśli oba

1. komórka jest równa powyższej: A1=OFFSET(A1;-1;0)
2. komórka powyżej jest widoczna: SUBTOTAL(103;OFFSET(A1;-1;0))=1

wtedy wynik jest prawdziwy, więc komórka jest duplikatem widocznej komórki powyżej i powinna być np. wyszarzona.

Uwaga: użycie OFFSETfunkcji powoduje, że formatowanie warunkowe nie ulegnie zerwaniu po wstawieniu dodatkowego wiersza.

Oto rozwiązanie, którego właśnie użyłem:

Utworzyłem nową kolumnę C (i ukryłem oryginał (kolumna B)). W nowej kolumnie użyłem formuły = SUBTOTAL (9, B2) Który SUMS JEDEN wiersz Cię interesuje. Następnie skopiowałem go wszystkie wiersze!

Teraz, kiedy filtrujesz za pomocą filtra zaawansowanego. Wszystkie wartości są ZEROWANE w tej kolumnie, chyba że są widoczne (nie są filtrowane).

Następnie normal = SUMIF () działa jak bohater. Po prostu nie używaj przypadkowo ukrytej kolumny do sumowania. Suma w nowo utworzonej kolumnie SUBTOTAL ().