Formule pour automatiser un fichier Excel avec un base de données Business Object

Bonjour,

J’ai besoin d’aide pour créer une formule dans le fichier ci-joint : 07122017base Foncier 2017 .xlsx (258,8 Ko)

je souhaiterais que le montant dans la cellule L3 de l’onglet Foncier soit repris automatiquement à partir de la colonne N de l’extraction brute mais que le montant correspond au code opération et art et env de l’onglet Foncier, repris dans l’onglet extraction brute sous les titres de colonne suivants : opé. AP EPCP et Description nat. ana. opération

J’ai cherché en utilisant les fonctions si, rechercheV, index Equiv mais je n’arrive à rien de concluant. Pourriez-vous m’aider s’il vous plait

Bonsoir @fred16,

Voici une proposition:

Dans la l’onglet “Extraction brute” j’ai du ajouter une colonne “G” afin d’extraire le numéro “Env” avec cette formule:

En “G5”

=STXT(N5;TROUVE("-";N5;1)+1;TROUVE("/";SUBSTITUE(N5;"-";"/";2))-TROUVE("-";N5;2)-1)*1

Tirer vers le bas autant que nécessaire.

Puis dans l’onglet “FONCIER”

En “L3”

=SIERREUR(INDEX('Extraction brute'!$O$5:$O$1551;EQUIV($I3&$K3;'Extraction brute'!$G$5:$G$1551&'Extraction brute'!$H$5:$H$1551;0));0)

Formule matricielle à valider avec les touches ctrl + maj + entrée et tirer vers le bas autant que nécessaire.

Ci-joint ton fichier en retour ICI==> fred16 V1 base Foncier 2017 .xlsx (290,7 Ko)

Cordialement.

1 J'aime

Bonjour,

Je vous remercie pour votre aide.
Par contre quand je mets la formule dans mon fichier (la 1ère formule STXT …), la formule reste afficher et je n’arrive pas à faire aboutir
Comment puis-je résoudre ce problème ?
Je vous remercie

J’ai trouvé, il a fallu que je mette mes cellules en nombre puis entrer et après remettre en texte et entrer

Bizarre tout de même mais ca fonctionne!! :grinning::grinning::grinning::grinning:

1 J'aime

Bonjour,

Je reviens sur a demande précédente qui fonctionne très bien :grinning: ! Encore merci !

Ma question est la suivante pour continuer :
Je souhaite que la formule utiliser pour l’onglet foncier soit utilisable pour tous les autres onglets (toujours en partant de la même extraction de BO)
Je vous joins le fichier sur lequel fred16base Foncier 2017 .xlsx (602,6 Ko)
j’ai essayé de mettre la formule sur l’onglet DUAV dans la cellule AG6 le résultat doit être 1500000 mais ca ne fonctionne pas et même chose dans l’onglet habitat dans la cellule AG3 résultat doit être = à 260000

Pourriez-vous me dire pourquoi cela ne fonctionne pas et comment résoudre le problème.

Par ailleurs, je souhaiterais que dans l’extraction BO une couleur se mette automatiquement si l’opération + compte + AP EPCE ne sont pas trouvé dans les divers onglets du fichier, je pensais à mettre une mise en forme conditionnelle, Est-ce que vous auriez une meilleure idée?

D’avance merci pour votre retour

Bonsoir @fred16,

Ça ne fonctionne pas, parce qu’il y a des doublons dans les recherches avec “EQUIV”.

Regarde cette capture d’écran et tu comprendras.

Capture

Pour remédier à ce problème, il faudra prendre une autre donnée en plus des 2 autres à moins que je ne trouve une autre solution.

Je regarderai ça plus tard, suis plutôt fatigué ce soir.

Cordialement.

Bonjour @fred16,

Voici une nouvelle proposition, qui semble tenir la route, a voir !

J’ai corrigé dans toutes les feuilles uniquement les colonnes “AG”, j’ai vu que tu poursuivais sur les colonnes d’à cotées, je te laisse le faire.

Donc j’ai pris en compte la colonne “F” de la feuille “1112Extraction brute” correspondant aux colonnes “F” des autres feuilles.

Pour plus de visibilité j’ai nommé les colonnes dans le “Gestionnaire de noms”

Voici les formules du “Gestionnaire de noms”:

AP_EPCP=DECALER(compte;;2;)    
compte=DECALER('1112Extraction brute'!$G$5;;;NBVAL('1112Extraction brute'!$G:$G)-4)
Opé=DECALER(compte;;-1;)

Ci-joint ton fichier ICI==> fred16 V2 base Foncier 2017 .xlsx (642,2 Ko)

Ce serait sympas de me faire un retour, pour savoir si tout va bien.

Cordialement.

1 J'aime

Bonjour mdo100,

Merci pour la réponse mais malheureusement ca ne fonctionne que jusqu’à la colonne AL.
Dans l’extraction de base il y a des colonnes supplémentaires et donc il n’y avait pas de correspondance avec les colonnes de l’onglet foncier. Le simple fait de supprimer les colonnes supplémentaires fait que ca fonctionne !!! :star_struck::thinking::yum::grinning::grin::joy::rofl:
SUPER SUPER SUPER MERCI !!!

Par contre pourrais-tu m’expliquer en détail le gestionnaire des noms pour APEPCP, compte et opé, que je ne connais pas du tout. pourquoi valeur (…) et fait référence à Decaler …

Je te remercie énormément pour le temps que tu accordes à mes demandes, si tu as besoin de temps (quand tu es fatigué, plusieurs demandes à traiter, …) ce n’est pas un souci je comprends tout à fait ca arrive à beaucoup de monde :stuck_out_tongue:.

En tout cas je trouve ce forum super et toi aussi dans les solutions apportées.
Merci pour ta future réponse et au plaisir de revenir prochainement

Re @fred16,

J’ai employé cette méthode dans ce cas précis, juste pour éviter de répéter par exemple pour la colonne “G” (compte) de “G5 à G1486”, même chose pour les colonnes “F & I”.

La formule:

compte=DECALER('1112Extraction brute'!$G$5;;;NBVAL('1112Extraction brute'!$G:$G)-4)

compte = le nom de la colonne, j’aurais pu lui donner un autre nom.
DECALER(‘1112Extraction brute’!$G$5;;; signifie que je commence a compter les valeurs à partir de “G5”;ligne 0;colonne 0;hauteur NBVAL(de toute la colonne G -4 lignes.

AP_EPCP=DECALER(compte;;2;)

Je me décale de 2 colonnes vers la droite de la colonne “G”, ce qui me renvoie à la colonne “I”.

Opé=DECALER(compte;;-1;)

Je me décale de moins une colonne vers la gauche de la colonne “G”, ce qui me renvoie à la colonne “F”.

Pas facile d’expliquer: je te renvoie à cette formation de @DocteurExcel.

https://www.formuleexcel.com/comment-utiliser-la-fonction-decaler-sur-excel/

Cordialement.

2 J'aimes

Bonjour Mdo100,

Je souhaite encore te remercier pour tout, cela fonctionne à merveille !!! :star_struck::stuck_out_tongue_winking_eye::stuck_out_tongue_closed_eyes:

J’ai encore un autre souhait avec ce fichier

Je souhaiterai mettre une formule (dans l’onglet de l’extraction ou autre part) qui permettrais d’identifier automatiquement par une couleur, une ligne qui existe sur l’extraction mais pas sur les autres onglets.
Comment pourrais-je faire ?

Je t’en remercie par avance

Coucou Mod100,

J’ai encore une demande :grin::grin: : :weary::weary:

dans le fichier ci-joint 27 12 base Foncier 2017 .xlsx (746,4 Ko)
, dans la cellule AO99 (en jaune) je dois mettre le montant (en jaune colonne X) qui est dans l’extraction correspondant au même numéro opé (colonne F) mais avec le numéro de compte 775 (colonne G de l’extraction).

La formule utilisée précédemment pour les autres cellules (=SIERREUR(INDEX(‘Extraction brute’!$O$5:$O$1551;EQUIV($I3&$K3;‘Extraction brute’!$G$5:$G$1551&‘Extraction brute’!$H$5:$H$1551;0));0)
ne fonctionne pas car il n’existe pas de correspondance dans les n° de compte.

Aurais-tu une solution a m’apporter ?? ca serait :+1::+1::+1:

Je t’en remercie par avance
je te souhaite de bonnes fêtes de fin d’année :christmas_tree::christmas_tree::christmas_tree:

Bonsoir @fred16,

La cellule “AO99” de la feuille “ECONOMIE” n’est pas jaune, la colonne “X” de la feuille “Extraction” n’est pas jaune.

Maintenant, je vois que la formule fonctionne parfaitement, mais si tu cherche la somme 275 025,60 avec le numéro “opé” 401O023 “compte” 775 il faut donc mettre dans la feuille “ECONOMIE” le numéro “AP EPCP” 401E15, sinon ça ne marche pas.

Ou alors je n’ais pas compris la question, ce qui est possible et à propos de question…

À celle ci je n’est pas de réponse, car il est pratiquement impossible à ma connaissance de faire des MFCs de feuille à feuille.

Et bien moi aussi, je te souhaite un bon réveillon du nouvel an et une année 2018 heureuse. :moneybag::four_leaf_clover::evergreen_tree::rainbow:

Cordialement.

1 J'aime

Bonjour Mdo,

J’ai encore une demande à vous faire :wink::wink:

Je souhaiterais mettre une formule dans l’onglet extraction brute colonne A pour qu’il me rapatrie le nom (NPRU ANRU UTLS …) de la feuil3

J’essaye une recherche V mais pb de valeur:disappointed_relieved::disappointed_relieved:

Pourriez-vous m’aider s’il vous plait

Merci d’avance
testBASE_DUAV 2018 - Copie.xlsx (118,6 Ko)

Bonjour @fred16,

En “A2”

=SIERREUR(INDEX(Feuil3!$A:$A;EQUIV($F2;Feuil3!$B:$B;0));"Pas de données")

Tirer vers le bas autant que nécessaire.

Fichier ICI==> 2 mois plus tard fred16.xlsx (153,4 Ko)

Cordialement.

1 J'aime

Super !!!:grin::grin:

Merci Mdo

Bonne journée :grinning::grinning: