Таблицы в Excel

Таблицы в Excel

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

Использование умного стола

"Умная" таблица — это особый тип форматирования, после применения которого к определенному диапазону данных массив ячеек приобретает определенные свойства. Сначала программа начинает рассматривать его не как круг ячеек, а как целостный элемент. Эта функция появляется в программе, начиная с Excel 2007. Если вы вводите любую из ячеек в строке или столбце, которые находятся непосредственно на границах, эта строка или столбец автоматически включается в этот диапазон таблицы.

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

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

Создайте умный стол

Но прежде чем мы перейдем к описанию возможностей "умный" table, давайте разберемся, как его создать.

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

    Затем мы переходим на вкладку «Главная», если вы сейчас находитесь на другой вкладке в Excel. Затем нажмите кнопку "Форматировать как таблицу" который находится на планке в коробке "Стили" . Затем открывается список различных стилей для массива таблиц. Но выбранный стиль никак не повлияет на функциональность, поэтому нажмите на тот вариант, который вам больше нравится визуально.

    Таблицы в Excel

    Есть еще один вариант форматирования. Таким же образом мы выбираем полностью или частично диапазон, который будем преобразовывать в массив таблиц. Затем переходим в раздел "Вставить", а на панели инструментов "Столы" нажмите на большой значок "Таблица" . Только в этом случае выбор стиля не предусмотрен и будет установлен по умолчанию.

    Таблицы в Excel

    Однако самый быстрый вариант — использовать сочетание клавиш Ctrl + T после выбора ячейки или массива.

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

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

  3. Таблицы в Excel

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

имя

Однажды сформированный "умный" table, ему будет автоматически присвоено имя. По умолчанию это имя типа "Таблица 1" ,, "Таблица 2" и т.п.

  1. Чтобы узнать, какое имя у нашего массива, выделите его элемент и переместите на вкладку "Дизайнер" в разделе "Работа с таблицами" . На коробке с лентой "Имя таблицы" находится в группе "Свойства" . Просто закончилось его имя. В нашем случае это "Таблица 3" .
  2. Таблицы в Excel

  3. При желании имя можно изменить, просто разбив имя в поле выше.

Расширяемый диапазон

Теперь давайте сосредоточимся на том, как новые строки и столбцы добавляются в область видимости таблицы.

  1. Выберите любую ячейку в первой строке под массивом таблицы. Делаем в нем случайную запись.
  2. Таблицы в Excel

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

Формулы для автоматического наполнения

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

  1. Выберите первую ячейку пустого столбца. Вводим туда каждую формулу. Делаем это обычным способом: ставим ячейку "знак равно" , затем щелкните по этим ячейкам, арифметические операции между которыми мы будем выполнять. Поместите метку математического действия между адресами ячеек клавиатуры ( "+" ,, "-" ,, "*" ,, "/" так далее.). Как видите, даже адрес ячеек отображается иначе, чем обычно. Вместо координат, отображаемых на горизонтальной и вертикальной панелях в виде чисел и латинских букв, в этом случае в качестве адреса отображаются названия столбцов того языка, на котором они введены. Знак "@" означает, что ячейка находится в том же порядке, что и формула. В результате вместо обычного случая

    = C2 * D2

    мы получаем выражение для "умный" Таблица:

    = [@ Количество] * [@ Цена]

  2. Таблицы в Excel

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

Порядок агрегатов

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

  1. Чтобы включить суммирование, выберите любой элемент в таблице. Затем переходим к разделу "Дизайнер" в разделе "Работа с таблицами". В наборе инструментов "Параметры стилей таблиц" проверить значение "Строка результатов" .

    Таблицы в Excel

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

  2. Внизу таблицы появится дополнительная строка, которая будет называться "Всего" . Как видите, сумма последнего столбца теперь вычисляется автоматически с помощью встроенной функции ПРОМЕЖУТОЧНАЯ.
  3. Таблицы в Excel

  4. Но мы можем рассчитать общие значения для других столбцов, используя совершенно другие типы сумм. Выберите ячейку в строке "Резюме"щелчком левой кнопки мыши. Как видите, справа от этого элемента появляется значок треугольника. Щелкаем по нему. У нас есть список различных вариантов подведения итогов:
    • Среднее;
    • Число;
    • Максимум;
    • Минимум;
    • количество;
    • Отклонение отклонение;
    • Рассеянная дисперсия.
    • Мы выбираем вариант, который превосходит те результаты, которые мы считаем необходимыми.

      Таблицы в Excel

    • Например, если вы выберете опцию "Количество номеров" , количество ячеек в столбце, заполненных числами, будет отображаться в строке суммы. Это значение будет выводиться той же функцией, что и ПРОМЕЖУТОЧНЫЙ.
    • Таблицы в Excel

    • Если вам не хватает стандартных функций, предоставляемых списком инструментов оценки, описанным выше, щелкните элемент "Другие преимущества …" внизу.
    • Таблицы в Excel

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

    Сортировка и фильтрация

    В "умный" По умолчанию при создании таблицы полезные инструменты автоматически связываются, обеспечивая сортировку и фильтрацию данных.

    1. Как видите, в заголовке рядом с именами столбцов в каждой ячейке уже есть значки треугольников. Через них мы получаем доступ к функции фильтрации. Щелкните значок рядом с названием столбца, которым мы собираемся управлять. Затем отображается список возможных действий.
    2. Таблицы в Excel

    3. Если в столбце есть текстовые значения, вы можете применить сортировку в алфавитном порядке или в обратном порядке. Для этого выберите "Сортировать от А до Я" или "Сортировать от А до Я"соответственно.

      Таблицы в Excel

      После этого строки будут расположены в выбранном порядке.

      Таблицы в Excel

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

      Таблицы в Excel

      Для числового формата будут доступны два варианта: "Сортировать от минимума до максимума" а также "Сортировать от максимума к минимуму" .

    4. Таблицы в Excel

    5. Чтобы применить фильтр, мы вызываем меню сортировки и фильтрации таким же образом, щелкая значок в столбце, для данных которого вы будете использовать операцию. Затем снимаем галочки в списке с тех значений, строки которых мы хотим скрыть. После выполнения вышеуказанного обязательно нажмите кнопку "в порядке" внизу всплывающего меню.
    6. Таблицы в Excel

    7. Тогда будут видны только те строки, вокруг которых вы проверили настройки фильтра. Остальное будет скрыто. Характерно, что значения в порядке "Всего" тоже изменится. Данные из отфильтрованных строк не будут учитываться при подведении итогов и других результатов.

      Таблицы в Excel

      Это особенно важно, учитывая, что при использовании стандартной функции суммирования (SUM), а не оператора INTERMEDIATE.TOTAL, даже скрытые значения будут включены в расчет.

    Преобразование таблицы в нормальный диапазон

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

    1. Выберите каждый элемент массива таблицы. На ленте перейдите на вкладку "Дизайнер" . Щелкните значок "Преобразование области видимости" , который находится в панели инструментов "инструменты" .
    2. Таблицы в Excel

    3. После этого действия появится диалоговое окно с вопросом, действительно ли вы хотите преобразовать формат таблицы в нормальный диапазон данных. Если пользователь уверен в своих действиях, нажмите на кнопку "да" .
    4. Таблицы в Excel

    5. Затем единый массив таблиц будет преобразован в нормальный диапазон, для которого будут актуальны общие свойства и правила Excel.

    Таблицы в Excel

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