При сортировке в excel сбивается формулы - Word и Excel

User-MTU

Пользователь

Сообщений: 17
Регистрация: 01.01.1970

Сделал табличку. В результате работы создается список. Итоги сортируются встроенными средствами Excel. Если были внесены изменения, то итоги необходимо отсортировать повторно. Но возникла проблема — при сортировке портятся формулы в тех ячейках строк, которые участвовали в сортировке. Если вместо адресов ячеек стоят числа то порча не происходит. Но использовать числа нельзя, надо чтобы были адреса ячеек, так как числа меняются из-за внесенных результатов.
Как решить проблему? или из-за чего это происходит? Пробовали на 2 разных компьютерах — проблема есть.

Файлик приложен. В нем есть масса скрытых строк и столбцов, но они роли не играют — в них порча не происходит, они используются для облегчения ввода данных.
Что деламем в файлике:
Первоначальная сортировка уже проведена. К примеру меняем значение в ячейке AE49 с 23 на 456. Рассчет проходит нормально.Теперь надо провести сортировку по столбцу AZ с большего к меньшему. При сортировке почему то изменяется содержимое ячеек в столбце BA. Итог — неверный рассчет всего остального… Можно попробовать сортировать по другому столбцу — например по процентному — итог тот же — ломаются адреса ячеек в столбцах, правее столбца AZ. Но почему так?

VDM Пользователь Сообщений: 779 Регистрация: 22.12.2012	В ячейках столбца «BC» попробуйте заменить «=BD29» на «=$BD$29»

User-MTU

Пользователь

Сообщений: 17
Регистрация: 01.01.1970

Не, не помогает.

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

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	Файл не смотрел — ну не хочется в большой залезать, боюсь утонуть в лишнем мусоре Округление? Сортировка не всех столбцов? В формулах функции типа СТРОКА()?

VDM Пользователь Сообщений: 779 Регистрация: 22.12.2012	ну, да это я проглядел … здесь везде тоже конечно поменяйте =СРЗНАЧ(AZ31:AZ51) на =СРЗНАЧ($AZ$31:$AZ$51) и вот сюда тоже загляните: http://www.planetaexcel.ru/tip.php?aid=236

User-MTU

Пользователь

Сообщений: 17
Регистрация: 01.01.1970

Округление нормально работает, столбцы на сортировку подхватывает все.

Спасибо за подсказку VDM
Добавли в адреса ячеек символ $ правда не в столбце ВС а в столбце ВА. И оно при сортировке перестало самовольно менять адреса ячеек в формулах.
Все заработало, спасибо !!

User-MTU Пользователь Сообщений: 17 Регистрация: 01.01.1970	Ой, пока ответ сочинял, Вы уже подправили Символ $ указывает на запрет подмены адреса ячейки?

ikki Пользователь Сообщений: 9709 Регистрация: 22.12.2012	#8 10.02.2012 22:46:11 vikttur, нет. в каждой строке стоит формула = СРЗНАЧ(AZ31:AZ51), причем именно строки с 31-й по 51-ю и сортируются. имхо: в кач-ве диапазона функции попробовать указать нечто «сортировоутойчивое», например ИНДЕКС(AZ:AZ;31):ИНДЕКС(AZ:AZ;51) фрилансер Excel, VBA — контакты в профиле «Совершенствоваться не обязательно. Выживание — дело добровольное.» Э.Деминг

Источник

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

lema

Дата: Среда, 07.09.2016, 11:11 |
Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

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

К сообщению приложен файл:

6411903.xls
(25.5 Kb)

Ответить

pabchek

Дата: Среда, 07.09.2016, 11:31 |
Сообщение № 2

Группа: Проверенные

Ранг: Ветеран

Сообщений: 931

Репутация:
218
±

Замечаний:
0% ±

Excel 2007

Здравствуйте!
Сводной пойдет?

К сообщению приложен файл:

6060464.xls
(32.0 Kb)

«Учиться, учиться и еще раз учиться!»
WM: R399923528092

Ответить

lema

Дата: Среда, 07.09.2016, 12:22 |
Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Я даже не понимаю что это и как этим пользоваться.

Ответить

koyaanisqatsi

Дата: Среда, 07.09.2016, 14:34 |
Сообщение № 4

Группа: Проверенные

Ранг: Ветеран

Сообщений: 712

Репутация:
15
±

Замечаний:
0% ±

Excel 2010

lema, а как вы хотите сортировать столбец в котором не однородные данные, а данные перемешаны с вычислениями этих же самих данных ?

Ответить

Szekerfehesvar

Дата: Среда, 07.09.2016, 16:27 |
Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
6
±

Замечаний:
20% ±

Excel 2013

lema, попробуйте формулу ниже:

Код

=ИНДЕКС(B:B;ПОИСКПОЗ(A2+1;A:A;0);1)+ИНДЕКС(B:B;ПОИСКПОЗ(A2+2;A:A;0);1)

прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно

К сообщению приложен файл:

1198934.xls
(25.5 Kb)

Сообщение отредактировал Pelena — Среда, 07.09.2016, 17:03

Ответить

lema

Дата: Среда, 07.09.2016, 17:42 |
Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно

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

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

Ответить

buchlotnik

Дата: Среда, 07.09.2016, 19:24 |
Сообщение № 7

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

Цитата

при сортировке религия мелкософта не позволила?

вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие

Сообщение отредактировал buchlotnik — Среда, 07.09.2016, 19:25

Ответить

Szekerfehesvar

Дата: Четверг, 08.09.2016, 09:22 |
Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
6
±

Замечаний:
20% ±

Excel 2013

по формуле — ИНДЕКС() выискивает ячейку по ее адресу в диапазоне, что как раз подходит под Вашу задачу
ПОИСКПОЗ() — ищет в диапазоне ячейку по ее значению
в итоге получается
=ИНДЕКС( вводим диапазон, где находятся искомые данные — то есть столбец В в вашем случае; далее ищем строку, с которой нужно взять значение — ПОИСКПОЗ(значение, которое ищем — в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых;указываем диапазон, где искать наш порядковый номер — столбец А; указываем 0 — это даст нам поиск точных совпадений);указываем 1, так как в нашем диапазоне только 1 столбец)

Ответить

lema

Дата: Четверг, 08.09.2016, 12:46 |
Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

вообще-то это вам религия не позволила нормально организовать данные.

Интересно, если бы при вырезании тоже отсутствовала динамическая смена адресов, вы бы также говорили — тебе религия не позволяет нормально организовать данные!
А можно узнать, что ненормального в организации данных?
Как по вашему должны выглядеть нормально организованные данные?
Можете показать на примере, так правильно организованны данные, что при сортировке которых, примитивные и простые формулы (такие как сложить ячейки, вычесть, разделить и.д. ) не сбиваются без использования многосложных формул, макросов и т.д. Т.е. результат получен в чистом виде благодаря «нормальной» организации и только ей.
Спасибо.

Цитата

Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке?

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

Цитата

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

Действительно страшно! Думать вообще страшно.

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

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

Сообщение отредактировал lema — Четверг, 08.09.2016, 14:48

Ответить

buchlotnik

Дата: Четверг, 08.09.2016, 15:40 |
Сообщение № 10

Группа: Заблокированные

Ранг: Участник клуба

Сообщений: 3442

Репутация:
929
±

Замечаний:
20% ±

2010, 2013, 2016 RUS / ENG

lema, из всего вашего эмоционального поста главное

Цитата

Плевать по какому принципу

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

Цитата

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

. Майкрософту хватило мозгов В ПРИНЦИПЕ сделать Excel и они-то понимают разницу между перемещением диапазона и сортировкой. так напишите быстрый алгоритм и место в Майкрософте, а то и в Гугле вам гарантировано!

Цитата

Думать вообще страшно.

Жаль, что вам думать страшно. Только вот если бы вашу «хотелку» реализовали, сортировка занимала бы не долю секунды, а минуты — и вы бы пришли сюда с чем-то вроде «ребята, почему у мелкомягких сортировка минуты (часы) занимает? — у меня ж тут простые числовые данные»

Цитата

Можете показать на примере, так правильно организованные данные при сортировке которых, примитивные и простые формулы … не сбиваются

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

Цитата

результат получен в чистом виде благодаря «нормальной» организации

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

Сообщение отредактировал buchlotnik — Четверг, 08.09.2016, 15:55

Ответить

Manyasha

Дата: Четверг, 08.09.2016, 16:21 |
Сообщение № 11

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация:
898
±

Замечаний:
0% ±

Excel 2010, 2016

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

и данные более реальные бы посмотреть. Может кто-то предложит более удобную структуру.

ЯД: 410013299366744 WM: R193491431804

Ответить

Szekerfehesvar

Дата: Четверг, 08.09.2016, 17:08 |
Сообщение № 12

Группа: Пользователи

Ранг: Новичок

Сообщений: 29

Репутация:
6
±

Замечаний:
20% ±

Excel 2013

в общем с соседними столбцами дружить ни как нельзя.

Лучше уж тогда скиньте файл, в котором будет как минимум структура, как в реальном файле (то есть если идут разные коды, идентификаторы и прочее — пусть они будут как минимум по типу в файле-примере сходными, и т.д.). Мой вариант решения исходил из того, что у Вас в файле принцип построения схож с файловым, раз сама суть содержимого столбцов иная, то естественно мой вариант уже не подойдет. Было б гораздо проще, если бы был приведен настоящий файл в урезанном виде и без конфиденциальной инфы — ее можно заменить на какие то типовые аналоги (слова на слова, цифры на цифры)

Ответить

lema

Дата: Воскресенье, 11.09.2016, 11:47 |
Сообщение № 13

Группа: Пользователи

Ранг: Прохожий

Сообщений: 5

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Лучше уж тогда скиньте файл

Смысл следующий, есть строка с данными, она означает лист с контактами. Разберём на примере последнего (строка — 165):
Поступает лист с контактами, записываются данные касаемо этого листа, такие как дата его приёма, дата выдачи и т.д.
В ячейке F165 количество контактов на листе, в ячейке R165 пишется фамилия работника которому выдан лист, но бывает так, что лист делиться на 3 или 4 примерно равные части, и каждая часть раздаётся разным работникам, поэтому лист R165 становиться «комбо». Ни когда заранее не известно будет разбиваться лист или нет. Ниже как видим оранжевым отмечены строки из которых теперь этот лист состоит и естественно данные работника, каждого, который отчитывается по своему листу-кусочку (строка 166,167,168) должны поступать в главный лист (строка 165). Те самые данные которые должны суммироваться они отмечены зелёным, в них как раз находятся формулы. Бывает, что необходимо отсортировать листы по разным столбцам, отсюда и проблема.
Естественно, нужно чтобы процесс разбития был быстрым, не знаю, может каким-то шаблонным копированием формулы с незначительным внесением изменений, но уж точно не написанием вручную здоровенной формулы.

К сообщению приложен файл:

7231289.xls
(96.0 Kb)

Сообщение отредактировал lema — Воскресенье, 11.09.2016, 11:53

Ответить

Источник

0 / 0 / 0

Регистрация: 11.06.2015

Сообщений: 2

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

11.06.2015, 09:58. Показов 8629. Ответов 3

Добрый день!

Есть отчет, в нем список проектов с определенными данными (проекты и данные будут меняться каждую неделю, поэтому список не фиксированный). Необходимо было добавить 2 новых столбца, а именно: Суммарная оценка проекта = Типизация+Срочность+Стратегичность и Модуль максимального отклонения=модуль отклонения по срокам+модуль отклонения по трудозатратам. (Столбцы добавила, формула для них прописала на втором листе).
Необходимо, чтобы возможно было сортировать данные по столбцам Суммарная оценка и Модуль макс. отклонения (по убыванию), но после сортировки по любому из них — формула слетает и приходится восстанавливать ее и продливать на весь столбец.
Прошу помочь устранить данную ошибку, чтобы не приходилось делать восстановление вручную.

С уважением.

Чорумфанин

346 / 346 / 320

Регистрация: 06.03.2014

Сообщений: 899

11.06.2015, 11:44

Как вариант:
— перед сортировкой копировать всю таблицу на другой лист при помощи PasteSpecial Values
— сортируем

Если это надо делать часто, то можно сделать небольшой макрос, который будет это делать

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

11.06.2015, 13:27

Сообщение было отмечено LuxMi как решение

Решение

Почему сразу на листе не считаете?

0 / 0 / 0

Регистрация: 11.06.2015

Сообщений: 2

11.06.2015, 13:42

[ТС]

Благодарю! Я просто не могла придумать как по-другому посчитать суммарную оценку, нашла только такой способ, поэтому и счет модуля вынесла на др. страницу. Теперь все отлично работает, еще раз спасибо! Вопрос закрыт.

IT_Exp Эксперт 87844 / 49110 / 22898 Регистрация: 17.06.2006 Сообщений: 92,604	11.06.2015, 13:42
4

Источник

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

Сортируйте связанные данные и сохраняйте формулы с помощью ярлыков

Сортируйте связанные данные и сохраняйте формулы с помощью Kutools for Excel

Сортируйте связанные данные и сохраняйте формулы с помощью ярлыков

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

Выберите ячейку формулы, выберите формулу в строке формул и нажмите F4 Клавиша изменения ссылки на абсолютную ссылку.

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

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

Теперь формулы сохраняются при сортировке.

Сортируйте связанные данные и сохраняйте формулы с помощью Kutools for Excel

Изменение ссылок по очереди занимает много времени, но с Преобразовать ссылки полезность в Kutools for Excel, вы можете быстро изменить все ссылки в диапазоне абсолютных ссылок по мере необходимости.

После установки Kutools for Excel, пожалуйста, сделайте следующее:(Бесплатная загрузка Kutools for Excel Сейчас!)

1. Выберите ячейки, ссылку на которые вы хотите изменить, нажмите Кутулс > Подробнее (в группе Формула) > Преобразовать ссылки.

2. в Преобразование ссылок на формулы диалог, проверьте К абсолютному вариант. Смотрите скриншот:

3. Нажмите Ok. Затем весь выбор был преобразован в абсолютные ссылки.

4. Затем вы можете отсортировать данные.

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (5)

Оценок пока нет. Оцените первым!

Источник

I have tried googleing for an answer but could not find any answers so I try here.

The table below contains values in A and B columns, a formula formatted as text in the C column and the same formula formatted as a real formula (=A1+B1 etc.) which displays the result in column D.

    A     B     C     D
1   1     1   A1+B1   2
2   2     2   A2+B2   4
3   3     3   A3+B3   6

If I then sort the table on A in descending order so it looks like below:

    A     B     C     D
1   3     3   A1+B1   6
2   2     2   A2+B2   4
3   1     1   A3+B3   2

Are the formulas recalculated when sorting?
Dealing with small tables or sheets such as this it does not really matter — but if the file contains more than a couple of thousands rows and complex formulas recalculating should be kind of slow. Is it a good idea to copy D and paste as values in D before sorting if I am finished editing the formula?

asked Apr 6, 2017 at 11:27

Calculation does seem to be triggered when sorting.

You can as you state copy/paste values, but another way to get around this, is to enable manual calculation. You can do this at:

File —> Options —> Formulas —> Manual Calculation

This way you can choose by yourself when to calculate the formulas by pressing F9.
I would not leave this on indefinitely, since it can give a wrong view on the data, but when manipulating the data, this feature can come in handy.

answered Apr 6, 2017 at 11:37