Mga kaugnay na talahanayan sa Excel: Mga detalyadong tagubilin

Mga kaugnay na talahanayan sa Microsoft Excel.

Kapag gumaganap ang ilang mga gawain sa Excel minsan ay may upang harapin ang ilang mga talahanayan na may kaugnayan din sa bawat isa. Iyon ay, ang data mula sa isang talahanayan ay masikip sa iba at ang mga halaga sa lahat ng kaugnay na mga talahanayan ay muling kinalkula kapag sila ay nabago.

Ang mga kaugnay na talahanayan ay maginhawa upang magamit upang mahawakan ang isang malaking halaga ng impormasyon. Ilagay ang lahat ng impormasyon sa isang talahanayan, bukod pa, kung hindi ito homogenous, hindi masyadong maginhawa. Mahirap magtrabaho kasama ang mga bagay na ito at hanapin ang mga ito. Ang tinukoy na problema ay dinisenyo lamang upang maalis ang mga kaugnay na mga talahanayan, ang impormasyon sa pagitan ng kung saan ay ipinamamahagi, ngunit sa parehong oras ay interconnected. Ang mga kaugnay na talahanayan ay maaaring hindi lamang sa loob ng isang sheet o isang libro, kundi pati na rin na matatagpuan sa magkakahiwalay na mga libro (mga file). Ang huling dalawang pagpipilian sa pagsasanay ay kadalasang ginagamit, dahil ang layunin ng teknolohiyang ito ay upang makalayo mula sa akumulasyon ng data, at ang kanilang nodding sa isang pahina ay hindi malulutas ang isang panimula. Alamin kung paano lumikha at kung paano magtrabaho kasama ang isang uri ng pamamahala ng data.

Paglikha ng mga kaugnay na talahanayan

Una sa lahat, tumuon tayo sa tanong, kung saan posible na lumikha ng isang koneksyon sa pagitan ng iba't ibang mga talahanayan.

Paraan 1: Direct Binding Tables Formula.

Ang pinakamadaling paraan upang magbigkis ng data ay ang paggamit ng mga formula kung saan may mga sanggunian sa iba pang mga talahanayan. Ito ay tinatawag na direktang pagbubuklod. Ang pamamaraan na ito ay madaling maunawaan, dahil kapag binigyan ito ng halos tulad ng paglikha ng mga sanggunian sa data sa isang array ng talahanayan.

Tingnan natin kung paano ang halimbawa na maaari mong bumuo ng komunikasyon sa pamamagitan ng direktang pagbubuklod. Mayroon kaming dalawang talahanayan sa dalawang sheet. Sa parehong mesa, ang suweldo ay kinakalkula gamit ang formula sa pamamagitan ng pagpaparami ng mga rate ng empleyado para sa isang solong koepisyent.

Sa ikalawang sheet mayroong isang hanay ng table kung saan mayroong isang listahan ng mga empleyado sa kanilang suweldo. Ang listahan ng mga empleyado sa parehong mga kaso ay iniharap sa isang order.

Talaan na may mga rate ng empleyado sa Microsoft Excel.

Kinakailangan na gawin ang data na iyon sa mga taya mula sa ikalawang sheet na higpitan sa mga kaukulang cell ng una.

Sa unang sheet, inilalaan namin ang unang cell ng haligi ng "Taya". Inilalagay namin ito sa pag-sign "=". Susunod, mag-click sa label na "Sheet 2", na inilalagay sa kaliwang bahagi ng excel interface sa status bar.

Pumunta sa ikalawang sheet sa Microsoft Excel.

May kilusan sa ikalawang lugar ng dokumento. Mag-click sa unang cell sa haligi ng "Taya". Pagkatapos ay mag-click sa pindutan ng ENTER sa keyboard upang ipasok ang data sa cell kung saan ang "katumbas" na sign ay na-install na dati.

Umiiral na may isang cell ng pangalawang talahanayan sa Microsoft Excel

Pagkatapos ay mayroong awtomatikong paglipat sa unang sheet. Tulad ng makikita natin, ang halaga ng unang empleyado mula sa ikalawang talahanayan ay nakuha sa kaukulang cell. Sa pamamagitan ng pag-install ng cursor sa isang cell na naglalaman ng taya, nakikita namin na ang karaniwang formula ay ginagamit upang ipakita ang data sa screen. Ngunit sa harap ng mga coordinate ng cell, mula sa kung saan ang data ay output, mayroong isang expression "List2!", Na nagpapahiwatig ng pangalan ng lugar ng dokumento kung saan sila matatagpuan. Ang pangkalahatang formula sa aming kaso ay ganito:
= List2! B2.

Ang dalawang selula ng dalawang talahanayan ay konektado sa Microsoft Excel

Ngayon ay kailangan mong ilipat ang data tungkol sa mga rate ng lahat ng iba pang mga empleyado ng enterprise. Siyempre, ito ay maaaring gawin sa parehong paraan na natupad namin ang gawain para sa unang empleyado, ngunit isinasaalang-alang na ang parehong mga listahan ng empleyado ay matatagpuan sa parehong pagkakasunud-sunod, ang gawain ay maaaring makabuluhang pinasimple at pinabilis ng desisyon nito. Magagawa ito sa pamamagitan lamang ng pagkopya ng formula sa hanay sa ibaba. Dahil sa ang katunayan na ang mga sanggunian sa Excel ay kamag-anak, kapag kinopya ang kanilang mga halaga, ang paglilipat ng mga halaga ay inilipat na kailangan namin. Ang pamamaraan ng kopya mismo ay maaaring gawin gamit ang isang marker ng pagpuno.
Kaya, inilalagay namin ang cursor sa ibabang kanang lugar ng elemento sa formula. Pagkatapos nito, dapat i-convert ang cursor sa marker ng pagpuno sa anyo ng isang itim na krus. Ginagawa namin ang salansan ng kaliwang pindutan ng mouse at hilahin ang cursor sa bilang ng haligi.

Ang lahat ng data mula sa isang katulad na haligi sa isang sheet 2 ay nakuha sa isang table sa isang sheet 1. Kapag nagbabago ang data sa isang sheet 2, awtomatiko silang magbabago sa una.

Ang lahat ng mga haligi ng ikalawang haligi ng talahanayan ay inililipat sa unang sa Microsoft Excel

Paraan 2: Gamit ang blundering ng mga index ng operator - paghahanap

Ngunit ano ang gagawin kung ang listahan ng mga empleyado sa mga array ng table ay hindi matatagpuan sa parehong pagkakasunud-sunod? Sa kasong ito, tulad ng nakasaad na mas maaga, ang isa sa mga opsyon ay i-install ang relasyon sa pagitan ng bawat isa sa mga selula na dapat manu-mano. Ngunit angkop ito maliban sa maliliit na mga talahanayan. Para sa napakalaking saklaw, ang pagpipiliang ito ay pinakamahusay na tumagal ng maraming oras sa pagpapatupad, at sa pinakamasama - sa pagsasanay sa pangkalahatan ay hindi makatotohanan. Ngunit ang problemang ito ay maaaring malutas gamit ang isang grupo ng mga index ng operator - paghahanap. Tingnan natin kung paano ito magagawa sa pamamagitan ng pag-tilling ng data sa mga talahanayan tungkol sa kung saan ang pag-uusap ay nasa nakaraang paraan.

I-highlight namin ang unang elemento ng haligi ng "Taya". Pumunta sa wizard ng function sa pamamagitan ng pag-click sa icon na "Ipasok ang function".

Magsingit ng isang tampok sa Microsoft Excel.

Sa wizard ng mga function sa grupo "mga link at arrays" nakita namin at inilalaan ang pangalan na "index".

Paglipat sa index ng function ng argometheus window sa Microsoft Excel

Ang operator na ito ay may dalawang anyo: isang form para sa pagtatrabaho sa arrays at reference. Sa aming kaso, ang unang pagpipilian ay kinakailangan, kaya sa susunod na window ng pagpili ng form na bubukas, piliin ito at mag-click sa pindutan ng "OK".

Piliin ang function function index sa Microsoft Excel.

Ang index ng mga argumento ng operator ay nagsisimula tumakbo. Ang gawain ng tinukoy na function ay ang output ng halaga na matatagpuan sa napiling hanay sa linya kasama ang tinukoy na numero. Pangkalahatang Formula Operator Index tulad ng:
= Index (array; number_name; [number_stolbits])

Ang "array" ay isang argumento na naglalaman ng hanay ng hanay mula sa kung saan ay kunin namin ang impormasyon sa pamamagitan ng bilang ng tinukoy na hilera.
Ang "Row Number" ay isang argumento na ang bilang ng linyang ito. Mahalagang malaman na ang numero ng linya ay dapat na tinukoy na hindi kamag-anak sa buong dokumento, ngunit kamag-anak lamang sa inilalaan na array.
Ang "bilang ng haligi" ay isang argumento na opsyonal. Upang malutas ang partikular sa aming gawain, hindi namin gagamitin ito, at samakatuwid hindi kinakailangan upang ilarawan ito nang hiwalay.
Inilalagay namin ang cursor sa "array" na patlang. Pagkatapos nito, pumunta sa sheet 2 at, hawak ang kaliwang pindutan ng mouse, piliin ang buong nilalaman ng haligi ng "Rate".

Array ng argumento sa index ng function ng window ng argumento sa Microsoft Excel

Matapos ang mga coordinate ay ipinapakita sa window ng operator, inilalagay namin ang cursor sa patlang ng "Row Number". Inalis namin ang argument na ito gamit ang operator ng paghahanap. Samakatuwid, mag-click sa isang tatsulok na matatagpuan sa kaliwa ng function string. Ang isang listahan ng mga bagong ginagamit na operator ay bubukas. Kung nakita mo ang pangalan na "kumpanya ng paghahanap" sa kanila, maaari kang mag-click dito. Sa kabaligtaran kaso, mag-click sa pinakabagong punto ng listahan - "Iba pang mga function ...".

Argument window function index sa Microsoft Excel.

Nagsisimula ang standard window wizard window. Pumunta sa ito sa parehong grupo "mga link at arrays". Sa oras na ito sa listahan, piliin ang item na "Kumpanya sa Paghahanap". Magsagawa ng isang pag-click sa pindutan ng "OK".

Paglipat sa Arguamet window ng function ng paghahanap sa Microsoft Excel

Ang pag-activate ng mga argumento ng mga argumento ng operator ng paghahanap ay ginaganap. Ang tinukoy na function ay dinisenyo upang i-output ang numero ng halaga sa isang partikular na array sa pamamagitan ng pangalan nito. Salamat sa tampok na ito na kinakalkula namin ang bilang ng isang string ng isang tiyak na halaga para sa function function. Ang syntax ng search board ay iniharap:
= Search Board (Search_name; pagtingin__Nassive; [type_station])
Ang "nais" ay isang argumento na naglalaman ng pangalan o tirahan ng cell ng hanay ng third-party na kung saan ito matatagpuan. Ito ang posisyon ng pangalan na ito sa hanay ng target at dapat kalkulahin. Sa aming kaso, ang papel na ginagampanan ng unang argumento ay isinangguni sa mga selula sa isang sheet 1, kung saan matatagpuan ang mga empleyado.
Ang "Listful Array" ay isang argument, na isang reference sa isang array, na gumaganap ng paghahanap para sa tinukoy na halaga upang matukoy ang posisyon nito. Magkakaroon kami ng papel na ito upang maisagawa ang address ng haligi ng "Pangalan" sa isang sheet 2.
"Uri ng paghahambing" - isang argumento na opsyonal, ngunit, hindi katulad ng nakaraang operator, ang opsyonal na argumento na ito ay kinakailangan. Ipinapahiwatig nito kung paano tumugma sa operator ang nais na halaga sa isang array. Ang argument na ito ay maaaring magkaroon ng isa sa tatlong halaga: -1; 0; 1. Para sa disordered arrays, piliin ang opsyon na "0". Ang pagpipiliang ito ay angkop para sa aming kaso.
Kaya, magpatuloy sa pagpuno ng mga patlang ng window ng argumento. Inilalagay namin ang cursor sa field na "fogular value", mag-click sa haligi ng unang cell na "pangalan" sa isang sheet 1.

Ang argumento ay ang nais na halaga sa window ng argumento ng function ng paghahanap sa Microsoft Excel

Matapos ang mga coordinate ay ipinapakita, itakda ang cursor sa "listahan ng napakalaking" patlang at pumunta sa label na "sheet 2", na matatagpuan sa ilalim ng window ng Excel sa itaas ng status bar. Clement sa kaliwang pindutan ng mouse at i-highlight ang cursor lahat ng mga cell ng haligi ng "pangalan".

Ang argumento ay tiningnan ng isang array sa argument window ng function ng paghahanap sa Microsoft Excel

Matapos ang kanilang mga coordinate ay ipinapakita sa "listahan ng napakalaking" patlang, pumunta sa patlang ng "Mapping Uri" at itakda ang numero na "0" mula sa keyboard. Pagkatapos nito, muli kaming bumalik sa field na "naghahanap sa array". Ang katotohanan ay na gagawin namin ang pagkopya ng formula, tulad ng ginawa namin sa nakaraang paraan. Magkakaroon ng paglilipat ng mga address, ngunit narito ang mga coordinate ng array na tiningnan na kailangan nating i-secure. Hindi siya dapat maglipat. I-highlight namin ang mga coordinate sa cursor at mag-click sa F4 function key. Tulad ng makikita mo, lumitaw ang dollar sign bago ang mga coordinate, na nangangahulugan na ang reference mula sa kamag-anak ay naging ganap. Pagkatapos ay mag-click sa pindutang "OK".

Arguamet window function para sa search board sa Microsoft Excel

Ang resulta ay ipinapakita sa unang cell ng haligi ng "Taya". Ngunit bago kopyahin, kailangan naming ayusin ang isa pang lugar, lalo na ang unang index ng function ng argumento. Upang gawin ito, piliin ang elemento ng hanay, na naglalaman ng isang formula, at lumipat sa formula string. Ilaan ang unang argumento ng index ng operator (B2: B7) at mag-click sa pindutan ng F4. Tulad ng makikita mo, lumitaw ang dollar sign malapit sa napiling mga coordinate. I-click ang Enter key. Sa pangkalahatan, kinuha ng formula ang sumusunod na form:
= Index (sheet2! $ B $ 2: $ b $ 7; search board (sheet1! A4; list2! $ A $ 2: $ a $ 7; 0))

I-convert ang mga link sa absolute sa Microsoft Excel.

Ngayon ay maaari mong kopyahin ang paggamit ng isang marker ng pagpuno. Tinatawag namin ito sa parehong paraan na sinalita namin nang mas maaga, at umaabot sa dulo ng hanay ng tabular.

Tulad ng makikita mo, sa kabila ng katotohanan na ang pagkakasunud-sunod ng mga string sa dalawang kaugnay na mga talahanayan ay hindi nag-tutugma, gayunpaman, ang lahat ng mga halaga ay tightened ayon sa mga pangalan ng mga manggagawa. Ito ay nakamit salamat sa paggamit ng kumbinasyon ng paghahanap ng index ng operator.

Ang mga halaga ay nauugnay dahil sa isang kumbinasyon ng mga pag-andar ng pag-expire ng index sa Microsoft Excel

Ang halaga ng suweldo para sa enterprise ay muling kinalkula sa Microsoft Excel

Paraan 4: Espesyal na insert

Ang mga arrays ng Tie Table sa Excel ay maaari ring gumamit ng isang espesyal na pagpapasok.

Piliin ang mga halaga na nais mong "higpitan" sa isa pang talahanayan. Sa aming kaso, ito ang hanay ng haligi ng "taya" sa isang sheet 2. Mag-click sa dedikadong fragment gamit ang kanang pindutan ng mouse. Sa listahan na bubukas, piliin ang item na "Kopyahin". Ang isang alternatibong kumbinasyon ay ang Control + C key na kumbinasyon. Pagkatapos nito, lumipat kami sa sheet 1.

Ang paglipat sa lugar ng aklat na kailangan mo, maglaan ng mga selula kung saan ang mga halaga ay kailangang masikip. Sa aming kaso, ito ang haligi ng "bid". Mag-click sa nakalaang fragment gamit ang kanang pindutan ng mouse. Sa menu ng konteksto sa toolbar ng "Ipasok ang Mga Parameter", mag-click sa icon na "Ipasok ang komunikasyon".

Mayroon ding alternatibo. Siya, sa pamamagitan ng paraan, ay ang isa lamang para sa mas lumang mga bersyon ng Excel. Sa menu ng konteksto, dalhin namin ang cursor sa "espesyal na insert" item. Sa karagdagang menu na bubukas, piliin ang posisyon na may parehong pangalan.

Paglipat sa isang espesyal na insert sa Microsoft Excel.

Pagkatapos nito, bubukas ang isang espesyal na insert window. Mag-click sa pindutan ng "Ipasok ang komunikasyon" sa ibabang kaliwang sulok ng cell.

Espesyal na insert window sa Microsoft Excel.

Anuman ang pagpipilian na pinili mo, ang mga halaga mula sa isang table array ay ipapasok sa isa pa. Kapag binabago ang data sa pinagmulan, awtomatiko din silang magbabago sa hanay na ipinasok.

Ang mga halaga ay ipinasok gamit ang isang espesyal na insertion sa Microsoft Excel

Aralin: Espesyal na Ipasok sa Excel.

Paraan 5: Komunikasyon sa pagitan ng mga talahanayan sa maraming mga libro

Bilang karagdagan, maaari mong ayusin ang isang link sa pagitan ng mga lugar ng talahanayan sa iba't ibang mga libro. Gumagamit ito ng isang espesyal na tool sa pagpasok. Ang mga pagkilos ay ganap na katulad ng mga itinuturing namin sa nakaraang paraan, maliban na ang pag-navigate sa panahon ng mga formula ay hindi magkakaroon sa pagitan ng mga lugar ng isang libro, ngunit sa pagitan ng mga file. Naturally, ang lahat ng kaugnay na mga libro ay dapat mabuksan.

Piliin ang hanay ng data upang mailipat sa isa pang libro. Mag-click dito sa tamang pindutan ng mouse at piliin ang posisyon na "Kopyahin" sa binuksan na menu.

Kinokopya ang data mula sa aklat sa Microsoft Excel.

Pagkatapos ay lumipat kami sa aklat kung saan dapat ipasok ang data na ito. Piliin ang nais na saklaw. I-click ang kanang pindutan ng mouse. Sa menu ng konteksto sa grupo ng "Ipasok ang Mga Setting", piliin ang item na "Ipasok ang komunikasyon".

Ipasok ang komunikasyon mula sa isa pang libro sa Microsoft Excel.

Pagkatapos nito, ang mga halaga ay ipasok. Kapag nagbabago ang data sa source book, ang isang table array mula sa nagtatrabaho libro ay awtomatikong i-tighten ang mga ito. At ito ay hindi kinakailangan upang matiyak na ang parehong mga libro ay bukas. Ito ay sapat na upang buksan ang isang tanging workbook, at awtomatiko itong i-tailage ang data mula sa saradong kaugnay na dokumento kung may mga nakaraang pagbabago dito.

Ang komunikasyon mula sa isa pang libro ay ipinasok sa Microsoft Excel.

Ngunit dapat pansinin na sa kasong ito ang inset ay gagawin sa anyo ng hindi nagbabagong array. Kapag sinusubukan mong baguhin ang anumang cell na may ipinasok na data, ang isang mensahe ay populated na nagpapaalam tungkol sa kawalan ng kakayahan na gawin ito.

Impormasyon ng mensahe sa Microsoft Excel.

Ang mga pagbabago sa naturang array na nauugnay sa isa pang libro ay maaari lamang masira ang koneksyon.

Pamagat ng mga break sa pagitan ng mga talahanayan

Minsan ito ay kinakailangan upang masira ang koneksyon sa pagitan ng mga talahanayan. Ang dahilan para sa mga ito ay maaaring inilarawan sa itaas kapag nais mong baguhin ang isang array na ipinasok mula sa isa pang libro at simpleng pag-aatubili ng gumagamit upang ang data sa parehong talahanayan ay awtomatikong na-update mula sa iba.

Paraan 1: Ang komunikasyon ay pumipigil sa pagitan ng mga aklat

Upang masira ang koneksyon sa pagitan ng mga aklat sa lahat ng mga cell, sa pamamagitan ng pagsasagawa ng isang operasyon. Sa kasong ito, ang data sa mga selula ay mananatili, ngunit sila ay magiging static na hindi na-update na mga halaga na hindi nakasalalay sa iba pang mga dokumento.

Sa aklat kung saan ang mga halaga mula sa iba pang mga file ay tightened, pumunta sa tab na Data. Mag-click sa icon na "Baguhin ang Mga Link", na matatagpuan sa tape sa "koneksyon" na toolbar. Dapat pansinin na kung ang kasalukuyang libro ay hindi naglalaman ng mga koneksyon sa iba pang mga file, ang button na ito ay hindi aktibo.

Paglipat sa mga pagbabago sa mga link sa Microsoft Excel.

Inilunsad ang window ng pagbabago ng link. Pumili mula sa listahan ng mga kaugnay na aklat (kung may ilan sa mga ito) ang file na gusto naming masira ang koneksyon. Mag-click sa pindutan na "basagin ang koneksyon".

Mga koneksyon sa window sa Microsoft Excel.

Ang isang window ng impormasyon ay bubukas, na nagbibigay ng babala tungkol sa mga kahihinatnan ng mga karagdagang pagkilos. Kung sigurado ka na gagawin mo, mag-click sa pindutan ng "Break Communication".

Pagkatapos nito, ang lahat ng mga sanggunian sa tinukoy na file sa kasalukuyang dokumento ay papalitan ng mga static na halaga.

Ang mga link ay pinalitan ng mga static na halaga sa Microsoft Excel.

Paraan 2: Pagpasok ng mga halaga

Ngunit ang pamamaraan sa itaas ay angkop lamang kung kailangan mong ganap na masira ang lahat ng mga link sa pagitan ng dalawang aklat. Paano kung kailangan mong idiskonekta ang nauugnay na mga talahanayan sa loob ng parehong file? Maaari mong gawin ito sa pamamagitan ng pagkopya ng data, at pagkatapos ay ipasok ang parehong lugar bilang mga halaga. Sa pamamagitan ng paraan, ang pamamaraan na ito ay maaaring ruptied sa pagitan ng mga indibidwal na data saklaw ng iba't ibang mga libro nang walang paglabag sa isang karaniwang relasyon sa pagitan ng mga file. Tingnan natin kung paano gumagana ang pamamaraang ito sa pagsasanay.

Itinatampok namin ang hanay kung saan nais naming tanggalin ang komunikasyon sa isa pang talahanayan. Mag-click dito kanang pindutan ng mouse. Sa bukas na menu, piliin ang item na "Kopyahin". Sa halip na tinukoy na mga pagkilos, maaari kang mag-dial ng alternatibong kumbinasyon ng mga hot key na Ctrl + C.

Susunod, nang hindi inaalis ang pagpili mula sa parehong fragment, muling i-click ito gamit ang kanang pindutan ng mouse. Sa oras na ito sa listahan ng pagkilos, mag-click sa icon na "Halaga", na nai-post sa insert na grupo ng mga parameter.

Magsingit bilang mga halaga sa Microsoft Excel.

Pagkatapos nito, ang lahat ng mga sanggunian sa dedikadong hanay ay papalitan ng mga static na halaga.

Ang mga halaga ay ipinasok sa Microsoft Excel.

Tulad ng makikita mo, ang Excel ay may mga paraan at mga tool upang iugnay ang ilang mga talahanayan sa kanilang mga sarili. Kasabay nito, ang tabular na data ay maaaring nasa iba pang mga sheet at kahit sa iba't ibang mga libro. Kung kinakailangan, ang koneksyon na ito ay madaling masira.