Bill feltette az e heti kérdést a redundáns Excel adatokról.
Havi tranzakciós listát készítek az Excel programban. A hónap végén meg kell szüntetnem a felesleges adatokat, és számlakóddal kell kitalálnom az összeget. Minden számlakód többször előfordulhat. Ezután Bill leírta jelenlegi Excel módszertanát, amely hasonlít az alábbi 1. módszerhez, hogy egyedi számlakód-listát állítson össze azzal a tervvel, hogy a teljes kereséshez egy mátrix CSE-képletet használ. Megkérdezi, van-e egyszerűbb módja annak, hogy egyedi számlakódokat hozzunk létre az egyes számlák összesítésével?
Ez egy tökéletes ünnepi kérdés. 15 éven át Lotus-felhasználóként felismertem Bill módszerét, mint klasszikus módszert a "gyors és piszkos" adatkezelésre a Lotus 2.1 jó régi napjaiban. Ez egy olyan évszak, hogy megszámoljuk áldásainkat. Ha elgondolkodik ezen a kérdésen, rájön, hogy a Microsoft emberei valóban számos eszközt adtak nekünk az évek során. Ha Excel 97-et használ, akkor legalább öt módszer van erre a feladatra, amelyek mindegyike sokkal könnyebb, mint a Bill által leírt klasszikus módszer. A héten bemutatót kínálok az öt módszerről.
Az egyszerűsített adathalmazom számlaszámai az A oszlopban vannak, és az összegek a B oszlopban vannak. Az adatok A2: B100-ból futnak. Az elején nincs rendezve.
1. módszer
Használja a kreatív If utasításokat a Speciális értékek beillesztésével együtt a válasz megtalálásához.
Tekintettel az Excel által kínált újabb eszközökre, már nem ajánlom ezt a módszert. Sokat használtam ezt, mielőtt jobb dolgok jöttek volna, és még mindig vannak olyan helyzetek, amikor ez hasznosnak bizonyul. Ennek alternatív nevem a "The-Lotus-123-When-You-Were-In-The-The-Mood-To-Use- @ DSUM" módszer. Itt vannak a lépések.
- Rendezze az adatokat az A oszlop szerint.
- Találjon ki egy képletet a C oszlopban, amely számlánként megtartja az összesített futást. A C2 sejt az
=IF(A2=A1,C1+B2,B2). - Találjon ki egy képletet a D-ben, amely azonosítja az adott fiók utolsó bejegyzését. A D2 sejt az
=IF(A2=A3,FALSE,TRUE). - Másolja le a C2: D2 fájlt az összes sorára.
- C2: D100 másolat. Végezzen egy Edit - PasteSpecial - Értékeket vissza a C2: D100-ra, hogy a képleteket értékekké változtassa.
- Rendezés a D oszlop szerint csökkenő módon.
- Azoknál a soroknál, amelyeknél a D oszlopban IGAZ van, egyedi listája van az A számlaszámról, a végső futási összeg pedig C-ben található.
Előnyök: Gyors. Minden amire szükséged van, ha élénk értelemben írsz IF-utasításokat.
Hátrányok: Vannak jobb módszerek.
2. módszer
Használja az Adatszűrő - Speciális szűrőt az egyedi fiókok listájának lekéréséhez.
Bill valóban azt kérdezte, hogyan lehet egyedi számlaszámot létrehozni, hogy a CSE-képletek segítségével meg tudja szerezni az összegeket. Ez egy módszer az egyedi számlaszámok listájának lekérésére.
- Jelölje ki az A1: A100 elemet
- A menüből válassza az Adatok, Szűrő, Speciális szűrő lehetőséget
- Kattintson a "Másolás más helyre" választógombra.
- Kattintson a "Csak egyedi rekordok" jelölőnégyzetre.
- Válasszon egy üres részt a munkalapon, ahol meg szeretné jeleníteni az egyedi listát. Írja be ezt a "Másolás ide:" mezőbe. (Vegye figyelembe, hogy ez a mező szürkén jelenik meg, amíg a "Másolás más helyre" lehetőséget választja.
- Kattintson az OK gombra. Az egyedi számlaszámok az F1-ben jelennek meg.
- Adja meg az esetleges alsóvonali manipulációkat, tömbképleteket stb.
Előnyök: Gyorsabb, mint az 1. módszer. Nincs szükség válogatásra.
Hátrányok: Az ezek után szükséges CSE-képletek megpördítik a fejét.
3. módszer
Használja a Data Consolidate alkalmazást.
Az életminőségem javult, amikor az Excel felajánlotta a Data Consolidate szolgáltatást. Ez NAGY volt! 30 másodpercbe telik a beállítása, de a DSUM-ok és más módszerek halálát írta elő. Számlaszámának az összesíteni kívánt számmezőtől balra kell lennie. Minden oszlop felett címsorokkal kell rendelkeznie. Tartománynevet kell rendelnie ahhoz a cellák téglalap alakú blokkjához, amely tartalmazza a számlaszámokat a bal oszlop mentén és a fejléceket a tetején. Ebben az esetben ez a tartomány A1: B100.
- Jelölje ki az A1: B100 elemet
- Hozzárendeljen egy tartománynevet ehhez a területhez úgy, hogy rákattint a névmezőre (a képletsávtól balra), és beír egy nevet, például "TotalMe". (Alternatív megoldásként használja a Beszúrás - Név parancsot).
- Helyezze a cellamutatót a munkalap üres részébe.
- Adatok kiválasztása - konszolidálás
- A referencia mezőbe írja be a tartomány nevét (TotalMe).
- A Címkék használata részben ellenőrizze a felső sor és a bal oszlopot is.
- Kattintson az OK gombra
Előnyök: Ez a kedvenc módszerem. Nincs szükség válogatásra. A parancsikon alt-D N (rangename) alt-T alt-L enter. Könnyen méretezhető. Ha a tartomány 12 havi oszlopot tartalmaz, akkor a válasz minden hónapra vonatkozóan összesítést tartalmaz.
Hátrányok: Ha ugyanazon a lapon másik adatkonszolidációt hajt végre, akkor a Törlés gombbal törölnie kell a régi tartománynevet az Összes hivatkozás mezőből. A számlaszámnak a számadatoktól balra kell lennie. Ez kissé lassabb, mint a pivot táblák, ami észrevehetővé válik a 10 000+ rekordot tartalmazó adatkészleteknél.
4. módszer
Adat részösszegek használata.
Ez egy jó tulajdonság. Mivel az így kapott adatokat furcsa dolgozni, ezért ritkábban használom, mint a Data Consolidate-et.
- Rendezés A oszlop szerint növekvő.
- Jelöljön ki bármely cellát az adattartományban.
- Válassza a menü Adatok - Részösszegek menüpontját.
- Alapértelmezés szerint az Excel felajánlja az adatok utolsó oszlopának részösszeget. Ez ebben a példában működik, de a megfelelő mezők kiválasztásához gyakran át kell görgetnie az "Összösszeg hozzáadása:" listát.
- Kattintson az OK gombra. Az Excel a számlaszám minden változásakor új sort szúr be, összesen.
Miután megadta a részösszegeket, a névmező alatt egy kis 123 jelenik meg. Kattintson a 2-re, hogy számlánként csak egy sort láthasson az összesítéssel. Olvassa el az Excel részösszegek másolása című részt, ahol megismerheti az új helyre másolásához szükséges speciális lépéseket. Kattintson a 3 gombra az összes sor megtekintéséhez. Előnyök: Cool Feature. Nagyszerű jelentések kinyomtatásához összesítéssel és oldalszakadásokkal az egyes szakaszok után.
Hátrányok: Először az adatokat kell rendezni. Lassú sok adat esetén. Csak a Goto-Special-VisbileCellsOn-t kell használnia, hogy máshova kerüljön az összesítés. Az eredeti adatokhoz való visszatéréshez a Data-Subtotals-RemoveAll alkalmazást kell használnia.
5. módszer
Használjon kimutatótáblát.
A pivot táblák a legsokoldalúbbak. Adatait nem kell rendezni. A numerikus oszlopok lehetnek a számlaszámtól balra vagy jobbra. Könnyedén lenyomhatja a számlaszámokat az oldalon vagy az oldalon.
- Jelöljön ki bármely cellát az adattartományban.
- Válassza a menü Adatok - PivotTable elemét.
- Fogadja el az alapértelmezéseket az 1. lépésben
- Győződjön meg arról, hogy a 2. lépésben megadott adattartomány helyes (általában az)
- Ha az Excel 2000 programot használja, kattintson a 3. lépésben az Elrendezés gombra.
- Az elrendezés párbeszédpanelen húzza a Fiók gombot a párbeszédpanel jobb oldalán, és dobja be a Sor területre.
- Húzza az Összeg gombot a párbeszédpanel jobb oldalán, és dobja be az Adatok területre.
- Az Excel 2000 felhasználói kattintson az OK gombra, az Excel 95/97 felhasználók pedig a Tovább gombra.
- Adja meg, hogy az eredményeket új lapra vagy egy meglévő lap adott szakaszára szeretné-e tenni. További információ a pivot táblákról az Excel pivot tábla speciális trükköiben.
- A pivot-táblák hihetetlen funkcionalitást kínálnak, és ezt a feladatot egy pillanattal később teszik lehetővé. A kimutatás eredményeinek másolásához el kell végeznie egy Edit-PasteSpecial-Values értéket, különben az Excel nem engedi beilleszteni a sorokat stb.
Előnyök: Gyors, rugalmas, erőteljes. Gyors, még sok adat esetén is.
Hátrányok: Kissé félelmetes.
Billnek most négy új módszere van a felesleges adatok kiküszöbölésére. Bár ezek a módszerek az idők kezdete óta nem állnak rendelkezésre, mind a Lotus, mind az Excel nagyszerű újító volt, hogy gyorsabb módszereket nyújtson számunkra ennek a hétköznapi feladatnak a megvalósításához.
