[RESOLU] Formules, intersection de lignes et de colonnes, compteurs

Orodreth

XLDnaute Impliqué
Bonjour le forum,

J'ai un problème depuis ce matin que je n'arrive pas à résoudre. Je dois pas être très loin, mais les formules ne sont pas mon domaine de prédilection.

Petit descriptif:
J'ai un classeur qui comprend 3 feuilles (dans la version exemple).
- Sys_Address_Preop: Tableau qui me sort à définir les plages de lignes des numéros de série de mes données.
A noter: la première ligne me sert à définir les coordonnées du tableau (Ligne/Colonne de début, Ligne/Colonne de fin) d'adressage.

- Prog.: Tableau de planning qui contient le planning de mes données (planning sur lequel je m'appuie pour établir des chiffres). Il est divisé en semaine par année, et on distingue Activité, Numéro de Série, Numéro d'Engin.

- P.Ch: tableau d'indicateurs qui compte le nombre d'interventions d'un certain sur un intervalle de temps donné.
Ce tableau est annuel, et fait les distinctions mois par mois.

Première problématique: comment faire le lien entre le planning par semaine et les indicateurs par mois.
Pour répondre à cette question, j'ai décidé de nommer des plages de cellules.

Ainsi, les colonnes E à I dans le planning sont nommées "janvier2013" puisqu'elles correspondent à la période de janvier 2013.

Le principe me convient bien, mais je fais dès lors face à la deuxième problématique: compter les interventions par type, en fonction du croisement "lignes / colonnes".

J'avais trouvé, sous forme de formules, ce qui permettaient de définir l'intersection entre plages de lignes et plages de colonnes. C'est exactement ce qu'il me faut. En dynamique.

J'ai mis au point une formule (en plusieurs étapes) qui me permet de définir les différentes coordonnées ainsi que l'intervention recherchée.

Ci-dessous, la formule en question (que j'essaye de faire rentrer dans la cellule I11 de la feuille P.Ch):
Code:
=NB.SI("Prog.!$"&
          RECHERCHEV(CONCATENER($G11;" ";$F11);
                             INDIRECT("SYS_Address_Preop!" & SYS_Address_Preop!$D$1):INDIRECT("SYS_Address_Preop!"&SYS_Address_Preop!$F$1);
                             2;
                             FAUX
                            )
          &":$"&RECHERCHEV(CONCATENER($G11;" ";$F11);
                                     INDIRECT("SYS_Address_Preop!" & SYS_Address_Preop!$D$1):INDIRECT("SYS_Address_Preop!"&SYS_Address_Preop!$F$1);
                                     3;
                                     FAUX)&" "&
          INDIRECT(CONCATENER(I$10;$I$9));$H11)

Que font les RECHERCHEV:
- Ils recréent l'information à aller chercher (la concaténation)
- Ils déterminent la plage de recherche à l'aide des formules INDIRECT internes ET des informations en cellules D1 et F1 de la feuille SYS_Address_Preop
- Ils renvoient respectivement la ligne de début (pour le premier), et la ligne de fin (pour le second).
- Et bien sûr, pas de valeur approchante.

Que fait le dernier INDIRECT:
- Il est censé recréer la référence des colonnes qui correspondent, en fonction de 2 paramètres: le mois qui est en en-tête de colonne (Feuille P.Ch, cellule I10) et l'année qui est en titre du même tableau (Feuille P.Ch, cellule I9). Concrètement, la concaténation recrée le nom "moisAnnée" (exemple: janvier2013), lequel est utilisé pour référencer la plage correspondante et créer l'intersection.

Mais là, ça plante. J'ai notamment essayé de déplacer l'INDIRECT en question en début de formule (juste après le NB.SI) mais il me fait une erreur #REF.
Si je le laisse à la place indiquée plus haut (juste avant le dernier CONCATENER), Excel me dit que la formule a une erreur. Et c'est tout, pas plus d'informations, rien.
J'aimerais bien évaluer la formule pour savoir ce qui le gène, mais comme il refuse de valider la formule, je n'ai accès à aucun menu.

Je me tourne vers vous pour m'éclairer, si vous avez des solutions ou des pistes.

Merci d'avance,
Cordialement,
Orodreth

PS: ci-joint, le fichier exemple qui contient les 3 feuilles en question.
PS2: je sais, il y a plus simple à faire (c'est d'ailleurs ce qui est fait sur la feuille P.Ch) mais c'est justement pour faire évoluer et dynamiser ce système que je suis à mon boulot en ce moment.
PS3: désolé, je suis pas forcément très clair sur ce que je cherche à faire
PS4: je reviens, je vais demander des sous à Sony pour la pub que je viens de leur faire ... (Ok, je sors ==> [] )
 

Pièces jointes

  • Formule Intersection Lignes & Colonnes.xls
    208 KB · Affichages: 76
Dernière édition:

Orodreth

XLDnaute Impliqué
Re : Formules, intersection de lignes et de colonnes, compteurs

Bonjour,

Je fais remonter ce post, toujours pas de solution, mais j'ai identifié le problème.
En me basant sur ce qui est expliqué dans cette page web , j'avais compris que l'intersection de deux plages de cellules se traduit par:
Code:
=Plage1 Plage2
L'espace entre les deux plages représentant la notion d'intersection.

Je vous poste un classeur (juste fait pour illustrer l'intersection) pour décrire le problème qui survient.

J'ai 2 plages nommées de cellules:
- TabLigne qui prend toutes les cellules des lignes 6 à 10 de la Feuil1
- TabColonne qui prend toutes les cellules des colonnes F à I de la Feuil1

De fait, j'obtiens un tableau d'intersection, sur les cellules F6 à I10.

Dans ce tableau, je veux chercher une valeur renseignée (cellule B3), pour en faire le décompte dans la cellule A13, sachant que je détermine mes plages de cellules de manière dynamique à l'aide de la fonction CONCATENER, et des cellules B1 et C1 pour TabLigne, B2 et C2 pour TabColonne

Ainsi, la formule en A13 est la suivante:
Code:
=NB.SI(INDIRECT(CONCATENER(B1;C1) & " " & CONCATENER(B2;C2));B3)

Tant que je reste dans la zone F6 à I10 (bleu foncé), le compteur est bon, pas de soucis.
En revanche, si je rajoute des valeurs en dehors de la plage de croisement (F6:I10), tant que je suis toujours dans la plage du premier paramètre de l'intersection, il me le compte également.
(Exemple, si je rajoute la valeur cherchée dans les cellules E8 et L9, elles seront comptabilisées.)

Ce qui se traduit par "Compte toutes les valeurs dans la première plage, et pour la deuxième plage, seulement avec l'intersection".
Ce qui se réduit au final à la première plage.
Autrement dit, la formule:
Code:
=NB.SI(INDIRECT(CONCATENER(B1;C1) & " " & CONCATENER(B2;C2));B3)
est parfaitement équivalente à celle-ci:
Code:
=NB.SI(INDIRECT(CONCATENER(B1;C1)); B3)
et n'est donc pas une réelle intersection.

A noter que si on inverse les plages, le raisonnement est le même.

Quelqu'un saurait m'aider ? Ou m'indiquer une autre piste ?

Merci d'avance,
Cordialement,
Orodreth
 

Pièces jointes

  • Formula Compteur Intersection.xls
    14 KB · Affichages: 63

Orodreth

XLDnaute Impliqué
Re : Formules, intersection de lignes et de colonnes, compteurs

Fini par trouver.

Un peu tendu la formule, mais si elle intéresse quelqu'un:
Code:
=NB.SI(INDIRECT(ADRESSE(LIGNE(INDIRECT(B1&C1));COLONNE(INDIRECT(B2&C2))) & ":" & ADRESSE(LIGNE(INDIRECT(B1&C1))+LIGNES(INDIRECT(B1&C1))-1;COLONNE(INDIRECT(B2&C2))+COLONNES(INDIRECT(B2&C2))-1));B3)

En jouant avec la formule adresse, et en déterminant cellule de début de la plage d'intersection, et cellule de fin de la plage d'intersection, on arrive à obtenir l'intersection seule.

Cordialement,
Orodreth
 

Discussions similaires

Réponses
14
Affichages
639
Réponses
3
Affichages
147

Statistiques des forums

Discussions
311 720
Messages
2 081 909
Membres
101 836
dernier inscrit
karmon