اگر نیاز به مرتب‌سازی فهرست دارید، راه‌های زیادی در خدمت شما وجود دارد که ساده‌ترین آنها دکمه‌های مرتب‌سازی در برگه یا منو است. داده ها (داده ها - مرتب سازی). با این حال، شرایطی وجود دارد که مرتب سازی لیست باید به طور خودکار انجام شود، یعنی فرمول ها. این ممکن است مورد نیاز باشد، برای مثال، هنگام تولید داده‌ها برای یک لیست کشویی، هنگام محاسبه داده‌ها برای نمودارها، و غیره. چگونه یک لیست را با یک فرمول مرتب کنیم؟
روش 1. داده های عددی
اگر لیست فقط حاوی اطلاعات عددی باشد، مرتب سازی آن را می توان به راحتی با استفاده از توابع انجام داد کمترین (کم اهمیت) и LINE (ROW):
عملکرد کمترین (کم اهمیت) از آرایه (ستون A) n-امین کوچکترین عنصر در یک ردیف را بیرون می کشد. آن ها SMALL(A:A;1) کوچکترین عدد در ستون، SMALL(A:A;2) دومین عدد کوچکتر است و غیره.
عملکرد LINE (ROW) شماره سطر را برای سلول مشخص شده برمی گرداند، یعنی ROW(A1)=1، ROW(A2)=2 و غیره. لیست مرتب شده ما با همین موفقیت، می‌توان یک ستون اضافی درست کرد، آن را به صورت دستی با دنباله عددی 1,2,3،1,2,3،XNUMX ... پر کرد و به جای تابع ROW به آن مراجعه کرد.
روش 2. لیست متن و فرمول های معمولی
اگر لیست حاوی اعداد نیست، بلکه متن باشد، تابع SMALL دیگر کار نخواهد کرد، بنابراین باید مسیر متفاوت و کمی طولانی‌تری را طی کنید.
ابتدا، بیایید یک ستون سرویس با فرمولی اضافه کنیم که در آن شماره سریال هر نام در لیست مرتب شده آینده با استفاده از تابع محاسبه می شود. COUNTIF (COUNTIF):
در نسخه انگلیسی این خواهد بود:
=COUNTIF(A:A،»<"&A1)+COUNTIF($A$1:A1,"="&A1)
عبارت اول تابعی برای شمارش تعداد سلول هایی است که کمتر از عدد فعلی هستند. دومی یک شبکه ایمنی است در صورتی که هر نامی بیش از یک بار تکرار شود. سپس آنها یکسان نخواهند داشت، اما تعداد آنها به طور متوالی در حال افزایش است.
حال اعداد دریافتی باید به ترتیب صعودی مرتب شوند. برای این کار می توانید از تابع استفاده کنید کمترین (کم اهمیت) از راه اول:
خوب، در نهایت، فقط باید اسامی را با شماره آنها از لیست خارج کنیم. برای این کار می توانید از فرمول زیر استفاده کنید:
عملکرد بیشتر در معرض (همخوانی داشتن) در ستون B شماره سریال مورد نظر (1، 2، 3 و ...) را جستجو می کند و در واقع شماره خطی که این شماره در آن قرار دارد را برمی گرداند. عملکرد شاخص (فهرست مطالب) نام شماره این خط را از ستون A بیرون می آورد.
روش 3: فرمول آرایه
این روش در واقع همان الگوریتم مکان‌یابی است که در روش 2 وجود دارد، اما با فرمول آرایه پیاده‌سازی می‌شود. برای ساده‌سازی فرمول، محدوده سلول‌های C1:C10 نامگذاری شد فهرست (سلول ها را انتخاب کنید، فشار دهید Ctrl + F3 و دکمه ساختن):
در سلول E1 فرمول ما را کپی کنید:
=INDEX(List; MATCH(SMALL(COUNTIF(List; "<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0))
یا در نسخه انگلیسی:
=INDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0))
و فشار Ctrl + Shift + Enterتا آن را به عنوان فرمول آرایه وارد کنید. سپس فرمول به دست آمده را می توان در تمام طول لیست کپی کرد.
اگر می خواهید فرمول یک محدوده ثابت را در نظر نگیرد، اما بتوانید هنگام افزودن عناصر جدید به لیست، آن را تنظیم کنید، باید استراتژی را کمی تغییر دهید.
ابتدا، محدوده List باید به صورت پویا تنظیم شود. برای انجام این کار، هنگام ایجاد، شما باید نه یک محدوده ثابت C3:C10، بلکه یک فرمول خاص را مشخص کنید که به تمام مقادیر موجود، صرف نظر از تعداد آنها اشاره می کند. کلیک Alt + F3 یا برگه را باز کنید فرمول ها - مدیر نام (فرمول ها - مدیر نام)، یک نام جدید ایجاد کنید و در فیلد ارتباط دادن (ارجاع) فرمول زیر را وارد کنید (من فرض می کنم محدوده داده هایی که باید مرتب شوند از سلول C1 شروع می شود):
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)
ثانیاً، فرمول آرایه بالا باید با یک حاشیه به سمت پایین کشیده شود - با انتظار ورود داده های اضافی در آینده. در این حالت، فرمول آرایه شروع به دادن خطای #NUMBER در سلول هایی می کند که هنوز پر نشده اند. برای رهگیری آن، می توانید از تابع استفاده کنید IFERROR، که باید "در اطراف" فرمول آرایه ما اضافه شود:
=IFERROR(INDEX(List; MATCH(SMALL(COUNTIF(List; "<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0));»»)
=IFERROR(NDEX(List, MATCH(SMALL(COUNTIF(List, «<"&List), ROW(1:1)), COUNTIF(List, "<"&List), 0));"")
خطای #NUMBER را دریافت می کند و به جای آن یک void (نقل قول خالی) تولید می کند.
:
- مرتب سازی محدوده بر اساس رنگ
- فرمول های آرایه چیست و چرا به آنها نیاز است
- مرتب‌سازی و آرایه‌های پویا در Office 365 جدید