La funció de sumproducte per fer una suma condicional

Ja heu parlat de com utilitzar la suma de la bruixa condicional (suma condicional en francès) per realitzar una suma condicional en diversos criteris. També us vaig mostrar com fer-ho utilitzant el sumif (suma en francès). Avui, voldria mostrar-vos una altra alternativa, molt potent, ja sigui l’ús de la funció Sumproduct (Sommeprod en francès) per no fer la suma de camps multiplicats, sinó fer una quantitat condicional segons diversos criteris. Un cop hagueu dominat aquest mètode, no podreu passar-ho!

demostració concreta de l’ús de la funció

a l’exemple següent, tenim un fitxer amb dos pestanyes. El que volem demostrar és el moment en què cada empleat realment va a fer un treball productiu contra el temps total on està disponible per treballar.

La primera pestanya es titula “Llista principal”. Conté hores programades de treball (atorgat) per empleat en diferents dates durant l’any 2011.

La segona pestanya té dret “ocupació” Empleats “i s’utilitza per calcular la taxa d’ocupació real dels empleats contra les hores de treball programades (atorgades). En aquesta pestanya, primer hem inserit una taula en la qual hem posat el nom de tots els empleats en una columna amb cèl·lules addicionals per afegir empleats segons sigui necessari. Vegeu Columna B a la imatge següent.

Completa la taula de dades

Hem assignat cada empleat un sector d’activitats. A més, a la pestanya “Llista principal”, la columna “Matrals” no és una entrada, sinó un càlcul derivat de la taula següent. Simplement hem utilitzat la fórmula VLOOKUP (cercat en francès) per presentar el sector empresarial de cada empleat.

Després, hem afegit una columna “hores setmanals” per indicar el nombre d’hores disponibles per setmana per empleat.

Finalment, hem afegit dues columnes: “Inici” i “End” per indicar la data de contractació i la data de finalització de cada recurs.

Crea un menú desplegable de validació de dades

Després hem creat un menú desplegable amb tots els noms dels empleats mitjançant la funció de validació de dades (validació de dades en francès). Per tant, quan feu clic a la cel·la B14 (vegeu la imatge següent), tenim una caiguda -Noun menú amb la llista d’empleats i hem de triar un empleat. A la cel·la B15, utilitzem un simple VLOOKUP (recerca de francès) per indicar el sector de l’activitat.

A la cel·la B19, vam indicar el Data del primer dilluns de l’any 2 011. A la cel·la B20, només hem registrat: = B19 + 7 i hem copiat la fórmula fins que arribeu a l’últim dilluns de 2011, el 26 de desembre de 2011.

Calculeu el nombre d’hores programades

A la columna C, vam utilitzar una fórmula especial per calcular el nombre d’hores previst (atorgat) per recurs per setmana. A la cel·la C19, la fórmula de la funció Sumproduct sembla així:

= Sumpoduct (‘llista principal’! $ B $ 2: $ b $ 1901 = $ b $ 14) * (‘llista principal’ $ C $ 2: $ c $ 1901 > = $ b19) * (‘llista principal’! $ C $ 2: $ c $ 1901 < $ b20); “llista principal”! $ d $ 2: $ d $ 1901)

Què entendre és que tots els arguments separats per * són condicions. Així, en el present exemple, es deia per primera vegada que l’empleat ha de ser igual a la cel·la B14 (on es troba la selecció del menú desplegable). Llavors es diu que la data ha de ser superior o igual al 3 de gener de 2011 i ha de ser inferior al 10 de gener de 2011. L’últim argument precedit per un; Especifica les cèl·lules que s’han d’abordar, les hores programades (atorgades).

Calculeu les hores disponibles

Per calcular les hores disponibles, a la cel·la D19, hem utilitzat la següent fórmula:

= si (i (Vlookup ($ B $ 14, $ 4: $ F $ 11; 4, false) = $ B19; VLOOKUP ($ B $ 14, $ 4: $ F $ 11; 5; False) > = $ B19);
VLOOKUP ($ B $ 14, $ B $ 4: $ F $ 11; 3; FALSE); 0)

En resum, aquesta fórmula indica que si la data indicada a la columna B és superior o igual a la data de contractació i igual o inferior al final de l’ús, s’hauria d’indicar el nombre d’hores de treball disponible. Aquesta setmana .

Calculeu la diferència

En conclusió, la diferència és simplement la diferència entre les hores concedides (planificades) i les hores disponibles.

Dibuixa un gràfic d’hores adjudicats vs disponible

Finalment, podem dibuixar un gràfic que Mostra les hores de treball atorgades (planejat) vs les hores de treball disponibles per empleat, segons el seu període d’ocupació.

quí És un breu vídeo que mostra el dinamisme dels gràfics, així creat.

A més, notareu que els títols dels gràfics canvien automàticament. Us expliquem com fer-ho: Excel: creeu títols gràfics dinàmics.

Formació complementària

És tan bo com penses amb Excel? Provar! A continuació, podreu seguir la formació d’actualització d’Excel que pot servir com a actualització per a professionals que ja utilitzen Excel com a part de les seves funcions i volen anar més enllà.

Aquí hi ha alguns comentaris dels aprenents que tinguin Seguit entrenament d’Excel: actualització:

El CFO emmascarat ofereix un ampli ventall de decisió -Formació d’informàtica amb Excel i Power Bi, a través d’un portal en línia i remot en temps real, segons un calendari. Si voleu organitzar cursos privats, simplement envieu-nos un correu electrònic a [email protected] Els certificats adequats per a les normes de formació continuada per a les diferents ordres laborals de Quebec s’ofereixen per a tota la formació.

Leave a comment

L'adreça electrònica no es publicarà. Els camps necessaris estan marcats amb *