Kiam vi plenumas certajn taskojn en Excel, foje vi devas trakti plurajn tabelojn, kiuj ankaŭ rilatas al si. Tio estas, la datumoj de unu tabelo estas tiritaj en la alian, kaj kiam ili ŝanĝas, la valoroj en ĉiuj rilataj tabelaj rangoj estas kalkulitaj.
Ligitaj tabloj estas tre utilaj por prilabori grandajn informojn. Ne estas tre oportune havi ĉiujn informojn en unu tabelo, kaj se ĝi ne estas homogena. Estas malfacile labori kun tiaj objektoj kaj serĉi ilin. Ĉi tiu problemo celas forigi rilatajn tablojn, informoj inter kiuj estas distribuita, sed samtempe estas interrilata. Ligitaj tablotakoj povas troviĝi ne nur ene de unu folio aŭ unu libro, sed ankaŭ troviĝas en apartaj libroj (dosieroj). Praktike, la lastaj du opcioj plej ofte estas uzataj, ĉar la celo de ĉi tiu teknologio estas foriri de la akumulado de datumoj, kaj amasigi ilin sur la sama paĝo ne fundamente solvas la problemon. Ni lernu kiel krei kaj kiel labori kun ĉi tiu speco de datuma administrado.
Krei ligitajn tabelojn
Unue, ni pensu pri kiel eblas krei ligon inter malsamaj tablaj gamoj.
Metodo 1: Rekte ligante tablojn kun formulo
La plej facila maniero ligi datumojn estas uzi formulojn ligitajn al aliaj tablaj intervaloj. I nomiĝas rekta ligilo. Ĉi tiu metodo estas intuicia, ĉar kun ĝi la ligo estas plenumata preskaŭ sammaniere kiel krei referencojn al datumoj en unu tabela tabelo.
Ni vidu kiel ekzemplo povas ligi rektan ligon. Ni havas du tablojn sur du folioj. En unu tabelo, la etato estas kalkulita uzante formulon multiplikante la imposto de laboristoj per ununura rapideco por ĉiuj.
Sur la dua folio estas tabula distanco en kiu estas listo de dungitoj kun iliaj salajroj. La listo de dungitoj en ambaŭ kazoj estas prezentita en la sama ordo.
Estas necese, ke la datumoj pri tarifoj de la dua folio estu tirataj en la respondaj ĉeloj de la unua.
- Sur la unua folio, elektu la unuan kolumnon. "Veto". Ni enmetis ŝian markon "=". Poste alklaku la etikedon "Folio 2"Kiu troviĝas ĉe la maldekstra flanko de la Excel-interfaco super la statobreto.
- Movas al la dua areo de la dokumento. Alklaku la unuan ĉelon en la kolumno. "Veto". Tiam alklaku la butonon. Eniru sur la klavaro por efektivigi datumajn enirojn en la ĉelo en kiu la signo antaŭe estis fiksita egalas.
- Tiam estas aŭtomata transiro al la unua folio. Kiel vi povas vidi, la indico de la unua dungito de la dua tablo estas tirita en la taŭgan ĉelon. Metinte la kursoron sur la ĉelon kun la veto, ni vidas, ke la kutima formulo estas uzata por montri datumojn sur la ekrano. Sed antaŭ ol la koordinatoj de la ĉelo, kie la datumoj estas montrataj, estas esprimo "Folio2!"kiu indikas la nomon de la areo de la dokumento, kie ili troviĝas. La ĝenerala formulo en nia kazo estas jene:
= Folio2! B2 - Nun vi devas transdoni la datumojn pri la tarifoj de ĉiuj aliaj dungitoj de la entrepreno. Kompreneble, ĉi tio povas esti farita sammaniere kiel ni plenumis la taskon por la unua dungito, sed ĉar ambaŭ listoj de dungitoj estas aranĝitaj laŭ la sama ordo, la tasko povas esti signife simpligita kaj rapidigi ĝian solvon. Ĉi tio povas esti farita simple kopiante la formulon al la suba rango. Pro la fakto, ke ligoj en Excel defaŭlte estas relativaj, kiam ili estas kopiitaj, la valoroj ŝanĝas, kion ni bezonas. La kopia procedo mem povas esti farita per la pleniga markilo.
Do, metu la kursoron en la suban rajton de la elemento kun la formulo. Post tio, la kursoro devas konvertiĝi al plenigo en la formo de nigra kruco. Ni plenumas la krampon de la maldekstra musbutono kaj trenas la kursoron al la fundo de la kolumno.
- Ĉiuj datumoj de la sama kolumno Folio 2 estis tiritaj al la tablo Folio 1. Kiam datumoj ŝanĝas al Folio 2 ili aŭtomate ŝanĝos la unuan.
Metodo 2: uzu fasadon de operacisimboloj - MATCH
Sed, ĉu la listo de dungitoj en tabularaj tabeloj ne aranĝitaj en la sama ordo? En ĉi tiu kazo, kiel menciis pli frue, unu el la opcioj estas agordi la ligon inter ĉiu el tiuj ĉeloj, kiuj devus esti ligitaj permane. Sed ĉi tio taŭgas nur por malgrandaj tabloj. Por masivaj teritorioj, ĉi tiu opcio, plejofte, bezonos multan tempon por efektivigi, kaj plej malbone - praktike ĝi tute ne ebligos. Sed vi povas solvi ĉi tiun problemon kun aro da telefonistoj INDICE - DIVIDUO. Ni vidu kiel ĉi tio povas esti farita per ligado de datumoj en tabularaj gamoj, kiuj estis diskutitaj en la antaŭa metodo.
- Elektu la unuan eron en la kolumno. "Veto". Iru al Funkciaj Sorĉistoklakante sur la ikono "Enmetu funkcion".
- En Funkcia sorĉisto en grupo "Ligoj kaj tabeloj" trovu kaj elektu la nomon INDICE.
- Ĉi tiu operatoro havas du formojn: formo por labori kun tabeloj kaj referenco. En nia kazo, la unua opcio necesas, do en la sekva fenestro por elekti formularon, kiu malfermos, ni elektos ĝin kaj alklaku la butonon "Bone".
- La operacianta fenestro funkciis. INDICE. La tasko de la specifita funkcio estas montri la valoron en la elektita intervalo en la linio kun la specifa nombro. Operatorenerala operatora formulo INDICE estas ĉi tio:
= Indekso (tabelo; nombro linio; [nombro kolumno])"Array" - la argumento enhavanta la adreson de la intervalo de kiu ni ĉerpos informojn laŭ la nombro de la specifita ĉeno.
"Linia nombro" - la argumento estas la nombro de ĉi tiu linio mem. Gravas scii, ke la linia nombro ne devas esti specifita rilate al la tuta dokumento, sed nur rilate al la elektita tabelo.
"Kolumna nombro" - La argumento estas nedeviga. Por solvi nian problemon specife, ni ne uzos ĝin, kaj pro tio ne necesas priskribi ĝian esencon aparte.
Metu la kursoron sur la kampon "Array". Post tio iru al Folio 2 kaj, tenante la maldekstran musbutonon, elektu la tutan enhavon de la kolumno "Veto".
- Post kiam la koordinatoj estas montritaj en la operatora fenestro, metu la kursoron sur la kampon "Linia nombro". Ni montros ĉi tiun argumenton per la operatoro DIVIDUO. Sekve, alklaku la triangulon situantan maldekstre de la funkcia linio. Listo de ĵus uzitaj operatoroj malfermiĝas. Se vi trovas inter ili la nomon "MATCH"tiam vi povas alklaki ĝin. Alie, alklaku la plej lastan liston en la listo - "Aliaj ecoj ...".
- La norma fenestro komenciĝas. Funkcio majstroj. Iru al ĝi en la sama grupo. "Ligoj kaj tabeloj". Ĉi-foje en la listo elektu la elementon "MATCH". Efektivigu klakon sur la butonon. "Bone".
- Aktivigas argumentojn de operatoraj fenestroj DIVIDUO. La specifita funkcio celas montri la nombron de valoro en specifa tabelo laŭ ĝia nomo. Danke al ĉi tiu ŝanco, ni kalkulos la vicon de specifa valoro por la funkcio. INDICE. Sintakso DIVIDUO prezentita kiel:
= MATCH (serĉvaloro; serĉaĵaro; [matrico-tipo])"Serĉita valoro" - la argumento enhavanta la nomon aŭ adreson de la triaj gamo ĉelo en kiu ĝi troviĝas. I estas la pozicio de ĉi tiu nomo en la cela teritorio, kiu devas esti kalkulita. En nia kazo, la unua argumento estos ĉelaj referencoj al Folio 1en kiu estas lokitaj la nomoj de dungitoj.
"Vida tabelo" - argumento reprezentanta ligon al tabelo en kiu la specita valoro estas serĉita por determini ĝian pozicion. Ni ludos ĉi tiun rolon adreso kolumno "Unua nomo plu Folio 2.
"Mapping Type" - argumento estas nedeviga, sed, malkiel la antaŭa aserto, ni bezonos ĉi tiun laŭvolan argumenton. I indikas kiel la operatoro egalos la deziratan valoron kun la tabelo. Ĉi tiu argumento povas havi unu el tri valoroj: -1; 0; 1. Por neordigitaj aroj, elektu la opcion "0". Ĉi tiu opcio taŭgas por nia kazo.
Do, ni komencu plenigi la kampojn de la argumenta fenestro. Metu la kursoron sur la kampon "Serĉita valoro", alklaku la unuan ĉelon de la kolumno "Nomo" plu Folio 1.
- Post la montrado de la koordinatoj, metu la kursoron sur la kampon "Vida tabelo" kaj daŭrigu la fulmoklavon "Folio 2"kiu troviĝas ĉe la malsupro de la Excel-fenestro super la statobreto. Tenu la maldekstran musbutonon kaj reliefigu ĉiujn ĉelojn en la kolumno. "Nomo".
- Post kiam iliaj koordinatoj estas montritaj en la kampo "Vida tabelo"iru al la kampo "Mapping Type" kaj agordu la numeron de la klavaro "0". Post ĉi tio, ni revenos al la kampo denove. "Vida tabelo". La fakto estas, ke ni kopios la formulon, kiel ni faris en la antaŭa metodo. Estos kompenso de adresoj, sed ni devas korekti la koordinatojn de la tabelo vidata. I ne devas ŝanĝi. Elektu la koordinatojn de la kursoro kaj alklaku la funkcian klavon F4. Kiel vi povas vidi, dolara signo aperis antaŭ la koordinatoj, kio signifas, ke la ligo de parenco fariĝis absoluta. Tiam alklaku la butonon "Bone".
- La rezulto estas montrita en la unua ĉelo de la kolumno. "Veto". Sed antaŭ ol kopii, ni devas fiksi alian areon, nome la unuan argumenton de la funkcio INDICE. Por fari tion, elektu la elementon de la kolumno, kiu enhavas la formulon, kaj iru al la formala stango. Elektu la unuan argumenton de la operatoro INDICE (B2: B7) kaj alklaku la butonon F4. Kiel vi povas vidi, la dolara signo aperis proksime al la elektitaj koordinatoj. Alklaku la butonon Eniru. Enerale, la formulo prenis la jenan formon:
= INDICE (Sheet2! $ B $ 2: $ B $ 7; MATCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0)) - Nun vi povas kopii per la pleniga markilo. Nomu ĝin sammaniere kiel ni antaŭe parolis, kaj etendu ĝin al la fino de la tabelaro.
- Kiel vi povas vidi, malgraŭ la fakto, ke la ordo de la vicoj de la du rilataj tabuloj ne kongruas, tamen ĉiuj valoroj estas streĉitaj laŭ la nomoj de la laboristoj. Ĉi tio estis atingita per la uzo de kombinaĵo de operatoroj INDICE-DIVIDUO.
Vidu ankaŭ:
Excel-funkcio INDICE
La matĉa funkcio en Excel
Metodo 3: Elfari Matematikajn Operaciojn kun Rilataj Datumoj
Rekta ligado de datumoj ankaŭ bonas, ĉar ĝi permesas ne nur montri valorojn montritajn en aliaj tabelaj rangoj en unu el la tabeloj, sed ankaŭ plenumi diversajn matematikajn operaciojn kun ili (aldono, divido, subtraho, multipliko ktp.).
Ni vidu kiel ĉi tio estas farita en praktiko. Ni faru tion Folio 3 ĝeneralaj kompaniaj salajaj datumoj estos montritaj sen dungito. Por ĉi tio, la impostoj de personaro estos forigitaj Folio 2, resumu (uzante la funkcion SUMO) kaj multobligita per la koeficiento uzanta la formulon.
- Elektu la ĉelon, en kiu estos montrata la totala etato Folio 3. Alklaku la butonon "Enmetu funkcion".
- I devus lanĉi la fenestron Funkcio majstroj. Iru al la grupo "Matematika" kaj elektu la nomon tie "SUMM". Poste alklaku la butonon "Bone".
- Moviĝante al la fenestro de funkcia argumento SUMOkiu estas desegnita por kalkuli la sumon de la elektitaj nombroj. I havas la jenan sintakson:
= SUMO (numero1; nombro2; ...)La kampoj en la fenestro respondas al la argumentoj de la specifita funkcio. Kvankam ilia nombro povas atingi 255 pecojn, por nia celo nur unu sufiĉos. Metu la kursoron sur la kampon "Numero1". Alklaku la etikedon "Folio 2" super la statobreto.
- Post kiam ni moviĝis al la dezirata sekcio de la libro, elektu la kolumnon, kiu devas esti resumita. Ni igas ĝin kursoro, tenante la maldekstran musbutonon. Kiel vi povas vidi, la koordinatoj de la elektita areo tuj aperas en la kampo de la argumentfenestro. Tiam alklaku la butonon. "Bone".
- Post tio, ni aŭtomate moviĝas al Folio 1. Kiel vi povas vidi, la totala kvanto de salajroj de laboristoj estas jam montrita en la responda elemento.
- Sed tio ne estas ĉio. Kiel ni memoras, la salajro kalkulas multiplikante la valoron de la kurzo per la koeficiento. Tial, ni denove elektas la ĉelon en kiu la sumita valoro situas. Post tio iru al la formala stango. Ni aldonas multiplikan signon al ĝia formulo (*), kaj tiam alklaku la elementon en kiu troviĝas la koeficiento. Por fari la kalkulon, alklaku Eniru sur la klavaro. Kiel vi povas vidi, la programo kalkulis la tutan salajron por la entrepreno.
- Reiru al Folio 2 kaj ŝanĝi la grandon de la indico de iu dungito.
- Post ĉi tio, denove moviĝu al la paĝo kun la tuta sumo. Kiel vi povas vidi, pro ŝanĝoj en la rilata tablo, la rezulto de la totala salajro aŭtomate estis kalkulita.
Metodo 4: speciala enigo
Vi ankaŭ povas ligi tabelanojn en speciala enmeto.
- Elektu la valorojn, kiuj devas "streĉiĝi" al alia tablo. En nia kazo, ĉi tio estas la kolumna intervalo. "Veto" plu Folio 2. Alklaku la elektitan fragmenton per la dekstra musbutono. En la listo, kiu malfermiĝas, elektu la elementon "Kopiu". Alternativa klavkombino estas Stir + C. Post tiu movo al Folio 1.
- Moviĝante al la dezirata areo de la libro, ni elektas la ĉelojn en kiuj vi volas tiri la valorojn. En nia kazo, ĉi tio estas kolumno. "Veto". Alklaku la elektitan fragmenton per la dekstra musbutono. En la kunteksta menuo en la ilobreto "Insercaj Elektoj" alklaku la ikonon "Enmetu Ligilon".
Ankaŭ ekzistas alternativo. Parenteze, ĝi estas la sola por pli malnovaj versioj de Excel. En la kunteksta menuo, movu la kursoron al la objekto "Algluu Specialaĵon". En la aldona menuo kiu malfermiĝas, elektu la elementon kun la sama nomo.
- Post tio malfermas speciala eniga fenestro. Ni premas la butonon "Enmetu Ligilon" en la malsupra maldekstra angulo de la ĉelo.
- Ajna eblo elektita, valoroj de unu tabela aro estos enmetitaj en alian. Kiam vi ŝanĝas la datumojn en la fonto, ili ankaŭ aŭtomate ŝanĝos la enmetitan gamon.
Leciono: Algluu Specialan en Excel
Metodo 5: Rilato inter tabeloj en pluraj libroj
Krome vi povas organizi la ligon inter tablaj spacoj en malsamaj libroj. Ĉi tio uzas la specialan ilon. Agoj estos absolute similaj al tiuj, kiujn ni konsideris en la antaŭa metodo, escepte ke navigado dum la enkonduko de la formuloj ne devas okazi inter areoj de unu libro, sed inter dosieroj. Nature, ĉiuj rilataj libroj devus esti malfermitaj.
- Elektu la gamon de datumoj, kiujn vi volas transdoni al alia libro. Alklaku ĝin per la dekstra musbutono kaj elektu la pozicion en la menuo kiu malfermas "Kopiu".
- Tiam ni moviĝu al la libro en kiu ĉi tiuj datumoj devos esti enmetitaj. Elektu la deziratan gamon. Alklaku la dekstran musbutonon. En la kunteksta menuo en la grupo "Insercaj Elektoj" elekti artikolon "Enmetu Ligilon".
- Post ĉi tio, la valoroj estos enmetitaj. Kiam vi ŝanĝas la datumojn en la fonta libro, la tabula tabelo de la laborejo tiros ilin aŭtomate. Kaj tute ne necesas malfermi ambaŭ librojn al ĉi tio. Sufiĉas malfermi nur unu laborlibron, kaj ĝi aŭtomate tiros la datumojn de la fermita ligita dokumento, se ŝanĝoj estis antaŭe faritaj en ĝi.
Sed oni notu, ke en ĉi tiu kazo la enmeto estos farita en la formo de neŝanĝebla tabelo. Se vi provas ŝanĝi ĉelon per enmetitaj datumoj, mesaĝo aperos informante vin, ke ne eblas fari ĉi tion.
Ŝanĝoj en tia tabelo asociita kun alia libro povas esti faritaj nur rompante la ligon.
Malkonektado inter tabloj
Kelkfoje necesas rompi la ligon inter tablobakoj. La kialo de ĉi tio eble estas, kiel la supre priskribita kazo, kiam vi volas ŝanĝi tabelon enmetitan de alia libro, aŭ simple ĉar la uzanto ne volas, ke la datumoj en unu tablo estu aŭtomate ĝisdatigitaj de alia.
Metodo 1: malkonekti inter la libroj
Vi povas rompi la ligon inter libroj en ĉiuj ĉeloj per plenumado de preskaŭ unu operacio. Samtempe, la datumoj en la ĉeloj restos, sed ili estos jam statikaj ne-ĝisdatigitaj valoroj, kiuj ne dependas de aliaj dokumentoj.
- En la libro, en kiu valoroj de aliaj dosieroj estas tiritaj, iru al la langeto "Datumoj". Alklaku la ikonon "Redakti ligojn"kiu situas sur la bendo en la bloko de iloj "Rilatoj". Ni notu, ke se la nuna libro ne enhavas ligilojn al aliaj dosieroj, ĉi tiu butono estas neaktiva.
- La fenestro por ŝanĝi ligojn lanĉas. Elektu el la listo de rilataj libroj (se estas kelkaj) la dosiero per kiu ni volas rompi la ligon. Alklaku la butonon "Rompu la ligon".
- Informfenestro malfermiĝas, en kiu estas averto pri la sekvoj de pliaj agoj. Se vi estas certa pri tio, kion vi faros, tiam alklaku la butonon. "Rompi ligojn".
- Post tio, ĉiuj referencoj al la specifita dosiero en la nuna dokumento estos anstataŭigitaj per senmovaj valoroj.
Metodo 2: Enigu valorojn
Sed la supra metodo estas taŭga nur se vi bezonas tute forigi ĉiujn ligojn inter la du libroj. Kion fari se vi volas malkonekti rilatajn tabelojn en la sama dosiero? Vi povas fari ĉi tion kopiante la datumojn, kaj poste alglui ĝin al la sama loko kiel la valoroj.Parenteze, la sama metodo povas esti uzata por rompi la konekton inter apartaj datumaj intervaloj de malsamaj libroj sen rompi la ĝeneralan ligon inter dosieroj. Ni vidu kiel ĉi tiu metodo funkcias praktike.
- Elektu la teritorion, en kiu ni volas forigi la ligon al alia tablo. Alklaku ĝin per la dekstra musbutono. En la menuo kiu malfermiĝas, elektu la elementon "Kopiu". Anstataŭ ĉi tiuj agoj, vi povas tajpi alternativan varman klavkombinon. Stir + C.
- Tiam, sen forigi la elekton de la sama fragmento, denove alklaku ĝin per la dekstra musbutono. Ĉi-foje en la listo de agoj ni alklakas la ikonon "Valoroj"kiu estas enmetita en grupon de iloj "Insercaj Elektoj".
- Post tio, ĉiuj ligoj en la elektita intervalo estos anstataŭigitaj per senmovaj valoroj.
Kiel vi povas vidi, Excel havas metodojn kaj ilojn por ligi plurajn tabelojn. En ĉi tiu kazo, la tabulaj datumoj povas esti sur aliaj folioj kaj eĉ en malsamaj libroj. Se necese, ĉi tiu konekto povas esti facile rompita.
