Majedwali kuhusiana katika Excel: maelekezo ya kina.

Wakati wa kufanya kazi fulani katika Excel wakati mwingine wanapaswa kukabiliana na meza kadhaa ambazo zinahusiana pia. Hiyo ni, data kutoka kwenye meza moja imeimarishwa kwa wengine na maadili katika meza zote zinazohusiana zinarejeshwa wakati zinabadilishwa.

Majedwali kuhusiana ni rahisi sana kutumia kushughulikia kiasi kikubwa cha habari. Weka habari zote katika meza moja, badala, ikiwa sio sawa, sio rahisi sana. Ni vigumu kufanya kazi na vitu vile na kuwatafuta. Tatizo maalum ni tu iliyoundwa ili kuondoa meza zinazohusiana, habari kati ya ambayo inasambazwa, lakini wakati huo huo inaunganishwa. Majedwali kuhusiana inaweza kuwa sio tu ndani ya karatasi moja au kitabu kimoja, lakini pia kuwa iko katika vitabu tofauti (faili). Chaguo mbili za mwisho katika mazoezi mara nyingi hutumiwa, kwa kuwa lengo la teknolojia hii ni tu kupata mbali na mkusanyiko wa data, na nodding yao kwenye ukurasa mmoja hauwezi kutatua kimsingi. Hebu tujifunze jinsi ya kuunda na jinsi ya kufanya kazi na aina hiyo ya usimamizi wa data.

Kujenga meza kuhusiana.

Kwanza kabisa, hebu tuzingalie swali, kwa njia ambayo inawezekana kuunda uhusiano kati ya meza mbalimbali.

Njia ya 1: Mfumo wa moja kwa moja wa kisheria

Njia rahisi ya kumfunga data ni matumizi ya formula ambayo kuna marejeo ya meza nyingine. Inaitwa kumfunga moja kwa moja. Njia hii ni intuitive, tangu wakati inafunga karibu kama kuunda marejeo ya data katika safu moja ya meza.

Hebu tuone jinsi mfano unaweza kuunda mawasiliano kwa kumfunga moja kwa moja. Tuna meza mbili kwenye karatasi mbili. Kwenye meza hiyo, mshahara huhesabiwa kwa kutumia formula kwa kuzidisha viwango vya mfanyakazi kwa mgawo mmoja.

Meza ya mshahara katika Microsoft Excel.

Kwenye karatasi ya pili kuna meza ya meza ambayo kuna orodha ya wafanyakazi wenye mshahara wao. Orodha ya wafanyakazi katika kesi zote mbili zinawasilishwa kwa amri moja.

Jedwali na viwango vya wafanyakazi katika Microsoft Excel.

Ni muhimu kufanya data hiyo kwenye bets kutoka karatasi ya pili inaimarisha ndani ya seli zinazofanana za kwanza.

Kwenye karatasi ya kwanza, tunatoa kiini cha kwanza cha safu ya "Bet". Sisi kuweka ndani yake ishara "=". Kisha, bofya studio ya "Karatasi ya 2", ambayo imewekwa kwenye sehemu ya kushoto ya interface ya Excel juu ya bar ya hali.

Nenda kwenye karatasi ya pili katika Microsoft Excel.

Kuna harakati katika eneo la pili la hati. Bofya kwenye kiini cha kwanza kwenye safu ya "Bet". Kisha bofya kitufe cha Ingiza kwenye kibodi ili uingie data kwenye kiini ambacho ishara ya "sawa" ilikuwa imewekwa hapo awali.

Kumfunga na kiini cha meza ya pili katika Microsoft Excel

Kisha kuna mpito wa moja kwa moja kwenye karatasi ya kwanza. Kama tunaweza kuona, thamani ya mfanyakazi wa kwanza kutoka meza ya pili ni vunjwa ndani ya kiini kinachofanana. Kwa kufunga mshale kwenye kiini kilicho na bet, tunaona kwamba formula ya kawaida hutumiwa kuonyesha data kwenye skrini. Lakini mbele ya kuratibu za kiini, kutoka ambapo data ni pato, kuna maneno "orodha2!", Ambayo inaonyesha jina la eneo la hati ambapo iko. Fomu ya jumla katika kesi yetu inaonekana kama hii:
= Orodha2! B2.

Seli mbili za meza mbili zinaunganishwa na Microsoft Excel

Sasa unahitaji kuhamisha data kuhusu viwango vya wafanyakazi wengine wote wa biashara. Bila shaka, hii inaweza kufanyika kwa namna ile ile tuliyotimiza kazi kwa mfanyakazi wa kwanza, lakini kwa kuzingatia kwamba orodha zote za wafanyakazi ziko katika utaratibu huo, kazi inaweza kuwa rahisi sana na kuhamishwa na uamuzi wake. Hii inaweza kufanyika kwa tu kwa kuiga fomu kwa upeo chini. Kutokana na ukweli kwamba marejeo ya Excel ni jamaa, wakati wa kuiga maadili yao, mabadiliko ya maadili yanabadilishwa kwamba tunahitaji. Utaratibu wa nakala yenyewe unaweza kufanywa kwa kutumia alama ya kujaza.
Kwa hiyo, tunaweka cursor kwenye eneo la chini la haki ya kipengele na formula. Baada ya hapo, mshale lazima ageuke kwenye alama ya kujaza kwa njia ya msalaba mweusi. Tunafanya kamba ya kifungo cha kushoto cha mouse na kuvuta mshale kwa idadi ya safu.

Takwimu zote kutoka kwenye safu sawa kwenye karatasi 2 zilipigwa kwenye meza kwenye karatasi 1. Wakati data inabadilika kwenye karatasi ya 2, itabadilika moja kwa moja juu ya kwanza.

Nguzo zote za safu ya pili ya meza huhamishiwa kwanza katika Microsoft Excel

Njia ya 2: Kutumia ukatili wa index ya waendeshaji - Tafuta

Lakini nini cha kufanya kama orodha ya wafanyakazi katika safu ya meza haipo katika utaratibu huo? Katika kesi hiyo, kama ilivyoelezwa mapema, moja ya chaguzi ni kufunga uhusiano kati ya kila seli hizo ambazo zinapaswa kuhusishwa kwa manually. Lakini ni mzuri isipokuwa kwa meza ndogo. Kwa safu kubwa, chaguo hili litakuwa bora kuchukua muda mwingi juu ya utekelezaji, na wakati mbaya - katika mazoezi itakuwa kwa ujumla kuwa isiyo ya kweli. Lakini tatizo hili linaweza kutatuliwa kwa kutumia kikundi cha index ya waendeshaji - tafuta. Hebu tuone jinsi inaweza kufanywa kwa kuimarisha data katika meza ambayo mazungumzo yalikuwa katika njia ya awali.

Tunasisitiza kipengele cha kwanza cha safu ya "Bet". Nenda kwenye Mchawi wa Kazi kwa kubonyeza icon ya "Ingiza kazi".

Ingiza kipengele katika Microsoft Excel.

Katika mchawi wa kazi katika kikundi "Viungo na Mipangilio" tunapata na kutenga jina "index".

Mpito kwa index ya kazi ya Argometheus katika Microsoft Excel

Operesheni hii ina aina mbili: fomu ya kufanya kazi na safu na kumbukumbu. Kwa upande wetu, chaguo la kwanza linahitajika, kwa hiyo katika dirisha la uteuzi wa fomu inayofuata, chagua na bonyeza kitufe cha "OK".

Chagua kazi ya kazi ya kazi katika Microsoft Excel.

Orodha ya hoja ya operator kuanza kukimbia. Kazi ya kazi maalum ni pato la thamani iliyo katika aina iliyochaguliwa kwenye mstari na nambari maalum. Index ya Fomu ya Mfumo Mkuu kama:
= Index (safu; namba_name; [namba_stolbits])

"Array" ni hoja iliyo na aina mbalimbali ambazo tutaondoa habari kwa idadi ya mstari uliowekwa.
"Nambari ya mstari" ni hoja ambayo ni idadi ya mstari huu. Ni muhimu kujua kwamba nambari ya mstari inapaswa kuwa maalum si kuhusiana na hati nzima, lakini tu kuhusiana na safu iliyotengwa.
"Idadi ya safu" ni hoja ambayo ni ya hiari. Ili kutatua mahsusi ya kazi yetu, hatuwezi kuitumia, na kwa hiyo sio lazima kuelezea tofauti.
Tunaweka cursor katika uwanja wa "Array". Baada ya hayo, nenda kwenye karatasi ya 2 na, ukizingatia kifungo cha kushoto cha mouse, chagua yaliyomo yote ya safu ya "kiwango".

Aina ya hoja katika ripoti ya kazi ya dirisha katika Microsoft Excel

Baada ya kuratibu zinaonyeshwa kwenye dirisha la operator, tunaweka cursor katika uwanja wa "Nambari ya Row". Tutaondoa hoja hii kwa kutumia operator wa utafutaji. Kwa hiyo, bofya kwenye pembetatu iliyoko upande wa kushoto wa kamba ya kazi. Orodha ya waendeshaji wapya hufungua. Ikiwa unapata jina "kampuni ya utafutaji" kati yao, unaweza kubofya. Kwa upande mwingine, bofya kwenye hatua ya hivi karibuni ya orodha - "kazi nyingine ...".

Kuhamisha dirisha kazi index katika Microsoft Excel.

Dirisha la Wilaya ya Wilaya ya Dirisha huanza. Nenda kwao katika kundi moja "Viungo na Mipangilio". Wakati huu katika orodha, chagua kipengee cha "Tafuta Kampuni". Fanya bonyeza kwenye kitufe cha "OK".

Mpito kwa dirisha la Arguamet la kazi ya utafutaji katika Microsoft Excel

Utekelezaji wa hoja za hoja za operator wa utafutaji hufanyika. Kazi maalum imeundwa kwa pato namba ya thamani katika safu maalum kwa jina lake. Ni shukrani kwa kipengele hiki ambacho tunahesabu idadi ya kamba ya thamani maalum kwa kazi ya kazi. Syntax ya bodi ya utafutaji imewasilishwa:
= Bodi ya utafutaji (search_name; kuangalia__nassive; [aina_station])
"Inahitajika" ni hoja iliyo na jina au anwani ya kiini cha aina ya tatu ambayo iko. Ni nafasi ya jina hili katika aina mbalimbali na inapaswa kuhesabiwa. Kwa upande wetu, jukumu la hoja ya kwanza litaelezewa kwenye seli kwenye karatasi ya 1, ambayo wafanyakazi wako iko.
"Orodha ya orodha" ni hoja, ambayo ni kumbukumbu ya safu, ambayo hufanya utafutaji wa thamani maalum ili kuamua nafasi yake. Tutakuwa na jukumu hili kutekeleza anwani ya safu ya "Jina" kwenye karatasi 2.
"Aina ya kulinganisha" - hoja ambayo ni ya hiari, lakini, tofauti na operator uliopita, hoja hii ya hiari itahitajika. Inaonyesha jinsi ya kufanana na operator ni thamani ya taka na safu. Majadiliano haya yanaweza kuwa na maadili ya tatu: -1; 0; 1. Kwa vitu visivyo na matatizo, chagua chaguo "0". Chaguo hili linafaa kwa kesi yetu.
Kwa hiyo, endelea kujaza mashamba ya dirisha ya hoja. Tunaweka cursor katika uwanja "Thamani ya Fomu", bofya safu ya kwanza ya "Jina" kwenye karatasi 1.

Majadiliano ni thamani ya taka katika dirisha la hoja la kazi ya utafutaji katika Microsoft Excel

Baada ya kuratibu zinaonyeshwa, weka mshale katika uwanja wa "orodha kubwa" na uende kwenye lebo ya "Karatasi ya 2", ambayo iko chini ya dirisha la Excel juu ya bar ya hali. Clement kifungo cha kushoto cha mouse na kuonyesha cursor seli zote za safu ya "Jina".

Majadiliano yanatazamwa na safu katika dirisha la hoja la kazi ya utafutaji katika Microsoft Excel

Baada ya kuratibu zao zinaonyeshwa kwenye uwanja wa "Orodha ya Kubwa", nenda kwenye uwanja wa "Ramani ya Ramani" na kuweka namba "0" kutoka kwenye kibodi. Baada ya hapo, sisi tena kurudi kwenye shamba "kuangalia kupitia safu". Ukweli ni kwamba tutafanya kuiga fomu, kama tulivyofanya katika njia ya awali. Kutakuwa na mabadiliko ya anwani, lakini hapa kuratibu za safu inayoonekana tunahitaji kupata. Haipaswi kuhama. Tunasisitiza kuratibu na mshale na bonyeza kitufe cha F4. Kama unaweza kuona, ishara ya dola ilionekana kabla ya kuratibu, ambayo ina maana kwamba kumbukumbu kutoka kwa jamaa ikageuka kuwa kabisa. Kisha bofya kitufe cha "OK".

Kazi ya Window ya Arguamet kwa Bodi ya Utafutaji katika Microsoft Excel

Matokeo yanaonyeshwa kwenye kiini cha kwanza cha safu ya "Bet". Lakini kabla ya kuiga, tunahitaji kurekebisha eneo lingine, yaani index ya kazi ya kwanza ya hoja. Ili kufanya hivyo, chagua kipengele cha safu, ambacho kina formula, na uende kwenye kamba ya formula. Weka hoja ya kwanza ya index ya operator (B2: B7) na bofya kifungo cha F4. Kama unaweza kuona, ishara ya dola ilionekana karibu na kuratibu zilizochaguliwa. Bofya kitufe cha Ingiza. Kwa ujumla, formula ilichukua fomu ifuatayo:
= Index (karatasi2! $ B $ 2: $ B $ 7; Bodi ya Utafutaji (SHEET1! A4; Orodha2! $ $ 2: $ $ 7; 0))

Badilisha viungo kwa kabisa katika Microsoft Excel.

Sasa unaweza nakala kwa kutumia alama ya kujaza. Tunaiita kwa njia ile ile ambayo tumezungumzia hapo awali, na kunyoosha hadi mwisho wa aina ya tabular.

Kama unaweza kuona, licha ya ukweli kwamba amri ya masharti katika meza mbili kuhusiana haina sanjari, hata hivyo, maadili yote yameimarishwa kulingana na majina ya wafanyakazi. Hii ilifikia shukrani kwa matumizi ya mchanganyiko wa utafutaji wa ripoti ya waendeshaji.

Maadili yanahusishwa kutokana na mchanganyiko wa kazi za kumalizika kwa index katika Microsoft Excel

Kiasi cha mshahara kwa ajili ya biashara ni recalculated katika Microsoft Excel

Njia ya 4: Ingiza maalum

Mipangilio ya meza katika Excel inaweza pia kutumia kuingizwa maalum.

Chagua maadili unayotaka "kaza" kwenye meza nyingine. Kwa upande wetu, hii ni safu ya "Bet" kwenye karatasi 2. Bonyeza kwenye kipande cha kujitolea na kifungo cha haki cha mouse. Katika orodha inayofungua, chagua kipengee cha "Nakala". Mchanganyiko mbadala ni mchanganyiko muhimu wa CTRL + C. Baada ya hapo, tunahamia kwenye karatasi 1.

Kuhamia eneo la kitabu unachohitaji, kugawa seli ambazo maadili yatahitaji kufungwa. Kwa upande wetu, hii ni safu ya "jitihada". Bofya kwenye kipande cha kujitolea na kifungo cha haki cha mouse. Katika orodha ya mazingira katika "vigezo vya kuingiza" toolbar, bonyeza kitufe cha "Insert Communication".

Pia kuna mbadala. Yeye, kwa njia, ndiye pekee kwa matoleo ya zamani ya Excel. Katika orodha ya mazingira, tunaleta mshale kwenye kipengee cha "kuingiza maalum". Katika orodha ya ziada inayofungua, chagua nafasi na jina moja.

Mpito kwa kuingiza maalum katika Microsoft Excel.

Baada ya hapo, dirisha la kuingiza maalum linafungua. Bofya kwenye kitufe cha "Insert Communication" kwenye kona ya chini ya kushoto ya seli.

Dirisha maalum ya kuingiza katika Microsoft Excel.

Chaguo chochote unachochagua, maadili kutoka kwenye safu moja ya meza itaingizwa ndani ya mwingine. Wakati wa kubadilisha data katika chanzo, pia watabadilika moja kwa moja katika aina iliyoingizwa.

Maadili yanaingizwa kwa kutumia kuingizwa maalum katika Microsoft Excel

Somo: Kuingiza maalum katika Excel.

Njia ya 5: Mawasiliano kati ya meza katika vitabu kadhaa

Kwa kuongeza, unaweza kuandaa kiungo kati ya maeneo ya meza katika vitabu tofauti. Hii inatumia chombo maalum cha kuingiza. Vitendo vitakuwa sawa kabisa na wale tuliofikiria katika njia ya awali, isipokuwa kwamba urambazaji wakati wa formula hautakuwa kati ya maeneo ya kitabu kimoja, lakini kati ya faili. Kwa kawaida, vitabu vyote vinavyohusiana vinapaswa kufunguliwa.

Chagua aina ya data ili kuhamishiwa kwenye kitabu kingine. Bonyeza kifungo cha kulia cha panya na chagua nafasi ya "Nakala" kwenye orodha iliyofunguliwa.

Kuiga data kutoka kwenye kitabu cha Microsoft Excel.

Kisha tunahamia kwenye kitabu ambacho data hii inapaswa kuingizwa. Chagua aina ya taka. Bonyeza kifungo cha kulia cha mouse. Katika orodha ya muktadha katika kikundi cha "Weka Mipangilio", chagua kipengee cha "Ingiza Mawasiliano".

Ingiza mawasiliano kutoka kwenye kitabu kingine katika Microsoft Excel.

Baada ya hapo, maadili yataingizwa. Wakati wa kubadilisha data katika kitabu cha chanzo, safu ya meza kutoka kwenye kitabu cha kazi itaimarisha moja kwa moja. Na sio lazima kabisa kuhakikisha kwamba vitabu vyote vimefunguliwa. Inatosha kufungua kitabu cha kazi moja tu, na itaangalia data moja kwa moja kutoka kwa hati iliyofungwa ikiwa kuna mabadiliko ya awali ndani yake.

Mawasiliano kutoka kwa kitabu kingine ni kuingizwa katika Microsoft Excel.

Lakini ni lazima ieleweke kwamba katika kesi hii inset itazalishwa kwa namna ya safu isiyobadilika. Wakati wa kujaribu kubadilisha kiini chochote na data iliyoingizwa, ujumbe utawajulisha habari kuhusu kutokuwa na uwezo wa kufanya hivyo.

Ujumbe wa habari katika Microsoft Excel.

Mabadiliko katika safu hiyo yanayohusiana na kitabu kingine inaweza kuvunja tu uhusiano.

Mapumziko ya kichwa kati ya meza.

Wakati mwingine inahitajika kuvunja uhusiano kati ya meza. Sababu ya hii inaweza kuwa ya hapo juu wakati unataka kubadilisha safu iliyoingizwa kutoka kwenye kitabu kingine na kusita kwa mtumiaji ili data katika meza hiyo inasasishwa moja kwa moja kutoka kwa nyingine.

Njia ya 1: Mapumziko ya mawasiliano kati ya vitabu.

Ili kuvunja uhusiano kati ya vitabu katika seli zote, kwa kufanya kazi moja kwa moja. Katika kesi hiyo, data katika seli zitabaki, lakini tayari kuwa static si maadili ya updated ambayo hayategemei nyaraka nyingine.

Katika kitabu ambacho maadili kutoka kwa mafaili mengine yameimarishwa, nenda kwenye kichupo cha data. Bofya kwenye icon ya "Viungo vya Badilisha", ambayo iko kwenye mkanda katika chombo cha "Connection". Ikumbukwe kwamba ikiwa kitabu cha sasa hana uhusiano na mafaili mengine, basi kifungo hiki haifanyi kazi.

Mpito kwa mabadiliko katika viungo katika Microsoft Excel.

Dirisha ya mabadiliko ya kiungo imezinduliwa. Chagua kutoka kwenye orodha ya vitabu vinavyohusiana (ikiwa kuna kadhaa yao) faili ambayo tunataka kuvunja uunganisho. Bofya kwenye kifungo "Kuvunja uunganisho".

Dirisha la uhusiano katika Microsoft Excel.

Dirisha la habari linafungua, ambalo hutoa onyo juu ya matokeo ya vitendo zaidi. Ikiwa una hakika kwamba utafanya, bofya kitufe cha "Kuvunja Mawasiliano".

Baada ya hapo, marejeo yote ya faili maalum katika hati ya sasa yatabadilishwa na maadili ya static.

Viungo vinabadilishwa na maadili ya static katika Microsoft Excel.

Njia ya 2: Kuingiza Values.

Lakini njia ya hapo juu inafaa tu ikiwa unahitaji kuvunja kabisa viungo vyote kati ya vitabu viwili. Nini kama unahitaji kuondokana na meza zinazohusiana ndani ya faili moja? Unaweza kufanya hivyo kwa kuiga data, na kisha kuingiza mahali sawa na maadili. Kwa njia, njia hii inaweza kupasuka kati ya data ya mtu binafsi ya vitabu mbalimbali bila kuvunja uhusiano wa kawaida kati ya faili. Hebu tuone jinsi njia hii inavyofanya kazi.

Tunasisitiza aina ambayo tunataka kufuta mawasiliano na meza nyingine. Bofya kwenye kifungo cha kulia cha mouse. Katika orodha ya wazi, chagua kipengee cha "Nakala". Badala ya vitendo maalum, unaweza kupiga mchanganyiko mbadala wa funguo za moto Ctrl + C.

Kisha, bila kuondoa uteuzi kutoka kwa kipande kimoja, bonyeza tena na kifungo cha haki cha mouse. Wakati huu katika orodha ya hatua, bofya icon ya "Thamani", ambayo imewekwa kwenye kikundi cha vigezo vya kuingiza.

Ingiza kama maadili katika Microsoft Excel.

Baada ya hapo, marejeo yote katika aina ya kujitolea yatabadilishwa na maadili ya static.

Maadili yanaingizwa katika Microsoft Excel.

Kama unaweza kuona, Excel ina njia na zana za kuhusisha meza kadhaa kati yao. Wakati huo huo, data ya tabular inaweza kuwa kwenye karatasi nyingine na hata katika vitabu tofauti. Ikiwa ni lazima, uhusiano huu unaweza kuvunjika kwa urahisi.