Excel при сортировке сбиваются формулы

Обновлено: 04.07.2024

Office 365 ProPlus переименован в Майкрософт 365 корпоративные приложения. Для получения дополнительной информации об этом изменении прочитайте этот блог.

Симптомы

Эта операция требует одинакового размера объединенных ячеек.

Причина

Эта проблема может возникнуть при сортировке ряда ячеек, а также при следующих условиях:

Вы ранее объединены некоторые из ячеек, но не все из ячеек в диапазоне сортировки.
Вы ранее слили все ячейки в диапазоне сортировки, и ячейки не имеют одинаковых размеров.

Обходной путь

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

Общее решение

Выберите весь диапазон, который необходимо сортировать.

В группе Выравнивание на вкладке Главная выберите диалоговое окно Выравнивание.

Выберите вкладку Выравнивание, а затем очистим поле ячейки Merge.

Это может изменить расположение данных в диапазоне.

Пример решения

В таблицу введите следующие данные:

Объединение ячеек A1 и B1, A2 и B2, а также A3 и B3. Для этого выполните следующие действия:

Выберите каждую пару ячеек.

Выберите диалоговое окно Выравнивание в группе Выравнивание на вкладке Главная.

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

Не объединяй ячейки в столбце C.

Выберите ячейки A1:C3, выберите фильтр сортировки & в группе редактирования на вкладке Главная, а затем нажмите настраиваемый сорт.

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

Отключайте автоперерасчет на время сортировки. С макросами-эвентами сложнее, но тоже решаемо.

А вообще - не надо сортировать таблицы-источники
Правильный подход - это последовательное наполнение источников данными, а для всяких "выходных красивостей" вы просто должны создавать выборку/отчет/копию данных (и только данных, безо всяких формул внутри полученных таблиц!), и уже их оформлять как требуется.

Отключайте автоперерасчет на время сортировки. С макросами-эвентами сложнее, но тоже решаемо.

Суть формул в том, что задается кол-во дней, начало срока (привязано к окончанию предыдущего срока + один день) и окончание срока (начало срока + заданное кол-во дней)

Подскажите, как правильно делать сортировку, чтобы привязка формул осталась на месте?
Например, если отсортировать по полю Сумма, то чтобы дата начала Сидорова осталась привязана к дате окончания Петрова.

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

с текстом ниже не соглашусь:

The_Prist
И отследить взаимосвязь формул в такой мешанине весьма трудозатратно. Если бы это было реализовано - даже 100 строк скорее всего сортировались бы непомерно долго.

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

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

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

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

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

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

нуб987
странно было слышать мнение, что екселю трудно сортировать формулы. Ему не трудно, отвечаю.

мне не интересны ни удивления, ни убежденности.
Это вообще-то не вопрос.
Возьми, да научи его, как надо.
Для персонально твоего, маленького случая, это не составит сложности даже и особенно для тебя.

нуб987
Тогда да, придется самому копировать данные, сортировать, а потом ручонками писать формулы обратно.

вот про ручонки - безоговорочно верю.
Не требуя доказательств.

нужно отсортировать таблицу по одному из полей (по ФИО, например), изменить какие-то даты или кол-во дней, а потом вернуть сортировку обратно
при этом формулы дат ссылаются на предыдущие ячейки в своем столбце

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

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

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

В этой статье

Проблемы, которые приводят к существенной потере функциональности

При сортировке листа книги использовано более трех условий сортировки. В более ранних версиях Excel эта информация будет потеряна.

Что это означает. В Excel 2007 г. или более поздней 2007 г. для сортировки данных можно применить до 64 условий сортировки, но в Excel 97–2003 поддерживается только три состояния сортировки. Чтобы исключить потерю сведений об условиях сортировки в Excel 97–2003, может потребоваться сокращение количества условий сортировки до трех. В Excel 97–2003 вы также можете сортировать данные вручную.

Однако все сведения о состоянии сортировки остаются доступными в книге и применяются при повторном ее повторном открыть в Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.

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

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

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

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

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

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

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

Проблемы, которые приводят к небольшой потере точности

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

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

Однако все сведения о состоянии фильтра остаются доступными в книге и применяются при повторном ее повторном Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.

Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти отфильтрованные данные, после чего очистите фильтр для отображения скрытых строк. Чтобы очистить фильтр, на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр и выберите команду Очистить.

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

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

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

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

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

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

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

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

Группировку данных также можно отключить на вкладке "Дополнительные параметры" Excel Параметры. (Файл, параметры).

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