حذف داده های تکراری

به منظور امکان استخراج داده های یکتا یا حذف داده های تکراری مربوط به یک ستون در بحث آموزش اکسل پیشرفته هفت روش به شرح زیر ارائه شده است.

فرض کنید ما اینجا جدولی داریم که حاوی ستون نام محصول هست و این نام محصول در ردیف های مختلف تکرار شده است. حالا ما می‌خواهیم نام محصول ها را به صورت یکتا یا واحد داشته باشیم و تکراری ها را حذف کنیم .

هفت تا روش برای این کار می‌توانیم داشته باشیم که مزایا و معایب هر کدام از روش ها به طور خلاصه به شرح زیر می‌باشد که در ادامه چگونگی استفاده از آنها ارائه شده است.

روش اول حذف داده تکرای: استفاده از گزینه REMOVE DUPLICATES

ستونی که می‌خواهیم DUPLICATE ها یا داده های تکراری‌ آن را حذف کنیم را انتخاب می‌کنیم.

برای این کار با قرارگیری در سر ستون مورد نظر و با استفاده از کلیدهای میانبر CTRL+SHIFT و کلید جهت نما به سمت پایین ستون مورد نظر را انتخاب می‌کنیم.

پس از انتخاب ستون مورد نظر با استفاده از CTRL+C داده های ستون را کپی می کنیم و سپس در نمونه زیر با قرارگیری در سلول E11 که قرار است داده‌های یونیک را داشته باشیم داده‌ها را PASTE می‌کنیم.

پس از آن بلافاصله در TAB DATA گزینه REMOVE DUPLICATES را مطابق شکل زیر انتخاب می کنیم.

در منوی نمایش داده شده سوال می کند که آیا عنوان دارید؟
با توجه به اینکه عنوان کپی شده، تیک آن را فعال می کنیم. ضمناً تیک قسمت مربوط به اینکه کدام ستون موارد تکراری آن را می خواهید حذف کنید را می زنید (PRODUCT) سپس OK می‌کنید.

در این قسمت پیغام داده می شود که 14 داده یونیک پیدا کرده و مابقی را حذف نموده است.

روش دوم حذف داده تکرای: استفاده از ADVANCE FILTER

روش دوم ساده‌تر از روش اول است و بدین صورت می‌باشد که در اکسل در TAB DATA گزینه ADVANCE را انتخاب می‌کنیم فقط قبل از آن یکی از سلول های این جدول باید انتخاب شده باشد تا خودش محدوده درست را تشخیص بدهد.

ADVANCE را که بزنیم گزینه اول FILTER است ولی گزینه دوم که کپی می باشد را انتخاب می‌کنیم.

در قسمت RANGE LIST ستونی که مد نظر می باشد را انتخاب می‌کنیم($B:$B)، در قسمت CRITERIA چیزی نمی‌نویسیم و مورد بعد در COPY TO در پاسخ به این که کجا کپی کند مثلاً می‌گوییم در سلول F11 کپی کند.

نکته مهم که بایستی توجه کرد این است که تیک مرتبط با داده های یونیک را بزنیم و سپس OK .

و نهایتاً داده‌های یونیک را به روش دوم در محدوده مورد نظر درج می‌نماید:

روش سوم حذف داده تکرای: استفاده از ترکیب توابع

روش سوم نسبت به دو روش فوق یک مقدار پیچیده تر می‌باشد

ولی مزیتی که دارد این است که اگر داده‌ای به این جدول اضافه کنیم در روش سوم بطور اتوماتیک، داده جدید را در نتایج ما ارائه می نماید ولی در روش های قبلی این اتفاق نمی‌افتد

و شما هر بار باید نسبت به اجرای مجدد کار اقدام کنید. خوب برویم در کنار ستون های قبلی در سلول G11 روش سوم را بکار ببریم عنوان ستون را کپی می کنیم سپس در سلول G12 نسبت به درج فرمول اقدام می کنیم که در این روش با تابع COUNTIF در اکسل شروع می‌کنیم.

می ‌پرسد که محدوده مورد نظرتان کجا است؟

محدوده را می‌گوییم از سلول قبل به صورت ثابت تا خودش بصورت غیر ثابت و اینجا باید فقط این سلول را ثابت کنیم و بعد می‌گوید شرطتتان چیست؟

شرط ما، کل ستون B می‌باشد که اگر محدوده داده‌ها را TABLE کرده باشیم یک سلول را انتخاب کنیم.

CTRL+SPACE را بزنیم کل آن ستون انتخاب می‌شود CTRL+BACKSPACE را بزنیم بر می‌گردد به فرمول.

و اگر پرانتز را ببندیم و بخواهیم نتیجه را نگاه کنیم، کلید F9 را می‌زنیم،

نتیجه را ببینید به‌ازاء همه سلول ها مواردی که در سلول بالایی آن بوده عدد می‌گیرد و بقیه موارد صفر می‌شود. اما از آنجائیکه کلمه PRODUCT در محدوده انتخابی ما نبوده، کلا صفر شد.

حالا می‌خواهیم چه کار کنیم؟

می‌خواهیم بگوییم که تابع MATCH عدد صفر را در این داده ها پیدا کند. به چه صورت؟

در اینجا قبل از این فرمول می‌نویسیم MATCH پرانتز باز چی را؟
MATCH کند صفر را در کجا؟ در محدوده‌ای که تابع COUNTIF را نوشته ایم و آخرش هم کاما صفر، پرانتز بسته که اینجا نتیجه ما چی می‌شود؟

وF9 را بزنیم .

نتیجه یک می‌شود و حالا که نتیجه یک شد این را می‌گذاریم داخل تابع INDEX، لذا INDEX پرانتز باز می‌گوید

چه چیزی را INDEX کنیم باز ستون B را باید انتخاب کنیم.

برای این کار مجدداً یک سلولش را کلیک می‌کنیم CTRL+SPACE کل ستون انتخاب می‌شود و کاما، سلول چندمش را درج نماید

که اینجا می‌شود سلول اول و بعد می‌رود یکی یکی سلول های غیر تکراری را برای ما می‌آورد.

نکته این تابع این است که اگر بخواهیم این تابع را اعمال کنیم

بدلیل اینکه در این سلول از تابع COUNTIF بصورت آرایه‌ای استفاده کرده‌ایم در قسمت CRITERIA به جای یک سلول یک محدوده داده‌ایم و باید CTRL+SHIFT+ENTER را بزنی تا جواب را به ما بدهد

که اگر آن را بسط دهیم تا پایین می‌ببینید همه موارد را می‌آورد و مواردی که موردی نداشته پیغام خطای #N/A را نمایش می‌دهد.

در این حالت می‌توانیم تابع IFERROR را به اول فرمول اضافه کنیم اگر ERROR بود چه کاری کند؟

خالی بگذارد، پس دو تا دبل کوتیشن و CTRL+SHIFT+ENTER و بسط تا پایین و لذا دیگر جایی که ERROR باشد را خالی نشان می‌دهد.

الان خوبی این روش این است که اگر یک محصول جدید داشته باشیم مثلا XX، ببینید می‌آید و در ستون مورد نظر نمایش داده می شود درصورتیکه در سایر روشهای فوق این اتفاق نمی‌افتاد.

یک کار دیگری هم که می‌خواهیم بکنیم اینکه CTRL+SHIFT+ENTER را نزنیم و به عبارتی حالت آرایه‌ای نداشته باشیم.
برای این کار می‌توانیم قسمت COUNTIF در فرمول فوق را در داخل تابع INDEX بگذاریم.

به چه صورت، مطابق نمونه زیر می‌نویسیم INDEX پرانتز باز می‌آییم آخر تابع COUNTIF کاما و پرانتز را می بندیم. توضیح این مورد که INDEX کاما پرانتز بسته چه کاری می‌کند در آموزش تابع INDEX توضیح داده شده است.

بر این اساس دیگر فرمول فوق آرایه‌ای نیست و اینتر بزنیم کافی است و تا پایین بسط می‌دهیم.

این فرمول خیلی خوب و دقیق جواب را به صورت داینامیک به ما می دهد.

روش چهارم حذف داده تکرای: استفاده از تابع یونیک

تابع یونیک را فقط در آفیس 365 داریم که در اکسل نمی‌تونم به شما نشان بدهم و می‌رویم در گوگل شیت و عملکرد تابع یونیک را در آنجا مشاهده می کنیم.
خب اینجا تابع یونیک را به این صورت در سلول E1 می‌نویسیم.

می پرسد که محدوده مورد نظرتان کجا می‌باشد؟
محدوده مورد نظر این داده‌ها کل ستون B می باشد، سپس پرانتز بسته اینتر و به همین سادگی داده ها به صورت یونیک درج می گردد که داینامیک هم می باشد

یعنی اگر یک داده جدید در جدول اضافه بشود، در ستون E داده مرتبط اضافه می‌شود پس ما اگر به آفیس 365 دسترسی ‌داشتیم، به همین راحتی می‌توانیم این کار را انجام بدهیم و نیازی به استفاده از فرمول‌های پیچیده نمی‌باشد.

روش پنجم حذف داده تکرای: استفاده از PIVOT TABLE

خوب برویم روش بعدی که استفاده از PIVOT TABLE هست. داده ها را در ابتدا به PIVOT تبدیل می‌کنیم، در زبانه INSERT،PIVOT TABLE را می زنیم و جدول را انتخاب می کنیم، می‌پرسد که نتیجه را می خواهی کجا بگذاری؟
مثلاً می‌گوییم توی همین شیت، کجا؟ فرض کنید بطور مثال در کنار داده های قبلی در سلول H11 بگذاریم و OK را بزنیم.

وقتی که پیوت را ایجاد کردیم فقط PRODUCT را می‌آوریم در قسمت ROWS، که اینجا می بینید PRODUCTها را به صورت یونیک آورده است.

می توانید پنجره PIVOT TABLE FIELDS را ببندید و همچنین می‌توانیم جمع کل (GRAND TOTAL) را هم کلیک راست کنیم تا نمایش داده نشود.

روش ششم حذف داده تکرای: استفاده از POWER QUERY

برای اینکه از POWER QUERY استفاده کنیم یا باید آفیس 2010 و 2013 داشته باشیم و افزونه POWER QUERY را دانلود و نصب کنم یا آفیس 2016 یا 2019 باشد

و در این حالت در قسمت GET & TRANSFORM گزینه های آن را داریم و نیازی به نصب هیچ افزونه‌ای نداریم.
خوب در این حالت یکی از سلول های جدول را انتخاب می کنیم گزینه FROM TABLE را می‌زنیم تا برود در محیط QUERY EDITOR.

خوب در اینجا ستونی که می‌خواهیم ستون PRODUCT می‌باشد و انتخاب می‌کنیم سپس کلیک راست و گزینه REMOVE OTHER COLUMN را انتخاب می‌کنیم تا بقیه ستون ها را حذف کند.

و دوباره کلیک راست و DUPLICATES REMOVE.

و حالا نتیجه را می‌خواهیم بفرستیم در اکسل، گزینه CLOSE & LOAD TO و اینجا هم یک پنجره باز می‌شود و می‌پرسد که کجا می‌خواهی داده ها را درج کنی؟

که مثلاً می‌گوییم در همین شیت در سلول I11.

و درمحدوده تعریف شده داده‌های استخراج شده LOAD می‌شود. این روش نیز آپدیت پذیر می‌باشد

ولی نیاز به رفرش دارد.خوب برای اینکه رفرش بشود مثلا ما YYY می گذاریم حالا باید چه کار کنیم؟ باید روی جدول کلیک راست کنیم و گزینه رفرش را بزنیم تا YYY بیاید و درج شود.

روش هفتم: استفاده از POWER PIVOT یا DATA MODEL یا فرمولهای DAX

این روش به این صورت می‌باشد که محلی که می‌خواهیم جدول قرار بگیرد آن سلول را انتخاب می‌کنیم، قبل از این کار حتما باید محدوده داده ها به TABLE تبدیل شده باشد یعنی چی؟
یعنی کل محدوده‌ای که انتخاب کرده باشیم و CTRL+T را زده باشیم که الان چون قبلا این کار را کرده‌ام دوباره نمی‌توانم به جدول تبدیلش کنم، خوب پس محدوده داده ها را به TABLE تبدیل کرده باشید.
برای روش POWER PIVOT یا DATA MODEL در سلول مورد نظر( مثلاً J11) کلیک می‌کنیم و در TAB DATA گزینه GET EXTERNAL DATA و EXISTING CONNECTION و اینجا TAB TABLE را انتخاب می‌کنیم

و اینجا هم جدول مورد نظر را (SALE) انتخاب می‌کنیم و سپس OPEN را می‌زنیم.

با توجه به پنجره باز شده سوال می‌پرسد که نتیجه را کجا بگذارد که انتخاب ما همین جایی که سلولش را انتخاب کردیم(J11) می باشد سپس OK را می زنیم.

OK را که بزنیم بجای اینکه ستون مورد نظر (PRODUCT) را بصورت یونیک درج نماید کل داده ها را می آورد.

حالا باید کاری کنیم که درست بشود. برای این کار کلیک راست می‌کنیم روی این جدول گزینه TABLE و DAX EDIT.

در اینجا می‌گوییم حالت دستورهای ما دستورهای DAX می باشد نام جدول را خودش می آورد و قبل از نام جدول کلمه EVALUATE را تایپ می‌کنیم
و بعد می‌توانیم اینتر را بزنیم یا می‌توانیم پشت سرهم در یک خط بنویسیم که برای سادگی کار اینتر می‌زنیم و در خط بعدی از تابع ALL استفاده می‌کنیم.
در این حالت مانند نمونه ارائه شده به شرح زیر بعد از ALL پرانتز باز SALE را که خودش نوشته بود می‌نویسیم
بعد از آن در داخل براکت، ستونی که می‌خواهیم داده‌های یونیک آن را داشته باشیم عنوان آن را می نویسیم، در این مثال عنوان ما چیست؟
عنوان ما PRODUCT می‌باشد. لازم به ذکر است این قسمت به حروف بزرگ و کوچک حساس نیست و اینجا هم پرانتز را می بنیدم و نهایتاً OK.

ببینید این روش هم داده های ما را به صورت یونیک می آورد که اگر بخواهیم REFRESH کنیم دوباره باید کلیک راست و گزینه REFRESH را بزنیم تا این نتیجه را به ما بدهد و نتیجه اش آپدیت بشود.