Problème avec formules nb.si.ens et sommeprod

Bonjour à tous,

J'ai un souci avec les formules NB.SI.ENS et SOMMEPROD. Le contexte général c'est que j'ai un tableau de données brutes qui correspondent à des enregistrements d'ultrasons de chauves-souris où chaque ligne correspond à un enregistrement de 5s. Ma 1ère colonne (pour celles qui nous intéressent du moins) désigne la saison (Transits printemps, Mise-bas ou Transits automnaux), ensuite j'ai une colonne "vérification" qui contient le nom des espèces et des colonnes qui concernent le temps (Heure, Minute, Temps => qui contient l'heure au format hh:mm).

Ce que je cherche à faire avec tout ça c'est de calculer le nombre de valeurs (lignes) pour lesquelles pour une période donnée l'heure est comprise entre 2 valeurs de temps désignées par des cellules. En fait dans une nouvelle feuille de calcul j'ai crée un tableau où j'ai mes heures avec un pas de temps de 10 min et je souhaite que mon nombre de valeurs correspondantes s'affiche en dessous.

Mes colonnes de base sont les suivantes : A = Période; J = Vérification; M = Heure; O = Minute et Q = Temps

Mes cellules sont : B83 = 21h15 ; C83 = 21h25 etc jusqu'à 6h15

B81 =Heure(B83) ; B82 =Minute(B83) ; C81 =Heure(C83) ; C82 =Minute(C83)

J'ai testé plusieurs formules mais aucune ne fonctionne pleinement.

=NB.SI.ENS('Données brutes'!$A$2:$A$28726;"Transits automnaux";'Données brutes'!$Q$2:$Q$28726;">="&B83;'Données brutes'!$Q$2:$Q$28726;"<"&C83)

ou

{=SOMMEPROD(('Données brutes'!$A$2:$A$30000="Transits automnaux")*('Données brutes'!$N$2:$N$30000>=B81)*('Données brutes'!$O$2:$O$30000>=B82)*('Données brutes'!$N$2:$N$30000<=C81)*('Données brutes'!$O$2:$O$30000<C82))}

Le problème que je rencontre avec c'est formules c'est que la 1ère s'arrête à 00:00 et m'affiche 0 comme valeur après cela et avec sommeprod, le souci vient du fait que ça m'affiche 0 à chaque changement d'heure, ce qui fait que j'ai des trous à chaque passage d'heure...

J'espère que j'ai été clair sur mon problème et le détail de mes tableau. Si vous avec une solution à me donner, que ce soit en excel classique ou VBA, je suis preneur.

Merci d'avance

PS : j'ai mis mon fichier en pièce jointe. Le tableau qui me pose problème est dans la feuille "TA" à partir de la ligne 75

Bonjour,

tu as oublié le fichier

P.

Oh mince alors, j'ai essayé 4-5 de joindre mon fichier mais ça ne semble pas fonctionner

Affaire de taille/ poids ?

Pensez à joindre un fichier pour faciliter la compréhension du problème et augmenter les chances de vous faire aider. La taille maximale autorisée est de : 500ko (si vous utilisez la version 2007 d'Excel ou une version plus récente, préférez les formats ".xlsx" ou ".xlsm" pour réduire la taille du fichier).

Voici le bon fichier. Pardon j'avais supprimé trop de données pour que le fichier atteigne les 500ko

Bonjour encore

rien de certain (à tester donc avec les filtres dans la base) mais j'ai des chiffres

j'ai essayé ceci (en B85) mais en nomnant les colonne du tableau de base de données ****

j'ai choisi "mise-bas" dans la cellule "A86" de la même feuille

=SOMMEPROD((Période=$A$86)*(Heure>=B81)*(Minute>=B82)*(Heure<=C81)*(Minute<=C82))

**** CTRL* CTRL SHIFT F3 / ligne du haut , ça permet de mettre des noms au lieu des références style $A$2:$A$9150

P.

10greg014-xlp.xlsm (493.55 Ko)

Re-Bonjour Patrick,

Merci beaucoup pour ton test mais une fois encore il y a le même problème, à chaque passage d'une heure à l'autre, par exemple de 23h55 à 00h05 ou 1h55 à 2h05, ça me met 0 alors que lorsque l'on filtre les données brutes pour vérifier, on trouve des lignes.

Regarde avec le dernier fichier que j'ai envoyé, il y a plus de lignes pour la période Transits automnaux, ce qui permet de mieux se rendre compte.

Merci quand même

Greg014 a écrit :

Re-Bonjour Patrick,

Merci beaucoup pour ton test mais une fois encore il y a le même problème, à chaque passage d'une heure à l'autre, par exemple de 23h55 à 00h05 ou 1h55 à 2h05, ça me met 0 alors que lorsque l'on filtre les données brutes pour vérifier, on trouve des lignes.

Regarde avec le dernier fichier que j'ai envoyé, il y a plus de lignes pour la période Transits automnaux, ce qui permet de mieux se rendre compte.

Merci quand même

Je n'ai pas de 23h55 pour "transits automnaux" ...

Si je filtre sur Heure : 1 Minute: 5-6-7 j'ai 7 enregistrements filtrés et 7 dans le tableau du bas , juste ou pas ?

Mais je suis peut être dans la panade

Tu as nommé tes colonnes pour la facilité ?

Re Patrick,

J'ai trouvé d'où venait mon incompréhension, étant donné que j'avais rajouté des lignes (le nombre n'est jamais fixe) et que tu avais défini les noms sur 92 lignes, le compte que j'avais ne pouvais pas être juste.

J'ai modifié la plage de gestion des noms à 30 000 lignes histoire d'avoir de la marge mais ta formule fonctionne comme la formule sommeprod que j'avais donné dans le 1er message. Ça me met 0 à chaque intervalle de passage d'heure, c'est vraiment bizarre.

Alors....

je passe la main

P.

Ça marche, en tout cas un grand merci pour avoir essayé

Bonjour,

pas regardé en détail mais il y a une logique.

Dans un cas tu testes 22:15 <= x < 22:25, ça va.

et dans l'autre 23:55 <= x < 00:05 et forcément il ne peut pas y en avoir.

Fait +1 sur les heures du matin (< 12:00) qu'elles soient considérées étant le lendemain et tes tests redeviendront corrects.

eric

Bonjour Eric,

Je ne vois pas bien ce que tu veux dire par faire +1 sur les heures du matin, tu peux m'expliquer stp.

Merci

Bonjour,

Pour excel 1= 1 jour, les heures étant des fractions de jour.

6:00 = 0.25 (visible si tu remets la cellule au format Standard).

23:55 = 0.996527778

00:05 = 0.003472222

Toi tu voudrais 23:55 < 00:05 soit 0.996527778 < 0.003472222 ce qui sera toujours faux.

Par contre 0.996527778 < 1.003472222 est vrai, 1.003472222 correspond à 00:05 du lendemain (hh:mm+1).

Donc pour toutes les heures inférieures à midi additionne 1 qu'elles soient considérées du lendemain et non du matin de la même journée. Ca ne te poserait un problème que si les pipistrelles volaient de 11:55 à 12:05, ce qui n'est pas pour demain.

Un tableau pour te faire comprendre.

eric

5classeur1.xlsx (12.36 Ko)

Re-Bonjour Eric,

J'ai bien compris ce que tu m'as expliqué et j'avais pensé au même problème (même si je ne savais pas comment faire )

Par contre j'ai essayé et mes heures entre 00:00 et 6:15 sont bien en 1,....... mais ça ne marche pas pour autant. Je ne sais vraiment pas ce qui pose problème puisque par le passé j'avais fait quelque chose d'approchant et ça fonctionnait parfaitement.

Si tu as une autre idée n'hésite pas

Merci tout de même, c'était bien vu

bonjour,

Sinon il y a les BD** !!

Les BD** sont particulièrement à l'aise sur les très grandes bases de données :

Pas de formules compliquées,

Bien plus rapides que les matricielles et sans validations compliquées.

Ne nécessitent pas de conversions particulières pour les dates et/ou les heures.

Aisément adaptable par macros...

Indispensable à tous ceux qui manipulent fréquemment de grandes quantités de données !

A+

C'est pourtant simple : aujourd'hui est le hier de demain !

Je t'ai fait une colonne Données brutes!S:S et repris les lignes TA!83:84 pour t'expliquer.

Sur le même principe (aujourd'hui+1 = demain) tu pourrais t'ajouter à la place une colonne qui t'indique directement la tranche à laquelle appartient ton jour+heure avec l'avantage de pouvoir faire des TCD.

Comme dit galopin, plus de formule (compliquée ou pas)

Ajout colonne T (je n'ai pas repris TA en utilisant cette données, je pense que tu trouveras tout seul. Ca simplifie)

Mais qui ont l'inconvénient de démarrer la journée à 0:00, et que tu doives ajouter une série de données vide avec juste toutes les tranches horaires si tu veux qu'elles apparaissent toutes (pas fait).

Je t'ai fait un exemple sans fignoler, les graphiques ce n'est pas ma tasse de thé.

eric

fichier zippé, ça ne passait plus

Rechercher des sujets similaires à "probleme formules ens sommeprod"