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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск и отображение недостающих чисел в ряду - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск и отображение недостающих чисел в ряду (Формулы/Formulas)
Поиск и отображение недостающих чисел в ряду
AVI Дата: Четверг, 29.06.2017, 11:33 | Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
Добрый день!
Который день уже думаю как сделать, но никак не могу даже продвинуться к решению задачи.
У меня есть адреса домов и количество квартир, которое по этому адресу должно быть. Но по факту из, например, 20-ти квартир, есть только 18. Проверять какой именно квартиры нет это очень муторно ибо таких домов может быть несколько десятков тысяч. Возможно ли автоматически отображать какие именно квартиры отсутствуют?
К сообщению приложен файл: Microsoft_Excel.xlsx (9.4 Kb)
 
Ответить
СообщениеДобрый день!
Который день уже думаю как сделать, но никак не могу даже продвинуться к решению задачи.
У меня есть адреса домов и количество квартир, которое по этому адресу должно быть. Но по факту из, например, 20-ти квартир, есть только 18. Проверять какой именно квартиры нет это очень муторно ибо таких домов может быть несколько десятков тысяч. Возможно ли автоматически отображать какие именно квартиры отсутствуют?
Автор - AVI
Дата добавления - 29.06.2017 в 11:33
Russel Дата: Четверг, 29.06.2017, 12:01 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
Добрый день, AVI,
как вариант, с допстолбцом, сводной и УФ.
К сообщению приложен файл: 4506641.xlsx (15.0 Kb)

QIWI 9173973973
 
Ответить
СообщениеДобрый день, AVI,
как вариант, с допстолбцом, сводной и УФ.
Автор - Russel
Дата добавления - 29.06.2017 в 12:01
AVI Дата: Четверг, 29.06.2017, 12:20 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
Russel, сводная не пойдет( Спасибо!
 
Ответить
СообщениеRussel, сводная не пойдет( Спасибо!
Автор - AVI
Дата добавления - 29.06.2017 в 12:20
Pelena Дата: Четверг, 29.06.2017, 12:32 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19569
Репутация: 4646 ±
Замечаний: ±

Excel 365 & Mac Excel
Можно UDF
К сообщению приложен файл: 4506641.xlsm (16.6 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеМожно UDF
Автор - Pelena
Дата добавления - 29.06.2017 в 12:32
Sancho Дата: Четверг, 29.06.2017, 13:11 | Сообщение № 5
Группа: Проверенные
Ранг: Обитатель
Сообщений: 279
Репутация: 19 ±
Замечаний: 0% ±

2007, 2010, 2013
Pelena, думаю необходимо в функцию добавить строку [vba]
Код
Application.Volatile True
[/vba] иначе при изменении списка квартир или корректировка списка домов с количеством квартир приходится заходить в каждую ячейку с функцией и подтверждать ввод, что бы произошел пересчет
 
Ответить
СообщениеPelena, думаю необходимо в функцию добавить строку [vba]
Код
Application.Volatile True
[/vba] иначе при изменении списка квартир или корректировка списка домов с количеством квартир приходится заходить в каждую ячейку с функцией и подтверждать ввод, что бы произошел пересчет
Автор - Sancho
Дата добавления - 29.06.2017 в 13:11
AlexM Дата: Четверг, 29.06.2017, 13:30 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
Ради интереса вариант с формулой массива.
Недостающие номера по одному в ячейке.
Код
=--ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СМЕЩ($C$1;ПОИСКПОЗ($F3&$G3;$A$2:$A$99&$B$2:$B$99;);;СЧЁТ(1/($F3&$G3=$A$2:$A$99&$B$2:$B$99)));СТРОКА(A1:ИНДЕКС(A:A;МАКС(($F3&$G3=$A$3:$A$99&$B$3:$B$99)*$C$3:$C$99))))=0;СТРОКА(A1:ИНДЕКС(A:A;1+МАКС(($F3&$G3=$A$3:$A$99&$B$3:$B$99)*$C$3:$C$99))));СТОЛБЕЦ(A1));"[<="&$H3&"]0;;")

Ошибка скрыта УФ, а можно скрыть функцией ЕСЛИОШИБКА()
К сообщению приложен файл: 3459090.xlsx (11.3 Kb)


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

Сообщение отредактировал AlexM - Четверг, 29.06.2017, 14:09
 
Ответить
СообщениеРади интереса вариант с формулой массива.
Недостающие номера по одному в ячейке.
Код
=--ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СМЕЩ($C$1;ПОИСКПОЗ($F3&$G3;$A$2:$A$99&$B$2:$B$99;);;СЧЁТ(1/($F3&$G3=$A$2:$A$99&$B$2:$B$99)));СТРОКА(A1:ИНДЕКС(A:A;МАКС(($F3&$G3=$A$3:$A$99&$B$3:$B$99)*$C$3:$C$99))))=0;СТРОКА(A1:ИНДЕКС(A:A;1+МАКС(($F3&$G3=$A$3:$A$99&$B$3:$B$99)*$C$3:$C$99))));СТОЛБЕЦ(A1));"[<="&$H3&"]0;;")

Ошибка скрыта УФ, а можно скрыть функцией ЕСЛИОШИБКА()
Автор - AlexM
Дата добавления - 29.06.2017 в 13:30
AVI Дата: Пятница, 30.06.2017, 04:20 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
Почему-то не могу лайкнуть. Pelena, Sancho, Спасибо!!работает как часы!
AlexM, Я пытался придумать формулу, но до такого я бы точно не догадался. Самое ужасное, что я даже разобраться в ней не могу(


Сообщение отредактировал AVI - Пятница, 30.06.2017, 04:29
 
Ответить
СообщениеПочему-то не могу лайкнуть. Pelena, Sancho, Спасибо!!работает как часы!
AlexM, Я пытался придумать формулу, но до такого я бы точно не догадался. Самое ужасное, что я даже разобраться в ней не могу(
Автор - AVI
Дата добавления - 30.06.2017 в 04:20
AVI Дата: Пятница, 30.06.2017, 04:38 | Сообщение № 8
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
Попытался доделать сам, но не смог.
Я добавил еще один столбец с городом. А как в формуле это поменять?
К сообщению приложен файл: ____.xlsm (17.5 Kb)
 
Ответить
СообщениеПопытался доделать сам, но не смог.
Я добавил еще один столбец с городом. А как в формуле это поменять?
Автор - AVI
Дата добавления - 30.06.2017 в 04:38
Pelena Дата: Пятница, 30.06.2017, 07:20 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 19569
Репутация: 4646 ±
Замечаний: ±

Excel 365 & Mac Excel
Проверьте
К сообщению приложен файл: 6622437.xlsm (17.9 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеПроверьте
Автор - Pelena
Дата добавления - 30.06.2017 в 07:20
AVI Дата: Пятница, 30.06.2017, 07:35 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация: 17 ±
Замечаний: 0% ±

Excel 2016
Pelena, Спасибо, понял как надо было исправлять
 
Ответить
СообщениеPelena, Спасибо, понял как надо было исправлять
Автор - AVI
Дата добавления - 30.06.2017 в 07:35
vikttur Дата: Пятница, 30.06.2017, 09:14 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Формула массива:
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(A$1:ИНДЕКС(A:A;$H3));ЕСЛИ($A$3:$A$74=$F3;ЕСЛИ($B$3:$B$74=$G3;$C$3:$C$74));));СТРОКА(A$1:ИНДЕКС(A:A;$H3)));СТОЛБЕЦ(A1));"")

Количество квартир. Лучше ограничить вычисления:
Код
=МАКС(ЕСЛИ(F3=$A$3:$A$74;ЕСЛИ(G3=$B$3:$B$74;$C$3:$C$74)))
 
Ответить
СообщениеФормула массива:
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(A$1:ИНДЕКС(A:A;$H3));ЕСЛИ($A$3:$A$74=$F3;ЕСЛИ($B$3:$B$74=$G3;$C$3:$C$74));));СТРОКА(A$1:ИНДЕКС(A:A;$H3)));СТОЛБЕЦ(A1));"")

Количество квартир. Лучше ограничить вычисления:
Код
=МАКС(ЕСЛИ(F3=$A$3:$A$74;ЕСЛИ(G3=$B$3:$B$74;$C$3:$C$74)))
Автор - vikttur
Дата добавления - 30.06.2017 в 09:14
AlexM Дата: Пятница, 30.06.2017, 10:05 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4517
Репутация: 1130 ±
Замечаний: 0% ±

Excel 2003
vikttur, Здорово!
А для количества квартир у меня формула была чуть короче. :)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениеvikttur, Здорово!
А для количества квартир у меня формула была чуть короче. :)
Автор - AlexM
Дата добавления - 30.06.2017 в 10:05
vikttur Дата: Пятница, 30.06.2017, 10:12 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Репутация: 526 ±
Замечаний: 0% ±

Я видел. Это та же формула. Но, как всегда - лучше быстрее, чем короче )


Сообщение отредактировал vikttur - Пятница, 30.06.2017, 10:13
 
Ответить
СообщениеЯ видел. Это та же формула. Но, как всегда - лучше быстрее, чем короче )
Автор - vikttur
Дата добавления - 30.06.2017 в 10:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск и отображение недостающих чисел в ряду (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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