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

Вход

Регистрация

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

 

= Мир MS Excel/Отображение пропущенных чисел из диапазона по доп. условию - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Отображение пропущенных чисел из диапазона по доп. условию (Формулы/Formulas)
Отображение пропущенных чисел из диапазона по доп. условию
ЕСЛИВГОЛОВЕМАЛО Дата: Четверг, 28.05.2020, 10:13 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Доброго времени суток. Подскажите, возможна формула для отображения пропущенных чисел из диапазона, но с условием.
Например: пропущенные числа из диапазона у Саши.
На форуме нашел закрытую тему, там умные люди написали формулу, которая работает, но без условия:
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА($A1:$A1001)-1;$A:$A;0));СТРОКА($A1:$A1001)-1);СТРОКА(A1));"")

Возможно в нее включить условие? У меня тяму не хватает.
Пример во вложении.
К сообщению приложен файл: 12.xls(24.5 Kb)


Сообщение отредактировал ЕСЛИВГОЛОВЕМАЛО - Четверг, 28.05.2020, 11:45
 
Ответить
СообщениеДоброго времени суток. Подскажите, возможна формула для отображения пропущенных чисел из диапазона, но с условием.
Например: пропущенные числа из диапазона у Саши.
На форуме нашел закрытую тему, там умные люди написали формулу, которая работает, но без условия:
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА($A1:$A1001)-1;$A:$A;0));СТРОКА($A1:$A1001)-1);СТРОКА(A1));"")

Возможно в нее включить условие? У меня тяму не хватает.
Пример во вложении.

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 28.05.2020 в 10:13
gling Дата: Четверг, 28.05.2020, 10:59 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2371
Репутация: 617 ±
Замечаний: 0% ±

2010
Здравствуйте.
Странная какая то задача. Есть фамилии и числа, а считать нужно карандаши. Какой должен быть результат, покажите в каком виде.


ЯД-41001506838083
 
Ответить
СообщениеЗдравствуйте.
Странная какая то задача. Есть фамилии и числа, а считать нужно карандаши. Какой должен быть результат, покажите в каком виде.

Автор - gling
Дата добавления - 28.05.2020 в 10:59
ЕСЛИВГОЛОВЕМАЛО Дата: Четверг, 28.05.2020, 11:16 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Прошу прощение, пример был не корректный, файл исправил.
Логика такая: формула отображает пропущенные числа для каждой фамилии начиная с минимального пропущенного (если из 1го до 5, пропущены числа 2 и 3, то сначала 2, потом 3), потом следующего за максимальным т.е. 6 т.к. 5 есть.
К сообщению приложен файл: 1163847.xls(24.5 Kb)


Сообщение отредактировал ЕСЛИВГОЛОВЕМАЛО - Четверг, 28.05.2020, 11:46
 
Ответить
СообщениеПрошу прощение, пример был не корректный, файл исправил.
Логика такая: формула отображает пропущенные числа для каждой фамилии начиная с минимального пропущенного (если из 1го до 5, пропущены числа 2 и 3, то сначала 2, потом 3), потом следующего за максимальным т.е. 6 т.к. 5 есть.

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 28.05.2020 в 11:16
китин Дата: Четверг, 28.05.2020, 11:27 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 6895
Репутация: 1056 ±
Замечаний: 0% ±

Excel 2007;2010;2016
ЕСЛИВГОЛОВЕМАЛО, - Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеЕСЛИВГОЛОВЕМАЛО, - Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)

Автор - китин
Дата добавления - 28.05.2020 в 11:27
ЕСЛИВГОЛОВЕМАЛО Дата: Четверг, 28.05.2020, 11:47 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Спасибо, прочел, вроде все поправил согласно правилам.
 
Ответить
СообщениеСпасибо, прочел, вроде все поправил согласно правилам.

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 28.05.2020 в 11:47
gling Дата: Четверг, 28.05.2020, 11:53 | Сообщение № 6
Группа: Друзья
Ранг: Старожил
Сообщений: 2371
Репутация: 617 ±
Замечаний: 0% ±

2010
Если числа всегда идут по порядку, то такой вариант
Код
=ЕСЛИОШИБКА(СУММПРОИЗВ(ПОИСКПОЗ(1;($A$1:$A$12=D3)*($B$1:$B$12="");))-ПОИСКПОЗ(D3;$A$1:$A$12;);(СЧЁТЕСЛИ(A1:$A$12;D3)))
К сообщению приложен файл: 7714668.xlsx(9.0 Kb)


ЯД-41001506838083
 
Ответить
СообщениеЕсли числа всегда идут по порядку, то такой вариант
Код
=ЕСЛИОШИБКА(СУММПРОИЗВ(ПОИСКПОЗ(1;($A$1:$A$12=D3)*($B$1:$B$12="");))-ПОИСКПОЗ(D3;$A$1:$A$12;);(СЧЁТЕСЛИ(A1:$A$12;D3)))

Автор - gling
Дата добавления - 28.05.2020 в 11:53
ЕСЛИВГОЛОВЕМАЛО Дата: Четверг, 28.05.2020, 12:05 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Спасибо, но к сожалению могут идти в перемешку, если бы не сказали, я бы и не задумался на этим.
 
Ответить
СообщениеСпасибо, но к сожалению могут идти в перемешку, если бы не сказали, я бы и не задумался на этим.

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 28.05.2020 в 12:05
ЕСЛИВГОЛОВЕМАЛО Дата: Четверг, 28.05.2020, 12:10 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Цифры которые будут пропускаться, будут добавляться потом, поэтому нумерация будет не всегда по порядку, например 1,2,3,4 __ 6,7, пять пропусти и введется после 7.
 
Ответить
СообщениеЦифры которые будут пропускаться, будут добавляться потом, поэтому нумерация будет не всегда по порядку, например 1,2,3,4 __ 6,7, пять пропусти и введется после 7.

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 28.05.2020 в 12:10
gling Дата: Четверг, 28.05.2020, 12:33 | Сообщение № 9
Группа: Друзья
Ранг: Старожил
Сообщений: 2371
Репутация: 617 ±
Замечаний: 0% ±

2010
Цитата ЕСЛИВГОЛОВЕМАЛО, 28.05.2020 в 12:10, в сообщении № 8 ()
пять пропусти и введется после 7
Это не важно, рост чисел будет по мере роста строк? Может ли быть так 1; 7; 2; _; 5; 3; 6; _; не хватает каких, 0 и 4 по возрастанию? Надеюсь что не может, а для вашего вариант формула работать должна, ведь для 7 создастся новая строка в столбце А будет фамилия. и если после 7 введется 5, то ответ формула выдаст 8. Пробуйте, подсказывайте что не так получается.


ЯД-41001506838083
 
Ответить
Сообщение
Цитата ЕСЛИВГОЛОВЕМАЛО, 28.05.2020 в 12:10, в сообщении № 8 ()
пять пропусти и введется после 7
Это не важно, рост чисел будет по мере роста строк? Может ли быть так 1; 7; 2; _; 5; 3; 6; _; не хватает каких, 0 и 4 по возрастанию? Надеюсь что не может, а для вашего вариант формула работать должна, ведь для 7 создастся новая строка в столбце А будет фамилия. и если после 7 введется 5, то ответ формула выдаст 8. Пробуйте, подсказывайте что не так получается.

Автор - gling
Дата добавления - 28.05.2020 в 12:33
ЕСЛИВГОЛОВЕМАЛО Дата: Четверг, 28.05.2020, 18:48 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Да, рост чисел будет по мере роста строк.
Может ли быть так 1; 7; 2; _; 5; 3; 6; _; не хватает каких, 0 и 4 по возрастанию?
- тут не понял Ваш вопрос
Таблица будет заполнятся так:
ввели: Иван 0 - (формула показывает 1)
ввели: Иван 1 - (формула показывает 2)
ввели: Иван 2 - (формула показывает 3)
ввели: Иван 5 - (забыли 3 и 4) - формула тут показывать 3,
ввели: Иван 3 - (формула показывает 4)
ввели: Иван 4 - (формула показывает 6)

В вашей формуле немножко не так, в примере указал.
К сообщению приложен файл: 17.xlsx(8.6 Kb)
 
Ответить
СообщениеДа, рост чисел будет по мере роста строк.
Может ли быть так 1; 7; 2; _; 5; 3; 6; _; не хватает каких, 0 и 4 по возрастанию?
- тут не понял Ваш вопрос
Таблица будет заполнятся так:
ввели: Иван 0 - (формула показывает 1)
ввели: Иван 1 - (формула показывает 2)
ввели: Иван 2 - (формула показывает 3)
ввели: Иван 5 - (забыли 3 и 4) - формула тут показывать 3,
ввели: Иван 3 - (формула показывает 4)
ввели: Иван 4 - (формула показывает 6)

В вашей формуле немножко не так, в примере указал.

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 28.05.2020 в 18:48
gling Дата: Четверг, 28.05.2020, 20:45 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2371
Репутация: 617 ±
Замечаний: 0% ±

2010
Цитата ЕСЛИВГОЛОВЕМАЛО, 28.05.2020 в 18:48, в сообщении № 10 ()
Иван 5 - (забыли 3 и 4) - формула тут показывать 3,
После 2 должно быть 3, это и показывает формула.
Первоначально у вас были пустые ячейки для пропущенных чисел, по этому и спрашивал, нарушится ли порядок по мере возрастания строк. Моя формула рассчитана на правильный порядок, находит наименьшею пустую строку и указывает какое число в ней отсутствует. А как вы пишите необходимо определить какие числа присутствуют у Ивана от минимального до максимального, определить наименьшее отсутствующее между ними, показать его, а если все имеются то нужно показать на 1 больше максимального. А это совсем другая задача и всё что сделано можно затереть и начать заново. 10 сообщений ушло на выяснение задачи.
Формула массива
Код
=ПОИСКПОЗ(1=1;ЕОШИБКА(ПОИСКПОЗ(СТРОКА($B$1:$B$18);($A$1:$A$18=D5)*($B$1:$B$18);));)
Нулевое значение не победил, но начиная с 1 показывает вроде правильно. Проверяйте, или 0 обязательно нужен?
К сообщению приложен файл: 17-1-.xlsx(8.9 Kb)


ЯД-41001506838083

Сообщение отредактировал gling - Четверг, 28.05.2020, 21:23
 
Ответить
Сообщение
Цитата ЕСЛИВГОЛОВЕМАЛО, 28.05.2020 в 18:48, в сообщении № 10 ()
Иван 5 - (забыли 3 и 4) - формула тут показывать 3,
После 2 должно быть 3, это и показывает формула.
Первоначально у вас были пустые ячейки для пропущенных чисел, по этому и спрашивал, нарушится ли порядок по мере возрастания строк. Моя формула рассчитана на правильный порядок, находит наименьшею пустую строку и указывает какое число в ней отсутствует. А как вы пишите необходимо определить какие числа присутствуют у Ивана от минимального до максимального, определить наименьшее отсутствующее между ними, показать его, а если все имеются то нужно показать на 1 больше максимального. А это совсем другая задача и всё что сделано можно затереть и начать заново. 10 сообщений ушло на выяснение задачи.
Формула массива
Код
=ПОИСКПОЗ(1=1;ЕОШИБКА(ПОИСКПОЗ(СТРОКА($B$1:$B$18);($A$1:$A$18=D5)*($B$1:$B$18);));)
Нулевое значение не победил, но начиная с 1 показывает вроде правильно. Проверяйте, или 0 обязательно нужен?

Автор - gling
Дата добавления - 28.05.2020 в 20:45
ЕСЛИВГОЛОВЕМАЛО Дата: Четверг, 28.05.2020, 21:02 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
А как вы пишите необходимо определить какие числа присутствуют у Ивана от минимального до максимального, определить наименьшее отсутствующее между ними, показать его, а если все имеются то нужно показать на 1 больше максимального

Вы идеально описали задачу! Не могу похвастаться тем же....
 
Ответить
Сообщение
А как вы пишите необходимо определить какие числа присутствуют у Ивана от минимального до максимального, определить наименьшее отсутствующее между ними, показать его, а если все имеются то нужно показать на 1 больше максимального

Вы идеально описали задачу! Не могу похвастаться тем же....

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 28.05.2020 в 21:02
Pelena Дата: Четверг, 28.05.2020, 21:32 | Сообщение № 13
Группа: Админы
Ранг: Местный житель
Сообщений: 18551
Репутация: 4200 ±
Замечаний: ±

Excel 2016 & Mac Excel
После того, как Владимир
Цитата ЕСЛИВГОЛОВЕМАЛО, 28.05.2020 в 21:02, в сообщении № 12 ()
идеально описали задачу
появилось такое решение
Код
=АГРЕГАТ(15;6;(СТРОКА($A$1:$A$100)-1)/(СЧЁТЕСЛИМН($B$1:$B$100;СТРОКА($A$1:$A$100)-1;$A$1:$A$100;D2)=0);1)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПосле того, как Владимир
Цитата ЕСЛИВГОЛОВЕМАЛО, 28.05.2020 в 21:02, в сообщении № 12 ()
идеально описали задачу
появилось такое решение
Код
=АГРЕГАТ(15;6;(СТРОКА($A$1:$A$100)-1)/(СЧЁТЕСЛИМН($B$1:$B$100;СТРОКА($A$1:$A$100)-1;$A$1:$A$100;D2)=0);1)

Автор - Pelena
Дата добавления - 28.05.2020 в 21:32
ЕСЛИВГОЛОВЕМАЛО Дата: Четверг, 28.05.2020, 22:52 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Нулевое значение не победил, но начиная с 1 показывает вроде правильно. Проверяйте, или 0 обязательно нужен?


0 не обязателен

появилось такое решение


gling и Pelena обе формулы работают идеально, вы просто боги exel, от всего сердца поклон, решили не посильную для меня задачу)

Подскажите еще для саморазвития где научится, или что почитать?, как вы вяжите все формулы и их логику в одно формулу? А то я только несколько ЕСЛИ сцепить могу, или посчитать по условию). А вы выдаете метровые формулы:)))
 
Ответить
Сообщение
Нулевое значение не победил, но начиная с 1 показывает вроде правильно. Проверяйте, или 0 обязательно нужен?


0 не обязателен

появилось такое решение


gling и Pelena обе формулы работают идеально, вы просто боги exel, от всего сердца поклон, решили не посильную для меня задачу)

Подскажите еще для саморазвития где научится, или что почитать?, как вы вяжите все формулы и их логику в одно формулу? А то я только несколько ЕСЛИ сцепить могу, или посчитать по условию). А вы выдаете метровые формулы:)))

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 28.05.2020 в 22:52
ЕСЛИВГОЛОВЕМАЛО Дата: Вторник, 02.06.2020, 17:36 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Снова подниму тему, обнаружил "ошибку" если ее так можно назвать в обоих формулах, если добавить вверху пустую строку или несколько строк (для названия столбцов и т.д., для "шапки") то все сбивается, хотя диапазон в формулах их неохватывает, от этого можно уйти?
К сообщению приложен файл: 18.xlsx(10.1 Kb)
 
Ответить
СообщениеСнова подниму тему, обнаружил "ошибку" если ее так можно назвать в обоих формулах, если добавить вверху пустую строку или несколько строк (для названия столбцов и т.д., для "шапки") то все сбивается, хотя диапазон в формулах их неохватывает, от этого можно уйти?

Автор - ЕСЛИВГОЛОВЕМАЛО
Дата добавления - 02.06.2020 в 17:36
Che79 Дата: Вторник, 02.06.2020, 17:46 | Сообщение № 16
Группа: Друзья
Ранг: Старожил
Сообщений: 1649
Репутация: 306 ±
Замечаний: 0% ±

2013 Win, 365 Mac
ЕСЛИВГОЛОВЕМАЛО, у Вас диапазоны в формулах поехали при добавлении строк. Почитайте про абсолютные и относительные ссылки в Excel
К сообщению приложен файл: 18_1.xlsx(10.1 Kb)


Делай нормально и будет нормально!

Сообщение отредактировал Che79 - Вторник, 02.06.2020, 18:03
 
Ответить
СообщениеЕСЛИВГОЛОВЕМАЛО, у Вас диапазоны в формулах поехали при добавлении строк. Почитайте про абсолютные и относительные ссылки в Excel

Автор - Che79
Дата добавления - 02.06.2020 в 17:46
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Отображение пропущенных чисел из диапазона по доп. условию (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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