فهم خلايا Excel مقابل وظائف النطاق في VBA

فهم خلايا Excel مقابل وظائف النطاق في VBA

برنامج Excel قوي. إذا كنت تستخدمه كثيرًا ، فمن المحتمل أنك تعرف بالفعل الكثير من الحيل باستخدام الصيغ أو التنسيق التلقائي ، ولكن مع الاستفادة من الخلايا و نطاق وظائف في VBA ، يمكنك تعزيز تحليلات Excel إلى مستوى جديد تمامًا.





تكمن مشكلة استخدام وظائف الخلايا والنطاق في VBA في أنه في المستويات المتقدمة ، يواجه معظم الأشخاص صعوبة في فهم كيفية عمل هذه الوظائف بالفعل. يمكن أن يصبح استخدامها مربكًا للغاية. إليك كيف يمكنك الاستفادة منها بطرق ربما لم تتخيلها أبدًا.



وظيفة الخلايا

تتيح لك وظائف الخلايا والنطاق معرفة ذلك البرنامج النصي VBA الخاص بك بالضبط أين تريد الحصول على البيانات في ورقة العمل الخاصة بك أو ضعها. الفرق الرئيسي بين الخليتين هو ما تشير إليه.





الخلايا عادةً ما يشير إلى خلية واحدة في كل مرة ، بينما نطاق تشير إلى مجموعة من الخلايا في وقت واحد. تنسيق هذه الوظيفة هو خلايا (صف ، عمود) .

يشير هذا إلى كل خلية مفردة في الورقة بأكملها. إنه المثال الوحيد الذي لا تشير فيه وظيفة الخلايا إلى خلية واحدة:



Worksheets('Sheet1').Cells

يشير هذا إلى الخلية الثالثة من اليسار ، من الصف العلوي. الخلية C1:

Worksheets('Sheet1').Cells(3)

تشير التعليمات البرمجية التالية إلى الخلية D15:



Worksheets('Sheet1').Cells(15,4)

إذا كنت ترغب في ذلك ، يمكنك أيضًا الإشارة إلى الخلية D15 باستخدام 'الخلايا (15 ،' D ')' - يُسمح لك باستخدام حرف العمود.

هناك قدر كبير من المرونة في القدرة على الرجوع إلى خلية باستخدام رقم للعمود والخلية ، خاصة مع البرامج النصية التي يمكنها ذلك حلقة من خلال عدد كبير من الخلايا (وإجراء الحسابات عليها) بسرعة كبيرة. سنصل إلى ذلك بمزيد من التفصيل أدناه.



وظيفة المدى

من نواحٍ عديدة ، تعد وظيفة النطاق أقوى بكثير من استخدام الخلايا ، لأنها تتيح لك الرجوع إلى خلية واحدة ، أو نطاق معين من الخلايا ، مرة واحدة. لن ترغب في إجراء تكرار عبر دالة النطاق ، لأن مراجع الخلايا ليست أرقامًا (إلا إذا قمت بتضمين وظيفة الخلايا بداخلها).

تنسيق هذه الوظيفة هو النطاق (الخلية رقم 1 ، الخلية رقم 2) . يمكن تحديد كل خلية برقم حرف.

لنلقِ نظرة على بعض الأمثلة.

إعادة تعيين كلمة مرور شبكة بلاي ستيشن لا يعمل

هنا ، تشير وظيفة النطاق إلى الخلية A5:

Worksheets('Sheet1').Range('A5')

هنا ، تشير وظيفة النطاق إلى جميع الخلايا بين A1 إلى E20:

Worksheets('Sheet1').Range('A1:E20')

كما هو مذكور أعلاه ، ليس عليك استخدام تعيينات الخلايا المكونة من رقم. يمكنك بالفعل استخدام وظيفتين للخلايا داخل دالة النطاق لتحديد نطاق على الورقة ، مثل هذا:


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

يشير الرمز أعلاه إلى نفس النطاق مثل وظيفة Range ('A1: E20'). القيمة في استخدامه ، هي أنه سيسمح لك بكتابة التعليمات البرمجية التي تعمل ديناميكيًا مع النطاقات باستخدام الحلقات.

الآن بعد أن فهمت كيفية تنسيق وظائف الخلايا والنطاق ، دعنا نتعمق في كيفية الاستفادة الإبداعية من هذه الوظائف في كود VBA الخاص بك.

معالجة البيانات بوظيفة الخلايا

تكون وظيفة الخلايا أكثر فائدة عندما يكون لديك صيغة معقدة تريد تنفيذها عبر نطاقات متعددة من الخلايا. يمكن أن توجد هذه النطاقات أيضًا عبر أوراق متعددة.

لنأخذ مثال بسيط. لنفترض أنك تدير فريق مبيعات مكون من 11 شخصًا ، وتريد أن تلقي نظرة كل شهر على أدائهم.

قد يكون لديك الورقة 1 الذي يتتبع عدد مبيعاتهم وحجم مبيعاتهم.

تشغيل Sheet2 هو المكان الذي تتبع فيه تقييم تعليقاتهم على مدار الثلاثين يومًا الماضية من عملاء شركتك.

إذا كنت تريد حساب المكافأة في الورقة الأولى باستخدام قيم من الورقتين ، فهناك طرق متعددة للقيام بذلك. يمكنك كتابة صيغة في الخلية الأولى لإجراء الحساب باستخدام البيانات عبر الصفحتين وسحبها لأسفل. سوف يعمل هذا.

البديل لذلك هو إنشاء برنامج VBA النصي الذي تقوم بتشغيله عندما تفتح الورقة ، أو يتم تشغيله بواسطة زر أمر في الورقة حتى تتمكن من التحكم في وقت الحساب. يمكنك استخدام برنامج نصي لـ VBA لسحب جميع بيانات المبيعات من ملف خارجي على أي حال.

فلماذا لا يتم فقط تشغيل الحسابات لعمود المكافأة في نفس البرنامج النصي في ذلك الوقت؟

وظيفة الخلايا في العمل

إذا لم تكتب VBA في Excel من قبل ، فستحتاج إلى تمكين عنصر قائمة Developer. للقيام بذلك ، انتقل إلى ملف > خيارات . انقر فوق تخصيص الشريط . أخيرًا ، اختر Developer من الجزء الأيمن ، يضيف إلى الجزء الأيمن ، وتأكد من تحديد خانة الاختيار.

الآن ، عند النقر فوق نعم والعودة إلى الورقة الرئيسية ، سترى خيار قائمة Developer.

يمكنك استعمال ال إدراج القائمة لإدراج زر أمر ، أو انقر فقط مشاهدة الكود لبدء الترميز.

في هذا المثال ، سنقوم بتشغيل البرنامج النصي في كل مرة يتم فيها فتح المصنف. للقيام بذلك ، فقط انقر فوق مشاهدة الكود من قائمة المطور ، والصق الوظيفة الجديدة التالية في نافذة التعليمات البرمجية.

Private Sub Workbook_Open()
End Sub

ستبدو نافذة التعليمات البرمجية الخاصة بك مثل هذا.

أنت الآن جاهز لكتابة الرمز للتعامل مع الحساب. باستخدام حلقة واحدة ، يمكنك التنقل بين جميع الموظفين البالغ عددهم 11 موظفًا ، وباستخدام وظيفة الخلايا ، اسحب المتغيرات الثلاثة اللازمة للحساب.

تذكر أن وظيفة الخلايا تحتوي على صف وعمود كمعلمات لتحديد كل خلية على حدة. سنجعل 'x' الصف ، ونستخدم رقمًا لطلب بيانات كل عمود. عدد الصفوف هو عدد الموظفين ، لذلك سيكون من 1 إلى 11. سيكون معرّف العمود 2 لعدد المبيعات ، و 3 لحجم المبيعات ، و 2 من الورقة 2 للحصول على نقاط الملاحظات.

يستخدم الحساب النهائي النسب المئوية التالية لإضافة ما يصل إلى 100 بالمائة من إجمالي نقاط المكافأة. يعتمد على عدد المبيعات المثالي الذي يبلغ 50 ، وحجم المبيعات 50000 دولار ، ودرجة ردود الفعل 10.

  • (عدد المبيعات / 50) × 0.4
  • (حجم المبيعات / 50،000) × 0.5
  • (نتيجة التقييم / 10) × 0.1

يمنح هذا النهج البسيط موظفي المبيعات مكافأة مرجحة. بالنسبة للعدد 50 ، والحجم 50000 دولار ، والنتيجة 10 - يحصلون على الحد الأقصى الكامل لمكافأة الشهر. ومع ذلك ، فإن أي شيء أقل من الكمال على أي عامل يقلل من المكافأة. أي شيء أفضل من المثالي يعزز المكافأة.

الآن دعونا نلقي نظرة على كيفية سحب كل هذا المنطق في نص بسيط للغاية وقصير من VBA:

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

هذا ما سيبدو عليه إخراج هذا البرنامج النصي.

ماذا يمكنك أن تفعل مع التوت باي

إذا أردت أن يظهر عمود البونص المكافأة بالدولار الفعلي بدلاً من النسبة المئوية ، فيمكنك ضربها في الحد الأقصى لمبلغ المكافأة. والأفضل من ذلك ، ضع هذا المقدار في خلية على ورقة أخرى ، وقم بالإشارة إليه في التعليمات البرمجية الخاصة بك. سيؤدي ذلك إلى تسهيل تغيير القيمة لاحقًا دون الحاجة إلى تعديل التعليمات البرمجية الخاصة بك.

يكمن جمال وظيفة الخلايا في أنه يمكنك بناء منطق إبداعي لسحب البيانات منه العديد من الخلايا عبر العديد من الأوراق المختلفة ، وأداء بعض حسابات معقدة للغاية معهم.

يمكنك تنفيذ جميع أنواع الإجراءات على الخلايا باستخدام وظيفة الخلايا - أشياء مثل مسح الخلايا وتغيير تنسيق الخط وغير ذلك الكثير.

لاستكشاف كل ما يمكنك القيام به بشكل أكبر ، تحقق من صفحة Microsoft MSDN لكائن الخلايا.

تنسيق الخلايا مع وظيفة النطاق

للتكرار عبر العديد من الخلايا واحدة تلو الأخرى ، فإن وظيفة الخلايا مثالية. ولكن إذا كنت تريد تطبيق شيء ما على نطاق كامل من الخلايا مرة واحدة ، فإن وظيفة النطاق تكون أكثر كفاءة.

قد تكون إحدى حالات الاستخدام لذلك تنسيق نطاق من الخلايا باستخدام البرنامج النصي ، إذا تم استيفاء شروط معينة.

على سبيل المثال ، لنفترض أنه إذا كان إجمالي حجم المبيعات لجميع موظفي المبيعات يتجاوز 400000 دولار في المجموع ، فأنت تريد تمييز جميع الخلايا في عمود المكافأة باللون الأخضر للإشارة إلى أن الفريق قد حصل على مكافأة إضافية للفريق.

دعنا نلقي نظرة على كيفية القيام بذلك باستخدام بيان IF .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

عند تشغيل هذا ، إذا تجاوزت الخلية هدف الفريق ، فسيتم ملء جميع الخلايا في النطاق باللون الأخضر.

هذا مجرد مثال واحد بسيط للعديد من الإجراءات التي يمكنك تنفيذها على مجموعات من الخلايا باستخدام وظيفة النطاق. تشمل الأشياء الأخرى التي يمكنك القيام بها ما يلي:

  • قم بتطبيق مخطط تفصيلي حول المجموعة
  • تدقيق إملائي للنص داخل نطاق من الخلايا
  • مسح الخلايا أو نسخها أو قصها
  • ابحث في نطاق باستخدام طريقة 'البحث'
  • أكثر بكثير

تأكد من قراءة ملف صفحة Microsoft MSDN لكائن Range لمعرفة كل الاحتمالات.

خذ Excel إلى المستوى التالي

الآن بعد أن فهمت الاختلافات بين وظائف الخلايا و Range ، فقد حان الوقت لنقل البرمجة النصية لـ VBA إلى المستوى التالي. ستسمح لك مقالة دان حول استخدام وظائف العد والإضافة في Excel بإنشاء برامج نصية أكثر تقدمًا يمكنها تجميع القيم عبر جميع مجموعات البيانات الخاصة بك بسرعة كبيرة.

وإذا كنت قد بدأت للتو مع VBA في Excel ، فلا تنس أن لدينا رائعة دليل تمهيدي لبرنامج Excel VBA لك أيضا.

يشارك يشارك سقسقة بريد الالكتروني مقارنة بين Canon و Nikon: ما هي ماركة الكاميرا الأفضل؟

Canon و Nikon هما أكبر اسمين في صناعة الكاميرات. ولكن ما هي العلامة التجارية التي تقدم أفضل تشكيلة من الكاميرات والعدسات؟

اقرأ التالي مواضيع ذات صلة
  • إنتاجية
  • برمجة
  • البرمجة المرئية الأساسية
  • مايكروسوفت اكسل
نبذة عن الكاتب ريان دوبي(تم نشر 942 مقالة)

رايان حاصل على درجة البكالوريوس في الهندسة الكهربائية. لقد عمل 13 عامًا في هندسة الأتمتة ، وخمس سنوات في تكنولوجيا المعلومات ، وهو الآن مهندس تطبيقات. مدير تحرير سابق لـ MakeUseOf ، تحدث في المؤتمرات الوطنية حول تصور البيانات وتم عرضه على التلفزيون والإذاعة الوطنية.

المزيد من Ryan Dube

اشترك في نشرتنا الإخبارية

انضم إلى النشرة الإخبارية لدينا للحصول على نصائح تقنية ومراجعات وكتب إلكترونية مجانية وصفقات حصرية!

انقر هنا للاشتراك