Как включить умную таблицу в excel

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

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

Видео

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

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

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

Решение

Выделяем любую ячейку в таблице и на вкладке Главная (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 не было и этого.

Умные Таблицы Excel – секреты эффективной работы

В MS Excel есть много потрясающих инструментов, о которых большинство пользователей не подозревают или сильно недооценивает. К таковым относятся Таблицы Excel. Вы скажете, что весь Excel – это электронная таблица? Нет. Рабочая область листа – это только множество ячеек. Некоторые из них заполнены, некоторые пустые, но по своей сути и функциональности все они одинаковы.

Таблица Excel – совсем другое. Это не просто диапазон данных, а цельный объект, у которого есть свое название, внутренняя структура, свойства и множество преимуществ по сравнению с обычным диапазоном ячеек. Также встречается под названием «умные таблицы».

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Есть горячая клавиша Ctrl+T.

Появится маленькое диалоговое окно, где можно поправить диапазон и указать, что в первой строке находятся заголовки столбцов.

Как правило, ничего не меняем. После нажатия Ок исходный диапазон превратится в Таблицу Excel.

Перед тем, как перейти к свойствам Таблицы, посмотрим вначале, как ее видит сам Excel. Многое сразу прояснится.

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

Для написания ссылок совсем не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны в подсказках после выбора Таблицы и открытии квадратной скобки (в английской раскладке).

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

=СУММ(D2:D8)

то она автоматически переделается в

=Отчет[Продажи]

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

Это значит, что диаграмма или сводная таблица, где в качестве источника указана Таблица Excel, автоматически будет подтягивать новые записи.

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

1. Каждая Таблица имеет заголовки, которые обычно берутся из первой строки исходного диапазона.

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

3. В таблицу по умолчанию добавляется автофильтр, который можно отключить в настройках. Об этом чуть ниже.

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

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

Помимо указанных свойств есть возможность сделать дополнительные настройки.

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

В группе Стили таблиц можно выбрать другой формат. По умолчанию он такой как на картинках выше, но это легко изменить, если надо.

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

Однако самое интересное – это создание срезов.

Срез – это фильтр, вынесенный в отдельный графический элемент. Нажимаем на кнопку Вставить срез, выбираем столбец (столбцы), по которому будем фильтровать,

и срез готов. В нем показаны все уникальные значения выбранного столбца.

Для фильтрации Таблицы следует выбрать интересующую категорию.

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Ограничения Таблиц Excel

Несмотря на неоспоримые преимущества и колоссальные возможности, у Таблицы Excel есть недостатки.

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Поделиться в социальных сетях:

Как включить/отключить смарт-теги в Excel

В этом руководстве показано, как включить или отключить смарт-теги в Excel и Google Таблицах.

Что такое смарт-теги?

Смарт-теги — это небольшие значки, которые появляются под ячейками после выполнения определенных действий с данными в электронной таблице. Наиболее распространенный смарт-тег появляется при копировании и вставке данных, предлагая различные варианты вставки. После вставки данных под диапазоном вставки появляется следующий значок.

Если щелкнуть по нему, появятся различные параметры вставки, такие как вставка значений, вставка формул, транспонирование и т. д. средство проверки ошибок и быстрый анализ. Они включены по умолчанию. В разделе ниже показано, как отключить смарт-теги, если вы не хотите, чтобы они отображались.

Отключить смарт-теги

Если вы не используете смарт-теги или просто не хотите, чтобы они отображались, вы можете отключить их в параметрах Excel. У каждого из упомянутых выше смарт-тегов есть свой вариант включения/отключения, поэтому мы будем отображать их отдельно.

Параметры вставки и автозаполнение

Чтобы отключить параметры вставки и автозаполнение смарт-тегов, на ленте выберите Файл > Параметры .

В параметрах Excel выберите Дополнительно , прокрутите вниз до раздела Вырезать, копировать и вставить, снимите флажок Показать кнопку параметров вставки при вставке содержимого и нажмите OK.

В результате при копировании и вставке данных значок параметров вставки больше не отображается. (В этом примере отображается значок «Быстрый анализ», потому что он все еще включен.)

Если вы хотите снова включить смарт-тег , следуйте указанному выше пути и установите флажок. Это касается всех остальных смарт-тегов .

Быстрое заполнение

Функция мгновенного заполнения распознает шаблоны по мере ввода, поэтому вы можете автоматически заполнять поля в диапазоне предлагаемыми предложениями.

Чтобы отключить мгновенное заполнение, в окне параметров Excel:

Выберите Дополнительно и перейдите к параметрам редактирования.
Снимите флажок Автоматическое мгновенное заполнение .
Затем нажмите OK.

В результате смарт-тег Flash Fill не будет отображаться при вводе.

Быстрый анализ

Тег «Быстрый анализ» предоставляет возможные варианты анализа данных после выбора диапазона данных (например, форматирование, диаграммы, таблицы и т. д.).

Чтобы отключить смарт-тег быстрого анализа, в окне параметров Excel:

Выберите Общие в параметрах пользовательского интерфейса.
Снимите флажок Показать параметры быстрого анализа при выборе .
Затем нажмите OK.

Теперь смарт-тег быстрого анализа не отображается при выборе диапазона значений.

Проверка ошибок

При вводе формулы, в которой есть ошибка, появится смарт-тег проверки ошибок (зеленая стрелка в верхнем левом углу ячейки с желтым значком слева), сообщающий, что в ячейке ошибка.

Чтобы отключить смарт-тег проверки ошибок, в окне параметров Excel:

Выберите Формулы и прокрутите вниз до раздела Проверка ошибок
Снимите флажок Включить фоновую проверку ошибок .
Затем нажмите OK.

Подключите Microsoft Excel к интеграции Smartsheet за 2 минуты

Как подключить Microsoft Excel + Smartsheet

Zapier позволяет автоматически отправлять информацию между Microsoft Excel и Smartsheet — код не требуется.

Когда это произойдет...

сделайте это автоматически!

Бесплатная 14-дневная пробная версия

Более 5000 поддерживаемых приложений

Новая строка

Срабатывает при добавлении новой строки на рабочий лист электронной таблицы.

Триггер
Новая строка в таблице

Срабатывает при добавлении новой строки в таблицу электронной таблицы.

Триггер
Новый рабочий лист

Запускается при добавлении нового рабочего листа в электронную таблицу.

Триггер
Обновленная строка

Запускается при добавлении или обновлении строки на листе.

Триггер
Добавить строку

Добавляет новую строку в конец листа.

Действие
Добавить строку в таблицу

Добавляет новую строку в конец определенной таблицы.

Действие
Создать таблицу

Создает новую таблицу

Действие
Обновить строку

Обновляет строку на определенном листе.

Действие
Microsoft Excel
Находит строку по столбцу и значению. Возвращает всю строку, если она найдена.

Запланировано

Действие
Microsoft Excel
Находит или создает определенную строку поиска.

Запланировано

Действие
Smartsheet
Запускается при обновлении строки.

Запланировано

Триггер
Smartsheet
Запускается при добавлении нового вложения в строку.

Запланировано

Триггер
Smartsheet
Запускается при добавлении нового комментария.

Запланировано

Триггер
Smartsheet
Запускается при добавлении новой строки.

Запланировано

Триггер
Smartsheet
Создает рабочую область.

Запланировано

Действие
Smartsheet
Добавляет вложенный файл в строку.

Запланировано

Действие
Smartsheet
Добавляет обсуждение в строку.

Запланировано

Действие
Smartsheet
Добавление строки в таблицу.

Запланировано

Действие
Smartsheet
Копирует папку в другое место назначения.

Запланировано

Действие
Smartsheet
Копирует строку на другой лист.

Запланировано

Действие
Smartsheet
Создает копию указанной рабочей области.

Запланировано

Действие
Smartsheet
Перемещает строку на другой лист.

Запланировано

Действие
Smartsheet
Создать новый лист из существующего.

Запланировано

Действие
Smartsheet
Создание нового листа на основе шаблона.

Запланировано

Действие
Smartsheet
Обновление существующей строки новыми значениями. Требуется идентификатор строки.

Запланировано

Действие
Smartsheet
Отправить строку по электронной почте.

Запланировано

Действие
Smartsheet
Отправить таблицу по электронной почте (в формате PDF или Excel).

Запланировано

Действие
Smartsheet
Общий доступ к таблице.

Запланировано

Действие
Smartsheet
Общий доступ к рабочей области.

Запланировано

Действие

Шаг 1. Проверка подлинности Microsoft Excel и Smartsheet.

30 секунд
Шаг 2: Выберите одно из приложений в качестве триггера, который запустит вашу автоматизацию.

15 секунд
Шаг 3: Выберите результирующее действие из другого приложения.

15 секунд
Шаг 4: Выберите данные, которые вы хотите отправить из одного приложения в другое.

2 минуты
Готово! Больше времени для работы над другими делами.

Приложения Connect

Connect Microsoft Excel + Smartsheet

О Microsoft Excel

Microsoft Excel — это приложение для работы с электронными таблицами, которым пользуются миллионы пользователей по всему миру. Он является частью экосистемы Office и считается отраслевым стандартом для электронных таблиц. В настоящее время Zapier поддерживает только файлы в OneDrive для бизнеса (доступно с сервером Office 365 и Sharepoint).

Microsoft
Электронные таблицы

OneDrive

Управление файлами и хранилище, Microsoft

Smartsheet

Электронные таблицы

Quip

Документы

О Smartsheet

Smartsheet — это ведущая облачная платформа для выполнения работы, позволяющая командам и организациям планировать, фиксировать, управлять, автоматизировать и составлять отчеты о работе в масштабе, что приводит к повышению эффективности процессов и лучшие бизнес-результаты.

Электронные таблицы

MySQL

Базы данных

Zoho Creator

App Builder, Zoho

Knack