نحوه ساخت جدول در اکسل بر اساس حروف الفبا. لیست کشویی مرتبط در اکسل بر اساس حروف الفبا

نحوه ایجاد یک لیست کشویی دراکسل بر اساس حروف الفبا برای انتخاب نام خانوادگی با حرف الفبا؟ وقتی نام‌های خانوادگی زیادی در لیست کشویی وجود دارد، راحت است که یک الفبا در لیست کشویی ایجاد کنید و سپس با انتخاب حرف مورد نظر، نام خانوادگی را انتخاب کنید. برای این شما باید انجام دهید لیست های کشویی مرتبط با اکسل.
به عنوان مثال، در صفحه اول جدول اصلی را داریم که یک لیست کشویی با نام ها در آن قرار می دهیم.
در برگه دوم کتاب کار اکسل، بیایید لیستی از نام خانوادگی را تهیه کنیم. بیایید این لیست را بر اساس حروف الفبا مرتب کنیم. لیست به شرح زیر است.
نحوه ایجاد یک لیست غیر تکراری برای یک لیست کشویی از یک لیست بزرگ با نام های تکراری، به مقاله "ایجاد یک لیست در اکسل بدون تکرار" مراجعه کنید.
حالا بیایید این لیست را یک نام بگذاریم. برای نحوه نامگذاری یک محدوده، به مقاله "محدوده در اکسل" مراجعه کنید. ما نام این محدوده را "نام خانوادگی" گذاشتیم.
برای تغییر خودکار مرزهای محدوده هنگام اضافه یا تفریق نام خانوادگی، باید آن را پویا کنید. به مقاله «تغییر اندازه خودکار جدول اکسل» مراجعه کنید.
اکنون باید لیستی از حروفی ایجاد کنیم که نام های خانوادگی را در لیست ما شروع می کند.
نحوه نوشتن حروف الفبا در اکسل با فرمول.
ابتدا در ستون C الفبای معمول را می نویسیم. این را می توان به سرعت با یک فرمول انجام داد. در سلول C فرمول را بنویسید.
= CHAR (192 + LINE (Z1) -1)
192 کد حرف A است. فرمول را از پایین ستون تا حرف I کپی کنید. الفبا آماده است.
اکنون در ستون بعدی فقط باید حروفی را بنویسید که نام خانوادگی در لیست ما با آنها شروع می شود. اینجا به ما کمک خواهد کردفرمول آرایه اکسل... در سلول D2، چنین فرمول طولانی را نوشتیم.
= IFERROR (INDEX ($ C $ 2: $ C $ 33; SMALL (IF (COUNTIF
(نام خانوادگی؛ $ C $ 2: $ C $ 33 & "*")> 0; STRING ($ C $ 2: $ C $ 33)؛ "");
LINE (Z1)) - LINE ($ C $ 1))؛ "")
این یک فرمول آرایه است، p.e. ما نه تنها "Enter" را فشار می دهیم، بلکه کلیدهای ترکیبی "Shift" + "Ctrl" + "Enter" را فشار می دهیم. فرمول در بریس های مجعد محصور خواهد شد. فرمول را به سمت پایین ستون بکشید. اینجوری گرفتیم

همه چیز. اکنون در سلول D1 یک حرف را انتخاب می کنیم و در سلول E1 یک لیست کشویی از نام خانوادگی فقط برای این حرف ظاهر می شود. برای مثال. یا

دسته ای از کاربران اکسل وجود دارند که با کتاب های کاری کار می کنند که به جای 3 برگه استاندارد، تعداد بسیار بیشتری از آنها را شامل می شود. یک نفر در این دفاتر تخمین هایی دارد که اعداد آن اسم برگه ها است، یکی کارهایی که توسط پیمانکاران انجام شده است، یکی مصرف سوخت ماشین های تاکسی مسیر ثابت، یکی مجمع محاسبات اقتصادی و دیگری برقی دارد. ویژگی های تجهیزات تکنولوژیکی، روزانه در یک برگه جداگانه (یا حتی ساعتی) ثبت می شود. اگر برگه های چنین کتابی به نحوی از قبل مرتب شده باشد خوب است، اما اگر نه؟ حتی یک جستجوی ساده برای برگه مناسب در یک کتاب به دور از سرگرمی است. من واقعاً می خواهم کاربرگ ها را بر اساس حروف الفبا مرتب کنم، اما در ابزارهای استاندارد اکسل این امکان وجود ندارد.

من به شما توجه شما را با استفاده آسان ارائه می کنم افزودنی (ماکرو) برای اکسلکه اجازه می دهد برگه های کتاب کار اکسل را به ترتیب صعودی و نزولی مرتب کنید ... اگر کسی با مرتب‌سازی نادرست مواجه شده باشد، وقتی بلافاصله بعد از Sheet1 Sheet11 است و Sheet2 نیست، ممکن است نگران نباشند. این افزونه این مشکل را حل می کند، برگه هایی را با همان کیفیت بالا مرتب می کند که نام آنها از حروف، اعداد و ترکیبی از حروف، اعداد، فاصله و علامت تشکیل شده است. علاوه بر این، این کار را حتی با صفحات مخفی انجام می دهد. افزونه به‌درستی و سریع برگه‌هایی را با نام‌هایی که فقط از اعداد تشکیل شده‌اند، مانند 1، 2، 3، 4، 5، و همچنین برگه‌هایی با نام‌هایی مانند 1-1-1، 1-1-2 مرتب می‌کند. 1-1 -3.

افزونه توسط دکمه ای که در منوی اکسل نمایش داده می شود راه اندازی می شود و با کلیک بر روی آن کاربر کادر محاوره ای را می بیند که با آن می تواند روش مرتب سازی برگه ها را به ترتیب صعودی یا نزولی انتخاب کند. نیز وجود داردورق ها را بر اساس رنگ برگه و لیست های سفارشی مرتب کنید.

شما همچنین می توانید. نصب این افزونه آسان است و برای کسانی که هنوز با نصب افزونه ها در اکسل مواجه نشده اند، آموزش گام به گام... به نظر من، این یک جایگزین مناسب برای کشیدن و انداختن دستی برگه ها است.

در این مثال، سریع‌ترین و مؤثرترین روش‌های مرتب‌سازی داده‌ها در اکسل را بر اساس معیارهای مختلف در نظر خواهیم گرفت: اعداد صعودی یا نزولی، بر اساس تاریخ از قدیم به جدید و از جدید به قدیمی، حروف الفبا از A تا Z و همچنین به ترتیب حروف الفبای معکوس ....

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

شکل زیر مجموعه ای از اطلاعات از پایگاه داده در مورد کارکنان شرکت را نشان می دهد:

فرض کنید باید جدول را نسبت به ستون "Bonus" به ترتیب نزولی مقادیر مرتب کنیم. برای حل این مشکل از ساده ترین ابزارهای مرتب سازی (پایه) در اکسل استفاده می کنیم.

مکان نما صفحه کلید را به هر سلولی در ستون G ببرید (حتی می توانید به سربرگ ستون G1 بروید). و سپس ابزار را انتخاب کنید: "MAIN" - "Editing" - "Sort and Filter" - "Sort Descending".

در نتیجه، داده ها در یک ساختار خاص مرتب و سازماندهی شدند:

همانطور که معلوم شد، بزرگترین جایزه توسط کارمند "Evgeniy Prutkiy" دریافت شد.

توجه! لیستی که باید مرتب شود نباید حاوی سلول های ادغام شده باشد.

توجه داشته باشید. ابزار مرتب‌سازی اولیه محدوده‌ها نیز در برگه موجود است: "DATA" - "مرتب‌سازی صعودی / نزولی".

و در منوهای کشویی هدر جدول در حالت فیلتر خودکار یا جدول هوشمند تعبیه شده است:

همچنین در منوی زمینه که با کلیک بر روی دکمه سمت راست ماوس ظاهر می شود، تعبیه شده است:

نحوه حذف مرتب سازی در اکسل

اگر جدول نیاز به بازیابی به شکل اصلی خود دارد، قبل از هر مرتب سازی یک ستون شماره ردیف ایجاد کنید. در این مثال، این ستون A است (# p / p). اغلب لازم است که جدول پس از انجام مرتب‌سازی داده‌های متعدد و پیچیده با ستون‌ها، معیارها یا شرایط مختلف، به شکل اصلی خود بازگردد. سپس کافی است بر اساس ستون با اعداد ردیف مرتب کنیم تا جدول شکل اصلی خود را به خود بگیرد.

چگونه یک ستون را در اکسل مرتب کنیم

گاهی اوقات لازم است که مرتب سازی مستقل فقط در یک ستون از جدول انجام شود بدون اینکه به ستون های دیگر متصل شود یا آنها را تغییر دهد. به عنوان مثال، ستون B "نام". این کار را می توان به سادگی حل کرد:

1. کل ستون B را با کلیک چپ روی سربرگ ستون برگه انتخاب کنید.
2. ابزار را انتخاب کنید: "HOME" - "Editing" - "Sort & Filter" - "Sort Descending".
3. در کادر محاوره‌ای «داده‌های شناسایی شده در محدوده نامشخص» که ظاهر می‌شود، گزینه «مرتب‌سازی در محدوده انتخاب‌شده» را انتخاب کنید. و بر روی دکمه "مرتب سازی" کلیک کنید.

توجه داشته باشید که مقادیر سلول ها در ستون های باقی مانده بدون تغییر باقی می مانند:

این روش فقط باید به عنوان یک روش کمکی استفاده شود.

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

چگونه مرتب سازی را در اکسل سفارشی کنیم؟ فرض کنید می خواهیم کارمندان را بر اساس تاریخ تولد (قدیمی ترین به جدیدترین) بر اساس هر شهر (محل تولد) مرتب کنیم. برای این:

جدول ساختار زیر را به دست آورده است:

ابتدا مقادیر آن بر اساس حروف الفبا نسبت به نام شهرها مرتب شده است. علاوه بر این، نسبت به هر شهر، تاریخ تولد از قدیم به جدید توزیع می شود.

اگر فقط با یک قطعه از جدول کار کنیم که در آن سر ستونی وجود ندارد، بلکه فقط محدوده ای از سلول ها با مقادیر فعلی وجود دارد، همانطور که در شکل زیر نشان داده شده است:

سپس در کادر محاوره ای ابزار مرتب سازی سفارشی، گزینه My Data Has Column Headers را خاموش کنید. در نتیجه، عناوین ستون‌های کاربرگ اکسل (ستون A، ستون B، C و غیره) به عنوان معیار برای اولین لیست کشویی در بخش "ستون" در هر سطح استفاده می شود. و هنگامی که تمام پارامترها اعمال می شوند، تمام ردیف ها و سلول های محدوده اصلی (قطعه جدول) در مرتب سازی قرار می گیرند که در این مورد نتیجه صحیح است.

توجه داشته باشید. دکمه فراخوانی ابزار برای مرتب سازی سفارشی نیز در برگه "DATA" - "Sort" موجود است.

جداول داده هایی که از کارمندان مختلف دریافت می کنیم در بیشتر موارد سفارشی نیستند و بدون سازماندهی کار با آنها دشوار است. نیاز به مرتب سازی وجود دارد: مقادیر متن بر اساس حروف الفبا، مقادیر عددی به ترتیب صعودی، و تاریخ ها از قدیمی به جدید. مرتب کردن همه ستون ها به صورت جداگانه با دست بهترین راه حل برای این کار نیست. به خصوص اگر بیش از هزار ستون وجود داشته باشد. بنابراین، بهتر است از ابزار داخلی اکسل مخصوص - مرتب سازی سفارشی استفاده کنید. این ابزار به طور خودکار داده ها را بر اساس چندین ستون به طور همزمان مرتب می کند. آنقدر انعطاف پذیر است که می توان از آن برای تقریباً هر مرتب سازی با هر سطح دشواری استفاده کرد.

در این مقاله، من به شما نشان خواهم داد که چگونه در اکسل داده ها را بر اساس چندین ستون، بر اساس حروف الفبا بر اساس سرفصل ستون ها و بر اساس مقادیر در هر ردیف مرتب کنید. همچنین یاد خواهید گرفت که چگونه داده ها را به روش های غیر استاندارد مرتب کنید، در صورتی که مرتب سازی بر اساس حروف الفبا یا مقدار عدد قابل اجرا نیست.

من فکر می کنم همه می دانند که چگونه بر اساس ستون حروف الفبا یا صعودی / نزولی مرتب شوند. این کار با لمس یک دکمه انجام می شود. و من(الف-ذ) و Z-A(Z-A) در بخش ویرایشبرگه (ویرایش). خانه(صفحه اصلی) یا در بخش مرتب سازی و فیلتر کردنبرگه (مرتب سازی و فیلتر). داده ها(داده ها):

با این حال، مرتب‌سازی در اکسل پارامترها و حالت‌های عملیاتی قابل تنظیم بیشتری دارد که چندان واضح نیستند، اما می‌توانند بسیار راحت باشند:

مرتب سازی بر اساس چندین ستون

من به شما نشان خواهم داد که چگونه داده ها را بر اساس دو یا چند ستون در اکسل مرتب کنید. کار ابزار با استفاده از مثال Excel 2010 نشان داده شده است - این نسخه نصب شده بر روی رایانه من است. اگر در نسخه دیگری از برنامه کار می کنید، نباید مشکلی داشته باشید، زیرا مرتب سازی در اکسل 2007 و اکسل 2013 تقریباً به یک شکل عمل می کند. تنها تفاوت را می توان در رنگ کادرهای محاوره ای و شکل دکمه ها مشاهده کرد. پس بیایید شروع کنیم ...

مرتب سازی داده ها بر اساس چندین ستون در اکسل به هیچ وجه دشوار نیست، درست است؟ با این حال، در کادر محاوره ای مرتب سازی(مرتب سازی) امکانات بسیار بیشتری وجود دارد. بعداً در این مقاله، به شما نشان خواهم داد که چگونه به جای ستون، داده ها را در یک کاربرگ بر اساس حروف الفبا مرتب کنید. همچنین یاد خواهید گرفت که چگونه داده ها را به روش های غیر استاندارد مرتب کنید، در صورتی که مرتب سازی بر اساس حروف الفبا یا مقدار عدد قابل اجرا نیست.

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

من معتقدم که 90٪ مواقع اکسل داده ها را بر اساس مقدار در یک یا چند ستون مرتب می کند. با این حال، گاهی اوقات مجموعه داده های چندان ساده ای وجود ندارد که باید بر اساس ردیف (افقی) مرتب شوند، یعنی ستون ها را از چپ به راست بر اساس عناوین یا مقادیر ستون در یک ردیف خاص مرتب کنید.

در اینجا لیستی از دوربین های ارائه شده توسط نماینده منطقه ای شما یا دانلود شده از اینترنت آمده است. این لیست شامل انواع ویژگی ها، مشخصات و اطلاعات قیمت است و چیزی شبیه به این است:

ما باید این لیست از دوربین ها را بر اساس پارامترهایی که برای ما مهم هستند مرتب کنیم. به عنوان مثال، اولین مرحله مرتب سازی بر اساس نام مدل است:

در نتیجه مرتب سازی، باید به چیزی شبیه به این برسید:

در مثال ما، مرتب‌سازی بر اساس عناوین ستون‌ها ارزش عملی جدی ندارد و فقط برای نشان دادن نحوه کارکرد آن به شما انجام می‌شود. به همین ترتیب، می‌توانیم لیست دوربین‌های خود را بر اساس خطی حاوی ابعاد، وضوح، نوع سنسور یا هر پارامتر دیگری که مهم‌تر می‌دانیم، مرتب کنیم. بیایید مرتب سازی دیگری انجام دهیم، این بار بر اساس قیمت.

وظیفه ما این است که مراحل 1 - 3 بالا را تکرار کنیم. سپس در مرحله 4 به جای خط 1 خط را انتخاب کنید 4 ، که در آن قیمت های خرده فروشی درج شده است (Retail Price). در نتیجه مرتب سازی، جدول به شکل زیر خواهد بود:

لطفاً توجه داشته باشید که داده ها نه تنها در ردیف انتخاب شده مرتب شده اند. کل ستون ها مبادله می شوند، اما داده ها به هم ریخته نمی شوند. به عبارت دیگر، اسکرین شات بالا لیستی از دوربین ها است که از ارزان ترین به گران ترین طبقه بندی شده اند.

امیدواریم اکنون مشخص شده باشد که مرتب سازی بر اساس ردیف در اکسل چگونه کار می کند. اما اگر لازم باشد داده های ما مرتب شوند نه بر اساس حروف الفبا یا صعودی / نزولی چه؟

مرتب سازی به ترتیب تصادفی (بر اساس لیست سفارشی)

اگر نیاز دارید که داده های خود را به ترتیب خاصی (نه بر اساس حروف الفبا) سازماندهی کنید، می توانید از لیست های سفارشی ساخته شده در اکسل استفاده کنید یا لیست های خود را ایجاد کنید. با استفاده از فهرست‌های قابل تنظیم داخلی، می‌توانید مثلاً روزهای هفته یا ماه‌های سال را مرتب کنید. مایکروسافت اکسل دو نوع از این لیست های آماده را ارائه می دهد - با نام های کوتاه و با نام کامل.

فرض کنید فهرستی از کارهای هفتگی در خانه داریم و می خواهیم آنها را بر اساس روز هفته یا بر اساس اهمیت مرتب کنیم.

آماده! اکنون کارهای خانه بر اساس روزهای هفته مرتب می شوند:

اظهار نظر:اگر می‌خواهید در این داده‌ها تغییراتی ایجاد کنید، به یاد داشته باشید که داده‌های جدید اضافه شده یا تغییر یافته موجود به‌طور خودکار مرتب نمی‌شوند. برای مرتب سازی مجدد، روی دکمه کلیک کنید تکرار(تجدید درخواست) در زیر مرتب سازی و فیلتر کردنبرگه (مرتب سازی و فیلتر). داده ها(داده ها).

همانطور که می بینید، مرتب کردن داده ها در اکسل به یک لیست سفارشی به هیچ وجه دشوار نیست. ترفند دیگری که باید یاد بگیریم این است که داده ها را در لیست سفارشی خود مرتب کنیم.

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

جدول ما یک ستون دارد اولویت- شامل اولویت های وظایف است. برای استفاده از آن برای سازماندهی وظایف هفتگی خود از مهم تر به کم اهمیت تر، این مراحل را دنبال کنید.

مراحل 1 و 2 را از مثال قبلی تکرار کنید. وقتی کادر محاوره ای باز می شود لیست ها(فهرست های سفارشی)، در ستونی به همین نام در سمت چپ، کلیک کنید لیست جدید(لیست جدید) و مقادیر مورد نیاز را در فیلد وارد کنید فهرست موارد(مدخل های فهرست). عناصر لیست خود را دقیقاً به ترتیبی که در نتیجه مرتب سازی باید مرتب شوند، با دقت وارد کنید.

را کلیک کنید اضافه کردن(افزودن)، و لیستی که ایجاد کرده اید به لیست های موجود اضافه می شود. بعد فشار دهید خوب.

فهرست کارهای خانه ما به ترتیب بر اساس اهمیت به این صورت است: