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

Вход

Регистрация

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

 

= Мир MS Excel/Сортировка на секунду верная, а потом нет - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка на секунду верная, а потом нет (Формулы/Formulas)
Сортировка на секунду верная, а потом нет
letasm Дата: Воскресенье, 14.02.2016, 23:39 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
Мне нужно отсортировать таблицу по количеству очков
Если нажать сортировка по убыванию, то на секунду он сортирует все верно, а затем пересортировывает не так, как должно быть
Что нужно исправить?


Сообщение отредактировал letasm - Понедельник, 15.02.2016, 00:37
 
Ответить
СообщениеМне нужно отсортировать таблицу по количеству очков
Если нажать сортировка по убыванию, то на секунду он сортирует все верно, а затем пересортировывает не так, как должно быть
Что нужно исправить?

Автор - letasm
Дата добавления - 14.02.2016 в 23:39
gling Дата: Воскресенье, 14.02.2016, 23:46 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1641
Репутация: 399 ±
Замечаний: 0% ±

2010
Вы сортируете по столбцу с формулами, отключите автопересчет формул на время сортировки.


ЯД-41001506838083
 
Ответить
СообщениеВы сортируете по столбцу с формулами, отключите автопересчет формул на время сортировки.

Автор - gling
Дата добавления - 14.02.2016 в 23:46
letasm Дата: Понедельник, 15.02.2016, 00:28 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 37
Репутация: 0 ±
Замечаний: 40% ±

Excel 2010
gling, Спасибо большое!
 
Ответить
Сообщениеgling, Спасибо большое!

Автор - letasm
Дата добавления - 15.02.2016 в 00:28
Gustav Дата: Вторник, 16.02.2016, 13:34 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1442
Репутация: 550 ±
Замечаний: 0% ±

начинал с Excel 4.0...
Иех, хотел было поучаствовать в теме, немного смотрел её в выходные. В понедельник хватился, глядь - "ан демонов-то и нет" а файла-то уже и нет в первом посте.

Но ничего, уже "у нас с собой было" - см. приложенный файл-копию. Так вот, чего-то не получилось у меня воспользоваться советом уважаемого gling'а по поводу отключения пересчета...

Нашёл в Сети даже какой-то совет от Microsoft типа "если с первого раза не получилось, то отсортируйте еще раз". Не очень понятно что они имели в виду - сортировал повторно и два, и три, и четыре раза - всё "мимо кассы".

Разберем ситуацию подробнее. В исходном виде в колонке "Очки"(N) первые пять ячеек (N2:N6) содержат формулы со ссылками на ячейки колонки A:
A2
A3
A4
A5
A6

После сортировки по убыванию ситуация со ссылками в пяти ячейках N2:N6 меняется:
A2
A5
A9
A15
A4

Если отключить пересчет, то в первый момент цифры вроде выстраиваются в визуально правильном порядке, но в ячейках в N2:N6 обстановка всё равно меняется как показано выше, т.е. в ссылках наступает некий хаос, который далее не лечится ни включением пересчета, ни повторными сортировками.

По правильной сортировке первые 5 мест по убыванию очков должны выглядеть так:

Кот - 11 очков
Мега-Пыхарь - 10 очков
Мяу - 10 очков
Милка - 8 очков
Номер 9 - 7 очков

Такой порядок можно получить, если перед сортировкой по колонке "Очки" (N) заменить в ее ячейках формулы значениями или отключить пересчет ячеек.

Если же выполнить сортировку, не отключая пересчет, то, как следует из названия темы, такое состояние подержится секунду-другую, после чего картина исказится:

Кот - 11 очков
Мега-Пыхарь - 8 очков
Мяу - 5 очков
Милка - 0 очков
Номер 9 - 10 очков

Посмотрим, в каких ячейках колонки N при этом оказываются ссылки на ячейки A2:A6. Вот в таких:

A2 - в N2
A3 - в N100
A4 - в N6
A5 - в N3
A6 - в N10

Ничего себе, да?!

Оставляя желающим поразбираться полную свободу действий в дальнейшем анализе, приведу свои соображения по нормализации ситуации формулами.

В исходной формуле ячейки N2
Код
=СУММЕСЛИМН(Игры!G:G;Игры!A:A;Статистика!A2)+СУММЕСЛИМН(Игры!E:E;Игры!D:D;Статистика!A2)

меняем в двух местах ссылку Статистика!A2 на полную колонку Статистика!A:A и копируем ячейку N2 в используемый диапазон N2:N100. Сортируем - вуа-ля! - очки выстраиваются правильно и далее не шевелятся ни через секунду, ни через минуту, ни вообще.

Итак, "полная колонка" победила плохую сортировку, но, подозреваю, при этом расходуется лишняя память.

Можно попробовать "улучшиться" использованием ссылки только необходимого размера: вместо Статистика!A:A написать абсолютный адрес $A$2:$A$100.

Рассматривал также замену Статистика!A:A выражением ИНДЕКС($A$1:$A$100;СТРОКА()), но не знаю, насколько это оправдано.

Подобным образом следует поменять ссылки типа "Статистика!A2" в колонках E, G, I:M. В них тоже наступает аналогичный хаос в ссылках после сортировки по колонке "Очки".

Что интересно, в ячейках колонки B, также имеющей ссылки на колонку A замена на "полную колонку" не требуется и после сортировки по ней ссылки в ячейках по-прежнему идут монотонно-гладенько от A2 до A100. А, может, дело в каких-то особенностях функции СУММЕСЛИМН, присутствующей в проблемных колонках?..
К сообщению приложен файл: CopySort.xlsx(80Kb)


Мой tip box - яд 41001663842605
 
Ответить
СообщениеИех, хотел было поучаствовать в теме, немного смотрел её в выходные. В понедельник хватился, глядь - "ан демонов-то и нет" а файла-то уже и нет в первом посте.

Но ничего, уже "у нас с собой было" - см. приложенный файл-копию. Так вот, чего-то не получилось у меня воспользоваться советом уважаемого gling'а по поводу отключения пересчета...

Нашёл в Сети даже какой-то совет от Microsoft типа "если с первого раза не получилось, то отсортируйте еще раз". Не очень понятно что они имели в виду - сортировал повторно и два, и три, и четыре раза - всё "мимо кассы".

Разберем ситуацию подробнее. В исходном виде в колонке "Очки"(N) первые пять ячеек (N2:N6) содержат формулы со ссылками на ячейки колонки A:
A2
A3
A4
A5
A6

После сортировки по убыванию ситуация со ссылками в пяти ячейках N2:N6 меняется:
A2
A5
A9
A15
A4

Если отключить пересчет, то в первый момент цифры вроде выстраиваются в визуально правильном порядке, но в ячейках в N2:N6 обстановка всё равно меняется как показано выше, т.е. в ссылках наступает некий хаос, который далее не лечится ни включением пересчета, ни повторными сортировками.

По правильной сортировке первые 5 мест по убыванию очков должны выглядеть так:

Кот - 11 очков
Мега-Пыхарь - 10 очков
Мяу - 10 очков
Милка - 8 очков
Номер 9 - 7 очков

Такой порядок можно получить, если перед сортировкой по колонке "Очки" (N) заменить в ее ячейках формулы значениями или отключить пересчет ячеек.

Если же выполнить сортировку, не отключая пересчет, то, как следует из названия темы, такое состояние подержится секунду-другую, после чего картина исказится:

Кот - 11 очков
Мега-Пыхарь - 8 очков
Мяу - 5 очков
Милка - 0 очков
Номер 9 - 10 очков

Посмотрим, в каких ячейках колонки N при этом оказываются ссылки на ячейки A2:A6. Вот в таких:

A2 - в N2
A3 - в N100
A4 - в N6
A5 - в N3
A6 - в N10

Ничего себе, да?!

Оставляя желающим поразбираться полную свободу действий в дальнейшем анализе, приведу свои соображения по нормализации ситуации формулами.

В исходной формуле ячейки N2
Код
=СУММЕСЛИМН(Игры!G:G;Игры!A:A;Статистика!A2)+СУММЕСЛИМН(Игры!E:E;Игры!D:D;Статистика!A2)

меняем в двух местах ссылку Статистика!A2 на полную колонку Статистика!A:A и копируем ячейку N2 в используемый диапазон N2:N100. Сортируем - вуа-ля! - очки выстраиваются правильно и далее не шевелятся ни через секунду, ни через минуту, ни вообще.

Итак, "полная колонка" победила плохую сортировку, но, подозреваю, при этом расходуется лишняя память.

Можно попробовать "улучшиться" использованием ссылки только необходимого размера: вместо Статистика!A:A написать абсолютный адрес $A$2:$A$100.

Рассматривал также замену Статистика!A:A выражением ИНДЕКС($A$1:$A$100;СТРОКА()), но не знаю, насколько это оправдано.

Подобным образом следует поменять ссылки типа "Статистика!A2" в колонках E, G, I:M. В них тоже наступает аналогичный хаос в ссылках после сортировки по колонке "Очки".

Что интересно, в ячейках колонки B, также имеющей ссылки на колонку A замена на "полную колонку" не требуется и после сортировки по ней ссылки в ячейках по-прежнему идут монотонно-гладенько от A2 до A100. А, может, дело в каких-то особенностях функции СУММЕСЛИМН, присутствующей в проблемных колонках?..

Автор - Gustav
Дата добавления - 16.02.2016 в 13:34
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка на секунду верная, а потом нет (Формулы/Formulas)
Страница 1 из 11
Поиск:

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