ElyxAI

Réserve de sinistre dans Excel : quel dossier ouvert a encore de la réserve

Gestionnaire de sinistresRéserve de sinistre

Une liste de réserves, c'est une ligne par sinistre. Vous saisissez le dossier, le titulaire, le mot de type, une date d'ouverture, la réserve et le montant déjà payé. Le reste est la réserve moins le payé tant que la ligne est ouverte. La fenêtre dit ouvert-mt quand cette différence est au-dessus de zéro, solde quand les montants se valent, et depasse quand le payé est au-dessus de la réserve. Le premier écran est une feuille nommée Dossiers. La ligne 1 est l'en-tête. La ligne 2 est un dossier que vous pouvez vérifier à la main : réserve 1200, payé 400, statut ouvert. Le reste est 800. La fenêtre est ouvert-mt. La date d'ouverture est sur la ligne et n'entre pas dans ce résultat. Cette feuille n'est pas un échéancier de renouvellement de contrats. Cet échéancier utilise une date de contrat et un préavis. Cette liste utilise la réserve et le montant payé que vous avez saisis. Ce n'est pas un registre de factures fournisseurs, et ce n'est pas une liste de relance clients. Le mot de type est un filtre. Vous saisissez la réserve. La feuille ne l'augmente pas quand le payé la dépasse. Les lignes closes sortent de la fenêtre et restent sur la feuille. Elyx peut écrire le reste et la fenêtre. Vous saisissez toujours la réserve et le montant payé. L'essai dure 7 jours, sans carte.

Le probleme

Le dossier échoue quand la réserve vit dans une note et le montant payé dans une autre. Personne ne voit quel dossier ouvert est encore au-dessus de zéro, parce que les deux nombres ne sont pas sur la même ligne. Une phrase sans ces deux nombres n'est pas une liste de réserves. Un deuxième échec est de mélanger cette feuille avec un échéancier de renouvellement. Une ligne de renouvellement répond à quelle date de contrat tombe dans le préavis. Une ligne de facture répond à quelle facture fournisseur n'est pas rapprochée. Une ligne de recouvrement répond à quelle facture client est en retard. Si vous collez ici des dates de renouvellement, des factures fournisseurs ou des factures clients, la somme du matin ne veut plus dire la réserve restante sur les dossiers ouverts. Gardez cette feuille pour les dossiers, les mots de type et les deux montants. Un troisième échec est de supprimer un dossier quand il est clos. Passez le statut à clos et laissez la ligne. Le reste se vide. Supprimer la ligne retire la seule trace du 1200 et du 400 déjà saisis. Si le payé est au-dessus de la réserve, la fenêtre dit depasse. Vous resaisissez la réserve vous-même.

Les benefices

Chaque dossier est une ligne avec une réserve et un montant payé saisis.

Le reste est la réserve moins le payé tant que la ligne est ouverte.

Ouvert-mt veut dire que le reste est au-dessus de zéro. Solde veut dire que les montants se valent. Depasse veut dire que le payé est au-dessus de la réserve.

Les lignes closes restent sur la feuille et sortent de la somme des ouvert-mt.

La date d'ouverture est conservée. Elle n'est pas dans la formule d'argent.

Tutoriel pas a pas

1

Créer la liste des dossiers

Nommez la feuille Dossiers. En ligne 1, saisissez : Dossier, Titulaire, Type, Ouverture, Reserve, Paye, Statut, Reste, Fenetre, Note. Dossier est le numéro que vous utilisez. Titulaire est le nom. Type est un mot comme auto, habitation ou sante. Ouverture est une vraie date que vous saisissez, et les formules d'argent ne l'utilisent pas. Reserve et Paye sont des montants saisis. Reste et Fenetre sont des formules. Statut est un mot d'une liste. Figez la ligne 1. Ne fusionnez pas les cellules.

Une ligne par dossier. Un second paiement sur le même dossier met à jour Paye. N'ajoutez pas une seconde ligne pour le même dossier, sauf si c'est un autre dossier.

2

Verrouiller les statuts et les types

Sélectionnez Statut à partir de la ligne 2. Données, Validation des données, Liste. La source, sur une feuille nommée Listes, est ouvert et clos. Ouvert veut dire que la réserve est encore en jeu. Clos veut dire que vous en avez fini avec ce dossier. Seul ouvert reste dans la fenêtre. Sélectionnez Type. La source est auto, habitation et sante. Ajoutez un mot sur la feuille Listes si vous en avez besoin. Il faut choisir un mot. Un synonyme comme paye ou voiture ne correspond pas aux formules. Le type est un filtre. Ce n'est pas une décision de garantie.

Reserve et Paye sont des nombres. Ne saisissez pas le mot ouvert-mt dans ces colonnes.

3

Saisir cinq dossiers que vous pouvez vérifier

En ligne 2, saisissez le dossier CL-19, le type auto, n'importe quelle date d'ouverture, réserve 1200, payé 400, statut ouvert. Vous prévoyez un reste de 800 et une fenêtre ouvert-mt. En ligne 3, dossier CL-20, type habitation, réserve 300, payé 100, statut ouvert. Le reste doit être 200 et la fenêtre ouvert-mt. En ligne 4, dossier CL-21, type auto, réserve 500, payé 500, statut ouvert. Le reste doit être 0 et la fenêtre solde. En ligne 5, dossier CL-22, type sante, réserve 1200, payé 1500, statut ouvert. Le reste doit être -300 et la fenêtre depasse. En ligne 6, dossier CL-23, type auto, réserve 1200, payé 400, statut clos. Le reste et la fenêtre doivent se vider. Changez la date d'ouverture de la ligne 2 : le reste doit rester 800.

Si vous ne connaissez pas encore le montant payé, laissez Paye vide. Le reste reste vide. Ne saisissez pas zéro sauf si rien n'a été payé.

4

Écrire le reste

Le reste est vide quand le statut est clos, ou quand Reserve ou Paye est vide. Sinon c'est Reserve moins Paye. Avec Reserve en E2, Paye en F2 et Statut en G2, utilisez =SI(OU(G2="clos";E2="";F2="");"";E2-F2). Mettez le format Nombre. Un nombre positif veut dire que la réserve est encore au-dessus du payé. Zéro veut dire que les montants se valent. Un nombre négatif veut dire que le payé est au-dessus de la réserve saisie. La date d'ouverture en colonne D n'est pas dans cette formule.

=SI(OU(G2="clos";E2="";F2="");"";E2-F2)

1200 moins 400 font 800. 300 moins 100 font 200. 1200 moins 1500 font -300. Si vous voyez un autre nombre, les cellules ne sont pas les montants saisis.

5

Écrire la fenêtre

La fenêtre est vide quand le statut est clos, ou quand un des montants est vide. Sinon elle dit ouvert-mt quand Reserve moins Paye est au-dessus de zéro, depasse quand cette différence est en dessous de zéro, et solde quand elle est nulle. Utilisez =SI(OU(G2="clos";E2="";F2="");"";SI(E2-F2>0;"ouvert-mt";SI(E2-F2<0;"depasse";"solde"))). Ouvert-mt veut dire que la réserve est encore au-dessus du payé. Solde veut dire que les deux montants se valent. Depasse veut dire que vous avez payé plus que la réserve saisie, donc la cellule de réserve mérite un regard. La formule ne change pas la réserve.

=SI(OU(G2="clos";E2="";F2="");"";SI(E2-F2>0;"ouvert-mt";SI(E2-F2<0;"depasse";"solde")))

N'enveloppez pas le reste dans ABS. Cacher le signe moins cache CL-22, où 1500 a été saisi contre une réserve de 1200.

6

Additionner seulement les restes ouverts

Sur une feuille nommée Matin, les lignes ouvert-mt sont =NB.SI.ENS(Dossiers!I:I;"ouvert-mt"). Avec les lignes ci-dessus, ce compte est 2 : le reste de 800 et le reste de 200. La somme ouverte est =SOMME.SI.ENS(Dossiers!H:H;Dossiers!I:I;"ouvert-mt"), soit 1000. Le compte des depasse est =NB.SI.ENS(Dossiers!I:I;"depasse"), soit 1, le reste de -300. Le dossier clos CL-23 est dehors parce que sa fenêtre est vide. Une fenêtre vide n'est pas ouvert-mt. Les dossiers auto en ouvert-mt sont =NB.SI.ENS(Dossiers!C:C;"auto";Dossiers!I:I;"ouvert-mt"), soit 1, seulement CL-19. Écrivez la règle à côté de la somme.

=SOMME.SI.ENS(Dossiers!H:H;Dossiers!I:I;"ouvert-mt")

N'ajoutez pas le -300 dans la somme ouverte. Depasse est un autre mot, donc SOMME.SI.ENS le laisse de côté.

7

Lire un reste négatif comme une réserve à resaisir

Si le reste est négatif et que le statut est encore ouvert, le payé est au-dessus de la réserve et la ligne n'a pas été close. C'est une ligne à ouvrir, pas une ligne à supprimer, et pas une ligne dont la formule devrait augmenter la réserve. Soit le paiement a été saisi sur le mauvais dossier, soit la réserve de 1200 n'est plus le nombre que vous voulez à côté d'un payé de 1500. Corrigez la cellule qui est fausse. Le -300 ne dit pas que le dossier est refusé. Un reste sur une ligne close ne devrait pas arriver, parce que clos vide la cellule. Si vous voyez un nombre sur une ligne close, la formule a été écrasée.

Filtrez Fenetre sur ouvert-mt pour la liste du matin. Filtrez Type sur habitation quand vous ne voulez que ce mot. Filtrez Statut sur clos quand vous avez besoin de l'historique.

8

Vérifier les cinq dossiers avant de partager la somme

Réserve 1200 et payé 400, statut ouvert : reste 800, fenêtre ouvert-mt. Réserve 300 et payé 100, statut ouvert : reste 200, fenêtre ouvert-mt. Réserve 500 et payé 500, statut ouvert : reste 0, fenêtre solde. Réserve 1200 et payé 1500, statut ouvert : reste -300, fenêtre depasse. Les mêmes 1200 et 400 avec le statut clos : reste vide, fenêtre vide. La somme des ouvert-mt est 1000, et le compte est 2. Changer la date d'ouverture ne bouge pas le reste. Si un contrôle échoue, corrigez les montants ou le mot de statut avant d'envoyer la somme. La somme est seulement cette feuille. Ce n'est pas une date de renouvellement, et ce n'est pas un avis de garantie.

Écrivez les cinq contrôles sur Matin si plusieurs personnes mettent les dossiers à jour.

Fonctionnalites du template

Reste tant que le dossier est ouvert

Réserve moins payé. Vide quand le statut est clos ou qu'un montant est vide.

=SI(OU(G2="clos";E2="";F2="");"";E2-F2)

Fenêtre à partir de cette différence

Ouvert-mt au-dessus de zéro, depasse en dessous, solde à zéro.

=SI(OU(G2="clos";E2="";F2="");"";SI(E2-F2>0;"ouvert-mt";SI(E2-F2<0;"depasse";"solde")))

Compte des ouvert-mt

NB.SI.ENS lit la fenêtre. Avec les lignes d'exemple, le compte est 2.

=NB.SI.ENS(Dossiers!I:I;"ouvert-mt")

Somme des ouvert-mt

SOMME.SI.ENS additionne seulement les restes dont la fenêtre est ouvert-mt. La somme d'exemple est 1000.

=SOMME.SI.ENS(Dossiers!H:H;Dossiers!I:I;"ouvert-mt")

L'historique reste sur la feuille

Les lignes closes demeurent. Elles sortent de la somme ouverte parce que la fenêtre est vide.

Exemples concrets

800 encore au-dessus du payé

CL-19 a une réserve de 1200 et 400 déjà payés. Vous le voulez sur la liste du matin.

Réserve : 1200. Payé : 400. Statut : ouvert. Type : auto.

Resultat : Le reste est 800. La fenêtre est ouvert-mt. Le compte ouvert inclut la ligne. Le 1200 et le 400 sont les nombres saisis.

Un second dossier ouvert fait 1000

CL-20 a une réserve de 300 et 100 déjà payés. CL-19 est encore en ouvert-mt à 800.

CL-20 réserve 300, payé 100, statut ouvert. CL-19 toujours à 1200 et 400.

Resultat : Le reste est 200, fenêtre ouvert-mt. La somme ouverte est 1000, soit 800 plus 200. Le compte est 2.

Un payé au-dessus de la réserve

CL-22 a une réserve de 1200 et 1500 saisis comme payé. Vous ne voulez pas cette ligne dans la somme ouverte, et vous ne voulez pas que la formule augmente la réserve.

Réserve : 1200. Payé : 1500. Statut : ouvert.

Resultat : Le reste est -300. La fenêtre est depasse. La somme ouverte reste 1000. Clore la ligne vide le reste et laisse les montants sur la feuille.

Astuces de pro

Zéro payé est un nombre

Saisissez 0 seulement quand rien n'a été payé. Une cellule vide veut dire que vous ne savez pas encore, et le reste reste vide. 1200 moins 0 font un reste de 1200 et une fenêtre ouvert-mt.

La date d'ouverture n'est pas la fenêtre

Saisissez n'importe quelle vraie date dans Ouverture. Changez-la : le reste ne bouge pas. L'échéancier de renouvellement est la feuille qui lit les dates. Celle-ci lit les deux montants.

Un montant en texte ne se soustrait pas

Si Reserve a été collée en texte, E2-F2 ne fera pas 800. Saisissez à nouveau 1200 et 400 comme des nombres. Puis relisez la fenêtre.

Distinct d'un échéancier de renouvellement

Les dates de contrat vont sur la liste de l'agent d'assurance. Les factures fournisseurs vont sur le registre des comptes fournisseurs. Les factures clients vont sur la liste de recouvrement. Ne collez pas ces lignes ici en attendant que la somme veuille dire la réserve restante.

Formules utilisees

Pas envie de le faire à la main ? Demandez à Elyx

Exemple de demande

« Prépare dans mon fichier : Réserve et montant déjà payé, avec les formules, les totaux et la mise en forme »

Elyx l’exécute directement dans votre fichier Excel.

7 jours offerts · sans carte bancaire

Guides liés

Questions frequentes