একাধিক বই থেকে বিভিন্ন শিরোনাম দিয়ে টেবিল তৈরি করুন

সমস্যা প্রণয়ন

আমাদের একটি ফোল্ডারে বেশ কয়েকটি ফাইল রয়েছে (আমাদের উদাহরণে - 4 টুকরা, সাধারণ ক্ষেত্রে - যতগুলি আপনি চান) প্রতিবেদন:

ভিতরে, এই ফাইলগুলি দেখতে এইরকম:

যেখানে:

• আমাদের যে ডেটা শীটটি প্রয়োজন তা সর্বদা বলা হয় দা, কিন্তু ওয়ার্কবুকের যেকোনো জায়গায় হতে পারে।
  
• চাদর ছাড়িয়ে দা প্রতিটি বইয়ের অন্য শীট থাকতে পারে।
• ডেটা সহ সারণিগুলিতে একটি ভিন্ন সংখ্যক সারি থাকে এবং ওয়ার্কশীটে একটি ভিন্ন সারি দিয়ে শুরু হতে পারে।
• বিভিন্ন টেবিলে একই কলামের নাম ভিন্ন হতে পারে (উদাহরণস্বরূপ, পরিমাণ = পরিমাণ = পরিমাণ).
• টেবিলের কলামগুলি ভিন্ন ক্রমে সাজানো যেতে পারে।

টাস্ক: শীট থেকে সমস্ত ফাইল থেকে বিক্রয় ডেটা সংগ্রহ করুন দা পরবর্তীতে একটি সারাংশ বা অন্য কোনো বিশ্লেষণ তৈরি করার জন্য একটি সাধারণ টেবিলে।

ধাপ 1. কলাম নামের একটি ডিরেক্টরি প্রস্তুত করা হচ্ছে

প্রথম কাজটি হল কলামের নাম এবং তাদের সঠিক ব্যাখ্যার জন্য সম্ভাব্য সমস্ত বিকল্প সহ একটি রেফারেন্স বই প্রস্তুত করা:

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

পাওয়ার কোয়েরি ক্যোয়ারী এডিটর উইন্ডোতে, আমরা ঐতিহ্যগতভাবে ধাপটি মুছে ফেলি পরিবর্তিত প্রকার এবং বোতামে ক্লিক করে এর পরিবর্তে একটি নতুন ধাপ যোগ করুন f_xসূত্র বারে (যদি এটি দৃশ্যমান না হয়, তাহলে আপনি ট্যাবে এটি সক্ষম করতে পারেন পর্যালোচনা) এবং সেখানে বিল্ট-ইন পাওয়ার কোয়েরি ভাষা M-তে সূত্র লিখুন:

= টেবিল। ToRows(সূত্র)

এই কমান্ডটি আগের ধাপে লোড করাকে রূপান্তর করবে উৎস নেস্টেড তালিকা (তালিকা) সমন্বিত একটি তালিকায় রেফারেন্স টেবিল, যার প্রত্যেকটি, ঘুরে, এক জোড়া মান। এটা-হয়ে গেল এক লাইন থেকে:

আমাদের এই ধরনের ডেটার প্রয়োজন হবে একটু পরে, যখন সমস্ত লোড করা টেবিল থেকে শিরোনামগুলিকে ভর করে নামকরণ করা হবে।

রূপান্তর সম্পূর্ণ করার পরে, কমান্ড নির্বাচন করুন হোম — বন্ধ করুন এবং লোড করুন — বন্ধ করুন এবং লোড করুন... এবং আমদানির ধরন শুধু একটি সংযোগ তৈরি করুন (হোম - বন্ধ করুন এবং লোড করুন - বন্ধ করুন এবং লোড করুন ... - শুধুমাত্র সংযোগ তৈরি করুন) এবং এক্সেলে ফিরে যান।

ধাপ 2. আমরা সব ফাইল থেকে সবকিছু যেমন আছে লোড করি

এখন ফোল্ডার থেকে আমাদের সমস্ত ফাইলের বিষয়বস্তু লোড করা যাক - আপাতত, যেমন আছে। দল নির্বাচন করছে ডেটা - ডেটা পান - ফাইল থেকে - ফোল্ডার থেকে (ডেটা — ডেটা পান — ফাইল থেকে — ফোল্ডার থেকে) এবং তারপর ফোল্ডার যেখানে আমাদের উৎস বই আছে.

পূর্বরূপ উইন্ডোতে, ক্লিক করুন রূপান্তর করুন (রূপান্তর) or পরিবর্তন (সম্পাদনা করুন):

এবং তারপরে সমস্ত ডাউনলোড করা ফাইলের বিষয়বস্তু প্রসারিত করুন (বাইনারী) কলাম শিরোনামে ডবল তীর সহ বোতাম সন্তুষ্ট:

প্রথম ফাইলের উদাহরণে পাওয়ার কোয়েরি (Vostok.xlsx) আমাদেরকে প্রতিটি ওয়ার্কবুক থেকে যে শীটটি নিতে চাই তার নাম জিজ্ঞাসা করবে – বেছে নিন দা এবং OK চাপুন:

এর পরে (আসলে), বেশ কয়েকটি ঘটনা ঘটবে যা ব্যবহারকারীর কাছে স্পষ্ট নয়, যার পরিণতিগুলি বাম প্যানেলে স্পষ্টভাবে দৃশ্যমান:

1. পাওয়ার কোয়েরি ফোল্ডার থেকে প্রথম ফাইলটি নেবে (আমাদের কাছে এটি থাকবে Vostok.xlsx — দেখ ফাইল উদাহরণ) একটি উদাহরণ হিসাবে এবং একটি প্রশ্ন তৈরি করে এর সামগ্রী আমদানি করে নমুনা ফাইল রূপান্তর. এই প্রশ্নের মত কিছু সহজ পদক্ষেপ থাকবে উৎস (ফাইল অ্যাক্সেস) ন্যাভিগেশন (শীট নির্বাচন) এবং সম্ভবত শিরোনাম উত্থাপন। এই অনুরোধটি শুধুমাত্র একটি নির্দিষ্ট ফাইল থেকে ডেটা লোড করতে পারে Vostok.xlsx.
   
2. এই অনুরোধের ভিত্তিতে, এর সাথে যুক্ত ফাংশন তৈরি করা হবে ফাইল রূপান্তর করুন (একটি বৈশিষ্ট্যযুক্ত আইকন দ্বারা নির্দেশিত f_x), যেখানে সোর্স ফাইলটি আর ধ্রুবক হবে না, কিন্তু একটি পরিবর্তনশীল মান - একটি প্যারামিটার। সুতরাং, এই ফাংশনটি যেকোন বই থেকে ডেটা বের করতে পারে যা আমরা একটি যুক্তি হিসাবে এটিতে স্লিপ করি।
   
3. ফাংশনটি কলাম থেকে প্রতিটি ফাইলে (বাইনারী) পালাক্রমে প্রয়োগ করা হবে সন্তুষ্ট - পদক্ষেপ এর জন্য দায়ী কাস্টম ফাংশন কল আমাদের ক্যোয়ারীতে যা ফাইলের তালিকায় একটি কলাম যোগ করে ফাইল রূপান্তর করুন প্রতিটি ওয়ার্কবুক থেকে আমদানি ফলাফল সহ:

   

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

   
   

ধাপ 3. স্যান্ডিং

পূর্ববর্তী স্ক্রিনশট স্পষ্টভাবে দেখায় যে সরাসরি সমাবেশ "যেমন আছে" খারাপ মানের হতে দেখা গেছে:

• কলাম বিপরীত হয়.
• অনেক অতিরিক্ত লাইন (খালি এবং শুধু নয়)।
• সারণী শিরোনাম শিরোনাম হিসাবে অনুভূত হয় না এবং ডেটার সাথে মিশ্রিত হয়।
  

আপনি এই সমস্ত সমস্যাগুলি খুব সহজেই ঠিক করতে পারেন - শুধু রূপান্তর নমুনা ফাইল ক্যোয়ারীটি টুইক করুন৷ আমরা এতে যে সমস্ত সমন্বয় করি তা স্বয়ংক্রিয়ভাবে সংশ্লিষ্ট কনভার্ট ফাইল ফাংশনের মধ্যে পড়ে, যার মানে প্রতিটি ফাইল থেকে ডেটা আমদানি করার সময় সেগুলি পরে ব্যবহার করা হবে।

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

বিভিন্ন ফাইলের কলামগুলি পরস্পরের নীচে স্বয়ংক্রিয়ভাবে ফিট হওয়ার জন্য, তাদের অবশ্যই একই নামকরণ করতে হবে। আপনি এম-কোডের এক লাইনের সাথে পূর্বে তৈরি করা ডিরেক্টরি অনুসারে এই ধরনের ভর পুনঃনামকরণ করতে পারেন। এর আবার বোতাম টিপুন f_x সূত্র বারে এবং পরিবর্তন করতে একটি ফাংশন যোগ করুন:

= টেবিল।পুনঃনামকলাম (#"উন্নত শিরোনাম", শিরোনাম, অনুপস্থিতক্ষেত্র। উপেক্ষা করুন)

এই ফাংশনটি পূর্ববর্তী ধাপ থেকে টেবিল নেয় এলিভেটেড হেডার এবং নেস্টেড লুকআপ তালিকা অনুযায়ী এটির সমস্ত কলামের নাম পরিবর্তন করে শিরোনাম. তৃতীয় যুক্তি মিসিংফিল্ড। উপেক্ষা করুন প্রয়োজন হয় যাতে সেই শিরোনামগুলিতে যেগুলি ডিরেক্টরিতে রয়েছে, কিন্তু টেবিলে নেই, একটি ত্রুটি ঘটবে না।

আসলে, যে সব.

রিকোয়েস্টে ফিরছি প্রতিবেদন আমরা একটি সম্পূর্ণ ভিন্ন চিত্র দেখতে পাব - আগেরটির চেয়ে অনেক সুন্দর:

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