এক্সেলে মোট চলছে

পদ্ধতি 1. সূত্র

চলুন শুরু করা যাক, ওয়ার্মিং আপের জন্য, সবচেয়ে সহজ বিকল্পের সাথে - সূত্র। যদি আমাদের কাছে ইনপুট হিসাবে তারিখ অনুসারে বাছাই করা একটি ছোট টেবিল থাকে, তবে একটি পৃথক কলামে চলমান মোট হিসাব করতে, আমাদের একটি প্রাথমিক সূত্রের প্রয়োজন:

এখানে প্রধান বৈশিষ্ট্য হল SUM ফাংশনের ভিতরে পরিসরের জটিল ফিক্সিং - পরিসরের শুরুর রেফারেন্সটি সম্পূর্ণ (ডলারের চিহ্ন সহ) এবং শেষ পর্যন্ত - আপেক্ষিক (ডলার ছাড়া)। তদনুসারে, পুরো কলামে সূত্রটি অনুলিপি করার সময়, আমরা একটি প্রসারিত পরিসর পাই, যার সমষ্টি আমরা গণনা করি।

এই পদ্ধতির অসুবিধাগুলি সুস্পষ্ট:

• টেবিলটি তারিখ অনুসারে বাছাই করা আবশ্যক।
  
• ডেটা সহ নতুন সারি যোগ করার সময়, সূত্রটি ম্যানুয়ালি প্রসারিত করতে হবে।

পদ্ধতি 2. পিভট টেবিল

এই পদ্ধতিটি একটু বেশি জটিল, কিন্তু অনেক বেশি আনন্দদায়ক। এবং আরও বাড়াতে, আসুন একটি আরও গুরুতর সমস্যা বিবেচনা করি - 2000 সারি ডেটার একটি টেবিল, যেখানে তারিখের কলাম অনুসারে কোনও সাজানো নেই, তবে পুনরাবৃত্তি রয়েছে (যেমন আমরা একই দিনে বেশ কয়েকবার বিক্রি করতে পারি):

আমরা আমাদের মূল টেবিলটিকে একটি "স্মার্ট" (ডাইনামিক) কীবোর্ড শর্টকাটে রূপান্তর করি জন্য ctrl+T বা দল হোম - একটি টেবিল হিসাবে বিন্যাস (হোম - টেবিল হিসাবে ফর্ম্যাট), এবং তারপর আমরা কমান্ড দিয়ে এটির উপর একটি পিভট টেবিল তৈরি করি সন্নিবেশ করুন - PivotTable (ঢোকান — পিভট টেবিল). আমরা সংক্ষিপ্তসারে সারি এলাকায় তারিখ রাখি এবং মান এলাকায় বিক্রি হওয়া পণ্যের সংখ্যা:

অনুগ্রহ করে মনে রাখবেন যে যদি আপনার কাছে Excel এর পুরানো সংস্করণ না থাকে, তাহলে তারিখগুলি স্বয়ংক্রিয়ভাবে বছর, চতুর্থাংশ এবং মাস দ্বারা গোষ্ঠীভুক্ত হয়৷ আপনার যদি আলাদা গ্রুপিং প্রয়োজন হয় (বা একেবারেই প্রয়োজন নেই), তাহলে আপনি যেকোনো তারিখে ডান-ক্লিক করে এবং কমান্ড নির্বাচন করে এটি ঠিক করতে পারেন। গ্রুপ/আনগ্রুপ (গ্রুপ/আনগ্রুপ).

আপনি যদি একটি পৃথক কলামে পিরিয়ড দ্বারা ফলাফলের মোট এবং চলমান মোট উভয়ই দেখতে চান, তাহলে ক্ষেত্রটিকে মান এলাকায় নিক্ষেপ করা বোধগম্য হয় বিক্রীত আবার ফিল্ডের ডুপ্লিকেট পেতে - এতে আমরা চলমান মোটের প্রদর্শন চালু করব। এটি করার জন্য, ক্ষেত্রের উপর ডান-ক্লিক করুন এবং কমান্ডটি নির্বাচন করুন অতিরিক্ত গণনা - ক্রমবর্ধমান মোট (চলমান মোট হিসাবে মানগুলি দেখান):

সেখানে আপনি শতাংশ হিসাবে ক্রমবর্ধমান মোটের বিকল্পটিও নির্বাচন করতে পারেন এবং পরবর্তী উইন্ডোতে আপনাকে সেই ক্ষেত্রটি নির্বাচন করতে হবে যার জন্য জমা হবে – আমাদের ক্ষেত্রে, এটি হল তারিখ ক্ষেত্র:

এই পদ্ধতির সুবিধা:

• প্রচুর পরিমাণে ডেটা দ্রুত পড়া হয়।
• ম্যানুয়ালি কোনো সূত্র প্রবেশ করাতে হবে না।
• সোর্স ডেটা পরিবর্তন করার সময়, ডান মাউস বোতাম বা ডেটা – রিফ্রেশ অল কমান্ড দিয়ে সারাংশ আপডেট করা যথেষ্ট।

অসুবিধাগুলি হল যে এটি একটি সারাংশ, যার মানে হল যে আপনি এটিতে যা চান তা করতে পারবেন না (লাইন সন্নিবেশ করান, সূত্র লিখুন, কোনো ডায়াগ্রাম তৈরি করুন, ইত্যাদি) আর কাজ করবে না।

পদ্ধতি 3: পাওয়ার কোয়েরি

কমান্ডটি ব্যবহার করে পাওয়ার কোয়েরি ক্যোয়ারী এডিটরে সোর্স ডেটা সহ আমাদের "স্মার্ট" টেবিল লোড করা যাক ডেটা - টেবিল/রেঞ্জ থেকে (ডেটা — টেবিল/রেঞ্জ থেকে). এক্সেলের সর্বশেষ সংস্করণগুলিতে, যাইহোক, এটির নাম পরিবর্তন করা হয়েছিল - এখন এটি বলা হয় পাতা দিয়ে (শীট থেকে):

তারপরে আমরা নিম্নলিখিত পদক্ষেপগুলি সম্পাদন করব:

1. কমান্ড সহ তারিখ কলাম দ্বারা সারণীকে আরোহী ক্রমে সাজান সাজান আরোহী টেবিল হেডারে ফিল্টার ড্রপ-ডাউন তালিকায়।

2. একটু পরে, চলমান মোট গণনা করতে, আমাদের সারি সারি সংখ্যা সহ একটি সহায়ক কলাম প্রয়োজন। এর কমান্ড দিয়ে যোগ করা যাক কলাম যোগ করুন - সূচক কলাম - 1 থেকে (কলাম যোগ করুন — সূচক কলাম — 1 থেকে).

3. এছাড়াও, চলমান মোট হিসাব করতে, আমাদের কলামের একটি রেফারেন্স প্রয়োজন বিক্রীত, যেখানে আমাদের সংক্ষিপ্ত তথ্য রয়েছে। পাওয়ার কোয়েরিতে, কলামগুলিকে তালিকা (তালিকা)ও বলা হয় এবং এটিতে একটি লিঙ্ক পেতে, কলাম হেডারে ডান-ক্লিক করুন এবং কমান্ডটি নির্বাচন করুন বিস্তারিত (বিস্তারিত দেখাও). আমাদের প্রয়োজনীয় অভিব্যক্তিটি পূর্ববর্তী ধাপের নাম সহ সূত্র বারে উপস্থিত হবে #"সূচক যোগ করা হয়েছে", যেখান থেকে আমরা টেবিল এবং কলামের নাম নিই [বিক্রয়] বর্গাকার বন্ধনীতে এই টেবিল থেকে:

আরও ব্যবহারের জন্য ক্লিপবোর্ডে এই অভিব্যক্তিটি অনুলিপি করুন।

4. অপ্রয়োজনীয় আরও শেষ ধাপ মুছুন বিক্রীত এবং কমান্ডের সাথে চলমান মোট হিসাব করার জন্য একটি গণনাকৃত কলাম যোগ করুন একটি কলাম যোগ করা - কাস্টম কলাম (কলাম যোগ করুন — কাস্টম কলাম). আমাদের যে সূত্রটি প্রয়োজন তা এইরকম দেখাবে:

এখানে ফাংশন তালিকা। পরিসর মূল তালিকা নেয় (কলাম [বিক্রয়]) এবং এটি থেকে উপাদানগুলি বের করে, প্রথম থেকে শুরু করে (সূত্রে, এটি 0, যেহেতু পাওয়ার কোয়েরিতে সংখ্যা শূন্য থেকে শুরু হয়)। পুনরুদ্ধার করার জন্য উপাদানের সংখ্যা হল সারি সংখ্যা যা আমরা কলাম থেকে নিই [সূচী]. সুতরাং প্রথম সারির জন্য এই ফাংশনটি শুধুমাত্র কলামের একটি প্রথম ঘর প্রদান করে বিক্রীত. দ্বিতীয় লাইনের জন্য - ইতিমধ্যে প্রথম দুটি কক্ষ, তৃতীয়টির জন্য - প্রথম তিনটি, ইত্যাদি।

ওয়েল, তারপর ফাংশন তালিকা.সমষ্টি নিষ্কাশিত মানগুলি যোগ করে এবং আমরা প্রতিটি সারিতে সমস্ত পূর্ববর্তী উপাদানগুলির যোগফল পাই, যেমন ক্রমবর্ধমান মোট:

আমাদের আর প্রয়োজন নেই এমন সূচী কলামটি মুছে ফেলার জন্য এবং হোম – ক্লোজ এবং লোড টু কমান্ড সহ এক্সেল-এ ফলাফল আপলোড করা বাকি আছে।

সমস্যাটি সমাধানকৃত.

দ্রুত ও ক্ষিপ্ত

নীতিগতভাবে, এটি বন্ধ করা যেতে পারে, তবে মলমে একটি ছোট মাছি রয়েছে - আমরা যে অনুরোধটি তৈরি করেছি তা একটি কচ্ছপের গতিতে কাজ করে। উদাহরণস্বরূপ, আমার দুর্বলতম পিসিতে নয়, মাত্র 2000 সারির একটি টেবিল 17 সেকেন্ডে প্রক্রিয়া করা হয়। আরও তথ্য থাকলে কি হবে?

গতি বাড়ানোর জন্য, আপনি বিশেষ List.Buffer ফাংশন ব্যবহার করে বাফারিং ব্যবহার করতে পারেন, যা এটিকে দেওয়া তালিকা (তালিকা) RAM-তে একটি যুক্তি হিসাবে লোড করে, যা ভবিষ্যতে এটিতে অ্যাক্সেসের গতি বাড়িয়ে দেয়। আমাদের ক্ষেত্রে, আমাদের 2000-সারি টেবিলের প্রতিটি সারিতে চলমান মোট গণনা করার সময় পাওয়ার ক্যোয়ারিকে অ্যাক্সেস করতে হবে এমন #"অ্যাডেড ইনডেক্স"[বিক্রীত] তালিকাটি বাফার করা বোধগম্য।

এটি করার জন্য, প্রধান ট্যাবের পাওয়ার কোয়েরি সম্পাদকে, পাওয়ার কোয়েরিতে নির্মিত এম ভাষাতে আমাদের কোয়েরির সোর্স কোড খুলতে অ্যাডভান্সড এডিটর বোতামে (হোম – অ্যাডভান্সড এডিটর) ক্লিক করুন:

এবং তারপর সেখানে একটি পরিবর্তনশীল সঙ্গে একটি লাইন যোগ করুন আমার তালিকা, যার মান বাফারিং ফাংশন দ্বারা ফেরত দেওয়া হয়, এবং পরবর্তী ধাপে আমরা এই ভেরিয়েবলের সাথে তালিকায় কলটি প্রতিস্থাপন করি:

এই পরিবর্তনগুলি করার পরে, আমাদের ক্যোয়ারী উল্লেখযোগ্যভাবে দ্রুত হয়ে উঠবে এবং মাত্র 2000 সেকেন্ডের মধ্যে একটি 0.3-সারি টেবিলের সাথে মোকাবিলা করবে!

আরেকটি জিনিস, তাই না? 🙂

• প্যারেটো চার্ট (80/20) এবং কিভাবে এটি এক্সেলে তৈরি করবেন
• টেক্সটে কীওয়ার্ড সার্চ এবং পাওয়ার কোয়েরিতে কোয়েরি বাফারিং