پث‌وایز

آموزش اکسل و گوگل شیتدرس ۱۲ از ۱۲

پروژه نهایی: ساخت جدول بودجه ماهانه در اکسل

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

چیزی که از این درس می‌بری

  • ورودی (Log)، تنظیمات (Lists) و نتیجه‌ها (Summary) رو جدا از هم نگه دار.
  • لیست‌های کشویی و عددهای خالی، داده‌ها رو از همون اول تمیز نگه می‌دارن.
  • فرمول SUMIFS با محدوده‌های قفل‌شده، جمع هر دسته رو برای ماه انتخابی حساب می‌کنه.
فهرست این درس۶
  1. سه تا شیت، سه تا کار
  2. مرحله‌ی ۱: شیت Log
  3. مرحله‌ی ۲: شیت Summary
  4. مرحله‌ی ۳: هشدارها و نمودار
  5. سرپا نگه‌داشتن فایل
  6. مرور کل دوره

۰۱طراحی ساختار

سه تا شیت، سه تا کار

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

۰۲قدم‌به‌قدم

مرحله‌ی ۱: شیت Log

  1. عنوان‌ها تو ردیف ۱

    تاریخ، دسته‌بندی، شرح، مبلغ. ردیف ۱ رو فریز کن (درس ۴).

  2. لیست کشویی دسته‌بندی

    ابزار Data validation روی ستون B که به شیت Lists وصل می‌شه (درس ۱۱).

  3. مبلغ‌ها به شکل عدد خالی

    کلمه‌ای برای واحد پول تو سلول‌ها ننویس؛ واحد پول رو بذار تو عنوان ستون (درس ۱).

  4. تبدیل به جدول (Table)

    تو اکسل Ctrl+T رو بزن تا ردیف‌های جدید خودکار به جدول اضافه بشن.

خودت رو امتحان کنسؤال ۱ از ۴

چرا دسته‌بندی‌ها و بودجه‌ها رو تو شیت جداگانه‌ی Lists می‌ذاریم به جای اینکه مستقیماً تو فرمول‌ها تایپشون کنیم؟

  1. چون فرمول‌ها نمی‌تونن متن داشته باشن
  2. تا با یه تغییر تو یه جا، لیست کشویی، جمع‌ها و هشدارها با هم آپدیت بشن
  3. چون جدول‌های محوری به شیت دوم نیاز دارن
  4. چون حجم فایل کمتر می‌شه
جواب رو ببین

تا با یه تغییر تو یه جا، لیست کشویی، جمع‌ها و هشدارها با هم آپدیت بشن

آره. یه دسته‌بندی اضافه کن یا بودجه‌ای رو تو Lists تغییر بده؛ هر چی بهش وصله خودبه‌خود آپدیت می‌شه.

۰۳قدم‌به‌قدم

مرحله‌ی ۲: شیت Summary

  1. انتخاب ماه

    تو B1 روز اول ماهی که می‌خوای بررسی کنی رو بنویس، مثلاً ۱ فروردین. تو B2 فرمول =EOMONTH(B1, 0) آخرین روز اون ماه رو بهت می‌ده.

  2. نوشتن دسته‌بندی‌ها

    از A5 به پایین دسته‌بندی‌ها؛ تو B5 بودجه‌هاشون با فرمول جستجو از Lists (درس ۶).

  3. خرج این ماه

    تو C5 بنویس: =SUMIFS(Log!$D:$D, Log!$B:$B, A5, Log!$A:$A, ">="&$B$1, Log!$A:$A, "<="&$B$2) و تا پایین کپی کن.

  4. باقی‌مونده

    تو D5 بنویس: =B5-C5 و تا پایین کپی کن.

مثال

خلاصه‌ی فروردین

دسته‌بندی     بودجه       خرج         باقی‌مونده
خوراکی      8,000,000   6,450,000   1,550,000
رفت‌وآمد     2,500,000   2,900,000   -400,000
قبوض        3,000,000   2,100,000     900,000

خروجی

رفت‌وآمد ۴۰۰٬۰۰۰ از بودجه رد شده

فرمول SUMIFS مبالغی رو جمع می‌زنه که دسته‌بندی‌شون یکی باشه و تاریخ‌شون هم تو همون ماه بیفته. B1 رو به اردیبهشت تغییر بده تا همه‌ی عددها آپدیت بشن.

خودت رو امتحان کنسؤال ۲ از ۴

تو فرمول خرج این ماه، محدوده‌ها با علامت دلار به شکل Log!$D:$D نوشته شدن تا وقتی فرمول رو تا پایین لیست دسته‌بندی‌ها کپی می‌کنی جابه‌جا نشن.

  1. درست
  2. نادرست
جواب رو ببین

درست

درسته. تو هر ردیف فقط باید دسته‌بندی عوض بشه (A5، A6…)، در حالی که ستون‌های Log و سلول‌های ماه باید ثابت بمونن. این همون قانون درس ۳ بود.

۰۴قدم‌به‌قدم

مرحله‌ی ۳: هشدارها و نمودار

  1. قرمز وقتی از بودجه رد شدی

    فرمت شرطی روی A5:D12 با فرمول =$D5<0 (درس ۱۱).

  2. زرد وقتی نزدیک شدی

    قانون دوم: =AND($D5>=0, $C5>0.9*$B5) برای دسته‌هایی که بالای ۹۰٪ بودجه‌شون خرج شده.

  3. نمودار بکش

    ستون دسته‌بندی و خرج رو انتخاب کن و یه نمودار میله‌ای بذار (درس ۱۰). عنوانش رو پیام‌دار بذار، مثلاً «فروردین: بودجه‌ی رفت‌وآمد رد شده».

خودت رو امتحان کنسؤال ۳ از ۴

هر بخش فایل بودجه‌بندی از کدوم درس‌های قبلی میاد؟

ورود درست داده‌هاگرفتن جواب‌ها از شیت

  • لیست کشویی دسته‌بندی
  • مبلغ‌ها به شکل عدد خالی
  • فرمول SUMIFS برای خرج این ماه
  • قرمز شدن ردیف وقتی بودجه رد می‌شه
  • نمودار میله‌ای هزینه‌ها
جواب رو ببین

ورود درست داده‌ها: لیست کشویی دسته‌بندی, مبلغ‌ها به شکل عدد خالی

گرفتن جواب‌ها از شیت: فرمول SUMIFS برای خرج این ماه, قرمز شدن ردیف وقتی بودجه رد می‌شه, نمودار میله‌ای هزینه‌ها

سرپا نگه‌داشتن فایل

  • هزینه‌ها رو همون موقع ثبت کن؛ چند ثانیه در روز خیلی بهتر از یه غروب نشستن پای رسیدهاست.
  • هیچ‌وقت روی سلول فرمول‌دار تایپ نکن؛ اگه عددی غلطه، ردیفش رو تو شیت Log درست کن.
  • ماهی یه بار بررسیش کن: B1 رو عوض کن، ردیف‌های قرمز و زرد رو ببین، و بودجه‌ی ماه بعد رو تنظیم کن.
  • قبل از تغییرات بزرگ (مثل اضافه کردن شیت یا ستون جدید)، یه کپی از فایل نگه دار.

خودت رو امتحان کنسؤال ۴ از ۴

تو اردیبهشت، شیت Summary آرمان خرج همه‌ی دسته‌ها رو ۰ نشون می‌ده، با اینکه تو Log ۶۰ تا ردیف برای اردیبهشت داره. اول از همه چی رو باید چک کنه؟

  1. اینکه B1 هنوز روی فروردینه
  2. اینکه نمودار دایره‌ایه
  3. اینکه شیت Log مرتب شده
  4. اینکه بودجه‌ها زیادی بالان
جواب رو ببین

اینکه B1 هنوز روی فروردینه

درسته. تابع SUMIFS فقط تاریخ‌های بین B1 و آخر اون ماه رو جمع می‌زنه. اگه B1 هنوز فروردین باشه، ردیف‌های اردیبهشت بیرون اون بازه می‌افتن و جمع‌ها هیچی برای اردیبهشت نشون نمی‌دن. B1 رو بذار ۱ اردیبهشت تا پیداشون بشه.

۰۶جمع‌بندی

مرور کل دوره

  • ورودی (Log)، تنظیمات (Lists) و نتیجه‌ها (Summary) رو جدا از هم نگه دار.
  • لیست‌های کشویی و عددهای خالی، داده‌ها رو از همون اول تمیز نگه می‌دارن.
  • فرمول SUMIFS با محدوده‌های قفل‌شده، جمع هر دسته رو برای ماه انتخابی حساب می‌کنه.
  • قانون‌های رنگی و نمودار میله‌ای، جمع‌ها رو به تصمیم‌هایی تبدیل می‌کنن که می‌تونی عملیشون کنی.

به سبک خودت یادش بگیر.

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

Cafe Bazaar Myket توی اپ شخصی‌سازیش کن