afficher les fournisseurs les moins chers.
Grâce à la fonction "Min" j'ai pu voir quels prix étaient les moins chers.
Je souhaite afficher dans une colonne voisine les fournisseurs associés au prix le moins cher.
A=MIN(B2:F2)
=INDEX($B$1:$F$1;EQUIV(MIN($B2:$F2);$B2:$F2;0))
autre ex similaire = INDEX($C$1:$H$1;1;EQUIV(I2;C2:H2;0)) = i2 = cellule où offre minimum
=INDEX($A$1:$F2;1;EQUIV(PETITE.VALEUR($B2:$F2;1);$A2:$F2;0))
=index(ligne avec nom des fournisseurs en figé F4;sinon1;equiv(cellule de la formule minimum; sur la place ligne des offres non fiche; sinon zero)
La fonction Excel INDEX utilisée avec la fonction EQUIV permet la recherche de valeur dans un tableau.
Pour vous aider à mieux comprendre, l’exemple utilisé ici est une combinaison des exemples des 2 tutoriels indiqués, leur lecture est donc vivement recommandée.
L’objectif ici est de trouver le numéro de dossier ainsi que le nombre de points du lieu recherché. L’utilisateur doit pouvoir entrer la ville dans la partie verte et voir ensuite le résultat de sa recherche dans la partie bleue :
Sélectionnez pour commencer la fonction INDEX :
Dans « Matrice », entrez la plage de cellules du tableau.
Dans « No_lig », n’entrez rien pour le moment (la fonction EQUIV qui calculera le numéro de ligne en fonction de la recherche sera insérée ici).
Dans « No_col », entrez la colonne du tableau qui contient les données à renvoyer (dans cet exemple, le but est d’afficher le numéro de dossier, c’est donc la colonne 1).
Positionnez-vous dans « No_lig » et ajoutez la fonction EQUIV en cliquant ici :
Le but de la fonction EQUIV est donc de renvoyer le numéro de ligne de la ville :
Dans « Valeur_cherchée », entrez la valeur dont il faut rechercher la position (ici, la ville).
Dans « Tableau_recherche », entrez la plage de cellules dans laquelle la fonction recherchera la position de la ville.
Dans « Type », entrez 0 pour trouver la valeur exacte.
Le numéro de dossier de la ville est alors affiché :
Pour afficher ensuite les points, il suffit de copier la formule et de modifier le numéro de colonne (remplacez 1 par 3) :
En cas de besoin, vous pouvez télécharger le fichier Excel de cet exemple : index-equiv.xlsx
A B C D E F G H I J K L M N 1 2 3 4 5 6 7 8 9 10 11 12 13 14
O P Q 15 16 17
R S T U V W X Y Z 18 19 20 21 22 23 24 25 26
Pour dérouler la formule sur plusieurs colonnes mettre une ligne avec les chiffres des colonnes du fichier source : 2 3 4 en 1ère ligne, 2 3 4 5 en haut des colonnes =RECHERCHEV($A2;ADRESSE!$1:$1048576;P1;0) =RECHERCHEV($A2;ADRESSE!$1:$1048576;Q1;0) =RECHERCHEV($A2;ADRESSE!$1:$1048576;R1;0) recherche et affiche les données de la cellule du fichier 2 afficher en P1 = se réfère à la colonne 2 du fichier « Adresse » où est marqué « P. » POUR PAYS SE Référe au même onglet = donc pas de nim car plage de la feuille)
puis en 3e ligne : figer la colonne de la cellule de réf et dérouler la formule sur le long du tableau ensuite, onglet de réf =RECHERCHEV($A3;ADRESSE!$A:$BZ;ACHATS!P$1;FAUX) =RECHERCHEV($A3;ADRESSE!$A:$BZ;ACHATS!Q$1;FAUX) =RECHERCHEV($A3;ADRESSE!$A:$BZ;ACHATS!R$1;FAUX)
Recherche V (
NOM
Recherche la donnée = il faut trier la colonne de A à Z de la donnée source (ex le code AL) sur le fichier source, et sur le fichier où trouver la donnée) Sur le Fichier où trouver la donnée , mettre en colonne A la donnée source (cad similaire au fichier source) = ex : code AL
PLAGE DE RECHERCHE
Tri personnalisé > colonne n° x > de A à Z ou filtrer A à Z
Mettre n° de la colonne à trouver
Mise en forme conditionnelle Formule = « <>0 » = jaune car cellule où la formule est vraie, cad fonctionne et correspond au cas différent de zéro
RechercheV = recherche verticale RechercheH = recherche Horizontale syntaxe de la fonction est : =recherchev(valeur;matrice;n°col) où n°col est le numéro de la colonne dont on doit ramener la valeur, sachant que ce numéro de colonne est relatif à la matrice.
Pour la recherche horizontale, il suffit de remplacer n°col par n°lig… =RECHERCHEH(A17;’GridReport (offre3) (2)’!$DE$2:$HA$29;28;0)
=RECHERCHEH(A2;’Grille de prix retraitée’!$DE$2:$HB$29;28;0) Valeur Cherchée : Nom de société Tableau : va du nom de la société à prendre jusqu’à la somme des K1pondéré et tout le fichier de 5RONE à VISION INTERIM (que partie K1 Pondéré) « No_index_lig », nous devons entrez le n° de la ligne qui contient les données à afficher =
Du nom de la société, des 25 K1 pondéré et de la somme K1 pondéré : 28Lx1C = 28 lignes x 1 Colonne Donc il s’agit de la 28e ligne de l’ensemble des lignes sélectionnées du tableau (même si la ligne souhaitée se trouve en vérité à la 29e ligne sur le tableau, ce n’est pas le cas dans la totalité des lignes sélectionnées qui ne prend pas en compte les lignes vides : 28e ligne est vide et donc non prise en compte.
=RECHERCHEH(A102;’Grille de prix retraitée’!$DE$2:$HB$29;28;0)
=RECHERCHEH(A2;’Grille de prix retraitée’!$DE$2:$HB$29;28;0)
=RECHERCHEH(A2;’Grille de prix retraitée’!$DE$2:$HB$29;28;0)
=RECHERCHEH(A2;’Grille de prix retraitée’!$C$2:$CZ$29;28;0)
La fonction RECHERCHEH cherche une valeur dans la première ligne d’un tableau puis elle affiche le contenu de la cellule qui se situe sur la même colonne que la valeur recherchée. Prenons un exemple : Nous voulons afficher l’âge d’une personne en fonction de son prénom.
Ici, nous cherchons l’âge de Séverine. • Dans « Valeur_cherchée », nous entrons B6 qui correspond à Séverine. • Dans « Tableau », nous définissons une plage de cellule qui contient le tableau. • Dans « No_index_lig », nous devons entrez le n° de la ligne qui contient les données à afficher (ici les âges). Le n° de ligne est celui du tableau sélectionné. • « Valeur_proche » est facultatif, mais permet soit de chercher la valeur exacte de « Valeur_cherchée » (en entrant : FAUX), soit de trouver la valeur la plus proche de « Valeur_cherchée » (en entrant : VRAI ou en laissant vide).
On obtient finalement l’âge de Séverine. Maintenant, il ne reste plus qu’à modifier B6 pour trouver l’âge d’une autre personne.
Les fonctions Recherche H et V Dans les messages du 16 et du 18 mars, nous avons vu comment fonctionnait la fonction Recherche(), avec ses deux syntaxes.
Une des difficultés de la fonction Recherche(), pour certains utilisateurs, tient à ce qu’elle fonctionne en colonne ou en ligne selon la forme de la matrice.
Pour résoudre ce problème, après la création de la fonction Recherche(), qui était d’ailleurs présente dans le premier Visicalc sur Apple II en 1979, Microsoft a créé deux nouvelles fonctions, RechercheV() et RechercheH() qui – comme leur nom l’indique – pratiquent exclusivement des recherches verticales et horizontales.
Il a été décidé, par la même occasion, de résoudre une autre difficulté pour certains utilisateurs, le fait que la recherche verticale ramenait le contenu de la cellule la plus à droite dans la matrice pour la ligne sélectionnée.
C’est pourquoi la syntaxe de la fonction est : =recherchev(valeur;matrice;n°col) où n°col est le numéro de la colonne dont on doit ramener la valeur, sachant que ce numéro de colonne est relatif à la matrice.
Pour la recherche horizontale, il suffit de remplacer n°col par n°lig… Dans l’exemple ci-dessus, nous cherchons à récupérer le nom de famille et le salaire d’une personne à partir de son prénom. Les formules utilisées sont :
En F15 : =recherchev(F14;E3:F11;2)
En F16 : =recherchev(F14;E3:G11;3)
Remarque 1 – Une seule matrice
Puisque nous indiquons le numéro de la colonne à ramener, nous aurions pu utiliser le même second argument E3:G11 (ou, mieux encore, le nom de ce bloc) dans les deux formules.
Remarque 2 – Et Gilles, alors ?
Vous avez probablement noté la présence de deux Gilles. Lequel sera retenu par nos deux formules ? Le dernier, évidemment, si vous avez bien compris l’algorithme présenté le 18 mars : on descend jusqu’à trouver le premier prénom plus grand, Hervé, puis l’on remonte d’une ligne, ce qui nous fait immanquablement arriver au dernier Gilles.
Remarque 3 – Une erreur possible
Une grosse erreur aurait été d’utiliser la formule =recherchev(F15;F3:G11;2) en cherchant le salaire à partir du nom. En effet, les noms ne sont pas en ordre alphabétique et vous obtenez alors n’importe un résultat souvent – mais pas toujours – faux, vu la façon dont marche l’algorithme de recherche.
1/ la cellule est au format texte. Fais une clic droit sur la cellule, Format de cellule, onglet Nombre. Le format ne doit pas être « texte ». Si c’est le cas, efface la formule, change le format (mets « standard », par exemple) et entre ta formule.
2/ Si ce n’est pas le cas, clique sur l’onglet Fichier, Options, Options avancées, Options d’affichage de la feuille de calcul, vérifie que la case « Formules dans les cellules au lieu de leur résultat calculé » n’est pas cochée.
3/ vérifie que ta formule commence bien par « = ».
—————————————
Conseil : Essayez d’utiliser la nouvelle fonction XLOOKUP, une version améliorée de RECHERCHEV qui fonctionne dans n’importe quelle direction et renvoie des correspondances exactes par défaut, ce qui rend son utilisation plus facile et plus pratique que celle de son prédécesseur.
Utilisez RECHERCHEV lorsque vous avez besoin de rechercher des éléments dans un tableau ou une plage par ligne. Par exemple, recherchez le prix d’une pièce automobile sur la base de son numéro de pièce ou recherchez le nom d’un employé en fonction de son ID d’employé.
Dans sa forme la plus simple, la fonction RECHERCHEV a la signification suivante :
=RECHERCHEV(ce que vous voulez rechercher, où vous voulez le rechercher, le numéro de colonne dans la plage contenant la valeur à renvoyer, renvoyer une correspondance approximative ou exacte ( indiquée par 1/VRAI ou 0/FAUX).
Conseil : Pour tirer le meilleur parti de la fonction RECHERCHEV, il convient d’organiser vos données afin que la valeur que vous recherchez (numéro de référence) se trouve à gauche de la valeur de retour à rechercher (prix de la pièce).
Détails techniques
Prise en main
Quatre types d’informations sont nécessaires pour définir la syntaxe de la fonction RECHERCHEV :
La valeur que vous voulez rechercher (également appelée valeur de recherche).
La plage dans laquelle se trouve la valeur recherchée. N’oubliez pas que la valeur recherchée doit toujours apparaître dans la première colonne de la plage pour que RECHERCHEV fonctionne correctement. Par exemple, si la valeur recherchée est dans la cellule C2, votre plage doit commencer par C.
Le numéro de colonne dans la plage qui contient la valeur de retour. Par exemple, si vous spécifiez la plage B2:D11, vous devez compter B comme la première colonne, C comme la deuxième, et ainsi de suite.
Vous pouvez aussi spécifier VRAI pour obtenir une correspondance approximative ou FAUX pour obtenir une correspondance exacte de la valeur de retour. Si vous n’indiquez rien, la valeur par défaut sera toujours VRAI ou une correspondance approximative.
Vous devez à présent définir tous les éléments ci-dessus comme suit :
=RECHERCHEV(valeur de recherche; plage contenant la valeur de recherche; numéro de colonne dans la plage contenant la valeur de retour, Correspondance approximative (VRAI) ou Correspondance exacte (FAUX)).
Exemples
Voici quelques exemples supplémentaires d’utilisation de le fonction RECHERCHEV :
Exemple 1
Exemple 2
Exemple 3
Exemple 4
Exemple 5
Combiner les données de plusieurs tables dans une feuille de calcul à l’aide de la rechercheV
=SIERREUR(PRIX UNITAIRE du fournisseur x Conditionnement d’origine ou modifié / Conditionnements du fournisseur)
Cette formule permet de gérer les erreurs dans une formule. SiERREUR renvoie une valeur que vous spécifiez si une formule retourne une erreur ; sinon, elle renvoie le résultat de la formule.
Syntaxe
SIERREUR(valeur;valeur_si_erreur)
La syntaxe de la fonction SIERREUR contient les arguments suivants :
value_if_error Obligatoire. Représente la valeur à renvoyer si une formule génère une erreur. Les types d’erreur suivants sont évalués : #N/A, #VALEUR!, #REF!, #DIV/0!, #NOMBRE!, #NOM?, ou #NUL!.
Remarques
Si value_if_error est une cellule vide, siERREUR la considère comme une valeur de chaîne vide (« »).
Si valeur est une formule de tableau, siERREUR renvoie une matrice de résultats pour chaque cellule de la plage spécifiée dans valeur. Voir le second exemple ci-dessous.
Exemples
Copiez les données d’exemple dans le tableau suivant, et collez-le dans la cellule A1 d’un nouveau classeur Excel. Pour que les formules affichent des résultats, sélectionnez-les, appuyez sur F2, et sur Entrée.
Quota
Unités vendues
210
35
55
0
23
Formule
Description
Résultat
=SIERREUR(A2/B2, « Erreur de calcul »)
Recherche une erreur dans le premier argument de la formule (divise 210 par 35), ne trouve aucune erreur, puis renvoie le résultat de la formule.
6
=SIERREUR(A3/B3, « Erreur de calcul »)
Recherche une erreur dans le premier argument de la formule (divise 55 par 0), trouve une erreur de division par 0, puis renvoie l’argument valeur_si_erreur.
Erreur de calcul
=SIERREUR(A4/B4, « Erreur de calcul »)
Recherche une erreur dans le premier argument de la formule (divise « » par 23), ne trouve aucune erreur, puis renvoie le résultat de la formule.
0
Exemple 2
Quota
Unités vendues
Rapport
210
35
6
55
0
Erreur de calcul
23
0
Formule
Description
Résultat
=C2
Recherche une erreur dans le premier argument du premier élément de la matrice de la formule (A2/B2 ou divise 210 par 35), ne trouve aucune erreur, puis renvoie le résultat de la formule.
6
=C3
Recherche une erreur dans le premier argument du second élément de la matrice de la formule (A3/B3 ou divise 55 par 0), trouve une erreur de division par 0, puis renvoie l’argument valeur_si_erreur.
Erreur de calcul
=C4
Recherche une erreur dans le premier argument du troisième élément de la matrice de la formule (A2/B4 ou divise « » par 35), ne trouve aucune erreur, puis renvoie le résultat de la formule.
0
Remarque : si vous avez une version actuelle de Microsoft 365,vous pouvez entrer la formule dans la cellule supérieure gauche de la plage de sortie, puis appuyer sur Entrée pour confirmer la formule en tant que formule de tableau dynamique. Sinon, vous devez entrer la formule comme une formule de tableau héritée : sélectionnez la plage de sortie, entrez la formule dans la cellule en haut à gauche de la plage de sortie, puis appuyez sur Ctrl+Maj+Entrée pour confirmer la formule. Excel ajoute automatiquement des accolades au début et à la fin de la formule. Pour plus d’informations sur les formules de tableau, voir Instructions et exemples de formules de tableau.
La fonction Excel SIERREUR vérifie si la valeur entrée est une erreur, elle retourne ensuite cette même valeur s’il n’y a aucune erreur ou une autre valeur définie en cas d’erreur.
Utilisation :
=SIERREUR(valeur, valeur_si_erreur)
CAS PRATIQUE
Reprenons l’exemple de la fonction RECHERCHEV qui affiche le nombre de points en fonction du numéro de dossier entré :
Le problème dans ce cas est qu’une erreur sera affichée si aucun résultat n’est trouvé :
Sans la fonction SIERREUR, il faudrait utiliser la fonction SI ainsi qu’une autre fonction pour tester si la fonction RECHERCHEV renvoie une erreur et retourner le résultat de la fonction RECHERCHEV si tout va bien ou une autre valeur en cas d’erreur :
La formule est donc inutilement longue et la fonction RECHERCHEV y est présente à double …
La fonction SIERREUR permet de simplifier tout cela en testant une valeur et en renvoyant directement cette même valeur s’il n’y a aucune erreur (ou une autre en cas d’erreur) :
Voici comment on peut extraire des données de SAP et ensuite les exporter en fichier Excel :
SAP transaction code : ZMM101 – PO Status List – liste du statut des commandes achats
Mettez les conditions que vous souhaitez pour filtrer les commandes / Purchase Orders
Indiquer dans « Basic Data » : company code, purchasing organization, Document date (2022.01.01 to 2022.01.12)
Dans la rubrique »Domestic/Import » cocher import
Dans additional Data : cocher Display Global Language
Dans Display Option / afficher les options : cocher Collective Display pour avoir un maximum d’informations (la quantité réceptionnée surtout)
Une fois les PO listés, cliquer sur EXPORT (icône d’un tableur excel avec une cellule jaune, à fauche de l’icône Information et refresh
Choisissez le format Spreedsheet
Dans la fenêtre pop-up : choisissez le format excel XLSX et sauvegardez
Purchasing data can be extracted via T code ZMM101. It allows to extract historical purchasing data. It allows to see all the purchasing data of year 2021 (supplier, purchasing item, volume, etc).
The usage on each selection options are pretty much self-explanatory.
The Display Options :
Separate display is to see brief information abt. the PO whereas collective display is to see detailed information (including GR (Goods reception & IV (services based)
Since collective display shows more information, the runtime takes longer than the separate display option.
1- Sélectionner la colonne A. Cela permet de sélectionner tout le tableau et non la 1ère ligne uniquement ! Ouf!
2- Aller dans Données / Datas
3- Convertir en mettant une virgule ou point virgule (on voit en bas comment le tableau excel va être)
4 – Suivant, Terminer et enregistrer en Excel
Un fichier en « .csv » se rencontre souvent sur Internet. En l’état, le format CSV n’est pas souvent exploitable. Il faut donc le convertir pour pouvoir utiliser ses données plus facilement. La conversion la plus courante se fait avec Excel. Voici un tutoriel qui explique comment convertir les données d’un fichier CSV en colonne et l’enregistrer en « .XLS.
Description du fichier en « .CSV » :
Le format CSV (Comma-separated values), est un format informatique ouvert. Il représente des données tabulaires (valeurs dans un tableau) sous forme de texte. Chaque valeur est séparée par une virgule (à l’anglaise) ou un point virgule (à la française) (d’autres séparateurs sont possibles, comme par exemple : les guillemets). Lors de la conversion d’un fichier CSV en XLS (format Excel classique) ou XLSX format Excel plus récent).
Comment convertir les données d’un fichier CSV en fichier XLS avec Excel ?
Ouvrir le fichier CSV avec Excel.
Allez dans l’onglet « Données ».
Sélectionner la colonne qui contient les données à transformer. (1ère cellule dans mon cas)
Cliquez sur « Convertir »
Capture d’écran du logiciel Excel: convertir les données d’un fichier CSV en XLS.
Une fenêtre s’ouvre, c’est l’assistant de conversions, en trois étapes.
Dans la première étape : si le séparateur de colonnes est une virgule, ou un point virgule, il faut laisser le bouton radio « Délimiter » coché, puis appuyez sur le bouton « Suivant ».
Dans l’étape 2/3 : il faut choisir le symbole séparateur. Dans la majorité des cas, il faut choisir la virgule ou le point virgule. Dans l’exemple ci-dessus, j’ai coché « virgule » et « espace » (afin de supprimer les espaces après la virgule).
Appuyer sur « Suivant » pour valider les étapes 2/3.
Dans l’étape 3/3, il est possible de définir le format des données de chaque colonne (exemple : date, texte, etc.). Si l’on n’a pas d’exigences spécifiques, il suffit simplement de laisser le bouton radio « standard » coché et de terminer l’opération en appuyant sur le bouton « Terminer ».
La conversion s’effectue. Il reste ensuite à enregistrer le document. Aller dans « Enregistrer sous », puis dans « classeur Excel ». Dans la fenêtre qui s’ouvre, vous pouvez nommer le document et choisir le format de l’extension comme par exemple « .xls » ou « .xlsx ». Enfin, appuyer sur le bouton « Enregistrer ».
Capture d’écran du logiciel Excel: rendu final, à l’aide de la conversion de fichier.
Dans le cas du fichier csv ci-après : je choisis seulement la séparation par virgule, et laisse identificateur de texte avec des guillemets « , pour que cela s’affiche en colonnes. Il ne faut pas que je retire les espaces (donc je ne coche pas espace), pour avoir le texte Date de création avec espace et en une colonne et non en 3 colonnes « date », « de », « creation ».
Comme avec la plupart des importations de bases de données, cet exercice nécessite l’importation d’un fichier de format .csv (voir image ci-dessous).
Mais si vous ne souhaitez pas importer votre fichier dans Outlook ou dans tout autre base de données mais plutôt l’utiliser directement dans Excel. Comment faire pour convertir votre fichier .csv en tableau Excel standard?
Rien de plus simple!
Sélectionnez votre première colonne de données
Rendez-vous dans le menu DATA (Données en français)
Cliquez sur Text to Column (Convertir en français)
Cliquez sur Delimited (Délimité en français)
Cliquez sur Next (Suivant en français)
À l’écran suivant, vous devez indiquer à Excel comment sont délimitées les données dans votre fichier .csv. Par défaut, Excel suppose que les données sont délimitées par un espace. Dans notre exemple, ce n’est pas le cas donc, si vous jetez un coup d’oeil à la fenêtre d’aperçu d’Excel (en jaune dans l’image ci-bas), vous voyez que les données n’ont pas été converties.
Dans notre exemple, les données de notre fichier .csv sont plutôt délimitées par des ” . Il faut donc cocher la case Other (Autre en français) et indiquer ” . On verra les données bien séparées en colonne dans la fenêtre d’aperçu d’Excel (en jaune dans l’image ci-bas).
Cliquez ensuite sur Next (Suivant en français)
Cliquez finalement sur Finish (Terminer en français)
Notez que vous pouvez par la suite enregistrer votre nouveau fichier dans un format .xls plutôt que .csv. Pour ce faire, vous aller dans le menu Save As (Enregistrer sous en français) et vous choisissez, sous Save as Type (Enregistrer dans le format en français), le format .xls à l’aide du menu déroulant (voir image ci-dessous).
Calcul de l’ancienneté du personnel salarié ou consultant.
Un consultant externe peut travailler 2 ans maximum au sein d’une société.
Avoir dans la même cellule le nombre d’années, de mois et de jours de la manière suivante : 1a2m13j
Ici intervient une des fonctions secrètes d’Excel qui ne figure pas dans l’aide et les didacticiels.
Vous pouvez l’utiliser pour répondre à cette question et plus généralement pour calculer l’écart entre deux dates (Ancienneté, Âges, etc)
Dans la cellule A1, entrez la formule: =AUJOURDHUI()
qui donne la date d’aujourd’hui 15/05/06 (à la date de cette réponse).
Dans la cellule A2, entrez la date qui vous intéresse. (Dans votre cas, c’est la date d’embauche de l’employé) Par exemple 25/01/02
Dans la cellule ou vous souhaitez voir apparaître le résultat, entrez: =DATEDIF($A$2;$A$1; »y »)& » ans « &DATEDIF($A$2;$A$1; »ym »)& » mois « &DATEDIF($A$2;$A$1; »md »)& » jours «
Le résultat renvoyé est: 4 ans 3 mois 20 jours
Pour obtenir selon votre demande la réponse 4a3m20j: =DATEDIF($A$2;$A$1; »y »)& »a »&DATEDIF($A$2;$A$1; »ym »)& »m »&DATEDIF($A$2;$A$1; »md »)& »j »
Extraction du prénom et du nom parfois sans le prénom
Mes cellules contiennent 1 ou 2 informations :
– Le prénom toujours en minuscule sauf la première lettre (exemples de formulation : Patrice ou Jean-Paul).
– Le nom toujours en majuscule (exemples de formulation : DUBOIS ou MARTIN-DURAND ou DE LAMAISON).
Ce qui pose problème est que le prénom n’est pas toujours notifié.
Certaines cellules ne contiennent donc que le nom toujours en majuscule (DUBOIS ou MARTIN-DURAND ou DE LAMAISON.
Comment extraire le prénom et le nom dans 2 cellules distinctes en tenant compte de cette difficulté (absence de prénom possible) ?
Idée : Dans le cas où dans les cellules sans prénom figurerait un message d’erreur du type #N/A ou #VALEUR!, il serait peut-être intéressant de remplacer ce message d’erreur par un tiret (« -« ).
Nom =SIERREUR(MAJUSCULE(DROITE($A2;NBCAR($A2)-TROUVE( » « ;$A2)));MAJUSCULE(DROITE($A2;NBCAR($A2))))
Formules à mettre sur la même ligne que les deux Prénom et NOM de la cellule en Ax
Pour le prénom, ta formule fonctionne bien, sauf lorsqu’il n’y a pas de prénom ET que le nom est à particule (comme avec DE LAMAISON). EXCEL prend la particule (qu’elle soit écrite en minuscule ou en majuscule) pour le prénom.
Pour le nom, ta formule fonctionne bien, sauf lorsqu’il n’y a pas de prénom ET que le nom est à particule. EXCEL ignore la particule et ne figure que le la deuxième partie du nom.
Aucun soucis avec un nom simple sans le prénom notifié (la case du prénom est vide sans message d’erreur).
J’ai ajouté une vérification de casse sur le deuxième caractère, traité comme nom ou prénom selon le cas. Ca fonctionne bien dans les cas évoqués, mais je n’ai pas traité si les prénoms composés étaient séparés par un espace.
Pour le prénom =SI(EXACT(STXT(A2;2;1);MAJUSCULE(STXT(A2;2;1))); » »;SIERREUR(GAUCHE(A2;TROUVE » « ;A2));A2))
Nom =SIERREUR(DROITE(A2;NBCAR(A2)-SI(EXACT(STXT(A2;2;2);MAJUSCULE(STXT(A2;2;2)));0;TROUVE( » « ;A2)));DROITE(A2;NBCAR(A2)))
Je n’ai pas d’espace dans les prénoms composés.
En conséquence, tes 2 formules fonctionnent très bien et répondent exactement à mes demandes et contraintes.
De mon côté, après de nombreux tests, j’ai trouvé également trouvé 2 formules qui répondent à mes nombreuses exigences :
Pour le prénom : =SI(EXACT(MAJUSCULE(A1);A1); »-« ;GAUCHE(A1;TROUVE( » « ;A1)))
Pour le nom : =SI(EXACT(MAJUSCULE(A1);A1);A1;DROITE(A1;NBCAR(A1)-TROUVE( » « ;A1))
En cas d’absence de prénom, EXCEL place un tiret dans la cellule « Prénom ».
Remarque : Nous faisons de notre mieux pour vous fournir le contenu d’aide le plus récent aussi rapidement que possible dans votre langue. Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez-vous nous indiquer en bas de page si ces informations vous ont aidé ? Voici l’article en anglais à des fins de référence aisée.
Cet article décrit la syntaxe de formule et l’utilisation de la fonction SOUS.TOTAL dans Microsoft Excel.
Description
Renvoie un sous-total dans une liste ou une base de données. Il est généralement plus facile de créer une liste comportant des sous-totaux à l’aide de la commande Sous-total du groupe Contour dans l’onglet Données de l’application de bureau Excel. Une fois cette liste de sous-totaux créée, vous pouvez la modifier en changeant la fonction SOUS.TOTAL.
Syntaxe
SOUS.TOTAL(no_fonction,réf1,[réf2],…)
La syntaxe de la fonction SOUS.TOTAL contient les arguments suivants :
no_fonction Obligatoire. Le nombre 1 à 11 ou 101 à 111 qui spécifie la fonction à utiliser pour calculer le sous-total. 1 à 11 inclut les lignes masquées manuellement, tandis que 101 à 111 les exclut ; les cellules filtrées sont toujours exclues.
no_fonction
(comprend les valeurs masquées)
no_fonction
(ignore les valeurs masquées)
Fonction
1
101
MOYENNE
2
102
NB
3
103
NBVAL
4
104
MAX
5
105
MIN
6
106
PRODUIT
7
107
ECARTYPE
8
108
ECARTYPEP
9
109
SOMME
10
110
VAR
11
111
VAR.P
réf1 Obligatoire. Première référence ou plage nommée dont vous souhaitez calculer le sous-total.
réf2;… Facultatifs. Plages ou références nommées 2 à 254 dont vous souhaitez calculer le sous-total.
Remarques
S’il existe d’autres sous-totaux dans réf1, réf2,… (ou sous-totaux imbriqués), ces sous-totaux imbriqués ne sont pas pris en compte afin d’éviter les doublons.
Pour les constantes no_fonction de 1 à 11, la fonction SOUS.TOTAL comprend les valeurs des lignes masquées par la commande Masquer les lignes dans le sous-menu Masquer et afficher de la commande Format du groupe Cellules dans l’onglet Accueil de l’application de bureau Excel. Utilisez ces constantes pour obtenir le sous-total des nombres masqués et non masqués dans une liste. Pour les constantes no_fonction de 101 à 111, la fonction SOUS.TOTAL ignore les valeurs des lignes masquées par la commande Masquer les lignes. Utilisez ces constantes pour obtenir uniquement le sous-total des nombres non masqués d’une liste.
La fonction SOUS.TOTAL ignore toute ligne non comprise dans le résultat d’un filtre, peu importe la constante no_fonction utilisée.
La fonction SOUS.TOTAL est conçue pour les colonnes de données, ou plages verticales. Elle n’est pas conçue pour les lignes de données, ou plages horizontales. Par exemple, lorsque vous effectuez le sous-total d’une plage horizontale au moyen de la constante no_fonction 101 ou plus, telle que SOUS.TOTAL(109,B2:G2), les colonnes masquées n’influencent pas le sous-total. En revanche, les lignes masquées dans une plage verticale influenceront le sous-total.
Si l’une des références est une référence 3D, la fonction sous. total renvoie le #VALUE! #VALEUR!.
Exemple
Copiez les données d’exemple dans le tableau suivant, et collez-le dans la cellule A1 d’un nouveau classeur Excel. Pour que les formules affichent des résultats, sélectionnez-les, appuyez sur F2, et sur Entrée. Si nécessaire, vous pouvez adapter la largeur des colonnes pour afficher toutes les données.
Données
120
10
150
23
Formule
Description
Résultat
=SOUS.TOTAL(9;A2:A5)
Somme du sous-total des cellules A2:A5, avec 9 comme premier argument.
303
=SOUS.TOTAL(1;A2:A5)
Moyenne du sous-total des cellules A2:A5, avec 1 comme premier argument.
75.75
Remarques
La fonction SOUS.TOTAL nécessite toujours un argument numérique (de 1 à 11, de 101 à 111) comme premier argument. Cet argument numérique est appliqué au sous-total des valeurs (plages de cellules, plages nommées) spécifiées sous la forme des arguments qui suivent.
Microsoft Excel enregistre les dates sous la forme de numéros séquentiels afin qu’elles puissent être utilisées dans des calculs. Par défaut, le 1er janvier 1900 correspond au numéro séquentiel 1, et le 1er janvier 2012 correspond au numéro séquentiel 40909 car 40,909 jours se sont écoulés depuis le 1er janvier 1900.
Renvoie le nombre de jours ouvrés entiers compris entre deux dates à l’aide de paramètres identifiant les jours du week-end et leur nombre. Les jours du week-end et ceux qui sont désignés comme des jours fériés ne sont pas considérés comme des jours ouvrés.
Ex : =(NB.JOURS.OUVRES.INTL(J29;K29;1;($S$2:$S$11)))
Mettre des points ; à chaque étape de la formule et fermer les parenthèses finales.
Exemple : date d’entrée et date de sortie du consultant, hors w-e, hors jours fériés = nb de jours travaillés x TJM (taux journalier moyen)
Figer dans un onglet à part la liste des jours fériés de l’année en sélectionnant la liste des dates des jours fériés
(Touche F4 dollars sur les colonnes (Lettre) et les lignes (chiffres))
La syntaxe de la fonction NB.JOURS.OUVRES.INTL contient les arguments suivants :
date_début et date_fin Obligatoire. Dates pour lesquelles la différence doit être calculée. La valeur de date_début peut être antérieure, identique ou ultérieure à celle de date_fin.
week_end Facultatif. Indique les jours de la semaine qui représentent les jours du week-end et qui ne sont pas compris dans le nombre de jours ouvrés entiers inclus entre date_début et date_fin. L’argument week-end est un numéro de week-end ou une chaîne qui indique la date du week-end.
Les valeurs de numéro de week-end indiquent les jours de week-end suivants : numéro de week-end
Les valeurs de chaîne du week-end comportent sept caractères, et chaque caractère de la chaîne représente un jour de la semaine qui commence par lundi. 1 représente un non-jours. Seuls les caractères 1 et 0 sont autorisés dans la chaîne. Si vous tapez 1111111, le résultat renvoyé sera toujours 0.
Par exemple, 0000011 se traduit par un week-end composé du samedi et du dimanche.
jours_fériés Facultatif. Ensemble d’une ou de plusieurs dates à exclure du calendrier des jours ouvrés. jours_fériés sera une plage de cellules contenant les dates ou une constante matricielle des valeurs sérielles qui représentent ces dates. Le tri des dates ou des valeurs sérielles de l’argument jours_fériés peut être arbitraire.
Remarques
Si l’argument date_début est ultérieur à l’argument date_fin, la valeur renvoyée sera négative, et l’amplitude correspondra au nombre de jours ouvrés entiers.
Si start_date n’est pas valide pour la valeur de base de la date actuelle, NB. jours. ouvres. INTL renvoie le #NUM ! .
Si end_date n’est pas valide pour la valeur de base de la date actuelle, NB. jours. ouvres. INTL renvoie le #NUM ! .
Si une chaîne du week-end est de longueur incorrecte ou contient des caractères non valides, NB. jours. ouvres. INTL renvoie le #VALUE ! #VALEUR!.
Exemple
Copiez les données d’exemple dans le tableau suivant, et collez-le dans la cellule A1 d’un nouveau classeur Excel. Pour que les formules affichent des résultats, sélectionnez-les, appuyez sur F2, et sur Entrée. Si nécessaire, vous pouvez modifier la largeur des colonnes pour afficher toutes les données.
Permet d’obtenir 22 futurs jours ouvrés. Soustrait 9 jours de week-end non ouvrés (5 samedis et 4 dimanches) des 31 jours totaux entre les deux dates. Par défaut, le samedi et le dimanche sont considérés comme non ouvrés.
Permet d’obtenir 22 futur jours ouvrés en soustrayant 10 jours non ouvrés (4 vendredis, 4 samedis, 2 jours fériés) des 32 jours compris entre le 1er janvier 2006 et le 1er février 2006. Utilise l’argument 7 pour le week-end, ce qui correspond à vendredi et samedi. Il y a également deux jours fériés au cours de cette période.
Permet d’obtenir 22 futurs jours ouvrés. Même période que dans l’exemple juste au-dessus, mais avec le dimanche et le mercredi comme jours de week-end.
20
Remarque : Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez vous nous dire si les informations vous ont été utiles ? Voici l’article en anglais pour référence.
Fonction NB.JOURS.OUVRES.INTL
Excel pour Office 365Excel pour Office 365 pour MacExcel pour le webExcel 2019Plus…
Renvoie le nombre de jours ouvrés entiers compris entre deux dates à l’aide de paramètres identifiant les jours du week-end et leur nombre. Les jours du week-end et ceux qui sont désignés comme des jours fériés ne sont pas considérés comme des jours ouvrés.
La syntaxe de la fonction NB.JOURS.OUVRES.INTL contient les arguments suivants :
date_début et date_fin Obligatoire. Dates pour lesquelles la différence doit être calculée. La valeur de date_début peut être antérieure, identique ou ultérieure à celle de date_fin.
week_end Facultatif. Indique les jours de la semaine qui représentent les jours du week-end et qui ne sont pas compris dans le nombre de jours ouvrés entiers inclus entre date_début et date_fin. L’argument week-end est un numéro de week-end ou une chaîne qui indique la date du week-end.
Les valeurs de numéro de week-end indiquent les jours de week-end suivants :
Numéro de week-end
Jours de week-end
1 ou omis
samedi, dimanche
2
dimanche, lundi
3
lundi, mardi
4
mardi, mercredi
5
mercredi, jeudi
6
jeudi, vendredi
7
vendredi, samedi
11
dimanche uniquement
12
lundi uniquement
13
mardi uniquement
14
mercredi uniquement
15
jeudi uniquement
16
vendredi uniquement
17
samedi uniquement
Les valeurs de chaîne du week-end comportent sept caractères, et chaque caractère de la chaîne représente un jour de la semaine qui commence par lundi. 1représente un non-jours. Seuls les caractères 1 et 0 sont autorisés dans la chaîne. Si vous tapez 1111111, le résultat renvoyé sera toujours 0.
Par exemple, 0000011 se traduit par un week-end composé du samedi et du dimanche.
jours_fériés Facultatif. Ensemble d’une ou de plusieurs dates à exclure du calendrier des jours ouvrés. jours_fériés sera une plage de cellules contenant les dates ou une constante matricielle des valeurs sérielles qui représentent ces dates. Le tri des dates ou des valeurs sérielles de l’argument jours_fériés peut être arbitraire.
Remarques
Si l’argument date_début est ultérieur à l’argument date_fin, la valeur renvoyée sera négative, et l’amplitude correspondra au nombre de jours ouvrés entiers.
Si start_date n’est pas valide pour la valeur de base de la date actuelle, NB. jours. ouvres. INTL renvoie le #NUM ! .
Si end_date n’est pas valide pour la valeur de base de la date actuelle, NB. jours. ouvres. INTL renvoie le #NUM ! .
Si une chaîne du week-end est de longueur incorrecte ou contient des caractères non valides, NB. jours. ouvres. INTL renvoie le #VALUE ! #VALEUR!.
Exemple
Copiez les données d’exemple dans le tableau suivant, et collez-le dans la cellule A1 d’un nouveau classeur Excel. Pour que les formules affichent des résultats, sélectionnez-les, appuyez sur F2, et sur Entrée. Si nécessaire, vous pouvez modifier la largeur des colonnes pour afficher toutes les données.
Permet d’obtenir 22 futurs jours ouvrés. Soustrait 9 jours de week-end non ouvrés (5 samedis et 4 dimanches) des 31 jours totaux entre les deux dates. Par défaut, le samedi et le dimanche sont considérés comme non ouvrés.
Permet d’obtenir 22 futur jours ouvrés en soustrayant 10 jours non ouvrés (4 vendredis, 4 samedis, 2 jours fériés) des 32 jours compris entre le 1er janvier 2006 et le 1er février 2006. Utilise l’argument 7 pour le week-end, ce qui correspond à vendredi et samedi. Il y a également deux jours fériés au cours de cette période.
Permet d’obtenir 22 futurs jours ouvrés. Même période que dans l’exemple juste au-dessus, mais avec le dimanche et le mercredi comme jours de week-end.
20
Remarque : Cette page a été traduite automatiquement et peut donc contenir des erreurs grammaticales ou des imprécisions. Notre objectif est de faire en sorte que ce contenu vous soit utile. Pouvez vous nous dire si les informations vous ont été utiles ? Voici l’article en anglais pour référence.