Форум программистов, компьютерный форум, киберфорум
MS Office Excel
Войти
Регистрация
Восстановить пароль
 
 
Рейтинг 4.75/4: Рейтинг темы: голосов - 4, средняя оценка - 4.75
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
1

Копирование ссылок на другой лист с шагом в Excel

17.06.2020, 06:34. Просмотров 730. Ответов 26

Добрый день !

На листе "План" находятся плановые данные по месяцам
На листе "План-факт" нужны формулы (ссылки) на плановые данные пропуская один столбец, в котором будут фактические данные. Шаг пропуска может быть разный: 1,2, 3 и т.п.

Очень нужная штука в работе. В больших и сложных таблицах когда такое копирование делаешь "ручками" постоянно возникают косяки.

Нужно решение с макросом - большие громоздкие формулы сильно усложнят отладку реальных таблиц.


Заранее спасибо !
0
Вложения
Тип файла: xlsx План-факт 01.xlsx (8.2 Кб, 5 просмотров)
Programming
Эксперт
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
17.06.2020, 06:34
Ответы с готовыми решениями:

Excel копирование определенных ячеек и строк на другой лист
Здравствуйте. Имеется лист с которого нужно при нажатии кнопки скопировать определенные данные на...

Экономное копирование из одного листа в другой (добавление готовых накладных на лист для печати в excel)
Здравствуйте, подскажите, пожалуйста макрос который будет компактно копировать подготовленные...

Создание фильтра и копирование результатов фильтрации на другой лист (либо в другой файл)
Необходима помощь "чайнику". Есть большой массив строк (тексты и цифры), в которых присутствуют...

КОПИРОВАНИЕ НА ДРУГОЙ ЛИСТ ПО ДАТАМ
Доброго времени суток! Возможно данный вопрос уже решался, но на форуме найти я не смог. Суть...

26
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
17.06.2020, 10:40 2
chip2020, Если правильно понял замысел, то можно и так. Предполагается, что обе таблицы начинаются с самой первой строки (при желании/необходимости - это ограничение можно изменить)

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Private Sub Test()
    Dim r As Range, c As Range, i&
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("План-факт"): Set ws2 = Worksheets("План")
    Set r = ws1.UsedRange.Rows(1): i = ws2.UsedRange.Rows.Count - 1
    
    Application.ScreenUpdating = False
    ws1.Rows("2:" & ws1.Rows.Count).Delete
    For Each r In r.Cells
        Set c = ws2.Rows(1).Find(r, , xlValues, xlWhole)
        If Not c Is Nothing Then
           r(2).Resize(i).Formula = "=" & c(2).Address(False, False, , True)
        End If
    Next
    Application.ScreenUpdating = True
End Sub
0
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
17.06.2020, 11:14  [ТС] 3
Можно ли прислать файл с встроенным макросом?
0
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
17.06.2020, 11:32 4
Тест был на Вашем файле, ничего нового там нет.
1
Вложения
Тип файла: xls План-факт 01.xls (36.5 Кб, 2 просмотров)
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
17.06.2020, 11:46  [ТС] 5
Большое спасибо !!! Работает как надо ! Теперь для практического применения нужен интерфейс который бы позволял выделить нужное число колонок (месяцев) по горизонтали и нужные строки данных по вертикали. И шаг на который сдвигаются данные (сейчас он равен 1).
0
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
17.06.2020, 12:37 6
Какие названия столбцов Вы заполните в листе "План-факт", на те столбцы и будут созданы ссылки. Тоже самое касается и шага. Он сейчас создаётся пользователем вручную. А количество строк, действительно, от 2 до последней ячейки (CTRL+G, F5 - Выделить - последняя ячейка)
0
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
17.06.2020, 13:21  [ТС] 7
Сейчас первая ячейка в обоих листах это A1, в реальных таблицах это может быть не так. Например A1 (План-факт) и С2(План). Как это настроить (изменить)

И я не понял как настроить на копирование определенных строки, например только первой. Вообще лучше оставить только одну первую строку - дальше в План-факт можно просто протянуть формулы вниз

Почему то при расширении таблицы (увеличении числа месяцев) происходит "забивка" нулями
0
Вложения
Тип файла: xls План-факт 03.xls (40.5 Кб, 1 просмотров)
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
17.06.2020, 13:45 8
1) + 2) смотрите пример
3) не смешно, ссылка на пустую ячейку будет возвращать 0, так что при "расширении" смотрите на какие ячейки создаются ссылки
0
Вложения
Тип файла: xls План-факт 02.xls (44.0 Кб, 1 просмотров)
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
17.06.2020, 14:08  [ТС] 9
Вы волшебник ! Но принципиально иметь возможность выбора числа строк - проще вообще оставить одну.

А вот с месяцами не понял - как добавить например апр ? Наверное вообще надо уйти от заголовков таблицы - просто копировать со сдвигом строки (ОДНУ строку).

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

P.s. и почему выходные таблицы плодятся как кролики ?
0
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
17.06.2020, 14:21 10
Там есть выбор строк, выбираете с 3 по 12 будет 10 строк.

Апрель никак не добавить, потому, что ссылки создаются на реально существующие столбцы. Сейчас в исходной таблице всего три столбца. Значит можно добавить ссылки только на них.

Код открыт, каждый отчёт на новом листе.
0
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
17.06.2020, 14:38  [ТС] 11
Написал Вам на е-mail (предложение о сотрудничество) из вашего профиля, но письмо не доставлено. Как связаться?

Добавлено через 6 минут
Разобрался - все идеально работает. Осталась настройка начальных ячеек в План и План-факт
0
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
17.06.2020, 19:55 12
chip2020, Вроде бы учёл Ваши желания и чаяния.

В форме можно ввести вручную(или выбрать из списка) первую строку (без проверки), в текстовом поле можно ввести количество строк (без проверки и запрета ввода мусора)
Если же в рабочем листе "План-факт" в заголовке(шапке) ввести/скопировать название нового столбца, то при условии его наличия, Вы получите "расширение таблицы". Ссылки, в этом случае, формируются так - если их вообще нет, они создаются заново, т.е. начиная со второй и заканчивая последней строкой в листе "План". Если же ссылки уже наличествуют, то номера строк берутся уже из имеющихся. Например, если наличествуют всего одна строка и ссылка на =План!B8 , то в новой ссылке также будет использоваться именно 8я строка.

Если же я не попал и промахнулся, то ... значит не судьба
0
Вложения
Тип файла: xls План-факт 03.xls (43.5 Кб, 1 просмотров)
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
18.06.2020, 09:12  [ТС] 13
Все как всегда работает идеально, но я не понял как изменить расположение первой ячейки в План-факт.

И как Кнопку "засунуть" в надстройку Add-In по примеру известной надстройки PLEX, чтобы она не была привязана к конкретной книге (в данном случае План-факт05.xls ). Ведь на практике книга может быть любая !
0
Вложения
Тип файла: xls План-факт 05.xls (43.5 Кб, 1 просмотров)
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
18.06.2020, 09:47 14
Кнопку никуда "засовывать" не нужно, т.к. макрос написан для конкретной книги и двух вполне определённых листов, более того, там используется событие листа, которое предназначено для, так называемого, "расширения таблицы". Введите/скопируйте апр-план и/или май-план в любую ячейку заголовка таблицы (лист "План-факт") и увидите о чём я говорил ранее и сейчас.
0
Вложения
Тип файла: xls План-факт 03v2.xls (43.0 Кб, 2 просмотров)
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
18.06.2020, 10:27  [ТС] 15
С первой ячейкой План-факт разобрался - работает ! И кнопок стало меньше

Теперь с переносом кнопки. В реальной ситуации УЖЕ имеется таблица План, которая имеет связи на листы (ячейки) из других книг. Получается я должен перенести (скопировать) все это хозяйство в книгу с кнопкой 1 ? Что-то типа сено идет за лошадью

P/S Вы не ответили как с Вами связаться по e-mail например
0
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
18.06.2020, 10:42 16
Цитата Сообщение от chip2020 Посмотреть сообщение
В реальной ситуации УЖЕ имеется таблица План, которая имеет связи на листы (ячейки) из других книг. Получается я должен перенести (скопировать) все это хозяйство в книгу с кнопкой 1 ?
Наоборот, перенести(скопировать) код из модуля лист (с событием Worksheet_Change), код из стандартного модуля, и перенести форму (экспорт + импорт)

P.S. Форум открыт для всех. Если у людей есть время и желание, они отвечают, если нет, зачем их беспокоить...
0
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
18.06.2020, 11:19  [ТС] 17
Понял (" – Да, нет, уж лучше вы к нам"), спасибо !

P.S. если вопрос по созданию в Excel DashBoard для конкретного предприятия ?
0
0 / 0 / 0
Регистрация: 17.06.2020
Сообщений: 19
19.06.2020, 08:49  [ТС] 18
Попробовал на реальных файлах. Не очень функционально. Для файла План-факт книг План как правило несколько и операцию экспорт-импорт нужно делать для каждого фай План ?

Наверное лучшее решение Add-ins которое будет ВНЕШНИМ для ВСЕХ файлов ?
0
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
19.06.2020, 12:54 19
Цитата Сообщение от chip2020 Посмотреть сообщение
Наверное лучшее решение Add-ins которое будет ВНЕШНИМ для ВСЕХ файлов ?
Тогда сохраните в личной книге макросов "Personal.xls(b)", только вместо события листа используйте событие приложения, вкупе с проверкой имени листа. А форму вызывайте, например, с помощью горячих клавиш CTRL+u (здесь лишитесь стандартного подчёркивания, так что можно выбрать и другие комбинации клавиш)

Можно и просто кинуть книгу в папку автозагрузки XLStart (Application.StartupPath) или альтернативный каталог автозагрузки (Application.AltStartupPath). Последнего может и не быть, но его можно и создать.
0
4057 / 2178 / 922
Регистрация: 01.12.2010
Сообщений: 4,541
19.06.2020, 13:50 20
Вот примерно то, о чём я говорил ранее.

Книгу в любую папку автозагрузки.
Вызов формы CTRL+U.
"Расширение таблицы" происходит только в рабочем листе с именем "План-факт" (регистр не важен).

За сим позволю себе откланяться. Всю остальную информацию можно найти на форуме. Или во всемирной помойке.
0
Вложения
Тип файла: xls planStartUp.xls (47.5 Кб, 1 просмотров)
IT_Exp
Эксперт
87844 / 49110 / 22898
Регистрация: 17.06.2006
Сообщений: 92,604
19.06.2020, 13:50

Заказываю контрольные, курсовые, дипломные и любые другие студенческие работы здесь.

Копирование строк в другой лист
Всем привет. Просьба помочь с задачей: В документе есть 12 листов (кол-во месяцев в году)....

Копирование значений строки на другой лист
Подскажите пожалуйста. Как скопировать все имеющиеся значения в первой строке на "Лист 1" и...

Перенос и копирование данных на другой лист
И снова здравствуйте! Появился новый вопрос по поводу переноса данных, а если быть точнее...

Подсчет повторений и копирование на другой лист
Доброго времени суток! Нужно решить макросом такую задачу (файл-пример в приложении): Sheet1 это...

Копирование данных на другой лист по условию
Добрый день, уважаемые форумчане. Прошу помощи в решении следующего вопроса: Есть файл (во...

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


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

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

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