Практически все пользователи Excel сталкивались с ситуацией, когда при добавлении новой строки или столбца в массив таблиц нужно пересчитывать формулы и форматировать этот элемент в соответствии с общим стилем. Этих проблем не было бы, если бы вместо обычного варианта использовалась так называемая «умная» таблица. Это автоматически «потянет» за собой все элементы, которые пользователь размещает по краям. После этого Excel начинает воспринимать их как часть диапазона таблиц. Это не полный список того, для чего полезна умная таблица. Давайте узнаем, как его создать и какие функции он предлагает.
Применение «умной» таблицы
«Умная» таблица — это особый вид форматирования, после которого она применяется к указанному диапазону данных, массив ячеек приобретает определенные свойства. Во-первых, после этого программа начинает рассматривать его не как диапазон ячеек, а как единый элемент. Эта функция появилась в программе, начиная с версии Excel 2007. Если вы вставляете запись в любую из ячеек строки или столбца, которые находятся непосредственно на границах, эта строка или столбец автоматически включается в этот диапазон таблиц.
Использование этой технологии позволяет не пересчитывать формулы после добавления строк, если данные из нее извлекаются в другом диапазоне определенной функцией, например ВПР. Кроме того, среди достоинств следует выделить блокировку заголовка вверху листа, а также наличие кнопок фильтра в заголовках.
К сожалению, эта технология также имеет некоторые ограничения. Например, использование слияния клеток нежелательно. Особенно это касается шапки. Для нее вообще недопустимо объединение элементов. Кроме того, даже если вы не хотите, чтобы значение, расположенное на границах массива таблицы, было включено в него (например, примечание), оно все равно будет рассматриваться Excel как его неотъемлемая часть. Следовательно, все ненужные метки должны быть помещены хотя бы в один пустой диапазон из массива таблицы. Кроме того, формулы матриц не будут работать, и книга не сможет быть опубликована. Все имена столбцов должны быть уникальными, то есть не должны повторяться.
Создание «умной» таблицы
Но прежде чем мы перейдем к описанию функций умного стола, давайте узнаем, как его создать.
- Выберите диапазон ячеек или любой элемент массива, к которому мы хотим применить форматирование таблицы. Дело в том, что даже если вы выберете элемент массива, программа во время процедуры форматирования захватит все соседние элементы. Поэтому не имеет значения, выбираете ли вы весь целевой диапазон или только его часть.
Затем перейдем к вкладке «Главная», если вы сейчас находитесь на другой вкладке Excel. Затем нажмите кнопку «Форматировать как таблицу», расположенную на ленте в панели инструментов «Стили». Затем открывается список с выбором различных стилей для массива таблиц. Но выбранный стиль никак не повлияет на функциональность, поэтому давайте выберем вариант, который вам больше всего нравится визуально.
Есть еще один вариант форматирования. Точно так же мы выбираем весь или часть диапазона, который собираемся преобразовать в массив таблиц. Затем переключитесь на вкладку «Вставка» и на ленте на панели инструментов «Таблицы» щелкните большой значок «Таблица». Только в этом случае выбор стиля не предусмотрен и будет установлен по умолчанию.
Но самый быстрый вариант — использовать горячие клавиши Ctrl + T после выбора ячейки или массива.
Для любого из вышеперечисленных вариантов открывается небольшое окно. Содержит адрес диапазона, который будет преобразован. В подавляющем большинстве случаев программа правильно определяет диапазон независимо от того, выбрана вся или только одна ячейка. Но в любом случае на всякий случай нужно проверить адрес массива в поле и, если он не совпадает с нужными вам координатами, изменить его.
Также не забудьте установить флажок рядом с таблицей с заголовками, так как в большинстве случаев исходный набор данных уже имеет заголовки. Убедившись, что все параметры введены правильно, нажмите кнопку «ОК».
Наименование
После создания интеллектуальной таблицы ей автоматически будет присвоено имя. По умолчанию это имя выглядит как «Таблица1», «Таблица2» и т.д.
- Чтобы увидеть, какое имя имеет наш массив таблиц, выберите один из его элементов и перейдите на вкладку «Дизайн» блока вкладок «Работа с таблицами». На ленте в группе инструментов «Свойства» вы найдете поле «Имя таблицы». Он содержит только его имя. В нашем случае это Table3».
- При желании имя можно изменить, просто прервав имя в поле выше с клавиатуры.
Теперь при работе с формулами, чтобы сообщить конкретной функции, что ей нужно обрабатывать весь диапазон таблиц, вместо обычных координат достаточно будет ввести ее имя в качестве адреса. Причем это не только удобно, но и практично. Если вы используете стандартный адрес в виде координат, то при добавлении строки в нижнюю часть массива таблицы, даже после того, как она была включена в его состав, функция не будет захватывать эту строку для обработки, и аргументы должны быть снова сломаться. Если вы укажете адрес в виде имени диапазона таблиц в качестве аргумента функции, все строки, добавленные в будущем, будут автоматически обработаны функцией.
Растягивающийся диапазон
Теперь давайте сосредоточимся на том, как новые строки и столбцы добавляются в диапазон таблицы.
- Выберите ячейку в первой строке под таблицей. Вставляем произвольную запись.
- Затем нажмите клавишу Enter на клавиатуре. Как видите, после этого действия вся строка, содержащая только что добавленную запись, была автоматически включена в массив таблицы.
Кроме того, автоматически применялось то же форматирование, что и для остальной части диапазона таблицы, и все формулы, расположенные в соответствующих столбцах, были ограничены.
Аналогичное добавление произойдет, если мы вставим запись в столбец, который находится на краю массива таблицы. Он также войдет в его состав. Кроме того, ему автоматически будет присвоено имя. По умолчанию имя будет «Столбец1», следующий добавляемый столбец — «Столбец2» и так далее. Но при желании их всегда можно стандартным образом переименовать.
Еще одно полезное свойство «умной» таблицы состоит в том, что независимо от количества записей, даже если вы спуститесь вниз, имена столбцов всегда будут прямо у вас на глазах. В отличие от обычного блока заголовка, в этом случае имена столбцов будут размещены там, где горизонтальная полоса координат при движении вниз.
Автозаполнение формулами
Ранее мы видели, что когда новая строка добавляется к ее ячейке того столбца массива таблиц, где уже есть формулы, эта формула копируется автоматически. Но то, как мы работаем с данными, которые мы изучаем, может сделать больше. Просто заполните ячейку пустого столбца формулой, чтобы она автоматически скопировалась во все остальные элементы этого столбца.
- Выберите первую ячейку пустого столбца. Ставим туда любую формулу. Делаем это обычным образом: ставим знак «=» в ячейку, затем нажимаем на те ячейки, арифметическую операцию между которыми мы будем выполнять. Между адресами ячеек клавиатуры ставим знак математического действия («+», «-», «*», «/» и так далее). Как видите, адрес ячейки также отображается иначе, чем обычно. Вместо координат, отображаемых на горизонтальной и вертикальной панелях в виде цифр и латинских букв, в этом случае имена столбцов на том языке, на котором они были введены, отображаются в виде адреса. Символ @ указывает, что ячейка находится в той же строке, что и формула. Следовательно, вместо формулы в обычном случае
получаем выражение для умной таблицы:
- Теперь, чтобы просмотреть результат на листе, нажмите клавишу Enter. Но, как видите, расчетное значение отображается не только в первой ячейке, но и во всех остальных элементах столбца. То есть формула автоматически копировалась в другие ячейки и для этого даже не было необходимости использовать маркер заполнения или другие стандартные инструменты копирования.
Эта модель применима не только к обычным формулам, но и к функциям.
Также следует отметить, что если пользователь вводит адреса элементов других столбцов в целевой ячейке в виде формулы, они будут отображаться как обычно, как и для любого другого диапазона.
Строка итогов
Еще одна интересная функция, обеспечиваемая режимом работы, описанным в Excel, — вывод итогов по столбцам в отдельной строке. Для этого не нужно вручную добавлять строку специальным образом и вводить формулы суммирования, так как инструментарий «умных» таблиц уже имеет в своем арсенале заготовки необходимых алгоритмов.
- Чтобы активировать суммирование, выберите любой табличный элемент. Затем перейдите на вкладку «Дизайн» группы вкладок «Работа с таблицами». В панели инструментов «Параметры стиля таблицы» поставьте галочку рядом со значением «Итоговая строка».
Для активации итоговой строки вместо предыдущих действий также можно использовать сочетание клавиш Ctrl + Shift + T.
Далее внизу массива таблицы появится дополнительная строка, которая будет называться «Итого». Как видите, сумма последнего столбца уже была рассчитана автоматически с помощью встроенной функции INTERMEDIATE.TOTAL.
- Но мы можем рассчитать итоги для других столбцов и использовать совершенно другие типы итогов. Выделите ячейку в строке «Итого», щелкнув левой кнопкой мыши. Как видите, справа от этого элемента появляется значок треугольника. Щелкаем по нему. Перед нами открывается список различных вариантов, чтобы подвести итог:
- В среднем;
- Количество;
- Максимум;
- Минимум;
- Сумма;
- Отклонение сдвинуто;
- Сдвинутая дисперсия.
Выбираем вариант отметки результатов, которые считаем необходимыми.
Например, если мы выберем параметр «Количество чисел», в итоговой строке будет отображаться количество ячеек в столбце, заполненных числами. Это значение будет выводиться самой функцией INTERMEDIATE.TOTAL.
Если стандартных функций, предоставляемых списком инструментов сводки, описанным выше, вам недостаточно, щелкните запись «Дополнительные функции…» внизу.
- Откроется окно мастера, в котором пользователь может выбрать любую функцию Excel, которую он сочтет полезной. Результат его обработки будет занесен в соответствующую ячейку строки «Итого».
Сортировка и фильтрация
Умная таблица автоматически включает полезные инструменты, которые обеспечивают сортировку и фильтрацию данных, когда она создается по умолчанию.
- Как видите, в шапке рядом с названиями столбцов в каждой ячейке уже есть пиктограммы в виде треугольников. Именно через них мы получаем доступ к функции фильтрации. Щелкните значок рядом с названием столбца, которым мы собираемся управлять. Далее открывается список возможных действий.
Если столбец содержит текстовые значения, их можно отсортировать в алфавитном порядке или в обратном порядке. Для этого выберите «Сортировать от А до Я» или «Сортировать от Я до А» соответственно».
Впоследствии линии будут выровнены в выбранном порядке.
Если вы попытаетесь отсортировать значения в столбце, содержащем данные в формате даты, вам будет предложен выбор между двумя вариантами сортировки: «Сортировать старые по новым» и «Сортировать с новых по старым».
Для числового формата вам также будут предложены два варианта: «Сортировать от минимального к максимальному» и «Сортировать от максимального к минимальному».
Чтобы применить фильтр, точно так же вызовите меню сортировки и фильтрации, щелкнув значок в столбце, для которого вы хотите использовать операцию. Далее в списке снимаем галочки со значений, строки которых мы хотим скрыть. После выполнения вышеуказанных шагов не забудьте нажать кнопку «ОК» внизу всплывающего меню.
После этого будут видны только те линии, рядом с которыми вы поставили галочку в настройках фильтра. Остальное будет скрыто. Примечательно, что значения в строке «Итого» также изменятся. Данные из отфильтрованной строки не будут включены в сводку и другие итоги.
Это особенно важно, поскольку использование стандартной функции суммы (SUM) вместо оператора INTERMEDIATE.TOTAL также может включать скрытые значения.
Преобразование таблицы в обычный диапазон
Конечно, довольно редко, но иногда все же необходимо преобразовать умную таблицу в диапазон данных. Например, это может произойти, когда вам нужно использовать формулу массива или другую технологию, которая не поддерживается в режиме работы Excel, который мы изучаем.
- Выбираем любой элемент массива таблицы. На ленте перейдите на вкладку «Дизайн». Щелкните значок «Преобразовать в диапазон», расположенный на панели инструментов «Инструменты».
После этого действия появится диалоговое окно с вопросом, действительно ли мы хотим преобразовать табличный формат в нормальный диапазон данных? Если пользователь уверен в своих действиях, нажмите кнопку «Да».
- После этого единый массив таблиц будет преобразован в обычный диапазон, для которого будут актуальны общие свойства и правила Excel.
Как видите, «умный» стол намного функциональнее обычного. С его помощью вы можете ускорить и упростить решение многих задач обработки данных. Преимущества его использования включают автоматическое расширение диапазона при добавлении строк и столбцов, автоматический фильтр, автоматическое заполнение ячеек формулами, строку итогов и другие полезные функции.