Бүгін біз Excel функциясын қарастырамыз − ТАБУ(ағылшын тілінде ТАБУ), бұл бірінші таңбаға қатысты мәтіндегі іздеу мәтінінің орнын табуға мүмкіндік береді. Әдетте, бірақ әрқашан емес, FIND функциясы Excel бағдарламасының басқа функцияларымен бірге қолданылады, біз оны келесі мақалаларда қарастырамыз және бүгін біз бұл функцияның жұмыс істеу принципін түсінуге тырысамыз.

Бізде есімдер мен фамилиялар тізімі бар кесте бар делік.

Біздің міндетіміз - бірінші таңбаға қатысты кеңістіктің орнын табу. Мысалы, аты мен фамилиясын алайық Дани Эстрада, алшақтық қайда екенін өзіміз есептеп көрейік. Бос орын бар 5-шібірінші әріптен басталатын таңба. Енді біз оны FIND функциясы арқылы автоматты түрде жасаймыз.

Функция синтаксисі келесідей

FIND(іздеу_мәтін; қаралған_мәтін; [бастапқы_позиция]), мұнда

іздеу_мәтін - бұл мәтіннен табу керек мәтін, біздің жағдайда бұл бос орын, ол былай белгіленеді " ".

қаралған_мәтін– бұл қажетті мәтін ізделетін мәтін, біздің жағдайда бұл аты және тегі немесе A2 ұяшығы.

start_position - қосымша аргумент, ол мәтінді тіркелгінің қай позициясынан іздеу керек екенін көрсетеді; Бұл, мысалы, іздеу мәтіні екі рет орын алса және біз екінші іздеу мәтінінің орнын білуіміз керек болса, қажет болуы мүмкін.

=ТАП(" " ;A2 ;1 формуласын жазамыз, оны төмен сүйреп, қажетті нәтижені аламыз.

Бұл оқулық функциялардың негізгі артықшылықтарын түсіндіреді ИНДЕКСЖәне ІЗДЕУ Excel-де, бұл оларды салыстырғанда неғұрлым тартымды етеді VLOOKUP. Функция алдында тұрған көптеген күрделі тапсырмаларды оңай жеңуге көмектесетін формулалардың бірнеше мысалдарын көресіз. VLOOKUPкүшсіз.

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

Бұл бізге не үшін керек? – деп сұрайсың. Иә, өйткені VLOOKUPІздеу Excel бағдарламасындағы жалғыз іздеу мүмкіндігі емес және оның көптеген шектеулері көптеген жағдайларда қалаған нәтижелерді алуға кедергі келтіруі мүмкін. Екінші жағынан, функциялар ИНДЕКСЖәне ІЗДЕУ– анағұрлым икемді және олармен салыстырғанда оларды тартымды ететін бірқатар мүмкіндіктерге ие VLOOKUP.

INDEX және MATCH туралы негізгі ақпарат

Өйткені бұл оқу құралының мақсаты функциялардың мүмкіндіктерін көрсету ИНДЕКСЖәне ІЗДЕУ Excel бағдарламасында тік іздеуді жүзеге асыру үшін біз олардың синтаксисі мен қолданылуына тоқталмаймыз.

Мұнда біз мәнін түсіну үшін қажетті минимумды ұсынамыз, содан кейін пайдаланудың артықшылықтарын көрсететін формулалардың мысалдарын егжей-тегжейлі қарастырамыз. ИНДЕКСЖәне ІЗДЕУорнына VLOOKUP.

INDEX – функция синтаксисі және қолданылуы

Функция ИНДЕКС Excel бағдарламасындағы (INDEX) берілген жол және баған нөмірлеріндегі массивтен мәнді қайтарады. Функцияда мына синтаксис бар:

Әрбір аргументтің өте қарапайым түсіндірмесі бар:

• массив(массив) - мән шығарғыңыз келетін ұяшықтар ауқымы.
• жол_саны(line_number) – мәнді шығарғыңыз келетін массивтегі жолдың нөмірі. Егер көрсетілмесе, аргумент қажет баған_саны(баған_нөмірі).
• баған_саны(баған_саны) - мәнді шығарғыңыз келетін массивтегі бағанның нөмірі. Егер көрсетілмесе, аргумент қажет жол_саны(жол_нөмірі)

Егер екі аргумент де көрсетілсе, онда функция ИНДЕКСкөрсетілген жол мен бағанның қиылысындағы ұяшықтан мәнді қайтарады.

Мұнда функцияның қарапайым мысалы берілген ИНДЕКС(ИНДЕКС):

ИНДЕКС(A1:C10,2,3)
=ИНДЕКС(A1:C10,2,3)

Формула ауқымды іздейді A1:C10және ұяшық мәнін қайтарады 2-шісызық және 3-шібаған, яғни ұяшықтан C2.

Өте қарапайым, солай ма? Дегенмен, іс жүзінде сіз әрқашан қай жол мен баған қажет екенін біле бермейсіз, сондықтан функцияның көмегі қажет ІЗДЕУ.

MATCH – функция синтаксисі және қолданылуы

Функция МАТЧ Excel бағдарламасындағы (МАТЧА) ұяшықтар ауқымында көрсетілген мәнді іздейді және осы мәннің ауқымдағы салыстырмалы орнын қайтарады.

Мысалы, егер ауқымда болса B1:B3Нью-Йорк, Париж, Лондон мәндерін қамтиды, содан кейін келесі формула санды қайтарады 3 , өйткені «Лондон» тізімдегі үшінші элемент.

МАТЧ("Лондон",B1:B3,0)
=МАТЧ("Лондон";B1:B3;0)

Функция МАТЧ(MATCH) келесі синтаксиске ие:

MATCH(іздеу_мәні,іздеу_жиымы,)
SATCH(іздеу_мәні, іздеу_жиымы, [сәйкестік_түрі])

• іздеу_мәні(іздеу_мәні) - сіз іздеп жатқан сан немесе мәтін. Аргумент логикалық мәнді немесе ұяшық сілтемесін қоса алғанда мән болуы мүмкін.
• іздеу_массиві(қаралған_массив) – іздеу жүргізілетін ұяшықтар ауқымы.
• сәйкестік_түрі(сәйкестік_түрі) – Бұл аргумент функцияны айтады ІЗДЕУ, дәл немесе шамамен сәйкестікті тапқыңыз келе ме:
  • 1 немесе көрсетілмеген– табады максималды мән, қажеттіден аз немесе оған тең. Қаралып жатқан массив өсу ретімен, яғни ең кішіден ең үлкенге қарай реттелуі керек.
  • 0 – қажетті мәнге тең бірінші мәнді табады. Комбинация үшін ИНДЕКС/ІЗДЕУсізге әрқашан дәл сәйкестік қажет, сондықтан функцияның үшінші аргументі ІЗДЕУтең болуы керек 0 .
  • -1 – іздеу мәнінен үлкен немесе оған тең ең кіші мәнді табады. Қаралып жатқан массив кему ретімен, яғни ең үлкенінен кішісіне қарай сұрыпталуы керек.

Бір қарағанда, функцияның пайдасы ІЗДЕУкүмән тудырады. Ауқымдағы элементтің орнын білу кімге керек? Біз бұл элементтің мағынасын білгіміз келеді!

Еске сала кетейік, біз іздеген мәннің салыстырмалы орны (яғни, жол және/немесе баған нөмірі) дәл дәлелдер үшін көрсетуіміз керек нәрсе. жол_саны(жол_нөмірі) және/немесе баған_саны(баған_саны) функциялары ИНДЕКС(ИНДЕКС). Естеріңізде болса, функция ИНДЕКСберілген жол мен бағанның қиылысындағы мәнді қайтара алады, бірақ ол бізді қай жол мен баған қызықтыратынын анықтай алмайды.

Excel бағдарламасында INDEX және MATCH пайдалану жолы

Енді сіз осы екі функция туралы негізгі ақпаратты білетін болсаңыз, бұл функциялардың қалай жұмыс істейтіні қазірдің өзінде түсінікті болды деп ойлаймын ІЗДЕУЖәне ИНДЕКСбірге жұмыс істей алады. ІЗДЕУұяшықтардың берілген ауқымындағы іздеу мәнінің салыстырмалы орнын анықтайды және ИНДЕКСсол санды (немесе сандарды) пайдаланады және сәйкес ұяшықтан нәтижені қайтарады.

Әлі толық анық емес пе? Функцияларды көрсетіңіз ИНДЕКСЖәне ІЗДЕУбұл пішінде:

INDEX(,(СӘЙКЕСТІК ( іздеу мәні,біз қарап отырған баған,0))
=INDEX( біз шығаратын баған;(СӘйкестену( іздеу мәні;біз қарап отырған баған;0))

Мысал арқылы түсіну одан да оңай болады деп ойлаймын. Сізде мемлекеттік астаналардың келесі тізімі бар делік:

Келесі формула бойынша астаналардың бірінің, мысалы, Жапонияның халқын табайық:

ИНДЕКС($D$2:$D$10,МАТЧ("Жапония",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10,МАТЧ("Жапония",$B$2:$B$10,0))

Енді осы формуланың әрбір элементі не істейтінін қарастырайық:

• Функция МАТЧ(МАТЧ) бағандағы «Жапония» мәнін іздейді Б, және әсіресе – ұяшықтарда B2:B10, және санды қайтарады 3 , өйткені «Жапония» тізімде үшінші орында.
• Функция ИНДЕКС(INDEX) пайдаланады 3 дәлел үшін жол_саны(жол_саны), ол мән қай жолдан қайтарылуы тиіс екенін көрсетеді. Сол. қарапайым формуланы аламыз:

  ИНДЕКС($D$2:$D$10,3)
  =ИНДЕКС($D$2:$D$10,3)

  Формула келесідей нәрсені айтады: ұяшықтарды келесіден қараңыз D2дейін D10және үшінші жолдан мәнді, яғни ұяшықтан шығарыңыз D4, өйткені санау екінші жолдан басталады.

Бұл Excel бағдарламасында алынған нәтиже:

Маңызды! Функция пайдаланатын массивтегі жолдар мен бағандар саны ИНДЕКС(INDEX), аргумент мәндеріне сәйкес келуі керек жол_саны(жол_нөмірі) және баған_саны(баған_саны) функциялары МАТЧ(МАТЧ). Әйтпесе, формуланың нәтижесі қате болады.

Күте тұрыңыз, күтіңіз... неге біз жай ғана функцияны пайдалана алмаймыз? VLOOKUP(VPR)? Лабиринттерді анықтауға уақытты жоғалтудың мәні бар ма? ІЗДЕУЖәне ИНДЕКС?

VLOOKUP("Жапония",$B$2:$D$2,3)
=VLOOKUP("Жапония",$B$2:$D$2,3)

Бұл жағдайда ешқандай мән жоқ! Бұл мысалдың мақсаты тек демонстрациялық мақсаттарға арналған, осылайша сіз қалай жұмыс істейтінін түсіне аласыз ІЗДЕУЖәне ИНДЕКСжұппен жұмыс. Келесі мысалдар буманың шынайы күшін көрсетеді. ИНДЕКСЖәне ІЗДЕУ, ол кезде көптеген қиын жағдайларды оңай жеңеді VLOOKUPтұйыққа тіреледі.

Неліктен INDEX/MATCH VLOOKUP қарағанда жақсы?

Тік іздеу үшін қандай формуланы қолдану керектігін шешкен кезде, Excel гуруларының көпшілігі бұған сенеді ИНДЕКС/ІЗДЕУқарағанда әлдеқайда жақсы VLOOKUP. Дегенмен, көптеген Excel пайдаланушылары әлі де пайдаланады VLOOKUP, өйткені бұл функция әлдеқайда қарапайым. Бұл ауысудың барлық артықшылықтарын өте аз адамдар толық түсінетіндіктен орын алады VLOOKUPбір шоққа ИНДЕКСЖәне ІЗДЕУ, және күрделірек формуланы зерттеуге ешкім уақытты босқа кетіргісі келмейді.

Excel бағдарламасында MATCH/INDEX пайдаланудың 4 негізгі артықшылықтары:

1. Оңнан солға қарай іздеу.Кез келген құзыретті Excel пайдаланушысы білетіндей, VLOOKUPсолға қарай алмайды, яғни ізделетін мән міндетті түрде зерттелетін ауқымның ең сол жақ бағанында болуы керек. Жағдайда ІЗДЕУ/ИНДЕКС, іздеу бағаны іздеу ауқымының сол немесе оң жағында болуы мүмкін. Мысал: бұл мүмкіндікті әрекетте көрсетеді.

2. Бағандарды қауіпсіз қосыңыз немесе жойыңыз.Функциясы бар формулалар VLOOKUPіздеу кестесіне бағанды ​​алып тастасаңыз немесе қоссаңыз, жұмысты тоқтатыңыз немесе қате мәндерді қайтарыңыз. Функция үшін VLOOKUPкез келген кірістірілген немесе жойылған баған формуланың нәтижесін өзгертеді, себебі синтаксис VLOOKUPтолық ауқымды және деректерді шығарғыңыз келетін арнайы баған нөмірін көрсетуді талап етеді.

Мысалы, егер сізде үстел болса A1:C10, және сіз бағаннан деректерді шығарып алғыңыз келеді Б, содан кейін мәнді орнату керек 2 дәлел үшін col_index_num(баған_саны) функциялары VLOOKUP, бұл сияқты:

VLOOKUP("іздеу мәні",A1:C10,2)
=VLOOKUP("іздеу мәні";A1:C10;2)

Егер сіз кейінірек бағандар арасына жаңа баған енгізсеңіз АЖәне Б, содан кейін аргументтің мәнін өзгерту керек болады 2 қосулы 3 , әйтпесе формула жаңадан енгізілген бағандағы нәтижені қайтарады.

Қолдану ІЗДЕУ/ИНДЕКСҚажет мәнді қамтитын баған тікелей анықталғандықтан, нәтижені бұрмаламай тексерілетін ауқымға бағандарды жоюға немесе қосуға болады. Расында да солай үлкен артықшылық, әсіресе үлкен көлемдегі деректермен жұмыс істеу керек болғанда. Сіз пайдаланатын әрбір функцияны түзетуге алаңдамай бағандарды қосуға және жоюға болады VLOOKUP.

3. Ізделетін мәннің өлшеміне шектеу жоқ.Қолдану VLOOKUP, ізделетін мәннің ұзындығы 255 таңбамен шектелетінін есте сақтаңыз, әйтпесе қате алу қаупі бар. #VALUE!(#МӘН!). Сонымен, егер кестеде ұзын жолдар болса, жұмыс істейтін жалғыз шешім - пайдалану ИНДЕКС/ІЗДЕУ.

Сіз бұл формуланы пайдаланасыз делік VLOOKUP, ол ұяшықтардан іздейді B5дейін D10ұяшықта көрсетілген мән A2:

VLOOKUP(A2,B5:D10,3,ЖАЛҒАН)
=ВЛОКОП(A2,B5:D10,3,ЖАЛҒАН)

Ұяшықтағы мән болса, формула жұмыс істемейді A2 255 таңбадан ұзақ. Оның орнына ұқсас формуланы пайдалану керек ИНДЕКС/ІЗДЕУ:

ИНДЕКС(D5:D10,СӘЙКЕСТІК(ШЫН,ИНДЕКС(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10,СӘЙКЕСТІК(ШЫН,ИНДЕКС(B5:B10=A2,0),0))

4. Қосымша жоғары жылдамдықжұмыс.Егер сіз кішкентай кестелермен жұмыс жасасаңыз, Excel өнімділігіндегі айырмашылық, ең алдымен, байқалмайды соңғы нұсқалары. Мыңдаған жолдар мен жүздеген іздеу формулалары бар үлкен кестелермен жұмыс жасасаңыз, Excel бағдарламасын пайдалансаңыз, әлдеқайда жылдам жұмыс істейді. ІЗДЕУЖәне ИНДЕКСорнына VLOOKUP. Жалпы алғанда, бұл ауыстыру жылдамдықты арттырады Excel жұмысықосулы 13% .

Әсер ету VLOOKUP Excel өнімділігі жұмыс кітабында жүздеген күрделі массив формулалары болса, әсіресе байқалады VLOOKUP+SUM. Мәселе мынада, массивтегі әрбір мәнді тексеру бөлек функцияны шақыруды қажет етеді VLOOKUP. Сондықтан, массивте неғұрлым көп мәндер болса және кестеңізде неғұрлым көп массив формулалары болса, Excel баяу жұмыс істейді.

Екінші жағынан, функциялары бар формула ІЗДЕУЖәне ИНДЕКСол тек іздеуді орындайды және нәтижені қайтарады, ұқсас жұмысты айтарлықтай жылдамырақ орындайды.

INDEX және MATCH – формулалардың мысалдары

Енді сіз функцияларды үйренудің себептерін түсіндіңіз ІЗДЕУЖәне ИНДЕКС, қызық бөлігіне өтіп, теориялық білімді практикада қалай қолдануға болатынын көрейік.

MATCH және INDEX көмегімен сол жақтан қалай іздеу керек

Кез келген оқулық VLOOKUPбұл функцияның солға қарай алмайтынын айтады. Сол. егер сіз қарап отырған баған іздеу ауқымындағы ең сол жақ баған болмаса, одан алу мүмкіндігі жоқ VLOOKUPқалаған нәтиже.

Функциялар ІЗДЕУЖәне ИНДЕКС Excel әлдеқайда икемді және сіз шығарып алғыңыз келетін мән бар бағанның қай жерде орналасқанына мән бермейді. Мысалы, штат астаналары мен халық саны туралы кестеге оралайық. Бұл жолы біз формуланы жазамыз ІЗДЕУ/ИНДЕКС, ол Ресей астанасының (Мәскеу) халық саны бойынша қандай орын алатынын көрсетеді.

Төмендегі суретте көріп отырғаныңыздай, формула бұл тапсырманы тамаша орындайды:

ИНДЕКС($A$2:$A$10,МАТЧ("Ресей",$B$2:$B$10,0))

Енді сізде бұл формула қалай жұмыс істейтінін түсіну қиын болмауы керек:

• Алдымен функцияны қолданайық МАТЧ(MATCH), ол тізімде «Ресей» орнын табады:

  MATCH("Ресей",$B$2:$B$10,0))
  =МАТЧ("Ресей",$B$2:$B$10,0))

• Содан кейін функцияның ауқымын орнатыңыз ИНДЕКС(INDEX) мәнін шығару үшін. Біздің жағдайда солай A2:A10.
• Содан кейін екі бөлікті біріктіріп, формуланы аламыз:

  ИНДЕКС($A$2:$A$10;МАТЧ("Ресей";$B$2:$B$10,0))
  =ИНДЕКС($A$2:$A$10,МАТЧ("Ресей",$B$2:$B$10,0))

Анықтама:Дұрыс шешім әрқашан абсолютті сілтемелерді пайдалану болып табылады ИНДЕКСЖәне ІЗДЕУ, формуланы басқа ұяшықтарға көшіру кезінде іздеу ауқымдары жоғалып кетпеуі үшін.

Excel бағдарламасындағы INDEX және MATCH көмегімен есептеулер (ORTA, MAX, MIN)

Басқа Excel функцияларын ішіне кірістіруге болады ИНДЕКСЖәне ІЗДЕУ, мысалы, ең төменгі, максимум немесе орташа мәнге ең жақынды табу үшін. Мұнда кестеге қатысты формулалардың бірнеше нұсқалары берілген:

1. МАКС(MAX). Формула бағандағы максимумды табады D Cбірдей жол:

КӨРСЕТКІШ($C$2:$C$10,СӘйкестік(MAX($D$2:I$10),$D$2:D$10,0))
=КӨРСЕТКІШ($C$2:$C$10,СӘйкестік(МАКС($D$2:I$10),$D$2:D$10,0))

Нәтиже: Пекин

2. MIN(MIN). Формула бағандағы минимумды табады Dжәне бағандағы мәнді қайтарады Cбірдей жол:

ИНДЕКС($C$2:$C$10,СӘйкес (MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10,СӘйкестік(MIN($D$2:I$10),$D$2:D$10,0))

Нәтиже: Лима

3. ОРТА(Орташа). Формула ауқымның орташа мәнін есептейді D2:D10, содан кейін оған ең жақынын тауып, бағандағы мәнді қайтарады Cбірдей жол:

ИНДЕКС($C$2:$C$10,СӘйкес (ОРТА($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10,СӘйкестік(Орташа($D$2:D$10),$D$2:D$10,1))

Нәтиже: Мәскеу

ОРТА функциясын INDEX және MATCH көмегімен пайдалану кезінде есте сақтау керек нәрселер

Функцияны пайдалану ОРТАкомбинациясында ИНДЕКСЖәне ІЗДЕУ, функцияның үшінші аргументі ретінде ІЗДЕУжиі көрсету қажет болады 1 немесе -1 сіз көріп отырған ауқымда орташа мәнге тең мән бар екеніне сенімді болмасаңыз. Мұндай мән бар екеніне сенімді болсаңыз, қойыңыз 0 дәл сәйкестікті табу үшін.

• Көрсетсеңіз 1 , іздеу бағанындағы мәндер өсу ретімен реттелуі керек және формула орташа мәннен аз немесе оған тең максималды мәнді қайтарады.
• Көрсетсеңіз -1 , іздеу бағанындағы мәндер кему ретімен реттелуі керек және орташа мәннен жоғары немесе оған тең ең төменгі мән қайтарылады.

Біздің мысалда бағандағы мәндер Dөсу ретімен реттелген, сондықтан біз сұрыптау түрін қолданамыз 1 . Формула ИНДЕКС/SEARCHPOЗ«Мәскеу» қайтарады, өйткені Мәскеу қаласының тұрғындары орташа мәнге (12 269 006) жақын.

Белгілі жол мен бағанды ​​іздеу үшін INDEX және MATCH пайдалану жолы

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

Бұл мысалда формула ИНДЕКС/ІЗДЕУбір ғана айырмашылығы бар, біз осы сабақта талқылаған формулаларға өте ұқсас болады. Қайсысын тап?

Естеріңізде болса, функция синтаксисі ИНДЕКС(INDEX) үш аргументке мүмкіндік береді:

INDEX(массив,жол_саны,)
INDEX(массив, жол_нөмірі, [баған_нөмірі])

Мен оны болжағандарды құттықтаймын!

Формула үлгісін жазудан бастайық. Ол үшін бізге бұрыннан таныс формуланы алайық ИНДЕКС/ІЗДЕУжәне оған басқа функция қосыңыз ІЗДЕУ, ол баған нөмірін қайтарады.

INDEX(Сіздің кестеңіз ,(MATCH(,) іздеу үшін баған,0)),(СӘйкестендіру(, іздеуге арналған жол,0))
=INDEX(Сіздің кестеңіз ,(MATCH( тік іздеу мәні,іздеу үшін баған,0)),(СӘйкестендіру( көлденең іздеу мәні,іздеуге арналған жол,0))

Екі өлшемді іздеу үшін аргументте бүкіл кестені көрсету керек екенін ескеріңіз массив(массив) функциялары ИНДЕКС(ИНДЕКС).

Енді осы үлгіні іс жүзінде қолданып көрейік. Төменде сіз әлемдегі халқы ең көп елдердің тізімін көресіз. Біздің міндетіміз 2015 жылы Америка Құрама Штаттарының халқын анықтау болып табылады делік.

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

Ендеше екі функциядан бастайық ІЗДЕУ, ол функция үшін жол және баған нөмірлерін қайтарады ИНДЕКС:

• Баған үшін SATCH– біз бағанға қараймыз Б, дәлірек айтқанда диапазонда B2:B11, ұяшықта көрсетілген мән H2(АҚШ). Функция келесідей болады:

  СӘЙКЕСТІК($H$2,$B$1:$B$11,0)
  =СӘйкес ($H$2,$B$1:$B$11,0)

  4 , өйткені «АҚШ» бағандағы 4-ші тізім элементі Б(атауды қоса алғанда).

• Жол үшін MATCH– біз ұяшық мәнін іздейміз H3(2015) қатарында 1 , яғни жасушаларда A1:E1:

  СӘЙКЕСТІК($H$3,$A$1:$E$1,0)
  =СӘйкес ($H$3,$A$1:$E$1,0)

  Бұл формуланың нәтижесі болады 5 , өйткені «2015» 5-бағанда.

Енді осы формулаларды функцияға енгіземіз ИНДЕКСжәне voila:

КӨРСЕТКІШ($A$1:$E$11,МАТЧ($H$2,$B$1:$B$11,0),МАТЧ($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11,СӘйкес ($H$2,$B$1:$B$11,0),СӘйкес ($H$3,$A$1:$E$1,0))

Функцияларды ауыстырсаңыз ІЗДЕУолар қайтаратын мәндер негізінде формула оңай және түсінікті болады:

ИНДЕКС($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11,4,5))

Бұл формула қиылыстағы мәнді қайтарады 4-шісызықтар және 5-шідиапазондағы баған A1:E11, яғни ұяшық мәні E4. Тек? Иә!

INDEX және MATCH көмегімен көп критерий бойынша іздеу

Оқулықта VLOOKUPфункциясы бар формуланың мысалын көрсеттік VLOOKUPбірнеше критерийлер арқылы іздеу. Дегенмен, бұл шешімнің маңызды шектеуі көмекші бағанды ​​қосу қажеттілігі болды. Жақсы жаңалық: формула ИНДЕКС/ІЗДЕУкөмекші бағанды ​​жасауды қажет етпей, екі бағандағы мәндер бойынша іздей алады!

Бізде тапсырыстар тізімі бар делік және біз екі критерийге негізделген соманы тапқымыз келеді - сатып алушының аты(Тұтынушы) және өнім(Өнім). Бір сатып алушы бірден бірнеше түрлі өнімді сатып ала алатындығымен және парақтағы кестедегі сатып алушылардың аты-жөнімен мәселе қиындады. Іздеу кестесікездейсоқ ретпен орналастырылған.

Міне формула ИНДЕКС/ІЗДЕУмәселені шешеді:

(=INDEX("Іздеу кестесі"!$A$2:$C$13, MATCH(1,(A2="Іздеу кестесі"!$A$2:$A$13)*
(B2="Іздеу кестесі"!$B$2:$B$13),0),3))
(=INDEX("Іздеу кестесі"!$A$2:$C$13; MATCH(1,(A2="Іздеу кестесі"!$A$2:$A$13)*
(B2="Іздеу кестесі"!$B$2:$B$13);0);3))

Бұл формула біз бұрын талқылаған басқаларға қарағанда күрделірек, бірақ функциялар туралы біліммен қаруланған ИНДЕКСЖәне ІЗДЕУСіз оны жеңесіз. Ең қиыны - бұл функция ІЗДЕУ, Менің ойымша, алдымен оны түсіндіру керек.

MATCH(1,(A2="Іздеу кестесі"!$A$2:$A$13),0)*(B2="Іздеу кестесі"!$B$2:$B$13)
MATCH(1;(A2="Іздеу кестесі"!$A$2:$A$13);0)*(B2="Іздеу кестесі"!$B$2:$B$13)

Жоғарыда көрсетілген формулада біз іздеп отырған мән 1 , ал іздеу массиві көбейтудің нәтижесі болып табылады. Жарайды, нені көбейтуіміз керек және неге? Барлығын ретімен қарастырайық:

• Бағандағы бірінші мәнді алыңыз А(Тапсырыс беруші) парақта Негізгі кестежәне оны парақтағы кестедегі барлық тұтынушы аттарымен салыстырыңыз Іздеу кестесі(A2:A13).
• Сәйкестік табылса, теңдеу қайтарылады 1 (ШЫН), ал егер жоқ болса - 0 (ӨТІРІК).
• Әрі қарай, баған мәндері үшін де солай істейміз Б(Өнім).
• Содан кейін алынған нәтижелерді көбейтеміз (1 және 0). Тек екі бағанда да сәйкестіктер табылса (яғни екі шартта да дұрыс), сіз аласыз 1 . Егер екі критерий де жалған болса немесе олардың біреуі ғана қанағаттандырылса, сіз аласыз 0 .

Неге сұрағанымызды енді түсінесіз 1 , қалаған мән қандай? Бұл функция дұрыс ІЗДЕУекі критерий де орындалғанда ғана позицияны қайтарды.

Ескерту:Бұл жағдайда функцияның үшінші қосымша аргументін пайдалану керек ИНДЕКС. Бұл қажет, өйткені бірінші аргументте біз бүкіл кестені көрсетеміз және функцияға мәнді қай бағаннан алу керектігін айтуымыз керек. Біздің жағдайда бұл баған C(Сум), біз де кірдік 3 .

Ақырында, өйткені біз массивтің әрбір ұяшығын тексеруіміз керек, бұл формула массив формуласы болуы керек. Мұны оның ішінде орналасқан бұйра жақшалардан көруге болады. Сондықтан формуланы енгізуді аяқтаған кезде, басуды ұмытпаңыз Ctrl+Shift+Enter.

Егер бәрі дұрыс орындалса, төмендегі суреттегідей нәтиже аласыз:

INDEX және MATCH Excel бағдарламасында IFERROR-мен біріктірілген

Сіз байқаған боларсыз (бірнеше рет), егер қате мән енгізсеңіз, мысалы, қаралып жатқан массивте жоқ, формула ИНДЕКС/ІЗДЕУқате туралы хабарлайды #Жоқ(#Жоқ) немесе #VALUE!(#МӘН!). Мұндай хабарламаны неғұрлым түсінікті нәрсемен ауыстырғыңыз келсе, формуланы енгізуге болады ИНДЕКСЖәне ІЗДЕУфункцияға айналдырады ҚАТЕЛІК.

Функция синтаксисі ҚАТЕЛІКөте қарапайым:

IFERROR(мән,қате_егер_мән)
IFERROR(мән,қате_егер_мән)

Дау қайда мән(мән) - қате үшін тексерілетін мән (біздің жағдайда формуланың нәтижесі ИНДЕКС/ІЗДЕУ); және аргумент мән_егер_қате(қате_егер_мән) формула қате жіберсе қайтарылатын мән.

Мысалы, функцияға кірістіруге болады ҚАТЕЛІКбұл сияқты:

ҚАТЕЛІК(ИНДЕКС($A$1:$E$11,МАТЧ($G$2,$B$1:$B$11,0), МАТЧ($G$3,$A$1:$E$1,0)),
"Сәйкестік табылмады. Қайталап көріңіз!") =ҚАТЕЛІК(ИНДЕКС($A$1:$E$11, МАТЧ($G$2,$B$1:$B$11,0), MATCH($G$3,$A$1 : $E$1;0));
"Сәйкестік табылмады. Қайталап көріңіз!")

Ал енді, егер біреу қате мән енгізсе, формула мына нәтижені береді:

Қате болған жағдайда ұяшықты бос қалдырғыңыз келсе, функцияның екінші аргументінің мәні ретінде тырнақшаларды («») пайдалануға болады. ҚАТЕЛІК. Бұл сияқты:

ҚАТЕЛІК(ИНДЕКС(массив,СӘйкес(іздеу_мәні,іздеу_жиым,0),"")
ҚАТЕЛІК(ИНДЕКС(массив,СӘЙКЕСТІК(іздеу_мәні,қаралған_массив,0),"")

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

Есепте көптеген бағандарда деректер көп кесте бар делік. Мұндай кестелерге визуалды талдау жүргізу өте қиын. Есеппен жұмыс істеу тапсырмаларының бірі белгілі бір айға қатысты жол және баған тақырыптарына қатысты деректерді талдау болып табылады. Бір қарағанда, бұл өте қарапайым тапсырма, бірақ оны тек біреуін пайдалану арқылы шешу мүмкін емес стандартты функция. Иә, әрине мына құралды пайдалануға болады: «БАСТЫ» - «Өңдеу» - «Табу» CTRL + F пернелер тіркесімін басып, мәнді іздеу терезесін ашыңыз. Excel парағы. Немесе кесте үшін шартты пішімдеу ережесін жасаңыз. Бірақ содан кейін алынған нәтижелермен одан әрі есептеулер жүргізу мүмкін болмайды. Сондықтан сәйкес формуланы құру және дұрыс қолдану қажет.

Excel массивінде мәнді табу

Мәселенің шешімі келесідей көрінеді:

• B1 ұяшығына бізді қызықтыратын деректерді енгіземіз;
• B2 ұяшығы B1 ұяшығының мәнін қамтитын баған тақырыбын көрсетеді
• B3 ұяшығы B1 ұяшығының мәнін қамтитын жолдың атын көрсетеді.

Сізге Excel бағдарламасында координаттарды іздеу керек. Бұл не үшін? Көбінесе кесте координаттарын мән бойынша алуымыз керек. Кері матрицалық талдау сияқты. Қысқаша айтқанда, нақты мысал келесідей көрінеді. Сандардағы қойылған мақсат - бұл мақсатқа кім және қашан жақын екенін анықтау керек; Мысалы, төмендегі суретте көрсетілгендей үш тоқсанда сатылған тауарлардың саны туралы есебі бар қарапайым деректер матрицасын қолданамыз. Барлық сандық көрсеткіштердің сәйкес келуі маңызды. Қолмен жасап, толтырғыңыз келмесе Excel электрондық кестесінөлден бастап, содан кейін мақаланың соңында сіз дайын мысалды жүктей аласыз.

Біз әртүрлі күрделіліктегі шешім нұсқаларын дәйекті түрде қарастырамыз, ал мақаланың соңында – түпкілікті нәтиже.

Excel бағанында мәнді табу

Алдымен кесте бағанының тақырыптарын мән бойынша алу жолын үйренейік. Ол үшін мына қадамдарды орындаңыз:

1. В1 ұяшығына 5277-кестеден алынған мәнді енгізіп, енгізу өрісін оқуға ыңғайлы ету үшін оның фонын көк түспен белгілеңіз (жаңа мәндермен тәжірибе жасау үшін кейінірек В1 ұяшығына басқа сандарды енгіземіз).
2. C2 ұяшығына осы мәнді қамтитын кесте бағанының тақырыбын алу үшін формуланы енгізіңіз:
3. Формуланы енгізгеннен кейін растау үшін CTRL+SHIFT+Enter пернелер тіркесімін басыңыз, себебі формула массивте орындалуы керек. Егер бәрі дұрыс орындалса, формула жолының жиектерінде бұйра жақшалар ( ) пайда болады.

Excel жолындағы мәнді табу

Енді сол мән үшін жол нөмірін аламыз (5277). Ол үшін C3 ұяшығына келесі формуланы енгізіңіз:

Растау үшін формуланы енгізгеннен кейін CTRL+SHIFT+Enter пернелер тіркесімін қайтадан басып, нәтижені алыңыз:

Формула 9 санын қайтарды – сәйкес кесте мәнін пайдаланып парақ жолының тақырыбын тапты. Нәтижесінде бізде D9 мәнінің толық мекенжайы бар.



Кестенің баған тақырыбы мен жол тақырыбын қалай алуға болады

Енді мән бойынша бүкіл парақты емес, ағымдағы кестенің координаталарын алуды үйренейік. Бір сөзбен айтқанда, тақырыптарды алу үшін D9 орнына 5277 мәнін табу керек:

• кесте бағаны бойынша – наурыз;
• желі үшін – Өнім4.

Бұл мәселені шешу үшін біз C2 және C3 ұяшықтарында бұрыннан алынған мәндері бар формуланы қолданамыз. Мұны істеу үшін біз мұны істейміз:

Нәтижесінде кестенің ішкі координаталары мән бойынша алынады – наурыз; 4-өнім:

Excel ауқымында бірдей мәндерді табу

Кесте мәндерінің арасында көшірмелердің бар-жоғын тексеру үшін біз көшірмелердің бар екендігі туралы хабарлай алатын және олардың санын есептей алатын формуланы жасаймыз. Ол үшін E2 ұяшығына формуланы енгізіңіз:

Сонымен қатар, кесте бөлімінің ауқымы үшін шартты пішімдеу ережесін жасаймыз:

Көріп отырғаныңыздай, егер көшірмелер болса, тақырыптар формуласы тақырыпты бірінші көшірмеден көлденеңінен (солдан оңға қарай) алады. Ал жолдың атын (нөмірін) алу формуласы бірінші көшірмедегі санды тігінен (жоғарыдан төменге қарай) алады. Түзету үшін бұл шешім 2 жолы бар:

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

Мұнда бірінші көшірменің тік координаталары дұрыс көрсетіледі (жоғарыдан төменге қарай) - парақ және тамыз үшін I7; Кестеге арналған 2-тармақ. Бұл опцияны келесі соңғы мысалға қалдырайық.

Excel ауқымындағы ең жақын мәнді табу

Бұл кесте әлі де керемет емес. Өйткені, талдау кезінде сіз оның барлық құндылықтарын нақты білуіңіз керек. Егер формула кестеде B1 ұяшығына енгізілген санды таппаса, қате қайтарылады - #VALUE! Формула үшін, егер бастапқы сан кестеде болмаса, кестедегі ең жақын мәнді таңдау өте қолайлы болар еді. Кестелерді талдауға арналған осындай бағдарламаны жасау үшін F1 ұяшығына жаңа формуланы енгізіңіз:

Осыдан кейін барлық басқа формулаларда B1 орнына сілтеме F1 болуы керек!Сондай-ақ шартты пішімдеудегі сілтемені өзгерту қажет. Таңдаңыз: «БАСТЫ» - «Стильдер» - «Шартты пішімдеу» - «Ережелерді басқару» - «Ережені өңдеу». Және мұнда параметрлерде B1 орнына F1 көрсетіңіз. Бағдарламаның жұмысын тексеру үшін В1 ұяшығына кестеде жоқ санды енгізіңіз, мысалы: 8000. Бұл соңғы нәтижеге әкеледі:

Енді сіз кез келген бастапқы мәнді енгізе аласыз және бағдарламаның өзі кестедегі ең жақын нөмірді таңдайды. Содан кейін ол ағымдағы мән үшін баған тақырыбы мен жол атауын көрсетеді. Мысалы, егер сіз 5000 санын енгізсеңіз, біз жаңа нәтиже аламыз:

Біздің Excel бағдарламасы 4965-тің бастапқы мәніне ең жақын мәнін тапты - 5000. Мұндай бағдарлама бизнес-жоспарлауда, мақсат қоюда, ұтымды шешім табуда және т.б. әртүрлі аналитикалық есептерді автоматты түрде шешуге пайдалы болуы мүмкін. Алынған жолдар мен бағандар жаңа Excel формулалары арқылы есептердің осы түрінің есептеу мүмкіндіктерін одан әрі кеңейтуге мүмкіндік береді.

Көбінесе Excel бағдарламасында деректермен жұмыс істеу кезінде ақпараттың бір бөлігін таңдаулы түрде жоюға немесе жасыруға тура келеді, көбінесе бұл белгілі бір сөздерді, әріптерді, сандарды, белгілерді немесе олардың комбинацияларын қамтитын немесе жоқ жолдар немесе бағандар. Бұлар көмектеседі стандартты құралдарІздеу, сүзгі және кеңейтілген сүзгі сияқты Excel. Егер бұл құралдар мәселені шешу үшін жеткіліксіз болса, VBA көмекке келеді.

Excel жолында берілген сөздерді қалай табуға болады? Excel бағдарламасында сөздерді, әріптерді, сандарды және белгілерді табыңыз

Берілген сөзді, әріпті, санды, таңбаны немесе олардың комбинациясын іздеудің ең қарапайым жолы стандартты іздеу болып табылады. Барлық іздеу параметрлері «Табу және ауыстыру» диалогтық терезесіне енгізіледі, оны негізгі мәзірден немесе «Ctrl+f» пернелер тіркесімі арқылы шақыруға болады (мұндағы f – бірінші әріп). Ағылшын сөзітабу - табу). Тұрақты іздеуден басқа, ауыстыру арқылы іздеуді де орындауға болады.

Excel бағдарламасында белгілі бір сөзді, әріпті, санды немесе таңбаны табу үшін сүзгілерді пайдаланыңыз

Пайдаланушы анықтаған ақпаратты қамтитын жолдарды таңдау үшін сүзуді пайдалануға болады. Excel 2007 және одан жоғары нұсқаларында, мысалы, әдеттегі мәтін сүзгісіне қосымша, ұяшықтарды толтыру түсі мен қаріп түсі бойынша сүзгі бар. Мәтін сүзгісі «тең...», «тең емес...», «...мен басталады», «...мен аяқталады», «құрамында...», сияқты шарттарды пайдалануға мүмкіндік береді. «құрамында... жоқ». Барлық қажетті жолдар сүзгіден өткеннен кейін олармен кез келген әрекеттерді орындауға болады, соның ішінде жолдарды жою.

VBA Like операторы арқылы Excel бағдарламасында белгілі бір жолдарды бағдарламалы түрде қалай табуға және жоюға болады?

Әртүрлі себептермен стандартты Excel құралдарыбелгілі бір мәселелерді шешуге әрқашан қолайлы бола бермейді. Төменде пайдаланылған ауқымның ұяшықтарынан үлгімен көрсетілген мәтінді табуға және көрсетілген мәтіні бар ұяшықты қамтитын белсенді жұмыс парағының бүкіл жолын жоюға мүмкіндік беретін макростың бағдарлама коды берілген. Іздеу мәтіні "Үлгі" айнымалысына тағайындалады үлгіге сәйкес келетін арнайы таңбалар .

Sub Delete_Strok_Po_Shablonu() Dim r Ұзындығы сияқты, Бірінші қатар ұзын, Соңғы қатар Ұзындығы Аймақ сияқты, iRow диапазон сияқты, Ұяшық диапазон сияқты Күңгірт Үлгі Жол үлгісі = "іздеу мәтінін осы жерге енгізіңіз" Аймақты орнату = ActiveSheet.UsedRange FirstRow = Аймақ Жол LastRow = Region.Row - 1 + Region.Rows.Count үшін r = Соңғы қатардан бірінші қатарға қадам -1 iRow = Region.Rows(r - FirstRow + 1) iRow.Ұяшықтарындағы әрбір ұяшық үшін Үлгі ұнаса, содан кейін жолдар. ( r).Соңы болса, келесі ұяшық Келесі r End Ішкі болса жою

Бұл макрос қажетті сөз тіркестерін табу үшін анық емес іздеуді және жолдарды үлгімен салыстыруға мүмкіндік беретін Like VBA салыстыру операторын пайдаланады. Жолдарды салыстыру кезінде бұл оператор үлкен және кіші әріптерді ажыратады және салыстыру нәтижесі нұсқауға байланысты болады. Салыстыру опциясы .

VBA функциялары Instr және Find ұқсас мәселелерді шешу үшін де пайдаланылуы мүмкін.

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

Қайырлы күн, құрметті Хабро тұрғындары!

Уақыт өте келе кейбіреулеріміз (бәлкім, кейбіреулері) үй шаруашылығының бюджетін құрастыру мен талдаудан бастап жұмысқа, оқуға және т. Мүмкін бұл үшін ең қолайлы құрал Microsoft Excel(немесе басқа аналогтар болуы мүмкін, бірақ олар сирек кездеседі).

Іздеу маған Хабре туралы ұқсас тақырыпта бір ғана мақала берді - «Google SpreadSheet-тегі формулаларды пайдаланатын Талмуд». Ол береді жақсы сипаттама excel-де жұмыс істеуге арналған негізгі нәрселер (бірақ бұл Excel-дің өзі туралы 100% емес).

Осылайша, сұраныстардың/тапсырмалардың белгілі бір пулын жинақтап, оларды теріп, ұсыну идеясы пайда болды. мүмкін шешімдер(бәрі мүмкін емес, бірақ тез нәтиже береді).

Біз пайдаланушылар жиі кездесетін мәселелерді шешу туралы сөйлесетін боламыз.

Шешімдердің сипаттамасы келесідей құрылымдалған: бірте-бірте күрделене түсетін бастапқы тапсырмадан тұратын жағдай беріледі және әр қадам үшін түсіндірмелермен егжей-тегжейлі шешім беріледі. Функциялардың атаулары орыс тілінде беріледі, бірақ орыс тіліндегі түпнұсқа атауы бірінші айтылғанда жақша ішінде беріледі. Ағылшын(тәжірибе бойынша, пайдаланушылардың басым көпшілігінде орыс нұсқасы орнатылған).

Case_1: Логикалық функциялар және сәйкес функциялар
«Менде кестеде мәндер жинағы бар және белгілі бір шарт/шарттар жинағы орындалған кезде белгілі бір мән көрсетілуі керек» (c) Пайдаланушы

Деректер әдетте кесте түрінде беріледі:

Шарты:

• егер «Саны» бағанындағы мән 5-тен үлкен болса,
• содан кейін «Нәтиже» бағанында «Тапсырыс қажет емес» мәнін көрсету керек,

Логикалық формулаларға сілтеме жасайтын және формулада алдын ала жазатын кез келген мәндерді шешімде шығара алатын «IF» формуласы бізге көмектеседі. Кез келген мәтін мәндері тырнақшалар арқылы жазылатынын ескеріңіз.

Формула синтаксисі келесідей:
ЕГЕР(логикалық_өрнек, [шын_егер_мән], [жалған_егер_мән])

• Логикалық өрнек - ШЫН немесе ЖАЛҒАН деп есептелетін өрнек.
• Мән_егер_шындық - логикалық өрнек ақиқат болса, басып шығарылатын мән
• Мән_егер_жалған - логикалық өрнегі жалған болса басып шығарылатын мән

Шешім формуласының синтаксисі:

=Егер(C5>5, “Тапсырыс қажет емес”, “Тапсырыс қажет”)

Шығаруда біз нәтиже аламыз:

Шарт күрделірек болады, мысалы, 2 немесе одан да көп шарттарды орындау:

• егер «Саны» бағанындағы мән 5-тен үлкен болса және «Түр» бағанындағы мән «А» болса

Бұл жағдайда біз енді тек «Егер» формуласын қолданумен шектеле алмаймыз, оның синтаксисіне басқа формула қосу керек; Бұл тағы бір логикалық «ЖӘНЕ» формуласы болады.
Формула синтаксисі келесідей:
ЖӘНЕ(логикалық_мән1, [логикалық_мән2], ...)

• Логикалық_мән1-2, т.б. - сыналатын шарт, оны бағалау TRUE немесе FALSE мәнін береді

Нәтижені D2 ұяшығына шығару:

=Егер(ЖӘНЕ(C2>5,B2=“A”),1,0)

Осылайша, 2 формуланың комбинациясын қолдана отырып, біз өз мәселеміздің шешімін табамыз және нәтиже аламыз:

Тапсырманы күрделендіріп көрейік – жаңа шарт:

• егер «Саны» бағанындағы мән 10 және «Түрі» бағанындағы мән «А» болса
• немесе Саны бағанындағы мән 5-тен үлкен немесе оған тең, ал Түр мәні B
• онда «Нәтиже» бағанында «1» мәнін, әйтпесе «0» мәнін көрсету керек.

Шешім синтаксисі келесідей болады:
Нәтижені D2 ұяшығына шығару:

=Егер(НЕМЕСЕ(ЖӘНЕ(C2=10,B2=“A”); ЖӘНЕ(C2>=5,B2=“B”)),1,0)

Жазбадан көріп отырғаныңыздай, IF формуласында бір НЕМЕСЕ шарты және екі ЖӘНЕ шарты бар. Егер 2-деңгейдегі шарттардың кем дегенде біреуінде «ШЫН» мәні болса, «Нәтиже» бағанында «1» нәтижесі көрсетіледі, әйтпесе «0» болады.
Нәтиже:

Енді келесі жағдайға көшейік:
«Шарт» бағанындағы мәнге байланысты ол көрсетілуі керек деп елестетіп көрейік белгілі бір шарт«Нәтиже» бағанында мәндер мен нәтиже арасындағы сәйкестік төменде берілген.
Шарты:

• 1 = А
• 2 = B
• 3 = B
• 4 = G

«Егер» функциясын пайдаланып мәселені шешу кезінде синтаксис келесідей болады:

=ЕСЕР(A2=1,“A”, ЕГЕР(A2=2,“B”, ЕГЕР(A2=3,“C", ЕГЕР(A2=4,“D”,0))))

Нәтиже:

Көріп отырғаныңыздай, мұндай формуланы жазу өте ыңғайлы және қиын емес, сонымен қатар тәжірибесіз қолданушыға қателік туындаған жағдайда оны өңдеуге біраз уақыт кетуі мүмкін.
Бұл тәсілдің кемшілігі оның аздаған шарттарға жарамдылығы болып табылады, өйткені олардың барлығын қолмен енгізу керек және біздің формуламызды үлкен өлшемдерге «көптеген» дегенмен, бұл тәсіл мәндердің толық «қошқылдығымен» ерекшеленеді; және қолданудың әмбебаптығы.

Балама шешім_1:
ТАҢДАУ формуласын қолдану
Функция синтаксисі:
ТАҢДАУ(индекс_саны, мән1, [мән2], ...)

• Индекс_саны - таңдалған мән аргументінің нөмірі. Индекс нөмірі 1 мен 254 арасындағы сан, формула немесе 1 мен 254 арасындағы санды қамтитын ұяшыққа сілтеме болуы керек.
• Мән1, мән2,... - 1-ден 254-ке дейінгі мән аргументтері, олардың ішінен «ТАҢДАУ» функциясы индекс нөмірін пайдаланып орындалатын мәнді немесе әрекетті таңдайды. Аргументтер сандар, ұяшық сілтемелері, арнайы атаулар, формулалар, функциялар немесе мәтін болуы мүмкін.

Оны пайдалану кезінде көрсетілген мәндерге байланысты шарттар нәтижелерін бірден енгіземіз.
Шарты:

• 1 = А
• 2 = B
• 3 = B
• 4 = G

Формула синтаксисі:

=ТАҢДАУ(A2, “A”, “B”, “C”, “D”)

Нәтиже жоғарыдағы IF функция тізбегі шешіміне ұқсас.
Бұл формуланы қолдану кезінде келесі шектеулер қолданылады:
«A2» ұяшығына (индекс нөмірі) тек сандарды енгізуге болады және нәтиже мәндері 1-ден 254 мәнге дейін өсу ретімен көрсетіледі.
Басқаша айтқанда, функция «A2» ұяшығында өсу ретімен 1-ден 254-ке дейінгі сандар болған жағдайда ғана жұмыс істейді және бұл формуланы пайдалану кезінде белгілі бір шектеулер қояды.
Сол. егер біз 5 санын көрсеткенде «G» мәнін көрсетуді қаласақ,

• 1 = А
• 2 = B
• 3 = B
• 5 = G

онда формула келесі синтаксиске ие болады:
Нәтижені B2 ұяшығына шығару:

=ТАҢДАУ(A31, “A”, “B”, “C”, “D”)

Көріп отырғаныңыздай, формуладағы «4» мәнін бос қалдырып, «G» нәтижесін «5» сериялық нөміріне көшіру керек.

Балама шешім_2:
Міне, біз ең танымалдардың біріне келдік Excel функциялары, оны меңгеру кез келген кеңсе қызметкерін автоматты түрде «тәжірибелі excel пайдаланушысына» айналдырады /сарказм/.
Формула синтаксисі:
VLOOKUP(іздеу_мәні, кесте, баған_нөмірі, [аралық_іздеу])

• Іздеу_мәні – функция арқылы ізделетін мән.
• Кесте – мәліметтерді қамтитын ұяшықтар ауқымы. Дәл осы ұяшықтарда іздеу жүргізіледі. Мәндер мәтіндік, сандық немесе логикалық болуы мүмкін.
• Баған_саны - сәйкестік болған жағдайда мән алынатын «Кесте» аргументіндегі бағанның нөмірі. Бағандар жалпы парақ торының бойымен (A.B,C,D, т.б.) емес, «Кесте» аргументінде көрсетілген массив ішінде есептелетінін түсіну маңызды.
• Interval_lookup - функция нақты сәйкестікті немесе шамамен сәйкестікті табу керектігін анықтайды.

Маңызды: VLOOKUP функциясы сәйкестікті тек бірінші бірегей жазбадан іздейді, егер іздеу_мәні Кесте аргументінде бірнеше рет болса және әртүрлі мағыналар, содан кейін «VLOOKUP» функциясы тек БІРІНШІ сәйкестікті табады, барлық басқа сәйкестіктердің нәтижелері көрсетілмейді «VLOOKUP» формуласын пайдалану деректермен жұмыс істеудің басқа тәсілімен, атап айтқанда «каталогтарды қалыптастырумен байланысты. ».
Әдістің мәні шарттар мен олардың сәйкес мәндері жазылған негізгі массивтен бөлек нақты нәтижеге «Ізденген_мән» аргументінің сәйкестігінің «каталогын» құру болып табылады:

Содан кейін кестенің жұмыс бөлігінде бұрын толтырылған анықтамалық сілтемесі бар формула жазылады. Сол. «D» бағанындағы каталогта «А» бағанының мәні ізделеді және сәйкестік табылған кезде «В» бағанында «E» бағанының мәні көрсетіледі.
Формула синтаксисі:
Нәтижені B2 ұяшығына шығару:

Нәтиже:

Енді бір кестеден екінші кестеге деректерді тарту қажет болатын жағдайды елестетіп көріңіз және кестелер бірдей емес. Төмендегі мысалды қараңыз

Екі кестенің «Өнім» бағандарындағы жолдар сәйкес келмейтінін көруге болады, бірақ бұл «VLOOKUP» функциясын пайдалануға кедергі емес.
Нәтижені B2 ұяшығына шығару:

Бірақ шешкен кезде біз қарсымыз жаңа мәселе– «В» бағанынан «Е» бағанына оңға жазған формуланы «созғанда» «баған_саны» аргументін қолмен ауыстыруға тура келеді. Бұл көп еңбекті қажет ететін және алғыссыз жұмыс, сондықтан бізге көмекке тағы бір функция келеді - «БАҒАН» (БАҒАН).
Функция синтаксисі:
БАҒАН([сілтеме])

• Сілтеме - баған нөмірін қайтарғыңыз келетін ұяшық немесе ұяшықтар ауқымы.

Егер сіз келесідей жазбаны пайдалансаңыз:

содан кейін функция ағымдағы бағанның нөмірін көрсетеді (формула жазылған ұяшықта).
Нәтиже - VLOOKUP функциясында пайдалануға болатын сан, біз оны қолданамыз және келесі формуланы аламыз:
Нәтижені B2 ұяшығына шығару:

=ТҮРКІНДІ КӨРСЕТУ($A3,$H$3:$M$6, БАҒАН(),0)

"COLUMN" функциясы каталогтағы іздеу бағанының нөмірін анықтау үшін "Баған_саны" аргументі арқылы пайдаланылатын ағымдағы бағанның нөмірін анықтайды.
Сонымен қатар, сіз келесі құрылысты пайдалана аласыз:

«1» санының орнына сіз бағандағы белгілі бір ұяшыққа сілтеме жасағыңыз келмесе, қалаған нәтижені алу үшін кез келген санды (және оны шегеріп қана қоймай, сонымен бірге алынған мәнге қосуға болады) пайдалана аласыз. бізге керек сан.
Нәтиже:

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

• Егер «Каталог» бағанында 1 саны көрсетілсе, деректерді көрсетілген айға сәйкес «Каталог_1» кестесінен, 2 саны болса, «Каталог_2» кестесінен алу керек.

Ойға бірден келетін шешім мынау:

=ЕГЕР($B3=1; ТҮРЛЕНІП КӨРСЕТУ($A3,$G$3:$I$6; БАҒАН()-1,0); ТҮРДЕ КӨРСЕТУ($A3,$K$3:$M$6; БАҒАН()-1;0 ))

Артықшылықтары: каталогтың атауы кез келген нәрсе болуы мүмкін (мәтін, сандар және олардың комбинациясы), кемшіліктері - 3 опциядан көп болса, ол жақсы сәйкес келмейді.
Егер каталог нөмірлері әрқашан сандар болса, келесі шешімді қолданған дұрыс:
Нәтижені C3 ұяшығына шығару:

=VLOOKUP($A3, SELECT($B3,$G$3:$I$6,$K$3:$M$6), COLUMN()-1,0)

Артықшылықтары: формула 254 каталог атауын қамтуы мүмкін, кемшіліктері - олардың атауы қатаң сандық болуы керек.
ТАҢДАУ функциясын қолданатын формуланың нәтижесі:

Бонус: "іздеу_мәні" аргументіндегі екі немесе одан да көп сипаттамаларға негізделген VLOOKUP.
Шарты:

• Әдеттегідей, бізде кесте түрінде деректер массиві бар деп елестетіп көрейік (егер олай болмаса, онда біз белгілі бір сипаттамаларға негізделген массивтен мәндерді алып, оларды басқа кесте түрінде орналастыруымыз керек); .

Екі кесте де төменде көрсетілген:

Кесте пішіндерінен көрініп тұрғандай, әрбір элементтің аты ғана емес (бірегей емес), сонымен қатар белгілі бір сыныпқа жатады және өзінің орау мүмкіндігі бар.
Атау мен сынып пен қаптаманың тіркесімін пайдалана отырып, біз бұл үшін жаңа сипаттаманы жасай аламыз, кестеде деректермен бірге «Қосымша сипаттама» қосымша бағанасын жасаймыз, оны келесі формула арқылы толтырамыз:

«&» белгісін қолдана отырып, біз үш сипаттаманы біріне біріктіреміз (сөздер арасындағы бөлгіш кез келген болуы мүмкін немесе мүлдем болмауы мүмкін, ең бастысы іздеу үшін ұқсас ережені қолдану)
Формуланың аналогы «CONCATENATE» функциясы болуы мүмкін, бұл жағдайда ол келесідей болады:

=CONCATENATE(H3;"_";I3;"_";J3)

Деректер кестесіндегі әрбір жазба үшін қосымша атрибут жасалғаннан кейін біз осы атрибут үшін іздеу функциясын жазуды жалғастырамыз, ол келесідей болады:
Нәтижені D3 ұяшығына шығару:

=ҚАТЕЛІК(VLOOKUP(A2&"_"&B2&"_"$G$2:$K$6,5,0),0)

«VLOOKUP» функциясында «іздеу_мәні» аргументі ретінде біз үш сипаттаманың бірдей комбинациясын қолданамыз (name_class_packing), бірақ біз оны толтыру үшін кестеде алып, оны тікелей аргументке енгіземіз (балама түрде, біз толтыру үшін кестедегі қосымша бағандағы аргумент мәні, бірақ бұл әрекет қажет емес болады).
Қажетті мән табылмаса, «ҚАУІСІЗ» функциясын пайдалану қажет екенін және «VLOOKUP» функциясы бізге «#N/A» мәнін беретінін еске саламын (бұл туралы толығырақ төменде).
Нәтиже төмендегі суретте:

Бұл әдісті сипаттамалардың көп саны үшін қолдануға болады, жалғыз шарт - бұл орындалмаса, нәтиже дұрыс емес болады;

Case_3 Жиымдағы мәнді іздеу немесе VLOOKUP бізге көмектесе алмағанда

Ұяшық массивінде бізге қажетті мәндер бар-жоғын түсінуіміз керек жағдайды қарастырайық.
Тапсырма:

• «Іздеу шарты» бағанында мән бар және оның «Іздеу массиві» бағанында бар-жоғын анықтау керек.

Көрнекі түрде бәрі келесідей көрінеді:

Көріп отырғанымыздай, «VLOOKUP» функциясы бұл жерде әлсіз, өйткені Біз дәл сәйкестікті емес, ұяшықта қажетті мәннің болуын іздейміз.
Мәселені шешу үшін бірнеше функциялардың комбинациясын пайдалану қажет, атап айтқанда:
"Егер"
«ЕГЕР ҚАТЕ»
«ТӨМЕН»
«ТАБУ»

Барлығына қатысты біз бұрын «ЕГЕР» туралы талқылаған болатынбыз, сондықтан «IFERROR» функциясына көшейік.

IFERROR(мән, қате_мән)

• Мән қателер үшін тексерілетін аргумент болып табылады.
• Қателік_мән - формуланы есептеу кезінде қате болған жағдайда қайтарылатын мән. Қателердің келесі түрлері мүмкін: #N/A, #VALUE!, #REF!, #DIV/0!, #NUMBER!, #NAME? және #БОС!.

Маңызды: бұл формула ақпарат массивтерімен және каталогтармен жұмыс істегенде әрдайым дерлік қажет, өйткені Көбінесе сіз іздеген мән каталогта жоқ және бұл жағдайда функция қатені қайтарады. Егер ұяшықта қате көрсетілсе және ұяшық, мысалы, есептеуге қатысты болса, онда ол да қателікпен пайда болады. Сонымен қатар, формула қатені қайтарған ұяшықтарға статистикалық өңдеуді жеңілдететін әртүрлі мәндер тағайындалуы мүмкін. Сондай-ақ, қате болған жағдайда, сіз басқа функцияларды орындай аласыз, бұл массивтермен жұмыс істеу кезінде өте ыңғайлы және тармақталған шарттарды ескере отырып, формулаларды құруға мүмкіндік береді.

«ТӨМЕН»

• Мәтін – кіші әріпке түрлендірілетін мәтін.

Маңызды: «ТӨМЕН» функциясы әріп емес таңбаларды ауыстырмайды.
Формуладағы рөл: «ТАБУ» функциясы мәтіннің регистрін іздейтін және ескеретіндіктен, барлық мәтінді бір регистрге түрлендіру қажет, әйтпесе «шай» «шайға» тең болмайды, т.б. Бұл регистр мәні мәндерді іздеу және таңдау шарты болмаса маңызды, әйтпесе «ТӨМЕН» формуласын пайдалану мүмкін емес, сондықтан іздеу дәлірек болады.

Енді FIND функциясының синтаксисін толығырақ қарастырайық.

ТАБУ(іздеу_мәтін, қаралған_мәтін, [бастапқы_позиция])

• Іздеу_мәтін - табу керек мәтін.
• Іздеу_мәтін - ізделетін мәтінді тапқыңыз келетін мәтін.
• Бастау_позициясы - іздеуді бастайтын белгі. "Көру_мәтін" мәтініндегі бірінші таңба 1 болып нөмірленген. Егер сан көрсетілмесе, ол әдепкі бойынша 1 болады.

Шешім формуласының синтаксисі келесідей болады:
Нәтижені B2 ұяшығына шығару:

=ЕГЕР(ҚАТЕЛІК(ТАБУ(ЖОЛ(A2), LINE(E2),1),0)=0,“сәтсіз”,“бинго!”)

Формуланың логикасын кезең-кезеңімен талдап көрейік:

1. LOWER(A2) – A2 ұяшығындағы Search_Text аргументін кіші әріпке түрлендіреді
2. FIND функциясы LOWER(E2) функциясы арқылы кіші әріппен мәтінге түрлендірілетін Search_Text массивіндегі Search_Text түрлендірілген аргументін іздеуді бастайды.
3. Егер функция сәйкестікті тапса, яғни. сәйкес сөздің/мәннің бірінші таңбасының сериялық нөмірін қайтарады, «Егер» формуласындағы TRUE шарты іске қосылады, себебі алынған мән нөлге тең емес. Нәтижесінде «Нәтиже» бағанында «Бинго!» мәні көрсетіледі.
4. Алайда, функция сәйкестікті таппаса, яғни. сәйкес сөздің/мәннің бірінші таңбасының реттік нөмірі көрсетілмейді және мәннің орнына қате қайтарылады, «IFERROR» формуласына енгізілген шарт іске қосылады және «0» мәніне сәйкес келетін мән қайтарылады «Егер» формуласындағы ЖАЛҒАН шартқа, себебі алынған мән «0». Нәтижесінде «Нәтиже» бағанында «сәтсіз» мәні көрсетіледі.

Жоғарыдағы суреттен көрініп тұрғандай, «LOW» және «FIND» функцияларының арқасында біз таңбалардың жағдайына және ұяшықтағы орналасуына қарамастан қажетті мәндерді табамыз, бірақ біз 5-жолға назар аударуымыз керек.
Іздеу термині «111» мәніне орнатылған, бірақ іздеу массивінде «1111111 cookie файлдары» мәні бар, бірақ формула «Бинго!» нәтижесін береді. Бұл «111» мәні «1111111» мәндер қатарына қосылғандықтан орын алады, нәтижесінде сәйкестік табылды. Әйтпесе бұл жағдайжұмыс істемейді.

Case_4 Бірнеше шарттарға негізделген массивтегі мәнді іздеу немесе VLOOKUP бізге көмектесе алмаған кезде

«Каталог» екі өлшемді массивіндегі «Нәтижелері бар кестеден» мәнді бірнеше шарттарға сәйкес, атап айтқанда «Аты» және «Ай» мәні бойынша табу қажет жағдайды елестетіп көрейік.
Тапсырманың кестелік формасы келесідей болады:

Шарты:

• «Аты» және «Ай» шарттарының сәйкестігіне сәйкес деректерді нәтижемен кестеге тарту керек.

Бұл мәселені шешу үшін «INDEX» және «SEARCH» функцияларының тіркесімі қолайлы.

INDEX функциясының синтаксисі

INDEX(массив, жол_нөмірі, [баған_нөмірі])

• Массив - іздеу шарттары сәйкес келсе, мәндері көрсетілетін ұяшықтар ауқымы.
• Егер алапта тек бір жол немесе бір баған болса, сәйкесінше жол_саны немесе баған_саны аргументі міндетті емес.
• Егер массив бірнеше жолды және бір бағанды ​​алып жатса және жол_саны мен баған_саны аргументтерінің біреуі ғана берілсе, INDEX функциясы массив аргументінің бүкіл жолынан немесе бүкіл бағанынан тұратын массивді қайтарады.
• Жол_нөмірі - мәнді қайтарғыңыз келетін массивтегі жолдың нөмірі.
• баған_нөмірі - мәнді қайтарғыңыз келетін массивтегі бағанның нөмірі.

Басқаша айтқанда, функция «Массив» аргументіндегі көрсетілген массивтен «Жол_саны» және «Баған_саны» аргументтерінде көрсетілген координаттардың қиылысында орналасқан мәнді қайтарады.

MATCH функциясының синтаксисі

SATCH(іздеу_мәні, іздеу_жиымы, [сәйкестік_түрі])

• Іздеу_мәні - іздеу_массив аргументіндегі мәндерге сәйкес келетін мән. Іздеу_мәні аргументі мән (сан, мәтін немесе логикалық) немесе осындай мәнді қамтитын ұяшыққа сілтеме болуы мүмкін.
• Looked_array - іздеу орындалатын ұяшықтар ауқымы.
• сәйкестік_түрі - қосымша аргумент. Сан -1, 0 немесе 1.

MATCH функциясы іздейді көрсетілген элементұяшықтар ауқымында және сол элементтің ауқымдағы салыстырмалы орнын қайтарады.
«INDEX» және «SEARCH» функцияларының комбинациясын пайдаланудың мәні мынада: біз «координаталық осьтер» бойымен мәндердің координаттарын олардың аты бойынша іздейміз.
Y осі «Аты» бағаны, ал X осі «Айлар» жолы болады.

Формула бөлігі:

СӘЙКЕСТІК($A4,$I$4:$I$7,0)

санды Y осі бойымен қайтарады, бұл жағдайда ол 1-ге тең болады, өйткені "A" мәні ізделетін ауқымда бар және сол ауқымда "1" салыстырмалы орны бар.
формуланың бөлігі:

СӘйкес (B$3,$J$3:$L$3,0)

#N/A қайтарады, себебі "1" мәні қаралатын ауқымда емес.

Осылайша, біз «INDEX» функциясы «Массив» аргументінде іздеу үшін пайдаланатын нүктенің координаталарын алдық (1; #N/A).
B4 ұяшығы үшін толық жазылған функция келесідей болады:

=ИНДЕКС($J$4:$L$7, МАТЧ($A4,$I$4:$I$7,0), МАТЧ(B$3,$J$3:$L$3,0))

Негізінде, егер біз қажетті мәннің координаттарын білсек, функция келесідей болады:

=ИНДЕКС($J$4:$L$7,1,#Жоқ))

“Баған_саны” аргументінде “#Жоқ/А” мәні болғандықтан, “B4” ұяшығы үшін нәтиже сәйкес болады.
Нәтижеден көрініп тұрғандай, нәтиже бар кестедегі барлық мәндер анықтамалықпен сәйкес келмейді және нәтижесінде кестедегі кейбір мәндер «#N/A» түрінде көрсетілгенін көреміз. , бұл деректерді әрі қарай есептеулер үшін пайдалануды қиындатады.
Нәтиже:

Бұл жағымсыз әсерді бейтараптандыру үшін біз бұрын оқыған «IFERROR» функциясын қолданамыз және қате болған жағдайда қайтарылған мәнді «0» мәніне ауыстырамыз, содан кейін формула келесідей болады:

=ҚАТЕЛІК(ИНДЕКС($J$4:$L$7, СӘЙКЕСТІК($A4,$I$4:$I$7,0), MATCH(B$3,$J$3:$L$3,0)),0)

Нәтижені көрсету:

Суретте көріп отырғаныңыздай, «#N/A» мәндері бұдан былай нәтижелер кестесіндегі мәндерді пайдалана отырып, кейінгі есептеулерімізге кедергі келтірмейді.

Case_5 Сандар ауқымындағы мәнді табу

Белгілі бір диапазонға енгізілген сандарға белгілі бір белгі беру керек деп елестетіп көрейік.
Шарты:
Өнімнің өзіндік құнына байланысты оған белгілі бір санат берілуі керек
Егер мән ауқымда болса

• 0-ден 1000-ға дейін = A
• 1001-ден 1500-ге дейін = В
• 1501 жылдан 2000 жылға дейін = В
• 2001 жылдан 2500 = Г
• 2501 = D артық

ІЗДЕУ функциясы жолдан, бағаннан немесе жиымнан мәнді қайтарады. Функцияның екі синтаксистік формасы бар: векторлық және массив формасы.

ІЗДЕУ(іздеу_мәні, іздеу_векторы, [нәтиже_векторы])

• іздеу_мәні - ІЗДЕУ функциясы бірінші векторда іздейтін мән. Іздеу_мәні сан, мәтін, логикалық, атау немесе мән сілтемесі болуы мүмкін.
• Watch_vector - бір жолдан немесе бір бағаннан тұратын ауқым. Іздеу_векторы аргументіндегі мәндер мәтін, сандар немесе логикалық мәндер болуы мүмкін.
• Көрініс_векторы аргументіндегі мәндер өсу ретімен болуы керек: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; әйтпесе, ІЗДЕУ функциясы дұрыс емес нәтижені қайтаруы мүмкін. Төменгі жағындағы мәтін және бас әріпэквивалент болып саналады.
• нәтиже_векторы - бір жолдан немесе бағаннан тұратын ауқым. Нәтиже_векторы іздеу_векторымен бірдей өлшем болуы керек.

=ҚАРАУ(E3,$A$3:$A$7,$B$3:$B$7)

«Көрініс_векторы» және «Нәтиже_векторы» аргументтерін массив түрінде жазуға болады - бұл жағдайда оларды Excel парағында бөлек кестеде көрсету қажет емес.
Бұл жағдайда функция келесідей болады:
Нәтижені B3 ұяшығына шығару:
=ҚАРАУ(E3;(0;1001;1501;2001;2501);("A","B","C","D","D"))

Case_6 Сандардың сипаттамалары бойынша қосындысы

Белгілі бір сипаттамаларға негізделген сандарды қосу үшін сіз үш түрлі функцияны пайдалана аласыз:
SUMIF – тек бір атрибут бойынша қосындылар
SUMIFS – бірнеше сипаттамалар бойынша қосындылар
СУПРОДУКТ – бірнеше сипаттамалар бойынша қосындылар
Сондай-ақ, "SUM" формуласы массивке көтерілгенде, "SUM" және массив формуласы функциясын пайдаланатын опция бар:
((=СУМ(()*()))
бірақ бұл тәсіл өте ыңғайсыз және «SUMPRODUCT» формуласымен толық функционалдылықпен қамтылған
Енді «SUMPRODUCT» синтаксисі туралы қосымша мәліметтер алу үшін:

SUMPRODUCT(1-массив, [массив2], [массив3],...)

• Массив1 - құрамдастарын көбейту керек, содан кейін нәтижелерді қосу керек бірінші массив.
• Массив2, массив3… - 2-ден 255 массивке дейін, олардың құрамдастарын көбейтіп, содан кейін нәтижелерді қосу керек.

Шарты:

• Белгілі бір кезеңдегі әрбір өнім бойынша жөнелтулердің жалпы құнын табыңыз:

Мәліметтері бар кестеден көріп отырғанымыздай, өзіндік құнын есептеу үшін бағаны санға көбейтіп, таңдау шарттарын қолдана отырып, алынған мәнді нәтижесі бар кестеге көшіру қажет.
Дегенмен, SUMPROIZ формуласы формула ішінде мұндай есептеулерді жүргізуге мүмкіндік береді.
Нәтижені B4 ұяшығына шығару:

=ҚОРЫТЫНДЫ(($A4=$H$3:$H$11)*($K$3:$K$11>=B$3)*($K$3:$K$11
Формуланы бөліктерге бөліп қарастырайық:
– нәтижесі бар кестенің «Аты» бағанындағы деректермен кестенің «Аты» бағанында таңдау шартын орнату

($K$3:$K$11>=B$3)*($K$3:$K$11 – біз уақыт шеңберіне негізделген шарт қоямыз, күн ағымдағы айдың бірінші күнінен үлкен немесе оған тең, бірақ келесі айдың бірінші күнінен аз. Сол сияқты, шарт нәтижесі бар кестеде, массив деректері бар кестеде.
– деректер кестесіндегі «Саны» және «Баға» бағандарын көбейтіңіз.
Бұл функцияның сөзсіз артықшылығы - шарттарды жазудың еркін тәртібі, олар кез келген тәртіпте жазылуы мүмкін, бұл нәтижеге әсер етпейді.
Нәтиже:

Енді шартты қиындатып, «cookie файлдары» атауын таңдау тек «кіші» және «үлкен» сыныптарында болады деген талапты қосайық, ал «ролл» атауы үшін «кептелісі бар» сыныбынан басқасының барлығында болады:

Нәтижені B4 ұяшығына шығару:

=ҚОРЫТЫНДЫ(($A4=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11
Cookie файлдарын таңдау формуласына жаңа шарт қосылды:

(($I$3:$I$11=“кіші”)+($I$3:$I$11=“үлкен”))

– көріп отырғаныңыздай, бір бағандағы екі немесе одан да көп шарттар «+» белгісін пайдаланып, шарттарды қосымша жақшаға алып, жеке топқа бөлінеді.
Сондай-ақ орамдар бойынша таңдау формуласына жаңа шарт қосылды:

=ҚОРЫТЫНДЫ(($A5=$H$3:$H$11)*($J$3:$J$11>=B$3)*($J$3:$J$11 “кептелісі бар”);($L$3:$L$11)*($K$3:$K$11))

Бұл:

($I$3:$I$11<>«тосаппен»)

– шын мәнінде, бұл формулада таңдау шартын cookie файлдары арқылы таңдау кезіндегідей жазуға болады, бірақ содан кейін формулада үш шартты тізімдеу керек еді, бұл жағдайда ерекшелік жазу оңайырақ. - «кептеліспен» тең емес бұл үшін біз « мәнін қолданамыз<>».
Жалпы, егер ерекшеліктер/сыныптар топтары алдын ала белгілі болса, онда барлық шарттарды функцияға толтырып, оны көбейтіп жазғанша, анықтамалықтар жасап, осы топтарға біріктірген дұрыс.
Нәтиже:

Міне, біз қысқа нұсқаулығымыздың соңына келдік, ол шын мәнінде әлдеқайда ұзағырақ болуы мүмкін, бірақ мақсат әлі де белгілі бір (бірақ әлдеқайда қызықты жағдайлар) шешімін сипаттау емес, ең көп таралған жағдайлардың шешімін ұсыну болды. ).
Нұсқаулық біреуге Excel көмегімен мәселелерді шешуге көмектеседі деп үміттенемін, өйткені бұл менің еңбегім бекер болған жоқ дегенді білдіреді!

Уақытыңыз үшін рахмет!