Fonctions pour créer fichier d'observations

Le 09/11/2018 à 20:30

Hello les pros d'Excel

Je suis une thésarde en écologie comportementale. J'observe le comportement d'insectes en laboratoire, et chaque semaine j'ai un tableau de données qui ressemble à ça:

aaaaa

Je crée en parallèle un fichier EXCEL qui regroupe mes données de toutes les semaines. Je souhaite calculer certaines occurrences et autres, voici les fonctions que je veux créer:

1. Si au moins un "Y" dans la ligne : écrire Y

2. Si au moins 3 "Y" à la suite dans la ligne : écrire Y

3. Calculer combien de blocs de "Y" différents dans la ligne (ex: dans la paire 2, j'ai 2 blocs de Y, dans les paires 4 et 6 j'en ai 1)

4. Calculer combien de "Y" dans le bloc le plus long dans la ligne (ex: dans la paire 2, le bloc le plus long contient 2Y, dans la paire 4, il en contient 4)

5. Calculer le nombre de "N" avant le premier "Y" dans la ligne

6. Calculer le nombre d'observations (= Y ou N) avant un bloc de 3 "Y" dans la ligne

Voilà, c'est super précis :/ Je m'intéresse beaucoup aux fonctions Excel, j'espère progresser avec vos astuces/conseils!

J'ai pu trouvé quelques pistes pour les premières fonctions qui sont plus simples (utilisation de "NB.SI") mais impossible de trouver nulle part ce qui concerne des "occurences qui se suivent" ou autre

Merci énormément à ceux qui prennent le temps!

Le 09/11/2018 à 23:31

Bonsoir Sylph et bienvenue sur EXCEL-PRATIQUE ,

Un fichier aurait été, lui aussi, le bienvenu!

Voir formules dans le fichier joint...

9sylph-comptage-serie-v1a.xlsx (17.04 Ko)
Le 09/11/2018 à 23:58

Re,

Une v2 avec une formule plus concise pour le calcul du "Nombre d'observations (Y ou N) avant un bloc de 3 Y" (voir colonne T).

13sylph-comptage-serie-v2.xlsx (17.54 Ko)
Le 10/11/2018 à 01:45

Bonjour,

Joli travail de MaPoire

Je me permet de me mettre dans son fichier.

Une version avec des fonctions personnalisées.

Ce sont des fonctions écrites en VBA qui s'utilisent comme une fonction habituelle d'excel.

Tu en as 5 :

=EC_minYok(plage, nbYmin) ' nbYmin = 1 ou 3 pour ton besoin mais tu peux mettre le nombre de Y voulu
=EC_maxY(plage)
=EC_nbBlocsY(plage)
=EC_nbN0(plage)
=EC_nbObsAv3Y(plage)

J'ai essayé de leur donner un nom parlant, elles commencent toutes par EC_ (comme écologie comportementale) pour être regroupées quand tu démarre une saisir de formule.

Pour les avoir dans ton fichier :

  • Alt+F11 pour ouvrir VBE
  • faire glisser le module Mod_EcoComp de ce classeur dans le projet de ton classeur (liste à gauche dans la fenêtre VBE)
eric

9sylph-comptage-serie.xlsm (22.71 Ko)
Le 10/11/2018 à 14:40

Re salut,

Merci beaucoup pour vos retours! MaPoire, top les formules

Déjà concernant les formules "simples", j'ai bien compris celle pour "un Y ou plus", et j'ai compris partiellement celle pour "Combien de N avant le premier Y": la partie avec la fonction "EQUIV" donne la position du 1er Y dans ma ligne d'observations, mais je ne saisie pas la reste de la formule... quelqu'un pour me l'expliquer?

Je vais me pencher un peu plus sur les autres formules plus complexes mais oui, si quelqu'un peut m'expliquer mieux l'utilisation de la formule SIERREUR, ça serait génial

Le 10/11/2018 à 14:43
eriiic a écrit :

Bonjour,

Joli travail de MaPoire

Je me permet de me mettre dans son fichier.

Une version avec des fonctions personnalisées.

Ce sont des fonctions écrites en VBA qui s'utilisent comme une fonction habituelle d'excel.

Tu en as 5 :

=EC_minYok(plage, nbYmin) ' nbYmin = 1 ou 3 pour ton besoin mais tu peux mettre le nombre de Y voulu
=EC_maxY(plage)
=EC_nbBlocsY(plage)
=EC_nbN0(plage)
=EC_nbObsAv3Y(plage)

J'ai essayé de leur donner un nom parlant, elles commencent toutes par EC_ (comme écologie comportementale) pour être regroupées quand tu démarre une saisir de formule.

Pour les avoir dans ton fichier :

  • Alt+F11 pour ouvrir VBE
  • faire glisser le module Mod_EcoComp de ce classeur dans le projet de ton classeur (liste à gauche dans la fenêtre VBE)
eric

Eric merci beaucoup pour ta solution également, hyper propre et visiblement pratique, mais je maîtrise pas du tout le VBA, mais j'ai bien envie de me pencher dessus

Le 10/11/2018 à 15:04

Bonjour,

pour faire simple, tu ouvres VBE (l'éditeur VBA) par Alt+F11.

Clic-droit sur le projet de ton classeur (à gauche) et 'Insérer / Module'.

Tu copie-colles dans le module (à droite) tout les texte du code ci-dessous.

(ou bien tu fais glisser le module d'un projet à l'autre pour l'y copier)

Enregistre ton classeur sous le type *.xlsm (choisir dans la liste déroulante le type) pour conserver les macros.

Les fonctions personnalisées sont maintenant utilisables dans ce classeur.

Code :

Option Explicit

Function EC_minYok(plage As Range, nbYmin As Long) As String
    Dim bl, i As Long, Ymax As Long
    bl = blocs(plage)
    For i = 2 To UBound(bl) Step 2
        Ymax = Application.Max(Ymax, bl(i))
    Next i
    If Ymax >= nbYmin Then EC_minYok = "Y" ' Else EC_minYok = ""
End Function

Function EC_maxY(plage As Range) As String
    Dim bl, i As Long, Ymax As Long
    bl = blocs(plage)
    For i = 2 To UBound(bl) Step 2
        Ymax = Application.Max(Ymax, bl(i))
    Next i
    EC_maxY = Ymax
End Function

Function EC_nbBlocsY(plage As Range) As Long
    EC_nbBlocsY = UBound(blocs(plage)) \ 2
End Function

Function EC_nbN0(plage As Range) As Long
    If UBound(blocs(plage)) > 1 Then EC_nbN0 = blocs(plage)(1)
End Function

Function EC_nbObsAv3Y(plage As Range) As Long
    Dim bl, i As Long, nbObs As Long, ok As Boolean
    bl = blocs(plage)
    If UBound(bl) > 1 Then
        For i = 1 To UBound(bl)
            If i Mod 2 = 0 And bl(i) >= 3 Then ok = True: Exit For
            nbObs = nbObs + bl(i)
        Next i
    End If
    If ok Then EC_nbObsAv3Y = nbObs
End Function

Private Function blocs(plage As Range)
    Dim datas, result, typeBloc As String
    Dim i As Long, j As Long
    datas = plage.Value
    ReDim result(1 To UBound(datas, 2))
    typeBloc = "N": j = 1
    For i = 1 To UBound(datas, 2)
        Select Case datas(1, i)
        Case "N"
            If typeBloc = "Y" Then typeBloc = "N": j = j + 1
            result(j) = result(j) + 1
        Case "Y"
            If typeBloc = "N" Then typeBloc = "Y": j = j + 1
            result(j) = result(j) + 1
        End Select
    Next i
    ReDim Preserve result(1 To j)
    blocs = result
End Function

eric

Le 10/11/2018 à 15:08
eriiic a écrit :

Bonjour,

pour faire simple, tu ouvres VBE (l'éditeur VBA) par Alt+F11.

Clic-droit sur le projet de ton classeur (à gauche) et 'Insérer / Module'.

Tu copie-colles dans le module (à droite) tout les texte du code ci-dessous.

(ou bien tu fais glisser le module d'un projet à l'autre pour l'y copier)

Enregistre ton classeur sous le type *.xlsm (choisir dans la liste déroulante le type) pour conserver les macros.

Les fonctions personnalisées sont maintenant utilisables dans ce classeur.

Code :

Option Explicit

Function EC_minYok(plage As Range, nbYmin As Long) As String
    Dim bl, i As Long, Ymax As Long
    bl = blocs(plage)
    For i = 2 To UBound(bl) Step 2
        Ymax = Application.Max(Ymax, bl(i))
    Next i
    If Ymax >= nbYmin Then EC_minYok = "Y" ' Else EC_minYok = ""
End Function

Function EC_maxY(plage As Range) As String
    Dim bl, i As Long, Ymax As Long
    bl = blocs(plage)
    For i = 2 To UBound(bl) Step 2
        Ymax = Application.Max(Ymax, bl(i))
    Next i
    EC_maxY = Ymax
End Function

Function EC_nbBlocsY(plage As Range) As Long
    EC_nbBlocsY = UBound(blocs(plage)) \ 2
End Function

Function EC_nbN0(plage As Range) As Long
    If UBound(blocs(plage)) > 1 Then EC_nbN0 = blocs(plage)(1)
End Function

Function EC_nbObsAv3Y(plage As Range) As Long
    Dim bl, i As Long, nbObs As Long, ok As Boolean
    bl = blocs(plage)
    If UBound(bl) > 1 Then
        For i = 1 To UBound(bl)
            If i Mod 2 = 0 And bl(i) >= 3 Then ok = True: Exit For
            nbObs = nbObs + bl(i)
        Next i
    End If
    If ok Then EC_nbObsAv3Y = nbObs
End Function

Private Function blocs(plage As Range)
    Dim datas, result, typeBloc As String
    Dim i As Long, j As Long
    datas = plage.Value
    ReDim result(1 To UBound(datas, 2))
    typeBloc = "N": j = 1
    For i = 1 To UBound(datas, 2)
        Select Case datas(1, i)
        Case "N"
            If typeBloc = "Y" Then typeBloc = "N": j = j + 1
            result(j) = result(j) + 1
        Case "Y"
            If typeBloc = "N" Then typeBloc = "Y": j = j + 1
            result(j) = result(j) + 1
        End Select
    Next i
    ReDim Preserve result(1 To j)
    blocs = result
End Function

eric

Merci encore Mais ce qui m'intéresse bien plus que d'avoir une solution pour mes calculs, c'est de comprendre pour progresser et reproduire, donc je pense davantage utiliser les fonctions déjà présentes dans Excel et j'espère comprendre comment marche "SIERREUR"

Le 10/11/2018 à 15:18

Et tu as bien raison

Le mieux est de consulter l'aide la fonction qui est très bien faite:

2018 11 10 15 14 47

Description

Renvoie une valeur que vous spécifiez si une formule génère une erreur ; sinon, elle renvoie le résultat de la formule. La fonction SIERREUR permet d’intercepter et de gérer des erreurs présentes dans une formule (formule : suite de valeurs, références de cellule, noms, fonctions ou opérateurs dans une cellule permettant de générer une nouvelle valeur. Une formule commence toujours par le signe égal (=).).

Syntaxe

SIERREUR(valeur, valeur_si_erreur)

eric

Le 10/11/2018 à 15:31

Je l'ai lu, et elle fait sens mais dans mon cas précis je ne comprend pas

Le"-1" fait référence au N avant le premier Y, mais à quel moment la formule me donne le nombre de N avant le premier Y ? Je comprend le bout de la formule qui permet de trouver le premier Y, mais vraiment pas l'utilisation de SIERREUR pour calculer le nombre de N qui précédent

Le 10/11/2018 à 15:41

Tu as un outil pour t'aider comprendre le fonctionnement d'une formule :

'Formule / Audit de formule / Evaluation d'une formule'

Il te permet de l'évaluer pas à pas.

Si tu le fais en R3 tu vois que EQUIV("Y";D3:M3;0) retourne #N/A car il ne trouve pas de Y.

A la place MaPoire a fait le choix d'afficher ""

Autre façon de faire : dans la zone d'édition de la formule tu sélectionne la partie qui t'intéresse : EQUIV("Y";D3:M3;0)

Faire F9 dessus évalue cette partie. Quitter par Echap pour ne pas remplacer la formule.

eric

PS : c'est Equiv() qui recherche la position du 1er Y, Sierreur() n'y a aucun rôle autre que d'éviter l'affichage de #N/A

Le 10/11/2018 à 15:55

Bonjour Sylph, eriiic,

Des éclaircissements sur SIERREUR()

Prenons la formule en R3 : =SIERREUR(EQUIV("Y";D3:M3;0)-1;"")

Elle est basée sur la recherche du premier Y dans la plage D3:M3

EQUIV("Y";D3:M3;0) va renvoyer le rang de la première cellule dans D3:M3 qui contient "Y".

Le nombre de N situés avant le premier Y est donc EQUIV("Y";D3:M3;0)-1.

Mais que ce passe-t-il s'il n'y a aucun Y dans cette plage D3:M3?

Hé bien EQUIV renverra une erreur de type #N/A.

On pourrait en rester là et dire que l'erreur signifie "pas de Y dans la plage".

On peut aussi utiliser la fonction SIERREUR(EQUIV("Y";D3:M3;0)-1 ; "")

Ceci va renvoyer EQUIV("Y";D3:M3;0)-1 si il y a un Y dans la plage et sinon renverra la chaine vide "" qui est (au goût de certain) plus élégant ou esthétique que de laisser s'afficher une erreur.

edit: désolé eriic, télescopage tardif de ma part

Le 10/11/2018 à 16:32

Pas de quoi l'être, ce sont tes formules après tout

Et puis différentes façons de dire les choses peuvent aider à la compréhension.

eric

PS : à voir ta maîtrise des formules tu ne serais pas copain avec mapomme des fois ?

Le 10/11/2018 à 16:36

Re eriiic,

eriiic a écrit :

PS : à voir ta maîtrise des formules tu ne serais pas copain avec mapomme des fois ?

Fichtre, diantre! Me voilà démasqué

Le 10/11/2018 à 16:50

Ca n'a fait tilt que cette am.

Bienvenue au petit nouveau alors

Le 11/11/2018 à 10:20

Merci beaucoup tous les deux pour votre aide, j'ai compris

Le 11/11/2018 à 11:47

Re-hello,

encore un soucis ça fait des heures que je planche sur la formule du "au moins 3 Y à la suite" : =SI(MAX(FREQUENCE(SI(D6:M6="Y";COLONNE(D6:M6));SI(D6:M6="N";COLONNE(D6:M6))))>=3;"Y";"")

Je comprend pas la partie FREQUENCE de la formule. De ce que je comprend, la partie "tableau_données" de la fonction veut que s'il y a un "Y" dans la ligne, je suis renvoyée à la position de la première observation de la ligne.

la partie "matrice_intervalle" de la formule veut que si la ligne contient un "N", je suis renvoyée à la position de la première observation de la ligne.

...Pas clair du tout pour moi merci de votre aide!

Je me demande aussi pourquoi dans la formule pour "Nombre d'observations (Y ou N) avant un bloc de 3 Y (v2)", dans la fonction EQUIV, la recherche de "YYY" se fait dans "D11:K11&E11:L11&F11:M11". Pourquoi ne pas rechercher dans toute la ligne..?

Merci!

Le 11/11/2018 à 13:19

Bonjour Sylph,

Voir le fichier joint pour explication...

9sylph-comptage-serie-v2-comm-a.xlsx (20.79 Ko)
Le 13/11/2018 à 16:31

Merci beaucoup pour toute l'aide apportee!

Rechercher des sujets similaires à "fonctions creer fichier observations"