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

برنامه Excel متعلق به شرکت مایکروسافت، شاید هیجان انگیزترین برنامه ای نباشد که یک معمار از آن استفاده می کند، اما برای محاسبات و برنامه ریزی ابزاری دقیق و بسیار کارآمد است. استفاده از اکسل مزیت های زیادی به همراه دارد که باعث می شود محاسبات سرعت بیشتری داشته باشند و بتوان از اطلاعات موجود در آن به طرق مختلف استفاده کرد. در این مقاله سعی داریم ۱۲ فرمول کاربردی و مهم این نرم افزار را معرفی کنیم؛ اما قبل از آن مقدمه ای راجع به این برنامه در اختیار شما قرار خواهیم داد تا با آن بیشتر آشنا شوید.

اکسل؛ چیزی بیش از یک جدول الکترونیکی است. بسیاری از کسانی که تا به حال اکسل را دیده اند یا با آن کار کرده اند، نمی دانند که این برنامه فقط برای درج اطلاعات نیست. اکسل قابلیت فرمول و برنامه نویسی دارد و می تواند اطلاعات دریافتی را به اشکال مختلف نمایش دهد. قابلیت های اکسل در حد و حدودی است که می توان از آن به تنهایی به عنوان یک برنامه حسابداری، فروش، متره، انبارداری و … استفاده نمود.

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

قبل از نوشتن فرمول

اکسل از سلول های زیادی تشکیل شده است که هر یک از این سلول ها می تواند پذیرای متن، عدد و یا فرمول باشد. برای نوشتن متن یا عدد، براحتی می توان روی سلول کلیک کرده و با زدن دکمه های کیبرد، سلول را از اطلاعات پرکرده و در انتها کلید Enter را برای ثبت فشرد. اما برای نوشتن فرمول، ۲ شرط خاص وجود دارد : ۱- استفاده از علامت مساوی (=) در ابتدای محتوا    ۲- تایپ به زبان انگلیسی. برای مثال به تصویر زیر توجه کنید که به محض زدن دکمه مساوی به عنوان اولین کاراکتر سلول، چگونه ظاهر آن عوض می شود.

البته برای درج فرمول های اکسل، نیازی به ثبت دستی یا حفظ کردن دستورها نیست چون خود برنامه، کامل ترین راهنمای آن است. کافیست به تب Formulas در بالای صفحه مراجعه کنید و دکمه Insert Function را بزنید تا در اقیانوس فرمول ها غرق شوید! انتخاب نحوه فرمول نویسی با شماست و یک سلیقه شخصی. اما به مرور زمان که بیشتر با اکسل آشنا شوید، به نوشتن دستی عادت خواهید کرد. هر فرمول اکسل از دو بخش تشکیل می شود : ۱- تابع    ۲- شروط.  تابع در واقع نام فرمول است و شروط، اطلاعات و شروط مورد نیازی هستند که برای هر تابع، متفاوت هستند. در ادامه با این دو بخش بیشتر آشنا خواهید شد.

فرمول اول : SUM

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

=SUM(A1:A30)

سلولی که فرمول بالا در آن تایپ شده باشد، جمع عدد سلول های بین A1 و A30 را نمایش خواهد داد.

فرمول دوم :‌ IF

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

=IF(A1>A2;”Bozorg”;”Kuchik”)

سلولی که فرمول بالا در آن تایپ شده باشد، مقدار سلول A1 و A2 را با هم مقایسه می کند. این شرط اول است. در صورتی که مقدار A1 بزرگتر از A2 باشد، شرط دوم که عبارت Bozorg است را در سلول نمایش می دهد. اگر شرط اول برقرار نباشد، شرط سوم که عبارت Kuchik است نمایش داده می شود.

فرمول سوم :‌ SUMIF ، SUMIFS

فرمول های SUMIF و SUMIFS دقیقا مانند فرمول SUM هستند، با این تفاوت که جمع را بصورت شرطی اجرا می کنند و همه سلول ها را با هم جمع نمی زنند. فرمول SUMIF برای اجرای فقط یک شرط و فرمول SUMIFS برای اجرای بیش از یک شرط استفاده می شوند. برای استفاده از این فرمول ها مانند نمونه ذکر شده عمل کنید :

=SUMIF(A1:A20;”Factori”;B1:B20)

سلولی که فرمول بالا در آن تایپ شده باشد، جمع تمامی سلول هایی از ستون B (ردیف ۱ تا ۲۰) را نمایش می دهد که ردیف همتای آن ها در ستون A، برابر عبارت Factori باشد.

=SUMIFS(B1:B20;A1:A20;”Factori”;C1:C20;”<>”&”Proje”)

سلولی که فرمول بالا در آن تایپ شده باشد، جمع تمامی سلول هایی از ستون B (ردیف ۱ تا ۲۰) را نمایش می دهد که ردیف همتای آن ها در ستون A، برابر عبارت Factori باشد و همچنین ردیف همتای آن ها در ستون C، با مقدار Proje برابر نباشد.

فرمول چهارم : COUNT ، COUNTA ، COUNTBLANK

فرمول های COUNT ، COUNTA و COUNTBLANK ، همگی تعداد سلول ها را شمارش می کنند. COUNT به صورت عادی سلول ها را می شمارد. COUNTA سلول هایی را می شمارد که خالی نیستند. COUNTBLANK سلول هایی را می شمارد که خالی هستند. برای استفاده از این فرمول ها مانند نمونه ذکر شده عمل کنید :

=COUNT(A1:A20)

سلولی که فرمول بالا در آن تایپ شده باشد، تعداد سلول های بین سلول A1 و A20 را نمایش می دهد.

=COUNTA(A1:A20)

سلولی که فرمول بالا در آن تایپ شده باشد، تعداد سلول های غیرخالی بین سلول A1 و A20 را نمایش می دهد.

=COUNTBLANK(A1:A20)

سلولی که فرمول بالا در آن تایپ شده باشد، تعداد سلول های خالی بین سلول A1 و A20 را نمایش می دهد.

فرمول پنجم : COUNTIF و COUNTIFS

فرمول های COUNTIF و COUNTIFS همان کاری را انجام می دهند که فرمول COUNT انجام می داد، یعنی شمارش تعداد سلول ها. اما فرق آنها با هم این است که COUNTIF و COUNTIFS، شمارش را بصورت شرطی انجام می دهد. فرمول COUNTIF برای اجرای فقط یک شرط و فرمول COUNTIFS برای اجرای بیش از چند شرط استفاده می شوند. برای استفاده از این فرمول ها مانند نمونه ذکر شده عمل کنید :

=COUNTIF(A1:A20;”<100″)

سلولی که فرمول بالا در آن تایپ شده باشد، تعداد سلول های بین سلول A1 و A20 را نمایش می دهد که مقدار آن ها کمتر از عدد ۱۰۰ باشد.

=COUNTIFS(A1:A20;B1:B20;”<100″;C1:C20;”Base”)

سلولی که فرمول بالا در آن تایپ شده باشد، تعداد سلول های بین سلول A1 و A20 را نمایش می دهد که مقدار ردیف مشابه آن ها در ستون B کمتر از عدد ۱۰۰ باشد و مقدار ردیف مشابه آن ها در ستون C برابر با عبارت Base باشد.

فرمول ششم : AVERAGE

فرمول AVERAGE مقدار چندین سلول را بصورت معدل به شما نمایش می دهد. برای استفاده از این فرمول مانند نمونه عمل کنید :

=AVERAGE(A1:A20)

سلولی که فرمول بالا در آن تایپ شده باشد، معدل سلول های بین A1 و A20 را نمایش می دهد.

فرمول هفتم : MIN

فرمول MIN کوچکترین عضو یک مجموعه را معرفی می کند. معمولا در مجموعه اطلاعات بزرگ، به کمک این فرمول می توان به راحتی کوچکترین عضو (به عنوان مثال مساحت یا طول) را پیدا کرد. برای استفاده از این فرمول مانند نمونه عمل کنید :

=MIN(A1:A20)

سلولی که فرمول بالا در آن تایپ شده باشد، کوچکنرین عضو سلول های بین A1 و A20 را نمایش می دهد.

فرمول هشتم : MAX

فرمول MAX بزرگترین عضو یک مجموعه را معرفی می کند. معمولا در مجموعه اطلاعات بزرگ، به کمک این فرمول می توان به راحتی بزرگترین عضو (به عنوان مثال مساحت یا طول) را پیدا کرد. برای استفاده از این فرمول مانند نمونه عمل کنید :

=MAX(A1:A20)

سلولی که فرمول بالا در آن تایپ شده باشد، بزرگترین عضو سلول های بین A1 و A20 را نمایش می دهد.

فرمول نهم : VLOOKUP

فرمول VLOOKUP برای یافتن مقادیر عددی یا متنی بصورت شرطی می باشد و یکی از پرکاربردترین فرمول های برنامه اکسل است. برای استفاده از این فرمول چهار شرط باید داخل فرمول نوشته شود. شرط اول چیزی است که به دنبال آن می گردید. شرط دوم محلی است که برنامه باید در آن دنبال شرط اول بگردد. شرط سوم شماره ستونی است که می خواهید بعنوان نتیجه برگردد. شرط چهارم هم جستجوی دقیق است که می توان آن را ۰ یا ۱ قرار داد. جستجوی دقیق به این معنی است که عبارت یافت شده باید دقیقا برابر عبارت مورد نظر شما باشد یا خیر. مقدار ۰ به معنی دقیقا برابر و عبارت ۱ به معنی حدودا برابر است. برای استفاده از این فرمول مانند نمونه عمل کنید :

=VLOOKUP(A2;C:D;2;0)

سلولی که فرمول بالا در آن تایپ شده باشد، مقدار سلول A2 را دریافت می کند و در محدوده سلول های C تا D به دنبال آن می گردد. در صورتی که عبارت داخل سلول A2 در محدوده مذکور پیدا شد (جستجو بصورت عبارت دقیق است) هم ردیف آن سلول و ۲ ستون جلوتر را نمایش می دهد.

فرمول دهم : ROUND ، ROUNDUP ، ROUNDDOWN

فرمول های ROUND ، ROUNDUP و ROUNDDOWN همگی برای رُند کردن اعداد اعشاری به کار می روند. ROUND براساس استاندارد عدد را رُند می کند. ROUNDUP عدد را رو به بالا رُند می کند. ROUNDDOWN عدد را رو به پایین رُند می کند. برای هرکدام از این فرمول ها دو شرط وجود دارد. شرط اول مقار عدد یا سلول حاوی عدد است. شرط دوم تعداد اعشاری که می خواهید رُند شود می باشد. برای استفاده از این فرمول مانند نمونه عمل کنید :

=ROUND(7.89;1)

سلولی که فرمول بالا در آن تایپ شده باشد، مقدار ۷٫۹ را نمایش می دهد.

=ROUNDUP(7.23;0)

سلولی که فرمول بالا در آن تایپ شده باشد، مقدار ۸ را نمایش می دهد.

=ROUNDDOWN(8.85;1)

سلولی که فرمول بالا در آن تایپ شده باشد، مقدار ۸٫۸ را نمایش می دهد.

فرمول یازدهم : FLOOR و CEILING

فرمول های FLOOR و CEILING همانند ROUND هستند با این تفاوت که شرط دوم آن ها بصورت ضریب عدد را رُند می کند. این فرمول ها برای رُند کردن اطلاعات مالی بیشتر به کار می روند. برای استفاده از این فرمول مانند نمونه عمل کنید :

=FLOOR(122556;10)

سلولی که فرمول بالا در آن تایپ شده باشد، مقدار ۱۲۲۵۵۰ را نمایش می دهد.

=CEILING(122556;10)

سلولی که فرمول بالا در آن تایپ شده باشد، مقدار ۱۲۲۵۶۰ را نمایش می دهد.

فرمول دوازدهم : CONCATENATE

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

=FLOOR(A2;B2;C8)

سلولی که فرمول بالا در آن تایپ شده باشد، مقدار به هم پیوسته مقادیر سلول های A2 ، B2 و C8 را نمایش می دهد.

نکاتی راجع به فرمول ها

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

1. آدرس دهی سلول ها در اکسل به دو شکل است : تقریبی و مطلق. آدرس های تقریبی (مثل A2) وابسته به مکان نیستند و در صورتی که سلول های اطراف آن را حذف کنید، به صورت خودکار و با توجه به تغییرات ایجاد شده، عوض می شوند. اما سلول هایی که آدرس دهی مطلق دارند (مثل ۲$A$) به این شکل نیستند و با ایجاد تغییرات در صفحه، به همان شکل می مانند. علامت $ پشت ردیف یا ستون، نشانه مطلق یا تقریبی بودن آن است.
2. آدرس سلول ها می تواند متعلق به شیت یا حتی فایل دیگر باشد. برای مثال اگر یک فایل دارای دو شیت به نام های Sheet1 و Sheet2 باشد و فرمول نویسی در Sheet1 انجام پذیرد، آدرس یک سلول نمونه در Sheet2 به این صورت تایپ می شود : Sheet2!A2=
3. استفاده از عبارات دستی در فرمول ها (دستی یعنی محل سلول نباشد و عبارت دلخواه تایپی باشد) باید به همراه علامت ” باشد. یعنی اگر داخل فرمول می خواهید بنویسید MeemarBashi ، باید به اینصورت نوشته شود : “MeemarBashi”
4. نشانگرهای برنامه اکسل به این صورت است : (=) مساوی | (+) جمع | (-) تفریق | (*) ضرب | (/) تقسیم | (<>) نامساوی | (<=) بزرگتر مساوی | (>=) کوچکتر مساوی | (^) به توان
5. در صورتی که با ارورهایی مثل N/A# یا ERROR# مواجه شدید، بدانید که شروط فرمول را بصورت درست تایپ نکرده اید.

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

منبع : http://meemarbashi.com/