Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Группировка данных из нескольких листов в одну ячейку (с условием)
 
#1
04.05.2021 15:03:06
Доброго времени суток!
Имеются листы и данные на них:
Лист1 - А1-А10 (порядковые номера), В1 (значение группы для порядкового номера А1-А10)
Лист2 - А1-А10 (порядковые номера), В1 (порядковый номер для всех номеров на данном листе)
Лист3 - А1-А10 (порядковые номера), В1 (порядковый номер для всех номеров на данном листе)

Задача:
Получить на Листе1, в ячейке В1, значение "Группа1, Группа2". Т.е. нужна формула, которая будет проверять наличие порядкового (Лист1 А1) на других листах (Лист2-Лист3 А1-А10) и, при положительном поиске, указывать номер конкретной группы, в зависимости от листа, в ячейку Лист1 В1.
Для понимания приложил эксель файл с данными и результатом (без формулы).

З.Ы. пробовал решить задачу через ВПР, но несколько ВПР в одной ячейке применить нельзя.
 
 
 
#2
04.05.2021 15:05:39
Andrey, только макросом)
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#3
04.05.2021 15:16:19
К сожалению, в макросах понимаю мало, есть возможность подсказать в каком направлении двигаться и, что конкретно "копать"? Возможно, у моей задачи есть есть какое-то более правильное название?
 
 
 
#4
04.05.2021 15:18:10
Andrey, макрос
Код
Sub mrshkei()
Dim sh As Worksheet, sh2 As Worksheet, cell As Range, x As String
Set sh = Worksheets("Лист1")
For i = 1 To sh.Cells(Rows.Count, 1).End(xlUp).Row
For Each sh2 In Worksheets
    If sh.Name <> sh2.Name Then
        Set cell = sh2.Columns(1).Find(sh.Cells(i, 1), LookIn:=xlFormulas, LookAt:=xlWhole)
        If Not cell Is Nothing Then
            x = x & cell.Offset(0, 1) & ", "
        End If
    End If
Next sh2
sh.Cells(i, 2) = x: x = ""
Next i
End Sub
лучше вести на одном листе все сразу и делать сводную в нужном вам виде, а вообще как много листов с данными, если всего ниего то можно и формулой в ЛОБ
Код
=ИНДЕКС(Лист2!$B:$B;ПОИСКПОЗ(A1;Лист2!$A:$A;0))&","&ИНДЕКС(Лист3!$B:$B;ПОИСКПОЗ(A1;Лист3!$A:$A;0))
Изменено: Mershik - 04.05.2021 15:29:07
Не бойтесь совершенства. Вам его не достичь.
 
 
 
#5
04.05.2021 15:37:36
Наверно можно и формулой:
Код
=ВПР(A2;Лист2!A:B;2;0)&", "&ВПР(Лист1!A2;Лист3!A:B;2;0)

только еще завернуть оба ВПР и среднюю вставку в ЕСЛИОШИБКА.

Или еще можно через СЧЕТЕСЛИ
 
 
 
#6
04.05.2021 16:11:53
Цитата
Mershik написал: лучше вести на одном листе все сразу и делать сводную в нужном вам виде
Проблема в том, что свести все на один лист не получится. На каждый лист заносятся унифицированные (по столбцам) данные, но сами по себе разные + промежуточные расчетные формулы.

Цитата
Mershik написал: и формулой в ЛОБ
Спасибо, работает! Макрос не осилил, т.ч. буду использовать формулу. Благо листов не так много, да и нужно настроить только 1 раз.

Цитата
Мария Hrutckaia написал:Наверно можно и формулой:
Спасибо, тоже работает!
Только у меня получилось, что интервальный просмотр (0) не нужно указывать. Если указать, пишет "Н/Д". Вот так работает прекрасно:
Цитата
=ВПР(A2;Лист2!A:B;2)&", "&ВПР(Лист1!A2;Лист3!A:B;2)
 
 
 
#7
04.05.2021 16:39:00
Цитата
Только у меня получилось, что интервальный просмотр (0) не нужно указывать. Если указать, пишет "Н/Д".
0 нужно указывать, иначе подтягивает приближенно - см. описание функции
на значение "2" берет с Листа 2 всё, что приблизительно "2"
но ведь "2" это не "20" и не "21" и не "1"
поэтому и надо заворачивать в ЕСЛИОШИБКА()
Изменено: Мария Hrutckaia - 04.05.2021 16:42:04
 
 
 
#8
05.05.2021 12:45:06
Цитата
Мария Hrutckaia написал:
см. описание функции
Спасибо за замечание, тоже заметил ошибки в работе формулы без "0".
Цитата
Мария Hrutckaia написал:
поэтому и надо заворачивать в ЕСЛИОШИБКА()
Само выражение завернул в "ЕСЛИОШИБКА", а как правильно завернуть &", "&  дабы лишние запятые не показывались? Получается не очень красиво, когда много листов и много формул с ошибкой (в ячейке отображается примерно так: ",,группа3,,,,,,группа1,,,"
 
 
 
Страницы: 1
Наверх