রিপোর্টিং এবং ডেটা প্রক্রিয়াকরণের সাথে জড়িতদের জন্য এক্সেল লাইফ হ্যাক

2024 লেখক: Malcolm Clapton | [email protected]. সর্বশেষ পরিবর্তিত: 2024-01-13 00:49

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

এই নিবন্ধটি এক্সেলে আপনার কাজকে সহজ করার জন্য সহজ কৌশলগুলি রয়েছে৷ এগুলি তাদের জন্য বিশেষভাবে উপযোগী যারা ম্যানেজমেন্ট রিপোর্টিংয়ে নিয়োজিত, 1C এবং অন্যান্য রিপোর্ট থেকে ডাউনলোডের উপর ভিত্তি করে বিভিন্ন বিশ্লেষণাত্মক প্রতিবেদন তৈরি করে, তাদের থেকে ব্যবস্থাপনার জন্য উপস্থাপনা এবং ডায়াগ্রাম তৈরি করে। আমি নিখুঁত নতুনত্বের ভান করি না - এক বা অন্য ফর্মে, এই কৌশলগুলি সম্ভবত ফোরামে আলোচনা করা হয়েছিল বা নিবন্ধগুলিতে উল্লেখ করা হয়েছিল।

VLOOKUP এবং HLOOKUP-এর সহজ বিকল্প, যদি পছন্দসই মানগুলি টেবিলের প্রথম কলামে না থাকে: LOOKUP, INDEX + SEARCH

VLOOKUP এবং HLOOKUP ফাংশনগুলি শুধুমাত্র তখনই কাজ করে যদি পছন্দসই মানগুলি টেবিলের প্রথম কলাম বা সারিতে থাকে যেখান থেকে আপনি ডেটা পাওয়ার পরিকল্পনা করছেন৷

অন্যথায়, দুটি বিকল্প আছে:

1. LOOKUP ফাংশন ব্যবহার করুন।

   এটির নিম্নলিখিত সিনট্যাক্স রয়েছে: LOOKUP (lookup_value; lookup_vector; result_vector)। কিন্তু এটি সঠিকভাবে কাজ করার জন্য, ভিউ_ভেক্টর পরিসরের মানগুলিকে আরোহী ক্রমে সাজাতে হবে:

   

2. MATCH এবং INDEX ফাংশনের সংমিশ্রণ ব্যবহার করুন।

   MATCH ফাংশন অ্যারের একটি উপাদানের ক্রমিক সংখ্যা প্রদান করে (এর সাহায্যে আপনি খুঁজে পেতে পারেন যে টেবিলের কোন সারিতে অনুসন্ধান করা উপাদান রয়েছে), এবং INDEX ফাংশন একটি প্রদত্ত সংখ্যা সহ একটি অ্যারে উপাদান প্রদান করে (যা আমরা খুঁজে বের করব) MATCH ফাংশন ব্যবহার করে)।

   

   ফাংশন সিনট্যাক্স:

   • অনুসন্ধান (অনুসন্ধান_মান; অনুসন্ধান_অ্যারে; ম্যাচ_টাইপ) - আমাদের ক্ষেত্রে, আমাদের একটি মিল টাইপ প্রয়োজন "সঠিক মিল", এটি 0 নম্বরের সাথে মিলে যায়।

   • INDEX (অ্যারে; লাইন_সংখ্যা; [কলাম_সংখ্যা])। এই ক্ষেত্রে, আপনাকে কলাম নম্বর নির্দিষ্ট করতে হবে না, যেহেতু অ্যারে একটি সারি নিয়ে গঠিত।

কিভাবে দ্রুত একটি তালিকায় খালি ঘর পূরণ করতে হয়

কাজটি হল কলামের কক্ষগুলি উপরের মানগুলি দিয়ে পূরণ করা (যাতে বিষয়টি টেবিলের প্রতিটি সারিতে থাকে, এবং শুধুমাত্র বিষয়ের বইগুলির ব্লকের প্রথম সারিতে নয়):

"বিষয়" কলামটি নির্বাচন করুন, "হোম" গ্রুপের রিবনে ক্লিক করুন, "খুঁজে নিন এবং নির্বাচন করুন" বোতাম → "কোষের একটি গোষ্ঠী নির্বাচন করুন" → "ফাঁকা ঘর" এবং সূত্রটি প্রবেশ করা শুরু করুন (অর্থাৎ, একটি সমান রাখুন সাইন করুন) এবং শুধুমাত্র আপনার কীবোর্ডে তীর চিহ্নে ক্লিক করে উপরের কক্ষটি দেখুন। এরপর Ctrl + Enter চাপুন। এর পরে, আপনি প্রাপ্ত ডেটা মান হিসাবে সংরক্ষণ করতে পারেন, যেহেতু সূত্রগুলির আর প্রয়োজন নেই:

কিভাবে একটি সূত্রে ত্রুটি খুঁজে বের করতে হয়

একটি সূত্রের একটি পৃথক অংশের গণনা

একটি জটিল সূত্র বোঝার জন্য (যেটিতে অন্যান্য ফাংশনগুলি ফাংশন আর্গুমেন্ট হিসাবে ব্যবহৃত হয়, অর্থাৎ কিছু ফাংশন অন্যগুলিতে নেস্ট করা হয়) বা এতে ত্রুটির উত্স খুঁজে পেতে, আপনাকে প্রায়শই এটির অংশ গণনা করতে হবে। দুটি সহজ উপায় আছে:

1. সূত্র বারে একটি সূত্রের অংশ গণনা করতে, সেই অংশটি নির্বাচন করুন এবং F9 টিপুন:

   

   এই উদাহরণে, অনুসন্ধান ফাংশনের সাথে একটি সমস্যা ছিল - এতে আর্গুমেন্টগুলি অদলবদল করা হয়েছে৷ এটি মনে রাখা গুরুত্বপূর্ণ যে আপনি যদি ফাংশনের অংশের গণনা বাতিল না করেন এবং এন্টার টিপুন তবে গণনা করা অংশটি একটি সংখ্যা থেকে যাবে।

2. রিবনের সূত্র গ্রুপে গণনা সূত্র বোতামে ক্লিক করুন:

   

   প্রদর্শিত উইন্ডোতে, আপনি ধাপে ধাপে সূত্রটি গণনা করতে পারেন এবং নির্ধারণ করতে পারেন কোন পর্যায়ে এবং কোন ফাংশনে একটি ত্রুটি ঘটে (যদি থাকে):

   

একটি সূত্র কিসের উপর নির্ভর করে বা বোঝায় তা কীভাবে নির্ধারণ করবেন

একটি সূত্র কোন কক্ষের উপর নির্ভর করে তা নির্ধারণ করতে, রিবনের সূত্র গোষ্ঠীতে, কোষকে প্রভাবিত করে বোতামটি ক্লিক করুন:

তীরগুলি গণনার ফলাফলের উপর নির্ভর করে তা নির্দেশ করে।

যদি লাল রঙে ছবিতে হাইলাইট করা প্রতীকটি প্রদর্শিত হয়, তবে সূত্রটি অন্যান্য শীট বা অন্যান্য বইয়ের ঘরগুলির উপর নির্ভর করে:

এটিতে ক্লিক করে, আমরা দেখতে পাব ঠিক কোথায় প্রভাবিতকারী কোষ বা রেঞ্জগুলি অবস্থিত:

"প্রভাবিত কোষ" বোতামের পাশে রয়েছে "নির্ভরশীল কোষ" বোতাম, যেটি একইভাবে কাজ করে: এটি সক্রিয় কোষ থেকে তার উপর নির্ভরশীল কোষগুলিতে একটি সূত্র সহ তীর প্রদর্শন করে।

একই ব্লকে অবস্থিত "তীর সরান" বোতামটি আপনাকে প্রভাবিতকারী কোষে তীর, নির্ভরশীল কোষে তীর বা উভয় প্রকারের তীর একবারে অপসারণ করতে দেয়:

একাধিক শীট থেকে ঘরের মানের সমষ্টি (সংখ্যা, গড়) কীভাবে খুঁজে পাবেন

ধরা যাক আপনার কাছে একই ধরণের ডেটা সহ একাধিক শীট রয়েছে যা আপনি অন্য কোনও উপায়ে যোগ করতে, গণনা করতে বা প্রক্রিয়া করতে চান:

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

আপনি "Data1", "Data2", "Data3" শিট থেকে B3 ঠিকানা সহ কক্ষের যোগফল পাবেন:

এই ঠিকানাটি অবস্থিত শীটগুলির জন্য কাজ করে ধারাবাহিকভাবে … সিনট্যাক্সটি নিম্নরূপ: = FUNCTION (first_list: last_list! রেঞ্জ রেফারেন্স)।

কীভাবে স্বয়ংক্রিয়ভাবে টেমপ্লেট বাক্যাংশ তৈরি করবেন

এক্সেলে পাঠ্যের সাথে কাজ করার প্রাথমিক নীতিগুলি এবং কয়েকটি সাধারণ ফাংশন ব্যবহার করে, আপনি রিপোর্টের জন্য টেমপ্লেট বাক্যাংশ প্রস্তুত করতে পারেন। পাঠ্যের সাথে কাজ করার কয়েকটি নীতি:

• আমরা & চিহ্ন ব্যবহার করে পাঠ্যটিকে সংযুক্ত করি (আপনি এটিকে কনকেটনেট ফাংশন দিয়ে প্রতিস্থাপন করতে পারেন, তবে এটির খুব বেশি অর্থ হয় না)।
• পাঠ্যটি সর্বদা উদ্ধৃতিতে লেখা হয়, পাঠ্য সহ কক্ষের রেফারেন্সগুলি সর্বদা ছাড়া থাকে।
• পরিষেবা অক্ষর "উদ্ধৃতি চিহ্ন" পেতে, আর্গুমেন্ট 32 সহ CHAR ফাংশন ব্যবহার করুন।

সূত্র ব্যবহার করে একটি টেমপ্লেট বাক্যাংশ তৈরির একটি উদাহরণ:

ফলাফল:

এই ক্ষেত্রে, CHAR ফাংশন ছাড়াও (উদ্ধৃতি প্রদর্শনের জন্য), IF ফাংশন ব্যবহার করা হয়, যা আপনাকে একটি ইতিবাচক বিক্রয় প্রবণতা আছে কিনা তার উপর নির্ভর করে পাঠ্য পরিবর্তন করতে দেয় এবং TEXT ফাংশন, যা আপনাকে প্রদর্শন করতে দেয় যেকোনো বিন্যাসে সংখ্যা। এর সিনট্যাক্স নীচে বর্ণিত হয়েছে:

TEXT (মান; বিন্যাস)

বিন্যাসটি উদ্ধৃতি চিহ্নগুলিতে নির্দিষ্ট করা হয়েছে, ঠিক যেমন আপনি বিন্যাস ঘর উইন্ডোতে একটি কাস্টম বিন্যাস প্রবেশ করছেন।

আরও জটিল পাঠ্যগুলিও স্বয়ংক্রিয় হতে পারে। আমার অনুশীলনে, ম্যানেজমেন্ট রিপোর্টিং ফরম্যাটে দীর্ঘ, কিন্তু রুটিন মন্তব্যের স্বয়ংক্রিয়তা ছিল “পরিকল্পনার সাপেক্ষে XX দ্বারা সূচকের পতন/বৃদ্ধি, প্রধানত XX দ্বারা FACTOR1-এর বৃদ্ধি/পতন, FACTOR2-এর বৃদ্ধি/পতনের কারণে YY…” কারণের একটি পরিবর্তনশীল তালিকা সহ। আপনি যদি প্রায়ই এই ধরনের মন্তব্য লেখেন এবং সেগুলি লেখার প্রক্রিয়াটি অ্যালগরিদমাইজ করা যেতে পারে, তাহলে একটি সূত্র বা একটি ম্যাক্রো তৈরি করার জন্য একবার বিভ্রান্ত করা সার্থক যা আপনাকে অন্তত কিছু কাজ বাঁচাতে পারে।

সংযোজন করার পরে প্রতিটি ঘরে ডেটা কীভাবে সংরক্ষণ করবেন

যখন আপনি কক্ষগুলিকে মার্জ করেন, শুধুমাত্র একটি মান বজায় থাকে৷ সেল একত্রিত করার চেষ্টা করার সময় এক্সেল এই সম্পর্কে সতর্ক করে:

তদনুসারে, প্রতিটি কক্ষের উপর নির্ভর করে যদি আপনার কাছে একটি সূত্র থাকে, তবে এটি তাদের একত্রিত করার পরে কাজ করা বন্ধ করে দেবে (উদাহরণটির 3-4 লাইনে # N/A ত্রুটি):

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

একাধিক ডেটা উত্স থেকে কীভাবে একটি পিভট তৈরি করবেন

আপনি যদি একবারে একাধিক ডেটা উত্স থেকে একটি পিভট তৈরি করতে চান, তাহলে আপনাকে রিবন বা দ্রুত অ্যাক্সেস প্যানেলে "পিভটটেবল এবং চার্ট উইজার্ড" যোগ করতে হবে, যেখানে এই ধরনের একটি বিকল্প রয়েছে।

আপনি এইভাবে এটি করতে পারেন: "ফাইল" → "বিকল্প" → "দ্রুত অ্যাক্সেস টুলবার" → "সমস্ত কমান্ড" → "পিভটটেবল এবং চার্ট উইজার্ড" → "যোগ করুন":

এর পরে, রিবনে একটি সংশ্লিষ্ট আইকন উপস্থিত হবে, যার উপর ক্লিক করে একই উইজার্ডকে কল করে:

যখন আপনি এটিতে ক্লিক করেন, একটি ডায়ালগ বক্স উপস্থিত হয়:

এটিতে, আপনাকে "অনেক একত্রীকরণ রেঞ্জে" আইটেমটি নির্বাচন করতে হবে এবং "পরবর্তী" ক্লিক করতে হবে। পরবর্তী ধাপে, আপনি "এক পৃষ্ঠার ক্ষেত্র তৈরি করুন" বা "পৃষ্ঠা ক্ষেত্র তৈরি করুন" নির্বাচন করতে পারেন। আপনি যদি প্রতিটি ডেটা উত্সের জন্য স্বাধীনভাবে একটি নাম নিয়ে আসতে চান তবে দ্বিতীয় আইটেমটি নির্বাচন করুন:

পরবর্তী উইন্ডোতে, পিভট তৈরি করা হবে এমন সমস্ত রেঞ্জ যোগ করুন এবং তাদের নাম দিন:

এর পরে, শেষ ডায়ালগ বক্সে, পিভট টেবিলের প্রতিবেদনটি কোথায় স্থাপন করা হবে তা নির্দিষ্ট করুন - একটি বিদ্যমান বা নতুন শীটে:

পিভট টেবিল রিপোর্ট প্রস্তুত. "পৃষ্ঠা 1" ফিল্টারে, যদি প্রয়োজন হয় তবে আপনি শুধুমাত্র একটি ডেটা উত্স নির্বাচন করতে পারেন:

টেক্সট বি তে পাঠ্য A-এর সংঘটনের সংখ্যা কীভাবে গণনা করা যায় ("MTS সুপারএমটিএস ট্যারিফ" - MTS সংক্ষেপণের দুটি ঘটনা)

এই উদাহরণে, কলাম A-তে বেশ কয়েকটি পাঠ্য লাইন রয়েছে এবং আমাদের কাজ হল E1 কক্ষে থাকা অনুসন্ধান পাঠ্যটি কতবার রয়েছে তা খুঁজে বের করা:

এই সমস্যা সমাধানের জন্য, আপনি নিম্নলিখিত ফাংশন সমন্বিত একটি জটিল সূত্র ব্যবহার করতে পারেন:

1. DLSTR (LEN) - পাঠ্যের দৈর্ঘ্য গণনা করে, একমাত্র যুক্তি পাঠ্য। উদাহরণ: DLSTR ("মেশিন") = 6।
2. SUBSTITUTE - একটি টেক্সট স্ট্রিং-এ একটি নির্দিষ্ট টেক্সটকে অন্যটির সাথে প্রতিস্থাপন করে। সিনট্যাক্স: SUBSTITUTE (পাঠ্য; পুরানো_পাঠ; নতুন_পাঠ)। উদাহরণ: SUBSTITUTE ("কার"; "অটো"; "") = "মোবাইল"।
3. UPPER - একটি স্ট্রিং এর সমস্ত অক্ষরকে বড় হাতের অক্ষর দিয়ে প্রতিস্থাপন করে। একমাত্র যুক্তি পাঠ্য। উদাহরণ: UPPER ("মেশিন") = "কার"। কেস সংবেদনশীল অনুসন্ধান করতে আমাদের এই ফাংশনটি প্রয়োজন। সর্বোপরি, UPPER ("কার") = UPPER ("মেশিন")

অন্যটিতে একটি নির্দিষ্ট টেক্সট স্ট্রিং এর উপস্থিতি খুঁজে পেতে, আপনাকে আসলটির সমস্ত ঘটনা মুছে ফেলতে হবে এবং আসলটির সাথে ফলাফলের স্ট্রিংটির দৈর্ঘ্য তুলনা করতে হবে:

DLSTR ("ট্যারিফ MTS সুপার MTS") - DLSTR ("ট্যারিফ সুপার") = 6

এবং তারপরে আমরা যে স্ট্রিংটি খুঁজছিলাম তার দৈর্ঘ্য দ্বারা এই পার্থক্যটি ভাগ করুন:

6 / DLSTR (“MTS”) = 2

এটা ঠিক দুইবার যে লাইন "MTS" মূল এক অন্তর্ভুক্ত করা হয়েছে.

সূত্রের ভাষায় এই অ্যালগরিদমটি লিখতে বাকি আছে (আসুন আমরা যে পাঠ্যটি "পাঠ্য" দ্বারা বোঝাই যেটিতে আমরা ঘটনাগুলি খুঁজছি, এবং "চাওয়া হয়েছে" - যার সংঘটনের সংখ্যায় আমরা আগ্রহী):

= (DLSTR (টেক্সট) -LSTR (SUBSTITUTE (UPPER (টেক্সট); UPPER (সার্চ), ""))) / DLSTR (সার্চ)

আমাদের উদাহরণে, সূত্রটি এইরকম দেখাচ্ছে:

= (DLSTR (A2)-LSTR (SUBSTITUTE (UPPER (A2), UPPER ($E $1), “”))) / DLSTR ($E $1)