Сортировка формулами - Мир MS Excel

Регистрация

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

= Мир MS Excel/Сортировка формулами - Мир MS Excel

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка формулами (Формулы/Formulas)

Сортировка формулами

Дата: Вторник, 24.03.2015, 13:23 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 56

Репутация: 6 ±

Замечаний: 0% ±

Excel 2010

Остался мне один шаг для решения моей промежуточной задачи и споткнулся я

Есть диапазон C4:C9 с данными, например: «0, 1, 0, 3, 0, 0». Надо чтоб другой диапазон, например D4:D9, заполнился данными, отсортированными по принципу - нули внизу, а числа, отличные от нуля, сверху. В итоге чтоб в D4:D9 стало «1, 3, 0, 0, 0, 0».
Причём порядок сортировки неважен.
Макрос по сортировке применять пока не хочу, хочу формулой, но не могу

Самое обидное, что через НАИМЕНЬШИЙ() и ПОИСК() позиции нулей в диапазоне нахожу, а позиции «не нулей» не могу

P.S. Пример приложить пока не могу, смогу только вечером из дома (если нужно).
P.P.S. Поиском нашёл две темы, не помогли

Ответить

Дата: Вторник, 24.03.2015, 13:30 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4511

Репутация: 1128 ±

Замечаний: 0% ±

Excel 2003

Покажите в файле как делали

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

Ответить

Дата: Вторник, 24.03.2015, 13:41 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 56

Репутация: 6 ±

Замечаний: 0% ±

Excel 2010

AlexM, хорошо, вечером из дома приложу пример.

Ответить

Дата: Вторник, 24.03.2015, 13:50 | Сообщение № 4

Группа: Друзья

Ранг: Старожил

Сообщений: 1347

Репутация: 249 ±

Замечаний: 0% ±

Excel 2010

Формула массива в D4:

Код

=ЕСЛИОШИБКА(ИНДЕКС(C$4:C$9;НАИМЕНЬШИЙ(ЕСЛИ(C$4:C$9>0;СТРОКА(A$1:A$6));СТРОКА(A1)));0)

и тянем вниз.
[moder]Ответ скрыт. Будет пример - откроем.[/moder]
[moder]Открыл

Сообщение отредактировал _Boroda_ - Вторник, 24.03.2015, 21:35

Ответить

Дата: Вторник, 24.03.2015, 14:39 | Сообщение № 5

Группа: Пользователи

Ранг: Участник

Сообщений: 56

Репутация: 6 ±

Замечаний: 0% ±

Excel 2010

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

Ответить

Дата: Вторник, 24.03.2015, 14:45 | Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4511

Репутация: 1128 ±

Замечаний: 0% ±

Excel 2003

На примере будет видна ваша ошибка, значит будет возможность ее исправить. Т.е. вам помочь, а не сделать за вас.

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

Ответить

Дата: Вторник, 24.03.2015, 15:03 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 56

Репутация: 6 ±

Замечаний: 0% ±

Excel 2010

[offtop] AlexM, дело в том, что ошибки пока никакой нет. Есть тупик, в который я попал, и если мне подскажут как сделать то, что я описал в первом сообщении, то я буду двигаться дальше.
Если я попал в тупик по своей глупости, тогда буду придумывать обходные пути решения

[/offtop]

Ответить

Дата: Вторник, 24.03.2015, 15:11 | Сообщение № 8

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4511

Репутация: 1128 ±

Замечаний: 0% ±

Excel 2003

Догадаться можно, в чем тупик, но есть правила и их надо соблюдать.
Вы нарушаете тем, что не прикладываете файл с тупиком.
А помогать нарушителям то же нарушение.
Думаю (вот опять гадаю) ваша формула правильная, но в ней кое-чего не хватает.

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

Ответить

Дата: Вторник, 24.03.2015, 15:19 | Сообщение № 9

Группа: Пользователи

Ранг: Участник

Сообщений: 56

Репутация: 6 ±

Замечаний: 0% ±

Excel 2010

AlexM, ясно. Я же не против прикладывания примеров, но и тут есть некоторые небольшие сложности.

Ответить

Дата: Вторник, 24.03.2015, 16:53 | Сообщение № 10

Группа: Проверенные

Ранг: Ветеран

Сообщений: 713

Репутация: 15 ±

Замечаний: 0% ±

Excel 2010

rvshestakov, На самом деле не только в ошибке не ошибке дело. На примере Можно более емко и как можно более приближенно к реальной работе все показать и описать. Что может значительно сократить время и мозговую активность помогающих. Так как по словесному описанию можно понять слишком разные вещи и придумать 1-10 методов решений из которых может верным ни оказаться ни одно.

Ответить

Дата: Вторник, 24.03.2015, 21:28 | Сообщение № 11

Группа: Пользователи

Ранг: Участник

Сообщений: 56

Репутация: 6 ±

Замечаний: 0% ±

Excel 2010

мне повезло и я добрался дома до ноутбука.
пример во вложении.

К сообщению приложен файл: 6863199.xlsx (12.3 Kb)

Ответить

Дата: Вторник, 24.03.2015, 21:34 | Сообщение № 12

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16675

Репутация: 6481 ±

Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS

Так нужно?

Код

=НАИБОЛЬШИЙ(C$4:C$18;СТРОКА(E1))

К сообщению приложен файл: 6863199_1.xlsx (10.6 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Дата: Вторник, 24.03.2015, 21:51 | Сообщение № 13

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4511

Репутация: 1128 ±

Замечаний: 0% ±

Excel 2003

Если надо по порядку

Код

=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(C$4:C$18;C$4:C$18);СТРОКА(A1));)

Формула массива. Ввод Ctrl+Shift+Enter

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

Ответить

Дата: Среда, 25.03.2015, 06:55 | Сообщение № 14

Группа: Пользователи

Ранг: Участник

Сообщений: 56

Репутация: 6 ±

Замечаний: 0% ±

Excel 2010

_Boroda_, AlexM, Спасибо именно так.
Я догадывался, что вопрос гроша ломаного не стоит, но в связи с моими ограниченными эксель-возможностями рождаются темы подобные этой

Ответить

Дата: Среда, 25.03.2015, 16:08 | Сообщение № 15

Группа: Друзья

Ранг: Старожил

Сообщений: 2347

Репутация: 989 ±

Замечаний: 0% ±

Excel 2007,2010,2013

Вчера, когда смотрел эту тему, из-за отсутствия примера подумал, что из строки типа "0, 1, 0, 3, 0, 0" нужно получить "1, 3, 0, 0, 0, 0", даже формулу с UDF-кой придумал, а оказалось, что числа в отдельных ячейках. Ну так не интересно...

Но раз формула у мну ужо есть, то держите(сь)

формула (массивная)

Код

=JoinArr(ТРАНСП(ЕСЛИОШИБКА(ПРОСМОТР(ПОИСКПОЗ(НАИМЕНЬШИЙ(ЕСЛИ(-ПСТР(A1;ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1);ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1)));ДЛСТР(A1)+1)-ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС(A:A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1));МУМНОЖ(-(-ПСТР(A1;ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1);ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1)));ДЛСТР(A1)+1)-ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС(A:A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1))>=ТРАНСП(-ПСТР(A1;ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1);ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1)));ДЛСТР(A1)+1)-ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС(A:A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))^0));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1)));ЕСЛИ(-ПСТР(A1;ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1);ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1)));ДЛСТР(A1)+1)-ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС(A:A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1));МУМНОЖ(-(-ПСТР(A1;ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1);ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1)));ДЛСТР(A1)+1)-ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС(A:A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1))>=ТРАНСП(-ПСТР(A1;ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1);ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1)));ДЛСТР(A1)+1)-ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС(A:A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))^0)););СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1));--ПСТР(A1;ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1);ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1)));ДЛСТР(A1)+1)-ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ПСТР(A1;СТРОКА($A$1:ИНДЕКС(A:A;ДЛСТР(A1)));1)=",";СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1))));СТРОКА($A$1:ИНДЕКС($A:$A;ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;",";))+1))-1)+2;1)));)))

UDF
[vba]

Код

Function JoinArr$(arr As Variant, Optional delim$ = ", ")
JoinArr = join(arr, delim)
End Function

[/vba]

К сообщению приложен файл: 5397505.xlsm (12.6 Kb)

email:krosav4ig26@gmail.com WMR R207627035142 WMZ Z821145374535 ЯД 410012026478460

Сообщение отредактировал krosav4ig - Среда, 25.03.2015, 16:08

Ответить

Дата: Воскресенье, 23.05.2021, 15:23 | Сообщение № 16

Группа: Пользователи

Ранг: Новичок

Сообщений: 44

Репутация: 0 ±

Замечаний: 0% ±

А помогите мне тоже пожалуйста
Как сделать чтобы список в колонке А отсортировать так чтобы отображались именно те названия напротив которых в колонке В не нуль Помогите пожалуйста

К сообщению приложен файл: 3261988.xlsx (8.9 Kb)

Ответить

Дата: Воскресенье, 23.05.2021, 15:52 | Сообщение № 17

Группа: Друзья

Ранг: Старожил

Сообщений: 1200

Репутация: 313 ±

Замечаний: 0% ±

Excel 2010

Если формулами, то так

Код

=IFERROR(INDEX(A$1:A$1000;AGGREGATE(15;6;ROW($1:$1000)/($B$1:$B$1000<>0);ROWS($1:1)));"")

К сообщению приложен файл: 3166681.xlsx (9.8 Kb)

Ответить

Дата: Воскресенье, 23.05.2021, 18:52 | Сообщение № 18

Группа: Пользователи

Ранг: Новичок

Сообщений: 44

Репутация: 0 ±

Замечаний: 0% ±

jakim, Спасибо большое Супер решение

Ответить

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сортировка формулами (Формулы/Formulas)

Страница 1 из 1
1

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