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

Вход

Регистрация

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

 

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

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Число совпадающих значений в разных строках массива (Формулы/Formulas)
Число совпадающих значений в разных строках массива
SoulKeeper Дата: Вторник, 18.02.2020, 10:31 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Здравствуйте!
Есть массив, допустим 100*100 элементов
В каждой строке есть определённое количество повторяющихся элементов.
Необходимо найти и вывести элементы которые повторяются во всех строках массива данных.
Как это делать чисто формулами без применения VBA?
Элементы могут повторятся. Количество элементов в строках массива может варьироваться.
Пример во вложении.
Версия Excel 2019, но можно предложить варианты решения и для более ранних версий.

Заранее благодарю за помощь.
К сообщению приложен файл: _1.xls (27.0 Kb)
 
Ответить
СообщениеЗдравствуйте!
Есть массив, допустим 100*100 элементов
В каждой строке есть определённое количество повторяющихся элементов.
Необходимо найти и вывести элементы которые повторяются во всех строках массива данных.
Как это делать чисто формулами без применения VBA?
Элементы могут повторятся. Количество элементов в строках массива может варьироваться.
Пример во вложении.
Версия Excel 2019, но можно предложить варианты решения и для более ранних версий.

Заранее благодарю за помощь.

Автор - SoulKeeper
Дата добавления - 18.02.2020 в 10:31
Fidgy Дата: Вторник, 18.02.2020, 14:35 | Сообщение № 2
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 11 ±
Замечаний: 0% ±

Excel 2016
Задача не понятна. Из вашего же примера, что должно получиться?
 
Ответить
СообщениеЗадача не понятна. Из вашего же примера, что должно получиться?

Автор - Fidgy
Дата добавления - 18.02.2020 в 14:35
SoulKeeper Дата: Вторник, 18.02.2020, 15:41 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Задача не понятна. Из вашего же примера, что должно получиться?

В данном примере только позиции "Яблоко" и "Груша" повторяется во всех строках.
В этом и состоит задача, чтобы находить такие повторяющиеся именно во всех строках позиции.
 
Ответить
Сообщение
Задача не понятна. Из вашего же примера, что должно получиться?

В данном примере только позиции "Яблоко" и "Груша" повторяется во всех строках.
В этом и состоит задача, чтобы находить такие повторяющиеся именно во всех строках позиции.

Автор - SoulKeeper
Дата добавления - 18.02.2020 в 15:41
Fidgy Дата: Вторник, 18.02.2020, 16:01 | Сообщение № 4
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 11 ±
Замечаний: 0% ±

Excel 2016
В данном примере только позиции "Яблоко" и "Груша" повторяется во всех строках.В этом и состоит задача, чтобы находить такие повторяющиеся именно во всех строках позиции.

Груши нет в третей строке

Есть список всех элементов? Или нужно ещё и составить список всех элементов и затем выявить какой из элементов есть в каждой строке?
Может ли элемент повторяться несколько раз в одной строке?
 
Ответить
Сообщение
В данном примере только позиции "Яблоко" и "Груша" повторяется во всех строках.В этом и состоит задача, чтобы находить такие повторяющиеся именно во всех строках позиции.

Груши нет в третей строке

Есть список всех элементов? Или нужно ещё и составить список всех элементов и затем выявить какой из элементов есть в каждой строке?
Может ли элемент повторяться несколько раз в одной строке?

Автор - Fidgy
Дата добавления - 18.02.2020 в 16:01
SoulKeeper Дата: Вторник, 18.02.2020, 16:20 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Груши нет в третей строке

Прошу прощения, "Яблоко" и "Слива".
Вот в этом то и проблема что трудно найти на глаз.
Есть список всех элементов? Или нужно ещё и составить список всех элементов и затем выявить какой из элементов есть в каждой строке?

Списка всех элементов нет. Я заранее не могу сказать какие элементы будут в заказе.

Может ли элемент повторяться несколько раз в одной строке?

Теоретически один элемент может встречаться только один раз в строке, но есть человеческий фактор, который может ошибочно его добавить. Поэтому лучше ещё и проверку на ошибку сделать. Но к сути вопроса это относится опосредованно, т.к. даже если и есть повторяющиеся элементы в одной строке, то на основную задачу это не влияет. Мне надо знать какие именно элементы повторяются во всех строках.
Результатом расчёта должен быть список элементов, которые присутствуют во всех строках, но вполне может быть ситуация что совпадений нет.
 
Ответить
Сообщение
Груши нет в третей строке

Прошу прощения, "Яблоко" и "Слива".
Вот в этом то и проблема что трудно найти на глаз.
Есть список всех элементов? Или нужно ещё и составить список всех элементов и затем выявить какой из элементов есть в каждой строке?

Списка всех элементов нет. Я заранее не могу сказать какие элементы будут в заказе.

Может ли элемент повторяться несколько раз в одной строке?

Теоретически один элемент может встречаться только один раз в строке, но есть человеческий фактор, который может ошибочно его добавить. Поэтому лучше ещё и проверку на ошибку сделать. Но к сути вопроса это относится опосредованно, т.к. даже если и есть повторяющиеся элементы в одной строке, то на основную задачу это не влияет. Мне надо знать какие именно элементы повторяются во всех строках.
Результатом расчёта должен быть список элементов, которые присутствуют во всех строках, но вполне может быть ситуация что совпадений нет.

Автор - SoulKeeper
Дата добавления - 18.02.2020 в 16:20
Fidgy Дата: Вторник, 18.02.2020, 17:41 | Сообщение № 6
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 11 ±
Замечаний: 0% ±

Excel 2016
Теоретически один элемент может встречаться только один раз в строке, но есть человеческий фактор, который может ошибочно его добавить. Поэтому лучше ещё и проверку на ошибку сделать. Но к сути вопроса это относится опосредованно, т.к. даже если и есть повторяющиеся элементы в одной строке, то на основную задачу это не влияет. Мне надо знать какие именно элементы повторяются во всех строках.

Если предположим, что в значения в строках не повторяются, и следуя условию "в каждой строке", то можно проверить только первую строку
Решение в приложении на втором листе
К сообщению приложен файл: 5340967.xls (30.0 Kb)


Сообщение отредактировал Fidgy - Вторник, 18.02.2020, 17:42
 
Ответить
Сообщение
Теоретически один элемент может встречаться только один раз в строке, но есть человеческий фактор, который может ошибочно его добавить. Поэтому лучше ещё и проверку на ошибку сделать. Но к сути вопроса это относится опосредованно, т.к. даже если и есть повторяющиеся элементы в одной строке, то на основную задачу это не влияет. Мне надо знать какие именно элементы повторяются во всех строках.

Если предположим, что в значения в строках не повторяются, и следуя условию "в каждой строке", то можно проверить только первую строку
Решение в приложении на втором листе

Автор - Fidgy
Дата добавления - 18.02.2020 в 17:41
SoulKeeper Дата: Вторник, 18.02.2020, 18:04 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Fidgy, не могу понять как можно использовать ваше решение для моей задачи. Мне в идеале получить вертикальный столбец где будут перечислены все нужные мне значения.
 
Ответить
СообщениеFidgy, не могу понять как можно использовать ваше решение для моей задачи. Мне в идеале получить вертикальный столбец где будут перечислены все нужные мне значения.

Автор - SoulKeeper
Дата добавления - 18.02.2020 в 18:04
Fidgy Дата: Вторник, 18.02.2020, 18:21 | Сообщение № 8
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 11 ±
Замечаний: 0% ±

Excel 2016
SoulKeeper,
1) если нужны значения, которые есть в каждой строке, значит можно проверить только первую строку
2) Если в каждой строке, только уникальные значения, значит можно посчитать сколько раз значение повторяется во всём диапазоне. Если это количество будет равно количеству строк, то значение есть в каждой строке
3) На втором листе формула ссылается, на ту-же ячейку где и формула, только на первом листе и отображает значение ячейки, если это значение есть в каждой строке (технически можно сделать и на одном листе)
Так мы получили диапазон со значениями. Правда не вертикальный, а горизоонтальный, но не думаю что это уже проблема. Решить её, а так-же убрать пустые ячейки, можно с помощью сводной таблицы
 
Ответить
СообщениеSoulKeeper,
1) если нужны значения, которые есть в каждой строке, значит можно проверить только первую строку
2) Если в каждой строке, только уникальные значения, значит можно посчитать сколько раз значение повторяется во всём диапазоне. Если это количество будет равно количеству строк, то значение есть в каждой строке
3) На втором листе формула ссылается, на ту-же ячейку где и формула, только на первом листе и отображает значение ячейки, если это значение есть в каждой строке (технически можно сделать и на одном листе)
Так мы получили диапазон со значениями. Правда не вертикальный, а горизоонтальный, но не думаю что это уже проблема. Решить её, а так-же убрать пустые ячейки, можно с помощью сводной таблицы

Автор - Fidgy
Дата добавления - 18.02.2020 в 18:21
SoulKeeper Дата: Среда, 19.02.2020, 09:45 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Fidgy, да, я теперь понял логику. Очень изящное решение. Огромное спасибо. Я попробую применить его в работе.
 
Ответить
СообщениеFidgy, да, я теперь понял логику. Очень изящное решение. Огромное спасибо. Я попробую применить его в работе.

Автор - SoulKeeper
Дата добавления - 19.02.2020 в 09:45
AlexM Дата: Среда, 19.02.2020, 09:58 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Немного иначе.
Формула массива
Код
=ИНДЕКС(A$1:G$1;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$1:G$7;A$1:G$1)=ЧСТРОК(A$1:G$7);СТОЛБЕЦ(A$1:G$1));СТРОКА(A1)))
Ошибку можно убрать функцией ЕСЛИОШИБКА()
К сообщению приложен файл: _1_01.xls (28.0 Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеНемного иначе.
Формула массива
Код
=ИНДЕКС(A$1:G$1;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(A$1:G$7;A$1:G$1)=ЧСТРОК(A$1:G$7);СТОЛБЕЦ(A$1:G$1));СТРОКА(A1)))
Ошибку можно убрать функцией ЕСЛИОШИБКА()

Автор - AlexM
Дата добавления - 19.02.2020 в 09:58
SoulKeeper Дата: Среда, 19.02.2020, 13:31 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
AlexM, тоже отличное решение. Но я не могу понять как вставить результирующие ячейки в произвольное место листа. Ни в вашем случае, ни в случае предыдущего специалиста возникала жёсткая привязка места вывода результата и исходной таблицы.
 
Ответить
СообщениеAlexM, тоже отличное решение. Но я не могу понять как вставить результирующие ячейки в произвольное место листа. Ни в вашем случае, ни в случае предыдущего специалиста возникала жёсткая привязка места вывода результата и исходной таблицы.

Автор - SoulKeeper
Дата добавления - 19.02.2020 в 13:31
AlexM Дата: Среда, 19.02.2020, 13:48 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
вставить результирующие ячейки в произвольное место листа
Код
=ИНДЕКС($A$1:$G$1;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$1:$G$7;$A$1:$G$1)=ЧСТРОК($A$1:$G$7);СТОЛБЕЦ($A$1:$G$1));СТРОКА(A1)))



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
вставить результирующие ячейки в произвольное место листа
Код
=ИНДЕКС($A$1:$G$1;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$1:$G$7;$A$1:$G$1)=ЧСТРОК($A$1:$G$7);СТОЛБЕЦ($A$1:$G$1));СТРОКА(A1)))

Автор - AlexM
Дата добавления - 19.02.2020 в 13:48
SoulKeeper Дата: Среда, 19.02.2020, 17:22 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
AlexM, да, теперь работает отлично. Но как и писал ранее иногда попадается человеческий фактор, когда по строке попадается повтор одного из значений. В этом случае данная формула перестаёт работать. Если ли вариант модифицировать её до состояния, когда повторы одного наименования по строкам не будут портить результат?
Также программа не работает в том случае если заявленный диапазон имеет пустые строки, т.е. если я заявил рабочий диапазон, например 100*100, а по факту забил только 50 строк, то тоже не работает. Необходимо чётко задать диапазон на фактическое количество строчек. Как сделать так, чтобы можно было работать с заранее указанным диапазоном, даже если не все ячейки заполнены.


Сообщение отредактировал SoulKeeper - Среда, 19.02.2020, 18:37
 
Ответить
СообщениеAlexM, да, теперь работает отлично. Но как и писал ранее иногда попадается человеческий фактор, когда по строке попадается повтор одного из значений. В этом случае данная формула перестаёт работать. Если ли вариант модифицировать её до состояния, когда повторы одного наименования по строкам не будут портить результат?
Также программа не работает в том случае если заявленный диапазон имеет пустые строки, т.е. если я заявил рабочий диапазон, например 100*100, а по факту забил только 50 строк, то тоже не работает. Необходимо чётко задать диапазон на фактическое количество строчек. Как сделать так, чтобы можно было работать с заранее указанным диапазоном, даже если не все ячейки заполнены.

Автор - SoulKeeper
Дата добавления - 19.02.2020 в 17:22
AlexM Дата: Среда, 19.02.2020, 20:28 | Сообщение № 14
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
по строке попадается повтор одного из значений
Покажите такой пример и покажите желаемый результат.



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
по строке попадается повтор одного из значений
Покажите такой пример и покажите желаемый результат.

Автор - AlexM
Дата добавления - 19.02.2020 в 20:28
AlexM Дата: Среда, 19.02.2020, 21:01 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Для 99*10
Код
=ИНДЕКС($A$1:$J$1;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$1:$J$99;$A$1:$J$1)=СЧЁТЗ($A$1:$A$99);СТОЛБЕЦ($A$1:$J$1));СТРОКА(A1)))
Если с повторами, решения у меня нет.
К сообщению приложен файл: _1_02.xls (31.0 Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеДля 99*10
Код
=ИНДЕКС($A$1:$J$1;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$1:$J$99;$A$1:$J$1)=СЧЁТЗ($A$1:$A$99);СТОЛБЕЦ($A$1:$J$1));СТРОКА(A1)))
Если с повторами, решения у меня нет.

Автор - AlexM
Дата добавления - 19.02.2020 в 21:01
Pelena Дата: Среда, 19.02.2020, 22:36 | Сообщение № 16
Группа: Админы
Ранг: Местный житель
Сообщений: 19167
Репутация: 4412 ±
Замечаний: ±

Excel 365 & Mac Excel
попадается человеческий фактор

Сделайте Условное форматирование, чтобы повторы в строке были сразу видны
К сообщению приложен файл: 8079983.xls (31.0 Kb)


"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
Сообщение
попадается человеческий фактор

Сделайте Условное форматирование, чтобы повторы в строке были сразу видны

Автор - Pelena
Дата добавления - 19.02.2020 в 22:36
Fidgy Дата: Четверг, 20.02.2020, 16:27 | Сообщение № 17
Группа: Пользователи
Ранг: Участник
Сообщений: 50
Репутация: 11 ±
Замечаний: 0% ±

Excel 2016
Было интересно решить эту задачу. Получилась монструозная формула массива, поэтому за скорость работы не ручаюсь
Формула в столбце P, и столбец O вспомогательный
К сообщению приложен файл: 4728665.xls (31.5 Kb)
 
Ответить
СообщениеБыло интересно решить эту задачу. Получилась монструозная формула массива, поэтому за скорость работы не ручаюсь
Формула в столбце P, и столбец O вспомогательный

Автор - Fidgy
Дата добавления - 20.02.2020 в 16:27
SoulKeeper Дата: Среда, 26.02.2020, 10:41 | Сообщение № 18
Группа: Пользователи
Ранг: Новичок
Сообщений: 15
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Fidgy, AlexM, огромное спасибо! Оба решения работают. Если у меня будет возможность протестировать скорость работы на больших объёмах данных то я отпишусь какое работает быстрее.


Сообщение отредактировал SoulKeeper - Среда, 26.02.2020, 10:42
 
Ответить
СообщениеFidgy, AlexM, огромное спасибо! Оба решения работают. Если у меня будет возможность протестировать скорость работы на больших объёмах данных то я отпишусь какое работает быстрее.

Автор - SoulKeeper
Дата добавления - 26.02.2020 в 10:41
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Число совпадающих значений в разных строках массива (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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