Здравствуйте! Есть массив, допустим 100*100 элементов В каждой строке есть определённое количество повторяющихся элементов. Необходимо найти и вывести элементы которые повторяются во всех строках массива данных. Как это делать чисто формулами без применения VBA? Элементы могут повторятся. Количество элементов в строках массива может варьироваться. Пример во вложении. Версия Excel 2019, но можно предложить варианты решения и для более ранних версий.
Заранее благодарю за помощь.
Здравствуйте! Есть массив, допустим 100*100 элементов В каждой строке есть определённое количество повторяющихся элементов. Необходимо найти и вывести элементы которые повторяются во всех строках массива данных. Как это делать чисто формулами без применения VBA? Элементы могут повторятся. Количество элементов в строках массива может варьироваться. Пример во вложении. Версия Excel 2019, но можно предложить варианты решения и для более ранних версий.
Задача не понятна. Из вашего же примера, что должно получиться?
В данном примере только позиции "Яблоко" и "Груша" повторяется во всех строках. В этом и состоит задача, чтобы находить такие повторяющиеся именно во всех строках позиции.
Задача не понятна. Из вашего же примера, что должно получиться?
В данном примере только позиции "Яблоко" и "Груша" повторяется во всех строках. В этом и состоит задача, чтобы находить такие повторяющиеся именно во всех строках позиции.SoulKeeper
В данном примере только позиции "Яблоко" и "Груша" повторяется во всех строках.В этом и состоит задача, чтобы находить такие повторяющиеся именно во всех строках позиции.
Груши нет в третей строке
Есть список всех элементов? Или нужно ещё и составить список всех элементов и затем выявить какой из элементов есть в каждой строке? Может ли элемент повторяться несколько раз в одной строке?
В данном примере только позиции "Яблоко" и "Груша" повторяется во всех строках.В этом и состоит задача, чтобы находить такие повторяющиеся именно во всех строках позиции.
Груши нет в третей строке
Есть список всех элементов? Или нужно ещё и составить список всех элементов и затем выявить какой из элементов есть в каждой строке? Может ли элемент повторяться несколько раз в одной строке?Fidgy
Может ли элемент повторяться несколько раз в одной строке?
Теоретически один элемент может встречаться только один раз в строке, но есть человеческий фактор, который может ошибочно его добавить. Поэтому лучше ещё и проверку на ошибку сделать. Но к сути вопроса это относится опосредованно, т.к. даже если и есть повторяющиеся элементы в одной строке, то на основную задачу это не влияет. Мне надо знать какие именно элементы повторяются во всех строках. Результатом расчёта должен быть список элементов, которые присутствуют во всех строках, но вполне может быть ситуация что совпадений нет.
Может ли элемент повторяться несколько раз в одной строке?
Теоретически один элемент может встречаться только один раз в строке, но есть человеческий фактор, который может ошибочно его добавить. Поэтому лучше ещё и проверку на ошибку сделать. Но к сути вопроса это относится опосредованно, т.к. даже если и есть повторяющиеся элементы в одной строке, то на основную задачу это не влияет. Мне надо знать какие именно элементы повторяются во всех строках. Результатом расчёта должен быть список элементов, которые присутствуют во всех строках, но вполне может быть ситуация что совпадений нет.SoulKeeper
Теоретически один элемент может встречаться только один раз в строке, но есть человеческий фактор, который может ошибочно его добавить. Поэтому лучше ещё и проверку на ошибку сделать. Но к сути вопроса это относится опосредованно, т.к. даже если и есть повторяющиеся элементы в одной строке, то на основную задачу это не влияет. Мне надо знать какие именно элементы повторяются во всех строках.
Если предположим, что в значения в строках не повторяются, и следуя условию "в каждой строке", то можно проверить только первую строку Решение в приложении на втором листе
Теоретически один элемент может встречаться только один раз в строке, но есть человеческий фактор, который может ошибочно его добавить. Поэтому лучше ещё и проверку на ошибку сделать. Но к сути вопроса это относится опосредованно, т.к. даже если и есть повторяющиеся элементы в одной строке, то на основную задачу это не влияет. Мне надо знать какие именно элементы повторяются во всех строках.
Если предположим, что в значения в строках не повторяются, и следуя условию "в каждой строке", то можно проверить только первую строку Решение в приложении на втором листеFidgy
Fidgy, не могу понять как можно использовать ваше решение для моей задачи. Мне в идеале получить вертикальный столбец где будут перечислены все нужные мне значения.
Fidgy, не могу понять как можно использовать ваше решение для моей задачи. Мне в идеале получить вертикальный столбец где будут перечислены все нужные мне значения.SoulKeeper
SoulKeeper, 1) если нужны значения, которые есть в каждой строке, значит можно проверить только первую строку 2) Если в каждой строке, только уникальные значения, значит можно посчитать сколько раз значение повторяется во всём диапазоне. Если это количество будет равно количеству строк, то значение есть в каждой строке 3) На втором листе формула ссылается, на ту-же ячейку где и формула, только на первом листе и отображает значение ячейки, если это значение есть в каждой строке (технически можно сделать и на одном листе) Так мы получили диапазон со значениями. Правда не вертикальный, а горизоонтальный, но не думаю что это уже проблема. Решить её, а так-же убрать пустые ячейки, можно с помощью сводной таблицы
SoulKeeper, 1) если нужны значения, которые есть в каждой строке, значит можно проверить только первую строку 2) Если в каждой строке, только уникальные значения, значит можно посчитать сколько раз значение повторяется во всём диапазоне. Если это количество будет равно количеству строк, то значение есть в каждой строке 3) На втором листе формула ссылается, на ту-же ячейку где и формула, только на первом листе и отображает значение ячейки, если это значение есть в каждой строке (технически можно сделать и на одном листе) Так мы получили диапазон со значениями. Правда не вертикальный, а горизоонтальный, но не думаю что это уже проблема. Решить её, а так-же убрать пустые ячейки, можно с помощью сводной таблицыFidgy
AlexM, тоже отличное решение. Но я не могу понять как вставить результирующие ячейки в произвольное место листа. Ни в вашем случае, ни в случае предыдущего специалиста возникала жёсткая привязка места вывода результата и исходной таблицы.
AlexM, тоже отличное решение. Но я не могу понять как вставить результирующие ячейки в произвольное место листа. Ни в вашем случае, ни в случае предыдущего специалиста возникала жёсткая привязка места вывода результата и исходной таблицы.SoulKeeper
AlexM, да, теперь работает отлично. Но как и писал ранее иногда попадается человеческий фактор, когда по строке попадается повтор одного из значений. В этом случае данная формула перестаёт работать. Если ли вариант модифицировать её до состояния, когда повторы одного наименования по строкам не будут портить результат? Также программа не работает в том случае если заявленный диапазон имеет пустые строки, т.е. если я заявил рабочий диапазон, например 100*100, а по факту забил только 50 строк, то тоже не работает. Необходимо чётко задать диапазон на фактическое количество строчек. Как сделать так, чтобы можно было работать с заранее указанным диапазоном, даже если не все ячейки заполнены.
AlexM, да, теперь работает отлично. Но как и писал ранее иногда попадается человеческий фактор, когда по строке попадается повтор одного из значений. В этом случае данная формула перестаёт работать. Если ли вариант модифицировать её до состояния, когда повторы одного наименования по строкам не будут портить результат? Также программа не работает в том случае если заявленный диапазон имеет пустые строки, т.е. если я заявил рабочий диапазон, например 100*100, а по факту забил только 50 строк, то тоже не работает. Необходимо чётко задать диапазон на фактическое количество строчек. Как сделать так, чтобы можно было работать с заранее указанным диапазоном, даже если не все ячейки заполнены.SoulKeeper
Сообщение отредактировал SoulKeeper - Среда, 19.02.2020, 18:37
Было интересно решить эту задачу. Получилась монструозная формула массива, поэтому за скорость работы не ручаюсь Формула в столбце P, и столбец O вспомогательный
Было интересно решить эту задачу. Получилась монструозная формула массива, поэтому за скорость работы не ручаюсь Формула в столбце P, и столбец O вспомогательныйFidgy
Fidgy, AlexM, огромное спасибо! Оба решения работают. Если у меня будет возможность протестировать скорость работы на больших объёмах данных то я отпишусь какое работает быстрее.
Fidgy, AlexM, огромное спасибо! Оба решения работают. Если у меня будет возможность протестировать скорость работы на больших объёмах данных то я отпишусь какое работает быстрее.SoulKeeper
Сообщение отредактировал SoulKeeper - Среда, 26.02.2020, 10:42