При сортировке в excel сбивается формулы
Сделал табличку. В результате работы создается список. Итоги сортируются встроенными средствами Excel. Если были внесены изменения, то итоги необходимо отсортировать повторно. Но возникла проблема — при сортировке портятся формулы в тех ячейках строк, которые участвовали в сортировке. Если вместо адресов ячеек стоят числа то порча не происходит. Но использовать числа нельзя, надо чтобы были адреса ячеек, так как числа меняются из-за внесенных результатов.
Как решить проблему? или из-за чего это происходит? Пробовали на 2 разных компьютерах — проблема есть.
Файлик приложен. В нем есть масса скрытых строк и столбцов, но они роли не играют — в них порча не происходит, они используются для облегчения ввода данных.
Что деламем в файлике:
Первоначальная сортировка уже проведена. К примеру меняем значение в ячейке AE49 с 23 на 456. Рассчет проходит нормально.Теперь надо провести сортировку по столбцу AZ с большего к меньшему. При сортировке почему то изменяется содержимое ячеек в столбце BA. Итог — неверный рассчет всего остального… Можно попробовать сортировать по другому столбцу — например по процентному — итог тот же — ломаются адреса ячеек в столбцах, правее столбца AZ. Но почему так?
Сообщений: 779
Регистрация: 22.12.2012
В ячейках столбца «BC» попробуйте заменить «=BD29» на «=$BD$29»
Сообщений: 17
Регистрация: 01.01.1970
Я вот пытаюсь понять, думается что проблема в рассчете среднего арифметического вкупе с сортировкой. Такое ощущение, что после каждого шага сортировки, что-то пересчитывается со средним арифметическим не так, что приводит к затыку…
Сообщений: 47199
Регистрация: 15.09.2012
Файл не смотрел — ну не хочется в большой залезать, боюсь утонуть в лишнем мусоре 
Округление?
Сортировка не всех столбцов?
В формулах функции типа СТРОКА()?
Сообщений: 779
Регистрация: 22.12.2012
ну, да это я проглядел … здесь везде тоже конечно поменяйте =СРЗНАЧ(AZ31:AZ51) на =СРЗНАЧ($AZ$31:$AZ$51) и вот сюда тоже загляните:
Сообщений: 17
Регистрация: 01.01.1970
Округление нормально работает, столбцы на сортировку подхватывает все.
Спасибо за подсказку VDM
Добавли в адреса ячеек символ $ правда не в столбце ВС а в столбце ВА. И оно при сортировке перестало самовольно менять адреса ячеек в формулах.
Все заработало, спасибо !!
Сообщений: 17
Регистрация: 01.01.1970
Ой, пока ответ сочинял, Вы уже подправили 
Символ $ указывает на запрет подмены адреса ячейки?
Сообщений: 9709
Регистрация: 22.12.2012
vikttur, нет.
в каждой строке стоит формула = СРЗНАЧ(AZ31:AZ51), причем именно строки с 31-й по 51-ю и сортируются.
имхо: в кач-ве диапазона функции попробовать указать нечто «сортировоутойчивое», например ИНДЕКС(AZ:AZ;31):ИНДЕКС(AZ:AZ;51)
фрилансер Excel, VBA — контакты в профиле
«Совершенствоваться не обязательно. Выживание — дело добровольное.» Э.Деминг
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Замечаний:
0% ±
Excel 2010
Суть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами.
Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда.
Прилагаю пример, сортируете столбец B от «А до Я» и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально.
К сообщению приложен файл:
6411903.xls
(25.5 Kb)
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация:
218
±
Замечаний:
0% ±
Excel 2007
К сообщению приложен файл:
6060464.xls
(32.0 Kb)
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Замечаний:
0% ±
Excel 2010
Я даже не понимаю что это и как этим пользоваться.
Группа: Проверенные
Ранг: Ветеран
Сообщений: 712
Замечаний:
0% ±
Excel 2010
lema, а как вы хотите сортировать столбец в котором не однородные данные, а данные перемешаны с вычислениями этих же самих данных ?
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Замечаний:
20% ±
Excel 2013
lema, попробуйте формулу ниже:
прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно
К сообщению приложен файл:
1198934.xls
(25.5 Kb)
Сообщение отредактировал Pelena — Среда, 07.09.2016, 17:03
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Замечаний:
0% ±
Excel 2010
прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно.
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация:
929
±
Замечаний:
20% ±
2010, 2013, 2016 RUS / ENG
при сортировке религия мелкософта не позволила?
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие
Сообщение отредактировал buchlotnik — Среда, 07.09.2016, 19:25
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Замечаний:
20% ±
Excel 2013
по формуле — ИНДЕКС() выискивает ячейку по ее адресу в диапазоне, что как раз подходит под Вашу задачу
ПОИСКПОЗ() — ищет в диапазоне ячейку по ее значению
в итоге получается
=ИНДЕКС( вводим диапазон, где находятся искомые данные — то есть столбец В в вашем случае; далее ищем строку, с которой нужно взять значение — ПОИСКПОЗ(значение, которое ищем — в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых;указываем диапазон, где искать наш порядковый номер — столбец А; указываем 0 — это даст нам поиск точных совпадений);указываем 1, так как в нашем диапазоне только 1 столбец)
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Замечаний:
0% ±
Excel 2010
вообще-то это вам религия не позволила нормально организовать данные.
Интересно, если бы при вырезании тоже отсутствовала динамическая смена адресов, вы бы также говорили — тебе религия не позволяет нормально организовать данные!
А можно узнать, что ненормального в организации данных?
Как по вашему должны выглядеть нормально организованные данные?
Можете показать на примере, так правильно организованны данные, что при сортировке которых, примитивные и простые формулы (такие как сложить ячейки, вычесть, разделить и.д. ) не сбиваются без использования многосложных формул, макросов и т.д. Т.е. результат получен в чистом виде благодаря «нормальной» организации и только ей.
Спасибо.
Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке?
Нет. Зачем мне так считать? Плевать по какому принципу происходит смена адреса при вырезании и при сортировке. Не вижу преграды для того, что бы программа при сортировке зная что ячейка А1 после сортирвки становится например ячейкой А7, поменять её автоматически в формуле на А7, если это же было возможно сделать при вырезании и не только при вырезании, а даже при вставке новых строк и столбцов.
Страшно подумать сколько времени занимало бы данное мероприятие
Действительно страшно! Думать вообще страшно.
значение, которое ищем — в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых
К сожалению это не универсально, потому как порядковые номера я привёл чисто из примера, на самом деле там могут быть абсолютно любые данные без всяких закономерностей не только цифры но и текст. Зависимость от столбца А не подходит. И ещё, под желтыми строками может быть любое количество белых строк из которых для суммирования берутся данные, там закономерности тоже нет. в общем с соседними столбцами дружить ни как нельзя.
Сообщение отредактировал lema — Четверг, 08.09.2016, 14:48
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация:
929
±
Замечаний:
20% ±
2010, 2013, 2016 RUS / ENG
lema, из всего вашего эмоционального поста главное
Плевать по какому принципу
Так вот, на дружественном форуме есть человек, у которого в подписи замечательная фраза
Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
. Майкрософту хватило мозгов В ПРИНЦИПЕ сделать Excel и они-то понимают разницу между перемещением диапазона и сортировкой. так напишите быстрый алгоритм и место в Майкрософте, а то и в Гугле вам гарантировано!
Думать вообще страшно.
Жаль, что вам думать страшно. Только вот если бы вашу «хотелку» реализовали, сортировка занимала бы не долю секунды, а минуты — и вы бы пришли сюда с чем-то вроде «ребята, почему у мелкомягких сортировка минуты (часы) занимает? — у меня ж тут простые числовые данные»
Можете показать на примере, так правильно организованные данные при сортировке которых, примитивные и простые формулы … не сбиваются
нет таких примеров — вам об этом сказали в четвёртом посте — в столбце для сортировки должны быть однородные данные — либо значения, либо одинаковые формулы. И если бы вы не пихали всё в один столбец не пришлось бы танцевать с бубном изобретая сложные формулы или прибегать к макросам.
результат получен в чистом виде благодаря «нормальной» организации
озвучте конечную цель сортировки. Возможно, это не единственный приём для решения вашей задачи
Сообщение отредактировал buchlotnik — Четверг, 08.09.2016, 15:55
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация:
898
±
Замечаний:
0% ±
Excel 2010, 2016
озвучте конечную цель сортировки. Возможно, это не единственный приём для решения вашей задачи
и данные более реальные бы посмотреть. Может кто-то предложит более удобную структуру.
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Замечаний:
20% ±
Excel 2013
в общем с соседними столбцами дружить ни как нельзя.
Лучше уж тогда скиньте файл, в котором будет как минимум структура, как в реальном файле (то есть если идут разные коды, идентификаторы и прочее — пусть они будут как минимум по типу в файле-примере сходными, и т.д.). Мой вариант решения исходил из того, что у Вас в файле принцип построения схож с файловым, раз сама суть содержимого столбцов иная, то естественно мой вариант уже не подойдет. Было б гораздо проще, если бы был приведен настоящий файл в урезанном виде и без конфиденциальной инфы — ее можно заменить на какие то типовые аналоги (слова на слова, цифры на цифры)
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Замечаний:
0% ±
Excel 2010
Лучше уж тогда скиньте файл
Смысл следующий, есть строка с данными, она означает лист с контактами. Разберём на примере последнего (строка — 165):
Поступает лист с контактами, записываются данные касаемо этого листа, такие как дата его приёма, дата выдачи и т.д.
В ячейке F165 количество контактов на листе, в ячейке R165 пишется фамилия работника которому выдан лист, но бывает так, что лист делиться на 3 или 4 примерно равные части, и каждая часть раздаётся разным работникам, поэтому лист R165 становиться «комбо». Ни когда заранее не известно будет разбиваться лист или нет. Ниже как видим оранжевым отмечены строки из которых теперь этот лист состоит и естественно данные работника, каждого, который отчитывается по своему листу-кусочку (строка 166,167,168) должны поступать в главный лист (строка 165). Те самые данные которые должны суммироваться они отмечены зелёным, в них как раз находятся формулы. Бывает, что необходимо отсортировать листы по разным столбцам, отсюда и проблема.
Естественно, нужно чтобы процесс разбития был быстрым, не знаю, может каким-то шаблонным копированием формулы с незначительным внесением изменений, но уж точно не написанием вручную здоровенной формулы.
К сообщению приложен файл:
7231289.xls
(96.0 Kb)
Сообщение отредактировал lema — Воскресенье, 11.09.2016, 11:53
Слетает формула при сортировке
11.06.2015, 09:58. Показов 8629. Ответов 3
Есть отчет, в нем список проектов с определенными данными (проекты и данные будут меняться каждую неделю, поэтому список не фиксированный). Необходимо было добавить 2 новых столбца, а именно: Суммарная оценка проекта = Типизация+Срочность+Стратегичность и Модуль максимального отклонения=модуль отклонения по срокам+модуль отклонения по трудозатратам. (Столбцы добавила, формула для них прописала на втором листе).
Необходимо, чтобы возможно было сортировать данные по столбцам Суммарная оценка и Модуль макс. отклонения (по убыванию), но после сортировки по любому из них — формула слетает и приходится восстанавливать ее и продливать на весь столбец.
Прошу помочь устранить данную ошибку, чтобы не приходилось делать восстановление вручную.
Как вариант:
— перед сортировкой копировать всю таблицу на другой лист при помощи PasteSpecial Values
— сортируем
Если это надо делать часто, то можно сделать небольшой макрос, который будет это делать
5942 / 3154 / 698
Сообщение было отмечено LuxMi как решение
Решение
Почему сразу на листе не считаете?
Благодарю! Я просто не могла придумать как по-другому посчитать суммарную оценку, нашла только такой способ, поэтому и счет модуля вынесла на др. страницу. Теперь все отлично работает, еще раз спасибо! Вопрос закрыт.
87844 / 49110 / 22898
Если список ячеек имеет несколько формул или связан с другими ячейками на том же листе, связанные данные будут изменены при сортировке ячеек, как показано ниже. В этой статье я расскажу о методах сортировки связанных данных и хранения формул в Excel.
Сортируйте связанные данные и сохраняйте формулы с помощью ярлыков
Сортируйте связанные данные и сохраняйте формулы с помощью Kutools for Excel![]()
Сортируйте связанные данные и сохраняйте формулы с помощью ярлыков
Чтобы отсортировать связанные данные и сохранить формулы без изменений, вы можете изменить ссылки в формулах на абсолютные ссылки, а затем отсортировать данные. Таким образом, данные сохранят формулы, даже если их порядок изменится.
Выберите ячейку формулы, выберите формулу в строке формул и нажмите F4 Клавиша изменения ссылки на абсолютную ссылку.
Функции: Если в одной ячейке несколько ссылок, вам нужно изменить ссылки одну за другой, выбрав и нажав F4 в строке формул.
Затем поочередно измените ссылки в других ячейках формулы на абсолютные.
Теперь формулы сохраняются при сортировке.
Сортируйте связанные данные и сохраняйте формулы с помощью Kutools for Excel
Изменение ссылок по очереди занимает много времени, но с Преобразовать ссылки полезность в Kutools for Excel, вы можете быстро изменить все ссылки в диапазоне абсолютных ссылок по мере необходимости.
После установки Kutools for Excel, пожалуйста, сделайте следующее: (Бесплатная загрузка Kutools for Excel Сейчас!)
1. Выберите ячейки, ссылку на которые вы хотите изменить, нажмите Кутулс > Подробнее (в группе Формула) > Преобразовать ссылки.
2. в Преобразование ссылок на формулы диалог, проверьте К абсолютному вариант. Смотрите скриншот:
3. Нажмите Ok. Затем весь выбор был преобразован в абсолютные ссылки.
Почему при сортировке в excel меняется формула
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно.
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно. lema
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно. Автор — lema
Дата добавления — 07.09.2016 в 17:42
Почему при сортировке в excel меняется формула
Как отсортировать связанные данные и сохранить формулы в Excel?
Если список ячеек имеет несколько формул или связан с другими ячейками на том же листе, связанные данные будут изменены при сортировке ячеек, как показано ниже. В этой статье я расскажу о методах сортировки связанных данных и хранения формул в Excel.
![]() |
![]() |
![]() |
Сортировка связанных данных и сохранение формул с помощью Kutools for Excel
![]()
Сортируйте связанные данные и сохраняйте формулы с помощью ярлыков
Чтобы отсортировать связанные данные и сохранить формулы без изменений, вы можете изменить ссылки в формулах на абсолютные ссылки, а затем отсортировать данные. Таким образом, данные сохранят формулы, даже если их порядок изменится.
Выберите ячейку формулы, выберите формулу в строке формул и нажмите F4 Клавиша изменения ссылки на абсолютную ссылку.
Функции: Если в одной ячейке несколько ссылок, вам нужно изменить ссылки одну за другой, выбрав и нажав F4 в строке формул.
Затем поочередно измените ссылки в других ячейках формулы на абсолютные.
![]() |
![]() |
![]() |
Теперь формулы сохраняются при сортировке.
Сортировка связанных данных и сохранение формул с помощью Kutools for Excel
Изменение ссылок по очереди занимает много времени, но с Преобразовать ссылки полезность в Kutools for Excel, вы можете быстро изменить все ссылки в диапазоне абсолютных ссылок по мере необходимости.
После установки Kutools for Excel, сделайте следующее: (Бесплатная загрузка Kutools for Excel прямо сейчас!)
1. Выберите ячейки, ссылку на которые вы хотите изменить, нажмите Кутулс > Подробнее (в группе Формула) > Преобразовать ссылки.
2. в Преобразование ссылок на формулы диалог, проверьте К абсолютному вариант. Смотрите скриншот:
3. Нажмите Ok. Затем весь выбор был преобразован в абсолютные ссылки.
Почему не сортируется столбец в excel

Если данные текстовые, их можно отсортировать по алфавиту («от А до Я» или «от Я до А»). Если данные числовые, их можно отсортировать в порядке возрастания или убывания. Если в диапазоне данных есть строка или столбец, в которых содержатся данные типа время или дата, их можно отсортировать в прямом или обратном хронологическом порядке. Имеется также возможность сортировки предварительно отформатированных данных по элементам этого форматирования.
Сортировать данные можно по одному условию (например, сортировка списка сотрудников по фамилии) или нескольким (например, сортировка списка сотрудников по занимаемой должности, а внутри каждой должности фамилии отсортировать в алфавитном порядке). Данные можно сортировать по столбцу (или нескольким столбцам) или по строке.
Сортировка по одному критерию
- В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
- На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter].

- Выбрать нужную кнопку:
– сортировка по возрастанию или
сортировка по убыванию.
Отметим, что буквы на этой кнопке указывают только на направление сортировки, а вид кнопки остается один и тот же и при текстовых, и при числовых данных.
Существует и другой удобный способ сортировки данных: щелкнув правой кнопкой мыши по ячейке столбца, по которому будет выполняться сортировка, в контекстном меню выбрать пункт Сортировка [Sort], а далее – требуемый вариант сортировки.
Многоуровневая сортировка
- Выделить одну ячейку из сортируемого массива данных.
Если диапазоне данных имеются пустые столбцы или строкой, то Excel автоматически воспринимает их как границы сортируемого массива данных. В таком случае следует выделить все данные, подлежащие сортировке.

- На вкладке Данные [Data] найти группу команд Сортировка и фильтр [Sort&Filter] и на ней выбрать команду Сортировка [Sort].
- Последовательно задать уровни сортировки (определяемые именем столбца).
Нажимая на стрелку возле трех полей (Столбец, Сортировка, Порядок) необходимо выбрать:
- Имя столбца для сортировки.
- Тип критерия (в зависимости от того, будет ли вестись сортировка по значениям данных в столбце, или по оформлению ячейки, или по значку ячейки).
- Порядок сортировки (по убыванию или по возрастанию).
Если выбранный для сортировки столбец содержит названия месяцев или дней недели, то в списке поля Порядок можно выбрать опцию Настраиваемый список и в новом окне отметить один из предлагаемых вариантов сортировки.

Сортировка по форматированию
Часто для анализа данных делается заливка ячеек (или шрифта) цветом. С помощью сортировки можно также упорядочивать данные на основе их форматирования.
Пошаговый порядок действий:
- Щелкнуть по любой ячейки из столбца, по которому будет выполняться сортировка.
- На вкладке Данные [Data] выбрать группу Сортировка и фильтр [Sort&Filter], а затем выбрать команду Сортировка [Sort].
- В поле Столбец [Column] укажите столбец по которому будет проводиться сортировка.
- В поле Сортировка [Sort On] из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
- Поле Порядок [Order] содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором – размещение ячеек, отсортированных по данному критерию (строку Сверху [On Top] или Снизу [On Bottom]).
- При необходимости добавить еще один критерий сортировки, в окне Сортировка нужно выбрать кнопку Добавить уровень.

Можно также воспользоваться командой «Копировать уровень» [Copy Level], заменив в поле «Порядок» прежнее значение на новое.
При сортировке данных по алфавиту в Экселе могут возникнуть проблемы.
Например, список не сортируется или сортируется некорректно.
В этом случае необходимо проверить формат всех (или «проблемных») ячеек сортируемого столбца.
Формат должен быть одинаковым для всех его ячеек — Общий, Числовой или Текстовый.
Например, если в столбце есть ячейки разных форматов, то при сортировке ячейки, содержащие числа в числовом формате (несмотря на их значения), окажутся выше ячеек с числами в текстовом формате.
Чтобы решить эту проблему нужно выделить весь столбец и для всех его ячеек указать один формат.
Еще одной причиной может стать пробел в начале слова или числа. Особенно этот момент нужно проверить в случае импортирования данных из других программ.
Представляем вам гостевой пост, из которого вы узнаете, как избежать самых распространённых проблем с Excel, которые мы создаём себе сами.
Читатели Лайфхакера уже знакомы с Денисом Батьяновым, который делился с нами секретами Excel. Сегодня Денис расскажет о том, как избежать самых распространённых проблем с Excel, которые мы зачастую создаём себе самостоятельно.
Сразу оговорюсь, что материал статьи предназначается для начинающих пользователей Excel. Опытные пользователи уже зажигательно станцевали на этих граблях не раз, поэтому моя задача уберечь от этого молодых и неискушённых «танцоров».
Вы не даёте заголовки столбцам таблиц
Многие инструменты Excel, например: сортировка, фильтрация, умные таблицы, сводные таблицы, — подразумевают, что ваши данные содержат заголовки столбцов. В противном случае вы либо вообще не сможете ими воспользоваться, либо они отработают не совсем корректно. Всегда заботьтесь, чтобы ваши таблицы содержали заголовки столбцов.
Пустые столбцы и строки внутри ваших таблиц
Это сбивает с толку Excel. Встретив пустую строку или столбец внутри вашей таблицы, он начинает думать, что у вас 2 таблицы, а не одна. Вам придётся постоянно его поправлять. Также не стоит скрывать ненужные вам строки/столбцы внутри таблицы, лучше удалите их.
На одном листе располагается несколько таблиц
Если это не крошечные таблицы, содержащие справочники значений, то так делать не стоит.
Вам будет неудобно полноценно работать больше чем с одной таблицей на листе. Например, если одна таблица располагается слева, а вторая справа, то фильтрация одной таблицы будет влиять и на другую. Если таблицы расположены одна под другой, то невозможно воспользоваться закреплением областей, а также одну из таблиц придётся постоянно искать и производить лишние манипуляции, чтобы встать на неё табличным курсором. Оно вам надо?
Данные одного типа искусственно располагаются в разных столбцах
Очень часто пользователи, которые знают Excel достаточно поверхностно, отдают предпочтение такому формату таблицы:

Казалось бы, перед нами безобидный формат для накопления информации по продажам агентов и их штрафах. Подобная компоновка таблицы хорошо воспринимается человеком визуально, так как она компактна. Однако, поверьте, что это сущий кошмар — пытаться извлекать из таких таблиц данные и получать промежуточные итоги (агрегировать информацию).
Дело в том, что данный формат содержит 2 измерения: чтобы найти что-то в таблице, вы должны определиться со строкой, перебирая филиал, группу и агента. Когда вы найдёте нужную стоку, то потом придётся искать уже нужный столбец, так как их тут много. И эта «двухмерность» сильно усложняет работу с такой таблицей и для стандартных инструментов Excel — формул и сводных таблиц.
Если вы построите сводную таблицу, то обнаружите, что нет возможности легко получить данные по году или кварталу, так как показатели разнесены по разным полям. У вас нет одного поля по объёму продаж, которым можно удобно манипулировать, а есть 12 отдельных полей. Придётся создавать руками отдельные вычисляемые поля для кварталов и года, хотя, будь это всё в одном столбце, сводная таблица сделала бы это за вас.
Если вы захотите применить стандартные формулы суммирования типа СУММЕСЛИ (SUMIF), СУММЕСЛИМН (SUMIFS), СУММПРОИЗВ (SUMPRODUCT), то также обнаружите, что они не смогут эффективно работать с такой компоновкой таблицы.
Рекомендуемый формат таблицы выглядит так:

Разнесение информации по разным листам книги «для удобства»
Ещё одна распространенная ошибка — это, имея какой-то стандартный формат таблицы и нуждаясь в аналитике на основе этих данных, разносить её по отдельным листам книги Excel. Например, часто создают отдельные листы на каждый месяц или год. В результате объём работы по анализу данных фактически умножается на число созданных листов. Не надо так делать. Накапливайте информацию на ОДНОМ листе.
Информация в комментариях
Часто пользователи добавляют важную информацию, которая может им понадобиться, в комментарий к ячейке. Имейте в виду, то, что находится в комментариях, вы можете только посмотреть (если найдёте). Вытащить это в ячейку затруднительно. Рекомендую лучше выделить отдельный столбец для комментариев.
Бардак с форматированием
Определённо не добавит вашей таблице ничего хорошего. Это выглядит отталкивающе для людей, которые пользуются вашими таблицами. В лучшем случае этому не придадут значения, в худшем — подумают, что вы не организованы и неряшливы в делах. Стремитесь к следующему:
- Каждая таблица должна иметь однородное форматирование. Пользуйтесь форматированием умных таблиц. Для сброса старого форматирования используйте стиль ячеек «Обычный».

- Не выделяйте цветом строку или столбец целиком. Выделите стилем конкретную ячейку или диапазон. Предусмотрите «легенду» вашего выделения. Если вы выделяете ячейки, чтобы в дальнейшем произвести с ними какие-то операции, то цвет не лучшее решение. Хоть сортировка по цвету и появилась в Excel 2007, а в 2010-м — фильтрация по цвету, но наличие отдельного столбца с чётким значением для последующей фильтрации/сортировки всё равно предпочтительнее. Цвет — вещь небезусловная. В сводную таблицу, например, вы его не затащите.
- Заведите привычку добавлять в ваши таблицы автоматические фильтры (Ctrl+Shift+L), закрепление областей. Таблицу желательно сортировать. Лично меня всегда приводило в бешенство, когда я получал каждую неделю от человека, ответственного за проект, таблицу, где не было фильтров и закрепления областей. Помните, что подобные «мелочи» запоминаются очень надолго.
Объединение ячеек
Используйте объединение ячеек только тогда, когда без него никак. Объединенные ячейки сильно затрудняют манипулирование диапазонами, в которые они входят. Возникают проблемы при перемещении ячеек, при вставке ячеек и т.д.
Объединение текста и чисел в одной ячейке
Тягостное впечатление производит ячейка, содержащая число, дополненное сзади текстовой константой « РУБ.» или » USD», введенной вручную. Особенно, если это не печатная форма, а обычная таблица. Арифметические операции с такими ячейками естественно невозможны.
Числа в виде текста в ячейке
Избегайте хранить числовые данные в ячейке в формате текста. Со временем часть ячеек в таком столбце у вас будут иметь текстовый формат, а часть в обычном. Из-за этого будут проблемы с формулами.
Если ваша таблица будет презентоваться через LCD проектор
Выбирайте максимально контрастные комбинации цвета и фона. Хорошо выглядит на проекторе тёмный фон и светлые буквы. Самое ужасное впечатление производит красный на чёрном и наоборот. Это сочетание крайне неконтрастно выглядит на проекторе — избегайте его.

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

Сортировка в Excel применяется для визуализации данных и их упорядочивания, что существенно облегчает восприятие информации, представленной в табличной форме. Необходимость в этой функции возникает при работе с бухгалтерскими ведомостями, списками складских запасов и строительными сметами.
Часто проблемой может стать вопрос расположения чисел от большего к меньшему или наоборот. На самом деле критерии для упорядочивания информации в Экселе бывают разными: дата, время, цвет ячеек или тип шрифта. Чаще всего при изучении приемов работы с электронными таблицами рассматриваются примеры, где представлены списки сотрудников или товаров, так как сортировать по алфавиту на практике приходится очень часто. Рассортировать в программе можно по двум различным параметрам.
Как отсортировать по алфавиту
Поскольку в большинстве таблиц числовые параметры привязаны к какому-либо тексту — фамилии или названию товара то удобно расположить по алфавиту. Рассмотрим особенности работы с этим инструментом в редакторе Еxcel на примере таблицы «Содержание питательных веществ во фруктах и овощах».
Для того чтобы отсортировать названия овощей и фруктов по алфавиту необходимо выделить первый столбец, кликнув мышью по его заголовку. Далее требуется перейти на панель инструментов «Редактирование» и открыть вкладку «Главная», на которой расположена специальная кнопка «Сортировка и фильтр». Для упорядочивания названий в первом столбце от А до Я или наоборот, достаточно выбрать соответствующую команду сортировки в выпадающем меню. Если в таблицу Еxcel будут добавлены данные о других продуктах сработает автоматическая сортировка.

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

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

Чтобы рассортировать строки таблицы по дате в редакторе Эксель нужно выполнить следующую последовательность действий:
- Выделить любую ячейку в столбце с датами, которые требуется упорядочить.
- Во вкладке «Главная» найти кнопку «Сортировка и фильтр», навести на нее курсор и выбрать одно из предложенных действий в выпадающем списке. Это может быть распределение от новых к старым, когда в начале списка будут находится наиболее поздние даты и привязанные к ним значения, либо наоборот от старых к новым.
Сортировка по цвету ячейки и по шрифту
В случае если определенный диапазон табличных данных отформатирован с использованием шрифта различных цветов или заливки, пользователь может рассортировать строки по цвету, в который окрашена часть ячеек. Упорядочить данные можно также по набору значков, при создании которых применялось условное форматирование. В любом случае это можно сделать следующим образом:

- Курсором мыши нужно выбрать одну из ячеек с данными в нужном столбце.
- На вкладке «Главная» в функциональной группе «Редактирование» следует найти кнопку «Сортировка и фильтрация» и выбрать в дополнительном меню команду «Настраиваемая сортировка».
- В открывшемся окне сначала указать столбец, в котором нужно упорядочить данные, а потом тип сортировки. Это может быть цвет ячейки, цвет шрифта или значок условного форматирования.
- В зависимости от выбранного вида сортировки в группе порядок необходимо отметить нужный значок либо оттенок заливки или шрифта.
- Последний параметр, который нужно указать – это последовательность расположения (сверху или снизу).
Поскольку определенный порядок значков или цветов в редакторе не предусмотрен, его нужно создать самостоятельно. Для этого следует необходимо нажать кнопку «Добавить уровень» и затем повторить указанные шаги для каждого цвета или значка отдельно, исключая те, которые не нужно включать в сортировку,
Сортировка в Excel по нескольким столбцам
Если возникла необходимость отсортировать данные в редакторе Эксель по двум или более столбцам, следует так же, как и в предыдущем случае выбрать диапазон данных и открыть окно «Настраиваемая сортировка». Далее в первой группе следует отметить заголовок столбца, данные в котором нужно упорядочить в первую очередь. Вторая группа остается без изменений, а в третьей необходимо указать желаемый тип сортировки.

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

- Если информация в ячейках столбца представлена числами, используются функции НАИМЕНЬШИЙ и СТРОКА. Первая находит наименьший элемент из массива, а вторая определяет порядковый номер строки. Таким образом формируется последовательность. Формула записывается следующим образом: =НАИМЕНЬШИЙ(A:A;СТРОКА(A1)).
- Когда в ячейках содержаться текст, первая формула не сработает. Для сортировки в этом случае целесообразно применять формулу: =СЧЁТЕСЛИ(A:A;»<«&A1)+ СЧЁТЕСЛИ ($A$1:A1;»=»&A1).
- Для автоматической сортировки текстовой информации текстовой информации также используется формула массива: =ИНДЕКС(List; ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(List; «<«&List); СТРОКА(1:1)); СЧЁТЕСЛИ(List; «<«&List); 0)). Здесь List – это заданный диапазон.

Как убрать сортировку в Excel
Для отмены единичной сортировки диапазона данных достаточно нажать кнопку «Отменить ввод» в левом углу экрана. Бывает так, что изменения в файле были сохранены и отменить действие невозможно. Как убрать сортировку в этом случае?
Если после сложных манипуляций с таблицей, ее нужно будет вернуть в первоначальный вид, перед выполнением сортировки следует специально создать дополнительный столбец, в котором будет отражена нумерация строк. После завершения сложного анализа числовых и текстовых данных, представленных в таблице, чтобы отменить все проделанные операции достаточно будет установить сортировку по созданному столбцу.
Пересчитываются ли формулы Excel при сортировке?
Я попытался найти ответ в Google, но не смог найти никаких ответов, поэтому я пытаюсь здесь.
Таблица ниже содержит значения в столбцах A и B, формулу, отформатированную как текст в столбце C, и ту же формулу, отформатированную как реальная формула ( =A1+B1 и т. Д.), Которая отображает результат в столбце D.
Если я затем сортирую таблицу по A в порядке убывания, чтобы она выглядела следующим образом:
Пересчитываются ли формулы при сортировке? Работа с небольшими таблицами или листами, такими как эта, на самом деле не имеет значения, но если файл содержит более пары тысяч строк, а сложные формулы пересчет должен быть довольно медленным. Является ли хорошей идеей скопировать D и вставить в качестве значений в D перед сортировкой, если я закончу редактирование формулы?
2 ответа
Расчет, похоже, срабатывает при сортировке.
Вы можете указать значения копирования / вставки, но другой способ обойти это — включить ручной расчет. Вы можете сделать это по адресу:
Файл -> Параметры -> Формулы -> Расчет вручную
Таким образом, вы можете сами выбрать, когда рассчитывать формулы, нажав F9. Я бы не оставлял это на неопределенное время, так как это может дать неправильное представление о данных, но при манипулировании данными эта функция может пригодиться.
Да, если я использую инструмент Volatile Formulas от Charles Williams, Excel перезапускает формулы на сортировке
Однако — инструмент также указывает, что сортировка пересчитывается, даже если нет формул.
Excel при сортировке сбиваются формулы
Далее, мелочь, но нервирует — если при открытии файла excel 2010 на клавиатуре стоит русский, «горячие» клавиши excel не работают. Очень странная проблема. Приходится выходить из excel, переключать на английскую или латышскую клавиатуру и снова открывать файл, после чего функции «горячих» клавиш восстанавливаются. Все программы лицензионные, до того работали корректно не один год.
Так как эти проблемы возникли сразу после установки W10, я ждал обновлений с исправлениями ошибок. Но, пока что, ничего не происходит. Есть ли способ устранить эти ошибки?
Эта цепочка заблокирована. Вы можете просмотреть вопрос или оставить свой голос, если сведения окажутся полезными, но вы не можете написать ответ в этой цепочке.
Оскорбление — это любое поведение, которое беспокоит или расстраивает человека или группу лиц. К угрозам относятся любые угрозы самоубийством, насилием, нанесением ущерба и др. Любое содержимое для взрослых или недопустимое на веб-сайте сообщества. Любое изображение, обсуждение наготы или ссылка на подобные материалы. Оскорбительное, грубое или вульгарное поведение и другие проявления неуважения. Любое поведение, нарушающее лицензионные соглашения, в том числе предоставление ключей продуктов или ссылок на пиратское ПО. Незатребованная массовая рассылка или реклама. Любые ссылки или пропаганда сайтов с вирусным, шпионским, вредоносным или фишинговым ПО. Любое другое неуместное содержимое или поведение в соответствии с правилами использования и кодексом поведения. Любое изображение, ссылка или обсуждение, связанные с детской порнографией, детской наготой или другими вариантами оскорбления или эксплуатации детей.
Ответы (3)
В формуле, использующей функцию ВПР, ГПР, ПРОСМОТР или ПОИСКПОЗ, указан неверный тип значения аргумента искомое_значение.
Сведения об этих функциях и их аргументах можно найти в статьях Функция ВПР , Функция ГПР , Функция ПРОСМОТР и Функция ПОИСКПОЗ .
Формула, использующая функцию ВПР, ГПР или ПОИСКПОЗ, пытается найти значение в несортированной таблице.
Если вы используете функцию ПОИСКПОЗ, попробуйте изменить значение аргумента тип_сопоставления, чтобы указать порядок сортировки таблицы. Чтобы найти точное совпадение, установите для аргумента тип_сопоставления значение 0 (ноль).
Формула массива ссылается на диапазон, не соответствующий по количеству строк или столбцов диапазону, содержащему формулу массива.
В формуле, использующей стандартную или пользовательскую функцию, отсутствует один или несколько обязательных аргументов.
Пользовательская функция, которую вы ввели, недоступна.
Совет : Если в Microsoft Excel включена проверка ошибок, нажмите кнопку рядом с ячейкой, в которой показана ошибка. Выберите пункт Показать этапы вычисления, если он отобразится, а затем выберите подходящее решение.
Оскорбление — это любое поведение, которое беспокоит или расстраивает человека или группу лиц. К угрозам относятся любые угрозы самоубийством, насилием, нанесением ущерба и др. Любое содержимое для взрослых или недопустимое на веб-сайте сообщества. Любое изображение, обсуждение наготы или ссылка на подобные материалы. Оскорбительное, грубое или вульгарное поведение и другие проявления неуважения. Любое поведение, нарушающее лицензионные соглашения, в том числе предоставление ключей продуктов или ссылок на пиратское ПО. Незатребованная массовая рассылка или реклама. Любые ссылки или пропаганда сайтов с вирусным, шпионским, вредоносным или фишинговым ПО. Любое другое неуместное содержимое или поведение в соответствии с правилами использования и кодексом поведения. Любое изображение, ссылка или обсуждение, связанные с детской порнографией, детской наготой или другими вариантами оскорбления или эксплуатации детей.
У меня есть список из значений, на которые ссылается формула внизу, но при сортировке все «едет» и значения меняются. как прикрепить значения к формуле, или формулу к значению ну вы поняли _)
У меня есть список из значений, на которые ссылается формула внизу, но при сортировке все «едет» и значения меняются. как прикрепить значения к формуле, или формулу к значению ну вы поняли _) Gelion Master
Gelion Master, вообще-то у нас тут принято файлы-примеры прикреплять, или в крайних случаях — работать с файлообменниками. Кроме того, как и в любом приличном обществе, принято здороваться.
Gelion Master, вообще-то у нас тут принято файлы-примеры прикреплять, или в крайних случаях — работать с файлообменниками. Кроме того, как и в любом приличном обществе, принято здороваться. kim
только названия должны быть одинаковыми в таблице и в итоге
А если разные, то нужно таблицу соответствия названий
только названия должны быть одинаковыми в таблице и в итоге
А если разные, то нужно таблицу соответствия названий _Boroda_
только названия должны быть одинаковыми в таблице и в итоге
А если разные, то нужно таблицу соответствия названий Автор — _Boroda_
Дата добавления — 05.03.2013 в 03:55
у вас формула в одной строке, а если
[sup]
=СУММ(100%-(D3*100%/B4))
=СУММ(B4-D3)?
спасибо, понял принцип
у вас формула в одной строке, а если
[sup]
=СУММ(100%-(D3*100%/B4))
=СУММ(B4-D3)?
спасибо, понял принцип Gelion
Чтобы не плодить темы отпишу сюда.
Есть перечень партийных номеров (столбец C) (последовательность внесения номеров не последовательна, возможны повторения), им присваивается порядковый номер (два варианта формул в столбцах B и E). Проверяется уникальность номера, если номер повторяется, ищется и подставляется уже присвоенное значение.
Все красиво, пока не возникает необходимость отсортировать значения по возрастанию по столбцу C. может есть варианты как это вылечить? Из найденных мной — залинковать данные на другой лист и сортировать уже там.
[moder]Не, так не пойдет. Правила форума читайте.
Эта тема закрыта
Чтобы не плодить темы отпишу сюда.
Есть перечень партийных номеров (столбец C) (последовательность внесения номеров не последовательна, возможны повторения), им присваивается порядковый номер (два варианта формул в столбцах B и E). Проверяется уникальность номера, если номер повторяется, ищется и подставляется уже присвоенное значение.
Все красиво, пока не возникает необходимость отсортировать значения по возрастанию по столбцу C. может есть варианты как это вылечить? Из найденных мной — залинковать данные на другой лист и сортировать уже там.
[moder]Не, так не пойдет. Правила форума читайте.
Эта тема закрыта Kerby
Суть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами.
Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда.
Прилагаю пример, сортируете столбец B от «А до Я» и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально.
Суть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами.
Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда.
Прилагаю пример, сортируете столбец B от «А до Я» и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально. lema
lema, а как вы хотите сортировать столбец в котором не однородные данные, а данные перемешаны с вычислениями этих же самих данных ?
lema, а как вы хотите сортировать столбец в котором не однородные данные, а данные перемешаны с вычислениями этих же самих данных ? koyaanisqatsi
прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно
прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно Szekerfehesvar
прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно Автор — Szekerfehesvar
Дата добавления — 07.09.2016 в 16:27
прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно.
прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно. lema
прикрепил файл с Вашим примером и данной формулой — при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps
Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно. Автор — lema
Дата добавления — 07.09.2016 в 17:42
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие buchlotnik
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие Автор — buchlotnik
Дата добавления — 07.09.2016 в 19:24
по формуле — ИНДЕКС() выискивает ячейку по ее адресу в диапазоне, что как раз подходит под Вашу задачу
ПОИСКПОЗ() — ищет в диапазоне ячейку по ее значению
в итоге получается
=ИНДЕКС( вводим диапазон, где находятся искомые данные — то есть столбец В в вашем случае; далее ищем строку, с которой нужно взять значение — ПОИСКПОЗ(значение, которое ищем — в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых;указываем диапазон, где искать наш порядковый номер — столбец А; указываем 0 — это даст нам поиск точных совпадений);указываем 1, так как в нашем диапазоне только 1 столбец)
по формуле — ИНДЕКС() выискивает ячейку по ее адресу в диапазоне, что как раз подходит под Вашу задачу
ПОИСКПОЗ() — ищет в диапазоне ячейку по ее значению
в итоге получается
=ИНДЕКС( вводим диапазон, где находятся искомые данные — то есть столбец В в вашем случае; далее ищем строку, с которой нужно взять значение — ПОИСКПОЗ(значение, которое ищем — в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых;указываем диапазон, где искать наш порядковый номер — столбец А; указываем 0 — это даст нам поиск точных совпадений);указываем 1, так как в нашем диапазоне только 1 столбец) Szekerfehesvar
Средство проверки совместимости обнаружило одну или несколько проблем совместимости, связанных с сортировкой и фильтрацией.
Важно: Прежде чем продолжить сохранение книги в формате файла более ранней версии, необходимо устранить проблемы, которые приводят к существенной потере функциональности, чтобы предотвратить безвозвратную потерю данных или неправильную работу функций.
Проблемы, которые приводят к небольшой потере точности, не всегда обязательно устранять перед сохранением: данные и функциональность не теряются, но книга при ее открытии в более ранней версии 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 2007 г. или более поздней, если они не были изменены в Excel 97–2003.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти данные, отсортированные по настраиваемому списку, и измените условия сортировки таким образом, чтобы они не содержали настраиваемого списка.
При сортировке листа книги использовано условие сортировки, включающее сведения о форматировании. В более ранних версиях Excel эта информация будет потеряна.
Что это означает. В Excel 2007 г. или более поздней можно сортировать данные по определенному формату, такому как цвет ячейки, цвет шрифта или наборы значков. В Excel 97–2003 вы можете сортировать только текст.
Однако все сведения о состоянии сортировки остаются доступными в книге и применяются при повторном ее повторном открыть в 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 2007 г. или более поздней, если они не были изменены в Excel 97–2003.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти отфильтрованные данные, после чего очистите фильтр для отображения скрытых строк. Чтобы очистить фильтр, на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр и выберите команду Очистить.
Книга содержит данные, к которым применен фильтр по цвету шрифта. В более ранней версии 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 2007 г. или более поздней, если они не были изменены в Excel 97–2003.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти отфильтрованные данные, после чего очистите фильтр для отображения скрытых строк. Чтобы очистить фильтр, на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр и выберите команду Очистить.
Книга содержит данные, к которым применен фильтр, использующий более двух условий. В более ранней версии Excel строки, скрытые этим фильтром, останутся скрытыми, но сам фильтр будет отображаться неправильно.
Что это означает. В Excel 2007 г. или более поздней можно фильтровать данные по более чем двум условиям. Чтобы не потерять функциональность фильтра, его лучше очистить перед сохранением книги в файле формата более ранней версии Excel. В Excel 97–2003 после этого можно фильтровать данные вручную.
Однако все сведения о состоянии фильтра остаются доступными в книге и применяются при повторном ее повторном Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти отфильтрованные данные, после чего очистите фильтр для отображения скрытых строк. Чтобы очистить фильтр, на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр и выберите команду Очистить.
Книга содержит данные, к которым применен фильтр по сгруппированной иерархии дат, использующий более двух условий. В более ранней версии Excel строки, скрытые этим фильтром, останутся скрытыми, но сам фильтр будет отображаться неправильно.
Что это означает. В Excel 2007 г. или более поздней можно фильтровать даты по сгруппанной иерархии. Поскольку в Excel 97–2003 эта возможность не поддерживается, может потребоваться разгруппировать иерархию дат. Чтобы не потерять функциональность фильтра, его лучше очистить перед сохранением книги в файле формата более ранней версии Excel.
Однако все сведения о состоянии фильтра остаются доступными в книге и применяются при повторном ее повторном Excel 2007 г. или более поздней, если они не были изменены в Excel 97–2003.
Что необходимо сделать. В средстве проверки совместимости нажмите кнопку Найти, чтобы найти отфильтрованные данные, после чего очистите фильтр для отображения скрытых строк. Чтобы очистить фильтр, на вкладке Главная в группе Редактирование нажмите кнопку Сортировка и фильтр и выберите команду Очистить.
Группировку данных также можно отключить на вкладке «Дополнительные параметры» Excel Параметры. (Файл, параметры).
Есть отчет, в нем список проектов с определенными данными (проекты и данные будут меняться каждую неделю, поэтому список не фиксированный). Необходимо было добавить 2 новых столбца, а именно: Суммарная оценка проекта = Типизация+Срочность+Стратегичность и Модуль максимального отклонения=модуль отклонения по срокам+модуль отклонения по трудозатратам. (Столбцы добавила, формула для них прописала на втором листе).
Необходимо, чтобы возможно было сортировать данные по столбцам Суммарная оценка и Модуль макс. отклонения (по убыванию), но после сортировки по любому из них — формула слетает и приходится восстанавливать ее и продливать на весь столбец.
Прошу помочь устранить данную ошибку, чтобы не приходилось делать восстановление вручную.
Зависает Ексель при сортировке!
Есть файл екселя там очень много формул и информации когда я начинаю сортировать одну строку он.
Перенос формулы, при сортировке таблицы.
Help please. Вопрос таков: как сделать так, что бы ячейка А1 осталась = 1 после сортировки.

AUTOCAD 2012: при предварительном просмотре при печати слетает приложение
Система Win 7. Установил AUTOCAD 2012, все работает, но при предварительном просмотре рисунка при.
Как вариант:
— перед сортировкой копировать всю таблицу на другой лист при помощи PasteSpecial Values
— сортируем
Если это надо делать часто, то можно сделать небольшой макрос, который будет это делать
Решение
Благодарю! Я просто не могла придумать как по-другому посчитать суммарную оценку, нашла только такой способ, поэтому и счет модуля вынесла на др. страницу. Теперь все отлично работает, еще раз спасибо! Вопрос закрыт.
Ошибка при сортировке
При сортировке массива вылетает причем именно на последнем числе сортирую так : for (int.
Ошибка при сортировке
Для подключения использую компоненты FireDAC, для отображения — EhLib. При попытке отсортировать.
Ошибка при сортировке
нажимаю сортировка и получаю: "project project1.exe raised exception class econverterror with.



