إكسل والصيغ الطويلة

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

ولكن أود أن أعرض بعض الأمثلة على استخدام وظائف ورقة عمل Excel القياسية. في كثير من الأحيان ، عليك أن تسيطر على الحديقة من الصيغ للحصول على النص أو الأرقام التي تحتاجها من الخلايا. يحدث هذا عادة بسبب العرض الخاطئ للبيانات. الأعداد أرقام ، النص نص ، لا تخلطها. التواريخ (والوقت) هي الأرقام التي من الممكن والضروري إجراء العمليات الحسابية عليها. لا تخلط 14 مارس 2018 من 14 مارس 2018. والثاني هو تنسيق التاريخ المخصص فقط [$ -F800] ، ما نراه في الخلية ، وعلى شريط الصيغة سيكون 14 مارس 2018. ويكفي ذلك.

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


في الصورة ، جزء من الجدول _t2. من الضروري حساب الحد الأقصى لعدد الأحداث المتزامنة. لهذا هناك صيغة:

=(((_t2[start]>=(_t2[start]))*(_t2[start]<=(_t2[end]));(_t2[start])^0))     =MAX(MMULT((_t2[start]>=TRANSPOSE(_t2[start]))*(_t2[start]<=TRANSPOSE(_t2[end])),ROW(_t2[start])^0)) 

الصيغة ضخمة ، يتم تأكيد الإدخال بالضغط في نفس الوقت على أزرار Control ، Shift ، Enter (CSE). سيظهر شريط الصيغة أن الصيغة في أقواس. MUMNOZH يجعل مصفوفة افتراضية بحجم عدد صفوف الجدول حسب عدد صفوف الجدول ، في الصفوف التي يتم إدخال نتائج الحساب فيها ، كما لو تم إدخال الصيغة التالية في صفوف الجدول (للصف الثاني) = SUMPRODUCT (F ((C $ 2: C2> A2))) / = SUMPRODUCT (N ((C $ 2: C2> A2))) ، وتمتد إلى الجدول بأكمله.

2. نفس الشيء ، ولكن أكثر تعقيدا. بالإضافة إلى البداية والنهاية ، هناك عدد الوحدات أو القوة المعطاة / المستقبلة. من الضروري تحديد الطاقة القصوى في وقت ما.

جزء من الجدول _ t1:


والصيغة نفسها:

 =((((_t1[start]*1440)<=(1:1436)-1)*((_t1[stop]*1440)>=(1:1436)-1);_t1[power])) =MAX(MMULT((TRANSPOSE(_t1[start]*1440)<=ROW(1:1436)-1)*(TRANSPOSE(_t1[stop]*1440)>=ROW(1:1436)-1),_t1[power])) 

نظرًا لأن الوقت جزء من وحدة ، فإننا نضرب القيم في عدد الدقائق في اليوم للحصول على أعداد صحيحة. والحجة الثانية هي MULTIPLE - عمود من قيم الطاقة. في الحالة الأولى ، تم إنشاء عمود وحدة.

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


في العمود H في الأيام الأولى من الأشهر ، في العمود I الصيغ التالية:

 =(_tis[sum per day]*((((H3;0)>_tis[end];_tis[end];(H3;0))-(H3>_tis[start];H3;_tis[start])+1)^0,5;)^2) =SUM(_tis[sum per day]*IFERROR((IF(EOMONTH(H3,0)>_tis[end],_tis[end],EOMONTH(H3,0))-IF(H3>_tis[start],H3,_tis[start])+1)^0.5,)^2) 

الصيغ ضخمة. يمكنك استخدام SUMPRODUCT / SUMPRODUCT ، ولكن إذا كان الخطأ لا يعمل بدون إدخال ضخم. أيضًا ، للأسف ، لا يمكن لـ MAX و MIN تكوين مجموعة افتراضية من القيم. لذلك ، يتم استخدام IF لحساب أجزاء الفترات الشهرية. نظرًا لأن الأجزاء الناتجة يمكن أن تكون سالبة ، يحدث خطأ بشكل مصطنع (يؤدي الجذر التربيعي لرقم سلبي إلى ذلك). في الجدول المصدر ، كان من الممكن الاستغناء عن أعمدة المدة الفاصلة والمبلغ اليومي. ستصبح الصيغة أطول قليلاً.

4. القليل من المرح. كم يوم جمعة سعيد يمكن أن يكون في عام واحد؟


عدد أيام الجمعة:

 =(((((A:A;B2):(A:A;C2)))=13)*((((A:A;B2):(A:A;C2));2)=5)) =SUMPRODUCT((DAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)))=13)*(WEEKDAY(ROW(INDEX(A:A,B2):INDEX(A:A,C2)),2)=5)) 

مواعيد الجمعة:

 =((15;6;(($A:$A;$B2):($A:$A;$C2))/(((($A:$A;$B2):(A:A;$C2)))=13)/(((($A:$A;$B2):($A:$A;$C2));2)=5);(A$2));"") =IFERROR(AGGREGATE(15,6,ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2))/(DAY(ROW(INDEX($A:$A,$B2):INDEX(A:A,$C2)))=13)/(WEEKDAY(ROW(INDEX($A:$A,$B2):INDEX($A:$A,$C2)),2)=5),COLUMN(A$2)),"") 

من الجيد جدًا (هنا) أن السطر الأول من الورقة يتوافق مع 01/01/1901. لذلك ، من السهل تشكيل مجموعة من تواريخ السنة باستخدام INDEX و STRING وإحصاء العدد الثالث عشر من الجمعة فقط.

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


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

بالنسبة لبيانات المصدر هذه:


الصيغة

 =(A3;tbl;2)*(A3-(A3;tbl;1))+(((tbl[limit]<=A3)*(tbl[limit]-(tbl[limit];-1;))*(tbl[rate];-1;);)) =VLOOKUP(A3,tbl,2)*(A3-VLOOKUP(A3,tbl,1))+SUMPRODUCT(IFERROR((tbl[limit]<=A3)*(tbl[limit]-OFFSET(tbl[limit],-1,))*OFFSET(tbl[rate],-1,),)) 

الصيغة ضخمة. في A3 ، المبلغ الذي سيتم احتساب الفائدة عليه. البيانات الأولية –tbl.
وأخيرًا ، خط ائتمان بسعر فائدة متغير. صيغة لحساب مقدار الفائدة حسب تواريخ / مبالغ الشرائح / العوائد.

سجل الائتمان:


تغيير العرض:

 =(B1=0;(((_d;_s[[date]:[rate]]);)*(B5:B16)*((_d>(A5:A16))*(_d<=A2);;)*1/(365+(((_d);4)=0)))-(((_d;_s[[date]:[rate]]);)*(D5:D16)*((_d>(C5:C16))*(_d<=A2);;)*1/(365+(((_d);4)=0)));) =IF(B1=0,SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(B5:B16)*INDEX((_d>TRANSPOSE(A5:A16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0)))-SUM(IFERROR(LOOKUP(_d,_s[[date]:[rate]]),)*TRANSPOSE(D5:D16)*INDEX((_d>TRANSPOSE(C5:C16))*(_d<=A2),,)*1/(365+(MOD(YEAR(_d),4)=0))),) 

الصيغة ضخمة. تحسب الفائدة حسب الأيام ، مع مراعاة سنوات كبيسة.

شكرا لكم على اهتمامكم!