J'ai un vidage de données dans Excel qui consiste en des données annuelles de clients pour deux valeurs différentes. Les données ont été fournies avec une rangée distincte pour la valeur pour chaque année et le client. C'est comme ça:
Je suis coincé avec une ligne pour le client 1 pour la valeur A en 2009 et une rangée séparée pour la valeur B pour le même client la même année.
Dans certains cas, il n'y a pas de valeur A ou de valeur B. Dans l'exemple ci-dessus, vous pouvez voir que le client 1 n'a pas de valeur B en 2011, donc aucune ligne n'a été générée pour cela. Et, bien que non représenté dans l'exemple, certains clients n'auront aucune donnée pour la valeur en une année (et donc aucune ligne pour ce client cette année). Dans cette situation, il manque une ligne pour ce client cette année.
Je souhaite que cela se fasse dans une feuille de travail où il y a une rangée pour les deux valeurs pour chaque année et le client. C'est-à-dire, je veux que les données ressemblent à ceci:
Quel est le moyen le plus efficace de créer ce résultat?
C'est VBA, ou une macro que vous pouvez exécuter sur votre feuille. Vous devez toucher alt + F11 pour afficher l'invite Visual Basic for Application, aller dans votre classeur et right click - insert - module
et coller ce code là-bas. Vous pouvez ensuite exécuter le module à partir de VBA en appuyant sur F5 . Cette macro s'appelle "test"
Sub test() 'define variables Dim RowNum as long, LastRow As long 'turn off screen updating Application.ScreenUpdating = False 'start below titles and make full selection of data RowNum = 2 LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row Range("A2", Cells(LastRow, 4)).Select 'For loop for all rows in selection with cells For Each Row In Selection With Cells 'if customer name matches If Cells(RowNum, 1) = Cells(RowNum + 1, 1) Then 'and if customer year matches If Cells(RowNum, 4) = Cells(RowNum + 1, 4) Then 'move attribute 2 up next to attribute 1 and delete empty line Cells(RowNum + 1, 3).Copy Destination:=Cells(RowNum, 3) Rows(RowNum + 1).EntireRow.Delete End If End If End With 'increase rownum for next test RowNum = RowNum + 1 Next Row 'turn on screen updating Application.ScreenUpdating = True End Sub
Cela se déroulera dans une feuille de calcul triée et combinera des lignes consécutives qui correspondent à la fois au client et à l'année et supprimeront la ligne maintenant vide. La feuille de calcul doit être triée comme vous l'avez présenté, les clients et les années en hausse, cette macro particulière ne regardera pas au-delà des lignes consécutives .
Editer – c'est tout à fait possible que ma with statement
soit complètement inutile, mais ça ne fait pas mal à personne.
Quelqu'un a utilisé cette réponse dans une autre question et quand je suis retourné, je pensais que ce VBA était pauvre. Je l'ai redéfini –
Sub CombineRowsRevisited() Dim c As Range Dim i As Integer For Each c In Range("A2", Cells(Cells.SpecialCells(xlCellTypeLastCell).Row, 1)) If c = c.Offset(1) And c.Offset(,4) = c.Offset(1,4) Then c.Offset(,3) = c.Offset(1,3) c.Offset(1).EntireRow.Delete End If Next End Sub
Demandé à nouveau Comment combiner les valeurs de plusieurs lignes en une seule ligne? Ayez un module, mais avez besoin des variables expliquant et encore, c'est plutôt médiocre.
Sub CombineRowsRevisitedAgain() Dim myCell As Range Dim lastRow As Long lastRow = Cells(Rows.Count, "A").End(xlUp).Row For Each myCell In Range(Cells("A2"), Cells(lastRow, 1)) If (myCell = myCell.Offset(1)) And (myCell.Offset(0, 4) = myCell.Offset(1, 4)) Then myCell.Offset(0, 3) = myCell.Offset(1, 3) myCell.Offset(1).EntireRow.Delete End If Next End Sub
Cependant, en fonction du problème, il est préférable de step -1
sur un numéro de ligne, de sorte que rien ne soit ignoré.
Sub CombineRowsRevisitedStep() Dim currentRow As Long Dim lastRow As Long lastRow = Cells(Rows.Count, 1).End(xlUp).Row For currentRow = lastRow To 2 Step -1 If Cells(currentRow, 1) = Cells(currentRow - 1, 1) And _ Cells(currentRow, 4) = Cells(currentRow - 1, 4) Then Cells(currentRow - 1, 3) = Cells(currentRow, 3) Rows(currentRow).EntireRow.Delete End If Next End Sub
Une autre option:
Special
-> Blanks
-> OK
=
appuyez sur ↑ , puis sur CTRL + ENTER Special
-> Blanks
-> OK
=IF(
appuyez sur ← type =
appuyez sur ← appuyez sur ↓ ,
appuyez sur ↓ ,0)
puis CTRL + ENTER Copy
et Paste Special as Values
MODIFIER:
Explication: J'essaie d'utiliser l'option GoTo Blanks . Une fois que vous avez sélectionné des blancs, vous pouvez entrer la même formule pour toutes les cellules vierges sélectionnées. En ce qui concerne la question de OP, les données semblent avoir des blancs cohérents , c'est-à-dire: Value A
espaces de colonne de la Value A
ont le même ID de CustomerID
que la ligne non blanche ci-dessus. De la même manière, les flancs de colonne de la Value B
ont le même ID de CustomerID
que la ligne non blanche ci-dessous.
Le moyen le plus efficace de procéder est de transférer toutes les données dans une table pivot et déposer «Client» dans les étiquettes de ligne et ensuite suivre les autres colonnes. Vous pouvez supprimer l'année dans l'en-tête de colonne si vous souhaitez voir la ventilation par année
Les tables pivotantes peuvent être trouvées sous Insérer dans Excel 2010
Voici des étapes pour créer une table séparée avec les données condensées.
Customer
et Year
. Cela fournira le cadre de la table condensée. Dans B2 (la première entrée pour la Value A
), entrez ce qui suit:
=IFERROR(INDEX(Sheet1!B$1:B$11,MIN(IF(Sheet1!$A$1:$A$11=$A2,IF(Sheet1!$D$1:$D$11=$D2,IF(Sheet1!B$1:B$11<>"",ROW(Sheet1!$A$1:$A$11),1000000),1000000),1000000))),"")
Entrez la formule comme une formule de tableau en appuyant sur Ctrl + Maj + Entrée .
Remplissez la formule dans la colonne. Ensuite, remplissez pour remplir également la colonne Value B
Voila!
Quelques notes:
1000000
s dans la formule à quelque chose de plus grand que votre nombre de lignes. Tout le monde utilise beaucoup de code VBA ou des fonctions compliquées pour cela. J'ai une méthode qui prend une seconde à mettre en œuvre mais est beaucoup plus compréhensible et très facile à ajuster en fonction de diverses autres possibilités.
Dans l'exemple que vous avez donné ci-dessus, collez ces (4) fonctions dans les cellules, E2, F2, G2 et H2, respectivement (les cellules de référence des fonctions F & G ci-dessus):
=IF(D2=D3, A2, IF(D2<>D1, A2, "")) =IF(D2=D3, MAX(B2:B3), IF(D2<>D1, B2, "")) =IF(D2=D3, MAX(B2:B3), IF(D2<>D1, IF(C2=0,"",C2),"")) =IF(D2=D3, D2, IF(D2<>D1, D2, ""))
Faites glisser ces formules aussi loin que nécessaire. Il génère une seule rangée de données chaque fois que 2 lignes sont présentes, laissant les lignes simples ne sont pas affectées. Collez spécial les valeurs (pour supprimer les formules) des colonnes EFGH ailleurs et trier par client pour supprimer toutes les lignes supplémentaires.