Discussion :

[s92ylvain]

Bonjour tout le monde,
NewBize sous excel ! Apres beaucoup de recherche, sur ma feuil2 j'ai réussi à trouver la formule pour calculer le nombre d’occurrence d'une valeur CSD_FRA_CAR ds la colonne D supérieur à 19 . Voici la formule =NB.SI.ENS(B2:B594;">19";D2:D594;"CSD_FRA_CAR")
J'obtiens 25 ds une cellule sur cette Feuil2
Maintenant je souhaite que ce résultat "25" s'affiche dans une cellule sur la feuil1 . Les données brute resterons tjr ds la colonne B et D de la Feuil2.

J'avais essayé cette formule mais rien n'y fait .

Code Formule :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMMEPROD(NB.SI(INDIRECT("Feuil"&LIGNE(2)&"!B2:B594;">19"D2:D593");"CSD_FRA_CAR"))

se met à la place de : .
Merci grandement de votre aide.

[ARTURO83]

Bonjour,

Pas sûr d'avoir bien compris.
Vous voulez que le résultat de la feuille 2 soit recopié dans la feuille 1 et qu'il reste figé, même si les données de la feuille 2 changent? Si c'est le cas, ce n'est pas possible avec une formule, il faut passer par le VBA.
Sinon, appliquer la même formule dans la feuille 1.

Au sujet de la deuxième formule avec sommeprod, attention, les longueurs des plages doivent être identiques, ce qui n'est pas le cas ici.

En attendant un peu plus de précisions de votre part.

Cdlt

[Menhir]

J'obtiens 25 ds une cellule sur cette Feuil2

Quelle cellule ?

Maintenant je souhaite que ce résultat "25" s'affiche dans une cellule sur la feuil1.

Quelle cellule ? (bis)

En supposant que la formule est en A1 de Feuil2 et que tu veux le résultat en B2 de Feuil1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Worksheets("Feuil1").Range("B2").Value = Worksheets("Feuil2").Range("A1").Value

[s92ylvain]

Bonsoir ARTURO83 et Menhir,
Merci de votre réponse, en fait , toutes les semaines nous recevons par mail un fichier Excel de données brutes. Dans ce fichier, 3 colonnes m’intéressent où je souhaite extraire un résultat:

Exemple du fichier de la semaine1
la colonne A représente un numéro de dossier.
la colonne B l'ancienneté en jours de ces dossiers.
la colonne D représente des "nom de portefeuille" (CSD_FRA_CAR , etc )

Exemple ci-dessous, (le dossier 1809F86946 a 30 jours d’ancienneté dans le portefeuille CSD_FRA_CAR )

1809F86946	30	CSD_FRA_CAR
1809F95440	25	CSD_FRA_CAR
1809H26763	25	CSD_FRA_CAR
1809H20223	09	CSD_FRA_CAR
1810S79938	13	CSD_FRA_DANONE
1810S71238	28	CSD_FRA_DANONE

Notre service traite ces dossiers , les numéros de dossiers évoluent (création, fermeture) et leur jours d'ancienneté également.


En utilisant l'exemple mon objectif est le suivant
1) Récupérer la semaine1 le nombre de dossiers par portefeuille supérieurs à 19 jours d’ancienneté, dans le cas ci dessus on a :

3 pour le portefeuiile CSD_FRA_CAR
1 pour le portefeuiile CSD_FRA_DANONE


2) Je souhaite que ces résultats 3 et 1 figurent sur la première feuille "Feuil1" d'un autre fichier Excel XX ds les cellules A1 et B2.

Puis la semaine2, répéter 1) les résultats de la semaine 2 se retrouveront ds les cellules B1 et B2 , du fichier XX

Etc, toutes les semaines.

Y a-t-il une ou plusieurs formules afin d'obtenir ce résultat ?

Un grand merci de votre aide.

[ARTURO83]

Bonjour,

Besoin de compléments d'informations:
Le fichier reçu porte t-il toujours le même nom? si oui lequel? ou bien son nom comporte t-il le N° de la semaine?
les données reçues commencent-elles directement à la ligne 1?
Dans le fichier de récupération, les N° de semaines sont-ils indiqués, sur la première ligne?
Tous les portefeuilles sont-ils déjà listés dans ce fichier? si oui est-ce en colonne1? Ou alors doit-on afficher le nom du portefeuille dans la même cellule que la valeur trouvée?

Vous voyez, il y a un minimum d'informations à connaître pour ce que vous attendez, sinon vous allez nous retourner le sujet à chaque fois parce qu'il y aura quelque chose qui ne va pas ou qui manquera.
Mettez-vous à la place de ceux qui veulent vous aider, ils n'ont aucune idée sur la forme de vos fichiers et encore moins sur le rendu que vous en attendez.

En attendant de vous lire.

Cdlt

[eriiic]

Bonjour,

si tu as réussi ta formule sur une feuille, le moyen le plus simple est de la couper-coller dans l'autre feuille.
Toutes les références seront correctes. Tu pourras t'en servir de modèle pour les suivantes.
eric

[s92ylvain]

Bonsoir,

Arturo83,
le jeu des questions/réponses me parait adapté vu mon niveau Excel.
Oui le fichier reçu par mail porte le même nom à chaque fois: backlog.xlsx .
Les données de ce fichier backlog.xlsx commencent à la ligne 7. Seuls les collones A , B et D m'intéressent .


Ds mon fichier de récupération , Oui les semaines st indiquées et commencent à la ligne 30 colonne A.
Oui tous les portefeuilles st listé ds ce fichier et commencent à la ligne 30 colonne B .

En piece jointe mon fichier de récupération.
Les résultats de la semaine 1 à 14 ont été obtenus "manuellement " en utilisant le filtre du fichier backlog.
Mon but étant extraire ces données du fichier backlog pour obtenir le nombre de dossiers par portefeuille supérieurs à 19 jours d’ancienneté en utilisant une formule ou autre .
( le fichier backlog en piece jointe a été envoyé par mail semaine15 ) .

Cdt.

[eriiic]

Et les autres, ils sentent le fromage ???
Bye

[s92ylvain]

Eriic ,
J'avais essayé de couper-coller la formule mais ca ne fonctionnait pas sur mon autre feuille.
Cdt.

[ARTURO83]

Bonjour,

Le plus simple comme vous l'a proposé Eriiic, faites une copie de la feuille du fichier "Backlog" et collez-la dans la feuille "Import_Semaine" du fichier "TicketLong".
Allez sur la feuille Tabl_Graph et cliquez sur le bouton vert.
le code utilisé:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Sub Recup_Données()
    Dim DerLig_f1 As Long, DerLig_f2 As Long
    Application.ScreenUpdating = False
    Set f1 = Sheets("Tabl_Graph")
    Set f2 = Sheets("Import_Semaine")
 
    DerLig_f1 = f1.[B1000].End(xlUp).Row
    DerLig_f2 = f2.[A10000].End(xlUp).Row
 
    Formule = "=COUNTIFS(Import_Semaine!R1C2:R" & DerLig_f2 & "C2,"">19"",Import_Semaine!R1C4:R" & DerLig_f2 & "C4,R28C)"
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).FormulaR1C1 = Formule
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).Value = f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).Value
 
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

Avec le fichier
Pièce jointe 469172

Cdlt

[s92ylvain]

Bonjour ARTURO83 et eriiic ,

merci beaucoup,
Avec un fichier backlog ayant la même "structure", c'est exactement ce qu'il me fallait !!
Je vais prendre le temps de lire le code utilisé , le comprendre ! à terme le nombre de portefeuille evoluera d'içi la fin de l'année.
Un grand merci.
Cdt.

[s92ylvain]

Si j'ai bien compris le code

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
    DerLig_f1 = f1.[B1000].End(xlUp).Row    
B1 correspond à la colonne B  

    DerLig_f2 = f2.[A10000].End(xlUp).Row
A1 correspond à la colonne A  

Ça se complique avec la formule ci dessous. Pouvez-vous me la décrire un peu ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Formule = "=COUNTIFS(Import_Semaine!R1C2:R" & DerLig_f2 & "C2,"">19"",Import_Semaine!R1C4:R" & DerLig_f2 & "C4,R28C)"
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).FormulaR1C1 = Formule
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).Value = f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).Value

Si je souhaite ajouter les portefeuilles CSD_FRA_ATOS et CSD_FRA_STG

la formule deviendrait ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
    Formule = "=COUNTIFS(Import_Semaine!R1C2:R" & DerLig_f2 & "C2,"">19"",Import_Semaine!R1C4:R" & DerLig_f2 & "C4,R28C)"
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).FormulaR1C1 = Formule
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).Value = f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).Value
 
    f1.Range(Cells(DerLig_f1 + 1, "D"), Cells(DerLig_f1 + 1, "E")).FormulaR1C1 = Formule
    f1.Range(Cells(DerLig_f1 + 1, "D"), Cells(DerLig_f1 + 1, "E")).Value = f1.Range(Cells(DerLig_f1 + 1, "D"), Cells(DerLig_f1 + 1, "E")).Value
 
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

Cdt.

[ARTURO83]

Bonjour,

DerLig_f1 = f1.[B1000].End(xlUp).Row Recherche la dernière ligne de la colonne B de la feuille 1 "Tabl_Graph"

DerLig_f2 = f2.[A10000].End(xlUp).Row Recherche la dernière ligne de la colonne A de la feuille 2 "Import_Semaine"

La formule utilisée est "=NB.SI.ENS(Import_Semaine!$B$1:$B$76;">19";Import_Semaine!$D$1:$D$76;B$28)" traduite en VBA

La formule est unique, seules les plages où elle est appliquée seront modifiées, ici de B à C:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).FormulaR1C1 = Formule
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).Value = f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "C")).Value

devient, ici de B à E

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "E")).FormulaR1C1 = Formule
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "E")).Value = f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, "E")).Value

Si vous avez plusieurs portefeuilles, il vaut mieux dans ce cas , modifier les plages pour que la formule s'adapte automatiquement aux nombres de portefeuilles, vous n'avez plus qu'à ajouter les portefeuilles sur la lignes 28 et la programme s'adaptera.Il suffit de lui faire détecter la dernière colonne de la ligne 28, avec DerCol_f1 = f1.[B28].End(xlToRight).Column

et les 2 lignes précédentes deviennent, ici de B à la dernière colonne trouvée:
f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, DerCol_f1)).FormulaR1C1 = Formule
f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, DerCol_f1)).Value = f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, DerCol_f1)).Value

Le code en entier

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
Sub Recup_Données()
    Dim DerLig_f1 As Long, DerLig_f2 As Long, DerCol_f1 As Long
    Application.ScreenUpdating = False
    Set f1 = Sheets("Tabl_Graph")
    Set f2 = Sheets("Import_Semaine")
 
    DerLig_f1 = f1.[B1000].End(xlUp).Row
    DerCol_f1 = f1.[B28].End(xlToRight).Column
    DerLig_f2 = f2.[A10000].End(xlUp).Row
 
    Formule = "=COUNTIFS(Import_Semaine!R1C2:R" & DerLig_f2 & "C2,"">19"",Import_Semaine!R1C4:R" & DerLig_f2 & "C4,R28C)"
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, DerCol_f1)).FormulaR1C1 = Formule
    f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, DerCol_f1)).Value = f1.Range(Cells(DerLig_f1 + 1, "B"), Cells(DerLig_f1 + 1, DerCol_f1)).Value
 
    Set f1 = Nothing
    Set f2 = Nothing
End Sub

Cdlt

[s92ylvain]

Bonsoir,
ça fonctionne à merveille en rajoutant 2 portefeuilles.
un grand merci pour les explications du code

Cdt.