Таблицы, как отдельный объект, появились в Excel уже давно, однако многие пользователи их по-прежнему не применяют, предпочитая оформлять данные с помощью других стандартных инструментов форматирования программы.
Тем не менее такие таблицы неспроста получили название «умные», ведь с их помощью можно не только максимально быстро оформить диапазон с данными, применив к нему какой-то из стандартных или пользовательских стилей, но они также позволяют максимально упростить и ускорить вычисления. И речь здесь идет не только о том, что не нужно вручную копировать формулу по всему столбцу, так как в таблице эта операция производится автоматически, но и о том, что алгоритм вычисления в умной таблице оптимизирован. Это позволяет производить вычисления быстрее, что будет заметно при работе со значительными массивами данных, состоящими из тысяч строк и столбцов.
Эта заметка является простой инструкцией, которая позволит вам повысить эффективность работы в Excel.
Итак, начнем с создания таблицы в Excel.
Создание умной таблицы в Excel
Как и многое в Excel создать таблицу можно несколькими способами.
Если уже есть некоторый неразрывный диапазон значений, то достаточно установить табличный курсор в любую из его ячеек (1, см.рис.ниже) и выбрать соответствующий инструмент (3) на вкладке Вставка (2). Также можно просто нажать сочетание клавиш Ctrl + T, чтобы открыть тоже самое окно (4).
Если в диапазоне есть строка с заголовками (5), то стоит проследить, чтобы соответствующая галочка была установлена (6). В этом случае Excel автоматически превратит первую строку диапазона в строку заголовков.
Если этого не сделать, то будет создана еще одна строка выше и именно ее Excel будет считать заголовком.
Если же данных еще нет и нужно создать пустую таблицу, то предварительно выделяем диапазон ячеек необходимого размера, а далее используем сочетание клавиш Ctrl + T или соответствующий инструмент со вкладки Вставка.
Форматирование таблицы
При создании таблицы ей будет присвоено стандартное форматирование и его всегда можно изменить с помощью соответствующей панели инструментов.
Здесь стоит учитывать, что таблица является объектом, а значит необязательно выделять весь ее диапазон для изменения форматирования. Достаточно установить табличный курсор в любую из ячеек таблицы и форматирование будет распространяться на весь объект целиком, а не только на активную ячейку.
Помимо готовых стилей (1) есть возможность создать свой стиль таблицы (2). При этом появится окно (3), в котором можно детально настроить каждый из элементов таблицы, указав границы, заливку, параметры шрифта и произведя другие настройки.
Но к таким скрупулёзным настройкам мне прибегать не доводилось и я всегда обходился стандартными стилями и дополнительными настройками форматирования, доступными на панели с параметрами стилей. С их помощью можно управлять элементами форматирования, включая или отключая соответствующие чек-боксы.
Название (имя) таблицы
Как я уже упомянул, умная таблица является объектом Excel, а значит для идентификации каждой таблице присваивается уникальное имя, которое в дальнейшем будет использоваться в формулах.
Имя отображается в соответствующем поле на контекстной вкладке Конструктор. Напомню, что контекстные вкладки появляются при выборе соответствующего объекта на листе Excel.
По умолчанию Excel присваивает стандартное имя «Таблица» с порядковой цифрой. У меня в документе создана только одна таблица, поэтому ее порядковый номер 1.
Имя таблицы можно изменить на произвольное. Делать это необязательно, но если в документе будет несколько таблиц, данные из которых будут связываться между собой формулами, то «человеческие» названия сильно упростят работу. По этой причине я рекомендую присваивать имена таблицам, опираясь на характер данных, которые в этих таблицах находятся. Например, «перечень_заказов».
Фильтры и сортировка
Безусловно, вы обратили внимание на то, что в заголовках таблицы есть кнопки фильтров.
Это стандарт для умных таблиц и возможность фильтрации включается по умолчанию. Если фильтры не нужно выводить, то их можно скрыть с помощью соответствующего чек-бокса на контекстной вкладке Конструктор.
Кнопка фильтра позволяет очень быстро отсортировать или отфильтровать данные по содержимому любого столбца. Очень удобная функция, которая экономит массу времени при работе со значительными массивами данных. Например, Excel автоматически группирует даты по месяцам, а также в дополнительном меню есть и другие опции, позволяющие максимально точно отфильтровать данные по времени.
Включенный фильтр отображается на кнопке (1) и чтобы его снять, нужно выбрать соответствующую опцию из меню (2).
Если уникальных данных в столбце много, то быстро найти нужное значение бывает затруднительно. В этом случае на помощь придет поиск, позволяющий вывести только те значения, которые удовлетворяют введенным символам.
Ну а если таблица большая и не умещается по высоте в один экран, то при прокрутке заголовки сместятся в область названия столбцов листа и будут доступны при прокрутке таблицы, что очень удобно, ведь нет необходимости закреплять шапку таблицы при работе с данными в ней. Соответственно, и сортировка, и фильтры доступны в любой момент.
Кроме фильтров в заголовках можно использовать и так называемые срезы. Это тоже фильтр, который имеет свой индивидуальный интерфейс. Например, нам нужно получить данные по отдельным менеджерам. Каждый раз выбирать менеджера в фильтрах заголовка столбца неудобно. Намного проще вставить срез (1) по столбцу «Менеджер» (2) и в окне появится отдельная панель (3), в которой будут перечислены уникальные значения из столбца - в нашем случае фамилии менеджеров.
Щелкая по нужной фамилии мы получим данные только по его работе.
Строка итогов
Кроме фильтров на панели инструментов с параметрами стилей можно включить строку итогов. В ней автоматически будет подсчитываться сумма (или количество значений) по последнему столбцу таблицы.
Если подвести указатель мыши к этому значению, то появится кнопка, вызывающее контекстное меню с доступными функциями.
Кроме суммирования мы можем в строке итогов вывести среднее значение, количество значений в столбце, количество чисел, максимальное или минимальное значение или при необходимости сформировать свою формулу, вызвав окно вставки функций.
Тоже самое можно проделать и для любого другого столбца в строке итогов.
При использовании фильтров или срезов строка итогов будет учитывать только отображаемые на экране значения. В результате можно очень быстро получить итоговую информацию по интересующим нас позициям.
Авторасширение таблицы
Еще одним преимуществом умных таблиц является то, что они автоматически подхватывают вновь введенные данные. Если мы рядом с умной таблицей в ближайшем столбце начнем вводить данные, то таблица автоматически расширится и на них и к новому столбцу будет применено такое же форматирование.
Тоже самое касается и новой строки и тут есть и еще один нюанс - если в каком-то из столбцов вычисляется формула, то она автоматически распространится и на новую строку.
Расширить границы умной таблицы можно и вручную. Для этого захватываем мышью маркер, который находится в правом нижнем углу таблицы и перемещаем его на нужное количество ячеек. Таким образом можно как расширять, так и уменьшать умную таблицу.
Перемещение строк или столбцов
Умные таблицы позволяют быстро изменять положение строк или столбцов в пределах таблицы. Для этого нужно лишь установить табличный курсор в заголовок столбца, который нужно переместить и затем сместить указатель мыши на границу ячейки, чтобы указатель изменил внешний вид на соответсвующий. Захватываем заголовок мышью и автоматически выделяется весь столбец умной таблицы. Перемещаем столбец в нужную позицию.
Аналогично действуем и при выборе строки.
Автоматическое выделение столбца или строки в пределах умной таблицы позволяет исключить перемещение данных за ее пределами, то есть в других ячейках листа или таблицах, расположенных рядом.
Удаление дубликатов
Поскольку умные таблицы являются объектом, то нет необходимости полностью выделять их диапазон при проведении каких-то операций, а достаточно установить табличный курсор в любую из ячеек таблицы.
Таким образом можно в пару щелчков мыши почистить таблицу от повторяющихся значений. Выбираем соответствующий инструмент на контекстной вкладке Конструктор, затем указываем столбец, который необходимо исследовать на предмет дубликатов.
Строки с дубликатами будут удалены, но это никак не повлияет на данные, которые могут находится рядом с таблицей.
Кстати, данный инструмент очень удобно использовать для получения списка уникальных значений, хотя тоже самое можно сделать с помощью других инструментов, например, с помощью сводных таблицы.
Сводные таблицы
На контекстную вкладку Конструктор вынесен отдельный инструмент, позволяющий быстро создать сводную таблицу на основе данных из умной таблицы.
Сводные таблицы - это мощнейший инструмент Excel и им я посвятил отдельный большой видеокурс.
При создании сводной таблицы на основе умной таблицы, в соответствующем поле будет отображаться не какой-то диапазон ячеек, а заданное нами ранее название умной таблицы - "перечень_заказов".
Это означает, что при добавлении новых данных в таблицу они будут автоматически подхватываться и сводной таблицей. Нам не нужно будет вручную расширять диапазон на новые данные.
Создадим список уникальных товаров и посчитаем сумму заказов по каждому из них.
Если теперь в исходной умной таблице добавятся новые данные, то нужно лишь обновить сводную таблицу, чтобы данные автоматически в нее подтянулись.
Такой подход позволяет очень сильно упростить работу, ведь ненужно следить за диапазоном значений, на основе которого строится сводная таблица, а вероятность не учесть вновь введенные данные стремится к нулю.
Все тоже самое касается и диаграмм.
Диаграммы и графики
Диаграммы в Excel строятся на основе некоторого диапазона данных и также напрямую зависят от выбранного диапазона.
Внесение новых данных в обычный диапазон не проявится на диаграмме сразу - потребуется обновить информацию в настройках диаграммы.
Умные таблицы позволяют не задумываться о выбранном предварительно диапазоне, так как новые данные будут автоматически подхватываться таблицей и также автоматически выводиться на диаграмме.
Нюансам работы с диаграммами и графиками я посвятил отдельный большой видеокурс.
Расчеты в умных таблицах
Поскольку умные таблицы являются отдельными объектами Excel, то и адресация в их ячейках имеет свои особенности.
В рамках таблицы в формулах будут участвовать не относительные ссылки, как это обычно бывает при вычислениях на листе Excel, а ссылки на конкретные столбцы умной таблицы.
В квадратных скобках указывается название заголовка соответствующего столбца, а символ "@" свидетельствует о том, что данные берутся из той же строки, в которую вводится формула.
В итоге формула будет автоматически раскопирована во все ячейки столбца вне зависимости от его размера.
Еще одним преимуществом умных таблиц является то, что мы можем легко использовать данные из них в формулах, которые расположены за пределами умной таблицы или даже на другом листе книги.
Так, например, если бы нам нужно было на новом листе посчитать сумму всех заказов, то пришлось бы выделять весь соответствующий диапазон и в итоговой формуле фигурировало бы название листа, а также диапазон значений, то есть формула бы имела следующий вид:
=СУММ(Лист1!G2:G100)
Поскольку умная таблица имеет уникальное название, то в пределах книги мы можем на нее ссылаться только по этому названию. То есть просто указываем название таблицы, а затем в квадратных скобках название нужного столбца:
=СУММ(перечень_заказов[Сумма заказа])
При вводе названий Excel автоматически предложит варианты, уже имеющиеся в документе и останется лишь их подставить нажатием клавиши Tab.
Ну и стоит упомянуть, что умные таблицы позволяют избежать проблем при работе с функциями просмотра, вроде функции ВПР.
Так как при создании функции мы будем ссылаться не на какой-то диапазон, а на конкретный столбец таблицы, то, во-первых, нет никакой необходимости в фиксации диапазона, так как умная таблица имеет иную систему координат, а, во-вторых, отпадает необходимость в изменении диапазона в формуле, когда в таблицу вносятся новые данные. Умная таблица их подхватит автоматически.
Заключение
Безусловно, есть ситуации, когда умные таблицы могут быть неуместны, например, при работе со стандартизованными бланками, имеющими определенную форму и использующими многострочные заголовки с объединенными ячейками. Однако если речь идет о массиве данных, который нужно проанализировать с помощью формул, диаграмм или любых других инструментов Excel, то умные таблицы будут отличным решением и значительно ускорят и упростят вашу работу.
Ну а если вы решите, что умная таблица более вам не подходит, то преобразовать ее в обычный диапазон можно с помощью соответствующего инструмента со вкладки Конструктор.
Таблица будет преобразована и мы получим самые обычные данные на листе электронной таблицы.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
