تحلیل داده های فروش در اکسل

**امين اسماعيلي** · 2014/03/10, 03:32

با درود
نمیدونم ماین موضوع قبلا کجا جواب داده شده بوده اما خوب
راه حل مثلا بیشترین مقدار فرمول

کد:

=MAX(IF(B:B=B2,I:I))

به صورت ارایه ای برای مثلا کالای 1 تو ستونa هستش ( کپی و سپس Ctr+Shift+enter ) . بعدش با کمک از توابع جستجو یعنی جستجو همین مورد که پیدا شد به دیگر خواسته های خودتون میتونین برسین. اما چون حجم داده ها زیاده اول باید یه لیست منحصر به فرد از کالاها (1و 2 .........) یعنی یکبار . ایجاد کنین بعد این مورد رو اعمال کنین که را حت میتونین درگ کنین تا پایین . لیست منحصر به فرد قبلا انجام شده . ماکزیمم شرطی هم همینطور و index یا vlook up هم که توابع جستجو هستند نیز به دفعات

**alieddy0** · 2014/03/10, 10:21

با سلام
جهت آشنايي با نحوه ايجاد ليست بدون تكرار با فرمول آرايه اي كه آقاي اسماعيلي فرمودن ميتونيد از لينك زير هم ايده بگيريد

لينك

**امين اسماعيلي** · 2014/03/10, 10:49

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

فایل های پیوست شده

sells_file.xlsm (80.8 کیلو بایت, مشاهدات 26)

**hamidrezasadeghi** · 2014/03/10, 12:49

سلام استاد اسماعیلی
ممنون از پاسخ سریع تان. راستش راه حل ارائه شده ، اشکال داره. در قسمت اول ، ماکزیمم "سود" را برای "نام کالا" ، به درستی می یابد ولی قسمت دوم ، "زمان بیشترین سود" مربوط به همان کالا را اشتباه می یابد. چونکه در فرمول Match ، ماکزیمم "سود" یافته شده در قبل را در تمام ستون "سود" جستجو می کند و اولین مقدار مساوی با آن را بازمیگرداند. در حجم دیتایی به این گستردگی و تنوع ، این عدد لزوما ممکن است مربوط به کالای مربوطه نباشد. لذا فرمول Match و به تبع آن Index باید ماکزیمم "سود" یافته شده در قبل را فقط در آن ناحیه ایی از ستون "سود" (ستون I) جستجو کند که مربوط به همان کالا باشد. مثلا فایل جدید پیوست را ملاحظه فرمایید. برای کالای2 "زمان بیشترین سود" ساعت 20:51:00 است در حالیکه اشتباها ساعت 9

24 یافته میشود.

sells_new

**امين اسماعيلي** · 2014/03/10, 13:01

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

**امين اسماعيلي** · 2014/03/10, 13:15

با درود
این. چک کن. من ستون a رو واسه کمکی به کار بردم. البته راه های دیگه ای هم هست

فایل های پیوست شده

sells_file.xlsm (91.1 کیلو بایت, مشاهدات 16)

**امين اسماعيلي** · 2014/03/10, 13:31

با درود مجدد
اینم بدون ستون کمکی. با فرمول ارایه ای یعنی index با match چند شرطی. با ارایه ای بودت دقت کن. ببین درست عمل میکنه

فایل های پیوست شده

sells_file2.xlsm (81.3 کیلو بایت, مشاهدات 20)

**hamidrezasadeghi** · 2014/03/10, 13:50

معرکه ایی استاد اسماعیلی. ممنون. اگه ممکنه ، کنجکاو شدم راه حل های دیگه ایی که فرموده بودید را هم بدانم. در ضمن کاربرد دستور زیر در چه مواردی است؟
Application.ScreenUpdating = False

**امين اسماعيلي** · 2014/03/10, 13:59

اسکرول رو در مواقعی که دستورات vba داره اجرا میشه خاموش میکنه . واسه افزایش در اجرای ماکرومون. البته میشه تمامی event ها رو غیر فعال و بعد در اخر فعال کرد.خوب به دو طریق این قسمت انجام شد . هر چند میشه به صورت کامل ماکروییش کرد اما خوب فرمول ها اگه جوابگو هستند چه کاریه:D ضمنا فایل هاتونو دوست عزیز تو همین سایت اپلود کن اگه ممکنه

به صورت ارایه ای اینطوری هم میشه مثلا برای تایم

کد:

=IF(J2="","",INDEX(A:A,MATCH(J2&K2,B:B&I:I,0)))

و برلی
حد اقل فی فروش

کد:

=IF(J2="","",INDEX(G:G,MATCH(J2&K2,B:B&I:I,0)))

پ

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

**hamidrezasadeghi** · 2014/03/10, 15:24

ممنون استاد اسماعیلی. اگه ممکنه عملکرد match در آرایه ایی که ارائه فرموده بودین را توضیح دهید. در ضمن فایل دوم تون (بدون ستون کمکی) سرعت را پایین می آورد. با توجه به حجم زیاد اطلاعات، آیا برای افزایش سرعت اجرا در اینگونه فایل ها ایده ایی دارین؟ مجددا از سرعت پاسخگویی و عملکرد ماهرانه تان سپاسگزارم.

**hamidrezasadeghi** · 2014/03/10, 15:26

منظورم دستور match(1,(i:i=k2)*(b:b=j2),0) است.

**hamidrezasadeghi** · 2014/03/10, 16:33

استاد اسماعیلی
از آنجاییکه این عملیات را میخواهم روی حدود 30 فایل انجام دهم و نتایج را استخراج کنم، ماکروی آن به چه صورت خواهد بود؟ میتوانم به کمک ماکرو، فایل ها را باز کنم و عملیات اندکی که عموما استفاده از توابع اکسل است را بنویسم. اما نمیدانم قسمت آرایه ایی را چگونه بنویسم. چگونه به وسیله VBA با نوشتن فرمول در سلولی میتوان Ctrl+Shift+Enter را که به مفهوم آرایه است به اکسل فهماند؟

**امين اسماعيلي** · 2014/03/10, 20:20

تو قسمت vba خودمون دستور داریم و همچنین کلک زدنم میتونه به کار بیاد . مثلا داریم

کد:

activecell.FormulaArray=

در مورد اینکه این کار ها همش با vba انجام بشه دیگه با کمک خودته . تو دوره ها که برگذار میشه خوب توضیح میدن دوستان و همچنین قسمت پروزه سایت واسه مباحث پروژه ای

**hamidrezasadeghi** · 2014/03/10, 20:58

سلام دوست عزیز
از اینکه حس زیبای همکاری و کمک کردن به دیگران را دارید ، همچنین از بابت کمک هاتون به من ، سپاسگزارم. در ضمن
1- با چه دستوری میتوان نام فایلی که اکتیو و باز است را در متغیری ذخیره کرد؟
2-با چه دستوری میتوان در فایلی که باز کرده ایم اگر کاربرگی با نام info وجود داشت ، آنرا حذف نماید؟
3- با چه دستوری میتوان هر یک از 3 فایلی که باز است را بسته به نیاز ، فراخوانی کرد؟