كيفية تصفية البيانات في اكسل

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

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

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

إنشاء عوامل تصفية بسيطة في Excel

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

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

عند النقر فوق "تصفية" ، سيحتوي كل عمود في الصف الأول تلقائيًا على زر صغير منسدل يضاف إلى أقصى اليمين.

تابع الآن وانقر على سهم القائمة المنسدلة في عمود المدينة. سترى بضعة خيارات مختلفة ، والتي سأشرحها أدناه.

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

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

كما ترون ، يتم الآن فرز جميع البيانات الموجودة في جدول البيانات بناءً على القيم الموجودة في عمود المدينة. حتى الآن ، لا توجد صفوف مخفية. الآن دعونا نلقي نظرة على خانات الاختيار في أسفل مربع الحوار عامل التصفية. في المثال الخاص بي ، لدي فقط ثلاث قيم فريدة في عمود "المدينة" وتلك الثلاثة تظهر في القائمة.

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

الآن دعنا نقول أنني أريد التصفية في عمود ثانٍ لتقليل عدد النتائج. في العمود C ، لدي إجمالي عدد الأعضاء في كل أسرة وأريد فقط رؤية النتائج للعائلات التي تضم أكثر من عضوين.

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

سينبثق مربع حوار جديد وهنا يمكنك كتابة قيمة المرشح. يمكنك أيضًا إضافة أكثر من معيار باستخدام دالة AND أو OR. يمكنك القول أنك تريد صفوفًا حيث تكون القيمة أكبر من 2 ولا تساوي 5 ، على سبيل المثال.

أنا الآن أقل من 5 صفوف من البيانات: العائلات فقط من نيو أورليانز وبها 3 أعضاء أو أكثر. سهل بما فيه الكفاية؟ لاحظ أنه يمكنك بسهولة مسح عامل تصفية على أحد الأعمدة بالنقر فوق القائمة المنسدلة ثم النقر فوق مسح التصفية من "اسم العمود" حلقة الوصل.

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

إنشاء عوامل تصفية متقدمة في Excel

إذا كنت ترغب في إنشاء المزيد من المرشحات المتقدمة ، يجب عليك استخدام المتقدمة الحوار مرشح. على سبيل المثال ، دعنا نقول أنني أردت رؤية جميع الأسر التي تعيش في نيو أورليانز مع أكثر من 2 من أفراد أسرتها أو جميع العائلات في كلاركسفيل مع أكثر من 3 أفراد في أسرهم و فقط مع تلك .EDU إنهاء عنوان البريد الإلكتروني. الآن لا يمكنك فعل ذلك باستخدام مرشح بسيط.

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

الآن هنا هو كيف تعمل المرشحات المتقدمة. عليك أولاً كتابة المعايير الخاصة بك في الأعمدة في الأعلى ثم انقر فوق المتقدمة زر تحت فرز وتصفية على ال البيانات التبويب.

فما الذي يمكن أن نكتبه بالضبط في هذه الخلايا؟ حسنًا ، لذلك لنبدأ بمثالنا. نريد فقط رؤية البيانات من نيو أورليانز أو كلاركسفيل ، لذلك دعونا نكتب البيانات في الخلايا E2 و E3.

عندما تكتب القيم في صفوف مختلفة ، فهذا يعني OR. الآن نحن نريد عائلات نيو أورليانز مع أكثر من عضوين وعائلات كلاركسفيل مع أكثر من 3 أفراد. للقيام بذلك ، اكتب في > 2 في C2 و > 3 في C3.

نظرًا لأن> 2 ونيو أورليانز على نفس الصف ، فسيكون عامل تشغيل AND. وينطبق الشيء نفسه على الصف 3 أعلاه. أخيرًا ، نريد فقط للعائلات التي بها .EDU إنهاء عنوان البريد الإلكتروني. للقيام بذلك ، فقط اكتب * ايدو في كل من D2 و D3. الرمز * يعني أي عدد من الأحرف.

بمجرد القيام بذلك ، انقر فوق أي مكان في مجموعة البيانات الخاصة بك ثم انقر فوق المتقدمة زر. ال قائمة رانجسيقوم الحقل e تلقائيًا بتحديد مجموعة البيانات الخاصة بك منذ أن قمت بالنقر فوقها قبل النقر فوق الزر Advanced. الآن انقر على زر صغير صغير على يمين وتتراوح المعايير زر.

حدد كل شيء من A1 إلى E3 ، ثم انقر فوق الزر نفسه مرة أخرى للعودة إلى مربع الحوار Advanced Filter. انقر فوق "موافق" وسيتم الآن تصفية بياناتك!

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

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

تلخيص البيانات التي تمت تصفيتها

الآن دعنا نقول أنني أريد أن ألخص عدد أفراد الأسرة في بياناتي التي تمت تصفيتها ، كيف يمكنني أن أفعل ذلك؟ حسنًا ، دعنا نقضي على مرشحنا من خلال النقر على واضح زر في الشريط. لا تقلق ، من السهل جدًا تطبيق المرشح المتقدم مرة أخرى بمجرد النقر فوق الزر Advanced والنقر فوق OK مرة أخرى.

في أسفل مجموعة البيانات الخاصة بنا ، دعنا نضيف خلية تسمى مجموع ثم قم بإضافة دالة sum لتلخيص مجموع أفراد الأسرة. في المثال الخاص بي ، لقد كتبت فقط = SUM (C7: C31).

لذلك إذا نظرت إلى جميع العائلات ، فإن لدي 78 عضوًا. دعنا الآن نمضي قدمًا ونعيد تطبيق مرشحنا المتقدم ونرى ما سيحدث.

يصيح! بدلاً من إظهار الرقم الصحيح ، 11 ، ما زلت أرى أن الإجمالي هو 78! لماذا هذا؟ حسنًا ، لا تتجاهل الدالة SUM الصفوف المخفية ، لذلك لا تزال تقوم بالحساب باستخدام جميع الصفوف. لحسن الحظ ، هناك عدة وظائف يمكنك استخدامها لتجاهل الصفوف المخفية.

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

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

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

عندما تضغط على enter ، سترى أن قيمة 78 هي نفسها كما كانت في السابق. ومع ذلك ، إذا قمت الآن بتطبيق المرشح مرة أخرى ، فسنرى 11!

ممتاز! هذا هو بالضبط ما نريد. يمكنك الآن ضبط عوامل التصفية الخاصة بك وسوف تعكس القيمة دائمًا الصفوف التي تظهر حاليًا فقط.

الوظيفة الثانية التي تعمل تمامًا تمامًا مثل وظيفة SUBTOTAL مجموع. الفرق الوحيد هو وجود معلمة أخرى في دالة AGGREGATE حيث يتعين عليك تحديد أنك تريد تجاهل الصفوف المخفية.

المعلمة الأولى هي وظيفة الجمع التي تريد استخدامها وكما هو الحال مع SUBTOTAL ، 9 تمثل وظيفة SUM. الخيار الثاني هو المكان الذي يجب أن تكتب فيه 5 لتجاهل الصفوف المخفية. المعلمة الأخيرة هي نفسها وهي نطاق الخلايا.

يمكنك أيضًا قراءة مقالتي حول وظائف الملخص لمعرفة كيفية استخدام وظيفة AGGREGATE والوظائف الأخرى مثل MODE ، MEDIAN ، AVERAGE ، إلخ. بمزيد من التفاصيل.

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