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

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

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

مراحل گام به گام ایجاد برنامه حسابداری در اکسل

نرم‌افزارهای صفحه گسترده مانند مایکروسافت اکسل (MS-Excel) بیشتر برای سازماندهی اطلاعات حسابداری و ثبت دریافت‌ها و پرداخت‌ها به کار می‌روند. در این فرآیند، محاسبه مانده حساب، که از تفاوت میان پرداخت‌ها و دریافت‌ها به دست می‌آید، اهمیت زیادی دارد. مشابه دفتر روزنامه که در آن اسناد دریافتنی و پرداختنی ثبت می‌شود، در اکسل هم می‌توان جدولی برای وارد کردن این اطلاعات ساخت و سپس مجموع دریافت‌ها، پرداخت‌ها و مانده حساب را محاسبه کرد. این یکی از کارهای ابتدایی است که یک حسابدار برای انجام وظایف خود در حسابداری انجام می‌دهد.

برای تهیه گزارش‌های حسابداری در اکسل (مثل جمع‌بندی‌ها و صورت‌های مالی)، استفاده از ابزارهای دیگر نرم‌افزار مانند فیلترها و تابع SUBTOTAL بسیار ضروری است. همچنین استفاده از «جدول محوری» (PivotTable) با توجه به انواع گزارشاتی که می‌تواند تولید کند، گزینه بسیار مناسبی برای تهیه صورت‌های مالی است.

مطالعه بیشتر: تجزیه و تحلیل صورت های مالی

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

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

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

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

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

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

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

نکته: هر تراکنش مالی، چه دریافت یا پرداخت، به عنوان “تراکنش” شناخته می‌شود. بنابراین، هر سطر از این جدول نمایانگر یک تراکنش مالی خواهد بود.

حالا به معرفی ستون‌های مختلف جدول اکسل و نقش هر کدام در ثبت تراکنش‌های مالی می‌پردازیم:

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

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

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

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

برای این کار، مراحل زیر را دنبال می‌کنیم:

1. ابتدا سطر سوم کاربرگ که شامل نام ستون‌هاست را انتخاب می‌کنیم.
2. سپس از تب Data گزینه Filter را فعال می‌کنیم.
3. با استفاده از دستگیره‌های فیلتر که بالای هر ستون قرار دارند، می‌توانیم هر گزینه از ستون “دسته” را انتخاب کرده و فقط سطرهای مربوط به آن گزینه را نمایش دهیم.

برای مثال، به تصویر ۴ دقت کنید. در اینجا، گزارش نتایج فیلتر شدن جدول بر اساس گروه “هزینه اداری” نمایش داده شده است.

همچنین ممکن است بخواهیم از تابع “زیرجمع” (SUBTOTAL) استفاده کنیم که با فیلتر هم‌خوانی دارد. برای این منظور، فرمول‌هایی که در سطر دوم کاربرگ، بالای ستون‌های هزینه و درآمد وارد می‌کنیم به شکل زیر خواهند بود:

E2: =SUBTOTAL(9,E4:E20)

F2: =SUBTOTAL(9,F4:F20)

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

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

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

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

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

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

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

• ابتدا ناحیه اطلاعاتی کاربرگ درآمد و هزینه (یعنی سلول‌های B3 تا F9) را انتخاب کنید.
• از برگه Insert، در قسمت Tables، گزینه PivotTable را انتخاب کنید.
• در پنجره‌ای که باز می‌شود، تنظیمات را طبق تصویر 6 انجام داده و دکمه OK را بزنید.

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

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

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

این گزارش تنها مربوط به هزینه‌های صورت‌گرفته است.

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

نکته دیگر اینکه می‌توان تمام این گزارش‌ها را در یک جدول ارائه کرد. برای این کار، کافی است متغیر «درآمد» را به بخش Values در پنجره PivotTable Fields اضافه کنیم. البته این کار ممکن است باعث شود جدول محوری گسترده‌تر شود و خوانایی آن کاهش یابد، اما می‌توان چندین متغیر را در بخش‌های مختلف جدول محوری (سطر، ستون، فیلتر، و مقادیر جمع‌بندی) قرار داد. این کار باعث می‌شود گزارش‌های حسابداری در اکسل متنوع‌تر و جامع‌تر باشند.

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

مطالعه بیشتر: ۴ نوع کاربرگ حسابداری برای آماده سازی مالی

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

وقتی که جدول اصلی (که شامل درآمد و هزینه‌ها است) تغییراتی مثل اضافه شدن سطر یا تغییر مقادیر را تجربه می‌کند، جدول محوری (Pivot Table) که گزارش‌های خلاصه شده را نشان می‌دهد به‌طور خودکار به‌روزرسانی نمی‌شود و این تغییرات در محاسبات لحاظ نمی‌شود. بنابراین برای اینکه داده‌های جدید در محاسبات گنجانده شود، لازم است که منطقه اطلاعاتی مربوط به جدول محوری را دوباره مشخص کنید. این کار از طریق گزینه “Change Data Source” در برگه Analyze که در بخش PivotTable Tools قرار دارد، انجام می‌شود. این برگه فقط زمانی قابل مشاهده است که یکی از سلول‌های جدول محوری انتخاب شده باشد.

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

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

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