மேம்பட்ட VLOOKUP எடுத்துக்காட்டுகள்: பல அளவுகோல் தேடல்

செயல்பாடு பற்றிய எங்கள் டுடோரியலின் இரண்டாம் பகுதியில் வி.பி.ஆர் (VLOOKUP) எக்செல் இல், அனைத்து சக்தியையும் இயக்க உதவும் சில எடுத்துக்காட்டுகளை நாங்கள் பகுப்பாய்வு செய்வோம் வி.பி.ஆர் மிகவும் லட்சியமான எக்செல் பணிகளை தீர்க்க. இந்த அம்சம் எவ்வாறு செயல்படுகிறது என்பது பற்றிய அடிப்படை அறிவு உங்களுக்கு ஏற்கனவே உள்ளது என்று எடுத்துக்காட்டுகள் கருதுகின்றன. இல்லையெனில், தொடரியல் மற்றும் அடிப்படை பயன்பாட்டை விளக்கும் இந்த டுடோரியலின் முதல் பகுதியுடன் தொடங்க நீங்கள் ஆர்வமாக இருக்கலாம். வி.பி.ஆர். சரி, ஆரம்பிக்கலாம்.

• பல அளவுகோல்களின்படி எக்செல் இல் தேடுங்கள்
• VLOOKUP ஐப் பயன்படுத்தி 2வது, 3வது, முதலிய மதிப்புகளைப் பிரித்தெடுக்கிறோம்
• விரும்பிய மதிப்பின் அனைத்து மறுமுறைகளையும் மீட்டெடுக்கவும்
• தெரிந்த வரிசை மற்றும் நெடுவரிசை மூலம் XNUMXD தேடல்
• ஒரு சூத்திரத்தில் பல VLOOKUPகளைப் பயன்படுத்துதல்
• வெவ்வேறு அட்டவணைகளிலிருந்து தரவின் மாறும் மாற்றீடு

பல அளவுகோல்களின்படி எக்செல் இல் தேடுங்கள்

விழா வி.பி.ஆர் எக்செல் என்பது ஒரு தரவுத்தளத்தில் ஒரு குறிப்பிட்ட மதிப்பைத் தேடுவதற்கு மிகவும் சக்திவாய்ந்த கருவியாகும். இருப்பினும், ஒரு குறிப்பிடத்தக்க வரம்பு உள்ளது - அதன் தொடரியல் உங்களை ஒரே ஒரு மதிப்பைத் தேட அனுமதிக்கிறது. நீங்கள் பல நிபந்தனைகளின்படி தேட விரும்பினால் என்ன செய்வது? நீங்கள் கீழே தீர்வு காணலாம்.

எடுத்துக்காட்டு 1: 2 வெவ்வேறு அளவுகோல்களின்படி தேடுங்கள்

எங்களிடம் ஆர்டர்களின் பட்டியல் உள்ளது மற்றும் நாங்கள் கண்டுபிடிக்க விரும்புகிறோம் என்று வைத்துக்கொள்வோம் பொருட்களின் அளவு (Qty.), இரண்டு அளவுகோல்களின் அடிப்படையில் – நுகர்வி பெயர் (வாடிக்கையாளர்) மற்றும் தயாரிப்பு விவரம் (தயாரிப்பு). ஒவ்வொரு வாங்குபவர்களும் பல வகையான பொருட்களை ஆர்டர் செய்ததன் மூலம் விஷயம் சிக்கலானது, கீழே உள்ள அட்டவணையில் இருந்து காணலாம்:

வழக்கமான செயல்பாடு வி.பி.ஆர் இந்த சூழ்நிலையில் வேலை செய்யாது, ஏனெனில் கொடுக்கப்பட்ட தேடல் மதிப்புடன் பொருந்தக்கூடிய முதல் மதிப்பை அது வழங்கும். உதாரணமாக, நீங்கள் ஒரு பொருளின் அளவை அறிய விரும்பினால் இனிப்புகள்'வாங்குபவர் உத்தரவிட்டார் ஜெர்மி ஹில், பின்வரும் சூத்திரத்தை எழுதவும்:

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

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

- இந்த சூத்திரம் முடிவை வழங்கும் 15தயாரிப்புடன் தொடர்புடையது ஆப்பிள்கள், ஏனெனில் இது பொருந்தக்கூடிய முதல் மதிப்பு.

ஒரு எளிய தீர்வு உள்ளது - தேவையான அனைத்து அளவுகோல்களையும் இணைக்க கூடுதல் நெடுவரிசையை உருவாக்கவும். எங்கள் எடுத்துக்காட்டில், இவை நெடுவரிசைகள் நுகர்வி பெயர் (வாடிக்கையாளர்) மற்றும் தயாரிப்பு விவரம் (தயாரிப்பு). இணைக்கப்பட்ட நெடுவரிசை எப்போதும் தேடல் வரம்பில் இடதுபுற நெடுவரிசையாக இருக்க வேண்டும் என்பதை மறந்துவிடாதீர்கள், ஏனெனில் இது இடது நெடுவரிசையில் செயல்படும் வி.பி.ஆர் மதிப்பைத் தேடும் போது பார்க்கிறது.

எனவே, நீங்கள் அட்டவணையில் ஒரு துணை நெடுவரிசையைச் சேர்த்து, அதன் அனைத்து கலங்களிலும் பின்வரும் சூத்திரத்தை நகலெடுக்கவும்: =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 வாதத்தின் ஒருங்கிணைந்த மதிப்பைக் கொண்டுள்ளது பார்வை_ மதிப்பு (பார்வை_மதிப்பு) மற்றும் 4 – வாதம் col_index_num (column_number), அதாவது மீட்டெடுக்கப்பட வேண்டிய தரவைக் கொண்ட நெடுவரிசையின் எண்ணிக்கை.

எடுத்துக்காட்டு 2: VLOOKUP இரண்டு அளவுகோல்களின்படி மற்றொரு தாளில் அட்டவணையைப் பார்க்கிறது

மற்றொரு தாளில் அல்லது மற்றொரு எக்செல் பணிப்புத்தகத்தில் அமைந்துள்ள இரண்டாவது அட்டவணையில் (லுக்அப் டேபிள்) தரவைச் சேர்ப்பதன் மூலம் பிரதான அட்டவணையை (முதன்மை அட்டவணை) புதுப்பிக்க வேண்டும் என்றால், நீங்கள் செருகும் சூத்திரத்தில் விரும்பிய மதிப்பை நேரடியாகச் சேகரிக்கலாம். பிரதான அட்டவணையில்.

முந்தைய எடுத்துக்காட்டில் உள்ளதைப் போல, ஒருங்கிணைந்த மதிப்புகளுடன் தேடல் அட்டவணையில் உங்களுக்கு துணை நிரல் தேவைப்படும். இந்த நெடுவரிசையானது தேடல் வரம்பில் இடதுபுற நெடுவரிசையாக இருக்க வேண்டும்.

எனவே சூத்திரம் வி.பி.ஆர் இப்படி இருக்கலாம்:

=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) செய்ய வேண்டும்.

நினைவில்! விழா வி.பி.ஆர் 255 எழுத்துகளுக்கு மட்டுப்படுத்தப்பட்ட, 255 எழுத்துகளுக்கு மேல் நீளமுள்ள மதிப்பைத் தேட முடியாது. இதை மனதில் வைத்து, விரும்பிய மதிப்பின் நீளம் இந்த வரம்பை மீறவில்லை என்பதை உறுதிப்படுத்தவும்.

துணை நெடுவரிசையைச் சேர்ப்பது மிகவும் நேர்த்தியானது மற்றும் எப்போதும் ஏற்றுக்கொள்ளக்கூடிய தீர்வு அல்ல என்பதை நான் ஒப்புக்கொள்கிறேன். ஹெல்பர் நெடுவரிசை இல்லாமல் நீங்கள் அதையே செய்யலாம், ஆனால் அதற்கு செயல்பாடுகளின் கலவையுடன் மிகவும் சிக்கலான சூத்திரம் தேவைப்படும் அட்டவணையில் (INDEX) மற்றும் போட்டி (மேலும் வெளிப்பட்டது).

VLOOKUP ஐப் பயன்படுத்தி 2வது, 3வது, முதலிய மதிப்புகளைப் பிரித்தெடுக்கிறோம்

அது உங்களுக்கு ஏற்கனவே தெரியும் வி.பி.ஆர் ஒரே ஒரு பொருந்தக்கூடிய மதிப்பை மட்டுமே வழங்க முடியும், இன்னும் துல்லியமாக, முதலில் கண்டுபிடிக்கப்பட்டது. ஆனால் பார்க்கப்பட்ட வரிசையில் இந்த மதிப்பு பலமுறை திரும்பத் திரும்பக் காட்டப்பட்டு, அவற்றில் 2வது அல்லது 3வது பிரித்தெடுக்க விரும்பினால் என்ன செய்வது? எல்லா மதிப்புகளும் இருந்தால் என்ன செய்வது? சிக்கல் சிக்கலானதாகத் தெரிகிறது, ஆனால் தீர்வு உள்ளது!

அட்டவணையின் ஒரு நெடுவரிசையில் வாடிக்கையாளர்களின் பெயர்கள் (வாடிக்கையாளர் பெயர்), மற்ற நெடுவரிசையில் அவர்கள் வாங்கிய தயாரிப்புகள் (தயாரிப்பு) உள்ளன என்று வைத்துக்கொள்வோம். கொடுக்கப்பட்ட வாடிக்கையாளர் வாங்கிய 2வது, 3வது மற்றும் 4வது பொருட்களைக் கண்டுபிடிக்க முயற்சிப்போம்.

நெடுவரிசைக்கு முன் துணை நெடுவரிசையைச் சேர்ப்பதே எளிதான வழி வாடிக்கையாளர் பெயர் ஒவ்வொரு பெயரின் மறு எண்ணையும் கொண்டு வாடிக்கையாளர் பெயர்களை நிரப்பவும், எடுத்துக்காட்டாக, ஜான் டோ 1, ஜான் டோ 2 முதலியன. செயல்பாட்டைப் பயன்படுத்தி எண்ணிடுவதன் மூலம் தந்திரம் செய்வோம் COUNTIF (COUNTIF), வாடிக்கையாளர் பெயர்கள் நெடுவரிசை B இல் இருப்பதால்:

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

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

அதன் பிறகு நீங்கள் சாதாரண செயல்பாட்டைப் பயன்படுத்தலாம் வி.பி.ஆர்தேவையான ஆர்டரைக் கண்டுபிடிக்க. உதாரணத்திற்கு:

• கண்டுபிடிக்க 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-இ மீண்டும் மீண்டும், நீங்கள் மிகவும் சிக்கலான சூத்திரத்தை உருவாக்குவதன் மூலம் துணை நெடுவரிசை இல்லாமல் செய்யலாம்:

=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 - உங்கள் அட்டவணை அல்லது வரம்பின் இறுதி செல்.

இந்த சூத்திரம் இரண்டாவது பொருந்தும் மதிப்பை மட்டுமே கண்டறியும். மீதமுள்ள மறுமுறைகளை நீங்கள் பிரித்தெடுக்க வேண்டும் என்றால், முந்தைய தீர்வைப் பயன்படுத்தவும்.

உங்களுக்கு அனைத்து போட்டிகளின் பட்டியல் தேவைப்பட்டால் - செயல்பாடு வி.பி.ஆர் இது ஒரு உதவியாளர் அல்ல, ஏனெனில் இது ஒரு நேரத்தில் ஒரு மதிப்பை மட்டுமே வழங்குகிறது - காலம். ஆனால் எக்செல் ஒரு செயல்பாடு உள்ளது அட்டவணையில் (INDEX), இந்தப் பணியை எளிதாகச் சமாளிக்க முடியும். அத்தகைய சூத்திரம் எப்படி இருக்கும், பின்வரும் எடுத்துக்காட்டில் நீங்கள் கற்றுக்கொள்வீர்கள்.

விரும்பிய மதிப்பின் அனைத்து மறுமுறைகளையும் மீட்டெடுக்கவும்

மேலே குறிப்பிட்டுள்ளபடி வி.பி.ஆர் ஸ்கேன் செய்யப்பட்ட வரம்பிலிருந்து அனைத்து நகல் மதிப்புகளையும் பிரித்தெடுக்க முடியாது. இதைச் செய்ய, உங்களுக்கு சற்று சிக்கலான சூத்திரம் தேவை, இது போன்ற பல எக்செல் செயல்பாடுகளால் ஆனது அட்டவணையில் (INDEX), சிறிய (சிறியது) மற்றும் வரிசை (LINE)

எடுத்துக்காட்டாக, கீழே உள்ள சூத்திரம் 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வரிசை சூத்திரத்தை சரியாக உள்ளிட.

இது எவ்வாறு செயல்படுகிறது என்பதைப் புரிந்துகொள்வதில் நீங்கள் ஆர்வமாக இருந்தால், சூத்திரத்தின் விவரங்களுக்குச் சிறிது டைவ் செய்யலாம்:

பகுதி 1:

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,"","",""}

பகுதி 2:

ROW()-3

СТРОКА()-3

இங்கே செயல்பாடு வரிசை (LINE) கூடுதல் கவுண்டராக செயல்படுகிறது. சூத்திரம் F4:F9 கலங்களில் நகலெடுக்கப்பட்டதால், எண்ணைக் கழிப்போம் 3 மதிப்பைப் பெற செயல்பாட்டு முடிவிலிருந்து 1 செல்லில் F4 (வரி 4, கழித்தல் 3) பெற 2 செல்லில் F5 (வரி 5, கழித்தல் 3) மற்றும் பல.

பகுதி 3:

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, முதலியன

பகுதி 4:

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) ஒரு வரிசையில் உள்ள குறிப்பிட்ட கலத்தின் மதிப்பை வழங்கும் சி 2: சி 16. கலத்திற்கு F4 செயல்பாடு குறியீட்டு ($C$2:$C$16) திரும்பும் ஆப்பிள்கள்ஐந்து F5 செயல்பாடு குறியீட்டு ($C$2:$C$16) திரும்பும் இனிப்புகள்' மற்றும் பல.

பகுதி 5:

IFERROR()

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

இறுதியாக, செயல்பாட்டின் உள்ளே சூத்திரத்தை வைக்கிறோம் IFERROR (IFERROR), ஏனெனில் நீங்கள் பிழை செய்தியில் மகிழ்ச்சியடைய வாய்ப்பில்லை #ஏடி (#N/A) பார்முலா நகலெடுக்கப்பட்ட கலங்களின் எண்ணிக்கை, பார்க்கப்படும் வரம்பில் உள்ள நகல் மதிப்புகளின் எண்ணிக்கையை விட குறைவாக இருந்தால்.

தெரிந்த வரிசை மற்றும் நெடுவரிசை மூலம் XNUMXD தேடல்

Excel இல் XNUMXD தேடலைச் செய்வது, அறியப்பட்ட வரிசை மற்றும் நெடுவரிசை எண் மூலம் மதிப்பைத் தேடுவதை உள்ளடக்குகிறது. வேறு வார்த்தைகளில் கூறுவதானால், நீங்கள் ஒரு குறிப்பிட்ட வரிசை மற்றும் நெடுவரிசையின் குறுக்குவெட்டில் செல் மதிப்பைப் பிரித்தெடுக்கிறீர்கள்.

எனவே, எங்கள் அட்டவணைக்குத் திரும்பி, ஒரு செயல்பாட்டுடன் ஒரு சூத்திரத்தை எழுதுவோம் வி.பி.ஆர், இது மார்ச் மாதத்தில் விற்கப்படும் எலுமிச்சை விலை பற்றிய தகவல்களைக் கண்டறியும்.

XNUMXD தேடலைச் செய்ய பல வழிகள் உள்ளன. விருப்பங்களைச் சரிபார்த்து, உங்களுக்கு மிகவும் பொருத்தமான ஒன்றைத் தேர்ந்தெடுக்கவும்.

VLOOKUP மற்றும் MATCH செயல்பாடுகள்

நீங்கள் ஒரு சில செயல்பாடுகளை பயன்படுத்தலாம் வி.பி.ஆர் (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);ЛОЖЬ)

மேலே உள்ள சூத்திரம் ஒரு வழக்கமான செயல்பாடு வி.பி.ஆர், A2 முதல் A9 வரையிலான கலங்களில் "லெமன்ஸ்" மதிப்பின் சரியான பொருத்தத்தை இது தேடுகிறது. ஆனால் மார்ச் மாத விற்பனை எந்த நெடுவரிசையில் உள்ளது என்று உங்களுக்குத் தெரியாததால், மூன்றாவது செயல்பாட்டு வாதத்திற்கான நெடுவரிசை எண்ணை உங்களால் அமைக்க முடியாது. வி.பி.ஆர். அதற்கு பதிலாக, செயல்பாடு பயன்படுத்தப்படுகிறது மேலும் வெளிப்படுத்தப்பட்டதுஇந்த நெடுவரிசையை வரையறுக்க.

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

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

மனித மொழியில் மொழிபெயர்க்கப்பட்ட இந்த சூத்திரம்:

• நாங்கள் "மார்" - வாதம் எழுத்துக்களைத் தேடுகிறோம் பார்வை_ மதிப்பு (பார்வை_மதிப்பு);
• A1 முதல் I1 வரையிலான கலங்களில் தேடுதல் - வாதம் தேடுதல்_வரிசை (தேடுதல்_வரிசை);
• சரியான பொருத்தம் - வாதம் போட்டி_வகை (பொருத்த_வகை).

பயன்படுத்தி 0 மூன்றாவது வாதத்தில், செயல்பாடுகள் என்கிறீர்கள் மேலும் வெளிப்படுத்தப்பட்டது நீங்கள் தேடும் மதிப்புடன் சரியாகப் பொருந்தக்கூடிய முதல் மதிப்பைத் தேடுங்கள். இது மதிப்புக்கு சமம் பொய்யா (FALSE) நான்காவது வாதத்திற்கு வி.பி.ஆர்.

இரு பரிமாண தேடல் அல்லது இருதரப்பு தேடல் என்றும் அழைக்கப்படும் எக்செல் இல் இரு வழி தேடல் சூத்திரத்தை உருவாக்குவது இதுதான்.

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))

பெயரிடப்பட்ட வரம்புகள் மற்றும் குறுக்குவெட்டு ஆபரேட்டர்

சிக்கலான எக்செல் ஃபார்முலாக்கள் அனைத்தையும் நீங்கள் விரும்பவில்லை என்றால், இந்த காட்சி மற்றும் மறக்கமுடியாத வழியை நீங்கள் விரும்பலாம்:

1. அட்டவணையைத் தேர்ந்தெடுத்து, தாவலைத் திறக்கவும் சூத்திரங்கள் (சூத்திரங்கள்) மற்றும் கிளிக் செய்யவும் தேர்விலிருந்து உருவாக்கவும் (தேர்வில் இருந்து உருவாக்கவும்).
2. பெட்டிகளை சரிபார்க்கவும் மேல் வரிசை (மேலே உள்ள வரியில்) மற்றும் இடது நெடுவரிசை (இடதுபுறத்தில் உள்ள நெடுவரிசையில்). மைக்ரோசாஃப்ட் எக்செல் உங்கள் விரிதாளின் மேல் வரிசை மற்றும் இடது நெடுவரிசையில் உள்ள மதிப்புகளிலிருந்து வரம்புகளுக்கு பெயர்களை ஒதுக்கும். இப்போது நீங்கள் சூத்திரங்களை உருவாக்காமல் நேரடியாக இந்தப் பெயர்களைப் பயன்படுத்தி தேடலாம்.
3. எந்த வெற்று கலத்திலும், எழுதவும் =வரிசை_பெயர் நிரல்_பெயர், எடுத்துக்காட்டாக இது போன்றது:

   = எலுமிச்சை மார்

   … அல்லது நேர்மாறாக:

   =மார் லெமன்ஸ்

   வரிசை மற்றும் நெடுவரிசையின் பெயர்கள் ஒரு இடைவெளியால் பிரிக்கப்பட வேண்டும் என்பதை நினைவில் கொள்ளுங்கள், இது இந்த விஷயத்தில் குறுக்குவெட்டு ஆபரேட்டரைப் போலவே செயல்படுகிறது.

நீங்கள் ஒரு பெயரை உள்ளிடும்போது, ​​மைக்ரோசாஃப்ட் எக்செல் நீங்கள் ஒரு சூத்திரத்தை உள்ளிடுவது போல, பொருந்தும் பெயர்களின் பட்டியலுடன் ஒரு உதவிக்குறிப்பைக் காண்பிக்கும்.

4. பிரஸ் உள்ளிடவும் மற்றும் முடிவை சரிபார்க்கவும்

பொதுவாக, மேலே உள்ள முறைகளில் எதை நீங்கள் தேர்வு செய்தாலும், இரு பரிமாண தேடலின் முடிவு ஒரே மாதிரியாக இருக்கும்:

ஒரு சூத்திரத்தில் பல VLOOKUPகளைப் பயன்படுத்துதல்

பிரதான அட்டவணை மற்றும் தேடல் அட்டவணையில் பொதுவான ஒரு நெடுவரிசை இல்லை, இது வழக்கமான செயல்பாட்டைப் பயன்படுத்துவதைத் தடுக்கிறது வி.பி.ஆர். இருப்பினும், நாங்கள் ஆர்வமுள்ள தகவலைக் கொண்டிருக்காத மற்றொரு அட்டவணை உள்ளது, ஆனால் பிரதான அட்டவணை மற்றும் தேடல் அட்டவணையுடன் பொதுவான நெடுவரிசை உள்ளது.

பின்வரும் உதாரணத்தைப் பார்ப்போம். எங்களிடம் ஒரு நெடுவரிசையுடன் ஒரு முதன்மை அட்டவணை உள்ளது SKU (புதியது), மற்றொரு அட்டவணையில் இருந்து தொடர்புடைய விலைகளுடன் ஒரு நெடுவரிசையைச் சேர்க்க வேண்டும். கூடுதலாக, எங்களிடம் 2 தேடல் அட்டவணைகள் உள்ளன. முதலாவது (பார்வை அட்டவணை 1) புதுப்பிக்கப்பட்ட எண்களைக் கொண்டுள்ளது SKU (புதியது) மற்றும் தயாரிப்பு பெயர்கள், மற்றும் இரண்டாவது (பார்வை அட்டவணை 2) - தயாரிப்பு பெயர்கள் மற்றும் பழைய எண்கள் SKU (பழையது).

இரண்டாவது தேடல் அட்டவணையில் இருந்து பிரதான அட்டவணையில் விலைகளைச் சேர்க்க, நீங்கள் இரட்டை எனப்படும் செயலைச் செய்ய வேண்டும் வி.பி.ஆர் அல்லது கூடு கட்டப்பட்டது வி.பி.ஆர்.

1. ஒரு செயல்பாட்டை எழுதுங்கள் வி.பி.ஆர், இது அட்டவணையில் தயாரிப்பு பெயரைக் கண்டறியும் தேடல் அட்டவணை 1பயன்படுத்தி எழு, விரும்பிய மதிப்பாக:

   =VLOOKUP(A2,New_SKU,2,FALSE)

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

   இங்கே புதிய_SKU - பெயரிடப்பட்ட வரம்பு $A:$B மேஜையில் தேடல் அட்டவணை 1, 2 - இது நெடுவரிசை B, இதில் பொருட்களின் பெயர்கள் உள்ளன (மேலே உள்ள படத்தைப் பார்க்கவும்)

2. அட்டவணையில் இருந்து விலைகளைச் செருகுவதற்கான சூத்திரத்தை எழுதவும் தேடல் அட்டவணை 2 நன்கு அறியப்பட்ட தயாரிப்பு பெயர்களின் அடிப்படையில். இதைச் செய்ய, நீங்கள் முன்பு உருவாக்கிய சூத்திரத்தை புதிய செயல்பாட்டிற்கான தேடல் மதிப்பாக ஒட்டவும் வி.பி.ஆர்:

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

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

   இங்கே விலை - பெயரிடப்பட்ட வரம்பு $A:$C மேஜையில் தேடல் அட்டவணை 2, 3 C நெடுவரிசையில் விலைகள் உள்ளன.

நாங்கள் உருவாக்கிய சூத்திரத்தின் மூலம் கிடைத்த முடிவை கீழே உள்ள படம் காட்டுகிறது:

VLOOKUP மற்றும் INDIRECT ஐப் பயன்படுத்தி வெவ்வேறு அட்டவணைகளிலிருந்து தரவின் மாறும் மாற்றீடு

முதலில், நாம் ஒருவரையொருவர் சரியாகப் புரிந்துகொள்கிறோம் என்பதை உறுதிப்படுத்த, "வெவ்வேறு அட்டவணைகளிலிருந்து தரவின் மாறும் மாற்று" என்ற வெளிப்பாட்டின் மூலம் நாம் என்ன சொல்கிறோம் என்பதை தெளிவுபடுத்துவோம்.

ஒரே வடிவமைப்பின் தரவுகளுடன் பல தாள்கள் இருக்கும்போது சூழ்நிலைகள் உள்ளன, மேலும் கொடுக்கப்பட்ட கலத்தில் உள்ளிடப்பட்ட மதிப்பைப் பொறுத்து ஒரு குறிப்பிட்ட தாளில் இருந்து தேவையான தகவல்களைப் பிரித்தெடுப்பது அவசியம். இதை ஒரு உதாரணத்துடன் விளக்குவது எளிதாக இருக்கும் என்று நினைக்கிறேன்.

ஒரே தயாரிப்புகள் மற்றும் ஒரே வடிவத்தில் பல பிராந்தியங்களுக்கான விற்பனை அறிக்கைகள் உங்களிடம் இருப்பதாக கற்பனை செய்து பாருங்கள். ஒரு குறிப்பிட்ட பிராந்தியத்திற்கான விற்பனை புள்ளிவிவரங்களைக் கண்டறிய வேண்டும்:

உங்களிடம் இதுபோன்ற இரண்டு அறிக்கைகள் மட்டுமே இருந்தால், செயல்பாடுகளுடன் கூடிய இழிவான எளிய சூத்திரத்தைப் பயன்படுத்தலாம் வி.பி.ஆர் и IF (IF) தேட விரும்பும் அறிக்கையைத் தேர்ந்தெடுக்க:

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

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

எங்கே:

• $ டி $ 2 தயாரிப்பின் பெயரைக் கொண்ட கலமாகும். சூத்திரத்தை மற்ற கலங்களுக்கு நகலெடுக்கும் போது தேடுதல் மதிப்பை மாற்றுவதைத் தவிர்க்க இங்கே முழுமையான குறிப்புகளைப் பயன்படுத்துகிறோம் என்பதை நினைவில் கொள்ளவும்.
• $D3 என்பது பிராந்தியத்தின் பெயரைக் கொண்ட ஒரு கலமாகும். ஒரே நெடுவரிசையில் உள்ள மற்ற கலங்களுக்கு ஃபார்முலாவை நகலெடுக்க நாங்கள் திட்டமிட்டுள்ளதால், முழுமையான நெடுவரிசைக் குறிப்பையும் தொடர்புடைய வரிசைக் குறிப்பையும் பயன்படுத்துகிறோம்.
• FL_Sales и CA_விற்பனை - தொடர்புடைய விற்பனை அறிக்கைகளைக் கொண்ட அட்டவணைகளின் பெயர்கள் (அல்லது பெயரிடப்பட்ட வரம்புகள்). எடுத்துக்காட்டாக, நீங்கள் வழக்கமான தாள் பெயர்கள் மற்றும் செல் வரம்பு குறிப்புகளைப் பயன்படுத்தலாம் 'FL தாள்'!$A$3:$B$10, ஆனால் பெயரிடப்பட்ட வரம்புகள் மிகவும் வசதியானவை.

இருப்பினும், இதுபோன்ற பல அட்டவணைகள் இருக்கும்போது, ​​​​செயல்பாடு IF சிறந்த தீர்வு அல்ல. அதற்கு பதிலாக, நீங்கள் செயல்பாட்டைப் பயன்படுத்தலாம் மறைமுக (INDIRECT) விரும்பிய தேடல் வரம்பை வழங்க.

ஒருவேளை உங்களுக்கு தெரியும், செயல்பாடு மறைமுக டெக்ஸ்ட் ஸ்டிரிங் மூலம் கொடுக்கப்பட்ட இணைப்பை திரும்பப் பெறப் பயன்படுகிறது, இதுவே இப்போது நமக்குத் தேவை. எனவே, மேலே உள்ள சூத்திரத்தில் உள்ள வெளிப்பாட்டை செயல்பாட்டுடன் தைரியமாக மாற்றவும் IF செயல்பாட்டுடன் இணைக்க மறைமுக. இங்கே ஒரு கலவை உள்ளது வி.பி.ஆர் и மறைமுக இதனுடன் சிறப்பாக செயல்படுகிறது:

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

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

எங்கே:

• $ டி $ 2 - இது தயாரிப்பின் பெயரைக் கொண்ட கலமாகும், இது முழுமையான இணைப்பின் காரணமாக மாறாமல் உள்ளது.
• $D3 பிராந்தியத்தின் பெயரின் முதல் பகுதியைக் கொண்ட கலமாகும். எங்கள் எடுத்துக்காட்டில், இது FL.
• _விற்பனை - பெயரிடப்பட்ட அனைத்து வரம்புகள் அல்லது அட்டவணைகளின் பெயரின் பொதுவான பகுதி. செல் D3 இல் உள்ள மதிப்புடன் இணைந்தால், அது தேவையான வரம்பின் முழு தகுதியான பெயரை உருவாக்குகிறது. செயல்பாட்டிற்கு புதிதாக வருபவர்களுக்கான சில விவரங்கள் கீழே உள்ளன மறைமுக.

INDIRECT மற்றும் VLOOKUP எப்படி வேலை செய்கின்றன

முதலில், செயல்பாட்டின் தொடரியலை உங்களுக்கு நினைவூட்டுகிறேன் மறைமுக (மறைமுகம்):

INDIRECT(ref_text,[a1])

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

முதல் வாதம் செல் குறிப்பு (A1 அல்லது R1C1 பாணி), வரம்பு பெயர் அல்லது உரை சரம். இரண்டாவது வாதம் முதல் வாதத்தில் எந்த வகையான இணைப்பு உள்ளது என்பதை தீர்மானிக்கிறது:

• A1வாதம் என்றால் உண்மை குறியீடு (சரி) அல்லது குறிப்பிடப்படவில்லை;
• ஆர் 1 சி 1, என்றால் Fஏஎஸ் ஈ (FALSE).

எங்கள் விஷயத்தில், இணைப்பு பாணியைக் கொண்டுள்ளது A1, எனவே நீங்கள் இரண்டாவது வாதத்தை விட்டுவிட்டு முதல் வாதத்தில் கவனம் செலுத்தலாம்.

எனவே எங்கள் விற்பனை அறிக்கைகளுக்கு வருவோம். நீங்கள் நினைவில் வைத்திருந்தால், ஒவ்வொரு அறிக்கையும் தனித்தனி தாளில் அமைந்துள்ள தனி அட்டவணை. சூத்திரம் சரியாக வேலை செய்ய, உங்கள் அட்டவணைகளுக்கு (அல்லது வரம்புகள்) பெயரிட வேண்டும், மேலும் அனைத்து பெயர்களுக்கும் பொதுவான பகுதி இருக்க வேண்டும். உதாரணமாக, இது போன்றது: CA_விற்பனை, FL_Sales, TX_Sales மற்றும் பல. நீங்கள் பார்க்க முடியும் என, அனைத்து பெயர்களிலும் “_Sales” உள்ளது.

விழா மறைமுக D நெடுவரிசையில் உள்ள மதிப்பையும் "_Sales" என்ற உரைச் சரத்தையும் இணைக்கிறது வி.பி.ஆர் எந்த அட்டவணையில் தேட வேண்டும். செல் D3 இல் "FL" மதிப்பு இருந்தால், சூத்திரம் அட்டவணையைத் தேடும் FL_Sales, "CA" என்றால் - அட்டவணையில் CA_விற்பனை மற்றும் பல.

செயல்பாடுகளின் முடிவு வி.பி.ஆர் и மறைமுக பின்வருவனவாக இருக்கும்:

தரவு வெவ்வேறு எக்செல் புத்தகங்களில் இருந்தால், பெயரிடப்பட்ட வரம்பிற்கு முன் புத்தகத்தின் பெயரை நீங்கள் சேர்க்க வேண்டும், எடுத்துக்காட்டாக:

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

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

செயல்பாடு என்றால் மறைமுக மற்றொரு பணிப்புத்தகத்தை குறிக்கிறது, அந்த பணிப்புத்தகம் திறந்திருக்க வேண்டும். அது மூடப்பட்டால், செயல்பாடு பிழையைப் புகாரளிக்கும். #REF! (#SSYL!).