مایکروسافت اکسل یک برنامه کاربردی است که به طور گسترده مورد استفاده قرار می گیرد، اما در مورد حذف و حذف داده های تکراری می تواند کمی گیج کننده باشد. حذف موارد تکراری در اکسل یک کار رایج برای افرادی است که روی مجموعه داده های بزرگ کار می کنند. هنگامی که جداول مختلف را ترکیب می‌کنید، یا زمانی که افراد زیادی به یک سند دسترسی دارند، ممکن است در صفحه‌گسترده خود داده های تکراری داشته باشید. بنابراین، داده ها اضافی می شوند. هرچه مجموعه داده بزرگتر باشد، شانس مواجهه با رکوردهای تکراری بیشتر است. اگر به درستی شناسایی و مدیریت نشود، می تواند مشکل ساز باشد. در این مطلب از سلام دنیا درباره نحوه حذف داده های تکراری در اکسل صحبت خواهیم کرد. با ما همراه باشید.
نحوه حذف داده های تکراری در اکسل
ما نحوه حذف موارد تکراری در اکسل را با استفاده از مجموعه داده Sports نشان خواهیم داد. این مجموعه داده حاوی اطلاعاتی در مورد مدال آوران المپیک برای سال 2012 است.
موارد تکراری را پیدا و حذف کنید.
داده‌های تکراری گاهی اوقات مفید هستند، اما اغلب درک داده‌ها را سخت‌تر می‌کنند. پیدا کردن، برجسته کردن و بررسی موارد تکراری قبل از حذف بهتر از حذف فوری همه موارد تکراری است.
حتما بخوانید: 50 کلید میانبر در اکسل
1. محدوده سلول های حاوی مقادیر تکراری را که باید حذف شوند انتخاب کنید.
توجه: بهترین راه برای حذف موارد تکراری این است که همه طرح‌ها یا جمع‌های فرعی را از داده‌های خود حذف کنید.
2. با انتخاب Data > Remove Duplicates و سپس علامت زدن یا برداشتن علامت ستون هایی که می خواهید پاک شوند، می توانید رکوردهای تکراری را حذف کنید.
3. سپس بر روی OK کلیک کنید .
مراحل حذف مقادیر تکراری در اکسل
اکسل یک ابزار داخلی دارد که به حذف ورودی های مکرر در مجموعه داده شما کمک می کند. بیایید نگاهی به مراحلی که باید برای حذف موارد تکراری در اکسل دنبال شود، بیاندازیم.
مرحله 1: ابتدا، روی هر سلول یا محدوده خاصی در مجموعه داده ای که می خواهید موارد تکراری را از آن حذف کنید، کلیک کنید . اگر روی یک سلول کلیک کنید، اکسل به طور خودکار محدوده را در مرحله بعد برای شما تعیین می کند.
مرحله 2: در مرحله بعد، گزینه " Remove Duplicates " را پیدا کرده و آن را انتخاب کنید.
- DATA tab → Data Tools section → Remove Duplicates
مرحله 3: یک کادر محاوره ای مانند شکل زیر ظاهر می شود. می‌توانید ستون‌هایی را که می‌خواهید مقایسه کنید انتخاب کنید و داده‌های تکراری را بررسی کنید.
در صورتی که داده‌های شما شامل سرصفحه‌های ستون است، گزینه «my data has headsrs» را انتخاب کرده و روی OK کلیک کنید.
با بررسی گزینه هدر، ردیف اول برای حذف مقادیر تکراری در نظر گرفته نمی شود.
مرحله 4: اکسل اکنون ردیف های تکراری را حذف می کند و یک کادر محاوره ای نمایش می دهد. کادر محاوره ای خلاصه ای از تعداد مقادیر تکراری پیدا و حذف شده به همراه تعداد مقادیر منحصر به فرد را نشان می دهد.
مرحله 5: همانطور که متوجه شدید، رکوردهای تکراری حذف می شوند.
بیایید به جلو حرکت کنیم و نحوه حذف موارد تکراری در اکسل را با استفاده از گزینه Advanced Filter درک کنیم.
با هدف به دست آوردن لیستی از مقادیر منحصر به فرد، می توانید مقادیر منحصر به فرد را فیلتر کنید یا مقادیر تکراری را حذف کنید. وظایف در خدمت یک هدف مشترک است. با این حال، یک تفاوت اساسی وجود دارد. در فیلتر کردن مقادیر منحصر به فرد، مقادیر تکراری به طور موقت پنهان می شوند، در حالی که گزینه حذف مقادیر تکراری به طور دائم مقادیر تکراری را حذف می کند.
حتما بخوانید: آموزش تبدیل عکس به اکسل در کامپیوتر
همچنین، درک این نکته ضروری است که مقایسه مقادیر تکراری به آنچه در سلول ظاهر می‌شود، متکی است نه مقدار زیرین موجود در سلول. برای مثال، اگر دو سلول با مقدار تاریخ یکسان به‌عنوان «3/9/2022» و «مارس 9، 2022» قالب‌بندی شوند، به‌عنوان دو مقدار منحصربه‌فرد در نظر گرفته می‌شوند. بنابراین، قبل از حذف موارد تکراری بررسی کنید. سعی کنید مقادیر منحصر به فرد را فیلتر یا به صورت مشروط قالب بندی کنید تا نتایج مورد انتظار را به دست آورید.
فیلتر برای مقادیر منحصر به فرد
مراحل زیر را برای فیلتر کردن مقادیر منحصر به فرد انجام دهید:
1. با انتخاب محدوده سلول ها شروع کنید. شما باید مطمئن شوید که سلول فعال در یک جدول است.
2. در مرحله بعد باید در گروه Sort & Filter گزینه Advanced filter را پیدا کرده و انتخاب کنید.
Data→ sort&filter → Advanced.
گزینه مرتب سازی و فیلتر
3. کادر بازشو Advanced Filter روی صفحه نمایش شما ظاهر می شود. می توانید یکی از اقدامات زیر را انجام دهید:
- اگر می‌خواهید محدوده سلول‌ها/جدول‌های موجود را فیلتر کنید، روی فیلتر کردن لیست در محل کلیک کنید.
- اگر باید نتایج فیلتر را در مکان دیگری کپی کنید، اقدامات زیر را انجام دهید:
- برای کپی کردن مقادیر، روی گزینه “Copy to other location” کلیک کنید.
- در کادر "copy to"، یک مرجع سلولی را وارد کنید که در آن مقادیر حاصل باید کپی شوند.
- می‌توانید به‌طور موقت روی گزینه Collapse Dialog ( ) کلیک کنید تا پنجره بازشو پنهان شود. سپس، هنگامی که یک
- سلول در کاربرگ را انتخاب کردید، روی گزینه "Expand" () کلیک کنید.
- کادر گزینه «Unique records only» را علامت بزنید و سپس روی OK کلیک کنید.
حذف داده های تکراری در اکسل با استفاده از گزینه فیلتر پیشرفته
گزینه Advanced Filter در اکسل به شما کمک می کند مقادیر تکراری را فیلتر کرده و مقادیر منحصر به فرد را در مکان دیگری کپی کنید. برای اطلاع از نحوه عملکرد گزینه Advanced Filter به مراحل زیر توجه کنید.
- ابتدا روی سلول یا محدوده ای در مجموعه داده ای که می خواهید موارد تکراری را از آن حذف کنید، کلیک کنید. اگر روی یک سلول کلیک کنید، اکسل به طور خودکار محدوده را با کلیک بر روی Advanced Filter تعیین می کند.
- گزینه Advanced Filter را پیدا کنید.
DATA tab → Sort & Filter section → Advanced
و روی آن کلیک کنید.
- یک کادر محاوره ای خواهید دید. این شامل لیستی از گزینه های فیلتر پیشرفته است.
- برای کپی کردن مقادیر منحصر به فرد در مکان دیگری، گزینه "copy to another location" را انتخاب کنید.
- محدوده رکوردهای خود را در قسمت "List Range" بررسی کنید و مطمئن شوید که محدوده ای است که شما مشخص کرده اید.
- در فیلد «copy to»، محدوده‌ای را وارد کنید که مقادیر منحصربه‌فرد حاصل باید کپی شوند.
- کادر «only Unique records» را علامت بزنید. این مرحله مهم ترین است.
- روی OK کلیک کنید.
- مقادیر منحصر به فرد در سلول G1 کپی می شود.
اینها عملکردهای داخلی در اکسل بودند که به ما در حذف موارد تکراری کمک می کنند. اکنون، بیایید به جلو برویم و یاد بگیریم که چگونه می توانیم عملکرد خود را برای انجام همان کار ایجاد کنیم.
چگونه از فرمول ها برای حذف موارد تکراری در اکسل استفاده کنیم؟
ما از یک مثال ساده شامل ستون‌ها استفاده خواهیم کرد: نوع ورزش، نام ورزشکار و مدال کسب شده برای نشان دادن این رویکرد.
این روش شامل ترکیب ستون ها با استفاده از فرمول اکسل و یافتن تعداد است. سپس مقادیر تکراری (آنهایی که تعداد آنها بیشتر از 1 است) را فیلتر می کنیم.
بیایید ستون های A، B و C را با استفاده از عملگر الحاق "&" ترکیب کنیم. بنابراین، فرمول اکسل به صورت زیر خواهد بود:
=A2&B2&C2
این فرمول در سلول D2 وارد شده و سپس در تمام ردیف ها کپی می شود.
اکنون، برای یافتن موارد تکراری در ستون D به ستون دیگری به نام " Count " نیاز داریم. بنابراین، ما از تابع COUNTIF در سلول E2 استفاده می کنیم. فرمول این خواهد بود:
=COUNTIF($D$2:D2,D2)
این فرمول به شمارش تعداد دفعات هر مقدار در ستون D کمک می کند.
اگر مقدار Count "1" باشد، فقط یک بار ظاهر شده است و منحصر به فرد است. اگر مقدار "2" یا بیشتر باشد، یک مقدار تکراری در نظر گرفته می شود.
حتما بخوانید: آموزش رسم نمودار در اکسل تصویری و گام به گام
- حالا با انتخاب گزینه Filter یک فیلتر به ستون Count اضافه کنید.
آن را در تب DATA → بخش Sort & Filter → Filter پیدا خواهید کرد
روی فیلتر بالای ستون E کلیک کنید. "1" را انتخاب کنید تا فقط مقادیر منحصر به فرد را حفظ کنید و موارد تکراری را حذف کنید.
- با کلیک بر روی OK، مقادیر تکراری از جدول حذف خواهند شد. می‌توانید این رکوردهای منحصربه‌فرد حاصل را کپی کرده و در جای دیگری جای‌گذاری کنید.
مشکلات حذف موارد تکراری از داده های خلاصه شده یا جمع آوری شده
یک مشکل رایج هنگام حذف موارد تکراری این است که نمی‌توانید مقادیر تکراری را از داده‌های کلی یا جمع‌بندی شده حذف کنید. برای حذف موارد تکراری، باید هم طرح کلی و هم جمع های فرعی را حذف کنید.
مقادیر منحصر به فرد یا تکراری را بصورت مشروط قالب بندی کنید
قالب بندی شرطی به پیدا کردن و برجسته کردن مناطق تکراری کمک می کند. با این حال، اکسل نمی تواند موارد تکراری را در ناحیه مقادیر گزارش PivotTable برجسته کند. برای سایر مناطق، مراحل زیر را دنبال کنید:
1. با انتخاب سلول هایی که می خواهید مقادیر تکراری را بررسی کنید، شروع کنید.
2. در مرحله بعد، باید گزینه Duplicate Values را تحت قالب بندی شرطی در تب Home پیدا کنید.
Home tab → Conditional Formatting → Highlight Cells Rules → Duplicate Values
3. پس از کلیک بر روی گزینه Duplicate Values یک کادر پاپ آپ ظاهر می شود.
4. اکنون، در کادر کنار گزینه “values with”، قالب بندی مقادیر تکراری را از منوی کشویی انتخاب کنید. سپس بر روی OK کلیک کنید.
جعبه بازشو مقادیر تکراری اکسل
مقادیر تکراری در برگه برجسته خواهند شد. قبل از استفاده از ویژگی حذف موارد تکراری، به یاد داشته باشید که تمام داده های تکراری را برای همیشه حذف می کند. بنابراین، عاقلانه‌تر است که داده‌های اصلی را در یک کاربرگ جداگانه کپی کنید تا از دست رفتن تصادفی اطلاعات جلوگیری شود.
اکنون برای حذف مقادیر تکراری مراحل زیر را انجام دهید:
1. محدوده سلول هایی با مقادیر تکراری را که می خواهید حذف کنید انتخاب کنید.
2. سپس گزینه "Remove Duplicates" را بیابید و آن را انتخاب کنید.
Data tab → Data Tools section → Remove Duplicates
3. در زیر ستون ها، ستون هایی را که می خواهید موارد تکراری را حذف کنید علامت بزنید یا علامت آن را بردارید. سپس، روی OK کلیک کنید.
گزینه Remove Duplicates
به عنوان مثال، ما می خواهیم ستون ژانویه را با اطلاعات قیمت در کاربرگ زیر نگه داریم.
کاربرگ نمونه
بنابراین، تیک گزینه ژانویه را در کادر Remove Duplicates بردارید.
گزینه ژانویه علامت زده نشد
تمام موارد تکراری دیگر به جز ستون های بدون علامت حذف خواهند شد.
حتما بخوانید: روش استفاده از تابع COUNT برای شمارش سلول های پر در اکسل
چگونه از ابزار Power Query برای حذف موارد تکراری در اکسل استفاده کنیم؟
Power Query در اکسل به شما امکان می دهد داده ها را از منابع مختلف وارد کنید، داده های خود را تمیز و تغییر دهید. این ابزار حذف موارد تکراری در اکسل را بدون دردسر می کند.
- یک سلول یا محدوده را انتخاب کنید، تب Data → بخش Get & Transform Data → From Table/Range را پیدا کرده و روی آن کلیک کنید.
- با کلیک کردن، یک کادر محاوره ای برای ایجاد جدول power query خواهید دید . مطمئن شوید که محدوده مقادیر به درستی مشخص شده است. روی OK کلیک کنید.
- پنجره ویرایشگر Power Query زیر ظاهر می شود.
حالا شما دو گزینه دارید. می توانید موارد تکراری را بر اساس موارد زیر حذف کنید:
- یک یا چند ستون
- کل جدول
برای حذف موارد تکراری بر اساس یک یا چند ستون، روی هدر ستون خاص کلیک راست کنید. شما می توانید بیش از یک ستون را با استفاده از دکمه CTRL انتخاب کنید و مطابق آن موارد تکراری را حذف کنید.
برای حذف رکوردهای تکراری بر اساس کل جدول، روی دکمه موجود در گوشه سمت چپ بالای پیش نمایش داده ها کلیک کنید. و سپس گزینه 'Remove Duplicates' را انتخاب کنید.
به این ترتیب داده ها از مقادیر تکراری پاک می شوند.
با کلیک بر روی گزینه "close&Load"، داده ها در صفحه گسترده شما بارگذاری می شوند.
سخن آخر
در این مقاله روش‌های مختلفی برای حذف رکوردهای تکراری یاد گرفتیم. چه به یادگیری اصول اولیه اکسل علاقه مند باشید، حذف موارد تکراری در اکسل یک کار بسیار خسته کننده است و امیدواریم این مقاله به شما کمک کرده باشد که چگونه به طور موثر موارد تکراری را حذف کنید.
مقالات مرتبط:
رفع مشکل کار نکردن و غیرفعال شدن نوار ابزار اکسل
رفع مشکل ذخیره نشدن فایل اکسل [Document not saved] در ویندوز 11