Как в excel скопировать несвязанные диапазоны

Сделал как ты описал, но получил другое сообщение: «Данная команда неприменима для несвязных диапазонов».
Нет ли у тебя объединенных ячеек (это вообще источник гемороя при работе с таблицами )?
Вообще, если для вставки выбрать одну ячейку, то данные вставляются. Но все подряд, а не с пропусками!

Что предпринять?
Можно в режиме автофильтра в первой ячейке второго столбца (куда вставляешь) написать формулу =(первая ячейка первого столбца) и скопировать ее на весь второй столбец. Формулы вставятся только в видимые ячейки.
Потом, при желании, можно заменить формулы на значения: снять автофильтр, выделить второй столбец, копировать, Правка — Специальная вставка — значения.

Нет ли у тебя объединенных ячеек (это вообще источник гемороя при работе с таблицами )?
Нет, обычные ячейки

Можно в режиме автофильтра в первой ячейке второго столбца (куда вставляешь) написать формулу =(первая ячейка первого столбца) и скопировать ее на весь второй столбец.

Не получается
Делаю так — автофильтр, выделяю итоговый столбец, Ctrl+G «Выделить только видимые ячейки», в первую итоговую ячейку — относительную формулу ссылки на ячейки с данными =[Книга2]Лист1!B1
Ctrl+Enter — копирую формулу на все выделенные ячейки

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

GreedBeef
Я все же не могу смоделировать у себя ситуацию, когда возникает ошибка, которую ты описал: «Данные не могут быть вставлены из-за несоответствия формы и размеров области копирования и области вставки».

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

На Листе2 в ячейках A1:A10 находятся числа 1. 10.
Делаю все так, как описывал выше, и ожидаю увидеть напротив ИСТИНА — число из списка.
Однако происходит, как на картинке

Nosorog
Значит, копируется столбец из таблицы, к которой применен автофильтр, в другую книгу в таблицу, к которой тоже применен автофильтр?
Нет

Vic_K
Если копируем с листа с автофильтром и вставляем в лист с автофильтром
Нет

Постараюсь объяснить ещё раз — я копирую данные из обычной таблицы (без автофильтра) в колонку другой таблицы, у которой по одному из столцов применен автофильтр.
Как надо правильно копировать непрерывный диапазон в диапазон, содержащий пустые ячейки только для нужных значений (за исключением скрытых из-за фильтрации)?

В листе 1, Там где список уже с автофильтром — рядом в столбце поставьте СТРОКА(А1), скопируйте эту формулу на все значения строк.
Например:
35
39
40
43
70
71
89
101
191
305

В листе 2, Специальная вставка — Значения — вставить. — вы получите список строк подряд.

Затем в VBA напишите следующее (или подобное — смотрите как вам надо)

В программе надеюсь разберетесь (вставлен мой пример)

‘считываем значение
ivalue = Worksheets(«Лист2»).Cells(i, 2).Value

For j = 1 To 14
‘находим значение и меняем ячейки рядом
Worksheets(«Лист1»).Cells(ivalue, 6 + j) = Worksheets(«Лист2»).Cells(i, 2 + j)

В результате — получите вставленные значения, которые шли подряд — в листе 2, в автофильтр — который в листе 1

P.S. У меня работало с автофильтром по одному столбцу, с несколькими столбцами не работает — ошибка «Данная команда не работает для несвязных диапазонов» или не вставляет в неотображаемые (не попавшие в фильтр) ячейки.

Блог активной пенсионерки.

Страницы

Translate

суббота, 23 августа 2014 г.

Microsoft Excel. Выделение ячеек. Связные и несвязные диапазоны ячеек.

Каждая ячейка в таблице имеет свой адрес. Он обозначается номером столбца и номером строки.

Столбцы в таблице обозначаются буквой С и цифрами.
Ряды — обозначаются буквой R и цифрами.

Например ячейка , расположенная в 5-м ряду, во втором столбце имеет адрес: R5C2.

Чтобы выделить ячейку, нужно поставить курсор в ячейку и щелкнуть левой кнопкой мыши.

Ячейка становится активной и туда можно помещать информацию.

Чтобы выделить ряд, нужно щелкнуть на значении ряда.

Чтобы выделить столбец, нужно щелкнуть на значении столбца.

Выделенная совокупность ячеек называется диапазоном.
Диапазоны бывают связные и несвязные.

Связные диапазоны имеют одну выделенную границу. В правом нижнем углу связной ячейки находится маркер.

Щелкните левой кнопкой мыши на требуемой ячейке и не отпуская кнопки, протяните вправо и вниз. Отпустите кнопку.
Вы получите связный диапазон ячеек.

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

В поле имени указывается адрес только левой верхней ячейки диапазона.

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

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

Можно создать связный диапазон с помощью клавиши Shift и клавиши стрелка.

Поместите курсор в ячейку, нажмите клавишу Shift и несколько раз щелкните клавишей стрелка вправо и потом кнопкой стрелка вниз или наоборот.

Копирование только видимых ячеек

Если некоторые ячейки, строки или столбцы на листе не отображаются, вы сможете скопировать все ячейки (или только видимые ячейки). По умолчанию Excel копирует не только видимые, но и скрытые или фильтрованные ячейки. Если же требуется скопировать только видимые ячейки, выполните действия, описанные ниже. Например, можно скопировать только сводные данные из структурированного листа.

Выделите ячейки, которые вы хотите скопировать. Дополнительные сведения можно найти в разделе выделение ячеек, диапазонов, строк и столбцов на листе.

Совет: Чтобы отменить выделение ячеек, щелкните любую ячейку на листе.

Щелкните Главная > Найти и выделить, а затем выберите пункт Выделение группы ячеек.

Выберите параметр только видимые ячейки и нажмите кнопку ОК.

Щелкните Копировать (или нажмите клавиши CTRL+C).

Выделите левую верхнюю ячейку области вставки и нажмите кнопку Вставить (или нажмите клавиши CTRL + V).

Совет: Чтобы скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните вкладку другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки.

Примечание: При копировании значения последовательно вставляются в строки и столбцы. Если область вставки содержит скрытые строки или столбцы, возможно, потребуется отобразить их, чтобы увидеть все скопированные данные.

При копировании и вставке видимых ячеек в диапазоне данных, который содержит скрытые ячейки или к которому применен фильтр, можно заметить, что скрытые ячейки вставляются вместе с видимыми. К сожалению, вы не можете изменить этот параметр, когда вы копируете и вставляете диапазон ячеек в Excel в Интернете, так как Вставка только видимых ячеек недоступна.

Тем не менее, если отформатировать данные как таблицу и применить фильтр, можно скопировать и вставить только видимые ячейки.

Если не нужно форматировать данные как таблицу и установлено классическое приложение Excel, можно открыть книгу в нем, чтобы скопировать и вставить видимые ячейки. Для этого нажмите кнопку Открыть в Excel и выполните действия, описанные в статье копирование и вставка только видимых ячеек.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Как переместить ячейки в Excel? Перемещение ячеек в пределах листа

Перемещение с помощью мыши

Вырезать и вставить

Сочетание клавиш для вырезания – Ctrl+X или Shift+Delete. Сочетание клавиш для вставки – Ctrl+V или Shift+Insert.

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

Все эти и аналогичные задачи легко решаются при помощи надстройки для Excel.

Надстройка для быстрого перемещения ячеек

1. одним кликом мыши вызывать диалоговое окно макроса прямо из панели инструментов excel;

4. выбирать одно из двенадцати условий для значений ячеек;

5. определять направление перемещения для ячеек, удовлетворяющих заданным условиям и значениям:

а) перемещать ячейки на заданное количество строк вверх или вниз;

б) перемещать ячейки на заданное количество столбцов влево или вправо;

в) перемещать ячейки одновременно вверх/вниз и влево/вправо;

Как копировать в Экселе — простые и эффективные способы

Сначала разберемся с принципами копирования и переноса информации, а потом углубимся в практику.

Если вы выполняете копирование – исходные данные сохраняются, а если перемещение – удаляются. Теперь давайте рассмотрим все способы копирования и переноса, которые предлагает нам Эксель.

Копирование с помощью горячих клавиш

Этот способ – самый простой и привычный, наверное, для всех. Клавиши копирования и вставки совпадают с общепринятыми в приложениях для Windows:

Копирование с помощью контекстного меню

Команды копирования в контекстном меню Эксель

Копирование с помощью команд ленты

Те же действия можно выполнить и с помощью команд ленты:

Копирование в Эксель с помощью ленточных команд

Последняя команда из перечисленных – комбинированная, она имеет дополнительные опции вставки (см. рис. выше) вставить только формулы:

• Вставить – вставить ячейку полностью (значения, формулы, форматы ячейки и текста, проверка условий)
• Формулы – вставить только формулы или значения
• Формулы и форматы чисел – числа, значения с форматом числа как в источнике
• Сохранить исходное форматирование – вставить значения, формулы, форматы ячейки и текста
• Без рамок – все значения и форматы, кроме рамок
• Сохранить ширину столбцов оригинала – вставить значения, формулы, форматы, установить ширину столбца, как у исходного
• Транспонировать – при вставке повернуть таблицу так, чтобы строки стали столбцами, а столбцы – строками
• Значения – вставить только значения или результаты вычисления формул
• Значения и форматы чисел – формулы заменяются на результаты их вычислений в исходном формате чисел
• Значения и исходное форматирование формулы заменяются на результаты их вычислений в исходном формате чисел и ячеек
• Форматирование – только исходный формат, без данных
• Вставить связь – вставляет формулу, ссылающуюся на скопированную ячейку
• Рисунок – вставляет выделенный диапазон, как объект «Изображение»
• Связанный рисунок – Вставляет массив, как изображение. При изменении ячейки-источника – изображение так же изменяется.

Все перечисленные команды являются инструментами Специальной вставки .

Копирование перетягиванием в Эксель

Этот способ – самый быстрый и наименее гибкий. Выделите массив для копирования и наведите мышью на одну из его границ. Курсор примет вид четырёхнаправленной стрелки. Хватайте мышью и тяните ячейки туда, куда хотите их переместить.

Чтобы скопировать массив – при перетягивании зажмите Ctrl . Курсор из четырехнаправленного превратится в стрелку со знаком «+».

Копирование автозаполнением

Маркер автозаполнения

Есть еще один способ – команда Заполнить . Выделите массив для заполнения так, чтобы ячейка для копирования стояла первой в направлении заполнения. Выполните одну из команд, в зависимости от направления заполнения:

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

[expert_bq id=»1570″]Просматриваемый массив аргумент, представляющий собой ссылку на массив, в котором выполняется поиск указанного значения для определения его позиции. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Итак, ставим курсор в нижнюю правую область элемента с формулой. После этого курсор должен преобразоваться в маркер заполнения в виде черного крестика. Выполняем зажим левой кнопки мыши и тянем курсор до самого низа столбца.

Как разорвать связи в excel

Для этого нужно нажать Файл (или кнопку Офис на ленте), выбрать Параметры Excel, открыть Сохранение, установить флажок Автосохранение каждые… и время (например, 1 минута), нажать ОК.

[expert_bq id=»1570″]Можно в режиме автофильтра в первой ячейке второго столбца куда вставляешь написать формулу первая ячейка первого столбца и скопировать ее на весь второй столбец. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Существует также альтернативный вариант. Он, кстати, является единственным для более старых версий Excel. В контекстном меню наводим курсор на пункт «Специальная вставка». В открывшемся дополнительном меню выбираем позицию с одноименным названием.

Как сделать выпадающий список в Excel — ЭКСЕЛЬ ХАК

• Вставить – вставить ячейку полностью (значения, формулы, форматы ячейки и текста, проверка условий)
• Формулы – вставить только формулы или значения
• Формулы и форматы чисел – числа, значения с форматом числа как в источнике
• Сохранить исходное форматирование – вставить значения, формулы, форматы ячейки и текста
• Без рамок – все значения и форматы, кроме рамок
• Сохранить ширину столбцов оригинала – вставить значения, формулы, форматы, установить ширину столбца, как у исходного
• Транспонировать – при вставке повернуть таблицу так, чтобы строки стали столбцами, а столбцы – строками
• Значения – вставить только значения или результаты вычисления формул
• Значения и форматы чисел – формулы заменяются на результаты их вычислений в исходном формате чисел
• Значения и исходное форматирование формулы заменяются на результаты их вычислений в исходном формате чисел и ячеек
• Форматирование – только исходный формат, без данных
• Вставить связь – вставляет формулу, ссылающуюся на скопированную ячейку
• Рисунок – вставляет выделенный диапазон, как объект «Изображение»
• Связанный рисунок – Вставляет массив, как изображение. При изменении ячейки-источника – изображение так же изменяется.

К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.
[expert_bq id=»1570″]Совет Чтобы скопировать выделенный фрагмент на другой лист или в другую книгу, щелкните вкладку другого листа или выберите другую книгу и выделите левую верхнюю ячейку области вставки. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] P.S. У меня работало с автофильтром по одному столбцу, с несколькими столбцами не работает — ошибка «Данная команда не работает для несвязных диапазонов» или не вставляет в неотображаемые (не попавшие в фильтр) ячейки.

Как в excel скопировать несвязанные диапазоны — Компьютерный справочник

• Объединить по строкам — позволяет выбрать диапазон ячеек, который содержит несколько строк. В этом случае Excel будет создавать объединенные ячейки по одной на каждой строке
• Объединить ячейки — объединяет выделенные ячейки в одну, но при этом не выравнивает текст в ячейке.
• Отменить объединение ячеек — позволяет разбить объединенную ячейку на несколько.

Этот способ – самый быстрый и наименее гибкий. Выделите массив для копирования и наведите мышью на одну из его границ. Курсор примет вид четырёхнаправленной стрелки. Хватайте мышью и тяните ячейки туда, куда хотите их переместить.
[expert_bq id=»1570″]Копировать информацию сочетанием клавиш ctrl C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню Копировать. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Теперь, если вы выберите в первом выпадающем списке страну «Россия», то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете «США» из первого выпадающего списка.

Основы Excel. Как выделять ячейки, задавать имена диапазонов и сохранять файлы? MS Office Excel — Работа на компьютере: инструкции и советы — Образование, воспитание и обучение — Сообщество взаимопомощи учителей

• выделить ячейку (диапазон), перейти на ленте на вкладку Формулы, выбрать Присвоить имя и в диалоговом окне Создание имени ввести имя ячейки (диапазона) (например, ИТОГО) и нажать OK;

«Номер строки» — аргумент, являющийся номером этой самой строчки. При этом важно знать, что номер строки следует указывать не относительно всего документа, а только относительно выделенного массива.
[expert_bq id=»1570″]Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Есть еще один способ – команда Заполнить . Выделите массив для заполнения так, чтобы ячейка для копирования стояла первой в направлении заполнения. Выполните одну из команд, в зависимости от направления заполнения:

Как работать с объединенными ячейками Excel

В книге, в которой подтягиваются значения из других файлов, переходим во вкладку «Данные». Щелкаем по значку «Изменить связи», который расположен на ленте в блоке инструментов «Подключения». Нужно отметить, что если текущая книга не содержит связей с другими файлами, то эта кнопка является неактивной.

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

[expert_bq id=»1570″]Значит, копируется столбец из таблицы, к которой применен автофильтр, в другую книгу в таблицу, к которой тоже применен автофильтр. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Но нужно отметить, что в этом случае вставка будет произведена в виде неизменяемого массива. При попытке изменить любую ячейку со вставленными данными будет всплывать сообщение, информирующее о невозможности сделать это.

Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)

• Выбрать ячейку, в которой мы хотим создать выпадающий список;
• Перейти на вкладку « Данные » => раздел « Работа с данными » на панели инструментов => выбрать пункт « Проверка данных «:

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