Страницы: 1
RSS
Перебор всех возможных комбинаций значений, Перебрать все значения двух столбцов, используя только формулу (не макросы)
 
Добрый вечер, уважаемые гуру Excel. Учусь работать в Экселе, изучая разные задачки. Столкнулась вот с одной запаркой, может кто поможет:

Итак, есть две колонки. Например
А                  B
Вася             Иванов
Гена             Петров
Петя              Сидоров

Вопрос. Задать формулами в колонки C и D алгоритм, чтобы перебирались все возможные сочетания (при этом не повторяясь)
Т.е. должны получиться 9 комбинаций, Вася Иванов, Вася Петров.... Петя Сидоров. При этом подразумевается, чтобы формула работала с большой исходной таблицей (например, сорок фамилий и семь имён, всего будет 280 комбинаций, или меньше если какие-то из значений исходника повторяются), при этом чтобы если в исходнике несколько раз встречается одно и то же имя\фамилия, то в результате повторов не было, т.е. не было двух Васей Ивановых. Надеюсь, понятно объяснила. Макросом или сводной таблицей всё решается, меня интересует можно ли достичь решения формулами, и если можно, то как это будет выглядеть. Спасибо за внимание.  
Изменено: Кэтти-бри - 28.02.2017 16:59:42
 
Догадываюсь, что надо использовать формулу массива, но что-то не получается - видимо навыка не хватает, и где-то ошибаюсь.
 
Вот перечитывала, поняла, что неверно сформулировала задачу. Значения должны переноситься не в одну колонку, а в две, т.е. перебор девяти значений как в примере должен происходить в колонках C и D соответственно. т.е. - первые три в C - Вася-Вася-Вася, а в D соответствующие им разные фамилии.
 
Код
=ИНДЕКС(C1;ЦЕЛОЕ((СТРОКА()-1)/СЧЁТЗ(C2))+1)&" "&ИНДЕКС(C2;ОСТАТ(СТРОКА()-1;3)+1)
обошлось обычным ИНДЕКС, без формул массива
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
спасибо, всё работает! Вопрос: можно ли быстро переделать так, чтобы результат всё-таки попадал в два разных столбца, потому что при попадании в один возникнут проблемы с дальнейшей работой с данными. Плюс при растягивании формула ведёт себя не очень корректно, продолжаясь с бесконечно повторяющимися фамилиями.
 
в формуле явно видно один ИНДЕКС для имени а второй - для фамилии
а что, самостоятельно разбить формулу на 2 части - никак? возможно Excel - это не Ваше
Цитата
Плюс при растягивании формула ведёт себя не очень корректно
формула ведет себя так, как в ней написано
Вы же прекрасно посчитали, что в случае 3 имени, 3 фамилии будет 9 вариантов сочетаний, а семь имен и 40 фамилий = 280...
не растягивайте формулу за пределы количества сочетаний - и все будет нормально))
или поправьте немного, чтобы "за пределами" формула возвращала пустую строку
удачи!
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
Ігор Гончаренко написал:
в формуле явно видно один ИНДЕКС для имени а второй - для фамилии
а что, самостоятельно разбить формулу на 2 части - никак? возможно Excel - это не Ваше
Уже сделала всё, спасибо большое за помощь. Я просто сказала что должно быть в идеале, вдруг вы любите всё делать идеально. Без Вашей подсказки не справилась бы.
 
Кстати, проблема всё равно не решена, потому в решении, предложенном пользователем Ігор Гончаренко, при повторяющихся значениях в итоговую таблицу может несколько раз попасть одно и то же.
 
а если исключить ввод "кривых" значений в данные?
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
Цитата
Ігор Гончаренко написал:
а если исключить ввод "кривых" значений в данные?
Так вопрос не в "кривых" значениях. Допустим, в этих же колонках например не имя и фамилия, а фамилия и наименование товара. Ну, что продал. Ну скажем:
А_____________________________В
Пупкин_______________________Стиральная машина
Губкин________________________Магнитофон
Пупкин________________________Магнитофон
Губкин_________________________Магнитофон
Канарейкина_____________________вездеход
Тогда в итоге должно получиться
C___________________________________D
Пупкин_______________________________Стиральная машина (в третьей колонке например количество, но как его посчитать даже мне понятно, оно в рамках данной задачи не интересно)
Пупкин________________________________Магнитофон
Пупкин___________________________________вездеход
Губкин____________________________________Стиральная машина.... (и т.д.)
При этом второй раз строки Пупкин - Стиральная машина нам не надо, потому что нам одной строчки достаточно чтобы узнать, сколько он их продал.
 
Здравствуйте! А файл с примером исходных данных никак нельзя показать? Что-то мне подсказывает, что тут дело в изначальной организации данных, а не в необходимости создания формул, которыми сначала нужно наплодить множество комбинаций, а потом из них убирать совпадения. Сделал вариант на основе файла от Ігор Гончаренко, проверяйте.  
Изменено: _Igor_61 - 28.02.2017 22:57:01
 
Добрый день,_Igor_61 ! Извините, что долго не отвечала, сейчас прикреплю вариант исходника, основанный на вашем же примере. Ваш вариант куда ближе к правде, но он некорректно работает, когда количество заполненных строк и столбцов неодинаково. Я чуть поменяла исходные данные, вот что получилось.
А вообще исходник тоже прикреплю. Вдруг вам так понятнее будет.
В исходнике - первые два листа пишутся от руки, лист Звонки приходит и на его основании автоматически должен формироваться лист Отчёт. Вся загвоздка в правой таблице, а именно в столбцах G и H, не понимаю как сделать чтобы они формировались автоматически путём перебора всех вариантов
направлений из первого листа и сотрудников из второго, исходя из того что и к направлениям, и к фамилиям могут добавляться новые, и тогда количество строк будет изменяться. В присланном примере на листе Отчёт столбцы G и H набиты вручную.
 
Файлы удалил: превышен максимально допустимый размер.
 
Уфф... Чем дальше в лес... Кэтти-бри, а Вы не могли бы сформулировать итоговую задачу - что в итоге должно получиться? Нужно посчитать кол-во звонков по каждому сотруднику по каждому направлению, или общее время звонков по каждому сотруднику или что-то еще? Понимаю, что Вам наверное интересно сделать перебор формулой, но тот ли это способ для решения задачи?
 
 
_Igor_61, если вы успели скачать удалённый администрацией файл, то должно получиться следующее:
Трёхколоночная таблица списка всех сотрудников, где для каждого из них по каждому направлению (направлению, а не коду оператора, это важно), посчитано суммарное время вызова, при этом она должна растягиваться при добавлении ещё одного сотрудника либо направления. При этом задача элементарно решается при помощи сводной таблицы, или не так элементарно - при помощи макроса. Но задача стоит именно решить только с помощью базовых формул. При этом можно создать энное кол-во вспомогательных листов, если они нужны, или промежуточных операций.
 
_Igor_61
Ну т.е. исходя из вашей формулировки - по общее время звонка по каждому сотруднику по каждому направлению.
 
Проверяйте
 
Фантастика. Всё правильно, всё работает. Огромное спасибо. Пошла разбираться как Вы этого добились.
 
:)
 
Доброе время суток.
Не прошло и суток, чтобы понять что чукча Кэтти хочет :)
Вариант на Power Pivot.
Изменено: Андрей VG - 01.03.2017 15:09:38 (Поменял формат, а то суммарная длительность за сутки перевалила :) )
Страницы: 1
Наверх