„Excel“

Dinaminių masyvų formulės „Excel“

Dynamic Array Formulas Excel

Dinaminiai masyvai yra didžiausias „Excel“ formulių pakeitimas per pastaruosius metus. Galbūt didžiausias pokytis. Taip yra todėl, kad dinaminiai masyvai leidžia lengvai dirbti su keliomis vertėmis vienu metu formulėje. Daugeliui vartotojų tai bus pirmas kartas, kai jie supranta ir naudoja masyvo formules.





Tai didelis atnaujinimas ir sveikintinas pokytis. „Dinaminiai masyvai“ išspręs tikrai sunkias „Excel“ problemas ir iš esmės pakeis darbo lapų projektavimo ir konstravimo būdą.

Prieinamumas

Galimi tik dinaminiai masyvai ir naujos funkcijos „Excel 365“ . „Excel 2016“ ir „Excel 2019“ neteikia dinaminio masyvo formulių palaikymo. Kad būtų patogiau, naudosiu „Dynamic Excel“ („Excel 365“) ir „Traditional Excel“ (2019 m. Ar ankstesnė), kad atskirtų toliau pateiktas versijas.





Nauja: „Dynamic Array Formula“ vaizdo įrašų mokymai

Naujos funkcijos

Kaip dinaminio masyvo atnaujinimo dalis, „Excel“ dabar turi 8 naujas funkcijas, kurios tiesiogiai naudoja dinaminius masyvus, kad išspręstų problemas, kurias tradiciškai sunku išspręsti naudojant įprastas formules. Norėdami gauti išsamesnės informacijos apie kiekvieną funkciją ir jos pavyzdžius, spustelėkite toliau pateiktas nuorodas:

Funkcija Paskirtis
FILTRAS Filtruokite duomenis ir grąžinkite atitinkamus įrašus
RANDARRAY Sukurkite atsitiktinių skaičių masyvą
SEKA Sukurkite nuoseklių skaičių masyvą
RŪŠIUOTI Rūšiuoti diapazoną pagal stulpelį
RŪŠIUOTI PAGAL Rūšiuoti diapazoną pagal kitą diapazoną ar masyvą
UNIKALUS Ištraukite unikalias vertes iš sąrašo ar diapazono
XLOOKUP Modernus VLOOKUP pakaitalas
XMATCH Modernus MATCH funkcijos pakeitimas

Vaizdo įrašas: Naujos „Excel“ dinaminio masyvo funkcijos (apie 3 minutes).



Excel konvertuoti minutes į valandas ir minutes

Pastaba: XLOOKUP ir XMATCH nebuvo pradinėje naujų dinaminio masyvo funkcijų grupėje, tačiau jos puikiai veikia su nauju dinaminio masyvo varikliu. „XLOOKUP“ pakeičia „VLOOKUP“ ir siūlo modernų, lankstų požiūrį, kuriame naudojami masyvai. „XMATCH“ yra „MATCH“ funkcijos atnaujinimas, suteikiantis naujų galimybių INDEX ir MATCH formules.

Pavyzdys

Prieš pradėdami gilintis į detales, pažvelkime į paprastą pavyzdį. Žemiau mes naudojame naują UNIKALI funkcija išskirti unikalias vertes iš diapazono B5: B15, naudojant a vienišas formulė, įvesta į E5:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

UNIKALUS funkcijos pavyzdys

Rezultatas yra penkių unikalių miestų pavadinimų sąrašas, rodomas E5: E9.

UNIQUE, kaip ir visos formulės, bus automatiškai atnaujinamas, kai duomenys pasikeis. Toliau Vankuveris pakeitė Portlandą 11 eilutėje. UNIQUE rezultatas dabar apima Vankuverį:

UNIKALUS funkcijos pavyzdys po pakeitimo

Išsiliejimas - viena formulė, daug vertybių

„Dynamic Excel“ formulės, kurios grąžina kelias reikšmes, žaidimas “šias vertes tiesiai į darbalapį. Formulių vartotojams tai iškart bus logiškiau. Tai taip pat visiškai dinamiškas elgesys - pasikeitus šaltinio duomenims, išsilieję rezultatai bus nedelsiant atnaujinti.

Stačiakampis, apimantis reikšmes, vadinamas „ žaidimo diapazonas “. Pastebėsite, kad išsiliejimo diapazonas yra ypatingai paryškintas. Aukščiau pateiktame unikaliame pavyzdyje išsiliejimo diapazonas yra E5: E10.

Pasikeitus duomenims, išsiliejimo diapazonas prireikus padidės arba susitrauks. Gali būti, kad bus pridėtos naujos vertės arba išnyks esamos. Tokiu būdu išsiliejimo diapazonas yra naujos rūšies dinaminis diapazonas.

Pastaba: kai išsiliejimą blokuoja kiti duomenys, pamatysite #SPILL klaidą. Kai paliekate vietos išsiliejimo diapazonui, formulė automatiškai išsilieja.

Vaizdo įrašas: Išsiliejimas ir išsiliejimo diapazonas

Išsiliejimo diapazono nuoroda

Norėdami nurodyti išsiliejimo diapazoną, naudokite maišos simbolį (#) po pirmojo diapazono langelio. Pavyzdžiui, norėdami nurodyti aukščiau pateiktos funkcijos UNIQUE rezultatus, naudokite:

 
=E5# // reference UNIQUE results

Tai yra tas pats, kas nuoroda į visą išsiliejimo diapazoną, ir šią sintaksę pamatysite rašydami formulę, nurodančią visą išsiliejimo diapazoną.

Išsiliejimo diapazono nuorodą galite tiesiogiai įtraukti į kitas formules. Pavyzdžiui, norėdami suskaičiuoti UNIQUE grąžintų miestų skaičių, galite naudoti:

 
= COUNTA (E5#) // count unique cities

Dinaminio masyvo išsiliejimo diapazono nuorodos pavyzdys

Pasikeitus išsiliejimo diapazonui, formulė atspindės naujausius duomenis.

Masinis supaprastinimas

Pridėjus naujų dinaminio masyvo formulių, tam tikros formulės gali būti labai supaprastintos. Štai keletas pavyzdžių:

  • Išskirkite ir išvardykite unikalias vertes ( anksčiau | po )
  • Skaičiuokite unikalias vertes ( anksčiau | po )
  • Filtruoti ir ištraukti įrašus ( anksčiau | po )
  • Išgauti dalines atitiktis ( anksčiau | po )

Vieno galia

Vienas iš galingiausių metodo „viena formulė, daug vertybių“ privalumų yra mažiau priklausomybė absoliutus arba sumaišyti nuorodos. Kadangi dinaminio masyvo formulė išlieja rezultatus į darbalapį, nuorodos lieka nepakitusios, tačiau formulė generuoja teisingus rezultatus.

Pavyzdžiui, žemiau mes naudojame funkciją FILTER, kad išgautume „A“ grupės įrašus. F5 langelyje įvedama viena formulė:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

Tik dinaminės masyvo vienos formulės pavyzdys

Atkreipkite dėmesį, kad abu diapazonai yra atrakinamos santykinės nuorodos, tačiau formulė puikiai veikia.

Tai didžiulė nauda daugeliui vartotojų, nes tai labai supaprastina formulių rašymo procesą. Dar vieną gerą pavyzdį rasite žemiau esančioje daugybos lentelėje.

Grandinių funkcijos

Viskas tampa tikrai įdomu, kai sujungiate daugiau nei vieną dinaminio masyvo funkciją. Galbūt norite rūšiuoti UNIQUE grąžintus rezultatus? Lengva. Tiesiog apvyniokite SORT funkcija aplink unikalią funkciją taip:

UNIKALIO ir RŪŠIUOTI kartu pavyzdys

Kaip ir anksčiau, pasikeitus šaltinio duomenims, automatiškai pasirodo nauji unikalūs rezultatai, gražiai surūšiuoti.

Vietinis elgesys

Svarbu suprasti, kad dinaminio masyvo elgesys yra gimtoji ir giliai integruota . Kada bet koks formulė grąžina kelis rezultatus, šie rezultatai išsilieja į kelis darbalapio langelius. Tai apima senesnes funkcijas, kurios iš pradžių nebuvo sukurtos dirbti su dinaminiais masyvais.

Pavyzdžiui, tradicinėje „Excel“, jei pateikiame LEN funkcija į diapazonas teksto reikšmių, pamatysime a vienišas rezultatas. „Dynamic Excel“, jei suteiksime LEN funkcijai reikšmių diapazoną, pamatysime daugkartinis rezultatus. Šiame ekrane rodomas senas elgesys kairėje ir naujas elgesys dešinėje:

LEN funkcija su masyvais - sena ir nauja

Tai didžiulis pokytis, galintis paveikti visų rūšių formules. Pavyzdžiui, Funkcija VLOOKUP yra skirta iš lentelės gauti vieną vertę, naudojant stulpelių indeksą. Tačiau naudojant „Dynamic Excel“, jei VLOOKUP suteiksime daugiau nei vieną stulpelio indeksą naudodami masyvo konstanta kaip šitas:

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP grąžins kelis stulpelius:

Keli rezultatai naudojant VLOOKUP ir dinaminius masyvus

Kitaip tariant, nors „VLOOKUP“ niekada nebuvo sukurta taip, kad grąžintų kelias reikšmes, dabar ji gali tai padaryti dėl naujo „Dynamic Excel“ formulės variklio.

Visos formulės

Galiausiai atkreipkite dėmesį, kad dinaminiai masyvai veikia su visos formulės ne tik funkcijas . Žemiau esančiame pavyzdyje C5 langelyje yra viena formulė:

konvertuoti gmt į est Excel
 
=B5:B14*C4:L4

Rezultatas patenka į 10–10 diapazoną, kuriame yra 100 ląstelių:

Dinaminių masyvų daugybos lentelė

Pastaba: Jei naudojate tradicinę „Excel“, galite matyti kelis rezultatus, pateiktus pagal masyvo formulę Norėdami patikrinti formulę, naudokite F9 . Bet nebent įvesite formulę kaip kelių ląstelių masyvo formulė , darbalapyje bus rodoma tik viena reikšmė.

Masyvai patenka į pagrindinę sritį

Išleidus dinaminius masyvus, žodis „ masyvas “pasirodys daug dažniau. Tiesą sakant, „masyvas“ ir „diapazonas“ gali būti naudojami beveik pakaitomis. Pamatysite „Excel“ masyvus, išdėstytus garbanotomis skliausteliais:

 
{1,2,3} // horizontal array {123} // vertical array

Masyvas yra programavimo terminas, nurodantis elementų, rodomų tam tikra tvarka, sąrašą. Priežastis, kodėl „Excel“ formulėse masyvai atsiranda taip dažnai, yra ta, kad masyvai gali puikiai išreiškia reikšmes ląstelių diapazone .

Vaizdo įrašas: Kas yra masyvas?

Masyvo operacijos tampa svarbios

Kadangi dinaminės „Excel“ formulės gali lengvai dirbti su keliomis reikšmėmis, masyvo operacijos taps svarbesnės. Terminas „masyvo operacija“ reiškia išraišką, kuri masyvui atlieka loginį testą arba matematinę operaciją. Pavyzdžiui, toliau pateikta išraiška patikrina, ar B5: B9 reikšmės yra lygios „ca“

 
=B5:B9='ca' // state = 'ca'

Masyvo veikimo pavyzdys testas a

kadangi B5: B9 yra 5 langeliai, rezultatas yra 5 TRUE/FALSE reikšmės masyve:

 
{FALSETRUEFALSETRUETRUE}

Žemiau esanti masyvo operacija tikrina, ar sumos yra didesnės nei 100:

 
=C5:C9>100 // amounts > 100

Masyvo veikimo pavyzdys bandymas b

Paskutinė masyvo operacija sujungia testą A ir testą B į vieną išraišką:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

Masyvo veikimo pavyzdys a ir b

Pastaba: „Excel“ matematinės operacijos metu automatiškai priverčia TRUE ir FALSE reikšmes 1 ir 0.

Norėdami tai sugrąžinti į dinaminių masyvų formules programoje „Excel“, toliau pateiktame pavyzdyje parodyta, kaip mes galime naudoti lygiai tą pačią masyvo operaciją FILTRO funkcijoje kaip ir įtraukti argumentas:

Masyvo valdymas su FILTER funkcija

FILTER grąžina du įrašus, kuriuose būsena = 'ca' ir suma> 100.

Demonstracijai žr. Kaip filtruoti pagal du kriterijus (vaizdo įrašas).

Naujos ir senos masyvo formulės

„Dynamic Excel“ nereikia įvesti masyvo formulių naudojant „Control“ + „Shift“ + „Enter“. Kai kuriama formulė, „Excel“ patikrina, ar formulė gali pateikti kelias reikšmes. Jei taip, jis bus automatiškai išsaugotas kaip dinaminio masyvo formulė, tačiau nematysite garbanotų skliaustų. Žemiau pateiktame pavyzdyje parodyta tipinė masyvo formulė, įvesta į „Dynamic Excel“:

Pagrindinė masyvo formulė tradicinėje „Excel“

Jei atidarysite tą pačią formulę tradicinėje „Excel“ programoje, pamatysite garbanotas petnešas:

Pagrindinė masyvo formulė dinaminėje „Excel“

Kita kryptimi, kai „Dynamic Excel“ atidaroma „tradicinė“ masyvo formulė, formulės juostoje pamatysite garbanotas skliaustus. Pavyzdžiui, toliau pateiktame ekrane rodoma paprasta masyvo formulė tradicinėje „Excel“:

Paprasta masyvo formulė su matomomis garbanotomis petnešomis

Tačiau jei iš naujo įvedate formulę be pakeitimų, garbanotieji skliausteliai pašalinami ir formulė pateikia tą patį rezultatą:

Paprasta masyvo formulė su garbanotomis petnešomis nematoma

Esmė ta, kad masyvo formulės, įvestos naudojant „Control“ + „Shift“ + „Enter“ (TPV), vis dar veikia, kad išlaikytų suderinamumą, tačiau jums nereikės įvesti masyvo formulių su TPP „Dynamic Excel“.

Charakteris

Įvedę dinaminius masyvus, formulėse @ simbolis bus rodomas dažniau. Simbolis @ įgalina elgesį, vadinamą „ numanoma sankryža “. Netiesioginis susikirtimas yra logiškas procesas, kai daugelis reikšmių sumažinamos iki vienos vertės.

Tradicinėje „Excel“ numanoma sankirta yra tylus elgesys, naudojamas (kai reikia), siekiant sumažinti kelias reikšmes iki vieno rezultato viename langelyje. „Dynamic Excel“ to paprastai nereikia, nes į darbalapį gali išsilieti keli rezultatai. Kai to reikia, numanomas susikirtimas iškviečiamas rankiniu būdu, naudojant simbolį @.

Atidarant skaičiuokles, sukurta senesnė „Excel“ versija, galite matyti, kad @ simbolis automatiškai pridedamas prie esamų formulių, kuriose yra potencialus grąžinti daug vertybių. „Tradicinėje„ Excel “formulėje, kuri pateikia kelias reikšmes, darbalapis nebus išsiliejęs. Simbolis @ priverčia tą patį veikti „Dynamic Excel“, kad formulė veiktų taip pat ir grąžintų tą patį rezultatą, kaip ir pradinėje „Excel“ versijoje.

Kitaip tariant, pridedamas @, kad senesnė formulė neišleistų kelių rezultatų į darbalapį. Priklausomai nuo formulės, galite pašalinti @ simbolį ir formulės elgesys nesikeis.

Santrauka

  • Dinaminiai masyvai žymiai palengvins tam tikrų formulių rašymą.
  • Dabar naudodami formules galite lengvai filtruoti atitinkamus duomenis, rūšiuoti ir išskirti unikalias vertes.
  • „Dinaminio masyvo“ formulės gali būti susietos grandinėmis (įdėtos), kad būtų galima filtruoti ir rūšiuoti.
  • Formulės, kurios grąžina daugiau nei vieną reikšmę, automatiškai išsilieja.
  • Norint įvesti masyvo formulę, nebūtina naudoti „Ctrl“+„Shift“+„Enter“.
  • Dinaminio masyvo formulės pasiekiamos tik „Excel 365“.
Autorius Dave'as Brunsas


^