Copia le formule senza spostamento del collegamento

Problema

Supponiamo di avere una tabella semplice come questa, in cui gli importi vengono calcolati per ogni mese in due città, e quindi il totale viene convertito in euro al tasso della cella gialla J2.

Il problema è che se copi l'intervallo D2:D8 con le formule da qualche altra parte nel foglio, Microsoft Excel correggerà automaticamente i collegamenti in queste formule, spostandoli in una nuova posizione e interrompendo il conteggio:

Compito: copiare l'intervallo con le formule in modo che le formule non cambino e rimangano le stesse, mantenendo i risultati del calcolo.

Metodo 1. Collegamenti assoluti

Metodo 2: disabilitare temporaneamente le formule

Per evitare che le formule vengano modificate durante la copia, è necessario (temporaneamente) assicurarsi che Excel smetta di trattarle come formule. Questo può essere fatto sostituendo il segno di uguale (=) con qualsiasi altro carattere non normalmente presente nelle formule, come un cancelletto (#) o una coppia di e commerciali (&&) per il tempo di copia. Per questo:

1. Seleziona l'intervallo con le formule (nel nostro esempio D2:D8)
2. Clicchi Ctrl + H sulla tastiera o su una scheda Home – Trova e seleziona – Sostituisci (Home — Trova&Seleziona — Sostituisci)

   

3. Nella finestra di dialogo che appare, inserisci ciò che stiamo cercando e con cosa sostituiamo, e in parametri (Opzioni) non dimenticare di chiarire Ambito di ricerca – Formule. Premiamo Sostituisci tutto (Sostituisci tutto).
4. Copia l'intervallo risultante con le formule disattivate nel posto giusto:

   

5. sostituire # on = indietro utilizzando la stessa finestra, restituendo funzionalità alle formule.

Metodo 3: copia tramite Blocco note

Questo metodo è molto più veloce e più facile.

Premi la scorciatoia da tastiera Ctrl+A o pulsante Mostra formule linguetta formula (Formule — Mostra formule), per attivare la modalità di verifica della formula – invece dei risultati, le celle visualizzeranno le formule con cui vengono calcolate:

Copia il nostro intervallo D2:D8 e incollalo nello standard Taccuino:

Ora seleziona tutto ciò che è incollato (Ctrl + A), copialo di nuovo negli appunti (Ctrl + C) e incollalo sul foglio nel punto che ti serve:

Resta solo da premere il pulsante Mostra formule (Mostra formule)per riportare Excel alla modalità normale.

Nota: questo metodo a volte non riesce su tabelle complesse con celle unite, ma nella stragrande maggioranza dei casi funziona correttamente.

Metodo 4. Macro

Se devi spesso eseguire tale copia di formule senza spostare i riferimenti, ha senso utilizzare una macro per questo. Premi la scorciatoia da tastiera Alt + F11 o pulsante Visual Basic linguetta sviluppatore (Sviluppatore), inserire un nuovo modulo tramite il menu Inserisci – Modulo  e copia lì il testo di questa macro:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Seleziona le celle con le formule da copiare.", _ "Copia esattamente le formule", Default:=Selection.Address, Type := 8) If copyRange Is Nothing Then Exit Sub Set pasteRange = Application.InputBox("Ora seleziona l'intervallo di incolla." & vbCrLf & vbCrLf & _ "L'intervallo deve avere dimensioni uguali all'intervallo di celle originale " & vbCrLf & _ " da copiare." , "Copia esattamente le formule", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Gli intervalli di copia e incolla variano in dimensione!", vbExclamation, "Errore di copia " Exit Sub End If If pasteRange non è niente Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

È possibile utilizzare il pulsante per eseguire la macro. Macro linguetta sviluppatore (Sviluppatore — Macro) o scorciatoia da tastiera Alt + F8. Dopo aver eseguito la macro, ti chiederà di selezionare l'intervallo con le formule originali e l'intervallo di inserimento e copierà automaticamente le formule:

• Comoda visualizzazione di formule e risultati allo stesso tempo
• Perché è necessario lo stile di riferimento R1C1 nelle formule di Excel
• Come trovare rapidamente tutte le celle con le formule
• Strumento per copiare formule esatte dal componente aggiuntivo PLEX