جستجو و توابع مرجع در لیبره آفیس کالک
لیبره آفیس کالک همه توابع جستجو و مرجع لازم را برای گردش کار روزانه شما و حل مشکلات صفحه گسترده پیچیده ارائه میدهد.
جستجو و مرجع در لیبره آفیس کالک
مفهوم جستجو اساساً بازیابی یا یافتن برخی از مقادیر بر اساس “کلید” یا مرجع است. به عنوان مثال، اگر دو مجموعه داده با یک ستون مشترک دارید، ID میوهها را مانند زیر بگویید. یک میز دارای نام و قیمت هر کیلو می باشد. و یک جدول دیگر دارای زیر مجموعهای از میوهها است.
چگونه میتوانید با واکشی دادههای مربوطه از جدول دیگر، نامها را بدست آورید و قیمت کل را محاسبه کنید؟
VLOOKUP
VLOOKUP مخفف جستجوی عمودی است. بنابراین، برای پیدا کردن نامها و سایر موارد در مثال بالا، فرمول زیر را با استفاده از VLOOKUP به سلول B2 اضافه میکنم. در اینجا سینتکس و فرمول آن آمده است.
سینتکس
|
1 |
=VLOOKUP(search item, table array, column number of table array to be returned, search type) |
|
1 |
search type: 0=Exact match; 1=Approximate match |
مثال
|
1 |
=VLOOKUP(A2,F6:G9,2,0) |
توضیح: فرمول شناسه (ID) را در A2 جستجو می کند، یعنی “200” در جدول F6:G9، و ستون دوم (“2”) جدول انتخاب شده را برمیگرداند.
برای محاسبه بیشتر، همچنین میتوانید قیمت را از جدول هدف دریافت کنید، آن را در ستون D قرار دهید و مقدار کل را برای محاسبه قیمت ضرب کنید.
سپس دسته سلول را به سمت پایین بکشید تا بقیه سلولها با فرمول پر شوند.
همچنین، مطمئن شوید که مرجع سلول را با اضافه کردن “$” به عنوان جهانی تغییر دهید تا از افزایش آن جلوگیری کنید، که جدول جستجو را تغییر میدهد. به این میگویند مرجع مطلق. میتوانید با فشار دادن F4 در حالی که مکاننما را در سلول هدف نگه دارید، به آن دست پیدا کنید.
بنابراین، فرمول نهایی این میشود:
|
1 |
=VLOOKUP(A2,$F$6:$G$9,2,0) |
|
1 |
=VLOOKUP(A2,$F$6:$H$9,3,0)*C2 |
در اینجا جریان کار و نتیجه نهایی آمده است.
HLOOKUP
تابع بعدی جستجوی افقی (یا HLOOKUP) است. HLOOKUP دقیقاً مخالف VLOOKUP است که در بالا توضیح داده شد.
بنابراین، این تابع به جای جستجوی بالا به پایین، از چپ به راست جستجو میکند. در اینجا نتیجه همان مثال بالا است.
|
1 |
=HLOOKUP(A2,$G$5:$J$6,2,0) |
|
1 |
=HLOOKUP(A2,$G$5:$J$7,3,0)*C2 |
همانطور که میبینید، نتایج دقیقاً یکسان است.
INDEX و MATCH
زیبایی لیبره آفیس کالک در این است که شما میتوانید با استفاده از توابع INDEX و MATCH همان نتیجه فوق را بدست آورید.
در اینجا سینتکس هر دو تابع آمده است.
|
1 |
=INDEX(reference, row, column,[range]) |
- reference: جدول/محدودهای که دادهها از آنجا برداشت میشوند
- row: شماره سطر جدول مرجع بالا
- column: شماره ستون جدول مرجع
|
1 |
=MATCH(search criteria, lookup array, type) |
- search criteria: مقدار مورد جستجو
- lookup array: آرایه تکبعدی برای جستجو (به عنوان مثال یک ستون)
- type: مقدار 0=تطابق دقیق، مقدار 1=تطابق تقریبی
بنابراین، برای به دست آوردن همان نتیجه فوق، میتوانید از دو فرمول زیر استفاده کنید.
|
1 |
=INDEX($F$6:$G$9,MATCH(A2,$F$6:$F$9,0),2) |
توضیح: مقدار جدول F6 را به G9 برمیگردد. شماره ردیف توسط MATCH() که 2 است پیدا میشود. و شماره ستون به عنوان 2 وارد میشود زیرا باید مقدار را از ستون G برگردانیم.
|
1 |
=INDEX($F$6:$H$9,MATCH(A2,$F$6:$F$9,0),3)*C2 |
توضیح: به همین ترتیب، اعمال همین مفهوم میتواند با ذکر شماره ستون به عنوان 3، قیمت را برگرداند.
دیدگاهتان را بنویسید