Умные таблицы Excel - секреты эффективной работы и особенности

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=СУММ(D2:D8)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Фильтрация

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

Выводы

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

Программа курса:

1. Новые возможности Excel 2013

Мгновенный анализ данных. Разбивка данных в столбце на основании вводимого текста. Использование надстройки Inquire.

2. Таблица в Excel 2013

«Умные» Таблицы в Excel. Особенности использования Таблиц в формулах. Имена Таблиц и указатели столбцов.

3. Проведение более сложных вычислений

Команда Текст по столбцам — недостатки. Использование текстовых функций для дальнейшей разбивки данных по столбцам. Замена использования нескольких функций ЕСЛИ (IF) функцией ВПР (VLOOKUP). Накопительная сумма. Поиск и подстановка данных с помощью комбинирования функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH). Условные суммы и подсчет на основе нескольких критериев (новые функции Excel). Секреты функции СУММПРОИЗВ (SUMPRODUCT). Исключение ошибки в формуле.

4. Оформление документа

Визуальное представление данных с помощью условного форматирования. Быстрое и эффективное сравнение списков данных с помощью инфокривых (спарклайнов).

5. Организация информации в рабочей книге

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

6. Создание динамических списков со сводными таблицами и диаграммами

Общие сведения об отчетах сводной диаграммы и сводной таблицы; Создание отчета сводной таблицы на основе данных листа; Компоновка, форматирование и модификация сводных таблиц.; Работа со списком полей сводной таблицы; Поля промежуточных и общих итогов в отчете сводной таблицы; Отображение и скрытие общих итогов для всего отчета; Макеты отчета сводной таблицы; Группировка элементов в отчете сводной таблицы (ручная и автоматическая); Дополнительные вычисления в сводных таблицах для быстрого анализа (процентные соотношения, нарастающие итоги, «родительские» соотношения).; Создание, изменение и удаление формулы сводной таблицы (вычисляемые поля и вычисля объекты); Использование срезов для фильтрации данных сводной таблицы; Подключение срезов к нескольким сводным таблицам. Ссылки на ячейки сводной таблицы. Функция GetPivotDate; Пользовательские форматы в сводных таблицах; Особенности получения нескольких сводных таблиц на основе одного источника; Создание отчета сводной диаграммы на основе отчета сводной таблицы; Правила копирования результатов сводных таблиц; Особенности создания «несвязанных» сводных таблиц.

7. Новый механизм расчета сводных таблиц (Data Object Model)

Связи и сводная по нескольким таблицам. Функция подсчета количества уникальных элементов. Экспресс-просмотр

8. Power View: исследование, визуализация и представление данных

Запуск Power View в первый раз. Источники данных для Power View. Создание диаграмм и других визуализаций. Диаграммы и другие элементы представления в Power View. Таблицы в Power View. Преобразование таблицы в диаграмму или другой зрительный образ. Срезы в Power View.

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

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

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

В наличии имеется обычный диапазон данных о продажах.

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

Есть горячая клавиша Ctrl+T.

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

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

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

Структура и ссылки на Таблицу Excel

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

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

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

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

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

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

=Отчет[Продажи]

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

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

А теперь о том, как Таблицы облегчают жизнь и работу.

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

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

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

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

5. Новые столбцы также автоматически включатся в Таблицу.

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

Помимо указанных свойств есть возможность сделать дополнительные настройки.

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

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

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

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

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

Ограничения Таблиц Excel

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

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

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

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

Множество других секретов Excel вы найдете в онлайн курсе.

В последние годы появились новые "умные" технологии: умные телефоны, умные дома, холодильники, чехлы для телефонов, телевизоры и т. д. Не стали исключением и электронные таблицы. Умные таблицы в Excel были внедрены начиная с версии 2010 года.

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

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

Корпорация Microsoft, создавшая Excel, предложила называть умные таблицы таблицами, а то, что до этого называлось таблицами, - диапазоном. Однако в нашей стране эти идеи не прижились, поэтому таблицы остались таблицами, а новшество стали называть умными таблицами.

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

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

Предположим, что у нас уже введены некоторые данные в Excel. В этом случае выделяем их, переходим в меню ленты "Главное" и просматриваем группы команд, находим "Стили", в ней щелкаем на кнопке "Форматировать как таблицу".

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

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

После чего необходимо подтвердить выполненные действия путем нажатия на кнопку "ОК" или нажав Enter.

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

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

Выпадающие списки

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

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

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

В появившемся окне нажимаем на стрелочку рядом с "Типом данных" и выбираем "Список".

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

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

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

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

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

Представленный способ является одним из секретов эффективной работы с умными таблицами в Excel.

Изменяем стиль умной таблицы

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

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

После этого перемещаемся в таб "Конструктор", группа "Стили таблиц". Если не хватает представленных стилей, кликаем на "Дополнительные параметры" и видим всю палитру стилей.

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

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

Параметры стилей

Умные таблицы в Excel 2010 и старше, а также в Excel 2007 можно настраивать под себя в зависимости от предпочтений и необходимости.

Для этого переходим курсором на любую ячейку таблицы. Переходим на таб "Конструктор", группа "Параметры стилей таблиц", и там ставим/убираем галочки с интересующих нас опций: "Кнопка фильтра", "Чередующиеся столбцы или строки", "Строка заголовка или итогов", "Первый или последний столбец".

Использование формул

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

В случае необходимости создания нового столбца, в котором нужно ввести формулу, в ячейке данного столбца вводим формулу, которая несколько отличается от стандартной для данного типа электронной таблицы. Она имеет вид [@[{Название столбца}]]арифметические действия.

Здесь {Название столбца} - конкретное название столбца, в котором осуществляется расчет, например "Сумма, руб.", арифметические действия - это алгебраические выражения, принятые в формулах Excel и, в случае необходимости, числа, @ - показывает, что данные будут взяты из той же строки.

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

При нажатии на Enter произойдет пересчет в данном столбце во всех ячейках таблицы.

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

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

Автофильтрация

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

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

Задаем название таблицы

После того как умная таблица в Excel создана, приложение присваивает ей название в соответствии с порядком ее следования (если это первая таблица будет присвоено название "Таблица1" и т. д.).

Для того чтобы узнать название таблицы, ставим курсор в любую ее ячейку, переходим в таб "Конструктор", группа "Свойства" и там находим параметр "Имя таблицы", который можно редактировать, для чего нужно поместить на "Таблица1" курсор, выделить и ввести новое название, после чего нажать Enter.

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

Название таблицы может быть использовано при применении функций ВПР, ГПР. Например, мы хотим найти, кто у нас был 1229-м покупателем. Для этого в какую-то свободную ячейку, например K1, вводим 1229, в ячейку L1 вводим формулу =ВПР(K1;Таблица1 (или ее измененное название);1). Здесь 1 - номер столбца, в котором будет осуществляться поиск. В результате в ячейке L1 появятся Ф. И. О. покупателя под №1229 (если, конечно, он был в первом столбце).

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

Умные таблицы в Excel позволяют вместо диапазона ячеек с данными применять имена полей. Так, чтобы подсчитать сумму по столбцу C с 1-й по 21-ю ячейку, можно ввести формулу, общепринятую в Excel = СУММ(C1:C21). Предположим, что в столбце С у нас занесена информация по сумме покупки, совершенной каждым конкретным покупателем, и называется этот столбец "Сумма, руб.", а таблица была переименована в "Покупатели". Таким образом, в качестве формулы может быть введено следующее выражение: = СУММ(Покупатели[Сумма, руб.]).

Помимо этого, можно использовать специальную адресацию:

• «=Покупатели[#Эта строка]» будет ссылаться на текущую строку.
• «=Покупатели[#Данные]» будет ссылаться на данные, не учитывая строки заголовков.
• «=Покупатели[#Заголовки]» будет ссылаться на заголовки столбцов таблицы, расположенные в первой строке таблицы.
• «=Покупатели[#Итоги]» будет ссылаться на итоговую строку (в случае ее наличия в умной таблице).
• «=Покупатели[#Все]» будет ссылаться на всю таблицу, включая все вышеперечисленные формулы.

Так, например, подсчитать суммы покупок можно вводом формулы =СУММ(Покупатели[#Все]).

Дополнительные "фишки" умных таблиц

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

В Excel 2010 появилась возможность при работе с умными таблицами создавать срезы. Они представляют собой автофильтр отдельного столбца, представленный в виде элемента графики. Переходим в таб "Конструктор", группа команд "Инструменты", нажимаем "Вставить срез", в диалоговом окне выбираем названия столбцов (или одного столбца), из которых будет сформирован срез. При необходимости фильтрации таблицы выбираем необходимую категорию или категории, зажав кнопку Ctrl.

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

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

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

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

В заключение

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

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

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

Эта заметка является простой инструкцией, которая позволит вам повысить эффективность работы в Excel.

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

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

Как и многое в Excel создать таблицу можно несколькими способами.

Если уже есть некоторый неразрывный диапазон значений, то достаточно установить табличный курсор в любую из его ячеек (1, см.рис.ниже) и выбрать соответствующий инструмент (3) на вкладке Вставка (2). Также можно просто нажать сочетание клавиш Ctrl + T, чтобы открыть тоже самое окно (4).

Если в диапазоне есть строка с заголовками (5), то стоит проследить, чтобы соответствующая галочка была установлена (6). В этом случае Excel автоматически превратит первую строку диапазона в строку заголовков.

Если этого не сделать, то будет создана еще одна строка выше и именно ее Excel будет считать заголовком.