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

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

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

فیلم آموزش استفاده از توابع و فرمول نویسی در اکسل Excel در فرادرس

کلیک کنید

تابع Cells

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

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – مقدماتی در فرادرس

کلیک کنید

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

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

1Worksheets("Sheet1").Cells

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

1Worksheets("Sheet1").Cells(3)

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

1Worksheets("Sheet1").Cells(15,4)

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

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

تابع Range

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

فیلم آموزش برنامه نویسی وی بی ای در اکسل – VBA در Excel – تکمیلی در فرادرس

کلیک کنید

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

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

1Worksheets("Sheet1").Range("A5")

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

1Worksheets("Sheet1").Range("A1:E20")

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

1With Worksheets("Sheet1")
2 .Range(.Cells(1, 1), _ 
3 .Cells(20, 5))
4End With

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

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

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

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

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

فیلم آموزش اکسل – محاسبات ریاضی و ماتریسی با Microsoft Excel 2016 در فرادرس

کلیک کنید

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

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

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

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

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

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

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

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

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

1Private Sub Workbook_Open() 
2
3End Sub

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

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

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

محاسبات نهایی برای به دست آوردن درصد پاداش است که می‌تواند تا %100 پیش برود. پایه‌ی کار را بر این در نظر می‌گیریم که 50 فروش به ارزش 50,000 دلار و امتیاز بازخورد 10 حالت ایده‌آل هستند.

• 0.4 × (50 / تعداد فروش)
• 0.5 × (50000 / ارزش فروش)
• 0.1 × (10 / امتیاز بازخوردها)

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

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

1Private Sub Workbook_Open()
2For x = 2 To 12
3Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _
4 + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _
5 + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _
6Next x
7End Sub

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

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

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

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

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

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

فیلم آموزش کاربرد اکسل در حسابداری و مدیریت در فرادرس

کلیک کنید

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

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

1Private Sub Workbook_Open()
2If Worksheets("Sheet1").Cells(13, 3).Value > 400000 Then
3 ActiveSheet.Range("D2:D12").Interior.ColorIndex = 4
4End If
5End Sub

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

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

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

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

• آموزش‌های مجموعه نرم‌افزاری آفیس
• مجموعه آموزش‌های برنامه‌نویسی
• آموزش استفاده از توابع و فرمول نویسی در اکسل
• اکسل و سه فرمول شگفت‌انگیز آن که باید بدانید
• برنامه نویسی VBA در اکسل — آموزش مقدماتی

^^