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

Вход

Регистрация

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

 

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

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

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
Группа: Друзья
Ранг: Старожил
Сообщений: 2706
Репутация: 477 ±
Замечаний: 0% ±

Собственно, вопрос: где есть диапазон чисел? У Вас?
В таком случае берите карандаш, листок бумаги, вписывайте пропущенные, переносите на лист Excel. Не забудьте карандаш заточить :)
 
Ответить
СообщениеСобственно, вопрос: где есть диапазон чисел? У Вас?
В таком случае берите карандаш, листок бумаги, вписывайте пропущенные, переносите на лист Excel. Не забудьте карандаш заточить :)

Автор - vikttur
Дата добавления - 03.01.2018 в 15:15
noobik Дата: Среда, 03.01.2018, 15:22 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

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


Сообщение отредактировал noobik - Среда, 03.01.2018, 15:22
 
Ответить
СообщениеТак и сделал :)

Автор - noobik
Дата добавления - 03.01.2018 в 15:22
bmv98rus Дата: Среда, 03.01.2018, 15:39 | Сообщение № 4
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

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


Сообщение отредактировал 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4692
Репутация: 1011 ±
Замечаний: 0% ±

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


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщение
Код
=НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(A1:A1001)-1;A$1:A$1000;));СТРОКА(A1:A1001)-1);СТРОКА(C1))

Автор - Nic70y
Дата добавления - 03.01.2018 в 15:57
noobik Дата: Среда, 03.01.2018, 16:14 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 26
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо) Да я знаю, что пример надо прикладывать, да только там примера - один столбец с цифрами) Пробовал немного сам разобраться, вышло корявенько)
Еще раз всем спасибо.
 
Ответить
СообщениеСпасибо) Да я знаю, что пример надо прикладывать, да только там примера - один столбец с цифрами) Пробовал немного сам разобраться, вышло корявенько)
Еще раз всем спасибо.

Автор - noobik
Дата добавления - 03.01.2018 в 16:14
vikttur Дата: Среда, 03.01.2018, 16:16 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2706
Репутация: 477 ±
Замечаний: 0% ±

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

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


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

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

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

Excel 2010
Но это были не Вы.
Еще раз всем огромное спасибо.
 
Ответить
СообщениеНо это были не Вы.
Еще раз всем огромное спасибо.

Автор - noobik
Дата добавления - 03.01.2018 в 16:30
bmv98rus Дата: Среда, 03.01.2018, 16:40 | Сообщение № 9
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

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

Автор - bmv98rus
Дата добавления - 03.01.2018 в 16:40
vikttur Дата: Среда, 03.01.2018, 17:09 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2706
Репутация: 477 ±
Замечаний: 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 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)


Сообщение отредактировал 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4692
Репутация: 1011 ±
Замечаний: 0% ±

Excel 2013
bmv98rus,
от 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)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)
 
Ответить
Сообщениеbmv98rus,
от 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 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)


Сообщение отредактировал 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
Группа: Друзья
Ранг: Старожил
Сообщений: 2706
Репутация: 477 ±
Замечаний: 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 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));"")
 
Ответить
Сообщение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
Группа: Друзья
Ранг: Старожил
Сообщений: 2706
Репутация: 477 ±
Замечаний: 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
Группа: Проверенные
Ранг: Старожил
Сообщений: 1261
Репутация: 214 ±
Замечаний: 0% ±

Excel 2013/2016
[offtop]
MATCH будет перебирать весь указанный

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

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

Автор - bmv98rus
Дата добавления - 03.01.2018 в 22:08
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как показать пропущенные числа из диапазона чисел? (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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