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

Обновлено: 05.07.2024

Данная команда не применима для несвязных диапазонов excel

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

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

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

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

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

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

Excel 2003 (imho для данного вопроса это не важно)

Скопируйте получившиеся числа строк.

Заполните строки как вам нужно

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

Private Sub CommandButton1_Click()

Скрытый текст:Sub PasteToVisible()
Dim copyrng As Range, pasterng As Range
Dim cell As Range, i As Long

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

Данные, расположенные в ячейке или в диапазоне ячеек, можно перемещать с одного места рабочего листа в другое, используя как прямое перетаскивание мышью, так и кнопки на вкладках ленты, контекстное меню, а также процедуры (макросы) – программы, написанные на языке Visual Basic for Application, сокращенно VBA.

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

Простейший способ переместить ячейки с места на место – использовать манипулятор мыши. Для перемещения ячейки на новое место, необходимо выделить нужную ячейку или диапазон, навести курсор мыши на границу выделенного диапазона, чтобы курсор стал наклонной стрелкой с крестиком, как показано на рисунке и при нажатой левой кнопке мыши, перетащить выделенную область на новое место и после этого отпустить кнопку мыши. Если при наведении курсора мыши на границу выделенного диапазона он не превращается в наклонную стрелку с крестиком и делает невозможным перетаскивание ячеек, значит, отключена специальная настройка и ее необходимо включить, щелкнув в верхнем левом углу программы по кнопке «Office» и перейдя в Параметры Excel/Дополнительно. В группе «Параметры правки» необходимо установить флажок возле пункта «Разрешить маркеры заполнения и перетаскивание ячеек».

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

Для переноса ячеек наряду с их перемещением мышью используется вырезание с последующей вставкой. Для того чтобы вырезать/вставить ячейку или диапазон ячеек, необходимо последовательно выбрать команду «Вырезать», переместить курсор на новое место и выбрать команду «Вставить». Для этого можно использовать контекстное меню (меню, которое появляется при нажатии правой кнопки мыши в области выделенного участка), группу «Буфер обмена» на вкладке «Главная» или горячие клавиши (сочетание клавиш на клавиатуре, за которыми закреплена та или иная команда).

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

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

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

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

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

2. задавать искомое значение для ячеек;

3. задавать диапазон для поиска;

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

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

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

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

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

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

д) перемещать ячейки в заданную строку.

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

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

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

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

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

Этот адрес показан в поле имени.

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

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

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

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

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

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

Несвязные – выделенные не смежные ячейки.

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

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

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

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

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

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

Читать еще: Excel несколько файлов объединить в один

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

Редактирование листов

К основным операциям редактирования можно отнести:

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

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

Если необходимо произвести некоторые изменения данных (изменить текст или формулу), то сначала следует выделить ячейку, а затем отредактировать содержимое ячейки либо в строке формул, либо в самой ячейке, предварительно нажав клавишу F2 или дважды щелкнув мышкой по ячейке. При редактировании используются клавиши Del, Backspace .

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

Для очистки только содержимого ячейки следует нажать клавишу Delete или выбрать одноименную команду в контекстном меню.

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

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

При использовании буфера обмена необходимо выполнить следующие действия:

· выделить исходные блок ячеек для копирования (перемещения);
· выбрать соответственно команды Копировать (Вырезать) из контекстного меню.
· установить курсор в то место, куда идет копирование или перемещение;
· выполнить команду Вставить.

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

Копирование и перемещение с помощью мыши осуществляется двумя способами: с использование левой либо правой кнопок мыши (для правшей).

Правила вырезания и вставки:

Если вставляются ячейки, то в диалоговом окне Добавление ячеек задается направление смещения прилегающих ячеек (команды: добавить ячейки со сдвигом вправо или вниз).

Удаление выделенных объектов выполняется командой Удалить во вставке ячейки или контекстном меню. После этого в диалоговом окне Удаление ячеек выбирается объект для удаления и/или направление смещения прилегающих ячеек.

Закрепление областей таблицы. Для удобства просмотра больших таблиц фиксируют неподвижно некоторые части таблицы. Чаще всего фиксируют шапку или/и боковик таблицы. Остальные части таблицы можно перемещать относительно неподвижных частей.

Excel. Формулы. Копирование формул

Чтобы скопировать только формулы (без форматов), протягивание нужно осуществлять не левой, а правой кнопкой мыши. Тогда Excel выдаст контекстное меню, в котором можно указать, что именно нужно копировать: всё, только значения (этот пункт копирует и формулы) или только форматы.

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

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

Способ 3. Двойной клик по маркеру автозаполнения

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

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

Способ 4. Копирование с помощью буфера обмена

Классический способ копирования с использование команд Ctrl+C (копировать) и Ctrl+V (вставить). Выделяете ячейку или диапазон, копируете, выделяете диапазон вставки (можно несмежный, можно несколько) и вставляете. В результате вставится и формула, и форматирование.

Способ 5. Копирование с использованием Специальной вставки

Некоторые варианты вставки доступны по клику правой кнопкой мыши в виде пиктограмм быстрого действия.

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

Видеоверсию данной статьи смотрите на нашем канале на YouTube

Вопросы по Excel можно задать нашему боту обратной связи в Telegram @ExEvFeedbackBot

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

Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:

Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.

Способ 1. Абсолютные ссылки

Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:

При большом количестве ячеек этот вариант, понятное дело, отпадает - слишком трудоемко.

Способ 2. Временная деактивация формул

Выделяем диапазон с формулами (в нашем примере D2:D8)
Жмем Ctrl+H на клавиатуре или на вкладке Главная - Найти и выделить - Заменить (Home - Find&Select - Replace)

Способ 3. Копирование через Блокнот

Этот способ существенно быстрее и проще.

Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas - Show formulas) , чтобы включить режим проверки формул - в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:

Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:

Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:

Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.

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

Способ 4. Макрос

Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert - Module и скопируйте туда текст вот такого макроса:

Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

Дело в том, что остальная область меняется и она заполненна в каждом файле по-разному. Т.е. задача обстоит так:есть большое количество файлов, у которых форматы всех ячеек одинаковые (в каждом файле ещё 12 листов). Мне нужно поменять даты в двух местах. И чтобы ускорить хоть немного процесс я думал есть возможность выделить ctrl+c ячейки (на жёлтом фоне в первом посте) и повставлять их в другие файлы, но чтоб менялись только жёлтые выделенные ячейки.

Если в книге-приемнике у Вас такой же формат и желтые ячейки пустые (файл 11111), то, скопировав из файла 22222 зеленые ячейки (именно так захватилось из-за объединенных ячеек), перейдя в файл 11111, встаньте в ячейку Е2 - Спецвставка - поставьте галку "Пропускать пустые ячейки" и жмите ОК

что остальная область меняется и она заполненна в каждом файле по-разному

Если там одинаковый формат (объединение, наличие значений, при этом сами значения могут быть разными), то все нормально. Вы попробуйте, возможно, Вам понравится.

Кстати, а почему Вы не хотите просто в 11-и файлах написать прямую ссылку на 12-й? типа

Или сделать 13-й файл, в котором будете просто забивать вручную нужные данные, а в 12-и других будет ссылка на этот 13-й. что остальная область меняется и она заполненна в каждом файле по-разному

Кстати, а почему Вы не хотите просто в 11-и файлах написать прямую ссылку на 12-й? типа

Или сделать 13-й файл, в котором будете просто забивать вручную нужные данные, а в 12-и других будет ссылка на этот 13-й. _Boroda_ что остальная область меняется и она заполненна в каждом файле по-разному

Кстати, а почему Вы не хотите просто в 11-и файлах написать прямую ссылку на 12-й? типа

Или сделать 13-й файл, в котором будете просто забивать вручную нужные данные, а в 12-и других будет ссылка на этот 13-й. Автор - _Boroda_
Дата добавления - 18.01.2016 в 16:26

Да вот наказан за то, что сразу не сделал, как положено)) А теперь объём сделанных файлов очень большой и хотелось бы в след.году не играться в каждом файле отдельно, а поменял на первой вкладке и всего делов-то.

В общем не получается у меня всё равно никак. Как я делаю: в файле 1____1 выделяю верхние две жёлтых (объединённых) ячейки, зажимаю ctrl и выделяю нижние две ячейки отмеченные жёлтым, открываю другой файл с такими же форматами ячеек (но вместо значений на жёлтом фоне - пусто) и выбирая "спец.вставку" ставлю галочку "пропустить пустые" вставляю скопированные области ячеек. И вот тут всё и заканчивается. в лучшем случае просто вставляются одна под другой.

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

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

Excel 2003 (imho для данного вопроса это не важно)

Скопируйте получившиеся числа строк.

Заполните строки как вам нужно

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

Private Sub CommandButton1_Click()

Читать еще: Как в эксель защитить отдельные ячейки в excel

Скрытый текст:Sub PasteToVisible()
Dim copyrng As Range, pasterng As Range
Dim cell As Range, i As Long

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

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

Выполните указанные ниже действия.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Этот адрес показан в поле имени.

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

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

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

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

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

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

Несвязные – выделенные не смежные ячейки.

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

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

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

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

Выполните указанные ниже действия.

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

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

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

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

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

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

Копирование данных листа стандартными средствами Excel

Копировать данные в Excel можно с помощью стандартных системных средств: контекстное меню, перетягивание курсора Excel мышкой (за квадратик), комбинация клавиш CTRL+C. А можно воспользоваться удобным инструментом «Буфер обмена». В чем разница?

Копирование стандартными средствами – это возможность скопировать только один выделенный элемент (диапазон ячеек, диаграмма, таблица и т.п.). Стандартный метод позволяет перенести содержимое данных не только в приделах программ входящих в пакет MS Office, но и в другие Windows программы.

Инструмент «Буфер обмена» позволяет выделять в памяти программы до 24-х мест временного хранения содержимого данных. Таким образом, вы можете поместить в оперативную память 24 совершенно не связанных между собой элементов. Кроме того буфер обмена позволяет управлять содержимым хранилища оперативной памяти. Работа с буфером обмена ограничена в рамках программ пакета MS Office.

Нельзя вложиться в рамки одного урока, чтобы описать возможности копирования в программе Excel. Это одна из наиболее часто используемых функций в процессе работы. Сначала рассмотрим возможности перемещения данных в Excel стандартными системными средствами, а дальше оценим преимущества удобного инструмента «Буфер обмена».

Примеры копирования данных стандартными средствами Excel

Для наглядного примера рассмотрим копирование диапазонов данных.

Примечание. Другие элементы данных (диаграммы, таблицы, фигуры и т.п.) копируются аналогичным способом.

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

Как копировать таблицу в Excel?

Копировать выделенные ячейки в Excel можно несколькими способами. Создайте таблицу, так как показано на рисунке, а в ячейку C2 добавьте примечание.

Очень важно чтобы форматы ячеек отличались в исходной таблице. Содержимое ячеек не имеет значения за исключением наличия примечания.

Переместим данные диапазона B2:C3 в F3:G4.

Задание 1. Выделите диапазон B2:C3.

Перейдите на закладку инструментов «Главная» выберите «Копировать». Перейдите в ячейку F3 и нажмите на инструмент «Главная»-«Вставить».

Задание 2. Выделите диапазон ячеек B2:C3. Нажмите комбинацию клавиш CTRL+C и перейдите курсором на ячейку F3. После чего нажмите комбинацию клавиш CTRL+V.

Задание 3. По предварительно выделенному диапазону B2:C3 щелкните правой кнопкой мышки. Из появившегося контекстного меню выберите опцию «Копировать». Теперь щелкните правой кнопкой мышки по ячейке F3 и щелкните по опции контекстного меню «Вставить скопированные ячейки». Далее нажмите «ОК».

Задание 4. По-прежнему выделяем B2:C3. Далее наведите курсор мышки на рамку выделенного диапазона. Нажмите клавишу CTRL на клавиатуре. Возле курсора мышки появится значок «+». Удерживая левую клавишу мышки и клавишу CTRL, перетащите диапазон, расположив его по необходимому адресу (F3:G4). Отпустите сначала клавишу мышки, а потом CTRL.

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

Функциональное копирование смежных диапазонов ячеек

Теперь копируем смежный диапазон с нижней гранью. Для этого выделяем диапазон A4:D4. Наведите указатель на точку в нижнем правом углу курсора Excel и стрелка примет вид жирного плюса «+». Теперь удерживая левую клавишу мышки, смещаем указатель на одну ячейку вниз.

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

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

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

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

Примечание. Если данный инструмент для вас не является вспомогательным, а скорее раздражительным, то его можно отключить. Для этого зайдите в настройки программы: «Файл»-«Параметры»-«Дополнительно»-«Вырезание, копирование и вставка». Там же уберите галочку напротив «Отображать кнопку параметров вставки при вставке содержимого».

Инструмент «Формат по образцу»

Чтобы копировать формат ячейки в Excel нужно использовать инструмент «Формат по образцу» предназначен для копирования только оформления ячеек без их значений. Принцип работы этого инструмента прост. Программой выделяется место для временного хранения в оперативной памяти только форматов без значений. Это очень удобно, когда нужно скопировать оформление и сохранить при этом значения данных.

Чтобы воспользоваться данным инструментом выделяем область формата таблицы A1:D4. Дальше надо нажать «Главная»-«Формат по образцу». Курсор мышки примет значок инструмента. После чего следует щелкнуть по ячейке B7 для вставки. Как видите, переместились только форматы без значений.

Если после выделения диапазона A1:D4 сделать двойной щелчок по инструменту «Формат по образцу», то это действие позволяет зафиксировать нажатие кнопки. Теперь можно несколько раз щелкать по разным ячейкам для вставки формата в несмежные диапазоны по одному клику.

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

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

Этот адрес показан в поле имени.

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

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

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

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

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

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

Несвязные – выделенные не смежные ячейки.

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

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

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

Автоматически изменяемые диапазоны ячеек в Excel

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

Как в Excel сделать автоматическое изменение диапазона

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

Сформируйте отчет по доходности нашего объекта инвестирования, так как показано на рисунке:

Данную задачу можно было-бы решить путем суммирования целого столбца B и после появления в нем записей общая сумма изменялась бы автоматически. Но это некорректный способ решения задач в Excel. Во-первых, нам нельзя будет использовать ячейки столбца B для ввода других данных. Во-вторых, так функция суммирования будет нерационально потреблять больше памяти. Это может привести к серьезным проблемам при работе с документом. Самым рациональным решением является использование динамических имен.

Выберите инструмент «Формулы»-«Определенные имена»-«Присвоить имя».
Заполните поля диалогового окна «Создание имени» как на рисунке. Обратите внимание на то, что в поле «Диапазон:» мы используем функцию =СМЕЩ, а в одном из ее параметров используем функцию =СЧЕТ. Пример: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
Переместите курсор в ячейку D2 и введите функцию =СУММ с именем «доход» в ее параметрах.

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

Функция СМЕЩ в Excel

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

Функция =СМЕЩ определяет наш диапазон в зависимости от количества заполненных ячеек в столбце B. 5 параметров функции =СМЕЩ(начальная ячейка; смещение размера диапазона по строкам; смещение по столбцам; размер диапазона в высоту; размер диапазона в ширину):

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

Последние 2 параметра функции являются необязательными. Если их не заполнять, то диапазон будет состоять из 1-ой ячейки. Например: =СМЕЩ(A1;0;0) – это просто ячейка A1, а параметр =СМЕЩ(A1;2;0) ссылается на A3.

Теперь разберем функцию: =СЧЕТ, которую мы указывали в 4-ом параметре функции: =СМЕЩ.

Что определяет функция СЧЕТ

Функция =СЧЕТ($B:$B) автоматически считает количество заполненных ячеек в столбце B.

Таким образом, мы с помощью функции =СЧЕТ() и =СМЕЩ() автоматизируем процесс формирования диапазона для имени «доход», что делает его динамическим. Теперь еще раз посмотрим на нашу формулу, которой мы присвоили имя «доход»: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Читать данную формулу следует так: первый параметры указывает на то, что наш автоматически изменяемый диапазон начинается в ячейке B2. Следующие два параметра имеют значения 0;0 – это значит, что динамический диапазон не смещается относительно начальной ячейки B2. А увеличивается только его размер по вертикали, о чем свидетельствует 4-тый параметр. В нем находится функция СЧЕТ и она возвращает число равно количеству заполненных ячеек в столбце B. Соответственно количество ячеек по вертикали в диапазоне будет равно числу, которое нам даст функция СЧЕТ. А за ширину диапазона у нас отвечает последний 5-тый параметр, где находиться число 1.

Благодаря функции СЧЕТ мы рационально загружаем в память только заполненные ячейки из столбца B, а не весь столбец целиком. Данный факт исключает возможные ошибки связанные с памятью при работе с данным документом.

Динамические диаграммы в Excel

У нас есть динамическое имя, теперь создадим динамическую диаграмму для данного типа отчета:

Используя наше динамическое имя «доход» нам удалось создать автоматически изменяемую динамическую диаграмму, которая сама добавляет и отображает новые данные в отчете.

Именованный диапазон в Excel

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

Имя ячейки

Длина имени ограничена 255 символами, что более чем достаточно. Также в имени не должно быть пробелов, поэтому если оно состоит из нескольких слов, то их можно разделять знаком подчеркивания.

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

Именованный диапазон

Аналогичным образом можно задать имя и для диапазона ячеек, то есть выделим диапазон (1) и в поле имени укажем его название (2):

Далее это название можно использовать в формулах, например, при вычислении суммы:

Также создать именованный диапазон можно с помощью вкладки Формулы , выбрав инструмент Задать имя .

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

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

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

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

Именованный диапазон из таблицы

Если же диапазон значений имеет заголовки или речь идет о таблице, то стоит воспользоваться инструментом Создать из выделенного . Как понятно из его названия, предварительно необходимо выделить диапазон или таблицу (1). Затем указываем место, в котором находятся заголовки (3).

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

При этом, если заголовки будут состоять из нескольких слов, то Эксель автоматически подставит знак подчеркивания между словами.

Использование именованных диапазонов

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

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

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

Читать еще: Поиск решения в excel 2016 где находится

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

Ну и более наглядно и подробно об именованных диапазонах смотрите в видео:

При задании имен помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

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

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

Минусы такого способа:

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

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

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

В итоге должно получиться что-то вроде этого:

Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:

Microsoft Excel

трюки • приёмы • решения

Как распечатать несмежные диапазоны таблицы Excel на одной странице

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

Нажмите Ctrl во время выбора диапазонов для печати, а затем выберите Разметка страницы ► Параметры страницы ► Область печати ► Задать. После того как вы определили область печати, вы можете использовать команду Разметка страницы ► Параметры страницы ► Область печати ► Добавить в область печати для указания дополнительных диапазонов для печати.

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

Выберите диапазон.
Нажмите Ctrl+C.
Активизируйте другую ячейку.
Выполните команду Главная ► Буфер обмена ► Вставить ► Связанный рисунок.

К счастью, в Excel есть гораздо более эффективный инструмент для такой задачи: Камера (который по некоторым причинам Microsoft держит довольно скрытым).

Использование инструмента Камера

Чтобы добавить инструмент Камера на панель быстрого доступа, выполните следующие действия.

Инструмент Камера создает связанный рисунок диапазона ячеек. Поэтому если данные в связанном диапазоне изменяются, рисунок обновляется автоматически. На рис. 185.1 показан лист, с которого нужно напечатать два диапазона (А3:С10 и E13:G20) на отдельной странице.

Рис. 185.1. Обычно нет возможности распечатать диапазоны А3:С10 и E13:G20 на одной странице

Съемка камерой

Далее я расскажу вам, как использовать инструмент Камера для печати нескольких диапазонов на одном листе бумаги.

Убедитесь, что инструмент Камера доступен на панели быстрого доступа. Выполните шаги из предыдущего подраздела, если это не так.
Вставьте новый лист (Лист2), который будет содержать связанные рисунки.
Выберите диапазон А3:С10 и щелкните на инструменте Камера.
Активизируйте лист Лист2 и нажмите кнопку для вставки связанного рисунка.
Вернитесь к предыдущему листу, выберите E13:G20 и нажмите кнопку инструмента Камера.
Активизируйте Лист2 и вставьте связанный рисунок.
Используйте мышь, чтобы разместить связанные рисунки так, как вам нужно.

Вы можете отключить отображение сетки на листе, который содержит связанные рисунки (рис. 185.2). Если данные листа Лист1 изменятся, то связанные рисунки также обновятся. При печати листа Лист2 оба диапазона окажутся на одной странице.

Рис. 185.2. Использование связанных рисунков позволяет печатать два диапазона ячеек на одной странице

Читайте также: