عیدی

کاربرد توابع Cells و Range در اکسل – به زبان ساده

۲۲ مرداد ۱۳۹۷


تعداد بازدید ها:
۸

اکسل یک نرم‌افزار بسیار قدرتمند است. اگر از آن دسته کاربرانی باشید که به طور روزانه با آن سروکار دارند قطعا با بسیاری از فرمول‌ها و ویژگی‌های آن آشنا هستید. در این مطلب می‌خواهیم راجع به توابع «Cells» و «Range» صبحت کنیم. کار با این توابع می‌تواند عملکرد شما در اکسل را به سطح کاملا جدیدی برساند.

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

تابع Cells

تابع‌های «Cells» و «Range» به اسکریپت VBA می‌گویند که دقیقا عملیات مورد نظر باید در کجای صفحه گسترده انجام شود. تفاوت اصلی بین این دو در بخشی است که به آن اشاره می‌کنند.

«Cells» معمولا در هر زمان به یک سلول اشاره دارد، در حالی که تابع «Range» محدوده‌ای از سلول‌ها را هدف قرار می‌دهد. قالب کلی نوشتن این تابع به شکل «(ستون ,سطر)Cells» است. در ادامه مثال‌هایی از این تابع را مشاهده می‌کنید.

کد زیر شامل تمامی سلول‌های صفحه گسترده‌ی ما می‌شود. در این مثال تابع «Cells» تنها به یک سلول اشاره ندارد:


کد زیر به سومین سلول از سمت چپ و در اولین سطر اشاره دارد، یعنی سلول C1:


کد زیر به سلول D15 اشاره می‌کند:


در صورت علاقه می‌توانید به صورت «(“D” ,‏۱۵)Cells» نیز به سلول D15 اشاره کنید. به عبارتی دیگر برای اشاره به ستون‌ها استفاده از حروف آن‌ها نیز مانعی ندارد.

برای اشاره به یک سلول مشخص می‌توانید از ترکیب‌های مختلفی برای شماره‌ی سطر و ستون استفاده کنید. اگر با استفاده از حلقه‌ها در حال چرخش بین چندین سلول و انجام محاسباتی بر روی آن‌ها باشید، این انعطاف پذیری را به خوبی حس خواهید کرد. در ادامه به جزئیات بیشتری از این موضوع خواهیم پرداخت.

تابع Range

در بسیاری از حالات، تابع Range بسیار قدرتمندتر از تابع Cells است، چراکه علاوه بر امکان انتخاب یک سلول، امکان اشاره به مجموعه‌ای از آن‌ها را نیز به شما می‌دهد. بهتر است از این تابع در حلقه‌ها استفاده نکنید، چراکه مشخص کردن محدوده به صورت اعداد صورت نمی‌گیرد و باید از نام سلول استفاده نمایید (روش‌هایی برای دور زدن این مساله وجود دارد که در ادامه یک مثال از آن را به شما نشان خواهیم داد).

قالب کلی استفاده از این تابع به شکل «Range(Cell#1, Cell#2)» است. هر سلول با نام و شماره‌ی آن مشخص می‌شود. در ادامه به چند مثال از این تابع می‌پردازیم.

در قطعه کد زیر، اشاره به سلول «A5» صورت گرفته است:


در کد زیر محدوده‌ای از سلول‌ها را در نظر گرفته‌ایم که از خانه‌ی «A1» شروع شده و تا «E20» ادامه دارد:


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


در کد بالا به همان محدوده‌ای اشاره شده است که تابع «Range(“A1:E20“)» اشاره می‌کند. ویژگی این کد این است که امکان استفاده از مقادیر پویا را در بین حلقه‌ها فراهم می‌کند.

حال که با قالب کلی توابع Cells و Range آشنا شده‌اید، کمی بیشتر وارد موضوع شده و به نحوه‌ی نوشتن توابع خلاقانه با استفاده از این دو تابع در VBA می‌پردازیم.

پردازش داده‌ها با استفاده از تابع Cells

تابع Cells بیشتر در زمانی کاربرد دارد که بخواهید فرمول‌های پیچیده‌ای را بر روی مجموعه‌ای از سلول‌ها اعمال کنید. محدوده‌ی سلول‌هایتان می‌تواند در کاربرگ‌های متفاوتی قرار داشته باشد، در هر صورت این تابع کار شما را راه می‌اندازد.

یک مثال ساده را بررسی می‌کنیم. فرض کنید مدیر یک تیم فروش ۱۱ نفره هستید و می‌خواهید هر ماه عملکرد هر شخص را به دست آورید.

یک کاربرگ با نام «Sheet1» دارید که آمار فروش هر فرد را به دست می‌آورد و به شکل زیر است:

Sheet1

در «Sheet2» نیز بازخوردهای ۳۰ روز اخیر مشتریان خود از آن‌ها را نگه‌داری می‌کنید:

Sheet2

اگر بخواهید در کاربرگ اول پاداش هر کسی را بر اساس مقادیر هر دو کاربرگ بررسی کنید، می‌توانید از راه‌های زیادی استفاده نمایید. یکی از روش‌ها این است که یک فرمول در کاربرگ اول بنویسید که با استفاده از داده‌های هر دو کاربرگ مقدار پاداش را محاسبه کرده و نمایش می‌دهد. مشکلی در این روش وجود ندارد.

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

استفاده از تابع Cells

اگر تا به حال در اکسل از VBA استفاده نکرده باشید، باید ابتدا منوی «Developer» را فعال کنید. برای این کار از زبانه‌ی «File» گزینه‌ی «Options» را انتخاب کرده و به بخش «Customize Ribbon» بروید. از پنل سمت چپ گزینه‌ی «Developer» را انتخاب کرده و بر روی «Add» کلیک کنید تا وارد پنل سمت راست شود، سپس آن را فعال نمایید.

Developer Mode

اینک با کلیک کردن بر روی گزینه‌ی «OK» یک زبانه‌ی «Developer» در کنار سایر زبانه‌های اکسل اضافه خواهد شد. همچنین اگر بار اول است که می‌خواهید از VBA استفاده کنید، شاید استفاده از «آموزش برنامه نویسی VBA در اکسل» برایتان مناسب باشد.

با استفاده از گزینه‌ی «Insert» که در این زبانه وجود دارد، یک «Command Button» به صفحه گسترده‌ی خود اضافه کنید یا با کلیک کردن بر روی گزینه‌ی «View Code» شروع به کدنویسی نمایید.

Developer Options

در این مثال می‌خواهیم اسکریپت را به گونه‌ای بنویسیم که در زمان باز شدن کاربرگ اجرا شود. برای این کار از زبانه‌ی «Developer» بر روی گزینه‌ی «View Code» کلیک کرده و تابع زیر را در پنجره‌ی کدنویسی آن وارد کنید.


کد شما باید شبیه به تصویر زیر باشد:

تابع

حال آماده‌ی نوشتن کد خود هستید. با استفاده از یک حلقه‌ی ساده می‌توانید در بین تمام ۱۱ کارمند گشته و با استفاده از تابع «Cells» سه متغیری که برای محاسبات ما مهم است را به دست آورید.

اگر به یاد داشته باشید، تابع Cells برای محاسبات خود نیاز به مقدار سطر و ستون داشت تا هر سلول را پیدا کند. متغیر x را برابر با سطر فعلی قرار می‌دهیم. دقت کنید که شماره‌ی هر سطر در واقع به یک کارمند اشاره دارد، در نتیجه باید از ۱ تا ۱۱ بشمریم. شماره‌ی ستون ۲ برای تعداد فروش، ستون ۳ برای ارزش فروش، و ستون ۲ از کاربرگ دوم نیز برای امتیاز بازخورد هر کارمند است.

محاسبات نهایی برای به دست آوردن درصد پاداش است که می‌تواند تا %۱۰۰ پیش برود. پایه‌ی کار را بر این در نظر می‌گیریم که ۵۰ فروش به ارزش ۵۰,۰۰۰ دلار و امتیاز بازخورد ۱۰ حالت ایده‌آل هستند.

  • ۰.۴ × (۵۰ / تعداد فروش)
  • ۰.۵ × (۵۰۰۰۰ / ارزش فروش)
  • ۰.۱ × (۱۰ / امتیاز بازخوردها)

این روش ساده می‌تواند مقدار پاداش هر کارمند را محاسبه کند. هر شخصی که ۵۰ فروش به ارزش ۵۰,۰۰۰ دلار داشته باشد و امتیاز ۱۰ را دریافت کرده باشد، حداکثر مبلغ پاداش را برای ماه به دست می‌آورد. با این حال هر چیزی که کمتر از مقدار ایده‌آل باشد مبلغ پاداش را کاهش می‌دهد. هر چیزی بالاتر از حد باشد پاداش را افزایش می‌دهد.

حال منطق بالا را به صورت قطعه کد می‌نویسیم. مشاهده می‌کنید که به چه سادگی می‌توان آن را پیاده‌سازی کرد:


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

نتیجه‌ی کد

اگر می‌خواستید در ستون «Bonus» به جای درصد، مبلغ پاداش را نمایش دهید، به سادگی می‌توانستید آن را در حداکثر مقدار پاداش ضرب کنید. حتی می‌توانستید مقدار آن را در یک سلول دیگر در کاربرگی مجزا نوشته و در کد خود به آن اشاره کنید. با این کار در آینده راحت‌تر می‌توانید مقدار آن را تغییر دهید و نیاز به ویرایش کد خود نخواهید داشت.

با استفاده از تابع Cells می‌توانید منطق‌های خلاقانه‌ای را بر روی داده‌های سلول‌های زیادی اعمال کرده و با به دست آوردن هر سلول، فرمول‌های پیچیده‌ای را بر روی آن اعمال کنید.

به کمک این تابع می‌توانید کارهای زیادی را بر روی سلول‌های خود انجام دهید، برای مثال می‌توانید سلول را خالی کرده یا فونت آن را تغییر دهید.

قالب‌بندی سلول‌ها با استفاده از تابع Range

اگر می‌خواهید در لحظه بین سلول‌های زیادی بچرخید، تابع Cells به عالی‌ترین نحو این کار را برای شما میسر می‌سازد. ولی اگر می‌خواهید فورا عملی را بر روی مجموعه‌ای از سلول‌ها انجام دهید، تابع «Range» موثرترین روش است.

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

مجموع ارزش فروش

برای مثال فرض کنید که می‌خواهیم اگر مجموع ارزش فروش تمام کارمندان از ۴۰۰,۰۰۰ دلار عبور کرد، تمام سلول‌های ستون پاداش را به رنگ سبز در بیاوریم تا مشخص شود که کل اعضای تیم یک پاداش اضافی به دست آورده‌اند. می‌خواهیم این کار را با یک دستور شرطی «IF» انجام دهیم.


اگر در هنگام اجرای این دستور، مقدار سلول مشخص شده از حد انتظار بیشتر باشد، تمام سلول‌های داخل محدوده‌ی مشخص شده به رنگ سبز در خواهند آمد.

این تنها یک مثال ساده از کارهای بسیاری است که می‌توانید با استفاده از تابع Range بر روی سلول‌های مختلف انجام دهید. برخی دیگر از کارهایی که می‌توانید انجام دهید شامل موارد زیر می‌شوند:

  • برجسته کردن گروهی از سلول‌ها
  • بررسی املای صحیح متون داخل مجموعه‌ای از سلول‌ها
  • حذف، کپی یا بریدن محتوای سلول‌ها
  • جست‌وجو در مجموعه‌ای از سلول‌ها با استفاده از متد «Find»

اگر این مطلب برای‌تان کاربردی بوده است، آموزش‌هایی که در ادامه آمده‌اند نیز به شما پیشنهاد می‌شوند:

^^

آیا این مطلب برای شما مفید بود؟