Всім привіт! Сьогодні я продовжу підвищувати ваші навички володіння важливими програмами і покажу, як зробити сортування в excel.
Вміння потрібне, і якщо ви вирішили попрацювати зі змістовною таблицею, візьміть її дублікат. Працюючи з оригіналом, ви ризикуєте порушити точність підрахунків за рахунок порушення цілісності посилань, а численні сортування перемішують так усі дані, що повернути початковий варіант стане проблематично. Зверніть також увагу, що присутність об'єднаних осередків неприпустима. Тут допоможе їхнє роз'єднання.
Як зробити сортування в excel
Що має у своїй функціональності Excel для сортування даних? Я покажу кілька прикладів, щоб відповісти на запитання: як зробити сортування Excel.
Готуємо таблицю:
Таблиця заповнена результатами, без формул. А осередки, які були об'єднані, змінилися на роз'єднані. Очищаємо заголовки від тексту і отримуємо готову для сортування таблицю.
Для сортування одного стовпця необхідно:
Тепер ми маємо дані, які пройшли сортування до стовпця «Чистий прибуток».
Сортування у стовпці
Сортуємо один стовпець, не торкаючись сусідніх:
Ось так виглядає відсортований стовпець:
Сортуємо в EXCEL за кольором комірки
Можливості сортування настільки широкі, що можливо це зробити навіть з огляду на колір комірки. Створення окремої таблиці передбачає як копіювання даних, а й їх формат. Необхідно:
Розділяємо за наявності об'єднані осередки.
Сортуємо за кольорами.
Робимо виділення таблиці. Вибираємо через "Дані", "Сортування".
Заходимо до параметрів, відзначаємо «Мої дані містять заголовки стовпців». Підтверджуємо кнопкою "Ок". 
Вгорі розташовується найгірший прибуток. 
Сподіваюся, ви розібрали та добре засвоїли урок про те, як зробити сортування у excel. Чекаю на наступні уроки.
Як зробити сортування в excel
Найчастіше роботи з даними вони організовані вертикально, тому коли мається на увазі обробка мається на увазі саме вертикальна обробка даних, включаючи сортування. Тобто. якщо потрібно відсортувати числа за зростанням, мається на увазі, що вгорі списку будуть цифри з мінімальними значеннями, а внизу списку з максимальними. Саме з такою "вертикальною" організацією даних і пов'язана надзвичайна популярність (щодо тієї ж самої).
У цьому матеріалі ми не розглядаємо стандартне "вертикальне" сортування, йому приділено достатньо уваги як на просторах інтернету, так і в нашому інтернет-магазині. В курсі ми розглянули сортування за одним, декількома критеріями, а також сортування з урахуванням атрибутів осередків.
Але, все-таки, як відсортувати комірки Excel по горизонталі? Для горизонтального сортування в Excel є кілька варіантів, які доповнюють один одного:
- За допомогою стандартного механізму настроюваного сортування: Вкладка “Головна”/ група “Редагування”/ команда “Сортування та фільтр”/ “Сортування, що настроюється”. Далі, в параметрах вибираємо сортування стовпчиками діапазону.
Плюси такого варіанту сортування в тому, що можна сортувати будь-який тип інформації (як числа, так і текст). Мінус такого варіанту полягає в тому, що якщо потрібно відсортувати велику кількість даних, доведеться кожен окремий рядок налаштовувати окремо.
- За допомогою функцій або . Цей спосібВирішує проблему великої кількості даних по вертикалі, вміло оперуючи, можна запросто зробити горизонтальне сортування для великої кількості даних. Але в цього способу є одна істотна вада, яка, до речі, відсутня в першому варіанті, – це можливість сортування лише числової інформації. Так, на жаль, для тексту не підійде.
- За допомогою механізму. Ми вже неодноразово використовували цей інструмент при вирішенні конкурсних завдань ExcelWorldChamp та . На перший погляд, рішення може здатися громіздким, проте, в міру освоєння Power Query, можна звикнути. А якщо ви плануєте займатися обробкою даних в Excel, то без використання даного інструменту зараз просто не можна.
Використовуємо горизонтальне сортування за допомогою Power Query
Для здійснення будь-яких маніпуляцій у Power Query дані необхідно попередньо завантажити. На вкладці дані, залежно від типу джерела даних, користуємося відповідною командою. У нашому простому випадку – це “З таблиці/діапазону”.
У самому вікні редактора Power Query переходимо на вкладку "Додавання стовпця", команда "Стовпець, що налаштовується". У діалоговому вікні налаштування стовпця вводимо функцію List.Sort(), як аргументи передаємо стовпці. За умовчанням функцію сортуємо за зростанням, тому цей опціональний параметр потрібно обов'язково вказувати, якщо сортування потрібне у спадному порядку.
List.Sort(([Стовпець1],[Стовпець2],[Стовпець3],[Стовпець4],[Стовпець5],[Стовпець6],[Стовпець7]), Order.Ascending)
що рівноцінно
List.Sort(([Стовпець1],[Стовпець2],[Стовпець3],[Стовпець4],[Стовпець5],[Стовпець6],[Стовпець7])))
а ось формула для спадного порядку
List.Sort(([Стовпець1],[Стовпець2],[Стовпець3],[Стовпець4],[Стовпець5],[Стовпець6],[Стовпець7]), Order.Descending)
Оскільки результатом виходить елемент типу список, потрібно визначитися з подальшими діями сортування, якщо потрібно отримати стовпець зі значеннями, вибираємо опцію "Розгорнути в нові рядки", проте, якщо йде сортування для великої кількості даних, краще скористатися опцією "Вибрати значення…". При вилученні значень усі вони витягуються в одну комірку з певним роздільником (ну чи без). Якщо потім виникне необхідність рознести значення по осередках зробити це можна декількома способами, ми їх розглянули в даному, однак, найкращим варіантомбуде скористатися командою "Розділити стовпець" вкладки "Головна" у самому Power Query.
Якщо в таблиці потрібно знайти певне значення, то найзручніше це робити у відсортованих даних. Далі ми розглянемо, як відсортувати таблицю в Excel за спаданням або зростанням, кольором тексту або осередків та інше.
Для сортування таблиць у Excel використовується пункт меню «Сортування та фільтр»на вкладці головна. Найпростіше сортування даний у таблиці Excel, це сортування за зростанням або за спаданням, яка може бути застосована як до текстових значень, так і до числових.
Для правильного сортування всі осередки, що сортуються, повинні мати однаковий формат, наприклад, всі осередки з цифрами повинні знаходитися в числовому форматі. Також перед значеннями не повинно бути зайвих прогалин і в діапазоні, що сортується, не повинно бути прихованих рядків і стовпців.
Сортування значень у таблиці Ексель починається з вибору стовпця, що сортується. Достатньо навіть встановити курсор у першу комірку стовпця. У нашому прикладі виділимо перший осередок другого стовпця, виберемо в меню «Сортування та фільтр», і вкажемо «Сортування за зростанням».
В результаті сортування даних у таблиці значення у другій таблиці відсортовані за зростанням, але перший осередок так і залишився на своєму місці. Усе це оскільки за замовчуванням перший рядок у таблиці вважається заголовками, тобто. назвами стовпців і не сортується.
Діапазон сортованих даних розширюється автоматично, тобто. Ексель автоматично виділяє всю таблицю та сортує дані по рядках відповідно до обраного стовпця. Якщо виділити всі осередки стовпця, які необхідно відсортувати, та вибрати сортування за зростанням або зменшенням, Ексель видасть повідомлення з пропозицією вибрати варіант сортування. Буде доступний варіант "Автоматично розширити виділений діапазон", при якому буде виділено всю таблицю, і "Сортувати в межах зазначеного виділення", що дозволить відсортувати лише виділений стовпець не торкаючись при цьому даних у сусідніх стовпцях. У цьому випадку при сортуванні знову ж таки не враховуватиметься перший осередок.
Щоб сортування таблиць в Excel проводилося повністю по всіх виділених осередках, слід у меню «Сортування та фільтр»вибирати пункт «Сортування, що налаштовується…».
При цьому відразу побачимо, яку частину таблиці Ексель виділяє для подальшого сортування.
Щоб виділялася вся область даних, слід у вікні, що з'явилося. «Сортування»прибрати галочку з пункту "Мої дані містять заголовки".
Тепер у цьому вікні можна налаштувати сортування наших даних. У першій графі «Стовпець»в рядку "Сортувати за"вибираємо стовпець, дані у якому необхідно відсортувати. У другій графі «Сортування»необхідно вибрати умову, за якою буде виконано сортування. Доступні варіанти «Значення», «Колір комірки», «Колір шрифту»і «Значок осередку». У нашому випадку ми сортуватимемо значення. Ну і в останній графі «Порядок»можна вибрати порядок сортування значень "За зростанням", "За зменшенням"і «Список, що налаштовується». Виберемо за зростанням. В результаті значення середнього стовпця відсортовано.
Також можна налаштовувати сортування окремого стовпця. Крім того, в Екселі можна налаштувати декілька рівнів сортування. Наприклад, відсортуємо перший стовпець по зростанню, а потім сортування другого стовпця також відсортуємо за зростанням. А останній третій стовпець відсортуємо спочатку за кольором комірки, а потім за кольором шрифту. Для додавання нового рівня необхідно у віконці «Сортування»натиснути кнопку «Додати рівень», У цьому порядок слідування рівнів має значення.
Сортування в Ексель - це вбудована функція, за допомогою якої користувач зможе розташувати дані в стовпцях на аркуші в зручному порядку для подальшого аналізу.
Ви зможете відсортувати інформацію в алфавітному порядку, за зростанням або зменшенням значень, за датою або за значками, за кольором тексту або комірки. Саме про це й йтиметься у цій статті.
Чисел
Тут усе досить просто. Наприклад візьмемо таку таблицю. Зробимо в ній сортування даних по стовпцю С. Для цього виділяємо його та на вкладці «Головна» клацаємо на кнопочку «Сортування та фільтр». У наступному меню виберіть або «… від мінімального до максимального», або «… від максимального до мінімального». Виберемо другий варіант.
Тепер у нас дані в С розміщені в порядку зменшення.
У мене стовпець С розташований між двома іншими, які заповнені даними. У цьому випадку Excel вважає, що виділений стовпець - це частина таблиці (і вважає правильно). В результаті з'явилося таке повідомлення. Оскільки мені потрібно зробити сортування конкретно для класу, виділяю маркером пункт «… у межах зазначеного виділення»та натискаю «Сортування» .
За алфавітом
Вона робиться за тим самим принципом, як було описано вище. Виділяємо потрібний діапазон і натискаємо кнопочку «Сортування та фільтр». У меню, що випадає, пункти змінилися. Виберіть або від "А до Я", або від "Я до А".
Список імен у прикладі відсортовано за абеткою.
По даті
Щоб відсортувати дати в Ексель, спочатку зверніть увагу, який формат встановлений для осередків, в яких вони записані. Виділіть їх та на вкладці «Головна» подивіться на групу «Число» . Найкраще підійде або формат «Дата», короткий або довгий, або «(всі формати)» – дата може бути записана у різний спосіб: ДД.ММ.РРРР, ДД.МММ, МММ.РР.
Цей момент дуже важливий, тому що в іншому випадку, дати можуть бути відсортовані просто за зростанням перших двох чисел, або за місяцями в алфавітному порядку.
Після цього виділяємо потрібний діапазон осередків і тиснемо на кнопочку «Сортування та фільтр». У меню можна вибрати або «від старих до нових», або «від нових до старих».
За кольором комірки чи тексту
Цей спосіб можна використовувати, коли в таблиці Excel текст у комірках або самі комірки зафарбовані у різний колір. Наприклад візьмемо стовпець із чисел, зафарбованих різними кольорами. Відсортуємо його, щоб спочатку йшли числа, зафарбовані у червоний, потім зелений та чорний колір.
Виділяємо весь діапазон, клацаємо на кнопочку «Сортування та фільтр»та вибираємо з меню «Настроювана…».
У наступному вікні приберіть галочку з поля , якщо Ви виділили їх без верхнього рядка, який є шапкою таблиці. Потім вибираємо стовпець, яким сортуватимемо, у прикладі це «I» . У розділі «Сортування» зі списку вибираємо «Колір шрифту» . У розділі порядок вибираємо "червоний колір" - "Зверху". Це ми відсортували числа червоного кольору.
Тепер потрібно, щоб у стовпчику йшли числа зеленого кольору. Натисніть на кнопку «Додати рівень». Усі налаштування ті самі, тільки виберіть "зелений колір" . Натисніть кнопку «ОК» .
Наш стовпець відсортовано в такий спосіб.
Як бачите, числа йдуть не по порядку. Давайте відсортуємо числа у порядку зростання. Виділяємо стовпець, натискаємо «Сортування та фільтр» – «Настроювана …». У вікні, натисніть на кнопку «Додати рівень». Стовпець залишається «I», в наступному полі вибираємо за «Значенням», порядок "За зростанням". Натисніть кнопку «ОК» .
Тепер наш стовпець відсортований і за кольором тексту та у порядку зростання даних.
Аналогічно сортуються дані і за кольором комірки, тільки у розділі «Сортування» вибирайте зі списку «Колір комірки» .
Таблиці
Якщо у Вас є таблиця, в якій потрібно виконати сортування одразу за кількома стовпцями, робимо таке. Виділяємо весь діапазон осередків таблиці разом із шапкою. Клацаємо по кнопочці «Сортування та фільтр»і вибираємо «Настроювана …».
Давайте відсортуємо клас у порядку зростання, і так само середній бал.
У вікні сортування ставимо галочку у полі "Мої дані містять заголовки". У розділі «Стовпець» вибираємо зі списку «Клас», сортування за «Значенням», а порядок "За зростанням".
Щоб зробити все те саме за середнім балом, натисніть на кнопку «Додати рівень». У розділі «Стовпець» вибираємо «Середн.бал». Натисніть кнопку «ОК» .
Дані у таблиці відсортовані.
Тепер у стовпці «Ім'я» зафарбуємо осередки з хлопчиками синій колір, клітинки з дівчатами в рожевий. Щоб не робити це для кожного осередку окремо, прочитайте статтю, як виділити осередки в Excel – в ній написано, як виділити несуміжні осередки.
Виконаємо сортування цього стовпця за кольором осередку: спочатку будуть дівчатка, потім хлопчики. Знову виділяємо всю таблицю, тиснемо «Сортування» – «Настроювана …».
У вікні вже є два рівні, які ми зробили раніше. Ці рівні мають пріоритет – у першого найбільший, у другого менше тощо. Тобто, якщо ми хочемо, щоб спочатку виконалося сортування даних у таблиці дівчинки/хлопчики, потім за класом, а потім за середнім балом – потрібно в такому порядку розставити рівні.
Натискаємо на кнопку «Додати рівень». У розділі «Стовпець» вибираємо «Ім'я», сортування – «Колір комірки», порядок – «рожевий», «Зверху».
Тепер за допомогою стрілок переміщуємо цей рядок нагору списку. Натисніть кнопку «ОК» .
Таблиця з відсортованими даними виглядає так.
Сортування даних у Excel – інструмент для представлення інформації у зручному для користувача вигляді.
Числові значення можна відсортувати за зростанням та спаданням, текстові – за алфавітом та у зворотному порядку. Доступні варіанти – за кольором та шрифтом, у довільному порядку, за кількома умовами. Сортуються стовпці та рядки.
Порядок сортування в Excel
Існує два способи відкрити меню сортування:
Методи сортування, що часто використовуються, представлені однією кнопкою на панелі завдань:
Сортування таблиці за окремим стовпцем:
Якщо виділити всю таблицю і виконати сортування, відсортується перший стовпець. Дані у рядках стануть відповідно до положення значень у першому стовпці.
Сортування за кольором комірки та за шрифтом
Програма Excel надає користувачеві багаті можливості форматування. Отже, можна оперувати різними форматами.
Зробимо в навчальній таблиці стовпець «Підсумок» та «заллємо» комірки зі значеннями різними відтінками. Виконаємо сортування за кольором:
Програма відсортувала осередки за акцентами. Користувач може самостійно вибрати порядок сортування кольору. Для цього в списку можливостей інструменту вибираємо «Сортування, що настроюється».
У вікні вводимо необхідні параметри:
Тут можна вибрати порядок представлення різних за кольором осередків.
За таким же принципом сортуються дані щодо шрифту.
Сортування в Excel за кількома стовпцями
Як встановити порядок вторинного сортування в Excel? Для вирішення цього завдання потрібно задати кілька умов сортування.
Програма дозволяє додати відразу кілька критеріїв, щоб виконати сортування в особливому порядку.
Сортування рядків у Excel
За замовчуванням сортуються дані про стовпці. Як здійснити сортування за рядками в Excel:
У такий спосіб виконується сортування таблиці в Excel за кількома параметрами.
Випадкове сортування в Excel
Вбудовані параметри сортування не дозволяють розташувати дані у стовпці випадковим чином. З цим завданням впорається функція СЛЧИС.
Наприклад, необхідно розмістити у випадковому порядку набір деяких чисел.
Ставимо курсор у сусідній осередок (зліва-праворуч, не важливо). У рядок формул вводимо СЛЧИС(). Тиснемо Enter. Копіюємо формулу на весь стовпець – отримуємо набір випадкових чисел.
Тепер відсортуємо отриманий стовпець за зростанням/зменшенням – значення у вихідному діапазоні автоматично розташуються у випадковому порядку.
Динамічний сортування таблиці в MS Excel
Якщо застосувати до таблиці стандартне сортування, то при зміні даних воно не буде актуальним. Потрібно зробити так, щоб значення сортувалися автоматично. Використовуємо формули.
Якщо потрібно зробити динамічне сортування за спаданням, використовуємо функцію НАЙБІЛЬШИЙ.
Для динамічного сортування текстових значеньзнадобляться формули масиву.
Згодом при додаванні даних до таблиці процес сортування виконуватиметься автоматично.
