Кім тым жалқау немесе оқуға уақыты жоқ – бейнені қараңыз. Мәліметтер мен нюанстар төмендегі мәтінде.
Мәселені тұжырымдау
Сонымен, бізде екі үстел бар - тапсырыс кестесі и бағалар тізімі:
Міндет - кейінірек құнын есептей алатындай етіп, өнімнің атауына назар аудара отырып, бағаларды прейскуранттан тапсырыстар кестесіне автоматты түрде ауыстыру.
шешім
Excel функциялар жинағында санат астында Сілтемелер мен массивтер (Іздеу және сілтеме) функциясы бар VPR (КӨРУ).Бұл функция берілген мәнді іздейді (біздің мысалда бұл «Алма» сөзі) көрсетілген кестенің (бағалар тізімі) сол жақ бағанында жоғарыдан төмен қарай жылжытылады және оны тапқаннан кейін көрші ұяшықтың мазмұнын көрсетеді. (23 рубль) .Сұлбалық түрде бұл функцияның жұмысын көрсетуге болады Сонымен:
Функцияны әрі қарай пайдалануды жеңілдету үшін бірден бір әрекетті орындаңыз – бағалар тізіміндегі ұяшықтар ауқымына өз атыңызды беріңіз. Ол үшін бағалар тізімінің «тақырыптан» басқа барлық ұяшықтарын таңдаңыз (G3: H19), мәзірден таңдаңыз Кірістіру - Атау - Тағайындау (Кірістіру — Атау — Анықтау) немесе түймесін басыңыз CTRL + F3 сияқты кез келген атауды (бос орынсыз) енгізіңіз баға… Енді болашақта бұл атауды прейскурантқа сілтеме жасау үшін пайдалануға болады.
Енді біз функцияны қолданамыз VPR… Ол енгізілетін ұяшықты таңдап (D3) және қойындыны ашыңыз Формулалар – Функцияны енгізу (Формулалар — Функцияны кірістіру)… санатта Сілтемелер мен массивтер (Іздеу және анықтама) функциясын табыңыз VPR (КӨРУ) және баспасөз OK… Функция үшін аргументтерді енгізу терезесі пайда болады:
Біз оларды кезекпен толтырамыз:
- Қалаған мән (Іздеу мәні) – функция бағалар тізімінің сол жақ бағанында табуы тиіс өнім атауы. Біздің жағдайда В3 ұяшығынан алынған «Алма» сөзі.
- үстел (Кесте массиві) – қалаған мәндері XNUMXbuXNUMX алынған кесте, яғни біздің бағалар тізімі. Анықтама үшін біз бұрын берілген «Баға» атауын қолданамыз. Егер сіз атау бермесеңіз, кестені таңдауға болады, бірақ түймесін басуды ұмытпаңыз F4доллар белгілері бар сілтемені бекіту үшін, өйткені әйтпесе, формуланы D3:D30 бағанындағы ұяшықтардың қалған бөлігіне көшіру кезінде ол төмен қарай сырғытады.
- Баған_нөмірі (баған индексінің нөмірі) – сериялық нөмірі (әріп емес!) Прайс-парақшадағы баға мәндерін алатын бағанның. Атаулары бар прейскуранттың бірінші бағанында 1 нөмірленген, сондықтан бізге №2 бағандағы баға қажет.
- аралық_іздеу (диапазонды іздеу) – бұл өріске тек екі мәнді енгізуге болады: FALSE немесе TRUE:
- Мән енгізілсе 0 or ӨТІРІК (ЖАЛҒАН), онда шын мәнінде бұл тек іздеуге рұқсат етілгенін білдіреді дәл сәйкестік, яғни функция бағалар тізімінде тапсырыс кестесінде көрсетілген стандартты емес элементті таппаса (мысалы, «Кокос» енгізілсе), ол #N/A (деректер жоқ) қатесін жасайды.
- Мән енгізілсе 1 or TRUE (ШЫН), онда бұл іздеуге дәл емес, бірақ рұқсат беретіндігіңізді білдіреді шамамен сәйкестік, яғни «кокос жаңғағы» жағдайында функция «кокос жаңғағы» мүмкіндігінше жақын атауы бар өнімді табуға тырысады және осы атаудың бағасын қайтарады. Көп жағдайда мұндай шамамен ауыстыру пайдаланушыға шын мәнінде болған дұрыс емес өнімнің мәнін ауыстыру арқылы ойын ойнауы мүмкін! Сондықтан көптеген нақты бизнес мәселелері үшін шамамен іздеуге рұқсат бермеген дұрыс. Ерекшелік - біз мәтінді емес, сандарды іздеген кезде, мысалы, қадамдық жеңілдіктерді есептеу кезінде.
Барлығы! Басу қалады OK және енгізілген функцияны бүкіл бағанға көшіріңіз.
# N / A қателер және олардың жолын кесу
функция VPR (КӨРУ) #N/A қатесін қайтарады (#Жоқ) егер а:
- Дәл іздеу қосылды (аргумент Интервалдық көрініс = 0) және қалаған атау жоқ үстел.
- Дөрекі іздеу енгізілген (Интервалдық көрініс = 1), бірақ үстел, іздеу жүріп жатқан атаулардың өсу реті бойынша сұрыпталмаған.
- Атаудың қажетті мәні келетін ұяшықтың форматы (мысалы, біздің жағдайда B3) және кестенің бірінші бағанының ұяшықтарының форматы (F3: F19) әртүрлі (мысалы, сандық және мәтіндік ). Бұл жағдай, әсіресе, мәтін атауларының орнына сандық кодтарды (шот нөмірлері, идентификаторлар, күндер және т.б.) пайдаланған кезде тән. Бұл жағдайда сіз функцияларды пайдалана аласыз Ч и TEXT деректер пішімдерін түрлендіру үшін. Ол келесідей болады:
=VLOOKUP(МӘТІН(B3),бағасы,0)
Бұл туралы толығырақ мына жерден оқи аласыз.
- Функция қажетті мәнді таба алмайды, себебі кодта бос орындар немесе көрінбейтін басып шығарылмайтын таңбалар (жол үзілімдері және т.б.) бар. Бұл жағдайда мәтіндік функцияларды пайдалануға болады TRIM (ТРИМ) и PRINT(ТАЗА) оларды жою үшін:
=VLOOKUP(ТРИМБАСТЫҚТАР(ТАЗАЛАУ(B3)),бағасы,0)
=VLOOKUP(TRIM(CLEAN(B3));бағасы;0)
Қате туралы хабарды басу үшін # Жоқ (#Жоқ) функция дәл сәйкестікті таба алмаған жағдайларда, функцияны пайдалануға болады ҚАТЕЛІК (ҚАТЕ)… Осылайша, мысалы, бұл құрылым VLOOKUP жасаған кез келген қателерді тоқтатады және оларды нөлдермен ауыстырады:
= ҚАТЕЛІК (VLOOKUP (B3, баға, 2, 0), 0)
= ҚАТЕЛІК (VLOOKUP (B3; баға; 2; 0); 0)
PS
Егер сізге бір мәнді емес, бүкіл жиынды бірден шығару қажет болса (егер бірнеше әртүрлі болса), онда массив формуласымен шаманизациялауға тура келеді. немесе Office 365 жүйесіндегі жаңа XLOOKUP мүмкіндігін пайдаланыңыз.
- VLOOKUP функциясының жақсартылған нұсқасы (VLOOKUP 2).
- VLOOKUP функциясы арқылы қадамдық (диапазон) жеңілдіктерді жылдам есептеу.
- INDEX және MATCH функцияларын пайдаланып «сол жақ VLOOKUP» қалай жасауға болады
- Тізімдегі деректермен пішіндерді толтыру үшін VLOOKUP функциясын қалай пайдалануға болады
- Кестеден бірінші емес, барлық мәндерді бірден қалай шығаруға болады
- VLOOKUP2 және VLOOKUP3 функциялары PLEX қондырмасынан