Utilitzeu SUM i OFFSET per trobar totals per a rangs dinàmics de dades
Si el vostre full de càlcul d' Excel inclou càlculs basats en un rang canviant de cel·les, utilitzant les funcions SUM i OFFSET junts en una fórmula SUM OFFSET simplifica la tasca de mantenir actualitzats els càlculs.
Creeu un interval dinàmic amb les funcions SUM i OFFSET
Si feu servir càlculs durant un període de temps que canvia contínuament, com ara vendes totals del mes, la funció OFFSET us permet configurar un rang dinàmic que continuï canviant a mesura que s'afegeixin les xifres de vendes de cada dia.
Per si sol, la funció SUM sol allotjar noves cel·les de dades que s'insereixen en el rang sumat.
Es produeix una excepció quan les dades s'insereixen a la cel·la on es troba actualment la funció.
A la imatge d'exemple que acompanya aquest article, les noves xifres de vendes de cada dia s'afegeixen a la part inferior de la llista, la qual cosa obliga al total a canviar contínuament una cel·la cada vegada que s'agreguen les noves dades.
Si la funció SUM es va utilitzar per a completar les dades, seria necessari modificar l'interval de cel·les que s'utilitzava com a argument de la funció cada vegada que es van afegir noves dades.
Amb l'ús de les funcions SUM i OFFSET junts, però, el rang totalitzat es converteix en dinàmic. En altres paraules, canvia per allotjar noves cel·les de dades. L'addició de noves cel·les de dades no causa problemes perquè l'interval continua ajustant-se com s'afegeix cada nova cel·la.
Sintaxi i Arguments
Consulteu la imatge que acompanya aquest article a seguir juntament amb aquest tutorial.
En aquesta fórmula, la funció SUM s'utilitza per completar l'interval de dades subministrat com a argument. El punt d'inici d'aquest rang és estàtic i s'identifica com a referència de cel·la al primer número que es va completar amb la fórmula.
La funció OFFSET està amagada dins de la funció SUM i s'utilitza per crear un punt final dinàmic al rang de dades amb la fórmula. Això s'aconsegueix establint l'extrem del rang a una cel·la per sobre de la ubicació de la fórmula.
Sintaxi de la fórmula:
= SUM (Inici de rang: OFFSET (Referència, files, Cols))Inici de rang : (obligatori) el punt de partida de l'interval de cel·les que es sumarà a la funció SUM. A la imatge d'exemple, aquesta és la cel·la B2.
Referència : (requerida) la referència de la cel·la utilitzada per calcular l'extrem final del rang situat a moltes files i columnes de distància. A la imatge d'exemple, l'argument de referència és la referència de la cel·la per a la fórmula, ja que sempre volem que el rang finalitzi una cel·la per sobre de la fórmula.
Files : (obligatori) el nombre de files a dalt o per sota de l'argument de referència utilitzat en el càlcul del desplaçament. Aquest valor pot ser positiu, negatiu o fixat a zero.
Si la ubicació del desplaçament està per sobre de l'argument de referència , aquest valor és negatiu. Si és inferior, l'argument Rows és positiu. Si el desplaçament es troba a la mateixa fila, aquest argument és zero. En aquest exemple, el desplaçament comença una fila per sobre de l'argument de referència , de manera que el valor d'aquest argument és negatiu un (-1).
Cols : (obligatori) el nombre de columnes a l'esquerra o a la dreta de l'argument de referència utilitzat en el càlcul del desplaçament. Aquest valor pot ser positiu, negatiu o fixat a zero
Si la ubicació del desplaçament es troba a l'esquerra de l'argument de referència , aquest valor és negatiu. Si a la dreta, l'argument Cols és positiu. En aquest exemple, les dades que s'estan completant es troben a la mateixa columna que la fórmula, de manera que el valor d'aquest argument és zero.
Ús de la fórmula SUM OFFSET 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 completar les dades de vendes durant quatre dies.
El següent pas és moure la fórmula SUM OFFSET en una fila per deixar espai 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 de referència a la cel·la B7 i afegeix la cel·la B6 al rang sumat per la fórmula.
S'està introduint la fórmula SUM OFFSET
- Feu clic a la cel·la B6, que és la ubicació on es mostraran els resultats de la fórmula inicialment.
- Feu clic a la pestanya Fórmules del menú de la cinta .
- Trieu Math & Trig de la cinta per obrir la llista desplegable de la funció.
- Feu clic a SUM a la llista per mostrar el quadre de diàleg de la funció.
- Al quadre de diàleg, feu clic a la línia Número1 .
- Feu clic a la cel·la B2 per introduir aquesta referència de cel·la al quadre de diàleg. Aquesta ubicació és l'extrem estàtic de la fórmula;
- Al quadre de diàleg, feu clic a la línia Número2 .
- Introduïu la següent funció OFFSET: OFFSET (B6, -1,0) per formar l'extremitat dinàmica de la fórmula.
- Feu clic a D' acord per completar la funció i tancar el quadre de diàleg.
El total de $ 5679.15 apareix a la cel·la B7.
Quan feu clic a la cel·la B3, la funció completa = SUM (B2: OFFSET (B6, -1,0)) apareix a la barra de fórmules que hi ha a sobre del full de treball.
Afegiu 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 l' encapçalament de la fila per a la fila 6 per obrir el menú contextual.
- Al menú, feu clic a Insereix per inserir una nova fila al full de treball.
- Com a resultat, la fórmula SUM OFFSET es mou cap a la cel·la B7 i la fila 6 ja està buida.
- Feu clic a la cel·la A6 .
- Introduïu el número 5 per indicar que el total de vendes del cinquè dia s'introdueix.
- Feu clic a la cel·la B6.
- Escriviu el número $ 1458.25 i premeu la tecla Enter al teclat.
Cell B7 actualitza el nou total de $ 7137.40.
Quan feu clic a la cel·la B7, la fórmula actualitzada = SUM (B2: OFFSET (B7, -1,0)) apareix a la barra de fórmules.
Nota : La funció OFFSET té dos arguments opcionals: Alçada i Amplada, que es van ometre en aquest exemple.
Aquests arguments es poden utilitzar per indicar a la funció OFFSET la forma de la sortida pel que fa a tantes columnes d'ample i tantes columnes.
En omissió d'aquests arguments, la funció, per defecte, utilitza l'alçada i l'amplada de l'argument de referència, que, en aquest exemple, és una fila alta i una columna d'ample.