بسیاری از فرمولهای اکسل که برای لیستها استفاده میکنم، به نظارت نیاز دارند. گاهی وقتی یک ردیف جدید اضافه میکنم، چیزی خراب میشود یا نیاز به تنظیم دارد. تابع TAKE و تابع DROP این مشکل را ندارند. این دو تابع آرایه پویا که جدولها را بهصورت خودکار گسترش میدهند، به شما امکان میدهند ردیفها و ستونهای خاصی را از یک مجموعه داده استخراج یا حذف کنید و خود بهصورت خودکار با تغییر دادههای شما بهروز میشوند.
بسیاری از فرمولهای اکسل که من برای فهرستها استفاده میکنم نیاز به نظارت دارند. گاهی اوقات، وقتی یک ردیف جدید اضافه میکنم، چیزی خراب میشود یا نیاز به تنظیم دارد. TAKE و DROP این مشکل را ندارند. این دو تابع آرایهپویا که جداول را بهصورت خودکار گسترش میدهند، به شما اجازه میدهند ردیفها و ستونهای خاصی را از یک مجموعه داده استخراج یا حذف کنید و بهطور خودکار با تغییر دادههای شما بهروز میشوند.
آنها را با توابعی مانند SORT و FILTER ترکیب کنید و فهرستهایی دریافت میکنید که بدون هیچگونه تلاش اضافهای خود را نگهداری میکنند. در اینجا نحوه استفاده من از آنها برای ساخت فهرستهایی که واقعاً بهروز میمانند، آمده است.
دستگاههای گسترده را بهصورت دستی مرتب نکنید وقتی FILTER داخلی اکسل کار سنگین را انجام میدهد.
TAKE دقیقاً ردیفها یا ستونهای مورد نیاز شما را میگیرد
دادههای خود را به آن نشان دهید، تعداد ردیفها را بگویید، و نتایج را دریافت کنید
TAKE یک کار را بهخوبی انجام میدهد. تعداد معینی از ردیفها یا ستونها را از یک محدوده استخراج میکند، شروع از هر سروی که مشخص کنید. به ستونهای کمکی یا تو در توی پیچیدهٔ INDEX-MATCH نیاز ندارید. دادههای خود را به آن نشان میدهید، تعداد ردیفهای مورد نظر را میگویید، و نتایج را دریافت میکنید.
در زیر نحوهٔ نوشتار آمده است:
=TAKE(array, rows, [columns])
- array: بازه یا آرایهٔ منبعی که استخراج میکنید.
- rows: تعداد ردیفهایی که باید برگردانده شود. عدد مثبت از بالا استخراج میشود؛ عدد منفی از پایین.
- columns (optional): به همان شکل عمل میکند — عدد مثبت از سمت چپ، منفی از سمت راست.
فرض کنید دادههای فروش در A2:D20 دارید و میخواهید اولین پنج ردیف را بگیرید. از فرمول زیر استفاده میکنید:
=TAKE(A2:D20, 5)
به جای آن به سه ردیف آخر نیاز دارید؟ از -3 استفاده کنید. نتیجه بهصورت خودکار بهسلولهای مجاور ریخته میشود و هر زمانی که دادههای منبع تغییر کنند، دوباره محاسبه میشود.
DROP ردیفها یا ستونهایی که نمیخواهید را حذف میکند
به آن بگویید چه چیزی را حذف کند، و بقیه را برمیگرداند
DROP معکوس TAKE است. بهجای مشخص کردن اینکه چه چیزی میخواهید نگه دارید، به آن میگویید چه چیزی را حذف کند و بقیه را برمیگرداند. من این را در موقعیتهایی که دقیقاً میدانم چند ردیف نامربوط در بالای یا پایین مجموعه داده وجود دارد، شهودی میدانم.
نوشتار مشابه TAKE است:
=DROP(array, rows, [columns])
- array: بازه یا آرایهٔ منبع.
- rows: تعداد ردیفهایی که باید حذف شوند. عدد مثبت از بالا حذف میشود؛ عدد منفی از پایین.
- columns (optional): عدد مثبت از سمت چپ حذف میشود، منفی از سمت راست.
فرض کنید مجموعه دادهٔ شما در A2:D20 دو ردیف خلاصهگونه شبیهبهسرصفحه دارد که نیازی به آنها ندارید. فرمول زیر آنها را حذف کرده و بقیه را برمیگرداند.
=DROP(A2:D20, 2)
همچنین میتوانید ستونها را با استفاده از این عمل حذف کنید:
=DROP(A2:D20, 0, 1)
این اولین ستون را حذف میکند، که زمانی مفید است که فیلد ID خروجی شما را شلوغ میکند. مانند TAKE، نتیجه بهصورت پویا ریخته میشود و با رشد دادههای شما بهروز میشود.
ترکیب TAKE و DROP برای برش دقیق دادهها
آشکارسازی آنها با هم برای استخراج هر بخشی از میانه
بهتنهایی، TAKE و DROP از لبههای یک مجموعه داده استخراج میکنند. اما اگر آنها را با هم ترکیب کنید، میتوانید هر بخشی از میانه را استخراج کنید، چیزی که هیچکدام به تنهایی قادر به انجام آن نیستند.
ایده این است که ابتدا با DROP ردیفهایی که در بالا نیاز ندارید حذف کنید، سپس نتیجه را با TAKE محدود کنید که چند ردیف نگه داشته شوند. بهعنوان مثال با دادههای فروش، فرض کنید میخواهید چهار ورودی اول را نادیده بگیرید و هشت ردیف بعدی را بگیرید، که شامل تراکنشهای 12 ژانویه تا 3 فوریه است. فرمول به این شکل خواهد بود:
=TAKE(DROP(A2:D20, 4), 8)
DROP اولین چهار ردیف (ورودیهای 5 ژانویه) را حذف میکند و TAKE هشت ردیف بعدی از بقیه را برمیگرداند. خروجی شامل تمام چهار ستون تاریخ تا فروشنده است، بنابراین یک برش کامل دریافت میکنید بدون نیاز به ستونهای کمکی یا انتخاب دستی محدوده.
این بهویژه وقتی مفید است که مجموعه دادهٔ شما به مرور زمان رشد کند، زیرا هر دو تابع بهصورت پویا با اضافه شدن ردیفهای جدید تنظیم میشوند.
ساخت فهرست « top N » بهصورت خودبهروزرسانی با TAKE و SORT
فهرست بهصورت خودکار هر زمان که دادههای شما تغییر کنند، بهروز میشود
اینجاست که TAKE بهدست آوردن ارزش خود را نشان میدهد. ترکیب آن با SORT به شما امکان میدهد فهرستی بسازید که همیشه بالاترین (یا پایینترین) مقادیر را در مجموعهٔ دادهتان نمایش میدهد — و هر زمان دادهها تغییر کنند، خود را بهروزرسانی میکند.
دوباره با استفاده از دادههای فروش، فرض کنید میخواهید فهرستی از پنج تراکنش با بالاترین مبلغ فروش داشته باشید. فرمول به این صورت است:
=TAKE(SORT(A2:J20, 7, -1), 5)
SORT تمام 19 ردیف را بر اساس ستون 7 (مبلغ فروش) بهصورت نزولی ترتیب میدهد و TAKE پنج بالا را از آن نتیجه مرتب استخراج میکند. شما تمام ردیف هر ورودی را دریافت میکنید، تاریخ، منطقه، فروشنده و همه چیز، بنابراین زمینه حفظ میشود. اگر با تابع SORT آشنایی ندارید، راهنمای ما درباره مرتبسازی دادهها در اکسل مبانی را پوشش میدهد.
گزارش MUO: مشترک شوید و هرگز مهم را از دست ندهید
بهترین بخش این است که وقتی دادههای فروش جدید اضافه میشود چه اتفاق میافتد. وقتی یک ردیف با مبلغ فروش بالاتر اضافه میکنید، بهصورت خودکار به پنچ بالا میپیوندد. اگر محدودهٔ منبع خود را به یک جدول اکسل تبدیل کنید، محدوده بهطور خودکار گسترش مییابد و تمام تنظیمات کاملاً بدون دخالت دستی میمانند.
ایجاد فهرست پویا « آخرین ورودیها » با DROP و COUNTA
همیشه آخرین ردیفها را نمایش میدهد
« فهرست top N » مفید است، اما گاهی فقط میخواهید جدیدترین افزودنیهای یک مجموعهداده را ببینید. ترکیب DROP با COUNTA این کار را بهخوبی انجام میدهد — تعداد ردیفهای موجود را میشمارد و همه را بهجز چند ردیف آخر حذف میکند.
با استفاده از دادههای فروش، اینجا نحوهٔ نمایش دائمی آخرین پنج ورودی آمده است:
=DROP(A2:A20, COUNTA(A2:A20) - 5)
COUNTA تمام سلولهای پر در ستون A را میشمارد — که در این مثال 19 عدد است. با کم کردن 5، مقدار 14 به DROP میرسد، بنابراین اولین 14 ردیف حذف میشوند و پنج باقیمانده برگردانده میشوند. اینها در حال حاضر تراکنشهای مارس از فروشندگانی مانند مایک ویلسن تا تام رودریگز هستند.
اگر ردیف جدیدی برای آوریل اضافه کنید، COUNTA به 20 میرسد، DROP به 15 تنظیم میشود و خروجی بهروز شده و پنج ورودی اخیر را نشان میدهد. میتوانید از فرمول زیر برای همان نتیجه استفاده کنید، اما ترکیب DROP و COUNTA انعطافپذیرتر است وقتی محدوده ثابت نیست.
=TAKE(A2:A20, -5)
TAKE و DROP با توابع دیگر حتی بهتر کار میکنند
در مرحلهٔ بعد این ترکیبها را امتحان کنید
شما TAKE را با SORT و DROP را با COUNTA دیدهاید، اما موارد بیشتری برای کاوش وجود دارد. FILTER را داخل TAKE بپیچید تا فهرستهایی بسازید که فقط فروشهای الکترونیک بالای ۳٬۰۰۰ دلار را نشان میدهد — مرتب شده و محدود به تعداد مشخص. یا DROP را با UNIQUE ترکیب کنید تا سرصفحههای تکراری را هنگام ترکیب دادهها از چند برگه حذف کنید. CHOOSECOLS همراه با TAKE میتواند مجموعههای دادهٔ پهن را به فقط ستونهای مورد نیاز محدود کند. وقتی شروع به تو در تو کردن این توابع میکنید، ترکیبها سریعاً عملی میشوند.