Сравнение чисел, диапазон которых указан в двух ячейках

Дата: Пятница, 06.01.2017, 19:52 | Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Здраствуйте,
думал, что решение простое, но ничего не нашел. Можно ли решить подобную задачу без использования макросов.

Имеется таблица с тремя столбцами. Значения первых двух - переменные натуральные числа. Значение первого столбца - начало диапазона чисел,второго - конец диапазона. Третий - количество чисел в диапазоне, заданном значениями ячеек первых двух столбцов.

Задача такая, нужна проверка диапазона чисел, заданных значениями ячеек первых двух столбцов на каждой строке, на пересечение с таким диапазоном в других строках.

Например:
Имеем 4 строки с переменными значениями:
A3=25, B3=28,
A4=60, B4=65,
A5=100, B5=103,
A6=58, B6=63.

То есть, исходя из таблицы, имеем 4 диапазона натуральных чисел:
1={25;26;27;28},
2={60;61;62;63;64;65},
3={100;101;102;103},
4={58;59;60;61;62;63}.

Нужно сверить диапазон каждой строки с диапазонами на других строках. И, если диапазон на строке пересекается с другим, выводить в третьем стобце сообщение "ошибка", вместо количества чисел.

В примере, второй диапазон пересекается с четвертым, то есть совпадают числа 60, 61, 62 ,63, значит в ячейке C4 должно быть "ошибка", так же как и в C6.

Есть какие-нибудь идеи как это можно реализовать?

К сообщению приложен файл: 9292781.xls (20.0 Kb)

Сообщение отредактировал bboohh - Пятница, 06.01.2017, 19:55

СообщениеЗдраствуйте,
думал, что решение простое, но ничего не нашел. Можно ли решить подобную задачу без использования макросов.

Имеется таблица с тремя столбцами. Значения первых двух - переменные натуральные числа. Значение первого столбца - начало диапазона чисел,второго - конец диапазона. Третий - количество чисел в диапазоне, заданном значениями ячеек первых двух столбцов.

Задача такая, нужна проверка диапазона чисел, заданных значениями ячеек первых двух столбцов на каждой строке, на пересечение с таким диапазоном в других строках.

Например:
Имеем 4 строки с переменными значениями:
A3=25, B3=28,
A4=60, B4=65,
A5=100, B5=103,
A6=58, B6=63.

То есть, исходя из таблицы, имеем 4 диапазона натуральных чисел:
1={25;26;27;28},
2={60;61;62;63;64;65},
3={100;101;102;103},
4={58;59;60;61;62;63}.

Нужно сверить диапазон каждой строки с диапазонами на других строках. И, если диапазон на строке пересекается с другим, выводить в третьем стобце сообщение "ошибка", вместо количества чисел.

В примере, второй диапазон пересекается с четвертым, то есть совпадают числа 60, 61, 62 ,63, значит в ячейке C4 должно быть "ошибка", так же как и в C6.

Есть какие-нибудь идеи как это можно реализовать?

Автор - bboohh
Дата добавления - 06.01.2017 в 19:52

Дата: Пятница, 06.01.2017, 20:19 | Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8962

Репутация: 2344 ±

Замечаний: 0% ±

Excel 2010

вдруг правильно

Код

=ЕСЛИ((СУММПРОИЗВ((A3<=B$3:B$9)*(A3>=A$3:A$9)+(B3>=A$3:A$9)*(B3<=B$3:B$9))>2)*(A3<>"");"ошибка";"")

К сообщению приложен файл: 9901662.xls (27.5 Kb)

ЮMoney 41001841029809

Дата: Пятница, 06.01.2017, 20:40 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Nic70y,
Спасибо, формула работает как нужно.

Только вот не понимаю, как она работает. Не могли бы вы поподробней объяснить, что происходит в этой формуле?

Сообщение отредактировал bboohh - Пятница, 06.01.2017, 20:47

Дата: Пятница, 06.01.2017, 20:54 | Сообщение № 4

Группа: Друзья

Ранг: Экселист

Сообщений: 8962

Репутация: 2344 ±

Замечаний: 0% ±

Excel 2010

если B3>=A$3:A$9 и одновременно B3<=B$3:B$9 значит B3 входит в диапазон, аналогично и с A3

Цитата bboohh, 06.01.2017 в 20:40, в сообщении № 3 (

)

(B3>=A$3:A$9)*(B3<=B$3:B$9)

поскольку A3 и B3 полюбому входят в свой же диапазон будет мин 1+1=2,
поэтому проверяем >2

Цитата Nic70y, 06.01.2017 в 20:19, в сообщении № 2 ()

A3<>""

проверка есть ли данные в строках, иначе формула примет "пустоту" за 0
[p.s.]
что-то форум косячит при редактировании сооб. при наличии цитат[/p.s.]

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Пятница, 06.01.2017, 21:00

Дата: Пятница, 06.01.2017, 21:08 | Сообщение № 5

Группа: Друзья

Ранг: Экселист

Сообщений: 8962

Репутация: 2344 ±

Замечаний: 0% ±

Excel 2010

Неправильная формула оказалась

К сообщению приложен файл: 7141353.xls (27.5 Kb)

ЮMoney 41001841029809

Дата: Пятница, 06.01.2017, 21:11 | Сообщение № 6

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Я тоже заметил, только при других значениях

К сообщению приложен файл: 2281569.xls (21.5 Kb)

Дата: Пятница, 06.01.2017, 21:13 | Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

По той формуле вопрос:
если

Цитата

поскольку A3 и B3 полюбому входят в свой же диапазон будет мин 1+1=2

,то зачем тогда нужно :(A3<=B$3:B$9)*(A3>=A$3:A$9)+(B3>=A$3:A$9)*(B3<=B$3:B$9),
оно ведь при любых значениях будет 2.

Так я и не понял, как эта формула работает

Сообщение отредактировал bboohh - Пятница, 06.01.2017, 21:15

Дата: Пятница, 06.01.2017, 21:21 | Сообщение № 8

Группа: Друзья

Ранг: Экселист

Сообщений: 8962

Репутация: 2344 ±

Замечаний: 0% ±

Excel 2010

ну вот вроде бы правильная наверное
массивная:

Код

=ЕСЛИ(СЧЁТ(ЕСЛИ(A3<=B$3:B$9;ЕСЛИ(A$3:A$9<=B3;A$3:A$9)))>1;"ошибка";"")

Цитата bboohh, 06.01.2017 в 21:13, в сообщении № 7 ()

оно ведь при любых значениях будет 2.

нет, при вхождении в другие диапазоны будет больше 2

К сообщению приложен файл: 6736676.xls (28.0 Kb)

ЮMoney 41001841029809

Дата: Пятница, 06.01.2017, 21:31 | Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Цитата

нет, при вхождении в другие диапазоны будет больше 2

точно, все разобрался.

Что-то пошло не так

К сообщению приложен файл: 5748282.xls (22.0 Kb)

Сообщение отредактировал bboohh - Пятница, 06.01.2017, 21:33