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

Вход

Регистрация

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

 

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

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

Excel 2013
Добрый день Уважаемые форумчане.
Пытаюсь два дня найти решение в нэте, но пока никак...
Своих знаний не хватает, по этому обращаюсь к Вам.
Есть таблица с исходными данными (вкладка Invoices), нужно на отдельном листе (Data) при выборе одной из позиций из списка (ячейка B1), что бы в колонку "С" он вертикально выводил список всех строчек с этой позиции из списка исходных данных, рядом подставлял все значения, находящиеся справа от этих позиций.

пример того, как это должно быть, показан на вкладке Example (тока там без форму, естественно... :) )

заранее Огромное СПАСИБО!
К сообщению приложен файл: SHIPMENTS.xlsx(25.9 Kb)
 
Ответить
СообщениеДобрый день Уважаемые форумчане.
Пытаюсь два дня найти решение в нэте, но пока никак...
Своих знаний не хватает, по этому обращаюсь к Вам.
Есть таблица с исходными данными (вкладка Invoices), нужно на отдельном листе (Data) при выборе одной из позиций из списка (ячейка B1), что бы в колонку "С" он вертикально выводил список всех строчек с этой позиции из списка исходных данных, рядом подставлял все значения, находящиеся справа от этих позиций.

пример того, как это должно быть, показан на вкладке Example (тока там без форму, естественно... :) )

заранее Огромное СПАСИБО!

Автор - Alexushu
Дата добавления - 06.06.2018 в 11:00
pabchek Дата: Среда, 06.06.2018, 11:09 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 899
Репутация: 215 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте!
Можно, конечно, большими формулами (лень писать). Может такой вариант подойдет, с модифицированной сводной
К сообщению приложен файл: _SHIPMENTS.xlsx(29.2 Kb)


"Учиться, учиться и еще раз учиться!"
WM: R399923528092
 
Ответить
СообщениеЗдравствуйте!
Можно, конечно, большими формулами (лень писать). Может такой вариант подойдет, с модифицированной сводной

Автор - pabchek
Дата добавления - 06.06.2018 в 11:09
китин Дата: Среда, 06.06.2018, 11:19 | Сообщение № 3
Группа: Модераторы
Ранг: Участник клуба
Сообщений: 5071
Репутация: 807 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
или формулой массива
Код
=ЕСЛИОШИБКА(ИНДЕКС(Invoices!$B$2:$B$36;НАИМЕНЬШИЙ(ЕСЛИ(Data!$B$1=Invoices!$A$2:$A$36;СТРОКА(Invoices!$A$2:$A$36)-1);СТРОКА(A1)));"")
К сообщению приложен файл: Alexushu.xlsx(30.5 Kb)


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538
 
Ответить
Сообщениеили формулой массива
Код
=ЕСЛИОШИБКА(ИНДЕКС(Invoices!$B$2:$B$36;НАИМЕНЬШИЙ(ЕСЛИ(Data!$B$1=Invoices!$A$2:$A$36;СТРОКА(Invoices!$A$2:$A$36)-1);СТРОКА(A1)));"")

Автор - китин
Дата добавления - 06.06.2018 в 11:19
Alexushu Дата: Среда, 06.06.2018, 11:20 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
pabchek, Спасибо большое, но такой вариант я уже рассматривал и он, к сожалению, не подойдет для моей задачи (((
Дело в том, что эти полученные данные далее используются другой программой, и она не умеет работать, если сверху от самих данных есть какие либо заголовки, фильтры и т.п.
Ей нужно, что бы с самой первой строчки шли именно данные.
 
Ответить
Сообщениеpabchek, Спасибо большое, но такой вариант я уже рассматривал и он, к сожалению, не подойдет для моей задачи (((
Дело в том, что эти полученные данные далее используются другой программой, и она не умеет работать, если сверху от самих данных есть какие либо заголовки, фильтры и т.п.
Ей нужно, что бы с самой первой строчки шли именно данные.

Автор - Alexushu
Дата добавления - 06.06.2018 в 11:20
Alexushu Дата: Среда, 06.06.2018, 11:25 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
китин, СПАСИБО!!!! Именно то, что было нужно!
 
Ответить
Сообщениекитин, СПАСИБО!!!! Именно то, что было нужно!

Автор - Alexushu
Дата добавления - 06.06.2018 в 11:25
_Boroda_ Дата: Среда, 06.06.2018, 11:38 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 13194
Репутация: 5425 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Alexushu, если значения в исходнике по порядку, то можно обычной формулой
Код
=ЕСЛИ(СТРОКА()>СЧЁТЕСЛИ(Invoices!$A:$A;$B$1);"";ИНДЕКС(Invoices!A:A;ПОИСКПОЗ($B$1;Invoices!$A$1:$A$999;)-1+СТРОКА()))


И еще выпадающий список Вам нормальный сделал )см. имена - Контрл F3)
Файл перевложил
К сообщению приложен файл: SHIPMENTS_2.xlsx(27.2 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995


Сообщение отредактировал _Boroda_ - Среда, 06.06.2018, 11:41
 
Ответить
СообщениеAlexushu, если значения в исходнике по порядку, то можно обычной формулой
Код
=ЕСЛИ(СТРОКА()>СЧЁТЕСЛИ(Invoices!$A:$A;$B$1);"";ИНДЕКС(Invoices!A:A;ПОИСКПОЗ($B$1;Invoices!$A$1:$A$999;)-1+СТРОКА()))


И еще выпадающий список Вам нормальный сделал )см. имена - Контрл F3)
Файл перевложил

Автор - _Boroda_
Дата добавления - 06.06.2018 в 11:38
boa Дата: Среда, 06.06.2018, 11:45 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 167
Репутация: 27 ±
Замечаний: 0% ±

2013, 365
Alexushu,
из персональной коллекции :)
Макрос ищет все вхождения одного и более искомых значений
[vba]
Код

Option Explicit

Sub SearchByList()
'' Author:  boa
'' Written: 20.10.2017
'' Edited:
'  Description: Берет данные из заданного диапазона искомых значений(Словаря) и сравнивает их со списком значений,
'               если находит совпадения, то переносит все уникальные значения из заданного столбца
'               и сопоставленное ему значение из Словаря в новую книгу.
    
    Dim MyList As Range         'Список искомых значений
    Dim MyRange As Range        'Диапазон для поиска
    Dim SearchColumn As Integer 'колонка в которой ищем совпадения
    Dim ZnachColumn As Integer  'колонка из которой нужно вывести значения
    Dim iRow&, V$, Znach As Variant
    Dim strCaption$, strLabel$
    
    On Error GoTo Proverka
    strCaption = "Поиск уникальных значений по списку"
    strLabel = "Введите ссылку на список значений которые надо найти(Словарь)." & vbCrLf & _
               "Будут учитываться только видимы значения из выбранного диапазона."
    Set MyList = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=8)
    strLabel = "Введите ссылку на диапазон содержащий искомые значения и колонку для сопоставления со Словарем."
    Set MyRange = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=8)
    If Not MyRange Is Nothing Then SearchColumn = MyRange.Columns.Count
    strLabel = "Введите номер колонки от 1 до " & SearchColumn & " в выбранном диапазоне, по которой должен быть произведен поиск значений из Словаря."
    SearchColumn = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Default:=SearchColumn, Type:=1)
    
    strLabel = "Введите номер колонки в массиве из которой надо вывести найденный результат." & vbCrLf & _
               "Если номер колонки не вводить(нажать ""Отмена""), то в результат будет выведена вся строка из выделенного диапазона."
    ZnachColumn = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=1)
Proverka:
    If MyList Is Nothing Or MyRange Is Nothing Or SearchColumn < 1 Then _
        MsgBox "Не введены все обязательные параметры для поиска значений.", vbCritical, "": Exit Sub
    Dim MeTime As Date, Start!, sMsg$
    MeTime = Time
    Start! = Timer
    
    Dim i&, a As Range, DicSearch As Object, Dic As Object
    
    Set DicSearch = CreateObject("Scripting.Dictionary")
    Set Dic = CreateObject("Scripting.Dictionary")
On Error Resume Next
    For Each a In MyList    'Список искомых значений
      If a.Rows.Hidden = False Then DicSearch.Add CStr(a.Value), a.Value
    Next a
    
    For i = 1 To MyRange.Rows.Count 'Список найденных значений
      If DicSearch.Exists(CStr(MyRange.Cells(i, SearchColumn).Value)) Then
        If ZnachColumn > 0 Then V = CStr(MyRange.Cells(i, ZnachColumn).Value) Else V = i
        Dic.Add V, CStr(MyRange.Cells(i, SearchColumn).Value)
      End If
    Next i
    
    With Workbooks.Add(xlWBATWorksheet).Worksheets(1)   'вывод результатов
        .Cells(1, 1).Value = "Значения из списка"
        .Cells(1, 2).Value = "Найденные значение " & Dic.Count
        iRow = 2
        If ZnachColumn > 0 Then
            .Range(.Cells(iRow, 1), .Cells(Dic.Count + 1, 2)).Value = Application.Transpose(Array(Dic.Items, Dic.Keys))
        Else
            .Range(.Cells(iRow, 1), .Cells(Dic.Count + 1, 1)).Value = Application.Transpose(Array(Dic.Items))
            For Each Znach In Dic
                .Range(.Cells(iRow, 2), .Cells(iRow, MyRange.Columns.Count + 1)).Value = MyRange.Rows(Znach).Value
                iRow = iRow + 1
            Next
        End If
        .UsedRange.EntireColumn.AutoFit
    End With
    
    sMsg = "Для заданного списка найдено " & Dic.Count & " значений." & vbCrLf & _
           "Затрачено " & Format(Timer - Start, "0.00") & " сек."
    Debug.Print "Затрачено: " & Timer - Start
    MsgBox sMsg, vbInformation
End Sub
[/vba]

при необходимости подкорректируете под свои нужды
К сообщению приложен файл: SHIPMENTS.xlsb(27.9 Kb)


 
Ответить
СообщениеAlexushu,
из персональной коллекции :)
Макрос ищет все вхождения одного и более искомых значений
[vba]
Код

Option Explicit

Sub SearchByList()
'' Author:  boa
'' Written: 20.10.2017
'' Edited:
'  Description: Берет данные из заданного диапазона искомых значений(Словаря) и сравнивает их со списком значений,
'               если находит совпадения, то переносит все уникальные значения из заданного столбца
'               и сопоставленное ему значение из Словаря в новую книгу.
    
    Dim MyList As Range         'Список искомых значений
    Dim MyRange As Range        'Диапазон для поиска
    Dim SearchColumn As Integer 'колонка в которой ищем совпадения
    Dim ZnachColumn As Integer  'колонка из которой нужно вывести значения
    Dim iRow&, V$, Znach As Variant
    Dim strCaption$, strLabel$
    
    On Error GoTo Proverka
    strCaption = "Поиск уникальных значений по списку"
    strLabel = "Введите ссылку на список значений которые надо найти(Словарь)." & vbCrLf & _
               "Будут учитываться только видимы значения из выбранного диапазона."
    Set MyList = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=8)
    strLabel = "Введите ссылку на диапазон содержащий искомые значения и колонку для сопоставления со Словарем."
    Set MyRange = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=8)
    If Not MyRange Is Nothing Then SearchColumn = MyRange.Columns.Count
    strLabel = "Введите номер колонки от 1 до " & SearchColumn & " в выбранном диапазоне, по которой должен быть произведен поиск значений из Словаря."
    SearchColumn = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Default:=SearchColumn, Type:=1)
    
    strLabel = "Введите номер колонки в массиве из которой надо вывести найденный результат." & vbCrLf & _
               "Если номер колонки не вводить(нажать ""Отмена""), то в результат будет выведена вся строка из выделенного диапазона."
    ZnachColumn = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=1)
Proverka:
    If MyList Is Nothing Or MyRange Is Nothing Or SearchColumn < 1 Then _
        MsgBox "Не введены все обязательные параметры для поиска значений.", vbCritical, "": Exit Sub
    Dim MeTime As Date, Start!, sMsg$
    MeTime = Time
    Start! = Timer
    
    Dim i&, a As Range, DicSearch As Object, Dic As Object
    
    Set DicSearch = CreateObject("Scripting.Dictionary")
    Set Dic = CreateObject("Scripting.Dictionary")
On Error Resume Next
    For Each a In MyList    'Список искомых значений
      If a.Rows.Hidden = False Then DicSearch.Add CStr(a.Value), a.Value
    Next a
    
    For i = 1 To MyRange.Rows.Count 'Список найденных значений
      If DicSearch.Exists(CStr(MyRange.Cells(i, SearchColumn).Value)) Then
        If ZnachColumn > 0 Then V = CStr(MyRange.Cells(i, ZnachColumn).Value) Else V = i
        Dic.Add V, CStr(MyRange.Cells(i, SearchColumn).Value)
      End If
    Next i
    
    With Workbooks.Add(xlWBATWorksheet).Worksheets(1)   'вывод результатов
        .Cells(1, 1).Value = "Значения из списка"
        .Cells(1, 2).Value = "Найденные значение " & Dic.Count
        iRow = 2
        If ZnachColumn > 0 Then
            .Range(.Cells(iRow, 1), .Cells(Dic.Count + 1, 2)).Value = Application.Transpose(Array(Dic.Items, Dic.Keys))
        Else
            .Range(.Cells(iRow, 1), .Cells(Dic.Count + 1, 1)).Value = Application.Transpose(Array(Dic.Items))
            For Each Znach In Dic
                .Range(.Cells(iRow, 2), .Cells(iRow, MyRange.Columns.Count + 1)).Value = MyRange.Rows(Znach).Value
                iRow = iRow + 1
            Next
        End If
        .UsedRange.EntireColumn.AutoFit
    End With
    
    sMsg = "Для заданного списка найдено " & Dic.Count & " значений." & vbCrLf & _
           "Затрачено " & Format(Timer - Start, "0.00") & " сек."
    Debug.Print "Затрачено: " & Timer - Start
    MsgBox sMsg, vbInformation
End Sub
[/vba]

при необходимости подкорректируете под свои нужды

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

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