Sommario
Per configurare COUNTIFS (o COUNTIF) con un intervallo variabile, è possibile utilizzare la funzione OFFSET. Nell'esempio mostrato, la formula in B11 è:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Questa formula conta le celle non vuote in un intervallo che inizia da B5 e termina 2 righe sopra la cella in cui si trova la formula. La stessa formula viene copiata e incollata 2 righe sotto l'ultima voce nei dati come mostrato.
Spiegazione
Nell'esempio mostrato, la formula in B11 è:
=COUNTIFS(OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1),"")
Lavorando dall'interno verso l'esterno, il lavoro di impostazione di un intervallo variabile viene svolto dalla funzione OFFSET qui:
OFFSET(B$5,0,0,ROW()-ROW(B$5)-1,1) // variable range
OFFSET ha cinque argomenti ed è configurato in questo modo:
- riferimento = B $ 5, inizia dalla cella B5, riga bloccata
- righe = 0, offset zero righe dalla cella iniziale
- cols = 0, offset zero colonne che iniziano la cella
- altezza = RIGA () - RIGA (B $ 5) -1 = 5 righe di altezza
- larghezza = 1 colonna di larghezza
Per calcolare l'altezza dell'intervallo in righe, usiamo la funzione ROW in questo modo:
ROW()-ROW(B$5)-1 // work out height
Poiché ROW () restituisce il numero di riga della cella "corrente" (ovvero la cella in cui risiede la formula), possiamo semplificare in questo modo:
=ROW()-ROW(B$5)-1 =11-5-1 =5
Con la configurazione precedente, OFFSET restituisce l'intervallo B5: B9 direttamente a COUNTIFS:
=COUNTIFS(B5:B9,"") // returns 4
Si noti che il riferimento a B $ 5 nella formula sopra è un riferimento misto, con la colonna relativa e la riga bloccata. Ciò consente di copiare la formula in un'altra colonna e continuare a funzionare. Ad esempio, una volta copiata in C12, la formula è:
=COUNTIFS(OFFSET(C$5,0,0,ROW()-ROW(C$5)-1,1),"")
Nota: OFFSET è una funzione volatile e può causare problemi di prestazioni in fogli di lavoro grandi o complessi.
Con INDIRETTO e INDIRIZZO
Un altro approccio consiste nell'utilizzare una formula basata sulle funzioni INDIRETTO e INDIRIZZO. In questo caso, assembliamo un intervallo come testo, quindi utilizziamo INDIRETTO per valutare il testo come riferimento. La formula in B11 sarebbe:
=COUNTIFS(INDIRECT(ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())),"")
La funzione INDIRIZZO viene utilizzata per costruire un intervallo come questo:
ADDRESS(5,COLUMN())&":"&ADDRESS(ROW()-2,COLUMN())
Nella prima istanza di ADDRESS, forniamo il numero di riga come valore hardcoded 5 e forniamo il numero di colonna con la funzione COLUMN:
=ADDRESS(5,COLUMN()) // returns "$B$5"
Nella seconda istanza, forniamo il numero di riga "corrente" meno 2 e la colonna corrente con la funzione COLUMN:
=ADDRESS(ROW()-2,COLUMN()) // returns "$B$9"
Dopo aver concatenato questi due valori insieme, abbiamo:
"$B$5:$B$9" // as text
Nota che questa è una stringa di testo. Per convertire in un riferimento valido, dobbiamo usare INDIRETTO:
=INDIRECT("$B$5:$B$9") // returns $B$5:$B$9 as valid range
Infine, la formula in B11 diventa:
=COUNTIFS($B$5:$B$9,"") // returns 4
Nota: INDIRETTO è una funzione volatile e può causare problemi di prestazioni in fogli di lavoro grandi o complessi.
