Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/При сортировке сбиваются формулы. - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » При сортировке сбиваются формулы. (Формулы/Formulas)
При сортировке сбиваются формулы.
lema Дата: Среда, 07.09.2016, 11:11 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Суть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами.
Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда.
Прилагаю пример, сортируете столбец B от "А до Я" и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально.
К сообщению приложен файл: 6411903.xls (25.5 Kb)
 
Ответить
СообщениеСуть проблемы проста. При сортировке формулы не меняют динамически адреса тех ячеек которые в ходе сортировки смещаются и оказываются под другими адресами.
Искал решение, но везде натыкался на многосложные проблемы с сортировкой, у меня вроде проще некуда.
Прилагаю пример, сортируете столбец B от "А до Я" и значения формул в желтых ячейках изменяются, а мне нужно, что бы не менялись и привязка оставалась к тем ячейкам к которым была изначально.

Автор - lema
Дата добавления - 07.09.2016 в 11:11
pabchek Дата: Среда, 07.09.2016, 11:31 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 931
Репутация: 218 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!
Сводной пойдет?
К сообщению приложен файл: 6060464.xls (32.0 Kb)


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
СообщениеЗдравствуйте!
Сводной пойдет?

Автор - pabchek
Дата добавления - 07.09.2016 в 11:31
lema Дата: Среда, 07.09.2016, 12:22 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Сводной пойдет?

Я даже не понимаю что это и как этим пользоваться.
 
Ответить
Сообщение
Сводной пойдет?

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

Автор - lema
Дата добавления - 07.09.2016 в 12:22
koyaanisqatsi Дата: Среда, 07.09.2016, 14:34 | Сообщение № 4
Группа: Проверенные
Ранг: Ветеран
Сообщений: 713
Репутация: 15 ±
Замечаний: 0% ±

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

Автор - koyaanisqatsi
Дата добавления - 07.09.2016 в 14:34
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, попробуйте формулу ниже:
Код
=ИНДЕКС(B:B;ПОИСКПОЗ(A2+1;A:A;0);1)+ИНДЕКС(B:B;ПОИСКПОЗ(A2+2;A:A;0);1)

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

Автор - Szekerfehesvar
Дата добавления - 07.09.2016 в 16:27
lema Дата: Среда, 07.09.2016, 17:42 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

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


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

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


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

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

Автор - lema
Дата добавления - 07.09.2016 в 17:42
buchlotnik Дата: Среда, 07.09.2016, 19:24 | Сообщение № 7
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
Цитата
при сортировке религия мелкософта не позволила?
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие %)


Сообщение отредактировал buchlotnik - Среда, 07.09.2016, 19:25
 
Ответить
Сообщение
Цитата
при сортировке религия мелкософта не позволила?
вообще-то это вам религия не позволила нормально организовать данные. Вы всерьёз считаете, что при сортировке Excel тягаяет ячейки так же как при вырезании и вставке? Страшно подумать сколько времени занимало бы данное мероприятие %)

Автор - buchlotnik
Дата добавления - 07.09.2016 в 19:24
Szekerfehesvar Дата: Четверг, 08.09.2016, 09:22 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 6 ±
Замечаний: 20% ±

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

Автор - Szekerfehesvar
Дата добавления - 08.09.2016 в 09:22
lema Дата: Четверг, 08.09.2016, 12:46 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 5
Репутация: 0 ±
Замечаний: 0% ±

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

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

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

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

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


Сообщение отредактировал lema - Четверг, 08.09.2016, 14:48
 
Ответить
Сообщение
вообще-то это вам религия не позволила нормально организовать данные.

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

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

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

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

Автор - lema
Дата добавления - 08.09.2016 в 12:46
buchlotnik Дата: Четверг, 08.09.2016, 15:40 | Сообщение № 10
Группа: Заблокированные
Ранг: Участник клуба
Сообщений: 3442
Репутация: 929 ±
Замечаний: 20% ±

2010, 2013, 2016 RUS / ENG
lema, из всего вашего эмоционального поста главное
Цитата
Плевать по какому принципу
Так вот, на дружественном форуме есть человек, у которого в подписи замечательная фраза
Цитата
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
. Майкрософту хватило мозгов В ПРИНЦИПЕ сделать Excel и они-то понимают разницу между перемещением диапазона и сортировкой.
Цитата
Не вижу преграды
так напишите быстрый алгоритм и место в Майкрософте, а то и в Гугле вам гарантировано!
Цитата
Думать вообще страшно.
Жаль, что вам думать страшно. Только вот если бы вашу "хотелку" реализовали, сортировка занимала бы не долю секунды, а минуты - и вы бы пришли сюда с чем-то вроде "ребята, почему у мелкомягких сортировка минуты (часы) занимает? - у меня ж тут простые числовые данные"
Цитата
Можете показать на примере, так правильно организованные данные при сортировке которых, примитивные и простые формулы ... не сбиваются
нет таких примеров - вам об этом сказали в четвёртом посте - в столбце для сортировки должны быть однородные данные - либо значения, либо одинаковые формулы. И если бы вы не пихали всё в один столбец не пришлось бы танцевать с бубном изобретая сложные формулы или прибегать к макросам.
Цитата
результат получен в чистом виде благодаря "нормальной" организации
озвучте конечную цель сортировки. Возможно, это не единственный приём для решения вашей задачи


Сообщение отредактировал buchlotnik - Четверг, 08.09.2016, 15:55
 
Ответить
Сообщениеlema, из всего вашего эмоционального поста главное
Цитата
Плевать по какому принципу
Так вот, на дружественном форуме есть человек, у которого в подписи замечательная фраза
Цитата
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
. Майкрософту хватило мозгов В ПРИНЦИПЕ сделать Excel и они-то понимают разницу между перемещением диапазона и сортировкой.
Цитата
Не вижу преграды
так напишите быстрый алгоритм и место в Майкрософте, а то и в Гугле вам гарантировано!
Цитата
Думать вообще страшно.
Жаль, что вам думать страшно. Только вот если бы вашу "хотелку" реализовали, сортировка занимала бы не долю секунды, а минуты - и вы бы пришли сюда с чем-то вроде "ребята, почему у мелкомягких сортировка минуты (часы) занимает? - у меня ж тут простые числовые данные"
Цитата
Можете показать на примере, так правильно организованные данные при сортировке которых, примитивные и простые формулы ... не сбиваются
нет таких примеров - вам об этом сказали в четвёртом посте - в столбце для сортировки должны быть однородные данные - либо значения, либо одинаковые формулы. И если бы вы не пихали всё в один столбец не пришлось бы танцевать с бубном изобретая сложные формулы или прибегать к макросам.
Цитата
результат получен в чистом виде благодаря "нормальной" организации
озвучте конечную цель сортировки. Возможно, это не единственный приём для решения вашей задачи

Автор - buchlotnik
Дата добавления - 08.09.2016 в 15:40
Manyasha Дата: Четверг, 08.09.2016, 16:21 | Сообщение № 11
Группа: Модераторы
Ранг: Старожил
Сообщений: 2198
Репутация: 898 ±
Замечаний: 0% ±

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

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


ЯД: 410013299366744 WM: R193491431804
 
Ответить
Сообщение
озвучте конечную цель сортировки. Возможно, это не единственный приём для решения вашей задачи

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

Автор - Manyasha
Дата добавления - 08.09.2016 в 16:21
Szekerfehesvar Дата: Четверг, 08.09.2016, 17:08 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 29
Репутация: 6 ±
Замечаний: 20% ±

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

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

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

Автор - Szekerfehesvar
Дата добавления - 08.09.2016 в 17:08
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
 
Ответить
Сообщение
Лучше уж тогда скиньте файл

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

Автор - lema
Дата добавления - 11.09.2016 в 11:47
Мир MS Excel » Вопросы и решения » Вопросы по Excel » При сортировке сбиваются формулы. (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!