Агуулга
Динамик массив гэж юу вэ
2018 оны 7-р сард Microsoft нь Microsoft Excel-д цоо шинэ хэрэгсэл болох Динамик массив болон тэдгээртэй ажиллах XNUMX шинэ функцийг нэмсэн шинэчлэлтийг гаргасан. Эдгээр зүйлүүд нь хэтрүүлэлгүйгээр томъёо, функцтэй ажиллах ердийн техникийг эрс өөрчилж, хэрэглэгч бүрийг санаа зовдог.
Үүний мөн чанарыг тайлбарлахын тулд энгийн жишээг авч үзье.
Бидэнд хот-сарын өгөгдөл бүхий энгийн хүснэгт байна гэж бодъё. Хэрэв бид хуудасны баруун талд байгаа хоосон нүдийг сонгоод, нэг нүд рүү биш, тэр даруй мужтай холбосон томьёо оруулбал юу болох вэ?
Excel-ийн өмнөх бүх хувилбаруудад товшсоны дараа оруулна уу Бид зөвхөн нэг эхний B2 нүдний агуулгыг авах болно. Өөр яаж?
За, эсвэл энэ мужийг =SUM(B2:C4) гэх мэт нэгтгэх функцээр боож, нийт дүнг авах боломжтой.
Хэрэв бидэнд өвөрмөц утгууд эсвэл Топ 3-ыг задлах гэх мэт энгийн нийлбэрээс илүү төвөгтэй үйлдлүүд хэрэгтэй бол бид гарын товчлолыг ашиглан томъёогоо массив томьёо болгон оруулах хэрэгтэй болно. Ctrl+Shift+оруулна уу.
Одоо бүх зүйл өөр болсон.
Одоо ийм томъёог оруулсны дараа бид зүгээр л дарж болно оруулна уу - ба үр дүнд нь бидний дурдсан бүх утгыг нэн даруй аваарай:
Энэ бол ид шид биш, харин одоо Microsoft Excel-ийн шинэ динамик массивууд юм. Шинэ ертөнцөд тавтай морил 🙂
Динамик массивтай ажиллах онцлог
Техникийн хувьд бидний бүх динамик массив нь эхний G4 нүдэнд хадгалагдаж, шаардлагатай тооны нүднүүдийг баруун болон доош өгөгдлөөр дүүргэдэг. Хэрэв та массив дахь өөр нүдийг сонговол томъёоны мөрөнд байгаа холбоос идэвхгүй байх бөгөөд энэ нь бид "хүүхэд" нүднүүдийн аль нэгэнд байгааг харуулж байна.
Нэг буюу хэд хэдэн "хүүхдийн" нүдийг устгах оролдлого нь ямар ч үр дүнд хүргэхгүй - Excel нэн даруй дахин тооцоолж бөглөнө.
Үүний зэрэгцээ бид эдгээр "хүүхдийн" эсүүдийг бусад томъёонд аюулгүйгээр дурдаж болно.
Хэрэв та массивын эхний нүдийг (жишээлбэл, G4-ээс F8 хүртэл) хуулж авбал массив бүхэлдээ (түүний лавлагаа) ердийн томьёотой ижил чиглэлд шилжих болно.
Хэрэв бид массивыг зөөх шаардлагатай бол (хулгана эсвэл хослолоор) зөөхөд хангалттай Ctrl+X, Ctrl+V), дахин, зөвхөн эхний үндсэн нүд G4 - үүний дараа энэ нь шинэ газар шилжиж, бидний бүх массив дахин өргөжих болно.
Хэрэв та хуудасны өөр газар үүсгэсэн динамик массивыг дурдах шаардлагатай бол түүний тэргүүлэх нүдний хаягийн ард # ("фунт") тусгай тэмдэгтийг ашиглаж болно.
Жишээлбэл, одоо та үүсгэсэн динамик массивыг харуулсан нүдэнд унадаг жагсаалтыг хялбархан хийж болно:
Динамик массивын алдаа
Гэхдээ массивыг өргөжүүлэхэд хангалттай зай байхгүй эсвэл түүний замд өөр өгөгдөл аль хэдийн эзлэгдсэн нүд байвал яах вэ? Excel дээр цоо шинэ төрлийн алдаатай танилцана уу - #ШИЛЖҮҮЛЭХ! (#АСГАРСАН!):
Хэрэв бид шар очир алмааз, анхаарлын тэмдэг бүхий дүрс дээр дарвал асуудлын эх үүсвэрийн талаар илүү нарийвчилсан тайлбарыг авах бөгөөд бид хөндлөнгөөс оролцож буй эсүүдийг хурдан олох болно.
Хэрэв массив хуудаснаас гарах эсвэл нэгтгэсэн нүд рүү ороход ижил төстэй алдаа гарна. Хэрэв та саад тотгорыг арилгавал бүх зүйл нэн даруй засах болно.
Динамик массив ба ухаалаг хүснэгтүүд
Хэрэв динамик массив нь гарын товчлолоор үүсгэгдсэн "ухаалаг" хүснэгтийг зааж байвал Ctrl+T эсвэл by Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах), дараа нь энэ нь мөн үндсэн чанараа өвлөн авах болно - автомат хэмжээ.
Доод эсвэл баруун талд шинэ өгөгдөл нэмэх үед ухаалаг хүснэгт болон динамик муж автоматаар сунгагдана:
Гэсэн хэдий ч, нэг хязгаарлалт байдаг: бид ухаалаг хүснэгт доторх форумд динамик хүрээний лавлагааг ашиглах боломжгүй:
Динамик массив болон бусад Excel функцууд
За, та хэлж байна. Энэ бүхэн сонирхолтой, хөгжилтэй байдаг. Өмнөх шигээ томьёог гараараа анхны мужын эхний нүдийг доош, баруун тийш нь зааж өгөх шаардлагагүй. Тэгээд бүгд үү?
Тийм ч дөхсөнгүй.
Динамик массив нь Excel-ийн өөр нэг хэрэгсэл биш юм. Одоо тэд Microsoft Excel-ийн зүрх сэтгэлд (эсвэл тархинд) суулгагдсан байдаг - тооцооллын систем. Энэ нь бидэнд танил болсон Excel-ийн бусад томьёо, функцууд нь динамик массивтай ажиллахыг дэмждэг гэсэн үг юм. Өөрчлөлтийн гүн гүнзгий байдлын талаар ойлголт өгөхийн тулд цөөн хэдэн жишээг харцгаая.
Шилжүүлэх
Мужийг шилжүүлэх (мөр ба баганыг солих) Microsoft Excel нь үргэлж суурилагдсан функцтэй байдаг ШИЛЖИЛТ (TRANSPOSE). Гэхдээ үүнийг ашиглахын тулд эхлээд үр дүнгийн мужийг зөв сонгох хэрэгтэй (жишээ нь, хэрэв оролт нь 5 × 3-ийн муж байсан бол та 3 × 5-ыг сонгосон байх ёстой), дараа нь функцийг оруулаад товчийг дарна уу. хослол Ctrl+Shift+оруулна уу, учир нь энэ нь зөвхөн массив томъёоны горимд ажиллах боломжтой.
Одоо та зүгээр л нэг нүдийг сонгоод ижил томъёог оруулаад ердийн дээр дарна уу оруулна уу - динамик массив бүх зүйлийг өөрөө хийх болно:
Үржүүлэх хүснэгт
Excel дээр массивын томьёоны ашиг тусыг дүрслэн харуулахыг хүсэх үед миний өгсөн жишээ энэ юм. Одоо Пифагорын хүснэгтийг бүхэлд нь тооцоолохын тулд эхний B2 нүдэнд зогсож, хоёр массивыг (1..10 тооны босоо ба хэвтээ багц) үржүүлэх томъёог оруулаад зүгээр л дарна уу. оруулна уу:
Цавуу болон хайрцаг хувиргах
Массивыг зөвхөн үржүүлээд зогсохгүй стандарт оператор & (амперсанд) ашиглан нааж болно. Бид хоёр баганаас овог нэрээ гаргаж аваад анхны өгөгдөлд үсрэх тохиолдлыг засах хэрэгтэй гэж бодъё. Бид үүнийг массивыг бүхэлд нь бүрдүүлдэг нэг богино томъёогоор хийж, дараа нь функцийг түүнд хэрэглэнэ PROPNACH (ЗӨВ)бүртгэлийг цэгцлэхийн тулд:
Дүгнэлт Топ 3
Бидэнд эхний гурван үр дүнг гаргах, буурах дарааллаар байрлуулах олон тооны тоо байна гэж бодъё. Одоо энэ нь нэг томъёогоор хийгдсэн бөгөөд дахин ямар ч шаардлагагүй Ctrl+Shift+оруулна уу өмнөх шиг:
Хэрэв та үр дүнг баганад биш, харин дараалан байрлуулахыг хүсвэл энэ томьёоны хоёр цэгийг (мөр тусгаарлагч) цэгтэй таслалаар (нэг мөрөнд элемент тусгаарлагч) солиход хангалттай. Excel-ийн англи хувилбарт эдгээр тусгаарлагч нь цэгтэй таслал, таслал юм.
VLOOKUP олон баганыг нэгэн зэрэг задлах
чиг үүрэг VPR (ХАРАХ) Одоо та утгуудыг нэгээс биш, хэд хэдэн баганаас нэг дор татаж авах боломжтой - функцийн гурав дахь аргумент дахь массив болгон тэдгээрийн тоог (хүссэн дарааллаар) зааж өгөхөд хангалттай.
OFFSET функц нь динамик массивыг буцаадаг
Өгөгдлийн дүн шинжилгээ хийхэд хамгийн сонирхолтой бөгөөд хэрэгтэй (VLOOKUP-ийн дараа) функцүүдийн нэг бол функц юм ХӨГЖИЛ (OFSET), би нэгэн зэрэг номынхоо бүхэл бүтэн бүлгийг, энд нэг өгүүллийг зориулав. Энэ функцийг ойлгох, эзэмшихэд бэрхшээлтэй байдаг нь үр дүнд нь массив (хүрээ) өгөгдлүүдийг буцаадаг байсан боловч Excel нь массивтай хэрхэн ажиллахаа мэдэхгүй хэвээр байсан тул бид үүнийг харж чадахгүй байсан.
Одоо энэ асуудал өнгөрсөн байна. Нэг томьёо болон OFFSET-ээр буцаасан динамик массив ашиглан та ямар ч эрэмбэлэгдсэн хүснэгтээс өгөгдсөн бүтээгдэхүүний бүх мөрийг гаргаж авах боломжтойг харна уу:
Түүний аргументуудыг харцгаая:
- A1 - эхлэл нүд (лавлагаа цэг)
- ПОИСКПОЗ(F2;A2:A30;0) – эхлэлийн эсээс доош – анхны олдсон байцаа хүртэлх шилжилтийн тооцоо.
- 0 - "цонх"-ыг эхлэх нүдтэй харьцуулахад баруун тийш шилжүүлэх
- СЧЁТЕСЛИ(A2:A30;F2) – буцаасан “цонхны” өндрийн тооцоо – байцаа байгаа мөрийн тоо.
- 4 — "цонх"-ын хэмжээ хэвтээ, өөрөөр хэлбэл 4 багана гарна
Динамик массивуудад зориулсан шинэ функцууд
Хуучин функцууд дахь динамик массив механизмыг дэмжихээс гадна Microsoft Excel-д динамик массивтай ажиллахад тусгайлан хурцалсан хэд хэдэн цоо шинэ функцүүд нэмэгдсэн. Ялангуяа эдгээр нь:
- -р анги (ЭРЭМБҮҮЛЭХ) – оролтын мужийг эрэмбэлж, гаралт дээр динамик массив үүсгэдэг
- SORTPO (SORTBY) - нэг мужийг нөгөөгөөс нь утгаараа ангилж болно
- FILTER (ШҮҮЛҮҮР) – заасан нөхцөлийг хангасан эхийн мужаас мөрүүдийг татаж авна
- UNIK (ОНЦГОЙ) - мужаас өвөрмөц утгыг задлах эсвэл давхардлыг арилгана
- SMASSIVE (RANDARRAY) – өгөгдсөн хэмжээтэй санамсаргүй тоонуудын массив үүсгэдэг
- ТӨРСӨНИЙ ДАРАА (ДАЯЛАЛ) — өгөгдсөн алхам бүхий тоонуудын дарааллаас массив үүсгэдэг
Тэдний тухай дэлгэрэнгүй - бага зэрэг дараа. Тэдгээрийг сайтар судлахын тулд тусдаа нийтлэл (мөн нэг биш) үнэ цэнэтэй юм 🙂
Дүгнэлт
Хэрэв та дээр бичсэн бүх зүйлийг уншсан бол гарсан өөрчлөлтийн цар хүрээг аль хэдийн ойлгосон байх гэж бодож байна. Excel-ийн олон зүйлийг одоо илүү хялбар, хялбар, логикоор хийх боломжтой болсон. Энд, энэ сайт болон миний номон дээрх хэдэн нийтлэлийг засах шаардлагатай болсныг би бага зэрэг цочирдсон гэдгээ хүлээн зөвшөөрөх ёстой, гэхдээ би үүнийг хөнгөн зүрх сэтгэлээр хийхэд бэлэн байна.
Үр дүнг нэгтгэн дүгнэхэд, нэмнэ Динамик массивын хувьд та дараахь зүйлийг бичиж болно.
- Та хослолын талаар мартаж болно Ctrl+Shift+оруулна уу. Excel одоо "ердийн томъёо" болон "массив томьёо" хоёрын хооронд ямар ч ялгааг олж харахгүй бөгөөд тэдгээрийг ижил аргаар авч үздэг.
- Функцийн тухай SUMPRODUCTION (ХҮРЭЭНИЙ БҮТЭЭГДЭХҮҮН), өмнө нь массивын томьёог оруулахад ашигладаг байсан Ctrl+Shift+оруулна уу Та бас мартаж болно - одоо хангалттай хялбар боллоо SUM и оруулна уу.
- Ухаалаг хүснэгтүүд болон танил функцууд (SUM, IF, VLOOKUP, SUMIFS гэх мэт) нь динамик массивуудыг бүрэн эсвэл хэсэгчлэн дэмждэг болсон.
- Хоцрогдсон нийцтэй байдал бий: хэрэв та Excel-ийн хуучин хувилбарт динамик массив бүхий ажлын номыг нээвэл тэдгээр нь массив томьёо (буржгар хаалтанд) болж хувирч, "хуучин хэв маяг" -аар үргэлжлүүлэн ажиллах болно.
Хэдэн дугаар оллоо хасах:
- Та динамик массиваас тусдаа мөр, багана эсвэл нүдийг устгах боломжгүй, өөрөөр хэлбэл энэ нь нэг нэгж байдлаар амьдардаг.
- Та динамик массивыг ердийн аргаар эрэмбэлэх боломжгүй Өгөгдөл - эрэмбэлэх (Өгөгдөл - эрэмбэлэх). Одоо үүнд зориулсан тусгай функц бий. -р анги (ЭРЭМБҮҮЛЭХ).
- Динамик мужийг ухаалаг ширээ болгон хувиргах боломжгүй (гэхдээ та ухаалаг ширээн дээр тулгуурлан динамик мужийг хийж болно).
Мэдээжийн хэрэг, энэ бол төгсгөл биш бөгөөд Майкрософт ирээдүйд энэ механизмыг үргэлжлүүлэн сайжруулна гэдэгт би итгэлтэй байна.
Би хаанаас татаж авах вэ?
Эцэст нь гол асуулт 🙂
Майкрософт анх 2018 оны XNUMX-р сард нэгэн бага хурал дээр Excel-ийн динамик массивуудын урьдчилсан үзүүлбэрийг зарлаж, үзүүлэв. гал авалцаж. Дараагийн хэдэн сард эхлээд шинэ боломжуудыг сайтар туршиж, нэвтрүүлсэн Муур Майкрософт компанийн ажилтнууд, дараа нь 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 арга