AVERAGE(E2:E9) ആണോ അതോ AVERAGE(Attendance[Attendance %]) ആണോ പരിശോധിക്കാൻ കൂടുതൽ എളുപ്പം?
Cell addresses-ന് പകരം Table, Column പേരുകൾ ഉപയോഗിച്ച് Formulas എഴുതാൻ Structured References സഹായിക്കുന്നു. ഇത് വിശകലനങ്ങൾ വായിക്കാനും, പരിശോധിക്കാനും കൂടുതൽ എളുപ്പമാക്കുന്നു; കൂടാതെ പുതിയ Data ചേർക്കുമ്പോൾ ഫോർമുലകൾ തകരാറിലാകുന്നത് (break ആകുന്നത്) ഒഴിവാക്കുകയും ചെയ്യുന്നു.
എന്താണ് ഒരു Structured Reference?
നിങ്ങളുടെ Data ഒരു Excel Table-ൽ ആകുമ്പോൾ (Topic 21), ടേബിളിന്റെ പേരും ഓരോ കോളത്തിന്റെ പേരും Excel തിരിച്ചറിയുന്നു. ഒരു Structured Reference ഫോർമുലകളിൽ ആ പേരുകൾ നേരിട്ട് ഉപയോഗിക്കുന്നു:
| നിങ്ങൾക്ക് ആവശ്യമുള്ളത്… | Cell-address formula | Structured reference |
| ഒരു Column മുഴുവനായി | =SUM(D2:D9) | =SUM(Attendance[Attended]) |
| അതേ Row-ലെ വാല്യൂ | =D2/C2 | =[@Attended]/[@Held] |
| ഒരു Column-ന്റെ Header cell | =D1 | =Attendance[[#Headers], [Attended]] |
| ഒരു Column-ന്റെ Total row cell | =D10 | =Attendance[[#Totals], [Attended]] |
ഇതിന്റെ ഭാഗങ്ങൾ ലളിതമാണ്:
- Table name:
Attendance - Square brackets-ലുള്ള Column specifier:
[Attended] @ചിഹ്നം: “This row” — ഫോർമുലയുള്ള അതേ വരിയിലെ സെൽ.- Special items:
[#All](Header, Total എന്നിവയടക്കം ടേബിൾ മുഴുവനായി),[#Data](Data rows മാത്രം),[#Headers],[#Totals].
ഒരു Column Name-ൽ space അല്ലെങ്കിൽ ചില പ്രത്യേക ചിഹ്നങ്ങൾ (ഉദാഹരണത്തിന് %, $, #, (, ), -, &) ഉണ്ടെങ്കിൽ, Excel അതിനെ ഒരു അധിക pair of brackets ഉപയോഗിച്ച് പൊതിയുന്നു (ഉദാഹരണത്തിന് [@[Attendance %]]). Header-ൽ [, ], #, അല്ലെങ്കിൽ @ എന്നിവ ഉണ്ടെങ്കിൽ, അവയ്ക്ക് മുന്നിൽ ഒരു apostrophe (') ചേർക്കേണ്ടി വരും — അതിനാൽ തലക്കെട്ടുകൾ ലളിതമായി സൂക്ഷിക്കുന്നതാണ് എപ്പോഴും നല്ലത്.
Excel 365 (Windows)-ൽ ഘട്ടംഘട്ടമായി എങ്ങനെ ചെയ്യാം?
- Table നിർമ്മിക്കുക: നിങ്ങളുടെ Data-യിലെ ഏതെങ്കിലും സെല്ലിൽ ക്ലിക്ക് ചെയ്ത്
Ctrl+Tഅമർത്തുക (അല്ലെങ്കിൽInsert > Tableനൽകുക). “My table has headers” എന്നത് ടിക്ക് ചെയ്തിട്ടുണ്ടെന്ന് ഉറപ്പുവരുത്തി OK ക്ലിക്ക് ചെയ്യുക. - Table-ന് പേര് നൽകുക: ടേബിളിനുള്ളിലെ ഒരു സെൽ തിരഞ്ഞെടുത്ത ശേഷം, Table Design ടാബിലേക്ക് പോയി Table Name ബോക്സിൽ (റിബണിന്റെ ഇടതുവശത്ത്) ഒരു പേര് ടൈപ്പ് ചെയ്ത് Enter അമർത്തുക. പേരുകൾ അക്ഷരത്തിലോ (letter), underscore (
_), അല്ലെങ്കിൽ backslash (\) എന്നിവയിലോ ആരംഭിക്കണം; ഇടയിൽ space ഉണ്ടാകരുത് (Attendanceഅല്ലെങ്കിൽSem5_Attendanceഅനുയോജ്യമാണ്). നിങ്ങൾ പേര് മാറ്റാതിരുന്നാൽ, Excel സ്വയമേവTable1പോലുള്ള പേരുകൾ നൽകും, ഇത് വായിക്കുന്നവർക്ക് മനസ്സിലാകാൻ ബുദ്ധിമുട്ടാണ്. - Excel തനിയെ reference എഴുതാൻ അനുവദിക്കുക: പുതിയൊരു കോളത്തിൽ ടൈപ്പ് ചെയ്യുമ്പോൾ, സെൽ അഡ്രസ്സ് ടൈപ്പ് ചെയ്യുന്നതിന് പകരം ടേബിളിനുള്ളിലെ സെല്ലിൽ ക്ലിക്ക് ചെയ്യുക. Excel സ്വയമേവ Structured Reference ചേർത്തുനൽകും (ഉദാഹരണത്തിന്
[@Attended]). - Enter അമർത്തുക: ഒരു Table-ൽ ഫോർമുല നൽകി Enter അടിച്ചാൽ, ആ കോളത്തിലെ എല്ലാ വരികളിലേക്കും അത് സ്വയമേവ നിറയുന്നു — ഇതാണ് Calculated Column.
- മറ്റൊരു Sheet-ൽ നിന്ന് ടേബിൾ റഫർ ചെയ്യുക: മറ്റൊരു ഷീറ്റിൽ ചെന്ന്
=SUM(Attendance[എന്ന് ടൈപ്പ് ചെയ്യുമ്പോൾ തന്നെ തിരഞ്ഞെടുക്കാനുള്ള കോളങ്ങളുടെ ഒരു ഡ്രോപ്പ്ഡൗൺ ലിസ്റ്റ് Excel കാണിക്കും. കോളത്തിന്റെ പേര് തിരഞ്ഞെടുത്ത് ബ്രാക്കറ്റ് ക്ലോസ് ചെയ്ത് Enter അമർത്തുക.
Older versions & Mac: Excel 2010–2019 പതിപ്പുകളിൽ ഈ ടാബ് Table Tools > Design എന്നായിരിക്കാം. Excel for Mac-ലും ഇതേ reference syntax തന്നെയാണ് ഉപയോഗിക്കുന്നത്; റിബണിലെ പേരുകളിൽ ചെറിയ വ്യത്യാസങ്ങൾ ഉണ്ടായേക്കാം.
പ്രായോഗിക ഉദാഹരണം: Class Attendance
Semester 5 ക്ലാസിൽ ഇതുവരെ 60 ലെക്ചറുകൾ നടന്നിട്ടുണ്ട്. ഓരോ വിദ്യാർത്ഥിയുടെയും Attendance Percentage-ഉം, അവർക്ക് ആവശ്യമായ 75% ഹാജർ ഉണ്ടോ എന്നതും ട്യൂട്ടർക്ക് കണ്ടെത്തേണ്ടതുണ്ട്. ഇതിന്റെ യഥാർത്ഥ രജിസ്റ്റർ Raw_Data എന്ന ഷീറ്റിലും, വിശകലനം Analysis എന്ന കോപ്പി ഷീറ്റിലും, സംഗ്രഹം Output ഷീറ്റിലും തയ്യാറാക്കുക.
Analysis ഷീറ്റിലെ Table-ന്റെ പേര് Attendance എന്നാണ്:
| Roll No | Name | Held | Attended | Attendance % | Status |
| 101 | Aysha | 60 | 54 | 90.0 | Eligible |
| 102 | Fathima | 60 | 41 | 68.3 | Shortage |
| 103 | Rahul | 60 | 48 | 80.0 | Eligible |
| 104 | Nandana | 60 | 57 | 95.0 | Eligible |
| 105 | Muhammed | 60 | 44 | 73.3 | Shortage |
| 106 | Sreya | 60 | 51 | 85.0 | Eligible |
| 107 | Adil | 60 | 39 | 65.0 | Shortage |
| 108 | Devika | 60 | 46 | 76.7 | Eligible |
Calculated columns (ആദ്യ വരിയിൽ മാത്രം നൽകുക; ബാക്കി വരികൾ Table സ്വയമേവ പൂരിപ്പിക്കും):
- Attendance %:
=ROUND([@Attended]/[@Held]*100, 1) - Status:
=IF([@[Attendance %]]>=75, "Eligible", "Shortage")
(ശ്രദ്ധിക്കുക: Attendance % എന്ന പേരിൽ സ്പേസും % ചിഹ്നവും ഉള്ളതുകൊണ്ടാണ് അതിനു ചുറ്റും double brackets വന്നത്).
Output ഷീറ്റിലെ സംഗ്രഹം:
| Measure | Formula | Result |
| Number of students | =COUNTA(Attendance[Name]) | 8 |
| Total lectures attended | =SUM(Attendance[Attended]) | 380 |
| Average attendance % | =ROUND(AVERAGE(Attendance[Attendance %]), 1) | 79.2 |
| Lowest attendance % | =MIN(Attendance[Attendance %]) | 65.0 |
| Highest attendance % | =MAX(Attendance[Attendance %]) | 95.0 |
| Students with shortage | =COUNTIF(Attendance[Status], "Shortage") | 3 |
ഓരോ ഫോർമുലയും എന്താണ് കണക്കാക്കുന്നത് എന്ന് വ്യക്തമായി കാണിക്കുന്നു. നിങ്ങളുടെ ഫയൽ മുൻപ് കണ്ടിട്ടില്ലാത്ത ഒരു അധ്യാപകന് പോലും =MIN(Attendance[Attendance %]) കണ്ടാൽ അത് എന്താണെന്ന് ഒറ്റനോട്ടത്തിൽ മനസ്സിലാക്കാൻ സാധിക്കും.
യഥാർത്ഥ നേട്ടം: ടേബിൾ വളരുമ്പോൾ, ഫോർമുലകളും വികസിക്കുന്നു
വൈകി അഡ്മിഷൻ നേടിയ Anjali (Roll No 109, Attended 50) എന്ന വിദ്യാർത്ഥിയുടെ വിവരങ്ങൾ ടേബിളിന്റെ തൊട്ടുതാഴെയുള്ള വരിയിൽ ടൈപ്പ് ചെയ്യുന്നു. അപ്പോൾ തന്നെ ടേബിൾ സ്വയമേവ താഴേക്ക് വികസിക്കുകയും, അവളുടെ Attendance % (83.3), Status (Eligible) എന്നിവ സ്വയം കണക്കാക്കപ്പെടുകയും ചെയ്യുന്നു. കൂടാതെ Output ഷീറ്റിലെ എല്ലാ ഫോർമുലകളും തത്സമയം അപ്‌ഡേറ്റാകുന്നു: 9 വിദ്യാർത്ഥികൾ, 430 അറ്റൻഡ് ചെയ്ത ലെക്ചറുകൾ, ശരാശരി 79.6%, ഷോർട്ടേജ് ഇപ്പോഴും 3 പേർ തന്നെ.
ഇതിനുപകരം സാധാരണ =SUM(D2:D9) ആയിരുന്നെങ്കിൽ, ഓരോ ഫോർമുലയിലെയും റേഞ്ച് ഓർത്ത് മാറ്റിയെഴുതേണ്ടി വരുമായിരുന്നു — എന്നാൽ മറന്നുപോയ റേഞ്ചുകൾ മുന്നറിയിപ്പൊന്നുമില്ലാതെ തെറ്റായ ഉത്തരങ്ങൾ നൽകും.
Total Row
Table Design ടാബിലെ Total Row ടിക്ക് ചെയ്യുക. ഏതെങ്കിലും കോളത്തിന്റെ ടോട്ടൽ സെല്ലിലുള്ള ഡ്രോപ്പ്ഡൗണിൽ നിന്ന് ആവശ്യമുള്ള ഫംഗ്ഷൻ തിരഞ്ഞെടുക്കാം. ഇതിലൂടെ Excel =SUBTOTAL(109, [Attended]) പോലെയുള്ള ഫോർമുലകൾ തയ്യാറാക്കുന്നു; ഇത് ഫിൽട്ടർ വഴി മറയ്ക്കപ്പെട്ട വരികളെ ഒഴിവാക്കുന്നതിനാൽ, സ്ക്രീനിൽ കാണുന്ന ഡാറ്റയ്ക്ക് കൃത്യമായ ടോട്ടൽ ലഭിക്കും.
ഇതിൽ നിന്ന് നാം എന്താണ് മനസ്സിലാക്കുന്നത്?
ശരാശരി ഹാജർ 79.2% ആണ്, ഇത് 75%-ന് മുകളിലാണ്. എന്നാൽ ഈ ശരാശരി യഥാർത്ഥ പ്രശ്നത്തെ മറച്ചുവെക്കുന്നു: എട്ടിൽ മൂന്ന് വിദ്യാർത്ഥികൾക്കും (37.5%) ആവശ്യമായ ഹാജരില്ല, ഏറ്റവും കുറഞ്ഞ ഹാജർ 65% ആണ്. അതിനാൽ ട്യൂട്ടർ വെറുമൊരു ശരാശരി മാത്രം നോക്കാതെ Status കോളം പരിശോധിക്കേണ്ടതുണ്ട്. സെൽ അഡ്രസ്സുകൾക്ക് പിന്നിൽ ഒന്നും ഒളിച്ചുവെക്കാതെ, 75%-ൽ താഴെയുള്ള Attendance % ആണ് “Shortage” എന്ന് ആർക്കും ലളിതമായി മനസ്സിലാക്കാൻ Structured References സഹായിക്കുന്നു.
റിസൾട്ട് എങ്ങനെ പരിശോധിക്കാം ?
- സംഗ്രഹം നൽകിയ ഒരു സെല്ലിൽ ക്ലിക്ക് ചെയ്ത് Formula Bar നോക്കുക: ടേബിളിന്റെയും കോളത്തിന്റെയും പേരുകൾ നിങ്ങൾ കണക്കാക്കാൻ ഉദ്ദേശിച്ചതുമായി പൊരുത്തപ്പെടുന്നുണ്ടോ എന്ന് കാണാം.
- Attended കോളത്തിലെ ഡാറ്റ മുഴുവൻ സെലക്ട് ചെയ്യുക; സ്റ്റാറ്റസ് ബാറിൽ
Sum = 380എന്ന് കാണാം. ഇത്=SUM(Attendance[Attended])ഫോർമുലയുമായി പൊരുത്തപ്പെടണം. - സംഗ്രഹം നൽകിയ സെല്ലിൽ വെച്ച് Formulas > Trace Precedents നൽകുക — ആമ്പടയാളം ടേബിളിലെ ആ കോളം മുഴുവനായി ചൂണ്ടിക്കാണിക്കും.
- ടെസ്റ്റ് ചെയ്യുക: താൽക്കാലികമായി ഒരു വരി ചേർക്കുക, വിദ്യാർത്ഥികളുടെ എണ്ണം ഒന്നുകൂടി വർദ്ധിക്കുന്നുണ്ടെന്ന് ഉറപ്പുവരുത്തുക, തുടർന്ന്
Ctrl+Zഅടിച്ച് അത് ഒഴിവാക്കുക.
സാധാരണ വരുത്തുന്ന തെറ്റുകൾ
- Default Name (Table1) മാറ്റാതെ വിടുന്നത്: ഇത് പ്രവർത്തിക്കുമെങ്കിലും
Table1[Score]എന്നത് വ്യക്തത തരില്ല. അതിനാൽ ഫോർമുലകൾ എഴുതുന്നതിന് മുൻപ് തന്നെ ടേബിളിന് അനുയോജ്യമായ പേര് നൽകുക. - References സ്വയം ടൈപ്പ് ചെയ്യുമ്പോൾ കോളത്തിന്റെ പേര് തെറ്റിക്കുന്നത്: ഇത് Excel-ൽ Error വരാൻ ഇടയാക്കും. സ്വയം ടൈപ്പ് ചെയ്യുന്നതിന് പകരം സെല്ലുകളിൽ ക്ലിക്ക് ചെയ്യുകയോ ഡ്രോപ്പ്ഡൗൺ ലിസ്റ്റിൽ നിന്ന് തിരഞ്ഞെടുക്കുകയോ ചെയ്യുക.
- Symbols അല്ലെങ്കിൽ Spaces ഉള്ള കോളങ്ങൾക്ക് അധിക Brackets നൽകാൻ മറക്കുന്നത്:
[@Attendance %]പ്രവർത്തിക്കില്ല; പകരം[@[Attendance %]]എന്ന് നൽകണം. - Fill-right-ഉം Copy-യും ഒരേപോലെ പ്രവർത്തിക്കുമെന്ന് കരുതുന്നത്: ഒരു ഫോർമുല കോപ്പി ചെയ്യുമ്പോൾ Structured References മാറ്റമില്ലാതെ തുടരും. എന്നാൽ Fill Handle ഉപയോഗിച്ച് വലത്തേക്ക് ഡ്രാഗ് (Fill-right) ചെയ്യുമ്പോൾ, കോളം റഫറൻസുകൾ സീരീസ് പോലെ മാറും. അതിനാൽ നീക്കിയ ശേഷം ഫോർമുല പരിശോധിക്കുക.
- ശ്രദ്ധിക്കാതെ Table-നെ Range-ലേക്ക് മാറ്റുന്നത്: Table Design > Convert to Range നൽകിയാൽ എല്ലാ Structured References-ഉം സാധാരണ അബ്സൊല്യൂട്ട് അഡ്രസ്സുകളായി (
$D$2:$D$9പോലെ) മാറും, പിന്നീട് പുതിയ വരികൾ ചേർക്കുമ്പോൾ അവ സ്വയമേവ ഫോർമുലയിൽ ഉൾപ്പെടില്ല.
പരിശീലന ചോദ്യങ്ങൾ (Practice Exercise)
Attendance ടേബിൾ ഉപയോഗിച്ച് താഴെ പറയുന്നവയ്ക്കുള്ള ഫോർമുലകൾ എഴുതുക:
- ഓരോ വിദ്യാർത്ഥിക്കും നഷ്ടപ്പെട്ട ക്ലാസുകളുടെ എണ്ണം (Missed എന്ന പുതിയ കോളത്തിൽ).
- ക്ലാസ് മുഴുവനായി നഷ്ടപ്പെടുത്തിയ ആകെ ക്ലാസുകൾ.
- വിദ്യാർത്ഥികൾ ഹാജരായ ക്ലാസുകളുടെ ശരാശരി (1 ദശാംശ സംഖ്യയിലേക്ക് റൗണ്ട് ചെയ്തത്).
ഉത്തരങ്ങൾ:
- (a)
=[@Held] - [@Attended](Aysha 6, Fathima 19, … Devika 14). - (b)
=SUM(Attendance[Missed]) = 100(480 ക്ലാസുകൾ നടന്നു – 380 ക്ലാസുകളിൽ പങ്കെടുത്തു). - (c)
=ROUND(AVERAGE(Attendance[Attended]), 1) = 47.5.
പ്രധാന ആശയങ്ങൾ
- ഒരു Structured Reference ടേബിളിന്റെയും കോളത്തിന്റെയും പേര് വ്യക്തമാക്കുന്നു:
Table[Column]; ഇതിൽ@എന്നത് നിലവിലെ വരിയെ (Current row) സൂചിപ്പിക്കുന്നു. - ഫോർമുലകൾ എഴുതുന്നതിന് മുൻപ് നിങ്ങളുടെ Table-ന് എപ്പോഴും അർത്ഥവത്തായ ഒരു പേര് നൽകുക.
- ഫോർമുലകൾ ഉണ്ടാക്കുമ്പോൾ സെല്ലുകളിൽ ക്ലിക്ക് ചെയ്താൽ Excel സ്വയം reference എഴുതിക്കൊള്ളും.
- Structured References ഉപയോഗിക്കുന്ന ഫോർമുലകൾ ടേബിൾ വികസിക്കുന്നതിനനുസരിച്ച് സ്വയമേവ വികസിക്കുന്നു — ഇത് ഫലങ്ങൾ എപ്പോഴും കൃത്യമായി നിലനിർത്താൻ സഹായിക്കും.
- വ്യക്തമായി വായിക്കാവുന്ന ഫോർമുലകൾ പരിശോധിക്കാനും, വിശദീകരിക്കാനും, മറ്റൊരാൾക്ക് കൈമാറാനും എളുപ്പമാണ്. Raw data, analysis, output എന്നിവ വ്യത്യസ്ത ഷീറ്റുകളിൽ സൂക്ഷിക്കുക.