Comme vous pouvez le voir sur l'image, il y a quelques 1 et 0 réorganisés en 3 lignes et un alphabet anglais pour chaque colonne. Ce que je dois faire est de concaténer les alphabets anglais pour chaque ligne lorsque la valeur de colonne respective est 0. Comment puis-je le faire?
6 Réponses :
Vous pouvez tout faire en une seule formule si vous le souhaitez:
=CONCATENATE(IF($A1=0,'A',''),IF($B1=0,'B',''), ...)
Ou placez les chaînes intermédiaires sur une ligne séparée, puis concaténez-les (pour éviter l'usure de vos doigts).
Allez-vous à ceci dans beaucoup plus de colonnes, ou juste celles que vous avez mentionnées? Tant que le nombre de colonnes est relativement petit, comme dans votre image, vous pouvez concaténer des fonctions IF pour obtenir votre résultat.
Voici ce que j'ai fait:
L'utilisation de cette formule vous donnera un résultat comme celui que vous avez:
En supposant également que vous ayez les valeurs dans une feuille de calcul comme la mienne, collez simplement la formule = IF (B3 = 1, "", B $ 1) & IF (C3 = 1, "", C $ 1) & IF ( D3 = 1, "", D $ 1) & IF (E3 = 1, "", E $ 1) & IF (F3 = 1, "", F $ 1) & IF (G3 = 1, "", G $ 1) & IF (H3 = 1, "", H $ 1) & IF (I3 = 1, "", I $ 1) & IF (J3 = 1, "", J $ 1)
dans B7, puis faites glisser vers B8 et B9 pour obtenir le reste des résultats.
Bien sûr, si vous comptez faire cela pour beaucoup plus de colonnes, il est peut-être préférable d'utiliser VBA.
Voici une solution VBA qui peut gérer n'importe quel nombre de colonnes (en supposant que la lettre associée à chaque colonne est l'étiquette de colonne standard):
Function ZeroColumns(R As Range) As String Dim n As Long Dim count As Long Dim cols As Variant Dim cell As Range n = R.Cells.count ReDim cols(1 To n) For Each cell In R.Cells If cell.Value = 0 Then count = count + 1 cols(count) = Split(cell.Address, "$")(1) End If Next cell ReDim Preserve cols(1 To count) ZeroColumns = Join(cols, "") End Function
Le code ne doit pas être trop dur pour ajuster si l'hypothèse énoncée ne tient pas.
Ici, ajoutez cette fonction à un module. Vous pouvez ensuite l'appeler directement via Excel. Bien joué.
Function conc(ref As Range, Optional Separator As String) As String Dim Cell As Range Dim Result As String For Each Cell In ref If Cell.Value = 0 Then Result = Result & chr(64 + Cell.Column) & Separator End If Next Cell If Separator <> "" Then conc = Left(Result, Len(Result) - 1) Else: conc = Result End Function
SourceRowRange
: la plage contenant les valeurs qui seront
écrit dans CCROW
par ex. A, B, C ...
Obligatoire. CriteriaRowRange
: la plage qui sera vérifiée
CriteriaValue
. Obligatoire. CriteriaValue
: la valeur que les cellules de CriteriaRowRange
être vérifié. La valeur par défaut est 0
. Facultatif. JoinString
: la valeur qui sera placée entre les valeurs qui
être écrit dans CCROW
. La valeur par défaut est ""
. Facultatif. 'Copiez le code suivant dans un module standard ie dans VBE allez dans Insert>Module
.
=CCROW(A$1:I$1,A3:I3,1) ' Result: BCEFHI =CCROW(A$1:I$1,A4:I4,1) ' Result: ABDEGH =CCROW(A$1:I$1,A5:I5,1) ' Result: ABCEFHI
=CCROW(A$1:I$1,A3:I3,,",") ' Result: A,D,G =CCROW(A$1:I$1,A3:I3,0,",") ' Result: A,D,G =CCROW(A$1:I$1,A3:I3,0,", ") ' Result: A, D, G
Si vous ajoutez JoinString
:
=CCROW(A$1:I$1,A3:I3) ' Result: ADG =CCROW(A$1:I$1,A4:I4) ' Result: CFI =CCROW(A$1:I$1,A5:I5) ' Result: DG
SI vous modifiez CriteriaValue
:
Function CCROW(ByVal SourceRowRange As Range, ByVal CriteriaRowRange As Range, _ Optional ByVal CriteriaValue As Variant = 0, _ Optional ByVal JoinString As String) As String Dim vntS As Variant ' Source Array Dim vntC As Variant ' Criteria Array Dim NoC As Long ' Number of Columns Dim j As Long ' Arrays Column Counter Dim strB As String ' String Builder Dim strC As String ' Criteria String ' Calculate number of columns of the narrower Range. NoC = WorksheetFunction.Min(SourceRowRange.Columns.count, _ CriteriaRowRange.Columns.count) ' Copy resized (adjust them to same size) Ranges to Arrays. vntS = SourceRowRange.Resize(1, NoC) vntC = CriteriaRowRange.Resize(1, NoC) ' Loop through columns of either Array. For j = 1 To NoC ' Write current value of Criteria Array to Criteria String. strC = vntC(1, j) ' Check if Criteria String is NOT empty. If strC <> "" Then ' Check if Criteria String is equal to Criteria Value. If strC = CriteriaValue Then ' Check if String Builder is NOT empty. If strB <> "" Then ' NOT empty. strB = strB & JoinString & vntS(1, j) Else ' IS empty (only once). strB = vntS(1, j) End If End If End If Next ' Write String Builder to Conditionally Concatenate Row. CCROW = strB End Function
Remarques
Verrouillez ( $
) la ligne de SourceRowRange
pour qu'elle reste la même lorsque la formule est copiée.
La formule matricielle suivante fera l'affaire (entrez-la avec Ctrl + Maj + Entrée):
=CONCAT(IF($A1:$I1=0,UNICHAR(64+COLUMN($A1:$I1)),""))
Pour les anciennes versions d'Excel, utilisez les fonctions héritées CONCATENATE () code> et
CHAR ()
à la place de ces fonctions.
N.B. Le CONCAT code > fonction
, comme la Fonction TEXTJOIN
mentionnée dans les commentaires , n'est disponible qu'à partir d'Office 365
Quelle version d'Excel? Si vous êtes à jour, vous pouvez utiliser TEXTJOIN () sfmagazine.com/post-entry/...