Kiam vi plenumas iujn taskojn en Excel, foje devas trakti plurajn tabelojn, kiuj ankaŭ rilatas al si. Tio estas, la datumoj de unu tablo estas streĉitaj al aliaj kaj la valoroj en ĉiuj rilataj tabloj estas rekalkulitaj kiam ili ŝanĝiĝas.
Rilataj tabloj estas tre konvenaj por uzi grandan informon. Metu la tutan informon en unu tablo, krom, se ĝi ne estas homogena, ne tre konvena. Estas malfacile labori kun tiaj objektoj kaj serĉi ilin. La specifita problemo estas nur desegnita por forigi rilatajn tablojn, la informojn inter kiuj estas distribuitaj, sed samtempe estas interkonektitaj. Rilataj tabloj povas esti ne nur ene de unu folio aŭ unu libro, sed ankaŭ esti lokita en apartaj libroj (dosieroj). La lastaj du opcioj en praktiko estas plej ofte uzataj, ĉar la celo de ĉi tiu teknologio estas nur foriri de la akumulado de datumoj, kaj ilia kapjesado sur unu paĝo ne solvas fundamente. Ni lernu kiel krei kaj kiel labori kun tia speco de datuma administrado.
Krei rilatajn tablojn
Unue, ni koncentriĝu pri la demando, laŭ kiu eblas krei ligon inter diversaj tabloj.Metodo 1: Rekta Binda Tabelo Formulo
La plej facila maniero por ligi datumojn estas la uzo de formuloj en kiuj estas referencoj al aliaj tabeloj. I nomiĝas Rekta Ligo. Ĉi tiu metodo estas intuicia, ĉar kiam ĝi ligas ĝin preskaŭ same kiel krei referencojn al datumoj en unu tablo-tabelo.
Ni vidu kiel la ekzemplo vi povas formi komunikadon per rekta devigo. Ni havas du tabelojn sur du tukojn. Sur la sama tablo, la salajro estas kalkulita per la formulo, kiu multiplikas la dungitajn tarifojn por ununura koeficiento.
Sur la dua folio estas tablo gamo en kiu estas listo de dungitoj kun sia salajro. La listo de dungitoj en ambaŭ kazoj estas prezentita en unu ordo.
Estas necese fari, ke datumoj pri la vetas de la dua folio streĉas la respondajn ĉelojn de la unua.
- Sur la unua folio, ni asignas la unuan ĉelon de la "BET" kolumno. Ni enmetas ĝin la signon "=". Poste, alklaku la etikedon "Folio 2", kiu estas metita sur la maldekstran parton de la EXEL-interfaco super la statobreto.
- Estas movado en la dua areo de la dokumento. Alklaku la unuan ĉelon en la kolumno "Bet". Tiam alklaku la butonon Entajpu sur la klavaro por eniri la datumojn en la ĉelon, en kiu la "egala" signo estis antaŭe instalita.
- Tiam estas aŭtomata transiro al la unua folio. Kiel ni povas vidi, la valoro de la unua dungito de la dua tablo estas tirita en la respondan ĉelon. Instalante la kursoron sur ĉelo enhavanta veton, ni vidas, ke la kutima formulo estas uzata por montri datumojn sur la ekrano. Sed antaŭ la koordinatoj de la ĉelo, de kie la datumoj estas eligo, estas esprimo "listo2!", Kiu indikas la nomon de la dokumenta areo kie ili troviĝas. La ĝenerala formulo en nia kazo aspektas tiel:
= Listo2! B2
- Nun vi bezonas transdoni datumojn pri la tarifoj de ĉiuj aliaj dungitoj de la entrepreno. Kompreneble, ĉi tio povas esti farita laŭ la sama maniero, ke ni plenumis la taskon por la unua dungito, sed konsiderante, ke ambaŭ listoj de dungito situas en la sama ordo, la tasko povas esti signife simpligita kaj akcelita de ĝia decido. Ĉi tio povas esti farita simple per kopiado de la formulo al la suba teritorio. Pro la fakto, ke la referencoj al Excel estas relativaj, dum kopiado de iliaj valoroj, la valoroj ŝanĝiĝas, kiujn ni bezonas. La kopia procedo mem povas esti farita per plenigaĵo.
Do ni metas la kursoron al la suba dekstra areo de la elemento kun la formulo. Post tio, la kursoro devas konvertiĝi al la plenigaĵo en la formo de nigra kruco. Ni plenumas la krampon de la maldekstra musbutono kaj tiras la kursoron al la nombro de la kolumno.
- Ĉiuj datumoj de simila kolumno sur folio 2 estis tiritaj en tablon sur folio 1. Kiam la datumoj ŝanĝiĝas sur folio 2, ili aŭtomate ŝanĝos la unuan.
Metodo 2: Uzado de la indico de la operatoroj - Serĉo
Sed kion fari se la listo de dungitoj en tablaj aroj ne troviĝas en la sama ordo? En ĉi tiu kazo, kiel antaŭe dirite, unu el la ebloj estas instali la rilaton inter ĉiu el tiuj ĉeloj, kiuj devus esti asociitaj permane. Sed ĝi taŭgas escepte de malgrandaj tabloj. Por amasaj teritorioj, ĉi tiu opcio plejofte daŭros multan tempon pri la efektivigo, kaj plej malbone - en praktiko ĝi ĝenerale estos nereala. Sed ĉi tiu problemo povas esti solvita per fasko de indekso de operatoroj - serĉado. Ni vidu kiel ĝi povas esti farita per ĝis la datumoj en la tabloj pri kiuj la konversacio estis en la antaŭa metodo.
- Ni reliefigas la unuan elementon de la kolumno "vetas". Iru al la funkcioj sorĉisto alklakante la ikonon "Enmetu funkcion".
- En la sorĉisto de funkcioj en la grupo "Ligoj kaj Arrays" ni trovas kaj asignas la nomon "Indekso".
- Ĉi tiu operatoro havas du formojn: formo por labori kun aroj kaj referenco. En nia kazo, la unua opcio estas bezonata, do en la sekva formo selekta fenestro kiu malfermiĝas, elektu ĝin kaj alklaku la butonon "OK".
- La indekso de la operatoro de la operatoro ekkuras. La tasko de la specifita funkcio estas la eligo de la valoro situanta en la elektita gamo en la linio kun la specifita nombro. Enerala Formulo-Operatora Indekso Tiaj:
= Indekso (tabelo; nombro_nomo; [number_stolbits])
La "tabelo" estas argumento enhavanta la gamon de la teritorio de kiu ni eltiros informojn per la nombro de la specifita vico.
"Row-nombro" estas argumento, kiu estas la nombro de ĉi tiu linio. Gravas scii, ke la linio-nombro devas esti specifita ne relativa al la tuta dokumento, sed nur rilate al la asignita tabelo.
La "nombro de la kolumno" estas argumento, kiu estas nedeviga. Solvi la specife de nia tasko, ni ne uzos ĝin, kaj tial ne necesas priskribi ĝin aparte.
Ni metas la kursoron en la kampo "tabelo". Post tio, iru al la folio 2 kaj, tenante la maldekstran musbutonon, elektu la tutan enhavon de la "tarifo" kolumno.
- Post kiam la koordinatoj montriĝas en la fenestro de la operatoro, ni metas la kursoron en la kampo "vico". Ni retiros ĉi tiun argumenton uzante la serĉan operatoron. Sekve, alklaku triangulon, kiu situas maldekstre de la funkcia ŝnuro. Listo de nove uzataj operatoroj malfermiĝas. Se vi trovas la nomon "Serĉa Firmao" inter ili, vi povas alklaki ĝin. En la kontraŭa kazo, alklaku la lastan punkton de la listo - "Aliaj funkcioj ...".
- Standard Window Wizard-fenestro komenciĝas. Iru al ĝi en la sama grupo "Ligoj kaj Arrays". Ĉi-foje en la listo, elektu la artikolon "Serĉa Firmao". Plenumu klakon sur la butonon "OK".
- Aktivigo de la argumentoj de la argumentoj de la serĉa operatoro estas plenumata. La specifita funkcio estas desegnita por eligi la valoran numeron en specifa tabelo laŭ ĝia nomo. I estas danke al ĉi tiu funkcio, ke ni kalkulas la nombron de ŝnuro de specifa valoro por la funkcia funkcio. La sintakso de la serĉlibro estas prezentita:
= Search-estraro (search_name; vido £nasivo; [tipo_stacio])
"La dezirata" estas argumento enhavanta la nomon aŭ adreson de la ĉelo de la tria partio en kiu ĝi troviĝas. I estas la pozicio de ĉi tiu nomo en la cela teritorio kaj devus esti kalkulita. En nia kazo, la rolo de la unua argumento estos referencita al ĉeloj sur folio 1, en kiu dungitoj situas.
"Lista tabelo" estas argumento, kiu estas referenco al tabelo, kiu plenumas la serĉon de la specifa valoro por determini ĝian pozicion. Ni havos ĉi tiun rolon por ekzekuti la adreson de la "nomo" kolumno sur folio 2.
"Tipo de komparo" - argumento, kiu estas nedeviga, sed, male al la antaŭa operatoro, ĉi tiu laŭvola argumento estos bezonata. I indikas kiel kongrui kun la operatoro estas la dezirata valoro kun tabelo. Ĉi tiu argumento povas havi unu el tri valoroj: -1; 0; 1. Por senordaj aroj, elektu la opcion "0". Ĉi tiu opcio taŭgas por nia kazo.
Do, daŭrigu plenigi la argumentajn fenestrojn. Ni metas la kursoron en la kampon "Fogular Valoro", alklaku la unuan ĉelon "Nomo" kolumno sur folio 1.
- Post kiam la koordinatoj estas montrataj, starigis la kursoron en la "printita masiva" kampo kaj iru al la etikedo "folio 2", kiu situas ĉe la fino de la elstara fenestro super la statobreto. Clemente la maldekstra musbutono kaj reliefigu la kursoron ĉiujn ĉelojn de la "nomo" kolumno.
- Post kiam iliaj koordinatoj montriĝas en la "listo", iru al la kampo "Mapping Type" kaj starigu la numeron "0" de la klavaro. Post tio, ni denove revenas al la kampo "rigardante tra la tabelo". La fakto estas, ke ni plenumos kopii la formulon, kiel ni faris en la antaŭa metodo. Estos movo de adresoj, sed ĉi tie la koordinatoj de la tabelo vidita ni devas certigi. Li ne devus ŝanĝi. Ni reliefigas la koordinatojn kun la kursoro kaj alklaku la F4-Funkcian klavon. Kiel vi povas vidi, la dolara signo aperis antaŭ la koordinatoj, kio signifas, ke referenco de relativa fariĝis absoluta. Tiam alklaku la butonon "OK".
- La rezulto estas montrita en la unua ĉelo de la "vetas" kolumno. Sed antaŭ kopii, ni devas ripari alian areon, nome la unua argumenta funkcio indekso. Por fari tion, elektu la kolumnan elementon, kiu enhavas formulon, kaj moviĝu al la formulo. Atribui la unuan argumenton de la operatora indekso (B2: B7) kaj alklaku la F4-butonon. Kiel vi povas vidi, la dolara signo aperis proksime al la elektitaj koordinatoj. Alklaku la Eniga-klavo. Enerale, la formulo prenis la sekvan formon:
= Indekso (Sheet2! $ B $ 2: $ B $ 7; Search Board (Sheet1! A4; Listo2! $ A $ 2: $ $ 7; 0))
- Nun vi povas kopii per plenigaĵo. Ni nomas ĝin sammaniere, ke ni antaŭe parolis, kaj streĉu ĝis la fino de la tabula teritorio.
- Kiel vi povas vidi, malgraŭ la fakto, ke la mendado de kordoj en du rilataj tabloj ne koincidas, tamen ĉiuj valoroj estas streĉitaj laŭ la nomoj de laboristoj. Ĉi tio estis atingita danke al la uzo de la kombinaĵo de spektantoj de operatoroj.
Metodo 4: Speciala Inserto
TIE-tablaj aroj en Excel ankaŭ povas uzi specialan enmetaĵon.
- Elektu la valorojn, kiujn vi volas "streĉi" al alia tablo. En nia kazo, ĉi tiu estas la "veto" kolumno gamo sur folio 2. Alklaku la dediĉita fragmento kun la dekstra musbutono. En la listo, kiu malfermiĝas, elektu la artikolon "Kopio". Alternativa kombinaĵo estas la CTRL + C-ŝerco. Post tio, ni moviĝas al la folio 1.
- Moviĝante al la areo de la libro, kiun vi bezonas, asignas la ĉelojn, en kiuj la valoroj bezonos streĉiĝi. En nia kazo, ĉi tiu estas la "propono" kolumno. Alklaku la dediĉitan fragmenton per la dekstra musbutono. En la kunteksta menuo en la ilobreto "enmeti parametrojn", alklaku la ikonon "Enmetu komunikadon".
Ekzistas ankaŭ alternativo. Li, cetere, estas la sola por pli malnovaj versioj de Excel. En la kunteksta menuo, ni alportas la kursoron al la "speciala enigaĵo". En la aldona menuo, kiu malfermiĝas, elektu la pozicion kun la sama nomo.
- Post tio, speciala eniga fenestro malfermiĝas. Alklaku la butonon "Enmetu komunikadon" en la maldekstra angulo de la ĉelo.
- Kian ajn opcion, kiun vi elektas, valoroj de unu tablo-tabelo estos enmetitaj en alian. Kiam oni ŝanĝas datumojn en la fonto, ili ankaŭ aŭtomate ŝanĝiĝos en la enmetita teritorio.
Leciono: Speciala enmeto en Excel
Metodo 5: Komunikado inter tabloj en pluraj libroj
Krome, vi povas organizi ligon inter la tablo areoj en malsamaj libroj. Ĉi tio uzas specialan enmeti ilon. Agoj estos absolute similaj al tiuj, kiujn ni konsideras en la antaŭa metodo, escepte ke la navigado dum la formuloj ne havos inter la areoj de unu libro, sed inter la dosieroj. Nature, ĉiuj rilataj libroj devas esti malfermitaj.
- Elektu la datuman gamon por esti transdonita al alia libro. Alklaku ĝin dekstre musbutonon kaj elektu la pozicion "kopii" en la menuo malfermita.
- Tiam ni moviĝas al la libro, en kiu ĉi tiuj datumoj devas esti enmetitaj. Elektu la deziratan gamon. Alklaku butonon dekstre muson. En la kunteksta menuo en la grupo "Enmetu Agordojn", elektu la "INSET-komunikadon".
- Post tio, la valoroj estos enmetitaj. Ŝanĝinte datumojn en la fontlibro, tablo-tabelo de la laborlibro aŭtomate streĉos ilin. Kaj tute ne necesas certigi, ke ambaŭ libroj estas malfermitaj. Sufiĉas malfermi nur unu lernolibron, kaj ĝi aŭtomate tailage-datumojn de fermita rilata dokumento se estis antaŭaj ŝanĝoj en ĝi.
Sed oni notu, ke en ĉi tiu kazo la inseto estos produktita en la formo de senŝanĝa tabelo. Provante ŝanĝi ajnan ĉelon kun la enmetitaj datumoj, mesaĝo estos loĝata informante pri la nekapablo fari ĉi tion.
Ŝanĝoj en tia tabelo asociita kun alia libro nur povas rompi la konekton.
Titolo rompas inter tabloj
Kelkfoje necesas rompi la ligon inter la tabloj. La kialo de ĉi tio povas esti la supre priskribita kiam vi volas ŝanĝi tabelon enmetitan de alia libro kaj simple la nevolemo de la uzanto tiel ke la datumoj en la sama tablo aŭtomate ĝisdatigas de la alia.Metodo 1: Komunikado rompas inter libroj
Por rompi la ligon inter libroj en ĉiuj ĉeloj, per plenumado efektive unu operacio. En ĉi tiu kazo, la datumoj en la ĉeloj restos, sed ili jam estos statikaj ne ĝisdatigitaj valoroj, kiuj ne dependas de aliaj dokumentoj.
- En la libro en kiu valoroj de aliaj dosieroj estas streĉitaj, iru al la datuma langeto. Alklaku la ikonon "Ŝanĝi Ligilojn", kiu situas sur la bendo en la ilobreto "konekto". Oni notu, ke se la nuna libro ne enhavas ligojn kun aliaj dosieroj, tiam ĉi tiu butono estas neaktiva.
- La ligilo ŝanĝi fenestron estas lanĉita. Elektu el la listo de rilataj libroj (se estas pluraj el ili) la dosieron per kiu ni volas rompi la konekton. Alklaku la butonon "rompi la ligon".
- Inform-fenestro malfermiĝas, kiu provizas averton pri la konsekvencoj de pliaj agoj. Se vi certas, ke vi faros, alklaku la butonon "Break Communication".
- Post tio, ĉiuj referencoj al la specifa dosiero en la aktuala dokumento estos anstataŭitaj per statikaj valoroj.
Metodo 2: Enmeti valorojn
Sed la supra metodo taŭgas nur se vi bezonas tute rompi ĉiujn ligojn inter la du libroj. Kio se vi bezonas malkonekti la rilatajn tablojn ene de la sama dosiero? Vi povas fari ĉi tion kopiante la datumojn, kaj poste enmetante la saman lokon kiel valoroj. Parenteze, ĉi tiu metodo povas esti malkonfirmita inter la individuaj datumoj intervaloj de diversaj libroj sen rompi komunan rilaton inter dosieroj. Ni vidu kiel ĉi tiu metodo funkcias praktike.
- Ni reliefigas la teritorion, en kiu ni volas forigi komunikadon kun alia tablo. Alklaku ĝin dekstre musbutonon. En la malfermita menuo, elektu la "kopian" artikolon. Anstataŭ la specifaj agoj, vi povas telefoni alternativan kombinon de varmaj klavoj Ctrl + C.
- Poste, sen forigo de la elekto de la sama fragmento, denove alklaku ĝin per la dekstra musbutono. Ĉi-foje en la listo de Ago, alklaku la "Valoro" ikonon, kiu estas afiŝita en la enmeta parametroj grupo.
- Post tio, ĉiuj referencoj en la dediĉita gamo estos anstataŭitaj per statikaj valoroj.
Kiel vi povas vidi, Excel havas manierojn kaj ilojn por asocii plurajn tabelojn inter si. Samtempe tabulaj datumoj povas esti sur aliaj tukoj kaj eĉ en malsamaj libroj. Se necese, ĉi tiu konekto povas esti facile rompita.