Calcul des surfaces selon un fichier de référence

[KIM]

Bonjour le forum, les ami(e)s,
Je suis confronté à une nouvelle problématique et vous remercie d'avance de votre aide.
Nous avons un fichier de référence des usages des locaux d'un bâtiment (Feuil USAGES). Il me faut calculer certaines surfaces (SHON, SDO, SHO, SUB, SUN, SU) d'un bâtiment selon la définition de ces surfaces dans ce fichier de référence. Quand il y a un "X" dans la cellule, il faut prendre en compte la surface du local selon les usages des col A, B et C et selon la surface à calculer. voir fichier joint. La Feuil "LOCAUX" correspond à la liste des locaux d'un ou plusieurs bâtiments avec les usages de ces locaux.
Les différentes surfaces sont à calculer pa étage de chaque bâtiment (feuil ETAGES) et par globalisé par bâtiment (feuil BAT).
Merci d'avance
Bien cordialement
KIM

 

[mercant76]

Re : Calcul des surfaces selon un fichier de référence

bonsoir KIM, ChTi160 et le forum

pour répondre à tes questions :

Feuille LOCAUX :

colonne M : oui, au départ, c'était des formules, mais pour faire simple, je les avais bloquées.

je vais les remettre.

colonnes O et P pour calcul selon l'étage :

je suis parti du principe que ce qui commence par S veut dire SOUS SOL, par 0 veut dire ETAGE, et par T veut dire TERRASSE.

donc, colonne M, j'extrait la première lettre des étages soit S, 0, ou T.

par convention, (et par intérêt), j'attribue la valeur 2 à S, 3 à 0, et 4 à T.

ce qui est aussi la colonne des étages dans USAGES.

c'est ce que je calcule en colonne O.

ainsi, en colonne P, je fais une recherche verticale de la valeur de la colonne N ( concaténation de USAGE-1, USAGE-2 et COMPLEMENT, que je vais chercher dans la base
D à L ( où en colonne D, il y a la même concaténation ) qui se trouve dans USAGES, avec O6, etc... qui correspond à la colonne dans laquelle je vais chercher s'il y a une X.

ensuite, en colonne Q, s'il y a une X, je note la surface, sinon rien.

après, avec un sommeprod, je récapitule par pièce, batiment,... la somme des surfaces.

je n'ai pas optimisé les formules, mais est ce nécessaire ?

je ne sais pas utiliser DECALER ( pas eu besoin), mais nommer un champ ne pose pas de problèmes.

créer une feuille travail, comme je te l'avais proposé, peut se faire sans problèmes ( uniquement du temps ).

les explications te conviennent-elles ?

@+

 

[ChTi160]

Re : Calcul des surfaces selon un fichier de référence

Bonjour KIM
Bonjour Le fil (Mercant76)
Bonjour le forum

arff je viens de regarder le très beau travail de Mercant76
et je n'y ai pas retrouvé , ce qui me pose à moi problème .

Le traitement de ces quelques cas particuliers

1 ° Pas de correspondance de Usage1 dans la feuille Usages (renvoie de la Ligne 64)
2 ° Pas de correspondance de Usage2 dans la feuille Usages (prendre Usage1)

je vais encore regarder le fichier y'a peut être quelque chose qui m'a échappé lol (moi les formules ??? )

je vais taché de trouver la combine pour répondre à la demande ,j'ai la marche à suivre , mais je n'ai pas encore trouvé comment traduire tout cela en VBA Lol

Bonne journée

 

[ChTi160]

Re : Calcul des surfaces selon un fichier de référence

Re KIM ,Mercant76

pour ce post une erreur de tableau lol


Merci d'avance

 

[mercant76]

Re : Calcul des surfaces selon un fichier de référence

bonjour KIM et ChT1160,

ChTi160, si tu as besoin que je t'éclaircisses mon esprit tortueux, n'hésite pas.

@+

 

[KIM]

Re : Calcul des surfaces selon un fichier de référence

Salut Jean-Marie,
Bonjour Mercant76,
& le forum,

Effectivement, je n'ai pas regardé, dans la solution de mercant76 les cas particuliers quand il n'y pas de correspondance de Usage 2 ou usage1. Je le ferai à mon retour.
@mercant76,
Merci pour les explications,
Il me reste à comprendre la formule Rechechev dans col P: Dans la formule RECHERCHEV(N7;USAGES!$D$4:$L$64;O7;FAUX), le rôle du 3iem champ fait reference au contenu d'une cellule 07. Par contre les autres formules Recherchev RECHERCHEV(N7;USAGES!$D$4:$L$64;5;FAUX), le 3ieme champ est un décalage de col. C'est la 1ere formule recherchev (col P) que je n'ai pas comprise, Merci d'avance pour les explications. Comme disait Jean-Marie est-ce qu'on peut traiter les cas particuliers évoqués précédemment? Je suis aussi prenant d'une solution sans modifier l'ordre des colonnes initiales et avec une feuille de Travail. Merci si tu as le temps.
@Jean-Marie,
Merci d'avance pour le temps consacré à trouver une sol vba.
Je suis en déplacement pour des raisons familiales et serais de retour mardi.
Bon we et merci à vous tous et au forum
KIM

 

[ChTi160]

Re : Calcul des surfaces selon un fichier de référence

Salut KIM
Bonjour le fil
Bonjour le Forum

En pièce jointe , une proposition

Le Lien : Cijoint.fr - Service gratuit de dépôt de fichiers

fichier (changé)
Bonne Journée

 

[KIM]

Re : Calcul des surfaces selon un fichier de référence

Salut Jean-Marie,
Bonjour Mercant76 & le forum,
Je regarderai ton fichier rapidement et te tiens au courant de mes résultats.
Merci encore
Bien amicalement
KIM

 

[KIM]

Re : Calcul des surfaces selon un fichier de référence

Salut Jean-Marie,
Bonjour Mercant76 & le forum,
@Jean-Marie,
J'ai comparé les resultats de ta solution et celle de Mercant76. La différence se trouve effectivement dans les cas particuliers. Bravo pour le code. Je l'ai regardé, compliqué pour moi surtout avec les lignes de code avec IIF que je ne connais pas.
1- J'ai essayé de trouver le cas particulier ou usage1 ne correspond pas, on le considère par défaut à usage1 = VACANT. Si je décide pour ce cas particulier de prendre usage1 =INCONNU , est-ce suffisant de changer dans le code la ligne suivante:
Set C = .Range(.Cells(2, 1), .Cells(DerLigne, 1)).Find("INCONNU")?
2- Je n'ai pas trouvé ou tu as traité le cas où usage2 ne correspond, il faut traiter usage1.
Merci de m'expliquer si possible le déroulement de ta logique si tu as le temps.

3- Que faut-il modifier dans ton code pour une logique inverse c-à-d quand il y a un "X" la surface du local n'est pas à prendre en compte?, voir fichier ci-joint
Merci d'avance
KIM

 

[ChTi160]

Re : Calcul des surfaces selon un fichier de référence

Salut KIM ,Maurice

Bonjour Le Fil, Le Forum

En pièce jointe le fichier modifié .

j'ai pour répondre à ta demande Crée une nouvelle Constant "StrAutreUsg" (voir module "Variables") et remplacer dans le Code de la procédure "RechercheUsages" tout les "VACANT" par "StrAutreUsg" ce qui te permet de donner la valeur que tu veux à cette variable en modifiant sa valeur dans le module "Variables" .
Ensuite pour ce qui est de faire l'inverse de ce que tu as demandé , il suffit de donner la valeur vide ("") à la variable constante "Ok_S" ,même principe que pour "StrAutreUsg" tu mets ("" ou "X") selon , j'ai pour cela en fin de procédure "RechercheUsages" , remplacé (ce que je n'avais pas fait ) les "X" par "Ok_S" .
Pour ce qui est de la fonction IIF , il te suffit de sélectionner dans le code de la procédure "RechercheUsages" le texte IIF et de faire "F1" tu auras ainsi accès à des explications , exemple sur cette fonction , cela est trés pratique (la touche F1)
le principe
T= SI (X= 1, alors donner la valeur "Bien" à T , Sinon (X est différent de 1) on donne la valeur "Mal")
pour répondre à

Je n'ai pas trouvé ou tu as traité le cas où usage2 ne correspond, il faut traiter usage1

il te faut regarder la procédure "RechercheUsage23" du module "Recherche" (je pense que les explications (Commentaires) devraient te suffire )Sinon tu reviens me voir... Lol

Le fichier :Regarde la pièce jointe CalculSurfSelonUsages_v08JM4.zip

Bonne fin de journée

 

[mercant76]

Re : Calcul des surfaces selon un fichier de référence

bonsoir KIM et Jean-Marie,

je vais expliquer comment j'ai procédé :

en mettant en place les formules, à certains moments j'avais des erreurs.

je confesse que je n'ai pas regardé comment régler les litiges.

en regardant de plus près, j'ai vu que dans USAGES/COMPLEMENT, il y avait des infos supplémentaires.

en les analysant, on s'aperçoit qu'il s'agit plus d'infos "utilitaires", et qui n'ont rien à faire dans COMPLEMENT.

ainsi, dans USAGES, pour BUREAU, dans COMPLEMENT, il y a indiqué : secr, irps, sfc, medecin ...

donc, s'il y a celà, on remonte dans l'arborescence, et on prend la valeur du dessus( qui est identique à celle que l'on aurait pu prendre ).

J'ai considéré que ces indications, sont des infos d'UTILISATION, mais non des infos susceptibles de changer le M² à prendre en compte.

dans ce cas ci, USAGES-1 = Bureau, quelle utilité d'avoir bureau dans USAGES-2 ?

le fair d'avoir secr, irps, sfc, medecin ... ne doit pas interférer dans le calcul des surfaces.

ou alors, qu'on m'explique pourquoi un bureau avec un médecin doit être ou pas pris en compte.

si toutes ces infos de "CONFORT" sont mises dans une colonne intitulée "Utilisation",
elles sont toujours disponibles, mais ne polluent pas ce qui sert au calcul des surfaces.

pour mes formules, j'ai donc "viré" toutes ces infos polluantes pour calculer les surfaces.

je n'ai pas tenu compte des éventuels cas particuliers, mais à priori, çà peut se faire.

voilà comment et pourquoi, j'ai pris la décision de ne pas tenir compte de ces infos utilitaires.

bonsoir à tous.

@+

PS: au fait, les calculs sont bons ?

 

[KIM]

Re : Calcul des surfaces selon un fichier de référence

Salut Jean-Marie,
Bonjour Mercant76 & le forum,
@Jean-Marie
Merci pour ton dernier code et les variables supplémentaires rajoutées. C'est génial, Cela me facilite l'utilisation du code selon le fichier à traiter. Je retourne au bureau la semaine prochaine et l'appliquer au fichier d'origine. Ton code vba sera intégré dans un ensemble de codes pour sortir un tableau de bord général.
@Mercant76,
En effet la feuille Usage a été modifiée pour mieux voir les cas particuliers. Il faut savoir que les utilisateurs doivent remplir Usage1 et Usage2 d'après les listes qui existent dans l'onglet USAGES. Comme il n'y a pas de contrôle il arrive que les utilisateurs saisissent autre chose. Pour Usage1, c'est OK. Pour usage2, j'ai mis usage2 = Bureau quand Usge1= bureau, cela n'a pas de sens, mais c'est seulement pour indiquer le cas où usage2 ne correspondant pas à la liste dans l'onglet USAGES, il faut traiter usage1 seulement, c-à-d considérer usage2 un vide. Pour Complément, c'est la même chose mais son utilisation est libre. L'utilisateur peut mettre toute information qu'il souhaite mais je ne traite que l'information qui existe dans complément de l'onglet USAGES. A savoir aussi que Si Complément est OK, Usage2 n'est pas bon, il faut traiter seulement Usage1.
Je vous remercie tous les deux de votre aide, Je vais intégrer la solution vba, et la solution par formule a été très intéressante car elle nous a aidé à analyser le problème.
Je reviendrai sur ce fil après contrôle des surfaces.
Merci les amis et le forum,
Bien amicalement
KIM

 

[ChTi160]

Re : Calcul des surfaces selon un fichier de référence

Salut KIM ,Maurice

KIM , c'est toujours un plaisir de passer un moment à "Coder" pour toi .

1° Tes demandes sont toujours (depuis que je te connais) précises ,tes Tableaux Claires etc etc
2° tu ne manques jamais de venir voir ce qui ce passe sur tes posts

3° tu ne manques jamais de venir remercier , ce qui est très agréable .

je te remercie donc pour cet état d'esprit (car nombre des membres de ce Forum qui le font vivre passent , parfois (comme moi ) de nombreuses Heures pour essayer de satisfaire les demandes (qui deviennent parfois des Challenges ).

4° j'attends donc ta prochaine demande Lol

Bonne fin de journée

 

[KIM]

Re : Calcul des surfaces selon un fichier de référence

Cher ami, le fil & le forum,
A moi de te retourner les remerciements. C'est tout à fait normal de te remercier ainsi que toutes les autres personnes comme toi et d'autres que je ne cite pas et qui n'hésitent pas à donner de leur temps pour nous aider.
Ton aide m'a été précieuse. Mercis encore, merci encore à Maurice et le forum.
Bonne continuation en espérant ne pas vous décourager dans nos sollicitations.
Bien amicalement
KIM