Comment extraire un sous-réseau d'un tableau dans une fonction de feuille de calcul ?

Question

Existe-t-il un moyen d'obtenir dans Excel un tableau d'une taille inférieure à celle du tableau de départ dans une fonction de la feuille de calcul d'une cellule ?

Donc, si je l'avais fait :

{23, "", 34, 46, "", "16"}

Je finirais avec :

{23, 34, 46, 16}

que je pourrais ensuite manipuler avec une autre fonction.

Conclusion : Si je devais en faire beaucoup, j'utiliserais sans aucun doute la solution du peigne UDF de jtolle. La formule utilisée par le CPP est proche, mais en me plongeant dans les tests, j'ai découvert qu'elle donnait des erreurs dans les emplacements vides, qu'elle manquait la première valeur et qu'il existait un moyen plus simple d'obtenir les numéros de ligne :

=IFERROR(INDEX($A$1:$A$6, SMALL(IF(($A$1:$A$6<>""),ROW($A$1:$A$6)),ROW(1:6))),"")

qui doit être saisie comme une formule de tableau (CTRL-SHIFT-ENTER). Si elle est affichée, elle doit être saisie dans une zone au moins aussi grande que l'ensemble de résultats afin d'afficher tous les résultats.

Answer 1

Si tout ce que vous voulez faire, c'est saisir un sous-ensemble d'un tableau, et vous connaissez déjà les positions des éléments que vous voulez vous pouvez simplement utiliser INDEX avec un tableau comme argument d'index. En d'autres termes, il s'agit d'un tableau :

=INDEX({11,22,33,44,55},{2,3,5})

retours {22,33,55} . Mais ce n'est généralement pas très utile parce que vous ne connaissez pas les positions, et je ne connais aucun moyen de les obtenir sans UDF.

Ce que j'ai fait pour ce type de filtrage de tableau dans la feuille de calcul est d'écrire un UDF sous la forme suivante :

'Filters an input sequence based on a second "comb" sequence.
'Non-False-equivalent, non-error values in the comb represent the positions of elements
'to be kept.
Public Function combSeq(seqToComb, seqOfCombValues)

    'various library calls to work with 1xn or nx1 arrays or ranges as well as 1-D arrays

    'iterate the "comb" and collect positions of keeper elements

    'create a new array of the right length and copy in the keeper elements

End Function

Je n'ai affiché que du pseudocode parce que mon code réel est constitué d'appels à des fonctions de la bibliothèque, y compris les opérations "collect-positions" et "copy-from-positions". Cela obscurcirait probablement l'idée de base, qui est assez simple.

Vous appelleriez un tel UDF de la manière suivante :

=combSeq({23, "", 34, 46, "", "16"}, {23, "", 34, 46, "", "16"} <> "")

ou

=combSeq(Q1:Q42, SIN(Z1:Z42) > 0.5)

et utiliser la mécanique normale des tableaux d'Excel pour générer le "peigne". Il s'agit d'une méthode légère, adaptée à Excel, qui permet d'obtenir un grand nombre d'avantages de la méthode plus standard du filter(list-to-filter, test-function) que l'on peut trouver dans d'autres systèmes de programmation.

J'utilise le nom "peigne" parce que "filtre" signifie généralement "filtrer avec cette fonction", et avec Excel, vous devez appliquer la fonction de test avant d'appeler la fonction de filtrage. De plus, il peut être utile de calculer un "peigne" comme résultat intermédiaire et de l'utiliser ensuite pour... euh, peigner... plusieurs listes.

Answer 2

La réponse se trouve sur ce site : http://www.mrexcel.com/forum/showthread.php?t=112002 . Peu d'explications cependant.

Supposons que vous ayez des données avec des cellules vides dans la colonne A et que vous mettiez ceci dans la colonne B ; cela permettra de récupérer les données dans le même ordre, en sautant les cellules vides.

=INDEX(  $A$1:$A$6, 
         SMALL(  
            IF(
               ($A$2:$A$6<>""), 
               ROW($A$2:$A$6)
            ), 
         ROW()-ROW($B$1)
         )
      )

Voici l'explication :

• ROW()-ROW($B$1) est juste une astuce qui vous donnera un nombre incrémentiel (c'est-à-dire 1 dans B1, 2 dans B2...)
• IF (... , ROW($A$2:$A$6) ) est la partie principale de l'astuce : elle construit un tableau des numéros de ligne où la condition IF est vraie (notez que IF n'a pas de valeur "else")
• PETIT(..) renvoie la Xe plus petite valeur de ce tableau (dans notre cas, le numéro de la Xe ligne non vierge), où X est le numéro de ligne de la cellule actuelle (1 dans B1 ...).
• INDEX passera alors du numéro de ligne à sa valeur
• Il convient de noter que INDEX y ROW commencer une ligne au-dessus de la table actuelle pour toujours avoir un décalage > 0 (INDEX n'aime pas les zéros)

Answer 3

Les réponses ci-dessus donnent toutes des formules fragiles qui ne peuvent pas être déplacées à différents endroits de la feuille et qui sont très sensibles aux lignes et colonnes insérées.

Voici une version qui n'est pas sensible et qui peut être déplacée sur n'importe quelle ligne :

=INDEX($A$10:$A$40, SMALL(IF(B$10:B$40,ROW(INDIRECT("1:30"))),ROW(INDIRECT("1:30"))))

Dans cet exemple, les valeurs du tableau original sont placées dans $A$10:$A$40 (peut-être en utilisant la formule de tableau {TRANSPOSE(originalArray)} si les données originales étaient une ligne au lieu d'une colonne).

Les colonnes B$10:B$40 contiennent des indicateurs booléens (VRAI ou FAUX) qui déterminent si cet élément de tableau doit être conservé dans le résultat (VRAI) ou non (FAUX). Vous pouvez remplir cette colonne à l'aide de la fonction de votre choix. Pour créer le test mentionné dans l'OP, <>"", B$10 doit être rempli avec : =A10<>"" (et ensuite copié jusqu'à B$40). La colonne A a des références absolues et la colonne B des références relatives, de sorte que la formule peut être copiée dans les colonnes situées plus à droite, ce qui vous permet de créer d'autres types d'attributs et de sous-réseaux, qui seront régis par des tests booléens que vous placerez dans les colonnes C et D, etc.

Cet exemple traitera un tableau original comportant jusqu'à 30 éléments. Pour un tableau plus grand, ajustez les plages $A$10:$A$40 et B$10:B$40 (qui représentent 30 lignes) ainsi que les deux occurrences de "1:30".

Answer 4

Une solution possible pour la fonction de la feuille de travail :

=INDEX(A1:A6,N(IF(1,MODE.MULT(IF(A1:A6<>"",ROW(1:6)*{1,1})))))

Les MODE.MULT renvoie un tableau réduit d'indices et de N(IF(1,.)) est inséré de façon à ce que le tableau soit transmis par référence à la INDEX fonction.