Скопировать строки через одну в excel
Хотим, упрощенно говоря, повернуть таблицу на бок, т.е. то, что располагалось в строке - пустить по столбцу и наоборот:
Способ 1. Специальная вставка
Выделяем и копируем исходную таблицу (правой кнопкой мыши - Копировать). Затем щелкаем правой по пустой ячейке, куда хотим поместить повернутую таблицу и выбираем из контекстного меню команду Специальная вставка (Paste Special) . В открывшемся диалоговом окне ставим галочку Транспонировать (Transpose) и жмем ОК.
Минусы : не всегда корректно копируются ячейки с формулами, нет связи между таблицами (изменение данных в первой таблице не повлияет на вторую).
Плюсы : в транспонированной таблице сохраняется исходное форматирование ячеек.
Способ 2. Функция ТРАНСП
Выделяем нужное количество пустых ячеек (т.е. если, например, исходная таблица была из 3 строк и 5 столбцов, то выделить обязательно нужно диапазон из 5 строк и 3 столбцов) и вводим в первую ячейку функцию ТРАНСП (TRANSPOSE) из категории Ссылки и массивы (Lookup and Reference):
После ввода функции необходимо нажать не Enter, а Ctrl+Shift+Enter, чтобы ввести ее сразу во все выделенные ячейки как формулу массива . Если раньше не сталкивались с формулами массивов, то советую почитать тут - это весьма экзотический, но очень мощный инструмент в Excel.
Плюсы : между таблицами сохраняется связь, т.е. изменения в первой таблице тут же отражаются во второй.
Минусы : не сохраняется форматирование, пустые ячейки из первой таблицы отображаются в виде нулей во второй, нельзя редактировать отдельные ячейки во второй таблице, поскольку формулу массива можно менять только целиком.
Способ 3. Формируем адрес сами
Этот способ отчасти похож не предыдущий, но позволяет свободно редактировать значения во второй таблице и вносить в нее любые правки при необходимости. Для создания ссылок на строки и столбцы нам понадобятся четыре функции из категории Ссылки и массивы:
- Функция АДРЕС(номер_строки; номер_столбца) - выдает адрес ячейки по номеру строки и столбца на листе, т.е. АДРЕС(2;3) выдаст, например, ссылку на ячейку C2.
- Функция ДВССЫЛ(ссылка_в_виде_текста) - преобразует текстовую строку, например, "F3" в настоящую ссылку на ячейку F3.
- Функции СТРОКА(ячейка) и СТОЛБЕЦ(ячейка) - выдают номер строки и столбца для заданной ячейки, например =СТРОКА(F1) выдаст 1, а =СТОЛБЕЦ(А3) выдаст 3.
Теперь соединяем эти функции, чтобы получить нужную нам ссылку, т.е. вводим в любую свободную ячейку вот такую формулу:
=ДВССЫЛ(АДРЕС(СТОЛБЕЦ(A1);СТРОКА(A1)))
в английской версии Excel это будет =INDIRECT(ADDRESS(COLUMN(A1),ROW(A1)))
А затем копируем (протягиваем) формулу на соседние ячейки как обычно черным крестом. В итоге должно получиться примерно следующее:
Т.е. при копировании формулы вниз по столбцу, она выдает ссылку, которая уходит вправо по строке и наоборот. Чего и требовалось.
Плюсы : сохраняются связи между таблицами, можно легко вносить изменения во вторую таблицу.
Минусы : форматирование не сохраняется, но его можно легко воспроизвести Специальной вставкой (вставить только Формат с флажком Транспонировать
Добрый день,
необходимо перенести определенные ячейки из одной книги в другую с определенным интервалов ( через 4 ячейки):
A22=A5 F22=A3 N22=D6 W22=D5
A23=A9 F23=A7 N23=D10 W23=D9
. . . .
Как создать формулу чтобы автоматом растянуть на все остальные ячейки.
Файлы во вложении ( из файла 1 в файл 2)
Добрый день,
необходимо перенести определенные ячейки из одной книги в другую с определенным интервалов ( через 4 ячейки):
A22=A5 F22=A3 N22=D6 W22=D5
A23=A9 F23=A7 N23=D10 W23=D9
. . . .
Как создать формулу чтобы автоматом растянуть на все остальные ячейки.
Файлы во вложении ( из файла 1 в файл 2) xrust666
Как создать формулу чтобы автоматом растянуть на все остальные ячейки.
Файлы во вложении ( из файла 1 в файл 2) Автор - xrust666
Дата добавления - 11.01.2017 в 08:01
Примеров нет. Не понятны объмы, порой руками проще. Но по сути надо получить зависисмость строк
для A22
Примеров нет. Не понятны объмы, порой руками проще. Но по сути надо получить зависисмость строк
для A22
Замечательный Временно просто медведь , процентов на 20.
Примеров нет. Не понятны объмы, порой руками проще. Но по сути надо получить зависисмость строк
для A22
СМЕЩ(offset) - задает смещение относительно указанного адреса. В данном случае для простоты первая ячейка нужной таблицы
Далее следуют параметры, на сколько сместить адрес по строкам
и в нашем случае
- это как раз (22-21)*4 =4 для 23 будет 8 . соответвенно A1 имеет строку 1 и при прибавлеии 4 и 8 будет 5 и 9, что вам и нужно( для других значений, естествено свой расчет).
и столбцам. 0 - означает не смещать
следующие два параметра - это размер области, в данном случае интересует одна ячейка. то есть 1 и 1
соотвтевенно в итоге получаем адрес искомой ячейки в другой книге.
для удобства можно использовать в качестве базовой ячейки для N22 не A1 из другой книги. а N1 и всегда иметь смещение 0 по столбцам.
Так более понятно?
СМЕЩ(offset) - задает смещение относительно указанного адреса. В данном случае для простоты первая ячейка нужной таблицы
Далее следуют параметры, на сколько сместить адрес по строкам
и в нашем случае
- это как раз (22-21)*4 =4 для 23 будет 8 . соответвенно A1 имеет строку 1 и при прибавлеии 4 и 8 будет 5 и 9, что вам и нужно( для других значений, естествено свой расчет).
и столбцам. 0 - означает не смещать
следующие два параметра - это размер области, в данном случае интересует одна ячейка. то есть 1 и 1
соотвтевенно в итоге получаем адрес искомой ячейки в другой книге.
для удобства можно использовать в качестве базовой ячейки для N22 не A1 из другой книги. а N1 и всегда иметь смещение 0 по столбцам.
Так более понятно? bmv98rus
Замечательный Временно просто медведь , процентов на 20.
СМЕЩ(offset) - задает смещение относительно указанного адреса. В данном случае для простоты первая ячейка нужной таблицы
Далее следуют параметры, на сколько сместить адрес по строкам
и в нашем случае
- это как раз (22-21)*4 =4 для 23 будет 8 . соответвенно A1 имеет строку 1 и при прибавлеии 4 и 8 будет 5 и 9, что вам и нужно( для других значений, естествено свой расчет).
и столбцам. 0 - означает не смещать
следующие два параметра - это размер области, в данном случае интересует одна ячейка. то есть 1 и 1
соотвтевенно в итоге получаем адрес искомой ячейки в другой книге.
для удобства можно использовать в качестве базовой ячейки для N22 не A1 из другой книги. а N1 и всегда иметь смещение 0 по столбцам.
Так более понятно? Автор - bmv98rus
Дата добавления - 11.01.2017 в 10:02
В этой статье описаны некоторые хитрые способы, которые помогут вам быстро и удобно скопировать каждую вторую строку в Microsoft Excel.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Предположим, вам нужно скопировать каждую вторую строку из следующего диапазона:
Скопируйте каждую вторую строку в Excel с помощью маркера заполнения
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Фактически, мы можем быстро копировать и вставлять любой контент из одного диапазона в другой. Мы поможем вам завершить его на основе приведенного выше примера:
Шаг 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) без труда.
Как скопировать и вставить несколько несмежных ячеек / строк / столбцов в Excel?
Обычно, когда мы копируем несколько несмежных ячеек в Excel, появляется диалоговое окно с предупреждением, как показано ниже. Помимо копирования и вставки каждой ячейки по одной, существуют ли какие-либо методы для копирования и вставки нескольких несмежных ячеек / строк / столбцов вместе? Да! В этой статье будут рекомендованы два простых метода:
Скопируйте и вставьте несколько несмежных ячеек / строк / столбцов
Хотя Microsoft Excel не поддерживает копирование и вставку нескольких несмежных ячеек / ячеек / столбцов, но позволяет пользователям копировать и вставлять несколько несмежных ячеек / строк / столбцов в определенных условиях.
(1) Скопируйте и вставьте несколько несмежных ячеек в один столбец / строки
1, Удержание Ctrl key и выберите несколько несмежных ячеек в одном столбце / строках одну за другой.
2, нажмите Ctrl + C ключи одновременно для копирования этих несмежных ячеек; выберите первую ячейку целевого диапазона и нажмите Ctrl + V клавиши одновременно, чтобы вставить эти несмежные ячейки. См. Скриншоты ниже:
(2) Скопируйте и вставьте несколько несмежных строк (или столбцов), содержащих одинаковые столбцы (или строки).
1, Удержание Ctrl key и выберите несколько несмежных строк (или столбцов), содержащих одинаковые столбцы (или строки).
В нашем примере мы выбираем несколько несмежных строк с одинаковыми столбцами. Смотрите скриншот:
2, нажмите Ctrl + C клавиши, чтобы скопировать эти несмежные строки, затем выберите первую ячейку целевого диапазона и нажмите Ctrl + V ключи для вставки этих строк. Смотрите скриншот выше:
Скопируйте и вставьте несколько несмежных столбцов, как показано на скриншоте ниже:
Скопируйте и вставьте несколько несмежных ячеек / строк / столбцов с помощью Kutools for Excel
Иногда вам может потребоваться скопировать и вставить несколько несмежных ячеек из разных строк / столбцов или скопировать несколько несмежных строк / столбцов с разными столбцами / строками. В этих условиях можно попробовать Копировать диапазоны утилита Kutools для Excel.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите несколько несмежных ячеек / строк / столбцов, удерживая Ctrl ключ и щелкните Кутулс > Копировать диапазоны. Смотрите скриншот:
2. В открывшемся диалоговом окне "Копировать несколько диапазонов" установите флажок Все и нажмите Ok кнопка. Смотрите скриншот:
3. Затем во втором диалоговом окне «Копировать несколько диапазонов» укажите первую ячейку целевого диапазона и щелкните значок OK кнопка. Смотрите второй снимок экрана выше:
Пока все выбранные несмежные ячейки / строки / столбцы были скопированы и вставлены в новый диапазон. Смотрите скриншот:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Демонстрация: скопируйте и вставьте несколько несмежных ячеек / строк / столбцов в Excel
В большинстве случаев повторы в наших данных нежелательны и мы с вами стараемся от них избавиться разными способами. Но иногда случается, что дубликаты нужны и полезны, и более того - нам необходимо их создавать!
Допустим, что у нас есть вот такая таблица с именами людей, заказавших билет в кино и количеством билетов для каждого:
Каждому билету нужно присвоить уникальный 6-значный номер, который формируется здесь простой функцией СЛУЧМЕЖДУ (RANDBETWEEN) , генерирующей целое случайное число в заданном диапазоне 100000-999999. Да, я знаю, что теоретически совпадения могут быть, но вероятность очень невелика и пока нас, допустим, устраивает (кого не устраивает - см.эту статью). Проблема в другом: для тех, кто заказал больше одного билета, нужно вставить в таблицу новые строки-дубли (по количеству заказанных билетов), т.е. на выходе получить вот такое:
Руками такое делать - тоскливо, формулами - сложно. Так что остаются два наиболее удобных варианта - макросы и Power Query.
Способ 1. Создание дубликатов строк макросом
Откроем редактор макросов кнопкой Visual Basic на вкладке Разработчик (Developer) или сочетанием клавиш Alt + F11 . Вставим новый модуль через меню Insert - Module и скопируем туда текст нашего макроса:
Принцип тут не самый сложный:
- проходим сверху-вниз по столбцу начиная с B2 до первой пустой ячейки
- если число в ячейке >1, то вставляем пустых строк под ячейкой на одну меньше, чем число билетов
- заполняем пустые ячейки (метод FillDown - аналог "протягивания за черный крестик" в правом нижнем углу ячейки)
- переходим к следующей ячейке и т.д.
Способ 2. Создание дубликатов строк в Power Query
Тем, кто хотя бы немного сталкивался с Power Query, рекламировать его мощь не нужно :) Для тех, кто не знаком (если коротко), то Power Query - это бесплатная надстройка для Excel от Microsoft, умеющая делать с данными практически все, что только можно себе представить: загрузку из любых источников, очистку, трансформацию, анализ данных и т.д. Для Excel 2010-2013 ее можно скачать с сайта Microsoft (появится отдельная вкладка Power Query после установки), а в Excel 2016 она уже встроена по-умолчанию (группа Получить внешние данные на вкладке Данные).
Power Query может легко и красиво решить нашу проблему с генерацией дубликатов.
Для начала, выделим нашу таблицу и загрузим ее в Power Query кнопкой Из таблицы/диапазона (From Table/Range) на вкладке Данные (Data) или Power Query:
После окна подтверждения увидим редактор запросов и нашу таблицу. Добавим пользовательский столбец на вкладке Добавить столбец (Add Column - Custom Column) :
В появившемся окне введем имя столбца и формулу, которая создает список чисел от 1 до количества билетов в каждой строке:
После нажатия на ОК появится новый столбец со списками, элементы которых можно развернуть в строки, используя кнопку в шапке таблицы:
В итоге, получаем практически то, что хотелось:
Осталось удалить ненужный больше столбец Список (правой кнопкой мыши по заголовку - Удалить столбец) и выгрузить данные обратно на лист на вкладке Главная (Home) с помощью кнопки Закрыть и загрузить - Закрыть и загрузить в. (Close&Load - Close&Load to. ) и указать подходящее место для результирующей таблицы:
И останется совсем простая часть - добавить к таблице столбец с формулой СЛУЧМЕЖДУ (RANDBETWEEN) для генерации случайных номеров билетов:
Особенно приятно, что при любых изменениях в исходной (левой) таблице (добавлении новых людей или изменении количества билетов), достаточно будет просто обновить правой кнопкой мыши нашу результирующую таблицу с номерами билетов.
Если нужно, чтобы случайные числа не генерировались каждый раз заново при пересчете листа, а формировались один раз, сохраняя потом свои значения, то придется использовать макро-функцию StaticRandBetween из надстройки PLEX или что-то аналогичное.
Также можно, для наглядности, склеивать через дефис номер билета и порядковый номер из столбца Список прямо в Power Query, используя команду Объединить столбцы на вкладке Преобразование (Transform) .
Читайте также: