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

[~M*E*H*D*I~]

با درود

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

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

شرح سوال:

واحد فروش محصولات شرکت مولتی کافه میزان فروش 3 نمایندگی خود را به شهرهای مختلف کشور از ابتدای سال 2020 تا پایان ژانویه 2021در شیت Database وارد کرده است.

کارشناس این واحد قصد دارد داشبوردی طراحی کند که بدون توجه به سال، مجموع فروش یک محصول توسط یکی از نمایندگی‌ها را به یکی از شهرهای مقصد به دست آورد (به عنوان مثال کل میزان فروش محصول A1 در ماه ژانویه سالهای 2020 و 2021 به شهر اردبیل توسط نمایندگی شماره 3)

او قصد دارد با استفاده از ابزار Pivot Table این کار را انجام دهد اما میخواهد به نحو داینامیک این کار را انجام دهد تا توسط لیست های فروریز و تغییر مقادیر مقدار فروش را به دست آورد. او برای این کار گامهای زیر را طی می کند:
1- در شیت REF ستونهای A تا D مقادیر منحصر به فرد Pcode،sales representative، Des City با استفاده از فرمول به دست می‌آورد به نحوی که در صورت هر گونه تغییر اطلاعات در شیت دیتابیس این مقادیر نیز به روز آوری شوند. همچنین در ستون Month اعداد 1 تا 12 را وارد میکند
2- در شیت Respond در سلولهای D6تاG6 با استفاده از ابزار Datavalidation list منوهای فروریز برای هر یک از پارامترهای Pcode، sales representative، Des City، Month در جدول شماره 1 ایجاد می نماید به نحوی که در صورت اضافه شدن به مقادیر هر کدام از متغیرها بدون نیاز به تنظیمات دوباره منوی فروریز به روزآوری شود.
3- سپس در شیت Pivot با استفاده از اطلاعات شیت Database یک Pivot Table ایجاد میکند و بر اساس مقادیری که میخواهد به دست آورد تنظیمات لازم را انجام می‌دهد (منظور این است که برای بدست آوردن مقادیر تعیین شده در جدول شماره 2 در قسمت ROWS,VALUES,COLMNS از Pivot table چه پارامترهایی را باید اضافه کرد)

4- پس از تنظیمات لازم در سلولهایی از جدول 2 که نیاز به استفاده از مقادیر Pivot Table وجود دارد با استفاده از تابع GETPIVOTDATA مقادیر مورد نیاز را استخراج و بر اساس پارامترهای جدول شماره 1 این تابع را داینامیک می نماید.


با توجه به پیچیدگی سوال مراحل گام به گام توضیح داده شده و سعی شده سوال شفافیت لازم را داشته باشد با این حال هرگونه ابهام در طرح سوال را در این تاپیک سوال بفرمایید.(برای فهم بهتر سوال فیلم کوتاهی نیز پیوست شده است)

موفق باشید.

[mehrdad1985]

وقت بخیر،
در نمونه ارسال شده برای "جدول شماره 2"، عدد 231 برای Sales Number درج شده، در حالیکه چنین عددی در شیت Database وجود ندارد

[vafa.hasti]

سلام خسته نباشید
پیوت تیبل هم باید جدولی شفاف باشه؟ یا همین که اطلاعات رو توی شیت هدف داینامیک کنه کفایت میکنه؟

[~M*E*H*D*I~]

وقت بخیر،
در نمونه ارسال شده برای "جدول شماره 2"، عدد 231 برای Sales Number درج شده، در حالیکه چنین عددی در شیت Database وجود ندارد
مگر لزوما باید باشه؟
شما دارید مجموع ماه رو میشمارید چه در 2020 چه 2021

[~M*E*H*D*I~]

سلام خسته نباشید
پیوت تیبل هم باید جدولی شفاف باشه؟ یا همین که اطلاعات رو توی شیت هدف داینامیک کنه کفایت میکنه؟

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

[~M*E*H*D*I~]

دوستان در ایجاد Pivot Table ظرافتهای لازم رو رعایت کنید که امتیاز از دست ندید. چیز اضافی استفاده نکنید در این که چه پارامتری رو کجا قرار بدید دقت داشته باشید

[nocy]

با سلام،
برای این سوال می‌شه از macro استفاده کنیم؟

[saman319]

سلام , امکانش هست برای Pivote Table سوال دوم هم یک عکس راهنما اضافه کنید تا شمای کلی مدنظر شما ، مشخص بشه ؟

[mokaram]

با سلام،
برای این سوال می‌شه از macro استفاده کنیم؟
سلام دوست عزیز به انجمن اکسل ایران خوش آمید :wcom:
متاسفانه همونطوری که در فایل سوال هم ذکر شده استفاده از vba ممنوع هست.

[Ali Jourian]

وقت بخیر،
در نمونه ارسال شده برای "جدول شماره 2"، عدد 231 برای Sales Number درج شده، در حالیکه چنین عددی در شیت Database وجود ندارد

سلام
با اجازه ادمین محترم
مجموع Sale Number برای کد محصول انتخابی و فروشنده انتخابی و در ماه انتخابی (بطور مثال ژانویه - بدون در نظر گرفتن سال) باید مد نظر قرار گیرد ، و همچنین Total Sale

[Dastgerdi]

سلام و عرض ارادت

سپاس و خداقوت

برای سوال دوم مربوط به منوهای فروریز و جدول شماره 1

آیا امکان استفاده از شیت REF در Data validation list وجود دارد؟

برای سوال Pivot لطفا یک نمونه قرار بدید.

ممنون

[~M*E*H*D*I~]

سلام و عرض ارادت

سپاس و خداقوت

برای سوال دوم مربوط به منوهای فروریز و جدول شماره 1

آیا امکان استفاده از شیت REF در Data validation list وجود دارد؟

برای سوال Pivot لطفا یک نمونه قرار بدید.

ممنون

اصلا کاربرد شیت Ref برای ایجاد لیستهای دیتاولیدیشن هست ، صورت سوال رو دقیق مطالعه کنید
برای پیوت تیبل امکان ارائه نمونه نیست چون جزو سوال هست و باید در کاربرد پیوت تیبل مهارت داشته باشید گرچه بسیار هم ساده است

[msgladiator]

سلام
امکان تبدیل شیت Database به Table وجود داره؟
و استفاده از Name های Table در لیست و بقیه موارد؟

[~M*E*H*D*I~]

سلام
امکان تبدیل شیت Database به Table وجود داره؟
و استفاده از Name های Table در لیست و بقیه موارد؟

بله قطعا

[~M*E*H*D*I~]

راهنمایی1: برای تنظیمات پیوت تیبل ستون Date رو به قسمت ROWS اضافه کنید پس از اضافه شدن تاریخ در ردیفها بر روی یکی از مقادیر تاریخ راست کلیک کنید در پنجره باز شده Group رو باز کنید و غیر از Month سایر موارد رو از حالت انتخاب خارج و OK بزنید.
راهنمایی2: در دو قسمت FILTERS و COLUMNS هیچ پارامتری نیاز به اضافه شدن نیست
راهنمایی3: در جدول 2 شیت Respond قاعدتا امکان استخراج متن با استفاده از GETPIVOTDATA نیست لذا برای استخراج نام محصول با استفاده از کد محصول از Vlookup استفاده کنید
راهنمایی4: ستون Product هیچ نقشی در پیوت تیبل ندارد

[z.maghsoudi]

سلام
میشه تو فرم مربوطه مسابقات چندین فایل را آپلود کرد؟
ظاهرا 1 فایل را بیشتر قبول نمیکند چون به سه روش پاسخ دادم

[iranlover]

پاسخ سوال دوم رو در آخرین لحظات ارسال کردم. با آرزوی موفقیت برای همه دوستان.

[mohammad_ashooryan]

سلام
من هم دقایق پایانی پاسخ رو ارسال کردم:d
22287

[iranlover]

راهنمایی1: برای تنظیمات پیوت تیبل ستون Date رو به قسمت ROWS اضافه کنید پس از اضافه شدن تاریخ در ردیفها بر روی یکی از مقادیر تاریخ راست کلیک کنید در پنجره باز شده Group رو باز کنید و غیر از Month سایر موارد رو از حالت انتخاب خارج و OK بزنید.
راهنمایی2: در دو قسمت FILTERS و COLUMNS هیچ پارامتری نیاز به اضافه شدن نیست
راهنمایی3: در جدول 2 شیت Respond قاعدتا امکان استخراج متن با استفاده از GETPIVOTDATA نیست لذا برای استخراج نام محصول با استفاده از کد محصول از Vlookup استفاده کنید
راهنمایی4: ستون Product هیچ نقشی در پیوت تیبل ندارد



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

[rasools13]

سلام آقا مهدی عزیز،، ببخشید ولی اگر مسابقه اس دیگه راهنمایی کردن برای چیه!!!! :d اگر مهم نیستند به نظرتون بهتر نیست اصلا جزو موارد پاسخدهی نیان...من نگران اینم کسایی که زودتر جواب دادن و طبق این راهنمایی عمل نکردن.در ارزشیابی پاسخ ها ازشون امتیاز کسر بشه:confused:

- - - Updated - - -


سلام
میشه تو فرم مربوطه مسابقات چندین فایل را آپلود کرد؟
ظاهرا 1 فایل را بیشتر قبول نمیکند چون به سه روش پاسخ دادم

سلام خانم مقصودی
سه تا فایل رو زیپ zip کنید بعد ارسال نمایید

[~M*E*H*D*I~]

سلام آقا مهدی عزیز،، ببخشید ولی اگر مسابقه اس دیگه راهنمایی کردن برای چیه!!!! :d اگر مهم نیستند به نظرتون بهتر نیست اصلا جزو موارد پاسخدهی نیان...من نگران اینم کسایی که زودتر جواب دادن و طبق این راهنمایی عمل نکردن.در ارزشیابی پاسخ ها ازشون امتیاز کسر بشه:confused:

- - - Updated - - -



سلام خانم مقصودی
سه تا فایل رو زیپ zip کنید بعد ارسال نمایید

بنده صرفا سوال طرح میکنم سیاستگذاری با بنده نیست ، شخصا اگر صفر تا صد موضوع رو بخوام انجام بدم کیفیت و نکات یک سوال برام در اولویت قرار داره نه تعداد شرکت کنندگان

بابت اون نگرانی هم زمانی که یک بخش سوال برای افزایش مشارکت افشا میشه قاعدتا از بخش امتیاز دهی هم حذف میشه

[~M*E*H*D*I~]

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

من اصولا اهل راهنمایی کردن نیستم بخشی هم که راهنمایی کردم از امتیاز دهی حذف خواهد شد
سیاست گذاری با من نیست
اگر با من بود در مجموع تعداد شرکت کننده از انگشتان یک دست تجاوز نمیکرد در نهایت هم شاید یک نفر برنده می شد;)

[z.maghsoudi]

- - - Updated - - -



سلام خانم مقصودی
سه تا فایل رو زیپ zip کنید بعد ارسال نمایید

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

[iranlover]

من اصولا اهل راهنمایی کردن نیستم بخشی هم که راهنمایی کردم از امتیاز دهی حذف خواهد شد
سیاست گذاری با من نیست
اگر با من بود در مجموع تعداد شرکت کننده از انگشتان یک دست تجاوز نمیکرد در نهایت هم شاید یک نفر برنده می شد;)

;);););) قطعا امتیاز کامل رو که نمیگیرفتم، گفتم شاید نفرات کمتری جواب درست کامل رو داده باشم که از غافله جا نمونیم.:)

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

خودم به شخصه تو همین دوتا سوال کلی ریزه کاری یاد گرفتم دوباره. به سهم خودم تشکر میکنم ازتون

[moh_taheri]

با سلام
در جدول شماره 2 شيت response نام Product را نمي توان از پايوت استخراج نمود و لازمه آن استفاده از ابزارهاي ديگر است. ايا امكان استفاده از پاور كوئري و قراردادن نتياج در شيت را داريم؟
ممنون

[~M*E*H*D*I~]

با سلام
در جدول شماره 2 شيت response نام Product را نمي توان از پايوت استخراج نمود و لازمه آن استفاده از ابزارهاي ديگر است. ايا امكان استفاده از پاور كوئري و قراردادن نتياج در شيت را داريم؟
ممنون


اینقدر پیچیده نیست با یک فرمول ساده جستجو کنید و بدست بیارید شما کد محصول رو دارید