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

Вход

Регистрация

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

 

= Мир MS Excel/Сцепить+ВПР уникальные значения - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сцепить+ВПР уникальные значения (Формулы/Formulas)
Сцепить+ВПР уникальные значения
tamara19 Дата: Понедельник, 23.03.2020, 13:11 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день,
Есть проблема с тем, чтобы сцепить в одну ячейку данные столбца, но не все подряд, а только уникальные значения.
То есть, например, номеру партии "2339" соотвутствуют фамилии покупателей Иванов И., Андреев Е., Ионов О., Попов А., Андреев И., Самсонов У. без повторяющихся ФИО.
Я использовала на Листе 1 следующую формулу -
Код
=СцепитьЕсли(Лист2!A:A;A2;Лист2!C:C;", ";)

, но она просто всё сцепляет без удаления повторов.

Помогите, пожалуйста. :shy:

Пример таблицы вложен в файл. Лист 2 - исходная таблица, Лист 1 - то, что я сама сделала...
К сообщению приложен файл: 0924640.xlsm(22.0 Kb)


Сообщение отредактировал tamara19 - Понедельник, 23.03.2020, 13:28
 
Ответить
СообщениеДобрый день,
Есть проблема с тем, чтобы сцепить в одну ячейку данные столбца, но не все подряд, а только уникальные значения.
То есть, например, номеру партии "2339" соотвутствуют фамилии покупателей Иванов И., Андреев Е., Ионов О., Попов А., Андреев И., Самсонов У. без повторяющихся ФИО.
Я использовала на Листе 1 следующую формулу -
Код
=СцепитьЕсли(Лист2!A:A;A2;Лист2!C:C;", ";)

, но она просто всё сцепляет без удаления повторов.

Помогите, пожалуйста. :shy:

Пример таблицы вложен в файл. Лист 2 - исходная таблица, Лист 1 - то, что я сама сделала...

Автор - tamara19
Дата добавления - 23.03.2020 в 13:11
китин Дата: Понедельник, 23.03.2020, 13:19 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 6153
Репутация: 950 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
tamara19, Вы смотрите, куда размещаете тему. Здесь Готовые решения, а не Вопросы по Эксель. Перенес тему
- Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщениеtamara19, Вы смотрите, куда размещаете тему. Здесь Готовые решения, а не Вопросы по Эксель. Перенес тему
- Прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)

Автор - китин
Дата добавления - 23.03.2020 в 13:19
Pelena Дата: Понедельник, 23.03.2020, 14:28 | Сообщение № 3
Группа: Админы
Ранг: Местный житель
Сообщений: 15466
Репутация: 3383 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщениеtamara19, посмотрите готовое решение
Конкатенация уникальных значений из диапазона в строку с разделителем

Автор - Pelena
Дата добавления - 23.03.2020 в 14:28
китин Дата: Понедельник, 23.03.2020, 15:13 | Сообщение № 4
Группа: Модераторы
Ранг: Экселист
Сообщений: 6153
Репутация: 950 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
или при помощи великого и ужасного Power Query
[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Сгруппированные строки" = Table.Group(Источник, {"номер партии"}, {{"Количество", each _, type table}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Сгруппированные строки", "Пользовательская", each List.Distinct(Table.Column([Количество],"фамилия"))),
    #"Извлеченные значения" = Table.TransformColumns(#"Добавлен пользовательский объект", {"Пользовательская", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Извлеченные значения",{"Количество"})
in
    #"Удаленные столбцы"
[/vba]
К сообщению приложен файл: PQ_.xlsm(32.1 Kb)


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщениеили при помощи великого и ужасного Power Query
[vba]
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Сгруппированные строки" = Table.Group(Источник, {"номер партии"}, {{"Количество", each _, type table}}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Сгруппированные строки", "Пользовательская", each List.Distinct(Table.Column([Количество],"фамилия"))),
    #"Извлеченные значения" = Table.TransformColumns(#"Добавлен пользовательский объект", {"Пользовательская", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Извлеченные значения",{"Количество"})
in
    #"Удаленные столбцы"
[/vba]

Автор - китин
Дата добавления - 23.03.2020 в 15:13
Hugo Дата: Понедельник, 23.03.2020, 19:16 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2909
Репутация: 646 ±
Замечаний: 0% ±

Код
=VLOOKUPCOUPLE(Лист2!A:C;1;A2;3;", ")

Есть ещё такая UDF, код где-то был на форуме...
P.S. не нашёл...
[vba]
Код

Function VLOOKUPCOUPLE(Table As Variant, _
                       SearchColumnNum As Integer, _
                       SearchValue As Variant, _
                       RezultColumnNum As Integer, _
                       Separator_ As String, _
                       Optional BezPovtorov As Boolean = True)

'Table - таблица, где ищем
'SearchColumnNum - столбец, где ищем
'SearchValue - данные, которые ищем
'RezultColumnNum - столбец, откуда берём результат
'Separator_ - разделитель, желательно вводить с пробелом в конце
'BezPovtorov - если поставить 0, то будут выведены все повторяющиеся совпадения

    Dim i As Long, tmp As String, vlk

    If TypeName(Table) = "Range" Then Table = Intersect(Table.Parent.UsedRange, Table).Value
    If BezPovtorov Then
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(Table)
                If Table(i, SearchColumnNum) = SearchValue Then
                    tmp = Table(i, RezultColumnNum)
                    If tmp <> "" Then
                        If Not .Exists(tmp) Then
                            .Add tmp, 0&
                            vlk = vlk & Separator_ & Table(i, RezultColumnNum)
                        End If
                    End If
                End If
            Next i
        End With
    Else
        For i = 1 To UBound(Table)
            If Table(i, SearchColumnNum) = SearchValue Then
                vlk = vlk & Separator_ & Table(i, RezultColumnNum)
            End If
        Next i
    End If
    If vlk > 0 Then vlk = Mid(vlk, Len(Separator_) + 1) Else vlk = ""
    VLOOKUPCOUPLE = vlk
End Function
[/vba]


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069


Сообщение отредактировал Hugo - Понедельник, 23.03.2020, 19:19
 
Ответить
Сообщение
Код
=VLOOKUPCOUPLE(Лист2!A:C;1;A2;3;", ")

Есть ещё такая UDF, код где-то был на форуме...
P.S. не нашёл...
[vba]
Код

Function VLOOKUPCOUPLE(Table As Variant, _
                       SearchColumnNum As Integer, _
                       SearchValue As Variant, _
                       RezultColumnNum As Integer, _
                       Separator_ As String, _
                       Optional BezPovtorov As Boolean = True)

'Table - таблица, где ищем
'SearchColumnNum - столбец, где ищем
'SearchValue - данные, которые ищем
'RezultColumnNum - столбец, откуда берём результат
'Separator_ - разделитель, желательно вводить с пробелом в конце
'BezPovtorov - если поставить 0, то будут выведены все повторяющиеся совпадения

    Dim i As Long, tmp As String, vlk

    If TypeName(Table) = "Range" Then Table = Intersect(Table.Parent.UsedRange, Table).Value
    If BezPovtorov Then
        With CreateObject("Scripting.Dictionary")
            For i = 1 To UBound(Table)
                If Table(i, SearchColumnNum) = SearchValue Then
                    tmp = Table(i, RezultColumnNum)
                    If tmp <> "" Then
                        If Not .Exists(tmp) Then
                            .Add tmp, 0&
                            vlk = vlk & Separator_ & Table(i, RezultColumnNum)
                        End If
                    End If
                End If
            Next i
        End With
    Else
        For i = 1 To UBound(Table)
            If Table(i, SearchColumnNum) = SearchValue Then
                vlk = vlk & Separator_ & Table(i, RezultColumnNum)
            End If
        Next i
    End If
    If vlk > 0 Then vlk = Mid(vlk, Len(Separator_) + 1) Else vlk = ""
    VLOOKUPCOUPLE = vlk
End Function
[/vba]

Автор - Hugo
Дата добавления - 23.03.2020 в 19:16
Hugo Дата: Понедельник, 23.03.2020, 19:27 | Сообщение № 6
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2909
Репутация: 646 ±
Замечаний: 0% ±

А вообще и изначальная UDF сцепляет без повторов, если ей это приказать. А моя работает наоборот - ей нужно приказать чтоб повторы показывала! :)


excel@nxt.ru
webmoney: E265281470651 R418926282008 Z422237915069
 
Ответить
СообщениеА вообще и изначальная UDF сцепляет без повторов, если ей это приказать. А моя работает наоборот - ей нужно приказать чтоб повторы показывала! :)

Автор - Hugo
Дата добавления - 23.03.2020 в 19:27
tamara19 Дата: Вторник, 24.03.2020, 17:17 | Сообщение № 7
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Огромное спасибо
 
Ответить
СообщениеОгромное спасибо

Автор - tamara19
Дата добавления - 24.03.2020 в 17:17
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Сцепить+ВПР уникальные значения (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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