വിപുലമായ VLOOKUP ഉദാഹരണങ്ങൾ: മൾട്ടി-ക്രൈറ്റീരിയ തിരയൽ

പ്രവർത്തനത്തെക്കുറിച്ചുള്ള ഞങ്ങളുടെ ട്യൂട്ടോറിയലിന്റെ രണ്ടാം ഭാഗത്തിൽ VPR (VLOOKUP) Excel-ൽ, എല്ലാ ശക്തിയും നയിക്കാൻ നിങ്ങളെ സഹായിക്കുന്ന കുറച്ച് ഉദാഹരണങ്ങൾ ഞങ്ങൾ വിശകലനം ചെയ്യും VPR ഏറ്റവും അഭിലഷണീയമായ Excel ടാസ്ക്കുകൾ പരിഹരിക്കാൻ. ഈ സവിശേഷത എങ്ങനെ പ്രവർത്തിക്കുന്നു എന്നതിനെക്കുറിച്ചുള്ള അടിസ്ഥാന അറിവ് നിങ്ങൾക്ക് ഇതിനകം ഉണ്ടെന്ന് ഉദാഹരണങ്ങൾ അനുമാനിക്കുന്നു. ഇല്ലെങ്കിൽ, വാക്യഘടനയും അടിസ്ഥാന ഉപയോഗവും വിശദീകരിക്കുന്ന ഈ ട്യൂട്ടോറിയലിന്റെ ആദ്യ ഭാഗത്തിൽ നിന്ന് ആരംഭിക്കാൻ നിങ്ങൾക്ക് താൽപ്പര്യമുണ്ടാകാം. VPR. ശരി, നമുക്ക് ആരംഭിക്കാം.

• ഒന്നിലധികം മാനദണ്ഡങ്ങൾ ഉപയോഗിച്ച് Excel-ൽ തിരയുക
• VLOOKUP ഉപയോഗിച്ച് ഞങ്ങൾ 2, 3, മുതലായ മൂല്യങ്ങൾ എക്‌സ്‌ട്രാക്‌റ്റുചെയ്യുന്നു
• ആവശ്യമുള്ള മൂല്യത്തിന്റെ എല്ലാ ആവർത്തനങ്ങളും വീണ്ടെടുക്കുക
• അറിയപ്പെടുന്ന വരിയും നിരയും ഉപയോഗിച്ച് XNUMXD തിരയുക
• ഒരു ഫോർമുലയിൽ ഒന്നിലധികം VLOOKUP-കൾ ഉപയോഗിക്കുന്നു
• വ്യത്യസ്ത പട്ടികകളിൽ നിന്നുള്ള ഡാറ്റയുടെ ഡൈനാമിക് സബ്സ്റ്റിറ്റ്യൂഷൻ

ഒന്നിലധികം മാനദണ്ഡങ്ങൾ ഉപയോഗിച്ച് Excel-ൽ തിരയുക

ഫംഗ്ഷൻ VPR ഒരു ഡാറ്റാബേസിൽ ഒരു പ്രത്യേക മൂല്യത്തിനായി തിരയലുകൾ നടത്തുന്നതിനുള്ള ഒരു ശക്തമായ ഉപകരണമാണ് Excel. എന്നിരുന്നാലും, ഒരു പ്രധാന പരിമിതിയുണ്ട് - അതിന്റെ വാക്യഘടന നിങ്ങളെ ഒരു മൂല്യം മാത്രം തിരയാൻ അനുവദിക്കുന്നു. നിങ്ങൾക്ക് ഒന്നിലധികം വ്യവസ്ഥകൾ ഉപയോഗിച്ച് തിരയണമെങ്കിൽ എന്തുചെയ്യും? നിങ്ങൾ താഴെ പരിഹാരം കണ്ടെത്തും.

ഉദാഹരണം 1: 2 വ്യത്യസ്ത മാനദണ്ഡങ്ങൾ പ്രകാരം തിരയുക

ഞങ്ങൾക്ക് ഓർഡറുകളുടെ ഒരു ലിസ്റ്റ് ഉണ്ടെന്നും ഞങ്ങൾ കണ്ടെത്താൻ ആഗ്രഹിക്കുന്നുവെന്നും കരുതുക സാധനങ്ങളുടെ അളവ് (Qty.), രണ്ട് മാനദണ്ഡങ്ങളെ അടിസ്ഥാനമാക്കി - ഇടപാടുകാരന്റെ പേര് (ഉപഭോക്താവ്) മുതലായവ ഉൽപ്പന്നത്തിന്റെ പേര് (ഉൽപ്പന്നം). ചുവടെയുള്ള പട്ടികയിൽ നിന്ന് കാണാൻ കഴിയുന്നതുപോലെ, ഓരോ വാങ്ങലുകാരും നിരവധി തരം സാധനങ്ങൾ ഓർഡർ ചെയ്തതിനാൽ കാര്യം സങ്കീർണ്ണമാണ്:

പതിവ് പ്രവർത്തനം VPR ഈ സാഹചര്യത്തിൽ പ്രവർത്തിക്കില്ല, കാരണം നൽകിയിരിക്കുന്ന ലുക്കപ്പ് മൂല്യവുമായി പൊരുത്തപ്പെടുന്ന ആദ്യ മൂല്യം അത് തിരികെ നൽകും. ഉദാഹരണത്തിന്, നിങ്ങൾക്ക് ഒരു ഇനത്തിന്റെ അളവ് അറിയണമെങ്കിൽ മധുരപലഹാരങ്ങൾ'വാങ്ങുന്നയാൾ ഉത്തരവിട്ടു ജെറമി ഹിൽ, ഇനിപ്പറയുന്ന ഫോർമുല എഴുതുക:

=VLOOKUP(B1,$A$5:$C$14,3,FALSE)

=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)

- ഈ ഫോർമുല ഫലം നൽകും 15ഉൽപ്പന്നവുമായി പൊരുത്തപ്പെടുന്നു ആപ്പിൾ, കാരണം അത് പൊരുത്തപ്പെടുന്ന ആദ്യത്തെ മൂല്യമാണ്.

ഒരു ലളിതമായ പരിഹാരമുണ്ട് - ആവശ്യമുള്ള എല്ലാ മാനദണ്ഡങ്ങളും സംയോജിപ്പിക്കാൻ ഒരു അധിക നിര സൃഷ്ടിക്കുക. ഞങ്ങളുടെ ഉദാഹരണത്തിൽ, ഇവയാണ് നിരകൾ ഇടപാടുകാരന്റെ പേര് (ഉപഭോക്താവ്) മുതലായവ ഉൽപ്പന്നത്തിന്റെ പേര് (ഉൽപ്പന്നം). ലയിപ്പിച്ച കോളം എല്ലായ്‌പ്പോഴും തിരയൽ ശ്രേണിയിലെ ഇടത്തെ കോളം ആയിരിക്കണം എന്നത് മറക്കരുത്, കാരണം അത് ഇടത് കോളമാണ് ഫംഗ്‌ഷൻ ചെയ്യുന്നത് VPR ഒരു മൂല്യം തിരയുമ്പോൾ മുകളിലേക്ക് നോക്കുന്നു.

അതിനാൽ, നിങ്ങൾ പട്ടികയിലേക്ക് ഒരു സഹായ കോളം ചേർക്കുകയും അതിന്റെ എല്ലാ സെല്ലുകളിലും ഇനിപ്പറയുന്ന ഫോർമുല പകർത്തുകയും ചെയ്യുക: =B2&C2. സ്‌ട്രിംഗ് കൂടുതൽ വായിക്കാനാകുന്നതായിരിക്കണമെങ്കിൽ, നിങ്ങൾക്ക് സംയോജിത മൂല്യങ്ങളെ ഒരു സ്‌പെയ്‌സ് ഉപയോഗിച്ച് വേർതിരിക്കാം: =B2&» «&C2. അതിനുശേഷം, നിങ്ങൾക്ക് ഇനിപ്പറയുന്ന ഫോർമുല ഉപയോഗിക്കാം:

=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)

=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)

or

=VLOOKUP(B1,$A$7:$D$18,4,FALSE)

=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)

സെൽ എവിടെയാണ് B1 ആർഗ്യുമെന്റിന്റെ സംയോജിത മൂല്യം അടങ്ങിയിരിക്കുന്നു ലുക്ക്അപ്പ്_മൂല്യം (lookup_value) കൂടാതെ 4 - വാദം ചൊല്_ഇംദെക്സ_നുമ് (column_number), അതായത് വീണ്ടെടുക്കേണ്ട ഡാറ്റ അടങ്ങുന്ന നിരയുടെ എണ്ണം.

ഉദാഹരണം 2: മറ്റൊരു ഷീറ്റിൽ ടേബിൾ കാണുന്ന രണ്ട് മാനദണ്ഡങ്ങളാൽ VLOOKUP

മറ്റൊരു ഷീറ്റിലോ മറ്റൊരു Excel വർക്ക്ബുക്കിലോ സ്ഥിതി ചെയ്യുന്ന രണ്ടാമത്തെ പട്ടികയിൽ നിന്ന് (ലുക്ക്അപ്പ് ടേബിൾ) ഡാറ്റ ചേർത്തുകൊണ്ട് നിങ്ങൾക്ക് പ്രധാന പട്ടിക (മെയിൻ ടേബിൾ) അപ്ഡേറ്റ് ചെയ്യണമെങ്കിൽ, നിങ്ങൾ ചേർക്കുന്ന ഫോർമുലയിൽ നിങ്ങൾക്ക് ആവശ്യമുള്ള മൂല്യം നേരിട്ട് ശേഖരിക്കാനാകും. പ്രധാന മേശയിലേക്ക്.

മുമ്പത്തെ ഉദാഹരണത്തിലെന്നപോലെ, സംയോജിത മൂല്യങ്ങളുള്ള ലുക്ക്അപ്പ് പട്ടികയിൽ നിങ്ങൾക്ക് ഒരു സഹായ കോളം ആവശ്യമാണ്. ഈ കോളം തിരയൽ ശ്രേണിയിലെ ഇടതുവശത്തുള്ള കോളമായിരിക്കണം.

അതിനാൽ ഫോർമുല കൂടെ VPR ഇതുപോലെയായിരിക്കാം:

=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)

=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)

ഇവിടെ, B, C നിരകളിൽ യഥാക്രമം ഉപഭോക്തൃ നാമങ്ങളും ഉൽപ്പന്ന നാമങ്ങളും ലിങ്കും അടങ്ങിയിരിക്കുന്നു ഓർഡറുകൾ!$A&$2:$D$2 മറ്റൊരു ഷീറ്റിൽ നോക്കാൻ ഒരു പട്ടിക നിർവചിക്കുന്നു.

ഫോർമുല കൂടുതൽ വായിക്കാനാകുന്നതാക്കാൻ, നിങ്ങൾക്ക് കാഴ്ച ശ്രേണിക്ക് ഒരു പേര് നൽകാം, തുടർന്ന് ഫോർമുല വളരെ ലളിതമായി കാണപ്പെടും:

=VLOOKUP(B2&" "&C2,Orders,4,FALSE)

=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)

ഫോർമുല പ്രവർത്തിക്കുന്നതിന്, നിങ്ങൾ നോക്കുന്ന പട്ടികയുടെ ഇടതുവശത്തെ കോളത്തിലെ മൂല്യങ്ങൾ തിരയൽ മാനദണ്ഡത്തിലെ അതേ രീതിയിൽ സംയോജിപ്പിക്കണം. മുകളിലുള്ള ചിത്രത്തിൽ, ഞങ്ങൾ u2bu2band മൂല്യങ്ങൾ സംയോജിപ്പിച്ച് അവയ്ക്കിടയിൽ ഒരു ഇടം നൽകി, അതുപോലെ തന്നെ ഫംഗ്ഷന്റെ ആദ്യ ആർഗ്യുമെന്റിൽ നിങ്ങൾ ചെയ്യേണ്ടത് (BXNUMX& "" & CXNUMX).

ഓർമ്മിക്കുക! ഫംഗ്ഷൻ VPR 255 പ്രതീകങ്ങളായി പരിമിതപ്പെടുത്തിയിരിക്കുന്നു, ഇതിന് 255 പ്രതീകങ്ങളിൽ കൂടുതൽ ദൈർഘ്യമുള്ള ഒരു മൂല്യത്തിനായി തിരയാൻ കഴിയില്ല. ഇത് മനസ്സിൽ വയ്ക്കുക, ആവശ്യമുള്ള മൂല്യത്തിന്റെ ദൈർഘ്യം ഈ പരിധി കവിയുന്നില്ലെന്ന് ഉറപ്പാക്കുക.

ഒരു ഓക്സിലറി കോളം ചേർക്കുന്നത് ഏറ്റവും ഗംഭീരവും എല്ലായ്പ്പോഴും സ്വീകാര്യമല്ലാത്തതുമായ പരിഹാരമല്ലെന്ന് ഞാൻ സമ്മതിക്കുന്നു. സഹായ കോളം കൂടാതെ നിങ്ങൾക്ക് ഇത് ചെയ്യാൻ കഴിയും, എന്നാൽ അതിന് ഫംഗ്‌ഷനുകളുടെ സംയോജനമുള്ള കൂടുതൽ സങ്കീർണ്ണമായ ഫോർമുല ആവശ്യമാണ് INDEX (INDEX) കൂടാതെ മത്സരം (കൂടുതൽ വെളിപ്പെടുത്തിയത്).

VLOOKUP ഉപയോഗിച്ച് ഞങ്ങൾ 2, 3, മുതലായ മൂല്യങ്ങൾ എക്‌സ്‌ട്രാക്‌റ്റുചെയ്യുന്നു

നിങ്ങൾക്കത് ഇതിനകം അറിയാം VPR പൊരുത്തപ്പെടുന്ന ഒരു മൂല്യം മാത്രമേ നൽകാനാകൂ, കൂടുതൽ കൃത്യമായി പറഞ്ഞാൽ, ആദ്യം കണ്ടെത്തിയത്. എന്നാൽ കണ്ട അറേയിൽ ഈ മൂല്യം പല പ്രാവശ്യം ആവർത്തിക്കുകയും അവയിൽ 2-ാമത്തേതോ 3-ാമത്തെയോ എക്‌സ്‌ട്രാക്‌റ്റുചെയ്യാൻ നിങ്ങൾ ആഗ്രഹിക്കുന്നെങ്കിലോ? എല്ലാ മൂല്യങ്ങളും ആണെങ്കിലോ? പ്രശ്നം സങ്കീർണ്ണമാണെന്ന് തോന്നുന്നു, പക്ഷേ പരിഹാരം നിലവിലുണ്ട്!

പട്ടികയുടെ ഒരു കോളത്തിൽ ഉപഭോക്താക്കളുടെ പേരുകളും (ഉപഭോക്താവിന്റെ പേര്) മറ്റൊരു കോളത്തിൽ അവർ വാങ്ങിയ ഉൽപ്പന്നങ്ങളും (ഉൽപ്പന്നം) അടങ്ങിയിരിക്കുന്നു. നൽകിയിരിക്കുന്ന ഉപഭോക്താവ് വാങ്ങിയ 2, 3, 4 ഇനങ്ങൾ കണ്ടെത്താൻ ശ്രമിക്കാം.

നിരയ്ക്ക് മുമ്പായി ഒരു സഹായ കോളം ചേർക്കുക എന്നതാണ് ഏറ്റവും എളുപ്പമുള്ള മാർഗം ഉപഭോക്താവിന്റെ പേര് ഓരോ പേരിന്റെയും ആവർത്തന നമ്പർ ഉപയോഗിച്ച് ഉപഭോക്തൃ നാമങ്ങൾ പൂരിപ്പിക്കുക, ഉദാഹരണത്തിന്, ജോൺ ഡോ 1, ജോൺ ഡോ 2 മുതലായവ. ഫംഗ്ഷൻ ഉപയോഗിച്ച് ഞങ്ങൾ നമ്പറിംഗ് ഉപയോഗിച്ച് ട്രിക്ക് ചെയ്യും COUNTIF (COUNTIF), ഉപഭോക്തൃ പേരുകൾ കോളം B-ൽ ഉള്ളതിനാൽ:

=B2&COUNTIF($B$2:B2,B2)

=B2&СЧЁТЕСЛИ($B$2:B2;B2)

അതിനുശേഷം നിങ്ങൾക്ക് സാധാരണ പ്രവർത്തനം ഉപയോഗിക്കാം VPRആവശ്യമായ ഓർഡർ കണ്ടെത്താൻ. ഉദാഹരണത്തിന്:

• കണ്ടെത്തുക 2-ാം ഉപഭോക്താവ് ഓർഡർ ചെയ്ത ഇനം ഡാൻ ബ്രൗൺ:

  =VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ)

• കണ്ടെത്തുക 3-ാം ഉപഭോക്താവ് ഓർഡർ ചെയ്ത ഇനം ഡാൻ ബ്രൗൺ:

  =VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)

  =ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)

വാസ്തവത്തിൽ, ഇനിപ്പറയുന്ന ചിത്രത്തിൽ കാണിച്ചിരിക്കുന്നതുപോലെ നിങ്ങൾക്ക് വാചകത്തിന് പകരം ഒരു സെൽ റഫറൻസ് ലുക്ക്അപ്പ് മൂല്യമായി നൽകാം:

നിങ്ങൾ തിരയുകയാണെങ്കിൽ മാത്രം 2-e ആവർത്തനം, കൂടുതൽ സങ്കീർണ്ണമായ ഒരു ഫോർമുല സൃഷ്ടിച്ചുകൊണ്ട് നിങ്ങൾക്ക് സഹായ കോളം കൂടാതെ ഇത് ചെയ്യാൻ കഴിയും:

=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")

=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")

ഈ ഫോർമുലയിൽ:

• $F$2 - വാങ്ങുന്നയാളുടെ പേര് അടങ്ങിയ ഒരു സെൽ (അത് മാറ്റമില്ല, ദയവായി ശ്രദ്ധിക്കുക - ലിങ്ക് കേവലമാണ്);
• $ ബി $ - കോളം ഉപഭോക്താവിന്റെ പേര്;
• Table4 - നിങ്ങളുടെ ടേബിൾ (ഈ സ്ഥലവും ഒരു സാധാരണ ശ്രേണി ആകാം);
• $ C16 - നിങ്ങളുടെ പട്ടികയുടെയോ ശ്രേണിയുടെയോ അവസാന സെൽ.

ഈ സൂത്രവാക്യം രണ്ടാമത്തെ പൊരുത്തപ്പെടുന്ന മൂല്യം മാത്രമേ കണ്ടെത്തൂ. നിങ്ങൾക്ക് ശേഷിക്കുന്ന ആവർത്തനങ്ങൾ എക്‌സ്‌ട്രാക്‌റ്റ് ചെയ്യണമെങ്കിൽ, മുമ്പത്തെ പരിഹാരം ഉപയോഗിക്കുക.

നിങ്ങൾക്ക് എല്ലാ പൊരുത്തങ്ങളുടെയും ഒരു ലിസ്റ്റ് വേണമെങ്കിൽ - ഫംഗ്ഷൻ VPR ഇത് ഒരു സഹായി അല്ല, കാരണം ഇത് ഒരു സമയത്ത് ഒരു മൂല്യം മാത്രം നൽകുന്നു - കാലയളവ്. എന്നാൽ Excel-ന് ഒരു ഫംഗ്ഷൻ ഉണ്ട് INDEX (INDEX), ഈ ടാസ്ക്കിനെ എളുപ്പത്തിൽ നേരിടാൻ കഴിയും. അത്തരമൊരു ഫോർമുല എങ്ങനെയിരിക്കും, ഇനിപ്പറയുന്ന ഉദാഹരണത്തിൽ നിങ്ങൾ പഠിക്കും.

ആവശ്യമുള്ള മൂല്യത്തിന്റെ എല്ലാ ആവർത്തനങ്ങളും വീണ്ടെടുക്കുക

മുകളിൽ പറഞ്ഞ പോലെ VPR സ്കാൻ ചെയ്ത ശ്രേണിയിൽ നിന്ന് എല്ലാ തനിപ്പകർപ്പ് മൂല്യങ്ങളും വേർതിരിച്ചെടുക്കാൻ കഴിയില്ല. ഇത് ചെയ്യുന്നതിന്, നിങ്ങൾക്ക് കുറച്ച് കൂടുതൽ സങ്കീർണ്ണമായ ഫോർമുല ആവശ്യമാണ്, ഇത് പോലുള്ള നിരവധി Excel ഫംഗ്ഷനുകൾ ഉൾക്കൊള്ളുന്നു INDEX (INDEX), ചെറുത് (ചെറുത്) കൂടാതെ വരിയിൽ (ലൈൻ)

ഉദാഹരണത്തിന്, ചുവടെയുള്ള ഫോർമുല B2:B2 ശ്രേണിയിലെ സെൽ F16-ൽ നിന്ന് മൂല്യത്തിന്റെ എല്ലാ ആവർത്തനങ്ങളും കണ്ടെത്തുകയും C നിരയിലെ അതേ വരികളിൽ നിന്ന് ഫലം നൽകുകയും ചെയ്യുന്നു.

{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}

{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}

ഈ അറേ ഫോർമുല സെല്ലുകൾ പോലെയുള്ള ഒന്നിലധികം സെല്ലുകളിലേക്ക് നൽകുക F4: F8ചുവടെയുള്ള ചിത്രത്തിൽ കാണിച്ചിരിക്കുന്നതുപോലെ. സെല്ലുകളുടെ എണ്ണം തിരഞ്ഞ മൂല്യത്തിന്റെ പരമാവധി ആവർത്തനങ്ങളുടെ എണ്ണത്തിന് തുല്യമോ അതിലധികമോ ആയിരിക്കണം. ക്ലിക്ക് ചെയ്യാൻ മറക്കരുത് Ctrl+Shift+Enterഅറേ ഫോർമുല ശരിയായി നൽകുന്നതിന്.

ഇത് എങ്ങനെ പ്രവർത്തിക്കുന്നുവെന്ന് മനസിലാക്കാൻ നിങ്ങൾക്ക് താൽപ്പര്യമുണ്ടെങ്കിൽ, ഫോർമുലയുടെ വിശദാംശങ്ങളിലേക്ക് അൽപ്പം കടന്നുപോകാം:

ഭാഗം XX:

IF($F$2=B2:B16,ROW(C2:C16)-1,"")

ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")

$F$2=B2:B16 - സെൽ F2 ലെ മൂല്യം B2:B16 ശ്രേണിയിലെ ഓരോ മൂല്യങ്ങളുമായി താരതമ്യം ചെയ്യുക. ഒരു പൊരുത്തം കണ്ടെത്തിയാൽ, പദപ്രയോഗം STRING(C2:C16)-1 അനുബന്ധ വരിയുടെ എണ്ണം നൽകുന്നു (മൂല്യം -1 തലക്കെട്ട് ലൈൻ ഉൾപ്പെടുത്താതിരിക്കാൻ നിങ്ങളെ അനുവദിക്കുന്നു). പൊരുത്തങ്ങളൊന്നുമില്ലെങ്കിൽ, പ്രവർത്തനം IF (IF) ഒരു ശൂന്യമായ സ്ട്രിംഗ് നൽകുന്നു.

പ്രവർത്തന ഫലം IF (IF) അത്തരമൊരു തിരശ്ചീന ശ്രേണി ഉണ്ടായിരിക്കും: {1,"",3,"",5,"","","","","","",12,"","",""}

ഭാഗം XX:

ROW()-3

СТРОКА()-3

ഇവിടെ പ്രവർത്തനം വരിയിൽ (LINE) ഒരു അധിക കൗണ്ടറായി പ്രവർത്തിക്കുന്നു. ഫോർമുല F4:F9 സെല്ലുകളിലേക്ക് പകർത്തിയതിനാൽ, ഞങ്ങൾ നമ്പർ കുറയ്ക്കുന്നു 3 മൂല്യം ലഭിക്കുന്നതിന് ഫംഗ്ഷൻ ഫലത്തിൽ നിന്ന് 1 സെല്ലിൽ F4 (ലൈൻ 4, കുറയ്ക്കുക 3) ലഭിക്കാൻ 2 സെല്ലിൽ F5 (ലൈൻ 5, കുറയ്ക്കുക 3) തുടങ്ങിയവ.

ഭാഗം XX:

SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ഫംഗ്ഷൻ ചെറുത് (ചെറുത്) തിരികെ നൽകുന്നു n-oh ഡാറ്റ അറേയിലെ ഏറ്റവും ചെറിയ മൂല്യം. ഞങ്ങളുടെ കാര്യത്തിൽ, ഏത് സ്ഥാനമാണ് (ഏറ്റവും ചെറിയതിൽ നിന്ന്) തിരികെ നൽകേണ്ടതെന്ന് ഫംഗ്ഷൻ നിർണ്ണയിക്കുന്നു വരിയിൽ (LINE) (ഭാഗം 2 കാണുക). അതിനാൽ, ഒരു സെല്ലിനായി F4 ഫംഗ്ഷൻ ചെറുത്({array},1) വരുമാനം 1-ാം (ഏറ്റവും ചെറിയ) അറേ ഘടകം, അതായത് 1. സെല്ലിനായി F5 വരുമാനം 2-ാം അറേയിലെ ഏറ്റവും ചെറിയ ഘടകം, അതായത് 3, തുടങ്ങിയവ.

ഭാഗം XX:

INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))

ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))

ഫംഗ്ഷൻ INDEX (INDEX) ഒരു അറേയിലെ ഒരു നിർദ്ദിഷ്ട സെല്ലിന്റെ മൂല്യം നൽകുന്നു സി 2: സി 16. സെല്ലിനായി F4 ഫംഗ്ഷൻ സൂചിക ($C$2:$C$16) തിരിച്ചു വരും ആപ്പിൾവേണ്ടി F5 ഫംഗ്ഷൻ സൂചിക ($C$2:$C$16) തിരിച്ചു വരും മധുരപലഹാരങ്ങൾ' ഇത്യാദി.

ഭാഗം XX:

IFERROR()

ЕСЛИОШИБКА()

അവസാനമായി, ഞങ്ങൾ ഫംഗ്ഷനിൽ ഫോർമുല ഇട്ടു IFERROR (IFERROR), കാരണം പിശക് സന്ദേശത്തിൽ നിങ്ങൾ തൃപ്തനാകാൻ സാധ്യതയില്ല #എ.ടി (#N/A) ഫോർമുല പകർത്തിയ സെല്ലുകളുടെ എണ്ണം കാണുന്ന ശ്രേണിയിലെ ഡ്യൂപ്ലിക്കേറ്റ് മൂല്യങ്ങളുടെ എണ്ണത്തേക്കാൾ കുറവാണെങ്കിൽ.

അറിയപ്പെടുന്ന വരിയും നിരയും ഉപയോഗിച്ച് XNUMXD തിരയുക

Excel-ൽ ഒരു XNUMXD തിരയൽ നടത്തുന്നത് അറിയപ്പെടുന്ന ഒരു വരിയും കോളവും ഉപയോഗിച്ച് ഒരു മൂല്യത്തിനായി തിരയുന്നത് ഉൾപ്പെടുന്നു. മറ്റൊരു വിധത്തിൽ പറഞ്ഞാൽ, ഒരു പ്രത്യേക വരിയുടെയും നിരയുടെയും കവലയിൽ നിങ്ങൾ സെൽ മൂല്യം എക്‌സ്‌ട്രാക്‌റ്റുചെയ്യുന്നു.

അതിനാൽ, നമുക്ക് നമ്മുടെ ടേബിളിലേക്ക് തിരിയുകയും ഒരു ഫംഗ്ഷൻ ഉപയോഗിച്ച് ഒരു ഫോർമുല എഴുതുകയും ചെയ്യാം VPR, മാർച്ചിൽ വിൽക്കുന്ന നാരങ്ങയുടെ വിലയെക്കുറിച്ചുള്ള വിവരങ്ങൾ കണ്ടെത്തും.

ഒരു XNUMXD തിരയൽ നടത്താൻ നിരവധി മാർഗങ്ങളുണ്ട്. ഓപ്ഷനുകൾ പരിശോധിച്ച് നിങ്ങൾക്ക് ഏറ്റവും അനുയോജ്യമായത് തിരഞ്ഞെടുക്കുക.

VLOOKUP, MATCH ഫംഗ്‌ഷനുകൾ

നിങ്ങൾക്ക് ഒരു കൂട്ടം ഫംഗ്ഷനുകൾ ഉപയോഗിക്കാം VPR (VLOOKUP) കൂടാതെ കൂടുതൽ തുറന്നുകാട്ടപ്പെട്ടു (MATCH) ഫീൽഡുകളുടെ കവലയിൽ മൂല്യം കണ്ടെത്താൻ ഉൽപ്പന്നത്തിന്റെ പേര് (സ്ട്രിംഗ്) കൂടാതെ മാസം സംശയാസ്പദമായ ശ്രേണിയുടെ (നിര)

=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)

=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)

മുകളിലുള്ള സൂത്രവാക്യം ഒരു സാധാരണ പ്രവർത്തനമാണ് VPR, A2 മുതൽ A9 വരെയുള്ള സെല്ലുകളിൽ "ലെമൺസ്" എന്ന മൂല്യത്തിന്റെ കൃത്യമായ പൊരുത്തത്തിനായി നോക്കുന്നു. എന്നാൽ മാർച്ചിലെ വിൽപ്പന ഏത് കോളത്തിലാണ് എന്ന് നിങ്ങൾക്ക് അറിയാത്തതിനാൽ, മൂന്നാം ഫംഗ്ഷൻ ആർഗ്യുമെന്റിനായി കോളം നമ്പർ സജ്ജീകരിക്കാൻ നിങ്ങൾക്ക് കഴിയില്ല. VPR. പകരം, ഫംഗ്ഷൻ ഉപയോഗിക്കുന്നു കൂടുതൽ തുറന്നുകാട്ടപ്പെട്ടുഈ കോളം നിർവ്വചിക്കാൻ.

MATCH("Mar",$A$1:$I$1,0)

ПОИСКПОЗ("Mar";$A$1:$I$1;0)

മനുഷ്യ ഭാഷയിലേക്ക് വിവർത്തനം ചെയ്ത ഈ ഫോർമുല അർത്ഥമാക്കുന്നത്:

• ഞങ്ങൾ "മാർ" എന്ന കഥാപാത്രങ്ങൾക്കായി തിരയുകയാണ് - വാദം ലുക്ക്അപ്പ്_മൂല്യം (ലുക്ക്അപ്പ്_മൂല്യം);
• A1 മുതൽ I1 വരെയുള്ള സെല്ലുകളിൽ നോക്കുന്നു - വാദം ലുക്ക്അപ്പ്_അറേ (ലുക്ക്അപ്പ്_അറേ);
• കൃത്യമായ പൊരുത്തം തിരികെ നൽകുന്നു - വാദം പൊരുത്തം_തരം (പൊരുത്തം_തരം).

ഉപയോഗിക്കുന്നു 0 മൂന്നാമത്തെ ആർഗ്യുമെന്റിൽ, നിങ്ങൾ ഫംഗ്‌ഷനുകൾ പറയുന്നു കൂടുതൽ തുറന്നുകാട്ടപ്പെട്ടു നിങ്ങൾ തിരയുന്ന മൂല്യവുമായി കൃത്യമായി പൊരുത്തപ്പെടുന്ന ആദ്യ മൂല്യത്തിനായി നോക്കുക. ഇത് മൂല്യത്തിന് തുല്യമാണ് തെറ്റായ (FALSE) നാലാമത്തെ വാദത്തിന് VPR.

ദ്വിമാന തിരയൽ അല്ലെങ്കിൽ ദ്വിദിശ തിരയൽ എന്നും അറിയപ്പെടുന്ന Excel-ൽ ഒരു ടൂ-വേ സെർച്ച് ഫോർമുല സൃഷ്ടിക്കുന്നത് ഇങ്ങനെയാണ്.

SUMPRODUCT പ്രവർത്തനം

ഫംഗ്ഷൻ SUMPRODUCT (SUMPRODUCT) തിരഞ്ഞെടുത്ത അറേകളുടെ ഉൽപ്പന്നങ്ങളുടെ ആകെത്തുക നൽകുന്നു:

=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)

=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX, MATCH ഫംഗ്‌ഷനുകൾ

അടുത്ത ലേഖനത്തിൽ ഞാൻ ഈ പ്രവർത്തനങ്ങൾ വിശദമായി വിശദീകരിക്കും, അതിനാൽ ഇപ്പോൾ നിങ്ങൾക്ക് ഈ ഫോർമുല പകർത്താൻ കഴിയും:

=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))

=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))

പേരിട്ട ശ്രേണികളും ഇന്റർസെക്ഷൻ ഓപ്പറേറ്ററും

നിങ്ങൾ ആ സങ്കീർണ്ണമായ Excel ഫോർമുലകളിലല്ലെങ്കിൽ, ഈ ദൃശ്യപരവും അവിസ്മരണീയവുമായ മാർഗ്ഗം നിങ്ങൾക്ക് ഇഷ്ടപ്പെട്ടേക്കാം:

1. പട്ടിക തിരഞ്ഞെടുക്കുക, ടാബ് തുറക്കുക സൂത്രവാക്യങ്ങൾ (സൂത്രവാക്യങ്ങൾ) ക്ലിക്ക് ചെയ്യുക തിരഞ്ഞെടുക്കലിൽ നിന്ന് സൃഷ്ടിക്കുക (തിരഞ്ഞെടുപ്പിൽ നിന്ന് സൃഷ്ടിക്കുക).
2. ബോക്സുകൾ പരിശോധിക്കുക മുകളിലെ നിര (മുകളിലുള്ള വരിയിൽ) കൂടാതെ ഇടത് കോളം (ഇടതുവശത്തുള്ള നിരയിൽ). നിങ്ങളുടെ സ്‌പ്രെഡ്‌ഷീറ്റിന്റെ മുകളിലെ വരിയിലും ഇടത് കോളത്തിലും ഉള്ള മൂല്യങ്ങളിൽ നിന്ന് Microsoft Excel ശ്രേണികൾക്ക് പേരുകൾ നൽകും. ഇപ്പോൾ നിങ്ങൾക്ക് ഈ പേരുകൾ ഉപയോഗിച്ച് ഫോർമുലകൾ സൃഷ്ടിക്കാതെ നേരിട്ട് തിരയാനാകും.
3. ഏതെങ്കിലും ശൂന്യമായ സെല്ലിൽ, എഴുതുക = വരി_നാമം കോളം_നാമം, ഉദാഹരണത്തിന് ഇതുപോലെ:

   =നാരങ്ങ മാർ

   … അല്ലെങ്കിൽ തിരിച്ചും:

   =മാർ ലെമൺസ്

   വരിയുടെയും നിരയുടെയും പേരുകൾ ഒരു സ്പേസ് കൊണ്ട് വേർതിരിക്കണമെന്ന് ഓർമ്മിക്കുക, ഈ സാഹചര്യത്തിൽ ഇന്റർസെക്ഷൻ ഓപ്പറേറ്റർ പോലെ പ്രവർത്തിക്കുന്നു.

നിങ്ങൾ ഒരു പേര് നൽകുമ്പോൾ, നിങ്ങൾ ഒരു ഫോർമുല നൽകുമ്പോൾ പോലെ, പൊരുത്തപ്പെടുന്ന പേരുകളുടെ ലിസ്റ്റ് ഉള്ള ഒരു ടൂൾടിപ്പ് Microsoft Excel കാണിക്കും.

4. അമർത്തുക നൽകുക ഫലം പരിശോധിക്കുക

പൊതുവേ, മുകളിൽ പറഞ്ഞ രീതികളിൽ ഏതാണ് നിങ്ങൾ തിരഞ്ഞെടുക്കുന്നത്, ഒരു ദ്വിമാന തിരയലിന്റെ ഫലം സമാനമായിരിക്കും:

ഒരു ഫോർമുലയിൽ ഒന്നിലധികം VLOOKUP-കൾ ഉപയോഗിക്കുന്നു

പ്രധാന പട്ടികയ്ക്കും ലുക്ക്അപ്പ് ടേബിളിനും പൊതുവായ ഒരു കോളം ഇല്ല, ഇത് സാധാരണ ഫംഗ്ഷൻ ഉപയോഗിക്കുന്നതിൽ നിന്ന് നിങ്ങളെ തടയുന്നു VPR. എന്നിരുന്നാലും, ഞങ്ങൾക്ക് താൽപ്പര്യമുള്ള വിവരങ്ങൾ അടങ്ങിയിട്ടില്ലാത്ത മറ്റൊരു പട്ടികയുണ്ട്, പക്ഷേ പ്രധാന പട്ടികയും ലുക്ക്അപ്പ് ടേബിളും ഉള്ള ഒരു പൊതു നിരയുണ്ട്.

ഇനി പറയുന്ന ഉദാഹരണം നോക്കാം. ഞങ്ങൾക്ക് ഒരു കോളമുള്ള ഒരു പ്രധാന പട്ടികയുണ്ട് SKU (പുതിയത്), മറ്റൊരു പട്ടികയിൽ നിന്ന് അനുബന്ധ വിലകളുള്ള ഒരു കോളം ചേർക്കാൻ നിങ്ങൾ ആഗ്രഹിക്കുന്നിടത്ത്. കൂടാതെ, ഞങ്ങൾക്ക് 2 ലുക്ക്അപ്പ് ടേബിളുകളുണ്ട്. ആദ്യത്തേതിൽ (ലുക്ക്അപ്പ് ടേബിൾ 1) പുതുക്കിയ നമ്പറുകൾ അടങ്ങിയിരിക്കുന്നു SKU (പുതിയത്) ഉൽപ്പന്ന നാമങ്ങളും, രണ്ടാമത്തേത് (ലുക്ക്അപ്പ് ടേബിൾ 2) - ഉൽപ്പന്ന നാമങ്ങളും പഴയ നമ്പറുകളും SKU (പഴയത്).

രണ്ടാമത്തെ ലുക്ക്അപ്പ് ടേബിളിൽ നിന്ന് പ്രധാന ടേബിളിലേക്ക് വിലകൾ ചേർക്കുന്നതിന്, നിങ്ങൾ ഇരട്ട എന്നറിയപ്പെടുന്ന ഒരു പ്രവർത്തനം നടത്തണം VPR അല്ലെങ്കിൽ നെസ്റ്റഡ് VPR.

1. ഒരു ഫംഗ്ഷൻ എഴുതുക VPR, പട്ടികയിൽ ഉൽപ്പന്നത്തിന്റെ പേര് കണ്ടെത്തുന്നു ലുക്ക്അപ്പ് ടേബിൾ 1ഉപയോഗിച്ച് കേരളമല്ലെന്ന്, ആവശ്യമുള്ള മൂല്യം പോലെ:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   ഇവിടെ പുതിയ_എസ്.കെ.യു - പേരുള്ള ശ്രേണി $A:$B പട്ടികയിൽ ലുക്ക്അപ്പ് ടേബിൾ 1, 2 - ഇതാണ് നിര ബി, അതിൽ സാധനങ്ങളുടെ പേരുകൾ അടങ്ങിയിരിക്കുന്നു (മുകളിലുള്ള ചിത്രം കാണുക)

2. ഒരു പട്ടികയിൽ നിന്ന് വിലകൾ ചേർക്കാൻ ഒരു ഫോർമുല എഴുതുക ലുക്ക്അപ്പ് ടേബിൾ 2 അറിയപ്പെടുന്ന ഉൽപ്പന്ന പേരുകൾ അടിസ്ഥാനമാക്കി. ഇത് ചെയ്യുന്നതിന്, നിങ്ങൾ നേരത്തെ സൃഷ്ടിച്ച ഫോർമുല പുതിയ ഫംഗ്ഷന്റെ ലുക്ക്അപ്പ് മൂല്യമായി ഒട്ടിക്കുക VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   ഇവിടെ വില - പേരുള്ള ശ്രേണി $A:$C പട്ടികയിൽ ലുക്ക്അപ്പ് ടേബിൾ 2, 3 വിലകൾ അടങ്ങുന്ന കോളം C ആണ്.

ഞങ്ങൾ സൃഷ്ടിച്ച ഫോർമുല നൽകിയ ഫലം ചുവടെയുള്ള ചിത്രം കാണിക്കുന്നു:

VLOOKUP, INDIRECT എന്നിവ ഉപയോഗിച്ച് വ്യത്യസ്ത പട്ടികകളിൽ നിന്നുള്ള ഡാറ്റയുടെ ഡൈനാമിക് സബ്സ്റ്റിറ്റ്യൂഷൻ

ആദ്യം, "വ്യത്യസ്‌ത പട്ടികകളിൽ നിന്നുള്ള ഡാറ്റയുടെ ഡൈനാമിക് സബ്‌സ്റ്റിറ്റ്യൂഷൻ" എന്ന പദപ്രയോഗം കൊണ്ട് എന്താണ് അർത്ഥമാക്കുന്നത് എന്ന് നമുക്ക് വ്യക്തമാക്കാം.

ഒരേ ഫോർമാറ്റിന്റെ ഡാറ്റയുള്ള നിരവധി ഷീറ്റുകൾ ഉള്ളപ്പോൾ സാഹചര്യങ്ങളുണ്ട്, തന്നിരിക്കുന്ന സെല്ലിൽ നൽകിയിരിക്കുന്ന മൂല്യത്തെ ആശ്രയിച്ച് ഒരു നിശ്ചിത ഷീറ്റിൽ നിന്ന് ആവശ്യമായ വിവരങ്ങൾ എക്‌സ്‌ട്രാക്റ്റുചെയ്യേണ്ടത് ആവശ്യമാണ്. ഒരു ഉദാഹരണത്തിലൂടെ ഇത് വിശദീകരിക്കുന്നത് എളുപ്പമാണെന്ന് ഞാൻ കരുതുന്നു.

ഒരേ ഉൽപ്പന്നങ്ങളും ഒരേ ഫോർമാറ്റിലുള്ളതുമായ നിരവധി പ്രദേശങ്ങൾക്കായി നിങ്ങൾക്ക് വിൽപ്പന റിപ്പോർട്ടുകൾ ഉണ്ടെന്ന് സങ്കൽപ്പിക്കുക. ഒരു നിർദ്ദിഷ്‌ട പ്രദേശത്തിനായുള്ള വിൽപ്പന കണക്കുകൾ കണ്ടെത്താൻ നിങ്ങൾ ആഗ്രഹിക്കുന്നു:

നിങ്ങൾക്ക് അത്തരം രണ്ട് റിപ്പോർട്ടുകൾ മാത്രമേ ഉള്ളൂവെങ്കിൽ, ഫംഗ്‌ഷനുകളുള്ള അപമാനകരമായ ലളിതമായ ഫോർമുല നിങ്ങൾക്ക് ഉപയോഗിക്കാം VPR и IF (IF) തിരയാൻ ആവശ്യമുള്ള റിപ്പോർട്ട് തിരഞ്ഞെടുക്കാൻ:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

എവിടെ:

• $D$2 ഉൽപ്പന്നത്തിന്റെ പേര് അടങ്ങിയ ഒരു സെല്ലാണ്. ഫോർമുല മറ്റ് സെല്ലുകളിലേക്ക് പകർത്തുമ്പോൾ ലുക്കപ്പ് മൂല്യം മാറ്റുന്നത് ഒഴിവാക്കാൻ ഞങ്ങൾ ഇവിടെ സമ്പൂർണ്ണ റഫറൻസുകൾ ഉപയോഗിക്കുന്നുവെന്നത് ശ്രദ്ധിക്കുക.
• $D3 പ്രദേശത്തിന്റെ പേരുള്ള ഒരു സെല്ലാണ്. ഞങ്ങൾ സമ്പൂർണ്ണ കോളം റഫറൻസും ആപേക്ഷിക വരി റഫറൻസും ഉപയോഗിക്കുന്നു, കാരണം അതേ കോളത്തിലെ മറ്റ് സെല്ലുകളിലേക്ക് ഫോർമുല പകർത്താൻ ഞങ്ങൾ പദ്ധതിയിടുന്നു.
• FL_Sales и CA_സെയിൽസ് - അനുബന്ധ വിൽപ്പന റിപ്പോർട്ടുകൾ അടങ്ങിയിരിക്കുന്ന പട്ടികകളുടെ പേരുകൾ (അല്ലെങ്കിൽ പേരുള്ള ശ്രേണികൾ). നിങ്ങൾക്ക് തീർച്ചയായും, സാധാരണ ഷീറ്റ് പേരുകളും സെൽ റേഞ്ച് റഫറൻസുകളും ഉപയോഗിക്കാം, ഉദാഹരണത്തിന് 'FL ഷീറ്റ്'!$A$3:$B$10, എന്നാൽ പേരുള്ള ശ്രേണികൾ കൂടുതൽ സൗകര്യപ്രദമാണ്.

എന്നിരുന്നാലും, അത്തരം നിരവധി പട്ടികകൾ ഉള്ളപ്പോൾ, പ്രവർത്തനം IF മികച്ച പരിഹാരമല്ല. പകരം, നിങ്ങൾക്ക് ഫംഗ്ഷൻ ഉപയോഗിക്കാം ഇൻഡിറക്റ്റ് (INDIRECT) ആവശ്യമുള്ള തിരയൽ ശ്രേണി നൽകുന്നതിന്.

നിങ്ങൾക്കറിയാവുന്നതുപോലെ, പ്രവർത്തനം ഇൻഡിറക്റ്റ് ഒരു ടെക്സ്റ്റ് സ്ട്രിംഗ് നൽകിയ ലിങ്ക് തിരികെ നൽകാൻ ഉപയോഗിക്കുന്നു, അതാണ് ഇപ്പോൾ നമുക്ക് വേണ്ടത്. അതിനാൽ, മുകളിലുള്ള ഫോർമുലയിലെ പദപ്രയോഗത്തെ ധീരമായി ഫംഗ്‌ഷൻ ഉപയോഗിച്ച് മാറ്റിസ്ഥാപിക്കുക IF ഫംഗ്ഷനുമായി ലിങ്ക് ചെയ്യാൻ ഇൻഡിറക്റ്റ്. ഇതാ ഒരു കോമ്പിനേഷൻ VPR и ഇൻഡിറക്റ്റ് ഇതോടൊപ്പം മികച്ച രീതിയിൽ പ്രവർത്തിക്കുന്നു:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

എവിടെ:

• $D$2 - ഇത് ഉൽപ്പന്നത്തിന്റെ പേരുള്ള ഒരു സെല്ലാണ്, കേവല ലിങ്ക് കാരണം ഇത് മാറ്റമില്ല.
• $D3 പ്രദേശത്തിന്റെ പേരിന്റെ ആദ്യഭാഗം ഉൾക്കൊള്ളുന്ന സെല്ലാണ്. ഞങ്ങളുടെ ഉദാഹരണത്തിൽ, ഇത് FL.
• _വിൽപ്പന - പേരുള്ള എല്ലാ ശ്രേണികളുടെയും പട്ടികകളുടെയും പേരിന്റെ പൊതുവായ ഭാഗം. സെൽ D3-ലെ മൂല്യവുമായി സംയോജിപ്പിക്കുമ്പോൾ, ആവശ്യമുള്ള ശ്രേണിയുടെ പൂർണ്ണ യോഗ്യതയുള്ള പേര് അത് രൂപപ്പെടുത്തുന്നു. ചടങ്ങിൽ പുതുതായി വരുന്നവർക്കായി ചില വിശദാംശങ്ങൾ ചുവടെയുണ്ട് ഇൻഡിറക്റ്റ്.

INDIRECT ഉം VLOOKUP ഉം എങ്ങനെ പ്രവർത്തിക്കുന്നു

ആദ്യം, ഫംഗ്‌ഷന്റെ വാക്യഘടന ഞാൻ നിങ്ങളെ ഓർമ്മിപ്പിക്കട്ടെ ഇൻഡിറക്റ്റ് (പരോക്ഷം):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

ആദ്യ ആർഗ്യുമെന്റ് ഒരു സെൽ റഫറൻസ് (A1 അല്ലെങ്കിൽ R1C1 ശൈലി), ഒരു ശ്രേണി നാമം അല്ലെങ്കിൽ ഒരു ടെക്സ്റ്റ് സ്ട്രിംഗ് ആകാം. ആദ്യ ആർഗ്യുമെന്റിൽ ഏത് തരത്തിലുള്ള ലിങ്കാണ് അടങ്ങിയിരിക്കുന്നതെന്ന് രണ്ടാമത്തെ ആർഗ്യുമെന്റ് നിർണ്ണയിക്കുന്നു:

• A1എങ്കിൽ വാദം യഥാർത്ഥ കോഡ് (ശരി) അല്ലെങ്കിൽ വ്യക്തമാക്കിയിട്ടില്ല;
• R1C1, എങ്കിൽ Fഎഎസ് ഇ (തെറ്റായ).

ഞങ്ങളുടെ കാര്യത്തിൽ, ലിങ്കിന് ശൈലി ഉണ്ട് A1, അതിനാൽ നിങ്ങൾക്ക് രണ്ടാമത്തെ വാദം ഉപേക്ഷിച്ച് ആദ്യത്തേതിൽ ശ്രദ്ധ കേന്ദ്രീകരിക്കാം.

അതിനാൽ നമുക്ക് ഞങ്ങളുടെ വിൽപ്പന റിപ്പോർട്ടുകളിലേക്ക് മടങ്ങാം. നിങ്ങൾ ഓർക്കുന്നുവെങ്കിൽ, ഓരോ റിപ്പോർട്ടും ഒരു പ്രത്യേക ഷീറ്റിൽ സ്ഥിതിചെയ്യുന്ന ഒരു പ്രത്യേക പട്ടികയാണ്. ഫോർമുല ശരിയായി പ്രവർത്തിക്കുന്നതിന്, നിങ്ങളുടെ പട്ടികകൾക്ക് (അല്ലെങ്കിൽ ശ്രേണികൾ) പേര് നൽകണം, കൂടാതെ എല്ലാ പേരുകൾക്കും ഒരു പൊതു ഭാഗം ഉണ്ടായിരിക്കണം. ഉദാഹരണത്തിന്, ഇതുപോലെ: CA_സെയിൽസ്, FL_സെയിൽസ്, TX_സെയിൽസ് ഇത്യാദി. നിങ്ങൾക്ക് കാണാനാകുന്നതുപോലെ, എല്ലാ പേരുകളിലും “_സെയിൽസ്” ഉണ്ട്.

ഫംഗ്ഷൻ ഇൻഡിറക്റ്റ് D നിരയിലെ മൂല്യവും "_Sales" എന്ന ടെക്സ്റ്റ് സ്ട്രിംഗും ബന്ധിപ്പിക്കുന്നു, അതുവഴി പറയുന്നു VPR ഏത് പട്ടികയിലാണ് തിരയേണ്ടത്. സെൽ D3 ൽ "FL" മൂല്യം അടങ്ങിയിട്ടുണ്ടെങ്കിൽ, ഫോർമുല പട്ടികയിൽ തിരയും FL_സെയിൽസ്, "CA" ആണെങ്കിൽ - പട്ടികയിൽ CA_സെയിൽസ് ഇത്യാദി.

പ്രവർത്തനങ്ങളുടെ ഫലം VPR и ഇൻഡിറക്റ്റ് ഇനിപ്പറയുന്നതായിരിക്കും:

ഡാറ്റ വ്യത്യസ്‌ത Excel പുസ്‌തകങ്ങളിലാണ് സ്ഥിതി ചെയ്യുന്നതെങ്കിൽ, പേരിട്ടിരിക്കുന്ന ശ്രേണിയ്‌ക്ക് മുമ്പായി നിങ്ങൾ പുസ്തകത്തിന്റെ പേര് ചേർക്കേണ്ടതുണ്ട്, ഉദാഹരണത്തിന്:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

ചടങ്ങാണെങ്കിൽ ഇൻഡിറക്റ്റ് മറ്റൊരു വർക്ക്ബുക്കിനെ പരാമർശിക്കുന്നു, ആ വർക്ക്ബുക്ക് തുറന്നിരിക്കണം. ഇത് അടച്ചാൽ, ഫംഗ്ഷൻ ഒരു പിശക് റിപ്പോർട്ട് ചെയ്യും. #രെഫ്! (#SSYL!).