В MS Excel есть много замечательных инструментов, о которых большинство пользователей не знают или сильно недооценивают. К ним относятся электронные таблицы Excel. Вы хотите сказать, что весь Excel — это электронная таблица? Нет. Рабочая область листа — это просто набор ячеек. Какие-то из них полные, какие-то пустые, но по сути и функционалу они все одинаковые.
Электронная таблица Excel совершенно другая. Это не просто диапазон данных, а цельный объект, который имеет собственное имя, внутреннюю структуру, свойства и множество преимуществ перед обычным диапазоном ячеек. Также встречается под названием «умные столы».
Как создать Таблицу в Excel
Доступен обычный диапазон данных о продажах.
Чтобы преобразовать диапазон в таблицу, выберите любую ячейку и нажмите «Вставить таблицу
Есть сочетание клавиш Ctrl+T.
Появится небольшое диалоговое окно, в котором вы можете настроить диапазон и указать, что первая строка содержит заголовки столбцов.
Как правило, мы ничего не меняем. После нажатия кнопки «ОК» исходный диапазон будет преобразован в электронную таблицу Excel.
Прежде чем мы перейдем к свойствам таблицы, давайте сначала посмотрим, как ее видит Excel. Скоро многое станет ясно.
Структура и ссылки на Таблицу Excel
Каждая таблица имеет свое имя. Это можно увидеть на вкладке «Макет», которая появляется при выборе любой ячейки в таблице. По умолчанию это будет «Таблица1», «Таблица2» и так далее
Если вы планируете иметь несколько таблиц в своей книге Excel, имеет смысл дать им более описательные имена. В будущем это облегчит использование (например, при работе в Power Pivot или Power Query). Я изменю название на Отчет. Таблица отчетов отображается в именах, определенных формулой диспетчера имен диспетчера имен.
А также при наборе формулы вручную.
Но самое интересное, что Excel видит не только всю Таблицу, но и отдельные ее части: столбцы, заголовки, итоги и т.д. Ссылки выглядят так.
=Report[#All] — для всей таблицы
=Report[#Data] — только для данных (без заголовка)
=Report[#Headers] — только по первой строке заголовков
=Report[#Totals] — к итогу
=Отчет[@] — для всей текущей строки (где вводится формула)
=Отчет[Продажи] — для всей колонки «Продажи»
=Report[@Sales]: по ячейке в текущей строке столбца Sales
Чтобы писать ссылки, не обязательно запоминать все эти конструкции. При наборе формулы вручную все они видны во всплывающей подсказке после выбора Таблица и раскрытия скобки (в английской раскладке).
Выберите нужный с помощью клавиши Tab. Не забудьте закрыть все скобки, включая квадрат.
Если в любой ячейке написать формулу суммирования по всему столбцу «Продажи»
=СУММ(D2:D8)
то он автоматически изменится на
=Отчет[Продажи]
Те ссылки ведут не на конкретный диапазон, а на весь указанный столбец.
Это означает, что диаграмма или сводная таблица, источником которых является таблица Excel, будут автоматически извлекать новые записи.
А теперь о том, как таблицы облегчают жизнь и работу.
Свойства Таблиц Excel
1. Каждая таблица имеет заголовки, которые обычно берутся из первой строки исходного диапазона.
2. Если таблица большая, при прокрутке вниз имена столбцов таблицы заменяются именами столбцов листа.
Очень удобно, не надо специально закреплять участок.
3. В таблицу по умолчанию добавлен автоматический фильтр, который можно отключить в настройках. Подробнее об этом ниже.
4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому сразу попадают в формулу (или график), ссылающуюся на какой-то столбец Таблицы.
Новые ячейки также оформляются в соответствии со стилем таблицы и заполняются формулами, если они есть в каких-либо столбцах. Короче говоря, чтобы расширить Таблицу, достаточно ввести только значения. Форматы, формулы, ссылки — все добавит само.
5. Новые столбцы также будут автоматически включены в таблицу.
6. Когда вы вводите формулу в ячейку, она сразу копируется на весь столбец. Не нужно тянуть вручную.
Кроме этих свойств возможны дополнительные настройки.
Настройки Таблицы
Контекстная вкладка Designer содержит дополнительные инструменты анализа и настройки.
Использование флажков в группе «Параметры стиля таблицы
вы можете внести следующие изменения.
— Удалить или добавить строку заголовка
— Добавить или удалить строку с итогами
— Сделать чередующееся форматирование строк
— Сделать первый столбец жирным
— Жирный последний столбец
— Сделать чередующуюся заливку строк
— Удалить автофильтр по умолчанию
В следующем видеоуроке показано, как это работает в действии.
В группе «Стили таблиц» вы можете выбрать другой формат. По умолчанию это похоже на изображения выше, но при необходимости это легко изменить.
В группе «Инструменты» вы можете создать сводную таблицу, удалить дубликаты и преобразовать в обычный диапазон.
Однако самое интересное — это создание слайсов.
Срез — это фильтр, размещенный на отдельном графическом элементе. Нажмите кнопку «Вставить слайсер», выберите столбцы, по которым мы будем фильтровать,
и нарезка готова. Показывает все уникальные значения выбранного столбца.
Чтобы отфильтровать таблицу, выберите интересующую категорию.
Если вам нужно выбрать несколько категорий, зажмите клавишу Ctrl или нажмите кнопку в правом верхнем углу, слева от удаления фильтра.
Попробуйте сами, насколько хороша фильтрация по срезам (кликните мышкой).
Для настройки собственного таргетинга на ленте также появляется контекстная вкладка «Параметры». В нем можно изменить стиль, размер кнопок, количество колонок и т.д. Там все ясно.
Ограничения Таблиц Excel
Несмотря на неоспоримые преимущества и огромные возможности, у электронных таблиц Excel есть и недостатки.
1. Представления не работают. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).
2. Текущей книгой нельзя поделиться.
3. Нельзя вводить промежуточные итоги.
4. Не работают формулы массива.
5. Ячейки не могут быть объединены. Правда, делать это в обычном диапазоне не следует.
Однако на фоне свойств и возможностей Таблиц эти недостатки практически не заметны.
В онлайн-курсе вы найдете много других секретов Excel.