Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сбор и структурирование разрозненных данных в книге на одном листе.
 
#1
11.05.2017 13:20:58
Доброго времени суток. Столкнулся с нестандартной задачей.
Есть несколько листов с данными, на каждом из листов, данные расположены в определенной колонке "Например G" но в разных строках, зачастую не по порядку.
Задача состоит в том, чтобы наиболее быстрым способом собрать и структурировать данные на одном "Итогов" листе.
На данный момент, я делаю это следующим способ. Создаю лист итогов, именную колонки, с помощью функции "=сцепка" копирую определенную ячейку с первого листа и заношу в итоговую колонку с данным наименованием. Затем фиксирую знаком $ значения растягиваю формулу. Вручную построчно меняю в формулах номера листов(идет по порядку), через "Поиск и замену" заменяю статичное значение формата $G$число на Gчисло. Растягиваю получению колонку по остальным колонкам. Из проделанных манипуляций мы имеем сцепленные ячейки с разными Буквами столбцов в формуле. И затем, так же кнопкой "Поиск и замена" заменяю названия. Например в полученной соседней колонке формулы с ячейкой H12, меняю на G14.
Более подробная информация в прикрепленном файле.
Можно этот процесс как-то по другому автоматизировать например, через макрос или же посредством какой-то функции "Консолидации" данных?
Напишите о своих решениях.
Заранее благодарю за ответы.
 
 
 
#2
11.05.2017 13:50:58
Если в первом столбце листа итогов заменить номер на имя листа, то можно применить такую формулу, которую можно копировать-протягивать без лишних ухищрений:
Для строки 9:
Код
=ВПР(B$2;ДВССЫЛ("'"&$A9&"'!B:D";ИСТИНА);2;0)
Правда, спецы считают функцию ДВССЫЛ нежелательной.
Первое из-за чего: файл, на который она ссылается должен быть обязательно открыт. Но у Вас один файл, поэтому эта гадкость Вам ничего не попортит.
Второе - она "летучая". Это значит, пересчитывается гораздо чаще, чем многие другие, поэтому при очень многократном применении раньше, чем другие вызовет торможение-подвисание. А вот это вполне возможно.
Изменено: PerfectVam - 11.05.2017 13:51:47
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
#3
11.05.2017 14:00:33
Как вариант в модуль листа итогов:
Код
Public Sub www()
    Dim i&, a, d1 As Object, sh As Worksheet, b
    Set d1 = CreateObject("scripting.dictionary")
    For Each sh In ThisWorkbook.Worksheets
        If sh.Name <> Me.Name Then
            a = sh.Range("b3:c" & sh.Cells(sh.Rows.Count, 3).End(xlUp).Row)
            For i = 1 To UBound(a)
                If d1.exists(a(i, 1)) Then d1(a(i, 1)) = d1(a(i, 1)) & "|" & a(i, 2) Else d1(a(i, 1)) = a(i, 2)
            Next
        End If
    Next
    a = d1.keys: b = d1.items
    Me.[b2].Resize(1, UBound(a) + 1) = a
    For i = 0 To UBound(b)
        a = Application.Transpose(Split(b(i), "|"))
        Me.Cells(3, i + 2).Resize(UBound(a)) = a
    Next
End Sub
Я сам - дурнее всякого примера! ...
 
 
 
#4
11.05.2017 14:22:05
Цитата
PerfectVam написал:
1=ВПР(B$2;ДВССЫЛ("'"&$A9&"'!B:D";ИСТИНА);2;0)
Уже пробовал так делать, но данных в книге на столько бывает много, что он зависает на несколько минут.
Цитата
kuklp написал:
Как вариант в модуль листа итогов:
Я не совсем разбираюсь в макросах, но тут явно написано, для данного примера. В оригинальном документе, колонок от 25 до 100 бывает и такое. А так же,искомые данные располагаются не по порядку. А имеют конкретную позицию у себя на листе. (К примеру: в ячейке G40 на всех листах, есть значение, а вот следующее значение, которое нужно будет вставить в "итоги" будет располагаться в ячейке G42). Вставку заголовка к наименованию, необязательная функция.
За ответ спасибо.
 
 
 
#5
11.05.2017 14:37:50
Если данных настолько много, что формулы даже для одного пересчета тормозят, то:
1) Можно пробовать оптимизировать формулы - максимально сузить диапазон поиска. Вместо столбцов задавать прямоугольный диапазон. Главное, чтобы строк хватило для данных любого листа.
2) После пересчета скопировать и сохранить как значения.
С формулами больше никак.
Макрос, предложенный kuklp если и неоптимален, то весьма близок к оптимальному. И то, что данные могут встречаться не по порядку, он предусматривает. А что относится к другим диапазонам - это недостаток Вашего примера. Если бы задействовали столбцы, в которых Ваши реальные данные, сразу рабочую версию получили бы...
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
#6
11.05.2017 15:03:21
Немного изменил пример. Стал более похож на истину.
 
 
 
#7
11.05.2017 15:13:13
Цитата
PerfectVam написал:
1) Можно пробовать оптимизировать формулы - максимально сузить диапазон поиска. Вместо столбцов задавать прямоугольный диапазон. Главное, чтобы строк хватило для данных любого листа.
Не совсем понял, это как?
 
 
 
#8
11.05.2017 15:26:37
Вместо B:D использовать B10:D5000.
Главное, чтобы раньше 10-ой и ниже 5000-ной строк не было нужных данных ни на одном листе.
Всего в каждом столбце больше миллиона ячеек. Ограничение до 5000 должно увеличить быстродействие в 200 раз.
Цифры я привел для примера. Только Вы можете знать или прикидывать: как Вы можете ограничить Ваши данные.
Есть еще трюкинг с динамическими диапазонами, но функции типа ПРОСМОТР все равно используют весь столбец, а СЧЁТЗНАЧ (который шерстит только занятый диапазон) дает эффект, когда все ячейки какого-то столбца заполнены без исключения.
Изменено: PerfectVam - 11.05.2017 23:43:39
Следствие из третьего закона Чизхолма:
"Даже если ясность изложения исключает неверное толкование, все равно найдется кто-то, кто поймет Вас неправильно."
 
 
 
#9
11.05.2017 20:43:34
PerfectVam, есть очередная идея. А что если просто транспортировать данные с листов. Тогда встает только вопрос как это через формулу или макрос сделать. Если через макрос, то нужен цикл с перелистыванием листов и постоянным смещением на одну ячейку по оси Х.  
 
 
 
#10
13.05.2017 10:42:41
такой вариант подойдет?
 
 
 
#11
15.05.2017 11:24:27
copper-top, такой вариант не подойдет поскольку, данные поступают в таблица объемом A1:L46 фиксировано, на каждом листе, листов может быть от 2 до 200. Для этого и приходиться разрабатывать варианты структурирования данных на одном листе, не задевая лишние.  
 
 
 
#12
15.05.2017 11:46:53
Можно попробовать вот так, но тогда вопрос следующий, какой цикл написать для смены листов в книге. А так же, для вставки, что бы ячейка в колонке B на листе "Итоги" смещалась на одну вниз.

Код
    Sheets("Лист (1)").Select
    Range("G7:G46").Select
    Selection.Copy
    Sheets("Итоги").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
 
 
 
#13
15.05.2017 11:57:26
Цитата
foma067 написал:
какой цикл написать для смены листов в книге
В поиск: "Как перебрать листы"
 
 
 
#14
22.05.2017 13:21:48
Решение следующее:
Код
=ДВССЫЛ("'"&$A4&"'"&"!"&"D"&C$1)
 
 
 
Страницы: 1
Наверх