كيف تُنشئ نطاقًا مسمى ديناميكيًا في Excel؟
عادةً ما تكونالنطاقات المسماةمفيدة جدًّا لمستخدمي Excel، إذ يمكنك تحديد مجموعة من القيم في عمود، وإعطائها اسمًا، ثم الإشارة إليها لاحقًا باستخدام هذا الاسم بدلًا من مراجع الخلايا. لكن في معظم الأحيان، ستحتاج إلى إضافة بيانات جديدة لتوسيع قيم النطاق مستقبلاً. وفي هذه الحالة، يتعيّن عليك العودة إلىالصيغ > مدير الأسماءلإعادة تعريف النطاق ليشمل القيم الجديدة. ولتجنب هذه الخطوة المتكررة، يمكنك إنشاء نطاق مسمى ديناميكي—ما يعني أنك لن تحتاج أبدًا إلى تعديل مراجع الخلايا كلما أضفت صفًّا أو عمودًا جديدًا إلى قائمتك.
إنشاء نطاق مسمى ديناميكي في Excel بإنشاء جدول
إنشاء نطاق مسمى ديناميكي في Excel باستخدام دالة
إنشاء نطاق مسمى ديناميكي في Excel باستخدام كود VBA
إنشاء نطاق مسمى ديناميكي في Excel بإنشاء جدول
إذا كنت تستخدم Excel 2007 أو إصدارًا أحدث، فإن أسهل طريقة لإنشاء نطاق مسمى ديناميكي هي تحويل بياناتك إلى جدول Excel مسمى.
لنفترض أن لديك نطاقًا من البيانات التالي، ويجب أن يتحول إلى نطاق مسمى ديناميكي.
1. أولًا، سأقوم بتعريف اسم للنطاق. حدد النطاق A1:A6، ثم أدخل الاسمDate فيمربع الاسمواضغط مفتاحEnter. وبالمثل، عيّن اسمSaleprice للنطاق B1:B6 باستخدام الطريقة نفسها. وفي الوقت نفسه، أنشأت صيغة=sum(Saleprice) في خلية فارغة، كما في لقطة الشاشة التالية:
2. حدد النطاق، ثم انقر علىإدراج > جدول. انظر لقطة الشاشة:
3. في مربع الحوارإنشاء جدول، حدد الخياريحتوي جدولي على رؤوس (إذا لم يكن للنطاق رؤوس، فألغِ تحديده)، ثم انقر على زرموافق، وسيتم تحويل بيانات النطاق إلى جدول. انظر لقطات الشاشة:
 |  |  |
4. بعد إدخال قيم جديدة بعد البيانات، سيتكيف النطاق المسمى تلقائيًا، وستُحدَّث الصيغة التي تم إنشاؤها أيضًا. انظر لقطات الشاشة التالية:
 | |  |
ملاحظات:
1. يجب أن تكون البيانات الجديدة التي تُدخلها مجاورة للبيانات السابقة، دون أي صفوف أو أعمدة فارغة تفصل بينها وبين البيانات الموجودة.
2. في الجدول، يمكنك إدراج بيانات بين القيم الموجودة.
إنشاء نطاق مسمى ديناميكي في Excel باستخدام دالة
في Excel 2003 أو الإصدارات الأقدم، لن تكون الطريقة الأولى متاحة، لذا إليك طريقة بديلة. يمكن للدالة التاليةOFFSET( ) أن تنجز هذه المهمة لك، لكنها تتطلب بعض الجهد. لنفترض أن لديك نطاقًا من البيانات يحتوي على أسماء خلايا قمت بتحديدها؛ فمثلًا، يكون اسم النطاقA1:A6 هوDate، واسم النطاقB1:B6 هوSaleprice. وفي الوقت نفسه، أنشأت صيغة لـSaleprice. انظر لقطة الشاشة:
يمكنك تحويل اسم الخلية إلى اسم الخلية ديناميكي باتباع الخطوات التالية:
1. اذهب وانقر علىالصيغ > مدير الأسماء، كما في لقطة الشاشة:
2. في مربع الحوارمدير الأسماء، حدد العنصر الذي تريد استخدامه، ثم انقر على زرتعديل.
3. في مربع الحوار المنبثقتحرير الاسم، أدخل الصيغة التالية=OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1) في مربع النصيشير إلى. انظر لقطة الشاشة:
4. ثم انقر علىموافق، وكرّر الخطوة 2 والخطوة 3 لنسخ هذه الصيغة=OFFSET(Sheet1!$B$1, 0, 0, COUNTA($B:$B)، 1) في مربع النصيشير إلىالخاص باسم الخليةSaleprice.
5. وهكذا يتم إنشاء النطاقات المسماة الديناميكية! فبمجرد إدخال قيم جديدة بعد البيانات، يتكيف النطاق المسمى تلقائيًا، وتتغيّر الصيغة التي تم إنشاؤها أيضًا. انظر لقطات الشاشة:
| | |
ملاحظة:إذا كانت هناك خلايا فارغة في منتصف نطاقك، فستكون نتيجة صيغتك غير دقيقة. ذلك لأن الخلايا غير الفارغة فقط هي التي يتم احتسابها، مما يجعل نطاقك أقصر مما ينبغي، وبالتالي تُستبعد الخلايا الأخيرة في النطاق.
تلميح: شرح لهذه الصيغة:
- =OFFSET(مرجع،صفوف،أعمدة،[الارتفاع]،[العرض])
- =OFFSET(Sheet1!$A$1, 0, 0, COUNTA($A:$A), 1)
- مرجعيتوافق مع موضع الخلية الابتدائية، في هذا المثالSheet1!$A$1؛
- الصفيشير إلى عدد الصفوف التي ستنتقلها لأسفل من الخلية الابتدائية (أو لأعلى إذا استخدمت قيمة سالبة). في هذا المثال، يشير 0 إلى أن القائمة ستبدأ من الصف الأول لأسفل.
- العموديتوافق مع عدد الأعمدة التي تنتقلها إلى اليمين من الخلية الابتدائية (أو إلى اليسار إذا استخدمت قيمة سالبة). في الصيغة المذكورة أعلاه، يشير الرقم 0 إلى عدم التوسع بأي أعمدة إلى اليمين.
- [الارتفاع]يتوافق مع ارتفاع (أو عدد الصفوف) للنطاق بدءًا من الموضع المعدّل $A:$A، حيث سيتم احتساب جميع العناصر المدخلة في العمود A.
- [العرض]يتوافق مع عرض النطاق (أو عدد أعمدته) بدءًا من الموضع المعدَّل. في الصيغة أعلاه، ستكون القائمة بعرض عمود واحد.
يمكنك تعديل هذه المعاملات وفقًا لاحتياجاتك.
إنشاء نطاق مسمى ديناميكي في Excel باستخدام كود VBA
إذا كان لديك أعمدة متعددة، يمكنك تكرار وإدخال صيغة منفصلة لكل عمود متبقي، لكن ذلك سيكون عملية طويلة ومتكررة. ولتسهيل الأمور، يمكنك استخدام كود لإنشاء النطاق المسمى الديناميكي تلقائيًا.
1. فعِّل ورقة العمل الخاصة بك.
2. اضغط مع الاستمرار على مفاتيحALT + F11، وسيفتح ذلك نافذةمايكروسوفت Visual Basic for Applications.
3. انقر علىإدراج > وحدة نمطية، ثم الصق الكود التالي فينافذة الوحدة النمطية.
كود VBA: إنشاء نطاق مسمى ديناميكي
Sub CreateNamesxx()
'Update 20131128
Dim wb As Workbook, ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long
Dim myName As String, Start As String
Const Rowno = 1
Const Colno = 1
Const Offset = 1
On Error Resume Next
Set wb = ActiveWorkbook
Set ws = ActiveSheet
lcol = ws.Cells(Rowno, 1).End(xlToRight).Column
lrow = ws.Cells(Rows.Count, Colno).End(xlUp).Row
Start = Cells(Rowno, Colno).Address
wb.Names.Add Name:="lcol", RefersTo:="=COUNTA($" & Rowno & ":$" & Rowno & ")"
wb.Names.Add Name:="lrow", RefersToR1C1:="=COUNTA(C" & Colno & ")"
wb.Names.Add Name:="myData", RefersTo:="=" & Start & ":INDEX($1:$65536," & "lrow," & "Lcol)"
For i = Colno To lcol
myName = Replace(Cells(Rowno, i).Value, " ", "_")
If myName <> "" Then
wb.Names.Add Name:=myName, RefersToR1C1:="=R" & Rowno + Offset & "C" & i & ":INDEX(C" & i & ",lrow)"
End If
Next
End Sub
4. ثم اضغط على مفتاحF5 لتشغيل الكود، وسيتم إنشاء نطاقات مسماة ديناميكية تحمل أسماءً مستمدة من قيم الصف الأول، بالإضافة إلى نطاق ديناميكي يُسمىMyData يغطي جميع البيانات.
5. عند إدخال قيم جديدة بعد الصفوف أو الأعمدة، سيتم توسيع النطاق تلقائيًا. انظر لقطات الشاشة:
 |
 |
 |
ملاحظات:
1. مع هذا الكود، لا يظهر اسم الخلية فيمربع الاسم. ولعرض اسم الخلية واستخدامه بسهولة، قمت بتثبيتKutools لـ Excel، حيث تُعرض النطاقات الديناميكية التي تم إنشاؤها عبر ميزةالتنقلالخاصة به.
2. باستخدام هذا الكود، يمكنك توسيع نطاق البيانات بالكامل عموديًّا أو أفقيًّا، ولكن تذكّر ألا تترك أي صفوف فارغة أو أعمدة بين البيانات عند إدخال قيم جديدة.
3. عند استخدام هذا الكود، يجب أن يبدأ نطاق البيانات من الخلية A1.
مقال ذو صلة:
كيفية تحديث مخطط تلقائيًا بعد إدخال بيانات جديدة في Excel؟
أفضل أدوات الإنتاجية لمكتبتك
عزِّز مهاراتك في Excel باستخدام Kutools لـ Excel، وعايش الكفاءة كما لم تفعل من قبل.يقدّم Kutools لـ Excel أكثر من 300 ميزة متقدمة لتعزيز الإنتاجية ووقت الحفظ.انقر هنا للحصول على الميزة التي تحتاجها أكثر من غيرها...
يجلب Office Tab واجهة ذات علامات تبويب إلى Office، ويجعل عملك أسهل بكثير
- تمكّن من التحرير والقراءة باستخدام علامات التبويب في Word وExcel وPowerPoint، وPublisher وAccess وVisio وProject.
- افتح وأنشئ مستندات متعددة في علامات تبويب جديدة داخل النافذة نفسها، بدلاً من فتح نوافذ جديدة.
- يزيد إنتاجيتك بنسبة 50% ويوفّر لك مئات نقرات الفأرة كل يوم!
جميع الإضافات من Kutools في برنامج تثبيت واحد!
Kutools for Office حزمةٌ تحتوي على إضافاتٍ مخصصة لتطبيقات Excel وWord وOutlook وPowerPoint، إلى جانب Office Tab Pro، مما يجعلها الخيار المثالي للفِرق التي تعمل عبر تطبيقات Office.
- حزمة شاملة واحدة— إضافات Excel وWord وOutlook وPowerPoint بالإضافة إلى Office Tab Pro
- برنامج تثبيت واحد، ترخيص واحد— الإعداد خلال دقائق (جاهز لـ MSI)
- يعمل بشكل أفضل معًا— إنتاجية ميسَّرة عبر تطبيقات Office
- تجربة مجانية لمدة 30 يومًا بكامل الميزات— بدون تسجيل، بدون بطاقة ائتمان
- أفضل قيمة— وفِّر مقارنةً بشراء الإضافات بشكل منفصل