Поиск и обозначение взаимоисключающих данных в таблице
vitos88
Дата: Пятница, 07.06.2019, 08:43 |
Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация:
5
±
Замечаний:
0% ±
Добрый день! Сердечно поздравляю всех с долгожданной пятницей! Задача упростить формулу, которая ищет в результатах анкетирования ошибки ввода. Например, одновременное указывание мужского и женского пола. Мне на ум приходит формула типа:Код
=если(или(счётз(ячейки с полом по строке 1)>1;счётз(ячейки с полом по строке 2)>1;счётз(ячейки с полом по строке 3)>1;счётз(ячейки с полом по строке 4)>1; и т.д. до 100);"ошибка";"нет ошибки")
Всё дело в том, что таких строк может быть 100, а вписывать это в формулу очень муторно. Может, есть варианты упростить задачу?
Добрый день! Сердечно поздравляю всех с долгожданной пятницей! Задача упростить формулу, которая ищет в результатах анкетирования ошибки ввода. Например, одновременное указывание мужского и женского пола. Мне на ум приходит формула типа:Код
=если(или(счётз(ячейки с полом по строке 1)>1;счётз(ячейки с полом по строке 2)>1;счётз(ячейки с полом по строке 3)>1;счётз(ячейки с полом по строке 4)>1; и т.д. до 100);"ошибка";"нет ошибки")
Всё дело в том, что таких строк может быть 100, а вписывать это в формулу очень муторно. Может, есть варианты упростить задачу? vitos88
Ответить
Сообщение Добрый день! Сердечно поздравляю всех с долгожданной пятницей! Задача упростить формулу, которая ищет в результатах анкетирования ошибки ввода. Например, одновременное указывание мужского и женского пола. Мне на ум приходит формула типа:Код
=если(или(счётз(ячейки с полом по строке 1)>1;счётз(ячейки с полом по строке 2)>1;счётз(ячейки с полом по строке 3)>1;счётз(ячейки с полом по строке 4)>1; и т.д. до 100);"ошибка";"нет ошибки")
Всё дело в том, что таких строк может быть 100, а вписывать это в формулу очень муторно. Может, есть варианты упростить задачу? Автор - vitos88 Дата добавления - 07.06.2019 в 08:43
китин
Дата: Пятница, 07.06.2019, 08:59 |
Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 7015
Репутация:
1073
±
Замечаний:
0% ±
Excel 2007;2010;2016
Добрый и вам. так?Код
=ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")
и немного УФ
Добрый и вам. так?Код
=ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")
и немного УФ китин
Не судите очень строго:я пытаюсь научиться ЯД 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
Группа: Модераторы
Ранг: Экселист
Сообщений: 7015
Репутация:
1073
±
Замечаний:
0% ±
Excel 2007;2010;2016
добавил файл в пост выше с УФ
добавил файл в пост выше с УФ китин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852
Ответить
Сообщение добавил файл в пост выше с УФ Автор - китин Дата добавления - 07.06.2019 в 09:06
_Boroda_
Дата: Пятница, 07.06.2019, 09:18 |
Сообщение № 5
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация:
6481
±
Замечаний:
0% ±
2003; 2007; 2010; 2013 RUS
Еще вариант ФормулаКод
=СУММПРОИЗВ(--(B6:B41&C6:C41<>C6:C41&B6:B41))
Формат ячейки "Ошибка";"Нет ошибок" И Условное форматирование
Еще вариант ФормулаКод
=СУММПРОИЗВ(--(B6:B41&C6:C41<>C6:C41&B6:B41))
Формат ячейки "Ошибка";"Нет ошибок" И Условное форматирование _Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Еще вариант ФормулаКод
=СУММПРОИЗВ(--(B6:B41&C6:C41<>C6:C41&B6:B41))
Формат ячейки "Ошибка";"Нет ошибок" И Условное форматирование Автор - _Boroda_ Дата добавления - 07.06.2019 в 09:18
bmv98rus
Дата: Пятница, 07.06.2019, 09:55 |
Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4110
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
Да чтож вы так SUMIFS не любитеКод
=IF(COUNTIFS(B6:B41;"<>";C6:C41;"<>")>0;"Ошибка";"Нет ошибок")
Да чтож вы так SUMIFS не любитеКод
=IF(COUNTIFS(B6:B41;"<>";C6:C41;"<>")>0;"Ошибка";"Нет ошибок")
bmv98rus
Замечательный Временно просто медведь , процентов на 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% ±
=ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")
Будет ли это работать с тремя, пятью вариантами ответов, а не только с двумя?
=ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")
Будет ли это работать с тремя, пятью вариантами ответов, а не только с двумя?vitos88
Сообщение отредактировал vitos88 - Пятница, 07.06.2019, 10:13
Ответить
Сообщение =ЕСЛИ(СУММПРОИЗВ(($B$6:$B$41<>"")*($C$6:$C$41<>""))=1;"Ошибка";"Нет ошибок")
Будет ли это работать с тремя, пятью вариантами ответов, а не только с двумя?Автор - vitos88 Дата добавления - 07.06.2019 в 10:03
китин
Дата: Пятница, 07.06.2019, 10:12 |
Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 7015
Репутация:
1073
±
Замечаний:
0% ±
Excel 2007;2010;2016
vitos88 , не цитируйте весь пост . Это нарушение Правил форума. исправляйте. и поясните, что вы имеете в виду на примере
vitos88 , не цитируйте весь пост . Это нарушение Правил форума. исправляйте. и поясните, что вы имеете в виду на примерекитин
Не судите очень строго:я пытаюсь научиться ЯД 41001877306852
Ответить
Сообщение vitos88 , не цитируйте весь пост . Это нарушение Правил форума. исправляйте. и поясните, что вы имеете в виду на примереАвтор - китин Дата добавления - 07.06.2019 в 10:12
vitos88
Дата: Пятница, 07.06.2019, 10:16 |
Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация:
5
±
Замечаний:
0% ±
_Boroda_ , формула не работает, если заполнять таблицу одинаковыми символами.
_Boroda_ , формула не работает, если заполнять таблицу одинаковыми символами.vitos88
Ответить
Сообщение _Boroda_ , формула не работает, если заполнять таблицу одинаковыми символами.Автор - vitos88 Дата добавления - 07.06.2019 в 10:16
_Boroda_
Дата: Пятница, 07.06.2019, 10:23 |
Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация:
6481
±
Замечаний:
0% ±
2003; 2007; 2010; 2013 RUS
А Вы не заполняйте Кстати, зачем вообще 2 столбца в таблице? Что не сделаете один столбец "Пол" и в нем выбор из вып списка вариантов "М" и "Ж"? Аналогичный вопрос и про возраст Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать Как-то так примерно
А Вы не заполняйте Кстати, зачем вообще 2 столбца в таблице? Что не сделаете один столбец "Пол" и в нем выбор из вып списка вариантов "М" и "Ж"? Аналогичный вопрос и про возраст Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать Как-то так примерно _Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение А Вы не заполняйте Кстати, зачем вообще 2 столбца в таблице? Что не сделаете один столбец "Пол" и в нем выбор из вып списка вариантов "М" и "Ж"? Аналогичный вопрос и про возраст Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать Как-то так примерно Автор - _Boroda_ Дата добавления - 07.06.2019 в 10:23
bmv98rus
Дата: Пятница, 07.06.2019, 10:41 |
Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4110
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
Будет ли это работать с тремя, пятью вариантами ответов,
Мне вот интересно, ну ладно три пола я знаю, Мужской, Женский , Медвежий, но еще то два какие?
Будет ли это работать с тремя, пятью вариантами ответов,
Мне вот интересно, ну ладно три пола я знаю, Мужской, Женский , Медвежий, но еще то два какие?bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение Будет ли это работать с тремя, пятью вариантами ответов,
Мне вот интересно, ну ладно три пола я знаю, Мужской, Женский , Медвежий, но еще то два какие?Автор - bmv98rus Дата добавления - 07.06.2019 в 10:41
_Boroda_
Дата: Пятница, 07.06.2019, 10:43 |
Сообщение № 12
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация:
6481
±
Замечаний:
0% ±
2003; 2007; 2010; 2013 RUS
[offtop]Очень просто: Мужской Женский Медвежий Другое Пока не определился[/offtop]
[offtop]Очень просто: Мужской Женский Медвежий Другое Пока не определился[/offtop] _Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: 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_ , Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать
ошибки ввода будет сделать сложнее, но вот заполнение результатов будет идти медленнее, согласитесь, гораздо проще любой символ тыкнуть в ячейку, чем выбирать нужный из списка или вводить вручную. На счёт обработки данных, то мне проще обрабатывать именно такую таблицу, там формулы проще получаются. А по поводу трёх колонок, анкета не состоит из одного вопроса про пол, там есть вопросы и с пятью вариантами ответов. Я так понимаю, что упростить формулу с более чем двумя ответами становится намного сложнее, если, конечно, оно вообще возможно.vitos88
Ответить
Сообщение _Boroda_ , Гораздо удобнее будет последующая обработка данных. И ошибки ввода сложнее сделать
ошибки ввода будет сделать сложнее, но вот заполнение результатов будет идти медленнее, согласитесь, гораздо проще любой символ тыкнуть в ячейку, чем выбирать нужный из списка или вводить вручную. На счёт обработки данных, то мне проще обрабатывать именно такую таблицу, там формулы проще получаются. А по поводу трёх колонок, анкета не состоит из одного вопроса про пол, там есть вопросы и с пятью вариантами ответов. Я так понимаю, что упростить формулу с более чем двумя ответами становится намного сложнее, если, конечно, оно вообще возможно.Автор - vitos88 Дата добавления - 07.06.2019 в 11:07
_Boroda_
Дата: Пятница, 07.06.2019, 11:17 |
Сообщение № 14
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация:
6481
±
Замечаний:
0% ±
2003; 2007; 2010; 2013 RUS
Категорически несогласен. Особенно про последующую обработку и простоту формул. Но как угодно, дело Ваше По поводу множества вариантов - у Вас есть блок типа или Разможьте его (блок) столько раз, сколько нужно. Только столбцы поменяйте - В на С,D,E,...
Категорически несогласен. Особенно про последующую обработку и простоту формул. Но как угодно, дело Ваше По поводу множества вариантов - у Вас есть блок типа или Разможьте его (блок) столько раз, сколько нужно. Только столбцы поменяйте - В на С,D,E,... _Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Категорически несогласен. Особенно про последующую обработку и простоту формул. Но как угодно, дело Ваше По поводу множества вариантов - у Вас есть блок типа или Разможьте его (блок) столько раз, сколько нужно. Только столбцы поменяйте - В на С,D,E,... Автор - _Boroda_ Дата добавления - 07.06.2019 в 11:17
vitos88
Дата: Пятница, 07.06.2019, 11:27 |
Сообщение № 15
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация:
5
±
Замечаний:
0% ±
_Boroda_ , В том-то и дело, что я сразу же так попробовал сделать. Не получается (прилагаю файл)
_Boroda_ , В том-то и дело, что я сразу же так попробовал сделать. Не получается (прилагаю файл)vitos88
Ответить
Сообщение _Boroda_ , В том-то и дело, что я сразу же так попробовал сделать. Не получается (прилагаю файл)Автор - vitos88 Дата добавления - 07.06.2019 в 11:27
_Boroda_
Дата: Пятница, 07.06.2019, 11:41 |
Сообщение № 16
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация:
6481
±
Замечаний:
0% ±
2003; 2007; 2010; 2013 RUS
Не, у Михаила формула для текста. Для чисел ее нужно немного иначе написать. Хотя нет, эты формула работает для заполнения ВСЕХ ячеек, а не нескольких. Кстати, это будет ответом на вопрос Да чтож вы так SUMIFS не любите
А вот формулу Игоря менять не нужно. ПочтиКод
=ЕСЛИ(СУММПРОИЗВ((B6:B41<>"")+(C6:C41<>"")+(D6:D41<>""))>1;"Ошибка";"Нет ошибок")
Если заполняете только единичками, то так можноКод
=СУММПРОИЗВ(--(D6:D41+E6:E41+F6:F41>1))
И формат конечно (см мой первый пост)
Не, у Михаила формула для текста. Для чисел ее нужно немного иначе написать. Хотя нет, эты формула работает для заполнения ВСЕХ ячеек, а не нескольких. Кстати, это будет ответом на вопрос Да чтож вы так SUMIFS не любите
А вот формулу Игоря менять не нужно. ПочтиКод
=ЕСЛИ(СУММПРОИЗВ((B6:B41<>"")+(C6:C41<>"")+(D6:D41<>""))>1;"Ошибка";"Нет ошибок")
Если заполняете только единичками, то так можноКод
=СУММПРОИЗВ(--(D6:D41+E6:E41+F6:F41>1))
И формат конечно (см мой первый пост)_Boroda_
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Не, у Михаила формула для текста. Для чисел ее нужно немного иначе написать. Хотя нет, эты формула работает для заполнения ВСЕХ ячеек, а не нескольких. Кстати, это будет ответом на вопрос Да чтож вы так 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
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4110
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
Разможьте его (блок) столько раз, сколько нужно.
Александр, нет, не пойдет, в одном случае AND в другом случае AND(OR . что-то типаКод
=IF(SUMPRODUCT(--(MMULT(--(A2:E7<>"");{1;1;1;1;1})>1));"Ошибка";"")
.там формулы проще получаются.
ну если смотреть на ту что у вас не получилась, то это не так.
Разможьте его (блок) столько раз, сколько нужно.
Александр, нет, не пойдет, в одном случае AND в другом случае AND(OR . что-то типаКод
=IF(SUMPRODUCT(--(MMULT(--(A2:E7<>"");{1;1;1;1;1})>1));"Ошибка";"")
.там формулы проще получаются.
ну если смотреть на ту что у вас не получилась, то это не так.bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Сообщение отредактировал bmv98rus - Пятница, 07.06.2019, 11:53
Ответить
Сообщение Разможьте его (блок) столько раз, сколько нужно.
Александр, нет, не пойдет, в одном случае AND в другом случае AND(OR . что-то типаКод
=IF(SUMPRODUCT(--(MMULT(--(A2:E7<>"");{1;1;1;1;1})>1));"Ошибка";"")
.там формулы проще получаются.
ну если смотреть на ту что у вас не получилась, то это не так.Автор - bmv98rus Дата добавления - 07.06.2019 в 11:49
vitos88
Дата: Пятница, 07.06.2019, 11:54 |
Сообщение № 18
Группа: Пользователи
Ранг: Участник
Сообщений: 97
Репутация:
5
±
Замечаний:
0% ±
_Boroda_ , Ни та, ни другая формулы не работают (
_Boroda_ , Ни та, ни другая формулы не работают (vitos88
Ответить
Сообщение _Boroda_ , Ни та, ни другая формулы не работают (Автор - vitos88 Дата добавления - 07.06.2019 в 11:54
bmv98rus
Дата: Пятница, 07.06.2019, 11:59 |
Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4110
Репутация:
768
±
Замечаний:
0% ±
Excel 2013/2016
А так?Не, у Михаила формула для текста.
А вот это не понял!
А так?Не, у Михаила формула для текста.
А вот это не понял! bmv98rus
Замечательный Временно просто медведь , процентов на 20 .
Ответить
Сообщение А так?Не, у Михаила формула для текста.
А вот это не понял! Автор - bmv98rus Дата добавления - 07.06.2019 в 11:59
_Boroda_
Дата: Пятница, 07.06.2019, 12:09 |
Сообщение № 20
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация:
6481
±
Замечаний:
0% ±
2003; 2007; 2010; 2013 RUS
Да я и сам не понял
Скажи мне, кудесник, любимец ба’гов... Платная помощь: Boroda_Excel@mail.ru Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
Ответить
Сообщение Да я и сам не понял Автор - _Boroda_ Дата добавления - 07.06.2019 в 12:09