Форум программистов, компьютерный форум, киберфорум
VBA
Войти
Регистрация
Восстановить пароль
 
 
Рейтинг 4.69/13: Рейтинг темы: голосов - 13, средняя оценка - 4.69
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
1

Нахождение и подстановка числа из других вкладок по нескольким условиям

18.07.2015, 15:10. Показов 2550. Ответов 53
Метки нет (Все метки)

Привет всем. Помогите пожалуйста сделать задачу.
В задаче будут задействованы столбцы E и S. выделено зелёным цветом
Во вкладках с 1 по 17 такие же столбцы Е и S
Нужно сравнивать значания(текст) в столбце Е со значением в столбце Е во всех других вкладках
Нужно сравнивать значания(числа) в столбце S со значением в столбце S во всех других вкладках
Если эти 2 условия выполнены, то в столбец V вводить значение, которое указано во вкладках напротив(в той же строке)
привожу пример:
Е22 имеет значение THUN - BASEL. Ищем его в других вкладках. Нашлось во вкладке 2 E15
смотрим совпадает ли второе условие. S22 =S22 dj второй вкладке. Значит подставляем значение V15 из второй вкладки в V22 лист1. на данный момент это 0

Заранее большое спасибо!
Файл прикрепил. там всё более понятно:
0

Помощь в написании контрольных, курсовых и дипломных работ здесь.

Вложения
Тип файла: rar Пример2.rar (1.67 Мб, 5 просмотров)
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
18.07.2015, 15:10
Ответы с готовыми решениями:

Нахождение уникальных значений из таблицы по нескольким условиям
Добрый день, задался вопросом о нахождение уникальных значений из таблицы по нескольким условиям. ...

поиск и подстановка значения по заданным условиям
ВКЛАДКА СВОД. В НЕЙ ЕСТЬ ЯЧЕЙКА полуфабрикат, В КОТОРУЮ НУЖНО ПОДТЯГИВАТЬ ИТОГОВОЕ ЗНАЧЕНИЕ ИЗ...

Суммирование по нескольким условиям
Помогите пожалуйста, необходимо просуммировать данные по нескольким критериям (данные во вложении)

Поиск по нескольким условиям
Добрый день! С SQL начинаю только разбираться. Опыт есть, но только с запросами. Глобально никогда...

53
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
18.07.2015, 22:04  [ТС] 2
Ну, где вы, волшебники exel?
0
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
19.07.2015, 15:53  [ТС] 3
Вот немного изменил задачу. может эта полегче будет.
Файл прикрепил:
Задание выделил жёлтым
0
Вложения
Тип файла: rar пример5.rar (1.41 Мб, 3 просмотров)
1990 / 1161 / 444
Регистрация: 20.12.2014
Сообщений: 3,087
20.07.2015, 00:44 4
Макрос называется poisk(). Расположен в модуле 6. Если в листах есть повторяющиеся значения (в вашем примере, если THUN - BASEL будет не только во вкладке 2 E15, но и еще где-то), то на листе 1 будет вставлено последнее из них.
1
Вложения
Тип файла: rar Пример2а.rar (1.67 Мб, 3 просмотров)
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 00:49  [ТС] 5
chumich, последнее не надо. нужно чтобы подходило по ДВУМ условиям!
0
1990 / 1161 / 444
Регистрация: 20.12.2014
Сообщений: 3,087
20.07.2015, 00:57 6
Да, про второе я как-то забыл . Вот вариант с прохождением по двум условиям. Но там далеко не всё проходит по двум условиям. В этом случае на листе 1 в столбце V будет "-".
1
Вложения
Тип файла: rar Пример2б.rar (1.67 Мб, 3 просмотров)
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 01:04  [ТС] 7
chumich, а зачем мне "-". мне нужно по двум,а о и позже 3 условиям. Спасибо конечно, но не подойдёт. Мне нудно кучу файлов обработать таким образом. Вы второй вариант задания смотрели? может там полегче для вас будет?
0
1990 / 1161 / 444
Регистрация: 20.12.2014
Сообщений: 3,087
20.07.2015, 01:13 8
Цитата Сообщение от Oleg395 Посмотреть сообщение
а зачем мне "-". мне нужно по двум,а о и позже 3 условиям
"-" просто заполняет пустую клетку листа 1 там, где нет результата, удовлетворяющего вашим двум условиям. Можно, конечно и пустую оставить. Ничего сложного в вашем задании для меня нет , главное, чтобы у вас не было сложностей в понимании того, что вам сделали.
1
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 01:16  [ТС] 9
сделайте пожалуйста по двум условиям. чтоб цифры нужные находились. ну вы поняли. тем олее если для вас нет сложного. это для меня не посильная задача....
0
1990 / 1161 / 444
Регистрация: 20.12.2014
Сообщений: 3,087
20.07.2015, 01:26 10
Цитата Сообщение от Oleg395 Посмотреть сообщение
сделайте пожалуйста по двум условиям
Я уже сделал вам по двум условиям. Пример 2б во вложении. Макрос находит и вносит в нужные клетки все данные, которые этим условиям удовлетворяют. Но у вас есть некоторые строки, для которых нет данных, удовлетворяющих одновременно этим двум условиям. Поэтому я сделал так, чтобы в этих клетках проставлялся "-". Если хотите, их можно оставить пустыми. Вы файл-то смотрели? Откройте, очистите столбец V на листе 1, зайдите в VB и запустите макрос в модуле 6. Тогда увидите, что всё работает и заполняется. Примерно за минуту.
1
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 01:50  [ТС] 11
chumich, вы получали сообщения в личку от меня? я вам написал 2 или 3 письма
0
1990 / 1161 / 444
Регистрация: 20.12.2014
Сообщений: 3,087
20.07.2015, 02:18 12

Не по теме:

Цитата Сообщение от Oleg395 Посмотреть сообщение
вы получали сообщения в личку от меня?
Всё, что вы пишете на форуме, всегда приходит по адресу.



Добавлено через 20 минут
Комментарии к коду:
Кликните здесь для просмотра всего текста
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
LastRow = Лист1.Cells(Лист1.Rows.Count, 5).End(xlUp).Row 'поиск последней строки на листе 1
ReDim mass(LastRow - 14, 3) 'объявление массива для данных трех столбцов листа 1
'массивы используются для ускорения работы программы, без них программа работает около 10 минут
For i1 = 15 To LastRow 'цикл для заполнения массива данными
    mass(i1 - 15, 0) = Лист1.Cells(i1, 5) 'заполнение массива данными столбца E
    mass(i1 - 15, 1) = Лист1.Cells(i1, 19) 'заполнение массива данными столбца S
    mass(i1 - 15, 2) = Лист1.Cells(i1, 22) 'заполнение массива данными столбца V
Next i1
 
ReDim arr(16) 'объявление массива массивов для данных трех столбцов других листов
For j2 = 1 To 17 'цикл делающий перебор листов
    Worksheets("" & j2 & "").Select 'активация листа с определенным именем
    EndRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 5).End(xlUp).Row 'поиск последней строки на этом листе
    ReDim listArr(EndRow, 3) 'объявление массива для данных трех столбцов этого листа
    arr(j2 - 1) = listArr 'передача данных массива листа в массив массивов
    For k2 = 15 To EndRow 'цикл для заполнения массивов данных трех столбцов каждого листа
        arr(j2 - 1)(k2 - 15, 0) = ActiveSheet.Cells(k2, 5) 'заполнение массива данными столбца E
        arr(j2 - 1)(k2 - 15, 1) = ActiveSheet.Cells(k2, 19) 'заполнение массива данными столбца S
        arr(j2 - 1)(k2 - 15, 2) = ActiveSheet.Cells(k2, 22) 'заполнение массива данными столбца V
    Next k2
Next j2
 
For i = 15 To LastRow 'цикл для перебора строк листа 1
    For j = 1 To 17 'цикл для перебора листов 1 - 17
        For k = 0 To UBound(arr(j - 1)) 'цикл для перебора элементов массивов листов 1-17
            If mass(i - 15, 0) = arr(j - 1)(k, 0) And mass(i - 15, 1) = arr(j - 1)(k, 1) Then mass(i - 15, 2) = arr(j - 1)(k, 2) 'проверка соответствия двум условиям одновременно
            If mass(i - 15, 0) = arr(j - 1)(k, 0) And mass(i - 15, 1) <> arr(j - 1)(k, 1) Then mass(i - 15, 2) = "-" 'заполнение ячеек не удовлетворяющих условиям
        Next k
    Next j
Next i
 
For i2 = 15 To LastRow 'цикл для заполнения строк столбца V листа 1
    Лист1.Cells(i2, 22) = mass(i2 - 15, 2) 'заполнение столбца V
Next i2
2
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 11:36  [ТС] 13
chumich, спасибо! но прошу прощения. для меня и такое объяснение ничего не объяснило. В общем ничего не понимаю. Вам наверно кажется , что мне должно быть понятно. т.к. для вас это "элементарно Ватсон", но это для вас элементарно, а для таких как я всякие макросы или другие неизвестности созданные кем то полная темнота в понимании.
Я ждал другого объяснения. Объяснения каждого слова, выражения, каждого числа в строке.
У меня куча вопросов возникло, принципе как и были вопросы такие и остались.
Например, что означают эти числа 16, 17, 15. Что означает буквы j2, k2, k и все остальные. Что такое arr, reDim, Ubound, и куча остальных терминов.
Ведь чтобы разобраться в макросе, нужно знать что означает каждая цифорка, буковка, слово или выражение.
Поймите меня правильно. Я не хочу вас загрузить. Но если для вас это легко, объясните пожалуйста с полным разжёвыванием, представив, что вы взрослый, а перед вами 2 летний ребёнок, который поймёт только тогда когда ему разжевать абсолютно всё.

ПС/ мне надо будет изменять потом макрос, меняя столбцы, количество строк, вкладок и другое, чтобы аналогично подстраивать для своих нужд. А сделать я это сейчас не могу, т.к. нахожусь в ступоре. Я бы мог вам выслать свой файл, в котором нужно выполнить все эти действия макросов. Вы бы сделали. Но я же не могу высылать все свои файлы, и чтобы вы делали. Сам хочу научиться и уже изходя из этого сам делать, благодаря вашему макросу и полному его пониманию.

Добавлено через 9 минут
chumich,

ПС/ мне надо изменить сейчас макрос, т.к. задание немного изменилось (изменились столбцы и их количество, количество строк, вкладок и другое_) чтобы аналогично подстраивать для своих нужд. А сделать я это сейчас не могу, т.к. нахожусь в ступоре. Я бы мог вам выслать свой файл, в котором нужно выполнить все эти действия макросов. Вы бы сделали. Но я же не могу высылать все свои файлы, и чтобы вы делали. Да и не культурно это будет выглядеть. Сам хочу научиться и уже изходя из этого сам делать, благодаря вашему макросу и полному его пониманию.
Ну честно говорю. Уже несколько раз пересматривал макрос, и сейчас смотрю. И картина такая: Смотрю, "как баран на новые ворота"
0
5694 / 2979 / 619
Регистрация: 23.11.2010
Сообщений: 9,923
20.07.2015, 12:04 14
Учебники, справочники, самоучители
1
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 12:15  [ТС] 15
Fairuza, самый простой способ послать на три буквы. Но я обратился не к вам на счёт объяснения макроса, а к chumich, как к разработчику этого макроса. И вообще я считаю. намного эфективней на практике всё делать. И объяснение макроса, реального, даст намного больше понимания. чем чтение кучи литературы. К примеру лет 8 назад. я хотел освоить фотошоп, читал всякие книги, но эффект был минимальным, пока не просмотрел видео не помню как звать его, Попова и его уроки фотошоп с нуля в видеоформате. И всё стало понятно. А книги это мало эфективно для быстрого обучения. они годятся только уже для подготовленных в той или иной сфере, чтобы уже быстро схватывать нужные знания. А я буду с этими книгами месяцы проводить. Считаю, что такой формат общения как на форуме, когда задают вопрос, а тебе помогают конкретную задачу сделать, самое эффективное обучение. 1 раз помогли. потом уже сам знаешь как делать.
0
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 12:55  [ТС] 16
chumich, сразу говорю макрос не верно работает. 1. он работает, только по 1 условию. Если есть совпадения, он берёт значение у 1 -го попавшегося, не сравнивая по второму условию. 2. макрос возможно и вообще не правильно работает. т.к. даже если нет одинаковых значений в столбце E(которые могут попадаться в вкладках 1-17. во вкладках 1-17 могут одинаковые значения в столбце E, вот поэтому нужно и второе условие!), то макрос берёт и подставляет в столбец V цифру как бы "с потолка"
Вы сами смотрели хоть результат макроса? в ручную посмотрите результат несколько ячеек в столбце V и вы увидите неточности! Хорошо я подскажу эти неточности которые я увидел сразу и таких неточностей там целая куча, т.к. макрос работает неверно:
прикрепил файл с неточностями
ПС. на данный момент немного исправил задачу. нужно выводить значения в стоkбец Y из стоkбцов Y(f не как ранее из V в V) (извините, идиотский пунто свитчер.)
0
Вложения
Тип файла: rar Пример2b.rar (1.69 Мб, 3 просмотров)
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 13:02  [ТС] 17
chumich, а если можно. то самый лучший вариант. это сравнение по ТРЁМ условиям. Чтобы совсем шанс, что будут совпадения и в 2 условиях исключить насовсем.
3 условие, если возможно. то делать по столбцу Q
0
1990 / 1161 / 444
Регистрация: 20.12.2014
Сообщений: 3,087
20.07.2015, 13:39 18
Цитата Сообщение от Oleg395 Посмотреть сообщение
с полным разжёвыванием
Кликните здесь для просмотра всего текста
Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
'LastRow - переменная, в которую заносится номер последней заполненной строки, название произвольное, можно было назвать любой буквой или сочетанием символов
'Лист1.Cells(Лист1.Rows.Count, 5).End(xlUp).Row - это конструкция в которой для вас, в случае использования важны две составляющие: Лист1 - это название листа (берется из свойства (Name) в окне свойств) и 5 - порядковый номер столбца в котором ищется последняя заполненная строка. В данном случае - это столбец Е. Все остальные составляющие при использовании воспроизводятся без изменений.
LastRow = Лист1.Cells(Лист1.Rows.Count, 5).End(xlUp).Row 'поиск последней строки на листе Лист1
'ReDim - оператор для переобъявления (или объявления при использовании переменных в размерности) массива
'mass (и далее arr и listArr) - это названия массивов, названия выбраны произвольно
'mass(x, y) - в общем виде, запись двумерного массива. x - количество строк, y - количество столбцов
'mass(LastRow - 14, 3) - в данном случае, LastRow - 14 - количество строк с 15-ой по последнюю заполненную (математику разжевывать не буду), 3 - количество столбцов (E, S, V). 15 - здесь (и далее) номер первой заполненной строки на этом (и других) листах
ReDim mass(LastRow - 14, 3) 'объявление массива для данных трех столбцов листа Лист1
'массивы используются для ускорения работы программы, без них программа работает около 10 минут
'For...Next - в общем виде, конструкция цикла с параметром, два оператора, между которыми находятся операторы выполняемые во время работы цикла
For i1 = 15 To LastRow ' i1 (и далее j2, k2, i2, j, k, i) - параметр (или счетчик), который изменяется с каждым шагом на единицу в диапазоне, в данном случае, от 15 до (То) значения LastRow, найденного выше.
'таким образом цикл перебирает все ячейки трех столбцов листа Лист1 с 15-ой по последнюю заполненную строку и заносит данные  ячеек в соответствующие элементы массива mass
    mass(i1 - 15, 0) = Лист1.Cells(i1, 5) 'заполнение массива данными столбца E (номер 5)
'mass(i1 - 15, 0) - нумерация строк любого массива начинается с 0, поэтому из значения счетчика вычитается 15
'нумерация столбцов массива также начинается с нуля, поэтому здесь и ниже 0,1,2 а не 1,2,3
'ИмяЛиста.Cells(НомерСтроки, НомерСтолбца) - в общем виде, адрес ячейки из которой берутся данные
    mass(i1 - 15, 1) = Лист1.Cells(i1, 19) 'заполнение массива данными столбца S (номер 19)
    mass(i1 - 15, 2) = Лист1.Cells(i1, 22) 'заполнение массива данными столбца V (номер 22)
Next i1 'завершающий оператор цикла, возвращающий программу к его началу со следующим значением счетчика i1
 
ReDim arr(17) 'объявление массива массивов для данных трех столбцов других листов, размерность массива определяется количеством листов с данными (не считая лист Лист1)
For j2 = 1 To 17 'цикл делающий перебор листов (объяснено выше)
    Worksheets("" & j2 & "").Select 'активация листа с определенным именем. Активацию пришлось использовать из-за того, что у вас листы перемешаны и расположены не по порядку индексов. Конструкция стандартная, вас в ней должна интересовать только переменная j2, которая задает название листа, то есть то, что у вас написано на ярлычках листов. Слава Богу, что вы их обозначили цифрами :)
' следующая строка уже объяснена выше, единственное отличие - это использование ActiveSheet, в связи с тем, что поиск происходит на активном листе. EndRow - то же самое, что и LastRow
    EndRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 5).End(xlUp).Row 'поиск последней строки на этом листе
    ReDim listArr(EndRow - 14, 3) 'объявление массива для данных трех столбцов этого активного листа, аналогично объявлению массива mass
    arr(j2 - 1) = listArr 'передача данных массива листа в массив массивов, нумерация массива начинается с 0, поэтому используется (j2 - 1), а не j2
'дальше должно быть всё понятно, кроме обозначения arr
'arr(z)(x, y) - элемент массива массивов. z - порядковый номер массива (соответствует названию листа - 1),  x - порядковый номер строки этого массива, y - порядковый номер столбца этого массива
    For k2 = 15 To EndRow 'цикл для заполнения массивов данных трех столбцов каждого листа
        arr(j2 - 1)(k2 - 15, 0) = ActiveSheet.Cells(k2, 5) 'заполнение массива данными столбца E 
        arr(j2 - 1)(k2 - 15, 1) = ActiveSheet.Cells(k2, 19) 'заполнение массива данными столбца S 
        arr(j2 - 1)(k2 - 15, 2) = ActiveSheet.Cells(k2, 22) 'заполнение массива данными столбца V 
    Next k2
Next j2
' таким образом в результате работы вышеприведенного фрагмента программы создано и заполнено данными 18 массивов. 
For i = 15 To LastRow 'цикл для перебора строк массива с данными листа Лист1
    For j = 1 To 17 'цикл для перебора массивов с данными листов 1 - 17
        For k = 0 To UBound(arr(j - 1)) 'цикл для перебора элементов массивов с данными листов 1-17
' далее идет условный оператор, работающий следующим образом:
' если (If) элемент текущей строки первого столбца массива mass (то есть строки столбца E листа Лист1) равен элементу текущей строки первого столбца массива arr (j - 1)() (то есть строки столбца E листа j) и, одновременно, (And) равны элементы вторых столбцов (S), то в третий столбец массива mass заносится значение третьего столбца массива arr (j - 1)
            If mass(i - 15, 0) = arr(j - 1)(k, 0) And mass(i - 15, 1) = arr(j - 1)(k, 1) Then mass(i - 15, 2) = arr(j - 1)(k, 2) 'проверка соответствия двум условиям одновременно
'ниже, если значения вторых столбцов не равны в третий заносится "-"
            If mass(i - 15, 0) = arr(j - 1)(k, 0) And mass(i - 15, 1) <> arr(j - 1)(k, 1) Then mass(i - 15, 2) = "-" 'заполнение ячеек не удовлетворяющих условиям
        Next k
    Next j
Next i
' думаю нижний цикл уже можно не комментировать
For i2 = 15 To LastRow 'цикл для заполнения строк столбца V листа 1
    Лист1.Cells(i2, 22) = mass(i2 - 15, 2) 'заполнение столбца V
Next i2

Пока писал комментарий, нашел пару небольших ошибок, скорее всего из-за этого работает неправильно. Вечером постараюсь исправить. Fairuza права, спасибо ей, - азы VBA вам всё-таки необходимо изучить. Иначе, в данный момент, вы тратите наше время на то, что в состоянии понять самостоятельно, и потом его может не хватить на то, что вам действительно будет трудно понять или сделать. Необязательно читать всё подряд. Смотрите по темам, которые вас касаются: объявление переменных, объявление массивов, циклы, условный оператор. Экономьте наше время - оно, возможно, вам пригодится
1
83 / 60 / 21
Регистрация: 03.12.2014
Сообщений: 232
20.07.2015, 13:45 19
Цитата Сообщение от Oleg395 Посмотреть сообщение
вы увидите неточности!
Почему "неточности"? вот я вижу что макрос который Вам сделал chumich, находит и вставляет последнее значение по совпадению, к примеру:
1 - THUN - BASEL во 2 вкладке две позиции, значения их равны 1-0 и 2-226,1
2- CлBRELлA - PALESTINл в 3 вкладке -500 и он же есть еще в 7 вкладке со значением 1605,2
Вам как надо чтоб первый который встретил или же .....
1
5 / 5 / 0
Регистрация: 15.06.2015
Сообщений: 342
20.07.2015, 13:47  [ТС] 20
AntikBantik, мне нужно по двум. а теперь уже желательно по ТРЁМ условиям! Вы посмотрите внимательно задание. мне не нужно подставлять саое 1 попавшееся(то есть абы что), а нужно подставлять согласно 2, а теперь уже 3 условиям(если возможно это сделать). понимаете? тоесть чтобы 100% попадание было по 2(и если можно то 3) условиям.
0
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
20.07.2015, 13:47

Помощь в написании контрольных, курсовых и дипломных работ здесь.

Фильтрация по нескольким условиям
Ребята, всем привет! У меня стоит следующая задача, в решении которой прошу вашей помощи!...

Select по нескольким условиям
Уважаемые форумчане) Помогите разобраться с костылями! у меня есть решение но я считаю его сложным...

Суммирование по нескольким условиям
Уважаемые формучане! Помогите, пожалуйста, с формулой. Необходимо суммировать значения в столбце по...

Поиск по нескольким условиям
вот к примеру имеем такую таблицу 1. Салат - еда 2. Салат - кухня 3. Салат - ресторан 4. Нож...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
20
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2021, vBulletin Solutions, Inc.