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

66021 25.11.2012 Скачать пример

Видео

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

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

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  4  
Надежда Степанова
06.11.2015 10:42:54
подскажите пожалуйста что произошло: копирую и хочу вставить в следующую строку данные, а они не вставляются - пустое значение. Так неудобно, ранее вставлялось
Ссылка
Марина
09.11.2015 13:33:21
Добрый день, Николай! Подскажите, пожалуйста, что не так делаю:
В умной таблице живет себе формула, автоматически добавляется в новые строки, и пока все хорошо. Потом формулу редактируем в произвольной ячейке столбца. И тут есть два варианта - (1) столбец автоматически сам заполняется новой формулой и дальше все хорошо; (2) новая формула остается только в текущей ячейке и дальше все странно. После ручного "растягивания" формулы на весь (!) столбец таблицы все нормально только до добавления новых строк - а в новой строке внезапно появляется старая (до редактирования) формула!
Вопрос №1 - отчего новая формула не размножается по столбцу?
Вопрос №2 - где живет старая формула и как от нее избавиться?
Ссылка
Alina
14.12.2015 14:28:33
Николай, добрый день!
Подскажите пожалуйста, куда посмотреть и обучиться... :-)
Имеется книга с большим количеством листов. На каждом листе таблица с итоговой ячейкой (сумма).
Вопрос: Как сделать ссылки на эти итоговые ячейки на один отчетный лист?
Заранее спасибо.
Ссылка
Dmitriy Kim
28.02.2016 15:19:24
Подскажите пожалуйста в умных таблицах ячейке с выпадающим списком автоматически не копируются? Выпадающий список реализован стандартным способом.
Ссылка
Dmitriy Kim
28.02.2016 15:21:57
Подскажите пожалуйста, в умных таблицах ячейки с выпадающим спискам автоматически не копируются при добавлении новой строки? Выпадающий список реализован стандартным способом.
Ссылка
Вадим Кузьмин
31.03.2016 16:31:57
Здравствуйте.

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

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

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

У меня вопрос по поводу форматирования: когда я добавляю стиль в таблицу, автоматически нумеруются столбцы - Столбец1, Столбец2 и т.д., при удалении текста они все равно автоматически подставляются. Как это убрать?
Ссылка
Марина Сагиян
20.10.2016 10:43:29
Добрый день. Когда вы выбираете форматирование таблицы, ставьте галочку на "таблица с заголовками"
Родитель Ссылка
Тимур Кузьменко
08.05.2016 16:25:32
Здравствуйте! На сколько смог тщательно изучил тему, но не нашел ответа на вопрос, построил умную таблицу, заполнил ячейки разным функционалом, списки, формулы и прочее, списки в добавленных строках работают, а вот формулы нет( Подскажите, где допустил ошибку?
 
Ссылка
Александр Боронников
01.03.2019 14:00:19
Без примера сложно что-либо сказать. Прикрепите файл
Родитель Ссылка
Александр Скрипник
28.07.2016 11:43:54
Здравствуйте, подскажите есть ли возможность строку итогов с низу перенести на верх?
Есть очень большая таблица и не удобно каждый раз листать ее вниз для просмотра итогов, было бы на много удобней при открытии книги строку итогов видеть с верху сразу под шапкой  
Ссылка
Виктор Косенков
12.08.2017 19:38:41
Александр, можно добавить над таблицей пустую строку и там ввести формулу со ссылкой на ячейку с итогами умной таблицы.
=Таблица1[[#Итоги];[Имя поля]]
Эта формула всегда будет показывать то же значение, что и ячейка с итогами.
Родитель Ссылка
Екатерина
04.08.2016 18:28:19
Отличная статья
Ссылка
vlja
27.08.2016 10:36:07
Добрый день! Подскажите, как протянуть/скопировать формулу в строке итогов? Например, мне нужно в строке итогов сделать средневзвешенную величину. Я прописываю формулу в одной ячейке итогов и хочу скопировать в остальные, но она ссылки на столбцы не двигает, ссылаясь все время на одни и те же. Как "протянуть", чтобы не менять вручную номера столбцов? (при этом мне важно ссылаться именно на умные столбцы, а не просто на ячейки В2:В9 например, т.к. при применении фильтра нужен пересчет)
Ссылка
Тынайбек Орозахунов
31.08.2016 16:41:38
Здравствуй Николай! А как сделать, чтобы при изменении (продолжении) "умной" таблицы автоматически изменялась бы и сводная таблица.
Ссылка
Марина Сагиян
20.10.2016 10:02:35
Добрый день. Пользуюсь умными таблицами давно и успешно и вдруг она перестала менять диапазон при добавлении строк ниже. Подскажите, пожалуйста, что cделать, чтобы восстановить эту функцию. Спасибо
Ссылка
Светлана
28.10.2016 15:07:30
У меня та же проблема, что и у Марины. Подставляю новые значения вниз умной таблицы, а формулы в соседние столбцы автоматом подставляются через раз, а во второй таблице вообще не подставляются. Я в растерянности... Как это можно вылечить?
Ссылка
Татьяна Гугенотова
11.01.2017 12:12:31
Доброго дня,Павел!
Спасибо за прекрасные уроки!
По данной теме. Можно ли создав свой стиль умной таблицы сохранить ее, чтобы её можно было применить и в других книгах ексель?
Заранее благодарю за ответ!
Ссылка
Елена Я
16.02.2017 09:37:01
Здравствуйте.
Есть вопрос по данной теме.
Я создала 2 умные таблицы. Название строк во второй таблице берется из первой. Мне нужно чтоб при добавлении строки в первой таблице вторая автоматически добавляла строку.  
Ссылка
Denis Cornei
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. ответ нашел сам в справке. Ссылаться можно, но должен быть открыт источник, иначе возвращается #ССЫЛКА!. Есть способ получить эти данные через "подключение", но тогда совсем теряются заголовки.

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

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

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

На листе 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 который используется для печати документа добавлялись/убавлялись значения
Возможно ли применение умных таблиц в таком ключе?  
Ссылка
Страницы: 1  2  3  4  
Наверх