Мазмұны
Динамикалық массивтер дегеніміз не
2018 жылдың қыркүйегінде Microsoft корпорациясы Microsoft Excel бағдарламасына мүлдем жаңа құрал қосатын жаңартуды шығарды: Динамикалық массивтер және олармен жұмыс істеуге арналған 7 жаңа функция. Бұл нәрселер, артық айтпастан, формулалармен және функциялармен жұмыс істеудің барлық әдеттегі техникасын түбегейлі өзгертеді және сөзбе-сөз әрбір пайдаланушыны алаңдатады.
Мәнін түсіндіру үшін қарапайым мысалды қарастырыңыз.
Бізде қала-айлар туралы деректер бар қарапайым кесте бар делік. Парақтың оң жағындағы кез келген бос ұяшықты таңдап, оған бір ұяшыққа емес, бірден диапазонға байланыстыратын формуланы енгізсек не болады?
Excel бағдарламасының барлық алдыңғы нұсқаларында басқаннан кейін кіру біз тек бір бірінші B2 ұяшығының мазмұнын алатын едік. Басқа қалай?
Немесе бұл диапазонды =SUM(B2:C4) сияқты қандай да бір жинақтау функциясына орап, ол үшін жалпы қорытынды алуға болады.
Егер бізге бірегей мәндерді немесе Үздік 3-ті шығару сияқты қарапайым қосындыдан гөрі күрделі операциялар қажет болса, онда біз формуланы пернелер тіркесімін пайдаланып массив формуласы ретінде енгізуіміз керек еді. Ctrl+ауысым+кіру.
Қазір бәрі басқаша.
Енді мұндай формуланы енгізгеннен кейін біз жай ғана шертеміз кіру – және нәтижесінде біз сілтеме жасаған uXNUMXbuXNUMXb барлық мәндерін дереу алыңыз:
Бұл сиқыр емес, бірақ қазір Microsoft Excel-де бар жаңа динамикалық массивтер. Жаңа әлемге қош келдіңіз 🙂
Динамикалық массивтермен жұмыс істеу ерекшеліктері
Техникалық тұрғыдан алғанда, біздің бүкіл динамикалық массив бірінші G4 ұяшығында сақталады, қажетті ұяшықтар санын оның деректерімен оңға және төменге толтырады. Жиымдағы кез келген басқа ұяшықты таңдасаңыз, формула жолындағы сілтеме белсенді емес болады, бұл біздің «еншілес» ұяшықтардың бірінде екенімізді көрсетеді:
Бір немесе бірнеше «еншілес» ұяшықтарды жою әрекеті ештеңеге әкелмейді - Excel оларды дереу қайта есептеп, толтырады.
Сонымен қатар, біз басқа формулаларда осы «бала» ұяшықтарға сенімді түрде сілтеме жасай аламыз:
Егер сіз массивтің бірінші ұяшығын көшірсеңіз (мысалы, G4-тен F8-ге дейін), онда бүкіл массив (оның сілтемелері) әдеттегі формулалардағыдай бағытта қозғалады:
Егер массивті жылжыту қажет болса, онда оны жылжыту жеткілікті болады (тінтуірдің көмегімен немесе комбинациясы Ctrl+X, Ctrl+V), қайтадан, тек бірінші негізгі G4 ұяшығы – одан кейін ол жаңа орынға ауыстырылады және біздің бүкіл массив қайтадан кеңейтіледі.
Жасалған динамикалық массивке парақтың басқа жеріне сілтеме жасау қажет болса, оның жетекші ұяшығының мекенжайынан кейін # («фунт») арнайы таңбасын қолдануға болады:
Мысалы, енді сіз жасалған динамикалық массивке сілтеме жасайтын ұяшықта ашылмалы тізімді оңай жасай аласыз:
Динамикалық массив қателері
Бірақ массивті кеңейту үшін орын жеткіліксіз болса немесе оның жолында басқа деректер басып алған ұяшықтар болса не болады? Excel бағдарламасында қателердің түбегейлі жаңа түрімен танысыңыз – #Трансфер! (#ТӨКІЛДІ!):
Әдеттегідей, сары гауһар және леп белгісі бар белгішені бассақ, біз мәселенің көзі туралы егжей-тегжейлі түсініктеме аламыз және кедергі жасайтын ұяшықтарды тез таба аламыз:
Ұқсас қателер массив парақтан шығып кетсе немесе біріктірілген ұяшыққа тисе, орын алады. Егер сіз кедергіні жойсаңыз, бәрі бірден түзетіледі.
Динамикалық массивтер және смарт кестелер
Егер динамикалық массив пернелер тіркесімі арқылы жасалған «ақылды» кестені көрсетсе Ctrl+T немесе бойынша Басты – кесте ретінде пішімдеу (Үй — Кесте ретінде пішімдеу), содан кейін ол өзінің негізгі сапасын - автоматты өлшемді иеленеді.
Жаңа деректерді төменгі немесе оң жаққа қосқанда смарт кесте мен динамикалық ауқым да автоматты түрде созылады:
Дегенмен, бір шектеу бар: біз смарт кестедегі форумдарда динамикалық ауқым сілтемесін пайдалана алмаймыз:
Динамикалық массивтер және басқа Excel мүмкіндіктері
Жарайды, айтасың. Мұның бәрі қызықты және күлкілі. Бұрынғыдай, формуланы бастапқы диапазонның бірінші ұяшығына сілтеме жасай отырып, төменге және оңға және осының барлығына қолмен созудың қажеті жоқ. Және бәрі осы ма?
өте емес.
Динамикалық массивтер Excel бағдарламасындағы басқа құрал ғана емес. Енді олар Microsoft Excel бағдарламасының ең жүрегіне (немесе миына) - оның есептеу механизміне енгізілген. Бұл бізге таныс басқа Excel формулалары мен функцияларының динамикалық массивтермен жұмыс істеуге қолдау көрсететінін білдіреді. Орын алған өзгерістердің тереңдігі туралы түсінік беру үшін бірнеше мысалды қарастырайық.
Ауыстыру
Ауқымның орнын ауыстыру (жолдар мен бағандарды ауыстыру) Microsoft Excel бағдарламасында әрқашан кірістірілген функция болды КӨЛІК (TRANSPOSE). Дегенмен, оны пайдалану үшін алдымен нәтижелер ауқымын дұрыс таңдау керек (мысалы, кіріс 5×3 ауқымы болса, онда сіз 3×5 таңдаған болуыңыз керек), содан кейін функцияны енгізіп, түймесін басыңыз. комбинациясы Ctrl+ауысым+кіру, себебі ол тек массив формуласы режимінде жұмыс істей алады.
Енді сіз бір ұяшықты таңдай аласыз, оған бірдей формуланы енгізіп, қалыпты түймені басыңыз кіру – динамикалық массив бәрін өздігінен жасайды:
Көбейту кестесі
Бұл Excel бағдарламасындағы массив формулаларының артықшылықтарын визуализациялауды сұрағанда мен беретін мысал. Енді бүкіл Пифагор кестесін есептеу үшін бірінші В2 ұяшығында тұру жеткілікті, ол жерге екі массивті көбейтетін формуланы енгізіңіз (сандардың тік және көлденең жиыны 1..10) және жай ғана басыңыз. кіру:
Желімдеу және корпусты түрлендіру
Массивтерді көбейтіп қана қоймай, стандартты & (амперсанд) операторымен бірге жапсыруға болады. Екі бағаннан аты мен тегін шығарып, бастапқы деректердегі секіру жағдайын түзетуіміз керек делік. Біз мұны бүкіл массивті құрайтын бір қысқа формуламен орындаймыз, содан кейін оған функцияны қолданамыз PROPNACH (ДҰРЫС)тізілімді ретке келтіру үшін:
Қорытынды Жоғарғы 3
Бізде кему ретімен орналастырып, ең жоғарғы үш нәтижені алғымыз келетін көптеген сандар бар делік. Енді бұл бір формуламен және қайтадан ешбір формуламен орындалады Ctrl+ауысым+кіру бұрынғыдай:
Нәтижелерді бағанға емес, қатарға орналастыруды қаласаңыз, осы формуладағы қос нүктелерді (жолды бөлгіш) нүктелі үтірмен (бір жолдағы элементті бөлгіш) ауыстыру жеткілікті. Excel бағдарламасының ағылшын тіліндегі нұсқасында бұл бөлгіштер сәйкесінше нүктелі үтір және үтір болып табылады.
VLOOKUP бір уақытта бірнеше бағандарды шығарып жатыр
функциялары VPR (КӨРУ) енді мәндерді бірден емес, бірнеше бағандардан алуға болады – функцияның үшінші аргументінде массив ретінде олардың сандарын (кез келген қажетті ретпен) көрсетіңіз:
OFFSET функциясы динамикалық массивді қайтарады
Деректерді талдауға арналған ең қызықты және пайдалы функциялардың бірі (VLOOKUP кейін) функция болып табылады Диспозалы (OFSET), мен оған бір уақытта кітабымның бір тарауын және осында бір мақаланы арнадым. Бұл функцияны түсіну және меңгерудегі қиындық әрқашан оның нәтижесінде деректер массивін (диапазонын) қайтаруында болды, бірақ біз оны көре алмадық, өйткені Excel әлі де қораптан тыс массивтермен жұмыс істеуді білмеді.
Қазір бұл мәселе артта қалды. Бір формуланы және OFFSET арқылы қайтарылған динамикалық массивті пайдаланып, кез келген сұрыпталған кестеден берілген өнім үшін барлық жолдарды қалай шығаруға болатынын қараңыз:
Оның аргументтерін қарастырайық:
- A1 – бастапқы ұяшық (анықтамалық нүкте)
- ПОИСКПОЗ(F2;A2:A30;0) – бастапқы ұяшықтан төмен – бірінші табылған орамжапыраққа ауысуды есептеу.
- 0 – «терезенің» бастапқы ұяшыққа қатысты оңға жылжуы
- СЧЁТЕСЛИ(A2:A30;F2) – қайтарылған «терезенің» биіктігін есептеу – орамжапырақ бар жолдар саны.
- 4 — «терезенің» өлшемі көлденеңінен, яғни 4 бағанды шығару
Динамикалық массивтерге арналған жаңа функциялар
Ескі функциялардағы динамикалық массив механизмін қолдаудан басқа, Microsoft Excel бағдарламасына динамикалық массивтермен жұмыс істеу үшін нақтыланған бірнеше мүлдем жаңа функциялар қосылды. Атап айтқанда, бұлар:
- СЫНЫП (СҰРУ) – кіріс ауқымын сұрыптайды және шығыста динамикалық массив шығарады
- SORTPO (БОЙЫНША СҰРЫПТАУ) – бір диапазонды екіншісінен мәндер бойынша сұрыптай алады
- СҮЗГІ (СҮЗГІ) – көрсетілген шарттарға сәйкес келетін бастапқы ауқымнан жолдарды шығарады
- UNIK (ЕРЕКШЕ) – ауқымнан бірегей мәндерді шығарады немесе көшірмелерді жояды
- SLMASSIVE (RANDARRAY) – берілген өлшемдегі кездейсоқ сандар массивін жасайды
- ТУЫЛҒАН КЕЙІН (ЖҮЙЕЛІ) — берілген қадаммен сандар тізбегінен массив құрайды
Олар туралы толығырақ – сәл кейінірек. Олар мұқият зерттеу үшін бөлек мақалаға (бір емес) тұрарлық 🙂
Қорытындылар
Егер сіз жоғарыда жазылғандардың барлығын оқыған болсаңыз, онда сіз орын алған өзгерістердің ауқымын түсіндіңіз деп ойлаймын. Excel бағдарламасында көптеген нәрселерді енді оңай, оңай және логикалық түрде жасауға болады. Мойындауым керек, осында, осы сайтта және менің кітаптарымда қанша мақаланы түзетуге тура келетініне біраз таң қалдым, бірақ мен мұны жеңіл жүрекпен жасауға дайынмын.
Қорытындылай келе, артықшылықтары динамикалық массивтер үшін келесіні жазуға болады:
- Сіз комбинация туралы ұмыта аласыз Ctrl+ауысым+кіру. Excel бағдарламасы енді «қалыпты формулалар» мен «жиым формулалары» арасында ешқандай айырмашылықты көрмейді және оларды бірдей қарастырады.
- Функция туралы SUMPRODUCT (ҚЫСТЫҚ), ол бұрын массив формулаларын енгізу үшін пайдаланылған Ctrl+ауысым+кіру Сіз де ұмыта аласыз – енді бұл оңай SUM и кіру.
- Смарт кестелер мен таныс функциялар (SUM, IF, VLOOKUP, SUMIFS, т.б.) енді динамикалық массивтерді толық немесе ішінара қолдайды.
- Кері үйлесімділік бар: Excel бағдарламасының ескі нұсқасында динамикалық массивтері бар жұмыс кітабын ашсаңыз, олар массив формулаларына (бұйра жақшаларда) айналады және «ескі стильде» жұмысын жалғастырады.
Нөмірді таптым минустар:
- Динамикалық массивтен жеке жолдарды, бағандарды немесе ұяшықтарды жою мүмкін емес, яғни ол бір нысан ретінде өмір сүреді.
- Сіз динамикалық массивді әдеттегі жолмен сұрыптай алмайсыз Деректер – сұрыптау (Деректер - сұрыптау). Қазір бұл үшін арнайы функция бар. СЫНЫП (СҰРУ).
- Динамикалық диапазонды смарт кестеге айналдыру мүмкін емес (бірақ смарт кесте негізінде динамикалық ауқым жасауға болады).
Әрине, бұл соңы емес, Microsoft корпорациясы бұл механизмді болашақта да жетілдіретініне сенімдімін.
Қайдан жүктеп алуға болады?
Соңында, негізгі сұрақ 🙂
Microsoft Excel бағдарламасындағы динамикалық массивтердің алдын ала қарауын алғаш рет 2018 жылдың қыркүйегінде конференцияда жариялады және көрсетті. жандыру. Келесі бірнеше айда жаңа мүмкіндіктерді мұқият сынау және іске қосу болды, ең алдымен мысықтар Microsoft корпорациясының қызметкерлері, содан кейін Office Insider тобының ерікті тестерлерінде. Осы жылы динамикалық массивтерді қосатын жаңарту бірте-бірте тұрақты Office 365 жазылушыларына таратыла бастады. Мысалы, мен оны тамыз айында Office 365 Pro Plus (айлық мақсатты) жазылымымен ғана алдым.
Егер Excel-де әлі динамикалық массивтер болмаса, бірақ олармен шынымен жұмыс істегіңіз келсе, келесі опциялар бар:
- Office 365 жазылымыңыз болса, осы жаңарту сізге жеткенше күте аласыз. Мұның қаншалықты жылдам болатыны жаңартулардың кеңсеге қаншалықты жиі жеткізілетініне байланысты (жылына бір рет, алты айда бір рет, айына бір рет). Егер сізде корпоративтік компьютер болса, әкімшіден жиірек жүктелетін жаңартуларды орнатуды сұрауға болады.
- Сіз Office Insider сынақ еріктілерінің қатарына қосыла аласыз – сонда сіз барлық жаңа мүмкіндіктер мен функцияларды бірінші болып аласыз (бірақ Excel бағдарламасында қателерді көбейту мүмкіндігі бар, әрине).
- Жазылымыңыз болмаса, бірақ Excel бағдарламасының қораптағы оқшау нұсқасы болса, кем дегенде 2022 жылы Office және Excel бағдарламасының келесі нұсқасы шыққанша күтуіңіз керек. Мұндай нұсқалардың пайдаланушылары тек қауіпсіздік жаңартулары мен қателерді түзетеді, ал барлық жаңа «жақсылықтар» енді тек Office 365 жазылушыларына беріледі. Өкінішті, бірақ шындық 🙂
Кез келген жағдайда Excel-де динамикалық массивтер пайда болғанда – осы мақаладан кейін сіз оған дайын боласыз 🙂
- Массив формулалары дегеніміз не және оларды Excel бағдарламасында қалай пайдалану керек
- OFFSET функциясын қолданатын терезе (диапазон) қосындысы
- Excel бағдарламасында кестені ауыстырудың 3 жолы