Valorile de căutare de text în MS Excel cu concluzia lor într-o listă separată

Localizați valorile de text care îndeplinesc criteriile definite de utilizator. Criterii definite folosind metacaractere. Căutarea va fi efectuată într-un interval cu valori repetate. În prezența de repetiții, ne putem aștepta ca criteriul va satisface mai multe valori. Pentru a le afișa într-un interval separat este convenabil de a folosi o formulă matrice.

Să lista inițială de valori (de exemplu, o listă de instrumente) în intervalul A5: A13. Cm. Exemplu de fișier.

Deriva o gamă separată de valori care satisfac criteriul, criteriul este setat folosind metacaractere (*.). Luați în considerare o varietate de opțiuni de căutare.

A. Găsiți valorile care încep cu un criteriu și care conțin un anumit număr de caractere

Pentru comoditatea de formule de scriere a crea intervalul numit pentru gama Lista A5: A13.

O listă a valorilor găsite vor fi plasate într-o coloană separată.

Găsim toate numele instrumentului care încep cu fraza semănătoarea și lungimea liniei este de 5 caractere.

Criteriul va fi introdus în celula C2 si arata ca acest lucru: „? Drill“. Semnul întrebării este un joker.

Pentru a efectua această opțiune de căutare funcție cerută vă permite să utilizați metacaractere. Folosind funcția de căutare (). "? Drill" Conform criteriului (lungime de 5 caractere) - ar trebui să fie retrase 3 valori: Masina de gaurit, de foraj, foraj.

Pentru a crea o listă care conține valorile găsite, folosiți formula matrice:
= INDEX (lista;
SMALL (IFERROR (IF ((SEARCH ($ C $ 2; List) = 1) * (LEN ($ C $ 2) = LEN (lista)) = 1; LINE (List) -Row ($ A $ 4); ND () ), "");
ROW (INDIRECTE ( "A1: A" RÂDURI (List))))
)

Partea de Căutare ($ C $ 2; List) = 1 determină: A13 cu fraza: Dacă începe valoarea gamei A5 "burghiu?".
O parte din formula LEN ($ C $ 2) = LEN (List) determină dacă valoarea șirului de lungime egală A5: A13 5 simboluri?

Simbolul * (multiplica) între porțiunile și formula este o condiție (valoarea trebuie să înceapă cu burghiul și au aceeași lungime ca și criteriu, adică 5 litere). Criteriul de asemenea, se va întâlni astfel de chestiune absurdă cum drel5. drelM etc. (În cazul în care acestea sunt cuprinse în listă).

B. Găsiți valorile care încep cu un burghiu sau un burghiu și care conțin cel puțin 6 caractere

Criteriul este introdus în celula E2, și arată ca aceasta: „Drill ??“. În acest caz, toate valorile în care începe cu textul-test (cu un burghiu al cuvântului) și o lungime de cel puțin 6 caractere vor fi afișate.

Pentru a crea o listă care conține valorile găsite, folosiți formula matrice:
= INDEX (Lista, cea mai mică (
IFERROR (IF (SEARCH ($ E $ 2; List) = 1; LINE (List) -Row ($ A $ 4); ND ()); "");
ROW (INDIRECTE ( "A1: A" RÂDURI (List))))
)

Partea de Căutare ($ E $ 2; List) = 1 determină dacă valoarea gamei A5 începe: A13 cu fraza „foraj ??“. Criteriul se va întâlni, de asemenea, valorile începând cu fraze de forare. drel23 etc.

B. Găsiți valoarea la care cuvântul este în mijlocul unui șir de foraj

Criteriul este introdus în celula G2 și arată astfel: „? Drill“. În acest caz, vor fi afișate toate valorile care conțin semănătoarea cuvânt, și care au în fața lui și după cel puțin un simbol.

Pentru a crea o listă care conține valorile găsite, folosiți formula matrice:
= INDEX (Lista, cea mai mică (
IFERROR (IF (SEARCH (G $ $ 2; List); ROW (List) -Row ($ A $ 4), ND ()); "");
ROW (INDIRECTE ( "A1: A" RÂDURI (List))))
)

Partea de Căutare ($ G $ 2; Lista) determină dacă valoarea conține o serie de A5: A13 Expresie „foraj?“. Criteriul se va potrivi, de asemenea, valori care conțin expresii 5drel7. Adrelu etc.

G. Găsiți valorile care se termină cu cuvântul burghiului sau detaliați

Criteriul este introdus în celula I2 și arată ca aceasta: „? Drill“. În acest caz, sunt afișate toate valorile, care se încheie cu cuvintele unui burghiu sau un burghiu.

Pentru a crea o listă care conține valorile găsite, folosiți formula matrice:
= INDEX (Lista, cea mai mică (
IFERROR (IF (Căutare ($ I $ 2; DREAPTA ((Lista), LEN ($ I $ 2))) LINE (Lista) -Row ($ A $ 4); ND ()); "");
ROW (INDIRECTE ( "A1: A" RÂDURI (List))))
)

CAUTA parte din formula (I $ 2 $, DREAPTA ((Lista) LEN ($ I $ 2))) determină dacă ultimele 5 caractere sunt aceleași valori ale gamei A5: A13 cu fraza "foraj?". Criteriul se va potrivi, de asemenea, valorile care se încheie pe o frază de foraj. drel6 etc.