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

Вход

Регистрация

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

 

= Мир MS Excel/Извлечение и сцепка данных по дублям - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение и сцепка данных по дублям (Формулы/Formulas)
Извлечение и сцепка данных по дублям
sergeyssavateev Дата: Четверг, 18.01.2024, 11:02 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Привет тебе, о великий и знающий человек!
Помоги, пожалуйста, разобраться с такой проблемой, так как до меня никак не доходит, как это сделать =(
Пример во вложениях

Дано 2 столбца B и C.
В столбце B есть одинаковые номера
В столбце С тоже есть одинаковые номера, которые соотносятся с данными из B
Нужно сделать 2 новых столбца, где в первом будут удалены дубли из B, а во втором значения объедены через запятую.

пример: (фио столбец B, товары столбец C)
Иванов Иван - 10 яблок
Иванов Иван - 5 груш
Сидоров Сидр - 5 яблок
Петров Пётр - 7 яблок
Петров Пётр - 9 груш

А результат: (столбец D и товар столбец E)
Иванов Иван - 10 яблок, 5 груш
Сидоров Сидр - 5 яблок
Петров Пётр - 7 яблок, 9 груш
К сообщению приложен файл: 3334068.xlsx (9.5 Kb)
 
Ответить
СообщениеПривет тебе, о великий и знающий человек!
Помоги, пожалуйста, разобраться с такой проблемой, так как до меня никак не доходит, как это сделать =(
Пример во вложениях

Дано 2 столбца B и C.
В столбце B есть одинаковые номера
В столбце С тоже есть одинаковые номера, которые соотносятся с данными из B
Нужно сделать 2 новых столбца, где в первом будут удалены дубли из B, а во втором значения объедены через запятую.

пример: (фио столбец B, товары столбец C)
Иванов Иван - 10 яблок
Иванов Иван - 5 груш
Сидоров Сидр - 5 яблок
Петров Пётр - 7 яблок
Петров Пётр - 9 груш

А результат: (столбец D и товар столбец E)
Иванов Иван - 10 яблок, 5 груш
Сидоров Сидр - 5 яблок
Петров Пётр - 7 яблок, 9 груш

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 11:02
Nic70y Дата: Четверг, 18.01.2024, 11:42 | Сообщение № 2
Группа: Друзья
Ранг: Экселист
Сообщений: 8650
Репутация: 2207 ±
Замечаний: 0% ±

Excel 2010
для
Цитата sergeyssavateev, 18.01.2024 в 11:02, в сообщении № 1 ()
Excel 2016
должа подойти формула в столбец F
Код
=TEXTJOIN(", ",1;INDEX(C:C,MATCH(E2,B:B,0)):INDEX(C:C,MATCH(E2,B:B,1)))
писал в гугл таблице, т.к. в Excel 2010 этой функции нет


ЮMoney 41001841029809
 
Ответить
Сообщениедля
Цитата sergeyssavateev, 18.01.2024 в 11:02, в сообщении № 1 ()
Excel 2016
должа подойти формула в столбец F
Код
=TEXTJOIN(", ",1;INDEX(C:C,MATCH(E2,B:B,0)):INDEX(C:C,MATCH(E2,B:B,1)))
писал в гугл таблице, т.к. в Excel 2010 этой функции нет

Автор - Nic70y
Дата добавления - 18.01.2024 в 11:42
elovkov Дата: Четверг, 18.01.2024, 11:42 | Сообщение № 3
Группа: Друзья
Ранг: Обитатель
Сообщений: 364
Репутация: 50 ±
Замечаний: 0% ±

Excel 2013
Добрый день
Убрать дубли - для Е2 и протянуть
Код
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$9; ПОИСКПОЗ(0; ИНДЕКС(СЧЁТЕСЛИ($E$1:E1; $B$2:$B$9); 0; 0); 0)); "")

для второго столбца наверняка можно использовать ОБЪЕДИНИТЬ из новых версий, но у меня такой нет, поискав в интернете нашел такую пользовательскую функцию:
[vba]
Код
Function sergeyssavateev(TextRange As Range, SearchRange As Range, Condition As String)
    Dim Delimeter As String, i As Long
    
    Delimeter = ", " 'разделитель
     
    If SearchRange.Count <> TextRange.Count Then
        sergeyssavateev = CVErr(xlErrRef)
        Exit Function
    End If
        
    For i = 1 To SearchRange.Cells.Count
        If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter
    Next i
     
    sergeyssavateev = Left(OutText, Len(OutText) - Len(Delimeter))
End Function
[/vba]

И тогда в ячейку F2
Код
=sergeyssavateev($C$2:$C$9;$B$2:$B$9;E2)
и протянуть
К сообщению приложен файл: 3334068.xlsm (15.4 Kb)


Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица
 
Ответить
СообщениеДобрый день
Убрать дубли - для Е2 и протянуть
Код
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$9; ПОИСКПОЗ(0; ИНДЕКС(СЧЁТЕСЛИ($E$1:E1; $B$2:$B$9); 0; 0); 0)); "")

для второго столбца наверняка можно использовать ОБЪЕДИНИТЬ из новых версий, но у меня такой нет, поискав в интернете нашел такую пользовательскую функцию:
[vba]
Код
Function sergeyssavateev(TextRange As Range, SearchRange As Range, Condition As String)
    Dim Delimeter As String, i As Long
    
    Delimeter = ", " 'разделитель
     
    If SearchRange.Count <> TextRange.Count Then
        sergeyssavateev = CVErr(xlErrRef)
        Exit Function
    End If
        
    For i = 1 To SearchRange.Cells.Count
        If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter
    Next i
     
    sergeyssavateev = Left(OutText, Len(OutText) - Len(Delimeter))
End Function
[/vba]

И тогда в ячейку F2
Код
=sergeyssavateev($C$2:$C$9;$B$2:$B$9;E2)
и протянуть

Автор - elovkov
Дата добавления - 18.01.2024 в 11:42
elovkov Дата: Четверг, 18.01.2024, 11:46 | Сообщение № 4
Группа: Друзья
Ранг: Обитатель
Сообщений: 364
Репутация: 50 ±
Замечаний: 0% ±

Excel 2013
писал в гугл таблице

Почемуто у меня в гугл таблицах ОБЪЕДИНИТЬ пишет неизвестная функция, а вот TEXTJOIN нормально воспринимает


Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица
 
Ответить
Сообщение
писал в гугл таблице

Почемуто у меня в гугл таблицах ОБЪЕДИНИТЬ пишет неизвестная функция, а вот TEXTJOIN нормально воспринимает

Автор - elovkov
Дата добавления - 18.01.2024 в 11:46
Nic70y Дата: Четверг, 18.01.2024, 11:51 | Сообщение № 5
Группа: Друзья
Ранг: Экселист
Сообщений: 8650
Репутация: 2207 ±
Замечаний: 0% ±

Excel 2010
а вот TEXTJOIN
да есть такое,
не все формулы переводятся с русского,
по этому сразу переключился на английский


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 18.01.2024, 11:51
 
Ответить
Сообщение
а вот TEXTJOIN
да есть такое,
не все формулы переводятся с русского,
по этому сразу переключился на английский

Автор - Nic70y
Дата добавления - 18.01.2024 в 11:51
sergeyssavateev Дата: Четверг, 18.01.2024, 12:23 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
должа подойти формула в столбец F

=ОБЪЕДИНИТЬ(", ";1;ИНДЕКС(C:C;ПОИСКПОЗ(E2;B:B;0)):ИНДЕКС(C:C;ПОИСКПОЗ(E2;B:B;1)))


работает не правильно, объединяет только значение "1" и не со всеми строками (на скриншоте)
К сообщению приложен файл: 2203630.png (5.9 Kb)
 
Ответить
Сообщение
должа подойти формула в столбец F

=ОБЪЕДИНИТЬ(", ";1;ИНДЕКС(C:C;ПОИСКПОЗ(E2;B:B;0)):ИНДЕКС(C:C;ПОИСКПОЗ(E2;B:B;1)))


работает не правильно, объединяет только значение "1" и не со всеми строками (на скриншоте)

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 12:23
sergeyssavateev Дата: Четверг, 18.01.2024, 12:27 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
подправил форму, сделал так: =СЦЕПИТЬ(ИНДЕКС(C:C;ПОИСКПОЗ(E2;B:B;0));", ";ИНДЕКС(C:C;ПОИСКПОЗ(E2;B:B;1)))

теперь корректно
К сообщению приложен файл: 5234111.png (9.8 Kb)
 
Ответить
Сообщениеподправил форму, сделал так: =СЦЕПИТЬ(ИНДЕКС(C:C;ПОИСКПОЗ(E2;B:B;0));", ";ИНДЕКС(C:C;ПОИСКПОЗ(E2;B:B;1)))

теперь корректно

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 12:27
Nic70y Дата: Четверг, 18.01.2024, 12:32 | Сообщение № 8
Группа: Друзья
Ранг: Экселист
Сообщений: 8650
Репутация: 2207 ±
Замечаний: 0% ±

Excel 2010
Цитата sergeyssavateev, 18.01.2024 в 12:27, в сообщении № 7 ()
подправил форму
эта формула объединит только 2 ячейки
Цитата sergeyssavateev, 18.01.2024 в 12:23, в сообщении № 6 ()
работает не правильно
посмотрите файл - у себя проверить не могу
К сообщению приложен файл: 3334068_1.xlsx (17.9 Kb)


ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата sergeyssavateev, 18.01.2024 в 12:27, в сообщении № 7 ()
подправил форму
эта формула объединит только 2 ячейки
Цитата sergeyssavateev, 18.01.2024 в 12:23, в сообщении № 6 ()
работает не правильно
посмотрите файл - у себя проверить не могу

Автор - Nic70y
Дата добавления - 18.01.2024 в 12:32
sergeyssavateev Дата: Четверг, 18.01.2024, 12:36 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
посмотрите файл - у себя проверить не могу


не работает, результат на скрине (ОБЪЕДЕНИТЬ заменил на СЦЕПИТЬ, так как нет объединить)

эта формула объединит только 2 ячейки

да, получается там, где было только 1 значение - формула дублирует его(( можно как-то исправить этот момент? или может дополнительно как-то убрать такие дубли по факту?
К сообщению приложен файл: 5723908.png (53.3 Kb)
 
Ответить
Сообщение
посмотрите файл - у себя проверить не могу


не работает, результат на скрине (ОБЪЕДЕНИТЬ заменил на СЦЕПИТЬ, так как нет объединить)

эта формула объединит только 2 ячейки

да, получается там, где было только 1 значение - формула дублирует его(( можно как-то исправить этот момент? или может дополнительно как-то убрать такие дубли по факту?

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 12:36
Nic70y Дата: Четверг, 18.01.2024, 12:40 | Сообщение № 10
Группа: Друзья
Ранг: Экселист
Сообщений: 8650
Репутация: 2207 ±
Замечаний: 0% ±

Excel 2010
возможно майкрософт меня обманули на счет 2016
тогда смотрите пост:
http://www.excelworld.ru/forum/2-52884-345022-16-1705567364
или можно с доп.столбцом решить


ЮMoney 41001841029809
 
Ответить
Сообщениевозможно майкрософт меня обманули на счет 2016
тогда смотрите пост:
http://www.excelworld.ru/forum/2-52884-345022-16-1705567364
или можно с доп.столбцом решить

Автор - Nic70y
Дата добавления - 18.01.2024 в 12:40
sergeyssavateev Дата: Четверг, 18.01.2024, 12:53 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
или можно с доп.столбцом решить


понял, что есть ещё одна проблема, сцепка может быть не только 2 результатов, но и 3-4-5.
а формулы выше сцепляют только по 2 значения( а там, где есть сцепка, например, 4х значений, формула сцепляет только 1оге и 4ое (на скрине пример)

как можно сделать, чтобы объединение работа не только по 2 значениям а объединяло все?

возможно майкрософт меня обманули на счет 2016

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


понял, что есть ещё одна проблема, сцепка может быть не только 2 результатов, но и 3-4-5.
а формулы выше сцепляют только по 2 значения( а там, где есть сцепка, например, 4х значений, формула сцепляет только 1оге и 4ое (на скрине пример)

как можно сделать, чтобы объединение работа не только по 2 значениям а объединяло все?

возможно майкрософт меня обманули на счет 2016

можно также продолжить писать, смысл функций одинаковый, по другому называется

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 12:53
sergeyssavateev Дата: Четверг, 18.01.2024, 12:53 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
СКРИН
К сообщению приложен файл: 2458951.png (7.6 Kb)
 
Ответить
СообщениеСКРИН

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 12:53
sergeyssavateev Дата: Четверг, 18.01.2024, 12:54 | Сообщение № 13
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Цитата sergeyssavateev, 18.01.2024 в 12:53, в сообщении № 12 ()
СКРИН


НЕ ВЕРНЫЙ СКРИН добавил

вот корректный
 
Ответить
Сообщение
Цитата sergeyssavateev, 18.01.2024 в 12:53, в сообщении № 12 ()
СКРИН


НЕ ВЕРНЫЙ СКРИН добавил

вот корректный

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 12:54
sergeyssavateev Дата: Четверг, 18.01.2024, 12:55 | Сообщение № 14
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
с
К сообщению приложен файл: 0932518.png (5.8 Kb)
 
Ответить
Сообщениес

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 12:55
elovkov Дата: Четверг, 18.01.2024, 12:57 | Сообщение № 15
Группа: Друзья
Ранг: Обитатель
Сообщений: 364
Репутация: 50 ±
Замечаний: 0% ±

Excel 2013
sergeyssavateev, видимо в 2016 ОБЪЕДИНИТЬ нет, а мой вариант из поста №3 не подходит?


Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица

Сообщение отредактировал elovkov - Четверг, 18.01.2024, 12:57
 
Ответить
Сообщениеsergeyssavateev, видимо в 2016 ОБЪЕДИНИТЬ нет, а мой вариант из поста №3 не подходит?

Автор - elovkov
Дата добавления - 18.01.2024 в 12:57
Nic70y Дата: Четверг, 18.01.2024, 13:03 | Сообщение № 16
Группа: Друзья
Ранг: Экселист
Сообщений: 8650
Репутация: 2207 ±
Замечаний: 0% ±

Excel 2010
sergeyssavateev, если не хотите вариант от elovkov, то доп.столбцы.
формулы справятся даже если №№ идут не по-порядку
[p.s.]обратите внимание - файл перезалит
должен быть 215_1[/p.s.]
К сообщению приложен файл: 215_1.xlsx (10.3 Kb)


ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Четверг, 18.01.2024, 13:07
 
Ответить
Сообщениеsergeyssavateev, если не хотите вариант от elovkov, то доп.столбцы.
формулы справятся даже если №№ идут не по-порядку
[p.s.]обратите внимание - файл перезалит
должен быть 215_1[/p.s.]

Автор - Nic70y
Дата добавления - 18.01.2024 в 13:03
sergeyssavateev Дата: Четверг, 18.01.2024, 13:44 | Сообщение № 17
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
ОБЪЕДИНИТЬ


есть, просто называется по другому - СЦЕПИТЬ. аналогично действуют

а мой вариант из поста №3 не подходит?


так, не сразу смог разобраться как прописать это в 16ой версии, но сделал
и теперь да, всё раскидывает и сцепляет везде корректно как и надо!)

СПАСИБО вам обоим, благодаря вашим ответам получилось сделать то, что нужно) не мог никак сам понять и обратился за помощью к вам, гуру) спасибо ещё раз))
 
Ответить
Сообщение
ОБЪЕДИНИТЬ


есть, просто называется по другому - СЦЕПИТЬ. аналогично действуют

а мой вариант из поста №3 не подходит?


так, не сразу смог разобраться как прописать это в 16ой версии, но сделал
и теперь да, всё раскидывает и сцепляет везде корректно как и надо!)

СПАСИБО вам обоим, благодаря вашим ответам получилось сделать то, что нужно) не мог никак сам понять и обратился за помощью к вам, гуру) спасибо ещё раз))

Автор - sergeyssavateev
Дата добавления - 18.01.2024 в 13:44
Nic70y Дата: Четверг, 18.01.2024, 13:50 | Сообщение № 18
Группа: Друзья
Ранг: Экселист
Сообщений: 8650
Репутация: 2207 ±
Замечаний: 0% ±

Excel 2010
Цитата sergeyssavateev, 18.01.2024 в 13:44, в сообщении № 17 ()
есть, просто называется по другому - СЦЕПИТЬ. аналогично действуют
sergeyssavateev, нет это разные функции,
похожесть в чем-то, но совсем не то.


ЮMoney 41001841029809
 
Ответить
Сообщение
Цитата sergeyssavateev, 18.01.2024 в 13:44, в сообщении № 17 ()
есть, просто называется по другому - СЦЕПИТЬ. аналогично действуют
sergeyssavateev, нет это разные функции,
похожесть в чем-то, но совсем не то.

Автор - Nic70y
Дата добавления - 18.01.2024 в 13:50
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Извлечение и сцепка данных по дублям (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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