XL 2013 NB.SI sur plage variable + recherche à plusieurs critères

V

Vio_d

XLDnaute Nouveau
Bonsoir,

Je vous demande de l'aide car je patauge complètement et je ne trouve pas de solutions à mes problèmes. J'ai créé un fichier excel qui permet à des viticulteurs de renseigner leurs interventions (ce qu'ils font sur leurs parcelles) via un ensemble de formulaires. Ces formulaires remplissent un tableau "DONNEES" qui contient donc l'ensemble des interventions effectuées. A partir de ce tableau DONNEES :

1) je souhaite remplir automatiquement un tableau INDICATEURS qui compte le nombre de fois qu'une intervention a été effectuée. Pour cela, j'ai utilisé la fonction NB.SI. Mais elle fonctionne bien sur une plage "fixe" et mon tableau DONNEES a un nombre de lignes variables (car elles sont implémentées par des formulaires). Je n'arrive pas à "fixer" la formule, si une nouvelle ligne est ajoutée à DONNEES, la formule est décalée d'une ligne et ne comptabilise pas la nouvelle ligne. J'ai fouillé un peu du côté de la fonction DECALER mais je n'ai pas compris comment l'intégrer à un NB.SI

2) j'aimerais remplir automatiquement un tableau CALENDRIER qui récapitule les interventions effectuées par mois et par décade. Un ensemble d'interventions sont effectuées pour un mois + une décade précise. Par exemple, pour le mois "juillet" et la décade "D3", il y a deux interventions "produits phytosanitaires", j'aimerais que les cellules V3 et V4 du calendrier aient pour valeurs le code "Phy". Pour cela, je cherche une fonction (ou une macro ??) qui recherche toutes les interventions pour un mois et une décade précise et qui attribue un code dans le calendrier en fonction de l'intervention (si "récolte" -> code "R")

Je vous joins un extrait du fichier (sans les macros et les formulaires) avec le tableau DONNEES complété en espérant avoir été claire !! (j'ai remis des explications dans le fichier)

Merci beaucoup
 

Pièces jointes

  • interventions_viticulture.xlsx
    36.8 KB · Affichages: 34
Trier par date Trier par votes
chris

chris

XLDnaute Barbatruc
Bonjour

Plusieurs choses m'interpellent dans ton classeur
  1. pourquoi ne pas mettre les Données sous forme de tableau ?
    Excel connait à tout moment le nombre de lignes d'un tableau et donc le problème ne se pose pas
  2. La fonction NB.SI peut cependant prendre en compte des colonnes entières donc au pire A:A évitera aussi le problème.
  3. Quand on utilise NB.SI il est 1000 fois préférable de préparer le tableau en mettant les intitulés à compter :
    Code: 
    =NB.SI(DONNEES!A$3:A$60;"préparation du sol SLR")
    peut être remplacé par
    Code: 
    =NB.SI(DONNEES!A:A;D2)
    si D2 contient préparation du sol SLR plutôt que Nb préparation du sol et surtout la formule devient recopiable si on a bien intitulé la colonne D
    Tu peux toujours intituler la colonne E Nombre
  4. Il serait bien que ces intitulés soient concordant avec ceux des données ce qui n'est pas le cas : récolte d'une côté, vendange de l'autre, Tonte d'un côté, Tonte IR de l'autre, etc
  5. Avec plus de rigueur sur la structure des données, tu peux aussi analyser part tableau croisé dynamique ce qui est plus souple que les formules, formules placées dans un tableau rempli de cellules fusionnées donc assez peu manipulable...
  6. Pour le calendrier, il faut aussi revoir ton tableau d'actions : en l'état c'est difficilement exploitable : cellules fusionnées, libellés non cohérents avec les données, infos essentielles sous forme de phrases là encore non en cohérence avec les données...
 
Dernière édition:
Vote positif 0 Vote négatif
V

Vio_d

XLDnaute Nouveau
Bonsoir !

Merci pour ta réponse ! Alors je suis désolée, je ne comprends pas tout dans tes questions donc je vais y répondre du mieux possible

1) De quelles données parles tu ? Le tableau indicateur sert à implémenter une fiche de synthèse de l'itinéraire technique avec des graphiques etc (la fiche n'est pas présente dans l'extrait du fichier excel). L'idée de ce fichier en entier est de faire un prototype d'outils d'aide à la décision, d'où mon besoin d'automatisation entre le tableau brut DONNEES et la synthèse INDICATEURS

2) à 3) Oui, tu as tout à fait raison ! ça marche nickel. Je ne connaissais pas la synthaxe pour une colonne entière. Sur le coup des intitulés, tu as raison aussi, cependant le tableau INDICATEUR est un livrable en soit et servirait dans un rapport (d'où le Nb mais, je pourrais le mettre en légende, bref).

4) Oui les intitulés ne sont pas les mêmes , IR = inter-rang, SLR = sous le rang, j'ai oublié de précisé ce jargon... Le tableau INDICATEUR reprend ces termes dans les colonnes, ce qui fait qu'on s'y retrouve entre les deux

5) Justement je n'ai pas besoin de cette souplesse, il me faut un tableau fixe et non modifiable (le feuillet INDICATEURS sera verrouillé) toujours dans cette idée d'outils d'aide à la décision et de sorties "graphiques".

6) Les codes sont totalement cohérents avec les données. Les interventions sont juste regroupées en grand type pour une meilleure lisibilité du calendrier. Le tableau que tu vois en dessous du calendrier vide est un tableau de légende (d'où les phrases), ce n'est pas le tableau à compléter (qui lui est au-dessus). Je ne sais pas si je suis plus claire sur cette histoire de calendrier, c'est difficile de bien expliquer à l'écrit mais je suis preneuse si tu as des pistes de fonctions à me proposer (j'ai vu que RECHERCHEV peut se faire sur plusieurs critères mais il me faudrait en plus un IF pour remplacer les libellés de DONNEES par les codes du calendrier selon les différents libellés possibles). Ou alors je me prends trop la tête et il y a une solution plus simple !

Merci beaucoup, ça m'enlève une bonne épine :)
 
Vote positif 0 Vote négatif
chris

chris

XLDnaute Barbatruc
RE

1. Je parle de la feuille DONNEES

4. toi tu t'y retrouves mais pour une formule c'est une autre paire de manches.
Si tu veut faciliter la formulation il faut plus de cohérence (comme pour le point 6)

5. Rien n'est immuable : j'ai rarement vu des tableaux de bord ou d'aide à la décision qui n'évoluent un jour ou l'autre parce que les processus ou les besoins d'analyse changent...
Mais bon ce n'est qu'un conseil d'ami, tu fait comme tu veux...

6. J'ai bien compris ce que tu veux obtenir mais pour faire des calculs il faut déceler la cohérence de façon directe : tel que, c'est quasi inexploitable par formules.
Pour Phytosanitaire ou Récolte OK car ces données sont les mêmes dans la colonne Interventions de ta liste dans CALENDRIER que dans la colonne A de DONNEES mais comment retrouver
  • effeuillage qui est lui dans la colonne Signification ainsi que rognage/écimage qui en plus est inversé,
  • ou pire Travaux du sol IR ou Herbicides qui nécessite de décortiquer Interventions + Signification ?
Sans restructurer cette liste et mettre en cohérence les libellés...
 
Vote positif 0 Vote négatif
V

Vio_d

XLDnaute Nouveau
1) DONNEES est directement liée aux différents formulaires, si je la change, ça veut dire changer beaucoup de choses dans une dizaine de formulaires multipages.

4) Oui mais c'est moi qui fait le prototype donc ce qui compte c'est que je m'y retrouve et c'est tout.

6) je ne suis pas sûre que tu aies compris. Le tableau qui explique la signification des codes est un tableau de légende. Effeuillage est dans la colonne signification car correspond au code Ef. Il n'y a pas cette histoire de décortiquer : travail du sol IR sera rentré tel quel dans le tableau DONNEES, je cherche une façon de récupérer l'information (dans le calendrier vide) pour un mois et une décade associée puis de la remplacer par Wir et associer une couleur à l'aide de la mise en forme de données.
 
Vote positif 0 Vote négatif
V

Vio_d

XLDnaute Nouveau
j'ai trouvé une solution pour mon problème de calendrier : j'ai écrit une macro mais elle n'est pas du tout élégante... voilà un exemple pour un mois et une décade. Si jamais vous avez une idée sur comment simplifier cette macro, je suis preneuse car je dois pour l'instant la recopier pour chaque décade et chaque mois. J'ai essayé de faire une boucle sur les décades et une sur les colonnes d'insertion (T, U,V) mais sans succès pour l'instant!! Merci :)

VB: 
'JUILLET

  Sheets("DONNEES").Select
  ActiveSheet.Range("$A$1:$J$41").AutoFilter Field:=3, Criteria1:="Juillet"
  ActiveSheet.Range("$A$1:$J$41").AutoFilter Field:=4, Criteria1:="D1"
  Range("A3:A" & Range("A65536").End(xlUp).Row).SpecialCells(xlVisible).Select
  Selection.Copy
  Application.CutCopyMode = False
  Selection.Copy
  Sheets("CALENDRIER").Select
  Range("T3").Select
  ActiveSheet.Paste
  With Sheets("DONNEES")
  .Cells.AutoFilter
  End With
  For i = 3 To 7
  If Range("T" & i).Value = "préparation du sol SLR" Then
  Range("T" & i).Value = "Psr"
  Else
  If Range("T" & i).Value = "semis SLR" Then
  Range("T" & i).Value = "Ssr"
  Else
  If Range("T" & i).Value = "préparation du sol IR" Then
  Range("T" & i).Value = "Pir"
  Else
  If Range("T" & i).Value = "semis IR" Then
  Range("T" & i).Value = "Sir"
  Else
  If Range("T" & i).Value = "désherbage chimique SLR" Then
  Range("T" & i).Value = "Hsr"
  Else
  If Range("T" & i).Value = "désherbage chimique IR" Then
  Range("T" & i).Value = "Hir"
  Else
  If Range("T" & i).Value = "travail du sol SLR" Then
  Range("T" & i).Value = "Wsr"
  Else
  If Range("T" & i).Value = "travail du sol IR" Then
  Range("T" & i).Value = "Wir"
  Else
  If Range("T" & i).Value = "tonte SLR" Then
  Range("T" & i).Value = "Tsr"
  Else
  If Range("T" & i).Value = "tonte IR" Then
  Range("T" & i).Value = "Tir"
  Else
  If Range("T" & i).Value = "mulching SLR" Then
  Range("T" & i).Value = "Rsr"
  Else
  If Range("T" & i).Value = "mulching IR" Then
  Range("T" & i).Value = "Rir"
  Else
  If Range("T" & i).Value = "amendements basiques" Then
  Range("T" & i).Value = "Ab"
  Else
  If Range("T" & i).Value = "amendements organiques" Then
  Range("T" & i).Value = "Ao"
  Else
  If Range("T" & i).Value = "engrais" Then
  Range("T" & i).Value = "E"
  Else
  If Range("T" & i).Value = "décompaction du sol" Then
  Range("T" & i).Value = "D"
  Else
  If Range("T" & i).Value = "engrais foliaire" Then
  Range("T" & i).Value = "Efo"
  Else
  If Range("T" & i).Value = "prétaillage" Then
  Range("T" & i).Value = "Pr"
  Else
  If Range("T" & i).Value = "taille" Then
  Range("T" & i).Value = "T"
  Else
  If Range("T" & i).Value = "tirage bois" Then
  Range("T" & i).Value = "Tb"
  Else
  If Range("T" & i).Value = "pliage attachage" Then
  Range("T" & i).Value = "Pa"
  Else
  If Range("T" & i).Value = "brûlage sarments" Then
  Range("T" & i).Value = "Sf"
  Else
  If Range("T" & i).Value = "exportation sarments" Then
  Range("T" & i).Value = "Sex"
  Else
  If Range("T" & i).Value = "broyage sarments" Then
  Range("T" & i).Value = "Sbr"
  Else
  If Range("T" & i).Value = "ébourgeonnage" Then
  Range("T" & i).Value = "Eb"
  Else
  If Range("T" & i).Value = "épamprage" Then
  Range("T" & i).Value = "Ep"
  Else
  If Range("T" & i).Value = "levage/relevage" Then
  Range("T" & i).Value = "Re"
  Else
  If Range("T" & i).Value = "rognage/écimage" Then
  Range("T" & i).Value = "Ro"
  Else
  If Range("T" & i).Value = "effeuillage" Then
  Range("T" & i).Value = "Ef"
  Else
  If Range("T" & i).Value = "éclaircissage" Then
  Range("T" & i).Value = "Ec"
  Else
  If Range("T" & i).Value = "produits phytosanitaires" Then
  Range("T" & i).Value = "Phy"
  Else
  If Range("T" & i).Value = "récolte" Then
  Range("T" & i).Value = "R"

  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  End If
  Next i
 
Dernière édition:
Vote positif 0 Vote négatif
gosselien

gosselien

XLDnaute Barbatruc
Tu peux , envoyer dans ta feuille CALENDRIER , un raccourci venant d'une table de référence:

en colonne X "préparation du sol SLR" et toutes les possibilités
en colonne Y: "PSR" et tous les raccourcis

Tu encodes les raccourcis (venant éventuellement) d'une liste de validation, en colonne A de "donnees" et à côté tu auras l'intitulé (venant de la colonne X) et dans la macro (à revoir) tu envoie directement "SRL" ou "PIR" ou "SIR".
Le raccourci en colonne A , avec un rechercheV irait te chercher l'intitulé pour contrôle.

Tu vois ce que je veux dire ?

Je te fais un court exemple base sur ta feuille "donnees"
 
Vote positif 0 Vote négatif
V

Vio_d

XLDnaute Nouveau
Merci d'avoir regardé ! :)
Oui je vois !! Au lieu de passer par une table, je vais direct modifier mes différents formulaires pour que lorsqu'une intervention est rentrée, le code est automatiquement rentré aussi.
Une fois que j'ai le tableau DONNEES de prêt avec les différents raccourcis, je n'ai plus qu'à faire une macro pour filtrer et copier directement les codes dans le calendrier c'est bien ça ?
J'ai toujours le soucis des boucles sur les décades et le changement de colonnes (je ne sais pas si ma phrase est très claire...)
(ah oui et je suis sous 2013)
 
Vote positif 0 Vote négatif
V

Vio_d

XLDnaute Nouveau
Voilà où j'en suis :

VB: 
'JUILLET

    Sheets("DONNEES").Select
    ActiveSheet.Range("$A$1:$K$41").AutoFilter Field:=4, Criteria1:="Juillet"
    ActiveSheet.Range("$A$1:$K$41").AutoFilter Field:=5, Criteria1:="D1"
    Range("B3:B" & Range("B65536").End(xlUp).Row).SpecialCells(xlVisible).Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CALENDRIER").Select
    Range("T3").Select
    ActiveSheet.Paste
  
    With Sheets("DONNEES")
    .Cells.AutoFilter
    End With

Je cherche un moyen de faire une boucle sur les décades, j'ai essayé ces boucles :

VB: 
For Each i In Array("D1", "D2", "D3")
    For Each j In Array("T3", "U3", "V3")

    Sheets("DONNEES").Select
    ActiveSheet.Range("$A$1:$K$41").AutoFilter Field:=4, Criteria1:="Juillet"
    ActiveSheet.Range("$A$1:$K$41").AutoFilter Field:=5, Criteria1:=i
    Range("B3:B" & Range("B65536").End(xlUp).Row).SpecialCells(xlVisible).Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("CALENDRIER").Select
    Range(j).Select
    ActiveSheet.Paste
    Next j
  
    With Sheets("DONNEES")
    .Cells.AutoFilter
    End With
  
    Next i

Mais j'ai dû mal placer mes Next car seule la dernière décade du mois est enregistrée, et ce dans les 3 colonnes D1 D2 D3 ...
je vous mets un nouveau fichier modifié avec uniquement DONNEES, CALENDRIER et la macro!
(merci beaucoup en tous cas, j'ai bien avancé grâce à vos retours !!)
 

Pièces jointes

  • interventions_viticulture_2.xlsm
    41.5 KB · Affichages: 27
Vote positif 0 Vote négatif
gosselien

gosselien

XLDnaute Barbatruc
Je pense que ton code est mal parti..

Juste un exemple: tu filtres en dur sur juillet, comment alors remplir les tableaux en septembre ? tu vas changer le code de la macro ?
Je regarde si je sais faire un autre code qui scannerais toutes les données pour les mettre dans le calendrier sauf si une autre intervenant a fait qq chose entre temps :)
P.
 
Vote positif 0 Vote négatif
V

Vio_d

XLDnaute Nouveau
gosselien à dit:
Je pense que ton code est mal parti..

Juste un exemple: tu filtres en dur sur juillet, comment alors remplir les tableaux en septembre ? tu vas changer le code de la macro ?
Je regarde si je sais faire un autre code qui scannerais toutes les données pour les mettre dans le calendrier sauf si une autre intervenant a fait qq chose entre temps :)
P.
Cliquez pour agrandir...

Ah mais je comptais copier coller le même code 12 fois, juste en changeant le premier filtre sur le mois ! Je sais que c'est pas propre mais ça marche ;) (probablement moins efficace aussi)
 
Vote positif 0 Vote négatif
gosselien

gosselien

XLDnaute Barbatruc
Bonjour,

j'ai un peu travaillé et bousillé ton tableau de départ et tes formats condi, mais ça, c'est facile à refaire
regarde ici comment je copie (1 seul clic sinon ça se cumule) vers le calendrier.

P.
 

Pièces jointes

  • interventions_viticulture_2.xlsm
    49.5 KB · Affichages: 41
Vote positif 0 Vote négatif

Discussions similaires

M
  • Question
XL 2021 Créer une liste d'informations en colonne dans des cellules différentes
Réponses
3
Affichages
432
djidji59430
djidji59430
N
  • Question
XL 2019 Aide pour optimiser mon tableau Excel de suivi d'activité
Réponses
6
Affichages
312
Nicolaasss
N
A
  • Résolu(e)
Microsoft 365 NB.SI sous condition_fusionner deux formules
Réponses
10
Affichages
204
job75
job75
T
  • Résolu(e)
XL 2019 besoin de formuler pour se positionner à un endroit précis dans ma feuille
Réponses
11
Affichages
360
telemarrk
T
M
  • Résolu(e)
XL 2013 Recherche avec Vba
2 3
Réponses
31
Affichages
1 K
Dranreb
Dranreb
E
  • Résolu(e)
Microsoft 365 Remplacer données dans TS
Réponses
9
Affichages
401
TooFatBoy
TooFatBoy
S
  • Question
XL 2021 nbre occurence identique en fonction de plusieurs critères
Réponses
2
Affichages
209
stefb1968
S
R
  • Question
nb.si equiv indirect
Réponses
3
Affichages
462
rfroufrou
R
C
  • Question
Microsoft 365 Avoir un calendrier d'équipe du type Outlook, mais sur Excel pour bénéficier des champs de listes
Réponses
1
Affichages
373
ChTi160
ChTi160
Y
  • Question
XL 2016 Création d'un publipostage avec PJ PDF sur Outlook
Réponses
2
Affichages
137
Phil69970
Phil69970

Membres actuellement en ligne

18 membres et 110 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
315 089
Messages
2 116 092
Membres
112 658
dernier inscrit
doro 76