Comment puis-je extraire des données d'un tableau Excel en fonction d'un nombre variable de critères ?

Votre cas d'utilisation est parfait pour Les fonctions de base de données d'Excel . Pour les utiliser, vous devrez d'abord déplier vos données de sorte que chaque ligne corresponde à une observation dans vos données. Cela signifie que vous aurez 4 colonnes : Country , State , Year y Salary .

Vous pouvez ensuite utiliser DGET() pour obtenir une entrée spécifique dans la base de données et DAVERAGE() pour obtenir une moyenne. Les critères des fonctions de base de données doivent être présentés sous forme de paires de cellules où le champ de filtrage se trouve au-dessus et la valeur à filtrer en dessous. Voici un exemple concret :

La formule de la cellule I10 est la même que celle de la cellule I9, sauf qu'elle utilise DAVERAGE à la place.

• En I9 entrez =IFERROR(DGET($B$4:$E$12,$E$4,$G$4:$I$5),"")
• En I10 entrez =IFERROR(DAVERAGE($B$4:$E$12,$E$4,$G$4:$I$5),"")

El IFERROR() autour, il s'assure que DGET n'affiche pas #NUM ! lorsque vous n'avez pas fourni de valeurs dans les trois entrées de la ligne 6. En effet, il ne peut pas trouver une seule valeur pour le salaire si, par exemple, vous ne lui indiquez pas l'année sur laquelle il doit filtrer.

Je pense qu'une façon de procéder est de nommer la colonne - Par exemple, faites un clic droit sur la cellule avec la valeur 1400 (salaire de Shanghai pour 2013) et define name

Ensuite, dans une autre cellule ou simplement en faisant référence à cette cellule - l'avantage de cette méthode est que peu importe si cette cellule est déplacée, elle conservera le contenu que vous souhaitez.

Je suis d'accord avec Dave Rook, c'est l'endroit où vous devriez utiliser SQL.

Il est également possible de le faire entièrement avec des formules Excel (qu'est-ce qui ne l'est pas ?). Voici comment :

Tout d'abord, vous devez trouver un moyen d'adresser chaque colonne. Vous avez deux options pour cela.

1. Tout d'abord, (comme Dave Rook l'a suggéré) plus simple et plus "beau", est de définir des plages nommées. Assurez-vous simplement que chaque plage nommée est suffisamment grande pour contenir tous les enregistrements futurs (ou utilisez une sorte de plage nommée dynamique). Malheureusement, cela nécessite un clic pour chaque colonne, et ne peut donc pas s'adapter à une centaine de colonnes.

2. L'autre option (que je préfère pour les calculs lourds) consiste à générer les chaînes d'adresses des plages de colonnes en utilisant, par exemple, la méthode suivante =ADDRESS(ROW(C3);COLUMN(C3);4;;"sheet1") & ":" & ADDRESS(ROW(C3)-1+$A$2;COLUMN(C3);4) pour accéder aux données sur la colonne C avec le nombre d'enregistrements stockés dans la cellule A2 . Ensuite, vous adressez la gamme en utilisant INDIRECT() partout, alors que vous devriez normalement coller la plage nommée de la méthode 1.

Une fois que toutes vos colonnes (champs) sont des plages de noms, vous pouvez effectuer toutes sortes de tâches de consultation et de référence sur elles.

Par exemple, pour identifier le nombre d'enregistrements qui contiennent le mot "Shanghai", vous utiliseriez =MATCH("Shanghai";State;0) , donde State est la plage nommée pour la colonne "État".

Une fois que vous avez le numéro d'enregistrement (disons qu'il est dans la cellule X10 vous pouvez obtenir des informations sur le salaire avec =INDEX(Salary;X10) .

Toutes sortes de tâches sont possibles dans les formules Excel ; par exemple, vous pouvez créer une liste de unique (ou les valeurs qui correspondent à certains critères), vous pouvez calculer toute forme de somme/moyenne. Vous pouvez même les trier (oui !).

Veuillez noter que dans ma région, j'utilise le point-virgule. ; comme séparateur d'argument (pas de virgule). Les pays anglophones utilisent généralement , et dans ce cas, pour utiliser mes formules, vous devrez remplacer tous les éléments suivants ; -> , .

Avec un peu de préparation, cela peut aussi se faire facilement dans Excel. (Bien que cela puisse être plus facile et plus naturel avec SQL).

La préparation : utiliser des plages nommées comme @Adam l'a suggéré -. country pour le pays, state pour l'État, et j'ai également nommé chacune des années dans ce format year2012 .

Vous pouvez ensuite créer votre feuille de réponses

     A              B         C         D
1 salary in     Shanghai    2013    =IFERROR(INDEX(INDIRECT("year"&C1),MATCH(B1,state,0)),INDEX(INDIRECT("year"&C1),MATCH("Other",state,0)))
2 salary in     Hubei       2012    =IFERROR(INDEX(INDIRECT("year"&C2),MATCH(B2,state,0)),INDEX(INDIRECT("year"&C2),MATCH("Other",state,0)))
3 average       China       2013    =AVERAGEIF(country,B3,INDIRECT("year" & C3))
4 highest       China       2012    {=INDEX(state,MATCH(MAX(IF(country=B4,INDIRECT("year"&C4))),INDIRECT("year"&C4),0))}

Chacune de ces lignes est dynamique, de sorte que vous pouvez modifier vos "questions" selon vos besoins. La chose importante à noter est que la 4ème formule (la plus élevée) est la formule Array qui nécessite l'utilisation de CTRL+SHIFT+ENTER en y entrant.

p.s. Votre réponse à la 3ème question est fausse, le salaire moyen en Chine en 2013 devrait être de 1333.

Adoptez cette approche VBA, elle devrait fonctionner sans trop d'ajustements. La gestion des erreurs pourrait être mise à jour, cependant :

Private Sub get_money(byval country as string, byval city as string, byval year as string)
    if country == "" then country == "nope" end if
    if city == "" then city =="nope" end if
    if year == "" then year =="nope" end if
    if autofiltermode = true then autofiltermode = false

    dim all_columns as double
    all_columns = thisworkbook.activesheet.range("A1").currentregion.columns.count
    with thisworkbook.activesheet
        for cell_position = 3 to all_columns
            if .cells(1,cell_position) == year
                year_to_filter = cell_position
                cell_position = all_columns
            end if
        next cell_position
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=1, Criteria1:=country, Operator:=xlFilterValues
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=2, Criteria1:=city, Operator:=xlFilterValues
        .Range(.cells(1,1), .cells(1,all_columns)).AutoFilter Field:=cell_position, Criteria1:=year, Operator:=xlFilterValues 
    end with      
end sub