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

Вход

Регистрация

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

 

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

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

Excel 2013
Добрый день!
Который день уже думаю как сделать, но никак не могу даже продвинуться к решению задачи.
У меня есть адреса домов и количество квартир, которое по этому адресу должно быть. Но по факту из, например, 20-ти квартир, есть только 18. Проверять какой именно квартиры нет это очень муторно ибо таких домов может быть несколько десятков тысяч. Возможно ли автоматически отображать какие именно квартиры отсутствуют?
К сообщению приложен файл: Microsoft_Excel.xlsx(9Kb)
 
Ответить
СообщениеДобрый день!
Который день уже думаю как сделать, но никак не могу даже продвинуться к решению задачи.
У меня есть адреса домов и количество квартир, которое по этому адресу должно быть. Но по факту из, например, 20-ти квартир, есть только 18. Проверять какой именно квартиры нет это очень муторно ибо таких домов может быть несколько десятков тысяч. Возможно ли автоматически отображать какие именно квартиры отсутствуют?

Автор - AVI
Дата добавления - 29.06.2017 в 11:33
Russel Дата: Четверг, 29.06.2017, 12:01 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1263
Репутация: 286 ±
Замечаний: 0% ±

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


QIWI 9173973973
 
Ответить
СообщениеДобрый день, AVI,
как вариант, с допстолбцом, сводной и УФ.

Автор - Russel
Дата добавления - 29.06.2017 в 12:01
AVI Дата: Четверг, 29.06.2017, 12:20 | Сообщение № 3
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 222
Репутация: 6 ±
Замечаний: 0% ±

Excel 2013
Russel, сводная не пойдет( Спасибо!
 
Ответить
СообщениеRussel, сводная не пойдет( Спасибо!

Автор - AVI
Дата добавления - 29.06.2017 в 12:20
Pelena Дата: Четверг, 29.06.2017, 12:32 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11467
Репутация: 2546 ±
Замечаний: 0% ±

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


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеМожно UDF

Автор - Pelena
Дата добавления - 29.06.2017 в 12:32
Sancho Дата: Четверг, 29.06.2017, 13:11 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 181
Репутация: 10 ±
Замечаний: 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3584
Репутация: 902 ±
Замечаний: 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(11Kb)



Номер мобильного модема (без голосовой связи)
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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 222
Репутация: 6 ±
Замечаний: 0% ±

Excel 2013
Почему-то не могу лайкнуть. 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
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 222
Репутация: 6 ±
Замечаний: 0% ±

Excel 2013
Попытался доделать сам, но не смог.
Я добавил еще один столбец с городом. А как в формуле это поменять?
К сообщению приложен файл: ____.xlsm(18Kb)
 
Ответить
СообщениеПопытался доделать сам, но не смог.
Я добавил еще один столбец с городом. А как в формуле это поменять?

Автор - AVI
Дата добавления - 30.06.2017 в 04:38
Pelena Дата: Пятница, 30.06.2017, 07:20 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 11467
Репутация: 2546 ±
Замечаний: 0% ±

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


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеПроверьте

Автор - Pelena
Дата добавления - 30.06.2017 в 07:20
AVI Дата: Пятница, 30.06.2017, 07:35 | Сообщение № 10
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 222
Репутация: 6 ±
Замечаний: 0% ±

Excel 2013
Pelena, Спасибо, понял как надо было исправлять
 
Ответить
СообщениеPelena, Спасибо, понял как надо было исправлять

Автор - AVI
Дата добавления - 30.06.2017 в 07:35
vikttur Дата: Пятница, 30.06.2017, 09:14 | Сообщение № 11
Группа: Друзья
Ранг: Старожил
Сообщений: 2387
Репутация: 412 ±
Замечаний: 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3584
Репутация: 902 ±
Замечаний: 0% ±

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



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениеvikttur, Здорово!
А для количества квартир у меня формула была чуть короче. :)

Автор - AlexM
Дата добавления - 30.06.2017 в 10:05
vikttur Дата: Пятница, 30.06.2017, 10:12 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2387
Репутация: 412 ±
Замечаний: 0% ±

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


Сообщение отредактировал vikttur - Пятница, 30.06.2017, 10:13
 
Ответить
СообщениеЯ видел. Это та же формула. Но, как всегда - лучше быстрее, чем короче )

Автор - vikttur
Дата добавления - 30.06.2017 в 10:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск и отображение недостающих чисел в ряду (Формулы/Formulas)
Страница 1 из 11
Поиск:

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