بعد سنوات من التعامل مع جداول بيانات معقدة وفوضوية، اكتشفت أربع دوال في Excel توفر لي ساعات عمل كل أسبوع، وذلك عن طريق أتمتة المهام الروتينية التي يقوم بها معظم الناس يدويًا. هذه الدوال لا غنى عنها لكل من يتعامل مع البيانات بشكل منتظم، سواء كنت محلل بيانات محترفًا أو مجرد مستخدم عادي يسعى لتبسيط عمله.
روابط سريعة
4. XLOOKUP: البحث المتقدم في جداول البيانات
XLOOKUP هي دالة بحث متطورة في برامج جداول البيانات مثل Microsoft Excel و Google Sheets، تتجاوز قدرات دوال البحث التقليدية مثل VLOOKUP و HLOOKUP. توفر XLOOKUP مرونة أكبر، وقدرة على التعامل مع البيانات بشكل أكثر فعالية، وتقليل الأخطاء الشائعة المرتبطة بالدوال القديمة. تعتبر XLOOKUP أداة أساسية للمحللين الماليين، وعلماء البيانات، وأي شخص يتعامل مع كميات كبيرة من البيانات ويحتاج إلى استخلاص معلومات محددة بسرعة ودقة. من خلال استخدام XLOOKUP، يمكنك البحث عن قيمة في نطاق معين وإرجاع قيمة مقابلة من نطاق آخر، بغض النظر عن موقع الأعمدة أو الصفوف. كما تدعم XLOOKUP البحث من اليمين إلى اليسار ومن الأسفل إلى الأعلى، مما يجعلها أكثر تنوعًا من الدوال الأخرى.
وداعًا لـ VLOOKUP: XLOOKUP هو الحل الأمثل
توقفت عن استخدام VLOOKUP منذ سنوات عندما اكتشفت XLOOKUP. ففي حين أن VLOOKUP يبحث فقط إلى اليمين ويتعطل عند تحريك الأعمدة، يعمل XLOOKUP في أي اتجاه ويظل مرنًا. يعتبر XLOOKUP أحد دوال Excel التي يمكن أن توفر لك الوقت في العثور على بيانات محددة في جداول البيانات الخاصة بك.
في بيانات أسعار مكونات الكمبيوتر الخاصة بي، أحتاج إلى العثور على أسعار وحدات معالجة الرسومات (GPU) محددة بناءً على نماذج المنتجات. مع VLOOKUP، سأحتاج إلى إعادة هيكلة الجدول بأكمله. ولكن مع XLOOKUP، كل ما عليّ فعله هو كتابة:
=XLOOKUP("GIGABYTE GeForce RTX 3060 12GB Gaming OC", C:C, D:D)
يبحث XLOOKUP في عمود المنتج بأكمله، ويعثر على وحدة معالجة الرسومات الخاصة بي، ويعيد السعر المقابل. لا يهم مكان وجود عمود السعر، ولن يتعطل إذا أضفت المزيد من الأعمدة لاحقًا. أستخدم هذا باستمرار للإشارة المرجعية إلى معلومات المنتج عبر أوراق مختلفة دون إعادة تنسيق أي شيء.
الصيغة الأساسية لـ XLOOKUP هي:
=XLOOKUP(lookup_value, lookup_array, return_array)
- lookup_value: القيمة التي تريد البحث عنها.
- lookup_array: المكان الذي تبحث فيه عن القيمة.
- return_array: العمود أو الصف الذي يحتوي على القيمة التي تريد إرجاعها.
لذا، في حالتي، كانت القيمة التي أردت العثور عليها هي “GIGABYTE GeForce RTX 3060 12GB Gaming OC”. أردت البحث عن هذه القيمة في العمود C:C، وإرجاع القيمة المقابلة من D:D في نفس الصف الذي تم العثور فيه على التطابق.
شيء آخر يعجبني في XLOOKUP هو أنه إذا أضفت “, -1” في نهاية الصيغة، فإنه يبحث من الأسفل إلى الأعلى، مما يتيح لي العثور على أحدث إدخال للسعر تلقائيًا. هذا يوفر عليّ الاضطرار إلى فرز البيانات يدويًا في كل مرة أقوم فيها بتحديث جداول البيانات الخاصة بي.
3. استخدام دالتي SUMIFS و COUNTIFS في جداول البيانات
التعامل مع معايير متعددة باحترافية
تعتبر دالتا SUM و COUNT الأساسيتان كافيتين للمهام البسيطة، لكنهما قاصرتان عند الحاجة إلى تحليل حقيقي. عندما أحتاج إلى تحليل بيانات التسعير الخاصة بي في ظل ظروف متعددة، فإنني أستخدم عادةً الدالتين SUMIFS و COUNTIFS. تسمح لي هاتان الدالتان بتقسيم مئات الصفوف بسهولة.
لنفترض أنني أريد حساب عدد معالجات AMD المتوفرة على Amazon US. بدلاً من التصفية يدويًا، أكتب:
=COUNTIFS(F:F, "Amazon US", K:K, "AMD")
يوضح لي هذا على الفور أن هناك 14 معالج AMD مدرجًا في Amazon في مجموعة البيانات الخاصة بي. والجميل هنا هو أنه يمكنني تجميع أكبر عدد ممكن من المعايير حسب حاجتي.
لتحليل الأسعار، تعمل دالة SUMIFS بنفس الطريقة. لحساب القيمة الإجمالية لجميع معالجات Intel المتوفرة حاليًا في المخزون، أستخدم:
=SUMIFS(D:D, K:K, "Intel", G:G, "In Stock")
يجمع هذا كل الأسعار في العمود D حيث تكون العلامة التجارية تساوي “Intel” و حالة المخزون تساوي “In Stock”.
الصيغة الخاصة بدالة SUMIFS هي:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2...)
- sum_range: العمود الذي تريد جمعه.
- criteria_range1: العمود الأول للتحقق من الشروط مقابله.
- criteria1: الشرط الخاص بالنطاق الأول.
- criteria_range2, criteria2: نطاقات وشروط إضافية (اختيارية).
تعمل دالة COUNTIFS بشكل مماثل، باستثناء أنها تحسب الصفوف المطابقة بدلاً من جمع القيم:
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2...)
أفضل استخدام SUMIFS و COUNTIFS للتقارير السريعة لأنها تتحدث على الفور مع البيانات الجديدة، وتتناسب بدقة مع الصيغ الموجودة لدي، وتتيح لي الاحتفاظ بكل شيء مضمنًا دون إعداد جدول محوري منفصل. هذه الأدوات تسمح بتحليل بيانات دقيق وفعال، مما يوفر الوقت والجهد في إعداد التقارير المعقدة. استخدام دوال مثل SUMIFS و COUNTIFS يمثل مهارة أساسية لكل محلل بيانات يسعى إلى استخلاص رؤى قيمة من البيانات بسرعة وسهولة.
2. التشذيب والتنظيف: خطوات أساسية للحفاظ على المظهر
وداعًا لفوضى البيانات
لا شيء يفسد جدول البيانات أسرع من البيانات غير المنظمة المليئة بالمسافات الزائدة والأحرف المخفية. لقد تعلمت هذا بالطريقة الصعبة عندما كانت عمليات البحث الخاصة بي تفشل باستمرار بسبب وجود مسافات زائدة في نهايات أسماء النماذج.
تقوم الدالة TRIM بإزالة المسافات الزائدة من بداية ونهاية النص، بالإضافة إلى أي مسافات إضافية بين الكلمات. عندما أقوم باستيراد البيانات من مصادر مختلفة، غالبًا ما تأتي أسماء المنتجات بمسافات غير متناسقة. بدلاً من تنظيف كل خلية يدويًا، أقوم بإنشاء عمود مساعد واستخدام:
=TRIM(C2)
ثم أقوم بتحريك مؤشر الماوس على حافة الخلية حتى يتحول إلى علامة الجمع (+)، ثم أسحبه لأسفل إلى جميع الصفوف التي أريد أن تعمل عليها دالة TRIM.
1. TEXTBEFORE و TEXTAFTER: شرح مفصل وأهميتهما
استخلاص البيانات المطلوبة بدقة
تُعدّ دالتا TEXTBEFORE و TEXTAFTER من بين دوال Excel المفضلة لدي لتنظيف جداول البيانات الفوضوية. تتفوق دوال النصوص الحديثة في Excel في استخلاص معلومات محددة من سلاسل نصية غير منظمة. على سبيل المثال، كان عمود الأسعار لدي يحتوي على إدخالات مثل “$177.52” و “178.33 USD” و “₱9055 ” و “9645.50 PHP” مختلطة معًا.
تستخلص الدالة TEXTBEFORE كل شيء يسبق فاصلًا محددًا:
=TEXTBEFORE(D2, " USD")
بهذه الطريقة، استخلصت الدالة “178.33” من “178.33 USD” على الفور.
تعمل الدالة TEXTAFTER بشكل عكسي، حيث تستخلص كل شيء بعد الفاصل:
=TEXTAFTER(C2, "AMD ")
بهذه الطريقة، استخلصت الدالة “Ryzen 5 5700X 8-Core AM4 Processor” من “AMD Ryzen 5 5700X 8-Core AM4 Processor”.
لعمليات الاستخلاص المعقدة، أقوم بدمج كلتا الدالتين. للحصول على السعر الرقمي فقط من “$177.52 USD”:
=TEXTBEFORE(TEXTAFTER(D8, "$"), " USD")
الصيغة العامة للدالتين TEXTBEFORE و TEXTAFTER هي:
=TEXTBEFORE(text, delimiter) and =TEXTAFTER(text, delimiter)
يكمن التطور الهائل الذي أحدثته هاتان الدالتان في دقتهما. فبدلاً من استخدام تركيبات معقدة من دوال MID و FIND و LEN، يمكنني الحصول على عمليات استخلاص نظيفة باستخدام صيغ بسيطة وسهلة القراءة. أستخدم هاتين الدالتين باستمرار لفصل أرقام الطرازات، واستخلاص مواصفات المنتج، وسحب بيانات نظيفة من النصوص المستوردة التي كانت تتطلب ساعات من التحرير اليدوي.
تعالج هذه الدوال الأربع بعضًا من أكبر مضيعات الوقت في Excel، مثل: إيجاد البيانات باستخدام عمليات بحث مرنة، والتحليل وفقًا لمعايير متعددة، وتنظيف النصوص المستوردة الفوضوية، واستخلاص معلومات محددة من سلاسل نصية معقدة. يتعامل معظم الأشخاص مع هذه المهام يدويًا، ويقضون ساعات في العمل الذي يستغرق دقائق معدودة باستخدام الصيغ الصحيحة.
لقد استخدمت هذه الدوال في كل شيء بدءًا من تحليل أسعار المكونات وحتى إعداد تقارير إدارة المخزون. وهي تعمل بغض النظر عن مجال عملك؛ لأن البيانات الفوضوية ومتطلبات البحث المعقدة هي مشاكل عالمية. بمجرد إتقانك لهذه الدوال، ستتساءل كيف تمكنت من إدارة جداول البيانات بدونها.
