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