Умные таблицы Excel 2007-2013

Видео

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

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

table1.gif

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

Решение

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

table2.gif

 

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

table5.gif

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

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

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

    table4.gif

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

    table6.gif
  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 не было и этого.




Страницы: 1  2  3  
31.03.2016 16:31:57
Здравствуйте.

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

Хотелось бы динамически формировать  структурированное имя вида: тблРасходка[Коробка].
Собирался использовать функцию =ДВССЫЛ("тблРасходка"&"["&[@Обозначение]&"]";), где [@Обозначение] - столбец содержащий в ячейках имена столбцов таблицы тблРасходка, а как узнать имя самой таблицы не знаю, прописывать её имя в виде строки или константы нецелесообразно.
14.09.2016 11:11:19
Имя таблицы можно извлечь в окно Immediate простым макросом

Sub Name_Tabl ()
Debug.Print ActiveCell.ListObject.Name
End Sub
Но как сослаться на это имя в макросе?
21.04.2016 12:03:28
Добрый день!

У меня вопрос по поводу форматирования: когда я добавляю стиль в таблицу, автоматически нумеруются столбцы - Столбец1, Столбец2 и т.д., при удалении текста они все равно автоматически подставляются. Как это убрать?
20.10.2016 10:43:29
Добрый день. Когда вы выбираете форматирование таблицы, ставьте галочку на "таблица с заголовками"
08.05.2016 16:25:32
Здравствуйте! На сколько смог тщательно изучил тему, но не нашел ответа на вопрос, построил умную таблицу, заполнил ячейки разным функционалом, списки, формулы и прочее, списки в добавленных строках работают, а вот формулы нет( Подскажите, где допустил ошибку?
 
Без примера сложно что-либо сказать. Прикрепите файл
Здравствуйте, подскажите есть ли возможность строку итогов с низу перенести на верх?
Есть очень большая таблица и не удобно каждый раз листать ее вниз для просмотра итогов, было бы на много удобней при открытии книги строку итогов видеть с верху сразу под шапкой  
12.08.2017 19:38:41
Александр, можно добавить над таблицей пустую строку и там ввести формулу со ссылкой на ячейку с итогами умной таблицы.
=Таблица1[[#Итоги];[Имя поля]]
Эта формула всегда будет показывать то же значение, что и ячейка с итогами.
04.08.2016 18:28:19
Отличная статья
27.08.2016 10:36:07
Добрый день! Подскажите, как протянуть/скопировать формулу в строке итогов? Например, мне нужно в строке итогов сделать средневзвешенную величину. Я прописываю формулу в одной ячейке итогов и хочу скопировать в остальные, но она ссылки на столбцы не двигает, ссылаясь все время на одни и те же. Как "протянуть", чтобы не менять вручную номера столбцов? (при этом мне важно ссылаться именно на умные столбцы, а не просто на ячейки В2:В9 например, т.к. при применении фильтра нужен пересчет)
Здравствуй Николай! А как сделать, чтобы при изменении (продолжении) "умной" таблицы автоматически изменялась бы и сводная таблица.
20.10.2016 10:02:35
Добрый день. Пользуюсь умными таблицами давно и успешно и вдруг она перестала менять диапазон при добавлении строк ниже. Подскажите, пожалуйста, что cделать, чтобы восстановить эту функцию. Спасибо
28.10.2016 15:07:30
У меня та же проблема, что и у Марины. Подставляю новые значения вниз умной таблицы, а формулы в соседние столбцы автоматом подставляются через раз, а во второй таблице вообще не подставляются. Я в растерянности... Как это можно вылечить?
Доброго дня,Павел!
Спасибо за прекрасные уроки!
По данной теме. Можно ли создав свой стиль умной таблицы сохранить ее, чтобы её можно было применить и в других книгах ексель?
Заранее благодарю за ответ!
16.02.2017 09:37:01
Здравствуйте.
Есть вопрос по данной теме.
Я создала 2 умные таблицы. Название строк во второй таблице берется из первой. Мне нужно чтоб при добавлении строки в первой таблице вторая автоматически добавляла строку.  
16.03.2017 15:27:06
Здравствуйте!
Подскажите как можно обратиться к умной таблице через VBA в частности меня интересует можно ли с помощью VBA заменить название таблицы на название листа.

Заранее Благодарен за ответ
16.03.2017 17:25:00
Без проблем. Нужно что-то типа:
ActiveSheet.ListObjects("Таблица1").Name = ActiveSheet.Name
 
29.04.2017 08:42:50
Ребята добрый день. Помогите плз. Пример: Есть 3 таблицы: в первой - 90 человек, во второй - 100 человек. Что лучше использовать, макрос или формулу, которая покажет в 3-й таблице изменения, а именно новых - 10 человек
16.05.2017 14:24:38
Добрый день, Николай!
Есть ли функция, возвращающая порядковый номер поля в таблице? Аналогично column(). Для использования в качестве аргумента в vlookup.
например vlookup(C7;PayPlan;column(PayPlan[Coef]);false)
в этом примере все будет работать, если таблица PayPlan начинается с колонки А. А если правее? Тогда номер столбца Coef в таблице PayPlan не будет совпадать с номером колонки.
Спасибо.
01.09.2017 16:30:43
=ПОИСКПОЗ("Coef"; PayPlan[#headers];0)
23.05.2017 22:28:45
Добрый день.
Таблицы это шикарное открытие.
Вопрос - можно ли ссылаться на таблицу в другой книге, если она закрыта?

P.S. ответ нашел сам в справке. Ссылаться можно, но должен быть открыт источник, иначе возвращается #ССЫЛКА!. Есть способ получить эти данные через "подключение", но тогда совсем теряются заголовки.

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

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

Как быть? Стоит 2007 офис.
01.09.2017 16:26:59
В умных таблицах нельзя вставлять промежуточные итоги.
А группировку можно сделать и вручную - выделить строки и Данные - Группировать.
18.12.2017 22:51:22
Здравствуйте, Николай!
У меня с третьей строки таблицы формулы перестают работать. Нужно поставить курсор в строку формулы и нажать Enter, только в этом случае формула в действие. У меня таблица заполняется через форму vba и получается, что надо тратить время на каждую строку. У меня обычные формулы = ЕСЛИ.
25.04.2018 00:34:14
У вас скорее всего включен ручной пересчет формул, замените его на автоматический.
Возможно ограничения пересчета формул устанавливаются в коде для его быстродействия, но потом отсутсвует строка, которая возвращает пересчет на место.
12.04.2018 19:48:04
Здравствуйте.
Подскажите пожалуйста, как зафиксировать значение выбранное по горизонтали и вертикали (действие умножения) в "Умной таблице"?
Спасибо.
03.07.2018 10:56:34
:like:
Thanks!
Добрый день! Возможно поможете решить задачку:

На листе 1 столбцы:
Документ | Номер | Дата
1                  1            01.01.01
2                  2            02.01.01
Строк с данными может быть от 1 до 15

На листе 2 нужно вывести по порядку друг за другом значение строк:
Применены: 1 : 1 : 01.01.01, 2 : 2 : 02.01.01 и т.д.

Количество строк меняется и соответственно хотелось бы, что бы на листе 2 который используется для печати документа добавлялись/убавлялись значения
Возможно ли применение умных таблиц в таком ключе?  
14.09.2018 13:05:55
Подскажите пожалуйста! Умная таблица отфильтрована по одному столбцу, во втором пытаюсь сделать протяжку вниз 1-2-3-4 т.е. увеличивать на 1цу. Протяжка просто копирует 1цу. Кто сталкивался?
По-моему проще поставить туда автоматический счетчик типа =Строка()+Строка(Таблица1[#Заголовки]) ну или что-нибудь в этом духе
24.01.2019 06:47:31
Здравствуйте!
Есть задача в которой имеется два Листа. На первом Листе имеется умная Таблица в которую автоматически подставляются данные с второго Листа. Во второй Лист данные попадают из автоматического экспорта данных из текстового файла при открытии документа. Суть задачи в том чтобы размерность по количеству строк в первой таблице подстраивался под размерность данных экспортируемых на второй лист.
Подскажите пожалуйста как добиться данного функционала?
Даже приблизительно не имею представления как подобраться к данной задаче. Размерность на втором листе может варьироваться от 10 строк, и до 1000+ и вручную править размер умной таблицы крайне неудобно. Есть ли возможность работать с умной Таблицей посредством макросов?
15.03.2019 11:40:45
Николай, здравствуйте!
подскажите! в умной таблице есть ячейка с выпадающем списком. при добавлении строки с новыми значениями приходится постоянно копировать ячейку с выпадающим списком. как сделать, что бы при добавлении строки эта ячейка была уже с выпадающим списком.
24.09.2019 10:57:32
Николай, добрый день! надеюсь, что я по адресу вопрос пишу)
я работаю я программой bCAD и создаю отчет о всех деталях проекта мебели в экселе путем экспорта XML. Открываю новый документ, Разработчик, Источник, и выбираю нужный источник, поставляемый программой bCAD, с расширением .xsd. Далее из дерева источника накидываю заголовки колонок своей будущей умной таблицы, т.е. создаю шапку таблицы куда и пойдут все детали мебели. Так вот в данной таблице есть несколько колонок с названием Материал, которая в последствии будет содержать название материала детали (например, Белая). Все хорошо, но проблема в том, что в денной колонке мне необходимо не только просто материала (Белая), но и его путь к по папкам к этому материалу (например, ЛДСП/Egger/толщ 16мм/Белая). Путь по папкам уже идет откуда-то из каталога программы.
Можете ли Вы помочь мне в этом? очень надеюсь, потому что техподдержка bCAD утверждает, что в этом мне могут помочь только специалисты по Excel.  
05.10.2019 13:07:09
.Добрый день, Николай. Вопрос такой: можно ли задавать количество строк умной таблицы через значение в ячейке?  Поясню на примере. Пользователь вводит в ячейку количество месяцев, необходимых для проекта, ( обычно от 2 до 10), умная таблица, в которой уже есть первая строка с названием текущего месяца (формат дата) цепляет введённое число и добавляет необходимое количество строк с названиями следующих по порядку месяцев. Конечной целю является создание выпадающих списков с нужным количеством месяцев.  Спасибо.
Страницы: 1  2  3  
Наверх