المرجع المطلق يُبقي الخلية ثابتة عند نسخ الصيغة. في =B2*$E$1، تثبّت علامات الدولار الخلية E1: انسخ الصيغة إلى الأسفل وسيبقى كل صف يضرب في E1، بينما تنتقل B2 إلى B3 وB4 وهكذا. لإضافة علامات الدولار، انقر المرجع في الصيغة واضغط F4 (على Mac: Cmd+T).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $425 | ||
| 4 | Chen | $15,200 | $760 | ||
| 5 | Dina | $9,800 | $490 | ||
| 6 | Eli | $11,000 | $550 |
كُتبت C2 مرة واحدة ونُسخت إلى الأسفل. انقر C4: صيغتها =B4*$E$1. انتقلت خلية المبيعات إلى الصف 4، وبقيت النسبة في E1. غيّر النسبة في E1 إلى 8% فتتحدث كل العمولات.
المراجع النسبية مقابل المطلقة
| المرجع | الاسم | بعد نسخه صفًا واحدًا إلى الأسفل وعمودًا واحدًا إلى اليمين |
|---|---|---|
A1 | نسبي | B2 |
$A$1 | مطلق | $A$1 |
A$1 | مختلط: الصف مثبّت | B$1 |
$A1 | مختلط: العمود مثبّت | $A2 |
المرجع العادي مثل B2 نسبي: يخزّنه الاكسل على أنه "الخلية التي في هذا الموضع مني"، لذا تشير النسخة التي في صف أدنى إلى صف أدنى. وهذا بالضبط ما تريده لبيانات كل صف، وهو السلوك الافتراضي. وعلامة $ قبل حرف العمود أو رقم الصف تثبّت ذلك الجزء.
الخطأ الشائع: النسخ إلى الأسفل بدون $
هذه ورقة العمولة مرة أخرى، مع =B2*E1 في C2 وبدون علامات دولار. الصف الأول صحيح. والبقية 0.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Commission | Rate | 5% |
| 2 | Ana | $12,000 | $600 | ||
| 3 | Ben | $8,500 | $0 | ||
| 4 | Chen | $15,200 | $0 | ||
| 5 | Dina | $9,800 | $0 | ||
| 6 | Eli | $11,000 | $0 |
تحتوي C3 على =B3*E2: انتقل مرجع النسبة إلى الأسفل إلى E2، وهي فارغة، والخلية الفارغة تُحسب 0. أصلح ذلك هنا: انقر C2، وغيّر الصيغة إلى =B2*$E$1 واضغط Enter. يتبعها العمود كله، لأن C3:C6 نسخ من C2. وعندما تكون الخلية الثابتة مقسومًا عليه، كما في =B2/B7 لحصة من مجموع، يُظهر الخطأ نفسه #DIV/0! بدلًا من 0 (النسبة من المجموع هي الحالة المعتادة).
اضغط F4 لإضافة علامات الدولار
أثناء كتابة صيغة أو تحريرها، ضع المؤشر داخل مرجع (أو بعده مباشرة) واضغط F4. كل ضغطة تنتقل إلى الشكل التالي:
E1 -> $E$1 -> E$1 -> $E1 -> E1
في كثير من الحواسيب المحمولة يتحكم F4 في الشاشة أو الصوت، لذا اضغط Fn+F4. على Mac، استخدم Cmd+T، أو Fn+F4. يمكنك أيضًا كتابة علامات $ بنفسك.
المراجع المختلطة: ثبّت الصف أو العمود فقط
المرجع المختلط فيه علامة دولار واحدة. $A2 يقرأ العمود A دائمًا لكنه يترك الصف يتحرك؛ وB$1 يقرأ الصف 1 دائمًا لكنه يترك العمود يتحرك. وباجتماعهما في صيغة واحدة، تبني صيغة واحدة منسوخة عبر شبكة جدول ضرب:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | x | 1 | 2 | 3 | 4 | 5 |
| 2 | 1 | 1 | 2 | 3 | 4 | 5 |
| 3 | 2 | 2 | 4 | 6 | 8 | 10 |
| 4 | 3 | 3 | 6 | 9 | 12 | 15 |
| 5 | 4 | 4 | 8 | 12 | 16 | 20 |
| 6 | 5 | 5 | 10 | 15 | 20 | 25 |
تحتوي B2 على =$A2*B$1. انقر F6: فيها =$A6*F$1، أي رقم الصف من العمود A في رقم العمود من الصف 1، لذا تعرض 25. احذف علامة دولار واحدة في B2 فينهار الجدول، لأن النسخ تبدأ بضرب الخلايا المجاورة بدلًا من العناوين.
والنمط نفسه يسعّر قائمة بعدة خصومات: =$A2*(1-B$1) مع الأسعار أسفل العمود A ونسب الخصم عبر الصف 1.
مجموع تراكمي بنطاق نصف مثبّت
يمكن تثبيت النطاق من طرف واحد فقط. تبدأ =SUM($B$2:B2) من B2 دائمًا، بينما ينزل طرفها الآخر مع نسخ الصيغة، فيجمع كل صف كل ما حتى نفسه:
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | Total so far |
| 2 | Jan | 420 | 420 |
| 3 | Feb | 380 | 800 |
| 4 | Mar | 510 | 1310 |
| 5 | Apr | 450 | 1760 |
| 6 | May | 470 | 2230 |
تحتوي C6 على =SUM($B$2:B6) وتعرض 2230، مجموع الأشهر الخمسة كلها. والنطاق نصف المثبّت نفسه يجعل =COUNTIF($A$2:A2,A2) تعدّ كم مرة ظهرت قيمة حتى الآن، وهكذا تُعلَّم التكرارات بعد الأول.
تمرين: صيغة واحدة للجدول كله
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Price | 10% | 20% | 30% |
| 2 | $40.00 | |||
| 3 | $25.00 | |||
| 4 | $60.00 | |||
| 5 | $18.00 |
دورك: في B2، اكتب سعر المنتج الأول بعد الخصم الموجود في B1. استخدم $ بحيث تعطي الصيغة نفسها، منسوخة أفقيًا وعموديًا حتى D5، كل سعر في الجدول.
تنسخ الورقة صيغتك في كل خلية من B2:D5، كما يفعل مقبض التعبئة، ويقرأ زر التحقق النتائج الاثنتي عشرة كلها. وبدون علامات الدولار الصحيحة، تقرأ النسخ في الصف 3 أو العمود C السعر الخطأ أو الخصم الخطأ.
المراجع المطلقة إلى ورقة أخرى أو جدول بحث
تعمل علامات الدولار بالطريقة نفسها مع اسم ورقة: =B2*Settings!$B$1. وهي أهم ما تكون في البحث، حيث يجب أن يبقى الجدول في مكانه بينما تتحرك قيمة البحث: تبقى =VLOOKUP(A2,$E$2:$F$10,2,FALSE) منسوخة إلى الأسفل تبحث في E2:F10، بينما تُنزل =VLOOKUP(A2,E2:F10,2,FALSE) الجدول صفًا مع كل نسخة وتبدأ بتفويت الصفوف الأولى (VLOOKUP). وإذا كانت خلية ثابتة مستخدمة في صيغ كثيرة، يمكنك أيضًا تسميتها من الصيغ > تعريف الاسم وكتابة =B2*Rate؛ الاسم المعرَّف بهذه الطريقة يشير إلى الخلية نفسها من كل صيغة، مثل $E$1.
الأسئلة الشائعة
ماذا تعني علامة $ في معادلة الاكسل؟
تثبّت جزء المرجع الذي يليها. في $E$1 يُثبَّت العمود E والصف 1 كلاهما، فيبقى المرجع E1 أينما نُسخت الصيغة. أما E$1 فتثبّت الصف فقط، و$E1 العمود فقط.
ما اختصار المرجع المطلق في الاكسل؟
انقر داخل المرجع أثناء تحرير الصيغة واضغط F4 (Fn+F4 في كثير من الحواسيب المحمولة). كل ضغطة تنتقل بين $A$1 وA$1 و$A1 وA1. على Mac، اضغط Cmd+T، أو Fn+F4.
ما الفرق بين المراجع النسبية والمطلقة؟
المرجع النسبي مثل B2 يتحرك عند نسخ الصيغة: بعد صف واحد إلى الأسفل يصبح B3. والمرجع المطلق مثل $B$2 يبقى $B$2. استخدم المراجع المطلقة لخلية واحدة يحتاجها كل صف، مثل نسبة أو مجموع.
ما المرجع المختلط في الاكسل؟
مرجع فيه علامة دولار واحدة: $A2 يثبّت العمود ويترك الصف يتحرك، وB$1 يثبّت الصف ويترك العمود يتحرك. والصيغة =$A2*B$1 منسوخة أفقيًا وعموديًا عبر شبكة تبني جدول ضرب.
لماذا تعرض صيغتي 0 أو #DIV/0! بعد سحبها إلى الأسفل؟
لأن مرجعًا كان يجب أن يبقى ثابتًا تحرك مع النسخ. إذا كانت في الصف 2 الصيغة =B2/B7، فسيحصل الصف 3 على =B3/B8، وB8 فارغة. ثبّت المجموع بالصيغة =B2/$B$7 وانسخ مجددًا.
