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

Вход

Регистрация

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

 

= Мир MS Excel/подсчет числовых совпадений по двум колонкам одновременно - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
подсчет числовых совпадений по двум колонкам одновременно
Nekr0mant Дата: Воскресенье, 24.09.2023, 13:08 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Доброго дня!
Помогите пожалуйста создать формулу для сверки соответствия по двум столбцам одновременно:

Есть три колонки с цифрами в таблице Microsoft Excel 2007: №1, №2 и №3 (B C и D соответственно).

В 1 и 2 разный набор цифр, третья пока пустая (она для решения по формуле).

формула должна проверить на соответствие все цифры из второй колонки (к примеру цифра 2) сравнивая сколько уникальных цифр ставит в левой (первой) колонке B (к примеру у двоек их колонки C есть всего два уникальных соответствия в колонке B = 1 и 5, две уникальные цифры). При этом так как цифра в колонке C 2 повторяется ТРИ раза, формула должна это учитывать проверяя все цифры в двух колонках одновременно чтобы понять количество повторений для всех этих двоек вместе и дать им общий ответ.

Если у анализируемого числа из столбца C было ТОЛЬКО одно уникальное число в столбце B , то в столбце D давался ответ = 0, во всем остальных случаях ответ = 1 (либо, что еще лучше, число уникальных числовых вариантов, 2,3...10...20...)

Должно выглядеть так:

B C D
1 2 2
2 3 0
3 1 0
5 2 2
1 2 2

пробовал такую формулу:
Код
=СЧЁТЕСЛИМН($B$2:$B$6; B2; $C$2:$C$6; C2)
(по идее должна сравнивать всю колонку с текущим числом и если сравнилась делать счетчик)

но она не работает, помогите понять что я не так делаю?


Сообщение отредактировал Nekr0mant - Воскресенье, 24.09.2023, 14:30
 
Ответить
СообщениеДоброго дня!
Помогите пожалуйста создать формулу для сверки соответствия по двум столбцам одновременно:

Есть три колонки с цифрами в таблице Microsoft Excel 2007: №1, №2 и №3 (B C и D соответственно).

В 1 и 2 разный набор цифр, третья пока пустая (она для решения по формуле).

формула должна проверить на соответствие все цифры из второй колонки (к примеру цифра 2) сравнивая сколько уникальных цифр ставит в левой (первой) колонке B (к примеру у двоек их колонки C есть всего два уникальных соответствия в колонке B = 1 и 5, две уникальные цифры). При этом так как цифра в колонке C 2 повторяется ТРИ раза, формула должна это учитывать проверяя все цифры в двух колонках одновременно чтобы понять количество повторений для всех этих двоек вместе и дать им общий ответ.

Если у анализируемого числа из столбца C было ТОЛЬКО одно уникальное число в столбце B , то в столбце D давался ответ = 0, во всем остальных случаях ответ = 1 (либо, что еще лучше, число уникальных числовых вариантов, 2,3...10...20...)

Должно выглядеть так:

B C D
1 2 2
2 3 0
3 1 0
5 2 2
1 2 2

пробовал такую формулу:
Код
=СЧЁТЕСЛИМН($B$2:$B$6; B2; $C$2:$C$6; C2)
(по идее должна сравнивать всю колонку с текущим числом и если сравнилась делать счетчик)

но она не работает, помогите понять что я не так делаю?

Автор - Nekr0mant
Дата добавления - 24.09.2023 в 13:08
Pelena Дата: Воскресенье, 24.09.2023, 15:18 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 19211
Репутация: 4427 ±
Замечаний: ±

Excel 365 & Mac Excel
число уникальных числовых вариантов

формула массива
Код
=СУММ(ЕСЛИОШИБКА(1/ЕСЛИ($C$2:$C$6=C2;СЧЁТЕСЛИМН($B$2:$B$6;$B$2:$B$6;$C$2:$C$6;C2);0);0))


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
число уникальных числовых вариантов

формула массива
Код
=СУММ(ЕСЛИОШИБКА(1/ЕСЛИ($C$2:$C$6=C2;СЧЁТЕСЛИМН($B$2:$B$6;$B$2:$B$6;$C$2:$C$6;C2);0);0))

Автор - Pelena
Дата добавления - 24.09.2023 в 15:18
Nekr0mant Дата: Воскресенье, 24.09.2023, 17:44 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Pelena, спасибо за вариант решения.
"=СУММ(ЕСЛИОШИБКА(1/ЕСЛИ($C$2:$C$6=C2;СЧЁТЕСЛИМН($B$2:$B$6;$B$2:$B$6;$C$2:$C$6;C2);0);0))"

Попробовал Ваш вариант, с фигурными скобками массива действительно классно работает!!

1 2 2
2 3 1
3 1 1
5 2 2
1 2 2
 
Ответить
СообщениеPelena, спасибо за вариант решения.
"=СУММ(ЕСЛИОШИБКА(1/ЕСЛИ($C$2:$C$6=C2;СЧЁТЕСЛИМН($B$2:$B$6;$B$2:$B$6;$C$2:$C$6;C2);0);0))"

Попробовал Ваш вариант, с фигурными скобками массива действительно классно работает!!

1 2 2
2 3 1
3 1 1
5 2 2
1 2 2

Автор - Nekr0mant
Дата добавления - 24.09.2023 в 17:44
Nekr0mant Дата: Понедельник, 25.09.2023, 09:59 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
PS на 46 тыс строк комп как то запредельно напрягся.
А есть ли какие то варианты оптимизации расчёта или характера вычисления?
 
Ответить
СообщениеPS на 46 тыс строк комп как то запредельно напрягся.
А есть ли какие то варианты оптимизации расчёта или характера вычисления?

Автор - Nekr0mant
Дата добавления - 25.09.2023 в 09:59
Nic70y Дата: Понедельник, 25.09.2023, 10:49 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8801
Репутация: 2295 ±
Замечаний: 0% ±

Excel 2010
см. вариант с доп.столбцом
К сообщению приложен файл: kniga1.xlsx (9.0 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщениесм. вариант с доп.столбцом

Автор - Nic70y
Дата добавления - 25.09.2023 в 10:49
Nekr0mant Дата: Понедельник, 25.09.2023, 14:04 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Nic70y, спасибо за ответ. посмотрел, Вы соединили два числовых значения через разделитель | что в 2 раза снизило объем обрабатываемых массивов, это очень клёвая идея. + тут нет {} для массивов и оно при этом работает.

единственное, я не понял почему в столбце С6 пропало значение, хотя формула работает исправно и может успешно опознать эту ячейку.
 
Ответить
СообщениеNic70y, спасибо за ответ. посмотрел, Вы соединили два числовых значения через разделитель | что в 2 раза снизило объем обрабатываемых массивов, это очень клёвая идея. + тут нет {} для массивов и оно при этом работает.

единственное, я не понял почему в столбце С6 пропало значение, хотя формула работает исправно и может успешно опознать эту ячейку.

Автор - Nekr0mant
Дата добавления - 25.09.2023 в 14:04
Nic70y Дата: Понедельник, 25.09.2023, 14:26 | Сообщение № 7
Группа: Друзья
Ранг: Экселист
Сообщений: 8801
Репутация: 2295 ±
Замечаний: 0% ±

Excel 2010
Nekr0mant, значение не пропало
Код
=ЕСЛИ(ЕНД(ПОИСКПОЗ(A6&"|"&B6;C$1:C5;));A6&"|"&B6;"")

ищем A6&"|"&B6 в диапазоне выше ячейки, если не нашли ЕНД, тогда A6&"|"&B6, а если нашли (т.е. это повтор), то "" (условно пустота)


ЮMoney 41001841029809
 
Ответить
СообщениеNekr0mant, значение не пропало
Код
=ЕСЛИ(ЕНД(ПОИСКПОЗ(A6&"|"&B6;C$1:C5;));A6&"|"&B6;"")

ищем A6&"|"&B6 в диапазоне выше ячейки, если не нашли ЕНД, тогда A6&"|"&B6, а если нашли (т.е. это повтор), то "" (условно пустота)

Автор - Nic70y
Дата добавления - 25.09.2023 в 14:26
Pelena Дата: Понедельник, 25.09.2023, 18:46 | Сообщение № 8
Группа: Админы
Ранг: Местный житель
Сообщений: 19211
Репутация: 4427 ±
Замечаний: ±

Excel 365 & Mac Excel
Для новых версий Excel совсем простая формула)
Код
=СЧЁТЗ(УНИК(ФИЛЬТР($A$2:$A$6;$B$2:$B$6=B2)))


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеДля новых версий Excel совсем простая формула)
Код
=СЧЁТЗ(УНИК(ФИЛЬТР($A$2:$A$6;$B$2:$B$6=B2)))

Автор - Pelena
Дата добавления - 25.09.2023 в 18:46
Nekr0mant Дата: Вторник, 26.09.2023, 21:41 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2010
Pelena, спасибо! все очень интересно)

"Для новых версий Excel совсем простая формула)"

О каких версиях идет речь, 2019?
И оно работает без {}?
 
Ответить
СообщениеPelena, спасибо! все очень интересно)

"Для новых версий Excel совсем простая формула)"

О каких версиях идет речь, 2019?
И оно работает без {}?

Автор - Nekr0mant
Дата добавления - 26.09.2023 в 21:41
Pelena Дата: Вторник, 26.09.2023, 22:19 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 19211
Репутация: 4427 ±
Замечаний: ±

Excel 365 & Mac Excel
2019 не уверена. Есть в 2021 и по подписке Office 365


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение2019 не уверена. Есть в 2021 и по подписке Office 365

Автор - Pelena
Дата добавления - 26.09.2023 в 22:19
  • Страница 1 из 1
  • 1
Поиск:

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