اکسل برای ایجاد نمودار بر اساس داده ها در یک جدول داده عالی است. می توانید از ابزارهای موجود در برگه Insert نوار برای شناسایی سریع کل جدول داده استفاده کنید یا می توانید بخشی از یک جدول داده را انتخاب کنید و از همان ابزارها برای ایجاد نمودار فقط بر اساس آن قسمت استفاده کنید.
اگر مرتباً محدوده داده را برای نمودار خود تغییر دهید، تغییر مداوم مرجع محدوده داده می تواند خسته کننده باشد. به عنوان مثال، اگر یک جدول داده ای دارید که شامل داده های چند ساله است، ممکن است بخواهید نموداری را مشاهده کنید که بر اساس داده های پنج سال اول است و سپس محدوده داده ها را تغییر دهید تا نمودار به زیرمجموعه دیگری از داده ها. تغییرات را اغلب به اندازه کافی انجام دهید و شروع به جستجو برای یافتن راه هایی برای آسان تر کردن تغییرات (و مطمئن تر) خواهید کرد.
یکی از راه‌های انجام این کار استفاده از محدوده‌های نامگذاری شده و چندین تابع کاربرگ است. بیایید بگوییم که نمودار شما بر روی یک کاربرگ جاسازی شده است، اما کاربرگ با صفحه ای که داده منبع در آن قرار دارد متفاوت است. در همان صفحه نمودار، دو سلول ورودی ایجاد کنید که به عنوان نشانگرهای "از" و "به" عمل می کنند. نام این دو سلول را چیزی شبیه FromYear و ToYear بگذارید.
در کاربرگ داده‌های شما (یکی بدون نمودار، نام کاربرگ را «داده منبع» می‌گذارم)، داده‌ها با هر سال در یک ستون جداگانه و یک سری عوامل هزینه در هر ردیف مرتب می‌شوند. جدول خود را در ستون F شروع کنید و سال های خود را در ردیف 2 قرار دهید. فاکتورهای هزینه را در ستون E قرار دهید و از ردیف 3 شروع کنید. بالای سال ها یک حرف بزرگ که همان حرف ستون است و در ستون D یک عدد قرار دهید. که همان شماره ردیف داده است. (شکل 1 را ببینید.)
شکل 1. مرحله اول آماده سازی داده ها.
در این مثال، نموداری که در کاربرگ دیگر تعبیه شده است، بر اساس داده های محدوده F2:I5 است. هیچ چیز خاصی در مورد نمودار وجود ندارد، اما تغییراتی که برای ایجاد آن آماده می شوید، آن را پویا و در نتیجه بسیار مفیدتر می کند.
هنوز روی کاربرگ «داده منبع» کار می کنید، با قرار دادن فرمول زیر در سلول B1 شروع کنید:
="Trends for " & IF(FromYear=ToYear,FromYear,FromYear & " to " & ToYear)
این فرمول عنوانی پویا ارائه می دهد که بعداً برای نمودار خود استفاده خواهید کرد. به سلول B1 نام addrTitle بدهید ، سپس فرمول زیر را در سلول B2 قرار دهید:
="Source Data!$" & INDEX($F$1:$I$1,1,MATCH(FromYear,$F$2:$I$2)) &
"$" & D2 & ":$" & INDEX($F$1:$I$1,1,MATCH(ToYear,$F$2:$I$2)) & "$" & D2
به یاد داشته باشید که این یک فرمول واحد است، اگرچه برای وضوح در اینجا در دو خط نشان داده شده است. فرمول B2 را در سلول های B3:B5 کپی کنید. این فرمول رشته های آدرسی را برمی گرداند که محدوده های مورد نظر را برای مقادیر محور X و سری داده ها نشان می دهد. بازه های واقعی بازگردانده شده توسط فرمول ها، بر اساس مقادیری که در سلول های FromYear و ToYear در کاربرگ دیگر وارد می کنید، متفاوت خواهد بود. برای شفاف‌تر کردن موارد، می‌توانید برخی از برچسب‌ها را در ستون A وارد کنید (شکل 2 را ببینید.)
شکل 2. مرحله دوم آماده سازی داده ها.
اکنون باید هر یک از سلول های محدوده را B2:B5 نام گذاری کنید. B2 را انتخاب کنید و در Name Box (درست بالای ستون A) نام "addrXVal" (بدون نقل قول) را وارد کنید. به طور مشابه، B3 را به عنوان addrCost1، B4 را به عنوان adrCost2 و B5 را به عنوان adrCost3 نام ببرید.
مرحله بعدی ایجاد چند فرمول نامگذاری شده است که می توانید در ایجاد نمودارها از آنها استفاده کنید. تب Formulas روبان را نمایش دهید و بر روی ابزار Define Name کلیک کنید تا کادر محاوره ای New Name نمایش داده شود. (شکل 3 را ببینید.)
شکل 3. کادر محاوره ای New Name.
در قسمت Name، در بالای کادر محاوره‌ای، "rngXVal" (بدون نقل قول) را تایپ کنید، سپس عبارت زیر را در کادر Refers To تایپ کنید:
=INDIRECT(addrXVal)
روی OK کلیک کنید و نام آن مشخص می شود. سپس، با استفاده از همان کادر محاوره‌ای New Name، نام‌های اضافی (rngCost1، rngCost2، و rngCost3) را تعریف کنید که از همان نوع فرمول INDIRECT برای ارجاع به محدوده‌های adrCost1، addrCost2 و addrCost3 استفاده می‌کنند.
اکنون در نهایت آماده به روز رسانی مراجع در نمودار خود هستید. بر روی نمودار کلیک راست کنید و بسته به نسخه اکسل خود، گزینه Select Source یا Select Data را انتخاب کنید. اکسل کادر محاوره ای انتخاب منبع داده را نمایش می دهد. (شکل 4 را ببینید.)
شکل 4. تب Series از کادر محاوره ای Source Data.
برای هر یک از سری داده های لیست شده در سمت چپ کادر محاوره ای، روی دکمه Edit کلیک کنید و نام و مقادیر را با توجه به نام هایی که تعریف کرده اید وارد کنید. بنابراین، برای سری Cost1 باید نام =Source Data!addrCost1 و مقادیر =Source Data!rngCost1 را وارد کنید . شما می توانید از مراجع و نام های مشابه برای هر یک از سری های داده دیگر نیز استفاده کنید.
توجه داشته باشید که در مراجعی که وارد می‌کنید باید نام کاربرگ خود (داده‌های منبع) را با حروف آپستروف وارد کنید. در مرجع برچسب های محور رده (X) می توانید =Source Data!rngXVal را وارد کنید .
پس از انجام این کار، می‌توانید سال‌های شروع و پایان را در سلول‌های FromYear و ToYear تغییر دهید و اکسل به‌طور خودکار و فورا نمودار را برای نمایش داده‌هایی که مشخص کرده‌اید به‌روزرسانی می‌کند.
برای لمس بیشتر، اگر قبلاً عنوان نموداری را اضافه نکرده اید، ادامه دهید و این کار را انجام دهید. اگر از اکسل 2007 یا اکسل 2010 استفاده می کنید، نمودار را انتخاب کنید، تب Layout روبان را نمایش دهید، ابزار Chart Title را کلیک کنید و راهی را که می خواهید عنوان نمایش داده شود انتخاب کنید. عنوان باید بلافاصله در نمودار ظاهر شود.
اگر از اکسل 2013 یا نسخه جدیدتر استفاده می کنید، روی نماد نمودار عناصر، نزدیک گوشه سمت راست بالای نمودار خود کلیک کنید. (به نظر می رسد یک علامت مثبت باشد.) اکسل یک منوی "fly-out" را نمایش می دهد که عناصر مختلفی را که می توانید به نمودار خود اضافه کنید فهرست می کند. (شکل 5 را ببینید.)
شکل 5. افزودن عناوین به نمودار در اکسل در آفیس 365.
مطمئن شوید که یک علامت چک در کنار عنصر عنوان نمودار ظاهر می شود. عنوان باید بلافاصله در نمودار ظاهر شود.
صرف نظر از نسخه اکسل که استفاده می کنید، یک بار روی عنوان (در خود نمودار) کلیک کنید تا آن را انتخاب کنید. شما باید کادر انتخاب را در اطراف عنوان ببینید. در نوار فرمول موارد زیر را وارد کنید:
=Source Data!addrTitle
عنوان نمودار اکنون به سلول حاوی رشته عنوان پیوند داده شده است، که به نوبه خود هر بار که مقادیر FromYear و ToYear را تغییر می دهید به صورت پویا به روز می شود.
