Ma Dynamic arrays mu Excel

Kodi ma dynamic arrays ndi chiyani

Mu Seputembala 2018, Microsoft idatulutsa zosintha zomwe zimawonjezera chida chatsopano ku Microsoft Excel: Dynamic Arrays ndi ntchito 7 zatsopano zogwirira nawo ntchito. Zinthu izi, popanda kukokomeza, zimasintha kwambiri njira zonse zanthawi zonse zogwirira ntchito ndi mafomu ndi ntchito ndi nkhawa, kwenikweni, wogwiritsa ntchito aliyense.

Ganizirani chitsanzo chosavuta chofotokozera tanthauzo lake.

Tiyerekeze kuti tili ndi tebulo losavuta lomwe lili ndi deta ya miyezi ya mizinda. Kodi chingachitike n'chiyani ngati titasankha selo lililonse lomwe lili kumanja kwa pepalalo ndikulowetsamo njira yomwe imalumikizana osati ndi selo imodzi, koma nthawi yomweyo ndi gulu?

M'mitundu yonse yam'mbuyomu ya Excel, mukadina Lowani titha kupeza zomwe zili mu cell imodzi yokha B2. Nanga bwanji?

Chabwino, kapena zingatheke kukulunga izi mumtundu wina wa ntchito yophatikizira monga = SUM(B2:C4) ndikupeza kuchuluka kwake.

Ngati timafunikira ma opareshoni ovuta kwambiri kuposa ndalama zakale, monga kuchotsa zinthu zapadera kapena Top 3, ndiye kuti titha kuyika fomula yathu ngati njira yophatikizira pogwiritsa ntchito njira yachidule ya kiyibodi. Ctrl+kosangalatsa+Lowani.

Tsopano zonse ndi zosiyana.

Tsopano titatha kuyika fomula yotere, titha kungodinanso Lowani - ndipo pezani zotsatira zake nthawi yomweyo zikhalidwe zonse uXNUMXbuXNUMXb zomwe tidatchulapo:

Izi si zamatsenga, koma zida zatsopano zomwe Microsoft Excel ili nazo. Takulandilani kudziko latsopano 🙂

Mawonekedwe akugwira ntchito ndi ma dynamic arrays

Mwaukadaulo, gulu lathu lonse losinthika limasungidwa mu cell yoyamba ya G4, ndikudzaza ma cell ofunikira kumanja ndi pansi ndi deta yake. Ngati musankha selo lina lililonse pamndandanda, ndiye kuti ulalo wa fomula udzakhala wosagwira ntchito, kuwonetsa kuti tili mu imodzi mwamaselo a "mwana":

Kuyesa kuchotsa selo limodzi kapena angapo a "mwana" sikungabweretse chilichonse - Excel idzawerengeranso ndikudzaza.

Nthawi yomweyo, titha kuloza ma cell a "ana" awa m'njira zina:

Ngati mungakopere selo loyamba la gulu (mwachitsanzo, kuchokera ku G4 mpaka F8), ndiye kuti gulu lonse (zofotokozera zake) lidzasuntha mofanana ndi machitidwe okhazikika:

Ngati tifunika kusuntha gululo, ndiye kuti lidzakhala lokwanira kusuntha (ndi mbewa kapena kuphatikiza kwa Ctrl+X, Ctrl+V), kachiwiri, selo loyamba lalikulu la G4 - pambuyo pake, lidzasamutsidwa ku malo atsopano ndipo gulu lathu lonse lidzakulitsidwa kachiwiri.

Ngati mukufuna kulozera kwinakwake papepala pamagulu osinthika omwe adapangidwa, ndiye kuti mutha kugwiritsa ntchito mawonekedwe apadera # ("mapaundi") pambuyo pa adilesi yotsogolera:

Mwachitsanzo, tsopano mutha kupanga mndandanda wotsitsa mosavuta mu selo lomwe limatanthawuza gulu losinthika lopangidwa:

Zolakwa zazikulu

Koma chimachitika ndi chiyani ngati palibe malo okwanira kuti awonjezere gululo, kapena ngati pali maselo omwe ali kale ndi deta ina panjira yake? Pezani zolakwika zamtundu watsopano mu Excel - #KUTULUKA! (#PULANI!):

Monga nthawi zonse, ngati tidina pa chithunzi chokhala ndi diamondi yachikasu ndi chilembo chokweza, tipeza kufotokozera mwatsatanetsatane komwe kudayambitsa vutoli ndipo titha kupeza mwachangu ma cell osokoneza:

Zolakwika zofananazo zidzachitika ngati gululo likuchoka papepala kapena kugunda selo lophatikizidwa. Ngati muchotsa chopingacho, ndiye kuti zonse zidzakonzedwa nthawi yomweyo pa ntchentche.

Mitundu yamphamvu ndi matebulo anzeru

Ngati gulu losunthika likulozera ku tebulo la "smart" lopangidwa ndi njira yachidule ya kiyibodi Ctrl+T kapena Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table), ndiye idzalandiranso khalidwe lake lalikulu - auto-size.

Mukawonjezera zatsopano pansi kapena kumanja, tebulo lanzeru ndi mitundu yosunthika idzatambasulanso:

Komabe, pali choletsa chimodzi: sitingagwiritse ntchito tsatanetsatane wamitundu yosiyanasiyana m'mabwalo mkati mwa tebulo lanzeru:

Dynamic arrays ndi zina za Excel

Chabwino, inu mukuti. Zonsezi ndizosangalatsa komanso zoseketsa. Palibe chifukwa, monga kale, kutambasulira pamanja chilinganizocho ndikulozera ku cell yoyamba yamitundu yoyambira pansi ndi kumanja ndi zonsezo. Ndipo ndizo zonse?

Osati choncho.

Dynamic arrays si chida china mu Excel. Tsopano aphatikizidwa mu mtima (kapena ubongo) wa Microsoft Excel - injini yake yowerengera. Izi zikutanthauza kuti mitundu ina ya Excel ndi ntchito zomwe timazidziwa tsopano zimathandizira kugwira ntchito ndi magulu osinthika. Tiyeni tiwone zitsanzo zingapo kuti zikupatseni lingaliro lakuya kwa kusintha komwe kwachitika.

Kutsegula

Kuti musinthe mtundu (kusinthana mizere ndi mizati) Microsoft Excel nthawi zonse imakhala ndi ntchito yomangidwira Mtengo wa TRANSP (TRANSPOSE). Komabe, kuti mugwiritse ntchito, choyamba muyenera kusankha molondola mtundu wazotsatira (mwachitsanzo, ngati zolowetsazo zinali 5 × 3, ndiye kuti mwasankha 3 × 5), ndiye lowetsani ntchitoyi ndikusindikiza batani. kuphatikiza Ctrl+kosangalatsa+Lowani, chifukwa imatha kugwira ntchito mumitundu yosiyanasiyana.

Tsopano mutha kungosankha selo imodzi, lowetsani fomula yomweyo ndikudina yanthawi zonse Lowani - Gulu lamphamvu lidzachita chilichonse palokha:

Kuchulutsa tebulo

Ichi ndiye chitsanzo chomwe ndimakonda kupatsa ndikafunsidwa kuti ndiwonere maubwino amitundu yosiyanasiyana mu Excel. Tsopano, kuti muwerenge tebulo lonse la Pythagorean, ndikwanira kuima mu selo loyamba la B2, lowetsani ndondomeko yomwe imachulukitsa magawo awiri (okwera ndi opingasa a manambala 1..10) ndikungodinanso pa Lowani:

Gluing ndi kutembenuka kwa kesi

Ma Arrays sangachulukitsidwe kokha, komanso kumangirizidwa pamodzi ndi woyendetsa wamba & (ampersand). Tiyerekeze kuti tikufunika kuchotsa dzina loyamba ndi lomaliza pamizati iwiri ndikukonza kulumpha muzolemba zoyambirira. Timachita izi ndi fomula limodzi lalifupi lomwe limapanga gulu lonse, ndiyeno timayika ntchitoyo PROPNACH (ZOYENERA)kukonza registry:

Pomaliza Top 3

Tiyerekeze kuti tili ndi manambala ambiri omwe tikufuna kuti tipeze zotsatira zitatu zapamwamba, kuzikonza motsika. Tsopano izi zimachitika ndi chilinganizo chimodzi ndipo, kachiwiri, popanda Ctrl+kosangalatsa+Lowani monga kale:

Ngati mukufuna kuti zotsatira zikhazikike osati pamzati, koma motsatira, ndiye kuti ndizokwanira kusintha ma colon (olekanitsa mzere) mu fomuyi ndi semicolon (cholekanitsa chinthu mkati mwa mzere umodzi). M'Chingerezi cha Excel, olekanitsa awa ndi ma semicolons ndi ma koma, motsatana.

VLOOKUP imatulutsa zipilala zingapo nthawi imodzi

Nchito VPR (VLOOKUP) tsopano mutha kukoka zikhalidwe osati kuchokera kumodzi, koma kuchokera pamizere ingapo nthawi imodzi - ingotchulani manambala awo (mu dongosolo lililonse lomwe mukufuna) ngati gulu lachitatu la ntchitoyi:

Ntchito ya OFFSET ikubweza mndandanda wosinthika

Chimodzi mwazinthu zosangalatsa komanso zothandiza (pambuyo pa VLOOKUP) pakusanthula deta ndi ntchitoyo Kutaya (ZOCHITIKA), imene ndinathera nthaŵi ina mutu wathunthu m’buku langa ndi nkhani apa. Kuvuta kumvetsetsa ndikuzindikira ntchitoyi nthawi zonse kwakhala kuti idabweza mndandanda (mitundu) ya data monga zotsatira zake, koma sitinathe kuziwona, chifukwa Excel sanadziwebe momwe angagwiritsire ntchito ndi masanjidwe kunja kwa bokosi.

Tsopano vutoli lili m'mbuyomu. Onani momwe tsopano, pogwiritsa ntchito fomula imodzi ndi gulu losinthika lomwe labwezedwa ndi OFFSET, mutha kuchotsa mizere yonse yazinthu zomwe zaperekedwa patebulo lililonse losanjidwa:

Tiyeni tiwone zotsutsana zake:

  • A1 - cell yoyambira (malo oyambira)
  • ПОИСКПОЗ(F2;A2:A30;0) - kuwerengera kusintha kuchokera ku selo loyambira pansi - kupita ku kabichi woyamba kupezeka.
  • 0 - Kusintha kwa "zenera" kupita kumanja kumanja kwa selo loyambira
  • СЧЁТЕСЛИ(A2:A30;F2) - kuwerengera kutalika kwa "zenera" lobwerera - kuchuluka kwa mizere komwe kuli kabichi.
  • 4 - kukula kwa "zenera" mopingasa, mwachitsanzo, mizati 4

Ntchito Zatsopano za Dynamic Arrays

Kuphatikiza pa kuthandizira makina osinthika muzochita zakale, ntchito zingapo zatsopano zawonjezeredwa ku Microsoft Excel, zonoleredwa makamaka kuti zigwire ntchito ndi magulu osinthika. Makamaka, awa ndi:

  • kalasi (SINTHA) - amasankha zolowetsamo ndikupanga magulu osinthika pazotulutsa
  • SORTPO (SANJANI POTENGERA) - imatha kusanja mtundu umodzi ndi zikhalidwe kuchokera ku wina
  • FILTER (SEFA) - imapezanso mizere kuchokera kumagwero omwe amakwaniritsa zomwe zatchulidwa
  • UNIK (CHIYAMBIRA) - amachotsa zinthu zapadera kuchokera pagulu kapena kuchotsa zobwereza
  • SLMASSIVE (RANARRAY) - imapanga manambala amtundu wamtundu womwe waperekedwa
  • ATANGOBADWA (SEQUENCE) - imapanga mndandanda kuchokera pamndandanda wa manambala ndi sitepe yoperekedwa

Zambiri za iwo - pakapita nthawi. Ndiwofunika nkhani yosiyana (osati imodzi) yophunzirira mozama 🙂

Mawuwo

Ngati mwawerenga zonse zomwe zalembedwa pamwambapa, ndiye ndikuganiza kuti mukuzindikira kale kuchuluka kwa zosintha zomwe zachitika. Zinthu zambiri mu Excel zitha kuchitidwa mosavuta, zosavuta komanso zomveka. Ndiyenera kuvomereza kuti ndikudabwa pang'ono kuti ndi nkhani zingati zomwe zikuyenera kuwongoleredwa pano, patsamba lino ndi m'mabuku anga, koma ndine wokonzeka kuchita izi ndi mtima wopepuka.

Kufotokozera mwachidule zotsatira, kuphatikiza dynamic arrays, mutha kulemba izi:

  • Mukhoza kuiwala za kuphatikiza Ctrl+kosangalatsa+Lowani. Excel tsopano sakuwona kusiyana pakati pa "mafomu okhazikika" ndi "mafomu osiyanasiyana" ndipo amawachitira chimodzimodzi.
  • Za ntchito SUMPRODUCT (SUMPRODUCT), yomwe idagwiritsidwa ntchito m'mbuyomu kuyika mafomu osiyanasiyana popanda Ctrl+kosangalatsa+Lowani mutha kuyiwalanso - tsopano ndi zophweka mokwanira SUM и Lowani.
  • Matebulo anzeru ndi magwiridwe antchito omwe amadziwika bwino (SUM, IF, VLOOKUP, SUMIFS, ndi zina.) tsopano amathandizira kwathunthu kapena pang'ono magulu osinthika.
  • Pali kuyanjana kobwerera m'mbuyo: ngati mutsegula bukhu lantchito lomwe lili ndi magawo osinthika mumtundu wakale wa Excel, amasinthidwa kukhala mitundu yosiyanasiyana (mu ma curly braces) ndikupitiliza kugwira ntchito "munjira yakale".

Ndinapeza nambala ina minuses:

  • Simungathe kuchotsa mizere, zipilala kapena ma cell kuchokera kumagulu osiyanasiyana, mwachitsanzo, amakhala ngati chinthu chimodzi.
  • Simungathe kusanja gulu losinthika mwanjira yanthawi zonse Data - Kusanja (Deta - Sanjani). Tsopano pali ntchito yapadera ya izi. kalasi (SINTHA).
  • Gulu losinthika silingasinthidwe kukhala tebulo lanzeru (koma mutha kupanga masinthidwe osinthika kutengera tebulo lanzeru).

Zachidziwikire, uku sikumapeto, ndipo ndikukhulupirira kuti Microsoft ipitiliza kukonza makinawa mtsogolomo.

Kodi ndingatsitse kuti?

Ndipo potsiriza, funso lalikulu 🙂

Microsoft idalengeza koyamba ndikuwonetsa chithunzithunzi chamagulu amphamvu ku Excel kumbuyo mu Seputembara 2018 pamsonkhano Musanyalanyaze. M'miyezi ingapo yotsatira, panali kuyezetsa mozama ndikuyendetsa zatsopano, poyamba amphaka ogwira ntchito a Microsoft palokha, ndiyeno pa oyesa odzipereka kuchokera ku gulu la Office Insiders. Chaka chino, zosintha zomwe zimawonjezera zida zamphamvu zidayamba kutulutsidwa pang'onopang'ono kwa olembetsa a Office 365. Mwachitsanzo, ndidalandira mu Ogasiti ndikulembetsa ku Office 365 Pro Plus (Monthly Targeted).

Ngati Excel yanu ilibe magawo osinthika, koma mukufunadi kugwira nawo ntchito, ndiye kuti pali zotsatirazi:

  • Ngati mwalembetsa ku Office 365, mutha kungodikirira mpaka izi zikufikani. Momwe izi zimachitikira mwachangu zimatengera momwe zosintha zimaperekedwa ku Ofesi yanu (kamodzi pachaka, kamodzi miyezi isanu ndi umodzi iliyonse, kamodzi pamwezi). Ngati muli ndi PC yamakampani, mutha kufunsa woyang'anira wanu kuti akhazikitse zosintha kuti zitsitsidwe pafupipafupi.
  • Mutha kulowa nawo m'gulu la odzipereka oyesa a Office Insiders - ndiye mudzakhala woyamba kulandira zatsopano ndi ntchito (koma pali mwayi wowonjezera ngolo mu Excel, inde).
  • Ngati mulibe zolembetsa, koma mtundu wa Excel wokhala ndi bokosi, ndiye kuti muyenera kudikirira mpaka kutulutsidwa kwa mtundu wina wa Office ndi Excel mu 2022, osachepera. Ogwiritsa ntchito mitundu yotere amangolandira zosintha zachitetezo ndi kukonza zolakwika, ndipo "zabwino" zonse zatsopano zimangopita kwa olembetsa a Office 365 okha. Zachisoni koma zoona 🙂

Mulimonsemo, magulu amphamvu akawoneka mu Excel - pambuyo pa nkhaniyi, mudzakhala okonzeka 🙂

  • Kodi mafomula ndi momwe angagwiritsire ntchito mu Excel
  • Zenera (range) kuphatikiza pogwiritsa ntchito ntchito ya OFFSET
  • Njira za 3 Zosinthira Tebulo mu Excel

Siyani Mumakonda