Устранение неполадок с форматами дат в Excel
Иногда, когда вы пытаетесь отсортировать в Excel по дате, вы можете обнаружить, что не можете заставить Excel распознавать данные как дату. Представьте, например, что кто-то дает вам электронную таблицу с датами. Вы поместили данные в Excel, но они не сортируются правильно, когда вы пытаетесь использовать описанные выше методы. Почему не работает?
Рассмотрим этот список дат:
Выглядит достаточно просто. Однако, когда вы выбираете данные, нажимаете кнопку «Сортировка и фильтр» и выбираете сортировку от самых старых до самых новых, это не работает! Вы можете получить что-то вроде этого:
Обратите внимание, что последняя дата в столбце отсортирована неправильно. Что, черт возьми, происходит?. Скорее всего, даже если данные в ячейке A9 выглядит как и дата, она не форматируется как дата, поэтому Excel не может ее правильно отсортировать
В зависимости от того, какую версию Excel вы используете, даже если вы отформатируете ошибочную ячейку как дату, выбрав ячейку, щелкнув правой кнопкой мыши и выбрав Формат ячеек и выбрав параметр Дата, Все еще не решит проблему
Скорее всего, даже если данные в ячейке A9 выглядит как и дата, она не форматируется как дата, поэтому Excel не может ее правильно отсортировать. В зависимости от того, какую версию Excel вы используете, даже если вы отформатируете ошибочную ячейку как дату, выбрав ячейку, щелкнув правой кнопкой мыши и выбрав Формат ячеек и выбрав параметр Дата, Все еще не решит проблему.
Как исправить даты, которые не будут правильно отсортированы в Excel
К счастью, эту проблему легко исправить.
- Вырежьте столбец дат, выбрав ячейки и нажав Ctrl + x, выбрав «Вырезать» в главном меню или щелкнув правой кнопкой мыши по ячейке (ям) и выбрав «Вырезать».
- Откройте Блокнот или другой текстовый редактор.
- Вставьте даты в текстовый редактор.
- Вернитесь в Excel, выберите столбец, в котором раньше были даты, щелкните правой кнопкой мыши и выберите Формат ячеек.
- Выберите «Дата» и нажмите «ОК».
- Теперь вернитесь в текстовый редактор, выберите и скопируйте список дат.
- Вернитесь в Excel и вставьте даты в столбец, который вы отформатировали на шаге 5.
- Теперь у вас должна быть возможность сортировать. Выберите столбцы с данными, нажмите кнопку «Сортировка и фильтр» и выберите «Сортировать от старых к новым». Альт! Оно работает!
Google Sheet – электронные таблицы от Гугл онлайн
- Перейти на «Таблицы Гугл».
- Нажать «Открыть Гугл таблицы» (при необходимости пройдите процедуру регистрации).
- Затем необходимо кликнуть на кнопку с плюсиком внизу, и перейти непосредственного к созданию таблицы онлайн.
- Для сохранения результата необходимо будет нажать на «Файл» — «Скачать как» — «Файл Майкрософт Эксель».
Как импортировать данные из Интернета в Microsoft Excel — TonV
Пока ваши веб-данные представлены в табличной форме и у вас есть соответствующая версия Microsoft Excel, импорт веб-данных в Excel должен быть простым. Вы также можете импортировать каталог Windows или сведения о файле в Excel.
Из Интернета
Сортировка одного столбца дат в Excel
Самый простой способ отсортировать даты в Excel — использовать инструмент сортировки для отображения дат в нужном вам порядке.
Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)
- Выберите столбец дат, щелкнув заголовок столбца. На изображении ниже мы выбрали столбец, щелкнув заголовок столбца A.
- Вы также можете просто выбрать определенные ячейки в столбце, в которых есть даты.
- Выбрав данные, выберите «Сортировка и фильтр» в главном меню.
Теперь выберите Сортировать от старых к новым или Сортировать от новых к старым. В приведенном ниже примере мы отсортировали от самых новых к самым старым
Обратите внимание, что Excel не изменяет формат дат, но правильно сортирует даты, начиная с самых новых
Этот метод должен работать в подавляющем большинстве случаев использования.
Импорт данных (Excel для ПК)
После того как вы определили веб-сайт, содержащий требуемую информацию, вы можете импортировать данные непосредственно в Excel, используя Из Интернета инструмент всего за несколько кликов, настраивая параметры импорта на этом пути.
Вот как импортировать таблицу данных из Интернета на ПК:
Выбрать Данные > Из Интернета.
Выбратьосновной, введите или вставьте URL в поле и выберитеХОРОШО.При появлении запроса выберите соединять на сайт.
В поле Навигатор выберите таблицы для импорта. Excel изолирует блоки контента (текст, таблицы и графику), если он знает, как их анализировать. Чтобы импортировать более одного объекта данных, поставьте галочку рядом сВыберите несколько элементов .
После выбора таблицы в правой части окна появится предварительный просмотр. Если это нужная таблица, выберитенагрузка, Таблица появится на новом рабочем листе.
В правой части экрана отображается Запросы и подключения панель. Если вы импортировали несколько таблиц, выберите таблицу на панели «Запросы и подключения», чтобы просмотреть ее.
Редактирование данных перед импортом
Если требуемый набор данных очень велик или не отформатирован в соответствии с вашими ожиданиями, измените его в редакторе запросов перед загрузкой данных с веб-сайта в Excel.
В поле Навигатор выберитеПреобразовать данные вместоЗагрузка. Excel загружает таблицу в редактор запросов вместо электронной таблицы. Этот инструмент открывает таблицу в специализированной коробке, которая позволяет:
- Управление запросом
- Выбор или удаление столбцов и строк в таблице
- Сортировка данных
- Разделенные столбцы
- Группировать и заменить значения
- Объедините таблицу с другими источниками данных
- Отрегулируйте параметры таблицы
Редактор запросов предлагает расширенную функциональность, более близкую к среде базы данных (например, Microsoft Access), чем знакомые электронные таблицы Excel.
После загрузки веб-данных в Excel вы получите доступ к ленте инструментов запросов. Этот новый набор команд поддерживает редактирование источника данных (через Редактор запросов), обновление исходного источника данных, слияние и добавление с другими запросами в книге, а также совместное использование очищенных данных с другими пользователями Excel.
Сортировка в Excel по нескольким столбцам
Сортировку строк в Excel можно осуществить по нескольким столбцам одновременно. В этом случае вначале происходит сортировка по первому указанному столбцу. Затем те строки, где ячейки в отсортированном столбце повторяются, сортируются по второму указанному столбцу и т.д. Таким образом, отчет о продажах можно упорядочить вначале по региону, затем в рамках каждого региона по менеджеру, затем в рамках одного региона и менеджера по группе товаров.
Выделив одну ячейку или всю таблицу, вызываем на ленте команду сортировки. Если нужно, ставим галочку Мои данные содержат заголовки и добавляем необходимое количество уровней.
Повторение и продолжение обучения
Сортировка это ещё одно умение, которое нужно держать в готовом виде в своей книге Excel. Когда вам нужно повторно сопоставить данные в электронной таблице, слишком много времени можно потратить для вырезания и вставки строк в определенном порядке, поэтому сортировка является обязательной.
Как всегда, вы можете опираться на навыки, которые вы узнали сегодня, погрузившись в более удобные уроки по Excel. Посмотрите ссылки ниже:
- Сортировка данных это мощный навык, но иногда вам может понадобится подвести итог и уяснить наборы данных, поэтому вместо этого используйте сводные таблицы.
- Ниже приведен обзор дополнительных руководств по сортировке, если вы хотите изучить этот навык из другого источника.
- Узнайте, как работать с формулами Excel — Как работать с формулами в Excel: копирование, вставка и автозаполнение.
Как вы сортируете данные? Используете ли вы простые методы или расширенный инструмент сортировки для обеспечения идеальной сортировкт?
Расшифровка кода
1. – метод Sort объекта Worksheet возвращает объект Sort.
Объект Sort – это объект, представляющий сортировку диапазона данных.
2. – метод SortFields объекта Sort возвращает коллекцию объектов SortFields. Метод Clear объекта SortFields удаляет все существующие объекты SortField.
Объект SortField содержит все сведения о параметрах сортировки для заданного рабочего листа.
3. – метод Add объекта SortFields создает и возвращает новый экземпляр объекта SortField с заданными параметрами.
Параметры метода Add объекта SortFields:
– обязательный параметр, который задает значение ключа для сортировки. Тип данных – Range. Обычно указывается первая ячейка столбца при сортировке по строкам или первая ячейка строки при сортировке по столбцам. Сортировка диапазона будет осуществлена по данным столбца (строки), первая ячейка которого указана в качестве ключа.
– необязательный параметр, который задает критерий сортировки (по какому свойству ячеек производится сортировка).
Значения, которые может принимать SortOn:
| Константа | Значение | Описание |
|---|---|---|
| SortOnValues | сортировка по значению (значение по умолчанию) | |
| SortOnCellColor | 1 | сортировка по цвету ячейки |
| SortOnFontColor | 2 | сортировка по цвету шрифта |
| SortOnIcon | 3 | сортировка по иконке* |
* Иконки (значки) могут быть заданы ячейкам при условном форматировании диапазона.
– необязательный параметр, задающий порядок сортировки (по возрастанию или по убыванию).
Значения, которые может принимать Order:
| Константа | Значение | Описание |
|---|---|---|
| xlAscending | 1 | сортировка по возрастанию (значение по умолчанию) |
| xlDescending | 2 | сортировка по убыванию |
– необязательный параметр, который задает способ сортировки текста.
Значения, которые может принимать DataOption:
| Константа | Значение | Описание |
|---|---|---|
| xlSortNormal | числовые и текстовые данные сортируются отдельно (значение по умолчанию) | |
| xlSortTextAsNumbers | 1 | текстовые данные рассматриваются для сортировки как числовые |
4. – метод SetRange объекта Sort задает диапазон (таблицу), в котором выполняется сортировка.
5. – свойство Header объекта Sort указывает, является ли первая строка таблицы строкой заголовков (шапкой).
Значения, которые может принимать свойство Header:
| Константа | Значение | Описание |
|---|---|---|
| xlGuess | Excel сам определяет, есть ли строка заголовков | |
| xlYes | 1 | строка заголовков есть, сортировка ее не затрагивает |
| xlNo | 2 | строки заголовков нет (значение по умолчанию) |
6. – свойство MatchCase объекта Sort указывает, как учитывать регистр при сортировке.
Значения, которые может принимать свойство MatchCase:
| Константа | Значение | Описание |
|---|---|---|
| False | регистр не учитывается (значение по умолчанию) | |
| True | 1 | сортировка с учетом регистра |
7. – свойство Orientation объекта Sort задает ориентацию для сортировки.
Значения, которые может принимать свойство Orientation:
| Константа | Значение | Описание |
|---|---|---|
| xlTopToBottom | 1 | сортировка по стокам (значение по умолчанию) |
| xlLeftToRight | 2 | сортировка по столбцам |
8. – метод Apply объекта Sort выполняет сортировку диапазона в соответствии с примененными параметрами.
Сортировка в Excel
Сортировка данных в Excel весьма полезный инструмент, улучшающий восприятие представленной информации. Прежде, чем перейти к разбору интерфейса, разберем порядок сортировки приложения:
- Первыми идут цифры от меньшего к большему;
- Затем идут специальные символы ($, %, * и т.д.);
- За ними следуют знаки препинания;
- Далее начинаются алфавиты, сначала английский, затем русский. Также не учитывается регистр, т.е. заглавные и прописные буквы равны друг другу (А=а).
Для того чтобы выполнить упорядочивание, пройдите на вкладку «Данные» и выберите раздел «Сортировка и фильтр». Для сортировки предназначено 3 значка:
Сортировка по возрастанию (от А до Я), по убыванию (от Я до А) и значок сортировки для задания условий по нескольким столбцам одновременно.
Рассмотрим все пункты подробнее.
Для того чтобы в эксель отсортировать диапазон ячеек, предварительно выделите его, затем кликните по необходимому значку. Учтите, что самая верхняя ячейка в выделенном диапазоне будет воспринята как шапка таблицы, т.е. отсортирована не будет и останется на прежнем месте. Также, если рядом с выделенным диапазоном имеются другие заполненные ячейки, то программой будет выведено следующее окно:
Если Вами будет выбран первый вариант, то строки таблицы сохранят свою целостность. Если второй, то данные вне диапазона сортировки останутся нетронутыми. На рисунках приведены результаты до и после сортировки для двух вариантов.
Установка порядка первого столбца таблицы при выборе первого варианта:
Как видно строки сохранили свою целостность, и имена по-прежнему соответствуют своим фамилиям.
Те же самые действия, но при выборе второго варианта:
Сортировка также произведена по первому столбцу, за исключением того, что второй столбец не сместился относительно отсортированных ячеек.
Выбирайте сразу необходимый диапазон для сортировки, чтобы Excel не предлагал Вам автоматически его расширить. Столбец, с которого Вы начнете выделение диапазона, будет являться столбцом для условий сортировки.
Если возникла необходимость задать порядок по нескольким столбцам, то в этом поможет значок «Сортировка», который уже упоминался. Выделите диапазон, который необходимо упорядочить
В данном случае неважно, с какого столбца он начнет выделяться, т.к. условия и столбцы будут задаваться вручную
Для примера будем использовать следующую таблицу:
Мы специально будем использовать такие данные, чтобы наглядно отобразить, как Excel осуществляет сортировку.
Выделите диапазон нашей таблицы и кликните по значку «Сорировка». Появится следующее окно:
Изначально первый уровень сортировки уже добавлен. В выпадающем списке выберите нужный столбец. В качестве его названия служит верхняя («шапка»).
В следующем выпадающем списке можно выбрать, по каким параметрам сортировать диапазон. Например, если Вы использовали заливку для ячеек или меняли цвет шрифта, то возможно задать порядок по данным параметрам.
Следующий выпадающий список задает порядок: по возрастанию, по убыванию и т.п.
Для добавления второго и последующих столбцов для сортировки воспользуйтесь кнопкой «Добавить уровень» и задайте для него параметры.
Для нашего примера используем трехуровневую сортировку. Для первого уровня зададим сортировку по «Столбец1», порядок «От Я до А». Для второго уровня зададим сортировку по «Столбец2», порядок «По возрастанию». Для третьего уровня по «Столбец3», порядок «По убыванию». Параметры для сортировки везде «значения».
Рассмотрим получившийся результат:
Сначала сортировка прошла по первому столбцу (первый уровень), выделив данные в определенные поддиапазоны (в красной рамке). Далее происходит сортировка второго уровня, но данные сортируются только в пределах определенных первым уровнем поддиапазонов. Отсортировав второй уровень, вновь определяются поддиапазоны (в синей рамке). Затем сортировка происходит по третьему уровню по такому же алгоритму.
Таким образом, можно задавать сортировку в Excel по значительному количеству столбцов.
Также в окне сортировки можно задавать различные параметры:
- Поставив флажок на поле «Мои данные содержать заголовки», программа учтет, что верхние ячейки диапазонов упорядочивать не нужно;
- Нажав кнопку «Параметры» Вы получите окно, в котором можно задать:
- Возможность учета регистра (заглавные символы имеют больший порядковый номер по сравнению с прописными);
- Выбрать сортировку по строкам или по столбцам.
Как видите, пользоваться сортировкой в Excel совсем не трудно, но это важный инструмент, который часто требуется при работе с данными.
Использование пользовательских параметров сортировки Excel для изменения порядка столбцов
Как упоминалось выше, в то время как пользовательские сортировки доступны в диалоговом окне «Сортировка» в Excel, эти параметры нелегко использовать, когда речь идет о переупорядочении столбцов на листе. Опции для создания пользовательского порядка сортировки, доступные в диалоговом окне Сортировка, предназначены для сортировки данных по цвету ячеек, цвету шрифта и значку.
Если в каждом столбце уже не было применено уникальное форматирование, например, разные цвета шрифтов или ячеек, это форматирование должно быть добавлено к отдельным ячейкам в той же строке для каждого переупорядоченного столбца.
Например, чтобы использовать цвет шрифта для изменения порядка столбцов на изображении выше:
- Нажмите на каждый имя поля и изменить цвет шрифта для каждого, например, красный, зеленый, синий и т. д.
- В диалоговом окне «Сортировка» установите Сортировать по вариант Цвет шрифта .
- Под порядок, вручную установите порядок цветов полей чтобы соответствовать желаемому порядку столбцов.
- После сортировки, сбросить цвет шрифта для каждого имени поля.
Возможности сортировки данных в Excel списком
Excel позволяет анализировать данные по разным параметрам. Элементом комплексного анализа является классификация. Для работы элемента информация должна быть представлена в виде списка, отформатированного определенным образом.
Данные таблицы разделены на строки и столбцы. Столбцы содержат значения одного типа. Цифры, даты, текст. Но не смешанный. Сортировка не будет работать, если есть пустые строки или столбцы.
Сортировка данных в Excel
В Excel вы можете сортировать списки:
- вверх;
- нисходящий;
- по заданным пользователем параметрам (дни недели, месяцы и т.д.).
Сортировка по возрастанию означает сортировку данных:
- от 0 до 9;
- от А до Я;
- от А до Я.
По убыванию — в обратном порядке.
Для вызова пользовательского порядка сортировки необходимо выполнить следующую последовательность: «данные» — «сортировка» — «порядок» — «пользовательский список». Появляется такое меню.
Как отсортировать данные в таблице Excel по столбцу?
У нас есть обучающая таблица со списком сотрудников и зарплат:
Отсортируйте зарплату в порядке убывания, от самой высокой к самой низкой. Выберите нужный столбец — правая кнопка мыши — параметр сортировки — от максимального значения к минимальному — введите:
Диапазон сортировки расширяется автоматически. Получается так:
Выберите столбец — правая кнопка мыши — на этот раз укажите: от минимального значения до максимального значения.
Как сортировать по алфавиту в Excel?
Выделите столбец с фамилиями — правая кнопка мыши — выберите: произвольная сортировка.
«Сортировать по» — выберите название столбца, значения которого будут отсортированы по алфавиту. У нас есть имя. Сортировкой мы выбираем, на какие параметры будет ориентироваться инструмент. Может быть цвет ячейки, шрифт, значок ячейки. В нашем примере «значения» (разбор значений в ячейке). «Заказ»: от А до Я или от Я до А. Давайте сделаем прямой заказ. Нажмите «ОК.
Результат алфавитной сортировки:
Как сделать сортировку по дате в Excel?
Нам необходимо классифицировать товары по дате их поступления на склад.
Убедитесь, что столбец даты имеет правильный формат («Дата»). Выбираем столбец и сортируем даты от старых к новым (от новых к старым).
Выбирайте от старого к новому. Значения списка расположены в хронологическом порядке.
Вы можете сортировать данные в диапазоне по дням недели, по месяцам. Для этого в меню «Пользовательская сортировка» выберите «Пользовательский список».
Сортируем данные по смыслу
Допустим, информацию в таблице нужно распределить по должностям, воинским званиям, удостоверениям, клиентам и активам в зависимости от их значимости для конкретной компании и т д. Для этого предназначены настраиваемые списки, которые можно составить самостоятельно.
Выберите новый список в меню Пользовательский список. Мы формируем иерархию любого объекта. Разделителями могут быть запятые или клавиша Enter».
Когда иерархические объекты введены, нажмите «Добавить».
А вот и результат сортировки обучающей таблицы по позициям:
Сортировка данных по нескольким столбцам
Если вам нужно отсортировать информацию по разным условиям в разных столбцах, используйте меню «Пользовательский список». Есть кнопка Добавить уровень».
Данные в таблице будут отсортированы по различным параметрам. В столбик, например, по возрастанию. В другом («тогда по…») — по алфавиту и т.д. В результате диапазон будет сформирован по заданной комплексной сортировке.
Уровни можно добавлять, удалять, копировать, обменивать с помощью кнопок «Вверх» — «Вниз».
Это самые простые способы сортировки данных в таблицах. Для этих целей можно использовать формулы. Для этих целей подходят встроенные инструменты «МАЛЕНЬКИЙ», «СТРОКА», СЧЁТЕСЛИ».