വ്യത്യസ്ത അളവിലുള്ള "നേരായ" പ്രാരംഭ ഡാറ്റ എഴുതിയിരിക്കുന്ന ഒരു ലിസ്റ്റ് നിങ്ങളുടെ പക്കലുണ്ടെന്ന് കരുതുക - ഉദാഹരണത്തിന്, വിലാസങ്ങൾ അല്ലെങ്കിൽ കമ്പനി പേരുകൾ:
 |  |
ഒരേ നഗരമോ കമ്പനിയോ ഇവിടെ മോട്ട്ലി വേരിയന്റുകളിൽ ഉണ്ടെന്ന് വ്യക്തമായി കാണാം, ഇത് ഭാവിയിൽ ഈ പട്ടികകളുമായി പ്രവർത്തിക്കുമ്പോൾ ധാരാളം പ്രശ്നങ്ങൾ സൃഷ്ടിക്കും. നിങ്ങൾ അൽപ്പം ചിന്തിച്ചാൽ, മറ്റ് മേഖലകളിൽ നിന്ന് സമാനമായ ജോലികളുടെ ധാരാളം ഉദാഹരണങ്ങൾ നിങ്ങൾക്ക് കണ്ടെത്താൻ കഴിയും.
അത്തരം വളഞ്ഞ ഡാറ്റ നിങ്ങൾക്ക് സ്ഥിരമായി വരുന്നതായി സങ്കൽപ്പിക്കുക, അതായത് ഇത് ഒറ്റത്തവണ “സ്വമേധയാ ശരിയാക്കുക, മറക്കുക” എന്ന കഥയല്ല, മറിച്ച് സ്ഥിരവും ധാരാളം സെല്ലുകളിലെയും ഒരു പ്രശ്നമാണ്.
എന്തുചെയ്യും? വളഞ്ഞ വാചകം 100500 തവണ സ്വമേധയാ മാറ്റി പകരം വയ്ക്കരുത്, "കണ്ടെത്തുക, മാറ്റിസ്ഥാപിക്കുക" എന്ന ബോക്സിലൂടെയോ ക്ലിക്ക് ചെയ്യുക വഴിയോ Ctrl+H?
അത്തരമൊരു സാഹചര്യത്തിൽ ആദ്യം മനസ്സിൽ വരുന്നത് തെറ്റായതും ശരിയായതുമായ ഓപ്ഷനുകൾ പൊരുത്തപ്പെടുത്തുന്നതിന് മുൻകൂട്ടി കംപൈൽ ചെയ്ത റഫറൻസ് ബുക്ക് അനുസരിച്ച് ഒരു കൂട്ടം മാറ്റിസ്ഥാപിക്കുക എന്നതാണ് - ഇതുപോലെ:
നിർഭാഗ്യവശാൽ, അത്തരമൊരു ടാസ്ക്കിന്റെ വ്യക്തമായ വ്യാപനത്തോടെ, മൈക്രോസോഫ്റ്റ് എക്സൽ അത് പരിഹരിക്കുന്നതിനുള്ള ലളിതമായ ബിൽറ്റ്-ഇൻ രീതികളില്ല. ആരംഭിക്കുന്നതിന്, VBA അല്ലെങ്കിൽ പവർ ക്വറിയിലെ മാക്രോകളുടെ രൂപത്തിൽ “കനത്ത പീരങ്കികൾ” ഉൾപ്പെടുത്താതെ, ഫോർമുലകൾ ഉപയോഗിച്ച് ഇത് എങ്ങനെ ചെയ്യാമെന്ന് നമുക്ക് നോക്കാം.
കേസ് 1. ബൾക്ക് ഫുൾ റീപ്ലേസ്‌മെന്റ്
താരതമ്യേന ലളിതമായ ഒരു കേസിൽ നമുക്ക് ആരംഭിക്കാം - പഴയ വളഞ്ഞ വാചകം പുതിയൊരെണ്ണം ഉപയോഗിച്ച് മാറ്റിസ്ഥാപിക്കേണ്ട ഒരു സാഹചര്യം. പൂർണ്ണമായി.
നമുക്ക് രണ്ട് ടേബിളുകൾ ഉണ്ടെന്ന് പറയാം:
ആദ്യത്തേതിൽ - കമ്പനികളുടെ യഥാർത്ഥ വൈവിധ്യമാർന്ന പേരുകൾ. രണ്ടാമത്തേതിൽ - കത്തിടപാടുകളുടെ ഒരു റഫറൻസ് പുസ്തകം. കമ്പനിയുടെ പേരിൽ ആദ്യ പട്ടികയിൽ കോളത്തിൽ നിന്ന് ഏതെങ്കിലും വാക്ക് കണ്ടെത്തിയാൽ കണ്ടെത്താൻ, അപ്പോൾ നിങ്ങൾ ഈ വളഞ്ഞ പേര് പൂർണ്ണമായും ശരിയായത് ഉപയോഗിച്ച് മാറ്റിസ്ഥാപിക്കേണ്ടതുണ്ട് - നിരയിൽ നിന്ന് പകരം രണ്ടാമത്തെ ലുക്ക്അപ്പ് ടേബിൾ.
സൗകര്യത്തിന്:
- രണ്ട് പട്ടികകളും ഒരു കീബോർഡ് കുറുക്കുവഴി ഉപയോഗിച്ച് ഡൈനാമിക് ("സ്മാർട്ട്") ആയി പരിവർത്തനം ചെയ്യപ്പെടുന്നു Ctrl+T അല്ലെങ്കിൽ ടീം തിരുകുക - പട്ടിക (തിരുകുക - പട്ടിക).
- ദൃശ്യമാകുന്ന ടാബിൽ കൺസ്ട്രക്ടർ (ഡിസൈൻ) പേരുള്ള ആദ്യ പട്ടിക ഡാറ്റ, രണ്ടാമത്തെ റഫറൻസ് പട്ടിക - പകരക്കാർ.
സൂത്രവാക്യത്തിന്റെ യുക്തി വിശദീകരിക്കാൻ, നമുക്ക് ദൂരെ നിന്ന് അല്പം പോകാം.
സെൽ A2-ൽ നിന്നുള്ള ആദ്യത്തെ കമ്പനിയെ ഉദാഹരണമായി എടുത്ത് ബാക്കി കമ്പനികളെക്കുറിച്ച് താൽക്കാലികമായി മറന്ന്, കോളത്തിൽ നിന്ന് ഏത് ഓപ്ഷൻ നിർണ്ണയിക്കാൻ ശ്രമിക്കാം കണ്ടെത്താൻ അവിടെ കണ്ടുമുട്ടുന്നു. ഇത് ചെയ്യുന്നതിന്, ഷീറ്റിന്റെ സ്വതന്ത്ര ഭാഗത്ത് ഏതെങ്കിലും ശൂന്യമായ സെൽ തിരഞ്ഞെടുത്ത് അവിടെ ഫംഗ്ഷൻ നൽകുക കണ്ടുപിടിക്കാൻ (കണ്ടെത്തുക):
തന്നിരിക്കുന്ന സബ്സ്ട്രിംഗ് ഉൾപ്പെടുത്തിയിട്ടുണ്ടോ എന്ന് ഈ ഫംഗ്ഷൻ നിർണ്ണയിക്കുന്നു (ആദ്യത്തെ ആർഗ്യുമെന്റ് കോളത്തിൽ നിന്നുള്ള എല്ലാ മൂല്യങ്ങളും ആണ് കണ്ടെത്താൻ) സോഴ്‌സ് ടെക്‌സ്‌റ്റിലേക്ക് (ഡാറ്റ ടേബിളിൽ നിന്നുള്ള ആദ്യത്തെ കമ്പനി) കൂടാതെ ടെക്സ്റ്റ് കണ്ടെത്തിയ പ്രതീകത്തിന്റെ ഓർഡിനൽ നമ്പർ അല്ലെങ്കിൽ സബ്സ്ട്രിംഗ് കണ്ടെത്തിയില്ലെങ്കിൽ ഒരു പിശക് ഔട്ട്‌പുട്ട് ചെയ്യണം.
ആദ്യത്തെ ആർഗ്യുമെന്റായി ഞങ്ങൾ ഒന്നല്ല, നിരവധി മൂല്യങ്ങൾ വ്യക്തമാക്കിയതിനാൽ, ഈ ഫംഗ്‌ഷനും ഫലമായി ഒരു മൂല്യമല്ല, 3 ഘടകങ്ങളുടെ ഒരു ശ്രേണിയാണ് നൽകുന്നത് എന്നതാണ് ഇവിടെയുള്ള തന്ത്രം. ഡൈനാമിക് അറേകളെ പിന്തുണയ്‌ക്കുന്ന Office 365-ന്റെ ഏറ്റവും പുതിയ പതിപ്പ് നിങ്ങളുടെ പക്കലില്ലെങ്കിൽ, ഈ ഫോർമുല നൽകിയ ശേഷം ക്ലിക്ക് ചെയ്യുക നൽകുക ഷീറ്റിൽ തന്നെ നിങ്ങൾ ഈ അറേ കാണും:
നിങ്ങൾക്ക് Excel-ന്റെ മുൻ പതിപ്പുകൾ ഉണ്ടെങ്കിൽ, ക്ലിക്ക് ചെയ്ത ശേഷം നൽകുക ഫല ശ്രേണിയിൽ നിന്നുള്ള ആദ്യ മൂല്യം മാത്രമേ ഞങ്ങൾ കാണൂ, അതായത് പിശക് #VALUE! (#മൂല്യം!).
നിങ്ങൾ ഭയപ്പെടേണ്ടതില്ല 🙂 വാസ്തവത്തിൽ, ഞങ്ങളുടെ ഫോർമുല പ്രവർത്തിക്കുന്നു, ഫോർമുല ബാറിൽ നൽകിയ ഫംഗ്ഷൻ തിരഞ്ഞെടുത്ത് കീ അമർത്തിയാൽ ഫലങ്ങളുടെ മുഴുവൻ ശ്രേണിയും നിങ്ങൾക്ക് തുടർന്നും കാണാൻ കഴിയും F9(അമർത്താൻ മറക്കരുത് Escഫോർമുലയിലേക്ക് മടങ്ങാൻ):
തത്ഫലമായുണ്ടാകുന്ന ഫലങ്ങളുടെ നിര അർത്ഥമാക്കുന്നത് യഥാർത്ഥ വളഞ്ഞ കമ്പനിയുടെ പേരിൽ എന്നാണ് (GK മൊറോസ്കോ OAO) ഒരു നിരയിലെ എല്ലാ മൂല്യങ്ങളുടെയും കണ്ടെത്താൻ രണ്ടാമത്തേത് മാത്രം കണ്ടെത്തി (മൊറോസ്കോ), കൂടാതെ തുടർച്ചയായി നാലാമത്തെ പ്രതീകം മുതൽ ആരംഭിക്കുന്നു.
ഇനി നമുക്ക് നമ്മുടെ ഫോർമുലയിലേക്ക് ഒരു ഫംഗ്ഷൻ ചേർക്കാം കാണുക(തിരയൽ):
ഈ പ്രവർത്തനത്തിന് മൂന്ന് ആർഗ്യുമെന്റുകളുണ്ട്:
- ആവശ്യമുള്ള മൂല്യം - നിങ്ങൾക്ക് ആവശ്യത്തിന് വലിയ സംഖ്യ ഉപയോഗിക്കാം (പ്രധാന കാര്യം അത് ഉറവിട ഡാറ്റയിലെ ഏത് വാചകത്തിന്റെയും ദൈർഘ്യം കവിയുന്നു എന്നതാണ്)
- കണ്ട_വെക്റ്റർ - ഞങ്ങൾ ആവശ്യമുള്ള മൂല്യത്തിനായി തിരയുന്ന ശ്രേണി അല്ലെങ്കിൽ ശ്രേണി. മുമ്പ് അവതരിപ്പിച്ച പ്രവർത്തനം ഇതാ കണ്ടുപിടിക്കാൻ, ഇത് ഒരു അറേ നൽകുന്നു {#VALUE!:4:#VALUE!}
- വെക്റ്റർ_ഫലം - ആവശ്യമുള്ള മൂല്യം അനുബന്ധ സെല്ലിൽ കണ്ടെത്തിയാൽ, മൂല്യം തിരികെ നൽകേണ്ട ശ്രേണി. കോളത്തിൽ നിന്നുള്ള ശരിയായ പേരുകൾ ഇതാ പകരം ഞങ്ങളുടെ റഫറൻസ് പട്ടിക.
ഇവിടെ പ്രധാനവും വ്യക്തമല്ലാത്തതുമായ സവിശേഷത പ്രവർത്തനമാണ് കാണുക കൃത്യമായ പൊരുത്തമില്ലെങ്കിൽ, എല്ലായ്‌പ്പോഴും ഏറ്റവും അടുത്തുള്ള ഏറ്റവും ചെറിയ (മുമ്പത്തെ) മൂല്യത്തിനായി നോക്കുക. അതിനാൽ, ആവശ്യമുള്ള മൂല്യമായി ഏതെങ്കിലും കനത്ത സംഖ്യ (ഉദാഹരണത്തിന്, 9999) വ്യക്തമാക്കുന്നതിലൂടെ, ഞങ്ങൾ നിർബന്ധിക്കും കാണുക {#VALUE!:4:#VALUE!} എന്ന ശ്രേണിയിലെ ഏറ്റവും ചെറിയ സംഖ്യയുള്ള (4) സെൽ കണ്ടെത്തി ഫല വെക്‌ടറിൽ നിന്ന് അനുബന്ധ മൂല്യം നൽകുക, അതായത് കോളത്തിൽ നിന്ന് ശരിയായ കമ്പനിയുടെ പേര് പകരം.
രണ്ടാമത്തെ സൂക്ഷ്മത, സാങ്കേതികമായി, ഞങ്ങളുടെ ഫോർമുല ഒരു അറേ ഫോർമുലയാണ്, കാരണം ഫംഗ്ഷൻ കണ്ടുപിടിക്കാൻ ഒന്നല്ല, മൂന്ന് മൂല്യങ്ങളുടെ ഒരു അറേ ഫലങ്ങളായി നൽകുന്നു. എന്നാൽ ചടങ്ങ് മുതൽ കാണുക ബോക്‌സിന് പുറത്ത് അറേകളെ പിന്തുണയ്‌ക്കുന്നു, തുടർന്ന് ഞങ്ങൾ ഈ ഫോർമുല ഒരു ക്ലാസിക് അറേ ഫോർമുലയായി നൽകേണ്ടതില്ല - ഒരു കീബോർഡ് കുറുക്കുവഴി ഉപയോഗിച്ച് Ctrl+മാറ്റം+നൽകുക. ലളിതമായ ഒന്ന് മതിയാകും നൽകുക.
അത്രയേയുള്ളൂ. നിങ്ങൾക്ക് ലോജിക് കിട്ടുമെന്ന് പ്രതീക്ഷിക്കുന്നു.
പൂർത്തിയായ ഫോർമുല നിരയുടെ ആദ്യ സെൽ ബി 2 ലേക്ക് കൈമാറാൻ ഇത് ശേഷിക്കുന്നു നിശ്ചിത - ഞങ്ങളുടെ ചുമതല പരിഹരിച്ചു!
തീർച്ചയായും, സാധാരണ (സ്മാർട്ടല്ല) പട്ടികകൾക്കൊപ്പം, ഈ ഫോർമുലയും മികച്ച രീതിയിൽ പ്രവർത്തിക്കുന്നു (കീയെക്കുറിച്ച് മറക്കരുത് F4 കൂടാതെ പ്രസക്തമായ ലിങ്കുകൾ ശരിയാക്കുന്നു):
കേസ് 2. ബൾക്ക് ഭാഗിക മാറ്റിസ്ഥാപിക്കൽ
ഈ കേസ് അൽപ്പം തന്ത്രപരമാണ്. വീണ്ടും നമുക്ക് രണ്ട് "സ്മാർട്ട്" ടേബിളുകൾ ഉണ്ട്:
വളച്ചൊടിച്ച് എഴുതിയ വിലാസങ്ങളുള്ള ആദ്യത്തെ പട്ടിക തിരുത്തേണ്ടതുണ്ട് (ഞാൻ അതിനെ വിളിച്ചു ഡാറ്റ 2). രണ്ടാമത്തെ പട്ടിക ഒരു റഫറൻസ് പുസ്തകമാണ്, അതനുസരിച്ച് നിങ്ങൾ വിലാസത്തിനുള്ളിലെ ഒരു സബ്സ്ട്രിംഗ് ഭാഗികമായി മാറ്റിസ്ഥാപിക്കേണ്ടതുണ്ട് (ഞാൻ ഈ പട്ടിക എന്ന് വിളിച്ചു. പകരക്കാർ 2).
ഇവിടെ അടിസ്ഥാനപരമായ വ്യത്യാസം നിങ്ങൾ യഥാർത്ഥ ഡാറ്റയുടെ ഒരു ഭാഗം മാത്രം മാറ്റിസ്ഥാപിക്കേണ്ടതുണ്ട് എന്നതാണ് - ഉദാഹരണത്തിന്, ആദ്യത്തെ വിലാസത്തിന് തെറ്റായ ഒരു വിലാസമുണ്ട് “സെന്റ്. പീറ്റേഴ്സ്ബർഗ്" വലതുവശത്ത് “സെന്റ്. പീറ്റേഴ്സ്ബർഗ്", ബാക്കിയുള്ള വിലാസം (സിപ്പ് കോഡ്, തെരുവ്, വീട്) അതേപടി വിടുക.
പൂർത്തിയായ സൂത്രവാക്യം ഇതുപോലെ കാണപ്പെടും (ധാരണയുടെ എളുപ്പത്തിനായി, ഞാൻ അതിനെ എത്ര വരികളായി വിഭജിച്ചു ആൾട്ട്+നൽകുക):
സ്റ്റാൻഡേർഡ് എക്സൽ ടെക്സ്റ്റ് ഫംഗ്ഷനാണ് ഇവിടെ പ്രധാന ജോലി ചെയ്യുന്നത് സബ്സിറ്റ്യൂട്ട് (പകരം), ഇതിന് 3 വാദങ്ങളുണ്ട്:
- ഉറവിട വാചകം - വിലാസ കോളത്തിൽ നിന്നുള്ള ആദ്യത്തെ വളഞ്ഞ വിലാസം
- നമ്മൾ എന്താണ് തിരയുന്നത് - ഇവിടെ ഞങ്ങൾ ഫംഗ്ഷൻ ഉപയോഗിച്ച് ട്രിക്ക് ഉപയോഗിക്കുന്നു കാണുക (തിരയൽ)നിരയിൽ നിന്ന് മൂല്യം പിൻവലിക്കാൻ മുമ്പത്തെ രീതിയിൽ നിന്ന് കണ്ടെത്താൻ, ഒരു വളഞ്ഞ വിലാസത്തിൽ ഒരു ശകലമായി ഉൾപ്പെടുത്തിയിരിക്കുന്നു.
- എന്താണ് മാറ്റിസ്ഥാപിക്കേണ്ടത് - അതേ രീതിയിൽ തന്നെ നിരയിൽ നിന്ന് അതിന് അനുയോജ്യമായ ശരിയായ മൂല്യം ഞങ്ങൾ കണ്ടെത്തുന്നു പകരം.
ഉപയോഗിച്ച് ഈ ഫോർമുല നൽകുക Ctrl+മാറ്റം+നൽകുക ഇവിടെയും ആവശ്യമില്ല, വാസ്തവത്തിൽ ഇത് ഒരു അറേ ഫോർമുല ആണെങ്കിലും.
കൂടാതെ, ഇത് വ്യക്തമായി കാണാം (മുമ്പത്തെ ചിത്രത്തിലെ #N/A പിശകുകൾ കാണുക) അത്തരമൊരു സൂത്രവാക്യത്തിന്, അതിന്റെ എല്ലാ ചാരുതയ്ക്കും, രണ്ട് പോരായ്മകളുണ്ട്:
- ഫംഗ്ഷൻ SUBSTITUTE കേസ് സെൻസിറ്റീവ് ആണ്, അതിനാൽ അവസാന വരിയിലെ "Spb" മാറ്റിസ്ഥാപിക്കൽ പട്ടികയിൽ കണ്ടെത്തിയില്ല. ഈ പ്രശ്നം പരിഹരിക്കാൻ, നിങ്ങൾക്ക് ഒന്നുകിൽ ഫംഗ്ഷൻ ഉപയോഗിക്കാം ZAMENIT (മാറ്റിസ്ഥാപിക്കുക), അല്ലെങ്കിൽ പ്രാഥമികമായി രണ്ട് പട്ടികകളും ഒരേ രജിസ്റ്ററിലേക്ക് കൊണ്ടുവരിക.
- വാചകം തുടക്കത്തിൽ ശരിയാണെങ്കിൽ അല്ലെങ്കിൽ അതിൽ പകരം വയ്ക്കാൻ ഒരു ശകലവുമില്ല (അവസാന വരി), അപ്പോൾ ഞങ്ങളുടെ ഫോർമുല ഒരു പിശക് എറിയുന്നു. ഫംഗ്ഷൻ ഉപയോഗിച്ച് പിശകുകൾ തടസ്സപ്പെടുത്തുകയും മാറ്റിസ്ഥാപിക്കുകയും ചെയ്യുന്നതിലൂടെ ഈ നിമിഷം നിർവീര്യമാക്കാനാകും IFERROR (IFERROR):
- ഒറിജിനൽ വാചകം ഉൾക്കൊള്ളുന്നുവെങ്കിൽ ഡയറക്ടറിയിൽ നിന്ന് ഒരേസമയം നിരവധി ശകലങ്ങൾ, അപ്പോൾ ഞങ്ങളുടെ ഫോർമുല അവസാനത്തേത് മാത്രം മാറ്റിസ്ഥാപിക്കുന്നു (എട്ടാമത്തെ വരിയിൽ, ലിഗോവ്സ്കി «അവന്യൂ« എന്നതിലേക്ക് മാറ്റി "pr-t", പക്ഷേ "എസ്-പിബി" on “സെന്റ്. പീറ്റേഴ്സ്ബർഗ്" ഇനി, കാരണം “എസ്-പിബി"ഡയറക്ടറിയിൽ ഉയർന്നതാണ്). ഞങ്ങളുടെ സ്വന്തം ഫോർമുല വീണ്ടും പ്രവർത്തിപ്പിക്കുന്നതിലൂടെ ഈ പ്രശ്നം പരിഹരിക്കാൻ കഴിയും, പക്ഷേ ഇതിനകം നിരയിൽ നിശ്ചിത:
സ്ഥലങ്ങളിൽ തികഞ്ഞതും ബുദ്ധിമുട്ടുള്ളതുമല്ല, എന്നാൽ അതേ മാനുവൽ മാറ്റിസ്ഥാപിക്കുന്നതിനേക്കാൾ വളരെ മികച്ചതാണ്, അല്ലേ? 🙂
PS
അടുത്ത ലേഖനത്തിൽ, മാക്രോകളും പവർ ക്വറിയും ഉപയോഗിച്ച് അത്തരമൊരു ബൾക്ക് സബ്സ്റ്റിറ്റ്യൂഷൻ എങ്ങനെ നടപ്പിലാക്കാമെന്ന് ഞങ്ങൾ കണ്ടെത്തും.
- വാചകം മാറ്റിസ്ഥാപിക്കാൻ SUBSTITUTE ഫംഗ്ഷൻ എങ്ങനെ പ്രവർത്തിക്കുന്നു
- കൃത്യമായ ഫംഗ്ഷൻ ഉപയോഗിച്ച് കൃത്യമായ വാചക പൊരുത്തങ്ങൾ കണ്ടെത്തുന്നു
- കേസ് സെൻസിറ്റീവ് തിരയലും പകരം വയ്ക്കലും (കേസ് സെൻസിറ്റീവ് VLOOKUP)