لنفترض أن لديك التقرير التالي عن مبيعات مندوبي المبيعات:
من ذلك تحتاج إلى معرفة المقدار اقلام رصاصبيعت من قبل مندوب المبيعات إيفانوفالخامس يناير.
مشكلة: كيف تلخص البيانات بمعايير متعددة ؟؟
حل: طريقة 1:
BDSUMM (A1: G16؛ F1؛ I1: K2)
في النسخة الإنجليزية:
DSUM (A1: G16، F1، I1: K2)
كيف تعمل:
من قاعدة البيانات التي حددناها A1: G16وظيفة الساديةيسترد ويجمع بيانات العمود كمية(دعوى " مجال" = F1) حسب المعطى في الخلايا I1: K2 (البائع = إيفانوف; المنتجات = أقلام الرصاص;الشهر = يناير) معايير.
العيوب: يجب أن تكون قائمة المعايير على الورقة.
ملحوظات: عدد معايير التجميع محدود بواسطة RAM.
منطقة التطبيق: أي إصدار من Excel
الطريقة الثانية:
SUMPRODUCT ((B2: B16 = I2) * (D2: D16 = J2) * (A2: A16 = K2) * F2: F16)
في النسخة الإنجليزية:
SUMPRODUCT ((B2: B16 = I2) * (D2: D16 = J2) * (A2: A16 = K2) * F2: F16)
كيف تعمل:
تنشئ الدالة SUMPRODUCT صفيفات من قيم TRUE و FALSE ، وفقًا للمعايير المحددة ، في ذاكرة Excel.
إذا تم إجراء الحسابات في خلايا الورقة (للتوضيح ، سأوضح العملية الكاملة للصيغة كما لو كانت العمليات الحسابية تجري على الورقة ، وليس في الذاكرة) ، فإن المصفوفات ستبدو كما يلي:
من الواضح أنه إذا ، على سبيل المثال ، D2 = أقلام الرصاص، فإن القيمة ستكون TRUE ، وإذا D3 = المجلدات، ثم FALSE (لأن معيار اختيار المنتج في مثالنا هو القيمة اقلام رصاص).
مع العلم أن TRUE تساوي دائمًا 1 وأن FALSE تساوي دائمًا 0 ، نواصل العمل مع المصفوفات كما هو الحال مع الرقمين 0 و 1.
بضرب القيم التي تم الحصول عليها من المصفوفات مع بعضها البعض بالتتابع ، نحصل على مجموعة واحدة من الأصفار والآحاد. حيث تم استيفاء جميع معايير الاختيار الثلاثة ، ( إيفانوف ، أقلام الرصاص ، يناير) أي. كانت جميع الشروط صحيحة ، نحصل على 1 (1 * 1 * 1 = 1) ، ولكن إذا لم يتم استيفاء شرط واحد على الأقل ، نحصل على 0 (1 * 1 * 0 = 0 ؛ 1 * 0 * 1 = 0 ؛ 0 * 1 * 1 = 0).
الآن يبقى فقط ضرب المصفوفة الناتجة في المصفوفة التي تحتوي على البيانات التي نحتاج إلى تلخيصها نتيجة لذلك (النطاق F2: F16) وفي الواقع ، لخص ما لم يتم ضربه بصفر.
قارن الآن المصفوفات التي تم الحصول عليها بمساعدة الصيغة والحساب خطوة بخطوة على الورقة (مظلل باللون الأحمر).
أعتقد أن كل شيء واضح :)
ناقص: SUMPRODUCT - صيغة صفيف "ثقيلة". عند الحساب على نطاقات البيانات الكبيرة ، يزداد وقت إعادة الحساب بشكل ملحوظ.
ملحوظات
منطقة التطبيق: أي إصدار من Excel
الطريقة الثالثة: صيغة الصفيف
SUM (IF ((B2: B16 = I2) * (D2: D16 = J2) * (A2: A16 = K2)، F2: F16))
في النسخة الإنجليزية:
SUM (IF ((B2: B16 = I2) * (D2: D16 = J2) * (A2: A16 = K2)، F2: F16))
كيف تعمل:تمامًا مثل الطريقة رقم 2. هناك اختلافان فقط - صيغة معينةدخلت بالضغط Ctrl + Shift + Enterبدلاً من الضغط فقط يدخلوالمصفوفة المكونة من 0 و 1 لا يتم ضربها في نطاق التجميع ، ولكن يتم تحديدها باستخدام الدالة IF.
ناقص: تعمل صيغ الصفيف عند الحساب على نطاقات بيانات كبيرة على زيادة وقت إعادة الحساب بشكل ملحوظ.
ملحوظات: عدد المصفوفات المعالجة يقتصر على 255.
منطقة التطبيق: أي إصدار من Excel
الطريقة الرابعة:
SUMIFS (F2: F16، B2: B16، I2، D2: D16، J2، A2: A16، K2)
في وقت سابق ، وصفت كيفية استخدام وظيفة مخصصة للبحث. لسوء الحظ ، لا تعمل هذه الوظيفة إذا تم تلوين الخلايا بها تنسيق مشروط. لقد وعدت بـ "إنهاء" الوظيفة. لكن في العامين اللذين انقضيا على نشر تلك المذكرة ، لم أتمكن من كتابة كود قابل للفهم سواء بمفردي أو بمساعدة المعلومات من الإنترنت ... ( تحديث 29 مارس 2017بعد خمس سنوات أخرى ، تمكنت من كتابة الرمز ؛ انظر الجزء الأخير من الملاحظة). وقد صادفت مؤخرًا فكرة واردة في كتاب D. Holy ، R. Holy "Excel 2007. Tricks" ، والذي يتيح لك الاستغناء عن التعليمات البرمجية على الإطلاق.
يجب أن تكون هناك قائمة بالأرقام من 1 إلى 100 ، موضوعة في النطاق A1: A100 (الشكل 1 ؛ انظر أيضًا ورقة "SUMIS" في ملف Excel). يحتوي النطاق على تنسيق شرطي يشير إلى الخلايا التي تحتوي على أرقام أكبر من 10 وأقل من أو تساوي 20.
أرز. 1. مجموعة من الأرقام. يميز التنسيق الشرطي الخلايا التي تحتوي على قيم من 10 إلى 20
قم بتنزيل الملاحظة بتنسيق ، أمثلة في التنسيق
أنت الآن بحاجة إلى إضافة القيم في الخلايا التي تفي بالمعايير التي أنشأتها للتو. لا يهم التنسيق الذي يتم تطبيقه على هذه الخلايا ، ولكنك تحتاج إلى معرفة المعايير التي يتم من خلالها تمييز الخلايا.
لإضافة نطاق من الخلايا المقابلة لـ وحيدالمعيار ، يمكنك استخدام وظيفة SUMIF (الشكل 2).
أرز. 2. جمع الخلايا التي تستوفي نفس الشرط
اذا كنت تمتلك بعضالشروط ، يمكنك استخدام وظيفة SUMIFS (الشكل 3).
أرز. 3. جمع الخلايا التي تفي بشروط متعددة
يمكنك استخدام الدالة COUNTIF لحساب عدد الخلايا التي تفي بنفس المعايير.
يمكنك استخدام الدالة COUNTIFS لحساب عدد الخلايا التي تفي بمعايير متعددة.
يوفر Excel وظيفة أخرى تسمح لك بتحديد شروط متعددة. هذه الميزة جزء من مجموعة الميزات الأساسية. بيانات Excelويسمى BDSUMM. للتحقق من ذلك ، استخدم نفس مجموعة الأرقام في النطاق A2: A100 (الشكل 4 ؛ انظر أيضًا الورقة "BDSUMM" لملف Excel).
أرز. 4. استخدام ميزات قاعدة البيانات
حدد الخلايا C1: D2 وقم بتسمية معايير النطاق عن طريق كتابتها في مربع الاسم على يسار شريط الصيغة. حدد الآن الخلية C1 وأدخل = $ A $ 1 ، وهو الرابط إلى الخلية الأولى في ورقة العمل التي تحتوي على اسم قاعدة البيانات. أدخل = $ A $ 1 في الخلية D1 وستحصل على نسختين من عنوان العمود A. سيتم استخدام هذه النسخ كعناوين لشروط BDSUMM (C1: D2) ، والتي قمت بتسميتها "المعايير". في الخلية C2 ، أدخل> 10. في الخلية D2 ، اكتب<=20. В ячейке, где должен быть результат, введите следующую формулу:
BDSUMM (1 دولار أسترالي: 101.1 دولار أسترالي ، المعايير)
يمكنك استخدام الدالة COUNT لحساب عدد الخلايا التي تفي بمعايير متعددة.
أثناء قراءة كتاب John Walkenbach ، تعلمت أنه بدءًا من Excel 2010 ، قدم VBA خاصية DisplayFormat جديدة (انظر ، على سبيل المثال ، خاصية Range.DisplayFormat). بمعنى ، يمكن لـ VBA قراءة التنسيق المعروض على الشاشة. لا يهم كيف تم الحصول عليه ، من خلال إعدادات المستخدم المباشرة ، أو باستخدام التنسيق الشرطي. لسوء الحظ ، قام مطورو MS بعمل ذلك بحيث تعمل خاصية DisplayFormat فقط في الإجراءات التي يتم استدعاؤها من VBA ، والوظائف المعرفة من قبل المستخدم القائمة على هذه الخاصية تنشئ الخطأ #VALUE! ومع ذلك ، يمكنك الحصول على مجموع القيم في نطاق عبر خلايا بلون معين باستخدام إجراء (ماكرو ، وليس دالة). فتح (يحتوي على رمز فبا). اذهب من خلال القائمة منظر -> وحدات الماكرو -> وحدات الماكرو؛ فى الشباك دقيق، قم بتمييز الخط سومكلوركوند.، و اضغط يجري. قم بتشغيل الماكرو ، وحدد نطاق التجميع والمعيار. ستظهر الإجابة في النافذة.
قانون الإجراءات
Sub SumColorCond () Application.Vol المتغير خافت حقيقي SumColor كـ خافت مزدوج i كنطاق مستخدم خافت للنطاق كمجموعة أبعاد نطاق كنطاق SumColor = 0 "مجموعة طلبات النطاق UserRange = Application.InputBox (_ موجه: =" حدد نطاق المجموع "، _ العنوان: = "تحديد النطاق" ، _ الافتراضي: = ActiveCell.Address ، _ النوع: = 8) "مجموعة استعلام المعيار CriterionRange = Application.InputBox (_ Prompt: =" حدد معيار الجمع"، _ Title: =" تحديد المعايير "، _ الافتراضي: = ActiveCell.Address، _ Type: = 8)" جمع الخلايا "الصحيحة" لكل i في UserRange If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat . Interior.Color ثم SumColor = SumColor + i End If التالية MsgBox SumColor End Sub
Sub SumColorCond () طلب. متقلب صحيح قاتمة سوماللون مزدوج خافت أنا كمجموعة خافت UserRange كنطاق معيار خافت نطاق كمدى SumColor = 0 "طلب النطاق تعيين UserRange = Application.InputBox (_ رسالة مطالبة: = "حدد نطاق التجميع" ، _ العنوان: = "اختيار النطاق" ، _ الافتراضي: = ActiveCell.Address، _ اكتب: = 8) "معايير الطلب تعيين CriterionRange = التطبيق. صندوق الإدخال(_ موجه: = "تحديد معيار الجمع", _ العنوان: = "اختيار المعايير" ، _ الافتراضي: = ActiveCell. عنوان ، _ |
غالبًا ما تحتاج إلى جمع كل خلية ثانية وثالثة ورابعة وما إلى ذلك في جدول بيانات. الآن ، بفضل الحيلة التالية ، يمكن القيام بذلك.
لا يوفر Excel دالة قياسية يمكنها جمع كل منها الخلية nأو سلسلة. ومع ذلك ، يمكنك إنجاز هذه المهمة بعدة طرق مختلفة. تعتمد كل هذه الأساليب على وظائف ROW (ROW) و MOD (MOD).
وظيفة ROWإرجاع رقم الصف لمرجع الخلية المحدد: ROW (مرجع) ، في الإصدار الروسي من Excel ROW (مرجع).
وظيفة MOD (MOD)تُرجع الباقي بعد قسمة رقم على القاسم: MOD (رقم ؛ مقسوم عليه) ، في النسخة الروسية من Excel MOD (رقم ؛ مقسوم عليه).
ضع الدالة ROW في دالة MOD (لتمرير وسيطة رقمية) ، اقسم على 2 (لتجميع كل خلية ثانية) ، وتحقق مما إذا كانت النتيجة ليست صفرًا. إذا كان الأمر كذلك ، يتم تلخيص الخلية. يمكن استخدام هذه الوظائف بعدة طرق - سيوفر بعضها نتائج أفضل من غيرها. على سبيل المثال ، قد تبدو صيغة صفيف لجمع كل خلية أخرى في النطاق $ A $ 1: $ A $ 100 كما يلي: = SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500)؛ 2) = 0؛ $ 1 دولار أسترالي: 500 دولار أسترالي ؛ 0))
نظرًا لأن هذه صيغة صفيف ، يجب عليك إدخالها بالضغط على Ctrl + Shift + Enter ، سيضيف Excel أقواس معقوفة بحيث تبدو كما يلي: (= SUM (IF (MOD (ROW ($ A $ 1: $ A $ 500) ، 2) = 0 ؛ $ A $ 1: $ A $ 500 ؛ 0))) $ 500 ؛ 0))) نحتاج إلى Excel لإضافة أقواس متعرجة ؛ إذا أضفتها بنفسك ، فلن تعمل الصيغة.
على الرغم من تحقيق الهدف ، إلا أن هذه الطريقة تؤثر سلبًا على التصميم. جدول. هذا تطبيق غير ضروري لصيغة صفيف. لجعل الأمور أسوأ ، تحتوي هذه الصيغة الطويلة على دالة ROW قابلة للحساب متداخلة داخلها ، مما يجعل الصيغة الأكبر قابلة للحساب أيضًا. هذا يعني أنه سيتم إعادة حسابه باستمرار ، بغض النظر عما تفعله دفتر العمل. هذه طريقة سيئة للغاية!
ها هي صيغة أخرى وهي قليلة الخيار الأفضل: = SUMPRODUCT ((MOD (ROW ($ A $ 1: $ A $ 500) ؛ 2) = 0) * ($ A $ 1: $ A $ 500)): $ A $ 500) ؛ 2) = 0) * ($ A $ 1 : 500 دولار أسترالي)).
ومع ذلك ، يجب أن تدرك أن هذه الصيغة سترجع الخطأ #VALUE! (#VALUE!) إذا كانت أي خلايا في النطاق تحتوي على نص بدلاً من أرقام. هذه الصيغة ، على الرغم من أنها ليست في الواقع صيغة صفيف ، تعمل على إبطاء سرعتها أيضًا وظيفة Excelإذا تم استخدامه عدة مرات ، أو إذا كان يشير إلى نطاق كبير في كل مرة.
لحسن الحظ هناك أفضل طريقة، وهو ليس فقط أكثر كفاءة ، ولكنه أيضًا أكثر مرونة. يتطلب استخدام دالة DSUM. في هذا المثال ، استخدمنا النطاق A1: A500 باعتباره النطاق الذي يتم فيه جمع كل خلية نونية.
في الخلية E1 ، أدخل الكلمة معايير. في الخلية E2 ، أدخل الصيغة التالية: = MOD (ROW (A2) - $ C $ 2-1، $ C $ 2) = 0 = 0. حدد الخلية C2 وحدد البيانات → التحقق من الصحة.
في حقل نوع البيانات (السماح) ، حدد قائمة (قائمة) ، وفي حقل المصدر (المصدر) ، أدخل 1 ، 2 ، 3 ، 4 ، 5 ، 6 ، 7 ، 8 ، 9 ، 10. تأكد من أن القائمة تم تحديد خانة الاختيار من القيم الصالحة (In-Cell) ، وانقر فوق الزر "موافق". في الخلية C1 ، أدخل النص SUM كل…. في أي خلية باستثناء الصف 1 ، أدخل الصيغة التالية: = DSUM ($ A: $ A؛ 1؛ $ E $ 1: $ E $ 2): $ E $ 2).
في الخلية الموجودة أعلى الخلية التي أدخلت فيها وظيفة DSUM مباشرةً ، أدخل النص = "Summing Every" & $ C $ 2 & CHOOSE ($ C $ 2؛ "st"؛ "nd"؛ "rd"؛ "th"؛ " th "؛" th "؛" th "؛" th "؛" th "؛" th ") و" الخلية ". الآن يبقى فقط تحديد الرقم المطلوب في الخلية C2 ، وسيتم تنفيذ الباقي بواسطة وظيفة DSUM.
باستخدام دالة DSUM ، يمكنك جمع الخلايا على فترات زمنية تحددها. تعد الدالة DSUM أكثر فاعلية من صيغة صفيف أو دالة SUMPRODUCT. على الرغم من أن إعدادها يستغرق وقتًا أطول قليلاً ، إلا أن هذه حالة يصعب تعلمها وسهلة القتال.