یافتن سطرهای تکراری دوصفحه در اکسل با استفاده از VBA

امتیاز 5.00 ( 1 رای )

توجه:برای استفاده و مطلع شدن از سایر آموزش های رایگان و غیر رایگان سایت در موارد زیر عضویت یابید:


یافتن رکوردهای های تکراری و غیر تکراری و کپی در صفحه دیگر اکسل

یافتن رکوردهای های تکراری و غیر تکراری و کپی در صفحه دیگر اکسل


یافتن سطرهای تکراری یا غیر تکراری (مشترک یا غیرمشترک) دو صفحه و انتقال به صفحه دیگر اکسل:

شاید برای شما هم پیش آمده باشد که در دو صفحه اکسل تعدادی رکورد  همچون تصویر زیر (به منظور مشاهده عکس در تصویر بزرگتر روی آن کلیک راست کنید و گزینه Open Image In New Tab را بزنید.) قصد دارید آن دسته از سطرهایی که در دو صفحه یکسان (غیر یکسان) هستند، را بیابید و به صفحه دیگری در اکسل منتقل کنید.

حتما در سایت عضو شوید و در پایین صفحه فیلم این آموزش را دانلود و به توضیحات دقیق من توجه بفرمایید.

پاسخ و راه حل این مشکل استفاده از کدنویسی VBA است؛ زیرا در نرم افزار اکسل چنین ابزاری به صورت پیش فرض وجود ندارد که بتواند سطرهای تکراری یا غیر تکراری دوصفحه را بیابد و به صفحه دیگری در اکسل منتقل کند. درسته که در اکسل ابزاری به اسم Remove Duplicates وجود دارد، ولی این ابزار تنها سلول های تکراری شما را حذف می نماید و نمی تواند نیاز ما را با شرایط توضیح داده شده رفع نماید.

رکورد ها صفحه اول و دوم

رکوردها در صفحه اول و دوم

رکوردها در صفحه سوم

رکوردها در صفحه سوم

خب حالا که متوجه شدیم گزینه دیگری جز استفاده از کدنویسی VBA روی میز نیست بدون هیچ ترسی میریم سراغش؛

توصیه: اگر تا به کنون کدنویسی انجام نداده اید و تب(زبانه) Developer را فعال نکرده اید حتما آموزش ماکرو چیست؟ را مشاهده بفرمایید.

حتما در سایت عضو شوید و در پایین صفحه فیلم این آموزش را دانلود و به توضیحات دقیق من توجه بفرمایید.

یافتن سطرهای تکراری دوصفحه در اکسل:

در ابتدا به منظور یافتن سطرهای تکراری باید یک فیلد (ستون) را انتخاب کنید که بر اساس آن سرچ انجام شود و تشخیص دهیم کدام سطر، تکراری است. همانطور که در تصویر بالا مشاهده می فرمایید بهترین فیلد برای مقایسه تکراری بودن رکوردها، کد فروش است؛ زیرا به ازای تمامی فروش ها منحصر به فر است، مگر همان رکوردهای تکراری که قصد پیدا کردن آن را داریم.

باید به ازای تمامی (۱) کد فروش های موجود در صفحه اول، با تمامی (۲) کد فروش های صفحه دوم چک شود، که آیا کد فروش موجود در صفحه اول برابر است کد فروش صفحه دوم؟ اگر (۳) برابر شد اکنون به صفحه سوم برود و بر اساس یک فیلد(مثلا همان ستون اول) بررسی کند تمای سلول های  این ستون (۴) را که اگر (۵) مقدارش خالی بود مقدار پیدا شده یکسان (کد پرسنلی) و ستون های کناری شامل (نام، نام خانوادگی، محل سکونت و موبایل) (۶) را همان جا قرار دهد اگر نه به سراغ سطر بعدی برود و به حدی حلقه تکرار شود تا سطر خالی دیگری در صفحه سوم برای قرار دادن رکورد تکراری، پیدا شود. سپس از حلقه سوم خارج شود (۷)؛ زیرا اگر از حلقه سوم (مربوط به بررسی صفحه سوم) خارج نشویم این حلقه به سراغ سایر سطرهای خالی دیگر می رود و رکورد یکسان شناخته شده موجود را، در آنجا هم قرار می دهد. همچنین باید از حلقه دوم هم خارج شویم(۸)؛ چون زمانی که کدفروشی از صفحه اول برابر کدفروش دیگری در صفحه دوم می شود و در صفحه سوم قرار گرفته باشد دیگر نیاز نیست که بررسی شود که آیا همان کد فروش از صفحه اول با سایر کدفروش های دیگر صفحه دوم یکسان است یا نه؟! پس از حلقه دوم خارج می شویم و به حلقه اول باز می گردد(یعنی همین کارها را برای کدپرسنلی بعدی از سلول های محدوده A2:A20 صفحه اول انجام می دهد.). اگر توجه کرده باشید تمامی کدنویسی لازم را در بالا به صورت زبان فارسی توضیح دادم.خیلی خیلی راحت… اما حالا مفهوم این توضیح را بازتر میکنیم:

نکته: اگر کلمه کلیدی Exit For در هر حلقه ای استفاده شود، موجب خروج از آن حلقه و رفتن به خط بعد Next آن حلقه می شود.

(۱) در اینجا در حال اشاره به یک حلقه ای هستم (گزینه بهتر حلقه For Each – ار اینجا آموزشش را ببینید.) که باید روی سلول های محدوده A2:A20 صفحه اول بچرخد.

(۲) در اینجا هم در حال اشاره به یک حلقه ای هستم (گزینه بهتر حلقه For Each – ار اینجا آموزشش را ببینید.) که باید روی سلول های محدوده A2:A20 صفحه دوم بچرخد. نوشتن حلقه اول و قرار دادن حلقه دوم در دل آن، یعنی هربار یکی از سلول های محدوده A2:A20 از صفحه اول انتخاب می شود و قرار است با تمامی سلول های محدوده A2:A20 صفحه دوم مقایسه شود (که آیا یکسان است یا خیر؟).

(۳) در اینجا در حال بررسی شرط برابر بودن همان سلول های برگزیده شده برای مقایسه تکراری بودن رکورد بر اساس دو حلقه (۱) و (۲) هستیم.

(۴) در اینجا در حال اشاره به یک حلقه ای هستم (گزینه بهتر حلقه For Each – ار اینجا آموزشش را ببینید.) که باید روی سلول های محدوده A2:A20 صفحه سوم بچرخد.

(۵) در اینجا در حال بررسی شرط خالی بودن سلولس از محدوده A2:A20 صفحه سوم هستیم.

(۶) در اینجا با استفاده از properties از نوع Offset (آموزشش را از اینجا مشاهده بفرمایید.) میتوانیم به همان کد پرسنلی که یکسان شناخته شده است و ستون های کناری اش، اشاره کنیم و همان ها را به کمک همان ویژگی Offset، به ترتیب در سلول های صفحه سومی که بر اساس (۵) خالی شناسایی شده است، قرار دهیم.

(۷) و (۸) در بالا به کاملی در بالا توضیح داده شد.

کدنویسی انجام شده مظابق با توضیحات بالا، دقیقا به صورت تصویر زیر می باشد:

کدنویسی یافتن رکورد های تکراری دو صفحه و انتقال به صفحه دیگر در نرم افزار اکسل

کدنویسی یافتن سطرهای تکراری دو صفحه و انتقال به صفحه دیگر در نرم افزار اکسل

حتما در سایت عضو شوید و در پایین صفحه فیلم این آموزش را دانلود و به توضیحات دقیق من توجه بفرمایید.

یافتن سطرهای غیر تکراری دوصفحه در اکسل:

ممکن است، در ابتدا این فکر به ذهن شما خطور کند که اگر در مورد (۵) در توضیحات قبلی علامت = را به علامت <> (نابرابر) تبدیل کنید. کار حله!!! اما اصلا اینجوری نیست و شما به راحتی می توایند با زدن دکمه F8 از صفحه کلید، کد را به صورت خط به خط اجرا کنید و ببیند که نحوه اجرای کدنویسی برای نیاز ما اصلا رعایت نمیشه…

شاید بتوانم بگویم اصلی ترین تفاوت کدنویسی یافتن سطرهای تکراری دوصفحه با سطرهای غیر تکراری دوصفحه این است که ما در  یافتن سطرهای تکراری دوصفحه که در بالا آموزش دیدیم، هر بار یکی از سلول های موجود در محدوده A2:A20 صفحه اول را انتخاب و تا زمانی که مساوی با یکی از سلول های محدوده A2:A20 صفحه دوم شود ادامه می دادیم و ادامه کار… . ولی در یافتن سطرهای غیر تکراری دوصفحه باید، هرسلولی که از سلول های موجود در محدوده A2:A20 صفحه اول انتخاب می شود با تمامی سلول های موجود در محدوده A2:A20 صفحه دوم مقایسه شود و اگر مساوی نبود سپس ادامه کار… .

برای این منظور، در سطر اول ابتدا به تمامی کدهای فروش موجود در صفحه اول اشاره می شود (۱) که باید با استفاده از حلقه For each (آموزش این حلقه را اینجا مشاهده کنید.) روی آن حرکت کنیم. در سطر بعدی متغییری با نام دلخواه قرار می دهیم و و مقدار آن را را برابر خالی (” “) می گذاریم (۲). اینکه این ظرف یا متغییر، دقیقا بعد از سطر مربوط به حلقه ای که روی سلول های محدوده A2:A20 صفحه اول (حلقه اول) ، تعریف و برابر خالی گذاشته می شود، این است که؛ بعد از هر بار چرخش حلقه اول مقدار متغییر تعریف شده خالی شود. دلیل وجودی تعریف این متغییر را در ادامه توضیحات متوجه می شویم. سپس حلقه بعدی تعریف می شود که این حلقه روی سلول های محدوده A2:A20 صفحه دوم می چرخد(۳). حال در مرحله بعدی چک می شود(۴) که آیا سلول انتخابی از سلول های موجود در محدوده A2:A20 صفحه اول برابر با سلول های موجود در محدوده A2:A20 صفحه دوم است یا نه؟ اگر برابر بود، داخل ظرف یا متغییر تعریف شده OP واژه ای دلخواه، به عنوان مثال “Duplicate” قرار گیرد و از حلقه دوم خارج شود (۵). خروج از حلقه دوم (یعنی ورود به خط بعد از Next Rng2). اگر به انتهای حلقه دوم رسیدیم و برابر نبود، باز هم از حلقه ذوم، خود به خود خارج شود با این تفاوت که دیگر درون ظرف OP خالی است.(یعنی ورود به خط بعد از Next Rng2).

پس دو حالت داریم که ممکن است به وجود بیاید:

سلولی از سلول های محدوده A2:A20 صفحه اول = سلولی از سلول های محدوده A2:A20 صفحه دوم>>> متغییر OP=”Duplicate” و خروج از حلقه  و ورود به سطری بعد از Next Rng2

سلولی از سلول های محدوده A2:A20 صفحه اول <> سلولی از سلول های محدوده A2:A20 صفحه دوم>>> متغییر OP=”” و خروج از حلقه دوم و ورود به سطری بعد از Next Rng2

در سطر بعد از Next Rng2 بررسی می کنیم که درون ظرف یا متغییر OP چه چیزی است؟!(۶) اگر خالی بود؛ یعنی برابر نشده و سطر تکراری نیست و باید به فکر منتقل کردن اطلاعات به صفحه سوم باشیم(۷). اگر داخل ظرف OP مقداری غیر خالی بود؛ یعنی اینکه برابر شده است و سطر تکراری است و شرط دستور If برقرار نمی شود، دستور If و End If رد می شود و به سراغ next Rng1 می رود.

داخل دستور If فعلی هم مثل بالا که توضیح داده شد یک حلقه تشکیل می دهیم که به بررسی سلول های خالی ستون A از صفحه سوم بپردازد و بتوانیم سطرهای غیر تکراری را به صفحه سوم منتقل کنیم.

کدنویسی انجام شده مطابق با توضیحات بالا، دقیقا به صورت تصویر زیر می باشد:

کدنویسی یافتن سطرهای غیرتکراری دو صفحه و انتقال به صفحه دیگر در اکسل

کدنویسی یافتن سطرهای غیرتکراری دو صفحه و انتقال به صفحه دیگر در اکسل

توصیه میکنم که اگر به کدنویسی VBA یا ویژوال بیسیک در اکسل علاقه دارید، مجموعه آموزشی جامع کدنویسی ویژوال بیسیک کاربردی در اکسل که حاصل تجربه و مهارت و چندین کلاس بین المللی و داخلی است رو از دست ندهید. 

در این قسمت طی یک فیلم، به آموزش یافتن سطرهای تکراری یا غیر تکراری (مشترک یا غیرمشترک) دو صفحه و انتقال به صفحه دیگر اکسل می پردازیم. اگر مفید بود یا درخواست آموزشی مشابه دارین، زیر همین پست کامنت بگذارید.

مطالعه بیشتر