NB.SI.ENS en VBA

Bonjour,

J'ai besoin de calculer le nombre d'incidents ouvert aujourd'hui pour l'équipe n°2, 3 et 4 par exemple. Donc j'ai un tableau avec les incidents : 1ere colonne avec ID, 2eme avec Date ouverture et 3eme avec le nom de l'équipe. Sachant qu'il peut manquer des dates donc ne pas prendre en compte quand c'est vide.

Avec la formule j'étais obligé d'avoir quelque chose comme :

Code:

---

1 2 3

=NB.SI.ENS('Données incidents'!K:K;"<>""";'Données incidents'!K:K;">="&AUJOURDHUI();'Données incidents'!K:K;"<"&AUJOURDHUI()+1;'Données incidents'!B:B;"Equipe 2") +NB.SI.ENS('Données incidents'!K:K;"<>""";'Données incidents'!K:K;">="&AUJOURDHUI();'Données incidents'!K:K;"<"&AUJOURDHUI()+1;'Données incidents'!B:B;"Equipe 3") +NB.SI.ENS('Données incidents'!K:K;"<>""";'Données incidents'!K:K;">="&AUJOURDHUI();'Données incidents'!K:K;"<"&AUJOURDHUI()+1;'Données incidents'!B:B;"Equipe 4")

---

etc...

Sauriez-vous comment je peux faire avec le countif par exemple s'il vous plait ? Merci :)

Bonjour
voici un fichier qui propose une solution avec du countifs

par rapport à la formule classique proposée, j'ai simplifié car pour moi, valeur >= aujourdhui et valeur < aujourdhui +1 correspondent tout simplement à valeur = aujourdhui
de même pour le test en k différent de rien. du moment où on teste s'il y a la date du jour c'est que c'est déjà forcément différent de rien
après il y a peut être une raison à cela ?

pour tester, j'ai créé une table avec 1 incident pour eq1, 2 pour eq 2, 3 pour eq3 et enfin 4 pour eq4
J'ai nommé des cellules et des plages pour y faire plus facilement référence

Le code sera sans doute à réadapter, mais il devrait être possible d'y parvenir avec ceci :

Code:

---

1 2 3 4 5 6 7 8 9 10

Sub Test_nbsiens()   Range("INCID_E1") = WorksheetFunction.CountIfs(Range("Date_incid"), Range("DATE_JOUR").Value, Range("equipes"), "Equipe 1") Range("INCID_E2") = WorksheetFunction.CountIfs(Range("Date_incid"), Range("DATE_JOUR").Value, Range("equipes"), "Equipe 2") Range("INCID_E3") = WorksheetFunction.CountIfs(Range("Date_incid"), Range("DATE_JOUR").Value, Range("equipes"), "Equipe 3") Range("INCID_E4") = WorksheetFunction.CountIfs(Range("Date_incid"), Range("DATE_JOUR").Value, Range("equipes"), "Equipe 4")   MsgBox ("Aujourd'hui, il y a eu " & Range("total_incid").Value & " incidents"), , "Récapitulatif"   End Sub

---

En espérant avoir pu aider
Fabien

Salut

Nul besoin de VBA pour cela, mais sans Power Query, il te faudra une colonne en plus. De plus, les tableaux structurés permettent d'éviter les formules reprenant toute la colonne telles que K:K. A utiliser sans modération.

Dans la colonne supplémentaire de ton tableau, tu ajoutes la formule qui teste que l'équipe est présente dans le tableau des équipes sélectionnées, puis tu comptes la somme matricielle (validée par SHIFT+CTRL+ENTER hors XL365)

Pièce jointe 603928

Citation:

---

Envoyé par Fab_de_chez_PERFORM

par rapport à la formule classique proposée, j'ai simplifié car pour moi, valeur >= aujourdhui et valeur < aujourdhui +1 correspondent tout simplement à valeur = aujourdhui[...]

---

Non. Ce raisonnement n'est vrai que si la colonne des dates ne contient que la date. Ici, la formule utilisée laisse supposer que la colonne du "moment" contient la date ET l'heure de survenance de l'incident, ce qui impose que l'on est plus grand ou égal à aujourd'hui ET plus petit que demain (ce que j'ai traduit par un ENT(...) dans ma formule)

Avec Power Query, tu peux te passer de la colonne de construction:

1. Importer les deux tableaux dans Power Query;
2. Fusionner les requêtes sur base de la colonne des équipes en jointure interne;
3. Filtrer sur la date du jour;
4. Compter les lignes;
5. Renvoyer dans Excel.

Pièce jointe 603929


Avantages et inconvénients des méthodes:

• la méthode formulée requiert une colonne supplémentaire mais le calcul est effectué en temps réel (si calcul automatique) et l'extension d'un des tableau actualise les formules en temps réel également;
• la méthode Power Query ne requiert pas de colonnes mais il faut actualiser la requête en cas de modification de données.

Personnellement, je ne vois pas de méthode formulée sans construction de colonnes car il faudrait une double matricielle.

Troisième solution exclusive 365^(*)=> utiliser la fonction FILTRE et compter les lignes qu'elle renvoie

Pièce jointe 603930

^(*) Mais tu ne précises pas ta version ;)

Bonjour Pierre,

Pour ceux qui ont Office365, ta formule me semble la mieux adaptée mais pour les autres, le calcul me semble possible avec une formule matricielle classique sans colonne intermédiaire:

Code:

---

=SOMME(NON(ESTERREUR(EQUIV(TableauDesIncidents[Equipe]; TableauDesEquipes[Equipe]; 0))) * (ENT(TableauDesIncidents[Date]) = AUJOURDHUI()))

---

=> EQUIV accepte de traiter une matrice en premier argument.

Qu'appelles-tu une double matricielle?

Merci je vais jeter un oeil, ma version est 2008

Bonjour à tous,

Citation:

---

Envoyé par questionsguy

Merci je vais jeter un oeil, ma version est 2008

---

tu es sous MAC ?

Citation:

---

Envoyé par Igloobel

[...]tu es sous MAC ?

---

Apparemment oui, d'où l'importance de préciser le tag de version... ^^

Citation:

---

Envoyé par Pierre Fauconnier

Troisième solution exclusive 365^(*)=> utiliser la fonction FILTRE et compter les lignes qu'elle renvoie

^(*) ;)

---

Bonjour Pierre,

Quand tu parles d'ajouter une colonne, de laquelle parles-tu ? si c'est la colonne avec la liste des équipes c'est prévu.

Par contre j'ai l'impression qu'il y a beaucoup d'étapes pour faire ce calcul et mon but est vraiment d'automatiser au maximum, d'où ma question en VBA.

Concernant ma formule, j'ai essayé avec ça

Code:

---

=NB.SI.ENS('Données incidents'!K:K;"<>""";'Données incidents'!K:K;">="&D2;'Données incidents'!K:K;"<="&D2+7;'Données incidents'!B:B;A2)

---

Où D2 est une date et A2 une équipe mais au lieu de me renvoyer un nombre ça me renvoie une date, du genre 27/04/1900, comment ça se fait svp ?

Salut.

Promethée t'a donné une solution qui se réalise en une seule étape... Moins c'est dur ;)

A voir si ta version le permet (si tu es en 2008, c'est que tu es en Mac, et là, je décline toute aide, je déteste Mac et déteste encore plus Excel sous Mac).


Quoi qu'il est soit, et donc quelle que soit ta version, il me semble utile de passer par un tableau qui reprend les équipes dont tu veux dénombrer les lignes. C'est à mon sens une meilleure pratique que de les énoncer in extenso dans la formule. Donc tu passeras de toute façon par l'étape de création d'une plage reprenant les équipes à considérer, et il me semble pertinent que cette plage soit un tableau structuré (je ne sais pas si Mac permet les tableaux structurés).

Citation:

---

Envoyé par Pierre Fauconnier

Salut.

Promethée t'a donné une solution qui se réalise en une seule étape... Moins c'est dur ;)

A voir si ta version le permet (si tu es en 2008, c'est que tu es en Mac, et là, je décline toute aide, je déteste Mac et déteste encore plus Excel sous Mac).

---

Non pas du tout je suis sur PC :mrgreen: 365 for enterprise.
Oui ça m'arrange beaucoup de passer par le tableau car les managers devront mettre leurs équipes dans la liste

Dès lors, je ne comprends pas que tu dises sur une version 2008 (Tu parles de Office ou de windows??)

Si tu as Office 365, ma réponse donnée au message 5 devrait faire l'affaire. L'as-tu testée?

Désolé, pour être plus clair, je suis sur :
Windows 10 Professionnel x64 version 20H2
Excel version 2008 Microsoft 365 Apps for enterprise EDIT c'est la version 16 !

Je vois sur le réponse 5 qu'il est question d'un autre logiciel, je souhaite utiliser seulement les formules ou vba pour automatiser et ne pas passer par d'autres étapes. Il faudra que les managers récupèrent le fichier et mette à jour la liste de leurs équipes et chaque semaine les données incidents seulement pour que les calculs se mettent à jour tout seul, on ne pourra pas leur en demander plus...

C'est pourquoi j'avais fait des calculs en VBA mais ce n'est pas idéal car plus lent que les formules j'ai l'impression. Par contre avec les formules je dois bien trouver un moyen simple de faire ça j'imagine :)

Citation:

---

Envoyé par questionsguy

[...]
Excel version 2008 Microsoft 365 Apps for enterprise

---

Je ne comprends pas ^^

Citation:

---

Envoyé par questionsguy

[...]Je vois sur le réponse 5 qu'il est question d'un autre logiciel[...])

---

De quel "autre logiciel" est-il question dans ce message? Je ne comprends pas ^^

Citation:

---

Envoyé par questionsguy

[...]C'est pourquoi j'avais fait des calculs en VBA mais ce n'est pas idéal car plus lent que les formules j'ai l'impression. Par contre avec les formules je dois bien trouver un moyen simple de faire ça j'imagine :)

---

Deux solutions simples t'ont été fournies, une par moi et une par Prométhée... Je ne comprends pas ^^



En fait, je ne comprends rien du tout à ton propos. As-tu seulement essayé la solution que je donne dans mon message sus-cité? As-tu essayée la solution de promothée?

J'ai manifestement de plus en plus de difficultés avec les questions des gens, sur ce forum.

Citation:

---

Envoyé par Pierre Fauconnier

Troisième solution exclusive 365^(*)=> utiliser la fonction FILTRE et compter les lignes qu'elle renvoie

Pièce jointe 603930

^(*) Mais tu ne précises pas ta version ;)

---

J'ai confondu avec le message 4 où il était question de power query ! :calim2:

Pour la version : Il y a écrit Excel 2008 365... Mais quand je clique sur détails c'est bien la VERSION 16 !!!

Pour le filtre je vais tester du coup car je n'ai pas tout compris dans la formule. Il y a écrit "aujourd'hui" dans la formule, comme spécifié aussi dans mon premier message. Il s'avère que j'ai aussi besoin de la semaine précédente de lundi à vendredi donc je vais tester avec des dates que j'ai entré dans les cellules, j'espère pouvoir rentrer une condition supplémentaire

EDIT : ça fonctionne, merci Promethee et Pierre, j'ai pu utiliser cette formule :

Code:

---

=SOMME(NON(ESTERREUR(EQUIV(Tableau1[Groupe];Tableau2[Groupes]; 0))) * (ENT(Tableau1[Date Ouverture Incident]) = AUJOURDHUI()-4))

---

Je vais maintenant essayer de l'adapter pour calculer entre deux dates

Bonjour,

Je pense qu'il faut interpréter 2008 comme août 2020.

Citation:

---

Envoyé par Promethee25

Bonjour,

Je pense qu'il faut interpréter 2008 comme août 2020.

---

Effectivement c'est probablement ça...

J'ai essayé de sélectionner les incidents fermés donc avec date de clôture et j'y parviens avec ça :

Code:

---

(NON(ESTVIDE(Tableau1[Date de clôture])))

---

Mais pas avec ça :

Code:

---

(ENT(Tableau1[Date de clôture]) <> "")

---

Sauriez vous pourquoi ? Quelle est la bonne méthode ?

Salut.

La discussion initiale parle de dénombrement avec NB.SI.ENS. Ici, tu parles de sélectionner... Que veux-tu faire? Dénombrer ou filtrer?

Pour dénombrer, tu peux utiliser le critère "<>"

Pièce jointe 604438



Pour filtrer, il suffit de décocher l'item "(Vides)" du filtre

Pièce jointe 604439


Avec une 365, tu peux utiliser FILTRE pour extraire

Pièce jointe 604440


Tu pourrais aussi extraire les non-vides par Power Query pour les récupérer dans un tableau structuré.


Bref, tu as le choix. Si rien ne convient, précise ton besoin ;)

Citation:

---

Envoyé par questionsguy

[...]Sauriez vous pourquoi ?[...]

---

Pour répondre à ta question: (ENT(Tableau1[Date de clôture]) <> "") va tenter de transformer la cellule vide en numérique pour pouvoir en extraire la partie entière. Du coup, le "vide" va être transformé en 0, et ENT(0) donne 0 qui est <>"". Du coup, toutes les cellules de la colonne sont <> "" et tu ne filtres rien.

Il existe un chouette outil dans Excel qui s'appelle l'audit de formule et que tu trouveras dans l'onglet Formules du ruban^(*). Dans ton cas, il t'aurait permis de comprendre rapidement ce qui clochait. N'hésite pas à l'utiliser ;)

Pièce jointe 604446

Cet outil te permet de réaliser la formule pas-à-pas pour en décortiquer les étapes et voir quelles valeurs sont manipulées au fur et à mesure de l'exécution du calcul...

Pièce jointe 604447

^* Je m'en sers tellement souvent que je l'ai placé sur la barre d'outils d'accès rapide... Pièce jointe 604448