സ്വയമേവ വലിപ്പമുള്ള ഡൈനാമിക് ശ്രേണി

Excel-ൽ വലുപ്പം മാറ്റാൻ കഴിയുന്ന ഡാറ്റയുള്ള പട്ടികകൾ നിങ്ങളുടെ പക്കലുണ്ടോ, അതായത് ജോലിയുടെ ഗതിയിൽ വരികളുടെ എണ്ണം (നിരകൾ) കൂട്ടുകയോ കുറയുകയോ ചെയ്യുമോ? പട്ടികയുടെ വലുപ്പം “ഫ്ലോട്ട്” ആണെങ്കിൽ, നിങ്ങൾ ഈ നിമിഷം നിരന്തരം നിരീക്ഷിക്കുകയും അത് ശരിയാക്കുകയും വേണം:

• ഞങ്ങളുടെ പട്ടികയെ പരാമർശിക്കുന്ന റിപ്പോർട്ട് ഫോർമുലകളിലെ ലിങ്കുകൾ
• ഞങ്ങളുടെ പട്ടിക അനുസരിച്ച് നിർമ്മിച്ച പിവറ്റ് പട്ടികകളുടെ പ്രാരംഭ ശ്രേണികൾ
• ഞങ്ങളുടെ പട്ടിക അനുസരിച്ച് നിർമ്മിച്ച ചാർട്ടുകളുടെ പ്രാരംഭ ശ്രേണികൾ
• ഞങ്ങളുടെ പട്ടിക ഒരു ഡാറ്റാ ഉറവിടമായി ഉപയോഗിക്കുന്ന ഡ്രോപ്പ്ഡൗണുകൾക്കുള്ള ശ്രേണികൾ

മൊത്തത്തിൽ ഇതെല്ലാം നിങ്ങളെ ബോറടിപ്പിക്കാൻ അനുവദിക്കില്ല 😉

ഒരു ഡൈനാമിക് "റബ്ബർ" ശ്രേണി സൃഷ്ടിക്കുന്നത് കൂടുതൽ സൗകര്യപ്രദവും കൃത്യവുമാണ്, അത് ഡാറ്റയുടെ വരികളുടെയും നിരകളുടെയും യഥാർത്ഥ എണ്ണത്തിലേക്ക് സ്വയമേവ വലുപ്പം ക്രമീകരിക്കും. ഇത് നടപ്പിലാക്കാൻ, നിരവധി മാർഗങ്ങളുണ്ട്.

രീതി 1. സ്മാർട്ട് ടേബിൾ

നിങ്ങളുടെ സെല്ലുകളുടെ ശ്രേണി ഹൈലൈറ്റ് ചെയ്‌ത് ടാബിൽ നിന്ന് തിരഞ്ഞെടുക്കുക വീട് - പട്ടികയായി ഫോർമാറ്റ് ചെയ്യുക (ഹോം - ടേബിളായി ഫോർമാറ്റ് ചെയ്യുക):

ഒരു പാർശ്വഫലമായി പട്ടികയിൽ ചേർത്തിരിക്കുന്ന വരയുള്ള ഡിസൈൻ നിങ്ങൾക്ക് ആവശ്യമില്ലെങ്കിൽ, ദൃശ്യമാകുന്ന ടാബിൽ നിങ്ങൾക്ക് അത് ഓഫാക്കാം. കൺസ്ട്രക്ടർ (ഡിസൈൻ). ഈ രീതിയിൽ സൃഷ്ടിച്ച ഓരോ പട്ടികയ്ക്കും ടാബിലെ അതേ സ്ഥലത്ത് കൂടുതൽ സൗകര്യപ്രദമായ ഒന്ന് ഉപയോഗിച്ച് മാറ്റിസ്ഥാപിക്കാവുന്ന ഒരു പേര് ലഭിക്കുന്നു. കൺസ്ട്രക്ടർ (ഡിസൈൻ) കളത്തിൽ പട്ടികയുടെ പേര് (പട്ടികയുടെ പേര്).

ഇപ്പോൾ നമുക്ക് നമ്മുടെ "സ്മാർട്ട് ടേബിളിലേക്ക്" ഡൈനാമിക് ലിങ്കുകൾ ഉപയോഗിക്കാം:

• പട്ടിക 1 - തലക്കെട്ട് വരി ഒഴികെ മുഴുവൻ പട്ടികയിലേക്കും ലിങ്ക് ചെയ്യുക (A2:D5)
• പട്ടിക1[#എല്ലാം] - മുഴുവൻ പട്ടികയിലേക്കും ലിങ്ക് ചെയ്യുക (A1:D5)
• പട്ടിക1[പീറ്റർ] - ആദ്യ സെൽ-ഹെഡർ (C2:C5) ഇല്ലാത്ത ഒരു ശ്രേണി-നിരയുടെ റഫറൻസ്
• പട്ടിക1[#തലക്കെട്ടുകൾ] - നിരകളുടെ പേരുകളുള്ള "തലക്കെട്ടിലേക്ക്" ലിങ്ക് ചെയ്യുക (A1:D1)

അത്തരം റഫറൻസുകൾ ഫോർമുലകളിൽ മികച്ച രീതിയിൽ പ്രവർത്തിക്കുന്നു, ഉദാഹരണത്തിന്:

= SUM (പട്ടിക1[മോസ്കോ]) - "മോസ്കോ" നിരയുടെ തുകയുടെ കണക്കുകൂട്ടൽ

or

=VPR(F5;പട്ടിക 1.

ടാബിൽ തിരഞ്ഞെടുത്ത് പിവറ്റ് പട്ടികകൾ സൃഷ്ടിക്കുമ്പോൾ അത്തരം ലിങ്കുകൾ വിജയകരമായി ഉപയോഗിക്കാനാകും തിരുകുക - പിവറ്റ് പട്ടിക (തിരുകുക - പിവറ്റ് പട്ടിക) ഡാറ്റ ഉറവിടമായി സ്മാർട്ട് ടേബിളിന്റെ പേര് നൽകുക:

നിങ്ങൾ അത്തരമൊരു പട്ടികയുടെ ഒരു ഭാഗം തിരഞ്ഞെടുത്ത് (ഉദാഹരണത്തിന്, ആദ്യത്തെ രണ്ട് നിരകൾ) ഏതെങ്കിലും തരത്തിലുള്ള ഒരു ഡയഗ്രം സൃഷ്ടിക്കുകയാണെങ്കിൽ, പുതിയ വരികൾ ചേർക്കുമ്പോൾ, അവ യാന്ത്രികമായി ഡയഗ്രാമിലേക്ക് ചേർക്കും.

ഡ്രോപ്പ്-ഡൗൺ ലിസ്റ്റുകൾ സൃഷ്‌ടിക്കുമ്പോൾ, സ്‌മാർട്ട് ടേബിൾ ഘടകങ്ങളിലേക്ക് നേരിട്ടുള്ള ലിങ്കുകൾ ഉപയോഗിക്കാൻ കഴിയില്ല, എന്നാൽ ഒരു തന്ത്രപരമായ ട്രിക്ക് ഉപയോഗിച്ച് നിങ്ങൾക്ക് ഈ പരിമിതിയെ എളുപ്പത്തിൽ മറികടക്കാൻ കഴിയും - ഫംഗ്‌ഷൻ ഉപയോഗിക്കുക ഇൻഡിറക്റ്റ് (പരോക്ഷം), ഇത് വാചകത്തെ ഒരു ലിങ്കാക്കി മാറ്റുന്നു:

ആ. ഒരു ടെക്സ്റ്റ് സ്‌ട്രിംഗിന്റെ രൂപത്തിലുള്ള ഒരു സ്‌മാർട്ട് ടേബിളിലേക്കുള്ള ഒരു ലിങ്ക് (ഉദ്ധരണ ചിഹ്നങ്ങളിൽ!) ഒരു പൂർണ്ണമായ ലിങ്കായി മാറുന്നു, ഡ്രോപ്പ്-ഡൗൺ ലിസ്റ്റ് സാധാരണയായി അത് മനസ്സിലാക്കുന്നു.

രീതി 2: ഡൈനാമിക് നാമമുള്ള ശ്രേണി

ചില കാരണങ്ങളാൽ നിങ്ങളുടെ ഡാറ്റ ഒരു സ്മാർട്ട് ടേബിളാക്കി മാറ്റുന്നത് അഭികാമ്യമല്ലെങ്കിൽ, നിങ്ങൾക്ക് അൽപ്പം സങ്കീർണ്ണവും എന്നാൽ വളരെ സൂക്ഷ്മവും ബഹുമുഖവുമായ ഒരു രീതി ഉപയോഗിക്കാം - ഞങ്ങളുടെ പട്ടികയെ പരാമർശിക്കുന്ന Excel-ൽ ചലനാത്മകമായ ഒരു ശ്രേണി സൃഷ്ടിക്കുക. തുടർന്ന്, ഒരു സ്‌മാർട്ട് ടേബിളിന്റെ കാര്യത്തിലെന്നപോലെ, ഏത് ഫോർമുലകളിലും റിപ്പോർട്ടുകളിലും ചാർട്ടുകളിലും മറ്റും സൃഷ്‌ടിച്ച ശ്രേണിയുടെ പേര് നിങ്ങൾക്ക് സ്വതന്ത്രമായി ഉപയോഗിക്കാം. നമുക്ക് ഒരു ലളിതമായ ഉദാഹരണത്തിൽ നിന്ന് ആരംഭിക്കാം:

ടാസ്ക്: പുതിയ നഗരങ്ങൾ ചേർക്കുമ്പോഴോ ഇല്ലാതാക്കുമ്പോഴോ നഗരങ്ങളുടെ ഒരു ലിസ്‌റ്റിനെ പരാമർശിക്കുന്ന ചലനാത്മകമായ ഒരു ശ്രേണി ഉണ്ടാക്കുക.

ഏത് പതിപ്പിലും ലഭ്യമായ രണ്ട് ബിൽറ്റ്-ഇൻ Excel ഫംഗ്ഷനുകൾ ഞങ്ങൾക്ക് ആവശ്യമാണ് - POICPOZ (മത്സരം) ശ്രേണിയുടെ അവസാന സെൽ നിർണ്ണയിക്കാൻ, ഒപ്പം INDEX (ഇൻഡക്സ്) ഒരു ഡൈനാമിക് ലിങ്ക് സൃഷ്ടിക്കാൻ.

MATCH ഉപയോഗിച്ച് അവസാന സെൽ കണ്ടെത്തുന്നു

MATCH(ലുക്ക്അപ്പ്_മൂല്യം, ശ്രേണി, പൊരുത്തം_തരം) - ഒരു ശ്രേണിയിൽ (വരി അല്ലെങ്കിൽ നിര) നൽകിയിരിക്കുന്ന മൂല്യത്തിനായി തിരയുകയും അത് കണ്ടെത്തിയ സെല്ലിന്റെ ഓർഡിനൽ നമ്പർ നൽകുകയും ചെയ്യുന്ന ഒരു ഫംഗ്ഷൻ. ഉദാഹരണത്തിന്, MATCH("മാർച്ച്";A1:A5;0) എന്ന ഫോർമുല ഫലമായി 4 എന്ന നമ്പർ നൽകും, കാരണം A1:A5 കോളത്തിലെ നാലാമത്തെ സെല്ലിലാണ് "മാർച്ച്" എന്ന വാക്ക് സ്ഥിതി ചെയ്യുന്നത്. അവസാന ഫംഗ്ഷൻ ആർഗ്യുമെന്റ് Match_Type = 0 അർത്ഥമാക്കുന്നത് നമ്മൾ ഒരു കൃത്യമായ പൊരുത്തത്തിനായി തിരയുന്നു എന്നാണ്. ഈ ആർഗ്യുമെന്റ് വ്യക്തമാക്കിയിട്ടില്ലെങ്കിൽ, ഫംഗ്ഷൻ ഏറ്റവും അടുത്തുള്ള ഏറ്റവും ചെറിയ മൂല്യത്തിനായുള്ള തിരയൽ മോഡിലേക്ക് മാറും - ഞങ്ങളുടെ അറേയിലെ അവസാനത്തെ സെല്ലിനെ കണ്ടെത്താൻ ഇത് വിജയകരമായി ഉപയോഗിക്കാം.

തന്ത്രത്തിന്റെ സാരാംശം ലളിതമാണ്. മുകളിൽ നിന്ന് താഴേക്കുള്ള ശ്രേണിയിലുള്ള സെല്ലുകൾക്കായുള്ള തിരയലുകൾ മാച്ച് ചെയ്യുക, സിദ്ധാന്തത്തിൽ, നൽകിയിരിക്കുന്നതിന് ഏറ്റവും അടുത്തുള്ള ഏറ്റവും ചെറിയ മൂല്യം കണ്ടെത്തുമ്പോൾ അത് നിർത്തണം. ആവശ്യമുള്ള മൂല്യമായി പട്ടികയിൽ ലഭ്യമായതിനേക്കാൾ കൂടുതലായ ഒരു മൂല്യം നിങ്ങൾ വ്യക്തമാക്കുകയാണെങ്കിൽ, MATCH പട്ടികയുടെ അവസാനത്തിൽ എത്തും, ഒന്നും കണ്ടെത്തുകയും അവസാനം പൂരിപ്പിച്ച സെല്ലിന്റെ സീക്വൻസ് നമ്പർ നൽകുകയും ചെയ്യും. ഞങ്ങൾക്ക് അത് ആവശ്യമാണ്!

ഞങ്ങളുടെ ശ്രേണിയിൽ അക്കങ്ങൾ മാത്രമേ ഉള്ളൂവെങ്കിൽ, ആവശ്യമുള്ള മൂല്യമായി നമുക്ക് ഒരു സംഖ്യ വ്യക്തമാക്കാൻ കഴിയും, അത് പട്ടികയിലെ ഏതെങ്കിലും ഒന്നിനെക്കാളും വലുതാണ്:

ഒരു ഗ്യാരണ്ടിക്കായി, നിങ്ങൾക്ക് 9E + 307 എന്ന നമ്പർ ഉപയോഗിക്കാം (9 ന്റെ ശക്തിയിൽ 10 തവണ 307, അതായത് 9 പൂജ്യങ്ങളുള്ള 307) - Excel-ന് തത്വത്തിൽ പ്രവർത്തിക്കാൻ കഴിയുന്ന പരമാവധി നമ്പർ.

ഞങ്ങളുടെ കോളത്തിൽ ടെക്‌സ്‌റ്റ് മൂല്യങ്ങൾ ഉണ്ടെങ്കിൽ, സാധ്യമായ ഏറ്റവും വലിയ സംഖ്യയ്ക്ക് തുല്യമായി, നിങ്ങൾക്ക് നിർമ്മാണം REPEAT ("i", 255) ചേർക്കാം - 255 അക്ഷരങ്ങൾ "i" അടങ്ങുന്ന ഒരു ടെക്സ്റ്റ് സ്ട്രിംഗ് - അവസാന അക്ഷരം അക്ഷരമാല. തിരയുമ്പോൾ Excel യഥാർത്ഥത്തിൽ പ്രതീക കോഡുകൾ താരതമ്യം ചെയ്യുന്നതിനാൽ, ഞങ്ങളുടെ ടേബിളിലെ ഏത് വാചകവും സാങ്കേതികമായി ഇത്രയും നീളമുള്ള “yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy” y XNUMX+XNUMX)ഉം

INDEX ഉപയോഗിച്ച് ഒരു ലിങ്ക് സൃഷ്‌ടിക്കുക

പട്ടികയിലെ അവസാനത്തെ ശൂന്യമല്ലാത്ത മൂലകത്തിന്റെ സ്ഥാനം ഇപ്പോൾ ഞങ്ങൾക്കറിയാം, അത് ഞങ്ങളുടെ മുഴുവൻ ശ്രേണിയിലേക്കും ഒരു ലിങ്ക് രൂപപ്പെടുത്താൻ ശേഷിക്കുന്നു. ഇതിനായി ഞങ്ങൾ ഫംഗ്ഷൻ ഉപയോഗിക്കുന്നു:

INDEX(ശ്രേണി; വരി_സംഖ്യ; നിര_സംഖ്യ)

ഇത് സെല്ലിലെ ഉള്ളടക്കങ്ങൾ വരിയും നിരയും അനുസരിച്ച് ശ്രേണിയിൽ നിന്ന് നൽകുന്നു, ഉദാഹരണത്തിന്, നഗരങ്ങളും മാസങ്ങളും ഉള്ള ഞങ്ങളുടെ ടേബിളിലെ ഫംഗ്‌ഷൻ =INDEX(A1:D5;3;4) മുമ്പത്തെ രീതിയിൽ നിന്ന് 1240 നൽകും - ഉള്ളടക്കം 3-ാം വരിയിൽ നിന്നും 4-ാം നിരയിൽ നിന്നും, അതായത് സെല്ലുകൾ D3. ഒരു കോളം മാത്രമേ ഉള്ളൂ എങ്കിൽ, അതിന്റെ നമ്പർ ഒഴിവാക്കാവുന്നതാണ്, അതായത് ഫോർമുല INDEX(A2:A6;3) അവസാന സ്ക്രീൻഷോട്ടിൽ "സമര" നൽകും.

പൂർണ്ണമായും വ്യക്തമല്ലാത്ത ഒരു സൂക്ഷ്മതയുണ്ട്: സാധാരണ പോലെ = ചിഹ്നത്തിന് ശേഷം INDEX സെല്ലിലേക്ക് നൽകിയിട്ടില്ലെങ്കിലും കോളണിന് ശേഷമുള്ള ശ്രേണിയെക്കുറിച്ചുള്ള റഫറൻസിന്റെ അവസാന ഭാഗമായി ഉപയോഗിക്കുന്നുവെങ്കിൽ, അത് മേലിൽ നൽകില്ല. സെല്ലിന്റെ ഉള്ളടക്കം, പക്ഷേ അതിന്റെ വിലാസം! അങ്ങനെ, $A$2:INDEX ($A$2:$A$100;3) പോലുള്ള ഒരു ഫോർമുല, ഔട്ട്‌പുട്ടിൽ A2:A4 ശ്രേണിയെ പരാമർശിക്കും.

ഇവിടെയാണ് MATCH ഫംഗ്‌ഷൻ വരുന്നത്, ലിസ്റ്റിന്റെ അവസാനം ഡൈനാമിക് ആയി നിർണ്ണയിക്കാൻ ഞങ്ങൾ INDEX-നുള്ളിൽ തിരുകുന്നു:

=$A$2:INDEX($A$2:$A$100; മത്സരം(പ്രതിനിധി("I";255);A2:A100))

പേരുള്ള ഒരു ശ്രേണി സൃഷ്ടിക്കുക

എല്ലാം ഒരൊറ്റ മൊത്തത്തിൽ പായ്ക്ക് ചെയ്യാൻ അവശേഷിക്കുന്നു. ഒരു ടാബ് തുറക്കുക സൂത്രവാക്യം (സൂത്രവാക്യങ്ങൾ) ക്ലിക്കുചെയ്യുക നെയിം മാനേജർ (പേര് മാനേജർ). തുറക്കുന്ന വിൻഡോയിൽ, ബട്ടൺ ക്ലിക്ക് ചെയ്യുക സൃഷ്ടിക്കാൻ (പുതിയത്), ഫീൽഡിൽ ഞങ്ങളുടെ ശ്രേണിയുടെ പേരും ഫോർമുലയും നൽകുക ശ്രേണി (റഫറൻസ്):

ക്ലിക്ക് ചെയ്യാൻ അവശേഷിക്കുന്നു OK കൂടാതെ റെഡി റേഞ്ച് ഏത് ഫോർമുലകളിലും ഡ്രോപ്പ്-ഡൗൺ ലിസ്റ്റുകളിലും ചാർട്ടുകളിലും ഉപയോഗിക്കാം.

• പട്ടികകളും ലുക്ക്അപ്പ് മൂല്യങ്ങളും ലിങ്ക് ചെയ്യുന്നതിന് VLOOKUP ഫംഗ്ഷൻ ഉപയോഗിക്കുന്നു
• ഒരു ഓട്ടോ-പോപ്പുലേറ്റിംഗ് ഡ്രോപ്പ്ഡൗൺ ലിസ്റ്റ് എങ്ങനെ സൃഷ്ടിക്കാം
• ഒരു വലിയ അളവിലുള്ള ഡാറ്റ വിശകലനം ചെയ്യാൻ ഒരു പിവറ്റ് ടേബിൾ എങ്ങനെ സൃഷ്ടിക്കാം