Come sostituire i nomi degli intervalli con i riferimenti di cella in Excel?

Quando si lavora con gli intervalli denominati in Excel, potreste trovarli utili per la leggibilità e la gestione delle formule. Tuttavia, ci sono situazioni in cui potreste voler rimuovere i nomi degli intervalli e ripristinare i riferimenti di cella effettivi, specialmente quando si condividono file, si risolvono problemi o si standardizzano modelli. In questo articolo, esploreremo diversi modi per sostituire gli intervalli denominati con i corrispondenti riferimenti di cella in Excel.

Sostituire i nomi degli intervalli con i riferimenti di cella tramite codice VBA

Sostituire rapidamente e facilmente i nomi degli intervalli con i riferimenti di cella tramite Kutools per Excel

Ad esempio, in questo foglio di lavoro, nominerò l'intervallo A2:A6 come saleprice e B2 come discount, e poi utilizzerò entrambi i nomi degli intervalli in una formula per calcolare il nuovo prezzo. Vedere gli screenshot:

Dopo aver seguito questi trucchi rapidi, il nome dell'intervallo verrà sostituito con il riferimento di cella come mostrato di seguito:

Sostituire i nomi degli intervalli con i riferimenti di cella tramite codice VBA

Utilizzando VBA per sostituire i nomi degli intervalli nelle formule con i riferimenti di cella, è possibile procedere passo dopo passo come segue:

1. Selezionare l'intervallo contenente le formule che si desidera sostituire con i loro nomi tramite riferimenti di cella.

2. Premere Alt + F11 per aprire una nuova finestra di Microsoft Visual Basic per applicazioni, quindi fare clic su Inserisci > Modulo, e copiare e incollare il seguente codice nel modulo:

Codice VBA: Sostituire i nomi degli intervalli con riferimenti assoluti

Sub AbsoleteNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(VBA.Replace(xName.RefersTo, "=", ""), "$", ""))
        End If
    Next
Next
End Sub

Codice VBA: Sostituire i nomi degli intervalli con riferimenti relativi

Sub ReplaceNamesWithRelativeRefs()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim xName As Name
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas)
For Each Rng In WorkRng
    For Each xName In ThisWorkbook.Names
        If InStr(Rng.Formula, xName.Name) > 0 Then
            Rng.Formula = VBA.Replace(Rng.Formula, xName.Name, VBA.Replace(xName.RefersTo, "=", ""))
        End If
    Next
Next
End Sub

3. Quindi premere F5 per eseguire il codice, verrà visualizzata una finestra di dialogo per selezionare un intervallo da sostituire. Vedere lo screenshot:

4. Quindi fare clic su OK. Tutti i nomi degli intervalli delle formule selezionate saranno stati sostituiti.

Sostituire rapidamente e facilmente i nomi degli intervalli con i riferimenti di cella tramite Kutools per Excel

Mentre farlo manualmente o tramite VBA può essere dispendioso in termini di tempo e soggetto a errori, Kutools per Excel offre una soluzione con un solo clic per semplificare l'intero processo. Se hai Kutools per Excel, il suo strumento Converti nomi in riferimenti di intervallo ti aiuterà a sostituire i nomi degli intervalli con i riferimenti di cella rapidamente e facilmente.

1. Si prega di applicare questa utilità facendo clic su Kutools > Strumenti nome > Converti nomi in riferimenti di intervallo.

2. Verrà visualizzata una finestra di dialogo, fare clic sulla scheda Intervallo e quindi selezionare l'intervallo che si desidera sostituire con i riferimenti di cella dall'Intervallo base. Quindi, fare clic sul pulsante Sostituisci. Vedere lo screenshot:

3. Verrà visualizzata un'altra finestra di dialogo per informarvi sul numero di formule modificate e non modificate, fare clic su OK, e quindi i nomi degli intervalli nell'intervallo selezionato saranno stati sostituiti con i riferimenti di cella.

🔚 Conclusione

Che tu stia gestendo poche formule o un'intera cartella di lavoro, sostituire gli intervalli denominati con i riferimenti di cella effettivi è un ottimo modo per migliorare trasparenza, compatibilità e facilità di collaborazione.

• Per gli utenti avanzati, VBA offre una soluzione flessibile e programmabile per automatizzare il processo di sostituzione.
• Per tutti gli altri, Kutools per Excel offre il modo più veloce e facile da usare per completare il compito in pochi clic - nessuna codifica o modifica delle formule richiesta.

Scegliendo il metodo che meglio si adatta al tuo flusso di lavoro, puoi pulire efficacemente le tue formule e assicurarti che i tuoi fogli di calcolo rimangano facili da comprendere e mantenere. Se sei interessato a esplorare ulteriori suggerimenti e trucchi di Excel, il nostro sito web offre migliaia di tutorial per aiutarti a padroneggiare Excel.

Articolo correlato:

Cambia il riferimento di cella nelle formule in nomi di intervallo in Excel