محاسبه درصد تغییرات در اکسل با PivotTable — به زبان ساده

 

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

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

تبدیل متن به تاریخ در اکسل — به زبان ساده

تبدیل متن به تاریخ در اکسل :

احتمالاً یکی از رایج‌ترین خطاهایی که افراد تازه‌کار در زمان وارد کردن تاریخ در اکسل مرتکب می‌شوند این است که از کاراکتر نقطه (.) برای جداسازی روز، ماه و سال استفاده می‌کنند. اکسل این مقادیر را به عنوان تاریخ شناسایی نمی‌کند و آن را به صورت متنی ذخیره می‌سازد. با این حال، می‌توان این مشکل را با ابزار Find and Replace حل کرد. اکسل با جایگزینی علامت ممیز (/) به جای نقطه، به صورت خودکار مقادیر را به صورت تاریخ شناسایی می‌کند.

 

بدین منظور ابتدا ستون‌هایی را که می‌خواهید عملیات Find and Replace را روی آن‌ها اجرا کنید، انتخاب نمایید.

پنهان کردن خطاها در اکسل

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

پنهان کردن خطاها در اکسل با تابع IFERROR

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

در این مثال یک تابع VLOOKUP، مقدار خطای #N/A بازگشت داده است.

تابع Workbook و Workbooks

تابع Workbook و Workbooks :

در اکسل ما همیشه با یک فایل کلی و زیر مجموعه از آنها روبرو هستیم مثل ستون و سلول های آن یا ردیف و سلول های آن، فایل های اکسل نیز اینطور است، وقتی فایل های جدید از اکسل را ایجاد میکنیم با یک برنامه کلی اکسل که تمامی فایل های اکسل در آن گنجانده شده می باشند که به این فایل کلی Workbooks و به زیر مجموعه های آن workbook یا کاربرگ گفته می شود. به عنوان مثال چند فایل اکسل ایجاد کنید

آموزش برنامه نویسی VBA اکسل - عملگرها

عملگرهای مقایسه ای:

به کمک این عملگرها دو یا چند رشته متنی را میتوانیم با هم مقایسه کنیم.  این عملگر بیشتر در رابطه های شرطی به کار برده میشود. (در بحث شرطی ها کاربرد این عملگر را توضیح خواهم داد) که در اکثر زبان های برنامه نویسی یکسان می باشد.

عملگر = مساوی

عملگر < بزرگتر

عملگر > کوچکتر

عملگر =< بزرگتر یا مساوی

عملگر => کوچکتر یا مساوی

عملگر <> نامساوی

عملگرهای منطقی :

آموزش VBA اکسل - آدرس دهی

دستور Select :

دستور select  برای انتخاب یک یا مجموعه ای از سلول ها به کار میرود. مثال :

Range("B1").select

 

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

آدرس دهی ROW :

در این نوع آدرس دهی شماره ردیف رو میدهیم :

Rows(1).SELECT

Rows ("1").SELECT

 

آدرس دهی Column :

آموزش برنامه نویسی VBA اکسل - آدرس دهی سلول ها در vba

آدرس دهی سلول ها در vba

برای آدرس دهی از روش های زیر استفاده می شود :

– آدرس دهی Range

Sub Name()
Range("A1").Value = "MrCoder"
End Sub

حالا  کلید F5 را فشار دهید مشاهده میکنید که برنامه اجرا شده و با این فرمول مشخص کردیم که مفدار سلول A1 برابر MrCoder باشد. حالا با فرمول زیر مقدار دو سلول را مشخص میکنیم :(از این به بعد فقط کد ها را بدون Sub می گذارم :

Range("A1,D5").Value = "MrCoder"

 

مقدار دو سلول A1  و D5 برابر “MrCoder” می باشد. حالا برای آدرس دهی یک محدوده از فرمول زیر استفاده میکنیم :

Range("A1:D5").Value = "MrCoder"

 

مشاهده میکنید که سلول های A1 تا D5 مقدارشان برابر “MrCoder” شد. حالا برای آدرس دهی دو یا چند محدوده :

Range("A1:D5", "E5:G5").Value = "MrCoder"

 

فرمول های بالایی را ترکیب کردیم و برای آدرس دهی چند محدوده استفاده کردیم و سلول های A1 تا D5 و E5 تا G5 را آدرس دهی و مقدار دادیم.

 

– آدرس دهی CELLS

ویژوال بیسیک

ابتدا بر روی یک دکمه قرار می دهیم یا در یک تکس باکس کد زیر را می نویسم تا ویژوال ان را بخواند

Dim ras, sapi

ras = Text1.Text

Set sapi = CreateObject("sapi.spvoice")

sapi.Speak ras

Text1.SetFocus

VBA

ویژوال بیسیک یکی از اولین زبان های برنامه نویسی جهت تشکیل WYSIWYG  ( چیزی که می بینید همان چیزی است که بدست می آورید ) می باشد . ویژوال بیسیک به جهت محیط تصویری آن ، یک زبان لذت بخش می باشد . برنامه نویسی تحت ویندوز با ویژوال بیسیک اکثراً تنها به کشاندن و قرار دادن (Drag  &  Drop  ) اشیاء گرافیکی ، از جعبه ابزار ( Tool box ) که آن اشیاء در آن قرار دارند به روی صفحه کاری نیاز دارد . همانطور که شما در حال اضافه کردن اشیاء هستید برنامه تحت ویندوز در پیش چشم شما ظاهر می شود .

ویژوال بیسیک بیش از یک زبان برنامه نویسی می باشد . این زبان برنامه نویسی ، تمام اعمالی را که در اجـرای برنـامـه ویـژوال بیسیک رخ مـی دهند تشـکیـل می دهـد . با این حال ، ایـن زبـان به رابـط کاربـر ( user   Inter  face  )  یـک تمرکـز ثانویـه دارد . یـک بـرنامه تحـت وینـدوز ، درجـه ای بـالا از محـا وره ( Interaction )  با کاربر را ارائه می کند . این کار توسط عناصر گرافیکی که کاربر بر روی صفحه نمـایـش می بیند انجام می گردد .

میکروسافت اساس ویژوال بیسیک را بر مبنای “ زبان نویسی برای مبتدیان ”  که بیسیک نام دارد قرار داد . بیسیک بیش از 35 سال در گونه های مختلف موجود بوده است . طراحان اصلی زبان ، قصد ایجاد یک زبان برنامه نویسی را داشتند که مبتدیان بتوانند از آن استفاده کنند .

Visual Basic

انواع داده ای در Visual Basic

برای تعریف یک متغیر در ویژوال بیسیک  از عبارت Dim استفاده می شود. برای مثال چمله زیر یک متغیر جدید به نام myVar تعریف می کند:
Dim myVar
هنگامی که یک متغیر مانند فوق بدون در نظر گرفتن نوع آن تعریف شود، آن متغیر بعنوان یک شئ (Object) در نظر گرفته می شود. یک متغیر از نوع شئ در  می تواند شامل انواع داده از قبیل رشته، عدد یا انواع پیچیده تر مانند کلاس باشد.
اغلب نیازی نیست که از متغیرهای از نوع شئ در برنامه استفاده شود چرا که این متغیرها باعث کاهش کارآیی و سرعت برنامه می شود. هنگامی که از یک متغیر از نوع شئ استفاده شود، نوع مناسب آن متغیر در زمان اجرا تشخیص داده می شود و این باعث کندی می شود. بنابراین بهتر است که نوع داده را از ابتدا مشخص کنیم.
جهت مشخص نمودن نوع داده از کلمه کلیدی As استفاده می کنیم. بعنوان مثال یک متغیر از نوع رشته بصورت زیر تعریف می شود:
Dim myVar As String
مجموعه  انواع داده ای زیر را پشتیبانی می کند: