Форум программистов
 

Восстановите пароль или Зарегистрируйтесь на форуме, о проблемах и с заказом рекламы пишите сюда - alarforum@yandex.ru, проверяйте папку спам!

Вернуться   Форум программистов > Microsoft Office и VBA программирование > Microsoft Office Excel
Регистрация

Восстановить пароль
Повторная активизация e-mail

Купить рекламу на форуме - 42 тыс руб за месяц

Ответ
 
Опции темы Поиск в этой теме
Старый 22.05.2012, 21:56   #1
strannick
Форумчанин
 
Регистрация: 21.10.2011
Сообщений: 433
По умолчанию Формула выборки уникальных значений по критерию

Добрый вечер, уважаемые форумчане!
На первом листе пополняемый массив из двух столбцов с повторяющимися номерами ID вида U1300001. Первый столбец - родительские ID, второй - дочерние ID. На втором листе в первом столбце все существующие ID на сегодня. Необходимо на втором листе напротив каждого ID в строку вывести все относящиеся к нему дочерние ID из первого листа. В реале таблица из 10000 строк. Попытался отобрать все уникальные дочерние ID при помощи формулы массива:

=ИНДЕКС(Лист1!$B$2:$B$10000;НАИМЕНЬ ШИЙ(ЕСЛИ((СЧЁТЕСЛИ($B$2:B$2;Лист1!$ B$2:$B$10000)=0)*(Лист1!$A$2:$A$100 00=$A$2);СТОЛБЕЦ(A1));1))

Не отбирает. Выдает нули. Вроде все ссылки в формуле правильные. Где я ошибаюсь? СТОЛБЕЦ(А1)? Подскажите. Пример во вложении.
Заранее спасибо.
Вложения
Тип файла: rar фрагмент.rar (8.2 Кб, 55 просмотров)
strannick вне форума Ответить с цитированием
Старый 22.05.2012, 22:01   #2
Serge 007
Участник клуба
 
Аватар для Serge 007
 
Регистрация: 15.12.2009
Сообщений: 1,448
По умолчанию

Код:
=ИНДЕКС(Лист1!$B$2:$B$41;НАИМЕНЬШИЙ(ЕСЛИ(ПОИСКПОЗ(Лист1!$A$2:$A$41;Лист1!$A$2:$A$41;0)=СТРОКА($1:$40);СТРОКА($1:$40));СТОЛБЕЦ(A1)))
Цитата:
Сообщение от strannick Посмотреть сообщение
...В реале таблица из 10000 строк...
Тормозить будет.
Лучше макросом
Бесплатная помощь: www.excelworld.ru
Платная помощь: serge_007.planetaexcel@mail.ru
https://yoomoney.ru: 41001419691823
Serge 007 вне форума Ответить с цитированием
Старый 22.05.2012, 22:22   #3
strannick
Форумчанин
 
Регистрация: 21.10.2011
Сообщений: 433
По умолчанию

Да, в принципе, понимаю, что макросом лучше будет. Вот только как, ума не приложу. Кстати, чего-то формула у меня не заработала. Выдала первого, а в остальных #ЧИСЛО!.
strannick вне форума Ответить с цитированием
Старый 22.05.2012, 22:35   #4
Hugo121
Старожил
 
Регистрация: 11.05.2010
Сообщений: 5,166
По умолчанию

Если выводить в одну ячейку через разделитель по вкусу - уже есть UDF.
Даже несколько - VLOOKUPCOUPLE() или СцепитьЕсли(). Гугл сразу находит.
Можно VLOOKUPCOUPLE() переписать на массив, т.к. 10000- всёж видно, как формула работает.
webmoney: E265281470651 Z422237915069 R418926282008
Hugo121 вне форума Ответить с цитированием
Старый 22.05.2012, 22:44   #5
Hugo121
Старожил
 
Регистрация: 11.05.2010
Сообщений: 5,166
По умолчанию

Вот например, на массиве. Убрал работу с закрытой книгой, добавил работу с столбцом целиком.
Надеюсь, совпадений не будет столько, что не уместится в ячейку?
Вложения
Тип файла: rar фрагмент.rar (10.5 Кб, 82 просмотров)
webmoney: E265281470651 Z422237915069 R418926282008
Hugo121 вне форума Ответить с цитированием
Старый 22.05.2012, 23:45   #6
strannick
Форумчанин
 
Регистрация: 21.10.2011
Сообщений: 433
По умолчанию

Класс! Работает отлично. Ну дальше я разберусь. Спасибище!!!
strannick вне форума Ответить с цитированием
Старый 23.05.2012, 00:02   #7
strannick
Форумчанин
 
Регистрация: 21.10.2011
Сообщений: 433
По умолчанию

Рановато я обрадовался. Выдает не уникальные, а все встречающиеся. Как быть?
strannick вне форума Ответить с цитированием
Старый 23.05.2012, 00:12   #8
Hugo121
Старожил
 
Регистрация: 11.05.2010
Сообщений: 5,166
По умолчанию

Отбор уникальных есть в СцепитьЕсли()
Версию VLOOKUPCOUPLE() с таким функционалом я себе тоже сделал, но она пока не на массиве. И отбирает только уникальные, без опции.
Код:
Function VLOOKUPCOUPLE3(Table As Variant, SearchColumnNum As Integer, SearchValue As Variant, _
                        RezultColumnNum As Integer, Separator_ As String)
'Table - таблица, где ищем
'SearchColumnNum - столбец, где ищем
'SearchValue - данные, которые ищем
'RezultColumnNum - колонка, откуда берём результат
'Separator_ - разделитель, желательно вводить с пробелом в конце

    Dim i As Long, oDict As Object, temp As String
    Set oDict = CreateObject("Scripting.Dictionary")

    Select Case TypeName(Table)
    Case "Range"
        For i = 1 To Table.Rows.Count
            If Table.Cells(i, SearchColumnNum) = SearchValue Then
                temp = Table.Cells(i, RezultColumnNum)
                If temp <> "" Then
                    If Not oDict.Exists(temp) Then
                        oDict.Add temp, CStr(1)
                        If VLOOKUPCOUPLE3 <> "" Then
                            VLOOKUPCOUPLE3 = VLOOKUPCOUPLE3 & Separator_ & Table.Cells(i, RezultColumnNum)
                        Else
                            VLOOKUPCOUPLE3 = Table.Cells(i, RezultColumnNum)
                        End If
                    End If
                End If
            End If
        Next i
    Case "Variant()"
        For i = 1 To UBound(Table)
            If Table(i, SearchColumnNum) = SearchValue Then
                temp = Table.Cells(i, RezultColumnNum)
                If temp <> "" Then
                    If Not oDict.Exists(temp) Then
                        oDict.Add temp, CStr(1)
                        If VLOOKUPCOUPLE3 <> "" Then
                            VLOOKUPCOUPLE3 = VLOOKUPCOUPLE3 & Separator_ & Table(i, RezultColumnNum)
                        Else
                            VLOOKUPCOUPLE3 = Table(i, RezultColumnNum)
                        End If
                    End If
                End If
            End If
        Next i
    End Select
    If VLOOKUPCOUPLE3 = 0 Then VLOOKUPCOUPLE3 = ""
End Function
Вариант на массиве, без работы с закрытыми книгами, но работает с целым столбцом (т.е. можно и под 2007 писать в ячейку =VLOOKUPCOUPLE3_s(Лист1!A:B;1;A2;2; ", ") ).
И с отбором уникальных:
Код:
Function VLOOKUPCOUPLE3_s(Table As Variant, SearchColumnNum As Integer, SearchValue As Variant, _
                          RezultColumnNum As Integer, Separator_ As String)
'Table - таблица, где ищем
'SearchColumnNum - столбец, где ищем
'SearchValue - данные, которые ищем
'RezultColumnNum - колонка, откуда берём результат
'Separator_ - разделитель, желательно вводить с пробелом в конце

    Dim a(), i As Long, oDict As Object, temp As String
    Set oDict = CreateObject("Scripting.Dictionary")

    a = Intersect(Table.Parent.UsedRange, Table).Value

    For i = 1 To UBound(a)
        If a(i, SearchColumnNum) = SearchValue Then
            temp = a(i, RezultColumnNum)
            If temp <> "" Then
                If Not oDict.Exists(temp) Then
                    oDict.Add temp, vbNullString
                    If VLOOKUPCOUPLE3_s <> "" Then
                        VLOOKUPCOUPLE3_s = VLOOKUPCOUPLE3_s & Separator_ & a(i, RezultColumnNum)
                    Else
                        VLOOKUPCOUPLE3_s = a(i, RezultColumnNum)
                    End If
                End If
            End If
        End If
    Next i
    If VLOOKUPCOUPLE3_s = 0 Then VLOOKUPCOUPLE3_s = ""
End Function
Если кому охота - можно собрать третий вариант - для закрытых книг, и четвёртый - с опциональным отбором уникальных
webmoney: E265281470651 Z422237915069 R418926282008

Последний раз редактировалось Hugo121; 23.05.2012 в 00:31.
Hugo121 вне форума Ответить с цитированием
Старый 23.05.2012, 00:32   #9
strannick
Форумчанин
 
Регистрация: 21.10.2011
Сообщений: 433
По умолчанию

Во, последняя сработала.Тяжеловато, но ничего. Вот теперь сосчитать их надо сколько в каждой ячейке получилось. Спасибо еще раз!!!
strannick вне форума Ответить с цитированием
Старый 23.05.2012, 09:10   #10
Hugo121
Старожил
 
Регистрация: 11.05.2010
Сообщений: 5,166
По умолчанию

Как считать собрались - на пальцах или на калкуляторе?

Код:
Function VLOOKUPCOUPLE3_s(Table As Variant, SearchColumnNum As Integer, SearchValue As Variant, _
                          RezultColumnNum As Integer, Separator_ As String)
'Table - таблица, где ищем
'SearchColumnNum - столбец, где ищем
'SearchValue - данные, которые ищем
'RezultColumnNum - колонка, откуда берём результат
'Separator_ - разделитель, желательно вводить с пробелом в конце

    Dim a(), i As Long, oDict As Object, temp As String
    Dim cnt&
    Set oDict = CreateObject("Scripting.Dictionary")

    a = Intersect(Table.Parent.UsedRange, Table).Value

    For i = 1 To UBound(a)
        If a(i, SearchColumnNum) = SearchValue Then
            temp = a(i, RezultColumnNum)
            If temp <> "" Then
                If Not oDict.Exists(temp) Then
                cnt = cnt + 1
                    oDict.Add temp, vbNullString
                    If VLOOKUPCOUPLE3_s <> "" Then
                        VLOOKUPCOUPLE3_s = VLOOKUPCOUPLE3_s & Separator_ & a(i, RezultColumnNum)
                    Else
                        VLOOKUPCOUPLE3_s = a(i, RezultColumnNum)
                    End If
                End If
            End If
        End If
    Next i
    If VLOOKUPCOUPLE3_s = 0 Then
    VLOOKUPCOUPLE3_s = ""
    Else
    VLOOKUPCOUPLE3_s = VLOOKUPCOUPLE3_s & " Всего=" & cnt
    End If
End Function
webmoney: E265281470651 Z422237915069 R418926282008
Hugo121 вне форума Ответить с цитированием
Ответ


Купить рекламу на форуме - 42 тыс руб за месяц

Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск


Похожие темы
Тема Автор Раздел Ответов Последнее сообщение
выбор значений по критерию Volodymyr Microsoft Office Access 3 30.08.2011 12:29
Из вертикальной выборки уникальных, в горизонтальную. masterenergy Microsoft Office Excel 5 01.04.2011 16:41
Список уникальных значений PARTOS Microsoft Office Excel 13 18.12.2009 11:14
Отбор уникальных значений Alex___ Microsoft Office Excel 11 18.08.2009 19:31
Выборка уникальных значений Mary_star SQL, базы данных 9 11.02.2008 22:46