مدل سازی ساده با اکسل “نام های محدوده ها”

20 مرداد 1400

دقیقه

نام های محدوده ها    سوالاتی که در این فصل پاسخ داده می شود: ‌- می خواهم جمع فروش ایالت های آریزونا ،کالیفرنیا ،مونتانا ،نیویورک و نیوجرسی را حساب کنم ، آیا راهی وجود دارد که جمع کل فروش این ایالت ها را به جای اینکه با فرمول SUM(A21:A25) بنویسم به صورت مثلاً AZ+CA+MT+NY+NJ نوشته...

آخرین به‌روزرسانی: 18 بهمن 1401

نام های محدوده ها

 

 سوالاتی که در این فصل پاسخ داده می شود:

‌- می خواهم جمع فروش ایالت های آریزونا ،کالیفرنیا ،مونتانا ،نیویورک و نیوجرسی را حساب کنم ، آیا راهی وجود دارد که جمع کل فروش این ایالت ها را به جای اینکه با فرمول SUM(A21:A25) بنویسم به صورت مثلاً AZ+CA+MT+NY+NJ نوشته و پاسخ صحیح را به دست آورم؟

– فرمول هایی مثل Average(A:A) چه کار می کنند ؟

– چه تفاوتی میان نام محدوده با گستره کتاب‌کار و در گستره کاربرگ وجود دارد؟

– من از گزینه نام خوشم آمده و در بسیاری از فایل های اکسل شروع به استفاده از آنها کرده‌ام ، اما نام محدوده ها در فرمول های من ظاهر نمی شوند چگونه می توانم نام هایی را که اخیراً ایجاد کرده‌انم در فرمول هایی که قبلاً نوشته‌ام نمایش دهم؟

– چگونه میتوانم فهرستی از همه نام های محدوده (و سلول هایی که بر آنها دلالت دارند) در فایل را نشان دهم؟

– من در حال محاسبه پیش بینی درآمد سال آینده بر اساس مضربی از سال گذشته هستم ، آیا در این مورد می توان فرمولی مثل (1+growth)*last year داشت ؟

– دستمزد ساعتی و ساعت کارکرد هر هر روز هفته به ما داده شده است ،  آیا می توانیم با فرمول ساعت کار ضربدر حقوق ( wages*hours)، دستمزد هر روز را محاسبه کنیم ؟

حتما پیش آمده که با فایلی که چنین فرمولی SUM(A5000:A5049) داشته ، کار کرده باشید. در چنین مواردی باید دید که در این محدوده چه گنجانده شده است. اگر این در این محدوده فروش ایالت ها گنجانده شده باشد آیا فرمول SUM(USSales) ساده‌تر و رساتر نیست؟

در این فصل خواهیم آموخت که چگونه برای یک سلول یا محدوده ای از سلول ها نام بگذاریم . همچین می آموزیم که چگونه از این نام ها در فرمول ها استفاده کنیم.

 

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

برای نامگذاری یک محدوده سه راه وجود دارد:

  • وارد کردن نام در کادر نام ( Name box)
  • با کلیک بر گزینه Create From Selection (ایجاد بوسیله انتخاب) در گروه Defined Names (نامهای تعریف شده) از سربرگ Formulas (فرمول) واقع در نوار ریبون
  • با کلیک بر گزینه Name Manager (مدیر نام) یا Define Name (تعریف نام) در گروه Defined Names از سربرگ Formulas واقع در نوار ریبون .

استفاده از کادر نام برای ایجاد یک نام محدوده

کادر نام درست بالای عنوان ستون A قرار دارد (تصویر 1-2 ) . برای دیدن کادر نام می بایست نوار فرمول نشان داده شده باشد. برای ایجاد یک نامه محدوده به وسیله کادر نام سلول یا محدوده سلول هایی را که مایلید نامگذاری کنید، انتخاب نموده و بر روی Name box کلیک کرده و نامی مورد نظر را در آن بنویسید. سپس کلید Enter را فشار دهید تا نام ایجاد شود .با کلیک بر پیکان کنار Name box می توانید نام هایی را که در کتاب کار تعریف شده‌اند ببینید. همچنین می توانید با فشردن کلید F3 کادر محاوره‌ای Paste Name (چسباندن نام) را باز کرده و تمامی نام های به کار رفته در یک کتاب کار را در آنجا دید. وقتی از کادر Name box نامی را بر می گزینید اکسل سلول های آن نام را انتخاب می کند. این به شما امکان می‌دهد که سلول یا محدوده سلول هایی را که نامگذاری کرده‌اید کنترل کنید. برای نام های محدوده حروف کوچک و بزرگ تفاوتی ندارد.

 

محاسبه دستمزد هفتگی

تصویر 1-2 برای ایجاد نام محدوده به وسیله کادر Name box سلول ها را انتخاب کرده و بعد نام مورد نظر خود را در کادر نام بنویسید.

برای نمونه فرض کنید میخواهید سلول F3 را به عنوان east و سلول F4 را به عنوان west نام گذاری کنید. تصویر 2-2 و فایل Eastwest.xlsx را ببینید. خیلی ساده سلول F3 را انتخاب کرده و در کادر نام کلمه east را تایپ کنید و دکمه Enter را فشار دهید سپس سلول F4 را برگزیده و کلمه west را در کادر نام نوشته و کلید Enter را بفشارید. اکنون اگر در جایی سلول F3 را آدرس دهید به جای آن نام east ظاهر خواهد شد. این بدین معناست که اکسل هر جا در فرمولی کلمه east را ببیند محتویات سلول F3 را در آن وارد خواهد کرد.

 

محاسبه هزینه های نانوایی: قیمت های متفاوت برای هر فروشنده

تصویر 2-2 نامگذاری سلولهای F3و F4 به ترتیب به نام های east و west

 

فرض کنید میخواهید به محدوده مستطیل شکلی از سلولها (مانند A1:B4 ) نامی اختصاص بدهید. به راحتی محدوده را انتخاب کرده و کلمه data را در کادر نام تایپ کرده و کلید Enter را بفشارید. فرمولی مثل =AVERAGE(data)  میانگین محتویات محدوده را محاسبه خواهد کرد. فایل Data.xlsx  و تصویر3-2 را ببینید .

 

استفاده از دکمه AutoSum برای محاسبه جمع کل گروهی از اعد

تصویر 3-2 نامگذاری محدوده A1:B4 به نام data

 

گاهی اوقات ممکن است محدوده‌های مستطیل شکل از سلول ها را که به صورت ناپیوسته اند مانند B3:C4 ، E6:G7 و B10:C10 نام گذاری کنید. برای مثال در تصویر 4-2 و فایل  Noncontigtemp.xlsx  می خواهیم نام noncontig  را به این محدوده ها نسبت دهیم. برای این کار یکی از سه محدوده را انتخاب کرده  (مثلا B3:C4) کلید Ctrl را پائین نگه داشته و سپس دو محدود دیگر را (E6:G7 و B10:C10) انتخاب می کنیم . اکنون کلید Ctrl را رها کرده و در کادر نام ،کلمه noncontig  را تایپ می کنیم و دکمه Enter را می فشاریم.استفاده از نام noncontig  در هر فرمولی به محتویات این سه محدوده رجوع خواهد کرد.برای مثال فرمول =AVERAGE(noncontig)  در سلول E11 حاصلی برابر ۴.۷۵ خواهد داشت زیرا جمع ۱۲ سلول ما برابر ۵۷ خواهد بود و از تقسیم ۵۷ بر ۱۲ عدد ۴.۷۵ حاصل می‌شود.

 

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

تصویر 4-2 نامگذاری محدوده غیر پیوسته ای از سلولها

 

ایجاد نام محدوده با استفاده از گزینه Create From Selection (ایجاد از طریق انتخاب)

کاربرگ Statestemp.xlsx  حاوی فروش ماه مارچ ۵ ایالت امریکاست. تصویر5-2 بخشی از این داده ها را نشان می دهد. می خواهیم هر سلول در این محدوده را با نام اختصاری آن ایالت نامگذاری کنیم. برای این کار محدوده B6:B55 را انتخاب کرده ، از سربرگ Formulas در نوار ریبون و گروه Defined Names  و سپس کلیک بر گزینه Create From Selection این کار را انجام می‌دهیم تصویر 6-2 . سپس از کادر ظاهر شده گزینه Left Column (ستون سمت چپ) را علامت زده دکمه OK را  همانگونه که در تصویر7-2 نشان داده شده کلیک میکنیم.

 

نام گذاری سلول های فروش

تصویر 5-2با نام گذاری سلول های فروش ایالات ۵۰ گانه با علامت اختصاری هر ایالت، می توانید از نام اختصاری ایالت در فرمول ها به جای نام ستون و شماره ردیف استفاده کنیم.

گزینه های کادر Create From Selection

تصویر6-2 گزینه های کادر Create From Selection

گزینه‌های ایجاد نام از  سطر و ستون های خاص

تصویر 7-2 گزینه‌های ایجاد نام از  سطر و ستون های خاص

 

 اکنون اکسل نام های مندرج در ستون اول را به محدوده انتخاب شده در ستون دوم نسبت می دهد لذا به سلول B6  نام AL و سلول B7 نام AK و به همین ترتیب اختصاص داده می‌شود. توجه دارید که انجام این کار به وسیله کادر نام بسیار خسته کننده است. با کلیک بر پیکان کنار کادر نام ، بررسی کنید که نام ها ایجاد شده باشند.

 

ایجاد نام محدوده با استفاده از گزینه Define Name

در نوار ریبون از سربرگ Formulas گروه Defined Names گزینه Define Name را همان گونه که پیشتر در تصویر 6-2 ۲۶ نشان داده شد کلیک کنید .کادر محاوره‌ای همانند تصویر 8-2 باز می شود.

 

کادر محاوره‌ایNew Name  پیش از ایجاد نام محدوده

تصویر 8-2 کادر محاوره‌ایNew Name  پیش از ایجاد نام محدوده

فرض کنید میخواهید به محدوده سلول‌های A2:B7 نام range1  را نسبت دهید. (حروف بزرگ و کوچک در نام محدوده ها تفاوتی ندارند) در کادر نام Name box خیلی ساده range1 را تایپ کرده و سپس محدوده ای را که به آن نسبت داده شده مشخص کنید. اکنون کادر محاوره ای New Name  شبیه تصویر 9-2 خواهد شد دکمه OK را بفشارید و کار تمام است.

 

کادر محاوره‌ای پس از ایجاد یک نام محدوده

تصویر 9-2 کادر محاوره‌ای پس از ایجاد یک نام محدوده

اگر فهرست Scope (گستره) را باز کنید می توانید گزینه کتاب کار یا هر کاربرگی را که مایلید برگزید. این مطلب بعدا توضیح داده خواهد شد ، پس فعلاً گستره پیش فرض یعنی کتاب کار را انتخاب کنید. در ضمن می توانید به نام های محدوده برای روشن تر شدن مقصود از ایجاد آنها توضیحاتی اضافه نمایید.

 

مدیریت نام ( Name Manager)

اگر پیکان کنار کادر نام را در نوار فرمول کلیک کنید نام range1 و هر نام دیگری که ایجاد کرده باشید در آن ظاهر خواهد شد. در اکسل ۲۰۱۹ راه ساده‌ای برای اصلاح یا حذف نام های محدوده وجود دارد . خیلی ساده کادر محاوره‌ای Name Manager  را با کلیک بر سربرگ Formulas در نوار ریبون و کلیک بر گزینه Name Manager  در گروه Defined Names  باز می کنیم. با این کار فهرستی از تمامی نام محدوده ها را خواهید دید. برای نمونه در فایل States.xlsx کادر محاوره‌ای Name Manager شبیه تصویر 10-2خواهد بود.

 

کادر محاوره‌ای Name Manager برای فایل States.xlsx

تصویر 10-2 کادر محاوره‌ای Name Manager برای فایل States.xlsx

برای اصلاح هر نام محدوده ، بر روی آن دوبار کلیک کرده آن را انتخاب نموده و دکمه Edit را کلیک نمایید. آنگاه در کادر محاوره‌ای Edit Name می توانید نام محدوده ، سلولهای نسبت داده شده به آن و گستره نام را تغییر دهید.

برای حذف زیرمجموعه‌ای از نام های محدوده ، اگر به صورت پشت سر هم فهرست شده باشند ابتدا اولین نام محدوده را در گروهی که می خواهید حذف نمایید ، کلیک کنید، سپس دکمه Shift را پایین نگه داشته و آخرین نام محدوده را در گروه کلیک نمایید. اما اگر نام ها به صورت پشت سر هم نباشند کلید Ctrl را پایین نگه داشته و با کلیک بر روی تک تک نام هایی که مایلید حذف کنید آنها را انتخاب می کنیم. در پایان با فشردن کلید Delete کار را به اتمام برسانید.

اکنون اجازه دهید به چند مثال خاص از به کار بردن نام محدوده نظری بیفکنیم.

 

 پاسخ به سوالات فصل

– می خواهم جمع فروش ایالت های آریزونا ،کالیفرنیا ،مونتانا ،نیویورک و نیوجرسی را حساب کنم ، آیا راهی وجود دارد که جمع کل فروش این ایالت ها را به جای اینکه با فرمول SUM(A21:A25) بنویسم به صورت مثلاً AZ+CA+MT+NY+NJ نوشته و پاسخ صحیح را به دست آورم؟

اجازه دهید به فایل States.xlsx بازگردیم که در آن اختصارات نام ایالت ها را برای فروش آنها نسبت داده ایم اگر بخواهید فروش ایالت های آلاباما آلاسکا آریزونا و آرکانزاس را حساب کنید می توانید از فرمول SUM(B6:B9) استفاده نمایید. همچنین می توانید با کلیک بر سلول‌های B6, B7, B8 و B9 این کار را انجام دهید که در آن سرعت فرمول به شکل =AL+AK+AZ+AR خواهد شد. روشن است که گزینه دوم با نام های اختصاری ایالت ها برای فهمیدن بسیار ساده تر است.

ترسیم دیگری از چگونگی به کار بردن نام ها را می توانید در فایل Historicalinvesttemp.xlsx که در تصویر 11-2 نشان داده شده ببینید . این فایل شامل درصد سالانه بازده سهام ، درصد اوراق خزانه داری و درصد اوراق ۱۰ ساله می باشد. (در این تصویر بعضی از داده ها نشان داده نشده و داده ها در سطر ۸۹ خاتمه می یابند.)

 

داده‌های سرمایه‌گذاری تاریخی

تصویر 11-2  داده‌های سرمایه‌گذاری تاریخی

 

با انتخاب محدوده  B1:D89 و کلیک بر گزینه Create From Selection در سربرگ Formulas ، نام ها را از بالاترین ردیف ایجاد کرده ایم . محدوده B1:D89 به نام Stocks ، محدوده  C2:C89 به نام Tbills و محدوده  D2:D89 به نام Bonds10 نامگذاری شده‌اند. پس از تایپ فرمول =AVERAGE( در یک سلول و پیش از تایپ کردن در محدوده کلید F3 را فشرده تا کادر محاوره ای Paste Name (چسباندن نام) همانند تصویر 12-2 ظاهر شود.

 

کادر محاوره‌ای Paste Name

تصویر 12-2 می توانید با استفاده از کادر محاوره‌ای Paste Name هر نامی را که مایلید به یک فرمول اضافه کنید.

اکنون از این کادر گزینه Stocks را برگزیده و OK را کلیک می کنیم ( فرمول در سلول E3 وارد شده است. ) پس از وارد کردن پرانتز پایانی فرمول =AVERAGE(Stocks) متوسط بازده سهام را محاسبه می‌کند (11.41 درصد). زیبایی این روش در این است که حتی اگر به یاد نمی آورید که داده‌ها کجا هستند می توانید با آنها در هر جای کاربرگ کار کنید.

مدیون خواهم بود اگر یادی از قابلیت تکمیل خودکار اکسل ۲۰۱۹ نکنم. اگر فرمول را به این صورت=Average(T تایپ کنید ،اکسل فهرستی از نام های محدوده و توابع را که با حرف T آغاز می شوند نشان خواهد داد. حالا خیلی راحت را از میان آنها Tbills  را کلیک کرده، پرانتز پایانی را بنویسید تا محاسبه میانگین تکمیل شود.

فرمول هایی مثل =AVERAGE(A:A)  چه کار می کنند؟

اگر نام ستون را به شکل A:A, C:C  و مانند آنها در یک فرمول بیاورید اکسل کل ستون را محدوده نامگذاری شده تلقی خواهد کرد. برای مثال فرمول =AVERAGE(A:A) میانگین کل اعداد موجود در ستون A را محاسبه خواهد کرد. به کار بردن نام محدوده برای کل یک ستون ، وقتی که مرتباً داده های جدید در آن ستون وارد می کنید بسیار سودمند خواهد بود. برای مثال اگر ستون A فروش ماهیانه محصولی را در بر داشته باشد ، هر ماهه داده فروش جدیدی وارد خواهد شد و فرمول میانگین به روز فروش ماهیانه را محاسبه خواهد کرد. همین جا هشدار می‌دهم که اگر فرمول =AVERAGE(A:A)  را در یکی از سلولهای ستون A بنویسید پیام چرخه بی انتها را از اکسل دریافت خواهید کرد ، زیرا مقدار سلولهای مندرج در فرمول ، متکی به سلول هایی است که خود این فرمول هم یکی از آنهاست. در فصل ۱۱ “چرخه های بی پایان” خواهید آموخت که چگونه این گونه مشکلات را حل کنید. به همین ترتیب فرمول =AVERAGE(1:1)  میانگین همه اعداد موجود در ردیف ۱ را محاسبه خواهد کرد.

 

 چه تفاوتی میان نام محدوده با گستره کتاب کار و نام محدوده با گستره کاربرگ وجود دارد ؟

فایل Sheetnames.xlsx به شما کمک خواهد کرد تا تفاوت میان نام محدوده با گستره کتاب کار را با نام محدوده با گستره کاربرگ دریابید. وقتی نامی را با استفاده از کادر نام تعریف کنیم آن نام گستره کتاب کار دارد. برای نمونه فرض کنید از کادر نام برای نسبت دادن نام sales به محدوده سلول های E4:E6  در کاربرگ۳ استفاده کرده‌ایم و این سلول ها به ترتیب مقادیر ۱ و ۲ و ۴ را در خود دارد. اگر فرمولی مانند =SUM(sales)   را در هر جای این کتاب کار وارد کنید ، مقدار ۷ را به عنوان پاسخ دریافت خواهید نمود. زیرا کادر نام ، نام ها را با گستره کتاب کار ایجاد می کند. در هر جای کتاب کار به نام sales  ارجاع دهید که گستره کتاب کار دارد ، این نام به محدوده E4:E6 از کاربرگ۳ رجوع خواهد کرد.

بیایید در سلولهای E4:E6 از کاربرگ۱ اعداد ۴ و ۵ و ۶ و در همین محدوده از کاربرگ۲ اعداد ۳ و ۴ و ۵ را وارد کنیم، سپس کادر مدیر نام را از نوار ریبون باز کرده و نام jam را به محدوده E4:E6 از کاربرگ۱ نسبت دهید و گستره این نام را همان کاربرگ۱ تعریف می کنیم. حال به کاربرگ ۲ رفته و کادر مدیر نام را باز کرده و همان نام jam  را به محدوده سلولهای E4:E6 نسبت داده و گستره را  کاربرگ۲ تعریف می‌کنیم. اکنون کادر نام باید شبیه تصویر 13-2 باشد .

 

کادر محاوره‌ای مدیر نام با نام هایی در کاربرگ ها و کتاب کار

تصویر 13-2 کادر محاوره‌ای مدیر نام با نام هایی در کاربرگ ها و کتاب کار 

 

اکنون اگر فرمول =SUM(jam)  را در هر کاربرگ وارد کنیم چه خواهد شد؟ در کاربرگ1 حاصل این فرمول از آنجایی که محدوده در آن حاوی مقادیر و ۴ و ۵ و ۶ می باشد عدد ۱۵ خواهد بود. در صورتی که در کاربرگ۲ حاصل همین فرمول عدد ۱۲ خواهد بود زیرا محدوده حاوی مقادیر ۳ و ۴ و ۵ می باشد. اما در کاربرگ۳ فرمول منجر به تولید خطای #NAME?  خواهد شد زیرا در کاربرگ3 محدوده‌ای با نام  jam وجود ندارد. حال اگر در هر جای کاربرگ3 فرمول  =SUM(Sheet2!jam) را وارد کنید اکسل نام jam با گستره کاربرگ2 که بر محدوده E4:E6 را  معتبر شناخته و عدد 12 را به عنوان حاصل بر می گرداند. بنابر این آوردن نام با گستره کاربرگ در حالیکه پیش از آن نام کاربرگ و به دنبال آن علامت تعجب آمده باشد ، به شما امکان می‌دهد که به یک نام محدوده با گستره کاربرگ در دیگر کاربرگها رجوع کنید.

من از گزینه نام محدوده خوشم آمده و در بسیاری از فایلهای اکسل شروع به استفاده از آنها کرده‌انم. اما نام محدوده‌ها در فرمول‌های من ظاهر نمی شوند چگونه می توانم نام هایی را که اخیراً ایجاد کرده‌ام در فرمول هایی که قبلا نوشته ام نمایش دهم؟

اجازه دهید نظری به فایل Applynames.xlsx  بیاندازیم( تصویر 14-2 را ببینید) .

 

چگونه نام محدوده ها را به فرمول ها اعمال کنیم؟

تصویر 14-2 چگونه نام محدوده ها را به فرمول ها اعمال کنیم؟

قیمت محصول را در سلول F3 از کاربرگ1 و تقاضا برای آن را با فرمول =10000–300*F3  در سلول F4 وارد کرده ایم. هزینه واحد و هزینه ثابت به ترتیب در سلولهای F5  و F6 وسود هم با فرمول =F4*(F3–F5)–F6 در سلول F7  گنجانده شده است. با استفاده از سربرگ Formulas و گزینه Create From Selection و انتخاب Left Column سلول‌های F3 ، F4 ،F5  ، F6 ،F7  را به ترتیب price ، demand ، unit cost ،  fixed cost ، profit نامگذاری کرده ایم،  حال می خواهیم این نام ها در فرمول های موجود در سلول ها ظاهر شوند. برای این کار ابتدا محدوده ای را که می خواهید نام ها در آن اعمال شوند انتخاب کنید (در این مورد F4:F7) .  حال به سربرگ Formulas از نوار ریبون و گروه Defined Names رفته پیکان کنار آن را کلیک کرده سپس از گزینه‌های ظاهر شده گزینه Apply Names را کلیک نمایید. از کادر ظاهر شده نام هایی را که می خواهید انتخاب کرده و دکمه ok را کلیک کنید. توجه کنید که اکنون همانگونه که شما خواستید فرمول سلول F4  به =10000–300*price  و فرمول سلول F7  به  =demand*(price–unitcost)–fixedcost تغییر یافته است .

اگر خواستید که نام ها به کل کاربرگ اعمال گردد با کلیک بر روی دکمه مثلث شکل واقع در تقاطع ردیف ها و ستون ها ،کل کاربرگ را انتخاب کنید.

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

کلید F3  را برای نمایش کادر محاوره‌ای Paste Name فشرده ، سپس دکمه Paste List را کلیک کنید. فهرستی از نام‌های محدوده ها و سلول هایی که بر آنها دلالت دارند در کاربرگ شما ، از مکان سلول فعال فعلی کپی خواهد شد.

 

من در حال محاسبه پیش‌بینی درآمد سال آینده بر اساس مضربی از سال گذشته هستم، آیا در این مورد می توان فرمولی مثل (1+growth)*last year  داشت؟

فایل Last year.xlsx  راه‌حلی برای این مسئله در خود دارد. همانگونه که در تصویر 15-2 نشان داده شده است می‌خواهیم درآمد سال های 2021- 2014 را بر پایه درآمد ۳۰۰ میلیون دلار در سال ۲۰۱۴ ، با رشد ۱۰ درصد در هر سال ، محاسبه کنیم.

ایجاد یک نام محدوده برای سال گذشته

تصویر 15-2 ایجاد یک نام محدوده برای سال گذشته

با نامگذاری سلول B3 به growth از طریق کادر نام آغاز می‌کنیم. اکنون نوبت بخش محشر کار است! مکان نما را به سلول B7 برده و کادر محاوره ای Edit Name  را با کلیک بر گزینه Define Name از سربرگ Formulas از نوار ریبون و گروه Defined Names باز می‌کنیم. این کادر را همانند آنچه در تصویر 16-2 نشان داده شده پر می‌کنیم. در حالی که مکان نما در سلول B7 است ، می‌توانیم نام محدوده ای با عنوان lastyear ایجاد کنیم که به سلولی در یک ردیف بالاتر از سلول فعلی رجوع می‌کند.

 

ایجاد نام محدوده‌ای که در بردارنده فروش سال گذشته باشد

تصویر 16-2 ایجاد نام محدوده‌ای که در بردارنده فروش سال گذشته باشد.

 به خاطر آن که ما هم اکنون در سلول B7 هستیم اکسل این گونه تفسیر می کند که همواره باید به سلول بالای سلول فعلی رجوع کند. البته اگر آدرس حاوی علامت دلار باشد این امر تحقق نخواهد یافت. حال اگر در سلول B7  فرمول =lastyear*(1+growth)  را وارد کرده و آن را به محدوده B8:B13 کپی‌کنیم هر سلول حاوی فرمولی است که عدد ۱.۱ را در محتوای سلول بالای سلول فعال فعلی ضرب خواهد کرد و این همان چیزی است که ما می‌خواستیم.

 

دستمزد ساعتی و ساعت کارکرد هر هر روز هفته به ما داده شده است ،  آیا می توانیم با فرمول ساعت کار ضربدر حقوق ( wages*hours)، دستمزد هر روز را محاسبه کنیم ؟

همانگونه که در تصویر 17-2 نشان داده شده ( فایل Namedrows.xlsx ) ردیف 12 حاوی نرخ روزانه حقوقها و ردیف 13 حاوی ساعات کارکرد هر روز است.

 

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

تصویر 17-2 به کار بردن نام محدوده برای رجوع به ردیف های مختلف

به راحتی با کلیک بر عدد 12 نام ردیف ، کل ردیف 12 را انتخاب کرده و در کادر نام wage  را تایپ نموده  و بهمین ترتیب با انتخاب ردیف 13 در کادر نام  hours را تایپ کنید. اکنون اگر در سلول  F14 فرمول wage*hours را وارد کرده و آن را به محدوده  G14:L14 کپی کنیم خواهید دید که اکسل مقادیر حقوق و ساعت کارکرد را یافته و در هم ضرب می کند.

 

نکات قابل توجه

اکسل اجازه نمی‌دهد که حروف r  و c  را به تنهایی به عنوان نام محدوده به کار برید.

اگر از گزینه Create From Selection برای ایجاد یک نام محدود استفاده کنید و نام شما حاوی فاصله باشد ، اکسل آن را با خط زیر جایگزین خواهد کرد. برای نمونه Product 1 به صورت Product_1 ایجاد خواهد شد.

نام محدوده ها نمی‌توانند با عدد شروع شوند یا شبیه آدرس سلول ها باشند برای مثال 3Q  و A4  نمی‌توانند نام محدوده باشند. از آنجا که اکسل بیش از 16000 ستون دارد ، نام محدوده ای همچون cat1  اجازه داده نمی‌شود زیرا ستونی با همین نام وجود دارد. اگر تلاش کنید برای سلولی این نام را بگذارید اکسل به شما خواهد گفت که این نام نامعتبر است احتمالاً بهترین جایگزین نام cat1_ خواهد بود.

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

 

 مسائل

فایل Stock.xlsx حاوی بازده ماهیانه سهام شرکتهای جنرال موتورز و مایکروسافت است. محدوده بازده سهام هر شرکت را نامگذاری کرده و متوسط بازده ماهیانه سهام هریک را محاسبه کنید

کتاب کاری را باز کرده و محدوده‌های A1:B3  و A6:B8  را به عنوان  Red نامگذاری کنید.

عرض و طول جغرافیایی هر دو شهر داده شده و فایل Citydistances.xlsx از این طریق فاصله بین دو شهر را محاسبه می کند. عرض و طول جغرافیایی هر شهر را نامگذاری کرده و مطمئن شوید که اینها در فرمولی که فاصله شهرها را محاسبه می کند ظاهر شود.

فایل Sharedata.xlsx حاوی تعداد برگه‌های سهام شما از هر سهمی که دارید و قیمت آن است. مقدار ارزش هر سهم را با فرمول shares*price محاسبه کنید.

نام محدوده ای ایجاد کنید که متوسط فروش ۵ سال اخیر را محاسبه کند. فرض کنید که آمار فروش سالیانه همگی در یک ستون آمده است.

 

فایل ها جانبی:
دانلود فایل نمونه
اشتراک گذاری در شبکه های اجتماعی

مایکروسافت اکسل (Excel)

loader

لطفا شکبیا باشید...