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

Вход

Регистрация

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

 

= Мир MS Excel/Сортировка на листах макросом без фильтра - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка на листах макросом без фильтра
Сортировка на листах макросом без фильтра
donMaksimka Дата: Четверг, 21.03.2013, 15:41 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Есть 3 Таблицы
1. Есть таблица с названием "Люди" на одноименном листе с названием "Люди"
2. Есть таблица с названием "Хорошие" на одноименном листе
3. Есть таблица с названием "Плохие" на соответственно одноименном листе
4. Задача: Отображать на листах "Хороние" и "Плохие" в соответствующих таблицах
данные только тех кто подходит по этому признаку; тоько просьба
не применять фильтр так как счетчик перестанет работать
К сообщению приложен файл: 7667121.xlsx (14.6 Kb)
 
Ответить
СообщениеЕсть 3 Таблицы
1. Есть таблица с названием "Люди" на одноименном листе с названием "Люди"
2. Есть таблица с названием "Хорошие" на одноименном листе
3. Есть таблица с названием "Плохие" на соответственно одноименном листе
4. Задача: Отображать на листах "Хороние" и "Плохие" в соответствующих таблицах
данные только тех кто подходит по этому признаку; тоько просьба
не применять фильтр так как счетчик перестанет работать
Автор - donMaksimka
Дата добавления - 21.03.2013 в 15:41
donMaksimka Дата: Четверг, 21.03.2013, 15:47 | Сообщение № 2
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Цитата (donMaksimka)
Задача: Отображать на листах "Хороние" и "Плохие" в соответствующих таблицах
данные только тех кто подходит по этому признаку;
Только с пояснением а то не разбирусь
 
Ответить
Сообщение
Цитата (donMaksimka)
Задача: Отображать на листах "Хороние" и "Плохие" в соответствующих таблицах
данные только тех кто подходит по этому признаку;
Только с пояснением а то не разбирусь
Автор - donMaksimka
Дата добавления - 21.03.2013 в 15:47
_Boroda_ Дата: Четверг, 21.03.2013, 16:08 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 16718
Репутация: 6505 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Для любой ячейки любой из 2-х таблиц (кроме столбца В)

[vba]
Код
=ИНДЕКС(ДВССЫЛ("Люди["&A$4&"]");НАИМЕНЬШИЙ(ЕСЛИ(Люди[Хороший/Плохой]=$B$4;СТРОКА(Люди[ФИО])-СТРОКА(Люди[[#Заголовки];[ФИО]]));СТРОКА()-СТРОКА(A$4)))
[/vba]
К сообщению приложен файл: 7667121_1.xlsx (15.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДля любой ячейки любой из 2-х таблиц (кроме столбца В)

[vba]
Код
=ИНДЕКС(ДВССЫЛ("Люди["&A$4&"]");НАИМЕНЬШИЙ(ЕСЛИ(Люди[Хороший/Плохой]=$B$4;СТРОКА(Люди[ФИО])-СТРОКА(Люди[[#Заголовки];[ФИО]]));СТРОКА()-СТРОКА(A$4)))
[/vba]
Автор - _Boroda_
Дата добавления - 21.03.2013 в 16:08
Jhonson Дата: Четверг, 21.03.2013, 16:15 | Сообщение № 4
Группа: Друзья
Ранг: Ветеран
Сообщений: 514
Репутация: 169 ±
Замечаний: 0% ±

Еще вариант в файле
К сообщению приложен файл: 1234.xlsx (17.8 Kb)

"Ничто не приносит людям столько неприятностей, как разум."
 
Ответить
СообщениеЕще вариант в файле
Автор - Jhonson
Дата добавления - 21.03.2013 в 16:15
donMaksimka Дата: Четверг, 21.03.2013, 16:43 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Цитата (Jhonson)
Еще вариант в файл
Да господа и не знал что можно ограничется просто формулой без макроса smile СПС Вариант Дженсона более широк к применению smile т.к. при изменении Таблици "Люди" Меняется и остальное.
 
Ответить
Сообщение
Цитата (Jhonson)
Еще вариант в файл
Да господа и не знал что можно ограничется просто формулой без макроса smile СПС Вариант Дженсона более широк к применению smile т.к. при изменении Таблици "Люди" Меняется и остальное.
Автор - donMaksimka
Дата добавления - 21.03.2013 в 16:43
AlexM Дата: Четверг, 21.03.2013, 16:50 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Это же надо. Никто макрос не предложил smile
В ячейках формулы типа, так как у меня Excel2003
Код
=ЕСЛИ(ЕОШ(ИНДЕКС(Люди!$A$1:$A$100;Хороший));"";ИНДЕКС(Люди!$A$1:$A$100;Хороший))

Для Excel версий выше
Код
=ЕСЛИОШИБКА(ИНДЕКС(Люди!$A$1:$A$100;Хороший));"")
К сообщению приложен файл: 7667121_new.xls (55.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЭто же надо. Никто макрос не предложил smile
В ячейках формулы типа, так как у меня Excel2003
Код
=ЕСЛИ(ЕОШ(ИНДЕКС(Люди!$A$1:$A$100;Хороший));"";ИНДЕКС(Люди!$A$1:$A$100;Хороший))

Для Excel версий выше
Код
=ЕСЛИОШИБКА(ИНДЕКС(Люди!$A$1:$A$100;Хороший));"")
Автор - AlexM
Дата добавления - 21.03.2013 в 16:50
donMaksimka Дата: Четверг, 21.03.2013, 17:07 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Только почему простым копированием я не могу добавить еще один лист допустим нейтральные? Формулы действуют - копирую Переименовываю таблицу, и что видим????? В чем подвох??? Обьясните
К сообщению приложен файл: Bp321.xlsx (21.6 Kb)
 
Ответить
СообщениеТолько почему простым копированием я не могу добавить еще один лист допустим нейтральные? Формулы действуют - копирую Переименовываю таблицу, и что видим????? В чем подвох??? Обьясните
Автор - donMaksimka
Дата добавления - 21.03.2013 в 17:07
donMaksimka Дата: Четверг, 21.03.2013, 17:38 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

AlexM, Тоже неплохо, но и тут простым копированием и и изменением не проходит Где эти подводные камни???
 
Ответить
СообщениеAlexM, Тоже неплохо, но и тут простым копированием и и изменением не проходит Где эти подводные камни???
Автор - donMaksimka
Дата добавления - 21.03.2013 в 17:38
AlexM Дата: Четверг, 21.03.2013, 18:36 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
В моем варианте использованы именованные формулы. Посмотреть/поправить/добавить можно в диспетчере имен.
Именованные формулы определяют позицию(номер строки) с необходимыми данными для каждого листа
Формула с именем Хороший
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100="Хороший";СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A5))

Формула с именем Плохой
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100="Плохой";СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A5))

Формула с именем Нейтральный
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100="Нейтральный";СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A5))
К сообщению приложен файл: 7667121_new1.xls (67.5 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Сообщение отредактировал AlexM - Четверг, 21.03.2013, 18:38
 
Ответить
СообщениеВ моем варианте использованы именованные формулы. Посмотреть/поправить/добавить можно в диспетчере имен.
Именованные формулы определяют позицию(номер строки) с необходимыми данными для каждого листа
Формула с именем Хороший
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100="Хороший";СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A5))

Формула с именем Плохой
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100="Плохой";СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A5))

Формула с именем Нейтральный
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100="Нейтральный";СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A5))
Автор - AlexM
Дата добавления - 21.03.2013 в 18:36
AlexM Дата: Четверг, 21.03.2013, 19:04 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Еще вариант с одной именованной формулой
Характер
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100=ДВССЫЛ("B4");СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A1))

Хороший, Плохой или Нейтральный формула берет из ячейки В4.
На всех листах формулы получились одинаковые. Думаю копировать будет проще.
К сообщению приложен файл: 7667121_new2.xls (67.0 Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЕще вариант с одной именованной формулой
Характер
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100=ДВССЫЛ("B4");СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A1))

Хороший, Плохой или Нейтральный формула берет из ячейки В4.
На всех листах формулы получились одинаковые. Думаю копировать будет проще.
Автор - AlexM
Дата добавления - 21.03.2013 в 19:04
donMaksimka Дата: Четверг, 21.03.2013, 20:43 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

AlexM, Огромное спасибо, все было так просто, искал глубоко а оно вот оно на видном месте! Самый Оптимальный вариант.
Только хотелось бы уточнить если добавление/удаление записи в Исходную таблицу будет осуществляться с формы, остальные листы будут работать по прежнему или прейдется мудрить? Заранее спасибо за консультацию!!!!
 
Ответить
СообщениеAlexM, Огромное спасибо, все было так просто, искал глубоко а оно вот оно на видном месте! Самый Оптимальный вариант.
Только хотелось бы уточнить если добавление/удаление записи в Исходную таблицу будет осуществляться с формы, остальные листы будут работать по прежнему или прейдется мудрить? Заранее спасибо за консультацию!!!!
Автор - donMaksimka
Дата добавления - 21.03.2013 в 20:43
AlexM Дата: Четверг, 21.03.2013, 21:07 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Именованная формула работает до 100 строки, а на листах с характерами формулы вставлены до 30 строки.
Про добавление с формы сказать не могу. Если сейчас работает, то мудрить придется, если количество данных на листах превысит 30, добавите формулы и если на первом листе таблица будет больше 100 строк, поправите диапазон в именованной формуле.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеИменованная формула работает до 100 строки, а на листах с характерами формулы вставлены до 30 строки.
Про добавление с формы сказать не могу. Если сейчас работает, то мудрить придется, если количество данных на листах превысит 30, добавите формулы и если на первом листе таблица будет больше 100 строк, поправите диапазон в именованной формуле.
Автор - AlexM
Дата добавления - 21.03.2013 в 21:07
donMaksimka Дата: Четверг, 21.03.2013, 21:40 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

AlexM, Так очень хорошо все получается, но это все статичные данные. А если данные будут динамичными, допустим в основной таблице появятся еще две колонки Возраст1 и Рост1 мы получаем на Листах Хороший, Плохой, итп ... еще +2 колонки, Есть ли вариант, при Помощи скажем формулы не получать еще +2 Одноименных колонки а Получать в таблице Плохой В колонке "Возраст" поле со списком, нечто вроде "18" значит рост "150" если "20" рост соответственно "152"
Другими словами на человека по мимо статичной характеристики характер, может быть динамичная характеристика рост в разном возрасте и чтоб не плодить множество колонок в остальных таблицах как то использовать более органомично, но опять же повторюсь Каждый рост привязан к своему возрасту а тот привязан к ФИО
 
Ответить
СообщениеAlexM, Так очень хорошо все получается, но это все статичные данные. А если данные будут динамичными, допустим в основной таблице появятся еще две колонки Возраст1 и Рост1 мы получаем на Листах Хороший, Плохой, итп ... еще +2 колонки, Есть ли вариант, при Помощи скажем формулы не получать еще +2 Одноименных колонки а Получать в таблице Плохой В колонке "Возраст" поле со списком, нечто вроде "18" значит рост "150" если "20" рост соответственно "152"
Другими словами на человека по мимо статичной характеристики характер, может быть динамичная характеристика рост в разном возрасте и чтоб не плодить множество колонок в остальных таблицах как то использовать более органомично, но опять же повторюсь Каждый рост привязан к своему возрасту а тот привязан к ФИО
Автор - donMaksimka
Дата добавления - 21.03.2013 в 21:40
donMaksimka Дата: Четверг, 21.03.2013, 21:53 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Я так понимаю будет что то на основе ГПР Только как связать
 
Ответить
СообщениеЯ так понимаю будет что то на основе ГПР Только как связать
Автор - donMaksimka
Дата добавления - 21.03.2013 в 21:53
AlexM Дата: Четверг, 21.03.2013, 22:07 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Рост связать с возрастом??? Круто!!!
Мне 55, рост 186, а у соседа с таким возрастом рост 160. Почти одинаковый. biggrin
Цитата (AlexM)
Именованные формулы определяют позицию(номер строки) с необходимыми данными

А раз так, то вы можете добавлять в таблицу "Люди" столбцы, например Е, тогда на листах с характерами в новых столбцах Е будут формулы
Код
=ЕСЛИ(ЕОШ(ИНДЕКС(Люди!$E$1:$E$100;Характер));"";ИНДЕКС(Люди!$E$1:$E$100;Характер))

Вроде просто.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеРост связать с возрастом??? Круто!!!
Мне 55, рост 186, а у соседа с таким возрастом рост 160. Почти одинаковый. biggrin
Цитата (AlexM)
Именованные формулы определяют позицию(номер строки) с необходимыми данными

А раз так, то вы можете добавлять в таблицу "Люди" столбцы, например Е, тогда на листах с характерами в новых столбцах Е будут формулы
Код
=ЕСЛИ(ЕОШ(ИНДЕКС(Люди!$E$1:$E$100;Характер));"";ИНДЕКС(Люди!$E$1:$E$100;Характер))

Вроде просто.
Автор - AlexM
Дата добавления - 21.03.2013 в 22:07
donMaksimka Дата: Пятница, 22.03.2013, 09:25 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Всем огромное спасибо за консультацию думаю тему можно закрывать! Информации более чем достаточно. Оказывается все оч просто! Всем еще раз спасибо!!!!
 
Ответить
СообщениеВсем огромное спасибо за консультацию думаю тему можно закрывать! Информации более чем достаточно. Оказывается все оч просто! Всем еще раз спасибо!!!!
Автор - donMaksimka
Дата добавления - 22.03.2013 в 09:25
donMaksimka Дата: Пятница, 22.03.2013, 09:40 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Ой ой ой чуть на забыл, а по чему в таблицах с характерами не действует сортировка от "А-Я" ????? Я так понимаю по тому что программа не видит там имен а видит формулы???? Я Прав? Это не решается?
 
Ответить
СообщениеОй ой ой чуть на забыл, а по чему в таблицах с характерами не действует сортировка от "А-Я" ????? Я так понимаю по тому что программа не видит там имен а видит формулы???? Я Прав? Это не решается?
Автор - donMaksimka
Дата добавления - 22.03.2013 в 09:40
AlexM Дата: Пятница, 22.03.2013, 09:47 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1129 ±
Замечаний: 0% ±

Excel 2003
Цитата (donMaksimka)
чему в таблицах с характерами не действует сортировка от "А-Я"

Обратите внимание на именованную формулу
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100=ДВССЫЛ("B4");СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A1))

Функция НАИМЕНЬШИЙ ориентируется на номер строки СТРОКА(Люди!$A1). При сортировке ничего меняться не будет, так как ориентир остается тот же.


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
Цитата (donMaksimka)
чему в таблицах с характерами не действует сортировка от "А-Я"

Обратите внимание на именованную формулу
Код
=НАИМЕНЬШИЙ(ЕСЛИ(Люди!$B$9:$B$100=ДВССЫЛ("B4");СТРОКА(Люди!$B$9:$B$100);"");СТРОКА(Люди!$A1))

Функция НАИМЕНЬШИЙ ориентируется на номер строки СТРОКА(Люди!$A1). При сортировке ничего меняться не будет, так как ориентир остается тот же.
Автор - AlexM
Дата добавления - 22.03.2013 в 09:47
Serge_007 Дата: Пятница, 22.03.2013, 09:48 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2749 ±
Замечаний: ±

Excel 2016
Цитата (donMaksimka)
Я Прав?
Да
Цитата (donMaksimka)
Это не решается?
Решается сводной таблицей (см. вложение)
К сообщению приложен файл: donMaksimka.xls (60.0 Kb)

ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Цитата (donMaksimka)
Я Прав?
Да
Цитата (donMaksimka)
Это не решается?
Решается сводной таблицей (см. вложение)
Автор - Serge_007
Дата добавления - 22.03.2013 в 09:48
donMaksimka Дата: Суббота, 23.03.2013, 07:05 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 0% ±

Цитата (Serge_007)
Решается сводной таблицей

Да все посмотрел тоже интересный вариант, только обьясните мне в чем причина:
Глядя на ваш пример я вижу что на каждой из страниц фильтр не просто работает а выбирает именно нужные значения, а вот если я ставлю фильтр то он показывает большее количество строк, т. е. возьмем к примеру лист "хорошие" там отфильтрована какая таблица???? Если я как понимаю Изначальная имеется виду "Люди" то она будет показывать что в таблице все равно не 2 строки а 6. Или там другой алгоритм???
Основной вопрос на словах мне буквально в 2х строчках откуда бирем вашу таблицу из чего из той которая на странице "Люди" или из той что на странице"Хорошие"? и если второй вариант, то куда потом прятать вторую
 
Ответить
Сообщение
Цитата (Serge_007)
Решается сводной таблицей

Да все посмотрел тоже интересный вариант, только обьясните мне в чем причина:
Глядя на ваш пример я вижу что на каждой из страниц фильтр не просто работает а выбирает именно нужные значения, а вот если я ставлю фильтр то он показывает большее количество строк, т. е. возьмем к примеру лист "хорошие" там отфильтрована какая таблица???? Если я как понимаю Изначальная имеется виду "Люди" то она будет показывать что в таблице все равно не 2 строки а 6. Или там другой алгоритм???
Основной вопрос на словах мне буквально в 2х строчках откуда бирем вашу таблицу из чего из той которая на странице "Люди" или из той что на странице"Хорошие"? и если второй вариант, то куда потом прятать вторую
Автор - donMaksimka
Дата добавления - 23.03.2013 в 07:05
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка на листах макросом без фильтра
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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