Excel: 5 sfaturi pentru a evita problemele legate de fișiere

Un client a pus recent următoarea întrebare: „Într-un context în care mai multe departamente lucrează la un buget, ne-am separat Model în mai multe fișiere diferite … cu dificultățile pe care le implică, formulele mai complicate și legăturile dintre fișierele care urmează să fie menținute. Faceți-i alții altfel? „. Desigur, problemele fișierelor de Excel asociate apar în special în companiile care au un proces bugetar descentralizat.

Galeley a fișierelor Excel legate

Deși Excel poate oferi întotdeauna un răspuns la toate Probleme (prin natura sa de flexibilitate extremă), conceptul de gestionare a dosarului legat constituie fără îndoială călcâiul său Ahile. Această slăbiciune încurajează pe mulți să se întoarcă la bugetarea / prognozarea sistemelor. Acestea din urmă oferă interfețe colaborative și elimină problemele fișierelor independente. Dar aceste sisteme, adesea scumpe, sunt într-adevăr soluția finală la orice probleme legate de fișiere? Articolul urmărește să ofere alternative bugetare simple și foarte scăzute, dar adesea necunoscute utilizatorilor Excel.

Utilizatorii Excel creează fișiere conectate?

teoretic, unul nu ar trebui să împărțiți niciodată un model Excel în Mai multe fișiere, cu excepția cazului în care:

  • sunteți mai mult de un utilizator care trebuie să utilizeze modelul în același timp, iar opțiunea de lucru colaborativă inclusă în Excel nu mai este suficientă
  • Diferite părți ale modelului trebuie trimise către diferiți destinatari
  • modelul dvs. este prea mare
Acest articol a fost publicat inițial pe 11 iulie 2013 și a pus Din 16 noiembrie 2020.

Care sunt creatorii de fișiere conectate?

Managementul fișierelor în Excel expune utilizatorii la mai multe probleme potențiale:

  • Vizualizați datele actualizate, într-un fișier care se alimentează într-un alt fișier, ambele fișiere trebuie să fie deschise
  • când un utilizator modifică fișierul sursă, mai ales atunci când șterge sau modifică intervalele de date, fără a deschide Model (fișierul Excel care se hrănește pe acesta), modelul modelului se va întâlni adesea cu formule care nu mai sunt doar dreptate (referință la plaje de date proaste sau care se întorc de la mesajul de eroare #ref la în mod direct! (Referință la intervalele de date care au fost șterse)
  • Dacă numele frunzelor și fișierelor este cel puțin lung, acest lucru va crește semnificativ formulele și, prin urmare, greutatea modelului, crescând astfel posibilitatea întâlnirii Probleme de performanță
Aveți surse de date voluminoase pentru a aduna și analiza în Excel? Aflați cum să automatizați întregul proces de import, transformare și modelare a datelor cu antrenamentul nostru pe interogarea energică și Power Pivot în Excel.

Lucrați corect cu fișiere excel excel

când Diferitele fișiere au fost împărțite pentru a permite diferiților utilizatori să actualizeze secțiuni diferite și că opțiunea de lucru colaborativă inclusă în Excel nu a fost reținută, aici sunt alternativele care trebuie puse în aplicare. Dintre acestea, ultimele două sunt cu siguranță cele mai interesante, mai ales cele mai recente.

Utilizați formule 3D pentru a elimina fișierele asociate

Fiecare manager de buget ar putea să-și îndeplinească propriul buget într-un exemplu independent Fișier, care ar fi identic pentru fiecare manager. Am putea apoi să copiem pur și simplu conținutul acestor pagini, între terminalele de start și de capăt, un fișier de consolidare, cuprinzând formule 3D, care ar fi, prin urmare, permise ca și când adaugă aceste pagini. Desigur, aceasta nu ar fi o opțiune „dinamică”. Prin urmare, ar trebui să fie compensată prin crearea unei pagini de documentare, menținând în memorie datele ultimei versiuni a acestor pagini, astfel încât acest proces să fie eficient, o anumită verificare ar fi Fii atât de necesar. Dacă puteți elimina acest loc de muncă „manual”, acesta va fi întotdeauna preferat. Apoi ar fi necesar să alegeți alte opțiuni prezentate mai jos.

Utilizați câmpurile numite în fișierele sursă

Utilizați câmpurile „Numed” pentru câmpurile fișierului sursă care sunt utilizate . În model. Această opțiune, deși simplu de configurat, provoacă avantaje și dezavantaje.Atât de bine folosit, va păstra formulele aferente, dar dacă utilizatorii încep să copieze informațiile dintr-o foaie Excel cu câmpuri numite în alte fișiere Excel, acestea ar putea începe să creeze link-uri între fișiere. Care nu ar trebui să fie legate. Prin urmare, este necesar să existe o vigilentă suplimentară cu această opțiune. Dacă puteți prioritiza o altă opțiune următoare, va fi întotdeauna mai bine să o faceți.

Utilizați foi „Mirror” în fișierele dvs. legate

Creați o foaie „Importarea datelor” în modelul și un „date de export” în fișierul sursă. Fiecare dintre aceste foi trebuie să fie o oglindă una de cealaltă. Astfel, în loc să creeze o formulă în model, care se referă la celulele unui fișier extern, vor fi formulele de celule create în fișa de import a aceluiași fișier (deci link-ul intern). Numai foaia de import va avea legături cu fișierul sursă. Astfel, în celula F10 a foii 1 Depunerea 1, vom găsi o formulă care se va referi la Conținutul celulei F10 din Flata 1 din registrul de lucru 2. Aceasta va facilita în mare măsură gestionarea și actualizarea acestor fișiere.

Exemplu de creare a frunzelor oglinzii

De exemplu, Aici avem datele fișierului original (clasa UR 2):

Exportarea datelor în Excel

și aici avem foaia de import a modelului nostru (registrul de lucru 1). Rețineți că, în celula C8, este pur și simplu o formulă = care se referă exact la aceeași celulă sau C8, dar din al doilea registru de lucru.

Importarea datelor în Excel

Pentru a finaliza fila model care este utilizată pentru a calcula vânzarea managerului A, trebuie evitată să creați o formulă care se referă direct la fișierul sursă și acest lucru din toate motivele menționate mai sus. Rețineți că numărul registrului de lucru cu datele sursă și numele filelor acestui registru de lucru va fi lung, cu atât mai mult formula va fi grea, cât mai mult în ceea ce privește greutatea și înțelegerea.

Fișierele asociate în Excel

Pentru a finaliza această secțiune, ar trebui să fie mai degrabă pur și simplu la un calcul în fila Import.

Fișierele asociate în Excel

În plus față de ușurarea formulelor, dacă vreodată o persoană a fost ștersă sau modificată celulele în registrul de lucru 2, fără deschidere Binder 1, probabil că ar putea să vadă erorile create în fila Export și să le corecteze înainte de a salva fișierul. Astfel, la deschiderea regisului de lucru 1, formulele vor continua să raporteze valorile corecte.

Utilizați programarea VBA pentru a înlocui fișierele asociate

Ar fi posibil să fiți, de asemenea, posibil să programați a Macro VBA care ar permite, ori de câte ori un utilizator va face clic pe un buton, importați datele din fișierul (fișierele) sursă în model și acest lucru, în valori (astfel încât nici o problemă de performanță legată de formulele prea lacome). Trebuie doar să găsiți pe cineva din afacerea dvs. care știe cum să programez în VBA! Dar dacă este posibil, este o soluție destul de eficientă!

Utilizați interogarea de alimentare (opțiunea noastră preferată … și de la Afar!) Pentru a înlocui fișierele asociate

opțiunea final rămâne Pentru mine, utilizarea interogării de putere, care permite tuturor utilizatorilor, cu atât mai mult la Excel, să importe date de fișier extern prin interogări care vor trebui să fie actualizate numai pentru a prelua valorile. Cele mai recente. Spre deosebire de limbajul VBA, aceste cereri sunt destul de simple de scris, deoarece, chiar dacă interogarea de putere se bazează pe un limbaj de programare, limba M, a fost proiectată de Microsoft pentru o soluție „cu cod scăzut”. Cu alte cuvinte, utilizatorii pot crea interogări, fără a fi nevoie să învețe să codifice. Ele pot face clic doar pe diferite butoane și meniuri pentru a-și construi interogările și acest lucru va genera automat codul pentru ei. Nu este minunat?

Exemplu de utilizare a interogării de alimentare

Importul datelor de fișier mai degrabă decât legarea

De exemplu, aici, va fi mai întâi, de la liant 1, importați datele registrului de lucru 2.

Importați un fișier Excel cu interogare de alimentare următoare, va fi necesar să alegeți fila Registrului de lucru 2 pe care vrem să le importăm în registrul de lucru 1.

Fișierul Excel importat prin intermediul interogării de alimentare

Creați o solicitare de transformare

În cele din urmă, făcând clic pe mai multe butoane și caracteristici, vom putea transforma datele în tabel bine structurat și aceste pași de transformare vor fi înregistrate prin pașii aplicați (panoul din dreapta).Aceasta înseamnă că, la discount, cererea va reveni prin toate etapele de transformare și dacă există, prin urmare, date noi sau date modificate în registrul de lucru 2, întregul va fi ușor integrat în Bolster 1. P.>

Preluarea datelor importate într-o filă

Acesta va fi apoi posibilă recuperarea datelor într-o masă Excel, creând a Dynamic Crosstab sau un crrosstab dinamic bazat pe aceste date sau trimiterea acestor date în Power Pivot pentru a crea un cub virtual prin conectarea la acest tabel cu alte tabele Interogare de interogare de putere

Creați o interogare alternativă pentru a obține același rezultat ca și în mod anterior

în mod normal, cu interogare de alimentare, încercăm să reconstruim baze de date cu datele „care nu sunt calculate „Mai degrabă, sunt utilizate măsurătorile din DAX, în pivotul de putere sau în tabele. Crossover dinamic, pentru a face calculele. Dar, după cum este necesar, ar fi posibil, în interogarea energică, pentru a obține o pictură cu vânzări, mai degrabă decât o masă cu prețuri și numerele de unități. Acest lucru ne-ar aduce înapoi la rezultat în exemplul nostru.

Asigurați avantajul datelor importate asupra datelor Obținut prin link

ceea ce este mai ales necesar aici este că utilizatorul de interogare a energiei nu mai funcționează cu date conexe, ci mai degrabă cu date importate, actualizate la cerere. În acest sens, problemele fișierelor asociate sunt complet eliminate. În mod similar, greutatea fișierului, care nu necesită formule complexe, care comprimă datele.

Nu mai este nici un motiv pentru a nu utiliza interogarea de alimentare

putere. Interogare este acum disponibilă în Marea majoritate a versiunilor Excel gratuit. Trebuie să adăugăm o completare pentru versiunea 2010 și 2013, dar este disponibilă în toate celelalte versiuni ulterioare. Inteeria de alimentare a schimbat complet modul de lucru cu Excel, optimizând timpul necesar pentru a efectua sarcini manuale, dar și ajustarea problemelor de lungă durată, cum ar fi problema fișierelor conexe!

Fișier de suport VIP pentru a descărca

Pentru a descărca fișierul utilizat în acest tutorial, deveniți membru VIP al CFO mascat.

Formare complementară

pentru a vă aprofunda cunoștințele cu Excel, în special Sarcini care afectează datele, consolidarea și prelucrarea datelor, recomandăm antrenamentul nostru Excel – Introducere la interogarea energică și limba M.

Iată câteva comentarii de la cursanții care au urmat această pregătire:

CFO-MASK_FORMATION-IN-LINE_FB CFO mascate oferă o gamă largă de decizii făcând formarea de calcul cu Excel și Power BI, prin intermediul unui portal online și la distanță în Te MP-uri adevărate, conform unui calendar. Dacă doriți să organizați cursuri private, trimiteți-ne pur și simplu un e-mail la [email protected] Certificatele adecvate pentru standardele educaționale continue pentru diferitele ordine profesionale din Quebec sunt oferite pentru toate formarea.

Lasă un răspuns

Adresa ta de email nu va fi publicată. Câmpurile obligatorii sunt marcate cu *