دوال الاكسل: كل دالة مع ورقة تفاعلية
شرح صيغ ودوال الاكسل على أوراق يمكنك تعديلها: VLOOKUP وXLOOKUP وIF وSUMIF وCOUNTIF والتواريخ والنصوص والمصفوفات الديناميكية. غيّر رقمًا أو صيغة وستعيد الورقة الحساب في متصفحك.
ابدأ الدورةأساسيات الصيغ
- SUMاكتب =SUM(B2:B6) تحت عمود من الأرقام لجمعها، أو اضغط Alt+= ليكتب الجمع التلقائي الصيغة عنك. اجمع الصفوف والخلايا المتفرقة والأوراق الأخرى على أوراق حية يمكنك تعديلها.
- الطرحلا توجد في الاكسل دالة SUBTRACT: اكتب =B2-C2 لطرح خلية من أخرى. اطرح عمودًا كاملًا أو عدة خلايا دفعة واحدة أو نسبة مئوية أو تاريخًا، على أوراق حية يمكنك تعديلها.
- الضرب والقسمةاضرب في الاكسل بعلامة النجمة =B2*C2، واقسم بالشرطة المائلة =B2/C2. اضرب عمودًا في رقم واحد، واستخدم PRODUCT، وتخلص من أخطاء #DIV/0!، على أوراق حية يمكنك تعديلها.
- AVERAGEتجمع =AVERAGE(B2:B7) الأرقام في B2:B7 وتقسم على عددها. تعلّم كيف تغيّر الخلايا الفارغة والأصفار النتيجة، وكيف تتجاهل الأصفار، وكيف تحسب متوسط أعلى 3 قيم.
- COUNT وCOUNTAتعدّ =COUNT(B2:B8) الخلايا التي تحتوي على أرقام، وتعدّ =COUNTA(B2:B8) كل خلية غير فارغة، وتعدّ =COUNTBLANK(B2:B8) الخلايا الفارغة. شاهد الثلاث على ورقة يمكنك تعديلها.
- المرجع المطلقالمرجع المطلق مثل $E$1 يبقى كما هو عند نسخ الصيغة، بينما يتحرك المرجع النسبي مثل E1 معها. اضغط F4 لإضافة علامات الدولار. شاهد الفرق على أوراق يمكنك تعديلها.
- النسبة المئويةصيغة النسبة المئوية في الاكسل هي =الجزء/الكل، مثل =B2/C2، مع تنسيق الخلية كنسبة مئوية. النسبة من المجموع، ونسبة من رقم، وإضافة نسبة أو خصمها، على أوراق حية.
- نسبة التغيرصيغة نسبة التغير في الاكسل هي =(الجديد-القديم)/القديم، مثل =(C2-B2)/B2، بتنسيق نسبة مئوية. النتيجة السالبة نقصان. أوراق حية للتغير الشهري، والبداية من صفر، والنقاط المئوية.
المنطق
- IFتتحقق =IF(B2>=50,"Pass","Fail") مما إذا كانت B2 تساوي 50 أو أكثر، فتعيد Pass إن كانت كذلك وFail إن لم تكن. تعلّم صيغة IF، وIF مع النص، وIF مع عملية حسابية، وIF للخلية الفارغة، والأخطاء التي تجعل IF تعيد نتيجة خاطئة.
- IF المتداخلةتضع =IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) دالة IF داخل أخرى للاختيار بين أكثر من نتيجتين. تعلّم كيف تُقرأ IF المتداخلة، ولماذا يهم ترتيب الشروط، ومتى تكون IFS أو جدول البحث خيارًا أفضل.
- IFSتختبر =IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F") كل شرط بالترتيب وتعيد القيمة المقترنة بأول شرط يكون TRUE. تعلّم صيغة IFS، والقيمة الافتراضية TRUE، ولماذا تعيد IFS الخطأ #N/A، وكيف تقارن بدالة IF المتداخلة.
- AND وOR وNOTتعيد =AND(B2>=10,B2<=20) القيمة TRUE فقط عندما يكون كل شرط صحيحًا، وتعيد =OR(B2="North",B2="South") القيمة TRUE عندما يصح شرط واحد على الأقل. تعلّم AND وOR وNOT وXOR وحدها وداخل IF، وكيف تختبر وقوع رقم بين قيمتين، وكيف تكتب AND وOR في صيغ المصفوفات.
- IFERRORتعيد =IFERROR(B2/C2,0) ناتج B2/C2، أو 0 عندما تعطي القسمة خطأ. تعلّم IFERROR مع VLOOKUP، وإعادة خلية فارغة بدل الخطأ، ولماذا IFNA خيار أفضل للبحث، ولماذا قد يخفي إخفاء كل الأخطاء أخطاء حقيقية.
- SWITCHتقارن =SWITCH(B2,"N","North","S","South","Unknown") الخلية B2 بكل قيمة بالترتيب وتعيد النتيجة المقترنة بأول تطابق تام، أو Unknown عندما لا يتطابق شيء. تعلّم صيغة SWITCH، والقيمة الافتراضية، ونمط SWITCH(TRUE,...)، ومتى تستخدم IFS أو IF المتداخلة بدلًا منها.
- ISBLANK وISNUMBERتعيد =ISBLANK(B2) القيمة TRUE عندما تكون B2 فارغة، وتعيد =ISNUMBER(B2) القيمة TRUE عندما تحتوي B2 على رقم. تعلّم ISBLANK وISNUMBER وISTEXT وISERROR وISNA وISEVEN وISODD، ولماذا لا تُعدّ الصيغة التي تعيد "" فارغة، وكيف تتحقق ISNUMBER(SEARCH()) من أن خلية تحتوي على نص.
البحث
- VLOOKUPتبحث =VLOOKUP(F2,A2:D6,3,FALSE) عن F2 في العمود الأول من A2:D6 وتعيد القيمة من العمود الثالث في الصف نفسه. المطابقة التامة والتقريبية، وحل #N/A، والبحث في ورقة أخرى، والبحث بشرطين.
- XLOOKUPتبحث =XLOOKUP(F2,A2:A6,C2:C6) عن F2 في A2:A6 وتعيد القيمة الموجودة في الصف نفسه من C2:C6. نص عند عدم العثور، وعدة أعمدة دفعة واحدة، والبحث إلى اليسار، وآخر تطابق، والمطابقة التقريبية وبأحرف البدل.
- INDEX MATCHتجد =INDEX(C2:C6,MATCH(F2,A2:A6,0)) صف F2 في العمود A وتعيد القيمة من ذلك الصف في العمود C. تبحث إلى اليسار، وتبحث في اتجاهين، وتعمل في كل إصدارات الاكسل.
- INDEXتعيد =INDEX(A2:C6,3,2) القيمة الموجودة في الصف الثالث والعمود الثاني من A2:C6. استخدمها للعنصر رقم n في قائمة، ولصف أو عمود كامل، وللقيمة في موضع وجدته MATCH.
- MATCHتعيد =MATCH(E2,A2:A6,0) موضع E2 داخل A2:A6: 4 إذا كانت العنصر الرابع. أنواع المطابقة 0 و1 و-1، وأحرف البدل، والمطابقة الحساسة لحالة الأحرف، والتحقق من وجود قيمة في قائمة.
- HLOOKUPتبحث =HLOOKUP("Mar",A1:E3,2,FALSE) عن Mar في الصف الأول من A1:E3 وتعيد القيمة من الصف الثاني في العمود نفسه. المطابقة التامة والتقريبية، ومتى تكون XLOOKUP خيارًا أفضل.
- XMATCHتعيد =XMATCH(E2,A2:A6) موضع E2 داخل A2:A6، بمطابقة تامة افتراضيًا. ويمكنها أيضًا إيجاد القيمة الأصغر أو الأكبر التالية دون ترتيب، والبحث من الأسفل، واستخدام أحرف البدل.
- البحث بعدة شروطتعيد =XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) القيمة من الصف الذي يطابق فيه العمود A الخلية E2 ويطابق فيه العمود B الخلية F2. ونسخة INDEX MATCH، وعمود مساعد لـ VLOOKUP، وFILTER لكل التطابقات.
- VLOOKUP مقابل XLOOKUPتفعل XLOOKUP كل ما تفعله VLOOKUP مع مطابقة تامة افتراضيًا، ودون رقم عمود، وبحث إلى اليسار، ووسيط لحالة عدم العثور. وتبقى VLOOKUP الخيار عندما يجب أن يُفتح الملف في Excel 2019 أو ما قبله.
- INDIRECTتقرأ =INDIRECT("C"&E2) الخلية التي يُبنى عنوانها كنص: العمود C، والصف E2. استخدمها لاختيار ورقة باسمها من خلية، وبناء نطاقات من أرقام، وإنشاء قوائم منسدلة تابعة.
- OFFSETتعيد =OFFSET(A1,3,2) الخلية التي تبعد 3 صفوف إلى الأسفل وعمودين أفقيًا عن A1. ومع ارتفاع تعيد نطاقًا كاملًا، وهكذا تجمع آخر N صفوف أو تبني متوسطًا متحركًا.
- CHOOSEتعيد =CHOOSE(B2,"Low","Medium","High") القيمة Low عندما تكون B2 تساوي 1، وMedium عندما تساوي 2، وHigh عندما تساوي 3. حوّل الأرقام إلى أسماء، واختر نطاقًا لجمعه، واستبدل IF متداخلة، واختر أعمدة بالدالة CHOOSECOLS.
العد والجمع بشرط
- COUNTIFتعدّ =COUNTIF(B2:B7,"North") الخلايا في B2:B7 التي تحتوي على North. العد حسب النص والأرقام وأحرف البدل والخلايا الفارغة والتواريخ، وإيجاد التكرارات، على أوراق حية يمكنك تعديلها.
- COUNTIFSتعدّ =COUNTIFS(A2:A7,"North",C2:C7,">50") الصفوف التي تكون فيها المنطقة North والمبيعات أكبر من 50. العد بين رقمين أو تاريخين، ومنطق OR، والخلايا الفارغة، على أوراق حية.
- SUMIFتجمع =SUMIF(A2:A7,"North",C2:C7) القيم في C2:C7 في الصفوف التي يكون فيها العمود A يساوي North. الجمع بشرط أكبر من، وإذا احتوى النص، وحسب التاريخ، ومن ورقة أخرى، على أوراق حية يمكنك تعديلها.
- SUMIFSتجمع =SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple") المبيعات في C2:C7 حيث تكون المنطقة North والمنتج Apple. نطاقات التواريخ، ومنطق OR، والمرشحات الاختيارية، على أوراق حية.
- AVERAGEIFتحسب =AVERAGEIF(A2:A7,"North",C2:C7) متوسط القيم في C2:C7 في الصفوف التي يكون فيها العمود A يساوي North. AVERAGEIFS لعدة شروط، والمتوسط مع تجاهل الأصفار، وحل #DIV/0!، وMAXIFS وMINIFS.
- عدّ الخلايا النصيةتعدّ =COUNTIF(A2:A8,"*") الخلايا في A2:A8 التي تحتوي على نص، وتتخطى الأرقام والتواريخ والخلايا الفارغة. عدّ الخلايا التي تحتوي على كلمة معينة، وإرجاع قيمة إذا احتوت الخلية على نص.
- COUNTIF غير الفارغةتعدّ =COUNTIF(B2:B8,"<>") الخلايا في B2:B8 غير الفارغة، مثل COUNTA تمامًا. أضف شروطًا أخرى بالدالة COUNTIFS، وتعامل مع الخلايا التي تبدو فارغة فقط.
- عدّ القيم الفريدةتعدّ =COUNTA(UNIQUE(A2:A9)) القيم المختلفة في A2:A9. وفي الإصدارات الأقدم من الاكسل استخدم =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)). عدّ القيم التي تظهر مرة واحدة، والعد بشرط، وتخطي الخلايا الفارغة.
- SUMPRODUCTتضرب =SUMPRODUCT(B2:B6,C2:C6) كل كمية في سعرها وتجمع النتائج. ومع شروط مثل (A2:A7="North")*C2:C7 تجمع وتعدّ حيث لا تستطيع SUMIFS: حسب الشهر، وعمود مقابل عمود، ومع OR.
- SUBTOTALتجمع =SUBTOTAL(9,C2:C8) النطاق C2:C8 مثل SUM لكنها تتجاهل صفوف SUBTOTAL الأخرى داخل النطاق والصفوف التي أخفتها التصفية. رقما الدالة 9 و109، وعدّ الصفوف الظاهرة، وAGGREGATE لتخطي الأخطاء.
- المتوسط المرجحالصيغة =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) متوسط مرجح: تُضرب كل قيمة في وزنها، وتُجمع حواصل الضرب، ويُقسم الإجمالي على مجموع الأوزان. الدرجات، والمعدل التراكمي حسب الساعات، والأسعار حسب الكمية.
النصوص
- دمج النصوصتدمج `=A2&" "&B2` النص الموجود في A2 وB2 مع مسافة بينهما. وتؤدي CONCATENATE وCONCAT المهمة نفسها، وتُبقي TEXT الأرقام والتواريخ مقروءة عند دمجها.
- TEXTJOINتدمج `=TEXTJOIN(", ",TRUE,A2:A6)` كل خلايا A2:A6 في نص واحد، مع فاصلة ومسافة بين العناصر وتخطي الخلايا الفارغة. أضف FILTER لدمج الصفوف التي تحقق شرطًا فقط.
- فصل النصتعيد `=TEXTBEFORE(A2," ")` الاسم الأول من `Ana Silva`، وتعيد `=TEXTAFTER(A2," ")` اسم العائلة. تقسم TEXTSPLIT الخلية إلى عدة أعمدة دفعة واحدة، وتؤدي LEFT وMID وFIND المهمة نفسها في الإصدارات الأقدم.
- LEFT وRIGHT وMIDتعيد `=LEFT(A2,3)` أول 3 أحرف من A2، وتعيد `=RIGHT(A2,2)` آخر حرفين، وتعيد `=MID(A2,5,4)` أربعة أحرف بدءًا من الحرف الخامس. اجمعها مع FIND وLEN عندما يختلف الطول.
- FIND وSEARCHتعيد `=SEARCH("apple",A2)` الموضع الذي تبدأ فيه `apple` في A2، مع تجاهل حالة الأحرف. وتفعل FIND الشيء نفسه لكنها تميّز حالة الأحرف. تعيد الدالتان #VALUE! عندما يكون النص مفقودًا، وتحوّل ISNUMBER ذلك إلى اختبار "هل تحتوي الخلية على".
- SUBSTITUTE وREPLACEتحذف `=SUBSTITUTE(A2,"-","")` كل شرطة من A2: تستبدل SUBSTITUTE النص بمطابقته. أما REPLACE فتستبدل حسب الموضع: تكتب `=REPLACE(A2,1,3,"XYZ")` فوق أول 3 أحرف.
- TRIMتحذف `=TRIM(A2)` المسافات قبل النص في A2 وبعده، وتحوّل كل سلسلة من المسافات بين الكلمات إلى مسافة واحدة. وتحذف SUBSTITUTE كل المسافات أو المسافات غير المنقسمة التي تفوت TRIM.
- UPPER وLOWER وPROPERتحوّل `=UPPER(A2)` كل أحرف A2 إلى أحرف كبيرة، وتحوّلها `=LOWER(A2)` كلها إلى أحرف صغيرة، وتكبّر `=PROPER(A2)` الحرف الأول من كل كلمة. ولتكبير الحرف الأول من النص فقط، اجمع UPPER وLEFT وMID.
- LENتعيد `=LEN(A2)` عدد الأحرف في A2، بما في ذلك المسافات وعلامات الترقيم. ومع TRIM وSUBSTITUTE تعدّ الكلمات أيضًا، ومع SUM تعدّ الأحرف في نطاق كامل.
- TEXTتحوّل `=TEXT(A2,"mmm d, yyyy")` التاريخ في A2 إلى نص مثل `Mar 15, 2026`، وتحوّل `=TEXT(B2,"$#,##0.00")` الرقم 1250.5 إلى `$1,250.50`. النتيجة نص، فاستخدمها للتسميات لا لعمليات حسابية لاحقة.
- النص إلى رقمتحوّل `=VALUE(A2)` رقمًا مخزنًا كنص، مثل `'120`، إلى الرقم 120. وتفعل علامتا الطرح، `=--A2`، الشيء نفسه، وتتعامل NUMBERVALUE مع الفاصلة كفاصل عشري، ويصلح الأمر تحويل إلى رقم الخلايا في مكانها.
- سطر جديد في خليةاضغط Alt+Enter أثناء الكتابة في خلية لبدء سطر جديد فيها (Control+Option+Return على Mac). وفي الصيغة، `CHAR(10)` هو فاصل السطر: تضع `=A2&CHAR(10)&B2` الخلية B2 في سطر ثانٍ يظهر بعد تفعيل التفاف النص.
- الأصفار البادئةيحذف الاكسل الأصفار البادئة لأن `00742` هو الرقم 742. احتفظ بها بتنسيق أرقام مخصص مثل `00000`، أو بفاصلة عليا (`'00742`)، أو بتنسيق النص، أو أضفها بالصيغة `=TEXT(A2,"00000")`.
- أحرف البدلفي شروط الاكسل، `*` تمثل أي عدد من الأحرف و`?` حرفًا واحدًا بالضبط: تعدّ `=COUNTIF(A2:A7,"*apple*")` الخلايا التي تحتوي على `apple`. وتعيد `~` حرف البدل إلى حرف عادي.
التواريخ والأوقات
- حساب العمرتعيد =DATEDIF(B2,TODAY(),"Y") العمر بالسنوات الكاملة لشخص وُلد في التاريخ الموجود في B2. احسب العمر في تاريخ محدد، وبالسنوات والأشهر والأيام، ودون DATEDIF.
- DATEDIFتعدّ =DATEDIF(A2,B2,"M") الأشهر الكاملة بين تاريخ البداية في A2 وتاريخ النهاية في B2. الوحدات Y وM وD وYM وMD وYD، ولماذا تغيب DATEDIF عن قائمة الدوال، والخطأ #NUM!.
- الأيام بين تاريخينتعيد =B2-A2 عدد الأيام بين التاريخ في A2 والتاريخ اللاحق في B2. عدّ الأيام بالدالة DAYS، واحتسب التاريخين كليهما، واحصل على الأسابيع أو الأشهر أو السنوات أو أيام العمل بدلًا من ذلك.
- يوم الأسبوعتعيد =TEXT(A2,"dddd") اسم يوم التاريخ الموجود في A2، مثل Monday، وتعيده =WEEKDAY(A2) رقمًا. الأسماء المختصرة، وأنواع الإرجاع في WEEKDAY، والتحقق من عطلة نهاية الأسبوع.
- TODAY وNOWتعيد =TODAY() تاريخ اليوم وتعيد =NOW() التاريخ والوقت الحاليين، وتتحدث الاثنتان في كل مرة تُعاد فيها حسابات الورقة. عدّ الأيام المتبقية حتى تاريخ معين، وأدرج تاريخًا لا يتغير أبدًا بالاختصار Ctrl+;.
- إضافة أيام وأشهرتعيد =A2+30 التاريخ الذي يلي A2 بثلاثين يومًا. لإضافة أشهر استخدم =EDATE(A2,3)، ولنهاية الشهر =EOMONTH(A2,0)، وللسنوات EDATE مع 12 شهرًا لكل سنة.
- NETWORKDAYS وWORKDAYتعدّ =NETWORKDAYS(A2,B2) أيام العمل (من الاثنين إلى الجمعة) من A2 إلى B2، مع التاريخين. وتعيد =WORKDAY(A2,10) التاريخ الذي يلي A2 بعشرة أيام عمل. وتستطيع الدالتان تخطي قائمة عطلات.
- DATE وYEAR وMONTH وDAYتعيد =DATE(2026,3,15) التاريخ 15 مارس 2026، من سنة وشهر ويوم. وتفكك YEAR وMONTH وDAY التاريخ إلى أجزائه، وتنقل DATE الشهر 13 إلى السنة التالية.
- حسابات الوقتتعيد =B2-A2 الوقت بين وقت البداية في A2 ووقت النهاية في B2: نسّقها بالتنسيق h:mm لترى 8:30، أو اضربها في 24 لتحصل على 8.5 ساعات. النوبات التي تتجاوز منتصف الليل، والإجماليات فوق 24 ساعة، والأجر من ساعات العمل.
- رقم الأسبوعتعيد =WEEKNUM(A2) رقم أسبوع التاريخ الموجود في A2، مع أسابيع تبدأ يوم الأحد. وتعيد =ISOWEEKNUM(A2) أسبوع ISO المستخدم في أوروبا، حيث تبدأ الأسابيع يوم الاثنين. تاريخ بداية الأسبوع، والتاريخ من رقم الأسبوع.
الرياضيات والإحصاء
- ROUNDتقرّب =ROUND(A2,2) الرقم الموجود في A2 إلى منزلتين عشريتين، وتقرّبه =ROUND(A2,0) إلى أقرب عدد صحيح. والأرقام السالبة تقرّب إلى العشرات والمئات والآلاف؛ وتقرّب MROUND إلى أي مضاعف.
- ROUNDUP وROUNDDOWNتقرّب =ROUNDUP(A2,0) دائمًا بعيدًا عن الصفر، فتصبح 2.1 هي 3، وتقرّب =ROUNDDOWN(A2,0) دائمًا نحو الصفر، فتصبح 2.9 هي 2. وتقرّب CEILING وFLOOR إلى الأعلى أو الأسفل إلى مضاعف، وتحذف INT وTRUNC الكسور العشرية.
- الانحراف المعياريتعطي =STDEV.S(B2:B9) الانحراف المعياري لعينة وتعطيه =STDEV.P(B2:B9) لمجتمع كامل. استخدم STDEV.S إلا إذا كانت بياناتك هي كل القيم الموجودة. وتعطي VAR.S وVAR.P التباين.
- RANKتعطي =RANK.EQ(B2,$B$2:$B$7) ترتيب B2 بين القيم في B2:B7، مع الترتيب 1 لأكبر قيمة. أضف 1 وسيطًا ثالثًا لترتيب الأصغر أولًا. القيم المتساوية تتشارك الترتيب؛ وترتّب COUNTIFS داخل مجموعة.
- الأرقام العشوائيةتعيد =RANDBETWEEN(1,100) عددًا صحيحًا عشوائيًا من 1 إلى 100، وتعيد =RAND() رقمًا عشريًا عشوائيًا من 0 حتى أقل من 1. تملأ RANDARRAY نطاقًا كاملًا، وتختار INDEX مع RANDBETWEEN عنصرًا عشوائيًا، ويجمّد لصق خاص > قيم النتائج.
- MOD وABSتعيد =MOD(A2,B2) باقي قسمة A2 على B2، لذا =MOD(17,5) تساوي 2. وتعيد =ABS(A2) الرقم دون إشارته، لذا =ABS(B2-C2) هي الفرق بين قيمتين أيًا كانت الأكبر.
- PMTتعيد =PMT(B2/12,B3*12,-B1) القسط الشهري لقرض قيمته B1 بالمعدل السنوي في B2 على مدى B3 سنة. اقسم المعدل على 12، واضرب السنوات في 12، وضع علامة طرح قبل مبلغ القرض لتحصل على قسط موجب.
- NPV وIRRتخصم =NPV(E2,B3:B5)+B2 التدفقات النقدية المستقبلية بالمعدل في E2 وتضيف الاستثمار الأولي في B2، الذي يجب ألا تخصمه NPV. وتعيد =IRR(B2:B5) المعدل الذي تكون عنده NPV صفرًا. وتأخذ XNPV وXIRR تواريخ حقيقية.
- CAGRتعطي =(B2/A2)^(1/C2)-1 معدل النمو السنوي المركب من قيمة بداية في A2 إلى قيمة نهاية في B2 على مدى C2 سنة. وتعيد =RRI(C2,A2,B2) المعدل نفسه. نسّق الخلية نسبة مئوية.
المصفوفات الديناميكية
- FILTERتعيد =FILTER(A2:C7,B2:B7="North") كل صف من A2:C7 منطقته North، وتتحدث النتيجة عندما تتغير البيانات. تعلّم الشروط المتعددة بالرمزين * و+، وif_empty، والخطأ #CALC!، وفرز النتيجة.
- UNIQUEتعيد =UNIQUE(B2:B8) كل قيمة في B2:B8 مرة واحدة، بترتيب ظهورها الأول، وتتحدث عندما تتغير القائمة. تعلّم الصفوف الفريدة، وexactly_once، والقائمة الفريدة المرتبة، وعدّ القيم الفريدة، واستخدام النتيجة مصدرًا لقائمة منسدلة.
- SORT وSORTBYتعيد =SORT(A2:C7,3,-1) الجدول A2:C7 مرتبًا حسب عموده الثالث، الأكبر أولًا، وتواصل إعادة الفرز عندما تتغير البيانات. وتفرز SORTBY حسب أي نطاق، بما في ذلك عدة أعمدة وترتيب مخصص.
- SEQUENCEتعيد =SEQUENCE(5) الأرقام من 1 إلى 5 في عمود، وتملأ =SEQUENCE(3,4) ثلاثة صفوف في أربعة أعمدة. أضف بداية وخطوة لأي سلسلة، بما في ذلك التواريخ، وترقيم الصفوف الذي يكبر مع القائمة، وتقويم شهري.
- TRANSPOSEتحوّل =TRANSPOSE(A1:D3) صفوف A1:D3 إلى أعمدة وتبقى مرتبطة بالمصدر. ولنسخة لمرة واحدة، استخدم لصق خاص > تبديل الموضع. وتكدّس TOCOL شبكة كاملة في عمود واحد.
- LETتحسب =LET(total,SUM(B2:B6),IF(total>500,total*0.9,total)) المجموع مرة واحدة، وتسميه total وتستخدم الاسم مرتين. تجعل LET الصيغ الطويلة أقصر وأسهل قراءة وأسرع، لأن كل جزء مسمّى يُحسب مرة واحدة فقط.
- LAMBDAتعرّف =LAMBDA(price,price*1.2)(B2) دالة صغيرة لها مدخل واحد، price، وتستدعيها على B2. احفظ LAMBDA في إدارة الأسماء لتستخدمها مثل دالة مدمجة، أو مررها إلى MAP وBYROW وSCAN وREDUCE.
الأخطاء وحلولها
- خطأ #SPILL!يعني #SPILL! أن صيغة تعيد عدة قيم لا تجد مكانًا لوضعها: خلية في نطاق امتدادها ليست فارغة. امسح الخلايا التي تعترض الطريق فتظهر النتيجة.
- خطأ #VALUE!يعني #VALUE! أن صيغة حصلت على نوع خاطئ من القيم، غالبًا نص حيث تحتاج إلى رقم: تفشل =B2+C2 عندما تحتوي C2 على "n/a" أو مسافة. وتتجاهل SUM النص، فتعمل =SUM(B2:C2).
- خطأ #NAME?يعني #NAME? أن الاكسل لا يتعرف على كلمة في الصيغة: دالة مكتوبة خطأ مثل =SUMM(B2:B6)، أو نص بلا علامات اقتباس، أو نقطتان مفقودتان في نطاق، أو اسم غير معرّف، أو دالة لا يحتوي عليها إصدار الاكسل لديك.
- خطأ #REF!يعني #REF! أن صيغة تشير إلى خلية لم تعد موجودة، عادة لأن صفًا أو عمودًا أو ورقة كانت تستخدمها حُذفت: تصبح =B2*C2 بالشكل =B2*#REF!. ويظهر أيضًا عندما تطلب VLOOKUP أو INDEX عمودًا أو صفًا خارج نطاقها.
- خطأ #N/Aيعني #N/A أن البحث لم يجد القيمة التي يبحث عنها. تحقق من الأخطاء الإملائية والمسافات الزائدة ونطاق الجدول الذي تحرك عند نسخ الصيغة إلى الأسفل، ثم استخدم IFNA لعرض رسالة للقيم المفقودة فعلًا.
- خطأ #DIV/0!يظهر #DIV/0! عندما تقسم صيغة على صفر أو على خلية فارغة، كما في =B2/C2 مع C2 فارغة. تعرض =IF(C2=0,"",B2/C2) خلية فارغة بدلًا منه، وتعيده AVERAGE أيضًا لنطاق لا أرقام فيه.
- المرجع الدائريالمرجع الدائري صيغة تشير إلى خليتها، مباشرة أو عبر صيغ أخرى، مثل =SUM(B2:B7) المكتوبة في B7. يحذّر الاكسل، ويعرض 0، ويذكر الخلية تحت صيغ > تدقيق الأخطاء > المراجع الدائرية.
- الصيغة لا تُحسبإذا عرض الاكسل الصيغة بدلًا من النتيجة، فالخلية منسقة كنص، أو تبدأ الصيغة بفاصلة عليا أو مسافة، أو إظهار الصيغ مفعّل. وإذا لم تتحدث النتائج، فالحساب مضبوط على يدوي: صيغ > خيارات الحساب > تلقائي.
أدوات البيانات
- إزالة التكراراتحدد البيانات وانقر بيانات > إزالة التكرارات لحذف الصفوف المكررة في مكانها، أو استخدم =UNIQUE(A2:A9) للحصول على نسخة نظيفة مع الاحتفاظ بالأصل. اعثر على التكرارات وميّزها وعدّها، واحذفها اعتمادًا على عمودين.
- تمييز التكراراتحدد الخلايا واختر الصفحة الرئيسية > التنسيق الشرطي > قواعد تمييز الخلايا > القيم المتكررة. وللصفوف الكاملة، أو النسخة الثانية فقط، أو التطابقات بين عمودين، استخدم قاعدة صيغة مثل =COUNTIF($A$2:$A$9,A2)>1.
- التنسيق الشرطييلوّن التنسيق الشرطي الخلية عندما يتحقق شرط. استخدم الصفحة الرئيسية > التنسيق الشرطي للقواعد الجاهزة، أو قاعدة جديدة > استخدام صيغة مع قاعدة مثل =$C2>100 لتلوين صفوف كاملة والتواريخ المتأخرة ومطابقات النص.
- القائمة المنسدلةحدد الخلايا، واذهب إلى بيانات > التحقق من صحة البيانات، واختر قائمة، واكتب العناصر (North,South,East) أو حدد نطاقًا كمصدر. ثم اجعل القائمة ديناميكية بالدالة UNIQUE، أو تابعة لقائمة أخرى، وابحث عن العنصر المختار.
- مقارنة عمودينلمقارنة عمودين صفًا بصف، استخدم =A2=B2 (أو EXACT لمراعاة حالة الأحرف). ولإيجاد القيم في عمود المفقودة من الآخر، استخدم COUNTIF أو MATCH أو XLOOKUP، وميّز الاختلافات بالتنسيق الشرطي.
- الجدول المحورييجمّع الجدول المحوري صفوف جدول حسب فئة ويحسب مجموع رقم لكل منها، دون صيغ: إدراج > PivotTable، ثم اسحب الحقول إلى الصفوف والقيم. إليك الخطوات، وشرح المناطق الأربع، والملخص نفسه مبنيًا بالصيغ.
