بدون الصيغ ، سيبدو جدول بيانات Excel مختلفًا قليلاً عن جداول البيانات التي تم إنشاؤها في Word. تسمح لك الصيغ بإجراء حسابات معقدة للغاية. بمجرد تغيير بيانات الحسابات ، يقوم البرنامج على الفور بإعادة حساب النتيجة باستخدام الصيغ.

في بعض الحالات ، لإعادة حساب الصيغ ، يجب استخدام أداة خاصة ، لكننا سننظر في ذلك في قسم منفصل عن حسابات الصيغ.

تعبئة أوراق إكسل بالصيغ

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

المهمة 1. في الخلية F2 ، أدخل الصيغة التالية على النحو التالي: = D2 + D2 * E2. بعد الدخول ، اضغط على "دخول".

المهمة 2. في الخلية F2 ، أدخل علامة "=" فقط. ثم انقر فوق الخلية D2 ، ثم اضغط على "+" ، ثم انقر فوق D2 مرة أخرى ، ثم أدخل "*" ، وانقر فوق الخلية E2. بعد الضغط على مفتاح "Enter" ، نحصل على نفس النتيجة.

هناك طرق أخرى لتقديم الصيغ ، ولكن في هذه الحالة ، هذان الخياران كافيان.

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

بشكل افتراضي ، تعرض الخلايا التي تحتوي على صيغ نتيجة حسابها. عند العرض ، لا يمكنك تحديد مكان الخلايا التي تحتوي على الصيغ على الفور ومكان وجود البيانات الواردة للحسابات. لذلك ، من الملائم أحيانًا استخدام تركيبة المفاتيح السريعة CTRL + ~ (يكون المفتاح ~ أمام المفتاح "1") للتبديل إلى وضع عرض الصيغة. سيؤدي الضغط على هذه المجموعة مرة أخرى إلى العودة إلى وضع عرض نتائج حساب الصيغ.

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



انسخ الصيغ إلى عمود

في الخلايا F3 و F4 ، أدخل نفس الصيغة لحساب العائد كما في F2 ، ولكن بطريقة فعالة مختلفة للنسخ.

المهمة 1. انتقل إلى الخلية F3 واضغط على مجموعة المفاتيح CTRL + D. وبالتالي ، سيتم نسخ الصيغة الموجودة في الخلية أعلاه (F2) تلقائيًا. لذلك يسمح لك Excel بنسخ الصيغة إلى العمود بأكمله. افعل الشيء نفسه في الخلية F4.

المهمة 2. احذف الصيغ في الخلايا F3: F4 (حدد النطاق واضغط على مفتاح "حذف"). بعد ذلك ، حدد نطاق الخلايا F2: F4. واضغط على مجموعة المفاتيح CTRL + D. كما ترى ، هذه طريقة أكثر فاعلية لتعبئة عمود كامل من الخلايا بصيغة من F2.

المهمة 3. احذف الصيغ الموجودة في النطاق F3: F4. اجعل الخلية F2 نشطة عن طريق تحريك المؤشر فوقها. بعد ذلك ، حرك مؤشر الماوس فوق النقطة الموجودة في الركن الأيمن السفلي من المؤشر المستطيل. سيتغير مؤشر الماوس إلى علامة الجمع "+". ثم اضغط باستمرار على زر الماوس الأيسر ، حرك المؤشر لأسفل خليتين أخريين بحيث يتم تحديد النطاق الكامل F2: F4.

بمجرد تحرير المفتاح الأيسر ، سيتم نسخ الصيغة تلقائيًا في كل خلية.

يمكن نسخ الصيغ بثلاث طرق أخرى:

• باستخدام الأدوات الموجودة على الشريط ؛
• باستخدام مجموعة من مفاتيح التشغيل السريع ؛
• عن طريق التحكم في مؤشر الماوس والضغط على مفتاح "CTRL".

هذه الأساليب أكثر ملاءمة لبعض المواقف ، والتي سننظر فيها في الدروس التالية.

أكمل المهام:

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

قم بتنشيط الخلية F2 وأدخل التاريخ الحالي كـ 27.11.02 .

قم بتنشيط الخلية جي2 وأدخل الوقت الحالي كـ 10:32 .

قم بتنشيط الخلية ج 3أدخل الرقم 25 . توصية : اطلب الأرقام والفاصلة العشرية على لوحة المفاتيح الرقمية (المؤشرNum Lockيجب تمكين).

قم بتنشيط الخلية ج 4أدخل الرقم 13,6 .

المهمة 4. إدخال الصيغ

لإكمال هذه المهمة ، يجب أن تعرف:

1. يبدأ إدخال صيغة بعلامة التساوي= .

2. قد تشمل الصيغةالأرقام وعناوين الخلايا والوظائف القياسية ، مرتبطة بعلامات العمليات الحسابية:

٪ نسبه مئويه،

^ عملية الأس (رمز^ المجندين فيلاتيني التسجيل عند الضغط على المفاتيحالتحول + 6 على لوحة المفاتيح الرئيسية)

عمليه الضرب،

/ قطاع،

+ إضافة ،

 الطرح.

عند كتابة تعبير ، يمكنك استخدامهبين قوسين . على سبيل المثال ، = 2 ^ 3- (34+10).

3. يتم إدخال عناوين الخلية في الصيغة أو عن طريق الكتابةفي السجل اللاتيني أومن خلال النقر على الخلية المطلوبة (الخيار الثاني أكثر ملاءمة).

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

أكمل المهام:

المهمة 4. تحرير البيانات في خلية

لإكمال هذه المهمة ، يجب أن تعرف:

للاستبدال الكاملالبيانات ، يكفي تنشيط الخلية وإدخال بيانات جديدة ، بينما يتم حذف البيانات القديمة تلقائيًا.

للاستبدال الجزئيالبيانات موجودة بطريقتين:

الطريقة الأولى : انقر نقرًا مزدوجًا على الخلية (سيظهر مؤشر نصي) وقم بتحرير البيانات.

الطريقة الثانية: قم بتنشيط الخلية ، وانقر في شريط الصيغة (سيظهر مؤشر نص) وقم بتحرير البيانات.

أكمل المهام:

في الخلية في 2تغيير النص إلى " الجدول رقم 1»واضغط على المفتاح يدخل.

في الخلية ه 5قم بتغيير الصيغة جزئيًا: = 25.5-С3واضغط على المفتاح يدخل.

المهمة 5. إدخال الملاحظات

صملاحظة - هذا هونص توضيحيإلى الخلية.

أكمل المهام:

أضف إلى الخلية في 2ملاحظة (اسمك الأخير واسمك الأول) ، لهذا:

تنشيط الخلية B2 ؛

حدد عنصر القائمة Insert ® Note (نتيجة لذلك ، سيظهر إطار بمؤشر نص بداخله بالقرب من الخلية) ؛

أدخل النص المطلوب ؛

انقر خارج الإطار (نتيجة لذلك ، سيظهر مثلث أحمر في الخلية في الزاوية اليمنى العليا - إشارة إلى وجود ملاحظة لهذه الخلية).

لعرض الملاحظة ، حرك مؤشر الماوس فوق الخلية (نتيجة لذلك ، ستظهر نافذة بها نص الملاحظة).

قم بتحرير الملاحظة للقيام بذلك:

• تنشيط الخلية B2 ؛

  حدد عنصر القائمة إدراج ® تحرير الملاحظة ؛

  تغيير نص الملاحظة (أضف رقم المجموعة) ؛

  انقر خارج الإطار.

الدرس مخصص لكيفية حل المهمة السابعة لامتحان علوم الكمبيوتر

الموضوع السابع - "جداول بيانات Excel" - يتميز بأنه مهام ذات مستوى أساسي من التعقيد ، مدة التنفيذ حوالي 3 دقائق ، الحد الأقصى للدرجة هو 1

* بعض صور الصفحة مأخوذة من مواد العرض التقديمي لـ K. Polyakov

أنواع ارتباط الخلية

الصيغ المكتوبة في خلايا الجدول هي نسبيا, مطلقو مختلط.

وظائف Excel القياسية

في الاستخدام ، توجد الوظائف القياسية التالية في الصيغ:

• COUNT - عدد الخلايا غير الفارغة ،
• SUM هو المبلغ
• AVERAGE - متوسط ​​القيمة ،
• MIN هي الحد الأدنى للقيمة ،
• MAX - القيمة القصوى

كمعامل دالة ، يُشار إلى نطاق من الخلايا في كل مكان: MIN (A2: A240)

ضع في اعتبارك أنه عند استخدام وظيفة AVERAGE ، لا يتم أخذ الخلايا والخلايا النصية الفارغة في الاعتبار ؛ على سبيل المثال ، بعد إدخال الصيغة في C2ستظهر القيمة 2 (لا تعد فارغة أ 2):

التخطيط

حل مهام الاستخدام في علوم الكمبيوتر

ضع في اعتبارك كيف يتم حل المهمة 7 من الاستخدام في علوم الكمبيوتر.

تحليل الرسم البياني

7_1:

أي من الرسوم البيانية يعكس بشكل صحيح نسبة العدد الإجمالي للمشاركين (من جميع المناطق الثلاث) لكل من المشاركين في الاختبار؟

✍ الحل:

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

400 + 100 + 200 + 400 + 200 + 200 + 400 + 300 + 200 = 2400

باستخدام مخطط دائري ، من الممكن تحديد حصص المكونات الفردية فقط بالمبلغ الإجمالي: في حالتنا ، هذه هي حصص المشاركين في مواضيع الاختبار المختلفة ؛

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

علم الأحياء: 1200/2400 = 0.5 = 50٪ التاريخ: 600/2400 = 0.25 = 25٪ الكيمياء: 600/2400 = 0.25 = 25٪

الآن دعنا نقارن البيانات التي تم الحصول عليها مع المخططات الدائرية. تتوافق البيانات مع الرسم التخطيطي الموجود أسفل الرقم 1 .

نتيجة: 1

نقدم لكم مشاهدة تحليل مفصل لهذه المهمة السبعة على الفيديو:

7_2:

يوضح الرسم التخطيطي عدد المشاركين في الاختبار حسب الموضوع في مناطق مختلفة من روسيا.


أي من الرسوم البيانية يعكس بشكل صحيح نسبة عدد المشاركين في الاختبار حسب التاريخ في المناطق؟

✍ الحل:

نتيجة: 2

للحصول على تحليل مفصل للمهمة ، انظر الفيديو:

نسخ الصيغ

7_3: امتحان الدولة الموحد في المعلوماتية 2016 ، "مهام الاختبار النموذجية في المعلوماتية" ، Krylova S.S. ، Churkina T.E. الخيار 2.:

يتم إعطاء جزء من جدول البيانات.

من الخلية A3في خلية C2
C2?

✍ الحل:

نتيجة: 180

لتحليل هذه المهمة السبع ، انظر الفيديو:

7_4: الاستخدام في المعلوماتية 2017 ، "مهام الاختبار النموذجية في المعلوماتية" ، Krylova S.S. ، Churkina T.E. الخيار 5:

A3في خلية ه 2تم نسخ الصيغة. عند النسخ ، تم تغيير عناوين الخلايا تلقائيًا.
ما هي القيمة الرقمية للصيغة في الخلية ه 2?

✍ الحل:

• ضع في اعتبارك صيغة في خلية A3: = $ E $ 1 * A2. تعني علامة الدولار العنونة المطلقة: عند نسخ الصيغة ، لن يتغير الحرف أو الرقم المجاور للدولار. هذا هو ، في حالتنا ، العامل 1 دولار أمريكيلذلك سيبقى في الصيغة عند النسخ.
• بما أن النسخ يتم في خلية ه 2، تحتاج إلى حساب عدد الأعمدة التي ستنتقل إليها الصيغة جهة اليمين: 5 أعمدة (من أقبل ه). تبعا لذلك ، في العامل أ 2رسالة أسيتم استبداله بـ ه.
• لنحسب الآن عدد الأسطر التي ستتحرك الصيغة لأعلى عند نسخها: واحد (ج أ 3 إصبع قدم 2 ). تبعا لذلك ، في العامل أ 2رقم 2 سيتم استبداله بـ 1 .
• احصل على الصيغة واحسب النتيجة: = $ E $ 1 * E1 = 1

نتيجة: 1

7_5: 7 مهمة. نسخة تجريبية من امتحان المعلوماتية 2018:

يتم إعطاء جزء من جدول البيانات. من الخلية ب 3في خلية A4تم نسخ الصيغة. عند نسخ عناوين الخلايا في الصيغة ، يتم تغييرها تلقائيًا.
ما هي القيمة الرقمية للصيغة في الخلية A4?


ملاحظة: تشير علامة $ إلى العنونة المطلقة.

✍ حل المهمة 7:

• تعني علامة الدولار $ العنونة المطلقة:
• يعني $ الموجود أمام الحرف أن العمود ثابت: أي عند نسخ الصيغة ، لن يتغير اسم العمود ؛
• يعني $ أمام الرقم أن السطر ثابت: عند نسخ الصيغة ، لن يتغير اسم السطر.
• في حالتنا ، لن تتغير الأحرف والأرقام المحددة: = ج 2 + د $3
• نسخ الصيغة عمود واحد إلى اليسار يعني أن الحرف د(في D $ 3) يجب أن يتغير إلى السابق ج. عند نسخ الصيغة لأسفل سطر واحد ، القيمة 2 (في $ C2) يتغير إلى 3 .
• نحصل على الصيغة:

= C3 دولار + 3 دولارات كندية

نتيجة لذلك ، لدينا النتيجة: 300 + 300 = 600

نتيجة: 600

للحصول على حل مفصل لهذه المهمة السبعة من النسخة التجريبية USE لعام 2018 ، شاهد الفيديو:

ما هي الصيغة التي كتبت

7_6: 7 ـ مهمة الامتحان. المهمة 6 GVE الصف الحادي عشر 2018 (FIPI)

يحتاج Kolya إلى إنشاء جدول لقيم الصيغة باستخدام جداول البيانات 5x – 3yمن أجل القيم Xو فيمن 2 قبل 5 . للقيام بذلك ، أولاً في النطاقات B1: E1و ج 2: A5قام بتدوين الأرقام 2 قبل 5 . ثم إلى الخلية في 2اكتب الصيغة (A2 - x value ؛ B1 - y value) ، ثم نسخها إلى جميع خلايا النطاق B2: E5. والنتيجة هي الجدول أدناه.


ما هي الصيغة التي تمت كتابتها في الخلية في 2?

ملاحظة: تُستخدم علامة $ للدلالة على العنونة المطلقة.

خيارات:
1) = 5 * دولارات أسترالية 2–3 * دولار 1 دولار
2) = 5 * $ A2–3 * B $ 1
3) = 5 * دولار أمريكي 2–3 * دولار أمريكي B1
4) = 5 * A2–3 * 1 مليار دولار أمريكي

✍ الحل:

• تخيل عقليًا نسخ خلية باستخدام صيغة منفصلة أفقيًا وعموديًا.

أفقيًا:

• مرجع العمود في الصيغة لكنيجب عدم تغيير الحرف عند النسخ ، مما يعني أنه يجب عليك وضع علامة $ أمامه:

= 5 * دولار أ

بينما اسم العمود بيجب أن تتغير (إلى C ، D ، E) بحيث تتغير الأرقام في التغير المطروح (3 ، 4 ، 5):

= 3 * ب

عموديا:

يجب أن يتغير رقم السطر في الحد الأدنى بحيث تزداد الأرقام الموجودة فيه (3 ، 4 ، 5). في حين أن الأسطر في المطروح يجب ألا تتغير: A2 دولار. وبالتالي ، من الضروري وضع علامة $ قبل رقم السطر في الحد الأدنى: ب 1 دولار

نتيجة لذلك ، نحصل على الصيغة: = 5 * $ A2 - 3 * B $ 1الذي يتوافق مع الرقم 2 .

نتيجة: 2

معنى صيغة SUM أو AVERAGE

7_7: الاستخدام في مهمة علوم الكمبيوتر 7 (مثال على المهمة P-00 ، Polyakov K.)

لكل

كيف ستتغير قيمة الخلية ج 3، إذا قمت بعد إدخال الصيغ بنقل محتويات الخلية B2في ب 3?
("+1"يعني زيادة 1 , "-واحد"يعني انخفاض في 1 ):

خيارات:
1) -2
2) -1
3) 0
4) +1

✍ الحل:

دعنا نحلل بيانات جدول البيانات قبل الانتقال:
• في الخلية C2الرقم سيكون 4 ، منذ الوظيفة التحقق منتحسب عدد الخلايا غير الفارغة في النطاق المحدد.
• في الخلية ج 3الرقم سيكون 3 :

(1 + 2 + 2 + 6 + 4) / 5 = 3

لنرى الآن ما سيحدث بعد الانتقال:

نقل محتويات خلية يعني أن الخلية B2ستكون فارغة ، وفي الخلية ب 3سيظهر رقم 6 .

ثم حساب الصيغة في الخلية C2سيتغير: عدد الخلايا غير الفارغة في النطاق A1: B2يصبح متساويا 3 .

ستتغير القيمة وفقًا لذلك بعد حساب صيغة الخلية ج 3: متوسط ​​قيمة محتويات نطاق من الخلايا A1: C2يصبح مساويا لـ:

(1 + 2 + 2 + 3) / 4 = 2

(لا تنس أن الوظيفة معدللا تأخذ في الاعتبار الخلايا الفارغة ، وبالتالي فإن الخلية B2لا تؤخذ بعين الاعتبار).

وبالتالي ، تغيرت القيمة بعد تحريك الصيغة ، وتناقصت بمقدار 1 . اجابة صحيحة 2

نتيجة: 2

حل فيديو مفصل:

7_8:

في جدول البيانات ، قيمة الصيغة = AVERAGE (C2: C5) هي 3 .

ما هي قيمة الصيغة = SUM (C2: C4) إذا كانت قيمة الخلية C5يساوي 5 ?

✍ الحل:

• دور معدلتم تصميمه لحساب الوسط الحسابي لنطاق الخلايا المحدد. أولئك. في حالتنا ، متوسط ​​قيمة الخلايا C2 و C3 و C4 و C5.
• نتيجة الدالة = AVERAGE (C2: C5) معطاة بشرط ، دعنا نستبدلها بالصيغة:

(C2 + C3 + C4 + C5) / 4 = 3

لنأخذ كمية غير معروفة xواحصل على صيغة حساب متوسط ​​القيمة:

س / 4 = 3

لنجد x:

س = 3 * 4 = 12 -> C2 + C3 + C4 + C5 = 12

وفقًا للتعيين ، يجب أن تجد = SUM (С2: С4). معرفة القيمة في الخلية C5اطرحه من المجموع الناتج وابحث عن الإجابة:

C2 + C3 + C4 = C2 + C3 + C4 + C5 -C5 = = 12 - 5 = 7

نتيجة: 7

للحصول على حل مفصل ، شاهد الفيديو:

ما هو الرقم الذي يجب أن يكون في الخلية

7_9: الاستخدام في المعلوماتية 2017 ، خيار FIPI 7 (Krylov SS ، Churkina T.E.):

بالنظر إلى جزء من جدول بيانات:

أ 1للحصول على مخطط يستند إلى قيم الخلية A2: C2تطابق الصورة؟من المعروف أن جميع قيم الخلايا من النطاق المدروس غير سالبة.

✍ الحل:

• لدينا مخطط دائري يعرض حصص المكونات الفردية في المجموع. من صورة المخطط ، يمكن للمرء أن يحكم ، على الأرجح ، أن القيم في جميع خلايا الصيغة يجب أن تكون متساوية (أقسام الرسم التخطيطي متساوية بصريًا).
• أ 1 -> x:

A2: x + 4 - 3 = x + 1 B2: (5 * x + 5) / 5 C2: (x + 1) * (x - 4) = x 2-3 * x - 4

نظرًا لأن قطاعات الرسم البياني متساوية ، فإننا نساوي أي تعبيرين ناتجين (على سبيل المثال ، C2 = A2):

x²-3 ​​* x - 4 = x + 1 x²-4 * x - 5 = 0 x1.2 = (4 ± √16-4 * 1 * (-5)) / 2 = (4 ± 6) / 2 × 1 = 5 ، × 2 = -1

وفقًا لشرط المهمة ، يجب ألا يكون الرقم سالبًا ، لذا فإن الرقم 5 يناسبنا

نتيجة: 5

للحصول على تحليل أكثر تفصيلاً ، نقترح مشاهدة فيديو الحل لهذه المهمة السبعة من الامتحان في علوم الكمبيوتر:

ضع في اعتبارك مثالًا آخر لحل المهمة 7 من الاختبار في علوم الكمبيوتر:

7_10: الاستخدام في المعلوماتية 2017 المهمة 7 FIPI option 15 (Krylov SS، Churkina T.E.):

بالنظر إلى جزء من جدول بيانات:

ما هو العدد الصحيح الذي يجب كتابته في الخلية C1بحيث يتم إنشاء المخطط بعد إجراء العمليات الحسابية بواسطة قيم نطاق الخلايا A2: C2هل تطابق الصورة؟
من المعروف أن جميع قيم النطاق الذي يُبنى عليه المخطط لها نفس العلامة.

✍ الحل:

• يعرض المخطط الدائري نسب الأجزاء الفردية في الإجمالي. في حالتنا ، يعكس المخطط نتائج حساب الصيغ في الخلايا A2: C2
• من المخطط ، يمكن للمرء أن يحكم على أنه ، على الأرجح ، يجب أن تكون القيم التي تم الحصول عليها في الصيغ في جميع الخلايا متساوية (قطاعات الرسم التخطيطي متساوية بصريًا).
• احصل على تعبيرات من صيغ الخلايا عن طريق الاستبدال C1 -> x:

A2: x + 2 B2: 8/2 = 4 C2: x * 2

نظرًا لأن قطاعات الرسم البياني متساوية ، فإننا نساوي اثنين من التعبيرات التي تم الحصول عليها (على سبيل المثال ، C2 = B2):

2 * س = 4 => س = 2

مهمتنا هي تحديد وإدخال الصيغة المثلى للخلية B2 التي تحدد مضاعفة العوامل الأولى. للوهلة الأولى ، يبدو أن هذه هي الصيغة التالية:

A2 * B1

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

أرز. 3.2

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

الروابط المطلقة والنسبية

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

يشير إلى خلية تتكون من خلية واحدة أعلى وإلى يسار الخلية B2 (أي الخلية A1). بعد نسخ محتويات الخلية B2 إلى الخلية C4 ، لن تشير الصيغة الموجودة في الخلية C4 بعد ذلك إلى الخلية A1 ، ولكن إلى الخلية B3 (أي إلى الخلية الموجودة فوق خلية واحدة أعلى وإلى يسار الخلية C4). وبالتالي ، عند نسخ محتويات خلية إلى أي مكان في ورقة العمل ، لن تشير الصيغة الموجودة فيها إلى خلية معينة ، ولكن إلى خلية تقع على مسافة ما من الخلية التي تحتوي على الصيغة. الخطوط المنقطة في الشكل. 3.3 يشير إلى مكان إعادة توجيه الارتباط عند نسخ الصيغة من الخلية B2 إلى الخلايا C4 و D6.

أرز. 3.3تغيير المرجع عند نسخ صيغة بمرجع نسبي إلى الخلية A1 من الخلية B2 إلى الخليتين C4 و D6

إذا قمت بنسخ هذه الصيغة إلى أي خلية في الصف 1 أو العمود A ، فسيظهر النقش التالي في الخلية وشريط الصيغة:

ملاحظة.

على التين. 3.3 في الجزء السفلي من نافذة Excel ، توجد لوحة الرسم ، والتي تم بها رسم الأسهم.

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

هذا هو مبين في الشكل. 3.4 ، حيث يتم نسخ الصيغة ذات المرجع المطلق للخلية A1 من الخلية B2 إلى الخليتين C4 و D6.

أرز. 3.4.عند نسخ صيغة بمرجع مطلق ، لا يتغير المرجع

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

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

وبالمثل ، إذا قمت بنسخ الخلية B2 إلى موقع آخر باستخدام الصيغة:

عندها سيتغير رقم الصف فقط ، لكن اسم العمود سيبقى كما هو. لذلك ، نحن نتحدث هنا عن مرجع عمود مطلق ومرجع صف نسبي.

توضح الأشكال أدناه استخدام مرجع صف مطلق (الشكل 3.6) ومرجع عمود مطلق (الشكل 3.5) في الخلية B2. ينتج عن نسخ هذه الصيغة في الخلايا C4 و D6 صيغ مختلفة.

أرز. 3.5نتيجة نسخ الصيغة من الخلية B2 إلى الخليتين C4 و D6 ، والتي تتضمن مرجع صف نسبي ومرجع عمود مطلق في الخلية A1

أرز. 3.6نتيجة نسخ الصيغة من الخلية B2 إلى الخليتين C4 و D6 ، والتي تتضمن مرجعًا مطلقًا للصف ومرجع نسبي إلى عمود الخلية A1

يتم تغيير نوع الارتباط دوريًا ، نتيجة للضغط المتتالي على مفتاح الوظيفة أثناء وجود مؤشر الإدخال في نص الرابط. على سبيل المثال ، إذا كان هناك ارتباط بالخلية A1 في الخلية B2. ثم في كل مرة تضغط فيها على المفتاح ، سيتغير مظهره في شريط الصيغة: A1 - $ A $ 1 - A $ 1 - $ A1 - A1 - $ A $ 1 ، إلخ.

إدخال صيغة مرجعية مختلطة في منطقة الحساب

لتعبئة منطقة الحساب بالصيغ بسرعة ، تحتاج في الخلية B2 إلى إدخال صيغة بمرجع مختلط:

= $ A2 * B $ 1

يمكن نسخ هذه الصيغة بأمان إلى خلايا أخرى - سيتم تنفيذ العمليات الحسابية في الجدول بشكل صحيح. عند النسخ في المكون الأول من الصيغة ، يتغير رقم الصف فقط (= $ A2 ، $ 3 ، $ A4 ، وما إلى ذلك) ، ويظل المرجع إلى العمود الذي توجد فيه عناصر العامل 1 ثابتًا. في المكون الثاني ، على العكس من ذلك ، يتغير اسم العمود (= C $ 1 ، D $ 1 ، E $ 1 ، إلخ) ، والإشارة إلى الصف حيث توجد عناصر العامل 2 تظل ثابتة. لإدخال صيغة الضرب في منطقة الحساب بالجدول ، سنستخدم طريقة تعبئة الخلايا بنفس البيانات. في هذه الحالة ، يكون تسلسل الإجراءات التالي هو الأمثل:

• حدد نطاق الخلايا B2: K11.
• اكتب علامة "=" على لوحة المفاتيح واضغط على المفتاح لإنشاء ارتباط بالخلية A2.
• اضغط على مفتاح الوظيفة ثلاث مرات (إنشاء مرجع مطلق للعمود A).
• أدخل علامة "*" (مضاعفة) واضغط على المفتاح لإنشاء ارتباط إلى الخلية B1.
• اضغط على مفتاح الوظيفة مرتين (إنشاء رابط مطلق للصف 1).
• اضغط على مجموعة المفاتيح.

يظهر جدول الضرب بالصيغ ونتائج الحساب في الشكل. 3.7 و 3.8.

عند استخدام مواد من هذا الموقع - ووضع اللافتة إلزامي !!!

اختبار العمل على الموضوع: "الصيغ والوظائف في Excel"

العمل العملي المجهز:إرمولايفا ناتاليا نيكولاييفنا ، مدرس علوم الكمبيوتر ، البريد الإلكتروني: [البريد الإلكتروني محمي]

اختبار.

الخيار 1.

1. تحديد نوع الروابط: A $ 10؛ 8 دولارات دوالر ؛ F5

2- ابحث عن قيمة الخلية C2:

3. تم إدخال الصيغة = $ A1 + $ B $ 2-B1 في الخلية B2. ما هي الصيغة التي سيتم الحصول عليها بعد نسخ المعينة في الخلية D3؟

4. في جدول البيانات ، قيمة الصيغة = SUM (C1: C3) هي 12. ما قيمة الخلية C4 إذا كانت قيمة الصيغة = AVERAGE (C1: C4) تساوي 5؟

5. ما هي قيم A2 في الخلية B6 ، حيث تتم كتابة الصيغة = IF (AND (A2<10;А2>5) ؛ 1 ؛ 0) ، سيعرض الرقم 1؟

اختبار.

الخيار 2.

1. تحديد نوع الروابط: A3 ؛ C7 دولارات أمريكية ؛ 12 دولارًا أمريكيًا.

2- ابحث عن قيمة الخلية C2:

3. تم إدخال الصيغة = A $ 1- $ B $ 1-B3 في الخلية A1. ما هي الصيغة التي سيتم الحصول عليها بعد نسخ المعينة في الخلية C4؟

4. في جدول البيانات ، قيمة الصيغة = SUM (A1: A2) هي 7. ما هي قيمة الخلية A3 إذا كانت قيمة الصيغة = AVERAGE (A1: A3) تساوي 3؟

5. ما هي قيم B1 في الخلية C4 ، حيث تكون الصيغة = IF (AND (B1<7;B1>= 12) ؛ 1 ؛ 0) ، سيعرض الرقم 0؟

اختبار.

الخيار 3.

1. تحديد نوع الروابط: $ B $ 5؛ A12 ؛ E4 دولارات.

2- ابحث عن قيمة الخلية C2:

3. تم إدخال الصيغة = $ A $ 1 + B1-C $ 1 في الخلية C1. ما هي الصيغة التي سيتم الحصول عليها بعد نسخ المعينة في الخلية D5؟

4. في جدول البيانات ، قيمة الصيغة = SUM (B1: D4) هي 13. ما هي قيمة الخلية B4 إذا كانت قيمة الصيغة = AVERAGE (B1: D3) تساوي 3؟

5. ما هي قيم A1 في الخلية B5 ، حيث تتم كتابة الصيغة = IF (AND (A1<=5;А1>2) ؛ 1 ؛ 0) ، سيعرض الرقم 1؟

اختبار.

الخيار 4.

1. تحديد نوع الروابط: D11 ؛ 5 دولارات أمريكية ؛ 1 دولار أسترالي.

2- ابحث عن قيمة الخلية C2:

3. تحتوي الخلية A2 على الصيغة = $ A2 + $ A $ 1-B1. ما الصيغة التي سيتم الحصول عليها بعد نسخ المعطى المعطى في الخلية F3؟

4. في جدول البيانات ، قيمة الصيغة = SUM (D1: D3) هي 7. ما هي قيمة الخلية D3 إذا كانت قيمة الصيغة = AVERAGE (D1: D2) تساوي 3؟

5. ما هي قيم B2 في الخلية C4 ، حيث تكون الصيغة = IF (AND (B2<7;B2>= 11) ؛ 1 ؛ 0) ، سيعرض الرقم 0؟

الإجابات:

1. A $ 10 - مختلط $ D $ 8 - مطلق F5 - نسبي
2. A1 = 1 A2 = 10 B1 = 2 B2 = 12 C1 = 17 C2 = 22
3. = $ A2 + $ B $ 2-D2
4. C1 + C2 + C3 = 12

(C1 + C2 + C3 + C4) / 4 = 5

1. A3 - نسبي $ С7 - مختلط ؛ $ E $ 12 - مطلق
2. A1 = 10 A2 = 6 B1 = 4 B2 = 5 C1 = 1 C2 = 4
3. = C $ 1- $ B $ 1-D6
4. A1 + A2 = 7

1. $ B $ 5 - مطلق ؛ A12 - نسبي ؛ E4 دولار - مختلط
2. A1 = 8 A2 = 2 B1 = 4 B2 = 2 C1 = 6 C2 = 15
3. = دولار أسترالي 1 دولار + C5-د 1 دولار
4. B1 + B2 + B3 + B4 \ u003d 13

(B1 + B2 + B3) / 3 = 3

1. D11 - نسبي ؛ F $ 5 - مختلط ؛ 1 دولار أسترالي - مطلق
2. A1 = 3 A2 = 3 B1 = 1 B2 = 7 C1 = 8 C2 = 13
3. = A3 دولار أمريكي + دولار أسترالي 1 دولار أمريكي - G2
4. D1 + D2 + D3 = 7