كتبت مؤخرًا مقالة عن كيفية استخدام وظائف التلخيص في Excel لتلخيص كميات كبيرة من البيانات بسهولة ، لكن هذه المقالة أخذت في الاعتبار جميع البيانات في ورقة العمل. ماذا لو كنت تريد فقط النظر إلى مجموعة فرعية من البيانات وتلخيص مجموعة فرعية من البيانات؟
في Excel ، يمكنك إنشاء عوامل تصفية على أعمدة تخفي الصفوف التي لا تطابق عامل التصفية الخاص بك. بالإضافة إلى ذلك ، يمكنك أيضًا استخدام وظائف خاصة في Excel لتلخيص البيانات باستخدام البيانات التي تمت تصفيتها فقط.
في هذه المقالة ، سوف أطلعك على خطوات إنشاء الفلاتر في Excel وكذلك باستخدام ميزة مدمجة وظائف لتلخيص تلك البيانات التي تمت تصفيتها.
إنشاء عوامل تصفية بسيطة في Excel
في Excel ، يمكنك إنشاء عوامل تصفية بسيطة وفلاتر معقدة. لنبدأ بفلاتر بسيطة. عند العمل مع الفلاتر ، يجب أن يكون لديك دائمًا صفًا واحدًا في الأعلى يُستخدم للتسميات. لا يشترط الحصول على هذا الصف ، ولكنه يجعل العمل مع الفلاتر أسهل قليلاً.
أعلاه ، لدي بعض البيانات المزورة وأنا تريد إنشاء فلتر على عمود المدينة. في Excel ، من السهل جدًا القيام بذلك. استمر وانقر على علامة التبويب البياناتفي الشريط ، ثم انقر على الزر تصفية. لا يلزم تحديد البيانات الموجودة على الورقة أو النقر في الصف الأول أيضًا.
عند النقر فوق "تصفية" ، كل عمود في السطر الأول ، سيتم تلقائيًا إضافة زر قائمة منسدلة صغير إلى اليسار.
الآن ، انتقل وانقر على سهم القائمة المنسدلة في عمود المدينة. سترى اثنين من الخيارات المختلفة ، والتي سأوضحها أدناه.
في الجزء العلوي ، يمكنك فرز جميع الصفوف بسرعة عن طريق القيم في عمود المدينة. لاحظ أنه عند فرز البيانات ، فإنه سينقل الصف بأكمله ، وليس فقط القيم الموجودة في العمود "المدينة". سيضمن ذلك الحفاظ على سلامة بياناتك كما كانت من قبل.
كما أنه من المفيد إضافة عمود في الواجهة الأمامية يحمل الاسم ID ورقمه من واحد إلى عدد الصفوف الموجودة في ورقة عمل. وبهذه الطريقة ، يمكنك دائمًا التصنيف حسب عمود المعرف وإعادة بياناتك بنفس الترتيب الأصلي ، إذا كان ذلك مهمًا بالنسبة إليك.
كما ترى ، يتم الآن فرز جميع البيانات في جدول البيانات استنادًا إلى القيم الموجودة في العمود "المدينة". حتى الآن ، لا يتم إخفاء أي صفوف. لنلقِ الآن نظرة على مربعات الاختيار في الجزء السفلي من مربع حوار الفلتر. في المثال الخاص بي ، لدي ثلاثة قيم فريدة فقط في العمود "المدينة" وتظهر تلك الثلاثة في القائمة.
لقد تقدمت ولم يتم فحصها مدينتان وترك واحد فحصها. الآن ليس لدي سوى 8 صفوف من البيانات تظهر والباقي مخفي. يمكنك بسهولة معرفة أنك تبحث في البيانات التي تمت تصفيتها إذا قمت بالتحقق من أرقام الصف في أقصى اليسار. اعتمادًا على عدد الصفوف المخفية ، سترى بعض الخطوط الأفقية الإضافية ، وسيصبح لون الأرقام باللون الأزرق.
الآن لنفترض أنني أريد التصفية على عمود ثان لتقليل عدد من النتائج. في العمود C ، لدي إجمالي عدد الأعضاء في كل عائلة وأريد أن أرى فقط النتائج للعائلات التي لديها أكثر من عضوين.
انطلق وانقر على سهم القائمة المنسدلة في العمود C ، وستظهر لك مربعات الاختيار نفسها لكل قيمة فريدة في العمود. ومع ذلك ، في هذه الحالة ، نود النقر على فلاتر الأرقامثم النقر على أكبر من. كما ترى ، هناك مجموعة من الخيارات الأخرى أيضًا.
سينبثق مربع حوار جديد وهنا يمكنك كتابة القيمة للفلتر. يمكنك أيضًا إضافة أكثر من معيار واحد بوظيفة AND أو OR. يمكنك أن تقول أنك تريد صفوفًا تكون فيها القيمة أكبر من 2 ولا تساوي 5 ، على سبيل المثال.
الآن أنا في الأسفل فقط 5 صفوف من البيانات: العائلات فقط من نيو أورليانز ومع 3 أعضاء أو أكثر. سهل بما فيه الكفاية؟ لاحظ أنه يمكنك مسح الفلتر بسهولة في أحد الأعمدة عن طريق النقر على القائمة المنسدلة ثم النقر على الرابط مسح الفلتر من "اسم العمود".
هذا هو الأمر بالنسبة إلى الفلاتر البسيطة في Excel. فهي سهلة الاستخدام للغاية والنتائج جميلة إلى الأمام. لنلقِ نظرة الآن على الفلاتر المعقدة باستخدام مربع الحوار خيارات متقدمةللفلاتر.
إنشاء فلاتر متقدمة في Excel
إذا كنت تريد إنشاء فلاتر أكثر تقدمًا ، يجب استخدام مربع حوار الفلتر خيارات متقدمة. على سبيل المثال ، لنفترض أنني أردت رؤية جميع العائلات التي تعيش في مدينة نيو أورلينز مع أكثر من عضوين في أسرتها أوجميع العائلات في كلاركسفيل مع أكثر من 3 أفراد في عائلتهم وفقط تلك التي تحتوي على .EDUعنوان البريد الإلكتروني النهائي. الآن لا يمكنك فعل ذلك باستخدام فلتر بسيط.
للقيام بذلك ، نحتاج إلى إعداد ورقة Excel بطريقة مختلفة قليلاً. امض قدمًا وادخل بضعة صفوف فوق مجموعة البيانات وانسخ عناوين العناوين تمامًا في الصف الأول كما هو موضح أدناه.
الآن هنا كيف تعمل المرشحات المتقدمة. يجب أولاً كتابة المعايير الخاصة بك في الأعمدة في الأعلى ، ثم النقر فوق الزر خيارات متقدمةضمن التصنيف & amp؛ تصفيةفي علامة التبويب البيانات.
ما الذي يمكننا كتابته بالضبط في هذه الخلايا؟ حسنًا ، لنبدأ بمثالنا. نحن نريد فقط مشاهدة البيانات من New Orleans أو Clarksville ، لذلك دعونا نكتب تلك في الخلايا E2 و E3.
عندما تكتب القيم على مختلف الصفوف ، فهذا يعني OR. الآن نريد عائلات نيو أورليانز مع أكثر من عضوين وعائلات كلاركسفيل مع أكثر من 3 أعضاء. لإجراء ذلك ، اكتب & gt؛ 2في C2 و & gt؛ 3في C3.
بما أن & gt؛ 2 و New Orleans في نفس الصف ، فسيكون مشغل AND. وينطبق الشيء نفسه على الصف 3 أعلاه. وأخيرًا ، لا نريد سوى العائلات التي لديها عنوان بريد إلكتروني لإنهاء .EDU. للقيام بذلك ، اكتب فقط في *. eduفي كل من D2 و D3. يشير الرمز * إلى أي عدد من الأحرف.
بمجرد القيام بذلك ، انقر فوق أي مكان في مجموعة البيانات الخاصة بك ، ثم انقر فوق متقدم زر. سيكتشف حقل القائمة رانجتلقائيًا مجموعة البيانات منذ أن نقرت عليها قبل النقر على الزر "خيارات متقدمة". انقر الآن على الزر الصغير الصغير على يمين الزر نطاق المعايير.
حدد كل شيء من A1 إلى E3 ثم انقر فوق الزر نفسه مرة أخرى للرجوع إلى مربع الحوار "تصفية متقدمة". انقر فوق موافق ويجب الآن تصفية البيانات الخاصة بك!
كما ترى ، لدي الآن 3 نتائج فقط تتطابق مع كل تلك المعايير. لاحظ أن التسميات الخاصة بنطاق المعايير يجب أن تتطابق تمامًا مع تصنيفات مجموعة البيانات حتى يعمل ذلك.
يمكنك بوضوح إنشاء استعلامات أكثر تعقيدًا باستخدام هذه الطريقة ، لذلك يمكنك التلاعب بها للحصول على النتائج المرجوة. أخيرًا ، لنتحدث عن تطبيق وظائف الجمع على البيانات التي تمت تصفيتها.
تلخيص البيانات التي تمت تصفيتها
الآن لنفترض أنني أريد أن ألخص عدد أفراد العائلة في البيانات التي تمت تصفيتها ، كيف يمكنني حصلت على القيام بذلك؟ حسنًا ، دعنا نتخلص من فلترنا من خلال النقر على زر مسحفي الشريط. لا تقلق ، فمن السهل جدًا تطبيق الفلتر المتقدم مرة أخرى بمجرد النقر على الزر "خيارات متقدمة" والنقر على موافق مرة أخرى.
في أسفل مجموعة البيانات ، دعنا نضيف خلية باسم الإجمالي، ثم نضيف دالة مجموع لتلخيص إجمالي أفراد العائلة. في المثال الخاص بي ، قمت للتو بكتابة = SUM (C7: C31).
إذا نظرت إلى جميع العائلات ، لدي 78 عضوا. الآن دعنا نذهب إلى الأمام وإعادة تطبيق مرشحنا المتقدم ونرى ما يحدث.
! بدلاً من إظهار الرقم الصحيح ، 11 ، ما زلت أرى الإجمالي 78! لماذا هذا؟ حسنًا ، لا تتجاهل الدالة SUM الصفوف المخفية ، لذا فهي لا تزال تجري الحساب باستخدام جميع الصفوف. لحسن الحظ ، هناك بعض الوظائف التي يمكنك استخدامها لتجاهل الصفوف المخفية.
أولها المجموع الفرعي. قبل أن نستخدم أيًا من هذه الوظائف الخاصة ، ستحتاج إلى مسح الفلتر الخاص بك ومن ثم اكتب الدالة.
بمجرد مسح الفلتر ، امض قدمًا واكتب = SUBTOTAL (وسترى مربع القائمة المنسدلة يظهر مع مجموعة من الخيارات ، باستخدام هذه الوظيفة ، عليك أولاً اختيار نوع دالة التجميع التي تريد استخدامها باستخدام رقم.
في مثالنا ، أريد استخدام SUM، لذلك اكتب الرقم 9 أو انقر عليه من القائمة المنسدلة ، ثم اكتب فاصلة وحدد نطاق الخلايا.
عندما تضغط على enter ، يجب أن ترى قيمة 78 هي نفسها السابقة ، لكن إذا قمت الآن بتطبيق المرشح مرة أخرى ، فسوف نرى 11!
ممتاز ، هذا هو بالضبط ما نريده ، والآن يمكنك ضبط الفلاتر وستظل القيمة دائمًا تظهر فقط الصفوف التي تظهر حاليًا.
الوظيفة الثانية التي تعمل إلى حد كبير تمامًا مثل الدالة SUBTOTAL هي AGGREGATE. الفرق ly هو أن هناك معلمة أخرى في الدالة AGGREGATE حيث يجب عليك تحديد أنك تريد تجاهل الصفوف المخفية.
المعلمة الأولى هي دالة التجميع التي تريد استخدامها وكما هو الحال مع SUBTOTAL ، تمثل 9 الدالة SUM. الخيار الثاني هو حيث عليك كتابة 5 لتجاهل الصفوف المخفية. المعلمة الأخيرة هي نفسها وهي نطاق الخلايا.
يمكنك أيضًا قراءة مقالي حول وظائف التلخيص لمعرفة كيفية استخدم الدالة AGGREGATE ووظائف أخرى مثل MODE ، MEDIAN ، AVERAGE ، إلخ. بمزيد من التفاصيل.
نأمل ، هذا تمنحك المقالة نقطة بداية جيدة لإنشاء الفلاتر واستخدامها في Excel. إذا كانت لديك أي أسئلة ، فلا تتردد في نشر تعليق. استمتع!