Pagrindinė biuro paskirtis Excel programas– atlikti skaičiavimus. Šios programos dokumente (knygoje) gali būti daug lapų su ilgomis lentelėmis, užpildytomis skaičiais, tekstu ar formulėmis. Automatizuotas Greita paieška leidžia rasti jose reikiamas ląsteles.

Paprasta paieška

Norėdami ieškoti vertės Excel skaičiuoklė, turite atidaryti išskleidžiamąjį įrankio „Rasti ir pakeisti“ sąrašą „Pagrindinis“ skirtuke ir spustelėti elementą „Rasti“. Tą patį efektą galima pasiekti naudojant sparčiuosius klavišus Ctrl + F.

Paprasčiausiu atveju pasirodžiusiame lange „Rasti ir pakeisti“ reikia įvesti norimą reikšmę ir spustelėti „Rasti viską“.

Kaip matote, paieškos rezultatai pasirodė dialogo lango apačioje. Rastos reikšmės lentelėje pabrauktos raudonai. Jei vietoj „Rasti viską“ paspausite „Rasti kitą“, pirmiausia bus ieškoma pirmame langelyje su šia reikšme, o dar kartą spustelėjus – antrajame.

Teksto paieška atliekama taip pat. Tokiu atveju ieškomas tekstas įvedamas paieškos juostoje.

Jei duomenų ar teksto neieškoma visoje Excel lentelėje, pirmiausia reikia pasirinkti paieškos sritį.

Išplėstinė paieška

Tarkime, kad norite rasti visas reikšmes diapazone nuo 3000 iki 3999. Tokiu atveju paieškos juostoje įveskite 3???. Pakaitos simbolis "?" pakeičia bet kurią kitą.

Analizuojant paieškos rezultatus, galima pastebėti, kad kartu su teisingais 9 rezultatais programa pateikė ir netikėtų, paryškintų raudonai. Jie siejami su skaičiaus 3 buvimu ląstelėje arba formulėje.

Galite būti patenkinti dauguma gautų rezultatų, nekreipdami dėmesio į neteisingus. Tačiau „Excel 2010“ paieškos funkcija gali veikti daug tiksliau. Šiam tikslui skirtas dialogo lange esantis Parinkčių įrankis.

Paspaudęs „Parinktys“, vartotojas turi galimybę atlikti išplėstinę paiešką. Visų pirma, atkreipkime dėmesį į elementą „Paieškos sritis“, kuris pagal numatytuosius nustatymus nustatytas į „Formulės“.

Tai reiškia, kad paieška buvo atlikta, taip pat ir tose ląstelėse, kuriose yra ne reikšmė, o formulė. Skaičiaus 3 buvimas juose davė tris neteisingus rezultatus. Jei pasirinksite „Vertės“ kaip paieškos sritį, ieškosite tik duomenų, o neteisingi rezultatai, susieti su formulės langeliais, išnyks.

Norėdami atsikratyti vienintelio likusio neteisingo rezultato pirmoje eilutėje, išplėstinės paieškos lange turite pasirinkti elementą „Visa langelis“. Po to paieškos rezultatas tampa 100% tikslus.

Tokį rezultatą galima pasiekti iškart pasirinkus elementą „Visa langelis“ (netgi „Paieškos srityje“ palikus „Formulės“ reikšmę).

Dabar pereikime prie elemento „Ieškoti“.

Jei vietoj numatytosios „Stape“ pasirenkate „In Workbook“, tada nereikia būti ieškomų langelių lape. Ekrano kopijoje matyti, kad vartotojas pradėjo paiešką būdamas tuščiame 2 lape.

Kitas išplėstinės paieškos lango elementas yra „View“, kuris turi dvi reikšmes. Numatytasis nustatymas yra „pagal eilutes“, o tai reiškia, kad langeliai nuskaitomi eilė po eilutės. Pasirinkus kitą reikšmę – „pagal stulpelius“ – bus pakeista tik paieškos kryptis ir rezultatų seka.

Ieškant dokumentuose Microsoft Excel, taip pat galite naudoti kitą pakaitos simbolį – „*“. Jei laikoma "?" reiškė bet kokį simbolį, tada „*“ pakeičia ne vieną, o bet kokį simbolių skaičių. Žemiau yra Luizianos paieškos ekrano kopija.

Kartais ieškant reikia atsižvelgti į simbolių atvejį. Jei žodis louisiana rašomas didžiąja raide, paieškos rezultatai nepasikeis. Bet jei išplėstinės paieškos lange pasirinksite „Suderinti didžiąją ir mažąją raidę“, paieška bus nesėkminga. Programa žodžius Luiziana ir Luiziana laikys skirtingais ir, žinoma, pirmojo iš jų neras.

Paieškos tipai

Ieškoti atitikmenų

Kartais lentelėje reikia aptikti pasikartojančias vertes. Norėdami ieškoti atitikmenų, pirmiausia turite pasirinkti paieškos diapazoną. Tada tame pačiame skirtuke „Pagrindinis“ grupėje „Stiliai“ atidarykite „ Sąlyginis formatavimas“ Tada iš eilės pasirinkite elementus „Ląstelių paryškinimo taisyklės“ ir „Pasikartojančios reikšmės“.

Rezultatas parodytas žemiau esančioje ekrano kopijoje.

Jei reikia, vartotojas gali pakeisti suderintų langelių vaizdinio ekrano spalvą.

Filtravimas

Kitas paieškos tipas yra filtravimas. Tarkime, kad vartotojas B stulpelyje nori rasti skaitines reikšmes nuo 3000 iki 4000.

Kaip matote, rodomos tik eilutės, kurios atitinka įvestą sąlygą. Visi kiti buvo laikinai paslėpti. Norėdami grįžti į pradinę būseną, pakartokite 2 veiksmą.

Įvairios paieškos parinktys buvo aptartos naudojant pavyzdį Excel 2010. Kaip ieškoti kitų versijų Excel? 2003 m. versijoje perėjimas prie filtravimo skiriasi. Meniu „Duomenys“ turėtumėte paeiliui pasirinkti komandas „Filter“, „Auto Filter“, „Condition“ ir „Custom Auto Filter“.

Vaizdo įrašas: paieška Excel lentelėje

Tikrai lengviau ieškoti per vieną, nors ir didelę, visą lentelę arba gretimuose langelių diapazonuose, nei per kelias lenteles, suskirstytas į dalis, išsklaidytas skirtinguose ne gretimuose diapazonuose ar net atskiruose lapuose. Net jei tai padarysi automatinė paieška Vienu metu keliose lentelėse gali kilti didelių kliūčių. Tačiau sudėti visus duomenis į vieną lentelę sunku, kartais beveik neįmanoma. Įjungta konkretus pavyzdys Parodysime teisingą sprendimą, kaip vienu metu ieškoti keliose „Excel“ lentelėse.

Vienalaikė paieška keliuose diapazonuose

Vaizdiniam pavyzdžiui sukurkime tris paprastas atskiras lenteles, esančias ne gretimuose vieno lapo diapazonuose:

Reikėtų ieškoti kiekio, reikalingo pagaminti 20 gaminių. Deja, šie duomenys yra skirtinguose stulpeliuose ir eilutėse. Todėl visų pirma reikia pasitikrinti, kiek laiko užtruks šių gaminių gamyba (pirma lentelė).

Remdamiesi gautais duomenimis, turite nedelsdami pradėti paiešką kitoje lentelėje ir sužinoti, kiek darbuotojų turėtų būti įtraukta į tam tikrą gamybos apimtį. Gautą rezultatą reikėtų palyginti su trečios lentelės duomenimis. Taigi per vieną paieškos operaciją per tris lenteles iš karto nustatysime reikiamas išlaidas (sumą).

Paprastas „Excel“ vartotojas ieškos sprendimo naudodamas formulėmis pagrįstas funkcijas, pvz., VLOOKUP. Ir ji atliktų paiešką 3 etapais (kiekvienai lentelei atskirai). Pasirodo, kad atlikę paiešką tik 1 etape, naudodami specialią formulę, galite iš karto gauti paruoštą rezultatą. Už tai:

1. E6 langelyje įveskite reikšmę 20, kuri yra paieškos užklausos sąlyga.
2. E7 langelyje įveskite šią formulę:

Gamybos kaina už 20 vnt. tam tikras produktas.

Kaip formulė su VLOOKUP veikia keliose lentelėse:

Šios formulės veikimo principas pagrįstas nuoseklia visų pagrindinės funkcijos VLOOKUP (pirmosios) argumentų paieška. Pirma, trečioji funkcija VLOOKUP pirmoje lentelėje ieško laiko, reikalingo pagaminti 20 gaminio vienetų, nurodytos kaip langelio E6 reikšmė (kurią vėliau galima pakeisti, jei reikia). Tada antroji funkcija VLOOKUP ieško pirmojo pagrindinės funkcijos argumento reikšmės.

Ieškodami trečiosios funkcijos, gauname reikšmę 125, kuri yra pirmasis antrosios funkcijos argumentas. Gavusi visus parametrus, antroji funkcija antroje lentelėje ieško gamybai reikalingų darbuotojų. Dėl to grąžinama reikšmė 5, kurią vėliau naudos pagrindinė funkcija. Remiantis visais gautais duomenimis, formulė grąžina galutinį skaičiavimo rezultatą. Būtent 1750 USD suma, reikalinga 20 vienetų tam tikro produkto pagaminti.

Naudodamiesi šiuo principu, galite naudoti VLOOKUP funkcijos formules iš kelių lapų.

Laba diena, mieli Habro gyventojai!

Retkarčiais kai kurie (galbūt daugiau nei kai kurie) susiduria su užduotimi apdoroti nedidelį duomenų kiekį, pradedant kompiliavimu ir analize. namų biudžetas ir baigiant bet kokiais darbo, studijų ir pan. skaičiavimais. Bene tinkamiausias įrankis tam yra Microsoft Excel (o gal ir kiti analogai, bet jie rečiau).

Paieška man davė tik vieną straipsnį apie Habré panašia tema - „Talmudas naudojant formules Google SpreadSheet“. Jame gerai aprašomi pagrindiniai darbo su „Excel“ dalykai (nors tai nėra 100% apie patį „Excel“).

Taigi, susikaupus tam tikrą užklausų/užduočių telkinį, kilo mintis juos suvesti ir pasiūlyti galimi sprendimai(nors ir ne viskas įmanoma, bet greitai duoda rezultatų).

Mes kalbėsime apie dažniausiai pasitaikančių problemų, su kuriomis susiduria vartotojai, sprendimą.

Sprendimų aprašymas sudarytas taip: pateikiamas atvejis, kuriame yra pradinė užduotis, kuri palaipsniui tampa sudėtingesnė, ir kiekvienam žingsniui pateikiamas išsamus sprendimas su paaiškinimais. Funkcijų pavadinimai bus pateikiami rusų kalba, tačiau pirminis pavadinimas rusų kalba bus pateiktas skliausteliuose pirmą kartą paminėjus. Anglų kalba(kadangi, remiantis patirtimi, dauguma vartotojų yra įdiegę rusišką versiją).

Case_1: Loginės funkcijos ir atitikimo funkcijas
„Aš turiu verčių rinkinį lentelėje ir būtina, kad kai įvykdoma tam tikra sąlyga / sąlygų rinkinys, būtų rodoma tam tikra reikšmė“ (c) Vartotojas

Duomenys paprastai pateikiami lentelės forma:

Būklė:

• jei reikšmė stulpelyje „Kiekis“ yra didesnė nei 5,
• tada stulpelyje „Rezultatas“ turite parodyti reikšmę „Užsakymo nereikia“,

Formulės sintaksė yra tokia:
IF(loginė_išraiška, [reikšmė_jei_tiesa], [vertė_jei_klaidinga])

• Loginė išraiška yra išraiška, kuri įvertinama kaip TRUE arba FALSE.
• Value_if_true – reikšmė, kuri išspausdinama, jei loginė išraiška teisinga
• Value_if_false – reikšmė, kuri išspausdinama, jei loginė išraiška klaidinga

=IF(C5>5, „Užsakymas nereikalingas“, „Reikalingas užsakymas“)

Taip atsitinka, kad sąlyga yra sudėtingesnė, pavyzdžiui, įvykdoma 2 ar daugiau sąlygų:

• jei reikšmė stulpelyje „Kiekis“ yra didesnė nei 5, o vertė stulpelyje „Tipas“ yra „A“

• Boolean_value1-2 ir kt. - tikrintina sąlyga, kurią įvertinus gaunama reikšmė TRUE arba FALSE

=JEI(IR(C2>5,B2=“A”),1,0)

Pabandykime apsunkinti užduotį – nauja sąlyga:

• jei reikšmė stulpelyje „Kiekis“ yra 10, o stulpelio „Tipas“ vertė yra „A“
• arba reikšmė stulpelyje Kiekis yra didesnė arba lygi 5, o tipo reikšmė yra B
• tada stulpelyje „Rezultatas“ turite rodyti reikšmę „1“, kitu atveju „0“.

=JEI(ARBA(IR(C2=10,B2="A"); IR(C2>=5,B2="B"),1,0)

Dabar pereikime prie kitos situacijos:
Įsivaizduokime, kad priklausomai nuo reikšmės stulpelyje „Sąlyga“, stulpelyje „Rezultatas“ turėtų būti rodoma tam tikra sąlyga; žemiau yra reikšmių ir rezultato atitikimas.
Būklė:

• 1 = A
• 2 = B
• 3 = B
• 4 = G

=JEI(A2=1,“A”, IF(A2=2, „B”, IF(A2=3, „C”, IF(A2=4, „D”,0))))

Kaip matote, tokios formulės rašymas ne tik nėra labai patogus ir gremėzdiškas, bet ir nepatyrusiam vartotojui gali prireikti šiek tiek laiko ją redaguoti, įvykus klaidai.
Šio metodo trūkumas yra tas, kad jis taikomas nedaugeliui sąlygų, nes visas jas reikės įvesti rankiniu būdu ir mūsų formulę „išpūsti“ iki didelių dydžių, tačiau metodas išsiskiria visišku verčių „visaėdžiu“ ir naudojimo universalumas.

Alternatyvus sprendimas_1:
Naudojant formulę PASIRINKTI
Funkcijos sintaksė:
SELECT(indekso_numeris, reikšmė1, [reikšmė2], ...)

• Indekso_numeris – pasirinktos vertės argumento numeris. Indekso numeris turi būti skaičius nuo 1 iki 254, formulė arba nuoroda į langelį, kuriame yra skaičius nuo 1 iki 254.
• Reikšmė1, reikšmė2,... - reikšmė nuo 1 iki 254 reikšmių argumentų, iš kurių funkcija „SELECT“, naudodama indekso numerį, parenka reikšmę ar veiksmą, kurį reikia atlikti. Argumentai gali būti skaičiai, langelių nuorodos, konkretūs pavadinimai, formulės, funkcijos arba tekstas.

• 1 = A
• 2 = B
• 3 = B
• 4 = G

=PASIRINKIMAS(A2, "A", "B", "C", "D")

• 1 = A
• 2 = B
• 3 = B
• 5 = G

=PASIRINKIMAS (A31, „A“, „B“, „C“, „D“)

Alternatyvus sprendimas_2:
Čia mes prieiname prie vieno iš populiariausių Excel funkcijos, kurio įvaldymas bet kurį biuro darbuotoją automatiškai paverčia „patyrusiu excel vartotoju“ /sarkazmas/.
Formulės sintaksė:
VLOOKUP(paieškos_reikšmė, lentelė, stulpelio_numeris, [intervalo_žvalga])

• Search_value – reikšmė, kurios ieško funkcija.
• Lentelė yra langelių diapazonas, kuriame yra duomenų. Būtent šiose ląstelėse bus atliekama paieška. Reikšmės gali būti tekstinės, skaitinės arba loginės.
• Stulpelio_numeris – argumento „Lentelė“ stulpelio, iš kurio bus gauta reikšmė, jei bus atitiktis, numeris. Svarbu suprasti, kad stulpeliai skaičiuojami ne pagal bendrą lapo tinklelį (A.B,C,D ir kt.), o masyvo, nurodyto argumente „Lentelė“, viduje.
• Interval_lookup – nustato, ar funkcija turi rasti tikslią ar apytikslę atitiktį.

Tada darbinėje lentelės dalyje užrašoma formulė su nuoroda į anksčiau užpildytą žinyną. Tie. kataloge, „D“ stulpelyje ieškoma „A“ stulpelio reikšmės, o radus atitiktį „B“ rodoma „E“ stulpelio reikšmė.
Formulės sintaksė:
Rezultato išvedimas langelyje B2:

Dabar įsivaizduokite situaciją, kai reikia ištraukti duomenis į vieną lentelę iš kitos, tačiau lentelės nėra identiškos. Žiūrėkite pavyzdį žemiau

Matyti, kad abiejų lentelių stulpeliuose „Produktas“ eilutės nesutampa, tačiau tai nėra kliūtis naudoti „VLOOKUP“ funkciją.
Rezultato išvedimas langelyje B2:

• Nuoroda yra langelis arba langelių diapazonas, kurio stulpelio numerį norite grąžinti.

=VLOOKUP($A3,$H$3:$M$6, COLUMN(),0)

Mes ir toliau plėtojame temą ir apsunkiname sąlygą: įsivaizduokite, kad turime du katalogus su skirtingais produktų duomenimis ir turime rodyti reikšmes lentelėje su rezultatu, priklausomai nuo to, kokio tipo katalogas yra nurodytas „Katalogas“ stulpelyje
Būklė:

• Jei stulpelyje „Katalogas“ nurodytas skaičius 1, duomenis reikia ištraukti iš lentelės „Katalogas_1“, jei skaičius yra 2, tada iš lentelės „Katalogas_2“ pagal nurodytą mėnesį.

Iš karto į galvą ateina toks sprendimas:

=IF($B3=1; VLOOKUP($A3,$G$3:$I$6; COLUMN()-1,0); VLOOKUP($A3,$K$3:$M$6; COLUMN()-1;0 ))

=VLOOKUP($A3, PASIRINKTI($B3,$G$3:$I$6,$K$3:$M$6), COLUMN()-1,0)

Premija: VLOOKUP pagrįsta dviem ar daugiau parametrų „paieškos_vertė“ argumente.
Būklė:

• Įsivaizduokime, kad, kaip visada, mes turime duomenų masyvą lentelės pavidalu (jei ne, tada mes į jį pateikiame duomenis), turime gauti iš masyvo reikšmes pagal tam tikras charakteristikas ir sudėti jas į kitą lentelės formą. .

Kaip matyti iš lentelių formos, kiekviena prekė turi ne tik pavadinimą (kuris nėra unikalus), bet ir priklauso konkrečiai klasei bei turi savo įpakavimo galimybę.
Naudodami pavadinimo ir klasės bei pakuotės derinį, galime sukurti naują charakteristiką, tam lentelėje su duomenimis sukuriame papildomą stulpelį „Papildoma charakteristika“, kurią užpildome pagal šią formulę:

=SUJUNGTI(H3;"_";I3;"_";J3)

=KLAIDA(VLOOKUP(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

Ši technika gali būti naudojama didesniam skaičiui charakteristikų, vienintelė sąlyga yra gaunamų derinių unikalumas, jei to nesilaikoma, rezultatas bus neteisingas.

Case_3 Ieškoma reikšmės masyve arba kai VLOOKUP negali mums padėti

Panagrinėkime situaciją, kai turime suprasti, ar ląstelių masyve yra mums reikalingos reikšmės.
Užduotis:

• stulpelyje „Paieškos sąlyga“ yra reikšmė ir turite nustatyti, ar ji yra stulpelyje „Paieškos masyvas“

Kaip matome, funkcija „VLOOKUP“ čia yra bejėgė, nes Mes ieškome ne tikslios atitikties, o mums reikalingos vertės buvimo langelyje.
Norint išspręsti problemą, būtina naudoti kelių funkcijų derinį, būtent:
"JEI"
"JEI KLAIDA"
"ŽEMESNIS"
"RASTI"

Kalbant apie visus, mes jau aptarėme „IF“, todėl pereikime prie funkcijos „IFERROR“.

IFERROR(reikšmė, klaidos_vertė)

• Vertė yra argumentas, kuris patikrinamas dėl klaidų.
• Value_on_error – vertė, grąžinama, jei apskaičiuojant formulę įvyko klaida. Galima šių tipų klaidos: #N/A, #VALUE!, #REF!, #DIV/0!, #NUMBER!, #NAME? ir #TUŠTA!.

"ŽEMESNIS"

• Tekstas – tekstas paverstas mažosiomis raidėmis.

Dabar atidžiau pažvelkime į funkcijos FIND sintaksę.

RASTI(paieškos_tekstas, peržiūrėtas_tekstas, [pradžios_pozicija])

• Search_text – tekstas, kurį reikia rasti.
• Paieškos_tekstas – tekstas, kuriame norite rasti ieškomą tekstą.
• Start_position – ženklas, nuo kurio pradedama paieška. Pirmasis simbolis tekste „view_text“ yra sunumeruotas 1. Jei skaičius nenurodytas, jis pagal nutylėjimą yra 1.

=JEI(IFERROR(RASTI(LINE(A2), LINIJA(E2),1),0)=0,"nepavyko", "bingo!")

1. LOWER(A2) – konvertuoja argumentą Search_Text langelyje A2 į mažąsias raides
2. Funkcija FIND pradeda ieškoti konvertuoto argumento Search_Text masyve Search_Text, kuris konvertuojamas naudojant LOWER(E2) funkciją, taip pat į tekstą mažosiomis raidėmis.
3. Jei funkcija suranda atitikmenį, t.y. grąžina pirmojo atitinkančio žodžio / reikšmės simbolio eilės numerį, formulėje „IF“ suaktyvinama sąlyga TRUE, nes gauta reikšmė nėra lygi nuliui. Dėl to stulpelyje „Rezultatas“ bus rodoma reikšmė „Bingo!
4. Jei vis dėlto funkcija neranda atitikmens, t.y. nenurodomas atitinkančio žodžio/reikšmės pirmojo simbolio eilės numeris ir vietoj reikšmės grąžinama klaida, suveikiama sąlyga, įtraukta į formulę „IFERROR“ ir grąžinama reikšmė lygi „0“, kuri atitinka į sąlygą FALSE formulėje „IF“, nes gauta reikšmė yra „0“. Dėl to stulpelyje „Rezultatas“ bus rodoma reikšmė „nepavyko“.

Kaip matyti iš aukščiau esančio paveikslo, funkcijų „LOW“ ir „FIND“ dėka randame norimas reikšmes, nepaisant simbolių ir vietos langelyje, tačiau turime atkreipti dėmesį į 5 eilutę.
Paieškos terminas nustatytas į "111", tačiau paieškos masyve yra reikšmė "1111111 cookies", tačiau formulė grąžina rezultatą "Bingo!" Taip atsitinka todėl, kad reikšmė „111“ įtraukta į reikšmių seriją „1111111“, todėl randama atitiktis. Priešingu atveju ši sąlyga neveiks.

Case_4 Reikšmės paieška masyve pagal kelias sąlygas arba kai VLOOKUP dar labiau negali mums padėti

Įsivaizduokime situaciją, kai reikia rasti reikšmę iš „Lentelės su rezultatu“. dvimatis masyvas„Katalogas“ kelioms sąlygoms, būtent reikšmėms „Vardas“ ir „Mėnuo“.
Užduoties lentelės forma atrodys taip:

Būklė:

• Būtina surinkti duomenis į lentelę su rezultatu pagal sąlygų „Vardas“ ir „Mėnuo“ sutapimą.

Funkcijos INDEX sintaksė

INDEX(masyvas, eilutės_numeris, [stulpelio_numeris])

• Masyvas - langelių diapazonas, iš kurio bus rodomos reikšmės, jei jų paieškos sąlygos atitiks.
• Jei masyve yra tik viena eilutė arba vienas stulpelis, argumentas eilutės_numeris arba stulpelio_numeris atitinkamai yra neprivalomas.
• Jei masyvas užima daugiau nei vieną eilutę ir vieną stulpelį, ir pateikiamas tik vienas iš argumentų eilutės_numeris ir stulpelio_numeris, funkcija INDEX grąžina masyvą, susidedantį iš visos masyvo argumento eilutės arba viso stulpelio.
• Eilutės_numeris – masyvo eilutės, iš kurios norite grąžinti reikšmę, numeris.
• stulpelio_numeris – masyvo stulpelio, iš kurio norite grąžinti reikšmę, numeris.

MATCH funkcijos sintaksė

ATITIKTIS(paieškos_vertė, paieškos_masyvas, [atitikties_tipas])

• Lookup_value yra reikšmė, atitinkanti argumento lookup_masyvo reikšmes. Argumentas lookup_value gali būti reikšmė (skaičius, tekstas arba Būlio reikšmė) arba nuoroda į langelį, kuriame yra tokia reikšmė.
• Looked_array – langelių diapazonas, kuriame atliekama paieška.
• match_type yra neprivalomas argumentas. Skaičius yra -1, 0 arba 1.

Formulės dalis:

MATCH ($A4,$I$4:$I$7,0)

ATITIKTIS (B$3,$J$3:$L$3,0)

Taigi, gavome taško koordinates (1; #N/A), kurią funkcija „INDEX“ naudoja ieškant argumente „Array“.
Visiškai parašyta langelio B4 funkcija atrodytų taip:

=INDEKSAS($J$4:$L$7, ATITIKTIS($A4,$I$4:$I$7,0), ATITIKTIS(B$3,$J$3:$L$3,0))

=INDEKSAS($J$4:$L$7,1,#N/A))

Norėdami neutralizuoti šį neigiamą poveikį, naudojame funkciją „IFERROR“, apie kurią skaitėme anksčiau, ir klaidos atveju grąžintą reikšmę pakeisime „0“, tada formulė atrodys taip:

=IFIKLA(INDEKSAS($J$4:$L$7, ATITIKTIS($A4,$I$4:$I$7,0), ATITIKTIS(B$3,$J$3:$L$3,0)),0)

Kaip matote paveikslėlyje, „#N/A“ reikšmės nebetrukdo mūsų tolesniems skaičiavimams naudojant rezultatų lentelės reikšmes.

Case_5 Reikšmės radimas skaičių diapazone

Įsivaizduokime, kad skaičiams, patenkantiems į tam tikrą diapazoną, reikia suteikti tam tikrą ženklą.
Būklė:
Atsižvelgiant į produkto kainą, jam turėtų būti priskirta tam tikra kategorija
Jei reikšmė yra diapazone

• nuo 0 iki 1000 = A
• Nuo 1001 iki 1500 = B
• Nuo 1501 iki 2000 = B
• Nuo 2001 m. iki 2500 = G
• Daugiau nei 2501 = D

Funkcija LOOKUP grąžina reikšmę iš eilutės, stulpelio arba masyvo. Funkcija turi dvi sintaksines formas: vektorinę ir masyvo formą.

PERŽIŪRA(paieškos_reikšmė, paieškos_vektorius, [rezultato_vektorius])

• lookup_value yra reikšmė, kurios funkcija LOOKUP ieško pirmame vektoriuje. Lookup_value gali būti skaičius, tekstas, loginė vertė, pavadinimas arba reikšmės nuoroda.
• Watch_vector yra diapazonas, susidedantis iš vienos eilutės arba vieno stulpelio. Argumente lookup_vector reikšmės gali būti tekstas, skaičiai arba loginės reikšmės.
• Argumento view_vector reikšmės turi būti didėjančia tvarka: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; kitu atveju funkcija LOOKUP gali pateikti neteisingą rezultatą. Tekstas mažosiomis ir didžiosiomis raidėmis laikomas lygiaverčiais.
• Rezultatas_vektorius yra diapazonas, susidedantis iš vienos eilutės arba stulpelio. Rezultato_vektorius turi būti tokio pat dydžio kaip ir lookup_vector.

=ŽIŪRĖTI(E3,$A$3:$A$7,$B$3:$B$7)

Argumentai „View_vector“ ir „Result_vector“ gali būti parašyti masyvo forma – tokiu atveju jums nereikia jų rodyti atskiroje „Excel“ lapo lentelėje.
Šiuo atveju funkcija atrodys taip:
Rezultato išvedimas langelyje B3:
=PERŽIŪRA(E3;(0;1001;1501;2001;2501);("A", "B", "C", "D", "D"))

Atvejis_6 Skaičių sumavimas pagal charakteristikas

Norėdami susumuoti skaičius pagal tam tikras charakteristikas, galite naudoti tris skirtingas funkcijas:
SUMIF – sumos tik pagal vieną požymį
SUMIFS – kelių charakteristikų sumos
SUMPRODUCT – kelių charakteristikų sumos
Taip pat yra parinktis naudojant „SUM“ ir masyvo formulės funkciją, kai „SUM“ formulė pakeliama į masyvą:
((=SUM(()*()))
tačiau šis metodas yra gana nepatogus ir jo funkcionalumas visiškai padengtas formule „SUMPRODUCT“.
Dabar daugiau informacijos apie „SUMPRODUCT“ sintaksę:

SUMPRODUCT(masyvas1, [masyvas2], [masyvas3],...)

• Masyvas1 yra pirmasis masyvas, kurio komponentus reikia padauginti, o tada pridėti rezultatus.
• Masyvas2, masyvas3… - nuo 2 iki 255 masyvai, kurių komponentai turi būti padauginti ir tada pridėti rezultatai.

• Raskite bendrą siuntų kainą kiekvienam produktui tam tikram laikotarpiui:

Kaip matyti iš lentelės su duomenimis, norint apskaičiuoti savikainą, reikia kainą padauginti iš kiekio, o gautą reikšmę, taikant atrankos sąlygas, perkelti į lentelę su rezultatu.
Tačiau SUMPROIZ formulė leidžia tokius skaičiavimus atlikti formulėje.
Rezultato išvedimas langelyje B4:

=SUMMA(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$11
Pažvelkime į formulę dalimis:
– lentelės stulpelyje „Vardas“ nustatykite pasirinkimo sąlygą su duomenimis lentelės stulpelyje „Vardas“ su rezultatu

($K$3:$K$11>=B$3)*($K$3:$K$11 – nustatome sąlygą pagal laiko tarpą, data yra didesnė arba lygi einamojo mėnesio pirmai dienai, bet mažesnė už kito mėnesio pirmą dieną. Panašiai sąlyga yra lentelėje su rezultatu, masyvas yra lentelėje su duomenimis.
– lentelės stulpelius „Kiekis“ ir „Kaina“ padauginkite iš duomenų.
Neabejotinas šios funkcijos pranašumas yra laisva įrašymo sąlygų tvarka, jas galima rašyti bet kokia tvarka, tai neturės įtakos rezultatui.
Rezultatas:

Dabar apsunkinkime sąlygą ir pridėkime reikalavimą, kad pavadinimas „slapukai“ pasirenkamas tik klasėse „maži“ ir „dideli“, o pavadinimui „ritiniai“ viskas, išskyrus klasę „su uogiene“:

Rezultato išvedimas langelyje B4:

=SUMMA(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11)
Į slapukų pasirinkimo formulę įtraukta nauja sąlyga:

(($I$3:$I$11="mažas")+($I$3:$I$11 ="didelis")

– kaip matote, dvi ar daugiau sąlygų viename stulpelyje yra suskirstytos į atskirą grupę, naudojant simbolį „+“ ir sąlygas įterpiant į papildomus skliaustus.
Į atrankos pagal ritinius formulę taip pat įtraukta nauja sąlyga:

=SUMMA(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11) "su uogiene");($L$3:$L$11)*($K$3:$K$11))

Tai:

(3 USD: 11 USD<>"su uogiene")

– Tiesą sakant, šioje formulėje buvo galima parašyti pasirinkimo sąlygą taip pat, kaip ir pasirenkant slapukus, bet tada formulėje tektų išvardyti tris sąlygas, tokiu atveju lengviau parašyti išimtį - nelygu „su uogiene“, tam naudojame reikšmę „<>».
Apskritai, jei ypatybių/klasių grupės žinomos iš anksto, tai geriau jas sujungti į šias grupes, kuriant žinynus, nei surašyti visas sąlygas į funkciją, ją išpūsti.
Rezultatas:

Na, čia mes priėjome prie mūsų trumpo vadovo, kuris iš tikrųjų galėtų būti daug ilgesnis, pabaiga, bet tikslas vis tiek buvo pateikti dažniausiai pasitaikančių situacijų sprendimą, o ne aprašyti konkrečių (bet daug įdomesnių atvejų) sprendimą. ).
Tikiuosi, kad vadovas kam nors padės išspręsti problemas naudojant „Excel“, nes tai reikš, kad mano darbas nebuvo veltui!

Ačiū už sugaištą laiką!

IN Microsoft dokumentai„Excel“, kurią sudaro daugybė laukų, dažnai reikia rasti tam tikrus duomenis, eilutės pavadinimą ir pan. Labai nepatogu, kai reikia peržvelgti daugybę eilučių, kad rastum tinkamą žodį ar posakį. Integruota Microsoft Excel paieška padės sutaupyti laiko ir nervų. Išsiaiškinkime, kaip tai veikia ir kaip jį naudoti.

„Microsoft Excel“ paieškos funkcija suteikia galimybę rasti norimą tekstą arba skaitines reikšmes per langą „Rasti ir pakeisti“. Be to, programa siūlo išplėstines duomenų paieškos galimybes.

1 būdas: paprasta paieška

Paprasta duomenų paieška programoje „Excel“ leidžia rasti visus langelius, kuriuose yra paieškos lange įvestų simbolių rinkinys (raidės, skaičiai, žodžiai ir kt.), neatsižvelgiant į didžiąsias ir mažąsias raides.

2 būdas: ieškokite pagal nurodytą langelių diapazoną

Jei turite gana didelę lentelę, tokiu atveju ne visada patogu ieškoti visame lape, nes paieškos rezultatuose gali būti daugybė rezultatų, kurių konkrečiu atveju nereikia. Yra būdas apriboti paieškos erdvę tik tam tikram langelių diapazonui.

3 būdas: Išplėstinė paieška

Kaip minėta aukščiau, atliekant įprastą paiešką, paieškos rezultatai apima absoliučiai visus langelius, kuriuose yra nuoseklus paieškos simbolių rinkinys bet kokia forma, neatsižvelgiant į didžiąsias ir mažąsias raides.

Be to, į rezultatus gali būti įtrauktas ne tik konkretaus langelio turinys, bet ir elemento, su kuriuo jis susijęs, adresas. Pavyzdžiui, langelyje E2 yra formulė, kuri yra langelių A4 ir C3 suma. Ši suma yra 10, ir būtent šis skaičius rodomas langelyje E2. Bet jei paieškoje įvesime skaičių „4“, tada tarp rezultatų bus tas pats langelis E2. Kaip tai galėjo atsitikti? Tiesiog langelyje E2 kaip formulė yra langelio A4 adresas, kuriame tiesiog yra norimas skaičius 4.

Tačiau kaip galime atkirsti šiuos ir kitus akivaizdžiai nepriimtinus paieškos rezultatus? Būtent šiems tikslams egzistuoja „Excel“ išplėstinė paieška.

1. Atidarius langą „Rasti ir pakeisti“ naudodami bet kurį aukščiau aprašytą metodą, spustelėkite mygtuką "Galimybės".



2. Lange pasirodo keletas papildomų paieškos valdymo įrankių. Pagal numatytuosius nustatymus visi šie įrankiai yra tokios pačios būsenos kaip ir įprasta paieška, tačiau prireikus galima atlikti koregavimus.

   

   Pagal numatytuosius nustatymus funkcijos „Rungtynių atvejis“ Ir "Visos ląstelės" yra išjungti, bet jei pažymime langelius prie atitinkamų elementų, tokiu atveju generuojant rezultatą bus atsižvelgta į įvestą didžiąją ir tikslią atitiktį. Jei įvesite žodį su maža raide, langeliai, kuriuose yra šio žodžio rašyba didžiosiomis raidėmis, nebebus rodomi paieškos rezultatuose, kaip būtų pagal numatytuosius nustatymus. Be to, jei funkcija įjungta "Visos ląstelės", tada prie rezultatų bus pridėti tik elementai, kuriuose yra tikslus pavadinimas. Pavyzdžiui, jei nustatote paieškos užklausą „Nikolajevas“, langeliai, kuriuose yra tekstas „Nikolaev A.D.“, nebus įtraukti į paieškos rezultatus.

   

   Pagal numatytuosius nustatymus paieška atliekama tik aktyviame Excel lape. Bet jei parametras "Paieška" persikelsite į poziciją "Knygoje", tada paieška bus vykdoma visuose atidaryto failo lapuose.

   

   Parametre "Naršyti" galite pakeisti paieškos kryptį. Pagal numatytuosius nustatymus, kaip minėta aukščiau, paieška atliekama eilutė po eilutės. Jungiklio perkėlimas į padėtį „Pagal stulpelius“, galite nustatyti paieškos rezultatų generavimo tvarką, pradedant nuo pirmojo stulpelio.

   

   Stulpelyje "Paieškos sritis" nustatoma, kokių konkrečių elementų ieškoma. Pagal numatytuosius nustatymus tai yra formulės, tai yra duomenys, kurie, spustelėjus langelį, rodomi formulės juostoje. Tai gali būti žodis, skaičius arba langelio nuoroda. Tuo pačiu metu programa, atlikdama paiešką, mato tik nuorodą, o ne rezultatą. Šis poveikis buvo aptartas aukščiau. Norėdami ieškoti tiksliai pagal rezultatus, pagal duomenis, kurie rodomi langelyje, o ne formulės juostoje, turite perkelti jungiklį iš padėties "Formulės"į padėtį "Vertybės". Be to, galima ieškoti pagal užrašus. Tokiu atveju perjunkite jungiklį į padėtį "Pastabos".

   

   Dar tikslesnę paiešką galite nurodyti spustelėję mygtuką "Formatas".

   

   Taip atidaromas langelių formatavimo langas. Čia galite nustatyti langelių, kurie dalyvaus paieškoje, formatą. Galite nustatyti apribojimus pagal skaičių formatą, lygiavimą, šriftą, kraštinę, užpildymą ir apsaugą, bet kurį iš jų arba jų derinį.

   

   Jei norite naudoti konkretaus langelio formatą, tada lango apačioje spustelėkite mygtuką "Naudoti šio langelio formatą...".

   

   Po to pipetės pavidalu pasirodo įrankis. Naudodami jį galite pasirinkti langelį, kurio formatą ketinate naudoti.

   

   Sukonfigūravę paieškos formatą, spustelėkite mygtuką "GERAI".

   

   Kartais reikia ieškoti ne pagal konkrečią frazę, o rasti langelius, kuriuose paieškos žodžiai yra bet kokia tvarka, net jei jie yra atskirti kitais žodžiais ir simboliais. Tada šie žodžiai turi būti paryškinti iš abiejų pusių „*“ ženklu. Dabar paieškos rezultatuose bet kokia tvarka bus rodomi visi langeliai, kuriuose yra šie žodžiai.



3. Kai nustatysite paieškos nustatymus, spustelėkite mygtuką "Rasti viską" arba "Rasti kitą" norėdami pereiti į paieškos rezultatus.

Kaip matote, „Excel“ yra gana paprastas, bet kartu ir labai funkcionalus paieškos įrankių rinkinys. Norėdami paprasčiausiai girgždėti, tiesiog iškvieskite paieškos langą, įveskite jame užklausą ir paspauskite mygtuką. Tačiau tuo pačiu metu galima sukonfigūruoti individualią paiešką su daugybe skirtingų parametrų ir papildomų nustatymų.

Tarkime, kad reikia surasti darbuotojo telefono plėtinį naudodami jo numerį ir taip pat teisingai įvertinti komisinių koeficientą pardavimo sumai. Ieškote duomenų, kad greitai ir efektyviai rastumėte konkrečius duomenis sąraše ir automatiškai patikrintumėte, ar duomenys naudojami teisingai. Peržiūrėję duomenis galite atlikti skaičiavimus ir rodyti rezultatus, nurodydami grąžinamas reikšmes. Yra keletas būdų, kaip ieškoti reikšmių duomenų sąraše ir rodyti rezultatus.

Šiame straipsnyje

Raskite vertes sąraše vertikaliai pagal tikslią atitiktį

Norėdami atlikti šią užduotį, galite naudoti funkciją VLOOKUP arba funkcijų INDEX ir MATCH derinį.

Funkcijos VLOOKUP naudojimo pavyzdžiai

VLOOKUP funkcija.

INDEX ir MATCHES pavyzdžiai

Ką tai reiškia:

=INDEX(reikia grąžinti reikšmę iš C2:C10, kuri atitiks MATCH(pirmoji reikšmė "Cabbage" masyve B2:B10))

Formulė ieško pirmosios reikšmės langelyje C2:C10, atitinkančioje kopūstai(B7) ir grąžina reikšmę C7 ( 100 ) – pirmoji atitinkama reikšmė kopūstai.

Daugiau informacijos rasite funkcijos INDEX ir MATCH funkcijos.

Raskite vertes sąraše vertikaliai pagal apytikslę atitiktį

Norėdami tai padaryti, naudokite funkciją VLOOKUP.

Svarbu:Įsitikinkite, kad reikšmės pirmoje eilutėje yra surūšiuotos didėjančia tvarka.

Aukščiau pateiktame pavyzdyje funkcija VLOOKUP ieško mokinio, kuris turi 6 vėlavimus diapazone A2:B7, vardo. Lentelėje nėra įrašo 6 vėluoja, todėl funkcija VLOOKUP ieško kitos didžiausios atitikties žemiau 6 ir suranda reikšmę 5, susietą su vardu Dave'as, todėl grįžta Dave'as.

Norėdami gauti daugiau informacijos, žr. funkciją VLOOKUP.

Vertikalių verčių paieška nežinomo dydžio sąraše su tikslia atitiktimi

Norėdami atlikti šią užduotį, naudokite funkcijas OFFSET ir MATCH.

Pastaba:Šis metodas naudojamas, jei duomenys yra išoriniame duomenų diapazone, kurį atnaujinate kiekvieną dieną. Jūs žinote, kad B stulpelyje yra kaina, bet nežinote, kiek duomenų eilučių grąžina serveris, o pirmasis stulpelis nėra surūšiuotas abėcėlės tvarka.

C1 yra viršutinis kairysis diapazono langelis (taip pat vadinamas pradiniu langeliu).

Match("apelsinai"; C2: C7; 0) ieško oranžinės spalvos diapazone C2:C7. Į diapazoną neturėtumėte įtraukti pradinio langelio.

1 - stulpelių, esančių dešinėje nuo pradžios langelio, skaičius, kurio grąžinama vertė turi būti grąžinta. Mūsų pavyzdyje grąžinama vertė yra D stulpelyje, Pardavimai.

Raskite reikšmes sąraše horizontaliai pagal tikslią atitiktį

Šiai užduočiai atlikti naudojama funkcija GLOOKUP. Žemiau yra pavyzdys.

Funkcija LOOKUP ieško stulpelio Pardavimai ir grąžina reikšmę iš 5 eilutės nurodytame diapazone.

Norėdami gauti daugiau informacijos, žr. LOOKUP funkcijos.

Sukurkite paieškos formulę naudodami peržvalgos vedlį (tik „Excel 2007“)

Pastaba:„Excel 2010“ peržvalgos vedlio papildinys nebeteikiamas. Ši funkcija buvo pakeista funkcijų vedliu ir galimomis paieškos ir nuorodų (nuorodų) funkcijomis.

Programoje Excel 2007 peržvalgos vedlys sukuria paieškos formulę, pagrįstą darbalapio duomenimis, turinčiais eilučių ir stulpelių antraštes. Peržvalgos vedlys padeda rasti kitas reikšmes eilutėje, kai žinote reikšmę viename stulpelyje, ir atvirkščiai. Peržvalgos vedlys savo sukurtose formulėse naudoja indeksą ir MATCH.