Импорт HTML-файлов

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

#ИмпортДанных

Импортирование файлов XML

Формат XML (Extensible Markup Language) - это текстовый формат, предназначенный для описания структур данных. При этом данные заключаются в специальные теги, которые предназначены для описания данных.

Excel может открывать XML-файлы, и простые файлы будут корректно отображаться при приложении минимума усилий с вашей стороны. Однако для отображения сложных XML-файлов потребуются определенные усилия.

#ИмпортДанных

​​Импорт файла или его открытие

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

Еще одним примером файлов, которые не открываются в Excel напрямую, являются файлы формата XML. При открытии XML-файла вам будет предложено сделать выбор: любо открыть его как рабочую книгу в режиме только для чтения, либо импортировать его в таблицу.
Текстовые файлы всегда открываются напрямую. Excel понимает структуру CSV- файлов, поэтому при их открытии вам не будет задано никаких дополнительных вопросов.
В случае текстовых файлов с разделением полей символом табуляции либо файлов с фиксированной длиной полей можно будет воспользоваться инструментом Мастер текстов (импорт), который поможет определить структуру импортируемых данных.

Когда файл открывается напрямую, в строке заголовка Excel выводится его имя. В примере показана строка заголовка программы после открытия файла с именем Реализация по месяцам.txt. Когда же команда Файл => Открыть на самом деле осуществляет импорт данных, они всегда импортируются в новую рабочую книгу. В этом случае в строке заголовка отображается стандартное название новой рабочей книги Excel, например Книга1.

#ИмпортДанных

​​Импорт текстового файла. Мастер текстов (импорт)

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

Начиная с версии Excel 2019 импорт текстовых файлов рекомендуется выполнять с использованием средства Скачать и преобразовать, а не устаревшего инструмента Мастер текстов (импорт). Средство Скачать и преобразовать представляет собой мощный инструмент, который мы рассмотрим далее в темах Power Pivot и Power Query.

Чтобы активизировать функцию устаревшего мастера импорта выберите команду Файл => Параметры => Данные и установите флажок Из текста (прежних версий).

#ИмпортДанных

​​Импорт текстового файла

1. Выберите команду Данные => Получить и преобразовать данные => Получить данные => Устаревшие мастеры => Из текста (прежних версий). Откроется диалоговое окно Импорт текстового файла.
2. Перейдите в папку, содержащую нужный текстовый файл.
3. Выберите файл, а затем щелкните на кнопке Импорт. На экране откроется диалоговое окно Мастер текстов (импорт).
4. В первом окне Мастер текстов (импорт) задайте способ импортирования данных: установите переключатель Укажите формат данных в положение С разделителями и убедитесь, что флажок Мои данные содержат заголовки сброшен. Щелкните на кнопке Далее.
5. В следующем окне мастера укажите символ-разделитель: для CSV-файлов установите флажок запятая.
6. Щелкните на кнопке Готово. Откроется диалоговое окно Импорт данных.
7. В диалоговом окне Импорт данных задайте размещение импортируемых данных. Это может быть ячейка на существующем рабочем листе или новый рабочий лист.
8. Щелкните на кнопке ОК, и Excel выполнит импортирование данных.

#ИмпортДанных

​​Методы предварительной подготовки данных. Удаление повторяющихся строк

Зачастую данные могут поступать одновременно из разных источников, и в этом случае высока вероятность появления дублирующихся строк. Начиная с версии Excel 2007 появилась удобная команда Удалить дубликаты.

Для того чтобы удалить дубликаты, установите курсор в любую ячейку в обрабатываемом диапазоне данных. Затем выберите команду Данные => Работа с данными => Удалить дубликаты. Откроется диалоговое окно Удалить дубликаты.
В диалоговом окне Удалить дубликаты приводится список всех столбцов в вашем диапазоне данных или в таблице. Установите флажок рядом со столбцами, значения из которых должны быть учтены в процессе поиска повторяющихся значений. Чаще всего выбираются все столбцы, и такая установка задается по умолчанию. Щелкните на кнопке ОК, и программа Excel просмотрит все строки с повторяющимися значениями и выведет сообщение с информацией о том, сколько дубликатов было удалено.
Если программа Excel удалит слишком много строк, есть возможность отменить действие процедуры удаления дубликатов. Для этого щелкните на кнопке Отменить удаление дубликатов на панели быстрого доступа (или нажмите комбинацию клавиш <Ctrl+Z>).
Если задать в диалоговом окне Удалить дубликаты все столбцы, Excel удалит строку только в том случае, если повторяются значения во всех столбцах. Если в вашей ситуации учитывать значения, находящиеся в определенных столбцах, не нужно, для таких столбцов просто сбросьте их флажки в диалоговом окне Удалить дубликаты. Например, если каждая строка включает некий уникальный идентификационный код, в этом столбце Excel никогда не найдет ни одного повторяющегося значения, поэтому флажок такого столбца в диалоговом окне Удалить дубликаты следует сбросить. При обнаружении повторяющихся строк первая строка будет сохранена, а все последующие дубликаты будут удалены.

📌 Если ваши данные размещены в таблице, для выполнения этой задачи можно воспользоваться командой Работа с таблицами => Конструктор => Инструменты => Удалить дубликаты. Эти две команды работают абсолютно одинаково.

#ИмпортДанных

​​Методы предварительной подготовки данных. Обнаружение повторяющихся строк

Есть еще один метод, которым можно воспользоваться, если вам нужно выделить повторяющиеся строки, чтобы проанализировать их, а не удалять сразу. В отличие от вышеописанного метода, этот метод работает с реальными, а не с отображаемыми значениями.
Создадим справа от анализируемых данных формулу, конкатенирующую (т.е. объединяющую в одно целое) все ячейки, расположенные слева от нее. В приведенных внизу формулах предполагается, что данные хранятся в столбцах A:E. Для этого введите в ячейку F2 следующую формулу:


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


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

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


#ИмпортДанных

​​Методы предварительной подготовки данных. Текст по столбцам

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

Cначала убедитесь в том, что у столбца, содержащего разбиваемые строки данных, справа достаточно пустых столбцов, чтобы разместить извлеченные из строк данные. Затем выберите анализируемые данные и выполните команду Данные => Работа с данными => Текст по столбцам. Excel откроет окно мастера Мастер распределения текста по столбцам, выводящего последовательность диалоговых окон, которые помогут вам преобразовать один столбец данных в несколько.

▪️ С разделителями. Разбиваемые данные разделены разделителями: запятыми, косыми чертами, пробелами или другими символами.
▪️ Фиксированной ширины. Все компоненты в строке имеют одинаковую длину, т.е каждому из них отведено одно и то же количество символов.
Установите переключатель Укажите формат данных в требуемое положение и щелкните на кнопке Далее, чтобы перейти к этапу 2, который зависит от выбора, сделанного на этапе 1.
Работая с данными, разделенными разделителями, задайте символ (или символы) разделителя. Вы увидите предварительный вид ожидаемого результата. При работе с данными фиксированной длины задайте ширину столбцов прямо в окне предварительного просмотра.
Если полученный результат вас полностью удовлетворяет, щелкните на кнопке Далее, чтобы перейти к этапу 3. На этом этапе щелкните в окне предварительного просмотра на заголовке каждого столбца и с помощью переключателя Формат данных столбца выберите для него требуемый формат данных. В завершение щелкните на кнопке Готово, и Excel разобьет данные в соответствии с заданным вами правилом.

#ИмпортДанных

​​Методы предварительной подготовки данных. Мгновенное заполнение

Мастер распределения текста по столбцам хорошо работает для многих типов данных. Но иногда можно столкнуться с данными, которые не могут быть разобраны с помощью этого мастера. Например, мастер распределения текста по столбцам оказывается бесполезным в случае данных переменной длины, которые не имеют разделителей. В подобной ситуации сэкономить довольно много времени поможет команда Мгновенное заполнение. Однако следует иметь в виду, что она правильно работает, только если данные однотипные.
При использовании команды мгновенного заполнения данных для их извлечения (а также объединения) используется специальный шаблон. Для этого достаточно ввести несколько образцов в столбец, расположенный рядом с данными, и выбрать команду Данные => Работа с данными => Мгновенное заполнение (или нажать комбинацию клавиш <Ctrl+E>). Excel проанализирует эти примеры и попытается заполнить остальные ячейки согласно приведенному образцу. Если Excel неправильно распознает предложенный вами шаблон, нажмите комбинацию клавиш <Ctrl+Z>, введите еще один-два образца и попробуйте снова.

#ИмпортДанных

​​Методы предварительной подготовки данных. Мгновенное заполнение

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

Для этого достаточно ввести несколько образцов в столбец, расположенный рядом с данными, и выбрать команду Данные => Работа с данными => Мгновенное заполнение (или нажать комбинацию клавиш <Ctrl+E>)

#ИмпортДанных

​​​​Методы предварительной подготовки данных. Изменение регистра символов

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

Для этой цели можно использовать три функции:
▪️ =ПРОПИСН() преобразует все символы текстовой строки В ПРОПИСНЫЕ
▪️ =СТРОЧН() преобразует все символы текстовой строки в строчные
▪️ =ПРОПНАЧ() преобразует первую букву каждого слова текстовой строки В Прописную

#ИмпортДанных

​​Методы предварительной подготовки данных. Удаление лишних пробелов

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

Можно создать формулу, в которой функция =СЖПРОБЕЛЫ() будет использоваться для удаления всех начальных и завершающих пробелов, а также для замены всех последовательностей из нескольких пробелов одним пробелом.

#ИмпортДанных

​Методы предварительной подготовки данных. Объединение столбцов

Для совмещения в формуле данных из двух и более столбцов можно воспользоваться функцией =СЦЕП().

Зачастую необходимо вставлять пробел между содержимым ячеек, например если столбцы содержат имя, отчество и фамилию. Конкатенация с помощью функции =СЦЕП() в результате дает что-то вроде ИванИвановичИванов. Воспользуемся функцией =ОБЪЕДЕНИТЬ(), чтобы в результат добавить пробелы (т.е. чтобы получилось Иван Иванович Иванов):

=ОБЪЕДИНИТЬ(" ";ИСТИНА;A2:C2)

Первый аргумент функции объединить задает разделитель, который будет вставляться между значениями в отдельных ячейках. Второй аргумент, когда он имеет значение ИСТИНА, определяет требование игнорировать пустые ячейки. Если же он имеет значение ЛОЖЬ, то на месте пустых ячеек будет стоять два разделителя подряд.

#ИмпортДанных

​Методы предварительной подготовки данных. Переупорядочение столбцов

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

Ниже описан еще более простой способ.
1. Щелкните на заголовке столбца, который будет переноситься.
2. Выберите команду Главная => Буфер обмена => Вырезать.
3. Щелкните на заголовке столбца, расположенного справа от целевого столбца.
4. Щелкните правой кнопкой мыши и выберите в появившемся контекстном меню Вставить вырезанные ячейки.

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

#ИмпортДанных

​​Методы предварительной подготовки данных. Заполнение пропусков в импортированном отчете

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

Если записей не много, можно ввести недостающие значения в ячейки вручную, воспользовавшись командой Главная => Редактирование => Заполнить => Вниз или комбинацией клавиш <Ctrl+D>. Но если у вас большой список, то воспользуйтесь следующим приемом:
1. Выделите имеющий пропуски диапазон (например, А2:А17).
2. Выберите команду Главная => Редактирование => Найти и выделить => Выделить группу ячеек, чтобы открыть одноименное диалоговое окно.
3. Выберите вариант Пустые ячейки и щелкните на кнопке ОК. В результате этого действия в исходной выборке будут выделены все пустые ячейки.
4. В строке формул введите знак равенства (=) и адрес первой ячейки с информацией в исходном столбце (в нашем случае это =А2) и нажмите комбинацию клавиш <Ctrl+Enter>.
5. Еще раз выделите исходный диапазон и, для того чтобы скопировать выделенное, нажмите <Ctrl+C>.
6. Для преобразования формул в значения выберите команду Главная => Буфер обмена => Вставить значения.

#ИмпортДанных

​​Методы предварительной подготовки данных. Перевод вертикально расположенных данных в горизонтально расположенные

В примере приведен довольно часто встречающийся при импортировании файлов тип расположения данных. Каждая запись состоит из трех последовательных ячеек, расположенных в одном столбце и содержащих значения полей ФИО, Отдел и Расположение.

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

Есть несколько способов преобразования данных такого типа, но мы опишем самое простое решение. Оно требует очень незначительной подготовительной стадии, а вся задача выполняется с помощью одной формулы, которая копируется в диапазон.
Начнем с создания неких вертикальных и горизонтальных цифровых «заголовков». В столбце С указаны числа, которые соответствуют первой строке каждого элемента данных (в нашем случае это будет столбец ФИО). В этом примере в столбец С помещены следующие значения: 1, 4, 7,10,13,16 и 19.
Горизонтальный диапазон заголовков состоит из последовательных целых чисел, начиная с 1. В этом примере каждая запись содержит три ячейки данных, поэтому горизонтальный заголовок содержит 1,2 и 3.
А вот и формула, которая будет помещена в ячейку D2:

=СМЕЩ($A$1;$C2+D$1-2;0)

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

#ИмпортДанных

​Методы предварительной подготовки данных. Поиск текста в списке

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

=ЕСЛИ(СЧЁТЕСЛИ($F$2:$F$22;B2)>0;"Исключен";"")

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

#ИмпортДанных

​Методы предварительной подготовки данных. Классификация значений

Часто случается так, что в вашем распоряжении имеются значения, которые каким-либо образом должны группироваться. Например, если собираются данные о возрасте людей, может возникнуть необходимость в классификации их по возрастным группам, например 17 и моложе, 18-24,25-34 и т.д.

Проще всего это реализуется с помощью таблиц просмотра. В примере показано, что значения возраста людей хранятся в столбце А, а их классификация - в столбце В. Для заполнения значениями столбца В используется таблица просмотра, хранящаяся в диапазоне D2:E9. В ячейку В2 была занесена приведенная ниже формула, которая затем была скопирована в расположенные под ней ячейки.

=ВПР(A2;$D$2:$E$9;2)

#ИмпортДанных

​Методы предварительной подготовки данных. Классификация значений

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

Таблица просмотра, состоящая из двух столбцов, размещается в диапазоне D2:E84. Хранящаяся в ячейке В2 формула копируется в ячейки, расположенные снизу от нее:

=ВПР(A2;$D$2:$E$84;2;ЛОЖЬ)

📌 Еще одна выгода, которую можно извлечь при использовании функции =ВПР(), заключается в том, что она возвращает значение #Н/Д, если соответствие найдено не было. Это удобно для выявления ошибок в написании названий регионов (в данном примере). То, что последним аргументом передается значение ЛОЖЬ, отражает тот факт, что требуется обеспечить точное соответствие названий.

#ИмпортДанных

​Средства проверки данных

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

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

#ПроверкаДанных