एक्सेल और लंबे सूत्र

VBA एक बहुत ही उपयोगी चीज है। आप कई फ़ाइलों से डेटा को समेकित कर सकते हैं और दिलचस्प एल्गोरिदम का उपयोग करके बड़ी मात्रा में जानकारी संसाधित कर सकते हैं। उदाहरण के लिए, Nodupes मैक्रो । यह कोड संग्रह के दौरान होने वाली त्रुटि का उपयोग जनरेट किए गए संग्रह में एक तत्व की उपस्थिति के लिए चेक के रूप में करता है। मैक्रोज़ हैं जो सभी संभव संयोजनों और क्रमपरिवर्तन को बनाने के लिए पुनरावृत्ति का उपयोग करते हैं।

लेकिन मैं मानक एक्सेल वर्कशीट फ़ंक्शन का उपयोग करने के कुछ उदाहरण दिखाना चाहूंगा। अक्सर, आपको कक्षों से वांछित पाठ या संख्या निकालने के लिए बगीचे को सूत्रों से बाहर बाड़ देना होगा। यह आमतौर पर डेटा की गलत प्रस्तुति के कारण होता है। संख्याएं संख्याएं हैं, पाठ पाठ है, उन्हें मिश्रण न करें। तिथियां (और समय) संख्याएं हैं जिनके साथ गणना करना संभव और आवश्यक है। 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)) 

सूत्र बड़े पैमाने पर है, इनपुट की पुष्टि नियंत्रण, शिफ्ट, एंटर (CSE) बटन को एक साथ दबाकर की जाती है। सूत्र पट्टी दिखाएगा कि सूत्र ब्रेसिज़ में है। MUMNOZH एक आभासी मैट्रिक्स को तालिका की पंक्तियों की संख्या से तालिका की पंक्तियों की संख्या का आकार बनाता है, जिनमें से पंक्तियों में गणना परिणाम दर्ज किए जाते हैं, जैसे कि तालिका के पंक्तियों में निम्नलिखित सूत्र दर्ज किए गए थे (दूसरी पंक्ति के लिए) = SUMPRODUCT (F (C $ 2: C2> A2)) /)। = SUMPRODUCT (N (($ $ 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 का उपयोग कर सकते हैं, लेकिन IF EROR बड़े इनपुट के बिना काम नहीं करता है। इसके अलावा, दुर्भाग्य से, 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))),) 

सूत्र बड़े पैमाने पर है। दिनों से ब्याज गिनता है, खाते में छलांग लेता है।

आपका ध्यान के लिए धन्यवाद!