Daptar eusi
Naon arrays dinamis
Dina Séptémber 2018, Microsoft ngaluarkeun pembaruan anu nambihan alat énggal ka Microsoft Excel: Dynamic Arrays sareng 7 fungsi énggal pikeun damel sareng aranjeunna. Hal-hal ieu, tanpa kaleuleuwihan, sacara radikal ngarobih sadaya téknik anu biasa damel sareng rumus sareng fungsi sareng perhatian, sacara harfiah, unggal pangguna.
Mertimbangkeun conto basajan pikeun ngajelaskeun hakekat.
Anggap urang boga tabel basajan kalawan data dina kota-bulan. Naon anu bakal kajantenan upami urang milih sél kosong anu aya di sisi katuhu lambaran sareng lebetkeun kana éta rumus anu henteu nyambung ka hiji sél, tapi langsung ka rentang?
Dina sadaya vérsi Excel sateuacana, saatos ngaklik asup urang bakal meunang eusi ngan hiji sél munggaran B2. Kumaha deui?
Nya, atanapi tiasa waé mungkus rentang ieu dina sababaraha jinis fungsi agrégat sapertos =SUM (B2: C4) sareng kéngingkeun jumlahna ageung.
Upami urang peryogi operasi anu langkung kompleks tibatan jumlah primitif, sapertos ékstrak nilai unik atanapi Top 3, maka urang kedah ngalebetkeun rumus urang salaku rumus array nganggo potong kompas keyboard. Ctrl+ngarobah+asup.
Ayeuna sagalana geus béda.
Ayeuna saatos ngalebetkeun rumus sapertos kitu, urang ngan saukur tiasa klik asup - sareng kéngingkeun hasilna langsung sadaya nilai uXNUMXbuXNUMXbto anu kami rujuk:
Ieu sanés sihir, tapi susunan dinamis énggal anu ayeuna dipiboga ku Microsoft Excel. Wilujeng sumping di dunya anyar 🙂
Fitur gawé bareng arrays dinamis
Téhnisna, sakabéh Asép Sunandar Sunarya dinamis urang disimpen dina sél munggaran G4, ngeusian jumlah diperlukeun sél ka katuhu jeung ka handap kalawan data na. Upami anjeun milih sél anu sanés dina susunan, teras tautan dina bar rumus bakal teu aktip, nunjukkeun yén kami aya dina salah sahiji sél "anak":
Usaha mupus hiji atanapi langkung sél "anak" moal ngakibatkeun nanaon - Excel bakal langsung ngitung deui sareng ngeusianana.
Dina waktos anu sami, urang aman tiasa ngarujuk kana sél "anak" ieu dina rumus anu sanés:
Lamun anjeun nyalin sél mimiti hiji Asép Sunandar Sunarya (contona, ti G4 mun F8), lajeng sakabéh Asép Sunandar Sunarya (referensi na) bakal mindahkeun dina arah nu sarua sakumaha dina rumus biasa:
Lamun urang kudu mindahkeun Asép Sunandar Sunarya, mangka bakal cukup pikeun mindahkeun (jeung mouse atawa kombinasi Ctrl+X, Ctrl+V), sakali deui, ngan ukur sél G4 utama anu munggaran - saatosna, éta bakal dialihkeun ka tempat anu énggal sareng sadayana Asép Sunandar Sunarya bakal dilegakeun deui.
Upami anjeun kedah ngarujuk ka tempat anu sanés dina lambaran kana susunan dinamis anu diciptakeun, anjeun tiasa nganggo karakter khusus # ("pound") saatos alamat sél anu ngarah:
Contona, ayeuna anjeun bisa kalayan gampang nyieun daptar dropdown dina sél nu nujul kana susunan dinamis dijieun:
Kasalahan susunan dinamis
Tapi naon anu lumangsung lamun aya teu cukup spasi rék dilegakeun Asép Sunandar Sunarya, atawa lamun aya sél geus dikawasaan ku data sejenna dina jalur na? Minuhan jinis kasalahan anu dasarna anyar dina Excel - #MINDAHKEUN! (#SAMPEL!):
Sakumaha biasa, upami urang ngaklik ikon kalayan inten konéng sareng tanda seru, urang bakal nampi katerangan anu langkung rinci ngeunaan sumber masalah sareng urang gancang mendakan sél anu ngaganggu:
Kasalahan anu sami bakal kajantenan upami susunanna kaluar tina lambaran atanapi pencét sél anu dihijikeun. Upami anjeun ngaleungitkeun halangan, maka sadayana bakal langsung dilereskeun dina laleur.
Array dinamis sareng tabel pinter
Lamun Asép Sunandar Sunarya dinamis nunjuk ka tabel "pinter" dijieun ku potong kompas keyboard Ctrl+T atanapi ku Imah - Format salaku méja (Imah - Format salaku Méja), mangka ogé bakal inherit kualitas utamina - otomatis-sizing.
Nalika nambahkeun data anyar ka handap atawa ka katuhu, tabel pinter jeung rentang dinamis ogé bakal otomatis manjang:
Nanging, aya hiji watesan: urang teu tiasa nganggo rujukan rentang dinamis dina forum di jero méja pinter:
Array dinamis sareng fitur Excel anu sanés
Oke, ceuk anjeun. Sadaya ieu metot jeung lucu. Teu kedah, sapertos sateuacana, sacara manual manteng rumus kalayan rujukan ka sél mimiti rentang aslina ka handap sareng ka katuhu sareng sadayana. Sareng éta sadayana?
Teu cukup.
Asép Sunandar Sunarya dinamis sanés ngan ukur alat anu sanés dina Excel. Ayeuna aranjeunna dipasang dina jantung (atanapi otak) Microsoft Excel - mesin itunganna. Ieu ngandung harti yén rumus sareng fungsi Excel sanés anu akrab pikeun urang ayeuna ogé ngadukung damel sareng susunan dinamis. Hayu urang tingali sababaraha conto pikeun masihan anjeun ide ngeunaan jerona parobihan anu parantos kajantenan.
Transposional
Pikeun transpose rentang (swap baris jeung kolom) Microsoft Excel geus salawasna miboga fungsi diwangun-di TRANSP (TRANPOSE). Nanging, supados tiasa dianggo, anjeun kedah leres-leres milih kisaran hasil (contona, upami inputna kisaran 5×3, maka anjeun kedah milih 3×5), teras lebetkeun pungsi sareng pencét kombinasi Ctrl+ngarobah+asup, sabab ngan bisa jalan dina modeu rumus Asép Sunandar Sunarya.
Ayeuna anjeun ngan ukur tiasa milih hiji sél, asupkeun rumus anu sami kana éta teras klik dina normal asup - Asép Sunandar Sunarya dinamis bakal ngalakukeun sadayana nyalira:
méja multiplikasi
Ieu mangrupikeun conto anu kuring biasa masihan nalika kuring dipenta pikeun ngabayangkeun mangpaat rumus Asép Sunandar Sunarya dina Excel. Ayeuna, keur ngitung sakabéh tabel Pythagorean, éta cukup pikeun nangtung dina sél munggaran B2, lebetkeun aya rumus nu multiplies dua arrays (set nangtung sarta horizontal angka 1..10) jeung saukur klik dina. asup:
Gluing sarta konvérsi kasus
Arrays teu ngan bisa dikalikeun, tapi ogé glued babarengan jeung operator baku & (ampersand). Anggap urang kudu nimba ngaran hareup jeung tukang ti dua kolom jeung ngabenerkeun kasus jumping dina data aslina. Urang ngalakukeun ieu ku hiji rumus pondok nu ngabentuk sakabéh Asép Sunandar Sunarya, lajeng urang nerapkeun fungsi eta PROPNACH (PROPER)pikeun ngabersihan daptar:
Kacindekan Top 3
Anggap urang boga kebat angka ti mana urang hayang diturunkeun tilu hasil luhur, nyusun aranjeunna dina urutan nurun. Ayeuna ieu dilakukeun ku hiji rumus sareng, deui, tanpa aya Ctrl+ngarobah+asup kawas saméméhna:
Upami anjeun hoyong hasilna henteu disimpen dina kolom, tapi sakaligus, maka cukup pikeun ngagentos titik-titik (separator garis) dina rumus ieu sareng titik koma (separator unsur dina hiji garis). Dina vérsi Inggris Excel, pemisah ieu nyaéta titik koma sareng koma, masing-masing.
VLOOKUP ékstrak sababaraha kolom sakaligus
fungsi VPR (VLOOKUP) Ayeuna anjeun tiasa narik nilai sanés tina hiji, tapi tina sababaraha kolom sakaligus - ngan ukur nunjukkeun nomerna (dina urutan anu dipikahoyong) salaku susunan dina argumen katilu fungsina:
Fungsi OFFSET ngabalikeun susunan dinamis
Salah sahiji fungsi anu paling narik sareng mangpaat (sanggeus VLOOKUP) pikeun analisis data nyaéta fungsi Buang (OFFSET), nu kuring devoted dina hiji waktu sakabeh bab dina buku kuring jeung artikel di dieu. Kasusah dina pamahaman sarta mastering fungsi ieu salawasna geus yén éta balik hiji Asép Sunandar Sunarya (rentang) data salaku hasilna, tapi urang teu bisa ningali eta, sabab Excel masih teu nyaho kumaha carana dianggo kalayan arrays out of the box.
Ayeuna masalah ieu geus kaliwat. Tingali kumaha ayeuna, nganggo rumus tunggal sareng susunan dinamis anu dipulangkeun ku OFFSET, anjeun tiasa nimba sadaya baris pikeun produk anu dipasihkeun tina tabel anu diurutkeun:
Hayu urang tingali argumen nya:
- A1 - sél mimiti (titik rujukan)
- ПОИСКПОЗ(F2;A2:A30;0) - itungan shift tina sél mimiti ka handap - ka kol munggaran kapanggih.
- 0 - mindahkeun "jandela" ka katuhu relatif ka sél mimiti
- СЧЁТЕСЛИ(A2:A30;F2) - itungan jangkungna "jandela" balik - jumlah garis dimana aya kol.
- 4 - ukuran "jandela" horisontal, nyaéta kaluaran 4 kolom
Fungsi Anyar pikeun Array Dinamis
Salian ngarojong mékanisme array dinamis dina fungsi heubeul, sababaraha fungsi lengkep anyar geus ditambahkeun kana Microsoft Excel, diasah husus pikeun gawé bareng arrays dinamis. Khususna, ieu nyaéta:
- kelas (SORT) - sorts rentang input sarta ngahasilkeun Asép Sunandar Sunarya dinamis dina kaluaran
- SORTPO (DIURUTKEUN DUMASARKEUN) - tiasa nyortir hiji rentang ku nilai ti anu sanés
- saringan (FILTER) – retrieves baris ti rentang sumber nu minuhan kaayaan dieusian
- UNIK (UNIK) - nimba nilai unik tina kisaran atanapi ngahapus duplikat
- SLMASSIVE (RANDARRAY) - ngahasilkeun hiji Asép Sunandar Sunarya angka acak tina ukuran nu tangtu
- SARENG BAHIR (RUNTUNGAN) - ngabentuk Asép Sunandar Sunarya ti runtuyan angka kalawan hambalan dibikeun
Langkung seueur ngeunaan aranjeunna - sakedik engké. Éta patut artikel misah (teu hiji) pikeun ulikan wijaksana 🙂
conclusions
Upami anjeun parantos maca sadayana anu diserat di luhur, maka kuring pikir anjeun parantos sadar kana skala parobihan anu parantos kajantenan. Janten seueur hal dina Excel ayeuna tiasa dilakukeun langkung gampang, langkung gampang sareng langkung logis. Kuring kedah ngaku yén kuring rada ngareureuwas dina sabaraha artikel ayeuna kedah dilereskeun di dieu, dina situs ieu sareng dina buku kuring, tapi kuring siap ngalakukeun ieu kalayan ati anu hampang.
Nyimpulkeun hasil, pro arrays dinamis, anjeun tiasa nyerat ieu:
- Anjeun tiasa poho ngeunaan kombinasi Ctrl+ngarobah+asup. Excel ayeuna teu ningali bédana antara "rumus biasa" sareng "rumus array" sareng ngarawatna ku cara anu sami.
- Ngeunaan fungsi SUMPRODUCT (SUMPRODUCT), nu saméméhna dipaké pikeun nuliskeun rumus Asép Sunandar Sunarya tanpa Ctrl+ngarobah+asup anjeun ogé tiasa hilap - ayeuna éta cukup gampang jumlah и asup.
- Méja pinter sareng fungsi akrab (SUM, IF, VLOOKUP, SUMIFS, jsb) ayeuna ogé pinuh atanapi sawaréh ngadukung arrays dinamis.
- Aya kasaluyuan mundur: upami anjeun muka buku kerja sareng susunan dinamis dina versi Excel anu lami, aranjeunna bakal janten rumus Asép Sunandar Sunarya (dina kurung keriting) sareng teras damel dina "gaya lami".
Kapanggih sababaraha nomer minuses:
- Anjeun teu bisa mupus baris individu, kolom atawa sél tina Asép Sunandar Sunarya dinamis, nyaeta hirup salaku hiji éntitas tunggal.
- Anjeun teu bisa nyortir hiji Asép Sunandar Sunarya dinamis dina cara biasa ngaliwatan Data - Asihan (Data - Urut). Ayeuna aya fungsi husus pikeun ieu. kelas (SORT).
- A rentang dinamis teu bisa robah jadi tabel pinter (tapi anjeun bisa nyieun rentang dinamis dumasar kana tabel pinter).
Tangtosna, ieu sanés tungtungna, sareng kuring yakin Microsoft bakal terus ningkatkeun mékanisme ieu ka hareup.
Dimana abdi tiasa ngundeur?
Sarta pamustunganana, patarosan utama 🙂
Microsoft mimiti ngumumkeun sareng nunjukkeun sawangan ngeunaan susunan dinamis dina Excel dina Séptémber 2018 dina konperénsi ngahurungkeun. Dina sababaraha bulan ka hareup, aya uji lengkep sareng ngajalankeun fitur-fitur anyar, mimitina ucing karyawan Microsoft sorangan, lajeng dina testers volunteer ti bunderan Office Insiders. Taun ieu, pembaruan anu nambihan susunan dinamis mimiti laun-laun digulung ka palanggan Office 365 biasa. Salaku conto, kuring ngan ukur nampi dina bulan Agustus kalayan langganan Office 365 Pro Plus (Sasaran Bulanan).
Upami Excel anjeun henteu acan gaduh susunan dinamis, tapi anjeun hoyong damel sareng aranjeunna, maka aya pilihan ieu:
- Upami anjeun gaduh langganan Office 365, anjeun tiasa ngantosan dugi apdet ieu dugi ka anjeun. Sakumaha gancangna ieu kajadian gumantung kana sabaraha sering apdet dikirimkeun ka Kantor anjeun (sataun sakali, genep bulan sakali, sabulan sakali). Upami anjeun gaduh PC perusahaan, anjeun tiasa naroskeun ka administrator anjeun pikeun nyetél apdet supados langkung sering diunduh.
- Anjeun tiasa gabung dina jajaran sukarelawan uji Office Insiders - maka anjeun bakal janten anu munggaran nampi sadaya fitur sareng fungsi énggal (tapi aya kasempetan pikeun ningkat buggy di Excel, tangtosna).
- Upami anjeun teu gaduh langganan, tapi versi Excel mandiri kotak, maka anjeun kedah ngantosan dugi ka sékrési versi Office sareng Excel salajengna dina 2022, sahenteuna. Pamaké vérsi sapertos kitu ngan ukur nampi apdet kaamanan sareng perbaikan bug, sareng sadaya "goodies" énggal ayeuna ngan ukur ka palanggan Office 365. Sedih tapi leres 🙂
Dina naon waé, nalika susunan dinamis muncul dina Excel anjeun - saatos tulisan ieu, anjeun bakal siap 🙂
- Naon rumus Asép Sunandar Sunarya sareng kumaha ngagunakeunana dina Excel
- Jandéla (rentang) penjumlahan ngagunakeun fungsi OFFSET
- 3 Cara pikeun Transpose méja dina Excel