VLOOKUP функциясы әрқашан Excel бағдарламасының ең қуатты функцияларының бірі болды. Ол кестенің бірінші бағанындағы мәндерді іздеуге және оң жақтағы өрістерден мәндерді қайтаруға мүмкіндік береді. Бірақ Excel бағдарламасында кез келген бағандағы немесе жолдағы мәнді іздеуге және кез келген басқа бағандағы деректерді қайтаруға мүмкіндік беретін XLOOKUP деп аталатын функция бар.
XLOOKUP қалай жұмыс істейді
XLOOKUP функциясын пайдалану VLOOKUP функциясына қарағанда әлдеқайда оңай, себебі нәтижелер бағанының мәнін көрсетудің орнына бүкіл ауқымды көрсетуге болады.
Функция сонымен қатар қиылысатын ұяшықтағы мәнді тауып, баған мен жолды іздеуге мүмкіндік береді.
XLOOKUP функциясының параметрлері келесідей:
=ТЕКСЕРУ (іздеу_мәні, іздеу_жиымы, қайтару_жиымы, [сәйкес_режим], [іздеу_режимі])
- іздеу_мәні: Іздегіңіз келетін мән
- іздеу_жиымы: Іздегіңіз келетін массив (баған)
- қайтару_массив:ішінен мәнді алғыңыз келетін нәтиже (баған)
- сәйкестік_режимі (қосымша): дәл сәйкестікті (0), дәл сәйкестікті немесе келесі ең кіші мәнді (-1) немесе қойылмалы таңба сәйкестігін (2) таңдаңыз.
- іздеу_режимі (қосымша): бағандағы бірінші элементтен (1), бағандағы соңғы элементтен (-1), екілік іздеудің өсу жолынан басталатынын таңдаңыз. (2) немесе кему бойынша екілік іздеу (-2).
Төменде XLOOKUP функциясы арқылы жасауға болатын ең көп таралған іздеулердің бірнешеуі берілген.
XLOOKUP көмегімен бір нәтижені қалай іздеу керек
XLOOKUP пайдаланудың ең оңай жолы - бір бағандағы деректер нүктесін пайдаланып бір нәтижені іздеу.
-
Бұл мысал электрондық кесте элементті, бірлік санын, құнын және жалпы сатылымды қамтитын сату өкілдері жіберген тапсырыстар тізімі.
Image -
Егер белгілі бір сату өкілі жіберген тізімдегі бірінші сатылымды тапқыңыз келсе, Өкіл бағанында атауды іздейтін XLOOKUP функциясын жасауға болады. Функция жалпы бағандағы нәтижені қайтарады. Бұл үшін XLOOKUP функциясы:
=ТЕКСЕРУ(I2, C2:C44, G2:G44, 0, 1)
- I2: Қайталау атауы іздеу ұяшығы көрсетеді
- C2:C44: Бұл «Rep» бағаны, ол іздеу массиві
- G2:G33: Бұл жиынтық баған, ол қайтарылатын массив
- 0: дәл сәйкестікті таңдайды
- 1: Нәтижелердегі бірінші сәйкестікті таңдайды
-
Enter пернесін басып, сату өкілінің атын терген кезде «Жалпы нәтиже» ұяшығы сол сату өкілінің кестедегі бірінші нәтижесін көрсетеді.
Image -
Егер ең соңғы сатылымды іздегіңіз келсе (кесте күн бойынша кері ретпен реттелгендіктен), соңғы XLOOKUP аргументін - 1 етіп өзгертіңіз, ол басталады іздеу массивіндегі соңғы ұяшықтан іздеу және оның орнына сізге сол нәтижені береді.
Image -
Бұл мысал іздеу кестесінің бірінші бағанында Қайталау бағанын пайдалану арқылы VLOOKUP функциясымен орындауға болатын ұқсас іздеуді көрсетеді. Дегенмен, XLOOKUP кез келген бағанды кез келген бағытта іздеуге мүмкіндік береді. Мысалы, жылдың бірінші Binder тапсырысын сатқан сату өкілін тапқыңыз келсе, келесі XLOOKUP функциясын пайдаланасыз:
=ТЕКСЕРУ(I2, D2:D44, C2:C44, 0, 1)
- D2: Элементті іздеу ұяшығына меңзейді
- D2:D44: Бұл элемент бағаны, ол іздеу массиві
- C2:C44: Бұл іздеу массивінің сол жағындағы қайтару массиві болып табылатын «Rep» бағаны
- 0: дәл сәйкестікті таңдайды
- 1: Нәтижелердегі бірінші сәйкестікті таңдайды
-
Бұл жолы нәтиже жылдың бірінші байланыстырушы тапсырысын сатқан сату өкілінің аты болады.
Image
XLOOKUP көмегімен тік және көлденең сәйкестікті орындау
VLOOKUP мүмкіндігі жоқ XLOOKUP мүмкіндігінің тағы бір мүмкіндігі тік және көлденең іздеуді орындау мүмкіндігі болып табылады, яғни элементті бағанның астынан және жол бойынан іздеуге болады.
Бұл қос іздеу мүмкіндігі INDEX, MATCH немесе HLOOKUP сияқты басқа Excel функцияларын тиімді алмастырады.
-
Келесі мысал электрондық кестеде әрбір сату өкілінің сатылымдары тоқсанға бөлінген. Егер сіз XLOOKUP функциясынсыз белгілі бір сату өкілінің үшінші тоқсандағы сатылымдарын көргіңіз келсе, бұл іздеу түрі қиын болар еді.
Image -
XLOOKUP функциясының көмегімен іздеудің бұл түрі оңай. Келесі келесі XLOOKUP функциясын пайдаланып, белгілі бір сату өкілі үшін үшінші тоқсандағы сатылымдарды іздеуге болады:
=ТЕКСЕРУ(J2, B2:B42, ТЕКСЕРУ(K2, C1:H1, C2:H42))
- J2: Өкіл іздеу ұяшығын көрсетеді
- B2:B42: Бұл бағанды іздеу массиві болып табылатын Элемент бағаны
- K2: Ширек іздеу ұяшығын көрсетеді
- C1:H1: Бұл жолды іздеу массиві
- C2:H42: Бұл әр тоқсандағы доллар сомасын іздеу массиві
Бұл кірістірілген XLOOKUP функциясы алдымен сату өкілін анықтайды, ал келесі XLOOKUP функциясы қажетті тоқсанды анықтайды. Қайтару мәні - бұл екеуі қиылысатын ұяшық.
-
Бұл формуланың нәтижесі Томпсон есімді өкілдің тоқсандық табысы болып табылады.
Image
XLOOKUP функциясын пайдалану
XLOOKUP функциясы тек Office Insider жазылушыларына қолжетімді, бірақ жақын арада барлық Microsoft 365 жазылушыларына таратылады.
Функцияны өзіңіз тексергіңіз келсе, Office Insider бола аласыз. Файл > Тіркелгі таңдаңыз, содан кейін жазылу үшін Office Insider ашылмалы тізімін таңдаңыз.
Office Insider бағдарламасына қосылғаннан кейін Excel бағдарламасының орнатылған нұсқасы барлық соңғы жаңартуларды алады және XLOOKUP функциясын пайдалана бастай аласыз.
