Formula generica
=A1+(COUNTIF(exp_rng,A1)-1)*adjustment
Sommario
Per rompere i legami, puoi utilizzare una colonna helper e la funzione CONTA.SE per regolare i valori in modo che non contengano duplicati e quindi non si traducano in pareggi. Nell'esempio mostrato, la formula in D5 è:
=C5+(COUNTIF($C$5:C5,C5)-1)*0.01
Contesto
A volte, quando utilizzi funzioni come SMALL, LARGE o RANK per classificare i valori più alti o più bassi, ti ritroverai con dei legami, perché i dati contengono duplicati. Un modo per rompere i legami in questo modo è aggiungere una colonna helper con i valori che sono stati regolati, quindi classificare quei valori invece degli originali.
In questo esempio, la logica utilizzata per regolare i valori è casuale: il primo valore duplicato "vincerà", ma puoi modificare la formula per utilizzare la logica che si adatta alla tua situazione e al caso d'uso.
Spiegazione
Fondamentalmente, questa formula utilizza la funzione CONTA.SE e un intervallo in espansione per contare le occorrenze di valori. Il riferimento in espansione viene utilizzato in modo che CONTA.SE restituisca un conteggio progressivo di occorrenze, invece di un conteggio totale per ogni valore:
COUNTIF($C$5:C5,C5)
Successivamente, 1 viene sottratto dal risultato (il che rende zero il conteggio di tutti i valori non duplicati) e il risultato viene moltiplicato per 0,01. Questo valore è l '"aggiustamento" e intenzionalmente piccolo in modo da non influire materialmente sul valore originale.
Nell'esempio mostrato, Metrolux e Diamond hanno entrambi la stessa stima di $ 5000. Poiché Metrolux compare per primo nell'elenco, il conteggio parziale di 5000 è 1 e viene annullato sottraendo 1, quindi la stima rimane invariata nella colonna helper:
=C8+(COUNTIF($C$5:C8,C8)-1)*0.01 =C8+(1-1)*0.01 =C8+0 =C8
Tuttavia, per Diamond, il conteggio parziale di 5000 è 2, quindi la stima viene modificata:
=C11+(COUNTIF($C$5:C11,C11)-1)*0.01 =C11+(2-1)*0.01 =C11+1*0.01 =C11+0.01
Infine, i valori corretti vengono utilizzati per la classifica al posto dei valori originali nelle colonne G e H. La formula in G5 è:
=SMALL($D$5:$D$12,F5)
La formula in H5:
=INDEX($B$5:$B$12,MATCH(G5,$D$5:$D$12,0))
Vedi questa pagina per una spiegazione di queste formule.
Colonna aiutante temporaneo
Se non si desidera utilizzare una colonna helper nella soluzione finale, è possibile utilizzare temporaneamente una colonna helper per ottenere i valori calcolati, quindi utilizzare Incolla speciale per convertire i valori "sul posto" ed eliminare la colonna helper in seguito. Questo video mostra la tecnica.
