مرتب کردن با بیش از سه ستون در اکسل

ویژگی مرتب (sort) کردن در برنامه Excel این محدودیت را دارد که اجازه مشخص کردن بیش از سه داده برای مرتب کردن (سورت کردن) را نمی دهد. در بیشتر موارد همین سه داده کافیست اما شرایطی پیش می آید که امکان مرتب کردن با بیش از سه داده بسیار مفید خواهد بود. در این قسمت راهی برای دور زدن این محدودیت به شما نشان داده می شود.
برای این مثال، فرض می کنیم داده های مرتبطی در ستون های A، B، C، D و E داریم و می خواهیم آن ها را ابتدا بر طبق ستون A، سپس B، سپس C، سپس D و پس از آن E مرتب کنیم. برای انجام این کار، لازم است امکان سورت کردن وارونه (Backward) را داشته باشیم. به عبارت دیگر، ابتدا بر طبق آخرین مورد سورت کرده و سپس به ترتیب تا اولین داده سورت می کنیم.
ستون های A تا E را انتخاب کرده و سپس مسیرData → Sortرا دنبال کنید. سورت کردن را با این ترتیب مشخص کنید که ابتدا ستون C، پس از آن D و سپس E مرتب شوند. روی sort کلیک کنید. اکنون ستون های A تا E را انتخاب کرده و مسیر Data → Sort را دنبال کنید. این بار ابتدا ستون A و سپس ستون B را مرتب کنید. روی Sort کلیک کرده و پس از آن همه چیز مرتب می شود. با این کار برنامه Excel به جای سه مورد داده، مرتب کردن را با پنج مورد داده انجام می دهد.

اگر می خواهید این کار به صورت خودکار انجام شود، می توانید از یک ماکرو استفاده کنید که هم داده های انتخابی را سورت کرده و هم بر اساس فرمت کردن ردیف اول ستون های انتخاب شده، تخمین می زند که داده های شما دارای عنوان ستون هستند یا نیستند. اگر عنوان ها Bold باشند، برنامه Excel تشخیص می دهد که آن ها عنوان ستون ها هستند و مرتبشان نمی کند. در عوض، از اولین ستون سمت چپ تا آخرین ستون سمت راست، تا 256 ستون، مرتب می شود. کُد ماکرو مورد نیاز شما باید در مدول استاندارد قرار داده شود. برای رفتن به این قسمت، مسیر Tools → Macro → Visual Basic Editorرا دنبال کرده (Alt/Options-F11)، سپس Insert → Module را انتخاب و کد زیر را وارد کنید:
 Sub SortBy )
Dim l As Long
For l = Selection.Columns.Count To 1 Step -1
    Selection.Sort Key1:=Selection.Cells(2, l), _
     Order1:=xlAscending, Header:=xlGuess, Orientation:=xlTopToBottom
Next l
End Sub
برای برگشتن به فایل، روی آیکون Excel کلیک کرده یا Alt+Q را فشار دهید. با یک بار وارد کردن این کد می توانید در هر بار استفاده از Excel، تعداد ستون بیشتری از حد استاندارد را مرتب کنید.

مرتب کردن تصادفی (Random)در اکسل
شما می توانید با استفاده از برنامه ی Excelسه برندهیکم، دوم و سوم را به طور تصادفی (random) و از یک لیست دلخواه در صفحه ی گسترده ی خودتان انتخاب کنید. آسان ترین و بهترین روش برای این کار استفاده از تابع RAND و ترکیب آن با امکانات مرتب کردن (سورت کردن) است.
فرض کنیم یک جدول سه ستونی در صفحه گسترده ی خود دارید که از ستون Bشروع شده و به ترتیب شامل نام، سن و شماره شناسنامه است. می توانید تابع RAND در اکسل را در خانهA2 قرار داده و در خانه های پایینی آن به تعداد دلخواه، تا پایان جدول، کپی کنید. به محض انجام این کار، هر خانه در ستون A دارای تابع RAND خواهد بود که به شکل خودکار (اتوماتیک) یک عدد تصادفی (رندوم) را بر می گرداند که با استفاده از آن می توان جدول را سورت کرد. به عبارت دیگر، می توان ستون های A، B، C و D را به ترتیب صعودی یا نزولی و با استفاده از ستون A، مرتب کرد که به این ترتیب سه داده اول، سه برنده ی مورد نظر خواهند بود.
تابع RAND یک تابع به اصطلاح نا پایدار است که با انجام هر تغییری در فایل، به عنوان مثال وارد کردن داده ها در هر قسمت یا تکرار یک سری محاسبات با فشار دادن F9، محاسبات را دوباره انجام می دهد. پس بهتر است قبل از هر کار دیگر، برنده ها را در جایی دیگر یادداشت کنید.
البته می توانید از این خصوصیت تابع RAND به نفع خودتان استفاده کرده یک ماکرو را ثبت کنید که داده هایی را بی درنگ بعد از هر محاسبه مجدد، مرتب کرده  و تابع RAND را مجبور کند تا به مجموعه دیگری از اعداد رندوم برگردد. پس از آن شما می توانید این ماکرو را به یک دکمه پیوست (attach) کرده و هر بار که بخواهید سه برنده را نشان دهید، فقط باید روی همان دکمه کلیک کرده و سه نام بالایی را انتخاب کنید.
برای مثال، فرض کنیم در ستون های B، C و D داده هایی داریم و در ردیف 1، عنوان های ستون ها را قرار داده ایم. در ابتدا، عنوان RAND را در خانهA1 قرار می دهیم. عبارت =RAND() را در خانهA2 وارد کرده و به تعداد نیاز در خانه های پایین آن کپی می کنیم. سپس هر خانه را انتخاب و مسیر Developer → Code → Record Macro (در نسخه های قبل از 2007، مسیر Tools → Macro → Record New Macro …) را دنبال می کنیم.
ستون های A، B، C و D را انتخاب کرده و F9 را فشار می دهیم (برای محاسبه ی مجدد در Mac از علامت Office استفاده کنید). با دنبال کردنSort & Filter options → Data → Sort، داده ها بر طبق ستون A مرتب می شوند. پس از آن باید ثبت کردن ماکرو را متوقف کنیم.
سپس، مسیر Control Options → Developer → Insert (برای نسخه های قبل از 2007، مسیر View → Toolbars → Forms) را دنبال می کنیم. یک دکمه را از نوار ابزار Forms انتخاب کرده و در یک جای دلخواه صفحه (worksheet) قرار می دهیم. ماکرویی که ثبت کرده ایم را به این دکمه اختصاص داده و روی OK کلیک می کنیم (اگر می خواهید می توانید نام دکمه را از Button 1 به هر نام دیگری تغییر دهید).
می توانید ستون A را انتخاب و آن را مخفی (Hide) کنید. لزومی ندارد که هر کاربری این ستون را ببیند. هر بار که روی این دکمه کلیک کنید، داده های شما به طور تصادفی مرتب شده و تنها کافیست مطابق شکل 6، سه داده ی بالایی (برنده ها) را بخوانید.
 تابع RAND در Excel 2003 و Excel 2007 یک ایراد بزرگ دارد. اگرچه در فایل Help این برنامه ها به روشنی بیان شده که عدد تصادفی برگشت داده شده بین 0 و 1 خواهد بود، همیشه در مواردی که این تابع برای چندینخانه استفاده می شود، چنین نیست. در برخی موارد تابع RAND عددی کمتر از 0 را بر می گرداند. در سایت http://support.microsoft.com/default.aspx?kbid=828795، دلیل Microsoft برای تغییر الگوریتم آورده شده است.
دستکاری (Manipulate) داده ها با فیلتر پیشرفته (Advanced Filter)
اگر با ابزار AutoFilter برنامه ی Excel آشنایی دارید، با محدودیت های آن نیز آشنا هستید. اگر به دستکاری داده ها در مقیاس وسیعی نیاز دارید، استفاده از ابزار فیلتر پیشرفته (Advanced Filter)، همان راهی است که باید انتخاب کنید.
با تمام محدودیت ها، AutoFilters یک راه مناسب برای نمایش داده هایی است که در یک محدوده ی مشخص قرار دارند (در معیارهای مشخصی صدق می کنند). اگرچه در برخی موارد نمی توانید با استفاده از امکانات استاندارد AutoFilters، اطلاعات مورد نظرتان را جمع آوری کنید. ابزار Advanced Filter که یکی از امکانات متنوع و چند کاره در Excel است، امکان دستکاری بیشتر در داده ها را برای شما فراهم می کند. وقتی از ابزار Advanced Filter استفاده می کنید، جدول شماباید در فرمت جدول کلاسیک تنظیم شده باشد.
برای استفاده از ابزار Advanced Filter لازم است یک کپی از عنوان های ستون های جدول خود را در جایی بالای داده ها داشته باشید. همیشه باید دست کم سه ردیف خالی بالای جدول داده ها داشته باشید. برای اطمینان از اینکه عنوان های شما به درستی یکسان بوده و بدون توجه به تغییر عنوان های ستون ها یکسان باقی می مانند، همیشه عنوان ستون ها را با یک فرمول مرجع ساده مانند =A4 که در آن A4 شامل یک عنوان ستون است، ارجاع بدهید. این فرمول را به تعداد عنوان های ستون موجود در جدول خود کپی کنید. این فرمول نویسی ساده شما را از پویا (دینامیک) بودن عنوان ها در فیلتر پیشرفته مطمئن می کند. درست زیر این عنوان ها، معیارهای استفاده از فیلتر پیشرفته را قرار دهید. برای اطلاع بیشتر به قسمت Advanced Filters Criteria در Excel Help مراجعه کنید.
در زمان کار با فیلتر پیشرفته به خاطر داشته باشید که برای دو یا چند معیاری که به طور مستقیم در زیر عنوان قرار گرفته اند باید از OR استفاده کرد. اگر می خواهید از AND استفاده کنید، عنوان ستون ها و معیار باید دو بار، پهلو به پهلو، ظاهر شوند. شکل 7 چگونگی استفاده از عملگر OR و شکل 8 چگونگی استفاده از عملگر AND برای فیلتر کردن داده ها را نشان می دهد.
استفاده ی ساده ای از فیلتر پیشرفته در هر دو مثال نشان داده شده است و در هر دو مورد می توان به جای فیلتر پیشرفته از AutoFilter استفاده کرد. در ادامه مثال هایی زده می شوند که در آن ها امکان استفاده از AutoFilter وجود ندارد و باید از فیلتر پیشرفته استفاده شود.
توجه به این نکته مهم است که در زمان استفاده از یک فرمول برای معیار، نباید در بالای معیار از یک عنوان یکسان با داده های داخل جدول استفاده کرد. برای مثال اگر لیستی از داده های عددی در ستون A دارید که از خانه A5 شروع می شود (یعنی خانهA4 عنوان ستون است) و می خواهید تمام عدد های بیشتر از متوسط را از آن خارج کنید، باید از معیاری مانند:
 =A5>AVERAGE($A$5:$A$500)
استفاده کنید. اگر معیار در خانهA2 قرار گرفته، محدوده ی داده عبارت از $A$1:$a$2 است اما $A$1 نمی تواند شامل عنوان یکسانی با آنچه برای یکی از لیست ها استفاده شده، باشد. یا باید خالی بوده یا به کلی متفاوت باشد.
همچنین توجه به این نکته نیز اهمیت دارد که تمام فرمول های مورد استفاده باید بازگشت TRUE یا FALSE داشته باشند. محدوده ی تابع متوسط با اضافه کردن علامت دلار ($) قطعی می شود در حالی که ارجاع به خانه A5، یک مرجع وابسته (نسبی) است. توجه به این نکته ضروری است چرا که در زمان استفاده از فیلتر پیشرفته (Advanced Filter)، برنامه Excel، خانهA5 را یک مرجع نسبی در نظر گرفته و لیست را در هر زمان به اندازه ی یک ورودی پایین برده و آن راTRUE یا FALSE نشان می دهد. اگر TRUE برگردانده شود، متوجه می شود که باید نشان داده شود اما اگر FALSE برگردد، در معیار مورد نظر قرار نگرفته پس نشان داده نمی شود.
فرض کنیم چند نام در محدوده ی SAS3:$A$500 و با عنوان های شروع شده از A4 تکرار شده اند. همچنین فرض کنیم تعدادی از عنوان ها نیز چند بار تکرار شده اند. برای جدا کردن نام هایی که بیشتر از یک بار تکرار شده اند، باید از Advanced Filter و فرمول زیر به عنوا معیار استفاده کنید:
 =COUNTIF($A$5:$A$500,A5)>1
با استفاده از Advanced Filterو گزینه ی Copy to Another Location، لیستی که تازه ایجاد شده، شامل تمامنام هایی خواهد بود که بیشتر از یک بار در لیست اصلی تکرار شده اند (شکل 9 را ببینید). تعداد زیادی از این نام ها چندین بار تکرار شده اند اما می توانید به سادگی این لیست جدید را با استفاده ی مجدد از Advanced Filter، فیلتر کرده و این بار ازUnique Records Onlyاستفاده کنید (شکل 10 را ببینید). این بار نام هایی به شما نشان داده خواهد شد که بیش از یک بار در لیست آمده اند.
کسانی که از Advanced Filter استفاده می کنند این سوال را مطرح می کنند که چگونه می توان Excel را مجبور کرد که داده ها را بدون هیچ کم و کاستی با معیاری که آن ها مشخص کرده اند، فیلتر کند. اگر معیار شما Dave باشد و از Advanced Filter روی یک لیست طولانی استفاده کنید، Excel در کنار اسم Dave، اسامی مانند Davey، Dave J، Dave K و غیره را نیز نشان می دهد. به بیان دیگر، هر اسمی که با Dave شروع شود در این معیار قرار می گیرد. برای اینکه Excel مجبور به پیدا کردن عبارت دقیق، در این مثال فقط نام Dave، شود باید از معیار =”=Dave” استفاده کنید.