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

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

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

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

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

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

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

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

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

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

   

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

   

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

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

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



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

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

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

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

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





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

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

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

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

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



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

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

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

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

Урок: Как добавить новую строку в Excel

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

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

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

   =C2*D2

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

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



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

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

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

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

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

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

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

2. После этого в самом низу табличного массива появится дополнительная строка, которая так и будет называться – «Итог». Как видим, сумма последнего столбца уже автоматически подсчитана с помощью встроенной функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.



3. Но мы можем подсчитать суммарные значения и для других столбцов, причем использовать при этом совершенно разные виды итогов. Выделяем щелчком левой кнопки мыши любую ячейку строки «Итог». Как видим, справа от этого элемента появляется пиктограмма в виде треугольника. Щелкаем по ней. Перед нами открывается список различных вариантов подведения итогов:
   • Среднее;
   • Количество;
   • Максимум;
   • Минимум;
   • Сумма;
   • Смещенное отклонение;
   • Смещенная дисперсия.

   Выбираем тот вариант подбития итогов, который считаем нужным.



4. Если мы, например, выберем вариант «Количество чисел», то в строке итогов отобразится количество ячеек в столбце, которые заполнены числами. Данное значение будет выводиться все той же функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.



5. Если вам недостаточно тех стандартных возможностей, которые предоставляет список инструментов подведения итогов, описанный нами выше, то жмем на пункт «Другие функции…» в его самом низу.



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

Читайте также:
Мастер функций в Экселе
Функция промежуточные итоги в Excel

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

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

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



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

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

   

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

   

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



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



4. После этого останутся видны только строки, около которых в настройках фильтрации вы оставили галочки. Остальные будут спрятаны. Что характерно, значения в строке «Итог» тоже изменятся. Данные отфильтрованных строк не будут учитываться при суммировании и подведении других итогов.
   

   Это особенно важно, учитывая то, что при применении стандартной функции суммирования (СУММ), а не оператора ПРОМЕЖУТОЧНЫЕ.ИТОГИ, в подсчете участвовали бы даже скрытые значения.

Урок: Сортировка и фильтрация данных в Экселе

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

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

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



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



3. После этого единый табличный массив будет преобразован в обычный диапазон, для которого будут актуальными общие свойства и правила Excel.

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

Видео

Постановка задачи

Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера — вот такого вида:

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):

В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:

В результате после такого преобразования диапазона в «умную» Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

1. Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
2. Созданная один раз Таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

   

3. В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)).
4. При добавлении новых строк в них автоматически копируются все формулы.
5. При создании нового столбца с формулой — она будет автоматически скопирована на весь столбец — не надо тянуть формулу черным крестом автозаполнения.
6. При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):

   
7. Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:

   

8. К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица1[НДС]) вместо =СУММ(F2:F200) и не думать уже про размеры таблицы, количество строк и корректность диапазонов выделения. Также возможно использовать еще следующие операторы (предполагается, что таблица имеет стандартное имя Таблица1):

• =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
• =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
• =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
• =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
• =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы.
  (В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

P.S.

В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list). Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

Сегодня поговорим про ТАБЛИЦЫ. Не про таблицы, а именно про ТАБЛИЦЫ . Именно так Microsoft предложил называть те замечательные таблицы, о которых пойдёт речь ниже. В зачаточном состоянии они появились в Excel 2003 и назывались там » списками » («lists»). В Excel 2007 их довели до ума и переименовали в ТАБЛИЦЫ (TABLES), а то что раньше все нормальные люди называли таблицами, теперь предложено называть ДИАПАЗОНОМ (range). В России этот подход не прижился, да и чего ради людям менять задним числом устоявшиеся термины, поэтому TABLES мы будем называть » умными таблицами «, а таблицы в их общеупотребительном понимании оставим в покое.

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

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

Зачем они нужны?

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

1. Всем столбцам давать уникальные названия колонок.
2. Не допускать пустых столбцов и строк в таблице.
3. Не допускать разнородных данных в пределах одной колонки. Если уж решили, что, например, в колонке E должен хранится объем продаж в штуках, то не надо туда же вносить объём продаж, скажем, в деньгах у части строк таблицы.
4. Не объединять ячейки без самой крайней необходимости.
5. Форматировать таблицу, чтобы она выглядела одинаково во всех своих частях. То есть элементарно рисовать сетку, выделять цветом заголовки столбцов.
6. Закреплять области, чтобы заголовок был всегда виден на экране.
7. Ставить фильтр по умолчанию.
8. Вставлять строку подитогов.
9. Грамотно использовать абсолютные и относительные ссылки в формулах, чтобы их можно было протягивать без необходимости внесения изменений.
10. При рабте с таблицей не выделять цветом строки/столбцы за пределами таблицы. Это поветрие, кстати очень сильно распространено, — взять выделить всю строку или весь столбец одним кликом мыши и закрасить. И наплевать, что в таблице 100 строк, а закрасилось помимо них ещё 1 000 000 строк. А потом невинно интересоваться: «Почему мои файлы так много весят?»

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

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

Итак, давайте познакомимся, как создаются умные таблицы и какими полезными свойствами обладают.

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

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

1. Способ 1 — на ленте ГЛАВНАЯ выбираем Форматировать как таблицу , выбираем понравишейся дизайн (при этом вам доступны 60 стандартных способа форматирования)
2. Способ 2 — Нажимаем Ctrl-T
3. Способ 3 — На ленте ВСТАВКА выбрать Таблица

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

2.Форматирование

Если до того, как поумнеть, ваша таблица имела форматирование (рамки, цвета букв и фона и т.п.), то возможно вам стоит это форматирование сбросить, чтобы оно не «конфликтовало» с форматированием умной таблицы. Для этого:

1. Выделите таблицу целиком — проще всего 2 раза нажать Ctrl-A (латинская «A»!)
2. На ленте ГЛАВНАЯ щёлкните Стили ячеек , далее стиль Обычный

При этом все проблемы с форматированием сразу решаются. Однако вам придётся восстанавливать форматы столбцов ячеек: формат даты, времени, нюансы числового формата (типа количества знаков после точки), но это не очень сложно. В любом случае вам решать — сбрасывать форматирование этим способом, либо каким-то другим, менее «разрушительным», но знать о нём надо.

3.Предпросмотр стиля таблицы

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

4.Прочие плюшки и полезности.

1. Чередующийся цвет строк или столбцов! Да знаете ли вы, что раньше для этого надо было 10 минут колдовать с условным форматированием с бубном и крысиными костями!. «А теперь? Оглянитесь вокруг, — какие вам корпуса понастроили, какие газоны разбили, водопровод, телевизор, газовая кухня, парники, цветники. «
2. Включение строки итогов одним нажатием!
3. Фильтр по умолчанию
4. Первый и последний столбец могут быть выделены жирным шрифтом
5. При прокрутке таблицы столбцы видны БЕЗ закрепления областей! Чего ж вам боле?!

5.Упрощенное выделение таблицы, столбцов, строк

6.Умная таблица имеет имя и его можно изменять

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

7.Вставка срезов.

В Excel 2010 появилась такая полезная функция как срезы . Это наглядные фильтры, которые можно добавлять к сводным таблицам, а также и к умным таблицам тоже. Посмотрим как это работает:

8.Структурированные формулы.

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

Предупреждение для любителей полазить по иностранным сайтам: товарищи, учитывайте различия в региональных настройках России и западных стран (англоязычных то точно). В региональных настройках есть такой параметр, как » Разделитель элементов списка «. Так вот на Западе это запятая , а у нас точка с запятой . Поэтому, когда они пишут формулы в Excel, то параметры разделяются запятыми , а когда мы пишем, то — точкой с запятой . Тоже самое и в переводных книгах, везде.

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

Видео

Постановка задачи

Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера — вот такого вида:

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table) :

В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:

В результате после такого преобразования диапазона в «умную» Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):

1. Созданная Таблицаполучает имяТаблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design) . Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
2. Созданная один раз Таблицаавтоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки — она растянется ниже, если добавить новые столбцы — разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:

• =Таблица1[#Все] — ссылка на всю таблицу, включая заголовки столбцов, данные и строку итогов
• =Таблица1[#Данные] — ссылка только на данные (без строки заголовка)
• =Таблица1[#Заголовки] — ссылка только на первую строку таблицы с заголовками столбцов
• =Таблица1[#Итоги] — ссылка на строку итогов (если она включена)
• =Таблица1[#Эта строка] — ссылка на текущую строку, например формула =Таблица1[[#Эта строка];[НДС]] — будет ссылаться на значение НДС из текущей строки таблицы.

(В англоязычной версии эти операторы будут звучать, соответственно, как #All, #Data, #Headers, #Totals и #This row).

В Excel 2003 было что-то отдаленно похожее на такие «умные» таблицы — называлось Списком и создавалось через меню Данные — Список — Создать список (Data — List — Create list) . Но даже половины нынешнего функционала там не было и в помине. В более древних версиях Excel не было и этого.

«Умная таблица» в Excel

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

Общие правила создания и заполнения таблиц

Чтобы ваша таблица, «обычная» или «умная», правильно работала, следует придерживаться при ее создании и заполнении следующих несложных правил:

• не использовать в таблице объединенные ячейки;
• присваивать графам (колонкам) уникальные имена;
• в одной графе использовать один формат данных;
• не оставлять при заполнении таблицы пустых строк;
• в «обычной» таблице следует выделить строку заголовков (шапку таблицы) форматированием (цвет, шрифт) и закрепить на начальном экране.

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

Для создания «Умной таблицы» необходимо выбрать любую ячейку внутри таблицы без форматирования или выделить произвольный диапазон, в котором планируется создать такую таблицу, и нажать кнопку «Форматировать как таблицу» на вкладке «Главная». Откроется окно выбора формата будущей «Умной таблицы»:

Выбрать можно любой образец форматирования таблицы и нажать на него, а после создания «Умной таблицы» точнее подобрать форматирование с помощью предпросмотра. После нажатия на образец формата программа Excel предложит проверить диапазон будущей таблицы и выбрать, где будет создана строка заголовков (шапка таблицы) – внутри таблицы, если она уже с заголовками, или над таблицей в новой строке:

В примере заголовки уже присутствуют внутри диапазона с таблицей, поэтому галочку «Таблица с заголовками» оставляем. Нажав «OK», получим следующую «Умную таблицу»:

Теперь при записи формулы создаются адреса с именами колонок, а при нажатии «Enter» формула автоматически копируется во все ячейки этой графы:

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

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

При выборе любой ячейки внутри «Умной таблицы» на панели инструментов появляется вкладка «Работа с таблицами Конструктор». Перейти в нее можно, нажав на слово «Конструктор».

На вкладке «Конструктор» отображены все инструменты для работы с «Умной таблицей» (неполный перечень):

• редактирование имени таблицы;
• изменение цветового чередования строк на цветовое чередование столбцов;
• добавление строки итогов;
• удаление кнопок автофильтра;
• изменение стиля таблицы (то же, что и по кнопке «Форматировать как таблицу» на вкладке «Главная»);
• удаление дубликатов;
• добавление срезов*, начиная с Excel 2010;
• создание сводной таблицы;
• удаление функционала «Умной таблицы» командой «Преобразовать в диапазон».

*Срезы представляют из себя удобные фильтры по графам в отдельных окошках, работающие аналогично кнопкам автофильтра в строке заголовков. Создается срез (или срезы) нажатием кнопки «Вставить срез» и выбором нужной колонки (или колонок). Чтобы удалить срез, его нужно выбрать и нажать на клавиатуре «Delet» или пункт «Удалить (имя среза)» в контекстном меню.

Работа с «Умной таблицей»

Начните заполнять новую строку или новый столбец, и после заполнения первой ячейки «Умная таблица» автоматически расширится на эту строку или на этот столбец. Если в таблице использовались формулы, то при добавлении новой строки они будут автоматически в нее скопированы.

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

Чтобы автоформа всегда была доступной, ее кнопку необходимо добавить на панель быстрого доступа (вверху слева). Для непосредственного перехода к настройке панели быстрого доступа нажмите на правую кнопку этой панели в виде черточки с треугольником внизу. Откроется следующее меню:

В этом меню необходимо выбрать пункт «Другие команды…» для перехода к окну настройки панели быстрого доступа:

Здесь необходимо выбрать в верхнем раскрывающемся списке «Команды не на ленте», в окне со списком команд выделить пункт «Форма…», нажать кнопку «Добавить >>», а затем кнопку «OK». На панели быстрого доступа появится кнопка вызова автоформы.

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

Обратите внимание, что ячейка с формулой не редактируется. Добавление новой записи или сохранение внесенных изменений в существующую строку происходит при нажатии кнопок «Добавить», «Закрыть» или «Enter» на клавиатуре. При нажатии кнопок «Назад» или «Далее» внесенные изменения не сохраняются.

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

Самым простым способом было бы вставить формулу =[предыдущая ячейка]+1 , но она не копируется автоматически в новую запись. А в ячейках с этой формулой программа Excel выводит замечание: «Несовместимая формула в вычисляемом столбце».

Для нашего примера подойдет следующая формула: =СТРОКА([@Наименование])-1 , которая вычисляет номер текущей строки рабочего листа Excel и уменьшает ее на единицу, так как первая запись в нашей «Умной таблице» начинается со второй строки рабочего листа Excel.

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

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

К преимуществам «Умной таблицы» можно отнести следующие:

• автоматическое форматирование по выбранному шаблону;
• автоматическое расширение «Умной таблицы» при добавлении новых записей или колонок;
• автоматическое копирование формул в новую запись;
• возможность добавления строки итогов (критерий расчета итога для каждой отдельной графы можно выбрать из раскрывающегося списка);
• возможность использования автоформы* для добавления новых записей и редактирования существующих (такая же возможность существует и для обычных таблиц).

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

Умные таблицы excel

Начиная с Excel 2007 появилась возможность использовать так называемые «умные» таблицы Excel .

Для того чтобы преобразовать обычную таблицу в «умную» нужно выделить в обычной таблице любую ячейку, на вкладке меню Главная развернуть список Форматировать как таблицу и выбрать один из понравившихся стилей (см. рисунок выше) , либо использовать команды меню Вставка -> Таблица .

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

Сначала о достоинствах:

• преобразование вашей таблицы в «умную» придаст ей презентабельный «полосатый» внешний вид;
• «умная» таблица автоматически увеличивается размерах при добавлении данных правее последнего столбца или ниже последней строки. Причем при добавлении строк формулы в ячейках сохраняются. Еще один способ добавить в таблицу строки и столбцы — просто «потащить» ее за правый нижний угол;
• можно мгновенно создать строку итогов с результатами на ваш выбор (сумма, количество, среднее, минимум, максимум и т.д.);
• Excel автоматически присваивает имена столбцам «умной» таблицы, которые можно использовать в формулах.
• с умной таблицей очень удобно работать в надстройке Power Query . На мой взгляд это главное достоинство умных таблиц.

Недостатки «умной» таблицы – это продолжение ее достоинств:

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

Кстати, если вам так хочется «полосатости», это легко можно сделать и для обычной таблицы. Достаточно задать для вашей таблицы правило условного форматирование вот с такой формулой: =ЕЧЁТН(СТРОКА()

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

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

• Первой строкой умной таблицы должны быть заголовки столбцов. Аналогичное требования предъявляются к исходным данным при создании сводных таблиц. Это лишает вас возможности делать сложные шапки, а также использовать одну из фишек ExcelSoft — строку суперфильтра.
• Если адресоваться к столбцам таблицы, используя наименования столбцов, которые «умная таблица» присвоила автоматически, то формулы могут стать «трехэтажными».

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

Второй вариант — использовать в формуле имя, присвоенное умной таблицей, например Таблица 1[Наименование контрагента] . Но если у вас сложные формулы, они станут в разы длиннее и просто нечитабельными.

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

Вот пример не самой длинной формулы в ячейке на листе «Контрагенты» в программе ES: “Финансовое планирование и учет” , которая рассчитывает сальдо по контрагенту:

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

• На защищенных листах функция ставки строк растаскивание за уголок или автоматическое расширение при добавлении данных не работает. Вставить строки внутри «умной» таблицы на защищенном листе также невозможно. И если для обычных таблиц есть «костыль» – использование методов Insert , Delete в макросах VBA, то умные таблицы Excel эти методы не поддерживают и как решить проблему вставки строк с помощью макросов пока непонятно.

• В Excel есть очень полезный инструмент – так называемые Представления . Представления позволяют запоминать внешний вид таблицы на заданном листе (закрепление областей, скрытые и видимые строки и столбцы, условия фильтрации и т.д.), что особенно удобно при подготовке отчетов. Более подробно о Представлениях можно почитать здесь . К сожалению, если в рабочей книге есть хоть одна «умная» таблица, использование Представлений становится невозможным.

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

Представьте себе, как будет обидно, если вы сделали программу, отладили формулы (которые обращаются непосредственно к колонкам «умной» таблицы) и тут такой облом. Переделывать придется очень много. Я собственно так и попался в первый раз.

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

В заключении нужно отметить, что «умные» таблицы — это будущее Excel , уже наступившее:). Рано или поздно на них перейдут все . А по имеющимся недостаткам будем надеяться, что компания Microsoft исправит их добавит в коробочные версии MS Office .

Работа с таблицами в Excel

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

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

Понятие умных таблиц

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

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

Поэтому можно выделить отдельное название для таблицы Excel – «Умная таблица» или Smart Table.

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

Предположим, мы создали диапазон данных с информацией о продажах.

Это еще не таблица. Чтобы превратить диапазон в нее, необходимо его выделить и найти вкладку «Вставка» и там найти кнопку «Таблица» в одноименном блоке.

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

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

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

Понятие структуры таблицы Excel

Все таблицы имеют определенное имя, отображаемое на специальной вкладке «Конструктор». Она показывается сразу после выделения любой ячейки. Стандартно имя принимает форму «Таблица 1» или «Таблица 2», и соответственно.

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

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

Давайте нашу таблицу назовем «Отчет». Имя можно увидеть в окне, которое называется диспетчером имен. Чтобы его открыть, необходимо пройти по следующему пути: Формулы – Определенные Имена – Диспетчер имен.

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

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

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

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

Если необходимо суммировать содержимое всего столбца с продажами, необходимо написать следующую формулу:

После этого она автоматически превратится в =СУММ(Отчет[Продажи]). Простыми словами, ссылка будет вести на конкретную колонку. Удобно, согласитесь?

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

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

Таблицы Excel: свойства

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

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

Также в функционал входит автофильтр. Но если он не нужен, его всегда можно отключить в настройках.

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

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

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

Все эти свойства – хорошо. Но ведь можно самостоятельно настроить таблицу и расширить ее функционал.

Настройка Таблицы

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

Предусмотрены следующие возможности:

1. Добавление или удаление строки заголовков.
2. Добавление или удаление строки с итогами.
3. Сделать строки чередующимися.
4. Выделение крайних колонок жирным начертанием.
5. Включение или отключение чередующейся заливки строк.
6. Отключение автофильтра.

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

Также можно найти группу «Инструменты», где возможно создание сводной таблицы, удаление копий и преобразование таблицы в стандартный диапазон.

Но наиболее занимательная функция – создание срезов.

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

Все, теперь появляется панель, где перечислены все уникальные значения, содержащиеся в ячейках этой колонки.

Чтобы отфильтровать таблицу, необходимо выбрать категорию, которая наиболее интересна в данный момент.

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

Чтобы выставлять параметры непосредственно на ленте, можно воспользоваться одноименной вкладкой. С ее помощью возможно редактирование разных свойств среза: внешнего вида, размера кнопок, количества и так далее.

Основные ограничения умных таблиц

Несмотря на то, что таблицы Excel имеют множество преимуществ, все же пользователю придется мириться с некоторыми минусами:

1. Представления не функционируют. Простыми словами, нет возможности запомнить определенные параметры листа.
2. Нельзя использовать книгу вместе с другим человеком.
3. Нет возможности вставлять промежуточные итоги.
4. Нельзя использовать формулы массивов.
5. Нет возможности объединять ячейки. Но этого не рекомендуется делать и так.

Впрочем, достоинств значительно больше, чем недостатков, поэтому эти минусы будут не очень заметными.

Примеры использования умных таблиц

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

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

Подведение итогов с помощью функционала Excel

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

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

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

1. Среднее арифметическое.
2. Количество.
3. Максимум.
4. Смещенное отклонение.

И многое другое. Чтобы получить доступ к функциям, не вошедшим в список выше, нужно нажать по пункту «Другие функции». Здесь удобно то, что диапазон автоматически определяется. Мы выбрали функцию СУММ , потому что в нашем случае надо знать, сколько всего футболок было продано.

Автоматическая вставка формул

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

Функция сортировки

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

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

Причем все Excel может сделать вместо вас.

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

Все, теперь информация о том, кто совершил оплату, представлена наглядно.

Фильтрация

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

Выводы

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