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, esamineremo 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
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. Vedi le schermate:
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, puoi procedere passo dopo passo come segue:
1. Seleziona l'intervallo contenente le formule che desideri sostituire con i loro nomi nei riferimenti di cella.
2. Premi Alt + F11 per aprire una nuova finestra di Microsoft Visual Basic per applicazioni, quindi fai clic su Inserisci > Modulo, e copia e incolla il seguente codice nel modulo:
Codice VBA: Sostituisci 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 SubCodice VBA: Sostituisci 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 Sub3. Quindi premi F5 per eseguire il codice, appare una finestra di dialogo per selezionare un intervallo da sostituire. Vedi screenshot:
4. Quindi fai clic su OK. Tutti i nomi degli intervalli delle formule selezionate saranno stati sostituiti.
Sostituisci rapidamente e facilmente i nomi degli intervalli con i riferimenti di cella con 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 Nome in Riferimento Intervallo ti aiuterà a sostituire i nomi degli intervalli con i riferimenti di cella rapidamente e facilmente.
1. Applica questo strumento facendo clic su Kutools > Strumenti Nome > Converti Nome in Riferimento Intervallo.
2. Viene visualizzata una finestra di dialogo, fai clic sulla scheda Intervallo e quindi seleziona l'intervallo che desideri sostituire con i riferimenti di cella dall'Intervallo Base. Successivamente, fai clic sul pulsante Sostituisci. Vedi screenshot:
3. Un'altra finestra di avviso verrà visualizzata per informarti il numero di formule modificate e formule non modificate, fai clic su OK, e quindi i nomi degli intervalli nell'intervallo selezionato verranno sostituiti con i riferimenti di cella.
🔚 Conclusione
Che tu stia affrontando 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ù rapido e facile da usare per completare il compito in pochi clic—nessuna codifica o modifica della formula 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 scoprire più suggerimenti e trucchi su 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
I migliori strumenti per la produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un'efficienza mai vista prima. Kutools per Excel offre oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in