Laborante kun ligitaj tabloj en Microsoft Excel

Kiam vi plenumas iujn taskojn en Excel, foje vi devas trakti plurajn tabelojn, kiuj ankaŭ rilatas. Tio estas, datumoj de unu tablo estas enŝovitaj en alian kaj kiam ili estas ŝanĝitaj, valoroj rekalkuliĝas en ĉiuj rilataj tablaj gamoj.

Ligitaj tabloj estas tre oportune uzeblaj por prilabori multan informon. Meti ĉiujn informojn en unu tablon, krom se ĝi ne estas homogena, ĝi ne tre taŭgas. Estas malfacile labori kun tiaj celoj kaj serĉi ilin. La indikita problemo estas desegnita por esti forigita per ligitaj tabloj, informoj inter kiuj estas distribuitaj, sed samtempe estas interligitaj. Ligitaj tabelintervaloj troveblas ne nur en unu folio aŭ unu libro, sed ankaŭ povas trovi en apartaj libroj (dosieroj). La lastaj du opcioj estas plej ofte uzataj en la praktiko, ĉar la celo de ĉi tiu teknologio estas foriĝi de la amasiĝo de datumoj, kaj amasi ilin sur unu paĝo ne esence solvas la problemon. Ni lernu kiel krei kaj kiel labori kun ĉi tiu tipo de datuma administrado.

Krei ligitajn tabulojn

Unue ni pensu pri kiaj metodoj ekzistas ebleco krei interrilaton inter diversaj tabeloj.

Metodo 1: rekte ligi tablojn kun formulo

La plej facila maniero kunligi datumojn estas uzi formulojn, kiuj referencas aliajn tabelojn. Ĝi estas nomata rekta ligado. Ĉi tiu metodo estas intuicia, ĉar kun ĝi la ligado realiĝas preskaŭ same kiel kreante ligojn al datumoj en unu tabela tabelo.

Ni vidu kiel, ekzemple, ligilo povas formiĝi per rekta ligado. Ni havas du tablojn sur du littukoj. Sur unu tablo, salajro kalkuliĝas per la formulo multigante la dungitan indicon per ununura koeficiento por ĉiuj.

Sur la dua folio estas tabelo, kiu enhavas liston de dungitoj kun iliaj salajroj. La listo de dungitoj en ambaŭ kazoj estas prezentita en la sama ordo.

Oni devas certigi, ke la datumoj pri la tarifoj de la dua folio estas enŝovitaj al la respondaj ĉeloj de la unua.

1. Sur la unua folio, elektu la unuan ĉelon en la kolumno Oferto. Ni metis signon en ĝin "=". Tuj poste alklaku la ŝparvojo "Folio 2", kiu situas maldekstre de la Excel-interfaco super la status-stango.



2. Moviĝas al la dua areo de la dokumento. Ni alklakas la unuan ĉelon en la kolumno Oferto. Tiam alklaku la butonon Eniru sur la klavaro por enigi datumojn en la ĉelon en kiu antaŭe estis starigita la signo egalas.



3. Poste estas aŭtomata transiro al la unua folio. Kiel vi povas vidi, la indico de la unua oficisto el la dua tablo estas trenita en la respondan ĉelon. Metante la kursoron sur la ĉelon, kiu enhavas la veton, ni vidas, ke la kutima formulo estas uzata por prezenti datumojn sur la ekrano. Sed antaŭ la koordinatoj de la ĉelo, de kie eligas la datumojn, ekzistas esprimo "Folio2!", kiu indikas la nomon de la dokumenta areo kie ili troviĝas. La ĝenerala formulo en nia kazo aspektas jene:

   = Folio2! B2



4. Nun vi devas translokigi la datumojn pri la tarifoj de ĉiuj aliaj dungitoj de la entrepreno. Kompreneble tio povas fariĝi laŭ la sama maniero, ke ni kompletigis la taskon por la unua oficisto, sed konsiderante ke ambaŭ listoj de dungitoj estas aranĝitaj en la sama ordo, la tasko povas signife simpligi kaj rapidigi sian solvon. Ĉi tio eblas per simple kopii la formulon al la suba gamo. Pro la fakto, ke la ligoj en Excel estas relative defaŭlte, kiam ili estas kopiitaj, la valoroj ŝanĝiĝas, kio estas, kion ni bezonas. La kopia proceduro mem povas esti farita per la pleniga markilo.

   Do, metu la kursoron en la suban dekstran areon de la elemento kun la formulo. Post tio, la kursoro devas esti transformita al plenigilo en la formo de nigra kruco. Alpremu la maldekstran musbutonon kaj trenu la kursoron al la fundo de la kolumno.



5. Ĉiuj datumoj el simila kolumno sur Folio 2 estis tiritaj en tablon pluen Folio 1. Ŝanĝante datumojn al Folio 2 ili aŭtomate ŝanĝos je la unua.

Metodo 2: uzante faskon da INDEX-operatoroj - SEARCH

Sed se la listo de dungitoj en la tabelaj tabeloj ne estas en la sama ordo? En ĉi tiu kazo, kiel menciite antaŭe, unu el la opcioj estas establi rilaton inter ĉiu el tiuj ĉeloj, kiuj devas esti konektitaj permane. Sed ĉi tio taŭgas nur por malgrandaj tabloj. Por amasaj gamoj, tia eblo plej bone bezonos multan tempon por efektivigi, kaj en la plej malbona praktike ĝi ne fareblas. Sed ĉi tiu problemo povas esti solvita uzante faskon da telefonistoj INDEX - SEKRETO. Ni vidu, kiel tio eblas, ligante la datumojn en la tabelojn, kiujn oni priparolis en la antaŭa metodo.

1. Elektu la unuan kolumnan elementon Oferto. Iru al Feature Sorĉistoalklakante la ikonon "Enmetu funkcion".



2. En Funkcia sorĉisto en la grupo Referencoj kaj Arkivoj trovi kaj reliefigi la nomon INDEX.



3. Ĉi tiu telefonisto havas du formojn: formularon por labori kun tabeloj kaj referenca. En nia kazo, la unua opcio estas bezonata, do en la sekva fenestro por elekti la formon, kiu malfermiĝos, elektu ĝin kaj alklaku la butonon "Bone".



4. Fenestro de argumentoj de telefonisto lanĉita INDEX. La tasko de ĉi tiu funkcio estas produkti valoron, kiu estas en la elektita gamo en la linio kun la specifita nombro. Formulo de ĝenerala telefonisto INDEX tia estas:

   = INDEX (tabelo; vico_numero; [kolumna_numero])

   Array - argumento enhavanta la adreson de la rango de kiu ni ĉerpos informojn per la nombro de la specifita linio.

   Linia numero - la argumento, kiu estas la nombro de ĉi tiu sama linio. Gravas scii, ke la linia nombro ne devas esti specifita rilate al la tuta dokumento, sed nur rilate al la elektita tabelo.

   Numero Kolumno - argumento laŭvola. Ni ne uzos ĝin por solvi nian specifan problemon, kaj tial ne necesas aparte priskribi ĝian esencon.

   Metu la kursoron sur la kampo Array. Post tio, iru al Folio 2 kaj tenante la maldekstran musbutonon, elektu la tutan enhavon de la kolumno Oferto.



5. Post kiam la koordinatoj aperu en la fenestro de la telefonisto, metu la kursoron sur la kampo Linia numero. Ni eligos ĉi tiun argumenton per la telefonisto SEKRETO. Tial ni alklakas la triangulon, kiu situas maldekstre de la funkcia linio. Malfermiĝas listo de ĵus uzataj telefonistoj. Se vi trovos nomon inter ili "Serĉi"tiam vi povas alklaki ĝin. Alie alklaku la lastan eron de la listo - "Aliaj ecoj ...".



6. La norma fenestro komenciĝas Funkciaj Sorĉistoj. Ni transdonas ĝin al la sama grupo Referencoj kaj Arkivoj. Ĉi-foje elektu la eron en la listo "Serĉi". Alklaku la butonon. "Bone".



7. Fenestro de argumentoj de telefonisto estas aktiva SEKRETO. La specifita funkcio celas montri la nombron de valoro en specifa tabelo per sia nomo. Danke al ĉi tiu funkcio, ni kalkulos la linian numeron de certa valoro por la funkcio INDEX. Sintakso SEKRETO reprezentita jene:

   = SEARCH (serĉo_valoro; serĉado_array; [match_type])

   "Serĉanta valoron" - argumento enhavanta la nomon aŭ adreson de la ĉelo de la tria parto en kiu ĝi troviĝas. Ĝi estas la pozicio de ĉi tiu nomo en la cela gamo, kiu devus esti kalkulita. En nia kazo, la unua argumento estos referencoj al ĉeloj sur Folio 1kie troviĝas la nomoj de dungitoj.

   Vidita Array - argumento reprezentanta referencon al tabelo en kiu oni serĉas la specifitan valoron por determini ĝian pozicion. La kolumna adreso "ludos ĉi tiun rolon ĉi tie."Antaŭnomo sur Folio 2.

   Matĉo-Tipo - argumento, kiu estas laŭvola, sed, male al la antaŭa aserto, ni bezonos ĉi tiun laŭvolan argumenton. Ĝi indikas kiel la telefonisto egalos la serĉan valoron kun la tabelo. Ĉi tiu argumento povas havi unu el tri valoroj: -1; 0; 1. Por neordiritaj tabeloj, elektu "0". Ĉi tiu opcio taŭgas por nia kazo.

   Do, ni komencu plenigi la kampojn de la argumentoj-fenestro. Metu la kursoron sur la kampo "Serĉanta valoron"alklaku la unuan ĉelon de la kolumno "Nomo" sur Folio 1.



8. Post kiam la koordinatoj estas montritaj, agordu la kursoron sur la kampo Vidita Array kaj alklaku la ŝparvojo "Folio 2", kiu situas ĉe la fundo de la Excel-fenestro super la status-stango. Tenu la maldekstran musbutonon kaj elektu per la kursoro ĉiujn ĉelojn en la kolumno "Nomo".



9. Post iliaj koordinatoj estas montritaj sur la kampo Vidita Arrayiru al la kampo Matĉo-Tipo kaj agordi la numeron tie de la klavaro "0". Post tio, ni revenas al la kampo denove Vidita Array. Fakte ni kopios la formulon, kiel ni faris en la antaŭa metodo. Direkta ŝanĝo okazos, sed ĉi tie ni devas ripari la koordinatojn de la rigardo. Ĝi ne estu delokita. Elektu la koordinatojn per la kursoro kaj premu la funkcion-klavon F4. Kiel vi povas vidi, la dolara signo aperis antaŭ la koordinatoj, kio signifas, ke la ligo turniĝis de relative al absoluta. Tiam alklaku la butonon "Bone".



10. La rezulto estas montrata en la unua ĉelo de la kolumno. Oferto. Sed antaŭ ol kopii, ni bezonas ripari alian areon, nome la unuan argumenton de la funkcio INDEX. Por fari tion, elektu la kolumnan elementon, kiu enhavas la formulon, kaj moviĝu al la linio de formuloj. Ni elektas la unuan argumenton de la telefonisto INDEX (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:

    = INDEX (Sheet2! $ B $ 2: $ B $ 7; SEARCH (Sheet1! A4; Sheet2! $ A $ 2: $ A $ 7; 0))



11. Nun vi povas kopii per la pleniga markilo. Ni nomas ĝin tiel, kiel ni parolis antaŭe, kaj etendas ĝin ĝis la fino de la tabela gamo.



12. Kiel vi povas vidi, malgraŭ la fakto, ke la vico-ordo de la du rilataj tabeloj ne kongruas, tamen ĉiuj valoroj estas streĉitaj laŭ la nomoj de dungitoj. Tio estis atingita per la uzo de kombinaĵo de telefonistoj INDEX-SEKRETO.

Legu ankaŭ:
EXEX-funkcio en Excel
Funkcio EXCEL en Excel

Metodo 3: plenumu matematikajn operaciojn kun rilataj datumoj

Rekta datuma ligado estas ankaŭ bona, ĉar ĝi permesas ne nur aperigi valorojn, kiuj estas montritaj en aliaj tabeloj, en unu el la tabloj, sed ankaŭ realigi diversajn matematikajn operaciojn kun ili (aldono, divido, subtraho, multiplikado, ktp).

Ni vidu kiel tio efektivigas praktike. Ni realigu tion Folio 3 ĝeneralaj salajraj datumoj por la kompanio estos montritaj sen rompo de dungitoj. Por tio, la salajrataj tarifoj estos eltiritaj Folio 2, resumita (uzante la funkcion SUMO) kaj multipliku per koeficiento uzante la formulon.

1. Elektu la ĉelon, kie montriĝos la rezulto de nombrilo. Folio 3. Alklaku la butonon. "Enmetu funkcion".



2. Fenestro devas komenciĝi Funkciaj Sorĉistoj. Iru al la grupo "Matematika" kaj elektu la nomon tie SUMO. Tuj poste alklaku la butonon "Bone".



3. La funkciaj argumentoj estas translokigitaj al la fenestro SUMO, kiu estas desegnita por kalkuli la sumon de la elektitaj nombroj. Ĝi havas la jenan sintakson:

   = SUMO (nombro1; numero2; ...)

   La kampoj en la fenestro respondas al la argumentoj de la specifita funkcio. Kvankam ilia nombro povas atingi 255, nur unu sufiĉos por nia celo. Metu la kursoron sur la kampo "Numero1". Alklaku la ŝparvojo "Folio 2" super la status-stango.



4. Post kiam ni translokiĝis al la dezirata sekcio de la libro, elektu la kolumnon, kiu devus esti resumita. Ni faras tion per la kursoro tenante la maldekstran musbutonon. Kiel vi povas vidi, la koordinatoj de la elektita areo tuj aperas sur la kampo de la argumentoj. Tiam alklaku la butonon "Bone".



5. Post tio, ni aŭtomate translokiĝas al Folio 1. Kiel vi povas vidi, la tuta kvanto de dungosoldatoj jam aperas en la responda elemento.



6. Sed tio ne estas ĉio. Kiel ni memoras, salajro kalkuliĝas multobligante la valoron de la indico per faktoro. Tial ni denove elektas la ĉelon, en kiu troviĝas la sumigita valoro. Post tio ni pasas al la linio de formuloj. Aldonu al la formulo en ĝi multiplikan signon (*), kaj tiam alklaku la elementon, en kiu troviĝas la indikilo de koeficiento. Por plenumi la kalkulon, alklaku la butonon Eniru sur la klavaro. Kiel vi povas vidi, la programo kalkulis la tutan salajron por la entrepreno.



7. Reen al Folio 2 kaj ŝanĝi la imposton de iu dungito.



8. Post tio, ni denove moviĝas al la paĝo kun la tuta kvanto. Kiel vi povas vidi, pro ŝanĝoj en la ligita tabelo, la rezulto de la tuta salajro aŭtomate rekalkuliĝis.

Metodo 4: kutimo enmeti

Vi ankaŭ povas ligi tabelajn tabelojn en Excel per speciala enmeto.

1. Ni elektas la valorojn, kiuj devos esti "tiritaj" en alian tablon. En nia kazo, ĉi tiu estas la gamo de la kolumno Oferto sur Folio 2. Ni alklakas la elektitan fragmenton per la dekstra musbutono. En la listo, kiu malfermiĝas, elektu Kopii. Alternativa klavara ŝparvojo estas Ctrl + C. Post tio ni translokiĝas al Folio 1.



2. Moviĝinte al la areo de la libro, kiun ni bezonas, ni elektas la ĉelojn, en kiuj ni devos streki la valorojn. En nia kazo, ĉi tio estas kolumno Oferto. Ni alklakas la elektitan fragmenton per la dekstra musbutono. En la kunteksta menuo en la ilo-bloko Enmetu Eblojn alklaku la ikonon Alglui Ligilon.

   

   Ekzistas ankaŭ alternativo. Intence, ĝi estas la sola por pli malnovaj versioj de Excel. En la kunteksta menuo, transsaltu "Speciala enmeto". En la aldona menuo, kiu malfermiĝas, elektu la pozicion kun la sama nomo.



3. Post tio, la speciala enmeto-fenestro malfermiĝas. Alklaku la butonon Alglui Ligilon en la malsupra maldekstra angulo de la ĉelo.



4. Kian ajn elekton vi elektos, valoroj el unu tabela tabelo estos enmetitaj en alian. Ŝanĝante datumojn en la fonto, ili ankaŭ aŭtomate ŝanĝos en la enigita gamo.

Leciono: Speciala Enmeto en Excel

Metodo 5: ligo inter tabloj en multoblaj libroj

Krome vi povas organizi komunikadon inter tabelaj lokoj en diversaj libroj. Speciala enmetita ilo estas uzata. La agoj estos absolute similaj al tiuj, kiujn ni konsideris en la antaŭa metodo, krom ke vi devos navigi dum vi faras formulojn ne inter areoj de la sama libro, sed inter dosieroj. Kompreneble ĉiuj rilataj libroj estu malfermitaj.

1. Elektu la gamon da datumoj, kiujn vi volas translokigi al alia libro. Dekstre alklaku ĝin kaj elektu la pozicion en la menuo, kiu malfermiĝas. Kopii.



2. Poste ni translokiĝas al la libro, en kiu necesos enmeti ĉi tiujn datumojn. Elektu la deziratan gamon. Dekstra alklako. En la kunteksta menuo en la grupo Enmetu Eblojn elektebla ero Alglui Ligilon.



3. Post tio, la valoroj estos enmetitaj. Kiam datumoj en la fontlibra laboro ŝanĝiĝas, la tabela tabelo de la laborlibro aŭtomate streĉos ĝin. Cetere, ne necesas, ke ambaŭ libroj estu malfermitaj por ĉi tio. Sufiĉas malfermi nur unu laborlibron, kaj ĝi aŭtomate ĉerpos datumojn de fermita ligita dokumento se antaŭe estis faritaj ŝanĝoj.

Sed oni devas rimarki, ke en ĉi tiu kazo la enmeto fariĝos kiel senmova tabelo. Kiam vi provas ŝanĝi ajnan ĉelon kun la enmetitaj datumoj, mesaĝo aperos informante vin, ke estas neeble fari ĉi tion.

Ŝanĝoj en tia tabelo asociitaj kun alia libro eblas nur rompante la ligon.

Intervalo inter tabloj

Fojfoje vi devas rompi la rilaton inter tablaj gamoj. La kialo por ĉi tio povas esti aŭ la supre priskribita kazo, kiam vi bezonas ŝanĝi tabelon enmetitan de alia libro, aŭ simple la ruzecon de la uzanto, ke la datumoj en unu tabelo aŭtomate ĝisdatigas de alia.

Metodo 1: rompi la rilaton inter libroj

Vi povas rompi la rilaton inter libroj en ĉiuj ĉeloj per plenumado de preskaŭ unu operacio. Samtempe la datumoj en la ĉeloj restos, sed ili jam estos senmovaj ĝisdatigeblaj valoroj, kiuj neniel dependas de aliaj dokumentoj.

1. En la libro, en kiu eltiriĝas valoroj de aliaj dosieroj, iru al la langeto "Datumoj". Alklaku la ikonon "Ŝanĝi Komunikadojn"situanta sur la rubando en la ilobreto Ligoj. Oni devas rimarki, ke se la nuna libro ne enhavas ligojn al aliaj dosieroj, ĉi tiu butono estas neaktiva.



2. La fenestro por ŝanĝi ligojn estas lanĉita. Ni elektas la dosieron kun kiu ni volas rompi la konekton el la listo de rilataj libroj (se estas pluraj). Alklaku la butonon Rompu la ligon.



3. Informfenestro malfermiĝas, en kiu aperas avizo pri sekvoj de pluaj agoj. Se vi certas, kion vi faros, tiam alklaku la butonon "Rompaj ligoj".



4. Post tio, ĉiuj ligoj al la specifita dosiero en la aktuala dokumento estos anstataŭigitaj per statikaj valoroj.

Metodo 2: Enmetu Valorojn

Sed la supra metodo taŭgas nur se vi bezonas tute rompi ĉiujn ligojn inter la du libroj. Kion fari, se vi bezonas apartigi rilatajn tabelojn, kiuj estas en la sama dosiero? Vi povas fari tion kopiante la datumojn kaj poste algluante ĝin en la sama loko kiel la valoroj. Parenteze, vi sammaniere povas rompi la rilaton inter la individuaj datumoj de diversaj libroj sen rompi la ĝeneralan rilaton inter la dosieroj. Ni vidu kiel funkcias ĉi tiu metodo en la praktiko.

1. Elektu la gamon, en kiu ni volas forigi la ligon al alia tablo. Ni alklakas ĝin per la dekstra musbutono. En la menuo, kiu malfermiĝas, elektu Kopii. Anstataŭ ĉi tiuj agoj, vi povas tajpi alternativan kombinaĵon de varmaj klavoj Ctrl + C.



2. Plue, sen forigi la elekton el la sama fragmento, ree alklaku ĝin. Ĉi-foje en la listo de agoj, alklaku la ikonon "Valoroj"kiu situas en la ilo-grupo Enmetu Eblojn.



3. Post tio, ĉiuj ligoj en la elektita gamo estos anstataŭigitaj per statikaj valoroj.

Kiel vi povas vidi, en Excel ekzistas manieroj kaj iloj por ligi plurajn tablojn kune. Samtempe tabulaj datumoj povas esti sur aliaj folioj kaj eĉ en malsamaj libroj. Se necese, ĉi tiu rilato facile rompiĝas.