عمليات البحث والرسوم البيانية والإحصائيات والجداول المحورية

بعد مراجعة الوظائف الأساسية ومراجع الخلايا ووظائف التاريخ والوقت ، نقوم الآن بالتعمق في بعض الميزات الأكثر تقدمًا في Microsoft Excel. نقدم أساليب لحل المشاكل الكلاسيكية في التمويل وتقارير المبيعات وتكاليف الشحن والإحصاءات.

ملاحة المدرسة

1. لماذا تحتاج الصيغ والوظائف?
2. تعريف وخلق صيغة
3. مرجع الخلية النسبية والمطلقة ، والتنسيق
4. وظائف مفيدة يجب أن تعرفها
5. عمليات البحث والرسوم البيانية والإحصائيات والجداول المحورية

هذه الوظائف مهمة لرجال الأعمال والطلاب وأولئك الذين يريدون فقط معرفة المزيد.

VLOOKUP و HLOOKUP

إليك مثال لتوضيح البحث العمودي (VLOOKUP) ووظائف البحث الأفقية (HLOOKUP). يتم استخدام هذه الوظائف لترجمة رقم أو قيمة أخرى إلى شيء يمكن فهمه. على سبيل المثال ، يمكنك استخدام VLOOKUP لأخذ رقم جزء وإرجاع وصف العنصر.

وللتحقق من هذا الأمر ، دعنا نرجع إلى جدول بيانات "صانع القرار" في الجزء 4 ، حيث تحاول جين أن تقرر ما سترتديه إلى المدرسة. لم تعد مهتمة بما ترتديه ، منذ أن حطت صديقها الجديد ، لذا ستقوم الآن بارتداء ملابس وأحذية عشوائية..

في جدول بيانات Jane ، تسرد ملابسها في الأعمدة والأحذية الرأسية والأعمدة الأفقية.

فتقوم بفتح جدول البيانات والدالة RANDBETWEEN (1،3) لإنشاء رقم بين أو يساوي واحد وثلاثة يقابل الأنواع الثلاثة من الملابس التي يمكن أن ترتديها.

تستخدم الدالة RANDBETWEEN (1،5) للاختيار من بين خمسة أنواع من الأحذية.

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

نستخدم الدالة VLOOKUP لترجمة رقم الزي إلى اسم الزي. HLOOKUP يترجم من رقم الحذاء إلى أنواع مختلفة من الأحذية في الصف.

يعمل جدول البيانات على هذا النحو للأزياء:

يختار Excel رقمًا عشوائيًا من واحد إلى ثلاثة ، حيث أن لديها ثلاثة خيارات للزي.

بعد ذلك ، تقوم الصيغة بترجمة الرقم إلى نص باستخدام = VLOOKUP (B11 ، A2: B4 ، 2) والذي يستخدم رقمًا عشوائيًا من B11 للبحث في النطاق A2: B4. ومن ثم يعطي النتيجة (C11) من البيانات المذكورة في العمود الثاني.

نستخدم الأسلوب نفسه لاختيار الأحذية ، باستثناء هذه المرة نستخدم VOOKUP بدلاً من HLOOKUP.

مثال: الإحصاءات الأساسية

يعرف الجميع تقريباً صيغة واحدة من الإحصاءات - المتوسط ​​- ولكن هناك إحصائية أخرى مهمة للأعمال: الانحراف المعياري.

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

إذاً كيف يمكننا ، أو جامعة ، قياس وتفسير نتائج اختبار SAT؟ وفيما يلي نتائج اختبارات SAT لخمسة طلاب تتراوح أعمارهم من 1870 إلى 2230.

الأرقام المهمة التي يجب فهمها هي:

معدل - يُشار إلى المتوسط ​​أيضًا باسم "المتوسط".

الانحراف المعياري (STD أو σ) - يوضح هذا العدد مدى انتشار مجموعة من الأرقام. إذا كان الانحراف المعياري كبيرًا ، تكون الأرقام متباعدة وإذا كانت صفرًا ، تكون جميع الأرقام متماثلة. يمكنك أن تقول أن الانحراف المعياري هو متوسط ​​الفرق بين متوسط ​​القيمة والقيمة المرصودة ، أي 998 نقطة ولكل نقاط SAT. يرجى ملاحظة أنه من الشائع اختصار الانحراف المعياري باستخدام الرمز اليوناني سيجما "σ".

الرتبة المئوية - عندما يحصل الطالب على درجة عالية ، يمكنهم التباهي بأنهم في أعلى 99 في المائة أو شيء من هذا القبيل. "الرتبة المئوية" تعني أن نسبة الدرجات أقل من درجة معينة.

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

STD	نسبة الدرجات	مجموعة من عشرات SAT
1	68٪	1،854-2،142
2	95٪	1،711-2،285
3	99.73٪	1،567-2،429
4	99.994٪	1،424-2،572

كما ترون ، فإن فرصة وجود أي نتيجة SAT خارج 3 STD هي صفر عمليا ، لأن 99.73 في المئة من الدرجات ضمن 3 STD.

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

الآن نوضح الصيغ:

= AVERAGE (B2: B6)

متوسط ​​جميع الدرجات عبر النطاق B2: B6. على وجه التحديد ، مجموع كل الدرجات مقسومًا على عدد الأشخاص الذين أجروا الاختبار.

= STDEV.P (B2: B6)

الانحراف المعياري عبر النطاق B2: B6. ".P" تعني أنه يتم استخدام STDEV.P على جميع الدرجات ، بمعنى ، جميع السكان وليس مجرد مجموعة فرعية.

= PERCENTRANK.EXC ($ B $ 2: $ B $ 6، B2)

يقوم هذا بحساب النسبة التراكمية عبر النطاق B2: B6 بناءً على درجة SAT ، في هذه الحالة B2. على سبيل المثال ، 83٪ من النتائج أقل من درجة Walker.

الرسوم البيانية النتائج

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

الطلاب على المحور الأفقي وتظهر درجات SAT الخاصة بهم كرسم بياني أزرق على مقياس (المحور الرأسي) من 1600 إلى 2300.

الترتيب المئوي هو المحور الرأسي الأيمن من 0 إلى 90 في المائة ، ويمثله الخط الرمادي.

كيفية إنشاء مخطط

إن إنشاء مخطط هو موضوع في حد ذاته ، إلا أننا سنشرح باختصار كيف تم إنشاء الرسم البياني أعلاه.

أولاً ، حدد نطاق الخلايا ليكون في المخطط. في هذه الحالة ، من A2 إلى C6 لأننا نريد الأرقام بالإضافة إلى أسماء الطلاب.

من قائمة "إدراج" ، حدد "الرسوم البيانية" -> "المخططات الموصى بها":

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

يمكنك استخدام الرسم البياني حول وإعادة حجمه حتى يكون لديك الحجم والحجم الذي تريده. بمجرد رضاك ​​، يمكنك حفظ المخطط في جدول البيانات.

إذا قمت بالنقر بزر الماوس الأيمن على المخطط ، ثم "تحديد البيانات" ، فسيعرض لك البيانات التي تم تحديدها للنطاق.

عادةً ما تدفعك ميزة "المخططات الموصى بها" من الاضطرار للتعامل مع تفاصيل معقدة مثل تحديد البيانات المراد تضمينها ، وكيفية تعيين التسميات ، وكيفية تعيين المحاور الرأسية اليمنى واليسرى.

في مربع الحوار "تحديد مصدر البيانات" ، انقر فوق "تسجيل" ضمن "مقالات الإيضاح (السلسلة)" واضغط على "تحرير" ، وقم بتغييره ليقول "درجة".

ثم قم بتغيير السلسلة 2 ("المئوية") إلى "Percentile".

عد إلى الرسم البياني واضغط على "عنوان المخطط" وقم بتغييره إلى "SAT Scores". والآن لدينا مخطط كامل. له محورين أفقيين: أحدهما لدرجة SAT (الأزرق) وواحد للنسبة المئوية التراكمية (برتقالي).

مثال: مشكلة النقل

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

قبل البدء بهذا المثال ، علينا تمكين "Excel Solver".

تمكين Solver الوظيفة الإضافية

اختر "ملف" -> "خيارات" -> "الوظائف الإضافية". في أسفل خيارات الوظائف الإضافية ، انقر على الزر "انتقال" بجوار "إدارة: وظائف Excel الإضافية".

في القائمة الناتجة ، انقر فوق خانة الاختيار لتمكين "Solver Add-in" ، ثم انقر فوق "OK".

مثال: احسب أقل تكاليف شحن iPad

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

سعر شحن كل جهاز iPad هو المسافة من المصنع إلى مركز التوزيع إلى المصنع مقسومًا على 20 ألف كيلومتر. على سبيل المثال ، تبلغ المسافة من شانغهاي إلى ملبورن 8024 كم ، وهي 8،024 / 20،000 أو 40 دولارًا لكل جهاز iPad.

السؤال هو كيف نقوم بشحن جميع أجهزة iPad من هذه المصانع الثلاثة إلى هذه الوجهات الأربع بأقل تكلفة ممكنة?

كما يمكنك أن تتخيل ، قد يكون اكتشاف ذلك صعباً للغاية من دون بعض الصيغ والأدوات. في هذه الحالة ، يتعين علينا شحن 462،000 (F12) إجمالي iPads. محطات لديها قدرة محدودة من وحدات 500،250 (G12).

في جدول البيانات ، حتى تتمكن من رؤية كيفية عمله ، قمنا بكتابة 1 في الخلية B10 وهذا يعني أننا نريد شحن 1 iPad من شنغهاي إلى ملبورن. بما أن تكاليف النقل على طول هذا الطريق هي 0.40 دولار لكل جهاز iPad ، فإن التكلفة الإجمالية (B17) هي 0.40 دولار.

تم حساب الرقم باستخدام الدالة = SUMPRODUCT (التكاليف ، التي يتم شحنها) "التكاليف" هي النطاقات B3: E5.

و "شحنها" هي النطاق B9: E11:

يضاعف SUMPRODUCT "التكاليف" مرات المدى "شحنها" (B14). وهذا ما يسمى "مضاعفة المصفوفة".

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

باستخدام Solver

إذا كان كل ما كان علينا القيام به هو مضاعفة المصفوفات "تكاليف" المضروبة "المشحونة" التي لن تكون معقدة للغاية ، ولكن علينا التعامل مع القيود هناك أيضًا.

يجب علينا شحن ما يحتاجه كل مركز توزيع. نضع هذا الثابت في حل مثل هذا: $ B $ 12: $ E $ 12> = $ B $ 13: $ E $ 13. هذا يعني أن مجموع ما يتم شحنه ، أي إجماليات الخلايا $ 12 $: $ E $ 12 ، يجب أن يكون أكبر من أو يساوي ما يتطلبه كل مركز توزيع ($ B $ 13: $ E $ 13).

لا يمكننا شحن أكثر مما ننتج. نكتب هذه القيود مثل: $ F $ 9: $ F $ 11 <= $G$9:$G$11. Put another way, what we ship from each plant $F$9:$F$11 cannot exceed (must be less than or equal to) the capacity of each plant: $G$9:$G$11.

انتقل الآن إلى قائمة "البيانات" واضغط على زر "Solver". إذا لم يكن زر "Solver" موجودًا ، فأنت بحاجة إلى تمكين الوظيفة الإضافية لـ Solver.

اكتب في العمودين اللذين تم تفصيلهما سابقًا واختر نطاق "الشحنات" ، وهو نطاق الأرقام الذي نريد حساب Excel له. اختر أيضًا الخوارزمية الافتراضية "Simplex LP" ، وأشر إلى أننا نريد "تقليل" الخلية B15 ("إجمالي تكاليف الشحن") ، حيث تقول "تعيين الهدف".

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

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

هنا يقول Excel أنه وجد حلا. اضغط على "موافق" للحفاظ على الحل والعودة إلى جدول البيانات.

مثال: صافي القيمة الحالية

كيف تقرر الشركة ما إذا كانت ستستثمر في مشروع جديد؟ إذا كانت "القيمة الحالية الصافية" (NPV) إيجابية ، فسوف تستثمر فيها. هذا هو النهج القياسي الذي يتبعه معظم المحللين الماليين.

على سبيل المثال ، لنفترض أن شركة تعدين Codelco تريد توسيع منجم النحاس في Andinas. النهج القياسي لتحديد ما إذا كان يجب المضي قدمًا في مشروع هو حساب صافي القيمة الحالية. إذا كانت قيمة NPV أكبر من الصفر ، فسيكون المشروع مربحًا في ضوء مدخلين (1) و (2) تكلفة رأس المال.

في اللغة الإنجليزية العادية ، تكلفة رأس المال تعني المبلغ الذي يمكن أن يكسبه هذا المال إذا تركوه في البنك. إنك تستخدم تكلفة رأس المال لتخفيض القيم النقدية إلى القيمة الحالية ، وبعبارة أخرى قد يكون 100 دولار في خمس سنوات اليوم 80 دولارًا.

في السنة الأولى ، تم تخصيص 45 مليون دولار كرأس مال لتمويل المشروع. وقد حدد المحاسبون أن تكلفة رأس المال لديهم تبلغ 6 في المائة.

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

بعد 13 عامًا ، يبلغ صافي قيمة NPV 3،945،074 دولارًا أمريكيًا ، لذلك سيكون المشروع مربحًا. ووفقًا للمحللين الماليين ، فإن "فترة رد الدفع" هي 13 عامًا.

إنشاء جدول محوري

"الجدول المحوري" هو في الأساس تقرير. نحن نطلق عليها جداول محورية لأنه يمكنك تبديلها بسهولة إلى نوع واحد من التقارير إلى أخرى دون الحاجة إلى إعداد تقرير جديد بالكامل. لذلك هم محور في المكان. دعونا نعرض مثالا أساسيا يعلم المفاهيم الأساسية.

مثال: تقارير المبيعات

يتمتع موظفو المبيعات بقدرة تنافسية عالية (وهذا جزء من كونهم مندوب مبيعات) لذا فهم يريدون بطبيعة الحال معرفة كيف يتصرفون ضد بعضهم البعض في نهاية ربع العام ونهاية العام ، بالإضافة إلى حجم عمولاتهم..

لنفترض أن لدينا ثلاثة أشخاص من الموزعين - كارلوس ، فريد ، وجولي - جميعهم يبيعون النفط. تظهر مبيعاتهم بالدولار عن كل ربع سنة مالية لعام 2014 في جدول البيانات أدناه.

لإنشاء هذه التقارير ، نقوم بإنشاء جدول محوري:

حدد "إدراج -> Pivot Table ، على الجانب الأيمن من شريط الأدوات:

حدد جميع الصفوف والأعمدة (بما في ذلك اسم البائع) كما هو موضح أدناه:

يظهر مربع الحوار pivot table في الجانب الأيسر من جدول البيانات.

إذا قمنا بالنقر فوق كافة الحقول الأربعة في مربع الحوار pivot table (الربع ، السنة ، المبيعات ، والمندوب المبيعات) يضيف Excel تقريراً إلى جدول البيانات الذي لا معنى له ، ولكن لماذا?

كما ترى ، لقد حددنا جميع الحقول الأربعة لإضافتها إلى التقرير. السلوك الافتراضي لـ Excel هو تجميع الصفوف بواسطة حقول نصية ثم جمع كافة باقي الصفوف.

هنا يعطينا مجموع عام 2014 + 2014 + 2014 + 2014 = 24،168 ، وهو هراء. كما أنه أعطى مجموع الأرباع 1 + 2 + 3 + 4 = 10 * 3 = 3 0. نحن لا نحتاج إلى هذه المعلومات ، لذلك قمنا بإلغاء تحديد هذه الحقول لإزالتها من جدول المحورية الخاص بنا.

ومع ذلك ، فإن "مجموع المبيعات" (إجمالي المبيعات) مناسب ، لذا سنصلح ذلك.

مثال: المبيعات حسب البائع

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

في مربع الحوار الناتج ، نقوم بتغيير الاسم إلى "إجمالي المبيعات" ثم انقر فوق "تنسيق الرقم" وقم بتغييره إلى "العملة".

يمكنك بعد ذلك رؤية عملك اليدوي في الجدول المحوري:

مثال: المبيعات حسب البائع والربع

الآن دعونا نضيف المجاميع الفرعية لكل ربع سنة. لإضافة مجاميع فرعية فقط انقر بزر الماوس الأيسر على الحقل "ربع" وأمسكها واسحبها إلى قسم "الصفوف". يمكنك رؤية النتيجة على لقطة الشاشة أدناه:

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

مع وضع هذه المهارات في الاعتبار ، يمكنك الآن إنشاء الجداول المحورية من البيانات الخاصة بك!

استنتاج

لقد أوضحنا لك بعض ميزات الصيغ والوظائف الخاصة بـ Microsoft Excel والتي يمكنك تطبيق Microsoft Excel على أعمالك أو احتياجاتك الأكاديمية أو احتياجاتك الأخرى..

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

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

تذكر ، إذا كنت تريد الرجوع إلى الصف مرة أخرى ، يمكنك البدء من جديد مع الدرس الأول!