Как отсортировать по дате в Excel

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

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

Как отсортировать столбцы Excel по возрастанию либо убыванию даты

В Excel просто отсортировать отдельные столбцы в порядке возрастания либо убывания даты:

Изберите данные, которые желаете отсортировать.
В строке меню (также именуемой лентой) в высшей части экрана на вкладке « Основная » изберите « Сортировка и фильтр» .
Потом изберите « Сортировать от старенькых к новеньким» либо « От новейших к старенькым» .

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

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

Как отсортировать целые таблицы по возрастанию либо убыванию даты

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

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

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

К счастью, Excel обычно впору обнаруживает эту ошибку, чтоб предупредить ее.

Если вы попытаетесь выполнить сортировку лишь по избранным датам, Excel выдаст предупреждение и спросит, не желаете ли вы расширить собственный выбор. Когда покажется сообщение о ошибке, изберите « Развернуть выбор» и нажмите « Сортировать» .

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

Изберите заглавия и данные, которые необходимо отсортировать. Потом нажмите Основная> Сортировка и фильтр и изберите порядок сортировки.

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

Внедрение настраиваемой сортировки Excel для сортировки по дате

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

В приведенном ниже примере обычный сортировки, как мы уже проявили, вы отсортируете элементы в столбце «Транзакция» в алфавитном порядке заместо того, чтоб упорядочивать даты. Так как дата находится во 2-м столбце, нам необходимо употреблять настраиваемую сортировку, чтоб сказать Excel, чтоб отсортировать даты в столбце B.

Выделите заглавия и данные под ними.
На ленте Excel щелкните Основная> Сортировка и фильтр> Пользовательская сортировка . Рядом с Сортировать по изберите Дата в раскрывающемся меню.
Установите флаг в правом верхнем углу, чтоб употреблять заглавия данных в качестве категорий сортировки. Изберите от старенькых к новеньким либо от новейших к старенькым в раскрывающемся меню « Порядок» .

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

Интересно почитать: Как в excel вернуть сетку

В поле «Пользовательская сортировка» изберите « Сортировать по> [Первый столбец]» . Потом щелкните Добавить уровень> Потом по> [Второй столбец] . Нажмите ОК, чтоб отсортировать таблицу.

Внедрение функций Excel для сортировки по месяцам

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

Существует огромное количество функций и формул Excel, которые посодействуют для вас делать резвые вычисления либо извлекать данные из ячейки. Одной из таковых функций является функция МЕСЯЦ.

Используйте функцию МЕСЯЦ, чтоб вынуть цифру месяца каждой записи из полной даты. Поначалу добавьте столбец с заголовком «Месяц». Щелкните первую пустую ячейку под Месяцем. В этом случае это будет C2. Введите = МЕСЯЦ (B2) , где B2 относится к первой записи в столбце Дата.

Функция МЕСЯЦ выведет цифру от 1 до 12 в подобающую ячейку. Эти числа представляют месяцы в хронологическом порядке дат, потому январь = 1, а декабрь = 12. Чтоб стремительно ввести функцию в каждую ячейку, наведите указатель мыши на C2, пока в нижнем углу ячейки не покажется зеленоватое поле. Потом щелкните и перетащите поле в нижнюю часть столбца.

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

Вы также сможете употреблять функцию для сортировки данных по годам. Следуйте приведенным выше инструкциям, заменив функцию МЕСЯЦ на = ГОД (A2) . Результатом будет перечень лет по порядку.

Внедрение формулы Excel для сортировки по месяцу и деньку

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

Добавьте столбец «Деньки рождения». В пустой ячейке (C2) рядом с первой датой введите = ТЕКСТ (B2, «MMDD») , где B2 относится к первой дате. Это возвратит перечень дат без года в формате MMDD. К примеру, 07.12.1964 станет 1207.

Сделайте произвольную сортировку, чтоб упорядочить перечень дней рождения. Так как деньки рождения отформатированы как текст, а не как даты, Excel выдаст предупреждение. Изберите Сортировать все, что смотрится как число, как число> ОК , и ваш перечень будет в правильном порядке.

Что созодать, если в Excel не работает сортировка по дате?

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

Организуйте данные Excel по дате

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

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

QUERY. Функция для сотворения запросов в Гугл-Таблицах

Функция QUERY дозволяет создать подборку подходящих строк из таблицы при помощи SQL-запроса и отсортировать их.

=QUERY(данные; запрос; [заголовки])

данные — это начальный спектр, который будет обрабатываться и из которого мы будем сформировывать подборку;
запрос на языке API визуализации Гугл (схожий SQL), обозначенный в кавычках, с соблюдением определенных правил, которые мы обсудим дальше;
заглавия — количество строк с заголовками в начальном спектре. По дефлоту равен -1 (минус одному), и это значит, что количество строк с заголовками будет определяться автоматом.

Итак, правила формирования запросов:

Запрос указывается в кавычках.
В запросе употребляются главные слова:
1. SELECT — описывает, какие столбцы из начальной таблицы выгружать и в каком порядке. К примеру: «SELECT A, C, D, B». Если пропустить либо указать звездочку («SELECT *») заместо заголовков столбцов, будут грузиться все столбцы в начальном порядке.
2. WHERE — ключевое слово, опосля которого следуют условия, по которым происходит отбор. Без него будут загружаться все строчки начального спектра.
3. GROUP BY — группирует значения по данным полям.
4. PIVOT — дозволяет создавать нечто вроде сводных таблиц, группируя данные по значениям из определенного поля начальной таблицы.
5. ORDER BY — задает сортировку. К примеру: «ORDER BY C DESC» — сортировка по столбцу C по убыванию.
6. LIMIT — ограничивает количество возвращаемых строк. К примеру: «LIMIT 50».
7. OFFSET — пропускает данное количество строк от начала спектра. К примеру: «OFFSET 100». В сочетании с LIMIT это ключевое слово действует первым, другими словами при использовании LIMIT 70 OFFSET 30 будут возвращены строчки с 31‑й до 100-й.
8. FORMAT — описывает формат определенных столбцов по данному шаблону.
9. LABEL — дозволяет переименовать столбцы в выдаче. К примеру, «LABEL MAX(D) ‘Среднее в 2016 году'». Заместо max 2016 в сформированной выдаче будет заголовок «Среднее в 2016 году».
Разглядим несколько примеров внедрения QUERY на практике.

Обычный пример: избираем книжки определенной темы из таблицы

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

При всем этом тему будем выбирать из выпадающего перечня на отдельном листе:

Функция QUERY для решения данной для нас задачки будет смотреться последующим образом:

Мы извлекаем данные из столбцов A и C в спектре ‘Книжки’!A1:C. Фильтруем данные по столбцу B (теме) этого спектра по избранному аспекту из выпадающего перечня в ячейке A1. Сортируем по убыванию по столбцу C начального спектра и добавляем к нашей выборке заглавия (крайний аргумент функции QUERY = 1).

Группируем данные при помощи GROUP BY и PIVOT

Сгруппировать данные, используя QUERY, можно при помощи 2-ух главных слов: GROUP BY и PIVOT, ниже разглядим примеры с ними.

Таблица, с которой мы будем работать:

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

Начнем с GROUP BY, текст функции будет таковым:

=QUERY(‘Книжки‘!A1:C6;»select B, sum(C) group by B»)

Направьте внимание: чтоб функция работала, кроме группировки (group by B) нужна хотя бы одна аггрегирующая функция, в нашем случае это sum(C). Напишу, на всякий вариант, все аггрегирующие функции для QUERY: sum(), max(), min(), avg() и count().

Итог нашей формулы:

При помощи GROUP BY вероятна группировка и по нескольким столбцам, для этого просто перечислите их, как в функции ниже и не забудьте добавить эти столбцы в SELECT:

Группировка при помощи PIVOT.

Направьте внимание, что тут в SELECT не надо писать столбец B, по которому данные будут сгруппированы.

Пока отличие в том, что сгрупированные элементы размещены по столбцам, а не по строчкам, как в GROUP BY.

Добавим очередной столбец для группировки.

Видите — два сгруппированных столбца показываются в одном поле через запятую. В этом ключевое отличие PIVOT от GROUP BY, если там любой столбец группировки занимает отдельный столбец, то в PIVOT выходит нечто вроде сводной таблицы с неповторимыми полями из нескольких частей. По сиим полям, к слову, позже можно достаточно просто находить необходимое значение при помощи ГПР либо ПОИСКПОЗ.

Строим сводную таблицу со средними/наивысшими значениями по тематикам

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

Мы используем схожий спектр (в отличие от предшествующего в нем есть реализации за 2015 и 2016 годы), извлекаем средние значения по столбцам C и D (SELECT avg(C), avg(D)) и группируем их по столбцу B (тема).

Приобретенный итог транспонируем для комфортного отображения (при помощи функции TRANSPOSE (ТРАНСП)):

Можно употреблять и остальные функции заместо avg (среднего), к примеру max (наибольшие значения):

Либо показать и среднее, и максимум, но лишь по столбцу D:

Чемодан «Считаем средний чек, выбирая данные с определенной даты»

На снимке экрана массив данных, с которым мы будем работать:

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

Начнем. Сделаем QUERY с умножением количества клиентов (столбец B) на средний чек (столбец С) начиная с определенной даты:

Верно употреблять дату в формуле QUERY так:
- QUERY работает с датой лишь в формате yyyy-mm-dd. Чтоб перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
- перед датой и перед апострофом необходимо написать date;
- можно и не созодать ссылку на ячейку с датой, а написать ее сходу в QUERY, тогда формула будет смотреться так:
- дата с 2-ух сторон обрамляется одиночными кавычками (‘).
Вернемся к тому, что у нас вышло. Наша формула выдала вот таковой массив данных:

Это построчные произведения количества клиентов на средний чек. Нам необходимо просуммировать их, для этого введем перед формулой СУММ (SUM):

Чтоб получить средний чек, получившееся число необходимо поделить на общую сумму клиентов в отобранных строчках. Чтоб закрепить внедрение QUERY, снова воспользуемся данной для нас формулой.

Берем предшествующую формулу, меняем B*C на sum(B) и получаем такую систему:

В конце концов, совмещаем формулы:

Все работает, ура! 53 (этот итог видно на всплывающей подсказке в верхнем левом углу) — средний чек с учетом количества клиентов, рассчитанный через среднее взвешенное.

Чемодан «QUERY и выпадающий перечень»

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

Итак, выпадающий перечень. Сначала сделаем новейший лист (допустим, наша начальная таблица громадна, и всю аналитику мы желаем создавать на другом листе). Кликаем правой клавишей мыши на ячейку А1, избираем Проверка данных.

В Правилах избираем Значение из перечня, перечисляем все наши темы через запятую и жмем Сохранить:

Перечень вышел вот таковым:

В соседнюю ячейку А2 впишем последующую формулу:

И разберем ее по частям:
- ‘Книжки’!A1:C13 — начальный спектр, таблица с продажами, книжками и темами.
- SELECT A, C — в сформированную функцией таблицу попадут данные из этих столбцов, другими словами наименования книжек и реализации.
- WHERE B = ‘»&A1&»‘ отбирает лишь те книжки, тема (в столбце B) которых соответствует обозначенной в ячейке A1. Направьте внимание на синтаксис: текст из ячейки указывается меж апострофов, которые относятся к тексту запроса. Опосля их идут кавычки (мы закрываем текст запроса), амперсанд (присоединяем к тексту запроса текст из ячейки), адресок ячейки, очередной амперсанд, опосля которого в кавычках длится текст запроса.
- ORDER BY C DESC — сортируем данные по столбцу B (продажам) по убыванию.
Изменив тему в ячейке А1 на философию, мы получим книжки лишь по философии, отсортированные по продажам. Комфортно.

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

Query по нескольким спектрам данных

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

Спектры указываются через точку с запятой в фигурных скобках:

Принципиально отметить: в таком случае столбцы снутри запроса обозначаются не знаками, как в остальных вариантах (A, B, AH, CZ и так дальше), а в виде ColN, где N — номер столбца.

Канал «Гугл Таблицы — это просто» в Телеграме

Спасибо Евгению Намоконову за помощь в подготовке кейсов для данной для нас статьи. Мы с Евгением ведем канал в Телеграме по Гугл Таблицам.