ഇന്നത്തെ വിഷയം: #11 – Relative References (Module 2, Cycle 1: main teaching article)
ഒരു ഫോർമുല ഒരു തവണ മാത്രം എഴുതി 500 വരികളിലേക്ക് കോപ്പി ചെയ്താൽ പോലും ഓരോ വരിയും അതിന്റേതായ റിസൾട്ട് കണക്കുകൂട്ടുന്നു. ഇതാണ് Relative References ചെയ്യുന്ന ജോലി; മിക്കവാറും എല്ലാ Analysis വർക്ക്ഷീറ്റുകളും ഇതിനെയാണ് ആശ്രയിക്കുന്നത്. അവ എങ്ങനെയാണ് മാറുന്നതെന്ന് മനസ്സിലാക്കിയാൽ, കോപ്പി ചെയ്ത ഫോർമുലയെ എപ്പോൾ വിശ്വസിക്കാമെന്ന് നിങ്ങൾക്ക് കൃത്യമായി അറിയാൻ സാധിക്കും.
എന്താണ് Relative Reference?
B2 പോലുള്ള ഒരു Cell Reference ബൈ ഡിഫോൾട്ടായി (by default) റിലേറ്റീവ് ആണ്. Excel യഥാർത്ഥത്തിൽ “B2 എന്ന സെൽ” എന്നല്ല സൂക്ഷിക്കുന്നത്. ഫോർമുലയുള്ള സെല്ലിനെ ആപേക്ഷികമായുള്ള സ്ഥാനമാണ് (relative position) അത് സ്റ്റോർ ചെയ്യുന്നത്. ഉദാഹരണത്തിന്, “ഇടത്തോട്ട് മൂന്ന് കോളങ്ങൾ, അതേ വരിയിൽത്തന്നെ” എന്നത് പോലെ.
അതുകൊണ്ട് ഫോർമുല മറ്റൊരു സെല്ലിലേക്ക് കോപ്പി ചെയ്യുമ്പോൾ, അതേ എണ്ണം വരികളും കോളങ്ങളും അനുസരിച്ച് റഫറൻസും മാറുന്നു:
- ഒരു വരി താഴേക്ക് കോപ്പി ചെയ്താൽ: വരികളുടെ സംഖ്യ (Row number) 1 വീതം കൂടുന്നു (
B2എന്നത്B3ആയി മാറുന്നു). - ഒരു കോളം വലത്തോട്ട് കോപ്പി ചെയ്താൽ: കോളത്തിന്റെ അക്ഷരം (Column letter) ഒരെണ്ണം മുന്നോട്ട് മാറുന്നു (
B2എന്നത്C2ആകുന്നു). - രണ്ട് വരി താഴോട്ടും രണ്ട് കോളം വലത്തോട്ടും കോപ്പി ചെയ്താൽ:
A1എന്നത്C3ആയി മാറും.
ഒരു Relative Reference-ൽ ഡോളർ ചിഹ്നങ്ങൾ ($) ഉണ്ടാകില്ല. $ അടങ്ങിയ റഫറൻസുകൾ ($A$1, $A1, അല്ലെങ്കിൽ A$1 പോലെ) Absolute അല്ലെങ്കിൽ Mixed റഫറൻസുകളാണ്. അവ ഈ കോഴ്സിലെ അടുത്ത രണ്ട് വിഷയങ്ങളാണ്.
ഘട്ടം ഘട്ടമായി: Relative References ഉപയോഗിച്ച് ഫോർമുല കോപ്പി ചെയ്യുന്നത് എങ്ങനെ
- ഉത്തരം ആവശ്യമുള്ള ആദ്യത്തെ സെല്ലിൽ ക്ലിക്ക് ചെയ്ത് ഫോർമുല ടൈപ്പ് ചെയ്യുക (ഉദാഹരണത്തിന്
=B2+C2+D2). തുടർന്ന് Enter അമർത്തുക. - ഫോർമുലയുള്ള സെല്ലിൽ വീണ്ടും ക്ലിക്ക് ചെയ്യുക. അതിന്റെ താഴെ വലത് കോണിൽ ഒരു ചെറിയ ചതുരം കാണാം. ഇതാണ് Fill Handle.
- ഫിൽ ചെയ്യേണ്ട സെല്ലുകളിലേക്ക് ഫിൽ ഹാൻഡിൽ താഴേക്കോ വശങ്ങളിലേക്കോ ഡ്രാഗ് ചെയ്ത് വിടുക. ആവശ്യമെങ്കിൽ, എന്ത് കാര്യങ്ങളാണ് കോപ്പി ചെയ്യേണ്ടത് എന്ന് തിരഞ്ഞെടുക്കാൻ ദൃശ്യമാകുന്ന Auto Fill Options ബട്ടൺ ഉപയോഗിക്കാം.
- കീബോർഡ് വഴി: ഫോർമുലയുള്ള സെല്ലും അതിന് താഴെയുള്ള ഒഴിഞ്ഞ സെല്ലുകളും ഒരുമിച്ച് സെലക്ട് ചെയ്ത ശേഷം
Ctrl+D(Fill Down) അമർത്തുക. വലത്തോട്ട് ഫിൽ ചെയ്യാൻ, വരിയിലുടനീളം സെലക്ട് ചെയ്ത്Ctrl+Rഅമർത്തുക. - Ribbon വഴി: സെല്ലുകൾ സെലക്ട് ചെയ്ത് Home > Fill എന്നതിലേക്ക് പോയി Down, Right, Up, അല്ലെങ്കിൽ Left തിരഞ്ഞെടുക്കുക.
ശ്രദ്ധിക്കുക: Fill Handle കാണാൻ കഴിയുന്നില്ലെങ്കിൽ, File > Options > Advanced > Editing options > Enable fill handle and cell drag-and-drop എന്നത് ഓൺ ചെയ്യുക. Windows-ലെ Excel 2016, 2019, 2021, 2024 എന്നിവയിലെല്ലാം ഈ ഘട്ടങ്ങൾ ഒരേപോലെയാണ്. Excel for Mac-ൽ ഫിൽ ഹാൻഡിൽ ഡ്രാഗ് ചെയ്യുന്നത് ഒരുപോലെയാണെങ്കിലും ചില കീബോർഡ് ഷോർട്ട്കട്ടുകളും മെനു പേരുകളും വ്യത്യാസപ്പെട്ടിരിക്കും; ഇതിനായി Microsoft Help പേജിന്റെ Mac വേർഷൻ കാണുക.
ഉദാഹരണം: Internal Assessment മാർക്കുകൾ
ആറ് Semester 5 വിദ്യാർത്ഥികളുടെ ഇന്റേണൽ മാർക്കുകൾ ഒരു അധ്യാപിക രേഖപ്പെടുത്തുന്നു: 20 മാർക്ക് വീതമുള്ള രണ്ട് ടെസ്റ്റുകളും 10 മാർക്കിന്റെ ഒരു അസൈൻമെന്റും. ആകെ മാർക്ക് 50-ലാണ്. ഈ മാർക്കുകൾ മാറ്റമില്ലാതെ RawData എന്ന ഷീറ്റിൽ സൂക്ഷിക്കുക. യഥാർത്ഥ വിവരങ്ങൾ തിരുത്തപ്പെടാതിരിക്കാൻ കണക്കുകൂട്ടലുകൾ Analysis എന്ന മറ്റൊരു ഷീറ്റിലോ, അല്ലെങ്കിൽ വ്യക്തമായി തിരിച്ചിട്ടുള്ള കോളങ്ങളിലോ ചെയ്യുക.
| Row | A: Student | B: Test 1 (20) | C: Test 2 (20) | D: Assignment (10) | E: Total (50) | F: Percentage |
| 2 | Anjali | 16 | 18 | 9 | 43 | 86 |
| 3 | Rahul | 12 | 14 | 7 | 33 | 66 |
| 4 | Fathima | 19 | 17 | 10 | 46 | 92 |
| 5 | Arjun | 9 | 11 | 6 | 26 | 52 |
| 6 | Nandana | 14 | 15 | 8 | 37 | 74 |
| 7 | Muhammed | 17 | 13 | 9 | 39 | 78 |
| 8 | Column total | 87 | 88 | 49 | 224 |
(വിശദീകരണത്തിനായുള്ള ഡാറ്റ.)
Step 1 – ഓരോ വിദ്യാർത്ഥിയുടെയും Total (താഴേക്ക് കോപ്പി ചെയ്യുക)
E2-ൽ =B2+C2+D2 എന്ന് ടൈപ്പ് ചെയ്യുക. ഇത് E7 വരെ താഴേക്ക് fill ചെയ്യുക. Excel തനിയെ Row നമ്പറുകൾ ക്രമീകരിക്കുന്നു:
| Cell | Excel തയ്യാറാക്കുന്ന ഫോർമുല |
| E2 | =B2+C2+D2 |
| E3 | =B3+C3+D3 |
| E4 | =B4+C4+D4 |
| E7 | =B7+C7+D7 |
Step 2 – Percentage (താഴേക്ക് കോപ്പി ചെയ്യുക)
F2-ൽ =E2/50*100 എന്ന് ടൈപ്പ് ചെയ്ത് F7 വരെ fill down ചെയ്യുക. ഇവിടെ E2 എന്ന റഫറൻസ് E3, E4 എന്നിങ്ങനെ മാറുന്നു. 50 എന്ന സംഖ്യ ഫോർമുലയിൽ നേരിട്ട് നൽകിയിരിക്കുന്നതിനാൽ (hardcoded) എല്ലാ കോപ്പികളിലും അത് മാറ്റമില്ലാതെ തുടരുന്നു.
Step 3 – Column Totals (വലത്തോട്ട് കോപ്പി ചെയ്യുക)
B8-ൽ താഴെ പറയുന്ന ഫോർമുല ടൈപ്പ് ചെയ്യുക:
=B2+B3+B4+B5+B6+B7
തുടർന്ന് Ctrl+R ഉപയോഗിച്ച് E8 വരെ വലത്തോട്ട് fill ചെയ്യുക. ഇത്തവണ കോളത്തിന്റെ അക്ഷരങ്ങൾ മാറുകയും വരികളുടെ സംഖ്യകൾ മാറ്റമില്ലാതെ തുടരുകയും ചെയ്യുന്നു. C8 എന്നത് =C2+C3+C4+C5+C6+C7 എന്നും, E8 എന്നത് =E2+E3+E4+E5+E6+E7 എന്നും ആയിത്തീരും. (തുടർന്നുള്ള ഒരു പാഠത്തിൽ SUM ഫംഗ്ഷൻ ഉപയോഗിച്ച് ഇത് കൂടുതൽ ചുരുക്കി എഴുതാൻ നമ്മൾ പഠിക്കും).
നിങ്ങൾ ആകെ മൂന്ന് ഫോർമുലകൾ മാത്രമാണ് എഴുതിയത്, എന്നാൽ Excel 15 കൃത്യമായ ഫലങ്ങൾ നൽകി.
ഇത് നമുക്ക് എന്താണ് കാണിച്ചു തരുന്നത്?
- Fathima-യ്ക്കാണ് ഏറ്റവും ഉയർന്ന ആകെ മാർക്ക് (46/50, 92%), Arjun-നാണ് ഏറ്റവും കുറവ് (26/50, 52%). ഈ ചെറിയ ക്ലാസ്സിൽത്തന്നെ 40 ശതമാനത്തിന്റെ വലിയ വ്യത്യാസമുണ്ട്. End-semester പരീക്ഷയ്ക്ക് മുമ്പ് ഏറ്റവും കൂടുതൽ ശ്രദ്ധ നൽകേണ്ട വിദ്യാർത്ഥി Arjun ആണ്.
- Test 1 (87), Test 2 (88) എന്നിവയുടെ ആകെ മാർക്കുകൾ ഏറെക്കുറെ തുല്യമാണ്; അതിനാൽ രണ്ട് ടെസ്റ്റുകളിലുമായി ക്ലാസിന്റെ പ്രകടനം പൊതുവെ സ്ഥിരത പുലർത്തി. എന്നാൽ വ്യക്തിഗതമായി വിദ്യാർത്ഥികളിൽ മാറ്റമുണ്ടായി: Rahul-ഉം Anjali-യും മാർക്ക് മെച്ചപ്പെടുത്തിയപ്പോൾ, Muhammed-ന് 4 മാർക്ക് കുറഞ്ഞു.
- Assignment ആകെത്തുക (സാധ്യമായ 60-ൽ 49) ശതമാനക്കണക്കിൽ ഏറ്റവും ഉയർന്നതാണ് (ഏകദേശം 82%). നിശ്ചിത സമയത്ത് എഴുതുന്ന പരീക്ഷകളേക്കാൾ മികച്ച പ്രകടനമാണ് വിദ്യാർത്ഥികൾ അസൈൻമെന്റുകളിൽ കാഴ്ചവയ്ക്കുന്നത്.
- ഫോർമുലകൾ റിലേറ്റീവ് ആയതിനാൽ, അധ്യാപിക യഥാർത്ഥ ഡാറ്റയിൽ ഒരു മാർക്ക് തിരുത്തിയാൽ പോലും (ഉദാഹരണത്തിന്, Arjun-ന്റെ Test 2 റീ-ഇവാല്യുവേഷൻ നടത്തിയാൽ), എല്ലാ ടോട്ടലുകളും പെർസെന്റേജുകളും കോളം ടോട്ടലുകളും തനിയെ അപ്‌ഡേറ്റ് ആകുന്നു. ഇതാണ് വർക്ക്ഷീറ്റിനെ വിശ്വസനീയവും എളുപ്പത്തിൽ വീണ്ടും ഉപയോഗിക്കാവുന്നതുമാക്കുന്നത് (reproducible).
നിങ്ങളുടെ റിസൾട്ട് എങ്ങനെ പരിശോധിക്കാം?
- അവസാന വരി പരിശോധിക്കുക (Spot-check the last row):
E7-ൽ ക്ലിക്ക് ചെയ്ത് Formula Bar നോക്കുക. അതിൽ=B7+C7+D7എന്ന് കാണണം. അത് 6-ാമത്തെയോ 8-ാമത്തെയോ വരിയിലേക്കാണ് വിരൽ ചൂണ്ടുന്നതെങ്കിൽ, ആദ്യത്തെ ഫോർമുല തെറ്റായിരുന്നുവെന്നും ആ തെറ്റ് താഴേക്ക് കോപ്പി ചെയ്യപ്പെട്ടു എന്നും മനസ്സിലാക്കാം. - ഫോർമുല എന്താണ് ഉപയോഗിക്കുന്നതെന്ന് കാണുക: ഫോർമുലയുള്ള ഒരു സെൽ തിരഞ്ഞെടുത്ത്
F2അമർത്തുക. ഫോർമുല പരാമർശിക്കുന്ന സെല്ലുകളെ Excel വിവിധ നിറങ്ങളിൽ എടുത്തു കാണിക്കും. അല്ലെങ്കിൽ Formulas > Formula Auditing > Trace Precedents എന്നതിലേക്ക് പോയി ആ സെല്ലുകളിലേക്ക് അമ്പടയാളങ്ങൾ വരയ്ക്കാം (പരിശോധനയ്ക്ക് ശേഷം Remove Arrows ഉപയോഗിക്കുക). - Cross-total Check:
E8-ലെ ഗ്രാൻഡ് ടോട്ടൽ (224), ഓരോ കോളത്തിന്റെയും ആകെത്തുക കൂട്ടിയതിന് തുല്യമായിരിക്കണം: 87 + 88 + 49 = 224. വരികളിലെ ആകെത്തുകയും കോളങ്ങളിലെ ആകെത്തുകയും യോജിക്കുന്നുവെങ്കിൽ, കോപ്പി ചെയ്ത ഫോർമുലകൾ കൃത്യമാണ്. - ഒരു വരി നേരിട്ട് കണക്കുകൂട്ടി ഉറപ്പാക്കുക (Hand-check): Anjali: 16 + 18 + 9 = 43, കൂടാതെ 43/50 × 100 = 86. ✔
സാധാരണയായി സംഭവിക്കുന്ന തെറ്റുകൾ
- റഫറൻസുകൾക്ക് പകരം സംഖ്യകൾ നേരിട്ട് ടൈപ്പ് ചെയ്യുന്നത്:
=16+18+9എന്ന് നൽകുന്നത് ഒരിക്കൽ ശരിയായ ഉത്തരം നൽകുമെങ്കിലും, അത് മറ്റ് വരികളിലേക്ക് കോപ്പി ചെയ്യാൻ കഴിയില്ല; ഡാറ്റ മാറുമ്പോൾ തനിയെ അപ്‌ഡേറ്റ് ആകുകയുമില്ല. എപ്പോഴും സെല്ലുകളെ പരാമർശിക്കുക (point to cells). - തെറ്റായ വരിയിൽ നിന്ന് തുടങ്ങുന്നത്:
E2-ൽ=B3+C3+D3എന്നാണ് നൽകുന്നതെങ്കിൽ, കോപ്പി ചെയ്യുന്ന ഓരോ ഫോർമുലയും ഒരു വരി തെറ്റിപ്പോകും. അപ്പോൾ എല്ലാ റിസൾട്ടുകളും തെറ്റുകയും അവസാന വരിയിൽ ഒരുപക്ഷേ 0 വരികയും ചെയ്യും. ഫോർമുല കോപ്പി ചെയ്യുന്നതിന് മുമ്പ് ആദ്യത്തെ ഫോർമുല ശ്രദ്ധയോടെ പരിശോധിക്കുക. - ഒരു നിശ്ചിത സെല്ലിലേക്ക് വിരൽ ചൂണ്ടേണ്ട ഫോർമുല കോപ്പി ചെയ്യുന്നത്: പരമാവധി മാർക്ക് (50)
H1എന്ന സെല്ലിൽ നൽകിയിരിക്കുകയും നിങ്ങൾ=E2/H1*100എന്ന് ഫോർമുല എഴുതുകയും ചെയ്താൽ, അത് താഴേക്ക് കോപ്പി ചെയ്യുമ്പോൾH1എന്നത്H2,H3എന്നിങ്ങനെ മാറും. ഇവ ഒഴിഞ്ഞ സെല്ലുകളായതിനാൽ#DIV/0!എറർ ലഭിക്കും. അടുത്ത വിഷയത്തിൽ നമ്മൾ പഠിക്കുന്ന Absolute References പരിഹരിക്കുന്നത് ഈ പ്രശ്നമാണ്. - ഡാറ്റയ്ക്കുള്ളിലെ ബ്ലാങ്ക് അല്ലെങ്കിൽ മെർജ് ചെയ്ത വരികൾ: ബ്ലാങ്ക് വരികളിലേക്ക് കോപ്പി ചെയ്യുന്ന ഫോർമുല 0 എന്ന ഫലം തരുന്നു; ഈ 0 ഒരു യഥാർത്ഥ വിദ്യാർത്ഥിയുടെ സ്കോർ ആയി തെറ്റിദ്ധരിക്കപ്പെടാം. ഒരു വരിയിൽ ഒരു റെക്കോർഡ് എന്ന കണക്കിൽ ഡാറ്റ തുടർച്ചയായ ഒറ്റ ബ്ലോക്കായി സൂക്ഷിക്കുക.
- കോപ്പി ചെയ്തതിന് ശേഷം പരിശോധിക്കാതിരിക്കുന്നത്: തെറ്റായ ഒരു ഫോർമുല 500 തവണ കോപ്പി ചെയ്താൽ 500 തെറ്റായ ഉത്തരങ്ങളായിരിക്കും ലഭിക്കുക. എപ്പോഴും ആദ്യത്തെയും അവസാനത്തെയും വരികൾ പരിശോധിച്ച് ഉറപ്പാക്കുക.
പരിശീലന വ്യായാമം
ഒരു താലൂക്ക് ആരോഗ്യ ഓഫീസ് മൂന്ന് ഹെൽത്ത് സെന്ററുകളിലെ Outpatient സന്ദർശനങ്ങളുടെ കണക്കുകൾ രേഖപ്പെടുത്തുന്നു:
| Row | A: Centre | B: July | C: August | D: September | E: Q2 Total |
| 2 | Centre P | 410 | 385 | 452 | |
| 3 | Centre Q | 520 | 498 | 530 | |
| 4 | Centre R | 365 | 402 | 388 |
E2-നുള്ള ഫോർമുല എഴുതി അത് താഴേക്ക് fill ചെയ്യുക.E4-ൽ Excel നൽകുന്ന ഫോർമുല എന്തായിരിക്കും?- മൂന്ന് സെന്ററുകളിലെയും പാദവാർഷിക ആകെത്തുക (Quarterly Total) എത്രയാണ്, ഏറ്റവും തിരക്കേറിയ കേന്ദ്രം ഏതാണ്?
E2-ലെ ഫോർമുല കോപ്പി ചെയ്ത്F3-ൽ പേസ്റ്റ് ചെയ്താൽ ഏത് ഫോർമുലയാണ് ലഭിക്കുക?
ഉത്തരങ്ങൾ:
E2-ൽ:=B2+C2+D2.E4-ൽ വരുന്ന ഫോർമുല=B4+C4+D4ആയിരിക്കും.- Centre P = 1,247, Centre Q = 1,548, Centre R = 1,155. Centre Q ആണ് ഏറ്റവും കൂടുതൽ തിരക്കുണ്ടായിരുന്ന കേന്ദ്രം. പരിശോധന: 410 + 520 + 365 = 1,295 (July), 385 + 498 + 402 = 1,285 (August), 452 + 530 + 388 = 1,370 (September). 1,295 + 1,285 + 1,370 = 3,950; ഇത് 1,247 + 1,548 + 1,155 എന്നതിന് തുല്യമാണ്. ✔
E2-ൽ നിന്ന്F3-ലേക്ക് മാറുന്നത് ഒരു കോളം വലത്തോട്ടും ഒരു വരി താഴോട്ടുമാണ്; അതിനാൽ എല്ലാ റഫറൻസുകളും അതേ രീതിയിൽ മാറുന്നു:=C3+D3+E3.F3-നെ സംബന്ധിച്ച് ഇത് തികച്ചും തെറ്റായ ഫോർമുലയാണ്. റിലേറ്റീവ് റഫറൻസുകൾ എപ്പോഴും കോപ്പി ചെയ്യുന്നതിനനുസരിച്ച് സ്ഥാനമാറ്റം വരുത്തുന്നു.
പ്രധാന കാര്യങ്ങൾ
$ചിഹ്നം ഇല്ലാത്ത റഫറൻസുകൾ റിലേറ്റീവ് ആണ്. അവ ഒരു നിശ്ചിത വിലാസമല്ല, ഫോർമുലയുള്ള സെല്ലിനെ അടിസ്ഥാനമാക്കിയുള്ള ആപേക്ഷിക സ്ഥാനമാണ് സൂക്ഷിക്കുന്നത്.- താഴേക്ക് കോപ്പി ചെയ്യുമ്പോൾ വരികളുടെ സംഖ്യകൾ മാറുന്നു. വശങ്ങളിലേക്ക് കോപ്പി ചെയ്യുമ്പോൾ കോളത്തിന്റെ അക്ഷരങ്ങൾ മാറുന്നു.
- ഫോർമുലകൾ അതിവേഗം കോപ്പി ചെയ്യാൻ Fill Handle,
Ctrl+D/Ctrl+R, അല്ലെങ്കിൽ Home > Fill ഉപയോഗിക്കുക. - ആദ്യത്തെ ഫോർമുല കൃത്യമാക്കുക, തുടർന്ന് Formula Bar,
F2, അല്ലെങ്കിൽ Trace Precedents ഉപയോഗിച്ച് ശേഷമുള്ള ഏതെങ്കിലും ഒരു വരി പരിശോധിച്ച് ഉറപ്പുവരുത്തുക. - സംഖ്യകൾ നേരിട്ട് ടൈപ്പ് ചെയ്യുന്നതിന് പകരം സെല്ലുകളെ പരാമർശിക്കുന്ന ഫോർമുലകൾ, യഥാർത്ഥ ഡാറ്റ മാറുമ്പോഴും നിങ്ങളുടെ വിശകലനത്തെ കൃത്യതയോടെ നിലനിർത്തുന്നു.
- ഒരു ഫോർമുല എപ്പോഴും ഒരൊറ്റ നിശ്ചിത സെല്ലിലേക്ക് മാത്രം വിരൽ ചൂണ്ടേണ്ട സാഹചര്യങ്ങളിൽ Relative References പരാജയപ്പെടുന്നു; അപ്പോഴാണ് Absolute References ആവശ്യമായി വരുന്നത്.