دیوید یک کاربرگ دارد که از آن برای ردیابی فروش بر اساس شرکت در طی چند ماه استفاده می کند. نام شرکت در ستون A و حداکثر پانزده ماه فروش در ستون B:P آمده است. دیوید مایل است نموداری ایجاد کند که بتوان آن را به صورت پویا تغییر داد تا فروش یک شرکت را از کاربرگ نشان دهد.
راه های مختلفی برای انجام این کار وجود دارد؛ سه مورد از آنها را در این نکته بررسی می کنم. به عنوان مثال، اجازه دهید فرض کنیم که کاربرگ MyData نام دارد و ردیف اول حاوی سرصفحه های داده است. نام شرکت ها در محدوده A2:A151 است و داده های فروش این شرکت ها در B2:P151 است.
یک رویکرد استفاده از قابلیت های فیلتر خودکار Excels است. نمودار خود را همانطور که به طور معمول انجام می دهید ایجاد کنید، مطمئن شوید که نمودار به گونه ای پیکربندی شده است که سری داده های خود را از ردیف های کاربرگ MyData ترسیم کند. همچنین باید نمودار را روی برگه خودش قرار دهید.
اکنون، A1 را در MyData انتخاب کنید و یک AutoFilter اعمال کنید. (برگه Data روبان را نمایش دهید و روی ابزار Filter کلیک کنید.) یک فلش کشویی کوچک در بالای هر ستون ظاهر می شود. روی فلش کشویی ستون A کلیک کنید و شرکتی را که می خواهید در نمودار مشاهده کنید انتخاب کنید. اکسل نمودار را دوباره ترسیم می کند تا فقط یک شرکت را شامل شود.
تنها اشکال احتمالی رویکرد AutoFilter این است که هر شرکت یک سری داده مستقل در نظر گرفته می شود، حتی اگر تنها یکی از آنها در نمودار نمایش داده شود. از آنجایی که آنها مستقل هستند، هر شرکت به رنگ متفاوتی نمودار می شود. اگر می‌خواهید همیشه از همان رنگ‌های نمودار استفاده شود، باید از یکی از روش‌های دیگر استفاده کنید.
راه دیگر برای نزدیک شدن به مشکل، استفاده از یک جدول داده "واسطه" است - جدولی که به صورت پویا ایجاد می شود و فقط اطلاعات مورد نظر شما را از جدول داده های بزرگتر بیرون می کشد. سپس نمودار بر اساس جدول میانی پویا است. این مراحل را دنبال کنید:
شکل 1. کادر محاوره ای Format Control.
=INDEX(MyData!A2:A151,$A$1)
="Data for " & A3
=ChartData!$A$3
اکنون یک نمودار پویا کاملاً کارآمد دارید. می توانید از Combo Box برای انتخاب یک شرکت استفاده کنید و نمودار با استفاده از داده های شرکتی که انتخاب کرده اید دوباره ترسیم می شود. در صورت تمایل، می توانید Combo Box را به برگه حاوی نمودار خود منتقل یا کپی کنید تا هر بار که انتخاب می کنید بتوانید نمودار به روز شده را مشاهده کنید. همچنین می توانید در صورت تمایل، کاربرگ ChartData را مخفی کنید.
رویکرد سوم استفاده از ماکرو برای تغییر محدوده ای است که نمودار بر اساس آن است. برای آماده شدن برای این رویکرد، دو محدوده با نام در کتاب کار خود ایجاد کنید. نام اول باید ChartTitle باشد و باید به فرمول =OFFSET(MyData!$A$1,22,0,1,1) اشاره داشته باشد. نام دوم باید ChartXRange باشد و باید به فرمول =OFFSET(MyData!$A$1,22,0,1,15) اشاره داشته باشد.
با نام های تعریف شده، می توانید محدوده MyData!B1:P2 را انتخاب کرده و نمودار خود را ایجاد کنید. شما باید نمودار را بر اساس این محدوده ساده قرار دهید و مطمئن شوید که مقداری متن موقت را در عنوان نمودار قرار دهید. مطمئن شوید که نمودار روی برگه خودش ایجاد شده است و نام آن را ChartSheet می‌گذارید.
با ایجاد نمودار، روی نمودار کلیک راست کرده و Select Data را انتخاب کنید. اکسل کادر محاوره ای انتخاب منبع داده را نمایش می دهد. سری داده ها را انتخاب کرده و روی Edit کلیک کنید. اکسل کادر محاوره ای Edit Series را نمایش می دهد. هر آنچه در کادر Series Values است را با فرمول زیر جایگزین کنید:
=Book1!ChartXRange
مطمئن شوید که Book1 را با نام کتاب کاری که در آن کار می کنید جایگزین کرده اید. روی OK کلیک کنید و نمودار اکنون بر اساس محدوده نامگذاری شده ای است که قبلاً مشخص کرده اید. اکنون می توانید عنوان نمودار را انتخاب کنید و موارد زیر را در نوار فرمول قرار دهید تا عنوان پویا شود:
=MyData!ChartTitle
اکنون آماده اضافه کردن ماکرو هستید که همه چیز را پویا می کند. ویرایشگر VBA را نمایش دهید و ماکرو زیر را به پنجره کد کاربرگ MyData اضافه کنید. (روی نام کاربرگ در ناحیه Project Explorer دوبار کلیک کنید.)
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
ActiveWorkbook.Names.Add Name:="ChartXRange", _
RefersToR1C1:="=OFFSET(MyData!R1C1," & _
ActiveCell.Row - 1 & ",1,1,15)"
ActiveWorkbook.Names.Add Name:="ChartTitle", _
RefersToR1C1:="=OFFSET(MyData!R1C1," & _
ActiveCell.Row - 1 & ",0,1,1)"
Sheets("ChartSheet").Activate
Cancel = True
End Sub
اکنون می توانید کاربرگ MyData را نمایش دهید و روی هر ردیفی دوبار کلیک کنید. (خب، برای یک ردیف در ستون A دوبار کلیک کنید.) سپس ماکرو محدوده های نامگذاری شده را به روز می کند تا به ردیفی که روی آن دوبار کلیک کرده اید اشاره کنند و سپس صفحه ChartSheet را نمایش می دهد. نمودار (و عنوان) دوباره ترسیم می شود تا داده های ردیفی را که روی آن دوبار کلیک کرده اید منعکس کند.
- یک کاربرگ جدید ایجاد کنید و نام آن را چیزی مانند ChartData بگذارید.
- هدر ستون ها را از کاربرگ MyData در ردیف دوم برگه ChartData کپی کنید. (به عبارت دیگر، MyData!A1:P1 را در ChartData!A2:P2 کپی کنید. با این کار ردیف اول برگه ChartData موقتا خالی می ماند.)
- در حالی که کاربرگ MyData قابل مشاهده است، تب Developer روبان را نمایش دهید.
- با استفاده از ابزار Insert در گروه Controls، یک کنترل Combo Box را در جایی از کاربرگ MyData بکشید. (مطمئن شوید که از جعبه ترکیبی Form Controls استفاده می کنید، نه از جعبه ترکیبی ActiveX Controls.)
- کادر محاوره ای Format Control را برای Combo Box تازه ایجاد شده نمایش دهید. (روی Combo Box راست کلیک کرده و Format Control را انتخاب کنید.)
- با استفاده از کنترل‌های موجود در کادر محاوره‌ای، محدوده ورودی را به عنوان MyData تعیین کنید!$A$2:$A$151، پیوند سلول را به عنوان ChartData!$A$1 مشخص کنید، و خطوط کشویی را 25 (یا هر شکلی که می‌خواهید) مشخص کنید. (شکل 1 را ببینید.)
- روی OK کلیک کنید تا کادر محاوره ای رد شود. اکنون یک Combo Box دارید که پس از استفاده از آن برای انتخاب نام شرکت، مقداری را در سلول A1 کاربرگ ChartData قرار می دهد که نشان می دهد چه چیزی را انتخاب کرده اید.
- با نمایش کاربرگ ChartData، فرمول زیر را در سلول A3 وارد کنید:
- محتویات سلول A3 را در محدوده B3:P3 کپی کنید. ردیف 3 اکنون حاوی داده های هر شرکتی است که در Combo Box انتخاب شده است.
- در سلول B1 فرمول زیر را وارد کنید. (نتیجه این فرمول به عنوان عنوان نمودار پویا شما عمل می کند.)
- سرصفحه ها و داده های ستون را انتخاب کنید (B2:P3) و نموداری را بر اساس این داده ها ایجاد کنید. عنوان نمودار را بر روی متنی قرار دهید. مهم نیست الان چی هست
- در نمودار تمام شده، عنوان نمودار را انتخاب کنید.
- در نوار فرمول فرمول زیر را وارد کنید:
