العمل مع المصفوفات في برنامج إكسل لقد تحولت من كونها شيئًا "سحريًا" ومخصصًا للمستخدمين المتقدمين إلى أداة يومية بفضل وظائف المصفوفة الديناميكية وظهور لامدا ومابإذا بدت كل هذه الأسماء مألوفة ولكنك لست متأكدًا تمامًا من كيفية إدراجها في جدول البيانات الخاص بك، فسوف نقوم بشرحها بهدوء وبأمثلة واضحة.
في هذه المقالة سوف ترى كيف إنشاء المصفوفات وتحويلها وتحليلها باستخدام دوال لامدا، وماب، وصيغ المصفوفات الكلاسيكية، بدءًا من الحالات البسيطة (مثل تطبيق الحد الأدنى بين قائمتين) وصولًا إلى استخدامات أكثر قوة، مثل تصفية البيانات حسب الشروطتجاهل الأخطاء أو احسب الفروقات بين النطاقات. الفكرة هي أن تحصل في النهاية على مجموعة كبيرة من الصيغ الجاهزة للاستخدام في ملفات Excel الخاصة بك، دون الخوف من صيغ المصفوفات المعقدة.
ما هي دالة MAP وكيف يتم دمجها مع دالة LAMBDA في برنامج Excel؟
الوظيفة MAP تُعد هذه إحدى الميزات الرئيسية لبرنامج Excel الجديد: فهي تتيح لك التكرار عبر مصفوفة واحدة أو أكثر (نطاقات) وإرجاع مصفوفة جديدة حيث يمثل كل عنصر نتيجة تطبيق دالة لامدا على القيم الأصلية. عمليًا، يشبه الأمر إخبار برنامج إكسل: "قم بمعالجة كل خلية على حدة، ثم قم بهذه العملية مع كل قيمة، وأعد المجموعة كاملة كمصفوفة."
الصيغة الأساسية لكلمة MAP باللغة الإسبانية هي: =MAP(array1; lambda_o_array<#>)الحجة الأولى، المصفوفة 1، هو النطاق أو المصفوفة التي تريد العمل معها؛ يجب أن تكون الوسيطة الأخيرة دائمًا من نوع LAMBDA أو، في بعض الحالات المتقدمة، مصفوفة أخرى. يجب أن تحتوي دالة لامدا على مُعامل لكل مصفوفة يتم تمريرها إلى دالة MAP.
من المهم أن نفهم ذلك يمكن لـ MAP استقبال مصفوفات متعددة (على سبيل المثال، عمودان متوازيان) وستتولى دالة لامدا معالجة القيم الموجودة في نفس الموضع. يُعد هذا مفيدًا بشكل خاص عند الحاجة إلى مقارنة أو دمج أو حساب شيء ما من قائمتين أو أكثر من قوائم البيانات، دون اللجوء إلى سحب وإفلات الصيغ أو النسخ اليدوي.
عندما لا يكون وسيط LAMBDA الخاص بالدالة MAP مُعرَّفًا بشكل صحيح، أو عندما لا يتطابق عدد معلمات LAMBDA مع عدد المصفوفات المُمرَّرة إلى الدالة، يُعيد Excel خطأً. خطأ #VALUE! مع رسالة "معلمات غير صحيحة"عادة ما يكون هذا هو أول عرض يشير إلى أن أحد المعلمات مفقود أو زائد في تعريف دالة LAMBDA.
شرح مفصل لكيفية عمل صيغة لامدا و MAP
لتحقيق أقصى استفادة من برنامج MAP، يجب أن تكون مرتاحًا لاستخدام هذه الوظيفة LAMBDAتتيح لك دالة لامدا في إكسل تعريف نوع من "الدوال المخصصة" مباشرةً ضمن الصيغة، مع تضمين معاييرها وحساباتها الداخلية، دون الحاجة إلى VBA أو وحدات الماكرو. يعتمد برنامج MAP على دالة لامدا هذه لتطبيق عملية منطقية على كل عنصر من عناصر المصفوفات.
الفكرة العامة هي أن تكتب شيئاً كهذا: =MAP(range; LAMBDA(x; operation_with_x))حيث x يمثل هذا المعامل كل قيمة على حدة في المصفوفة. بمجرد الضغط على مفتاح الإدخال، يقوم برنامج Excel بالمرور على عناصر المصفوفة وإرجاع مصفوفة أخرى بنفس الحجم مع تطبيق العملية عليها مسبقًا. في حال استخدام عدة مصفوفات، ستحتوي دالة LAMBDA على عدة معاملات (على سبيل المثال، a و b)، وستقوم العملية بدمجها.
على سبيل المثال، يدعم MAP أيضًا استدعاءات للوظائف المنطقية مثل IF وAND وOR، شريطة استخدامها بشكل صحيح في وضع المصفوفات. لاحظ أن الدوال مثل AND أو OR، عند استخدامها مباشرةً، تُرجع قيمة واحدة فقط (صواب/خطأ)؛ لذلك، في العديد من الحالات المتقدمة، تُستخدم عمليات الجمع والطرح الحسابية مع الشروط لمحاكاة سلوك OR أو AND لكل عنصر.
علاوة على ذلك، فإن دالة MAP متوافقة تمامًا مع دوال المصفوفات الديناميكية الأخرى مثل تصفية، أو تقريب، أو تقليل، أو مسح ضوئي، أو إنشاء مصفوفةفي الواقع، تكمن القوة الحقيقية في ربط العديد من هذه الوظائف معًا، مما يسمح باستخدام مصفوفة محسوبة بواسطة MAP كمعيار تصفية، أو كمجمع، أو كمدخل أساسي لحسابات أخرى.
يجب دائمًا احترام البنية الداخلية لـ LAMBDA: أولًا المعلمات (الأسماء التي ستستخدمها داخل الدالة)، مفصولة بفواصل منقوطة، وفي النهاية تضع تعبير حسابي يستخدم هذا البرنامج تلك المعاملات. إذا نسيتَ معاملاً، أو استخدمتَ معاملاً في التعبير لم تُصرِّح عنه، فستواجه خطأ المعاملات غير الصحيحة مرة أخرى.
أمثلة عملية باستخدام MAP و LAMBDA في المصفوفات
يُعد تطبيق عملية رياضية منطقية على كل عنصر من عناصر الخريطة استخدامًا كلاسيكيًا جدًا لـ MAP مصفوفة من الأرقامعلى سبيل المثال، لنفترض أن لديك بيانات في النطاق A1:C2 وتريد من Excel حساب مربع كل قيمة فقط إذا كانت أكبر من 4؛ وإلا، فأنت تريد الاحتفاظ بالقيمة الأصلية.
في تلك الحالة، ستكون إحدى الصيغ الممكنة كالتالي: =MAP(A1:C2; LAMBDA(a; SI(a>4; a*a; a)))تقوم دالة MAP بقراءة كل قيمة من A1 إلى C2 وتعيينها للمعامل. a تُحدد دالة لامدا، بمساعدة SI، ما إذا كان سيتم تربيع القيمة أو تركها كما هي. والنتيجة هي مصفوفة جديدة ديناميكية بالكامل بنفس الحجم، والتي سيتم تحديثها إذا قمت بتغيير أي من البيانات الأصلية.
سيناريو آخر مثير للاهتمام هو عندما تحتاج إلى إجراء فحص عمودان من القيم المنطقية (صواب/خطأ) ومعرفة متى يكون كلاهما صحيحًا في الوقت نفسه. تخيل أن لديك جدولًا يُسمى "الجدول أ"، يحتوي على عمودين Col1 و Col2، وتريد عمودًا ثالثًا يُشير إلى ما إذا كان كلا الحقلين صحيحًا في كل صف.
يمكن أن تكون الصيغة في عمود النتائج كالتالي: =MAP(TableA; TableA; LAMBDA(a; b; Y(a; b)))هنا، تقوم دالة MAP بالمرور على كلا العمودين بالتوازي، ولكل زوج من القيم (أ، ب) تُطبق دالة AND، التي تُرجع القيمة TRUE فقط إذا كانت كلتا القيمتين صحيحتين. والنتيجة هي مصفوفة رأسية من قيم TRUE/FALSE تتكيف تلقائيًا مع حجم الجدول.
من الممكن أيضًا دمج MAP مع وظائف ديناميكية أخرى مثل فلتر لإجراء عمليات تحديد متقدمة. على سبيل المثال، لنفترض أن لديك جدولًا يحتوي على عمودين: الحجم (في D2:D11) واللون (في E2:E11)، وتريد الاحتفاظ فقط بالصفوف التي يكون فيها الحجم "كبيرًا" واللون "أحمر".
إحدى الطرق الأنيقة للقيام بذلك هي: =FILTER(D2:E11; MAP(D2:D11; E2:E11; LAMBDA(s; c; Y(s=»كبير»; c=»أحمر»))))تقوم دالة MAP بتقييم كل صف من خلال الجمع بين قيم الحجم (s) واللون (c)، وتعيد القيمة TRUE فقط إذا استوفى كلاهما الشروط، وتستخدم دالة FILTER مصفوفة TRUE/FALSE لتحديد الصفوف التي يجب الاحتفاظ بها.
مثال محدد: الحد الأدنى لنسبة العناصر بين مصفوفتين ديناميكيتين
من الحالات الشائعة جدًا عند التعامل مع البيانات وجود عمودين أو مصفوفتان من نفس الطول وأريد إنشاء مصفوفة ثالثة تحتوي على أصغر قيمة بين كل زوج من العناصر. أو بعبارة أخرى: لكل صف، أريد إيجاد أصغر قيمة بين قيم المصفوفة A والمصفوفة B.
تخيل، على سبيل المثال، هؤلاء عمودين عموديين و النتيجة المتوقعة:
| المصفوفة-أ (عمودي) | المصفوفة ب (عمودي) | النتيجة (عمودية) |
|---|---|---|
| 4 | 5 | MIN(4; 5) |
| 2 | 1 | MIN(2; 1) |
| 3 | 4 | MIN(3; 4) |
إذا كانت كلتا القائمتين ثابتتين، فيمكنك كتابة =MIN(A1; B1) ويسحب إلى الأسفل. ولكن عندما نتحدث عن المصفوفات الديناميكية مع تغير حجم البيانات، تصبح هذه الطريقة غير عملية. أنت بحاجة إلى صيغة تُرجع عمود النتائج بالكامل دفعة واحدة، مع تعديل حجمه تلقائيًا مع تغير مصفوفات الإدخال.
يتناسب مزيج MAP + LAMBDA تمامًا مع هذه المشكلة: =MAP(MATRIX_A; MATRIX_B; LAMBDA(x; y; MIN(x; y)))هنا، تقوم دالة MAP بالمرور على عناصر القائمتين عنصرًا بعنصر، وتُسند قيمًا إلى المعاملين x و y، بينما تُعيد دالة LAMBDA القيمة MIN(x, y). والنتيجة هي مصفوفة جديدة لها دائمًا نفس طول المصفوفات الأصلية، وتتكيف تلقائيًا دون الحاجة إلى سحب الصيغ أو تعبئتها يدويًا.
يحترم هذا الحل تمامًا أسلوب المصفوفات في برنامج Excel ويتجنب العيب الرئيسي لدالة MIN، وهو يميل إلى إرجاع قيمة دنيا واحدة عند تمرير مصفوفة إليها؛ بفضل MAP، نحصل على التقييم الذي يتم صفًا تلو الآخر، مما يؤدي إلى إرجاع عدد من القيم الدنيا يساوي عدد الصفوف.
صيغ المصفوفات المتقدمة مع وظائف Excel الكلاسيكية
على الرغم من أن دالتي LAMBDA و MAP قد حسّنتا التعامل مع المصفوفات، إلا أن برنامج Excel ظل لسنوات يسمح باستخدام صيغ المصفوفات القوية باستخدام وظائف أكثر تقليدية مثل المجموع، الشرط، الخطأ، المتوسط أو ماكس وغيره وظائف البحث والمرجعالفرق اليوم هو أنه مع المصفوفات الديناميكية، لم يعد من الضروري تأكيد هذه الصيغ باستخدام مجموعات مفاتيح خاصة (Ctrl+Shift+Enter في الإصدارات القديمة)؛ ببساطة اضغط على Enter.
من المشاكل الشائعة جمع نطاق يحتوي على أخطاء (مثل #N/A، #DIV/0!، إلخ). إذا استخدمت دالة الجمع المباشر على هذا النطاق، فستحصل على خطأ بدلاً من نتيجة عددية. لحل هذه المشكلة، يمكنك "تنظيف" المصفوفة داخليًا باستخدام صيغة مثل هذه: =SUM(IF(ISERROR(Data); ""; Data))تقوم الدالة ISERROR بالكشف عن الخلايا المتضاربة، بينما تقوم الدالة IF باستبدال الأخطاء بسلاسل نصية فارغة وتترك القيم الصحيحة سليمة، أما الدالة SUM فتضيف القيم الصحيحة فقط.
وباتباع فكرة مماثلة، يمكنك أيضًا حساب عدد الأخطاء في نطاق يُسمى "البيانات". ستكون النسخة الأولى كالتالي: =SUM(IF(ISERROR(Data); 1; 0))تُنشئ هذه الدالة مصفوفة من 1 لكل خلية بها خطأ، و0 للخلايا السليمة؛ بينما تُعطي دالة SUM العدد الإجمالي للأخطاء. إذا حذفتَ الوسيط الثالث من دالة IF، يُمكنك ترك الصيغة كما هي. =SUM(IF(ISERROR(Data); 1))لأن دالة IF سترجع FALSE للخلايا التي لا تحتوي على أخطاء، وهذه القيم FALSE لا تؤثر على المجموع.
ويمكن تبسيط الأمر أكثر بفضل خدعة منطقية صغيرة: =SUM(IF(ISERROR(البيانات)*1))في هذه الحالة، يتم استغلال حقيقة أن TRUE*1 يساوي 1 و FALSE*1 يساوي 0؛ وبهذه الطريقة، ينتج ISERROR(Data) مصفوفة من TRUE/FALSE وعند ضربها في 1 يتم تحويلها إلى أصفار وواحدات، وهو أمر مثالي للجمع.
المجاميع والمتوسطات الشرطية باستخدام منطق المصفوفات
تُعد صيغ المصفوفات مفيدة للغاية عند القيام بـ المجاميع الشرطية دون اللجوء دائمًا إلى دالتي SUMIF أو SUMIFS. مثال كلاسيكي: جمع القيم الموجبة فقط في نطاق يُسمى المبيعات. ستكون صيغة المصفوفة كالتالي: =SUM(IF(Sales>0; Sales))، حيث تقوم الدالة IF بإنشاء مصفوفة بالقيم الموجبة وتعيد القيمة FALSE للقيم المتبقية، والتي تتجاهلها الدالة SUM.
عندما ترغب في تطبيق أكثر من شرط في الوقت نفسه، يمكنك دمج المقارنات بشكل ضربي. على سبيل المثال، لجمع قيم المبيعات الأكبر من 0 والأصغر من أو تساوي 5، يمكنك استخدام =SUM((المبيعات>0)*(المبيعات<=5)*(المبيعات))كل مقارنة تُنتج مصفوفة TRUE/FALSE، وضربها ينتج عنه قيمة واحدة حيث يتم استيفاء جميع الشروط.
تفصيل مهم: تتطلب هذه التقنية لضرب الشروط أن يحتوي النطاق على القيم العددية فقطإذا كانت هناك خلايا نصية أو خلايا فارغة لا تتم معالجتها بشكل صحيح، فقد تواجه أخطاءً غير متوقعة. لذلك، من الأفضل دائمًا فهم طبيعة النطاق الذي تقوم بمعالجته.
إذا كنت بحاجة إلى شرط "أو" (أي، يجب أن يكون أحد الأمرين صحيحًا أو الآخر صحيحًا)، فإن الاستراتيجية تتغير قليلاً. فبدلاً من الضرب، عادةً ما تستخدم الجمع. على سبيل المثال، لجمع قيم المبيعات الأقل من 5 أو الأكبر من 15، يمكنك كتابة =مجموع(إذا كانت((المبيعات<5)+(المبيعات>15)؛ المبيعات))هنا، أي صف يستوفي شرطًا واحدًا على الأقل ينتج قيمة غير صفرية، وإذا كان كذلك، فإنه يعتبر صحيحًا.
في هذه الحالات، لا تعمل دالتا "و" و"أو" التقليديتان بشكل جيد في صيغ المصفوفات لأنهما مصممتان لإرجاع نتيجة عامة واحدة (صواب أو خطأ)، وليس مصفوفة كاملة. لذلك، يكمن الحل في ترجمة منطق AND/OR إلى منتجات (لـ Y) أو مجاميع (لـ O)، تمامًا كما رأينا في الأمثلة.
يمكنك أيضًا حساب واحد متوسط يتجاهل قيمًا معينةمثل الأصفار. إذا كان لديك نطاق يسمى المبيعات وتريد الحصول على متوسط الخلايا غير الفارغة، يمكنك استخدام =AVERAGE(IF(Sales<>0; Sales))تقوم دالة IF بإنشاء مصفوفة تتضمن فقط الخلايا غير الصفرية، وتقوم دالة AVERAGE بحساب المتوسط على تلك القائمة التي تم تصفيتها داخليًا.
قارن النطاقات واكتشف الاختلافات باستخدام صيغ المصفوفات
يُعد مجال آخر تُعد فيه صيغ المصفوفات مفيدة بشكل خاص هو مقارنة النطاقعلى سبيل المثال، إذا كان لديك نطاقان لهما نفس عدد الصفوف والأعمدة، ويسميان MyData و YourData، وتريد معرفة عدد الخلايا المختلفة عن بعضها البعض، فيمكنك استخدام صيغة مثل هذه: =SUM(IF(MyData=YourData; 0; 1)).
يُنشئ هذا التعبير مصفوفةً يكون فيها كل عنصر 0 إذا كانت الخلايا المتناظرة متطابقة، أو 1 إذا كانت مختلفة. ثم تقوم دالة SUM بجمع كل هذه الأصفار والآحاد، وتعيد الناتج. إجمالي عدد الاختلافات بين مجموعتي البيانات؛ إذا كان المجموع 0، فهذا يعني أن كل شيء متطابق تمامًا.
إذا كنت ترغب في نسخة أصغر حجماً، فهناك نسخة شائعة الاستخدام: =SUM(1*(بياناتي<>بياناتك))هنا، يُستخدم عامل المقارنة <> (لا يساوي) لإنشاء مصفوفة صواب/خطأ مباشرةً، والتي تصبح بعد ضربها في 1 مصفوفة من 1 و0. ومرة أخرى، يُشير مجموع هذه المصفوفة إلى عدد الخلايا المختلفة.
ومن الممكن أيضًا تحديد موقع موضع القيمة القصوى في نطاق عمود واحد، على سبيل المثال، في نطاق يسمى "البيانات". إحدى طرق تحقيق ذلك باستخدام المصفوفات هي استخدام الصيغة =MIN(IF(Data=MAX(Data); ROW(Data); «»))يكمن السر في أن دالة IF تُرجع رقم الصف الذي تكون فيه القيمة هي الحد الأقصى وتترك سلسلة نصية فارغة في الباقي.
تتجاهل دالة MIN، عند تطبيقها على مصفوفة مختلطة من الأرقام والسلاسل الفارغة، السلاسل وتعيد أصغر رقم صف من بين القيم القصوى الموجودة، أي أول ظهور للقيمة القصوىإذا كانت هناك عدة قيم عظمى متطابقة، فإن الدالة MIN تأخذ صف القيمة العظمى الأولى.
إذا كنت ترغب أيضًا في الحصول على مرجع الخلية بالنسبة للقيمة القصوى، وليس فقط الصف، يمكنك تضمين الحساب أعلاه في دالة ADDRESS: =ADDRESS(MIN(IF(Data=MAX(Data); ROW(Data); "")); COLUMN(Data))وبالتالي، يوفر COLUMN(Data) رقم العمود، ويقوم ADDRESS بإنشاء المرجع الكامل، مثل "$B$7".
تُظهر هذه المجموعة الكاملة من الصيغ أنه حتى بدون وظائف حديثة مثل MAP، فإن عمليات المصفوفات تتيح لك هذه الأدوات حل المشكلات المعقدة المتعلقة بالمقارنة والبحث وتحليل البيانات بسهولة نسبية بمجرد فهمك للمنطق الكامن وراء الصواب/الخطأ والأصفار والآحاد والشروط المتسلسلة.
بدمج التقنيات الكلاسيكية مع وظائف جديدة مثل LAMBDA وMAP وFILTER وREDUCE وSCAN وMAKEARRAY وBYCOL وBYROW وISOMITTED، تنفتح أمامك مجموعة واسعة من الإمكانيات: بدءًا من إنشاء أعمدة محسوبة ديناميكية بالكامل وصولًا إلى تصميم "وحدات" صغيرة قابلة لإعادة الاستخدام من منطق متقدم تتكيف تلقائيًا مع حجم بياناتك دون الحاجة إلى تكرار الصيغ. كل هذا يجعل Excel أداة أكثر مرونة لـ أتمتة عمليات حساب المصفوفات المعقدة دون الحاجة إلى برمجة خارجية.
