Excel бағдарламасының VLOOKUP функциясын БАҒАН функциясымен біріктіру арқылы дерекқордың немесе деректер кестесінің бір жолынан бірнеше мәнді қайтаратын іздеу формуласын жасауға болады. Бір деректер жазбасынан бірнеше мәнді қайтаратын іздеу формуласын жасау жолын үйреніңіз.
Осы мақаладағы нұсқаулар Excel 2019, 2016, 2013, 2010 нұсқаларына қолданылады; және Microsoft 365 үшін Excel.
Төменгі жол
Іздеу формуласы COLUMN функциясының VLOOKUP ішіне кірістірілген болуын талап етеді. Функцияны кірістіру екінші функцияны бірінші функцияның аргументтерінің бірі ретінде енгізуді қамтиды.
Оқулық деректерін енгізіңіз
Бұл оқулықта БАҒАН функциясы VLOOKUP үшін баған индексінің нөмірі аргументі ретінде енгізілген. Оқулықтағы соңғы қадам таңдалған бөліктің қосымша мәндерін шығарып алу үшін іздеу формуласын қосымша бағандарға көшіруді қамтиды.
Осы оқулықтағы бірінші қадам деректерді Excel жұмыс парағына енгізу болып табылады. Осы оқулықтағы қадамдарды орындау үшін төмендегі суретте көрсетілген деректерді келесі ұяшықтарға енгізіңіз:
- Деректердің жоғарғы ауқымын D1-G1 ұяшықтарына енгізіңіз.
- Екінші ауқымды D4-G10 ұяшықтарына енгізіңіз.
Осы оқулықта жасалған іздеу шарты мен іздеу формуласы жұмыс парағының 2-жолында енгізілген.
Бұл оқулық суретте көрсетілген негізгі Excel пішімдеуін қамтымайды, бірақ бұл іздеу формуласының жұмысына әсер етпейді.
Деректер кестесі үшін атаулы ауқым жасау
Аталған ауқым – формуладағы деректер ауқымына сілтеме жасаудың оңай жолы. Деректер үшін ұяшық сілтемелерін терудің орнына, ауқымның атын теріңіз.
Аталған ауқымды пайдаланудың екінші артықшылығы формула жұмыс парағындағы басқа ұяшықтарға көшірілгенде де осы ауқымға арналған ұяшық сілтемелерінің ешқашан өзгермейтіндігі. Ауқым атаулары формулаларды көшіру кезінде қателерді болдырмау үшін абсолютті ұяшық сілтемелерін пайдалануға балама болып табылады.
Ауқым атауы деректерге арналған тақырыптарды немесе өріс атауларын қамтымайды (4-жолда көрсетілгендей), тек деректер.
-
Жұмыс парағындағы D5 - G10 ұяшықтарын бөлектеңіз.
Image -
Меңзерді А бағанының үстінде орналасқан Атау жолағына қойып, Кесте деп теріңіз, содан кейін Enter пернесін басыңыз. D5 - G10 ұяшықтарында кестенің ауқым атауы бар.
Image - VLOOKUP кесте массивінің аргументі үшін ауқым атауы осы оқулықта кейінірек пайдаланылады.
VLOOKUP диалогтық терезесін ашыңыз
Іздеу формуласын жұмыс парағындағы ұяшыққа тікелей теру мүмкін болса да, көптеген адамдарға синтаксисті түзу сақтау қиынға соғады, әсіресе осы оқулықта пайдаланылған сияқты күрделі формула үшін.
Балама ретінде VLOOKUP функциясының дәлелдері диалогтық терезесін пайдаланыңыз. Excel функцияларының барлығында дерлік диалогтық терезе бар, онда функция аргументтерінің әрқайсысы бөлек жолға енгізіледі.
-
Жұмыс парағының E2 ұяшығын таңдаңыз. Бұл екі өлшемді іздеу формуласының нәтижелері көрсетілетін орын.
Image -
Таспада Формулалар қойындысына өтіп, Іздеу және анықтама тармағын таңдаңыз.
Image -
VLOOKUP опциясын таңдап, Функция аргументтері тілқатысу терезесін ашыңыз.
Image - Функция аргументтері диалогтық терезесі ВLOOKUP функциясының параметрлері енгізілетін орын.
Іздеу мәні аргументін енгізіңіз
Әдетте, іздеу мәні деректер кестесінің бірінші бағанындағы деректер өрісіне сәйкес келеді. Бұл мысалда іздеу мәні ақпаратты тапқыңыз келетін бөліктің атауын білдіреді. Іздеу мәні үшін рұқсат етілген деректер түрлері мәтіндік деректер, логикалық мәндер, сандар және ұяшық сілтемелері болып табылады.
Абсолютті ұяшық сілтемелері
Формулалар Excel бағдарламасында көшірілгенде, ұяшық сілтемелері жаңа орынды көрсету үшін өзгереді. Бұл орын алса, D2, іздеу мәніне арналған ұяшық сілтемесі F2 және G2 ұяшықтарында өзгереді және қателер жасайды.
Формулалар көшірілгенде абсолютті ұяшық сілтемелері өзгермейді.
Қателерді болдырмау үшін D2 ұяшық сілтемесін абсолютті ұяшық сілтемесіне түрлендіріңіз. Абсолютті ұяшық сілтемесін жасау үшін F4 пернесін басыңыз. Бұл ұяшық сілтемесінің айналасында $D$2 сияқты доллар белгілерін қосады.
-
Функция аргументтері тілқатысу терезесінде курсорды іздеу_мәні мәтін жолағына қойыңыз. Содан кейін, жұмыс парағында ұяшық D2 ұяшығын іздеу_мағынасына қосу үшін таңдаңыз. D2 ұяшығы - бөлік атауы енгізілетін орын.
Image -
Кірістіру нүктесін жылжытпай, D2 абсолютті ұяшық сілтемесіне $D$2 түрлендіру үшін F4 пернесін басыңыз.
Image - Оқулықтағы келесі қадам үшін VLOOKUP функциясының диалогтық терезесін ашық қалдырыңыз.
Кесте массивінің аргументін енгізіңіз
Кесте массиві - қажетті ақпаратты табу үшін іздеу формуласы іздейтін деректер кестесі. Кесте массивінде деректердің кемінде екі бағаны болуы керек.
Бірінші бағанда іздеу мәні аргументі (алдыңғы бөлімде орнатылған), ал екінші баған сіз көрсеткен ақпаратты табу үшін іздеу формуласы арқылы ізделеді.
Кесте массивінің аргументі деректер кестесі үшін ұяшық сілтемелерін қамтитын ауқым ретінде немесе ауқым атауы ретінде енгізілуі керек.
Деректер кестесін VLOOKUP функциясына қосу үшін курсорды тілқатысу терезесіндегі кесте_массиві мәтін жолына қойып, Кесте теріңізосы аргументтің ауқым атауын енгізу үшін.
COLUMN функциясын кірістіру
Әдетте VLOOKUP деректер кестесінің бір бағанындағы деректерді ғана қайтарады. Бұл баған баған индексінің нөмірі аргументі арқылы орнатылады. Алайда бұл мысалда үш баған бар және баған индексінің нөмірін іздеу формуласын өңдеусіз өзгерту қажет. Мұны орындау үшін, БАҒАН функциясын VLOOKUP функциясының ішінде Col_index_num аргументі ретінде кірістіріңіз.
Функцияларды кірістіру кезінде Excel оның аргументтерін енгізу үшін екінші функцияның диалогтық терезесін ашпайды. COLUMN функциясын қолмен енгізу керек. COLUMN функциясының тек бір аргументі бар, ол ұяшық сілтемесі болып табылатын Анықтама аргументі.
COLUMN функциясы сілтеме аргументі ретінде берілген баған нөмірін қайтарады. Ол баған әрпін санға түрлендіреді.
Элементтің бағасын табу үшін деректер кестесінің 2-бағанындағы деректерді пайдаланыңыз. Бұл мысал Col_index_num аргументіне 2 кірістіру үшін Сілтеме ретінде B бағанын пайдаланады.
-
Функция аргументтері тілқатысу терезесінде курсорды Col_index_num мәтін ұясына қойып, COLUMN() теріңіз. (Ашық дөңгелек жақшаны міндетті түрде қосыңыз.)
Image -
Жұмыс парағында сол ұяшық сілтемесін Анықтама аргументі ретінде енгізу үшін B1 ұяшығын таңдаңыз.
Image - БАҒАН функциясын аяқтау үшін жабылатын дөңгелек жақша теріңіз.
VLOOKUP ауқымын іздеу аргументін енгізіңіз
VLOOKUP диапазонын іздеу аргументі - VLOOKUP іздеу_мәніне дәл немесе шамамен сәйкестікті табу керектігін көрсететін логикалық мән (ШЫН немесе ЖАЛҒАН).
- ШЫН немесе Өткізілген: VLOOKUP Іздеу_мәніне жақын сәйкестікті қайтарады. Егер дәл сәйкестік табылмаса, VLOOKUP келесі ең үлкен мәнді қайтарады. Кесте_массивінің бірінші бағанындағы деректер өсу ретімен сұрыпталуы керек.
- FALSE: VLOOKUP іздеу_мағынасына дәл сәйкестікті пайдаланады. Егер Table_array бірінші бағанында іздеу мәніне сәйкес келетін екі немесе одан да көп мән болса, бірінші табылған мән пайдаланылады. Егер дәл сәйкестік табылмаса, N/A қатесі қайтарылады.
Осы оқулықта белгілі бір жабдық элементі туралы арнайы ақпарат ізделеді, сондықтан Ауқым_іздеу "ЖАЛҒАН" күйіне орнатылады.
Функция аргументтері тілқатысу терезесінде меңзерді Ауқым_іздеу мәтін жолына қойып, VLOOKUP функциясына деректердің дәл сәйкестігін қайтару үшін False теріңіз.
Іздеу формуласын аяқтау және диалогтық терезені жабу үшін OK таңдаңыз. E2 ұяшығында N/A қатесі болады, себебі іздеу шарты D2 ұяшығына енгізілмеген. Бұл қате уақытша. Бұл оқулықтың соңғы қадамында іздеу шарты қосылғанда түзетіледі.
Іздеу формуласын көшіріп, критерийлерді енгізіңіз
Іздеу формуласы деректер кестесінің бірнеше бағандарынан деректерді бір уақытта шығарып алады. Мұны істеу үшін іздеу формуласы ақпарат қажет өрістердің барлығында болуы керек.
Деректер кестесінің 2, 3 және 4 бағандарынан деректерді алу үшін (баға, бөлік нөмірі және жеткізушінің аты) Іздеу_мәні ретінде ішінара атауды енгізіңіз.
Деректер жұмыс парағында қалыпты үлгіде орналастырылғандықтан, E2 ұяшығындағы іздеу формуласын F2 ұяшықтарына көшіріңіз және G2 Формула көшіріліп жатқанда, Excel формуланың жаңа орнын көрсету үшін БАҒАН функциясындағы (B1 ұяшығы) қатысты ұяшық сілтемесін жаңартады. Формула көшіру кезінде Excel абсолютті ұяшық сілтемесін (мысалы, $D$2) және аталған ауқымды (Кесте) өзгертпейді.
Excel бағдарламасында деректерді көшірудің бірнеше жолы бар, бірақ ең оңай жолы - толтыру тұтқасын пайдалану.
-
Оны белсенді ұяшық ету үшін іздеу формуласы орналасқан E2 ұяшығын таңдаңыз.
Image -
Толтыру тұтқасын G2 ұяшығына сүйреңіз. F2 және G2 ұяшықтары E2 ұяшығында бар N/A қатесін көрсетеді.
Image -
Деректер кестесінен ақпаратты алу үшін іздеу формулаларын пайдалану үшін жұмыс парағында D2 ұяшығын таңдап, Виджет теріңіз де, түймесін басыңыз. Енгізіңіз.
Image Келесі ақпарат E2-G2 ұяшықтарында көрсетіледі.
- E2: $14,76 - виджет бағасы
- F2: PN-98769 - виджеттің бөлік нөмірі
- G2: Widgets Inc. - виджеттерді жеткізушінің аты
-
VLOOKUP массивінің формуласын тексеру үшін D2 ұяшығына басқа бөліктердің атын теріңіз және E2-G2 ұяшықтарындағы нәтижелерді бақылаңыз.
Image - Іздеу формуласы бар әрбір ұяшықта сіз іздеген аппараттық құрал элементі туралы деректердің басқа бөлігі бар.
COLUMN сияқты кірістірілген функциялары бар VLOOKUP функциясы басқа деректерді іздеу сілтемесі ретінде пайдаланып, кесте ішіндегі деректерді іздеудің қуатты әдісін қамтамасыз етеді.
