سورت کردن داده با فرمول
سورت کردن داده ها با استفاده از فرمول نویسی
سورت کردن داده ها یکی از مهم ترین و پرکاربرد ترین مسائل در اکسل است که روش ها و حالت های متنوعی هم داره. این کار رو هم با استفاده از ابزار و هم با استفاده از فرمول نویسی میتونیم انجام بدیم. استفاده از ابزار برای مرتب کردن داده ها رو قبلا آموزش دادیم. حتما مقاله راجع به این موضوع رو مطالعه کنید. در این مقاله میخواهیم به مرتب کردن داده ها با استفاده از فرمول نویسی بپردازیم.
سورت کردن داده ها در ورژن ۲۰۲۱
همونطور که قبلا راجع بهش صحبت کردیم و در مقاله معرفی توابع جدید ۲۰۲۱ توضیح دادیم، تابع Sort از دسته توابع Dynamic Array Function به اکسل اضافه شده است. این تابع میتونه داده های یک جدول رو بر اساس معیاری که تعیین میکنیم ،مرتب کنه.
جدولی داریم مطابق شکل ۱ که میخواهیم داده ها رو بر اساس ستون مبلغ مرتب کنیم. برای این کار میخوایم از تابع Sort که از ورژن ۲۰۲۱ در دسترس است استفاده کنیم.
شکل ۱- داده های موجود در دیتابیس
برای استفاده از تابع Sort ابتدا آرگومان هاش رو یاد میگیریم:
Array: داده هایی که میخواهیم مرتب بشن. در این مثال، محدوده A1:C10
[Sort_Index]: شماره ستونی که میخواهیم مرتب بشه. در اینجا ستون مبلغ سومین ستون از محدوده مورد نظر است. پس عدد ۳ رو در این آرگومان مشخص میکنیم.
[Sort_Order]: در این آرگومان جهت مرتب سازی (صعوی ۱ و نزولی -۱) رو مشخص میکنیم.
[By_Col]: در این آرگومان مشخص میکنیم که مرتب سازی افقی انجام بشه یا عمودی. اگر این آرگومان رو تعیین نکنیم بصورت پیشفرض عمودی مرتب می شود.
پس فرمولی که برای این کار می نویسیم به شکل زیر خواهد بود:
=SORT(A2:C10,3,-1)
این تابع داده های محدوده A2:c10 رو بر اساس ستون سوم (مبلغ) از بزرگ به کوچک مرتب میکنه. دقیقا منطق عملکرد این تابع مثل ابزار Sort است و با این ویژگی مهم که با تغییر داده های مبدا، نتیجه بلافاصله آپدیت می شه (در ابزار هر بار باید مراحل مرتب کردن انجام بشه)
آرگومان دوم و سوم با توجه به اینکه از جنس عدد هستند، خیلی خوب میتونن با سایر توابع ترکیب بشن. مثلا برای اینکه مرتب کردن صعودی و نزولی رو بصورت داینامیک داشته باشیم میتونیم از if استفاده کنیم. به اینصورت که اگر در یک سلول نوشته شد “صعودی” عدد ۱ و اگر نوشته شد “نزولی”، عدد ۱- تعیین بشه.
پس تابع if زیر رو در آرگومان سوم تابع Sort قرار میدیم:
=IF(I1=”نزولی”,۱-,۱)
به این شکل:
SORT(A2:C10, 3, IF(I1=”نزولی”,۱-,۱))
شکل ۲- داینامیک سازی جهت مرتب کردن داده ها
به همین ترتیب آرگومان دوم یعنی Sort Index رو هم میتونیم با تابعی مثل Match ترکیب کنیم که هر بار با توجه به فیلد انتخابی، داده ها سورت بشه. برای آشنایی بیشتر پیشنهاد میکنم مقاله مربوط به Match, Index رو مطالعه کنید.
آرگومان آخر این تابع میتونه امکانی مشابه Sort Left to right در ابزار Sort بهمون بده. یعنی مثلا اگر خواستیم جدولی از داده ها رو بصورت افقی و بر اساس یک سطر مرتب کنیم از این تابع استفاده میکنیم.
سورت کردن داده ها در سطوح مختلف
علاوه بر تابع Sort در اکسل ۲۰۲۱ تابعی نیز داریم برای مرتب کردن داده ها در سطوح مختلف به نام SortBy. مثلا فرض کنید داده های مثال اول رو میخواهیم ابتدا بر اساس واحد و سپس بر اساس مبلغ مرتب کنیم. برای این کار باید از تابع Sortby استفاده کنیم. در ادامه آرگومان های این تابع رو معرفی میکنیم:
Array: محدوده داده ها که باید مرتب بشه.
By_Array1: سطح اول (ستون مورد نظر برای مرتب کردن). در این مثال ستون واحد.
[Sort_Order1]: جهت مرتب کردن داده ها (صعودی یا نزولی).
[By_Order2]: سطح دوم (ستون دوم مورد نظر برای مرتب سازی). در این مثال ستون مبلغ.
[Sort_Order2]: جهت مرتب کردن داده ها (صعودی یا نزولی).
فرمول زیر رو در سلول J1 می نویسیم (شکل ۳):
=SORTBY(A2:C10,B2:B10,1,C2:C10,-1)
شکل ۳- سورت کردن داده ها در سطوح مختلف
همونطور که در شکل ۳ مشاهده میکنید، داده های محدوده انتخابی، ابتدا بر اساس ستون واحد (بصورت صعودی) و سپس بر اساس مبلغ (نزولی) مرتب شده اند. بدیهی است که در صورتی سطوح بعدی مرتب سازی معنی دار و قابل اجرا خواهند بود که در سطح قبلی داده تکراری وجود داشته باشه. مثلا در ستون واحد کلمه جنوب چند بار تکرار بشه تا امکان داده ها در سطح بعدی نیز وجود داشته باشه.
مرتب کردن داده ها در ورژن های قبل از ۲۰۲۱
همین مسئله رو میخوایم بدون تابع Sort و با استفاده از ترکیب توابع موجود در ورژن های قبل از ۲۰۲۱ حل کنیم.
برای اینکه بتونیم اعداد موجود در یک محدوده رو از کوچک به بزرگ یا بزرگ به کوچک مرتب کنیم میتونیم از دو تابع Small و Large استفاده کنیم. این دو تابع رو قبلا در مقاله مرتب کردن داده ها با فرمول شرح دادیم.
در این مثال نحوه برخورد با داده های تکراری به اینصورت است که اول داده ها رو با یک عدد خیلی خیلی کوچک منحصربفرد جمع میزنیم که اگر هم داده تکراری وجود داره، غیرتکراری بشه. مثلا دو عدد ۴۵۰ داریم، اگر یکیش با ۰.۰۰۰۰۰۱ و یکیش با ۰.۰۰۰۰۰۰۲ جمع بشه ،هم به لحاظ مقداری دیگه مشابه نیستن و هم روی ارزش عدد اثری نخواهد داشت. پس ابتدا داده های ستون مبلغ رو منحصر بفرد میکنیم. داده ستون مبلغ رو با عدد ۰.۰۰۰۰۰۰۱*Row(A1) جمع میکنیم. باعث میشه که هر بار یک عدد خیلی خیلی کوچک و غیرتکراری با داده ها جمع بشه. (شکل ۴)
شکل ۴- منحصر بفرد کردن داده ها
حالا محاسبات مربوط به مرتب کردن رو روی ستون D که داده های منحصر بفرد هستن انجام میدیم.
پس ابتدا ستون مبلغ رو با استفاده از تابع Small از کوچک به بزرگ مرتب میکنیم. مشابه شکل زیر:
=SMALL($D$2:$D$10,ROW(A1))
شکل ۵- مرتب کردن داده ها با استفاده از تابع Small
حالا کافیه فرمت ستون F رو تنظیم کنیم و نمایش اعشار رو صفر کنیم که اون اعداد کوچک اضافه شده نمایش داده نشن. برای این کار داده ها رو انتخاب میکنیم و از قسمت فرمت سل اعشار رو کاهش میدیم.
شکل ۶- کاهش اعشار دده ها از طریق فرمت سل
حالا کافیه که اعداد مرتب شده رو با استفاده از ترکیب Match/Index پیدا کنیم و داده های متناظر آنها رو در کنار داده های مرتب شده نمایش بدیم. برای درک این فرمول، مقاله مربوط به Index رو مطالعه کنید.
ابتدا با استفاده از Match مکان هر یک از اعداد ستون مبلغ رو پیدا میکنیم:
=MATCH($F2,$D$2:$D$10,0)
شکل۷- پیدا کردن مکان داده های ستون مبلغ
حالا کافیه این اعداد در تابع Index قرار بگیرن و داده متناظر در ستون A یعنی کد طرح رو پیدا کنند.
=INDEX($A$2:$A$10,MATCH($F2,$D$2:$D$10,0))
شکل ۸- مرتب کردن داده ها- جستجوی داده های مرتب شده
همین کار رو برای ستون واحد هم انجام میدیم.
یا اینکه از اول فرمول رو داینامیک و به شکل زیر در سلول G2 می نویسیم و درگ میکنیم:
=INDEX($A$2:$C$10,MATCH($F2,$D$2:$D$10,0),COLUMN(A1))
شکل ۹- جستجوی داده های مرتب شده
در این مقاله سعی کردیم مرتب کردن داده ها با استفاده از فرمول نویسی رو در ورژن های مختلف اکسل بررسی کنیم. همونطور که قبلا هم اشاره شد، این کار رو طبیعتا با استفاده از ابزار Sort هم میتونیم انجام بدیم. که در نهایت با توجه به شرایط داده ها، ورژن در دسترس و خواسته خودمون ،روش مرتب کردن رو انتخاب میکنیم.
ویدیوی مرتب کردن داده های جدول با فرمول
دانلود فایل اکسل این آموزش
میتونید برای تمرین بیشتر فایل اکسل زیر رو دانلود کنید.
