Главная » Статьи » Excel » Различные инструменты 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.Структурированные формулы.

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

"Умный" способ адрессации На что ссылается Формула возвращает Стандартный диапазон
=СУММ(Результаты) По умолчанию умная таблица, которая названа "Результаты" ссылается на область своих данных 87 B3:E7
=СУММ(Результаты[#Данные]) Тот же результат вернёт данная формула, где область данных указана в явном виде. 87 B3:E7
=СУММ(Результаты[Продажи]) Суммируем область данных столбца "Продажи". Если надо создать именованный диапазон, который будет ссылаться на столбец умной таблицы, то надо использовать синтаксис Результаты[Продажи]. 54 D3:D7
=Результаты[@Прибыль] Данную формулу мы вводили в строке 3. @ - означает текущую строку, а Прибыль - столбец, из которого возвращаются данные. 6 E3
=СУММ(Результаты[Продажи]:Результаты[Прибыль]) Ссылка на диапазон столбцов: от колонки "Продажи", до колонки "Прибыль" включительно. Обратите внимание на оператор ":", который создаёт диапазон. 87 D3:E7
=СУММ(Результаты[@]) Формулу вводили в троке 3. Она вернула всю строку таблицы. 11 B3:E3
=СЧЁТЗ(Результаты[#Заголовки]) Подсчёт количества элементов в #Заголовки. 4 B2:E2
=Результаты[[#Итоги];[Продажи]] Формула возвращает итоговую строку для столбца Продажи. Это не одно и тоже, что Результаты[Продажи], так как итоговая функция может быть разной, например, средней величиной. 54 D8

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



Вы всё ещё пользуетесь обычными таблицами?!?



Что ещё почитать на смежные темы:



Категория: Различные инструменты Excel | Добавил: dsb75 (06.06.2014) | Автор: Батьянов Денис E W
Просмотров: 36991 | Комментарии: 11 | Теги: smart tables, умные таблицы, Tables, structured references, структурированные ссылки | Рейтинг: 5.0/2
Всего комментариев: 11
11 r9166121453   (12.10.2016 21:24)
Спасибо за новый инструмент!

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

массив 2 с ценами: в столбцах годы, в строках - те же типы товаров. В ячейках - цена конкретного товара в конкретный год.

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

знаю, как это решить запросом в Access, но нужно наглядно в Excel.

Спасибо

9 dmstenko   (29.06.2016 00:02)
Добрый день!

Скажите, пожалуйста, реально ли использовать ссылки на умные таблицы для работы из из других книг в новых версиях excel? Простейший пример:
Код
=ВПР(C3;'C:\Users\SDA\Desktop\Книга1.xlsx'!Таблица1[[Статья расходов]:[Сумма]];2;0)

в версии 2010 возвращает "#ССЫЛКА!"
Ответ: коды надо обрамлять тэгами. исправил

0
10 irakitin2014   (29.06.2016 07:14)
Добрый день. Вам, наверное, лучше с этим вопросом на форум обратиться с приложением неработающего файла. Так проще посмотреть и найти ошибку

0
7 defaultNick   (09.10.2015 12:12)
Добрый день.
Я столкнулся со следующей проблемой:
Пользователь заполняет умную таблицу и в какой-то момент может вместо цифры вставить ссылку на соседнюю ячейку.

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

С уважением,

0
8 dsb75   (20.10.2015 00:20)
Здравствуйте, по крайней мере в Excel 2013 это действие надо подтвердить через всплывающую справа иконку.

0
1 test037   (15.06.2015 10:36)
Здравствуйте.
Подскажите пожалуйста. Создал "умную таблицу". Мне необходимо, чтобы в нескольких столбцах формулы были неструктурированными. Это возможно? Спасибо

0
2 dsb75   (15.06.2015 14:21)
Это возможно, но вы неправильно используете Excel. В рамках одной таблицы внутри столбца формула должна быть одинаковой для всех ячеек. На форуме покажите пример, я вам посоветую более конкретно.

0
3 aliador25   (21.06.2015 18:02)
Умные таблицы не решают проблему различающегося форматирования если текст не вводится с клавиатуры, а вставляется, например из интернета или других файлов cry При вставке используется формат исходного объекта.
Подскажите, пожалуйста, как сделать, чтобы формат ячейки в которую вставляется текст не менялся (в ручную выбирать для каждой ячейки слишком много)

0
4 dsb75   (21.06.2015 18:14)
На выбор:
1. Выделить всю таблицу - (Ctrl+A 2 раза нажать), установить стиль ячеек "Обычный". Правда при этом сбросятся форматы дата, время, числовые форматы.
2. Встали на УТ, идёте в ленту Конструктор, стиль таблицы, щёлкаете ПКМ, выбираете Применить и очистить форматирование
3. При вставке данных в УТ вставлять их как значения

0
5 dsb75   (21.06.2015 18:16)
Кстати 1-й способ можно усовершенствовать, если вы создадите стиль ячейки на основе Обычного (через команду Дублировать) и снимите там галку в разделе Число.

0
6 aliador25   (21.06.2015 19:37)
Спасибо огромное! первый способ после настройки стиля сработал на ура!

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика