XL 2016 formule SOMMEPROD sur deux onglets

casmaran · 9 Août 2021

Bonjour à tous,
j'ai une formule sur une page qui me permet de compter des heures de vols sur douze mois en arrière à partir de 'AUJOURDHUI' :

=SOMMEPROD((M6:M38)*(A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))

cette formule fonctionne bien mais en ajoutant un onglet je voudrais que le calcul se fasse sur les deux onglets, et là je sèche pour trouver la formule malgré une recherche sur le forum.

Je joins un extrait de mon fichier, la formule est en cellule H42.
Merci par avance de votre aide.

vgendron · 10 Août 2021

Bizarre..
je te le remets ici

casmaran · 10 Août 2021

vgendron à dit:
Bizarre..
je te le remets ici

Désolé il était bien arrivé mais quad je cliquais dessus il partait dans mes téléchargements au lieu de s'ouvrir directement ! je vais regarder pour voir comment je peux l'exploiter.

casmaran · 10 Août 2021

J'ai regardé le fichier et la recap, je vais avoir du mal pour faire la synthèse de tout ça et réunir les données dans une seule formule, je te tiens au courant.

vgendron · 10 Août 2021

casmaran à dit:
J'ai regardé le fichier et la recap, je vais avoir du mal pour faire la synthèse de tout ça et réunir les données dans une seule formule, je te tiens au courant.

L'ennui, c'est que la formule va dépendre du nombre de pages que tu as dans le classeur
et va dépendre de la période désirée: j'ai vu dans tes formules que tu cherches aussi sur une période de 6 mois..

et pas macro? est ce que ca pourrait convenir??
avec un formulaire sur lequel tu selectionnes la période souhaitée (date de début et date de fin)
tu peux aussi ajouter un critère de type d'aéronef, un critère de "c" ou "EP

tu valides et la macro regarde dans toutes les pages et te donne le résultat (qu'on peut ensuite ecrire quelque part dans l'onglet Recap. par exemple?
je regarde ca demain

vgendron · 11 Août 2021

Hello

Voici ce que je me suis amusé à faire
Cliques sur le bouton dans la feuille Recap
PS: la selection de la fonction à bord ne change rien: ce n'est pas pris en compte pour le moment

vgendron · 11 Août 2021

Re..
ci joint une mise à jour pour pouvoir selectionner la fonction

quelques modifs dans ton fichier

1) feuille Datalist
permet de créer des listes prédéfinies
type aéronef
fonction à bord
Immatriculation
ces listes sont utilisées en "data validation" pour remplir les colonnes G - I et H des feuilles "PageXX"

2) si j'ai bien compris, lorsque tu es en fonction C: le total des heures est mis dans la colonne N
si tu es en fonction EP, c'est mis dans la colonne M
==> j'ai vu que tu avais supprimé la formule en N20 et mis en M20: je suppose que tu fais ca à chaque fois que tu as EP

j'ai modifié les formules en M et N pour que le calcul se fasse directement, sans que tu aies besoin "d'ajuster " à la main..

3) sur le formulaire:
tu mets les deux dates de la période (soit à la main, soit en cliquant sur les boutons Aujourd'hui et -12mois - 6mois)
tu selectionnes ou pas un aeronef
tu selectionnes ou pas un type de fonction
tu cliques sur calculer ==> le résultat apparait dans la boite "total calculé"

si Aeronef et type de fonction ne sont pas selectionnées, le calcul prend en compte tous les aeronef et toutes les fonctions

dans le cas d'une fonction EP: ca prend les heures de la colonne M
dans le cas d'une fonction C: ca prend les heures colonne N
si PAS de fonction: ca prend QUE la colonne N (donc. comme fonction C) ==> à rectifier je suppose ??

casmaran · 11 Août 2021

ERRATUM ! je disais que la période 12 mois où 365 jrs commençait à partir du dernier vol mais c'est inexact, la période doit commencer au jour J actuel d'où l'importance d'avoir dans la formule AUJOURDHUI-12 mois ou AUJOURDHUI-365 jours. En supposant qu'il n'y ai pas eu d'enregistrement pendant 1 an, le résultat de la formule doit être de 00h00 heure, ce qui se vérifie si par exemple on sélectionne les colonnes Y et Z (vols de nuit pour lesquelles il n'y a pas eu d'heures enregistrées) au lieu de M et N.

Le calcul sur 6 mois ou 3 mois n'est pas important, c'était juste pour comparer avec les 12 mois la cohérence des totaux de la formule.
De même que la formule ne doit apparaître en réalité que sur la dernière page, je l'avais laissé sur la page 32 car la page 33 n'a été ajoutée que 06/08/2021, mais les résultats en I42 et I43 doivent bien prendre en compte les deux dernières pages.

Le calcul par type d'avion est intéressant mais je l'ai fait par ailleurs dans le fichier (que tu ne peux pas voir dans le fichier réduit que j'ai transmis).
Je pourrai te l'envoyer en entier mais apparemment ce n'ai pas recommandé sur le site.

En résumé je pourrai me contenter des formules suivantes en faisant abstraction de l'écart des 16 mn,

pour I42
=SOMMEPROD((Page32!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38)+SOMMEPROD((Page33!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38),
pour I43 qui ne prend que la colonne N
=SOMMEPROD((Page32!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!N6:N38)+SOMMEPROD((Page33!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!N6:N38).

casmaran · 11 Août 2021

casmaran à dit:
ERRATUM ! je disais que la période 12 mois où 365 jrs commençait à partir du dernier vol mais c'est inexact, la période doit commencer au jour J actuel d'où l'importance d'avoir dans la formule AUJOURDHUI-12 mois ou AUJOURDHUI-365 jours. En supposant qu'il n'y ai pas eu d'enregistrement pendant 1 an, le résultat de la formule doit être de 00h00 heure, ce qui se vérifie si par exemple on sélectionne les colonnes Y et Z (vols de nuit pour lesquelles il n'y a pas eu d'heures enregistrées) au lieu de M et N.

Le calcul sur 6 mois ou 3 mois n'est pas important, c'était juste pour comparer avec les 12 mois la cohérence des totaux de la formule.
De même que la formule ne doit apparaître en réalité que sur la dernière page, je l'avais laissé sur la page 32 car la page 33 n'a été ajoutée que 06/08/2021, mais les résultats en I42 et I43 doivent bien prendre en compte les deux dernières pages.

Le calcul par type d'avion est intéressant mais je l'ai fait par ailleurs dans le fichier (que tu ne peux pas voir dans le fichier réduit que j'ai transmis).
Je pourrai te l'envoyer en entier mais apparemment ce n'ai pas recommandé sur le site.

En résumé je pourrai me contenter des formules suivantes en faisant abstraction de l'écart des 16 mn,

pour I42
=SOMMEPROD((Page32!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38)+SOMMEPROD((Page33!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38),
pour I43 qui ne prend que la colonne N
=SOMMEPROD((Page32!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!N6:N38)+SOMMEPROD((Page33!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!N6:N38).

j'avais pas vu ton dernier message
je rentre à la main les fonctions en colonne M les heures comme élève pilote, en colonne N les heures comme commandant de bord, aucune autre considération n'est prise en compte C E G H I (la colonne C n'est pas une fonction, c'est la référence de l'aérodrome). Pour le cas présent je ne dois prendre en compte que les colonnes M et N; par la suite j'aurai peut-être besoin de rajouter les colonnes O et P (vols de nuit).

vgendron · 11 Août 2021

Hello

dans la PJ quelques nouveautés (je suis parti sur du formulaire)
1) Création d'une feuille "PageXX" qui sert de modèle vierge
lorsque ta dernière page est terminée, tu cliques sur le bouton "Nouvelle Page"
==> la macro détecte quelle est la dernière page (indice le plus fort ==>ici 33)
elle crée une nouvelle feuille avec l'indice supérieur ici 34
les valeurs de la M38:AC38 de la page 33 sont reportées automatiquement sur la ligne 5 de la nouvelle page

2) sur la feuille "Recap", j'ai repris ton tableau de "synthèse" présent sur chaque feuille sous les lignes de vol

lorsque tu lances le formulaire
tu selectionnes les dates de début et fin de période
DateMin = la toute première date trouvée dans toutes les pages
DateMax= la toute dernière date trouvée dans toutes les pages
aujourdhui..
-12mois = date de fin de période -12 mois
-6mois...

lorsque tu fais calculer
les dates de début et fin période sont reportées en K1 et M1 de la feuille Recap
le tableau de synthèse est rempli POUR la période sélectionnée

certaines lignes ne sont pas remplies, parce que je n'ai pas trouvé ce que tu calculais.

sur le formulaire lui meme, tu peux toujours ajouter des filtres sur le type d'aéronef et la fonction à bord
ces filtres sont utilisés pour le calcul qui apparait dans la boite "Total Calculé" sur le formulaire
==> ce calcul n'est pas enregistré sur la feuille recap (si besoin, on pourrait)

Wayki · 11 Août 2021

casmaran à dit:
ERRATUM ! je disais que la période 12 mois où 365 jrs commençait à partir du dernier vol mais c'est inexact, la période doit commencer au jour J actuel d'où l'importance d'avoir dans la formule AUJOURDHUI-12 mois ou AUJOURDHUI-365 jours. En supposant qu'il n'y ai pas eu d'enregistrement pendant 1 an, le résultat de la formule doit être de 00h00 heure, ce qui se vérifie si par exemple on sélectionne les colonnes Y et Z (vols de nuit pour lesquelles il n'y a pas eu d'heures enregistrées) au lieu de M et N.

Le calcul sur 6 mois ou 3 mois n'est pas important, c'était juste pour comparer avec les 12 mois la cohérence des totaux de la formule.
De même que la formule ne doit apparaître en réalité que sur la dernière page, je l'avais laissé sur la page 32 car la page 33 n'a été ajoutée que 06/08/2021, mais les résultats en I42 et I43 doivent bien prendre en compte les deux dernières pages.

Le calcul par type d'avion est intéressant mais je l'ai fait par ailleurs dans le fichier (que tu ne peux pas voir dans le fichier réduit que j'ai transmis).
Je pourrai te l'envoyer en entier mais apparemment ce n'ai pas recommandé sur le site.

En résumé je pourrai me contenter des formules suivantes en faisant abstraction de l'écart des 16 mn,

pour I42
=SOMMEPROD((Page32!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38)+SOMMEPROD((Page33!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!M6:N38),
pour I43 qui ne prend que la colonne N
=SOMMEPROD((Page32!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!N6:N38)+SOMMEPROD((Page33!B6:B38>=TEXTE(AUJOURDHUI()-365;"aaaamm"))*Page32!N6:N38).

Je trouvais bizarre aussi que tu veuille prendre le mois m-12 au complet mais soit.
Tu peux supprimer la colonne B et marquer la formule comme ça pour alléger :
=SOMMEPROD((Page32!A6:A38>=AUJOURDHUI()-365)*Page32!M6:N38), etc etc
A +

vgendron · 12 Août 2021

Wayki à dit:
=SOMMEPROD((Page32!A6:A38>=AUJOURDHUI()-365)*Page32!M6:N38), etc etc

Hello

On est quand meme d'accord que cette formule est quasi impossible à utiliser..
dans le fichier exemple.. on a 2 pages (32 et 33).. mais si j'ai bien compris.. le fichier original en contient.. 33
je serais prêt à parier qu'une formule à base de 33 sommeprod ne sera pas acceptée par Excel

la solution eut été (parce que ca me fait marrer d'écrire ca comme ca ) de faire une formule avec UN sommeprod + le report de la feuille précédente..
sauf que ce report... n'est pas forcément dans la bonne période....

Wayki · 12 Août 2021

Ouais c'est fastidieux, mais le message de base était d'additionner les plages que de 2 feuilles selon un ou des critère, je m'en suis arrêté là aha.
Peut être comme tu dis se contenter de faire 1 sommeprod et additionner au total de la feuille précédente, à essayer
A +

casmaran · 12 Août 2021

Wayki à dit:
Ouais c'est fastidieux, mais le message de base était d'additionner les plages que de 2 feuilles selon un ou des critère, je m'en suis arrêté là aha.
Peut être comme tu dis se contenter de faire 1 sommeprod et additionner au total de la feuille précédente, à essayer
A +

j'ai appliquer ta formule en supprimant la colonne B, je n'avais pas un bon résultat avec -365, j'ai donc rajouté MOIS.DECALER et passé la période à -12 et miracle..... ça marche, et en plus je n'ai plus cette erreur de 16 minutes supplémentaire, donc c'est super top !!! voici la formule qui finalement est relativement simple:
=SOMMEPROD((Page32!L6:M38)*(Page32!A6:A38>=MOIS.DECALER(AUJOURDHUI();-12)))+SOMMEPROD((Page33!L6:M38)*(Page33!A6:A38>=MOIS.DECALER(AUJOURDHUI();-12))).

Merci à vous vgendron et Wayki, vous êtes super ! je clôturerai la discussion dans 1 ou 2 jours.
Bonne continuation

XL 2016 formule SOMMEPROD sur deux onglets

casmaran

XLDnaute Nouveau

Pièces jointes

vgendron

XLDnaute Barbatruc

Pièces jointes

casmaran

XLDnaute Nouveau

casmaran

XLDnaute Nouveau

vgendron

XLDnaute Barbatruc

vgendron

XLDnaute Barbatruc

Pièces jointes

vgendron

XLDnaute Barbatruc

Pièces jointes

casmaran

XLDnaute Nouveau

casmaran

XLDnaute Nouveau

vgendron

XLDnaute Barbatruc

Pièces jointes

Wayki

XLDnaute Impliqué

vgendron

XLDnaute Barbatruc

Wayki

XLDnaute Impliqué

casmaran

XLDnaute Nouveau

Discussions similaires