La function Excel determina se una cella è visibile

Nella formattazione condizionale vorrei rilevare se la row sopra la cella corrente è nascosta o visibile. Come posso rilevare se una cella è visibile o no?

L'unico problema che posso pensare è:

  1. Crea una colonna con tutti i valori 1 .
  2. Utilizza una formula come il subtotal(109,c2:c2)=1 (cioè solo sulla cella che voglio controllare) per determinare se è visibile o nascosto.

C'è un modo per farlo senza una colonna temporanea che deve rimanere visibile quando viene visualizzata la row?


Per evitare un problema di XY , quello che voglio fare è avere una colonna che è la categoria della row. La prima row VISIBILE con una particolare categoria dovrebbe avere uno stile diverso; le file successive con la stessa categoria sono leggermente diverse. In ASCII:

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

Se il mio filter nasconde la row con dddd voglio che la row con eeee abbia il +BBB+ styling invece.

Invece di un subtotale che utilizza una sum su un'altra colonna, è ansible utilizzare il subtotale utilizzando il counta per vedere se una cella (nota-non-vuota) è nascosta o less. Ad esempio, se la colonna A sarà normalmente visibile (a less che la row non sia nascosta)

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

Puoi mettere questa formula in una colonna che può essere nascosta e functionrà ancora.

Quindi, nella formattazione condizionale è ansible utilizzare: = SUBTOTAL(103,$A2)=1 per determinare se la row è visibile.

Come aggiunta alla risposta di Phrogz, se è necessario verificare se una cella in una colonna è nascosta, prova una delle seguenti operazioni,

Formattazione condizionale

 =CELL("width",TargetCell)=0 

Questo viene automaticamente aggiornato non appena viene nascosta una colonna.

Formula di controllo

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

Questa formula non verrà aggiornata automaticamente e dovresti dirigere Excel a "Calcola ora" scegliendo l'opzione di menu o premendo "F9".

Questo è simile all'approccio di Gary's Student's. Definire la seguente function VBA:

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

Vedere Come aggiungere VBA in MS Office? se hai bisogno di aiuto. Ora è ansible utilizzare MyRowHidden( cell ) per verificare se la row contenente la cell è nascosta.

Il modo in cui ho ideato per risolvere il problema utilizza una colonna di helper, ma puoi nasconderla. Supponendo che i tuoi dati inizino in Riga 2 , con le categorie nella colonna A , digitare

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

nella cella H2 , e trascina verso il basso. Questa formula valuta a TRUE if

  • la categoria in questa row ( A2 ) è diversa dalla categoria nella precedente row ( A1 ); vale a dire, questa è la prima fila di una nuova categoria, o
  • la row precedente dovrebbe essere evidenziata, ma è nascosta.

Quindi utilizzare semplicemente la formattazione condizionale per evidenziare la cella A2 se =H2 è true.

Esempio: dati grezzi:

insieme completo di dati

Sì, sono un tradizionalist; Pluto è ancora un pianeta. Qui è nuovamente con le prime righe numerate (2, 3, 5, 7, 11 e 13) nascoste:

dati filtrati

Naturalmente dovrai abilitare le macro nella tua cartella di lavoro.

Per rilevare se la row sopra la cella triggers è nascosta , eseguire questa macro:

 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 

Ecco la soluzione che ho appena usato:

Ho creato una nuova colonna C (e HID l'originale (colonna B)). Nella nuova colonna ho usato la formula = SUBTOTAL (9, B2) che SUMS la row ONE a cui sei interessata. Ho poi copiato tutte le righe!

Ora, quando filtri utilizzando un filter avanzato. I valori sono tutti ZERO in questa colonna a less che non siano visibili (non filtrati).

Quindi, il normale = SUMIF () funziona come un campione. Non usare accidentalmente la colonna nascosta per riassumere. Somma nella colonna SUBTOTAL () appena creata.