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

Вход

Регистрация

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

 

= Мир MS Excel/Как показать пропущенные числа из диапазона чисел? - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как показать пропущенные числа из диапазона чисел? (Формулы/Formulas)
Как показать пропущенные числа из диапазона чисел?
noobik Дата: Среда, 03.01.2018, 15:02 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Доброго времени суток! Собственно, вопрос: есть диапазон чисел от 0 до 1000, в столбце показаны не все числа (например, 1, 5, 20, 34 и т.д), как в отдельном столбце отобразить пропущенные числа?
 
Ответить
СообщениеДоброго времени суток! Собственно, вопрос: есть диапазон чисел от 0 до 1000, в столбце показаны не все числа (например, 1, 5, 20, 34 и т.д), как в отдельном столбце отобразить пропущенные числа?
Автор - noobik
Дата добавления - 03.01.2018 в 15:02
vikttur Дата: Среда, 03.01.2018, 15:15 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Собственно, вопрос: где есть диапазон чисел? У Вас?
В таком случае берите карандаш, листок бумаги, вписывайте пропущенные, переносите на лист Excel. Не забудьте карандаш заточить :)
 
Ответить
СообщениеСобственно, вопрос: где есть диапазон чисел? У Вас?
В таком случае берите карандаш, листок бумаги, вписывайте пропущенные, переносите на лист Excel. Не забудьте карандаш заточить :)
Автор - vikttur
Дата добавления - 03.01.2018 в 15:15
noobik Дата: Среда, 03.01.2018, 15:22 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Так и сделал :)


Сообщение отредактировал noobik - Среда, 03.01.2018, 15:22
 
Ответить
СообщениеТак и сделал :)
Автор - noobik
Дата добавления - 03.01.2018 в 15:22
bmv98rus Дата: Среда, 03.01.2018, 15:39 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
noobik, вам vikttur так намекнул, что пример, пусть даже простой, нужно приложить.
Массивная
Код
=IFERROR(SMALL(IF(COUNTIF(A:A;ROW($1:$1000))=0;ROW($1:$1000);9^9);ROW());"")

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 03.01.2018, 17:58
 
Ответить
Сообщениеnoobik, вам vikttur так намекнул, что пример, пусть даже простой, нужно приложить.
Массивная
Код
=IFERROR(SMALL(IF(COUNTIF(A:A;ROW($1:$1000))=0;ROW($1:$1000);9^9);ROW());"")
Автор - bmv98rus
Дата добавления - 03.01.2018 в 15:39
Nic70y Дата: Среда, 03.01.2018, 15:57 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2273 ±
Замечаний: 0% ±

Excel 2010
Код
=НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(A1:A1001)-1;A$1:A$1000;));СТРОКА(A1:A1001)-1);СТРОКА(C1))

ЮMoney 41001841029809
 
Ответить
Сообщение
Код
=НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(A1:A1001)-1;A$1:A$1000;));СТРОКА(A1:A1001)-1);СТРОКА(C1))
Автор - Nic70y
Дата добавления - 03.01.2018 в 15:57
noobik Дата: Среда, 03.01.2018, 16:14 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Спасибо) Да я знаю, что пример надо прикладывать, да только там примера - один столбец с цифрами) Пробовал немного сам разобраться, вышло корявенько)
Еще раз всем спасибо.
 
Ответить
СообщениеСпасибо) Да я знаю, что пример надо прикладывать, да только там примера - один столбец с цифрами) Пробовал немного сам разобраться, вышло корявенько)
Еще раз всем спасибо.
Автор - noobik
Дата добавления - 03.01.2018 в 16:14
vikttur Дата: Среда, 03.01.2018, 16:16 | Сообщение № 7
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Цитата
да только там примера - один столбец с цифрами

Кому не лень, тот вместо Вас создал у себя пример (один столбец с цифрами) и на нем рисовал формулу...


Сообщение отредактировал vikttur - Среда, 03.01.2018, 16:18
 
Ответить
Сообщение
Цитата
да только там примера - один столбец с цифрами

Кому не лень, тот вместо Вас создал у себя пример (один столбец с цифрами) и на нем рисовал формулу...
Автор - vikttur
Дата добавления - 03.01.2018 в 16:16
noobik Дата: Среда, 03.01.2018, 16:30 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 30
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Но это были не Вы.
Еще раз всем огромное спасибо.
 
Ответить
СообщениеНо это были не Вы.
Еще раз всем огромное спасибо.
Автор - noobik
Дата добавления - 03.01.2018 в 16:30
bmv98rus Дата: Среда, 03.01.2018, 16:40 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
vikttur, иногда я сильно жалею, что на планете формулы не переводятся как тут,, а порой наоборот. ;-)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеvikttur, иногда я сильно жалею, что на планете формулы не переводятся как тут,, а порой наоборот. ;-)
Автор - bmv98rus
Дата добавления - 03.01.2018 в 16:40
vikttur Дата: Среда, 03.01.2018, 17:09 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

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

P.S. Глянул формулы... Не уверен, что работают правильно. Пропускаются числа - не закреплены верхние границы диапазонов. А проверить негде :)
bmv98rus, что-то одно лишнее: или 9^9, или проверка на ошибку.


Сообщение отредактировал vikttur - Среда, 03.01.2018, 17:40
 
Ответить
Сообщениеnoobik, мое бухтение - не о лени. Вы не поняли основного - пример нужно показывать, даже небольшой - помогающим легче и меньше времени уйдет на помощь. А часто бывает, что пример помогает понять то,что недосказано в описании задачи: расположение данных, их форматы и проч.

P.S. Глянул формулы... Не уверен, что работают правильно. Пропускаются числа - не закреплены верхние границы диапазонов. А проверить негде :)
bmv98rus, что-то одно лишнее: или 9^9, или проверка на ошибку.
Автор - vikttur
Дата добавления - 03.01.2018 в 17:09
bmv98rus Дата: Среда, 03.01.2018, 18:20 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
vikttur, Про $1 я сам давно заметил. Спера так сделал, а потом убрал случайно. на ошибку проверял с целью скрыть все что выше 1000 при размножении, но недодумал, что появятся группа 9^9 в конце списка. А так как уникальности в исходных данных гарантировать нельзя, то анализ сколько пропущено изначально сильно усложнит, но надо подумать.

тормознуто вышло
Код
=IF(ROW()<=(1000-SUMPRODUCT(IF(COUNTIF($A$1:$A$1000;$A$1:$A$1000)>0;1/COUNTIF($A$1:$A$1000;$A$1:$A$1000);0)));SMALL(IF(COUNTIF($A$1:$A$1000;ROW($1:$1000))=0;ROW($1:$1000);9^9);ROW());"")
К сообщению приложен файл: noobik.xlsx (10.1 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 03.01.2018, 18:56
 
Ответить
Сообщениеvikttur, Про $1 я сам давно заметил. Спера так сделал, а потом убрал случайно. на ошибку проверял с целью скрыть все что выше 1000 при размножении, но недодумал, что появятся группа 9^9 в конце списка. А так как уникальности в исходных данных гарантировать нельзя, то анализ сколько пропущено изначально сильно усложнит, но надо подумать.

тормознуто вышло
Код
=IF(ROW()<=(1000-SUMPRODUCT(IF(COUNTIF($A$1:$A$1000;$A$1:$A$1000)>0;1/COUNTIF($A$1:$A$1000;$A$1:$A$1000);0)));SMALL(IF(COUNTIF($A$1:$A$1000;ROW($1:$1000))=0;ROW($1:$1000);9^9);ROW());"")
Автор - bmv98rus
Дата добавления - 03.01.2018 в 18:20
Nic70y Дата: Среда, 03.01.2018, 19:19 | Сообщение № 12
Группа: Друзья
Ранг: Экселист
Сообщений: 8759
Репутация: 2273 ±
Замечаний: 0% ±

Excel 2010
bmv98rus,
Цитата noobik, 03.01.2018 в 15:02, в сообщении № 1 ()
от 0 до 1000
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(A$1:A$1001)-1;A$1:ИНДЕКС(A:A;ПОИСКПОЗ(9E+307;A:A));));СТРОКА(A$1:A$1001)-1);СТРОКА(C1));"")
К сообщению приложен файл: 1154899.xlsx (31.0 Kb)

ЮMoney 41001841029809
 
Ответить
Сообщениеbmv98rus,
Цитата noobik, 03.01.2018 в 15:02, в сообщении № 1 ()
от 0 до 1000
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(A$1:A$1001)-1;A$1:ИНДЕКС(A:A;ПОИСКПОЗ(9E+307;A:A));));СТРОКА(A$1:A$1001)-1);СТРОКА(C1));"")
Автор - Nic70y
Дата добавления - 03.01.2018 в 19:19
bmv98rus Дата: Среда, 03.01.2018, 19:58 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Nic70y, Ага, А я за одно поморщил мозг на предмет вычисления количества уникальных в исходно. + не обратил внимание, что от 0 надо, но это поправимо, ну и еще один обработчик ошибки :-)

Код
=IFERROR(MATCH(SMALL(IF(ISNA(MATCH(ROW($1:$1001)-1;$A:$A;0));ROW($1:$1001);9^9);ROW());ROW(A:A);0)-1;"")

ну и конечно
Код
=IFERROR(AGGREGATE(15;6;(ROW($1:$1001)-1)/ISNA(MATCH(ROW($1:$1001)-1;$A:$A;0));ROW());"")
К сообщению приложен файл: noobik2.xlsx (9.9 Kb)

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Среда, 03.01.2018, 20:27
 
Ответить
СообщениеNic70y, Ага, А я за одно поморщил мозг на предмет вычисления количества уникальных в исходно. + не обратил внимание, что от 0 надо, но это поправимо, ну и еще один обработчик ошибки :-)

Код
=IFERROR(MATCH(SMALL(IF(ISNA(MATCH(ROW($1:$1001)-1;$A:$A;0));ROW($1:$1001);9^9);ROW());ROW(A:A);0)-1;"")

ну и конечно
Код
=IFERROR(AGGREGATE(15;6;(ROW($1:$1001)-1)/ISNA(MATCH(ROW($1:$1001)-1;$A:$A;0));ROW());"")
Автор - bmv98rus
Дата добавления - 03.01.2018 в 19:58
vikttur Дата: Среда, 03.01.2018, 20:30 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

bmv98rus, эта же (из сообщения №4) рабочая:
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A:A;СТРОКА($1:$1001)-1)=0;СТРОКА($1:$1001)-1);СТРОКА(A1));)


Вариант для любого количества ячеек в диапазоне:
Код
=МИН(ЕСЛИ(СЧЁТЕСЛИ(A:A;C1+СТРОКА($1:$1001))=0;C1+СТРОКА($1:$1001)))

При очень больших массивах увеличить нижний диапазон. Хотя... не понадобится - на больших массивах будет тормозить )
Да, и ноль отслеживать отдельно.
К сообщению приложен файл: 1000.xlsx (9.0 Kb)


Сообщение отредактировал vikttur - Среда, 03.01.2018, 20:58
 
Ответить
Сообщениеbmv98rus, эта же (из сообщения №4) рабочая:
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A:A;СТРОКА($1:$1001)-1)=0;СТРОКА($1:$1001)-1);СТРОКА(A1));)


Вариант для любого количества ячеек в диапазоне:
Код
=МИН(ЕСЛИ(СЧЁТЕСЛИ(A:A;C1+СТРОКА($1:$1001))=0;C1+СТРОКА($1:$1001)))

При очень больших массивах увеличить нижний диапазон. Хотя... не понадобится - на больших массивах будет тормозить )
Да, и ноль отслеживать отдельно.
Автор - vikttur
Дата добавления - 03.01.2018 в 20:30
bmv98rus Дата: Среда, 03.01.2018, 20:57 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
vikttur, c поправкой на ,"") для красоты, да. :-) не учел что SMALL игнорит False/True , ну и понеслось :-) .
А вот что будет работать быстрее COUNTIF или MATCH? По идее Match до первого найденного, значит должно быть быстрее и экономичнее.
Код
=IFERROR(SMALL(IF(ISNA(MATCH(ROW($1:$1001)-1;$A:$A;0));ROW($1:$1001)-1);ROW(A1));"")

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщениеvikttur, c поправкой на ,"") для красоты, да. :-) не учел что SMALL игнорит False/True , ну и понеслось :-) .
А вот что будет работать быстрее COUNTIF или MATCH? По идее Match до первого найденного, значит должно быть быстрее и экономичнее.
Код
=IFERROR(SMALL(IF(ISNA(MATCH(ROW($1:$1001)-1;$A:$A;0));ROW($1:$1001)-1);ROW(A1));"")
Автор - bmv98rus
Дата добавления - 03.01.2018 в 20:57
vikttur Дата: Среда, 03.01.2018, 21:01 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Цитата
c поправкой на ,"")

Жертвую красотой сознательно. Мухи к мухам, котлеты отдельно.

Цитата
что будет работать быстрее

Если по возрастанию, то MATCH с неточным поиском вообще летать будет ). Бинарный поиск работает быстрее.
COUNTIF умеет определять диапазон, MATCH будет перебирать весь указанный.


Сообщение отредактировал vikttur - Среда, 03.01.2018, 21:06
 
Ответить
Сообщение
Цитата
c поправкой на ,"")

Жертвую красотой сознательно. Мухи к мухам, котлеты отдельно.

Цитата
что будет работать быстрее

Если по возрастанию, то MATCH с неточным поиском вообще летать будет ). Бинарный поиск работает быстрее.
COUNTIF умеет определять диапазон, MATCH будет перебирать весь указанный.
Автор - vikttur
Дата добавления - 03.01.2018 в 21:01
bmv98rus Дата: Среда, 03.01.2018, 22:08 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4107
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
[offtop]
Цитата vikttur, 03.01.2018 в 21:01, в сообщении № 16 ()
MATCH будет перебирать весь указанный

Спорить не буду, но мне кажется все оптимизированы под диапазон занятый, правд Match массив ожидает ... [/offtop]

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение[offtop]
Цитата vikttur, 03.01.2018 в 21:01, в сообщении № 16 ()
MATCH будет перебирать весь указанный

Спорить не буду, но мне кажется все оптимизированы под диапазон занятый, правд Match массив ожидает ... [/offtop]
Автор - bmv98rus
Дата добавления - 03.01.2018 в 22:08
ЕСЛИВГОЛОВЕМАЛО Дата: Среда, 27.05.2020, 17:07 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Цитата bmv98rus, 03.01.2018 в 20:57, в сообщении № 15 ()
vikttur, c поправкой на ,"") для красоты, да. :-) не учел что SMALL игнорит False/True , ну и понеслось :-) .
А вот что будет работать быстрее COUNTIF или MATCH? По идее Match до первого найденного, значит должно быть быстрее и экономичнее.

=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА($1:$1001)-1;$A:$A;0));СТРОКА($1:$1001)-1);СТРОКА(A1));"")


Добрый день. Использовал вашу формулу, но не совсем подходит. Возможно ли ее усложнить?, чтобы выводилось число еще и с доп условием, например: пропущенное или следующее число для "Ивана". Exel знаю не так хорошо, чтобы включить условие в вашу формулу:(
К сообщению приложен файл: 11.xlsx (8.4 Kb)
 
Ответить
Сообщение
Цитата bmv98rus, 03.01.2018 в 20:57, в сообщении № 15 ()
vikttur, c поправкой на ,"") для красоты, да. :-) не учел что SMALL игнорит False/True , ну и понеслось :-) .
А вот что будет работать быстрее COUNTIF или MATCH? По идее Match до первого найденного, значит должно быть быстрее и экономичнее.

=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА($1:$1001)-1;$A:$A;0));СТРОКА($1:$1001)-1);СТРОКА(A1));"")


Добрый день. Использовал вашу формулу, но не совсем подходит. Возможно ли ее усложнить?, чтобы выводилось число еще и с доп условием, например: пропущенное или следующее число для "Ивана". Exel знаю не так хорошо, чтобы включить условие в вашу формулу:(
Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 27.05.2020 в 17:07
Pelena Дата: Среда, 27.05.2020, 18:15 | Сообщение № 19
Группа: Админы
Ранг: Местный житель
Сообщений: 19179
Репутация: 4419 ±
Замечаний: ±

Excel 365 & Mac Excel
ЕСЛИВГОЛОВЕМАЛО, создайте свою тему, эта тема закрыта

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеЕСЛИВГОЛОВЕМАЛО, создайте свою тему, эта тема закрыта
Автор - Pelena
Дата добавления - 27.05.2020 в 18:15
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как показать пропущенные числа из диапазона чисел? (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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