Ki jan yo friz yon selil nan yon fòmil Excel

Souvan, itilizatè yo fè fas ak nesesite pou pin yon selil nan yon fòmil. Pou egzanp, li rive nan sitiyasyon kote ou vle kopye yon fòmil, men se konsa ke lyen an pa deplase monte ak desann menm kantite selil kòm li te kopye soti nan kote orijinal li.

Nan ka sa a, ou ka ranje referans selil la nan Excel. Lè sa a ka fè nan plizyè fason an menm tan. Ann pran yon gade pi pre sou fason pou reyalize objektif sa a.

Ki sa ki se yon lyen Excel

Fèy la konpoze de selil yo. Chak nan yo gen enfòmasyon espesifik. Lòt selil ka itilize li nan kalkil. Men, ki jan yo konprann ki kote yo ka resevwa done yo soti nan? Li ede yo fè lyen.

Chak lyen deziyen yon selil ki gen yon lèt ak yon nimewo. Yon lèt reprezante yon kolòn ak yon nimewo reprezante yon ranje. 

Gen twa kalite lyen: absoli, relatif ak melanje. Dezyèm lan mete pa default. Yon referans absoli se youn ki gen yon adrès fiks nan tou de yon kolòn ak yon kolòn. An konsekans, melanje se youn nan kote swa yon kolòn separe oswa yon ranje fiks.

1 metòd la

Pou konsève tou de adrès kolòn ak ranje, swiv etap sa yo:

1. Klike sou selil ki gen fòmil la.
2. Klike sou ba fòmil la pou selil nou bezwen an.
3. Peze F4.

Kòm yon konsekans, referans selil la pral chanje nan absoli. Li ka rekonèt pa siy nan dola karakteristik. Pa egzanp, si w klike sou selil B2 epi klike sou F4, lyen an ap parèt tankou sa a: $B$2.

Kisa siy dola a vle di anvan pati adrès la pou chak selil?

1. Si yo mete l devan yon lèt, li endike referans kolòn nan rete menm jan an, kèlkeswa kote yo te deplase fòmil la.
2. Si siy dola a devan nimewo a, li endike ke kòd la kole. 

2 metòd la

Metòd sa a se prèske menm jan ak yon sèl anvan an, sèlman ou bezwen peze F4 de fwa. pa egzanp, si nou te gen selil B2, apre sa l ap vin B$2. Nan mo senp, nan fason sa a nou jere ranje liy lan. Nan ka sa a, lèt la nan kolòn nan pral chanje.

Li trè pratik, pou egzanp, nan tab kote ou bezwen montre sa ki nan dezyèm selil la soti nan tèt la nan selil anba a. Olye pou w fè yon fòmil konsa anpil fwa, li ase pou ranje ranje a epi kite kolòn nan chanje.

3 metòd la

Sa a se konplètman menm jan ak metòd anvan an, sèlman ou bezwen peze kle a F4 twa fwa. Lè sa a, sèlman referans nan kolòn nan pral absoli, ak ranje a ap rete fiks.

4 metòd la

Sipoze nou gen yon referans absoli nan yon selil, men isit la li te nesesè yo fè li relatif. Pou fè sa, peze kle F4 la anpil fwa ke pa gen okenn siy $ nan lyen an. Lè sa a, li pral vin relatif, epi lè ou deplase oswa kopye fòmil la, tou de adrès kolòn nan ak adrès ranje a pral chanje.

Epingle selil pou yon seri gwo

Nou wè ke metòd ki anwo yo pa prezante okenn difikilte pou fè. Men, travay yo espesifik. Epi, pou egzanp, ki sa yo dwe fè si nou gen plizyè douzèn fòmil nan yon fwa, lyen yo nan ki bezwen yo dwe tounen nan yon sèl absoli. 

Malerezman, metòd Excel estanda pa pral reyalize objektif sa a. Pou fè sa, ou bezwen sèvi ak yon addon espesyal ki rele VBA-Excel. Li gen anpil karakteristik adisyonèl ki pèmèt ou fè travay komen ak Excel pi vit.

Li gen ladann plis pase yon santèn fonksyon itilizatè-defini ak 25 makro diferan, epi li se tou mete ajou regilyèman. Li pèmèt ou amelyore travay ak prèske nenpòt aspè:

1. Selil yo.
2. Macro
3. Fonksyon diferan kalite.
4. Lyen ak etalaj.

An patikilye, ajoute sa a pèmèt ou ranje lyen nan yon gwo kantite fòmil nan yon fwa. Pou fè sa, ou dwe fè etap sa yo:

1. Chwazi yon seri.
2. Louvri tab la VBA-Excel ki pral parèt apre enstalasyon an. 
3. Louvri meni "Fonksyon", kote opsyon "Fòmil fèmen" yo ye.
   

   

4. Apre sa, yon bwat dyalòg ap parèt kote ou bezwen presize paramèt ki nesesè yo. Addon sa a pèmèt ou mete yon kolòn ak yon kolòn separeman, ansanm, epi tou li retire yon pinning ki deja egziste ak yon pake. Apre yo fin chwazi paramèt ki nesesè yo lè l sèvi avèk bouton radyo ki koresponn lan, ou bezwen konfime aksyon ou pa klike "OK".

Egzanp

Annou pran en legzanp pou fer li pli kler. Ann di nou gen enfòmasyon ki dekri pri machandiz yo, kantite total li yo ak revni lavant yo. Epi nou ap fè fas ak travay la nan fè tab la, ki baze sou kantite a ak pri, otomatikman detèmine konbyen lajan nou jere yo touche san yo pa dedwi pèt.

Nan egzanp nou an, pou sa ou bezwen antre nan fòmil =B2 * C2. Li se byen senp, jan ou ka wè. Li trè fasil pou itilize egzanp li a pou dekri kijan ou ka ranje adrès yon selil oswa kolòn oswa ranje endividyèl li yo. 

Natirèlman, nan egzanp sa a, ou ka eseye trennen fòmil la desann lè l sèvi avèk makè autofill la, men nan ka sa a, selil yo pral otomatikman chanje. Se konsa, nan selil D3 pral gen yon lòt fòmil, kote nimewo yo pral ranplase, respektivman, pa 3. Anplis de sa, dapre konplo a - D4 - fòmil la pral pran fòm nan = B4 * C4, D5 - menm jan an, men ak la. nimewo 5 ak sou sa.

Si li nesesè (nan pifò ka yo li vire soti), Lè sa a, pa gen okenn pwoblèm. Men, si ou bezwen ranje fòmil la nan yon sèl selil pou ke li pa chanje lè trenen, Lè sa a, sa a pral yon ti jan pi difisil. 

Sipoze nou bezwen detèmine revni an dola. Ann mete l nan selil B7. Ann jwenn yon ti kras nostaljik epi endike pri a nan 35 rubles pou chak dola. An konsekans, yo nan lòd yo detèmine revni an nan dola, li nesesè divize kantite lajan an nan rubles pa echanj pousantaj lajan an.

Men sa li sanble nan egzanp nou an.

Si nou menm, menm jan ak vèsyon anvan an, eseye preskri yon fòmil, Lè sa a, nou pral echwe. Menm jan an tou, fòmil la pral chanje nan yon sèl ki apwopriye a. Nan egzanp nou an, li pral tankou sa a: =E3*B8. Soti isit la nou ka wè. ke premye pati nan fòmil la te tounen E3, epi nou mete tèt nou travay sa a, men nou pa bezwen chanje dezyèm pati nan fòmil la nan B8. Se poutèt sa, nou bezwen vire referans a nan yon sèl absoli. Ou ka fè sa san yo pa peze kle F4 la, jis pa mete yon siy dola.

Apre nou te vire referans a dezyèm selil la nan yon sèl absoli, li te vin pwoteje kont chanjman. Koulye a, ou ka san danje trennen li lè l sèvi avèk manch otofill la. Tout done fiks yo ap rete menm jan an, kèlkeswa pozisyon fòmil la, epi done ki pa angaje yo pral chanje yon fason fleksib. Nan tout selil yo, revni an rubles ki dekri nan liy sa a pral divize pa menm to echanj dola a.

Fòmil nan tèt li pral sanble sa a:

=D2/$B$7

Attention! Nou te endike de siy dola. Nan fason sa a, nou montre pwogram nan ke tou de kolòn nan ak ranje a bezwen yo dwe fiks.

Referans selilè nan makro

Yon makro se yon sousroutine ki pèmèt ou otomatize aksyon yo. Kontrèman ak fonksyonalite estanda nan Excel, yon macro pèmèt ou imedyatman mete yon selil espesifik ak fè sèten aksyon nan jis kèk liy nan kòd. Itil pou trete enfòmasyon pakèt, pou egzanp, si pa gen okenn fason yo enstale ajoute-ons (pa egzanp, yo itilize yon òdinatè konpayi, pa yon sèl pèsonèl).

Premye ou bezwen konprann ke konsèp kle nan yon macro se objè ki ka gen lòt objè. Objè Workbooks la responsab liv elektwonik la (ki vle di dokiman an). Li gen ladann objè a Sheets, ki se yon koleksyon tout fèy nan yon dokiman ouvè. 

An konsekans, selil yo se yon objè Cells. Li gen tout selil yo nan yon fèy patikilye.

Chak objè kalifye ak agiman parantèz. Nan ka selil yo, yo refere yo nan lòd sa a. Nimewo ranje a ki nan lis premye, ki te swiv pa nimewo kolòn oswa lèt (tou de fòma yo akseptab).

Pou egzanp, yon liy kòd ki gen yon referans a selil C5 ta sanble sa a:

Workbooks ("Liv2.xlsm"). Fèy ("Lis2"). Selil (5, 3)

Workbooks ("Liv2.xlsm"). Fèy ("Lis2"). Selil (5, "C")

Ou kapab tou jwenn aksè nan yon selil lè l sèvi avèk yon objè Tidy. An jeneral, li gen entansyon bay yon referans a yon seri (ki gen eleman, nan chemen an, kapab tou absoli oswa relatif), men ou ka tou senpleman bay yon non selil, nan menm fòma ak nan yon dokiman Excel.

Nan ka sa a, liy lan pral sanble ak sa a.

Workbooks ("Liv2.xlsm"). Fèy ("Lis2"). Ranje ("C5")

Li ka sanble ke opsyon sa a pi pratik, men avantaj nan de premye opsyon yo se ke ou ka itilize varyab nan parantèz epi bay yon lyen ki pa absoli ankò, men yon bagay tankou yon sèl relatif, ki pral depann de rezilta yo nan. kalkil yo.

Kidonk, macros ka efektivman itilize nan pwogram yo. An reyalite, tout referans sou selil oswa chenn isit la yo pral absoli, ak Se poutèt sa yo ka fiks ak yo tou. Se vre, li pa tèlman pratik. Itilizasyon makro ka itil lè w ap ekri pwogram konplèks ak yon gwo kantite etap nan algorithm la. An jeneral, fason estanda pou itilize referans absoli oswa relatif se pi plis pratik. 

Konklizyon

Nou kalkile ki sa yon referans selil se, ki jan li fonksyone, ki sa li ye. Nou te konprann diferans ki genyen ant referans absoli ak relatif epi kalkile kisa ki dwe fè yo nan lòd yo vire yon kalite nan yon lòt (nan mo senp, ranje adrès la oswa retire li). Nou kalkile ki jan ou ka fè li imedyatman ak yon gwo kantite valè. Koulye a, ou gen fleksibilite pou itilize karakteristik sa a nan bon sitiyasyon.