تحلیل اکسل با هوش مصنوعی

تحلیل فایل «فروش-۶-ماه.xlsx»

آنچه دریافت کردم

یک فایل اکسل با یک برگه (Sheet 1) و ۱۸ ردیف داده به‌علاوهٔ ردیف عنوان. متن فایل ناگهانی قطع نشده و به نظر کامل است. فایل نام صادرکننده یا تاریخ تهیه ندارد.

۱. ساختار فایل

• بازهٔ زمانی: فروردین تا شهریور. سال در فایل نیامده. فرض: سال ۱۴۰۵ است، چون امروز ۱۴۰۵/۰۷/۰۹ است و شهریور تازه تمام شده.
• واحد: طبق توضیح شما «تعداد» یعنی تعداد سفارش، نه تعداد کالا و نه مبلغ فروش.

۲. ایرادهای داده

• ماه به‌صورت نام متنی است. اکسل آن را به ترتیب الفبا مرتب می‌کند (اردیبهشت می‌آید پیش از فروردین). برای مرتب‌سازی و مقایسهٔ دورهٔ اول و دوم، ستون کمکی شمارهٔ ماه لازم است (فرمولش در بخش ۵ آمده).
• سال ندارد. اگر فایل را با داده‌های سال بعد ترکیب کنید، ماه‌ها قاطی می‌شوند.
• ترکیب مشهد و صابون وجود ندارد. از چهار ترکیب ممکن شهر و محصول، سه ترکیب در فایل هست. باید روشن شود که در مشهد صابون فروخته نمی‌شود یا ردیف‌هایش جا افتاده.
• عدد فروش نیست، تعداد سفارش است. قیمت دو محصول فرق دارد، پس رشد یا افت تعداد سفارش لزوماً با رشد یا افت درآمد برابر نیست.
• کاراکترهای پنهان: در متن دریافتی ارقام لاتین و حروف «ی» و «ک» فارسی دیده می‌شوند. ولی «دست‌ساز» نیم‌فاصله دارد و اگر جایی بدون آن تایپ شده باشد، در گروه‌بندی دو محصول جدا حساب می‌شود. اینجا یک شکل دارد و مشکلی نیست.
• تکراری، ردیف جمع‌کل داخل داده، یا مقدار پرت مشکوک پیدا نکردم.

۳. پاسخ سؤال شما

همهٔ اعداد از ۱۸ ردیف فایل محاسبه شده‌اند. مجموع کل ۱٬۶۸۶ سفارش است.

الف) رشد هر ترکیب، فروردین در برابر شهریور

مقایسهٔ فقط دو ماه به نوسان تصادفی حساس است. برای اطمینان، سه ماه اول (فروردین تا خرداد) را با سه ماه آخر (تیر تا شهریور) سنجیدم:

ب) به تفکیک محصول و شهر

نتیجه

• بیشترین رشد: محصول شمع معطر، و از نظر شهر مشهد. مشهد هم در درصد (‎+۱۳۱٫۱٪) و هم در تعداد مطلق (‎+۵۹ سفارش) از تهران جلوتر است. فروش شمع در مشهد هر ماه از ماه قبل بیشتر بوده است.
• افت: صابون دست‌ساز در تهران. تعداد سفارش آن شش ماه پیاپی کم شده: ۸۰، ۷۴، ۶۹، ۵۸، ۵۱، ۴۷. این تنها ترکیبی است که در هیچ ماهی بالا نرفته.

۴. پنج نکتهٔ کاربردی

1. شمع معطر موتور رشد است. ۱٬۳۰۷ از ۱٬۶۸۶ سفارش (۷۷٫۵٪) مال شمع است و سهم صابون ۲۲٫۵٪ است. بزرگ‌ترین ترکیب، شمع تهران، تنها ۵۲٫۱٪ کل سفارش‌ها را دارد.
2. مشهد از نظر سهم هم بالا می‌رود. سهم مشهد از سفارش‌های شمع از ۲۷٫۳٪ (۴۵ از ۱۶۵) به ۳۷٫۸٪ (۱۰۴ از ۲۷۵) رسیده. پایهٔ مشهد کوچک است و درصد رشدش را باید با احتیاط خواند، ولی روندش پیوسته است.
3. صابون در تهران جای خود را از دست می‌دهد. سهم صابون از سفارش‌های تهران از ۴۰٪ (۸۰ از ۲۰۰) به ۲۱٫۶٪ (۴۷ از ۲۱۸) رسیده، در حالی که مجموع تهران تقریباً ثابت مانده (بین ۲۰۰ و ۲۲۰). یعنی رشد شمع تهران عملاً افت صابون را پوشانده. اینکه مشتریان از صابون به شمع رفته‌اند فقط یک فرضیه است و با این فایل قابل اثبات نیست. برای بررسی آن به داده‌ٔ سفارش‌ها به تفکیک مشتری نیاز دارید.
4. تنها افت ماهانهٔ کل، تیر بود. مجموع از ۲۸۰ در خرداد به ۲۷۷ در تیر رسید (‎−۳ سفارش، حدود ‎−۱٫۱٪). علت آن کاهش شمع تهران (از ۱۵۰ به ۱۴۲) و ادامهٔ افت صابون بود. از مرداد به بعد رشد برگشت.
5. برای یلدا از حالا برنامه بریزید. شش ماه داده برای نتیجه‌گیری دربارهٔ فصلی بودن کافی نیست. ولی یلدا حدود دو ماه و نیم دیگر است و شمع معطر معمولاً از کالاهای مناسب آن است. فرضیه: تقاضای شمع در آذر بالاتر می‌رود. برای تأیید، فروش آذر سال قبل را کنار این روند بگذارید و موجودی مواد اولیه را از حالا بسنجید.

۵. فرمول‌های بررسی

فرض: داده در سلول‌های A1:D19 است و ردیف ۱ عنوان است. اگر اکسل شما فارسی یا با تنظیمات منطقه‌ای دیگر است، ممکن است جداکنندهٔ آرگومان‌ها ویرگول (,) یا نقطه‌ویرگول (;) باشد.

ستون کمکی E (شماره ماه) در E2 بنویسید و تا E19 پایین بکشید:

=MATCH(A2,{"فروردین","اردیبهشت","خرداد","تیر","مرداد","شهریور"},0)

• A2: نام ماه در همان ردیف.
• آرایهٔ داخل {}: فهرست ماه‌ها به ترتیب سال.
• 0: یعنی تطابق دقیق.

پاک‌سازی احتیاطی حروف عربی (اگر فایل‌های بعدی «ي» و «ك» عربی داشتند):

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,"ي","ی"),"ك","ک"))

مجموع کل (باید ۱٬۶۸۶ شود):

=SUM(D2:D19)

مجموع هر ترکیب (مثلاً شمع در مشهد، باید ۴۲۹ شود):

=SUMIFS($D$2:$D$19,$B$2:$B$19,"مشهد",$C$2:$C$19,"شمع معطر")

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

فروش یک ماه خاص (مثلاً شمع تهران در شهریور، باید ۱۷۱ شود):

=SUMIFS($D$2:$D$19,$B$2:$B$19,"تهران",$C$2:$C$19,"شمع معطر",$A$2:$A$19,"شهریور")

سه ماه اول و سه ماه آخر (مثلاً شمع مشهد، باید ۱۵۸ و ۲۷۱ شود):

=SUMIFS($D$2:$D$19,$B$2:$B$19,"مشهد",$C$2:$C$19,"شمع معطر",$E$2:$E$19,"<=3")
=SUMIFS($D$2:$D$19,$B$2:$B$19,"مشهد",$C$2:$C$19,"شمع معطر",$E$2:$E$19,">=4")

درصد رشد (فرض: عدد ماه اول در G2 و ماه آخر در H2 است):

=(H2-G2)/G2

سلول را با قالب درصد (Percentage) نمایش دهید.

سهم از کل (مثلاً سهم شمع، باید حدود ۷۷٫۵٪ شود):

=SUMIFS($D$2:$D$19,$C$2:$C$19,"شمع معطر")/SUM($D$2:$D$19)

جدول محوری (Pivot Table): از منوی Insert گزینهٔ PivotTable را بزنید و تنظیم کنید:

• Rows: محصول، سپس شهر
• Columns: شماره ماه (ستون E)، تا ماه‌ها به ترتیب سال بیایند
• Values: مجموع (Sum of) تعداد

نمودار پیشنهادی: نمودار خطی (Line) با سه خط، یعنی شمع تهران، شمع مشهد و صابون تهران، و محور افقی از فروردین تا شهریور. برای مقایسهٔ مجموع شش‌ماههٔ ترکیب‌ها، نمودار میله‌ای مرتب‌شده مناسب است.

قدم‌های بعدی

1. سال را به فایل اضافه کنید (یک ستون یا نام برگه) و ستون کمکی شمارهٔ ماه را بسازید.
2. روشن کنید که مشهد صابون نمی‌فروشد یا ردیف‌هایش جا افتاده. اگر جا افتاده، فایل کامل را دوباره بفرستید تا تحلیل تکرار شود.
3. اگر قیمت یا مبلغ هر سفارش را دارید، ستون درآمد را اضافه کنید. آن‌وقت می‌توان گفت افت صابون چقدر از درآمد را کم کرده.
4. دلیل افت صابون تهران را بررسی کنید (قیمت، موجودی، تبلیغ یا رقیب) و در صورت داشتن داده، فروش به تفکیک مشتری را ببینید.
5. فروش آذر سال قبل را برای مقایسه و برنامه‌ریزی موجودی شمع برای یلدا کنار این روند بگذارید.