Жиым өрнектер бойынша нұсқаулықтар мен мысалдар (ағылшын тілінде)

Жиым өрнектер бойынша нұсқаулықтар мен мысалдар (ағылшын тілінде)

Excel бағдарламасын кәсіби пайдаланушы болғыңыз келсе, жиым емес формулаларды қолданбай орындай алмайтын есептеулерді орындайтын жиым формулаларын қолдану жолы туралы білуіңіз қажет. Келесі Колин Вилкокс арқылы жазылған Excel бағдарламасын кәсіби пайдаланушы бағандарының қатарына негізделген және Джон Валкенвач арқылы жазылған кітап, Excel MVP Excel 2002 формулалары кітабының 14 және 15-тарауларына бейімделген.

Бұл мақалада:

Жиым формулалары туралы үйрену

Жиым формулаларын неліктен қолданады?

Жиымдар мен жиым формулаларына жылдам кіріспе

Оны орындап көріңіз!

Көп ұяшықтағы жиым формуласын жасау

Жалғыз ұяшықтағы жиым формуласын жасау

Жиым формуласы синтаксисіне көзқарас

Жиым формулаларын енгізуге және өзгертуге арналған ережелер

Жиым формуласын кеңейту

Жиым формулаларын қолданудың кемшіліктері

Жиымның тұрақты мәндері туралы үйрену

Жиым тұрақты мәндеріне қысқаша кіріспе

Бір өлшемді және екі өлшемді тұрақты мәндерді жасау

Көлденең тұрақты мәнді жасау

Тік тұрақты мәнді жасау

Екі өлшемді тұрақты мәнді жасау

Формулаларда тұрақты мәндерді пайдалану

Жиым тұрақты мәні синтаксисіне көзқарас

Тұрақты мәндерде қолдануға болатын элементтер

Жиымның тұрақты мәндерін атау

Жиым тұрақты мәндерінің ақаулықтарын жою

Әрекеттегі жиым тұрақты мәндері

Жиымдағы әрбір элементті көбейту

Жиымдағы элементтерді квадратқа енгізу

Бір өлшемді жолды ауыстыру

Бір өлшемді бағанды ауыстыру

Екі өлшемді тұрақты мәнді ауыстыру

Негізгі жиым формулаларын жұмысқа қою

Жұмысқа кірісу

Бар мәндерден жиымдарды және жиым тұрақты мәндерін жасау

Бар мәндерден жиым жасаңыз.

Бар мәндерден жиым тұрақты мәнін жасаңыз.

Ұяшықтардың ауқымындағы таңбаларды санау

Ауқымдағы n ең кішкентай мәнін табу

Ауқымдағы n ең үлкен мәнді табу

Ұяшықтар ауқымындағы ең ұзақ мәтіндік жолды табу

Қосымша жиым формулаларын жұмысқа қою

Қате мәндерді қамтитын ауқымды қосу

Ауқымдағы қате мәндер санын санау

Шарттарға негізделген мәндерді қосу

Нөлдерді алып тастайтын орташа мәнді есептейді

Ұяшықтардың екі ауқымының арасындағы айырмашылықтардың санын есептеу

Ауқымдағы ең үлкен мәннің орнын табу

Жиым формулалары туралы үйрену

Осы бөлім жиым формулаларымен таныстырады және оларды енгізу, өңдеу және олардың ақаулықтарын жою жолын түсіндіреді.

Жиым формулаларын неліктен қолданады?

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

  • Ұяшықтардың ауқымында қамтылатын таңбалардың санын есептеңіз.

  • Жоғарғы және төменгі шек арасында орналасқан сандардың ауқымындағы ең төмен мәндер сияқты нақты шарттарға сәйкес келетін сандарды ғана қосыңыз.

  • Мәндердің ауқымындағы әрбір n-мәнді қосыңыз.

Ескерту : «CSE» формулалары ретінде байланыстырылған жиым формулаларын көруге болады, себебі оларды жұмыс кітаптарына енгізу үшін CTRL+SHIFT+ENTER пернелер тіркесімдері басылады.

Жиымдар мен жиым формулаларына жылдам кіріспе

Кішкентай болса да бағдарламалауды жасасаңыз, сіз мүмкін жиым термині арқылы іске қосасыз. Біздің мақсаттарымыз бойынша жиым элементтердің жинағы. Excel бағдарламасында сол элементтер жалғыз жолда (бір өлшемді көлденең жиым деп аталады), бағанда (бір өлшемді тік жиым) немесе көп мәнді жолдар мен бағандарда (екі өлшемді жиым) орналасады. Excel бағдарламасында үш өлшемді жиымдарды немесе жиым формулаларын жасай алмайсыз.

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

Келесі бөлімдегі мысал көп ұяшықтағы және жалғыз ұяшықтағы жиым формулаларын жасау жолын көрсетеді.

Оны орындап көріңіз!

Бұл жаттығу сату мәндерінің жиынын есептеу үшін көп ұяшықтағы және жалғыз ұяшықтағы жиым формулаларын қолдану жолын көрсетеді. Қадамдардың алғашқы жинағы көп ұяшықтағы мәнді қорытындылардың жинағын есептеу үшін қолданады. Екінші жинақ жалпы жиынды көрсету үшін жалғыз ұяшықтағы формуланы пайдаланады.

Көп ұяшықтағы жиым формуласын жасау

  1. Жаңа бос жұмыс кітабын ашыңыз.

  2. Мысал жұмыс парағы деректерін көшіріп, оны A1 ұяшығынан басталатын жаңа жұмыс кітабына қойыңыз.

Сатушы

Машина түрі

Сатылған саны

Өнім бағасы

Жалпы сатулар

Рахимов

Тұрсынов

5

2200

Ахметов

4

1800

Қали

Тұрсынов

6

2300

Ахметов

8

1700

Қадір

Тұрсынов

3

2000

Ахметов

1

1600

Асылбек

Тұрсынов

9

2150

Ахметов

5

1950

Насредин

Тұрсынов

6

2250

Ахметов

8

2000

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

  2. Жиымдағы мәндерді көбейту үшін (ұяшық ауқымы C2 мен D11 аралығында), E2 және E11 аралығындағы ұяшықтарды таңдап, формула жолағына келесі формуланы енгізіңіз:

    =C2:C11*D2:D11

  3. CTRL+SHIFT+ENTER пернелер тіркесімін басыңыз.

Excel бағдарламасы мәнді жақшалармен ({ }) қоршайды және таңдалған ауқымның әрбір ұяшығына формула нұсқасын орналастырады. Бұл жылдам орындалады, осылай E бағанында көрсетілетін мән — әрбір сатушыға арналған әрбір машина түріне арналған жалпы сатулар мөлшері.

Үлгі деректер

Жалғыз ұяшықтағы жиым формуласын жасау

  1. Жұмыс кітабының A13 ұяшығына Жалпы сатулар деп енгізіңіз.

  2. B13 ұяшығына келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =SUM(C2:C11*D2:D11)

Мұндай жағдайда Excel бағдарламасы жиымдағы мәндерді көбейтеді (ұяшық ауқымы C2 мен D11 аралығында) және одан кейін қорытындыларды қосу үшін SUM функциясын қолданады. Нәтиже сатулардың жалпы жиыны $111,800. Осы мысал осы формуланың түрінің қаншалықты күшті бола алатынын көрсетеді. Мысалы, деректердің 15,000 жолы бар делік. Осы деректердің бөлігін немесе барлығын жалғыз ұяшықта жиым формуласын жасау арқылы қосуға болады.

Содай-ақ, жалғыз ұяшықтағы формула (B13 ұяшығындағы) көп ұяшықтағы формуладан (E2 мен E11 аралығындағы ұяшықтар) толығымен басқаша. Бұл жиым формулаларының тағы да бір артықшылығын — оның икемділігі екенін көрсетеді. Е бағанындағы формулаларды өзгерту немесе жалғыз ұяшықтағы формулаға әсер етпей сол бағанның барлығын жою сияқты кез келген әрекеттердің санын алуға болады.

Жиым формулалары сондай-ақ, келесі артықшылықтарды ұсынады:

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

  • Қауіпсіздік.    Көп ұяшықтағы жиым формуласының бөлігін қайта жазуға болады. Мысалы, E3 ұяшығын нұқып, DELETE пернесін басыңыз. Сондай-ақ, ұяшықтардың бүкіл ауқымын (E2 мен E11 аралығындағы ұяшықтар) таңдап, толық жиымға формуланы өзгертуге немесе жиымды солай қалдыруға болады. Қосылған қауіпсіздік шарасы ретінде, формуланың өзгертілгенін растау үшін, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз.

  • Кішкентай файл өлшемдері.    Бірнеше аралық формулалардың орнына жалғыз жиым формуласын жиі қолдана аласыз. Мысалы, осы жаттығу үшін жасалған жұмыс кітабы Е бағанындағы нәтижелерді есептеу үшін бір жиым формуласын қолданады. Егер стандартты формулаларды (=C2*D2 сияқты) қолданатын болсаңыз, бірдей нәтижелерге қол жеткізу үшін 11 әртүрлі формуланы қолдану қажет болады.

Жиым формуласы синтаксисіне көзқарас

Жалпы жиым формулалары стандартты формула синтаксисін қолданады.Олардың барлығы теңдік (=) таңбасымен басталады және жиым формулаларында бекітілген Excel функцияларының кез келгенін пайдалануға болады. Негізгі айырмашылық жиым формуласын қолданған кезде, формуланы енгізу үшін CTRL+SHIFT+ENTER пернелер тіркесімдерін басу қажет. Осылай орындаған кезде, Excel бағдарламасы жиым формуласын жақшалармен қоршайды — жақшаларды қолмен енгізсеңіз, формула мәтіндік жолға түрлендіріледі және ол жұмыс істемейді.

Түсініп алатын келесі жайт — жиым функцияларының қысқа сөз құратыны. Мысалы, бұрын қолданылған көп ұяшықтағы функция келесінің баламасы:

=C2*D2
=C3*D3

және т.с.с. B13 ұяшығындағы жалғыз ұяшықтағы формула барлық сол көбейту амалдарын қысқартады, осыған қоса жалпы жиындарды қосу қажет есеп: =E2+E3+E4 және т.с.с.

Жиым формулаларын енгізуге және өзгертуге арналған ережелер

Жиым формуласын жасауға арналған бастапқы ереже мәнді қайталау: жиым формуласын енгізу немесе өңдеу үшін CTRL+SHIFT+ENTER пернелер тіркесімдерін қажет кезде басыңыз. Осы ереже жалғыз ұяшықтағы және көп ұяшықтағы формулалар үшін бірдей қолданылады.

Көп ұяшықтағы формулалармен қашан жұмыс істесеңіз де, осы ережелерді орындау қажет:

  • Формуланы енгізудің алдында алдында нәтижелерді сақтау үшін ұяшықтардың ауқымын таңдау қажет. Е2 және Е11 аралығындағы ұяшықтарды таңдағанда, көп ұяшықтағы жиым формуласы мысалының 3-қадамында осылай жасадыңыз.

  • Жиым формуласындағы жеке ұяшықтардың мазмұнын өзгертуге болмайды. Осылай жасап көру үшін, үлгі жұмыс кітабындағы Е3 ұяшығын таңдап, DELETE пернесін басыңыз.

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

    Кеңес : Жиым формуласын жою үшін, бүкіл формуланы таңдаңыз (мысалы, =C2:C11*D2:D11), DELETE пернесін басыңыз және одан кейін CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз.

  • Бос ұяшықтарды оған кірістіруге немесе көп мәнді жиым формуласынан ұяшықтарды жоюға болмайды.

Жиым формуласын кеңейту

Кейде жиым формуласын кеңейту қажет болуы мүмкін. (Жиым формуласын қысқартуға болмайтынын есте сақтаңыз.) Бұл үрдіс күрделі емес, бірақ алдыңғы бөлімде тізімделген ережелерді есте сақтау қажет.

  1. Үлгі жұмыс кітабында кез келген мәтінді және негізгі кестеден төмен орналасқан жалғыз ұяшықтағы формулаларды жойыңыз.

  2. Деректердің осы қосымша жолдарын жұмыс кітабына A12 ұяшығынан бастап қойыңыз. Тағайындау пішімдеуін сәйкестендіру үшін жақын жерде көрсетілетін Қою параметрлері түймешігін Түймешік суреті басыңыз.

Құлахметов

Тұрсынов

6

2500

Ахметов

7

1900

Балабеков

Тұрсынов

4

2200

Ахметов

3

2000

Артықбаева

Тұрсынов

8

2300

Ахметов

8

2100

  1. Ағымдағы жиым формуласын (E2:E11) қамтитын ұяшықтардың ауқымын таңдап, жаңа деректерге жақын бос ұяшықтарды (E12:E17) қосыңыз. Басқаша айтқанда, E2:E17 ұяшықтарын таңдаңыз.

  2. Күйді өңдеу үшін, F2 пернесін басыңыз.

  3. Формула жолында C11 ұяшығын C17 ұяшығына, D11 ұяшығын D17 ұяшығына өзгертіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз. Excel бағдарламасы E2 және E11 ұяшықтары арасындағы формуланы жаңартады және формуланың нұсқасын E12 және E17 жаңа ұяшықтарына орналастырады.

    Үлгі деректер

Жиым формулаларын қолданудың кемшіліктері

Жиым формулалары сиқырлы болып көрінуі мүмкін, бірақ оларда кейбір кемшіліктер бар:

  • Байқаусыз CTRL+SHIFT+ENTER пернелер тіркесімін басуды ұмытуыңыз мүмкін. Жиым формуласын енгізгенде немесе өңдегенде осы пернелер тіркесімдерін басуды есте сақтаңыз.

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

  • Компьютердің өңдеу жылдамдығы мен жадына байланысты үлкен жиым формулаларын есептеу төмендетілуі мүмкін.

Жұмыс істейтін жиым формулалары туралы қосымша ақпарат алу үшін төмендегі бөлімді қараңыз:

Беттің жоғарғы жағы

Жиымның тұрақты мәндері туралы үйрену

Осы бөлім жиым тұрақты мәндерін таныстырады және оларды енгізу, өңдеу және олардың ақаулықтарын жою жолын түсіндіреді.

Жиым тұрақты мәндеріне қысқаша кіріспе

Жиым тұрақты мәндері жиым формулаларының бөлігі болып табылады. Элементтердің тізімін енгізу арқылы жиым тұрақты мәндерін жасайсыз және тізімге жақшаларды қолмен ({ }) енгізіңіз, мына сияқты:

={1,2,3,4,5}

Осы мақалада бұрынырақ, жиым формулаларын жасаған кезде CTRL+SHIFT+ENTER пернелер тіркесімдерін басу қажеттігі баса айтылады. Себебі жиым тұрақты мәндері жиым формулаларының бөлігі, тұрақты мәндерді қолмен енгізу арқылы жақшалармен қоршайсыз. Одан кейін сіз формуланы енгізу үшін, CTRL+SHIFT+ENTER пернелер тіркесімдерін басасыз.

Үтірлерді қолдану арқылы элементтерді бөлген кезде, сіз көлденең жиымды (жол) жасаңыз. Нүктелі үтірді қолдану арқылы элементтерді бөлсеңіз, тік жиымды (баған) жасаңыз. Екі өлшемді жиым жасау үшін, әрбір жолдағы элементтерді үтірлерді қолдану арқылы және әрбір жолды нүктелі үтірлерді қолдану арқылы бөлесіз.

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

Бір өлшемді және екі өлшемді тұрақты мәндерді жасау

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

Көлденең тұрақты мәнді жасау

  1. Алдыңғы мысалдардағы жұмыс кітабын пайдаланыңыз немесе жаңа жұмыс кітабын жасаңыз.

  2. A1 және E1 аралығындағы ұяшықтарды таңдаңыз.

  3. Формула жолағына келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    ={1,2,3,4,5}

    Ескерту : Мұндай жағдайда ашық және жабық жақшаларды ({ }) енгізу қажет.

Келесі нәтиже көрсетіледі.

Формуладағы көлденең массивтің тұрақты мәні

Неліктен қолмен сандарды енгізе алмайтыныңыз туралы ойлауыңыз мүмкін. Осы мақаланың соңында Формулаларда тұрақты мәндерді қолдану бөлімі жиым тұрақты мәндерін қолданудың басымдықтарын көрсетеді.

Тік тұрақты мәнді жасау

  1. Жұмыс кітабында бес ұяшықтан тұратын бағанды таңдаңыз.

  2. Формула жолағына келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    ={1;2;3;4;5}

Келесі нәтиже көрсетіледі.

Массив формуласындағы тік массив тұрақты мәні

Екі өлшемді тұрақты мәнді жасау

  1. Жұмыс кітабында ені төрт бағаннан, биіктігі үш жолдан тұратын ұяшықтардың тобын таңдаңыз.

  2. Формула жолағына келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

Төмендегі нәтижені көресіз:

Массив формуласындағы екі өлшемді массивтің тұрақты мәні

Формулаларда тұрақты мәндерді пайдалану

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

  1. Бос жұмыс парағын ашыңыз.

  2. А1 ұяшығынан басталатын келесі кестені көшіріңіз. Тағайындау пішімдеуін сәйкестендіру үшін жақын жерде көрсетілетін Қою параметрлері Түймешік суреті түймешігін басыңыз.

3

4

5

6

7

  1. А3 ұяшығына келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =SUM(A1:E1*{1,2,3,4,5})

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

Массивтің тұрақты мәні бар массив формуласы

85 мәні А3 ұяшығында көрсетіледі. Келесі бөлім формуланың жұмыс істеу жолын түсіндіреді.

Жиым тұрақты мәні синтаксисіне көзқарас

Жаңа ғана қолданылған формула бірнеше бөліктерді қамтиды.

Массив тұрақты мәні бар массив формуласының синтаксисі

1. Функция

2. Сақталған жиым

3. Амалдағыш

4. Жиым тұрақты мәні

Жақша ішіндегі ең соңғы элемент жиым тұрақты мәні: {1,2,3,4,5}. Excel бағдарламасының жиым тұрақты мәндерін жақшалармен қоршамайтынын есте сақтаңыз, сіз осылай жасауыңыз қажет. Сондай-ақ, тұрақты мәнді жиым формуласына қосқаннан кейін формуланы енгізу үшін CTRL+SHIFT+ENTER пернелер тіркесімдерін басуды есте сақтаңыз.

Excel бағдарламасы алдымен жақшаға алынған мәндер бойынша амалдарды орындайтындықтан, орындалатын келесі екі элемент жұмыс кітабында сақталған мәндер (A1:E1) мен амалдағыш. Осы кезеңде формула сақталған жиымдағы мәндерді тұрақты мәнге сәйкестендіру арқылы оларды көбейтеді. Ол келесі формуланың баламасы:

=SUM(A1*1,B1*2,C1*3,D1*4,E1*5)

Ақырында, SUM функциясы мәндерді қосады және 85 қосындысы A3 ұяшығында көрсетіледі:

Сақталған жиымды қолдануды болдырмау үшін және толық амалды жадта сақтау үшін, сақталған жиымды басқа жиым тұрақты мәнімен ауыстырыңыз:

=SUM({3,4,5,6,7}*{1,2,3,4,5})

Осыны орындау үшін, функцияны көшіріп, жұмыс кітабындағы бос ұяшықты таңдап, формуланы формула жолына қойыңыз, одан кейін CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз. Жиым формуласын =SUM(A1:E1*{1,2,3,4,5}) қолданатын бұрынғы жаттығудағы сияқты бірдей нәтижені көресіз.

Тұрақты мәндерде қолдануға болатын элементтер

Жиым тұрақты мәндері сандарды, мәнтінді, логикалық мәндерді (TRUE және FALSE сияқты) және қате мәндерін (#N/A сияқты) қамти алады. Бүтін санды, ондық санды және ғылыми пішімдердердегі сандарды қолдана аласыз. Мәтінді қамтыған кезде, сол мәтінді қос тырнақшаман (") қоршауыңыз қажет.

Жиым тұрақты мәндері қосымша жиымдарды, формулаларды немесе функцияларды қолдана алмайды. Басқаша айтқанда, олар тек қана үтірмен немесе үтірлі нүктелермен бөлектелген мәтінді немесе сандарды қамти алады. Excel бағдарламасы {1,2,A1:D4} or {1,2,SUM(Q2:Z8)} сияқты формуланы енгізген кезде ескерту хабарламасын көрсетеді. Сондай-ақ, сандық мәндер пайыз, доллар таңбаларын, үтірлерді немесе жақшаларды қамти алмайды.

Жиымның тұрақты мәндерін атау

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

  1. Формулалар қойындысындағы Анықталған аттар тобынан Атты анықтау түймешігін басыңыз.
    Атауды анықтау тілқатысу терезесі көрсетіледі.

  2. Атау жолағына 1-тоқсан деп енгізіңіз.

  3. Қатысты жолағына келесі тұрақты мәнді енгізіңіз (жақшаларды қолмен енгізуді есте сақтаңыз):

    ={"Қаңтар","Ақпан","Наурыз"}

    Тілқатысу терезесінің тұрақты мәндері келесі жолмен көрсетілуі қажет:

    Формуласы бар атауды өңдеу диалогтық терезесі

  4. Жарайды түймешігін басыңыз.

  5. Жұмыс парағында үш бос ұяшықтардан тұратын жолды таңдаңыз.

  6. Келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =1-тоқсан

Келесі нәтиже көрсетіледі.

Формула ретінде енгізілген аталған массив

Аталған тұрақты мәнді жиым формуласы ретінде қолданғанда, теңдік таңбасын енгізуді ұмыстпаңыз. Енгізбесеңіз, Excel бағдарламасы жиымды мәтіндік жол ретінде түрлендіреді. Ақырында, мәтін мен сандардың тіркесімдерін қолдана алатыныңызды есте сақтаңыз.

Жиым тұрақты мәндерінің ақаулықтарын жою

Жиым тұрақты мәндері жұмыс істемеген кезде келесі ақаулықтарды қараңыз:

  • Кейбір элементтер дұрыс таңбалармен бөлектелмеуі мүмкін. Үтірді немесе нүктелі үтірді қоюды ұмытып кетсеңіз немесе оны дұрыс емес орынға қойсаңыз, жиым тұрақты мәні дұрыс жасалмауы немесе сіз ескерту хабарламасын көруіңіз мүмкін.

  • Тұрақты мәндегі элементтердің санына сәйкес келмейтін ұяшықтардың ауқымын таңдаған болуыңыз мүмкін. Мысалы, бес ұяшықты тұрақты мәні бар алты ұяшықтан тұратын бағанды таңдасаңыз, бос ұяшықта #N/A қате мәні көрсетіледі. Керісінше, тіптен аз ұяшықтарды қолдансаңыз, Excel бағдаралмасы сәйкес ұяшығы жоқ мәндерді түсіріп тастайды.

Әрекеттегі жиым тұрақты мәндері

Келесі мысалдар жиым формулаларында қолдану үшін жиым тұрақты мәндерін қоюға болатын жолдардың бірнешеуін көрсетеді. Мысалдардың кейбірі жолдарды бағандарға және керісінше түрлендіру үшін TRANSPOSE функциясын қолданады.

Жиымдағы әрбір элементті көбейту

  1. Ені төрт бағаннан, биіктігі үш жолдан тұратын бос ұяшықтардың тобын таңдаңыз.

  2. Келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

Жиымдағы элементтерді квадратқа енгізу

  • Ені төрт бағаннан, биіктігі үш жолдан тұратын бос ұяшықтардың тобын таңдаңыз.

  • Келесі жиым формуласын енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    Керісінше, қақпақ белгісі амалдағышын (^) қолданатын осы жиым формуласын енгізіңіз:

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Бір өлшемді жолды ауыстыру

  1. Бес бос ұяшықтан тұратын бағанды таңдаңыз.

  2. Келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =TRANSPOSE({1,2,3,4,5})

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

Бір өлшемді бағанды ауыстыру

  1. Бес бос ұяшықтан тұратын жолды таңдаңыз.

  2. Келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =TRANSPOSE({1;2;3;4;5})

Тік жиым тұрақты мәнін енгізгенмен, TRANSPOSE функциясы тұрақты мәнді жолға түрлендіреді.

Екі өлшемді тұрақты мәнді ауыстыру

  1. Ені үш бағаннан, биіктігі төрт жолдан тұратын ұяшықтардың тобын таңдаңыз.

  2. Келесі тұрақты мәнді енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =TRANSPOSE({1,2,3,4;5,6,7,8;9,10,11,12})

TRANSPOSE функциясы әрбір жолды бағандардың қатарына түрлендіреді.

Беттің жоғарғы жағы

Негізгі жиым формулаларын жұмысқа қою

Осы бөлім негізгі жиым формулаларының мысалдарын береді.

Жұмысқа кірісу

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

  1. Бар жұмыс кітабын ашыңыз немесе жаңа жұмыс кітабын жасаңыз және оның екі бос жұмыс парағын қамтитын қамтамасыз етіңіз.

  2. Деректерді келесі кестеге көшіріп, А1 ұяшығынан басталатын жұмыс парағына оны қойыңыз.

400

жылдам

1

2

3

4

1200

қоңыр түлкі

5

6

7

8

3200

секіріп өткен

9

10

11

12

475

еріншек

13

4,3

15

16

500

қуатты пайдаланушы

2000

600

1700

800

2700

Аяқталған жұмыс парағы келесі түрде көрсетілуі қажет.

Деректер үлгісін аяқтау

  1. Бірінші жұмыс парағын Деректер деп атаңыз және екінші бос жұмыс парағын Жиымдар деп атаңыз.

Бар мәндерден жиымдарды және жиым тұрақты мәндерін жасау

Келесі мысал әртүрлі жұмыс парақтарындағы ұяшықтар ауқымдарының арасындағы байланыстарды жасау үшін жиым формулаларын қолдану жолын түсіндіреді. Ол сондай-ақ, мәндердің бірдей жинақтарынан жиым тұрақты мәнін жасау жолын да көрсетеді.

Бар мәндерден жиым жасаңыз.

  1. Үлгі жұмыс кітабында Жиымдар жұмыс парағын таңдаңыз.

  2. C1 және E3 ұяшықтары арасындағы ұяшық ауқымын таңдаңыз.

  3. Келесі формуланы формула жолына енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =Data!E1:G3

Келесі нәтиже көрсетіледі.

Массивтер жұмыс парағындағы нәтижелер

Формула деректер жұмыс парағындағы E1 және G3 ұяшықтары арасында сақталған мәндерді байланыстырады. Осы көп ұяшықтағы жиым формуласына керісінше төмендегі сияқты жиымдар жұмыс парағының әрбір ұяшығына бірегей формуланы орналастыру болып табылады.

=Data!E1

=Data!F1

=Data!G1

=Data!E2

=Data!F2

=Data!G2

=Data!E3

=Data!F3

=Data!G3

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

Бар мәндерден жиым тұрақты мәнін жасаңыз.

  1. Жиымдар жұмыс парағында C1 және E3 ұяшықтарын таңдаңыз.

  2. Күйге қосылу немесе өңдеу үшін F2 пернесін басыңыз.

  3. Ұяшыққа сілтемелерді мәндергі түрлендіру үшін F9 пернесін басыңыз. Excel бағдарламасы жиым тұрақты мәніндегі мәндерді түрлендіреді.

  4. Жиым тұрақты мәнін жиым формуласы ретінде енгізу үшін CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз.

Excel бағдарламасы =Data!E1:G3 жиым формуласын келесі жиым тұрақты мәнімен ауыстырады:

={1,2,3;5,6,7;9,10,11}

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

Ұяшықтардың ауқымындағы таңбаларды санау

Келесі мысал бос орындарды, қамтитын ұяшықтар ауқымындағы таңбалардың санын есептеу жолын көрсетеді.

  • Деректер жұмыс парағында С7 ұяшығына келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =SUM(LEN(C1:C5))

47 мәні C7 ұяшығында көрсетіледі.

Мұндай жағдайда, LEN функциясы ауқымдағы ұяшықтардың әрбіріндегі әрбір мәтіндік жолдың ұзындығын қайтарады. SUM функциясы сол мәндерді бірге қосады және формуланы қамтитын С7 ұяшығында нәтижені көрсетеді.

Ауқымдағы n ең кішкентай мәнін табу

Осы мысал ұяшықтардың ауқымындағы 3 ең кішкентай мәнді табу жолын көрсетеді.

  1. Деректер жұмыс парағында А12 және А14 ұяшықтарын таңдаңыз.

    Ұяшықтардың осы жинағы жиым формуласы қайтаратын нәтижелерді сақтайды.

  2. Формула жолағына келесі формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =SMALL(A1:A10,{1;2;3})

400, 475 және 500 мәндері сәйкесінше A12 және A14 аралығындағы ұяшықтарда көрсетіледі.

Осы формула SMALL функциясын үш есеге бағалау үшін және A1:A10 ұяшықтарында қамтылған жиымда ең кіші (1), екі ең кіші (2) және үш ең кіші (3) мүшелерді қайтару үшін жиым тұрақты мәнін пайдаланыңыз. Бірнеше мәнді таңдау үшін, тұрақты мәнге қосымша аргументтерді және A12:A14 ауқымына нәтиже ұяшықтарының балама санын қосыңыз. Сондай-ақ, формуламен бірге SUM немесе AVERAGE қосымша функцияларды пайдалануға болады. Мысалы:

=SUM(SMALL(A1:A10,{1;2;3}))

=AVERAGE(SMALL(A1:A10,{1;2;3}))

Ауқымдағы n ең үлкен мәнді табу

Ауқымдағы ең үлкен мәндерді табу үшін, SMALL функциясын LARGE функциясымен ауыстыруға болады. Қосымша, келесі мысал ROW және INDIRECT функцияларын қолданады.

  1. Деректер жұмыс парағында А12 және А14 ұяшықтарын таңдаңыз.

  2. Бөлектелген ұяшықтарды қалдырып, бар формуланы жою үшін DELETE пернесін басыңыз.

  3. Формула жолағына осы формуланы енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =LARGE(A1:A10,ROW(INDIRECT("1:3")))

3200, 2700 және 2000 мәндері сәйкесінше A12 және A14 аралығындағы ұяшықтарда көрсетіледі.

Осы жерде бұл ROW және INDIRECT функциялары туралы білуге көмектесуі мүмкін. Тізбекті бүтін сандарды жиымын жасау үшін ROW функциясын пайдалануға болады. Мысалы, 10 ұяшықтан тұратын бос бағанды жаттығу жұмыс кітабынан таңдап, осы жиым формуласын A1:A10 ұяшықтарына енгізіңіз, одан кейін CTRL+SHIFT+ENTER пернелер тіркесімдерін енгізіңіз:

=ROW(1:10)

Формула 10 тізбекті бүтін сандардан тұратын бағанды жасайды. Ықтимал ақаулықты көру үшін, жиым формуласын қамтитын ауқымның үстіне жолды кірістіріңіз (ол дегеніміз 1-жолдың жоғарғы жағына). Excel бағдарламасы жол сілтемелерін лайықтап, формула 2 мен 11 сандары аралығындағы бүтін сандарды шығарады. Осы ақаулықты дұрыстау үшін, фoрмулаға INDIRECT функциясын қосыңыз:

=ROW(INDIRECT("1:10"))

INDIRECT функциясы мәтіндік жолдарды олардың аргументтері ретінде қолданады (сондықтан 1:10 ауқымы қос тырнақшамен қоршалған). Excel бағдарламасы жолдарды кірістіргенде мәтіндік мәндерді лайықтамайды немесе керісінше жиым формуласын жойыңыз. Нәтижесінде, ROW функциясы әрқашан қажетті бүтін сандардың жиымын жасайды.

Бұрын қолданылған формуланы тексерейік — =LARGE(A1:A10,ROW(INDIRECT("1:3"))) — ішкі тырнақшамен басталып, сыртында жұмыс істейді: INDIRECT функциясы мәтіндік мәндердің жинағын қайтарады, бұл жағдайда 1 мен 3 аралығындағы мәндер. ROW функциясы өз кезегінде үш ұяшықтағы бағандаулы жиымды жасайды. LARGE функциясы A1:A10 ұяшық ауқымындағы мәндерді қолданады және ол үш рет бағаланады, әрбір сілтеме үшін бір рет ROW функциясы арқылы қайтарылады. 3200, 2700 және 2000 мәндері үш ұяшықтағы бағандаулы жиымды қайтарады. Қосымша мәндерді табу үшін, үлкенірек ұяшық ауқымын INDIRECT функциясына қосыңыз.

Ақырында, осы формуланы SUM және AVERAGE сияқты басқа функциялармен қолдануға болады.

Ұяшықтар ауқымындағы ең ұзақ мәтіндік жолды табу

Осы мысал ұяшықтар ауқымындағы ең ұзақ мәтінді табады. Бұл формула деректер ауқымында тек қана ұяшықтардың жалғыз бағаны болғанда ғана жұмыс істейді.

  • Деректер жұмыс парағында С7 ұяшығынан бар формуланы жойып, келесі формуланы сол ұяшыққа енгізіп, CTRL+SHIFT+ENTER пернелер тіркесімдерін басыңыз:

    =INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

Секіріп өткен мәні С7 ұяшығында көрсетіледі.

Ішкі элементтерінен және сыртқы жұмыс істеуінен бастап формуланы тексерейік. LEN функциясы C1:C5 ұяшық ауқымындағы элементтердің әрбірінің ұяшық ұзындығын қайтарады. MAX функциясы С3 ұяшығындағы ең ұзын мәтіндік жолға сәйкес келетін осы элементтер арасындағы ең үлкен мәнді есептейді.

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

(MAX(LEN(C1:C5))

және сол жиым былай орналасады:

LEN(C1:C5)

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

Ақырында, INDEX функциясы осы аргументтерді алады: жиым, сол жиымдағы жол және баған саны. C1:C5 ұяшық ауқымы жиымды береді, MATCH функциясы ұяшық мекенжайын береді және соңғы аргумент (1) жиымдағы алғашқы бағаннан шыққан мәнді көрсетеді.

Беттің жоғарғы жағы

Қосымша жиым формулаларын жұмысқа қою

Осы бөлім қосымша жиым формулаларының мысалдарын береді.

Қате мәндерді қамтитын ауқымды қосу

Excel бағдарламасындағы SUM функциясы #N/A сияқты қате мәнді қамтитын ауқымды қосуға әрекет жасаған кезде жұмыс істемейді. Осы мысал қателерді қамтитын деректер деп аталатын ауқымдағы мәндерді қосу жолын көрсетеді.

=SUM(IF(ISERROR(Data),"",Data))

Осы формула бастапқы мәндерден қате мәндерді алып тастайтын жаңа жиымды жасайды. Ішкі функциялар мен сыртта жұмыс істеуден бастап, ISERROR функциясы қателерге арналған ұяшық ауқымын (Деректер) іздейді. IF функциясы көрсетілген шарт TRUE мәнін беретін болса арнайы мәнді және FALSE мәнін беретін болса басқа мәнді қайтарады. Мұндай жағдайда, олар TRUE мәнін есептейтіндіктен барлық қателер үшін бас жолдарды қайтарады ("") және ол қате мәндерді қамтымайтынын білдіретін, FALSE мәнін есептейтіндіктен ауқымда қалған мәндерді (Деректер) қайтарады. SUM функциясы одан кейін сүзілген жиымға арналған қорытындыны есептейді.

Ауқымдағы қате мәндер санын санау

Осы мысал алдыңғы формулаға ұқсас, бірақ оларды сүзудің орынына қате мәндердің санын деректер деп аталған ауқымда қайтарады:

=SUM(IF(ISERROR(Data),1,0))

Осы формула қателерді қамтитын ұяшықтарға арналған 1 мәні және қатені қамтымайтын ұяшықтарға арналған 0 мәні бар жиымды жасайды. Формуланы көрсетіп, IF функциясына арналған үшінші аргументті жою арқылы бірдей нәтижені мұрағаттауға болады, мына сияқты:

=SUM(IF(ISERROR(Data),1))

Аргументті көрсетпесеңіз, IF функциясы ұяшықта қате мән болмаса, FALSE мәнін қайтарады. Формуланы одан да қарапайым етуге болады:

=SUM(IF(ISERROR(Data)*1))

Осы нұсқа TRUE*1=1 және FALSE*1=0 мәндері болғандықтан жұмыс істейді.

Шарттарға негізделген мәндерді қосу

Шарттарға негізделген мәндерді қосу қажет болуы мүмкін. Мысалы, осы жиым формуласы сатулар деп аталатын ауқымда тек қана оң бүтін сандарды ғана қосады:

=SUM(IF(Сатулар>0,Сатулар))

IF функциясы оң мәндердің және дұмыс емес мәндердің жиымын жасайды. SUM функциясы негізінен 0+0=0 болғандықтан дұрыс емес мәндерді елемейді. Осы формулада қолданылатын ұяшық ауқымы жолдар мен бағандардың санынан тұруы мүмкін.

Бірнеше шартты қамтитын мәндерді де қосуға болады. Мысалы, осы жиым формуласы 0-ден артық және 5-ке тең немесе одан аз мәндерді есептейді:

=SUM((Сатулар>0)*(Сатулар<=5)*(Сатулар))

Ауқымда бір немесе бірнеше сандық емес ұяшықтар болса, осы формуланың қате беретінін есте сақтаңыз.

Түрді немесе OR шартын қолданатын жиым формулаларын да жасауға болады. Мысалы, 5-тен төмен немесе 15-тен көп мәндерді қосуға да болады:

=SUM(IF((Сатулар<5)+(Сатулар>15),Сатулар))

IF функциясы 5-тен төмен және 15-тен көп барлық мәндерді тауып, сол мәндерді SUM функциясына жібереді.

Маңызды : AND және OR функцияларын тікелей жиым формулаларында қолдануға болмайды, себебі сол функциялар жалғыз нәтижені TRUE немесе FALSE мәнін қайтарады және жиым функциялары нәтижелердің жиымдарын қажет етеді. Алдыңғы формулада көрсетілген логиканы қолдану арқылы ақаулық бойынша жұмыс істеуге болады. Басқаша айтқанда, қосу немесе көбейту сияқты математикалық амалдарды OR немесе AND шартына сәйкес келетін мәндерде орындайсыз.

Нөлдерді алып тастайтын орташа мәнді есептейді

Осы мысал ауқымдағы мәндредің орташа мәнін анықтау қажет болғанда, ауқымнан нөлдерді алып тастау жолын көрсетеді. Формула сатулар деп аталатын ауқымды қолданады:

=AVERAGE(IF(Сатулар<>0,Сатулар))

IF функциясы 0-ге тең емес мәндердің жиымын жасап, сол мәндерді AVERAGE функциясына жібереді.

Ұяшықтардың екі ауқымының арасындағы айырмашылықтардың санын есептеу

Осы жиым формуласы менің деректерім және сіздің деректеріңіз деп аталатын ұяшықтардың екі ауқымының арасындағы мәндерді салыстырады. Екі ауқымның мазмұны бірдей болса, формула 0 мәнін қайтарады. Осы формуланы қолдану үшін, ұяшық ауқымдары бірдей өлшемде және бірдей өлшемділікте болуы қажет:

=SUM(IF(МеніңДерегім=СеніңДерегің,0,1))

Формула салыстырылатын ауқымдар ретінде бірдей өлшемдегі жаңа жиымды жасайды. IF функциясы 0 мәнімен және 1 мәнімен жиымды толтырады (0 мәні сәйкессіздіктер және 1 мәні бірдей ұяшықтар үшін). SUM функциясы одан кейін жиымдағы мәндердің жиынтығын қайтарады.

Формуланы мына сияқты қарапайым етуге болады:

=SUM(1*(МеніңДерегім<>СеніңДерегің))

Ауқымдағы қате мәндерді санайтын формула сияқты осы формула TRUE*1=1 және FALSE*1=0 мәндері болғандықтан жұмыс істейді.

Ауқымдағы ең үлкен мәннің орнын табу

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

=MIN(IF(Деректер=MAX(Деректер),ROW(Деректер),""))

IF функциясы деректер деп аталатын ауқымға сәйкес келетін жаңа жиымды жасайды. Сәйкес ұяшық ауқымда ең үлкен мәнді қамтитын болса, жиым жол санын қамтиды. Басқаша, жиым бос жолды ("") қамтиды. MIN функциясы екінші аргументі ретінде жаңа жиымды қолданады және ең кіші мәнді қайтарады, ол деректер ауқымындағы ең үлкен мәннің жол санына сәйкес келеді. Деректер деп аталатын ауқымда бірдей ең үлкен мәндер болса, формула бірінші мәннің жолын қайтарады.

Ең үлкен мәннің нақты ұяшық мекенжайын қайтарғыңыз келсе, осы формуланы қолданыңыз:

=ADDRESS(MIN(IF(Деректер=MAX(Деректер),ROW(Деректер),"")),COLUMN(Деректер))

Беттің жоғарғы жағы

Дағдыларды жетілдіру
Оқыту курсымен танысыңыз
Жаңа мүмкіндіктерге бірінші болып қол жеткізу
Office Insider бағдарламасына қосылу

Осы ақпарат пайдалы болды ма?

Пікіріңіз үшін рақмет!

Пікіріңізге рақмет! Сізді Office қолдау көрсету қызметіндегі агенттердің бірімен байланыстырған жөн болуы мүмкін.

×