Когда я узнал, как использовать таблицы в Microsoft Excel, это полностью изменило мой подход к работе с данными. Даже если вы думаете, что уже знаете все, что нужно знать о таблицах Excel, надеюсь, вы найдете несколько дополнительных советов в этом руководстве!
Преобразование данных в таблицу Excel
Прежде чем преобразовывать данные Excel в таблицу, убедитесь, что у вас есть заголовки столбцов вдоль верхней строки. Это значительно упростит работу с данными и их использование в дальнейшем.
Затем выберите все ячейки, которые вы хотите преобразовать, включая заголовки столбцов. В качестве альтернативы, если ваш диапазон является смежным (другими словами, все ячейки находятся рядом друг с другом без пустых столбцов или строк), просто выберите одну ячейку в данных. Затем на вкладке «Вставка» на ленте нажмите «Таблица» или используйте сочетание клавиш Microsoft Excel Ctrl+T.
Теперь в диалоговом окне «Создать таблицу» еще раз проверьте, что выбран весь диапазон, убедитесь, что установлен флажок «Моя таблица имеет заголовки», и нажмите «ОК».
Теперь ваши данные представляют собой отформатированную таблицу Excel, в заголовки столбцов которой добавлены кнопки фильтров.
Изменить дизайн таблицы
После создания таблицы настало время настроить ее макет. При выборе любой ячейки в недавно созданной таблице на ленте появляется вкладка «Конструктор таблиц». Откройте эту вкладку, чтобы увидеть различные параметры.
Сначала щелкните стрелку вниз в группе «Стили таблиц», чтобы выбрать различные варианты макета. В моем случае я выбрал стиль «средний сливовый».
Затем перейдите в группу «Параметры стиля таблицы» на вкладке «Конструктор таблиц», чтобы увидеть несколько флажков.
- Строка заголовка: Если в диалоговом окне «Создание таблицы» вы отметили пункт «Моя таблица имеет заголовки», оставьте отмеченным пункт «Строка заголовков».
- Всего строк: При выборе этого параметра в нижнюю часть таблицы добавляется дополнительная строка, в которую можно добавить итоговые значения столбцов (подробнее об этом позже).
- Ряды с полосами: Включите эту опцию, чтобы упростить чтение строк таблицы. Это особенно полезно, если у вас много столбцов.
- Первая колонка: Это применит форматирование к самому левому столбцу таблицы, по сути превращая этот столбец в заголовки строк.
- Последняя колонка: Это применяет форматирование к самому правому столбцу таблицы, что удобно, если он содержит общие или сводные данные.
- Колонны с полосами: Отметьте эту опцию, чтобы помочь людям следить за столбцами вниз. Я бы посоветовал вам поставить галочку или Полосатые ряды or Колонки с полосами — отметка обоих вариантов может привести к путанице и сделать таблицу неопрятной.
- Кнопка фильтра: Эта опция добавляет стрелки фильтра в строку заголовка.
После установки флажка «Итоговая строка» решите, какие столбцы вы хотите суммировать, выбрав соответствующую ячейку в итоговой строке, нажав стрелку вниз и выбрав один из вариантов агрегации.
Если применить фильтр к одному из столбцов, в итоговые значения в строке итогов будут включены только значения, отображаемые в таблице.
Последняя настройка, которую вам следует сделать на вкладке «Конструктор таблиц», — это дать таблице имя. По умолчанию таблицы Excel называются Таблица1, Таблица 2, Таблица3 и т. д. Однако изменение имени таблицы делает вашу электронную таблицу более удобной для навигации, помогает людям, использующим программы чтения с экрана, и упрощает понимание формул, если они ссылаются на данные в таблице.
Чтобы переименовать таблицу, перейдите в группу «Свойства» на вкладке «Конструктор таблиц» и замените имя по умолчанию на более подходящее.
Имена таблиц должны начинаться с буквы, подчеркивания или обратной косой черты, а остальные символы должны быть буквами, цифрами, точками или подчеркиваниями. Они также должны быть уникальными, не могут содержать пробел и не должны иметь то же имя, что и существующая ссылка на ячейку, например A1.
Не замораживайте верхний ряд
При работе с большими наборами данных в Excel вы, возможно, привыкли закреплять верхнюю строку, чтобы при прокрутке вниз заголовки столбцов оставались видимыми.
Однако одним из многочисленных преимуществ форматирования данных в виде таблицы Excel является то, что вам не нужно выполнять этот шаг. Вместо этого, при условии, что вы отметили «My Table Has Headers» в диалоговом окне Create Table и выбрали одну из ячеек в таблице, при прокрутке вниз буквы над вашей таблицей, представляющие столбцы (A, B, C и т. д.), автоматически превратятся в заголовки столбцов вашей таблицы. Это работает, даже если заголовки столбцов не находятся в первой строке.
Обратите внимание, что заголовки этих столбцов также отображают кнопки фильтров, что означает, что вам не придется прокручивать страницу до самого верха, чтобы изменить отображаемые в таблице данные.
Используйте кнопки фильтра
Я уже несколько раз упоминал кнопки фильтров в таблицах Excel в этом руководстве, и это потому, что они действительно экономят время. Короче говоря, кнопки фильтров позволяют легко сузить данные до наиболее релевантной информации, избавляя вас от необходимости вручную искать по строкам и столбцам.
Кнопки фильтров предоставляют вам различные возможности в зависимости от типа данных, которые у вас есть в каждом столбце. Например, кнопка фильтра для столбца D, который содержит текст, предлагает различные текстовые фильтры, такие как поиск значений, которые начинаются или заканчиваются определенными символами.
С другой стороны, когда я нажимаю кнопку фильтра в столбце, содержащем числа, фильтры соответствующим образом изменяются.
Помимо возможности сортировки столбца по возрастанию или убыванию или фильтрации по цвету, в раскрывающемся списке фильтров также есть поле поиска, которое помогает еще больше ускорить процесс фильтрации.
Нажмите «ОК» после того, как вы ввели свой поисковый запрос фильтра и отметили или сняли отметки с соответствующих опций, чтобы применить фильтр.
Еще один изящный трюк — фильтрация столбца на основе выбранного значения. В этом примере я хочу отобразить только сотрудников отдела электроники. Для этого я могу щелкнуть правой кнопкой мыши ячейку, содержащую слово «Электроника», и в меню Фильтр выбрать «Фильтр по значению выбранной ячейки».
Фильтр со слайсерами
Хотя кнопки фильтров — это отличный способ организовать и упорядочить данные, еще более простой способ сделать это — добавить срезы, особенно если определенный столбец с большей вероятностью будет отфильтрован, чем другие.
В этом примере, предположим, что генеральный директор больше всего интересуется прибылью, полученной сотрудниками в разных отделах, и какие люди являются лучшими кандидатами на повышение. Вы можете значительно облегчить жизнь генерального директора, добавив срезы для этих трех столбцов.
Выбрав любую ячейку в таблице, нажмите «Вставить срез» на вкладке «Конструктор таблиц» на ленте.
Далее отметьте столбцы, для которых вы хотите добавить срезы, и нажмите «ОК».
Теперь щелкните и перетащите слайсеры, чтобы изменить их положение или размер, или откройте вкладку «Слайсер» на ленте, чтобы увидеть больше параметров. Вы также можете щелкнуть правой кнопкой мыши слайсер и нажать «Настройки слайсера», чтобы дополнительно настроить этот расширенный инструмент фильтрации.
Теперь вы и ваш генеральный директор можете использовать срезы для быстрой фильтрации данных в таблице Excel. В этом примере срезы настроены на отображение сотрудников отделов красоты и одежды, которые имеют потенциал для продвижения на более высокую должность, и поскольку эти выборки окрашены в синий цвет, легко увидеть, как фильтруются данные.
Удерживайте клавишу Ctrl при выборе элементов в слайсере, чтобы отобразить их все одновременно, или нажмите Alt+C, чтобы отменить выбор всех элементов в слайсере.
Добавить столбцы и строки
Добавление столбца справа или строки внизу отформатированной таблицы Excel — это довольно просто. В этом примере, как только я ввел «Score» в ячейку K1 и нажал Enter, Excel расширил размеры таблицы, чтобы захватить новый столбец. Это означает, что мне не нужно применять ручное форматирование к новым данным.
Аналогично, когда я добавил еще один идентификатор сотрудника в ячейку A32, Excel не только расширил таблицу вниз, но и продублировал все формулы, созданные мной в предыдущих строках.
Если вы предпочитаете подготовить новые столбцы и строки до добавляя данные, щелкните и перетащите маркер заполнения таблицы вправо или вниз. Если вы используете этот метод, не забудьте дать имя каждому новому столбцу, добавив заголовок в первую строку.
Вы также можете добавить новую строку или столбец между существующими данными, щелкнув правой кнопкой мыши заголовок строки или столбца и нажав «Вставить», будучи уверенными в том, что форматирование таблицы будет автоматически применено к этим новым точкам ввода данных.
Нажатие кнопки «Вставить» в столбце добавляет новый столбец слева, а нажатие кнопки «Вставить» в строке добавляет новую строку выше.
Выполнение расчетов на основе столбцов
Еще одним преимуществом форматирования данных в виде таблицы Excel является то, что выполнение вычислений между столбцами становится намного проще и быстрее.
Предположим, у вас есть таблица, представленная на снимке экрана ниже, и ваша цель — рассчитать прибыль на единицу товара, разделив значения в столбце «Прибыль» на значения в столбце «Проданные единицы товара».
Для этого в ячейке G2 введите =, выберите ячейку F2, введите косую черту (/) и выберите ячейку E2. При этом обратите внимание, как Excel вставляет заголовки столбцов (также известные как структурированные ссылки) в вашу формулу, а не прямые ссылки на ячейки, и вот почему важно иметь именованные заголовки столбцов в вашей таблице:
=[@Прибыль]/[@[Проданные единицы]]
Квадратные скобки — это способ Excel указать, что используется структурированная ссылка, а символ @ (также известный как оператор пересечения) означает, что вычисление будет применено соответственно к каждой строке в таблице.
При нажатии Enter оставшиеся ячейки в столбце «Прибыль на единицу» принимают эту формулу.
Структурированные ссылки проще создавать, читать и анализировать, чем прямые ссылки, и они остаются верными соответствующим столбцам, даже если добавляются дополнительные столбцы. Более того, поскольку весь столбец ссылается в формуле своим заголовком, расчет будет автоматически применяться к любым новым строкам, которые вы добавляете.
Еще более впечатляюще, если вы переименуете один из заголовков столбцов, структурированная ссылка адаптируется для размещения этого изменения. В этом примере столбец «Проданные единицы» был переименован в «Единицы», а формула в столбце «Прибыль на единицу» отражает это обновление.
Структурированные ссылки работают независимо от того, работаете ли вы внутри таблицы или за ее пределами, то есть вы можете легко ссылаться на таблицу и ее столбцы из любого места в рабочей книге.
Здесь я вставил дополнительные строки над таблицей, так как хочу рассчитать общую прибыль здесь, а не внизу, чтобы не прокручивать страницу каждый раз. Еще одно преимущество выполнения этого расчета вне таблицы заключается в том, что он игнорирует любые фильтры. Это означает, что я мог бы выбрать вставку итоговой строки внизу, которая вычисляет только то, что отображается и общий итог за пределами таблицы, в котором подсчитываются все данные.
Теперь в ячейке F2 я введу:
= СУММ (
Затем, когда я навожу курсор на заголовок столбца «Прибыль», он превращается в черную стрелку вниз, указывая на то, что можно выбрать весь столбец.
В этот момент, когда я нажимаю, Excel добавит структурированную ссылку на столбец в формулу. Однако на этот раз, поскольку формула находится вне таблицы, она также ссылается на имя таблицы (Employee_Data):
=СУММА(Данные_о_сотруднике[Прибыль]
Вот почему так важно дать название таблице в группе «Свойства» вкладки «Конструктор таблиц» на ленте. Когда я закрываю круглые скобки и нажимаю Enter, расчет завершается.
Чтобы избежать использования мыши в этом процессе, после ввода текста = СУММ (, начните вводить имя таблицы и нажмите клавишу Tab, когда оно появится в списке подсказок.
Затем, добавив имя таблицы в формулу, добавьте квадратную скобку, чтобы увидеть список столбцов в таблице, используйте клавиши со стрелками, чтобы выбрать соответствующую метку, и снова нажмите клавишу Tab, чтобы добавить ее в формулу.
Наконец, закройте квадратные и круглые скобки и нажмите Enter.
После того, как вы создали свою таблицу, вы можете сделать еще один шаг и преобразовать ее в PivotTable, чтобы настроить способ визуализации данных, генерировать комплексные сводки данных и выполнять сложные анализы. Более того, вы можете затем создавать PivotCharts, удобные, если вы хотите создать панель мониторинга, содержащую все ваши ключевые показатели эффективности.
