Bill esitti tämän viikon kysymyksen tarpeettomista Excel-tiedoista.
Rakennan kuukausittaisen tapahtumaluettelon Excelissä. Kuukauden lopussa minun on poistettava turhat tiedot ja keksittävä yhteensä tilinumeron mukaan. Jokainen tilikoodi voi esiintyä useita kertoja. Sitten Bill kuvasi nykyisen Excel-menetelmänsä, joka on samanlainen kuin alla oleva menetelmä 1, saadakseen ainutlaatuisen luettelon tilikoodeista, ja aikoo käyttää täsmähakukoneen kaavojen matriisia kokonaissumman saamiseksi. Hän kysyy, onko olemassa helpompaa tapaa saada ainutlaatuinen luettelo tilikoodeista, joissa on jokaisen tilin kokonaissummat?
Tämä on täydellinen lomakysymys. Koska olen ollut Lotus-käyttäjä 15 vuoden ajan, tunnistan Billin menetelmän klassiseksi "nopean ja likainen" datan manipulointimenetelmäksi Lotus 2.1: n vanhoista hyvistä ajoista lähtien. Tämä on kausi laskemaan siunauksemme. Kun ajattelet tätä kysymystä, huomaat, että Microsoftin ihmiset ovat todella antaneet meille useita työkaluja vuosien varrella. Jos käytät Excel 97: tä, tämän tehtävän suorittamiseen on vähintään viisi tapaa, jotka kaikki ovat paljon helpompia kuin Billin kuvaama klassinen menetelmä. Tarjoan opetusohjelman viidestä menetelmästä tällä viikolla.
Yksinkertaistetussa tietojoukossa on tilinumerot sarakkeessa A ja summat sarakkeessa B. Tiedot käyvät A2: B100: sta. Sitä ei ole lajiteltu alussa.
Menetelmä 1
Käytä vastausta etsimällä Creative If -lausekkeita yhdessä Liitä erityisarvot -toiminnon kanssa.
Ottaen huomioon Excelin tarjoamat uudemmat työkalut, en enää suosittele tätä menetelmää. Käytin tätä paljon ennen kuin parempia asioita tuli esiin, ja on edelleen tilanteita, joissa se on hyödyllistä. Vaihtoehtoinen nimeni tälle on "The-Lotus-123-When-You-Not-In-The-Mood-To-Use- @ DSUM" -menetelmä. Tässä ovat vaiheet.
- Lajittele tiedot sarakkeen A mukaan.
- Keksi sarakkeessa C kaava, joka pitää käynnissä olevan summan tilikohtaisesti. Solu C2 on
=IF(A2=A1,C1+B2,B2). - Keksi kaavassa D: ssä kaava, joka tunnistaa tietyn tilin viimeisen merkinnän. Solu D2 on
=IF(A2=A3,FALSE,TRUE). - Kopioi C2: D2 alas kaikkiin riveihisi.
- Kopio C2: D100. Tee Edit - PasteSpecial - Arvot takaisin C2: D100: een muuttaaksesi kaavat arvoiksi.
- Lajittele sarakkeen D mukaan laskevasti.
- Niillä riveillä, joilla on TOSI sarakkeessa D, sinulla on ainutlaatuinen luettelo tilinumeroista A: ssa ja lopullinen juokseva summa C: ssä.
Plussat: Se on nopea. Tarvitset vain innokkaan tunnelman IF-lauseiden kirjoittamisesta.
Miinukset: On olemassa parempia tapoja.
Menetelmä 2
Käytä tietosuodatinta - Lisäsuodatin saadaksesi luettelon yksilöivistä tileistä.
Billin kysymys oli, kuinka saada ainutlaatuinen luettelo tilinumeroista, jotta hän voisi käyttää täsmähakukoneen kaavoja saadakseen kokonaissummat. Tämä on tapa saada luettelo yksilöllisistä tilinumeroista.
- Korosta A1: A100
- Valitse valikosta Tiedot, Suodatin, Lisäsuodatin
- Napsauta valintanappia Kopioi toiseen sijaintiin.
- Napsauta "Ainutlaatuiset tietueet" -valintaruutua.
- Valitse laskentataulukon tyhjä osa, jossa haluat yksilöllisen luettelon näkyvän. Kirjoita tämä Kopioi: -kenttään. (Huomaa, että tämä kenttä on harmaa, kunnes valitset "Kopioi toiseen sijaintiin".
- Napsauta OK. Yksilölliset tilinumero näkyvät kentässä F1.
- Syötä kaikki alalinjan manipulaatiot, taulukon kaavat jne. Saadaksesi tuloksia.
Plussat: Nopeampi kuin menetelmä 1. Lajittelua ei tarvita.
Miinukset: Tämän jälkeen vaaditut CSE-kaavat saavat pään pyörimään.
Menetelmä 3
Käytä tietojen yhdistämistä.
Elämänlaatuni parani, kun Excel tarjosi Data Consolidate -palvelua. Tämä oli iso! Sen asettaminen kestää 30 sekuntia, mutta se merkitsi kuolemaa DSUM: ille ja muille menetelmille. Tilisi numeron on oltava vasemmalla numeron kentistä, jotka haluat laskea yhteen. Jokaisen sarakkeen yläpuolella on oltava otsikot. Sinun on määritettävä alueen nimi suorakulmaiselle solulohkolle, joka sisältää tilinumerot vasemmassa sarakkeessa ja otsikot ylhäällä. Tässä tapauksessa alue on A1: B100.
- Korosta A1: B100
- Määritä alueen nimi tälle alueelle napsauttamalla nimiruutua (kaavapalkin vasemmalla puolella) ja kirjoittamalla nimi, kuten "TotalMe". (Vaihtoehtoisesti voit käyttää Lisää - Nimi).
- Aseta solun osoitin laskentataulukon tyhjään osaan.
- Valitse tiedot - Yhdistä
- Kirjoita viitekenttään alueen nimi (TotalMe).
- Tarkista Käytä tarroja -osiossa sekä ylärivi että vasen sarake.
- Napsauta OK
Plussat: Tämä on suosikkini menetelmä. Lajittelua ei vaadita. Pikakuvake on alt-D N (rangename) alt-T alt-L enter. Se on helposti skaalautuva. Jos alueesi sisältää 12 kuukausisaraketta, vastauksella on jokaisen kuukauden kokonaissummat.
Miinukset: Jos teet toisen tietojen yhdistämisen samalle arkille, sinun on tyhjennettävä vanha alueen nimi Kaikki viitteet -kentästä Poista-painikkeella. Tilinumeron on oltava numerotietojesi vasemmalla puolella. Se on hieman hitaampi kuin kääntötaulukot, mikä tulee havaittavaksi aineistoille, joissa on yli 10000 tietuetta.
Menetelmä 4
Käytä tietojen välisummia.
Tämä on hieno ominaisuus. Koska tuloksena olevia tietoja on outoa työskennellä, käytän niitä harvemmin kuin Data Consolidate.
- Lajittele sarakkeen A mukaan nousevasti.
- Valitse mikä tahansa solu data-alueelta.
- Valitse valikosta Tiedot - Välisummat.
- Oletusarvoisesti Excel tarjoaa välitason tietojen viimeisestä sarakkeesta. Tämä toimii tässä esimerkissä, mutta sinun on usein selattava Lisää välisumma: -luetteloa valitaksesi oikeat kentät.
- Napsauta OK. Excel lisää uuden rivin jokaiseen tilinumeron muutokseen, yhteensä.
Kun olet saanut välisummat sisään, näet pienen 123-nimen nimen alla. Napsauta 2 nähdäksesi vain yhden rivin kutakin tiliä kohden. Lue Kopioi Excel-välisummat -kohdasta selitys erityisvaiheista, joita tarvitaan näiden kopioimiseksi uuteen sijaintiin. Napsauta 3 nähdäksesi kaikki rivit. Plussat: Cool Feature. Sopii erinomaisesti raporttien tulostamiseen jokaisen osan jälkeen yhteensä ja sivumurtumilla.
Miinukset: Tiedot on ensin lajiteltava. Hitaasti paljon tietoja. Sinun on käytettävä Goto-Special-VisbileCellsOnly vain saadaksesi loppusummat muualle. Sinun on käytettävä Data-Välisummat-PoistaAll-painiketta palataksesi alkuperäisiin tietoihin.
Menetelmä 5
Käytä kääntötaulukkoa.
Pivot-pöydät ovat kaikista monipuolisimpia. Tietojasi ei tarvitse lajitella. Numerosarakkeet voivat olla tilinumeron vasemmalla tai oikealla puolella. Voit helposti saada tilinumerot alas tai sivun yli.
- Valitse mikä tahansa solu data-alueelta.
- Valitse valikosta Data - PivotTable.
- Hyväksy oletusasetukset vaiheessa 1
- Varmista, että vaiheen 2 data-alue on oikea (se on yleensä)
- Jos käytät Excel 2000: ta, napsauta Asettelu-painiketta vaiheessa 3. Excel 95- ja 97-käyttäjät siirtyvät automaattisesti asetteluun vaihe 3.
- Vedä asetteluikkunassa Tili-painiketta valintaikkunan oikealta puolelta ja pudota se Rivi-alueelle.
- Vedä Määrä-painiketta valintaikkunan oikealta puolelta ja pudota se Data-alueelle.
- Excel 2000 -käyttäjät napsauttavat OK, Excel 95/97 -käyttäjät napsauttavat Seuraava.
- Määritä, haluatko tulokset uudelle tai jo olemassa olevan taulukon tietylle osalle. Lue lisätietoja pivot-taulukoista Excel Pivot Table Advanced Tricks -sovelluksessa.
- Pivot-taulukot tarjoavat uskomattoman toiminnallisuuden ja tekevät tästä tehtävästä helppoa. Jos haluat kopioida pivot-taulukon tulokset, sinun on tehtävä Edit-PasteSpecial-Values, muuten Excel ei anna sinun lisätä rivejä jne.
Plussat: Nopea, joustava, tehokas. Nopea, jopa paljon tietoa varten.
Miinukset: Jotain pelottava.
Billillä on nyt neljä uutta tapaa poistaa turhat tiedot. Vaikka näitä menetelmiä ei ole ollut saatavana aikojen alusta lähtien, sekä Lotus että Excel ovat olleet suuria innovaattoreita tuomaan meille nopeammin tapoja suorittaa tämä arkipäiväinen tehtävä.
