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

Видео

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

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

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  
12.02.2013 22:02:11
Добавил видео для ленивых ;)
03.04.2019 16:30:19
Николай, подскажите пожалуйста, при копировании данных (значениями) из неумной таблицы в умную, данные вставляются не из нужного столбца, а из столбца совпадающего со столбцом, куда хочу вставить данные.
Например, если скопировать в обычной таблице столбец "С", а вставить скопированные данные в умную таблицу в столбец "D", то произойдет вставка данных из столбца "D" обычной таблицы, а не из "C", как планировалось.
Как этого избежать и как заставить Excel вставлять данные, те которые мне нужны, а не ему :).
Заранее спасибо
S B
25.11.2020 17:29:12
Блин, столкнулся с такой же проблемой, только у меня пустые ячейки вставляются, весь интернет облазил так и не нашел решение((
15.02.2013 16:55:46
Блин, пользовался только как для оформления. Не знал. Спасибо!!!:)
17.02.2013 08:55:06
Вы не одиноки, Роман. У меня на каждом тренинге такие люди находятся :)
19.02.2013 09:58:17
Николай, подскажите, пожалуйста, как закрепить столбец при ссылке Таблица1[НДС], чтобы при протягивании ссылка на столбец не менялась.  Знак $ не воспринимает, на F4, как обычная ссылка, не реагирует :)
20.02.2013 09:38:42
Алёна, что в вашем понимании "закрепить столбец"? Закрепить (знаками $) можно ссылку на конкретную ячейку. Если вам нужна ссылка на все ячейки столбца, то можно прямо так и написать в формуле - Таблица1[НДС]. Если вам нужна жесткая ссылка на конкретную ячейку в столбце НДС, то придется указывать ее в обычном виде - как $B$3, например.
20.02.2013 10:00:24
Использую при построении отчета формулы, которые  ссылается на данные столбца Таблица1[НДС]. Но при копировании формулы по строке данная ссылка съезжает на соседние столбцы и выглядит соответсвенно
Таблица1[Сосед], а надо чтобы осталась Таблица1[НДС].
20.02.2013 16:07:23
Смею предположить, что дело в разном способе выбора ячеек при заполнении формулы. Если начать в одной из ячеек писать формулу (=), а после этого стрелками на клавиатуре выбирать нужные ячейки из отформатированной "умной таблицы", в строке с формулой будет именно Таблица1[НДС]. А если вводить формулу при помощи выбора нужной ячейки мышкой, либо сразу с клавиатуры набирать, то получится B3, куда в последствии можно будет добавить и формулы.
20.02.2013 17:21:50
Иван,  спасибо! Либо ручками в каждом столце поправить формулу можно. Ссылка Таблица1[НДС] удобна тем, что она динамическая.
20.02.2013 20:01:04
Вводите в формулу ДВССЫЛ("Таблица1[НДС]") вместо Таблица1[НДС] - тогда при копировании по строке ссылка не будет сползать на соседние столбцы.
21.02.2013 06:44:43
Николай, вы гений! Работает!!!! Спасибо! :):)
15.03.2016 07:49:06
Добрый день Николай! А я нашел вариант проще для понимания для Фиксированной ссылки на столбец:
вместо ДВССЫЛ("Таблица1[НДС]")
пишем: [Таблица1[НДС]:[НДС]]

т.е. теперь при протягивании по месяцам вправо например на формуле Суммеслимн столбец с Условием "НДС" остается фиксированным;)
27.10.2017 17:35:56
Не [Таблица1[НДС]:[НДС]] , а Таблица1[[НДС]:[НДС]]
Если нужно ограничить область данных, то Таблица1[[#Данные];[Закупки]:[Продажи]]
03.12.2020 14:05:28
Не ориентируюсь совсем в русских названиях Excel, можно на английском? что такое ДВССЫЛ?
04.04.2023 22:01:29
04.04.2016 19:19:24
При протягивании формулы в таблице действительно происходит смещение. Попробуйте не тянуть формулу маркером автозаполнения, а копировать ячейку с формулой в соседний столбец или столбцы. Тогда имя указанного столбца будет оставаться неизменным.
21.02.2013 14:33:59
Полезный урок, спасибо!
21.02.2013 19:35:24
Век живи-век учись у Павлова! Буду вырабатывать правильные рефлексы работы в Excel, а протом сменю ник на Собака Павлова :D
28.02.2013 10:57:52
Подскажите как сослаться на конкретную ячейку в динамической (умной) таблице с другого листа книги? Вернее сослаться то получается вот так: Лист1!А1, но если к динамической таблице применить фильтр или сортировку, то на месте Лист1А1 уже другое значение, из другой строки! Возможно ли сделать чтобы формула Лист1А1 автоматически изменялась? Подозреваю, что нет((
03.03.2013 08:58:33
При фильтрации-то все ОК, а вот при сортировке - да, ссылка остается на старую ячейку, т.е. не корректируется после перестановки строк и дает неправильный результат. Выходов вижу два - либо использовать для вытаскивания из таблицы функцию ВПР, либо отказаться от использования "умных таблиц".
11.03.2013 05:12:14
Excel - он что, бесконечен? :) Спасибо за статью.
Не получается использовать "Промежуточный итог" с умной таблицей(кнопка неактивна)
11.03.2013 06:08:02
Не за что, Владимир! Да, к сожалению, без ложки дегтя не обойтись - в умных таблицах не работают промежуточные итоги (но по ней можно построить сводную и получить тот же результат).
16.06.2013 20:36:49
Приветствую.
Работает тут промежуточный итог...у меня очень удобненькл выведен наверх..автоматом изменяется...при фильтрации показывает только то, что зафильтровано.
Проблема с защитой листа...не работает автозаполнение, увы. Хочется закрыть именно столбцы с формулами, чтобы их корявыми ручками не редактировали, но тогда смысл вообще теряется.
Надеюсь, что здесь кто-то уже нашел решение и готов поделиться.
Я в свою очередь про промежуточный итог выдам "страшную" тайну. )
19.06.2013 11:37:18
Если вы имеете ввиду рукопашную вставку функции ПРОМЕЖУТОЧНЫЕ. ИТОГИ, то она работает, но я имел ввиду нечто более изящное и автоматическое :)
19.06.2013 21:45:28
ну да...я имела в виду автоматическую функцию "строка итогов"...,который я правда руками скопировала наверх таблицы...
вы меня заинтриговали! что значит "более изящное"?
а по защите таблицы нет информации? или мне теперь нечего выдавать? ;)
11.10.2013 10:32:13
Великолепный инструмент! Особенно в связке с заполнением бланков и сводными таблицами.
Не улавливаю проблему с промежуточными итогами.. Ведь при включенной строке Итогов в Конструкторе при фильтрации подводятся все промежуточные итоги, или речь о чём-то другом?
С благодарностью к Вам за все Ваши уроки, Дарья  
22.03.2013 12:14:39
Можно ли в ней работать несколькими пользователями в общем доступе??
22.03.2013 20:23:12
Нет, к сожалению. В общих файлах умные таблицы не работают.
27.03.2013 22:19:44
Спасибо за урок. Есть вопрос: как после включения форматирования "как таблицы", отключить данный режим. Просто использование данного режима оказалось не совсем удобным и хотелось бы вернуть прежние свойства своему массиву данных. Кроме как копировать все и вставить через Paste special-Values решения не вижу.
29.03.2013 00:33:55
Если выделить любую ячейку "умной таблицы", то на вкладке Конструктор - Преобразовать в диапазон (Design - Convert to Range).
31.03.2013 20:47:34
А можно ли, при использовании ВПР по умной таблице сослаться на название столбца, из которого будет возвращаться значение, вместо использования его номера?
31.03.2013 20:58:50
Получилось с помощью конструкции СТОЛБЕЦ(Таблица1[НДС]). Может быть можно проще?
11.04.2013 07:42:27
Да нет, куда уж проще - сам бы так и делал :)
Можно еще функцию ПОИСКПОЗ использовать для поиска в шапке нужного названия, но это не проще будет.
19.05.2017 11:19:53
Спасибо, как раз это решение искал. Планета Эксель как всегда спасает!
07.04.2013 15:53:54
Закрепить столбец таблицы в формуле без извращений можно так:
Таблица1[[Столбец1]: [Столбец1]]
При протягивании формулы влево-вправо он не будет смещаться.
11.04.2013 09:59:48
Из опыта работы с "умными таблицами" получил одно негативное наблюдение, что они перестают быть "умными", как только ставишь защиту листа, при этом оставив область таблицы (с учетом ее увеличения) не защищенной.
Let
20.04.2013 01:45:00
Николай, а почему до сих пор "Умные таблицы Excel 2007-2010", а не "Умные таблицы Excel 2007-2010-2013"? :)
21.04.2013 09:35:43
Да, сейчас поправлю, спасибо :)
10.05.2013 16:18:03
Коллеги, возможно ли для сводной таблицы использовать в формулах имя таблицы, а не ссылку на диапазон (по аналогии с "умными таблицами")?
Пробовал даже сводную преобразовать в умную таблицу, не помогает :)
Предполагаю, что я что-то делаю неправильно...
В частности интересует использование данной возможности в функции ВПР():
=ВПР(значение;имя_сводной_таблицы;столбец;ложь)
Если это возможно - подскажите, пожалуйста, правильный формат имени таблицы в формуле.
Заранее благодарен.
10.05.2013 19:06:54
Обошелся простым решением - сводной таблице присвоил имя диапазона (Формулы -> Диспетчер имен -> Создать...) и в формуле использовал имя диапазона, а не таблицы. Но это, так сказать, обходной путь.

Возможно есть другое решение?
04.04.2016 19:25:36
По умолчанию, если делать ссылку на любое значение сводной таблицы, Excel выводит функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. В ней же будут указаны критерии подобно функции СУММЕСЛИ или СУММЕСЛИМН. Можно заменить текстовые критерии ссылками на ячейки с критериями. Тогда еще круче чем ВПР получится, так как можно задать сразу несколько условий для получения результата.
28.05.2013 15:04:55
Есть таблица с некоторым набором столбцов и постоянно добавляющимися строчками - на Лист1.
Надо на Лист2 копию этой таблицы, без некоторых столбцов, при этом чтобы новые строчки тоже добавлялись.

Сделал во второй таблице в столбце формулу вида =ЕСЛИОШИБКА(Таблица1[Магазин];"")
Но в первой таблице когда добавляются новые строчки - во второй не добавляются. Можно ли сделать, чтобы добавлялись?
02.06.2013 08:05:29
Нет. Такое только макросом.
02.06.2013 09:38:49
Сделал копию через сводную в "табличной форме". Её тоже обновлять ручками (автоматически только макрос) - зато всё понятно, копия точная.
02.06.2013 11:47:33
Вариант, конечно. Но сводная (даже в табличной форме) - это не то же самое, что исходная таблица.
02.06.2013 12:49:22
Николай, а как бы вы копировали такую таблицу?
02.06.2013 13:50:04
Если на листе кроме нее ничего нет, то написал бы простой макрос копирования листа с последующим удалением ненужных столбцов в копии. Что-то типа:
Sub test()
   ActiveSheet.Copy
   ActiveSheet.Columns(2).Delete
   ActiveSheet.Columns(5).Delete
End Sub
02.06.2013 14:02:31
Спасибо, в самом деле автоматизировать, на кнопку повесить - да и всё
08.07.2013 02:50:21
Может так ?
=ЕСЛИОШИБКА(ДВССЫЛ("Таблица1[Магазин]");"")
и при добавлении новых строк на другом листе должно быть видно все строки, включая новые. Я подобное при проверке вводимых данных в ячейке "по списку" из столбца "умной" таблицы из другого листа использую: =ДВССЫЛ("Таблица3[аа]"), а иначе =Таблица3[аа] - выдаёт ошибку, хотя и видимость Таблица3 вся книга
08.06.2013 23:16:34
Добрый вечер. Скажите, а что делать, если шапка сложная (одно название на несколько столбцов, к примеру)?
Если ли решение?
спасибо
13.06.2013 09:41:42
Придумать название для каждого столбца - обязательно. Если у вас одно название на несколько столбцов, то как к ним адресоваться?
Многострочные шапки совсем нельзя. Можно попытаться имитировать или подставлять на время работы однострочную, а "для шефа" подставлять красивую многоэтажную.
21.06.2013 06:59:38
Здравствуйте. Никак не могу справиться:в таблицу вношу числовые данные, в крайнем столбце сумма по строке, но сумма должна вычисляться только тогда, когда все поля в строке заполнены, а если хоть одно пусто, то сумма 0. Спасибо
28.10.2013 09:23:10
Можно подсчитывать количество заполненных ячеек в строке и вычислять потом сумму, если заполнены все. Если, например, у вас пять столбцов в таблице, то в шестом столбце будет формула вида:
=ЕСЛИ(СЧЁТЗ(A2:E2)>0;СУММ(A2:E2);0)
06.11.2013 04:16:30
Здравствуйте. Данная формула работает некорректно, при наличии пустых строк в диапазоне, все равно происходит суммирование заполненых столбцов. Нашел какую-то функцию CountByMask, вроде работает, но уверен, что должно быть проще все. Подскажете? Спасибо
04.04.2016 19:29:50
Формула была дана ранее правильная, но нужно сравнивать не с нулем, а с точным нужным количеством. Например, если должно быть заполнено 5 ячеек прежде чем сумма посчитается, то формула будет =ЕСЛИ(СЧЁТЗ(A2:E2)=5;СУММ(A2:E2);""). В последнем аргументе ЕСЛИ стоит "", что будет приводить к пустой ячейке в результате, пока все 5 чисел не будут введены.
02.07.2013 21:21:53
Николай, здравствуйте. Как быть с защитой листа в умных таблицах?
Т.Е. как поставить защиту листа, при этом с отдельных столбцов снять "галку" защищаемая ячейка, и сохрать функционал "умной таблицы"?
26.07.2013 16:14:58
Добрый день. Возможно ли сделать умную таблицу горизонтальной. Чтобы поля со значениями располагались не в столбец а в строку, а заголовки были в левом столбце?
26.07.2013 17:03:15
Точно нет
23.09.2013 20:36:34
Уважаемый Николай почему не растягивается сводная таблица при защите определенных ячеек, точнее целых столбцов. Не имеется ввиду допустим от I1:I2351, а весь столбец. К примеру в видео Вы добавили столбец ПРИБЫЛЬ, вот именно и его надо защитить от не умелых рук. Так в принципе работать возможно, но не удобно растягивать таблицу самому. Что-то вроде перейти на машину с механической коробкой передач с автоматической) Заранее спасибо!
27.10.2013 20:46:25
Здравствуйте, Николай, подскажите пожалуйста решение в следующей ситуации
Имеется несколько умных таблиц, абсолютно идентичных по формату (число столбцов, заголовки), но с разным содержанием, каждая таблица на отдельном листе, наименование таблицы соответственно характеру содержания (типа Деревья, Звери, Рыбы и т.д.)
На первом листе умная таблица аналогичного формата (число столбцов, заголовки), но содержание формируется из остальных таблиц следующим образом:
1 колонка - все ячейки с выпадающими списками с полным перечислением названий  таблиц (типа Деревья, Звери, Рыбы и т.д.)
4 колонка - все ячейки с выпадающими списками с формулой =ДВССЫЛ([@1]&"[4]")
2 колонка с формулой =ИНДЕКС([@1];ПОИСКПОЗ([@4];[@1]&"["&"4"&"]";0);2)
3 колонка с формулой =ИНДЕКС([@1];ПОИСКПОЗ([@4];[@1]&"["&"4"&"]";0);3)
Ошибка возникает из-за невозможности прочитать имя диапазона(умной таблицы) из ячейки на которую дана ссылка типа [@1]
28.10.2013 09:20:17
Попробуйте использовать ДВССЫЛ для формирования ссылки во втором аргументе функции ПОИСКПОЗ.
28.10.2013 15:09:11
Николай, спасибо, работает!!!
Я как-то и забыл про ДВССЫЛ, а ведь решение было почти на поверхности:)
Добрый вечер, Николай. Спасибо. Все гораздо проще когда знаешь, совершенно с Вами согласен. Удачи Вам.
24.11.2013 11:20:25
Спасибо! :)
10.12.2013 00:24:05
Можно ли, формирование заголовков сделать через формулу?
19.12.2013 12:02:53
Николай, добрый день!

терзает меня вопрос - таблица создается как объект, и к нему можно обратиться в ВБА:
ActiveWorkbook.Worksheets("Лист1").ListObjects("Таблица1").

в самой таблице заголовки должны иметь уникальное имя, подозреваю они будут являться объектами внутри таблицы, так вот можно ли у ним обращаться к их имени, или только адресом внутри? :)
23.06.2014 13:00:21
ActiveWorkbook.Worksheets("Лист1").ListObjects("Таблица1").HeaderRowRange - диапазон строки заголовка (соответственно он имеет тип Range)
26.12.2013 14:38:55
Умные таблицы очень помогают....., но вот хотелось бы узнать, как можно получить связь ячеек с наиболее полным, не сколько информативность их (формулы, шрифт, формат), а возможна ли передача заливки, ссылок. Передачи заливки можно получить дополнительные формирования фильтров, формул.... . Т.е. упрощу вопрос: " Как можно создать связь ячейки (одного листа) чтобы она могла передавать изменения заливки! (и прочей информации ссылки, примечаний) на другой лист? И соответственно реагировать на изменения исходной.  Excel 2010 Или это вопрос не в той теме?
26.12.2013 16:12:26
Такое только макросом - однозначно. Причем, на практике надо будет еще смотреть: возможно "овчинка выделки" не будет стоить, т.к. при отслеживании достаточно больших диапазонов тормозить это все может неслабо.
26.12.2013 16:42:21
Прикрепил пример на странице
Отобразить заливку ячейки на другом листе

Может это поможет
26.12.2013 16:52:25
Таблица (минимилизирована количеством ячеек)  формируется в течении года, с сохранением всех данных, а корректировку нужно проводить чуть ли не ежедневно, так вот для выделения прошлых, существующих и перспективных данных, с различными вариациями, используем визуальную тонировку. По которой легко на отдельных листах показывать итоговые результаты и проводить манипуляции с цветом (сортировки, формулы...)
28.12.2013 11:02:03
Как уже писали коллеги на форуме, Excel не умеет отлавливать перекрашивание ячеек ни формулами ни событиями VBA. Единственный вариант: писать макрос для постоянного отслеживания цвета ячеек на любое перемещение выделения по листу (событие Selection_Change), но это жутко геморойно по ряду параметров. Очень не советую использовать цвет для серьезной работы с данными, любые дальнейшие действия с ними очень усложняются.
10.01.2014 07:57:55
Здравствуйте Николай! Очень понравилось то, что вы делаете. Дайте наводку на разрешение моей проблемы пожалуйста.
У меня есть две умные таблицы на одном листе, одна изменяющаяся другая нет. Располагаются так: изменяющаяся сверху на всю ширину листа (печатной области), а вторая маленькая ниже на две строки. Проблема возникает при добавлении новых строк в первую верхнюю таблицу, она растет вниз захватывая пустые строки и приближается ко второй таблице. Как сделать так чтобы это расстояние между таблицами оставалось неизменным.
Во второй таблице есть формулы которые ссылаются на первую таблицу. Обязательное условие: расположение обеих таблиц на одном листе  для дальнейшей распечатки.
11.01.2014 09:28:30
Я бы вставил несколько лишних пустых строк "про запас" между таблицами и скрыл их, чтобы не мешали.
14.01.2014 14:09:21
Евгений, топорный вариант - заполнить столбец между 1ой и 2ой таблицей значениями - можно просто в каждую ячейку пробел поставить, т.о если вы нажимаете Tab и умная таблица1 добавляет строку вниз она сдвигает вторую вниз. .... но я обычно добавляю путем вставки строки между (потом можно сортировать)
24.02.2020 16:18:27
поищите, интересный инструмент камера, для объединения нескольких диапазонов, или диаграмм  из разных мест, в одном месте для печати
06.02.2014 09:29:42
День добрый, Николай!
Подскажите, можно ли организовать выпадающий список который будет брать значения из определенного столбца умной таблицы?
06.02.2014 13:06:29
Спасибо =) Уже разобрался. Оказывается для каждого столбца умной таблицы нужно присвоить имя. Тогда и новые данные сами появляются в списке :)8)
11.02.2014 01:54:35
Можно и не присваивать, а использовать в поле Источник при создании выпадающего списка вот такую функцию:
=ДВССЫЛ("Таблица1[Имя_столбца]")
Страницы: 1  2  3  4  
Наверх