Probleem
Meil on tähtnumbriliste koodide loend. Iga kood koosneb ühest tähest (A, B, C jne), millele järgneb 3-kohaline number. Need koodid peaksid ilmuma tähestikulises järjekorras, kuid mõnikord on need järjestusest väljas. Tahame märgistada järjestusvälised koodid.
Väljakutse nr 1
Mis valemis veerus „Kontroll” asetatakse järjestusest väljas oleva koodi juurde „x”? Selles väljakutses kontrollime ainult seda, kas koodi * numbriline * osa on järjestusest väljas, mitte seda, et täht ise oleks järjestusest väljas.
Väljakutse nr 2
Kuidas saab ülaltoodud valemit laiendada, et kontrollida, kas koodi "alfa" osa (A, B, C jne) on järjestusest väljas? Näiteks peaksime tähistama koodi, mis algab tähega "A", kui see ilmub pärast koodi, mis algab tähega "C" või "B".
Laadige alla allolev tööleht ja võtke proovile!
Märkus: töövihikus on 2 lehte, üks väljakutse nr 1 jaoks, teine väljakutse nr 2 jaoks.
Vihje - selles videos on näpunäiteid sellise probleemi lahendamiseks.
Eeldused
- Kõik koodid sisaldavad alati nelja tähemärki: 1 suurtäht + 3 numbrit.
- Koodide arv tähe kohta on juhuslik, kuid arvväärtustes ei tohiks olla lünki.
- Ainult esimene kood tuleb tähistada järjestusest väljas oleva tähega, mitte kõik järgnevad koodid.
Siin on mõned toimivad lahendused. Oluline on mõista, et Excelis on levinud probleemide lahendamiseks palju, palju võimalusi. Allpool olevad vastused on lihtsalt minu isiklik eelistus. Kõigis allpool toodud valemites on funktsioonide nimed klõpsatavad, kui soovite lisateavet.
Väljakutse nr 1
Algselt läksin selle valemiga:
=IF((LEFT(B5)=LEFT(B6))*(MID(B5,2,3)+1MID(B6,2,3)+0),"x","")
Märkus. MID tagastab teksti. Lisades 1 ja lisades nulli, saame Exceli sundida teksti arvuks. IF-i sisese loogilise testi korrutamine kasutab tõestatud loogikat, et vältida teist pesastatud IF-d. Ma pole kindel, miks ma ei kasutanud RIGHT-i, mis toimiks ka siin hästi.
Pange tähele ka seda, et LEFT ei nõua märkide arvu ja tagastab esimese märgi, kui seda pole esitatud.
Tuginedes mõnele allpool toodud nutikale vastusele, saame veel natuke optimeerida:
=IF((LEFT(B5)=LEFT(B6))*(MID(B6,2,3)-MID(B5,2,3)1),"x","")
Siin sundib matemaatiline operatsioon MID-i lahutamisest MID-ist tekstiväärtused automaatselt numbriteks.
Väljakutse nr 2
Selle lahenduse jaoks kasutasin mitut pesastatud IF-d (loetavuse huvides lisati reavahet):
=IF(LEFT(B5)=LEFT(B6), IF((MID(B5,2,3)+1MID(B6,2,3)+0),"x",""), IF(CODE(B5)+1CODE(B6),"x",""))
Tegin seda seetõttu, et esimene test VASAK (B5) = VASAK (B6) määrab, kas kontrollime numbreid või tähti. Kui esimene märk on sama, kontrollime ülaltoodud numbreid. Kui ei, kontrollime ainult esimest tähte.
Pange tähele, et funktsioon CODE tagastab esimese märgi ascii numbri, kui tekstistring sisaldab rohkem kui 1 tähemärki. See tundub nagu häkkimine ja muudab koodi ehk vähem arusaadavaks, kuid töötab :)
Kui see riivab teie tundlikkust, kasutage ainult esimest märki.
