Excel: Création d'sets ou de strings de données dans une seule cellule

J'ai rencontré quelques solutions, mais je me demandais si ce n'est que la limitation d'Excel ou qu'il n'y a aucun moyen de créer de multiples valeurs à évaluer à partir d'une même cellule. C3 crée la moyenne de toutes les valeurs dans la cellule voisine.

Je vais vous expliquer pourquoi je voulais le faire. Je travaille sur une speadsheet pour les prix des articles dans un jeu et je veux créer une pseudo database sur l'une des cellules de la colonne. Un exemple de ce que je pensais le faire simplement dans une seule cellule:

2, 40, 300, 200, 340

Il n'y a pas de C2 (nom de la cellule) de toutes les inputs divisées par le nombre total d'inputs? Une moyenne de toutes les valeurs séparées par des virgules?

Je sais que cela se fait facilement avec plusieurs cellules, mais je voulais réduire l'encombrement horizontal, alors je me demandais s'il y avait une façon de séparer les valeurs avec la syntaxe, sans utiliser de code VBA ou trop compliqué.

la formule est un ours mais:

 =SUMPRODUCT(--MID(SUBSTITUTE(C2,",",REPT(" ",99)),(ROW(INDIRECT("1:" & LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1))-1)*99+1,99))/(LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1)

entrez la description de l'image ici

Il y a beaucoup de choses ici, alors divisez-le:

LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1 : obtient le nombre d'éléments séparés par LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1 dans ce cas 5 . Il est utilisé deux fois dans cette formule. Le deuxième est le dénominateur pour get la moyenne.

Le deuxième est utilisé dans le (ROW(INDIRECT("1:" & LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1))-1) . Voici la partie qui se déroule. SUMPRODUCT Provoque des itérations et résume les pièces.

La ligne returnne 1,2,3, … à ce qui est returnné par la fonction LEN (…) – LEN (…). Nous en soustrayons un pour commencer à 0.

Chaque itération est multipliée par 99 et nous ajoutons une parce que MID n'aime pas 0 comme début.

Le SUBSTITUTE(C2,",",REPT(" ",99)) crée une string qui a 99 espaces au lieu de chaque ",".

Le MID prend cette string de nombre séparée par maintenant 100 espaces et l'parsing. Le deuxième critère est itéré à partir de 1.100.199.298, … et ainsi de suite le nombre de nombres dans les time cellulaires. Ce qui tombera toujours sur un espace, puisque nous lui avons donné 100 espaces pour atterrir.

Le troisième critère définit la longueur de la string à revenir sur chaque itération. Nous utilisons 99 car, à nouveau, il atteindra toujours un espace après le numéro.

Le -- en face du MID entraînera le return de la string parsed returnner à un nombre. Cela oblige Excel à supprimer les espaces supplémentaires et à voir seulement le nombre.

C'est ce nombre que SUMPRODUCT () maintient et ajoute au total de chaque itération.

Vous pouvez également écrire une fonction VBA pour votre problème:

Exemple:

entrez la description de l'image ici

Code à coller avant que la fonction puisse être utilisée:

 Public Function AverageCell(allItems As Ssortingng) As Variant Dim itemArray() As Ssortingng Dim totalsum As Variant Dim totalav As Long totalsum = 0 totalav = 0 itemArray() = Split(allItems, ",") For Each num In itemArray() totalsum = totalsum + num totalav = totalav + 1 Next num AverageCell = totalsum / totalav End Function