StatsDaily – Course 01: Excel Fundamentals for Data Analytics | Module 2, Topic 1
ഇന്നത്തെ വിഷയം: Topic 9 – Formula syntax (Course 01, Module 2, Cycle 1 – പ്രധാന പാഠ്യഭാഗം)
ഈ കോഴ്‌സിൽ നിങ്ങൾ നിർമ്മിക്കുന്ന ഓരോ Total, Average, Grade, മറ്റ് സംഗ്രഹ അളവുകൾ (Summary measures) എന്നിവയെല്ലാം ആരംഭിക്കുന്നത് ഒരു Formula-യിൽ നിന്നാണ്. Excel പ്രതീക്ഷിക്കുന്ന രൂപത്തിൽ Formulas എഴുതാൻ പഠിച്ചാൽ, നിങ്ങളുടെ Worksheets കൃത്യമായി കണക്കുകൂട്ടലുകൾ നടത്തുകയും, Data മാറുമ്പോൾ തനിയെ Update ആവുകയും, ആ ഷീറ്റ് തുറക്കുന്ന ആർക്കും അത് എളുപ്പത്തിൽ പരിശോധിച്ച് ഉറപ്പുവരുത്താൻ സാധിക്കുകയും ചെയ്യും.
എന്താണ് ഒരു Formula?
ഒരു മൂല്യം (Value) കണക്കുകൂട്ടാൻ Excel-ന് നൽകുന്ന നിർദ്ദേശമാണ് (Instruction) Formula. നിങ്ങൾ ഈ നിർദ്ദേശം ഒരു Cell-ലേക്ക് ടൈപ്പ് ചെയ്യുന്നു; Excel നിർദ്ദേശം ഉള്ളിൽ സൂക്ഷിക്കുകയും എന്നാൽ അതിന്റെ ഫലം (Result) പുറത്തേക്ക് കാണിക്കുകയും ചെയ്യുന്നു. ആ Cell-ൽ ക്ലിക്ക് ചെയ്താൽ, Grid-ന് മുകളിലുള്ള Formula Bar-ൽ നിങ്ങൾ ടൈപ്പ് ചെയ്ത യഥാർത്ഥ Formula കാണാം.
ഈ ഒരു സവിശേഷതയാണ് സ്പ്രെഡ്ഷീറ്റുകളെ (Spreadsheets) Data Analysis-ന് ഏറെ ഉപകാരപ്രദമാക്കുന്നത്. ഉദാഹരണത്തിന്, ഒരു വിദ്യാർത്ഥിയുടെ മാർക്ക് തിരുത്തിയാൽ, ആ മാർക്കുമായി ബന്ധപ്പെട്ട എല്ലാ Formulas-ഉം സ്വയം വീണ്ടും കണക്കുകൂട്ടപ്പെടും (Recalculate). ഒന്നും വീണ്ടും ടൈപ്പ് ചെയ്യേണ്ടതില്ല; മാത്രമല്ല, കണക്കുകൂട്ടലിന്റെ ലോജിക് പരിശോധനയ്ക്കായി തെളിഞ്ഞു കാണുകയും ചെയ്യും.
“Formula syntax” എന്നാൽ Formula-യുടെ വ്യാകരണം (Grammar) എന്നാണ് അർത്ഥം: Excel-ന് മനസ്സിലാകുന്ന രീതിയിൽ അത് എങ്ങനെ കൃത്യമായി എഴുതണം എന്നതിനുള്ള നിയമങ്ങളാണവ.
Formula Syntax-ന്റെ നിയമങ്ങൾ
- Rule 1: എല്ലാ Formula-കളും സമം ചിഹ്നം (=) ഉപയോഗിച്ച് ആരംഭിക്കണം. ഇതില്ലാതെ ടൈപ്പ് ചെയ്താൽ Excel അതിനെ സാധാരണ Text അല്ലെങ്കിൽ Number ആയി മാത്രമേ കണക്കാക്കൂ.
C2+D2എന്നത് വെറുമൊരു Text ആണ്; എന്നാൽ=C2+D2എന്നത് ഒരു കണക്കുകൂട്ടലാണ്. - Rule 2: ഒരു Formula നാല് തരത്തിലുള്ള ഘടകങ്ങൾ ചേർത്താണ് നിർമ്മിക്കുന്നത്. Microsoft ഇതിനെ ഇപ്രകാരം വിവരിക്കുന്നു:
| ഘടകം | എന്താണ് ഉദ്ദേശിക്കുന്നത് | ഉദാഹരണം |
| Constants | മാറ്റമില്ലാതെ Formula-യിലേക്ക് നേരിട്ട് നൽകുന്ന കണക്കുകൂട്ടാത്ത മൂല്യം | 40, "Pass" |
| References | മൂല്യം ഉപയോഗിക്കുന്ന Cell അല്ലെങ്കിൽ Range-ന്റെ വിലാസം (Address) | C2, C2:E2 |
| Operators | മൂല്യങ്ങൾ വെച്ച് എന്ത് ചെയ്യണമെന്ന് നിർദ്ദേശിക്കുന്ന ചിഹ്നങ്ങൾ | +, *, >=, & |
| Functions | Excel-ൽ ഉള്ളടങ്ങിയിട്ടുള്ള മുൻകൂട്ടി നിശ്ചയിച്ച കണക്കുകൂട്ടലുകൾ (Built-in named calculations) | SUM(C2:E2), PI() |
- Rule 3: ശരിയായ Operators ഉപയോഗിക്കുക. Excel-ൽ നാല് കൂട്ടം Calculation Operators ഉണ്ട്:
- Arithmetic (അങ്കഗണിതം):
+(സങ്കലനം),-(വ്യവകലനം അല്ലെങ്കിൽ നെഗറ്റീവ് ആക്കൽ),*(ഗുണനം),/(ഹരണം),%(ശതമാനം),^(Power/Exponent). ശ്രദ്ധിക്കുക: ഗുണനത്തിനായി*ആണ് ഉപയോഗിക്കേണ്ടത്,×അല്ലെങ്കിൽxഅല്ല. - Comparison (താരതമ്യം):
=,>,<,>=,<=,<>(Not equal to). ഇവ എപ്പോഴുംTRUEഅല്ലെങ്കിൽFALSEഎന്ന Logical Value തരുന്നു. - Text concatenation (ടെക്സ്റ്റ് കൂട്ടിച്ചേർക്കൽ):
&ചിഹ്നം ഒന്നിലധികം വാക്കുകളെയോ മൂല്യങ്ങളെയോ ഒറ്റ Text String ആയി മാറ്റുന്നു. ഉദാഹരണത്തിന്,="Kodu"&"vally"എന്നത്Koduvallyഎന്ന് നൽകുന്നു. - Reference (റഫറൻസ്):
:(Colon) ഒരു Range ഉണ്ടാക്കുന്നു (ഉദാ:B5:B15);,(Comma) ഒന്നിലധികം Ranges ചേർക്കുന്നു (ഉദാ:SUM(B5:B15,D5:D15)); ഒരു സിംഗിൾ സ്പേസ് (Space) രണ്ട് Range-കളുടെ ഇന്റർസെക്ഷൻ (Intersection) നൽകുന്നു.
- Arithmetic (അങ്കഗണിതം):
- Rule 4: Functions എപ്പോഴും
NAME(arguments)എന്ന ഫോർമാറ്റിലാണ് വരുന്നത്. Function പേരിന് തൊട്ടുപിന്നാലെ ഒരു ഓപ്പണിംഗ് ബ്രാക്കറ്റും(, കോമകൾ ഉപയോഗിച്ച് വേർതിരിച്ച ഇൻപുട്ടുകളും (Arguments), തുടർന്ന് ക്ലോസിംഗ് ബ്രാക്കറ്റും)വരുന്നു. ഓരോ(-നും അനുയോജ്യമായ)നിർബന്ധമായും ഉണ്ടായിരിക്കണം. (യൂറോപ്യൻ റീജിയണൽ ക്രമീകരണങ്ങളുള്ള കമ്പ്യൂട്ടറുകളിൽ Arguments വേർതിരിക്കാൻ കോമയ്ക്ക് പകരം സെമികോളൻ;ആയിരിക്കും Excel ഉപയോഗിക്കുന്നത്). - Rule 5: Formula-യ്ക്കുള്ളിലെ Text നിർബന്ധമായും Double Quotes-ൽ ആയിരിക്കണം. Numbers-നും Cell References-നും Double Quotes ആവശ്യമില്ല: ഉദാഹരണത്തിന്
="Pass"എന്നത് ശരിയാണ്, എന്നാൽ=Passഎന്നത് തെറ്റാണ്.
Excel ഏത് ക്രമത്തിലാണ് *, +, ബ്രാക്കറ്റുകൾ എന്നിവ കണക്കാക്കുന്നത് എന്നത് നമ്മുടെ അടുത്ത പാഠമായ Order of Operations-ൽ ചർച്ച ചെയ്യുന്നതാണ്.
ഘട്ടം ഘട്ടമായി: ഒരു Formula നൽകുന്ന വിധം
- റിസൾട്ട് കാണേണ്ട Cell-ൽ ക്ലിക്ക് ചെയ്യുക.
=എന്ന് ടൈപ്പ് ചെയ്യുക.- ഉപയോഗിക്കേണ്ട ആദ്യത്തെ Cell-ൽ ക്ലിക്ക് ചെയ്യുക, അല്ലെങ്കിൽ അതിന്റെ വിലാസം നേരിട്ട് ടൈപ്പ് ചെയ്യുക (ഉദാ:
C2). +പോലുള്ള ഒരു Operator ടൈപ്പ് ചെയ്യുക.- അടുത്ത Cell ക്ലിക്ക് ചെയ്യുകയോ ടൈപ്പ് ചെയ്യുകയോ ചെയ്യുക. ആവശ്യമെങ്കിൽ 4, 5 ഘട്ടങ്ങൾ ആവർത്തിക്കുക.
- Enter പ്രസ് ചെയ്യുക. ഇപ്പോൾ Cell-ൽ ഫലവും (Result), Cell സെലക്ട് ചെയ്യുമ്പോൾ Formula Bar-ൽ യഥാർത്ഥ Formula-യും കാണാം.
ഒരു Sheet-ലെ ഫലങ്ങൾക്ക് പകരം എല്ലാ Formulas-ഉം ഒരുമിച്ച് കാണാൻ, Ctrl കീയും ഒപ്പം Grave Accent കീയും ( ` – സാധാരണയായി കീബോർഡിൽ 1-ന് ഇടതുവശത്ത് കാണുന്നത്) ഒരുമിച്ച് അമർത്തുക; അല്ലെങ്കിൽ Formulas > Formula Auditing > Show Formulas എന്ന ഓപ്ഷൻ തിരഞ്ഞെടുക്കുക. തിരികെ റിസൾട്ട് കാണാൻ ഇതേ ഷോർട്ട്കട്ട് വീണ്ടും പ്രസ് ചെയ്യുക.
പഴയ Desktop പതിപ്പുകളിലും (2016, 2019, 2021) ഇതേ നിയമങ്ങൾ തന്നെയാണ്. Mac കമ്പ്യൂട്ടറുകളിൽ Enter-ന് പകരം Return കീ അമർത്തുക; Show Formulas ഓപ്ഷൻ Formulas ടാബിൽ ലഭ്യമാണ്.
ഉദാഹരണം: 6 വിദ്യാർത്ഥികളുടെ Semester മാർക്കുകൾ (Worked example)
ഒരു Statistics അധ്യാപകന്റെ പക്കൽ ഒരു പേപ്പറിന്റെ മാർക്കുകൾ Internal (20-ൽ), Theory (60-ൽ), Practical (20-ൽ) എന്നിങ്ങനെ ലഭ്യമാണ്. ഈ റോ ഡാറ്റ (Raw data) RawData എന്ന ഷീറ്റിലും കണക്കുകൂട്ടലുകൾ മറ്റൊരു Analysis ഷീറ്റിലും ചെയ്യുക (പഠനാവശ്യത്തിനായി ഇവിടെ കാണിച്ചിരിക്കുന്നതുപോലെ തൊട്ടടുത്ത കോളങ്ങളിൽ വ്യക്തമായ ലേബലുകളോടെയും ചെയ്യാം).
| A | B | C | D | E | |
| 1 | Roll No | Name | Internal (20) | Theory (60) | Practical (20) |
| 2 | S501 | Anjana | 17 | 48 | 18 |
| 3 | S502 | Muhammed Fasil | 12 | 31 | 15 |
| 4 | S503 | Fathima Nidha | 9 | 22 | 6 |
| 5 | S504 | Arjun | 15 | 40 | 16 |
| 6 | S505 | Shahana | 18 | 52 | 19 |
| 7 | S506 | Vishnu | 10 | 27 | 11 |
ഇനി നാല് പുതിയ Formula Columns ചേർക്കുക. ഓരോ Formula-യും Row 2-ൽ ടൈപ്പ് ചെയ്ത ശേഷം, Row 7 വരെ താഴേക്ക് കോപ്പി ചെയ്യുക (തിരഞ്ഞെടുത്ത സെല്ലിന്റെ താഴെ വലതുവശത്തുള്ള ചെറിയ ചതുരമായ Fill Handle താഴേക്ക് Drag ചെയ്യുക).
| Column | Heading | Formula in row 2 | ഉപയോഗിച്ച ഘടകങ്ങൾ (Parts used) |
| F | Total (100) | =C2+D2+E2 | References, Arithmetic operator |
| G | Total check | =SUM(C2:E2) | Function, Range operator |
| H | Passed? | =F2>=40 | Reference, Comparison operator, Constant |
| I | Label | =B2&" ("&A2&")" | References, Text constants, & operator |
ലഭിച്ച ഫലങ്ങൾ :
| Name | Total | Total check | Passed? | Label |
| Anjana | 83 | 83 | TRUE | Anjana (S501) |
| Muhammed Fasil | 58 | 58 | TRUE | Muhammed Fasil (S502) |
| Fathima Nidha | 37 | 37 | FALSE | Fathima Nidha (S503) |
| Arjun | 71 | 71 | TRUE | Arjun (S504) |
| Shahana | 89 | 89 | TRUE | Shahana (S505) |
| Vishnu | 48 | 48 | TRUE | Vishnu (S506) |
അവസാനമായി, ക്ലാസ്സിലെ ആകെ ശരാശരി (Class average total) ലഭിക്കാൻ F9 സെല്ലിൽ =SUM(F2:F7)/6 എന്ന് ടൈപ്പ് ചെയ്യുക: 64.33 (386 ÷ 6) ലഭിക്കും.
ഇത് എന്താണ് വ്യക്തമാക്കുന്നത്?
ആറ് വിദ്യാർത്ഥികളിൽ അഞ്ച് പേരും പാസ് മാർക്കായ 40 നേടി; എന്നാൽ Fathima Nidha-യ്ക്ക് (37) അത് സാധിച്ചില്ല. 20-ൽ വെറും 6 മാർക്ക് മാത്രമുള്ള അവളുടെ Practical മാർക്കാണ് ക്ലാസിലെ ഏറ്റവും കുറഞ്ഞ ഘടകം; അതിനാൽ അവൾക്ക് പ്രായോഗിക പരിശീലനം (Practical support) നൽകുന്നതാണ് ഏറ്റവും അനുയോജ്യമായ നടപടി. ക്ലാസ് ശരാശരിയായ 64.33 പാസ് മാർക്കിനേക്കാൾ വളരെ മുകളിലാണെങ്കിലും, 37 മുതൽ 89 വരെയുള്ള മാർക്കുകളുടെ വ്യത്യാസം (Spread) ക്ലാസിലെ വിദ്യാർത്ഥികളുടെ പഠനനിലവാരം ഒരേപോലെയല്ല എന്ന് കാണിക്കുന്നു.
താരതമ്യ Formula ഇവിടെ “Pass”/”Fail” എന്നതിന് പകരം TRUE/FALSE എന്നാണ് നൽകിയത് എന്ന് ശ്രദ്ധിക്കുക. Comparison Operator-ന്റെ കൃത്യമായ സ്വഭാവമാണിത്. TRUE/FALSE എന്നതിനെ വാക്കുകളാക്കി മാറ്റുന്നത് IF Function വഴിയാണ് (Topic 19-ൽ നമ്മളിത് പഠിക്കും).
ഫലങ്ങൾ എങ്ങനെ പരിശോധിക്കാം?
- രണ്ട് വഴികൾ, ഒരൊറ്റ ഉത്തരം (Two routes, one answer): Column F-ഉം (
+) Column G-യും (SUM) വ്യത്യസ്ത രീതികളിലാണ് കണക്കുകൂട്ടിയത്; എന്നാൽ എല്ലാ വരികളിലും അവ രണ്ടും ഒരേപോലെ ആയിരിക്കണം. ഇതിൽ എന്തെങ്കിലും വ്യത്യാസമുണ്ടെങ്കിൽ അത് ടൈപ്പിംഗിലോ സെൽ റഫറൻസിലോ സംഭവിച്ച പിഴവാണ്. - ഒരു വരി സ്വയം പരിശോധിച്ച് നോക്കുക (Hand-check): Anjana-യുടെ മാർക്കുകൾ:
17 + 48 + 18 = 83✔ - Show Formulas ഉപയോഗിക്കുക:
Ctrl + \`` പ്രസ് ചെയ്ത് ഓരോ കോളവും താഴേക്ക് പരിശോധിക്കുക . ഓരോ വരിയിലും റഫറൻസുകൾ ക്രമമായി മാറിയിരിക്കുന്നത് കാണാം (=C3+D3+E3,=C4+D4+E4`…). Formula-യ്ക്ക് പകരം അബദ്ധത്തിൽ ടൈപ്പ് ചെയ്ത സംഖ്യകൾ പെട്ടെന്ന് തന്നെ തിരിച്ചറിയാൻ ഇതിലൂടെ സാധിക്കും. - ന്യായയുക്തത (Reasonableness): ടോട്ടൽ മാർക്ക് ഒരിക്കലും 100-ൽ കൂടാനോ 0-ൽ താഴെയാകാനോ പാടില്ല. അങ്ങനെ വന്നാൽ റഫറൻസിൽ തെറ്റുണ്ട് എന്ന് മനസ്സിലാക്കാം.
സ്ഥിരമായി സംഭവിക്കുന്ന പിഴവുകൾ
- Equal Sign (=) നൽകാൻ മറക്കുക:
=SUM(C2:E2)എന്നതിന് പകരംSUM(C2:E2)എന്ന് മാത്രം നൽകിയാൽ അത് സാധാരണ Text ആയി നിൽക്കുകയും കണക്കുകൂട്ടൽ നടക്കാതിരിക്കുകയും ചെയ്യും. - Formula-യ്ക്ക് പകരം റിസൾട്ട് ടൈപ്പ് ചെയ്യുന്നത്:
83എന്ന് നേരിട്ട് ടൈപ്പ് ചെയ്യുന്നത് ഇപ്പോൾ ശരിയായി തോന്നാമെങ്കിലും, മാർക്കിൽ മാറ്റം വരുമ്പോൾ അത് തനിയെ മാറില്ല; കൂടാതെ അത് എവിടെ നിന്ന് വന്നു എന്ന് ആർക്കും പരിശോധിക്കാനും കഴിയില്ല. - തെറ്റായ ചിഹ്നങ്ങൾ ഉപയോഗിക്കുന്നത്: ഗുണനത്തിന്
xഅല്ലെങ്കിൽ×ഉപയോഗിക്കുന്നതോ,>=എന്നതിന് പകരം=>ഉപയോഗിക്കുന്നതോ സാധുവാകില്ല. എപ്പോഴും*,>=എന്നിവ ഉപയോഗിക്കുക. - ബ്രാക്കറ്റുകളിലോ Quotes-ലോ വരുന്ന തെറ്റുകൾ:
=SUM(C2:E2എന്നത് അപൂർണ്ണമാണ്; Enter അടിക്കുമ്പോൾ Excel മുന്നറിയിപ്പ് നൽകും. Quotes ഇല്ലാതെ=B2&Passഎന്ന് ടൈപ്പ് ചെയ്താൽ അത് അപരിചിതമായ പേരായി കണ്ട്#NAME?എറർ നൽകും. അതിനാൽ ബ്രാക്കറ്റുകളുടെ എണ്ണം തുല്യമാണെന്ന് ഉറപ്പുവരുത്തുക, Text-കൾ" "ഉള്ളിൽ നൽകുക. - മാറ്റങ്ങൾ വരാൻ സാധ്യതയുള്ള മൂല്യങ്ങൾ നേരിട്ട് നൽകുന്നത് (Hard-coding): സ്ഥിരമായ പാസ് മാർക്കിന്
=F2>=40നൽകുന്നത് കുഴപ്പമില്ല; എന്നാൽ പാസ് മാർക്ക് മാറുവാൻ സാധ്യതയുണ്ടെങ്കിൽ, അതിനായി മറ്റൊരു Cell നൽകി ആ സെല്ലിന്റെ Reference നൽകുന്നതാണ് ഉത്തമം (ഇത്തരം സെല്ലുകളെ Lock ചെയ്യുന്നത് Topic 12-ൽ പഠിക്കും).
പരിശീലന വ്യായാമം
മുകളിലെ Dataset അടിസ്ഥാനമാക്കി താഴെ പറയുന്നവ ചെയ്യുക:
- Column J-ൽ, ഓരോ വിദ്യാർത്ഥിയുടെയും Theory മാർക്ക് 60-ന്റെ ശതമാനമായി (Percentage) കണക്കാക്കുന്ന Formula എഴുതുക.
- ഒരു സഹപാഠി ഒരു Cell-ൽ
SUM(C3:E3)എന്ന് ടൈപ്പ് ചെയ്തപ്പോൾ 58-ന് പകരം ആ Text തന്നെയാണ് കാണുന്നത്. എന്താണ് സംഭവിച്ച പിഴവ്? - ഒരു വിദ്യാർത്ഥിയുടെ Practical മാർക്ക് 20 അല്ലെങ്കിൽ
TRUEനൽകുന്ന Formula എഴുതുക.
ഉത്തരങ്ങൾ :
- J2-ൽ
=D2/60*100എന്ന് നൽകി താഴേക്ക് കോപ്പി ചെയ്യുക. Anjana:48 ÷ 60 × 100 = 80; Fathima Nidha:36.67. - Equal sign (=) വിട്ടുപോയി. അതിനെ
=SUM(C3:E3)എന്ന് തിരുത്തുക. =E2<>20. ആർക്കും മുഴുവൻ 20 മാർക്കും ലഭിക്കാത്തതിനാൽ എല്ലാ വിദ്യാർത്ഥികൾക്കും ഇത്TRUEഎന്ന് നൽകും.
പ്രധാന ആശയങ്ങൾ
- ഓരോ Formula-യും ആരംഭിക്കുന്നത്
=ചിഹ്നത്തിലാണ്; Excel Formula ഉള്ളിൽ സൂക്ഷിക്കുകയും ഫലം പ്രദർശിപ്പിക്കുകയും ചെയ്യുന്നു. - Constants, References, Operators, Functions എന്നിവ ഉപയോഗിച്ചാണ് ഒരു Formula നിർമ്മിക്കുന്നത്.
- നാല് Operator ഗ്രൂപ്പുകളെക്കുറിച്ച് അറിഞ്ഞിരിക്കുക: Arithmetic, Comparison (
TRUE/FALSEനൽകുന്നു), Text (&), Reference (:,,). - ഫംഗ്ഷനുകൾ
NAME(arguments)എന്ന ഫോർമാറ്റിലാണ്; Text-കൾ Double Quotes-ൽ ആയിരിക്കണം; ബ്രാക്കറ്റുകൾ കൃത്യമായി ക്ലോസ് ചെയ്തിരിക്കണം. - ഡാറ്റ അപ്ഡേറ്റ് ആകുന്നതിനും ഓഡിറ്റ് ചെയ്യുന്നതിനും സംഖ്യകൾ നേരിട്ട് ടൈപ്പ് ചെയ്യുന്നതിന് പകരം Cell References ഉപയോഗിക്കുക.
- പരിശോധിക്കുക: രണ്ട് രീതികളിൽ കണക്കാക്കി നോക്കുക, ഒരു വരി കൈകൊണ്ട് പരിശോധിച്ച് ഉറപ്പാക്കുക, കൂടാതെ Show Formulas ഉപയോഗിക്കുക.
- Raw data, Analysis, Output എന്നിവ വെവ്വേറെ ഷീറ്റുകളിലായി കൃത്യമായ പേരുകൾ നൽകി സൂക്ഷിക്കുക.