বিষয়বস্তু
সমস্যা প্রণয়ন
আসুন খুব সাধারণ পরিস্থিতিগুলির একটির জন্য একটি সুন্দর সমাধান দেখি যা বেশিরভাগ এক্সেল ব্যবহারকারীরা শীঘ্রই বা পরে সম্মুখীন হয়: আপনাকে দ্রুত এবং স্বয়ংক্রিয়ভাবে বিপুল সংখ্যক ফাইল থেকে একটি চূড়ান্ত টেবিলে ডেটা সংগ্রহ করতে হবে।
ধরুন আমাদের নিম্নলিখিত ফোল্ডার রয়েছে, যাতে শাখা শহরগুলির ডেটা সহ বেশ কয়েকটি ফাইল রয়েছে:
ফাইলের সংখ্যা কোন ব্যাপার না এবং ভবিষ্যতে পরিবর্তন হতে পারে। প্রতিটি ফাইল নামে একটি শীট আছে বিক্রয়ডেটা টেবিলটি কোথায় অবস্থিত:
টেবিলে সারি (অর্ডার) সংখ্যা, অবশ্যই, ভিন্ন, কিন্তু কলামের সেট সর্বত্র আদর্শ।
টাস্ক: সারণিতে শহরের ফাইল বা সারি যুক্ত বা মুছে ফেলার সময় পরবর্তী স্বয়ংক্রিয় আপডেট সহ সমস্ত ফাইল থেকে ডেটা সংগ্রহ করা। চূড়ান্ত সমন্বিত ছক অনুযায়ী তারপর যেকোনো রিপোর্ট, পিভট টেবিল, ফিল্টার-সর্ট ডাটা ইত্যাদি তৈরি করা সম্ভব হবে। মূল বিষয় হলো সংগ্রহ করতে সক্ষম হওয়া।
আমরা অস্ত্র নির্বাচন করি
সমাধানের জন্য, আমাদের প্রয়োজন এক্সেল 2016 এর সর্বশেষ সংস্করণ (প্রয়োজনীয় কার্যকারিতা ইতিমধ্যেই এটিতে ডিফল্টরূপে তৈরি করা হয়েছে) অথবা বিনামূল্যে অ্যাড-ইন ইনস্টল সহ Excel 2010-2013-এর পূর্ববর্তী সংস্করণগুলি। পাওয়ার কোয়েরি মাইক্রোসফ্ট থেকে (এটি এখানে ডাউনলোড করুন)। পাওয়ার কোয়েরি হল একটি অতি নমনীয় এবং অতি শক্তিশালী টুল যা এক্সেলে বহির্বিশ্ব থেকে ডেটা লোড করার জন্য, তারপরে এটিকে স্ট্রিপিং এবং প্রসেস করার জন্য। পাওয়ার কোয়েরি প্রায় সমস্ত বিদ্যমান ডেটা উত্স সমর্থন করে - পাঠ্য ফাইল থেকে SQL এবং এমনকি Facebook 🙂
আপনার যদি এক্সেল 2013 বা 2016 না থাকে তবে আপনি আরও পড়তে পারবেন না (শুধু মজা করছেন)। এক্সেলের পুরানো সংস্করণগুলিতে, এই ধরনের একটি কাজ শুধুমাত্র ভিজ্যুয়াল বেসিক (যেটি নতুনদের জন্য খুব কঠিন) একটি ম্যাক্রো প্রোগ্রামিং করে বা একঘেয়ে ম্যানুয়াল কপি করে (যা অনেক সময় নেয় এবং ত্রুটি তৈরি করে) দ্বারা সম্পন্ন করা যেতে পারে।
ধাপ 1. একটি নমুনা হিসাবে একটি ফাইল আমদানি করুন
প্রথমে, আসুন একটি উদাহরণ হিসাবে একটি ওয়ার্কবুক থেকে ডেটা আমদানি করি, যাতে এক্সেল "ধারণাটি তুলে নেয়"। এটি করতে, একটি নতুন ফাঁকা ওয়ার্কবুক তৈরি করুন এবং…
- আপনার যদি এক্সেল 2016 থাকে, তাহলে ট্যাবটি খুলুন উপাত্ত এবং তারপর প্রশ্ন তৈরি করুন - ফাইল থেকে - বই থেকে (ডেটা — নতুন প্রশ্ন- ফাইল থেকে — এক্সেল থেকে)
- আপনার যদি এক্সেল 2010-2013 পাওয়ার কোয়েরি অ্যাড-ইন ইনস্টল করা থাকে, তাহলে ট্যাবটি খুলুন পাওয়ার কোয়েরি এবং এটি নির্বাচন করুন ফাইল থেকে - বই থেকে (ফাইল থেকে - এক্সেল থেকে)
তারপরে, যে উইন্ডোটি খোলে, সেখানে রিপোর্ট সহ আমাদের ফোল্ডারে যান এবং শহরের যেকোন ফাইল নির্বাচন করুন (এটি কোন ব্যাপার না, কারণ সেগুলি সবই সাধারণ)। কয়েক সেকেন্ড পরে, ন্যাভিগেটর উইন্ডোটি উপস্থিত হওয়া উচিত, যেখানে আপনাকে বাম দিকে আমাদের প্রয়োজনীয় শীটটি নির্বাচন করতে হবে (বিক্রয়) এবং এর বিষয়বস্তু ডানদিকে প্রদর্শিত হবে:
যদি আপনি এই উইন্ডোর নীচের ডান কোণে বোতামে ক্লিক করেন ডাউনলোড (ভার), তারপর টেবিলটি অবিলম্বে তার আসল আকারে শীটে আমদানি করা হবে। একটি একক ফাইলের জন্য, এটি ভাল, তবে আমাদের এই ধরনের অনেকগুলি ফাইল লোড করতে হবে, তাই আমরা একটু ভিন্নভাবে যাব এবং বোতামটি ক্লিক করব সংশোধন (সম্পাদনা করুন). এর পরে, পাওয়ার ক্যোয়ারী ক্যোয়ারী এডিটরটি বই থেকে আমাদের ডেটা সহ একটি পৃথক উইন্ডোতে প্রদর্শিত হবে:
এটি একটি অত্যন্ত শক্তিশালী টুল যা আপনাকে আমাদের প্রয়োজনীয় দৃশ্যে টেবিলটিকে "সমাপ্ত" করতে দেয়। এমনকি এর সমস্ত ফাংশনগুলির একটি সুপারফিশিয়াল বর্ণনা প্রায় একশ পৃষ্ঠা লাগবে, তবে, খুব সংক্ষিপ্তভাবে, এই উইন্ডোটি ব্যবহার করে আপনি করতে পারেন:
- অপ্রয়োজনীয় ডেটা, খালি লাইন, ত্রুটি সহ লাইন ফিল্টার করুন
- এক বা একাধিক কলাম দ্বারা ডেটা সাজান
- পুনরাবৃত্তি পরিত্রাণ পেতে
- স্টিকি টেক্সটকে কলাম দ্বারা ভাগ করুন (ডিলিমিটার, অক্ষরের সংখ্যা ইত্যাদির মাধ্যমে)
- টেক্সট ক্রমানুসারে রাখুন (অতিরিক্ত স্পেস সরান, সঠিক কেস, ইত্যাদি)
- প্রতিটি সম্ভাব্য উপায়ে ডেটা প্রকারগুলি রূপান্তর করুন (পাঠ্যের মতো সংখ্যাগুলিকে সাধারণ সংখ্যায় পরিণত করুন এবং এর বিপরীতে)
- সারণী স্থানান্তর করুন (ঘোরান) এবং দ্বি-মাত্রিক ক্রস-টেবিলগুলিকে সমতলগুলিতে প্রসারিত করুন
- টেবিলে অতিরিক্ত কলাম যোগ করুন এবং Power Query-এ অন্তর্নির্মিত M ভাষা ব্যবহার করে তাদের মধ্যে সূত্র এবং ফাংশন ব্যবহার করুন।
- ...
উদাহরণস্বরূপ, আসুন আমাদের টেবিলে মাসের পাঠ্য নামের সাথে একটি কলাম যুক্ত করি, যাতে পরবর্তীতে পিভট টেবিল রিপোর্ট তৈরি করা সহজ হয়। এটি করতে, কলামের শিরোনামে ডান ক্লিক করুন তারিখএবং কমান্ড নির্বাচন করুন সদৃশ কলাম (ডুপ্লিকেট কলাম), এবং তারপর প্রদর্শিত ডুপ্লিকেট কলামের হেডারে ডান-ক্লিক করুন এবং কমান্ড নির্বাচন করুন রূপান্তর – মাস – মাসের নাম:
প্রতিটি সারির জন্য মাসের পাঠ্য নামের সাথে একটি নতুন কলাম তৈরি করা উচিত। একটি কলাম শিরোনামে ডাবল ক্লিক করে, আপনি এটির নাম পরিবর্তন করতে পারেন তারিখ কপি করুন আরো আরামদায়ক করতে মাস, যেমন।
যদি কিছু কলামে প্রোগ্রামটি সঠিকভাবে ডেটা টাইপ চিনতে না পারে, তবে আপনি প্রতিটি কলামের বাম দিকে ফর্ম্যাট আইকনে ক্লিক করে এটিকে সহায়তা করতে পারেন:
আপনি একটি সাধারণ ফিল্টার ব্যবহার করে ত্রুটি বা খালি লাইনের পাশাপাশি অপ্রয়োজনীয় পরিচালক বা গ্রাহকদের বাদ দিতে পারেন:
তদুপরি, সমস্ত সম্পাদিত রূপান্তরগুলি ডান প্যানেলে স্থির করা হয়েছে, যেখানে সেগুলি সর্বদা রোল ব্যাক (ক্রস) বা তাদের প্যারামিটার (গিয়ার) পরিবর্তন করা যেতে পারে:
হালকা এবং মার্জিত, তাই না?
ধাপ 2. আমাদের অনুরোধকে একটি ফাংশনে রূপান্তর করা যাক
পরবর্তীতে প্রতিটি আমদানি করা বইয়ের জন্য করা সমস্ত ডেটা রূপান্তর পুনরাবৃত্তি করার জন্য, আমাদের তৈরি করা অনুরোধটিকে একটি ফাংশনে রূপান্তর করতে হবে, যা তারপরে, আমাদের সমস্ত ফাইলে প্রয়োগ করা হবে। এটি করা আসলে খুব সহজ।
ক্যোয়ারী এডিটরে, ভিউ ট্যাবে যান এবং বোতামে ক্লিক করুন অ্যাডভান্সড এডিটর (দেখুন — অ্যাডভান্সড এডিটর). একটি উইন্ডো খোলা উচিত যেখানে আমাদের সমস্ত পূর্ববর্তী ক্রিয়াগুলি M ভাষায় কোড আকারে লেখা হবে। অনুগ্রহ করে মনে রাখবেন যে ফাইলটির পাথ যা আমরা উদাহরণের জন্য আমদানি করেছি সেটি কোডে হার্ডকোড করা হয়েছে:
এখন কিছু সমন্বয় করা যাক:
তাদের অর্থ সহজ: প্রথম লাইন (ফাইলপথ) => আর্গুমেন্ট সহ আমাদের পদ্ধতিকে একটি ফাংশনে পরিণত করে ফাইল পাথ, এবং নীচে আমরা এই ভেরিয়েবলের মানের নির্দিষ্ট পথ পরিবর্তন করি।
সব ক্লিক করুন শেষ এবং এটি দেখতে হবে:
ভয় পাবেন না যে ডেটা অদৃশ্য হয়ে গেছে - আসলে, সবকিছু ঠিক আছে, সবকিছু এইরকম হওয়া উচিত 🙂 আমরা সফলভাবে আমাদের কাস্টম ফাংশন তৈরি করেছি, যেখানে ডেটা আমদানি এবং প্রক্রিয়াকরণের সম্পূর্ণ অ্যালগরিদম একটি নির্দিষ্ট ফাইলের সাথে বাঁধা ছাড়াই মনে রাখা হয় . এটিকে আরও বোধগম্য নাম দেওয়া বাকি রয়েছে (উদাহরণস্বরূপ তথ্য সংগ্রহ করো) ক্ষেত্রের ডানদিকে প্যানেলে নাম এবং আপনি কাটতে পারেন হোম - বন্ধ করুন এবং ডাউনলোড করুন (বাড়ি — বন্ধ এবং লোড). অনুগ্রহ করে মনে রাখবেন যে ফাইলটির পাথ যা আমরা উদাহরণের জন্য আমদানি করেছি সেটি কোডে হার্ডকোড করা আছে। আপনি মূল মাইক্রোসফ্ট এক্সেল উইন্ডোতে ফিরে আসবেন, তবে আমাদের ফাংশনের সাথে তৈরি সংযোগ সহ একটি প্যানেল ডানদিকে উপস্থিত হওয়া উচিত:
ধাপ 3. সমস্ত ফাইল সংগ্রহ করা
সব কঠিন অংশ পিছনে, আনন্দদায়ক এবং সহজ অংশ অবশেষ. ট্যাবে যান ডেটা - ক্যোয়ারী তৈরি করুন - ফাইল থেকে - ফোল্ডার থেকে (ডেটা — নতুন ক্যোয়ারী — ফাইল থেকে — ফোল্ডার থেকে) অথবা, আপনার যদি এক্সেল 2010-2013 থাকে, একইভাবে ট্যাবের মতো পাওয়ার কোয়েরি. প্রদর্শিত উইন্ডোতে, ফোল্ডারটি নির্দিষ্ট করুন যেখানে আমাদের সমস্ত সোর্স সিটি ফাইল রয়েছে এবং ক্লিক করুন OK. পরবর্তী ধাপে একটি উইন্ডো খোলা উচিত যেখানে এই ফোল্ডারে পাওয়া সমস্ত এক্সেল ফাইল (এবং এর সাবফোল্ডার) এবং তাদের প্রতিটির বিশদ বিবরণ তালিকাভুক্ত করা হবে:
ক্লিক পরিবর্তন (সম্পাদনা করুন) এবং আবার আমরা পরিচিত ক্যোয়ারী এডিটর উইন্ডোতে প্রবেশ করি।
এখন আমাদের তৈরি করা ফাংশন সহ আমাদের টেবিলে আরেকটি কলাম যোগ করতে হবে, যা প্রতিটি ফাইল থেকে ডেটা "টান" করবে। এটি করতে, ট্যাবে যান কলাম যোগ করুন - কাস্টম কলাম (কলাম যোগ করুন — কাস্টম কলাম যোগ করুন) এবং প্রদর্শিত উইন্ডোতে, আমাদের ফাংশন লিখুন তথ্য সংগ্রহ করো, প্রতিটি ফাইলের সম্পূর্ণ পথ একটি যুক্তি হিসাবে এটির জন্য নির্দিষ্ট করে:
ক্লিক করার পরে OK তৈরি কলাম ডানদিকে আমাদের টেবিলে যোগ করা উচিত।
এখন আসুন সমস্ত অপ্রয়োজনীয় কলাম মুছে ফেলি (এক্সেলের মতো, ডান মাউস বোতাম ব্যবহার করে - অপসারণ), শুধুমাত্র যোগ করা কলাম এবং ফাইলের নামের সাথে কলাম রেখে, কারণ এই নামটি (আরো সঠিকভাবে বললে, শহর) প্রতিটি সারির মোট ডেটাতে থাকা উপযোগী হবে।
এবং এখন "ওয়াও মোমেন্ট" - আমাদের ফাংশনের সাথে যোগ করা কলামের উপরের ডানদিকে কোণায় তার নিজস্ব তীরগুলি সহ আইকনে ক্লিক করুন:
… আনচেক করুন উপসর্গ হিসাবে মূল কলাম নাম ব্যবহার করুন (প্রিফিক্স হিসাবে মূল কলামের নাম ব্যবহার করুন)এবং ক্লিক OK. এবং আমাদের ফাংশন রেকর্ড করা অ্যালগরিদম অনুসরণ করে এবং একটি সাধারণ টেবিলে সবকিছু সংগ্রহ করে প্রতিটি ফাইল থেকে ডেটা লোড এবং প্রক্রিয়া করবে:
সম্পূর্ণ সৌন্দর্যের জন্য, আপনি ফাইলের নাম সহ প্রথম কলাম থেকে .xlsx এক্সটেনশনগুলিও সরিয়ে ফেলতে পারেন - "কিছুই না" দিয়ে স্ট্যান্ডার্ড প্রতিস্থাপনের মাধ্যমে (কলাম হেডারে ডান-ক্লিক করুন - বিকল্প) এবং এই কলামটির নাম পরিবর্তন করুন শহর. এবং তারিখ সহ কলামে ডেটা বিন্যাসটিও সংশোধন করুন।
সমস্ত ! ক্লিক করুন হোম - বন্ধ এবং লোড (বাড়ি — বন্ধ এবং লোড). সমস্ত শহরের জন্য কোয়েরি দ্বারা সংগৃহীত সমস্ত ডেটা বর্তমান এক্সেল শীটে "স্মার্ট টেবিল" বিন্যাসে আপলোড করা হবে:
তৈরি কানেকশন এবং আমাদের অ্যাসেম্বলি ফাংশন আলাদাভাবে কোনোভাবেই সেভ করার দরকার নেই – এগুলি বর্তমান ফাইলের সাথে স্বাভাবিক পদ্ধতিতে একসাথে সেভ করা হয়।
ভবিষ্যতে, ফোল্ডারে (শহর যোগ করা বা অপসারণ করা) বা ফাইলগুলিতে (লাইনের সংখ্যা পরিবর্তন করা) কোনও পরিবর্তনের সাথে, সরাসরি টেবিলে বা ডান প্যানেলের ক্যোয়ারীতে ডান-ক্লিক করা এবং নির্বাচন করা যথেষ্ট হবে। আদেশ আপডেট এবং সংরক্ষণ করুন (রিফ্রেশ) - পাওয়ার কোয়েরি কয়েক সেকেন্ডের মধ্যে আবার সমস্ত ডেটা "পুনঃনির্মাণ" করবে।
PS
সংশোধন. জানুয়ারী 2017 আপডেটের পরে, পাওয়ার কোয়েরি শিখেছে কিভাবে Excel ওয়ার্কবুকগুলি নিজে থেকে সংগ্রহ করতে হয়, অর্থাৎ আর আলাদা ফাংশন করার দরকার নেই – এটি স্বয়ংক্রিয়ভাবে ঘটে। সুতরাং, এই নিবন্ধের দ্বিতীয় ধাপের আর প্রয়োজন নেই এবং পুরো প্রক্রিয়াটি লক্ষণীয়ভাবে সহজ হয়ে উঠেছে:
- বেছে নিন অনুরোধ তৈরি করুন - ফাইল থেকে - ফোল্ডার থেকে - ফোল্ডার নির্বাচন করুন - ঠিক আছে
- ফাইলের তালিকা প্রদর্শিত হওয়ার পরে, টিপুন পরিবর্তন
- ক্যোয়ারী এডিটর উইন্ডোতে, একটি ডবল তীর দিয়ে বাইনারি কলামটি প্রসারিত করুন এবং প্রতিটি ফাইল থেকে শীটের নাম নির্বাচন করুন
এবং যে সব! গানের !
- পিভট টেবিল তৈরির জন্য উপযুক্ত একটি ফ্ল্যাটে ক্রসট্যাবটিকে পুনরায় ডিজাইন করুন
- পাওয়ার ভিউতে একটি অ্যানিমেটেড বাবল চার্ট তৈরি করা
- বিভিন্ন এক্সেল ফাইল থেকে শীট একত্রিত করতে ম্যাক্রো