Az ismétlődő sorok kombinálása és az értékek összegzése Excelben (egyszerű trükkök)
Az Excelben gyakran előfordul, hogy ismétlődő bejegyzéseket tartalmazó adatkészlettel találkozik. Gyakran előfordulhat, hogy számos adattal találkozhat, ahol a fő kihívás az, hogy hatékonyan kombinálja ezeket az ismétlődő sorokat, miközben egyidejűleg összegzi az értékeket egy megfelelő oszlopban, a következő képernyőképen látható módon. Ebben az összefüggésben számos olyan gyakorlati módszert vizsgálunk meg, amelyek segíthetnek az ismétlődő adatok konszolidálásában és a hozzájuk tartozó értékek összesítésében, javítva az Excel-munkafüzetek átláthatóságát és hasznosságát.
Kombinálja a duplikált sorokat, és összegezze az értékeket
Kombinálja az ismétlődő sorokat, és összegezze az értékeket a Consolidate függvénnyel
A Consolidate egy hasznos eszköz számunkra több munkalap vagy sor összevonására Excelben, ezzel a funkcióval a duplikált sorokat kombinálhatjuk, és a hozzájuk tartozó értékeket gyorsan és egyszerűen összegezhetjük. Kérjük, kövesse a következő lépéseket:
1. lépés: Válasszon ki egy célcellát
Válassza ki, hol jelenjenek meg az összevont adatok.
2. lépés: Nyissa meg a Konszolidáció funkciót, és állítsa be a konszolidációt
- Kattints dátum > Összesítés, lásd a képernyőképet:
- A Összesítés párbeszédablak:
- (1.) Válassza ki Összeg ból ből Funkció legördülő lista;
- (2.) Kattintson a ikonra a konszolidálni kívánt tartomány kiválasztásához Referencia doboz;
- (3.) Ellenőrizze Felső sor és a Bal oszlop ból ből Használjon címkéket itt választási lehetőség;
- (4.) Végül kattintson OK gombot.
Eredmény:
Az Excel egyesíti az első oszlopban talált duplikációkat, és összeadja a megfelelő értékeket a szomszédos oszlopokban az alábbi képernyőkép szerint:
- Ha a tartomány nem tartalmaz fejlécsort, győződjön meg róla törölje a Felső sor jelölését tól Használjon címkéket itt opciót.
- Ezzel a funkcióval a számításokat csak az adatok első oszlopa (a bal szélső) alapján lehet konszolidálni.
Kombinálja az ismétlődő sorokat, és összegezze az értékeket egy hatékony funkcióval – a Kutools-szal
Ha telepítve van Kutools az Excel számára, annak Haladó kombinált sorok A funkció lehetővé teszi az ismétlődő sorok egyszerű kombinálását, lehetőséget biztosítva az adatok összegzésére, számolására, átlagolására vagy egyéb számítások végrehajtására. Sőt, ez a funkció nem korlátozódik egyetlen kulcsoszlopra, több kulcsoszlopot is képes kezelni, így sokkal könnyebbé válik az összetett adatkonszolidációs feladatok.
Telepítése után Kutools az Excel számára, válassza ki az adattartományt, majd kattintson a gombra Kutools > Egyesítés és felosztás > Haladó kombinált sorok.
A Haladó kombinált sorok párbeszédpanelen állítsa be a következő műveleteket:
- Kattintson annak az oszlopnak a nevére, amely alapján az ismétlődéseket egyesíteni szeretné, itt a Termékre kattintok, majd kiválasztom Elsődleges kulcs a. legördülő listából Művelet oszlop;
- Ezután válassza ki annak az oszlopnak a nevét, amelyben az értékeket összegezni szeretné, majd válassza ki Összeg a. legördülő listából Művelet oszlop;
- Ami a többi oszlopot illeti, kiválaszthatja a szükséges műveletet, például egyesítheti az értékeket egy adott elválasztóval vagy végrehajthat egy bizonyos számítást; (ez a lépés figyelmen kívül hagyható, ha csak két oszlopa van)
- Végül megtekintheti a kombinált eredmény előnézetét, majd kattintson OK gombot.
Eredmény:
Most a kulcsoszlopban lévő ismétlődő értékeket egyesítjük, és a többi megfelelő értékeket a következő képernyőkép szerint összegezzük:
- Ezzel a hasznos funkcióval a sorokat a duplikált cellaérték alapján is kombinálhatja, amint az alábbi bemutatón látható:
- Ez a funkció támogatja a visszavonást, ha vissza szeretné állítani az eredeti adatait, csak nyomja meg a gombot Ctrl + Z.
- A funkció alkalmazásához kérjük töltse le és telepítse a Kutools for Excel alkalmazást először.
Kombinálja az ismétlődő sorokat, és összegezze az értékeket a kimutatástáblázattal
Az Excel kimutatástáblája dinamikus módot biztosít az adatok átrendezésére, csoportosítására és összegzésére. Ez a funkció hihetetlenül hasznossá válik, ha duplikált bejegyzésekkel teli adatkészlettel kell szembenéznie, és a megfelelő értékeket össze kell adnia.
1. lépés: Pivot tábla létrehozása
- Válassza ki az adattartományt. És akkor menj a betétlap lapot, és kattintson a gombra Pivot Table, lásd a képernyőképet:
- A felugró párbeszédpanelen válassza ki, hová szeretné elhelyezni a kimutatástáblázat jelentését, és szükség szerint elhelyezheti új vagy meglévő lapra. Ezután kattintson OK. Lásd a képernyőképet:
- Most egy kimutatástábla kerül beszúrásra a kiválasztott célcellába. Lásd a képernyőképet:
2. lépés: A kimutatás konfigurálása:
- A PivotTable-mezők panelen húzza a duplikációkat tartalmazó mezőt a Sor terület. Ez csoportosítja az ismétlődéseket.
- Ezután húzza az összegezni kívánt értékeket tartalmazó mezőket a következőre Értékek terület. Alapértelmezés szerint az Excel összegzi az értékeket. Tekintse meg az alábbi bemutatót:
Eredmény:
A Pivot Table mostantól duplikátumokkal kombinálva jeleníti meg az adatokat, és összegzi az értékeket, így világos és tömör nézetet kínál az elemzéshez. Lásd a képernyőképet:
Kombinálja a duplikált sorokat, és összegezze az értékeket a VBA kóddal
Ha érdekli a VBA-kód, ebben a szakaszban egy VBA-kódot adunk meg az ismétlődő sorok konszolidálásához és a megfelelő értékek összegzéséhez más oszlopokban. Kérjük, kövesse a következő lépéseket:
1. lépés: Nyissa meg a VBA-lapmodul-szerkesztőt, és másolja ki a kódot
- Tartsa lenyomva a ALT + F11 kulcsokat az Excel programban a Microsoft Visual Basic for Applications ablak.
- Kattints betétlap > Modulok, és illessze be a következő kódot a Modulok Ablak.
VBA kód: Kombinálja az ismétlődő sorokat, és összegezze az értékeketSub CombineDuplicateRowsAndSumForMultipleColumns() 'Update by Extendoffice Dim SourceRange As Range, OutputRange As Range Dim Dict As Object Dim DataArray As Variant Dim i As Long, j As Long Dim Key As Variant Dim ColCount As Long Dim SumArray() As Variant Dim xArr As Variant Set SourceRange = Application.InputBox("Select the original range:", "Kutools for Excel", Type:=8) If SourceRange Is Nothing Then Exit Sub ColCount = SourceRange.Columns.Count Set OutputRange = Application.InputBox("Select a cell for output:", "Kutools for Excel", Type:=8) If OutputRange Is Nothing Then Exit Sub Set Dict = CreateObject("Scripting.Dictionary") DataArray = SourceRange.Value For i = 1 To UBound(DataArray, 1) Key = DataArray(i, 1) If Not Dict.Exists(Key) Then ReDim SumArray(1 To ColCount - 1) For j = 2 To ColCount SumArray(j - 1) = DataArray(i, j) Next j Dict.Add Key, SumArray Else xArr = Dict(Key) For j = 2 To ColCount xArr(j - 1) = xArr(j - 1) + DataArray(i, j) Next j Dict(Key) = xArr End If Next i OutputRange.Resize(Dict.Count, ColCount).ClearContents i = 1 For Each Key In Dict.Keys OutputRange.Cells(i, 1).Value = Key For j = 1 To ColCount - 1 OutputRange.Cells(i, j + 1).Value = Dict(Key)(j) Next j i = i + 1 Next Key Set Dict = Nothing Set SourceRange = Nothing Set OutputRange = Nothing End Sub
2. lépés: Hajtsa végre a kódot
- A kód beillesztése után nyomja meg a gombot F5 kulcs a kód futtatásához. A prompt mezőben válassza ki az egyesíteni kívánt adattartományt, és összegezze. És akkor kattintson OK.
- A következő prompt mezőben válasszon ki egy cellát, ahol kiírja az eredményt, és kattintson rá OK.
Eredmény:
Most az ismétlődő sorokat egyesítettük, és a megfelelő értékeket összegeztük. Lásd a képernyőképet:
Az ismétlődő sorok kombinálása és összegzése az Excelben egyszerű és hatékony lehet. Válasszon az egyszerű Consolidate funkció, a fejlett Kutools, az analitikus Pivot Tables vagy a rugalmas VBA kódolás közül, hogy megtalálja a képességeinek és igényeinek megfelelő megoldást. Ha további Excel-tippeket és trükköket szeretne felfedezni, webhelyünk több ezer oktatóanyagot kínál, kérjük, ide kattintva érheti el őket. Köszönjük, hogy elolvasta, és a jövőben is további hasznos információkkal szolgálunk!
Kapcsolódó cikkek:
- Több sor összevonása egybe az ismétlődések alapján
- Lehetséges, hogy van egy adattartománya, a Terméknév A oszlopban néhány ismétlődő tétel található, és most el kell távolítania az ismétlődő bejegyzéseket az A oszlopban, de össze kell kapcsolnia a megfelelő értékeket a B oszlopban. Hogyan lehetne ezt a feladatot Excelben megoldani. ?
- Vlookup és több értéket ad vissza duplikációk nélkül
- Előfordulhat, hogy érdemes vlookup-ot keresnie, és egyszerre több egyező értéket visszaadni egyetlen cellába. De ha néhány ismételt érték van feltöltve a visszaküldött cellákban, hogyan hagyhatja figyelmen kívül a duplikátumokat, és csak akkor őrizheti meg az egyedi értékeket, amikor az összes egyező értéket visszaküldi az Excelben bemutatott képernyőképet követve?
- Kombinálja az azonos azonosítóval/névvel rendelkező sorokat
- Például van egy táblázata, amely az alábbi képernyőképen látható, és kombinálnia kell a sorokat a megrendelés azonosítóival, van ötlete? Ez a cikk két megoldást mutat be az Ön számára.
A legjobb irodai hatékonyságnövelő eszközök
Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz. Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...
Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját
- Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
- Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
- 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!