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

Мал. 1. Сортування по полю Замовник: (А) за замовчуванням - від А до Я; (Б) в порядку зменшення доходу; (В) порядок сортування по полю Замовникне змінився при додаванні поля сектор

Завантажити замітку в форматі або, приклади в форматі

Сортування замовників в порядку убування доходу

Щоб відсортувати рядки зведеної таблиці в порядку убування доходу, виберіть будь-яку осередок шпальти Сума по полю Дохід, Наприклад, Е4 (але не заголовок), і клацніть на значку ЯА, Що знаходиться на вкладці дані(Рис. 2). Подібна сортування нагадує стандартну, але це лише зовнішню схожість. При виконанні сортування зведеної таблиці Excel створює правило, яке буде працювати і після внесення додаткових змін до зведеної таблиці.

На прикладі зведеної таблиці, що знаходиться в шпальтах G: I (рис. 1в), видно, що станеться після додавання нового зовнішнього полярядки сектор. Зведена таблиця продовжує сортувати дані в порядку убування доходу всередині кожного сектора. Наприклад, в секторі виробництвона першому місці знаходиться компанія General Motors з доходом 750 163 долара. За нею йде компанія Ford з доходом 622 794 долари. Якщо навіть видалити поле Замовникзі зведеної таблиці, виконати додаткові налаштування і повернути це поле назад, але вже в область стовпців, Excel запам'ятає сортування замовників в порядку зменшення доходу.

Щоб у зведеній таблиці, що знаходиться в шпальтах G: I (рис. 1в), сектори також були відсортовані в порядку убування доходу, можна піти одним з трьох способів:

• Виділіть клітинку G4, клацніть правою кнопкою миші і виберіть Згорнути всеполе, Щоб приховати всі елементи, які відносяться до замовника. Після того як на екрані будуть відображатися лише одні сектори, виділіть осередок I4 і клацніть на значку ЯА на вкладці данідля виконання сортування по спадаючій. Таким чином, буде створено правило сортування для поля сектор. Повторно виділіть осередок G4, клацніть правою кнопкою миші і виберіть Розгорнути всеполе.
• Тимчасово видаліть поле Замовникзі зведеної таблиці, відсортуйте таблицю по спадаючій доходу (методом, який був описаний для рис. 2), а потім знову поверніть поле Замовник.
• Скористайтеся можливостями команди додатковіпараметрисортування(Я користуюся саме цим методом). Щоб викликати команду: (а) виділіть осередок G4, клацніть правою кнопкою миші і виберіть Сортування → додатковіпараметрисортування(Рис. 3) або (б) клікніть на значку трикутника в поле сектор, А потім виберіть пункт додатковіпараметрисортування(Рис. 4). В обох випадках відкриється вікно Сортування(Рис. 5). Виберіть пункт за зменшеннямі виберіть рядок Сума по полю Дохід.

Мал. 3. Виклик команди додатковіпараметрисортуванняправою кнопкою миші

Мал. 4. Виклик команди додатковіпараметрисортуванняза допомогою меню Сортування і фільтриполя сектор

Мал. 5. Налаштування параметрів у вікні сектор

У лівому нижньому кутку діалогового вікна Сортуваннязнаходиться кнопка Додатково ... Після клацання на цій кнопці на екрані з'явиться діалогове вікно . В цьому вікні можна: (а) задати користувальницький список, який буде використовуватися для сортування по першому ключу (докладніше див. Нижче); (Б) замість стовпчика загальний висновокв якості базового стовпчика сортування вибрати інший стовпець.

Наприклад, для зведеної таблиці, зображеної на рис. 6 можна задати сортування не по загальному доходу, а по доходу від продажу одного виду товарів, наприклад, пристроїв(Зверніть увагу, що замовники відсортовані за стовпцем F, а по стовпчику С).

Мал. 6. Додаткові параметри дозволяють впорядкувати замовників не по загальному доходу, а по доходу від продажу товару Пристрій

Щоб виконати таке сортування:

1. Розкрийте список Замовник, який перебуває в осередку А4.
2. Виберіть параметр Додаткові параметри сортування.
3. У діалоговому вікні Сортування (Замовник)клацніть на кнопці додатково…
4. У діалоговому вікні Додаткові параметри сортування (Замовник)виберіть розділ Порядок сортуванняі встановіть перемикач Значення в виділеному стовпці.
5. Клацніть в полі посилання, а потім виберіть осередок С5. Зверніть увагу на те, що потрібно клацнути в одній з комірок значень Пристрій, Оскільки на заголовку Пристрійв осередку С4 клацнути неможливо.
6. Щоб завершити установку параметрів двічі клікніть ОK.

Не лякайтеся, опис цього покрокового алгоритму приведено, скоріше, в навчальних цілях. Починаючи з Excel 2013 сортування даних зведеної таблиці істотно спростилася. Тепер кнопки ЯА і АЯ на вкладці данівикористовують інтелектуальні алгоритми сортування. При спробі виконати сортування за допомогою цих кнопок програма спробує вгадати наміри користувача, грунтуючись на тому, яка осередок була виділена перед натисканням кнопки сортування (рис. 7):

• А1, С1, D1, Е1, F1, F2, А30, F30 - не доступні
• А2: А29 - розташує за алфавітом імена замовників в стовпці А
• В1, В2, С2, D2, E2 - розташує за алфавітом назви товарів в рядку 2
• В30, С30, D30, E30 - розташує по спадаючій (зростанню) суми доходу в рядку 30
• по зростанню (зменшенням) продажів В3: В29 - модулів, С3: С29 - пристроїв, D3: D29 - деталей, Е3: Е29 - препаратів, F3: F29 - разом.

Сортування вручну

Зверніть увагу на те, що в діалоговому вікні Сортування(Див. Рис. 5) можна вручну визначити правила сортування даних. Але сортування зведеної таблиці вручну також виконується іншим, вельми незвичайним способом. У звіті зведеної таблиці на рис. 8а показана послідовність категорій товарів, відсортованих в алфавітному порядку: Деталь, Модуль, Препараті Пристрій. Зверніть увагу на те, що обсяг проданих товарів, що відносяться до категорії деталь, Які не найбільший. І навряд чи варто цю категорію відображати першої. Встановіть покажчик миші в осередку Е4 і введіть слово деталь. Варто лише натиснути клавішу Enter, Як Excel визначить, що ви вирішили перемістити колонку детальв останній стовпець таблиці. Всі числові значення, які стосуються цієї категорії товарів, перемістяться з шпальти В в стовпець Е. Значення, що відносяться до інших категорій товарів, змістяться вліво. Подібна поведінка виглядає нелогічним і притаманне лише зведених таблицях Excel. Звичайний набір даних Excel переупорядочить таким чином не вдасться. На рис. 8б показана зведена таблиця після переміщення заголовка нового стовпця в клітинку Е4.

Мал. 8. Сортування вручну: (а) категорії товарів відсортовані за алфавітом, (б) категорія Деталь розміщена останньої

Любителі миші можуть просто перетягувати заголовки необхідних колонок (або окремі рядки). Клацніть в області заголовка стовпця і утримуйте курсор миші над межею діапазону виділених осередків до тих пір, поки він не придбає вид четирехнаправленной стрілки. Починайте перетягувати осередок в вибране місце; з'явиться покажчик у вигляді жирної лінії і зарубками. Як тільки ви відпустите кнопку миші, числові значення тут же перемістяться в нову колонку. Врахуйте, що при використанні ручного сортування товари, додаються в джерело даних, додаються в кінець списку. Це пов'язано з тим, що програма Excel не знає, куди саме потрібно додати новий регіон.

Сортування даних відповідно до призначеним для користувача списками

Ще одне рішення проблеми, пов'язаної з налаштуванням послідовності уявлення полів, полягає в створенні власних списків. За допомогою подібного списку будуть сортуватися зведені таблиці, що створюються в подальшому. За замовчуванням Excelсодержіт чотири призначених для користувача списку: для днів тижня, місяців року і скорочених назв днів тижня і місяців року. Програма сортує назви днів тижня в природній послідовності, починаючи з Пн і закінчуючи Нд (а не за абеткою).

Щоб створити власний список сортування, виконайте наступні дії:

1. У вільній від даних області робочого аркуша введіть назви категорій товарів в послідовності, яка відповідає створюваному користувацького списку. У кожному осередку вводите по одній назві, а назви розміщуйте в одному стовпці (рис. 9).
2. Виділіть отриманий список назв категорій товарів (осередки А10: А13).
3. Виберіть вкладку стрічки файлі в нижній частині панелі навігації, відображеної у вікні зліва, клацніть на кнопці параметридля відкриття діалогового вікна Параметри Excel.
4. Оберіть категорію додатково, Перейдіть в розділ загальніі клацніть на кнопці змінити списки.
5. У діалоговому вікні спискиадреса діапазону, що містить попередньо виділений список назв, відображається в полі Імпорт списку з осередків(Рис. 10). Клацніть на кнопці імпорт, Щоб сформувати новий список категорій товарів на основі зазначених даних. Новий список додається в нижню частину області списки.
6. Клацніть на кнопці ОК, щоб закрити діалогове вікно списки. Клацніть ще раз на кнопці ОК для закриття діалогового вікна параметри Excel.

Мал. 10. Вікно списки

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

Щоб впорядкувати раніше створені зведені таблиці відповідно до новим призначеним для користувача списком, виконайте наступні дії:

1. Розкрийте список поля товарі виберіть параметр Додаткові параметри сортування.
2. У діалоговому вікні Сортування (Товар)виберіть кнопку по зростанню (від А до Я) по полю, А в списку виберіть товар.
3. Клацніть на кнопці додатково…
4. У діалоговому вікні Додаткові параметри сортування (Товар)скасуйте установку прапорця автосортування.
5. Розкрийте список Сортування по першому ключуі виберіть список, що включає назви категорій товару (рис. 12).
6. Двічі клацніть на кнопці ОК.

Замітка написана на основі книги Білл Джела, Майкл Александер. . Глава 4.

Припустимо, що ми підготували звіт по продажах і фінансовими результатамипо каналах продажів і менеджерам такого вигляду:

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

Відразу відзначимо 2 важливих моменти.

1. Як правило, таблиці такого роду створюються за допомогою формул. До проведення сортування потрібно обов'язково прибрати всі формули з усіх осередків, Інакше провести операцію просто не вдасться, а якщо і вдасться, то все значення зміняться, тому що формули будуть посилатися на зовсім інші осередки, ніж повинні.

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

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

Для того, щоб одночасно прибрати все формули потрібно:

1. Виділити всю область листа, для чого необхідно натиснути лівою клавішею миші на верхній лівий кут сірого поля:

2. Скопіювати виділену область (не знімаючи виділення області, правою кнопкою миші викликаємо меню і вибираємо «Копіювати»)

3. Скористатися спеціальною вставкою (не знімаючи виділення області, правою кнопкою миші викликаємо меню і вибираємо «Спеціальна вставка» -> «значення»)

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

Перш ніж приступити безпосередньо до сортування, потрібно перевірити, щоб сортируемое таблиця не містила об'єднаних осередків в шапці або іншому місці. В такому випадку Excel буде лаятися і писати: «Для цього потрібно, щоб осередки мали однаковий розмір».

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

Наприклад, ми хочемо впорядкувати таблицю за спаданням виручки. Виділяємо сортовані рядки по сірому полю без шапки і натискаємо на «Сортування по спадаючій»:

УВАГА: Це стосується тільки об'єднаних осередків в шапці. Якщо всередині таблиці є об'єднані осередки, Excel немає буде сортувати такі рядки. Доведеться знімати об'єднання.

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

Для цього виділяємо всю область таблиці разом з шапкою, знаходимо в головному меню «Дані» і вибираємо «Сортування»:

У випадаючому вікні Excel запропонує зробити сортування за всіма показниками, перерахованим в шапці. Ми виберемо «Чистий прибуток»:

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

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

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

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

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

Наприклад, у запропонованій нижче таблиці слід впорядкувати співробітників за алфавітом. Стаємо в будь-яку осередок шпальти «Ім'я», і тиснемо на кнопку «Сортування і фільтр». Щоб імена упорядкувати за алфавітом, із списку вибираємо пункт «Сортування від А до Я».

Як бачимо, всі дані в таблиці розмістилися, відповідно до алфавітного списку прізвищ.

Для того, щоб виконати сортування в зворотному порядку, в тому ж меню вибираємо кнопку Сортування від Я до А ».

Список перебудовується в зворотному порядку.

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

настроюється сортування

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

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

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

В поле «Стовпець» вказуємо найменування стовпця, по якому буде виконуватися сортування. У нашому випадку, це стовпець «Ім'я». В поле «Сортування» вказується, з якого саме типу контенту буде проводитися сортування. Існує чотири варіанти:

• значення;
• Колір осередку;
• Колір шрифту;
• Значок осередки.

Але, в переважній більшості випадків, використовується пункт «Значення». Він і виставлений за умовчанням. У нашому випадку, ми теж будемо використовувати саме цей пункт.

У графі «Порядок» нам потрібно вказати, в якому порядку будуть розташовуватися дані: «Від А до Я» або навпаки. Вибираємо значення «Від А до Я».

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

З'являється ще один набір полів, який слід заповнити вже для сортування по іншому стовпцю. У нашому випадку, за стовпцем «Дата». Так як в даних осередків встановлений формат дати, то в поле «Порядок» ми встановлюємо значення не «Від А до Я», а «Від старих до нових», або «Від нових до старих».

Таким же чином, в цьому вікні можна налаштувати, при необхідності, і сортування по іншим стовпцями в порядку пріоритету. Коли всі налаштування виконані, тиснемо на кнопку «OK».

Як бачимо, тепер в нашій таблиці всі дані відсортовані, в першу чергу, по іменах співробітника, а потім, по датах виплат.

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

У вікні параметрів сортування, переводимо перемикач з позиції «Рядки діапазону» в позицію «Стовпці діапазону». Тиснемо на кнопку «OK».

Тепер, за аналогією з попереднім прикладом, можна вписувати дані для сортування. Вводимо дані, і тиснемо на кнопку «OK».

Як бачимо, після цього, стовпці помінялися місцями, згідно введеним параметрам.

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

Фільтр

Крім того, в програмі Microsoft Excel існує функція фільтра даних. Вона дозволяє залишити видимими тільки ті дані, які ви вважаєте потрібними, а решта приховати. При необхідності, приховані дані завжди можна буде повернути в видимий режим.

Щоб скористатися цією функцією, стаємо на будь-яку клітинку в таблиці (а бажано в шапці), знову тиснемо на кнопку «Сортування і фільтр» в блоці інструментів «Редагування». Але, на цей раз в меню вибираємо пункт «Фільтр». Можна також замість цих дій просто натиснути клавіші Ctrl + Shift + L.

Як бачимо, в осередках з найменуванням всіх стовпців з'явився значок у вигляді квадрата, в який вписаний перевернутий вниз трикутник.

Кількома по цьому значку в тому стовпці, за даними якого ми збираємося проводити фільтрацію. У нашому випадку, ми вирішили провести фільтрацію по імені. Наприклад, нам потрібно залишити дані лише працівника Миколаєва. Тому, знімаємо галочки з імен всіх інших працівників.

Коли процедура виконана, тиснемо на кнопку «OK».

Як бачимо, в таблиці залишилися тільки рядки з ім'ям працівника Миколаєва.

Ускладнити завдання, і залишимо в таблиці тільки дані, які відносяться до Миколаєва за III квартал 2016 року. Для цього, натискаємо по значку в осередку «Дата». У списку, знімаємо галочки з місяців «Май», "Червень" і "Жовтень", так як вони не відносяться до третього кварталу, і тиснемо на кнопку «OK».

Як бачимо, залишилися тільки потрібні нам дані.

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

Якщо ж ви хочете скинути фільтр в цілому по таблиці, тоді потрібно натиснути кнопку «Сортування і фільтр» на стрічці, і вибрати пункт «Очистити».

Якщо потрібно повністю видалити фільтр, то, як і при його запуску, в цьому ж меню слід вибрати пункт «Фільтр», або набрати комбінацію клавіш на клавіатурі Ctrl + Shift + L.

Крім того, слід зауважити, що після того, як ми включили функцію «Фільтр», то при натисканні на відповідний значок в осередках шапки таблиці, в меню стають доступні функції сортування, про які ми говорили вище: «Сортування від А до Я» , «Сортування від Я до А», і «Сортування за кольором».

розумна таблиця

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

Існує два способи створення «розумної таблиці». Для того, щоб скористатися першим з них, виділяємо всю область таблиці, і, перебуваючи у вкладці «Головна», натискаємо на кнопку на стрічці «Форматувати як таблицю». Дана кнопка знаходиться в блоці інструментів «Стилі».

Після цього, відкривається діалогове вікно, в якому можна змінити координати таблиці. Але, якщо ви раніше виділили область правильно, то більше нічого робити не потрібно. Головне, зверніть увагу, щоб близько параметра «Таблиця із заголовками» стояла галочка. Далі, просто натиснути на кнопку «OK».

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

Після цього, як і в минулий раз, відкриється вікно, де можна скорегувати координати розміщення таблиці. Тиснемо на кнопку «OK».

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

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

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

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

Типи сортируемих даних і порядок сортування

Сортування числових значень в Excel

Сортування числових значень по зростанню - це така розстановка значень, при якій значення розташовуються від найменшого до найбільшого (від мінімального до максимального).

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

Сортування текстових значень в Excel

"Сортування від А до Я" - сортування даних по зростанню;

"Сортування від Я до А" - сортування даних по спадаючій.

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

Текстові значення можуть містити алфавітні, числові і спеціальні символи. При цьому числа можуть бути збережені як в числовому, так і в текстовому форматі. Числа, збережені в числовому форматі, менше, ніж числа, збережені в текстовому форматі. Для коректної сортування текстових значень всі дані повинні бути збережені в текстовому форматі. Крім того, при вставці в осередку текстових даних з інших додатків, ці дані можуть містити пробіли в своєму початку. Перед початком сортування необхідно видалити початкові пробіли (або інші недруковані символи) з сортируемих даних, інакше сортування буде працювати коректно.

Можна впорядкувати текстові дані з урахуванням регістру. Для цього необхідно в параметрах сортування встановити прапорець в полі "Враховувати регістр".

Зазвичай літери верхнього регістру мають менші номери, ніж букви нижнього регістра.

Сортування значень дати і часу

"Сортування від старих до нових" - це сортування значень дати і часу від самого раннього значення до самого пізнього.

"Сортування від нових до старих" - це сортування значень дати і часу від самого пізнього значення до самого раннього.

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

У Microsoft Excel 2007 і вище передбачена сортування по форматування. Цей спосіб сортування використовується в тому випадку, якщо діапазон комірок відформатований із застосуванням кольору заливки осередків, кольору шрифту або набору значків. Кольори заливок і шрифтів в Excel мають свої коди, саме ці коди і використовуються при сортуванні форматів.

Сортування по настроюється списку

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

параметри сортування

Сортування по стовпцю

У будь-якій версії Excel за замовчуванням встановлена ​​сортування по стовпцю, тобто значення осередків вибраного стовпця розставляються в потрібному порядку, а рядки діапазону при цьому міняються місцями залежно від положення осередку в сортованому стовпці.Для того, щоб впорядкувати таблицю за стовпцем, досить встановити маркер виділення в будь-якому осередку таблиці і натиснути один із значків, які символізують сортування від мінімального до максимального, або від максимального до мінімального. Таблиця буде відсортована по тому колонки, в якому знаходиться маркер виділення.

Сортування по рядку

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

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

Отже, якщо проводиться сортування по стовпцю, то рядки міняються місцями, якщо дані упорядковано відповідно до рядку, то місцями змінюються стовпці.

Excel дозволяє сортувати дані не тільки по одному стовпцю або одному рядку, а створювати різну кількість рівнів сортування. В Excel 2007, наприклад, їх 64. Рівні сортування можна додавати, видаляти, копіювати і міняти місцями.

Крім усього іншого при сортуванні можна враховувати, або не враховувати регістр.

Надбудова для сортування даних в Excel

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

Для упорядкування та аналізу даних в списку застосовується команда "Сортування". "Сортування" дозволяє переставляти записи в певному порядку на підставі значень одного або декількох стовпців або переставляти стовпці на підставі значень в рядках.

Існують три типи сортування:

• в порядку зростання
• в порядку спадання
• в призначеному для користувача порядку

Сортування списку по зростанню означає впорядкування списку в порядку: від 0 до 9, прогалини, символи, літери від А до Z або від А до Я, а по спадаючій - в зворотному порядку. Призначений для користувача порядок сортування задається користувачем у вікні діалогу "Параметри" на вкладці "Списки", яке відкривається командою "Параметри" в меню "Сервіс", а відображається цей порядок сортування у вікні діалогу "Параметри сортування" (Рис. 1).

Мал. 1.

Для відображення вікон діалогу "Сортування діапазону", "Параметри сортування" і вибрати порядок сортування стовпців або рядків необхідно виділити осередок списку і виконати команду "Дані" - "Сортування". На екрані з'явиться вікно "Сортування діапазону" при натисканні в ньому кнопки "Параметри" з'явиться вікно "Параметри сортування".

У вікні "Параметри сортування" (Рис. 2) зі списку "Сортування по первинному ключу" можна вибрати призначений для користувача порядок сортування, а також встановити перемикач "Сортувати" в положення: "рядки діапазону" або "стовпці діапазону".

Мал. 2.

У вікні "Сортування діапазону" (Рис. 3) можна встановити перемикач в положення: "по зростанню" або "по спадаючій", а також вибрати положення перемикача ідентифікації діапазону даних.

Мал. 3.

Якщо підписи відформатовані відповідно до вищевикладених вимог, то перемикач за замовчуванням встановлюється в положення "підписам". Крім того, в списках: "Сортувати по", "Потім по" і "В останню чергу, по" можна вибрати заголовки стовпців, за якими здійснюється сортування. Таким чином, сортування записів можна здійснювати по одному, двох або трьох стовпцях.

Миттєва сортування записів по одному стовпцю здійснюється при виділенні в ньому осередку натисканням кнопки "Сортування по зростанню" або "Сортування по спадаючій" на стандартній панелі інструментів. В результаті редактор переставити записи відповідно до обраного порядком.

Алгоритм сортування записів по одному стовпцю наступний

• Виділіть клітинку в списку, який потрібно впорядкувати;
• Виконайте команду "Дані" - "Сортування", відкривається вікно діалогу "Сортування діапазону";
• У списку "Сортувати по" виберіть заголовок того стовпця, по якому будете здійснювати сортування;
• Виберіть тип сортування "По зростанню" або "По убуванню";

На малюнках 4 і 5 представлені фрагменти списку до сортування, і після сортування "по зростанню" по однім стовпці "№ складу".

Мал. 4.

Мал. 5.

При сортуванні списку за кількома стовпцями, рядки з однаковими значеннямив першому сортованому стовпці ( "Сортувати по"), сортуються в порядку, який визначається заголовком, зазначеним у списку "Потім по", а рядки з однаковими значеннями в перших двох сортируемих шпальтах упорядковано відповідно до заголовку, вказаною в списку "В останню чергу, за ". Іншими словами стовпці, зазначені в списках "Потім по" і "В останню чергу по" не впливають на порядок сортування в першому сортованому стовпці, а усувають неоднозначність в відсортованому списку.

Алгоритм сортування записів за двома або більше стовпцями наступний

• Виділіть клітинку в списку;
• Виберіть заголовок для сортування в списку "Сортувати по" і встановіть порядок сортування "по зростанню" або "по спадаючій";
• Відкрийте список "Потім по", встановіть заголовок іншого шпальти для сортування і задайте сортування "по зростанню" або "по спадаючій";
• Розкрийте список "В останню чергу по" і виберіть заголовок третього стовпця для сортування і вкажіть сортування "по зростанню" або "по спадаючій";
• Натисніть кнопку ОК для виконання сортування.

Алгоритм сортування даних по рядках

Для перестановки стовпців в списку необхідно виконати сортування на основі значень в рядках:

• Вкажіть осередок в сортованому списку;
• В меню "Дані" виберіть команду "Сортування";
• У вікні "Сортування діапазону" натисніть кнопку "Параметри";
• Встановіть перемикач "Сортувати" в положення "стовпці діапазону" і натисніть кнопку OK;
• У вікні "Сортування діапазону" виберіть рядки, за якими потрібно відсортувати стовпці в списках "Сортувати по", "Потім по", "В останню чергу, за".
• Натисніть кнопку ОК для виконання сортування

У деяких випадках потрібно нестандартний порядок сортування списків, наприклад сортування даних по місяцях в календарному порядку або сортування записів в порядку, створеному користувачем. Створення користувальницького порядку здійснюється у вікні "Параметри" на вкладці "Списки", яке можна викликати командою "Сервіс" - "Параметри". Призначений для користувача порядок сортування можна застосувати тільки для заголовка стовпця, зазначеного в розкриває списку "Сортувати по"

Алгоритм сортування в призначеному для користувача порядку

• Вкажіть осередок в списку;
• В меню "Дані" виберіть команду "Сортування";
• Виберіть в списку "Сортувати по" заголовок стовпця для сортування;
• Виберіть пункт "по зростанню" або "по спадаючій";
• Натисніть кнопку "Параметри";
• В розкриває списку "Порядок сортування за першим ключу" виберіть необхідний порядок сортування і натисніть кнопку OK;
• Натисніть кнопку ОК у вікні "Сортування діапазону" для завершення сортування.