Postavite uvjet u Excelu. Pronađite bilo koju riječ s popisa u ćeliji

Ostavite komentar 6,950

Ističemo ćelije koje sadrže tekst za pretraživanje. Razmotrimo različite varijante: odabir ćelija koje sadrže vrijednosti koje točno odgovaraju traženom tekstu; Odabir ćelija koje sadrže tekst za pretraživanje na početku, kraju ili sredini retka. Također ćemo naučiti kako odabrati ćeliju ako njezina vrijednost odgovara jednoj od nekoliko navedenih vrijednosti.

Ovaj je članak osmišljen za korisnike koji već znaju koristiti uvjetno oblikovanje. Za one koji tek počinju koristiti ovaj alat, preporučuje se da pogledaju osnovni članak o ovoj temi.

Izvorna tablica s tekstualnim vrijednostima

Neka u dometu A9:A17 Postoji popis s popisom alata (vidi primjer datoteke). Popis sadrži ćelije s jednom riječi i tekstualne nizove (više redaka odvojenih razmacima).

Zadatak 1 (traženje vrijednosti na popisu koje odgovaraju jednom kriteriju)

Odaberimo ćelije koje sadrže vrijednosti koje zadovoljavaju 1 kriterij (bez). Unesite kriterij u ćeliju B 6 . Osim toga, postavit ćemo 4 vrste pretraživanja:

  • Točno se podudara (odabire ćelije koje sadrže vrijednosti koje točno odgovaraju traženom tekstu);
  • Sadrži (odabire ćelije koje sadrže tekst za pretraživanje na početku, kraju ili sredini retka);
  • Početak (odabir ćelija koje sadrže traženi tekst na početku retka);
  • Završava (odabire ćelije koje sadrže tekst za pretraživanje na kraju retka).

Kao primjer kriterija koristimo tekst " bušilica“, koju ćemo unijeti u ćeliju B 6 . Naravno, za kriterij se može koristiti bilo koji drugi tekst.

Riješenje

Odaberemo vrstu pretraživanja koristeći Group and Switch. Spojimo sve na ćeliju B9 . Sada kada odaberete vrstu pretraživanja Točno odgovara ova ćelija će sadržavati broj 1, kada odaberete Sadrži - 2, Počinje - 3, Završava- 4 (pogledajte primjer datoteke Sheet 1 kriterij (tekst)).

Zadatak 2 (traži RIJEČI na popisu koje odgovaraju jednom kriteriju)

Utvrdite postoji li riječ na popisu nešto je teže nego utvrditi nalazi li se određeni tekst na popisu. Razlika u ovim pristupima pojavljuje se kada na popisu postoje riječi koje sadrže traženi tekst, ali mu se ne podudaraju. Na primjer, riječ na popisu Motorna bušilica sadrži tekst Bušilica, ali riječ Svrdlo se ne nalazi u ovoj ćeliji.

Za odabir ćelija na popisu koje sadrže riječ Proučite, trebate upotrijebiti formule u datoteci primjera na listu "1 kriterij (riječ)".


Jasno je da trebate odabrati ćelije u kojima:

  • točno sadrži riječ Drill ILI;
  • redak teksta počinje riječju Drill ILI;
  • tekstni redak završava riječju Drill ILI;
  • riječ Drill je u sredini tekstualni niz(kriterij je okružen razmacima).

U principu, možete napisati jednu veliku formulu i ne koristiti dodatne stupce (C:F)

OR(ILI(IFGREŠKA(A9=$A$6,0));
OR(IFERROR(SEARCH(" "&$A$6&" ";A9);0));
OR(IFERROR(SEARCH($A$6&" ";LEFT(A9,LENGTH($A$6)+1));0));
OR(IFERROR(" "&$A$6=RIGHT(A9,LENGTH($A$6)+1),0))
)

Zadatak 3 (traženje nekoliko riječi na popisu)

Razmotrite popise čiji su elementi pojedinačne riječi (ne fraze). Odabrat ćemo samo ćeliju koja sadrži bilo koju od kriterijskih riječi (pogledajte primjer datoteke Sheet Nekoliko kriterija (riječ točno)).


To se može učiniti na nekoliko načina.


Ova metoda nije baš zgodna, jer... Kada mijenjate kriterije, morat ćete promijeniti niz konstanti u Name Manageru.

Opcija koja koristi imenovani raspon. Formula =ILI($B2=Riječi_u_rasponu) sličan prethodnom, ali se naziv Words_in_range sada odnosi na raspon ćelija lista (pogledajte članak). Kada dodate nove riječi u ćelije u stupcu F, morat ćete redefinirati imenovani raspon.

Mogućnost korištenja dinamičkog raspona. omogućuje vam dodavanje novih kriterijskih riječi u stupac F bez potrebe za mijenjanjem formula i naziva.

Opcija bez korištenja imena. Formula =ILI($D2="riječ1";$D2="riječ2";$D2="riječ3") omogućuje vam da odbijete korištenje imena. Cijena za to bit će potreba za promjenom pravila Uvjetno oblikovanje svaki put kad se kriterijske riječi promijene.

Problem 4 (traženje nekoliko riječi u popisu, opći slučaj)

Razmotrimo popise čiji su elementi fraze. Odabrat ćemo samo ćeliju koja sadrži bilo koju od kriterijskih riječi: na početku, u sredini ili na kraju fraze ili ako fraza u potpunosti odgovara jednoj od kriterijskih riječi (pogledajte primjer datoteke Sheet Nekoliko kriterija (riječ)).


Riječ Klasa se ne pojavljuje na popisu (deklinacije se ne broje). Ali riječ Tehničar pojavljuje se i na početku fraza, u sredini i na kraju.

Unatoč činjenici da su formule slične onima korištenima u problemu 2, one se bitno razlikuju od njih, jer su .


Rad s VB projektom (11)
Uvjetno oblikovanje (5)
Popisi i rasponi (5)
Makronaredbe (VBA procedure) (62)
Razno (38)

Pronađite bilo koju riječ s popisa u ćeliji

Pretpostavimo da ste od dobavljača/kupca/klijenta primili ispunjenu tablicu s popisom proizvoda:

I ovu tablicu morate usporediti s brojevima artikala/šiframa proizvoda u vašem postojećem katalogu proizvoda:

Kao što vidite, naš katalog sadrži samo artikle bez naziva. Kupac osim brojeva artikala ima i naziv proizvoda, tj. puno nepotrebnih stvari. I morate razumjeti koji su proizvodi prisutni u vašem katalogu, a koji nisu:



U Excelu ne postoje standardne formule za takvo pretraživanje i usporedbu. Naravno, možete pokušati primijeniti VLOOKUP sa zamjenskim znakovima prvo na jednu tablicu, a zatim na drugu. Ali ako se takva operacija mora izvoditi iznova i iznova, tada pisanje nekoliko formula za svaku tablicu, iskreno govoreći, nije comme il faut.
Zato sam danas odlučio pokazati formulu koja, bez ikakvih dodataka. manipulacije će pomoći da se napravi takva usporedba. Da biste to sami shvatili, preporučujem preuzimanje datoteke:
Preuzmi datoteku:

(49,5 KiB, 7734 preuzimanja)


Na listu „Narudžba“ u ovoj datoteci nalazi se tablica primljena od kupca, a na listu „Katalog“ naši artikli.
Sama formula, koristeći datoteku primjera, izgledat će ovako:

PREGLED(2,1/TRAŽI(Imenik!$A$2:$A$11, A2);Imenik!$A$2:$A$11)
=LOOKUP(2,1/SEARCH(Imenik!$A$2:$A$11,A2),Imenik!$A$2:$A$11)
ova formula će vratiti naziv artikla ako tekst sadrži barem jedan artikl iz kataloga i #N/A (#N/A) ako se artikl ne nalazi u katalogu.
Prije nego što poboljšamo ovu formulu svim vrstama dodataka (kao što je uklanjanje nepotrebnih #N/A), shvatimo kako funkcionira.
Funkcija LOOKUP traži navedenu vrijednost (2) u navedenom rasponu (niz - drugi argument). Raspon je obično niz ćelija, ali funkcija VIEW ima prvu značajku koja nam je potrebna - pokušava izravno pretvoriti u niz bilo koji izraz napisan kao drugi argument. Drugim riječima, procjenjuje izraz u ovom argumentu, koji koristimo zamjenom izraza kao drugog argumenta: 1/SEARCH(Directory!$A$2:$A$11;A2) . Dio PRETRAŽIVANJE (Katalog!$A$2:$A$11;A2) pretražuje redom svaku vrijednost s popisa Kataloga u ćeliji A2 (naziv iz tablice Kupac). Ako je vrijednost pronađena, vraća se broj pozicije prvog znaka pronađene vrijednosti. Ako vrijednost nije pronađena, vraća se vrijednost pogreške #VALUE! (#VALUE!). Sada druga značajka: funkcija zahtijeva da podaci budu raspoređeni u nizu uzlaznim redoslijedom. Ako se podaci nalaze drugačije, funkcija će skenirati niz sve dok ne nađe vrijednost koja je veća od tražene, ali što bliže njoj (iako podaci to dopuštaju, za točniju pretragu ipak je bolje za sortiranje popisa uzlaznim redoslijedom). Stoga, prvo podijelimo 1 s izrazom SEARCH(Directory!$A$2:$A$11,A2) da dobijemo niz u obliku: (0.0181818181818182:#VALUE!:#VALUE!:#VALUE!:#VALUE!: #VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST !}
Pa, kao željenu vrijednost dajemo funkciji broj 2 - očito veći broj, nego što se općenito može pronaći u nizu (budući da će jedan podijeljen bilo kojim brojem biti manji od dva). I kao rezultat, dobit ćemo poziciju u nizu na kojoj se pojavljuje zadnje podudaranje iz imenika. Nakon čega će funkcija VIEW zapamtiti ovu poziciju i vratiti vrijednost iz polja Directory!$A$2:$A$11 (treći argument) zapisanu u ovom polju za ovu poziciju.
Faze izračuna funkcije možete vidjeti sami za svaku ćeliju; ovdje ću jednostavno predstaviti faze u malo proširenom obliku za razumijevanje:

  1. =BROWSE(2,1/SEARCH(Imenik!$A$2:$A$11, A2);Imenik!$A$2:$A$11)
  2. =POGLED(2;
    1/(55:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST;!}
    Katalog!$A$2:$A$11)
  3. =POGLED(2;(0.0181818181818182:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST!:#VRIJEDNOST;Каталог!$A$2:$A$11)!}
  4. =POGLED(2;
    1;
    ("FM2-3320":"CV455689":"Q5949X":"CE321A":"CE322A":"CE323A":"00064073":"CX292708":"CX292709":"CX292710"))
  5. = "FM2-3320"

Sada malo doradimo funkciju i napravimo još nekoliko implementacija
Implementacija 1:
Umjesto artikala i #N/A, prikazat ćemo "Da" za pronađene pozicije i "Nije pronađeno u katalogu" za one koje nedostaju:
=IF(END(VIEW(2,1/SEARCH(Catalog!$A$2:$A$11,A2)));"Nije pronađeno u katalogu","Da")
=IF(ISNA(LOOKUP(2,1/SEARCH(Imenik!$A$2:$A$11,A2))),,"Nije pronađeno u imeniku","Da")
Rad s funkcijom je jednostavan - razvrstali smo LOOKUP, tako da nam preostaje samo UND i IF.
UNM (ISNA) se vraća PRAVI ako izraz unutar njega vraća vrijednost greške #N/A (#N/A) I NETOČNO ako izraz unutar ne vrati ovu vrijednost pogreške.
IF (IF) vraća ono što je određeno drugim argumentom ako je izraz u prvom jednak PRAVI a što je specificirano trećim argumentom, ako je izraz prvog argumenta NETOČNO.

Implementacija 2:
Umjesto #N/A prikazat ćemo "Nije pronađeno u katalogu", ali ako su artikli pronađeni, prikazat ćemo nazive ovih artikala:
=IFERROR(VIEW(2,1/SEARCH(Katalog!$A$2:$A$11,A2),Katalog!$A$2:$A$11);"Nije u katalogu")
=IFERROR(LOOKUP(2,1/SEARCH(Katalog!$A$2:$A$11,A2),Katalog!$A$2:$A$11),"Nije u katalogu")
O funkciji IFERROR detaljno sam govorio u ovom članku:.
Ukratko, ako izraz određen prvim argumentom funkcije vraća vrijednost bilo koje pogreške, tada će funkcija vratiti ono što je zapisano drugim argumentom (u našem slučaju, tekst "Nije pronađeno u imeniku"). Ako izraz ne vrati pogrešku, tada će funkcija IFERROR upisati vrijednost koju je izraz dobio u prvi argument (u našem slučaju to će biti naziv članka).

Provedba 3
Potrebno je ne samo utvrditi kojem artiklu odgovara, već i prikazati cijenu artikla za ovaj artikl (same cijene se trebaju nalaziti u stupcu B Kataloškog lista):
=IFERROR(VIEW(2,1/SEARCH(Imenik!$A$2:$A$11,A2),Imenik!$B$2:$B$11);"")
=IFERROR(LOOKUP(2,1/SEARCH(Imenik!$A$2:$A$11,A2),Imenik!$B$2:$B$11),"")

Nekoliko važnih napomena:

  • podaci na listu s brojevima artikala ne smiju sadržavati prazna polja. U suprotnom, s visokim stupnjem vjerojatnosti, formula će točno vratiti vrijednost prazna ćelija, a ne onaj koji odgovara uvjetima pretraživanja
  • Formula pretražuje na takav način da se pronađe bilo koje podudaranje. Na primjer, broj je napisan kao članak 1 , a u retku imena može biti, osim cijelog 1, i 123 , 651123 , FG1412NM i tako dalje. Za sve ove stavke može se odabrati artikal broj 1, jer sadržan je u svakom naslovu. Obično se to može dogoditi ako se članak 1 nalazi na kraju popisa

Stoga je preporučljivo Razvrstajte popis prije upotrebe formule uzlazno (od najmanjeg prema najvećem, od A do Z).

U primjeru priloženom na početku članka pronaći ćete sve analizirane opcije.

Ako trebate prikazati sva imena, možete koristiti funkciju iz mog dodatka.

Je li članak pomogao? Podijelite link sa svojim prijateljima! Video lekcije

Nedavno su me u komentarima na post pitali kako izbrojati ćelije koje sadrže određeni znak/slovo. Uspio sam riješiti problem bez korištenja VBA koda na temelju formule polja. Ako prije niste koristili takve formule, preporučujem da počnete s napomenom.

Korak 1. Odredite nalazi li se željeni znak u ćeliji (slika 1).

Riža. 1. Je li željeni znak sadržan u odabranoj ćeliji?

FIND($C$1;A1) – traži znak pohranjen u $C$1 u nizu A1; ako je pronađen, vraća poziciju ovog znaka u nizu; ako ga ne pronađe, vraća grešku #VALUE!

Preuzmite bilješku u formatu, primjere u formatu

Korak 2. Riješite se pogrešnih vrijednosti (slika 2).

Riža. 2. Zamjena pogrešnih vrijednosti nulama

IFERROR(FIND($C$1,A1),0) – vraća 0 ako vrijednost funkcije FIND($C$1,A1) stvara pogrešku, inače vraća vrijednost same funkcije FIND($C$1,A1) .

Korak. 3. Zamijenite broj pozicije s jedinicom

Riža. 3. Svođenje svih pozitivnih vrijednosti na jednu

IFERROR(ORRUP(FIND($C$1,A1)/1000,1);0) – transformacija koja vam omogućuje da dobijete 1 za bilo koju vrijednost pozicije (nije pogreška); broj pozicije koji vraća funkcija FIND($C$1;A1) dijeli se s 1000 i zaokružuje na najbliži cijeli broj (da budemo precizni, Excel stanica može sadržavati najviše 32.767 znakova, tako da “radi sigurnosti” možete zamijeniti 1000 sa 32.767 :); u tom ćete slučaju izbjeći pogreške sa bilo kojim sadržajem ćelije).

Alternativno, možete koristiti funkciju IF:

IFERROR(IF(FIND($C$1,A1)>0,1);0) – ako je vrijednost funkcije FIND($C$1,A1) veća od nule, tada funkcija IF zamjenjuje ovu vrijednost s jedinicom. Imajte na umu da treći argument funkcije IF nedostaje jer FIND($C$1;A1) ne može vratiti vrijednost manju ili jednaku 0 (FIND($C$1;A1) vraća samo pozitivne vrijednosti ili #VALUE! ) pogreška.

Korak. 4. Sažejmo sve. Podsjećam da jedinica odgovara ćeliji koja sadrži željeni simbol, a nula odgovara ćeliji koja ne sadrži željeni simbol (slika 4).

Riža. 4. Zbroj ćelija koje sadrže željeni simbol

U ćeliji C2 upotrijebio sam formulu polja:

(=SUM(IFERROR(OCRUP(FIND($C$1,A1:A14)/1000,1),0)))

Funkcija sekvencijalno izračunava vrijednosti =IFERROR(OCRUP(FIND($C$1,A) 1 )/1000;1);0), =IFERROR(OCRUP(FIND($C$1,A) 2 )/1000;1);0) ... i tako dalje do =IFERROR(OCRUP(FIND($C$1;A) 14 )/1000;1);0)

Rezultat izračuna (0 ili 1) pohranjuje se u memoriju, tvoreći virtualni niz (1,1,1,0,1,1,0,1,1,0,0,0,1). Funkcija SUM jednostavno zbraja sve jedinice.

Alternativna funkcija: =SUM(IFERROR(IF(FIND($C$1,A1:A14)>0,1),0))

p.s. Ne unosite vitičaste zagrade u traku formule, već upišite cijelu formulu bez vitičastih zagrada i istovremeno pritisnite Ctrl+Shift+Enter



© Autorska prava 2017, https://craft-m.ru