... Des passages (en occitan)

[LibreOffice Calc] Astuces utiles

Détails
Catégorie : LibreOffice
Affichages : 14180

Libreoffice icon mixJe 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 :

  1. Menu "Édition" -> "Rechercher et remplacer..." (raccourci CTRL+H).
  2. 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")
  3. Dans le champ "Remplacer", laisser vide
  4. 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 :

  1. Menu "Édition" -> "Rechercher et remplacer..." (raccourci CTRL+H).
  2. Dans le champ "Rechercher", saisir "^\s" (l'espace se code "\s")
  3. Dans le champ "Remplacer", laisser vide
  4. 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" :

LibreOffice Calc Expressions Régulières

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 :

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-dessous).

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

SI.CONDITIONS

SI.MULTIPLE

SIERREUR

SOMME.SI / SOMME.SI.ENS

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

  1. Menu "Édition" -> "Rechercher et remplacer..." (raccourci CTRL+H).
  2. Dans le champ "Rechercher", saisir "^[0-9]"
  3. Dans le champ "Remplacer", saisir "&"
  4. 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)))

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;"";"")

LibreOffice Calc Validation Listes deroulantes condition2.png

LibreOffice Calc Validation Listes deroulantes condition

 

 

 

 

 


 

 

 

 

 

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)

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.