Comment compter le nombre d'observations (dans Excel)?

J'ai deux feuilles de calcul dans un fichier Excel:

entreprises

  A       B      C     D     E
1 COMPANY SECTOR VAR_1 VAR_2 VAR_3
2 Sony    Tech         2.40  no
3 Ikea    Home   7     1.44  yes
4 ING     Bank   5        0  yes
5 BofA    Bank   0           no
6 Google  Tech            0  yes
7 Staples Home         5.24  no
8 Trump   Ego    5     9.99  yes
9 ABN     Bank         2.64  no

Cette feuille détient des centaines de VARs pour des milliers d'entreprises. Il y a beaucoup de valeurs manquantes (cellule vide est la valeur manquante, 0 est en fait une valeur observée). J'ai besoin de savoir combien d'observations que j'ai pour chaque VAR. J'ai besoin de (1) un ensemble de compter le nombre d'observations et (2) le nombre d'observations pour les entreprises qui sont dans le secteur bancaire. Dans la feuille ci-dessous, (1) est dans la colonne B, (2) dans la colonne C:

variables

   A       B     C
1 VARIABLE TOTAL BANK
2 VAR_1    4     2  
3 VAR_2    7     2
4
5          Some random comment...
6
7 VAR_3    8     3

Prenons VAR_1 comme un exemple. En regardant les companies feuille, il y a 4 observations de cette variable (7, 5, 0 et 5). Si on regarde seulement les banques (SECTOR == 'Bank') il y a 2 valeurs observées (5 pour ING et 0 pour BofA).

Il est possible que dans le variables feuille, il y a des commentaires entre les différentes variables (bien que jamais dans la colonne A; cette colonne contient un nom de variable ou est vide). Aussi, le ordre de variables pourrait ne pas être la même. Ainsi, dans companies pourrait dire

  ... GH     GI     GJ
1     VAR_40 VAR_41 VAR_42

tandis que dans variables il dit

    A
...
60  VAR_40
61  VAR_42
62  VAR_41

Ma question est: quelle formule peut calculer le nombre d'observations dans variables colonnes B et C? Toute aide est grandement appréciée.

InformationsquelleAutor Pr0no | 2013-04-21

1

Vous n'avez pas besoin de Formules pour cela. Vous pouvez utiliser un Pivot

Voir cette capture d'écran

Comment compter le nombre d'observations (dans Excel)?

SUIVI

À L'Aide De Formules

Comment compter le nombre d'observations (dans Excel)?

Pour la Var2, Var3, ajuster les colonnes de la formule en conséquence.

Par exemple B14 pour Var2 deviendra =COUNTA(D2:D9) et C14 deviendra =COUNTIFS($B$2:$B$9,$C$12,D2:D9,"<>")

DE SUIVI (À PARTIR DE COMMENTAIRES/CHAT)

Depuis votre tableau n'est pas continue, je vous recommande de VBA (UDF) approche afin que vous pouvez réellement copier la formule vers le bas 😉

Collez ces deux codes dans le Module

    Function getVarCount(rngVar As Range, strSector As String, rngRw As Range) As Variant
        getVarCount = "Incomplete Data in Formula"

        If rngVar Is Nothing Or _
           rngRw Is Nothing Or _
           Len(Trim(strSector)) = 0 Then Exit Function

        Dim aCell As Range, SectorRange As Range
        Dim colSector As Long, colVar As Long

        Set aCell = rngRw.Find(What:=rngVar.Value, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            colVar = aCell.Column
        Else
            getVarCount = "VAR Heading Not Found"
            Exit Function
        End If

        Set aCell = rngRw.Find(What:="Sector", LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            colSector = aCell.Column
        Else
            getVarCount = "SECTOR Heading Not Found"
            Exit Function
        End If

        Set SectorRange = ThisWorkbook.Sheets(rngRw.Parent.Name).Columns(colSector)

        Set aCell = SectorRange.Find(What:=strSector, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If aCell Is Nothing Then
            getVarCount = strSector & " Not Found"
            Exit Function
        End If

        getVarCount = Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(rngRw.Parent.Name).Columns(colVar)) - 1
    End Function

Function getVarSectorCount(rngVar As Range, strSector As String, rngRw As Range) As Variant
        getVarSectorCount = "Incomplete Data in Formula"

        If rngVar Is Nothing Or _
           rngRw Is Nothing Or _
           Len(Trim(strSector)) = 0 Then Exit Function

        Dim aCell As Range, SectorRange As Range
        Dim colSector As Long, colVar As Long

        Set aCell = rngRw.Find(What:=rngVar.Value, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            colVar = aCell.Column
        Else
            getVarSectorCount = "VAR Heading Not Found"
            Exit Function
        End If

        Set aCell = rngRw.Find(What:="Sector", LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If Not aCell Is Nothing Then
            colSector = aCell.Column
        Else
            getVarSectorCount = "SECTOR Heading Not Found"
            Exit Function
        End If

        Set SectorRange = ThisWorkbook.Sheets(rngRw.Parent.Name).Columns(colSector)

        Set aCell = SectorRange.Find(What:=strSector, LookIn:=xlValues, _
                    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

        If aCell Is Nothing Then
            getVarSectorCount = strSector & " Not Found"
            Exit Function
        End If

        '=COUNTIFS($B$2:$B$9,$C$12,D2:D9,"<>")
        getVarSectorCount = Application.WorksheetFunction.CountIfs(ThisWorkbook.Sheets(rngRw.Parent.Name).Columns(colSector), _
                                                                   strSector, _
                                                                   ThisWorkbook.Sheets(rngRw.Parent.Name).Columns(colVar), _
                                                                   "<>")
End Function

Vous pouvez appeler à partir de la cellule Excel comme nous l'avons discuté dans le Chat

Capture d'écran

Comment compter le nombre d'observations (dans Excel)?

C'est très bien, mais les variables de la feuille est en quelque sorte mon tableau de bord pour le jeu de données. Ce serait génial si je peux juste ajouter une formule pour obtenir ces résultats comme une colonne de la feuille de calcul. Auriez-vous par hasard pour savoir comment faire cela aussi?
Oui c'est possible. La mise à jour de la réponse
J'ai trouvé =HLOOKUP(A2;n!$A$1:$OC$2;2;FALSE) moi-même, mais cela ne permet pas de filtre sur des "Banques"
Post mis à jour. Vous aurez pour actualiser la page.
Merci il fonctionne très bien. Cependant, j'ai des centaines de variables et ils sont (1) pas toujours dans le même ordre sur le companies et variables feuille (2) sur la variables feuille, parfois interrompues par des commentaires. Donc, avec ces formules, j'en aurais pour la recherche de l'cellrange pour chaque variable manuellement (et re-vérifier à chaque fois les variables sont ajoutés, supprimés ou mis en arrière et en avant de la feuille). C'est pourquoi j'ai essayé de lire le nom de la variable dans variables (A2 pour VAR_1 par exemple) et d'automatiquement la recherche de la colonne correspondante dans companies (C2:C9). Pourriez-vous ajouter cette fonctionnalité?
Puissé-je voir un exemple de classeur afin que je puisse travailler en temps-réel.
Bien sûr, veuillez consulter dropbox.com/s/su9j6lhe1let98e/Stackoverflow.xlsx 🙂
laissez-nous continuer cette discussion dans le chat

InformationsquelleAutor Siddharth Rout

Vous devez vous connecter pour publier un commentaire.