Перейти к содержимому

Как сделать динамическую таблицу в excel

  • автор:

Динамические таблицы в Excel (с использованием сводной таблицы и формул)

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

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

Например: допустим, в прошлом месяце наш диапазон данных был от A1 до C100. В этом месяце он увеличился с A1: C100 до A1: C200. Если вы уже применили сводную таблицу или любую другую формулу, она работает для диапазона данных за последний месяц, т.е. до ячейки C100, но как насчет обновленного источника данных?

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

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

Мы можем создать динамическую таблицу данных в Excel, используя несколько способов. В этой статье я покажу вам некоторые из них подробно. Следуйте этой статье, чтобы изучить эту технику.

Создать динамический диапазон с помощью таблиц Excel

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

У нас есть еще один инструмент под названием Таблицы данных, который является частью What-If-Analysis. Так что не путайся с этим.

Как создать динамические таблицы в Excel?

Существует два основных способа создания динамических таблиц в Excel — 1) Использование сводной таблицы и 2) Использование формул

Вы можете скачать этот шаблон Excel для динамических таблиц здесь — Шаблон Excel для динамических таблиц

Динамические таблицы в Excel — Использование сводной таблицы

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

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

Шаг 1: Выберите все данные.

Шаг 2. Выберите сводную таблицу на вкладке « Вставка ».

Шаг 3: После того, как стержень вставлен, перетащите Продавца в заголовок Строки и Продажи в Значения.

Шаг 4: Теперь я получил обновления продаж за февраль месяц. Я вставил в данные продаж за январь месяц.

Шаг 5: Если я обновлю сводную таблицу, она не выдаст мне обновленный отчет, поскольку диапазон данных ограничен только от A1 до D11.

Это проблема с нормальными диапазонами данных. Каждый раз, когда нам нужно изменить диапазон источника данных, чтобы обновить наши отчеты.

Создать динамическую таблицу для сокращения ручной работы

Создавая таблицы, мы можем сделать данные динамическими.

Шаг 1: Поместите курсор в любое место в данных о продажах за январь.

Шаг 2: Теперь нажмите Ctrl + T, который является клавишей быстрого доступа для вставки таблиц. Это покажет вам диалоговое окно ниже. Убедитесь, что в моей таблице установлены заголовки .

Шаг 3: Нажмите кнопку ОК, чтобы создать таблицу для вас.

Если вы заметите, как только курсор окажется в любом месте данных, он покажет вам новую вкладку на ленте в виде «Дизайн».

Шаг 4: На вкладке «Дизайн» дайте имя вашей таблице. Дайте имя, которое вам легко понять.

Шаг 5: Теперь вставьте новую сводную таблицу в эту таблицу. (Примените предыдущую технику) Одна из прелестей этой таблицы в том, что вам не нужно выбирать весь набор данных, вместо этого вы можете поместить курсор в таблицу и вставить сводную таблицу.

Шаг 6: Теперь добавьте данные о продажах за февраль за эту таблицу.

Шаг 7: Теперь перейдите к сводной таблице и обновите сводную таблицу. Вы можете нажать сочетание клавиш ALT + A + R + A.

Шаг 8: Это обновит сводную таблицу.

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

Динамические таблицы в Excel — Использование формул

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

Применяя функцию SUMIF к нормальному диапазону данных, я получил итоговые значения продаж каждого продавца.

Теперь я добавлю данные о продажах за февраль в список.

Формула по-прежнему показывает старые значения.

Теперь я буду применять формулу к динамической таблице.

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

= SUMIF (SalesTable (продавец), F2, SalesTable (продажи))

SalesTable: это имя таблицы.

Продавец: Это название столбца, на который мы ссылаемся.

F2: это ссылка на ячейку имени торгового представителя.

Продажи: это снова столбец, на который мы ссылаемся.

В таблицах Excel после создания таблицы все заголовки столбцов становятся их ссылками. В отличие от обычного диапазона данных, мы не увидим никаких диапазонов ячеек. Это так называемые именованные диапазоны, которые создаются самой таблицей Excel.

Что нужно помнить о динамических таблицах в Excel

  • Ctrl + T — это сочетание клавиш для создания таблиц.
  • Все заголовки относятся к соответствующим столбцам.
  • Даже отдельная ячейка в столбце указывается заголовком столбца.

  • Мы также можем создавать динамические именованные диапазоны.

Рекомендуемые статьи

Это было руководство к динамическим таблицам Excel. Здесь мы обсудим, как создать динамическую таблицу в Excel с помощью сводной таблицы и формул, а также с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Использование «умных» таблиц в Microsoft Excel

Умные таблицы в Microsoft Excel

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

Применение «умной» таблицы

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

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

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

Создание «умной» таблицы

Но прежде, чем перейти к описанию возможностей «умной» таблицы, давайте узнаем, как её создать.

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

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

Переформатирование диапазона в Умную таблицу в Microsoft Excel

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

Переформатирование диапазона в Умную таблицу через вкладку Вставка в Microsoft Excel

Наименование

После того, как «умная» таблица сформирована, ей автоматически будет присвоено имя. По умолчанию это наименование типа «Таблица1», «Таблица2» и т.д.

Наименование таблицы в Microsoft Excel

  1. Чтобы посмотреть, какое имя имеет наш табличный массив, выделяем любой его элемент и перемещаемся во вкладку «Конструктор» блока вкладок «Работа с таблицами». На ленте в группе инструментов «Свойства» будет располагаться поле «Имя таблицы». В нем как раз и заключено её наименование. В нашем случае это «Таблица3».
  2. При желании имя можно изменить, просто перебив с клавиатуры название в указанном выше поле.

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

Растягивающийся диапазон

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

Установкеа произвольного значение в ячейку в Microsoft Excel

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

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

Формула подтянулась в новую строку таблицы в Microsoft Excel

Аналогичное добавление произойдет, если мы произведем запись в столбце, который находится у границ табличного массива. Он тоже будет включен в её состав. Кроме того, ему автоматически будет присвоено наименование. По умолчанию название будет «Столбец1», следующая добавленная колонка – «Столбец2» и т. д. Но при желании их всегда можно переименовать стандартным способом.

Новый столбец включен в состав таблицы в Microsoft Excel

Ещё одним полезным свойством «умной» таблицы является то, что как бы много записей в ней не было, даже если вы опуститесь в самый низ, наименования столбцов всегда будут перед глазами. В отличие от обычного закрепления шапок, в данном случае названия колонок при переходе вниз будут размещаться прямо в том месте, где располагается горизонтальная панель координат.

наименования столбцов в Microsoft Excel

Автозаполнение формулами

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

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

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

Данная закономерность касается не только обычных формул, но и функций.

Функция в Умной таблице в Microsoft Excel

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

Адреса в формуле отображаются в обычном режиме в Microsoft Excel

Строка итогов

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

Установка строки итогов в Microsoft Excel

    Для того, чтобы активировать суммирование, выделяем любой табличный элемент. После этого перемещаемся во вкладку «Конструктор» группы вкладок «Работа с таблицами». В блоке инструментов «Параметры стилей таблиц» устанавливаем галочку около значения «Строка итогов».

  • Среднее;
  • Количество;
  • Максимум;
  • Минимум;
  • Сумма;
  • Смещенное отклонение;
  • Смещенная дисперсия.

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

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

  1. Как видим, в шапке около наименований столбцов в каждой ячейке уже имеются пиктограммы в виде треугольников. Именно через них мы получаем доступ к функции фильтрации. Жмем на значок около наименования того столбца, над которым мы собираемся производить манипуляцию. После этого открывается список возможных действий. Открытие меню сортировки и фильтрации в Microsoft Excel
  2. Если в столбце расположены текстовые значения, то можно применить сортировку согласно алфавиту или в обратном порядке. Для этого нужно выбрать соответственно пункт «Сортировка от А до Я» или «Сортировка от Я до А». Варианты сортировки для текстового формата в Microsoft Excel

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

Значения отсортированы от Я до А в Microsoft Excel

Если вы попытаетесь отсортировать значения в колонке, которая содержит в себе данные в формате даты, то вам будет предложено на выбор два варианта сортировки «Сортировка от старых к новым» и «Сортировка от новых к старым».

Варианты сортировки для формата даты в Microsoft Excel

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

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

  1. Выделяем любой элемент табличного массива. На ленте перемещаемся во вкладку «Конструктор». Щелкаем по иконке «Преобразовать в диапазон», которая расположена в блоке инструментов «Сервис». Переход к преобразованию Умной таблицы в диапазон в Microsoft Excel
  2. После этого действия появится диалоговое окно, в котором будет спрашиваться, действительно ли мы хотим преобразовать табличный формат в обычный диапазон данных? Если пользователь уверен в своих действиях, то следует нажать на кнопку «Да». Подтверждение преобразования таблицы в диапазон в Microsoft Excel
  3. После этого единый табличный массив будет преобразован в обычный диапазон, для которого будут актуальными общие свойства и правила Excel.

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

Как работать в Excel с таблицами для чайников: пошаговая инструкция

Программа Microsoft Excel удобна для составления таблиц и произведения расчетов. Рабочая область – это множество ячеек, которые можно заполнять данными. Впоследствии – форматировать, использовать для построения графиков, диаграмм, сводных отчетов.

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

Как создать таблицу в Excel для чайников

Работа с таблицами в Excel для чайников не терпит спешки. Создать таблицу можно разными способами и для конкретных целей каждый способ обладает своими преимуществами. Поэтому сначала визуально оценим ситуацию.

Посмотрите внимательно на рабочий лист табличного процессора:

Электронная таблица.

Это множество ячеек в столбцах и строках. По сути – таблица. Столбцы обозначены латинскими буквами. Строки – цифрами. Если вывести этот лист на печать, получим чистую страницу. Без всяких границ.

Сначала давайте научимся работать с ячейками, строками и столбцами.

Как выделить столбец и строку

Чтобы выделить весь столбец, щелкаем по его названию (латинской букве) левой кнопкой мыши.

Выделить столбец.

Для выделения строки – по названию строки (по цифре).

Выделить строку.

Чтобы выделить несколько столбцов или строк, щелкаем левой кнопкой мыши по названию, держим и протаскиваем.

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

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

  1. Передвинуть вручную, зацепив границу ячейки левой кнопкой мыши. Ширина столбца.
  2. Когда длинное слово записано в ячейку, щелкнуть 2 раза по границе столбца / строки. Программа автоматически расширит границы. Автозаполнение.
  3. Если нужно сохранить ширину столбца, но увеличить высоту строки, воспользуемся кнопкой «Перенос текста» на панели инструментов.

Для изменения ширины столбцов и высоты строк сразу в определенном диапазоне выделяем область, увеличиваем 1 столбец /строку (передвигаем вручную) – автоматически изменится размер всех выделенных столбцов и строк.

Ширина столбцов.

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Автоподбор высоты строки.

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

Выделяем столбец /строку правее /ниже того места, где нужно вставить новый диапазон. То есть столбец появится слева от выделенной ячейки. А строка – выше.

Место для вставки столбца.

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+» Добавить ячейки.» src=»https://exceltable.com/sozdat-tablicu/images/tablica14-10.png» >

Отмечаем «столбец» и жмем ОК.

Теперь при печати границы столбцов и строк будут видны.

Границы таблицы.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

Меню шрифт.

Поменяйте, к примеру, размер шрифта, сделайте шапку «жирным». Можно установить текст по центру, назначить переносы и т.д.

Как создать таблицу в Excel: пошаговая инструкция

Простейший способ создания таблиц уже известен. Но в Excel есть более удобный вариант (в плане последующего форматирования, работы с данными).

Сделаем «умную» (динамическую) таблицу:

  1. Переходим на вкладку «Вставка» — инструмент «Таблица» (или нажмите комбинацию горячих клавиш CTRL+T). Вставка таблицы.
  2. В открывшемся диалоговом окне указываем диапазон для данных. Отмечаем, что таблица с подзаголовками. Жмем ОК. Ничего страшного, если сразу не угадаете диапазон. «Умная таблица» подвижная, динамическая. Таблица с заголовками.

Примечание. Можно пойти по другому пути – сначала выделить диапазон ячеек, а потом нажать кнопку «Таблица».

Умная таблица.

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

Плюс склад.

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

Как работать с таблицей в Excel

С выходом новых версий программы работа в Эксель с таблицами стала интересней и динамичней. Когда на листе сформирована умная таблица, становится доступным инструмент «Работа с таблицами» — «Конструктор».

Конструктор таблиц.

Здесь мы можем дать имя таблице, изменить размер.

Доступны различные стили, возможность преобразовать таблицу в обычный диапазон или сводный отчет.

Возможности динамических электронных таблиц MS Excel огромны. Начнем с элементарных навыков ввода данных и автозаполнения:

  1. Выделяем ячейку, щелкнув по ней левой кнопкой мыши. Вводим текстовое /числовое значение. Жмем ВВОД. Если необходимо изменить значение, снова ставим курсор в эту же ячейку и вводим новые данные.
  2. При введении повторяющихся значений Excel будет распознавать их. Достаточно набрать на клавиатуре несколько символов и нажать Enter. Новая запись.
  3. Чтобы применить в умной таблице формулу для всего столбца, достаточно ввести ее в одну первую ячейку этого столбца. Программа скопирует в остальные ячейки автоматически. Заполнение ячеек таблицы.
  4. Для подсчета итогов выделяем столбец со значениями плюс пустая ячейка для будущего итога и нажимаем кнопку «Сумма» (группа инструментов «Редактирование» на закладке «Главная» или нажмите комбинацию горячих клавиш ALT+» Автосумма.» src=»https://exceltable.com/sozdat-tablicu/images/tablica14-25.png» >Результат автосуммы.

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

Числовые фильтры.

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

Умные таблицы в Excel

Умные таблицы появились в Excel, начиная с 2007 версии. Это привычные многим полосатые таблицы, которые выглядят примерно так:

умная таблица в excel

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

Как создать умную таблицу в Excel

Чтобы из обычной таблицы Excel сделать “умную” таблицу, нужно:

1. Щелкнуть на любой ячейке таблицы.

Обратите внимание, что всю таблицу выделять не обязательно — если в таблице нет пустых строк или столбцов, Excel сам определит границы диапазона.

2. Нажать сочетание клавиш Ctrl + T. Вокруг таблицы появится рамочка- выделение и окно Создать таблицу , в котором указан выделенный диапазон

умная таблица в excel

Если вдруг это выделение не захватило какую-то нужную область, можно выделить область таблицы вручную.

3. Нажать Ок или клавишу Enter. Готово, у нас получилось преобразовать обычный диапазон в умную таблицу Excel.

умная таблица в excel

Также есть второй способ создания умной таблицы.

1. Также щелкаем в любой ячейке таблицы.

2. Вкладка Главная — Блок Стили — Форматировать как таблицу — в Выпадающем окне выбираем стиль таблицы.

умная таблица в excel

3. Далее также смотрим, чтобы выделение захватило весь нужный диапазон и была установлена галочка Таблица с заголовками и нажимаем Ок .

Плюсы и минусы инструмента умные таблицы в Excel

Преимущества инструмента Умные таблицы в Excel

1. Таблица автоматически расширяется при добавлении строк или столбцов.

Главное требование при этом — новый столбец или строка должны добавляться “впритык” к таблице — без пропусков строк или столбцов.

умная таблица в excel

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

3. Автоматически добавляются автофильтры ко всем столбцам. И на столбцы, добавленные уже после создания таблицы, фильтры также “навешиваются” автоматически. При этом фильтр можно убрать (см. в разделе Форматирование умной таблицы)

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

умная таблица в excel

4. Умные таблицы в Excel имеет имя, которое присваивается автоматически, но его можно изменить.

Посмотреть и изменить имя умной таблицы в Excel можно на вкладке Конструктор таблиц (при этом курсор должен быть установлен на любой ячейке в таблице, иначе вкладка Конструктор таблиц не появится на ленте)

умная таблица в excel

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

умная таблица в excel

6. Структурированные формулы, которые легко читать.

В формулах внутри таблицы используются не привычные адреса ячеек и диапазонов, а специальные адреса, отражающие называние столбца или области внутри таблицы. Это облегчает “чтение” формулы (если, конечно, столбцам в таблице были даны релевантные названия).

умная таблица в excel

7. Автоматическое добавление итогов к таблице с возможностью выбора вида итога (сумма, среднее и т.д.).

8. Удобно создавать сводные таблицы на основании умных таблиц. При добавлении новых столбцов или строк в исходную таблицу, они автоматически добавляются в сводную таблицу (после ее обновления) без необходимости настраивать диапазон.

9. Умные таблицы в Excel удобно загружать в Power Query для последующей обработки.

10. Ну и конечно, красивый “полосатый” внешний вид без “танцев с бубнами” с условным форматированием.

Еще одно преимущество “внешности” — легкая настройка форматирования умной таблицы в Excel. На вкладке Конструктор таблиц его можно быстро настроить (см. раздел Форматирование умной таблицы)

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

Минусы инструмента Умные таблицы в Excel
  1. При достаточно большом размере таблицы и большом количестве формул файл с умной таблицей может тормозить. Это связано с тем, что форматирование умной таблицы сделано по типу условного форматирования, которое “нагружает” файл.
  2. Если добавить строку итогов, то функция автоматического расширение «умной таблицы» по строкам при добавлении данных перестает работать. Это логично, т.к. не могут же новые строки с данными быть после строки итогов. Расширить таблицу в этом случае можно только растягиванием за уголок.
  3. Заголовки можно располагать только в первой строке умной таблицы. Это лишает возможность делать сложные «шапки» таблиц, а также использовать одинаковые названия столбцов — если столбцы будут называться одинаково, Excel автоматически добавить к ним цифру 2, 3 и т.д.
  4. Формулы могут становятся очень длинными, поскольку в формулу прописывается наименование столбца. Особенно если заголовки длинные сами по себе. Этот момент ограничивает возможность давать сложные названия столбцам.
  5. Нет возможности закрепить ячейки или столбцы абсолютными/относительными ссылками, и при “протягивании” формулы ссылки съезжают.
  6. Нельзя установить режим совместного доступа в файл, который содержит хотя бы одну “умную” таблицу .

Форматирование умной таблицы

Можно достаточно гибко настраивать форматирование умной таблицы в Excel.

Настройка внешнего вида умной таблицы

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

умная таблица в excel

Также можно создать собственный стиль таблицы умной таблицы в Excel.

Вкладка Конструктор таблиц — блок Стили таблиц — выпадающий список — Создать стиль таблицы . И далее поочередно настроить форматирование для каждого элемента таблицы. Для этого нужно выбрать элемент, нажать на кнопку Формат и выбрать нужные параметры заливки, шрифта и т.д.

умная таблица в excel

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

Также можно очистить стиль умной таблицы.

Это отличное решение для таблиц, которые используются в промежуточных расчетах, и находятся не “на виду”, поскольку отсутствие стиля облегчает работу файла.

Для этого нужно щелкнуть на любой ячейке таблицы — вкладка Конструктор таблиц — блок Стили таблиц — выпадающий список — Очистить.

умная таблица в excel

Теперь таблица не имеет форматирование. Но она не перестала быть умной таблицей! Обратите внимание, что у нее так же есть границы и имя, следовательно, это такая же умная таблица, просто не цветная.

умная таблица в excel

Параметры стиля умных таблиц в Excel

Если вы выбрали предустановленный стиль умной таблицы в Excel, то можно сделать его поднастройку. На вкладке Конструктор таблиц в блоке Параметры стилей таблиц можно настроить параметры установкой/снятием “галочек”

умная таблица в excel

  • Строка заголовков: включает и отключает заголовки в таблице
  • Строка итогов: включает/отключает строку итогов.
  • Чередующиеся строки — включает/отключает “полосатость” таблицы, все строки становятся бесцветными.
  • Чередующиеся столбцы — включает/отключает чередования цвета столбцов. Со включенным параметром столбцы окрашиваются цветом “через один”
  • Первый столбец и Последний столбец — включает/отключает жирный шрифт для первого и последнего столбца в таблице.
  • Кнопка фильтра — включает/отключает автофильтр.

Как изменить имя умной таблицы в Excel

Также на вкладке Конструктор таблиц можно изменить имя таблицы. По умолчанию Excel присваивает таблицам имена типа Таблица1, Таблица2… Однако, лучше их менять на более релевантные, это удобно для использования в формулах.

Чтобы изменить имя умной таблицы, заходим на вкладку Конструктор таблиц — Блок Свойства — в поле Имя таблицы пишем новое имя.

умная таблица в excel

Добавляем срезы (фильтры) к умной таблице

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

Чтобы добавить срез к умной таблице в Excel, нужно перейти на вкладку Конструктор таблиц (должна быть активна любая ячейка внутри таблицы) — блок Инструменты — Вставить срез.

умная таблица в excel

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

умная таблица в excel

Срезы добавлены. Теперь можно фильтровать данные в умной таблице Excel с их помощью.

Как убрать умную таблицу

Иногда нужно преобразовать умную таблицу обратно в диапазон — это значит, что нужно убрать умную таблицу и оставить просто данные.

Для этого переходим на вкладку Конструктор таблиц — блок Инструменты — Преобразовать в диапазон

умная таблица в excel

В появившемся окне нажимаем кнопку Да.

умная таблица в excel

Таблица не изменила свой внешний вид — но она перестала быть умной таблицей (т.е. лишилась всех ее функций — см. раздел Плюсы и минусы умной таблицы в Excel).

В этой статье мы узнали, что такое умные таблицы в Excel, а также познакомились с особенностями таких таблиц.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *