Concatenating verdier fra en variabel Antall Celler

Pam har to kolonner med data. I kolonne A er det enkle identifikatorer, slik som A, B, C, etc. I kolonne B er det en rekke heltallsverdier. Hun kan sortere dataene etter identifikatoren og sekundært ved heltallsverdiene. Nå vil hun, i kolonne C, for å ha en formel som vil slå sammen alle heltallsverdiene for en bestemt identifikator. Dermed, hvis A1: A4 alle inneholder identifikatoren A, deretter i celle C1 hun ønsker å ha alle verdiene i B1: B4 sammensatt og deles med komma, for eksempel "11, 17, 19, 25". Siden antallet rader for hver identifikator kan være annerledes, er Pam ikke sikker på hvordan du skal gå om sammensetning.

Den enkleste måten å oppnå dette på er å bruke en makro, som kan opprettes som en brukerdefinert funksjon. Her er et eksempel:

Funksjon CatSame (c Som Range) As String
Application.Volatile
STEMP = ""
iCurCol = c.Column
Hvis iCurCol = 3 Da
Hvis c.Row = 1 Da
sLast = ""
Else
sLast = c.Offset (-1, -2)
End If
Hvis c.Offset (0, -2) <> sLast Deretter
J = 0
Gjøre
STEMP = STEMP & "," & c.Offset (J, -1)
J = J + 1
Loop Mens c.Offset (J, -2) = c.Offset (J - 1, -2)
STEMP = Høyre (STEMP, Len (STEMP) - 2)
End If
End If
CatSame = STEMP
End Function

Denne funksjonen tar utgangspunktet en verdi som er gått til det (en cellereferanse) og bekrefter at cellereferanse er for kolonne C. Hvis det er, så det begynner å sette sammen verdier fra kolonne B basert på verdiene i kolonne A. Det eneste returnerer strengen av sammenkjedede verdier dersom verdien er kolonnen A er forskjellig fra verdien i raden over den. Antar dine identifikatorer er i kolonne A og dine verdier som skal sammenkjedet er i kolonne B, kan du plassere følgende i kolonne C:

= CatSame (C1)

Kopiere dette ned så langt som nødvendig i kolonne C, og du ender opp med nøyaktig hva Pam ønsket.

En mer allsidig funksjon ville være en som ville fungere litt som VLOOKUP, men bringe tilbake en sammensatt liste over verdier som passer uansett hva du leter opp. Vurdere følgende funksjon:

Funksjon VLookupAll (vValue, rngAll Som Range, _
iCol As Integer, Valgfritt sSep As String = ",")
Dim Rceii Som Range
Dim RNG Som Range
On Error GoTo ErrHandler

Application.Volatile
Satt RNG = Skjæring (rngAll, rngAll.Columns (1))
For Hver Rceii I RNG
Hvis rCell.Value = vValue Deretter _
VLookupAll = VLookupAll & sSep & _
rCell.Offset (0, iCol) .Value
Neste Rceii

Hvis VLookupAll = "" Then
VLookupAll = CVErr (xlErrNA)
Else
VLookupAll = Høyre (VLookupAll, Len (VLookupAll) - Len (sSep))
End If
ErrHandler:
Hvis err.number <> 0 Then VLookupAll = CVErr (xlErrValue)
End Function

Denne funksjonen tar opp til fire argumenter. Den første er den verdien du ønsker å matche i din oppslag. I Pams eksempel, ville dette være identifikatoren du ønsker, for eksempel A, B eller C. Det andre argumentet er celleområdet som å lete etter de kampene (kolonne A i dette tilfellet). Det tredje argumentet er en offset (fra serien i det andre argumentet) som representerer de verdiene du ønsker sammensatt. Du kan bruke funksjonen på denne måten:

= VLookupAll ("B", A1: A99,1)

Hvis du ønsker å spesifisere en annen skilletegn mellom verdier, kan du gjøre det ved hjelp av valgfrie fjerde argument. Vil for eksempel gi følgende en streng hvor en dash skiller hver verdi:

= VLookupAll ("B", A1: A99,1, "-")

Løsningene så langt har fokusert på bruk av makroer. Grunnen til dette er relativt enkelt: Det er ikke en formel-basert løsning som kan gjøre hva Pam trenger. Ved hjelp av nestede IF-setninger for å vurdere hva som er i kolonne A vil ikke fungere godt fordi du er begrenset i hvor dypt IF uttalelser kan nestes.

Du kan bruke en formel og et mellomresultat hvis du ikke har noe imot å ha de sammenkjedede verdiene være i siste instans av en identifikator i kolonne A. Start med å sette denne formelen i celle C1:

= B1

Denne formelen bør gå inn i celle C2:

= IF (A2 = A1, C1 & "," & B2, B2)

Kopier denne formelen nedover så mange rader som er nødvendig. Hva du ender opp med er en stadig lengre rekke sammenkjedede verdiene i kolonne C, med den lengste i hvert løp blir på samme rad som den siste sekvensiell identifikator i kolonne A. Du kan deretter sette følgende i alle de aktuelle cellene i kolonne D:

= HVIS (LEN (K2)> LEN (C1), "", C1)

Denne formelen viser bare de lengste strenger fra kolonne C, som er det Pam nødvendig å begynne med.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (9197) gjelder for Microsoft Excel 97, 2000, 2002, og 2003. Du kan finne en versjon av dette tipset for Båndet av Excel (Excel 2007 og senere) her: concatenating verdier fra en variabel antall celler.