Je recense ici des petites astuces pour le tableur LibreOffice Calc que l'on est amené à utiliser régulièrement mais que l'on peut oublier.
Page pour les fonctions (intéressant pour les matrices par exemple) : https://help.libreoffice.org/latest/fr/text/scalc/01/04060107.html
Remarque : pour accéder directement à la dernière colonne ou la dernière ligne du tableur, utiliser le raccourci CTRL + Flèche BAS ou CTRL + Flèche DROITE. En rajoutant la touche SHIFT, on sélectionne en même temps. https://wiki.documentfoundation.org/Faq/Calc/147/fr
A- Suppression des espaces en fin de cellules :
- Menu "Édition" -> "Rechercher et remplacer..." (raccourci CTRL+H).
- Dans le champ "Rechercher", saisir "\s+$" ou " +$" (pour supprimer un seul espace à la fin, on utilisera l'expression "\s$" ou " $", l'espace peut se coder "\s")
- Dans le champ "Remplacer", laisser vide
- Cocher la case "Expressions régulières"
Remarque : le choix des autres options dépendra du besoin.
B- Suppression des espaces en début de cellules :
- Menu "Édition" -> "Rechercher et remplacer..." (raccourci CTRL+H).
- Dans le champ "Rechercher", saisir "^\s" (l'espace se code "\s")
- Dans le champ "Remplacer", laisser vide
- Cocher la case "Expressions régulières"
C- Recherches particulières :
Dans l'astuce précédente, on a utilisé ce qu'on appelle des expressions régulières (ou REGEX) pour rechercher des termes bien précis. Pour les utiliser il est nécessaire de vérifier que la fonctionnalité est bien activée en allant dans dans Outils > Options > LibreOffice Calc > Calcul > Choisir "Autoriser les expressions régulières dans les calculs" :
Ces expressions régulières sont des outils très puissants de recherche... à condition de maîtriser leur syntaxe... On peut trouver des infos ici :
- https://help.libreoffice.org/Common/List_of_Regular_Expressions/fr
- https://wiki.openoffice.org/wiki/Documentation/FR/Expressions_Regulieres_dans_Writer
- https://data.hypotheses.org/959
- Pour que ça concerne toutes les cellules, taper dans le champ Recherche : ".*" et cocher "Expressions régulières"
- Si l'on veut remplacer le contenu de toutes les cellules, taper dans le champ Recherche : ".+" et cocher "Expression régulières".
- Pour rajouter un préfixe P au début de toutes les cellules, taper dans le champ Remplacer : "P&" et cocher "Expressions régulières"
- Exemple d'utilisation pour compter les cellules qui commencent par le terme "lapin" : =NB.SI(A1:C5;"lapin.*"). La formule comptera toutes les cellules ayant "lapin" au début, ce qui incluera "lapin1", "lapine", ...
D- Supprimer les doublons dans une colonne :
Il faut passer par le menu -> "Données" -> "Plus de filtres" -> "Filtre spécial...", puis développer la partie "Options" et cocher la case "sans doublon".
Plus de détails ici : https://help.libreoffice.org/latest/fr/text/scalc/guide/specialfilter.html
E- Repérer les doublons dans une colonne :
Il peut être utile de repérer dans une colonne les doublons. Pour cela on va utiliser un formatage conditionnel avec une formule du type :NB.SI($A$2:$A$566;A2)>1
F- Compter le nombre de résultats affichés/filtrés d'une plage de cellules :
- Lorsqu'on filtre une plage, il peut être très utile de savoir combien on a de résultats affichés (c'est-à-dire sans prendre en compte les données masquées). Pour cela on utilise la fonction "SOUS-TOTAL" avec la valeur "3" comme premier paramètre.
Par exemple pour savoir combien on a de résultats pour les lignes 2 à 348, il suffit d'appliquer cette formule sur une colonne contenant des données (ici colonne A) :
=SOUS.TOTAL(3;A2:A348) - Lorsqu'on ne veut qu'afficher les résultats des cellules affichées après un masquage manuel d'autres cellules (c'est différent de cellules filtrées !), on utilisera la formule :
=SOUS.TOTAL(109; C3;C6;C9;C12)
Remarque : on peut utiliser une plage de cellules ou des cellules isolées (non contiguës) en les séparant par des points virgules (comme dans la formule ci-dessus).
Plus d'info sur la syntaxe de la fonction SOUS.TOTAL ici : https://help.libreoffice.org/6.4/fr/text/scalc/01/04060106.html?DbPAR=CALC#bm_id3165633
G- Filtrer pour ne pas afficher les doublons dans une plage de cellules :
Il peut être utile de ne pas voir apparaître les doublons dans une liste. Pour cela, on sélectionne la plage qui contient les doublons,
puis on va dans le menu "Données" -> "Plus de filtres" -> "Filtre standard...". Et là, on choisit dans "Nom de champ" la valeur "-aucun(e)-" , dans le champ "Valeur" la valeur "Non vide". Puis dans la partie "Options" que l'on déroule en appuyant sur la flèche, on coche (ou on décoche) "La plage contient des étiquettes de colonne", et on coche "Sans doublons". Puis on clique sur le bouton "OK".
Remarque : Après ce filtrage, il est possible d'obtenir le nombre de données uniques (sans doublons) avec la formule : =SOUS.TOTAL(103;plage)
Autre possibilité intéressante avec le filtre standard :
Il est possible de récupérer les différentes valeurs uniques d'une colonne dans une autre. Pour cela, on indique "aucune" dans le nom de champ et dans les options en bas on coche "Sans doublons" et "Copier le résultat vers :" en indiquant où on veut cette liste.
H- Mémoriser des filtres, copier les données issues d'un filtre :
Pour cela, il faut passer par le menu "Données" -> "Plus de filtres" -> "Filtre spécial...".
Vous pouvez aller voir cette vidéo de Pierre-Yves Samyn (un spécialiste de LibreOffice et des macros) : https://hooktube.com/watch?v=3o7FVkdNhZw dans laquelle on voit toute la puissance des filtres pour sélectionner très précisément des données selon des critères fixées !
I- Comptage, somme avec conditions :
Voir cette page : https://wiki.documentfoundation.org/FR/Calc/Sommes_et_calculs_conditionnels
Pour faire la somme d'une plage de cellules dans la colonne E, avec un critère vide "" dans la colonne A, il faut taper une formule de ce genre en formule MATRICIELLE (la formule "= SOMME.SI(A7:A41;"";E7:E41)" ne fonctionne pas) :
{=SOMME.SI(A7:A41="";1;E7:E41)}
J- Copier les cellules visibles uniquement :
Plus nécessaire avec les versions récentes de LibreOffice (peut-être la 6.4 mais à vérifier) en passant par le collage spécial. Mais ça n'est valable que pour les lignes masquées automatiquement avec l'autofiltre ou des filtres standard et spéciaux (voir l'évolution avec la version 7.4 ci-dessous)
La seule façon de réussir à copier/coller des cellules visibles (sans prendre en compte celles qui sont masquées) est d'installer l'extension "Copy only visible cells" téléchargeable ici : https://extensions.libreoffice.org/en/extensions/show/copy-only-visible-cells
Depuis la version 7.4 : plus besoin de l'extension ci-dessus car il est possible de ne copier que les cellules visibles (notamment quand des colonnes ou des lignes sont masquées manuellement). Plus d'info ici : https://help.libreoffice.org/latest/fr/text/scalc/guide/cellcopy.html
K- Formule pour cellule contenant un morceau de texte et plusieurs conditions :
Il faut utiliser la fonction CHERCHE. Par contre, si la valeur recherchée n'est pas trouvée, la cellule affichera une erreur #VALEUR.
Par exemple, si l'on veut plusieurs conditions de recherche, on peut utiliser la fonction SI.CONDITIONS. Mais là aussi, il faut prévoir à avoir toujours une condition VRAI à la fin pour éviter d'afficher un message d'erreur. Voir cet exemple où un recherche 2 morceaux de textes :
=SI.CONDITIONS(NON(ESTERR(CHERCHE("texteA";A2)));"Oui";NON(ESTERR(CHERCHE("TexteB";A2)));"Non";VRAI();"")
L- Récupérer l'arborescence, le nom du fichier Calc (sous Ubuntu) :
https://eric.bugnet.fr/libre-office-calc-nom-du-fichier-et-arborescence/
=STXT(CELLULE("nomfichier");9;TROUVE("§";SUBSTITUE(CELLULE("nomfichier");"/";"§";NBCAR(CELLULE("nomfichier"))-NBCAR(SUBSTITUE(CELLULE("nomfichier");"/";""))))-9)
M- Suppression des apostrophes en début de cellules :
Il arrive parfois que lors d'un collage, d'une transposition de données, qu'une apostrophe apparaisse au début de la colonne. Du coup, les valeurs numériques sont considérées comme du texte ce qui est problématique. Astuce trouvée ici : http://www.jmagnin.fr/lo-calc-suppr-apostrophe-formatage.html
- Menu "Édition" -> "Rechercher et remplacer..." (raccourci CTRL+H).
- Dans le champ "Rechercher", saisir "^[0-9]"
- Dans le champ "Remplacer", saisir "&"
- Cocher la case "Expressions régulières"
N- Comptage des valeurs distinctes dans une plage de données :
On peut avoir besoin de compter le nombre de valeurs uniques (sans doublons) dans une plage. Il n'y a pas de fonction directe le permettant mais on peut y arriver par cette formule (il faut remplacer "plage" par la zone de cellules concernée) :
=SOMMEPROD((plage<>"")/(NB.SI(plage;plage)+(plage="")))
Autre solution identique et plus courte :
=SOMMEPROD(1/NB.SI(plage;plage))
Attention : il ne faut pas de cellules vides dans la plage !
Attention aussi pour les chaînes de caractères aux espaces en début ou en fin de phrase qui peuvent causer des erreurs.
Source : https://www.it-swarm.dev/fr/libreoffice/differentes-valeurs-dans-une-colonne/959748672/
SI ON A DES CELLULES VIDES, dans la plage, il faudra utiliser la formule MATRICIELLE ci-dessous, en validant avec la combinaison de touches CTRL+MAJ+Entrée :
=SOMME(SI(ESTVIDE(B7:B1006);"";1/NB.SI($B$7:$B$1006;B7:B1006)))
N-bis- Comptage des valeurs distinctes dans une plage de données FILTRÉES :
Si l'on veut compter le nombre d'occurrences UNIQUES dans une colonne (ici la G) que l'on filtre via l'autofiltre, voici un exemple de formule utile qui n'a pas besoin d'être saisie comme une matrice (même s'il s'agit de calculs matriciels) :
=SOMMEPROD( SOUS.TOTAL(103;DECALER(G3;LIGNE(G3:G277)-LIGNE(G3);0)) * (G3:G277<>"") / ( PRODUITMAT(G3:G277=TRANSPOSE(G3:G277); SOUS.TOTAL(103;DECALER(G3;LIGNE(G3:G277)-LIGNE(G3);0))) + 1 - SOUS.TOTAL(103;DECALER(G3;LIGNE(G3:G277)-LIGNE(G3);0)) ))
SOUS.TOTAL(103;DECALER(G3;LIGNE(G3:G277)-LIGNE(G3);0))représente un vecteur de 275 lignes contenant 1 pour les lignes affichées et 0 pour les lignes masquées : ceci est permis avec la fonction DECALER (sinon nous n'obtiendrions qu'une valeur). On retrouve ce terme plusieurs fois dans la formule. On l'appellera V (qui correspond à la visibilité). Si la ligne est visible V=1.- LIGNE(G3:G277)-LIGNE(G3) produit un tableau {0;1;2;...;274} de 275 valeurs correspondant aux lignes à prendre en compte.
- DECALER renvoie un tableau de 275 valeurs, chacune pointant sur une cellule de la plage filtrée (débutant en G3)
* (G3:G277<>"")permet de ne prendre en compte que les cellules ayant un contenu : il s'agit d'une sécurité au cas où.- La partie dénominateur :
PRODUITMAT( G3:G277=TRANSPOSE(G3:G277) ; SOUS.TOTAL(103;DECALER(G3;LIGNE(G3:G277)-LIGNE(G3);0)) )permet d'obtenir un vecteur de 275 valeurs. On l'appellera N. Cela donne le nombre de lignes visibles dont la valeur est identique à celle de la ligne i.G3:G277=TRANSPOSE(G3:G277)permet d'obtenir une matrice carrée de 275 x 275 ou la cellule (i,j) vaut 1 (est VRAI) si i=jPRODUITMAT(matrice carrée de 275x275 valeurs ; vecteur V de 275 valeurs)
- On a donc un dénominateur de la forme :
N + 1 - Vqui n'est jamais nul (division par zéro). Il correspond au nombre de fois que cette valeur est affichée.
Si la ligne est visible → V = 1 → N + 1 - V = N, le dénominateur correspond au nombre de fois où la valeur est visible.
Si la ligne est masquée → V = 0 → N + 1 - V = N + 1, évite que le dénominateur soit nul mais de toute façon dans ce cas le numérateur sera égal à 0 (V = 0).
- Au final, pour chaque ligne, on a un calcul de cette forme :
V x NON_Vide/ (N + 1 - V)avec V qui vaut 1 ou 0 selon sa visibilité.
La fonction SOMMEPROD permet d'additionner ces 275 calculs correspondant à la part de l'apparition de chaque valeur (une ligne masquée comptant pour 0). Par exemple, une valeur apparaissant 5 fois comptera pour 1/5 à chaque apparition.
O- Compter des cas répondant à plusieurs critères avec des calculs matriciels :
Supposons que nous voulions compter combien de lignes correspondent à des mêmes valeurs dans différentes colonnes. On va utiliser pour ça des calculs matriciels avec la fonction SOMMEPROD ou SOMME. Voir cette page pour avoir des info sur les matrices :https://help.libreoffice.org/latest/fr/text/scalc/01/04060107.html?DbPAR=CALC.
ATTENTION pour indiquer un calcul matriciel, il faudra valider avec la combinaison de touches CTRL+MAJ+Entrée. Cela rajoutera dans la formule des accolades { }. De la même façon, si on veut éditer une formule matricielle, il faudra sélectionner la cellule qui contient la formule matricielle et appuyer sur les touches CTRL + / (division du pavé numérique) ce qui sélectionnera l'ensemble des cellules de la matrice et appuyer sur la touche F2.
Solution permettant de ne compter que le nombre de cellules filtrées répondant à un critère (sorte de combinaison de SOUS.TOTAL et NB.SI) : https://forum.openoffice.org/fr/forum/viewtopic.php?p=350049&sid=3f3d1108fe20989a6dafe5bc35277a31#p350049.
Il faudra rajouter pour cette méthode, une colonne avec une formule à recopier sur toute les lignes. Par exemple, dans la colonne D et sur la première ligne 5, la formule sera : =SOUS.TOTAL(3;D5) et on recopiera cette formule sous les lignes suivantes. Cette formule renvoie 1 pour les cellules non masquées.
Ensuite, la formule à renseigner sera de cette forme en ciblant une valeur (on peut aussi indiquer une cellule contenant cette valeur) dans la colonne B : =SOMMEPROD(($D$5:$D$134=1)*(B$5:B$134="texte ciblé"))
P- Compter le nombre de cellules commençant par une chaîne de caractères :
Supposons que l'on veuille savoir combien on a de cellules dans une colonne commençant par le texte "Arrivé⋅e le". Il ne faudra pas avoir oublié d'activer les expressions régulières dans le paramétrage de LibreOffice et il faudra saisir une expression du style (ne pas oublier le point avant l'astérisque ".*") :
=NB.SI(E$8:E$70;"Arrivé⋅e le.*")
Source : https://help.libreoffice.org/latest/fr/text/scalc/01/04060181.html?&DbPAR=WRITER&System=UNIX#countif
Autre solution avec la fonction SOMMEPROD (qui ne demande pas d'activer les expressions régulières dans les paramétrages de LibreOffice Calc pour le calcul) :
=SOMMEPROD(GAUCHE($A$3:$I$24;3)="011")
La formule ci-dessus compte sur la plage A3:I24, le nombre de cellule dont le texte comment par "011" (début d'un UAI).
Q- Compter seulement le nombre de cellules filtrées répondant à un critère :
La fonction SOUS.TOTAL permet de travailler uniquement sur les cellules affichées après masquage ou filtrage. Malheureusement cette fonction ne prend pas en compte des critères dans une formule du style SOUS.TOTAL(103;D2:D100=1) pour recenser le nombre de cellules non masquées contenant la valeur 1 sur la plage D2:A100.
Il faut donc trouver un contournement. Merci à OOotremer971 qui m'a donné la solution dans ce fil : https://forum.openoffice.org/fr/forum/viewtopic.php?p=340302&sid=87adb2244310390419e0bb34cc053725#p340302
ATTENTION, il faudra stipuler que cette formule est une matrice pour que ça fonctionne !!!
Pour indiquer un calcul matriciel, il faut valider avec la combinaison de touches CTRL+MAJ+Entrée. Cela rajoute dans la formule des accolades { }. De la même façon, si on veut éditer une formule matricielle, il faut sélectionner la cellule qui contient la formule matricielle et appuyer sur les touches CTRL + / (division du pavé numérique) ce qui sélectionne l'ensemble des cellules de la matrice et appuyer sur la touche F2.
=NB.SI(SI(SOUS.TOTAL(103;INDIRECT("D"&LIGNE($A$2:$A$100)));$D$2:$D$100;"");1)
R- Comportement de Calc dans les calculs et formules ainsi que les filtres :
Selon les options cochées dans les préférences de Calc, les calculs ou les formules ne se comporteront pas de la même façon notamment quant aux résultats des filtrages ou de l'autofitre ! En particulier selon que vous souhaitez travailler, faire des recherches sur la cellule entière ou pas. Cela dépend de vos choix dans les préférences de Calc :
Par exemple, si l'option ci-dessus n'est pas cochée, si vous filtrez une colonne sur l'entrée "CP", vous obtiendrez toutes les entrées contenant "CP" comme par exemple "Mi-CP", ce qui est problématique. Pour éviter cela, il faut cocher l'option "Les critères de recherche = et <> doivent s'appliquer à des cellules entières". Plus d'info ici : https://bugs.documentfoundation.org/show_bug.cgi?id=157878 (avec peut-être une amélioration à venir de ce comportement pas très explicite que j'avais pris pour un bug).
S- Listes déroulantes successives et conditionnelles avec la validation des données :
Avec LibreOffice Calc version 7.6.2, il est possible de faire assez facilement des listes déroulantes successives en réglant la "validité" d'une cellule et en utilisant des plages nommées. Par contre, même si on utilise une formule, dans le champ "Autoriser", il faudra choisir "Plage de cellules" (et non "Personnalisé"). On peut même obtenir des listes déroulantes variables avec des conditions s'appuyant sur la valeur d'une autre cellule.
Dans l'exemple ci-dessous, la liste déroulante de la cellule K2, dépend de la valeur choisie dans la cellule J2 avec une fonction conditionnelle (dans mon exemple, il s'agit de la fonction SI.MULTIPLE). Les 3 listes déroulantes possibles en K2 ont pour source 3 plages nommées (ListeCompétencesCP, ListeCompétencesMi-CP et ListeCompétencesCE1). La source de la cellule K2 est celle-ci :
SI.MULTIPLE(J2;"CP";ListeCompétencesCP;"Mi-CP";ListeCompétencesMiCP;"CE1";ListeCompétencesCE1;"";"")
T- Fonction RECHERCHEX plus performante que RECHERCHEV :
Fonction RECHERCHEX à privilégier dans les nouvelles versions de LibreOffice :
Avec la sortie de LibreOffice 24.8, une fonction XLOOKUP (RECHERCHEX en français) est apparue et est plus souple comparée à RECHERCHEV qui est une ancienne méthode.
Voir cette page : https://help.libreoffice.org/master/fr/text/scalc/01/func_xlookup.html
Exemple de formule permettant de récupérer la dernière valeur dans la plage de la feuille Annuaire "$'Annuaire_2024-2025'.$E$1:$'Annuaire_2024-2025'.$E$483" qui correspond à la plage de la feuille Annuaire $'Annuaire_2024-2025'.$C$1:$'Annuaire_2024-2025'.$C$483 ayant comme valeur $C3 :=XLOOKUP($C3;$'Annuaire_2024-2025'.$C$1:$'Annuaire_2024-2025'.$C$483;$'Annuaire_2024-2025'.$E$1:$'Annuaire_2024-2025'.$E$483;0;-1;-1)
Faire une recherche avec plusieurs critères : c'est possible en adoptant une formule de la forme :=RECHERCHEX(1;($GRIST_Data_Enseignants.$D$2:$GRIST_Data_Enseignants.$D$500 = $C3)*($GRIST_Data_Enseignants.$B$2:$GRIST_Data_Enseignants.$B$500 = "Direction");$GRIST_Data_Enseignants.$A$2:$GRIST_Data_Enseignants.$A$500;0;0;1)
Explications : Ici, je fais une recherche dans la feuille "GRIST_Data_Enseignants" où je cherche un enseignant dont l'UAI correspond à la valeur en $C3 dans la colonne D et qui occupe un poste de "Direction" dans la colonne B → La valeur retournée correspond à son nom qui se trouve dans la colonne A.
Autre exemple d'utilisation : je veux rechercher dans la plage de cellules AA5:KP5 la première valeur qui n'est pas égale à 0 en partant de la fin et renvoyant la valeur correspondante dans la ligne au-dessous AA6:KP6 :=XLOOKUP("^[1-9]\d*$";AA5:KP5;AA6:KP6; ;3;-1)
"^[1-9]\d*$" : REGEX qui recherche un nombre qui est plus grand que 0,
AA5:KP5 : Plage où l'on fait la recherche,
AA6:KP6 : Plage où l'on prend le résultat,
3 : Indique que l'on fait une recherche par REGEX,
-1 : Indique que l'on commence la recherche par la fin.
Ancienne méthode : utilisation de la fonction RECHERCHEV
La fonction RECHERCHEV (VLOOKUP en anglais) permet de retrouver en fonction de la valeur recherchée dans une cellule, une donnée dans la matrice contenant cette valeur dans la première colonne. Voir la page de documentation : https://wiki.documentfoundation.org/FR/Calc:_fonction_RECHERCHEV. La formule est de cette forme :
=RECHERCHEV(A1;$Feuille1.A1:E11;4;0)
Dans l'exemple ci-dessus, la formule cherche dans la 1ère colonne de plage A1:E11 de la Feuille1, la valeur contenue dans la cellule A1. Si la valeur est retrouvée, elle affiche alors la valeur de la 4e colonne (colonne D) qui se trouve sur la même ligne que la valeur retrouvée dans la 1ère colonne. Sinon elle affiche le résultat "#N/D" à cause du dernier paramètre qui indique qu'on cherche une valeur exacte et pas une valeur la plus proche possible (auquel cas on aurait laissé ce dernier paramètre vide ou égal à 1 à condition que la 1ère colonne de matrice soit triée).
Pour éviter d'avoir "#N/D" affiché, on peut utiliser la fonction SINA comme ci-dessous :
=SINA(RECHERCHEV(A1;$Feuille1.A1:E11;4;0);"")
U- Forcer un retour à la ligne dans une cellule :
Pour forcer un retour à la ligne dans une cellule, il faut appuyer sur les touches CTRL+ENTRÉE.
Dans une formule, on rajoutera : & CAR(10) &
V- Afficher le critère utilisé dans un autofiltre :
Lorsqu'on filtre via l'autofiltre avec UN SEUL critère dans une colonne, il peut être intéressant de retrouver ce critère. Pour cela, on peut utiliser la solution proposée par Jean-Louis Cadeillan ici : https://forum.openoffice.org/fr/forum/viewtopic.php?p=179249#p179249.
Il utilise pour cela un colonne supplémentaire "testant" si la ligne est affichée ou masquée par l'autofiltre. Dans cette colonne on utilise la fonction "SOUS.TOTAL" comme =SOUS.TOTAL(3;K2), celle-ci indiquera 0 si la cellule est masquée et 1 si elle est affichée. Et on recopiera cette formule dans les lignes au-dessous.
Ensuite on pourra utiliser la formule suivante pour afficher dans une cellule la valeur ayant servi au filtrage :
=SI(NB.SI($K$22:$K$136;1)=NB(K22:K136);"Résultats globaux non filtrés";INDEX($J$22:$J$136;EQUIV(1;$K$22:$K$136;0)))
W- Supprimer le formatage adresse courriel :
Lorsqu'on renseigne une adresse de courriel, la mise en forme automatique fait que celle-ci est convertie en lien URL. Si l'on désire obtenir toutes les adresses courriel sans ce formatage, on peut l'enlever en sélectionnant la colonne puis en allant dans le menu "Données" -> "Texte en colonne..." et on valide.
X- Isoler une partie de texte en s'appuyant sur un séparateur :
Par exemple, j'ai la cellule A1 contenant le texte suivant : "77 - Produits exceptionnels" et je veux récupérer les 2 parties de part et d'autre de " - " (qui compte pour 3 caractères avec des espaces au début et à la fin).
La formule ci-dessous permet de récupérer "77" :=GAUCHE(A1;CHERCHE(" - ";A1;1)-1)
La formule ci-dessous permet de récupérer "Produits exceptionnels" :=DROITE(A1;NBCAR(A1)-CHERCHE(" - ";A1;1)-2)
Remarque : si le séparateur est un point ".", il faut le faire précéder de "\" qui est le caractère d'échappement sous GNU/Linux.
Y- Calculer selon l'appartenance à une tranche de plusieurs intervalles de valeurs
Il peut s'agir de calculs par exemple d'un forfait ou d'une taxe/impôt. Voici par exemple, le type suivant (le classeur est téléchargeable au bas de cette page) :
Pour avoir le résultat dans la cellule F10, il faudra saisir la formule suivante :=SI(E10<B16;DECALER(C3;EQUIV(E10;B3:B16;1)-1;0);C15+ARRONDI.SUP((E10-B16+1)/1500)*46)
Explications :
EQUIV(E10;B3:B16;1): permet de trouver la ligne de la plage "B3:B16" (il faut que les données de la plage soient rangées dans l'ordre croissant) dans laquelle se trouve la valeur saisie en E10. Pour 5000,00€, comme la valeur exacte n'est pas trouvée, la fonction retourne la ligne contenant la valeur juste inférieure à 5000, ici il s'agit de 4601 qui se trouve en B6. La valeur retournée est 4 car il s'agit de la 4e ligne de la zone "B3:B16" (ne pas confondre avec la ligne du tableau qui elle est la 6).DECALER(C3;EQUIV(E10;B3:B16;1)-1;0): En partant de la cellule C3, on décale de 3 lignes (valeur retournée par la fonction EQUIV qui est égale à 4 et à laquelle on retranche 1) pour que le montant de la colonne C corresponde au palier 4601 de la colonne B. Le dernier paramètre, 0 correspond au décalage de colonne qui n'a pas lieu d'être dans notre cas puisqu'on reste dans la colonne C.- SI : Pour une somme au-dessous de 1 500 001 € (valeur de la cellule B16), on se sert de la fonction DECALE sinon on fait le calcul suivant :
C15+ARRONDI.SUP((E10-B16+1)/1500)*46
Autres solutions :
On pourrait aussi faire via une imbrication de plusieurs SI, ce qui donnerait la formule suivante :=SI(E10<B4;C3;SI(E10<B5;C4;SI(E10<B6;C5;SI(E10<B7;C6;SI(E10<B8;C7;SI(E10<B9;C8;SI(E10<B10;C9;SI(E10<B11;C10;SI(E10<B12;C11;SI(E10<B13;C12;SI(E10<B14;C13;SI(E10<B15;C14;SI(E10<B16;C15;C15+ARRONDI.SUP((E10-B16+1)/1500)*46)))))))))))))
Mais on a là une formule assez compliquée notamment à corriger...
Antoine Leroux propose une autre solution en se servant de la propriéte VRAI/FAUX d'une comparaison insérée dans une multiplication. Avec par exemple cette formule : (a<b)*(b<c) qui vaut 1 si a<b<c sinon qui vaut 0 si b<a ou c<b. Du coup, cela donne comme formule : =(B3<=E10)*(E10<B4)*C3+(B4<=E10)*(E10<B5)*C4+(B5<=E10)*(E10<B6)*C5+(B6<=E10)*(E10<B7)*C6+(B7<=E10)*(E10<B8)*C7+(B8<=E10)*(E10<B9)*C8+(B9<=E10)*(E10<B10)*C9+(B10<=E10)*(E10<B11)*C10+(B11<=E10)*(E10<B1)*C11+(B12<=E10)*(E10<B13)*C12+(B13<=E10)*(E10<B14)*C13+(B14<=E10)*(E10<B15)*C14+(B15<=E10)*(E10<B16)*C15+(E10>B16-1)*(C15+ARRONDI.SUP((E10-B16+1)/1500)*46)
Z- La puissance des REGEX pour modifier une formule complexe sur des milliers de cellules :
Suite à un rare cas qui pouvait se produire dans une formule, il pouvait y avoir une division par zéro empêchant par la suite de faire d'autres calculs en découlant. Du coup, il a fallu que je précise une condition utilisée sur 20 000 cellules. Je pouvais le faire par macro mais je me suis demandé s'il n'était pas possible de le faire via des REGEX (ou EXpression RÉGulières en français) avec la fonction "Rechercher et Remplacer" fournie dans Calc. Ne maîtrisant absolument pas ce langage je me suis fait aider par l'IA Claude en version gratuite et limitée et je dois dire que pour le coup l'IA a été d'une très grande utilité notamment pour m'expliciter comment fonctionnait sa formule. Je vais détailler ci-dessous afin d'en garder une trace.
Lors de la recherche, il ne faudra pas oublier de cocher la case "Expressions régulières" ainsi que "Rechercher dans : Formules" !
Formule de départ sur la première cellule concernée :
=SI($B10>0;D10/(D10+E10+F10)*100;"NC")
Cette formule se répète sur les milliers de cellules avec des n° de lignes changeant mais également des n° de colonnes changeant.
Je voudrais transformer toutes les formules en remplaçant la condition initiale $B10>0 par ET($B10>0;(D10+E10+F10)>0) pour obtenir à la place cette forme-là :
=SI(ET($B10>0;D10+E10+F10>0);D10/(D10+E10+F10)*100;"NC")
On remarque qu'il "suffit" de remplacer la partie en rouge de la formule avec une condition supplémentaire qui correspond au dénominateur de la division D10+E10+F10 qui ne doit pas être égal à 0.
Voici la solution proposée par l'IA pour le champ "RECHERCHER" :
\$B(\d+)>0(?=;[A-Z]+\1/\(([A-Z]+)\1\+([A-Z]+)\1\+([A-Z]+)\1\)\*100;"NC"\))
- Le signe \ permet d'échapper le signe qui le suit car ce signe à une signification particulière dans les REGEX comme le caractère $ et de ne pas être interpréter comme une commande REGEX.
- La partie recherchée devant être remplacée est celle-ci : \$B(\d+)>0
- \$B correspond à la recherche du motif $B
- \d+ : correspond à 1 ou plusieurs chiffres (signification du +) qui suivent le motif $B, c'est-à-dire le numéro de la ligne. S'il n'y avait pas d'antislash \, ça serait interprété comme la lettre "d".
- (\d+) : ce numéro de ligne est entre parenthèses pour indiquer qu'il s'agit d'un groupe, en l'occurrence le premier groupe (G1). Ces groupes sont importants car cela permet de les réutiliser par la suite !
- >0 : correspond à la fin de la condition dans la formule
- (?=...............) correspond à un bloc indiquant ce qui doit suivre la partie à remplacer mais qui ne doit pas être modifiée lors du remplacement. Si ce motif n'est pas retrouvé alors il n'y aura pas de remplacement de formule dans la cellule.
- ;[A-Z]+\1 correspond au point-virgule ; suivi de la lettre ou les lettres (avec le +) d'une colonne suivie de la valeur du groupe G1 qui correspond au numéro de la ligne. Ce qui donne par exemple ";D10",";G12", etc. On remarque que pour appeler la valeur d'un groupe, on utilise l'antislash \ suivi du numéro du groupe.
- / correspond à la division
- \( correspond au caractère littéral de la parenthèse (il ne s'agit pas ici du début d'un groupe REGEX !)
- ([A-Z]+) il s'agit ici d'un nouveau groupe G2 (car on a de nouvelles parenthèses) correspondant à la lettre ou les lettres (avec le +) d'une colonne et dont on aura besoin dans la partie Remplacer.
- \1 correspond encore au numéro de ligne G1.
- \+ correspond au caractère littéral + (ne devant pas être interprété comme une commande REGEX)
- ([A-Z]+)\1 on définit un nouveau groupe G3 correspondant à une nouvelle lettre ou aux lettres (avec le +) de colonne dont on aura également besoin dans la partie Remplacer, suivi du numéro de ligne G1.
- \+ correspond au caractère littéral + (ne devant pas être interprété comme une commande REGEX)
- ([A-Z]+)\1 on définit un nouveau groupe G4 correspondant à une nouvelle lettre ou aux lettres de colonne dont on aura également besoin dans la partie Remplacer, suivi du numéro de ligne G1.
- \) correspond au caractère littéral de la parenthèse (il ne s'agit pas ici de la fin d'un groupe !)
- \*100;"NC"\) correspond à la fin de la formule avec des \ pour que les caractères qui les suivent ne soient pas interprétés comme des commandes REGEX mais comme du texte littéral.
Voici la solution proposée par l'IA pour le champ "REMPLACER" :
ET(\$B$1>0;$2$1+$3$1+$4$1>0)
- Ici la formule est beaucoup plus simple car dans cette partie on considère qu'on n'a plus de REGEX et que par défaut l'ensemble des caractères doit être interprété littéralement sauf pour quelques-un comme les signes dollar $, esperluette & et antislash \. Du coup, on n'a pas besoin d'échapper les parenthèses dans cette partie !!!
- \$B permet d'insérer $B
- $1, $2, $3 et $4 correspondent au contenu des 4 groupes définis dans la partie "Rechercher". $1 correspond à G1 soit le numéro de ligne et les 3 groupes suivants correspondent aux 3 lettres de colonnes définies précédemment.
Il est vrai que la syntaxe REGEX peut être particulièrement complexe mais une fois qu'on a compris (et dans ce cas l'IA peut être particulièrement efficace), on voit toute sa puissance.
REMARQUE : avant de procéder au remplacement, il est fortement conseillé de d'abord cliquer sur le bouton "Tout rechercher" pour vérifier si la recherche fonctionne comme attendu.