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

  • Рочева Наталья Алексеевна, педагог дополнительного образования

Разделы: Информатика

Цели: закрепить пройденный материал по Excel; познакомить с алгоритмом сортировки строк и научить сортировать данные в Excel; развить алгоритмическое и логическое мышление; воспитать чувство коллективизма, любовь к родному краю. Реквизит: доска, мел, маркеры; компьютеры, ПО Excel, фронтальная работа на всех компьютерах; карточки с заданиями, листы бумаги.

Структура урока

  1. Организационный момент – 1 минута.
  2. Самостоятельная работа на повторение
  3. Новая тема (лекция) – 15 минут.
  4. Закрепление (фронтальная работа и самостоятельная работа) – 10 минут.
  5. Задание на дом – 1 минута.
  6. Итоги урока – 3 минуты.

1. Организационный момент

Здравствуйте, ребята. Сегодня на уроке мы продолжим работу в Excel и рассмотрим сортировку строк в таблице. Но вначале проверим пройденный материал.

За компьютеры садятся и открывают Excel 4 (8) ребят. Остальные (раздаю карточки) будут делать задания на карточках.

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

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

Кто работал на карточках, положите карточки на край стола. (Карточки собираю).

Работа на карточках ПРИЛОЖЕНИЕ 1. Работа на компьютере ПРИЛОЖЕНИЕ 2. Работают 8 минут.

3. Новая тема (лекция)

Очень часто возникает необходимость отсортировать введенную таблицу по возрастанию или убыванию. Существуют 2 способа сортировки, рассмотрим их:

1 способ — с помощью кнопок сортировки на панели инструментов “Стандартная”. Эти кнопки задают сортировку выделенных ячеек по возрастанию или убыванию. В качестве ключа сортировки программа берет тот столбец, где стоит курсор, и в соответствии с ним меняет местами строки таблицы. Если выделена не вся таблица, то Excel при сортировке перемешает всю вашу таблицу.

2 способ — дает больше возможности при сортировке, меню “Данные”/ команда “Сортировка”. В диалоговом окне “Сортировка” можно создать три ключа сортировки: Сортировать по…, Затем по…, В последнюю очередь по… можно выбрать сортировку по возрастанию или убыванию. Для чего это необходимо? Иногда встречаются случаи, когда необходимо отсортировать данные, в которых есть некоторые одинаковые строки. Например, вы написали список учащихся 5-го класса Щельябожской школы, и у вас есть трое учащихся, которых зовут Канев Володя, их тоже надо отсортировать, для этого необходимо будет воспользоваться вторым способом сортировки.

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

Выбрав Параметры, вы можете установить следующее:

  • Сортировка по первому ключу – можно выбрать любой нестандартный порядок сортировки.
  • Если установите флажок Учитывать регистр, то при сортировке будут различаться строчные и прописные символы.
  • Можете выбрать переключатель строки диапазона, для сортировки по строкам.

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

На доске плакат — диалоговое окно “Сортировка”, ПРИЛОЖЕНИЕ 3.

4. Закрепление (фронтальная работа и самостоятельная работа)

Теперь применим полученные знания на практике.

Содитесь за компьютеры, открывайте Excel, в “Моих документах” ищем файл “Фронтальная работа”. Открываем его. Откройте лист “Возрастная структура”. Вы видите таблицу “Возрастная структура населения Коми” за разные годы.

  • Выделите столбец от 0 до 19 лет.
  • На панели инструментов “Стандартная” нажмите кнопку “сортировать по возрастанию”. Что получилось? (Отсортировался только этот столбец, а остальные остались без изменения).
  • Отмените последнее действие.
  • Теперь выделите всю таблицу и опять отсортируйте с помощью кнопки “сортировка по возрастанию”. Что изменилось? (Вся таблица отсортировалась по столбцу в котором стоял курсор).
  • Выделите всю таблицу. Выберите “Данные”/“Сортировка”. В появившемся диалоговом окне в первом уровне выберите “от 0 до 19 лет”, выберите “по возрастанию”. Что получилось? (Таблица отсортировалась по возрастанию по столбцу “от 0 до 19 лет”).

Теперь откройте лист – “Друзья”.

  • Выделите таблицу. Отсортируйте по убыванию с помощью кнопки панели инструментов стандартная. Отмените действие.

Отсортируйте с помощью меню “Данные”/“Сортировка” 1 уровень — по фамилии по возрастанию, 2 уровень — по имени по убыванию, 3 уровень – по дате рождения по возрастанию. Что получилось? (Таблица отсортировалась по выбранным параметрам).

Задания для самостоятельной работы.

  • Отсортировать таблицу “Друзья” сначала по имени по убыванию, затем по № телефона по убыванию, далее по адресу по возрастанию (прохожу смотрю, что получилось).

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

На каждом компьютере создан файл “Фронтальная работа”. (ПРИЛОЖЕНИЕ 4, ПРИЛОЖЕНИЕ 5).

5. Задание на дом

Закройте программу, садитесь за парты, запишите домашнее задание.

6. Итоги урока

А теперь давайте повторим, что мы сегодня рассмотрели на уроке.

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

Сегодня, ребята, вы хорошо поработали. Мы много успели сделать. За самостоятельную работу на компьютере получили следующие отценки… За тест … На этом наш урок закончен, до свидания.

10.03.2005

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

Стандартная сортировка

Отсортировать таблицу в Excel можно стандартно, процедура включает в себя 3 варианта:

  • по алфавиту (от А до Я);
  • возрастанию;
  • убыванию.

Это удобно, при работе с конкретными и большими блоками информации. Например, объемы продаж или подсчет доходов или расходов. Конечно, в таблице 4х4 пользователь сможет быстро найти нужную цифру, но с размерами 44х44 это сделать сложнее – в этом случае и используют сортировку.

Изменение чисел или других данных по возрастанию или убыванию простое. В первую очередь следует определить наличие в таблице формул. Если они есть, то лучше всего перенести данные на новый лист, чтобы не сбить настройки ячеек. После завершения всех подготовительных операций нужно выделить нужную таблицу, и перейти в панель “Данные”. В этом меню и находится пункт “Сортировка”.image

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

Совет! Если в таблице имеются формулы, то необходимо поставить крестик возле строки “Мои данные содержат заголовки”.

Упорядочить текст по алфавиту можно так же, только следует выбрать соответствующий параметр фильтра (в разных версиях Excel он подписан по разному: A-Z или А-Я).

  Excel — подготовка к печати и варианты печати документа

Несколько ячеек

Для сортировки в Excel по нескольким столбцам или ячейкам стоит воспользоваться пунктом “Настраиваемый список” и выбрать меню “Добавить уровень”. Тут следует указать для каждого столбца необходимый параметр редактирования. Именно в таком случае информация в таблице изменится для каждого столбца отдельно.

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

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

По цвету

Форматирование данных по дате или по цвету мало чем отличается друг от друга. Требуется выделить таблицу, зайти в меню “Сортировка” и выбрать “Параметры”. В подразделе следует выбрать параметр “Сортировка по цвету”. После этого в соответствующем окне нужно выставить оттенок цвета с которого будет начинаться сортировка (т.е. он будет находится вверху или внизу таблицы). Редактор после подтверждения действия выдаст отредактированную таблицу, в которой указанный цвет будет расположен вверху или внизу.

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

По дате

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

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

  Как посчитать среднее арифметическое в Excel – все доступные способы

Важно! Проблемы с выбором данного фильтра могут быть обусловлены некорректным форматом ячеек. Например, если дата будет введена простым числами в формате 01.01.2011, без указания формата ячейки “Дата”.

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

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

Проходим стандартный путь – “Данные” – “Сортировка”. Выбираем меню “Настраиваемая”, “Параметры”. Для переключения варианта сортировки со столбцов на строки следует переместить галочку на “столбцы диапазона”.

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

Таким образом можно отсортировать строки.

Случайная и динамическая сортировка

Случайное редактирование производится с помощью функции СЛЧИС. Оно подойдет для случайного форматирования различных чисел. Для этого следует выделить нужные числа, после чего ввести функцию =СЛЧИС(). В скобках указать нужный диапазон.

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

Не работает сортировка в Excel

Чаще всего проблема заключается в формате ячеек. Даты нельзя отформатировать, если они находятся в формате ячейки “Строка”, и т.д. В случае сбоя программы может помочь – перезапуск Excel, перезагрузка компьютера или переустановка офисного пакета. Но чаще всего виновато именно некорректное заполнение, неправильные настройки или формат ячейки.

Читайте также:

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

Сортировка листов по алфавиту

Предположим, что перед нами стоит задача отсортировать листы по алфавиту, которые стоят в произвольном порядке:

Рассмотрим 2 варианта сортировки листов по алфавиту: по возрастанию (от А до Я) и по убыванию (от Я до А).

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

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

Для переноса макроса в свою книгу, скопируйте код и вставьте его в любой модуль книги в редакторе Visual Basic (для быстрого перехода в редактор нажмите Alt + F11). После выполнения макроса Sorting_Sheets_Ascending структура листов примет следующий вид:

Сортировка листов по возрастанию

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

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

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

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

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

Сортировать листы можно не только по алфавиту, но и по другим параметрам, например, по цвету ярлычка:

Код макроса для сортировки по возрастанию (для сортировки по убыванию в условии нужно поменять «>» на «<«):</p>

В зависимости от условия сортировки (по возрастанию, по убыванию) после выполнения макроса Sorting_Sheets_Tab получаем следующие результаты:

Сортировка листов по цвету ярлычка (по возрастанию)

Сортировка листов по цвету ярлычка (по убыванию)

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

Сортировка данных в Excel – инструмент для представления информации в удобном для пользователя виде.

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

Порядок сортировки в Excel

Существует два способа открыть меню сортировки:

  1. Щелкнуть правой кнопкой мыши по таблице. Выбрать «Сортировку» и способ.
  2. Открыть вкладку «Данные» — диалоговое окно «Сортировка».

Часто используемые методы сортировки представлены одной кнопкой на панели задач:

Сортировка таблицы по отдельному столбцу:

  1. Чтобы программа правильно выполнила задачу, выделяем нужный столбец в диапазоне данных.
  2. Далее действуем в зависимости от поставленной задачи. Если нужно выполнить простую сортировку по возрастанию/убыванию (алфавиту или обратно), то достаточно нажать соответствующую кнопку на панели задач. Когда диапазон содержит более одного столбца, то Excel открывает диалоговое окно вида: Чтобы сохранилось соответствие значений в строках, выбираем действие «автоматически расширить выделенный диапазон». В противном случае отсортируется только выделенный столбец – структура таблицы нарушится.

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

Сортировка по цвету ячейки и по шрифту

Программа Excel предоставляет пользователю богатые возможности форматирования. Следовательно, можно оперировать разными форматами.

Сделаем в учебной таблице столбец «Итог» и «зальем» ячейки со значениями разными оттенками. Выполним сортировку по цвету:

  1. Выделяем столбец – правая кнопка мыши – «Сортировка».
  2. Из предложенного списка выбираем «Сначала ячейки с выделенным цветом».
  3. Соглашаемся «автоматически расширить диапазон».

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

В открывшемся окне вводим необходимые параметры:

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

По такому же принципу сортируются данные по шрифту.

Сортировка в Excel по нескольким столбцам

Как задать порядок вторичной сортировки в Excel? Для решения этой задачи нужно задать несколько условий сортировки.

  1. Открываем меню «Настраиваемая сортировка». Назначаем первый критерий.
  2. Нажимаем кнопку «Добавить уровень».
  3. Появляются окошки для введения данных следующего условия сортировки. Заполняем их.

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

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

По умолчанию сортируются данные по столбцам. Как осуществить сортировку по строкам в Excel:

  1. В диалоговом окне «Настраиваемой сортировки» нажать кнопку «Параметры».
  2. В открывшемся меню выбрать «Столбцы диапазона».
  3. Нажать ОК. В окне «Сортировки» появятся поля для заполнения условий по строкам.

Таким образом выполняется сортировка таблицы в Excel по нескольким параметрам.

Случайная сортировка в Excel

Встроенные параметры сортировки не позволяют расположить данные в столбце случайным образом. С этой задачей справится функция СЛЧИС.

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

Ставим курсор в соседнюю ячейку (слева-справа, не важно). В строку формул вводим СЛЧИС(). Жмем Enter. Копируем формулу на весь столбец – получаем набор случайных чисел.

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

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

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

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

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

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

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

Скачать формулы сортировки данных в Excel

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

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

Видеоурок по данной теме «Как отсортировать сотрудников по дням рождения в Excel» можно найти на нашем канале «Учите компьютер вместе с нами».

Идеальная сортировка для такого случая — это упорядочивание людей по возрастанию их месяца рождения, а в пределах каждого месяца — по дате. Этим мы сейчас и займемся. Но прежде всего, какой вид будут иметь исходные данные? Пусть, в колонке «А» располагается порядковый номер сотрудника, в колонке «В» — его фамилия и инициалы, а в колонке «С» — дата рождения, как показано на рисунке ниже. imageЧисленность сотрудников может быть любой, до нескольких сотен или даже десятков тысяч человек. Первоочередная наша задача — это выделить из даты рождения каждого сотрудника отдельно месяц и день. Для этого будем использовать вспомогательные колонки «D» и «E«. Введем в ячейку D2 формулу =МЕСЯЦ(C2), а в ячейку Е2 — формулу =ДЕНЬ(C2). Далее, выделяем ячейки D2:Е2 и за правый нижний угол растягиваем введенные формулы вниз, до конца списка. В результате мы получим таблицу вида: imageНу а теперь, заключительный штрих. Нам осталось выполнить сортировку всех сотрудников по двум последним колонкам. Для этого, выделяем область сортировки, протягивая левой кнопкой мыши по заголовкам колонок А:Е. Затем переходим во вкладку «Данные» и в секции «Сортировка и фильтр» нажимаем кнопку «Сортировка«. imageВ появившемся окне «Сортировка» из выпадающего списка «Сортировать по» выбираем поле «Месяц«. Далее нажимаем кнопку «Добавить уровень» и в списке «Затем по» выбираем поле «День«. Все остальные параметры сортировки оставляем без изменений и нажимаем кнопку «ОК«. В результате, получим как раз то, что и требовалось: Как видим, теперь все сотрудники упорядочены по возрастанию месяца их рождения. Если в течение месяца день рождения наступает сразу у нескольких человек, тогда они упорядочены по возрастанию дней. Зная текущую дату, теперь не сложно определить, у кого из них скоро будет день рождения и в какой последовательности.

Как сообщалось на нашем сайте ранее, с помощью VBA в приложении Microsoft Excel можно имитировать эксперимент по выбрасыванию игральной кости.

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий