إنشاء قائمة منسدلة في الاكسل (التحقق من صحة البيانات)

لإنشاء قائمة منسدلة في الاكسل، حدد الخلايا، واذهب إلى بيانات > التحقق من صحة البيانات، واضبط السماح على قائمة، واكتب العناصر في المصدر مفصولة بفواصل (North,South,East,West) أو حدد النطاق الذي يحتوي عليها، واضغط موافق. تعرض كل خلية الآن سهمًا بهذه الاختيارات، ويُرفض أي إدخال آخر.

الصيغإعادة الضبط

E2fx

	A	B	C	D	E	F
1	Rep	Region	Sales		Region	Sales
2	Ana	North▾	120		North▾NorthSouthEastWest	360
3	Ben	South▾	85
4	Cara	North▾	240
5	Dan	East▾	60
6	Eve	South▾	150

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

تعرض F2 القيمة 360، مجموع North. اختر North في B3 فيزداد F2 بمقدار مبيعات Ben البالغة 85. وفي E2 قائمة منسدلة أيضًا: اختر South هناك فتعرض F2 مجموع South بدلًا من ذلك. قائمة للإدخال وصيغة تقرؤها هما الاستخدام الأشيع للقائمة المنسدلة.

كيف تنشئ قائمة منسدلة خطوة خطوة

1. حدد الخلايا التي يجب أن تحصل على القائمة، مثلًا B2:B6.
2. اذهب إلى بيانات > التحقق من صحة البيانات (مجموعة أدوات البيانات). وعلى Windows تسلسل المفاتيح هو Alt ثم A ثم V ثم V.
3. في علامة التبويب إعدادات، اضبط السماح على قائمة.
4. في المصدر، إما أن تكتب العناصر مفصولة بفواصل، North,South,East,West، أو تنقر في المربع وتحدد النطاق الذي فيه العناصر على الورقة، فيُكتب =$F$2:$F$5.
5. أبقِ قائمة منسدلة في الخلية محددًا (بدونه لا يوجد سهم، بل الفحص فقط).
6. اضغط موافق.

لفتح القائمة من لوحة المفاتيح، حدد الخلية واضغط Alt+السهم لأسفل (Windows) أو Option+السهم لأسفل (Mac). وفي Excel لـ Microsoft 365، تضيّق كتابة الأحرف الأولى في الخلية القائمة إلى العناصر المطابقة.

وفي مربع الحوار نفسه علامتا تبويب اختياريتان: رسالة الإدخال تعرض تلميحًا عند تحديد الخلية، وتنبيه إلى خطأ يحدد ما يحدث عندما يكتب أحدهم قيمة ليست في القائمة. مع النمط إيقاف (الافتراضي) يُرفض الإدخال؛ ومع تحذير أو معلومات يُسمح به بعد رسالة. ألغِ تحديد إظهار تنبيه إلى خطأ بعد إدخال بيانات غير صالحة لتسمح للناس بكتابة أي شيء مع إبقاء القائمة معروضة.

تُفصل العناصر المكتوبة بفاصل القوائم في الإعدادات الإقليمية للحاسوب. وفي معظم الإعدادات الإقليمية التي تستخدم الفاصلة العشرية (فرنسا وألمانيا وإسبانيا وإيطاليا)، يكون ذلك فاصلة منقوطة: Nord;Sud;Est;Ouest.

قائمة منسدلة من نطاق خلايا

القائمة المكتوبة في مربع الحوار مخفية عن النظر ويجب تعديلها هناك. أما القائمة الموجودة في خلايا فأسهل صيانة: غيّر خلية فتتغير كل قائمة منسدلة تستخدمها. هنا المناطق في E2:E5 وتستخدم القائمة المنسدلة على B2:B6 ذلك النطاق مصدرًا لها.

الصيغإعادة الضبط

B2fx

	A	B	C	D	E
1	Rep	Region	Sales		Regions
2	Ana	North▾NorthSouthEastWest	120		North
3	Ben	South▾	85		South
4	Cara	North▾	240		East
5	Dan	East▾	60		West
6	Eve	South▾	150

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

غيّر E5 من West إلى Central، ثم افتح أي سهم في العمود B: تعرض القائمة Central بدلًا من West. ولا تتغير القيم المختارة سابقًا في العمود B.

لاستخدام نطاق في ورقة أخرى، وهي الطريقة المعتادة لإبقاء القوائم بعيدة عن النظر، اكتب اسم الورقة في المصدر: =Lists!$A$2:$A$5. ولكي تكبر القائمة عندما تضيف عنصرًا في الأسفل، حوّل العناصر إلى جدول أولًا (حددها، ثم إدراج > جدول) ثم حدد عمود الجدول مصدرًا: يتوسع المرجع مع الجدول.

قائمة منسدلة ديناميكية بالدالة UNIQUE

عندما يجب أن تأتي العناصر من البيانات نفسها (كل منطقة تظهر في عمود، مرة واحدة لكل منها)، ابنِ القائمة بصيغة ووجّه القائمة المنسدلة إلى النتيجة. تمتد =SORT(UNIQUE(B2:B8)) في G2 بالمناطق المميزة بترتيب أبجدي.

الصيغإعادة الضبط

E2fx

	A	B	C	D	E	F	G
1	Rep	Region	Sales		Pick	Sales	Regions
2	Ana	North	120		South▾EastNorthSouthWest	235	East
3	Ben	South	85				North
4	Cara	North	240				South
5	Dan	East	60				West
6	Eve	South	150
7	Fay	West	95
8	Gus	East	110

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

تمتد G2 بالقيم East وNorth وSouth وWest، وتعرض القائمة المنسدلة في E2 هذه الأربع. غيّر B7 إلى Central فتظهر Central في الامتداد وفي القائمة معًا.

في الاكسل، اضبط مصدر القائمة المنسدلة على =$G$2#. يعني # بعد خلية "كل امتداد هذه الصيغة"، فتكون القائمة دائمًا بطول النتيجة تمامًا، دون صفوف فارغة في آخرها. ويحتاج مرجع الامتداد إلى Excel 365 أو 2021؛ ويمكن أن تكون خلية المصدر في ورقة أخرى (=Lists!$A$2#). وإذا كان في عمود البيانات خلايا فارغة، تعيد UNIQUE القيمة 0 لها؛ استبعدها بالصيغة =SORT(UNIQUE(FILTER(B2:B100,B2:B100<>""))). وتشرح صفحة UNIQUE الدالة بالتفصيل.

القوائم المنسدلة التابعة

تتغير القائمة التابعة مع الاختيار في خلية أخرى: اختر Fruit في A2، فلا تعرض B2 إلا الفواكه. وفي Excel 365 و2021، تبني صيغة FILTER القائمة الثانية: تعيد =FILTER(E2:E8,D2:D8=A2) العناصر التي تطابق فئتها A2، وتستخدم القائمة المنسدلة في B2 ذلك الامتداد مصدرًا.

الصيغإعادة الضبط

A2fx

	A	B	C	D	E	F	G
1	Category	Item		Category	Item		Items
2	Fruit▾FruitVegetableBakery	Pear▾		Fruit	Apple		Apple
3				Fruit	Pear		Pear
4				Vegetable	Carrot		Kiwi
5				Vegetable	Leek
6				Bakery	Bread
7				Fruit	Kiwi
8				Bakery	Bagel

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

مع Fruit في A2، تمتد G2 بالقيم Apple وPear وKiwi، وهي اختيارات B2. اختر Bakery في A2: تتغير G2 إلى Bread وBagel. وتظل B2 تقول Pear حتى تختار من جديد، لأن القائمة المنسدلة لا تغيّر أبدًا قيمة موجودة بالفعل في الخلية. وفي الاكسل يكون مصدر B2 هو =$G$2#.

في إصدارات الاكسل الأقدم، تستخدم الطريقة التقليدية INDIRECT والنطاقات المسمّاة:

1. ضع عناصر كل فئة في عمود خاص بها، مع اسم الفئة كعنوان: Fruit في عمود، وVegetable في العمود التالي.
2. حدد كل عمود عناصر وسمّه باسم فئته في مربع الاسم (يسار شريط الصيغة): Fruit وVegetable وBakery.
3. أعطِ A2 قائمة منسدلة مصدرها Fruit,Vegetable,Bakery.
4. أعطِ B2 قائمة منسدلة مصدرها =INDIRECT(A2). تحوّل INDIRECT النص في A2 إلى مرجع للنطاق الذي يحمل ذلك الاسم.

يجب أن تطابق الأسماء نص الفئة تمامًا ولا يمكن أن تحتوي على مسافات (استخدم Dairy_Products، أو =INDIRECT(SUBSTITUTE(A2," ","_")) في المصدر). والمزيد عن INDIRECT في صفحة INDIRECT.

البحث عن قيمة العنصر المختار

كثيرًا ما تكون القائمة المنسدلة مُدخل نموذج طلب أو عرض سعر: يختار المستخدم منتجًا فيملأ بحث سعره.

الصيغإعادة الضبط

B2fx

	A	B	C	D	E	F
1		Product	Price		Product	Price
2	Order	Pear▾ApplePearCarrotBreadMilk			Apple	$1.20
3					Pear	$1.50
4					Carrot	$0.80
5					Bread	$2.40
6					Milk	$1.10

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

دورك: أعد في C2 سعر المنتج المختار في B2 من الجدول في E:F.

تحققعرض الإجابة

مع اختيار Pear، تكون الإجابة $1.50. اختر منتجًا آخر في B2 فيتبعه السعر. وتعمل =XLOOKUP(B2,E2:E6,F2:F6) أيضًا؛ راجع VLOOKUP لمعرفة الوسائط.

تلوين خلية حسب العنصر المختار

لتلوين الخلية حسب ما اختير (أخضر لـ Done، وأحمر لـ Late)، أضف قاعدة تنسيق شرطي إلى الخلايا نفسها: حدد B2:B6، واذهب إلى الصفحة الرئيسية > التنسيق الشرطي > قواعد تمييز الخلايا > يساوي، واكتب Late واختر تنسيقًا. ولتلوين الصف كله، حدد A2:B6 واستخدم قاعدة جديدة > استخدام صيغة لتحديد الخلايا التي سيتم تنسيقها مع =$B2="Late".

الصيغإعادة الضبط

B3fx

	A	B
1	Task	Status
2	Quote	Done▾
3	Invoice	Late▾OpenDoneLate
4	Order	Open▾
5	Report	Late▾
6	Survey	Done▾

تمييز B2:B6 عندما

انقر على خلية لترى صيغتها. غيّر رقمًا أو صيغة وستعيد الورقة الحساب.

تُميَّز B3 وB5. اختر Late في B4 فتُميَّز أيضًا؛ واختر Done في B3 فيزول التمييز. وتشرح صفحة التنسيق الشرطي القواعد بالتفصيل.

لماذا لا تعمل القائمة المنسدلة

• قائمة منسدلة في الخلية غير محدد في بيانات > التحقق من صحة البيانات. لا تزال القائمة تقيّد الإدخالات، لكن لا يوجد سهم.
• لا يظهر السهم إلا على الخلية المحددة. ولا شيء في الشبكة يميّز الخلايا الأخرى التي فيها قائمة؛ ولإيجادها، استخدم الصفحة الرئيسية > بحث وتحديد > التحقق من صحة البيانات.
• في نطاق المصدر خلايا فارغة، فتعرض القائمة أسطرًا فارغة. حدد الخلايا الممتلئة فقط، أو استخدم مصدرًا ممتدًا (=$G$2#)، لا فراغات فيه.
• عناصر مكتوبة بفاصل خاطئ: تصبح North;South في اكسل يستخدم الفواصل عنصرًا واحدًا اسمه North;South.
• القائمة المنسدلة تحمل قيمة واحدة. اختيار عنصر ثانٍ يستبدل الأول؛ واختيار عدة عناصر في خلية واحدة يحتاج إلى ماكرو VBA.

لنسخ قائمة منسدلة إلى خلايا أخرى دون نسخ القيمة، انسخ الخلية، ثم استخدم الصفحة الرئيسية > لصق > لصق خاص > التحقق من الصحة. ولحذف واحدة، حدد الخلايا واختر بيانات > التحقق من صحة البيانات > مسح الكل.