Функция Excel определяет, видна ли ячейка

Question

В условном форматировании я хочу определить, является ли строка над текущей ячейкой скрытой или видимой. Как я могу определить, видна ли клетка или нет?

Единственный взлом, который я могу придумать:

1. Создайте столбец со всеми 1 значениями.
2. Используйте формулу наподобие subtotal(109,c2:c2)=1 (т. Е. Просто в ячейке, которую я хочу проверить), чтобы определить, является ли она видимой или скрытой.

Есть ли способ сделать это без временного столбца, который должен оставаться видимым, когда отображается строка?

---

Чтобы избежать проблемы XY, я хочу иметь столбец, который является категорией для строки. Первая строка VISIBLE с определенной категорией должна иметь другой стиль; более поздние строки с той же категорией немного отличаются. В ASCII:

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

Если мой фильтр скрывает строку с помощью dddd тогда я хочу, чтобы строка с eeee вместо этого имела стиль +BBB+ .

Answer 1

Вместо промежуточного counta с использованием суммы в другом столбце вы можете использовать промежуточный итог с использованием счетчика, чтобы увидеть, скрыта ли (известная непустая ) ячейка или нет. Например, если столбец A обычно будет видимым (если строка не скрыта)

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

Вы можете поместить эту формулу в столбец, который может быть скрыт, и он все еще будет работать.

В условном форматировании вы можете просто использовать: = SUBTOTAL(103,$A2)=1 чтобы определить, видима ли строка.

Answer 2

В качестве дополнения к ответу Phrogz, если вам нужно проверить, не скрыта ли ячейка в столбце , попробуйте выполнить одно из следующих действий:

Условное форматирование

=CELL("width",TargetCell)=0

Это обновляется автоматически, как только столбец скрыт.

Проверка формулы

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

Эта формула не будет обновляться автоматически, и вам нужно будет указать в Excel "Рассчитать сейчас", выбрав пункт меню или нажав "F9".

Answer 3

Это похоже на подход Гэри Студента.  Определите следующую функцию VBA:

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

См. Как добавить VBA в MS Office? если вам нужна помощь с этим.  Теперь вы можете использовать MyRowHidden(cell) чтобы проверить, является ли строка, содержащая cell , скрытой.

Способ, который я придумал для решения проблемы, использует вспомогательный столбец, но вы можете скрыть это.  Предполагая, что ваши данные начинаются со строки 2 , с категориями в столбце A введите

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

в ячейку H2 и перетащите вниз.  Эта формула оценивается как ИСТИНА, если

• категория в этом ряду (A2) отличается от категории в предыдущем ряду (A1); это первая строка новой категории, или
• предыдущая строка должна быть выделена, но скрыта.

Затем просто используйте условное форматирование, чтобы выделить ячейку A2 если =H2 - true.

Пример: необработанные данные:

       

Да, я традиционалист; Я все еще считаю Плутон планетой.  Здесь снова скрыты строки с простыми номерами (2, 3, 5, 7, 11 и 13):

       

Конечно, вам нужно включить макросы в вашей книге.

Answer 4

Этот поток немного староват, но на случай, если он кому-нибудь пригодится, вот способ условно форматировать дубликаты на отфильтрованной таблице без использования VBA.

1. Сделать столбец заполненным 1

2. Сделайте еще один столбец и вставьте в него следующую формулу

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

3. Поместите обычное дублирующее условное форматирование в столбец, который вы хотите проверить.

Формула из шага 2 скопирует значение из столбца, который вы хотите проверить, но только тогда, когда строка видна. Таким образом, когда проверяются дубликаты, вы получаете только те, которые применимы к отфильтрованной таблице. Я думаю, что это может не сработать для нулей (или "" или что-то еще, что вы выбрали в качестве значения "else" в вашем выражении if) Поэтому может быть возможным получить нулевое значение строки в вашем списке, которое будет выделено как дубликат. Кроме этого мне повезло с этим методом.

Answer 5

Я бы предложил использовать следующую формулу (в диапазоне, например, $ A:$ A):

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

Что это делает:

Если оба

1. ячейка равна указанной выше: A1=OFFSET(A1;-1;0)
2. видимая выше ячейка: SUBTOTAL(103;OFFSET(A1;-1;0))=1

тогда результат равен True, поэтому ячейка является дубликатом видимой ячейки справа вверху и должна быть, например, серой.

Sidenote: использование функции OFFSET позволяет условному форматированию не прерываться при вставке дополнительной строки.

Answer 6

Чтобы определить, является ли строка над активной ячейкой скрытой, запустите этот макрос:

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

Answer 7

Вот решение, которое я только что использовал:

Я создал новый столбец C (и HID оригинал (столбец B)). В новом столбце я использовал формулу = SUBTOTAL (9, B2), которая равна одной строке, которая вас интересует. Затем я скопировал все строки!

Теперь при фильтрации используется расширенный фильтр. Все значения равны нулю в этом столбце, если они не видны (не отфильтрованы).

Тогда normal = SUMIF() работает как чемпион. Только не используйте случайно скрытый столбец для суммирования. Сумма по столбцу SUBTOTAL(), который вы только что создали.