Как вставить данные в excel

Microsoft Excel

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

Как импортировать данные из веб-страниц в таблицу Excel

В этом приеме описывается три способа получить данные, содержащиеся в веб-странице: вставить статическую копию информации; создать обновляемую ссылку на сайт; открыть страницу непосредственно в Excel.

Вставка статической информации

Один из способов получить данные из веб-страницы на лист — просто выделить текст в браузере, нажать Ctrl+C, чтобы скопировать его в буфер обмена, а затем вставить текст в таблицу. Результат может быть разным, в зависимости от того, какой браузер вы используете. Если это Internet Explorer, то вставленные данные будут, вероятно, очень похожи на оригинал — в комплекте с настройками форматирования, гиперссылками и графикой.

Если вы используете браузер, отличный от Internet Explorer, то, выбрав Главная ► Буфер обмена ► Вставить, можно вставить все, что вы скопировали с веб-страницы, в одну ячейку, а это, скорее всего, не то, чего вы хотите. Решение состоит в том, чтобы выбрать команду Главная ► Буфер обмена ► Вставить ► Специальная вставка, а затем пробовать различные варианты вставки.

Вставка обновляемой информации

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

Рис. 176.1. Этот сайт содержит информацию, которая часто меняется

1. Выберите Данные ► Получение внешних данных ► Из Интернета для открытия диалогового окна Создание веб-запроса.
2. В поле Адрес введите URL сайта и нажмите кнопку Пуск. Для этого примера URL-адрес веб-страницы, показанной на рис. 176.1, будет таким: http://cbr.ru. Обратите внимание, что диалоговое окно Создание веб-запроса содержит мини-браузер (Internet Explorer). Вы можете переходить по ссылкам и посещать сайты, пока не найдете данные, которые вас заинтересуют. Когда веб-страница отображается в окне Создание веб-запроса, вы видите одну или несколько желтых стрелок, которые соответствуют различным элементам на веб-странице.
3. Щелкните на желтой стрелке, и она превратится в зеленый флажок, который указывает, что данные этого элемента будут импортированы. Вы можете импортировать столько элементов, сколько нам нужно. Для этого
   примера я щелкну на стрелке рядом с таблицей курсов.
4. Нажмите кнопку Импорт для открытия диалогового окна Импорт данных.
5. В окне Импорт данных укажите место для импортированных данных. Это может быть ячейка в существующем или новом листе.
6. Нажмите кнопку ОК, и Excel импортирует данные (рис. 176.2).

Рис. 176.2. Данные, импортированные из веб-страницы

По умолчанию импортированные данные — это веб-запрос. Чтобы обновить информацию, щелкните правой кнопкой мыши на любой ячейке импортированного диапазона и выберите в контекстном меню команду Обновить. Если вы не хотите создавать обновляемый запрос, укажите это в шаге 5 предыдущего списка действии. В окне Импорт данных нажмите кнопку Свойства и снимите флажок сохранить определение запроса.

Открытие веб-страницы напрямую

Еще один способ получить данные веб-страницы на лист — открыть URL-адрес напрямую, с помощью команды Файл ► Открыть. Просто введите полный URL-адрес в поле Имя файла и нажмите кнопку Открыть. Результат будет отличаться в зависимости от того, какая разметка у веб-страницы, но в большинстве случаев он вас удовлетворит. Иногда таким способом извлекается довольно много посторонней информации.

Параметры вставки

По умолчанию при копировании (или вырезании) и вставке в Excel все элементы исходной ячейки или диапазона — данные, форматирование, формулы, проверка, примечания — вставляются в конечные ячейки. Это происходит при нажатии клавиш CTRL + V для вставки. Так как это может быть не так, в зависимости от того, что вы хотите скопировать, вы можете использовать множество других параметров вставки.

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

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

Совет: Скопировать формулы в смежные ячейки листа также можно с помощью маркера заполнения.

Параметры меню «Вставка» (на ленте)

На вкладке Главная нажмите кнопку Вставить и выберите нужный вариант вставки. Например, чтобы вставить только форматирование из скопированной ячейки, нажмите кнопку форматирование . В таблице ниже приведены пункты меню «Вставить»:

Вставка всего содержимого скопированных ячеек.

Сохранить ширину столбцов оригинала

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

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

Формула (-ы) без форматирования и примечаний.

Результаты формулы без форматирования и примечаний.

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

Значения и исходное форматирование

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

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

Вставка скопированного изображения.

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

Команда »Специальная вставка»

Чтобы использовать параметры окна «Специальная вставка», выберите Главная > Вставить > Специальная вставка.

Сочетание клавиш: CTRL+ALT+V.

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

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

Вставка всего содержимого ячеек и форматирование копируемых данных.

Вставка только формул копируемых данных, введенных в строке формул.

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

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

Примечания и заметки

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

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

с исходной темой

Вставка всего содержимого ячеек в формат темы документа, примененный к копируемым данным.

Вставка всего содержимого ячеек и форматирования, примененного к скопированной ячейке, кроме границ.

Вставка ширины одного скопированного столбца или диапазона столбцов в другой столбец или диапазон столбцов.

формулы и форматы чисел

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

значения и форматы чисел

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

Все условные форматы слияния

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

Вы также можете указать математическую операцию, которую нужно применить к копируемым данным.

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

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

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

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

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

Пропуск пустых значений

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

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

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

Проверка и исправление ссылок на ячейки во вставленной формуле

Примечание: Ссылки на ячейки автоматически корректируются при вырезании (не копировании) и вставке формул.

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

Например, если вы копируете формулу в ячейку a1 и вставляете ее в две ячейки вниз и вправо (C3), ссылки на ячейки в вставленной формуле будут изменяться следующим образом:

$A$1 (абсолютный столбец и абсолютная строка)

A$1 (относительный столбец и абсолютная строка)

$A1 (абсолютный столбец и относительная строка)

A1 (относительный столбец и относительная строка)

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

Выделите ячейку с формулой.

В строке формул выберите ссылку, которую вы хотите изменить.

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

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

При копировании в Excel в Интернете вы можете выбрать параметры вставки в конечных ячейках.

На вкладке Главная нажмите кнопку Вставить и выберите нужный вариант вставки. Например, чтобы вставить только форматирование из скопированной ячейки, нажмите кнопку вставить форматирования . В таблице ниже приведены пункты меню «Вставить»:

Вставка всего содержимого скопированных ячеек.

Формула (-ы) без форматирования.

Результаты формулы без форматирования.

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

Вставить и транспонировать

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

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

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

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

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

Приведу три способа Автоматического переноса данных с одного листа программы «Эксель» в другой.

Первый, самый простой и примитивный способ связи двух таблиц на разных листах документа -вставка данных при помощи опции специальной вставки.

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

Первый шаг.

Необходимо выделить ту таблицу, из которой будут транслироваться данные во вторую таблицу.

Второй шаг.

Копировать информацию сочетанием клавиш ctrl+C или вызвав контекстное меню правой кнопкой мыши и кликнув по пункту меню «Копировать»

Третий шаг.

Перейти на лист документа «Excel», в который Вы планируете транслировать информацию из первой таблицы.

Четвертый шаг.

Поставить курсор в первую (левую верхнюю) ячейку таблицы и выбрать в меню «Вставка» пункт «Вставить связь». В некоторых версиях программы «Excel» этот пункт находится в меню «Специальная вставка»

После вставки связи следует отформатировать вид ячеек – привести их к надлежащему виду.

Результат вставки связи

Второй способ переноса данных из одной таблицы в другую — это использование сводных таблиц в программе «Excel».

При использовании данного метода роль второй таблицы («реципиента») играет сама сводная таблица.

Как обновить сводную таблицу

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

О том, как в «Эксель» создавать сводные таблицы подробно написано в статье:

Как делать сводные таблицы в программе «Excel» и для чего они нужны.

Третий способ самый эффективный и наиболее автоматизированный — это использование меню надстройки «Power Query».

Правда нужно отметить, что этот способ подходит только пользователям Excel 2016 и пользователям Excel 2013и выше с установленной надстройкой «Power Query».

Смысл способа в следующем:

Необходимо открыть вкладку «Power Query». В разделе «Данные Excel» нажимаем кнопку (пиктограмму) «Из таблицы».

Из таблицы -Power Query

Далее нужно выбрать диапазон ячеек, из которых нужно «притянуть» информацию и нажимаем «Ок».

Источник данных для запроса Power Query

После выбора области данных появится окно настройки вида новой таблицы. В этом окне Вы можете настроить последовательность вывода столбцов и удалить ненужные столбцы.

После настройки вида таблицы нажмите кнопку «Закрыть и загрузить»

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

Обновление запроса в PowerQuery

Как копировать таблицу в Excel сохраняя формат ячеек

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

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

Как скопировать таблицу с шириной столбцов и высотой строк

Допустим, у нас есть такая таблица, формат которой необходимо сохранить при копировании:

При копировании на другой лист привычным методом Ctrl+C – Ctrl+V. Получаем нежелательный результат:

Поэтому приходится вручную «расширять» ее, чтобы привести в удобный вид. Если таблица большая, то «возиться» придется долго. Но есть способ существенно сократить временные потери.

Способ1:Используем специальную вставку

1. Выделяем исходную таблицу, которую нам необходимо скопировать, нажимаем на Ctrl+C.
2. Выделяем новую (уже скопированную) таблицу, куда нам необходимо формат ширины столбцов и нажимаем на ячейку правой кнопкой мыши, после чего в выпадающем меню находим раздел «Специальная вставка».
3. Выбираем в нем необходимый пункт напротив опции «ширина столбцов», нажимаем «ОК».

Она получила исходные параметры и выглядит идеально точно.

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

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

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

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

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

Полезный совет! Самый быстрый способ скопировать сложную и большую таблицу, сохранив ее ширину столбцов и высоту строк – это копировать ее целым листом. О том, как это сделать читайте: копирование и перемещение листов.

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

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

Чтобы решить такую задачу следует выполнить 2 операции, используя специальную вставку в Excel.

Операция I:

1. Выделяем исходную таблицу с формулами и копируем.
2. В месте где нужно вставить диапазон данных со значениями (но уже без формул), выбираем опцию «значения». Жмем ОК.

Операция II:

Так как скопированный диапазон у нас еще находится в буфере обмена после копирования, то мы сразу еще раз вызываем специальную вставку где выбираем опцию «форматы». Жмем ОК.

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

Полезный совет! Чтобы не выполнять вторую операцию можно воспользоваться инструментом «формат по образцу».

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