Formule équivalente à FILTRE ?

Bonjour à tous,

j'ai un petit soucis de formule. Je travaille sur deux Excels différents (en perso sur Excel 365 et en pro sur Excel 2016). J'ai un fichier de répartition de chambre en fonction des noms de mes collaborateurs. J'ai la base de données avec en colonne B les noms des personnes et en C la chambre attribuée. J'ai une deuxième feuille avec un plan de chambre, j'ai donc utilisé la formule :

=INDEX(Feuil1!B4:B99;EQUIV(Feuil2!D4;Feuil1!C4:C99))

Cette formule est presque parfaite, elle fonctionne que pour les chambres seules... Il y a la formule FILTRE() qui fonctionne chez moi mais pas à mon travail puisqu'elle est dispo que sur 365... Je cherche donc une formule similaire me permettant d'afficher en D4 le premier collaborateur de la liste ayant la chambre 1 et en D5 le deuxième collaborateur de la liste ayant aussi la chambre 1. D6 pour le 1er en chambre 2 et D7 pour le 2ème en chambre 2. Et ainsi de suite...

J'ai fais pleins de recherche mais rien trouvé qui fonctionne pour le moment.

Merci d'avoir pris le temps de me lire, en espérant avoir été clair dans mon explication de problème

Bonjour,

Voici une proposition avec une fonction personnalisée (via VBA).
Pour saisir le code suivant, il faut aller sur le volet développeur (s'il n'est pas activé, allez sur le volet Fichier > Options/Personnaliser le ruban/Cochez "Développeur") puis ouvrir l'éditeur Visual Basic, puis insérer un module via le menu contextuel "Insertion" et coller le code dedans :

Function EQUIV_MULT(Valeur_Cherchee, Plage As Range, Occurrence As Byte) As Integer

'PERMET D'OBTENIR LA POSITION D'UNE VALEUR DANS UN PLAGE À SA Nème OCCURRENCE 
Application.volatile

For i = 1 To Plage.Count ' boucle sur i de 1 à la taille de la plage
    If Plage.Cells(i).Value = Valeur_Cherchee Then 'si la valeur de la cellule à la ième position dans la plage = Valeur à chercher
        Cpt = Cpt + 1 'incrémentation (Cpt commence à 0)
        If Occurrence = Cpt Then 'si Cpt = Occurrence >>> CORRESPONDANCE
            EQUIV_MULT = i 'La fonction retourne la position
            Exit Function
        End If
    End If
Next i 'on passe au i suivant

End Function

Ensuite, vous pouvez saisir un INDEX EQUIV_MULT comme ça :

=INDEX(Feuil1!B4:B99;EQUIV_MULT(Feuil2!D4;Feuil1!C4:C99;7))

Dans cet exemple, EQUIV_MULT renvoie la 7è position de la valeur de D4 en C4:C99 et INDEX retourne la valeur associée en B4:B99.

En revanche, je ne sais pas comment vous l'utiliserez mais il faudra bien cadrer en amont son utilisation et parvenir à fixer une logique au niveau des occurrences, car pour l'instant, ça ne parait pas très clair...

Cdlt,

Bonjour mielo, 3GB,

Une autre solution par formules matricielles, si j'ai bien compris ta demande.

Cordialement

Bonjour haonv,

Pourrais-tu mettre la formule sur le post ? Ça m'intéresserait de la voir, mais sans avoir à télécharger le fichier.

Merci d'avance,

bonjour

une contribution dominicale

cordialement

Bonjour tulipe_4,

En fait, je demandais ça d'une part pour que la solution soit visible en consultant seulement le post mais aussi pour éviter de télécharger les fichiers quand ce n'est pas nécessaire

En tout cas, merci à toi, je vais regarder finalement...

Bon dimanche dominical

tulipe_4,

Est-ce que je serais en droit de solliciter une contribution supplémentaire ?

Peux-tu m'expliquer le fonctionnement de la formule ?

{=SIERREUR(INDEX(C$4:C$13;EQUIV(0;NB.SI(C$4:C$13;"<"&C$4:C$13)-SOMME(NB.SI(H$3:H3;C$4:C$13));0));"")}

Pourquoi EQUIV(0; ....) ? Pourquoi NB.SI(C$4:C$13;"<"&C$4:C$13) ? Je ne comprends pas. Le calcul matriciel convertit les range en tableaux dynamiques ?

bonjour

pas facile , car +/- vicieux

au depart , la partie equiv (renvoiera un 0 ou plusieurs car elle cherche dans une matrice de nombre composée du nombre de valeurs inferieures (par exemple pour le 1 il n'y en a pas donc 0) -la somme des valeurs qui correspondent au nombre de valeurs de la colonne des chambre avec comme critère ce que l'on est en train d'extraire (ceci pour eviter les doubles)

donc pour extraire le 1 : 0 parce quil n'y a pas d'inferieur -0 parce rien qui correspond au critère (H$3:h3) qui est l'entete de la colonne d'extraction =>prevoir une cel vide

alors 0-0=0 =>equiv trouve la place de ce premier 0 dans cette matrice virtuelle ,ensuite, lors de l'incrementation; Equiv trouvera encore la position du premier 0 dans cette matrice virtuelle à géométrie variable (H$3:H3 devient H$:H4) et comme on vient d'extraire le 1 => ça change tout

ceci impose de faire attention à figer avec des $$ ce qui doit rester fixe (la colonne source) de ce qui doit partielement évoluer (H$3:H3)

je ne peux t'en dire plus , et je pense meme que tu ne trouveras rien de plus sur cette construction

cordialement

nb) le mieux c'est quand meme de presenter une maquette , qui selon les réponses pourra etre utilisée directement en la renomant

Merci tulipe pour cette explication.

Tout n'est pas parfaitement clair mais je crois comprendre le mécanisme.

Cependant, je n'ai pas du tout saisi ce que tu entendais par présenter une maquette. C'est là que je me rends compte de la pauvreté de mon vocabulaire excelien...

Cdlt,

re

ben tout simplement une pièce jointe fidèle au document qui te pose problème , théoriquement c'est la règle sur ce forum

cordialement

Bonjour,

Oui désolé je n'avais inséré le fichier posant problème. J'ai essayé d'adapter vos solutions mais cela me met des erreurs je dois être un boulet
En tout cas merci a vous pour les réponses apportées

bonjour

je ne saisis pas bien ton système car la présentation est carrément différente

que remplis tu manuellement ; la feuille plan ou la feuille répartition qui , visiblement ne peux recevoir que 2 clients au max

essaie de nous aider en remettant une PJ avec d'avantage de clients pour mieux comprendre , n'hesite pas à y mettre le pourquoi du comment

cordialement

Bonjour,

Effectivement désolé
J'ai refais avec plus de nom ! Et oui mes chambres sont doubles et donc contenir 2 personnes max. J'ai refais avec plus de chambre. Je rempli manuellement la feuille répartition de chambre et j'aimerai un remplissage automatique de plan de chambre

bonjour

c'est un peu "binz" ton exemple , je te propose plutot ça , mais si tu tiens vraiment à ton "organisation" cela peut être faisable en beaucoup plus compliqué

cordialement