Ainutlaatuinen vierekkäisistä sarakkeista - Excel-vinkkejä

Eräänä päivänä olin aikeissa luoda ainutlaatuinen yhdistelmä kahdesta vierekkäisestä sarakkeesta Excelissä. Teen tämän yleensä Poista kopiot tai Lisäsuodattimella, mutta ajattelin yrittää tehdä sen uudella UNIQUE-toiminnolla, joka tulee Office 365: een vuonna 2019. Yritin useita ideoita, eikä mikään toiminut. Joten menin Dynamic Arrays -mestarin, Joe McDaidin, puoleen. Vastaus on melko siisti, ja olen varma, että unohdan sen, joten dokumentoin sen sinulle ja minulle. Olen varma, että kahden vuoden kuluttua aion googlata, kuinka tämä tehdään ja ymmärrän "Voi, katso! Minä olen se, joka kirjoitti artikkelin tästä!"

Ennen kuin pääset UNIQUE-toimintoon, katsokaa, mitä yritän tehdä. Haluan jokaisen ainutlaatuisen yhdistelmän myyntiedustajaa sarakkeesta B ja tuote sarakkeesta C. Normaalisti seuraan näitä vaiheita:

Kopioi otsikot B1: stä ja D1: stä laskentataulukon tyhjään osaan
Valitse kohdasta B1 Tiedot, Suodatin, Lisäasetukset
Valitse Lisäsuodatin-valintaikkunassa Kopioi uuteen sijaintiin
Määritä otsikon vaiheesta 1 lähtöalueeksi
Valitse ruutu Ainutlaatuiset arvot
Napsauta OK

Kopioi kaksi otsikkoa tyhjään osaan, josta tulee lähtöalue

Tuloksena on näiden kahden kentän jokainen ainutlaatuinen yhdistelmä. Huomaa, että Lisäsuodatin ei lajittele kohteita - ne näkyvät alkuperäisessä järjestyksessä.

Ainutlaatuisen luettelon saaminen on yksi suosituimmista käyttötavoistani Lisäsuodattimelle

Tästä prosessista tuli helpompaa Excel 2010: ssä Nauhan Data-välilehden Poista kopiot -komennon ansiosta. Toimi seuraavasti:

Valitse B1: D227 ja Ctrl + C kopiointia varten
Liitä laskentataulukon tyhjään osaan.

Tee kopio tiedoista, koska kaksoiskappaleiden poistaminen on tuhoavaa
Valitse Tiedot, Poista kaksoiskappaleet
Poista Poista kopiot -valintaikkunassa Päivämäärä-valinta. Tämä kertoo Excelille tarkastella vain Rep- ja Product-tuotteita.
Napsauta OK

Käske Poista kaksoiskappaleet huomioimaan vain Rep ja Date

Tulokset ovat melkein täydelliset - sinun tarvitsee vain poistaa Päivämäärä-sarake.

Poista ylimääräinen sarake

Kysymys: Onko olemassa jokin tapa saada UNIQUE-funktio tarkastelemaan vain sarakkeita B & D? (Jos et ole vielä nähnyt uutta UNIQUE-toimintoa, lue: UNIQUE-funktio Excelissä.)

Pyytäminen =UNIQUE(B2:D227)saisi jokaisen ainutlaatuisen Rep-, Date- ja Product-yhdistelmän, jota etsimme.

Kuinka voimme siirtää kaksi vierekkäistä saraketta UNIQUE-funktiolle?

Kun dynaamiset taulukot otettiin käyttöön syyskuussa, sanoin, että meidän ei koskaan tarvitse enää huolehtia Ctrl + Shift + Enter -kaavojen monimutkaisuudesta. Mutta tämän ongelman ratkaisemiseksi aiot käyttää konseptia nimeltä Nosto. Toivottavasti olet jo ladannut Excel Dynamic Arrays Straight To The Point -kirjani. Siirry sivuille 31-33 saadaksesi täydellisen selityksen nostamisesta.

Kirjassani on täydellinen selitys nostamisesta (ja myöhemmin, kun menet lajittelemaan tuloksia, pareittain nostaminen)

Ota Excel-toiminto, joka odottaa yhtä arvoa. =CHOOSE(Z1,"Apple","Banana")Palauttaa esimerkiksi joko Applen tai Banaanin sen mukaan, sisältääkö Z1 1 (Applelle) vai 2 (Banaanille). CHOOSE-toiminto odottaa skalaaria ensimmäisenä argumenttina.

Mutta sen sijaan aiot välittää taulukon vakion (1,2) ensimmäisenä argumenttina VALITSE. Excel suorittaa nostotoimenpiteen ja laskee VALITSE kahdesti. Jos haluat arvon 1, haluat myyntivihjeet B2: B227: ssä. Jos haluat arvon 2, haluat tuotteen D2: D227 tuotteet.

Käske VALITSE palauttamaan kaksi vastausta

Normaalisti vanhassa Excelissä implisiittinen leikkauspiste olisi vääristänyt tulokset. Mutta nyt, kun Excel voi levittää tuloksia moniin soluihin, yllä oleva kaava palauttaa joukon kaikkia vastauksia ryhmissä B ja D:

Menestys! Kaikki on täältä alamäkeen

Minusta tuntuu loukkaavalta älykkyyttäsi kirjoittamalla loppu artikkeli, koska täältä se on erittäin yksinkertaista.

Kiedo edellisen kuvakaappauksen kaava UNIQUE-muotoon ja saat vain ainutlaatuiset myyntiedustajan ja tuotteen yhdistelmät =UNIQUE(CHOOSE((1,2),B2:B227,D2:D227)).

Vielä ei ole lajiteltu

Varmistaaksesi ymmärryksesi, yritä muuttaa yllä olevaa kaavaa palauttaaksesi kaikki ainutlaatuiset yhdistelmät kolmesta sarakkeesta: Myyntiedustaja, Tuote, Väri.

Muuta ensin matriisivakio viittaamaan kohtaan (1,2,3).

Sitten lisätä neljäs argumentti valita palata väri E2: E227: =UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)).

Palauta kolmen sarakkeen ainutlaatuinen yhdistelmä

Olisi mukavaa lajitella nämä tulokset, joten valitsemme Lajittele kaavan avulla käyttämällä Lajittele ja Lajittele.

Normaalisti ensimmäisen sarakkeen mukaan nouseva lajittelutoiminto olisi =SORT(Array)tai =SORT(Array,1,1).

Jotta voit lajitella kolmen sarakkeen mukaan, sinun on nostettava pari kerrallaan =SORT(Array,(1,2,3),(1,1,1)). Tässä kaavassa, kun pääset SORT: n toiseen argumenttiin, Excel haluaa tietää, minkä sarakkeen mukaan lajitellaan. Lähetä yhden arvon sijaan kolme saraketta matriisivakion sisään: (1,2,3). Kun pääset kolmanteen argumenttiin, jossa määritetään 1 nousevaksi tai -1 laskevaksi, lähetä kolmen 1: n matriisivakio osoittamaan nousevaa, nousevaa ja nousevaa. Seuraava kuvakaappaus näkyy =SORT(UNIQUE(CHOOSE((1,2,3),B2:B227,D2:D227,E2:E227)),(1,2,3),(1,1,1)).

Lisätietoja parinostosta on Excel Dynamic Arrays Straight to the Point -sivulla 34.

Ainakin vuoden 2018 loppuun asti voit ladata Excel Dynamic Arrays -kirjan ilmaiseksi tämän sivun alaosassa olevan linkin kautta.

Minua kannustetaan huomaamaan, että vastaus tämän päivän kysymykseen on hieman monimutkainen. Kun dynaamiset taulukot ilmestyivät, ajattelin heti kaikkia hämmästyttäviä kaavoja, jotka Aladin Akyurek ja muut lähettivät viestitaululle, ja kuinka näistä kaavoista tulisi paljon yksinkertaisempia uudessa Excelissä. Mutta tämän päivän esimerkki osoittaa, että kaava-neroilla on edelleen tarve luoda uusia tapoja käyttää dynaamisia taulukoita.