Excel копирование ячеек с интервалом - Word и Excel - помощь в работе с программами

Копирование значений ячеек через заданный интервал

xrust666

Дата: Среда, 11.01.2017, 08:01 |
Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация:
0
±

Замечаний:
0% ±

Excel 2013

Добрый день,
необходимо перенести определенные ячейки из одной книги в другую с определенным интервалов ( через 4 ячейки):
A22=A5 F22=A3 N22=D6 W22=D5
A23=A9 F23=A7 N23=D10 W23=D9
… … … …

Как создать формулу чтобы автоматом растянуть на все остальные ячейки.

Файлы во вложении ( из файла 1 в файл 2)

Сообщение отредактировал xrust666 — Среда, 11.01.2017, 08:29

Ответить

китин

Дата: Среда, 11.01.2017, 08:17 |
Сообщение № 2

Группа: Модераторы

Ранг: Экселист

Сообщений: 6973

Репутация:
1063
±

Замечаний:
0% ±

Excel 2007;2010;2016

не ни файла 1 ни файла 2

Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852

Ответить

bmv98rus

Дата: Среда, 11.01.2017, 08:22 |
Сообщение № 3

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

xrust666,

Примеров нет. Не понятны объмы, порой руками проще. Но по сути надо получить зависисмость строк
для A22

Код

=OFFSET([Book2]Sheet1!$A$1;(ROW(A22)-21)*4;0;1;1)

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

Сообщение отредактировал bmv98rus — Среда, 11.01.2017, 08:23

Ответить

xrust666

Дата: Среда, 11.01.2017, 08:30 |
Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация:
0
±

Замечаний:
0% ±

Excel 2013

bmv98rus, прошу прощения не вложил файлы

Ответить

xrust666

Дата: Среда, 11.01.2017, 08:43 |
Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация:
0
±

Замечаний:
0% ±

Excel 2013

bmv98rus, не могу понять вашу формулу, можно поподробнее?

Ответить

bmv98rus

Дата: Среда, 11.01.2017, 10:02 |
Сообщение № 6

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

xrust666,

СМЕЩ(offset) — задает смещение относительно указанного адреса. В данном случае для простоты первая ячейка нужной таблицы
Далее следуют параметры, на сколько сместить адрес по строкам
и в нашем случае

— это как раз (22-21)*4 =4 для 23 будет 8 …. соответвенно A1 имеет строку 1 и при прибавлеии 4 и 8 будет 5 и 9, что вам и нужно( для других значений, естествено свой расчет).
и столбцам. 0 — означает не смещать
следующие два параметра — это размер области, в данном случае интересует одна ячейка. то есть 1 и 1
соотвтевенно в итоге получаем адрес искомой ячейки в другой книге.
для удобства можно использовать в качестве базовой ячейки для N22 не A1 из другой книги. а N1 и всегда иметь смещение 0 по столбцам.

Так более понятно?

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

Сообщение отредактировал bmv98rus — Среда, 11.01.2017, 10:05

Ответить

Manyasha

Дата: Среда, 11.01.2017, 10:32 |
Сообщение № 7

Группа: Модераторы

Ранг: Старожил

Сообщений: 2198

Репутация:
898
±

Замечаний:
0% ±

Excel 2010, 2016

Здравствуйте, еще вариант:

Код

=ИНДЕКС([4575992.xlsx]TDSheet!$A$1:$A$1000;5+4*(СТРОКА(A1)-1))

работает и при закрытой второй книге.

ЯД: 410013299366744 WM: R193491431804

Ответить

bmv98rus

Дата: Среда, 11.01.2017, 11:15 |
Сообщение № 8

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4009

Репутация:
760
±

Замечаний:
0% ±

Excel 2013/2016

Manyasha,

Точно, забыл. INDIRECT не работает с другой книгой, а OFFSET c закрытой. Единственное, и это не влияет на скорость яб весь столбец брал

Код

=ИНДЕКС([4575992.xlsx]TDSheet!$A:$A;5+4*(СТРОКА(A1)-1))

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

Сообщение отредактировал bmv98rus — Среда, 11.01.2017, 11:15

Ответить

xrust666

Дата: Среда, 11.01.2017, 13:53 |
Сообщение № 9

Группа: Пользователи

Ранг: Прохожий

Сообщений: 4

Репутация:
0
±

Замечаний:
0% ±

Excel 2013

Всем огромное спасибо. Особенно Manyasha

Ответить

Источник

zhuk_a

Пользователь

Сообщений: 103
Регистрация: 01.01.1970

Доброго времени суток.
Задача следующего плана: есть таблица из нее надо скопировать ячейки в новую, но вся беда в том, что в существующей таблице значения расположены через ячейку, т.е. есть ли какое средство, чтобы при автоматическом копировании(с помощью правого нижнего угла)в новой таблице в соседнюю ячейку попадало значение которое находится в старой таблице в ячейке через одну уфф…………, т.е. если можно выразиться то копирование с постоянным шагом.
Заранее спасибо.

Источник

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

Как перенести значения ячеек с интервалом с одного листа на другой?

23.06.2015, 17:57. Показов 6992. Ответов 24

нужно копировать значения ячеек,выделенных зеленым цветом в столбце В. 8, 57, 106, … (с интервалом 48) на другой лист. помогите! видела формулу, но выдает ошибку! вот часть таблицы

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

23.06.2015, 19:37

В ячейку А2 любого листа и протягивайте вниз

Код

=ДВССЫЛ("Лист3!B"&(СТРОКА()*49-90))

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 10:33

[ТС]

да! спасибо! в этом файле (с частью таблицы) сработало, но в общей таблице сделать не получается, выдает ССЫЛКА. вот часть таблицы, подскажите, пожалуйста, еще раз. наверное, я очень туплю, но единственный выход — разобраться!

(Напомню, с листа 6 перенести зеленые ячейки на лист 7)

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 12:58

Если начинаете с третьей строки, тогда в ячейку V3

Код

=ДВССЫЛ("Лист6!K"&(СТРОКА()*49-139))

в файле ссылка на столбец была неверной.

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 13:40

[ТС]

А если названия столбцов сделаны через RC можно каким-либо образом написать эту формулу? (изменить не могу, много гиперссылок)
И если, например, мне нужно начать с восьмой строки? в формуле вычитаю 49*6?
никак не могу добиться чтобы работало в огромной таблице

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 14:58

rfnzrfnzr, что-то подсказывает мне посмотреть в сторону переформатирования всей таблицы для удобства дальнейшей работы)))

По сути, можно в Параметрах — Формулы — убрать отметку «стиль ссылок РС»

Добавлено через 2 минуты
Надо рассчитать зависимость у=ах+b, в которой а — СТРОКА().

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 15:05

[ТС]

с удовольствием удалила бы там все но увы. мы люди маленькие, что говорят, то и приходится делать
а если столбцы прописываются АА, АВ… это играет какую-то роль в формуле? Все равно не получается уже не знаю в чем ошибка, стопятьсот раз перепробовала.

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 15:09

rfnzrfnzr, может на русской раскладке прописываете?

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 15:28

[ТС]

нееет

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 15:30

rfnzrfnzr, в приложенном файле тоже нет?

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 15:39

[ТС]

видимо с этой таблицей конкретный косяк. в другом файле на отрывке все получается.

спасибо!

Добавлено через 54 секунды
это была неудачная проба. отныне я поумнела

Добавлено через 4 минуты
я понялааааа
в оригинале я переношу с 8 строки 1 листа на 8 строку 2 листа. тогда же изменится формула?

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 15:45

Да)
Тогда надо пересчитывать)

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 15:49

[ТС]

ВСЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕЕ!!!
СПАСИИИИБООООО!!! ДОШЛОООО

Терпения Вам!

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 15:51

rfnzrfnzr, удачи)

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 16:24

[ТС]

а как быть если переношу с 8 строки на 229? Как составить формулу? поясните, пожалуйста, еще раз как высчитывать это b?

Добавлено через 4 минуты
или может быть есть какая-то статья или ссылка? чтобы понять

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 16:49

rfnzrfnzr, что-то я Вас не понимаю)))
Предложенная формула берет значения из нужной ячейки и подставляет последовательно, т.е. в ячейки А2, А3, А4, соответственно из другого листа и ячеек К8, К57, К106 (если не путаю)

Вычислять а и b надо функцией ЛИНЕЙН()

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 17:53

[ТС]

что делать, если в середине таблицы изменился интервал?

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 17:57

rfnzrfnzr, а может все-таки таблицу попробуем пересмотреть?)
Сделать исходный со всеми нужными столбцами, сделать выходной для начальства и для расчетов итоговый

0 / 0 / 0

Регистрация: 23.06.2015

Сообщений: 10

26.06.2015, 18:15

[ТС]

Исключено эту нужно доделать как есть, а вот дальше с новой таблицы новую жизнь начну

5942 / 3154 / 698

Регистрация: 23.11.2010

Сообщений: 10,524

26.06.2015, 18:45

rfnzrfnzr, хорошо. тогда давайте по-другому взглянем на проблему. Нужно вытащить суммы чего?

Добавлено через 1 минуту
Может быть фильтр сделать на этот столбец и фильтровать по цвету ячейки, потом выделение группы ячеек — только видимые — скопировать на другой лист?

Источник

В этой статье описаны некоторые хитрые способы, которые помогут вам быстро и удобно скопировать каждую вторую строку в Microsoft Excel.

Скопируйте каждую вторую строку в Excel с помощью маркера заполнения

Скопируйте каждую вторую строку в Excel с помощью команды фильтра

Скопируйте каждую вторую строку в Excel с помощью Kutools for Excel

Предположим, вам нужно скопировать каждую вторую строку из следующего диапазона:

Скопируйте каждую вторую строку в Excel с помощью маркера заполнения

Фактически, мы можем быстро копировать и вставлять любой контент из одного диапазона в другой. Мы поможем вам завершить его на основе приведенного выше примера:

Шаг 1. Введите формулу = A1 в ячейке E1. Затем щелкните ячейку E1 и перетащите маркер заливки в ячейку G1.

Шаг 2. Выберите и выделите диапазон E1: G2и перетащите маркер заливки через диапазон в соответствии с вашими потребностями.

Теперь он копирует и вставляет содержимое во все остальные строки:

Внимание: таким образом копируется только содержимое каждой второй строки, но не гиперссылки, стили форматирования и т. д.

Скопируйте каждую вторую строку в Excel с помощью команды фильтра

Если вы хотите скопировать не только контент, но и гиперссылки, стили форматирования во все остальные строки, вам следует попробовать Фильтр команда.
Шаг 1. В пустом столбце помимо исходных данных введите 0,1,0,1…, 0,1. В этом случае мы вводим числа в столбец E. см. Следующий снимок экрана:

Шаг 2: Выберите и выделите столбец E и щелкните значок Фильтр под Данные меню.

Шаг 3: Затем вернитесь к столбцу E и нажмите кнопку со стрелкой рядом с ячейкой E1.

Шаг 4. В раскрывающемся меню снимите флажок Выбрать все вариант и проверьте 0 вариант. Затем он скрывает строки с 1 в Excel см. следующий снимок экрана.

Шаг 5: Теперь вы можете выбрать и выделить эти строки интервалов и скопировать их, нажав кнопку Копировать под Главная вкладка (Ctrl + C) без труда.

Скопируйте каждую вторую строку в Excel с помощью Kutools for Excel

Если у вас есть Kutools for Excel установлен, вы можете легко скопировать каждую вторую строку без потери гиперссылок или стилей форматирования быстро с его Выберите интервальные строки и столбцы инструмент.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас.

Шаг 1. Выберите диапазон, с которым вы будете работать, и щелкните значок Кутулс > Выберите Инструменты > Выберите интервальные строки и столбцы….

Шаг 2: В Выберите интервальные строки и столбцы диалоговом окне, укажите параметры, как показано на следующем снимке экрана, и нажмите OK.

Затем выбираются и выделяются все остальные строки в выделенном фрагменте.

Шаг 3: Теперь вы можете скопировать эти выбранные строки интервалов, нажав кнопку Копировать под Главная вкладка (Ctrl + C) без труда.

Нажмите, чтобы узнать больше об этой функции выбора интервальных строк и столбцов.

Статьи по теме:

Выберите каждый n-й столбец в Excel
Выберите каждую вторую или n-ю строку в Excel
Удалить каждую вторую строку
Скрыть каждую вторую строку

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Комментарии (2)

Оценок пока нет. Оцените первым!

Источник

Копирование значений ячеек через заданный интервал

Автор xrust666, 11.01.2017, 08:48

« назад — далее »

Как создать формулу чтобы автоматом растянуть на все остальные ячейки.

Файлы во вложении ( из файла 1 в файл 2)

Растянуть единую формулу вряд ли получится — слишком сложная формула.
ИМХО проще использовать «полуавтоматический» подход:
1. создать копию значимой части таблицы из накладной
2. руками удалить лишние столбцы (необязательно)
3. руками добавить по три пустые строки между каждой из значимых строк вспомогательной таблицы: важно, чтобы расстояние между значимыми строками вспомогательной таблицы соответствовало расстоянию между строками выходной таблицы
4. руками прописать формулы для первой группы выходного документа (в виде «равно соответствующему значению вспомогательной таблицы». Важно: нужно использовать относительные ссылки — без «$»)
5. убедиться в том, что в области A3:D6 нового документа присутствуют только формулы, ссылающиеся на вспомогательный документ — удалить лишние значения или смириться с тем, что их затем придется править
6. скопировать этот диапазон и вставить его во все строки ниже — ссылки должны корректно измениться

P.S. Теоретически свои действия можно записать макрорекодером, отредактировать полученный макрос и пользоваться им в дальнейшем.

Помогли на другом форуме, вот эта формула прекрасно справляется с задачей =ИНДЕКС([1.xlsx]TDSheet!$A:$A;5+4*(СТРОКА(A1)-1))

Меня несколько смущает тот факт, что в один столбец второго документа следует копировать данные сразу из двух столбцов первого: поэтому, как мне кажется: единой формулой указанного вида (например, для ячеек А3 и А5 второго файла) обойтись не получится, но если Вам это не мешает, то я очень рад…