Получив чужую таблицу в программе Microsoft Excel, часто приходится сталкиваться с пропущенными данными, дубликатами, несогласованными названиями и некорректными формулами. Для приведения таких файлов в рабочий вид помогают пять проверенных инструментов.
Инструмент Go To Special для поиска пропусков
При получении запутанной таблицы не стоит сразу вносить изменения. Сначала нужно оценить масштаб проблем. Функция Go To Special позволяет выбирать ячейки на основе их содержимого — пустых значений, формул, констант или ошибок. С ее помощью можно быстро обнаружить пустые ячейки в таблице до внесения правок. Обнаруженные пропуски можно выделить желтым цветом, заполнить недостающей информацией, а затем убрать заливку.
Условное форматирование для поиска дубликатов
Условное форматирование применяет оформление к ячейкам на основе заданных правил, привлекая внимание к проблемным данным. В столбце Order ID с его помощью можно выявить повторяющиеся идентификаторы, после чего полные дубликаты можно удалить, а временное правило форматирования сбросить.
Для проверки столбца Product правила дубликатов не различают регистр символов, поэтому значения сначала нормализуются функцией UPPER, а затем уникальные правила выводят данные для проверки. В столбце State короткое правило на базе функции LEN подсвечивает полные названия штатов, которые не были сокращены. Все эти несоответствия можно исправить вручную, не меняя исходные данные досрочно.
Функция TEXTSPLIT для разделения объединенных данных
Функция TEXTSPLIT разделяет содержимое ячейки на несколько ячеек на основе разделителя, такого как запятая или пробел. Это полезно, когда в один столбец запихнули несколько параметров, например адрес электронной почты, телефон и почтовый индекс, разделенные символом вертикальной черты (|).
Функция позволяет разбить эти три элемента по отдельным столбцам за один раз. Полученные результаты проверяются, вставляются как значения, а исходный столбец удаляется. После разделения такие поля проще фильтровать, сортировать и использовать в формулах.
Превращение диапазона в таблицы Excel
После очистки диапазона данных его можно преобразовать в таблицу Excel, которая добавляет встроенную фильтрацию, автоматическое расширение и вычисляемые столбцы. Это делается после удаления дубликатов и разбора несоответствий, чтобы не усложнять процесс наведения порядка.
На этом этапе обычные формулы в вычисляемых столбцах можно перевести в структурные ссылки. Excel не меняет ссылки автоматически при создании таблицы, поэтому правка вносится вручную в первой строке, а после нажатия клавиши Enter формула автоматически распространяется вниз по столбцу.
Именованные диапазоны и вспомогательные столбцы
Вспомогательные столбцы позволяют разбивать сложные вычисления на небольшие видимые шаги, а именованные диапазоны дают важным константам понятные имена вместо скрытых значений внутри формул. С помощью инструмента Create from Selection можно задать имена для постоянных значений — ставки налога с продаж, а также порогов и ставок скидок.
Добавление столбца Discount в таблицу делает скидку для каждой строки наглядной. Итоговая формула Total использует этот вспомогательный столбец вместе с именованной ставкой налога. Благодаря этому при изменении налоговой ставки или правил скидки не придется искать нужные числа внутри длинной формулы.
Соблюдение базовых принципов структурирования помогает избегать проблем в будущем: используйте столбцы для полей и строки для записей, храните по одному элементу данных в ячейке, избегайте пустых столбцов, строк и ячеек, а также применяйте одну строку для заголовков.
«`html
Поиск пропусков и проблем с помощью Go To Special
Автор отмечает, что получал чужие таблицы бесчисленное количество раз. На первый взгляд файл может казаться нормальным, однако при детальном рассмотрении обнаруживаются отсутствующие записи, повторяющиеся идентификаторы заказов, несогласованные наименования, контактные данные, сжатые в одну ячейку, и формулы, выполняющие лишнюю работу. Именно в таких ситуациях применяются специальные инструменты для выявления проблем и внесения необходимых изменений.
Инструмент Go To Special позволяет находить не только пустые ячейки, но и множество других элементов, однако именно поиск пробелов служит полезной отправной точкой, позволяющей оценить ситуацию перед принятием решений о дальнейших исправлениях.
Особенности использования условного форматирования
Условное форматирование привлекает внимание к значениям, требующим расследования, не затрагивая при этом исходные данные. При проверке столбца с продуктами стандартные правила дубликатов не различают прописные и строчные буквы, поэтому предварительно значения приводятся к единому регистру с помощью функции UPPER, после чего правило для уникальных или повторяющихся значений выводит записи, заслуживающие внимания. Получившийся новый столбец очищается вручную с учетом того факта, что продукт, встречающийся в списке всего один раз, не обязательно является ошибкой.
Для поля State применяется простое правило на основе длины строки с использованием функции LEN, которое подсвечивает полные названия штатов, не преобразованные в аббревиатуры. После ручной стандартизации этих данных временное правило форматирования удаляется.
Разделение данных и структурирование таблиц
Функция TEXTSPLIT использует в качестве разделителя символ вертикальной черты (|), с помощью которого в исходном наборе данных в столбце контактных данных были объединены адрес электронной почты, номер телефона и почтовый индекс. После разделения информации на отдельные столбцы результаты проверяются, вставляются в виде обычных значений, а исходный объединенный столбец удаляется. Это делает поля удобными для фильтрации, сортировки и интеграции в другие формулы.
Превращение обычного диапазона в официальную таблицу Excel намеренно откладывается до завершения этапа очистки, чтобы структура таблицы не мешала процессу удаления дубликатов и устранения несоответствий. При ручном преобразовании формул в первой строке таблицы в структурные ссылки достаточно нажать клавишу Enter, чтобы Excel автоматически распространил формулу вниз по всему столбцу. В результате вычисления становятся намного понятнее, так как они ссылаются на имена столбцов таблицы, а не на стандартные координаты ячеек на листе.
Именованные диапазоны, вспомогательные столбцы и правила проектирования
Для реализации сложных расчетов применяются вспомогательные столбцы и именованные диапазоны. С помощью инструмента Create from Selection создаются имена для неизменяемых констант — ставки налога с продаж, а также двух порогов и ставок скидок. Добавление отдельного столбца Discount делает скидку для каждой строки видимой, а итоговая формула Total обращается как к этому столбцу, так и к именованной ставке налога. Такой подход избавляет от необходимости искать нужные числа внутри громоздких формул при изменении налоговой ставки или правил расчета скидок.
Соблюдение базовых принципов структурирования позволяет избежать проблем в будущем: под поля отводятся столбцы, под записи — строки, в каждой ячейке хранится строго один элемент данных, исключаются пустые столбцы, строки и по возможности пустые ячейки, а для заголовков используется ровно одна строка. Эти простые правила избавляют создателя файла и его получателей от необходимости исправлять чужие структурные ошибки.
«`
