الاكسل يتعامل مع الخلايا المدمجة كأنها خلية واحدة و قد لا يبدو أن هناك فائدة لحساب عدد الخلايا المدمجة بعددها الاصلي قبل الدمج، ولكن من خلال شرحي التالي سأوضح لكم فائدتها و لعلكم تستفيدون أيضا.
#وصف المشكلة:-
كنت اعمل على ملف إكسل لحساب الكميات الموضح في الصورة
و استعنت بدالة COUNT الموضحة في الصورة اعلاه لكتابة وحدة القياس (عدد او م او م² او م³) تلقائيا بناءً على عدد الخلايا التي تحتوي على أرقام في الاعمدة D:G لكل صف على حدة.
في بعض الأحيان ربما امتلك قياس جاهز بوحدة المساحة (م²) و عندها سأقوم بدمج خليتين معا او قياس جاهز بوحدة الحجم (م³) و سأدمج 3 خلايا معا، و هنا المشكلة، الاكسل سيتعامل مع هذه الخلايا المدمجة كأنها خلية واحدة و عند دمج خليتي و كتابة قيمة المساحة (التي قيمتها م²) سيظهر الأكسل الوحدة (م) و هذا خطأ و إذا اضفت رقم ثالث (ضرب المساحة * البُعد الثالث) الوحدة هي (م³) للحجم و لكل الاكسل سيظهر الوحدة م² لانه يحسب الخليتان المدمجتان كخلية واحدة. و نفس المشكلة عند دمج ال3 خانات و كنابة قيمة الحجم يظهر الاكسل الوحدة م و ليس م³
#خطوات حل المشكلة بالإستعانة بالذكاء الاصطناعي:
طلبت من الذكاء الإصطناعي حل لهذه المشكلة فاقترح لي كود VBA يقوم بتوليد دالة جديدة
-الضغط على (alt + f11) لفتح VBA
-اختيار insert > module
و في هذه النافذة سنكتب أوامر تأليف الدوال، يمكن كتابة كل الدوال في نفس النافذة (نفس الوحدة النمطية module).
1.كود يحسب عدد الخلايا في النطاق بغض النظر عن الدمج:
Function CountMergedCells(rng As Range) As Integer
Dim cell As Range
Dim count As Integer
count = 0
For Each cell In rng
count = count + cell.MergeArea.Cells.count
Next cell
CountMergedCells = count
End Function
و إستدعاء الدالة بكتابة اسمها في شريط الصيغة:
CountMergedCells(range)
حيث range هو نطاق الدالة مثل A2:A5
و هذه الدالة لم تحل مشكلتي و لا أظنها عملية لأنها تحسب عدد كل الخلايا في النطاق بغض النظر عن إذا تم إضافة رقم او كلمة او كانت فارغة (ربما تكون مفيدة لشخص اخر في تطبيق عملي مختلف)
2. كود تأليف دالة تحسب الخلايا التي بها قيم فقط (سواء رقم او نص او رمز) و تجاهل الخلايا الفارغة:
نجحت هذه الدالة التي تم تأليفها و طلبت منه اوامر تقوم بتأليف دوال مشابه للدوال COUNT و COUNTA و COUNTBLANK فكان رده كالتالي:
أ. دالة CountNumbersInMergedCells تقابل COUNT:
هذه الدالة تحسب عدد الخلايا التي تحتوي على أرقام فقط، مع مراعاة عدد الخلايا المدمجة:
Function CountNumbersInMergedCells(rng As Range) As Integer
Dim cell As Range
Dim count As Integer
count = 0
For Each cell In rng
If cell.Address = cell.MergeArea.Cells(1, 1).Address Then
If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
count = count + cell.MergeArea.Cells.count
End If
End If
Next cell
CountNumbersInMergedCells = count
End Function
إسم الدالة لكتابتها في شريط الصيغة:
CountNumbersInMergedCells
ب. دالة CountAllInMergedCells تقابل COUNTA
هذه الدالة ستحسب عدد الخلايا التي تحتوي على أي قيمة (بما في ذلك الأرقام والنصوص وغيرها) و تجاهل الخلايا الفارغة مع مراعاة عدد الخلايا المدمجة:
As Integer
Dim cell As Range
Dim count As Integer
count = 0
For Each cell In rng
If cell.Address = cell.MergeArea.Cells(1, 1).Address Then
If Not IsEmpty(cell.Value) Then
count = count + cell.MergeArea.Cells.count
End If
End If
Next cell
CountAllInMergedCells = count
End Function
إسم الدالة لكتابتها في شريط الصيغة:
CountAllInMergedCells
ج. دالة CountBlankInMergedCells تقابل COUNTBLANK
هذه الدالة ستحسب عدد الخلايا الفارغة فقط مع مراعاة عدد الخلايا المدمجة:
Function CountBlankInMergedCells(rng As Range) As Integer
Dim cell As Range
Dim count As Integer
count = 0
For Each cell In rng
If cell.Address = cell.MergeArea.Cells(1, 1).Address Then
If IsEmpty(cell.Value) Then
count = count + cell.MergeArea.Cells.count
End If
End If
Next cell
CountBlankInMergedCells = count
End Function
إسم الدالة لكتابتها في شريط الصيغة:
CountBlankInMergedCells
#تجربة الدوال و التأكد من عملها بصورة صحيحة:
هذه الدوال تعمل بصورة ممتازة و تكتب الناتج تلقائيا و بسرعة مثل اي دوال اخرى في اكسل و عند اجراء اي تعديل يتم حساب الناتج الجديد، و لأن الفرق بين هذه الدوال الجديدة و الدوال الموجودة مسبقا في اكسل هو التعامل مع الخلايا المدمجة فلابد من تجربة دمج الخلايا و إلغاء دمجها و التأكد من عملها جيدا، عندما اقوم أولا بدمج الخلايا التي تحتاج لدمج ثم اكتب القيم في الخلايا يظهر الناتج بصورة صحيحة و بدون مشاكل.
و لكن المشكلة هي عند كتابة القيم أولا ثم دمج خلية تحتوي على قيمة مع خلية خالية (أو اكثر) مجاورة لها لا يتم حساب الناتج تلقائيا، و أيضا عند كتابة القيمة أولا في خلية مدمجة ثم إلغاء الدمج لا يتم تحديث الناتج تلقائيا. اتوقع ان سبب هذه المشكلة هو أن الإكسل مبرمج على تحديث الناتج بعد كتابة القيم و الضغط على Enter و لم يتم برمجته لتحديث الناتج بعد دمج الخلايا أو إلغاء دمجها لأن كل دوال إكسل لا يتأثر ناتجها بالدمج و إلغاءه.
*حلول هذه المشكلة:
طلبت من الذكاء الإصطناعي حل هذه المشكلة بتحديث الناتج تلقائيا بعد دمج الخلايا و إلغاء دمجها. على مدار يوم كامل و أنا أعمل على هذه المشكلة مع الذكاء الإصطناعي و إقترح لي المئات من أكواد ماكرو (VBA) و كلها لم تنجح إلا كودان كانا اقرب لحل المشكلة:
الكود الأول: كان يقوم بتحديث النتائج تلقائيا بعد الدمج و إلغاءه و لكنه يستمر في العمل بدون توقف و يظهر الخطأ #value! مكان الناتج و يصاب الأكسل بتهنج و يغلق من تلقاء نفسه.
الكود الثاني كان يقوم بتحديث الناتج فقط عند دمج الخلايا و عند إلغاء دمجها لا يحدث الناتج.
فكانت الحلول الناجحة (لا تعمل تلقائيا بعد الدمج و إلغاء) هي:
1. دمج الخلايا أو إلغاء دمجها اولا ثم إضافة القيم ثانيا.
2. عند حدوث العكس بكنابة القيم اولا ثم احتجنا لدمج الخلايا أو إلغاء دمجها فيجب إعادة كتابة القيمة او كتابة اي قيمة اخرى او حذفها داخل نفس نطاق الدالة.
3. إضافة زر ماكرو يقوم بعملية إعادة الحساب لكامل ورقة الإكسل و كود الماكرو هو:
Sub RecalculateMergedCells()
Application.CalculateFull
End Sub
و يجب وضعه في وحدة نمطية (module) جديدة مستقلة عند السابقة التي فيها الدوال التي تم توليدها.
و يمكن عمل إختصار من لوحة المفاتيح لهذا الماكرو.
و هذا الحل قد يكون مفيد في حالة البيانات الكبيرة التي يصعب مراقبة ناتجها إذا كان صحيحا أم لا.
#آراءكم و مقترحاتكم و تجاربكم:
انتظركم بأن تبدو لي رأيكم في هذه الدوال التي تم توليدها بإستخدام VBA، واذكروا أمثلة أخرى يمكن الإستفادة فيها من هذه الدوال.
وأيضا جربوا هذه الدوال و اذكرو لي ملاحظاتكم و المشاكل التي واجهتموها و هل واجهتكم نفس مشكلتي أم لا؟
و أيضا انتظر منكم مقترحات حول حلول اخرى للمشكلة التي واجهتني.
──────
مع تحياتي أحمد فتحي.
