Este posibil să nu știți cum să utilizați funcția OFFSET în Excel

Știați că funcția OFFSET din Microsoft Excel vă poate ajuta să faceți multe alte lucruri, nu doar referințe de celule? Mai jos sunt modalități unice de a utiliza OFFSET în Excel pe care este posibil să nu le cunoașteți.

Funcția OFFSET din Microsoft Excel este un instrument flexibil și puternic. Datorită complexității și numărului de funcții similare, din păcate, această funcție Excel este subutilizată.

Această funcție relativ simplă poate ajuta utilizatorii să economisească timp și efort. Simplifică obținerea de puncte de date precise dintr-un set de date principal bine sortat. De asemenea, permite crearea simplă de rapoarte detaliate, vizualizări interactive și tablouri de bord complexe.

Creați un set de date cu dimensiuni personalizate

Una dintre cele mai obișnuite utilizări ale funcției OFFSET din Excel este de a colecta seturi de date redimensionabile. Cu un set mare de date, poate doriți să luați în considerare doar o mică parte a datelor la un moment dat.

De exemplu, pentru a furniza un raport automat al vânzărilor totale ale angajaților, trebuie să obțineți totalul pentru o anumită săptămână sau săptămâni.

Scrierea unei funcții SUM diferită pentru fiecare total poate fi destul de ușoară. Cu toate acestea, utilizarea funcției OFFSET va fi mai ușor de utilizat și mai rapid de reutilizat.

Mai întâi, introduceți data cu care doriți să începeți în celula B6. Apoi, introduceți numărul de zile în care doriți să rulați raportul în B7. Apoi, utilizați funcțiile simple SUM și MEDIE pentru a obține mai multe date pentru fiecare angajat în perioada selectată:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Ajustarea datei sau orei de începere este la fel de simplă ca și editarea unei celule, dar puteți trage și redimensiona grupuri de date mult mai ușor.

Permite crearea de rapoarte personalizate

Utilizați aceeași metodă ca mai sus pentru a crea un raport complet prin personalizarea variabilelor destul de ușor. Puteți face ca primul argument din funcția OFFSET să facă referire la o altă foaie. Acest lucru vă permite să separați introducerea datelor și raportarea.

Acțiunea de mai sus face, de asemenea, posibilă tragerea de date selectate dintr-o foaie de lucru în alta. În plus, vă permite să afișați numai informații relevante pentru raportul curent. Configurarea unui raport care arată totalurile zilnice folosind aceleași date de până acum este destul de simplă. Puteți face acest lucru fără a prelua date pentru date care nu au legătură.

Următoarea funcție de compensare plasată în celula B5 va prelua numărul de zile specificat în B2. Începe în săptămâna selectată în B1 și arată numai datele pentru perioada relevantă:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

Este posibil să restricționați intrările în B1 și B2 și să creați o listă derulantă în Excel cu săptămânile disponibile folosind validarea datelor.

Paginare tabelar

Funcția OFFSET poate face posibilă, de asemenea, paginarea simplă a foii de calcul, o sarcină adesea asociată cu utilizarea filei Excel Developer. Acest lucru poate face procesarea unor grupuri mari de date mai simplă și mai eficientă.

Când lucrați cu foi de calcul care conțin până la 30.000 de rânduri de date, poate fi dificil să căutați toate datele simultan. Creați un meniu derulant care acceptă un interval de numere de la 1 la 300, permițându-vă să separați rapid datele pe pagină cu 100 de rezultate pe pagină.

Funcția OFFSET are întotdeauna punctul de ancorare plasat în celula de date de sus. Cu toate acestea, al doilea argument este offset-ul vertical. Această valoare este egală cu numărul paginii selectate minus 100. Offset-ul este orizontal, al treilea argument poate fi setat la 0.

Al 4-lea argument este setat la 100, al 5-lea argument este numărul de coloane din setul de date. Această metodă face împărțirea și gestionarea tabelelor de date extrem de ușoară.

Mai jos este un exemplu de date paginate. Include 500 de informații înregistrate de la un senzor într-o foaie și un raport paginat pe cealaltă foaie.

Raportul folosește validarea datelor în celula B1 pentru a crea un meniu derulant cu pagini în interior. Următoarea expresie din celula A3 creează corpul raportului:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Mai sus sunt câteva modalități de a utiliza funcția de offset în Excel pe care puțini oameni le cunosc, dar sunt cu adevărat utile, mai ales atunci când trebuie să procesați cantități mari de date.

Sper că articolul vă este de folos.