Funkcija VLOOKUP visada buvo viena iš galingiausių „Excel“funkcijų. Tai leidžia ieškoti verčių pirmajame lentelės stulpelyje ir grąžinti vertes iš laukų dešinėje. Tačiau „Excel“taip pat turi funkciją XLOOKUP, kuri leidžia ieškoti reikšmės bet kuriame stulpelyje ar eilutėje ir grąžinti duomenis iš bet kurio kito stulpelio.
Kaip veikia XLOOKUP
Funkciją XLOOKUP naudoti daug lengviau nei funkciją VLOOKUP, nes užuot nurodyę rezultatų stulpelio reikšmę, galite nurodyti visą diapazoną.
Funkcija taip pat leidžia ieškoti ir stulpelyje, ir eilutėje, nustatant reikšmę susikertančiame langelyje.
Funkcijos XLOOKUP parametrai yra tokie:
=XLOOKUP (paieškos_vertė, paieškos_masyvas, grąžinimo_masyvas, [match_mode], [paieškos_režimas])
- lookup_value: vertė, kurios norite ieškoti
- lookup_array: masyvas (stulpelis), kurio norite ieškoti
- return_array: rezultatas (stulpelis), kurį norite gauti iš
- match_mode (pasirenkama): pasirinkite tikslią atitiktį (0), tikslią atitiktį arba kitą mažiausią reikšmę (-1) arba pakaitos simbolio atitiktį (2).
- search_mode (pasirenkama): pasirinkite, ar ieškoti pradedant nuo pirmo elemento stulpelyje (1), nuo paskutinio elemento stulpelyje (-1), dvejetainė paieška didėjančia tvarka (2) arba dvejetainė paieška mažėjančia tvarka (-2).
Toliau pateikiamos kelios dažniausiai pasitaikančios paieškos, kurias galite atlikti naudodami funkciją XLOOKUP.
Kaip ieškoti vieno rezultato naudojant XLOOKUP
Lengviausias būdas naudoti XLOOKUP yra ieškoti vieno rezultato naudojant duomenų tašką iš vieno stulpelio.
-
Ši pavyzdinė skaičiuoklė yra pardavimo atstovų pateiktų užsakymų sąrašas, įskaitant prekę, vienetų skaičių, kainą ir bendrą pardavimą.
Image -
Jei norite rasti pirmąjį išpardavimą sąraše, kurį pateikė konkretus pardavimo atstovas, galite sukurti funkciją XLOOKUP, kuri stulpelyje Rep ieško vardo. Funkcija grąžins rezultatą iš stulpelio Iš viso. XLOOKUP funkcija yra:
=XLOOKUP(I2, C2:C44, G2:G44, 0, 1)
- I2: nukreipia į Atstovybės pavadinimą paieškos langelį
- C2:C44: tai yra Rep stulpelis, kuris yra paieškos masyvas
- G2:G33: tai yra stulpelis Total, kuris yra grąžinimo masyvas
- 0: pasirenka tikslią atitiktį
- 1: pasirenkama pirmoji rezultato atitiktis
-
Kai paspausite Enter ir įvesite pardavimo atstovo pavadinimą, langelyje Bendras rezultatas bus rodomas pirmasis to pardavimo atstovo rezultatas lentelėje.
Image -
Jei norite ieškoti naujausio išpardavimo (kadangi lentelė išdėstyta pagal datą atvirkštine tvarka), pakeiskite paskutinį XLOOKUP argumentą į - 1, kuris prasidės paiešką iš paskutinio langelio peržvalgos masyve ir vietoje to pateikite tą rezultatą.
Image -
Šiame pavyzdyje parodyta panaši paieška, kurią galėtumėte atlikti naudodami funkciją VLOOKUP, naudodami stulpelį Rep kaip pirmąjį paieškos lentelės stulpelį. Tačiau XLOOKUP leidžia ieškoti bet kurio stulpelio bet kuria kryptimi. Pavyzdžiui, jei norite rasti pardavimo atstovą, kuris pardavė pirmąjį metų Binder užsakymą, naudokite šią XLOOKUP funkciją:
=XLOOKUP(I2, D2:D44, C2:C44, 0, 1)
- D2: nurodo elemento paieškos langelį
- D2:D44: Tai yra elemento stulpelis, kuris yra paieškos masyvas
- C2:C44: tai yra Rep stulpelis, kuris yra grąžinimo masyvas, esantis kairėje nuo paieškos masyvo
- 0: pasirenka tikslią atitiktį
- 1: pasirenkama pirmoji rezultato atitiktis
-
Šį kartą rezultatas bus pardavimo atstovo, kuris pardavė pirmąjį metų segtuvų užsakymą, vardas.
Image
Atlikite vertikalią ir horizontalią atitiktį naudodami XLOOKUP
Kita XLOOKUP galimybė, kurios VLOOKUP neveikia, yra galimybė atlikti vertikalią ir horizontalią paiešką, tai reiškia, kad galite ieškoti elemento stulpelyje ir visoje eilutėje.
Ši dvigubos paieškos funkcija veiksmingai pakeičia kitas Excel funkcijas, pvz., INDEX, MATCH arba HLOOKUP.
-
Toliau pateiktoje pavyzdinėje skaičiuoklėje kiekvieno pardavimo atstovo pardavimas yra padalintas į ketvirtį. Jei norėtumėte matyti konkretaus pardavimo atstovo trečiojo ketvirčio pardavimus be XLOOKUP funkcijos, tokia paieška būtų sudėtinga.
Image -
Su XLOOKUP funkcija tokia paieška paprasta. Naudodami sekančią funkciją XLOOKUP, galite ieškoti konkretaus pardavimo atstovo trečiojo ketvirčio pardavimų:
=XLOOKUP(J2, B2:B42, XLOOKUP(K2, C1:H1, C2:H42))
- J2: nurodo repo paieškos langelį
- B2:B42: Tai yra elemento stulpelis, kuris yra stulpelių paieškos masyvas
- K2: nurodo ketvirčio paieškos langelį
- C1:H1: tai eilučių paieškos masyvas
- C2:H42: tai kiekvieno ketvirčio sumos doleriais paieškos masyvas
Ši įdėta XLOOKUP funkcija pirmiausia identifikuoja pardavimo atstovą, o sekanti funkcija XLOOKUP – norimą ketvirtį. Grąžinama reikšmė bus yra langelis, kuriame šie du susikerta.
-
Šios formulės rezultatas yra atstovo vardu Thompsonas ketvirtojo pirmojo uždarbio.
Image
Funkcijos XLOOKUP naudojimas
Funkcija XLOOKUP pasiekiama tik „Office Insider“prenumeratoriams, tačiau netrukus ji bus pristatyta visiems „Microsoft 365“prenumeratoriams.
Jei norite patys išbandyti funkciją, galite tapti „Office Insider“. Pasirinkite Failas > Account, tada pasirinkite Office Insider išskleidžiamąjį meniu, kad užsiprenumeruotumėte.
Kai prisijungsite prie „Office Insider“programos, įdiegta „Excel“versija gaus visus naujausius naujinimus ir galėsite pradėti naudoti funkciją XLOOKUP.
