Умные таблицы 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  
09.04.2014 12:26:13
Добрый день.
подскажите как вытащить имя таблицы?
09.04.2014 13:10:58
Куда вытащить?
09.04.2014 19:22:49
Как представить имя таблицы, как строку? Т.е. При генерации отчета, должно в текст отчета вставляться названия таблиц, всех, которые есть на листе
09.04.2014 20:41:29
Ничего не понял. Какого отчета? Какая генерация?
На вкладке Конструктор есть имя таблицы в которой стоит активная ячейка.
На вкладке Формулы - Диспетчер имен - можно увидеть имена всех таблиц, которые есть в файле.
Подскажите, пожалуйста. Пользуюсь умными таблицами. Можно ли, например, в конце дня отследить, какие изменения я внесла на протяжении дня или недели в эту таблицу. Я так понимаю, такое можно посмотреть в общем доступе, но тогда утратятся таблицы? или нет? не пробовала делать, т.к. боюсь потерять данные таблицы.
03.08.2014 11:08:00
Да, в общем доступе таблицы не работают, к сожалению. Надо писать макрос отслеживания, который будет регистрировать все изменения таблицы на отдельном скрытом листе. Или делать регулярное резервное копирование и сравнивать старые и новые файлы.
30.07.2014 09:35:54
Ну кстати на удаление данных умная таблица автоматически НЕ подстраивается (вы написали, что она это делает), наверное, стоит поправить.
03.08.2014 11:04:36
Я имел ввиду удаление строк-столбцов, а не очистку ячеек, само собой :)
04.08.2014 10:30:51
ключевое слово "автоматически"
01.08.2014 11:46:31
Добрый день.
При использовании умными таблицами возникает необходимость составлять формулы по стандартным форме, например =СУММ(F2:F200) вместо =СУММ(Таблица1[НДС] ).
Подскажите, пожалуйста, как ускорить процесс ввода таких формул, не прибегая к ручному прописыванию (F2:F200), например.
03.08.2014 11:11:39
Легко. Заходим Файл - Параметры - Формулы и снимаем галочку Использовать имена таблиц в формулах.
05.08.2014 18:16:51
Спасибо, Николай!
15.09.2014 19:21:42
Здравствуйте, Николай. Случайно удалил "авторазвертывание" формул в "умной" таблице и нигде не могу найти, где включить обратно. Подскажите,пожалуйста. Туда же вопрос: в нескольких столбцах есть формулы с "авторазвертыванием", как удалить это "авторазвертывание" только из одного столбца?
18.09.2014 18:22:43
Сам же и нашел: "Авторазвертывание" -файл - параметры - правописание - параметры автозамены. Удаление авторазвертывание из одного столбца - только костыльно, удалить столбец а потом добавить обратно.
10.10.2014 13:08:32
Вопрос 1 - работает ли макрос с галочками с этой табличкой?
Вопрос 2 - а можно в ней получать две итоговые суммы? Первая по отсортированной дате; Вторая - общая по всей таблице.
30.12.2014 15:22:35
1. да, почему нет?
2. для итогов после фильтрации нужна функция вида ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Таблица1[имя_столбца]), а для общих итогов простая СУММ(Таблица1[имя_столбца])
29.12.2014 11:53:30
Добрый день.
Возможно ли преобразовать "Умную таблицу" Обратно простую. Столкнулась  с проблемой. При копирование данных из разных таблиц в одну. Таблица искажает данные и меняет ссылки. А когда я раньше работала с таблицей не преобразовав ее все вставлялось корректно.
30.12.2014 15:19:25
Вкладка Конструктор - Преобразовать в диапазон (Design - Convert to Range)
05.01.2015 18:10:26
Николай, скажите, возможна ли работа с "умной таблицей" при наложении защиты на лист? Если да, то каким образом это можно сделать?
Имеется в виду, оттягивание её.
14.01.2015 17:16:03
Добрый день.
Я правильно понимаю, что при использовании умных таблиц диаграмма построенная на базе такой таблицы будем сама менять диапазон в соответствии с изменением строк/столбцов в умной таблице?
UPD: При добавлении строк таблица расширяется, диаграмма достаивается. А вот при добавлении столбцов только таблица меняется.
15.01.2015 11:30:31
Николай, добрый день! У меня тоже вопрос по защите умных таблиц.  В таблице есть и данные для пользователей и мои формулы. При защите ячеек с формулами и включении защиты листа пользователи не видят вводимые данные в строке формул. Можно как-то обойти эту проблему? Спасибо!
16.01.2015 17:46:17
Здравствуйте, Николай.
Подскажите, возможно, ли вставить в определенном месте (в середине) диапазона / умной таблицы  какое-то количество новых строк (формат и количество столбцов одинаковое) таким образом, чтобы они не заменили информацию в уже существующих строках?
21.01.2015 16:43:26
Николай, добрый день!

Подскажите, можно ли в качестве диапазона для умной таблицы использовать динамический диапазон на функции СМЕЩ (OFFSET) или именованный диапазон с соответствующей функцией.

Идея была "приставить" умную таблицу к сводной таблице с изменяющимся количеством строк, для дополнительных вычислений. Если делать просто с помощью формул, то их приходится протягивать сильно вниз с запасом. Использование умной таблицы на основе динамического диапазона помогло бы решить задачу красиво, уменьшить размер файла, сократить время пересчёта...
30.03.2015 20:18:44
А почему нет?
31.03.2015 15:21:44
Не получается: создаю в диспетчере имён новый диапазон, который формирую с помощью функции СМЕЩ.
Но выбрать имя этого диапазона как источник данных для умной таблицы не получается :(
27.04.2015 19:20:50
:?: Вопрос по-прежнему актуален: можно ли размер умной таблицы задавать динамическим диапазоном?
Методом "Тыка" не получается, в интернете инструкций тоже не нашёл...

Если такая возможность есть - научите, буду признателен!
07.09.2015 09:11:49
По-прежнему не нашёл способа задавать размер "умной таблицы" динамически с помощью формулы СМЕЩ (OFFSET).
Если таковой имеется, подскажите пожалуйста!
25.03.2015 16:09:39
Добрый день:)

У меня есть уже отредактированный прайс цен, там ловольно много объединенных ячеек, но когда я применяю "Форматирование Таблицы", то все объединенные ячейки становятся обычными и заново их объединить не получается. Скажите, можно ли применив "Форматирование Таблицы" сохранить исходный вид прайса?  
Для примера прикрепляю два изображение как было и как стало:
https://pp.vk.me/c624329/v624329045/263ab/p4pH754iGWI.jpg
https://pp.vk.me/c624329/v624329045/263b4/s-BeZ4jb6cQ.jpg

Надеюсь на вашу помощь,
Спасибо:)
30.03.2015 20:18:16
Наталия, умные таблицы не могут содержать объединенные ячейки в принципе :(
03.04.2015 15:33:36
Николай, добрый день!
Скажите, как правильно обратиться к умной таблице в случае,
когда ее выбираем в качестве источника для выпадающего списка
в проверке данных?

Сейчас я просто ссылаюсь так: =Справочники!$B$2:$B$5

При этом я не учитываю, что этот же диапазон определяет умную таблицу Таблица1.
Как обратиться к Таблица1? =Справочники!Таблица1[Столбец1] ?
Выдает ошибку.

Спасибо.
27.04.2015 19:12:47
22.04.2015 09:01:40
Добрый день!

А подскажите пожалуйста, как заставить умную таблицу "забыть" формулу, которую я ввел в вычисляемом столбце.
Допустим, в каком-либо столбце была формула "=1" и она послушно разворачивается при добавлении строк в таблицу. Теперь я решил, что в данном поле должны быть просто данные. Простым удалением формулы из ячеек этого поля ничего не добьешься - при добавлении новых строк Excel упорно будет восстанавливать формулу. Как заставить его ее забыть без удаления поля и создания его заново?
22.04.2015 20:32:45
Здравствуйте Николай!

Немного эмоций :
все Ваши материалы КЛАД! и государству ничего не надо). Огромное спасибо за Вашу деятельность. С сайтом знакома давно, но был перерыв в практике использования Excel, и сейчас кое-что вспоминаю, а что-то и вновь осваиваю с  помощью  Ваших материалов уже на 2013. Более полезного и структурированного ресурса для себя не нахожу.

Вопрос связан с добавлением столбцов в "умную таблицу" - при вставке столбца и условиях, что справа и слева расположены столбцы со списками,
получаем новый столбец, соответственно со списком....Как избавиться от последнего (красиво и правильно избавиться)?  
На обычном листе, получается выделить столбец и изменить значение Список на Любое значение через Проверку данных при выделенном столбце, а вот в "умной таблице" при выделенном (добавленном вставкой) столбце - Проверка просто неактивна....(  Проверка активна, если выбрана ячейка, но тут либо к одной ячейке применить (что может быть оч затруднительно и трудоемко), либо ко всему диапазону с условием - тогда изменения постигнут и еще один столбец. а в нем - Список необходим.
Возможно есть какие хитрости при вставке, или выделении....Поделитесь пожалуйста -  Как получить внутри "умной таблицы"  "чистый" столбец? или по аналогии предыдущего вопроса - как заставить вставленный столбец забыть список?
27.04.2015 19:18:04
Возможно нужно выделить все значения того или иного столбца умной таблицы без самого заголовка?
У меня вроде бы работает так валидация...
Выделяются все значения столбца точно так же, как и весь столбец, когда появляется чёрная стрелка вниз, только ловить стрелку нужно в верхней части заголовка соответствующего столбца.
27.04.2015 21:34:51
Спасибо Евгений! Ваше предложение рабочее

((( до чего ж я темнота )))
...самое очевидное  - забыть про шапку ....  даже итоги отменяла...эээ :oops:
24.04.2015 11:01:09
Здравствуйте. Не могу справиться с умной таблицей. Есть список оборудования надо сделать автоматический расчет наработки часов. В умной таблице есть столбцы: "дата ввода в эксплуатацию" "наработка часов" есть ячейки за пределами таблицы "сегодняшняя дата" и "количество часов работы в сутки (константа)". Без умной таблицы рассчитывал так "текущая дата" - "дата ввода в эксплуатацию" * "количество часов работы в сутки" = "наработка часов".
А вот как сделать это с ячейками умной таблицы не знаю. при наборе формуле не получается выбрать ячейку из столбца умной таблицы.

Разобрался.  
21.05.2015 05:15:19
Здравствуйте, Николай!
Подскажите, пожалуйста, разумное решение ситуации.
В книге несколько листов с одинаковыми "умными" таблицами. Вернее "скелет" таблиц одинаковый, данные на каждом листе разные, и разное количество строк. Необходимо на итоговом листе (с тем же "скелетом") разместить итоговые строки по каждому листу и вычислить среднее по каждому столбцу. Есть ли какой-то способ перенести итоги, не тыкая в каждый аргумент на каждом листе? Или, может быть, это как-то быстро умеет делать PLEX?
23.05.2015 19:23:27
Здравствуйте

помогите пожалуйста..  есть столбец с номенклатурами 8 символов есть позиции которые начинаются с 1,2,3,4-ех "0" (например 00001111) мне нужно добавить в этот столбец несколько строк, как присоединить эти строки к имеющейся таблице чтобы они участвовали в дальнейших действиях. проблема в том что добавленные в ручную позиции не сортируются с остальной таблицей и не попадают в дальнейшие действия (например впр выдает по данным номенклатурам Н/Д хотя позиции в списке есть)
08.07.2015 09:39:29
Николай, спасибо за уроки. Скажите, почему при добавлении новой строки в умной таблице в одну из ячеек формула не добавляется автоматически (в другие ячейки формулы подставляются)? Вчера всё работало прекрасно, сегодня ячейка добавляется без формулы. Над пустой ячейкой формула есть. Помогите пожалуйста.
31.08.2015 15:04:34
Здравствуйте, Николай, у меня записанно макро, но оно не работает в динамическом таблице. Можите мне помогать.

Sub Sort_sharj()

   ActiveSheet.Unprotect Password:="aa"
   Range("a7:h100006").Sort Key1:=Range("b7"), Order1:=xlAscending, Key2:=Range("a7") _
       , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
       DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers
       
       ActiveSheet.Protect Password:="aa", DrawingObjects:=True, Contents:=True, Scenarios:=True _
       , AllowFiltering:=True
       Range("a7").Select
End Sub
03.10.2015 12:59:23
Здравствуйте Николай!
По умным таблицам проясните, пожалуйста.
Есть умная таблица и в одном из столбцов данные вводятся в формате "Проверка данных".
При добавлении новой строки все получается прекрасно формулы копируются, таблица автоматически расширяется, но атрибут "Проверка данных" в нужном столбце не появляется. И приходиться при каждом добавлении новой строки вручную копировать предыдущую ячейку столбца, что бы появилась "Проверка данных". Возможно ли сделать чтобы атрибут "Проверка данных" тоже копировался при добавлении строк в умной таблице.
Спасибо, Владимир
06.11.2015 10:42:54
подскажите пожалуйста что произошло: копирую и хочу вставить в следующую строку данные, а они не вставляются - пустое значение. Так неудобно, ранее вставлялось
09.11.2015 13:33:21
Добрый день, Николай! Подскажите, пожалуйста, что не так делаю:
В умной таблице живет себе формула, автоматически добавляется в новые строки, и пока все хорошо. Потом формулу редактируем в произвольной ячейке столбца. И тут есть два варианта - (1) столбец автоматически сам заполняется новой формулой и дальше все хорошо; (2) новая формула остается только в текущей ячейке и дальше все странно. После ручного "растягивания" формулы на весь (!) столбец таблицы все нормально только до добавления новых строк - а в новой строке внезапно появляется старая (до редактирования) формула!
Вопрос №1 - отчего новая формула не размножается по столбцу?
Вопрос №2 - где живет старая формула и как от нее избавиться?
14.12.2015 14:28:33
Николай, добрый день!
Подскажите пожалуйста, куда посмотреть и обучиться... :-)
Имеется книга с большим количеством листов. На каждом листе таблица с итоговой ячейкой (сумма).
Вопрос: Как сделать ссылки на эти итоговые ячейки на один отчетный лист?
Заранее спасибо.
Страницы: 1  2  3  
Наверх