آموزش ساخت نرم افزار حسابداری ساده با اکسل (برای کسب و کارهای کوچک)

بخش جدایی ناپذیر هر کسب و کاری، مدیریت مالی و حسابداری 💰 می باشد.

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

آموزش ساخت برنامه حسابداری اکسل برای کسب و کارهای کوچک

How to Create an Excel Accounting App for Small Businesses

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

۱- آموزش حسابداری با اکسل

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

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

سطرهای جدول، نشان دهنده مراکز هزینه یا درآمد می باشد. هر مورد که مربوط به هزینه است، با عبارت هزینه شروع شده است و هر مورد که مربوط به درآمد می باشد با کلمه درآمد شروع شده است.

۲- آموزش حسابداری مغازه با اکسل

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

ستون تاریخ: در این ستون، تاریخ هر تراکنش (دریافت یا پرداخت) وارد می‌ شود. برای نمایش تاریخ شمسی، می‌ توانید از تنظیمات فارسی‌ سازی تاریخ در اکسل استفاده کنید. تاریخ شمسی برای تهیه گزارش‌ های بومی بسیار کاربردی است. به مسیر Home > Number بروید. در بخش Category، گزینه Date را انتخاب کنید و Location را روی Persian تنظیم کنید.

ستون شرح: در این بخش توضیح مختصری در مورد تراکنش وارد می‌ شود. محتوا می‌تواند متنی، عددی یا ترکیبی از هر دو باشد.

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

ستون هزینه: مبلغ هر هزینه در این ستون وارد می‌ شود. توجه کنید که واحد پولی را در تمام جدول یکسان (ریال یا تومان) نگه دارید تا جمع‌ ها و مانده‌ ها معنی‌ دار باشند.

ستون درآمد: مبالغ مربوط به دریافت‌ ها یا درآمدها در این ستون نوشته می‌ شوند.

۱-۲- اعتبار سنجی داده ها

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

برای اینکار، ابتدا، ستون “دسته” را انتخاب کنید.

به منوی Data > Data Validation بروید.

محدوده‌ فهرست (مثلا B2:B11) را وارد نمایید. در بخش Source باید عبارت زیر را وارد کنید. به جای InitialData باید نام کاربرگ یا شیتی که اطلاعات اولیه قرار دارد را به صورت دقیق وارد کنید. دقت کنید که اگر نام کاربرگ شامل فاصله یا حروف فارسی است، باید داخل علامت نقل‌قول تک (‘ ‘) نوشته شود.

=InitialData!$B$2:$B$11

با فعال کردن گزینه‌ های Ignore blank و In-cell dropdown، سطرهای خالی نادیده گرفته می‌ شوند و یک منوی کشویی برای انتخاب دسته‌ ها در هر سلول ظاهر می‌ شود.

۲-۲- جمع‌ بندی هزینه و درآمد

بخشی برای جمع کل دو ستون هزینه‌ ها و درآمدها در نظر بگیرید. برای محاسبه‌ مجموع از تابع SUM استفاده کنید. در این مثال، ستون H مربوط به هزینه و ستون I مربوط به درآمد است. H20 و I20 نشان دهنده انتهای لیست هستند. اگر جدول شما بزرگ‌ تر است، محدوده را مطابق آخرین سطر خود تغییر دهید.

ما جمع هزینه ها و درآمد ها را در ستون A و B قرار دادیم.

A4: =SUM(H4:H20)

B4: =SUM(I4:I20)

برای محاسبه‌ مانده (سود یا زیان)، کافی است جمع هزینه را از درآمد کم کنید.

C4: =B4 – A4

برای اینکه هنگام اسکرول کردن در جدول، عنوان ستون‌ ها و سطر جمع همیشه قابل مشاهده باشند، روی سطر عنوان (در این مثال سطر ۴ که می خواهیم سطرهای قبل از آن ثابت باشند) کلیک کنید. سپس به منوی View > Freeze Panes بروید. به این ترتیب، سطر ۱ تا ۳ همیشه ثابت خواهد بود.

۳- فیلتر کردن داده‌ ها در جدول درآمد و هزینه

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

برای فعالسازی فیلتر در اکسل، سطر عناوین (در این مثال سطر ۳) را انتخاب کنید. فقط سطرهایی که دارای عنوان هستند را انتخاب کنید. سطر خالی نباشد.

از منوی Data در نوار ابزار بالای صفحه، گزینه‌ Filter را فعال نمایید.

اکنون در کنار عنوان هر ستون، یک فلش کوچک ظاهر می‌ شود.

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

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

D2: =SUBTOTAL(9,H4:H20)

E2: =SUBTOTAL(9,I4:I20)

برای نمونه فیلتر دسته “خرید اجناس از عمده فروش” را فعال کردیم. نتیجه زیرجمع به صورت زیر است.

۴- تهیه گزارش از جدول

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

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

ابتدا مطمئن شوید که هیچ فیلتری روی جدول فعال نیست. برای غیرفعال کردن، کافی است در برگه Data دوباره روی دکمه‌ Filter کلیک کنید.

محدوده‌ اطلاعاتی جدول درآمد و هزینه را انتخاب کنید (برای نمونه سلول‌ های E4 تا H11).

به مسیر Insert > PivotTable بروید.

در پنجره‌ باز شده، تنظیمات پیش‌ فرض را تایید کنید و روی OK کلیک نمایید.

اکنون یک PivotTable جدید ایجاد شده است که داده‌ های جدول درآمد و هزینه را به‌ صورت پویا نمایش می‌ دهد.

۱-۴- تنظیم فیلدهای جدول محوری

در سمت راست صفحه، بخش PivotTable Fields ظاهر می‌ شود. باید مشخص کنید هر ستون از داده‌ ها چه نقشی در گزارش داشته باشد.

قسمت Rows (سطر): ستون‌ هایی را قرار دهید که می‌ خواهید بر اساس آن‌ ها دسته‌ بندی انجام شود (برای مثال ستون “دسته” یا “تاریخ”). باید سطرهایی را انتخاب کنید که  شامل مقادیر تکراری باشند تا امکان گروه بندی وجود داشته باشد.

قسمت Values (مقادیر): ستون‌ هایی را اضافه کنید که باید جمع یا محاسبه شوند (برای نمونه ستون‌های “هزینه” و “درآمد”).

تیک گزینه ها را بزنید و برای جابجایی آن ها از روش Drag & Drop یا کشیدن و رها کردن استفاده کنید. برای نمونه گزینه تاریخ را به بخش Rows بکشید. با این تنظیمات، PivotTable به‌ صورت خودکار مجموع هزینه یا درآمد را برای هر دسته محاسبه و نمایش می‌ دهد.

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

در PivotTable اگر روی هر سلول دوبار کلیک کنید، اکسل یک کاربرگ جدید ایجاد می‌ کند که شامل تمام سطرهای جزیی مربوط به همان مقدار است. به این قابلیت، Drill In یا کنکاش در داده‌ ها گفته می‌ شود. مانند زمانی که در سیستم‌ های حسابداری از یک گزارش کلی به جزییات سند مربوطه می‌ روید.

۵- به‌ روزرسانی گزارش جدول محوری

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

در تب PivotTable Analyze، گزینه‌ Change Data Source را انتخاب کنید. در پنجره‌ باز شده، محدوده‌ جدید داده‌ ها (برای نمونه از E4 تا H11 به E4 تا H50) را مشخص کنید و روی OK کلیک نمایید.

اگر محدوده ثابت است، اما داده های موجود در جدول اصلی تغییر کرده اند، پس از کلیک روی یکی از سلولهای جدل محوری، از تب Data یا از تب PivotTable Analyze گزینه‌ Refresh All را انتخاب نمایید.

در انتها مطلب می توانید فایل فشرده شده نمونه کار حسابداری با اکسل که در این مطلب ساخته شده است را دانلود نمایید.

۶- فرمول‌ ها و توابع ضروری در حسابداری با اکسل

در ادامه توابع ضروری برای حسابداری در اکسل را ارائه خواهیم کرد.

جمع شرطی SUMIF: این تابع، فقط زمانی جمع می‌ زند که شرط خاصی برقرار باشد.

=SUMIF(D1:D10,”Sales”,E1:E10)

در مثال بالا، مقادیر ستون E را فقط زمانی جمع می‌ کند که در ستون D، کلمه‌ Sales نوشته شده باشد.

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

=DATEDIF(A1,B1,”y”) → تعداد سال‌ ها

=DATEDIF(A1,B1,”m”) → تعداد ماه‌ ها

=DATEDIF(A1,B1,”d”) → تعداد روزها

محاسبه قسط وام PMT: این تابع، مبلغ قسط دوره‌ ای یک وام را بر اساس نرخ بهره و مدت آن محاسبه می‌ کند.

=PMT(0.05/12,24,1000000)

در مثال بالا، مبلغ قسط ماهیانه وام ۱۰۰۰۰۰۰ تومان با بهره سالیانه ۵٪ و مدت ۲۴ ماه را محاسبه می‌ کند.

محاسبه ارزش آینده سرمایه FV: این تابع، ارزش آینده یک سرمایه‌ گذاری را بر اساس پرداخت‌ های منظم و نرخ بهره مشخص بر می‌ گرداند.

=FV(0.05/12,24,-100)

در مثال بالا، ارزش نهایی سرمایه‌ گذاری ماهیانه ۱۰۰ تومان برای ۲ سال با نرخ بهره سالانه ۵٪ را محاسبه می‌ کند.

تابع شمارش شرطی COUNTIF: این تابع، تعداد سلول‌ هایی که شرط خاصی را دارند، می‌ شمارد. از این می توانید برای شمارش تعداد تراکنش‌ های مثبت، پرداخت‌ های انجام‌ شده یا فاکتورهای باز استفاده کنید.

=COUNTIF(A1:A10,”>0″)

مثال بالا، تعداد سلول‌ هایی از محدوده A1:A10 را که مقدارشان بزرگتر از صفر است را می‌ شمارد.

۷- نکات قالب‌ بندی داده‌ های مالی در اکسل

برای نمایش واضح و حرفه‌ ای اطلاعات مالی، باید به صورت صحیح اعداد را قالب بندی کنید.

ستون یا سلول مورد نظر را انتخاب کنید و در مسیر Home > Number Format گزینه Accounting را انتخاب کنید.

با قابلیت Conditional Formatting، سلول‌ ها بر اساس شرایط خاص، به‌ صورت خودکار رنگ یا استایل خاصی خواهند گرفت. برای نمونه اعداد منفی با قرمز نمایش داده می شوند. جهت فعالسازی این قابلیت به مسیر Home > Conditional Formatting > Highlight Cell Rules / New Rule بروید.

برای حفاظت و امنیت، فایل را با رمز قفل کنید. برای اینکار به مسیر Review > Protect Sheet بروید.

۸- دانلود رایگان نرم افزار حسابداری در اکسل

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

دانلود فایل

همچنین از طریق Microsoft Templates می توانید به قالب‌ های آماده سود و زیان دسترسی داشته باشید. در اکسل به مسیر File > New بروید و Accounting را جستجو کنید.

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

جمع بندی

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

نظرات و پیشنهاد شما؟

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