Програма Microsoft Excel: сортування та фільтрація даних. Як додати сортування в екселе Горизонтальне сортування в Excel
Сортування даних у Excel – інструмент для представлення інформації у зручному для користувача вигляді.
Числові значення можна відсортувати за зростанням та спаданням, текстові – за алфавітом та у зворотному порядку. Доступні варіанти – за кольором та шрифтом, у довільному порядку, за кількома умовами. Сортуються стовпці та рядки.
Порядок сортування в Excel
Існує два способи відкрити меню сортування:
- Клацніть правою кнопкою миші по таблиці. Вибрати «Сортування» та спосіб.
- Відкрити вкладку "Дані" - діалогове вікно "Сортування".
Методи сортування, що часто використовуються, представлені однією кнопкою на панелі завдань:
Сортування таблиці за окремим стовпцем:
- Щоб програма правильно виконала завдання, виділяємо потрібний стовпець у діапазоні даних.
- Далі діємо залежно від поставленого завдання. Якщо потрібно виконати просте сортування за зростанням/зменшенням (алфавітом або назад), то достатньо натиснути відповідну кнопку на панелі завдань. Коли діапазон містить більше одного стовпця, Excel відкриває діалогове вікно виду: Щоб збереглася відповідність значень у рядках, вибираємо дію «автоматично розширити виділений діапазон». Інакше відсортується лише виділений стовпець – структура таблиці порушиться.
Якщо виділити всю таблицю і виконати сортування, відсортується перший стовпець. Дані у рядках стануть відповідно до положення значень у першому стовпці.
Сортування за кольором комірки та за шрифтом
Програма Excel надає користувачеві багаті можливості форматування. Отже, можна оперувати різними форматами.
Зробимо в навчальній таблиці стовпець «Підсумок» та «заллємо» осередки зі значеннями різними відтінками. Виконаємо сортування за кольором:
- Виділяємо стовпець - права кнопка миші - "Сортування".
- Із запропонованого списку вибираємо «Спочатку комірки з виділеним кольором».
- Погоджуємося «автоматично розширити діапазон».
Програма відсортувала осередки за акцентами. Користувач може самостійно вибрати порядок сортування кольору. Для цього в списку можливостей інструменту вибираємо «Сортування, що настроюється».
У вікні вводимо необхідні параметри:
Тут можна вибрати порядок подання різних за кольором осередків.
За таким же принципом сортуються дані щодо шрифту.
Сортування в Excel за кількома стовпцями
Як встановити порядок вторинного сортування в Excel? Для вирішення цього завдання потрібно задати кілька умов сортування.
- Відкриваємо меню «Сортування, що настроюється». Призначаємо перший критерій.
- Натискаємо кнопку «Додати рівень».
- З'являються вікна для введення даних наступної умови сортування. Заповнюємо їх.
Програма дозволяє додати відразу кілька критеріїв, щоб виконати сортування в особливому порядку.
Сортування рядків у Excel
За умовчанням сортуються дані про стовпці. Як здійснити сортування за рядками в Excel:
- У діалоговому вікні «Сортування, що настроюється» натиснути кнопку «Параметри».
- У меню вибрати «Стовпці діапазону».
- Натисніть кнопку ОК. У вікні «Сортування» з'являться поля для заповнення умов рядків.
Таким чином виконується сортування таблиці в Excel за кількома параметрами.
Випадкове сортування в Excel
Вбудовані параметри сортування не дозволяють розташувати дані у стовпці випадковим чином. З цим завданням впорається функція СЛЧИС.
Наприклад, необхідно розмістити у випадковому порядку набір деяких чисел.
Ставимо курсор у сусідній осередок (зліва-праворуч, не важливо). У рядок формул вводимо СЛЧИС(). Тиснемо Enter. Копіюємо формулу на весь стовпець – отримуємо набір випадкових чисел.
Тепер відсортуємо отриманий стовпець за зростанням/зменшенням – значення у вихідному діапазоні автоматично розташуються у випадковому порядку.
Динамічний сортування таблиці в MS Excel
Якщо застосувати до таблиці стандартне сортування, то при зміні даних воно не буде актуальним. Потрібно зробити так, щоб значення сортувалися автоматично. Використовуємо формули.
- Є набір простих чисел, які потрібно відсортувати за зростанням.
- Ставимо курсор у сусідній осередок і вводимо формулу: = НАЙМЕНШИЙ (A: A; РЯДОК (A1)). Саме так. Як діапазон вказуємо весь стовпець. А як коефіцієнт – функцію РЯДКУ з посиланням на першу комірку.
- Змінимо у вихідному діапазоні цифру 7 на 25 – «сортування» за зростанням також зміниться.
Якщо потрібно зробити динамічне сортування за спаданням, використовуємо функцію НАЙБІЛЬШИЙ.
Для динамічного сортування текстових значень знадобляться формули масиву.
- Вихідні дані – список деяких назв у довільному порядку. У прикладі – список фруктів.
- Виділяємо стовпець і даємо йому ім'я "Фрукти". Для цього в полі імен, що знаходиться біля рядка формул, вводимо потрібне нам ім'я для присвоєння його до виділеного осередку.
- У сусідньому осередку (у прикладі – у В5) пишемо формулу: Оскільки перед нами формула масиву, натискаємо поєднання Ctrl+Shift+Enter. Розмножуємо формулу на весь стовпець.
- Якщо у вихідний стовпець додаватимуться рядки, то вводимо трохи модифіковану формулу: Додамо в діапазон «фрукти» ще одне значення «помело» і перевіримо:
Завантажити формули сортування даних у Excel
Згодом при додаванні даних до таблиці процес сортування виконуватиметься автоматично.
Робота з таблицями в багатьох випадках вимагає аналізу даних, що містяться в них. Excel надає потужні засоби обробки інформації, можливість побудови статистичних звітів і вибірки даних за умовою. Але іноді досить просто зробити сортування елементів, щоб вирішити поставлене завдання.
Упорядкування чисел
Сортування чисел за зростанням або зменшенням – поширене завдання. Вона допомагає вибудувати числові параметри – кількість товарів та клієнтів, суми продажу та доходів, та подати інформацію у більш наочному вигляді. Зробимо в Excel сортування цих даних.
Наприклад, у таблиці файлу туристичної компанії зберігається інформація про вартість проживання в готелі. Ціна залежить від типу номера та пропонованого харчування. Турист зацікавлений у пошуку найбільш ощадливого розміщення, тому потрібно відсортувати дані щодо зростання ціни.
Для цього виділяємо стовпець таблиці "Вартість, руб." та заходимо на вкладку «Головна». У блоці «Редагування» знаходиться група інструментів «Сортування та фільтр». Вибираємо з меню пункт "Сортування за зростанням".
Програма, виявивши наявність пов'язаних стовпців, уточнює, чи потрібно виконувати сортування в Excel тільки виділеним полем, або ж розширити діапазон.
Вибираємо «Розширити», оскільки ми зацікавлені в упорядкуванні повних записів таблиці та натискаємо «Сортування». Записи збудовані у порядку зростання вартості проживання.
Ця операція виконується ще простіше, якщо таблицю відформатовано за стилем. У такому випадку потрібно викликати контекстне меню стовпця, клацнувши по списку в заголовку графи. У меню є аналогічні команди, за допомогою яких можна виконати сортування в Excel.
Для того щоб числа були відсортовані правильно, зверніть увагу на правильність формату даних у стовпці. Якщо значення внесено до таблиці як текст, а не число, вибудовування пройде не в очікуваному порядку. У таблиці наведено приклад сортування чисел 10, 11, 100, 15, 110, 132, залежно від формату даних.
Упорядкування тексту
У Excel сортування стовпців, що містять текст, виконується так само, як і операція з числами. Зробимо цю команду в колонці вже відомої нам таблиці цін на кімнати в готелі «Харчування». Виділяємо стовпець кліком на його заголовку і відкриваємо панель інструментів «Редагування» вкладки «Головна». Тепер у випадаючому меню замість команд сортування за зростанням та зменшенням розташовані команди сортування від А до Я і навпаки.
Виконуємо побудову осередків від А до Я, знову ж таки вказуючи розширення діапазону операції.
Упорядковуючи рядкові дані, переконайтеся, що значення не містять початкових пробілів, оскільки в такому разі бажаний порядок осередків буде порушено. Ця проблема трапляється, якщо інформація вивантажується у файл з іншого джерела: бази даних, сторонньої програми або іншого файлу.
Для форматованих таблиць сортування Excel виконати ще простіше. Зайшовши в контекстне меню заголовка колонки, ви побачите команди побудови осередків у порядку від А до Я і навпаки.
Упорядкування дат
Усі вказані вище операції, які застосовуються для чисел і рядкової інформації, використовуються для вибудовування дат і часу. Відмінність полягає лише в найменуванні команди, для такого типу даних вона називається "Сортування від старих до нових" і навпаки. Нижче наведено приклад.
Для дат діє те саме правило, що й для чисел: щоб упорядкування пройшло коректно, переконайтеся в тому, що інформація, що сортується, представлена саме у форматі дати, а не в текстовому.
Настроювані списки
Але не завжди є можливість вибудувати необхідний список штатними засобами, буває необхідність виконати сортування в Excel за полями, до яких не підходить жоден з описаних вище методів. Наприклад, у списку справ на тиждень ми хочемо збудувати завдання по днях тижня.
Щоб виконати таке сортування, програма надає можливість використання списків, що настроюються. У MS Excel є низка попередньо встановлених списків, одним із яких є перелік днів тижня. Щоб їх подивитися, зайдіть на вкладку «Дані» та знайдіть панель інструментів сортування та фільтрації Excel. На кнопці «Сортування» відкривається вікно виконання операції кількома стовпцями.
У полі «Порядок» вибираємо елемент «Список, що настроюється». З переліку вибираємо дні тижня та натискаємо ОК.
Впорядкування за кольором та значками
Ще однією зручною опцією вибудовування елементів комірки по порядку є можливість сортування їх за кольором. При цьому може використовуватися колір заливки, так і шрифту тексту. У таблиці справ позначимо робочі завдання помаранчевим кольором, сімейні справи – блакитним, свята – бежево-жовтим. І вишикуємо їх на кшталт.
Для цього заходимо в сортування, що настроюється. Крім описаного вище способу, можна скористатися меню панелі "Редагування" вкладки "Головна". Вибираємо зі списку команд «Сортування та фільтр» елемент «Сортування, що настроюється».
Вибираємо поле «Завдання», тип «Колір комірки» та встановлюємо помаранчевий колір зверху. Щоб домашні справи знаходилися внизу списку, додаємо ще один рівень і для тих самих параметрів встановлюємо блакитний колір знизу.
Цей спосіб дозволяє впорядковувати елементи таблиці та за значками. Наприклад, ми можемо позначити окремі завдання знаком оклику як важливі і виводити їх на початку списку.
Вище ми описали, як у Excel зробити сортування даних різних типів, використовуючи колір осередків та шрифту тексту та позначки у рядках. Сподіваємося, ця інформація допоможе вам працювати з програмою.
Worksheet_Change не реагує на перерахунок формул.
Якщо діапазони, на які посилаються формули, знаходяться далеко від таблиці (або на інших аркушах) і їх не можна включити до діапазону Target, що перевіряється, можна використовувати подію перерахунку аркуша:
Private Sub Worksheet_Calculate() Application.EnableEvents = False .CurrentRegion.Sort , xlDescending, Header:=xlYes Application.EnableEvents = True End Sub
Application.EnableEvents - щоб уникнути зациклювання, на час сортування відключаємо події аркуша.
Сортування відбуватиметься під час перерахунку формул. Щоб перерахунок відбувався і при ручній зміні даних, довільну комірку потрібно додати формулу з летючою функцією (летюча - перераховується при будь-якій зміні на аркуші), наприклад:
СЬОГОДНІ()
Недолік даного рішення - сортування включається за будь-якої зміни на аркуші.
Якщо перерахунок формул відбувається не часто, можна паралельно з Worksheet_Change використовувати подію активації аркуша - Worksheet_Activate
Сортування Excel - це вбудована функція аналізу даних.За допомогою неї можна виставити прізвища в алфавітному порядку, відсортувати середній бал абітурієнтів за зростанням або зменшенням, встановити порядок рядків залежно від кольору або значка і т.д. Також за допомогою цієї функції можна швидко надати таблиці зручний вигляд, що дозволить користувачеві швидше знаходити необхідну інформацію, аналізувати її та приймати рішення.
Відео щодо використання сортування Excel
Що можна сортувати?
Excel вміє сортувати дані за текстом (в алфавітному порядку або навпаки), числами (за зростанням або за спаданням), за датою і часом (від нових до старих, і навпаки). Сортувати можна як по одному стовпцю, так і одночасно за кількома. Наприклад, спочатку можна відсортувати всіх клієнтів в алфавітному порядку, а потім за загальною сумою їх покупок. Крім того, Ексель може сортувати за списками, що налаштовуються, або за форматом (колір комірки, колір тексту і т.д.). Зазвичай сортування застосовується тільки по колонках, але можна застосовувати цю функцію і до рядків.
Всі налаштування цієї опції зберігаються разом з книгою Excel, що дозволяє повторно сортувати інформацію при відкритті книги (якщо необхідно).
Сортування даних у Excel
Сортування в Екселі умовно можна розділити на просте та складне. Простим вважається сортування за зростанням або за спаданням.
Існує 2 основних типи сортування - за зростанням та за спаданням
Отже, перед тим, як почати, необхідно відкрити Excel і заповнити якоюсь інформацією. Наприклад, можна заповнити 10 осередків цифрами від 1 до 10. Тепер потрібно виділити весь стовпець (в даному випадку – всі 10 осередків) та вибрати в панелі меню пункти «Дані – Сортування». Відкриється нове вікно, в якому треба вказати, як відсортувати інформацію, за зростанням або зменшенням. Можна, наприклад, вибрати «за спаданням» і натиснути кнопку «ОК». Тепер числа будуть йти від 10 до 1. Можна знову відкрити вікно сортування і вибрати «за зростанням» — числа будуть йти від 1 до 10. Також ця процедура може виконуватися одночасно по 3 колонках. Хоча таке сортування краще виконувати.
Наприклад, можна створити таблицю, в якій зберігатиметься інформація про товар на складі. Таблиця складатиметься з трьох колонок: найменування, колір, кількість. Товари потрібно написати так, щоб однієї й тієї категорії було кілька. Наприклад, черевики чоловічі чорні (3 моделі), черевики чоловічі червоні (2 моделі), туфлі жіночі білі (4 моделі) і т.д. Кількість може бути будь-якою.
Отже, щоб увімкнути автофільтр, необхідно виділити весь аркуш і на панелі меню вибрати пункти «Дані — Фільтр — Автофільтр». У осередках з назвами стовпців (найменування, кількість тощо) повинен з'явитися невеликий значок, при натисканні на який відкриється список, що випадає. Наявність такої стрілочки означає, що автофільтр увімкнено правильно. У цьому списку можна відсортувати дані зі спадання або зростання, вказати, щоб у таблиці відображалися лише перші 10 найменувань (у даному прикладі ця опція не спрацює) або щоб показувало якийсь певний товар (наприклад, черевики чоловічі). Також можна вибрати пункт «Умова» та вказати, наприклад, щоб програма відобразила всі товари, кількість яких менша або дорівнює 10.
Якщо стрілка автофільтра пофарбована в синій колір, це означає, що ця колонка вже була відсортована.
Хитрощі сортування
Припустимо, що користувач має таблицю, в якій присутній стовпець з назвами місяців року. І коли потрібно відсортувати його, наприклад, за зростанням, виходить приблизно так: серпень, квітень, грудень і т.д. А хотілося б, щоб сортування відбувалося звичною послідовності, тобто. січень, лютий, березень тощо. Зробити це можна за допомогою особливого налаштування за аркушем користувача.
Для цього необхідно виділити всю таблицю, відкрити вікно сортування і в полі «Порядок» вказати пункт «Список, що налаштовується». Відкриється нове вікно, де можна вибрати потрібну послідовність місяців на рік. Якщо такого списку немає (наприклад, назва місяців англійською), його можна створити самому, вибравши опцію «Новий список».
Відсортувати дані дуже легко. Зате можна отримати зручну таблицю або звіт для швидкого перегляду необхідної інформації та прийняття рішення.
Excel дозволяє аналізувати дані з різних параметрів. Вбудований елемент аналізу – сортування. Щоб елемент спрацював, інформація має бути представлена у вигляді списку, оформленого певним способом.
Дані в таблиці розподілені за рядками та стовпцями. У шпальтах – однотипні значення. Числа, дати, текст. Але не впереміш. Сортування не працюватиме за наявності порожніх рядків або стовпців.
Сортування даних у Excel
В Excel можна впорядкувати списки:
- за зростанням;
- за зменшенням;
- за заданими користувачем параметрами (щодня тижні, місяці тощо).
Відсортувати за зростанням означає впорядкувати дані:
- від 0 до 9;
- від А до Я;
- від A до Z.
За спаданням – у зворотному порядку.
Щоб викликати користувальницький порядок сортування, необхідно виконати наступну послідовність: «дані» - «сортування» - «порядок» - «список, що налаштовується». Відображається меню такого виду.
Як відсортувати дані в таблиці Excel за стовпцем?
У нас є навчальна таблиця зі списком працівників та окладом:
Відсортуємо оклад за спаданням, від більшого до меншого. Виділяємо потрібний стовпець – права кнопка миші – опція сортування – від максимального значення до мінімального значення – введення:
Діапазон сортування розширюється автоматично. Виходить так:
Як у Excel відсортувати за зростанням?
Виділяємо стовпець - права кнопка миші - на цей раз вказуємо: від мінімального значення до максимального значення.
Результат:
Як сортувати за абеткою в Excel?
Виділяємо стовпець з прізвищами - права кнопка миші - вибираємо: сортування, що налаштовується.
"Сортувати за" - вибираємо ім'я стовпця, значення якого потрібно відсортувати за абеткою. У нас – ПІБ. У сортуванні вибираємо, на які параметри орієнтуватиметься інструмент. Це може бути колір комірки, шрифт, значок комірки. У прикладі – «значення» (аналіз значень в осередку). "Порядок" - від А до Я або від Я до А. Зробимо прямий порядок. Тиснемо ОК.
Результат сортування за абеткою:
Як зробити сортування за датою в Excel?
Вихідні дані:
Нам потрібно відсортувати товар за датою надходження на склад.
Порядок дій:
Перевірити, щоб у стовпці з датами був правильний формат (Дата). Виділяємо стовпець і сортуємо від старих дат до нових (від нових до старих).
Вибираємо від «старих до нових». Значення списку розташовують у хронологічному порядку.
Відсортувати дані в діапазоні можна щодня тижня, по місяцях. Для цього в меню «Сортування, що настроюється» вибираємо «Список, що настроюється».
Сортуємо дані за змістом
Припустимо, інформацію в таблиці потрібно розподілити за посадами, військовими званнями, сертифікатами, клієнтами та товарами залежно від їх важливості для конкретного підприємства тощо. Для цих цілей призначені списки користувача, які можна складати самостійно.
У меню «Список, що налаштовується» вибираємо новий список. Формуємо ієрархію будь-яких об'єктів. Розділювачами можуть бути коми або кнопка «Введення».
Коли внесено ієрархічні об'єкти, натискаємо «Додати».
А ось результат сортування навчальної таблиці за посадами:
Сортування даних за кількома стовпцями
Якщо потрібно відсортувати інформацію за різними умовами в різних стовпцях, використовується меню «Список, що налаштовується». Там є кнопка "Додати рівень".
Дані в таблиці будуть відсортовані за кількома параметрами. В одному стовпці, наприклад, за зростанням. У іншому («потім за…») – за алфавітом тощо. В результаті діапазон буде сформований так, як поставлено складним сортуванням.
Рівні можна додавати, видаляти, копіювати, змінювати місцями за допомогою кнопок «Вгору»-«Вниз».
Це найпростіші способи сортування даних у таблицях. Можна з цією метою застосовувати формули. Для цих цілей підходять вбудовані інструменти «Найменший», «РЯДК», РАХУНКИ».
Сортувати дані в Excel можуть навіть новачки. Важко не помітити одразу три кнопки на стрічці.
Але іноді запитують, як відсортувати дані в таблиці Excel за стовпцями, кольором і т.п. Всі ці завдання легко вирішуються в Excel.
Сортування в Excel за кількома стовпцями
Сортування рядків в Excel можна здійснити за кількома шпальтами одночасно. У цьому випадку спочатку відбувається сортування за першим зазначеним стовпцем. Потім ті рядки, де осередки у відсортованому стовпці повторюються, сортуються по другому стовпцю і т.д. Таким чином, звіт про продажі можна впорядкувати спочатку по регіону, потім в рамках кожного регіону менеджера, потім в рамках одного регіону і менеджера групи товарів.
Виділивши одну комірку чи всю таблицю, викликаємо на стрічці команду сортування. Якщо потрібно, ставимо галочку Мої дані містять заголовкита додаємо необхідну кількість рівнів.
Сортування за власним списком
Більшість користувачів знає, як відсортувати рядки в Excel за алфавітом чи зростанням чисел. Але іноді потрібно розставити рядки в заданому порядку, який не обов'язково відповідає алфавіту або послідовності, що зростає. Це може бути ієрархія елементів, міста та країни у зручній послідовності тощо. Загалом те, що має власну логіку.
Найпростіший (а, отже, вартий уваги) спосіб полягає в тому, щоб у стовпці поруч проставити числа відповідно до бажаних порядку рядків, а потім здійснити сортування таблиці по цьому стовпцю.
Спосіб хороший, але потребує створення додаткового тимчасового стовпця. При частому повторенні операції буде забирати час. Якщо доводиться сортувати одні й самі дані, можна створити спеціальний список, основі якого потім і відбудеться сортування. Це той же список, який використовується в .
Заходимо в Файл – Параметри – Додатково – Загальні – Змінити списки…
Тут створюємо вручну чи імпортуємо список, відсортований у потрібному порядку.
Тепер у вікні сортування у полі Порядокпотрібно вибрати Список, що налаштовується…
І в наступному вікні вказати потрібний список.
Створений список можна використовувати і в інших файлах Excel.
Сортування в Excel за кольором комірки, шрифту, значок
У налаштуваннях сортування також можна використовувати колір комірки, шрифту та значок (з ). Якщо для форматування окремих осередків використовувати заливку (наприклад, для вказівки проблемних товарів або клієнтів), то їх легко вивести вгору таблиці за допомогою сортування.
Сортування за стовпцями
Сортувати в Excel можна і стовпчиками. Для цього виділяємо таблицю разом із назвами стовпців. Потім у вікні сортування спочатку натискаємо Параметриі ставимо перемикач на стовпці діапазону.
Наступні налаштування звичайні: задаємо рядок (!) та порядок. Єдине, що тепер не можна використовувати назви рядків (за аналогією з назвою стовпців), будуть лише номери.
Сортування проміжних підсумків
В Excel є такий інструмент, як Проміжні висновки. Коротко він потрібен для того, щоб автоматично створювати проміжні підсумки під групою однорідних за деякою ознакою осередків.
Сортування даних в Excel – це дуже корисний інструмент, який дозволяє покращувати сприйняття інформації, особливо у великих обсягах. У цьому уроці ми навчимося застосовувати сортування, дізнаємося про основні команди, а також познайомимося з типами сортування в Excel.
При додаванні даних до Excel дуже важливо грамотно організувати інформацію на робочому аркуші. Одним із інструментів, який дозволяє зробити це, є сортування. За допомогою сортування Ви можете сформувати список контактної інформації на прізвище, розмістити вміст таблиці в алфавітному порядку або в порядку зменшення.
При сортуванні даних в Excel в першу чергу необхідно вирішити, як застосовувати сортування: до всього аркуша (таблиці) або лише до певного діапазону осередків.
Як зробити сортування листа (таблиці, списку) в Excel
У наступному прикладі ми відсортуємо форму замовлення футболок Прізвищем(Стовпець С) та розташуємо їх в алфавітному порядку.
При сортуванні таблиці або списку в Excel необхідно, щоб вони були відокремлені від сторонніх даних на аркуші щонайменше одним рядком або стовпцем. Інакше у сортуванні братимуть участь сторонні дані.
Як зробити сортування діапазону в Excel
У наступному прикладі ми виберемо окрему невелику таблицю на аркуші Excel, щоб відсортувати кількість футболок, замовлених у певні дні.
Якщо сортування в Excel виконується неправильно, то в першу чергу перевірте, чи правильно введені значення. Навіть невелика друкарська помилка може призвести до проблем при сортуванні великих таблиць. У наступному прикладі ми забули поставити дефіс у осередку A18, що призвело до неточного сортування.
