درک عملکردهای اکسل در مقابل محدوده در VBA

استفاده از توابع Range و Cells در اکسل می تواند بسیار گیج کننده باشد. در اینجا نحوه استفاده از آنها به روش هایی که احتمالاً هرگز تصور نمی کردید، با کمک VBA آورده شده است

اکسل قدرتمند است. اگر زیاد از آن استفاده می‌کنید، احتمالاً ترفندهای زیادی را با استفاده از فرمول‌ها یا قالب‌بندی خودکار می‌دانید، اما با استفاده از تابع Ranges و Cells در VBA، می‌توانید تجزیه و تحلیل اکسل خود را به سطح جدیدی ارتقا دهید.

مشکل استفاده از سلول‌ها و توابع محدوده در ماکروهای VBA این است که در سطوح پیشرفته، اکثر مردم در درک نحوه عملکرد این توابع مشکل دارند. استفاده از آنها می تواند بسیار گیج کننده باشد. در اینجا نحوه استفاده از آنها به روشی که احتمالاً هرگز تصور نمی کردید آورده شده است.

عملکرد سلول های ویژوال بیسیک

توابع Cells و Range به شما این امکان را می دهند که به اسکریپت VBA خود بگویید دقیقاً کجای کاربرگ خود را می خواهید بدست آورید یا داده ها را قرار دهید. تفاوت اصلی بین این دو سلول در چیزی است که آنها به آنها اشاره می کنند.

عملکرد سلول های VBA معمولاً به یک سلول واحد در یک زمان اشاره می کند، در حالی که Range به یک گروه از سلول ها در یک زمان اشاره می کند.

فرمت عملکرد سلول ها در اینجا آمده است:

 Cells(row, column)

این به هر سلول در کل صفحه اشاره می کند. این یکی از نمونه‌هایی است که در آن عملکرد سلول‌های ماکرو VBA به یک سلول منفرد اشاره نمی‌کند:

 Worksheets("Sheet1").Cells

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

 Worksheets("Sheet1").Cells(3)

بیایید با ارجاع سلول D15 با استفاده از عملکرد سلول های VBA کمی ماجراجو باشیم:

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

اگر می خواهید، می توانید سلول D15 را با کد VBA تابع سلول زیر ارجاع دهید:

 Cells(15,"D")"

— شما مجاز به استفاده از حرف ستون هستید.

با استفاده از تابع سلول VBA، انعطاف‌پذیری زیادی وجود دارد، زیرا می‌توانید با استفاده از یک عدد برای ستون و سلول، به سلولی ارجاع دهید، به خصوص با اسکریپت‌هایی که می‌توانند از میان سلول‌های زیادی حلقه بزنند (و محاسبات را روی آنها انجام دهند) خیلی سریع.

تابع برد

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

فرمت این تابع به صورت زیر است:

 Range(Cell #1,Cell #2)

هر سلول را می توان با یک عدد حرف مشخص کرد. بیایید به چند نمونه نگاه کنیم.

مطلب مرتبط: 10 توابع پیشرفته اکسل برای محاسبات دقیق

کد زیر یک تابع محدوده است که به سلول A5 ارجاع می دهد:

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

در اینجا، تابع محدوده به تمام سلول های بین A1 تا E20 ارجاع می دهد:

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

همانطور که در بالا ذکر شد، لازم نیست از تخصیص سلول های اعداد با حروف استفاده کنید. در واقع می توانید از دو تابع سلول VBA در داخل یک تابع Range برای شناسایی یک محدوده در صفحه استفاده کنید، مانند این:

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

این کد به همان محدوده ای اشاره می کند که تابع Range (“A1:E20”) انجام می دهد. ارزش استفاده از آن این است که به شما امکان می دهد کدی بنویسید که به صورت پویا با محدوده ها با استفاده از حلقه ها کار می کند.

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

پردازش داده ها با عملکرد سلول ها

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

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

ممکن است Sheet1 داشته باشید که تعداد فروش و حجم فروش آنها را ردیابی می کند.

در Sheet2، داده‌هایی برای پیگیری رتبه بازخورد آنها در 30 روز گذشته از مشتریان شرکت خود دارید.

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

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

پس چرا محاسبات ستون جایزه را در همان اسکریپت در آن زمان آغاز نکنیم؟

عملکرد سلول های ماکرو اکسل در عمل

اگر قبلاً هیچ کد VBA در اکسل ننوشته اید، باید آیتم منوی Developer را فعال کنید. برای این کار به File > Options بروید. روی Customize Ribbon کلیک کنید. در نهایت، Developer را از پنجره سمت چپ انتخاب کنید، آن را به صفحه سمت راست اضافه کنید و مطمئن شوید که چک باکس انتخاب شده است.

مطلب مرتبط: نحوه وارد کردن داده ها در اکسل از یک تصویر

حالا وقتی OK را بزنید و به صفحه اصلی برگردید، گزینه Developer menu را مشاهده خواهید کرد.

می توانید از منوی Insert برای درج یک دکمه دستوری استفاده کنید یا فقط روی View Code کلیک کنید تا کدنویسی شروع شود.

در این مثال، اسکریپت را طوری تنظیم می کنید که هر بار که کتاب کار باز می شود اجرا شود. برای انجام این کار، کافیست از منوی توسعه دهنده روی View Code کلیک کنید و تابع جدید زیر را در پنجره کد قرار دهید.

 Private Sub Workbook_Open() End Sub

پنجره کد شما چیزی شبیه به این خواهد بود.

اکنون آماده نوشتن کد برای انجام محاسبات هستید. با استفاده از یک حلقه، می‌توانید از تمام ۱۱ کارمند عبور کنید و با عملکرد سلول‌های Excel VBA، سه متغیر مورد نیاز برای محاسبه را وارد کنید.

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

محاسبه نهایی از درصدهای زیر استفاده می کند تا 100 درصد کل امتیاز جایزه را جمع کند. این بر اساس تعداد فروش ایده آل 50، حجم فروش 50000 دلار و امتیاز بازخورد 10 است.

(تعداد فروش/50) x 0.4
(حجم فروش/50000) x 0.5
(امتیاز بازخورد/10) x 0.1

(Sales Count/50) x 0.4

(Sales Volume/50,000) x 0.5

(Feedback Score/10) x 0.1

این رویکرد ساده به کارکنان فروش پاداش وزنی می دهد. برای شمارش 50، حجم 50000 دلار و امتیاز 10 — حداکثر پاداش ماه را دریافت می کنند. با این حال، هر چیزی که در هر فاکتوری بی نقص باشد، پاداش را کاهش می دهد. هر چیزی بهتر از ایده آل، پاداش را افزایش می دهد.

حال بیایید ببینیم که چگونه می توان تمام این منطق را در یک اسکریپت بسیار ساده و کوتاه VBA به دست آورد:

 Private Sub Workbook_Open()For x = 2 To 12Worksheets("Sheet1").Cells(x, 4) = (Worksheets("Sheet1").Cells(x, 2).Value / 50) * 0.4 _ + (Worksheets("Sheet1").Cells(x, 3).Value / 50000) * 0.5 _ + (Worksheets("Sheet2").Cells(x, 2).Value / 10) * 0.1 _Next xEnd Sub

خروجی این اسکریپت به این صورت خواهد بود.

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

مطلب مرتبط: 9 سؤال متداول در مصاحبه با برنامه نویس وب و نحوه پاسخ به آنها

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

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

برای کاوش در هر کاری که می توانید بیشتر انجام دهید، صفحه Microsoft MSDN را برای شی Cells بررسی کنید.

قالب بندی سلول ها با تابع محدوده

برای حلقه زدن چندین سلول در یک زمان، عملکرد سلول ها عالی است. اما اگر می خواهید چیزی را به یکباره روی طیف وسیعی از سلول ها اعمال کنید، تابع Range بسیار کارآمدتر است. در مقایسه مداوم محدوده VBA در مقابل سلول ها، چیزهای زیادی برای انتظار وجود دارد.

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

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

بیایید نگاهی بیندازیم که چگونه می توانید این کار را با دستور IF انجام دهید.

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

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

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

یک طرح کلی در اطراف گروه اعمال کنید
املای متن را در محدوده ای از سلول ها بررسی کنید
سلول ها را پاک، کپی یا برش دهید
در یک محدوده با روش Find جستجو کنید

حتماً صفحه Microsoft MSDN را برای شی Range بخوانید تا همه احتمالات را ببینید.

اکسل را به سطح بعدی ببرید

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