FITXA D'EXCEL SUM i OFFSET

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 a Excel us permet configurar un rang dinàmic que canvia a mesura que s'afegeixen les xifres de vendes de cada dia.

Per si mateix, la funció SUM sol allotjar la inserció de noves cel·les de dades al 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 anterior, les noves xifres de vendes de cada dia s'afegeixen a la part inferior de la llista, obligant al total a canviar contínuament una cel·la cada vegada a mesura que s'agreguen les dades noves.

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 de dalt per 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: Es requereix el punt de partida del rang de cel·les que es sumarà a la funció SUM. A la imatge d'exemple, aquesta és la cel·la B2.
• Referència: La referència de referència necessària per al càlcul de l'extrem del rang es troba 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, perquè sempre voleu que l'interval finalitzi una cel·la per sobre de la fórmula.
• Files: Es requereix 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ó de l'offset està per sobre de la Referència argument, el valor és negatiu. Si és inferior, l'argument Rows é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 de referència, de manera que el valor d'aquest argument és negatiu un (-1).
• Cols: Es requereix 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·laB7 i afegeix cel·laB6 al rang sumat per la fórmula.

S'està introduint la fórmula SUM OFFSET

1. Feu clic a la cel·la B6, que és la ubicació on es mostraran els resultats de la fórmula inicialment.
2. Feu clic alFórmules pestanya del menú de la cinta.
3. TrieuMath & Trigdes de la cinta per obrir la llista desplegable de la funció.
4. Fer clic aSUMA a la llista per mostrar el quadre de diàleg de la funció.
5. Al quadre de diàleg, feu clic a la iconaNúmero 1línia
6. 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.
7. Al quadre de diàleg, feu clic a la iconaNúmero 2línia
8. Introduïu la següent funció OFFSET: OFFSET (B6, -1,0) per formar l'extremitat dinàmica de la fórmula.
9. Feu clic D'acord per completar la funció i tancar el quadre de diàleg.

El total apareix a la cel·la B7.

Afegiu les dades de vendes del dia següent

Per afegir les dades de vendes del dia següent:

1. Feu clic amb el botó dret a l'encapçalament de la fila per a la fila 6 per obrir el menú contextual.
2. Al menú, feu clic aInsereix per inserir una nova fila al full de treball.
3. Com a resultat, la fórmula SUM OFFSET es mou cap a la cel·la B7 i la fila 6 ja està buida.
4. Feu clic a la cel·la A6.
5. Introduïu el número5 per indicar que el total de les vendes del cinquè dia s'introdueix.
6. Feu clic a la cel·la B6.
7. Escriviu el número$1458.25 i premeu la teclaEntra tecla 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.

La funció OFFSET té dos arguments opcionals: alçada i amplada , que no es van utilitzar en aquest exemple.

Aquests arguments es poden utilitzar per indicar a la funció OFFSET la forma de la sortida en termes de tantes files d'alta i tantes columnes d'ample.

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.