Суть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами. Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда. Прилагаю пример, сортируете столбец B от "А до Я" и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально.
Суть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами. Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда. Прилагаю пример, сортируете столбец B от "А до Я" и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально.lema
прикрепил файл с Вашим примером и данной формулой - при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно.
прикрепил файл с Вашим примером и данной формулой - при любой сортировке этого примера работает корректно
Да, здорово. Можете объяснить принцип действия этой формулы, что бы самому её писать, т.е. как происходит процесс?
ps Странно, почему при вырезании ячеек (в которых находятся значения) и переносе в другую ячейку, формула динамически меняет адреса ячеек и ни когда их не забывает, так сделано по умолчанию, а сделать тоже самое динамическое изменение формулы при сортировке религия мелкософта не позволила? Странно.lema
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие
Цитата
при сортировке религия мелкософта не позволила?
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие buchlotnik
Сообщение отредактировал buchlotnik - Среда, 07.09.2016, 19:25
по формуле - ИНДЕКС() выискивает ячейку по ее адресу в диапазоне, что как раз подходит под Вашу задачу ПОИСКПОЗ() - ищет в диапазоне ячейку по ее значению в итоге получается =ИНДЕКС( вводим диапазон, где находятся искомые данные - то есть столбец В в вашем случае; далее ищем строку, с которой нужно взять значение - ПОИСКПОЗ(значение, которое ищем - в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых;указываем диапазон, где искать наш порядковый номер - столбец А; указываем 0 - это даст нам поиск точных совпадений);указываем 1, так как в нашем диапазоне только 1 столбец)
по формуле - ИНДЕКС() выискивает ячейку по ее адресу в диапазоне, что как раз подходит под Вашу задачу ПОИСКПОЗ() - ищет в диапазоне ячейку по ее значению в итоге получается =ИНДЕКС( вводим диапазон, где находятся искомые данные - то есть столбец В в вашем случае; далее ищем строку, с которой нужно взять значение - ПОИСКПОЗ(значение, которое ищем - в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых;указываем диапазон, где искать наш порядковый номер - столбец А; указываем 0 - это даст нам поиск точных совпадений);указываем 1, так как в нашем диапазоне только 1 столбец)Szekerfehesvar
вообще-то это вам религия не позволила нормально организовать данные.
Интересно, если бы при вырезании тоже отсутствовала динамическая смена адресов, вы бы также говорили - тебе религия не позволяет нормально организовать данные! А можно узнать, что ненормального в организации данных? Как по вашему должны выглядеть нормально организованные данные? Можете показать на примере, так правильно организованны данные, что при сортировке которых, примитивные и простые формулы (такие как сложить ячейки, вычесть, разделить и.д. ) не сбиваются без использования многосложных формул, макросов и т.д. Т.е. результат получен в чистом виде благодаря "нормальной" организации и только ей. Спасибо.
Цитата
Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке?
Нет. Зачем мне так считать? Плевать по какому принципу происходит смена адреса при вырезании и при сортировке. Не вижу преграды для того, что бы программа при сортировке зная что ячейка А1 после сортирвки становится например ячейкой А7, поменять её автоматически в формуле на А7, если это же было возможно сделать при вырезании и не только при вырезании, а даже при вставке новых строк и столбцов.
Цитата
Страшно подумать сколько времени занимало бы данное мероприятие
значение, которое ищем - в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых
К сожалению это не универсально, потому как порядковые номера я привёл чисто из примера, на самом деле там могут быть абсолютно любые данные без всяких закономерностей не только цифры но и текст. Зависимость от столбца А не подходит. И ещё, под желтыми строками может быть любое количество белых строк из которых для суммирования берутся данные, там закономерности тоже нет. в общем с соседними столбцами дружить ни как нельзя.
вообще-то это вам религия не позволила нормально организовать данные.
Интересно, если бы при вырезании тоже отсутствовала динамическая смена адресов, вы бы также говорили - тебе религия не позволяет нормально организовать данные! А можно узнать, что ненормального в организации данных? Как по вашему должны выглядеть нормально организованные данные? Можете показать на примере, так правильно организованны данные, что при сортировке которых, примитивные и простые формулы (такие как сложить ячейки, вычесть, разделить и.д. ) не сбиваются без использования многосложных формул, макросов и т.д. Т.е. результат получен в чистом виде благодаря "нормальной" организации и только ей. Спасибо.
Цитата
Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке?
Нет. Зачем мне так считать? Плевать по какому принципу происходит смена адреса при вырезании и при сортировке. Не вижу преграды для того, что бы программа при сортировке зная что ячейка А1 после сортирвки становится например ячейкой А7, поменять её автоматически в формуле на А7, если это же было возможно сделать при вырезании и не только при вырезании, а даже при вставке новых строк и столбцов.
Цитата
Страшно подумать сколько времени занимало бы данное мероприятие
значение, которое ищем - в нашем случае это № из столбца А с прибавленной единицей или двойкой, так как именно на столько отличается порядковый номер у слагаемых
К сожалению это не универсально, потому как порядковые номера я привёл чисто из примера, на самом деле там могут быть абсолютно любые данные без всяких закономерностей не только цифры но и текст. Зависимость от столбца А не подходит. И ещё, под желтыми строками может быть любое количество белых строк из которых для суммирования берутся данные, там закономерности тоже нет. в общем с соседними столбцами дружить ни как нельзя.lema
Сообщение отредактировал lema - Четверг, 08.09.2016, 14:48
lema, из всего вашего эмоционального поста главное
Цитата
Плевать по какому принципу
Так вот, на дружественном форуме есть человек, у которого в подписи замечательная фраза
Цитата
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
. Майкрософту хватило мозгов В ПРИНЦИПЕ сделать Excel и они-то понимают разницу между перемещением диапазона и сортировкой.
Цитата
Не вижу преграды
так напишите быстрый алгоритм и место в Майкрософте, а то и в Гугле вам гарантировано!
Цитата
Думать вообще страшно.
Жаль, что вам думать страшно. Только вот если бы вашу "хотелку" реализовали, сортировка занимала бы не долю секунды, а минуты - и вы бы пришли сюда с чем-то вроде "ребята, почему у мелкомягких сортировка минуты (часы) занимает? - у меня ж тут простые числовые данные"
Цитата
Можете показать на примере, так правильно организованные данные при сортировке которых, примитивные и простые формулы ... не сбиваются
нет таких примеров - вам об этом сказали в четвёртом посте - в столбце для сортировки должны быть однородные данные - либо значения, либо одинаковые формулы. И если бы вы не пихали всё в один столбец не пришлось бы танцевать с бубном изобретая сложные формулы или прибегать к макросам.
Цитата
результат получен в чистом виде благодаря "нормальной" организации
озвучте конечную цель сортировки. Возможно, это не единственный приём для решения вашей задачи
lema, из всего вашего эмоционального поста главное
Цитата
Плевать по какому принципу
Так вот, на дружественном форуме есть человек, у которого в подписи замечательная фраза
Цитата
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
. Майкрософту хватило мозгов В ПРИНЦИПЕ сделать Excel и они-то понимают разницу между перемещением диапазона и сортировкой.
Цитата
Не вижу преграды
так напишите быстрый алгоритм и место в Майкрософте, а то и в Гугле вам гарантировано!
Цитата
Думать вообще страшно.
Жаль, что вам думать страшно. Только вот если бы вашу "хотелку" реализовали, сортировка занимала бы не долю секунды, а минуты - и вы бы пришли сюда с чем-то вроде "ребята, почему у мелкомягких сортировка минуты (часы) занимает? - у меня ж тут простые числовые данные"
Цитата
Можете показать на примере, так правильно организованные данные при сортировке которых, примитивные и простые формулы ... не сбиваются
нет таких примеров - вам об этом сказали в четвёртом посте - в столбце для сортировки должны быть однородные данные - либо значения, либо одинаковые формулы. И если бы вы не пихали всё в один столбец не пришлось бы танцевать с бубном изобретая сложные формулы или прибегать к макросам.
Цитата
результат получен в чистом виде благодаря "нормальной" организации
озвучте конечную цель сортировки. Возможно, это не единственный приём для решения вашей задачиbuchlotnik
Сообщение отредактировал buchlotnik - Четверг, 08.09.2016, 15:55
в общем с соседними столбцами дружить ни как нельзя.
Лучше уж тогда скиньте файл, в котором будет как минимум структура, как в реальном файле (то есть если идут разные коды, идентификаторы и прочее - пусть они будут как минимум по типу в файле-примере сходными, и т.д.). Мой вариант решения исходил из того, что у Вас в файле принцип построения схож с файловым, раз сама суть содержимого столбцов иная, то естественно мой вариант уже не подойдет. Было б гораздо проще, если бы был приведен настоящий файл в урезанном виде и без конфиденциальной инфы - ее можно заменить на какие то типовые аналоги (слова на слова, цифры на цифры)
в общем с соседними столбцами дружить ни как нельзя.
Лучше уж тогда скиньте файл, в котором будет как минимум структура, как в реальном файле (то есть если идут разные коды, идентификаторы и прочее - пусть они будут как минимум по типу в файле-примере сходными, и т.д.). Мой вариант решения исходил из того, что у Вас в файле принцип построения схож с файловым, раз сама суть содержимого столбцов иная, то естественно мой вариант уже не подойдет. Было б гораздо проще, если бы был приведен настоящий файл в урезанном виде и без конфиденциальной инфы - ее можно заменить на какие то типовые аналоги (слова на слова, цифры на цифры)Szekerfehesvar
Смысл следующий, есть строка с данными, она означает лист с контактами. Разберём на примере последнего (строка - 165): Поступает лист с контактами, записываются данные касаемо этого листа, такие как дата его приёма, дата выдачи и т.д. В ячейке F165 количество контактов на листе, в ячейке R165 пишется фамилия работника которому выдан лист, но бывает так, что лист делиться на 3 или 4 примерно равные части, и каждая часть раздаётся разным работникам, поэтому лист R165 становиться "комбо". Ни когда заранее не известно будет разбиваться лист или нет. Ниже как видим оранжевым отмечены строки из которых теперь этот лист состоит и естественно данные работника, каждого, который отчитывается по своему листу-кусочку (строка 166,167,168) должны поступать в главный лист (строка 165). Те самые данные которые должны суммироваться они отмечены зелёным, в них как раз находятся формулы. Бывает, что необходимо отсортировать листы по разным столбцам, отсюда и проблема. Естественно, нужно чтобы процесс разбития был быстрым, не знаю, может каким-то шаблонным копированием формулы с незначительным внесением изменений, но уж точно не написанием вручную здоровенной формулы.
Смысл следующий, есть строка с данными, она означает лист с контактами. Разберём на примере последнего (строка - 165): Поступает лист с контактами, записываются данные касаемо этого листа, такие как дата его приёма, дата выдачи и т.д. В ячейке F165 количество контактов на листе, в ячейке R165 пишется фамилия работника которому выдан лист, но бывает так, что лист делиться на 3 или 4 примерно равные части, и каждая часть раздаётся разным работникам, поэтому лист R165 становиться "комбо". Ни когда заранее не известно будет разбиваться лист или нет. Ниже как видим оранжевым отмечены строки из которых теперь этот лист состоит и естественно данные работника, каждого, который отчитывается по своему листу-кусочку (строка 166,167,168) должны поступать в главный лист (строка 165). Те самые данные которые должны суммироваться они отмечены зелёным, в них как раз находятся формулы. Бывает, что необходимо отсортировать листы по разным столбцам, отсюда и проблема. Естественно, нужно чтобы процесс разбития был быстрым, не знаю, может каким-то шаблонным копированием формулы с незначительным внесением изменений, но уж точно не написанием вручную здоровенной формулы.lema
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
