Як зробити сортування в excel

Сортування даних в Excel

Якщо дані текстові, їх можна відсортувати за алфавітом ( «від А до Я» або «від Я до А»). Якщо дані числові, їх можна впорядкувати в порядку зростання або зменшення. Якщо в діапазоні даних є рядок або стовпець, в яких містяться дані типу час або дата, їх можна впорядкувати в прямому або зворотному хронологічному порядку. Є також можливість сортування попередньо відформатованих даних за елементами цього форматування.

Сортувати дані можна по одній умові (наприклад, сортування списку співробітників на прізвище) або декільком (наприклад, сортування списку співробітників по займаній посаді, а всередині кожної посади прізвища впорядкувати в алфавітному порядку). Дані можна сортувати за стовпцем (або кількох стовпців) або по рядку.

Сортування за одним критерієм

  1. У стовпці, по якому повинна бути виконана сортування, потрібно виділити будь-яку клітинку (весь стовпець виділяти не треба).
  2. на вкладці дані [Data] знайти групу команд Сортування і фільтр [Sort & Filter].

  1. Вибрати потрібну кнопку: — сортування за зростанням або сортування за спаданням.

Відзначимо, що букви на цій кнопці вказують тільки на напрямок сортування, а вид кнопки залишається один і той же і при текстових, і при числових даних.

Існує й інший зручний спосіб сортування даних: клацнувши правою кнопкою миші по комірці стовпця, по якому буде виконуватися сортування, в контекстному меню вибрати пункт Сортування [Sort], а далі — опцію сортування.

багаторівнева сортування

  1. Виділити одну клітинку з сортованого масиву даних.

Якщо діапазоні даних є порожні стовпці або рядком, то Excel автоматично сприймає їх як кордони сортованого масиву даних. В такому випадку слід виділити всі дані, що підлягають сортуванню.

  1. на вкладці дані [Data] знайти групу команд Сортування і фільтр [Sort & Filter] і на ній вибрати команду Сортування [Sort].
  2. Послідовно задати рівні сортування (визначені ім’ям стовпця).

Натискаючи на стрілку біля трьох полів (стовпець, Сортування, порядок) Необхідно вибрати:

  1. Ім’я стовпця для сортування.
  2. Тип критерію (в залежності від того, чи буде вестися сортування за значеннями даних в стовпці, або з оформлення осередки, або по значку осередки).
  3. Порядок сортування (по спадаючій або по зростанню).

Якщо обраний для сортування стовпець містить назви місяців або днів тижня, то в списку поля порядок можна вибрати опцію настроюється список і в новому вікні зазначити один із запропонованих варіантів сортування.

Сортування по форматування

Часто для аналізу даних робиться заливка комірок (або шрифту) кольором. За допомогою сортування можна також упорядкувати дані на основі їх форматування.

Покроковий порядок дій:

  1. Клацнути по будь-якого елементу з шпальти, за яким буде виконуватися сортування.
  2. на вкладці дані [Data] вибрати групу Сортування і фільтр [Sort & Filter], а потім вибрати команду Сортування [Sort].
  3. У полі стовпець [Column] вкажіть стовпець за яким буде проводитися сортування.
  4. У полі Сортування [Sort On] у спливаючому меню вибрати критерій сортування: колір осередки, колір шрифту або значок осередку.
  5. поле порядок [Order] містить два випадають списку. У першому потрібно вибрати тип критерію, а в другому — розміщення осередків, відсортованих за даним критерієм (рядок зверху [On Top] або знизу [On Bottom]).
  6. При необхідності додати ще один критерій сортування, у вікні Сортування потрібно вибрати кнопку Додати рівень.

Сортування за кольором

Можна також скористатися командою «Копіювати рівень» [Copy Level], замінивши в поле «Порядок» колишнє значення на нове.

комп’ютер плюс

Сортування в Excel застосовується для візуалізації даних і їх упорядкування, що істотно полегшує сприйняття інформації, представленої в табличній формі. Необхідність в цій функції виникає при роботі з бухгалтерськими відомостями, списками складських запасів і будівельними кошторисами.

Часто проблемою може стати питання розташування чисел від більшого до меншого або навпаки. Насправді критерії для впорядкування інформації в Ексель бувають різними: дата, час, колір осередків або тип шрифту. Найчастіше при вивченні прийомів роботи з електронними таблицями розглядаються приклади, де представлені списки співробітників або товарів, так як сортувати за алфавітом на практиці доводиться дуже часто. Розсортувати в програмі можна за двома різними параметрами.

Як відсортувати за алфавітом

Оскільки в більшості таблиць числові параметри прив’язані до якого-небудь тексту — прізвища або назвою товару то зручно розташувати за алфавітом. Розглянемо особливості роботи з цим інструментом в редакторі Еxcel на прикладі таблиці «Вміст поживних речовин у фруктах і овочах».

Для того щоб впорядкувати назви овочів і фруктів за алфавітом необхідно виділити перший стовпець, клікнувши мишкою по його заголовку. Далі потрібно перейти на панель інструментів «Редагування» і відкрити вкладку «Головна», на якій розташована спеціальна кнопка «Сортування і фільтр». Для упорядкування назв в першому стовпці від А до Я або навпаки, досить вибрати відповідну команду сортування в випадаючому меню. Якщо в таблицю Еxcel будуть додані дані про інших продуктах спрацює автоматичне сортування.

Як в Еxcel впорядкувати за зростанням значень

Просте розподіл по зростанню в програмі Ексель здійснюється аналогічним чином, що і за алфавітом. Після виділення потрібного стовпчика у верхній частині головного вікна на панелі завдань «Головна» в розділі «редагування» вибирається кнопка «Сортування і фільтр», що має додаткове меню. У списку, необхідно вибрати підходящий варіант. Коли дані в таблиці Ексель потрібно впорядкувати від більшого до меншого, їх слід впорядкувати за спаданням, в іншому випадку вибирається пункт «Сортування по зростанню».

Якщо діапазон даних складається з двох і більше стовпців, при сортуванні на екрані має з’явитися діалогове вікно для вибору подальших дій. Якщо користувачеві необхідно впорядкувати за зростанням дані в усій таблиці, то слід вказати «автоматично розширити виділений діапазон», в другому випадку дані будуть впорядковані лише в обраному стовпці.

Як зробити сортування за датою

У таблицях, де в хронологічному порядку відображаються певні операції, їх часто потрібно впорядкувати за датою проведення. Оскільки дата це поєднання цілих чисел, представлених певним чином, для правильного здійснення сортування необхідно, щоб був обраний відповідний формат осередків. Якщо формат вказано неправильно, програма не зможе розпізнати значення дат, тому впорядкувати їх не вийде.

Щоб розсортувати рядки таблиці за датою в редакторі Ексель потрібно виконати наступну послідовність дій:

  1. Виділити будь-яку клітинку в стовпці з датами, які потрібно впорядкувати.
  2. У вкладці «Головна» знайти кнопку «Сортування і фільтр», навести на неї курсор і вибрати одне із запропонованих дій в випадаючому списку. Це може бути розподіл від нових до старих, коли на початку списку будуть знаходиться найбільш пізні дати і прив’язані до них значення, або навпаки від старих до нових.

Сортування за кольором осередку і за шрифтом

У разі якщо певний діапазон табличних даних відформатований з використанням шрифту різних кольорів або заливки, користувач може розсортувати рядки за кольором, в який пофарбована частина осередків. Упорядкувати дані можна також по набору значків, при створенні яких застосовувалося умовне форматування. У будь-якому випадку це можна зробити наступним чином:

  1. Курсором миші потрібно вибрати одну з комірок з даними в потрібному стовпці.
  2. На вкладці «Головна» в функціональній групі «Редагування» слід знайти кнопку «Сортування і фільтрація» і вибрати в додатковому меню команду «настроюється, сортування».
  3. У вікні, спочатку вказати стовпець, в якому потрібно упорядкувати дані, а потім тип сортування. Це може бути колір осередки, колір шрифту або значок умовного форматування.
  4. Залежно від обраного виду сортування в групі порядок необхідно відзначити потрібний значок або відтінок заливки або шрифту.
  5. Останній параметр, який потрібно вказати — це послідовність розташування (зверху чи знизу).

Оскільки певний порядок значків або квітів в редакторі не передбачений, його потрібно створити самостійно. Для цього слід необхідно натиснути кнопку «Додати рівень» і потім повторити зазначені кроки для кожного кольору або значка окремо, виключаючи ті, які не потрібно включати в сортування,

Сортування в Excel за кількома стовпцями

Якщо виникла необхідність впорядкувати дані в редакторі Ексель за двома або більше стовпцями, слід так само, як і в попередньому випадку вибрати діапазон даних і відкрити вікно «настроюється, сортування». Далі в першій групі слід зазначити заголовок стовпця, дані в якому потрібно упорядкувати в першу чергу. Друга група залишається без змін, а в третій необхідно вказати бажаний тип сортування.

Для вказівки критеріїв сортування для другого стовпця потрібно додати ще один рівень. В результаті кількість рівнів буде відповідати числу стовпців, по яких необхідно відсортувати дані.

Динамічна сортування таблиці в MS Excel

При виконанні деяких завдань в Excel, потрібно встановити автоматичну сортування, яка передбачає наявність формул. Залежно від типу даних у використовуваному діапазоні динамічна сортування може бути задана трьома способами:

  1. Якщо інформація в комірках стовпчика представлена ​​числами, використовуються функції НАЙМЕНШИЙ і СТРОКА. Перша знаходить найменший елемент з масиву, а друга визначає порядковий номер рядка. Таким чином формується послідовність. Формула записується в такий спосіб: = НАЙМЕНШИЙ (A: A; СТРОКА (A1)).
  2. Коли в осередках міститися текст, перша формула не спрацює. Для сортування в цьому випадку доцільно застосовувати формулу: = СЧЁТЕСЛІ (A: A; "

Як прибрати сортування в Excel

Для скасування одиничної сортування діапазону даних досить натиснути кнопку «Скасувати введення» в лівому кутку екрана. Буває так, що зміни в файлі були збережені і скасувати дію неможливо. Як прибрати сортування в цьому випадку?

Якщо після складних маніпуляцій з таблицею, її потрібно буде повернути в первинний вигляд, перед виконанням сортування слід спеціально створити додатковий стовпець, в якому буде відображена нумерація рядків. Після завершення складного аналізу числових і текстових даних, представлених в таблиці, щоб скасувати всі виконані операції досить буде встановити сортування по створеному колонки.

Динамічна сортування таблиці в EXCEL

history 24 квітня 2013 р.
    Групи статей

  • зміна таблиць
  • Сортування Текстових Значний
  • Таблиці в форматі EXCEL 2007
  • Умовне форматування
  • Сортування Числових Значний

Відсортуємо формулами таблицю, що складається з 2-х стовпців. Сортування будемо виробляти по одному з стовпців таблиці (вирішимо 2 завдання: сортування таблиці по числовому і сортування по текстовому стовпчику). Формули сортування налаштуємо так, щоб при додаванні нових даних в вихідну таблицю, сортована таблиця змінювалася динамічно. Це дозволить завжди мати відсортовану таблицю без втручання користувача. Також зробимо дворівневе сортування: спочатку по числовому, потім (для повторюваних чисел) — по текстовому стовпчику.

Нехай є таблиця, що складається з 2-х стовпців. Один стовпець — текстовий: список фруктів ; а другий — числовий Об’єм продажу (див. файл прикладу).

Задача1 (Сортування таблиці по числовому стовпці)

Необхідно впорядкувати рядки таблиці по вмісту числового стовпця (по Обсягом продажів). Передбачається, що користувач постійно заповнює рядки таблиці, тому необхідно написати формули з урахуванням додаються значень.

Для наочності величини значень в стовпці Об’єм продажу виділені за допомогою умовного форматування (Головна / Стилі / Умовне форматування / Гістограми). Також жовтим виділені повторювані значення.

рішення1

Якщо числовий стовпець гарантовано не містить повторюваних значень, то завдання вирішується легко:

  • Числовий стовпець впорядкувати функцією НАЙБІЛЬШИЙ () (див. статтю Сортований список (числове значення));
  • Функцією ВПР () або зв’язкою функцій ІНДЕКС () + ПОИСКПОЗ () вибрати значення з текстового стовпчика за відповідним йому числовим значенням.

Однак, в реальних задачах числової стовпець може містити повтори, а так як функція ВПР () в разі наявності повторів завжди вибирає тільки перше значення зверху (див. статтю Функція ВПР () в MS EXCEL), то цей підхід не годиться (назви Фруктів будуть виведені неправильно).

Тому механізм сортування доведеться реалізовувати по іншому.

Створимо для зручності 2 динамічних діапазону фрукти і продажі , які будуть посилатися на діапазони осередків, що містять значення у шпальтах вихідної таблиці. При додаванні нових рядків у таблицю, межі цих динамічних діапазонів будуть автоматично розширюватися.

У стовпчиках D і E розмістимо таблицю, яка буде динамічно сортуватися,

В осередку Е7 запишемо зубодробильну формулу масиву:

= ІНДЕКС (Продажі; ОКРУГЛ (ОСТАТ (НАЙБІЛЬШИЙ (- (СЧЁТЕСЛІ (Продажі; "СЧЁТЕСЛІ (Продажі;" повертає масив <4:5:0:2:7:1:3:5>. Це означає, що число 64 (з осередку B7 вихідної таблиці, т.е. перше число з діапазону продажі ) Більше 4-х значень з того ж діапазону; число 74 (з осередку B8 вихідної таблиці, т.е. друге число з діапазону продажі ) Більше 5-и значний з того ж діапазону; наступне число 23 — найменше (воно нікого не більше) і т.д.

  • Тепер вищевказаний масив цілих чисел перетворимо в масив чисел з дробовою частиною, де в якості дробової частини міститиметься номер позиції числа в масиві: <4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008>. Це реалізовано виразом & "," & ПОВТОР ( "0"; 3-ДЛСТР (СТРОКА (Продажі) -СТРОКА ($ E $ 6))) & СТРОКА (Продажі) -СТРОКА ($ E $ 6)) Саме в цій частині формули закладено обмеження про не більше 1000 рядків у вихідній таблиці (див. вище). При бажанні його можна легко змінити, але це безглуздо (див. нижче розділ про швидкість обчислень).
  • Функція НАЙБІЛЬШИЙ () сортує вищевказаний масив.
  • Функція ОСТАТ () повертає дробову частина числа, що представляє собою номери позицій / 1000, наприклад 0,005.
  • Функція ОКРУГЛ (), після множення на 1000, округлює до цілого і повертає номер позиції. Тепер всі номери позицій відповідають числам стовпчика обсяги продажів, відсортованих по спадаючій.
  • Функція ІНДЕКС () за номером позиції повертає відповідне йому число.
  • Аналогічну формулу можна написати для виведення значень в стовпець фрукти = ІНДЕКС (Фрукти; ОКРУГЛ (. ))

    У файлі прикладу, з міркувань швидкості обчислень (див. нижче), однотипна частина формули, т.е. все, що всередині функції ОКРУГЛ (), винесена в окремий стовпець J . Тому підсумкові формули в сортованого таблиці виглядають так: = ІНДЕКС (Фрукти; J7) і = ІНДЕКС (Продажі; J7)

    Також, змінивши в формулі масиву функцію НАЙБІЛЬШИЙ () на НАЙМЕНШИЙ () отримаємо сортування за зростанням.

    Для наочності, величини значень в стовпці Об’єм продажу виділені за допомогою умовного форматування (Головна / Стилі / Умовне форматування / Гістограми). Як видно, сортування працює.

    тестуємо

    Тепер додамо новий рядок в вихідну таблицю. У динамічно сортируемих таблицях ми повинні отримати відповідну сортування.

    1. У осередок А15 вихідної таблиці введіть слово морква ; 2. У осередок В15 введіть Об’єм продажу Моркви = 25; 3. Після введення значень, в шпальтах D і Е автоматично буде відображена відсортована по спадаючій таблиця; 4. У сортованого таблиці новий рядок буде відображена передостанній.

    Швидкість обчислень формул

    На "середньому" по продуктивності комп’ютері перерахунок пари таких формул масиву, розташованих в 100 рядках, практично не помітний. Для таблиць з 300 рядками час перерахунку займає 2-3 секунди, що викликає незручності. Або необхідно відключити автоматичний перерахунок листа (Формули / Обчислення / Параметри обчислення) і періодично натискати клавішу F9 , або відмовитися від використання формул масиву, замінивши їх стовпцями з відповідними формулами, або взагалі відмовитися від динамічної сортування на користь використання стандартних підходів (див. Наступного розділ).

    Альтернативні підходи до сортування таблиць

    Відсортуємо рядки вихідної таблиці за допомогою стандартного фільтра (виділіть заголовки вихідної таблиці і натисніть CTRL + SHIFT + L ). У випадаючому списку виберіть необхідну сортування.

    Отримаємо ідентичний нашому варіант таблиці, але при додаванні в таблицю нових значень доведеться застосовувати фільтр заново.

    Також можна скористатися інструментом Сортування (Дані / Сортування і Фільтр / Сортування). Для цього потрібно виділити всі значення вихідної таблиці не вмикаючи заголовок, викликати інструмент Сортування, вибрати стовпець, по якому потрібно сортувати і варіант сортування.

    Отримаємо ідентичний нашому варіант таблиці, але при додаванні нових значень також доведеться застосовувати фільтр заново.

    При використанні таблиць в форматі EXCEL2007 ми також не отримаємо динамічної сортування. Нове значення (Картопля) залишиться останнім в початковій таблиці (до примусової сортування таблиці через фільтр), не дивлячись на його значення продажів (200).

    Який варіант краще — як завжди — вибирати розробнику.

    Ще одна формула масиву (+ додатковий стовпець). Задача1.1

    Сортування таблиці можна зробити за допомогою іншого, більш простої формули масиву, але нам знадобиться додатковий (службовий) стовпець D (див. файл прикладу лист Приклад 2):

    У стовпці F міститься відсортоване стовпець В (обсяг продажів). Формула повертає позицію значення обсягу продажів. Наприклад, число 86 знаходиться в 5-му рядку таблиці.

    Для повторів вираз ЯКЩО (F8 = $ B $ 7: $ B $ 14; СТРОКА ($ B $ 7: $ B $ 14) -СТРОКА ($ B $ 6); 0) буде повертати кілька значень: <0:2:0:0:0:0:0:8>, т.е. число 74 знаходиться в рядках 2 і 8.

    За допомогою функції НАЙБІЛЬШИЙ () спочатку виводиться 2, потім 8 (в різних рядках).

    Ця формула більш наочна, ніж розглянута вище на початку статті, але вимагає наявності додаткового стовпця.

    Задача2 (Сортування таблиці по текстовому стовпчику)

    Відсортуємо рядки таблиці по вмісту Текстового шпальти (по Фруктам).

    Примітка : Про сортування списку текстових значень можна прочитати в статті Сортований список в MS EXCEL (текстові значення)

    Як і в попередній задачі припустимо, що в стовпці, по якому ведеться сортування є повтори (назви Фруктів повторюються).

    Для сортування таблиці доведеться створити 2 службових шпальти (D і E).

    Ця формула є аналогом рангу для текстових значень (позиція значення щодо інших значень списку). Текстовому значенням, розташованому нижче за алфавітом, відповідає більший "ранг". Наприклад, значенням Яблука відповідає максимальний "ранг" 7 (з урахуванням повторів).

    У стовпці E введемо звичайну формулу:

    Ця формула враховує повтори текстових значень і коригує "ранг". Тепер різним значенням Яблука відповідають різні "ранги" — 7 і 8. Це дозволяє вивести список відсортованих значень. Для цього використовуйте формулу (стовпець G):

    Аналогічна формула виведе відповідний обсяг продажів (стовпець Н).

    завдання 2.1 (Дворівнева сортування)

    Тепер знову відсортуємо вихідну таблицю по Обсягом продажів. Але тепер для значень, що повторюються (в стовпці А три значення 74), відповідні значення виведемо в алфавітному порядку.

    Для цього скористаємося результатами Завдання 1.1 і Завдання 2.

    Подробиці у файлі прикладу на аркуші Задача2.

    Ссылка на основную публикацию