Таблицы, как отдельный объект, появились в 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, начиная с 2007 версии. Это привычные многим полосатые таблицы, которые выглядят примерно так:
И несмотря на то, что их используют уже много лет, еще остаются пользователи, которые используют этот крутой инструмент только потому что “красиво” или не используют совсем. Давайте разберемся, какие преимущества дают умные таблицыв Excel, помимо приятно-полосатого внешнего вида. Но сначала узнаем, как ее создать.
- Как создать умную таблицу в Excel
- Плюсы и минусы умной таблицы в Excel
- Форматирование умной таблицы
- Добавляем срезы (фильтры) к умной таблице
- Как убрать умную таблицу
Чтобы из обычной таблицы Excel сделать “умную” таблицу, нужно:
1. Щелкнуть на любой ячейке таблицы.
Обратите внимание, что всю таблицу выделять не обязательно — если в таблице нет пустых строк или столбцов, Excel сам определит границы диапазона.
2. Нажать сочетание клавиш Ctrl + T. Вокруг таблицы появится рамочка- выделение и окно Создать таблицу, в котором указан выделенный диапазон
Если вдруг это выделение не захватило какую-то нужную область, можно выделить область таблицы вручную.
Обратите внимание на предыдущую картинку — если ваша таблица уже имеет заголовки (“шапку”), то обязательно должна быть установлена “галочка” Таблица с заголовками. Иначе заголовки будут добавлены автоматически в формате Столбец1, Столбец2 и т.д.
Если таблица не имеет заголовков, то “галочку” Таблица с заголовками лучше снять и уже потом переименовать заголовки. Иначе в качестве заголовков будут назначены значение из первой строки.
3. Нажать Ок или клавишу Enter. Готово, у нас получилось преобразовать обычный диапазон в умную таблицу Excel.
Также есть второй способ создания умной таблицы.
1. Также щелкаем в любой ячейке таблицы.
2. Вкладка Главная — Блок Стили — Форматировать как таблицу — в Выпадающем окне выбираем стиль таблицы.
3. Далее также смотрим, чтобы выделение захватило весь нужный диапазон и была установлена галочка Таблица с заголовками и нажимаем Ок.
Плюсы и минусы инструмента умные таблицы в Excel
Преимущества инструмента Умные таблицы в Excel
1. Таблица автоматически расширяется при добавлении строк или столбцов.
Главное требование при этом — новый столбец или строка должны добавляться “впритык” к таблице — без пропусков строк или столбцов.
2. Формулы, введенные в ячейку, после нажатия Enter автоматически копируются на весь столбец.
3. Автоматически добавляются автофильтры ко всем столбцам. И на столбцы, добавленные уже после создания таблицы, фильтры также “навешиваются” автоматически. При этом фильтр можно убрать (см. в разделе Форматирование умной таблицы)
При прокрутке таблицы строка с заголовками и фильтрами закреплена вверху страницы, даже если вы отдельно не закрепляли области.
4. Умные таблицы в Excel имеет имя, которое присваивается автоматически, но его можно изменить.
Посмотреть и изменить имя умной таблицы в Excel можно на вкладке Конструктор таблиц (при этом курсор должен быть установлен на любой ячейке в таблице, иначе вкладка Конструктор таблиц не появится на ленте)
5. К умной таблице можно добавить срезы, такие же, как для сводной таблицы. Срезы в умной таблице упрощают ее фильтрацию и делают более отчет более профессиональным.
6. Структурированные формулы, которые легко читать.
В формулах внутри таблицы используются не привычные адреса ячеек и диапазонов, а специальные адреса, отражающие называние столбца или области внутри таблицы. Это облегчает “чтение” формулы (если, конечно, столбцам в таблице были даны релевантные названия).
7. Автоматическое добавление итогов к таблице с возможностью выбора вида итога (сумма, среднее и т.д.).
8. Удобно создавать сводные таблицы на основании умных таблиц. При добавлении новых столбцов или строк в исходную таблицу, они автоматически добавляются в сводную таблицу (после ее обновления) без необходимости настраивать диапазон.
9. Умные таблицы в Excel удобно загружать в Power Query для последующей обработки.
10. Ну и конечно, красивый “полосатый” внешний вид без “танцев с бубнами” с условным форматированием.
Еще одно преимущество “внешности” — легкая настройка форматирования умной таблицы в Excel. На вкладке Конструктор таблиц его можно быстро настроить (см. раздел Форматирование умной таблицы)
Но, конечно, “умные” таблицы в Excel имеют и ряд особенностей, которые могут затруднить их использование.
Минусы инструмента Умные таблицы в Excel
- При достаточно большом размере таблицы и большом количестве формул файл с умной таблицей может тормозить. Это связано с тем, что форматирование умной таблицы сделано по типу условного форматирования, которое “нагружает” файл.
- Если добавить строку итогов, то функция автоматического расширение «умной таблицы» по строкам при добавлении данных перестает работать. Это логично, т.к. не могут же новые строки с данными быть после строки итогов. Расширить таблицу в этом случае можно только растягиванием за уголок.
- Заголовки можно располагать только в первой строке умной таблицы. Это лишает возможность делать сложные «шапки» таблиц, а также использовать одинаковые названия столбцов — если столбцы будут называться одинаково, Excel автоматически добавить к ним цифру 2, 3 и т.д.
- Формулы могут становятся очень длинными, поскольку в формулу прописывается наименование столбца. Особенно если заголовки длинные сами по себе. Этот момент ограничивает возможность давать сложные названия столбцам.
- Нет возможности закрепить ячейки или столбцы абсолютными/относительными ссылками, и при “протягивании” формулы ссылки съезжают.
- Нельзя установить режим совместного доступа в файл, который содержит хотя бы одну “умную” таблицу.
Форматирование умной таблицы
Можно достаточно гибко настраивать форматирование умной таблицы в Excel.
Настройка внешнего вида умной таблицы
- Откроем вкладку Конструктор таблиц (при этом должна быть активна любая ячейка внутри таблицы)
- В блоке Стили таблиц можно выбрать подходящий стиль из выпадающего списка.
Также можно создать собственный стиль таблицы умной таблицы в Excel.
Вкладка Конструктор таблиц — блок Стили таблиц — выпадающий список — Создать стиль таблицы. И далее поочередно настроить форматирование для каждого элемента таблицы. Для этого нужно выбрать элемент, нажать на кнопку Формат и выбрать нужные параметры заливки, шрифта и т.д.
К тому же, никто не запрещает настраивать внешний вид таблицы вручную. Например, сделать заливку определенного заголовки или ячейки другим цветом.
Также можно очистить стиль умной таблицы.
Это отличное решение для таблиц, которые используются в промежуточных расчетах, и находятся не “на виду”, поскольку отсутствие стиля облегчает работу файла.
Для этого нужно щелкнуть на любой ячейке таблицы — вкладка Конструктор таблиц — блок Стили таблиц — выпадающий список — Очистить.
Теперь таблица не имеет форматирование. Но она не перестала быть умной таблицей! Обратите внимание, что у нее так же есть границы и имя, следовательно, это такая же умная таблица, просто не цветная.
Параметры стиля умных таблиц в Excel
Если вы выбрали предустановленный стиль умной таблицы в Excel, то можно сделать его поднастройку. На вкладке Конструктор таблиц в блоке Параметры стилей таблиц можно настроить параметры установкой/снятием “галочек”
- Строка заголовков: включает и отключает заголовки в таблице
- Строка итогов: включает/отключает строку итогов.
- Чередующиеся строки — включает/отключает “полосатость” таблицы, все строки становятся бесцветными.
- Чередующиеся столбцы — включает/отключает чередования цвета столбцов. Со включенным параметром столбцы окрашиваются цветом “через один”
- Первый столбец и Последний столбец — включает/отключает жирный шрифт для первого и последнего столбца в таблице.
- Кнопка фильтра — включает/отключает автофильтр.
Как изменить имя умной таблицы в Excel
Также на вкладке Конструктор таблиц можно изменить имя таблицы. По умолчанию Excel присваивает таблицам имена типа Таблица1, Таблица2… Однако, лучше их менять на более релевантные, это удобно для использования в формулах.
Чтобы изменить имя умной таблицы, заходим на вкладку Конструктор таблиц — Блок Свойства — в поле Имя таблицы пишем новое имя.
Добавляем срезы (фильтры) к умной таблице
Срезы в умных таблицах выглядят аналогично срезам в сводных таблицах. Они упрощают фильтрацию таблиц.
Чтобы добавить срез к умной таблице в Excel, нужно перейти на вкладку Конструктор таблиц (должна быть активна любая ячейка внутри таблицы) — блок Инструменты — Вставить срез.
Далее нужно отметить галочками, срезы по каким столбцам нужны. Для примере выберем срезы по Дате и Адресу магазина.
Срезы добавлены. Теперь можно фильтровать данные в умной таблице Excel с их помощью.
Как убрать умную таблицу
Иногда нужно преобразовать умную таблицу обратно в диапазон — это значит, что нужно убрать умную таблицу и оставить просто данные.
Для этого переходим на вкладку Конструктор таблиц — блок Инструменты — Преобразовать в диапазон
В появившемся окне нажимаем кнопку Да.
Таблица не изменила свой внешний вид — но она перестала быть умной таблицей (т.е. лишилась всех ее функций — см. раздел Плюсы и минусы умной таблицы в Excel).
В этой статье мы узнали, что такое умные таблицы в Excel, а также познакомились с особенностями таких таблиц.
Вам может быть интересно:
В чем суть умных таблиц
Обычно информация в столбцах Excel не структурирована. Она называется диапазоном данных.
Диапазон можно расширить, используя так называемую «Умную таблицу».
По своей сути, умная таблица — это способ форматирования обычных диапазонов данных. При этом обрабатываются данные из нескольких смежных ячеек как единая структура. Если дополнить ячейку смежного столбца, то диапазон умной таблицы автоматически на него расширится.
Чтобы превратить наш диапазон в умную таблицу, выделите любую ячейку с данными и на вкладке «Главная» выберите «Форматировать как таблицу». После этого можно выбрать любой из вариантов графического форматирования, в результате чего получится примерно следующее:
Теперь наш диапазон имеет свое имя, задать которое можно в главном меню:
А все данные внутри таблицы рассматриваются как единый диапазон. При добавлении информации в столбцы или строки, этот диапазон будет автоматически расширять всю таблицу.
Кроме этого вы можете изменять общий размер таблицы с помощью ползунка:
Фильтр в столбцах умной таблицы теперь применяется автоматически, в т.ч. и при добавлении новых колонок. Верхняя строка автоматически закрепляется при прокрутке.
Таким образом, данные начинают выглядеть более привлекательно и цельно. Но кроме этого добавляются и новые возможности.
Продвинем ваш бизнес
В Google и «Яндексе», соцсетях, рассылках, на видеоплатформах, у блогеров
Подробнее
Базовые возможности умной таблицы Excel
Поскольку вся структура умной таблицы взаимодействует как единое целое, то многие полезные вещи в ней происходят автоматически:
- При добавлении новых строк в них автоматически будут скопированы все формулы, применяемые в ячейках до этого;
- При добавлении нового столбца с формулой, она будет автоматически применяться ко всему столбцу;
- Можно добавить итоговую строку, которая автоматически подсчитает итоги по всем значениям столбца:
Выставьте галочку на вкладке «Конструктор» — и строка появится в нижней части умной таблицы («Среднее» в столбце мы выбрали вручную):
По каждому столбцу можно выбрать формулу итогового подсчета, которая выдаст соответствующее значение в строке. В примере выше мы суммировали все доходы с разных каналов и вычислили среднее для всех средних чеков.
Обратите внимание, что итоговое значение в столбце умной таблицы может автоматически вычисляться не по должной формуле. Например, у нас Excel суммировал средний чек. Выставляйте нужные формулы из списка вручную.
- Названия столбцов автоматически считаются их именами. Кроме того, теперь к элементам таблицы можно обращаться, используя эти имена.
- Особенно это удобно для работы с формулами. Так, для вычисления суммы по всему столбцу можно не указывать диапазон значений, а обратиться по его имени:
Для этого можно из любой ячейки вызвать формулу, указать в качестве источника данных имя умной таблицы Excel, затем в квадратных скобках подставить имя нужного столбца.
- Работа с умной таблицей упрощает ввод новых формул. Можно, к примеру, использовать имена столбцов в квадратных скобках, чтобы указать формуле откуда брать значение ячейки:
- Можно перемещать столбцы и строки внутри умной таблицы. Для этого наведите курсор на верхнюю или левую ячейку элемента, дождитесь курсора с перекрестными стрелками и перетащите элемент куда вам нужно:
-
Можно обращаться ко всей таблице или отдельным ее частям. Базовые формулы:
- Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов;
- Таблица1[#Данные] — ссылка только на данные (без строки заголовка);
- Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов;
- Таблица1[#Итоги] — ссылка на строку итогов (если она включена);
- Таблица1[#Эта строка] — ссылка на текущую строку, например формула ;
- Таблица1[[#Эта строка];[Продажи]] — будет ссылаться на значение «Продажи» из текущей строки таблицы.
Добавление срезов к таблице
Если вы уже работали со сводными таблицами Excel, то могли иметь дело со срезами — это удобный инструмент для быстрой фильтрации данных и упрощения их анализа.
Срезы есть и в умных таблицах. Допустим, нам нужно увидеть значения продаж только по выбранному каналу. Можно применить фильтр, однако срез упрощает этот процесс — в ходе его использования у вас появится отдельная панель, в которой выбрать нужный канал можно будет простым одинарным кликом.
Кнопка вставки среза есть на вкладке «Конструктор» умной таблицы Excel:
В окне вставки среза выбираем один или несколько столбцов, по данным из которых мы будем получать сводную информацию.
Теперь, чтобы отфильтровать данные, достаточно кликнуть на нужном значении в панели:
Обратите внимание, что в строке итогов теперь выдаются итоговые значения именно по срезу данных, а не по всей таблице с скрытыми строками.
С точки зрения аналитики, срезы в умных таблицах удобны для построения быстрых отчетов и перемещения по данным «на лету».
Чтобы снять фильтрацию с таблицы, щелкните по иконке «Удалить фильтр» на панели среза.
Можно добавить и другой срез, при этом первый добавленный будет считаться главным, и примененный на нем фильтр не даст второму срезу отобразить скрытые данные:
В нашем примере дата 12.04.2023 на панели срезов показывается как скрытая, потому что срез по каналу продаж не выдает ее в конечной таблице. Можно выбрать и ее, но тогда данных в таблице не будет.
В нашем же случае мы сначала сделали срез по Маркетплейсу 1, а затем выбрали дату 11 апреля, что и привело к выдаче единственного значения. Если бы значений по данному каналу за выбранную дату было больше, мы бы получили срез по двум фильтрам, то есть наиболее уточненные данные для последующего анализа.
Чтобы удалить панель срезов, кликните по ней правой кнопкой и выберите соответствующий пункт.
Внимание: фильтрацию это не отключит, так что не пугайтесь. К столбцам таблицы будет продолжать применяться фильтр через заголовок, не забудьте отключить его там.
Такая функция умных таблиц может быть очень полезна, например, для ведения учета продаж и продвижения на маркетплейсах.
Другие полезные возможности
- В умных таблицах Excel можно быстро удалить дубликаты. Для этого выберите соответствующий пункт в меню «Конструктор» и укажите столбцы, в которых нужно искать повторяющиеся значения:
После удаления дубликатов все итоговые значения автоматически пересчитаются.
- Применяя диаграммы и графики для визуализации данных, вы можете автоматически обновлять их за счет использования умных таблиц.
Вставляя диаграмму, вы сразу можете убедиться, насколько удобнее становится этот процесс:
Добавьте диаграмму на любой элемент таблицы, и уже при выборе типа визуализации появится пример ее внешнего вида. Так, в нашем случае система сообразила, что лучше всего построить соотношение суммы и среднего чека по каждой дате.
- Добавьте срез или фильтр – и диаграмма автоматически обновится, демонстрируя показатели по выбранному пулу значений. Конечно же, можно вручную выбирать данные для показа на графиках и менять способы их отображения.
- Передача данных из умных таблиц в Power Query и сводные таблицы также происходит намного удобнее обычного диапазона. Обновите умную таблицу Excel – обновится и сводная.
Как отменить создание умной таблицы и вернуть все как было
Многие ошибочно полагают, что отменить создание умной таблицы можно там же, где оно и было произведено — в настройках форматирования. В конструкторе таблиц (стилей) есть пункт «Очистить стиль таблицы»:
Но при его выборе вы получите лишь таблицу стандартного черно-белого вида, «умной» она быть не перестанет.
В настройках форматирования на главной вкладке вы вообще не найдете возможности что-либо сделать с умной таблицей.
Отменить умную таблицу можно в «Конструкторе» — для этого используйте пункт «Преобразовать в диапазон»:
Согласитесь с преобразованием и данные вернутся к обычному виду. При этом сохраняется и форматирование, и строка итогов, но пропадают фильтры:
Функции и возможности умной таблицы при возврате пропадают, исчезают срезы. Но в любой момент вы можете вновь применить форматирование и вернуться к использованию этого без сомнения мощного и удобного инструмента Microsoft Excel.
Кстати: В Google Docs (Гугл.Таблицах) подобного инструмента пока нет. Для его использования вам потребуется установленный пакет Microsoft Office.
Недостатки умных таблиц
При всех достоинствах инструмент имеет и ряд минусов, к которым стоит быть готовым:
- При большом количестве данных в виде умных таблиц, файл Excel может замедлять работу компьютера. Поскольку работает условное форматирование и постоянный пересчет значений, это активнее нагружает систему.
- Таблица перестает автоматически расширяться по строкам после добавления строки итогов. Это может доставить неудобство при частом обновлении данных.
- Под заголовки и «шапку» таблицы выделена только одна строка. Это лишает возможности делать многоэтажные и разветвленные заголовки, — то есть умные таблицы по своей сути являются еще и простыми.
- При использовании длинных имен столбцов формулы становятся еще более длинными и сложными для восприятия.
- Общий доступ к Excel-файлу с умными таблицами невозможен.
Обратите внимание на эти недостатки, чтобы пользоваться преимуществами инструмента в полной мере и не отказываться от них, после того как долго наполняли файл данными.
Читайте также:
17 фишек Excel, с которыми будет проще запустить рекламу
20+ функций Excel, которые ускорят работу в несколько раз
Анализ данных на автомате: гайд по Microsoft Power BI для начинающих
Содержание
- Применение «умной» таблицы
- Создание «умной» таблицы
- Наименование
- Растягивающийся диапазон
- Автозаполнение формулами
- Строка итогов
- Сортировка и фильтрация
- Преобразование таблицы в обычный диапазон
- Вопросы и ответы
Практически каждый пользователь Excel встречался с ситуацией, когда при добавлении новой строки или столбца в табличный массив, приходится пересчитывать формулы и форматировать данный элемент под общий стиль. Указанных проблем не было бы, если вместо обычного варианта применять, так называемую, «умную» таблицу. Это позволит автоматически «подтянуть» к ней все элементы, которые пользователь располагает у её границ. После этого Эксель начинает воспринимать их, как часть табличного диапазона. Это далеко не полный перечень того, чем полезна «умная» таблица. Давайте узнаем, как её создать, и какие возможности она предоставляет.
Применение «умной» таблицы
«Умная» таблица представляет собой специальный вид форматирования, после применения которого к указанному диапазону данных, массив ячеек приобретает определенные свойства. Прежде всего, после этого программа начинает рассматривать его не как диапазон ячеек, а как цельный элемент. Данная возможность появилась в программе, начиная с версии Excel 2007. Если сделать запись в любой из ячеек строки или столбца, которые находятся непосредственно у границ, то эта строчка или столбец автоматически включаются в данный табличный диапазон.
Применение этой технологии позволяет не пересчитывать формулы после добавления строк, если данные из неё подтягиваются в другой диапазон определенной функцией, например ВПР. Кроме того, среди преимуществ следует выделить закрепление шапки вверху листа, а также наличие кнопок фильтрации в заголовках.
Но, к сожалению, у этой технологии имеются и некоторые ограничения. Например, нежелательно применение объединения ячеек. Особенно это касается шапки. Для неё объединение элементов вообще недопустимо. Кроме того, даже если вы не желаете, чтобы какое-то значение, находящееся у границ табличного массива, было в него включено (например, примечание), оно все равно будет расцениваться Excel, как его неотъемлемая часть. Поэтому все лишние надписи нужно размещать хотя бы через один пустой диапазон от табличного массива. Также в нём не будут работать формулы массивов и книгу невозможно будет применять для совместного использования. Все наименования столбцов должны быть уникальными, то есть, не повторяться.
Создание «умной» таблицы
Но прежде, чем перейти к описанию возможностей «умной» таблицы, давайте узнаем, как её создать.
- Выделяем диапазон ячеек или любой элемент массива, для которого хотим применить табличное форматирование. Дело в том, что даже, если выделить один элемент массива, то программа во время процедуры форматирования захватит все сопредельные элементы. Поэтому и нет большой разницы в том, выделите вы весь целевой диапазон или только его часть.
После этого перемещаемся во вкладку «Главная», если вы в данный момент находитесь в другой вкладке Excel. Далее кликаем по кнопке «Форматировать как таблицу», которая размещена на ленте в блоке инструментов «Стили». После этого открывается список с выбором различных стилей оформления табличного массива. Но выбранный стиль на функциональность никак влиять не будет, поэтому щелкаем по тому варианту, который визуально больше вам нравится.
Существует также ещё один вариант форматирования. Точно также выделяем полностью или часть диапазона, который собираемся преобразовать в табличный массив. Далее перемещаемся во вкладку «Вставка» и на ленте в блоке инструментов «Таблицы» щелкаем по большому значку «Таблица». Только в этом случае выбор стиля не предоставляется, и он будет установлен по умолчанию.
Но самый быстрый вариант — это после выделения ячейки или массива использовать нажатие горячих клавиш Ctrl+T.
- При любом из указанных выше вариантов действий открывается небольшое окошко. В нем располагается адрес диапазона, который будет подвергнут преобразованию. В подавляющем большинстве случаев программа определяет диапазон правильно, независимо от того выделили вы его весь или только одну ячейку. Но все-таки на всякий случай требуется проверить адрес массива в поле и, если он не соответствует тем координатам, которые вам нужны, то изменить его.
Кроме того, обратите внимание на то, чтобы стояла галочка около параметра «Таблица с заголовками», так как в большинстве случаев заголовки у исходного набора данных уже имеются. После того, как вы удостоверились, что все параметры правильно введены, жмите на кнопку «OK».
- После этого действия диапазон данных будет преобразован в «умную» таблицу. Это будет выражаться в приобретении некоторых дополнительных свойств у данного массива, а также в смене его визуального отображения, согласно выбранному ранее стилю. Об основных возможностях, которые предоставляют эти свойства, мы и поговорим далее.
Урок: Как сделать таблицу в Excel
Наименование
После того, как «умная» таблица сформирована, ей автоматически будет присвоено имя. По умолчанию это наименование типа «Таблица1», «Таблица2» и т.д.
- Чтобы посмотреть, какое имя имеет наш табличный массив, выделяем любой его элемент и перемещаемся во вкладку «Конструктор» блока вкладок «Работа с таблицами». На ленте в группе инструментов «Свойства» будет располагаться поле «Имя таблицы». В нем как раз и заключено её наименование. В нашем случае это «Таблица3».
- При желании имя можно изменить, просто перебив с клавиатуры название в указанном выше поле.
Теперь при работе с формулами для того, чтобы указать конкретной функции, что нужно обработать весь табличный диапазон, вместо обычных координат в качестве адреса достаточно будет ввести её имя. К тому же, это не только удобно, но и практично. Если применять стандартный адрес в виде координат, то при добавлении строки внизу табличного массива, даже после того, как она будет включена в его состав, функция не захватит для обработки эту строку и аргументы придется перебивать заново. Если же вы укажите, как аргумент функции, адрес в виде наименования табличного диапазона, то все строчки, в будущем добавленные к нему, автоматически будут обрабатываться функцией.
Растягивающийся диапазон
Теперь остановим внимание на том, каким образом в табличный диапазон добавляются новые строки и столбцы.
- Выделяем любую ячейку в первой же строчке ниже табличного массива. Делаем в ней произвольную запись.
- Затем жмем на клавишу Enter на клавиатуре. Как видим, после этого действия вся строчка, в которой находится только что добавленная запись, была автоматически включена в табличный массив.
Более того, к ней было автоматически применено то же форматирование, что и у остального табличного диапазона, а также были подтянуты все формулы, расположенные в соответствующих столбцах.
Аналогичное добавление произойдет, если мы произведем запись в столбце, который находится у границ табличного массива. Он тоже будет включен в её состав. Кроме того, ему автоматически будет присвоено наименование. По умолчанию название будет «Столбец1», следующая добавленная колонка – «Столбец2» и т. д. Но при желании их всегда можно переименовать стандартным способом.
Ещё одним полезным свойством «умной» таблицы является то, что как бы много записей в ней не было, даже если вы опуститесь в самый низ, наименования столбцов всегда будут перед глазами. В отличие от обычного закрепления шапок, в данном случае названия колонок при переходе вниз будут размещаться прямо в том месте, где располагается горизонтальная панель координат.
Урок: Как добавить новую строку в Excel
Автозаполнение формулами
Ранее мы видели, что при добавлении новой строчки, в её ячейку того столбца табличного массива, в котором уже имеются формулы, производится автоматическое копирование этой формулы. Но изучаемый нами режим работы с данными умеет больше. Достаточно заполнить одну ячейку пустого столбца формулой, чтобы она была автоматически скопирована во все остальные элементы данной колонки.
- Выделяем первую ячейку пустого столбца. Вписываем туда любую формулу. Делаем это обычным способом: устанавливаем в ячейку знак «=», после чего щелкаем по тем ячейкам, арифметическое действие между которыми собираемся выполнить. Между адресами ячеек с клавиатуры проставляем знак математического действия («+», «-», «*», «/» и т.д.). Как видим, даже адрес ячеек отображается не так, как в обычном случае. Вместо координат, отображающихся на горизонтальной и вертикальной панели в виде цифр и латинских букв, в данном случае в виде адреса отображаются наименования колонок на том языке, на котором они внесены. Значок «@» означает, что ячейка находится в той же строке, в которой размещается формула. В итоге вместо формулы в обычном случае
=C2*D2мы получаем выражение для «умной» таблицы:
=[@Количество]*[@Цена] - Теперь, чтобы вывести результат на лист, жмем на клавишу Enter. Но, как видим, значение вычисления отображается не только в первой ячейке, но и во всех остальных элементах столбца. То есть, формула была автоматически скопирована в другие ячейки, и для этого даже не пришлось применять маркер заполнения или другие стандартные средства копирования.
Данная закономерность касается не только обычных формул, но и функций.
Кроме того, нужно заметить, что, если пользователь будет вводить в целевую ячейку в виде формулы адреса элементов из других столбцов, то они будут отображаться в обычном режиме, как и для любого другого диапазона.
Строка итогов
Ещё одной приятной возможностью, которую предоставляет описываемый режим работы в Excel, является выведение итогов по столбцам в отдельной строке. Для этого не придется специально вручную добавлять строку и вбивать в неё формулы суммирования, так как инструментарий «умных» таблиц уже имеет в своем арсенале заготовки необходимых алгоритмов.
- Для того, чтобы активировать суммирование, выделяем любой табличный элемент. После этого перемещаемся во вкладку «Конструктор» группы вкладок «Работа с таблицами». В блоке инструментов «Параметры стилей таблиц» устанавливаем галочку около значения «Строка итогов».
Для активации строки итогов вместо вышеописанных действий можно также применить сочетание горячих клавиш Ctrl+Shift+T.
- После этого в самом низу табличного массива появится дополнительная строка, которая так и будет называться – «Итог». Как видим, сумма последнего столбца уже автоматически подсчитана с помощью встроенной функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
- Но мы можем подсчитать суммарные значения и для других столбцов, причем использовать при этом совершенно разные виды итогов. Выделяем щелчком левой кнопки мыши любую ячейку строки «Итог». Как видим, справа от этого элемента появляется пиктограмма в виде треугольника. Щелкаем по ней. Перед нами открывается список различных вариантов подведения итогов:
- Среднее;
- Количество;
- Максимум;
- Минимум;
- Сумма;
- Смещенное отклонение;
- Смещенная дисперсия.
Выбираем тот вариант подбития итогов, который считаем нужным.
- Если мы, например, выберем вариант «Количество чисел», то в строке итогов отобразится количество ячеек в столбце, которые заполнены числами. Данное значение будет выводиться все той же функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
- Если вам недостаточно тех стандартных возможностей, которые предоставляет список инструментов подведения итогов, описанный нами выше, то жмем на пункт «Другие функции…» в его самом низу.
- При этом запускается окошко Мастера функций, где пользователь может выбрать любую функцию Excel, которую посчитает для себя полезной. Результат её обработки будут вставлен в соответствующую ячейку строки «Итог».
Читайте также:
Мастер функций в Экселе
Функция промежуточные итоги в Excel
Сортировка и фильтрация
В «умной» таблице по умолчанию при её создании автоматически подключаются полезные инструменты, которые обеспечивают выполнение сортировки и фильтрации данных.
- Как видим, в шапке около наименований столбцов в каждой ячейке уже имеются пиктограммы в виде треугольников. Именно через них мы получаем доступ к функции фильтрации. Жмем на значок около наименования того столбца, над которым мы собираемся производить манипуляцию. После этого открывается список возможных действий.
- Если в столбце расположены текстовые значения, то можно применить сортировку согласно алфавиту или в обратном порядке. Для этого нужно выбрать соответственно пункт «Сортировка от А до Я» или «Сортировка от Я до А».
После этого строки будут выстроены в выбранном порядке.
Если вы попытаетесь отсортировать значения в колонке, которая содержит в себе данные в формате даты, то вам будет предложено на выбор два варианта сортировки «Сортировка от старых к новым» и «Сортировка от новых к старым».
Для числового формата тоже будет предложено два варианта: «Сортировка от минимального к максимальному» и «Сортировка от максимального к минимальному».
- Для того, чтобы применить фильтр, точно таким же образом вызываем меню сортировки и фильтрации, щелкнув по значку в том столбце, относительно к данным которого вы собираетесь задействовать операцию. После этого в списке снимаем галочки с тех значений, строки которых мы хотим скрыть. После выполнения вышеуказанных действий не забываем нажать на кнопку «OK» внизу всплывающего меню.
- После этого останутся видны только строки, около которых в настройках фильтрации вы оставили галочки. Остальные будут спрятаны. Что характерно, значения в строке «Итог» тоже изменятся. Данные отфильтрованных строк не будут учитываться при суммировании и подведении других итогов.
Это особенно важно, учитывая то, что при применении стандартной функции суммирования (СУММ), а не оператора ПРОМЕЖУТОЧНЫЕ.ИТОГИ, в подсчете участвовали бы даже скрытые значения.
Урок: Сортировка и фильтрация данных в Экселе
Преобразование таблицы в обычный диапазон
Конечно, довольно редко, но иногда все же существует необходимость преобразовать «умную» таблицу в диапазон данных. Например, это может произойти в том случае, если нужно применить формулу массива или другую технологию, которую не поддерживает изучаемый нами режим работы Excel.
- Выделяем любой элемент табличного массива. На ленте перемещаемся во вкладку «Конструктор». Щелкаем по иконке «Преобразовать в диапазон», которая расположена в блоке инструментов «Сервис».
- После этого действия появится диалоговое окно, в котором будет спрашиваться, действительно ли мы хотим преобразовать табличный формат в обычный диапазон данных? Если пользователь уверен в своих действиях, то следует нажать на кнопку «Да».
- После этого единый табличный массив будет преобразован в обычный диапазон, для которого будут актуальными общие свойства и правила Excel.
Как видим, «умная» таблица намного более функциональная, чем обычная. С её помощью можно ускорить и упростить решение многих задач по обработке данных. К преимуществам её использования можно отнести автоматическое расширение диапазона при добавлении строк и столбцов, автофильтр, автозаполнение ячеек формулами, строку итогов и другие полезные функции.
О чем речь? Умная таблица в Excel – это особый способ форматирования, который позволяет получить больше возможностей для работы с упорядоченными данными. С ее помощью вы быстрее найдете нужную информацию, а вычисления станут гораздо проще.
Как сделать? Создать умную таблицу не сложно, необходимо нажать буквально пару кнопок. Больше проблем доставляют настройка и форматирование, но, используя нашу инструкцию, вы без проблем справитесь с этой задачей.
В статье рассказывается:
- Суть умной таблицы в Excel
- Задачи и правила работы с умной таблицей
- Создание умной таблицы в Excel
- Настройка умной таблицы в Excel
-
Пройди тест и узнай, какая сфера тебе подходит:
айти, дизайн или маркетинг.Бесплатно от Geekbrains
Умная таблица – это способ форматирования в программе Microsoft Excel, который впервые появился в версии 2007. Суть его в том, что программный алгоритм позволяет обрабатывать данные из нескольких ячеек, расположенных рядом друг с другом, как единый элемент. Если пользователь внесет данные в произвольную ячейку столбца или колонки, располагающихся непосредственно у границы, то эти данные включаются в соответствующий табличный диапазон
Этот вид форматирования имеет отдельные ограничения. В частности, функция объединения ячеек в этом случае может работать некорректно. В первую очередь, эта особенность касается шапки, для которой объединение невозможно в принципе. Также, нередки ситуации, когда нет необходимости включать данные, размещенные в ячейке, находящейся у границ табличного диапазона, в общий массив, однако программа все равно выполнит эту операцию.
В этой связи нужно текстовые данные вводить хотя бы после одного пустого диапазона от массива. Не получится воспользоваться в режиме умной таблицы и функциями формул, станет невозможным совместный просмотр и редактирование книги. Во избежание лишних неполадок нужно следить за именами столбцов, заголовки которых не должны совпадать.
Задачи и правила работы с умной таблицей
Постоянное использование Excel приводит к тому, что при использовании данного приложения необходимо придерживаться определенных требований, позволяющих оптимально использовать возможности программы.
- Каждый столбец должен иметь заголовок, не совпадающий с названиями других столбцов.
- Исключать столбцы и строки, не содержащие данных.
- В одной колонке использовать данные только в одном формате. Например, если в столбце вносятся данные о количестве проданных товаров в штуках, в нем не должны содержаться значения, отражающие сумму продаж в денежном выражении.
Скачать
файл
- Избегать объединения ячеек, когда можно обойтись другими способами представления данных.
- Обеспечивать единообразное форматирование таблицы. Необходимо отмечать отдельные разделы, использовать выделение цветом и т.п.
- Использовать закрепление областей, чтобы заголовок не «убегал» из области обзора.
- Устанавливать опцию фильтра по умолчанию.
- Использовать строку промежуточного итога.
- Внимательно размещать относительные и абсолютные ссылки. Тогда их можно будет протягивать, не внося изменений.
Читайте также
Excel позволяет отметить цветом любое количество столбцов и строк. Однако не стоит этого делать, если в таблицу входит десять колонок и двадцать строк. Конечно, удобнее единожды кликнуть по заголовку любой из них и закрасить неограниченный диапазон, но в этом случае размер файла может увеличиться очень значительно. Это создаст неудобства при необходимости передачи книги по сети.
Вышеприведенные правила достаточно просты. Их соблюдение поможет оптимизировать работу и потратить время на освоение сложных полезных опций, а не на приведение в порядок бездумно составленной таблицы, в которой многие важные функции оказываются неработоспособными.
Excel 13 версии (Excel 2007) учитывает эти требования и предлагает пользователю опцию «умной таблицы», которая значительно облегчает или полностью автоматизирует их выполнение. Опытным юзерам эта функция сильно облегчает работу.
Создание умной таблицы в Excel
Чтобы включить режим «умной таблицы» в Excel, необходимо сделать следующее.
- В окне Excel, в котором открыта ранее сохраненная таблица или введены нужные данные, открываем вкладку «Главная», находим на ней раздел «Стили». В нем имеется кнопка «Форматировать как таблицу».
- На экране отобразится окно, где будет написан адрес таблицы, к которой применяется рассматриваемая функция. Если адрес выбран правильно, нужно кликнуть по кнопке «ОК».
- Для удобства не будет лишним задать для нее уникальное название, это поможет избежать путаницы. Для этого нужно кликнуть по любой ячейке выбранного диапазона и открыть вкладку «Конструктор». На ней есть раздел «Свойства», где помимо названия таблицы можно также изменить и ее размер.
Форматирование таблицы по размерам позволяет выставить маркер. Перемещая его, можно изменить формат таблиц целиком, не искажая данных и сохраняя удобство просмотра.
Настройка умной таблицы в Excel
Фильтры и сортировка
В заголовке таблицы по умолчанию присутствует значок фильтра. Если эта опция не требуется, значок можно отключить. Для этого нужно перейти на вкладку «Конструктор» и перевести соответствующий чек-бокс в нужное положение.
Фильтр предоставляет широкие возможности для операций, которые выполняются с частью данных в столбце. Можно сделать сортировку, скрыть ненужные элементы. Например, если интересуют продажи, которые были осуществлены в декабре, можно легко отключить отображение торговых операций, относящихся к остальным 11 месяцам года.
В больших массивах данных количество ячеек, данные в которых попадают в используемый фильтр, может оказаться очень большим. В этом случае поможет поиск, с которым можно вывести только значения, соответствующие поисковому запросу.
При большом объёме данных таблица может не пометиться целиком в один экран просмотра. В этом случае «умная таблица» позволит закрепить заголовки столбцов в верхней части и использовать опции фильтра при первой необходимости. Не нужно прокручивать книгу обратно, чтобы добраться до нужного чек-бокса.
Автоматическая шапка умной таблицы
Умная таблица в Excel позволяет сделать не только стандартную фильтрацию данных, но и срез по заданному критерию, также представляющий собой разновидность фильтра. К примеру, пользователю необходимо последовательно просмотреть данные о по разным группам товара. Можно отмечать в заголовке каждую из них по отдельности, но это не очень удобно.
Топ-30 самых востребованных и высокооплачиваемых профессий 2023
Поможет разобраться в актуальной ситуации на рынке труда
Подборка 50+ ресурсов об IT-сфере
Только лучшие телеграм-каналы, каналы Youtube, подкасты, форумы и многое другое для того, чтобы узнавать новое про IT
ТОП 50+ сервисов и приложений от Geekbrains
Безопасные и надежные программы для работы в наши дни
Уже скачали 20921 
После применения опции «Срез» отображается панель, в которой перечислены все уникальные значения из соответствующей колонки. Клик по любой из этих групп отобразит данные только по ней и скроет сведения по остальным.
Строка итогов
В разделе «Стили» можно найти опцию строки итогов. Она позволяет отобразить суммарное значение данных, внесенных в соответствующий столбец. При наведении курсора мыши на это значение отображается кнопка вызова контекстного меню с доступными инструментами.
Подсчётом суммы виды итогов не ограничиваются. Можно надстроить их показ в виде количества значений, максимальной и минимальной величины, а также в соответствии с произвольной формулой, заданной пользователем.
Вставка итоговых величин для колонок умной таблицы
Режим фильтров или срезов показывает итог по отображаемым на экране значениям. Это позволяет оперативно получить обобщенные данные по выбранным позициям.
Авторасширение таблицы
Полезной опцией умных таблиц является способность автоматически обрабатывать вновь набранные данные. Так, если ввести их в колонке, непосредственно граничащей с умной таблицей, то она расширится до этих данных с использованием к ним аналогичных параметров форматирования.
Аналогичные возможности предусмотрены и для строки. При этом, если в одной из колонок применяется формула, в нее будет автоматически включено значение из новой строки.
Также расширить или сжать умную таблицу в Excel можно вручную. Для этого требуется захватить курсором мыши маркер в правом нижнем углу заданного массива и перетянуть его в нужное место.
Перемещение строк или колонок
Одна из наиболее полезных опций умной таблицы в Excel – это возможность произвольно перемещать колонки и строки внутри таблицы. Для этого нужно навести указатель мыши на заголовок столбца, а точнее – на границу ячейки. Курсор изменит свой вид. После этого нужно нажать левую копку и поместить колонку в нужное место.
Читайте также
Если нужно переместить строку, то порядок действий аналогичен.
Поскольку в режиме умной таблицы массив данных выделяется в автоматическом режиме, это предотвращает случайное изменение записей, не относящихся к таблице и расположенных в других ячейках.
Удаление дубликатов
Поскольку умная таблица представляет собой элемент, состоящий из массива данных, не требуется для совершения какого-то действия с таблицей выделять всю их совокупность. Достаточно поставить курсор на любую ячейку, которая относится к соответствующему массиву.
В автоматическом режиме можно найти и удалить дубликаты, содержащиеся в столбце. Для этого нужно сначала указать нужный инструмент на вкладке Конструктор, а затем указать колонку, в которой нужно выполнить поиск и удаление задвоенных данных.
При этом очистка от дубликатов оставит нетронутыми записи, которые могут располагаются рядом с умной таблицей.
Этот инструмент позволяет, например, легко вывести перечень уникальных значений, содержащихся в таблице. Впрочем, для этой задачи можно использовать и другие опции, в частности, сводные таблицы.
Сводные таблицы
На панели инструментов «Конструктор» имеется опция создания сводных таблиц, для которой используются значения из умной таблицы. В этом случае в поле сводной таблицы будет указываться не массив ячеек, а назначенное пользователем название умной таблицы, например, «сделки в первом квартале».
Когда в умную таблицу вносятся новые записи, они тут же автоматически учитываются и в сводной таблице. Таким образом, отпадает необходимость расширять диапазон каждый раз вручную.
Единственное, что необходимо сделать – это обновить сводную таблицу, чтобы учесть изменения в умной таблице.
Только до 25.05
Скачай подборку тестов, чтобы определить свои самые конкурентные скиллы
Список документов:
Тест на определение компетенций
Чек-лист «Как избежать обмана при трудоустройстве»
Инструкция по выходу из выгорания
Чтобы получить файл, укажите e-mail:
Подтвердите, что вы не робот,
указав номер телефона:
Уже скачали 7503
Описанная опция упрощает работу в том плане, что пользователю не нужно все время концентрировать внимание на диапазоне значений, из которых формируется таблица. Новые записи будут учитываться автоматически.
Аналогичные инструменты можно применять и к диаграммам.
Диаграммы и графики
Excel позволяет создавать наглядные диаграммы. Они составляются на основе заданного пользователем массива данных. При этом указание в этом массиве новых записей не отразится в диаграмме автоматически, для этого необходимо настроить ее соответствующим образом.
Режим умных таблиц позволяет автоматически подхватывать вновь введённые данные и отобразить их в разделах диаграммы. Это освобождает пользователя от необходимости самостоятельно следить за изменениями диапазона.
Многочисленные достоинства умных таблиц вовсе не означают, что они подходят для всех задач без исключения. Так, они не потребуются для работы со стандартным формами, в которых присутствуют многострочные заголовки с объединёнными ячейками. Если же стоит задача быстро и эффективно обработать массив данных по определённому алгоритму, то для этого умные таблицы предлагают целый ряд полезных опций.
