برای یاد گیری Excel از کجا شروع کنیم....

**Ali Akbar.J** · 26 بهمن، ۱۳۹۱

اینم جواب تمرین

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

Lean · 2 اسفند، ۱۳۹۱

نام گذاری در اکسل:

در مبحث آدرس دهی دیدید که نام سطر و ستون در فرمول دیده می شود اما این امکان وجود دارد که برای سلول ها نام انتخاب نموده و در فرمول استفاده کرد ، این عمل ابتدایی ترین کاری است که می توان با ابزار name manager انجام داد. این کار مزایای زیر را دارد:

دسترسي راحت تر به محدوده ها - كاهش خطا در ادرس دهي ها - كم كردن طول فرمول - يادآوري موضوعات واقعي فرمولها با توجه به نامهاي داده شده

.

در ابتدا define name را که در تصویر فوق با کادر نارنجی نمایش داده شده کلیک کنید.

.

پنجره فوق را مشاهده خواهید کرد

در قسمت name نام مناسبی را باید انتخاب کنید

قواعد نام گذاری:

 اعداد نمی توانند استفاده شوند.

 از فاصله نمی توان استفاده کرد.

 علامتهایی به جز بک اسلش و خط نمی توانند استفاده شوند .

 در هنگامي كه ادرس ها در حالت r1c1 ميباشند نميتوان از اين امكان استفاده نمود

 یک نام می تواند شامل 255کاراکتر باشد

در قسمت scope سطحی که نام مورد شناسایی قرار گیرد را انتخاب می کنید این سطح می تواند کل workbook یا هر کدام از شیت ها باشد.

.

در بخش comment می توانید توضیحی در مورد نام گذاری که انجام می دهید وارد کنید فرض کنید سلول A1 را طبق تصویر فوق با a نام گذاری کنیم در این صورت با گذاشتن مساوی در سلول ها این نام در لیست فروریز نمایش داده شده و توضیحی که وارد کرده اید نشان داده می شود

.

در بخش refers to شما سلول یا محدوده ای که قصد دارید نام گذاری کنید انتخاب خواهید کرد.

نام گذاری سریع :

در تصویر آموزشی زیر می خواهیم محدوده زرد رنگ را به صورت سریع نام گذاری کنیم ، در سل بالای این ناحیه عبارت name وارد شده می خواهیم این محدوده به همین نام ثبت شود این عمل با انتخاب محدوده و استفاده از کلید های ترکیبی Ctrl+alt+F3 انجام می گردد.

.

همانطور که دیدید اکسل به صورت خودکار سلول بالایی محدوده را به طور هوشمند برای نامگذاری محدوده استفاده کرد اما آیا امکان تغییر این تنظیم وجود دارد پاسخ این است که بله شما با کلید های ترکیبی Ctrl+Shift+F3 میتوانید از طریق پنجره زیر این تنظیم را به دلخواه تغییر دهید.

.

روشی دیگر برای نام گذاری:

می توان با استفاده از کلید های Ctrl+F3 ویا با کلیک بر روی name manager به پنجره مربوطه منتقل شده و با کلیک بر روی قسمت new نام جدیدی تعریف نمایید

.

در پنجره فوق اسامی تعریف شده را مشاهده می کنید که این اسمی قابل ویرایش از طریق edit یا حذف از طریق delete وجود دارد در بخش filter شما براساس شرط خاصی میتوانید نام هایی که در این پنجره نشان داه می شود را محدود کنید

.

استفاده از نام گذاری در فرمول نویسی:

می توان با نامگذاری یک سلول تحت هر عنوانی در یک صفحه اکسل برای برابر قرار دادن سلولهای دیگر در تمامی صفحات دیگر از = همراه با نام آن سلول استفاده کرد

بعنوان مثال روی سلول A کلیدهای Ctrl+F3 را فشرده و بعنوان مثال نام Exceliran را برای سلول A1 انتخاب می کنیم

سپس مقدار سلول A1 را عدد 20 قرار می دهیم.

حال در هر سلول دیگری و در هر صفحه دیگری از این ورک بوک بنویسیم Exceliran= نتیجه 20 را به ما باز می گرداند.

همچنین اگر به جای یک سلول یک دامنه انتخاب شود نیز می توان در تمامی فرمولها از نام آن دامنه در فرمول نویسی استفاده کرد مثلاً اگر A1:A10 به عنوان SU نامگذاری شده باشند برای بدست آوردن مجموع A1:A10 می توان نوشت :

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

در این صورت ما مجموع اعدادی که در محدوده مذکور وارد کرده ایم خواهیم دید.

تمرین :

محدوده ای به نام maxnum تعریف کرده و فرمولی با استفاده از آن بنویسید که مقدار max اعداد وارد شده را در سلول a1 نشان دهد.

ژارسین · 4 اسفند، ۱۳۹۱

جواب تمرین 2:hanghead:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

**hasti1988** · 4 اسفند، ۱۳۹۱

ارسال شد...درسته؟

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

**Ali Akbar.J** · 5 اسفند، ۱۳۹۱

جواب تمرین

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

Lean · 5 اسفند، ۱۳۹۱

آشنایی با فرمول ها

در ادامه بحث به مبحث اساسی فرمول ها می پردازیم ، ابتدا به چند نکته اشاره میکنم

نیاز به یادگیری همه فرمول ها نیست ، بخش اعظم فرمول ها کاملا تخصصی است که بنا به رشته ممکن است هیچگاه مورد استفاده شما قرار نگیرد
فرمول ها نیاز به حفظ کردن ندارد بلکه تنها داشتن پیش زمینه ذهنی از عملکرد فرمول ها کفایت میکند
فرمول ها نباید بر اساس پارامتر ها فراگرفته شود بلکه بر اساس تمرین عملی فرا بگیرید

تقسیم بندی فرمول ها:

در نرم افزار اکسل این تقسیم بندی صورت گرفته که در شکل زیر می بینید:

.

در این مبحث به مهم ترین فرمول ها البته با دیدی پیشرفته تر پرداخته خواهد شد

توابع جستجو:

این توابع از جمله مهمترین توابع اکسل می باشد ، جستجوی یک داده بر اساس داده ای دیگر و یافتن یک مقدار خاص با این توابع صورت می گیرد البته در آینده روش هایی برای یافتن یک مقدار بدون استفاده از این توابع نیز ارائه می گردد.

تابع VLOOKUP :

مهمترین پرکاربرد ترین تابع جستجو و اولین تابعی که به شخصه با آن آشنا شدم :

تابع Vertical lookup برای جستجوی عمودی بر اساس شماره ستون و یک مقدار خاص شکل گرفته است پارامتر های این تابع به شرح زیر است:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

Lookup_ value : کد مقداری که مورد جستجو قرار میگیرد

Table _Array : محدوده ای که مقدار مورد جستجو در آن قرار دارد و شمامل چند ستون است

Clol_index_num : شماره ستونی که مورد جستجو در ان ستون واقع است

Range_look up : مقداری اختیاری که نشان می دهد جستجوی شما دقیق باشد و یا تقریبی و مقادیر 0 یا false به معنی دقیق و 1 یا true به معنی تقریبی است و مقدار پیشفرض 1 است که باید دقت شود.

در تصویر زیر نحوه استفاده را مشاهده میکنید.

.

در مبحث فرمول ها در چند مرحله برخی از فرمول ها را توضیح خواهیم داد

تمرین :

در فایل پیوست ، مقدار متناظر ماکزیمم مقادیر ستون a1 یک را برای value1 , value2بیابید.از سل کمکی استفاده نکنید

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

Lean · 8 اسفند، ۱۳۹۱

دوستان عدم پاسخ یعنی ادامه ندادن آموزش

**Ali Akbar.J** · 8 اسفند، ۱۳۹۱

درسته؟

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

ژارسین · 10 اسفند، ۱۳۹۱

من یاد نگرفتم ,میشه روی یه مثال توضیح بدید؟

Lean · 10 اسفند، ۱۳۹۱

در توضیح vlookup به تصویر زیر دقت کنید

قصد داریم بر اساس ستون کد مقدار متناظر کد d را در ستون value2 بیابیم

.

کد d را در سل C18 وارد شده است حال مقدار متناظر آن در ستون value2 مقدار 45 میشود در فرمول فاکتور اول آدرس سلولی که کد d وارد شده یعنی c18 وارد شده فاکتور دوم محدوده ای است که در شکل زیر مشخص است نسبت به ستون code ستون value2 در ستون سوم قرار دارد و فاکتور آخر یعنی صفر نشان دهنده این است که مقدار دقیق یعنی 45 وارد گردد. فایل این مسئله در پیوست مشاهده کنید

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

Lean · 10 اسفند، ۱۳۹۱

در این بخش به نکاتی در مورد name manager می پردازیم که مستلزم آشنایی با فرمول ها بود همچنین به بخشی مهم از data validation می پردازیم ، در واقع سعی داریم کاربرد ابزار ها را با استفاده از فرمول ها آموزش دهیم، همچنین به مدیریت خطا در در فرمول vlookup می پردازیم.

قبلا در بخش آشنایی با name manager گفتیم که میتوان برای نامگذاری محدوده سلول ها استفاده کنیم شایان ذکر است که می توان برای فرمول ها نیز استفاده کرد در تصویر زیر در بخش refer to فرمول vlook up را مشاهده میکنید ما نام بیاب را انتخاب کرده ایم حال کافیست در مقابل کد هایی که میخواهیم جستجو شود بنویسم =بیاب مقدار متناظر مشاهده می شود

.

به تصاویر فوق دقت کنید

زمانی که کدی در مجموعه مورد جستجو موجود نباشد خطای #N/A صادر می شود ، وجود خطا در صفحه چندان مناسب نیست و می توان آن را کنترل کرد برای کنترل خطا از فرمول iferror استفاده میکنیم به شکل زیر

.

به جای "این کد وجود ندارد" می توان از "" استفاده کرد که یک فضای خالی نمایش می دهد.

.

می توان فرمول نوشته شده را داینامیک کرد یعنی امکان تغییر را در فرمول ایجاد کرد تا با تغییر یک فاکتور نتیجه فرمول را تغییر داد برای اینکار راه های مختلف وجود دارد که به مرور مطرح می شود اینجا ما از لیست استفاده میکنیم:

امکان ایجاد لیست با استفاده از datavalidation :

.

بر روی قسمتی که در تصویر مشخص شده کلیک کنید:

.

از کشوی باز شده بخشی که هایلایت شده انتخاب کنید:

.

بر روی allow کلیک کنید تا کشوی مربوطه باز شود :

List را انتخاب کنید ، سلولی را انتخاب می کنیم و به شکل زیر اعداد 2 تا 4 را انتخاب میکنیم

.

با لینک فرمول به سلولی که لیست را وارد کرده ایم می توان اطلاعات ستون های مختلف را جستجو کرد

.

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

.

در فایل پیوست کلیه این موارد را مشاهده کنید.

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

boyeee barann · 12 خرداد، ۱۳۹۲

با سلام

حتما رو فرصت نکته به نکته شو کار میکنم

واقعا تاپیک عالی هستش....

باتشکر...

Lean · 14 مهر، ۱۳۹۲

7 دلیل برای استفاده از فرمول index

در بین فرمول های موجود در اکسل فرمول index به واقع یکی از 5 فرمول برتر آن است ، این فرمول همه کاره ، قوی و هوشمند است گرچه در ظاهر ساده به نظر می رسد. این فرمول قادر است تغییر عمده ای در روش آنالیز داده ها و محاسبه اعداد به وجود آورد.

مفهوم فرمول:

به بیان ساده فرمول index فرمولی است که به شما ارزش یا رفرنس یک داده را در یک جدول یا محدوده باز می گرداند شاید در ظاهر این موضوع جزئی به نظر برسد اما اگر به طور کامل با کاربرد های آن آشنا شوید شگفت زده خواهید شد.

چند کابرد ساده از فرمول:

فرض کنید شما لیستی 8 تایی از اسامی دارید حال میخواهید بدانید هشتمین آیتم از این لیست چه نامی است ، کافی است فرمول زیر را بنویسید

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

فرض کنید در ستون سوم از این لیست شماره تلفن وارد شده باشد می خواهید شماره تلفن هشتمین نفر از لیست را بدانید

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

ترکیب های مختلف فرمول index

فرمول index با دو ترکیب بکار می رود

در این ترکیب با دادن شماره سطر و ستون یک ناحیه می توان به رفرنس یا مقدار دست یافت

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

در این ترکیب شما به دادن شماره سطر و ستون به یک مقدار یا رفرنس در یک محدوده خاص دست خواهید یافت

در این مثال ما از داده هایی به شرح جدول زیر با نام List استفاده خواهیم کرد

[TABLE=class: ncode_imageresizer_warning, width: 500]

[TR]

[TD=class: td1, width: 20][/TD]

[TD=class: td2]براي نمايش در سايز اصلي بر روي نوشته كليك كنيد ، مشخصات تصوير هست 529 در317 پيكسل .[/TD]

[/TR]

[/TABLE]

دلیل اول : بدست آوردن n امین داده

این فرمول بهترین و ساده ترین فرمول برای این کار است کافیست بنویسیم:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

دلیل دوم : بدست آوردن مقدار حاصل از تقاطع یم ردیف ویک ستون با دادن شماره های ردیف و ستون

در صورتی که بخواهید به داده ای که n امین ردیف و m امین ستون قرار دارد برسید کافیست فرمول زیر را بنویسید:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

دلیل سوم: دریافت کل یا ستونی از یک جدول

گاهی شما می خواهید اطلاعاتی از کل یک جدول یا ستونی از یک جدول بدست آورید مثلا متوسط سن افراد شما می توانید از فرمول زیر استفاده کنید:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

همچنین می توانید از فرمول زیر نیز استفاده کنید سن افراد در ستون 5 جدول قرار دارد می توان نوشت:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

نکته: میتوان بجای ردیف از جای خالی یا 0 استفاده کنید همچنین در مورد ستون هم می توان بکار برد.

دلیل چهارم: جستجو به سمت چپ

می دانیم که استفاده از فرمول vlookup امکان جستجو در خلاف جهت را به ما نمی دهد اما با ترکیب فرمول های index&match می توان به این مشکل فائق آمد ، در مثال فوق می خواهیم بدانیم کدام فرد سنگین ترین وزن را دارد.

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

دلیل پنجم: ایجاد محدوده داینامیک

تا کنون با برخی از توانایی های تابع index آشنا شده اید که به نظر ساده بوده است ، حال به برخی کاربرد ها پیچیده تر این تابع می پردازیم ، توانایی واقعی index در ماهیت آن نهفته است شما در ظاهر می بینید که index مقداری را نمایش می دهد اما در واقع این تابع شما را به سلولی که حاوی آن مقدار است ارجاع می دهد به عنوان مثال وقتی می نویسد index(list;8) شما 8 امین مقدار از لیست را می بینید اما در واقع شما به سلول حاوی هشتمین مقدار ارجاع داده شده اید پس حاصل این تابع یک ارجاع است و هرگاه شما نیاز به ارجاعی داشته باشید می توانید از تابع استفاده کنید .

کمی گیج کننده است ، با مثال هایی به شرح موضوع می پردازیم :

شما وقتی می خواهید ناحیه ای مانند A1:A10 را جمع بزنید از sum(A1:A10) استفاده میکنید در این فرمول a1 , a10 رفرنس هستند حال به این فرمول توجه کنید :

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

جواب ها در هر دو یکسان است اما در دومی index از محدوده a1 تا a50 شما را به 10 خانه اول ارجاع می دهد.

مثال1 : متوسط قد X نفر اول :

فرض کنید می خواهید متوسط قد x نفر اول افراد لیست را بیابید ، x عددی متغیر است در این صورت فرمول زیر را خواهیم داشت:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

مثال 2 : متوسط قد افراد در یک لیست داینامیک

گاهی شما میخواهید متوسط قد همه افراد لیست را داشته باشید اما نمیدانید این افراد چند نفر هستند در این صورت هر بار که داده ای اضافه می شود باید فرمول ها را بروز کنید اما چگونه می توان از فرمول های خودکار استفاده کرد، یکی از راه ها استفاده از فرمول offset است :

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

اما به جای فرمول فوق می توان از فرمول زیر هم استفاده کرد:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

دلیل ششم : ارجاع به محدوده خاصی از چندین محدوده

سه لیست یا محدوده مجزا دارید list1,list2,list3 میخواهیم متوسط داده های این سه لیست را بدست آوریم لذا از ترکیب دوم تایع index استفاده مکنیم

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

(list1;list2;list3) نشان دهنده کل محدوه ها و d2 نشانگر شماره محدوده مورد نظر است.

دلیل هفتم : تابع index می تواند آرایه ها را پردازش کند

ماهیت تابع index به گونه ای است که بدون استفاده از CTRL+SHIFT+ENTER داده های آرایه ای را پردازش کند ، به عنوان مثال شما می توانید متوسط سنی افرادی که نام آنها با حرف F شروع شده بیایبد

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

همانطور که دیدید تابع index کاربرد های متفاوت و سودمندی دارد ، ممکن است شما از این تابع به نحو دیگری استفاده کرده باشید که برای غنای این مطلب لطفا مطرح کنید.

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

Lean · 21 مهر، ۱۳۹۲

فرمول offset جهت ارجاع به یک محدوده از طریق تعیین نقطه شروع و دادن طول و عرض بکار برده می شود .

ترکیب فرمول OFFSET

آرگومان های این فرمول به شرح زیر است

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

Starting point: یک سل که نقطه آغاز ما برای جابه جا شدن است

Rows & columns to move: چه تعداد ستون یا ردیف می خواهید از نقطه شروع فاصله بگیرید ، این اعداد می توانند مثبت ، منفی یا صفر باشند.

Height & width: این مقادیر مشخص کننده ابعاد محدوده ای است که می خواهید به آن ارجاع کنید به عنوان مثال 3و4 محدوده ای از سلول ها به بلندی 3 سلول و عرض 4 سلول می دهد.

شما می توانید همه آرگومان های فرمول Offset را به سلول های دیگر ارجاع دهید. به عنوان مثال می توانید بنویسید:

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

در این فرمول از سلول A1 شروع می شود D1 ردیف D2

ستون جابجا شده و محدوده ای با ارتفاع D3 و عرض D4 برمی گرداند

تصویر زیر گویای مطلب است :

[TABLE=class: ncode_imageresizer_warning, width: 500]

[TR]

[TD=class: td1, width: 20][/TD]

[TD=class: td2]براي نمايش در سايز اصلي بر روي نوشته كليك كنيد ، مشخصات تصوير هست 685 در142 پيكسل .[/TD]

[/TR]

[/TABLE]

چرا ازOffset استفاده می کنیم

چرا به طور مستقیم نمی نویسیم A1:C4 ، دلایلی وجود دارد

استفاده از محدوده های داینامیک : عبارتی مانند A1:C4 محدوده ای استاتیک فراهم می آورد در حالی که گاهی نیاز به محدوده ای داینامیک وجود دارد
گاهی آدرس دقیق وجود ندارد ، به عبارت بهتر اطلاعاتی در دست نیست که به کدام محدوده باید ارجاع دهیم ممکن است تعداد ستون یا ردیف و همچنین عرض و ارتفاع اعدادی حاصل از یک محاسبه باشد در این صورت استفاده از فرمول offset مفید خواهد بود.

ایجاد یک Workbook پویا

جهت درک بهتر مطلب یک فایل پویا آماده شده است که با تغییر آرگومان های مشخص شده به خوبی می توان پی به عملکرد این فرمول برد.

[TABLE=class: ncode_imageresizer_warning, width: 500]

[TR]

[TD=class: td1, width: 20][/TD]

[TD=class: td2]براي نمايش در سايز اصلي بر روي نوشته كليك كنيد ، مشخصات تصوير هست 670 در320 پيكسل .[/TD]

[/TR]

[/TABLE]

کاربرد عملی فرمول offset

اطلاعاتی از درصد ضایعات یک محصول در اختیار داریم که می بایست متوسط این درصد به صورت هفتگی گزارش گردد ، حالت ایده آل آن است که در یک داشبور مدیریتی آیتمی جهت نمایش اطلاعات 7 روز آخر نمایش داده شود اما چگونه:

با استفاده از فرمول offset امکان پذیر است

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

برای محاسبه متوسط داده های 7 روز آخر می بایست از کل داده ها ، داده های 7 روز آخر را داشته باشیم ، با استفاده از COUNTA(C3:C300) می توان تعداد داده های وارد شده را شمارش کنیم به عنوان مثال داده های 18 روز ثبت شده است با کم کردن 7 می توان به یک سلول قبل از شروع داده های مورد نیاز دست یافت این عدد نمایشگر تعداد ردیف هایی است که از مبدأ فاصله گرفته می شود ارتفاع محدوده مورد نیاز 7 است که در قسمت Height وارد می شود ، برای درک بهتر به فایل مربوطه رجوع و با Evaluate fomula عملکرد فرمول را بررسی کنید .

[TABLE=class: ncode_imageresizer_warning, width: 500]

[TR]

[TD=class: td1, width: 20][/TD]

[TD=class: td2]براي نمايش در سايز اصلي بر روي نوشته كليك كنيد ، مشخصات تصوير هست 550 در392 پيكسل .[/TD]

[/TR]

[/TABLE]

محدودیت های Offset

با آنکه این فرمول قادر به بازگرداندن محدوده های پویا است با این حال محدودیت هایی نیز دارد.

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

جایگزین هایی برای Offset

دو جایگزین برای Offset وجود دارد

استفاده از Excel tables : از ورژن 2007 به بعد با استفاده از table ایجاد محدوده های پویا تسهیل شده است و به راحتی می توان فرمول هایی جهت استفاده از محدوده های پویا نوشت
استفاده از فرمول index : توسط این فرمول نیز قادر به ایجاد محدوده هایی پویا خواهید بود گرچه در نوع استفاده تفاوت هایی وجود دارد در ضمن این فرمول همانند offset حافظه اشغال نمی کند.

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

Lean · 22 مهر، ۱۳۹۲

سه تابعی که شما را حرفه ای می کند...

فرض کنید حجم عظیمی از داده ها را در اختیار دارید ، داده هایی شامل فیلد ها و رکودها ، اگر بخواهید بر اساس یک رکورد در یک فیلد رکورد دیگری را در فیلدی دیگر بیابید چه باید بکنید ، پدران ما برای این کار دفاتری حجیم داشتند که آن ها بر اساس حرف الفبا یا به ترتیب شماره ها در قفسه های بزرگ دسته بندی کرده بودند ، فکر میکنید یافتن اطلاعات بانکی یک فرد بر اساس شماره حساب با این سیستم چه مدت زمان می برد؟ یا خود را بجای یکی از این افراد قرار دهید در طول روز چه مدت می بایست زمان صرف کنید تا اطلاعات خاصی را تهیه کنید ، فکر کردن به این موضوع من را خسته می کند نمیدانم شما چه حسی خواهید داشت؟

توابع شگفت انگیز

می توان وجه تمایز یک کاربر عادی excel و یک کاربر حرفه ای excel بدون اغراق در میزان توانایی وی در استفاده از سه تابع دانست ، توابعی که می توانند مشکلاتی که به آن اشاره شد مرتفع کنند ، سه تابع Vlookup ، Match ، Offset .

ترکیب توابع Vlookup ، Offset و Match

تصویر زیر به زبان ساده گویای آنچه این توابع انجام می دهند است

کاربرد Vlookup ,Match

این توابع این امکان را فراهم می آورد که توسط excel یک سوزن را در انبار کاه پیدا کنید ، فرض کنید در یک شیت اطلاعاتی از مشتری ها شامل شماره تماس ، نام ، شهر و سن را در محدوده A1

5000 فراهم آورده اید ، می خواهید بدانید شماره تماس 09355061941 متعلق به چه کسی است .

[TABLE=class: ncode_imageresizer_warning, width: 500]

[TR]

[TD=class: td1, width: 20][/TD]

[/TR]

[/TABLE]

همانطور که در تصویر پیداست تابع Vlookup شماره تلفن مورد نظر را ستون Tel جستجو می کند و با توجه به محدوده جستجو یعنی A1:B16 نام متناظر با شماره را یافته بر می گرداند ، اما تابع Match شماره را در ستون Tel جستجو کرده و شماره سل متناظر با آن یعنی 9 را بر میگرداند .

نکته: شایان ذکر است اگر در قسمت Lookup Array فرمول Match ارایه افقی وارد کنیم تابع محل ستون آیتم مورد جستجو را بر می گرداند ، این خصوصیت باعث می شود باترکیب توابع Match و Vlookup فرمول های پویاتر و کارآمد تری را طراحی کنیم .

کاربرد offset چیست

اما از طریق Offset به excel می گوییم تا بخشی از طلاعات موجود در یک ناحیه را واکشی کند ، جهت درک مطلب offset نقش شیشه یک خودرو در حال حرکت را دارد که نمایی از فضای وسیع بیرون را به شما نمایش می دهد.

Offset بخشی از یک محدوده بزرگ را برمیگرداند فرمولی به صورت

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

2*3=6 سلول را ارجاع خواهد داد که در تصویر زیر مشاهده می کنید .

با توجه به اینکه فرمول match موقعیت مکانی یه داده را بازمیگرداند ، می تواند به عنوانی یکی از آرگومان ها فرمول offset بکار رود ، نکته دیگر اینکه فرمول های vlookup و match در صورت عدم وجود داده مورد نظر خطاری #value را باز می گردانند همچنین فرمول offset بدون بکار بردن و ترکیب با سایر توابع مانند sum این خطا را باز خواهد گرداند .

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

ورود

برای یاد گیری Excel از کجا شروع کنیم....

ارسال های توصیه شده

Ali Akbar.J 3914

لینک به دیدگاه

Lean 56968

لینک به دیدگاه

ژارسین 4579

لینک به دیدگاه

hasti1988 22046

لینک به دیدگاه

Ali Akbar.J 3914

لینک به دیدگاه

Lean 56968

لینک به دیدگاه

Lean 56968

لینک به دیدگاه

Ali Akbar.J 3914

لینک به دیدگاه

ژارسین 4579

لینک به دیدگاه

Lean 56968

لینک به دیدگاه

Lean 56968

لینک به دیدگاه

boyeee barann 995

لینک به دیدگاه

Lean 56968

لینک به دیدگاه

Lean 56968

لینک به دیدگاه

Lean 56968

لینک به دیدگاه

سایت نواندیشان

انجمن نواندیشان

جریان فعالیت های من

کسب درآمد کنید