الطرق الثلاث المفضلة لاستخدام البيانات في جداول Excel

تصور هذا – لديك مصنف كبير مليء بالجداول المنسقة والمصفاة والمرتبة بشكل جيد. قد تعتقد أن عملك قد تم، ولكن في الواقع، Excel ينتظرك للقيام بالمزيد باستخدام هذه الجداول، وهو حريص على مساعدتك في تحقيق أقصى استفادة من العمل الشاق الذي قمت به حتى الآن.

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

VLOOKUP وHLOOKUP

يتم استخدام كل من VLOOKUP وHLOOKUP لتحديد موقع قيمة واستردادها من مواقع محددة في الجدول.

• فلوكوب يعتمد على كون جدول البيانات رأسيًا، ويبحث عن العمود الأول (عموديًا) في الجدول.
• هلوكوب يعتمد على كون جدول البيانات أفقيًا، ويبحث عن الصف الأول (الأفقي) في الجدول.

فلوكوب

لدي هنا قائمة بدرجات الامتحان والدرجات المطلوبة لكل درجة (دعنا نسمي هذا الجدول 1). ولدي أيضًا جدول يحتوي على درجات الطلاب (الجدول 2 من هذه النقطة). أريد أن يستخدم Excel المعلومات الواردة في الجدول 1 لإكمال العمود المفقود في الجدول 2.

سأستخدم VLOOKUP، لأنني أريد أن يبحث Excel عن القيم الموجودة في العمود الأول من الجدول 1 لإرجاع تقدير كل طالب في الجدول 2. تحتوي وظيفة VLOOKUP على بناء الجملة التالي:

=VLOOKUP(a,b,c,d)

أين

• أ هي القيمة التي يجب البحث عنها (في المثال أعلاه، هذه هي القيم الموجودة في العمود E)،
• ب هو الجدول الذي يحتوي على القيم المرجعية (في هذه الحالة، الخلايا من A1 إلى B9، أو الجدول 1)،
• ج هو رقم العمود في هذا الجدول (أريده أن يعرض الدرجة، لذا فهو العمود الثاني في الجدول 1)، و
• د هو معيار اختياري يطلب من Excel البحث عن القيم التقريبية (“TRUE”) أو بالضبط (“FALSE”). إذا تركت فارغة، القيمة الافتراضية هي TRUE.

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

=VLOOKUP(E2,$A$1:$B$9,2,TRUE)

هلوكوب

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

تحتوي الدالة HLOOKUP على بناء جملة مشابه لـ VLOOKUP:

=HLOOKUP(a,b,c,d)

أين

• أ هي القيمة المطلوب البحث عنها (في هذا المثال، القيم الموجودة في العمود C)،
• ب هو مرجع مطلق للخلايا التي تحتوي على قيمة البحث (في هذه الحالة، من A1 إلى I2)،
• ج هو رقم الصف في هذا الجدول (أريده أن يعرض الدرجة، لذا فهو الصف الثاني)، و
• د (اختياري) يكون إما “TRUE” للقيم التقريبية، أو “FALSE” للقيم الدقيقة.

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

=HLOOKUP(B5,$A$1:$I$2,2,TRUE)

الفهرس مع المباراة

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

بناء الجملة الفردية

قبل أن ننظر إلى استخدام هذه الوظائف معًا، دعونا ننظر إليها بإيجاز بشكل فردي.

بناء جملة INDEX هو

INDEX(a,b,c)

أين أ هو نطاق الخلايا التي تحتوي على البيانات، ب هو رقم الصف المطلوب تقييمه، و ج هو رقم العمود المطلوب تقييمه.

وعلى هذا الأساس،

INDEX(B2:D8,4,2)

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

بالنسبة للمباراة، نتبع

MATCH(x,y,z)

أين س هي القيمة التي نبحث عنها، ذ هو النطاق الذي يمكن العثور على القيمة فيه، و ض (اختياري) هو نوع المطابقة.

وعلى هذا الأساس،

MATCH(5,B2:B8,0)

سيخبرني أين يقع الرقم 5 ضمن النطاق B2 إلى B8، و 0 يخبر Excel بإجراء مطابقة تامة.

تستخدم معا

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

ولتحقيق ذلك، أحتاج إلى برنامج Excel لتحديد مكان اللاعب C في الجدول، ثم إخباري بالقيمة الموجودة في العمود الثالث من البيانات.

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

=INDEX(B2:D8,

الجزء التالي من بناء جملة INDEX هو رقم الصف، وسيختلف هذا اعتمادًا على اللاعب الذي أشير إليه في الخلية G2. على سبيل المثال، إذا أردت البحث عن اللاعب A، فسيكون في الصف الأول. للقيام بذلك، سأبدأ تشغيل وظيفة MATCH، حيث أريد أن يقوم Excel بمطابقة المشغل الذي كتبته في الخلية G2 مع الخلية المقابلة في عمود المشغل (A2:A8)، ومعرفة رقم الصف الموجود عليه. لقد أضفت أيضًا أ 0 في النهاية، لأنني أريد أن يقوم Excel بإرجاع استرجاع دقيق.

=INDEX(B2:D8,MATCH(G2,A2:A8,0),

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

=INDEX(B2:D8,MATCH(G2,A2:A8,0),G3)

عندما أضغط على Enter، يخبرني برنامج Excel بشكل صحيح أن اللاعب C سجل خمسة أهداف في الشهر الثالث.

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

كونتيف و سوميف

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

كونتيف

يقوم COUNTIF بحساب الخلايا التي تحتوي على معايير معينة. بناء الجملة هو

COUNTIF(a,b)

أين أ هو النطاق الذي تريد حسابه، و ب هي معايير العد.

وبالمثل، إذا أردت تضمين أكثر من معيار واحد، فسأستخدم COUNTIFS:

COUNTIFS(a,b,c,d)

أين أ و ب هي أول إقران لمعايير النطاق، و ج و د هي الاقتران الثاني بمعايير النطاق (يمكنك الحصول على ما يصل إلى 127 زوجًا).

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

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

=COUNTIF(C2:C6,">40000")

أين ج2:ج6 هو النطاق الذي تقع فيه الرواتب، و “>40000” هو المعيار.

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

=COUNTIFS(B2:B6,"Services",D2:D6,">1000")

ال B2:B6، “الخدمات” الجزء هو الاقتران بمعيار النطاق الأول، و D2:D6،”>1000″ هو الثاني.

سوميف

يقوم SUMIF بجمع الخلايا بناءً على المعايير التي قمت بتعيينها. إنه يعمل على مبدأ مماثل لـ COUNTIF، ولكن مع المزيد من الوسيطات بين قوسين. بناء الجملة هو

SUMIF(a, b, c)

أين أ هو نطاق الخلايا الذي تريد تقييمه قبل جمع المبلغ، ب هي معايير هذا التقييم (يمكن أن يكون هذا قيمة أو مرجع خلية)، و ج (اختياري) هي الخلايا المراد إضافتها إذا كانت مختلفة أ.

هذه المرة، لدينا ثلاثة أشياء يجب حلها: مجموع الرواتب التي تزيد عن 40 ألف جنيه إسترليني، وإجمالي الراتب لقسم الخدمة، ومجموع المكافآت للموظفين الذين تزيد رواتبهم عن 35 ألف جنيه إسترليني.

أولاً، لحساب مجموع الرواتب التي تزيد عن 40.000 جنيه إسترليني، أحتاج إلى كتابة الصيغة التالية في الخلية D8:

=SUMIF(C2:C6,">40000")

أين ج2:ج6 يشير إلى الرواتب في الجدول، و “>40000” يخبر Excel بجمع القيم التي تزيد عن هذا المبلغ فقط.

بعد ذلك أريد معرفة الراتب الإجمالي لقسم الخدمات. لذلك، في الخلية C9، سأكتب

=SUMIF(B2:B6,"Services",C2:C6)

أين ب2:ب6 يشير إلى عمود القسم ، “خدمات” يخبر Excel أنني أبحث تحديدًا عن موظفين في قسم الخدمات، و ج2:ج6 يخبر Excel بجمع رواتب هؤلاء الموظفين.

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

=SUMIF(C2:C6,">35000",D2:D6)

أين ج2:ج6 يخبر Excel بتقييم الرواتب، “>35000” هو المعيار لتلك الرواتب، و د2: د6 يخبر Excel بجمع مكافآت هؤلاء الأفراد الذين يستوفون المعيار.

يحتوي Excel أيضًا على وظيفة SUMIFS، والتي تنفذ نفس العملية ولكن لأكثر من معيار واحد. يحتوي على بناء جملة مختلف تمامًا عن SUMIF:

SUMIFS(a,b,c,d,e)

أين أ هو نطاق الخلايا لمجموعها، ب هو النطاق الأول الذي يتم تقييمه، ج هو المعيار ل ب، و د و ه هي الاقتران التالي بمعيار النطاق (يمكنك الحصول على ما يصل إلى 127 زوجًا).

باستخدام الجدول أعلاه، لنفترض أنني أردت جمع المكافآت للموظفين الذين يكسبون أكثر من 45000 جنيه إسترليني. هذه هي الصيغة التي سأكتبها:

=SUMIFS(D2:D6,B2:B6,"Personnel",C2:C6,">45000")

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