Dans MS Excel, j'essaie de fournir une liste d'en-têtes dans une cellule en fonction de si un en-tête a une valeur (supérieure à 0). J'espère également trier la liste de cette cellule par la plus grande en premier. Comment puis-je faire cela?
Je n'ai pas du tout appris Visual Basic et n'ai vraiment essayé d'utiliser que des instructions if. J'ai une très grande table à trier et je ne veux pas le faire à la main.
Ce que j'essaye d'obtenir:
--------- Header1 Header2 Header3 List
Apple Orange Pear
Row 1 - Ryan 0 0 0 --
Row 2 - Cindy 0 5 7 Pears, Oranges
Row 3 - Tom N/A
Row 4 - Jess 3 6 2 Oranges, Apples, Pears
Merci!
3 Réponses :
Bienvenue dans Stack Overflow!
Essayez de consulter TEXTJOIN < / a> pour joindre des cellules dans une liste. Consultez également ce lien sur concaténant conditionnellement une plage < / a> pour un bon exemple d'implémentation. Bonne chance!
Si vous avez Office 365 Excel, vous utiliseriez cette formule de tableau:
=MID(IF(B3>0,","&$B$1,"")&IF(C3>0,","&$C$1,"")&IF(D3>0,","&$D$1,""),2,999)
Étant une formule de tableau, il faut la confirmer avec Ctrl-Maj-Entrée au lieu de Entrée lors de la sortie du mode d'édition.
/ p>
Il construit un tableau des en-têtes dans l'ordre et le renvoie à la fonction TEXTJOIN. Une note cependant, s'il y a une égalité dans les valeurs, il renverra le même en-tête pour les deux. Par exemple, 6,6,6 renverra Apple,Apple,Apple.
p>
Si vous n'avez pas Office 365 Excel, mettez ce code dans un module attaché à la feuille de calcul et utilisez la formule comme indiqué ci-dessus:
Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
Dim d As Long
Dim c As Long
Dim arr2()
Dim t As Long, y As Long
t = -1
y = -1
If TypeName(arr) = "Range" Then
arr2 = arr.Value
Else
arr2 = arr
End If
On Error Resume Next
t = UBound(arr2, 2)
y = UBound(arr2, 1)
On Error GoTo 0
If t >= 0 And y >= 0 Then
For c = LBound(arr2, 1) To UBound(arr2, 1)
For d = LBound(arr2, 1) To UBound(arr2, 2)
If arr2(c, d) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
End If
Next d
Next c
Else
For c = LBound(arr2) To UBound(arr2)
If arr2(c) <> "" Or Not skipblank Then
TEXTJOIN = TEXTJOIN & arr2(c) & delim
End If
Next c
End If
TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function
Cela peut aussi être fait en créant une piqûre un mot à la fois:
=IFERROR(TEXTJOIN(",",TRUE,INDEX($B$1:$D$1,N(IF({1},MATCH(AGGREGATE(14,7,$B3:$D3/($B3:$D3>0),ROW($XFD$1:INDEX($XFD:$XFD,COUNTIF($B3:$D3,">0")))),$B3:$D3,0))))),IF(COUNT(B3:D3)>1,"---","N/A"))
Mais cela devient vraiment indiscipliné s'il y a beaucoup de colonnes:
Je ne renvoie pas de liste avec la formule matricielle Office 365. Je ne reçois que le plus gros élément de la liste. Comment avez-vous réussi à faire cela? Grande variété de réponses d'ailleurs!
Avez-vous utilisé Ctrl-Maj-Entrée lorsque vous quittez le mode d'édition? Quand vous avez fait cela, Excel a-t-il placé {} autour de la formule?
Ahh, merci !!
Vous pouvez le faire avec les anciennes versions d'Excel, mais la formule devient un peu compliquée afin d'afficher les virgules appropriées au bon endroit. J'ai séparé en 2 formules pour le rendre plus clair:
La première formule consiste à rechercher et écrire les en-têtes (situés dans la colonne E) :
=+IF(RIGHT(IF(COUNTIF(B6:D6,">"&0)>0,CONCATENATE(IF(B6>0,B$4&",",""),IF(C6>0,C$4&",",""),IF(D6>0,D$4,"")),""),1)=",",LEFT(IF(COUNTIF(B6:D6,">"&0)>0,CONCATENATE(IF(B6>0,B$4&",",""),IF(C6>0,C$4&",",""),IF(D6>0,D$4,"")),""),LEN(IF(COUNTIF(B6:D6,">"&0)>0,CONCATENATE(IF(B6>0,B$4&",",""),IF(C6>0,C$4&",",""),IF(D6>0,D$4,"")),""))-1),IF(COUNTIF(B6:D6,">"&0)>0,CONCATENATE(IF(B6>0,B$4&",",""),IF(C6>0,C$4&",",""),IF(D6>0,D$4,"")),""))
La seconde consiste à nettoyer les virgules et fournit le résultat final (situé dans la colonne F):
=+IF(RIGHT(E6,1)=",",LEFT(E6,LEN(E6)-1),E6)
Vous pouvez fusionner les deux en un mais il est alors difficile de clarifier ce que fait la formule:
=+IF(COUNTIF(B6:D6,">"&0)>0,CONCATENATE(IF(B6>0,B$4&",",""),IF(C6>0,C$4&",",""),IF(D6>0,D$4,"")),"")
deux choses, cela devient assez gros s'il y a plus de 3 colonnes et deuxièmement, l'OP a demandé d'avoir les en-têtes dans l'ordre de la valeur et non dans l'ordre de l'en-tête. Juste un FYI, si l'OP n'a que 3-4 articles et ne se soucie pas de la commande, cela fonctionnera très bien.
Je suis tout à fait d'accord, les mettre en ordre ou ajouter plus de colonnes serait insupportable dans une seule formule.
Aussi si je devais le faire avec une formule de chaîne plus ancienne: = MID (IF (B5> 0, "," & $ B $ 4, "") & IF (C5> 0, "," & $ C $ 4, " ") & IF (D5> 0,", "& $ D $ 4," "), 2 999) Voir ma réponse modifiée ci-dessous
Uniquement possible avec une formule si vous avez Office 365 Excel ou version ultérieure.
@Scott Craner: Vous pouvez également faire cela avec une formule avec Excel classique, voir la réponse ci-dessous.
@Kaiser il ne peut que voir mon commentaire sur votre réponse.