اکسل پیشرفته

سرفصلهای آموزشی:

​★​​​​​​توابع اطلاعاتی:

✔︎نمایش آدرس، شماره ستون، شماره ردیف سلول: CELL Address / Column / Row
✔︎برگرداندن داده/مقدار سلول CELL Contents
✔︎نمایش مسیر ذخیره و نام کارپوشه و کاربرگ سلول: CELL Filename
✔︎نمایش تعداد کل و شماره کاربرگ‌های کارپوشه: SHEET / SHEETS
✔︎بررسی عددی، زوج و فرد بودن داده سلول: ISNUMBER / ISEVEN / ISODD
✔︎بررسی خالی بودن محتوای سلول: ISBLANK
✔︎بررسی متنی بودن محتوای سلول: ISTEXT
✔︎بررسی فرمول‌نویسی داشتن سلول: ISFORMULA

★توابع منطقی:

✔︎ایجاد شرط برای داده‌ها: If
✔︎ایجاد چند شرط با بررسی یک / همه شرطها: Or / And
✔︎بررسی و ترکیب چند شرط برای داده‌ها: If to If / Ifs
✔︎رشته‌متنی جایگزین برای خطای N/A# در فرمول‌نویسی: IfNA

★توابع متنی:

✔︎شمارش طول کارکتر رشته متنی/عددی سلول: Len
✔︎جداسازی داده از سمت راست / وسط / چپ سلول: Right / Mid / Left
✔︎ترکیب و چسباندن رشته‌های متنی و داده‌های سلولها: Concatenate
✔︎تکرار کارکتر/رشته‌متنی به تعداد دلخواه: Rept
✔︎حذف فاصله اضافی ابتدا و انتها رشته‌متنی سلول: Trim
✔︎جایگزینی و تغییر در رشته متنی: Replace
✔︎جایگزینی مقدار دلخواه در سلول: Substitute
✔︎برگرداندن کارکتر بر اساس کد عددی: Char
✔︎برگرداندن موقعیت یک کارکتر/رشته متنی با/بدون حساسیت از رشته متنی سلول: Find / Search
✔︎ترکیب و ادغام داده‌های چند سلول و رشته‌متنی با یک جداکننده مشترک: Textjoin
✔︎جداسازی رشته‌های‌متنی در یک سلول براساس جداکننده مشترک بین داده‌ها: Textsplit
✔︎تبدیل کارکترهای رشته متنی انگلیسی به حروف بزرگ/کوچک: Upper / Lower
✔︎تبدیل حرف ابتدایی رشته متنی انگلیسی به حرف بزرگ: Proper
✔︎تبدیل رشته متنی حاوی عدد به مقدار عددی با قابلیت محاسبه: Value
✔︎جداسازی و استخراج داده از قبل/بعد کارکتر/رشته‌متنی در سلول: TextBefore/TextAfter
✔︎نمایش/تبدیل داده به رشته‌متنی با فرمت/ظاهر دلخواه: Text

★توابع ریاضی:

✔︎محاسبه جمع اعداد در محدوده انتخابی: Sum
✔︎محاسبه جمع اعداد در محدوده انتخابی با یک/چند شرط: SumIf / Sumifs
✔︎رُند کردن جزء صحیح/اعشار  به نزدیکترین عدد: Round
✔︎رُند کردن جزء صحیح/اعشار عدد به تعداد رقم دلخواه به بالا / پایین: RoundUp / RoundDown
✔︎برگرداندن جزء صحیح عدد و رُند رو به پایین: Int
✔︎محاسبه جمع، میانگین و شمارش مقادیر محدوده فیلتر و مخفی‌شده: Subtotal
✔︎تبدیل عدد منفی به عدد مثبت(قدر مطلق): Abs
✔︎تولید عدد تصادفی بین بازه اعداد دلخواه: Randbetween
✔︎تولید اعداد متوالی با ترتیب دلخواه(شماره ردیف، تاریخ و…): Sequence

★توابع جست‌وگر:

✔︎جست و جو در جدول داده و برگرداندن داده موردنظر از سطر/ستون انتخابی: Lookup
✔︎جست و جوی عمودی / افقی در جدول داده برای برگرداندن داده مورد نظر از ستون/سطر: Vlookup / Hlookup
✔︎جست و جو بدون محدودیت در جدول داده برای برگرداندن داده مورد نظر: Xlookup
✔︎جست و جوی غیرمستقیم در جدول داده برای برگرداندن داده مورد نظر: INDIRECT
✔︎ارجاع به یک سلول برای برگرداندن داده موردنظر براساس موقعیت سطر و ستون: OFFSET
✔︎برگرداندن داده بر اساس موقعیت آن در جدول داده: Index
✔︎برگرداندن موقعیت سطر/ستون داده مورد جستجو از جدول داده: Match
✔︎لینک‌کردن آدرس فایل و سایت به سلول: Hyperlink
✔︎فیلتر پیشرفته داده‌ها با شرط دلخواه: Filter
✔︎مرتب‌سازی داده‌ها بر اساس یک یا چند فیلد: Sortby
✔︎برگرداندن داده از بین لیست انتخابی: Choose
✔︎برگرداندن شماره ردیف / ستون سلول: Row / Column
✔︎تغییر جهت نمایش جدول‌ا‌داده از افقی به عمودی و بلعکس: Transpose

★توابع آماری:

✔︎شمارش تعدادی سلولهای دارای داده عددی: Count
✔︎شمارش تعدادی سلولهای غیرخالی(پُر با هر داده‌ای): CountA
✔︎شمارش تعدادی سلولهای خالی: CountBlank
✔︎شمارش تعدادی سلولها با یک / چند شرط: CountIf / Countifs
✔︎نمایش مقدار nاُمین عدد کوچک / بزرگ در محدوده: Small / Large
✔︎مشخص‌کردن رتبه داده‌ها/اعداد در محدوده: Rank
✔︎نما و بیشترین تکرار داده در محدوده: Mode
✔︎مشخص کردن میانه اعداد در محدوه: Median
✔︎بیشترین / کمترین عدد در محدوده اعداد: Max / Min
✔︎بیشترین / کمترین عدد در محدوده اعداد  با چند شرط: MaxIfs / MinIfs
✔︎محاسبه میانگین اعداد: Average
✔︎میانگین اعداد در محدوده اعداد  با یک شرط: AverageIf

★توابع مالی:

✔︎محاسبه مبلغ، اصل و  بهره هر قسط تسهیلات: Pmt / Ppmt / Ipmt
✔︎محاسبه استهلاک به روش خط مستقیم و مجموع سنوات: Sln / Syd

★توابع تاریخ و زمانی:

✔︎نمایش زمان و تاریخ لحظه‌ای سیستم: Now / Today
✔︎محاسبه اختلاف تعداد روز بین بازه تاریخ: Days
✔︎محاسبه اختلاف تعداد سال، ماه و روز بین بازه تاریخ: Datedif
✔︎محاسبه تعداد روزهای باقی‌مانده / گذشته تاریخ دلخواه: Today
✔︎محاسبه تعداد روزهای‌کاری بین ۲بازه تاریخی با احتساب تعطیلات پایان هفته و رسمی: Networkdays.intl
✔︎نمایش تاریخ پایان/شروع کار با احتساب تعطیلات پایان هفته و رسمی: workday.intl
✔︎محاسبه اختلاف روزانه ۲ تاریخ: Days

★مغایرت‌گیری داده‌ها و گزارشات:

✔︎به کمک فرمول‌نویسی و ترکیب توابع با توابع منطقی(If, Ifna)، توابع ریاضی(Sumif)،
توابع جستجوگر(Vlookup, Xlookup) و توابع آماری(Countif) مغایرت‌گیری از داده‌های مختلف مثل
صورتحساب بانکی و نرم‌افزار، موجودی و کالاها در انبار و نرم‌افزار، اسامی پرسنل در بانکهای اطلاعاتی و…
از لحاظ تعدادی، محتوایی، مبلغی و…
✔︎به کمک ابزار فرمت‌دهی شرطی(Conditional Formatting) از لحاظ محتوایی و ظاهری

★دستورات و ابزارها:

✔︎دستورات پنجره Paste Special : جابه جایی مکان و جهت نمایش جدول داده
محاسبات 4 عمل اصلی(ضرب، تفریق، تقسیم و ضرب) بدون فرمول‌نویسی
✔︎ابزار Text to Columns : جداسازی و تفکیک رشته‌های متنی و داده‌های یک سلول به چند سلول
✔︎ابزار Conditional Formatting : فرمت‌دهی شرطی
فرمول‌نویسی با توابع اطلاعاتی، منطقی، آماری و… در دستور کاندیشنال فرمتینگ
نمایش ردیفهای داده‌ها پر با فرمت دلخواه
یکی‌درمیون رنگی‌کردن ردیفها(زوج و فرد)
آلارم تشخیص ردیفهای داده‌ای که بیش‌از یکبار ثبت شدن 
آلارم برای وضعیت پرداخت تسویه‌شده/مشتریان بدهکار
اضافه‌کردن چک‌باکس و نمایش ردیفهای تیک‌خورده با فرمت دلخواه
ترسیم Data Bar در سلول با فرمت‌ دلخواه
اولویت‌بندی فرمتها در کاندیشنال‌فرمتینگ و…
✔︎مدلسازی، تحلیل داده‌ و حل مسئله:
‌Solver : مغایرت‌گیری، تحلیل و حل‌مسئله
مثال ۱) یک عدد حاصل جمع چند عدد هست
۲) از هرکالا چه‌تعداد بفروشیم که به سود موردنظر برسیم
Goal seek : رسیدن به هدف
مثال ۱) پرسنل در روز چه‌قدر حقوق بگیره که خالص‌پرداختی دلخواه بشه
۲) چه‌مبلغی فروش درنظر بگیریم که به سود دلخواه برسیم
✔︎ابزار Data Validation : اعمال محدودیت در ثبت داده با فرمول‌نویسی
جلوگیری از ثبت داده تکراری
لیست کشویی داینامیک(آپدیت گزینه‌های‌حذف/اضافه‌شده‌)
لیست کشویی وابسته و….
✔︎ابزار Subtotal و Consolidate : خلاصه سازی، گروه بندی، دسته بندی، جمع بندی و
ترکیب داده‌ها برای تهیه گزارشات مختلف و انجام عملیات جمع، شمارش، میانگین و… روی داده‌ها

تنظیمات:
✔︎تغییر جداکننده‌ آرگومانهای تابع در فرمول‌نویسی از کاما به سمی‌کالن و بلعکس
✔︎تغییر جداکننده رقم اعشار
✔︎ساخت لیست سفارشی تکمیل خودکار روزهفته، ماه‌ها، فصل‌ها و…

★گزارش‌گیری با ابزار قدرتمند PivotTable :

✔︎نکات و ترفندهای گزارش‌گیری با PivotTable
فروش مبلغی/درصدی تجمعی ماهانه و سالانه
رتبه‌بندی مبالغ فروش در هر فیلد
درصد تغییرات فروش
فیلتر گزارش با چند معیار با Slicer
تهیه نمودار ستونی و خطی با PivotChart
و….

نکات و دستورات فرمول‌نویسی حرفه‌ای:
✔︎انواع آدرس‌دهی و ارجاع به سلولها
✔︎ Defined Name & Name Manager
تعریف نام برای محدوده سلولها در فرمول‌نویسی
✔︎ Trace Precedents & Trace Dependents
ردیابی وابستگی و نمایش سلولهای موثر در فرمول‌نویسی
✔︎ Calculation Options
تنظیمات محسبات خودکار/دستی برای فرمول‌نویسی
✔︎ Custom Format Number
انواع فرمتهای سفارشی نمایش داده عددی مثل
فرمت درصد افزایش/کاهش،فرمت حذف۶رقم آخر(میلیون ریال) و…
✔︎فرمول‌نویسی کاربردی در گزارشات مثل:
درصد تغییرات از کل به جزء رسیدن و بلعکس و…
✔︎Error Checking
بررسی علت و رفع خطاهای فرمول نویسی
✔︎Watch Window
نمایش همزمان داده‌های چندسلول از چند کاربرگ در کنار هم و…

★ضبط ماکرو و کد نویسی:

✔︎تعریف و نکات ضبط و اجرای ماکروها
✔︎ضبط ماکروهای کاربردی مثل:
اضافه و حذف کردن ردیفهای گزارش با حفظ فرمت و فرمول و… خام کردن اطلاعات صورتحسابها
✔︎کدنویسی تنظیم خودکار عرض ستون و ارتفاع ردیفها
✔︎تنظیم و شخصی سازی دکمه برای اجرای ماکرو

مینی دوره تجزیه و تحلیل داده‌ها با Power Query :

✔︎ آموزش تعاریف و مفاهیم ابزار قدرتمند پاورکوئری؛ از پایه با یه‌گزارش واقعی و بازارکاری از استخراج تا بارگزاری؛ بدون فرمول‌نویسی و به‌صورت خودکار و سریع قدم به قدم برای فرآیند پردازش، تغییر و تجزیه و تحلیل داده‌ها با دستورات
Split column, Manage columns, Reduce rows, Remove duplicates, GroupBy, Replace values, Merge queries, Append queries, Trim
مثالها: ۱) تهیه گزارشات فروش از خروجی داده‌های خام فروش ماهانه
۱.۱- از هر محصول چه تعداد و مبلغی در کل فروش داشته‌ایم؟
۱.۲- هر مشتری در کل چه تعداد و مبلغی سفارش خرید از ما داشته است؟
۲) تهیه گزارش مغایرت‌گیری مبلغی ماهانه صورتحسابهای بانک و دفاتر
۲.۱- جمع گردش ستونهای بدهکار و بستانکار روزانه
۲.۲- چه‌رکوردها/مبالغی در صورتحساب بانک موجود هست اما در نرم‌افزار حسابداری ثبت‌نشده؟
۲.۳- چه‌رکوردها/مبالغی در صورتحساب دفاتر ثبت شده اما در بانک موجود نیست؟
۲.۴- محاسبه اختلاف روزانه بد/بس بین صورتحسابها

مشخصات دوره

• مدت زمان دوره: ۱۲ ساعت

• مدرس دوره: مهدی غیاثی

• محتواهای دوره: ۱۸ پارت ویدیو ضبط‌شده با کیفیت + ۱۱ فایل اکسل تمرینی +‌ جزوه پی‌دی‌اف راهنما

• پیش نیاز: اکسل مقدماتی(در حد مبتدی: شناسایی ابزارها و دستورات پایه‌ای اکسل-نیازی به تسلط به سرفصل توابع و فرمول‌نویسی نیست.)

درباره مدرس

مهدی غیاثی: مدرس نرم‌افزار اکسل - کارشناس ارشد حسابداری
۷ ساله که به صورت تخصصی در حوزه آموزش و ارائه خدمات با نرم‌افزار اکسل فعالیت دارم:
●تولید محتوای آموزشی در اینستاگرام، یوتوب، تلگرام و آپارات با برند AccExcel
●دوره‌های آموزش آفلاین اکسل مقدماتی-کاربردی، اکسل پیشرفته، مغایرت‌گیری داده‌ها با اکسل، کاربرد اکسل در حقوق‌و‌دستمزد با رویکرد چالش‌ها و نیازهای ویژه بازارکار و نمونه گزارشات واقعی
●تهیه و طراحی انواع گزارشات و داشبوردهای مالی و مدیریتی با اکسل و پاوربی‌ای
●برگزاری و آموزش دوره‌های اکسل در دانشگاه‌ها و شرکت‌ها برای دانشجویان رشته‌های حسابداری، مدیریت مالی، مهندسی صنایع، مدیریت بازرگانی، مهندسی آی‌تی و پرسنل واحدهای مالی و مالیاتی، فروش، بازرگانی، آی‌تی، مدیریت، منابع انسانی، تولید و...
●آموزش گروهی و خصوصی، حضوری و آنلاین دوره‌های اکسلی