Poldet1.ru

Пул Дет №1
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как преобразовать лист или ячейки Excel в таблицу HTML?

Конвертирование HTML в форматы Microsoft Excel

HTML в Microsoft Excel

Необходимость преобразования HTML-таблицы в Excel может возникнуть в нескольких случаях. Может потребоваться преобразование данных веб-страниц из Интернета или HTML-файлов, которые использовались локально для других целей специальными программами. Часто конвертация осуществляется в пути. То есть таблица должна быть сначала преобразована из HTML в XLS или XLSX, затем обработана или изменена и, наконец, преобразована обратно в файл с тем же расширением, чтобы она могла выполнять свою первоначальную функцию. Действительно, работать с таблицами в Excel намного проще. Давайте узнаем, как преобразовать электронную таблицу HTML в Excel.

Парсинг нетабличных данных с сайтов

Excel может без проблем загружать табличные данные из Интернета. Надстройка Excel Power Query упрощает выполнение этой задачи. На вкладке Данные выберите команду Данные — Из Интернета, вставьте адрес нужной веб-страницы (например, основные показатели Центрального банка) и нажмите OK:

Импорт данных с веб-страницы через Power Query

Power Query автоматически распознает все таблицы на веб-странице и отобразит их список в окне браузера:

Выбираем таблицу на сайте для импорта

Следующим шагом будет выбор нужной таблицы и загрузка ее в Power Query для дальнейшей обработки (кнопка Convert Data) или непосредственно в лист Excel (кнопка Load).

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

К сожалению, существует множество сайтов, на которых при попытке загрузить Power Query таким образом, Power Query «не видит» таблиц с нужными данными, т.е. в окне Навигатора просто нет таблиц 0, 1, 2. или среди них нет таблицы с нужной нам информацией. Это может быть вызвано несколькими факторами, но самый распространенный — дизайнер сайта использовал в HTML-коде страницы не стандартную структуру тегов, а ее аналог, вложенные теги-контейнеры. Эта техника очень распространена на сайтах, но, к сожалению, Power Query не может ее распознать и загрузить данные.

Читайте так же:
Как разбить большую таблицу на несколько небольших таблиц в Excel?

Однако есть способ обойти это ограничение 😉

Для тренировки попробуем загрузить цены и описания товаров с торговой площадки Wildberries — например, книг из раздела детективов:

Детективы на Wildberries

Загружаем HTML-код вместо веб-страницы

Сначала используйте тот же метод: выберите команду Из Интернета на вкладке Данные и введите адрес нужной страницы:

После этого вы увидите окно Навигатора, в котором нет никаких полезных таблиц, кроме непонятной Документ:

Навигатор без таблиц

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

Удаляем ненужный шаг Навигация

. а затем нажмите на значок шестеренки справа от шага «Источник», чтобы открыть его параметры:

Меняем тип файла

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

После нажатия кнопки OK вы увидите этот HTML код (он довольно длинный, но не пугайтесь):

Исходный код страницы в Power Query

Ищем за что зацепиться

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

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

Изучаем исходный код

  • Строки с ценами всегда содержат этикетку нижней цены
  • Строки с марками всегда содержат этикетку марки c-text-sm
  • Название товара можно найти с помощью этикетки названия товара c-text-sm.

Иногда процесс поиска можно значительно упростить, используя инструменты поиска кода, доступные во всех современных браузерах. Щелкнув правой кнопкой мыши на элементе веб-страницы (например, на цене или описании товара), вы можете выбрать Inspect из контекстного меню, а затем отобразить код в удобном окне прямо рядом с содержимым страницы:

Читайте так же:
Как применить шаблон к существующей диаграмме / книге в Excel?

Инспектирование кода HTML на веб-странице

Фильтруем нужные данные

Теперь давайте отфильтруем нужные нам строки в коде страницы в соответствии с найденными символами самым обычным образом. Для этого в окне Power Query [1] выберите Text Filters — Contains, переключитесь в режим Advanced [2] и введите наши критерии:

Фильтруем нужные строки

Используйте кнопку Добавить предложение [3] для добавления условий. И не забудьте установить для всех условий значение OR вместо AND в выпадающих списках слева [4] — иначе фильтрация просто не будет работать.

После нажатия кнопки OK на экране останутся только строки, содержащие необходимую нам информацию:

Отобранные строки

Чистим мусор

Просто удалите все это удобным для вас способом (их много). Например, таким образом:

  1. Удалить заменой на пустоту начальный тег: <span > через команду Главная — Замена значений (Home — Replace values) .
  2. Разделить получившийся столбец по первому разделителю » > » слева командой Главная — Разделить столбец — По разделителю (Home — Split column — By delimiter) и затем ещё раз разделить получившийся столбец по первому вхождению разделителя » < » слева, чтобы отделить полезные данные от тегов:

Отделяем данные от HTML-тегов

Зачищенные данные

Разбираем блоки по столбцам

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

Вы можете сделать это преобразование очень легко, буквально одной строкой кода на встроенном в Power Query языке M. Для этого нажмите на кнопку fx на панели формул (если вы ее не видите, активируйте ее на вкладке Вид) и введите следующее утверждение:

= Table.FromRows(List.Split( #»Замененное значение1″ [Column1.2.1] , 3 ))

Здесь — Список. В Шаге № «Подставленное значение1» мы разбиваем столбец с именем Column1.2.1 на три части одинаковых ячеек, и так далее для остальной части Таблицы. Вложенный список вложенных списков преобразуется обратно в таблицу, уже имеющую три столбца:

Разобранная на 3 столбца таблица

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

3. Используйте онлайн-инструменты для преобразования Excel в таблицу HTML.

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

Метод 1. Использование инструментов преобразования

Шаг 2: С помощью кнопки Обзор перейдите к документу Excel, который необходимо преобразовать. Вы также можете перетащить файл.

Excel в HTML 11

Кнопка «Конвертировать» активирована. Нажмите ее, чтобы начать процесс преобразования.

Excel в HTML 12

Шаг 4: Нажмите на кнопку Загрузить файл, чтобы загрузить его на свой компьютер.

Excel в HTML 13

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

Метод 2: использование инструментов Beautify

Этот веб-сайт генерирует более чистый код, чем приведенный выше. Здесь вы найдете базовую таблицу.

Это делается следующим образом.

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

Excel в HTML 14

Сгенерированный HTML-код появится в поле ниже. Вы можете скопировать и вставить его или нажать на кнопку «Загрузить», чтобы сохранить его в виде TXT-файла.

Excel в HTML 15

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

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

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

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

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

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

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

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

Ввод формулы

Чтобы ввести формулу, перейдите на лист Sales Amounts и щелкните по ячейке B5.

Нажмите Enter.

entering the vlookup functionentering the vlookup functionentering the vlookup functionВвод функции VLOOKUP

Результат должен быть равен 40. Для получения дополнительной информации еще раз щелкните на B5. Поместите курсор мыши на точку автозаполнения в правом нижнем углу ячейки, чтобы курсор изменил форму на перекрестие.

mouse pointer on the autofill dotmouse pointer on the autofill dotmouse pointer on the autofill dotКогда вы поместите курсор мыши на точку в правом нижнем углу ячейки, он превратится в перекрестие Автозаполнения.

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

worksheet with data after using autofillworksheet with data after using autofillworksheet with data after using autofillДважды кликните перекрестием Автозаполнения, чтобы скопировать формулу ниже по столбцу

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector