Excel extraire avec SOMMEPROD
Utilisateur anonyme
-
Utilisateur anonyme -
Utilisateur anonyme -
Bonjour,
J’aurais besoin d’aide : y’a-t-il moyen avec SOMMEPROD d’extraire des caractères d’une zone définie.
J’ai sur un onglet une zone définie sur des numéros au format texte. Un extrait :
6161001
6161002
6161003
6162001
6162002
Pour m’éviter de lister un =SOMMEPROD(((cpt="6161001")+(cpt="6161002")+(cpt="6161003")+(cpt="6162001")+(cpt="6162002"))*(Dep))
et là ce n’est qu’un extrait,
Je voudrais extraire du cpt que le 616 ou le 6161 ou le 6162 sans passer par une colonne intermédiaire. Je n’ai pas trouvé comment lui dire.
Merci.
J’aurais besoin d’aide : y’a-t-il moyen avec SOMMEPROD d’extraire des caractères d’une zone définie.
J’ai sur un onglet une zone définie sur des numéros au format texte. Un extrait :
6161001
6161002
6161003
6162001
6162002
Pour m’éviter de lister un =SOMMEPROD(((cpt="6161001")+(cpt="6161002")+(cpt="6161003")+(cpt="6162001")+(cpt="6162002"))*(Dep))
et là ce n’est qu’un extrait,
Je voudrais extraire du cpt que le 616 ou le 6161 ou le 6162 sans passer par une colonne intermédiaire. Je n’ai pas trouvé comment lui dire.
Merci.
A voir également:
- Excel extraire avec SOMMEPROD
- Extraire une video youtube - Guide
- Liste déroulante excel - Guide
- Extraire le son d'une vidéo - Guide
- Word et excel gratuit - Guide
- Déplacer colonne excel - Guide
7 réponses
Bonjour Michel,
Merci. Je n'ai pas su retranscrire la syntaxe avec le sommeprod.
Je mets une extraction de fichier avec ce que j'ai déjà fait et des explications. En espérant que ça te guidera mieux.
https://www.cjoint.com/?ewgoBtLXjJ
Merci d'avance.
Merci. Je n'ai pas su retranscrire la syntaxe avec le sommeprod.
Je mets une extraction de fichier avec ce que j'ai déjà fait et des explications. En espérant que ça te guidera mieux.
https://www.cjoint.com/?ewgoBtLXjJ
Merci d'avance.
re,
sur la formule feuille synthèse A6 était du format "nombre"
pour trouver la somme des 616 il faut transformer en texte car gauche(....) renvoie du texte
donc
=SOMMEPROD((GAUCHE(cpt;NBCAR(A6))=CTXT(A6;0;VRAI))*(Dep))
mais j'avoue ne pas comprendre tes niveaux d'extraction
par ex: on veut à 15 heures un niveau d'extraction sur 3 chiffres et à 15h12 à 4 chiffres
ou
on veut les 615xxxx à 5 chiffres, les 616xxxx à 3 chiffres, les 617xxxxxxx à 4chiffres...? à 15heures
et à 15h12 les 615 à 3chiffres, les 616 à 6 chiffres, etc...
est tu autorisée à utiliser du VBA ?
dans l'attente
sur la formule feuille synthèse A6 était du format "nombre"
pour trouver la somme des 616 il faut transformer en texte car gauche(....) renvoie du texte
donc
=SOMMEPROD((GAUCHE(cpt;NBCAR(A6))=CTXT(A6;0;VRAI))*(Dep))
mais j'avoue ne pas comprendre tes niveaux d'extraction
par ex: on veut à 15 heures un niveau d'extraction sur 3 chiffres et à 15h12 à 4 chiffres
ou
on veut les 615xxxx à 5 chiffres, les 616xxxx à 3 chiffres, les 617xxxxxxx à 4chiffres...? à 15heures
et à 15h12 les 615 à 3chiffres, les 616 à 6 chiffres, etc...
est tu autorisée à utiliser du VBA ?
dans l'attente
Re,
Bon, OK, ça paraît toujours simple quand on y est dedans !... Mais je ne comprends pas ta question...
Mes niveaux d'extraction : Je fais avec ce que j'ai : une base de données de dépenses enregistrées au niveau le plus fin, les numéros de compte (cpt), de 3 à 10 chiffres, qui sont regroupés par type de dépenses, suivant une nomenclature sur des numéros à 2, 3 à 6 chiffres... (colonne des 61, ici les 2 premiers chiffres du cpte, mais d'autres sont à 3 ou 4), comme toutes les nomenclatures comptables (mon extrait n'était peut-être pas très parlant ;-))
Pour un suivi plus fin, je dois faire des sous-groupes de dépenses supplémentaires non prévus dans la base au sein de ce premier regroupement, que je ne peux obtenir qu'en attaquant les n° de cpte. pas toujours bien structurés de surcroît. Donc effectivement, le nombre de chiffres varie.
D'où ma demande de tronquer ce n° dans le sommeprod. pour éviter d'enchaîner les comptes détaillés.
Mais ce ne doit pas être possible sans passer par une colonne intermédiaire. J'avais rajouté la colonne A dans synthese pour intégrer ta formule.
Je vais donc construire une nomenclature "maison" pour remonter les données avec ta formule qui marche très bien (j'ai bien vu le format de A6 !!)
Merci beaucoup pour ton aide Michel !
Bonne soirée.
PS : Pour le VBA, ce n'est pas une question d'autorisation, je monte mon fichier comme je veux, mais il doit être utilisé par d'autres personnes, et en cas de souci, je ne saurai pas dépanner, et même si je savais, en cas d'absence, on met la panique... personne pratique chez nous.
Bon, OK, ça paraît toujours simple quand on y est dedans !... Mais je ne comprends pas ta question...
Mes niveaux d'extraction : Je fais avec ce que j'ai : une base de données de dépenses enregistrées au niveau le plus fin, les numéros de compte (cpt), de 3 à 10 chiffres, qui sont regroupés par type de dépenses, suivant une nomenclature sur des numéros à 2, 3 à 6 chiffres... (colonne des 61, ici les 2 premiers chiffres du cpte, mais d'autres sont à 3 ou 4), comme toutes les nomenclatures comptables (mon extrait n'était peut-être pas très parlant ;-))
Pour un suivi plus fin, je dois faire des sous-groupes de dépenses supplémentaires non prévus dans la base au sein de ce premier regroupement, que je ne peux obtenir qu'en attaquant les n° de cpte. pas toujours bien structurés de surcroît. Donc effectivement, le nombre de chiffres varie.
D'où ma demande de tronquer ce n° dans le sommeprod. pour éviter d'enchaîner les comptes détaillés.
Mais ce ne doit pas être possible sans passer par une colonne intermédiaire. J'avais rajouté la colonne A dans synthese pour intégrer ta formule.
Je vais donc construire une nomenclature "maison" pour remonter les données avec ta formule qui marche très bien (j'ai bien vu le format de A6 !!)
Merci beaucoup pour ton aide Michel !
Bonne soirée.
PS : Pour le VBA, ce n'est pas une question d'autorisation, je monte mon fichier comme je veux, mais il doit être utilisé par d'autres personnes, et en cas de souci, je ne saurai pas dépanner, et même si je savais, en cas d'absence, on met la panique... personne pratique chez nous.
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Bonjour, argixu.
Tu m'as fait passer des heures sur ton cas !
Pour un maigre résultat, puisque je viens seulement de me rendre à l'évidence que ta suggestion d'utiliser la fonction GAUCHE() ne peut pas marcher dans une formule bloquée dans une cellule, pour l'excellente raison que GAUCHE(cpt;3) extrait les 3 premiers caractères du mot placé sur LA MEME LIGNE dans la colonne cpt. Il faut donc obligatoirement recopier cette formule d'extraction dans une colonne supplémentaire de "table", ce que tu voulais éviter. J'ai essayé avec SOMME.SI() à la place de SOMME.PROD() ; c'est pareil ...
Donc ton idée de prévoir une colonne à l'avance était bonne !
Si dans la feuille "synthese" tu peux te contenter d'une seule ligne d'affichage à la fois, une seule colonne de plus dans "table" suffira. Sinon, il faut autant de colonnes supplémentaires que tu veux de lignes de sous-totaux dans "synthese".
Tu m'as fait passer des heures sur ton cas !
Pour un maigre résultat, puisque je viens seulement de me rendre à l'évidence que ta suggestion d'utiliser la fonction GAUCHE() ne peut pas marcher dans une formule bloquée dans une cellule, pour l'excellente raison que GAUCHE(cpt;3) extrait les 3 premiers caractères du mot placé sur LA MEME LIGNE dans la colonne cpt. Il faut donc obligatoirement recopier cette formule d'extraction dans une colonne supplémentaire de "table", ce que tu voulais éviter. J'ai essayé avec SOMME.SI() à la place de SOMME.PROD() ; c'est pareil ...
Donc ton idée de prévoir une colonne à l'avance était bonne !
Si dans la feuille "synthese" tu peux te contenter d'une seule ligne d'affichage à la fois, une seule colonne de plus dans "table" suffira. Sinon, il faut autant de colonnes supplémentaires que tu veux de lignes de sous-totaux dans "synthese".
Salut ; encore moi.
Finalement, je te propose une solution avec SOMME.SI().
Je t'ai préparé une feuille "synthese" avec 7 lignes de consultation des comptes, ce qui a nécessité de créer les 7 colonnes K à Q dans la feuille "table", avec attribution de 7 noms de plage. J'ai aussi protégé (validation des données) les formules en C3:C9.
https://www.cjoint.com/?ewtVdNDmKp
C'est beaucoup moins élégant que ce que tu cherchais à faire, mais au moins ça fonctionne, et sans VBA !
Je crois que ça mérite un bisou ?
Finalement, je te propose une solution avec SOMME.SI().
Je t'ai préparé une feuille "synthese" avec 7 lignes de consultation des comptes, ce qui a nécessité de créer les 7 colonnes K à Q dans la feuille "table", avec attribution de 7 noms de plage. J'ai aussi protégé (validation des données) les formules en C3:C9.
https://www.cjoint.com/?ewtVdNDmKp
C'est beaucoup moins élégant que ce que tu cherchais à faire, mais au moins ça fonctionne, et sans VBA !
Je crois que ça mérite un bisou ?
Bonjour,
Bien vu Raymond. En préparant ma codification "maison", je bute effectivement comme tu l'as bien vu sur les lignes qui doivent remonter des numéros à plusieurs niveaux. Et c'est bien par là qu'il faut commencer. Donc je cogite encore... Je reviendrai peut-être...
Mais j'y vois maintenant plus clair (pourtant ce soir j'en louche !...) pour construire un fichier qui soit facile à suivre par tous.
Alors un grand merci pour ton montage et le temps passé,
et bien sûr... un gros bisou bien mérité ;-)))
Bien vu Raymond. En préparant ma codification "maison", je bute effectivement comme tu l'as bien vu sur les lignes qui doivent remonter des numéros à plusieurs niveaux. Et c'est bien par là qu'il faut commencer. Donc je cogite encore... Je reviendrai peut-être...
Mais j'y vois maintenant plus clair (pourtant ce soir j'en louche !...) pour construire un fichier qui soit facile à suivre par tous.
Alors un grand merci pour ton montage et le temps passé,
et bien sûr... un gros bisou bien mérité ;-)))