Excel бағдарламасының VLOOKUP функциясын COLUMN функциясымен біріктіру арқылы дерекқордың немесе деректер кестесінің бір қатарынан бірнеше мәндерді қайтаруға мүмкіндік беретін іздеу формуласын жасауға болады.
Жоғарыдағы суретте көрсетілген мысалда, іздеу формуласы баға, бөлік нөмірі және жабдықтаушы сияқты әртүрлі аппараттық құралдармен байланысты барлық мәндерді қайтаруға мүмкіндік береді.
01-тен 10-ке дейін
Excel бағдарламасымен бірнеше мәндерді қайтару VLOOKUP
Төменде келтірілген қадамдардан кейін, жоғарыдағы суретте көрсетілген іздеу формуласы жасалады, бұл бір деректер жазбасынан бірнеше мәндерді қайтарады.
Қарап шығу формуласы COLUMN функциясын VLOOKUP ішіне кірістіруді талап етеді.
Функцияны енгізу бірінші функцияның дәлелдерінің бірі ретінде екінші функцияны енгізуді қамтиды.
Бұл оқулықта COLUMN функциясы VLOOKUP үшін баған индексінің нөмірінің аргументі ретінде енгізіледі.
Оқу құралындағы соңғы қадам таңдалған бөлікке қосымша мәндерді алу үшін іздеу формуласын қосымша бағандарға көшіруді қамтиды.
Оқулық мазмұны
- Тәжірибелік деректерді енгізу
- Деректер кестесіне арналған аталмыш ауқымды жасау
- VLOOKUP функциясын іске қосу
- Абсолюттік ұяшық сілтемелері арқылы іздеу мәнінің аргументін енгізу
- Кесте массивінің аргументін енгізу
- Кірістірілген COLUMN функциясын енгізу
- VLOOKUP функциясын аяқтау
- Іздеу формуласын толтырылған тұтқамен көшіріп алу
- Деректерді іздеу формуласымен бірге алу
02-тен 10-ға дейін
Тәжірибелік деректерді енгізіңіз
Оқулықтағы алғашқы қадам деректерді Excel жұмыс парағына енгізу болып табылады.
Оқулықтағы қадамдарды орындау үшін суретте көрсетілген деректерді келесі ұяшықтарға енгізіңіз .
- D1-ден G1 ұяшықтарына деректердің жоғарғы ауқымын енгізіңіз
- D4-ден G10 ұяшықтарына екінші ауқымды енгізіңіз
Іздестіру критерийлері және осы оқу құралында жасалған іздеу формуласы жұмыс парағының 2-ші жолына енгізіледі.
Оқулық кескінде көрсетілген пішімдеуді қамтымайды, бірақ бұл іздеу формуласының жұмыс істеуіне әсер етпейді.
Жоғарыда көрсетілгендерге ұқсас пішімдеу параметрлері туралы ақпарат осы негізгі Excel пішімдеу оқу құралында қол жетімді.
Тәжірибелік қадамдар
- Жоғарыдағы суретте көрсетілгендей деректерді D1-ден G10 ұяшықтарына енгізіңіз
03 10
Деректер кестесіне арналған аталмыш ауқымды жасау
Аталған ауқым - бұл формулада деректер ауқымына сілтеме жасаудың оңай жолы. Деректерге арналған ұялы сілтемелерді терудің орнына, ауқымның атауын ғана енгізуге болады.
Аталған ауқымды қолданудың екінші артықшылығы - бұл ауқымға сілтеме жасалған ұяшық жұмыс парағындағы басқа ұяшықтарға формула көшірілген кезде де ешқашан өзгермейді.
Сондықтан, ауқым атаулары формулаларды көшіру кезінде қателерді болдырмау үшін абсолюттік ұяшық сілтемелерін пайдаланудың баламасы болып табылады.
Ескерту: ауқым атауы атау немесе деректерге арналған өріс атауын қамтымайды (4-жол), бірақ тек деректердің өзі.
Тәжірибелік қадамдар
- Жұмыс парағындағы D5-ден G10 ұяшықтарына оларды таңдау үшін бөлектеңіз
- А бағанындағы Атау қорабын басыңыз
- Аты қорабына «Кесте» (тырнақша жоқ) деп теріңіз
- Пернетақтадағы ENTER пернесін басыңыз
- D5-ден G10 ұяшықтары енді «кесте» ауқымының атауына ие. Біз VLOOKUP кесте жиымының аргументінің атын оқу құралында кейінірек пайдаланамыз
04 of 10
VLOOKUP тілқатысу терезесін ашу
Қарап шығу формуласын тікелей жұмыс парағындағы ұяшыққа тікелей енгізуге болатынына қарамастан, көптеген адамдар синтаксисті тікелей ұстауды қиындықтармен - әсіресе, осы оқу құралында пайдаланатын күрделі формула үшін қиын деп санайды.
Балама, бұл жағдайда VLOOKUP тілқатысу терезесін пайдалану . Excel бағдарламасының дерлік барлық функцияларында функцияның әрқайсысын жеке жолға енгізуге мүмкіндік беретін диалогтық терезесі бар.
Тәжірибелік қадамдар
- Жұмыс парағының E2 ұяшығына басыңыз - екі өлшемді іздеу формуласының нәтижелері көрсетілетін орын
- Таспаның Формулалары қойындысын нұқыңыз
- Функция ашылмалы тізімін ашу үшін таспаның Іздеу және анықтама параметрін басыңыз
- Функция диалогтық терезесін ашу үшін тізімдегі VLOOKUP түймесін басыңыз
05 of 10
Абсолюттік ұяшық сілтемелері арқылы іздеу мәнінің аргументін енгізу
Әдетте, іздеу мәні деректер кестесінің бірінші бағанындағы деректер өрісіне сәйкес келеді.
Біздің мысалда іздеу құны ақпаратты табу керек болатын аппараттық бөліктің атына сілтеме жасайды.
Іздеу мәніне арналған рұқсат етілген деректер түрлері:
- мәтіндік деректер
- логикалық мән (тек TRUE немесе FALSE)
- сан
- жұмыс парағындағы мәнге ұяшық сілтемесі
Бұл мысалда бөлік атының орналасатын жеріне - ұяшық сілтемесін енгіземіз - D2 ұяшығы.
Абсолюттік ұялы сілтемелер
Оқулықтағы кейінгі қадамда E2 ұяшығындағы іздеу формуласын F2 және G2 ұяшықтарына көшіреміз.
Әдетте, формулалар Excel бағдарламасында көшірілгенде, ұялы сілтемелер олардың жаңа орнын көрсету үшін өзгереді.
Егер бұл орын алса, D2 - іздеу мәніне арналған ұяшық сілтемесі - формула F2 және G2 ұяшықтарында қателер туындаған кезде көшіріледі.
Қателерді болдырмау үшін D2 ұяшық сілтемесін абсолюттік ұяшық сілтемесіне түрлендіреміз.
Формулалар көшіру кезінде абсолютті ұялы сілтемелер өзгермейді.
Абсолюттік ұялы сілтемелер пернетақтадағы F4 пернесін басу арқылы жасалады. Осылайша, $ D $ 2 сияқты ұяшық сілтемесі бойынша доллар белгілерін қосады
Тәжірибелік қадамдар
- Диалогтық терезеде lookup_value жолын басыңыз
- Бұл ұяшық сілтемесін lookup_value жолына қосу үшін D2 ұяшығын басыңыз. Бұл біз іздейтін бөлік атауын енгізетін ұяшық
- Кірістіру нүктесін жылжытпай, D2 пернесін пернетақтадағы F4 пернесін абсолютті ұяшыққа $ D $ 2 түрлендіру үшін басыңыз
- Оқу құралындағы келесі қадамға VLOOKUP функциясының тілқатысу терезесін ашық қалдырыңыз
06-дан 10-ке дейін
Кесте массивінің аргументін енгізу
Кесте жиымы - іздеу формуласы біз қалайтын ақпаратты табу үшін іздейтін деректер кестесі .
Кесте жиымында кемінде екі баған болуы керек.
- бірінші бағанда іздеу мәнінің дәлелі (оқу құралындағы алдыңғы қадам)
- Екіншіден, және кез-келген қосымша бағандар біз көрсеткен ақпаратты табу үшін іздеу формуласымен ізделеді.
Кесте жиымының аргументі деректер кестесінің немесе ауқымның атауы ретінде ұяшық сілтемелері бар ауқым ретінде енгізілуі керек.
Бұл мысал үшін оқулықтың 3-ші қадамында жасалған ауқымның атауын қолданамыз.
Тәжірибелік қадамдар
- Диалогтық терезеде table_array жолын нұқыңыз
- Осы аргумент үшін ауқым атауын енгізу үшін «Кесте» (тырнақшалар жоқ) деп теріңіз
- Оқу құралындағы келесі қадамға VLOOKUP функциясының тілқатысу терезесін ашық қалдырыңыз
07 ішіндегі 10
COLUMN функциясын кірістіру
Әдетте VLOOKUP тек деректер кестесінің бір бағанынан деректерді қайтарады және бұл баған баған индексінің нөмірі аргументі арқылы орнатылады.
Алайда, осы мысалда бізде деректерді қайтарғымыз келетін үш баған бар, осылайша баған индексінің нөмірін оңай өзгертуге қажет.
Бұл жерде COLUMN функциясы кіреді. Бағанды индекс нөмірі дәлел ретінде енгізу арқылы ол іздеу формуласы D2 ұяшығынан кейін E2 және F2 ұяшықтарына оқу құралында көшіріледі.
Кірістіру функциялары
COLUMN функциясы, VLOOKUP баған индексінің индексінің дәлелі ретінде әрекет етеді.
Бұл VLOOKUP ішіндегі COLUMN функциясын тілқатысу терезесінің Col_index_num жолына кірістіру арқылы орындалады.
COLUMN функциясын қолмен енгізу
Функцияларды ұя салғанда, Excel функциясы оның функциясының тілқатысу терезесін ашуға мүмкіндік бермейді, оның аргументтерін енгізу.
COLUMN функциясы Col_index_num жолында қолмен енгізілуі керек.
COLUMN функциясында тек бір аргумент бар - сілтеме дәлелі болып табылатын ұяшық сілтемесі.
COLUMN функциясының анықтамалық параметрін таңдау
COLUMN функциясының тапсырмасы Сілтеме аргументі ретінде берілген бағанның санын қайтару.
Басқаша айтқанда, бағанның әріптерін A бағанымен бірінші баған, екінші баған, екінші және т.б. сияқты бағанға түрлендіреді.
Деректердің бірінші өрісі біз қайтарылатыны - деректер кестесінің екі бағанында орналасқан элемент бағасы - біз B бағанындағы кез келген ұяшықтың кез келген ұяшығына сілтеме аргументі ретінде ұялы сілтемесін таңдай аламыз Col_index_num аргументі.
Тәжірибелік қадамдар
- VLOOKUP функциясының тілқатысу терезесінде Col_index_num жолын нұқыңыз
- Функция атауының бағанын, одан кейін ашық раунды кронштейні « ( »
- Ұяшық сілтемесін Сілтеме дәлел ретінде енгізу үшін жұмыс парағындағы B1 ұяшығын басыңыз
- COLUMN функциясын аяқтау үшін жабық дөңгелек жақшаны теріңіз
- Оқу құралындағы келесі қадамға VLOOKUP функциясының тілқатысу терезесін ашық қалдырыңыз
08-тен 10-ға дейін
VLOOKUP ауқымының іздеу дәлелін енгізу
VLOOKUP-ның Range_lookup аргументі - VLOOKUP-тың Lookup_value-ге нақты немесе болжалды сәйкестікті табуын қалайтыныңызды көрсететін логикалық мән (тек TRUE немесе FALSE).
- TRUE немесе осы аргумент жоқ болса, VLOOKUP Lookup_value-ге дәл сәйкестікті қайтарады, немесе дәл сәйкестік табылмаса, VLOOKUP келесі ең үлкен мәнді қайтарады. Формула жасау үшін Table_array бірінші бағанындағы деректер өсу тәртібімен сұрыпталуы керек.
- FALSE болса, VLOOKUP тек Lookup_value-ге дәл сәйкес келеді. Егер іздеу мәніне сәйкес келетін Table_array бірінші бағанында екі немесе одан да көп мән болса, табылған бірінші мән пайдаланылады. Егер дәл сәйкестік табылмаса, # N / A қатесі қайтарылады.
Бұл оқулықта нақты жабдық бөлімі туралы нақты ақпаратты іздейтіндіктен, Range_lookup тең False деп орнатамыз.
Тәжірибелік қадамдар
- Диалогтық терезеде Range_lookup жолын басыңыз
- VLOOKUP-ті біз іздеген деректер үшін дәл сәйкестікті қайтаруды қалайтынымызды көрсету үшін осы жолдағы жалған сөзді теріңіз
- Іздеу формуласын аяқтау және тілқатысу терезесін жабу үшін OK түймешігін басыңыз
- D2 ұяшығына іздеу критерийлері әлі енгізілмегендіктен E2 ұяшығында # N / A қатесі болады
- Бұл қате оқулықтың соңғы қадамында іздеу критерийлерін қосқанда түзетіледі
09-дан 10-ке дейін
Іздеу формуласын толтырылған тұтқамен көшіріп алу
Іздеу формуласы бір уақытта деректер кестесінің бірнеше бағанынан деректерді шығаруға арналған.
Бұл әрекетті орындау үшін, іздеу формуласы ақпарат қажет болатын барлық өрістерде орналасуы қажет.
Бұл оқулықта біз деректер кестесінің 2, 3 және 4 бағандарынан деректерді алуды қалаймыз - бұл баға атауы, бөлік нөмірі және жеткізушінің атауы, біз Lookup_value ретінде бөлік атын енгізген кезде.
Деректер жұмыс парағындағы әдеттегі үлгіде берілгендіктен, E2 ұяшығындағы іздеу формуласын F2 және G2 ұяшықтарына көшіруге болады.
Формула көшірілген кезде, Excel бағдарламасы формуланың жаңа орнын көрсету үшін COLUMN функциясында (B1) салыстырмалы ұяшық сілтемесін жаңартады.
Сондай-ақ, Excel бағдарламасы $ D $ 2 абсолютті ұяшық сілтемесін өзгертпейді және формула көшірілген кезде аталған ауқым кестесін өзгертпейді.
Деректерді Excel бағдарламасында көшірудің бірнеше жолы бар, бірақ, ең оңай жолы - Толтыру тұтқасын пайдалану .
Тәжірибелік қадамдар
- Белсенді ұяшықты жасау үшін, іздеу формуласы орналасқан E2 ұяшығын басыңыз
- Тінтуір көрсеткісін төменгі оң жақ бұрыштағы қара шаршыға қойыңыз. Көрсеткіш « + » белгісіне өзгереді - бұл толтыру тұтқасы
- Тінтуірдің сол жақ батырмасын басыңыз және G2 ұяшығына толтыру тұтқасын апарыңыз
- Тінтуір түймесін босатыңыз және F3 ұяшығы екі өлшемді іздеу формуласын қамтуы керек
- Егер дұрыс жасалса, F2 және G2 ұяшықтары енді E2 ұяшығында болатын # N / A қатесін қамтуы керек
10-дан 10-ға дейін
Іздеу критерийлерін енгізу
Іздеу формуласы қажетті ұяшықтарға көшірілгеннен кейін, ол деректер кестесінен ақпаратты алу үшін пайдаланылуы мүмкін.
Ол үшін Lookup_value ұяшығына (D2) шығарылатын элементтің атын енгізіп, пернетақтадағы ENTER пернесін басыңыз.
Аяқтағаннан кейін, іздеу формуласы бар әрбір ұяшық сіз іздеген аппараттық құрал туралы басқа деректерді қамтуы керек.
Тәжірибелік қадамдар
- Жұмыс парағындағы D2 ұяшығын басыңыз
- Виджетті D2 ұяшығына теріңіз және пернетақтадағы ENTER пернесін басыңыз
- E2-ден G2 ұяшықтарында келесі ақпарат көрсетілуі керек:
- E2 - $ 14.76 - виджеттің бағасы
- F2 - PN-98769 - виджеттің бөлшек нөмірі
- G2 - Widgets Inc. - виджеттер үшін жеткізушінің атауы
- VLOOKUP массив формуласын басқа бөліктердің атауын D2 ұяшығына теру және нәтижелерді E2-ден G2 ұяшықтарына қадағалау арқылы тексеріңіз
Егер #REF сияқты қате туралы хабар болса ! E2, F2 немесе G2 ұяшықтарында пайда болса, VLOOKUP қате туралы хабарлардың бұл тізімі мәселенің қай жерде екенін анықтауға көмектесуі мүмкін.