Si el vostre full de treball d'Excel inclou càlculs basats en un interval de cel·les canviant, utilitzeu les funcions SUMA i OFFSET juntes en una fórmula SUM OFFSET per simplificar la tasca de mantenir els càlculs actualitzats.
Les instruccions d'aquest article s'apliquen a Excel per a Microsoft 365, Excel 2019, Excel 2016, Excel 2013 i Excel 2010.
Creeu un rang dinàmic amb les funcions SUM i OFFSET
Si utilitzeu càlculs durant un període de temps que canvia contínuament, com ara determinar les vendes del mes, utilitzeu la funció OFFSET d'Excel per configurar un rang dinàmic que canvia a mesura que s'afegeixen les xifres de vendes de cada dia.
Per si mateixa, la funció SUMA normalment pot acomodar la inserció de noves cel·les de dades a l'interval que s'està sumant. Es produeix una excepció quan les dades s'insereixen a la cel·la on es troba actualment la funció.
A l'exemple següent, les noves xifres de vendes de cada dia s'afegeixen a la part inferior de la llista, cosa que obliga el total a baixar contínuament una cel·la cada vegada que s'afegeixen les dades noves.
Per seguir aquest tutorial, obriu un full de treball d'Excel en blanc i introduïu les dades de mostra. No cal que el vostre full de treball tingui el format de l'exemple, però assegureu-vos d'introduir les dades a les mateixes cel·les.
Si només s'utilitza la funció SUMA per sumar les dades, l'interval de cel·les utilitzat com a argument de funció s'hauria de modificar cada vegada que s'afegeixin dades noves.
En utilitzar les funcions SUMA i OFFSET juntes, l'interval que es suma es torna dinàmic i canvia per adaptar-se a noves cel·les de dades. L'addició de noves cel·les de dades no causa problemes perquè l'interval continua ajustant-se a mesura que s'afegeix cada cel·la nova.
Sintaxi i arguments
En aquesta fórmula, la funció SUMA s'utilitza per sumar el rang de dades subministrades com a argument. El punt inicial d'aquest interval és estàtic i s'identifica com la referència de cel·la al primer nombre que s'ha de sumar amb la fórmula.
La funció OFFSET està imbricada dins de la funció SUMA i crea un punt final dinàmic per a l'interval de dades totalitzat per la fórmula. Això s'aconsegueix establint el punt final de l'interval a una cel·la per sobre de la ubicació de la fórmula.
La sintaxi de la fórmula és:
=SUMA(Inici de l'interval: OFFSET(Referència, Files, Cols))
Els arguments són:
- Range Start: el punt de partida de l'interval de cel·les que es sumarà amb la funció SUMA. En aquest exemple, el punt de partida és la cel·la B2.
- Reference: la referència de cel·la necessària que s'utilitza per calcular el punt final de l'interval. A l'exemple, l'argument Referència és la referència de cel·la de la fórmula perquè l'interval acaba una cel·la per sobre de la fórmula.
- Rows: cal el nombre de files per sobre o per sota de l'argument Referència utilitzat per calcular la compensació. Aquest valor pot ser positiu, negatiu o zero. Si la ubicació del desplaçament està per sobre de l'argument Referència, el valor és negatiu. Si el desplaçament és inferior, l'argument Files és positiu. Si el desplaçament es troba a la mateixa fila, l'argument és zero. En aquest exemple, el desplaçament comença una fila per sobre de l'argument Referència, de manera que el valor de l'argument és negatiu (-1).
- Cols: el nombre de columnes a l'esquerra o a la dreta de l'argument Referència utilitzat per calcular el desplaçament. Aquest valor pot ser positiu, negatiu o zero. Si la ubicació del desplaçament es troba a l'esquerra de l'argument Referència, aquest valor és negatiu. Si el desplaçament és a la dreta, l'argument Cols és positiu. En aquest exemple, les dades que es sumen es troben a la mateixa columna que la fórmula, de manera que el valor d'aquest argument és zero.
Utilitzeu la fórmula SUM OFFSET per a les dades de vendes totals
Aquest exemple utilitza una fórmula SUM OFFSET per retornar el total de les xifres de vendes diàries que figuren a la columna B del full de treball. Inicialment, la fórmula es va introduir a la cel·la B6 i va totalitzar les dades de vendes durant quatre dies.
El següent pas és moure la fórmula SUM OFFSET una fila cap avall per deixar espai per al total de vendes del cinquè dia. Això s'aconsegueix inserint una nova fila 6, que mou la fórmula a la fila 7.
Com a resultat del moviment, Excel actualitza automàticament l'argument Referència a la cel·la B7 i afegeix la cel·la B6 a l'interval sumat per la fórmula.
- Seleccioneu la cel·la B6, que és la ubicació on es mostraran inicialment els resultats de la fórmula.
-
Seleccioneu la pestanya Fórmules de la cinta.
Image -
Tria Matemàtiques i activació.
Image -
Seleccioneu SUMA.
Image - Al quadre de diàleg Arguments de funció, col·loqueu el cursor al quadre de text Número1.
-
Al full de treball, seleccioneu la cel·la B2 per introduir aquesta referència de cel·la al quadre de diàleg. Aquesta ubicació és el punt final estàtic de la fórmula.
Image - Al quadre de diàleg Arguments de funció, col·loqueu el cursor al quadre de text Número2.
-
Introduïu OFFSET (B6, -1, 0). Aquesta funció OFFSET forma el punt final dinàmic de la fórmula.
Image -
Seleccioneu D'acord per completar la funció i tancar el quadre de diàleg. El total apareix a la cel·la B6.
Image
Afegeix les dades de vendes del dia següent
Per afegir les dades de vendes del dia següent:
- Feu clic amb el botó dret a la capçalera de la fila 6.
-
Seleccioneu Insereix per inserir una fila nova al full de treball. La fórmula SUM OFFSET es mou una fila cap avall fins a la cel·la B7 i la fila 6 ara està buida.
Image - Seleccioneu la cel·la A6 i introduïu el número 5 per indicar que s'està introduint el total de vendes del cinquè dia.
-
Seleccioneu la cel·la B6, introduïu $1458.25 i, a continuació, premeu Retorn.
Image - Cel·la B7 actualitza el nou total de 7137,40 $.
Quan seleccioneu la cel·la B7, la fórmula actualitzada apareix a la barra de fórmules.
=SUMA(B2:OFFSET(B7, -1, 0))
La funció OFFSET té dos arguments opcionals: Alçada i Amplada, que no s'han utilitzat en aquest exemple. Aquests arguments indiquen a la funció OFFSET la forma de la sortida en termes de nombre de files i columnes.
En ometre aquests arguments, la funció utilitza l'alçada i l'amplada de l'argument Referència, que en aquest exemple té una fila d'alçada i una columna d'amplada.
