Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Как связать данные по трём критериям через запятую?
 
Имеется
1. Закладка: tbl1
    Таблица: tbl1
2. Закладка: tbl2
    Таблица: tbl2

Вопрос
Как сделать чтобы tbl1 попадали данные отобранные по трём критериям из таблицы tbl2?
При этом проходила их сцепка:
      - вар 1.(tbl1, столбец 1) через запятую;
и
      - вар 2.(tbl2, столбец 2) через запятую и взять в кавычки.
1.jpg (66.07 КБ)
 
поищите пользовательскую ф-цию VLOOKUPCOUPLE по форуму, вроде должна подойти.
 
UDF
Код
Function СЦЕПИТЬЕСЛИМН(rngU As Range, ParamArray Conditions()) As String
'rngU - диапазон сцепления
'Conditions() - массив ПАР значений вида: Диапазон_Условий1;Условие1;Диапазон_Условий2;Условие2...Диапазон_УсловийN;УсловиеN, обязательный
'               должен иметь хотя-бы одну пару значений.
'Разделителем найденных уникальных значений является ', ' (запятая с пробелом)
'Все диапазоны должны состоять из одного столбца и иметь равное кол-во строк
Dim cl()
Dim arrFlag() As Boolean
Dim I&, J&
On Error Resume Next
cl = rngU.Value
For I = 1 To UBound(cl)
    ReDim arrFlag(Int(UBound(Conditions) / 2))
    For J = LBound(Conditions) To UBound(Conditions) Step 2
        If Left(Conditions(J + 1), 1) = ">" Or Left(Conditions(J + 1), 1) = "<" Then
            If Application.Evaluate(rngU.Parent.Cells(rngU(I).Row, Conditions(J).Column).Value & Conditions(J + 1)) Then
                If Err = 0 Then
                    arrFlag(Int(J / 2)) = True
                Else
                    Err.Clear
                End If
            End If
        ElseIf rngU.Parent.Cells(rngU(I).Row, Conditions(J).Column).Value Like Conditions(J + 1) Then
            arrFlag(Int(J / 2)) = True
        End If
    Next
    If WorksheetFunction.And(arrFlag) = True Then
        If СЦЕПИТЬЕСЛИМН <> Empty Then
            СЦЕПИТЬЕСЛИМН = СЦЕПИТЬЕСЛИМН & ", " & cl(I, 1)
        Else
            СЦЕПИТЬЕСЛИМН = cl(I, 1)
        End If
    End If
Next
End Function
Согласие есть продукт при полном непротивлении сторон.
 
Цитата
Sanja написал:
UDF
Я пока разбираюсь...
Может  подскажете почему строки пустые?
см. сиркн.
http://prntscr.com/ecpj0n
 
VLOOKUPCOUPLE прямо "из коробки" не пойдёт, нужно дорабатывать. Или в каждой таблице делать допстолбцы - тогда да.
Так что лучше берите уже готовую СЦЕПИТЬЕСЛИМН()
 
Цитата
excel_pl написал:
почему строки пустые?
- потому что там нет формулы! :)
 
Да вроде есть...
см. скрин http://prntscr.com/ecpmvp
Изменено: excel_pl - 24 Фев 2017 11:37:14
 
Я не вижу как настроил Sanja, но у меня работает так:
Код
=СЦЕПИТЬЕСЛИМН('tbl1'!$F$5:$F$31,'tbl2'!$C$5:$C$31,[@ОРГАНИЗАЦИЯ],'tbl2'!$D$5:$D$31,[@ПРОЕКТ],'tbl2'!$E$5:$E$31,[@ТАБЛИЦА])
 
Это правильно ?
Код
     'tbl1'!$F$5:$F$31
см. скрин  http://prntscr.com/ecpvio
Для чего это? Или у нас разное расположение таблиц?
Цитата
Hugo написал:  как настроил Sanja
Если вы про это
Код
=СЦЕПИТЬЕСЛИМН(Таблица13[ЗАДАЧИ];Таблица13[ОРГАНИЗАЦИЯ];[@ОРГАНИЗАЦИЯ];Таблица13[ПРОЕКТ];[@ПРОЕКТ];Таблица13[ТАБЛИЦА];[@ТАБЛИЦА])
Изменено: excel_pl - 24 Фев 2017 11:37:41
 
Цитата
excel_pl написал: Это правильно ?Код ? 1     'tbl1'!$F$5:$F$31
Цитата
Hugo написал: Я не вижу как настроил Sanja, но у меня работает так:
Исправил на
Код
 'tbl2'!$F$5:$F$31
Так наверное правильней?
Но всё равно не работает
см .скрин http://prntscr.com/ecq03k
Изменено: excel_pl - 24 Фев 2017 11:38:07
 
что за переносы строк в формуле???
формула.jpg (10.5 КБ)
Изменено: V - 24 Фев 2017 10:40:20
 
Ошибку убрал
Исправил
код
Код
=СЦЕПИТЬЕСЛИМН(
                            'tbl2'!$F$5:$F$31;
                            'tbl2'!$C$5:$C$31;[@ОРГАНИЗАЦИЯ];
                            'tbl2'!$D$5:$D$31;[@ПРОЕКТ];
                            'tbl2'!$E$5:$E$31;[@ТАБЛИЦА])
Но всё равно только  в одной строке отображается результат
http://prntscr.com/ecq3g9
 
Цитата
V написал: что за переносы строк в формуле???
Это я для нагладности
 
Переносы кстати не мешают.
P.S. Да, что-то я там напутал, и впрямь не работает. Может потому что таблица слишком умная? :)
Изменено: Hugo - 24 Фев 2017 10:54:20
 
Цитата
Hugo написал:
у меня все 3 строки отрабатывают в таком виде:
В чём вы отрабатываете?
Ну как оно отрабатывает ?

Если надо наверное

Не ('tbl1')
Код
'tbl1'!$F$5:$F$31,

а ('tbl2')
Код
'tbl2'!$F$5:$F$31,

И не "," , а ";" (по крайней  мере у меня как-то работает только с ";", правда показывает только одну строку)
см. скрин http://prntscr.com/ecq5z7
Изменено: excel_pl - 24 Фев 2017 10:51:55
 
С tb1/2 согласен, попутал, а вот насчёт запятых могу поспорить! Но не буду, у каждого свои недостатки как говорится... :)
 
Цитата
Hugo написал:
а вот насчёт запятых могу поспорить!
Да дело не в споре...  Мне даже интересно.. В чём колдовство?
Ставлю в ячейку
Код
=СЦЕПИТЬЕСЛИМН(   
'tbl2'!$F$5:$F$31,
            'tbl2'!$C$5:$C$31,[@ОРГАНИЗАЦИЯ],
            'tbl2'!$D$5:$D$31,[@ПРОЕКТ],
            'tbl2'!$E$5:$E$31,[@ТАБЛИЦА])
получаю ошибку http://prntscr.com/ecqc7p
У меня ошибка, а у вас работает..

Ставлю
Код
=СЦЕПИТЬЕСЛИМН(
                            'tbl2'!$F$5:$F$31;
                            'tbl2'!$C$5:$C$31;[@ОРГАНИЗАЦИЯ];
                            'tbl2'!$D$5:$D$31;[@ПРОЕКТ];
                            'tbl2'!$E$5:$E$31;[@ТАБЛИЦА])
получаю подсветку  http://prntscr.com/ecqcgr
И получаю запись тоьлко в одной строке, а не во всех ячейках
http://prntscr.com/ecqczp
Изменено: excel_pl - 24 Фев 2017 11:38:47
 
Цитата
excel_pl написал: почему строки пустые?
Потому что сцеплять нечего. В Вашей tbl2 нет строки с 'ОРГ 1' + 'ПРОЕКТ 1' + 'ТАБЛ 2'
Согласие есть продукт при полном непротивлении сторон.
 
)))
Вот я  бестолочь...
 
Бывает. Глаз замыливается  :)
Согласие есть продукт при полном непротивлении сторон.
 
:)
Цитата
excel_pl написал: см. сиркн.
 
Перед End Function добавьте строку
Код
If СЦЕПИТЬЕСЛИМН = Empty Then СЦЕПИТЬЕСЛИМН = "#Н/Д"
что-бы пустая ячейка Вас не смущала
Согласие есть продукт при полном непротивлении сторон.
 
Правильнее думаю будет
Код
If СЦЕПИТЬЕСЛИМН = Empty Then СЦЕПИТЬЕСЛИМН = CVErr(xlErrNA)

но убрать as string в первой строке (самой первой!)
Потому что для меня например "#Н/Д" - это что-то иностранное :)
 
Hugo, спасибо, возьму на вооружение
Согласие есть продукт при полном непротивлении сторон.
Страницы: 1
Читают тему (гостей: 1)