Динамическая сортировка в таблице excel

Сортировка по возрастанию и убыванию

При первом нажатии кнопки направление сортировки будет возрастать (от A до Z).

Щелкните еще раз, и направление сортировки будет нисходящим (от Z до A):

Sort

• Осло
• Стокгольм
• Хельсинки
• Берлин
• Рим
• Мадрид

Пример

<ul id=»id01″>  <li>Осло</li>  <li>Стокгольм</li>  <li>Хельсинки</li>  <li>Берлин</li>  <li>Рим</li>  <li>Мадрид</li></ul><script>function sortListDir() {  var list, i, switching, b, shouldSwitch, dir, switchcount = 0;  list = document.getElementById(«id01»);  switching = true;  // Установите направление сортировки по возрастанию:  dir = «asc»  // Сделайте цикл, который будет продолжаться до тех пор, пока переключение не будет сделано:  с (switching) {    // Начните с того: переключение не выполняется:    switching = false;    b = list.getElementsByTagName(«LI»);    // Цикл через все элементы списка:    for (i = 0; i < (b.length — 1); i++) {      // Начните с того, что не должно быть никакого переключения:      shouldSwitch = false;      /* Проверьте, должен ли следующий элемент поменяться местами с текущим элементом,      на основе направления сортировки (asc или desc): */      if (dir == «asc») {        if (b.innerHTML.toLowerCase() > b.innerHTML.toLowerCase()) {          /* Если следующий элемент в алфавитном порядке ниже текущего элемента,          отметьте как переключатель и разорвать петлю: */          shouldSwitch = true;          break;        }      } else if (dir == «desc») {        if (b.innerHTML.toLowerCase() < b.innerHTML.toLowerCase()) {          /* Если следующий элемент в алфавитном порядке выше текущего элемента,          отметьте как переключатель и разорвать петлю: */          shouldSwitch= true;          break;        }      }    }    if (shouldSwitch) {      /* Если переключатель был отмечен, сделайте переключатель      и отметьте, что переключатель был сделан: */      b.parentNode.insertBefore(b, b);      switching = true;      // Каждый раз, когда переключатель сделан, увеличьте количество переключателей на 1:      switchcount ++;    } else {      /* Если переключение не было сделано и направление «asc»,      выберите направление, чтобы «desc» и запустите вновь петля. */      if (switchcount == 0 && dir == «asc») {        dir = «desc»;        switching = true;      }    }  }}</script>

Настраиваемые списки

Но не всегда есть возможность выстроить необходимый список штатными средствами, бывает необходимость выполнить сортировку в Excel по полям, к которым не подходит ни один из описанных выше методов. К примеру, в списке дел на неделю мы хотим выстроить задачи по дням недели.

Чтобы выполнить такую сортировку, программа предоставляет возможность использования настраиваемых списков. В MS Excel есть ряд предустановленных списков, одним из которых и является перечень дней недели. Чтобы их посмотреть, зайдите на вкладку «Данные» и найдите панель инструментов сортировки и фильтрации Excel. По кнопке «Сортировка» открывается окно выполнения операции по нескольким столбцам.

В поле «Порядок» выбираем элемент «Настраиваемый список». Из перечня выбираем дни недели и нажимаем ОК.

Упорядочивание чисел

Сортировка чисел по возрастанию или убыванию – распространенная задача. Она помогает выстроить числовые параметры – количество товаров и клиентов, суммы продаж и доходов, и представить информацию в более наглядном виде. Сделаем в Excel сортировку этих данных.

Например, в таблице файла туристической компании хранится информация о стоимости проживания в отеле. Цена зависит от типа номера и предлагаемого питания. Турист заинтересован в поиске наиболее экономного размещения, поэтому нужно отсортировать данные по возрастанию цены.

Для этого выделяем столбец таблицы «Стоимость, руб.» и заходим на вкладку «Главная». В блоке «Редактирование» находится группа инструментов «Сортировка и фильтр». Выбираем из меню пункт «Сортировка по возрастанию».

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

Выбираем «Расширить», так как мы заинтересованы в упорядочивании полных записей таблицы, и нажимаем «Сортировка». Записи выстроены в порядке возрастания стоимости проживания.

Эта операция выполняется еще проще, если таблица отформатирована по стилю. В таком случае нужно вызвать контекстное меню столбца, щелкнув по выпадающему списку в заголовке графы. В меню находятся аналогичные команды, с помощью которых можно выполнить сортировку в Excel.

Для того чтобы числа были отсортированы верно, обратите внимание на корректность формата данных в столбце. Если значения внесены в таблицу как текст, а не число, выстраивание пройдет не в ожидаемом порядке

В таблице приведен пример сортировки чисел 10, 11, 100, 15, 110, 132 в зависимости от формата данных.

Сортировка в Excel

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

• Первыми идут цифры от меньшего к большему;
• Затем идут специальные символы ($, %, * и т.д.);
• За ними следуют знаки препинания;
• Далее начинаются алфавиты, сначала английский, затем русский. Также не учитывается регистр, т.е. заглавные и прописные буквы равны друг другу (А=а).

Для того чтобы выполнить упорядочивание, пройдите на вкладку «Данные» и выберите раздел «Сортировка и фильтр». Для сортировки предназначено 3 значка:

Сортировка по возрастанию (от А до Я), по убыванию (от Я до А) и значок сортировки для задания условий по нескольким столбцам одновременно.

Рассмотрим все пункты подробнее.

Для того чтобы в эксель отсортировать диапазон ячеек, предварительно выделите его, затем кликните по необходимому значку. Учтите, что самая верхняя ячейка в выделенном диапазоне будет воспринята как шапка таблицы, т.е. отсортирована не будет и останется на прежнем месте. Также, если рядом с выделенным диапазоном имеются другие заполненные ячейки, то программой будет выведено следующее окно:

Если Вами будет выбран первый вариант, то строки таблицы сохранят свою целостность. Если второй, то данные вне диапазона сортировки останутся нетронутыми. На рисунках приведены результаты до и после сортировки для двух вариантов.

Установка порядка первого столбца таблицы при выборе первого варианта:

Как видно строки сохранили свою целостность, и имена по-прежнему соответствуют своим фамилиям.

Те же самые действия, но при выборе второго варианта:

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

Выбирайте сразу необходимый диапазон для сортировки, чтобы Excel не предлагал Вам автоматически его расширить. Столбец, с которого Вы начнете выделение диапазона, будет являться столбцом для условий сортировки.

Если возникла необходимость задать порядок по нескольким столбцам, то в этом поможет значок «Сортировка», который уже упоминался. Выделите диапазон, который необходимо упорядочить

В данном случае неважно, с какого столбца он начнет выделяться, т.к. условия и столбцы будут задаваться вручную

Для примера будем использовать следующую таблицу:

Мы специально будем использовать такие данные, чтобы наглядно отобразить, как Excel осуществляет сортировку.

Выделите диапазон нашей таблицы и кликните по значку «Сорировка». Появится следующее окно:

Изначально первый уровень сортировки уже добавлен. В выпадающем списке выберите нужный столбец. В качестве его названия служит верхняя («шапка»).

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

Следующий выпадающий список задает порядок: по возрастанию, по убыванию и т.п.

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

Для нашего примера используем трехуровневую сортировку. Для первого уровня зададим сортировку по «Столбец1», порядок «От Я до А». Для второго уровня зададим сортировку по «Столбец2», порядок «По возрастанию». Для третьего уровня по «Столбец3», порядок «По убыванию». Параметры для сортировки везде «значения».

Рассмотрим получившийся результат:

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

Таким образом, можно задавать сортировку в Excel по значительному количеству столбцов.

Также в окне сортировки можно задавать различные параметры:

• Поставив флажок на поле «Мои данные содержать заголовки», программа учтет, что верхние ячейки диапазонов упорядочивать не нужно;
• Нажав кнопку «Параметры» Вы получите окно, в котором можно задать:

1. Возможность учета регистра (заглавные символы имеют больший порядковый номер по сравнению с прописными);
2. Выбрать сортировку по строкам или по столбцам.

Как видите, пользоваться сортировкой в Excel совсем не трудно, но это важный инструмент, который часто требуется при работе с данными.

Как убрать формулы из всех ячеек листа

Для того, чтобы одновременно убрать все формулы нужно:

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

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

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

Ну, а теперь можно удалить или скрыть лишние столбцы, не боясь изменения отчета.

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

Если Вы не намерены снимать объединение ячеек шапки и менять таблицу, можно перенести сортируемый столбик в левый крайний ряд и отсортировать его с помощью кнопочек сортировки.

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

только объединенных ячеек в шапке

Если же таблица не содержит объединенных ячеек, можно провести сортировку непосредственно внутри таблицы без переноса сортируемого столбца.

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

В выпавшем окне Excel предложит сделать сортировку по всем показателям, перечисленным в шапке. Мы выберем «Чистую прибыль»:

С помощью полученного рейтинга мы наглядно сможем видеть, какие менеджеры и клиенты приносят компании больше прибыли.

Если же мы совместим данный вид сортировки с сортировкой через автофильтр, мы сможем отсортировать данные по отделам, менеджерам и клиентам. Добавим столбец для сортировки и отсортируем данные по менеджерам:

Как видим, в результате у нас получился удобный отчет, который очень наглядно демонстрирует, кто из менеджеров приносит больше всего прибыли, а кто работает с нерентабельными клиентами.

Создание функции сортировки

Пример

<ul id=»id01″>  <li>Осло</li>  <li>Стокгольм</li>  <li>Хельсинки</li>  <li>Берлин</li>  <li>Рим</li>  <li>Мадрид</li></ul><script>function sortList() {  var list, i, switching, b, shouldSwitch;  list = document.getElementById(«id01»);  switching = true;  /* Сделайте цикл, который будет продолжаться до тех пор, пока  никакого переключения не было сделано: */  while (switching) {    // Начните со слов: переключение не выполняется:    switching = false;    b = list.getElementsByTagName(«LI»);    // Цикл по всем элементам списка:    for (i = 0; i < (b.length — 1); i++) {      // Начните с того, что не должно быть никакого переключения:      shouldSwitch = false;      /* Проверьте, должен ли следующий элемент      переключение с текущего пункт:*/      if (b.innerHTML.toLowerCase() > b.innerHTML.toLowerCase()) {        /* Если следующий элемент в алфавитном порядке ниже текущего элемента,        отметьте как переключатель и разорвать петлю: */        shouldSwitch = true;        break;      }    }    if (shouldSwitch) {      /* Если переключатель был отмечен, сделайте переключатель      и отметьте переключатель как сделать: */      b.parentNode.insertBefore(b, b);      switching = true;    }  }}</script>

Сортировка строк по столбцу в Excel

Ниже на рисунке представлена выборка информации из базы данных по сотрудникам фирмы:

Допустим нам необходимо отсортировать таблицу относительно столбца «Премия» по убыванию сумм. Для решения этой задачи воспользуемся самыми простыми (базовыми) инструментами сортировки в Excel.

Перейдите курсором клавиатуры на любую ячейку в столбце G (можно даже на заголовок столбца G1). А Потом выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка по убыванию».

В результате данные упорядочились и организовались в определенную структуру:

Как оказалось, наибольшую премию получил сотрудник «Евгений Пруткий».

Внимание! Список, который будет сортирован не должен содержать объединенных ячеек. Примечание

Инструмент для базовой сортировки диапазонов доступен так же на закладке: «ДАННЫЕ»-«Сортировка по возрастанию/по убыванию»

Примечание. Инструмент для базовой сортировки диапазонов доступен так же на закладке: «ДАННЫЕ»-«Сортировка по возрастанию/по убыванию».

И встроен в выпадающие меню заголовков таблиц в режиме автофильтра или умной таблицы:

Так же встроен в контекстное меню, которое появляется по нажатию правой кнопкой мышки:

Как убрать сортировку в Excel

Если таблицу необходимо будет привести в изначальный вид, тогда перед каждой сортировкой создавайте столбец нумерации строк. В данном примере – это столбец А (№п/п). Часто необходимо привести таблицу в изначальный вид после выполнения с ней многочисленных и сложных сортирований данных по разным столбцам, критериям или условиям. Тогда достаточно выполнить сортировку по столбцу с номерами строк, чтобы таблица приняла свой первоначальный вид.

Как в Excel сделать сортировку в столбце

Иногда нужно выполнить независимую сортировку только лишь внутри одного столбца таблицы без привязки к другим столбцам или их изменения. Например, столбец B «Имя». Такая задача решается так же просто:

1. Выделите целый столбец B щелкнув левой мышкой по заголовку столбца листа.
2. Выберите инструмент: «ГЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Сортировка по убыванию».
3. В появившемся диалоговом окне «Обнаруженные данные в неуказанного диапазона», выберите опцию: «сортировать в пределах указанного выделения». И нажмите на кнопку «Сортировка».

Обратите внимание на то, что значения ячеек в остальных столбцах остались без изменений:

Такой способ необходимо применять только как вспомогательный.

Динамическая сортировка таблицы в MS Excel

Если применить к таблице стандартную сортировку, то при изменении данных она не будет актуальной. Нужно сделать так, чтобы значения сортировались автоматически. Используем формулы.

1. Есть набор простых чисел, которые нужно отсортировать по возрастанию.
2. Ставим курсор в соседнюю ячейку и вводим формулу: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)). Именно так. В качестве диапазона указываем весь столбец. А в качестве коэффициента – функцию СТРОКА со ссылкой на первую ячейку.
3. Изменим в исходном диапазоне цифру 7 на 25 – «сортировка» по возрастанию тоже изменится.

Если необходимо сделать динамическую сортировку по убыванию, используем функцию НАИБОЛЬШИЙ.

Для динамической сортировки текстовых значений понадобятся формулы массива.

1. Исходные данные – перечень неких названий в произвольном порядке. В нашем примере – список фруктов.
2. Выделяем столбец и даем ему имя «Фрукты». Для этого в поле имен, что находится возле строки формул вводим нужное нам имя для присвоения его к выделенному диапазону ячеек.
3. В соседней ячейке (в примере – в В5) пишем формулу: Так как перед нами формула массива, нажимаем сочетание Ctrl + Shift + Enter. Размножаем формулу на весь столбец.
4. Если в исходный столбец будут добавляться строки, то вводим чуть модифицированную формулу: Добавим в диапазон «фрукты» еще одно значение «помело» и проверим:

Впоследствии при добавлении данных в таблицу процесс сортирования будет выполняться автоматически.

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

Анализ проделанной работы, объёмов продаж, роста прибыли, успеваемости учащихся, закупки дополнительных материалов сопровождаются выделением параметров, имеющих максимальный и минимальный показатели. Конечно, если таблица небольшая, то пользователь сумеет просто так отыскать лучший показатель. Но в тех случаях, когда Excel имеет чрезмерно большое количество строк и столбцов, без применения встроенных функций, позволяющих отсортировать таблицу, найти желаемый показатель можно, но придётся потратить большое количество рабочего времени.

Можно поступить гораздо практичнее, ознакомиться с информацией, как сортировать в Excel, и сразу приступить к практическому закреплению полученных знаний.

Фильтр по возрастанию и убыванию

Отсортировать данные по возрастанию или убыванию совсем просто. Необходимо только выяснить, сопровождается ли таблица многочисленными формулами. Если это так, то лучше всего перед тем, как сортировать данные, таблицу перенести на новый лист, что позволит избежать нарушений в формулах или случайного разрыва ссылок.

Кроме этого, запасной вариант таблицы никогда не помешает, поскольку иногда, запутавшись в собственных рассуждениях, желая вернуться к первоначальному варианту, осуществить это будет трудно, если не будет создана предварительная копия.

Итак, первоначально необходимо выделить таблицу, подлежащую проведению анализа. Далее следует перейти на новый лист, кликнуть правой кнопкой мыши, а затем кликнуть по строке «Специальная вставка». Перед пользователем на экране появится окно с параметрами, среди которых нужно выбрать параметр «Значение», после чего нажать «Ок».

Теперь дублирующий вариант создан, поэтому можно приступать к дальнейшим действиям. Чтобы полностью понять, как отсортировать таблицу в Excel по возрастанию, необходимо выделить вновь всю таблицу, затем перейти во вкладку «Данные», там среди нескольких инструментов будет находиться и желаемый «Сортировка», по которому нужно кликнуть.

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

Если необходимо осуществить сортировку не во всей таблице, а только в одном столбце, действия будут практически такими же, за исключением только двух моментов. Первоначально следует выделить не всю таблицу, а лишь желаемый столбик, а впоследствии, когда Excel предложит автоматически расширить диапазон, чтобы осуществить сортировку, нужно отказаться от этого, установив галочку рядом с фразой «Сортировать в пределах указанного диапазона».

Как сделать сортировку списка в excel?

Сортировку можно выполнить несколькими способами: по одному признаку, по нескольким. А можно создать свое правило сортировки.

Начнем с сортировки по одному признаку. Программа предлагает сортировку по возрастанию и по убыванию.

Шаг 1.

Выделите ячейку того столбика, который вы хотите сортировать.

Шаг 2.

Щелкните левой кнопкой мыши на уже знакомой вам кнопке «Сортировка и фильтр». («Как отфильтровать таблицу по одному признаку»)

В появившемся меню выберите нужную команду — «Сортировка от А до Я» или «Сортировка от Я до А» — и щелкните на ней левой кнопкой мыши.

Вот пример сортировки (от А до Я) по столбику с фамилиями:

А вот – по дате приема на работу (от Я до А):

Теперь – сортировка по нескольким признакам.

Задача такая: выполнить сортировку сначала по фамилиям (расположить их по алфавиту), а затем – по годовому фонду заработной платы (расположить по убыванию).

Шаг 1.

Выделите ячейку вашей таблицы, которую нужно отсортировать.

Шаг 2.

Снова нажмите кнопку «Сортировка и фильтр». В списке команд выберите команду «Настраиваемая сортировка».

Шаг 3.

Появится окно «Сортировка».

Теперь надо задать условия для сортировки.

Так как сначала нужна сортировка фамилий по алфавиту, то в списке «Сортировать по» выберите «Фамилия». В списке «Сортировка» оставьте «Значения». А в списке «Порядок» выберите «От А до Я».

Затем нужна сортировка по годовому фонду заработной платы. Щелкните на кнопке «Добавить уровень». Появится еще одна строка, где нужно выбрать условия.

В списке «Затем по» выберите «Годовой фонд заработной платы». В списке «Сортировка» опять оставьте «Значения». А в списке «Порядок» выберите «По убыванию». Нажмите ОК.

Сортировка будет выполнена.

Надеюсь, вы поняли принцип такой сортировки?

Еще в окне «Сортировка» можно сделать вот что:

Ну, и последний вид сортировки – это сортировка по своему списку. Я добавила к таблице столбик с началом считалки «Ехал грека через реку…». Сортировка по алфавиту мне не нужна. Мне надо, чтобы слова расположились так, как в считалке.

Шаг 1.

Снова откройте окно «Сортировка». В списке «Порядок» выберите «Настраиваемый список».

Шаг 2.

Откроется окно «Списки». В этом окне уже есть несколько списков. А мне нужно добавить новый список. Для этого:

Готово. Можно пользоваться для сортировки своим списком.

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

Сортировка числовых значений в Excel

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

Соответственно, сортировка числовых значений по убыванию — это расположение значений от наибольшего к наименьшему (от максимального к минимальному).

Сортировка текстовых значений в Excel

«Сортировка от А до Я» — сортировка данных по возрастанию;

«Сортировка от Я до А» — сортировка данных по убыванию.

Для сортировки текстовых значений по алфавиту, производится сравнение этих значений между собой. Как известно, при сохранении текста компьютер использует схему, в которой каждый символ имеет свой уникальный номер, называемый кодом символа. Вот именно эти коды и сравниваются для определения того, какое текстовое значение больше, а какое меньше.

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

Можно отсортировать текстовые данные с учетом регистра. Для этого необходимо в параметрах сортировки установить флажок в поле «Учитывать регистр».

Обычно буквы верхнего регистра имеют меньшие номера, чем буквы нижнего регистра.

Сортировка значений даты и времени

«Сортировка от старых к новым» — это сортировка значений даты и времени от самого раннего значения к самому позднему.

«Сортировка от новых к старым» — это сортировка значений даты и времени от самого позднего значения к самому раннему.

Сортировка форматов

В Microsoft Excel 2007 и выше предусмотрена сортировка по форматированию. Этот способ сортировки используется в том случае, если диапазон ячеек отформатирован с приминением цвета заливки ячеек, цвета шрифта или набора значков. Цвета заливок и шрифтов в Excel имеют свои коды, именно эти коды и используются при сортировке форматов.

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

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

Похожие записи:

Как узнать mac адрес компьютера в windows xp, 7, 8, 10? Топ 15 формул в excel Поиск в «ворде». как выполнить поиск в «ворде» по слову Как построить диаграмму в эксель по данным таблицы Как обновить драйвера видеокарты на windows 10 Как создать точку восстановления в windows 10 и откатить систему