Réalisation facturier d'après récap activité

[Ykaran]

Bonjour
Je me présente, Laurent 34 ans.
Je vais me lancer très bientôt dans la création d'une société de transport Express de marchandises en VL
Je vais travailler principalement avec 5 clients, je serai gestionnaire et chauffeur.
J'ai donc monté un fichier Excel pour récapituler l'activité de la société mensuellement dont la mission est de réaliser des livraisons en véhicule dédié. 5 clients donc 5 donneurs d'ordre différents qui peuvent.
Plusieurs courses possibles sur une même journée, courses pouvant être demandées par chacun des 5 clients.

J'ai donc :
- un onglet qui récapitule l'activité du mois, jour par jour, détaillée (prix, expéditeur, destinataire, coordonnées de chacun, donneur d'ordre, date)
- un 2nd onglet "refus" qui comptabilise par jour, toutes les missions que je refuserai, le tout pour éviter de refuser toujours au même client, de part mon activité et moi seul chauffeur malheureusement cela arrivera
- un 3ème onglet "listing clients" qui relate le nom de mes 5 clients et leurs coordonnées
- un dernier onglet enfin "facturation" qui doit reprendre par donneur d'ordre la totalité des missions du mois avec leurs tarifs. J'ai donc un menu déroulant qui me donne l'en-tête de la facture

Je souhaite que ce menu déroulant, une fois le client choisi, rappatrie toutes les missions faites pour ce client ainsi que le prix de chaque course pour compiler toute l'activité du client et me remplisse automatique la facture à lui envoyer
Mais je suis bloqué dans le rappatriement de ces données. J'ai utilisé une fonction "RECHERCHEV". elle est valable pour la première ligne du contenu de ma facture mais si je colle celle-ci dans toutes les autres ligne de la facture, j'obtiens 20fois la même mission et non pas les 20 missions détaillées demandées par ce client

Pouvez vous m'aider svp ?

Je peux vous joindre le fichier excel que j'ai monté mais il faudra peut être que je le modifie car ma société étant en cours d'immatriculation, ma collaboration avec les clients concernés doit pour l'instant rester confidentielle

 

[CISCO]

Bonjour

Cf. en pièce jointe, avec des formules matricielles à valider avec les touches Ctrl + maj (juste au dessus de Ctrl)+entrer, formules que tu peux tirer vers le bas.

@ plus

 

[Ykaran]

Bonjour CISCO
Merci c'est exactement cela que je cherchais par contre j'avoue que je ne connaissais pas la formule INDEX (matricielle)
VIsiblement par contre cela ne fonctionne pas avec les nombres car les couts des missions (colonne D) ne rentrent pas dans le récap de facturation avec la même formule

 

[CISCO]

Bonjour

Bonjour CISCO
VIsiblement par contre cela ne fonctionne pas avec les nombres car les couts des missions (colonne D) ne rentrent pas dans le récap de facturation avec la même formule

Il ni a pas de raison... Essayes avec

=INDEX('Activité mensuelle'!D$1:D$131;PETITE.VALEUR(SI('Activité mensuelle'!A$8:A$131=F$11;LIGNE(A$8:A$131));LIGNES($1:1)))

toujours à valider avec les 2 touches Ctrl+maj+entrer.

@ plus

P.S : Si tu veux comprendre comment cela fonctionne, --> Onglet Formules --> Evaluation de formule --> Pas à pas ou Suivant.

 

[Ykaran]

Bonjour Cisco

J'essaie vraiment mais cela ne fonctionne pas. Je ne comprends pas pourquoi (Version Excel ? Option à activer en amont ?) Je colle ta formule dans ma cellule "Prix course HT" et je valide comme tu me l'as dit avec "ctrl+maj+enter" mais rien ne se passe et j'obtiens comme résultat "0"

Je vais essayer de traduire la formule (tout du moins une partie car la fin je n'y arrive pas). En clair, si dans la colonne A de l'onglet "activité mensuelle", on retrouve le client sélectionné en F11 de l'onglet Facturation, alors l'index va chercher dans la colonne D "d'activité mensuelle" les résultats afférents à la ligne où apparait ce client dans "activité mensuelle" et afficher ce résultat dans la colonne sélectionnée dans "facturation", ici dans ta formule la colonne A, des cellules A8 à A131. Exact ? les références "Ligne" j'avoue je ne les comprends pas

Au final, quand j'utilise ce type de formule dans ma facturation dans la colonne A (date), je cherche en colonne B "d'activité mensuelle" mon client référencé en F11 de facturation et ça m'affiche en colonne A de facturation les résultats. ok ça ça fonctionne

Mais si j'adapte le même type de formule pour la colonne E "prix course" en cherchant en colonne D de l'activité mensuelle et en donnant les résultats en colonne E de facturation, j'obtiens zéro pour la ligne 8 et si j'étire la fonction, j'obtiens une erreur de nombre

La j'avoue je suis largué. ça marche pour la date mais pas pour le prix, qui sont tous 2 des données au format nombre. J'ai tenté de remplacer dans activité mensuelle le nombre par un mot mais il ne va pas plus me le chercher.... Please Help

 

[CISCO]

Bonjour

Bonjour Cisco
...
Je vais essayer de traduire la formule (tout du moins une partie car la fin je n'y arrive pas). En clair, si dans la colonne A de l'onglet "activité mensuelle", on retrouve le client sélectionné en F11 de l'onglet Facturation, alors l'index va chercher dans la colonne D "d'activité mensuelle" les résultats afférents à la ligne où apparait ce client dans "activité mensuelle" et afficher ce résultat dans la colonne sélectionnée dans "facturation", ici dans ta formule la colonne A, des cellules A8 à A131. Exact ? les références "Ligne" j'avoue je ne les comprends pas

C'est presque ça . La syntaxe de la fonction INDEX est ici INDEX(plage;n°). Ici donc, à la place de plage, tu indiques la colonne où tu vas chercher les informations, les valeurs à transférer, et à la place du n° tu as la formule

PETITE.VALEUR(SI('Activité mensuelle'!A$8:A$131=F$11;LIGNE(A$8:A$131));LIGNES($1:1))

SI('Activité mensuelle'!A$8:A$131=F$11;LIGNE(A$8:A$131)) en matriciel liste tous les n° de LIGNE contenant le clients F$11 dans la plage 'Activité mensuelle'!A$8:A$131.
Mais on ne veut pas toutes ces valeurs. Sur la ligne 18, on veut la plus petite, sur la 19 la seconde plus petite, sur la 20, la troisième plus petite...
Sur la ligne 18, LIGNES($1:1) = 1, et donc PETITE.VALEUR(.....;LIGNES($1:1)) renvoie le plus petit n° de ligne contenant le client F$11. Et donc la formule complète donne INDEX(plage;plus petit n° de ligne contenant F$1).
Sur la ligne 19, LIGNES($1:...) devient automatiquement LIGNES($1:2) = 2. Donc la formule complète donne INDEX(plage;seconde plus petite n° de ligne contenant F$11).
Et ainsi de suite.


@ plus

 

[Ykaran]

ok pour l'échelonnement des valeurs de plus petite à moins petite. là je comprends.
par contre, "PETITE.VALEUR" peut il être remplacé par une autre expression exprimant un ordre de grandeur de valeur ?
pourquoi la fonction matricielle que tu m'as donnée ne fonctionne pas pour le tarif en colonne D de "facturation" ? et ensuite pourquoi elle ne fonctionne pas en l'étirant pour les autres valeurs dessous (lignes 19, 20, etc... de la même colonne "Prix course HT") ?
il apparait une erreur de "nombre" mais je ne comprends pas où se trouve la redondance qui la génère

merci déjà d'avance pour tes informations

 

[CISCO]

Bonjour

J'ai changé ma dernière pièce jointe (Apparemment, je m'étais trompé de fichier lors de mon dernier envoi).

....
par contre, "PETITE.VALEUR" peut il être remplacé par une autre expression exprimant un ordre de grandeur de valeur ?

Qu'est-ce que tu veux exactement ? Avec GRANDE.VALEUR, tu auras la liste des choses à facturer pour ce client à partir de la fin.

Pourquoi la fonction matricielle que tu m'as donnée ne fonctionne pas pour le tarif en colonne D de "facturation" ?

Parcequ'il y avait une petite faute de frappe dedans : C'est LIGNE(E$8:E$131) qu'il faut et pas LIGNE(E$18:E$131).

@ plus

 

[Ykaran]

Je viens d'essayer et ça change pas le résultat.
En fait ce que je veux, peu importe la formule, c'est faire apparaitre le cout de la mission dans la colonne "prix course ht" en fonction de la date de la mission et donc en fonction du client

et puis que ça fonctionne pour chaque ligne soit en étirant soit en modifiant la formule

Dernière question :
pourquoi dans certaines lignes j'ai ce caractère qui apparait en début et fin de formule : { } et pas dans d'autres...

 

[CISCO]

Bonjour

pourquoi dans certaines lignes j'ai ce caractère qui apparait en début et fin de formule : { } et pas dans d'autres...

Normalement, tu devrais avoir ces symboles dans toutes les cellules dans les colonnes A, B, C et E. Si ce n'est pas le cas, c'est que les formules dedans n'ont pas été validées en matriciel. Sélectionnes une de ces cellules sans les accolades. Cliques dans la barre de formule et appuies sur les 3 touches Ctrl+maj (la touche juste au dessus du Ctrl) + entrer. Dans le cas présent, toutes peuvent être tirées vers le bas, une fois correctement validé.

S'il ni a pas ces accolades dans ces 4 colonnes, c'est normal que les résultats ne soient pas bons.

@ plus

 

[Ykaran]

j'ai réussi à activer les fonctions matricielles mais je n'arrive toujours pas à obtenir les prix dans la colonne "Prix course HT".... je dois vraiment être un boulet je ne vois que ça...
regarde dans la pièce jointe. Tu y arrives à afficher les prix CISCO dans cette colonne "prix course ht" dans l'onglet facturation ?

 

[CISCO]

Bonsoir

Sur ta dernière pièce jointe, tu as encore des 18 à la place de certains 8, par ex colonne A. Résultat, si l'information se trouve dans la ligne 8, la formule donne le n° de ligne 18 (ce qui est faux, mais ne renvoie pas de message d'erreur). Si c'est dans la ligne 9, elle donne 19... Si c'est en ligne 128, la formule donne 138. Si l'information recherchée est en ligne 129, la formule donne #NA puisque la partie LIGNE(..$8;..$138) s'arrête ligne 138.
Donc lorsque les informations recherchées se trouvent à la fin de ton tableau, comme par ex pour le client 2, Excel ne trouve pas les n° de lignes correspondant, et renvoie un message d'erreur.

Pourquoi n'utilises tu pas ma dernière pièce jointe, dans le post #6 ?

@ plus

 

[Ykaran]

javais pas vu ton fichier en post 6
effectivement avec une fonction SIERREUR en plus ça fonctionne, je comprends pas bien pourquoi mais du moment que ça fonctionne merci beaucoup ainsi que pour l'info du décalage des données recherchées à cause des lignes 18

 

[CISCO]

Bonsoir

Ca fonctionne à cause des 8 à la place des 18.

Le SIERREUR(.....;"") est là juste pour mettre des cellules vides lorsqu'il ni a plus de valeurs à écrire.

@ plus