Поиск и отображение недостающих чисел в ряду
AVI
Дата: Четверг, 29.06.2017, 11:33 |
Сообщение № 1
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация:
17
±
Замечаний:
0% ±
Excel 2016
Добрый день! Который день уже думаю как сделать, но никак не могу даже продвинуться к решению задачи. У меня есть адреса домов и количество квартир, которое по этому адресу должно быть. Но по факту из, например, 20-ти квартир, есть только 18. Проверять какой именно квартиры нет это очень муторно ибо таких домов может быть несколько десятков тысяч. Возможно ли автоматически отображать какие именно квартиры отсутствуют?
Добрый день! Который день уже думаю как сделать, но никак не могу даже продвинуться к решению задачи. У меня есть адреса домов и количество квартир, которое по этому адресу должно быть. Но по факту из, например, 20-ти квартир, есть только 18. Проверять какой именно квартиры нет это очень муторно ибо таких домов может быть несколько десятков тысяч. Возможно ли автоматически отображать какие именно квартиры отсутствуют? AVI
Ответить
Сообщение Добрый день! Который день уже думаю как сделать, но никак не могу даже продвинуться к решению задачи. У меня есть адреса домов и количество квартир, которое по этому адресу должно быть. Но по факту из, например, 20-ти квартир, есть только 18. Проверять какой именно квартиры нет это очень муторно ибо таких домов может быть несколько десятков тысяч. Возможно ли автоматически отображать какие именно квартиры отсутствуют? Автор - AVI Дата добавления - 29.06.2017 в 11:33
Russel
Дата: Четверг, 29.06.2017, 12:01 |
Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация:
320
±
Замечаний:
0% ±
Excel 2010
Добрый день, AVI , как вариант, с допстолбцом, сводной и УФ.
Добрый день, AVI , как вариант, с допстолбцом, сводной и УФ. Russel
QIWI 9173973973
Ответить
Сообщение Добрый день, AVI , как вариант, с допстолбцом, сводной и УФ. Автор - Russel Дата добавления - 29.06.2017 в 12:01
AVI
Дата: Четверг, 29.06.2017, 12:20 |
Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация:
17
±
Замечаний:
0% ±
Excel 2016
Russel , сводная не пойдет( Спасибо!
Russel , сводная не пойдет( Спасибо!AVI
Ответить
Сообщение Russel , сводная не пойдет( Спасибо!Автор - AVI Дата добавления - 29.06.2017 в 12:20
Pelena
Дата: Четверг, 29.06.2017, 12:32 |
Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 19177
Репутация:
4417
±
Замечаний:
±
Excel 365 & Mac Excel
Можно UDF
"Черт возьми, Холмс! Но как??!!" Ю-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
Ответить
Сообщение Pelena , думаю необходимо в функцию добавить строку [vba]Код
Application.Volatile True
[/vba] иначе при изменении списка квартир или корректировка списка домов с количеством квартир приходится заходить в каждую ячейку с функцией и подтверждать ввод, что бы произошел пересчетАвтор - Sancho Дата добавления - 29.06.2017 в 13:11
AlexM
Дата: Четверг, 29.06.2017, 13:30 |
Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация:
1114
±
Замечаний:
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;;")
Ошибка скрыта УФ, а можно скрыть функцией ЕСЛИОШИБКА()
Ради интереса вариант с формулой массива. Недостающие номера по одному в ячейке.Код
=--ТЕКСТ(НАИМЕНЬШИЙ(ЕСЛИ(ЧАСТОТА(СМЕЩ($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
Номер мобильного модема (без голосовой связи) 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 , Я пытался придумать формулу, но до такого я бы точно не догадался. Самое ужасное, что я даже разобраться в ней не могу(
Почему-то не могу лайкнуть. Pelena , Sancho , Спасибо!!работает как часы!AlexM , Я пытался придумать формулу, но до такого я бы точно не догадался. Самое ужасное, что я даже разобраться в ней не могу( AVI
Сообщение отредактировал 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
Попытался доделать сам, но не смог. Я добавил еще один столбец с городом. А как в формуле это поменять?
Попытался доделать сам, но не смог. Я добавил еще один столбец с городом. А как в формуле это поменять? AVI
К сообщению приложен файл:
____.xlsm
(17.5 Kb)
Ответить
Сообщение Попытался доделать сам, но не смог. Я добавил еще один столбец с городом. А как в формуле это поменять? Автор - AVI Дата добавления - 30.06.2017 в 04:38
Pelena
Дата: Пятница, 30.06.2017, 07:20 |
Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 19177
Репутация:
4417
±
Замечаний:
±
Excel 365 & Mac Excel
Проверьте
"Черт возьми, Холмс! Но как??!!" Ю-money 41001765434816
Ответить
Сообщение Проверьте Автор - Pelena Дата добавления - 30.06.2017 в 07:20
AVI
Дата: Пятница, 30.06.2017, 07:35 |
Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 523
Репутация:
17
±
Замечаний:
0% ±
Excel 2016
Pelena , Спасибо, понял как надо было исправлять
Pelena , Спасибо, понял как надо было исправлятьAVI
Ответить
Сообщение Pelena , Спасибо, понял как надо было исправлятьАвтор - AVI Дата добавления - 30.06.2017 в 07:35
vikttur
Дата: Пятница, 30.06.2017, 09:14 |
Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Формула массива:Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(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
Ответить
Сообщение Формула массива:Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(ЕНД(ПОИСКПОЗ(СТРОКА(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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4480
Репутация:
1114
±
Замечаний:
0% ±
Excel 2003
vikttur , Здорово! А для количества квартир у меня формула была чуть короче.
vikttur , Здорово! А для количества квартир у меня формула была чуть короче. AlexM
Номер мобильного модема (без голосовой связи) 9269171249 МегаФон, Московский регион.
Ответить
Сообщение vikttur , Здорово! А для количества квартир у меня формула была чуть короче. Автор - AlexM Дата добавления - 30.06.2017 в 10:05
vikttur
Дата: Пятница, 30.06.2017, 10:12 |
Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2941
Я видел. Это та же формула. Но, как всегда - лучше быстрее, чем короче )
Я видел. Это та же формула. Но, как всегда - лучше быстрее, чем короче ) vikttur
Сообщение отредактировал vikttur - Пятница, 30.06.2017, 10:13
Ответить
Сообщение Я видел. Это та же формула. Но, как всегда - лучше быстрее, чем короче ) Автор - vikttur Дата добавления - 30.06.2017 в 10:12