Fonction excel classique sur plage variable

Fermé

Formule

mohshadow Messages postés 2742 Date d'inscription mardi 30 août 2011 Statut Membre Dernière intervention 12 novembre 2018 - 18 déc. 2017 à 12:25
mohshadow Messages postés 2742 Date d'inscription mardi 30 août 2011 Statut Membre Dernière intervention 12 novembre 2018 - 20 déc. 2017 à 10:16

Bonjour à tous,
J'ai une sacrée colle que je soumets à vos mains expertes... Je n'ai pas de connaissance VBA mais je ne me débrouille pas trop mal avec les fonctions excel.
Dans mon exemple j'ai besoin d'utiliser des fonctions classiques type nbval ou nb.si mais sur une plage variable.... et là ca se complique.
J'ai tenté de rationaliser au maximum la problématique dans mon exemple ci-joint:
https://www.cjoint.com/c/GLsljmDxBSc

Je souhaiterai automatiser les résultats présents dans les colonnes "E" et "F".
1/ Dans la colonne "E" je souhaite compter pour chaque "panier" (qui se trouvent toujours au niveau 6) le nombre de fruits au niveau 7. Donc pour le panier1 {=nbval(B4:B13);7} sauf que cette valeur B13 est variable... elle est dépendante de la présence du panier2 en B14....
2/ Dans la colonne "F" c'est le nombre de fruits total incluant leur quantité en D:D
3/ Jusque là c'était trop simple alors pour corser le tout, je ne dois pas recompter un type de fruit déjà présent dans un panier précédent. C'est pourquoi dans mon panier2 je ne souhaite comptabiliser QUE le type abricot puisque le type pomme ET le type poire est déjà connu dans le panier1...

J'espère avoir été assez clair dans mes explications.
Merci de votre aide.

A voir également:

Formule excel plage de données variable
Frédéric cherche à faire le buzz sur les réseaux sociaux. il a ajouté une image de manchots sur une image de plage. retrouvez l'image originale de la plage. que cachent les manchots ? ✓ - Forum Windows
Formule excel - Guide
Liste déroulante excel - Guide
Formule moyenne excel - Guide
Formule excel si et - Guide

7 réponses

Réponse 1 / 7

Mike-31 Messages postés 18313 Date d'inscription dimanche 17 février 2008 Statut Contributeur Dernière intervention 21 avril 2024 5 073
18 déc. 2017 à 13:15

Bonjour,

tu peux essayer d’harmoniser tes dires et ton exemple s'il te plait
1/ Dans la colonne "E" je souhaite compter pour chaque "panier" (qui se trouvent toujours au niveau 6) et dans ton fichier exemple je comprends compter les niveau 7
s'il s'agit de compter les niveaux 7 pour le panier 1 j'en compte bien 4 mais pour le panier 2 j'en compte 3 pas 1 et pour le panier 3 j'en compte 2 pas 1

colonne F tu as 6, 6 et 2 je ne vois pas à quoi cela correspond

reprend ton fichier et donne plus d'explications avant de le reposter

mohshadow Messages postés 2742 Date d'inscription mardi 30 août 2011 Statut Membre Dernière intervention 12 novembre 2018 1 678
Modifié le 18 déc. 2017 à 14:23

Merci de ton retour. Je vais tacher d'être plus clair.
"s'il s'agit de compter les niveaux 7 pour le panier 1 j'en compte bien 4" >> On est d'accord: ici 4 correspond au nombre de niveau 7 sous le panier1 (soit pomme, banane,poire, cerise)

"mais pour le panier 2 j'en compte 3 pas 1 et pour le panier 3 j'en compte 2 pas 1" C'est exact sauf que j'ai mon hypothèse 3/ dans mon laius de départ. Donc pour mon panier2, alors que je devrai, comme tu le dis, avoir 3, moi je veux que mon résultat soit 1 car mes items "pommes" et "poire" on été comptabilisés dans mon panier1, je ne compte donc que l'item abricot. Idem dans le panier3 ou je ne compte que 1 et non 2 car mon item "pomme" est déjà connu.

Afin de simplifier la donne, je propose dans un premier temps d'"oublier" cette colonne F, ainsi que les items déjà comptabilisés cf hypothèse 3/

Donc comment comptabiliser ces "Niveau 7" éventuellement avec un nb.si en faisant varier la plage en fonction du panier suivant.

Réponse 2 / 7

Mike-31 Messages postés 18313 Date d'inscription dimanche 17 février 2008 Statut Contributeur Dernière intervention 21 avril 2024 5 073
Modifié le 18 déc. 2017 à 17:03

Re,

ta demande est complexe qui mériterait être traitée par VBA mais avec une colonne intermédiaire il est possible d'y arriver
j'ai travaillé sur ton premier fichier
exemple en colonne H qui pourra par la suite être masquée
en cellule H1 je mettrais cette formule matricielle qu'il faudra confirmer en cliquant en même temps sur 3 touches du clavier Ctrl, Shift et Entrée et si tu fais bien la formule se placera entre ces accolades {}
cette formule te donnera l'adresse de la dernière cellule non vide colonne C
=ADRESSE(MAX(LIGNE(C:C)*(C:C<>""));3;4)
ensuite en H4 cette simple formule qui te donnera la dernière adresse de chaque série
=SI(NB.SI(C4;"*panier*")=0;"";ADRESSE(SOMMEPROD((INDIRECT("C4:"&$H$1)=C4)*LIGNE(INDIRECT("C4:"&$H$1)));2;4))
En cellule E4 cette formule
=SI(H4="";"";NB.SI(INDIRECT("B4:"&SIERREUR(ADRESSE(SOMMEPROD((C4:INDIRECT($H$1)=GAUCHE(C4;6)&DROITE(C4;NBCAR(C4)-6)+1)*LIGNE(C4:INDIRECT($H$1)));3;4);$H$1));7)-SOMME(E$3:E3))
te donnera en E4 4 en E14 3 et E21 2
A+
Mike-31

Je suis responsable de ce que je dis, pas de ce que tu comprends...

Réponse 3 / 7

JvDo Messages postés 1978 Date d'inscription mercredi 27 juillet 2005 Statut Membre Dernière intervention 28 septembre 2020 856
Modifié le 18 déc. 2017 à 18:47

Bonsoir à tous,

moi aussi j'ai travaillé sur ton premier fichier.

tu as 2 solutions dans le fichier joint https://www.cjoint.com/c/GLsrNFqrCe4 :
dans la feuille "avec colonnes", c'est simple mais il faut créer 2 colonnes de calculs intermédiaires. (les formules sont dans les cellules vertes)
dans la feuille "sans colonnes", tu n'as plus besoin de calculs intermédiaires mais les formules se compliquent. (les formules sont dans les cellules bleues)

en terme de maintenance, je te conseille la 1ère.

Pour tes plages dynamiques, tu n'as que les noms globaux des colonnes de données et de celles des calculs intermédiaires le cas échéant à définir.
ça peut s'automatiser si nécessaire.

Pour les plages dynamiques des paniers, les formules s'en chargent.

il y a peut-être plus simple, mais je ne sais pas faire autrement.

cordialement

mohshadow Messages postés 2742 Date d'inscription mardi 30 août 2011 Statut Membre Dernière intervention 12 novembre 2018 1 678
Modifié le 19 déc. 2017 à 10:55

Petite question pour toi JvDo. Dans la colonne A:A que tu appelles "panier", la formule que tu as entré est: =SI(GAUCHE(D4;6)="panier";--SUBSTITUE(D4;"panier";"");A3)
Je comprends la fonction mais je ne comprends pas pourquoi elle te retourne la valeur "1"... ou "2"... ou "x"...
Je ne comprends pas non plus la syntaxe du double tiret devant "SUBSTITUE"

Désolé, de ces petites questions qui arriveront probablement au compte goutte, mais je ne souhaite pas démouler la solution en one shot, mais la comprendre afin de pouvoir l'exploiter :)
J'aurai en plus forcément des adaptations, car tu l'as compris dans j'ai utilisé des panier et des fruits en exemple pour simplifier :)

EDIT:... rhooo... je suis con... J'ai capté. Tu utilises le dernier caractère de la chaine "panier1" donc "1". Bon par contre ca m'arrange pas du coup, mais c'est de ma faute, c'est moi qui ai voulu rendre l'exemple plus compréhensible en rajoutant une incrémentation numérique. En réalité, j'ai toujours une valeur fixe, ce n'est pas "panier1", "panier2", mais "panier" simplement.
Ceci étant dit, je doit pouvoir utiliser cette logique pour identifier mes différents paniers.
Je continues donc... :)

JvDo Messages postés 1978 Date d'inscription mercredi 27 juillet 2005 Statut Membre Dernière intervention 28 septembre 2020 856
19 déc. 2017 à 14:16

Bonjour,

Si tu n'as que "panier" dans la colonne Type, c'est plus simple :

feuille avec colonnes : tu mets la formule =NB.SI($D$4:D4;"panier") en A4 et tu recopies vers le bas.

feuille sans colonnes : tu remplaces GAUCHE(Type;6)="panier" par Type="panier" dans les cellules bleues.
tu valides par CTRL+SHIFT+ENTER parce c'est une formule matricielle.

cordialement

mohshadow Messages postés 2742 Date d'inscription mardi 30 août 2011 Statut Membre Dernière intervention 12 novembre 2018 1 678
Modifié le 20 déc. 2017 à 10:18

Merci c'est en effet à peu près comme ca que j'ai fonctionné avec quelques variantes pour palier à mes besoins. J'avoue ne m'être penché que sur l'onglet avec colonne qui m'a parlé avec mon niveau excel :)
L'onglet 2 est trop complexe pour moi, n'ayant aucune notion de formules matricielles.

En fait pour entrer un peu dans les détails dans mes données réelles, j'ai un ou plusieur "type" de niveau supérieur à mes "paniers" que je veux ignorer (mettre une valeur 0) dans la colonne (paniier). (Ma donnée d'entrée est une extraction d'une descente d'arborescence) de niveau 1 à 12.

J'ai donc utilisé:
=SI(C4<6;0;SI(D4="panier";MAX(A$3:A3)+1;A3))

Je verrais quand même car cette fonction semble ne pas être franchement optimisé pour le calcul de mon PC. Quand je lui file mes 120 000 lignes de données d'entrée il couine un peu ... enfin un bon moment. ;)

Réponse 5 / 7

Jauster Messages postés 126 Date d'inscription jeudi 7 décembre 2017 Statut Membre Dernière intervention 31 août 2018 40
18 déc. 2017 à 15:34

Bonjour,

Comment votre plage change-t-elle ? Ajoutez vous manuellement les lignes pour ajouter un fruit au panier ?

mohshadow Messages postés 2742 Date d'inscription mardi 30 août 2011 Statut Membre Dernière intervention 12 novembre 2018 1 678
Modifié le 18 déc. 2017 à 15:46

Tout ceci est issu d'une extraction de base de données effectuée chaque semaine. Donc oui potentiellement des lignes (donc des fruits) peuvent s'intercaler.
Il s'agit ici juste d'un exemple, dans l'extraction de base je compte plus de 800 paniers.

Discussions similaires

Plage de données variable dans formule Excel

définition d'une plage variable sous excel

Plage données variable - Insertion ds formule

si plage contient du texte alors ....

Formule Excel : "SI" une cellule contient un mot, alors ?

[Excel] Vérifier existence cellules colonne A dans colonne C

Discussions similaires

Newsletters