img

ایجاد یک ورک شیت سه بعدی در اِکسل

/
/
/

یکی از بهترین ترفندهای اِکسل، مرجع سه بعدی آن است. این مشخصه که به شما امکان ایجاد فرمول هایی برای سلول یا ردیف های مشابه در چندین ورک شیت را می دهد، تهیه اسناد پیچیده همانند گزارش ماهانه فروش و موجودی انبار را تسهیل می نماید. مایکروسافت در اِکسل 2013 این قابلیت را با گزینه ها و فرمول های ارتقا یافته، تقویت نموده است.
ما در اینجا برای نشان دادن قدرت و انعطاف پذیری ورک شیت سه بعدی، یک پروژه با اساس زمانی سال به روز(YTD) ایجاد می کنیم که قبض ماهیانه آب و برق برای یک بیزینس کوچک را با استفاده از مجموع YTD در صفحه اول، محاسبه می نماید. این ورک بوک که شامل تمامی ورک شیت های این پروژه است، ستون ها، ردیف ها و چندین صفحه گسترده را به شکل سه بعدی محاسبه می کند.
به منظور صرفه جویی در زمان، ابتدا صفحه ماه ژانویه را ایجاد کرده و فرمول ها را برای ستون ها و ردیف ها اضافه می کنیم. سپس آن را 12 بار( برای هر ماه یک صفحه و یکی برای صفحه YTD) کپی می کنیم.
این کار را با انتخاب گزینه Blank workbook از منو Home آغاز می کنیم.

عنوان صفحه و تاریخ رسمی
در سلول A1 عبارت «ژانویه 2014» را وارد کنید. اِکسل خود فرمت آن را به Jan-14 تغییر می دهد. بر روی این سلول کلیک راست کرده و از لیست Format Cells را انتخاب نمایید. از گزینه های موجود، Date را انتخاب کنید؛ توجه کنید که در این لیست، سال به شکل چهاررقمی نشان داده نشده است. گزینه Custom را انتخاب کنید. در قسمت Type در بالای لیست، mmmm yyyy را وارد کرده و سپس OK را بزنید. حالا عنوان صفحه January 2014 را نشان می دهد.
در این مثال، محل کار توسط چهار نفر طراح اجاره شده است که مبلغ اجاره به شکل مساوی بین آن ها تقسیم می شود. اما، هزینه آب، برق و تلفن و هزینه های دیگر به نسبت مقدار استفاده بین آن ها تقسیم می شود. به هنگام وارد کردن این اعداد، علامت درصد را هم وارد نمایید.

وارد کردن داده ها و فرمت سلول ها
در سلول های A1 تا A4، این اطلاعات را وارد کنید: مجموع، ماهانه، Carrie C، Marilyn H، Pat B، Donna A و درصد کل. در سلول های A5 تا G5، این اطلاعات را وارد نمایید: آب و برق و تلفن، مجموع، 28%، 32%، 17%، 23% و بازبینی. سلول های A4 تا G5 را انتخاب کرده و از نوار در تب منو Home، گزینه Center را انتخاب نمایید. دقت کنید که شما می توانید ورودی سلول را هم به شکل عمودی و هم به شکل افقی در مرکز قرار دهید.
در سلول های A6 تا A15 اطلاعات زیر را وارد نمایید: برق، گاز، آب، زباله، تلفن مغازه، اینترنت، سرویس هشدار، تعمیر و نگه داری، خدمات تمیزکاری و مجموع. عرض ستون را متناسب با داده های ورودی تنظیم کنید. در سلول های B6 تا B14، این اعداد زا وارد نمایید: 646، 510، 211، 56، 165، 98، 55، 335 و 400. سلول های B6 تا G15 را انتخاب کنید. در تب منو Home، بر Center کلیک کرده و سپس دو بار بر گزینه Increase Decimal کلیک کرده تا فرمت اعداد بر دو رقم اعشار تنظیم گردد.

اضافه کردن فرمول ها
اگر همه ی فرمول ها را کپی کنید، این مرحله به سرعت پیش می رود. در سلول C6، وارد کنید:=sum (B6*C5) و بر کلیک کنید. در حالی که نشانگر را بر C6 نگه داشته اید، می توانید از کلید F2 برای اصلاح فرمول استفاده نمایید. در سلول B6، نشانگر را قبل از B (بین پرانتز سمت چپ و B) قرار دهید. کلید F4 را سه بار فشار دهید تا علامت دلار جلو حرف B ظاهر شود. در حالی که هنوز در حالت اصلاح هستید، نشانگر را به سمت چپ حرف C در C5 منتقل کرده و F4 را چهار بار فشار دهید تا علامت دلار جلوی عدد 5 ظاهرشود. را فشار دهید. فرمول سلول C6 باید شبیه این باشد:=sum($B6*C&5). این ترفند ساده، که مرجع مطلق نام دارد، قسمت هایی از فرمول را که نمی خواهید تغییر دهید، (ستون B و ردیف 5) را قفل می کند.

کپی کردن فرمول ها
نشانگر را بر سلول C6 قرار دهید. از نوار ریبون، گزینه Copy را انتخاب کنید( یا -C را فشار دهید). نشانگر را روی C7 برده و سلول های C7 تا C14 را انتخاب کرده و را بزنید. در حالی که سلول های C7 تا C14 را انتخاب کرده اید، Copy را مجدداً انتخاب کرده و نشانگر را روی D7 ببرید. سپس سلول های D7 تا F14 را انتخاب کرده و را فشار دهید. در چنین حالتی، تمامی سلول ها محاسبات را انجام می دهند.
برای اطمینان از عملکرد صحیح تمامی محاسبات (در صورت بروز خطای نگارشی)، نشانگر را بر G6 قرار داده و این فرمول را وارد نمایید:=sum(C6:F6). سپس را فشار دهید( شما می توانید این حدود را هایلایت کرده و اجازه دهید اِکسل خود محل این سلول ها را پر کند). درحالی که نشانگر بر سلول G6 است، Copy را فشار داده، سلول های G7 تا G14 را انتخاب کرده و را فشار دهید. اگر اعداد ستون G تطابق یک به یک با ستون B داشته باشد، تمامی فرمول های وارد شده صحیح می باشند.

کپی و تغییر اسم صفحات گسترده
این صفحه را 12 بار برای اطلاعات یک سال کپی کنید. در قسمت پایینی صفحه اِکسل، بر تب Sheet1 کلیک نمایید. کلیک راست کرده و از لیست Move or Copy را انتخاب کنید. گزینه Create a Copy را فعال کرده، گزینه move to end را انتخاب و OK را بزنید. این فرایند را تکرار کنید.
با دوبار کلیک بر تب Sheet1، نام آن را به YTD تغییر دهید. به همین ترتیب نام بقیه تب های ایجاد شده را بر اساس ماه های سال تغییر دهید.. سپس عنوان هر صفحه را متناسب با نام درج شده در تب آن تغییر دهید( عنوان هر صفحه را با فرمت صحیح به شکل January 2014 تایپ کنید). نکته: عنوان درج شده در تب صفحات را تا آن جایی که امکان دارد مختصر در نظر بگیرید تا محاسبات ابعادی ساده-تر صورت پذیرند.
در صفحات مربوط به ماه های فوریه تا دسامبر وارد شده و تعدادی عدد تصادفی در ستون مجموع ماهیانه- سلول های B6 تا B14(B15 را در نظر نمی گیریم، چون شامل فرمول است)- وارد کنید. در این مثال، ما فقط ماه های ژانویه تا می را درنظر گرفته ایم.
اضافه کردن فرمول های صفحات سه بعدی
بر صفحه YTD، نشانگر را بر B6 قرار داده و این فرمول را وارد نمایید: =sum(Jan:Dec!B6). این فرمول را از B6 در B7 تا B14 کپی کنید( آن را در B15 کپی نگنید زیرا این فرمول، مقادریر این ستون را جمع می زند). دوباره تأکید می کنیم که در فرمول ها، تنها ماه ژانویه تا می در نظر گرفته شده-اند.
در ادامه، سلول های B6 تا B14 را انتخاب کرده، گزینه Copy را برگزیده و سلول های B6 تا B14 را هایلایت می کنیم. سپس، را می زنیم. اکنون، صفحه YTD مجموع اطلاعات تمام سال را دارا می باشد. هرگاه تغییری در ستونB(مجموع ماهیانه) در صفحات ژانویه تا دسامبر ایجاد نمایید و یا به عبارت دیگر هرگاه مبلغ قبض آب، برق، گاز و… را اصلاح کنید، محاسبات مجدداً با مقادیر جدید در تمامی صفحات انجام می شوند.
در سال 2015، اعداد موجود در ستون B را از B6 تا B14 در تمامی صفحات مربوط به ماه های ژانویه تا دسامبر حذف کنید و سپس اطلاعات صحیح مربوط به مبالغ قبوض ماهیانه را به محض دریافت ثبت نمایید. توجه کنید که مقدار نهایی YTD با اضافه کردن مجموع هر ماه تغییر می کند. به محض اینکه این صفحات را با فرمول ها ایجاد کردید، دیگر لازم نیست تمام این مراحل را طی کنید.

اعمال تغییرات در آینده آسان است
اگر ناچارید که یک قبض (مثل آب یا برق و…) را از ورک شیت سه بعدی حذف کنید یا به آن اضافه کنید، در محدوده محاسبه شده بمانید. برای مثال، فرض کنید شما در ژوئن 2015 استفاده از خدمات هشدار را قطع می کنید: هیچ چیز را تغییر ندهید. تنها در سلول مربوطه در ماه های ژوئن تا دسامبر عدد صفر را وارد نمایید. سپس در ژانویه 2016، نشانگر را بر روی ردیف مربوطه قرار داده و از تب منو Home، Delete Sheet Row را انتخاب می کنیم( این کار را بر روی هریک از صفحات از ژانویه تا دسامبر و همچنین صفحه YTD انجام می دهیم).
برای اضافه کردن یک قبض جدید، نشانگر را جایی بین سلول B6 وB14 قرار داده و سپس Insert Sheet Row را انتخاب کنید. سپس، از ردیف بالا، فرمول ها را در این ردیف کپی کنید. اضافه کردن ردیف های جدید داخل ماتریس، باعث می شود که اعداد و فرمول های موجود در این ردیف ها در فرمول های موج.د در کل پروژه در نظر گرفته شوند. اگر ردیف های جدید را در خارج محدوده ماتریس اضافه نمایید، اعداد و فرم.ل های موجود در این ردیف ها در هیچ یک از محاسبات لحاظ نمی شود. مگر آن که تمام فرمول ها را طوری اصلاح کنید که بازه جدید را دربرگیرند.
توجه کنید که هنگامی که یک ردیف جدید بین B6 و B14 وارد می کنید، ردیف مجموع کل به B16 انتقال می یابد و فرمول این سلول( که اعداد ستون B را با هم جمع می کند) از =sum(B6:B14) به=sum(B6:B15). اگر در داخل بازه ماتریس اصلی بمانید، تمامی مقادیر وارد شده به شکل دقیقی در تمام پروژه در محاسبات لحاظ می شوند.
به محض ایجاد تمامی صفحات، دیگر لازم نیست مراحل طی شده را تکرار کنید.
——————————-
برگردان : مینا محمدی

نظر بدهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

It is main inner container footer text