Умные таблицы в Excel
Видео
Постановка задачи
Имеем таблицу, с которой постоянно приходится работать (сортировать, фильтровать, считать по ней что-то) и содержимое которой периодически изменяется (добавляется, удаляется, редактируется). Ну, хотя бы, для примера - вот такого вида:
Размер - от нескольких десятков до нескольких сотен тысяч строк - не важен. Задача - всячески упростить и облегчить себе жизнь, превратив эти ячейки в "умную" таблицу.
Решение
Выделяем любую ячейку в таблице и на вкладке Главная (Home) разворачиваем список Форматировать как таблицу (Format as table):
В раскрывшемся списке стилей выбираем любой вариант заливки на наш вкус и цвет и в окне подтверждения выделенного диапазона жмем ОК и получаем на выходе примерно следующее:
В результате после такого преобразования диапазона в "умную" Таблицу (с большой буквы!) имеем следующие радости (кроме приятного дизайна):
- Созданная Таблица получает имя Таблица1,2,3 и т.д. которое, можно изменить на более адекватное на вкладке Конструктор (Design). Это имя можно использовать в любых формулах, выпадающих списках и функциях, например в качестве источника данных для сводной таблицы или массива поиска для функции ВПР (VLOOKUP).
- Созданная один раз Таблица автоматически подстраивается в размерах при добавлении или удалении в нее данных. Если дописать к такой Таблице новые строки - она растянется ниже, если добавить новые столбцы - разойдется вширь. В правом нижнем углу Таблицы можно увидеть автоматически перемещающийся маркер границы и, при необходимости, скорректировать его положение мышью:
- В шапке Таблицы автоматически включается Автофильтр (можно принудительно отключить на вкладке Данные (Data)).
- При добавлении новых строк в них автоматически копируются все формулы.
- При создании нового столбца с формулой - она будет автоматически скопирована на весь столбец - не надо тянуть формулу черным крестом автозаполнения.
- При прокрутке Таблицы вниз заголовки столбцов (A, B, C…) меняются на названия полей, т.е. уже можно не закреплять шапку диапазона как раньше (в Excel 2010 там еще и автофильтр):
- Включив флажок Показать итоговую строку (Total row) на вкладке Конструктор (Design) мы получаем автоматическую строку итогов в конце Таблицы с возможностью выбора функции (сумма, среднее, количество и т.д.) по каждому столбцу:
- К данным в Таблице можно адресоваться, используя имена отдельных ее элементов. Например, для суммирования всех чисел в столбце НДС можно воспользоваться формулой =СУММ(Таблица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 не было и этого.
Например, если скопировать в обычной таблице столбец "С", а вставить скопированные данные в умную таблицу в столбец "D", то произойдет вставка данных из столбца "D" обычной таблицы, а не из "C", как планировалось.
Как этого избежать и как заставить Excel вставлять данные, те которые мне нужны, а не ему
Заранее спасибо
Таблица1[Сосед], а надо чтобы осталась Таблица1[НДС].
вместо ДВССЫЛ("Таблица1[НДС]")
пишем: [Таблица1[НДС]:[НДС]]
т.е. теперь при протягивании по месяцам вправо например на формуле Суммеслимн столбец с Условием "НДС" остается фиксированным;)
Если нужно ограничить область данных, то Таблица1[[#Данные];[Закупки]:[Продажи]]
Не получается использовать "Промежуточный итог" с умной таблицей(кнопка неактивна)
Работает тут промежуточный итог...у меня очень удобненькл выведен наверх..автоматом изменяется...при фильтрации показывает только то, что зафильтровано.
Проблема с защитой листа...не работает автозаполнение, увы. Хочется закрыть именно столбцы с формулами, чтобы их корявыми ручками не редактировали, но тогда смысл вообще теряется.
Надеюсь, что здесь кто-то уже нашел решение и готов поделиться.
Я в свою очередь про промежуточный итог выдам "страшную" тайну. )
вы меня заинтриговали! что значит "более изящное"?
а по защите таблицы нет информации? или мне теперь нечего выдавать?
Не улавливаю проблему с промежуточными итогами.. Ведь при включенной строке Итогов в Конструкторе при фильтрации подводятся все промежуточные итоги, или речь о чём-то другом?
С благодарностью к Вам за все Ваши уроки, Дарья
Можно еще функцию ПОИСКПОЗ использовать для поиска в шапке нужного названия, но это не проще будет.
Таблица1[[Столбец1]: [Столбец1]]
При протягивании формулы влево-вправо он не будет смещаться.
Пробовал даже сводную преобразовать в умную таблицу, не помогает
Предполагаю, что я что-то делаю неправильно...
В частности интересует использование данной возможности в функции ВПР():
Заранее благодарен.
Возможно есть другое решение?
Надо на Лист2 копию этой таблицы, без некоторых столбцов, при этом чтобы новые строчки тоже добавлялись.
Сделал во второй таблице в столбце формулу вида =ЕСЛИОШИБКА(Таблица1[Магазин];"")
Но в первой таблице когда добавляются новые строчки - во второй не добавляются. Можно ли сделать, чтобы добавлялись?
=ЕСЛИОШИБКА(ДВССЫЛ("Таблица1[Магазин]");"")
и при добавлении новых строк на другом листе должно быть видно все строки, включая новые. Я подобное при проверке вводимых данных в ячейке "по списку" из столбца "умной" таблицы из другого листа использую: =ДВССЫЛ("Таблица3[аа]"), а иначе =Таблица3[аа] - выдаёт ошибку, хотя и видимость Таблица3 вся книга
Если ли решение?
спасибо
Многострочные шапки совсем нельзя. Можно попытаться имитировать или подставлять на время работы однострочную, а "для шефа" подставлять красивую многоэтажную.
=ЕСЛИ(СЧЁТЗ(A2:E2)>0;СУММ(A2:E2);0)
Т.Е. как поставить защиту листа, при этом с отдельных столбцов снять "галку" защищаемая ячейка, и сохрать функционал "умной таблицы"?
Имеется несколько умных таблиц, абсолютно идентичных по формату (число столбцов, заголовки), но с разным содержанием, каждая таблица на отдельном листе, наименование таблицы соответственно характеру содержания (типа Деревья, Звери, Рыбы и т.д.)
На первом листе умная таблица аналогичного формата (число столбцов, заголовки), но содержание формируется из остальных таблиц следующим образом:
1 колонка - все ячейки с выпадающими списками с полным перечислением названий таблиц (типа Деревья, Звери, Рыбы и т.д.)
4 колонка - все ячейки с выпадающими списками с формулой =ДВССЫЛ([@1]&"[4]")
2 колонка с формулой =ИНДЕКС([@1];ПОИСКПОЗ([@4];[@1]&"["&"4"&"]";0);2)
3 колонка с формулой =ИНДЕКС([@1];ПОИСКПОЗ([@4];[@1]&"["&"4"&"]";0);3)
Ошибка возникает из-за невозможности прочитать имя диапазона(умной таблицы) из ячейки на которую дана ссылка типа [@1]
Я как-то и забыл про ДВССЫЛ, а ведь решение было почти на поверхности:)
терзает меня вопрос - таблица создается как объект, и к нему можно обратиться в ВБА:
ActiveWorkbook.Worksheets("Лист1").ListObjects("Таблица1").
в самой таблице заголовки должны иметь уникальное имя, подозреваю они будут являться объектами внутри таблицы, так вот можно ли у ним обращаться к их имени, или только адресом внутри?
Может это поможет
У меня есть две умные таблицы на одном листе, одна изменяющаяся другая нет. Располагаются так: изменяющаяся сверху на всю ширину листа (печатной области), а вторая маленькая ниже на две строки. Проблема возникает при добавлении новых строк в первую верхнюю таблицу, она растет вниз захватывая пустые строки и приближается ко второй таблице. Как сделать так чтобы это расстояние между таблицами оставалось неизменным.
Во второй таблице есть формулы которые ссылаются на первую таблицу. Обязательное условие: расположение обеих таблиц на одном листе для дальнейшей распечатки.
Подскажите, можно ли организовать выпадающий список который будет брать значения из определенного столбца умной таблицы?
=ДВССЫЛ("Таблица1[Имя_столбца]")При защите листа авторазвёртывание отключается автоматически. Но можно при защите разрешить пользователям вставку строк. Новые строки надо добавлять вовнутрь диапазона таблицы. Защита распространится на новые ячейки.
Ну, а с тем, что автоматически срабатывает автоподбор ширины столбцов, пришлось смириться.
Буду благодарна за помощь.
Помогите пожалуйста решить проблему. В своих книгах я использую "умные таблицы", соответственно все обращения в моих формулах написаны не в виде адреса, а в виде имен столбцов, строк и т.д. (ИНДЕКС(Сводка[#Данные];ПОИСКПОЗ(ОтчетОплатаПоставщику[@Контрагент]...). Я имею таблицу, в которой как выяснилось, периодически повторяются определенные данные, по которым происходит поиск соответствий. С помощью связки функций ИНДЕКС и ПОИСКПОЗ, я имею возможность получить только первое вхождение искомых данных. Не могли бы Вы подсказать, каким образом мне выудить не первое, а последнее вхождение искомого. Раньше я использовал функцию НАИМЕНЬШИЙ для изменения размера диапазона, а как же менять его размер, если он жестко зафиксирован размером столбца, например Таблица1[#Данные][Столбец1]? Заранее благодарю!
подскажите как вытащить имя таблицы?
На вкладке Конструктор есть имя таблицы в которой стоит активная ячейка.
На вкладке Формулы - Диспетчер имен - можно увидеть имена всех таблиц, которые есть в файле.
При использовании умными таблицами возникает необходимость составлять формулы по стандартным форме, например =СУММ(F2:F200) вместо =СУММ(Таблица1[НДС] ).
Подскажите, пожалуйста, как ускорить процесс ввода таких формул, не прибегая к ручному прописыванию (F2:F200), например.
Вопрос 2 - а можно в ней получать две итоговые суммы? Первая по отсортированной дате; Вторая - общая по всей таблице.
2. для итогов после фильтрации нужна функция вида ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;Таблица1[имя_столбца]), а для общих итогов простая СУММ(Таблица1[имя_столбца])
Возможно ли преобразовать "Умную таблицу" Обратно простую. Столкнулась с проблемой. При копирование данных из разных таблиц в одну. Таблица искажает данные и меняет ссылки. А когда я раньше работала с таблицей не преобразовав ее все вставлялось корректно.
Имеется в виду, оттягивание её.
Я правильно понимаю, что при использовании умных таблиц диаграмма построенная на базе такой таблицы будем сама менять диапазон в соответствии с изменением строк/столбцов в умной таблице?
UPD: При добавлении строк таблица расширяется, диаграмма достаивается. А вот при добавлении столбцов только таблица меняется.
Подскажите, возможно, ли вставить в определенном месте (в середине) диапазона / умной таблицы какое-то количество новых строк (формат и количество столбцов одинаковое) таким образом, чтобы они не заменили информацию в уже существующих строках?
Подскажите, можно ли в качестве диапазона для умной таблицы использовать динамический диапазон на функции СМЕЩ (OFFSET) или именованный диапазон с соответствующей функцией.
Идея была "приставить" умную таблицу к сводной таблице с изменяющимся количеством строк, для дополнительных вычислений. Если делать просто с помощью формул, то их приходится протягивать сильно вниз с запасом. Использование умной таблицы на основе динамического диапазона помогло бы решить задачу красиво, уменьшить размер файла, сократить время пересчёта...
Но выбрать имя этого диапазона как источник данных для умной таблицы не получается
Методом "Тыка" не получается, в интернете инструкций тоже не нашёл...
Если такая возможность есть - научите, буду признателен!
Если таковой имеется, подскажите пожалуйста!
У меня есть уже отредактированный прайс цен, там ловольно много объединенных ячеек, но когда я применяю "Форматирование Таблицы", то все объединенные ячейки становятся обычными и заново их объединить не получается. Скажите, можно ли применив "Форматирование Таблицы" сохранить исходный вид прайса?
Для примера прикрепляю два изображение как было и как стало:
Надеюсь на вашу помощь,
Спасибо:)
Скажите, как правильно обратиться к умной таблице в случае,
когда ее выбираем в качестве источника для выпадающего списка
в проверке данных?
Сейчас я просто ссылаюсь так: =Справочники!$B$2:$B$5
При этом я не учитываю, что этот же диапазон определяет умную таблицу Таблица1.
Как обратиться к Таблица1? =Справочники!Таблица1[Столбец1] ?
Выдает ошибку.
Спасибо.
А подскажите пожалуйста, как заставить умную таблицу "забыть" формулу, которую я ввел в вычисляемом столбце.
Допустим, в каком-либо столбце была формула "=1" и она послушно разворачивается при добавлении строк в таблицу. Теперь я решил, что в данном поле должны быть просто данные. Простым удалением формулы из ячеек этого поля ничего не добьешься - при добавлении новых строк Excel упорно будет восстанавливать формулу. Как заставить его ее забыть без удаления поля и создания его заново?
Немного эмоций :
все Ваши материалы КЛАД! и государству ничего не надо). Огромное спасибо за Вашу деятельность. С сайтом знакома давно, но был перерыв в практике использования Excel, и сейчас кое-что вспоминаю, а что-то и вновь осваиваю с помощью Ваших материалов уже на 2013. Более полезного и структурированного ресурса для себя не нахожу.
Вопрос связан с добавлением столбцов в "умную таблицу" - при вставке столбца и условиях, что справа и слева расположены столбцы со списками,
получаем новый столбец, соответственно со списком....Как избавиться от последнего (красиво и правильно избавиться)?
На обычном листе, получается выделить столбец и изменить значение Список на Любое значение через Проверку данных при выделенном столбце, а вот в "умной таблице" при выделенном (добавленном вставкой) столбце - Проверка просто неактивна....( Проверка активна, если выбрана ячейка, но тут либо к одной ячейке применить (что может быть оч затруднительно и трудоемко), либо ко всему диапазону с условием - тогда изменения постигнут и еще один столбец. а в нем - Список необходим.
Возможно есть какие хитрости при вставке, или выделении....Поделитесь пожалуйста - Как получить внутри "умной таблицы" "чистый" столбец? или по аналогии предыдущего вопроса - как заставить вставленный столбец забыть список?
У меня вроде бы работает так валидация...
Выделяются все значения столбца точно так же, как и весь столбец, когда появляется чёрная стрелка вниз, только ловить стрелку нужно в верхней части заголовка соответствующего столбца.
((( до чего ж я темнота )))
...самое очевидное - забыть про шапку .... даже итоги отменяла...эээ