Appels de fonds dans Excel : quel lot a encore un reste
Une liste d'appels de fonds, c'est une ligne par lot pour un mot de période. Vous saisissez le lot, le copropriétaire, la période, le montant appelé et le montant reçu. L'écart est l'appel moins le reçu tant que la ligne est ouverte. La fenêtre dit reste quand cet écart est au-dessus de zéro, solde quand les montants se valent, et trop quand le reçu est au-dessus de l'appel. Le premier écran est une feuille nommée Appels. La ligne 1 est l'en-tête. La ligne 2 est un lot que vous pouvez vérifier à la main : appel 450, reçu 300, statut ouvert. L'écart est 150. La fenêtre est reste. Il n'y a pas de date de bail ni de numéro de facture sur cet écran. Cette feuille n'est pas un calendrier de fins de bail. Ce calendrier utilise une date de fin et un préavis. Cette liste utilise les deux montants que vous avez saisis pour un appel déjà envoyé. Ce n'est pas une relance de factures commerciales, et ce n'est pas une liste d'appartements à vendre. La période est un mot que vous saisissez pour que deux appels du même lot restent sur deux lignes. Vous saisissez les deux montants. La question est quelle ligne ouverte a encore un reste. Les lignes closes sortent de la fenêtre. Elles restent sur la feuille. Un écart négatif est trop, pas un nombre à cacher. Elyx peut écrire l'écart et la fenêtre. Vous saisissez toujours l'appel et le reçu. L'essai dure 7 jours, sans carte.
Le probleme
L'appel échoue quand il vit dans un courrier. Une phrase dit que le lot A12 doit encore. Personne ne peut additionner les restes, parce que le montant appelé et le montant reçu ne sont pas sur la même ligne. Une phrase sans ces deux nombres n'est pas une liste d'appels. Un deuxième échec est de mélanger cette feuille avec un calendrier de baux. Une ligne de bail répond à quelle date de fin tombe dans le préavis. Une ligne de recouvrement répond à quelle facture client est en retard. Une ligne de vente répond à quel appartement est encore proposé. Si vous collez ici des dates de bail, des factures commerciales ou des annonces, la somme du matin ne veut plus dire le reste des appels saisis. Gardez cette feuille pour les lots, les mots de période et les deux montants. Un troisième échec est de supprimer un appel quand l'argent arrive. Passez le statut à clos et laissez la ligne. L'écart se vide. Supprimer la ligne retire la seule trace du 450 et du 300 déjà saisis. La feuille ne décide toujours pas de ce que l'appel aurait dû être.
Les benefices
Chaque appel est une ligne avec un lot, une période, un montant appelé et un montant reçu saisis.
L'écart est l'appel moins le reçu tant que la ligne est ouverte.
Reste veut dire que l'écart est au-dessus de zéro. Solde veut dire que les montants se valent. Trop veut dire que le reçu est au-dessus de l'appel.
Les lignes closes restent sur la feuille et sortent de la somme des restes.
La période est une étiquette. Ce n'est pas une date d'échéance.
Tutoriel pas a pas
Créer la liste et l'en-tête
Nommez la feuille Appels. En ligne 1, saisissez : Lot, Copro, Periode, Appel, Recu, Statut, Ecart, Fenetre, Note. Lot est le lot. Copro est le nom que vous utilisez. Periode est un mot saisi, par exemple Q1. Appel et Recu sont des montants saisis, pas une formule. Ecart et Fenetre sont des formules. Statut est un mot d'une liste. Note est facultative. Figez la ligne 1. Ne fusionnez pas les cellules.
Une ligne par lot et par période. Un second appel sur le même lot est une seconde ligne. Ne mettez pas deux périodes dans la même cellule.
Verrouiller les mots de statut
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 le reste est encore à suivre. Clos veut dire que vous en avez fini avec cet appel. Seul ouvert reste dans la fenêtre. Il faut choisir un mot. Un synonyme comme paye ou fait ne correspond pas aux formules. La liste est un filtre. Ce n'est pas une phrase sur un bail.
Appel et Recu sont des nombres. Ne saisissez pas le mot reste dans ces colonnes.
Saisir cinq appels que vous pouvez vérifier
En ligne 2, saisissez le lot A12, la période Q1, appel 450, reçu 300, statut ouvert. Vous prévoyez un écart de 150 et une fenêtre reste. En ligne 3, lot B07, période Q1, appel 200, reçu 80, statut ouvert. L'écart doit être 120 et la fenêtre reste. En ligne 4, lot C02, appel 450, reçu 450, statut ouvert. L'écart doit être 0 et la fenêtre solde. En ligne 5, lot D18, appel 450, reçu 500, statut ouvert. L'écart doit être -50 et la fenêtre trop. En ligne 6, lot E09, appel 450, reçu 300, statut clos. L'écart et la fenêtre doivent se vider. Utilisez ces montants avant de faire confiance aux formules.
Si vous ne connaissez pas encore le reçu, laissez Recu vide. L'écart reste vide. Ne saisissez pas zéro sauf si vous avez vraiment reçu zéro.
Écrire l'écart
L'écart est vide quand le statut est clos, ou quand Appel ou Recu est vide. Sinon c'est Appel moins Recu. Avec Appel en D2, Recu en E2 et Statut en F2, utilisez =SI(OU(F2="clos";D2="";E2="");"";D2-E2). Mettez le format Nombre. Un nombre positif est encore à encaisser. Zéro veut dire que les montants se valent. Un nombre négatif veut dire que le reçu est au-dessus de l'appel. C'est un signal pour ouvrir la ligne. Ce n'est pas une nouvelle charge.
=SI(OU(F2="clos";D2="";E2="");"";D2-E2)450 moins 300 font 150. 450 moins 500 font -50. Si vous voyez un autre nombre, les cellules ne sont pas les montants saisis.
Écrire la fenêtre
La fenêtre est vide quand le statut est clos, ou quand un des montants est vide. Sinon elle dit reste quand Appel moins Recu est au-dessus de zéro, trop quand cette différence est en dessous de zéro, et solde quand elle est nulle. Utilisez =SI(OU(F2="clos";D2="";E2="");"";SI(D2-E2>0;"reste";SI(D2-E2<0;"trop";"solde"))). Reste veut dire un reste. Solde veut dire que les deux montants se valent. Trop veut dire que le reçu est au-dessus de l'appel. Le copropriétaire et la période ne sont pas dans cette formule.
=SI(OU(F2="clos";D2="";E2="");"";SI(D2-E2>0;"reste";SI(D2-E2<0;"trop";"solde")))N'enveloppez pas l'écart dans ABS. Cacher le signe moins cache la ligne où le reçu est 500 pour un appel de 450.
Compter et additionner les lignes en reste
Sur une feuille nommée Matin, les lignes en reste sont =NB.SI.ENS(Appels!H:H;"reste"). Avec les cinq lignes ci-dessus, ce compte est 2 : l'écart de 150 et l'écart de 120. La somme des restes est =SOMME.SI.ENS(Appels!G:G;Appels!H:H;"reste"), soit 270. Le compte des trop est =NB.SI.ENS(Appels!H:H;"trop"), soit 1, l'écart de -50. La ligne close E09 est dehors parce que sa fenêtre est vide. Une fenêtre vide n'est pas un reste. Écrivez la règle à côté du compte.
=SOMME.SI.ENS(Appels!G:G;Appels!H:H;"reste")N'ajoutez pas le -50 dans la somme des restes. Trop est un autre mot, donc SOMME.SI.ENS le laisse de côté.
Lire un écart négatif comme un signal
Si l'écart est négatif et que le statut est encore ouvert, le reçu est au-dessus de l'appel et la ligne n'a pas été close. C'est une ligne à ouvrir, pas une ligne à supprimer. Soit le reçu a été saisi sur le mauvais lot, soit le montant de l'appel est en dessous de ce que vous vouliez. Corrigez la cellule qui est fausse. Le -50 du lot D18 ne dit pas que le lot doit une nouvelle charge. Il dit que 500 a été saisi contre un appel de 450. Un écart 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 reste pour la liste du matin. Filtrez Periode sur Q1 quand vous ne voulez que ce mot. Filtrez Statut sur clos quand vous avez besoin de l'historique.
Vérifier les cinq lignes avant de partager la somme
Appel 450 et reçu 300, statut ouvert : écart 150, fenêtre reste. Appel 200 et reçu 80, statut ouvert : écart 120, fenêtre reste. Appel 450 et reçu 450, statut ouvert : écart 0, fenêtre solde. Appel 450 et reçu 500, statut ouvert : écart -50, fenêtre trop. Les mêmes 450 et 300 avec le statut clos : écart vide, fenêtre vide, et la somme des restes ignore la ligne. La somme des lignes ouvertes en reste est 270, et le compte est 2. 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 bail, et ce n'est pas une facture commerciale.
Écrivez les cinq contrôles sur Matin si plusieurs personnes mettent les appels à jour.
Fonctionnalites du template
Écart tant que l'appel est ouvert
Appel moins reçu. Vide quand le statut est clos ou qu'un montant est vide.
=SI(OU(F2="clos";D2="";E2="");"";D2-E2)Fenêtre à partir de cet écart
Reste quand l'écart est au-dessus de zéro, trop quand il est en dessous, solde quand il est nul.
=SI(OU(F2="clos";D2="";E2="");"";SI(D2-E2>0;"reste";SI(D2-E2<0;"trop";"solde")))Compte des restes
NB.SI.ENS lit le mot de fenêtre. Avec les lignes d'exemple, le compte est 2.
=NB.SI.ENS(Appels!H:H;"reste")Somme des restes
SOMME.SI.ENS additionne seulement les écarts dont la fenêtre est reste. La somme d'exemple est 270.
=SOMME.SI.ENS(Appels!G:G;Appels!H:H;"reste")L'historique reste sur la feuille
Les lignes closes demeurent. Elles sortent de la somme parce que la fenêtre est vide.
Exemples concrets
Un reste de 150
Le lot A12 a été appelé de 450 pour la période Q1 et 300 ont été reçus. Vous le voulez sur la liste du matin.
Appel : 450. Reçu : 300. Statut : ouvert.
Resultat : L'écart est 150. La fenêtre est reste. Le compte des restes inclut la ligne. Le 450 et le 300 sont les nombres saisis.
Une seconde ligne en reste change la somme
Le lot B07 a été appelé de 200 et 80 ont été reçus. A12 est encore en reste de 150.
B07 appelé 200, reçu 80, statut ouvert. A12 toujours à 450 et 300.
Resultat : L'écart de B07 est 120, fenêtre reste. La somme des restes est 270, soit 150 plus 120. Le compte est 2.
Un reçu au-dessus de l'appel
Le lot D18 a été appelé de 450 et 500 ont été saisis comme reçu. Vous ne voulez pas cette ligne dans la somme des restes.
Appel : 450. Reçu : 500. Statut : ouvert.
Resultat : L'écart est -50. La fenêtre est trop. La somme des restes reste 270. Clore la ligne vide l'écart et laisse les montants sur la feuille.
Astuces de pro
Zéro reçu est un nombre
Saisissez 0 seulement quand rien n'a été reçu. Une cellule vide veut dire que vous ne savez pas encore, et l'écart reste vide. 450 moins 0 font un écart de 450 et une fenêtre reste.
La période est une étiquette
Q1 permet de filtrer. La formule de fenêtre ne lit jamais la période. Une seconde période sur le même lot est une autre ligne, avec son propre appel et son propre reçu.
Un montant en texte ne se soustrait pas
Si Appel a été collé en texte, D2-E2 ne fera pas 150. Saisissez à nouveau 450 et 300 comme des nombres. Puis relisez la fenêtre.
Distinct d'un calendrier de baux
Les dates de fin de bail vont sur le calendrier du gestionnaire immobilier. Les factures commerciales vont sur la liste de recouvrement. Les appartements à vendre vont sur la liste de l'agent. Ne collez pas ces lignes ici en attendant que la somme veuille dire la même chose.