[LibreOffice Calc] Astuces utiles

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 :

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. 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"

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/

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.

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 :

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 ! 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 :