Bonsoir ericcool,

Il me semble qu'une simple protection de la feuille pourrait convenir, en décochant toutes les cases pour interdire toute intervention, l'onglet sera ainsi consultable, mais protégé de toute modification malencontreuse.

Pour Excel 2003: Menu Outils/Protection/Protéger la feuille

Très bonne soirée,

mth

Bonjour tokren, bonjour le forum,

Merci tokren pour ton MP, ceci dit il est mieux de déposer les réponses sur le forum.

Du coup je me permets de passer moi même ton sujet en "résolu"

La fonction RECHERCHEV() fonctionne très bien dans ce cas là, mais elle ne te donne que le premier résultat trouvé, pas les suivants.

Je suis ravie que tu aies pu résoudre ton problème, très bonne soirée et à une prochaine fois sur le forum,

mth

Citation :

Bonjour mimi2a, et bienvenue sur le forum,

Tu as sûrement vu les tutoriels de myDearFriend ici Horloges, Timers et Clignotements mais il convient d'utiliser VBA, je ne pense pas que tu puisses faire autrement.

Très bonne soirée,

mth

Bonsoir tokren, bonsoir le forum,

Voici une approche à l'aide de formules en espérant qu'elle pourra t'aider.

Dans cellule E3 cette formule à copier vers la droite puis vers le bas:

=SI(COLONNES($E$1:E$1)<=NB.SI(PlageGuichet1;$D3);DECALER($A$2;EQUIV($D3;PlageGuichet1;0)+COLONNE(A$1)-1;1);"")

L'idée de cette formule est d'aller chercher dans la colonne B la première valeur puis la deuxième etc, en n'affichant que le nombre exact de valeurs correspondant au numéro de guichet, ce nombre pouvant varier.

Pour une meilleure lisibilité de la formule j'ai nommé la zone Feuil2!$A$3:$A$729 "PlageGuichet1"

(Voir ici pour nommer une plage de cellule)

Pour aller chercher la première valeur correspondant au numéro de guichet, puis la seconde etc., j'ai profité du fait que la première colonne soit triée, une fois que la première valeur est trouvée il suffit de se décaler de 1 ligne pour avoir la valeur suivante.

c'est ce que fait cette partie de la formule:

DECALER($A$2;EQUIV($D3;PlageGuichet1;0)+COLONNE(A$1)-1;1)

La fonction DECALER() permet, à partir d'une cellule de départ (ici la cellule A2), de se décaler verticalement et horizontalement pour atteindre une autre plage de cellules . (voir aide sur cette fonction ici)

Pour me décaler verticalement j'utilise les fonctions EQUIV() et COLONNE(), pour me décaler horizontalement je saisis 1, je ne me décale que d'une colonne vers la droite pour lire la valeur de la colonne B

La fonction EQUIV() permet de trouver la position d'un élément dans une plage (voir l'aide Excel ici qui est bien faite pour plus de détail)

En E3, je cherche la position de la valeur de D3 (soit 2004) au sein de la plage PlageGuichet1

2004 n'existant pas dans PlageGuichet1 la fonction renvoie une erreur. Mais pour la ligne suivante, 2112 existe, la fonction EQUIV() renvoie 110. 2112 est effectivement la à 110ème position de la plage.

La fonction COLONNE() renvoie le numéro de colonne. J'utilise cette fonction pour avoir la première valeur trouvée quand je suis en colonne E (COLONNE(A1)=1). Lorsque je copie la formule vers la droite, COLONNE(A1) devient COLONNE(B1) qui est donc égal à 2, ceci me permet de me décaler d'une ligne supplémentaire, etc.

Ainsi, si la fonction EQUIV() me renvoie 110 comme ci-dessus pour le guichet 2112, si j'ajoute 1 je me trouverai donc en position 113, position 114 en ajoutant 2 et ainsi de suite.

Une fois cette formule écrite, il ne faut pas indéfiniment recopier la formule sur la droite sans quoi j'afficherais des valeurs ne correspondant plus au on numéro de guichet (à force de me décaler d'une ligne à chaque fois). C'est pourquoi dans la première partie de la formule je calcule le nombre d'occurrence de mon numéro de guichet. S'il apparait 3 fois j'affiche 3 valeurs et pas plus.

NB.SI(PlageGuichet1;$D3) va compter le nombre d'occurrence de la valeur située cellule D3 au sein de la plage PlageGuichet1

Pour la première ligne, le résultat est zéro, pour la ligne suivante le résultat est 3.

Ceci signifie que je ne dois rien afficher sur la première ligne, que je dois afficher 3 valeurs sur la seconde ligne car le code guichet 2112 apparaît 3 fois, et que dès la 4ème colonne je ne dois plus rien afficher.

C'est pourquoi j'utilise la fonction COLONNES(). En Colonne E la formule COLONNES($E$1:E$1) renvoie 1 (il n'y a qu'une colonne)

En copiant la formule vers la droite, elle devient COLONNES($E$1:F$1), qui renvoie donc 2 car il y a 2 colonnes. (Le petit signe $ permet de figer la colonne E de départ, voir ici pour plus d'explications).

Ainsi arrivé en colonne H, j'ai COLONNES($E$1:H$1) qui renvoie 4, ce nombre est supérieur au nombre d'occurrence de mon numéro de guichet, donc je n'affiche rien.

Pour l'autre partie de la question, il faudrait une macro, mais une formule peut tout de même aider à repérer les numéros absents, par exemple en C3 et à copier vers la bas:

=RECHERCHEV(A3;$D$3:$D$37;1;0)

Si la valeur du guichet en A3 n'est pas trouvée dans la plage $D$3:$D$37, la fonction RECHERCHEV() renvoie #N/A, ce qui permet visuellement de bien repérer les valeurs absentes pour les supprimer.

(il est dommage que les deux listes soient au même niveau en haut de la feuille, car ensuite il suffirait de faire une recherche de cellule en colonne B, en cochant la case "erreur", puis clic droit "supprimer ligne entière". Ce n'est pas possible ici sous peine de supprimer les éléments du tableau de droite)

Voilà tokren j'espère que cela pourra t'aider, avec les formules écrites dans ton fichier pour voir plus concrètement.

Très bonne soirée,

mth

Bonsoir tokren, et bienvenue sur le forum.

Plusieurs valeurs peuvent correspondre à un numéro de guichet, par exemple pour le "guichet 1" numéro 2011 nous avons 10 valeurs différentes en colonne B, du coup laquelle de ces valeurs faut-il afficher en colonne E ?

Très bonne soirée,

mth

Bonjour xavierp,

Je soupçonne un problème de donnée mal saisie dans le fichier, qui fait que la formule renvoie une réponse qui semble incohérente. Il faudrait pouvoir vérifier les colonnes AX, V et C. Est-ce possible de les mettre dans un fichier zippé sur le forum ? Le fichier du premier post n'est pas passé (le Zip est vide) donc nous ne pouvons pas vérifier.

Très bonne journée et à bientôt,

mth

Bonjour xavierp et bienvenue sur le forum,

La fonction semble bien orthographiée, il y a peut-être un problème de qualité de donnée dans le fichier ( un espace qui traîne devant un M, une date saisie en texte au lieu d'une vraie date, etc. ) mais pour le voir il faudrait essayer de recharger le fichier, car le Zip est vide.

A bientôt,

mth

Bonjour Rosette,

Peut-être avec la fonction SOMMEPROD(), mais ce serait plus facile de voir un tout petit extrait de fichier d'une dizaine de lignes pour valider cette hypothèse (voir ici pour joindre un fichier).

Question subsidiaire, y a t-il 65000 lignes remplies dans le fichier ou moins?

A bientôt,

mth

Bonjour Danielle, bonjour le forum,

Il est possible d'utiliser une plage dynamique ou l'outil "Tableau" d'Excel, voir par exemple ici

Très bonne journée

mth

Bonjour Jango,

Sourire... ça m'apprendra à répondre sans fichier pour pouvoir tester , j'ai négligé les plages, en théorie ça serait plus proche de ceci:

=NB.SI.ENS((B$2:B3);0;B$2:B3;"<>0";B$2:B3;"<>"&"";(C$2:C3);0;C$2:C3;"<>"&0;C$2:C3;"<>"&"")

En théorie seulement, car cette formule ci-dessus ne donnera rien les critères étant incompatibles. Il faudrait que tu arrives à expliquer ce que tu veux en joignant un exemple de quelques lignes.

Cette fonction existe depuis 2007, elle peut-être bien plus pratique que la fonction BDNB(), ceci dit tu peux aussi utiliser SOMMEPROD().

Très bonne soirée,

mth