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

Вход

Регистрация

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

 

= Мир MS Excel/Выбор из массива по условию - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 212»
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Выбор из массива по условию (Макросы/Sub)
Выбор из массива по условию
Bregra1 Дата: Воскресенье, 14.02.2016, 12:43 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Здравствуйте,

Подскажите, пожалуйста, как решить задачу. Есть данные (пример во вложении), нужно данные на листе 1 загнать в массив и из этого массива по условиям на листе 2 вывести значения в столбец G. Например, если в массиве столбец 1 равен условию на втором листе в столбце B и столбец 2 из массива равен условию на листе 2 из столбца С и т. д. то выводится значение в стоблец G. Для первого варианта ответ 100, для второго 170. Значения в столбцах на первом листе могут повторяться, но все пять значений одновременно они уникальны. Спасибо за любой совет.
К сообщению приложен файл: 4676091.xlsx(9Kb)
 
Ответить
СообщениеЗдравствуйте,

Подскажите, пожалуйста, как решить задачу. Есть данные (пример во вложении), нужно данные на листе 1 загнать в массив и из этого массива по условиям на листе 2 вывести значения в столбец G. Например, если в массиве столбец 1 равен условию на втором листе в столбце B и столбец 2 из массива равен условию на листе 2 из столбца С и т. д. то выводится значение в стоблец G. Для первого варианта ответ 100, для второго 170. Значения в столбцах на первом листе могут повторяться, но все пять значений одновременно они уникальны. Спасибо за любой совет.

Автор - Bregra1
Дата добавления - 14.02.2016 в 12:43
Pelena Дата: Воскресенье, 14.02.2016, 12:55 | Сообщение № 2
Группа: Модераторы
Ранг: Экселист
Сообщений: 9879
Репутация: 2265 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
Здравствуйте.
Можно, конечно, формулой массива
Код
=ИНДЕКС(Лист1!$F$2:$F$13;ПОИСКПОЗ(B2&C2&D2&E2&F2;Лист1!$A$2:$A$13&Лист1!$B$2:$B$13&Лист1!$C$2:$C$13&Лист1!$D$2:$D$13&Лист1!$E$2:$E$13;0))

но лучше сделать доп. столбец, быстрее будет считать.
В файле оба варианта
[p.s.]Ой, не увидела, что в разделе VBA. Ну пусть останется[/p.s.]
К сообщению приложен файл: 9393439.xlsx(11Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеЗдравствуйте.
Можно, конечно, формулой массива
Код
=ИНДЕКС(Лист1!$F$2:$F$13;ПОИСКПОЗ(B2&C2&D2&E2&F2;Лист1!$A$2:$A$13&Лист1!$B$2:$B$13&Лист1!$C$2:$C$13&Лист1!$D$2:$D$13&Лист1!$E$2:$E$13;0))

но лучше сделать доп. столбец, быстрее будет считать.
В файле оба варианта
[p.s.]Ой, не увидела, что в разделе VBA. Ну пусть останется[/p.s.]

Автор - Pelena
Дата добавления - 14.02.2016 в 12:55
Bregra1 Дата: Воскресенье, 14.02.2016, 13:03 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Pelena, спасибо за решение. Но хотелось бы реализовать эту задачу через массивы в vba.
 
Ответить
СообщениеPelena, спасибо за решение. Но хотелось бы реализовать эту задачу через массивы в vba.

Автор - Bregra1
Дата добавления - 14.02.2016 в 13:03
Udik Дата: Воскресенье, 14.02.2016, 13:30 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
Для первого варианта ответ 100

неправда Ваша :)
коды символов для Условия1: 65 и 192
Pelena, видать за Вас отредактировала, и в примере привели совпадающие наборы строки 5-6 и последние 4строки совпадают.

а так можно было бы не массивкой
Код
=СУММПРОИЗВ((Лист1!$A$2:$A$13=B2)*(C2=Лист1!$B$2:$B$13)*(D2=Лист1!$C$2:$C$13)*(E2=Лист1!$D$2:$D$13)*(F2=Лист1!$E$2:$E$13)*(Лист1!$F$2:$F$13))


[p.s.]упс, тоже просмотрел, что ВБА, но тогда исправляйте пример
К сообщению приложен файл: 5451936.xlsx(12Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 14.02.2016, 13:33
 
Ответить
Сообщение
Для первого варианта ответ 100

неправда Ваша :)
коды символов для Условия1: 65 и 192
Pelena, видать за Вас отредактировала, и в примере привели совпадающие наборы строки 5-6 и последние 4строки совпадают.

а так можно было бы не массивкой
Код
=СУММПРОИЗВ((Лист1!$A$2:$A$13=B2)*(C2=Лист1!$B$2:$B$13)*(D2=Лист1!$C$2:$C$13)*(E2=Лист1!$D$2:$D$13)*(F2=Лист1!$E$2:$E$13)*(Лист1!$F$2:$F$13))


[p.s.]упс, тоже просмотрел, что ВБА, но тогда исправляйте пример

Автор - Udik
Дата добавления - 14.02.2016 в 13:30
Bregra1 Дата: Воскресенье, 14.02.2016, 13:40 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Udik, это моя ошибка, строчку 6 надо удалить. Все пять значений одновременно они уникальны.
К сообщению приложен файл: 6063897.xlsx(9Kb)
 
Ответить
СообщениеUdik, это моя ошибка, строчку 6 надо удалить. Все пять значений одновременно они уникальны.

Автор - Bregra1
Дата добавления - 14.02.2016 в 13:40
Rioran Дата: Воскресенье, 14.02.2016, 14:07 | Сообщение № 6
Группа: Авторы
Ранг: Ветеран
Сообщений: 886
Репутация: 281 ±
Замечаний: 0% ±

Excel 2013
Bregra1, здравствуйте.

Предлагаю такой вариант, пример во вложении. Даже если будет ошибка уникальности - возьмёт первое попавшееся значение при просмотре строк сверху вниз.

[vba]
Код
Sub Transition()
    Dim A, B, i&, j&, k&
    A = ThisWorkbook.Worksheets(1).Cells(1, 1).CurrentRegion
    B = ThisWorkbook.Worksheets(2).Cells(1, 1).CurrentRegion
    For j = 2 To UBound(B, 1)
        For i = 2 To UBound(A, 1)
            For k = 1 To 5
                If A(i, k) <> B(j, k + 1) Then Exit For
            Next k
            If k = 6 Then
                B(j, k + 1) = A(i, k)
                Exit For
            End If
        Next i
    Next j
    ThisWorkbook.Worksheets(2).Cells(1, 1).CurrentRegion.Value = B
End Sub
[/vba]
К сообщению приложен файл: RioCo-ns.xlsb(18Kb)


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279


Сообщение отредактировал Rioran - Воскресенье, 14.02.2016, 14:08
 
Ответить
СообщениеBregra1, здравствуйте.

Предлагаю такой вариант, пример во вложении. Даже если будет ошибка уникальности - возьмёт первое попавшееся значение при просмотре строк сверху вниз.

[vba]
Код
Sub Transition()
    Dim A, B, i&, j&, k&
    A = ThisWorkbook.Worksheets(1).Cells(1, 1).CurrentRegion
    B = ThisWorkbook.Worksheets(2).Cells(1, 1).CurrentRegion
    For j = 2 To UBound(B, 1)
        For i = 2 To UBound(A, 1)
            For k = 1 To 5
                If A(i, k) <> B(j, k + 1) Then Exit For
            Next k
            If k = 6 Then
                B(j, k + 1) = A(i, k)
                Exit For
            End If
        Next i
    Next j
    ThisWorkbook.Worksheets(2).Cells(1, 1).CurrentRegion.Value = B
End Sub
[/vba]

Автор - Rioran
Дата добавления - 14.02.2016 в 14:07
Bregra1 Дата: Воскресенье, 14.02.2016, 14:57 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Rioran, все отлично работает, спасибо, поясните пожалуйста только, как мне в макросе в условии отбора написать адрес ячейки. Т. е. чтобы первое условие равнялось Сеlls(2,2) или Range("B2"), чтобы второе условие равнялось Сеlls(2,3) или Range("C2"). Мне пока сложновато понимать код макроса. Спасибо.
 
Ответить
СообщениеRioran, все отлично работает, спасибо, поясните пожалуйста только, как мне в макросе в условии отбора написать адрес ячейки. Т. е. чтобы первое условие равнялось Сеlls(2,2) или Range("B2"), чтобы второе условие равнялось Сеlls(2,3) или Range("C2"). Мне пока сложновато понимать код макроса. Спасибо.

Автор - Bregra1
Дата добавления - 14.02.2016 в 14:57
Udik Дата: Воскресенье, 14.02.2016, 15:00 | Сообщение № 8
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
вот сделал с ловлей ошибок, правда диапазоны жёстко прописал :)
[vba]
Код

Option Explicit
Public Sub test()
Dim arr1()
Dim i As Integer, j%, k%, buffRaw%
Dim buffBool As Boolean
Dim buff2 As Byte
Const strL1 = "Лист1"
Const strL2 = "Лист2"

With Worksheets(strL1)
    arr1 = .Range("A2:F13").Value
End With
With Worksheets(strL2)
    For i = 2 To 4
        buff2 = 0
        For j = 1 To UBound(arr1)
            buffBool = True
            For k = 1 To 5
             buffBool = (buffBool And arr1(j, k) = .Cells(i, k + 1).Value)
            Next k
            If buffBool Then
                buff2 = buff2 + 1
                buffRaw = j
            End If
        Next j
        Select Case buff2
          Case 0
            .Cells(i, 7).Value = "Not Find"
          Case 1
            .Cells(i, 7).Value = arr1(buffRaw, 6)

          Case Else
            .Cells(i, 7).Value = "doubles"

        End Select
    Next i
End With
[/vba]
К сообщению приложен файл: 1577999.xlsb(18Kb)


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com


Сообщение отредактировал Udik - Воскресенье, 14.02.2016, 15:10
 
Ответить
Сообщениевот сделал с ловлей ошибок, правда диапазоны жёстко прописал :)
[vba]
Код

Option Explicit
Public Sub test()
Dim arr1()
Dim i As Integer, j%, k%, buffRaw%
Dim buffBool As Boolean
Dim buff2 As Byte
Const strL1 = "Лист1"
Const strL2 = "Лист2"

With Worksheets(strL1)
    arr1 = .Range("A2:F13").Value
End With
With Worksheets(strL2)
    For i = 2 To 4
        buff2 = 0
        For j = 1 To UBound(arr1)
            buffBool = True
            For k = 1 To 5
             buffBool = (buffBool And arr1(j, k) = .Cells(i, k + 1).Value)
            Next k
            If buffBool Then
                buff2 = buff2 + 1
                buffRaw = j
            End If
        Next j
        Select Case buff2
          Case 0
            .Cells(i, 7).Value = "Not Find"
          Case 1
            .Cells(i, 7).Value = arr1(buffRaw, 6)

          Case Else
            .Cells(i, 7).Value = "doubles"

        End Select
    Next i
End With
[/vba]

Автор - Udik
Дата добавления - 14.02.2016 в 15:00
Bregra1 Дата: Воскресенье, 14.02.2016, 15:04 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Хотел бы уточнить, условия отбора будут не подряд, а например первое условие в ячейки A1, второе условие в ячейки U15, третье условие в ячейке D8
 
Ответить
СообщениеХотел бы уточнить, условия отбора будут не подряд, а например первое условие в ячейки A1, второе условие в ячейки U15, третье условие в ячейке D8

Автор - Bregra1
Дата добавления - 14.02.2016 в 15:04
Udik Дата: Воскресенье, 14.02.2016, 15:09 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
тогда это другие условия задачи.
За такое надо 10 лет расстрела :D


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщениетогда это другие условия задачи.
За такое надо 10 лет расстрела :D

Автор - Udik
Дата добавления - 14.02.2016 в 15:09
Bregra1 Дата: Воскресенье, 14.02.2016, 15:17 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Udik, извините) Думал, что в коде будут прописаны адреса и я смогу адреса условий поменять на свои. Мне казалось, что в макросе будут прописаны условия отбора для каждого столбца массива) Но сейчас поразбираюсь, может получиться ваш код как то преобразовать. Спасибо за помочь.
 
Ответить
СообщениеUdik, извините) Думал, что в коде будут прописаны адреса и я смогу адреса условий поменять на свои. Мне казалось, что в макросе будут прописаны условия отбора для каждого столбца массива) Но сейчас поразбираюсь, может получиться ваш код как то преобразовать. Спасибо за помочь.

Автор - Bregra1
Дата добавления - 14.02.2016 в 15:17
Rioran Дата: Воскресенье, 14.02.2016, 15:32 | Сообщение № 12
Группа: Авторы
Ранг: Ветеран
Сообщений: 886
Репутация: 281 ±
Замечаний: 0% ±

Excel 2013
Bregra1, зачастую структура исходных данных значительно влияет на способ решения. В моём варианте был расчёт, что все условия идут подряд друг за другом. Если же условия могут находится в разнесённых друг от друга столбцах, то можно либо их сгруппировать в одном месте для применения моего кода, либо ввести дополнительный массив-указатель, определяющий номера столбцов с условиями.

Если в коде что-то не понятно - готов пояснить.

Поддерживаю товарища Udik'а, форумчанам будет гораздо удобнее отвечать на Ваши вопросы, если они будут чётко сформулированы, максимально близко к реальной задаче.


Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279
 
Ответить
СообщениеBregra1, зачастую структура исходных данных значительно влияет на способ решения. В моём варианте был расчёт, что все условия идут подряд друг за другом. Если же условия могут находится в разнесённых друг от друга столбцах, то можно либо их сгруппировать в одном месте для применения моего кода, либо ввести дополнительный массив-указатель, определяющий номера столбцов с условиями.

Если в коде что-то не понятно - готов пояснить.

Поддерживаю товарища Udik'а, форумчанам будет гораздо удобнее отвечать на Ваши вопросы, если они будут чётко сформулированы, максимально близко к реальной задаче.

Автор - Rioran
Дата добавления - 14.02.2016 в 15:32
Bregra1 Дата: Воскресенье, 14.02.2016, 15:46 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
Неправильно поставил задачу,извините. А можно ли написать код в котором в массив записываются данные с листа 1 и после значения выбираются по условию, например если объединенные значения 5 столбцов массива равны значению a1&s10&b4&f1&b1 или чтобы циклом каждый столбец массива сравнивался с определенной ячейкой. Спасибо
 
Ответить
СообщениеНеправильно поставил задачу,извините. А можно ли написать код в котором в массив записываются данные с листа 1 и после значения выбираются по условию, например если объединенные значения 5 столбцов массива равны значению a1&s10&b4&f1&b1 или чтобы циклом каждый столбец массива сравнивался с определенной ячейкой. Спасибо

Автор - Bregra1
Дата добавления - 14.02.2016 в 15:46
Udik Дата: Воскресенье, 14.02.2016, 16:28 | Сообщение № 14
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
У меня сейчас такой алгоритм: считываем в массив основную табличку, потом каждую строку проверяемой сравниваем с каждой "строкой" массива.
Непонятно что Вы хотите поменять.


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
СообщениеУ меня сейчас такой алгоритм: считываем в массив основную табличку, потом каждую строку проверяемой сравниваем с каждой "строкой" массива.
Непонятно что Вы хотите поменять.

Автор - Udik
Дата добавления - 14.02.2016 в 16:28
Bregra1 Дата: Воскресенье, 14.02.2016, 16:49 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
А что значит это выражение buffBool And arr1(j, k) это как раз выбор строки в массиве?
 
Ответить
СообщениеА что значит это выражение buffBool And arr1(j, k) это как раз выбор строки в массиве?

Автор - Bregra1
Дата добавления - 14.02.2016 в 16:49
Bregra1 Дата: Воскресенье, 14.02.2016, 16:51 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 10
Репутация: 0 ±
Замечаний: 0% ±

Excel 2007
И почему когда вы объявляет переменные ставите %,например j%?
 
Ответить
СообщениеИ почему когда вы объявляет переменные ставите %,например j%?

Автор - Bregra1
Дата добавления - 14.02.2016 в 16:51
StoTisteg Дата: Воскресенье, 14.02.2016, 16:58 | Сообщение № 17
Группа: Авторы
Ранг: Ветеран
Сообщений: 541
Репутация: 45 ±
Замечаний: 0% ±

Excel 2010
buffBool And arr1(j, k) — это произвольно выдранный вами из кода набор символов, а
buffBool And arr1(j, k) = .Cells(i, k + 1).Value
проверка того, что предыдущий массив совпадает с текущим.


Проверь всё. ThisWorkbook.Save. On Error Resume Next.
 
Ответить
СообщениеbuffBool And arr1(j, k) — это произвольно выдранный вами из кода набор символов, а
buffBool And arr1(j, k) = .Cells(i, k + 1).Value
проверка того, что предыдущий массив совпадает с текущим.

Автор - StoTisteg
Дата добавления - 14.02.2016 в 16:58
StoTisteg Дата: Воскресенье, 14.02.2016, 16:59 | Сообщение № 18
Группа: Авторы
Ранг: Ветеран
Сообщений: 541
Репутация: 45 ±
Замечаний: 0% ±

Excel 2010
И почему когда вы объявляет переменные ставите %,например j%?

Потому он так хочет. Не нравится — пишите свой код :p


Проверь всё. ThisWorkbook.Save. On Error Resume Next.
 
Ответить
Сообщение
И почему когда вы объявляет переменные ставите %,например j%?

Потому он так хочет. Не нравится — пишите свой код :p

Автор - StoTisteg
Дата добавления - 14.02.2016 в 16:59
Udik Дата: Воскресенье, 14.02.2016, 16:59 | Сообщение № 19
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
строка
[vba]
Код

buffBool = (buffBool And arr1(j, k) = .Cells(i, k + 1).Value)
[/vba]
запоминает результат логического умножения buffBool и результата сравнения очередного столбца листа и очередного "столбца" массива. Это чтобы ручками не писать пять проверок в одном операторе, засунуто в цикл. В принципе при первом False можно выходить из цикла, но это уже оптимизация кода.


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщениестрока
[vba]
Код

buffBool = (buffBool And arr1(j, k) = .Cells(i, k + 1).Value)
[/vba]
запоминает результат логического умножения buffBool и результата сравнения очередного столбца листа и очередного "столбца" массива. Это чтобы ручками не писать пять проверок в одном операторе, засунуто в цикл. В принципе при первом False можно выходить из цикла, но это уже оптимизация кода.

Автор - Udik
Дата добавления - 14.02.2016 в 16:59
Udik Дата: Воскресенье, 14.02.2016, 17:04 | Сообщение № 20
Группа: Друзья
Ранг: Старожил
Сообщений: 1219
Репутация: 153 ±
Замечаний: 0% ±

Excel 2013
ставите %,например j%?

чтобы не писать каждый раз Integer. ВБА желает чтобы каждой переменной тип прописывали персонально, иначе он ей Variant прилепит.


вот вам барабан
яд 41001231307558 wm R419131876897
udik1968@gmail.com
 
Ответить
Сообщение
ставите %,например j%?

чтобы не писать каждый раз Integer. ВБА желает чтобы каждой переменной тип прописывали персонально, иначе он ей Variant прилепит.

Автор - Udik
Дата добавления - 14.02.2016 в 17:04
Мир MS Excel » Вопросы и решения » Вопросы по VBA » Выбор из массива по условию (Макросы/Sub)
Страница 1 из 212»
Поиск:

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