اكتشفت هذه الدوال في Excel مُؤخرًا ولا أستطيع الاستغناء عنها الآن

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

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

5. TEXTSPLIT

يفصل النصوص الملتصقة ببعضها

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

دعنا نعمل مع نموذج جدول بيانات المبيعات. سترى أسماء مندوبي المبيعات مدرجة كـ “Sarah Chen” و “Mike Johnson” و “Lisa Park” كلها في عمود واحد. بدلاً من إعادة كتابة كل اسم يدويًا في أعمدة منفصلة، يمكن لـ TEXTSPLIT القيام بالمهمة تلقائيًا.

الصيغة كالتالي:

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

إليك ما تفعله كل معلمة:

• text: الخلية التي تحتوي على النص الذي تريد تقسيمه.
• col_delimiter: الحرف الذي يفصل بياناتك (مثل مسافة أو فاصلة أو فاصلة منقوطة).
• row_delimiter (اختياري): يستخدم عند التقسيم إلى كل من الصفوف والأعمدة.
• ignore_empty (اختياري): TRUE يتجاهل القيم الفارغة، FALSE يحتفظ بها (الافتراضي هو FALSE).
• match_mode (اختياري): يتحكم في حساسية حالة الأحرف (0 لحساسية حالة الأحرف، 1 لغير حساسية حالة الأحرف).
• pad_with (اختياري): ما الذي تملأ به الخلايا الفارغة عندما تكون النتائج ذات أطوال غير متساوية.

بالنسبة لأسماء مندوبي المبيعات، على سبيل المثال، سأستخدم الصيغة التالية لتقسيم الأسماء إلى أعمدة منفصلة:

=TEXTSPLIT(A2, " ")

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

4. TEXTJOIN

تدمج خلايا متعددة في خلية واحدة

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

الصيغة تبدو هكذا:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

إليك ما يتحكم فيه كل معلمة:

• delimiter: الحرف أو النص الذي يفصل القيم المدمجة (فاصلة، مسافة، شرطة، إلخ).
• ignore_empty: TRUE لتجاهل الخلايا الفارغة، FALSE لتضمينها في النتيجة.
• text1, text2, etc.: الخلايا أو النطاقات التي تريد دمجها (يمكنك الإشارة إلى خلايا فردية أو نطاقات كاملة).

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

=TEXTJOIN(" - ", TRUE, B2, D2)

عند الاختيار بين طرق دمج النصوص المختلفة، فإن فهم الاختلافات بين دالتي CONCAT و TEXTJOIN يمكن أن يساعدك في اختيار الأداة المناسبة لاحتياجات دمج البيانات المحددة.

3. CHOOSECOLS

تحدد أعمدة معينة من بياناتك

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

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

تتبع الدالة الصيغة التالية:

=CHOOSECOLS(array, col_num1, [col_num2], ...)

إليك كيفية عمل كل معامل:

• array: النطاق أو الجدول الذي يحتوي على بياناتك المصدر (يمكن أن يكون نطاق خلايا مثل A1:F100 أو مرجع جدول).
• col_num1: رقم العمود الأول الذي تريد استخلاصه (1 للعمود الأول، 2 للعمود الثاني، إلخ).
• col_num2, إلخ.: أرقام الأعمدة الإضافية التي ترغب في تضمينها (اختياري – يمكنك تحديد أي عدد تريده).

على سبيل المثال، إذا أردت استخلاص أسماء مندوبي المبيعات من العمود الثاني وحالتهم من العمود التاسع، فسأستخدم:

=CHOOSECOLS(A1:I23, 2, 9)

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

2. TAKE و DROP

استخلاص أجزاء من بياناتك

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

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

تستخدم TAKE هذه الصيغة:

=TAKE(array, rows, [columns])

تتبع DROP نمطًا مشابهًا:

=DROP(array, rows, [columns])

إليك كيفية عمل المعلمات لكلا الدالتين:

• array: نطاق البيانات المصدر الذي تريد الاستخلاص منه أو تعديله.
• rows: عدد الصفوف التي سيتم أخذها/إسقاطها (تعمل الأرقام الموجبة من الأعلى، والسالبة من الأسفل).
• columns (اختياري):
  عدد الأعمدة التي تريد أخذها أو إسقاطها (موجب من اليسار، سالب من اليمين).

للحصول على أول خمسة صفوف من بيانات المبيعات، استخدم الصيغة التالية:

=TAKE(A1:C100, 5)

لإزالة أول عشرين صفًا والعمل ببيانات نظيفة، جرب:

=DROP(A1:C23, 20)

يمكنك دمج عمليات الصفوف والأعمدة معًا. على سبيل المثال، تعطيك الصيغة التالية أول عشرة صفوف وأول ثلاثة أعمدة:

=TAKE(A1:F23, 10, 3)

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

1. AGGREGATE

حسابات قوية تتعامل مع البيانات الفوضوية

تجمع AGGREGATE وظائف 19 دالة إحصائية مختلفة في صيغة واحدة مرنة. ما يميزها هو قدرتها على تجاهل الأخطاء أو الصفوف المخفية أو البيانات المفلترة – وهو أمر لا تستطيع الدوال القياسية مثل SUM أو AVERAGE القيام به بشكل موثوق.

إذا كانت بياناتك تحتوي على بعض أخطاء #N/A، أو إذا قمت بالتصفية لإظهار مناطق معينة فقط، فيمكن لـ AGGREGATE حساب المجاميع أو المتوسطات أو الإحصائيات الأخرى دون أن تتسبب هذه المشكلات في تعطيل نتائجك. أجدها مفيدة عند العمل مع مجموعات البيانات الديناميكية حيث تتغير الرؤية وجودة البيانات بشكل متكرر.

يتضمن بناء الجملة عدة مكونات:

=AGGREGATE(function_num, options, array, [k])

يتحكم كل معيار في جوانب مختلفة من الحساب:

• function_num: رقم من 1 إلى 19 يحدد الدالة المراد استخدامها (1=AVERAGE، 4=MAX، 9=SUM، 12=MEDIAN، إلخ).
• options: يتحكم في ما يجب تجاهله أثناء الحساب (0=لا شيء، 1=صفوف مخفية، 2=قيم خطأ، 3=صفوف مخفية وأخطاء، 5=قيم خطأ فقط، 6=صفوف مخفية وقيم خطأ).
• array: نطاق الخلايا المراد إجراء الحساب عليها.
• k (اختياري):
  • يُستخدم فقط مع دوال معينة مثل LARGE أو SMALL أو PERCENTILE.

  لتلخيص مبالغ المبيعات الظاهرة مع تجاهل أي أخطاء، يمكنني استخدام:

  =AGGREGATE(9, 6, D2:D23)

  الرقم 9 يحدد SUM، والرقم 6 يخبر الدالة بتجاهل الصفوف المخفية وقيم الأخطاء على حد سواء.

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

  أدوات مدمجة تستحق الاستخدام

  غالبًا ما تكون أهم دوال Excel ليست هي تلك التي يتعلمها الناس أولاً. ومع ذلك، فهي تتعامل مع المشكلات الدقيقة التي تظهر في العمل الفعلي على جداول البيانات، بما في ذلك بيانات النصوص الفوضوية، واستخراج أجزاء معينة من مجموعات البيانات الكبيرة، وإجراء العمليات الحسابية على بيانات غير كاملة. لا تتطلب أي من الدوال التي ناقشناها مهارات Excel متقدمة. ومع ذلك، تتوفر TEXTSPLIT و CHOOSECOLS و TAKE و DROP فقط في Microsoft 365 و Excel للويب.

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