Funksioni SEARCH ( ) , versioni anglisht i SEARCH(), gjen paraqitjen e parë të një vargu teksti në një varg tjetër dhe kthen pozicionin fillestar të vargut të gjetur.

Sintaksa e funksionit

KËRKONI(teksti i kërkimit;varg_kërkuesish;[pozicioni i_fillimit])

Kërko_tekst- teksti që do të gjendet.

Kërkoni_string- teksti në të cilin kërkohet Kërko_tekst.

pozita_fillimiështë pozicioni i karakterit në lookup_string nga i cili duhet të fillojë kërkimi. Nëse argumenti pozita_fillimi të hequra, supozohet një vlerë prej 1.

Në argument teksti i kërkimit ju mund të përdorni - pikëpyetje (?) dhe yll (*). Pikëpyetja përputhet me çdo karakter; yll - çdo sekuencë karakteresh. Nëse duhet të gjeni një pikëpyetje ose një yll në tekst, vendosni një tildë (~) përpara tyre.

Nëse teksti i kërkimit nuk gjendet, #VALUE!

Funksioni SEARCH() nuk është i ndjeshëm ndaj shkronjave të vogla. Për një kërkim të ndjeshëm ndaj rasteve, përdorni funksionin FIND().

Shembuj

Formula =SEARCH("për";"Kanali i parë") do të kthehet 8 sepse letër teështë në pozitën e 8-të nga e majta.

Lëreni në një qelizë A2 rreshti i futur Kanali i parë është më i miri. Formula =SEARCH(CHAR(32),A2) do të kthehet 7, sepse karakteri i hapësirës (kodi 32) është në pozicionin e 7-të.

Formula =SEARCH("#???#";"Artikulli #123# ID") do të kërkojë në vargun " Neni #123# ID" është një sekuencë me 5 karaktere që fillon dhe mbaron me #.

Për të gjetur pozicionin e shfaqjes së dytë të shkronjës "a" në vargun "mami lau kornizën" përdorni formulën =SEARCH("a","nena lau kuadrin",SEARCH("a","nena lau kuadrin")+1). Për të përcaktuar nëse ka një paraqitje të tretë të shkronjës "m" në vargun "mami lau kornizën" përdorni formulën =IF(DLSTR(SUBSTITUTE("mama lau kuadrin";"m";"";3))=DLSTR("nena lau kuadrin");"Nuk ka dukuri te trete te m";"Ka nje te trete shfaqja e m")

Formula =SEARCH("thesar?";"raport") do të kthejë 3, d.m.th. fjala "raportim" përmban një fjalë me 5 shkronja, 4 të parat prej të cilave thesar(duke filluar me shkronjën e tretë të fjalës raporti).

Funksioni FIND () kundrejt SEARCH ()

Funksioni FIND() është i ndjeshëm ndaj shkronjave të vogla dhe nuk lejon shkronja të egra. Për kërkime të pandjeshme ndaj rasteve, si dhe për kërkime duke përdorur shkronjat e egra përdorni funksionin SEARCH().

Lidhja me funksionet LEFT() , RIGHT() dhe MID().

Funksioni SEARCH() mund të përdoret në lidhje me funksionet LEFT() , RIGHT() dhe MID().

Për shembull, në një qelizë A2 përmban mbiemrin dhe emrin "Ivanov Ivan", pastaj formulën =LEFTCHAR(A2,SEARCH(CHAR(32),A2)-1) do të nxjerrë mbiemrin, dhe =DJATHTA(A2,LONG(A2)-KËRKONI(CHAR(32),A2))- emri. Nëse ka më shumë se një hapësirë ​​midis emrit dhe mbiemrit, atëherë që formulat e mësipërme të funksionojnë, përdorni funksionin TRIM().

Qëllimi kryesor i programit Excel office është zbatimi i llogaritjeve. Dokumenti i këtij programi (Libri) mund të përmbajë shumë fletë me tabela të gjata të mbushura me numra, tekst ose formula. Kërkimi i shpejtë i automatizuar ju lejon të gjeni qelizat e nevojshme në to.

Kërkim i thjeshtë

Për të kërkuar një vlerë në një spreadsheet Excel, në skedën Home, hapni listën rënëse të mjetit Find and Replace dhe klikoni në artikullin Find. I njëjti efekt mund të merret duke përdorur shkurtoren e tastierës Ctrl + F.

Në rastin më të thjeshtë, në dritaren Find and Replace që shfaqet, vendosni vlerën që kërkoni dhe klikoni Find All.

Siç mund ta shihni, rezultatet e kërkimit janë shfaqur në fund të kutisë së dialogut. Vlerat e gjetura janë të nënvizuara me të kuqe në tabelë. Nëse në vend të "Gjej të gjitha" klikoni "Gjej Tjetër", atëherë së pari do të kërkohet qeliza e parë me këtë vlerë dhe kur të klikoni përsëri, e dyta.

Teksti kërkohet në të njëjtën mënyrë. Në këtë rast, teksti i kërkimit shtypet në shiritin e kërkimit.

Nëse të dhënat ose teksti nuk kërkohet në të gjithë tabelën e Excel-it, atëherë së pari duhet të zgjidhet zona e kërkimit.

kërkim i avancuar

Supozoni se doni të gjeni të gjitha vlerat midis 3000 dhe 3999. Në këtë rast, do të shkruani 3??? në vargun e kërkimit. Shenja "?" zëvendëson ndonjë tjetër.

Duke analizuar rezultatet e kërkimit të kryer, mund të vërehet se së bashku me 9 rezultatet e sakta, programi prodhoi edhe të papritura, të nënvizuara me të kuqe. Ato shoqërohen me praninë e numrit 3 në një qelizë ose formulë.

Ju mund të jeni të kënaqur me shumicën e rezultateve të marra, duke injoruar ato të gabuara. Por funksioni i kërkimit në Excel 2010 është në gjendje të funksionojë shumë më saktë. Ky është mjeti Options në kutinë e dialogut.

Duke klikuar "Opsionet", përdoruesi mund të kryejë një kërkim të avancuar. Para së gjithash, le t'i kushtojmë vëmendje artikullit "Zona e kërkimit", në të cilën vlera "Formulat" është vendosur si parazgjedhje.

Kjo do të thotë që kërkimi është kryer, përfshirë në ato qeliza ku nuk ka një vlerë, por një formulë. Prania e numrit 3 në to dha tre rezultate të pasakta. Nëse zgjidhni "Vlerat" si shtrirje të kërkimit, atëherë do të kërkohen vetëm të dhënat dhe rezultatet e pasakta të lidhura me qelizat e formulës do të zhduken.

Për të hequr qafe rezultatin e vetëm të pasaktë të mbetur në rreshtin e parë, në dritaren e kërkimit të avancuar, zgjidhni artikullin "E gjithë qeliza". Pas kësaj, rezultati i kërkimit bëhet 100% i saktë.

Një rezultat i tillë mund të arrihet duke zgjedhur menjëherë artikullin "E gjithë qeliza" (madje duke lënë vlerën "Formulat" në "Zonën e kërkimit").

Tani le të kthehemi te artikulli "Kërko".

Nëse në vend të paracaktuar "Në fletë" zgjidhni vlerën "Në libër", atëherë nuk ka nevojë të jetë në fletën e qelizave që kërkoni. Pamja e ekranit tregon se përdoruesi filloi kërkimin ndërsa ishte në një fletë bosh 2.

Artikulli tjetër në dritaren e kërkimit të avancuar është "Shfleto", i cili ka dy kuptime. Si parazgjedhje, është vendosur në "sipas rreshtave", që do të thotë sekuenca e skanimit të qelizave sipas rreshtave. Zgjedhja e një vlere tjetër - "sipas kolonave", do të ndryshojë vetëm drejtimin e kërkimit dhe sekuencën e rezultateve.

Kur kërkoni në dokumentet e Microsoft Excel, mund të përdorni gjithashtu një shkronjë tjetër - "*". Nëse konsiderohet "?" nënkuptonte çdo karakter, atëherë "*" zëvendëson jo një, por çdo numër karakteresh. Më poshtë është një pamje e një kërkimi për Luizianën.

Ndonjëherë kërkimi duhet të jetë i ndjeshëm ndaj rasteve. Nëse fjala luiziana është me shkronjë të madhe, atëherë rezultatet e kërkimit nuk do të ndryshojnë. Por nëse zgjidhni "Case sensitive" në dritaren e kërkimit të avancuar, kërkimi do të jetë i pasuksesshëm. Programi do t'i konsiderojë fjalët Luiziana dhe luiziana të ndryshme, dhe, natyrisht, nuk do të gjejë të parën prej tyre.

Kërko varietetet

Gjetja e ndeshjeve

Ndonjëherë është e nevojshme të zbulohen vlera të dyfishta në një tabelë. Për të kërkuar për përputhje, së pari duhet të nënvizoni gamën e kërkimit. Më pas, në të njëjtën skedë Home, në grupin Styles, hapni mjetin e Formatimit të Kushtëzuar. Më pas, zgjidhni në mënyrë sekuenciale artikujt "Rregullat e përzgjedhjes së qelizave" dhe "Dublikoni vlerat".

Rezultati tregohet në pamjen e mëposhtme të ekranit.

Nëse është e nevojshme, përdoruesi mund të ndryshojë ngjyrën e ekranit vizual të qelizave të përputhura.

Filtrimi

Një lloj tjetër kërkimi është filtrimi. Supozoni se përdoruesi dëshiron të gjejë vlera numerike në kolonën B midis 3000 dhe 4000.

Siç mund ta shihni, shfaqen vetëm rreshtat që plotësojnë kushtin e futur. Të gjithë të tjerët u fshehën përkohësisht. Për t'u kthyer në gjendjen fillestare, përsëritni hapin 2.

Opsione të ndryshme kërkimi u konsideruan duke përdorur si shembull Excel 2010. Si të kërkoni në Excel të versioneve të tjera? Ka një ndryshim në kalimin në filtrim në versionin 2003. Në menynë "Data" zgjidhni me radhë komandat "Filter", "Autofilter", "Condition" dhe "User AutoFilter".

Video: Kërko në një spreadsheet Excel

Gjithashtu artikuj rreth punës me tabela në Excel:

• Formatoni tabelat në Excel
• Krijimi i tabelave në excel
• Krijimi i një tabele kryesore në excel
• Si të zgjidhni të gjithë tabelën në Excel?

Mes mijëra rreshtave dhe dhjetëra kolonave të të dhënave manualisht në një tabelë Excel, është pothuajse e pamundur të gjesh diçka. Mundësia e vetme është të përdorni një lloj funksioni kërkimi, dhe më pas do të shikojmë se si të kërkoni në një spreadsheet Excel.

Për të kërkuar të dhëna në një spreadsheet Excel, duhet të përdorni artikullin e menysë "Gjej dhe Zgjidh" në skedën "Home", në të cilën duhet të zgjidhni opsionin "Gjej" ose të përdorni kombinimin e tastit "Ctrl + F" për të thirrur. atë.

Për shembull, le të përpiqemi të gjejmë numrin e kërkuar midis të dhënave në tabelën tonë, pasi gjatë kërkimit të numrave duhet të merren parasysh disa hollësi të kërkimit. Ne do të kërkojmë numrin "10" në tabelën Excel.

Pasi të zgjidhni artikullin e dëshiruar të menusë në kutinë e kërkimit që shfaqet, vendosni vlerën e dëshiruar. Ne kemi dy opsione për të kërkuar vlerat në tabelën Excel, kjo është që të gjejmë të gjitha ndeshjet menjëherë duke klikuar butonin "Gjej të gjitha" ose të shikojmë menjëherë secilën qelizë të gjetur duke klikuar çdo herë butonin "Gjej Tjetër". Kur përdorni butonin "Gjej tjetër", duhet të merrni parasysh edhe vendndodhjen aktuale të qelizës aktive, pasi kërkimi do të fillojë nga ky pozicion.

Le të përpiqemi t'i gjejmë të gjitha vlerat menjëherë, ndërsa gjithçka e gjetur do të renditet në dritaren nën cilësimin e kërkimit. Nëse i lini të gjitha cilësimet e paracaktuara, rezultati i kërkimit nuk do të jetë saktësisht ai që prisnim.

Për të kërkuar saktë të dhëna në një tabelë Excel, klikoni butonin "Opsionet" dhe konfiguroni zonën e kërkimit. Tani vlera e dëshiruar kërkohet edhe në formulat e përdorura në qelizat për llogaritjet. Ne duhet të specifikojmë kërkimin vetëm në vlera dhe, nëse dëshironi, mund të specifikoni edhe formatin e të dhënave që kërkoni.

Kur kërkoni për fjalë në një tabelë Excel, duhet gjithashtu të merrni parasysh të gjitha këto hollësi dhe, për shembull, mund të merrni edhe rastin e shkronjave.

Dhe së fundi, le të shohim se si të kërkojmë të dhëna në Excel vetëm në zonën e kërkuar të fletës. Siç mund ta shihni nga shembulli ynë, vlera e dëshiruar "10" gjendet në të gjitha kolonat e të dhënave menjëherë. Nëse keni nevojë të gjeni këtë vlerë, të themi, vetëm në kolonën e parë, duhet të zgjidhni këtë kolonë ose ndonjë gamë vlerash në të cilat dëshironi të kërkoni, dhe më pas të filloni kërkimin.

Kolona jonë e parë ka vetëm dy vlera të barabarta me '10', kështu që vetëm dy rezultate kërkimi duhet të shfaqen në listë kur përdorni opsionin Gjej të gjitha.

Në dokumentet e Microsoft Excel që përbëhen nga një numër i madh fushash, shpesh është e nevojshme të gjenden të dhëna specifike, një emër rreshti, etj. Është shumë e papërshtatshme kur duhet të shikoni nëpër një numër të madh rreshtash për të gjetur fjalën ose shprehjen e duhur. Kërkimi i integruar i Microsoft Excel do të ndihmojë në kursimin e kohës dhe nervave. Le të shohim se si funksionon dhe si ta përdorim atë.

Funksioni i kërkimit në Excel

Funksioni i kërkimit në Microsoft Excel ofron mundësinë për të gjetur tekstin e dëshiruar ose vlerat numerike përmes dritares "Gjeni dhe zëvendësoni". Përveç kësaj, aplikacioni ka mundësinë e kërkimit të avancuar të të dhënave.

Metoda 1: kërkim i thjeshtë

Një kërkim i thjeshtë i të dhënave në Excel ju lejon të gjeni të gjitha qelizat që përmbajnë një grup karakteresh të futura në kutinë e kërkimit (shkronja, numra, fjalë, etj.) pa ndjeshmëri ndaj shkronjave të vogla.

1. Duke qenë në skedën "Home", klikoni në butonin "Gjeni dhe zgjidhni", i cili ndodhet në shiritin në bllokun e veglave "Redaktimi". Në menunë që shfaqet, zgjidhni artikullin "Gjeni ...". Në vend të këtyre veprimeve, thjesht mund të shkruani kombinimin e tastit Ctrl + F në tastierë.
2. Pasi të keni lundruar te artikujt e duhur në shirit ose të keni shtypur një kombinim të "çelësave të nxehtë", dritarja "Gjej dhe zëvendëso" do të hapet në skedën "Gjej". Ne kemi nevojë për të. Në fushën "Gjeni", vendosni fjalën, simbolet ose shprehjet me të cilat do të kërkojmë. Klikoni në butonin "Gjej Tjetër" ose në butonin "Gjej të Gjithë".
3. Kur klikoni në butonin "Gjej Tjetër", kalojmë në qelizën e parë që përmban grupet e karaktereve të futura. Vetë qeliza bëhet aktive.

   Kërkimi dhe dalja e rezultateve kryhet rresht pas rreshti. Së pari, të gjitha qelizat e rreshtit të parë përpunohen. Nëse nuk gjenden të dhëna që përputhen me kushtin, programi fillon të shikojë në rreshtin e dytë, e kështu me radhë, derisa të gjejë një rezultat të kënaqshëm.

   Karakteret e kërkimit nuk duhet të jenë elementë të pavarur. Pra, nëse shprehja "e drejtë" jepet si pyetës, atëherë të gjitha qelizat që përmbajnë një grup të caktuar vijues karakteresh, qoftë edhe brenda një fjale, do të paraqiten në dalje. Për shembull, pyetja përkatëse në këtë rast do të ishte fjala "Në të djathtë". Nëse futni numrin "1" në motorin e kërkimit, atëherë përgjigja do të përfshijë qelizat që përmbajnë, për shembull, numrin "516".

   Për të kaluar te rezultati tjetër, klikoni përsëri butonin Gjeni Tjetrin.

   Mund të vazhdoni në këtë mënyrë derisa të fillojë shfaqja e rezultateve në një rreth të ri.

4. Nëse, kur filloni procedurën e kërkimit, klikoni në butonin "Gjej të gjitha", të gjitha rezultatet e kërkimit do të paraqiten në formën e një liste në fund të dritares së kërkimit. Kjo listë përmban informacione për përmbajtjen e qelizave me të dhëna që plotësojnë kërkesën e kërkimit, adresën e vendndodhjes së tyre, si dhe fletën dhe librin të cilit i referohen. Për të shkuar te ndonjë nga rezultatet e lëshimit, thjesht klikoni mbi të me butonin e majtë të miut. Pas kësaj, kursori do të hidhet në qelizën Excel që përdoruesi klikoi.

Metoda 2: Kërko brenda një gamë të caktuar qelizash

Nëse keni një tabelë mjaft të madhe, atëherë në këtë rast nuk është gjithmonë e përshtatshme të kërkoni të gjithë fletën, sepse rezultatet e kërkimit mund të përmbajnë një numër të madh rezultatesh që nuk nevojiten në një rast të veçantë. Ekziston një mënyrë për të kufizuar hapësirën e kërkimit vetëm në një gamë të caktuar qelizash.

1. Zgjidhni zonën e qelizave në të cilën duam të kërkojmë.
2. Ne shtypim kombinimin e tastit Ctrl + F në tastierë, pas së cilës do të fillojë dritarja e njohur "Gjej dhe Zëvendëso". Veprimet e mëtejshme janë saktësisht të njëjta si në metodën e mëparshme. Dallimi i vetëm do të jetë se kërkimi kryhet vetëm në gamën e specifikuar të qelizave.

Metoda 3: Kërkim i avancuar

Siç u përmend më lart, gjatë një kërkimi normal, absolutisht të gjitha qelizat që përmbajnë një grup të njëpasnjëshëm karakteresh kërkimi në çdo formë, pavarësisht nga rasti, futen në rezultatet e çështjes.

Për më tepër, jo vetëm përmbajtja e një qelize të veçantë mund të hyjë në dalje, por edhe adresa e elementit të cilit i referohet. Për shembull, qeliza E2 përmban një formulë që është shuma e qelizave A4 dhe C3. Kjo shumë është 10, dhe ky është numri i shfaqur në qelizën E2. Por, nëse vendosim numrin "4" në kërkim, atëherë e njëjta qelizë E2 do të jetë ndër rezultatet e daljes. Si mund të ndodhte kjo? Thjesht qeliza E2 përmban adresën e qelizës A4 si formulë, e cila thjesht përfshin numrin e dëshiruar 4.

Por si të ndërpriten rezultate të tilla dhe të tjera dukshëm të papranueshme të kërkimit? Pikërisht për këto qëllime ekziston një kërkim i avancuar në Excel.

1. Pasi të keni hapur dritaren "Gjeni dhe Zëvendësoni" në çdo mënyrë të përshkruar më sipër, klikoni në butonin "Opsionet".
2. Një numër mjetesh shtesë për menaxhimin e kërkimit shfaqen në dritare. Si parazgjedhje, të gjitha këto mjete janë në të njëjtën gjendje si në një kërkim normal, por ju mund të bëni rregullime nëse është e nevojshme.

   Si parazgjedhje, funksionet "Case sensitive" dhe "Të gjitha qelizat" janë të çaktivizuara, por nëse kontrollojmë kutitë pranë artikujve përkatës, atëherë në këtë rast, rasti i futur dhe përputhja e saktë do të merren parasysh gjatë gjenerimit të rezultatit. Nëse futni një fjalë me shkronjë të vogël, atëherë në rezultatet e kërkimit, qelizat që përmbajnë drejtshkrimin e kësaj fjale me shkronjë të madhe, siç do të ishte si parazgjedhje, nuk do të përfshihen më. Përveç kësaj, nëse funksioni "Të gjitha qelizat" është i aktivizuar, atëherë në dalje do të shtohen vetëm elementë që përmbajnë emrin e saktë. Për shembull, nëse vendosni pyetjen e kërkimit "Nikolaev", atëherë qelizat që përmbajnë tekstin "Nikolaev A.D." nuk do të shtohen në rezultatet e kërkimit.

   Si parazgjedhje, kërkimi kryhet vetëm në fletën aktive të Excel. Por, nëse transferoni parametrin "Kërko" në pozicionin "Në libër", atëherë kërkimi do të kryhet në të gjitha fletët e skedarit të hapur.

   Në opsionin Browse, mund të ndryshoni drejtimin e kërkimit. Si parazgjedhje, siç u përmend më lart, kërkimi kryhet me radhë, rresht pas rreshti. Duke lëvizur çelësin në pozicionin "Nga kolonat", mund të vendosni rendin në të cilin gjenerohen rezultatet e kërkimit, duke filluar nga kolona e parë.

   Në kolonën "Zona e kërkimit" përcaktohet se në cilët elementë të veçantë kryhet kërkimi. Si parazgjedhje, këto janë formula, domethënë të dhënat që, kur klikoni në një qelizë, shfaqen në shiritin e formulave. Mund të jetë një fjalë, një numër ose një referencë qelize. Në të njëjtën kohë, programi, kur kryen një kërkim, sheh vetëm lidhjen, dhe jo rezultatin. Ky efekt u diskutua më lart. Për të kërkuar saktësisht sipas rezultateve, sipas të dhënave që shfaqen në qelizë dhe jo në shiritin e formulës, duhet të zhvendosni çelësin nga pozicioni "Formulat" në pozicionin "Vlerat". Përveç kësaj, është e mundur të kërkoni me shënime. Në këtë rast, çelësi riorganizohet në pozicionin "Shënime".

   Ju mund të specifikoni kërkimin edhe më saktë duke klikuar në butonin "Format".

   Kjo hap dritaren Format Cells. Këtu mund të vendosni formatin e qelizave që do të marrin pjesë në kërkim. Mund të vendosni kufizime në formatin e numrave, shtrirjen, fontin, kufirin, hijezimin dhe mbrojtjen, një nga këto opsione ose një kombinim i tyre.

   Nëse dëshironi të përdorni formatin e një qelize specifike, atëherë në fund të dritares, klikoni në butonin "Përdorni formatin e kësaj qelize ...".

   Pas kësaj, shfaqet një mjet në formën e një pikatore. Me të, ju mund të zgjidhni qelizën formatin e së cilës do të përdorni.

   Pasi të jetë konfiguruar formati i kërkimit, klikoni në butonin "OK".

   Ka raste kur nuk duhet të kërkoni për një frazë specifike, por për të gjetur qeliza që përmbajnë fjalë kërkimi në çdo rend, edhe nëse ato janë të ndara me fjalë dhe simbole të tjera. Pastaj këto fjalë duhet të theksohen në të dy anët me shenjën "*". Tani rezultatet e kërkimit do të shfaqin të gjitha qelizat në të cilat ndodhen këto fjalë në çdo mënyrë.

3. Pasi të jenë vendosur cilësimet e kërkimit, duhet të klikoni në butonin "Gjej të gjitha" ose "Gjej tjetër" për të shkuar te rezultatet e kërkimit.

Siç mund ta shihni, programi Excel është një grup mjaft i thjeshtë, por në të njëjtën kohë shumë funksional i mjeteve të kërkimit. Për të bërë kërcitjen më të thjeshtë, mjafton të telefononi dritaren e kërkimit, të vendosni një pyetje në të dhe të klikoni në butonin. Por, në të njëjtën kohë, ekziston mundësia e vendosjes së një kërkimi individual me një numër të madh parametrash të ndryshëm dhe cilësime shtesë.

Jemi të lumtur që mundëm t'ju ndihmojmë të zgjidhni problemin.

Bëni pyetjen tuaj në komente, duke përshkruar në detaje thelbin e problemit. Ekspertët tanë do të përpiqen të përgjigjen sa më shpejt që të jetë e mundur.

A ju ndihmoi ky artikull?

Një pjesë e konsiderueshme e detyrave që zgjidhen duke përdorur spreadsheets supozojnë se përdoruesi tashmë ka të paktën disa të dhëna fillestare për të gjetur rezultatin e dëshiruar. Sidoqoftë, Excel 2010 ka mjetet e nevojshme me të cilat mund ta zgjidhni këtë problem përkundrazi - të zgjidhni të dhënat e duhura për të marrë rezultatin e dëshiruar.

"Kërkimi për një zgjidhje" është një nga mjetet e tilla që është më i përshtatshëm për "problemet e optimizimit". Dhe nëse nuk ju është dashur ta përdorni më parë, tani është koha për ta rregulluar atë.

Pra - fillojmë duke instaluar këtë shtesë (pasi nuk do të shfaqet më vete). Për fat të mirë, tani kjo mund të bëhet mjaft thjesht dhe shpejt - hapni menunë "Shërbimi", dhe tashmë në të "Shtesa"

Mbetet vetëm në kolonën "Menaxhimi" për të treguar "Excel Add-ins" dhe më pas klikoni butonin "Shko".

Pas këtij veprimi të thjeshtë, butoni për aktivizimin e "Kërko një zgjidhje" do të shfaqet në "Të dhënat". Siç tregohet në foto

Le të shohim se si Excel 2010 për gjetjen e zgjidhjeve përdoret saktë me disa shembuj të thjeshtë.

Shembulli një.

Le të themi se ju jeni drejtuesi i një departamenti të madh prodhimi dhe ju duhet të shpërndani siç duhet shpërblimet për punonjësit. Le të themi se shuma totale e shpërblimeve është 100,000 rubla, dhe është e nevojshme që shpërblimet të jenë proporcionale me pagat.

Kjo do të thotë, tani duhet të zgjedhim koeficientin e saktë të proporcionalitetit në mënyrë që të përcaktojmë madhësinë e bonusit në lidhje me pagën.

Para së gjithash, është e nevojshme të përpiloni shpejt (nëse nuk ekziston tashmë) një tabelë ku do të ruhen formulat dhe të dhënat fillestare, sipas të cilave do të jetë e mundur të merret rezultati i dëshiruar. Për ne, ky rezultat është vlera totale e primit. Dhe tani, vëmendje - qeliza e synuar C8 duhet të lidhet me qelizën e dëshiruar të ndryshueshme në adresën E2 duke përdorur formula. Kjo është kritike. Në shembull, ne i lidhim ato duke përdorur formula të ndërmjetme, të cilat janë përgjegjëse për llogaritjen e bonusit për secilin punonjës (С2: С7).

Tani mund të aktivizoni "Kërkimi për zgjidhje". Do të hapet një dritare e re në të cilën duhet të specifikojmë parametrat e nevojshëm.

Nën "" është qeliza jonë e synuar. Ajo mund të jetë vetëm një.

"" janë opsione të mundshme optimizimi. Në total, mund të zgjidhni vlerat e mundshme "Maksimale", "Minimum" ose "Specifik". Dhe nëse keni nevojë për një vlerë specifike, atëherë duhet ta specifikoni në kolonën përkatëse.

"" - mund të ketë disa qeliza për të ndryshuar (një gamë e tërë ose adresa të specifikuara veçmas). Në fund të fundit, është me ta që Excel do të funksionojë, duke renditur opsionet në mënyrë që të merret vlera e specifikuar në qelizën e synuar.

"" - Nëse keni nevojë të vendosni kufizime, atëherë duhet të përdorni butonin "Shto", por ne do ta shqyrtojmë këtë pak më vonë.

"" - butoni për kalimin në llogaritjet interaktive bazuar në programin që kemi specifikuar.

Por tani le të kthehemi te aftësia për të ndryshuar detyrën tonë duke përdorur butonin "Shto". Kjo fazë është mjaft e përgjegjshme (jo më pak se ndërtimi i formulave), pasi është kufizimi që ju lejon të merrni rezultatin e saktë në dalje. Këtu gjithçka bëhet sa më lehtë që të jetë e mundur, kështu që ju mund t'i vendosni ato jo vetëm për të gjithë gamën menjëherë, por edhe për qeliza të caktuara.

Për ta bërë këtë, mund të përdorni një numër shenjash specifike (dhe të njohura për të gjithë përdoruesit e Excel 2010) "=", ">=", "

Ne kemi një tabelë që regjistron vëllimet e shitjeve të mallrave të caktuara në muaj të ndryshëm. Është e nevojshme të gjenden të dhënat në tabelë, dhe kriteret e kërkimit do të jenë titujt e rreshtave dhe kolonave. Por kërkimi duhet të kryhet veçmas në vargun e rreshtit ose kolonës. Kjo do të thotë, do të përdoret vetëm një nga kriteret. Prandaj, funksioni INDEX nuk mund të përdoret këtu, por nevojitet një formulë e veçantë.

Gjetja e vlerave në një tabelë Excel

Për të zgjidhur këtë problem, ne ilustrojmë një shembull në një tabelë skematike që plotëson kushtet e përshkruara më sipër.

Fletë me një tabelë për të kërkuar vlerat vertikalisht dhe horizontalisht:

Mbi tabelën në vetvete është një vijë me rezultatet. Në qelizën B1 vendosim kriterin për pyetjen e kërkimit, domethënë titullin e kolonës ose emrin e rreshtit. Dhe në qelizën D1, formula e kërkimit duhet të kthejë rezultatin e llogaritjes së vlerës përkatëse. Pas kësaj, formula e dytë do të funksionojë në qelizën F1, e cila tashmë do të përdorë vlerat e qelizave B1 dhe D1 si kriter për gjetjen e muajit përkatës.

Gjetja e një vlere në një rresht Excel

Tani do të zbulojmë se në çfarë vëllimi maksimal dhe në cilin muaj ka pasur shitjen maksimale të Good 4.

Për të kërkuar sipas kolonave:

U gjet në cilin muaj dhe cila ishte shitja më e madhe e produktit 4 gjatë dy tremujorëve.

Parimi i funksionimit të formulës për gjetjen e një vlere në një rresht Excel:

Argumenti i parë i funksionit VLOOKUP (Vertical Lookup) përmban një referencë për qelizën ku ndodhet kriteri i kërkimit. Argumenti i dytë specifikon gamën e qelizave për të kërkuar gjatë kërkimit. Argumenti i tretë i funksionit VLOOKUP duhet të përmbajë numrin e kolonës nga i cili duhet marrë vlera kundrejt rreshtit me emrin Produkt 4. Por meqenëse nuk e dimë këtë numër paraprakisht, ne përdorim funksionin COLUMN për të krijuar një grup kolonash numrat për diapazonin B4:G15.

Kjo lejon funksionin VLOOKUP të mbledhë një grup të tërë vlerash. Si rezultat, memoria ruan të gjitha vlerat përkatëse për secilën kolonë në rreshtin Pika 4 (domethënë: 360; 958; 201; 605; 462; 832). Pas kësaj, funksioni MAX duhet vetëm të marrë numrin maksimal nga ky grup dhe ta kthejë atë si vlerë për qelizën D1, si rezultat i llogaritjes së formulës.

Siç mund ta shihni, dizajni i formulës është i thjeshtë dhe konciz. Bazuar në të, ju mund të gjeni tregues të tjerë për një produkt specifik në një mënyrë të ngjashme. Për shembull, vlera minimale ose mesatare e shitjeve duke përdorur funksionet MIN ose AVERAGE për këtë. Asgjë nuk ju pengon të aplikoni këtë skelet formule duke përdorur funksione më komplekse për të zbatuar analizën më komode të raportit të shitjeve.

Si të merrni titujt e kolonave nga fillimi i një qelize?

Për shembull, sa efektivisht kemi shfaqur muajin në të cilin ka pasur një shitje maksimale duke përdorur formulën e dytë. Nuk është e vështirë të vërehet se në formulën e dytë kemi përdorur skeletin e formulës së parë pa funksionin MAX. Struktura kryesore e formulës: VLOOKUP(B1;A5:G14;COLUMN(B5:G14);0). Ne kemi zëvendësuar funksionin MAX me MATCH, i cili përdor vlerën e marrë nga formula e mëparshme në argumentin e parë. Tani vepron si kriter për gjetjen e muajit. Dhe si rezultat, funksioni MATCH na kthen numrin e kolonës 2, ku ndodhet vlera maksimale e vëllimit të shitjeve për produktin 4. Pas kësaj, funksioni INDEX përfshihet në punë, i cili kthen vlerën me numrin të periudhës dhe kolonës nga diapazoni i përcaktuar në argumentet e tij. Meqenëse kemi një numër kolone 2, dhe numri i rreshtit në diapazonin ku ruhen emrat e muajve në çdo rast do të jetë 1. Atëherë na mbetet të përdorim funksionin INDEX për të marrë vlerën përkatëse nga diapazoni B4 : G4 - Shkurt (muaji i dytë).



Gjetja e një vlere në një kolonë Excel

Varianti i dytë i detyrës do të jetë një kërkim në tabelë duke përdorur emrin e muajit si kriter. Në raste të tilla, ne duhet të ndryshojmë skeletin e formulës sonë: funksioni VLOOKUP zëvendësohet nga GLOOKUP dhe funksioni COLUMN zëvendësohet me ROW.

Kjo do të na lejojë të zbulojmë se sa dhe çfarë produkti ka pasur shitjen maksimale në një muaj të caktuar.

Për të gjetur se cili produkt kishte vëllimin më të lartë të shitjeve në një muaj të caktuar:

Parimi i formulës për gjetjen e një vlere në një kolonë Excel:

Në argumentin e parë të funksionit HLOOKUP (Pamje horizontale), ne specifikojmë një lidhje në një qelizë me një kriter kërkimi. Argumenti i dytë përmban një referencë për gamën e tabelës që shikohet. Argumenti i tretë gjenerohet nga funksioni STRING, i cili krijon një grup me 10 elementë të numrave të rreshtave në memorie. Meqenëse në pjesën tabelare kemi 10 rreshta.

Më pas, funksioni HLOOKUP, duke përdorur çdo numër rreshti me radhë, krijon një grup të vlerave përkatëse të shitjeve nga tabela për një muaj të caktuar (qershor). Më pas, funksioni MAX duhet vetëm të zgjedhë vlerën maksimale nga ky grup.

KUJDES! Kur përdorni skeletin e formulës për detyra të tjera, gjithmonë kushtojini vëmendje argumenteve të dytë dhe të tretë të funksionit të kërkimit HLOOKUP. Numri i rreshtave të mbuluar në diapazonin e specifikuar në argument duhet të përputhet me numrin e rreshtave në tabelë. Dhe gjithashtu numërimi duhet të fillojë nga rreshti i dytë!

Përshëndetje miq. Sa shpesh duhet të kërkoni një përputhje në një tabelë Excel për disa vlera? Për shembull, duhet të gjeni adresën e një personi në drejtori, ose në listën e çmimeve - çmimin e mallrave. Nëse ndodhin detyra të tilla, ky postim është për ju!

Këto procedura i kryej çdo ditë dhe pa funksionet e përshkruara më poshtë, do ta kisha vërtet të vështirë. Merrni parasysh dhe vendosni ato në punë!

Funksionet e kërkimit të fletëllogaritës në Excel, VLOOKUP dhe HLOOKUP

Roli i këtyre funksioneve në jetën e një përdoruesi të zakonshëm është i vështirë të mbivlerësohet. Tani mund të gjeni lehtësisht një hyrje të përshtatshme në tabelën e të dhënave dhe të ktheni vlerën që korrespondon me të.

Sintaksa e funksionit VLOOKUP është: =VLOOKUP( kërkim_vlera; tabela_kërkuese; dalje_numri_kolona; [lloji_përputhje]). Merrni parasysh argumentet:

• Vlera e dëshiruarështë vlera që duhet kërkuar. Ky është një argument i kërkuar;
• Tabela e kërkimit- grupi i qelizave në të cilat do të jetë kërkimi. Kolona me vlerat e dëshiruara duhet të jetë e para në këtë grup. Ky është gjithashtu një argument i kërkuar;
• Numri i kolonës së daljesështë numri rendor i kolonës (duke filluar nga e para në grup), nga e cila funksioni do të shfaq të dhënat nëse vlerat e kërkuara përputhen. Argumenti i kërkuar;
• Lloji i hartës- zgjidhni "1" (ose "E VËRTETË") për një përputhje jo të rreptë, "0" ("FALSE") - për një përputhje të plotë. Argumenti është fakultativ, nëse ai hiqet, kërkimi do të kryhet ndeshje jo e rreptë.

Gjetja e një përputhje të saktë me VLOOKUP

Le të shohim një shembull se si funksionon funksioni VLOOKUP kur lloji i përputhjes është FALSE, duke kërkuar për një përputhje të saktë. Grupi B5:E10 përmban aktivet fikse të një kompanie të caktuar, vlerën e tyre kontabël, numrin e inventarit dhe vendndodhjen. Qeliza B2 përmban emrin për të cilin duhet të gjeni numrin e inventarit në tabelë dhe ta vendosni në qelizën C2.

Funksioni VLOOKUP në Excel

Le të shkruajmë formulën: =VLOOKUP(B2;B5:E10;3;FALSE) .

Këtu, argumenti i parë tregon se në tabelë duhet kërkuar vlerën nga qeliza B2, d.m.th. fjala faks. Argumenti i dytë thotë se tabela e kërkimit është në intervalin B5:E10, dhe duhet të kërkoni fjalën "Fax" në kolonën e parë, d.m.th. në grup B5:B10. Argumenti i tretë i tregon programit se rezultati i llogaritjes gjendet në kolonën e tretë të grupit, d.m.th. D5: D10 . Argumenti i katërt është FALSE, d.m.th. kërkohet ndeshje e plotë.

Dhe kështu, funksioni do të marrë vargun "Fax" nga qeliza B2 dhe do ta kërkojë atë në grupin B5:B10 nga lart poshtë. Pasi të gjendet një përputhje (rreshti 8), funksioni do të kthejë vlerën përkatëse nga kolona D, d.m.th. përmbajtja D8 . Kjo është pikërisht ajo që na duhej, problemi është zgjidhur.

Nëse vlera e dëshiruar nuk gjendet, funksioni do të kthehet.

Gjetja e një ndeshjeje të paqartë me VLOOKUP

Falë këtij opsioni në VLOOKUP, ne mund të shmangim formulat komplekse për të gjetur rezultatin e dëshiruar.

Vargu B5:C12 tregon normat e interesit për kreditë në varësi të shumës së kredisë. Në qelizën B2, ne tregojmë shumën e huasë dhe duam të marrim tarifën për një transaksion të tillë në C2. Detyra është e vështirë sepse shuma mund të jetë çdo dhe nuk ka gjasa të përputhet me ato të specifikuara në grup, kërkimi për një përputhje të saktë nuk është i përshtatshëm:

Pastaj shkruajmë formulën jo strikte të kërkimit: =VLOOKUP(B2,B5:C12,2,E VËRTETË). Tani, nga të gjitha të dhënat e paraqitura në kolonën B, programi do të kërkojë më të afërtin më të vogël. Domethënë për shumën 8000 do të zgjidhet vlera 5000 dhe do të shfaqet përqindja përkatëse.

VLOOKUP jo i rreptë në Excel

Që funksioni të funksionojë siç duhet, duhet të renditni kolonën e parë të tabelës në rend rritës. Përndryshe, mund të japë një rezultat të gabuar.

Funksioni HLOOKUP ka të njëjtën sintaksë si VLOOKUP, por e kërkon rezultatin në rreshta dhe jo në kolona. Kjo do të thotë, ai skanon tabelat jo nga lart poshtë, por nga e majta në të djathtë dhe shfaq numrin e rreshtit të dhënë, jo kolonën.

Gjetja e të dhënave duke përdorur funksionin BROWSE

Funksioni LOOKUP funksionon në mënyrë të ngjashme me VLOOKUP, por ka një sintaksë të ndryshme. Unë e përdor atë kur tabela e të dhënave përmban disa dhjetëra kolona dhe për të përdorur VLOOKUP, duhet të llogaritni gjithashtu numrin e kolonës së shfaqur. Në raste të tilla, funksioni VIEW e bën detyrën më të lehtë. Pra, sintaksa është: =SHFLETO( kërkim_vlera; Array_për_kërkim; grupi_për_shfaqje) :

• Vlera e dëshiruar– të dhënat ose një lidhje me të dhënat që do të kërkohen;
• Array për të kërkuar- një rresht ose kolonë në të cilën ne kërkojmë një vlerë të ngjashme. Ky grup duhet të renditet në rend rritës;
• Array për të shfaqur– një gamë që përmban të dhëna për shfaqjen e rezultateve. Natyrisht, duhet të jetë e njëjtë me madhësinë e grupit të kërkimit.

Me një shënim të tillë, ju po i jepni një referencë jo-relative grupit të rezultateve. Dhe ju tregoni drejtpërdrejt në të, d.m.th. nuk ka nevojë të llogaritet paraprakisht numri i kolonës së shfaqur. Ne përdorim funksionin VIEW në shembullin e parë për funksionin VLOOKUP (asetet fikse, numrat e inventarit): = KËRKONI (B2,B5:B10,D5:D10). Detyra u zgjidh me sukses!

Funksioni VIEW në Microsoft Excel

Kërko sipas koordinatave relative. Funksionet MATCH dhe INDEX

Një mënyrë tjetër për të kërkuar të dhëna është kombinimi i funksioneve MATCH dhe INDEX.

E para prej tyre përdoret për të kërkuar një vlerë në një grup dhe për të marrë numrin rendor të tij: MATCH( kërkim_vlera; grup_kërkuesish; [Lloji i hartës] ). Argumentet e funksionit:

• Vlera e dëshiruar- argumenti i kërkuar
• Grup kërkimi- një rresht ose kolonë në të cilën ne kërkojmë një përputhje. Argumenti i kërkuar
• Lloji i hartës- Specifikoni "0" për një përputhje të saktë, "1" për më të afërt më pak, "-1" për më të afërt më të madhe. Meqenëse funksioni kërkon nga fillimi i listës deri në fund, kur kërkoni për më të afërtin më të vogël, renditni kolonën e kërkimit në rend zbritës. Dhe kur kërkoni më shumë - renditeni në rend rritës.

Pozicioni i vlerës së kërkuar është gjetur, tani mund ta shfaqni në ekran duke përdorur funksionin INDEKSI( grup; Numri i linjes; [numri_kolona]) :

• varg- argumenti specifikon se nga cila grup qelizash dëshironi të zgjidhni një vlerë
• Numri i linjes– specifikoni numrin serial të rreshtit (duke filluar nga qeliza e parë e grupit) që dëshironi të shfaqni. Këtu mund të shkruani vlerën me dorë, ose të përdorni rezultatin e llogaritjes së një funksioni tjetër. Për shembull, KËRKONI.
• Numri i kolonës– argument opsional, i specifikuar nëse grupi përbëhet nga disa kolona. Nëse argumenti hiqet, formula përdor kolonën e parë të tabelës.

Tani le të kombinojmë këto funksione për të marrë rezultatin:

Funksionet MATCH dhe INDEX në Excel

Këto janë mënyrat për të kërkuar dhe shfaqur të dhënat në Excel. Më tej, ju mund t'i përdorni ato në llogaritjet, t'i përdorni në një prezantim, të kryeni operacione me ta, t'i specifikoni si argumente për funksionet e tjera, etj.

A e ndjeni se si njohuritë dhe aftësitë tuaja po rriten dhe forcohen? Atëherë mos u ndalni, vazhdoni të lexoni! Në postimin tjetër do të shqyrtojmë: do të jetë e vështirë dhe interesante!

Funksioni KËRKONI në Excel përdoret për të përcaktuar vendndodhjen e tekstit brenda një teksti dhe për të treguar pozicionin e saktë të tij.

Çfarë kthen funksioni

Funksioni kthen një vlerë numerike që tregon pozicionin fillestar të tekstit të kërkimit brenda një teksti tjetër. Pozicioni tregon numrin rendor të karakterit nga i cili fillon teksti i kërkimit.

Sintaksë

=KËRKO (gjeni_tekstin, brenda_tekstit, )- Versioni anglisht

=SEARCH(teksti_kërkues, teksti_kërkimi, [pozicioni_fillimi])- Versioni rus

Argumentet e funksionit

• gjeni_tekstin (teksti_kërkimi)– teksti ose vargu i tekstit që dëshironi të gjeni;
• brenda_teksti (shikimi_teksti)– tekstin brenda të cilit po kërkoni;
• ([pozicioni i_fillimit])është një vlerë numerike që tregon pozicionin nga i cili dëshironi të filloni kërkimin. Nëse ky argument nuk specifikohet, funksioni do të fillojë të kërkojë nga fillimi i tekstit.

informacion shtese

• Nëse pozicioni fillestar i kërkimit nuk është specifikuar, atëherë teksti kërkohet i pari i tekstit;
• Funksioni nuk është i ndjeshëm ndaj shkronjave të vogla. Nëse keni nevojë për ndjeshmëri ndaj rasteve, atëherë përdorni ;
• Funksioni mund të trajtojë shkronja të egra. Ekzistojnë tre karaktere të gërmave në Excel - ?, *, ~.
  • shenjë "?" – përputhet me çdo karakter të vetëm;
  • shenja "*" - përputhet me çdo karakter shtesë;
  • shenja "~" përdoret nëse duhet të gjeni vetë pikëpyetjen ose yll.
• Funksioni kthen një gabim nëse teksti i kërkimit nuk gjendet.

Shembuj të përdorimit të funksionit SEARCH në Excel

Shembull 1. Kërkimi i një fjale brenda një vargu teksti (që nga fillimi)

Shembulli i mësipërm tregon se kur kërkojmë fjalën "mirë" në tekstin "Mirëmëngjes", funksioni kthen vlerën "1", që korrespondon me pozicionin e fjalës "mirë" në tekstin "Mirëmëngjes".

Meqenëse funksioni është i pandjeshëm ndaj shkronjave të mëdha, nuk ka asnjë ndryshim se si e specifikojmë fjalën e kërkimit "lloj", nëse është "KIND", "Kind", "lloj", etj. funksioni do të kthejë të njëjtën vlerë.

Nëse keni nevojë të kryeni një kërkim të ndjeshëm ndaj rasteve, përdorni funksionin në Excel.

Shembulli 2. Ne po kërkojmë një fjalë brenda një vargu teksti (që tregon pozicionin fillestar të kërkimit)

Argumenti i tretë i funksionit tregon pozicionin rendor brenda tekstit nga i cili do të kryhet kërkimi. Në shembullin e mësipërm, funksioni kthen vlerën "1" kur kërkon fjalën "mirë" në tekstin "Mirëmëngjes", duke filluar kërkimin e tij nga pozicioni i parë.

Sidoqoftë, nëse i themi funksionit që kërkimi duhet të fillojë nga karakteri i dytë i tekstit "Mirëmëngjes", domethënë funksioni në këtë rast e sheh tekstin si "mirëmëngjes" dhe kërkon fjalën "mirë", atëherë. rezultati do të jetë një gabim.

Nëse nuk specifikoni një pozicion fillestar për kërkimin si argument, funksioni do të fillojë automatikisht kërkimin nga fillimi i tekstit.

Shembulli 3: Gjetja e një fjale kur ka shumë përputhje në tekst

Funksioni fillon kërkimin e tekstit nga një pozicion fillestar që ne mund ta specifikojmë si argument, ose do të fillojë kërkimin nga fillimi i tekstit automatikisht. Në shembullin e mësipërm, ne po kërkojmë fjalën "mirë" në tekstin "Mirëmëngjes" me pozicionin fillestar të kërkimit "1". Në këtë rast, funksioni kthen "1", pasi fjala e parë e gjetur "Mirë" fillon me karakterin e parë të tekstit.

Nëse i themi funksionit që të fillojë kërkimin, për shembull, nga karakteri i dytë, atëherë rezultati i llogaritjes së funksionit do të jetë "8".

Shembulli 4: Përdorimi i shkronjave të shkreta në funksionin SEARCH në Excel

Gjatë kërkimit, funksioni merr parasysh shenjat e egra. Në shembullin e mësipërm, ne jemi duke kërkuar për tekstin "c*l". Prania e shkronjës "*" në këtë pyetje do të thotë që ne po kërkojmë ndonjë fjalë që fillon me shkronjën "c" dhe mbaron me shkronjën "l", dhe ajo që është midis këtyre dy shkronjave nuk është e rëndësishme. Si rezultat, funksioni kthen vlerën "3", pasi në fjalën "Excel", e vendosur në qelizën A2, shkronja "c" është në pozicionin e tretë.