Көптеген критерийлері бар Excel іздеу формуласы

Excel бағдарламасындағы жиым формуласын пайдалану арқылы дерекқорда немесе деректер кестесінде ақпаратты табу үшін бірнеше өлшемдерді пайдаланатын іздеу формуласын жасауға болады.

Массив формуласы INDEX функциясының ішіндегі MATCH функциясын енгізуді қамтиды.

Бұл оқулықта үлгі дерекқордағы титан виджеттерінің жеткізушісін табу үшін бірнеше критерийді пайдаланатын іздеу формуласын жасаудың қадамдық мысалы бар.

Төмендегі нұсқаулықтағы қадамдардан кейін сіз жоғарыдағы суретте көрсетілген формуланы құру және пайдалану арқылы жүре аласыз.

01-ден 09-ге дейін

Тәжірибелік деректерді енгізу

Оқулықтағы алғашқы қадам деректерді Excel жұмыс парағына енгізу болып табылады.

Оқулықтағы қадамдарды орындау үшін суретте көрсетілген деректерді келесі ұяшықтарға енгізіңіз .

• D1-ден F2 ұяшықтарына деректердің жоғарғы ауқымын енгізіңіз
• Екінші ауқымды D5-ден F11 ұяшықтарына енгізіңіз

3 және 4 жолдары осы нұсқаулықта жасалған жиым формуласын орналастыру үшін бос қалдырылады.

Оқулық кескінде көрсетілген пішімдеуді қамтымайды, бірақ бұл іздеу формуласының жұмыс істеуіне әсер етпейді.

Жоғарыда көрсетілгендерге ұқсас пішімдеу параметрлері туралы ақпарат осы негізгі Excel пішімдеу оқу құралында қол жетімді.

02/09

INDEX функциясын іске қосу

INDEX функциясы бірнеше нысандары бар Excel бағдарламасындағы аздаған бірі болып табылады. Функцияда массив пішіні және анықтама пішіні бар .

Массив пішіні нақты деректерді дерекқордан немесе деректер кестесінен қайтарады, ал Сілтеме Пішім ұяшық сілтемесін немесе кестедегі деректердің орнын береді.

Бұл оқулықта біз Array Formін пайдаланамыз, өйткені біздің дерекқорымыздағы осы жеткізушіге сілтеме жасағаннан гөрі титан виджеттеріне арналған жеткізушінің атын білгіңіз келеді.

Әрбір пішінде функцияны бастамас бұрын таңдалуы керек аргументтердің басқа тізімі бар.

Тәжірибелік қадамдар

1. Белсенді ұяшықты жасау үшін F3 ұяшығын басыңыз. Мұнда кірістірілген функцияны енгіземіз.
2. Таспа мәзірінің Формулалар қойындысын нұқыңыз.
3. Функция ашылмалы тізімін ашу үшін таспадан іздеу және анықтаманы таңдаңыз.
4. Select Arguments тілқатысу терезесін жетілдіру үшін тізімдегі INDEX түймесін басыңыз.
5. Диалогтың терезесінде жиым, row_num, col_num параметрін таңдаңыз.
6. INDEX функциясы тілқатысу терезесін ашу үшін OK түймешігін басыңыз.

03 09

INDEX функциясының массив аргументін енгізу

Бірінші аргумент - Массив аргументі. Бұл аргумент қажетті деректерге ізделетін ұяшықтар ауқымын анықтайды.

Бұл оқулық үшін бұл дәлел біздің үлгі дерекқорымыз болады .

Тәжірибелік қадамдар

1. Индекс функциясының тілқатысу терезесінде Массив сызығын басыңыз.
2. Диалог терезесіне диапазонды енгізу үшін жұмыс парағында D6-мен F11 ұяшықтарын бөлектеңіз.

04 09

Кірістірілген MATCH функциясын іске қосу

Бір функцияны ішіне кірістіру кезінде қажетті дәлелдерді енгізу үшін екінші немесе кірістірілген функцияның тілқатысу терезесін ашу мүмкін емес.

Істелген функция бірінші функцияның аргументтерінің бірі ретінде терілуі керек.

Бұл оқулықта кірістірілген MATCH функциясы және оның дәлелдері INDEX функциясының екінші жолына енгізіледі - Row_num сызығы.

Функцияларды қолмен енгізу кезінде функцияның дәлелдері бір-бірінен үтірмен бөлінгенін ескеру маңызды.

MATCH функциясының Lookup_value дәлелін енгізу

Кірістірілген MATCH функциясына кірудің алғашқы қадамы Lookup_value дәлелін енгізу болып табылады.

Lookup_value дерекқорда сәйкес келетін іздеу терминіне арналған орын немесе ұяшық сілтемесі болады.

Әдетте Lookup_value бір ғана іздеу критерийін немесе терминді қабылдайды. Бірнеше критерийді іздеу үшін, Lookup_value ұзартуға тиіспіз .

Бұл амперсанд белгісі мен « & » белгісінің көмегімен екі немесе одан да көп ұяшық сілтемелерін біріктіру немесе қосу жолымен жасалады.

Тәжірибелік қадамдар

1. Индекс функциясының тілқатысу терезесінде Row_num жолына нұқыңыз.
2. Функция атауының сәйкестігін, содан кейін ашық раунды кронштейні « ( »
3. Диалогтық терезеге осы ұяшық сілтемесін енгізу үшін D3 ұяшығын басыңыз.
4. Екінші ұяшық сілтемесін қосу үшін D3 ұяшығына сілтеме жасағаннан кейін амперсанды және « & » теріңіз.
5. Екінші ұяшық сілтемесін диалогтық терезеге енгізу үшін E3 ұяшығын басыңыз.
6. MATCH функциясының Lookup_value дәлелін енгізуді аяқтау үшін E3 ұяшығынан кейін үтірді енгізіңіз .
7. Оқу құралындағы келесі қадамға арналған INDEX функциясының тілқатысу терезесін қалдырыңыз.

Оқу құралының соңғы қадамында Lookup_values ​​жұмыс парағының D3 және E3 ұяшықтарына енгізіледі.

05 09

Lookup_array функциясын MATCH функциясына қосу

Бұл қадам кірістірілген MATCH функциясына арналған Lookup_array дәлелін қосуды қамтиды.

Lookup_array - MATCH функциясы оқытудың алдыңғы қадамында қосылған Lookup_value дәлелін табу үшін іздейтін ұяшықтар ауқымы.

Lookup_array аргументінде екі іздеу өрісін анықтағандықтан, біз Lookup_array үшін де солай істеуіміз керек. MATCH функциясы көрсетілген әрбір термин үшін тек бір жиынды іздейді.

Көптеген массивтерді енгізу үшін біз қайтадан амперсанды және « және » массивтерді біріктіру үшін қолданамыз.

Тәжірибелік қадамдар

Бұл қадамдар алдыңғы қадамға енгізілген үтірден кейін INDEX функциясының тілқатысу терезесіндегі Row_num жолына енгізілгеннен кейін енгізіледі.

1. Кірістіру нүктесін ағымдағы енгізудің соңына орналастыру үшін үтірден кейін Row_num жолына нұқыңыз.
2. Ауқымды енгізу үшін жұмыс парағындағы D6-ден D11 ұяшықтарын бөлектеңіз. Бұл функция іздеуге болатын бірінші жиым.
3. D6: D11 ұяшығының сілтемелерінен кейін « & » амперсін теріңіз, себебі функция екі аралықты іздеуді қалайды.
4. Ауқымды енгізу үшін жұмыс парағындағы E6-E11 ұяшықтарын бөлектеңіз. Бұл функция іздеуге арналған екінші жиым.
5. MUCH функциясының Lookup_array дәлелін енгізуді аяқтау үшін E3 ұяшығынан кейін үтірді енгізіңіз .
6. Оқу құралындағы келесі қадамға арналған INDEX функциясының тілқатысу терезесін қалдырыңыз.

06 09

Матч түрін қосу және MATCH функциясын аяқтау

MATCH функциясының үшінші және соңғы дәлелі Match_type аргументі болып табылады .

Бұл аргумент Excel-ге Lookup_value мәнін Lookup_array-дегі мәндермен қалай сәйкестендіру керектігін айтады. Таңдау: 1, 0 немесе -1.

Бұл аргумент міндетті емес. Егер ол жоқ болса, функция 1 әдепкі мәнін пайдаланады.

• егер Match_type = 1 болса немесе есептен шығарылса: MATCH Lookup_value мәнінен аз немесе тең болатын ең үлкен мәнді табады. Lookup_array деректерінің өсу тәртібімен сұрыпталуы керек.
• Егер match_type = 0 болса, MATCH Lookup_value мәніне тең болатын бірінші мәнді табады. Lookup_array деректерін кез-келген ретпен сұрыптауға болады.
• Match_type = -1 болса, MATCH Lookup_value мәніне тең немесе одан асатын ең кіші мәнді табады. Lookup_array деректерін кемуі бойынша сұрыптау керек.

Тәжірибелік қадамдар

Бұл қадамдар алдыңғы қадамға енгізілген үтірден кейін INDEX функциясының тілқатысу терезесіндегі Row_num жолына енгізілгеннен кейін енгізіледі.

1. Row_num жолындағы үтірден кейін нөлдік « 0 » деп теріңіз, өйткені біз кірістірілген функция D3 және E3 ұяшықтарына енгізген шарттарға дәл сәйкестіктерді қайтаруды қалаймыз.
2. MATCH функциясын аяқтау үшін жабық дөңгелек жақшаны теріңіз.
3. Оқу құралындағы келесі қадамға арналған INDEX функциясының тілқатысу терезесін қалдырыңыз.

07 09

INDEX функциясына оралу

Енді MATCH функциясы орындалады, біз ашық тілқатысу терезесінің үшінші жолына көшіп, INDEX функциясының соңғы дәлелін енгіземіз.

Бұл үшінші және соңғы аргумент Excel баған нөмірін D6-дан F11 ауқымына дейін сипаттайтын Column_num аргументі болып табылады, онда ол функция арқылы қайтарылатын ақпаратты табады. Бұл жағдайда титан виджеттеріне арналған өнім.

Тәжірибелік қадамдар

1. Тілқатысу терезесіндегі Column_num жолын нұқыңыз.
2. D6-дан F11 ауқымындағы үшінші бағанда деректерді іздейтіндіктен, осы жолда үш « 3 » (тырнақша жоқ) санын енгізіңіз.
3. OK түймешігін баспаңыз немесе INDEX функциясы тілқатысу терезесін жабыңыз. Ол оқулықта келесі қадамға ашық болу керек - массив формуласын жасау.

08 09

Массив формуласын жасау

Диалогтың терезесін жаппас бұрын, кірістірілген функцияны жиым формуласына айналдыруымыз керек.

Массив формуласы деректер кестесінде бірнеше терминдерді іздеуге мүмкіндік беретін нәрсе. Осы оқулықта біз екі терминге сәйкес келеміз: 1-бағаннан виджеттер және 2-бағаннан титан.

Excel бағдарламасында жиым формуласын жасау пернетақтада CTRL , SHIFT және ENTER пернелерін басу арқылы орындалады.

Бұл кілттерді бір уақытта басып шығару әсері функцияны бұйра жақшалармен қоршау болып табылады: {} енді массив формуласы екенін көрсетеді.

Тәжірибелік қадамдар

1. Аяқталған тілқатысу терезесі әлі оқулықтың алдыңғы қадамынан ашылған кезде, пернетақтада CTRL және SHIFT пернелерін басып тұрыңыз да, ENTER пернесін басып, шығарыңыз .
2. Егер дұрыс жасалса, тілқатысу терезесі жабылады және F3 ұяшығында # N / A қатесі пайда болады - функцияға енгізілген ұяшық.
3. F3 ұяшығында # N / A қатесі пайда болады, себебі D3 және E3 ұяшықтары бос. D3 және E3 - бұл функцияның 5-қадамда Lookup_values-ді табу туралы айтқан ұяшықтар. Деректер екі ұяшыққа қосылғаннан кейін, қате дерекқордан алынған ақпаратпен ауыстырылады.

09 09

Іздеу критерийлерін қосу

Оқу құралындағы соңғы қадам біздің жұмыс парағына іздеу шарттарын қосу болып табылады.

Алдыңғы қадамда айтылғандай, біз Виджеттерді 1 бағанынан және Титаннан 2-бағанға сәйкес келеміз.

Егер дерекқордағы сәйкес бағандарда екі термин үшін де сәйкесінше формула табылса, ол үшінші бағаннан мәнді қайтарады.

Тәжірибелік қадамдар

1. D3 ұяшығын басыңыз.
2. Виджеттерді енгізіп , пернетақтадағы Enter пернесін басыңыз.
3. E3 ұяшығын басыңыз.
4. Титанны теріп , пернетақтадағы Enter пернесін басыңыз.
5. Сатушы атауы Widgets Inc. F3 ұяшығында пайда болуы керек - функцияның орналасқан жері, ол Titanium Widgets сататын жалғыз өнім беруші болып табылады.
6. F3 ұяшығын толық функцияны басқанда
   {= INDEX (D6: F11, MATCH (D3 & E3, D6: D11 & E6: E11, 0), 3)}
   жұмыс парағының үстіндегі формула жолында пайда болады.

Ескерту: Біздің мысалда титан виджеттеріне тек бір өнім берілді. Бірнеше жеткізуші болған жағдайда, дерекқорда бірінші рет жеткізілген өнім функциямен қайтарылады.