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

Вход

Регистрация

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

 

= Мир MS Excel/Консолидация списка с нескольких листов книги - Мир MS Excel

Старая форма входа
  • Страница 1 из 2
  • 1
  • 2
  • »
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Консолидация списка с нескольких листов книги (Формулы/Formulas)
Консолидация списка с нескольких листов книги
Шифр-шмыфр Дата: Понедельник, 25.03.2019, 16:53 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Добрый день!
В файле "Пример" 6 Листов: первые 5 из них содержат данные, в последнем - результат.
В Листах А1-А5 содержатся данные в Столбе Е, начиная с 6-ой строки, которые мне необходимо перенести в Лист "Результат".
Я создала упрощенный вариант своей задачи, в качестве примера, сохранив структуру и суть, где Листов с данными всего 5, на самом деле их больше 50.
Поэтому я воспользовалась функцией СМЕЩ для перенесения данных с Листа А1 в ,Столб "Данные" в Листе "Результат". Далее я планировала создать условие при помощи функции ЕСЛИ, для того чтобы в случае, когда все возможные данные с одного Листа были перенесены, то при протягивании формулы в ячейке без ссылки стоит 0. Из этого вытекает условие
Код
ЕСЛИ(A2=0;СМЕЩ(А2!#ССЫЛКА!;1;0);A2)
и т.д.
Т.е. если выражение равно 0 - истина, то мы переходим на следующий лист и проделываем те же самые операции.
Как будет выглядеть Формула, которая позволит прописать "шаг" для перехода на последующие листы? Имеется в виду можно ли использовать "глобальную переменную" для несколько листов?
P.S. напоминаю, что таких листов в реале больше 50
К сообщению приложен файл: 0758647.xlsx (13.1 Kb)


Сообщение отредактировал Шифр-шмыфр - Вторник, 26.03.2019, 11:55
 
Ответить
СообщениеДобрый день!
В файле "Пример" 6 Листов: первые 5 из них содержат данные, в последнем - результат.
В Листах А1-А5 содержатся данные в Столбе Е, начиная с 6-ой строки, которые мне необходимо перенести в Лист "Результат".
Я создала упрощенный вариант своей задачи, в качестве примера, сохранив структуру и суть, где Листов с данными всего 5, на самом деле их больше 50.
Поэтому я воспользовалась функцией СМЕЩ для перенесения данных с Листа А1 в ,Столб "Данные" в Листе "Результат". Далее я планировала создать условие при помощи функции ЕСЛИ, для того чтобы в случае, когда все возможные данные с одного Листа были перенесены, то при протягивании формулы в ячейке без ссылки стоит 0. Из этого вытекает условие
Код
ЕСЛИ(A2=0;СМЕЩ(А2!#ССЫЛКА!;1;0);A2)
и т.д.
Т.е. если выражение равно 0 - истина, то мы переходим на следующий лист и проделываем те же самые операции.
Как будет выглядеть Формула, которая позволит прописать "шаг" для перехода на последующие листы? Имеется в виду можно ли использовать "глобальную переменную" для несколько листов?
P.S. напоминаю, что таких листов в реале больше 50

Автор - Шифр-шмыфр
Дата добавления - 25.03.2019 в 16:53
_Boroda_ Дата: Понедельник, 25.03.2019, 17:06 | Сообщение № 2
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
- Еще раз прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)

И почему Вы не продолжили в предыдущей теме?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение- Еще раз прочитайте Правила форума
- Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)

И почему Вы не продолжили в предыдущей теме?

Автор - _Boroda_
Дата добавления - 25.03.2019 в 17:06
Шифр-шмыфр Дата: Понедельник, 25.03.2019, 17:16 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
_Boroda_, Сейчас всё исправлю.
Извините, я новичок
Создала новую, потому что подумала, что та тема безнадежна :D
Если бы знала, как её удалить, удалила бы
 
Ответить
Сообщение_Boroda_, Сейчас всё исправлю.
Извините, я новичок
Создала новую, потому что подумала, что та тема безнадежна :D
Если бы знала, как её удалить, удалила бы

Автор - Шифр-шмыфр
Дата добавления - 25.03.2019 в 17:16
bmv98rus Дата: Понедельник, 25.03.2019, 18:29 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
_Boroda_, Александр
как обычно в не читаемом виде, чтоб просто клиент понял что к чему. просто убегаю.

Удалено администрацией - автор вопроса не исправил замечание модератора
Михаил, последнее предупреждение. Потом буду лепить замечания

Хм, я формулу среди простыни текста и не рассмотрел.


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Понедельник, 25.03.2019, 20:35
 
Ответить
Сообщение_Boroda_, Александр
как обычно в не читаемом виде, чтоб просто клиент понял что к чему. просто убегаю.

Удалено администрацией - автор вопроса не исправил замечание модератора
Михаил, последнее предупреждение. Потом буду лепить замечания

Хм, я формулу среди простыни текста и не рассмотрел.

Автор - bmv98rus
Дата добавления - 25.03.2019 в 18:29
Шифр-шмыфр Дата: Вторник, 26.03.2019, 10:14 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
bmv98rus, да что не так? Я уже второе сообщение написала, а мне все отказываются помочь...
Какой ещё Михаил? Я не Михаил.
Короче эту тему тоже можно удалять и больше не суваться сюда с вопросами????
 
Ответить
Сообщениеbmv98rus, да что не так? Я уже второе сообщение написала, а мне все отказываются помочь...
Какой ещё Михаил? Я не Михаил.
Короче эту тему тоже можно удалять и больше не суваться сюда с вопросами????

Автор - Шифр-шмыфр
Дата добавления - 26.03.2019 в 10:14
_Boroda_ Дата: Вторник, 26.03.2019, 10:24 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Что Вам конкретно непонятно в этом тексте
Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)
? Есть Ваш первый пост, в нем есть формула, ее нужно оформить тегами, методика оформления приведена в самой фразе и подробнее в ссылке

Михаил - это не Вы, это bmv98rus

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


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЧто Вам конкретно непонятно в этом тексте
Оформите формулу тегами (в режиме правки поста выделите формулу и нажмите кнопку fx, пояснялка здесь)
? Есть Ваш первый пост, в нем есть формула, ее нужно оформить тегами, методика оформления приведена в самой фразе и подробнее в ссылке

Михаил - это не Вы, это bmv98rus

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

Автор - _Boroda_
Дата добавления - 26.03.2019 в 10:24
Шифр-шмыфр Дата: Вторник, 26.03.2019, 10:59 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо большое за подсказку и поддержку! Я оформила формулу.
 
Ответить
СообщениеСпасибо большое за подсказку и поддержку! Я оформила формулу.

Автор - Шифр-шмыфр
Дата добавления - 26.03.2019 в 10:59
китин Дата: Вторник, 26.03.2019, 11:16 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
То есть вам надо собрать данные из столбца Е со всех листов в один столбец на листе Результат?


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
СообщениеТо есть вам надо собрать данные из столбца Е со всех листов в один столбец на листе Результат?

Автор - китин
Дата добавления - 26.03.2019 в 11:16
_Boroda_ Дата: Вторник, 26.03.2019, 11:22 | Сообщение № 9
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Все это гораздо проще сделать макросом или пользовательской функцией. Нужно именно формулой? Формулой тоже можно, но она получится довольно сложной. Кстати, в реальном файле листы у Вас так и называются "А1", "А2, ...? Или названия произвольны?


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеВсе это гораздо проще сделать макросом или пользовательской функцией. Нужно именно формулой? Формулой тоже можно, но она получится довольно сложной. Кстати, в реальном файле листы у Вас так и называются "А1", "А2, ...? Или названия произвольны?

Автор - _Boroda_
Дата добавления - 26.03.2019 в 11:22
bmv98rus Дата: Вторник, 26.03.2019, 11:30 | Сообщение № 10
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Вариант 1- сложный и массивный
Вариант 2- с доп. столбцами которые можно вынести на отдельный лист или скрыть и элементарный
Оба летучие и рассчитаны на то, что листы в названии имею отличие только в индексе.

Файл в сообщении ниже


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 26.03.2019, 11:47
 
Ответить
СообщениеВариант 1- сложный и массивный
Вариант 2- с доп. столбцами которые можно вынести на отдельный лист или скрыть и элементарный
Оба летучие и рассчитаны на то, что листы в названии имею отличие только в индексе.

Файл в сообщении ниже

Автор - bmv98rus
Дата добавления - 26.03.2019 в 11:30
Шифр-шмыфр Дата: Вторник, 26.03.2019, 11:35 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Да)
В реальной жизни таких листов больше 50, поэтому я пыталась придумать формулу, чтобы облегчить себе жизнь
 
Ответить
СообщениеДа)
В реальной жизни таких листов больше 50, поэтому я пыталась придумать формулу, чтобы облегчить себе жизнь

Автор - Шифр-шмыфр
Дата добавления - 26.03.2019 в 11:35
Шифр-шмыфр Дата: Вторник, 26.03.2019, 11:44 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Названия произвольны)
Желательно формула, потому что макросами я пользоваться, к сожалению, пока не умею
Буду в скором времени осваивать
 
Ответить
СообщениеНазвания произвольны)
Желательно формула, потому что макросами я пользоваться, к сожалению, пока не умею
Буду в скором времени осваивать

Автор - Шифр-шмыфр
Дата добавления - 26.03.2019 в 11:44
bmv98rus Дата: Вторник, 26.03.2019, 11:46 | Сообщение № 13
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Вариант 3 с перечнем листов

Ну и тема конечно: Консолидация списка с нескольких листов книги.
К сообщению приложен файл: 9000848.xlsx (23.4 Kb)


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 26.03.2019, 11:52
 
Ответить
СообщениеВариант 3 с перечнем листов

Ну и тема конечно: Консолидация списка с нескольких листов книги.

Автор - bmv98rus
Дата добавления - 26.03.2019 в 11:46
Шифр-шмыфр Дата: Вторник, 26.03.2019, 11:54 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Спасибо большое за помощь!
 
Ответить
СообщениеСпасибо большое за помощь!

Автор - Шифр-шмыфр
Дата добавления - 26.03.2019 в 11:54
китин Дата: Вторник, 26.03.2019, 11:59 | Сообщение № 15
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
Цитата Шифр-шмыфр, 26.03.2019 в 11:44, в сообщении № 12 ()
потому что макросами я пользоваться, к сожалению, пока не умею

ох, ну раз уж написал, то выложу

[p.s.]критика от знатоков приветствуется и поощряется
К сообщению приложен файл: -222.xlsm (24.3 Kb)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852
 
Ответить
Сообщение
Цитата Шифр-шмыфр, 26.03.2019 в 11:44, в сообщении № 12 ()
потому что макросами я пользоваться, к сожалению, пока не умею

ох, ну раз уж написал, то выложу

[p.s.]критика от знатоков приветствуется и поощряется

Автор - китин
Дата добавления - 26.03.2019 в 11:59
Шифр-шмыфр Дата: Вторник, 26.03.2019, 12:08 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 23
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Огромное спасибо! Я постараюсь сейчас разобраться! Мне это пригодится, и многим на форуме, кого заинтересует эта тема!
 
Ответить
СообщениеОгромное спасибо! Я постараюсь сейчас разобраться! Мне это пригодится, и многим на форуме, кого заинтересует эта тема!

Автор - Шифр-шмыфр
Дата добавления - 26.03.2019 в 12:08
_Boroda_ Дата: Вторник, 26.03.2019, 12:30 | Сообщение № 17
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
критика от знатоков приветствуется и поощряется

Лови
По идее все нужно сначала сложить в массив, а потом выложить на лист. Но если не менять логику твоего макроса, то такие комментарии
[vba]
Код

Sub sss()
    Dim IR1&
    Dim sh As Worksheet
    Dim II&
    Dim IR&
    Application.ScreenUpdating = False
'    Application.CutCopyMode = False 'зачем?
'    Application.DisplayAlerts = False 'зачем?
    With Sheets("Результат") 'один раз обращаемся
        IR1 = .Cells(Rows.Count, 1).End(xlUp).Row
        If IR1 = 1 Then
            IR1 = 2
    '    Else
    '        IR1 = Sheets("Результат").Cells(Rows.Count, 1).End(xlUp).Row 'зачем?
        End If
        .Range("A2" & ":A" & IR1).ClearContents
        For Each sh In ThisWorkbook.Sheets
            IR = .Cells(Rows.Count, 1).End(xlUp).Row
            If sh.Name <> .Name Then 'имя луюше кодом
                II = sh.Cells(6, 5).End(xlDown).Row 'Sheets(sh.Name) - это sh
    '            Sheets(sh.Name).Activate    'зачем?
                sh.Cells(6, 5).Resize(II - 6 + 1).Copy
    '            .Activate
                .Range("A" & IR + 1).PasteSpecial Paste:=xlPasteValues
            End If
        Next sh
    End With
    Application.CutCopyMode = False 'один раз в конце нужно
    Application.ScreenUpdating = True
'    Application.CutCopyMode = True
'    Application.DisplayAlerts = True
End Sub
[/vba]


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

Лови
По идее все нужно сначала сложить в массив, а потом выложить на лист. Но если не менять логику твоего макроса, то такие комментарии
[vba]
Код

Sub sss()
    Dim IR1&
    Dim sh As Worksheet
    Dim II&
    Dim IR&
    Application.ScreenUpdating = False
'    Application.CutCopyMode = False 'зачем?
'    Application.DisplayAlerts = False 'зачем?
    With Sheets("Результат") 'один раз обращаемся
        IR1 = .Cells(Rows.Count, 1).End(xlUp).Row
        If IR1 = 1 Then
            IR1 = 2
    '    Else
    '        IR1 = Sheets("Результат").Cells(Rows.Count, 1).End(xlUp).Row 'зачем?
        End If
        .Range("A2" & ":A" & IR1).ClearContents
        For Each sh In ThisWorkbook.Sheets
            IR = .Cells(Rows.Count, 1).End(xlUp).Row
            If sh.Name <> .Name Then 'имя луюше кодом
                II = sh.Cells(6, 5).End(xlDown).Row 'Sheets(sh.Name) - это sh
    '            Sheets(sh.Name).Activate    'зачем?
                sh.Cells(6, 5).Resize(II - 6 + 1).Copy
    '            .Activate
                .Range("A" & IR + 1).PasteSpecial Paste:=xlPasteValues
            End If
        Next sh
    End With
    Application.CutCopyMode = False 'один раз в конце нужно
    Application.ScreenUpdating = True
'    Application.CutCopyMode = True
'    Application.DisplayAlerts = True
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 26.03.2019 в 12:30
bmv98rus Дата: Вторник, 26.03.2019, 12:35 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
китин,
Меньше дергатни



Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 26.03.2019, 12:54
 
Ответить
Сообщениекитин,
Меньше дергатни


Автор - bmv98rus
Дата добавления - 26.03.2019 в 12:35
_Boroda_ Дата: Вторник, 26.03.2019, 13:24 | Сообщение № 19
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Sheets("Результат").Range("A" & IR + 1).Resize(Range(Cells(6, 5), Cells(II, 5)).Rows.Count).Value = _
                .Range(sh.Cells(6, 5), .Cells(II, 5)).Value

Ничего здесь не напрягает? К чему относятся Range и два Cells? Что больше нравится - точка или sh?
Дергатни-то может и меньше (хотя если убрать Activate, то ненамного), зато строка получилась такая прям понятная-понятная :D


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Sheets("Результат").Range("A" & IR + 1).Resize(Range(Cells(6, 5), Cells(II, 5)).Rows.Count).Value = _
                .Range(sh.Cells(6, 5), .Cells(II, 5)).Value

Ничего здесь не напрягает? К чему относятся Range и два Cells? Что больше нравится - точка или sh?
Дергатни-то может и меньше (хотя если убрать Activate, то ненамного), зато строка получилась такая прям понятная-понятная :D

Автор - _Boroda_
Дата добавления - 26.03.2019 в 13:24
bmv98rus Дата: Вторник, 26.03.2019, 13:29 | Сообщение № 20
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4099
Репутация: 766 ±
Замечаний: 0% ±

Excel 2013/2016
Ничего здесь не напрягает?

не напрягает совсем, так как неважно на каком листе считать строки из указанного диапазона. Не к данным обращаемся, даже если другая книга будет активна , то все равно вернет число строк. хотя в данном случае просто можно было из II вычесть 6 и прибавить 1 то есть вычесть 5.


Замечательный Временно просто медведь , процентов на 20.

Сообщение отредактировал bmv98rus - Вторник, 26.03.2019, 13:31
 
Ответить
Сообщение
Ничего здесь не напрягает?

не напрягает совсем, так как неважно на каком листе считать строки из указанного диапазона. Не к данным обращаемся, даже если другая книга будет активна , то все равно вернет число строк. хотя в данном случае просто можно было из II вычесть 6 и прибавить 1 то есть вычесть 5.

Автор - bmv98rus
Дата добавления - 26.03.2019 в 13:29
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Консолидация списка с нескольких листов книги (Формулы/Formulas)
  • Страница 1 из 2
  • 1
  • 2
  • »
Поиск:

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