آموزش تابع VLOOKUP در اکسل

تابع VLOOKUP یکی از محبوب‌ترین توابع در اکسل است که برای ادغام داده‌‌ها در اکسل بکار می‌رود. همیشه در اکسل داده‌های ما در یک شیت متمرکز نیست. داده‌ها در شیت‌ها و فایل‌های مختلف پراکنده‌اند و همواره ما به گزارش و تحلیلی نیاز داریم که لازمه آن ادغام این اطلاعات پراکنده در یک شیت می‌باشد. به بیان ساده‌تر، هدف اصلی تابع Vlookup در اکسل، پیدا کردن داده‌هایی خاص و فراخوانی آن‌ها به قسمت مورد نظر ماست.

تابع Vlookup، داده‌ی مد نظر ما را از ستون مربوطه در اکسل پیدا می‌کند و به قسمت مورد نظر ما می‌آورد. حرف V در ابتدای این فانکشن مخفف vertical است که به معنای عمودی می‌باشد و بیان می‌کند که عمل جستجو، به صورت عمودی و در بین ستون‌ها رخ می‌دهد. (Hlookup که H ابتدای آن مخفف Horizontal است که به معنای افقی می‌باشد، داده‌ها را در میان سطرها و به صورت افقی می‌گردد و پیدا می‌کند)

آموزش نوشتن تابع VLOOKUP

تابع VLOOKUP از شما 4 ورودی می‌خواهد و سپس به شما پاسخ را خواهد داد. این تابع، به صورت زیر نوشته می‌شود:

=VLOOKUP (value, table, col_index, [range_lookup])

تابع VLOOKUP ابتدا از شما مقدار معلوم را می‌خواهد. سپس، جدولی که از آن یک مقدار بازیابی می‌شود را بنویسید. در قدم سوم، باید مشخص کنیم که که مجهول ما کجاست (کافیست که جای ستون مجهول را به آن بگوییم). در قدم چهارم و آخر که اختیاری است، 2 انتخاب پیش روی ماست. اگر بخواهیم تمامی مقادیر حدودی را نیز پیدا کنیم، کافیست True بنویسیم و اگر دقیقا همان چیزی که دنبالش هستیم را پیدا کنیم، False می‌نویسیم. اکسل به صورت پیش فرض، بر روی مقادیر حدودی یا True تنظیم شده است.

نحوه کار با تابع Vlookup با یک مثال کاربردی

به طور مثال، می‌خواهیم نمره دانشجو علی را در درس شیمی پبدا کنیم.

در ابتدا، باید مقدار معلوم رو بنویسیم. در اینجا ابتدا نام علی را به صورت زیر می‌نویسیم:

=VLOOKUP("ali"

سپس VLOOKUP از شما می‌خواهد که جدولی را برای جستجو مشخص کنید. چون درس شیمی در در ستون E قرار دارد، ما حداقل باید تا ستون E را در نظر بگیریم. بنابراین تابع را به شکل زیر ادامه می‌دهیم:

=VLOOKUP("ali" , C:E

حالا که معلوم و جدول را مشخص کردیم، نوبت می‌رسد که به VLOOKUP بگوییم که مجهول ما کجاست. کافی است که جای ستون مجهول را به آن بگوییم. یعنی بگوییم در این جدول (یعنی C:E) سومین ستون حاوی مقداری است که ما آن را لازم داریم. بنابراین باید بنویسیم:

=VLOOKUP("ali" , C:E,3

دقت داشته باشید که حتما باید سومین ورودی یک عدد باشد. در واقع شما نمی‌توانید نام ستون مجهول را بدهید.

در اخر کافیست عبارت False را بگذاریم. مانند زیر:

=VLOOKUP("ali" , C:E,3,false

حال فقط کافیست  Enter را زده و نتیجه را مشاهده نمایید:

نکات مهم در استفاده از تابع VLOOKUP

نکته اول: این تابع به حروف کوچک و بزرگ حساس نیست.

نکته دوم: عدد و متن متفاوتند. یعنی 2 = "2" نیست.

نکته سوم: همواره فرض ما این است که یک مقدار معلوم وجود دارد. اگر مقادیر معلوم‌ها تکراری بودند، تابع vlookup اولین مقدار را می‌یابد.

نکته چهارم: تابع vlookup از wildcard ها پشتیبانی می‌کند. یعنی می‌توانید به جای "ali" بنویسید "li*" و یعنی سلولی که در آن li وجود دارد.

نکته پنجم: اگر اطلاعات شما در شیت دیگری بود مثلا شیتی به نام DATA، فرمول اینگونه خواهد شد:

=VLOOKUP(2000 , DATA!B:F , 2 , false)

نکته ششم: تابع vlookup فقط می‌تواند رو به جلو جستجو کند. به همین دلیل است که همواره ستون معلوم‌ها را در ابتدای یک لیست می‌نویسیم.

نکته هفتم: هر گاه در فرمولی یک متن نوشتیم، باید آن متن در داخل علامت (دبل کوت) یعنی " " قرار گیرد.

نکته هشتم: علامت «:» در اکسل به معنای «تا» است.

تابع SYD چیست؟

تابع SYD یک تابع مالی در اکسل است که محاسبه استهلاک به‌صورت مجموع سنوات را بر عهده دارد. این عملکرد در محاسبه استهلاک یک دارایی، به ویژه جمع استهلاک سالها برای یک دوره مشخص در طول عمر یک دارایی، کمک می‌کند. در روش مجموع سنوات میزان استهلاک در سال‌های ابتدایی عمر تجهیز نسبت به سال‌های انتهایی عمر تجهیز بیشتر می‌باشد.

فرمول مجموع سنوات یا SYD برابر است با:

ورودی های تابع SYD

این تابع، 4 ورودی یا آرگومان دریافت میکند که شامل:

Cost= هزینه بهای اولیه دارایی

Salvage= ارزش اسقاط

Life= طول عمر

Per= دوره‌ای که می‌خواهیم میزان استهلاک آن را محاسبه نماییم.

خروجی این تابع برابر با میزان استهلاک در دو دوره‌ای است که در ورودی چهارم تابع وارد کرده‌ایم.

چگونه از تابع SYD در اکسل استفاده کنیم؟

برای درک کاربرد تابع SYD، مثالی را در نظر می‌گیریم:

فرض کنید می‌خواهیم استهلاک یک دارایی را با هزینه اولیه 300000 دلار و ارزش اسقاط 5000 دلار پس از 10 سال محاسبه کنیم. حال فرض کنید که می‌خواهیم استهلاک را در طول 5 سال محاسبه کنیم. فرمول محاسبه مقدار استهلاک به شرح زیر خواهد بود:

در سال اول، میزان استهلاک به شرح زیر خواهد بود:

حال اگر بخواهیم استهلاک تا 5 سال را بدست آوریم، کافیست به جای مقدار 1 در آخر تابع، عدد سال استهلاک خود را تغییر دهیم. مانند تصویر زیر:

مثال دوم: میزان استهلاک یک تجهیز با قیمت خرید 14 میلیون تومان و طول عمر 7 ساله در سال پنجم به روش مجموع سنوات چقدر است (با فرض آنکه ارزش اسقاطی تجهیز در انتهای دوره برابر با 1 میلیون تومان  است) ؟

مواردی که باید در مورد عملکرد SYD به خاطر بسپارید

خطای #VALUE هنگامی رخ می‌دهد که آرگومان‌های داده شده غیرعددی باشند.

خطای #NUM هنگامی رخ می‌دهد که:

• ارزش اسقاط کمتر از صفر باشد
• طول عمر کمتر یا مساوی صفر باشد
• دوره‌ای که می‌خواهیم میزان استهلاک آن را محاسبه کنیم، از عمر مفید بیشتر باشد.

منبع :نرم افزار حسابداری لاندا