استفاده از اکسل در حسابداری و ساخت گزارشات

حسابداری با اکسل از موضوعاتی است که اکثر حسابداران پیگیر آن هستند. برخی با تلاش و زحمت به یادگیری حسابداری می پردازند و در برنامه ای بلند مدت برای خود موفقیت های خوبی رقم میزنند. برخی نیز زمان برای گذر از یک برنامه بلند مدت ندارند و تمایلشان به این مورد است که به سرعت به یادگیری حسابداری بپردازند.

حسابداری با اکسل

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

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

از طرفی برای تهیه گزارشات حسابداری با اکسل (مانند جمع بندی و تهیه صورت های مالی) استفاده از ابزارهای دیگر این نرم افزار، مانند فیلتر و تابع SUBTOTAL ضروری به نظر می رسند. همچنین به کارگیری «جدول محوری» (PivotTable) با توجه به تنوع گزارشاتی که تولید می کند، یک گزینه بسیار مطلوب برای تهیه صورت های مالی خواهد بود. همه این موارد در این متن مورد توجه قرار گرفته و در پایان، فایل مربوط به ایجاد یک دفتر روزنامه و گزارش گیری از آن، در قالب یک کارپوشه (Workbook) به صورت یک نرم افزار حسابداری با اکسل قابل دستیابی خواهد بود.

گام های مربوط به ساخت نرم افزار حسابداری با اکسل را براساس یک مثال ساده از لیست هزینه و درآمدها پی می گیریم.

گام صفر: تعریف اقلام دسته یا گروه هزینه/درآمد

برای انجام عملیات حسابداری با اکسل باید به معرفی حساب های دریافتنی/پرداختنی بپردازید. البته در اینجا گام صفر که مربوط به انجام این عمل است، یک مرحله اختیاری است. در این گام، گزینه های مربوط به دسته یا گروه درآمدی یا هزینه ها را مشخص می کنیم. هر چند تعریف اقلام مربوط به هزینه یا درآمد ضروری نیست، ولی انجام این کار باعث می شود که لیست دریافت و پرداخت های تهیه شده، دارای گزینه هایی مشابه بوده و گزارش گیری از آن ها ساده تر صورت گیرد. اگر گام صفر را اجرا نکنید باز هم یک جدول (کاربرگ) درآمد و هزینه خواهید داشت ولی در تهیه گزارش جدول محوری، با مشکل مواجه خواهید شد. تصویر ۱، به معرفی مقادیر «کاربرگ اطلاعات اولیه» اشاره دارد.

همانطور که دیدید، سطرهای این جدول، نشانگر مراکز هزینه یا درآمد است. بنابراین هر یک از اقلام این جدول را باید برای ستون دسته جدول هزینه/درآمد مشخص کرد. این کار را در گام اول و بوسیله «اعتباردهی داده ها» (Data Validation) انجام خواهیم داد. واضح است که برای هر قلم که مربوط به هزینه است، در ابتدا عبارت هزینه و برای اقلامی که براساس درآمد هستند، کلمه درآمد قرار گرفته است. به این ترتیب شاید در گزارش های دیگری که مربوط به هزینه یا درآمد است، جمع بندی براساس این کلمات نیز امکان پذیر شود. به این ترتیب تفکیک گزارش حسابداری با اکسل به شکل مطلوب صورت می گیرد.

گام اول: ایجاد کاربرگ هزینه و درآمد در اکسل

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

برای اطلاع از چگونگی ثبت و نمایش تاریخ شمسی، مطلب تاریخ شمسی در اکسل | راهنمای کاربردی را مطالعه کنید.

نکته: هر تعامل مالی (دریافت یا پرداخت) را یک «تراکنش» (Transaction) می نامند. بنابراین هر سطر از این جدول به یک تراکنش مالی ارتباط خواهد داشت.

ستون های تعریف شده در کاربرگ اکسل که در تصویر ۲ مشاهده کردید، در ادامه معرفی و نقش هر یک از تراکنش های مالی ذکر شده است.

ستون تاریخ: تاریخ انجام تراکنش در این ستون وارد می شود. برای وارد کردن تاریخ شمسی در این ستون، از شیوه فارسی سازی تاریخ در اکسل استفاده کرده ایم. در گزارشاتی که در آینده بوسیله این جدول تهیه خواهیم کرد، تاریخ شمسی نقش مهمی ایفا می کند. به این ترتیب سیستم حسابداری با اکسل را به صورت کاملا فارسی در اختیار خواهید داشت.
ستون شرح: شرح تراکنش در این قسمت نوشته می شود. ممکن است محتویات این سلول ها، متنی، عددی یا مخلوطی از محتویات متنی و عددی باشد. البته اگر بخواهید مثلا یک شماره چک یا مثلا شماره کارتی که به آن پرداختی انجام داده اید را از این بخش جدا کنید، اکسل، توابع و برنامه های مختلفی در اختیارتان قرار می دهد. این موضوع در نوشتار دیگری از مجله فرادرس با عنوان جدا کردن عدد از متن در اکسل — راهنمای کاربردی قابل مشاهده است.
ستون دسته یا گروه: سلول های این ستون به دسته بندی یا طبقه بندی هزینه یا درآمد اشاره دارند. می توانید گزینه های خاصی را برای این ستون در نظر بگیرید. ایجاد یک لیست سفارشی و معرفی آن به عنوان مقادیر مجاز برای ثبت در این سلول ها راه کار مناسبی خواهد بود. کافی است که از Data Validation برای انجام این کار کمک بگیرید. انجام این عمل در ادامه متن مورد توجه قرار گرفته است.
ستون هزینه: هر مقدار هزینه ای که در تاریخ های ثبت شده در ستون تاریخ داشته اید را در این قسمت وارد کنید. ممکن است برای این عددها، از واحد ریال یا تومان استفاده کنید. این را به خاطر داشته باشید که اگر مقادیر ستون هزینه را به ریال وارد کرده اید، درآمدها را هم به ریال وارد کنید تا باقی مانده بدست آمده، معنی دار باشد. ستون هزینه ممکن است از نوع خرید، سود قسط بانکی و … باشند. بنابراین گزینه مناسب را از ستون دسته برای هر هزینه انتخاب کنید.
ستون درآمد: رفتار این ستون نیز درست به مانند ستون هزینه است. برای هر یک از مقادیر این ستون در قسمت دسته، گروه مناسب درآمد را انتخاب کنید. برای مثال ممکن است درآمد مربوط به فروش، دریافت سود بانکی و … باشد.

اعتبار سنجی داده برای ستون دسته

همانطور که گفته شد، بهتر است، برای مقادیر ستون «دسته» از اقلامی اطلاعاتی استاندارد که در «کاربرگ اطلاعات اولیه» تنظیم شده، استفاده کنیم. به این منظور ستون دسته را انتخاب کرده و از برگه Data دستور Data Validation را از بخش Data Tools اجرا می کنیم.

تصویر ۳: اعتبار دهی به داده ها براساس جدول کاربرگ اطلاعات اولیه

با انجام این کار مقادیری که در ستون «دسته» قرار می گیرند حتما باید یکی از اقلام کاربرگ اطلاعات اولیه در سلول های B2 تا B13 باشند. به علامت دلار ($) که در این آدرس ها ثبت شده و آن ها را به صورت آدرس مطلق درآورده نیز توجه داشته باشید.

نکته: انتخاب گزینه Ignore blank باعث می شود که سطرهای خالی در کاربرگ اطلاعات اولیه در ناحیه گفته شده، در لیست گزینه های انتخابی ظاهر نشوند. این سطرهای خالی به این منظور در Data Validation به کار رفته اند تا اگر سطرهای دیگری به جدول اطلاعات اولیه اضافه شد، به گزینه های انتخابی در جدول «درآمد هزینه» اضافه شوند. همچنین با انتخاب گزینه In-cell dropdown، دکمه ای برای انتخاب اقلام مورد نظر برای سلول های مربوط به ستون «دسته» ظاهر شده که با کلیک روی آن می توانید لیست گزینه های مورد نظر را ظاهر و از بین آن ها انتخاب انجام دهید.

جمع بندی برای ستون هزینه و درآمد

از طرفی برای هر دو ستون درآمد و هزینه، احتیاج به یک جمع نیز داریم. بنابراین بهتر است در قسمتی از لیست، یک محل نیز برای جمع بندی و خلاصه کردن جدول بالا، در نظر بگیریم. به این ترتیب می توانیم در همین قسمت، تفاضل هزینه از درآمد را محاسبه کرده و به عنوان سلول مانده (سود/زیان) در نظر بگیریم.

برای جمع کردن یک ناحیه، معمولا از تابع SUM در اکسل استفاده می کنیم. بنابراین برای محاسبه جمع کل هزینه و درآمد، فرمولی به صورت زیر در سلول های E1 و F1 می نویسیم. توجه داشته باشید که هر چه بعد از علامت «:E1» یا «:F1» قرار گرفته را باید در سلول بنویسید.

E1: = sum(E4:E20)

F1: = sum(F4:F20)

مشخص است که آدرس های F20 و E20 نشانگر انتهای لیست هستند. ولی اگر می خواهید مجموع را برای سطرهای دیگر جدول «رآمد هزینه» محاسبه کنید باید سطرهای آخر جدول را در این مکان مشخص کنید. برای مثال اگر سطر آخر جدول تان در ردیف هزارم قرار دارد باید آخرین سلول را به صورت E1000 و F1000 در فرمول مشخص کنید.

حال کافی است برای محاسبه مانده دو سلول E1 و F1 را از یکدیگر کم کنیم. معمولا هزینه را از درآمد کم کرده تا مانده (سود / زیان) مشخص شود. این کار را در سلول H1 انجام خواهیم داد.

H1: = F1 – E1

به این ترتیب با اضافه کردن هر سطر به جدول اطلاعاتی هزینه/درآمد، مانده عملیات صورت گرفته (تراکنش ها) به راحتی به دست آمده و قابل مشاهده است. بنابراین همانطور که دیدید بهتر است که سلول مربوط به جمع بندی را در بالاترین سطر جدول اطلاعاتی درج کنیم. اگر از دستور ثابت کردن سطر در اکسل (Freeze) استفاده کنیم، می توانیم اسامی ستون ها و همینطور جمع ها را همیشه در کاربرگ مشاهده کرده و با افزایش تعداد سطرهای جدول، آن ها را گم نکنیم.

ثابت کردن سطرهای اولیه کاربرگ درآمد هزینه

حال فرض کنید که جمع کل هزینه ها را بوسیله تابع SUM در سلول E1 و F1 نوشته و همچنین مانده نیز در سلول H1 محاسبه شده است. واضح است که با وارد کردن سطرهای جدید به «کاربرگ درآمد هزینه»، صفحه اکسل به پایین حرکت کرده و دیگر امکان نمایش سطرهای اول جدول را نخواهد داشت. اگر می خواهید سطرهایی از بالایی کاربرگ را به صورت ثابت نمایش داده و در عین حال قادر به ورود داده به سطرهای پایین باشید، باید از دستور Freeze Pane استفاده کنید. برای ثابت کردن عنوان های جدول و همینطور سطر جمع، به صورت زیر عمل می کنیم.

روی سطر ۴ کاربرگ کلیک می کنیم. به این ترتیب کل سطر چهارم کاربرگ درآمد هزینه انتخاب می شود.
از برگه View گزینه Freeze panes را از بخش Window کلیک می کنیم.
با انتخاب دستور Freeze Panes از محل سطر انتخابی به بالای کاربرگ ثابت خواهد شد.

حال اگر کاربرگ را به سمت پایین، پیمایش (Scroll) کنید، همیشه سطرهای اول تا سوم کاربرگ دیده خواهند شد. توجه دارید که سطر انتخابی باید اولین سطری باشد که قرار است، سطرهای قبل از آن ثابت شوند.

گام دوم: فیلتر کردن سطرهای جدول

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

به این منظور مراحل زیر را طی خواهیم کرد.

سطر سوم از کاربرگ که شامل اسامی ستون های جدول است را انتخاب می کنیم.
از برگه Data، گزینه Filter را به صورت فعال در می آوریم.
با استفاده از دستگیره های فیلتر که روی ستون ها قرار است، هر قلم از ستون «دسته» را انتخاب و سطرهای مرتبط را نمایش می دهیم.

به تصویر ۴ توجه کنید. این گزارش می تواند نتایج حاصل از فیلتر کردن جدول توسط گروه «هزینه اداری» را نشان دهد.

از طرفی شاید بهتر باشد که از تابع «زیرجمع» (SUBTOTAL) که با فیلتر نیز همخوانی دارد استفاده کنیم. بنابراین فرمولی که برای انجام این کار در سطر دوم کاربرگ و در بالای ستون هزینه و درآمد خواهیم نوشت به صورت زیر خواهد بود.

E2: =SUBTOTAL(9,E4:E20)

F2: =SUBTOTAL(9,F4:F20)

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

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

به تصویر ۵ توجه کنید. مانده حاصل از جمع هزینه و درآمد به همراه زیر جمع براساس فیلتر روی درآمد محصول ۱ صورت گرفته است. هر چند سطرهای ۴ تا ۲۰ در تابع SUBTOTAL معرفی شده اند، ولی فقط سلول هایی از این ستون در جمع نقش دارند که مطابق با فیلتر بوده و نمایش داده شده اند.

تصویر ۵: محاسبه زیر جمع و فیلتر در کاربرگ درآمد و هزینه

گام سوم: تهیه گزارش از جدول دریافت و پرداخت

هر چند فیلتر گذاری و محاسبه به کمک تابع SUBTOTAL می تواند بسیاری از گزارشات دلخواه ما را تهیه کند، ولی لازم است برای تهیه هر گزارش، گزارش قبلی را تغییر دهیم. از طرفی گزارش هایی که توسط تابع SUBTOTAL و فیلتر ایجاد می شوند، روی جدول اطلاعاتی اعمال شده و عملاً جدول داده های اصلی، دیده نمی شوند. بنابراین بهتر است از تکنیک یا ابزار «جدول محوری» (PivotTable) استفاده کنیم تا هم گزارش ها را بطور مجزا از جدول اطلاعاتی داشته باشیم و هم اینکه بتوانیم بطور همزمان چندین گزارش را مشاهده کنیم.

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

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

ناحیه اطلاعاتی «کاربرگ درآمد هزینه» یعنی سلول های B3 تا F9 را انتخاب کنید.
از برگه Insert و از قسمت Tables گزینه PivotTable را انتخاب کنید.
تنظیمات مربوط به پنجره ظاهر شده را مطابق با تصویر ۶ اجرا کرده و دکمه OK را انتخاب کنید.

به این ترتیب یک جدول محوری بر اساس متغیرهای موجود در ستون های تاریخ تا درآمد ساخته می شود. کافی است که در این هنگام متغیرهای دسته بندی (Grouping) و جمع بندی (Summarizing) را معرفی کنیم تا گزارش کامل شود.

همانطور که می دانید، متغیرهایی که برای دسته بندی لازم هستند، باید شامل مقادیر تکراری باشند تا امکان گروه بندی وجود داشته باشید. می توان از ستون «تاریخ» و همچنین «دسته» برای قسمت های ستون (Column) یا سطر (Row) جدول محوری استفاده کرد. از طرفی اگر قرار است جمع هزینه یا درآمد را به تفکیک هر یک از این بخش های بدست آوریم، لازم است که متغیرهای هزینه و درآمد را به عنوان متغیر «جمع بندی» (Value) معرفی می کنیم.

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

همانطور که مشاهده می کنید، این گزارش فقط مربوط به هزینه های صورت گرفته است.

به یاد داشته باشید که با دوبار کلیک روی یک گزینه از جدول محوری، در یک کاربرگ جدید، سطرهایی که نتیجه جمع بندی آن ها سلول بوده اند، دسترسی خواهید داشت. به این کار در جدول محوری، Drill in یا «کنکاش کردن» می گویند. این کار درست مثل آن است که از یک گزارش کلی به گزارش فرعی و ریز اسناد مربوط به آن حساب کل توسط سیستم حسابداری با اکسل دست پیدا کنید.

نکته: البته می توان همه این گزارشات در یک جدول ارائه نمود. برای این کار کافی است متغیر «درآمد» را به قسمت Values در قاب PivotTable Fields اضافه کنید. البته با این کار، گزارش و جدول محوری، عریض تر شده و شاید خوانایی لازم را نداشته باشد. ولی به هر حال می توان بیش از یک متغیر را در قسمت های مختلف یک جدول محوری یعنی، سطر-Row، ستون-Column، فیلتر-Filter و حتی مقادیر جمع بندی- Values، قرار داد. این کار گزارش حسابداری با اکسل را متنوع و تکمیل تر می کند.

توجه داشته باشید که برای نمایش اعداد به صورت فارسی یا قالب بندی جدول محوری باید از قلم های فارسی و همچنین رنگ آمیزی یا سبک های (Style) جدول استفاده کنید.

گام چهارم: به روز رسانی جدول گزارش

متاسفانه در زمانی که جدول اصلی یعنی جدول درآمد هزینه، دارای سطر جدیدی شده یا مقادیر آن تغییر یابد، جدول محوری که حاوی گزارش های جمع بندی است، تغییر نکرده و داده های جدید مورد محاسبه قرار نمی گیرند. بنابراین این لازم است ناحیه جدول اطلاعاتی مربوط به جدول محوری را مجدد تعیین کنید. این کار به کمک دستور Change Data Source از برگه Analyze در بخش PivotTable Tools امکان پذیر است. این برگه فقط در زمانی که یکی از سلول های جدول محوری انتخاب شده باشد، ظاهر خواهد شد.

سیستم حسابداری با اکسل باید به روز شود. لازم است که تغییرات صورت گرفته در این جدول، در گزارش جدول محوری، اعمال شوند، باید جدول محوری را به روزآوری (Refresh) کنید. به این منظور یکی از سلول های جدول محوری را انتخاب کرده و از برگه Data در قسمت Connection، روی Refresh All کلیک کنید.

با این کار تمامی تغییراتی که در جدول اصلی، جدول پایه و داده های آن ها ایجاد کرده اید در گزارش جدول محوری ارائه و مورد محاسبه قرار خواهد گرفت. به این ترتیب آخرین اطلاعات را در گزارش خواهید دید. در تصویر ۸، محل قرارگیری دستور به روزآوری (Refresh All) دیده می شود.