استرجاع البيانات في Microsoft Excel

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

تنفيذ العينة

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

الطريقة الأولى: تطبيق عامل تصفية تلقائي متقدم

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

1. حدد المنطقة على الورقة ، والتي تحتاج إلى تحديدها. في علامة التبويب "الصفحة الرئيسية" ، انقر فوق الزر "فرز وتصفية" . يتم وضعها في كتلة إعدادات "تحرير" . في القائمة التي تفتح بعد ذلك ، انقر فوق الزر "تصفية" .

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

2. بعد هذا الإجراء ، تظهر الرموز في رأس الجدول لبدء التصفية في شكل مثلثات مقلوبة صغيرة على الحافة اليمنى للخلايا. نضغط على هذا الرمز في عنوان العمود الذي نريد تحديده. في القائمة التي تفتح ، انتقل إلى العنصر "فلاتر النص" . بعد ذلك ، حدد العنصر "مرشح مخصص ..." .
3. يتم تنشيط نافذة تصفية المستخدم. في ذلك ، يمكنك تحديد التقييد الذي سيتم من خلاله التحديد. في القائمة المنسدلة للعمود الذي يحتوي على خلية التنسيق الرقمي ، والتي نستخدمها للمثال ، يمكنك اختيار واحد من خمسة أنواع من الشروط:
   • يساوي
   • غير متساوٍ
   • أكثر من.
   • أكبر من أو يساوي ؛
   • أقل من ذلك.

   لنقم بتعيين الشرط كمثال حتى نحدد فقط القيم التي يتجاوز بها مقدار العائد 10000 روبل. اضبط المفتاح على الوضع "المزيد" . في الحقل الصحيح ، أدخل القيمة "10000" . لتنفيذ هذا الإجراء ، انقر فوق الزر "موافق" .

4. كما ترون ، بعد الترشيح كان هناك خطوط فقط يتجاوز فيها مبلغ الإيرادات 10000 روبل.
5. ولكن في نفس العمود يمكننا إضافة الشرط الثاني. للقيام بذلك ، نعود إلى نافذة تصفية المستخدم مرة أخرى. كما يمكنك أن ترى ، في الجزء السفلي هناك تبديل أكثر شرط وحقل الإدخال المقابل لها. دعنا الآن نضع حد الاختيار الأعلى إلى 15000 روبل. للقيام بذلك ، ضع المفتاح في الموضع "Less" ، وفي الحقل إلى اليمين ، ندخل القيمة "15000" .

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

6. الآن ترك الجدول فقط الخطوط التي لا تقل عن مبلغ الإيرادات أقل من 10000 روبل ، ولكن لا يتجاوز 15000 روبل.
7. وبالمثل ، يمكنك تكوين المرشحات في الأعمدة الأخرى. في هذه الحالة ، من الممكن أيضًا تخزين الترشيح تحت الشروط السابقة التي تم تعيينها في الأعمدة. لذلك ، دعونا نرى كيف يتم إجراء تصفية للخلايا في تنسيق التاريخ. انقر فوق رمز التصفية في العمود المقابل. انقر باستمرار على العناصر الموجودة في القائمة "تصفية حسب التاريخ" و "تصفية مخصصة" .
8. يتم تشغيل نافذة CustomFilter المخصصة مرة أخرى. سنقوم باختيار النتائج في الجدول من 4 إلى 6 مايو 2016 شاملة. في مفتاح محدد الحالة ، كما ترى ، هناك خيارات أكثر من التنسيق الرقمي. اختر الموضع "بعد أو يساوي" . في الحقل على اليمين ، عيّن القيمة إلى "04/05/2016" . في الجزء السفلي ، عيّن المفتاح إلى الوضع "قبل أو يساوي" . في الحقل الصحيح ، ندخل القيمة "06.05.2016" . يتم ترك مفتاح توافق الحالة في الموضع الافتراضي - "AND" . من أجل تطبيق التصفية في العمل ، انقر على زر "موافق" .
9. كما ترون ، تم تخفيض قائمتنا مرة أخرى. الآن لا يترك سوى الخطوط التي يختلف فيها مقدار الإيرادات من 10،000 إلى 15000 روبل للفترة من 04.05 إلى 06.05.2016 ، شاملة.
10. يمكننا إعادة تعيين التصفية في أحد الأعمدة. دعونا نفعل ذلك لأرقام الإيرادات. انقر فوق رمز التصفية التلقائية في العمود المقابل. في القائمة المنسدلة ، انقر فوق العنصر "حذف عامل التصفية" .
11. كما ترون ، بعد هذه الإجراءات ، سيتم تعطيل العينة من مبلغ الإيرادات ، وسوف يكون الاختيار فقط حسب التواريخ متاحًا (من 04/05/2016 إلى 06/05/2016).
12. يوجد في هذا الجدول عمود آخر - "الاسم" . أنه يحتوي على بيانات في تنسيق النص. دعونا نرى كيفية إنشاء عينة عن طريق تصفية على هذه القيم.

    انقر فوق رمز التصفية في اسم العمود. دعونا نذهب من خلال أسماء القائمة "مرشحات النص" و "مرشح مخصص ..." .

13. مرة أخرى ، يفتح إطار التصفية التلقائية المخصصة. دعونا نجعل اختيار على أسماء "البطاطس" و "اللحوم" . في المربع الأول ، اضبط مفتاح الشرط على الوضع "متساوي" . في الحقل إلى اليمين ، ندخل كلمة "البطاطا" . يتم أيضًا وضع مفتاح الكتلة السفلى في موضع "متساوٍ" . في الحقل المقابل ، نصنع ملاحظة - "اللحوم" . ثم نقوم بتنفيذ ما لم نفعله من قبل: اضبط مفتاح توافق الحالة على الوضع "OR" . الآن سيتم عرض سطر يحتوي على أي من الشروط المحددة. نضغط على زر "موافق" .
14. كما ترون ، في العينة الجديدة هناك قيود على التاريخ (من 04/05/2016 إلى 06/05/2016) وباسم (البطاطس واللحوم). مبلغ الايرادات ليست محدودة.
15. يمكن إزالة عامل التصفية بالكامل نفس الأساليب التي تم استخدامها لتثبيته. ولا يهم الطريقة التي استخدمت. لإعادة ضبط التصفية ، في علامة التبويب "بيانات" ، انقر فوق الزر "تصفية" الموجود في مجموعة "الفرز والتصفية" .

    يتضمن الخيار الثاني التبديل إلى علامة التبويب الصفحة الرئيسية . هناك ، نضغط على الشريط الموجود في الزر "فرز وتصفية" في كتلة "تحرير" . في القائمة التي تم تنشيطها ، انقر فوق الزر "تصفية" .

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

الدرس: وظيفة التصفية التلقائية في Excel

الطريقة 2: تطبيق صيغة صفيف

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

1. على نفس الورقة ، نقوم بإنشاء جدول فارغ يحتوي على نفس أسماء الأعمدة في الرأس كمصدر.
2. حدد جميع الخلايا الفارغة من العمود الأول من الجدول الجديد. اضبط المؤشر في شريط الصيغة. هنا فقط سيتم إدخال الصيغة ، مما يجعل عينة وفقا للمعايير المحددة. نختار الخطوط ، ومقدار الإيرادات التي يتجاوز 15000 روبل. في مثالنا المحدد ، ستبدو صيغة الإدخال كما يلي:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

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

3. نظرًا لأن هذه هي صيغة مصفوفة ، لتطبيقها بشكل عملي ، تحتاج إلى الضغط على المفتاح Enter ومفتاح Ctrl + Shift + Enter . نحن نفعل ذلك.
4. حدد العمود الثاني مع التواريخ ووضع المؤشر في سطر الصيغة ، أدخل التعبير التالي:

   =ИНДЕКС(B2:B29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   اضغط على تركيبة المفاتيح Ctrl + Shift + Enter .

5. وبالمثل ، في العمود مع الإيرادات ، ندخل الصيغة التالية:

   =ИНДЕКС(C2:C29;НАИМЕНЬШИЙ(ЕСЛИ(15000<=C2:C29;СТРОКА(C2:C29);"");СТРОКА()-СТРОКА($C$1))-СТРОКА($C$1))

   مرة أخرى ، اكتب Ctrl + Shift + Enter .

   في الحالات الثلاث جميعها ، تتغير قيمة إحداثي الأول فقط ، وإلا تكون الصيغ متطابقة تمامًا.

6. كما ترى ، يتم ملء الجدول بالبيانات ، ولكن مظهره ليس جذابًا جدًا ، بالإضافة إلى ذلك ، يتم ملء قيم التاريخ فيه بشكل غير صحيح. من الضروري تصحيح أوجه القصور هذه. يرجع تاريخ غير صحيح إلى حقيقة أن تنسيق خلايا العمود المقابل عام ، ونحن بحاجة إلى تعيين تنسيق التاريخ. حدد العمود بأكمله ، بما في ذلك الخلايا التي بها أخطاء ، وانقر على التحديد باستخدام زر الماوس الأيمن. في القائمة التي تظهر ، انتقل إلى العنصر "تنسيق الخلية ..." .
7. في نافذة التنسيق المفتوحة ، افتح علامة التبويب "الرقم" . في كتلة "تنسيقات رقمية" ، حدد القيمة "التاريخ" . في الجزء الأيمن من النافذة ، يمكنك تحديد نوع عرض التاريخ المطلوب. بعد ضبط الإعدادات ، انقر فوق الزر "موافق" .
8. الآن يتم عرض التاريخ بشكل صحيح. ولكن ، كما ترى ، يتم ملء الجزء السفلي بالكامل من الجدول بالخلايا التي تحتوي على قيمة خاطئة "# NUMBER!" . في الواقع ، هذه هي الخلايا ، البيانات من العينة التي لم تكن كافية. سيكون أكثر جاذبية إذا تم عرضها على الإطلاق فارغة. لهذه الأغراض ، دعنا نستخدم التنسيق الشرطي. حدد جميع الخلايا في الجدول ، باستثناء الحد الأقصى. في علامة التبويب "Home" ، انقر فوق الزر "Conditional Formatting" ، الموجود في مربع الأداة "Styles" . في القائمة التي تظهر ، حدد العنصر "إنشاء قاعدة ..." .
9. في النافذة التي تفتح ، حدد نوع القاعدة "تنسيق الخلايا التي تحتوي على" فقط . في الحقل الأول تحت النص "تنسيق الخلايا فقط التي تم الوفاء بها الشرط التالي" ، حدد العنصر "أخطاء" . ثم انقر فوق الزر "تنسيق ..." .
10. في نافذة التنسيق التي تفتح ، انتقل إلى علامة التبويب "الخط" وحدد اللون الأبيض في الحقل المقابل. بعد هذه الإجراءات ، انقر فوق الزر "موافق" .
11. على الزر مع نفس الاسم بالضبط انقر بعد العودة إلى نافذة إنشاء الشرط.

الآن لدينا عينة جاهزة للقيود المحددة في جدول منفصل مصمم بشكل صحيح.

الدرس: التنسيق الشرطي في Excel

الطريقة الثالثة: شروط متعددة باستخدام الصيغة

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

1. نحن ندخل في العمود المنفصل الشروط الحدودية للعينة.
2. كما هو الحال في الطريقة السابقة ، يقوم واحد تلو الآخر بتحديد الأعمدة الفارغة في الجدول الجديد وإدراج الصيغ الثلاثة المقابلة بها. في العمود الأول ، أدخلنا التعبير التالي:

   =ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(($D$2=C2:C29);СТРОКА(C2:C29);"");СТРОКА(C2:C29)-СТРОКА($C$1))-СТРОКА($C$1))

   في الأعمدة التالية ، ندخل بالضبط نفس الصيغ ، فقط تغيير الإحداثيات مباشرة بعد اسم عامل INDEX على الأعمدة المقابلة للأعمدة التي نحتاجها ، بالقياس إلى الطريقة السابقة.

   في كل مرة بعد الإدخال ، لا تنس كتابة Ctrl + Shift + Enter .

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

الطريقة الرابعة: أخذ العينات العشوائية

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

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

   =СЛЧИС()

   تعرض هذه الوظيفة رقم عشوائي. لتفعيله ، اضغط على الزر ENTER .

2. لإنشاء عمود بالكامل من الأرقام العشوائية ، اضبط المؤشر على الزاوية السفلية اليمنى للخلية ، التي تحتوي بالفعل على الصيغة. يظهر مقبض التعبئة. اسحبه لأسفل مع الضغط على زر الماوس الأيسر بالتوازي مع جدول البيانات إلى نهايته.
3. الآن لدينا مجموعة من الخلايا المليئة بأرقام عشوائية. لكنها تحتوي على صيغة راند . نحن بحاجة أيضا للعمل مع القيم النقية. للقيام بذلك ، انسخ إلى العمود الفارغ على اليمين. نختار مجموعة من الخلايا بأرقام عشوائية. بعد تحديد موقع علامة التبويب "الصفحة الرئيسية" ، انقر على رمز "نسخ" على الشريط.
4. حدد العمود الفارغ وانقر فوق زر الماوس الأيمن ، استدعاء قائمة السياق. في مجموعة أدوات "معلمات الإدراج" ، حدد عنصر "القيم" ، ممثلة كصورة توضيحية تحتوي على أرقام.
5. بعد ذلك ، في علامة التبويب "الصفحة الرئيسية" ، انقر فوق الرمز "الفرز والتصفية " المألوف. في القائمة المنسدلة ، حدد "فرز مخصص" .
6. يتم تنشيط نافذة الفرز. تأكد من تحديد المربع بجوار "My data contains headers" ، في حالة وجود رأس ، ولا توجد علامة اختيار. في الحقل "فرز حسب" ، حدد اسم العمود الذي يتم فيه تضمين القيم المنسوخة للأرقام العشوائية. في حقل "التصنيف" ، نترك الإعدادات الافتراضية. في حقل "الطلب" ، يمكنك تحديد خيار "تصاعدي" أو "تنازلي" . لأخذ العينات العشوائية ، وهذا لا يهم. بعد إجراء الإعدادات ، انقر فوق الزر "موافق" .
7. بعد ذلك ، يتم ترتيب جميع قيم الجدول من أجل زيادة أو تقليل الأرقام العشوائية. يمكنك أخذ أي عدد من الأسطر الأولى من الجدول (5 ، 10 ، 12 ، 15 ، إلخ) ، ويمكن اعتبارها نتيجة لعينة عشوائية.

الدرس: فرز وتصفية البيانات في Excel

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

• ← باستخدام الاستيفاء في Microsoft Excel
• باستخدام وظيفة الجدولة في Microsoft Excel →