Paano magtakda ng isang hanay ng mga halaga sa Excel

Kapag gumagamit ng Microsoft Excel, madalas na hindi alam ng gumagamit nang maaga kung gaano karaming impormasyon ang nasa talahanayan bilang isang resulta. Samakatuwid, hindi namin naiintindihan sa lahat ng sitwasyon kung anong saklaw ang dapat saklawin. Pagkatapos ng lahat, ang isang set ng mga cell ay isang variable na konsepto. Upang maalis ang problemang ito, kinakailangan na gawing awtomatiko ang pagbuo ng hanay upang ito ay batay lamang sa dami ng data na ipinasok ng user.

Awtomatikong binabago ang mga hanay ng cell sa Excel

Ang bentahe ng mga awtomatikong hanay sa Excel ay ginagawa nilang mas madali ang paggamit ng mga formula. Bilang karagdagan, ginagawa nilang posible na makabuluhang gawing simple ang pagsusuri ng kumplikadong data na naglalaman ng isang malaking bilang ng mga formula, na kinabibilangan ng maraming mga pag-andar. Maaari mong bigyan ng pangalan ang hanay na ito, at pagkatapos ay awtomatiko itong ia-update depende sa kung anong data ang nilalaman nito.

Paano gumawa ng awtomatikong pagbabago ng saklaw sa Excel

Ipagpalagay na ikaw ay isang mamumuhunan na kailangang mamuhunan sa ilang bagay. Bilang resulta, gusto naming makakuha ng impormasyon tungkol sa kung magkano ang maaari mong kikitain sa kabuuan sa buong panahon na gagana ang pera para sa proyektong ito. Gayunpaman, upang makuha ang impormasyong ito, kailangan nating regular na subaybayan kung magkano ang kabuuang kita na dinadala sa atin ng bagay na ito. Gawin ang parehong ulat tulad ng sa screenshot na ito.

Sa unang sulyap, ang solusyon ay halata: kailangan mo lamang isama ang buong hanay. Kung lalabas ang mga entry dito, ang halaga ay ia-update nang nakapag-iisa. Ngunit ang pamamaraang ito ay may maraming mga kawalan:

1. Kung malulutas ang problema sa ganitong paraan, hindi posibleng gamitin ang mga cell na kasama sa column B para sa iba pang layunin.
2. Ang ganitong talahanayan ay kumonsumo ng maraming RAM, na gagawing imposibleng gamitin ang dokumento sa mga mahihinang computer.

Samakatuwid, kinakailangan upang malutas ang problemang ito sa pamamagitan ng mga dynamic na pangalan. Upang gawin ang mga ito, dapat mong gawin ang sumusunod na pagkakasunud-sunod ng mga aksyon:

1. Pumunta sa tab na "Mga Formula", na matatagpuan sa pangunahing menu. Magkakaroon ng isang seksyon na "Mga tinukoy na pangalan", kung saan mayroong isang pindutan na "Magtalaga ng isang pangalan", kung saan kailangan nating mag-click.
2. Susunod, lilitaw ang isang dialog box kung saan kailangan mong punan ang mga patlang tulad ng ipinapakita sa screenshot. Mahalagang tandaan na kailangan nating ilapat ang function = DISPLACEMENT kasama ng function CHECKupang lumikha ng saklaw ng awtomatikong pag-update. 
3. Pagkatapos nito kailangan nating gamitin ang function SUM, na gumagamit ng aming dynamic range bilang argumento. 

Pagkatapos makumpleto ang mga hakbang na ito, makikita natin kung paano ina-update ang saklaw ng mga cell na kabilang sa hanay ng "kita" habang nagdaragdag kami ng mga bagong elemento doon.

OFFSET function sa Excel

Tingnan natin ang mga function na naitala natin sa field na “range” kanina. Gamit ang function TANGGALIN matutukoy natin ang dami ng saklaw na ibinigay kung gaano karaming mga cell sa column B ang napunan. Ang mga argumento ng function ay ang mga sumusunod:

1. Simulan ang cell. Gamit ang argumentong ito, maaaring ipahiwatig ng user kung aling cell sa hanay ang ituturing na kaliwang tuktok. Mag-uulat ito pababa at sa kanan.
2. Ang hanay ay na-offset ng mga hilera. Gamit ang hanay na ito, itinakda namin ang bilang ng mga cell kung saan dapat mangyari ang isang offset mula sa kaliwang itaas na cell ng hanay. Maaari mong gamitin hindi lamang ang mga positibong halaga, ngunit ang zero at minus. Sa kasong ito, ang pag-aalis ay maaaring hindi mangyari sa lahat, o ito ay isasagawa sa kabaligtaran na direksyon.
3. Ang hanay ay na-offset ng mga column. Ang parameter na ito ay katulad ng nauna, pinapayagan ka lamang nitong itakda ang antas ng pahalang na paglilipat ng saklaw. Dito maaari mo ring gamitin ang parehong zero at negatibong mga halaga.
4. Ang dami ng range sa taas. Sa katunayan, ang pamagat ng argumentong ito ay nilinaw sa atin kung ano ang ibig sabihin nito. Ito ang bilang ng mga cell kung saan dapat tumaas ang hanay.
5. Ang halaga ng saklaw sa lapad. Ang argumento ay katulad ng nauna, tanging ito ay may kinalaman sa mga hanay.

Hindi mo kailangang tukuyin ang huling dalawang argumento kung hindi mo kailangan. Sa kasong ito, ang halaga ng hanay ay magiging isang cell lamang. Halimbawa, kung tinukoy mo ang formula =OFFSET(A1;0;0), ang formula na ito ay magre-refer sa parehong cell tulad ng isa sa unang argumento. Kung ang vertical offset ay nakatakda sa 2 unit, sa kasong ito ang cell ay tumutukoy sa cell A3. Ngayon ilarawan natin nang detalyado kung ano ang ibig sabihin ng function CHECK.

COUNT function sa Excel

Gamit ang function CHECK tinutukoy namin kung gaano karaming mga cell sa column B ang napunan namin sa kabuuan. Iyon ay, gamit ang dalawang function, tinutukoy namin kung gaano karaming mga cell sa hanay ang napunan, at batay sa impormasyong natanggap, tinutukoy ang laki ng hanay. Samakatuwid, ang huling formula ay ang mga sumusunod: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Tingnan natin kung paano maunawaan nang tama ang prinsipyo ng formula na ito. Itinuturo ng unang argumento kung saan magsisimula ang aming dynamic range. Sa aming kaso, ito ay cell B2. Ang mga karagdagang parameter ay may zero na coordinate. Iminumungkahi nito na hindi namin kailangan ng mga offset na nauugnay sa kaliwang itaas na cell. Ang pinupunan lang namin ay ang patayong laki ng hanay, na ginamit namin ang function bilang CHECK, na tumutukoy sa bilang ng mga cell na naglalaman ng ilang data. Ang pang-apat na parameter na pinunan namin ay ang yunit. Kaya ipinapakita namin na ang kabuuang lapad ng hanay ay dapat na isang haligi.

Kaya, gamit ang function CHECK magagamit ng user ang memorya nang mahusay hangga't maaari sa pamamagitan ng paglo-load lamang ng mga cell na naglalaman ng ilang value. Alinsunod dito, walang mga karagdagang error sa gawaing nauugnay sa mahinang pagganap ng computer kung saan gagana ang spreadsheet.

Alinsunod dito, upang matukoy ang laki ng hanay depende sa bilang ng mga haligi, kailangan mong magsagawa ng katulad na pagkakasunud-sunod ng mga aksyon, sa kasong ito kailangan mong tukuyin ang yunit sa ikatlong parameter, at ang formula sa ikaapat CHECK.

Nakikita namin na sa tulong ng mga formula ng Excel, hindi mo lamang ma-automate ang mga kalkulasyon sa matematika. Ito ay isang patak lamang sa karagatan, ngunit sa katunayan pinapayagan ka nitong i-automate ang halos anumang operasyon na naiisip.

Mga Dynamic na Chart sa Excel

Kaya, sa huling hakbang, nakagawa kami ng isang dynamic na hanay, ang laki nito ay ganap na nakasalalay sa kung gaano karaming mga cell ang nilalaman nito. Ngayon, batay sa data na ito, maaari kang lumikha ng mga dynamic na chart na awtomatikong magbabago sa sandaling gumawa ang user ng anumang mga pagbabago o magdagdag ng karagdagang column o row. Ang pagkakasunud-sunod ng mga aksyon sa kasong ito ay ang mga sumusunod:

1. Pinipili namin ang aming saklaw, pagkatapos nito ay naglalagay kami ng isang tsart ng uri ng "Histogram na may pagpapangkat". Mahahanap mo ang item na ito sa seksyong "Ipasok" sa seksyong "Mga Chart - Histogram".
2. Gumagawa kami ng kaliwang pag-click ng mouse sa isang random na column ng histogram, pagkatapos nito ay ipapakita ang function =SERIES() sa function line. Sa screenshot makikita mo ang detalyadong formula. 
3. Pagkatapos nito, kailangang gumawa ng ilang pagbabago sa formula. Kailangan mong palitan ang hanay pagkatapos ng "Sheet1!" sa pangalan ng hanay. Magreresulta ito sa sumusunod na function: =ROW(Sheet1!$B$1;;Sheet1!kita;1)
4. Ngayon ay nananatili pa ring magdagdag ng bagong tala sa ulat upang suriin kung awtomatikong na-update ang chart o hindi.

Tingnan natin ang aming diagram.

Ibuod natin kung paano natin ito ginawa. Sa nakaraang hakbang, gumawa kami ng dynamic na hanay, ang laki nito ay depende sa kung gaano karaming elemento ang nilalaman nito. Upang gawin ito, gumamit kami ng kumbinasyon ng mga function CHECK и TANGGALIN. Ginawa naming pinangalanan ang hanay na ito, at pagkatapos ay ginamit namin ang reference sa pangalang ito bilang hanay ng aming histogram. Aling partikular na hanay ang pipiliin bilang pinagmumulan ng data sa unang yugto ay hindi napakahalaga. Ang pangunahing bagay ay palitan ito ng pangalan ng hanay sa ibang pagkakataon. Sa ganitong paraan makakatipid ka ng maraming RAM.

Mga pinangalanang hanay at mga gamit nito

Pag-usapan natin ngayon nang mas detalyado ang tungkol sa kung paano gumawa ng wastong mga pinangalanang hanay at gamitin ang mga ito upang maisagawa ang mga gawaing itinakda para sa user ng Excel.

Bilang default, gumagamit kami ng mga regular na cell address upang makatipid ng oras. Ito ay kapaki-pakinabang kapag kailangan mong magsulat ng isang hanay ng isa o higit pang beses. Kung kailangan itong gamitin sa lahat ng oras o kailangan itong maging adaptive, dapat gamitin ang mga pinangalanang hanay. Pinapadali nila ang paggawa ng mga formula, at hindi magiging napakahirap para sa user na suriin ang mga kumplikadong formula na may kasamang malaking bilang ng mga function. Ilarawan natin ang ilan sa mga hakbang na kasangkot sa paggawa ng mga dynamic na hanay.

Nagsisimula ang lahat sa pagbibigay ng pangalan sa cell. Upang gawin ito, piliin lamang ito, at pagkatapos ay isulat ang pangalan na kailangan namin sa patlang ng pangalan nito. Mahalaga na ito ay madaling matandaan. Mayroong ilang mga paghihigpit na dapat isaalang-alang kapag pinangalanan:

1. Ang maximum na haba ay 255 character. Ito ay sapat na upang magtalaga ng isang pangalan na nais ng iyong puso.
2. Ang pangalan ay hindi dapat maglaman ng mga puwang. Samakatuwid, kung naglalaman ito ng ilang salita, posibleng paghiwalayin ang mga ito gamit ang underscore na character.

Kung sa ibang pagkakataon sa ibang mga sheet ng file na ito kailangan nating ipakita ang halagang ito o ilapat ito upang magsagawa ng karagdagang mga kalkulasyon, hindi na kailangang lumipat sa pinakaunang sheet. Maaari mo lamang isulat ang pangalan ng range cell na ito.

Ang susunod na hakbang ay upang lumikha ng isang pinangalanang hanay. Ang pamamaraan ay karaniwang pareho. Una kailangan mong pumili ng isang saklaw, at pagkatapos ay tukuyin ang pangalan nito. Pagkatapos nito, maaaring gamitin ang pangalang ito sa lahat ng iba pang operasyon ng data sa Excel. Halimbawa, ang mga pinangalanang hanay ay kadalasang ginagamit upang tukuyin ang kabuuan ng mga halaga.

Bilang karagdagan, posibleng gumawa ng pinangalanang hanay gamit ang tab na Mga Formula gamit ang tool na Itakda ang Pangalan. Pagkatapos naming piliin ito, lalabas ang isang window kung saan kailangan naming pumili ng pangalan para sa aming hanay, pati na rin tukuyin ang lugar kung saan ito ay manu-manong pahahabain. Maaari mo ring tukuyin kung saan gagana ang saklaw na ito: sa loob ng isang sheet o sa buong aklat.

Kung ang isang hanay ng pangalan ay nagawa na, pagkatapos ay upang magamit ito, mayroong isang espesyal na serbisyo na tinatawag na isang name manager. Pinapayagan nito hindi lamang ang pag-edit o pagdaragdag ng mga bagong pangalan, ngunit din ang pagtanggal ng mga ito kung hindi na kailangan.

Dapat tandaan na kapag gumagamit ng mga pinangalanang hanay sa mga formula, pagkatapos ay pagkatapos itong tanggalin, ang mga formula ay hindi awtomatikong ma-overwrite ng mga tamang halaga. Samakatuwid, maaaring mangyari ang mga pagkakamali. Samakatuwid, bago magtanggal ng pinangalanang hanay, kailangan mong tiyakin na hindi ito ginagamit sa alinman sa mga formula.

Ang isa pang paraan upang lumikha ng isang pinangalanang hanay ay upang makuha ito mula sa isang talahanayan. Upang gawin ito, mayroong isang espesyal na tool na tinatawag na "Gumawa mula sa Pinili". Tulad ng naiintindihan namin, upang magamit ito, kailangan mo munang piliin ang hanay na aming ie-edit, at pagkatapos ay itakda ang lugar kung saan mayroon kaming mga heading. Bilang resulta, batay sa data na ito, awtomatikong ipoproseso ng Excel ang lahat ng data, at awtomatikong itatalaga ang mga pamagat.

Kung ang pamagat ay naglalaman ng ilang salita, awtomatikong ihihiwalay ng Excel ang mga ito gamit ang isang underscore.

Kaya, naisip namin kung paano gumawa ng mga dynamic na pinangalanang hanay at kung paano ka nila pinapayagang i-automate ang trabaho na may malaking halaga ng data. Tulad ng nakikita mo, sapat na ang paggamit ng ilang mga pag-andar at ang mga tool ng programa na binuo sa pag-andar. Walang kumplikado sa lahat, bagaman ito ay maaaring mukhang gayon sa isang baguhan sa unang tingin.