Fixer partie de tableau

mik1 · October 30, 2019, 8:52am

Bonjour,

J’ai créé un tableau de comptabilité avec une partie “données” qui comprend environ 200 lignes et une dizaine de colonnes (catégorie) et une partie “récupération de données” que j’ai placé sur la droite. Dans cette deuxième partie qui opère la synthèse selon plusieurs formules qui récupère les données de la partie “données”, j’ai remarqué que selon le tri que j’opère sur la partie donnée en triant en tri croissant sur une des colonnes", les résultats de la partie “récupération de données” peut générer de mauvais résultats.
Par exemple faire un tri croissant sur la colonne “crédit TTC” donne des résultats bons, mais tri croissant sur “Crédit HT” donne des résultats mauvais. Pourtant, toutes les lignes apparaissent toujours dans le tableau, et les formules prennent en compte toutes les lignes.

Pour résoudre ce problème, j’aimerais “fixer” la partie “récupération de données” et la rendre indépendante du tri que je peux opérer sur la partie “données”.
Est ce possible ?? Et si oui, comment faire ?

merci

PS : “Bug” vu sur plusieurs versions de Libre Office 5.2 et 6.3
Mac OS 10.14.1 mais vu aussi sur MAC OS 10.12 ou MAC OS 10.13

ajlittoz · October 30, 2019, 9:11am

Je pense que votre question concerne le tableur calc et non le composant math qui sert à mettre en forme des formules mathématiques en vue de leur insertion dans un document texte. merci de requalifier la question.

ajlittoz · October 30, 2019, 9:21am

Sans plus de précision sur les formules utilisées, j’en suis réduit à des suppositions.

Je pense que vos formules sont du genre =SOMME(a2:A10) désignant les limites du calcul. Quand vous triez la colonne ou partie de colonne s’il y a un titre en tête, les contenus des cellules sont redistribués. La cellule antérieurement A1 peut se retrouver en A6. Ainsi, la formule peut devenir =SOMME(A6:A3) et n’englobe plus la totalité des données de la colonne.

Ce comportement est tout à fait normal: il s’agit des références relatives qui suivent les cellules notamment quand vous ajoutez des lignes/colonnes. Pour imposer un position fixe dans la feuille, il faut utiliser des références absolues, soit =SOMME(A$2:A$10). Notez que je n’ai fixé que la désignation de ligne, laissant flottante la désignation de colonne.

Pour tout complément d’information, éditez votre question ou faites un commentaire. N’utilisez pas une réponse.

ajlittoz · October 30, 2019, 9:22am

Si ma suggestion résoud le problème, je la transformerai en réponse pour que vous puissiez la cocher.

mik1 · October 30, 2019, 9:34am

Voici une des formules qui pose problème :
=SOMMEPROD((J7:J618=“FACTURE”)(K7:K618=“AT”)(F7:F618))+SOMMEPROD((J7:J618=“FACTURE”)(K7:K618=“MATG”)(F7:F618))*1/3

Nous avons pris arbitrairement la ligne 618 comme limite, mais elle est très loin d’être atteinte.
Il s’agit là de calculer dans la catégorie “FACTURE”, et du groupe “AT” la somme du montant des factures AT, additionné du 1/3 du montant du groupe “MATG”.

EN prenant une limite très lointaine nous pensions nous affranchir des problèmes de redistribution des lignes selon le classement.

mik1 · October 30, 2019, 9:42am

Merci à ajlittoz qui m’a conduit à trouver la solution ! Effectivement nos formules commencent par
J7, K7 etc… prenant en compte la ligne 7 comme début. Erreur de notre part.
Merci donc. Nous allons revérifier nos formules.

ajlittoz · October 30, 2019, 9:46am

Attention, dans le système MarkDown, * provoque la mise en italique jusqu’à l’* suivante. Pour faire apparaître l’astérisque dans le texte, il faut code \*, ou bien mettre la formule entre accents graves `.

Il me semble que vous êtes dans le cas que je signale. Le 618 ne doit pas trop causer de problème, en revanche le 7 me semble la source des ennuis. Que se passe-t-il si vous recodez J$7:j$618 etc. ?

ajlittoz · October 30, 2019, 10:01am

Les références de cellule de la forme A5, appelées références relatives, désignent le contenu de la cellule. Quand ce contenu est déplacé (insertion de lignes/colonnes/cellules ou bien tri comme ici), les formules sont ajustées pour toujours référencer le même contenu.

Quand il s’agit de faire une synthèse de données, par exemple la somme d’une colonne, on ne s’intéresse plus au contenu individuel des cellules mais à une zone “fixe” de la feuille de calcul. Il faut alors désolidariser la référence du contenu en utilisant des références absolues. L’indicateur est le signe $ qui peut préfixer la ligne, la colonne ou les deux.

$A5 fige la colonne, mais la ligne peut encore de déplacer.
A$5 réciproquement fige la ligne mais la colonne est libre
$A$5 désigne irrémédiablement la cinquième cellule de la première colonne, quoi qu’il arrive à la feuille

Ajoutez un $ là où c’est nécessaire dans vos formules. À vous de voir si vous avez besoin de complètement figer les références ou laisser libre les colonnes pour permettre l’insertion de nouvelles sans avoir à retoucher les formules de synthèse.

Pour montrer à la communauté que la question a trouvé sa réponse, cliquez sur ✓ à côté de la “bonne” réponse, et “votez” en cliquant sur la flèche ^ de toute réponse utile. Ce sont les mécanismes utilisés pour communiquer la qualité des questions et réponses sur ce site. Merci!

Pour tout complément d’information, éditez votre question ou faites un commentaire. N’utilisez pas une réponse.