Как структурировать данные в Microsoft Excel

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

1

Используйте поля (столбцы) и записи (строки)

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

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

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

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

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

2

Каждая ячейка должна содержать одну точку данных

Конечный способ сломать линейную структуру набора данных Excel – это втиснуть более одной точки данных в ячейку.

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

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

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

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

Принцип одноклеточного точки-точки также применяется к объединению ячеек. Для меня объединение ячеек в электронной таблице Excel подобно использованию Comic Sans MS в формальном документе Word – их следует избегать любой ценой! Как только вы объединяете две или более ячейки в одну, вы уходите от фундаментальной структуры сетки Excel, а это означает, что вы будете вызывать больше проблем, чем решите. Если соседние ячейки содержат одинаковое значение, это нормально! Там просто нет необходимости объединять их вместе.

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

3

Избегайте пустых столбцов и рядов

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

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

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

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

“Но как насчет когда группировка столбцов или рядов? »Я слышал, что вы спрашиваете. Это правда, что сгруппированные столбцы должны быть разделены хотя бы одним столбцом, а сгруппированные ряды должны быть разделены по крайней мере одним рядом. Однако они не должны быть пустыми: вместо этого превратите их в ценные диапазоны путем расчета подтоталов.

4

Отформатируйте свои данные в виде таблицы Excel

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

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

Затем выберите любую ячейку в наборе данных, нажмите «Формат как таблица» на вкладке «Дом» на ленте и выберите стиль таблицы.

Затем в диалоговом окне «Создание таблицы» проверьте «У моей таблицы заголовки» и нажмите «ОК».

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

5

Используйте одну строку для заголовков столбцов

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

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

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

=[@Sales]-[@Cost]

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

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

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

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

В этом примере есть два заголовка столбцов – первый – это год, а второй – квартал.

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

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

Далее, в диалоговом окне «Создание таблицы», Снимите «У моего стола есть заголовки», и нажимать «ОК».

На этом этапе Excel не признает, что у вас есть два столбца заголовка, поэтому удалить эту путаницу, на вкладке дизайна таблицы, Снимите “Ряд заголовка”.

Теперь вам нужно загрузить таблицу в редакторе запросов питания. После выбора любой ячейки в таблице нажмите «Из таблицы или диапазона» на вкладке Data.

На вкладке Transform of Editor Power Query нажмите «Transpose», чтобы эффективно перевернуть столбцы и ряды таблицы.

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

Теперь заполните любые пробелы в своих столбцах, щелкнув правой кнопкой мыши заголовок столбца и выбрав Fill> Down.

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

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

Затем выберите столбцы, которые ранее были вашими двумя строками заголовка, удерживая CTRL, когда вы нажимаете на них. Затем щелкните правой кнопкой мыши выбранные столбцы и нажмите «Освободить другие столбцы».

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

Наконец, нажмите верхнюю половину кнопки «Закрыть и загрузить» на вкладке «Дом» на ленте.

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

---

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