Bilas uždavė šios savaitės klausimą apie nereikalingus „Excel“ duomenis.
Aš kuriu mėnesio operacijų sąrašą „Excel“. Mėnesio pabaigoje turiu pašalinti nereikalingus duomenis ir pateikti bendrą sumą pagal sąskaitos kodą. Kiekvienas sąskaitos kodas gali atsirasti kelis kartus. Tada Billas aprašė savo dabartinę „Excel“ metodiką, panašią į toliau pateiktą 1 metodą, kad būtų sudarytas unikalus sąskaitų kodų sąrašas ir planai naudoti visuotinės pilietybės formulių matricą, kad gautumėte sumas. Jis klausia, ar yra lengvesnis būdas gauti unikalų sąskaitų kodų sąrašą su kiekvienos sąskaitos bendra suma?
Tai puikus atostogų klausimas. Būdamas „Lotus“ vartotoju 15 metų, aš pripažįstu Billo metodą kaip klasikinį „greito ir nešvaraus“ duomenų manipuliavimo metodą nuo senų gerų „Lotus“ leidimo 2.1 dienų. Tai yra mūsų palaiminimų skaičiavimo sezonas. Pagalvojęs apie šį klausimą supranti, kad „Microsoft“ žmonės per daugelį metų mums tikrai suteikė daugybę įrankių. Jei naudojate „Excel 97“, šiai užduočiai atlikti yra bent penki metodai, kurie visi yra daug lengvesni nei Billo aprašytas klasikinis metodas. Šią savaitę pasiūlysiu pamoką apie penkis metodus.
Mano supaprastintame duomenų rinkinyje sąskaitų numeriai yra A stulpelyje, o sumos - B stulpelyje. Duomenys vykdomi iš A2: B100. Pradžioje jis nėra rūšiuojamas.
1 metodas
Norėdami rasti atsakymą, naudokite „Creative“ teiginius kartu su „Įklijuokite specialiąsias vertybes“.
Atsižvelgiant į naujesnius „Excel“ siūlomus įrankius, šio metodo aš jau nerekomenduoju. Anksčiau tai naudojau daug, kol atsirado geresnių dalykų, ir vis dar yra situacijų, kai tai yra naudinga. Mano alternatyvus šio vardo pavadinimas yra „The Lotus-123-When-You-Nebe-In-The-Mood-To-Use- @ DSUM“. Čia yra žingsniai.
- Rūšiuoti duomenis pagal A stulpelį.
- Sugalvokite C stulpelio formulę, pagal kurią bus išlaikoma bendra kiekvienos sąskaitos suma. Ląstelė C2 yra
=IF(A2=A1,C1+B2,B2). - Sugalvokite D formulę, kuri identifikuos paskutinį įrašą konkrečioje sąskaitoje. Ląstelė D2 yra
=IF(A2=A3,FALSE,TRUE). - Nukopijuokite C2: D2 žemyn į visas savo eilutes.
- C2 kopija: D100. Atlikite „Edit - PasteSpecial - Values“ atgal į C2: D100, kad pakeistumėte formules į reikšmes.
- Rūšiuoti pagal D stulpelį mažėjančia tvarka.
- Eilutėse, kurių D stulpelyje yra TIESA, turite unikalų sąskaitų numerių sąrašą A, o galutinę einamąją sumą - C.
Argumentai už: tai greita. Viskas, ko jums reikia, yra labai svarbu rašyti IF teiginius.
Minusai: Yra geresnių būdų.
2 metodas
Norėdami gauti unikalių paskyrų sąrašą, naudokite duomenų filtrą - išplėstinį filtrą.
Billas iš tikrųjų klausė, kaip gauti unikalų sąskaitų numerių sąrašą, kad jis galėtų naudoti TPV formules, kad gautų visas sumas. Tai būdas gauti unikalių sąskaitų numerių sąrašą.
- Pažymėkite A1: A100
- Meniu pasirinkite Duomenys, Filtras, Išplėstinis filtras
- Spustelėkite radijo mygtuką „Kopijuoti į kitą vietą“.
- Spustelėkite žymimąjį laukelį „Tik unikalūs įrašai“.
- Pasirinkite tuščią darbalapio skiltį, kurioje norite rodyti unikalų sąrašą. Įveskite tai lauke „Kopijuoti į:“. (Atminkite, kad šis laukas yra pilkas, kol pasirenkate „Kopijuoti į kitą vietą“.
- Spustelėkite Gerai. Unikalūs sąskaitų numeriai bus rodomi F1.
- Norėdami gauti rezultatus, įveskite visas manipuliacijas, masyvo formules ir kt.
Argumentai už: Greitesnis nei 1 metodas. Rūšiavimo nereikia.
Trūkumai: po to reikalingos CSE formulės privers galvą suktis.
3 metodas
Naudokite duomenų konsolidavimą.
Mano gyvenimo kokybė pagerėjo, kai „Excel“ pasiūlė „Data Consolidate“. Tai buvo DIDELIS! Jo nustatymas trunka 30 sekundžių, tačiau DSUM ir kitiems metodams tai buvo mirtis. Jūsų sąskaitos numeris turi būti kairėje nuo skaitinių laukų, kuriuos norite sudaryti. Virš kiekvieno stulpelio turite turėti antraštes. Turite priskirti diapazono pavadinimą stačiakampio formos langelių blokui, kuriame yra abonementų numeriai kairiajame stulpelyje ir antraštės viršuje. Šiuo atveju tas diapazonas yra A1: B100.
- Paryškinkite A1: B100
- Priskirkite šios srities diapazono pavadinimą spustelėdami pavadinimo laukelį (kairėje nuo formulės juostos) ir įvesdami pavadinimą, pvz., „TotalMe“. (Arba naudokite „Insert - Name“).
- Įdėkite langelio žymeklį į tuščią darbalapio dalį.
- Pasirinkite duomenis - konsoliduoti
- Nuorodos lauke įveskite diapazono pavadinimą (TotalMe).
- Skyriuje „Naudoti etiketes“ patikrinkite viršutinę eilutę ir kairįjį stulpelį.
- Spustelėkite Gerai
Argumentai už: Tai mano mėgstamiausias metodas. Rūšiuoti nereikia. Spartusis klavišas yra alt-D N (rangename) alt-T alt-L enter. Jis lengvai keičiamas. Jei jūsų diapazone yra 12 mėnesio stulpelių, atsakyme bus kiekvieno mėnesio bendra suma.
Trūkumai: jei tame pačiame lape atliksite kitą duomenų konsolidavimą, naudodami mygtuką „Ištrinti“, iš lauko Visos nuorodos turite išvalyti senąjį diapazono pavadinimą. Sąskaitos numeris turi būti kairėje nuo skaitinių duomenų. Tai yra šiek tiek lėtesnė nei suvestinės lentelės, kuri tampa pastebima duomenų rinkiniuose, kuriuose yra daugiau nei 10 000 įrašų.
4 metodas
Naudokite duomenų tarpines sumas.
Tai puiki savybė. Kadangi gautus duomenis dirbti keista, juos naudoju rečiau nei „Data Consolidate“.
- Rūšiuoti pagal A stulpelį didėjimo tvarka.
- Pasirinkite bet kurią duomenų diapazono langelį.
- Meniu pasirinkite Duomenys - Tarpinės sumos.
- Pagal numatytuosius nustatymus „Excel“ siūlo tarpinę sumą sudaryti paskutinį jūsų duomenų stulpelį. Tai veikia šiame pavyzdyje, tačiau dažnai reikia slinkti per sąrašą „Pridėti tarpinę sumą:“, kad pasirinktumėte teisingus laukus.
- Spustelėkite Gerai. Kiekvieną sąskaitos numerio pakeitimą „Excel“ įterpia naują eilutę su visa suma.
Kai turėsite tarpines sumas, po pavadinimo laukeliu pamatysite mažą 123. Spustelėkite 2, kad pamatytumėte tik vieną eilutę kiekvienoje sąskaitoje su visomis sumomis. Skaitykite „Kopijuoti„ Excel “tarpines sumas“, kad paaiškintumėte specialius veiksmus, kurių reikia norint juos nukopijuoti į naują vietą. Norėdami pamatyti visas eilutes, spustelėkite 3. Argumentai už: puiki funkcija. Puikiai tinka atspausdinti ataskaitas su visomis ir puslapių pertraukomis po kiekvieno skyriaus.
Trūkumai: pirmiausia reikia surūšiuoti duomenis. Lėtas daug duomenų. Norėdami gauti sumas kitur, turite naudoti „Goto-Special-VisbileCellsOnly“. Norėdami grįžti prie pradinių duomenų, turite naudoti „Data-Subtotals-RemoveAll“.
5 metodas
Naudokite „Pivot“ lentelę.
„Pivot“ lentelės yra universaliausios iš visų. Jūsų duomenys neturi būti rūšiuojami. Skaitmeniniai stulpeliai gali būti kairėje arba dešinėje nuo sąskaitos numerio. Galite lengvai pasiekti, kad sąskaitos numeriai sumažėtų arba peržengtų puslapį.
- Pasirinkite bet kurią duomenų diapazono langelį.
- Meniu pasirinkite Duomenys - „PivotTable“.
- 1 veiksme sutikite su numatytaisiais nustatymais
- Įsitikinkite, kad 2 veiksmo duomenų diapazonas yra teisingas (paprastai yra)
- Jei naudojate „Excel 2000“, 3 žingsnyje spustelėkite mygtuką Išdėstymas. „Excel 95“ ir „97“ vartotojai automatiškai pereina į išdėstymą kaip 3 veiksmą.
- Išdėstymo dialogo lange vilkite mygtuką Paskyra iš dešinės dialogo pusės ir nuleiskite jį srityje Eilė.
- Vilkite mygtuką Dydis iš dešinės dialogo pusės ir nuleiskite jį duomenų srityje.
- „Excel 2000“ vartotojai spustelėja Gerai, „Excel 95/97“ vartotojai - Pirmyn.
- Nurodykite, ar norite, kad rezultatai būtų naujame lape, ar tam tikroje esamo lapo dalyje. Skaitykite daugiau apie suvestines lenteles „Excel Pivot Table Advanced Tricks“.
- „Pivot“ lentelės siūlo neįtikėtiną funkcionalumą, todėl ši užduotis tampa greita. Norėdami nukopijuoti suvestinės lentelės rezultatus, turite atlikti „Edit-PasteSpecial-Values“, kitaip „Excel“ neleis įterpti eilučių ir pan.
Argumentai už: greita, lanksti, galinga. Greita, net naudojant daug duomenų.
Suvart: šiek tiek bauginantis.
Dabar Billas turi keturis naujus nereikalingų duomenų pašalinimo metodus. Nors šie metodai nebuvo prieinami nuo laiko pradžios, tiek „Lotus“, tiek „Excel“ buvo puikūs novatoriai, pateikę mums greitesnius būdus, kaip atlikti šią kasdienę užduotį.
