اگر آنها را به روشی که قبلاً می کردم ، ایجاد لیست های برتر در اکسل می تواند خسته کننده باشد. من داده ها را به صورت دستی مرتب می کنم ، بالاترین مقادیر را کپی می کنم و آنها را در یک بخش جداگانه قرار می دهم. خوب کار کرد ، اما هر بار که داده های اساسی تغییر می کردند ، باید کل مراحل را تکرار کنم. اگر به روزرسانی را از دست بدهم ، گزارش من اطلاعات منسوخ شده را نشان می دهد ، که هنگام ارائه به ذینفعان حرفه ای نیست.
اگر آنها را به روشی که قبلاً می کردم ، ایجاد لیست های برتر در اکسل می تواند خسته کننده باشد. من داده ها را به صورت دستی مرتب می کنم ، بالاترین مقادیر را کپی می کنم و آنها را در یک بخش جداگانه قرار می دهم. خوب کار کرد ، اما هر بار که داده های اساسی تغییر می کردند ، باید کل مراحل را تکرار کنم. اگر به روزرسانی را از دست بدهم ، گزارش من اطلاعات منسوخ شده را نشان می دهد ، که هنگام ارائه به ذینفعان حرفه ای نیست.
این جایی است که توابع Take and Drop Excel به متوقف کردن هدر رفتن زمان در اکسل کمک می کند. این توابع به طور خودکار موارد بالا یا پایین را از هر مجموعه داده می کشند و هنگام تغییر داده های منبع ، خود را به روز می کنند. بنابراین ، هیچ کپی دستی بیشتر گزارش ها یا گزارش های طراوت وجود ندارد. در حالی که این مفهوم چشمگیر نیست ، اما دردسر لیست های به روزرسانی دستی را از بین می برد.
اول ، بیایید آرایه های پویا را درک کنیم
قبل از آرایه های پویا ، نتیجه فرمول محدود به یک سلول واحد بود. برای جمع کردن یک ستون ، مجبور بودید دسته پر را به سمت پایین بکشید. آرایه های پویا متفاوت هستند – یک فرمول واحد اکنون می تواند طیف کاملی از نتایج را برگرداند که به طور خودکار سلول های مختلف را پر می کند. این فرآیند اتوماتیک “ریختن” نامیده می شود. هنگامی که یک فرمول نتایج متعددی را به دست می آورد ، آنها به سلولهای خالی همسایه می ریزند. شما می دانید که این یک آرایه پویا است زیرا اکسل یک مرز آبی نازک را در کل محدوده خروجی ترسیم می کند. این نشانه بصری نشانه شماست که داده ها به صورت زنده و به فرمول منبع وصل می شوند.
شما فقط باید فرمول را در اولین سلول آن محدوده با مرز آبی مدیریت کنید. اگر سعی می کنید چیزی را در سلول دیگری در منطقه نشت تایپ کنید ، #Spill دریافت خواهید کرد! خطا کل آرایه توسط یک فرمول اولیه کنترل می شود ، که باعث می شود ورق ها تمیزتر و کمتر مستعد خطا باشند.
همچنین در مورد توابع Take and Drop صدق می کند. از آنجا که نتایج هنگامی که داده های منبع شما تغییر می کند ، به طور خودکار تنظیم می شود ، آرایه های پویا نیاز به تغییر اندازه جداول و به روزرسانی دستی را از بین می برند.
این ویژگی در Microsoft 365 و Excel 2021 یا Newer موجود است. اگر از نسخه قدیمی استفاده می کنید ، این فرمول ها کار نمی کنند ، بنابراین برای استفاده از این توابع باید ارتقا دهید.
در اینجا نحوه عملکرد Take به شما در گرفتن موارد برتر کمک می کند
عملکرد Take برای یک کار ساده طراحی شده است – از ابتدا یا پایان یک مجموعه داده ، تعداد مشخصی از ردیف ها را استخراج می کند. شما فقط به اکسل می گویید که به جای کپی کردن دستی و چسباندن ، چند ردیف را می خواهید. آنها را برای شما می گیرد. این برای ایجاد خلاصه یا تابلوهای اصلی از یک جدول بزرگتر کارآمد است.
استفاده از نحو زیر:
= گرفتن (آرایه ، ردیف ، [ستون])
در اینجا به معنای پارامترها چیست:
- آرایه: این دامنه منبع یا آرایه ای از داده هایی است که می خواهید از آن استفاده کنید.
- ردیف ها: تعداد ردیف هایی که می خواهید استخراج کنید. یک عدد مثبت (مانند 5) از بالا می گیرد ، در حالی که یک عدد منفی (مانند -5) از پایین می گیرد.
- [ستون]: این یک آرگومان اختیاری است. می توانید تعداد ستون ها را برای بازگشت از سمت چپ (شماره مثبت) یا راست (شماره منفی) مشخص کنید. اگر آن را حذف کردید ، تمام ستون ها را برگردانید.
بیایید این را در یک مثال در دنیای واقعی اعمال کنیم. با استفاده از داده های فروش ، می خواهم یک تابلوی پویا ایجاد کنم که مبلغ 5 فروش برتر را نشان می دهد.
نکته اصلی برای به دست آوردن مجریان برتر ، ابتدا مرتب سازی داده ها از بالاترین تا کمترین فروش است. برای این کار ، من از عملکرد مرتب سازی استفاده می کنم ، که یکی از فرمول های اکسل است که هر کارگر اداری باید بداند.
فرمول زیر کل جدول من را بر اساس ستون هفتم (مبلغ فروش) به ترتیب نزولی (-1) ترتیب می دهد.
= مرتب سازی (A2: K33 ، 7 ، -1)
با مرتب سازی داده ها ، اکنون می توانم از Take برای گرفتن فقط پنج مدخل برتر استفاده کنم. من به سادگی عملکرد مرتب سازی خود را در داخل عملکرد Take مانند این بسته بندی می کنم:
= گرفتن (مرتب سازی (A2: K33 ، 7 ، -1) ، 5)
این فرمول واحد ابتدا کل لیست را مرتب می کند و سپس 5 ردیف برتر را استخراج می کند و یک تابلوی خود را برای خود ایجاد می کند. اگر من هر مبلغ فروش را در جدول اصلی تغییر دهم ، این لیست فوراً رتبه بندی جدید را منعکس می کند بدون اینکه مجبور شوم کار دیگری انجام دهم.
عملکرد قطره به همان اندازه برای نادیده گرفتن داده هایی که لازم ندارید مفید است
عملکرد قطره به عنوان همتای برای گرفتن کار می کند. به جای گرفتن ردیف های خاص ، آنها را از مجموعه داده شما حذف می کند.
هنگامی که شما با داده های کثیف که شامل هدرها ، کل ها یا ورودی های بی ربط است که باید از آن استفاده کنید ، مفید است. به جای انتخاب دستی در اطراف ردیف های ناخواسته ، به اکسل می گویید که چند نفر از بالا یا پایین نادیده گرفته می شوند.
آینه های نحوی اما با منطق مخالف می گیرند:
= قطره (آرایه ، ردیف ، [ستون])
در اینجا به معنای پارامترها چیست:
- آرایه: دامنه منبع یا آرایه ای که می خواهید با حذف داده ها اصلاح کنید.
- ردیف ها: تعداد ردیف ها برای حذف. اعداد مثبت از بالا کاهش می یابد ، اعداد منفی از پایین کاهش می یابد.
- [ستون ها]: پارامتر اختیاری برای رها کردن ستون ها از سمت چپ (مثبت) یا راست (منفی). در صورت حذف ، همه ستون ها باقی می مانند.
با استفاده از داده های فروش دوباره ، بیایید بگوییم که می خواهم همه چیز را به جز سه مجری پایین تجزیه و تحلیل کنم. از این رو ، من می توانم از Drop برای حذف بدترین مجریان استفاده کنم.
ابتدا داده ها را برای شناسایی مجریان پایین مرتب می کنم:
= مرتب سازی (A2: K33 ، 7 ، 1)
توجه کنید که من به جای (1) به جای (-1) برای مرتب کردن از کمترین تا بالاترین مبلغ فروش استفاده می کنم. حالا سه ردیف پایین را رها می کنم:
= قطره (مرتب سازی (A2: K33 ، 7 ، 1) ، -3)
تعداد منفی به اکسل می گوید که سه ردیف را از انتهای لیست مرتب شده حذف کند.
این فرمول یک مجموعه داده تمیز به من می دهد که به جز کمترین مجریان روی همه تمرکز می کند ، که برای تجزیه و تحلیل تیم فروش اصلی شما مفید است بدون اینکه دور از تجزیه و تحلیل شما استفاده کند.
من آنها را برای ایجاد گزارش های پیشرفته تر ترکیب می کنم
با لانه سازی و کنار گذاشتن با هم ، می توانید یک قطعه خاص از داده ها را از وسط لیست رتبه بندی شده خود جدا کنید. این برای ایجاد گزارش های مرتب ، مانند شناسایی مجریان سطح متوسط یا محصولاتی که نه فروشنده های بالا و نه ساکنان پایین هستند ، مفید است.
بیایید دوباره از همان داده های فروش استفاده کنیم. ما قبلاً پنج نفر برتر و بقیه لیست را پیدا کرده ایم. اما اگر فقط بخواهیم کارمندانی را که از 6 تا 10 رتبه بندی شده اند ببینیم؟ برای دستیابی به این هدف ، ما از فرمولی استفاده می کنیم که به ترتیب خاص از داخل به بیرون اجرا می شود:
= گرفتن (قطره (مرتب سازی (A2: K33 ، 7 ، -1) ، 5) ، 5)
در اینجا نحوه پردازش اکسل درخواست می کند. ابتدا عملکرد مرتب سازی کل جدول را با مبلغ فروش به ترتیب نزولی ترتیب می دهد. سپس ، عملکرد قطره آن لیست مرتب شده را می گیرد و پنج ردیف برتر را حذف می کند. پس از آن ، عملکرد Take پنج ردیف اول را از داده های باقیمانده بدست می آورد ، که دقیقاً به مجریان ششم تا 10 می دهد.
شما همچنین می توانید از این امر برای یافتن فروش زیر عملکردی استفاده کنید بدون اینکه بدترین نتیجه را شامل شود. به عنوان مثال ، برای دیدن مجریان دوم به پنجم ، می توانید بدترین مجری را از پایین (-1) رها کنید و سپس چهار مورد بعدی را از آن لیست باقی مانده بگیرید. فرمول فقط یک تغییر جزئی است:
= قطره (نوع (مرتب سازی (A2: K33 ، 7 ، 1) ، 5) ، 1)
در اینجا ، داده ها به ترتیب صعودی (1) طبقه بندی می شوند ، پنج ردیف اول گرفته می شوند (5) ، و سپس ردیف بالاترین سطح (1) کاهش می یابد.
برای یک گزارش پیشرفته تر ، من این توابع را با فیلتر ترکیب می کنم تا یک صفحه رهبری برای یک منطقه خاص ایجاد کنم. برای یافتن سه مجری برتر در منطقه شمالی ، ابتدا داده ها را فیلتر می کنم ، سپس آن را مرتب می کنم و سپس سه نفر برتر را می گیرم.
= گرفتن (مرتب سازی (فیلتر (A2: K33 ، B2: B33 = “North”) ، 7 ، -1) ، 3)
این فرمول یک تابلوی کاملاً پویا و خاص منطقه ایجاد می کند. اگر داده های فروش تغییر کند یا اگر یک فروشنده به منطقه دیگری منتقل شود ، لیست خود را به روز می کند.
من هرگز به روش های قدیمی برنمی گردم
روشهای زیادی برای رتبه بندی داده ها در اکسل وجود دارد ، اما بیشتر آنها یا برای تجزیه و تحلیل سنگین طراحی شده اند یا مراحل دستی را شامل می شوند که فقط احساس ناراحتی می کنند. اگر تمام آنچه من نیاز دارم یک رهبر تمیز و خودپسندانه است ، لازم نیست با جداول محوری یا فرمول های میراث سخت خوانده شده مقابله کنم. ترکیب Take and Drop مسیری بسیار مستقیم تر به نتیجه می دهد.
این روش یک راه حل بدون سر و صدا است که بدون نیاز به تازه سازی کار می کند و از فرمول هایی استفاده می کند که درک آن برای هر کسی آسان است. آنها سریع و ساده برای ایجاد گزارش های رتبه بندی شده ای که با داده های من در جریان هستند ، سریع و ساده هستند.