Conosci l'ingegnoso trucco di creare un riferimento a un'intestazione di colonna nell'ultima cella non vuota di una riga? Il migliore: non hai bisogno di linee o colonne ausiliarie. È così facile:
Combina le quattro funzioni SE ERRORE (), INDICE (), MAX () e SE ()
L'amministratore delegato alle vendite ti invierà un elenco dei contratti conclusi mensilmente per i prodotti in fase di eliminazione ①. È necessario utilizzare una formula nella colonna N per specificare l'ultimo mese di vendita per ciascun prodotto, senza righe o colonne ausiliarie. Se non sono stati conclusi più contratti, inserire una cella vuota nella colonna N.
Questo esempio, a prima vista banale, si rivela un osso duro senza l'uso di linee o colonne ausiliarie. come Excel per la pratica-I lettori rompono il dado! Abbiamo la seguente formula di matrice nella cella per la risoluzione dei problemi N2 creato :
{= SE ERRORE (INDICE ($ B $ 1: $ M $ 1; MAX (SE (B2: M2 ""; COLONNA (B2: M2) -1; -1))); "")}
Dai un'occhiata a come funziona passo dopo passo
Inizia con la condizione IF() incorporata nella formula dell'array: {= SE ERRORE (INDICE ($ B $ 1: $ M $ 1; MASSIMO (SE (LA2: M2 "", COLONNA (LA2: M2) -1, -1)));"")}
La condizione IF() crea una linea ausiliaria fittizia nella formula dell'array e controlla le celle B2 fino a M2se questi sono vuoti o meno. Se una cella è vuota, il valore -1 viene altrimenti restituito tramite la funzione COLUMN(), il rispettivo numero di colonna meno il valore 1.
La sottrazione di 1 è richiesta nella formula perché la prima colonna della tabella non contiene il nome del mese, ma il nome del prodotto. Di seguito imparerai come utilizzare la funzione INDEX() per visualizzare il nome del mese corrispondente, che - se non hai sottratto l'1 - a causa della colonna aggiuntiva utilizzata UN. non sarebbe corretto di esattamente una colonna.
Se tutte le celle nell'intervallo B2: M2 sono vuoti, il valore -1 (nessuna vendita di prodotti) crea un errore che utilizziamo per rappresentare una cella vuota. La linea ausiliaria attiva è visibile in Fig. ③ nella linea 3.
Nel passaggio successivo si legge il valore più grande con la funzione MAX(), in cui è annidata la condizione IF(). Questo è il valore 12 nella riga 3 (colonna 13 meno 1; vedi riga ausiliaria in Figura ③):
{= SE ERRORE (INDICE ($ B $ 1: $ M $ 1;MAX (SE (B2: M2 "", COLONNA (B2: M2) -1, -1)));"")}
Passi questo valore MAX alla funzione INDEX(). Il mese di calendario corrispondente viene quindi letto nella riga 1. L'area dei dati della funzione INDEX () è l'area $ B $ 1: $ M $ 1. Il valore MAX passato - nell'esempio 12 - significa che il dodicesimo valore nell'elenco, ad es. dicembre per il mese di dicembre:
{= SE ERRORE(INDICE ($ B $ 1: $ M $ 1; MAX (SE (B2: M2 ""; COLONNA (B2: M2) -1; -1)));"")}
Se tutte le celle nell'intervallo dei mesi della tabella sono vuote, il valore più grande è -1 (vedere la condizione IF () all'inizio). Se il valore -1 viene passato alla funzione INDEX (), questo porta inevitabilmente a un valore di errore, poiché l'area dell'elenco della funzione INDEX () contiene solo dodici voci e quindi non riesce a trovare la voce -1. Si cattura questo valore di errore con la funzione IFERROR() e si restituisce invece una stringa vuota. Nell'esempio, questo è il caso per il prodotto C nella riga 4, perché non è stato possibile concludere più contratto per questo prodotto:
{=SE ERRORE(INDICE ($ B $ 1: $ M $ 1; MAX (SE (B2: M2 "", COLONNA (B2: M2) -1, -1)));"")}
Poiché si tratta di una formula di matrice, completa l'immissione della formula con la combinazione di tasti Ctrl + Maiusc + Invio.
