Viime yönä Craig Crossmanin Computer America -radioshow'lla Bostonin Joella oli kysymys:
Minulla on sarake laskunumeroita. Kuinka voin käyttää Exceliä kaksoiskappaleiden merkitsemiseen?
Ehdotin ehdollisten muotojen ja COUNTIF-kaavan käyttöä. Tässä on yksityiskohtia siitä, miten se toimii.
Haluamme määrittää ehdollisen muotoilun koko alueelle, mutta on helpompaa asettaa ehdollinen muoto alueen ensimmäiselle solulle ja kopioida sitten ehdollinen muoto. Meidän tapauksessamme solussa A1 on laskunumeron otsikko, joten valitsen solun A2 ja valikosta Muoto> Ehdollinen muotoilu. Ehdollinen muotoilu -valintaikkuna alkaa ensimmäisestä avattavasta valikosta "Solun arvo on". Jos kosketat tämän vieressä olevaa nuolta, voit valita "Kaava on".
Kun olet valinnut "Formula Is", valintaikkuna muuttaa ulkoasua. "X: n ja y: n välisten" ruutujen sijasta on nyt yksi kaavaruutu. Tämä kaavalaatikko on uskomattoman tehokas. Voit kirjoittaa minkä tahansa kaavan, josta voit haaveilla, kunhan kaava arvioi arvon TOSI tai EPÄTOSI.
Meidän tapauksessamme meidän on käytettävä COUNTIF-kaavaa. Ruutuun kirjoitettava kaava on
=COUNTIF(A:A,A2)>1
Englanniksi tämä sanoo: "katso läpi koko sarakkeen A alue. Laske, kuinka monta solua tällä alueella on sama arvo kuin mitä on A2: ssa. (On todella tärkeää, että kaavan" A2 "osoittaa nykyinen solu - solu, johon asetat ehdollisen muotoilun. Joten - jos tietosi ovat sarakkeessa E ja asetat ensimmäisen ehdollisen muotoilun E5: een, kaava olisi =COUNTIF(E:E,E5)>0). Sitten verrataan, onko tämä määrä on> 1. Ihannetapauksessa ilman kaksoiskappaleita määrä on aina 1 - koska solu A2 on alueella - meidän pitäisi löytää sarakkeesta A tarkalleen yksi solu, joka sisältää saman arvon kuin A2.
Napsauta Muotoile… -painiketta
Nyt on aika valita ikävä muoto. Tämän solujen muotoilu -valintaikkunan yläosassa on kolme välilehteä. Fontti-välilehti on yleensä ensimmäinen, joten voit valita lihavoidun, punaisen fontin, mutta pidän jotain ikävämmästä. Napsautan yleensä Kuviot-välilehteä ja valitsen joko kirkkaan punaisen tai kirkkaan keltaisen. Valitse väri ja sulje sitten Format Cells -valintaikkuna napsauttamalla OK.
Valittu muoto näkyy "Esikatselu käytettävästä muodosta" -ruudussa. Sulje Ehdollinen muotoilu -valintaikkuna napsauttamalla OK…
… Eikä mitään tapahdu. Vau. Jos asetat ehdollisen muotoilun ensimmäistä kertaa, olisi todella mukavaa saada täällä palautetta siitä, että se toimi. Mutta ellei sinulla ole onni, että solun A2 1098 on kopio jostakin muusta solusta, ehto ei ole totta ja näyttää siltä, ettei mitään tapahtunut.
Sinun on kopioitava ehdollinen muotoilu A2: sta muihin alueesi soluihin. Kun kohdistimen kynnys on A2, tee Muokkaa> Kopioi. Valitse koko sarake painamalla Ctrl + välilyöntinäppäintä. Tee Muokkaa> Liitä erityinen. Valitse Liitä erityinen -valintaikkunassa Muotoile. Napsauta OK.
Tämä kopioi ehdollisen muotoilun kaikkiin sarakkeen soluihin. Nyt - lopuksi - näet joitain soluja, joissa on punainen muotoilu, mikä osoittaa, että sinulla on kaksoiskappale.
On informatiivinen mennä soluun A3 ja tarkastella ehdollista muotoa kopion jälkeen. Valitse A3, paina od saadaksesi ehdollisen muotoilun esiin. Kaava on Kaava on -ruudussa muutettuna laskemaan, kuinka monta kertaa A3 näkyy sarakkeessa A: A.
Huomautuksia
Joen kysymyksessä hänellä oli vain 1700 laskua. Olen perustanut 65536 solua ehdollisella muotoilulla ja jokainen solu vertaa nykyistä solua 65536 muuhun soluun. Excel 2005: ssä - enemmän rivejä - ongelma on vielä pahempi. Teknisesti ensimmäisen vaiheen kaava olisi voinut olla:=COUNTIF($A$2:$A$1751,A2)>1
Lisäksi kun kopioit ehdollisen muodon koko sarakkeeseen, olisit voinut sen sijaan valita vain tiedot sisältävät rivit ennen Liitä erikoismuodot.
Lisää
Toinen kysymys, jonka kuvasin kysymyksen jälkeen, on, että saraketta ei voi lajitella ehdollisen muodon perusteella. Jos sinun on lajiteltava nämä tiedot siten, että kaksoiskappaleet ovat yhdellä alueella, toimi seuraavasti. Lisää ensin kohtaan B1 otsikko "Kopioi?". Kirjoita tämä kaava B2: =COUNTIF(A:A,A2)>1.
Kun solun osoitin on B2, napsauta automaattisen täytön kahvaa (pieni neliö solun oikeassa alakulmassa) kopioidaksesi kaavan koko alueen.
Voit nyt lajitella sarakkeen B laskevan ja A nousevan mukaan, jotta ongelmalaskut ovat alueen yläosassa.
Tämä ratkaisu olettaa, että haluat korostaa molemmat kaksoislaskuja, jotta voit manuaalisesti selvittää, mitkä haluat poistaa tai korjata. Jos et halua merkitä ensimmäinen esiintyminen kaksoiskappale, voit säätää kaavaa olla: =COUNTIF($A$2:$A2,A2)>1. On tärkeää kirjoittaa dollarin merkit täsmälleen kuvan osoittamalla tavalla. Tämä tarkastelee kaikkia soluja vain nykyisestä solusta ylöspäin ja etsii päällekkäisiä merkintöjä.
Kiitos Bostonille Joelle kysymyksestä!
