اشکال کاربری (User forms) در اکسل

07 فروردین 1401

دقیقه

اشکال کاربری (User forms)، کاربر اکسل 2019 را قادر می‌سازند تا مجموعه متنوعی از کنترل‌کننده‌های بسیار مفید و عالی را به کاربرگ خود اضافه کنند. در این فصل به شما نشان خواهیم داد که چقدر آسان می‌توان از دکمه‌های چرخشی، نوارهای پیمایش، دکمه‌های انتخابی، چک باکس‌ها، کادرهای ترکیبی و کادرهای حاوی فهرست استفاده کرد.

آخرین به‌روزرسانی: 27 دی 1401

در سری مقاله های آموزش اکسل، در فصل گذشته به بررسی بخش اول جداول اکسل و اسلایسرهای جداول (Table Slicers) پرداختیم، در این مقاله به ادامه آموزش Table Slicers می پردازیم.

اشکال کاربری (User forms)، کاربر اکسل 2019 را قادر می‌سازند تا مجموعه متنوعی از کنترل‌کننده‌های بسیار مفید و عالی را به کاربرگ خود اضافه کنند. در این فصل به شما نشان خواهیم داد که چقدر آسان می‌توان از دکمه‌های چرخشی، نوارهای پیمایش، دکمه‌های انتخابی، چک باکس‌ها، کادرهای ترکیبی و کادرهای حاوی فهرست استفاده کرد.

 

سؤال‌هایی که در این فصل پاسخ داده خواهند شد

  • می‌بایست تحلیل حساسیتی را انجام دهم که اقلام ورودی اصلی زیادی چون: فروش سال اول، میزان رشد فروش، قیمت سال اول و هزینه واحد را دارا می‌باشد. آیا راهی هست که بتوان خیلی سریع این داده‌های ورودی را تغییر داد تا بتوان تأثیر آن تغییر را مثلاً در محاسبه ارزش فعلی کل مشاهده کرد؟
  • چگونه می‌توان چک باکس ساده‌ای تنظیم کرد که بتواند قالب‌بندی شرطی ای را فعال یا غیرفعال کند؟
  • چگونه می‌توان کاربرگی را به‌گونه‌ای تنظیم کرد که پرسنل زنجیره تأمین بتوانند با کلیک بر یک دکمه انتخاب کنند که برای یک محصول قیمتی بالا، پایین و یا متوسط تخصیص‌داده‌شود.
  • چگونه می‌توان راه ساده‌ای برای کاربر یک کاربرگ اکسل ایجاد کرد تا بتواند روزهای هفته را بدون تایپ کردن آنها در آن کاربرگ وارد نماید؟

 

برای دستیابی به این اشکال کاربری به تب Developer در روی نوار منو رفته و بعد در گروه Controls روی گزینه Insert کلیک کنید تا اشکال کنترلی نمایش داده شوند. (این کنترل‌ها را نباید با کنترل‌های Active X اشتباه بگیرید که معمولاً در زبان برنامه‌نویسی مایکروسافت ویژوال بیسیک برای نرم‌افزارها [VBA] مورداستفاده قرار می‌گیرد.)

توجه: برای نمایش تب Developer بر روی تب File کلیک کرده و سپس روی گزینه Options کلیک می‌کنیم. حالا از منوی سمت چپ گزینه Customize Ribbon را انتخاب کرده در بخش Main Tabs گزینه Developer را تیک بزنید و بعد روی دکمه OK کلیک می‌کنیم.

اشکال کاربری که قرار است موردبحث قرار گیرند در تصویر 1-27 نمایش‌داده‌شده‌اند (همچنین می‌توانید فایل Controls.xlsx را در پوشه Practice Files همین فصل ببینید)

اشکال مختلف کنترل‌های کاربر در اکسل - User forms

تصویر 1-27 اشکال مختلف کنترل‌های کاربر در اکسل

پاسخ به سؤالات این فصل

می‌بایست تحلیل حساسیتی را انجام دهم که اقلام ورودی اصلی زیادی چون: فروش سال اول، میزان رشد فروش، قیمت سال اول و هزینه واحد را دارا می‌باشد. آیا راهی هست که بتوان خیلی سریع داده‌های ورودی را تغییر داد تا بتوان تأثیر آن تغییر را مثلاً در محاسبه ارزش فعلی کل مشاهده کرد؟

همان‌طور که در فصل 19 ، استفاده از مدیریت سناریو برای انجام تحلیل‌های حساسیت توضیح دادیم، گزینه Scenario Manager (در تب Data در گروه گزینه‌های Forecast و در زیر گزینه What-If Analysis) به شما اجازه می‌دهد گروهی از سلول‌های دارای ارقام را تغییر دهید تا خروجی‌های مختلف این تغییرات را مشاهده و بررسی نمایید. متأسفانه می‌بایست هر سناریو را جداگانه در Scenario Manger وارد کنید که این کار ایجاد بیش از یک سناریو را کمی مشکل کرده است. مثلاً فرض کنید به نظر شما چهار ورودی تعیین‌کننده ارزش خالص فعلی (NPV) یک مدل اتومبیل: فروش سال اول، میزان رشد فروش، قیمت سال اول و هزینه تولید سال اول می‌باشد. (فایلی به نام NPVspinners.xlsx را ببینید) می‌خواهید بدانید میزان NPV چگونه با تغییرات این ورودی‌ها در محدوده‌های زیر تغییر خواهد کرد:

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

دکمه چرخشی ابزاری کنترلی است که به سلول خاصی ارتباط داده می شود. وقتی روی پیکان بالا یا پایین دکمه چرخشی کلیک می‌کنید، مقدار سلول مرتبط تغییر پیدا می‌کند. می‌توانید ببینید که چگونه فرمول‌های موردنظر (از قبیل فرمول محاسبه ارزش خالص فعلی یک اتومبیل) در جواب به تغییرات ورودی‌ها دچار تغییر می‌شود. مثال‌هایی از اینکه چگونه دکمه‌های چرخشی می‌توانند با کلیک روی پیکان‌های خود باعث تغییر ارزش شوند در محدوده D2:D5 نشان‌داده‌شده است.

روش ایجاد دکمه‌های چرخشی که به شما اجازه دهد تا مقادیر فروش سال اول، میزان رشد فروش، قیمت سال اول و هزینه سال اول را در محدوده دلخواه محاسبه کنید به این شکل است: کاربرگ The Original Model(فایلی به نام NPVspinnerstemp.xlsx را در پوشه Templates فصل 27 مشاهده کنید) در تصویر 2-27 نمایش‌داده‌شده است.

کاربرگ The Original Model بدون دکمه‌های چرخشی میزان ارزش فعلی خالص محصول را در سلول B19 محاسبه می‌کند

تصویر 2-27 کاربرگ The Original Model بدون دکمه‌های چرخشی میزان ارزش فعلی خالص محصول را در سلول B19 محاسبه می‌کند.

برای ایجاد دکمه‌های چرخشی، ردیفی را که می‌خواهید در آن دکمه‌های چرخشی بگذارید را انتخاب کنید (در این مثال ردیف 5-2 را انتخاب کرده‌ایم) و پس با راست کلیک کردن و انتخاب Row Height بلندای ردیف را افزایش می‌دهیم. اندازه بلندای 27 معمولاً برای پیکان‌های دکمه‌های چرخشی اندازه مناسبی است. می‌توانید به هنگام ایجاد کنترل، کلید Alt را نگه دارید تا مناسب اندازه سلول شود.

با کلیک کردن بر گزینه Insert در گروه گزینه‌های Controls در تب Developer منوی User Forms را ظاهر کنید. (به‌خاطر داشته باشید که این تب را از صفحه Custom Ribbon در کادر محاوره‌ای Excel Options فعال کرده بودیم) حالا بر روی ابزار کنترلی Spin Button کلیک می‌کنیم (این کنترل در تصویر 1-27 نشان‌داده‌شده است) نشانگر ماوس شما اکنون به شکل یک علامت بعلاوه (+) تغییر شکل می‌دهد و با کلیک کردن بر روی هرجایی که می‌خواهید دکمه‌های چرخشی را پیاده کرده و به شما اجازه می‌دهد آن دکمه چرخشی را ترسیم کنید.

دکمه را در سلول D2 ترسیم می‌کنیم. برای تغییر این شکل کنترلی یا تغییر مکان آن کافی است دکمه Ctrl را نگاه‌داشته و روی آن کلیک کنید تا انتخاب شود. وقتی که نشانگر ماوس به یک پیکان چهار سویه تغییر کرد، ابزار کنترلی را بکشید تا آن را تغییر مکان بدهید. وقتی که نشانگر ماوس به یک پیکان دوسویه تغییر کرد می‌توانید ابزار کنترلی را بکشید تا تغییر اندازه دهد.

حالا دکمه چرخشی در سلول D2 قرار گرفته است. می‌توانید از این دکمه برای تغییر ارزش فروش سال اول استفاده کنید. برای انجام این کار روی دکمه چرخشی راست کلیک می‌کنیم و بعد گزینه Copy را کلیک می‌کنیم. روی سلول D3 راست کلیک کرده و این بار گزینه جای‌گذاری (Paste) را انتخاب می‌کنیم. دکمه چرخشی را در سلول‌های D4 و D5 نیز جای‌گذاری می‌کنیم. حالا درست مثل تصویر 3-27 می‌بایست چهار دکمه چرخشی را مشاهده کنید.

قراردادن دکمه‌های چرخشی در سلول‌های کاربرگ

تصویر 3-27 قراردادن دکمه‌های چرخشی در سلول‌های کاربرگ

حالا می‌بایست هریک از دکمه‌های چرخشی را به سلول ورودی مرتبط کنید. برای ایجاد ارتباط دکمه موجود در سلول D2 به سلول C2 ،روی دکمه چرخشی سلول D2 راست کلیک کرده و سپس گزینه Format Control را انتخاب می‌کنیم. کادر محاوره‌ای Format Control را همان‌طور که در تصویر 4-27 نشان‌داده‌شده تکمیل می‌کنیم. در اینجا عدد 10000 را برای ارزش فعلی، 5000 را برای کمترین ارزش، 30000 را برای بالاترین ارزش، 1000 را برای تغییرات تدریجی و $C$2 را به‌عنوان لینک ارتباطی سلول قرار می‌دهیم. گزینه 3-D Shading را به همان شکل انتخاب شده باقی می‌گذاریم و روی دکمه Ok کلیک می‌کنیم.

استفاده از کادر محاوره‌ای Format Control برای مرتبط کردن فروش سال اول به دکمه چرخشی

تصویر 4-27 استفاده از کادر محاوره‌ای Format Control برای مرتبط کردن فروش سال اول به دکمه چرخشی

فیلد ارزش فعلی چندان اهمیتی ندارد. باقی تنظیمات به اکسل می‌گویند که این دکمه چرخشی به مقادیر موجود در سلول C2 (فروش سال اول) مرتبط است، و اینکه هر کلیک روی پیکان سربالا مقادیر را در سلول C2 به میزان 1000 رقم بالا می‌برد و هر کلیک روی پیکان سرپایین ارزش سلول C2 را به میزان 1000 رقم پایین می‌آورد. هنگامی گه ارزش سلول C2 به 30000 برسد، کلیک کردن بر پیکان سربالا دیگر آن را افزایش نخواهد داد و وقتی که ارزش در سلول C2 به 5000 برسد، کلیک کردن روی پیکان سرپایین مقدار موجود در سلول C2 را پایین‌تر نمی‌برد.

حالا از کادر محاوره‌ای Format Control برای مرتبط کردن دکمه چرخشی سلول D4 به قیمت سال اول (سلول C4) استفاده می‌کنیم. (بعداً به دکمه چرخشی سلول D3 خواهیم پرداخت). برای مقدار فعلی از عدد 9 استفاده می‌کنیم. کمترین مقدار را عدد 6 ، بیشترین مقدار را عدد 20 و مقدار ارزش تدریجی را عدد 1 قرار می‌دهیم. دکمه چرخشی را به سلول C4 مرتبط می‌کنیم. اکنون کلیک کردن روی پیکان‌های دکمه چرخشی در سلول D4 ارزش قیمت سال اول را بین 6 دلار و 20 دلار با مقدار تغییر تدریجی 1 دلار تغییر می‌دهد.

برای مرتبط کردن دکمه چرخشی در سلول D5 به هزینه‌های سال اول (در سلول C5) از عدد شش برای ارزش فعلی، عدد 2 برای حداقل ارزش، عدد 15 برای حداکثر ارزش و عدد 1 به‌عنوان میزان تغییر تدریجی استفاده می‌کنیم. دکمه چرخشی را به سلول C5 مرتبط می‌کنیم. حالا کلیک کردن بر پیکان‌های سلول D5 میزان هزینه‌های سال اول را از 2 دلار به 15 دلار با میزان تغییر تدریجی 1 دلار تغییر می‌دهد.

ارتباط دادن دکمه چرخشی در سلول D3 به میزان افزایش فروش کمی دشوارتر است. می‌خواهیم این دکمه چرخشی کنترلی میزان رشد فروش را به صفر درصد، یک درصد و الی 50 درصد افزایش دهد. مشکل اینجاست که حداقل تغییر تدریجی‌ای که یک دکمه چرخشی می‌تواند انجام دهد 1 می‌باشد؛ بنابراین می‌بایست این دکمه چرخشی را به مقداری ساختگی در سلول E3 مرتبط کرده و فرمول E3/100 را در سلول C3 قرار دهید.

تصویر 5-27 چگونگی مرتبط کردن این دکمه چرخشی به سلول E3 را نشان می‌دهد: در اینجا عدد 48 را برای مقدار فعلی عدد صفر را برای حداقل مقدار، عدد 50 را برای حداکثر مقدار و عدد 1 را برای میزان تغییرات تدریجی قرار داده و دکمه را به سلول E3 مرتبط می‌کنیم. به‌خاطر داشته باشید که رقم رشد فروش در سلول C3 تنها عددی در سلول E3 است که بر عدد 100 تقسیم شده است.

راستی، اگر نشانگر ماوس درون یک کنترل‌کننده قرار گرفته باشد و کلید Ctrl را فشار داده و نگه دارید، می‌توانید به‌سادگی از دستگیره‌های ظاهر شده برای تغییر اندازه آن کنترل استفاده کنید.

استفاده از کادر محاوره‌ای Format Control برای تنظیم ارتباط دکمه چرخشی درون سلول D3 به سلول E3

تصویر 5-27 استفاده از کادر محاوره‌ای Format Control برای تنظیم ارتباط دکمه چرخشی درون سلول D3 به سلول E3

 از کادر محاوره‌ای Format Control برای ایجاد تغییرات سلول E3 بین ارقام صفر تا 50 با میزان کاهش یا افزایش یک‌رقمی استفاده می‌کنیم؛ در نتیجه مقادیر سلول C3 بین صفر و 0.50 با افزایش و کاهشی 0.01 رقمی تغییر می‌کنند.

با کلیک کردن روی دکمه چرخشی می‌توانید به‌سادگی ببینید چگونه تغییر تنها یک سلول ورودی – باتوجه‌به مقادیر داده شده سایر ورودی‌های لیست شده در کاربرگ – میزان ارزش خالص فعلی اتومبیل را تغییر می‌دهد. برای دیدن تأثیر این تغییرات، می‌توانید سلول F9 را انتخاب کنید، روی گزینه Freeze Panes در تب View (در گروه گزینه‌های Window) کلیک کرده و بعد دوباره روی گزینه Freeze Panes کلیک کنید. این فرمان باعث می‌شود داده‌های بالاتر از ردیف 9 در سمت چپ ستون F ثابت باقی بمانند. حالا می‌توانید از دکمه‌های پیمایشی در سمت راست پنجره استفاده کنید تا همان‌طور که در تصویر 6-27 می‌بینید آنها را مرتب نمایید.

ثابت کردن سرستون‌ها برای نمایش محاسبات در سایر بخش‌های یک کاربرگ

تصویر 6-27 ثابت کردن سرستون‌ها برای نمایش محاسبات در سایر بخش‌های یک کاربرگ

کلیک کردن بر پیکان‌های دکمه‌های چرخشی، باتوجه‌به مقادیر سایر ورودی‌های کاربرگ نشان می‌دهد که یک درصد افزایش در رشد فروش ارزشی برابر با 2000 دلار دارد. (برای برگرداندن کاربرگ به حالت نرمال، در تب Views روی گزینه Freez Panes کلیک کرده و این بار Unfreeze Panes را انتخاب کنید.

کنترل پیمایشی (Scrollbar) بسیار شبیه به دکمه‌های چرخشی عمل می‌کند. تفاوت اصلی آن است که با تکان دادن نمایشگر ماوس بر روی منطقه خاکستری در میان این پیمایشگر می‌توانید مدام مقدار سلول ارتباط داده شده را تغییر دهید. با انتخاب گزینه Format Control در منوی میان بر و تغییر مقدار در قسمت Page Change از کادر محاوره‌ای Format Control می‌توانید سرعت تغییر سلول‌های مرتبط را به‌دلخواه کنترل کنید.

 

چگونه می‌توان چک باکس ساده‌ای تنظیم کرد که بتواند قالب‌بندی شرطی ای را فعال یا غیرفعال کند؟

چک باکس یکی از اشکال کنترلی است که در صورت انتخاب‌شدن نشان می‌دهد که مقدار موردنظر درست و در صورت انتخاب نشدن نشان می‌دهد مقدار موردنظر نادرست می‌باشد. از چک باکس‌ها همچنین می‌توان برای دکمه‌های تغییر وضعیتی استفاده کرد که یک ویژگی خاص را فعال یا غیرفعال می‌سازد. در اینجا در مثالی به شما نشان می‌دهیم که چگونه از یک چک باکس استفاده کنید تا ویژگی قالب‌بندی خودکاری را فعال یا غیرفعال کنید.

فرض کنید کاربرگی حاوی فروش ماهیانه است و قصد داریم پنج تا از بیشترین فروش‌ها را به رنگ سبز و پنج تا از کمترین فروش‌ها را به رنگ قرمز درآوریم. (فایلی به نام Checkbox.xlsx را مشاهده کرده و روی چک باکس‌ها کلیک کنید تا ببینید چگونه قالب‌بندی‌ها را تغییر می‌دهد) در سلول G4 فرمول =LARGE(Sales,5) را وارد کنید که مقادیر پنج فروش بسیار بالا را محاسبه می‌کند. سپس همان‌طور که در تصویر 7-27 نمایش‌داده‌شده، با واردکردن فرمول =SMALL(Sales,5) در سلول H4 مقادیر پنج فروش بسیار پایین را محاسبه نمایید.

استفاده از چک باکس‌ها برای فعال یا غیرفعال‌کردن قالب‌بندی خودکار

تصویر 7-27 استفاده از چک باکس‌ها برای فعال یا غیرفعال‌کردن قالب‌بندی خودکار

سپس چک باکسی ایجاد کرده و آن را به‌گونه‌ای تنظیم کنید تا بتواند در سلول F1 عبارت‌های True و یا False را نمایش دهد. فایلی به نام Checkboxtemp.xlsx را از پوشه Templates همین فصل باز کنید. در تب Developer روی گزینه Insert کلیک کرده و از منوی کشویی Form Controls چک باکس را انتخاب کنید. حالا نشانگر ماوس به شکل علامت به‌اضافه درمی‌آید. نشانگر را روی سلول G9 بکشید و سپس متن آن را به Turn Formatting On or Off(فعال یا غیرفعال‌کردن قالب‌بندی) تغییر دهید (برای انجام این کار، روی ابزار کنترل راست کلیک کرده و Edit Text را انتخاب کنید) شاید لازم باشد که کنترل را کمی بزرگ‌تر کنید تا متن شما در آن به‌درستی جای بگیرد.

حالا روی چک باکس راست کلیک کرده و سپس گزینه Format Control را کلیک کنید و کادر محاوره‌ای را همان‌طور که در تصویر 8-27 نمایش‌داده‌شده پر کنید: گزینه Checked را انتخاب می‌کنیم؛ چک باکس را به سلول F1 مرتبط کرده و روی دکمه Ok کلیک می‌کنیم.

کادر محاوره‌ای Format Control برای تنظیم یک چک باکس

تصویر 8-27 کادر محاوره‌ای Format Control برای تنظیم یک چک باکس

حالا هرگاه روی چک باکس کلیک کنید در سلول F1 عبارت True ظاهر می‌شود و هرگاه چک باکس را غیرفعال کنید عبارت False در سلول F1 قرار داده می‌شود.

پس از انتخاب محدوده سلول D4:D29 در گروه گزینه‌های Styles در تب Home روی گزینه Conditional Formatting کلیک می‌کنیم. گزینه Use A Formula To Determine Which Cells To Format را انتخاب می‌کنیم و فرمول نشان‌داده‌شده در تصویر 9-27 یعنی =AND($F$1,D4>=$G$4) را وارد می‌کنیم تا پنج فروش بالاتر را به رنگ سبز درآوریم. (روی گزینه Format و سپس تب Fill کلیک کرده، رنگ سبز را انتخاب کرده و سپس روی دکمه Ok کلیک می‌کنیم) سپس فرمول نشان‌داده‌شده در تصویر 10-27 یعنی =AND($F$1,D4<=$H$4) را وارد می‌کنیم تا پنج تا از پایین‌ترین فروش‌ها را به رنگ قرمز درآوریم.

توجه کنید که بخش AND($F$1) از فرمول برای آن است که اطمینان حاصل کنیم که قالب‌بندی تنها وقتی اعمال شود که مقدار سلول F1 برابر با True باشد. البته این چک باکس است که مشخص می‌کند که آیا سلول F1 دارای مقادیر True یا False باشد، بنابراین اگر چک باکس انتخاب نشده باشد سلول‌ها سبز یا قرمز نخواهند شد.

تغییر قالب‌بندی برای سبز کردن پنج فروش برتر

تصویر 9-27 تغییر قالب‌بندی برای سبز کردن پنج فروش برتر

تغییر قالب‌بندی برای تبدیل پنج فروش کمتر به رنگ قرمز

تصویر 10-27 تغییر قالب‌بندی برای تبدیل پنج فروش کمتر به رنگ قرمز

اگر بخواهید می‌توانید سلول F1 را انتخاب کرده و رنگ فونت آن را به سفید تبدیل کنید تا عبارت‌های True و False را پنهان سازید.

چگونه می‌توان کاربرگی را به‌گونه‌ای تنظیم کرد که پرسنل زنجیره تأمین بتوانند با کلیک بر یک دکمه انتخاب ، برای یک محصول قیمتی بالا، پایین و یا متوسط تخصیص‌ دهند.

فرض کنیم که می‌توانید برای یک محصول سه قیمت در نظر بگیریم: قیمت بالا، متوسط و یا پایین. این قیمت‌ها در سلول‌های B7:B9 از فایلی به نامOptionbuttons.xlsx فهرست شده‌اند (کلیک کردن روی دکمه‌های انتخابی را امتحان کنید) می‌تواند به‌راحتی از جدول جستجویی (Lookup Table) استفاده کرد تا چنانچه کاربری عبارت‌های بالا، متوسط و یا پایین را تایپ کرد، قیمت را نمایش دهد. اما طراحی مناسب‌تر آن است که کاربر بتواند دکمه انتخابی را که روی آن نوشته شده قیمت بالا، قیمت متوسط و یا قیمت پایین انتخاب کند و سپس فرمولی به شکل خودکار قیمت را محاسبه نماید. (تصویر 11-27) این نوع دکمه را دکمه رادیویی (Radio Button) نیز می‌نامند.

استفاده از دکمه‌های انتخابی برای انتخاب قیمت یک محصول

تصویر 11-27 استفاده از دکمه‌های انتخابی برای انتخاب قیمت یک محصول

فایل Optionbuttonstemp.xlsx از پوشه Templates این فصل را باز کنید. برای استفاده از دکمه‌های انتخابی، ابتدا با انتخاب کنترل Group Box از منوی User Forms در تب Developer یک کادر گروهی یا همان Group Box بکشید، نام نمایشی (Screen Tip) این کنترل(Form Control) Group Box می‌باشد و در انتهای گوشه سمت چپ فهرست User Forms قرار گرفته است. این کنترل را در محدوده سلول‌های A14:C21 بکشید، روی آن راست کلیک کرده و گزینه Edit Text را انتخاب کنید و متن عنوان این کادر گروهی را به Select Price تغییر دهید.

حالا برای هریک از انتخاب‌هایتان یک دکمه انتخابی به درون این کادر گروهی بکشید. ازآنجاکه سه سطح قیمت دارید، می‌بایست سه دکمه انتخابی به درون کادر گروهی بکشید. برای انجام این کار در تب Developer روی گزینه Insert کلیک کرده و کنترل دکمه انتخابی (Option Button) را انتخاب کنید (تصویر 1-27 در ابتدای همین فصل را ببینید) و بعد نشانگر ماوس را در سلول B16 بکشید. روی هریک از دکمه‌های انتخابی کلیک کرده و سپس از فرمان Format Control استفاده کنید تا هریک از آنها را به سلولی مرتبط کنید.

در اینجا هریک از دکمه‌های انتخابی را (که آنها را قیمت بالا، قیمت متوسط و قیمت پایین نامیده‌ایم) به سلول E4 مرتبط می‌کنیم. اکنون تمام دکمه‌های انتخابی درون کادر گروهی به یک سلول مرتبط هستند. انتخاب اولین دکمه انتخابی عدد یک را در سلول E4 وارد خواهد کرد، انتخاب دکمه انتخابی دوم عدد 2 را در سلول E4 وارد خواهد کرد و انتخاب سومین دکمه انتخابی عدد 3 را در سلول E4 وارد خواهد کرد.

واردکردن فرمول = INDEX(A7:A9,E4,1) در سلول E7 توصیف قیمت مربوط به دکمه انتخابی منتخب را به ما می‌دهد. واردکردن فرمول =VLOOKUP(E7,A7:B9,2,FALSE) در سلول F7 قیمت مرتبط به دکمه انتخابی منتخب را محاسبه می‌کند.

چگونه می‌توان راه ساده‌ای برای کاربر یک کاربرگ اکسل ایجاد کرد تا بتواند روزهای هفته را بدون تایپ کردن آنها در آن وارد نماید؟

فایلی به نام Combobox.xlsx نشان‌دهنده نحوه استفاده از یک کامبو باکس یا لیست باکس (تصویر 12-27 را ببینید) برای قادر ساختن کاربر جهت انتخاب آسان موردی از یک لیست است. (ویژگی Data Validation یا ارزیابی داده‌ها ایجاد کادرهای باز شدنی را بسیار آسان می‌سازد، فصل 42 یا ارزیابی داده‌ها را مشاهده کنید) در اینجا هدف آن است که تعداد ساعت‌هایی که کارگری در روزی خاص کارکرده را محاسبه کنید. ساعت‌های کار شده هر روز در محدوده G9:G15 لیست شده‌اند.

کامبو باکس یا لیست باکس به شما اجازه می‌دهند تا هر مدخل (مقداری) را از لیستی انتخاب کنید. اگر کامبو باکس یا لیست باکس به یک سلول مرتبط باشند (از طریق Format Control) چنانچه اولین ورودی در آن لیست انتخاب شود، عدد 1 در سلول ارتباط داده شده درج می‌شود، اگر دومین ورودی در لیست انتخاب شود عدد 2 در سلول مرتبط درج می‌شود و به همین روال. برای تنظیم کاربرگ، ابتدا تب Developer را ظاهر کنید، روی Insert کلیک کرده از منوی Form Control گزینه Combo Box را انتخاب کنید (تصویر 1-27 را ببینید) و یک مستطیل بزرگ بالای سلول C5 بکشید تا کامبو باکس را در آنجا مستقر کنید.

سپس به سلول B14 یک لیست باکس یا کادر فهرست اضافه کنید. روی کامبو باکس راست کلیک کرده و سپس گزینه Format Control را کلیک کنید. محدوده ورودی F9 :F15 را انتخاب کرده (این محدوده حاوی روزهای هفته است) و آن را به سلول A8 مرتبط کنید. سپس روی لیست باکس راست کلیک کرده و Format Contorl را انتخاب کنید. محدوده ورودی F9:F15 را انتخاب کرده و آن را به سلول A13 مرتبط کنید. بعد از انجام این مراحل می‌بینید که چنانچه مثلاً روز سه‌شنبه را از کامبو باکس و روز دوشنبه را از لیست باکس انتخاب کنید در سلول A8 عدد 2 و در سلول a13 عدد 5 را خواهید دید.

استفاده از کامبو باکس و لیست باکس

تصویر 12-27 استفاده از کامبو باکس و لیست باکس

فرمول =INDEX(F9:F15,A8,1) در سلول F3 روز‌های هفته مرتبط با انتخاب‌های کامبو باکس را فهرست می‌کند. در سلول G3 نیز فرمول =VLOOKUP(F3,$F$9:$G$15,2,False) به شکل مشابهی تعداد ساعت‌های کار انجام شده در روز انتخاب شده در کامبو باکس را مشخص می‌کند.

مسئله‌های این فصل

دکمه چرخشی ای به مثال ارزش خالص فعلی اتومبیل‌ها اضافه کنید که به شما اجازه دهد که نرخ مالیات را بین 30 درصد و 50 درصد تغییر دهید.

دکمه‌ای چرخشی به مثال ارزش خالص فعلی اتومبیل‌ها اضافه کنید که به شما اجازه دهد نرخ بهره را بین 5 درصد و 20 درصد تغییر دهید.

کادر محاوره‌ای Format Control اجازه استفاده از حداقل ارزش صفر را می‌دهد. با وجود این محدودیت، آیا می‌توانید راهی پیدا کنید تا از دکمه چرخشی ای استفاده کنید تا میزان فروش را بین 10- درصد و 20 درصد تغییر دهد؟

از مثال لیموناد در فصل 17: آنالیز حساسیت با جداول داده استفاده کنید تا دکمه‌های چرخشی ای ایجاد کنید که تغییر داده‌های ورودی را در محدوده‌های زیر امکان‌پذیر سازد:

با استفاده از مثال پرداخت وام مسکن در فصل 17، دکمه‌های چرخشی ایجاد کنید که اجازه دهند تا داده‌های ورودی را در محدوده‌های زیر تغییر دهید:

برای مثال آخر هفته استفاده شده در فصل 24: قالب‌بندی شرطی، چک باکسی ایجاد کنید که قالب‌بندی شرطی را فعال یا غیرفعال نماید.

در فرمول مالی استفاده شده در فصل 10: توابع مالی بیشتری از اکسل، از آرگومان آخر 1 برای مشخص‌کردن جریان مالی آخر ماه و صفر برای مشخص‌کردن جریان مالی ابتدای ماه استفاده کرده‌ایم. اکسل عبارت True را برابر با عدد 1 و عبارت False را برابر با عدد صفر تشخیص می‌دهد. کاربرگی تنظیم کنید که کاربری بتواند عدد ماه‌های یک وام، اصل مبلغ و نرخ بهره سالیانه را وارد کند تا بتواند پرداخت ماهیانه را به دست آورد. سپس از چک باکسی استفاده کنید تا انتخاب کنید آیا پرداخت‌ها در ابتدای ماه هستند یا در انتهای ماه.

با استفاده از داده‌های مسئله 15 در فصل 22 : تابع OFFSET دکمه چرخشی ای ایجاد کنید که با آن بتوان چند ماه آخر فروش را به‌صورت نمودار نمایش داد. (محدوده آن از سه تا هشت ماه می‌باشد)

فایلی به نام Suppliers.xlsx حاوی قیمت واحدهایی که به هریک از تأمین‌کنندگان می‌پردازید و همچنین واحدهای خریداری شده می‌باشد. یک لیست باکس تهیه کنید که به کمک آن بتوانید با انتخاب تأمین‌کننده، قیمت واحد و تعداد واحدهای خریداری شده را به شما نشان دهد.

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

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

loader

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