photosdematures.com

Ús combinat de sumif (vlookup)

Sumif amb VLOOKUP és una combinació de dues funcions condicionals diferents, SUMIF s’utilitza per sumar les cel·les basant-se en alguna condició que pren arguments de rang que té les dades i després els criteris o la condició i les cel·les a sumar, en lloc dels criteris que fem servir. VLOOKUP com a criteri quan hi ha una gran quantitat de dades disponibles en diverses columnes.

SUMIF és una funció presentada a Excel des de la versió 2007 per sumar els diferents valors coincidents amb els criteris. VLOOKUP és una de les millors fórmules per recopilar dades d'altres taules. Quan hi ha diverses condicions i columnes, sumif (vlookup) s'utilitza per realitzar els càlculs múltiples al full Excel. L’inconvenient de la funció SUMIF que retorna l’únic número es supera mitjançant l’ús de VLOOKUP. VLOOKUP ajuda a retornar qualsevol tipus de dades d’una taula en funció dels criteris coincidents.

Explicació

Funció SUMIF: És una funció de trigonometria i matemàtica que suma els valors quan la condició establerta és certa. El valor de la suma s’obté basant-se només en un criteri.

Quan tractem la funció SUMIF a Excel, s’utilitza la següent fórmula

• Rang: És l’abast de les cèl·lules que s’utilitzen per avaluar els criteris establerts
• Criteris: És la condició per sumar els valors. Pot ser una referència de cel·la, un número i una altra funció excel. Quan vulguem combinar SUMIF i VLOOKUP, s'introduirà la funció vlookup al lloc dels criteris
• Rang de la suma: És l'interval de cel·les especificat per sumar els valors numèrics.

Ara, la fórmula es modifica a

Fórmula = SUMIF (Rang, Vlookup (valor de cerca, matriu de taula, número d'índex de columna, [cerca de rang]), [rang de suma])

• Valor de cerca: Especifica el valor que cal cercar en una taula. Pot ser una referència o un valor.
• Matriu de taules: És l'abast de la taula que conté dues o més de dues columnes.
• Número d'índex de columna: És l'índex relatiu d'una columna que cal especificar per retornar les dades necessàries d'una columna específica.
• [Consulta de rang]: És 0 o 1 per especificar si s’ha de retornar el valor exacte o s’ha de retornar el valor aproximat. Però és opcional per a un usuari. 0 indica una coincidència exacta i 1 indica una coincidència aproximada.

Com s'utilitza SUMIF amb la funció VLOOKUP?

L'ús combinat de sumif (vlookup) és útil per cercar dades basades en el criteri únic. S’utilitzen de moltes maneres a Excel per realitzar els càlculs cercant dades. S’utilitzen col·lectivament a l’entorn empresarial per realitzar diverses tasques i prendre bones decisions. Per utilitzar aquestes funcions de manera efectiva juntes,

En primer lloc, s’ha d’introduir la funció SUMIF utilitzant els dos mètodes.

Primer mètode: La fórmula ha d’escriure des del teclat tal com es mostra a la imatge següent.

Segon mètode: La funció SUMIF es pot inserir des de la pestanya "Fórmula" tal com es mostra a la imatge.

Després d’entrar a la funció SUMIF, la fórmula per a VLOOKUP s’introdueix dins de la funció SUMIF substituint l’element ‘Criteria’. Tots els paràmetres del VLOOKUP, inclosos el valor de cerca, la matriu de taula, el número d'índex de la columna i la cerca de l'interval. Aquests s'han d'incloure entre parèntesis per evitar errors amb la fórmula. L'interval de valors que cal sumar s'inclouen als elements de l'interval de suma de la funció SUMIF. Per fi, es van prémer les tecles CTRL, MAJÚS i ENTER per facilitar els valors com a matriu.

Exemples

Podeu descarregar aquesta plantilla Excel SUMIF amb VLOOKUP aquí - Plantilla Excel SUMIF amb VLOOKUP

Exemple # 1: ús de sumif (vlookup) junts per determinar algun valor

Aquest exemple mostra com utilitzar el sumif (vlookup) junts per trobar la suma de les vendes del mateix mes en anys diferents. Es consideren les dades següents per a aquest exemple, tal com es mostra a la captura de pantalla.

La taula de cerca considerada es mostra tal com s’esmenta a continuació. Incloïa valors de referència per als mesos de gener a desembre.

Després d’introduir dades a la taula principal i a la taula de cerca, la funció SUMIF s’utilitza per determinar el total de vendes generades en diferents mesos de l’any. Aquí, el valor de cerca considerat és el mes. La fórmula que combina el sumif (vlookup) es mostra com,

Les vendes totals del mes de gener es determinen com a 17263.3. Quan vam canviar el valor d’aspecte a un mes més, es generen les vendes totals respectives.

Exemple # 2: Determinació de la suma basada en criteris de coincidència en diferents fulls de treball

En aquest exemple, la taula de cerca i la taula principal es prenen en fulls diferents en lloc d'un full únic. Les dades de la taula de cerca es mostren a la captura de pantalla següent.

Les dades principals de la taula es mostren a la captura de pantalla següent.

Per determinar el total de vendes, es pren el nom del venedor com a valor de cerca i s’utilitzen els identificadors d’empleats com a referència. La fórmula s’introdueix tal com es presenta a continuació i s’observen pocs canvis en la fórmula en comparació amb el primer exemple.

En aquest exemple, en lloc de seleccionar una matriu de cerca, només s'esmenta Lookup_table. Prement les tres tecles, incloses les tecles CTRL, MAJÚS i ENTRADA, es produeixen resultats precisos.

Quan es canvia el nom del venedor per noms esmentats a la cerca, la suma de les vendes varia i produeix un resultat nou.

Beneficis

A continuació es detallen els avantatges d’utilitzar aquestes funcions.

• Podem extreure fàcilment els valors d’una altra taula per realitzar càlculs.
• Determinació de la suma de valors presentats en un interval que compleixi els criteris esmentats en diferents aspectes d’una empresa.

Coses que cal recordar

• Mentre s'utilitza la funció VLOOKUP, el número d'índex de columna no ha de ser inferior a 1 per evitar errors.
• Els índexs s'han de donar a les columnes de la taula de cerca indicant-los amb els números 1, 2, 3, etc.
• S'ha d'utilitzar CTRL + MAJÚS + ENTRAR en excel en lloc d'introduir la clau, ja que VLOOKUP s'introdueix com a fórmula de matriu.
• Cal definir dues taules, incloses Main i Lookup, per extreure valors i determinar la suma dels valors de la matriu.
• SUMIF proporciona resultats precisos només per a dades numèriques; no funcionarà per a un altre tipus de dades.