Sisällys
Kirjoitin jo siitä, kuinka voit nopeasti liimata tekstiä useista soluista yhdeksi ja päinvastoin jäsentää pitkän tekstijonon komponenteiksi. Tarkastellaan nyt läheistä, mutta hieman monimutkaisempaa tehtävää – kuinka liimaa tekstiä useista soluista, kun tietty määrätty ehto täyttyy.
Oletetaan, että meillä on asiakastietokanta, jossa yksi yrityksen nimi voi vastata sen työntekijöiden useita eri sähköposteja. Tehtävämme on kerätä kaikki osoitteet yritysten nimien mukaan ja ketjuttaa ne (eroteltuna pilkuilla tai puolipisteillä) tehdäksemme asiakkaille esimerkiksi postituslistan, eli saada jotain seuraavanlaista:
Toisin sanoen tarvitsemme työkalun, joka liimaa (linkittää) tekstin ehdon mukaan – funktion analogin SUMMESLI (SUMIF), mutta tekstiä varten.
Menetelmä 0. Kaava
Ei kovin tyylikäs, mutta helpoin tapa. Voit kirjoittaa yksinkertaisen kaavan, joka tarkistaa, eroaako seuraavan rivin yritys edellisestä. Jos se ei eroa, liimaa seuraava osoite pilkulla erotettuna. Jos se eroaa, "nollaamme" kertyneet, aloittaen uudelleen:
Tämän lähestymistavan haitat ovat ilmeiset: kaikista saadun lisäsarakkeen soluista tarvitsemme vain viimeiset jokaiselle yritykselle (keltainen). Jos luettelo on suuri, sinun on lisättävä toinen sarake, jotta voit valita ne nopeasti DLSTR (LEN), tarkistamalla kertyneiden merkkijonojen pituuden:
Nyt voit suodattaa ne pois ja kopioida tarvittavat osoiteliimaukset myöhempää käyttöä varten.
Menetelmä 1. Yhdellä ehdolla liimauksen makrofunktio
Jos alkuperäistä luetteloa ei ole lajiteltu yrityksen mukaan, yllä oleva yksinkertainen kaava ei toimi, mutta voit helposti kiertää VBA:n pienellä mukautetulla toiminnolla. Avaa Visual Basic Editor painamalla pikanäppäintä Alt + F11 tai käyttämällä painiketta Visual Basic kieleke kehittäjä (Kehittäjä). Lisää avautuvassa ikkunassa uusi tyhjä moduuli valikon kautta Insert – moduuli ja kopioi funktiomme teksti sinne:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " liimaukset eivät ole keskenään samanarvoisia - poistumme virheestä, jos SearchRange.Count <> TextRange.Count Sitten MergeIf = CVERr(xlErrRef) Poistu funktiosta End Jos 'käydä kaikki solut läpi, tarkista ehto ja kerää teksti muuttujaan OutText For i = 1 To SearchRange. Solut.Laske, jos hakualue.solut(i) Like ehto sitten OutText = OutText & TextRange.Cells(i) & erotin Seuraava i 'näytä tulokset ilman viimeistä erotinta MergeIf = Vasen (ulosteksti, linssi (ulkoteksti) - pitkä (erotin)) loppu toiminto
Jos palaat nyt Microsoft Exceliin, toimintoluettelossa (painike fx kaavapalkissa tai välilehdellä Kaavat – Lisää funktio) on mahdollista löytää toimintomme Yhdistä Jos luokkaan Käyttäjän määrittelemä (Käyttäjän määrittelemä). Funktion argumentit ovat seuraavat:
Menetelmä 2. Liitä teksti epätarkan ehdon mukaan
Jos korvaamme ensimmäisen merkin makromme 13. rivillä = likimääräiselle otteluoperaattorille Pitää, niin on mahdollista suorittaa liimaus alkuperäisten tietojen epätarkalla vastaavuudella valintakriteerin kanssa. Jos esimerkiksi yrityksen nimi voidaan kirjoittaa eri muunnelmilla, voimme tarkistaa ja kerätä ne kaikki yhdellä toiminnolla:
Tavallisia yleismerkkejä tuetaan:
- tähti (*) - tarkoittaa mitä tahansa merkkejä (mukaan lukien niiden puuttuminen)
- kysymysmerkki (?) – tarkoittaa mitä tahansa yksittäistä merkkiä
- punnan merkki (#) – tarkoittaa mitä tahansa yhtä numeroa (0-9)
Oletuksena Like-operaattori on isot ja pienet kirjaimet erotteleva, eli ymmärtää esimerkiksi "Orionin" ja "orionin" eri yrityksinä. Voit jättää kirjainkokoa huomioimatta lisäämällä rivin moduulin alkuun Visual Basic -editorissa Vaihtoehto Vertaa tekstiä, joka vaihtaa Like olla kirjainkoolla.
Tällä tavalla voit luoda erittäin monimutkaisia maskeja olosuhteiden tarkistamiseen, esimerkiksi:
- ?1##??777RUS – valikoima kaikkia 777 alueen rekisterikilpiä alkaen 1
- LLC* – kaikki yritykset, joiden nimi alkaa LLC:llä
- ##7## – kaikki tuotteet, joissa on viisinumeroinen digitaalinen koodi, jossa kolmas numero on 7
- ??????? – kaikki viiden kirjaimen nimet jne.
Menetelmä 3. Makrotoiminto tekstin liimaamiseen kahdessa tilanteessa
Teoksessa voi olla ongelma, kun joudut linkittämään tekstiä useamman kuin yhden ehdon. Oletetaan esimerkiksi, että edelliseen taulukkoomme lisättiin vielä yksi sarake, jossa on kaupunki, ja liimaus ei tulisi suorittaa vain tietylle yritykselle, vaan myös tietylle kaupungille. Tässä tapauksessa toimintoamme on hieman modernisoitava lisäämällä siihen toinen aluetarkistus:
Funktio MergeIfs(Tekstialue As Range, Search Range1 As Range, Condition1 As String, Search Range2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'erotinmerkit (voidaan korvata välilyönnillä tai ; jne.). e.) 'jos validointi- ja liimausalueet eivät ole samat, poistu virheestä If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVERr(xlErrRef) Poistu funktiosta End If 'käydä kaikki solut läpi, tarkista kaikki ehdot ja kerää teksti muuttujaan OutText For i = 1 To SearchRange1.Cells.Count, jos SearchRange1.Cells(i) = Ehto1 ja SearchRange2.Cells(i) = Ehto2 Sitten OutText = OutText & TextRange.Cells(i) & erottimen loppu, jos seuraavaksi näytän tulokset ilman viimeistä erotinta MergeIfs = vasen (ulkoteksti, linssi (ulosteksti) - linssi (erotin)) loppufunktio
Sitä käytetään täsmälleen samalla tavalla – vain argumentteja on nyt määritettävä enemmän:
Tapa 4. Ryhmittely ja liimaus Power Queryssä
Voit ratkaista ongelman ilman ohjelmointia VBA:ssa, jos käytät ilmaista Power Query -apuohjelmaa. Excel 2010-2013:lle sen voi ladata täältä, ja Excel 2016:ssa se on jo oletusarvoisesti sisäänrakennettu. Toimintojen järjestys on seuraava:
Power Query ei osaa työskennellä tavallisten taulukoiden kanssa, joten ensimmäinen askel on muuttaa taulukkomme "älykkääksi". Voit tehdä tämän valitsemalla sen ja painamalla yhdistelmää Ctrl+T tai valitse välilehdeltä Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona). Välilehdellä, joka sitten tulee näkyviin Rakentaja (Design) voit asettaa taulukon nimen (jätin standardin Taulukko 1):
Ladataan nyt taulukkomme Power Query -apuohjelmaan. Voit tehdä tämän välilehdellä Päiväys (jos sinulla on Excel 2016) tai napsauta Power Query -välilehteä (jos sinulla on Excel 2010-2013) Pöydältä (Tiedot – taulukosta):
Valitse avautuvassa kyselyn muokkausikkunassa sarake napsauttamalla otsikkoa Yhtiö ja paina yllä olevaa painiketta Ryhmä (Ryhmittelyperuste). Syötä ryhmittelyyn uuden sarakkeen nimi ja toiminnon tyyppi – Kaikki linjat (Kaikki rivit):
Napsauta OK ja saamme minitaulukon ryhmitellyistä arvoista jokaiselle yritykselle. Taulukoiden sisältö näkyy selvästi, kun napsautat hiiren vasemmalla painikkeella solujen valkoista taustaa (ei tekstiä!) tuloksena olevassa sarakkeessa:
Lisätään nyt vielä yksi sarake, johon liimaamme funktion avulla jokaisen minitaulukon Osoite-sarakkeiden sisällöt pilkuilla erotettuina. Voit tehdä tämän välilehdellä Lisää sarake painamme Muokattu sarake (Lisää sarake — Muokattu sarake) ja kirjoita näkyviin tulevaan ikkunaan uuden sarakkeen nimi ja kytkentäkaava Power Queryn sisäänrakennetulla M-kielellä:
Huomaa, että kaikki M-funktiot ovat isot ja pienet kirjaimet erottuvat (toisin kuin Excel). Napsautuksen jälkeen OK saamme uuden sarakkeen liimatuilla osoitteilla:
On vielä poistettava jo tarpeeton sarake Pöytäosoitteet (klikkaa otsikkoa hiiren oikealla painikkeella) Poista sarake) ja lataa tulokset taulukkoon napsauttamalla välilehteä Etusivu — Sulje ja lataa (Koti - Sulje ja lataa):
Tärkeä vivahde: Toisin kuin aikaisemmissa menetelmissä (funktioissa), Power Queryn taulukoita ei päivitetä automaattisesti. Jos lähdetietoihin tulee tulevaisuudessa muutoksia, sinun on napsautettava hiiren kakkospainikkeella mitä tahansa tulostaulukon kohtaa ja valittava komento Päivitä & Tallenna (Virkistää).
- Kuinka jakaa pitkä tekstimerkki osiin
- Useita tapoja liimata tekstiä eri soluista yhdeksi
- Like-operaattorin käyttäminen tekstin testaamiseen maskia vasten