Fonction excel classique sur plage variable

Formule

mohshadow Messages postés 2742 Date d'inscription Statut Membre Dernière intervention -
mohshadow Messages postés 2742 Date d'inscription Statut Membre Dernière intervention - 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.

Afficher la suite

A voir également:

Excel définir une plage de données variable
Liste déroulante excel - Guide
Déplacer une colonne excel - Guide
Trier des données excel - Guide
Word et excel gratuit - Guide
Image de manchots sur une image de plage. - Forum Graphisme

7 réponses

Réponse 1 / 7

Mike-31 Messages postés 18407 Date d'inscription Statut Contributeur Dernière intervention 5 135

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 Statut Membre Dernière intervention 1 681

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 18407 Date d'inscription Statut Contributeur Dernière intervention 5 135

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 Statut Membre Dernière intervention 859

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 Statut Membre Dernière intervention 1 681

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 Statut Membre Dernière intervention 859

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 Statut Membre Dernière intervention 1 681

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 Statut Membre Dernière intervention 41

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 Statut Membre Dernière intervention 1 681

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

Incrémentation formule avec des Noms de cellule

Calcul Date d'échéance(date) : date initiale (date)+mois(nombre)

Si valeur dans colonne A afficher contenu même ligne col B

Excel: Si contient *partie texte1* OU *partie de texte2 alors...

Formule Excel SI Cellule contient mots

Si une celule contient un texte ; alors ; sinon calculer ?

Votre réponse

Discussions similaires