تعريف وخلق صيغة

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

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

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

• الصفوف والأعمدة
• وظيفة الرياضيات المثال: SUM ()
• العاملين
• أسبقية المشغل
• المثال الوظيفة المالية: PMT () ، دفع القرض
• باستخدام دالة "string" ("string" هي اختصار لـ "string of text") داخل صيغة ووظائف التعشيش

الصيغ هي مزيج من "الوظائف" ، "المشغلين" ، و "المعاملات". قبل أن نكتب بعض الصيغ ، نحتاج إلى إنشاء وظيفة ولكن قبل أن نتمكن من إنشاء وظيفة ، نحتاج أولاً إلى فهم تدوين الصفوف والأعمدة.

الصفوف والأعمدة

لفهم كيفية كتابة الصيغ والدالات ، تحتاج إلى معرفة الصفوف والأعمدة.

يتم تشغيل الصفوف أفقيًا ويتم تشغيل الأعمدة رأسيًا. لتتذكر أيها ، فكّر في عمود يمسك سقفًا - فالأعمدة تتجه صعودًا ، وهكذا تذهب الصفوف إلى اليسار واليمين.

تتم تسمية الأعمدة بالحروف ؛ صفوف بأرقام. أول خلية في جدول البيانات هي A1 تعني العمود A ، الصف 1. يتم تسمية الأعمدة بـ A-Z. عندما تنفجر الأبجدية ، يضع Excel حرفًا آخر في المقدمة: AA و AB و AC… AZ و BA و BC و BC وما إلى ذلك..

مثال: الدالة Sum ()

الآن دعونا نوضح كيفية استخدام وظيفة.

يمكنك استخدام الدالات بكتابتها مباشرةً في أو استخدام معالج الدالة. يفتح معالج الدوال عند اختيار وظيفة من قائمة "الصيغ" من "مكتبة الدوال". وإلا ، يمكنك كتابة = في خلية وستسمح لك قائمة منسدلة سهلة الاستخدام باختيار وظيفة.

يخبرك المعالج ما هي الحجج التي تحتاج إلى توفيرها لكل وظيفة. كما يوفر ارتباطًا للتعليمات عبر الإنترنت إذا كنت بحاجة إلى المساعدة في فهم وظيفة الوظيفة وكيفية استخدامها. على سبيل المثال ، إذا كتبت = sum في خلية ، فسوف يعرض لك المعالج المضمَّن الوسائط المطلوبة لوظيفة SUM.

عند كتابة دالة ، يكون المعالج مضمنًا أو يمينًا في أصابعك. عند تحديد وظيفة من قائمة "الصيغ" ، يكون المعالج عبارة عن مربع منبثق. هنا هو معالج منبثق للدالة SUM ().

من أجل وظيفتنا الأولى ، دعنا نستخدم SUM () ، الذي يضيف قائمة بالأرقام.

لنفترض أن لدينا جدول بيانات يحتوي على خطط لوضع ميزانية لعائلة عائلتك:

لحساب إجمالي التكاليف ، يمكنك كتابة = b2 + b3 + b4 + b5 ولكن من الأسهل استخدام الدالة SUM ().

في Excel ، ابحث عن الرمز Σ في الزاوية العلوية اليسرى من شاشة Excel للبحث عن الزر "جمع تلقائي" (يستخدم علماء الرياضيات الحرف اليوناني Σ لإضافة سلسلة من الأرقام).

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

اضغط على "دخول" لقبول النطاق المحدد بواسطة Excel أو استخدم المؤشر لتغيير الخلايا التي تم تحديدها.

إذا نظرت إلى ما وضع إكسل في جدول البيانات ، يمكنك أن ترى أنه كتب هذه الوظيفة:

في هذه الصيغة ، يقوم Excel بجمع الأرقام من B2 إلى B9. لاحظ أننا تركنا بعض الغرف أسفل الصف 5 بحيث يمكنك إضافة ميزانية العطلات للعائلة - ومن المؤكد أن التكلفة سترتفع حيث أن قائمة الأطفال بما يريدون القيام به والأماكن التي يريدون الذهاب إليها أطول!

لا تعمل دالات الرياضيات مع الأحرف ، لذلك إذا وضعت الأحرف في العمود ، تظهر النتيجة على أنها "#NAME" كما هو موضح أدناه.

#اسم؟ يشير إلى وجود نوع من الخطأ. يمكن أن يكون أي عدد من الأشياء بما في ذلك:

• مرجع الخلية سيئة
• باستخدام الحروف في وظائف الرياضيات
• إهمال الحجج المطلوبة
• اسم وظيفة التدقيق الإملائي خاطئ
• عمليات الرياضيات غير القانونية مثل القسمة على 0

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

لقد نقرنا على الجزء العلوي من المربع الذي رسمه Excel لاتخاذ "تذاكر الطيران" من الميزانية. يمكنك رؤية رمز cross-hair الذي يمكنك رسمه لجعل النطاق المحدد أكبر أو أصغر.

اضغط على "دخول" لتأكيد النتائج.

مشغلي الحساب

هناك نوعان من العوامل: الرياضيات والمقارنة.

مشغل الرياضيات	فريف
+	إضافة
-	الطرح ، أو النفي ، على سبيل المثال ، 6 * -1 = -6
*	عمليه الضرب
/	قطاع
٪	نسبه مئويه
^	exponent ، على سبيل المثال 24 = 2 ^ 4 = 2 * 2 * 2 * 2 = 16

هناك عوامل تشغيل أخرى لا تتعلق بالرياضيات مثل "&" والتي تعني سلسلتين (ضم نهاية إلى نهاية). على سبيل المثال ، = "Excel" و "is Fun" يساوي "Excel is Fun".

الآن ننظر إلى شركات المقارنة.

مشغل مقارنة	فريف
=	يساوي 2 = 4 أو "b" = "b"
>	أكبر من ، على سبيل المثال ، 4> 2 أو "b"> "a"
<	أقل من ، على سبيل المثال ، 2 < 4 or “a” < “b”
> =	أكبر من أو يساوي - طريقة أخرى للتفكير في هذا هو = = يعني إما > أو =.
<=	اقل او يساوي.
	لا يساوي ، على سبيل المثال ، 46

كما ترى أعلاه ، تعمل عوامل المقارنة مع الأرقام والنص.

لاحظ أنه إذا قمت بإدخال = "أ"> "ب" في خلية ، فسيظهر "FALSE" لأن "a" ليس أكبر من "b". "b" يأتي بعد "a" في الأبجدية ، لذلك "a" > "ب" أو "ب"> "أ".

أسبقية طلب المشغل

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

تعني أسبقية الأمر الترتيب الذي يقوم الكمبيوتر بحساب الإجابة عليه. كما أوضحنا في الدرس الأول ، فإن مساحة الدائرة هي πr², وهو نفس π * r * r. أنه ليس (πr)².

لذلك يجب أن تفهم أسبقية الأمر عندما تكتب صيغة.

بشكل عام ، يمكنك قول هذا:

1. يقوم Excel أولاً بتقييم العناصر الموجودة بين قوسين يعملان من الداخل.
2. ثم يستخدم قواعد ترتيب الأسبقية للرياضيات.
3. عند وجود عنصرين لهما نفس الأسبقية ، يعمل Excel من اليسار إلى اليمين.

تظهر أسبقية مشغلي الرياضيات أدناه ، بترتيب تنازلي.

(و)	عندما يتم استخدام الأقواس ، فإنها تتجاوز القواعد العادية للأسبقية. هذا يعني أن Excel سيقوم بهذا الحساب أولاً. نفسر هذا أدناه.
-	نفي ، على سبيل المثال ، -1. هذا هو نفس ضرب رقم بواسطة -1. -4 = 4 * (-1)
٪	النسبة المئوية ، تعني الضرب على 100. على سبيل المثال ، 0.003 = 0.3٪.
^	الأس ، على سبيل المثال ، 10 ^ 2 = 100
* و /	اضرب واقسِّم. كيف يمكن أن يكون لدى المشغلين نفس الأسبقية؟ إنه يعني فقط أنه إذا كان لدى الصيغة عاملان آخران لهما نفس الأسبقية ، فسيتم إجراء الحساب إلى اليسار.
+ و -	جمع وطرح.

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

مثال: حساب مساحة الدائرة

مساحة الدائرة هي= PI () * radius ^ 2.

بالنظر إلى الجدول أعلاه ، نرى أن الأسس يأتي قبل الضرب. لذا ، يحسب الكمبيوتر أولاً نصف القطر ^ 2 ، ثم يقوم بمضاعفة تلك النتيجة بواسطة Pi.

مثال: حساب زيادة في الراتب

لنفترض أن رئيسك يقرر أنك تقوم بعمل رائع وسيعطيك زيادة بنسبة 10٪! كيف تحسب راتبك الجديد?

أولاً ، تذكر أن الضرب يأتي قبل الإضافة.

هل هو الراتب + الراتب * 10٪ أم هو الراتب + (الراتب * 10٪)?

افترض أن راتبك هو 100 دولار. مع زيادة بنسبة 10 ٪ ، سيكون راتبك الجديد:

= 100 + 100 * 10٪ = 100 + 10 = 110

يمكنك أيضًا كتابتها كما يلي:

= 100 + (100 * 10٪) = 100 + 10 = 110

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

بالمناسبة ، أسهل طريقة لكتابة هذا هو الراتب * 110٪

يمكن أن تتداخل الأقواس داخل بعضها البعض. لذلك ، عندما نكتب (3 + (4 * 2)) ، نعمل من الداخل إلى الخارج ، أولاً يحسب 4 * 2 = 8 ، ثم نضيف 3 + 8 للحصول على 11.

بعض الأمثلة الأخرى

هنا مثال آخر: = 4 * 3 / 2. ما هو الجواب?

نرى من القواعد في الجدول أعلاه أن * و / لها الأسبقية المتساوية. لذا يعمل Excel من اليسار إلى اليمين ، 4 * 3 = 12 أولاً ، ثم يقسم ذلك إلى 2 للحصول على 6.

مرة أخرى يمكنك جعل ذلك صريحًا بالكتابة = (4 * 3) / 2

ماذا عن = 4 + 3 * 2?

يرى الكمبيوتر مشغلي * و +. بحيث يتبع قواعد الأسبقية (الضرب يأتي قبل الإضافة) فإنه يحسب 3 * 2 = 6 أولاً ، ثم يضيف 4 للحصول على 10.

إذا أردت تغيير ترتيب الأسبقية ، فستكتب = (4 + 3) * 2 = 14.

ماذا عن هذا واحد = -1 ^ 3?

ثم الإجابة هي -3 لأن الكمبيوتر حساب = (-1) ^ 3 = -1 * -1 * -1 = -1.

تذكر أن الأوقات السلبية سلبية وإيجابية وأوقات سلبية إيجابية. يمكنك رؤية هذا مثل هذا (-1 * -1) * -1 = 1 * -1 = -1.

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

مثال: سداد القرض الوظيفي (PMT)

دعونا ننظر إلى مثال لحساب دفع القرض.

ابدأ بإنشاء ورقة عمل جديدة.

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

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

الآن لدينا:

أضف الدالة SUM () إلى المصروفات الشهرية "الإجمالية".

لاحظ ال الرهن العقاري الخلية غير مضمنة في الإجمالي. لا يعرف Excel أنك تريد تضمين هذا الرقم ، نظرًا لعدم وجود أي قيمة هناك. لذا كن حذرا لتمديد وظيفة SUM () إلى الأعلى إما باستخدام المؤشر أو الكتابة E2 حيث تقول E3 لتضمين الرهن في المجموع.

ضع المؤشر في خلية الدفع (B4).

في قائمة الصيغ حدد القائمة المنسدلة "مالية" ثم حدد الدالة PMT. ينبثق المعالج:

استخدم المؤشر لتحديد "rate." أو "nper" (مدة القرض) أو "Pv" ("القيمة الحالية" أو مبلغ القرض). لاحظ أن لديك لتقسيم سعر الفائدة بنسبة 12 منذ يتم احتساب الفائدة شهريا. كما تحتاج إلى مضاعفة مدة القرض بالسنوات 12 بحلول الحصول على مدة القرض في الأشهر. اضغط على "موافق" لحفظ النتيجة في جدول البيانات.

لاحظ أنه يتم عرض الدفع كرقم سالب: -1013.37062. لجعلها إيجابية وإضافتها إلى النفقات الشهرية ، أشر إلى خلية الرهن العقاري (E2). اكتب "= -" ثم استخدم المؤشر للإشارة إلى حقل الدفع. الصيغة الناتجة هي = -B4.

الآن يبدو جدول البيانات هكذا:

مصاريفك الشهرية هي 1،863 دولار - أوش!

مثال: وظيفة النص

هنا نوضح كيفية استخدام وظائف داخل صيغة ووظائف النص.

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

لمعالجة هذه المشكلة تحتاج إلى استخدام خوارزمية - أي إجراء خطوة بخطوة للقيام بذلك.

على سبيل المثال ، انظر إلى "واشنطن ، جورج". الإجراء الخاص بتقسيم ذلك إلى كلمتين سيكون:

1. حساب طول السلسلة.
2. ابحث عن موضع الفاصلة (وهذا يوضح المكان الذي تنتهي فيه كلمة واحدة وتبدأ الأخرى).
3. انسخ الجانب الأيمن من السلسلة لأعلى حتى الفاصلة.
4. انسخ الجانب الأيسر من السلسلة من الفاصلة إلى النهاية.

دعونا نناقش كيفية القيام بذلك مع "جورج واشنطن" خطوة بخطوة في Excel.

1. حساب طول السلسلة مع الدالة = LEN (A3) - النتيجة هي 18.
2. الآن العثور على موقف الفاصلة عن طريق إدخال هذه الوظيفة = FIND ("،"، A3 ") - والنتيجة هي 11.
3. الآن تأخذ الجانب الأيسر من السلسلة حتى الفاصلة وإنشاء هذه الصيغة المتداخلة باستخدام النتيجة من الخطوة 1: = LEFT (A3 ، FIND ("،" ، A3) -1). لاحظ أنه علينا طرح 1 من الطول لأن FIND يعطي موضع الفاصلة.

هنا ما يبدو عليه كل هذا عندما يتم وضع جميع الوظائف معًا في صيغة. في الخلية B3 ، يمكنك مشاهدة هذه الصيغة تأخذ جميع المعلومات من الخلية A3 وتدخل "واشنطن" في ذلك.

لذلك لدينا "واشنطن" ، والآن نحن بحاجة إلى الحصول على "جورج". كيف نفعل ذلك?

لاحظ أنه كان بإمكاننا حفظ النتيجة من الخطوة 1 في خلية في حد ذاتها ، على سبيل المثال ، B6 ، ثم كتابة صيغة أبسط = LEFT (A3 ، B6-1). لكن ذلك يستخدم خلية واحدة للخطوة المتقطعة.

1. تذكر موقف الفاصلة أو حسابها مرة أخرى.
2. حساب طول السلسلة.
3. عد الأحرف من نهاية السلسلة إلى الفاصلة.

خذ عدد الأحرف من الخطوة 3 وطرح واحدة لحذف الفاصلة والمسافة.

دعونا نفعل هذا خطوة بخطوة.

1. من فوق ، هذا هو = FIND ("،" ، A3 ")
2. طول السلسلة = LEN (A3)
3. سوف تحتاج إلى استخدام بعض الرياضيات للعثور على عدد الأحرف التي يجب أخذها: = LEN (A3) - FIND ("،"، A3) - 1
4. الجانب الأيمن من السلسلة التي نريدها هو = RIGHT (A3، LEN (A3) - FIND ("،"، A3) - 1)

يجب أن يبدو جدول البيانات مشابهًا للشاشة أدناه. لقد نسخت الصيغ كنص في الجزء السفلي من جدول البيانات لتسهيل قراءته ومشاهدته.

كان هذا صعبًا بعض الشيء ولكنك تحتاج فقط إلى كتابة هذه الصيغ مرة واحدة.

التالي…

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

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