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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск и обозначение взаимоисключающих данных в таблице - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск и обозначение взаимоисключающих данных в таблице (Формулы/Formulas)
Поиск и обозначение взаимоисключающих данных в таблице
vitos88 Дата: Пятница, 07.06.2019, 08:43 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация: 5 ±
Замечаний: 0% ±

Добрый день! Сердечно поздравляю всех с долгожданной пятницей!
Задача упростить формулу, которая ищет в результатах анкетирования ошибки ввода. Например, одновременное указывание мужского и женского пола. Мне на ум приходит формула типа:
Код
=если(или(счётз(ячейки с полом по строке 1)>1;счётз(ячейки с полом по строке 2)>1;счётз(ячейки с полом по строке 3)>1;счётз(ячейки с полом по строке 4)>1; и т.д. до 100);"ошибка";"нет ошибки")

Всё дело в том, что таких строк может быть 100, а вписывать это в формулу очень муторно. Может, есть варианты упростить задачу?
К сообщению приложен файл: 7266334.xlsx (10.3 Kb)
 
Ответить
СообщениеДобрый день! Сердечно поздравляю всех с долгожданной пятницей!
Задача упростить формулу, которая ищет в результатах анкетирования ошибки ввода. Например, одновременное указывание мужского и женского пола. Мне на ум приходит формула типа:
Код
=если(или(счётз(ячейки с полом по строке 1)>1;счётз(ячейки с полом по строке 2)>1;счётз(ячейки с полом по строке 3)>1;счётз(ячейки с полом по строке 4)>1; и т.д. до 100);"ошибка";"нет ошибки")

Всё дело в том, что таких строк может быть 100, а вписывать это в формулу очень муторно. Может, есть варианты упростить задачу?
Автор - vitos88
Дата добавления - 07.06.2019 в 08:43
китин Дата: Пятница, 07.06.2019, 08:59 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Добрый и вам. так?
Код
=ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")

и немного УФ
К сообщению приложен файл: vitos88.xlsx (10.7 Kb)

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеДобрый и вам. так?
Код
=ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")

и немного УФ
Автор - китин
Дата добавления - 07.06.2019 в 08:59
vitos88 Дата: Пятница, 07.06.2019, 09:03 | Сообщение № 3
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация: 5 ±
Замечаний: 0% ±

Да, спасибо!!!
 
Ответить
СообщениеДа, спасибо!!!
Автор - vitos88
Дата добавления - 07.06.2019 в 09:03
китин Дата: Пятница, 07.06.2019, 09:06 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
добавил файл в пост выше с УФ

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениедобавил файл в пост выше с УФ
Автор - китин
Дата добавления - 07.06.2019 в 09:06
_Boroda_ Дата: Пятница, 07.06.2019, 09:18 | Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще вариант
Формула
Код
=СУММПРОИЗВ(--(B6:B41&C6:C41<>C6:C41&B6:B41))

Формат ячейки
"Ошибка";"Нет ошибок"
И Условное форматирование
Код
=$B6&$C6<>$C6&$B6
К сообщению приложен файл: 7266334_1.xlsx (10.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант
Формула
Код
=СУММПРОИЗВ(--(B6:B41&C6:C41<>C6:C41&B6:B41))

Формат ячейки
"Ошибка";"Нет ошибок"
И Условное форматирование
Код
=$B6&$C6<>$C6&$B6
Автор - _Boroda_
Дата добавления - 07.06.2019 в 09:18
bmv98rus Дата: Пятница, 07.06.2019, 09:55 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Да чтож вы так SUMIFS не любите
Код
=IF(COUNTIFS(B6:B41;"<>";C6:C41;"<>")>0;"Ошибка";"Нет ошибок")

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеДа чтож вы так SUMIFS не любите
Код
=IF(COUNTIFS(B6:B41;"<>";C6:C41;"<>")>0;"Ошибка";"Нет ошибок")
Автор - bmv98rus
Дата добавления - 07.06.2019 в 09:55
vitos88 Дата: Пятница, 07.06.2019, 10:03 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация: 5 ±
Замечаний: 0% ±

Цитата китин, 07.06.2019 в 08:59, в сообщении № 2 ()

=ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")


Будет ли это работать с тремя, пятью вариантами ответов, а не только с двумя?


Сообщение отредактировал vitos88 - Пятница, 07.06.2019, 10:13
 
Ответить
Сообщение
Цитата китин, 07.06.2019 в 08:59, в сообщении № 2 ()

=ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")


Будет ли это работать с тремя, пятью вариантами ответов, а не только с двумя?
Автор - vitos88
Дата добавления - 07.06.2019 в 10:03
китин Дата: Пятница, 07.06.2019, 10:12 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 7014
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
vitos88, не цитируйте весь пост . Это нарушение Правил форума. исправляйте.
и поясните, что вы имеете в виду на примере

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщениеvitos88, не цитируйте весь пост . Это нарушение Правил форума. исправляйте.
и поясните, что вы имеете в виду на примере
Автор - китин
Дата добавления - 07.06.2019 в 10:12
vitos88 Дата: Пятница, 07.06.2019, 10:16 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация: 5 ±
Замечаний: 0% ±

_Boroda_, формула не работает, если заполнять таблицу одинаковыми символами.
 
Ответить
Сообщение_Boroda_, формула не работает, если заполнять таблицу одинаковыми символами.
Автор - vitos88
Дата добавления - 07.06.2019 в 10:16
_Boroda_ Дата: Пятница, 07.06.2019, 10:23 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
А Вы не заполняйте :)

Кстати, зачем вообще 2 столбца в таблице? Что не сделаете один столбец "Пол" и в нем выбор из вып списка вариантов "М" и "Ж"?
Аналогичный вопрос и про возраст
Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать
Как-то так примерно
К сообщению приложен файл: 7266334_2.xlsx (9.8 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеА Вы не заполняйте :)

Кстати, зачем вообще 2 столбца в таблице? Что не сделаете один столбец "Пол" и в нем выбор из вып списка вариантов "М" и "Ж"?
Аналогичный вопрос и про возраст
Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать
Как-то так примерно
Автор - _Boroda_
Дата добавления - 07.06.2019 в 10:23
bmv98rus Дата: Пятница, 07.06.2019, 10:41 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата vitos88, 07.06.2019 в 10:03, в сообщении № 7 ()
Будет ли это работать с тремя, пятью вариантами ответов,

Мне вот интересно, ну ладно три пола я знаю, Мужской, Женский , Медвежий, но еще то два какие?

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
Сообщение
Цитата vitos88, 07.06.2019 в 10:03, в сообщении № 7 ()
Будет ли это работать с тремя, пятью вариантами ответов,

Мне вот интересно, ну ладно три пола я знаю, Мужской, Женский , Медвежий, но еще то два какие?
Автор - bmv98rus
Дата добавления - 07.06.2019 в 10:41
_Boroda_ Дата: Пятница, 07.06.2019, 10:43 | Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
[offtop]Очень просто:
Мужской
Женский
Медвежий
Другое
Пока не определился[/offtop]

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение[offtop]Очень просто:
Мужской
Женский
Медвежий
Другое
Пока не определился[/offtop]
Автор - _Boroda_
Дата добавления - 07.06.2019 в 10:43
vitos88 Дата: Пятница, 07.06.2019, 11:07 | Сообщение № 13
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация: 5 ±
Замечаний: 0% ±

_Boroda_,
Цитата _Boroda_, 07.06.2019 в 10:23, в сообщении № 10 ()
Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать

ошибки ввода будет сделать сложнее, но вот заполнение результатов будет идти медленнее, согласитесь, гораздо проще любой символ тыкнуть в ячейку, чем выбирать нужный из списка или вводить вручную. На счёт обработки данных, то мне проще обрабатывать именно такую таблицу, там формулы проще получаются.
А по поводу трёх колонок, анкета не состоит из одного вопроса про пол, там есть вопросы и с пятью вариантами ответов.
Я так понимаю, что упростить формулу с более чем двумя ответами становится намного сложнее, если, конечно, оно вообще возможно.
 
Ответить
Сообщение_Boroda_,
Цитата _Boroda_, 07.06.2019 в 10:23, в сообщении № 10 ()
Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать

ошибки ввода будет сделать сложнее, но вот заполнение результатов будет идти медленнее, согласитесь, гораздо проще любой символ тыкнуть в ячейку, чем выбирать нужный из списка или вводить вручную. На счёт обработки данных, то мне проще обрабатывать именно такую таблицу, там формулы проще получаются.
А по поводу трёх колонок, анкета не состоит из одного вопроса про пол, там есть вопросы и с пятью вариантами ответов.
Я так понимаю, что упростить формулу с более чем двумя ответами становится намного сложнее, если, конечно, оно вообще возможно.
Автор - vitos88
Дата добавления - 07.06.2019 в 11:07
_Boroda_ Дата: Пятница, 07.06.2019, 11:17 | Сообщение № 14
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Категорически несогласен. Особенно про последующую обработку и простоту формул. Но как угодно, дело Ваше

По поводу множества вариантов - у Вас есть блок типа
Цитата китин, 07.06.2019 в 08:59, в сообщении № 2 ()
($B$6:$B$41<>"")
или
Цитата bmv98rus, 07.06.2019 в 09:55, в сообщении № 6 ()
B6:B41;"<>"

Разможьте его (блок) столько раз, сколько нужно. Только столбцы поменяйте - В на С,D,E,...

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеКатегорически несогласен. Особенно про последующую обработку и простоту формул. Но как угодно, дело Ваше

По поводу множества вариантов - у Вас есть блок типа
Цитата китин, 07.06.2019 в 08:59, в сообщении № 2 ()
($B$6:$B$41<>"")
или
Цитата bmv98rus, 07.06.2019 в 09:55, в сообщении № 6 ()
B6:B41;"<>"

Разможьте его (блок) столько раз, сколько нужно. Только столбцы поменяйте - В на С,D,E,...
Автор - _Boroda_
Дата добавления - 07.06.2019 в 11:17
vitos88 Дата: Пятница, 07.06.2019, 11:27 | Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация: 5 ±
Замечаний: 0% ±

_Boroda_, В том-то и дело, что я сразу же так попробовал сделать. Не получается (прилагаю файл)
К сообщению приложен файл: 1572307.xlsx (10.3 Kb)
 
Ответить
Сообщение_Boroda_, В том-то и дело, что я сразу же так попробовал сделать. Не получается (прилагаю файл)
Автор - vitos88
Дата добавления - 07.06.2019 в 11:27
_Boroda_ Дата: Пятница, 07.06.2019, 11:41 | Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Не, у Михаила формула для текста. Для чисел ее нужно немного иначе написать.
Хотя нет, эты формула работает для заполнения ВСЕХ ячеек, а не нескольких. Кстати, это будет ответом на вопрос
Цитата bmv98rus, 07.06.2019 в 09:55, в сообщении № 6 ()
Да чтож вы так SUMIFS не любите


А вот формулу Игоря менять не нужно. Почти
Код
=ЕСЛИ(СУММПРОИЗВ((B6:B41<>"")+(C6:C41<>"")+(D6:D41<>""))>1;"Ошибка";"Нет ошибок")

Если заполняете только единичками, то так можно
Код
=СУММПРОИЗВ(--(D6:D41+E6:E41+F6:F41>1))
И формат конечно (см мой первый пост)
К сообщению приложен файл: 1572307_1.xlsx (10.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНе, у Михаила формула для текста. Для чисел ее нужно немного иначе написать.
Хотя нет, эты формула работает для заполнения ВСЕХ ячеек, а не нескольких. Кстати, это будет ответом на вопрос
Цитата bmv98rus, 07.06.2019 в 09:55, в сообщении № 6 ()
Да чтож вы так SUMIFS не любите


А вот формулу Игоря менять не нужно. Почти
Код
=ЕСЛИ(СУММПРОИЗВ((B6:B41<>"")+(C6:C41<>"")+(D6:D41<>""))>1;"Ошибка";"Нет ошибок")

Если заполняете только единичками, то так можно
Код
=СУММПРОИЗВ(--(D6:D41+E6:E41+F6:F41>1))
И формат конечно (см мой первый пост)
Автор - _Boroda_
Дата добавления - 07.06.2019 в 11:41
bmv98rus Дата: Пятница, 07.06.2019, 11:49 | Сообщение № 17
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
Цитата _Boroda_, 07.06.2019 в 11:17, в сообщении № 14 ()
Разможьте его (блок) столько раз, сколько нужно.

Александр, нет, не пойдет, в одном случае AND в другом случае AND(OR .
что-то типа
Код
=IF(SUMPRODUCT(--(MMULT(--(A2:E7<>"");{1;1;1;1;1})>1));"Ошибка";"")


.
Цитата vitos88, 07.06.2019 в 11:07, в сообщении № 13 ()
там формулы проще получаются.
ну если смотреть на ту что у вас не получилась, то это не так.

Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Пятница, 07.06.2019, 11:53
 
Ответить
Сообщение
Цитата _Boroda_, 07.06.2019 в 11:17, в сообщении № 14 ()
Разможьте его (блок) столько раз, сколько нужно.

Александр, нет, не пойдет, в одном случае AND в другом случае AND(OR .
что-то типа
Код
=IF(SUMPRODUCT(--(MMULT(--(A2:E7<>"");{1;1;1;1;1})>1));"Ошибка";"")


.
Цитата vitos88, 07.06.2019 в 11:07, в сообщении № 13 ()
там формулы проще получаются.
ну если смотреть на ту что у вас не получилась, то это не так.
Автор - bmv98rus
Дата добавления - 07.06.2019 в 11:49
vitos88 Дата: Пятница, 07.06.2019, 11:54 | Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация: 5 ±
Замечаний: 0% ±

_Boroda_, Ни та, ни другая формулы не работают (
 
Ответить
Сообщение_Boroda_, Ни та, ни другая формулы не работают (
Автор - vitos88
Дата добавления - 07.06.2019 в 11:54
bmv98rus Дата: Пятница, 07.06.2019, 11:59 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4106
Репутация: 768 ±
Замечаний: 0% ±

Excel 2013/2016
А так?
Цитата _Boroda_, 07.06.2019 в 11:41, в сообщении № 16 ()
Не, у Михаила формула для текста.
А вот это не понял!
К сообщению приложен файл: Copy_of_992.xlsx (16.8 Kb)

Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеА так?
Цитата _Boroda_, 07.06.2019 в 11:41, в сообщении № 16 ()
Не, у Михаила формула для текста.
А вот это не понял!
Автор - bmv98rus
Дата добавления - 07.06.2019 в 11:59
_Boroda_ Дата: Пятница, 07.06.2019, 12:09 | Сообщение № 20
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16674
Репутация: 6479 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Да я и сам не понял :D

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеДа я и сам не понял :D
Автор - _Boroda_
Дата добавления - 07.06.2019 в 12:09
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск и обозначение взаимоисключающих данных в таблице (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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