يحتوي Excel على العديد من الوظائف المفيدة ، لكن ما زلنا لا نملك وظيفة لـ الجمع في Excel بشرط لون الخلية. ومع ذلك ، يمكن حل هذه الحاجة بمساعدة الميزات والإمكانيات الأخرى. في هذه المقالة ، سوف ندرس كيفية إضافة خلايا بلون معين في Excel معًا.
كما قلنا ، قد لا يتمتع Excel بقدرة منفصلة على جمع الخلايا بشرط لون ، ولكنه يحتوي على وظائف وصيغ مفيدة ومفيدة للغاية للتلخيص.
الإضافة في Excel بشرط لون الخلية بمساعدة عامل التصفية و SUBTOTAL
في بقية هذه المقالة ، سنرى طرقًا مختلفة لحساب الخلايا الملونة في Excel. الطريقة التي سنختبرها في هذه الخطوة هي أبسط حل. على سبيل المثال ، نرى جدولًا في الصورة أدناه يحتوي على أسماء العديد من الموظفين وأرقام مبيعاتهم.
نريد حساب مجموع كل الخلايا التي تحولت إلى اللون الأصفر والبرتقالي. على الرغم من أنه ليس لدينا أي وظيفة مضمنة لتلخيصها في Excel مع حالة لون الخلية ، إلا أن هناك حلًا بسيطًا لهذه المهمة. يسمح لنا Excel بتصفية الخلايا حسب لونها ، وبهذه الطريقة يمكننا إضافة خلايا بلون معين معًا.
لهذا الغرض ، ندخل الصيغة التالية في خلية النتيجة:
SUBTOTAL(9,B2:B11)=
في هذه الصيغة ، أدخلنا الرقم 9 بدلاً من الوسيطة الأولى. يخبر هذا الرقم دالة Subtotal بان نريد استخدام دالة SUM لجمع البيانات المحددة. كما ترى ، لم نكتب وظيفة SUM مباشرةً ، لأنه عندما نقوم بتصفية الخلايا حسب شرط معين (في هذا المثال ، اللون) ، تُستخدم وظيفة Subtotal لتجميع عامل التصفية في Excel وجمع الخلايا المرئية فقط . بينما ستحسب الدالة SUM مجموع كل الخلايا.
الآن بعد أن كتبنا دالة Subtotal ، نتخذ الخطوات التالية لحساب المجموع بناءً على حالة اللون:
- نختار خلية على صفحتنا وننتقل إلى علامة التبويب Data.
2. ثم ، في قسم Sort & Filter، انقر على أيقونة Filter بهذه الطريقة يمكننا تصفية بياناتنا.
3. لتطبيق الفلتر ، نضغط على أيقونة الفلتر في رأس عمود “المبيعات”.
4. ثم نضغط على خيار Filter By Color ونختار الألوان المطلوبة.
بمجرد القيام بذلك ، تتغير نتيجة دالة Subtotal لتظهر فقط مجموع الخلايا المرئية (في هذا المثال ، الخلايا الملونة) ويتم تنفيذ المجموع في Excel مع حالة لون الخلية.
وبالمثل ، إذا قمت بتصفية العمود باللون البرتقالي ، فستتغير النتيجة أيضًا وتظهر مجموع الخلايا البرتقالية. تجدر الإشارة إلى أن مفاتيح الاختصار لتطبيق مرشح على مجموعة بيانات هي Ctrl + Shift + L في Windows و Command + Shift + L على Mac.
ترميز VBA للإضافة في Excel مع حالة لون الخلية
قلنا أنه لا توجد وظيفة داخلية خاصة للإضافة مع حالة اللون في Excel ، ولكن يمكننا إنشاء وظيفتنا الخاصة في هذا البرنامج. بمساعدة وحدات ماكرو VBA في Excel ، يمكننا إنشاء وظيفة مخصصة مصممة لأغراض محددة ويمكن استخدامها مثل وظائف Excel الأخرى.
للقيام بذلك ، نتبع الخطوات التالية:
- أولاً ، اضغط على Alt + F11 على لوحة المفاتيح للدخول إلى بيئة برمجة VBA. ثم ، في النافذة الجديدة ، انقر بزر الماوس الأيمن على “الورقة” (Sheet) المطلوبة وحدد Insert ثم Module.
ثم اكتب الكود التالي في المربع المفتوح:
Function SumByColor(SumRange As Range, SumColor As Range)
Dim SumColorValue As Integer
Dim TotalSum As Long
SumColorValue = SumColor.Interior.ColorIndex
Set rCell = SumRange
For Each rCell In SumRange
If rCell.Interior.ColorIndex = SumColorValue Then
TotalSum = TotalSum + rCell.Value
End If
Next rCell
SumByColor = TotalSum
End Function
أخيرًا ، نغلق نافذة VBA ويمكننا استخدام هذه الوظيفة لحساب المجموع في Excel مع حالة لون الخلية. لاستخدام هذه الوظيفة ، نحتاج إلى خلية تحتوي على اللون المطلوب لإضافته.
في المثال أدناه ، استخدمنا خليتين D2 و D3 لعرض الألوان المطلوبة. اللون الأصفر في الخلية D2 واللون البرتقالي في الخلية D3. نكتب الصيغة التالية في هذه الخلايا:
SumByColor($B$2:$B$11,D2)=
تحتوي هذه الصيغة على وسيطين على النحو التالي:
- نطاق من الخلايا يحتوي على اللون المطلوب.
- قم بالإشارة إلى الخلية التي تحتوي على اللون المطلوب حتى تتمكن الصيغة من الحصول على فهرس اللون واستخدامه كشرط لجمع القيم.
لاحظ أن هذه الوظيفة ديناميكية وسيتم تحديثها إذا تغيرت الأرقام الملونة (مثل رفع و خفض القيم أو إضافتها أو إزالتها). ومع ذلك ، إذا لم يتم تحديث الصيغة بشكل صحيح في بعض الحالات ، يمكنك الضغط على المفتاح F9 على لوحة المفاتيح لتحديثها.
أيضًا ، نظرًا لأننا استخدمنا بعض رموز VBA في هذا المستند ، فنحن بحاجة إلى حفظه كمستند قادر على الماكرو (بامتداد XLSM.). إذا كنت تتعامل عادةً مع جمع الخلايا الملونة في Excel ، فمن الأفضل نسخ ولصق رمز VBA هذا في ورقة العمل الخاصة بك كصيغة مخصصة بحيث تكون متاحة دائمًا.
استخدم GET.CELL لتجميع الخلايا بشرط لون
تتضمن الطريقة الأخيرة التي نراها للجمع في Excel بشرط لون الخلية صيغة Excel سرية لا يعرفها الجميع. في هذه الطريقة ، نستخدم GET.CELL ، والذي يعطينا قيمة المنسب اللونی للخلايا الملونة.
بهذه الطريقة ، بمجرد حصولنا على قيمة الفهرس ، نحتاج فقط إلى استخدام دالة Sum بسيطة للحصول على مجموع خلايا لون معين. بالطبع ، هذه الطريقة ليست مثالية مثل كود VBA الذي رأيناه. ولكن إذا كنت لا ترغب في العمل مع VBA ، فهو بديل جيد.
لاحظ أن GET.CELL هي وظيفة Macro 4 قديمة لا تزال تُستخدم لتجنب كسر توافق Excel مع الملفات القديمة. ولكن نظرًا لأنه نادرًا ما يتم استخدامه ، فلا يوجد الكثير من التفاصيل عنه.
في المثال أدناه ، لدينا مجموعة بيانات بها بعض الخلايا الملونة.
لكي تعمل هذه الطريقة ، يجب علينا أولاً إنشاء نطاق مسمى يستخدم الدالة GET.CELL لإرجاع قيمة اللون لخلية. للقيام بذلك ، ننتقل إلى الخطوات التالية:
- أدخل علامة التبويب Formula. ثم ، في قسم Defined Names ، انقر فوق خيار Name Manager.
ثم ، في المربع الجديد ، نضغط على خيار New.
في الخطوة التالية ، نكتب اسم SumColor في مربع Name.
نملأ المربع Refers to بالصيغة GET.CELL(38,$B2)=
- أخيرًا ، انقر فوق Ok وأغلق مربع Name Manager.
من خلال القيام بذلك ، قمنا بإنشاء نطاق مسمى يمكننا استخدامه على صفحتنا. تحتوي الدالة GET.CELL على وسيطتين. الأول هو رقم يخبر الدالة بالمعلومات التي نحتاجها ، والثاني هو مرجع الخلية نفسها. في هذا المثال ، استخدمنا الرقم 38 كوسيطة أولى ، والتي تُرجع قيمة فهرس اللون للخلية المشار إليها.
الخطوة الثانية هي الحصول على قيمة مؤشر اللون لجميع الألوان في العمود B أو “المبيعات”. للقيام بذلك ، نتبع الخطوات أدناه:
أولاً ، نقوم بإنشاء عمود جديد ونطلق عليه اسم “مؤشر اللون”.
ثم نكتب الصيغة التالية في الخلية C2:
SumColor=
- في الخطوة التالية ، بمساعدة Fill Handle ، نقوم بتوسيع الصيغة إلى جميع الخلايا الموجودة في العمود C.
بهذه الطريقة ، نصل إلى الأرقام التي هي مؤشرات الالوان في هذه الخلايا. SumColor هو النطاق المسمى الذي أنشأناه والذي يساعدنا في الحصول على قيمة فهرس اللون للخلية الموجودة على اليسار. يمكنك استخدام هذه الوظيفة في أي خلية أو عمود ، ولكن تبدأ دائمًا من الخلية الثانية. على سبيل المثال ، يمكنك كتابة نفس الصيغة في العمودين I أو H ، ولكن يجب أن تبدأ من الخلية الثانية.
الآن بعد أن أصبح لدينا قيمة الفهرس الفريدة لكل لون ، يمكننا استخدامها لحساب المجموع في Excel مع حالة اللون. نقوم بالخطوات التالية للقيام بذلك:
- نقوم بإدخال الألوان المطلوبة في الخلايا E2 و E3. في هذا المثال ، أدخلنا لونين ، أصفر وبرتقالي.
ثم نكتب الصيغة التالية في الخلية F2:
SUMIF (C2: C15، SumColor، B2: B15)=
ثم نقوم بنسخ هذه الخلية ولصقها في الخلية F3 لتطبيق الصيغة عليها.
تحسب هذه الصيغة مجموع الخلايا ذات اللون المطلوب. في هذا القسم ، استخدمنا وظيفة Sumif في Excel لإجراء مجموع شرطي. بمساعدة هذه الوظيفة ، يتم تحديد وتجميع الخلايا من عمود “المبيعات” ، والتي تكون قيمة مؤشر الألوان الخاصة بها مماثلة لعمود “مؤشر اللون”.
لاحظ أنه على الرغم من أن الصيغة ديناميكية ، إذا قمت بإجراء تغييرات على الخلايا (مثل تغيير عدد الخلايا الملونة أو حذفها) ، فإن النتيجة النهائية لا تتغير على الفور. الحل البديل هو الانتقال إلى الخلية التي تحتوي على الصيغة والضغط على F2 على لوحة المفاتيح ، ثم الضغط على Enter. سيؤدي هذا إلى إعادة حساب الصيغة حتى يتم تحديث النتيجة.
إجابات على الأسئلة الشائعة
في ما يلي ، نرى بعض الأسئلة الشائعة حول الإضافة في Excel مع حالة اللون مع إجاباتها.
هل من الممكن استخدام دالة Sumif لتجميع الخلايا الملونة في Excel؟
نحن نعلم أن دالة Sumif هي مزيج من وظائف Sum و If في Excel. لهذا السبب ، باستخدام الشرط المناسب ، يمكن استخدام هذه الوظيفة لتجميع الخلايا التي لها لون معين. ولكن للقيام بذلك ، هناك حاجة أيضًا إلى خلفيات أخرى ، والتي شرحناها في هذه المقالة.
كيفية إضافة خلايا مرئية في Excel؟
أحد استخدامات دالة Subtotal هو جمع الخلايا المرئية بعد تصفية العمود. يمكن لهذه الوظيفة تطبيق عمليات مختلفة مثل الجمع والتوسيط والعثور على الأكبر والأصغر وما إلى ذلك ، على الخلايا المرئية في الجدول. بهذه الطريقة ، يمكننا أيضًا إضافة خلايا بلون معين بمساعدة هذه الوظيفة.
كلمة أخيرة
في هذه المقالة ، قمنا بفحص ثلاث طرق إضافة لـ الجمع في Excel بشرط لون الخلية. على الرغم من أن العمل باستخدام وظيفة Subtotal يعد أمرًا بسيطًا وسريعًا ، إلا أنه إذا كنت تحتاج عادةً إلى جمع خلايا بلون معين ، فمن الأفضل استخدام كود VBA وإنشاء وظيفتك الخاصة. بهذه الطريقة ، فقط أدخل اسم الوظيفة في خلية النتيجة وحدد وسيطاتها. أخيرًا ، قدمنا طريقة ثالثة تعد بديلاً جيدًا إذا كنت لا ترغب في العمل مع VBA. نأمل أن تكون قادرًا على أداء المبلغ المطلوب على بياناتك بمساعدة هذه الطرق.
This article is useful for me
1+ 3 People like this post
