شمارش سلولهای شرطی

[amir.habibi]

با عرض سلام و خسته نباشید خدمت دوستان عزیز
من یک فایل اکسل دارم (فایل پیوست) که هر ستون باید توسط conditional formatting-top/botton rules دارای 2 شرط بشه. بطوری که 50% اعداد بزرگ به رنگ سبز در بیان و 50% بقیه اعداد (اعداد کوچکتر) به رنگ قرمز در بیان. تا اینجای کار ساده اس و میشه یصورت تک تک برای هر ستون شرایط رو تعریف کرد.
بعد قرار این سلولهای رنگ با توجه به سلولهای ستون بعذی شمارش بشن. بطوری که سلولی که مثلاً در ستون 11 سبز هست و در ستون 12 قرمز باید بصورت (سبز-قرمز )شمارش بشه و سلولی که در هر دو ستون سبز هست در سلول (سبز-سبز) و ... در آخر باید تعداد سلولهای در یک فرمتی شبیه به این تجمیع بشه
سبز-سبز
سبز-قرمز
قرمز-سبز
قرمز-قرمز
سفید-سبز
سفید-قرمز
سبز-سفید
قرمز-سفید
در ستون هایی که تعداد داده ها فرد هستش حتماً یک سلول نه سبز میشه و نه قرمز- این سلول همون سلول سفید هستش که در بالا نوشتم.
اینکار رو بصورت دستی برای چند ستون انجام دادم.ولی اینکار هم خیلی خیلی زمان بر و سخت هستش. من مطمئنم روش بهتری هم برای انجام اینکار هست که انشالله با کمک شما پیداش میکنیم.
از اینکه وقت و انرژی تون رو صرف مطالعه مساله و کمک به حلش می کنید از شما سپاسگذارم.


excelliran.xlsx

[generalsamad]

با سلام
یه روش مطرح میکنم میتونید به جواب برسید
با استفاده از ابزار Filter و با کمک تابع Subtotal میتونید به جواب برسید
به این صورت عمل کنید که محدوده مورد نظر رو انتخاب کرده سپس فیلتر رو اعمال کنید بعد از فیلتر توی تنظیمات فیلتر ، Filter By Color رو انتخاب و رنگ مورد نظر رو برای هر ستون انتخا کرده
و در سطر آخر داده ها این فرمول رو وارد کنید

=SUBTOTAL(3;CG3:CG103) 

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

روشهای دیگه هم وجود داره که یکی از روشها با استفاده از Vba هست
فایل ضمیمه گردید

[amir.habibi]

با سلام
یه روش مطرح میکنم میتونید به جواب برسید
با استفاده از ابزار Filter و با کمک تابع Subtotal میتونید به جواب برسید
به این صورت عمل کنید که محدوده مورد نظر رو انتخاب کرده سپس فیلتر رو اعمال کنید بعد از فیلتر توی تنظیمات فیلتر ، Filter By Color رو انتخاب و رنگ مورد نظر رو برای هر ستون انتخا کرده
و در سطر آخر داده ها این فرمول رو وارد کنید

=SUBTOTAL(3;CG3:CG103) 

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

روشهای دیگه هم وجود داره که یکی از روشها با استفاده از Vba هست
فایل ضمیمه گردید

سلام دوست عزیز
خیلی ممنون از وقتی که گذاشتین. بسیار سپاسگذارم
روش شما خیلی کمکم کرد. ولی تعداد ستون هایی که قراره با هم مقایسه بشن خیلی زیاده و من این فایل رو بصورت نمونه اینجا قرار دادم. همین کار رو نمیشه بصورت کد نوشت؟ البته من خودم با این روش رکورد ماکرو انجام دادم و جواب هم میده. ولی چون کامل مسلط نیستم نمی تونم بهش بگم که ستون هایی که من بعنوان ستون اول و دوم بهت معرفی میکنم این عملیات رو روش انجام بده. یعنی توی معرفی رفرنس مشکل دارم. مخصوصاً که در بعضی از قسمتها حتی این ستون ها تو یک شیت هم نیستن و ممکنه یک ستون از شیت 1 با ستون دیگه ای از شیت 2 مقایسه بشه.
اگر هنگام run کردن ماکرو از من بخواد ابتدا ستون 1 و سپس ستون 2 رو بهش نشون بدم خیلی خوب میشه. البته طول ستون ها همه با هم برابر است یعنی مثلاًCG3:CG103 فقط این CG باید هر دفعه عوض بشه
ممنون میشم کمکم کنید

[mohammad_ashooryan]

سلام
من براي اينكار از تابع median (براي پيدا كردن ميانه) در كنار تابع sumproduct اينكار رو انجام دادم.
دقت كنيد كه من براي اينكه مثال عينيت بيشتري داشته باشه من با كانديشنال فرمتينگ سلولها رو قرمز و سبز كردم.
و با توجه به فرمول ديگه نيازي به كانديشنال فرمتينگ نيست.
سوالي بود در خدمتم.
فايل نمونه پيوست گرديد.
sample.xlsx

[amir.habibi]

سلام
من براي اينكار از تابع median (براي پيدا كردن ميانه) در كنار تابع sumproduct اينكار رو انجام دادم.
دقت كنيد كه من براي اينكه مثال عينيت بيشتري داشته باشه من با كانديشنال فرمتينگ سلولها رو قرمز و سبز كردم.
و با توجه به فرمول ديگه نيازي به كانديشنال فرمتينگ نيست.
سوالي بود در خدمتم.
فايل نمونه پيوست گرديد.
[ATTACH]8094[/ATTACH]

ممنون از لطفتون
خیلی جالب بود. فقط میشه نحوه تعمیم این فرمول به بقیه ستون ها رو هم بفرمایید؟ مخصوصاً ستون های غیر همجوار و ستون هایی که در یک شیت قرار ندارن.
خیلی لطف میکنید

[mohammad_ashooryan]

براي اينكار از توابع index و match ميتوانيد استفاده كنيد.
من براي اينكه فرمول شلوغ نشه از نامهاي ستون1 و ستون2 استفاده كردم در قسمت name manager ميتوانيد فرمول هاي اون رو ببينيد.
sample2.xlsx

[amir.habibi]

براي اينكار از توابع index و match ميتوانيد استفاده كنيد.
من براي اينكه فرمول شلوغ نشه از نامهاي ستون1 و ستون2 استفاده كردم در قسمت name manager ميتوانيد فرمول هاي اون رو ببينيد.
[ATTACH]8096[/ATTACH]

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

[mohammad_ashooryan]

خواهش ميكنم
اين مورد رو هم درست كردم.
باز هم اگه سوال و مشكلي بود در خدمتم.
sample3.xlsx

[amir.habibi]

خواهش ميكنم
اين مورد رو هم درست كردم.
باز هم اگه سوال و مشكلي بود در خدمتم.
[ATTACH]8102[/ATTACH]

سلام
آقا محمد خیلی ممنون
خیلی لطف کردی نمی دونم با چه زبونی تشکر کنم
اما من حتی نمی تونم این فرمول های شما رو به فایل اصلی خودم منتقل کنم.
روم سیاه میشه اینم برام انجام بدین؟
شیت cf فایل پیوست یکی از فایل های منه. لطف میکنید اگر روی این شیت پیداش کنید

[generalsamad]

با سلام و با اجازه دوست عزیز mohammad2060
Name Manger رو کنترل کنید
به ریبون Formulas رفته Name Manger رو باز کنید ببینید جناب mohammad2060 چه محدوده هایی نام گذاری و فرمول داده

[khakzad]

سلام
آقا محمد خیلی ممنون
خیلی لطف کردی نمی دونم با چه زبونی تشکر کنم
اما من حتی نمی تونم این فرمول های شما رو به فایل اصلی خودم منتقل کنم.
روم سیاه میشه اینم برام انجام بدین؟
شیت cf فایل پیوست یکی از فایل های منه. لطف میکنید اگر روی این شیت پیداش کنید

با اجازه اساتید

دوست عزیز،
سعی کنید فایل رو تحلیل کنید و سوالاتتون رو بپرسید. که بعدا هر تغییری خواستید خودتون بتونید انجام بدید و مستقل بشید (هرچند که ارائه فایل آماده توسط اساتید ما، بسیار راحت تر از آموزش هست، اما باز هم زحمت میکشن و وقت میذارن

موفق باشید

[mohammad_ashooryan]

سلام
آقا محمد خیلی ممنون
خیلی لطف کردی نمی دونم با چه زبونی تشکر کنم
اما من حتی نمی تونم این فرمول های شما رو به فایل اصلی خودم منتقل کنم.
روم سیاه میشه اینم برام انجام بدین؟
شیت cf فایل پیوست یکی از فایل های منه. لطف میکنید اگر روی این شیت پیداش کنید

سلام
فايل رو بررسي كردم.
فرمول براي اعداد منفي درست كار نميكرد و فرمولش رو عوض كردم.
مثلا توي فايل نمونه روبروي سبز-سبز به جاي فرمول اولي از فرمول دومي استفاده كردم.

توي شيت cf هم اعمالش كردم.
فرمول قسمت name manager شما رو هم درست كردم.
1-توي index محدوده رو از ستون e شروع كردم.
2- چون توي index از ستون e شروع كردم. از فرمول match هم 4 تا كم كردم.
sample3 (11).xlsx