Etalaj dinamik nan Excel

Ki sa ki etalaj dinamik

Nan mwa septanm 2018, Microsoft te pibliye yon aktyalizasyon ki ajoute yon zouti konplètman nouvo nan Microsoft Excel: Etalaj dinamik ak 7 nouvo fonksyon pou travay avèk yo. Bagay sa yo, san yo pa egzajerasyon, radikalman chanje tout teknik nòmal nan travay ak fòmil ak fonksyon ak enkyetid, literalman, chak itilizatè.

Konsidere yon egzanp senp pou eksplike sans lan.

Sipoze nou gen yon tablo senp ak done sou vil-mwa. Kisa ki pral rive si nou chwazi nenpòt selil vid sou bò dwat fèy la epi antre nan li yon fòmil ki lye pa yon sèl selil, men imedyatman nan yon seri?

Nan tout vèsyon anvan yo nan Excel, apre yo fin klike sou Antre nan nou ta jwenn sa ki nan yon sèl premye selil B2. Ki jan lòt bagay?

Oke, oswa li ta posib yo vlope seri sa a nan yon kalite fonksyon agrégation tankou = SOM (B2: C4) epi jwenn yon total total pou li.

Si nou bezwen operasyon pi konplèks pase yon sòm primitif, tankou ekstrè valè inik oswa Top 3, Lè sa a, nou ta dwe antre nan fòmil nou an kòm yon fòmil etalaj lè l sèvi avèk yon chemen kout klavye. Ctrl+Ka glise+Antre nan.

Koulye a, tout bagay diferan.

Koulye a, apre w fin antre nan fòmil sa a, nou ka tou senpleman klike sou Antre nan – epi jwenn kòm yon rezilta imedyatman tout valè uXNUMXbuXNUMXb ke nou refere yo:

Sa a se pa majik, men nouvo etalaj dinamik ke Microsoft Excel genyen kounye a. Byenvini nan nouvo mond lan 🙂

Karakteristik nan travay ak etalaj dinamik

Teknikman, tout etalaj dinamik nou an estoke nan premye selil G4 la, ranpli kantite selil ki nesesè sou bò dwat la ak desann ak done li yo. Si ou chwazi nenpòt lòt selil nan etalaj la, Lè sa a, lyen ki nan ba fòmil la pral inaktif, ki montre ke nou nan youn nan selil "pitit" yo:

Yon tantativ pou efase youn oswa plis selil "pitit" pa pral mennen nan anyen - Excel pral imedyatman rekalkile epi ranpli yo.

An menm tan an, nou ka san danje refere a selil "pitit" sa yo nan lòt fòmil:

Si ou kopye premye selil la nan yon etalaj (pa egzanp, soti nan G4 rive nan F8), Lè sa a, tout etalaj la (referans li yo) ap deplase nan menm direksyon an ak fòmil regilye yo:

Si nou bezwen deplase etalaj la, Lè sa a, li pral ase pou avanse pou pi (ak sourit la oswa yon konbinezon de Ctrl+X, Ctrl+V), ankò, sèlman premye selil prensipal G4 la - apre li, li pral transfere nan yon nouvo kote ak tout etalaj nou an pral elaji ankò.

Si ou bezwen refere yon lòt kote sou fèy la nan etalaj dinamik kreye a, Lè sa a, ou ka itilize karaktè espesyal # ("liv") apre adrès selil dirijan li yo:

Pou egzanp, kounye a ou ka fasilman fè yon lis dropdown nan yon selil ki refere a etalaj la dinamik kreye:

Erè etalaj dinamik

Men, sa k ap pase si pa gen ase espas pou elaji etalaj la, oswa si gen selil ki deja okipe pa lòt done nan chemen li yo? Rankontre yon nouvo kalite erè fondamantalman nan Excel - #Transfè! (#SPILL!):

Kòm toujou, si nou klike sou icon ki gen yon dyaman jòn ak yon mak esklamasyon, nou pral jwenn yon eksplikasyon pi detaye sou sous pwoblèm nan epi nou ka byen vit jwenn selil entèfere:

Erè menm jan an ap fèt si etalaj la ale nan fèy la oswa frape yon selil fizyone. Si ou retire obstak la, Lè sa a, tout bagay pral imedyatman dwe korije sou vole a.

Etalaj dinamik ak tab entelijan

Si etalaj la dinamik lonje dwèt sou yon tab "entelijan" ki te kreye pa yon chemen kout klavye Ctrl+T oswa pa Kay - Fòma kòm yon tab (Kay — Fòma kòm Tablo), Lè sa a, li pral tou eritye bon jan kalite prensipal li yo - oto-gwosè.

Lè w ajoute nouvo done anba oswa adwat, tab entelijan ak ranje dinamik pral otomatikman detire tou:

Sepandan, gen yon sèl limit: nou pa ka itilize yon referans ranje dinamik nan forumulas andedan yon tab entelijan:

Etalaj dinamik ak lòt karakteristik Excel

Oke, ou di. Tout bagay sa a se enteresan ak komik. Pa bezwen, tankou anvan, manyèlman detire fòmil la ak yon referans a premye selil la nan seri orijinal la desann ak sou bò dwat la ak tout sa. E sa se tout?

Pa byen.

Etalaj dinamik yo pa jis yon lòt zouti nan Excel. Koulye a, yo entegre nan kè a (oswa sèvo) nan Microsoft Excel - motè kalkil li yo. Sa vle di ke lòt fòmil Excel ak fonksyon abitye pou nou kounye a tou sipòte travay ak etalaj dinamik. Ann pran yon gade nan kèk egzanp pou ba ou yon lide sou pwofondè chanjman ki te fèt yo.

tradwir

Pou transpoze yon seri (chanje ranje ak kolòn) Microsoft Excel te toujou gen yon fonksyon entegre TRANSP (Transpoze). Sepandan, pou itilize li, ou dwe premye chwazi kòrèkteman seri a pou rezilta yo (pa egzanp, si opinyon an te yon seri 5 × 3, Lè sa a, ou dwe te chwazi 3 × 5), Lè sa a, antre nan fonksyon an epi peze la. konbinezon Ctrl+Ka glise+Antre nan, paske li te kapab sèlman travay nan mòd fòmil etalaj.

Koulye a, ou ka jis chwazi yon sèl selil, antre menm fòmil la nan li epi klike sou nòmal la Antre nan - etalaj dinamik pral fè tout bagay pou kont li:

Tablo miltiplikasyon

Sa a se egzanp mwen te itilize pou bay lè yo te mande m 'vizyalize benefis ki genyen nan fòmil etalaj nan Excel. Koulye a, pou kalkile tout tablo Pitagò a, li ase pou kanpe nan premye selil B2 a, antre nan yon fòmil ki miltipliye de etalaj (vètikal ak orizontal seri nimewo 1..10) epi tou senpleman klike sou Antre nan:

Kole ak konvèsyon ka

Etalaj pa ka sèlman miltipliye, men tou, kole ansanm ak operatè a estanda & (ampersand). Sipoze nou bezwen ekstrè non an ak siyati soti nan de kolòn ak korije ka a sote nan done orijinal yo. Nou fè sa ak yon fòmil kout ki fòme etalaj la tout antye, ak Lè sa a, nou aplike fonksyon an nan li PROPNACH (BON)pou netwaye rejis la:

Konklizyon Top 3

Sipoze nou gen yon pakèt nimewo ki soti nan ki nou vle tire premye twa rezilta yo, ranje yo nan lòd desandan. Koulye a, sa a se fè pa yon fòmil epi, ankò, san okenn Ctrl+Ka glise+Antre nan tankou anvan:

Si ou vle rezilta yo dwe plase pa nan yon kolòn, men nan yon ranje, Lè sa a, li ase ranplase kolon yo (separasyon liy) nan fòmil sa a ak yon pwen (separasyon eleman nan yon liy). Nan vèsyon an angle nan Excel, separateur sa yo se pwen-vir ak vigil, respektivman.

VLOOKUP ekstrè plizyè kolòn nan yon fwa

Fonksyon VPR (VCHACHUP) Koulye a, ou ka rale valè pa soti nan yon sèl, men nan plizyè kolòn nan yon fwa - jis presize nimewo yo (nan nenpòt ki lòd) kòm yon etalaj nan twazyèm agiman an nan fonksyon an:

Fonksyon OFFSET ki retounen yon etalaj dinamik

Youn nan fonksyon ki pi enteresan ak itil (apre VLOOKUP) pou analiz done se fonksyon an JETE (OFFSET), ki mwen te konsakre nan yon sèl fwa yon chapit antye nan liv mwen an ak yon atik isit la. Difikilte pou konprann ak metrize fonksyon sa a te toujou ke li te retounen yon etalaj (ranje) nan done kòm yon rezilta, men nou pa t 'kapab wè li, paske Excel toujou pa t' konnen ki jan yo travay ak etalaj soti nan bwat la.

Koulye a, pwoblèm sa a se nan tan lontan an. Gade ki jan kounye a, lè l sèvi avèk yon fòmil sèl ak yon etalaj dinamik retounen pa OFFSET, ou ka ekstrè tout ranje pou yon pwodwi yo soti nan nenpòt tablo klase:

Ann pran yon gade nan agiman li yo:

  • A1 - kòmanse selil (pwen referans)
  • ПОИСКПОЗ(F2;A2:A30;0) – kalkil chanjman an soti nan selil la kòmanse desann – nan premye chou a jwenn.
  • 0 - chanjman nan "fenèt la" sou bò dwat la relatif nan selil la kòmanse
  • СЧЁТЕСЛИ(A2:A30;F2) - kalkil wotè "fenèt la" retounen - kantite liy kote gen chou.
  • 4 - gwosè "fenèt la" orizontal, sa vle di pwodiksyon 4 kolòn

Nouvo Fonksyon pou etalaj dinamik

Anplis sipòte mekanis etalaj dinamik nan fonksyon fin vye granmoun, plizyè fonksyon konplètman nouvo yo te ajoute nan Microsoft Excel, file espesyalman pou travay ak etalaj dinamik. An patikilye, sa yo se:

  • GRADE (SORT) – klase seri a opinyon epi li pwodui yon etalaj dinamik sou pwodiksyon an
  • SORTPO (SORTBY) - ka klase yon seri pa valè soti nan yon lòt
  • FILTRE (FILTRE) – rekipere ranje nan seri sous ki satisfè kondisyon yo espesifye
  • UNIK (INIK) - Ekstrè valè inik nan yon seri oswa retire kopi
  • SLMASIVE (RANDARRAY) – jenere yon etalaj de nimewo o aza nan yon gwosè bay yo
  • APRÈNÈT (SEKANS) — fòme yon etalaj apati yon sekans nimewo ak yon etap bay yo

Plis sou yo - yon ti kras pita. Yo vo yon atik separe (e pa youn) pou etid reflechi 🙂

Konklizyon

Si ou te li tout sa ki ekri pi wo a, Lè sa a, mwen panse ke ou deja reyalize echèl chanjman ki te fèt yo. Se konsa, anpil bagay nan Excel kapab kounye a fè pi fasil, pi fasil ak plis lojik. Mwen dwe admèt ke mwen se yon ti kras choke nan konbyen atik yo pral kounye a dwe korije isit la, sou sit sa a ak nan liv mwen yo, men mwen pare pou fè sa ak yon kè lejè.

Rezime rezilta yo, plis etalaj dinamik, ou ka ekri bagay sa yo:

  • Ou ka bliye sou konbinezon an Ctrl+Ka glise+Antre nan. Excel kounye a pa wè okenn diferans ant "fòmil regilye" ak "fòmil etalaj" epi trete yo menm jan an.
  • Konsènan fonksyon an SUMPRODUCT (SÒM PWODWI), ki te deja itilize pou antre nan fòmil etalaj san yo pa Ctrl+Ka glise+Antre nan ou ka bliye tou - kounye a li fasil ase SOM и Antre nan.
  • Tablo entelijan ak fonksyon abitye (SUM, IF, VLOOKUP, SUMIFS, elatriye) kounye a tou sipòte totalman oswa pasyèlman etalaj dinamik.
  • Gen konpatibilite bak: si ou louvri yon liv travay ak etalaj dinamik nan yon ansyen vèsyon Excel, yo pral tounen fòmil etalaj (nan aparèy òtopedik boukle) epi yo kontinye travay nan "style ansyen".

Jwenn kèk nimewo minus:

  • Ou pa ka efase ranje endividyèl, kolòn oswa selil ki soti nan yon etalaj dinamik, sa vle di li ap viv kòm yon sèl antite.
  • Ou pa ka klase yon etalaj dinamik nan fason nòmal la Done - Klasye (Done — Triye). Gen kounye a yon fonksyon espesyal pou sa a. GRADE (SORT).
  • Yon seri dinamik pa ka tounen yon tab entelijan (men ou ka fè yon seri dinamik ki baze sou yon tab entelijan).

Natirèlman, sa a se pa fen a, e mwen sèten Microsoft pral kontinye amelyore mekanis sa a nan lavni.

Ki kote mwen ka telechaje?

Epi finalman, kesyon prensipal la 🙂

Microsoft te premye anonse epi te montre yon aperçu nan etalaj dinamik nan Excel tounen nan mwa septanm nan 2018 nan yon konferans. Dife. Nan kèk mwa kap vini yo, te gen yon tès apwofondi ak kouri nan nouvo karakteristik, premye sou chat anplwaye nan Microsoft tèt li, ak Lè sa a, sou tèsteur volontè soti nan sèk la nan Office Insiders. Ane sa a, aktyalizasyon a ki ajoute etalaj dinamik yo te kòmanse piti piti woule nan abònen Office 365 regilye yo. Pou egzanp, mwen te resevwa li sèlman nan mwa Out ak abònman Office 365 Pro Plus mwen (Vize chak mwa).

Si Excel ou a poko gen etalaj dinamik, men ou reyèlman vle travay avèk yo, Lè sa a, gen opsyon sa yo:

  • Si ou gen yon abònman Office 365, ou ka senpleman tann jiskaske aktyalizasyon sa a rive ou. Konbyen rapidman sa rive depann de konbyen fwa yo bay mizajou nan biwo w la (yon fwa pa ane, yon fwa chak sis mwa, yon fwa pa mwa). Si ou gen yon PC antrepriz, ou ka mande administratè w la pou li mete mizajou pou telechaje pi souvan.
  • Ou ka rantre nan ran volontè tès Office Insiders sa yo - Lè sa a, ou pral premye moun ki resevwa tout nouvo karakteristik ak fonksyon yo (men gen yon chans pou ogmante buggy nan Excel, nan kou).
  • Si ou pa gen yon abònman, men yon vèsyon otonòm nan bwat Excel, Lè sa a, ou pral oblije rete tann jiskaske liberasyon an nan pwochen vèsyon an nan Office ak Excel nan 2022, omwen. Itilizatè yo nan vèsyon sa yo resevwa sèlman mizajou sekirite ak korije ensèk, ak tout nouvo "bon" yo kounye a ale sèlman nan abonnés Office 365. Tris men vre 🙂

Nan nenpòt ka, lè etalaj dinamik parèt nan Excel ou a - apre atik sa a, ou pral pare pou li 🙂

  • Ki sa ki fòmil etalaj ak kouman yo sèvi ak yo nan Excel
  • Somasyon fenèt (ranje) lè l sèvi avèk fonksyon OFFSET la
  • 3 Fason yo transpoze yon tab nan Excel

Kite yon Reply