Repérer date de début, date de fin

Bonjour

Je recherche une formule me permettant de repérer dans un tableau une date de début puis de fin d'une période considérée.

Ci joint un tableau simplifié, où il faudrait je suppose repérer la première cellule non vide, date de début et la dernière cellule non vide date de fin.

Merci pour l'aide

Cdt

Berlingo

Bonjour le forum,

la date du début et de la fin d'une période indiquée dans le tableau B4:AF15

=SIERREUR("Du "&INDEX($B$3:$AF$3;EQUIV(INDEX(B4:AF4;EQUIV(VRAI;B4:AF4<>"";0));B4:AF4;0))&" "&"au "&INDEX($B$3:$AF$3;EQUIV(2;1/(INDEX(B4:AF4;MAX(COLONNE(B4:AF4)*NON(ESTVIDE(B4:AF4)))-COLONNE(B4:AF4)+1)=B4:AF4)))&" "&A4;"")

Formule matricielle.

Bonjour

Merci pour la réponse rapide. J'ai intégré la formule dans mon tableau et ça fonctionne. super

Il me reste cependant un dernier soucis pas évident à résoudre.Si sur la même ligne (c'est à dire le même mois) j'ai trois cellules non vides en début de mois ( 1, 2, 3 mars par exemple), puis quatre en fin de mois 28,29,30,31 mars) , la formule ne repère pas la coupure et indique du 1 au 31 mars.

Le but serait d'obtenir du 1 au 3 mars et du 28 au 31 mars. Est ce que la formule peut repérer une scission ?

Merci en tous les cas pour la formule

Cdt

Berlingo

Re

Désolé, mais je viens de m'apercevoir que j'avais omis une chose. Le tableau d'origine se remplit par liaison avec une autre feuille du classeur, c'est a dire que chaque cellule contient : "Feuil 1!" ce qui implique que les cellules ne sont pas vides.

Il faudrait que je remplace dans la formule ci dessous , les cellules non vide par cellule contenant CA ou RTT ou CF et cellule vide par peut être : différent de CA ou RTT ou CF

Mais je ne suis pas sur de bien l'écrire;

=SIERREUR("Du "&INDEX($B$3:$AF$3;EQUIV(INDEX(B4:AF4;EQUIV(VRAI;B4:AF4<>"";0));B4:AF4;0))&" "&"au "&INDEX($B$3:$AF$3;EQUIV(2;1/(INDEX(B4:AF4;MAX(COLONNE(B4:AF4)*NON(ESTVIDE(B4:AF4)))-COLONNE(B4:AF4)+1)=B4:AF4)))&" "&A4;"")

Cdt

Berlingo

Bonjour,

Autre proposition : fonction personnalisée :

Function DETECTP(refJ As Range, refM As Range)
    Dim P(), i%, r%, k%, n%
    Application.Volatile
    If refJ.Rows.Count > 1 Then Exit Function
    If refM.Cells.Count > 1 Then Exit Function
    ReDim P(0): r = refM.Row: k = refJ.Column: n = refJ.Columns.Count - 1
    With refJ.Worksheet
        For i = k To k + n
            If .Cells(r, i) <> "" Then
                P(0) = P(0) + 1: ReDim Preserve P(P(0))
                P(P(0)) = "du " & i - k + 1
                Do While .Cells(r, i + 1) <> ""
                    i = i + 1: If i = k + n Then Exit Do
                Loop
                P(P(0)) = P(P(0)) & " au " & i - k + 1 & " " & LCase(refM)
            End If
        Next i
    End With
    If P(0) > 0 Then
        DETECTP = P
    Else
        DETECTP = ""
    End If
End Function

Elle renvoie un tableau indiquant le nombre de périodes identifiées et les dates de chaque période.

Elle est donc à saisir sur sélection de 2 cellules voisines (au moins) et à valider matriciellement (Ctrl+Maj+Entrée)

Ne renvoie rien s'il n'y a rien.

En argument la plage (ligne) comportant les numéros de jours, et la cellule portant le nom du mois.

Elle est recopiable sur les lignes suivantes.

L'ayant validée sur deux cellules, elle ne fournit qu'une réponse. Si on voit 2 (ou plus) dans la première cellule, on resélectionne en étendant la sélection sur autant de cellule que le nombre de la première +1. On édite en se positionnant dans la barre de formule et on revalide matriciellement, toutes les réponses apparaîtront.

Cordialement.

Si tes cellules ne sont pas vides... ! Il faut faire en sorte qu'il se remplisse avec des "" !

Bsr

j'ai repris la formule avant d'étudier la macro, j'ai résolu le problème des cellules non vide mais pas entierrement

=SIERREUR("Du "&INDEX($B$3:$AF$3;EQUIV(INDEX(B4:AF4;EQUIV(VRAI;B4:AF4<>"";0));B4:AF4;0))&" "&"au "&INDEX($B$3:$AF$3;EQUIV(2;1/(INDEX(B4:AF4;MAX(COLONNE(B4:AF4)*NON(ESTVIDE(B4:AF4)))-COLONNE(B4:AF4)+1)=B4:AF4)))&" "&A4;"")

Par contre j'ai un soucis au niveau de la fin des périodes qui est toujours le 31 .

Ci joint le fichier plus explicatif

Merci pour l'aide

Cdt

Berlingo

Demo 2

Bsr

Je cherchais à modifier la formule ci dessous qui ne prend plus en compte l'arrêt des périodes dans mon nouveau fichier, mais seulement le début de la période.

J'ai reçu en réponse le même fichier que cette après midi qui contient une macro.

J'ai commencé à regarder mais si je rajoute une deuxième période en janvier par exemple , elle n 'est n'apparaît pas en colonne AI. Seule celles ce mars sont pris en compte.

Y a t il quelque chose que je dois rajouter ?

Cdt

Berlingo

=SIERREUR("Du "&INDEX($B$3:$AF$3;EQUIV(INDEX(B4:AF4;EQUIV(VRAI;B4:AF4<>"";0));B4:AF4;0))&" "&"au "&INDEX($B$3:$AF$3;EQUIV(2;1/(INDEX(B4:AF4;MAX(COLONNE(B4:AF4)*NON(ESTVIDE(B4:AF4)))-COLONNE(B4:AF4)+1)=B4:AF4)))&" "&A4;"")

Tu relis mon premier post, et tu verras que la formule étant prérentrée sur tous les mois, s'il n'y a rien, elle n'affiche rien, s'il y a une période, elle affiche 1 dans la première colonne où elle se trouve et la période (dates) dans la seconde. S'il y a plus de périodes distinctes, elle va afficher 2, 3... dans la première colonne mais n'affichera que les dates de la premières, donc dans ce cas :

tu resélectionnes les 2 cellules (sur 2 colonnes) où se trouve la formule et, si 2 tu étends à la colonne suivante, si 3 tu étends la sélection à 4 colonnes... puis, cet ajustement fait, tu édites la formule, il suffit pour cela de positionner le curseur dans la barre de formule, là il ne te reste plus qu'à la revalider avec la combinaison de touches Ctrl+Maj+Entrée et tous les résultats apparaîtront.

Cordialement.

Bonjour

Merci pour les explications que j'ai bien suivies. Le tableau exemple fonctionne bien.Je dois présent le mettre en place dans mon dossier. Mais avant j'aurais trois questions:

Serait il possible de remplacer dans la macro, cellules vides par cellule =0 et cellules non vide par différent de 0. En effet comme expliqué, les cellules font référence à une autre feuil du classeur et elles contiennent soit un zéro , soit un sigle. Cela m'éviterait donc de modifier tout mes tableaux par cellules vides si autres cellules vides.

Je souhaiterais ajouté juste après la colonne AH qui contient la formule, quatre autres colonnes , puis à nouveau une colonne avec la formule s'il y avait une deuxième période, est ce possible ? J'ai essayé et je n'y arrive pas

Enfin lorsque la troisième ou quatrième colonne contient la formule et qu'il n'y pas de date à écrire , il apparaît #N/A. Y aurait il possibilité de masquer ce symbole avec la formule si erreur ou autre ?

Merci encore pour le temps consacré à ce dossier.

Bonne fin d'aprem

Cdt

Norberlingo

Bonsoir,

Pour le 0 :

dans cette ligne :

            If .Cells(r, i) <> "" Then

il suffit de remplacer "" par 0.

(Attention les cellules vides [vraiment vides] renvoient 0 dans ce cas, toute autre valeur, texte ou nombre, y compris "", sera prise en compte...)

Je souhaiterais ajouté juste après la colonne AH qui contient la formule, quatre autres colonnes , puis à nouveau une colonne avec la formule s'il y avait une deuxième période, est ce possible ? J'ai essayé et je n'y arrive pas

Là, je ne comprends pas bien ce que tu veux ?

SIERREUR ne fonctionnera pas pour les réponses manquantes par rapport à la sélection initiale, Excel renvoie donc N/A. J'ai donc fait un petit ajout pour fournir une réponse dans ce cas.

Function DETECTP(refJ As Range, refM As Range)
    Dim P(), i%, r%, k%, n%
    Application.Volatile
    If refJ.Rows.Count > 1 Then Exit Function
    If refM.Cells.Count > 1 Then Exit Function
    ReDim P(0): r = refM.Row: k = refJ.Column: n = refJ.Columns.Count - 1
    With refJ.Worksheet
        For i = k To k + n
            If .Cells(r, i) <> "" Then
                P(0) = P(0) + 1: ReDim Preserve P(P(0))
                P(P(0)) = "du " & i - k + 1
                Do While .Cells(r, i + 1) <> ""
                    i = i + 1: If i = k + n Then Exit Do
                Loop
                P(P(0)) = P(P(0)) & " au " & i - k + 1 & " " & LCase(refM)
            End If
        Next i
    End With
    For i = UBound(P) + 1 To Application.Caller.Cells.Count - 1
        ReDim Preserve P(i): P(i) = ""
    Next i
    If P(0) > 0 Then
        DETECTP = P
    Else
        DETECTP = ""
    End If
End Function

Lorsqu'on fait des fonctions personnalisées, pour des cas auxquels les fonctions génériques d'Excel ne répondent pas (ou pas de façon simple), La personnalisation maximale ferait qu'on ne pourrait les utiliser hors du cas pour lequel elles ont été conçues.

On répond tout de même à un certain de cas similaire, et on peut généralement les adapter à des cas proches pour pouvoir élargir leur champ d'action.

Au cas particulier, la personnalisation réside dans l'adaptation à ton type de tableau :

On fournit en argument à la fonction ta ligne portant les numéros de mois : elle est utilisée par la fonction pour déterminer la longueur de ligne à tester et son positionnement en colonnes (donc son contenu importe peu, n'importe quelle ligne de même longueur comançant à la même colonne et finissant à la même ferait l'affaire). Cela parce qu'on calcule le numéro du jour par la position trouvée, mais on pourrait effectivement prendre en compte le contenu pour le renvoyer...

Et le 2e argument définit la position en ligne à tester, et là son contenu sert à fournir la réponse (nom de mois). Là, on renverrait un autre contenu s'il était différent.

L'adaptation à ta forme de tableau est qu'on traite une plage horizontale exclusivement.

L'autre aspect est qu'on renvoie plusieurs réponses, d'où nécessité de validation matricielle (comme pour toute fonction de l'espèce dans Excel. Elles sont renvoyées sur un même ligne, donc aussi dans une plage horizontale.

On peut faire diverses adaptations en vue de traiter des configurations différentes.

Tant qu'on est sur une réponse matricielle, elle devra naturellement s'inscrire dans une plage de cellules qu'on sélectionne au préalable.

Si tu souhaites individualiser les réponses, ce n'est pas impossible, bien sûr, mais il faut modifier la réponse renvoyée par la fonction... En gros, elle fera les mêmes calculs, mais n'en renverra que la partie qu'on lui demande (3e argument par exemple pour lui demander la 1re, la 2e ou la 3e... période).

Cordialement

Bonsoir

Merci pour toutes les réponses. Je vais essayer d'intégrer tout cela.

Pour répondes à la question ci-dessous , je joins mon fichier où j'ai indiqué ce que je cherche à obtenir , c'est plus simple.

"Je souhaiterais ajouté juste après la colonne AH qui contient la formule, quatre autres colonnes , puis à nouveau une colonne avec la formule s'il y avait une deuxième période, est ce possible ? J'ai essayé et je n'y arrive pas

Là, je ne comprends pas bien ce que tu veux ? expliquer

En tous les cas grand merci pour le temps passé à m'aider

Cdt

Berlingo

Bonjour,

Version "individualisée", comme annoncée :

Function DETECTP(refJ As Range, refM As Range, Optional rgp As Integer = 0)
    Dim P(), i%, r%, k%, n%
    Application.Volatile
    If refJ.Rows.Count > 1 Then Exit Function
    If refM.Cells.Count > 1 Then Exit Function
    ReDim P(0): r = refM.Row: k = refJ.Column: n = refJ.Columns.Count - 1
    With refJ.Worksheet
        For i = k To k + n
            If .Cells(r, i) <> 0 Then
                P(0) = P(0) + 1: ReDim Preserve P(P(0))
                P(P(0)) = "du " & i - k + 1
                Do While .Cells(r, i + 1) <> ""
                    i = i + 1: If i = k + n Then Exit Do
                Loop
                P(P(0)) = P(P(0)) & " au " & i - k + 1 & " " & LCase(refM)
            End If
        Next i
    End With
    If P(0) > 0 And rgp <= UBound(P) Then
        DETECTP = P(rgp)
    Else
        DETECTP = ""
    End If
End Function

N'est plus matricielle (ne renvoie qu'une seule valeur à la fois).

Cordialement.

Bonjour

Merci une nouvelle fois pour la macro que je viens de découvrir et cela correspond à mes besoins de créer de nouvelles colonnes.

J'ai intégré une base et un tableau dans mon classeur pour obtenir les 0 afin de vérifier le bon fonctionnement de la macro. Apparement j'ai un bug dans l'onglet "tableau", car j'ai bien le début des périodes qui s'affiche mais à chaque fois ça se termine en fin de mois ( marque le 31) au lieu de la date réelle et donc je n'ai plus qu'une période même quand il y en a deux .

Je joins le modèle qui sera plus explicatif .

Bonne après midi

Cdt

Berlingo

Bonsoir,

Au temps pour moi ! J'ai bien rectifié le premier "" en 0, mais il y en a un second (pour détecter la fin de la période) !

                Do While .Cells(r, i + 1) <> 0

Je te laisse mettre le 0 à la place du "" et provoquer le recalcul...

Cordialement.

Bonsoir

Merci, j'ai modifié et cela fonctionne sur mon tableau essai.

J'ai mis la macro sur mon tableau de travail réel et j'ai " #NOM?" qui s'affiche dans toutes les cellules contenant la formule=DETECTP($B$6:$AF$6;$A7) ou=DETECTP($B$6:$AF$6;$A7,1)

La seule différence que j'ai avec mon tableau essai c'est que la ligne date des jours est en A6 au lie de A3 et celle des mois débute en A7 au lieu de A4. Est ce que l'erreur peut venir de celà ?

Cdt

Berlingo

Non ! L'erreur #NOM? vient qu'un nom n'est pas reconnu, en l'occurrence le nom de la fonction...

Où l'as-tu placée ? Elle doit être dans un module Standard.

Bonsoir

J'ai changé son positionnement et ça à l'air de marcher.

Je vérifie tout ça et vous tiens au courant demain.

En tous les cas un grand grand merci.

Bonne fin de soirée

Cdt

Berloingo

Bonjour

J'ai réussit à mettre en place la macro dans mon tableau et un grand merci car ça fonctionne

Par contre je viens de m'apercevoir que je n'avais pas penser à certaines choses en voulant comptabiliser le nombre de sigles d'une manière trop simple.

Je joins mon tableau qui est plus parlant que de longues phrases. J'essaie de trouver une solution a ce nouveau petit problème.

En tous les cas merci encore pour tout ce dévouement.

Belle près midi

Cdt

Berlingo