Travay ak tab lye nan Microsoft Excel

Lè w ap fè sèten travay nan Excel, pafwa ou gen pou fè fas ak plizyè tab, ki gen rapò tou ak youn ak lòt. Sa vle di, done yo soti nan yon tab rale nan lòt la, epi lè yo chanje, valè yo nan tout chenn tab ki asosye yo rekalkile.

Tab ki lye yo trè itil pou trete gwo kantite enfòmasyon. Li pa trè pratik yo gen tout enfòmasyon ki nan yon sèl tablo, epi si li pa omojèn. Li difisil pou w travay ak objè sa yo epi fouye yo. Pwoblèm sa a fèt pou elimine tab ki gen rapò, enfòmasyon ki distribiye, men an menm tan an relye. Chenn tab lye ka lokalize pa sèlman nan yon sèl fèy oswa yon liv, men tou ki sitiye nan liv separe (dosye). Nan pratik, dènye de opsyon yo yo te itilize pi souvan, depi objektif la nan teknoloji sa a se jwenn lwen akimile nan done yo, ak anpile yo sou menm paj la pa fondamantalman rezoud pwoblèm nan. Se pou nou aprann ki jan yo kreye ak ki jan yo travay avèk sa a kalite jesyon done.

Kreye tab lye

Premye a tout, kite a rete sou kesyon an nan ki jan li se posib yo kreye yon lyen ant diferan tab tab.

Metòd 1: Dirèkteman ki lye ak tab yon fòmil

Fason ki pi fasil yo lye done se sèvi ak fòmil ki genyen lyen ki nan lòt tab tab. Yo rele sa dirèk obligatwa. Metòd sa a se entwisyon, depi ak li se obligatwa a fè nan prèske menm jan an kòm kreye referans a done nan yon etalaj tab sèl.

Se pou nou wè ki jan yon egzanp ka fòme yon kosyon pa obligatwa dirèk. Nou gen de tab sou de dra. Nan yon sèl tablo, se pewòl kalkile lè l sèvi avèk yon fòmil pa miltipliye to travayè yo pa yon to sèl pou tout moun.

Sou dezyèm fèy papye a gen yon seri tabulè kote gen yon lis anplwaye ki gen salè yo. Se lis la nan anplwaye nan tou de ka prezante nan lòd la menm.

Li nesesè pou fè pou done yo sou pousantaj nan dezyèm fèy la rale moute nan selil ki koresponn nan premye la.

1. Sou premye fèy la, chwazi premye selil kolòn yo. "Parye". Nou mete nan make li "=". Apre sa, klike sou etikèt la "Fèy 2"Ki sitiye sou bò gòch nan koòdone nan Excel pi wo a ba a sitiyasyon.



2. Deplase nan dezyèm zòn nan nan dokiman an. Klike sou premye selil ki nan kolòn nan. "Parye". Lè sa a, klike sou bouton an. Antre sou klavye a fè done antre nan selil la nan ki te siyen an deja mete egal.



3. Lè sa a, gen yon tranzisyon otomatik nan premye fèy la. Kòm ou ka wè, se pousantaj la nan premye anplwaye a soti nan dezyèm tab la rale nan selil ki apwopriye a. Èske w gen mete kurseur a sou selil la ki gen parye a, nou wè ke se fòmil abityèl la itilize yo montre done sou ekran an. Men, anvan kowòdone selil la kote done yo parèt, gen yon ekspresyon "Sheet2!"ki endike non zòn nan nan dokiman an kote yo ye. Fòmil jeneral la nan ka nou an se jan sa a:

   = Sheet2! B2



4. Koulye a, ou bezwen transfere done yo sou pousantaj yo nan tout lòt anplwaye nan antrepriz la. Natirèlman, sa a ka fè nan menm fason an ke nou akonpli travay la pou premye anplwaye a, men bay sa tou de lis nan anplwaye yo ranje nan lòd la menm, yo ka travay la siyifikativman senplifye ak pi vit solisyon li yo. Sa a ka fè pa senpleman kopye fòmil la nan ranje ki anba a. Akòz lefèt ke lyen nan Excel yo par defo, lè yo kopye, chanjman valè yo, ki se sa nou bezwen. Ka pwosedi a kopye tèt li dwe fè lè l sèvi avèk makè a ranpli.

   Se konsa, mete kurseur a nan zòn nan pi ba dwa nan eleman an ak fòmil la. Apre sa, kurseur a ta dwe konvèti nan yon ranpli nan fòm lan nan yon kwa nwa. Nou fè kranpon a nan bouton sourit gòch la ak trennen kurseur a nan pati anba a anpil nan kolòn nan.



5. Tout done ki sòti nan kolòn nan menm sou Fèy 2 yo te rale sou tab la Fèy 1. Lè done chanje nan Fèy 2 yo pral chanje otomatikman sou premye a.

Metòd 2: sèvi ak yon pakèt moun sou operatè INDEX - MATCH

Men, sa ki si lis la nan anplwaye yo nan ranje tabulèr pa ranje nan lòd la menm? Nan ka sa a, kòm mansyone pi bonè, youn nan opsyon yo se mete kanpe koneksyon ki genyen ant chak nan selil sa yo ki ta dwe lye manyèlman. Men, sa a se apwopriye sèlman pou ti tab. Pou chenn masiv, opsyon sa a, nan pi bon, pral pran yon anpil tan aplike, ak nan pi move - nan pratik li pa pral posib nan tout. Men, ou ka rezoud pwoblèm sa a avèk yon pakèt operatè yo Endèks - MATCH. Ann wè ki jan sa ka fèt pa ki lye ak done nan chenn tabulèr, ki te diskite nan metòd la anvan yo.

1. Chwazi premye atik la nan kolòn nan. "Parye". Ale nan Fonksyon Sòsyeklike sou icon a "Antre fonksyon".



2. Nan Fonksyon sòsye nan yon gwoup "Lyen ak ranje" jwenn epi chwazi non an Endèks.



3. Operatè sa a gen de fòm: yon fòm pou travay ak ranje ak referans. Nan ka nou an, se premye opsyon ki nesesè, se konsa nan pwochen fenèt la pou chwazi yon fòm ki pral louvri, nou chwazi li epi klike sou bouton an. "OK".



4. Fenèt la agiman operatè yo te kouri. Endèks. Travay la nan fonksyon an espesifye se montre valè a ki nan seri a chwazi nan liy la ak nimewo ki espesifye. Fòmil jeneral operatè Endèks se sa a:

   = INDEX (etalaj; nimewo_linye; [kantite kolòn])

   "Array" - agiman ki gen adrès la nan ranje ki soti nan ki nou pral ekstrè enfòmasyon pa kantite fisèl la espesifye.

   "Nimewo liy lan" - agiman an ki se nimewo sa a liy tèt li. Li enpòtan konnen ke nimewo liy lan pa ta dwe espesifye relatif nan tout dokiman an, men se sèlman relatif nan etalaj la chwazi.

   "Nimewo kolòn" - Agiman an se si ou vle. Pou rezoud pwoblèm nou an espesyalman, nou pa pral itilize li, ak Se poutèt sa li pa nesesè yo dekri sans li yo separeman.

   Mete kurseur a nan jaden an "Array". Apre sa ale nan Fèy 2 epi, kenbe bouton sourit gòch la, chwazi tout sa ki nan kolòn nan "Parye".



5. Apre kowòdone yo ap parèt nan fenèt la operatè, mete kurseur la nan jaden an "Nimewo liy lan". Nou pral montre agiman sa a lè l sèvi avèk operatè a MATCH. Se poutèt sa, klike sou triyang lan ki sitiye sou bò gòch fonksyon an liy. Yon lis operatè dènyèman te itilize ouvè. Si ou jwenn nan mitan yo non an "MATCH"Lè sa a, ou ka klike sou li. Sinon, klike sou atik ki pi resan nan lis la - "Lòt karakteristik ...".



6. Fenèt estanda a kòmanse. Fonksyon an mèt. Ale nan li nan menm gwoup la. "Lyen ak ranje". Fwa sa a, nan lis la, chwazi atik la "MATCH". Fè yon klike sou bouton an. "OK".



7. Aktive agiman fenèt operatè MATCH. Se fonksyon an espesifye gen entansyon montre nimewo a nan yon valè nan yon etalaj espesifik pa non li yo. Gras ak opòtinite sa a, nou pral kalkile kantite ranje yon valè espesifik pou fonksyon an. Endèks. Sentaks MATCH prezante kòm:

   = MATCH (valè rechèch; matyè filtraj; [match_type])

   "Valè chache" - agiman ki gen non oswa adrès selil ranje twazyèm pati kote li ye a. Li se pozisyon non sa a nan seri sib la ki ta dwe kalkile. Nan ka nou an, premye agiman an pral selil referans a Fèy 1nan ki yo sitiye non yo nan anplwaye yo.

   "Wè etalaj" - yon diskisyon ki reprezante yon lyen nan yon etalaj nan ki valè a espesifye se fouye pou detèmine pozisyon li yo. Nou pral jwe kolòn adrès wòl sa a "Premye non sou Fèy 2.

   "Kat Kalite" - yon diskisyon ki si ou vle, men, kontrèman ak deklarasyon an anvan, nou pral bezwen sa a agiman si ou vle. Li endike kijan operatè a ap matche ak valè a vle ak etalaj la. Agiman sa a ka gen youn nan twa valè yo: -1; 0; 1. Pou ranje non, chwazi opsyon an "0". Opsyon sa a apwopriye pou ka nou an.

   Se konsa, kite a kòmanse ranpli nan jaden yo nan fenèt la agiman. Mete kurseur a nan jaden an "Valè chache", klike sou premye selil kolòn nan "Non" sou Fèy 1.



8. Apre kowòdone yo ap parèt, mete kurseur la nan jaden an "Wè etalaj" epi ale sou chemen kout la "Fèy 2"ki sitiye nan fon fenèt Excel pi wo a ba estati a. Kenbe desann bouton sourit la ak mete aksan sou tout selil ki nan kolòn nan. "Non".



9. Apre kowòdone yo ap parèt nan jaden an "Wè etalaj"ale nan jaden an "Kat Kalite" ak mete nimewo a soti nan klavye la "0". Apre sa, nou retounen nan jaden an ankò. "Wè etalaj". Reyalite a se ke nou pral kopye fòmil la, jan nou te fè nan metòd anvan an. Pral gen yon konpanse nan adrès, men nou bezwen ranje kowòdone yo nan etalaj la yo te wè yo. Li pa ta dwe chanje. Chwazi kowòdone yo nan kurseur a, epi klike sou kle fonksyon an F4. Kòm ou ka wè, yon siy dola parèt devan kowòdone yo, ki vle di ke lyen ki soti nan fanmi te vin absoli. Lè sa a, klike sou bouton an "OK".



10. Rezilta a parèt nan premye selil kolòn nan. "Parye". Men, anvan kopye, nou bezwen ranje yon lòt zòn, sètadi agiman an premye nan fonksyon an Endèks. Pou fè sa, chwazi eleman kolòn ki gen fòmil la, epi ale nan ba fòmil la. Chwazi agiman an premye nan operatè a Endèks (B2: B7) epi klike sou bouton an F4. Kòm ou ka wè, siy dola a parèt tou pre chwazi kowòdone yo. Klike sou bouton an Antre. An jeneral, fòmil la te pran fòm sa a:

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



11. Koulye a, ou ka kopye lè l sèvi avèk makè a ranpli. Rele li nan menm fason an ke nou te pale de pi bonè, ak detire li nan fen seri a tab.



12. Kòm ou ka wè, malgre lefèt ke lòd la nan ranje yo nan de tab ki gen rapò yo pa matche ak, sepandan, tout valè yo ap sere boulon dapre non yo nan travayè yo. Sa a te reyalize nan sèvi ak yon konbinezon de operatè yo Endèks-MATCH.

Gade tou:
Excel fonksyon INDEX
Fonksyon matche ak nan Excel

Metòd 3: Egzekite Operasyon matematik ak done ki asosye yo

Done dirèk obligatwa tou bon nan ke li pèmèt pa sèlman yo montre valè ki parèt nan lòt chenn tab nan youn nan tablo yo, men tou fè divès kalite operasyon matematik avèk yo (adisyon, divizyon, soustraksyon, miltiplikasyon, elatriye).

Ann wè ki jan sa fèt an pratik. Ann fè sa Fèy 3 done salè jeneral antrepriz yo ap parèt san pann anplwaye. Pou sa, pousantaj anplwaye yo pral rale soti nan Fèy 2, rezime (lè l sèvi avèk fonksyon an SOM) ak miltipliye pa koyefisyan an lè l sèvi avèk fòmil la.

1. Chwazi selil la kote ou pral parèt sou pewòl total Fèy 3. Klike sou bouton an "Antre fonksyon".



2. Li ta dwe lanse fenèt la Fonksyon an mèt. Ale nan gwoup la "Matematik" epi chwazi non an la "SOM". Apre sa, klike sou bouton an "OK".



3. Deplase ou nan fenèt la agiman fonksyon SOMki fèt pou kalkile sòm chif yo. Li gen sentaks sa yo:

   = SUM (number1; number2; ...)

   Jaden yo nan fenèt la koresponn ak agiman yo nan fonksyon an espesifye. Malgre ke nimewo yo ka rive jwenn 255 moso, pou bi nou an sèlman yon sèl ap sifi. Mete kurseur a nan jaden an "Number1". Klike sou etikèt la "Fèy 2" pi wo a ba a sitiyasyon.



4. Apre nou te deplase nan seksyon an vle nan liv la, chwazi kolòn nan ki ta dwe adisyone. Nou fè li kurseur a, kenbe bouton an sourit gòch. Kòm ou ka wè, kowòdone yo nan zòn nan chwazi yo imedyatman parèt nan jaden an nan fenèt la agiman. Lè sa a, klike sou bouton an. "OK".



5. Apre sa, nou otomatikman deplase nan Fèy 1. Kòm ou ka wè, se kantite lajan total nan pousantaj yo salè nan travayè deja parèt nan eleman ki koresponn lan.



6. Men, sa a se pa tout. Kòm nou sonje, se salè a kalkile nan miltipliye valè a nan pousantaj la pa koyefisyan an. Se poutèt sa, nou ankò chwazi selil la nan ki valè a adisyone sitiye. Apre sa ale nan ba fòmil la. Nou ajoute yon siy miltiplikasyon nan fòmil li yo (*), ak Lè sa a, klike sou eleman nan ki se koyefisyan an ki sitiye. Fè kalkil la klike sou Antre sou klavye a. Kòm ou ka wè, pwogram nan kalkile salè total pou antrepriz la.



7. Tounen nan Fèy 2 ak chanje gwosè a nan pousantaj la nan nenpòt ki anplwaye.



8. Apre sa, ankò deplase ou nan paj la ak kantite lajan total. Kòm ou ka wè, akòz chanjman nan tablo a ki gen rapò, yo te rezilta a nan salè total otomatikman rekalkile.

Metòd 4: insert espesyal

Ou kapab tou lyen ranje tab nan Excel ak yon insert espesyal.

1. Chwazi valè yo ki bezwen yo dwe "sere boulon" nan yon lòt tab. Nan ka nou an, sa a se ranje a kolòn. "Parye" sou Fèy 2. Klike sou fragman chwazi a avèk bouton sourit dwat. Nan lis la ki ouvè, chwazi atik la "Kopi". Altènatif konbinezon kle se Ctrl + C. Apre sa, deplase nan Fèy 1.



2. Deplase nan zòn nan vle nan liv la, nou chwazi selil yo nan kote ou vle rale valè yo. Nan ka nou an, sa a se yon kolòn. "Parye". Klike sou fragman chwazi a avèk bouton sourit dwat. Nan meni an kontèks nan ba ikòn nan "Opsyon ensèsyon" klike sou icon a "Antre Lyen".

   

   Genyen tou yon altènatif. By wout la, li se youn nan sèlman pou vèsyon ki pi gran nan Excel. Nan meni an kontèks, deplase kurseur la nan atik la "Kole espesyal". Nan meni an plis ki ouvè, chwazi atik la ki gen menm non an.



3. Apre sa, yon fenèt insert espesyal ouvè. Nou peze bouton an "Antre Lyen" nan kwen gòch selil la.



4. Kèlkeswa sa ki opsyon ou chwazi, valè ki soti nan yon etalaj tab yo pral figire nan yon lòt. Lè ou chanje done yo nan sous la, yo pral tou otomatikman chanje nan seri a eleman.

Leson: kole espesyal nan Excel

Metòd 5: relasyon ant tab nan plizyè liv

Anplis de sa, ou ka òganize koneksyon ki genyen ant tablespaces nan diferan liv. Sa a itilize zouti insert espesyal la. Aksyon yo pral absoliman menm jan ak sa yo ke nou konsidere kòm nan metòd anvan an, eksepte ke navigasyon pandan entwodiksyon nan fòmil yo pa pral gen rive ant zòn nan yon sèl liv, men ant dosye. Natirèlman, tout liv ki gen rapò yo ta dwe louvri.

1. Chwazi seri a nan done ke ou vle transfere nan yon lòt liv. Klike sou li ak bouton an sourit dwa epi chwazi pozisyon an nan meni an ki ouvè "Kopi".



2. Lè sa a, nou deplase nan liv la nan ki done sa a ap bezwen yo pral figire. Chwazi ranje a vle. Klike sou bouton sourit dwat. Nan meni an kontèks nan gwoup la "Opsyon ensèsyon" chwazi yon atik "Antre Lyen".



3. Apre sa, valè yo pral antre. Lè ou chanje done yo nan liv la sous, etalaj la tabulèr soti nan workbook a pral rale yo moute otomatikman. Epi li pa nesesè pou toude liv yo louvri pou sa. Li se ase yo louvri sèlman yon liv gid, epi li pral otomatikman rale nan done yo soti nan dokiman an fèmen lye, si chanjman yo te deja te fè nan li.

Men, li ta dwe te note ke nan ka sa a ensèsyon an ap fèt nan fòm lan nan yon etalaj imuiabl. Si ou eseye chanje nenpòt selil ak done eleman, yon mesaj pral pòp moute enfòme ou ke li pa posib fè sa.

Chanjman nan tankou yon etalaj ki asosye ak yon lòt liv kapab fèt sèlman fèt pa kraze lyen an.

Dekoneksyon ant tab

Pafwa li nesesè kraze lyen ki genyen ant tab tablo yo. Rezon ki fè la pou sa a kapab, kòm ka a dekri anwo a, lè ou vle chanje yon etalaj eleman soti nan yon lòt liv, oswa tou senpleman paske itilizatè a pa vle done yo nan yon sèl tablo yo dwe mete ajou otomatikman soti nan yon lòt.

Metòd 1: dekonekte ant liv

Ou ka kraze koneksyon ki genyen ant liv nan tout selil pa fè nòmalman yon sèl operasyon. An menm tan an, done yo nan selil yo ap rete, men yo pral deja estatik ki pa mete ajou valè ki pa depann sou lòt dokiman yo.

1. Nan liv la, nan ki valè ki soti nan lòt dosye yo rale, ale nan tab la "Done". Klike sou icon a "Edit lyen"ki sitiye sou kasèt la nan blòk zouti yo "Koneksyon". Li ta dwe note ke si liv aktyèl la pa gen lyen ki mennen nan lòt dosye, bouton sa a se inaktif.



2. Se fenèt la pou chanje lyen te lanse. Chwazi nan lis la nan liv ki gen rapò (si gen plizyè) dosye a ak ki nou vle kraze koneksyon an. Klike sou bouton an "Kase lyen an".



3. Yon fenèt enfòmasyon ouvè, nan ki gen yon avètisman sou konsekans yo nan aksyon plis ... Si ou se asire w ke nan sa ou pral fè, Lè sa a, klike sou bouton an. "Lyen kase".



4. Apre sa, tout referans sou dosye espesifye nan dokiman aktyèl la pral ranplase ak valè estatik.

Metòd 2: Mete Valè yo

Men, metòd ki anwo la a apwopriye sèlman si ou bezwen konplètman separe tout lyen yo ant de liv yo. Ki sa ki fè si ou vle dekonekte tab ki gen rapò ak ki nan menm dosye a? Ou ka fè sa pa kopye done yo, epi kole l nan menm kote ak valè yo.By wout la, ka metòd la menm dwe itilize yo kraze koneksyon ki genyen ant chenn done apa nan liv diferan san yo pa kraze koneksyon an jeneral ant dosye. Ann wè ki jan metòd sa a ap travay an pratik.

1. Chwazi ranje a nan ki nou vle retire lyen ki nan yon lòt tab. Klike sou li ak bouton an sourit dwa. Nan meni ki ouvè, chwazi atik la "Kopi". Olye pou yo aksyon sa yo, ou ka tape yon altènatif cho konbinezon kle. Ctrl + C.



2. Lè sa a, san yo pa retire seleksyon an soti nan menm fragman an, nou ankò klike sou li ak bouton an sourit dwa. Fwa sa a, nan lis aksyon nou klike sou icon a "Valè"ki plase nan yon gwoup zouti "Opsyon ensèsyon".



3. Apre sa, tout lyen nan seri a chwazi yo pral ranplase ak valè estatik.

Kòm ou ka wè, Excel gen metòd ak zouti lyen plizyè tab yo ansanm. Nan ka sa a, done yo tabule ka sou lòt dra ak menm nan diferan liv. Si sa nesesè, koneksyon sa a ka fasil kase.