Excel: 5 Suggerimenti per evitare problemi di file collegati

Un cliente ha recentemente chiesto la seguente domanda: “In un contesto in cui diversi reparti stanno lavorando a un budget, abbiamo separato il nostro Modello in diversi file diversi … Con le difficoltà che implica, le formule e i collegamenti più complicati tra i file da mantenere. Gli altri funzionano diversamente? “. Certamente, i problemi dei relativi file Excel si verificano in particolare nelle aziende che hanno un processo di budget decentralizzato.

La cambusa dei file Excel collegati

Sebbene Excel possa sempre fornire una risposta a tutti i Problemi (per sua natura di estrema flessibilità), il concetto di gestione dei file collegati costituisce senza dubbio il suo tallone di Achille. È questa debolezza che incoraggia molti a passare a “sistemi” di bilancio / previsione. Quest’ultimo offre interfacce collaborative ed eliminano i problemi dei file indipendenti. Ma questi sistemi, spesso costosi, sono davvero la soluzione definitiva a qualsiasi problema di file correlato? Questo L’articolo mira ad offrire alternative di budget semplici e molto basse, ma spesso sconosciute agli utenti di Excel.

gli utenti Excel creano file collegati?

In teoria, non si dovrebbe mai dividere un modello Excel in A meno che:

  • sei più di un utente da utilizzare il modello allo stesso tempo e l’opzione di lavoro collaborativa inclusa in Excel non è più sufficiente
  • diverse parti del modello devono essere inviate a diversi destinatari
  • il modello è troppo grande
Questo articolo è stato originariamente pubblicato l’11 luglio 2013 e messo Dal 16 novembre 2020.

Quali sono i creatori dei file collegati?

Gestione dei file in Excel espone gli utenti a più potenziali problemi:

  • a Visualizza i dati aggiornati, in un file che feed su un altro file, entrambi i file devono essere aperti
  • quando un utente cambia il file sorgente, specialmente quando elimina o modifica gli intervalli di dati, senza aprire il Modello (il file Excel che si nutre di esso), il modello del modello si incontrerà spesso con le formule che non sono più giuste (riferimento a Bad Data Spiagge o che ritornano dal messaggio di errore #Ref a Starlyly! (Riferimento a intervalli di dati che sono stati eliminati)
  • Se il nome delle foglie e dei file è il minimo lungo, ciò aumenterà in modo significativo le formule e quindi il peso del modello, aumentando così la possibilità di incontrare Problemi di prestazione
Avete fonti di dati voluminose per raccogliere e analizzare in Excel? Scopri come automatizzare l’intera procedura di importazione, trasformazione e modellazione dei dati con la nostra formazione sulla query di alimentazione e il pivot di potenza in Excel.

funziona correttamente con file Excel esterni

quando il Sono stati suddivisi diversi file per consentire ai diversi utenti di aggiornare sezioni diverse e che l’opzione di lavoro collaborativa inclusa in Excel non è stata mantenuta, ecco le alternative da mettere in atto. Tra questi, gli ultimi due sono sicuramente i più interessanti, specialmente l’ultimo.

Utilizzare formule 3D per eliminare i file correlati

Ogni gestore di bilancio potrebbe soddisfare il proprio budget in un tipo di eccellente di tipo indipendente file, che sarebbe identico per ogni gestore. Potremmo quindi semplicemente copiare il contenuto di queste pagine, tra i terminali di avvio e fine, un file di consolidamento, comprendente formule 3D, che potrebbero quindi essere consentite come e quando aggiungono queste pagine. Naturalmente, questa non sarebbe un’opzione “dinamica”. Pertanto dovrebbe essere compensato creando una pagina di documentazione, mantenendo in memoria le date di ciascuna ultima versione di queste pagine così copiate. Per questo processo è efficace, un certo controllo sarebbe Essere così necessari. Se puoi eliminare questo lavoro “manuale”, sarà sempre preferito. Sarebbe quindi necessario scegliere un’altra opzione presentata di seguito.

Utilizzare i campi denominati nei file di origine

Utilizzare i campi “Nome” per i campi del file sorgente che vengono utilizzati . Nel modello. Questa opzione, anche se semplice da configurare, provoca vantaggi e svantaggi.Così ben utilizzato, preserverà le formule correlate, ma se gli utenti iniziano a copiare le informazioni da un foglio Excel con campi denominati in altri file Excel, potrebbero quindi iniziare a creare collegamenti tra i file. Quale non dovrebbe essere collegato. È quindi necessario essere un vigile eccessivo con questa opzione. Se è possibile dare la priorità ad un’altra delle seguenti opzioni, sarà sempre meglio farlo.

Usa fogli “specchio” nei tuoi file correlati

Crea un foglio “Importazione dei dati” in il modello e un foglio “esportazione di dati” nel file sorgente. Ciascuno di questi fogli deve essere uno specchio l’uno dall’altro. Pertanto, invece di creare una formula nel modello, che si riferisce alle celle di un file esterno, saranno le formule delle celle creato nel foglio di importazione dello stesso file (quindi il collegamento interno). Solo il foglio di importazione avrà collegamenti al file sorgente. Pertanto, nella cella F10 del foglio 1 deposito 1, troveremo una formula che si riferirà al Contenuto della cella F10 di Flashe 1 della cartella di lavoro 2. Questo faciliterà in gran parte la gestione e l’aggiornamento di tali file.

Esempio di creazione della creazione di foglie speculari

Ad esempio, Qui abbiamo i dati del file originale (classe UR 2):

Esportazione di dati in Excel

E qui abbiamo il nostro foglio di importazione del nostro modello (Workbook 1). Si noti che nella cella C8, è semplicemente una formula = che si riferisce esattamente alla stessa cella, o c8 ma della seconda cartella di lavoro.

Importazione dei dati in Excel

Per completare la scheda Modello utilizzata per calcolare le vendite di un manager, dovrebbe essere evitata per creare una formula che si riferisce direttamente al file sorgente e questo per tutti i motivi sopra menzionati. Nota Qui che il numero della cartella di lavoro con i dati di origine e il nome delle schede di questa cartella di lavoro saranno lunghi, più la formula sarà pesante, tanto in termini di peso e comprensione.

File correlati in Excel

Per completare questa sezione, dovrebbe essere piuttosto semplicemente fare riferimento a un calcolo nella scheda Importa.

File correlati in Excel

Oltre a alleggerire le formule, se mai una persona cancellata o modificata celle nella cartella di lavoro 2, senza apertura Il Binder 1, probabilmente sarebbe in grado di vedere gli errori creati nella scheda Esporta e correggerli prima di salvare il suo file. Pertanto, all’apertura della cartella di lavoro 1, le formule continueranno a segnalare i valori corretti.

Utilizzare la programmazione VBA per sostituire i file correlati

Sarebbe anche possibile programmare un Macro VBA che consentirebbe, ogni volta che un utente farebbe clic su un pulsante, importa i dati dai file sorgente nel modello e questo, nei valori (quindi nessun problema di prestazione relativo a formule troppo avidi). Devi solo trovare qualcuno nella tua attività che sa come programmare in VBA! Ma se è possibile, è una soluzione piuttosto efficace!

Usa la query di alimentazione (la nostra opzione preferita … e da lontano!) Per sostituire i file correlati

l’opzione ultima causa Per me, l’uso della query di potenza, che consente a tutti gli utenti il più limitato a Excel, importare i dati dei file esterni attraverso query che dovrà essere aggiornata solo per recuperare i valori. Più recente. A differenza del linguaggio VBA, tali richieste sono piuttosto semplici da scrivere poiché, anche se la query di potenza si basa su un linguaggio di programmazione, la lingua M, è stata progettata da Microsoft per una soluzione “codice basso”. In altre parole, gli utenti sono in grado di creare query, senza dover imparare a codice. Possono semplicemente fare clic su diversi pulsanti e menu per costruire le loro domande e questo genererà automaticamente il codice per loro. Non è fantastico?

Esempio di utilizzo della query di alimentazione

I dati del file di importazione anziché collegare

Ad esempio, qui, qui sarà, dal primo Binder 1, importare i dati della cartella di lavoro 2.

Importa un file Excel con la query di potenza Avanti, sarà necessario scegliere la scheda della cartella di lavoro 2 che vogliamo importare nella cartella di lavoro 1.

file excel importato tramite query di potenza

Crea una richiesta di trasformazione

Infine, facendo clic su diversi pulsanti e funzionalità, saremo in grado di trasformare i dati in una tabella ben strutturata e questi passaggi di trasformazione verranno registrati tramite i passaggi applicati (pannello a destra).Ciò significa che sullo sconto, la richiesta tornerà attraverso tutti i passaggi di trasformazione e se vi sono quindi nuovi dati o dati modificati nella cartella di lavoro 2, il tutto sarà facilmente integrato in Bolster 1. P.>

QUERY QUERY QUERY

Recupera i dati importati in una scheda

sarà quindi possibile recuperare i dati in una tabella Excel, creando a la crosstab dinamica o una crosstab dinamica basata su questi dati o invio di questi dati nel perno di potenza per creare un cubo virtuale collegando questa tabella con altre tabelle.

Array Excel risultante da A Combazione della query di alimentazione'une requête Power Query

Creare una query alternativa per ottenere lo stesso risultato in quanto precedentemente

Normalmente, con la query di potenza, stiamo cercando di ricostruire i database con i dati “non calcolati “. Piuttosto, vengono utilizzate le misurazioni in DAX, in Power PIVOt o Tabs. Crossover dinamico, per fare i calcoli. Ma se necessario, sarebbe anche possibile, nella query di potenza, per ottenere un dipinto con le vendite, piuttosto che un tavolo con prezzi e il numero di unità. Questo ci riporterebbe davvero al risultato nel nostro esempio.

QUERRY QUERRY QUERY

Fornisci il vantaggio dei dati importati sui dati Ottenuto per link

Che cosa è per lo più necessario qui è che l’utente della query di potenza non funziona più con i dati correlati, ma piuttosto con i dati importati, che vengono aggiornati su richiesta. In tal modo, i problemi dei file correlati sono completamente eliminati. Allo stesso modo, il peso del file, che non richiede formule complesse, che comprime i dati.

non più motivo per non utilizzare la query di alimentazione

power. Query è ora disponibile nel una stragrande maggioranza delle versioni di Excel gratuitamente. Dobbiamo aggiungere un complemento per la versione 2010 e 2013, ma è disponibile in tutte le altre versioni successive. La query di potenza ha completamente cambiato il modo di lavorare con Excel, ottimizzando il tempo necessario per eseguire attività manuali ma anche la regolazione di problemi di lunga data, come il problema dei file correlati!

File di supporto VIP da scaricare

Per scaricare il file utilizzato in questo tutorial, diventare un membro VIP del CFO mascherato.

Allenamento complementare

Al fine di approfondire le tue conoscenze con Excel, specialmente il Attività che influenzano i dati, il consolidamento e l’elaborazione dei dati, raccomandiamo la nostra formazione Excel – Introduzione alla query di potenza e nella lingua M.

Ecco alcuni commenti degli studenti che hanno seguito questo allenamento:

CFO-mask_formation-in-line_fbIl CFO mascherato offre una vasta gamma di decisioni Fare formazione informatica con Excel e Power BI, tramite un portale online e remoto in TE MPS reali, secondo un calendario. Se vuoi organizzare corsi privati, inviaci semplicemente un’email a [email protected] I certificati adatti per gli standard di istruzione continua per i vari ordini professionali di Quebec sono offerti per tutte le formazione.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *