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

Вход

Регистрация

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

 

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

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

Excel 2010
Доброго дня всем!
Требуется отобрать уникальные значения из диапазона ячеек с формулами на другой лист этого документа. Использование расширенного фильтра не допускается, т.к. он работает только в текущем листе, не работает с результатом формул и при добавлении или изменении данных в этом диапазоне придётся заново все фильтровать.
Где-то могу ошибаться-поправьте, пжлст.

Для примера приложил документ.

отобрать уникальные значения из "Лист 1" W3:W37 на лист "Данные" в столбец C.

Либо только:
отобрать уникальные значения из "Лист 1" W3:W37 на лист "Сортировка" по двум критериям (тип дерева и вид материала).
К примеру:
значение ячейки из листа "Лист1" W8 скопируется в ячейку B3 на листе "Сортировка", т.к. это РЕЙКА из ДУБА. И т.д.
К сообщению приложен файл: 8695126.xlsx(19Kb)
 
Ответить
СообщениеДоброго дня всем!
Требуется отобрать уникальные значения из диапазона ячеек с формулами на другой лист этого документа. Использование расширенного фильтра не допускается, т.к. он работает только в текущем листе, не работает с результатом формул и при добавлении или изменении данных в этом диапазоне придётся заново все фильтровать.
Где-то могу ошибаться-поправьте, пжлст.

Для примера приложил документ.

отобрать уникальные значения из "Лист 1" W3:W37 на лист "Данные" в столбец C.

Либо только:
отобрать уникальные значения из "Лист 1" W3:W37 на лист "Сортировка" по двум критериям (тип дерева и вид материала).
К примеру:
значение ячейки из листа "Лист1" W8 скопируется в ячейку B3 на листе "Сортировка", т.к. это РЕЙКА из ДУБА. И т.д.

Автор - al1025t01
Дата добавления - 30.06.2016 в 17:01
abtextime Дата: Четверг, 30.06.2016, 18:06 | Сообщение № 2
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
Сделал через UDF, для листа "Сортировка". Потестите, а то некогда слегка

[vba]
Код
Public Function SSearch(S1, S2 As String, R As Range, N As Integer) As String
'Ищет N-ю по порядку уникальную ячейку

Const MMax = 1000
Dim UnStr(MMax) As String

M = 0
'Cчетчик уникальных значений

For i = 1 To R.Rows.Count
'Цикл по строкам
    If R.Cells(i, 1) = S1 And R.Cells(i, 2) = S2 Then
        Found = False
        RC = R.Cells(i, R.Columns.Count)
        For j = 1 To M
' цикл по массиву уникальных строк
            If RC = UnStr(j) Then
                Found = True
                Exit For
            End If
        Next j
        If Not Found Then
            M = M + 1
            If M = N Then
                SSearch = RC
                Exit Function
            End If
            If M > MMax Then
                SSearch = "превышение макс. кол-ва уникальных записей, поправьте UDF SSearch"
                Exit Function
            End If
        End If
    End If
Next i

End Function
[/vba]


стопэ, глюки
К сообщению приложен файл: 8695126.xlsm(30Kb)


Сообщение отредактировал abtextime - Четверг, 30.06.2016, 18:10
 
Ответить
СообщениеСделал через UDF, для листа "Сортировка". Потестите, а то некогда слегка

[vba]
Код
Public Function SSearch(S1, S2 As String, R As Range, N As Integer) As String
'Ищет N-ю по порядку уникальную ячейку

Const MMax = 1000
Dim UnStr(MMax) As String

M = 0
'Cчетчик уникальных значений

For i = 1 To R.Rows.Count
'Цикл по строкам
    If R.Cells(i, 1) = S1 And R.Cells(i, 2) = S2 Then
        Found = False
        RC = R.Cells(i, R.Columns.Count)
        For j = 1 To M
' цикл по массиву уникальных строк
            If RC = UnStr(j) Then
                Found = True
                Exit For
            End If
        Next j
        If Not Found Then
            M = M + 1
            If M = N Then
                SSearch = RC
                Exit Function
            End If
            If M > MMax Then
                SSearch = "превышение макс. кол-ва уникальных записей, поправьте UDF SSearch"
                Exit Function
            End If
        End If
    End If
Next i

End Function
[/vba]


стопэ, глюки

Автор - abtextime
Дата добавления - 30.06.2016 в 18:06
abtextime Дата: Четверг, 30.06.2016, 18:15 | Сообщение № 3
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
[vba]
Код

Public Function SSearch(S1, S2 As String, R As Range, N As Integer) As String
'Ищет N-ю по порядку уникальную ячейку

Const MMax = 1000
Dim UnStr(MMax) As String

M = 0
'Cчетчик уникальных значений

For i = 1 To R.Rows.Count
'Цикл по строкам
    If Trim(R.Cells(i, 1)) = S1 And Trim(R.Cells(i, 2)) = S2 Then
        Found = False
        RC = Trim(R.Cells(i, R.Columns.Count))
        For j = 1 To M
' цикл по массиву уникальных строк
            If RC = UnStr(j) Then
                Found = True
                Exit For
            End If
        Next j
        If Not Found Then
            M = M + 1
            UnStr(M) = RC
            If M = N Then
                SSearch = RC
                Exit Function
            End If
            If M > MMax Then
                SSearch = "превышение макс. кол-ва уникальных записей, поправьте UDF SSearch"
                Exit Function
            End If
        End If
    End If
Next i

End Function
[/vba]

так, что ли ... проверяйте ...
К сообщению приложен файл: 0745935.xlsm(30Kb)
 
Ответить
Сообщение[vba]
Код

Public Function SSearch(S1, S2 As String, R As Range, N As Integer) As String
'Ищет N-ю по порядку уникальную ячейку

Const MMax = 1000
Dim UnStr(MMax) As String

M = 0
'Cчетчик уникальных значений

For i = 1 To R.Rows.Count
'Цикл по строкам
    If Trim(R.Cells(i, 1)) = S1 And Trim(R.Cells(i, 2)) = S2 Then
        Found = False
        RC = Trim(R.Cells(i, R.Columns.Count))
        For j = 1 To M
' цикл по массиву уникальных строк
            If RC = UnStr(j) Then
                Found = True
                Exit For
            End If
        Next j
        If Not Found Then
            M = M + 1
            UnStr(M) = RC
            If M = N Then
                SSearch = RC
                Exit Function
            End If
            If M > MMax Then
                SSearch = "превышение макс. кол-ва уникальных записей, поправьте UDF SSearch"
                Exit Function
            End If
        End If
    End If
Next i

End Function
[/vba]

так, что ли ... проверяйте ...

Автор - abtextime
Дата добавления - 30.06.2016 в 18:15
abtextime Дата: Четверг, 30.06.2016, 18:34 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
Пока мэтры не напинали, поправлю хотя бы самую корявую корявость в коде

[vba]
Код
Public Function SSearch(S1, S2 As String, R As Range, N As Integer) As String
'Ищет N-ю по порядку уникальную ячейку

Dim UnStr() As String
ReDim UnStr(N) As String

M = 0
'Cчетчик уникальных значений

For i = 1 To R.Rows.Count
'Цикл по строкам
    If Trim(R.Cells(i, 1)) = S1 And Trim(R.Cells(i, 2)) = S2 Then
        Found = False
        RC = Trim(R.Cells(i, R.Columns.Count))
        For j = 1 To M
' цикл по массиву уникальных строк
            If RC = UnStr(j) Then
                Found = True
                Exit For
            End If
        Next j
        If Not Found Then
            M = M + 1
            UnStr(M) = RC
            If M = N Then
                SSearch = RC
                Exit Function
            End If
        End If
    End If
Next i

End Function
[/vba]
К сообщению приложен файл: 0008028.xlsm(30Kb)
 
Ответить
СообщениеПока мэтры не напинали, поправлю хотя бы самую корявую корявость в коде

[vba]
Код
Public Function SSearch(S1, S2 As String, R As Range, N As Integer) As String
'Ищет N-ю по порядку уникальную ячейку

Dim UnStr() As String
ReDim UnStr(N) As String

M = 0
'Cчетчик уникальных значений

For i = 1 To R.Rows.Count
'Цикл по строкам
    If Trim(R.Cells(i, 1)) = S1 And Trim(R.Cells(i, 2)) = S2 Then
        Found = False
        RC = Trim(R.Cells(i, R.Columns.Count))
        For j = 1 To M
' цикл по массиву уникальных строк
            If RC = UnStr(j) Then
                Found = True
                Exit For
            End If
        Next j
        If Not Found Then
            M = M + 1
            UnStr(M) = RC
            If M = N Then
                SSearch = RC
                Exit Function
            End If
        End If
    End If
Next i

End Function
[/vba]

Автор - abtextime
Дата добавления - 30.06.2016 в 18:34
al1025t01 Дата: Пятница, 08.07.2016, 16:45 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 6
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
abtextime, спасибо тебе. Сейчас подпиливаю документ и столкнулся с некоторыми проблемами. А именно: не работает скрипт отбора уникальных значений для "Дуб", "Дуб проп","Груша проп" на листе "Сортировка", хотя "Ясень" и "Клен" сортирует. Везде все сделано по аналогии с исходным документом. Да, и ещё я столбец "ДхШхТ" перенес в середину таблицы. В формуле диапазон поменял. Где-то я накосячил - не пойму.
К сообщению приложен файл: 0008028-.xlsm(35Kb)
 
Ответить
Сообщениеabtextime, спасибо тебе. Сейчас подпиливаю документ и столкнулся с некоторыми проблемами. А именно: не работает скрипт отбора уникальных значений для "Дуб", "Дуб проп","Груша проп" на листе "Сортировка", хотя "Ясень" и "Клен" сортирует. Везде все сделано по аналогии с исходным документом. Да, и ещё я столбец "ДхШхТ" перенес в середину таблицы. В формуле диапазон поменял. Где-то я накосячил - не пойму.

Автор - al1025t01
Дата добавления - 08.07.2016 в 16:45
Pelena Дата: Пятница, 08.07.2016, 17:50 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 9869
Репутация: 2262 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
спасибо тебе

al1025t01, почитайте на досуге Формы общения на «ты» и на «Вы»


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
Сообщение
спасибо тебе

al1025t01, почитайте на досуге Формы общения на «ты» и на «Вы»

Автор - Pelena
Дата добавления - 08.07.2016 в 17:50
abtextime Дата: Пятница, 08.07.2016, 18:05 | Сообщение № 7
Группа: Проверенные
Ранг: Обитатель
Сообщений: 427
Репутация: 60 ±
Замечаний: 0% ±

Excel 2010
"Дуб", "Дуб проп","Груша проп"


Всё работает. "Дуб" у Вас был на одном листе как "дуб", на другом - как "Дуб", а это разные контексты
К сообщению приложен файл: 2989252.xlsm(36Kb)


Сообщение отредактировал abtextime - Пятница, 08.07.2016, 18:06
 
Ответить
Сообщение
"Дуб", "Дуб проп","Груша проп"


Всё работает. "Дуб" у Вас был на одном листе как "дуб", на другом - как "Дуб", а это разные контексты

Автор - abtextime
Дата добавления - 08.07.2016 в 18:05
_Boroda_ Дата: Понедельник, 11.07.2016, 11:32 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Где-то могу ошибаться-поправьте, пжлст.
Пожалуйста. Вот здесь:
Использование расширенного фильтра не допускается, т.к. он работает только в текущем листе, не работает с результатом формул
Если запускать расширенный фильтр с листа "Данные" и исходный диапазон прописать на Лист1 (например, вот так: Лист1!W1:W9999), то все прекрасно работает. И ему без разницы - формулы там или значения. Диапазон условий можно вообще не заполнять. Скрин см. в файле на листе "Данные".
Все это можно сделать макросом
[vba]
Код
Sub Макрос1()
    Sheets("Лист1").Range("W1:W999").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("C1"), Unique:=True
End Sub
[/vba]

А лист "Сортировка" можно заполнить формулой массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=ИНДЕКС(Лист1!$W$2:$W$37;МИН(ЕСЛИ((Лист1!$C$3:$C$37=ПРОСМОТР(;-КОДСИМВ($A$1:A$1);$A$1:A$1))*(Лист1!$D$3:$D$37=A$2)*(СЧЁТЕСЛИ(A$2:A2;Лист1!$W$3:$W$37)=0);СТРОКА(Лист1!$W$3:$W$37)-1)))&""
К сообщению приложен файл: 8695126_1.xlsb(60Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Где-то могу ошибаться-поправьте, пжлст.
Пожалуйста. Вот здесь:
Использование расширенного фильтра не допускается, т.к. он работает только в текущем листе, не работает с результатом формул
Если запускать расширенный фильтр с листа "Данные" и исходный диапазон прописать на Лист1 (например, вот так: Лист1!W1:W9999), то все прекрасно работает. И ему без разницы - формулы там или значения. Диапазон условий можно вообще не заполнять. Скрин см. в файле на листе "Данные".
Все это можно сделать макросом
[vba]
Код
Sub Макрос1()
    Sheets("Лист1").Range("W1:W999").AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=Range("C1"), Unique:=True
End Sub
[/vba]

А лист "Сортировка" можно заполнить формулой массива (вводится одновременным нажатием Контрл Шифт Ентер)
Код
=ИНДЕКС(Лист1!$W$2:$W$37;МИН(ЕСЛИ((Лист1!$C$3:$C$37=ПРОСМОТР(;-КОДСИМВ($A$1:A$1);$A$1:A$1))*(Лист1!$D$3:$D$37=A$2)*(СЧЁТЕСЛИ(A$2:A2;Лист1!$W$3:$W$37)=0);СТРОКА(Лист1!$W$3:$W$37)-1)))&""

Автор - _Boroda_
Дата добавления - 11.07.2016 в 11:32
Мир MS Excel » Вопросы и решения » Вопросы по Excel » отбор уникальных значений из диапазона в столбец на другом л (Формулы/Formulas)
Страница 1 из 11
Поиск:

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